USE [SyteLine_BI]
GO
/****** Object:  StoredProcedure [dbo].[PV_SP_FactProductionDetail]    Script Date: 7/31/2019 2:50:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Author:		Nguyen Tien Dat
-- Create date: 2018-07-19
-- Description: lấy thông tin sản xuất chi tiết theo ngày
-- EXEC dbo.[PV_SP_FactProductionDetail] @site_ref = N'PVD' -- nvarchar(10)

ALTER PROC [dbo].[PV_SP_FactProductionDetail]
	@site_ref NVARCHAR(10)
AS
BEGIN
	DECLARE @GoLiveDate DATETIME

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

	DELETE dbo.FactProductionDetail WHERE Site = @site_ref --AND dbo.FAB_CompareCurrDate(CAST(Year AS VARCHAR) + '-' + CAST(Month AS VARCHAR) + '-01') > 0


	DECLARE @tb_temp TABLE
	(
		[Site] [NCHAR](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[Year] [INT] NULL,
		[Month] [INT] NULL,
		[ProjNum] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[PileLength] [NUMERIC](8, 2) NULL,
		[ItemType] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[Shift] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[TranDate] [DATETIME] NULL,
		[PlanQty] [DECIMAL](28, 10) NULL,
		[ProdQty] [DECIMAL](28, 10) NULL,
		[Resource] [NVARCHAR](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		Job NVARCHAR(30),
		Suffix SMALLINT
	)

	-- insert thực tế theo kế hoạch
	INSERT @tb_temp
	(
	    Site,
	    Year,
	    Month,
	    ProjNum,
	    PileLength,
	    ItemType,
	    Shift,
	    TranDate,
	    PlanQty,
	    ProdQty,
		Resource,
		Job,
		Suffix
	)
	SELECT
		@site_ref,
		YEAR(p.TransDate),
		MONTH(p.TransDate),
		--'PJ' + dbo.ZVN_GetPileProject(j.item) + '01',
		co.Uf_ProjNum,
        dbo.ZVN_GetPileLength(j.item),
		dbo.ZVN_GetPileDiameter(j.item),
		p.Shift,
		p.TransDate,
		SUM(p.PlanQty),
		SUM(p.ProdQty),
		p.Resource,
		j.job,
		j.suffix
	FROM ERP.SyteLine_Apps.dbo.FAB_ProductionPlan_Shift_mst p
	INNER JOIN ERP.SyteLine_Apps.dbo.job_mst j ON j.job = p.Job
												  AND j.site_ref = p.SiteRef
												  AND p.JobSuffix = j.suffix
	INNER JOIN ERP.SyteLine_Apps.dbo.co_mst co ON co.co_num = j.ord_num AND co.site_ref = j.site_ref
	INNER JOIN ERP.SyteLine_Apps.dbo.proj_mst pj ON pj.proj_num = co.Uf_ProjNum AND pj.site_ref = co.site_ref
	WHERE j.site_ref = @site_ref
	--WHERE EXISTS
	--(
	--	SELECT	1
	--	FROM	ERP.SyteLine_Apps.dbo.jobtran_mst
	--	WHERE	j.site_ref = site_ref
	--			AND j.job = job
	--			AND j.suffix = suffix
	--			AND posted = 1
	--)
	GROUP BY	YEAR(p.TransDate),
				MONTH(p.TransDate),
				--'PJ' + dbo.ZVN_GetPileProject(j.item) + '01',
				co.Uf_ProjNum,
				dbo.ZVN_GetPileLength(j.item),
				dbo.ZVN_GetPileDiameter(j.item),
				p.Shift,
				p.TransDate,
				p.Resource,
				j.job,
				j.suffix

	INSERT @tb_temp
	(
	    Site,
	    Year,
	    Month,
	    ProjNum,
	    PileLength,
	    ItemType,
	    Shift,
	    TranDate,
	    PlanQty,
	    ProdQty,
		Resource,
		Job,
		Suffix
	)
	SELECT @site_ref,
		   YEAR(jt.trans_date) Year,
		   MONTH(jt.trans_date) Month,
		   --'PJ' + dbo.ZVN_GetPileProject(j.item) + '01' ProjNum,
		   co.Uf_ProjNum,
		   dbo.ZVN_GetPileLength(j.item) PileLength,
		   dbo.ZVN_GetPileDiameter(j.item) ItemType,
		   jt.shift,
		   jt.trans_date,
		   0 PlanQty,
		   SUM(jt.qty_complete) ProdQty,
		   jt.Uf_ZVN_Machine,
		   j.job,
		   j.suffix
	FROM ERP.SyteLine_Apps.dbo.jobtran_mst jt
		INNER JOIN ERP.SyteLine_Apps.dbo.job_mst j
			ON j.job = jt.job
			   AND j.suffix = jt.suffix
			   AND jt.site_ref = j.site_ref
		INNER JOIN ERP.SyteLine_Apps.dbo.co_mst co ON co.co_num = j.ord_num AND co.site_ref = j.site_ref
		INNER JOIN ERP.SyteLine_Apps.dbo.proj_mst p ON p.proj_num = co.Uf_ProjNum AND p.site_ref = co.site_ref
	WHERE j.type = 'J'
		  AND dbo.FAB_IsPileItem(j.item) > 0
		  AND j.rework = 0
		  AND CAST(jt.trans_date AS DATE) >= CAST(@GoLiveDate AS DATE)
		  AND jt.Uf_ZVN_Machine IS NOT NULL
		  AND j.site_ref = @site_ref
		  AND NOT EXISTS
	(
		SELECT 1
		FROM @tb_temp
		WHERE --('PJ' + dbo.ZVN_GetPileProject(j.item) + '01') = ProjNum
			  co.Uf_ProjNum = ProjNum
			  AND CAST(jt.trans_date AS DATE) = CAST(TranDate AS DATE)
			  AND dbo.ZVN_GetPileLength(j.item) = PileLength
			  AND dbo.ZVN_GetPileDiameter(j.item) = ItemType
			  AND Shift = jt.shift
			  AND Resource = jt.Uf_ZVN_Machine
			  AND Job = jt.job
			  AND Suffix = jt.suffix
	)
	GROUP BY YEAR(jt.trans_date),
			 MONTH(jt.trans_date),
			 --'PJ' + dbo.ZVN_GetPileProject(j.item) + '01',
			 co.Uf_ProjNum,
			 dbo.ZVN_GetPileLength(j.item),
			 dbo.ZVN_GetPileDiameter(j.item),
			 jt.shift,
			 jt.trans_date,
			 jt.Uf_ZVN_Machine,
			 j.job,
			 j.suffix
--SELECT * FROM @tb_temp WHERE ProjNum = 'PJ17320501' ORDER BY TranDate

	INSERT dbo.FactProductionDetail
	(
	    Site,
	    Year,
	    Month,
	    ProjNum,
	    PileLength,
	    ItemType,
	    Shift,
	    TranDate,
	    PlanQty,
	    ProdQty,
		Resource
	)
	SELECT
		Site,
	    Year,
	    Month,
	    ProjNum,
	    PileLength,
	    ItemType,
	    Shift,
	    TranDate,
	    SUM(PlanQty),
	    SUM(ProdQty),
		Resource
	FROM @tb_temp
	GROUP BY
		Site,
	    Year,
	    Month,
	    ProjNum,
	    PileLength,
	    ItemType,
	    Shift,
	    TranDate,
		Resource


	DELETE dbo.FactProductionDetail WHERE ProjNum = 'PJ15101901' AND YEAR = 2016
	DELETE dbo.FactProductionDetail WHERE ProjNum = 'PJ17318701'

	--nút Update production plan
	EXEC ERP.SyteLine_Apps.[dbo].[FAB_ProductionPlan_Update] @Site = @site_ref -- nvarchar(8)

END