IF OBJECTPROPERTY(OBJECT_ID('dbo.CheckFilesForHTMLGeneration'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.CheckFilesForHTMLGeneration
    IF OBJECTPROPERTY(OBJECT_ID('dbo.CheckFilesForHTMLGeneration'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.CheckFilesForHTMLGeneration >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.CheckFilesForHTMLGeneration >>>'
END
go
CREATE PROCEDURE CheckFilesForHTMLGeneration (
        @JustCheck bit = 1
)
--WITH ENCRYPTION
AS
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	23 Jan 2000						*/
/*	Description:	This SP is used to check for any new files or deleted   */
/*			for which the HTML content needs to be generated.	*/
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
-- Keep temporary tables DDLs together to avoid recompilation
SELECT Directory , RelativePath , [Filename] , [Create] , Write
INTO #MovedFiles FROM [My Resource Files]
WHERE 1 = 0
SELECT Directory , RelativePath , [Filename] , [Create] , Write
INTO #Files FROM [My Resource Files]
WHERE 1 = 0
ALTER TABLE #Files ADD DEFAULT ('.') FOR Directory

DECLARE @dirmask varchar(100), @mydocdir varchar(255),
	@cmdstr varchar(255), @dir varchar(500),
	@skipfiles nvarchar(255), @backuptablename varchar(255)
SELECT @mydocdir = REPLACE(Value, '\%s', '')
FROM Settings
WHERE Setting = 'My Documents'

SELECT @dirmask = Value
FROM Settings
WHERE Setting = 'Exclude Directories'

SELECT @skipfiles = Value
FROM Settings
WHERE Setting = 'Exclude Files'

SELECT	@dir = '',
	@cmdstr = 'dir /ad/b/s ' + @mydocdir + '|findstr /I /V ' + @dirmask
-- BACKUP before truncating...
IF @JustCheck = 0
BEGIN
	SELECT @backuptablename = 'My Resource Files Backup (' +
					convert(varchar, current_timestamp, 120) + ')'
	EXEC('SELECT * INTO [' + @backuptablename + '] FROM [My Resource Files]')
END
INSERT #Files (Directory)
EXEC master..xp_cmdshell @cmdstr

WHILE(@dir IS NOT NULL)
BEGIN
	SELECT @dir = MIN(Directory) FROM #Files WHERE Directory > @dir
	SELECT @cmdstr = 'dir /b/a-d "' + @dir + '"'

	INSERT #Files ([Filename]) EXEC master..xp_cmdshell @cmdstr
	
	UPDATE #Files SET Directory = @dir WHERE Directory = '.'
END
DELETE #Files
WHERE CHARINDEX('~' + [FileName] + '~', @skipfiles) > 0

UPDATE #Files
SET RelativePath = REPLACE(Directory, REPLACE(@mydocdir, '"', ''), '.')

UPDATE #Files
SET [FileName] = '<< ' + REVERSE(LEFT(REVERSE(RelativePath),
				CHARINDEX('\', REVERSE(RelativePath)) - 1)) + ' >>'
WHERE [FileName] IS NULL

UPDATE f
SET f.[Create] = w.[Create], f.[Write] = w.[Write]
FROM #Files f JOIN WebTechFiles w
ON w.[Filename] = f.[Filename]

BEGIN TRAN
IF @JustCheck = 0
        UPDATE r
        SET r.Directory = f.Directory, r.RelativePath = f.RelativePath,
	    r.[Create] = f.[Create], r.[Write] = f.[Write]
        FROM [My Resource Files] r JOIN #Files f
        ON r.[Filename] = f.[Filename]
        WHERE r.Directory <> f.Directory
ELSE
        SELECT CAST(f.RelativePath AS varchar(30)) AS RelativePath ,
               f.[Filename] AS [Moved File]
        FROM [My Resource Files] r JOIN #Files f
        ON r.[Filename] = f.[Filename]
        WHERE r.Directory <> f.Directory And
              ( SELECT COUNT( * ) FROM [My Resource Files] r1
                WHERE r1.[Filename] = r.[Filename] ) = 1
              
IF @JustCheck = 0
	INSERT [My Resource Files]
	SELECT * FROM #Files f
	WHERE NOT EXISTS(SELECT * FROM [My Resource Files] r
			WHERE r.Directory + '\' + r.[Filename] = f.Directory + '\' + f.[Filename])
ELSE
	SELECT CAST(f.RelativePath AS varchar(30)) AS RelativePath, f.[Filename] AS [New File]
	FROM #Files f
	WHERE NOT EXISTS( SELECT * FROM [My Resource Files] r
			  WHERE r.Directory + '\' + r.[Filename] = f.Directory + '\' + f.[Filename] And
                                NOT ( r.[FileName] = f.[FileName] And
                                      r.Directory <> f.Directory ) )

IF @JustCheck = 1
	SELECT CAST(f.RelativePath AS varchar(30)) AS RelativePath,
		f.[Filename] AS [Modified File]
	FROM #Files f
	WHERE EXISTS(SELECT * FROM [My Resource Files] r
			WHERE r.Directory + '\' + r.[Filename] =
					f.Directory + '\' + f.[Filename] And
				DATEDIFF( mi , r.Write , f.Write) > 0) And
		PATINDEX('<<%>>', f.[Filename]) = 0

IF @JustCheck = 0
	DELETE r
	FROM [My Resource Files] r
	WHERE NOT EXISTS(SELECT * FROM #Files f
			WHERE r.Directory + '\' + r.[Filename] = f.Directory + '\' + f.[Filename])
ELSE
	SELECT CAST(r.RelativePath AS varchar(30)) AS RelativePath,
		r.[Filename] AS [Deleted File]
	FROM [My Resource Files] r
	WHERE NOT EXISTS(SELECT * FROM #Files f
			WHERE r.Directory + '\' + r.[Filename] = f.Directory + '\' + f.[Filename])
COMMIT
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.CheckFilesForHTMLGeneration'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.CheckFilesForHTMLGeneration TO PUBLIC
    PRINT '<<< CREATED PROCEDURE dbo.CheckFilesForHTMLGeneration >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.CheckFilesForHTMLGeneration >>>'
GO