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

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

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


	CREATE ROLE twimanagement_readWrite AUTHORIZATION dbo 
	GO  

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

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'twimanagement_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 
			   (
			   '!/twi-management(/:search)(/:archived)(/)'
			   , 'twi-management'
			   , 'custom-screens/twi-management/'
			   , 'twimanagement_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'TWI Management'
			   ,@result_routeId 
			   ,1
			   ,1
			   ,1
			   ,'twimanagement_read'
			   ,NULL
			 )
	GO




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

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('twi_management','TWI Management')

	SET @ModuleId = @@IDENTITY

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


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


-------------------
-- SP Permissions
-------------------
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.GetEntries' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.GetEntries' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.GetCatalogs' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.GetCatalogs' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.UpsertEntry' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.UpsertEntry' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.DeleteEntry' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.DeleteEntry' -- the procedure
,@value = N'' 
GO





EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.DownloadFile' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.DownloadFile' -- the procedure
,@value = N'' 
GO



EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.DownloadFileByPath' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.DownloadFileByPath' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-SYSTEM.ConvertBase64ToBin' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-SYSTEM.ConvertBase64ToBin' -- the procedure
,@value = N'' 
GO
-------------------
-- 
-------------------
create schema TWI

create table TWI.CatProcesses (
	Id int identity(1,1) not null
	,[Name] varchar(20) not null
)


insert into TWI.CatProcesses
Values('ALL'),('EAF'),('LMF'),('VTD'),('CASTER')



CREATE TABLE TWI.Entries( 
Id int identity(1,1) not null,
[Name] varchar(MAX) not null,
[Document] varchar(MAX) null, -- (link to a path, can be null)
[DocumentBin] VARBINARY(MAX) null,
[CreationDate] datetime not null,
[Author] varchar(100) null,
[StartDate] datetime null, 
[EndDate] datetime null,
[GradeId] int null,   
[MeltOrderId] int null,
[Archived] bit default 0

)

-------------------
--  STORED PROCEDURES
-------------------
USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[DeleteEntry]    Script Date: 4 oct. 2021 20:49:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[DeleteEntry]
	@Id INT
	AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'


	-----------------------
	-- Query
	-----------------------

	DELETE TWI.Entries
		Where Id = @Id
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

GO


USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[DownloadFile]    Script Date: 4 oct. 2021 20:50:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[DownloadFile]
	@Id INT
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
	
	-----------------------
	-- Query
	-----------------------
	DECLARE @File_BIN VARBINARY(MAX)
	DECLARE @File_Extension VARCHAR(MAX)
	DECLARE @File_Name VARCHAR(MAX)
	DECLARE @File_Name_DEF VARCHAR(MAX)

	SELECT
		@File_BIN = DocumentBin
		,@File_Extension = cast(DocumentExtension as varchar(MAX))
		,@File_Name = cast(Document as varchar(MAX))
	from TWI.Entries
	where Id = @Id


	DECLARE @PATH VARCHAR(MAX)
	DECLARE @ObjectToken INT
	
	DECLARE	@DWG_PATH VARCHAR(8000)

	--SET @File_Name_DEF = 'Hola3'
	SET @File_Name_DEF = TRIM(@File_Name)

	--SET @PATH = 'C:\inetpub\wwwroot\ColumbiaMS\Export\PDF\' + @File_Name + '.' + @File_Extension
	   SET @PATH = 'C:\ToFolder\' + @File_Name_DEF + '.' + @File_Extension	
	--WRITE TEMPORAL FILE
	SET @ObjectToken = NULL
	EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
	EXEC sp_OASetProperty @ObjectToken, 'Type', 1
	EXEC sp_OAMethod @ObjectToken, 'Open'
	EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @File_BIN
	EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @PATH, 2
	EXEC sp_OAMethod @ObjectToken, 'Close'
	EXEC sp_OADestroy @ObjectToken
	

	SELECT [Path] = @PATH
	,[FileBin] = @File_BIN
	,[Token] = @ObjectToken
	,[FileName] = @File_Name
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

GO
USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[GetCatalogs]    Script Date: 4 oct. 2021 20:52:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[GetCatalogs]
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'


	-----------------------
	-- Query
	-----------------------
	select * from cat.Grades
	where Active = 1

	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

GO

USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[GetEntries]    Script Date: 4 oct. 2021 20:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[GetEntries]
	@search VARCHAR(MAX) = NULL,
	@archived BIT = 0
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @search: ' + ISNULL(CAST(@search AS VARCHAR), '')
		+ ', @archived: ' + ISNULL(CAST(@archived AS VARCHAR), '')

	
	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @search + '%'
	-----------------------
	-- Query
	-----------------------
	select 
	e.*
	,GradeMO =  g.[Name] + '-' + e.MeltOrder --'1289-A26583' -- GradeName + MO
	,[GradeName] = g.[Name]
	,CreationDateString = convert(varchar, IH.ToLocal(CreationDate,'EDTIND') , 120)
	from TWI.Entries e
	left join cat.Grades g on g.Id = e.GradeId
	where 
	
	(@SearchTextString IS NULL
					OR e.[Name] LIKE @SearchTextString 
					OR g.[Name] LIKE @SearchTextString
					OR e.[MeltOrder] LIKE @SearchTextString
					OR e.[Document] LIKE @SearchTextString)
	AND ((@archived = 0 and e.Archived = @archived) 
			OR (@archived = 1 and (e.Archived = 1 OR e.Archived = 0)))
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

GO

USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[UpsertEntry]    Script Date: 4 oct. 2021 20:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[UpsertEntry]
	@Id INT = NULL,
	@Name VARCHAR(50) = NULL,
	@MeltOrder VARCHAR(50) = NULL,
	@GradeId INT = NULL,
	@Archived BIT = NULL,
	@FileB64 NVARCHAR(MAX) = NULL,
	@FileExtension VARCHAR(100) = NULL,
	@FileName VARCHAR(100) = NULL
	AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'

	DECLARE @ModifedId INT = @Id
 
	DECLARE @File_BIN VARBINARY(MAX)
	-----------------------
	-- Query
	-----------------------
	IF (@FileB64 is not null and @FileExtension is not null and @FileName is not null)
	BEGIN
	
		SET @File_BIN = cast(N'' as xml).value('xs:base64Binary(sql:variable("@FileB64"))', 'varbinary(MAX)')

	--	EXEC SYSTEM.ConvertBase64ToBin @dwgB64 = @FileB64, @dwgName = @FileName, @extension= @FileExtension
	END

	DECLARE @FileName_Final VARCHAR(200)


	IF @Id is null
		BEGIN
		
			INSERT INTO TWI.Entries([Name],MeltOrder,GradeId,Archived,CreationDate)
			values(
			@Name
			,@MeltOrder
			,@GradeId
			,@Archived
			,GETUTCDATE())

			SET @ModifedId = @@IDENTITY

			SET @FileName_Final = convert(varchar, getdate(), 23) + REPLACE(convert(varchar, getdate(), 108),':','-') +'-'+ @FileName + '-' + cast(@ModifedId as varchar(20))

			UPDATE TWI.Entries
			set DocumentBin = @File_BIN,
				[Document] = case when @File_BIN is not null then  @FileName_Final else null end,
				[DocumentExtension] = case when @File_BIN is not null then @FileExtension else null end,
				DocumentB64 = case when @FileB64 is not null then @FileB64 else null end
			Where Id = @ModifedId

		END
	ELSE
	BEGIN
		
		SET @FileName_Final = convert(varchar, getdate(), 23) + REPLACE(convert(varchar, getdate(), 108),':','-') +'-'+ @FileName + '-' + cast(@ModifedId as varchar(20))

		UPDATE TWI.Entries
			set 
			[Name] = @Name,
			MeltOrder =  @MeltOrder,
			GradeId = @GradeId,
			Archived = @Archived,
			DocumentBin = @File_BIN,
			[Document] = case when @File_BIN is not null then @FileName_Final else null end,
			[DocumentExtension] = case when @File_BIN is not null then @FileExtension else null end,
			DocumentB64 = case when @FileB64 is not null then @FileB64 else null end
		Where Id = @Id

	END


	--DECLARE @B64 NVARCHAR(MAX) = (select DocumentB64 from twi.Entries where Id = @Id)
	--DECLARE @B64Extension NVARCHAR(MAX) = (select DocumentExtension from twi.Entries where Id = @Id)
	
	--	EXEC SYSTEM.ConvertBase64ToBin @dwgB64 = @B64
	--								  , @dwgName = 'aver'
	--								  , @extension= @B64Extension

	
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG
	
	SELECT [ID] = @ModifedId
			, BIN = @File_BIN
			,  B64 = @FileB64

END



USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[DeleteEntry]    Script Date: 6 oct. 2021 16:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [TWI].[SyncFileSystem]
	AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'


	-----------------------
	-- Query
	-----------------------
	DECLARE @MainFolderPath VARCHAR(MAX) = 'C:\ToFolder\'
	DECLARE @SubFolderName VARCHAR(MAX) = 'tempfiles'	
	DECLARE @SubFolderPath VARCHAR(MAX) = 'C:\ToFolder\' + @SubFolderName + '\'


	DECLARE @FolderAndFile VARCHAR(MAX)	
	----------------------------
	-- MAIN FOLDER FILES 
	----------------------------
		DECLARE @DirMainFolder VARCHAR(600)= 'dir /B "' + @MainFolderPath + '"';
		DECLARE @FilesInMainFolder TABLE ([FileName] VARCHAR(100));
		

		INSERT INTO @FilesInMainFolder
		EXEC xp_cmdshell @DirMainFolder;
		delete from @FilesInMainFolder where [FileName] is null or [FileName] like @SubFolderName
		
		
	-----------------------
	-- SUBFOLDER FILES 
	-----------------------			
			DECLARE @DirSubFolder VARCHAR(600)= 'dir /B "' + @SubFolderPath + '"';
			DECLARE  @FilesInSubfolder TABLE ([FileName] VARCHAR(100));

			INSERT INTO @FilesInSubfolder
			EXEC xp_cmdshell @DirSubFolder;

			delete from @FilesInSubfolder where [FileName] is null		
			
			
	-----------------------
	-- GET FILES IN TWI.Entries
	-----------------------
			-- CURSOR
			DECLARE 
				@twi_file_Id INT,
				@DocumentName VARCHAR(MAX),
				@Archived BIT
			DECLARE twi_cursor CURSOR
			FOR SELECT 
					[Id]
					,[DocumentExtension]
					,[Archived]
				FROM 
					TWI.Entries;

			OPEN twi_cursor;

			FETCH NEXT FROM twi_cursor INTO 
				@twi_file_Id,
				@DocumentName,
				@Archived;

			WHILE @@FETCH_STATUS = 0
				BEGIN
					IF (@Archived = 0)
					BEGIN
						
						
						IF @DocumentName IN (select [FileName] from @FilesInMainFolder)
						BEGIN
							print('NONE')
						END
						ELSE
						BEGIN
							EXEC [TWI].[DownloadFileInPath] @Id =@twi_file_Id , @Path = @MainFolderPath
						END

						IF @DocumentName IN (select [FileName] from @FilesInSubfolder)
						BEGIN
							SET @FolderAndFile = @SubFolderPath + @DocumentName
							PRINT 'DELETE: ' + @FolderAndFile							
							EXEC [TWI].[DeleteFileInPath] @Path = @FolderAndFile
						END
						ELSE
						BEGIN
							print('NONE')
						END

					END
					ELSE IF (@Archived = 1)
					BEGIN

						IF @DocumentName IN (select [FileName] from @FilesInMainFolder)
						BEGIN
							
							SET @FolderAndFile = @MainFolderPath + @DocumentName
							PRINT 'DELETE: ' + @FolderAndFile							
							EXEC [TWI].[DeleteFileInPath] @Path = @FolderAndFile
						END
						ELSE
						BEGIN
							print('NONE')
						END

						IF @DocumentName IN (select [FileName] from @FilesInSubfolder)
						BEGIN
							print('NONE')
						END
						ELSE
						BEGIN
							EXEC [TWI].[DownloadFileInPath] @Id =@twi_file_Id , @Path = @SubFolderPath
						END

					END

					FETCH NEXT FROM twi_cursor INTO 
						@twi_file_Id,
						@DocumentName,
						@Archived;
				END;

			CLOSE twi_cursor;

			DEALLOCATE twi_cursor;

			--
					   			 		  		  		 	   			   	  
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

GO

EXEC [TWI].[SyncFileSystem]

GO







/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------08 October 2021--------------------------------------------------------------*/
ALTER TABLE TWI.Entries
ADD PRIMARY KEY (Id);


create table twi.Files(
	Id int identity(1,1) not null,
	[EntryId] int not null,
	[DocumentName] varchar(500) not null,
	[DocumentBin] varbinary(MAX) not null,
	[Datetime] datetime not null,
	[OriginalDocumentName] varchar(500) not null,
	[Author] varchar(100) null,
	FOREIGN KEY (EntryId) REFERENCES TWI.Entries(Id)
)


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.GetFiles' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.GetFiles' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.UploadFile' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.UploadFile' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO

USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[GetFiles]    Script Date: 10 oct. 2021 20:13:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[GetFiles]
	@EntryId int = NULL
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
	
	
	-----------------------
	-- Query
	-----------------------



	select * from twi.Files
	where EntryId = @EntryId
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO


USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[UploadFile]    Script Date: 10 oct. 2021 20:12:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[UploadFile]
	@EntryId int = NULL,
	@FileB64 NVARCHAR(MAX) = NULL,
	@FileName VARCHAR(100) = NULL,
	@CurrentUser VARCHAR(50) -- auto included by 4i
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
	
	
	DECLARE @File_BIN VARBINARY(MAX)
	
	DECLARE @FileName_Final VARCHAR(500)
	-----------------------
	-- Query
	-----------------------
	IF (@FileB64 is not null and @FileName is not null)
	BEGIN	
		SET @File_BIN = cast(N'' as xml).value('xs:base64Binary(sql:variable("@FileB64"))', 'varbinary(MAX)')
	END

	SET @FileName_Final = replace(convert(varchar, getdate(),23),'-','') + REPLACE(convert(varchar, getdate(), 108),':','') + @FileName --YYYYMMDDHHMM+ filename 

	insert into twi.Files(EntryId,DocumentName,DocumentBin,[Datetime],OriginalDocumentName,Author)
	values(
		@EntryId
		,@FileName_Final
		,@File_BIN
		,GETUTCDATE()
		,@FileName
		,@CurrentUser
	)

	select Id = @@IDENTITY
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END





/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------09 October 2021--------------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[GetEntries]    Script Date: 11 oct. 2021 18:17:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[GetEntries]
	@search VARCHAR(MAX) = NULL,
	@archived BIT = 0,
	@GradeId INT = NULL,
	@ProcessId INT = NULL,
	@LayoutProcesses XML = NULL
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @search: ' + ISNULL(CAST(@search AS VARCHAR), '')
		+ ', @archived: ' + ISNULL(CAST(@archived AS VARCHAR), '')

	
	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @search + '%'

	
	DECLARE @layoutT TABLE([ProcessId] INT)


	INSERT INTO @layoutT
		(
			[ProcessId]
		)
		SELECT
			[ProcessId] =  T.Variable.value('@value','INT')
		FROM
		@LayoutProcesses.nodes('XMLRoot/Row') AS T(Variable)

	-----------------------
	-- Query
	-----------------------
	select 
		e.*
		,[GradeName] = g.[Name]
		,[ProcessName] = p.[Name]
		,CreationDateString = convert(varchar, IH.ToLocal(CreationDate,'EDTIND') , 120)
		,EndDateSring = convert(varchar, IH.ToLocal(EndDate,'EDTIND') , 120)
	from TWI.Entries e
	left join cat.Grades g on g.Id = e.GradeId
	left join twi.CatProcesses p on p.Id = e.ProcessId
	where 
	--(@ProcessId IS NULL OR p.Id = @ProcessId) AND
	((select count(*) from @layoutT) = 0 OR p.Id in (select [ProcessId] from @layoutT) ) AND
	(@GradeId IS NULL OR g.Id = @GradeId) AND
	(@SearchTextString IS NULL
					OR e.[Name] LIKE @SearchTextString 
					OR g.[Name] LIKE @SearchTextString
					OR e.[MeltOrder] LIKE @SearchTextString
					OR e.[FileName] LIKE @SearchTextString)
	AND ((@archived = 0 and e.Archived = @archived) 
			OR (@archived = 1 and (e.Archived = 1 OR e.Archived = 0)))
	ORDER BY Archived asc


	--select * from twi.Files
	select * from @layoutT
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
GO



ALTER TABLE TWI.Entries
ADD [Processes] varchar(MAX) null;
GO

ALTER TABLE TWI.Entries
ADD [GradeIds] varchar(MAX) null;
GO

ALTER DATABASE [MES] SET COMPATIBILITY_LEVEL = 130

GO


ALTER FUNCTION [TWI].[GetProcessNames]
(
	@intArray varchar(200)
)
RETURNS VARCHAR(500)
AS
BEGIN

	DECLARE @ProcessNamesTable TABLE ([Name] varchar(100))
	DECLARE @ProcessNames VARCHAR(500)

	insert into @ProcessNamesTable
	SELECT [Name]
	FROM TWI.CatProcesses
	WHERE Id IN (select cast([value] as int) from STRING_SPLIT(@intArray, ','))

	
	SELECT @ProcessNames = COALESCE(@ProcessNames + ',', '') + cast([Name] as varchar(10)) 
	FROM @ProcessNamesTable
	

	RETURN @ProcessNames
END

GO



/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------10 October 2021--------------------------------------------------------------*/
ALTER TABLE TWI.Entries
DROP COLUMN StartDate;


ALTER PROCEDURE [TWI].[GetGradesByGradeGroup]
	@LayoutGradeGroups XML = NULL
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'


	-----------------------
	-- Query
	-----------------------

	DECLARE @layoutT TABLE([GradeGroupId] INT)


	INSERT INTO @layoutT
		(
			[GradeGroupId]
		)
	SELECT
		[GradeGroupId] =  T.Variable.value('@value','INT')
	FROM
	@LayoutGradeGroups.nodes('XMLRoot/Row') AS T(Variable)


	select 
	g.Id
	,g.[Name]
	,[GradeGroupDescription] = gg.[Name]
	from cat.Grades g
	left join cat.GradeGroups gg on gg.Id = g.GradeGroupId
	where g.Active = 1 and 
	((select count(*) from @layoutT) = 0 OR GradeGroupId in (select [GradeGroupId] from @layoutT) )
	order by GradeGroupId asc
	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END

GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_read] -- The role
,@name = N'securedsp-TWI.GetGradesByGradeGroup' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [twimanagement_readWrite] -- The role
,@name = N'securedsp-TWI.GetGradesByGradeGroup' -- the procedure
,@value = N'' 
GO



ALTER TABLE TWI.CatProcesses
ADD PRIMARY KEY (ID);

create table twi.EntriesProcesses
(
	EntryId int,
	ProcessId int,
	--Primary key doble 
	-- 2 Foreing Key a Entries and Processes
	 CONSTRAINT PK_EntriesProcesses PRIMARY KEY NONCLUSTERED ([EntryId], [ProcessId]),
	 CONSTRAINT FK_EntriesProcesses_EntryId   FOREIGN KEY (EntryId) REFERENCES TWI.Entries(id),
     CONSTRAINT FK_EntriesProcesses_ProcessId FOREIGN KEY (ProcessId) REFERENCES TWI.CatProcesses(id),
)


create table twi.EntriesGrades
(
	EntryId int,
	GradeId int,
	 CONSTRAINT PK_EntriesGrades PRIMARY KEY NONCLUSTERED ([EntryId], GradeId),
	 CONSTRAINT FK_EntriesGrades_EntryId   FOREIGN KEY (EntryId) REFERENCES TWI.Entries(id),
     CONSTRAINT FK_EntriesGrades_GradeId FOREIGN KEY (GradeId) REFERENCES CAT.Grades(id),
	
)



create table twi.EntriesSizes
(
	EntryId int,
	SizeId int,
	 CONSTRAINT PK_EntriesSizes PRIMARY KEY NONCLUSTERED ([EntryId], SizeId),
	 CONSTRAINT FK_EntriesSizes_EntryId   FOREIGN KEY (EntryId) REFERENCES TWI.Entries(id),
     CONSTRAINT FK_EntriesSizes_SizeId    FOREIGN KEY (SizeId) REFERENCES cat.ProductSizes(id), --IMPORTANT: CHANGE THIS TABLE
	
)





/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------13 October 2021--------------------------------------------------------------*/


create table CAT.ProductSizes(
	Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL
	,[Name] varchar(100)
)

insert into CAT.ProductSizes
select distinct(MaterialSize) from LAD.MeltOrders




/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/
/*---------------------------------------------------------------18 October 2021--------------------------------------------------------------*/

update IHBoxSystem.[FrontEnd].Routes 
	set Route = '!/twi-management(/:Ids)(/)'
where id = 4797



USE [MES]
GO
/****** Object:  StoredProcedure [TWI].[GetEntries]    Script Date: 18 oct. 2021 08:16:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TWI].[GetEntries]
	@search VARCHAR(MAX) = NULL,
	@archived BIT = 0,
	@GradeId INT = NULL,
	@ProcessId INT = NULL,
	@LayoutProcesses XML = NULL,	
	@LayoutGrades XML = NULL,	
	@LayoutSizes XML = NULL,
	@LayoutIds XML = NULL,
	@fromRow INT = NULL, -- From what row to start fetching from
	@toRow INT = NULL, -- To row (@toRow row num not included in result data)
	@rowsToFetch INT = NULL -- Amount of rows to fetch
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)


	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @search: ' + ISNULL(CAST(@search AS VARCHAR), '')
		+ ', @archived: ' + ISNULL(CAST(@archived AS VARCHAR), '')

	-----------------------
	-- Parameter defaults
	-----------------------
	IF (@rowsToFetch IS NULL)
	SET @rowsToFetch = -1
	
	IF (@rowsToFetch > 0)
	BEGIN
	IF (@fromRow IS NULL)
	SET @fromRow = 1
	
	IF (@toRow IS NULL)
	SET @toRow = @fromRow + @rowsToFetch
	END

	DECLARE @SearchTextString VARCHAR(100)

	SET @searchTextString = '%' + @search + '%'

	-------------------------------------
	---- Ids convertion to Table
	--------------------------------------
	DECLARE @layoutTIds TABLE([Id] INT)

	INSERT INTO @layoutTIds([Id])
	SELECT
		[Id] =  T.Variable.value('@Id','INT')
	FROM
	@LayoutIds.nodes('XMLRoot/Row') AS T(Variable)



	-------------------------------------
	---- Processes convertion to Table
	--------------------------------------
	DECLARE @layoutTProcesses TABLE([ProcessId] INT)

	INSERT INTO @layoutTProcesses([ProcessId])
		SELECT
			[ProcessId] =  T.Variable.value('@value','INT')
		FROM
		@LayoutProcesses.nodes('XMLRoot/Row') AS T(Variable)

	-------------------------------------
	---- Grades convertion to Table
	--------------------------------------
	DECLARE @layoutTGrades TABLE([GradeId] INT)

	INSERT INTO @layoutTGrades([GradeId])
		SELECT
			[GradeId] =  T.Variable.value('@value','INT')
		FROM
		@LayoutGrades.nodes('XMLRoot/Row') AS T(Variable)

	-------------------------------------
	---- Sizes convertion to Table
	--------------------------------------
	DECLARE @layoutTSizes TABLE([SizeId] INT)

	INSERT INTO @layoutTSizes([SizeId])
		SELECT
			[SizeId] =  T.Variable.value('@value','INT')
		FROM
		@LayoutSizes.nodes('XMLRoot/Row') AS T(Variable)


	-----------------------
	-- Query
	-----------------------
	select 
		e.*
		,[ProcessNames] = left([TWI].[GetMultiselectNamesByType](e.Id,'PO'),100)
		,[GradeNames] =  (select  left([TWI].[GetMultiselectNamesByType](e.Id,'GR'),100) )
		,[SizeNames] =    left([TWI].[GetMultiselectNamesByType](e.Id,'SI'),100)
		,[ProcessIds] = [TWI].[GetMultiselectIdsByType](e.Id,'PO') 
		,[GradeIds] =   [TWI].[GetMultiselectIdsByType](e.Id,'GR') 
		,[SizeIds] =    [TWI].[GetMultiselectIdsByType](e.Id,'SI')
		,EndDateSring = convert(varchar, EndDate , 23)
	into #temp1
	from TWI.Entries e
	where 		
	
	(select count(*) from @layoutTIds) = 0 
	 AND
	(
	

			--Processes
			(
				(select count(*) from @layoutTProcesses) = 0 OR 
				e.Id in (select distinct(z.EntryId) from twi.EntriesProcesses z where z.ProcessId in (select ProcessId from @layoutTProcesses))
	
			) AND	
			--Grades
			(
				(select count(*) from @layoutTGrades) = 0 OR 
				e.Id in (select distinct(z.EntryId) from twi.EntriesGrades z where z.GradeId in (select GradeId from @layoutTGrades))
	
			) AND
			--Sizes
			(
				(select count(*) from @layoutTSizes) = 0 OR 
				e.Id in (select distinct(z.EntryId) from twi.EntriesSizes z where z.SizeId in (select SizeId from @layoutTSizes))
	
			) AND


			(@SearchTextString IS NULL
							OR e.[MeltOrder] LIKE @SearchTextString
							OR e.[Name] LIKE @SearchTextString)
			AND ((@archived = 0 and e.Archived = @archived) 
					OR (@archived = 1 and (e.Archived = 1 OR e.Archived = 0)))
	) 
	OR e.Id in (select Id from @layoutTIds)
	ORDER BY CreationDate desc, Archived asc


	SELECT 
	t1.*,
	RowNum = ROW_NUMBER() OVER (ORDER BY t1.[CreationDate] DESC)
	INTO #temp2
	FROM #temp1 t1

	SELECT
	*
	FROM #temp2 t2
	WHERE
	(@rowsToFetch = -1 OR (t2.RowNum BETWEEN @fromRow AND  @toRow))
		ORDER BY t2.CreationDate DESC
		,t2.Archived asc
	   	 

	-----------------------
	-- Finish procedure
	-----------------------
	PRINT @finishEndMSG

	RETURN 0
END
