USE [MES]
GO
/****** Object:  StoredProcedure [SCRAP].[GetCharges]    Script Date: 5 jul. 2020 17:40:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [SCRAP].[GetCharges] @from DATETIME = NULL
	,@to DATETIME = NULL
	,@timeZoneCode CHAR(10) = NULL
	,@shiftId INT = NULL
AS
BEGIN

	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE	@procName VARCHAR(300)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	PRINT @procName + ' Parameters:'
					+ '@from: ' + ISNULL(CAST(@from AS VARCHAR), '')
					+ ', @to: ' + ISNULL(CAST(@to AS VARCHAR), '')
					+ ', @timezoneCode: ' + ISNULL(CAST(@timezoneCode AS VARCHAR), '')

					
	-----------------------
	-- Parameters default
	-----------------------
	IF (@timeZoneCode IS NULL) SET @timeZoneCode = 'EDTIND'

	-----------------------
	-- Variables
	-----------------------
	IF (@to IS NULL) SET @to = DATEADD(day,1,@from)


	DECLARE @fromUTC DATETIME = IH.ToUTC(@from,@timeZoneCode)
	DECLARE @toUTC DATETIME = IH.ToUTC(@to,@timeZoneCode)



	-----------------------
	-- Begin Query
	-----------------------
	DECLARE @allMaterials TABLE (
		[Name] VARCHAR(MAX)
		,[NameMax] VARCHAR(MAX)
		)

	INSERT INTO @allMaterials
	SELECT DISTINCT [Name] = sm.Name
		,[NameMax] = CONCAT (
			sm.Name
			,'_max'
			)
	FROM SCRAP.Charges c
	JOIN SCRAP.ChargeDetails cd ON cd.ChargeId = c.Id
	LEFT JOIN MES.Heats h ON h.Id = c.HeatId
	JOIN SCRAP.ScrapMaterials sm ON sm.Id = cd.ScrapMaterialId
		WHERE @ShiftId IS NULL AND
			(
			(c.ChargeTimestamp BETWEEN @fromUTC AND @toUTC)
			OR (@fromUTC IS NULL AND @toUTC IS NULL)
			)
			OR @ShiftId = h.ShiftId AND  h.ProductionDate = @from
	--WHERE (
	--		(
	--			c.ChargeTimestamp BETWEEN @fromUTC
	--				AND @toUTC
	--			)
	--		OR (
	--			@fromUTC IS NULL
	--			AND @toUTC IS NULL
	--			)
	--		)

	DECLARE @materialsQuery VARCHAR(MAX)

	SELECT @materialsQuery = COALESCE(@materialsQuery + ',', '') + '[' + CONVERT(VARCHAR, am.Name) + ']'
	FROM @allMaterials am

	DECLARE @materialsQueryMax VARCHAR(MAX)

	SELECT @materialsQueryMax = COALESCE(@materialsQueryMax + ',', '') + '[' + CONVERT(VARCHAR, am.[NameMax]) + ']'
	FROM @allMaterials am

	PRINT @materialsQueryMax;

	DECLARE @selectMaterialNameQuery VARCHAR(MAX)

	SELECT @selectMaterialNameQuery = COALESCE(@selectMaterialNameQuery + ',', '') + '[' + CONVERT(VARCHAR, am.Name) + '] AS [' + CONVERT(VARCHAR, am.Name) + '] '
	FROM @allMaterials am

	DECLARE @selectMaterialNameQueryMax VARCHAR(MAX)

	SELECT @selectMaterialNameQueryMax = COALESCE(@selectMaterialNameQueryMax + ',', '') + '[' + CONVERT(VARCHAR, am.NameMax) + '] AS [' + CONVERT(VARCHAR, am.NameMax) + '] '
	FROM @allMaterials am

	--DECLARE @selectMaterialNameQueryMax VARCHAR(MAX)
	--SELECT @selectMaterialNameQueryMax = COALESCE(@selectMaterialNameQueryMax+',' ,'') + '[' + CONVERT(VARCHAR, am.NameMax) + '] AS [' + CONVERT(VARCHAR, am.NameMax) + ']'
	--FROM @allMaterials am
	SELECT [ChargeId] = c.Id
		,[Heat] = h.Name
		,[Bucket] = c.Bucket
		,[ChargeNumber] = c.ChargeNumber
		,[ScrapRecipe] = sr.Name
		,[ScrapRecipeId] = c.ScrapRecipeId
		,[ChargeTimestamp] = CONVERT(VARCHAR, IH.ToLocal(c.[ChargeTimestamp],@timeZoneCode), 120)
		,[Operator] = c.Operator
		,[Furnace] = c.Furnace
		,[Crew] = c.Crew
		,[Shift] = (case when h.ShiftId = 1 then 'D' else 'N' end)
		,[OrderNumber] = c.OrderNumber
		,[ScrapMaterialName] = sm.Name
		,[Weight] = cd.[Weight]
		,[Total] = NULL
		,[BucketTotalcost] = NULL
		,[RecipeCost] = NULL
	INTO #charges
	FROM SCRAP.Charges c
	JOIN SCRAP.ChargeDetails cd ON cd.ChargeId = c.Id
	JOIN SCRAP.ScrapMaterials sm ON sm.Id = cd.ScrapMaterialId
	LEFT JOIN MES.Heats h ON h.Id = c.HeatId
	LEFT JOIN SCRAP.ScrapRecipes sr ON sr.Id = c.ScrapRecipeId
	WHERE @ShiftId IS NULL AND
			(
			(c.ChargeTimestamp BETWEEN @fromUTC AND @toUTC)
			OR (@fromUTC IS NULL AND @toUTC IS NULL)
			)
			OR @ShiftId = h.ShiftId AND  h.ProductionDate = @from
	--WHERE (
	--		(
	--			c.ChargeTimestamp BETWEEN @fromUTC
	--				AND @toUTC
	--			)
	--		OR (
	--			@fromUTC IS NULL
	--			AND @toUTC IS NULL
	--			)
	--		)

	SELECT ChargeId_max = c.Id
		,MaterialName_max = sm.Name + '_max'
		,RecipeId = c.ScrapRecipeId
		,MaxWeight = (
			SELECT SUM(Weight)
			FROM SCRAP.ScrapRecipeLayers srl
			JOIN SCRAP.ScrapMaterials smj ON smj.Id = srl.ScrapMaterialId
			WHERE srl.ScrapRecipeId = c.ScrapRecipeId
				AND srl.ScrapMaterialId = smj.Id
				AND smj.Name + '_max' = sm.Name + '_max'
			)
	INTO #chargesMax
	FROM SCRAP.Charges c
	JOIN SCRAP.ChargeDetails cd ON cd.ChargeId = c.Id
	JOIN SCRAP.ScrapMaterials sm ON sm.Id = cd.ScrapMaterialId
	LEFT JOIN MES.Heats h ON h.Id = c.HeatId
	LEFT JOIN SCRAP.ScrapRecipes sr ON sr.Id = c.ScrapRecipeId
	WHERE @ShiftId IS NULL AND
			(
			(c.ChargeTimestamp BETWEEN @fromUTC AND @toUTC)
			OR (@fromUTC IS NULL AND @toUTC IS NULL)
			)
			OR @ShiftId = h.ShiftId AND  h.ProductionDate = @from
	--WHERE (
	--		(
	--			c.ChargeTimestamp BETWEEN @fromUTC
	--				AND @toUTC
	--			)
	--		OR (
	--			@fromUTC IS NULL
	--			AND @toUTC IS NULL
	--			)
	--		)
	GROUP BY c.Id
		,c.ScrapRecipeId
		,sm.Name + '_max'
	ORDER BY c.Id ASC

	----Create a temporal table to get the max weight of the materials. 
	--SELECT 
	--[ChargeId_max] = c.Id, 
	--[ScrapMaterialNameMax] = CONCAT(sm.Name , '_max'),
	--[MaxWeight] = ISNULL(srl.[Weight])
	--INTO #chargesMax
	--FROM
	--SCRAP.ScrapRecipeLayers srl
	--JOIN #materials m ON m.RecipeId = srl.
	--WHERE 
	--((c.ChargeTimestamp BETWEEN @fromUTC AND @toUTC)
	--OR (@fromUTC IS NULL AND @toUTC IS NULL))
	SELECT [ChargeId] = c.Id
		,[Total] = SUM([Weight])
	INTO #totals
	FROM SCRAP.Charges c
	JOIN SCRAP.ChargeDetails cd ON cd.ChargeId = c.Id
	LEFT JOIN MES.Heats h ON h.Id = c.HeatId
	WHERE @ShiftId IS NULL AND
		(
		(c.ChargeTimestamp BETWEEN @fromUTC AND @toUTC)
		OR (@fromUTC IS NULL AND @toUTC IS NULL)
		)
		OR @ShiftId = h.ShiftId AND  h.ProductionDate = @from
	--WHERE (
	--		(
	--			c.ChargeTimestamp BETWEEN @fromUTC
	--				AND @toUTC
	--			)
	--		OR (
	--			@fromUTC IS NULL
	--			AND @toUTC IS NULL
	--			)
	--		)
	GROUP BY c.Id

	UPDATE charges
	SET [Total] = t.Total
	FROM #charges charges
	JOIN #totals t ON t.ChargeId = charges.ChargeId

	SELECT [ChargeId] = c.Id
		,[BucketTotalCost] = SUM((cd.[Weight] / 2000.0) * sm.[Price])
	INTO #bucketTotalPrice
	FROM SCRAP.Charges c
	JOIN SCRAP.ChargeDetails cd ON cd.ChargeId = c.Id
	JOIN SCRAP.ScrapMaterials sm ON sm.Id = cd.ScrapMaterialId
	LEFT JOIN MES.Heats h ON h.Id = c.HeatId
	WHERE @ShiftId IS NULL AND
		(
		(c.ChargeTimestamp BETWEEN @fromUTC AND @toUTC)
		OR (@fromUTC IS NULL AND @toUTC IS NULL)
		)
		OR @ShiftId = h.ShiftId AND  h.ProductionDate = @from
	--WHERE (
	--		(
	--			c.ChargeTimestamp BETWEEN @fromUTC
	--				AND @toUTC
	--			)
	--		OR (
	--			@fromUTC IS NULL
	--			AND @toUTC IS NULL
	--			)
	--		)
	GROUP BY c.Id

	UPDATE charges
	SET [BucketTotalcost] = btp.[BucketTotalcost]
	FROM #charges charges
	JOIN #bucketTotalPrice btp ON btp.ChargeId = charges.ChargeId

	SELECT [ChargeId] = c.Id
		,[RecipeCost] = SUM((srl.[Weight] / 2000.0) * sm.[Price])
	INTO #recipeCost
	FROM SCRAP.Charges c
	JOIN SCRAP.ScrapRecipeLayers srl ON c.ScrapRecipeId = srl.ScrapRecipeId
	JOIN SCRAP.ScrapMaterials sm ON sm.Id = srl.ScrapMaterialId
	LEFT JOIN MES.Heats h ON h.Id = c.HeatId
	WHERE @ShiftId IS NULL AND
	(
	(c.ChargeTimestamp BETWEEN @fromUTC AND @toUTC)
	OR (@fromUTC IS NULL AND @toUTC IS NULL)
	)
	OR @ShiftId = h.ShiftId AND  h.ProductionDate = @from
	--WHERE (
	--		(
	--			c.ChargeTimestamp BETWEEN @fromUTC
	--				AND @toUTC
	--			)
	--		OR (
	--			@fromUTC IS NULL
	--			AND @toUTC IS NULL
	--			)
	--		)
	GROUP BY c.Id

	UPDATE charges
	SET [RecipeCost] = rc.[RecipeCost]
	FROM #charges charges
	JOIN #recipeCost rc ON rc.ChargeId = charges.ChargeId

	--Make a query that creates a pivot to turn the materials name from #charges into columns
	-- Join to another pivot to turn the max materials name from #changesMax into columns (the same of previous pivot)
	-- So with the join, both tables will turn into one, with his respective values.
	DECLARE @query VARCHAR(MAX) = '
	SELECT * FROM (	
		SELECT ChargeId, Heat, Bucket, ChargeNumber, ScrapRecipe, ChargeTimestamp, Operator, Furnace, Crew,Shift, OrderNumber, Total, BucketTotalCost, RecipeCost, ' + @selectMaterialNameQuery + ' 
		FROM #charges p
		PIVOT (
			SUM([Weight]) FOR [ScrapMaterialName] IN (' + @materialsQuery + ')
		) AS pv 
	) AS p
	JOIN
	(
			SELECT ChargeId_max, ' + @selectMaterialNameQueryMax + ' 
			FROM #chargesMax p2
			PIVOT (
				MAX([MaxWeight]) FOR [MaterialName_max] IN (' + @materialsQueryMax + ')
			) AS pv2 
	) AS p2 ON p2.ChargeId_max = p.ChargeId
	order by ChargeTimestamp DESC
'

	--DECLARE @query VARCHAR(MAX) = '
	--	SELECT ChargeId, ' + @selectMaterialNameQuery + ' 
	--	FROM
	--	(
	--		SELECT 
	--		[ChargeId] = c.Id, 
	--		[ScrapMaterialId] = sm.Id, 
	--		[Weight] = cd.[Weight]
	--		FROM
	--		SCRAP.Charges c
	--		JOIN SCRAP.ChargeDetails cd ON cd.ChargeId = c.Id
	--		JOIN SCRAP.ScrapMaterials sm ON sm.Id = cd.ScrapMaterialId
	--		' + CASE WHEN (@fromUTC IS NOT NULL AND @toUTC IS NOT NULL) THEN
	--		'WHERE (c.ChargeTimestamp BETWEEN '''+ CONVERT(VARCHAR, @fromUTC, 120) +''' AND ''' + CONVERT(VARCHAR, @toUTC, 120) + ''')'
	--		ELSE '' END + '
	--	) as t
	--	PIVOT (
	--		SUM([Weight]) FOR [ScrapMaterialId] IN (' + @materialsQuery + ')
	--	) AS pv
	--'
	--PRINT @query
	IF (
			(
				SELECT COUNT(ChargeId)
				FROM #charges
				) > 0
			)
	BEGIN
		SELECT [Status] = 'OK'

		EXEC (@query)
	END
	ELSE
	BEGIN
		SELECT [Status] = 'NODATA'

		SELECT NULL
	END

	--SELECT *
	--FROM
	--(
	--SELECT 
	--[ChargeId] = c.Id, 
	--[ScrapMaterialId] = sm.Id, 
	--[Weight] = cd.[Weight]
	--FROM
	--SCRAP.Charges c
	--JOIN SCRAP.ChargeDetails cd ON cd.ChargeId = c.Id
	--JOIN SCRAP.ScrapMaterials sm ON sm.Id = cd.ScrapMaterialId
	--WHERE 
	--((c.ChargeTimestamp BETWEEN @fromUTC AND @toUTC)
	--OR (@fromUTC IS NULL AND @toUTC IS NULL))
	--) p
	--PIVOT
	--(
	--	SUM([Weight]) FOR [ScrapMaterialId] IN ([42], [43])
	--) AS PivotTable; 
	DROP TABLE #charges

	DROP TABLE #totals

	DROP TABLE #bucketTotalPrice

	DROP TABLE #recipeCost
END
	--END ALTER
	--start create
	GO
	[SCRAP].[GetCharges]  @from ='2020-06-17', @shiftId = 2


	GO
	/*UPDATE SQL PROCEDURE - 04/03/2021 - David*/
ALTER PROCEDURE [SCRAP].[GetChargeDetails]
	@chargeId INT = NULL
AS
BEGIN 
	SELECT
		chargeDet.*,
		ScrapMaterialName = material.Name
	FROM SCRAP.ChargeDetails chargeDet
		JOIN SCRAP.ScrapMaterials material ON material.Id = chargeDet.ScrapMaterialId
	WHERE
		ChargeId = @chargeId
	ORDER BY
		LayerOrder ASC

		select 
			c.HeatNumber
			,c.Bucket
			,c.ChargeNumber
			,[ScrapRecipe] = sr.[Name]
			,ChargeTimestamp
			,Operator
			,Furnace
			,Crew
			,OrderNumber
			,TotWeight
		from scrap.Charges c
		LEFT JOIN SCRAP.ScrapRecipes sr ON sr.Id = c.ScrapRecipeId
		where c.Id = @chargeId
END