ALTER PROCEDURE [dbo].[PV_SP_InventoryMonth_01] -- 'pvd','@nam/09/09'
    @site NVARCHAR(10),
    @fromDate DATE,
    @thang INT,
    @nam INT
AS
--exec PV_TonDau @site=@site -- đổ bảng tồn đầu kì
DECLARE @dauKis DATE;


--get whse theo từng nhà máy
DECLARE @whse NVARCHAR(20);
IF (@site = 'PVD')
    SET @whse = N'BTL';
ELSE
    SET @whse = N'BTLT';

SET @dauKis =
(
    SELECT DATEADD(MONTH, -1, DATEADD(DAY, 0, @fromDate))
);

SELECT *
FROM FACT_ProductionbyMonth;

-- XÓA TABLE

DELETE FACT_ProductionbyMonth_item
WHERE Site = @site
      AND Thang = @thang
      AND Nam = @nam;
DELETE FACT_ProductionbyMonth
WHERE Site = @site
      AND Thang = @thang
      AND Nam = @nam;



INSERT INTO FACT_ProductionbyMonth_item
SELECT DISTINCT
       (item) loaicoc,
       @nam NAM,
       @thang,
       0 TonDau,
       0 GtrTonDau,
       0 SLSanXuat,
       0 SLCocHuy,
       0 SLNhapKhoSX,
       0 qtyNhapKho,
       0 qtyXuatCocCongTruong,
       0 qtyXuatKho,
       0 toncuoi,
       0 giatriTonCuoi,
       @site SITE
FROM ERP.SyteLine_Apps.dbo.matltran_mst m WITH (NOLOCK)
WHERE dbo.FAB_IsPileItem(m.item) = 1
      AND m.site_ref = @site
      AND whse = @whse
      AND (item) <> '';



DECLARE @temp TABLE
(
    qtyXuatKho DECIMAL(28, 10),
    loaicoc NVARCHAR(50),
    MONTH INT,
    yEAR INT,
    site_ref NVARCHAR(10)
);
INSERT INTO @temp
SELECT SUM(A.QTY) QTY,
       A.loaicoc,
       A.month,
       A.year,
       site_ref
FROM
(
    SELECT (m.item) loaicoc,
           ISNULL(SUM(qty), 0) QTY,
           MONTH(m.trans_date) month,
           YEAR(m.trans_date) year,
           m.site_ref
    FROM ERP.SyteLine_Apps.dbo.matltran_mst m
        LEFT JOIN ERP.SyteLine_Apps.dbo.trnitem_mst t
            ON m.ref_num = t.trn_num
               AND t.trn_line = m.ref_line_suf
    WHERE dbo.FAB_ComparePeriod(m.trans_date, @nam, @thang) = 1
          AND LEFT(m.item, 2) IN ( 'AA', 'AC', 'AN' )
          AND m.loc IS NOT NULL
          AND m.whse = @whse
          AND m.ref_type = 'T'
          AND t.from_whse = @whse
          AND t.to_whse != 'KHGB'
    GROUP BY (m.item),
             MONTH(m.trans_date),
             YEAR(m.trans_date),
             m.site_ref
    UNION ALL
    SELECT (m.item) loaicoc,
           ISNULL(SUM(m.qty), 0) QTY,
           MONTH(m.trans_date) month,
           YEAR(m.trans_date) year,
           m.site_ref
    FROM ERP.SyteLine_Apps.dbo.matltran_mst m
    WHERE dbo.FAB_ComparePeriod(m.trans_date, @nam, @thang) = 1
          AND LEFT(m.item, 2) IN ( 'AA', 'AC', 'AN' )
          AND m.loc IS NOT NULL
          AND m.whse = @whse
          AND m.ref_type = 'I'
          AND m.trans_type = 'G'
    GROUP BY (m.item),
             MONTH(m.trans_date),
             YEAR(m.trans_date),
             m.site_ref
    UNION ALL
    SELECT (m.item),
           ISNULL(SUM(qty), 0) QTY,
           MONTH(m.trans_date) month,
           YEAR(m.trans_date) year,
           m.site_ref
    FROM ERP.SyteLine_Apps.dbo.matltran_mst m
    WHERE dbo.FAB_ComparePeriod(m.trans_date, @nam, @thang) = 1
          AND LEFT(m.item, 2) IN ( 'AA', 'AC', 'AN' )
          AND m.loc IS NOT NULL
          AND m.whse = @whse
          AND m.ref_type = 'O'
    GROUP BY (m.item),
             MONTH(m.trans_date),
             YEAR(m.trans_date),
             m.site_ref
) A
WHERE site_ref = @site
GROUP BY A.loaicoc,
         A.month,
         A.year,
         site_ref;

UPDATE FACT_ProductionbyMonth_item
SET qtyXuatKho = ISNULL(a.qtyXuatKho, 0)
FROM FACT_ProductionbyMonth_item b WITH (NOLOCK)
    LEFT JOIN
    (SELECT * FROM @temp) a
        ON a.yEAR = b.Nam
           AND a.loaicoc = b.LoaiCoc
           AND a.site_ref = b.Site
           AND a.MONTH = b.Thang
WHERE b.Nam = @nam
      AND b.Thang = @thang
      AND a.loaicoc = b.LoaiCoc
      AND b.Site = @site;


INSERT INTO FACT_ProductionbyMonth
SELECT dbo.ZVN_GetPileDiameter(LoaiCoc) loaicoc,
       @nam,
       @thang,
       0 TonDau,
       0 GtrTonDau,
       SUM(SLSanXuat) SLSanXuat,
       SUM(SLCocHuy) SLCocHuy,
       SUM(SLNhapKhoSX) SLNhapKhoSX,
       SUM(qtyNhapKho) qtyNhapKho,
       SUM(qtyXuatCocCongTruong) qtyXuatCocCongTruong,
       SUM(qtyXuatKho) qtyXuatKho,
       SUM(TonCuoi) toncuoi,
       SUM(giatriTonCuoi) giatriTonCuoi,
       Site
FROM FACT_ProductionbyMonth_item a
WHERE Site = @site
      AND Thang = @thang
      AND Nam = @nam
GROUP BY dbo.ZVN_GetPileDiameter(loaicoc),
         Site;


UPDATE FACT_ProductionbyMonth
SET TonCuoi = a.toncuoi,
    giatriTonCuoi = a.endamount
FROM FACT_ProductionbyMonth b
    LEFT JOIN
    (
        SELECT dbo.ZVN_GetPileDiameter(item) loaicoc,
               SUM(qty) toncuoi,
               site,
               YEAR(@fromDate) year,
               MONTH(@fromDate) month,
               SUM(endamount) endamount
        FROM
        (
            SELECT SUM(m.qty) qty,
                   item,
                   SUM(m.qty * cost) endamount,
                   site_ref AS site
            FROM ERP.SyteLine_Apps.dbo.matltran_mst m
            WHERE CAST(m.trans_date AS DATE) <= EOMONTH(@fromDate)
                  AND LEFT(m.item, 2) IN ( 'AA', 'AC', 'AN' )
                  AND m.whse = @whse
                  AND m.site_ref = @site
                  AND m.loc IS NOT NULL
            GROUP BY item,
                     site_ref
        ) b
        GROUP BY dbo.ZVN_GetPileDiameter(item),
                 site
    ) a
        ON a.year = b.Nam
           AND a.loaicoc = b.LoaiCoc
           AND a.Site = b.Site
           AND a.month = b.Thang
WHERE a.year = @nam
      AND a.month = @thang
      AND a.Site = @site;
--tồn đầu

UPDATE FACT_ProductionbyMonth
SET TonDau = a.tondau,
    GtrTonDau = a.beginamount
FROM FACT_ProductionbyMonth b
    LEFT JOIN
    (
        SELECT dbo.ZVN_GetPileDiameter(item) loaicoc,
               SUM(ISNULL(qty, 0)) tondau,
               YEAR(@fromDate) year,
               MONTH(@fromDate) month,
               site,
               SUM(beginamount) beginamount
        FROM
        (
            SELECT SUM(m.qty) qty,
                   item,
                   site_ref AS site,
                   SUM(ISNULL(qty, 0) * ISNULL(cost, 0)) AS beginamount
            FROM ERP.SyteLine_Apps.dbo.matltran_mst m
            WHERE CAST(m.trans_date AS DATE) < CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, @fromDate), 0) AS DATE)
                  AND LEFT(m.item, 2) IN ( 'AA', 'AC', 'AN' )
                  AND m.whse = @whse
                  AND m.site_ref = @site -- AND m.Uf_ZVN_acct='1551'
                  AND m.loc IS NOT NULL
            GROUP BY item,
                     site_ref
        ) b
        GROUP BY dbo.ZVN_GetPileDiameter(item),
                 site
    ) a
        ON a.year = b.Nam
           AND a.loaicoc = b.LoaiCoc
           AND a.Site = b.Site
           AND a.month = b.Thang
WHERE a.year = @nam
      AND a.month = @thang
      AND a.Site = @site;
UPDATE FACT_ProductionbyMonth
SET SLSanXuat = ISNULL(a.qty, 0)
FROM FACT_ProductionbyMonth B WITH (NOLOCK)
    LEFT JOIN
    (
        SELECT S.site AS site,
               b.TransYear AS Year,
               MONTH(b.TransDay) AS month,
               dbo.ZVN_GetPileDiameter(c.Item) loaicoc,
               SUM(ISNULL(b.Value, 0)) AS qty
        FROM ERP.SyteLine_Apps.dbo.ZVN_ProjPlanning_Day_mst b WITH (NOLOCK)
            JOIN ERP.SyteLine_Apps.dbo.ZVN_ProjProduction_mst c WITH (NOLOCK)
                ON b.ProjNum = c.ProjNum
                   AND b.ResourceNum = c.ResourceId
                   AND b.FullSeg = c.Item
                   AND b.SiteRef = c.SiteRef
            LEFT JOIN ERP.SyteLine_Apps.dbo.site S WITH (NOLOCK) -- lấy ra nhà máy
                ON S.Uf_VendNum = c.ResourceId
        WHERE S.site = @site
              AND b.TransType = 'ttsx' --AND c.TRANSTYPE='ttsx'
        GROUP BY S.site,
                 b.TransYear,
                 MONTH(b.TransDay),
                 dbo.ZVN_GetPileDiameter(c.Item)
    ) a
        ON a.Year = B.Nam
           AND a.loaicoc = B.LoaiCoc
           AND a.site = B.Site
           AND a.month = B.Thang
WHERE a.site = @site
      AND a.month = @thang
      AND a.Year = @nam;
UPDATE FACT_ProductionbyMonth
SET SLCocHuy = ISNULL(a.QTYH, 0)
FROM FACT_ProductionbyMonth B WITH (NOLOCK)
    LEFT JOIN
    (
        SELECT MONTH,
               yEAR,
               SUM(QTYH) QTYH,
               dbo.ZVN_GetPileDiameter(ItemNo_) LOAICOC,
               site
        FROM
        (
            SELECT MONTH(A.ManufactureDate) MONTH,
                   YEAR(A.ManufactureDate) yEAR,
                   COUNT(A.ItemNo_) QTYH,
                   A.ItemNo_,
                   A.CreatedBySite site
            FROM NEXTG.dbo.Item_Serials A WITH (NOLOCK)
            WHERE A.CreatedBySite = @site --@site
                  AND A.SerialNo_ IS NOT NULL
                  AND A.ItemNo_ IS NOT NULL
                  AND A.ManufactureDate IS NOT NULL
                  AND A.Quanlity = 'H'
                  AND A.StatusID = 1
                  AND dbo.FAB_IsPileItem(A.ItemNo_) = 1
                  AND dbo.FAB_ComparePeriod(A.ManufactureDate, @nam, @thang) = 1
            GROUP BY ItemNo_,
                     YEAR(A.ManufactureDate),
                     MONTH(A.ManufactureDate),
                     A.CreatedBySite,
                     Quanlity,
                     FactoryNo_
        ) A
        WHERE MONTH = @thang
              AND yEAR = @nam
        GROUP BY MONTH,
                 yEAR,
                 dbo.ZVN_GetPileDiameter(ItemNo_),
                 site
    ) a
        ON a.yEAR = B.Nam
           AND a.LOAICOC = B.LoaiCoc
           AND a.Site = B.Site
           AND a.MONTH = B.Thang
WHERE B.Nam = @nam
      AND B.Thang = @thang
      AND a.LOAICOC = B.LoaiCoc
      AND B.Site = @site;



DECLARE @t TABLE
(
    qtyNhapKho DECIMAL(28, 10),
    LOAICOC NVARCHAR(50),
    MONTH INT,
    yEAR INT,
    site_ref NVARCHAR(10)
);


INSERT INTO @t
SELECT SUM(ISNULL(qty, 0)) qtyNhapKho,
       dbo.ZVN_GetPileDiameter(e.item) LOAICOC,
       MONTH(e.trans_date) MONTH,
       YEAR(e.trans_date) yEAR,
       e.site_ref
FROM
(
    SELECT SUM(qty) qty,
           item,
           trans_date,
           site_ref,
           whse
    FROM ERP.SyteLine_Apps.dbo.matltran_mst b
    WHERE b.qty >= 0
          AND NOT EXISTS
    (
        SELECT 1
        FROM
        (
            SELECT trans_num
            FROM --
                ERP.SyteLine_Apps.dbo.matltran_mst a
                LEFT JOIN ERP.SyteLine_Apps.dbo.job_mst b
                    ON a.ref_num = b.job
                       AND a.ref_line_suf = b.suffix
                       AND b.site_ref = a.site_ref
            WHERE rework = 1
                  AND a.site_ref = @site
                  AND a.whse = @whse
                  AND dbo.FAB_ComparePeriod(a.trans_date, @nam, @thang) = 1
            UNION ALL
            SELECT trans_num
            FROM ERP.SyteLine_Apps.dbo.matltran_mst a
            WHERE a.trans_type = 'T'
                  AND whse NOT IN ( 'btl', 'KHGB' )
                  AND site_ref = @site
                  AND dbo.FAB_ComparePeriod(a.trans_date, @nam, @thang) = 1
            UNION ALL
            SELECT t.trans_num
            FROM ERP.SyteLine_Apps.dbo.matltran_mst t WITH (NOLOCK)
                JOIN ERP.SyteLine_Apps.dbo.job_mst C WITH (NOLOCK)
                    ON C.job = t.ref_num
                       AND C.suffix = t.ref_line_suf
                       AND C.site_ref = t.site_ref
            WHERE t.ref_type = 'J'
                  AND t.site_ref = @site
                  AND dbo.FAB_IsPileItem(t.item) = 1
                  AND t.whse = @whse
                  AND dbo.FAB_ComparePeriod(t.trans_date, @nam, @thang) = 1
                  --and t.qty>=0
                  AND t.loc IS NOT NULL
                  AND t.whse IS NOT NULL
                  AND C.rework = 0
        ) a
        WHERE a.trans_num = b.trans_num
    )
          AND dbo.FAB_ComparePeriod(b.trans_date, @nam, @thang) = 1
          AND dbo.FAB_IsPileItem(b.item) = 1
          AND b.site_ref = @site
          AND b.whse = @whse
          AND b.loc IS NOT NULL
          AND b.whse IS NOT NULL
    GROUP BY item,
             trans_date,
             site_ref,
             whse
) e
GROUP BY dbo.ZVN_GetPileDiameter(e.item),
         MONTH(e.trans_date),
         YEAR(e.trans_date),
         site_ref;




UPDATE b
SET qtyNhapKho = ISNULL(a.qtyNhapKho, 0)
FROM FACT_ProductionbyMonth b WITH (NOLOCK)
    JOIN
    (SELECT * FROM @t) a
        ON a.yEAR = b.Nam
           AND a.LOAICOC = b.LoaiCoc
           AND a.site_ref = b.Site
           AND a.MONTH = b.Thang
WHERE b.Nam = @nam
      AND b.Thang = @thang
      AND a.LOAICOC = b.LoaiCoc
      AND b.Site = @site;



-- slNHAP KHO

UPDATE FACT_ProductionbyMonth
SET SLNhapKhoSX = ISNULL(QTY, 0)
FROM FACT_ProductionbyMonth B WITH (NOLOCK)
    LEFT JOIN
    (
        SELECT MONTH(t.trans_date) MONTH,
               YEAR(t.trans_date) YEAR,
               SUM((ISNULL(t.qty, 0))) QTY,
               dbo.ZVN_GetPileDiameter(t.item) LOAICOC,
               t.site_ref AS SITE
        --SELECT	SUM(t.qty)
        FROM ERP.SyteLine_Apps.dbo.matltran_mst t WITH (NOLOCK)
            JOIN ERP.SyteLine_Apps.dbo.job_mst C WITH (NOLOCK)
                ON C.job = t.ref_num
                   AND C.suffix = t.ref_line_suf
                   AND C.site_ref = t.site_ref
        WHERE t.ref_type = 'J'
              AND t.site_ref = @site
              AND dbo.FAB_IsPileItem(t.item) = 1
              AND t.whse = @whse
              AND dbo.FAB_ComparePeriod(t.trans_date, @nam, @thang) = 1
              --and t.qty>=0
              AND t.loc IS NOT NULL
              AND t.whse IS NOT NULL
              AND C.rework = 0
        GROUP BY MONTH(t.trans_date),
                 YEAR(t.trans_date),
                 dbo.ZVN_GetPileDiameter(t.item),
                 t.site_ref
    ) a
        ON a.YEAR = B.Nam
           AND a.LOAICOC = B.LoaiCoc
           AND a.SITE = B.Site
           AND a.MONTH = B.Thang
WHERE B.Nam = @nam
      AND B.Thang = @thang
      AND a.LOAICOC = B.LoaiCoc
      AND B.Site = @site;


DECLARE @TB TABLE
(
    qtyXuatCocCongTruong DECIMAL(28, 10),
    LOAICOC NVARCHAR(50),
    MONTH INT,
    yEAR INT,
    site_ref NVARCHAR(5)
);
INSERT INTO @TB
SELECT SUM(ISNULL(m.qty, 0)) qtyXuatCocCongTruong,
       dbo.ZVN_GetPileDiameter(m.item) LOAICOC,
       MONTH(m.trans_date) MONTH,
       YEAR(m.trans_date) yEAR,
       m.site_ref
FROM ERP.SyteLine_Apps.dbo.matltran_mst m WITH (NOLOCK)
    LEFT JOIN ERP.SyteLine_Apps.dbo.trnitem_mst t
        ON m.ref_num = t.trn_num
           AND t.trn_line = m.ref_line_suf
WHERE dbo.FAB_ComparePeriod(m.trans_date, @nam, @thang) = 1
      AND LEFT(m.item, 2) IN ( 'AA', 'AC', 'AN' )
      AND m.loc IS NOT NULL
      AND m.whse = @whse
      AND m.ref_type = 'T'
      AND t.from_whse = @whse
      AND t.to_whse = 'KHGB'
GROUP BY dbo.ZVN_GetPileDiameter(m.item),
         MONTH(m.trans_date),
         YEAR(m.trans_date),
         m.site_ref;

-- XUẤT CỌC CÔNG TRƯỜNG

UPDATE FACT_ProductionbyMonth
SET qtyXuatCocCongTruong = ISNULL(a.qtyXuatCocCongTruong, 0)
FROM FACT_ProductionbyMonth B WITH (NOLOCK)
    LEFT JOIN
    (SELECT * FROM @TB) a
        ON a.yEAR = B.Nam
           AND a.LOAICOC = B.LoaiCoc
           AND a.site_ref = B.Site
           AND a.MONTH = B.Thang
WHERE B.Nam = @nam
      AND B.Thang = @thang
      AND a.LOAICOC = B.LoaiCoc
      AND B.Site = @site;