USE [SyteLine_BI]
GO
/****** Object:  StoredProcedure [dbo].[PV_SP_CountValueExist]    Script Date: 9/26/2019 8:43:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--28-8-2019
-- store get giá trị tồn đầu, tồn cuối-- tính giá theo giá bình quân
--PV_SP_CountValueExist	'PVL' , '2019-08-01'
ALTER PROCEDURE [dbo].[PV_SP_CountValueExist]
    @site NVARCHAR(10),
    @fromdate DATE
AS
DELETE FACT_CountValueExist
WHERE month = MONTH(@fromdate)
      AND YEAR = YEAR(@fromdate)
      AND site = @site;
INSERT INTO FACT_CountValueExist

--SELECT a.month,a.year,a.qtytondau,a.valuetondau,a.site,a.item,a.ngay,a.duan,a.qtytoncuoi,a.valuetoncuoi,a.product_code,a.whse FROM (
SELECT MONTH(@fromdate) month,
       YEAR(@fromdate) year,
       SUM(ISNULL(qtytondau, 0)) qtytondau,
       SUM(ISNULL(valuetondau, 0)) valuetondau,
       @site site,
       a.item,
       '1999-01-01' ngay,
       '' duan,
       SUM(ISNULL(qtytoncuoi, 0)) qtytoncuoi,
       CASE
           WHEN (SUM(ISNULL(qtytondau, 0)) + SUM(ISNULL(qtyXuat, 0)) + SUM(ISNULL(qtynhap, 0))) = 0 THEN
               0
           ELSE
       ((SUM(ISNULL(valuetondau, 0)) + SUM(ISNULL(valuexuat, 0)) + SUM(ISNULL(valuenhap, 0)))
        / (SUM(ISNULL(qtytondau, 0)) + SUM(ISNULL(qtyXuat, 0)) + SUM(ISNULL(qtynhap, 0)))
       ) * SUM(ISNULL(qtytoncuoi, 0))
       END AS valuetoncuoi,
       a.product_code,
       a.whse,
       SUM(ISNULL(a.qtynhap, 0)) qtynhap,
       SUM(a.qtyXuat) qtyXuat
FROM
(
    SELECT SUM(qty) qtytondau,
           SUM(A.value) valuetondau,
           0 qtyXuat,
           0 valuexuat,
           0 qtynhap,
           0 valuenhap,
           0 qtytoncuoi,
           0 valuetoncuoi,
           A.item,
           A.product_code,
           A.whse
    FROM
    ( -- ĐẦU KÌ
        SELECT m.site_ref AS site,
               (m.item) item,
               SUM(ISNULL(m.qty, 0)) qty,
               SUM(ISNULL(qty, 0) * ISNULL(cost, 0)) value,
               i.product_code,
               m.whse
        FROM ERP.SyteLine_Apps.dbo.matltran_mst m WITH (NOLOCK)
            JOIN ERP.SyteLine_Apps.dbo.item_mst i WITH (NOLOCK)
                ON i.item = m.item
                   AND m.site_ref = i.site_ref
        WHERE CAST(m.trans_date AS DATE) < CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, @fromdate), 0) AS DATE)
              AND m.site_ref = @site
              AND m.loc IS NOT NULL
              AND m.whse IS NOT NULL
        --AND m.qty <> 0
        GROUP BY (m.item),
                 m.site_ref,
                 i.product_code,
                 m.whse
    ) A
    GROUP BY A.item,
             A.whse,
             A.product_code
    UNION ALL
    SELECT 0 qtytondau,
           0 valuetondau,
           SUM(a.qty) qtyXuat,
           SUM(a.value) valuexuat,
           0 qtynhap,
           0 valuenhap,
           0 qtytoncuoi,
           0 valuetoncuoi,
           a.item,
           product_code,
           a.whse
    FROM
    ( -- xuất
        SELECT m.site_ref AS site,
               (m.item) item,
               SUM(ISNULL(m.qty, 0)) qty,
               SUM(ISNULL(qty, 0) * ISNULL(cost, 0)) value,
               i.product_code,
               m.whse
        FROM ERP.SyteLine_Apps.dbo.matltran_mst m WITH (NOLOCK)
            JOIN ERP.SyteLine_Apps.dbo.item_mst i WITH (NOLOCK)
                ON i.item = m.item
                   AND m.site_ref = i.site_ref
        WHERE MONTH(m.trans_date) = MONTH(@fromdate)
              AND YEAR(m.trans_date) = YEAR(@fromdate)
              AND m.site_ref = @site
              AND m.loc IS NOT NULL
              AND m.whse IS NOT NULL
              AND m.qty < 0
        GROUP BY (m.item),
                 m.site_ref,
                 i.product_code,
                 m.whse
    ) a
    GROUP BY a.item,
             a.whse,
             a.product_code
    UNION ALL -- nhập
    SELECT 0 qtytondau,
           0 valuetondau,
           0 qtyXuat,
           0 valuexuat,
           SUM(A.qty) qtynhap,
           SUM(A.value) valuenhap,
           0 qtytoncuoi,
           0 valuetoncuoi,
           A.item,
           product_code,
           whse
    FROM
    ( -- NHẬP
        SELECT m.site_ref AS site,
               (m.item) item,
               SUM(ISNULL(m.qty, 0)) qty,
               SUM(ISNULL(qty, 0) * ISNULL(cost, 0)) value,
               i.product_code,
               m.whse
        FROM ERP.SyteLine_Apps.dbo.matltran_mst m WITH (NOLOCK)
            JOIN ERP.SyteLine_Apps.dbo.item_mst i WITH (NOLOCK)
                ON i.item = m.item
                   AND m.site_ref = i.site_ref
        WHERE MONTH(m.trans_date) = MONTH(@fromdate)
              AND YEAR(m.trans_date) = YEAR(@fromdate)
              AND m.site_ref = @site
              AND m.loc IS NOT NULL
              AND m.whse IS NOT NULL
              AND m.qty > 0
        GROUP BY (m.item),
                 m.site_ref,
                 i.product_code,
                 m.whse
    ) A
    GROUP BY A.item,
             A.whse,
             A.product_code
    UNION ALL
    SELECT 0 qtytondau,
           0 valuetondau,
           0 qtyXuat,
           0 valuexuat,
           0 qtynhap,
           0 valuenhap,
           SUM(qty) qtytoncuoi,
           SUM(A.value) valuetoncuoi,
           A.item,
           A.product_code,
           A.whse
    FROM
    (
        SELECT m.site_ref AS site,
               (m.item) item,
               SUM(ISNULL(m.qty, 0)) qty,
               SUM(ISNULL(qty, 0) * ISNULL(cost, 0)) value,
               i.product_code,
               m.whse
        FROM ERP.SyteLine_Apps.dbo.matltran_mst m WITH (NOLOCK)
            JOIN ERP.SyteLine_Apps.dbo.item_mst i WITH (NOLOCK)
                ON i.item = m.item
                   AND m.site_ref = i.site_ref
        WHERE CAST(m.trans_date AS DATE) <= EOMONTH(@fromdate)
              AND m.site_ref = @site
              AND m.loc IS NOT NULL
              AND m.whse IS NOT NULL
              AND m.qty <> 0
        GROUP BY (m.item),
                 m.site_ref,
                 i.product_code,
                 m.whse
    ) A
    GROUP BY A.item,
             A.whse,
             A.product_code
) a
GROUP BY a.item,
         a.whse,
         a.product_code;
--)a

DELETE FACT_CountValueExist
WHERE Qty = 0
      AND QtyEnd = 0
      AND QtyNhap = 0
      AND QtyXuat = 0;
UPDATE FACT_CountValueExist
SET valueend = 0
WHERE qtyend = 0;

DELETE FACT_CountValueExist WHERE MONTH> MONTH(GETDATE()) AND YEAR= YEAR(GETDATE())