*==================================================================== * RPG ILE HSSFCGI/GUB * * CRTBNDRPG PGM(HSSFCGI/GUB) DFTACTGRP(*NO) ACTGRP(*NEW) DBGVIEW(*SOURCE) * *==================================================================== /copy HSSFCGI/qrpglesrc,hspecs /copy HSSFCGI/qrpglesrc,hspecsbnd FGUB if e k disk usropn F extfile('HSSFCGI/GUB') /copy HSSFCGI/qrpglesrc,prototypeb /copy HSSFCGI/qrpglesrc,hssfcgix_p /copy HSSFCGI/qrpglesrc,usec D rc s 10i 0 D extHtml s 2000 inz('/hssfcgi/html/- D XLSGEN_template.txt') * Indicators for GetHtmlIfsMult subprocedure D IfsMultIndicators... D ds D NoErrors n D NameTooLong n D NotAccessible n D NoFilesUsable n D DupSections n D FileIsEmpty n D bookInd s n D sheetInd s n D lineFeed s 3 inz('%LF') D cell_width s 5i 0 inz(200) D row_height s 5i 0 D data s 1000 D tax s 15 D xmlStmf s 100 inz('/hssfcgi/tmp/gub.txt') D xlsStmf s 100 inz('/hssfcgi/tmp/gub.xls') D stmf800 s 800 varying D CodePage s 10i 0 inz(819) D Timestamp s z D MonthNbrC s 2 D Monthname s 15 D port s 10 D ContentType s 100 D EOR s 2 inz(x'0d25') * variables for procedure SndPgmMsg D pgmq s 5 D msgid s 7 D msgf s 10 D msgflib s 10 D msgdta s 512 D getMonthName pr 15 D monthnbrc 2 *==================================================================== * Main line *==================================================================== /free // Establish the row height and cell width to avoid distorsion // (in the original boat images height=width*0.632) row_height=(cell_width*0.632) * 0.75; // Set no Debug for max performance setNoDebug(*on); // Open file, if needed exsr OpnF; // Load external XML-like script, suited for XLSGEN command IfsMultIndicators = getHtmlIfsMult(%trim(exthtml):''); // Retrieve current month name timestamp=%timestamp(); monthNbrC=%subst(%char(timestamp):6:2); monthName=getMonthName(monthNbrC); // Reset indicators bookInd=*off; sheetInd=*off; // Read the motor boat records, generate the output XML in the output buffer gubtype='M'; exsr ReadBoats; // Read the sail boat records, generate the output XML in the output buffer gubtype='S'; exsr ReadBoats; // End the workbook if bookInd=*on; wrtsection('endbook'); bookInd=*off; endif; // Write the output buffer to an XLM stream file rc = WrtHtmlToStmf(%trim(xmlStmf):CodePage); callp ClrHtmlBuffer(); rc = docmd('chgaut ''' + %trim(xmlStmf) + ''' USER(*PUBLIC) DTAAUT(*RWX) OBJAUT(*ALL)'); // Use command HSSFCGI/XLSGEN to generate the XLS stream file rc = docmd('del ''' + %trim(xlsStmf) + ''''); rc = docmd('hssfcgi/xlsgen inpstmf(''' + %trim(xmlStmf) + ''') outstmf(''' + %trim(xlsStmf) + ''') frozen(2) + sheetsize(2300000 2)'); if rc<>0; msgdta='Program HSSFCGI/GUB failed creating an Excel workbook. + See previous joblog messages.'; callp sndpgmmsg('*PRV':'GUB':'CPF9897':'QCPFMSG':'QSYS': '*INFO':msgdta); msgdta='HSSFCGI installation was NOT validated.'; callp sndpgmmsg('*PRV':'GUB':'CPF9897':'QCPFMSG':'QSYS': '*ESCAPE':msgdta); endif; // Check if stream file "xlsStmf" ('/hssfcgi/tmp/gub.xls') was generated if not ChkIfsObj2(%trim(xlsStmf)); msgdta='HSSFCGI installation was NOT validated.'; callp sndpgmmsg('*PRV':'GUB':'CPF9897':'QCPFMSG':'QSYS': '*ESCAPE':msgdta); return; endif; rc = docmd('chgaut ''' + %trim(xlsStmf) + ''' USER(*PUBLIC) DTAAUT(*RWX) OBJAUT(*ALL)'); // If a CGI job, read and send the XLS stream file to the browser port=rtvenvvar('SERVER_PORT':qusec); if port<>' '; stmf800=%trim(xlsStmf); callp SndStmfToBrowser(stmf800:'XLS'); //subprocedure of srvpgm HSSFCGI/HSSFCGIX else; msgdta='Program HSSFCGI/GUB executed successfully.'; callp sndpgmmsg('*PRV':'GUB':'CPF9897':'QCPFMSG':'QSYS': '*INFO':msgdta); msgdta='HSSFCGI installation was validated.'; callp sndpgmmsg('*PRV':'GUB':'CPF9897':'QCPFMSG':'QSYS': '*INFO':msgdta); endif; // Back to caller return; /end-free *==================================================================== * Read boat records of a given type, * generate the output XML in the output buffer *==================================================================== /free begsr ReadBoats; setll gubtype gubrcd; reade gubtype gubrcd; dow not %eof; //Start workbook, if not yet done if bookInd=*off; wrtsection('startbook'); bookInd=*on; endif; //Start worksheet, if not yet done if sheetInd=*off; updhtmlvar('sheetname':'GU'+gubtype+'B'); updhtmlvar('worksheetkeywords': 'print-header="Giovanni''s Used Boats" + print-orientation="portrait" + print-scale="110"'); wrtsection('startsheet'); sheetInd=*on; exsr WriteTit; //write title row exsr WriteSubT; //write subtitle row endif; //Write boat row exsr WriteBoatR; //Read the next record reade gubtype gubrcd; enddo; // End the current worksheet wrtsection('endsheet'); sheetInd=*off; endsr; /end-free *==================================================================== * Write worksheet title row * Example: * * * * GUMB - Giovanni's Used Motor Boats * * * *==================================================================== /free begsr WriteTit; updhtmlvar('rowkeywords':'row-height="40"'); wrtsection('startrow'); updhtmlvar('cellkeywords':'hspan="2"'); wrtsection('startcell'); updhtmlvar('datatype':'LargeTextC'); updhtmlvar('datakeywords': 'valign="center" f-color="white" b-color="dark_blue"'); wrtsection('startdata'); select; when gubtype='M'; updhtmlvar('data': 'GUMB - Giovanni''s Used Motor Boats'); when gubtype='S'; updhtmlvar('data': 'GUSB - Giovanni''s Used Sail Boats'); endsl; wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); wrtsection('endrow'); endsr; /end-free *==================================================================== * Write worksheet subtitle row * Example: * * * * in April 2009 * * * *==================================================================== /free begsr WriteSubT; updhtmlvar('rowkeywords':'row-height="25"'); wrtsection('startrow'); updhtmlvar('cellkeywords':'hspan="2"'); wrtsection('startcell'); updhtmlvar('datatype':'SmallTextC'); updhtmlvar('datakeywords': 'valign="center" f-color="indigo" b-color="pale_blue"'); wrtsection('startdata'); updhtmlvar('data': 'in ' + %trim(monthName) + ' ' + %subst(%char(timestamp):1:4)); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); wrtsection('endrow'); endsr; /end-free *==================================================================== * Write boat row * Example: * * ... first cell ... * ... second cell ... * *==================================================================== /free begsr WriteBoatR; updhtmlvar('rowkeywords': 'row-height="' + %trim(%char(row_height)) + '"'); wrtsection('startrow'); //Write the first cell, containing a picture exsr WriteCell1; //Second cell, containing description exsr WriteCell2; updhtmlvar('rowkeywords':' '); wrtsection('endrow'); endsr; /end-free *==================================================================== * Write the first cell of the boat row (a picture) * Example: * * * /hssfcgi/images/boatM01.jpg * * *==================================================================== /free begsr WriteCell1; updhtmlvar('cellkeywords':'border'); wrtsection('startcell'); updhtmlvar('datatype':'picture'); updhtmlvar('datakeywords': 'width="' + %trim(%char(cell_width)) + '"'); wrtsection('startdata'); updhtmlvar('data': '/hssfcgi/images/boat' + gubtype + %trim(gubid) + '.jpg'); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); endsr; /end-free *==================================================================== * Write the second cell of the boat row (some description) * Example: * * * %LF36' Solent Spear%LF Year 2005%LF Current price: Tax Paid%LF Located in ... * * *==================================================================== /free begsr WriteCell2; updhtmlvar('cellkeywords':'border'); wrtsection('startcell'); updhtmlvar('datatype':'text'); if gubtype='M'; updhtmlvar('datakeywords': 'valign="center" + f-color="dark_blue" b-color="lemon_chiffon"'); else; updhtmlvar('datakeywords': 'valign="center" + f-color="teal" b-color="light_yellow"'); endif; wrtsection('startdata'); if gubpricea='Y'; tax='Tax Paid'; else; tax='Tax Not Paid'; endif; data=linefeed + %trim(gubmake) + linefeed + ' Year ' + %trim(gubyear) + linefeed + ' Current price: ' + %trim(gubpricec) + ' ' + %trim(%editc(gubprice:'J')) + ' ' + %trim(tax) + linefeed + ' Located in ' + %trim(gubloc) + linefeed + ' Hull Material: ' + %trim(gubhull) + linefeed + ' Engine/Fuel Type: ' + %trim(gubengine) + linefeed; updhtmlvar2('data':%addr(data): %len(%trim(data))); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); endsr; /end-free *==================================================================== * Open file, if needed *==================================================================== /free begsr OpnF; if not %open(GUB); open GUB; endif; endsr; /end-free *==================================================================== * "getMonthName" subprocedure - Convert month number to month name *==================================================================== P GetMonthName b D GetMonthName pi 15 D myMonthNbr 2 D myMonthName s 15 /free select; when myMonthNbr='01'; myMonthName='January'; when myMonthNbr='02'; myMonthName='February'; when myMonthNbr='03'; myMonthName='March'; when myMonthNbr='04'; myMonthName='April'; when myMonthNbr='05'; myMonthName='May'; when myMonthNbr='06'; myMonthName='June'; when myMonthNbr='07'; myMonthName='July'; when myMonthNbr='08'; myMonthName='August'; when myMonthNbr='09'; myMonthName='September'; when myMonthNbr='10'; myMonthName='October'; when myMonthNbr='11'; myMonthName='November'; when myMonthNbr='12'; myMonthName='December'; endsl; return myMonthName; /end-free P e