ALTER PROCEDURE [dbo].[ZVN_Rpt_FactoryProduction_DaySumSp]
    @ResourceId NVARCHAR(32),
    @fromDate DATE,
    @toDate DATE,
    @Site SiteType
AS
SET NOCOUNT ON;
EXEC dbo.SetSiteSp @Site = @Site,   -- SiteType
                   @Infobar = NULL; -- InfobarType

SET @ResourceId = ISNULL(@ResourceId, '');
SET @ResourceId = @ResourceId + '%';

DECLARE @t AS TABLE
(
    Ids NVARCHAR(30),
    Content NVARCHAR(200),
    Orders INT,
    TransDay DATE,
    Value DECIMAL(15, 2)
        DEFAULT 0,
    AccumulativeInMonth DECIMAL(15, 2)
        DEFAULT 0
);

--'PlanningPCs', N'Tổng số lượng kế hoạch/ngày, đoạn cọc'
--'RealPCs', N'Tổng số lượng thực hiện/ngày, đoạn cọc'
--'PlanningM3', N'Tổng khối lượng bê tông kế hoạch/ngày, m3 bê tông'
--'RealM3', N'Tổng khối lượng bê tông thực hiện/ngày, m3 bê tông'
--'PlanningMould', N'Tổng lượt khuôn kế hoạch/ngày, khuôn'
--'RealMould', N'Tổng lượt khuôn thực hiện/ngày, khuôn'
--'RealDeliveryPCs', N'Tổng số lượng Giao hàng/ngày, đoạn cọc'
--'RealDeliveryM3', N'Tổng khối lượng bê tông Giao hàng/ngày, m3 bê tông'
--'NumOfEmployee', N'Quân số'
--'NumOfAbsentEmployee', N'Vắng'

DECLARE @ids NVARCHAR(30),
        @content NVARCHAR(200),
        @order INT,
        @Day DATE,
        @i INT,
        @j INT;
SET @order = 0;

SET @ids = N'PlanningPCs';
SET @content = N'Tổng số lượng kế hoạch/ngày, đoạn cọc';
SET @order = @order + 1;
INSERT INTO @t
(
    Ids,
    Content,
    Orders,
    TransDay,
    Value
)
SELECT @ids,
       @content,
       @order,
       TransDate,
       SUM(PlanningPCs)
FROM dbo.ZVN_ProjProduction_Sum
WHERE SiteRef = @Site
      AND ResourceId LIKE @ResourceId
      AND TransDate >= @fromDate
      AND TransDate <= @toDate
GROUP BY TransDate;
SET @Day = DATEADD(DAY, -1, @fromDate);
WHILE (@Day < @toDate)
BEGIN
    SET @Day = DATEADD(DAY, 1, @Day);
    IF NOT EXISTS (SELECT * FROM @t WHERE Ids = @ids AND TransDay = @Day)
        INSERT INTO @t
        (
            Ids,
            Content,
            Orders,
            TransDay,
            Value
        )
        VALUES
        (@ids, @content, @order, @Day, 0);
END;
UPDATE t
SET AccumulativeInMonth =
    (
        SELECT SUM(PlanningPCs)
        FROM dbo.ZVN_ProjProduction_Sum
        WHERE SiteRef = @Site
              AND ResourceId LIKE @ResourceId
              AND TransDate >= @fromDate
              AND TransDate <= @toDate
    )
FROM @t t
WHERE Ids = @ids
      AND TransDay = @fromDate;

SET @ids = N'RealPCs';
SET @content = N'Tổng số lượng thực hiện/ngày, đoạn cọc';
SET @order = @order + 1;
INSERT INTO @t
(
    Ids,
    Content,
    Orders,
    TransDay,
    Value
)
SELECT @ids,
       @content,
       @order,
       TransDate,
       SUM(RealPCs)
FROM dbo.ZVN_ProjProduction_Sum
WHERE SiteRef = @Site
      AND ResourceId LIKE @ResourceId
      AND TransDate >= @fromDate
      AND TransDate <= @toDate
GROUP BY TransDate;
SET @Day = DATEADD(DAY, -1, @fromDate);
WHILE (@Day < @toDate)
BEGIN
    SET @Day = DATEADD(DAY, 1, @Day);
    IF NOT EXISTS (SELECT * FROM @t WHERE Ids = @ids AND TransDay = @Day)
        INSERT INTO @t
        (
            Ids,
            Content,
            Orders,
            TransDay,
            Value
        )
        VALUES
        (@ids, @content, @order, @Day, 0);
END;
UPDATE t
SET AccumulativeInMonth =
    (
        SELECT SUM(RealPCs)
        FROM dbo.ZVN_ProjProduction_Sum
        WHERE SiteRef = @Site
              AND ResourceId LIKE @ResourceId
              AND TransDate >= @fromDate
              AND TransDate <= @toDate
    )
FROM @t t
WHERE Ids = @ids
      AND TransDay = @fromDate;

SET @ids = N'RealDeliveryPCs';
SET @content = N'Tổng số lượng Giao hàng/ngày, đoạn cọc';
SET @order = @order + 1;
INSERT INTO @t
(
    Ids,
    Content,
    Orders,
    TransDay,
    Value
)
SELECT @ids,
       @content,
       @order,
       TransDate,
       SUM(RealDeliveryPCs)
FROM dbo.ZVN_ProjProduction_Sum
WHERE SiteRef = @Site
      AND ResourceId LIKE @ResourceId  AND 	 ResourceId<>  'V140263'
      AND TransDate >= @fromDate
      AND TransDate <= @toDate
GROUP BY TransDate;
SET @Day = DATEADD(DAY, -1, @fromDate);
WHILE (@Day < @toDate)
BEGIN
    SET @Day = DATEADD(DAY, 1, @Day);
    IF NOT EXISTS (SELECT * FROM @t WHERE Ids = @ids AND TransDay = @Day)
        INSERT INTO @t
        (
            Ids,
            Content,
            Orders,
            TransDay,
            Value
        )
        VALUES
        (@ids, @content, @order, @Day, 0);
END;
UPDATE t
SET AccumulativeInMonth =
    (
        SELECT SUM(RealDeliveryPCs)
        FROM dbo.ZVN_ProjProduction_Sum
        WHERE SiteRef = @Site
              AND ResourceId LIKE @ResourceId
              AND TransDate >= @fromDate
              AND TransDate <= @toDate
    )
FROM @t t
WHERE Ids = @ids
      AND TransDay = @fromDate;

SET @ids = N'PlanningM3';
SET @content = N'Tổng khối lượng bê tông kế hoạch/ngày, m3 bê tông';
SET @order = @order + 1;
INSERT INTO @t
(
    Ids,
    Content,
    Orders,
    TransDay,
    Value
)
SELECT @ids,
       @content,
       @order,
       TransDate,
       SUM(PlanningM3)
FROM dbo.ZVN_ProjProduction_Sum
WHERE SiteRef = @Site
      AND ResourceId LIKE @ResourceId
      AND TransDate >= @fromDate
      AND TransDate <= @toDate
GROUP BY TransDate;
SET @Day = DATEADD(DAY, -1, @fromDate);
WHILE (@Day < @toDate)
BEGIN
    SET @Day = DATEADD(DAY, 1, @Day);
    IF NOT EXISTS (SELECT * FROM @t WHERE Ids = @ids AND TransDay = @Day)
        INSERT INTO @t
        (
            Ids,
            Content,
            Orders,
            TransDay,
            Value
        )
        VALUES
        (@ids, @content, @order, @Day, 0);
END;
UPDATE t
SET AccumulativeInMonth =
    (
        SELECT SUM(PlanningM3)
        FROM dbo.ZVN_ProjProduction_Sum
        WHERE SiteRef = @Site
              AND ResourceId LIKE @ResourceId
              AND TransDate >= @fromDate
              AND TransDate <= @toDate
    )
FROM @t t
WHERE Ids = @ids
      AND TransDay = @fromDate;

SET @ids = N'RealM3';
SET @content = N'Tổng khối lượng bê tông thực hiện/ngày, m3 bê tông';
SET @order = @order + 1;
INSERT INTO @t
(
    Ids,
    Content,
    Orders,
    TransDay,
    Value
)
SELECT @ids,
       @content,
       @order,
       TransDate,
       SUM(RealM3)
FROM dbo.ZVN_ProjProduction_Sum
WHERE SiteRef = @Site
      AND ResourceId LIKE @ResourceId
      AND TransDate >= @fromDate
      AND TransDate <= @toDate
GROUP BY TransDate;
SET @Day = DATEADD(DAY, -1, @fromDate);
WHILE (@Day < @toDate)
BEGIN
    SET @Day = DATEADD(DAY, 1, @Day);
    IF NOT EXISTS (SELECT * FROM @t WHERE Ids = @ids AND TransDay = @Day)
        INSERT INTO @t
        (
            Ids,
            Content,
            Orders,
            TransDay,
            Value
        )
        VALUES
        (@ids, @content, @order, @Day, 0);
END;
UPDATE t
SET AccumulativeInMonth =
    (
        SELECT SUM(RealM3)
        FROM dbo.ZVN_ProjProduction_Sum
        WHERE SiteRef = @Site
              AND ResourceId LIKE @ResourceId
              AND TransDate >= @fromDate
              AND TransDate <= @toDate
    )
FROM @t t
WHERE Ids = @ids
      AND TransDay = @fromDate;

SET @ids = N'RealDeliveryM3';
SET @content = N'Tổng khối lượng bê tông Giao hàng/ngày, m3 bê tông';
SET @order = @order + 1;
INSERT INTO @t
(
    Ids,
    Content,
    Orders,
    TransDay,
    Value
)
SELECT @ids,
       @content,
       @order,
       TransDate,
       SUM(RealDeliveryM3)
FROM dbo.ZVN_ProjProduction_Sum
WHERE SiteRef = @Site
      AND ResourceId LIKE @ResourceId	  AND 	 ResourceId<>  'V140263'
      AND TransDate >= @fromDate
      AND TransDate <= @toDate
GROUP BY TransDate;
SET @Day = DATEADD(DAY, -1, @fromDate);
WHILE (@Day < @toDate)
BEGIN
    SET @Day = DATEADD(DAY, 1, @Day);
    IF NOT EXISTS (SELECT * FROM @t WHERE Ids = @ids AND TransDay = @Day)
        INSERT INTO @t
        (
            Ids,
            Content,
            Orders,
            TransDay,
            Value
        )
        VALUES
        (@ids, @content, @order, @Day, 0);
END;
UPDATE t
SET AccumulativeInMonth =
    (
        SELECT SUM(RealDeliveryM3)
        FROM dbo.ZVN_ProjProduction_Sum
        WHERE SiteRef = @Site
              AND ResourceId LIKE @ResourceId
              AND TransDate >= @fromDate
              AND TransDate <= @toDate
    )
FROM @t t
WHERE Ids = @ids
      AND TransDay = @fromDate;


-- ChieuPT EDIT: SỬA LẠI CÁCH LẤY SỐ LƯỢNG GIAO HÀNG CHO RIÊNG SITE PVC

----
--SET @ids = N'RealDeliveryM3';
--SET @content = N'Tổng khối lượng bê tông Giao hàng/ngày, m3 bê tông';
SET @order = @order + 1;
INSERT INTO @t
(
    Ids,
    Content,
    Orders,
    TransDay,
    Value
)
SELECT 'RealDeliveryPCs' AS ids,
       N'Tổng số lượng Giao hàng/ngày, đoạn cọc' AS Content,
       @order,
       CAST(m.trans_date AS DATE) trans_date,
       SUM(ISNULL(m.qty, 0)) qty
FROM dbo.matltran_mst m WITH (NOLOCK)
    JOIN dbo.item_mst I WITH (NOLOCK)
        ON I.site_ref = m.site_ref
           AND I.item = m.item
WHERE CAST(m.trans_date AS DATE)
      BETWEEN @fromDate AND @toDate
      AND LEFT(m.item, 2) IN ( 'AA', 'AC', 'AN' )
      AND m.loc IS NOT NULL
      AND m.ref_type = 'T'
      AND m.whse = 'KHGB'
      AND 'V140263' LIKE @ResourceId
      AND m.site_ref = 'PVC'
GROUP BY CAST(m.trans_date AS DATE);

SET @order = @order + 1;
INSERT INTO @t
(
    Ids,
    Content,
    Orders,
    TransDay,
    Value
)
SELECT 'RealDeliveryM3' AS ids,
       N'Tổng khối lượng bê tông Giao hàng/ngày, m3 bê tông' AS Content,
       @order,
       CAST(m.trans_date AS DATE) trans_date,
       SUM(ISNULL(m.qty, 0) * dbo.ZVN_GetPileLength(m.item) * p.M3PerM) value
FROM dbo.matltran_mst m WITH (NOLOCK)
    JOIN dbo.transfer_mst t WITH (NOLOCK)
        ON t.site_ref = m.site_ref
           AND m.ref_num = t.trn_num
    JOIN ZVN_ProjProduction_mst p WITH (NOLOCK)
        ON p.Item = m.item
           AND p.ProjNum = t.Uf_Project
           AND p.ResourceId = 'V140263'
WHERE CAST(m.trans_date AS DATE)
      BETWEEN @fromDate AND @toDate
      AND LEFT(m.item, 2) IN ( 'AA', 'AC', 'AN' )
      AND m.loc IS NOT NULL
      AND m.ref_type = 'T'
      AND m.whse = 'KHGB'
      AND 'V140263' LIKE @ResourceId
      AND m.site_ref = 'PVC'
GROUP BY CAST(m.trans_date AS DATE);


DECLARE @idM3 NVARCHAR(30) = N'RealDeliveryM3';
UPDATE t
SET AccumulativeInMonth =
    (
        SELECT SUM(Value)
        FROM @t
        WHERE 'V140263' LIKE @ResourceId
              AND TransDay >= @fromDate
              AND TransDay <= @toDate
              AND Ids = @idM3
    )
FROM @t t
WHERE Ids = @idM3
      AND TransDay = @fromDate;

DECLARE @idsPC NVARCHAR(30) = N'RealDeliveryPCs';
UPDATE t
SET AccumulativeInMonth =
    (
        SELECT SUM(Value)
        FROM @t
        WHERE 'V140263' LIKE @ResourceId
              AND TransDay >= @fromDate
              AND TransDay <= @toDate
              AND Ids = @idsPC
    )
FROM @t t
WHERE Ids = @idsPC
      AND TransDay = @fromDate

--SELECT * FROM  @t t

-----End ChieuPT


--SET @ids = 'PlanningMould'
--SET @content = N'Tổng lượt khuôn kế hoạch/ngày, khuôn'
--SET @order = @order + 1
--INSERT INTO @t(Ids, Content, Orders, TransDay, Value)
--	SELECT @ids, @content, @order, TransDate, SUM(PlanningMould)
--	FROM dbo.ZVN_ProjProduction_Sum
--	WHERE SiteRef = @Site
--		AND ResourceId LIKE @ResourceId
--		AND TransDate >= @fromDate
--		AND TransDate <= @toDate
--	GROUP BY TransDate
--UPDATE t
--SET AccumulativeInMonth = (SELECT SUM(PlanningMould)
--	FROM dbo.ZVN_ProjProduction_Sum
--	WHERE SiteRef = @Site
--		AND ResourceId LIKE @ResourceId
--		AND TransDate >= @fromDate
--		AND TransDate <= @toDate)
--FROM @t t
--WHERE Ids = @ids AND TransDay = @fromDate

--SET @ids = 'RealMould'
--SET @content = N'Tổng lượt khuôn thực hiện/ngày, khuôn'
--SET @order = @order + 1
--INSERT INTO @t(Ids, Content, Orders, TransDay, Value)
--	SELECT @ids, @content, @order, TransDate, SUM(RealMould)
--	FROM dbo.ZVN_ProjProduction_Sum
--	WHERE SiteRef = @Site
--		AND ResourceId LIKE @ResourceId
--		AND TransDate >= @fromDate
--		AND TransDate <= @toDate
--	GROUP BY TransDate
--UPDATE t
--SET AccumulativeInMonth = (SELECT SUM(RealMould)
--	FROM dbo.ZVN_ProjProduction_Sum
--	WHERE SiteRef = @Site
--		AND ResourceId LIKE @ResourceId
--		AND TransDate >= @fromDate
--		AND TransDate <= @toDate)
--FROM @t t
--WHERE Ids = @ids AND TransDay = @fromDate


--Select Result
SELECT Ids,
       Content,
       Orders,
       TransDay,
       Value,
       AccumulativeInMonth
FROM @t
ORDER BY Orders,
         TransDay;