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  :    https://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

This page was last updated on May 01, 2006 04:28 PM.