SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--=============================================================================
--本过程适用于门诊病人及住院病人发药
--本过程支持按产品批号出库(如果@BatchNo传入是空值,将检索所有批号库存).
--部门药品发药单体--循环器(先进先出/先失效先出/后进先出)
--2013.04.22 出错时提示加上药品名称
--2014.11.27 发药选择批次时过滤掉过期的批次
--2014.12.31 减小所粒度
--205.06.30  修改合计数量刚好等于剩余库存数量的情况
--==============================================================================
ALTER PROCEDURE [DPS_ExportForm_Track]
    @BCK01             int--部门ID
  , @BillID            int--单据ID
  , @ExpType           int--出库算法,0.先进先出 1.失失效先出 2.后进先出

  , @VAA01             int--病人ID
  , @VAJ01             int--记费ID
  , @BBY01             int--药品ID
  , @BatchNo           varchar(30)--产品批号
  , @SendOutNum        numeric(18, 6)--发药数量
AS
DECLARE
    @iCount            int--计数器
  , @iTatol            int--总行数
  , @DPK_ID            int--部门库存ID
  , @Row_Max           int--单据最大行号
  , @DPD_MaxID         int--单据最大序号
  , @Unit              varchar(20)--单位
  , @PackSize          numeric(18, 4)--单位包装
  , @LastQuantity      numeric(18, 6)--库存数量
  , @PurchasePrice     numeric(18, 6)--进货价
  , @Remark            varchar(255)--备注
  , @BBY05             varchar(128)--药品名称
  , @CDate             datetime --当前时间
  --初始产品批号
  IF @BatchNo = '' SET @BatchNo =  '%'
  --取药品名称
  SELECT @BBY05 = BBY05 from BBY1 with(nolock)  where BBY01 = @BBY01 
  SET @CDate = GETDATE()
  --建循环用临时表
  CREATE TABLE #TmpDPK(UID int identity(1, 1)
    , ID int
    , BBY01 int
    , Unit varchar(20)
    , PackSize numeric(18, 4)
    , BatchNO varchar(30)
    , LastQuantity  numeric(18, 6)
    , PurchasePrice numeric(18, 6)
    , primary key nonclustered(UID)
   )
  --建库存数临时表
  SELECT K.ID, K.BBY01, K.Unit, K.PackSize, K.BatchNO, K.LastQuantity - ISNULL(D.Qty,0) AS LastQuantity, K.PurchasePrice, K.ExpiryDate INTO #DPK1
  FROM DPK1 K with(nolock) left join (SELECT BBY01, DPK_ID, SUM(Quantity) Qty FROM DPD2 with(nolock)  WHERE BCK01 = @BCK01 and BillID = @BillID and BBY01 = @BBY01 GROUP BY BBY01, DPK_ID) D
    ON K.ID = D.DPK_ID and K.BBY01 = D.BBY01
  WHERE K.BCK01 = @BCK01 and K.BBY01 = @BBY01 and K.LastQuantity>0
    and k.ExpiryDate > @CDate 
    and K.LastQuantity - ISNULL(D.Qty,0) >= 0	
    and K.BatchNO like @BatchNo

  IF @ExpType = 0 --先进先出
  BEGIN
    INSERT INTO #TmpDPK(ID, BBY01, Unit, PackSize, BatchNO, LastQuantity,  PurchasePrice)
    SELECT ID, BBY01, Unit, PackSize, BatchNO, LastQuantity, PurchasePrice FROM #DPK1
    ORDER BY ID --排序，非常重要。
  END ELSE
  IF @ExpType = 1 --先失效先出
  BEGIN
    INSERT INTO #TmpDPK(ID, BBY01, Unit, PackSize, BatchNO, LastQuantity,  PurchasePrice)
    SELECT ID, BBY01, Unit, PackSize, BatchNO, LastQuantity, PurchasePrice FROM #DPK1
    ORDER BY ExpiryDate --排序，非常重要。
  END ELSE
  IF @ExpType = 2 --后进先出
  BEGIN
    INSERT INTO #TmpDPK(ID, BBY01, Unit, PackSize, BatchNO, LastQuantity,  PurchasePrice)
    SELECT ID, BBY01, Unit, PackSize, BatchNO, LastQuantity, PurchasePrice FROM #DPK1
    ORDER BY ID DESC --排序，非常重要。
  END --if
  IF not exists(SELECT * FROM #TmpDPK)
  BEGIN
    RAISERROR('没有找到待扣减库存记录，请刷新后重试！药房ID：%d,药品ID：%d，药品：%s,单据ID：%d', 16, 1,@BCK01,@BBY01,@BBY05 ,@BillID) WITH NOWAIT
    RETURN 1
  END

  --删除临时表
  DROP TABLE #DPK1
  --初始化变量
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM #TmpDPK
  --获取发药单最大行号
  SET @Row_Max = ISNULL((SELECT MAX(xRowNum) FROM DPD2 with(nolock)  WHERE BCK01 = @BCK01 and BillID = @BillID),0)
BEGIN TRAN
  --循环逐条处理记录
  WHILE @iCount <= @iTatol
  BEGIN
    --提取当前记录到变量中
    SELECT @DPK_ID = ID, @Unit = Unit, @PackSize = PackSize, @BatchNo = BatchNo, @LastQuantity = LastQuantity, @PurchasePrice = PurchasePrice
    FROM #TmpDPK WHERE UID = @iCount
    --每增加一条记录，序号加1
    SET @Row_Max = @Row_Max + 1
    --获取发药单最大序列Id
    EXEC Core_NewId_DPD2_ID @DPD_MaxID OUT

    --发药数量小于库存数量时
    IF @SendOutNum <= @LastQuantity
    BEGIN
      --写药房发药单从表
      INSERT INTO DPD2(ID, BCK01, BillID
       , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
       , VAA01, VAJ01, VAJ09, DPK_ID, Remark)
      VALUES(@DPD_MaxID, @BCK01, @BillID
       , @Row_Max, @BBY01, @Unit, @PackSize, @BatchNo, @SendOutNum, @PurchasePrice, @SendOutNum*@PurchasePrice
       , @VAA01, @VAJ01, 0, @DPK_ID, @Remark)
      IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
      BEGIN
        ROLLBACK TRAN
        RAISERROR('保存发药内容%d ,药品%s时错误,请重试！', 16, 1, @BBY01 , @BBY05) WITH NOWAIT
        RETURN 2
      END
      --清零
      SET @SendOutNum  = 0
      BREAK --中止循环
    END

    --写药房发药单从表
    INSERT INTO DPD2(ID, BCK01, BillID
     , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
     , VAA01, VAJ01, VAJ09, DPK_ID, Remark)
    VALUES(@DPD_MaxID, @BCK01, @BillID
     , @Row_Max, @BBY01, @Unit, @PackSize, @BatchNo, @LastQuantity, @PurchasePrice, @LastQuantity*@PurchasePrice
     , @VAA01, @VAJ01, 0, @DPK_ID, @Remark)
    IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('保存发药内容%d,药品%s时错误,请重试！', 16, 1, @BBY01 , @BBY05) WITH NOWAIT
      RETURN 3
    END

    --剩余未发数量/金额
    SET @SendOutNum  = @SendOutNum  - @LastQuantity
    SET @iCount = @iCount + 1 --循环计数器加1
  END--while

  --删除临时表
  DROP TABLE #TmpDPK
  --判断是否发完
  IF @SendOutNum <> 0
  BEGIN
    DECLARE @YS varchar(10)
    SET @YS = CAST(@SendOutNum AS varchar(10))
    ROLLBACK TRAN
    RAISERROR('库存异常错误: 药品Id: %d,药品%s的数量还有: %s 未循环处理完，可能是库存不足，请刷新后重试。', 16, 1, @BBY01,@BBY05 , @YS) WITH NOWAIT
    RETURN 4
  END
   
COMMIT TRAN