IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLPages'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.CreateHTMLPages
    IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLPages'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.CreateHTMLPages >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.CreateHTMLPages >>>'
END
go
CREATE PROCEDURE CreateHTMLPages (
        @dontgen bit = 1,
        @dirmask varchar(30) = NULL
)
--WITH ENCRYPTION
AS
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    17 Jan 2000                                             */
/*      Description:    This SP is used to create the HTML pages for my web site*/
/*                      using the Web Assistant job with a custom FrontPage     */
/*                      template file. The directories are automatically created*/
/*                      under the web-sites, source code is automatically zipped*/
/*                      or rather a CMD script created to run the zip commands. */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
DECLARE @dir varchar(255), @relpath varchar(75), @destdir varchar(255),
        @zipcmd varchar(255), @mydocdir varchar(75), @myhtmlfile varchar(255),
        @mytplfile varchar(255), @mywebdir varchar(75), @LastUpdated varchar(255),
        @querystr varchar(8000), @cmdstr nvarchar(4000), @blobfmt varchar(8000)
SELECT @mydocdir = REPLACE(REPLACE(Value, '%s', ''), '"', '')
FROM Settings
WHERE Setting = 'My Documents'

SELECT @mywebdir = Value
FROM Settings
WHERE Setting = 'FrontPage Web Directory'

SELECT @mywebdir = @mywebdir + Value
FROM Settings
WHERE Setting = 'Resources HTML Directory'

SELECT @zipcmd = Value
FROM Settings
WHERE Setting = 'Zip Command-line'

SELECT  @destdir = '', @mytplfile = @mydocdir + @mywebdir + 'Resource.Tpl',
        @dirmask = COALESCE(@dirmask, '%')

EXEC sp_fileop;1 'runzips.cmd', '@echo off'

DECLARE dirs CURSOR FOR
SELECT Directory, SUBSTRING(Relativepath, 3, LEN(Relativepath) -1)
FROM [My Resource Files] 
WHERE PATINDEX('<<%>>', Filename) > 0 And Directory LIKE @dirmask
ORDER BY RelativePath, [FileName]

OPEN dirs
WHILE(1=1)
BEGIN
        FETCH dirs INTO @dir, @relpath
        IF @@FETCH_STATUS < 0 BREAK

        SELECT @destdir = @mydocdir + @mywebdir + REPLACE(REPLACE(
                                REPLACE(@relpath, SPACE(1), ''), '.', ''), '-', ''),
                @cmdstr = 'md "' + @destdir + '"'
        EXEC master..xp_cmdshell @cmdstr, no_output

        SELECT @cmdstr = REPLACE(REPLACE(@zipcmd, '%s1', @destdir + '\Source.Zip'),
                                        '%s2', @dir + '\*')
        EXEC sp_fileop;2 'runzips.cmd', @cmdstr

        SELECT @myhtmlfile = @destdir + '\Main.htm',
                @querystr =
'SELECT replace(replace([filename], ''<'', ''''), ''>'', '''')
FROM [My Resource Files]
WHERE patindex(''<<%>>'', [filename]) > 0 And directory = ''' + @dir + '''
SELECT Description
FROM [My Resource Files]
WHERE patindex(''<<%>>'', [filename]) > 0 And directory = ''' + @dir + '''
SELECT Contents
FROM [My Resource Files]
WHERE patindex(''<<%>>'', [filename]) > 0 And directory = ''' + @dir + '''
SELECT convert(varchar, (select count(*) from [My Resource Files] r2
                        where r2.RelativePath = r1.RelativePath And
                                r2.[Create] <= r1.[Create] And
                                patindex(''<<%>>'', [Filename]) = 0)) + '') ''
        + 
        CAST(Description AS varchar(8000)),
        Contents, [Filename]
FROM [My Resource Files] r1
WHERE patindex(''<<%>>'', [Filename]) = 0 And directory = ''' + @dir + '''
order by [Create]',
                @blobfmt = '%2% FILE=' + @myhtmlfile+ ' TPLT=' +
                                @destdir + '\Resource.Tpl URL=Main.htm'
        IF @dontgen = 1
                EXEC(@querystr)
        ELSE
                EXECUTE sp_makewebtask @outputfile = @myhtmlfile, @query = @querystr,
                                        @templatefile = @mytplfile,
                                        @dbname = 'My Personal Db',
                                        @blobfmt = @blobfmt
END
DEALLOCATE dirs
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLPages'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.CreateHTMLPages To Public
    PRINT '<<< CREATED PROCEDURE dbo.CreateHTMLPages >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.CreateHTMLPages >>>'
go

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