EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

GRANT EXECUTE ON xp_cmdshell TO [Domain\GroupOrUser]

EXEC sp_xp_cmdshell_proxy_account 'Domain\ServiceAccount', 'ServiceAcountPassword'

IF OBJECT_ID (N'[dbo].[uFnBase64ToString]', N'FN') IS NOT NULL  
    DROP FUNCTION uFnBase64ToString;  
GO

CREATE FUNCTION [dbo].[uFnBase64ToString] ( @Input_Base64_Value VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN (
		SELECT
			CAST(
                CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Input_Base64_Value"))', 'VARBINARY(MAX)') 
            AS VARCHAR(MAX)
            ) AS RetVal
    )
END
GO

CREATE TABLE [dbo].[images]
(
	[ImageID] [bigint] PRIMARY KEY IDENTITY(1,1) NOT NULL,
	[Filename] [varchar](100) NOT NULL,
	[PhotoData] [varbinary](max) NOT NULL,
	[CustomOrder] [int] NULL,
	[IsPrimary] [bit] NOT NULL DEFAULT 1,
	[IsActive] [bit] NOT NULL DEFAULT 1,
	[DateCreated] [datetime] NOT NULL DEFAULT (GETDATE())
);

CREATE VIEW [dbo].[vImages] as
SELECT	[ImageID] as [Image ID],
		[Filename] as [Filename],
		CAST(CONCAT('data:image/jpeg;base64,',dbo.uFnBase64ToString([PhotoData])) as varchar(max)) as [Photo Data],
		[IsPrimary] as [Is Primary],
		[CustomOrder] as [Custom Order]
FROM [dbo].[images]
WHERE IsActive=1

CREATE PROCEDURE [dbo].[ImportImages] 
AS
BEGIN
	SET NOCOUNT ON;
	SET TEXTSIZE -1;

 	-- Declarations
	DECLARE @RowNum bigint;
	DECLARE @Counter bigint;
	DECLARE @ImageFolderPath varchar(1000);
	DECLARE @Filename varchar(1000);
	DECLARE @Path2OutFile varchar(2000);
	DECLARE @tsql varchar(2000);

	-- Create a temporary table to list all of the files in the drop folder
	IF OBJECT_ID('tempdb..#ImageList', 'U') IS NOT NULL DROP TABLE #ImageList;
	CREATE TABLE #ImageList (
       ID bigint IDENTITY(1,1) PRIMARY KEY,
	   ImageFileName varchar(4000)
	);

	INSERT INTO #ImageList (ImageFileName)
	EXEC xp_cmdshell 'dir /B "C:\Data\Images"';
	DELETE FROM #ImageList WHERE ImageFileName IS NULL;

	SET @RowNum = (SELECT COUNT(1) as RowNum FROM #ImageList);
	SET @Counter = 1;
	SET @ImageFolderPath = 'C:\Data\Images';

	-- Reset Stored Data Table
	TRUNCATE TABLE dbo.images;

	-- Process Files
	WHILE (@Counter<=@RowNum)
	BEGIN
		SET @Filename = (SELECT ImageFileName FROM #ImageList WHERE [ID]=@Counter);
		SET @Path2OutFile = CONCAT (@ImageFolderPath,'\',@Filename);

		-- Build script and execute   -- Build script and execute
		SET @tsql = 'INSERT INTO dbo.images (Filename,PhotoData) ' +
               'SELECT ' +
					'''' + @Filename + '''' + ', * ' + 
               'FROM OPENROWSET( BULK ' + '''' + @Path2OutFile + '''' + ', SINGLE_BLOB) as IMG'
		EXEC (@tsql);
        SET @Counter = @Counter + 1;
	END

	-- Make inactive invalid filetypes
	UPDATE CI
	SET CI.IsActive=0
	FROM dbo.images CI
	WHERE UPPER(RIGHT(CI.[Filename],3)) NOT IN ('jpg','png')
	;

END