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  :	http://www.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