--药库药品盘点单
ALTER PROCEDURE [DSS_CheckForm_Check]
    @BCK01		    int--房库ID
  , @BillID	    	int--单据ID
  , @Assessor		varchar(20)--审核人
  , @TerminalNO		varchar(50)--终端编号
AS
DECLARE
    @Error	           int--错误号
  , @BuildDSFBill      int
  , @CheckType         int--盘点类型0:按规格盘点 1:按批次盘点
  , @DSK_ID            int--药库库存ID
  , @DSF_RowNum        int--损益单单序号
  , @DSF_MaxID         int--损益从表是最大Id
  , @DSF_BillID        int--损益单据ID
  , @DSF_BillNO		   varchar(30)
  , @_BatchNo          varchar(30)--批号
  , @DesQuantity       numeric(18, 6)--递减数量
  , @LastQuantity      numeric(18, 6)--库存数量
  , @AuditingDate      datetime--审核日期

  , @BBY01             int--药品ID
  , @Unit              varchar(20)--单位
  , @PackSize          numeric(18, 4)--单位包装
  , @BatchNo           varchar(30)--产品批号
  , @Quantity          numeric(18, 6)--赢亏数量
  , @PurchasePrice     numeric(18, 6)--进货价,进货价=帐面金额/帐面数量
  , @PurchaseAmount    numeric(18, 6)--进价金额
  , @RetailPrice       numeric(18, 6)--当前销售价
  , @PresellPrice	   numeric(18, 6)--零售价
  , @PresellAmount      numeric(18, 6)--零售金额
  , @CostPrice         numeric(18, 6)--当前成本价
  , @CostAmount        numeric(18, 6)--成本金额
  , @Remark            varchar(255)--备注
  , @iCount int, @i int, @jCount int, @j int

  SET @AuditingDate = GetDate()
  IF not exists(SELECT * FROM DSG1
    WHERE BCK01 = @BCK01 and BillID = @BillID and AuditingDate IS NULL)
  BEGIN
    RAISERROR('单据不存在或已复核，请刷新后重试！',16, 1) WITH NOWAIT
    RETURN
  END
BEGIN TRAN
  --更新单据主表审核标志
  UPDATE DSG1 SET AuditingDate = @AuditingDate, Assessor = @Assessor
  WHERE BCK01 = @BCK01 and BillID = @BillID
  IF (@@ROWCOUNT = 0) or (@@ERROR <> 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('更新单据主表审核标志时错误,请重试！', 16, 1) WITH NOWAIT
    RETURN 1
  END

  SET @BuildDSFBill = 1
  --安全起见,先删除创建的临时表.
  --IF exists(SELECT * FROM sysobjects WHERE name = 'Tmp_DSG2') DROP TABLE Tmp_DSG2
  if OBJECT_ID('tempdb..#Tmp_DSG2') is not null drop table #Tmp_DSG2
  
  --获取本次盘点赢亏数
  SELECT xRowNum, BBY01, Unit, PackSize, BatchNo, ChkQuantity - AccQuantity AS Quantity, PurchasePrice, CostPrice, RetailPrice, Remark
    , IDENTITY(int, 1, 1) xRow
  INTO #Tmp_DSG2 FROM DSG2
  WHERE BCK01 = @BCK01 and BillID = @BillID and ChkQuantity <> AccQuantity
  ORDER BY xRowNum
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    SET @BuildDSFBill = 0  --返回,说明本次盘点没有盘赢盘亏.
  END

  /***以下是处理本次盘赢盘亏:1.赢亏数自动生成损益单 2.损益单自动审核并更改库存.***/
  IF @BuildDSFBill = 1
  BEGIN
  --药库损益单单序号
  SET @DSF_RowNum = 0
  --获取药库损益单最大序列Id
  SET @DSF_MaxID = (SELECT ISNULL(MAX(ID),0) FROM DSF2)
  --产生新的药库损益单据ID
  SELECT @DSF_BillID = ISNULL(MAX(BillID),0)+1
		, @DSF_BillNO = RIGHT('0000000000' + CAST((ISNULL(MAX(BillID),0)+1) AS VARCHAR),8) 
	FROM DSF1 WHERE BCK01 = @BCK01

  --盘点类型0:按规格盘点 1:按批号盘点
  SET @CheckType = (SELECT ChkType FROM DSG1 WHERE BCK01 = @BCK01 and BillID = @BillID)

  select @iCount = MAX(xRow), @i = 1 from #Tmp_DSG2
  while @i <= @iCount
  begin
    SELECT @BBY01=BBY01, @Unit=Unit, @PackSize=PackSize, @BatchNo=BatchNo, @Quantity=Quantity
      , @PurchasePrice=PurchasePrice, @RetailPrice=RetailPrice, @CostPrice=CostPrice, @Remark=Remark
    FROM #Tmp_DSG2 --本次赢亏表
    where xRow = @i

    --盘点类型是按批号盘点的,必须加批号作为过滤条件.
    IF @CheckType = 0 SET @_BatchNo = '%' ELSE SET @_BatchNo = @BatchNo
    --盘点单盘赢,把盘赢数加到最后一次入库的库存上.  进货价、零售价都重新从库存表上取批次价格
    SELECT @DSK_ID = ID, @BatchNo = BatchNo, @LastQuantity = LastQuantity, @PresellPrice = PresellPrice, @PurchasePrice = PurchasePrice
    FROM DSK1
    WHERE ID = (SELECT MAX(ID) FROM DSK1 B WHERE B.BCK01 = @BCK01 and B.BBY01 = @BBY01 and B.BatchNo like @_BatchNo)
    IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
    BEGIN
      RAISERROR('提取药品Id: %d 产品批号: %s 的最后一次入库批次号时错误', 16, 1, @BBY01, @_BatchNo) WITH NOWAIT
      RETURN
    END

    --以下产生损益单
    IF (@Quantity > 0) or   --盘赢
       (@Quantity < 0 and @Quantity + @LastQuantity >= 0) --盘亏
    BEGIN
      --初始变量
      SET @DSF_MaxID = @DSF_MaxID + 1
      SET @DSF_RowNum = @DSF_RowNum +1
      --计算金额
      SET @PurchaseAmount = @Quantity * @PurchasePrice
      --SET @PresellAmount = @Quantity * @PresellPrice
      SET @CostAmount = @Quantity * @CostPrice

      --写药库损益单从表
      INSERT INTO DSF2(ID, BCK01, BillID
      , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity
      , PurchasePrice, PurchaseAmount, RetailPrice, RetailAmount, CostPrice, CostAmount
      , DSK_ID, Remark)
      VALUES(@DSF_MaxID, @BCK01, @DSF_BillID
      , @DSF_RowNum, @BBY01, @Unit, @PackSize, @BatchNo, @Quantity --赢亏数量,可能是正数可能是负数
      , @PurchasePrice, @PurchaseAmount, @RetailPrice, @RetailPrice * @Quantity, @CostPrice, @CostAmount
      , @DSK_ID, @Remark)
    END ELSE
    BEGIN --盘亏,且亏损数大于最后入库批次数.为防止负库存处理.
      --初始递减数量
      SET @DesQuantity = ABS(@Quantity) --此时的@Quantity一定是负数
      
      if OBJECT_ID('tempdb..#tmpDSK1') is not null drop table #tmpDSK1
      SELECT ID, BatchNo, LastQuantity, PurchasePrice, PresellPrice, IDENTITY(int, 1, 1) xRow
      into #tmpDSK1
      FROM DSK1
      WHERE BCK01 = @BCK01 and BBY01 = @BBY01 and LastQuantity > 0 --必须检索库存大于0的记录
        and BatchNo like @_BatchNo
      ORDER BY ID DESC

      select @j = 1, @jCount = max(xRow) from #tmpDSK1
	  while @j <= @jCount
	  begin
        select @DSK_ID=ID, @BatchNo=BatchNo, @LastQuantity=LastQuantity, @PurchasePrice=PurchasePrice
        , @PresellPrice=PresellPrice
        from #tmpDSK1
        where xRow = @j

        IF @DesQuantity <= @LastQuantity
        BEGIN
          --初始变量
          SET @DSF_MaxID = @DSF_MaxID + 1
          SET @DSF_RowNum = @DSF_RowNum +1
          --计算金额
          SET @PurchaseAmount = @DesQuantity * @PurchasePrice * -1
          SET @PresellAmount = @DesQuantity * @PresellPrice * -1
          SET @CostAmount = @DesQuantity * @CostPrice * -1

          --写药库损益单从表
          INSERT INTO DSF2(ID, BCK01, BillID
          , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity
          , PurchasePrice, PurchaseAmount, RetailPrice, RetailAmount, CostPrice, CostAmount
          , DSK_ID, Remark)
          VALUES(@DSF_MaxID, @BCK01, @DSF_BillID
          , @DSF_RowNum, @BBY01, @Unit, @PackSize, @BatchNo, @DesQuantity * -1 --赢亏数量,可能是正数可能是负数
          , @PurchasePrice, @PurchaseAmount, @RetailPrice, @RetailPrice * @DesQuantity * -1, @CostPrice, @CostAmount
          , @DSK_ID, @Remark)

          --更改递减数
          SET @DesQuantity = 0
          BREAK --中断，退出循环。
        END ELSE
        BEGIN
          --初始变量
          SET @DSF_MaxID = @DSF_MaxID + 1
          SET @DSF_RowNum = @DSF_RowNum +1
          --计算金额
          SET @PurchaseAmount = @LastQuantity * @PurchasePrice * -1
          SET @PresellAmount = @LastQuantity * @PresellPrice * -1
          SET @CostAmount = @LastQuantity * @CostPrice * -1

          --写药库损益单从表
          INSERT INTO DSF2(ID, BCK01, BillID
          , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity
          , PurchasePrice, PurchaseAmount, RetailPrice, RetailAmount, CostPrice, CostAmount
          , DSK_ID, Remark)
          VALUES(@DSF_MaxID, @BCK01, @DSF_BillID
          , @DSF_RowNum, @BBY01, @Unit, @PackSize, @BatchNo, @LastQuantity * -1 --赢亏数量,可能是正数可能是负数
          , @PurchasePrice, @PurchaseAmount, @RetailPrice, @RetailPrice * @LastQuantity * -1, @CostPrice, @CostAmount
          , @DSK_ID, @Remark)

          --更改递减数
          SET @DesQuantity = @DesQuantity - @LastQuantity
         END --if @DesQuantity

        SET @j = @j + 1
      end  --@jCount

    END -- if else

    set @i = @i + 1
  end --@iCount


  --写药库损益单主表,不要审核.
  INSERT INTO DSF1(
    BCK01, BillID, BillNO, Proposer, Ratifier, Warehouseman, MakeDate, Maker,
    Abstract, BillType, TradeType, DSG_BillNO, UserID, TerminalNO)
  SELECT
    BCK01, @DSF_BillID, @DSF_BillNO, Checker, Ratifier, Warehouseman, MakeDate, Maker,
    Abstract, 1, 1, BillNO, UserID, @TerminalNO
  FROM DSG1
  WHERE BCK01 = @BCK01 and BillID = @BillID
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('盘点盈亏数量生成的损益单,保存主表时错误！', 16, 1) WITH NOWAIT
    RETURN
  END

  --药库损益单,审核并修改库存.
  EXEC @Error = DSS_ProfitLossForm_Check @BCK01, @DSF_BillID, @Assessor, @TerminalNO
  IF @Error > 0
  BEGIN
    ROLLBACK TRAN
    RAISERROR('盘点盈亏数量生成的损益单,审核时出错！',16, 1) WITH NOWAIT
    RETURN 4
  END

  END

  if @CheckType = 0
  UPDATE A SET A.ChkPresellAmount = (case when ISNULL(A.BBY19, 0) = 0 then A.ChkQuantity * A.RetailPrice else A.AccPresellAmount + ISNULL(A1.PresellAmount, 0) end)
    , A.ChkPurchaseAmount = A.AccPurchaseAmount + ISNULL(A1.PurchaseAmount, 0)
  FROM DSG2 A join DSG1 B on B.BCK01 = A.BCK01 and B.BillID = A.BillID
  join BBY1 Y on Y.BBY01 = A.BBY01
  LEFT JOIN (SELECT F1.BCK01, F1.BillID, F1.DSG_BillNO, F2.BBY01
    , SUM(K.PurchasePrice * F2.Quantity) as PurchaseAmount, SUM(K.PresellPrice * F2.Quantity) as PresellAmount
		FROM DSF1 F1 
		  LEFT JOIN DSF2 F2 ON F2.BCK01 = F1.BCK01 AND F2.BillID = F1.BillID
		  LEFT JOIN DSK1 K ON K.ID = F2.DSK_ID 
		GROUP BY F2.BBY01, F1.BCK01, F1.BillID, F1.DSG_BillNO
    ) as A1 ON A1.BBY01 = A.BBY01 AND A1.DSG_BillNO = B.BillNO AND A1.BCK01 = B.BCK01
  WHERE A.BCK01 = @BCK01 AND A.BillID = @BillID
  else
  UPDATE A SET A.ChkPresellAmount = (case when ISNULL(A.BBY19, 0) = 0 then A.ChkQuantity * A.RetailPrice else A.AccPresellAmount + ISNULL(A1.PresellAmount, 0) end)
    , A.ChkPurchaseAmount = A.AccPurchaseAmount + ISNULL(A1.PurchaseAmount, 0)
  FROM DSG2 A join DSG1 B on B.BCK01 = A.BCK01 and B.BillID = A.BillID
  join BBY1 Y on Y.BBY01 = A.BBY01
  LEFT JOIN (SELECT F1.BCK01, F1.BillID, F1.DSG_BillNO, F2.BBY01, F2.BatchNo
			 , SUM(K.PurchasePrice * F2.Quantity) as PurchaseAmount, SUM(K.PresellPrice * F2.Quantity) as PresellAmount
			 FROM DSF1 F1 
				LEFT JOIN DSF2 F2 ON F2.BCK01 = F1.BCK01 AND F2.BillID = F1.BillID
				LEFT JOIN DSK1 K ON K.ID = F2.DSK_ID 
			 GROUP BY F2.BBY01, F1.BCK01, F1.BillID, F1.DSG_BillNO, F2.BatchNo
    ) as A1 ON A1.BBY01 = A.BBY01 AND A1.DSG_BillNO = B.BillNO AND A1.BCK01 = B.BCK01 and A1.BatchNo = A.BatchNo
  WHERE A.BCK01 = @BCK01 AND A.BillID = @BillID

COMMIT TRAN