/*
Create by: ChieuPT
DATE: 2019-07-17
*/

--PVG_ProductionCostingTableSp_KT01 2019,6,'pvQ'
ALTER PROCEDURE [dbo].[PVG_ProductionCostingTableSp_KT01]
    @Year SMALLINT,
    @Period SMALLINT,
    @Site SiteType
AS
EXEC dbo.SetSiteSp @Site = @Site,   -- SiteType
                   @Infobar = NULL; -- InfobarType

DECLARE @tb TABLE
(
    ref_line_suf NVARCHAR(5),
    suffix NVARCHAR(100),
    job NVARCHAR(100),
    item NVARCHAR(100),
    ref_num NVARCHAR(200),
    qty DECIMAL(28, 10),
    cost DECIMAL(28, 10),
    u_m NVARCHAR(20),
    MatlDesc NVARCHAR(200)
);
INSERT INTO @tb
SELECT j.ref_line_suf,
       j.suffix,
       j.job,
       j.item,
       j.ref_num,
       SUM(ISNULL(M.qty, 0)),
       M.cost,
       i.u_m,
       i.description AS MatlDesc
FROM jobmatl j
    JOIN item i
        ON j.item = i.item
    LEFT JOIN matltran M
        ON M.ref_num = j.job
           AND M.ref_line_suf = j.suffix
           AND i.item = M.item
WHERE dbo.FAB_ComparePeriod(M.trans_date, @Year, @Period) = 1
      AND M.trans_type IN ( 'I', 'W' )
GROUP BY j.ref_line_suf,
         j.suffix,
         j.job,
         j.item,
         j.ref_num,
         M.cost,
         i.u_m,
         i.description;

DECLARE @tempMatl TABLE
(
    JOB NVARCHAR(50),
    suffix NVARCHAR(50),
    item NVARCHAR(50),
    u_m NVARCHAR(50),
    ItemDesc NVARCHAR(200)
);
INSERT INTO @tempMatl
SELECT j.job,
       j.suffix,
       j.item,
       i.u_m,
       i.description AS ItemDesc
FROM job j
    JOIN item i
        ON j.item = i.item
WHERE j.rework = 0
      AND dbo.FAB_ComparePeriod(j.job_date, @Year, @Period) = 1;
DECLARE @m TABLE
(
    qty DECIMAL(28, 10),
    item NVARCHAR(100)
);
INSERT INTO @m
SELECT SUM(qty) qty,
       item
FROM matltran WITH (NOLOCK)
WHERE dbo.FAB_ComparePeriod(trans_date, @Year, @Period) = 1
      AND trans_type = 'F' -- AND  item = 'AA173041A035N0600'
GROUP BY item;

SELECT j.item,
       jm.u_m,
       ABS((ISNULL(mt.qty, 0))) qtyCoc,
       ABS((ISNULL(jm.qty, 0))) qty,
       ABS(ISNULL(jm.cost, 0)) cost,
       ABS((ISNULL(jm.qty, 0) * ISNULL(jm.cost, 0))) AS AmountBOM,
       jm.item AS item_Matl,
       j.ItemDesc,
       jm.MatlDesc,
       --ChieuPT UPDATE :
       ABS(SUM(ISNULL(s.qty, 0))) AS SLChecnhLech,
       ABS(SUM(ISNULL(s.qty, 0) * ISNULL(jm.cost, 0))) AS TTChenhLech,
       ABS(SUM(ISNULL(d.qty, 0))) AS SLHaoHut,
       ABS(SUM(ISNULL(d.qty, 0) * ISNULL(jm.cost, 0))) AS TTHaoHut,
       ABS((ISNULL(jm.qty, 0) + SUM(ISNULL(s.qty, 0)))) AS SLDinhMuc,
       ABS(((ISNULL(jm.qty, 0) * ISNULL(jm.cost, 0) + SUM(ISNULL(s.qty, 0)) * ISNULL(jm.cost, 0)))) AS TTDinhMuc,
       ABS(ISNULL(jm.qty, 0) * ISNULL(jm.cost, 0)
           + SUM(ISNULL(s.qty, 0) * ISNULL(jm.cost, 0) + (ISNULL(s.qty, 0) * ISNULL(jm.cost, 0))
                 + (ISNULL(d.qty, 0) * ISNULL(jm.cost, 0))
                )
          ) AS TongTien,
       ABS((ISNULL(jm.qty, 0) + SUM(ISNULL(s.qty, 0) + ISNULL(d.qty, 0)))) SLThucTe
FROM matltran m WITH (NOLOCK)
    JOIN @tempMatl j
        ON m.ref_num = j.JOB
           AND m.ref_line_suf = j.suffix
           AND j.item = m.item
    LEFT JOIN @tb jm
        ON jm.job = j.JOB
           AND j.suffix = jm.suffix
    LEFT JOIN PVG_JobMaterialTrans s WITH (NOLOCK)
        ON s.item = jm.item
           AND jm.ref_num = s.JOB
           AND jm.ref_line_suf = s.suffix
           AND s.matl_type = 'B'
    LEFT JOIN dbo.PVG_JobMaterialTrans d WITH (NOLOCK)
        ON d.item = jm.item
           AND jm.ref_num = d.JOB
           AND jm.ref_line_suf = d.suffix
           AND d.matl_type = 'V'
    LEFT JOIN @m mt
        ON mt.item = j.item
WHERE jm.item IS NOT NULL -- AND jm.item='CA010100001'
      AND m.ref_type = 'J'
      --  AND m.trans_type = 'F'
      AND dbo.FAB_ComparePeriod(m.trans_date, @Year, @Period) = 1
      AND j.item NOT LIKE 'PB%'
GROUP BY j.item,
         jm.u_m,
         ISNULL(jm.cost, 0),
         jm.item,
         j.ItemDesc,
         (ISNULL(mt.qty, 0)),
         jm.MatlDesc,
         (ISNULL(jm.qty, 0));

DELETE @tb;
DELETE @tempMatl;

--SELECT * FROM PVG_JobMaterialTrans