SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--===========================================================================================
--Author     <...Fyq>
--Alter Date <...2015-11-24>
--Description<...住院结帐存储过程(分表后)>
--2014.09.16 <...医保病人结账时写入医保结账关联表>
--2014.11.11 <...增加会员卡多卡消费>
--2014.12.16 <...增加会员扣费校验>
--2014.12.31 <...减小锁粒度>
--===========================================================================================
ALTER  PROC [HORate_InHosCheckOut_EntryEx] 
                 @XML ntext ,
                 @Status int, 
                 @VAK01 int out , 
                 @VAK04 varchar(20) out 
AS 
--@Status 3=中途结帐、4=出院结帐
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)
        
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
  /*Root/VAA1病人信息*/
  SELECT * INTO #TmpVAA1
  from OpenXml(@iDOM,'/Root/VAA/Ie',8)
  WITH
  (
     	 VAA01 int
       , VAA07 int
       , ACF01 int
       , VAK07 numeric(18,4)
       , VAK08 numeric(18,4)
       , VAK09 numeric(18,4)
       , VAK10 numeric(18,4)
       , BDP02 varchar(50)
       , ABC02 varchar(20)
       , EmpId int
       , EmpNo varchar(20)
       , EmpName varchar(20)
       , HostName varchar(128)
       , HostIP varchar(32) 
       , FAB03 varchar(20)
       , BeginDate datetime
       , EndDate datetime 
       , VAK17 varchar(255)
       , VAK20 numeric(18,4)
       , JzDragFin varchar(1)
       , IsDecimal tinyint
       , IsHaveInsure tinyint
       , VBU01 int 
       , CanSelectFee tinyint 
  )	
  
  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 VAK07 < 0)
  BEGIN
      exec sp_xml_removedocument @iDOM
      RAISERROR('提交错误，结帐总额不能为负数.', 16, 1) with nowait 
      RETURN 2 
  END
  /*Root/VBL1/病人预交款记录*/
  select IDENTITY(int,1,1) AS ID, * into #tmpVBL1
  from OpenXml(@iDOM,'/Root/VBL1/Ie',8)
  with 
   (
   	    VBL01 int --预交款ID
       ,VBL13 numeric(18,4)--金额
       ,VBL14 varchar(32)
       ,VBL15 varchar(2)
       ,VBL24 numeric(18,4)--冲预交金额
   )	
   /*Root/VBL2/病人交款结算记录*/
   select IDENTITY(int,1,1) AS ID, * into #tmpVBL2
   from OpenXml(@iDOM,'/Root/VBL2/Ie',8)
   with 
   (   
       VBU01 int 
      ,FAF01 int 
      ,VBL13 numeric(18,4)--金额
      ,VBL11 varchar(20)
      ,VBL14 varchar(32)--支付方式
      ,VBL15 varchar(2)--支付类型
      ,BAQ03 varchar(128)
   )	
   --如果存在没有会员帐号的记帐类型的支付方式不允许
   If exists(select * from #tmpVBL2 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0 and ISNULL(VBU01 , 0) = 0 and VBL15 = '05')
   BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，记帐类型支付方式必须与会员卡关联', 16, 1) with nowait 
       RETURN 2
   END
  --如果存在没有会员帐号的赠送类型的支付方式不允许
  If exists(select * from #tmpVBL2 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 #tmpVBL2 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
   /*Root/VAJ1/病人费用明细*/ 
  select IDENTITY(int,1,1) AS ID, * into #tmpVAJ1
  from OpenXml(@iDOM,'/Root/VAJ/Ie',8) 
  with 
    (
       VAJ01 int--收费记录ID
       ,VAI01 int--单据ID
       ,VAJ36 numeric(18,4)
       ,VAJ37 numeric(18,4)
       ,VAJ38 numeric(18,4)
       ,VAJ61 numeric(18,4) 
       ,BDN01 varchar(2)
    )	
    
   If not exists(select * from #tmpVAJ1)
   BEGIN
   	   exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，病人收费记录信息为空', 16, 1) with nowait 
       RETURN 2
  END 
   --多个会员卡消费情况
   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)
   )
   --存在会员卡接口支付方式，未传入卡号消费的情况报错
   If exists(select * from #tmpVBL2 a, BBP1 b where a.VBL14 = b.BBP02 and ISNULL(BBP09 ,0) = 1 and a.VBL15 ='05')
   BEGIN
     If not exists(select * from #tmpVCA1)
     BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，记帐类型接口类型支付方式未传入待扣费会员卡信息。', 16, 1) with nowait 
       RETURN 2
     END
   END
  
  Declare @i int,--循环变量
          @j int,--循环变量
          @Count int,--数据记录数
          @Value int,--临时变量
  	      @VAA01 int,--病人ID
  	      @VAJ01 int,--明细ID
  	      @VAI01 int,--单据ID
  	      @VAJ05 int,--明细状态
  	      @VAA07 int,--就诊ID           
          @VBL01 int,--预交款ID
          @BDP02 varchar(20),--病人类别
          @ABC02 varchar(20),--病人费别
          @VBL02 varchar(20),--收据号，交款
          @VAK17 varchar(255),--备注信息
          @VBL13 numeric(18,4),--预交金额
          @Memo varchar(255),--备注
          @VBL14 varchar(32),--支付方式
          @VBL24 numeric(18,4),--冲交金额
          @VBL15 varchar(2),--支付类型
          @FAB03 varchar(20),--票据号          
          @VAK07 numeric(18,4),--金额、费用总额
          @VAK08 numeric(18,4),--应付、自负金额
          @VAJ61 numeric(18,4),--核算金额
          @VAK09 numeric(18,4),--实付
          @VAK10 numeric(18,4),--找零
          @VAJ38 numeric(18,4),--结账金额
          @VAE11 datetime,--住院日期
          @BeginDate datetime,--开始日期
          @EndDate datetime,--结束日期
          @IsDecimal tinyint,--是否有尾数处理
          @Wsje numeric(18,4),--尾数金额
          @CurVAK07 numeric(18,4),--现有数据库金额
          @TmpVAK07 numeric(18,4),--前台内存表传入金额
          @FundPay numeric(18,4),--社保记账
          @AllVBL numeric(18,4),--支付记录总和
          @IsHaveInsure tinyint ,
          @VBU01 int ,
          @SumAAO05 numeric(18,4) ,
          @SumAAO42 numeric(18,4) ,
          @SumAAO04 numeric(18,4) ,
          @Result int ,
          @ErrId int ,
          @CanSelectFee tinyint
     
  select @VAA01 = VAA01,@VAA07 = VAA07,@EmpId = EmpId ,@EmpNo = EmpNo,@EmpName = EmpName ,@BeginDate = BeginDate,@EndDate = EndDate ,
         @VAK07 = VAK07,@VAK08 = VAK08 ,@VAK09 = VAK09,@VAK10 = VAK10 , @FAB03 = FAB03 , @VAK17 = VAK17 , @IsDecimal = Isnull(IsDecimal,1),
         @IsHaveInsure = isnull(IsHaveInsure,0), @BDP02 = BDP02 , @ABC02 = ABC02 , @VBU01 = VBU01 , @CanSelectFee = CanSelectFee   
  from #TmpVAA1
  --住院预出院病人审核后才能结帐
  If exists(select * from sys_parameters where productid = 9999 and programid = 9999 and paramno = 16 and value = '1') and  @Status =4
  begin
    If not exists(select * from VAE1 where VAE01 = @VAA07 and VAE76 = 1)	
    BEGIN
       exec sp_xml_removedocument @iDOM
       RAISERROR('提交错误，病人尚未审核', 16, 1) with nowait 
       RETURN 2
    END
  end
  
  IF EXISTS(SELECT * from BBP1 where BBP06 = '1' and AAO01 = '03')  
    SELECT @FundPay = sum(Isnull(VBL13 , 0)) from #tmpVBL2 where VBL15 = '03' --or VBL15 = '98'   
  else 
    SET @FundPay = 0 
    
  SELECT @Wsje = Isnull(VBL13 , 0) from #tmpVBL2 where VBL15 = '98'    
  --如果传入的结账金额与后台统计金额不等，则提示重新检索病人
  select @CurVAK07 = SUM(VAJ38) from VAJ2 with(nolock) where VAA07 = @VAA07 and ACF01 =2 and VAK01 = 0
  select @TmpVAK07 = SUM(VAJ38) from #tmpVAJ1 
  select @AllVBL = SUM(VBL13) from #tmpVBL1   
  select @AllVBL = ISNULL(@AllVBL , 0) + ISNULL(SUM(VBL13) , 0) from #tmpVBL2 
  SET @AllVBL = ISNULL(@AllVBL , 0)
  
  if @VAK07 <> @AllVBL 
  BEGIN
    RAISERROR('病人结帐金额不等于支付方式总和,请检查.', 16, 1) with nowait 
    RETURN 1
  END
  
  if @Status = 4           --出院结账
  BEGIN
      If not exists(select * from VAE1 with(nolock) where VAE01 = @VAA07 and (VAE44 = 4 or VAE04>0))
      BEGIN
          RAISERROR('病人不是预出院状态,不能进行结帐操作.', 16, 1) with nowait 
          RETURN 1
      END
      
      If @VAK07 <> @CurVAK07
      BEGIN
          RAISERROR('病人费用已经发生变化,请重新检索.', 16, 1) with nowait 
          RETURN 1
      END 
      
      if @CurVAK07 <> @TmpVAK07 
      BEGIN
          RAISERROR('前台传入费用合计与后台合计不一致，请再次检索病人重试.', 16, 1) with nowait 
          RETURN 1
      END 
  END else if @Status = 3 --中途结算 
  BEGIN
     --SELECT @CurVAK07 = SUM(VAJ38) from #tmpVAJ1 
    
     If @VAK07 <> @TmpVAK07
     BEGIN
        RAISERROR('病人费用总额与明细合计不等,系统不允许结算.', 16, 1) with nowait 
        RETURN 1
     END  
  END
  --如果病人入院日期大于当前日期，提示错误  
  select @VAE11 =VAE11 from VAE1 with(nolock) where VAE01= @VAA07         
  if @VAE11 > getdate()
  BEGIN
  	  RAISERROR('结账失败，病人入院日期大于当前日期,请检查.', 16, 1) with nowait 
      RETURN 1
  END
  --如果参与后台结帐的预交款已经退掉，提示重新检索
  If EXISTS(select * from #tmpVBL1 a join VBL1 b with(nolock) on b.VBL01 = a.VBL01 and b.VBL05 = 1)
  BEGIN
    RAISERROR('参与结帐的预交款中存在退费数据,请重新检索.', 16, 1) with nowait 
    RETURN 1
  END
  --移除XML文档
  exec sp_xml_removedocument @iDOM  
  If @status= 3 
     set @Memo ='中途结帐' + @VAK17
  else If @Status= 4
  begin
     set @Memo ='住院结帐' + @VAK17
     select @BeginDate = MIN(VAJ47) from VAJ2 with(nolock) where VAA01 = @VAA01 and VAA07 = @VAA07 and ACF01 = 2 and VAK01 = 0
  end
  
  Declare @VAK21 numeric(18,4),
          @VAK23 numeric(18,4),
          @VAK24 numeric(18,4)
          
  select @VAK21 = sum(VAJ36) from #tmpVAJ1 
  select @VAK23 = sum(VAJ38) from #tmpVAJ1
  select @VAK24 = sum(VAJ61) from #tmpVAJ1
  SELECT @SumAAO04 = sum(Isnull(VBL13 , 0)) from #tmpVBL2 where VBL15 = '04'
  SET @SumAAO04 = isnull(@SumAAO04 , 0) 
  If ABS(@SumAAO04) > 0 
  BEGIN
    If @SumAAO04 <> (@VAK23 - @VAK24)
    BEGIN
      RAISERROR('减免支付方式与明细减免优惠汇总不等，请检查.', 16, 1) with nowait 
      RETURN 1
    END
  END
  --开始一个事物   
  BEGIN TRAN 
  /********************************************************生成结帐记录*******************************************************/
  Exec @ErrId = Core_NewIDEx 'VAK1','VAK01',@VAK01 out
  
  If @ErrId > 0 
  BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    Return 1
  END
  
  SET @VAK04 = '0000000000'
  SET @VAK04 = @VAK04 + Cast(@VAK01 AS VARCHAR)
  SET @VAK04 = 'K' +  SUBSTRING(@VAK04, Len(@VAK04) - 9, 10)
       
  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 , @FAB03 , @Status , Isnull(@Wsje ,0) , VAK08 + Isnull(@FundPay , 0), VAK09, VAK10, @EmpNo, @EmpName, getdate(),0,@Begindate,
        @EndDate, @Memo , 0 , 1 ,VAK20, @VAK21 ,@VAK23 ,@VAK24 , @EmpId , @VAK01 from #TmpVAA1
       
  IF @@ERROR > 0 --如果发生错误则回滚数据 
  BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RAISERROR('写入病人结帐单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
  END
  --更新医保结算表本地结账ID
  If @IsHaveInsure =1 
  begin
    SELECT 0 as IBL01 ,IDENTITY(int , 1 ,1) as ID ,a.IAS01 INTO #tmpIAS1 
    from IAS1 a with(nolock)
       join IAR1 b with(nolock) on a.IAR01 = b.IAR01 
    where b.VAA07 = @VAA07 and a.ACF01 =2 and b.ACF01 =2 and isnull(a.VAK01,0) =0 and a.IAS31 = 1 
    
     Update a 
        set a.VAK01 = @VAK01 
     from IAS1 a with(rowlock)
       join IAR1 b with(nolock) on a.IAR01 = b.IAR01 
     where b.VAA07 = @VAA07 and a.ACF01 =2 and b.ACF01 =2 and isnull(a.VAK01,0) =0 and a.IAS31 = 1              
    
	SELECT @i =0 , @Count = COUNT(0) from #tmpIAS1 
    DECLARE @IBL01 int 
    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 IBL01 , IAS01 , @VAK01 
    from #tmpIAS1             
     
    IF @@ERROR > 0 --如果发生错误则回滚数据
    BEGIN
      IF @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('写入医保结账记录关联时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
    END     
  end
  /*************************************************************病人预交款记录*****************************************************/
  select @i =0,@Count = count(*) from #tmpVBL1
  while @i < @Count
  BEGIN
      --2009-11-23 更新冲预交方式  	
      select @VBL01 = VBL01 ,@VBL13 = VBL13 ,@VBL24 = VBL24 from #tmpVBL1 where [ID]= @i + 1
      select @VBL14 = VBL14 , @VBL15 = VBL15 from VBL1 with(nolock) where VBL01 = @VBL01 
      --2009-12-08 修改暂时没有冲预交概念
      IF isnull(@VBL24,0) > 0 --如果需要冲销这笔预交款，则写上结账ID,并且插入一条冲销记录
      BEGIN
  	     update VBL1 with(rowlock)
  	       set  VBL05 = 0,
  	            --VBL12=@Memo, 
  	            VBL24 = @VBL24 ,
  	            VAK01 = @VAK01, 
  	            VBL26= VBL13 - isnull(@VBL24,0) 
  	     where VBL01 = @VBL01
      END           
             
      IF @@ERROR > 0 --如果发生错误则回滚数据
      BEGIN
   	     IF @@TRANCOUNT > 0 ROLLBACK TRAN
         RAISERROR('更新病人预交款记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
         Return 1
      END
      set @i = @i + 1
  END
  /***************************************************病人交款记录***********************************************************/
  select @i = 0,@Count = count(*) from #tmpVBL2
  while @i < @Count
  BEGIN
      select @VBL14 = VBL14 from #tmpVBL2 where [ID] = @i + 1
      select @VBL15 = AAO01 from BBP1 where BBP02 = @VBL14
      Exec @ErrId = Core_NewIdEx 'VBL1','VBL01',@Value out
      
      If @ErrId > 0 
      BEGIN
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        Return 1
      END
  
      SET @VBL02 = '000000000'
      SET @VBL02 = @VBL02 + Cast(@Value AS VARCHAR)
      SET @VBL02 = 'L' +  SUBSTRING(@VBL02, Len(@VBL02) - 9, 10)
      --插入数据 2009-11-26 修改
      Insert into VBL1(VBL01, VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, VBL11 ,VBL12, VBL13, VBL14, VBL15, VAK01, VBL17, VBL18,
       	              VBL19, VBL01A, BCE01, BCE02, BCE03, VBL24, VBL25, VBL26 , VBL27 , FAF01 , BCK01 , BAQ03)  
       	                       	                  
      Select @Value, @VBL02, @FAB03, 4 , 0, @VAA01, @VAA07, VBL11 , @Memo, VBL13, VBL14, @VBL15, @VAK01, 0,getdate(), 
       	                  getdate(), 0, @EmpID ,@EmpNo, @EmpName, 0, 0, 0 ,2 , FAF01 , @DeptID , BAQ03 from #tmpVBL2 where [ID]=@i+1
       	                  
      IF @@ERROR > 0 --如果发生错误则回滚数据
      BEGIN
   	     IF @@TRANCOUNT > 0 ROLLBACK TRAN
         RAISERROR('写入病人交款记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
         Return 1
      END
      set @i = @i + 1
  END  
  /*===================================================会员卡扣费==============================================================*/
  If (ISNULL(@VBU01 , 0) > 0) and (dbo.GetWorkingMode() > 0)
  BEGIN
    SELECT @SumAAO05 = sum(Isnull(VBL13 , 0)) from #tmpVBL2 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(BBP09,0) = 0 and VBL15 = '05'  
    SELECT @SumAAO42 = sum(Isnull(VBL13 , 0)) from #tmpVBL2 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(BBP09,0) = 0 and VBL15 = '42' and VBU01 > 0 
    
    If (ABS(ISNULL(@SumAAO05 ,0)) > 0) or (ABS(ISNULL(@SumAAO42 , 0)) > 0)
    BEGIN
        --判断病人基本余额是否够扣费
        IF ISNULL(@SumAAO05 ,0) <> 0
        BEGIN
           If not exists(select * from VBU1 with(nolock) where VBU01 = @VBU01 and VBU14 >= Isnull(@SumAAO05 , 0)) 
           BEGIN
     	      IF @@TRANCOUNT > 0 ROLLBACK TRAN
              RAISERROR('病人一卡通余额不足扣费，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
              Return 1
           END
        END
        --判断病人赠送余额是否够扣费
        IF ISNULL(@SumAAO42 ,0) <> 0
        BEGIN
           If not exists(select * from VBU1 with(nolock) where VBU01 = @VBU01 and VBU15 >= Isnull(@SumAAO42 , 0)) 
           BEGIN
     	      IF @@TRANCOUNT > 0 ROLLBACK TRAN
              RAISERROR('病人一卡通余额不足扣费，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
              Return 1
           END
        END
        --从会员卡上扣除用卡支付部分的金额  
        If exists(select * from #tmpVBL2 where VBL15 = '05' or (VBL15 = '42' and VBU01 > 0))
        BEGIN  	
            SELECT @VBL01 = Isnull(VBL01 , 0) from VBL1 with(nolock) where VAK01 = @VAK01 and VBL15 = '05'  
            If ISNULL(@VBL01 , 0) = 0
              SELECT @VBL01 = VBL01 from VBL1 with(nolock) where VAK01 = VAK01 and VBL15 = '42' and VBU01 > 0  
                                                         
            Exec HORate_CardCharge_Update @VAA01 , @VAA07 , 0 , 2 , @EmpId , 0 , @EmpNo ,@EmpName , @SumAAO05 , @Result out , 0 , 7 , @VAK01 , @VBL01 , @SumAAO42
       
            If @Result = 3 
            BEGIN
               IF @@TRANCOUNT > 0 ROLLBACK TRAN
               RAISERROR('一卡通病人扣费时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
               Return 1
            END
        END
      END          
  END
   /*=============================================================其他会员卡扣费===============================================================*/
  If exists(select * from #tmpVCA1)
  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' and ISNULL(b.BBP09 , 0) = 1
    Exec HORate_CardUpdate_Interface @Xml , @VAA01 , @VAA07 , 2 , @EmpId , @EmpName ,@Result out ,0 , 0 ,7,@VAK01 , @VBL01 
    If @Result = 3 
    BEGIN
       IF @@TRANCOUNT > 0 ROLLBACK TRAN
       RAISERROR('使用会员卡扣费时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
       Return 1
    END
  END
  /*======================================积分消费和现金支付产生积分============================================================*/ 
  IF @VBU01>0
  BEGIN
    DECLARE @Exchange numeric(18,2),@tmpVBL01 int

    --会员积分支付
    SELECT @Exchange=-SUM(VBL13) FROM #tmpVBL2 WHERE VBL15='43'
    SELECT @tmpVBL01=VBL01 FROM VBL1 with(nolock) WHERE VAK01=@VAK01 AND VBL15='43'
    IF ISNULL(@Exchange,0)<>0 AND @tmpVBL01>0
    BEGIN
     EXEC @ErrId=Customer_VBU17_DESC @VBU01,23,@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 #tmpVBL1 WHERE VBL15 NOT IN('01','02','03','05')
      SELECT @Exchange=ISNULL(@Exchange,0)+SUM(VBL13) FROM #tmpVBL2 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
    BEGIN
      SELECT @Exchange=SUM(VBL13) FROM #tmpVBL1 WHERE VBL15 IN('01','02','03','05')
      SELECT @Exchange=ISNULL(@Exchange,0)+SUM(VBL13) FROM #tmpVBL2 WHERE VBL15 IN('01','02','03','05')
    END

    IF ISNULL(@Exchange,0)>0
    BEGIN
     EXEC @ErrId=Customer_VBU17_INC @VBU01,21,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  
  /*========================================加入住院结账折扣信息，主要是处理折后金额===========================================*/
   --更新单据状态
   --select IDENTITY(int,1,1) AS ID, * into #tmpVAI1 from (select distinct VAI01 from #tmpVAJ1) a 
    
   Update a 
     set a.VAI16 = 2 ,
         a.VAK01 = @VAK01 
   from VAI2 a with(rowlock)
    join #tmpVAJ1 b on a.VAI01 = b.VAI01 
   where a.VAI16 = 1
   
   --更新明细状态
   UPDATE a 
     SET a.VAK01 = @VAK01,
         a.VAJ38 = b.VAJ38 ,
         a.VAJ61 = b.VAJ61 
   FROM VAJ2 a with(rowlock)
     JOIN #tmpVAJ1 b ON a.VAJ01 = b.VAJ01  
     
   UPDATE a 
     SET a.VAJ05 = 2 
   FROM VAJ2 a with(rowlock)
     JOIN #tmpVAJ1 b ON a.VAJ01 = b.VAJ01
   WHERE a.VAJ05 = 1  
  /*******************************************************出院结帐入库************************************************************/
  If @Status = 4 --出院结帐，找到病人未结帐的单据及明细更新其状态与结帐金额
  BEGIN
     update VAE1 with(rowlock)
       Set VAE44 = 5  
     where VAE01 = @VAA07 
	 
	 update VAE1 with(rowlock)
       Set VAE26 = GETDATE()  
     where VAE01 = @VAA07 and VAE04>0
         
     IF @@ERROR > 0 --如果发生错误则回滚数据
     BEGIN
   	    IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('更新病人登记状态时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
     END
     
     update VAA1 with(rowlock)
       set VAA61 = 3 
     where VAA01 = @VAA01
     
     IF @@ERROR > 0 --如果发生错误则回滚数据
     BEGIN
   	    IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('更新病人信息状态时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
     END
  END
  /*=================================================如果病人类别或者费别改变，更新====================================================*/
  If not exists(select * from VAE1 where VAE01 = @VAA07 and BDP02 = @BDP02)
  BEGIN
    update VAA1 with(rowlock)
      set BDP02 = @BDP02 
    where VAA01 = @VAA01 
    
    update VAE1 with(rowlock) 
      set BDP02 = @BDP02 
    where VAE01 = @VAA07
  END
  
  If not exists(select * from VAE1 where VAE01 = @VAA07 and ABC02 = @ABC02)
  BEGIN
    update VAA1 with(rowlock)
      set BDP02 = @BDP02 
    where VAA01 = @VAA01 
    
    update VAE1 with(rowlock)
      set BDP02 = @BDP02 
    where VAE01 = @VAA07
  END
  /*==========================================================更新销账记录状态===========================================================*/
  If @Status = 4
  BEGIN
     Update VAJ2 with(rowlock)
      set VAK01 = @VAK01 
     where VAJ05 > 2 and VAA07 = @VAA07 and ACF01 = 2 and VAK01 = 0 and VAJ47 between @BeginDate and @EndDate  
  END ELSE If @Status = 3
  BEGIN
     Update VAJ2 with(rowlock)
      set VAK01 = @VAK01 
     where VAJ05 > 2 and VAA07 = @VAA07 and ACF01 = 2 and VAK01 = 0 and VAJ47 between @BeginDate and @EndDate and VAJ65 = 1 
  END
 /*==========================================================更新病人余额表===========================================================*/          
  Declare @VBM01 int ,
          @VBM04 numeric(18,4) ,
          @VBM05 numeric(18,6)
          
  select @VBM04 =isnull(sum(isnull(VBL13,0)),0) from VBL1 with(nolock) where VBL04 = 4 and VBL27 = 1 and VAK01 = 0 and VAA07 = @VAA07 
  select @VBM05 =isnull(sum(isnull(VAJ38,0)),0) from VAJ2 with(nolock) where VAA07 = @VAA07 and ACF01 = 2 and VAJ05 = 1  
  
  update VBM1 with(rowlock)
    set VBM04 = @VBM04, 
        VBM05 = @VBM05 
  where VAA01 = @VAA01 and VBM03 = 2
  
  update VBM2 with(rowlock)
    set VBM04 = @VBM04, 
        VBM05 = @VBM05 
  where VAA01 = @VAA01 and VAA07 = @VAA07 and ACF01 = 2 
  /*====================================================更新CRM预约表状态为就诊==============================================================*/
  Update SCF1 with(rowlock) 
    SET SCF11 = 5 
  where VAA07B = @VAA07 
  /*=========================================================================================================================================*/
  COMMIT TRAN