*==================================================================== * RPG ILE HSSFCGI/FAMACC * * CRTBNDRPG HSSFCGI/FAMACC DFTACTGRP(*NO) ACTGRP(*NEW) DBGVIEW(*SOURCE) * *==================================================================== /copy HSSFCGI/qrpglesrc,hspecs /copy HSSFCGI/qrpglesrc,hspecsbnd FFAMACC if e k disk usropn F extfile('HSSFCGI/FAMACC') /copy HSSFCGI/qrpglesrc,prototypeb /copy HSSFCGI/qrpglesrc,hssfcgix_p /copy HSSFCGI/qrpglesrc,usec D ColHeading pr D heading 50 const varying 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 rowNumber 10i 0 D fammonthC s 2 D famdayC s 2 D data s 1000 D famExpTypeN s 10i 0 D dayExpTypeTot s 15p 0 D dayTotal s 15p 0 D xmlStmf s 100 inz('/hssfcgi/tmp/famacc.txt') D xlsStmf s 100 inz('/hssfcgi/tmp/famacc.xls') D stmf800 s 800 varying D CodePage s 10i 0 inz(819) D MonthNbrC s 2 D port s 10 D ContentType s 100 * variables for procedure SndPgmMsg D pgmq s 5 D msgid s 7 D msgf s 10 D msgflib s 10 D msgdta s 512 *==================================================================== C famkey klist C kfld famyear C kfld fammonth C kfld famday C kfld famexptype *==================================================================== * Main line *==================================================================== /free // 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):''); // The row number must be the same as shown on the spreadsheet rowNumber=0; // Start the workbook wrtsection('startbook'); // Start a worksheet exsr StrWorksheet; // Write title row exsr WriteTitle; // Write column headings row exsr WriteColHeads; // Write a row for each day of the month exsr ScanDays; // Test special formula exsr SpecialForm; // End current worksheet wrtsection('endsheet'); // End the workbook wrtsection('endbook'); // Write the output buffer to an XLM stream file rc = WrtHtmlToStmf(%trim(xmlStmf):CodePage); rc = docmd('chgaut ''' + %trim(xmlStmf) + ''' USER(*PUBLIC) DTAAUT(*RWX) OBJAUT(*ALL)'); callp ClrHtmlBuffer(); // Use command HSSFCGI/XLSGEN to generate the XLS stream file rc = docmd('hssfcgi/xlsgen inpstmf(''' + %trim(xmlStmf) + ''') outstmf(''' + %trim(xlsStmf) + ''') + frozen(2) + hdgcolor(SEA_GREEN)'); 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=%trimr(xlsStmf); SndStmfToBrowser(stmf800:'XLS'); //subprocedure of srvpgm HSSFCGI/HSSFCGIX else; msgdta='Program HSSFCGI/FAMACC executed successfully.'; sndpgmmsg('*PRV':'FAMACC':'CPF9897':'QCPFMSG':'QSYS': '*INFO':msgdta); msgdta='HSSFCGI installation was validated.'; sndpgmmsg('*PRV':'FAMACC':'CPF9897':'QCPFMSG':'QSYS': '*INFO':msgdta); endif; // Back to caller *inlr=*on; return; /end-free *==================================================================== * Start a worksheet *==================================================================== /free begsr StrWorkSheet; famyear=2010; fammonth=5; updhtmlvar('sheetname':%char(famyear) + '-0' + %char(fammonth)); updhtmlvar('worksheetkeywords': 'print-header="family accounting" + print-orientation="landscape" + print-scale="80"'); wrtsection('startsheet'); endsr; /end-free *==================================================================== * Write title row *==================================================================== /free begsr WriteTitle; updhtmlvar('rowkeywords':'row-height="60"'); wrtsection('startrow'); rowNumber+=1; //Number or rows so far updhtmlvar('cellkeywords':'hspan="11"'); wrtsection('startcell'); updhtmlvar('datatype':'LargeTextC'); updhtmlvar('datakeywords':'valign="center" + b-color="Lavender"'); wrtsection('startdata'); updhtmlvar('data':'FAMILY ACCOUNTING'); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); wrtsection('endrow'); updhtmlvar('rowkeywords':' '); endsr; /end-free *==================================================================== * Write column headings row *==================================================================== /free begsr WriteColHeads; updhtmlvar('cellkeywords':' '); wrtsection('startrow'); rowNumber+=1; //Number or rows so far colHeading('Day'); colHeading('Food'); colHeading('Clothes'); colHeading('Medical'); colHeading('Image'); colHeading('House'); colHeading('Car'); colHeading('Vacations'); colHeading('Extras'); colHeading('Taxes'); colHeading('Day total'); wrtsection('endrow'); endsr; /end-free *==================================================================== * Scan all days of May 2010 *==================================================================== /free begsr ScanDays; famday=1; dou famday>31; wrtsection('startrow'); rowNumber+=1; //Number or rows so far exsr ScanExp; wrtsection('endrow'); famday=famday+1; enddo; //Column totals wrtsection('startrow'); rowNumber+=1; //Number or rows so far colHeading('Month total'); for famexptypeN=1 to 9; updhtmlvar('cellkeywords':'cell-width="70"'); wrtsection('startcell'); updhtmlvar('datatype':'Formula'); updhtmlvar('datakeywords':'dec-pos="0"'); wrtsection('startdata'); data='VSUM noclear'; updhtmlvar('data':data); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); endfor; //Total spent in this month updhtmlvar('cellkeywords':'cell-width="70"'); wrtsection('startcell'); updhtmlvar('datatype':'Formula'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data='SUM(B3:J33)'; updhtmlvar('data':data); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); wrtsection('endrow'); //Column averages wrtsection('startrow'); rowNumber+=1; //Number or rows so far colHeading('Daily average'); for famexptypeN=1 to 9; updhtmlvar('cellkeywords':'cell-width="70"'); wrtsection('startcell'); updhtmlvar('datatype':'Formula'); updhtmlvar('datakeywords':'dec-pos="0"'); wrtsection('startdata'); data='VAVG'; updhtmlvar('data':data); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); endfor; //Average money spent in one day updhtmlvar('cellkeywords':'cell-width="70"'); wrtsection('startcell'); updhtmlvar('datatype':'Formula'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data='SUM(B' + %trim(%editc(rowNumber:'3')) + ':J' + %trim(%editc(rowNumber:'3'))+ ')'; updhtmlvar('data':data); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); wrtsection('endrow'); endsr; /end-free *==================================================================== * Scan the expenses of a given day *==================================================================== /free begsr ScanExp; //write day wrtsection('startcell'); updhtmlvar('datatype':'text'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); fammonthC=%char(fammonth); if %len(%trim(fammonthC))=1; fammonthC='0'+%trim(fammonthC); endif; famdayC=%char(famday); if %len(%trim(famdayC))=1; famdayC='0'+%trim(famdayC); endif; data=%char(famyear) + '-' + fammonthC + '-' + famdayC; updhtmlvar('data':data); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); //Nine expense columns famexptypeN=1; dou famexpTypeN>9; famexpType=%char(famexptypeN); exsr ScoreExpType; wrtsection('startcell'); updhtmlvar('datatype':'number'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data=%editc(dayExpTypeTot:'J'); updhtmlvar('data':data); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); famexptypeN=famexptypeN+1; enddo; //Day total wrtsection('startcell'); updhtmlvar('datatype':'Formula'); updhtmlvar('datakeywords':'dec-pos="0"'); wrtsection('startdata'); data='HSUM'; updhtmlvar('data':data); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); endsr; /end-free *==================================================================== * Score the expenses for a given expense-type in a given day *==================================================================== /free begsr ScoreExpType; dayExpTypeTot=0; setll famkey famrcd; reade famkey famrcd; dow not %eof; dayExpTypeTot=dayExpTypeTot+famamt; reade famkey famrcd; enddo; endsr; /end-free *==================================================================== * Test special formulas *==================================================================== /free begsr SpecialForm; wrtsection('startrow'); colHeading('Special Formulas -->'); //Formula B3+B4+B5-B6 wrtsection('startcell'); updhtmlvar('datatype':'Formula'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data='B3+B4+B5-B6'; updhtmlvar('data':data); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); wrtsection('endrow'); endsr; /end-free *==================================================================== * Open file, if needed *==================================================================== /free begsr OpnF; if not %open(FAMACC); open FAMACC; endif; endsr; /end-free *==================================================================== * Write a column heading *==================================================================== P ColHeading b D ColHeading pi D heading 50 const varying /free wrtsection('startcell'); updhtmlvar('datatype':'Column-Header'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); updhtmlvar('data':heading); wrtsection('data'); wrtsection('enddata'); wrtsection('endcell'); /end-free P ColHeading e