/****** Object:  StoredProcedure [DSS_MonthlyWastebook_Update]    Script Date: 2020-11-18 10:07:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--药库药品月结帐-结帐
ALTER PROCEDURE [DSS_MonthlyWastebook_Update]
    @BCK01          int--药库Id
  , @iYear          int--年
  , @iMonth         int--月
  , @Maker          varchar(20)--结帐人
  , @Remark         varchar(255)--备注
  , @TerminalNO     varchar(50)--终端编号
AS
DECLARE
    @PriYear        int--前年
  , @PriMonth       int--前月
  , @StartDate      datetime--开始日期
  , @EndDate        datetime--结束日期
  , @Str_EndDete    varchar(20)

  --取前一个年月
  IF @iMonth = 1
  BEGIN
    SET @PriYear = @iYear - 1
    SET @PriMonth = 12
  END ELSE
  BEGIN
    SET @PriYear = @iYear
    SET @PriMonth = @iMonth - 1
  END

  IF exists(SELECT * FROM DSM1
    WHERE BCK01 = @BCK01 and iYear = @iYear and iMonth = @iMonth)
  BEGIN
    RAISERROR('%d年%d月的月结帐数据已存在，如果要重新月结，请先解除。', 16, 1, @iYear, @iMonth) WITH NOWAIT
    RETURN
  END

  --本月断帐日期
  SET @EndDate = dbo.GetMonthSetDate(1, @BCK01, @iYear, @iMonth)--1=药品系统

  IF DATEDIFF(DAY, @EndDate, GETDATE()) = 0
    SET @EndDate = GETDATE() --如果当天做月结 完成月结后  医院希望当天能继续做业务  所以 月结时间需要精确到秒
  
  IF DATEDIFF(DAY, @EndDate, GetDate()) < 0
  BEGIN
    SET @Str_EndDete = CONVERT(VARCHAR(10), @EndDate, 120)
    RAISERROR('执行月结帐日期必须大于等于设定的断帐日期，请在 %s 后再执行月结帐。', 16, 1, @Str_EndDete) WITH NOWAIT
    RETURN
  END
  --前一个月断帐日期
  SET @StartDate = ISNULL((SELECT EndDate FROM DSM1 WHERE BCK01 = @BCK01 and iYear = @PriYear and iMonth = @PriMonth), '1900-01-01 00:00:01')
BEGIN TRAN
  --写月结帐主表数据，只有一个月的数据。
  INSERT INTO DSM1(BCK01, iYear, iMonth, StartDate, EndDate, Maker, Remark, TerminalNO)
  VALUES(@BCK01, @iYear, @iMonth, @StartDate, @EndDate, @Maker, @Remark, @TerminalNO)
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存%d年%d月的未结帐基本信息时错误', 16, 1, @iYear, @iMonth) WITH NOWAIT
    RETURN 1
  END

  if OBJECT_ID('tempdb..#DSK2') is not null drop table #DSK2
  select * into #DSK2 from (
  select ROW_NUMBER() over(partition by BBY01, DSK1_ID order by sysdate desc) as xRow, * from DSK2 where sysdate <= @EndDate
  ) A where A.xRow = 1

  if OBJECT_ID('tempdb..#DSL1') is not null drop table #DSL1
  select A.BBY01, A.BCK01, A.xTypeID, A.DSK_ID, A.BillType, A.AuditingDate
    , A.Quantity, A.PurchasePrice, A.PurchaseAmount, A.PresellPrice, A.RetailPrice, A.RetailAmount,A.PresellAmount
  into #DSL1
  from V_DSL1 A
  WHERE BCK01 = @BCK01
    AND CONVERT(VARCHAR, AuditingDate, 121) <= CONVERT(VARCHAR, @EndDate, 121)
  
  update A set A.PresellPrice = B.PresellPrice
  from #DSL1 A join #DSK2 B on B.BBY01 = A.BBY01 and B.DSK1_ID = A.DSK_ID
  
  --写月结帐从表数据，只有一个月的数据。
  INSERT INTO DSM2(BCK01, iYear, iMonth, BBY01, Maker
    , Quantity_i,  PurchaseAmount_i,  PresellAmount_i, RetailAmount_i
    , Quantity_2,  PurchaseAmount_2,  PresellAmount_2, RetailAmount_2 --药库进货单
    , Quantity_3,  PurchaseAmount_3,  PresellAmount_3, RetailAmount_3 --药库退货单
    , Quantity_4,  PurchaseAmount_4,  PresellAmount_4, RetailAmount_4 --药库出库单
    , Quantity_5,  PurchaseAmount_5,  PresellAmount_5, RetailAmount_5 --药库退库单

    , Quantity_6P, PurchaseAmount_6P, PresellAmount_6P, RetailAmount_6P --药库损益单_益
    , Quantity_6L, PurchaseAmount_6L, PresellAmount_6L, RetailAmount_6L --药库损益单_损
    , Quantity_6LF,PurchaseAmount_6LF,PresellAmount_6LF, RetailAmount_6LF --制剂消耗
    , Quantity_7W, PurchaseAmount_7W, PresellAmount_7W, RetailAmount_7W --药库盘点单_赢
    , Quantity_7L, PurchaseAmount_7L, PresellAmount_7L, RetailAmount_7L --药库盘点单_亏

    , Quantity_8,  PurchaseAmount_8,  PresellAmount_8, RetailAmount_8 --药库调入单
    , Quantity_9,  PurchaseAmount_9,  PresellAmount_9, RetailAmount_9 --药库调出单
    , PurchaseAmount_10 --进价差额
    , RetailAmount_10 --销售价差额
	, PresellAmount_10 --预售价差额
   )
  SELECT BCK01, @iYear, @iMonth, BBY01, @Maker
   , 0, 0, 0, 0
   --药库进货单
   , SUM(CASE xTypeID WHEN 2 THEN Quantity       ELSE 0 END)
   , SUM(CASE xTypeID WHEN 2 THEN PurchaseAmount ELSE 0 END)
   , SUM(CASE xTypeID WHEN 2 THEN PresellAmount   ELSE 0 END)
   , SUM(CASE xTypeID WHEN 2 THEN RetailAmount ELSE 0 END)
   --药库退货单
   , SUM(CASE xTypeID WHEN 3 THEN Quantity       ELSE 0 END)
   , SUM(CASE xTypeID WHEN 3 THEN PurchaseAmount ELSE 0 END)
   , SUM(CASE xTypeID WHEN 3 THEN PresellAmount   ELSE 0 END)
   , SUM(CASE xTypeID WHEN 3 THEN RetailAmount ELSE 0 END)
   --药库出库单
   , SUM(CASE xTypeID WHEN 4 THEN Quantity       ELSE 0 END)
   , SUM(CASE xTypeID WHEN 4 THEN PurchaseAmount ELSE 0 END)
   , SUM(CASE xTypeID WHEN 4 THEN PresellAmount   ELSE 0 END)
   , SUM(CASE xTypeID WHEN 4 THEN RetailAmount ELSE 0 END)
   --药库退库单
   , SUM(CASE xTypeID WHEN 5 THEN Quantity       ELSE 0 END)
   , SUM(CASE xTypeID WHEN 5 THEN PurchaseAmount ELSE 0 END)
   , SUM(CASE xTypeID WHEN 5 THEN PresellAmount  ELSE 0 END)
   , SUM(CASE xTypeID WHEN 5 THEN RetailAmount ELSE 0 END)
   --药库损益单_益
   , SUM(CASE xTypeID WHEN 6 THEN (CASE WHEN Quantity > 0 THEN Quantity ELSE 0 END) ELSE 0 END)
   , SUM(CASE xTypeID WHEN 6 THEN (CASE WHEN PurchaseAmount > 0 THEN PurchaseAmount ELSE 0 END) ELSE 0 END)
   , SUM(CASE xTypeID WHEN 6 THEN (CASE WHEN PresellAmount > 0 THEN PresellAmount ELSE 0 END) ELSE 0 END)
   , SUM(CASE xTypeID WHEN 6 THEN (CASE WHEN RetailAmount > 0 THEN RetailAmount ELSE 0 END) ELSE 0 END)
   --药库损益单_损
   --, ABS(SUM(CASE xTypeID WHEN 6 THEN (CASE WHEN Quantity < 0 THEN Quantity ELSE 0 END) ELSE 0 END))
   --, ABS(SUM(CASE xTypeID WHEN 6 THEN (CASE WHEN PurchaseAmount < 0 THEN PurchaseAmount ELSE 0 END) ELSE 0 END))
   --, ABS(SUM(CASE xTypeID WHEN 6 THEN (CASE WHEN PresellAmount < 0 THEN PresellAmount ELSE 0 END) ELSE 0 END))
   , abs(sum((case when (xTypeID = 6) and (BillType <> 2) and (Quantity < 0) then Quantity else 0 end)))
   , abs(sum((case when (xTypeID = 6) and (BillType <> 2) and (PurchaseAmount < 0) then PurchaseAmount else 0 end)))
   , abs(sum((case when (xTypeID = 6) and (BillType <> 2) and (PresellAmount < 0) then PresellAmount else 0 end)))
   , abs(sum((case when (xTypeID = 6) and (BillType <> 2) and (RetailAmount < 0) then RetailAmount else 0 end)))
   --制剂消耗
   , abs(sum(case when (xTypeID = 6) and (BillType = 2) then Quantity else 0 end))
   , abs(sum(case when (xTypeID = 6) and (BillType = 2) then PurchaseAmount else 0 end))
   , abs(sum(case when (xTypeID = 6) and (BillType = 2) then PresellAmount else 0 end))
   , abs(sum(case when (xTypeID = 6) and (BillType = 2) then RetailAmount else 0 end))
   --药库盘点单_赢
   , SUM(CASE xTypeID WHEN 7 THEN (CASE WHEN Quantity > 0 THEN Quantity ELSE 0 END) ELSE 0 END)
   , SUM(CASE xTypeID WHEN 7 THEN (CASE WHEN PurchaseAmount > 0 THEN PurchaseAmount ELSE 0 END) ELSE 0 END)
   , SUM(CASE xTypeID WHEN 7 THEN (CASE WHEN PresellAmount > 0 THEN PresellAmount ELSE 0 END) ELSE 0 END)
   , SUM(CASE xTypeID WHEN 7 THEN (CASE WHEN RetailAmount > 0 THEN RetailAmount ELSE 0 END) ELSE 0 END)
   --药库盘点单_亏
   , ABS(SUM(CASE xTypeID WHEN 7 THEN (CASE WHEN Quantity < 0 THEN Quantity ELSE 0 END) ELSE 0 END))
   , ABS(SUM(CASE xTypeID WHEN 7 THEN (CASE WHEN PurchaseAmount < 0 THEN PurchaseAmount ELSE 0 END) ELSE 0 END))
   , ABS(SUM(CASE xTypeID WHEN 7 THEN (CASE WHEN PresellAmount < 0 THEN PresellAmount ELSE 0 END) ELSE 0 END))
   , ABS(SUM(CASE xTypeID WHEN 7 THEN (CASE WHEN RetailAmount < 0 THEN RetailAmount ELSE 0 END) ELSE 0 END))
   --药库调入单
   , SUM(CASE xTypeID WHEN 8 THEN Quantity       ELSE 0 END)
   , SUM(CASE xTypeID WHEN 8 THEN PurchaseAmount ELSE 0 END)
   , SUM(CASE xTypeID WHEN 8 THEN PresellAmount   ELSE 0 END)
   , SUM(CASE xTypeID WHEN 8 THEN RetailAmount       ELSE 0 END)
   --药库调出单
   , ABS(SUM(CASE xTypeID WHEN 9 THEN Quantity       ELSE 0 END))
   , ABS(SUM(CASE xTypeID WHEN 9 THEN PurchaseAmount ELSE 0 END))
   , ABS(SUM(CASE xTypeID WHEN 9 THEN PresellAmount   ELSE 0 END))
   , ABS(SUM(CASE xTypeID WHEN 9 THEN RetailAmount       ELSE 0 END))
   --进价差额
   , SUM(CASE xTypeID WHEN 10 THEN PurchaseAmount ELSE 0 END)
   --销售价差额
   , SUM(CASE xTypeID WHEN 11 THEN RetailAmount       ELSE 0 END)
   --预售价差额
   , SUM(CASE xTypeID WHEN 10 THEN PresellAmount       ELSE 0 END)
  FROM #DSL1
  WHERE convert(varchar, AuditingDate, 121) > convert(varchar, @StartDate, 121)
  GROUP BY BCK01, BBY01
  IF @@ERROR > 0
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存%d年%d月的未结帐数据时错误', 16, 1, @iYear, @iMonth) WITH NOWAIT
    RETURN 2
  END

  --取前一月数据,如果本月没有发生交易，就把上月结存转入本月中。
  IF exists(SELECT * FROM DSM2 WHERE BCK01 = @BCK01 and iYear = @PriYear and iMonth = @PriMonth)
  BEGIN
    --前一月数据输出到临时表
    SELECT BBY01, Quantity_E, PurchaseAmount_E, PresellAmount_E, RetailAmount_E, IDENTITY(int, 1, 1) xRow INTO #Tmp_DSM2
    FROM DSM2
    WHERE BCK01 = @BCK01 and iYear = @PriYear and iMonth = @PriMonth

    --声明变量
    DECLARE
      @BBY01            int
    , @Quantity_E       numeric(18, 6)
    , @PurchaseAmount_E numeric(18, 6)
    , @PresellAmount_E   numeric(18, 6)
    , @RetailAmount_E  numeric(18, 6)
    , @i int, @iCount int

    select @i = 1, @iCount = COUNT(*) from #Tmp_DSM2
    
    WHILE @i <= @iCount
    BEGIN
      select @BBY01 = BBY01, @Quantity_E = Quantity_E, @PurchaseAmount_E = PurchaseAmount_E, @PresellAmount_E = PresellAmount_E, @RetailAmount_E = RetailAmount_E
      from #Tmp_DSM2 where xRow = @i
      
      IF exists(SELECT * FROM DSM2 WHERE BCK01 = @BCK01 and iYear = @iYear and iMonth = @iMonth and BBY01 = @BBY01)
      BEGIN --上月的结存数量这个月的期初数
        UPDATE DSM2 SET
          Quantity_i       = isnull(@Quantity_E, 0)
        , PurchaseAmount_i = isnull(@PurchaseAmount_E, 0)
        , PresellAmount_i   = isnull(@PresellAmount_E, 0)
        , RetailAmount_i  = ISNULL(@RetailAmount_E, 0)
        WHERE BCK01 = @BCK01 and iYear = @iYear and iMonth = @iMonth and BBY01 = @BBY01
      END ELSE
      BEGIN
        INSERT INTO DSM2(BCK01, iYear, iMonth, BBY01, Quantity_i, PurchaseAmount_i, PresellAmount_i, RetailAmount_i, Maker)
        VALUES(@BCK01, @iYear, @iMonth, @BBY01, isnull(@Quantity_E, 0), isnull(@PurchaseAmount_E, 0), isnull(@PresellAmount_E, 0), isnull(@RetailAmount_E, 0), @Maker)
      END

      set @i = @i + 1
    END --WHILE

    DROP TABLE #Tmp_DSM2
  END

  --清除空值
  UPDATE DSM2 SET 
    Quantity_2 = ISNULL(Quantity_2, 0),
    Quantity_3 = ISNULL(Quantity_3, 0),
    Quantity_4 = ISNULL(Quantity_4, 0),
    Quantity_5 = ISNULL(Quantity_5, 0),
    Quantity_6L = ISNULL(Quantity_6L, 0),
    Quantity_6LF = ISNULL(Quantity_6LF, 0),
    Quantity_6P = ISNULL(Quantity_6P, 0),
    Quantity_7L = ISNULL(Quantity_7L, 0),
    Quantity_7W = ISNULL(Quantity_7W, 0),
    Quantity_8 = ISNULL(Quantity_8, 0),
    Quantity_9 = ISNULL(Quantity_9, 0),

    PurchaseAmount_2 = ISNULL(PurchaseAmount_2, 0),
    PurchaseAmount_3 = ISNULL(PurchaseAmount_3, 0),
    PurchaseAmount_4 = ISNULL(PurchaseAmount_4, 0),
    PurchaseAmount_5 = ISNULL(PurchaseAmount_5, 0),
    PurchaseAmount_6P = ISNULL(PurchaseAmount_6P, 0),
    PurchaseAmount_6L = ISNULL(PurchaseAmount_6L, 0),
    PurchaseAmount_6LF=ISNULL(PurchaseAmount_6LF, 0),
    PurchaseAmount_7W = ISNULL(PurchaseAmount_7W, 0),
    PurchaseAmount_7L = ISNULL(PurchaseAmount_7L, 0),
    PurchaseAmount_8 = ISNULL(PurchaseAmount_8, 0),
    PurchaseAmount_9 = ISNULL(PurchaseAmount_9, 0),
    PurchaseAmount_10 = ISNULL(PurchaseAmount_10, 0),

    PresellAmount_2 = ISNULL(PresellAmount_2, 0),
    PresellAmount_3 = ISNULL(PresellAmount_3, 0),
    PresellAmount_4 = ISNULL(PresellAmount_4, 0),
    PresellAmount_5 = ISNULL(PresellAmount_5, 0),
    PresellAmount_6P = ISNULL(PresellAmount_6P, 0),
    PresellAmount_6L = ISNULL(PresellAmount_6L, 0),
    PresellAmount_6LF=ISNULL(PresellAmount_6LF, 0),
    PresellAmount_7W = ISNULL(PresellAmount_7W, 0),
    PresellAmount_7L = ISNULL(PresellAmount_7L, 0),
    PresellAmount_8 = ISNULL(PresellAmount_8, 0),
    PresellAmount_9 = ISNULL(PresellAmount_9, 0),
	PresellAmount_10 = isnull(PresellAmount_10, 0),

    RetailAmount_2 = ISNULL(RetailAmount_2, 0),
    RetailAmount_3 = ISNULL(RetailAmount_3, 0),
    RetailAmount_4 = ISNULL(RetailAmount_4, 0),
    RetailAmount_5 = ISNULL(RetailAmount_5, 0),
    RetailAmount_6P = ISNULL(RetailAmount_6P, 0),
    RetailAmount_6L = ISNULL(RetailAmount_6L, 0),
    RetailAmount_6LF=ISNULL(RetailAmount_6LF, 0),
    RetailAmount_7W = ISNULL(RetailAmount_7W, 0),
    RetailAmount_7L = ISNULL(RetailAmount_7L, 0),
    RetailAmount_8 = ISNULL(RetailAmount_8, 0),
    RetailAmount_9 = ISNULL(RetailAmount_9, 0),
    RetailAmount_10 = ISNULL(RetailAmount_10, 0)

  WHERE BCK01 = @BCK01 and iYear = @iYear and iMonth = @iMonth

  --更新期末余额
  UPDATE DSM2 SET
    Quantity_E = isnull(Quantity_i --期初
               + Quantity_2 + Quantity_5 + Quantity_6P + Quantity_7W + Quantity_8--本期增加
               - Quantity_3 - Quantity_4 - Quantity_6L - Quantity_6LF - Quantity_7L - Quantity_9--本期减少
			 , 0)
  , PurchaseAmount_E = isnull(PurchaseAmount_i --期初
                     + PurchaseAmount_2 + PurchaseAmount_5 + PurchaseAmount_6P + PurchaseAmount_7W + PurchaseAmount_8--本期增加
                     - PurchaseAmount_3 - PurchaseAmount_4 - PurchaseAmount_6L - PurchaseAmount_6LF  - PurchaseAmount_7L - PurchaseAmount_9--本期减少
                     + PurchaseAmount_10
                   , 0)
  , PresellAmount_E = ISNULL(PresellAmount_i
                     + PresellAmount_2 + PresellAmount_5 + PresellAmount_6P + PresellAmount_7W + PresellAmount_8--本期增加
                     - PresellAmount_3 - PresellAmount_4 - PresellAmount_6L - PresellAmount_6LF - PresellAmount_7L - PresellAmount_9--本期减少
					 + PresellAmount_10
                     , 0)
  , RetailAmount_E = isnull(RetailAmount_i --期初
                     + RetailAmount_2 + RetailAmount_5 + RetailAmount_6P + RetailAmount_7W + RetailAmount_8--本期增加
                     - RetailAmount_3 - RetailAmount_4 - RetailAmount_6L - RetailAmount_6LF  - RetailAmount_7L - RetailAmount_9--本期减少
                     + RetailAmount_10
                   , 0)
                   
  WHERE BCK01 = @BCK01 and iYear = @iYear and iMonth = @iMonth
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('计算%d年%d月的结存时错误，请检查结帐月份是否有已审核的业务单据。如果问题仍存在，请检查“断帐日子”设置或改为“月末“。', 16, 1, @iYear, @iMonth) WITH NOWAIT
    RETURN 3
  END

  --更新摘要
  UPDATE M SET
    Abstract = Y.BBY05, Unit = G.BDG02C
  FROM DSM2 M
  join BBY1 Y ON Y.BBY01 = M.BBY01
  join BAG1 G ON G.BBY01 = M.BBY01
  WHERE M.BCK01 = @BCK01 and M.iYear = @iYear and M.iMonth = @iMonth
COMMIT TRAN