USE [SyteLine_BI]
GO
/****** Object:  StoredProcedure [dbo].[PV_SP_FactRawMaterialGroupDetailsByDay]    Script Date: 7/31/2019 2:54:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Author:		Nguyen Tien Dat
-- Create date: 2018-10-09
-- Description: lấy thông tin tôn kho nguyên vật liệu
-- EXEC dbo.[PV_SP_FactRawMaterialGroupDetailsByDay] @site_ref = N'PVC' -- nvarchar(10)

ALTER PROC [dbo].[PV_SP_FactRawMaterialGroupDetailsByDay]
	@site_ref NVARCHAR(10)
AS
BEGIN
	DECLARE @fromDate DATE ,
            @toDate DATE,
			@dauKi DATE

	SET @dauKi = '2018-06-01'--đầu kì PVD

	SELECT @dauKi = CAST(Uf_GoLiveDate AS DATE) FROM erp.SyteLine_Apps.dbo.site WHERE site = @site_ref
	IF (@dauKi IS NULL)
	BEGIN
		SET @dauKi = '2019-01-01'
	END

    SET @fromDate = DATEADD(MONTH, -1, DATEADD(day, 1 - DAY(GETDATE()), GETDATE()))
    SET @toDate = dbo.EndOfMonth(GETDATE())

	IF (@toDate > CAST(GETDATE() AS DATE))
	BEGIN
		SET @toDate = CAST(GETDATE() AS DATE)
	END

	DECLARE @tb TABLE
	(
		Site NCHAR(10) NULL,
		Year INT NULL,
		Month INT NULL,
		Item NVARCHAR(50) NULL,
		whse NVARCHAR(6) NULL,
		TransDate DATETIME NULL,
		InStock DECIMAL(28, 10) DEFAULT 0,
		OutStock DECIMAL(28, 10) DEFAULT 0,
		StockPrepare DECIMAL(28, 10) DEFAULT 0,
		Measure_Production NVARCHAR(10)
	)

	----- tính tồn kho hiện tại -------------------------------------------------------

	DECLARE @itemStock TABLE
    (
        SiteRef NVARCHAR(10) ,
        Item NVARCHAR(50) ,
		whse NVARCHAR(6),
        TransDate DATE ,
		instock DECIMAL(28,10) ,
        outstock DECIMAL(28,10) ,
        qty DECIMAL(28,10),
		[trans_type] NVARCHAR(10)
    )

    DECLARE @item TABLE
    (
        SiteRef NVARCHAR(10) ,
        Item NVARCHAR(50),
		whse NVARCHAR(6)
    )
	-- đầu kì
	INSERT  INTO @itemStock
		(
			SiteRef ,
			Item ,
			whse,
			TransDate ,
			qty
		)
	SELECT
			m.site_ref ,
			r.Item ,
			whse,
			@dauKi ,
			qty
	FROM	ERP.SyteLine_Apps.dbo.matltran_mst m ,
			dbo.DimRawMaterialGroup r
	WHERE   m.whse IS NOT NULL
			AND m.loc IS NOT NULL
			AND m.site_ref = @site_ref
			AND m.trans_date < @dauKi
			AND LEFT(m.item, LEN(r.Item)) = r.Item
			AND m.site_ref = r.Site
			--AND m.whse IN ('MAIN','BTL','CKH')

	INSERT  INTO @item(SiteRef ,Item, whse)
    SELECT SiteRef ,item, whse FROM @itemStock i WHERE NOT EXISTS(SELECT 1 FROM @item WHERE Item = i.Item AND whse = i.whse AND SiteRef = i.SiteRef) GROUP BY SiteRef, Item,i.whse

	INSERT @tb
	(
	    Site,
	    Year,
	    Month,
	    Item,
		whse,
	    TransDate,
	    InStock,
		OutStock,
	    Measure_Production
	)

	SELECT  r.SiteRef ,
			s.Year,
			s.Month,
			r.Item ,
			r.whse,
			s.Date ,
			ISNULL(( SELECT SUM(qty)
						FROM   @itemStock
						WHERE  item = r.item
							AND whse = r.whse
							AND SiteRef = r.SiteRef
							AND TransDate = CAST(s.Date AS DATE)
							AND qty >= 0
					), 0),
			ISNULL(( SELECT SUM(qty)
						FROM   @itemStock
						WHERE  item = r.item
							AND whse = r.whse
							AND SiteRef = r.SiteRef
							AND TransDate = CAST(s.Date AS DATE)
							AND qty < 0
					), 0),
			Measure_Production = 'KLTon'
    FROM    @item r ,
            dbo.DimDate s
    WHERE   CAST(s.Date AS DATE) = @dauKi

	DELETE @itemStock
	-- tính sl tồn [NHẬP],[XUẤT] trong kỳ
    INSERT  INTO @itemStock
		(
			SiteRef ,
			Item ,
			whse,
			TransDate ,
			qty,
			trans_type
		)
    SELECT
			m.site_ref ,
			r.Item ,
			m.whse,
			m.trans_date ,
			qty,
			m.trans_type
    FROM    ERP.SyteLine_Apps.dbo.matltran_mst m ,
            dbo.DimRawMaterialGroup r
    WHERE   m.whse IS NOT NULL
            AND m.loc IS NOT NULL
            AND m.site_ref = @site_ref
            AND m.trans_date >= @dauKi
            AND m.trans_date <= @toDate
            AND LEFT(m.item, LEN(r.Item)) = r.Item
			AND m.site_ref = r.Site
			--AND m.whse IN ('MAIN','BTL','CKH')
			--AND m.trans_type <> 'T'

	INSERT  INTO @item(SiteRef ,Item, whse)
    SELECT SiteRef ,item, i.whse FROM @itemStock i WHERE NOT EXISTS(SELECT 1 FROM @item WHERE Item = i.Item AND whse = i.whse AND SiteRef = i.SiteRef) GROUP BY SiteRef, Item, i.whse

	INSERT @tb
	(
	    Site,
	    Year,
	    Month,
	    Item,
		whse,
	    TransDate,
	    InStock,
		OutStock,
	    Measure_Production
	)

	SELECT  r.SiteRef ,
			s.Year,
			CAST(s.Month AS INT),
			r.Item ,
			r.whse,
			s.Date ,
			ISNULL(( SELECT SUM(qty)
						FROM   @itemStock
						WHERE  item = r.item
							AND whse = r.whse
							AND SiteRef = r.SiteRef
							AND TransDate = CAST(s.Date AS DATE)
							AND qty >=0
							--AND trans_type <> 'W'
					), 0),
			ISNULL(( SELECT SUM(qty)
						FROM   @itemStock
						WHERE  item = r.item
							AND whse = r.whse
							AND SiteRef = r.SiteRef
							AND TransDate = CAST(s.Date AS DATE)
							AND (qty < 0 )
							--OR trans_type = 'W')
					), 0),
			Measure_Production = 'KLTon'
    FROM    @item r ,
            dbo.DimDate s
    WHERE   CAST(s.Date AS DATE) <= @toDate

	------------- tính số lượng chuẩn bị xuất
	DELETE @itemStock



	INSERT @tb
	(
	    Site,
	    Year,
	    Month,
	    Item,
		whse,
	    TransDate,
	    InStock, -- cho vào instock tạm
	    StockPrepare,
	    Measure_Production
	)
	SELECT A.site_ref, A.year, A.month, A.ItemCode, A.whse, A.RecordDate, SUM(A.qty), SUM(A.qtyPre), A.Measure_Production
	FROM
	(
		SELECT	@site_ref site_ref,
				YEAR(jt.trans_date) year,
				MONTH(jt.trans_date) month,
				r.Item ItemCode,
				jt.whse,
				jt.trans_date RecordDate,
				jm.matl_qty * jt.qty qty,
				0 qtyPre,
				Measure_Production = 'KLChuanBi'
		FROM ERP.SyteLine_Apps.dbo.jobmatl_mst jm
		INNER JOIN dbo.DimRawMaterialGroup r ON LEFT(jm.item, LEN(r.Item)) = r.Item AND jm.site_ref = r.Site
		INNER JOIN
		(
			SELECT jt.job, jt.suffix, jt.oper_num, jt.trans_date, jt.whse, SUM(jt.qty_complete + jt.qty_scrapped) qty
			FROM ERP.SyteLine_Apps.dbo.jobtran_mst jt
			WHERE jt.site_ref = @site_ref AND jt.posted = 0 AND CAST(jt.trans_date AS DATE) <= @toDate AND CAST(jt.trans_date AS DATE) >= @dauKi
			GROUP BY jt.job, jt.suffix, jt.oper_num, jt.trans_date, jt.whse
		) AS jt ON jt.job = jm.job AND jt.suffix = jm.suffix AND jt.oper_num = jm.oper_num
		WHERE jm.site_ref = @site_ref
	) AS A
	GROUP BY A.site_ref, A.year, A.month, A.ItemCode, A.whse, A.RecordDate, A.Measure_Production

	--SELECT jm.item, jm.matl_qty * jt.qty
	--FROM dbo.jobmatl_mst jm
	--INNER JOIN
	--(
	--	SELECT jt.job, jt.suffix, jt.oper_num, SUM(jt.qty_complete + jt.qty_scrapped) qty
	--	FROM dbo.jobtran_mst jt
	--	WHERE jt.site_ref = 'PVD' AND jt.posted = 0
	--	GROUP BY jt.job, jt.suffix, jt.oper_num
	--) AS jt ON jt.job = jm.job AND jt.suffix = jm.suffix AND jt.oper_num = jm.oper_num

	DELETE FactRawMaterialGroupDetailsByDay WHERE Site = @site_ref

	INSERT dbo.FactRawMaterialGroupDetailsByDay
	(
	    Site,
	    Year,
	    Month,
	    Item,
		Whse,
	    TransDate,
	    InStock,
		OutStock,
	    StockPrepare,
		Measure_Production
	)
	SELECT
		Site,
		Year,
		Month,
		Item,
		whse,
		TransDate,
		SUM(InStock)InStock,
		SUM(OutStock)OutStock,
		SUM(StockPrepare)StockPrepare,
		Measure_Production
	FROM @tb
	WHERE CAST(TransDate AS DATE) >= '2018-01-01'
	GROUP BY
		Site,
		Year,
		Month,
		Item,
		whse,
		TransDate,
		Measure_Production

	UPDATE fr
	SET fr.u_m = r.U_M
    FROM dbo.FactRawMaterialGroupDetailsByDay fr
	LEFT JOIN dbo.DimRawMaterialGroup r ON r.Site = fr.Site AND r.Item = fr.Item
END
--SELECT * FROM dbo.FactRawMaterialGroupByDay where item='CA1710' ORDER BY Measure_Production, Item
----SELECT * FROM dbo.FactRawMaterialGroup where item='CA1710' ORDER BY Measure_Production, Item


/*
SELECT i.product_code,*
FROM dbo.FactRawMaterialGroupByDay f
JOIN ERP.SyteLine_Apps.dbo.item_mst i  ON LEFT(i.item, LEN(f.Item)) = f.Item AND i.site_ref = 'PVD'

*/
RETURN
SELECT SUM(InStock) + SUM(OutStock) FROM dbo.FactRawMaterialGroupDetailByDay WHERE Item LIKE 'CA2012'  AND Measure_Production = 'KLTON' AND Year = 2018
			 AND CAST(TransDate AS DATE) < '2018-11-01' AND (InStock <> 0 OR OutStock <> 0) AND CAST(TransDate AS DATE) >= '2018-06-01'

			 SELECT
				SUM(InStock)
				,SUM(OutStock)
				 FROM dbo.FactRawMaterialGroupDetailByDay WHERE Item LIKE 'CA2012'  AND Measure_Production = 'KLTON' AND Year = 2018
			 AND CAST(TransDate AS DATE) >= '2018-11-01' AND CAST(TransDate AS DATE) <= '2018-11-30' AND (InStock <> 0 OR OutStock <> 0)

SELECT * FROM FactRawMaterialGroupByDay WHERE Item LIKE 'CA2012' AND Stock > 0  AND Measure_Production = 'KLTON' AND Year = 2018
			 AND CAST(TransDate AS DATE) >= '2018-11-01'

SELECT
			m.site_ref ,
			r.Item ,
			m.trans_date ,
			qty,
			m.whse,
			m.trans_type,*
    FROM    ERP.SyteLine_Apps.dbo.matltran_mst m ,
            dbo.DimRawMaterialGroup r
    WHERE   m.whse IS NOT NULL
            AND m.loc IS NOT NULL
            AND m.site_ref = 'PVD'
            AND m.trans_date >= '2018-06-01'
            AND m.trans_date < '2018-11-01'
            AND LEFT(m.item, LEN(r.Item)) = r.Item
			--AND m.whse IN ('MAIN','BTL','CKH')
			AND r.Item = 'CA2012'
			AND m.trans_date = '2018-06-05'
			AND m.trans_type= 'T'
			ORDER BY m.trans_date


			SELECT * FROM dbo.FactRawMaterialGroupDetailByDay WHERE Item LIKE 'CA2012'  AND Measure_Production = 'KLTON' AND Year = 2018
			 AND CAST(TransDate AS DATE) = '2018-06-01' AND (InStock <> 0 OR OutStock <> 0)
			 SELECT * FROM dbo.FactProductionDetailPosted

			 SELECT * FROM FactRawMaterialGroupDetailsByDay