InvMovement Monthly per 11 April

USE [RENO-AHa]
GO
/****** Object:  UserDefinedFunction [dbo].[FN_INVENTORY_MOVEMENT_ADIM]    Script Date: 13/04/2020 12.35.40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*                                             */
ALTER FUNCTION [dbo].[FN_INVENTORY_MOVEMENT_ADIM]
(
/** Part I **/ -- Parameter & Return Table
@datestart Date = '2020/1/1',
@dateend   Date = '2020/1/31',
@Filter SmallInt = 0
)
-- Filter Tipe Barang:
-- 0: All, 1: Barang Sendiri, 2: Kontra Bon, 3: Pastra, 4: Barang Sendiri pakai SubGroup

RETURNS @RESULT TABLE (
  mgc            varchar(4),
  mgn            varchar(64),
  sgc            varchar(9),
  sgn            varchar(128),
  beginning      Numeric(18,2) Default 0,
  receiving      Numeric(18,2) Default 0,
  deposit_return Numeric(18,2) Default 0,
  sales          Numeric(18,2) Default 0,
  cogs           Numeric(18,2) Default 0,
  wastage_real   Numeric(18,2) Default 0,
  wastage_obral  Numeric(18,2) Default 0,
  adjustment     Numeric(18,2) Default 0,
  ending_balance Numeric(18,2) Default 0
)
/** Part I End **/

/** Part II **/ -- Deklarasi Variabel termasuk @Temp Table
AS
  BEGIN
    DECLARE
      @CutOff              Date     = '2020/1/1', --Nantinya dapat diubah
      @TBStart             uniqueidentifier,   -- Filter Tipe Barang Awal
      @TBEnd               uniqueidentifier,   -- Filter Tipe Barang Akhir
      @Flag_DO             SmallInt = 100, -- Receiving
      @Flag_Deposit_Return SmallInt = 220, -- Deposit Return
      @Flag_Sales          SmallInt = 210, -- SALES
      @Flag_COGS           SmallInt = 310, -- COGS
      @Flag_CN             SmallInt = 110, -- Credit Note
      @Flag_WR             SmallInt = 410, -- Wastage Real
      @Flag_WO             SmallInt = 420, -- Wastage Obral
      @Flag_Adjustment     SmallInt = 500; -- Adjustment
/*
    DECLARE @TEMP TABLE (
      barang_id            uniqueidentifier,
      beginning            Numeric(18,2) Default 0,
      receiving            Numeric(18,2) Default 0,
      deposit_return       Numeric(18,2) Default 0,
      sales                Numeric(18,2) Default 0,
      cogs                 Numeric(18,2) Default 0,
      cn                   Numeric(18,2) Default 0,
      wr                   Numeric(18,2) Default 0,
      wo                   Numeric(18,2) Default 0,
      adjustment           Numeric(18,2) Default 0,
      ending               Numeric(18,2) Default 0
    );*/

    declare @temp table (
      transaksi  varchar(100),
      barang_id  uniqueidentifier,
      qtyin      numeric(18,2) default 0,
      qtyout     numeric(18,2) default 0,
      konversi   numeric(18,2) not null,
      tgl        date
    );

    declare @temp2 table (
      barang_id    uniqueidentifier,
      saldoawal    numeric(18,2) default 0,  --
      do           numeric(18,2) default 0,
      do_bonus     numeric(18,2) default 0,
      cn_receiving numeric(18,2) default 0,--
      dn_receiving numeric(18,2) default 0,--
      trader       numeric(18,2) default 0, --
      returtrader  numeric(18,2) default 0, --
      pos          numeric(18,2) default 0,--
      returpos     numeric(18,2) default 0,--
      wastage      numeric(18,2) default 0, --
      obral        numeric(18,2) default 0, --
      adjustment   numeric(18,2) default 0,
      saldoakhir   numeric(18,2) default 0   --
    );
/** Part II End **/

/** Part III **/ -- Mengatur Query Sesuai Filter
    IF @Filter = 0 BEGIN -- Semua Tipe Barang
      SELECT TOP 1 @TBStart = REF$TIPE_BARANG_ID FROM REF$TIPE_BARANG ORDER BY REF$TIPE_BARANG_ID;
      SELECT TOP 1 @TBEnd   = REF$TIPE_BARANG_ID FROM REF$TIPE_BARANG ORDER BY REF$TIPE_BARANG_ID DESC;
    END ELSE
    IF @Filter = 1 OR @Filter = 4 BEGIN
      SELECT @TBStart = REF$TIPE_BARANG_ID FROM REF$TIPE_BARANG  WHERE TPBRG_NAME = 'Barang Sendiri';
      SELECT @TBEnd = @TBStart;
    END ELSE
    IF @Filter = 2 BEGIN
      SELECT @TBStart = REF$TIPE_BARANG_ID  FROM REF$TIPE_BARANG WHERE TPBRG_NAME = 'Kontra Bon';
      SELECT @TBEnd = @TBStart;
    END ELSE
    IF @Filter = 3 BEGIN
      SELECT @TBStart = REF$TIPE_BARANG_ID  FROM REF$TIPE_BARANG WHERE TPBRG_NAME = 'Pasar Tradisional';
      SELECT @TBEnd = @TBStart;
    END
/** Part III End **/

/** Part IV **/ -- Query Insert ke @Temp

--Isi @Temp dengan query berikut

    insert into @temp(transaksi, barang_id, qtyin, konversi, tgl)
    select 'do', b.barang_id, b.dod_qty_order_recv, c.konvsat_scale, cast(a.do_date as date)
    from do a
         inner join do_detail b on a.do_id=b.do_id
         inner join ref$konversi_satuan c on c.barang_id=b.barang_id and c.ref$satuan_id=b.ref$satuan_id
    where cast(a.do_date as date) between @datestart and @dateend;

    insert into @temp(transaksi, barang_id, qtyin, konversi, tgl)
    select 'do bonus', b.dobi_barang_id, b.dobi_qty, c.konvsat_scale, cast(a.dob_date as date)
    from dobonus a
         inner join dobonusitem b on a.dobonus_id = b.dobi_dobonus_id
         inner join ref$konversi_satuan c on c.barang_id=b.dobi_barang_id and c.ref$satuan_id=b.dobi_uom_id
    where cast(a.dob_date as date) between @datestart and @dateend;

  --
    insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
    select 'cn', b.barang_id, b.cnrd_qty, c.konvsat_scale, cast(a.cnr_date as date)
    from cn_recv a
         inner join cn_recv_detil b on a.cn_recv_id=b.cn_recv_id
         inner join ref$konversi_satuan c on c.barang_id=b.barang_id and c.ref$satuan_id=b.ref$satuan_id
      where cast(a.cnr_date as date) between @datestart and @dateend;

    insert into @temp(transaksi, barang_id, qtyin, konversi, tgl)
    select 'dn', b.barang_id, b.dnrd_qty, c.konvsat_scale, cast(a.dnr_date as date)
    from dn_recv a
         inner join dn_recv_detil b on a.dn_recv_id=b.dn_recv_id
         inner join ref$konversi_satuan c on c.barang_id=b.barang_id and c.ref$satuan_id=b.ref$satuan_id
    where cast(a.dnr_date as date) between @datestart and @dateend;

  --
    insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
    select 'dot', b.dotitem_barang_id, b.dotitem_qty, c.konvsat_scale, a.dot_date
    from dotrader a
         inner join dotraderitem b on a.dotrader_id = b.dotitem_dotrader_id
         inner join ref$konversi_satuan c on c.barang_id = b.dotitem_barang_id and c.ref$satuan_id = b.dotitem_satuan_id
    where cast(a.dot_date as date) between @datestart and @dateend;

    insert into @temp(transaksi, barang_id, qtyin, konversi, tgl)
    select 'ret', b.retitem_barang_id, b.retitem_qty, c.konvsat_scale, cast(a.ret_date as date)
    from returtrader a
        inner join returtraderitem b on a.returtrader_id = b.retitem_returtrader_id
        inner join ref$konversi_satuan c on c.barang_id = b.retitem_barang_id and c.ref$satuan_id = b.retitem_satuan_id
      where cast(a.ret_date as date) between @datestart and @dateend;

  --
    insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
    select 'wastage', b.igrarwd_barang_id, b.igrarwd_qty_igra, c.konvsat_scale, cast(a.igrarw_date as date)
    from igrareal_wastage a
         inner join igrareal_wastage_item b on a.igrareal_wastage_id = b.igrarwd_igrarw_id
         inner join ref$konversi_satuan c on c.barang_id = b.igrarwd_barang_id and c.ref$satuan_id = b.igrarwd_uom_id
      where cast(a.igrarw_date as date) between @datestart and @dateend;

    insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
    select 'obral', b.igraobd_barang_id, b.igraobd_qty_igra, c.konvsat_scale, cast(a.igraob_date as date)
    from igraobral a
         inner join igraobral_item b on a.igraobral_id = b.igraobd_igraob_id
         inner join ref$konversi_satuan c on c.barang_id = b.igraobd_barang_id and c.ref$satuan_id = b.igraobd_uom_id
      where cast(a.igraob_date as date) between @datestart and @dateend;

  --
    insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
    select 'pos', c.barang_id, b.transd_qty, d.konvsat_scale, a.trans_is_pending, cast(a.trans_date as date)
    from transaksi a with (nolock)
         inner join transaksi_detil b  with (nolock) on b.transaksi_id = a.transaksi_id
         inner join barang c on b.transd_barang_id = c.barang_id
         Inner join [ref$konversi_satuan] d on d.Barang_id = c.Barang_id and d.[ref$satuan_id] = b.Transd_satuan_id
    where cast(a.trans_date as date) between @datestart and @dateend
          and (a.trans_is_pending is null or a.trans_is_pending = 0);

    insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
    select 'ret_transaksi', b.transrd_barang_id, b.transrd_qty, c.konvsat_scale, cast(a.transr_date as date)
    from returtransaksi a
         inner join returtransaksidetil b with (nolock) on a.returtransaksi_id = b.transrd_returtransaksi_id
         inner join ref$konversi_satuan c on b.transrd_barang_id = c.barang_id and b.transrd_satuan_id = c.ref$satuan_id
    where cast(a.transr_date as date) between @datestart and @dateend;

    insert into @temp(transaksi, barang_id, konversi, tgl, qtyin, qtyout)
    select 'adjustment product', b.adjd_barang_id, c.konvsat_scale, cast(a.adj_date as date),
           case when b.adjd_qty > 0 then b.adjd_qty else 0 end,
           case when b.adjd_qty < 0 then -b.adjd_qty else 0 end
    from adjustmentproduct a
         inner join adjustmentproductitem b on a.adjustmentproduct_id = b.adjd_adjustmentproduct_id
         inner join ref$konversi_satuan c on c.barang_id = b.adjd_barang_id and c.ref$satuan_id = b.adjd_uom_id
      where cast(a.adj_date as date) between @datestart and @dateend;



--Isi @Temp2 dengan query berikut

    insert into @temp2(barang_id, saldoawal)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp
    where tgl between @cutoff and dateadd(day, -1, @datestart) group by barang_id

    insert into @temp2(barang_id, do)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'do' group by barang_id

    insert into @temp2(barang_id, do_bonus)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'do bonus' group by barang_id

    insert into @temp2(barang_id, cn_receiving)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'cn' group by barang_id

    insert into @temp2(barang_id, dn_receiving)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'dn' group by barang_id

    insert into @temp2(barang_id, trader)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'dot' group by barang_id

    insert into @temp2(barang_id, returtrader)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'ret' group by barang_id

    insert into @temp2(barang_id, pos)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'pos' group by barang_id

    insert into @temp2(barang_id, returpos)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'ret_transaklsi' group by barang_id

    insert into @temp2(barang_id, wastage)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'wastage' group by barang_id

    insert into @temp2(barang_id, obral)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'obral' group by barang_id

    insert into @temp2(barang_id, adjustment)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
    and    transaksi = 'adjustment product' group by barang_id

    insert into @temp2(barang_id, saldoakhir)
    select barang_id, sum((qtyin - qtyout) * konversi) from @temp
    where tgl between @cutoff and @dateend group by barang_id

/** Part IV End **/

/** Part V **/ -- Return Table
    INSERT INTO @RESULT (mgc, mgn, sgc, sgn,
                beginning, receiving,deposit_return, sales, cogs,
                wastage_real, wastage_obral, adjustment, ending_balance)

    SELECT m.MERCHANGRUP_CODE gc,m.MERCHANGRUP_NAME gn,
           g.SUBGRUP_CODE sgc, g.SUBGRUP_NAME sgn,

           sum(tq.saldoawal) beginning,
           sum(tq.do) receiving,
           sum(tq.pos) deposit_return,
           sum(tq.returpos) cogs,
           sum(tq.cn_receiving) cn,
           sum(tq.wastage) wr,
           sum(tq.obral) wo,
           sum(tq.adjustment) adjustment,
           sum(tq.saldoakhir) ending

    FROM   @Temp2 tq
           INNER JOIN barang b
                   on b.BARANG_ID = tq.barang_id
           INNER JOIN ref$kategori k
                   on b.ref$kategori_id = k.ref$kategori_id
           INNER JOIN ref$sub_grup g
                   on k.ref$sub_grup_id = g.ref$sub_grup_id
           INNER JOIN ref$merchandise_grup m
                   on b.ref$merchandise_grup_id = m.ref$merchandise_grup_id

    GROUP BY g.SUBGRUP_CODE, g.SUBGRUP_NAME, m.MERCHANGRUP_CODE, m.MERCHANGRUP_NAME
/** Part V End **/

    RETURN
  END