SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--==========================================================================================================================
--Author     <...8065>
--Alter Date <...2015.11.19>
--Description<...会员卡支付(可以非本人卡、储值卡等,暂只支持基本账户扣费)>
--修改VCB04长
--History    <...8065 2015.11.19 修正分单结账时多单都存在代付会员支付方式时会员余额更新>
--==========================================================================================================================
ALTER proc [HORate_CardUpdate_Interface] @XML ntext ,
                                        @VAA01 int ,
                                        @VAA07 int ,    
                                        @ACF01 int ,      
                                        @BCE01 int ,
                                        @BCE03 varchar(20) ,                             
                                        @Result int out , --0=成功、1=单据已经扣费、2=余额不足、3=更新失败、4=不需要扣费
                                        @VAI01 int = 0,
                                        @AType tinyint = 0 ,  --0 = 扣费,1 = 退费 
                                        @AWorkType tinyint = 1 , --1=门诊记账，2=药房发药，3=医嘱记账，4=医技执行,5=挂号，6=门诊结账 , 7=住院结帐,8=简易挂号
                                        @VAK01 int =0 , --结账ID，默认为0 
                                        @VBL01 int =0 --缴款ID，默认为0                                       
As
BEGIN
  Declare @i int ,
          @k int ,
          @count int ,
          @countc int ,
          @VCA01 int ,
          @VAK28 int ,
          @VAK01A int ,
          @VBL01A int ,
          @BBP02 varchar(30) ,
          @CVBL13 numeric(18,4) ,
          @RVBL13 numeric(18,4)
  --=================================================创建扣费卡号及金额临时表====================================================
  --暂时只考虑扣费从前台传入的卡号及金额，退费时自动按VAK01或者VBL01去提取数据
  Create Table #tmpCardInfo
  (
    ID int identity(1 , 1) ,
    VCA01 int ,
    VBU01 int ,
    VCB04 varchar(64) ,
    BBP02 varchar(30) ,
    VCA10 numeric(18,4) ,
    VBU14 numeric(18,4) ,
    VBU15 numeric(18,4)
  )
  --分配后的会员卡对应接结算记录需要扣费金额
  Create Table #NewCardInfo
  (
    ID int identity(1 ,1) ,
    VCA01 int ,
    VBU01 int ,
    VBL01 int ,
    VAK01 int ,
    VAI01 int ,
    VCB04 varchar(64) ,
    VCA10 numeric(18,4) ,
    VBU14 numeric(18,4) ,
    VBU15 numeric(18,4)
  ) 
  IF @AType = 0            --扣费 
  BEGIN
     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
       
     INSERT INTO #tmpCardInfo(VBU01 , VCB04 , VCA10 , BBP02)
     SELECT * from OpenXml(@iDOM,'/Root/VCA/Ie',8)
     WITH
     (
         VBU01 int ,
         VCB04 varchar(64) ,
         VCA10 numeric(18,4) ,
         BBP02 varchar(30)
     )  
  /*退费时根据参数VAK01去后台查找退费类型的支付方式*/     
  END ELSE IF @AType = 1   --退费
  BEGIN
    SELECT @VAK01A = VAK01A 
    from VAK1 
    where VAK01 = @VAK01 
    
    SELECT @VBL01A = VBL01 
    from VBL1 a
      join BBP1 b on b.BBP02 = a.VBL14 
    where VAK01 = @VAK01A and VBL15 = '05' and ISNULL(b.BBP09 , 0) = 1
    
    INSERT INTO #NewCardInfo(VAK01 , VBL01 , VBU01 , VCA01 , VCA10 ,VCB04 )
    SELECT @VAK01 , @VBL01 , VBU01 , 0 ,-VCA10 , VCB04  
    from VCA1 
    where VBL01 = @VBL01A and VAA01 = @VAA01 and VAA07 = @VAA07 and ACF01 = @ACF01 
  END
  
  If @AType = 0
  BEGIN
    If not exists(select * from #tmpCardInfo) 
    BEGIN
       RAISERROR('提交错误，没有需要扣费的会员卡信息.', 16, 1) with nowait
       RETURN 2
    END
    --校验需要扣费的会员卡金额
    If exists(select * from #tmpCardInfo a join VBU1 b on a.VBU01 = b.VBU01 where a.VCA10 > b.VBU14)
    BEGIN
       RAISERROR('当前需要扣费的金额大于会员卡余额.', 16, 1) with nowait
       RETURN 2
    END
  END
  --====结算按支付方式重新生成待扣费会员卡信息(分单模式下，可能一个支付方式对应多个卡号，或者一个卡号对应多个支付方式)=========
  /*按每笔结算会员接口支付方式金额分配到扣费会员卡上，直到分配完成终止*/
  /*按结算生成的会员交易记录每个卡与当前结算记录匹配的金额，写入会员交易记录*/
  /*退费时暂时强制规定只允许全部按原方式退回或者全部退费为其他支付方式*/
  
  If (@AType = 0) and (@AWorkType >= 5 and @AWorkType <= 7) and (@VAK01 > 0 )
  BEGIN
    SELECT top 1 @BBP02 = BBP02 from #tmpCardInfo 
    SELECT @VAK28 = VAK28 from VAK1 where VAK01 = @VAK01 
    SELECT IDENTITY(int , 1 ,1) as [ID],VBL01 , VBL13 ,a.VAK01 INTO #tmpCardVBL1 
    from VBL1 a
      join VAK1 b on b.VAK01 = a.VAK01 
    where b.VAK28 = @VAK28 and a.VBL14 = @BBP02 and VBL15 = '05'
    
    SELECT @i = 0 ,@count = COUNT(1) from #tmpCardVBL1 
    while @i < @count 
    BEGIN
      SELECT @CVBL13 = VBL13 , @VBL01 = VBL01 , @VAK01 = VAK01 from #tmpCardVBL1 
      WHERE [ID] = @i + 1
      SELECT @k = 0 , @RVBL13 = 0 ,@countc = COUNT(1) from #tmpCardInfo 
      while @k < @countc 
      BEGIN
        --当前会员卡存在可分配金额
        IF exists(select * from #tmpCardInfo where VCA10 > 0 and ID = @k + 1)
        BEGIN
           SELECT @RVBL13 = VCA10 from #tmpCardInfo 
           WHERE ID = @k + 1
           SET @RVBL13 = @RVBL13 - @CVBL13 
        
           INSERT INTO #NewCardInfo(VAK01 , VBL01 , VBU01 , VCA01 , VCA10 ,VCB04)
           SELECT @VAK01 , @VBL01 , VBU01 , VCA01 , Case when @RVBL13 <=0 then VCA10 else @CVBL13 end , VCB04 from #tmpCardInfo 
           where ID = @k + 1
           
           Update #tmpCardInfo 
             SET VCA10 = Case when @RVBL13<= 0 then 0 else @RVBL13 end
           WHERE ID = @k + 1
           
           SET @CVBL13 = -@RVBL13
                             
           If @RVBL13 > 0 
           BEGIN
             SET @RVBL13 = 0
             SET @k = @k + 1
             BREAK 
           END                   
           SET @k = @k + 1
        END ELSE BEGIN  --当前会员卡金额分配完毕
          SET @k = @k + 1
          CONTINUE 
        END       
      END
      SET @i = @i + 1
    END
  END
 
  --===============================================生成会员交易跟踪记录临时表================================================== 
  SELECT @i = 0 , @count =COUNT(1) from #NewCardInfo 
  EXEC Core_NewId_VCA01 @VCA01 out , @count 
  
  While @i < @count 
  BEGIN
    Update #NewCardInfo 
      SET VCA01 = @VCA01 - @count + @i + 1
    where ID = @i + 1
    SET @i = @i + 1
  END

  BEGIN TRAN
  --=====================================================会员扣费==============================================================
  SELECT @i = 0 , @count =COUNT(1) from #NewCardInfo 
  
  While @i < @count 
  BEGIN
    Update a
      SET a.VBU14 = a.VBU14 - ISNULL(b.VCA10 ,0)
    from VBU1 a
      join #NewCardInfo b on b.VBU01 = a.VBU01 
    where ID = @i + 1
    
    Update a
    SET a.VBU14 = b.VBU14 ,
        a.VBU15 = b.VBU15 
    from #NewCardInfo a
    join VBU1 b on b.VBU01 = a.VBU01
    where ID = @i + 1
    
    SET @i = @i + 1
  END
   
    
  If @@ERROR > 0 
  BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RAISERROR('更新会员余额时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
    Return 3
  END
  --=====================================================交易跟踪==============================================================
  INSERT INTO VCA1 
     (VCA01, VAA01, VAA07, ACF01, VBU01 , VCB04 ,VCA07, VAI01, VCA09 ,VCA10, VBU14, VBU15 ,BCE03, VCA18, BCE01 , VAK01 , VBL01 )
  SELECT
     VCA01, @VAA01, @VAA07, @ACF01, VBU01 ,VCB04 ,@AWorkType, @VAI01, @AType ,VCA10, VBU14 , VBU15 , @BCE03, getdate(), @BCE01 , @VAK01 , @VBL01  
     from #NewCardInfo 
     order by ID 
     
  If @@ERROR > 0 
  BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RAISERROR('插入会员交易记录跟踪时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
    Return 3
  END     
  --===========================================================================================================================
  COMMIT TRAN
END    
IF Object_id('tempdb..#NewCardInfo') IS NOT NULL DROP TABLE #NewCardInfo                                    
IF Object_id('tempdb..#tmpCardInfo') IS NOT NULL DROP TABLE #tmpCardInfo 
IF Object_id('tempdb..#tmpCardVBL1') IS NOT NULL DROP TABLE #tmpCardVBL1