Re: create XML from RPG read as EXCEL
that code is just the heading to the XML
to get the fields in a file you need this--or-- something like it
( i made this a procedure so it only needs to be written once)
I would recommend you do the same.
that code is just the heading to the XML
to get the fields in a file you need this--or-- something like it
( i made this a procedure so it only needs to be written once)
I would recommend you do the same.
Code:
*-------------------------------------------------------- * PROGRAM - MSC52 * PURPOSE - Generic send email attachement program * WRITTEN - january 17th 2008 * AUTHOR - jamie flanary * PROGRAM DESCRIPTION * Generic send email attachment program * * INDICATOR USAGE *-------------------------------------------------------- * * To Compile: * CRTSQLRPGI OBJ(JAMIELIB/MSC52) SRCFILE(JAMIELIB/SOURCE) * SRCMBR(MSC52) OBJTYPE(*MODULE) * * CRTPGM PGM(lbiobj/MSC52) * MODULE(lbiobj/MSC52) * BNDSRVPGM(QTCP/QTMMSNDM) * * H BNDDIR('QC2LE':'BASE64') OPTION(*SRCSTMT:*NOSHOWCPY) fqaokl02a if e k disk prefix(q_) fqatmsmtpa if e k disk prefix(q_) // // *entry plist // d MSC52 pr d intype 3 d intoname 35 d intoaddress 35 d insubject 50 d inlibFile 21 d inattachname 35 d inbody 3000 varying d inspoolf 10 d MSC52 pi d intype 3 d intoname 35 d intoaddress 35 d insubject 50 d inlibFile 21 d inattachname 35 d inbody 3000 varying d inspoolf 10 D AR S 1 DIM(9999) D AKEY S 10 DIM(9999) D AK# S 2 0 DIM(9999) D J S 2 0 D JJ S 2 0 D I S 7 0 D S S 7 0 D RelRecNbr S 4 0 D XX S 7 0 D I1 S 1 D I2 S 2 D pFileLib S 20 D FileLib s 20 inz(*blanks) d headerstring s 3000 varying D InputLib s 10 inz(*blanks) D InputName s 10 inz(*blanks) D InFormat S 10 D EntryFmt S 10 inz('*FIRST') D NbrofField S 04 0 D OutField S 10 D OutSize S 15 5 D OutDecimal S 01 D OutType2 S 01 * D GENDS DS D OffsetHdr 117 120B 0 D SizeHeader 121 124B 0 D OffsetList 125 128B 0 D NbrInList 133 136B 0 D SizeEntry 137 140B 0 D HeaderDs DS D OutFileNam 1 10 D OutLibName 11 20 D OutType 21 25 D OutFormat 31 40 D RecordLen 41 44B 0 D InputDs DS D UserSpace 1 20 D SpaceName 1 10 D SpaceLib 11 20 D InpFileLib 29 48 D InpFFilNam 29 38 D InpFFilLib 39 48 D InpRcdFmt 49 58 D ListDs DS D FieldName 1 10 D FieldType 11 11 D BufferOut 13 16B 0 D FieldLen 21 24B 0 D Digits 25 28B 0 D Decimals 29 32B 0 D FieldDesc 33 82 D ErrorDs DS INZ D BytesPrv 1 4B 0 D BytesAvl 5 8B 0 D MessageId 9 15 D ERR### 16 16 D MessageDta 17 116 D ReceiveVr2 S 100 D ReceiveVar DS 4096 D NbrOfFmts 62 63B 0 D DBFileOff 317 320B 0 D FindSelDs DS 150 D NbrOfKeys 117 118B 0 D KeyOffset 136 139B 0 D KeyDataDs DS D DependKey 1 10 D DS D StartPosit 1 4B 0 D StartLen 5 8B 0 D SpaceLen 9 12B 0 D ReceiveLen 13 16B 0 D MessageKey 17 20B 0 D MsgDtaLen 21 24B 0 D MsgQueNbr 25 28B 0 DGenSpcPtr * DLstSpcPtr * DHdrPtr * // // Program Info // d SDS d @PGM 001 010 d @PARMS 037 039 0 d @MSGDTA 91 170 d @MSGID 171 174 d @JOB 244 253 d @USER 254 263 d @JOB# 264 269 0 /copy qprcsrc,attach_cp // start of sendmail copybook d QtmmSendMail PR ExtProc('QtmmSendMail') d FileName 255A const options(*varsize) d FileNameLen 10I 0 const d MsgFrom 256A const options(*varsize) d MsgFromLen 10I 0 const d RecipBuf likeds(ADDTO0100) d dim(32767) d options(*varsize) d NumRecips 10I 0 const d ErrorCode 8000A options(*varsize) d ADDTO0100 ds qualified d based(Template) d NextOffset 10I 0 d AddrLen 10I 0 d AddrFormat 8A d DistType 10I 0 d Reserved 10I 0 d SmtpAddr 256A d ADDR_NORMAL C CONST(0) d ADDR_CC C CONST(1) d ADDR_BCC C CONST(2) // end of sendmail copybook /copy qpgmsrc,base64_h d QCMDEXC PR ExtPgm('QCMDEXC') d cmd 32702A const options(*varsize) d len 15P 5 const d tmpnam PR * extproc('_C_IFS_tmpnam') d string 39A options(*omit) d MailDate PR 31A d CR c x'0d' d CRLF c x'0d25' d count s 4 0 d fromName s 100A varying d fromAddr s 300A varying d length s 4 0 d lines s 4 0 d toName s 100A varying d toAddr s 300A varying d subject s 80A varying d str s 4 0 d attFile s 21A varying d attName s 50A varying d filename s 50A varying d tempAttach s 50A varying d att s 10I 0 d fd s 10I 0 d SQ s 1 inz('''') d header s 2000A varying d body s 500A varying d cmd s 500A varying d boundary s 78A varying d x s 10I 0 d data s 54A d encData s 74A d encLen s 10i 0 d len s 10i 0 d recip ds likeds(ADDTO0100) d dim(1) d NullError ds d BytesProv 10I 0 inz(0) d BytesAvail 10I 0 inz(0) d wait s 1A /free // // intype // intoname // intoaddress // insubject // inlibFile // inattachname // fromName = %trim(@user); // use the from userid to get the email address clear FromAddr; chain (@user) QAOKL02A; if %found(QAOKL02A); chain (q_WOS1DDEN) qtmsmpr; if %found; fromAddr = %trim(Q_smtpuid) + '@'; if %subst(Q_DOMROUTE:1:5) = 'MAIL.'; fromAddr = %trim(fromAddr) + %trim(%subst(Q_DOMROUTE:6:100)); else; fromAddr = %trim(fromAddr) + %trim(Q_DOMROUTE); endif; endif; endif; toName = %trim(intoname); toAddr = %trim(intoaddress); subject = %trim(insubject); attFile = %trim(inlibfile); // ------------------------------------------ // Convert the PF to a format that PCs like // a Comma Separated Values (CSV) file // ------------------------------------------ select; when intype = 'CSV'; attname = %trim(inattachname) + '.csv'; exsr $getfields; tempAttach = %str(tmpnam(*omit)); unlink(tempAttach); // headings first cmd = 'CPYTOIMPF FROMFILE(QTEMP/temphead) ' + 'TOSTMF(''' + tempAttach +''') ' + 'MBROPT(*REPLACE) DTAFMT(*FIXED) ' + 'STMFCODPAG(*PCASCII) ' + 'RCDDLM(*CRLF)'; QCMDEXC( cmd : %len(cmd)); cmd = 'CPYTOIMPF FROMFILE(' + attFile + ') ' + 'TOSTMF(''' + tempAttach +''') ' + 'MBROPT(*ADD) ' + 'STMFCODPAG(*PCASCII) ' + 'RCDDLM(*CRLF)'; when intype = 'PDF'; attname = %trim(inattachname) + '.pdf'; tempAttach = '/home/'; // // CVTSPLF FROMFILE(@NORTHAP) TOSTMF(CRACK.PDF) // TODIR('/home/jamie') SPLNBR(*LAST) TOFMT(*PDF) // STMFOPT(*REPLACE) // // cmd = 'CVTSPLF FROMFILE(' + %trim(inspoolf ) + ')' + ' TOSTMF(' + %trim(inattachname) + '.pdf) ' + 'TODIR(' + SQ + %trim(tempAttach) + SQ + ') ' + 'SPLNBR( *LAST) ' + 'TOFMT(*PDF) STMFOPT(*REPLACE)'; tempAttach = '/home/' + %trim(inattachname) + '.pdf'; endsl; QCMDEXC( cmd : %len(cmd)); // ------------------------------------------ // create a temporary file in the IFS. // mark that file as ccsid 819 (ISO 8859-1 ASCII) // ------------------------------------------ filename = %str(tmpnam(*omit)); unlink(filename); fd = open( filename : O_CREAT+O_EXCL+O_WRONLY+O_CCSID : M_RDWR : 819 ); if (fd = -1); endif; // ------------------------------------------ // close file & reopen in text mode so that // data will be automatically translated // ------------------------------------------ callp close(fd); fd = open( filename : O_WRONLY + O_TEXTDATA ); if (fd = -1); endif; // ------------------------------------------ // build an e-mail header in a variable // ------------------------------------------ Boundary = '-----------------------'; header = 'From: ' + fromName + ' <' + fromAddr + '>' + CRLF +'To: ' + toName + ' <' + toAddr + '>' + CRLF +'Date: ' + maildate() + CRLF +'Subject: ' + subject + CRLF +'MIME-Version: 1.0' + CRLF +'Content-Type: multipart/mixed;' + ' boundary="' + Boundary + '"' + CRLF + CRLF + 'Your mail reader doesn''t support MIME!' + CRLF + CRLF; callp write(fd: %addr(header)+2: %len(header)); // ------------------------------------------ // Insert the headers for the text part // ------------------------------------------ body = '--' + boundary + CRLF +'Content-type: text/plain' + CRLF + CRLF; callp write(fd: %addr(body)+2: %len(body)); // ------------------------------------------ // Set up the body from passed in data // ------------------------------------------ lines = %len(%trim(inbody))/100; if %rem(%len(%trim(inbody)):100) > *zeros; lines +=1; endif; str = 1; for count = 1 to lines; length = ( %len(%trim(inbody)) - str)+1; if length > 100; length = 100; endif; body = %subst(inbody:str:length) + CRLF; callp write(fd: %addr(body)+2: %len(body)); str += length; endfor; // ------------------------------------------ // Insert the headers for the CSV file // ------------------------------------------ body = '--' + boundary + CRLF +'Content-Type: text/csv; name="' + attname + '"' + CRLF +'Content-Transfer-Encoding: base64' + CRLF +'Content-Disposition: attachment;' + ' filename="' + attname + '"' + CRLF + CRLF; callp write(fd: %addr(body)+2: %len(body)); // ------------------------------------------ // Read the attachment file, and base64 // encode it. Write the results to the // e-mail message. // ------------------------------------------ att = open( tempAttach: O_RDONLY ); if (att = -1); endif; unlink(tempAttach); dow '1'; len = read(att: %addr(data): %size(data)); if (len < 1); leave; endif; enclen = base64_encode( %addr(data) : len : %addr(encdata) : %size(encdata)-2 ); %subst(encdata:enclen+1) = CRLF; callp write(fd: %addr(encdata): enclen+2); enddo; callp close(att); // ------------------------------------------ // Finish the message & close the file // ------------------------------------------ body = CRLF + '--' + boundary + '--' + CRLF; callp write(fd: %addr(body)+2: %len(body)); callp close(fd); // ------------------------------------------ // Use the QtmmSendMail() API to send the // IFS file via SMTP // ------------------------------------------ recip(1).NextOffset = %size(ADDTO0100); recip(1).AddrFormat = 'ADDR0100'; recip(1).DistType = ADDR_NORMAL; recip(1).Reserved = 0; recip(1).SmtpAddr = toAddr; recip(1).AddrLen = %len(toAddr); QtmmSendMail( FileName : %len(FileName) : fromAddr : %len(fromAddr) : recip : %elem(recip) : NullError ); *inlr = *on; /end-free *--------------------------------------------------- * $getfields - get all the field names *--------------------------------------------------- c $getfields begsr * c eval SpaceName = 'FFDSPC' c eval SpaceLib = 'QTEMP' * * QTEMP/PCH49EXP * c eval InpFFilNam = %subst(inlibFile : c %scan('/': inlibfile)+1) c eval InpFFilLib = %subst(inlibFile : 1: c %scan('/': inlibfile)-1) c eval filelib = InpFFilNam + InpFFilLib * * Create the user space * c call 'QUSCRTUS' c parm UserSpace c parm *BLANKS SpaceAttr 10 c parm 4096 SpaceLen c parm *BLANKS Spaceval 1 c parm '*CHANGE' SpaceAuth 10 c parm *BLANKS SpaceText 50 c parm '*YES' SpaceRepl 10 c parm ErrorDs * * Attemp to retrieve object description * c call(e) 'QUSROBJD' c parm ReceiveVr2 c parm 100 ReceiveLen c parm 'OBJD0100' FileFormat 8 c parm FileLib c parm '*FILE' ObjectType 10 c parm ErrorDs * c if not%error * * List fields to user space * c call 'QUSLFLD' c parm UserSpace c parm 'FLDL0100' ListFormat 8 c parm InpFileLib c parm EntryFmt InpRcdFmt c parm '1' OverRide 1 c eval StartPosit = 1 c eval StartLen = 140 * c call 'QUSRTVUS' c parm UserSpace c parm StartPosit c parm StartLen c parm GENDS c eval StartPosit = OffsetHdr + 1 c eval StartLen = SizeHeader * c call 'QUSRTVUS' c parm UserSpace c parm StartPosit c parm StartLen c parm HeaderDs * c eval StartPosit = OffsetList + 1 c eval StartLen = SizeEntry c eval SpaceName = 'FFDSPC' c eval SpaceLib = 'QTEMP' * * read thru fields to build header * c clear headerstring c do NbrInList NbrOfField c call 'QUSRTVUS' c parm UserSpace c parm StartPosit c parm StartLen c parm ListDs c if headerstring = *blanks c eval headerstring += '"' + %trim(fielddesc) + '"' c else c eval headerstring += ',' + '"' + %trim(fielddesc) c + '"' c endif c eval StartPosit = StartPosit + SizeEntry c enddo * * create the file with headers first...... * c eval headerstring += ',""' * c/exec sql c+ drop table qtemp/temphead c/End-Exec c/exec sql c+ create table qtemp/temphead c+ (data char(512) ) c/End-Exec c/exec sql c+ insert into qtemp/temphead VALUES( c+ : headerstring c+ ) c/end-exec c endif c endsr *--------------------------------------------------- *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * MailDate(): Returns the current date, formatted for use * in an e-mail message. * * For example: 'Sat, 23 Oct 2004 14:42:06 -0500' *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P MailDate B D MailDate PI 31A D CEELOCT PR opdesc D Lilian 10I 0 D Seconds 8F D Gregorian 23A D fc 12A options(*omit) D CEEUTCO PR opdesc D Hours 10I 0 D Minutes 10I 0 D Seconds 8F D fc 12A options(*omit) D CEEDATM PR opdesc D input_secs 8F const D date_format 80A const options(*varsize) D char_date 80A options(*varsize) D feedback 12A options(*omit) D rfc2822 c 'Www, DD Mmm YYYY HH:MI:SS' D junk1 s 8F D junk2 s 10I 0 D junk3 s 23A D hours s 10I 0 D mins s 10I 0 D tz_hours s 2P 0 D tz_mins s 2P 0 D tz s 5A varying D CurTime s 8F D Temp s 25A /free // // Calculate the Timezone in format '+0000', for example // CST should show up as '-0600' // CEEUTCO(hours: mins: junk1: *omit); tz_hours = %abs(hours); tz_mins = mins; if (hours < 0); tz = '-'; else; tz = '+'; endif; tz += %editc(tz_hours:'X') + %editc(tz_mins:'X'); // // Get the current time and convert it to the format // specified for e-mail in RFC 2822 // CEELOCT(junk2: CurTime: junk3: *omit); CEEDATM(CurTime: rfc2822: Temp: *omit); return Temp + ' ' + tz; /end-free P E
Comment