SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HORate_CardCharge_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [HORate_CardCharge_Update] AS' 
END
GO


--=====================================================================================================
--Author     <...8065>
--Alter Date <...2018-12-21> 
--Description<...一卡通扣费过程>
--2011-06-17,HHY,增加“基本帐户交易时产生积分”
--2011-06-21,HHY,增加“基本帐户交易时自动升级”
--2011.12.20,8065,扣费时增加自动结帐过程
--2012.04.12,8065,判断会员扣费场所
--2013.03.15,8065,增加调用场合医生站简易挂号
--2013.03.15,8065,增加简易挂号、医嘱发送根据参数允许会员卡透支扣款
--2013.07.15,8065,处理简易挂号不能自动结算的问题
--2013.09.10,8065,门诊会员扣费时判断住院冻结金额
--2014.02.25,8065,单条单据扣费场合传入金额后后台重新取值
--修改VCB04长度
--2014.12.12,8065,减小锁粒度
--2014.12.16,8065,门诊、住院结算校验余额不足返回3
--2015.08.12,8065,根据参数医保病人会员扣费时自动结算来调用自动结算过程
--2016.04.06 修改VAI和VAJ状态包含医技记账费用
--2016.04.08 获取单据金额合计判断划价或者消帐数据
--2016.07.19 会员扣费时校验会员基本金额是否为负数
--2017.08.24 根据参数记录会员扣费单据自动结账队列
--2017.12.05 根据参数从本人账户或者关联主卡上扣费
--====================================================================================================
ALTER  PROC [HORate_CardCharge_Update] 
          @VAA01 int ,
          @VAA07 int , 
          @VAI01 int ,
          @ACF01 int ,
          @BCE01 int ,
          @BCK01 int ,
          @BCE02 varchar(20) ,
          @BCE03 varchar(20) ,
          @VAJ38 numeric(18,2) ,--基本账户扣费       
          @Result int out , --0=成功、1=单据已经扣费、2=余额不足、3=更新失败、4=不需要扣费
          @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        
          @VCA20 numeric(18,2) = 0 ,--赠送账户扣费
          @VBU01 int = 0 --会员帐号
AS 
  Declare @VCA01 int,          
          @IsHyType smallint ,          
          @VCB04 varchar(64) ,            
          @VBU14 numeric(18,2) ,
          @VBU15 numeric(18,2) ,
          @CVBU14 numeric(18,2) ,
          @CVBU15 numeric(18,2) ,
          @BEP06B numeric(18 ,2 ),
          @VAI22 numeric(18,4) ,
          @VAI23 numeric(18,4) ,
          @BCK01B int ,
          @ErrId int ,
          @AutoCharge int ,
          @AutoChargePlace varchar(128) ,
          @ChargeMoney varchar(20) ,
          @AvailableMoney varchar(20) ,
          @BalanceMoney varchar(20),
          @VBU14Show varchar(20) ,
          @BlockedMoneyShow varchar(20) ,
          @EasyRegOverDraft tinyint,
          @AdviceOverDraft tinyint,
          @BlockedMoney numeric(18,4),
          @InsureAutoCharge int ,
		  @VBU01A int 
  
  --如果不在会员扣费场所中，退出
  SELECT @AutoChargePlace = Value from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 43
  
  IF ((@AWorkType < 5) or (@AWorkType = 8)) and exists(select * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 43)
  BEGIN
    IF @AWorkType < 5
    BEGIN
      IF CHARINDEX(CAST(@AWorkType - 1 as varchar(1)) , ISNUll(@AutoChargePlace , '')) <= 0
      BEGIN
        RETURN 4
      END
    END ELSE IF @AWorkType = 8
    BEGIN
      IF CHARINDEX('4' , ISNUll(@AutoChargePlace , '')) <= 0
      BEGIN
        RETURN 4
      END
    END
  END
  
  IF exists(select * from VCA1 with(nolock) where VAI01 = @VAI01 and @VAI01 > 0 and @AType = 0 and ((@AWorkType < 5) or (@AWorkType = 8)))
  BEGIN
    SET @Result = 1
    RETURN 1
  END
     
  If (@VAI01 = 0) and ((@AWorkType < 5) or (@AWorkType = 8))
  BEGIN
    RAISERROR('单据ID传入异常.', 16, 1) with nowait
    RETURN 1
  END    
   
  SELECT @AutoCharge = Value from SYS_Parameters with(nolock) where ProgramID = 103010 and ParamNo = 72          
  SET @AutoCharge = ISNULL(@AutoCharge , 0)
  --如果参数选择扣费时自动结算，自动结算操作员为空，则退出
  If (@AutoCharge = 1) AND ((@AWorkType < 5) or (@AWorkType = 8))
  BEGIN
    If not exists(select * from SYS_Parameters with(nolock) where ProgramID = 103010 and ParamNo = 73 and Value <> '')
    BEGIN
       RAISERROR('请设置自动结算操作员.', 16, 1) with nowait
       Return 2
    END
  END
  
  SET @Result = 0
  ---按最优折扣费别优惠
  declare @ABC02 varchar(20)
  exec HORate_Optimal_Rebate @VAA01,@VAA07,@ABC02 out
  --2014.02.25单条单据扣费场合系统从后台重新获取扣费金额
  --2016.04.08获取单据金额合计判断划价或者消帐数据
  IF (@VAI01 > 0) and ((@AWorkType < 5) or (@AWorkType = 8) or (@AWorkType = 11 and @AType = 0))
  BEGIN
    SELECT @VAJ38 = SUM(VAJ38) 
    from VAJ1 with(nolock)
    WHERE VAI01 = @VAI01 and (VAJ05 = 1 or VAJ05 = 4) and (VAJ04 =2 or VAJ04 =5)
  END
  SET @VAJ38 = ISNULL(@VAJ38 , 0) 
  
  SET @VCA20 = ISNULL(@VCA20 , 0)
--IF (@VAJ38 = 0) AND (@VCA20 = 0 )
--BEGIN
--RETURN 0	
--END
  SELECT @IsHyType = dbo.GetWorkingMode()
  SELECT @BlockedMoney = dbo.GetBlockedMoney(@AWorkType , @VAA01)
  SELECT @BEP06B = BEP06B from VAA1 where VAA01 = @VAA01 
  SET @BEP06B = ISNULL(@BEP06B , 0)
  --如果当前单据已经扣费或者作废则退出
  If exists(select * from VAI1 with(nolock) where VAI01 = @VAI01 and (VAI18 = 3 or VAI16 = 9 or VAK01 > 0)) AND @AWorkType < 6
  BEGIN
    SET @Result = 1
    RETURN 2
  END
  --根据参数从本人账户或者关联主卡账户上扣费
  If ((@AWorkType < 5) or (@AWorkType = 8)) 
    and exists(select * from Sys_Parameters where ProductId = 100 and ProgramID = 103010 and ParamNo =311 and Value ='1')
  BEGIN
    SELECT @VBU01A = VBU01A from VBU1 where VAA01 = @VAA01 
	If ISNULL(@VBU01A , 0) > 0
	BEGIN
      Exec HORate_CardUpdate_Expand @VAA01 , @VAA07 , 0 , @VAI01 , @BCE01 , @BCE03 , @AWorkType , @VBU01A , @AutoCharge	 
	  If @@ERROR > 0
	  BEGIN
	     RAISERROR('会员病人绑定主卡扣费失败.', 16, 1) with nowait 
	  END
	  RETURN 2
	END
  END
  --如果当前卡余额不足本次扣费，则退出
  SELECT @VBU01 = a.VBU01 ,@VBU14 = VBU14 , @VBU15 = VBU15 , @VCB04 = VCB04 
  from VBU1  a with(nolock)
    JOIN VAA1 b with(nolock) ON a.VBU01 = b.VBU01  
  where b.VAA01 = @VAA01 
  
  If ISNULL(@VBU01 , 0) = 0 
  BEGIN
    RETURN 2 
  END
  
  If ISNULL(@VBU14, 0) < 0
  BEGIN
    SET @Result = 2 
    SELECT @VBU14Show = CAST(@VBU14 as varchar(20))
    RAISERROR('病人会员基本余额[%s]，为欠费状态，请及时充值.', 16, 1,@VBU14Show) with nowait
    --RETURN 2
  END
  
  
  SET @CVBU14 = @VBU14 
  SET @CVBU15 = @VBU15 
  
  SELECT @EasyRegOverDraft = ISNULL(dbo.GetSysParamValue(100,105002,260) , 0)
  SELECT @AdviceOverDraft = ISNULL(dbo.GetSysParamValue(100,105002,261) , 0)
  
  --判断卡余额是否够扣费(与病人门诊信用额度结合使用)
  IF (@AWorkType < 6) or (@AWorkType = 8)
  BEGIN     
     IF ((ABS(@VAJ38) > 0) and (@VBU14 + @BEP06B - @BlockedMoney < @VAJ38)) or ((ABS(@VCA20) > 0) and (@VBU15 < @VCA20))
     BEGIN        
     	--简易挂号或者医嘱允许会员卡透支扣款，此处不做判断
        If ((@AWorkType <> 3) and (@AWorkType <> 8)) 
           or ((@AWorkType = 3) and (@AdviceOverDraft = 0))
           or ((@AWorkType = 8) and (@EasyRegOverDraft = 0))
        BEGIN           
           SET @Result = 2 
           IF (@IsHyType >= 1) and (@AWorkType < 6)
           BEGIN 
              --基本余额扣费
              If ABS(@VAJ38) > 0
              BEGIN
                 SET @ChargeMoney = CAST(@VAJ38 as varchar(20))
                 SET @BlockedMoneyShow = CAST(@BlockedMoney as varchar(20))
                 SET @AvailableMoney = CAST((@VBU14 + @BEP06B - @BlockedMoney) as varchar(20))
                 SET @BalanceMoney =  CAST((@VAJ38 - @VBU14-@BEP06B) as varchar(20))
                 RAISERROR('病人余额[%s],住院冻结金额[%s],需扣会员金额[%s],差额[%s],不足扣费.', 16, 1,@AvailableMoney ,@BlockedMoneyShow, @ChargeMoney , @BalanceMoney) with nowait
              END
              --赠送余额扣费
              If ABS(@VCA20) > 0
              BEGIN
                 SET @ChargeMoney = CAST(@VCA20 as varchar(20))
                 SET @BlockedMoneyShow = CAST(@BlockedMoney as varchar(20))
                 SET @AvailableMoney = CAST(@VBU15 as varchar(20))
                 SET @BalanceMoney =  CAST((@VCA20 - @VBU15) as varchar(20))
                 RAISERROR('病人余额[%s],需扣会员金额[%s],差额[%s],不足扣费.', 16, 1,@AvailableMoney , @ChargeMoney , @BalanceMoney) with nowait
              END
          END
          RETURN 2
        END
     END 
  END ELSE BEGIN 
     IF (@VBU14 - @BlockedMoney < @VAJ38) or ( @VBU15 < @VCA20)
     BEGIN        
        SET @Result = 3         
        IF (@IsHyType >= 1) --and (@AWorkType <> 6)
        BEGIN 
           --基本余额扣费
           If ABS(@VAJ38) > 0
           BEGIN                           
              SET @ChargeMoney = CAST(@VAJ38 as varchar(20))
              SET @BlockedMoneyShow = CAST(@BlockedMoney as varchar(20))
              SET @AvailableMoney = CAST((@VBU14 - @BlockedMoney) as varchar(20))
              SET @BalanceMoney =  CAST((@VAJ38-@VBU14) as varchar(20))
              RAISERROR('病人余额[%s],住院冻结金额[%s],需扣会员金额[%s],差额[%s],不足扣费.', 16, 1,@AvailableMoney ,@BlockedMoneyShow, @ChargeMoney , @BalanceMoney) with nowait
           END
           --赠送余额扣费
           If ABS(@VCA20) > 0
           BEGIN
              SET @ChargeMoney = CAST(@VCA20 as varchar(20))
              SET @AvailableMoney = CAST(@VBU15 as varchar(20))
              SET @BalanceMoney =  CAST((@VCA20-@VBU15) as varchar(20))
              RAISERROR('病人余额[%s],需扣会员金额[%s],差额[%s],不足扣费.', 16, 1,@AvailableMoney , @ChargeMoney , @BalanceMoney) with nowait
           END
        END
        RETURN 2
     END 
  END 
  --开始一个事物
  BEGIN TRAN
  --更新单据状态
  If @Atype = 0                  --扣费
  BEGIN
    UPDATE VAI1 with(rowlock) 
      SET VAI18 =3 
    WHERE VAI01 = @VAI01 AND VAI18 in (2,5) and VAI16 = 1
    --更新明细状态
    UPDATE VAJ1 with(rowlock)
     SET VAJ04 =3
    WHERE VAI01 = @VAI01 AND VAJ04 in (2,5) and VAJ05 = 1
  END ELSE If @AType = 1         --退费
  BEGIN
   UPDATE VAI1 with(rowlock)
      SET VAI18 =3 
    WHERE VAI01 = @VAI01 AND VAI18 = 2 and VAI16 = 4
    --更新明细状态
    UPDATE VAJ1 with(rowlock)
     SET VAJ04 =3
    WHERE VAI01 = @VAI01 AND VAJ04 = 2 and VAJ05 = 4
  END
  --更新支付方式的VBU01
  IF ISNULL(@VBL01 , 0) > 0
  BEGIN
  	UPDATE VBL1 with(rowlock)
  	  SET VBU01 = @VBU01
  	WHERE VBL01 = @VBL01 
  END
  --更新卡余额
  IF abs(@VAJ38) > 0           --基本账户
  BEGIN
     SET @CVBU14 = ISNULL(@VBU14 , 0) - @VAJ38      
     Update VBU1 with(rowlock)
       set VBU14 = ISNULL(VBU14 ,0) - @VAJ38
     where VBU01 = @VBU01      
  END 
   
  IF abs(@VCA20) > 0           --赠送账户
  BEGIN
     SET @CVBU15 = ISNULL(@VBU15 , 0) - @VCA20      
     Update VBU1 with(rowlock)
       set VBU15 = ISNULL(VBU15 ,0) - @VCA20 
     where VBU01 = @VBU01 
  END
  
  IF @@ERROR > 0
  BEGIN
    SET @Result = 3
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RETURN 2 
  END
  --更新病人余额表 
  Update VBM1 with(rowlock)
    set VBM05 = ISNULL(VBM05 , 0) + @VAJ38  
  where VAA01 = @VAA01 and VBM03 = 3 
  
  IF @@ERROR > 0
  BEGIN
    SET @Result = 3
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RETURN 2 
  END  
  --写入会员交易跟踪
  EXEC Core_NewId_VCA01 @VCA01 out 
  INSERT INTO VCA1 
     (VCA01, VAA01, VAA07, ACF01, VBU01 , VCB04 ,VCA07, VAI01, VCA09 ,VCA10, VBU14, VBU15 ,BCE03, VCA18, BCE01 , VAK01 , VBL01 , VCA20)
  SELECT
     @VCA01, @VAA01, @VAA07, @ACF01, @VBU01 ,@VCB04 ,@AWorkType, @VAI01, @AType ,@VAJ38, @CVBU14 , @CVBU15 , @BCE03, getdate(), @BCE01 , @VAK01 , @VBL01 , @VCA20 
     
  IF @@ERROR > 0
  BEGIN
    SET @Result = 3
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RETURN 2 
  END      
  --如果参数设置为自动结算，调用自动结算过程
  If (@AutoCharge = 1) and (@AWorkType <= 4 or @AWorkType = 8)
  BEGIN     
    If exists(select * from sys_Parameters with(nolock) where ProductID= 9999 and ProgramID = 9999 and ParamNo = 109 and Value ='1' )
    BEGIN
      SELECT @BCK01B = BCK01B , @VAI22 = VAI22 , @VAI23 = VAI23 from VAI1 with(nolock) where VAI01 = @VAI01
      INSERT INTO ZIX1(VAI01 , VAK01 , BCE01 , BCE03 , VCA01 , VAA01 , VAA07 , VCA10 , VAI22 , VAI23 , VBU01 ,DFLAG ,BCK01B)
      SELECT @VAI01 , 0 , @BCE01 , @BCE03 , @VCA01 , @VAA01 , @VAA07 , @VAJ38  ,@VAI22 , @VAI23 , @VBU01 , 0 ,@BCK01B
      
      IF @@ERROR > 0
      BEGIN
         SET @Result = 3
         IF @@TRANCOUNT > 0 ROLLBACK TRAN
         RETURN 2 
      END 
    END ELSE BEGIN
      select @InsureAutoCharge = value 
      from sys_Parameters with(nolock)
      where productid = 100 and programid = 103010 and paramno =96
    
      If (ISNULL(@InsureAutoCharge , 0) = 1) 
      or (not exists(select * from IAR1 with(nolock) where VAA01 = @VAA01 and VAA07 = @VAA07 and IAR32 = 1 and ACF01 <> 2))
      BEGIN
         DECLARE @AutoVAK01 int 
         Exec @ErrId = HORate_VAK1_AutoCharge @VAI01 , @VCA01 , @AutoVAK01 out 
    
         If @ErrId > 0 
         BEGIN
           IF @@TRANCOUNT > 0 ROLLBACK TRAN      
           RETURN 2 
         END
      END
    END
  END
 
  COMMIT TRAN
  --基本帐户交易时产生积分
  IF @VAJ38 <> 0 --基本帐户交易额
  BEGIN
    DECLARE @Cur_VBU17 numeric(18,2),@Style int
    SET @Style=@ACF01*10 --10表示门诊消费；20表示住院消费
    EXEC @ErrId=Customer_VBU17_INC @VBU01,@Style,@VCA01,@VAK01,@VBL01,@VAJ38,@BCE03
    IF @ErrId>0 OR @@ERROR>0
    BEGIN
      --ROLLBACK TRAN
      RAISERROR('基本帐户交易产生积分时发生错误。',16,1) WITH NOWAIT
      RETURN @@ERROR 
    END
  END
  
  --基本帐户交易时自动升级
  IF @VAJ38>0 --基本帐户交易额
  BEGIN
    EXEC @ErrId=Customer_Upgrade_update @VBU01,@BCE01,@BCE03
    IF @ErrId>0 OR @@ERROR>0
    BEGIN
      --ROLLBACK TRAN
      RAISERROR('基本帐户交易自动升级时发生错误。',16,1) WITH NOWAIT
      RETURN @@ERROR 
    END
  END 
  
  

GO