/****** Object:  StoredProcedure [Customer_VBU1_Charge]    Script Date: 2020-11-18 15:14:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------------
--Author     <...8065>
--Alter Date <...2020.11.18>
--Description<...会员卡充值、退费>
--History    <...增加参数支持会员充值队列>
----------------------------------------------------------------------------------
ALTER Proc [Customer_VBU1_Charge] 
      @Xml ntext 
  	, @VBU01 int
    , @VAA01 int
	, @VBU07 int
	, @State int
	, @VBL01 int out	
	, @VBL02 varchar(20) out
	, @PayBills varchar(1024) = ''
	, @IsPayQueue tinyint = 0
AS
--@State  0:充值  1：退卡  2:退充值  3:押金  4:工本费
IF @State<0 OR @State>7
BEGIN
   RAISERROR('命令不正确，错误号: %d.', 16, 1, @State) with nowait
   RETURN @State 		
END

DECLARE @iDOM int ,    
        @ErrId int ,     
        @EmpId int ,
        @EmpName varchar(20) ,
        @UserName varchar(20) 
------------------------------------解析前台传递的XML数据---------------------------------------  
  --解析XML文档	
  Exec @ErrId = sp_xml_preparedocument @iDOM OUTPUT, @XML
  if @ErrId > 0
  BEGIN
    EXEC sp_xml_removedocument @iDOM
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @ErrId) with nowait
    RETURN @ErrId
  END    
  --操作员
  SELECT @EmpId = EmpID ,
	     @EmpName = EmpName ,
		 @UserName = UserName
  FROM OpenXml(@iDOM, '/Root/OperInfo', 8)
  WITH(
		   UserId INT
		   ,UserCode VARCHAR(32)
		   ,UserName VARCHAR(64)
		   ,UserLoginDate DATETIME
           ,EmpId INT	
           ,EmpName VARCHAR(20)
	)

  --------------------------------------读取充值临时表------------------------------------------
  SELECT * INTO #tmpVBLCharge
  FROM OpenXml(@iDOM, '/Root/VBL/Ie', 8) 	 
      WITH (
      	    [NO] varchar(20)    --票据号
		    ,VBL04 tinyint 
		    ,VBL05 tinyint
		    ,VBL12 varchar(128)	--备注
      	    ,VBL13 numeric(18,4)--金额
      	    ,VBL14 varchar(32)	--支付方式
      	    ,VBL15 varchar(2)	--支付类型
			,BCE01 int
			,BCE02 varchar(20)
			,BCE03 varchar(20)
			,VBL13A numeric(18,4) --赠送金额
			,VBL13B numeric(18,4) --积分值
            ,VBL12C varchar(128)	--押金备注
            ,VBL13C numeric(18,4) --押金        
            ,FAF01 INT --交易ID
			,VBL11 varchar(50)
			,FHN01 INT
      )    
	  
   If @State = 0 
   BEGIN
     If NOT EXISTS(SELECT * FROM VBU1 a WITH(NOLOCK) JOIN VCB1 b WITH(NOLOCK) ON b.VCB01 = a.VCB01 where a.VBU01 = @VBU01 and b.VCB12 = 2)
	 BEGIN
	    EXEC sp_xml_removedocument @iDOM
        RAISERROR('当前会员账户绑定卡不为发卡状态，请检查。', 16, 1) with nowait
        RETURN 2 
	 END
   END  
   Declare @RefundPay TABLE(PayBillNo varchar(50) , FHN01 INT)

   IF EXISTS(SELECT * FROM #tmpVBLCharge WHERE VBL15 IN('08' ,'09' , '10' , '12' , '13') and VBL13 <> 0)
   BEGIN
     If @State IN(0 , 3 ,4)
	 BEGIN
        UPDATE a
	      SET a.FHN01 = b.FHN01 
	    FROM #tmpVBLCharge a
	      JOIN FHN1 b WITH(NOLOCK) ON b.FHN06 = a.VBL11  
	    WHERE VBL15 IN('09' , '10' , '12' , '13') and b.FHN09 = 3 and b.FHN08 = 1 and FHN22 = 0
	 END ELSE If @State IN(1,2)
	 BEGIN
	    If ISNULL(@PayBills , '') <> ''
		BEGIN
		   IF EXISTS(SELECT * FROM #tmpVBLCharge WHERE VBL15 ='08' and VBL13 <> 0)
		   BEGIN
		     INSERT INTO @RefundPay(PayBillNo  , FHN01) 
			 SELECT Value , 0 FROM dbo.Split(@PayBills ,',')
		      UPDATE a
		        SET a.FHN01 = b.FHN01
		      FROM @RefundPay a
		        JOIN FHN1 b WITH(NOLOCK) ON b.FHN25 = a.PayBillNo
	          WHERE b.FHN09 = 3 and b.FHN08 = 1 and FHN22 = 0
		   END ELSE BEGIN
		     INSERT INTO @RefundPay(PayBillNo  , FHN01) 
			 SELECT Value , 0 FROM dbo.Split(@PayBills ,',')

		      UPDATE a
		        SET a.FHN01 = b.FHN01
		      FROM @RefundPay a
		        JOIN FHN1 b WITH(NOLOCK) ON b.FHN06 = a.PayBillNo
	          WHERE b.FHN09 = 3 and b.FHN08 = 1 and FHN22 = 0
		  END
		END
	 END
   END  
  --------------------------------------读取充值附表----------------------------------------------
  SELECT IDENTITY(int,0,1) ID,* INTO #tmpSAKCharge
    FROM OpenXml(@iDOM, '/Root/SAK1/Ie', 8) 	 
      WITH (
      	   SAK03 int
      	   ,BCE01 int
      	   ,BCE03 varchar(20)
      	   ,SAK06 numeric(9,4)
      	   ,SAK08 varchar(128)
      ) 
 
  If exists(select * from #tmpSAKCharge where ISNULL(BCE01 , 0) =0)
  BEGIN
    EXEC sp_xml_removedocument @iDOM
    RAISERROR('介绍人不能为空。', 16, 1) with nowait
    RETURN 2
  END
  --------------------------------------释放Xml---------------------------------------------------   
  EXEC sp_xml_removedocument @iDOM  
  	
SET @VBL01 = 0
DECLARE
    @VBL02C varchar(30)  
  , @VBL13 numeric(18, 4)
  , @VBL05 INT
  , @VBL13A numeric(18, 4) 
  , @VBL13B numeric(8,2) 
  , @BCE01 int
  , @BCE02 varchar(10)
  , @BCE03 varchar(64)
  , @FAF01 int
  , @VBL13C numeric(18, 4)
  , @VBL01C int = 0
  , @BCK01 int
  , @VBL12 varchar(128)
  , @CurDate datetime 
  , @VBL11 varchar(50)
  , @VBL14 varchar(32)
  , @VBL15 varchar(2)

SET @CurDate = GETDATE() 

SELECT
    @VBL13 = ISNULL(VBL13,0) --基本帐户
  , @VBL13A = ISNULL(VBL13A, 0) --赠送账户
  , @VBL13B = ISNULL(VBL13B, 0) --积分账户
  , @BCE01 = BCE01 
  , @BCE02 = BCE02
  , @BCE03 = BCE03
  , @FAF01 = FAF01
  , @VBL13C = VBL13C
  , @VBL05 = Case @State 
      WHEN 0 THEN 0
      WHEN 3 THEN 0
      when 4 then 0
      ELSE 4 END --状态 0=正常, 1=退预交, 2=冲预交,3=被退,4=退费
  , @VBL12 = VBL12
  , @VBL11 = VBL11 
  , @VBL14 = VBL14 
  , @VBL15 = VBL15
FROM #tmpVBLCharge
--2019.07.12 充值启用支付队列
If (@State = 0 ) and (@IsPayQueue = 1) and (@VBL13 > 0)
BEGIN
  Exec HORate_Pay_Queue @VBL11 , @VBL12 , @VAA01 , 0 , 5 , @EmpID , @EmpName , @VBL01 , @VBL02  ,@VBL14 , @VBL15 , @VBL13 ,'' ,  @VBU01
  IF @@ERROR>0
  BEGIN
      ROLLBACK TRAN
	  RAISERROR('插入支付待充值队列发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
	  RETURN @@ERROR 			
  END

  RETURN 1
END

select @BCK01=BCK01 from BCE1 where BCE01=@BCE01
--如果存在Pos接口类型的支付方式，校验是否刷卡扣费
if exists(select * from #tmpVBLCharge a, BBP1 b where a.VBL14 = b.BBP02 and a.VBL15 ='02' and b.BBP09 =1)
BEGIN
  if ISNULL(@FAF01 ,0) <= 0
  BEGIN
     RAISERROR('Pos接口类型的支付方式未传入Pos刷卡记录，请银联扣费成功后点保存按钮.', 16, 1 )  WITH NOWAIT
     RETURN 1
  END 
END
--通用类型的支付方式，校验是否传入支付单号
if exists(select * from #tmpVBLCharge a, BBP1 b where a.VBL14 = b.BBP02 and a.VBL15 ='11' and b.BBP09 =1)
BEGIN
  if ISNULL(@VBL11 ,'') = ''
  BEGIN
     RAISERROR('通用类型的支付方式未传入支付单号，请联系工程师检查.', 16, 1 )  WITH NOWAIT
     RETURN 1
  END 
END
----退卡、退充值判断传入金额是否大于余额
If (@VBL13 < 0)
BEGIN
  If exists(select * from VBU1 with(nolock) where VBU01 = @VBU01 and ABS(@VBL13) > VBU14)
  BEGIN
     RAISERROR('退费金额大于当前会员卡余额，系统不允许退费。', 16, 1 )  WITH NOWAIT
     RETURN 1
  END
END
--如果病人存在未结算费用，不允许会员卡退费或者退卡 
If dbo.GetEmpPermissionValue(116001011,@BCE01)=0 and (@State in(1 , 2) and (@VBL13 < 0))
BEGIN
  if exists(select * from VAJ1 with(nolock) where VAA01 = @VAA01 and VAK01 = 0 and VAJ05 = 1 and VAJ04 <> 1) 
   or exists(select * from VAJ2 with(nolock) where VAA01 = @VAA01 and VAK01 = 0 and VAJ05 = 1)
  BEGIN
     RAISERROR('当前病人存在未结算明细费用，你不具备退费的权限.', 16, 1 )  WITH NOWAIT
     RETURN 1
  END
END	
--如果前台选择从基本账户扣除费用，则判断余额
If exists(select * from #tmpVBLCharge a, BBP1 b where a.VBL14 = b.BBP02 and a.VBL15 = b.AAO01 and a.VBL15 ='05' )
BEGIN
  If @VBL13 > 0
  BEGIN
    If not exists(select * from VBU1 where VBU01 = @VBU01 and VBU14 >= @VBL13)
    begin
      RAISERROR('当前会员卡余额不足扣费.', 16, 1 )  WITH NOWAIT
      RETURN 1 
    end
  END
  
  Update #tmpVBLCharge 
    SET VBL13 = -VBL13
END

If @VBL13 <> 0
BEGIN
  Exec Core_NewIDEx 'VBL1','VBL01',@VBL01 out 
 
  SET @VBL02 ='C' + RIGHT('00000000000' + Cast(@VBL01 AS VARCHAR), 10)
END

BEGIN TRAN 
---------------------------------------------------------------1.基本帐户------------------------------------------------------------------------
IF @VBL13<>0
BEGIN	 
  --插入数据 病人缴款记录
  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, PNRCP, VBU01, FAF01, VBL01B,BCK01)
  SELECT @VBL01, @VBL02 ,'' , VBL04, VBL05, @VAA01, 0, VBL11, VBL12, VBL13,VBL14,VBL15, 0, 0, @CurDate, @CurDate, 
         0, BCE01, BCE02, BCE03, 0, 0, 0, 1, 0, @VBU01, @FAF01,@VBL01,@BCK01 
  from #tmpVBLCharge      	

  IF @@ERROR>0
  BEGIN
      ROLLBACK TRAN
	  RAISERROR('写入病人缴款时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
	  RETURN @@ERROR 			
  END
   /**======================================更新接口交易记录为结账状态===============================================================================*/
   IF EXISTS(SELECT * FROM #tmpVBLCharge WHERE FHN01 > 0)
   BEGIN
     UPDATE a
	   SET a.FHN22 = 1 ,
	       a.FHN24 = @CurDate ,
	       a.BCE03A = @EmpName
	 FROM FHN1 a 
	   JOIN #tmpVBLCharge b ON b.FHN01 = a.FHN01 
   END

   If @State IN(1,2)
   BEGIN
     IF EXISTS(SELECT * FROM @RefundPay WHERE FHN01 > 0)
	 BEGIN
	    UPDATE a
	      SET a.FHN22 = 1 ,
	          a.FHN24 = @CurDate ,
	          a.BCE03A = @EmpName
	    FROM FHN1 a 
	      JOIN @RefundPay b ON b.FHN01 = a.FHN01 
	 END
   END
  --从基本账户扣钱，更新余额
  If exists(select * from #tmpVBLCharge a, BBP1 b where a.VBL14 = b.BBP02 and a.VBL15 = b.AAO01 and a.VBL15 ='05' )
  BEGIN
    Update VBU1 with(rowlock)
      SET VBU14 = VBU14 - @VBL13
    where VBU01 = @VBU01 
  END
  --更新帐户余额
  IF @State in (3,4)  --如果是押金，则不需要更新余额
  BEGIN
    COMMIT TRAN
    RETURN 0
  END

  /*Update VBU1 set 
    VBU12 = ISNULL(VBU12,0)+ISNULL(@VBL13,0)--基本帐户
    ,VBU14 = isnull(VBU14,0) + ISNULL(@VBL13,0) --基本账户余额
  where VBU01 = @VBU01  
		
 IF @@ERROR>0 OR @@ROWCOUNT=0
  BEGIN
      ROLLBACK TRAN
	  RAISERROR('更新会员基本帐户余额时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
	  RETURN 1
  END*/


  IF exists(select * from vbu1 with(nolock) where vbu21=2 and vbu14<0 and vbu01=@VBU01)
  BEGIN
     Update VBU1 with(rowlock) 
      set VBU12=0 --基本帐户
          ,VBU14 = 0 --基本账户余额
          ,VBU16 = isnull(VBU16,0) + ISNULL(VBU14,0) --押金
     where VBU01 = @VBU01 
  END					 
END		

--------------------------------------------------------------------0.押金--------------------------------------------------------------------------
IF @VBL13C<>0
BEGIN
  Exec Core_NewIDEx 'VBL1','VBL01',@VBL01C out 
  if @VBL01=0
  BEGIN
    SET @VBL01=@VBL01C
  END

  SET @VBL02C ='C' + RIGHT('00000000000' + Cast(@VBL01C AS VARCHAR), 10)
  --插入数据 病人缴款记录
  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, PNRCP, VBU01, FAF01, VBL01B,BCK01)
  Select @VBL01C, @VBL02C ,'' , 6, Case when VBL13C>=0 then 0 else 4 end, @VAA01, 0, '', VBL12C,  VBL13C, VBL14,VBL15, 0, 0, @CurDate, @CurDate, 
         0, BCE01, BCE02, BCE03, 0, 0, 0, 1, 0, @VBU01, @FAF01, @VBL01,@BCK01  from #tmpVBLCharge        	

  IF @@ERROR>0
  BEGIN
      ROLLBACK TRAN
	  RAISERROR('写入病人缴款时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
	  RETURN @@ERROR 			
  END  

  --更新病人押金
  --UPDATE VBU1 SET VBU16=ISNULL(VBU16,0)+@VBL13C WHERE VBU01 = @VBU01 
END
--如果存在基本账户或者押金，更新VBU1表
If (@VBL13 <> 0) or (@VBL13C<>0)
BEGIN
  Update VBU1 with(rowlock) 
    set VBU12 = ISNULL(VBU12,0)+ISNULL(@VBL13,0)--基本帐户
        ,VBU14 = ISNULL(VBU14,0) + ISNULL(@VBL13,0) --基本账户余额
        ,VBU16=ISNULL(VBU16,0)+ISNULL(@VBL13C,0)
    where VBU01 = @VBU01  
END


COMMIT TRAN
-------------------------------------------------------------------------------------------------------------------------------------------
If @VBL13 <> 0
BEGIN
   --更新病人余额表
  if @VBU07=1
  BEGIN
    Exec @ErrId =HORate_VBM1_Update 0, @VAA01 ,3 , @VBL13 , 0, 0 
    If @ErrId >0 
    BEGIN
       --ROLLBACK TRAN
       RAISERROR('更新病人余额表时发生错误，错误号为: %d.', 16, 1, @ErrId)  WITH NOWAIT
       Return @ErrId
    END
  END
  
  --写会员缴款记录附表
  IF exists(SELECT * FROM #tmpSAKCharge)
  BEGIN
    DECLARE @SAK01 int
    EXEC Core_NewID 'SAK1','SAK01',@SAK01 out
    INSERT INTO SAK1(SAK01,VBL01,SAK03,BCE01,BCE03,SAK06,SAK07,SAK08)
    SELECT ID+@SAK01,@VBL01,SAK03,BCE01,BCE03,SAK06,GETDATE(),SAK08 FROM #tmpSAKCharge
  END
  
  --基本帐户充值时产生积分
  EXEC Customer_VBU17_INC @VBU01,30,0,0,@VBL01,@VBL13,@BCE03,@BCE01,@BCE02
  IF @@ERROR>0
  BEGIN    
      --ROLLBACK TRAN
	  RAISERROR('基本帐户充值时产生积分时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
	  RETURN 1
  END	
  
  --会员升级
  Declare @Status int 
  If @VBL13 > 0 
    SET @Status = 0 
  else 
    SET @Status = 1
    
  EXEC Customer_Upgrade_update @VBU01,@BCE01,@BCE03 ,1 ,@Status
  IF @@ERROR>0
  BEGIN
      --ROLLBACK TRAN
	  RAISERROR('会员基本帐户充值自动升级时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
	  RETURN 1
  END	
END
--------------------------------------------------------------2.赠送帐户-------------------------------------------------------------------------
IF @VBL13A<>0
BEGIN
  EXEC @ErrId=Customer_VBU13_Update '',@VBU01,@VAA01,0,0,@VBL13A,0,0,@BCE01,@BCE02,@BCE03,'',0,0,'',@VBL01
  IF @ErrId>0 OR @@ERROR>0
  BEGIN
      --ROLLBACK TRAN
	  RAISERROR('更新会员赠送帐户余额时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
	  RETURN 1
  END
  --如果参数选择赠送也打印小票，没有基本账户充值的情况下，系统返回前台赠送充值VBL01
  If exists(select * from sys_parameters where ProductID = 1 and ProgramID = 116001 and ParamNo = 32 and value ='1') and (ISNULL(@VBL01 ,0) = 0)
  BEGIN
    SELECT top 1 @VBL01 = VBL01 
    from VBL1 with(nolock)
    where VBL04 = 5 and VBL05 =0 and VAA01 = @VAA01 and VAA07 = 0 and VBL15 = '42' 
    order by VBL01 DESC 
  END
END
-----------------------------------------------------------------3.积分帐户-----------------------------------------------------------------------
IF @VBL13B<>0
BEGIN
  EXEC @ErrId=Customer_VBU17_INC @VBU01,31,0,0,0,@VBL13B,@BCE03,@BCE01,@BCE02,@VBL12 , @VBL01 
  IF @ErrId>0 OR @@ERROR>0
  BEGIN
      --ROLLBACK TRAN
	  RAISERROR('写入会员积分日志时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
	  RETURN 1
  END	
END
---------------------------------------------------------------------------------------------------------------------------------------------------