DROP PROCEDURE [HOPatient_PrePay_Entry]
GO

DROP PROCEDURE [HOPatient_PrePay_Delete]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--===================================================================================================
--Author     <...fyq>
--Alter Date <...2015-02-04>
--Description<...退预交款>
--2012.04.10 增加按就诊记录病人明细表
--2012.05.09 退预交款时取原记录的VBL29
--2012.07.10 预交款退费时票据退票功能改由前台实现
--2013.08.13 退预交款时更新病人信用额度
--2014.07.16 支持退会员卡支付预交款，模拟退预交款金额到会员卡，并且退掉当前预交款
--2014.12.31 减小锁粒度
--==================================================================================================
CREATE  PROC [HOPatient_PrePay_Delete]
       @VBL01 int out,
       @EmpId int,
       @VBM03 tinyint,
       @VBL14 varchar(32),
       @VBL15 varchar(2),
       @EmpNo varchar(20),
       @EmpName varchar(20)
AS

IF exists(select * from VBL1 where VBL01 = @VBL01 and VBL05 <> 0)
BEGIN
   RAISERROR('该预交费用已经退费,不能退费.', 16, 1 )  WITH NOWAIT
   Return 1
END

IF exists(select * from VBL1 where VBL01 = @VBL01 and VAK01 > 0)
BEGIN
   RAISERROR('该预交费用已经结帐，不能退费.', 16, 1 )  WITH NOWAIT
   Return 1
END

DECLARE @Value int ,
        @VAA01 int ,
        @VAA07 int ,
        @VBM01 int ,
        @ErrId int ,
        @VBL13 numeric(18,4),
        @VBL02 varchar(20),
        @VBU01 int ,
        @VCB04 varchar(20) ,
        @CVBU14 numeric(18,4) ,
        @CVBU15 numeric(18,4) ,
        @VCA01 int ,
        @VBL01B int 

select @VBL13 = VBL13,@VAA07=VAA07, @VBU01 = VBU01 , @VBL01B = VBL01B from VBL1 with(nolock) where VBL01=@VBL01

IF dbo.GetEmpPermissionValue(104006007,@EmpId)=0 and exists(select * from VBM2 with(nolock) where @VAA07=VAA07 and ACF01=2 and VBM04-VBM05<@VBL13)
BEGIN
   RAISERROR('余额小于本次退费金额，你不具备退费的权限.', 16, 1 )  WITH NOWAIT
   Return 1
END



BEGIN TRAN 
--==================================================处理会员卡交预交款=======================================================
If (@VBL15 = '05') and (@VBU01 > 0)
BEGIN
  If exists(select * from VCA1 with(nolock) where VBU01 = @VBU01 and VBL01 = @VBL01B )
  BEGIN
      SELECT @VCB04 = VCB04 from VBU1 with(nolock) where VBU01 = @VBU01 
      EXEC Core_NewIDEx 'VBL1','VBL01',@Value out 
      SET @VBL01B = @Value
 
      INSERT INTO VBL1(VBL01 ,VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, BAQ03, BBO02, VBL11, VBL12, 
        VBL13, VBL14, VBL15, VBL18, VBL19, BCE01, BCE02, BCE03 ,VBL27 , FAF01 , VBU01 )
      
      SELECT @Value, 'C'+ Right('0000000000' + CAST(@Value as varchar(10)),10), '', 5, 0, VAA01, VAA07, BCK01, BAQ03, BBO02, VBL11, VBL12 ,
       @VBL13, @VBL14, @VBL15, GETDATE(), GETDATE(), @EmpId, @EmpNo , @EmpName , 1  , FAF01 , @VBU01
      from VBL1 with(nolock)
      where VBL01 = @VBL01 
  
      IF @@ERROR > 0 --如果发生错误，则回滚数据
      BEGIN
        if @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('保存病人预交款时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
      END
      
      Update VBU1 with(rowlock)
        SET VBU14 = VBU14 + @VBL13 
      where VBU01 = @VBU01 
      
      SELECT @CVBU14 = VBU14 , @CVBU15 = VBU15 from VBU1 with(nolock) where VBU01 = @VBU01 
      --写入会员交易跟踪
      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, 2, @VBU01 ,@VCB04 ,9, 0, 0 ,-@VBL13, @CVBU14 , @CVBU15 , @EmpName , getdate(), @EmpId , 0 , @Value , 0 
        
      IF @@ERROR > 0 --如果发生错误，则回滚数据
      BEGIN
        if @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('保存会员交易记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
      END   
  END
END
--======================================================预交款维护===========================================================        
EXEC Core_NewIDEx 'VBL1','VBL01', @Value out 

INSERT INTO VBL1(VBL01, VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, BAQ03, VBL12, VBL13, VBL14, VBL15,
        VAK01, VBL17, VBL18, VBL19, VBL01A, BCE01, BCE02, BCE03, VBL24, VBL25, VBL26 , VBL27 , VBL29 , VBU01 , VBL01B)
       	                  
SELECT @Value, VBL02, VBL03, VBL04, 1, VAA01, VAA07, BCK01, BAQ03, '预交款退费', -VBL13, @VBL14 , @VBL15 ,
        0, 0, getdate(), Getdate(), @VBL01, @EmpId, @EmpNo, @EmpName, VBL24, 0, VBL26 ,VBL27 , ISNULL(VBL29 , 0) , @VBU01 , @VBL01B 
from VBL1 with(nolock) 
where VBL01 = @VBL01 
       	                 
IF @@ERROR > 0 --如果发生错误则回滚数据
BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RAISERROR('冲病人交款记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
    Return 1
END
--更新原预交款状态与冲销ID
UPDATE VBL1 with(rowlock)
  SET VBL05 = 1 
WHERE VBL01 = @VBL01 
--==================================================更新病人余额=====================================================
SELECT @VAA01 = VAA01 , @VAA07 = VAA07 , @VBL13 = -VBL13 from VBL1 with(nolock) where VBL01 = @VBL01 
Exec @ErrId = HORate_VBM1_Update @VBM01 , @VAA01 , 2 , @VBL13 , 0 , 0 , @VAA07 

If @ErrId > 0 
BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRAN
    RAISERROR('更新病人余额时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
    Return 1
END
--==================================================更新病人预交款===================================================
Declare @BEP06 numeric(18,4) ,
        @BDP02 varchar(20) 
                
SELECT @BDP02 = BDP02 from VAE1 where VAE01 = @VAA07    
SELECT @BEP06 = dbo.GetCreditLimit(@VBL13 , 0 , @BDP02 , 0 , 0)
Update VAA1 with(rowlock)
   SET BEP06 = ISNULL(@BEP06 , 0) + ISNULL(BEP06 , 0)
 where VAA01 = @VAA01 
--返回退预交款ID
SET @VBL01 = @Value 
--====================================================================================================================
COMMIT TRAN 





GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--存储过程.HOPatient_PrePay_Entry
--===============================================================================================
--Author     <...fyq>
--Alter Date <...2015-01-12>
--Description<...预交款充值>
--2012.04.10 更新病人余额增加就诊ID
--2012.06.12 通过参数限制会员使用住院预交款功能
--2013.08.13 交预交款时按规则写入信用额度
--2014.07.16 支持会员卡交预交款，模拟从会员基本账户退掉所需要的预交款金额，然后交入预交款
--2014.12.31 减小锁粒度
--===============================================================================================
CREATE PROC  [HOPatient_PrePay_Entry]
    @VBL01 int out,         
    @VBL02 varchar(20),   
    @VBL03 varchar(20),   
    @VBL04 tinyint,            
    @VBL05 tinyint,            
    @VAA01 int,                
    @VAA07 int,                
    @BCK01 int,               
    @BAQ03 varchar(128),
    @BBO02 varchar(64),  
    @VBL11 varchar(20),   
    @VBL12 varchar(128), 
    @VBL13 numeric(18,4) ,
    @VBL14 varchar(32),    
    @VBL18 datetime,       
    @VBL19 datetime,       
    @BCE01 int,                
    @BCE02 varchar(20),   
    @BCE03 varchar(20),   
    @FAA01 int = 0 ,
    @FAF01 int = 0 ,
    @VBU01 int = 0               
 AS
 DECLARE @VBL15 varchar(2) ,
         @VCA01 int ,
         @VCB04 varchar(20) ,
         @CVBU14 numeric(18,4) ,
         @CVBU15 numeric(18,4) ,
         @VBL01B int        
                
 SELECT  @VBL15=AAO01 FROM BBP1 WHERE BBP02=@VBL14
 
 If exists(select * from VAE1 with(nolock) where VAE01 = @VAA07 and VAE44 = 5)
 BEGIN 
   RAISERROR('当前病人已经出院结算，不允许做预交款操作.', 16, 1)  WITH NOWAIT 
   RETURN 2
 END
 
 If exists(select * from VAE1 with(nolock) where VAE01 = @VAA07 and VAE44 = 9)
 BEGIN 
   RAISERROR('当前病人已经取消入院，不允许做预交款操作.', 16, 1)  WITH NOWAIT 
   RETURN 2
 END
 
 If ISNULL(@VBL15 , '') = ''
 BEGIN
   RAISERROR('支付方式不存在，请检查.', 16, 1)  WITH NOWAIT 
   RETURN 2
 END
 
 If exists(SELECT * from SYS_Parameters where programid=104006 and productid = 100 and ParamNo=1 and Value='1') and exists(select * from VBU1 where VAA01=@VAA01 and VBU21=1)
 BEGIN
   RAISERROR('当前住院患者是会员,请在会员管理中进行充值.', 16, 1)  WITH NOWAIT 
   RETURN 2
 END

 If exists(select * from VAE1 with(nolock) where VAE01 = @VAA07 and VAE44 > 0 and VAE11 > @VBL18)
 BEGIN
   RAISERROR('收款时间不能小于病人入院时间.', 16, 1)  WITH NOWAIT 
   RETURN 2
 END
 
 --如果是会员支付，判断其余额
 If (@VBL15 = '05') 
 BEGIN
   If not exists(SELECT * from VBU1 with(nolock) where VBU14 >= @VBL13 and VBU01 = @VBU01)
   BEGIN
     RAISERROR('当前会员卡余额不足或不存在会员账号.', 16, 1)  WITH NOWAIT 
     RETURN 2
   END
 END
 --==========================================================插入病人预交款记录==========================================================
 BEGIN TRAN     
    EXEC Core_NewIDEx 'VBL1','VBL01',@VBL01 out 
    SET @VBL01B = @VBL01 
    INSERT INTO VBL1(VBL01 ,VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, BAQ03, BBO02, VBL11, VBL12, 
      VBL13, VBL14, VBL15, VBL18, VBL19, BCE01, BCE02, BCE03 ,VBL27 , FAF01 , VBU01)
      
    VALUES(@VBL01, @VBL02, @VBL03, @VBL04, @VBL05, @VAA01, @VAA07, @BCK01, @BAQ03, @BBO02, @VBL11, @VBL12 ,
     @VBL13, @VBL14, @VBL15, @VBL18, GETDATE(), @BCE01, @BCE02, @BCE03 , 1  , @FAF01 , @VBU01)
       
    IF @@ERROR > 0 --如果发生错误，则回滚数据
    BEGIN
       if @@TRANCOUNT > 0 ROLLBACK TRAN
       RAISERROR('保存病人预交款时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
       Return 1
    END  
   --======================================================会员扣费，处理会员金额==========================================================
   If (@VBL15 = '05') and (@VBU01 > 0)
   BEGIN
      SELECT @VCB04 = VCB04 from VBU1 with(nolock) where VBU01 = @VBU01 
      EXEC Core_NewIDEx 'VBL1','VBL01',@VBL01 out 
      INSERT INTO VBL1(VBL01 ,VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, BAQ03, BBO02, VBL11, VBL12, 
        VBL13, VBL14, VBL15, VBL18, VBL19, BCE01, BCE02, BCE03 ,VBL27 , FAF01 , VBU01 )
      
      VALUES(@VBL01, 'C'+ Right('0000000000' + CAST(@VBL01 as varchar(10)),10), '', 5, 4, @VAA01, @VAA07, @BCK01, @BAQ03, @BBO02, @VBL11, @VBL12 ,
       -@VBL13, @VBL14, @VBL15, @VBL18, GETDATE(), @BCE01, @BCE02, @BCE03 , 1  , @FAF01 , @VBU01)
       
      IF @@ERROR > 0 --如果发生错误，则回滚数据
      BEGIN
        if @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('保存病人预交款时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
      END
      
      Update VBU1 with(rowlock)
        SET VBU14 = VBU14 - @VBL13 
      where VBU01 = @VBU01 
      SELECT @CVBU14 = VBU14 , @CVBU15 = VBU15 from VBU1 with(nolock) where VBU01 = @VBU01 
      --写入会员交易跟踪
      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, 2, @VBU01 ,@VCB04 ,9, 0, 0 ,@VBL13, @CVBU14 , @CVBU15 , @BCE03, getdate(), @BCE01 , 0 , @VBL01 , 0 
        
      IF @@ERROR > 0 --如果发生错误，则回滚数据
      BEGIN
        if @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('保存会员交易记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
      END  
      
      Update VBL1 with(rowlock)
        SET VBL01B = @VBL01 
      where VBL01 = @VBL01B       
   END
   --==========================================================更新病人余额===================================================================
   DECLARE  @VBM01 int ,
            @ErrId int ,
            @BDP02 varchar(20) ,
            @BEP06 numeric(18,4)
            
   Exec @ErrId = HORate_VBM1_Update @VBM01 , @VAA01 , 2 , @VBL13 , 0 , 0 , @VAA07   
   If @ErrId > 0 
   BEGIN
     if @@ROWCOUNT > 0 ROLLBACK TRAN 
     RAISERROR('更新病人余额时发生错误.', 16, 1)  WITH NOWAIT 
     RETURN 2
   END
   --==========================================================更新病人信用额度=================================================================    
   SELECT @BDP02 = BDP02 from VAE1 where VAE01 = @VAA07 
   SELECT @BEP06 = dbo.GetCreditLimit(@VBL13 , 0 , @BDP02 , 0 , 0)
   Update VAA1 with(rowlock) 
     SET BEP06 = ISNULL(@BEP06 , 0) + ISNULL(BEP06 , 0)
   where VAA01 = @VAA01 
  --=============================================================================================================================================  
 COMMIT TRAN







GO

--========================================================================
--Author     <...Fyq>
--Alter Date <...2014.10.21>
--Description<...根据条件计算病人信用额度>
--========================================================================
ALTER Function [GetCreditLimit](@VBM13 numeric(18,4) ,--本次预交金额
                               @VAA54 numeric(18,4) ,--本次担保金额
                               @BDP02 varchar(20),   --病人类别
                               @BCK01 int,           --病区ID
                               @IsDef smallint       --返回设置信用额度默认值
                               )
RETURNS numeric(18,4)                          
As 
BEGIN
  Declare @BEP08 numeric(18,4) ,
          @BEP09 numeric(18,4) ,
          @BEP06 numeric(18,4)
  
  SELECT @BEP08 = BEP08 , @BEP09 = BEP09 ,@BEP06=BEP06 
  from BEP1 
  where BDP02 = @BDP02 
  
  SET @BEP08 = ISNULL(@BEP08 , 0)   
  SET @BEP09 = ISNULL(@BEP09 , 0)
  SET @BEP06 = ISNULL(@BEP06 , 0)
  IF @BEP06=0
  begin
    SET @BEP06 = @VBM13 * @BEP09 + @VAA54 * @BEP08 
    SET @BEP06 = ISNULL(@BEP06 , 0)
  end else begin
    If @IsDef = 0
      set @BEP06 = 0
  end
  RETURN @BEP06
END     

GO