SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--===============================================================================
--Alter Date 2014.07.15
--门诊药房病人退药
--本过程由[HORate_VAK1_Delete]调用
--2013.01.23 增加调用场合
--2013.03.05 增加会员病人药房退药
--===============================================================================
ALTER PROCEDURE [dbo].[DPS_ExportForm_Update2]
    @BCK01              int--药房ID, 禁止为空
  --, @Doc                ntext--表单内容(退药内容)
  , @VAK01A             int--冲销结帐ID , 禁止为空
  , @DeptID             int--就诊科室Id,  禁止为空
  , @Accepter           varchar(20)--病人姓名
  , @Sender             varchar(20)--发药人
  , @UserID             int--操作员ID
  , @TerminalNO         varchar(50)--终端号
  , @VAI01A             int = 0 --单据ID
AS
DECLARE
    @Error1             int
  , @TradeID            int --流水号
  , @iCount             int--计数器
  , @iTatol             int--总行数
  , @BillID             int--单据ID
  , @VAJ01              int--记费ID
  , @VAJ01B             int--关联ID 
  , @BBY01              int--药品ID
  , @DPD_MaxID          int--单据最大序号  
  , @BillNO             varchar(30)--发药单号
  
  Create table #TmpVAJ1_2
  ( 
    xRowNum int identity ,
    VAA01 int ,
    VAJ01 int ,
    VAJ09 int ,
    BBY01 int ,
    VAJ25 numeric(18,4),
    VAJ01B int ,
    VAJ32 numeric(18,6) ,
    VAJ33 numeric(18,6) ,
    VAJ54 varchar(255)
  )
  --门诊退费药品更新库存
  If ISNULL(@VAK01A , 0 ) > 0
  BEGIN      
     INSERT INTO #TmpVAJ1_2(VAA01 , VAJ01, VAJ09, BBY01, VAJ25, VAJ01B, VAJ54 , VAJ32 , VAJ33)    
     SELECT VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ01B, '门诊退费' VAJ54  , VAJ32 , VAJ33
     FROM 
       (SELECT a.VAA01, a.VAJ01, a.VAJ09, a.BBY01, a.VAJ25, case when ISNULL(a.VAJ01B ,0) > 0 then a.VAJ01B else a.VAJ09 end as VAJ01B, VAJ32 , VAJ33 
        FROM VAJ1 a 
        join VBY1 b on b.VAJ01 = a.VAJ09      
     WHERE b.BCK01 = @BCK01 AND b.VBY09 = 1 and a.VAK01 = @VAK01A and a.BDN01 <= '3' and a.VAJ48 = 0 AND a.VAJ53 = 0 and a.VAJ25 < 0
     ) A 
   END ELSE If ISNULL(@VAI01A , 0) > 0 --药房退药药品更新库存
   BEGIN
     INSERT INTO #TmpVAJ1_2(VAA01 , VAJ01, VAJ09, BBY01, VAJ25, VAJ01B, VAJ54 , VAJ32 , VAJ33)   
     SELECT VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ01B, '药房退药' VAJ54 , VAJ32 , VAJ33 
     FROM 
       (SELECT a.VAA01, a.VAJ01, a.VAJ09, a.BBY01, a.VAJ25, a.VAJ01B , VAJ32 , VAJ33
        FROM VAJ1 a    
     WHERE a.VAI01 = @VAI01A  and a.BCK01D = @BCK01 and a.BDN01 <= '3' and a.VAJ48 = 0 AND a.VAJ53 = 0 and a.VAJ25 < 0
     ) A 
   END
   
  IF not exists(SELECT * FROM #TmpVAJ1_2)
  BEGIN
    RAISERROR('没有找到要提交的退药内容，请重新后重试！', 16, 1) WITH NOWAIT
    RETURN 1
  END

  --检查是否已发药或销帐,网络环境下,防止用户抢发...
  IF exists(SELECT * FROM VAJ1 J WHERE exists(SELECT * FROM #TmpVAJ1_2 V WHERE V.VAJ01 = J.VAJ01) and (J.VAJ48 <> 0 or J.VAJ53 <> 0))
  BEGIN
    RAISERROR('提交的内容中有已经执行或已经销帐的记录，请刷新后重试！', 16, 1) WITH NOWAIT
    RETURN 2
  END
  
  --IF exists(SELECT * FROM #TmpVAJ1_2 WHERE VAJ09 <= 0 or VAJ25 > 0)
  IF exists(SELECT * FROM #TmpVAJ1_2 WHERE VAJ01B <= 0 or VAJ25 > 0)
  BEGIN
    RAISERROR('提交的内容中有非法数据：关联Id必须大于0并且数据必须小于0', 16, 1) WITH NOWAIT
    RETURN 1
  END
 
  --初始化变量
  --SELECT @iTatol = COUNT(*), @iCount = 1 FROM #TmpVAJ1_2
  --发药单据号
  SELECT @BillNO = RIGHT('0000000000' + CAST((ISNULL(MAX(BillID),0)+1) AS VARCHAR),8) FROM V_DPD1_FULL WHERE BCK01 = @BCK01
  --产生新的单据ID
  EXEC Core_NewId_DPD1_ID_B @BCK01, @BillID OUT
  
  --===============================2014.05.08退药时取原发药批次的数据及数量，退回到原对应批次上面====================
  SELECT  @BillID BillID,identity(int , 1 ,1) as xRowNum , d.BBY01 , @BCK01 as BCK01 , j.VAJ32 , j.VAJ33 
       ,  d.Unit, d.PackSize, d.BatchNo, -d.Quantity as VAJ25, d.PurchasePrice, PurchaseAmount
       , j.VAA01, j.VAJ01, j.VAJ09, d.DPK_ID, j.VAJ54 , j.VAJ01B into #tmpDPD2_2
      FROM #TmpVAJ1_2 j join V_DPD2_FULL d   --对于病人退药,要找出之前的发药信息取出BatchNo/DPK_ID
        ON j.BBY01 = d.BBY01 and j.VAJ01B = d.VAJ01 and j.VAA01 = d.VAA01 and d.BCK01 = @BCK01 
--初始化变量
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM #tmpDPD2_2        
BEGIN TRAN
  --循环逐条处理记录
  WHILE @iCount <= @iTatol
  BEGIN
    --SELECT @VAJ01 = VAJ01, @VAJ01B = VAJ01B, @BBY01 = BBY01 FROM #TmpVAJ1_2 WHERE xRowNum = @iCount
    SELECT @VAJ01 = VAJ01, @VAJ01B = VAJ01B, @BBY01 = BBY01 FROM #tmpDPD2_2 WHERE xRowNum = @iCount
    --获取发药单最大列ID
    EXEC Core_NewId_DPD2_ID @DPD_MaxID OUT
    
    --写药房发药单从表
    INSERT INTO DPD2(ID, BCK01, BillID  --对于病人退药,首先确认药房是否发过此药品,没有发过的,禁止从此药房退.
     , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
     , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, VAJ36, VAJ37, VAJ38, DPK_ID, Remark)     
    SELECT TOP 1 @DPD_MaxID, @BCK01, @BillID
     , @iCount, j.BBY01, j.Unit, j.PackSize, j.BatchNo, j.VAJ25, j.PurchasePrice, j.VAJ25*j.PurchasePrice
     , j.VAA01, j.VAJ01, 0, j.VAJ32, j.VAJ33, j.VAJ32*j.VAJ25, j.VAJ33*j.VAJ25, j.VAJ33*j.VAJ25, j.DPK_ID, j.VAJ54
    FROM #tmpDPD2_2 j 
    /*join V_DPD2_Full d   --对于病人退药,要找出之前的发药信息取出BatchNo/DPK_ID
      ON j.BBY01 = d.BBY01 and j.VAJ01B = d.VAJ01 and j.VAA01 = d.VAA01 */           
    WHERE --j.BCK01 = @BCK01 and j.BBY01 = @BBY01 
      --and j.xRowNum = @iCount   
      j.xRowNum = @iCount 
    IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('处理退药记费Id: %d, 关联Id: %d时错误。原因：药房未找到原始发药记录，不能本药房退药。', 16, 1, @VAJ01, @VAJ01B) WITH NOWAIT
      RETURN @@ERROR
    END  
    SET @iCount = @iCount + 1 --计数器加1
  END --while

  --写部门发药单主表
  INSERT INTO DPD1(BCK01, BillID
   , BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender
   , MakeDate, Maker, Abstract, BillType, TradeType, TradeTerm, UserID, TerminalNO)
  VALUES(@BCK01, @BillID
   , @BillNO, @DeptID, @Accepter, @Sender, @Sender, @Sender
   , Getdate(), @Sender, '', 2, 1, '', @UserID, @TerminalNO)
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存单据表头记录时错误,请重试！', 16, 1) WITH NOWAIT
    RETURN @@ERROR
  END

  --审核药房发药单,同时更新库存表.
  EXEC @Error1 = DPS_ExportForm_Check @BCK01, @BillID, @Sender, @TerminalNO, @TradeID OUT
  IF @Error1 > 0
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存单据表体未能通过审核,错误号%d', 16, 1, @Error1) WITH NOWAIT
    RETURN @Error1
  END
COMMIT TRAN