DROP PROCEDURE [HORate_VBL1_Charge]
GO

DROP PROCEDURE [HORate_DVAK01_Update]
GO

DROP PROCEDURE [HORate_VAK1_Update]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--============================================================================================================
--Author     <...8065>
--Alter date <...2016-08-22>
--Description<...门诊结帐过程(合并结帐)>
--2011.07.19,HHY,会员非帐户支付时也产生积分
--2011.08.25,HHY,会员积分支付
--2011.08.30,8065,结帐时更新CRM中对应本次就诊ID的预约记录为就诊,写入客户服务日志
--2011.09.19,HHY,积分余额不足时终止结算
--2011.09.20,HHY,卡消费时自动升级；伊莱美药品、材料和检验不送积分
--2012.05.07,8065,结算时更新CRM跟进人金额
--2012.05.28,8065,挂账时允许录入挂账单位
--2012.12.18,8065,如果存在医保结算记录，本地未传入医保支付方式，则不允许结算
--2012.12.31,8065,结算时改变CRM预约中状态
--2013.06.17,8065,体检病人结算时更新病人缴费明细表
--2013.07.09,8065,部分退费生成单据结算后，更新其处理状态为收费
--2013.10.18,8065,赠送或者积分类型的支付方式保存时校验是否会员病人
--2013.12.24,8065,会员卡支付方式只校验未设置接口驱动的记账类型
--2013.12.25,8065,前台传入多会员卡支付方式(用其他人卡消费)
--2013.12.26,8065,加入多卡扣费过程
--2014.01.21,8065,结账模式判断区分体检、His病人
--2014.06.25,8065,修改VCB04长度
--2014.09.16,8065,医保病人结算时写入医保结账关联记录
--2014.11.25,8065,根据药房参数写入药品摆药单
--2014.12.31,8065,减小锁粒度
--2015.07.27,8065,判断减免优惠支付方式与明细减免汇总是否相同
--2016.07.15,8065,增加会员多账号扣费处理
--2016.07.19,8065,会员卡基本账户扣费统一调用HORate_VBL1_Charge处理
--=============================================================================================================
CREATE PROC [HORate_VAK1_Update] @Xml ntext,@VAK01 int out,@VAK04 varchar(20) out,@VAK06 int =2 
AS 
DECLARE @iDOM int, @iret int
Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML
if @iret > 0
BEGIN
   exec sp_xml_removedocument @iDOM	
   RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
   return @iret
END 
--获取操作员基本信息
DECLARE @EmpId int ,
        @EmpNo varchar(20) ,
        @EmpName varchar(20) ,
        @HostIp varchar(256) ,
        @HostName varchar(256) ,
        @DeptId int ,
        @SCA01 int ,
        @SCH01 int ,
        @DeptNo varchar(20) ,
        @DeptName varchar(20) ,
        @HostMac varchar(256) ,
        @FileVer varchar(20) ,
        @Event varchar(4086) ,
        @SCH12 varchar(1024) ,
        @TotalVBL numeric(18,4) ,
        @VAA07 int ,
        @CurDate datetime 
        
SELECT  @EmpId = EmpId , 
        @EmpNo = EmpNo ,
        @EmpName = EmpName ,
        @DeptId = DeptId ,
        @DeptNo = DeptNo ,
        @DeptName = DeptName ,
        @HostIp = HostIp ,
        @HostName = HostName ,
        @HostMac = HostMac ,
        @FileVer = FileVer 
  FROM OpenXml(@iDOM,'/Root/OperInfo',8) 
  WITH 
  (  
  	    EmpId int , 
        EmpNo varchar(20) ,
        EmpName varchar(20) ,
        DeptId int ,
        DeptNo varchar(20) ,
        DeptName varchar(20) ,
        HostIp varchar(256) ,
        HostName varchar(256) ,
        HostMac varchar(256) ,
        FileVer varchar(20)
  )
  --如果发现当前的版本号低于指定的Dll文件版本号，提示客户升级
  Declare @ISSAME int ,
          @Description varchar(1024)          
  select @ISSAME = ISSAME ,@Description = Description from dbo.[FileVerCheck](@FileVer , 'HORate.dll')
  If @ISSAME = 0
  BEGIN
     exec sp_xml_removedocument @iDOM
     RAISERROR(@Description, 16, 1) with nowait
     RETURN 2
  END
  --病人信息
  SELECT * INTO #TmpVAA1
  from OpenXml(@iDOM,'/Root/VAA/Ie',8)
  WITH
  (
     	VAA01 int ,
        VAA07 int ,
        ACF01 int ,
        VAA03 varchar(20) ,
        VAK07 numeric(18,4) ,
        VAK08 numeric(18,4) ,
        VAK09 numeric(18,4) ,
        VAK10 numeric(18,4) ,
        BCK01A int ,
        BCE01A int ,
        BCE02A varchar(20) ,       
        BCE03A varchar(20) ,
        EmpId int ,
        EmpNo varchar(20) ,
        EmpName varchar(20) ,
        HostName varchar(128) ,
        HostIP varchar(32) ,
        WorkName varchar(20) ,
        WorkType smallint ,
        WorkFrom smallint ,   
        VAK17 varchar(255) ,   
        KuoZhan1 varchar(255) , 
        VBU01 int
   )	
      
  IF NOT EXISTS(SELECT * FROM #tmpVAA1)
  BEGIN
     EXEC sp_xml_removedocument @iDOM
     RAISERROR('提交错误，病人信息为空', 16, 1) with nowait
     RETURN 2
  END
  
  IF EXISTS(SELECT * FROM #tmpVAA1 where ISNULL(BCK01A , 0) <= 0)
  BEGIN
     EXEC sp_xml_removedocument @iDOM
     RAISERROR('提交错误，病人科室不能为空', 16, 1) with nowait
     RETURN 2
  END
  
  SELECT @VAA07 = VAA07 from #TmpVAA1 
  SET @VAA07 = ISNULL(@VAA07 , 0)
  --如果当前系统参数设置为合并结帐，提示操作员关闭结帐窗口，重新结帐
  If exists(select * from VAC1 with(nolock) where VAC01 = @VAA07 and VAC09 <> -3)
  BEGIN
     If not exists(select * from SYS_Parameters where ProductID = 100 and ProgramID = 103010 and ParamNo = 93 and Value='1')
     BEGIN
       If (@VAK06 = 2) and exists(select * from SYS_Parameters where ProductID = 100 and ProgramID = 103010 and ParamNo = 6 and Value = 1)
       BEGIN
         exec sp_xml_removedocument @iDOM
         RAISERROR('系统结帐参数已经改变为[分单结帐]模式，请关闭收费窗口，重新进入后结帐.', 16, 1) with nowait
         RETURN 2 
       END
     END
  END
  --结帐总金额不能为负数
  IF NOT EXISTS(SELECT * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 33 and Value = 1)
  BEGIN
     IF EXISTS(SELECT * from #TmpVAA1 where VAK07 < 0)
     BEGIN
        EXEC sp_xml_removedocument @iDOM
        RAISERROR('提交错误，结帐总金额不能为负数.', 16, 1) with nowait
        RETURN 2
     END
  END
  --病人交款信息
  select IDENTITY(int,1,1) AS ID, * into #tmpVBL1
  from OpenXml(@iDOM,'/Root/VBL/Ie',8)
  with 
  (
        VBL01 int ,
        FAF01 int ,
        VBL02 varchar(20) ,
        VBL11 varchar(20) ,
        VBL13 numeric(18,4) ,
        VBL14 varchar(32) ,
        VBL15 varchar(2) ,
        VBU01 int ,
        BAQ03 varchar(64),
        BBO02 varchAR(64)
   )	
   --多个会员卡消费情况
   select IDENTITY(int,1,1) AS ID, * into #tmpVCA1
   from OpenXml(@iDOM,'/Root/VCA/Ie',8) 
   with 
   (
    	VBU01 int ,
    	VCB04 varchar(64) ,
    	VCA10 numeric(18 ,4),
    	AMODE int 
   )   
   --如果存在尾数处理，并且金额大于1，提示错误
   If exists(select * from #tmpVBL1 where VBL15 = '98' and ABS(VBL13) > 1)
   BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，尾数金额不能大于1，请检查.', 16, 1) with nowait 
       RETURN 2
   END
   --如果存在没有会员帐号的记帐类型的支付方式不允许
   If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0  and VBL15 = '05' )   
   BEGIN
       If not exists(select * from #tmpVCA1 where AMODE = 1)
       BEGIN
         exec sp_xml_removedocument @iDOM
         RAISERROR('提交错误，记帐类型支付方式必须与会员卡关联', 16, 1) with nowait 
         RETURN 2
       END
   END
   --如果存在没有会员帐号的赠送类型的支付方式不允许
   If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0 and ISNULL(VBU01 , 0) = 0 and VBL15 = '42' and @VAK06 = 2)
   BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，赠送类型支付方式必须与会员卡关联', 16, 1) with nowait 
       RETURN 2
   END
    --如果存在没有会员帐号的积分类型的支付方式不允许
   If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0 and ISNULL(VBU01 , 0) = 0 and VBL15 = '43' and @VAK06 = 2)
   BEGIN     
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，积分类型支付方式必须与会员卡关联', 16, 1) with nowait 
       RETURN 2
   END   
   --存在会员卡接口支付方式，未传入卡号消费的情况报错
   If exists(select * from #tmpVBL1 a, BBP1 b where a.VBL14 = b.BBP02 and ISNULL(BBP09 ,0) = 1 and a.VBL15 ='05')
   BEGIN
     If not exists(select * from #tmpVCA1 where AMODE = 2)
     BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，记帐类型接口类型支付方式未传入待扣费会员卡信息。', 16, 1) with nowait 
       RETURN 2
     END
   END
   --by 潇湘子2010-12-18，折扣全为0的情况
   IF EXISTS(SELECT * FROM #tmpVAA1 WHERE VAK07 > 0)
   BEGIN
      IF (NOT EXISTS(SELECT * FROM #tmpVBL1)) 
      BEGIN
         EXEC sp_xml_removedocument @iDOM
         RAISERROR('提交错误，病人交款信息为空', 16, 1) with nowait
         RETURN 2
      END
   END
   
   select @TotalVBL = SUM(VBL13) from #tmpVBL1 
   --医保结算信息
   select IDENTITY(int,1,1) AS ID, * into #tmpIAS1
   from OpenXml(@iDOM,'/Root/IAS/Ie',8) 
   with 
   (   
        IBL01 int,
    	IAS01 int,
    	VAI01 int
   )
   --如果存在医保结算记录，本地未传入医保支付方式，则不允许结算
   If exists(select * from #tmpIAS1 a 
               join IAS1 b with(nolock) on b.IAS01 = a.IAS01 
             where ISNULL(b.IAS45 ,0) + ISNULL(b.IAS46 , 0) > 0
            ) 
   BEGIN
     If not exists(select * from #tmpVBL1 where VBL15 = '03')
     BEGIN
        exec sp_xml_removedocument @iDOM
        RAISERROR('提交错误，未传入已经结算的医保记帐信息.', 16, 1) with nowait
        RETURN 2
     END
   END 
   --病人收费单据
   select IDENTITY(int,1,1) AS ID, * into #tmpVAI1
   from OpenXml(@iDOM,'/Root/VAI/Ie',8) 
   with 
   (
        VAI00 varchar(38) ,
        VAI01 int ,
        VAI04 varchar(20) ,
        VAI05 varchar(1024) ,
        Enabled smallint ,
        VAI17 smallint	
   )	
   If not exists(select * from #tmpVAI1)
   BEGIN
        exec sp_xml_removedocument @iDOM
        RAISERROR('提交错误，病人单据信息为空', 16, 1) with nowait
        RETURN 2
   END
   --如果传入后台的单据费用中存在VAI01 <- 0，抛错
   If exists(select * from #tmpVAI1 where VAI01 <= 0)
   BEGIN
        exec sp_xml_removedocument @iDOM
        RAISERROR('提交错误，费用单据信息不正确.', 16, 1) with nowait
        RETURN 2
   END
   --病人收费记录信息
   select IDENTITY(int,1,1) AS ID, * into #tmpVAJ1
   from OpenXml(@iDOM,'/Root/VAJ/Ie',8) 
   with 
   (
        VAJ01 int ,
        ROWNR int ,
        VAI00 varchar(38) ,
        VAI01 int ,
        BDN01 varchar(2) ,
        BBY01 int ,
        VAJ22 smallint ,
        VAJ23 smallint ,
        VAJ24 numeric(18,4) ,
        VAJ25 numeric(18,4) ,
        VAJ26 smallint ,
        VAJ27 int ,
        BCK01D int ,
        VAJ30 numeric(9,4) ,
        VAJ31 numeric(9,4) ,
        VAJ59 numeric(18,6) ,
        VAJ32 numeric(18,4) ,
        VAJ33 numeric(18,4) ,
        VAJ34 numeric(18,4) ,
        VAJ35 varchar(20) ,
        VAJ36 numeric(18,4) ,
        VAJ37 numeric(18,4) ,
        VAJ38 numeric(18,4) ,
        VAJ39 smallint ,
        VAJ61 numeric(18,4)
     )
   
    Declare @i int ,
            @j int ,
            @VAA01 int ,
            @VAI01 int ,
            @VAI16 int , 
            @Count int ,        
            @Value int ,                      
            @VBL01 int ,
            @VBU01 int ,           
            @IAS01 int , 
            @ErrId int ,                 
            @BCK01A int ,
            @BCK01B int ,
            @BCE01A int ,
            @Result int ,
            @BBP02 varchar(32) ,
            @VAK09 numeric(18 ,2) ,
            @VAK10 numeric(18 ,2) ,   
            @IsHyType int ,                       
            @BBP06 tinyint ,
            @WorkType smallint , 
            @AWorkType smallint ,
            @WorkFrom smallint ,          
            @VBL02 varchar(20) , 
            @VAK07 numeric(18,4) ,
            @WorkName varchar(20) ,
            @VAK08 numeric(18,4) ,                                                           
            @BCE02A varchar(20) ,       
            @BCE03A varchar(20) , 
            @VAK17 varchar(255) , 
            @SumAAO05 numeric(18,4) , 
            @SumAAO42 numeric(18,4) ,
            @SumVCA10 numeric(18,4) , 
            @SumVAJ38 numeric(18,4) ,
            @YbMoney numeric(18,4) ,
            @CashMoney numeric(18,4) ,
            @VAK13 datetime ,
            @SumAAO04 numeric(18 ,4)     
   /*========================================================数据校验=============================================================*/        
   If not exists(select * from #tmpVAI1 I join #tmpVAJ1 J on J.VAI00 = I.VAI00)
   BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('保存错误，重新操作。', 16, 1) with nowait
       RETURN 2
   END
       
   If not exists(select * from #tmpVAJ1)
   BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，病人收费记录信息为空', 16, 1) with nowait
       RETURN 2
   END
   --如果存在没有明细的单据信息则返回
   If exists(select * from #tmpVAI1 where VAI00 not in(select VAI00 from #tmpVAJ1)) 
   BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，存在没有明细的单据信息，请重新检索收费信息.', 16, 1) with nowait
       RETURN 2
   END
   /*======================================如果是会员模式，并且存在多个类型为“记帐”的支付方式，不允许==================================*/
   If (dbo.GetWorkingMode() > 0)  
   BEGIN
     If exists(select AAO01 from BBP1 where AAO01 = '05' and ISNULL(BBP09 ,0) = 0 group by AAO01 having COUNT(AAO01)  > 1) 
     BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，会员模式下只允许一种类型为“记帐”的支付方式', 16, 1) with nowait
       RETURN 2
     END
   END
   
  exec sp_xml_removedocument @iDOM
  
  select @BBP02 = VBL14 from #tmpVBL1 where VBL15 = '03'        
  select @YbMoney = SUM(ISNULL(VBL13 , 0)) from #tmpVBL1 where VBL15 = '03'
  select @CashMoney = SUM(ISNULL(VBL13 ,0)) from #tmpVBL1 where VBL15 = '01'
  select @SumAAO04 = SUM(ISNULL(VBL13 ,0)) from #tmpVBL1 where VBL15 = '04'
  set @YbMoney = ISNULL(@YbMoney , 0)
  set @CashMoney = ISNULL(@CashMoney , 0)
  set @SumAAO04 = ISNULL(@SumAAO04 , 0)
  
  select @BBP06 = ISNULL(BBP06 , 0) from BBP1 where AAO01 = '03' and BBP02 = @BBP02
  
  IF ISNULL(@BBP06 , 0) = 0 --本地医保输入
    set @YbMoney = 0 
  else 
    set @YbMoney = ISNULL(@YbMoney , 0)
      
  select @VAA01 = VAA01,@VAA07 = VAA07 ,@WorkName = WorkName,@WorkType = WorkType,
         @BCK01A = BCK01A,@WorkFrom = WorkFrom ,@VAK07 = VAK07,@VAK08 = VAK08 + @YbMoney ,@VAK17 = VAK17 ,@BCE01A = BCE01A,
         @BCE02A = BCE02A, @BCE03A = BCE03A ,@VBU01 = ISNULL(VBU01 ,0) , @VAK09 = VAK09 , @VAK10 = VAK10 
  from #tmpVAA1  
  
  If ISNULL(@EmpId , 0) = 0
  BEGIN
    SELECT @EmpId = EmpId , @EmpNo = EmpNo , @EmpName = EmpName from #tmpVAA1 
  END       
  select @IsHyType = dbo.GetWorkingMode()
  
  /*===========================================如果实收金额大于0，判断实收金额是否 = 现金支付 + 找零=======================================*/
  If (@VAK09 > 0 ) and (ABS(@CashMoney) > 0) and (@VAK06 = 2)
  BEGIN
    If @VAK09 <> @CashMoney + @VAK10 
    BEGIN
       RAISERROR('提交错误，实收金额不等于(现金支付 + 找零)，请检查.', 16, 1)  WITH NOWAIT
       Return 1
    END
  END
  /*===================================================判断支付方式总额与结帐金额是否相等===================================================*/
  If @VAK07 <> @TotalVBL 
  BEGIN
     RAISERROR('提交错误，支付方式总额与结帐金额不等，请检查.', 16, 1)  WITH NOWAIT
     Return 1
  END
  --查找后台的金额合计
  select @SumVAJ38 = SUM(a.VAJ38)
  from VAJ1 a with(nolock)       
     join #tmpVAI1 b on b.VAI01 = a.VAI01 
    
   where b.Enabled = 0  and a.VAJ05 = 1     
  
   /*===================================================如果病人没有ID,不让保存数据=========================================================*/
   IF (@VAA01 <= 0)  OR (@VAA07 <= 0)
   BEGIN
      RAISERROR('提交错误，病人信息为空.', 16, 1)  WITH NOWAIT
      Return 1
   END
   /*=================================================如果尾数金额大于1,不让保存数据========================================================*/
   If abs(@VAK07 - @VAK08) >= 1
   BEGIN
      RAISERROR('尾数金额错误，不能大于1.', 16, 1)  WITH NOWAIT      
      Return 1
   END
   /*=================================================判断尾数金额与前台传入尾数金额是否相等================================================*/
   If exists(select * from #tmpVBL1 where VBL15 = '98' and (VBL13 <> (@VAK07 - @VAK08)))
   BEGIN      
      RAISERROR('提交错误，前台传入尾数金额与支付方式中尾数金额不等.', 16, 1)  WITH NOWAIT      
      Return 1   
   END
   /*==============================================判断如果明细中有已经结账的信息，则退出===================================================*/
   IF EXISTS(SELECT * FROM #tmpVAI1 a JOIN VAI1 b with(nolock) ON a.VAI01 = b.VAI01 WHERE a.Enabled = 0 AND b.VAI16 = 2 )
   BEGIN 
      RAISERROR('待结账信息中存在已结账单据.', 16, 1)  WITH NOWAIT
      Return 1 
   END  
   /*=============================================判断如果前台跟后台的金额不符，则提示不让结帐===============================================*/
   IF @VAK07 <> @sumVAJ38 
   BEGIN
      RAISERROR('前台传入金额与后台合计不一致，请重新检索.', 16, 1)  WITH NOWAIT
      Return 1 
   END  
  /*=====================================================生成病人交款、明细临时表============================================================*/                            
   SELECT * INTO #tmpVAIVCA
   FROM 
     (
      SELECT VAI01 
      FROM #tmpVAI1                  
      WHERE Enabled = 0
      
      UNION ALL 
      
      SELECT b.VAI01  
      FROM #tmpVAI1 a
        join VAI1 b with(nolock) on b.VAI01A = a.VAI01 
      WHERE Enabled = 0
     ) a 
     
   SELECT @i = 0 , @Count = COUNT(1) FROM #tmpVBL1 
   EXEC @ErrId = Core_NewIDEx 'VBL1' , 'VBL01' , @Value out , @Count 
   If @ErrId > 0
   BEGIN
     RETURN 1
   END
   
   WHILE @i < @Count 
   BEGIN
      SET @VBL02 = '000000000'
      SET @VBL02 = @VBL02 + Cast(@Value - @Count + @i + 1 AS VARCHAR)
      SET @VBL02 = 'L' +  SUBSTRING(@VBL02, Len(@VBL02) - 9, 10)
      
      UPDATE #tmpVBL1
   	  SET VBL01 = @Value - @Count + @i + 1,
   	      VBL02 = @VBL02 
      WHERE [ID] = @i + 1
      SET @i = @i + 1
   END  

   SET @CurDate = GETDATE() 
   
   BEGIN TRAN --开始事务   
  /*=====================================================保存病人结账信息及交款信息==========================================================*/  	
   --写入病人结帐单据,生成结帐单据号及ID
   DECLARE @VAK21 numeric(18,4),--病人费用总额包含尾数处理   	
  	       @VAK23 numeric(18,4),
  	       @VAK24 numeric(18,4)
  	        
   Exec @ErrId = Core_NewIdEx 'VAK1','VAK01',@VAK01 out
   
   If @ErrId > 0
   BEGIN 
     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RETURN 1
   END
   
   SET @VAK04 = '000000000'
   SET @VAK04 = @VAK04 + CAST(@VAK01 AS VARCHAR)
   SET @VAK04 = 'K' +  SUBSTRING(@VAK04, Len(@VAK04) - 9, 10)
   SET @VAK17 = '结账方式：[合并结账] ' + ISNULL(@VAK17 , '')     
   IF ISNULL(@VAK17,'') =''   --如果没有结账备注信息，则写入默认的备注信息
   BEGIN
      SET @VAK17 = @WorkName 
   END
   --插入数据
   INSERT INTO VAK1(VAK01, VAA01, VAA07, VAI04, FAB03, VAK06, VAK07, VAK08, VAK09, VAK10, BCE02A, BCE03A, 
      VAK13, VAK01A, VAK15, VAK16, VAK17, VAK18, VAK19 , BCE01A , VAK28)
      
   SELECT @VAK01, @VAA01, @VAA07, @VAK04, '', @VAK06, VAK07 - @VAK08, VAK08 + @YbMoney , VAK09, VAK10, @EmpNo, @EmpName, 
      @CurDate, 0, @CurDate, @CurDate,  @VAK17, 0, 1, @EmpID  , @VAK01 
   from #tmpVAA1
   
   IF @@ERROR > 0 --如果发生错误则回滚数据
   BEGIN
      IF @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('写入病人结帐单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END
   --如果是挂号，结帐成功后，更新挂号状态为1
   If @VAK06 = 1 
   BEGIN
     Update VAC1 with(rowlock)
       set VAC45 = 1 
     where VAC01 = @VAA07 
   END
   --写入病人交款单据
   INSERT INTO VBL1(VBL01, VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, VBL11 ,VBL12, VBL13, VBL14, VBL15, VAK01, VBL17,
                    VBL18, VBL19, VBL01A, BCE01, BCE02, BCE03, VBL24, VBL25, VBL26, VBL27 , VBU01 , FAF01 , BAQ03, BBO02  )
                        
   SELECT VBL01 , VBL02, '', @VAK06 + 1, 0, @VAA01, @VAA07, @DeptId, VBL11 ,@WorkName, VBL13, VBL14, VBL15, @VAK01, 0,
                    @CurDate, @CurDate, 0, @EmpID, @EmpNo, @EmpName, 0, 0, 0, 2 , VBU01 , ISNULL(FAF01 , 0), BAQ03, BBO02  
   from #tmpVBL1 
                           
   IF @@ERROR > 0 --如果发生错误则回滚数据   
   BEGIN
      IF @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('写入病人交款记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END
   
   /*======================================积分消费和现金支付产生积分================================================================================*/ 
   IF ISNULL(@VBU01 ,0) <= 0 
     SELECT @VBU01 = VBU01 from VAA1 with(nolock) where VAA01 = @VAA01 
          
   IF @VBU01>0
   BEGIN
     DECLARE @Exchange numeric(18,2),@tmpVBL01 int
     --会员积分支付
     SELECT @Exchange=-SUM(VBL13) FROM #tmpVBL1 WHERE VBL15='43'
     SELECT @tmpVBL01=VBL01 FROM #tmpVBL1 WHERE VBL15='43'
     IF ISNULL(@Exchange,0)<>0
     BEGIN
       EXEC @ErrId=Customer_VBU17_DESC @VBU01,13,@VAK01,@tmpVBL01,@Exchange,@EmpName
       IF @ErrId > 0 --如果发生错误则回滚数据   
       BEGIN
          IF @@TRANCOUNT > 0 
          ROLLBACK TRAN
          RAISERROR('会员客户积分支付时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
          Return 1
       END
     END
   
     --会员非帐户支付时也产生积分
     IF dbo.GetCRMMode()=2--伊莱美：药品、检验不送积分
     BEGIN
       DECLARE @Exchange2 numeric(18,2)
       --不能送积分的支付方式合计
       SELECT @Exchange=SUM(VBL13) FROM #tmpVBL1 WHERE VBL15 NOT IN('01','02','03','05')
       --能送积分的项目合计
       SELECT @Exchange2=SUM(VAJ38) FROM #tmpVAJ1 WHERE BDN01>'4' AND BDN01<>'L'
       
       SET @Exchange=ISNULL(@Exchange2,0)-ISNULL(@Exchange,0)
     END ELSE
       SELECT @Exchange=SUM(VBL13) FROM #tmpVBL1 WHERE VBL15 IN('01','02','03','05')
       
     IF ISNULL(@Exchange,0)>0
     BEGIN
       EXEC @ErrId=Customer_VBU17_INC @VBU01,11,0,@VAK01,0,@Exchange,@EmpName
       IF @ErrId>0 OR @@ERROR<>0 --如果发生错误则回滚数据   
       BEGIN
          IF @@TRANCOUNT > 0 ROLLBACK TRAN
          RAISERROR('会员客户现金交易产生积分时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
          Return 1
       END
     END
     
     --卡消费时自动升级
     IF @Exchange>0 
     BEGIN
       EXEC @ErrId=Customer_Upgrade_update @VBU01,@EmpId,@EmpName
       IF @ErrId>0 OR @@ERROR<>0
       BEGIN
         IF @@TRANCOUNT > 0 ROLLBACK TRAN
         RAISERROR('卡消费自动升级时发生错误。',16,1) WITH NOWAIT
         RETURN 1
       END
     END 
   END
  /*===========================================================更新挂号状态为结算=============================================================*/
  If @VAK06 = 2
  BEGIN
    Update VAC1 with(rowlock)
      SET VAC70 = 1 
    where VAC01 = @VAA07 
  END
  /*============================================================更新病人收费单据==============================================================*/     
   If exists(select * from #tmpVAI1 a join VAI1 b with(nolock) on a.VAI01 = b.VAI01  where b.VAI16 = 9 and a.Enabled = 0)
   BEGIN
      IF @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('病人存在已作废的费用单据，请重新检索.，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END
   
    Update a 
      set a.VAI16 = 2 ,
          a.VAI18 = @WorkType ,
          a.VAK01 = @VAK01 
    from VAI1 a with(rowlock)
      join #tmpVAI1 b on a.VAI01 = b.VAI01
    where a.VAI16 = 1 and b.Enabled = 0
 /*===========================================================写入病人收费记录明细信息============================================================*/
   If exists(select * from #tmpVAJ1 a join VAJ1 b with(nolock) on a.VAJ01 = b.VAJ01  where b.VAJ05 = 9 and a.VAJ01>0)
   BEGIN
      IF @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('病人存在已作废的费用明细，请重新检索.，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END
   
   update a
     set a.VAJ05 = 2 ,
         a.VAK01 = @VAK01 ,
         a.VAJ04 = @WorkType
     from VAJ1 a with(rowlock)      
       join #tmpVAI1 b on a.VAI01 = b.VAI01
       join #tmpVAJ1 c on b.VAI00 = c.VAI00 
   where b.Enabled = 0  and a.VAJ05 = 1     
   
   select @VAI01 = VAI01 from VAJ1 with(nolock) where VAK01 = @VAK01 order by VAI01 desc    
/*======================================================更新结账费用总额,包含尾数处理==============================================================*/
  select @VAK21 = SUM(VAJ36),@VAK23 = SUM(VAJ38),@VAK24 = SUM(VAJ61) 
  from VAJ1 with(nolock) 
  where VAK01 = @VAK01
  
  --减免优惠与明细中减免优惠合计不同，则系统提示并退出
  If ABS(@SumAAO04) > 0 
  BEGIN
    If @SumAAO04 <> (@VAK23 - @VAK24)
    BEGIN
      IF @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('减免支付方式与明细减免优惠汇总不等，请检查.', 16, 1) with nowait 
      RETURN 1
    END
  END
  
  update VAK1 with(rowlock)
     set VAK21 = @VAK21,
         VAK23 = @VAK23,
         VAK24 = @VAK24 
  where VAK01 = @VAK01 
/*========================================================更新本地医保结算信息=====================================================================*/
If Exists(select * from #tmpIAS1)
BEGIN
  	Update a 
  	  set a.VAK01 = @VAK01 
  	from IAS1 a with(rowlock)
  	  join #tmpIAS1 b on b.IAS01 = a.IAS01   
  	  
   DECLARE @IBL01 int 
   Exec Core_NewIDEx 'IBL1' , 'IBL01' , @IBL01 out 
   SELECT @i =0 , @Count = COUNT(0) from #tmpIAS1 
   
   while @i < @Count 
   BEGIN
     Update #tmpIAS1 
       SET IBL01 = @IBL01 - @Count + @i + 1
     where ID = @i + 1
     SET @i = @i + 1
   END
   
   INSERT INTO IBL1(IBL01 , IAS01 , VAK01)
   SELECT c.IBL01 , a.IAS01 , @VAK01 
   from IAS1 a 
     join #tmpIAS1 c on a.IAS01 = c.IAS01 
     
   IF @@ERROR > 0 --如果发生错误则回滚数据
   BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RAISERROR('写入医保结账记录关联时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
   END       
  --END  
END
/*================================================================会员扣费========================================================================*/
 IF exists(select * from #tmpVCA1) or 
    exists(select * from #tmpVBL1 where VBL15 = '42' and VBU01 > 0 and VBL13 > 0)
 BEGIN
     If @VAK06 = 1
       SET @AWorkType = 5
     ELSE if @VAK06 = 2 
       SET @AWorkType = 6  
      
     Exec @ErrId=HORate_VBL1_Charge @Xml , @VAK01 , @VBU01 , 0 , 0 , @AWorkType  
   
     IF @ErrId>0 OR @@ERROR<>0
     BEGIN
       IF @@TRANCOUNT > 0 ROLLBACK TRAN
       RAISERROR('会员卡消费时发生错误。',16,1) WITH NOWAIT
       RETURN 1
     END
 END
/*=======================================================删除业务单据通知中的数据==================================================================*/
If Exists(select * from VBX1 with(nolock) where VAA01 = @VAA01 and VAA07 = @VAA07 and ACF01 = 1 and VBX03 = 1 and VBX04 = 2)
BEGIN
  Update a
    SET VBX22 = 2
  from VBX1 a with(rowlock)
    join #tmpVAI1 b on b.VAI01 = a.VBX19 
  where b.Enabled = 0 and a.VAA01 = @VAA01 and a.VAA07 = @VAA07 and ACF01 = 1 and VBX03 = 1 and VBX04 = 2
END
/*==================================================更新CRM中对应本次就诊ID的预约记录状态为就诊====================================================*/
If @VAK06 = 2             --门诊结算
BEGIN
  Update SCF1 with(rowlock)
    SET SCF11 = 5
  where VAA07 = @VAA07 and ACF01 = 1
END ELSE If @VAK06 = 1    --挂号
BEGIN
  Update SCI1 with(rowlock)
    SET SCI11 = @VAK08 ,
        SCI05 = @VAK01 
  where VAA07 = @VAA07 and ACF01 = 1 
  
  Update SCF1 with(rowlock)
    SET SCF11 = 4
  where VAA07 = @VAA07 and ACF01 = 1 and SCF11 <= 2    
END
/*=====================================================体检病人结算后更新病人缴款明细表==========================================================*/
If exists(select * from VAC1 where VAC01 = @VAA07 and VAC09 = -3)
BEGIN
  Declare @VBM01 int 
  Exec HORate_VBM1_Update @VBM01 out , @VAA01 , 4 , 0 , @VAK23 , 0 ,@VAA07 
END
/*================================================================================================================================================*/
Commit Tran 
/*============================================================写入客户服务日志===================================================================*/
If @VAK06 = 2 
BEGIN
   SELECT @SCA01 = SCA01 from SCA1 where VAA01 = @VAA01 and SCA37 <> 2
   If ISNULL(@SCA01 , 0) > 0 
   BEGIN
      SELECT @VAK13 = VAK13 from VAK1 where VAK01 = @VAK01 
      SET @SCH12 = '结帐时间：' + convert(varchar(30) , @VAK13 , 111) + ' 结帐金额：' + CAST(@VAK08 as varchar(20))  
      Exec @ErrId = HOCRM_SCH1_Update @SCH01 out , @SCA01 , 0 , 'VAK1' , 'VAK01' , 106 , '门诊收费' , @SCH12 , 0 , 0 , @EmpId ,@EmpName 
   END
END
/*==============================================================根据参数写入药品摆药单============================================================*/
If exists(select * from Sys_Parameters where productid =100 and programid = 111001 and paramno = 63 and Value='1')
BEGIN
  If exists(select * from #tmpVAJ1 where BDN01 <= '3')
  BEGIN
    SELECT BCK01D INTO #tmpDSY1 
    from #tmpVAJ1
    where BDN01 <= '3'
    GROUP BY BCK01D
    
    INSERT INTO DSY1(VAA01 , VAA07 , ACF01 ,VAK01 , PNRCP , DFLAG , BCK01)
    SELECT @VAA01 , @VAA07 , 1 , @VAK01 , 0 , 0 , BCK01D from #tmpDSY1
  END
END
/*=================================================================================================================================================*/



GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--======================================================================================================
--Author     <...8065>
--Alter Date <...2016-08-22> 
--Description<...门诊结帐过程(分单结帐)>
--2011.07.20,HHY,会员非帐户支付时也产生积分
--2011.08.25,HHY,会员积分支付
--2011.08.30,8065,CRM客户服务日志
--2011.09.19,HHY,积分余额不足时终止结算
--2011.09.20,HHY,卡消费时自动升级；伊莱美药品、材料和检验不送积分
--2012.05.28,8065,挂账类型的支付方式允许录入工作单位
--2012.12.18,8065,如果存在医保结算记录，本地未传入医保支付方式，则不允许结算
--2013.06.17,8065,体检病人结算时更新病人缴费明细表
--2013.07.09,8065,部分退费生成业务通知表结算后更新其状态
--2013.10.18,8065,赠送或者积分类型的支付方式保存时校验是否会员卡
--2013.10.30,8065,处理分单结算模式下减免优惠支付方式的分配(按明细合计分配)
--2013.12.24,8065,会员卡支付方式只校验未设置接口驱动的记账类型
--2013.12.25,8065,前台传入多会员卡支付方式(用其他人卡消费)
--2014.01.21,8065,结账模式的判断排除体检病人
--2014.06.15,8065,修改VCB04长度
--2014.09.16,8065,结账时记录医保本地关联
--2014.11.25,8065,结账时根据参数记录药品摆药单
--2014.12.31,8065,减小锁粒度
--2015.08.18,8065,记录前台输入的支付方式备注
--2016.07.15,8065,会员支付支持多卡及多账号支付方式
--2016.07.20,8065,会员扣费时统一调用扣费过程
--=======================================================================================================
CREATE PROC [HORate_DVAK01_Update]  @XML ntext
AS 
DECLARE @iDOM int, @iret int
Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML 
if @iret > 0
BEGIN
   exec sp_xml_removedocument @iDOM	
   RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
   return @iret
END 
--获取操作员基本信息
DECLARE @EmpId int,
        @EmpNo varchar(20),
        @EmpName varchar(20),
        @HostIp varchar(256),
        @HostName varchar(256),
        @DeptId int,
        @SCA01 int,
        @SCH01 int,
        @DeptNo varchar(20),
        @DeptName varchar(20),
        @HostMac varchar(256),
        @FileVer varchar(20),
        @Event varchar(4086),
        @TotalVBL numeric(18 ,4),
        @SCH12 varchar(1024),
        @VAK13 datetime ,
        @VAA07 int ,
        @CurDate datetime   
        
SELECT  @EmpId = EmpId , 
        @EmpNo = EmpNo ,
        @EmpName = EmpName ,
        @DeptId = DeptId ,
        @DeptNo = DeptNo ,
        @DeptName = DeptName ,
        @HostIp = HostIp ,
        @HostName = HostName ,
        @HostMac = HostMac ,
        @FileVer = FileVer         
  FROM OpenXml(@iDOM,'/Root/OperInfo',8) 
  WITH 
  (  
  	    EmpId int , 
        EmpNo varchar(20) ,
        EmpName varchar(20) ,
        DeptId int ,
        DeptNo varchar(20) ,
        DeptName varchar(20) ,
        HostIp varchar(256) ,
        HostName varchar(256) ,
        HostMac varchar(256) ,
        FileVer varchar(20) 
  )
  --如果发现当前的版本号低于指定的Dll文件版本号，提示客户升级
  Declare @ISSAME int ,
          @Description varchar(1024)          
  select @ISSAME = ISSAME ,@Description = Description from dbo.[FileVerCheck](@FileVer , 'HORate.dll')
  If @ISSAME = 0
  BEGIN
     exec sp_xml_removedocument @iDOM
     RAISERROR(@Description, 16, 1) with nowait
     RETURN 2
  END
  --病人信息
  SELECT * INTO #TmpVAA1
  from OpenXml(@iDOM,'/Root/VAA/Ie',8)
  WITH
  (
     	VAA01 int ,
        VAA07 int ,
        ACF01 int ,
        VAA03 varchar(20) ,
        VAK07 numeric(18,4) ,
        VAK08 numeric(18,4) ,
        VAK09 numeric(18,4) ,
        VAK10 numeric(18,4) ,
        EmpId int ,
        EmpNo varchar(20) ,
        EmpName varchar(20) ,
        HostName varchar(128) ,
        HostIP varchar(32) ,
        WorkName varchar(20) ,
        WorkType smallint ,
        WorkFrom smallint ,
        BCK01A int ,
        BCK01B int ,
        BCE01A int , 
        BCE03A varchar(20) ,
        ABC02 varchar(20) ,
        BCE02A varchar(20) ,
        VAK17 varchar(255) ,
        VBU01 int  
  )	
  If not exists(select * from #tmpVAA1)
  BEGIN
     exec sp_xml_removedocument @iDOM
     RAISERROR('提交错误，病人信息为空', 16, 1) with nowait
     RETURN 2
  END
  
  IF EXISTS(SELECT * FROM #tmpVAA1 where ISNULL(BCK01A , 0) <= 0)
  BEGIN
     EXEC sp_xml_removedocument @iDOM
     RAISERROR('提交错误，病人科室不能为空', 16, 1) with nowait
     RETURN 2
  END
  
  SELECT @VAA07 = VAA07 from #TmpVAA1 
  SET @VAA07 = ISNULL(@VAA07 , 0)
  --如果当前系统参数设置为合并结帐，提示操作员关闭结帐窗口，重新结帐（非体检病人）
  If exists(select * from VAC1 where VAC01 = @VAA07 and VAC09 <> -3)
  BEGIN
     If not exists(select * from SYS_Parameters where ProductID = 100 and ProgramID = 103010 and ParamNo = 93 and Value='1')
     BEGIN
       If exists(select * from SYS_Parameters where ProductID = 100 and ProgramID = 103010 and ParamNo = 6 and Value = 0)
       BEGIN
         exec sp_xml_removedocument @iDOM
         RAISERROR('系统结帐参数已经改变为[合并结帐]模式，请关闭收费窗口，重新进入后结帐.', 16, 1) with nowait
         RETURN 2
       END
     END
  END
  --病人交款信息,除去尾数金额
  SELECT * into #tmpVBL1
  from OpenXml(@iDOM,'/Root/VBL/Ie',8)
  with     
  (    
  	    VBU01 int ,
  	    VBL01 int ,
  	    FAF01 int ,
        VAK01 int ,
        VBL02 varchar(20) ,
        VBL11 varchar(20) ,
        VBL13 numeric(18,4) ,
        VBL14 varchar(32) ,
        VBL15 varchar(2) ,
        BAQ03 varchar(64)    
   )   
   --多个会员卡消费情况
   select IDENTITY(int,1,1) AS ID, * into #tmpVCA1
   from OpenXml(@iDOM,'/Root/VCA/Ie',8) 
   with 
   (
    	VBU01 int ,
    	VCB04 varchar(64) ,
    	VCA10 numeric(18 ,4),
    	AMODE int 
   )
  /*==========================================如果存在没有会员帐号的记帐类型的支付方式不允许================================================*/
  If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) = 0 and VBL15 = '05')
  BEGIN
       If not exists(select * from #tmpVCA1 where AMODE = 1)
       BEGIN
         exec sp_xml_removedocument @iDOM
         RAISERROR('提交错误，记帐类型支付方式必须与会员卡关联', 16, 1) with nowait 
         RETURN 2
       END
  END
  --如果存在没有会员帐号的赠送类型的支付方式不允许
  If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) = 0 and ISNULL(VBU01 , 0) = 0 and VBL15 = '42' )
  BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，赠送类型支付方式必须与会员卡关联', 16, 1) with nowait 
       RETURN 2
  END
  --如果存在没有会员帐号的积分类型的支付方式不允许
  If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) = 0 and ISNULL(VBU01 , 0) = 0 and VBL15 = '43')
  BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，积分类型支付方式必须与会员卡关联', 16, 1) with nowait 
       RETURN 2
  END
  
  If exists(select * from #TmpVAA1 where VAK07 > 0) 
  BEGIN
     IF not exists(select * from #tmpVBL1)
     BEGIN
        exec sp_xml_removedocument @iDOM
        RAISERROR('提交错误，病人交款信息为空', 16, 1) with nowait
        RETURN 2
     END
  END
  select @TotalVBL = SUM(VBL13) from #tmpVBL1 
 
    --存在会员卡接口支付方式，未传入卡号消费的情况报错
   If exists(select * from #tmpVBL1 a, BBP1 b where a.VBL14 = b.BBP02 and ISNULL(BBP09 ,0) = 1 and a.VBL15='05')
   BEGIN
     If not exists(select * from #tmpVCA1 where AMODE = 2)
     BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，记帐类型接口类型支付方式未传入待扣费会员卡信息。', 16, 1) with nowait 
       RETURN 2
     END
   END
  /*=====================================================================================================================================*/
  --病人收费单据
  select IDENTITY(int,1,1) AS ID, * into #tmpVAI1
  from OpenXml(@iDOM,'/Root/VAI/Ie',8) 
  with 
  (
    	 VAI00 varchar(38) ,
         VAI01 int ,     
         VAI04 varchar(20) ,
         VAI05 varchar(1024) ,
         Enabled smallint ,
         OldMoney numeric(18,4) ,
         SumMoney numeric(18,4) ,
         YbMoney numeric(18,4) ,
         RebateMoney numeric(18,4) ,--减免优惠金额
         VAI17 smallint
   )	
   
  If not exists(select * from #tmpVAI1)
  BEGIN
        exec sp_xml_removedocument @iDOM
        RAISERROR('提交错误，病人单据信息为空', 16, 1) with nowait
        RETURN 2
  END
  --如果存在单据金额小于0，则不让结帐
  If not exists(select * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 33 and Value = 1)
  BEGIN
     If exists(select * from #tmpVAI1 where OldMoney < 0 and Enabled = 0)
     BEGIN
        exec sp_xml_removedocument @iDOM
        RAISERROR('提交错误，存在单据金额为负数的数据.', 16, 1) with nowait
        RETURN 2
     END
  END
   --如果传入后台的单据费用中存在VAI01 <- 0，抛错
   If exists(select * from #tmpVAI1 where VAI01 <= 0)
   BEGIN
        exec sp_xml_removedocument @iDOM
        RAISERROR('提交错误，费用单据信息不正确.', 16, 1) with nowait
        RETURN 2
   END
  --病人收费记录信息
  select IDENTITY(int,1,1) AS ID, * into #tmpVAJ1
  from OpenXml(@iDOM,'/Root/VAJ/Ie',8) 
  with 
  (
    	 VAI00 varchar(38) ,
         VAJ01 int ,
         ROWNR int ,
         VAI01 int ,
         VAK01 int ,
         BBY01 int ,
         BDN01 varchar(2) ,
         VAJ25 numeric(18,4) ,
         VAJ30 numeric(9,4) ,
         VAJ31 numeric(9,4) ,
         VAJ59 numeric(18,6) ,
         VAJ32 numeric(18,4) ,
         VAJ33 numeric(18,4) ,
         VAJ34 numeric(18,4) ,
         VAJ35 varchar(20) ,
         VAJ36 numeric(18,4) ,
         VAJ37 numeric(18,4) ,
         VAJ38 numeric(18,4) ,
         VAJ39 smallint ,
         VAJ61 numeric(18,4) ,
         BCK01D int 
    )	
   
   If not exists(select * from #tmpVAJ1)
   BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，病人收费记录信息为空', 16, 1) with nowait
       RETURN 2
   END
    
   If not exists(select * from #tmpVAI1 where Enabled = 0)
   BEGIN
       exec sp_xml_removedocument @iDOM
       --RAISERROR('提交错误，病人收费记录信息为空', 16, 1) with nowait
       RETURN 2
   END  
   --如果存在没有明细的单据信息则返回
   If exists(select * from #tmpVAI1 where VAI00 not in(select VAI00 from #tmpVAJ1)) 
   BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，存在没有明细的单据信息，请重新检索收费信息.', 16, 1) with nowait
       RETURN 2
   END
   
   --医保结算信息
   select IDENTITY(int,1,1) AS ID, * into #tmpIAS1
   from OpenXml(@iDOM,'/Root/IAS/Ie',8) 
   with 
   (    
        IBL01 int,
    	IAS01 int,
    	VAI01 int
    )
   --如果存在医保结算记录，本地未传入医保支付方式，则不允许结算
   If exists(select * from #tmpIAS1 a 
               join IAS1 b on b.IAS01 = a.IAS01 
             where ISNULL(b.IAS45 ,0) + ISNULL(b.IAS46 , 0) > 0
            ) 
   BEGIN
     If not exists(select * from #tmpVBL1 where VBL15 = '03')
     BEGIN
        exec sp_xml_removedocument @iDOM
        RAISERROR('提交错误，未传入已经结算的医保记帐信息.', 16, 1) with nowait
        RETURN 2
     END
   END 
  
   Declare @i int ,
           @j int ,
           @m int ,
           @VAA01 int ,
           @VAK01 int ,
           @Count int ,
           @CountI int ,
           @Value int ,           
           @VAI01 int ,
           @Result int ,
           @VAI01A int ,
           @VBL01 int ,
           @ErrId int ,
           @BCK01A int ,
           @BCK01B int ,
           @Enabled int ,           
           @IsHyType int ,               
           @BCE01A int ,
           @VAK28 int ,
           @BBP06 tinyint ,
           @WorkType smallint ,
           @BCE02A varchar(20) ,
           @BCE03A varchar(20) ,
           @WorkName varchar(20) ,          
           @VAK04 varchar(20) ,
           @VAI00 varchar(38) ,
           @VBL02 varchar(20) ,
           @VAK07 numeric(18,4) ,
           @VAK08 numeric(18,4) ,
           @VAK09 numeric(18,4) ,
           @VAK10 numeric(18,4) ,
           @VCA10 numeric(18,4) ,
           @VCA10A numeric(18,4) ,
           @SumMoney numeric(18,4) ,
           @CurSumMoney numeric(18,4) ,
           @VBL13 numeric(18,4) ,
           @CurVBL13 numeric(18,4) ,
           @VAK17 varchar(255) ,
           @VAK21 numeric(18,4) ,
           @VAK23 numeric(18,4) ,
           @VAK24 numeric(18,4) ,
           @YbBBP02 varchar(32) ,
           @YbMoney numeric(18,4) ,
           @WsMoney numeric(18,4) ,
           @OldMoney numeric(18,4) ,
           @WsBBP02 varchar(32) ,
           @SumVCA10 numeric(18,4) ,
           @SumAAO05 numeric(18,4) ,   
           @SumAAO42 numeric(18,4) ,      
           @SumVAJ38 numeric(18,4) ,
           @CashMoney numeric(18 ,4) ,
           @RebateMoney numeric(18,4) ,
           @RebateSumMoney numeric(18,4) ,
           @RebateBBP02 varchar(32) ,
           @VBL11 varchar(20)             
    
  select @VAA01 = VAA01,@VAA07 = VAA07 ,@WorkName = WorkName,@WorkType = WorkType,
         @BCK01A = BCK01A ,@BCK01B = BCK01B,@BCE01A = BCE01A,@BCE02A = BCE02A,@BCE03A = BCE03A,@VAK17 = VAK17 , @VAK07 = VAK07 ,
         @VAK09 = VAK09 , @VAK10 = VAK10   
  from #tmpVAA1 
  
  If ISNULL(@EmpId , 0) = 0
  BEGIN
    SELECT @EmpId = EmpId , @EmpNo = EmpNo , @EmpName = EmpName from #tmpVAA1 
  END 
  select @IsHyType = dbo.GetWorkingMode()
  /*======================================如果是会员模式，并且存在多个类型为“记帐”的支付方式，不允许========================================*/
  If (dbo.GetWorkingMode() > 0)  
  BEGIN
     If exists(select AAO01 from BBP1 where AAO01 = '05' and ISNULL(BBP09 , 0) = 0 group by AAO01 having COUNT(AAO01)  > 1) 
     BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，会员模式下只允许一种类型为“记帐”的支付方式', 16, 1) with nowait
       RETURN 2
     END
  END
  /*===================================================判断支付总额约结帐金额是否相等========================================================*/
  If @VAK07 <> @TotalVBL 
  BEGIN
     RAISERROR('提交错误，支付方式总额与结帐金额不等，请检查.', 16, 1)  WITH NOWAIT
     Return 1
  END
  /*=======================================如果存在医保结帐，系统存在1种以上支付方式则不让保存数据============================================*/
  If Exists(SELECT * from #tmpVBL1 where VBL15 = '03')
  BEGIN
    If exists(select count(1) from BBP1 where AAO01 = '03' group by AAO01 having Count(AAO01) > 1)
    BEGIN
       RAISERROR('提交错误，系统不能同时存在1种以上医保支付方式.', 16, 1) with nowait
       RETURN 2 
    END    
  END
  /*===========================================如果病人ID不存在，或者病人就诊ID不存在不让结帐==================================================*/
  If (isnull(@VAA01 , 0) <=0 ) or (isnull(@VAA07 , 0) < =0)
  BEGIN
     RAISERROR('提交错误，病人信息不能为空.', 16, 1) with nowait
     RETURN 2
  END    
  /*==============================================判断如果明细中有已经结账的信息，则退出=======================================================*/
  IF EXISTS(SELECT * FROM #tmpVAI1 a JOIN VAI1 b ON a.VAI01 = b.VAI01 WHERE a.Enabled = 0 AND b.VAI16 = 2 )
  BEGIN 
     RAISERROR('待结账信息中存在已结账单据，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1 
  END     
  /*==================================================判断是否是否等于现金支付+找零金额=========================================================*/
  SELECT @CashMoney = SUM(ISNULL(VBL13 , 0)) from #tmpVBL1 where VBL15 ='01'
  SET @CashMoney = ISNULL(@CashMoney , 0)
  
  If (@VAK09 > 0 ) and (ABS(@CashMoney) > 0)
  BEGIN
    IF @VAK09 <> @CashMoney + @VAK10 
    BEGIN
       RAISERROR('实收金额不等于现金支付加找零金额，请检查.', 16, 1) with nowait
       RETURN 2
    END
  END
  /*========================================================创建结账临时表======================================================================*/
  Create Table #tmpVAK1 
  (
     ID int IDENTITY(1,1) not null,
     VAK01 int,
     VAI01 int,
     VAK04 varchar(20),
     VAK07 numeric(18,4),
     VAK08 numeric(18,4),
     VAK17 varchar(255),
     VAK21 numeric(18,4),
     VAK23 numeric(18,4),
     VAK24 numeric(18,4)
  )
  
  --更新明细表单据ID
  Update a
    Set a.VAI01 = b.VAI01 
  from #tmpVAJ1 a
    join #tmpVAI1 b on a.VAI00 = b.VAI00  
  
  --生成病人交款方式
  SELECT * INTO #tmpNewVBL1 from #tmpVBL1 where VBL01 = -10 
  
  SET @BBP06 = 0 
  SELECT @YbBBP02 = VBL14 from #tmpVBL1 where VBL15 ='03'       
  SELECT @BBP06 = isnull(BBP06 , 0) from BBP1 where BBP02 = @YbBBP02  
  SELECT @WsBBP02 = VBL14 from #tmpVBL1 where VBL15 ='98'
  SELECT @RebateBBP02 = VBL14 from #tmpVBL1 where VBL15 ='04'
  SELECT @RebateSumMoney = SUM(VBL13) from #tmpVBL1 where VBL15 ='04'
  --针对多个结帐单据尾数金额合计为0的情况
  If ISNULL(@WsBBP02 , '') = ''
  BEGIN
    select @WsBBP02 = a.BBP02 
    from BBP1 a
      join BBR1 b on b.BBP01 = a.BBP01 
    where b.AAN01 = '02' and a.AAO01 = '98'
  END
  --如果支付方式中医保金额与单据中医保支付总额不相同，则本地不让结帐  
  If (@BBP06 = 1) and exists(select * from #tmpVBL1 where VBL15 = '03') 
  BEGIN
     SELECT @YbMoney = SUM(ISNULL(YbMoney , 0)) from #tmpVAI1 where Enabled = 0
     SET @YbMoney = ISNULL(@YbMoney , 0)
     If exists(select * from #tmpVBL1 where VBL15 = '03' and VBL13 <> @YbMoney)
     BEGIN
       RAISERROR('支付方式医保金额与单据中医保支付总额不相同，请联系医保开发工程师.', 16, 1) with nowait
       RETURN 2 
     END 
  END  
  
  --可以自动分配的支付方式临时表(除医保和系统减免及尾数类型的支付方式)
  SELECT IDENTITY(int,1,1) AS ID, * INTO #tmpVBL2 
     from #tmpVBL1 
  where (VBL15 <> '03' or (VBL15 = '03' and @BBP06 = 0)) and (VBL15 <> '98') and (VBL15 <> '04')
   --如果没有结账备注信息，则写入默认的备注信息 
  SET @VAK17 = '结账方式：[分单结账] ' + ISNULL(@VAK17 , '')
  IF isnull(@VAK17,'') = ''   
  BEGIN
    SET @VAK17 = @WorkName 
  END
  /*===================================================================生成结账、病人交款、明细临时表=============================================================*/         
  SELECT @i = 0 , @Count = count(1) from #tmpVAI1
  WHILE @i < @Count 
  BEGIN
     SELECT @VAI01 = VAI01, @VAI00 = VAI00 , @Enabled = Enabled , @VAK08 = SumMoney, @SumMoney = SumMoney , @YbMoney = ISNULL(YbMoney,0),
            @WsMoney = OldMoney - SumMoney - ISNULL(YbMoney ,0) , @OldMoney = ISNULL(OldMoney ,0) 
     from #tmpVAI1 
     where [ID] = @i + 1 
     
     SELECT @VAK07 = SUM(VAJ38) , @RebateMoney = SUM(VAJ38 - VAJ61) from #tmpVAJ1 where VAI00 = @VAI00    
     SET @RebateMoney = ISNULL(@RebateMoney ,0) 
     
     SELECT @m = 0,@CountI = count(1) from #tmpVBL2
     IF @Enabled = 0 --要结账的单据
     BEGIN     	
     	 Exec @ErrId = Core_NewIDEx 'VAK1' , 'VAK01' , @VAK01 out  
     	 
     	 IF @ErrId > 0 
     	 BEGIN
     	   Return 1 
     	 END
     	 
     	 SET @VAK04 = '000000000'
         SET @VAK04 = @VAK04 + CAST(@VAK01 AS VARCHAR)
         SET @VAK04 = 'K' +  SUBSTRING(@VAK04, Len(@VAK04) - 9, 10)
         SELECT @VAK21 = SUM(VAJ36)  , @VAK23 = SUM(VAJ38) ,@SumVAJ38 = SUM(VAJ38) ,@VAK24 = SUM(VAJ61) 
         from VAJ1 with(nolock) 
         where VAI01 = @VAI01 and VAJ05 = 1 
         --如果判断前台传过来的金额与后台不符，则提示
         If @OldMoney <> @SumVAJ38
         BEGIN        
            RAISERROR('提交错误，单据金额与前台金额不符，请重新检索...', 16, 1) with nowait
            RETURN 2
         END              
         --判断如果尾数处理大于1,则退出
         If ABS(@VAK07 - @VAK08 - @YbMoney) >= 1
         BEGIN
     	    RAISERROR('提交错误，尾数处理金额不能大于1.', 16, 1) with nowait
            RETURN 2
         END
         INSERT INTO #tmpVAK1(VAK01 , VAI01 ,VAK04 , VAK07 , VAK08 , VAK17 , VAK21 , VAK23 , VAK24)
         SELECT @VAK01 ,@VAI01 , @VAK04 ,@VAK07 - @VAK08 - @YbMoney ,@VAK08 + @YbMoney , @VAK17 , @VAK21 , @VAK23 , @VAK24 
         
         SET @SumMoney = @SumMoney - @RebateMoney 
         --循环写入病人交款记录
         WHILE @m < @CountI 
         BEGIN
             --SET @SumMoney = @SumMoney - @RebateMoney 
             SET @CurSumMoney = @SumMoney
             SELECT @VBL11 = VBL11 , @CurVBL13= VBL13,@VBL13 = VBL13 from #tmpVBL2 where [ID] = @m + 1
             --当前支付方式分配完毕继续下次循环
             If @VBL13 = 0 
             BEGIN
                 SET @m= @m + 1 
                 CONTINUE  
             END
             Set @SumMoney = @SumMoney - @VBL13 
             Set @VBL13 =@VBL13 - @CurSumMoney  
             Set @CurSumMoney = @CurSumMoney - @CurVBL13 
             If @VBL13 < 0 set @VBL13 = 0
             --生成病人交款ID及流水号
       	     Exec @ErrId = Core_NewIdEx 'VBL1','VBL01',@Value out
       	     
       	     IF @ErrId > 0 
     	       BEGIN
     	         Return 1 
     	       END
     	 
       	     SET @VBL02 = '000000000'
             SET @VBL02 = @VBL02 + Cast(@Value AS VARCHAR)
             SET @VBL02 = 'L' +  SUBSTRING(@VBL02, Len(@VBL02) - 9, 10)
             --插入数据 
             INSERT INTO #tmpNewVBL1 (VBL01 , VAK01 , VBL02 , VBL11 , VBL13 , VBL14 , VBL15 , VBU01 , FAF01 , BAQ03)
             SELECT @Value , @VAK01 , @VBL02 ,  VBL11 , @CurVBL13 - @VBL13 , VBL14 , VBL15 , VBU01 , FAF01 , BAQ03 from #tmpVBL2 where [ID] = @m + 1                        
                   
             --如果当前支付方式大于本次结账金额
       	     IF @VBL13 > 0  
             BEGIN
                update #tmpVBL2 set VBL13 = @VBL13 where [ID]=@m + 1 
                break 
             END Else  --当前支付方式小于等于本次结账金额
             BEGIN  
               update #tmpVBL2 
                 set VBL13 = 0 
               where [ID] = @m + 1
               set @m = @m + 1
               continue 
             END        	        	
        END
     END     
     --更新明细临时表结账ID
     Update #tmpVAJ1 
       SET VAK01 = @VAK01 
     where VAI01 = @VAI01 
     --如果当前单据有医保记账，保存医保记账方式
     If (isnull(@YbBBP02 ,'') <> '') and (@YbMoney > 0) and (@BBP06 = 1) and (@Enabled = 0)
     BEGIN
     	--生成病人交款ID及流水号
       	Exec @ErrId = Core_NewIdEx 'VBL1','VBL01',@Value out 
       	
       	IF @ErrId > 0 
     	BEGIN
     	   Return 1 
     	END
     	 
       	SET @VBL02 = '000000000'
        SET @VBL02 = @VBL02 + Cast(@Value AS VARCHAR)
        SET @VBL02 = 'L' +  SUBSTRING(@VBL02, Len(@VBL02) - 9, 10)
        --插入数据 
        INSERT INTO #tmpNewVBL1 (VBL01 , VAK01 , VBL02 , VBL11 , VBL13 , VBL14 , VBL15 )
        SELECT @Value , @VAK01 , @VBL02 ,  @VBL11 , @YbMoney , @YbBBP02 , '03'   
     END
     --如果有尾数处理金额，写入尾数处理
     If (Isnull(@WsBBP02,'') <> '') and (ABS(@WsMoney) > 0) and (@Enabled = 0)
     BEGIN        
        --生成病人交款ID及流水号
       	Exec @ErrId = Core_NewIdEx 'VBL1','VBL01',@Value out
       	
       	IF @ErrId > 0 
     	BEGIN
     	   Return 1 
     	END
     	 
       	SET @VBL02 = '000000000'
        SET @VBL02 = @VBL02 + Cast(@Value AS VARCHAR)
        SET @VBL02 = 'L' +  SUBSTRING(@VBL02, Len(@VBL02) - 9, 10)
        --插入数据 
        INSERT INTO #tmpNewVBL1 (VBL01 , VAK01 , VBL02 , VBL11 , VBL13 , VBL14 , VBL15 )
        SELECT @Value , @VAK01 , @VBL02 , @VBL11 , @WsMoney , @WsBBP02 , '98'   
     END
     --如果有减免类型支付金额，保存减免支付方式
      If (Isnull(@RebateBBP02,'') <> '') and (ABS(@RebateMoney) > 0) and (@Enabled = 0)
      BEGIN
        SELECT @VBL11 = VBL11 from #tmpVBL1 where VBL14 = @RebateBBP02 and VBL15 = '04'
        --生成病人交款ID及流水号
       	Exec @ErrId = Core_NewIdEx 'VBL1','VBL01',@Value out
       	
       	IF @ErrId > 0 
     	BEGIN
     	   Return 1 
     	END
     	 
       	SET @VBL02 = '000000000'
        SET @VBL02 = @VBL02 + Cast(@Value AS VARCHAR)
        SET @VBL02 = 'L' +  SUBSTRING(@VBL02, Len(@VBL02) - 9, 10)
        --插入数据 
        INSERT INTO #tmpNewVBL1 (VBL01 , VAK01 , VBL02 , VBL11 , VBL13 , VBL14 , VBL15 )
        SELECT @Value , @VAK01 , @VBL02 , @VBL11 , @RebateMoney, @RebateBBP02  , '04'                
      END
     set @i = @i + 1 
  END 
  /*======================================================如果存在尾数处理，并且金额大于1，提示错误===============================================================*/
   If exists(select * from #tmpNewVBL1 where VBL15 = '98' and ABS(VBL13) > 1)
   BEGIN       
       RAISERROR('提交错误，尾数金额不能大于1，请检查.', 16, 1) with nowait 
       RETURN 2
   END
    
  SELECT top 1 @VAK28 = VAK01 from #tmpVAK1    
  
  SET @CurDate = GETDATE()  	
  
  BEGIN TRAN --开始一个事物
 /*=============================================================保存病人结账信息及交款信息=======================================================================*/        
 --插入结账数据
 INSERT INTO VAK1(VAK01, VAA01, VAA07, VAI04, FAB03, VAK06, VAK07, VAK08, VAK09, VAK10, BCE02A, BCE03A, VAK13, VAK01A, VAK15, VAK16, VAK17, VAK18, 
       VAK19 , VAK20 , VAK21 , VAK23 , VAK24 , BCE01A , VAK28) 
                 
 SELECT VAK01, @VAA01, @VAA07, VAK04, '', 2, VAK07, VAK08, @VAK09, @VAK10, @EmpNo, @EmpName, @CurDate, 0, @CurDate, @CurDate,  VAK17,0,
      1 , 0 , VAK21 , VAK23 , VAK24 ,@EmpId , @VAK28 from #tmpVAK1
 
 IF @@ERROR > 0 --如果发生错误则回滚数据
 BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN 
    RAISERROR('写入病人结帐单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
    Return 1
 END
 --插入病人缴款数据
 INSERT INTO VBL1(VBL01, VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, VBL11 ,VBL12, VBL13 , VBL14, VBL15, VAK01, VBL17, VBL18, 
                 VBL19, VBL01A, BCE01, BCE02, BCE03, VBL24, VBL25, VBL26, VBL27 , VBU01 , FAF01 ,BAQ03 )
                              
 Select VBL01, VBL02 ,'', 3, 0, @VAA01, @VAA07, @DeptId, VBL11 , @WorkName, VBL13, VBL14, VBL15, VAK01, 0, @CurDate, 
                @CurDate, 0, @EmpID, @EmpNo, @EmpName, 0, 0, 0, 2 , VBU01 , ISNULL(FAF01 , 0) , BAQ03 from #tmpNewVBL1 
 
 IF @@ERROR > 0 --如果发生错误则回滚数据
 BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RAISERROR('写入病人交款记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
    Return 1
 END 
 
 /*======================================积分消费和现金支付产生积分================================================================================*/ 
 DECLARE @VBU01 int,@tmpVBL01 int
 SELECT @VBU01=VBU01 FROM VAA1 where VAA01 = @VAA01
 IF @VBU01>0
 BEGIN
   DECLARE @Exchange numeric(18,2)
   
   --会员积分支付
   SELECT @Exchange=-SUM(VBL13) FROM #tmpNewVBL1 WHERE VBL15='43'
   SELECT @tmpVBL01=VBL01 FROM #tmpNewVBL1 WHERE VBL15='43'
   IF ISNULL(@Exchange,0)<>0 AND ISNULL(@tmpVBL01,0)>0
   BEGIN
     EXEC @ErrId=Customer_VBU17_DESC @VBU01,13,@VAK01,@tmpVBL01,@Exchange,@EmpName
     IF @ErrId>0 OR @@ERROR<>0 --如果发生错误则回滚数据   
     BEGIN
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('会员客户积分支付时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
     END
   END

   --会员非帐户支付时也产生积分
   IF dbo.GetCRMMode()=2--伊莱美：药品、检验不送积分
   BEGIN
     DECLARE @Exchange2 numeric(18,2)
     --不能送积分的支付方式合计
     SELECT @Exchange=SUM(VBL13) FROM #tmpNewVBL1 WHERE VBL15 NOT IN('01','02','03','05')
     --能送积分的项目合计
     SELECT @Exchange2=SUM(VAJ38) 
     FROM #tmpVAJ1 WHERE BDN01>'4' AND BDN01<>'L'
     
     SET @Exchange=ISNULL(@Exchange2,0)-ISNULL(@Exchange,0)
   END ELSE
     SELECT @Exchange=SUM(VBL13) FROM #tmpNewVBL1 WHERE VBL15 IN('01','02','03','05')
       
   IF ISNULL(@Exchange,0)>0
   BEGIN
     EXEC @ErrId=Customer_VBU17_INC @VBU01,11,0,@VAK01,0,@Exchange,@EmpName
     IF @ErrId>0 OR @@ERROR<>0 --如果发生错误则回滚数据   
     BEGIN
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('会员客户现金交易产生积分时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
     END
   END 
   
   --卡消费时自动升级
   IF ISNULL(@Exchange , 0)>0 
   BEGIN
     EXEC @ErrId=Customer_Upgrade_update @VBU01,@EmpId,@EmpName
     IF @ErrId>0 OR @@ERROR<>0
     BEGIN
       IF @@TRANCOUNT > 0 ROLLBACK TRAN
       RAISERROR('卡消费自动升级时发生错误。',16,1) WITH NOWAIT
       RETURN 1
     END
   END
 END
 /*=========================================================更新收费单据状态============================================================*/
 Update VAC1 with(rowlock)
   SET VAC70 = 1 
 where VAC01 = @VAA07 
 
 If exists(select * from #tmpVAI1 a join VAI1 b with(nolock) on a.VAI01 = b.VAI01  where b.VAI16 = 9 and a.Enabled = 0)
 BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RAISERROR('病人存在已作废的费用单据，请重新检索.，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
    Return 1
 END
 
 Update a
   SET a.VAI16 = 2 , 
       VAI18 = 4
 from VAI1 a with(rowlock)
   join #tmpVAI1 b on a.VAI01 = b.VAI01
 where b.Enabled = 0
 --更新单据结帐ID
 Update a
   SET a.VAK01 = b.VAK01 
 from VAI1 a with(rowlock)
   join #tmpVAK1 b on a.VAI01 = b.VAI01 
 --判断如果传入得明细如果已经作废，则回滚事务
 If Exists(select * from #tmpVAJ1 a join VAJ1  b with(nolock) on a.VAJ01 = b.VAJ01 where b.VAJ05 = 9)
 BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RAISERROR('病人费用明细不存在，请重新检索.，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
    Return 1
 END
 --更新收费明细结账ID、状态
 Update a
   SET a.VAJ04 = 4,
       a.VAJ05 = 2,
       a.VAK01 = b.VAK01 
 from VAJ1 a with(rowlock)
   join #tmpVAJ1 b on a.VAI01 = b.VAI01
   join #tmpVAI1 c on b.VAI01 = c.VAI01
 where c.Enabled = 0 and a.VAJ05 = 1
--更新医保结算表本地结账ID
Update a
  Set a.VAK01 = b.VAK01
from IAS1 a with(rowlock)
  join #tmpVAK1 b on a.VAI01 = b.VAI01 
  join #tmpIAS1 c on a.IAS01 = c.IAS01 
 --插入医保本地关联
 If exists(select * from #tmpIAS1) 
 BEGIN
   DECLARE @IBL01 int 
   SELECT @i =0 , @Count = COUNT(0) from #tmpIAS1 
   Exec Core_NewIDEx 'IBL1' , 'IBL01' , @IBL01 out , @Count 
   
   while @i < @Count 
   BEGIN
     Update #tmpIAS1 
       SET IBL01 = @IBL01 - @Count + @i + 1
     where ID = @i + 1
     SET @i = @i + 1
   END
   
   INSERT INTO IBL1(IBL01 , IAS01 , VAK01)
   SELECT c.IBL01 , a.IAS01 , b.VAK01 
   from IAS1 a 
     join #tmpVAK1 b on a.VAI01 = b.VAI01 
     join #tmpIAS1 c on a.IAS01 = c.IAS01 
     
   IF @@ERROR > 0 --如果发生错误则回滚数据
   BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RAISERROR('写入医保结账记录关联时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
   END     
 END
/*===========================================================会员账户扣除==========================================================================*/
 IF exists(select * from #tmpVCA1) or 
    exists(select * from #tmpVBL1 where VBL15 ='42' and VBU01 > 0 and VBL13 > 0)
 BEGIN
     Exec @ErrId=HORate_VBL1_Charge @Xml , @VAK01 , @VBU01 , 0 , 0 , 6  
   
     IF @ErrId>0 OR @@ERROR<>0
     BEGIN
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('会员卡消费时发生错误。',16,1) WITH NOWAIT
        RETURN 1
     END
 END
/*=======================================================删除业务单据通知中的数据==================================================================*/
If Exists(select * from VBX1 where VAA01 = @VAA01 and VAA07 = @VAA07 and ACF01 = 1 and VBX03 = 1 and VBX04 = 2)
BEGIN
  Update a
    SET VBX22 = 2
  from VBX1 a with(rowlock)
    join #tmpVAI1 b on b.VAI01 = a.VBX19 
  where b.Enabled = 0 and a.VAA01 = @VAA01 and a.VAA07 = @VAA07 and ACF01 = 1 and VBX03 = 1 and VBX04 = 2
END
/*==================================================更新CRM中对应本次就诊ID的预约记录状态为就诊====================================================*/
Update SCF1 with(rowlock)
  SET SCF11 = 5
where VAA07 = @VAA07 and ACF01 = 1
/*========================================================更新体检费用总额表=======================================================================*/
If exists(select * from VAC1 where VAC01 = @VAA07 and VAC09 = -3)
BEGIN
  Declare @VBM01 int 
  Exec HORate_VBM1_Update @VBM01 out , @VAA01 , 4 , 0 , @VAK07 , 0  ,@VAA07 
END
/*================================================================================================================================================*/
Commit tran 
/*============================================================写入客户服务日志===================================================================*/
SELECT @SCA01 = SCA01 from SCA1 where VAA01 = @VAA01 and SCA37 <> 2
If ISNULL(@SCA01 , 0) > 0 
BEGIN
   SELECT @VAK13 = VAK13 from VAK1 where VAK01 = @VAK01 
   SET @SCH12 = '结帐时间：' + convert(varchar(30) , @VAK13 , 111) + ' 结帐金额：' + CAST(@TotalVBL as varchar(20))  
   Exec @ErrId = HOCRM_SCH1_Update @SCH01 out , @SCA01 , 0 , 'VAK1' , 'VAK01' , 106 , '门诊收费' , @SCH12 , 0 , 0 , @EmpId ,@EmpName 
END
/*==============================================================根据参数写入药品摆药单============================================================*/
If exists(select * from Sys_Parameters where productid =100 and programid = 111001 and paramno = 63 and Value='1')
BEGIN
  If exists(select * from #tmpVAJ1 where BDN01 <= '3')
  BEGIN
    SELECT BCK01D , VAK01 INTO #tmpDSY1 
    from #tmpVAJ1
    where BDN01 <= '3'
    GROUP BY BCK01D ,VAK01
    
    INSERT INTO DSY1(VAA01 , VAA07 , ACF01 ,VAK01 , PNRCP , DFLAG , BCK01)
    SELECT @VAA01 , @VAA07 , 1 , VAK01 , 0 , 0 , BCK01D from #tmpDSY1
  END
END

Select* from  #tmpVAK1 





GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--==============================================================================================================
--Author     <...8065>
--Alter Date <...2016.08.16>
--Description<...病人支付方式处理，主要包括会员部分账户变动、积分等处理>
--Description<...增加校验传入了VCA1无会员支付方式>
--==============================================================================================================
CREATE PROC [HORate_VBL1_Charge] @Xml ntext ,
                               @VAK01 int , 
                               @VBU01 int = 0 ,
                               @VBL01 int = 0 ,
                               @AType int = 0 , --0=收费、1=退费
                               @lWorkFrom int =6 --5=挂号、6=门诊结账、7=住院结账
AS

 DECLARE @iDOM int ,
         @iret int ,
         @EmpId int ,
         @EmpNo varchar(20) ,
         @EmpName varchar(20) ,
         @HostIp varchar(256) ,
         @HostName varchar(256) ,
         @DeptId int ,         
         @DeptNo varchar(20) ,
         @DeptName varchar(20) ,
         @HostMac varchar(256) ,
         @FileVer varchar(20)  
         
 Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML
 if @iret > 0
 BEGIN
    exec sp_xml_removedocument @iDOM	
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
    return @iret
 END   
 SELECT @EmpId = EmpId , 
        @EmpNo = EmpNo ,
        @EmpName = EmpName ,
        @DeptId = DeptId ,
        @DeptNo = DeptNo ,
        @DeptName = DeptName ,
        @HostIp = HostIp ,
        @HostName = HostName ,
        @HostMac = HostMac ,
        @FileVer = FileVer 
  FROM OpenXml(@iDOM,'/Root/OperInfo',8) 
  WITH 
  (  
  	    EmpId int , 
        EmpNo varchar(20) ,
        EmpName varchar(20) ,
        DeptId int ,
        DeptNo varchar(20) ,
        DeptName varchar(20) ,
        HostIp varchar(256) ,
        HostName varchar(256) ,
        HostMac varchar(256) ,
        FileVer varchar(20)
  ) 
  --病人收费单据
  select IDENTITY(int,1,1) AS ID, * into #tmpVAI1Member
  from OpenXml(@iDOM,'/Root/VAI/Ie',8) 
  with 
  (
    	 VAI00 varchar(38) ,
         VAI01 int ,     
         VAI04 varchar(20) ,
         VAI05 varchar(1024) ,
         Enabled smallint ,
         OldMoney numeric(18,4) ,
         SumMoney numeric(18,4) ,
         YbMoney numeric(18,4) ,
         RebateMoney numeric(18,4) ,--减免优惠金额
         VAI17 smallint
   )
   where Enabled = 0   
 --病人交款信息
  select IDENTITY(int,1,1) AS ID, * into #tmpVBL1Member
  from OpenXml(@iDOM,'/Root/VBL/Ie',8)
  with 
  (
        VBL01 int ,
        FAF01 int ,
        VBL02 varchar(20) ,
        VBL11 varchar(20) ,
        VBL13 numeric(18,4) ,
        VBL14 varchar(32) ,
        VBL15 varchar(2) ,
        VBU01 int ,
        BAQ03 varchar(64),
        BBO02 varchAR(64)
   )	
   where VBL15 = '05' or VBL15 = '42'
   --多个会员卡消费情况
   select IDENTITY(int,1,1) AS ID, * into #tmpVCA1Member
   from OpenXml(@iDOM,'/Root/VCA/Ie',8) 
   with 
   (
    	VBU01 int ,
    	VCB04 varchar(64) ,
    	VCA10 numeric(18 ,4),
    	AMODE int 
   )     
   
   If exists(select * from #tmpVCA1Member)  
   BEGIN
     If not exists(select * from #tmpVBL1Member where VBL15 ='05')
     BEGIN
       RAISERROR('当前会员消费未传入对应的支付方式。' , 16 ,1 ) with nowait
       RETURN 1
     END
   END
   
   Declare @i int ,
           @Count int ,
           @VAA01 int ,
           @VAA07 int ,
           @VAI01 int ,
           @Result int ,
           @VAK28 int ,
           @VAK01A int ,
           @ErrId int ,
           @SumVCA10 numeric(18,4) ,
           @SumAAO42 numeric(18,4)      

  SELECT @VAA01 = VAA01 , @VAA07 = VAA07 ,@VAK28 = VAK28 from VAK1 with(nolock) where VAK01 = @VAK01  
  
  SELECT IDENTITY(int , 1 ,1) as ID ,VBL01 , VBL13 , VBL14 , VBL15 , a.VAK01 INTO #tmpVBL1AAO42 
  from VBL1 a with(nolock)
    join VAK1 b with(nolock) on b.VAK01 = a.VAK01 
  where b.VAK28 = @VAK28 and a.VBL15 = '42'   
  
    
  exec sp_xml_removedocument @iDOM
  
  BEGIN TRAN 
  /***********************************************************退还自动扣费钱到会员账户中*************************************************************/
  If ISNULL(@VBU01 , 0) > 0
  BEGIN
    SELECT @i = 0 , @Count = COUNT(*) from #tmpVAI1Member    
    WHILE @i < @Count 
    BEGIN
      SELECT @VAI01 = VAI01 
      from #tmpVAI1Member 
      where ID = @i + 1
      
      SELECT @VAK01A = VAK01 from VAI1 with(nolock) where VAI01 = @VAI01 
      
      SELECT @SumVCA10 = -SUM(VCA10)
      from VCA1 with(nolock)
      where VAI01 = @VAI01  
      
      If ABS(ISNULL(@SumVCA10,0)) > 0
      BEGIN
        Exec HORate_CardCharge_Update @VAA01 , @VAA07 , @VAI01  , 1 , @EmpId , 0 , @EmpNo ,@EmpName , @SumVCA10 , @Result out , 1 , @lWorkFrom , @VAK01A
               
        If @Result = 3 
        BEGIN
          IF @@TRANCOUNT > 0 ROLLBACK TRAN 
          RAISERROR('一卡通病人退费时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
          Return 1
        END 
      END
      SET @i = @i + 1
    END    
  END   
  /*****************************************************************扣除赠送账户钱*****************************************************************/ 
  If exists(select * from #tmpVBL1AAO42 )
  BEGIN
    SELECT @i =0 , @Count = COUNT(*) from #tmpVBL1AAO42    
    while @i < @Count 
    BEGIN
      SELECT @SumAAO42 = VBL13 , @VAK01A = VAK01 , @VBL01 = VBL01 from #tmpVBL1AAO42 
      where ID = @i + 1
      
      Exec HORate_CardCharge_Update @VAA01 , @VAA07 , 0 , 1 , @EmpId , 0 , @EmpNo ,@EmpName , 0  , @Result out , 0 , @lWorkFrom , @VAK01 , @VBL01 , @SumAAO42 
                
      If @Result = 2
      BEGIN
         IF @@TRANCOUNT > 0 ROLLBACK TRAN 
         Return 1
      END
                
      If @Result = 3 
      BEGIN
         IF @@TRANCOUNT > 0 ROLLBACK TRAN
         RAISERROR('一卡通病人扣费时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
         Return 1
      END 
      
      SET @i = @i + 1
    END
  END 
  /**************************************************************扣除基本账户钱************************************************************************/
   If exists(select * from #tmpVCA1Member)
  BEGIN    
    SELECT @VBL01 = ISNULL(VBL01 , 0) 
    from VBL1 a with(nolock) 
      join BBP1 b on b.BBP02 = a.VBL14  
    WHERE VAK01 = @VAK01 and VBL15 = '05' 
    
    Exec @ErrId = HORate_CardUpdate_Interface @Xml , @VAA01 , @VAA07 , 1 , @EmpId , @EmpName ,@Result out ,0 , 0 ,@lWorkFrom,@VAK01 , @VBL01 
   
    If (@Result = 3) or (@ErrId > 0)
    BEGIN
       IF @@TRANCOUNT > 0 ROLLBACK TRAN
       RAISERROR('使用会员卡扣费时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
       Return 1
    END
  END 
  /****************************************************************************************************************************************************/                          
  COMMIT TRAN 
  IF Object_id('tempdb..#tmpVCA1Member') IS NOT NULL DROP TABLE #tmpVCA1Member 
  IF Object_id('tempdb..#tmpVBL1AAO42') IS NOT NULL DROP TABLE #tmpVBL1AAO42 
  IF Object_id('tempdb..#tmpVAI1Member') IS NOT NULL DROP TABLE #tmpVAI1Member 
GO