/* =============================================================================
   BI export database structure - SQL Server and Azure SQL Database

   Replace XXXXXX in dbRSExport_XXXXXX with your installation number everywhere it appears.

   Azure SQL Database:
   - Create the database in the portal (or use an existing database).
   - Connect SSMS / Azure Data Studio directly to that database (database name in
     the connection). Do NOT run PART 1. Run PART 2 only (from "PART 2" below).
   - Azure does not support USE to switch databases; this script has no USE in PART 2.

   SQL Server (on-premises):
   1) Connect to [master], run PART 1, then run PART 2.
   2) If the database already exists: connect to [dbRSExport_XXXXXX] and run PART 2 only.
   3) If you run PART 1 from [master], uncomment USE + GO at the start of PART 2 so
      the rest of the script runs in the target database (or change the connection
      to the target database and leave USE commented).
   ============================================================================= */

/*------------------------------------------------------------------------------
  PART 1 - Create database (SQL Server only — skip entirely on Azure SQL Database)
------------------------------------------------------------------------------*/
USE [master];
GO

IF DB_ID(N'dbRSExport_XXXXXX') IS NULL
    CREATE DATABASE [dbRSExport_XXXXXX];
GO

/*------------------------------------------------------------------------------
  PART 2 - Schema (types, tables, stored procedures)

  Prerequisite: Your session must be connected to [dbRSExport_XXXXXX]
  (required on Azure SQL Database). On SQL Server after PART 1 from [master],
  uncomment the following USE (or switch the connection to the target database):
------------------------------------------------------------------------------*/
-- USE [dbRSExport_XXXXXX];
-- GO
/****** Object:  UserDefinedTableType [dbo].[udtSetting] ******/
IF TYPE_ID(N'dbo.udtSetting') IS NULL
EXEC(N'CREATE TYPE [dbo].[udtSetting] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[CalculatePerformanceFromWeightedProducedUnits] [bit] NULL,
	[MicrostopAsPerformanceLoss] [bit] NULL,
	[ReworkAsQualityLoss] [bit] NULL
)');
GO
/****** Object:  UserDefinedTableType [dbo].[udtStop] ******/
IF TYPE_ID(N'dbo.udtStop') IS NULL
EXEC(N'CREATE TYPE [dbo].[udtStop] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[MeasurePointID] [uniqueidentifier] NULL,
	[MeasurePoint] [nvarchar](100) NULL,
	[IntervalStart] [datetime] NULL,
	[IntervalEnd] [datetime] NULL,
	[TotalStopDuration] [bigint] NULL,
	[StopReason] [nvarchar](300) NULL,
	[Categories] [nvarchar](300) NULL,
	[Station] [nvarchar](100) NULL,
	[Comment] [nvarchar](1000) NULL,
	[ShiftID] [nvarchar](1000) NULL
)');
GO
/****** Object:  UserDefinedTableType [dbo].[udtStopOccasion] ******/
IF TYPE_ID(N'dbo.udtStopOccasion') IS NULL
EXEC(N'CREATE TYPE [dbo].[udtStopOccasion] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[MeasurePointID] [uniqueidentifier] NULL,
	[MeasurePoint] [nvarchar](100) NULL,
	[IntervalStart] [datetime] NULL,
	[IntervalEnd] [datetime] NULL,
	[Duration] [bigint] NULL,
	[TotalStopDuration] [bigint] NULL,
	[ScheduledDuration] [decimal](10, 2) NULL,
	[ProductiveDuration] [decimal](10, 2) NULL,
	[StopQuantity] [bigint] NULL,
	[NumberOfProductiveOccasions] [bigint] NULL,
	[MTBF] [decimal](10, 2) NULL,
	[MTTR] [decimal](10, 2) NULL
)');
GO
/****** Object:  UserDefinedTableType [dbo].[udtWorktime] ******/
IF TYPE_ID(N'dbo.udtWorktime') IS NULL
EXEC(N'CREATE TYPE [dbo].[udtWorktime] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[MeasurePointID] [uniqueidentifier] NULL,
	[MeasurePoint] [nvarchar](100) NULL,
	[IntervalStart] [datetime] NULL,
	[IntervalEnd] [datetime] NULL,
	[ShiftID] [uniqueidentifier] NULL,
	[Shift] [nvarchar](100) NULL,
	[ProductionOrder] [nvarchar](50) NULL,
	[Article] [nvarchar](100) NULL,
	[ArticleName] [nvarchar](100) NULL,
	[ArticleType] [nvarchar](100) NULL,
	[TotalDuration] [decimal](10, 2) NULL,
	[ScheduledDuration] [decimal](10, 2) NULL,
	[ExcludedDuration] [decimal](10, 2) NULL,
	[StopDuration] [decimal](10, 2) NULL,
	[SetupStopDuration] [decimal](10, 2) NULL,
	[NoWorkTimeStopDuration] [decimal](10, 2) NULL,
	[MicroStopDuration] [decimal](10, 2) NULL,
	[ProductionTimeDuration] [decimal](10, 2) NULL,
	[UsedEffectiveTime] [decimal](10, 2) NULL,
	[ReworkedEffectiveTime] [decimal](10, 2) NULL,
	[ScrappedEffectiveTime] [decimal](10, 2) NULL,
	[OptimalProducedUnitsNoMicroStop] [decimal](10, 2) NULL,
	[OptimalProducedUnits] [decimal](10, 2) NULL,
	[ProducedUnits] [decimal](10, 2) NULL,
	[ReworkedUnits] [decimal](10, 2) NULL,
	[ScrappedUnits] [decimal](10, 2) NULL,
	[ApprovedUnits] [decimal](10, 2) NULL
)');
GO
/****** Object:  UserDefinedTableType [dbo].[udtShift] ******/
IF TYPE_ID(N'dbo.udtShift') IS NULL
EXEC(N'CREATE TYPE [dbo].[udtShift] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[MeasurePointID] [uniqueidentifier] NULL,
	[MeasurePoint] [nvarchar](100) NULL,
	[IntervalStart] [datetimeoffset] NULL,
	[IntervalEnd] [datetimeoffset] NULL,
	[ShiftID] [uniqueidentifier] NULL,
	[ShiftName] [nvarchar](50) NULL,
	[ShiftType] [nvarchar](50) NULL,
	[ShiftTeam] [nvarchar](50) NULL,
	[Weekday] [nvarchar](20) NULL
)');
GO
/****** Object:  Table [dbo].[tblSetting] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.tblSetting', N'U') IS NULL
CREATE TABLE [dbo].[tblSetting](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[CalculatePerformanceFromWeightedProducedUnits] [bit] NULL,
	[MicrostopAsPerformanceLoss] [bit] NULL,
	[ReworkAsQualityLoss] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] 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
/****** Object:  Table [dbo].[tblStop] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.tblStop', N'U') IS NULL
CREATE TABLE [dbo].[tblStop](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[MeasurePointID] [uniqueidentifier] NOT NULL,
	[MeasurePoint] [nvarchar](100) NOT NULL,
	[IntervalStart] [datetime] NOT NULL,
	[IntervalEnd] [datetime] NULL,
	[TotalStopDuration] [bigint] NULL,
	[StopReason] [nvarchar](300) NULL,
	[Categories] [nvarchar](300) NULL,
	[Station] [nvarchar](100) NULL,
	[Comment] [nvarchar](1000) NULL,
	[ShiftID] [nvarchar](1000) NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] ASC,
	[MeasurePointID] ASC,
	[IntervalStart] 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
/****** Object:  Table [dbo].[tblStopOccasion] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.tblStopOccasion', N'U') IS NULL
CREATE TABLE [dbo].[tblStopOccasion](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[MeasurePointID] [uniqueidentifier] NOT NULL,
	[MeasurePoint] [nvarchar](100) NOT NULL,
	[IntervalStart] [datetime] NOT NULL,
	[IntervalEnd] [datetime] NULL,
	[Duration] [bigint] NULL,
	[TotalStopDuration] [bigint] NULL,
	[ScheduledDuration] [decimal](10, 2) NULL,
	[ProductiveDuration] [decimal](10, 2) NULL,
	[StopQuantity] [bigint] NULL,
	[NumberOfProductiveOccasions] [bigint] NULL,
	[MTBF] [decimal](10, 2) NULL,
	[MTTR] [decimal](10, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] ASC,
	[MeasurePointID] ASC,
	[IntervalStart] 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
/****** Object:  Table [dbo].[tblWorktime] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.tblWorktime', N'U') IS NULL
CREATE TABLE [dbo].[tblWorktime](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[MeasurePointID] [uniqueidentifier] NOT NULL,
	[MeasurePoint] [nvarchar](100) NOT NULL,
	[IntervalStart] [datetime] NOT NULL,
	[IntervalEnd] [datetime] NULL,
	[ShiftID] [uniqueidentifier] NULL,
	[Shift] [nvarchar](100) NULL,
	[ProductionOrder] [nvarchar](50) NULL,
	[Article] [nvarchar](100) NULL,
	[ArticleName] [nvarchar](100) NULL,
	[ArticleType] [nvarchar](100) NULL,
	[TotalDuration] [decimal](10, 2) NULL,
	[ScheduledDuration] [decimal](10, 2) NULL,
	[ExcludedDuration] [decimal](10, 2) NULL,
	[StopDuration] [decimal](10, 2) NULL,
	[SetupStopDuration] [decimal](10, 2) NULL,
	[NoWorkTimeStopDuration] [decimal](10, 2) NULL,
	[MicroStopDuration] [decimal](10, 2) NULL,
	[ProductionTimeDuration] [decimal](10, 2) NULL,
	[UsedEffectiveTime] [decimal](10, 2) NULL,
	[ReworkedEffectiveTime] [decimal](10, 2) NULL,
	[ScrappedEffectiveTime] [decimal](10, 2) NULL,
	[OptimalProducedUnitsNoMicroStop] [decimal](10, 2) NULL,
	[OptimalProducedUnits] [decimal](10, 2) NULL,
	[ProducedUnits] [decimal](10, 2) NULL,
	[ReworkedUnits] [decimal](10, 2) NULL,
	[ScrappedUnits] [decimal](10, 2) NULL,
	[ApprovedUnits] [decimal](10, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] ASC,
	[MeasurePointID] ASC,
	[IntervalStart] 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
/****** Object:  Table [dbo].[tblShift] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.tblShift', N'U') IS NULL
CREATE TABLE [dbo].[tblShift](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[MeasurePointID] [uniqueidentifier] NOT NULL,
	[MeasurePoint] [nvarchar](100) NOT NULL,
	[IntervalStart] [datetimeoffset] NOT NULL,
	[IntervalEnd] [datetimeoffset] NULL,
	[ShiftID] [uniqueidentifier] NOT NULL,
	[ShiftName] [nvarchar](50) NULL,
	[ShiftType] [nvarchar](50) NULL,
	[ShiftTeam] [nvarchar](50) NULL,
	[Weekday] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] ASC,
	[ShiftID] 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
/****** Object:  StoredProcedure [dbo].[sp_LoadSettings] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.sp_LoadSettings', N'P') IS NULL
EXEC(N'CREATE PROCEDURE [dbo].[sp_LoadSettings] (@datatable [dbo].[udtSetting] READONLY)
	AS
	BEGIN
		SET NOCOUNT ON;
		SET XACT_ABORT ON
		BEGIN TRANSACTION
	
		MERGE dbo.tblSetting AS target  
		USING @datatable AS source  
		ON (target.InstallationID = source.InstallationID)  
			WHEN  MATCHED THEN  
				UPDATE SET	[CalculatePerformanceFromWeightedProducedUnits] = source.[CalculatePerformanceFromWeightedProducedUnits],
							[MicrostopAsPerformanceLoss] = source.[MicrostopAsPerformanceLoss],
							[ReworkAsQualityLoss] = source.[ReworkAsQualityLoss]
			WHEN NOT MATCHED THEN  
				INSERT ([InstallationID], 
						[Installation], 
						[CalculatePerformanceFromWeightedProducedUnits], 
						[MicrostopAsPerformanceLoss], 
						[ReworkAsQualityLoss])  
				VALUES (source.[InstallationID], 
						source.[Installation], 
						source.[CalculatePerformanceFromWeightedProducedUnits], 
						source.[MicrostopAsPerformanceLoss], 
						source.[ReworkAsQualityLoss]);
	
		COMMIT TRANSACTION
	END');
GO
/****** Object:  StoredProcedure [dbo].[sp_LoadStopOccasions] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.sp_LoadStopOccasions', N'P') IS NULL
EXEC(N'CREATE PROCEDURE [dbo].[sp_LoadStopOccasions] (@datatable [dbo].[udtStopOccasion] READONLY)
	AS
	BEGIN
		SET NOCOUNT ON;
		SET XACT_ABORT ON
		BEGIN TRANSACTION
	
		MERGE dbo.tblStopOccasion AS target  
		USING @datatable AS source  
		ON (target.[InstallationID] = source.[InstallationID] 
			AND target.[MeasurePointID] = CONVERT(uniqueidentifier, source.[MeasurePointID]) 
			AND target.[IntervalStart] = source.[IntervalStart])  
		
			WHEN MATCHED THEN  
				UPDATE SET [Installation] = source.[Installation],
						[MeasurePoint] = source.[MeasurePoint],
						[IntervalEnd] = source.[IntervalEnd],
						[Duration] = source.[Duration],
						[TotalStopDuration] = source.[TotalStopDuration],
						[ScheduledDuration] = source.[ScheduledDuration],
						[ProductiveDuration] = source.[ProductiveDuration],
						[StopQuantity] = source.[StopQuantity],
						[NumberOfProductiveOccasions] = source.[NumberOfProductiveOccasions],
						[MTBF] = source.[MTBF],
						[MTTR] = source.[MTTR]

			WHEN NOT MATCHED THEN  
				INSERT ([InstallationID], 
					[Installation], 
					[MeasurePointID], 
					[MeasurePoint], 
					[IntervalStart], 
					[IntervalEnd], 
					[Duration],
					[TotalStopDuration], 
					[ScheduledDuration], 
					[ProductiveDuration],
					[StopQuantity], 
					[NumberOfProductiveOccasions],
					[MTBF],
					[MTTR])  
				VALUES (source.[InstallationID], 
					source.[Installation], 
					CONVERT(uniqueidentifier,source.[MeasurePointID]), 
					source.[MeasurePoint], 
					source.[IntervalStart],
					source.[IntervalEnd], 
					source.[Duration],
					source.[TotalStopDuration], 
					source.[ScheduledDuration], 
					source.[ProductiveDuration],
					source.[StopQuantity], 
					source.[NumberOfProductiveOccasions], 
					source.[MTBF],
					source.[MTTR]);
	
		COMMIT TRANSACTION
	END');
GO
/****** Object:  StoredProcedure [dbo].[sp_LoadStops] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.sp_LoadStops', N'P') IS NULL
EXEC(N'CREATE PROCEDURE [dbo].[sp_LoadStops] (@datatable [dbo].[udtStop] READONLY)
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	BEGIN TRANSACTION;

	BEGIN TRY
		-- Step 1: Delete any records not in the latest export
		WITH StopTemp AS (
			SELECT InstallationID, MIN(IntervalStart) AS StartTime
			FROM @datatable
			GROUP BY InstallationID
		)

		DELETE target
		FROM dbo.tblStop AS target
		LEFT JOIN @datatable AS source
			ON source.[InstallationID] = target.[InstallationID]
			AND source.[MeasurePointID] = target.[MeasurePointID]
			AND source.[IntervalStart] = target.[IntervalStart]
		JOIN StopTemp AS temp
			ON temp.[InstallationID] = target.[InstallationID]
		WHERE source.InstallationID IS NULL
		  AND target.[IntervalStart] >= temp.StartTime;


		-- Step 2: Upsert the new data
		MERGE dbo.tblStop WITH (HOLDLOCK) AS target  
		USING @datatable AS source  
		ON (target.[InstallationID] = source.[InstallationID] 
			AND target.[MeasurePointID] = CONVERT(uniqueidentifier,source.[MeasurePointID]) 
			AND target.[IntervalStart] = source.[IntervalStart])  
		
			WHEN MATCHED THEN  
				UPDATE SET [Installation] = source.[Installation],
						[MeasurePoint] = source.[MeasurePoint],
						[IntervalEnd] = source.[IntervalEnd],
						[TotalStopDuration] = source.[TotalStopDuration],
						[StopReason] = source.[StopReason],
						[Categories] = source.[Categories],
						[Station] = source.[Station],
						[Comment] = source.[Comment],
						[ShiftID] = source.[ShiftID]

			WHEN NOT MATCHED THEN  
				INSERT ([InstallationID], 
					[Installation], 
					[MeasurePointID], 
					[MeasurePoint], 
					[IntervalStart], 
					[IntervalEnd], 
					[TotalStopDuration], 
					[StopReason], 
					[Categories],
					[Station], 
					[Comment],
					[ShiftID])  
				VALUES (source.[InstallationID], 
					source.[Installation], 
					CONVERT(uniqueidentifier,source.[MeasurePointID]), 
					source.[MeasurePoint], 
					source.[IntervalStart],
					source.[IntervalEnd], 
					source.[TotalStopDuration], 
					source.[StopReason], 
					source.[Categories],
					source.[Station], 
					source.[Comment], 
					source.[ShiftID]);
		COMMIT TRANSACTION;
	END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END');
GO
/****** Object:  StoredProcedure [dbo].[sp_LoadWorktimes] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.sp_LoadWorktimes', N'P') IS NULL
EXEC(N'CREATE PROCEDURE [dbo].[sp_LoadWorktimes] (@datatable [dbo].[udtWorktime] READONLY)
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	BEGIN TRANSACTION;

	BEGIN TRY
		-- Step 1: Delete any records not in the latest export
		WITH WorktimeTemp AS (
			SELECT InstallationID, MIN(IntervalStart) AS StartTime
			FROM @datatable
			GROUP BY InstallationID
		)

		DELETE target
		FROM dbo.tblWorktime AS target
		LEFT JOIN @datatable AS source
			ON source.[InstallationID] = target.[InstallationID]
			AND source.[MeasurePointID] = target.[MeasurePointID]
			AND source.[IntervalStart] = target.[IntervalStart]
		JOIN WorktimeTemp AS temp
			ON temp.[InstallationID] = target.[InstallationID]
		WHERE source.InstallationID IS NULL
		  AND target.[IntervalStart] >= temp.StartTime;


		-- Step 2: Upsert the new data
		MERGE dbo.tblWorktime WITH (HOLDLOCK) AS target  
		USING @datatable AS source  
			ON (target.[InstallationID] = source.[InstallationID] 
				AND target.[MeasurePointID] = CONVERT(uniqueidentifier,source.[MeasurePointID]) 
				AND target.[IntervalStart] = source.[IntervalStart])  
		
			WHEN MATCHED THEN  
				UPDATE SET [Installation] = source.[Installation],
						[MeasurePoint] = source.[MeasurePoint],
						[IntervalEnd] = source.[IntervalEnd],
						[ShiftID] = source.[ShiftID],
						[Shift] = source.[Shift],
						[ProductionOrder] = source.[ProductionOrder],
						[Article] = source.[Article],
						[ArticleName] = source.[ArticleName],
						[ArticleType] = source.[ArticleType],
						[TotalDuration] = source.[TotalDuration],
						[ScheduledDuration] = source.[ScheduledDuration],
						[ExcludedDuration] = source.[ExcludedDuration],
						[StopDuration] = source.[StopDuration],
						[SetupStopDuration] = source.[SetupStopDuration],
						[NoWorkTimeStopDuration] = source.[NoWorkTimeStopDuration],
						[MicroStopDuration] = source.[MicroStopDuration],
						[ProductionTimeDuration] = source.[ProductionTimeDuration],
						[UsedEffectiveTime] = source.[UsedEffectiveTime],
						[ReworkedEffectiveTime] = source.[ReworkedEffectiveTime],
						[ScrappedEffectiveTime] = source.[ScrappedEffectiveTime],
						[OptimalProducedUnitsNoMicroStop] = source.[OptimalProducedUnitsNoMicroStop],
						[OptimalProducedUnits] = source.[OptimalProducedUnits],
						[ProducedUnits] = source.[ProducedUnits],
						[ReworkedUnits] = source.[ReworkedUnits],
						[ScrappedUnits] = source.[ScrappedUnits],
						[ApprovedUnits] = source.[ApprovedUnits]
					
			WHEN NOT MATCHED THEN  
				INSERT ([InstallationID], 
					[Installation], 
					[MeasurePointID], 
					[MeasurePoint], 
					[IntervalStart], 
					[IntervalEnd],
					[ShiftID],
					[Shift],
					[ProductionOrder],
					[Article],
					[ArticleName],
					[ArticleType],
					[TotalDuration],
					[ScheduledDuration],
					[ExcludedDuration],
					[StopDuration],
					[SetupStopDuration],
					[NoWorkTimeStopDuration],
					[MicroStopDuration],
					[ProductionTimeDuration],
					[UsedEffectiveTime],
					[ReworkedEffectiveTime],
					[ScrappedEffectiveTime],
					[OptimalProducedUnitsNoMicroStop],
					[OptimalProducedUnits],
					[ProducedUnits],
					[ReworkedUnits],
					[ScrappedUnits],
					[ApprovedUnits]
					)  
				VALUES (source.[InstallationID], 
					source.[Installation], 
					CONVERT(uniqueidentifier,source.[MeasurePointID]), 
					source.[MeasurePoint], 
					source.[IntervalStart],
					source.[IntervalEnd], 
					source.[ShiftID],
					source.[Shift],
					source.[ProductionOrder],
					source.[Article],
					source.[ArticleName],
					source.[ArticleType],
					source.[TotalDuration],
					source.[ScheduledDuration],
					source.[ExcludedDuration],
					source.[StopDuration],
					source.[SetupStopDuration],
					source.[NoWorkTimeStopDuration],
					source.[MicroStopDuration],
					source.[ProductionTimeDuration],
					source.[UsedEffectiveTime],
					source.[ReworkedEffectiveTime],
					source.[ScrappedEffectiveTime],
					source.[OptimalProducedUnitsNoMicroStop],
					source.[OptimalProducedUnits],
					source.[ProducedUnits],
					source.[ReworkedUnits],
					source.[ScrappedUnits],
					source.[ApprovedUnits]
					);
		COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END');
GO
/****** Object:  StoredProcedure [dbo].[sp_LoadShifts] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.sp_LoadShifts', N'P') IS NULL
EXEC(N'CREATE PROCEDURE [dbo].[sp_LoadShifts] (@datatable [dbo].[udtShift] READONLY)
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;

	MERGE dbo.tblShift AS target  
	USING @datatable AS source  
		ON (target.[InstallationID] = source.[InstallationID] 
			AND target.[ShiftID] = source.[ShiftID])
		WHEN MATCHED THEN  
			UPDATE SET 
				[Installation] = source.[Installation],
				[MeasurePoint] = source.[MeasurePoint],
				[IntervalEnd] = source.[IntervalEnd],
				[ShiftName] = source.[ShiftName],
				[ShiftType] = source.[ShiftType],
				[ShiftTeam] = source.[ShiftTeam],
				[Weekday] = source.[Weekday]
		WHEN NOT MATCHED THEN  
			INSERT ([InstallationID], 
				[Installation], 
				[MeasurePointID], 
				[MeasurePoint], 
				[IntervalStart], 
				[IntervalEnd],
				[ShiftID],
				[ShiftName],
				[ShiftType],
				[ShiftTeam],
				[Weekday]
				)  
			VALUES (source.[InstallationID], 
				source.[Installation], 
				CONVERT(uniqueidentifier,source.[MeasurePointID]), 
				source.[MeasurePoint], 
				source.[IntervalStart],
				source.[IntervalEnd], 
				source.[ShiftID],
				source.[ShiftName],
				source.[ShiftType],
				source.[ShiftTeam],
				source.[Weekday]
				);
	COMMIT TRANSACTION;
END');
GO