USE [SE0085CT]
GO

/****** Object:  Table [dbo].[OrdSections]    Script Date: 07-24-2015 19:14:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[OrdSections](
	[OrdSectsId] [int] IDENTITY(1,1) NOT NULL,
	[OrderLineId] [int] NOT NULL,
	[OrdSectsNum] [int] NOT NULL,
	[OrdSectShipSpt] [int] NULL,
	[OrdSectsProdNum] [char](12) NULL,
	[OrdSectsCryptRt] [varchar](15) NULL,
	[OrdSectsRelMfg] [datetime2](2) NULL,
	[OrdSectsCurrWorkCtr] [varchar](10) NULL,
 CONSTRAINT [PK__OrdSecti__106AE3DE1CF15040] PRIMARY KEY CLUSTERED 
(
	[OrdSectsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[OrdSections]  WITH CHECK ADD  CONSTRAINT [FK_OrdSections_OrderLines] FOREIGN KEY([OrderLineId])
REFERENCES [dbo].[OrderLines] ([OrderLineId])
GO

ALTER TABLE [dbo].[OrdSections] CHECK CONSTRAINT [FK_OrdSections_OrderLines]
GO


------------------------------------
USE [SE0085CT]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertUpdateRunRate]    Script Date: 07-24-2015 18:58:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ===========================================================
-- Author:		Terry Taylor - Schneider Electric
-- Create date: 2015-07-22
-- Description:	Insert or Update the run rate for a given date
-- range.  The begin date must be earlier than the end date 
-- and the maximum range is 90 days.
-- ===========================================================
ALTER PROCEDURE [dbo].[sp_InsertUpdateRunRate] 
	-- Add the parameters for the stored procedure here
	@pAssemblyLineAbbrv varchar(4) = NULL,
    @pMfgGate varchar(15) = NULL,
	@pSchedBeginDate datetime2(2) = '1901-01-01', 
	@pSchedEndDate datetime2(2) = '1901-01-01', 
	@pSchedRunRate int = 0,
	@pPerformLevel1 int = 0,
    @pPerformLevel2 int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @SEID int
	DECLARE @LoopDt datetime2(2)

	SET @LoopDt = @pSchedBeginDate

	IF ((@pSchedBeginDate <= @pSchedEndDate) AND (DATEDIFF(day, @pSchedBeginDate, @pSchedEndDate) <= 90))
	BEGIN
		WHILE (@LoopDt <= @pSchedEndDate)
		BEGIN
			SET @SEID =  ISNULL((SELECT [SchedExecId]
			  FROM [SE0085CT].[dbo].[ScheduleExec]
			  WHERE [SchedDate] = @LoopDt),0)

			  IF @SEID = 0
				  BEGIN
				  INSERT INTO [dbo].[ScheduleExec]
						   ([SchedDate]
						   ,[AssemLineAbbrv]
						   ,[MfgGate]
						   ,[SchedRunRate]
						   ,[ScheduledToday]
						   ,[SchedPastDue]
						   ,[SchedCompleted]
						   ,[TotalCompleted]
						   ,[PerformLevel1]
						   ,[PerformLevel2])
					 VALUES
						   (@LoopDt
						   ,@pAssemblyLineAbbrv
						   ,@pMfgGate
						   ,@pSchedRunRate
						   ,0
						   ,0
						   ,0
						   ,0
						   ,@pPerformLevel1
						   ,@pPerformLevel2)

					SELECT @@ROWCOUNT AS ROW_COUNT
				  END
			  ELSE 
				  BEGIN
					 UPDATE [dbo].[ScheduleExec]
					   SET [SchedRunRate] = @pSchedRunRate
					 WHERE [SchedDate] = @LoopDt

					 SELECT @@ROWCOUNT AS ROW_COUNT
				  END
			  SET @LoopDt = DATEADD(day, (SELECT dc.[IncValue] FROM [SE0085CT].[dbo].[CalWeekDay] dc WHERE dc.[DayOfWeekVal] = DATEPART(WEEKDAY, @LoopDt)), @LoopDt)
			  SET @LoopDt = DATEADD(day, (ISNULL((SELECT hl.[HolidayAdjDays] FROM [SE0085CT].[dbo].[HolidayList] hl WHERE hl.[HolidayDate] = @LoopDt),0)), @LoopDt)
		END
	END
  END

  ---------------------------------------
  USE [SE0085CT]
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectUserRole]    Script Date: 07-24-2015 19:15:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_SelectUserRole](
@User varchar(50)
)
AS
BEGIN
	SELECT [Role] FROM [SE0085MSM].dbo.[User] WHERE Windows_Logon=@User
END
------------------------
USE [SE0085CT]
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectScheduleRecord]    Script Date: 07-24-2015 19:15:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_SelectScheduleRecord]
(
	@pAssemblyLineAbbrv varchar(4) = NULL,
	@pMfgGate varchar(15) = NULL,
	@pReport varchar(50) = NULL,
	@pRunDate datetime
)
AS
BEGIN
DECLARE @RunDate datetime
DECLARE @EndDate datetime
DECLARE @WorkCenter varchar(10)

SET @RunDate = CONVERT(date, @pRunDate, 101)
SET @EndDate = DATEADD(day, 1, @RunDate)
SET @WorkCenter = 'STDCONN'

--Past Due
if @pReport = 'Past Due'
BEGIN
	SELECT TOP 100 fo.OrderFO + '-' + ol.OrderLineNum + '-' + ol.OrderLineSL + '-' + ol.OrderLineSS + ':' + CONVERT(char(3), se.OrdSectShipSpt) + ':' + CONVERT(char(3), se.OrdSectsNum) AS Q2C
	, fo.OrderJobName AS JobName
	, oe.OrderLineCurTargetDt AS TargetDate
	, ol.OrderLineShipDt AS ShipDate
	, oe.OrderLineActAssyStDt AS Released
	, rt.RoutingQueuedDt AS Queued
	, rt.RoutingActStartDt AS ActualStart
	, oe.OrderLineSER AS SER
	, (rt.RoutingRunTimeMin ) As RunTime
	, oe.OrderLineElecClpx AS ElecClpx
	, oe.OrderLineMechClpx AS MechClpx
	, rt.RoutingSchStartDt AS ScheduledStart
	, rt.RoutingSchFinishDt AS ScheduledFinish
	, rt.RoutingActFinishDt AS ActualFinish
	FROM dbo.SEOrders AS fo INNER JOIN
		dbo.OrderLines AS ol ON fo.OrderId = ol.OrderId INNER JOIN
		dbo.AssemblyLines AS al ON al.AssemLineId = ol.AssemLineId INNER JOIN
		dbo.OrderLinesExt AS oe ON ol.OrderLineId = oe.OrderLineId INNER JOIN
		dbo.OrdSections AS se ON ol.OrderLineId = se.OrderLineId INNER JOIN
		dbo.SectionsRouting AS rt ON rt.OrdSectsId = se.OrdSectsId
	  WHERE al.AssemLineAbbrv = @pAssemblyLineAbbrv
	  AND rt.RoutingSchStartDt < @RunDate 
	  AND rt.RoutingActFinishDt = '1901-01-01'
	  AND rt.RoutingWorkCenter = @WorkCenter
	  AND ol.OrderLineShipped = 0 
	  AND ol.OrderLineShipDt < '2020-01-01'
	  AND oe.OrderLineCurTargetDt > '1901-01-01'
	ORDER BY rt.RoutingSchStartDt, fo.OrderFO, ol.OrderLineNum, se.OrdSectsNum
END
--Scheduled to start today
IF @pReport = 'Scheduled to start today'
BEGIN
	SELECT TOP 100 fo.OrderFO + '-' + ol.OrderLineNum + '-' + ol.OrderLineSL + '-' + ol.OrderLineSS + ':' + CONVERT(char(3), se.OrdSectShipSpt) + ':' + CONVERT(char(3), se.OrdSectsNum) AS Q2C
	, fo.OrderJobName AS JobName
	, oe.OrderLineCurTargetDt AS TargetDate
	, ol.OrderLineShipDt AS ShipDate
	, oe.OrderLineActAssyStDt AS Released
	, rt.RoutingQueuedDt AS Queued
	, rt.RoutingActStartDt AS ActualStart
	, oe.OrderLineSER AS SER
	, (rt.RoutingRunTimeMin ) As RunTime
	, oe.OrderLineElecClpx AS ElecClpx
	, oe.OrderLineMechClpx AS MechClpx
	, rt.RoutingSchStartDt AS ScheduledStart
	, rt.RoutingSchFinishDt AS ScheduledFinish
	, rt.RoutingActFinishDt AS ActualFinish
	FROM dbo.SEOrders AS fo INNER JOIN
		dbo.OrderLines AS ol ON fo.OrderId = ol.OrderId INNER JOIN
		dbo.AssemblyLines AS al ON al.AssemLineId = ol.AssemLineId INNER JOIN
		dbo.OrderLinesExt AS oe ON ol.OrderLineId = oe.OrderLineId INNER JOIN
		dbo.OrdSections AS se ON ol.OrderLineId = se.OrderLineId INNER JOIN
		dbo.SectionsRouting AS rt ON rt.OrdSectsId = se.OrdSectsId
	WHERE al.AssemLineAbbrv = @pAssemblyLineAbbrv
	AND rt.RoutingSchStartDt >= @RunDate 
	AND rt.RoutingSchStartDt < @EndDate
	AND rt.RoutingActFinishDt = '1901-01-01'
	AND rt.RoutingWorkCenter = @WorkCenter 
	AND ol.OrderLineShipped = 0 
	AND ol.OrderLineShipDt < '2020-01-01'
	AND oe.OrderLineCurTargetDt > '1901-01-01'
	ORDER BY rt.RoutingSchStartDt, fo.OrderFO, ol.OrderLineNum, se.OrdSectsNum
END
  
  --Scheduled to finish today
IF @pReport = 'Scheduled to finish today'
BEGIN
	SELECT TOP 100 fo.OrderFO + '-' + ol.OrderLineNum + '-' + ol.OrderLineSL + '-' + ol.OrderLineSS + ':' + CONVERT(char(3), se.OrdSectShipSpt) + ':' + CONVERT(char(3), se.OrdSectsNum) AS Q2C
	, fo.OrderJobName AS JobName
	, oe.OrderLineCurTargetDt AS TargetDate
	, ol.OrderLineShipDt AS ShipDate
	, oe.OrderLineActAssyStDt AS Released
	, rt.RoutingQueuedDt AS Queued
	, rt.RoutingActStartDt AS ActualStart
	, oe.OrderLineSER AS SER
	, (rt.RoutingRunTimeMin ) As RunTime
	, oe.OrderLineElecClpx AS ElecClpx
	, oe.OrderLineMechClpx AS MechClpx
	, rt.RoutingSchStartDt AS ScheduledStart
	, rt.RoutingSchFinishDt AS ScheduledFinish
	, rt.RoutingActFinishDt AS ActualFinish
	FROM dbo.SEOrders AS fo INNER JOIN
		dbo.OrderLines AS ol ON fo.OrderId = ol.OrderId INNER JOIN
		dbo.AssemblyLines AS al ON al.AssemLineId = ol.AssemLineId INNER JOIN
		dbo.OrderLinesExt AS oe ON ol.OrderLineId = oe.OrderLineId INNER JOIN
		dbo.OrdSections AS se ON ol.OrderLineId = se.OrderLineId INNER JOIN
		dbo.SectionsRouting AS rt ON rt.OrdSectsId = se.OrdSectsId
	WHERE al.AssemLineAbbrv = @pAssemblyLineAbbrv
	AND rt.RoutingSchFinishDt >= @RunDate 
	AND rt.RoutingSchFinishDt < @EndDate
	AND rt.RoutingActFinishDt = '1901-01-01'
	AND rt.RoutingWorkCenter = @WorkCenter 
	AND ol.OrderLineShipped = 0 
	AND ol.OrderLineShipDt < '2020-01-01'
	AND oe.OrderLineCurTargetDt > '1901-01-01'

	ORDER BY rt.RoutingSchFinishDt, fo.OrderFO, ol.OrderLineNum, se.OrdSectsNum
END
     
END