/****** Object:  UserDefinedFunction [dbo].[CreateDelimitedTableString]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CreateDelimitedTableString]
(@ETLImportFileId int)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @DestTableName varchar(100),
 @LoadTableName varchar(100),
 @FieldNames varchar(2000),
 @Select varchar(5000),
 @SQL varchar(max),
 @FieldNumbers varchar(max),
 @FieldNumbersWithCast varchar(max),
 @Delimiter char(1),
 @TabDelimited bit = 0

SET @DestTableName = ''
SET @LoadTableName = ''
SET @FieldNames = ''
SET @Select = ''
SET @FieldNumbersWithCast = ''
SET @SQL = ''
SET @FieldNumbers = ''
IF Exists (Select 1 From ETL_ImportDictionary Where ETLImportFileId = @ETLImportFileId)
BEGIN
Select @DestTableName = DestTableName, @LoadTableName = LoadTableName, @Delimiter = mi.Delimiter, @TabDelimited = mi.TabDelimiter
--select *
 FROM dbo.[ETL_ImportFiles] mi
 JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
 JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
 Where mi.ETLImportFileId = @ETLImportFileId
 AND mz.Active = 1
 AND Completed = 0 AND ContainsErrors = 0
SELECT @FieldNames = @FieldNames + Quotename(FieldName) + ', '
 FROM ETL_ImportDictionary id
 Where id.ETLImportFileId = @ETLImportFileId
 Order by FieldsOrder

SELECT @FieldNames = Substring(@FieldNames, 1, Len(@FieldNames) - 1)

--Remove the following cast option if you don't want to have a cast
--casting can cause issues if the data you're receiving isn't what you think it is
--in that case it might be better to set everything as a string, you can leave the cast here as well
--and just allow the cast to cast to varchar too
SELECT @FieldNumbersWithCast = @FieldNumbersWithCast + 'Cast (' + Quotename(Cast(FieldsOrder as varchar)) + ' as ' + DataTypeDescription + ')' + ','
 FROM dbo.ETL_ImportDictionary
 WHERE ETLImportFileId = @ETLImportFileId
 ORDER BY FieldsOrder

SELECT @FieldNumbers = @FieldNumbers + Quotename(Cast(FieldsOrder as varchar)) + ','
FROM dbo.ETL_ImportDictionary
WHERE ETLImportFileId = @ETLImportFileId
 ORDER BY FieldsOrder

SELECT @FieldNumbers = Substring(@FieldNumbers, 1, Len(@FieldNumbers)- 1)

SELECT @FieldNumbersWithCast = Substring(@FieldNumbersWithCast, 1, Len(@FieldNumbersWithCast)- 1)

SELECT @SELECT = 'Insert into ' + Quotename(@DestTableName) + '(' + @FieldNames + ') SELECT '

IF @TabDelimited = Cast(1 as bit)
BEGIN
	--Replace @FieldNumbersWithCast with @FieldNumbers if you want to remove cast option
	SELECT @SQL = @SELECT +  @FieldNumbersWithCast +  ' FROM (Select d.Id, WordNumber, Word ' + 
	 'FROM dbo.' + Quotename(@LoadTableName) + ' d Cross Apply dbo.DelimitedItem(d.EverythingElse,' + Quotename(@Delimiter, '''') + ')'
	 + ') p PIVOT (MAX([Word]) FOR WordNumber in (' + @FieldNumbers + ')) as pvt; '
END
ELSE
BEGIN
	--Replace @FieldNumbersWithCast with @FieldNumbers if you want to remove cast option
	SELECT @SQL = @SELECT +  @FieldNumbersWithCast +  ' FROM (Select d.Id, WordNumber, Word ' + 
	 'FROM dbo.' + Quotename(@LoadTableName) + ' d Cross Apply dbo.TabDelimitedItem(d.EverythingElse)'
	 + ') p PIVOT (MAX([Word]) FOR WordNumber in (' + @FieldNumbers + ')) as pvt; '
END
END
RETURN @SQL
END
GO
/****** Object:  UserDefinedFunction [dbo].[CreateParsedTableString]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CreateParsedTableString]
(@ETLImportFileId int)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @DestTableName varchar(100),
 @LoadTableName varchar(100),
 @FieldNames varchar(2000),
 @Select varchar(5000),
 @Substring varchar(5000),
 @SQL varchar(max)
SET @DestTableName = ''
SET @LoadTableName = ''
SET @FieldNames = ''
SET @Select = ''
SET @SQL = ''
SET @Substring = ''
IF Exists (Select 1 From ETL_ImportDictionary Where ETLImportFileId = @ETLImportFileId)
BEGIN
Select @DestTableName = DestTableName, @LoadTableName = LoadTableName
 FROM dbo.[ETL_ImportFiles] mi
 JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
 JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
 Where mi.ETLImportFileId = @ETLImportFileId
 AND mz.Active = 1
 AND Completed = 0 AND ContainsErrors = 0
SELECT @FieldNames = @FieldNames + Quotename(FieldName) + ', '
 FROM ETL_ImportDictionary id
 Where id.ETLImportFileId = @ETLImportFileId
SELECT @FieldNames = Substring(@FieldNames, 1, Len(@FieldNames) - 1)
SELECT @SELECT = 'Insert into ' + Quotename(@DestTableName) + '(' + @FieldNames + ') SELECT '
SELECT @Substring = @Substring + 'Substring(EverythingElse, ' + Cast(ParseStartPoint as varchar) + ', ' 
 + Cast((ParseEndPoint - ParseStartPoint + 1) as varchar) + ') as ' + FieldName + ' , '
 From ETL_ImportDictionary id
 Where id.ETLImportFileId = @ETLImportFileId
SELECT @Substring = Substring(@Substring, 1, len(@Substring) - 1)
SELECT @SQL = @SELECT + @Substring + ' FROM dbo.' + 
 Quotename(@LoadTableName) + '; '
END
RETURN @SQL
END
GO
/****** Object:  UserDefinedFunction [dbo].[DelimitedItem]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DelimitedItem] 
(
 -- Add the parameters for the function here
 @String varchar(8000), 
 @Delimiter char(1)
)
RETURNS 
@ListTable TABLE 
(
 -- Add the column definitions for the TABLE variable here
 WordNumber int,
 StartPoint int,
 EndingPoint int,
 Word varchar(500)
)
AS
BEGIN
with csvtbl(WordNumber, StartPoint,EndingPoint, Word)as
 (select WordNumber=1, StartPoint=1, EndingPoint=charindex(@Delimiter,@String+@Delimiter), Word=substring(@String, 1, charindex(@Delimiter,@String+@Delimiter)-1)
 union all
 select WordNumber=WordNumber+1, StartPoint=EndingPoint+1, EndingPoint=charindex(@Delimiter,@String+@Delimiter,EndingPoint+1), 
 Word=substring(@String, EndingPoint+1, charindex(@Delimiter,@String+@Delimiter,EndingPoint+1)-(EndingPoint+1))
 from csvtbl where charindex(@Delimiter,@String+@Delimiter,EndingPoint+1) <> 0)
Insert into @ListTable(WordNumber, StartPoint, EndingPoint, Word)
Select WordNumber, StartPoint, EndingPoint, Word
From csvtbl
RETURN 
END
GO
/****** Object:  UserDefinedFunction [dbo].[GetConcatenatedFieldsWithType]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetConcatenatedFieldsWithType]
(@ETLImportFileId int)
RETURNS nvarchar(2000)
AS
BEGIN
DECLARE @Fields nvarchar(2000)
SET @Fields = ''
SELECT @Fields = @Fields + '[' + FieldName + '] ' + DataTypeDescription + ' NULL, '
FROM ETL_ImportDictionary id
Where id.ETLImportFileId = @ETLImportFileId
RETURN @Fields
END
GO
/****** Object:  UserDefinedFunction [dbo].[TabDelimitedItem]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[TabDelimitedItem] 
(
 -- Add the parameters for the function here
 @String varchar(8000)
)
RETURNS 
@ListTable TABLE 
(
 -- Add the column definitions for the TABLE variable here
 WordNumber int,
 StartPoint int,
 EndingPoint int,
 Word varchar(500)
)
AS
BEGIN

with csvtbl(WordNumber, StartPoint,EndingPoint, Word)as
 (select WordNumber=1, StartPoint=1, EndingPoint=charindex(char(9),@String+char(9)), Word=substring(@String, 1, charindex(char(9),@String+char(9))-1)
 union all
 select WordNumber=WordNumber+1, StartPoint=EndingPoint+1, EndingPoint=charindex(char(9),@String+char(9),EndingPoint+1), 
 Word=substring(@String, EndingPoint+1, charindex(char(9),@String+char(9),EndingPoint+1)-(EndingPoint+1))
 from csvtbl where charindex(char(9),@String+char(9),EndingPoint+1) <> 0)

Insert into @ListTable(WordNumber, StartPoint, EndingPoint, Word)
Select WordNumber, StartPoint, EndingPoint, Word
From csvtbl
RETURN 
END

GO
/****** Object:  Table [dbo].[ETL_ImportDictionary]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_ImportDictionary](
	[ETLImportDictionaryId] [int] IDENTITY(1,1) NOT NULL,
	[ETLImportFileId] [int] NOT NULL,
	[FileCode] [char](2) NOT NULL,
	[FieldName] [varchar](200) NULL,
	[ParseStartPoint] [int] NULL,
	[ParseEndPoint] [int] NULL,
	[DataTypeDescription] [varchar](100) NULL,
	[FieldsOrder] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ETL_ImportFiles]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_ImportFiles](
	[ETLImportFileId] [int] IDENTITY(1,1) NOT NULL,
	[ETLFileId] [int] NOT NULL,
	[FileCode] [char](2) NOT NULL,
	[ImportFileName] [varchar](100) NULL,
	[LoadTableName] [varchar](100) NULL,
	[DestTableName] [varchar](100) NULL,
	[Delimited] [bit] NOT NULL,
	[Delimiter] [char](1) NULL,
	[DestFieldToIndex] [varchar](100) NULL,
	[TabDelimiter] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ETL_Run]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_Run](
	[ETLRunId] [int] IDENTITY(1,1) NOT NULL,
	[ETLFileId] [int] NOT NULL,
	[FileCode] [char](2) NOT NULL,
	[FileName] [varchar](200) NOT NULL,
	[FolderName] [varchar](100) NULL,
	[FileLocation] [varchar](500) NULL,
	[RunStart] [smalldatetime] NULL,
	[RunEnd] [smalldatetime] NULL,
	[Completed] [bit] NOT NULL,
	[ContainsErrors] [bit] NOT NULL,
	[Duration] [int] NULL,
	[Imported] [bit] NOT NULL,
	[FileDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ETL_ZipFiles]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_ZipFiles](
	[ETLFileId] [int] IDENTITY(1,1) NOT NULL,
	[FileCode] [char](2) NOT NULL,
	[FilePrefix] [varchar](100) NULL,
	[FileDescription] [varchar](500) NULL,
	[FolderNamePrefix] [varchar](100) NULL,
	[RunOrder] [int] NULL,
	[Active] [bit] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Raw_SampleFile1]    Script Date: 10/10/2022 1:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Raw_SampleFile1](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EverythingElse] [varchar](500) NULL,
 CONSTRAINT [PK_Raw_SampleFileZip1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ETL_ImportDictionary] ON 
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (1, 1, N'S1', N'Item', NULL, NULL, N'varchar(200)', 4)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (2, 1, N'S1', N'OrderDate', NULL, NULL, N'datetime', 1)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (3, 1, N'S1', N'Region', NULL, NULL, N'varchar(50)', 2)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (4, 1, N'S1', N'Rep', NULL, NULL, N'varchar(200)', 3)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (5, 1, N'S1', N'Total', NULL, NULL, N'decimal(15,2)', 7)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (6, 1, N'S1', N'UnitCost', NULL, NULL, N'decimal(15,2)', 6)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (7, 1, N'S1', N'Units', NULL, NULL, N'int', 5)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (8, 2, N'S2', N'Category', 1, 20, N'varchar(20)', 1)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (9, 2, N'S2', N'FoodItem', 21, 220, N'varchar(122)', 2)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (10, 2, N'S2', N'Rep', 221, 236, N'varchar(15)', 3)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (11, 3, N'S3', N'Item', NULL, NULL, N'varchar(200)', 4)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (12, 3, N'S3', N'OrderDate', NULL, NULL, N'datetime', 1)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (13, 3, N'S3', N'Region', NULL, NULL, N'varchar(50)', 2)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (14, 3, N'S3', N'Rep', NULL, NULL, N'varchar(200)', 3)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (15, 3, N'S3', N'Total', NULL, NULL, N'decimal(15,2)', 7)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (16, 3, N'S3', N'UnitCost', NULL, NULL, N'decimal(15,2)', 6)
GO
INSERT [dbo].[ETL_ImportDictionary] ([ETLImportDictionaryId], [ETLImportFileId], [FileCode], [FieldName], [ParseStartPoint], [ParseEndPoint], [DataTypeDescription], [FieldsOrder]) VALUES (17, 3, N'S3', N'Units', NULL, NULL, N'int', 5)
GO
SET IDENTITY_INSERT [dbo].[ETL_ImportDictionary] OFF
GO
SET IDENTITY_INSERT [dbo].[ETL_ImportFiles] ON 
GO
INSERT [dbo].[ETL_ImportFiles] ([ETLImportFileId], [ETLFileId], [FileCode], [ImportFileName], [LoadTableName], [DestTableName], [Delimited], [Delimiter], [DestFieldToIndex], [TabDelimiter]) VALUES (1, 1, N'S1', N'SampleDataCommaDelimited_', N'Raw_SampleFile1', N'Curated_SampleDataCommaDel', 1, N',', N'S1Data1Id', 0)
GO
INSERT [dbo].[ETL_ImportFiles] ([ETLImportFileId], [ETLFileId], [FileCode], [ImportFileName], [LoadTableName], [DestTableName], [Delimited], [Delimiter], [DestFieldToIndex], [TabDelimiter]) VALUES (2, 2, N'S2', N'sampledatafoodinfoFixedLength_', N'Raw_SampleFile1', N'Curated_sampledatafoodinfoFixedLength', 0, N' ', N'S2Data1Id', 0)
GO
INSERT [dbo].[ETL_ImportFiles] ([ETLImportFileId], [ETLFileId], [FileCode], [ImportFileName], [LoadTableName], [DestTableName], [Delimited], [Delimiter], [DestFieldToIndex], [TabDelimiter]) VALUES (3, 3, N'S3', N'SampleDataTableTabDelimited_', N'Raw_SampleFile1', N'Curated_SampleDataTableTabDelimited', 1, N' ', N'S3Data1Id', 0)
GO
SET IDENTITY_INSERT [dbo].[ETL_ImportFiles] OFF
GO
SET IDENTITY_INSERT [dbo].[ETL_ZipFiles] ON 
GO
INSERT [dbo].[ETL_ZipFiles] ([ETLFileId], [FileCode], [FilePrefix], [FileDescription], [FolderNamePrefix], [RunOrder], [Active]) VALUES (1, N'S1', N'SampleDataCommaDelimited_', N'Comma Delimited', N'sampledata', 1, 1)
GO
INSERT [dbo].[ETL_ZipFiles] ([ETLFileId], [FileCode], [FilePrefix], [FileDescription], [FolderNamePrefix], [RunOrder], [Active]) VALUES (2, N'S2', N'sampledatafoodinfoFixedLength_', N'Fixed len test', N'sampledata', 1, 1)
GO
INSERT [dbo].[ETL_ZipFiles] ([ETLFileId], [FileCode], [FilePrefix], [FileDescription], [FolderNamePrefix], [RunOrder], [Active]) VALUES (3, N'S3', N'SampleDataTableTabDelimited_', N'Tab Delimited test', N'sampledata', 1, 1)
GO
SET IDENTITY_INSERT [dbo].[ETL_ZipFiles] OFF
GO
ALTER TABLE [dbo].[ETL_ImportFiles] ADD  DEFAULT ((0)) FOR [TabDelimiter]
GO
/****** Object:  StoredProcedure [dbo].[ETL_GetLoadFileName]    Script Date: 10/10/2022 1:35:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[ETL_GetLoadFileName] (
		@FileName varchar(200),
		@LoadTableName varchar(50) OUTPUT
)
AS 

SET NOCOUNT ON;

Select @LoadTableName = LoadTableName
--select *
From dbo.[ETL_ImportFiles] e
Where Substring(@FileName, 1, Len(e.ImportFileName)) = e.ImportFileName
	
GO
/****** Object:  StoredProcedure [dbo].[ETL_InsertFileRun]    Script Date: 10/10/2022 1:35:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[ETL_InsertFileRun] (
		@FileName varchar(200)
)
AS 

SET NOCOUNT ON;

DECLARE @FileId int,
		@FileCode char(2),
		@FileLocation varchar(20) = 'curated',
		@FilePrefixLen int,
		@FileDateString varchar(8),
		@FileDate smalldatetime,
		@RunCreated bit = 0


--Only create a run if the file is a loadable file and hasn't already been completed
Select @FileId = e.ETLFileId, @FileCode = FileCode, @FilePrefixLen = Len(e.ImportFileName)
--select *
From dbo.[ETL_ImportFiles] e
Where Substring(@FileName, 1, Len(e.ImportFileName)) = e.ImportFileName
AND Exists (Select 1
			From dbo.ETL_ZipFiles z
			Where z.ETLFileId = e.ETLFileId
			AND z.[Active] = 1)
AND Not Exists (Select 1
				From dbo.ETL_Run e
				Where e.[FileName] = @FileName AND e.Completed = 1)

--Let's make sure this is a validly named file that exists in our import file types tables
If @FileCode IS NOT NULL
BEGIN

	Select @FileDateString = Substring(@FileName, @FilePrefixLen + 1, 8)

	Select @FileDate = Cast(Substring(@FileDateString, 5, 2) + '/' + Substring(@FileDateString, 7, 2) + '/' + Substring(@FileDateString, 1, 4) as smalldatetime)


	--If it was previously run, clear it out if you've developed the ability to re-load an already loaded file
	Delete e
	--select *
	From dbo.[ETL_Run] e
	Where e.[FileName] = @FileName
	AND e.Completed = 0

	--Insert the new file into the table
	Insert into dbo.ETL_Run (ETLFileId, FileCode, [FileName], FolderName, FileLocation, RunStart, RunEnd, Completed, ContainsErrors, Duration, Imported, FileDate)
	Select @FileId, @FileCode, @FileName, @FileLocation, @FileLocation + @FileName, GetDate(), NULL, 0, 0, NULL, 0, @FileDate

	Select @RunCreated = 1

END


SELECT @RunCreated RunCreated
	
GO
/****** Object:  StoredProcedure [dbo].[Package_ClearStagingTable]    Script Date: 10/10/2022 1:35:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[Package_ClearStagingTable]

AS
SET NOCOUNT ON

Truncate table dbo.Raw_SampleFile1
GO
/****** Object:  StoredProcedure [dbo].[Package_CreateDestinationTables]    Script Date: 10/10/2022 1:35:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Package_CreateDestinationTables] (
		@FileName varchar(200)
)
AS

SET NOCOUNT ON

BEGIN

DECLARE @SQL nvarchar(max),
 @DropSQL nvarchar(max)
DECLARE @DestTableCreate TABLE(ETLImportFileId int, 
 BeginString nvarchar(2000), FieldString nvarchar(2000), EndString nvarchar(2000), TotalString nvarchar(max))
SET @SQL = ''
SET @DropSQL = ''

	SELECT @DropSQL = @DropSQL + 
	 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' + 
	 Quotename('[dbo].' + Quotename(DestTableName), '''') + 
	 ') AND type in (N' +
	 Quotename('U', '''') +
	 '))DROP TABLE [dbo].' + 
	 Quotename(DestTableName) +
	 '; '
	FROM dbo.[ETL_ImportFiles] mi
	JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
	JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
	Where mz.Active = 1
	AND Completed = 0 AND ContainsErrors = 0
	and r.[FileName] = @FileName

	--CREATE TABLE STRING
	Insert into @DestTableCreate(ETLImportFileId, BeginString)
	SELECT ETLImportFileId, 'CREATE TABLE ' + Quotename('dbo') + '.' +
	 Quotename(DestTableName) + '( ' + Quotename(DestTableName + 'Id') + ' int NOT NULL Identity(1,1), '
	FROM dbo.[ETL_ImportFiles] mi
	JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
	JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
	Where mz.Active = 1
	AND Completed = 0 AND ContainsErrors = 0
	AND Exists (Select 1 From ETL_ImportDictionary md --Actually contains fields
	 Where md.ETLImportFileId = mi.ETLImportFileId)
	 AND r.[FileName] = @FileName

	--Concatenate Field Names and Types 
	Update d
	set FieldString = dbo.[GetConcatenatedFieldsWithType](d.ETLImportFileId)
	FROM @DestTableCreate d
	Update d
	set EndString = ' CONSTRAINT ' + Quotename('PK_' + DestTableName) + ' PRIMARY KEY CLUSTERED 
	(
	 ' + Quotename(DestTableName + 'Id') + ' ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]; '
	FROM @DestTableCreate d
	JOIN dbo.[ETL_ImportFiles] mi on mi.ETLImportFileId = d.ETLImportFileId
	JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
	JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
	Where mz.Active = 1
	AND Completed = 0 AND ContainsErrors = 0
	AND Exists (Select 1 From ETL_ImportDictionary md
	 Where md.ETLImportFileId = mi.ETLImportFileId)
	AND r.[FileName] = @FileName

	Update d
	set TotalString = BeginString + FieldString + EndString
	FROM @DestTableCreate d
	Select @SQL = @SQL + TotalString
	From @DestTableCreate

--SELECT @DropSQL
--Select @SQL
EXEC sp_executeSQL @DropSQL
EXEC sp_executeSQL @SQL
END
GO
/****** Object:  StoredProcedure [dbo].[Package_CreateIndexes]    Script Date: 10/10/2022 1:35:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Package_CreateIndexes]
AS

SET NOCOUNT ON

BEGIN

DECLARE @IndexSQL nvarchar(max)
SET @IndexSQL = ''
--Create DROP TABLE SQL
SELECT @IndexSQL = @IndexSQL + 
 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' + 
 Quotename('[dbo].' + Quotename(DestTableName), '''') + 
 ') AND type in (N' +
 Quotename('U', '''') +
 '))CREATE INDEX IX_' + DestTableName + '_' + CONVERT(varchar(255), Replace(NEWID(), '-', '')) + ' ON [dbo].' + Quotename(DestTableName) +
 ' (' + DestFieldToIndex + ')' + 
 '; '
FROM dbo.[ETL_ImportFiles] mi
JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
Where mz.Active = 1
AND Completed = 0 AND ContainsErrors = 0
AND r.Imported = 1 AND mi.DestFieldToIndex IS NOT NULL

--SELECT @IndexSQL
EXEC sp_executeSQL @IndexSQL
END
GO
/****** Object:  StoredProcedure [dbo].[Package_CreateLoadTables]    Script Date: 10/10/2022 1:35:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Package_CreateLoadTables]
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(max),
 @DropSQL nvarchar(max)
SET @SQL = ''
SET @DropSQL = ''
--Create DROP TABLE SQL
SELECT @DropSQL = @DropSQL + 
 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' + 
 Quotename('[dbo].' + Quotename(LoadTableName), '''') + 
 ') AND type in (N' +
 Quotename('U', '''') +
 '))DROP TABLE [dbo].' + 
 Quotename(LoadTableName) +
 '; '
From (Select Distinct LoadTableName
		FROM dbo.[ETL_ImportFiles] mi
		JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
		JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
		Where mz.Active = 1
		AND Completed = 0 AND ContainsErrors = 0) as a

--Add Select and Primary Key Constraint
Select @SQL = @SQL + 
 'CREATE TABLE ' + 
 Quotename('dbo') + '.' +
 Quotename(LoadTableName) + 
 '( ' + 
 Quotename('Id') +
 ' int NOT NULL Identity(1,1), EverythingElse varchar(500)' +
 ' CONSTRAINT [PK_' + LoadTableName + '] PRIMARY KEY CLUSTERED' + 
 '(
 [Id' + '] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]; '
From (Select Distinct LoadTableName
		FROM dbo.[ETL_ImportFiles] mi
		JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
		JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
		Where mz.Active = 1
		AND Completed = 0 AND ContainsErrors = 0) as a
--SELECT @DropSQL
--Select @SQL
EXEC sp_executeSQL @DropSQL
EXEC sp_executeSQL @SQL
GO
/****** Object:  StoredProcedure [dbo].[Package_PopulateDestination]    Script Date: 10/10/2022 1:35:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Package_PopulateDestination] (
	@FileName varchar(200) 
)
AS

SET NOCOUNT ON

BEGIN

DECLARE @SQL nvarchar(max)
SET @SQL = ''
IF Exists (Select 1
 From dbo.[ETL_ImportFiles] mi
 JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
 JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
 Where mz.Active = 1
 AND Completed = 0 AND ContainsErrors = 0
 AND r.[FileName] = @FileName
 AND mi.Delimited = 1)
BEGIN
 Select @SQL = @SQL + dbo.CreateDelimitedTableString(mi.ETLFileId) 
 FROM dbo.[ETL_ImportFiles] mi
 JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
 JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
 Where mz.Active = 1
 AND r.[FileName] = @FileName
 AND Completed = 0 AND ContainsErrors = 0 AND mi.Delimited = 1
END
IF Exists (Select 1
 From dbo.[ETL_ImportFiles] mi
 JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
 JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
 Where mz.Active = 1
 AND Completed = 0 AND ContainsErrors = 0
 AND r.[FileName] = @FileName
 AND mi.Delimited = 0)
BEGIN 
 Select @SQL = @SQL + dbo.CreateParsedTableString(mi.ETLFileId) 
 FROM dbo.[ETL_ImportFiles] mi
 JOIN dbo.ETL_zipfiles mz on mi.FileCode = mz.FileCode
 JOIN dbo.ETL_Run r on r.Filecode = mi.FileCode
 Where mz.Active = 1
 AND Completed = 0 AND ContainsErrors = 0
 AND r.[FileName] = @FileName
 AND mi.Delimited = 0
END
--SELECT @SQL
EXEC sp_executeSQL @SQL


END
GO
/****** Object:  StoredProcedure [dbo].[Package_SetRunCompletion]    Script Date: 10/10/2022 1:35:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[Package_SetRunCompletion]
 @FileName varchar(200)
AS
SET NOCOUNT ON

Update m
Set RunEnd = GetDate(), Completed = 1, Duration = DATEDIFF(minute, m.RunStart, GetDate())
--select *
FROM ETL_Run m
WHERE m.Completed = 0 AND ContainsErrors = 0
AND m.[FileName] = @FileName
GO