Property	Value
Object Type	Server
Short Description	Last Changed: 12-07-2004
Full Description	Job: GenHtmlFromSql ------------------------------------------------------------------------------------- Non-technical description: Generate Html from a SQL statement. ------------------------------------------------------------------------------------- Business rules: tbd ------------------------------------------------------------------------------------- Rules for restarting this job: Just rerun. ------------------------------------------------------------------------------------- Parameters: 1. FilePath 2. LogoPath 3. FileName 4. SqlStmt 5. SqlDsn 6. SqlUser 7. SqlPwd 8. DebugSw 9. EmailFlag 10. EmailTo 11. MsgJobName 12. EmailHeader ------------------------------------------------------------------------------------- Sources: SQL ------------------------------------------------------------------------------------- Targets: Sql2Html/FilePath ------------------------------------------------------------------------------------- Modification History: ------------------------------------------------------------------------------------- UserId Date Version Modification Description ------------------------------------------------------------------------------------- kduke 12-07-2004 1 Created job. -------------------------------------------------------------------------------------
Category	EtlStats
Multiple Instance	True
Job Version Number	40.0.0
Hash File Cache Sharing	False
Job Control	* ----------------------------------------------------------------- * Job: GenHtmlFromSql * ----------------------------------------------------------------- * Description: * Generate Html from a SQL statement. * ------------------------------------------------------------ * Parameters: * 1. FilePath = ./ * 2. LogoPath = OurLogo.jpg * 3. FileName = DsJobs * 4. SqlStmt = SELECT * FROM DS_JOBS WHERE NAME LIKE 'Kgd%'; * 5. SqlDsn = localuv * 6. SqlUser = null * 7. SqlPwd = null * 8. DebugSw = 1 (1 prints SQL) * 9. EmailFlag = 1 * 10. EmailTo = xxx (use EmailToRtn) * 11. MsgJobName = GenHtmlFromSql * 12. EmailHeader = Sql Results * ----------------------------------------------------------------- * Created by: Kim Duke November 29, 2004 * ----------------------------------------------------------------- * $INCLUDE DSINCLUDE JOBCONTROL.H * ----------------------------------------------------------------- DEFFUN GenHtmlHeader(ObjectName,Title,LogoPath) CALLING "DSU.GenHtmlHeader" DEFFUN GenHtmlDiv(DivName,DivDesc,IndentLevel) CALLING "DSU.GenHtmlDiv" DEFFUN GenHtmlTableHeader(ColNames,ColWidths,ColJust,IndentLevel) CALLING "DSU.GenHtmlTableHeader" DEFFUN GenHtmlTableRows(ColValues,ColJust,NoOfCols) CALLING "DSU.GenHtmlTableRows" DEFFUN GenHtmlFooter(NotUsed) CALLING "DSU.GenHtmlFooter" DEFFUN GenHtmlFromSqlRtn(SqlStmt,SqlDsn,SqlUser,SqlPwd,IndentLevel) CALLING "DSU.GenHtmlFromSqlRtn" DEFFUN EmailToRtn(JobCategory) CALLING "DSU.EmailToRtn" DEFFUN GetJobCategory(JobName) CALLING "DSU.GetJobCategory" * ----------------------------------------------------------------- JobName = "GenHtmlFromSql" Title = 'Generate Html from a Sql statement' Now = oconv(@DATE, "D4-YMD[4,2,2]"):' ':oconv(@TIME, "MTHS") ThisDay = convert('-','',oconv(@DATE, "D4-YMD[4,2,2]")) NotUsed = '' * DebugSw = @TRUE * parameter now LogoPath = "dcibanner.jpg" If System(91) = 0 Then Shell = "UNIX" Sep = '/' OtherSep = '\' End Else Shell = "DOS" Sep = '\' OtherSep = '/' End convert OtherSep to Sep in LogoPath convert OtherSep to Sep in FilePath HtmlPath = "Sql2Html" * :Sep:ThisDay DirName = HtmlPath open HtmlPath to HtmlDir else Cmd = 'CREATE.FILE Sql2Html 19' execute Cmd capturing output open HtmlPath to HtmlDir else Call DSLogInfo("Error: Unable to open " : HtmlPath, JobName) goto TheEnd end end Call DSLogInfo("Writing Sql Html to " : HtmlPath, JobName) RecCnt = 0 Call DSLogInfo('GenHtmlHeader(':FileName:',':Title:',':LogoPath:')', JobName) HtmlDoc = GenHtmlHeader(FileName,Title,LogoPath) * ----------------------------------------------------------------- * start sqlstmt division * ----------------------------------------------------------------- if DebugSw then DivName = "sqlstmt" DivDesc = "Sql Statement" IndentLevel = 0 Call DSLogInfo('GenHtmlDiv(':DivName:',':DivDesc:',':IndentLevel:')', JobName) GenHtml = GenHtmlDiv(DivName,DivDesc,IndentLevel) HtmlDoc := @FM:GenHtml HtmlDoc<-1> = ' ' * ----------------------------------------------------------------- * start sqlstmt table * ----------------------------------------------------------------- ColNames = 'Sql' ColWidths = '100' ColJust = '' IndentLevel = 1 GenHtml = GenHtmlTableHeader(ColNames, ColWidths, ColJust, IndentLevel) HtmlDoc := @FM:GenHtml ColValues = change(SqlStmt, char(13):char(10), @VM) NumCols = 1 GenHtml = GenHtmlTableRows(ColValues, ColJust, NumCols) HtmlDoc := @FM:GenHtml * ----------------------------------------------------------------- * close table and divisions * ----------------------------------------------------------------- HtmlDoc<-1> = '' HtmlDoc<-1> = ' ' end * ----------------------------------------------------------------- * start sqlinfo division * ----------------------------------------------------------------- DivName = "sqlinfo" DivDesc = "Sql Results" IndentLevel = 0 Call DSLogInfo('GenHtmlDiv(':DivName:',':DivDesc:',':IndentLevel:')', JobName) GenHtml = GenHtmlDiv(DivName,DivDesc,IndentLevel) HtmlDoc := @FM:GenHtml HtmlDoc<-1> = ' ' * ----------------------------------------------------------------- * start sqlinfo table * ----------------------------------------------------------------- IndentLevel = 1 Call DSLogInfo('GenHtmlFromSqlRtn(':SqlStmt:',':SqlDsn:',':SqlUser:',':SqlPwd:',':IndentLevel')', JobName) GenHtml = GenHtmlFromSqlRtn(SqlStmt,SqlDsn,SqlUser,SqlPwd,IndentLevel) HtmlDoc := @FM:GenHtml * ----------------------------------------------------------------- * close table and divisions * ----------------------------------------------------------------- HtmlDoc<-1> = '' HtmlDoc<-1> = ' ' * ----------------------------------------------------------------- * footer * ----------------------------------------------------------------- Call DSLogInfo('GenHtmlFooter(':NotUsed:')', JobName) GenHtml = GenHtmlFooter(NotUsed) HtmlDoc := @FM:GenHtml * ----------------------------------------------------------------- * write documents to disk * ----------------------------------------------------------------- HtmlId = FileName:'.html' Call DSLogInfo("Writing " : HtmlId :" to " : HtmlPath, JobName) write HtmlDoc on HtmlDir, HtmlId else Call DSLogInfo("Error: Unable to write to " : HtmlPath, JobName) goto TheEnd end * EmailFlag = @TRUE if EmailFlag then xEmailTo = EmailTo if EmailTo = '' or index('All,Kim,Ron,Sharon',EmailTo,1)=0 then JobCategory = GetJobCategory(MsgJobName) xEmailTo = EmailToRtn(JobCategory) end Cmd = 'SH Scripts':Sep:'Email.ksh ':xEmailTo:' ':MsgJobName Cmd := ' ':HtmlPath:Sep:HtmlId Cmd := ' ':HtmlPath:Sep:LogoPath:' ':EmailHeader Call DSLogInfo("Execute: " : Cmd, JobName) execute Cmd capturing output returning RtnCode Call DSLogInfo("Command Results: " : output, JobName) end * ----------------------------------------------------------------- * exit program here * ----------------------------------------------------------------- TheEnd:

Parameter Name	Prompt	Default Value	Help Text
FilePath	FilePath	./
LogoPath	LogoPath	OurLogo.jpg
FileName	FileName	DsJobs
SqlStmt	SqlStmt	SELECT * FROM DS_JOBS WHERE NAME LIKE 'Kgd%';
SqlDsn	SqlDsn	localuv
SqlUser	SqlUser
SqlPwd	SqlPwd
DebugSw	DebugSw	1	1 or 0
EmailFlag	EmailFlag	1
EmailTo	EmailTo	xxx
MsgJobName	JobName in message	GenHtmlFromSql
EmailHeader	EmailHeader

- Source Stages (0)  + Source Stages (0)

- Processing Stages (0)  + Processing Stages (0)

- Target Stages (0)  + Target Stages (0)