then just drop on my desktop.....
Tweet
Free Format with SQLRPG
**********************************************************************
* Program: EPOPOLOCHK *
* Text: Price discrepancy issues *
* Author: Mike Haston *
* Date created: November 2004 *
* *
* *
**********************************************************************
* compile options *
**********************************************************************
h bnddir( 'QC2LE' : 'JPLPGM/JPL_UTIL' )
h dftactgrp( *no )
h debug option( *srcstmt : *nodebugio )
**********************************************************************
* file specifications *
**********************************************************************
fEDIPRT10 o f 198 printer oflind(*inof) usropn
**********************************************************************
* prototype definitions for procedures *
**********************************************************************
* to call cl commands
d qcmdexc pr 10i 0 extproc( 'system' )
d command * value options( *string )
* date procedure prototypes
d/copy acscpy,dateproto
* create sql tables
d #createTables pr
* drop sql tables
d #dropTables pr
* gather voided records from epopolo file
d #getVoidedRecords...
d pr
* get price details by upc from epopolo file
d #getPriceChangeDetails...
d pr
d upc 12a
* get first record from table1
d #getTable1Record...
d pr
* get next record from table1
d #getNextTable1Record...
d pr
* get first record from table2
d #getTable2Record...
d pr
* get next record from table2
d #getNextTable2Record...
d pr
* send email report
d #sendEmail pr
**********************************************************************
* standalone variable, arrays, data structures *
**********************************************************************
* global variables - contains PSDS with spool file info
d/copy acscpy,globalvars
d acct# s 5a
d apos c x'7D'
d color s 3a
d count s 10i 0
d createReport s n
d current s 8s 0
d division s 2a
d folder s 10a varying
d heldUPC s 12a
d heldRetail s 7s 2
d heldPrice s 7s 2
d i s 10i 0
d lastPrint s 8s 0
d message s 256a varying
d newP c ':/P'
d oneYearOld s 8s 0
d price s 7s 2
d po# s 22a
d rcvDate s 8s 0
d reportTitle s 40a
d retail s 7s 2
d season s 1a
d size s 3a
d status s 1a
d subject s 60a varying
d style s 12a
d todaysDate s 35a
d toFile s 100a varying
d totalPrice s 10s 2
d totalRetail s 10s 2
d upc s 12a
d usaTime s t timfmt(*usa)
d voidDate s 8s 0
d x s 10i 0
* data array
d ds
d data dim( 300 )
d pacct# 5a overlay( data )
d pupc 12a overlay( data : *next )
d pvoidDate 10a overlay( data : *next )
d pstatus 1a overlay( data : *next )
d pdivision 2a overlay( data : *next )
d pseason 1a overlay( data : *next )
d pstyle 12a overlay( data : *next )
d pcolor 3a overlay( data : *next )
d psize 3a overlay( data : *next )
d pretail 7s 2 overlay( data : *next )
d pprice 7s 2 overlay( data : *next )
d ppo# 22a overlay( data : *next )
d prcvDate 10a overlay( data : *next )
d pLastPrint 10a overlay( data : *next )
/free
// setup email variables
subject = 'FOJ Price Difference Notification';
message = 'A price discrepancy has been detected. Please '
+ 'review the attached report. Do not reply to this '
+ 'email as it has been automatically generated.';
// report & spool file info
reportTitle = 'FOJ Price Difference Notification';
tofile = '/qdls/reports/epopolo.pdf';
todaysDate = #dayName( %date() ) + ', ' + #completeDate( %date() );
usaTime = %time();
current = %int( %char( %date() : *iso0 ) );
// ** testing **current = 20040703;
oneYearOld = %int( %char( %date() - %years(1) : *iso0 ) );
// override printer file
qcmdexc( 'OVRPRTF FILE(EDIPRT10) OUTQ(EDIERROR) HOLD(*YES)' );
open EDIPRT10;
*inof = *on;
// create temporary tables in qtemp
#createTables();
// get voided polo records for current day from epopolo file
#getVoidedRecords();
// get price information by upc from epopolo file
#getTable1Record();
dow sqlcod = 0;
#getPriceChangeDetails( upc );
#getNextTable1Record();
enddo;
// roll thru table2 which contains all information by upc
// place info in data array
#getTable2Record();
dow sqlcod = 0;
if upc = heldUPC;
i += 1;
pacct#(i) = acct#;
pupc(i) = upc;
pstatus(i) = status;
pdivision(i) = division;
pseason(i) = season;
pstyle(i) = style;
pcolor(i) = color;
psize(i) = size;
pretail(i) = retail;
totalRetail += retail;
pprice(i) = price;
totalPrice += price;
ppo#(i) = po#;
if voidDate > 0;
pvoidDate(i) = %char( %date( voidDate : *iso ) : *usa );
endif;
if rcvDate > 0;
prcvDate(i) = %char( %date( rcvDate : *iso ) : *usa );
endif;
if lastPrint > 0;
pLastPrint(i) = %char( %date( lastPrint : *iso ) : *usa );
endif;
else;
// if there is a price change at any point in the history
// we write it to the report
if ( totalPrice / i <> pprice(1) )
or ( totalRetail / i <> pretail(1) );
// data array holds data per upc. write it out to report
for x = 1 to i;
if ( pprice(x) <> heldPrice ) or ( pLastPrint(x) <> *blanks);
if x = 1;
*in80 = *on;
endif;
if *inof;
createReport = *on;
except header;
*in80 = *on;
endif;
if *in80;
except blank;
endif;
except detail;
*in80 = *off;
heldRetail = pretail(x);
heldPrice = pprice(x);
endif;
endfor;
heldRetail = 0;
heldPrice = 0;
endif;
// reset counters and fill first array element
reset data;
heldUPC = upc;
totalPrice = 0;
totalRetail = 0;
i = 1;
pacct#(i) = acct#;
pupc(i) = upc;
pstatus(i) = status;
pdivision(i) = division;
pseason(i) = season;
pstyle(i) = style;
pcolor(i) = color;
psize(i) = size;
pretail(i) = retail;
totalRetail += retail;
pprice(i) = price;
totalPrice += price;
ppo#(i) = po#;
if voidDate > 0;
pvoidDate(i) = %char( %date( voidDate : *iso ) : *usa );
endif;
if rcvDate > 0;
prcvDate(i) = %char( %date( rcvDate : *iso ) : *usa );
endif;
if lastPrint > 0;
pLastPrint(i) = %char( %date( lastPrint : *iso ) : *usa );
endif;
endif;
#getNextTable2Record();
enddo;
close EDIPRT10;
// if report is generated, email it
if createReport;
#sendEmail();
endif;
// drop (deleted) the temporary tables
#dropTables();
*inlr = *on;
/end-free
**********************************************************************
oEDIPRT10 e header 1 01
o todaysDate 35
o reportTitle 95
o procName 132
o e header 1
o usaTime 8
o 127 'Page:'
o page Z 132
o e header 1 1
o 70 'FOA/FOJ'
o 80 'FOA/FOJ'
o 127 'Selected'
o e header 1
o 5 'Acct#'
o 10 'UPC'
o 24 'Sts'
o 29 'Div'
o 34 'Sea'
o 41 'Style'
o 55 'Color'
o 61 'Size'
o 69 'MSRP'
o 79 'Price'
o 97 'As seen on PO#'
o 116 'Date Rcvd'
o 128 'for print'
o e header
o 5 '-----'
o 19 '------------'
o 24 '---'
o 29 '---'
o 34 '---'
o 48 '------------'
o 55 '-----'
o 61 '----'
o 71 '--------'
o 81 '--------'
o 105 '----------------------'
o 117 '----------'
o 129 '----------'
******
o e detail 1
o pacct#(x) 5
o 80 pupc(x) 19
o pstatus(x) 23
o pdivision(x) 28
o pseason(x) 34
o pstyle(x) 48
o pcolor(x) 54
o psize(x) 61
o pretail(x) 3 71
o pprice(x) 3 81
o ppo#(x) 105
o prcvDate(x) 117
o pLastPrint(x) 129
******
o e blank 1
o 1 ' '
**********************************************************************
* procedure: #createTables *
* purpose: create temporary tables in qtemp *
**********************************************************************
p #createTables b
d #createTables pi
* create table1
c/exec sql
c+ create table qtemp/epotable1 (
c+ upc char(12 ),
c+ voidDate decimal(8,0)
c+ )
c/end-exec
* create table2
c/exec sql
c+ create table qtemp/epotable2 (
c+ acct# char( 5 ),
c+ upc char(12 ),
c+ voidDate decimal(8,0),
c+ status char( 1 ),
c+ division char( 2 ),
c+ season char( 1 ),
c+ style char(12 ),
c+ color char( 3 ),
c+ size char( 3 ),
c+ retail decimal(7,2),
c+ price decimal(7,2),
c+ po# char(22 ),
c+ rcvDate decimal(8,0),
c+ lastPrint decimal(8,0)
c+ )
c/end-exec
p #createTables e
**********************************************************************
* procedure: #getVoidedRecords *
* purpose: grab records from epopolo file that are in 'V' status *
* and have a void date of today *
**********************************************************************
p #getVoidedRecords...
p b
d #getVoidedRecords...
d pi
* select records
c/exec sql
c+ insert into qtemp/epotable1
c+ select distinct epjupc, epvdat
c+ from jplfil/epopolo
c+ where epedtr = :current
c+ with nc
c/end-exec
p #getVoidedRecords...
p e
**********************************************************************
* procedure: #getPriceChangeDetails *
* purpose: go out by upc and get the price history going back as *
* far as 1 year. populate table2 with this info. *
**********************************************************************
p #getPriceChangeDetails...
p b
d #getPriceChangeDetails...
d pi
d upc 12a
* select records
c/exec sql
c+ insert into qtemp/epotable2
c+ select distinct acct#, epjupc, epvdat, epstat, epjdiv, epjsea,
c+ epjsty, epjclr, epjsiz, epprtl, eppprc, epepo#, epedtr, eplpdt
c+ from jplfil/epopolo, jplfil/epoc1l2
c+ where epjupc = :upc and epedtr > :oneYearOld and beg03 = epepo#
c+ with nc
c/end-exec
p #getPriceChangeDetails...
p e
**********************************************************************
* procedure: #getTable1Record *
* purpose: like a primer read, get the first record from table1 *
**********************************************************************
p #getTable1Record...
p b
d #getTable1Record...
d pi
* select records to the create summary file
c/exec sql
c+ declare c1 cursor for
c+ select upc
c+ from qtemp/epotable1
c+ order by upc
c/end-exec
* open cursor
c/exec sql
c+ open c1
c/end-exec
* fetch cursor
c/exec sql
c+ fetch c1 into :upc
c/end-exec
p #getTable1Record...
p e
**********************************************************************
* procedure: #getNextTable1Record *
* purpose: get the next record from table1 *
**********************************************************************
p #getNextTable1Record...
p b
d #getNextTable1Record...
d pi
* fetch cursor
c/exec sql
c+ fetch next from c1 into :upc
c/end-exec
p #getNextTable1Record...
p e
**********************************************************************
* procedure: #getTable2Record *
* purpose: like a primer read, get the first record from table2 *
**********************************************************************
p #getTable2Record...
p b
d #getTable2Record...
d pi
* select records
c/exec sql
c+ declare c2 cursor for
c+ select acct#, upc, voidDate, status, division, season, style,
c+ color, size, retail, price, po#, rcvDate, lastPrint
c+ from qtemp/epotable2
c+ order by upc, rcvDate desc, voidDate, po#
c/end-exec
* open cursor
c/exec sql
c+ open c2
c/end-exec
* fetch cursor
c/exec sql
c+ fetch c2 into :acct#, :upc, :voidDate, :status, :division, :season,
c+ :style, :color, :size, :retail, :price, :po#, :rcvDate, :lastPrint
c/end-exec
/free
if sqlcod = 0;
heldUPC = upc;
endif;
/end-free
p #getTable2Record...
p e
**********************************************************************
* procedure: #getNextTable2Record *
* purpose: get the next record from table2 *
**********************************************************************
p #getNextTable2Record...
p b
d #getNextTable2Record...
d pi
* fetch cursor
c/exec sql
c+ fetch next from c2 into :acct#, :upc, :voidDate, :status, :division,
c+ :season, :style, :color, :size, :retail, :price, :po#, :rcvDate,
c+ :lastPrint
c/end-exec
p #getNextTable2Record...
p e
**********************************************************************
* procedure: #dropTables *
* purpose: drop or delete the temporary tables from qtemp *
**********************************************************************
p #dropTables b
d #dropTables pi
* close cursor
c/exec sql
c+ close c1
c/end-exec
* drop table1
c/exec sql
c+ drop table qtemp/epotable1
c/end-exec
* close cursor
c/exec sql
c+ close c2
c/end-exec
* drop table2
c/exec sql
c+ drop table qtemp/epotable2
c/end-exec
p #dropTables e
**********************************************************************
* procedure: #sendEmail *
* purpose: when a report is generated as a spool file we execute *
* this procedure. convert the spool file into an Adobe *
* .pdf file. change the authority on the .pdf so that *
* the public can view it. send the distribution. then *
* delete the original spool file since we don't need it. *
**********************************************************************
p #sendEmail b
d #sendEmail pi
/free
// create the Adobe .pdf file from the splf
qcmdexc( 'SPLPDF FILE(EDIPRT10) TOFILE('+ apos + tofile + apos + ') '
+ 'JOB(' + %editc( jobNumber : 'X' ) + '/' + %trim( user )
+ '/' + %trim( jobName ) + ') SPLNBR(*LAST) LAUNCH(*NO)'
);
// change authority so public and read
qcmdexc( 'CHGAUT OBJ(' + apos + tofile + apos + ') USER(*PUBLIC) '
+ 'USER(*PUBLIC) DTAAUT(*RWX) OBJAUT(*ALL)'
);
// alternative command to change authority of IFS file
// QSH CMD('chmod ugo+r home/mhaston/www/pdf/epopolo.pdf')
// send email with attachment
// add *CC after name for carbon copy, *BCC for blind carbon copy
qcmdexc( 'SNDDST TYPE(*DOC) '
+ 'TOINTNET('
+ '(xxx@jonesapparel.com) '
+ '(yyy@jonesapparel.com) '
+ '(zzz@jonesapparel.com) '
+ '(x@jonesapparel.com *CC) '
+ '(y@jonesapparel.com *BCC)'
+ ') '
+ 'DSTD(' + apos + subject + apos + ') '
+ 'MSG(' + apos + message + apos + ') '
+ 'DOC(EPOPOLO.PDF) '
+ 'FLR(REPORTS) '
);
// dltsplf
qcmdexc( 'DLTSPLF FILE(EDIPRT10) '
+ 'JOB(' + %editc( jobNumber : 'X' ) + '/' + %trim( user )
+ '/' + %trim( jobName ) + ') SPLNBR(*LAST)'
);
// delete document from library
qcmdexc( 'DLTDLO DLO(EPOPOLO.PDF) FLR(REPORTS)' );
/end-free
p #sendEmail e
Comment