13 Writing a program to produce a simple report
::::::
W
ell... not so simple – just one that can’t be produced using other ways (or if there’s no time to investigate these “other ways” deeper). Here we’ll use the last sample from the previous chapter to produce a report. Firstly - just plain delimited text. (For simplicity sake just make it for the “BNK” company only but let’s add some complexity and try to report not only some particular day but an arbitrary period of time.)To proceed we need to understand that the file FBNK.ACCT.ACTIVITY might be big. Very big – depending on the number of accounts and movements of funds. So I’d prefer to use this opportunity to introduce so-called “Basic SELECT”. What’s the difference between it and a regular jQL SELECT?
“Basic SELECT” isn’t able to limit the selection – it just selects all @ids. But it’s ready to proceed records immediately and doesn’t depend on number of records – you can consider it as a “pointer” moving over a file. Herewith goes another feature of it: if new records are added to the file during the process, they most probably will be picked up as well. Quite the opposite, jQL SELECT makes a “snapshot” of record @ids available in a file – and of course might take very long time doing that.
In the previous chapter we missed the fact that data is grouped month-wise so we tried to sum up all movements occured on the 16th day of every month which makes not much sense (unless it’s a tax payday and you want to calculate the total turnover and compare it to average values). That’s why here we’ll have a possibility to include any period of time to the output. Assuming that @ids are in a form “account id.year month” we really need “Basic SELECT” here since date is at the end of @id rather than at the beginning and therefore jQL SELECT will be slow - especially if user decides to set quite a big range of dates to include to the report. Creating a secondary index is possible but often is not an option so we don’t consider that (though in other case you might do).
For compiler to find T24 insert files (one of them – I F.ACCT.ACTIVITY – we’ll certainly need) it’s necessary to add the path to T24.BP to jrunComp.cmd:
Change in jrunComp.cmd
34 jcompile -I <path_to_bnk.run_parent_directory>\T24.BP %1.b && del %1.dll %1.obj
So here’s the program. Necessary dates go as parameters which we’ll quickly check (I deliberately omit a check if a date is a fully valid one to save some space):
mvmtrep.b
1 * Program to report debit
2 * turnover for any period of time
3 $INCLUDE I_F.ACCT.ACTIVITY
4
5 * take dates (YYYYMMDD) as parameters, make a quick check
6
13 WRITING A PROGRAM TO PRODUCE A SIMPLE REPORT
7 V.BEG = SENTENCE(1)
8 V.END = SENTENCE(2)
9
10 IF NOT(V.BEG MATCHES ’8N’) OR NOT (V.END MATCHES ’8N’) THEN
11 CRT ’DATES ARE NOT VALID’
12 STOP
13 END
14
15 IF V.END LT V.BEG THEN
16 CRT ’END DATE IS LESS THAN START DATE’
17 STOP
18 END
We’ll create a report consisting of delimited strings of data:
mvmtrep.b - continued
19
20 * delimiter for report
21
22 V.DLM = ’;’ ;* make it comma if you like it more
23
For our report we need to proceed all records in FBNK.ACCT.ACTIVITY. Before we can issue “Basic SELECT” we need the file to be opened:
mvmtrep.b - continued
24 * open the file
25
26 OPEN ’FBNK.ACCT.ACTIVITY’ TO F.AC.ACT ELSE
27 CRT ’ERROR OPENING ACCT.ACTIVITY’
28 STOP
29 END
30
31 * select the file
32
33 SELECT F.AC.ACT TO 9
Now in a loop extract an @id; first of all see if it matches our range of dates:
mvmtrep.b - continued
34
35 * main loop
36
37 LOOP
38 READNEXT V.ID FROM 9 ELSE BREAK
39
13 WRITING A PROGRAM TO PRODUCE A SIMPLE REPORT
40 V.MTH = FIELD(V.ID, ’-’, 2)
41 IF V.BEG[1,6] GT V.MTH OR V.END[1,6] LT V.MTH THEN CONTINUE
42
If it does then:
mvmtrep.b - finished
43 * Read and proceed the record
44
45 READ R.AC.ACT FROM F.AC.ACT, V.ID ELSE
46 CRT ’UNABLE TO READ FBNK.ACCT.ACTIVITY RECORD ’ : V.ID
47 STOP
48 END
49
50 V.DAYNO.L = R.AC.ACT<IC.ACT.DAY.NO> ;* list of available days
51 V.DAYNO.QTY = DCOUNT(V.DAYNO.L, @VM)
52 V.ACCT = FIELD(V.ID, ’-’, 1) ;* a/c number for report
53
54 FOR V.I = 1 TO V.DAYNO.QTY
55 V.DAYNO = V.DAYNO.L<1,V.I>
56 IF V.DAYNO EQ ’’ THEN CONTINUE
57 V.DATE = V.MTH : V.DAYNO
58
59 * proceed only days that belong to our period
60
61 IF V.DATE GE V.BEG AND V.DATE LE V.END THEN
62 V.DR = R.AC.ACT<IC.ACT.TURNOVER.DEBIT,V.I>
63 IF NOT(V.DR) THEN CONTINUE
64 V.LINE = V.DATE : V.DLM : V.ACCT : V.DLM : V.DR
65 CRT V.LINE ;* just print it - redirection rules :)
66 ;* though not always :((
Compile and run it:
mvmtrep invocation C:\sa-tafc> jrunComp.cmd mvmtrep
mvmtrep.c
13 WRITING A PROGRAM TO PRODUCE A SIMPLE REPORT
C:\sa-tafc>j runT24.cmd
jsh ~ --> mvmtrep 20100725 20100805 ...
20100728;JPYUSD140160017;-18000 20100802;EUR144410003;-68152.09 20100804;EUR144410003;-1416239.08 20100805;EUR145550001;-20000 20100805;51284;-200000
20100728;USDSGD140160014;-3.74 20100805;EUR143040074;-20000 20100805;USD141960017;-37188.85 jsh ~ -->
This is the fastest way to get “raw” output. The programming itself shouldn’t take more than one hour.
Bottomline: consider using external tools – exclusively or combined with jQL or jBC, but sometimes just to write jBC program is the easiest and/or the fastest way.
As you can see, the results are not sorted, there’s no totals etc. To create a nice-looking report we can use any of the following methods:
• Amend the program to produce the final layout. If it’s a simple ASCII report like T24 account statement it’s quite easy to do.
• As it was already suggested earlier, take the output to Excel-like tool.
• Amend jBC program to create output in some fancy format like xml and proceed it somewhere else. Very fashionable option but we don’t consider it here assuming the fact that there are some limitations in jBC. xml shouldn’t be a problem (provided that you have an external tool to proceed it) but other formats - like pdf - certainly will. And forget about creating something like “doc” file - even Microsoft is unable to proceed all flavours of them.
• Use some external tool to proceed the final output from the “raw” output we already have. Will do that in the next chapter (though you might try the previous option if you like it more).