
-------------
-- ROLES
-------------
	USE [IHBoxSystem]
	GO
	CREATE ROLE forecastrunschedule_read AUTHORIZATION dbo 
	GO  

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'forecastrunschedule_read', 'admin'
	GO

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'forecastrunschedule_read', 'SDI/australtek'
	GO


	CREATE ROLE forecastrunschedule_readWrite AUTHORIZATION dbo 
	GO  

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'forecastrunschedule_readWrite', 'admin'
	GO

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'forecastrunschedule_readWrite', 'SDI/australtek'
	GO


-------------------
-- Menu and Route
-------------------
	declare @parent int, @result_routeId INT
	select @parent=id from [FrontEnd].[Menu] where [Name] = 'MES' and Parent is null

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/forecast-run-schedule(/)'
			   , 'forecast-run-schedule'
			   , 'custom-screens/forecast-run-schedule/'
			   , 'forecastrunschedule_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Forecast Run Schedule'
			   ,@result_routeId 
			   ,15
			   ,1
			   ,1
			   ,'forecastrunschedule_read'
			   ,NULL
			 )
	GO


-------------------
-- SP Permissions
-------------------
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [forecastrunschedule_read] -- The role
,@name = N'securedsp-MES.GetRunSchedule' -- the procedure
,@value = N'' 
GO





-------------------
--	Profiles
-------------------
	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('forecast_run_schedule','Forecast Run Schedule')

	SET @ModuleId = @@IDENTITY

	INSERT INTO [SECURITY].[ModuleActions]([ModuleId],[Code],[Name],[Role])
	VALUES(@ModuleId,'readWrite','Read/Write','forecastrunschedule_readWrite')


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'read','Read','forecastrunschedule_read')


	GO

/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------------------------------------*/



--------------
--- Tables ---
--------------

CREATE TABLE [MES].[RunSchedule](
	[UploadTimestamp] [datetime] NULL,
	[StringDate] [varchar](200) NULL,
	[DATE] [date],
	[SHIFT] [char](1),
	[CREW] [char](1) NULL,
	[HEATS] [int] NULL,
	[C1MOLD] [varchar](200) NULL,
	[C1MILL] [varchar](200) NULL,
	[C1SIZE] [varchar](200) NULL,
	[C1HEATS] [int] NULL,
	[C2MOLD] [varchar](200) NULL,
	[C2MILL] [varchar](200) NULL,
	[C2SIZE] [varchar](200) NULL,
	[C2HEATS] [int] NULL,
	[C1Size1] [varchar](200) NULL,
	[C1Size2] [varchar](200) NULL,
	[C1Size3] [varchar](200) NULL,
	[C2Size1] [varchar](200) NULL,
	[C2Size2] [varchar](200) NULL,
	[C2Size3] [varchar](200) NULL,
	
	constraint PK_Date_Shift Primary Key ([Date], [Shift])
)
GO


-------------------
---- Functions ----
-------------------
USE [MES]
GO
/****** Object:  UserDefinedFunction [MES].[GetWeekDays]    Script Date: 6/2/2023 14:27:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [MES].[GetWeekDays]
(
	@Start date,
	@End date
)
RETURNS @dataT TABLE
(
	[Date] date, 
	[Day] varchar(10)
)
AS
BEGIN

	;with cte as (
		select @start as [Date]
		union ALL
		select CAST(DATEADD(day,1,[Date]) as date)
		from cte
		where [Date] < @end
	)
	insert into @dataT([Date],[Day])
	select [Date],DATENAME(WEEKDAY,[Date]) from cte order by [Date]


	return

END

 

GO

--------------------------
--- Stored Procedures ---
--------------------------
USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetRunSchedule]    Script Date: 6/2/2023 14:12:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[GetRunSchedule]
AS
BEGIN
		
	------------------
	--- Variables ----
	------------------
	declare @start date 
	declare	@end   date 


	-----------------------
	-- Begin Query
	-----------------------
	declare @currentDatetime datetime = IH.ToLocal(GETUTCDATE(),null) -- DATEADD(DAY,0,GETDATE()) -- today's local date

	declare @currentWeek 	datetime = (SELECT DATEADD(dd, DATEPART(DW,@currentDatetime)*-1+1, @currentDatetime)) -- current week's Sunday
	declare @previousWeek	datetime = dateadd(day,-7,@currentWeek)
	declare @nextWeek		datetime = dateadd(day,+7,@currentWeek) 
	declare @nextnextWeek		datetime = dateadd(day,+7,@nextWeek) 

	
	declare @startPreviousWeek	date = cast(@previousWeek as date)
	declare @endPreviousWeek	date = cast(@currentWeek  as date)
	declare @startCurrentWeek	date = cast(@currentWeek as date)
	declare @endCurrentWeek		date = cast(@nextWeek  as date)
	declare @startNextWeek		date = cast(@nextWeek as date)
	declare @endNextWeek		date = cast(@nextnextWeek  as date)

	
	
	declare @t table ([Week] varchar(10), [Date] date, [Day] varchar(10), DayTons float, NightTons float,[Percentage] varchar(4), Size1 varchar(20),Size2 varchar(20),Size3 varchar(20))
	------------------------
	-- Get Data Per Week ---
	------------------------
	insert into @t
	select 
		'Previous'
		,g.[Date]
		,g.[Day]
	, (select top 1 z.C1HEATS from MES.RunSchedule z where z.[Date] = g.[Date])
	, (select top 1 z.C2HEATS from MES.RunSchedule z where z.[Date] = g.[Date])
	, '98%'
	, (select top 1 z.C1Size1 from MES.RunSchedule z where z.[Date] = g.[Date])
	, 'BB1'--(select top 1 z.C1Size2 from MES.RunSchedule z where z.[Date] = g.[Date])
	, 'BB2'--(select top 1 z.C1Size3 from MES.RunSchedule z where z.[Date] = g.[Date])
	from [MES].[GetWeekDays](@startPreviousWeek,@endPreviousWeek) g



	insert into @t
	select 
		'Current'
		,g.[Date]
		,g.[Day]
	, (select top 1 z.C1HEATS from MES.RunSchedule z where z.[Date] = g.[Date])
	, (select top 1 z.C2HEATS from MES.RunSchedule z where z.[Date] = g.[Date])
	, '100%'
	, (select top 1 z.C1Size1 from MES.RunSchedule z where z.[Date] = g.[Date])
	, (select top 1 z.C1Size2 from MES.RunSchedule z where z.[Date] = g.[Date])
	, (select top 1 z.C1Size3 from MES.RunSchedule z where z.[Date] = g.[Date])
	from [MES].[GetWeekDays](@startCurrentWeek,@endCurrentWeek) g


	insert into @t
	select 
		'Next'
		,g.[Date]
		,g.[Day]
	, null
	, null
	, null
	, 'Down Day'--(select top 1 z.C1Size1 from MES.RunSchedule z where z.[Date] = g.[Date])
	, 'Down Day'--(select top 1 z.C1Size2 from MES.RunSchedule z where z.[Date] = g.[Date])
	, 'Down Day'--(select top 1 z.C1Size3 from MES.RunSchedule z where z.[Date] = g.[Date])
	from [MES].[GetWeekDays](@startNextWeek,@endNextWeek) g
	   	
	select * from @t 

	select 
	[CurrentMonth] = 'February'
	,[CurrentMonthTons] = '40,600.00'
	,[PreviousWeekDate] = '10/2/2'
	,[PreviousWeekTons] = '35,800.00'
	,[PreviousWeekTonsProjected] = '37,990.00'
	,[CurrentWeekDate] = '20/2/2'
	,[CurrentWeekTons] = '55,750.00'
	,[CurrentWeekTonsProjected] = '49,850.00'
	,[NextWeekTonsProjected] = '42,155.00'


END

GO

---------------------
--- Other Queries ---
---------------------

select 
	*
	,case when DATEDIFF(MONTH,[DATETIME], CAST( [Date] + CAST(YEAR(GETUTCDATE()) as varchar) as datetime) ) < 3
		then 
			CAST( [Date] + CAST(YEAR(GETUTCDATE()) as varchar) as datetime) 
		when DATEDIFF(MONTH,[DATETIME], CAST( [Date] + CAST(YEAR(GETUTCDATE()) + 1   as varchar) as datetime) ) < 3 
		    then
			CAST( [Date] + CAST(YEAR(GETUTCDATE()) + 1 as varchar) as datetime) 
			else null end
	, DATEDIFF(MONTH,[DATETIME], CAST( [Date] + CAST(YEAR(GETUTCDATE()) as varchar) as datetime) )
	, DATEDIFF(MONTH,[DATETIME], CAST( [Date] + CAST(YEAR(GETUTCDATE()) + 1   as varchar) as datetime) ) 
from [MES].[RunSchedule]










--------------------------------------------------------------------------------------------------------------------------------
------- Stored Procedures at 10/02/2023 ----------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------

USE [MES]
GO
/****** Object:  UserDefinedFunction [MES].[GetRunScheduleWeekDays]    Script Date: 10/2/2023 00:44:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [MES].[GetRunScheduleWeekDays]()
RETURNS @dataT TABLE
(
	[Week] varchar(20),
	[Date] date, 
	[WeekDay] varchar(10)
)
AS
BEGIN

	declare @currentDatetime datetime = IH.ToLocal(GETUTCDATE(),null) -- DATEADD(DAY,0,GETDATE()) -- today's local date

	declare @currentWeek 	datetime = (SELECT DATEADD(dd, DATEPART(DW,@currentDatetime)*-1+1, @currentDatetime)) -- current week's Sunday
	declare @previousWeek	datetime = dateadd(day,-7,@currentWeek)
	declare @nextWeek		datetime = dateadd(day,+7,@currentWeek) 
	declare @nextnextWeek		datetime = dateadd(day,+7,@nextWeek) 

	
	declare @startPreviousWeek	date = cast(@previousWeek as date)
	declare @endPreviousWeek	date = cast(@currentWeek  as date)
	declare @startCurrentWeek	date = cast(@currentWeek as date)
	declare @endCurrentWeek		date = cast(@nextWeek  as date)
	declare @startNextWeek		date = cast(@nextWeek as date)
	declare @endNextWeek		date = cast(@nextnextWeek  as date)

	--declare @t table ([Week] varchar(10), [Date] date, [Day] varchar(10), DayTons float, NightTons float,[Percentage] varchar(4), Size1 varchar(20),Size2 varchar(20),Size3 varchar(20))
	

	declare @start date	
	declare @end date

	---------------------
	--- Previous Week ---
	---------------------
	set @start = @startPreviousWeek
	set @end = @endPreviousWeek

	;with cte as (
		select @start as [Date]
		union ALL
		select CAST(DATEADD(day,1,[Date]) as date)
		from cte
		where [Date] < dateadd(day,-1,@end)
	)
	insert into @dataT([Week],[Date],[WeekDay])
	select 'Previous',[Date],DATENAME(WEEKDAY,[Date]) from cte order by [Date]

	
	---------------------
	--- Current Week ---
	---------------------
	set @start = @startCurrentWeek 
	set @end = @endCurrentWeek

	;with cte as (
		select @start as [Date]
		union ALL
		select CAST(DATEADD(day,1,[Date]) as date)
		from cte
		where [Date] < dateadd(day,-1,@end)
	)
	insert into @dataT([Week],[Date],[WeekDay])
	select 'Current',[Date],DATENAME(WEEKDAY,[Date]) from cte order by [Date]

	---------------------
	--- Next Week ---
	---------------------
	set @start = @startNextWeek 
	set @end = @endNextWeek

	;with cte as (
		select @start as [Date]
		union ALL
		select CAST(DATEADD(day,1,[Date]) as date)
		from cte
		where [Date] < dateadd(day,-1,@end)
	)
	insert into @dataT([Week],[Date],[WeekDay])
	select 'Next',[Date],DATENAME(WEEKDAY,[Date]) from cte order by [Date]


	return

END

 

GO



----------------------------
--- Calculate the Abbrevs ---
----------------------------




USE [MES]
GO
/****** Object:  UserDefinedFunction [MES].[GetRunScheduleWeekDays]    Script Date: 10/2/2023 00:39:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [MES].[GetForecastRunScheduleAbbrevs]
(
	@Start date,
	@End date
)
RETURNS @dataT TABLE
(
	ProductionDate date,
	ShiftId char(1),
	Abbrevs varchar(max)
)
AS
BEGIN

	
	
	declare @target table (
		Id int identity(1,1),
		ProductionDate date,
		ShiftId char(1),
		Abbrevs varchar(max)
	)

	declare @source table (
		ProductionDate date,
		ShiftId char(1),
		MoldTypeId int
	)

	declare @AbbrevsTable table (
	
		Abbrev varchar(10)
	)


	;with source as (
	select 
	sp.*
	,s.MoldTypeId

	from MES.ShiftPerformance sp
	left join CAS.Processes p on p.ProductionDate = sp.ProductionDate and (case when p.ShiftId = 1 then 'D' else 'N'  end)= sp.ShiftId
	left join CAS.StrandProcesses s on s.ProcessId = p.Id

	)
	insert into @source
	select ProductionDate,ShiftId,MoldTypeId from source 



	INSERT INTO @Target (ProductionDate,ShiftId)
	SELECT ProductionDate,ShiftId
	FROM @Source
	GROUP BY ProductionDate,ShiftId



	declare @MaxId int = (select MAX(Id) from @target)

	DECLARE @id int
	DECLARE @Abbrevs varchar(max) = ''
	SET @id = (SELECT MIN(id) FROM @Target)

	WHILE @id <= @MaxId
	BEGIN
	
		;with cte as (
			select 	
				distinct(mt.Abbrev) 
			from @Target t
			left join @source s on s.ProductionDate = t.ProductionDate and s.ShiftId = t.ShiftId
			left join CAS.catMoldTypes mt on mt.MoldTypeId = s.MoldTypeId
			where t.Id = @id
		)
		insert into @AbbrevsTable
		select * from cte

		select 
		@Abbrevs = @Abbrevs + Abbrev + ','
		from @AbbrevsTable

		SET @Abbrevs = left(@Abbrevs,LEN(@Abbrevs ) -1)


		update t 
			set Abbrevs = @Abbrevs
		from @target t
		where t.Id = @Id

		delete from @AbbrevsTable


	  SET @id = @id + 1
	END


	
	
	
	
	insert into @dataT
	select ProductionDate,ShiftId,Abbrevs from @target

	return

END

 

 GO


 USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetRunSchedule]    Script Date: 9/2/2023 10:07:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[GetRunSchedule]
AS
BEGIN		
	-----------------------
	-- Begin Query
	-----------------------

	--DayTons float, NightTons float,[Percentage] varchar(4), Size1 varchar(20),Size2 varchar(20),Size3 varchar(20))

	--select 
	--[Week]
	--,[Date]
	--,[Day] = [WeekDay]
	--, DayTons = (select s.SolidTons from MES.ShiftPerformance s where s.ProductionDate = d.[Date] and s.ShiftId = 'D')
	--, NightTons = (select s.SolidTons from MES.ShiftPerformance s where s.ProductionDate = d.[Date] and s.ShiftId = 'N')
	--, [Percentage] =  '100%'
	--, DayProjectedTons = (select s.ProjectedTons from MES.ShiftPerformance s where s.ProductionDate = d.[Date] and s.ShiftId = 'D')
	--, NightProjectedTons = (select s.ProjectedTons from MES.ShiftPerformance s where s.ProductionDate = d.[Date] and s.ShiftId = 'N')
	--, [Size1] = '10'
	--, [Size2] = '10'
	--, [Size3] = '10'
	--from MES.GetRunScheduleWeekDays() d
	--order by [Date]

	select 
		s.[Week]
		,[Date] = convert(varchar,s.[Date],1)
		,[Day] = s.[WeekDay]
		,p.*
		,ProjectedTons = r.HEATS * 160
		,a.Abbrevs
	from MES.GetRunScheduleWeekDays() s
	left join MES.ShiftPerformance p on p.ProductionDate = s.[Date]
	left join MES.RunSchedule r on r.[DATE] = p.[ProductionDate] and r.[SHIFT] = p.ShiftId
	left join [MES].[GetForecastRunScheduleAbbrevs]() a on a.ProductionDate  = p.ProductionDate and a.ShiftId = p.ShiftId
	order by s.[Date]
	



	select 
	[CurrentMonth] = DATENAME(MONTH, GETDATE() )
	,[CurrentMonthTons] = (select SUM(SolidTons) from MES.ShiftPerformance where  DATENAME(MONTH, GETDATE() ) = DATENAME(MONTH, [ProductionDate] ))
	,[PreviousWeekDate] = (select top 1 convert(varchar,[Date],1) from MES.GetRunScheduleWeekDays() where [Week] = 'Previous' order by [Date] asc)
	,[PreviousWeekTons] = (select SUM(SolidTons) from MES.ShiftPerformance where  [ProductionDate] in  (select [Date] from MES.GetRunScheduleWeekDays() where [Week] = 'Previous') )
	,[PreviousWeekTonsProjected] = '37,990.00'
	,[CurrentWeekDate] = '20/2/2'
	,[CurrentWeekTons] = '55,750.00'
	,[CurrentWeekTonsProjected] = '49,850.00'
	,[NextWeekTonsProjected] = '42,155.00'


	

END


go



USE [MES]
GO
/****** Object:  StoredProcedure [MES].[TrackShiftPerformance]    Script Date: 10/2/2023 00:53:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [MES].[TrackShiftPerformance]
@Start datetime = null,
@End datetime = null

AS
BEGIN

	-------------------------
	--- Common Variables ----
	-------------------------
	
	declare @procName VARCHAR(300)
	declare @logMsg VARCHAR(MAX)
	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	DECLARE @buf varchar(200) = ''
	SET NOCOUNT ON	
	-----------------------------
	--- Start/End Parameters ----
	-----------------------------
	declare @StartOffset bigint
	declare @EndOffset bigint

	IF @End is null
	SET @End = dateadd(MINUTE,-5,IH.ToLocal(getutcdate(),null))

	IF @Start is null
	SET @Start = Dateadd(MINUTE,-60,@End)

	SET @StartOffset = IH.Date2MS(IH.ToUTC(@Start,null))
	SET @EndOffset = IH.Date2MS(IH.ToUTC(@End,null))

	-------------
	--- 
	------------
	declare @TonsPerHeat int = 160

	declare @ShiftPerformance table(ProductionDate date, ShiftId char(1), CrewId char(1), SolidTons decimal(10,2))
	declare @Processes table(ProductionDate date, ShiftId char(1),ProcessId int)
	declare @Shifts table(ProductionDate date, ShiftId char(1))

	--declare @Processes table (ProcessId int, ProductionDate date, ShiftId char(1), CrewId char(1))
	--------------------
	--- Begin Query ----
	--------------------


	----------------------
	--- Hystoric Data ----
	----------------------

	-- 1. Get produced products where CutCumpolete is between @Start and @end and sent to L3.
	-- 2. Get distinct ProcessId's involved on these new products.
	-- 3. Using the ProcessId's, We get the ProductionDate,ShiftId  grouped.
	-- 4. With the ProductionDate,ShiftId and CrewId we get all the processId's that correspond to that Shift.
	-- 5. With the processId's We get the Product Weight where SentToL3 = 1.
	-- 6. We group the weights per "Shift" that means (ProductionDate-ShiftId)

	---------------------
	--- Steps: 1,2,3 ----
	---------------------
	;with cte as(
		select 
			ProcessId = prod.ProcessId
	from cas.Processes z
	left join cas.products prod on prod.ProcessId = z.Id and prod.SentToL3 = 1	
	where 
		prod.[CutComplete] between IH.Ms2Date(@StartOffset) and IH.Ms2Date(@EndOffset)
		group by prod.ProcessId) 	
	--insert into @Processes
	insert into @Shifts
	select 
		p.ProductionDate
		,p.ShiftId
		--,p.Id
	from CAS.Processes  p 
	where Id in (select ProcessId  from cte)
	group by p.ProductionDate,p.ShiftId

	--insert into @Shifts
	--select 
	--	p.ProductionDate
	--	,p.ShiftId
	--from @Processes p
	--group by p.ProductionDate,p.ShiftId
	

	---------------------
	--- Steps: 4,5,6 ----
	---------------------
	;with cte as (
	select 
		p.Id
		,s.*
	from CAS.Processes p
	right join @Shifts s on s.ProductionDate = p.ProductionDate and s.ShiftId = p.ShiftId
	)
	insert into @ShiftPerformance(ProductionDate,ShiftId,CrewId,SolidTons)
	select 
		c.ProductionDate
		,case when c.ShiftId = 1 then 'D' else 'N' end
		,(select top 1 y.CrewId from CAS.Processes y where y.ProductionDate = c.ProductionDate and y.ShiftId = c.ShiftId)
		,cast(sum(z.[Weight]) as decimal(10,2))
	from CAS.Products z 
	right join cte c on c.Id = z.ProcessId	
	where z.SentToL3 = 1
	group by c.ProductionDate,c.ShiftId
	order by c.ProductionDate


	--select * from @ShiftPerformance order by [ProductionDate],[ShiftId]
	----------------------
	--- Schedule Data ----
	----------------------
	-- 1. Get Current ProductionDate-Shift
	-- 2. Insert forecast data into @ShiftPerformance table
	declare @CurrentProductionDate	date	= '2023-02-08'
	declare @CurrentShiftId			char(1) = 'D'
	
	/*--------------------------------------------------------------------------*/
	/*IMPORTANT: Use this code to get the current production date and shiftId*/
	/*--------------------------------------------------------------------------*/

	--select 
	-- @CurrentProductionDate= sh.ProductionDate
	--,@CurrentShiftId = case when sh.Id = 1 then 'D' else 'N' end 
	--from 
	--SYSTEM.GetShiftDataForDatetime(GETUTCDATE()) sh 

	insert into @ShiftPerformance(ProductionDate,ShiftId,CrewId,SolidTons)
	select 
		[DATE]
		,[SHIFT]
		, CREW
		, cast(HEATS*@TonsPerHeat as decimal(10,2)) 
	from MES.RunSchedule 
	where [DATE] > @CurrentProductionDate or 
		([DATE] = @CurrentProductionDate and  [SHIFT] = 'N' and @CurrentShiftId = 'D')
		
	select * from @ShiftPerformance order by ProductionDate,ShiftId




	BEGIN TRY
		
		MERGE MES.ShiftPerformance AS TARGET
		USING @ShiftPerformance AS SOURCE 
		ON  TARGET.[ProductionDate] = SOURCE.[ProductionDate] and TARGET.[ShiftId] = SOURCE.[ShiftId]
		WHEN MATCHED 
			THEN UPDATE SET 
				 TARGET.[SolidTons]		= SOURCE.[SolidTons]		
				 ,TARGET.[CrewId] = SOURCE.[CrewId]
		
			WHEN NOT MATCHED BY TARGET THEN 
			INSERT ([ProductionDate],[ShiftId],[CrewId],[SolidTons]) 
			VALUES( 
				SOURCE.[ProductionDate] 
				,SOURCE.[ShiftId]
				,SOURCE.[CrewId]		
				,SOURCE.[SolidTons]
			);		
			
			declare @rc int =@@ROWCOUNT
			if @rc>0
			begin
				set @logMsg = cast(@rc as varchar) + ' rows affected '  + @buf
				EXEC SYSTEM.LogMsg @message = @logMsg
				,@procedure = @procName
				,@typeCode = 'DEBUG'
				,@logError = 0
			end
		END TRY
		BEGIN CATCH
			SELECT 	@logMsg = ERROR_MESSAGE()

			SET @logMsg = 'Failed ' + @logMsg
				RAISERROR( @logMsg ,0,1) WITH NOWAIT

			EXEC SYSTEM.LogMsg @message = @logMsg
				,@procedure = @procName
				,@typeCode = 'ERROR'
				,@logError = 1
		END CATCH

end


go



go


USE [MES]
GO
/****** Object:  StoredProcedure [MES].[GetRunSchedule]    Script Date: 14/2/2023 23:42:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MES].[GetRunSchedule]
AS
BEGIN		
	-------------------------
	--- Common Variables ----
	-------------------------
	
	declare @procName VARCHAR(300)
	declare @logMsg VARCHAR(MAX)
	SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	DECLARE @buf varchar(200) = ''
	SET NOCOUNT ON	
	
	------------------
	--- Variables ----
	------------------
	declare @PrevWeekStartDate date  = (SELECT TOP 1 CONVERT(VARCHAR,[Date],1) FROM MES.GetRunScheduleWeekDays() WHERE [Week] = 'Previous' ORDER BY [Date] ASC)
	declare @PrevWeekEndDate date  = (SELECT TOP 1 CONVERT(VARCHAR,[Date],1) FROM MES.GetRunScheduleWeekDays() WHERE [Week] = 'Previous' ORDER BY [Date] ASC)
	declare @CurrWeekStartDate date  = (SELECT TOP 1 CONVERT(VARCHAR,[Date],1) FROM MES.GetRunScheduleWeekDays() WHERE [Week] = 'Current' ORDER BY [Date] ASC)
	declare @CurrWeekEndDate date  = (SELECT TOP 1 CONVERT(VARCHAR,[Date],1) FROM MES.GetRunScheduleWeekDays() WHERE [Week] = 'Current' ORDER BY [Date] ASC)
	declare @NextWeekStartDate date  = (SELECT TOP 1 CONVERT(VARCHAR,[Date],1) FROM MES.GetRunScheduleWeekDays() WHERE [Week] = 'Next' ORDER BY [Date] ASC)
	declare @NextWeekEndDate date  = (SELECT TOP 1 CONVERT(VARCHAR,[Date],1) FROM MES.GetRunScheduleWeekDays() WHERE [Week] = 'Next' ORDER BY [Date] ASC)
	
	declare @CurrentProductionDate	date	
	declare @CurrentShiftId			char(1) 

	-----------------------
	-- Begin Query
	-----------------------	
	select 
		 @CurrentProductionDate= sh.ProductionDate
		,@CurrentShiftId = case when sh.Id = 1 then 'D' else 'N' end 
	from SYSTEM.GetShiftDataForDatetime(GETUTCDATE()) sh 


	SELECT 
		s.[Week]
		,[Date] = CONVERT(VARCHAR,s.[Date],1)
		,[Day] = s.[WeekDay]
		,p.*
		,isPastData = case when p.ProductionDate < @CurrentProductionDate or (p.ProductionDate = @CurrentProductionDate and @CurrentShiftId = 'N'  ) then 1 else 0 end 
	into #temp
	FROM MES.GetRunScheduleWeekDays() s
	LEFT JOIN MES.ShiftPerformance p ON p.ProductionDate = s.[Date]
	ORDER BY s.[Date]

	select * from #temp


	SELECT 
		[CurrentMonth] = DATENAME(MONTH, GETDATE() )	
		,[PreviousWeekDate] = convert(varchar,@PrevWeekStartDate,1)
		,[CurrentWeekDate] =  convert(varchar,@CurrWeekStartDate,1)
		,[CurrentMonthTons] = (select  sum(isnull(ActualTonsCaster1,0) + isnull(ActualTonsCaster2,0)) from #temp)
		,[PreviousWeekTons] = (select  sum(isnull(ActualTonsCaster1,0) + isnull(ActualTonsCaster2,0)) from #temp where [Week] = 'Previous') 
		,[CurrentWeekTons] = (select  sum(isnull(ActualTonsCaster1,0) + isnull(ActualTonsCaster2,0)) from #temp where [Week] = 'Current')

	

END

go


















----------------------------------------------------------------
--------------------- 2023-02-26 -------------------------------
----------------------------------------------------------------


 ----------------------------------------------
 --- [MES].[GetForecastRunScheduleAbbrevs] 
 ----------------------------------------------
 -- Add extra space after comma 
 -- This line is repeated twice. So, it needs to be changed on both places.

select 
	@Abbrevs = @Abbrevs + Abbrev + ', '
	from @AbbrevsTable