Order List Programs

3 Program Examples

Order List Programs

Each of the order list programs produces a report as shown on page 3-2. This report lists the orders in the data base, broken down by product and sorted according to order number. The products themselves are listed in sorted order. Also, totals are maintained for all orders on each product as well as a total of all orders.

Example program 1 uses a two-set thread (see line 1320). This means that two pointers must be read in line 1480. The R1 pointer refers to a record in the PRODUCT set and the R2 pointer refers to a record in the CUSTOMER set.

Every time the product changes, the value of R1 also changes. S1 represents the value of R1 at the previous pass through the FOR loop. It is used to detect when it is necessary to print a trailer for the current product (consisting primarily of the total of the orders for the product) and a heading for the new product. Note however, that printing a trailer at the first pass through the loop is undesirable. A special test for S1=0 is made to stop this from occurring.

Note that the sort performed in line 1360 has Prod_no as its primary sort field. This variable comes from the PRODUCT data set (see line 1190). Because the schema item "PRODUCT-NO" is a search item, however, the value of the variable Product_no$ from the CUSTOMER detail set could just as well have been used.

This program shows many poor programming practices which are corrected by example program 2:

The status array is never tested at any point in the program. The data base may not have been opened; this will ultimately result in error 211 being issued in line 1180.
As pointed out earlier, the PRODUCT data set need not be involved in the sort. As discussed in Chapter 4, having the PRODUCT data set in the thread greatly reduces efficiency of the SORT BY statement. The description field, however, must be accessed to get the description field for printing. (This is done by a calculated-access DBGET in line 1690 of example program 2.)
After deleting PRODUCT from the thread there is only one pointer per record in the workfile (see line 1480). This points into the CUSTOMER set, so there is no way to wait for change in record number to indicate a change in product. Thus, the actual product numbers mut be compared. Note that the update of the old product number is accomplished by the IN DATA SET which is triggered when the DBGET in line 1690 is executed. This means that a line analogous to line 1710 in the first example is not needed.

Order List Report

                     OUTSTANDING ORDERS LIST

PRODUCT         ORDER NUMBER            CUSTOMER NAME                PRICE

50  (Tricycle)
                110                     Gissing, Malcom              45,00
                                                                  =========

   TOTAL ORDERS FOR 50                                               45,00

100 (Standard Bicycle)
                101                     Noname, Joseph               77,50
                103                     Hernandes, Jose             109,75
                108                     Arauja, Luciano A.           80,00
                                                                  =========
   TOTAL ORDERS FOR 100                                             267,25

300  (3-Speed Bicycle)
                104                     Houseman, Sean              133,00
                                                                  =========
                                                                    133,00
   TOTAL ORDERS FOR 300

500  (5-Speed Bicycle)
                100                     Smith, Thomas A.            175,50
                105                     Sono, Jomo A.               135,00
                109                     Bekker, Bart                125,00
                                                                  =========
   TOTAL ORDERS FOR 500                                             435,50

1000  (10-Speed Bicycle)
                102                     Johnson, Sam                162,50
                106                     Heining, Heinz              175,00
                107                     Dalling, Jimmy              150,50
                                                                  =========
   TOTAL ORDERS FOR 1000                                            488,00


                          TOTAL ORDERS                            $1368,25
                                                                  =========

Example Program 1: A Two-set Thread


   1000  !
   1010  !   OUTSTANDING ORDERS REPORT  (NOT INCLUDING ALL DETAIL)
   1020  !
   1030      INTEGER S(9) , Prod_no
   1040      DIM B$[12],P$[10],Buf$[170]
   1050      DIM Desc$[30],Order_no$[30],Name$[30]
   1060      DISP "~~"                    !   CLEAR SCREEN
   1090      B$="  SAD, SALES"
   1100      P$="MANAGER"
   1110      DBOPEN  (B$,P$,1,S(*) )      !   OPEN DATA BASE
   1150  !
   1160  !   SET UP ALL APPROPRIATE RELATIONSHIPS
   1170  !
   1180      DBASE IS B$
   1190      IN DATA SET "PRODUCT" USE Prod_no,Desc$
   1200      IN DATA SET "CUSTOMER" USE ALL
   1220  !
   1230  !   SET UP THE WORKFILE
   1240  !
   1310      ASSIGN "XYZ" TO #1
   1320      WORKFILE IS #1; THREAD IS "PRODUCT","CUSTOMER"
   1330  !
   1340  !   SORT THE STRUCTURE
   1350  !
   1360      SORT BY Prod_no,Order_no$
   1400  !
   1410  !   INITIALIZE VARIABLES & PRINT REPORT HEADER
   1420  !
   1430  Rep:Total=Master_total=0
   1440      S1=0
   1450      PRINT TAB(20);"OUTSTANDING ORDERS LIST";LIN(1)
   1460      PRINT "PRODUCT";SPA(8);"ORDER NUMBER";SPA(4);"CUSTOMER
             NAME";SPA(14);"PRICE";LIN(1);RPT$("-",63);LIN(1)
   1461  !
   1462  !   PRODUCE THE REPORT
   1463  !
   1470      FOR Z=1 TO WFLEN(1)
   1480        READ #1;R1,R2
   1570  !
   1580  !     PRINT TRAILER FOR PRODUCT (IF NEEDED)
   1590  !
   1600  !    (SKIP IF SAME PRODUCT AS BEFORE, OR FIRST TIME THRU LOOP)
   1610  !
   1620        IF (R1=S1) OR NOT S1 THEN Notot
   1630          PRINT USING Tot_image;VAL$(Prod_no),Total
   1640          Total=0
   1650  !
   1660  !     PRINT HEADER FOR PRODUCT (IF NEEDED)
   1670  !
   1680  Notot:IF R1=S1 THEN Skip1
   1690          DBGET (B$,"PRODUCT",4,S(*),"@",Buf$,R1)
   1710          S1=R1
   1720          PRINT VAL$(Prod_no);" (";TRIM$(Desc$);")"
   1810  !
   1820  !     PRINT ORDERS
   1830  !
   1840  Skip1:DBGET (B$,"CUSTOMER",4,S(*),"@",Buf$,R2)
   1860        PRINT TAB(16);
   1870        PRINT USING Itm_image;Order_no$,Name$,Price
   1880  Itm_image:IMAGE 16A,22A,2X,5DRDD
   1890  !
   1900  !     ACCUMULATE TOTALS
   1910  !
   1920        Total=Total+Price
   1940        Master_total=Master_total+Price
   1950      NEXT Z
   1960  !
   1970  !   PRINT FINAL TOTALS
   1980  !
   2000      PRINT USING Tot_image;VAL$(Prod_no),Total
   2010      PRINT USING Mstr_image;Master_total
   2040  Tot_image:IMAGE 54X,9("=") / 3X,
         "TOTAL ORDERS FOR ",10A,24X,6DRDD /
   2050  Mstr_image:IMAGE // 25X,"TOTAL ORDERS",14X,
         "$"8DRDD /   54X,9("=")
   2130      END

Example Program 2: Using Only One Set Instead of Two

   1000  !
   1010  !   OUTSTANDING ORDERS REPORT  (NOT INCLUDING ALL DETAIL)
   1020  !
   1030      INTEGER S(9),Product_no,Prod_no
   1040      DIM B$[12],P$[10],Buf$[170]
   1050      DIM Desc$[30],Order_no$[30],Name$[30]
   1060      DISP "~~"                   !  CLEAR SCREEN
   1090      B$="  SAD,SALES"
   1100      P$="MANAGER"
   1110      DBOPEN (B$,P$,1,S(*))        !  OPEN DATA BASE
   1120      IF S(0) THEN Dberr
   1150  !
   1160  !   SET UP ALL APPROPRIATE RELATIONSHIPS
   1170  !
   1180      DBASE IS B$
   1190      IN DATA SET "PRODUCT" USE Prod_no,Desc$
   1200      IN DATA SET "CUSTOMER" USE ALL
   1220  !
   1230  !   SET UP THE WORKFILE
   1240  !
   1310      ASSIGN "XYZ" TO #1
   1320      WORKFILE IS #1; THREAD IS "CUSTOMER"
   1330  !
   1340  !   SORT THE STRUCTURE
   1350  !
   1360      SORT BY Product_no,Order_no$
   1400  !
   1410  !   INITIALIZE VARIABLES & PRINT REPORT HEADER
   1420  !
   1430  Rep:Total=Master_total=0
   1440      Prod_no=-1
   1450      PRINT TAB(20);"OUTSTANDING ORDERS LIST";LIN(1)
   1460      PRINT "PRODUCT";SPA(8);"ORDER NUMBER";SPA(4);"CUSTOMER
             NAME";SPA(14);"PRICE";LIN(1);RPT$("-",63);LIN(1)
   1461  !
   1462  !   PRODUCE THE REPORT
   1463  !
   1470      FOR Z=1 TO WFLEN(1)
   1480        READ #1;R1
   1490        DBGET (B$,"CUSTOMER",4,S(*),"@",Buf$,R1)
   1500        IF S(0) THEN Dberr
   1570  !
   1580  !     PRINT TRAILER FOR PRODUCT (IF NEEDED)
   1590  !
   1600  !    (SKIP IF SAME PRODUCT AS BEFORE, OR FIRST TIME THRU LOOP)
   1610  !
   1620        IF (Prod_no=Product_no) OR (Prod_no%<0) THEN Notot
   1630          PRINT USING Tot_image;VAL$(Product_no),Total
   1640          Total=0
   1650  !
   1660  !     PRINT HEADER FOR PRODUCT (IF NEEDED)
   1670  !
   1680  Notot:IF Prod_no=Product_no THEN Skip1
   1690          DBGET (B$,"PRODUCT",7,S(*),"@",Buf$,Product_no)
   1700          IF S(0) THEN Dberr
   1720          PRINT VAL$(Prod_no);" (";TRIM$(Desc$);")"
   1810  !
   1820  !     PRINT ORDERS
   1830  !
   1860  Skip1:PRINT TAB(16);
   1870        PRINT USING Itm_image;Order_no$,Name$,Price
   1880  Itm_image:IMAGE 16A,22A,2X,5DRDD
   1890  !
   1900  !     ACCUMULATE TOTALS
   1910  !
   1920        Total=Total+Price
   1940        Master_total=Master_total+Price
   1950      NEXT Z
   1960  !
   1970  !   PRINT FINAL TOTALS
   1980  !
   2000      PRINT USING Tot_image;VAL$(Prod_no),Total
   2010      PRINT USING Mstr_image;Master_total
   2040  Tot_image:IMAGE 54X,9("=") / 3X,
         "TOTAL ORDERS FOR ",10A,24X,6DRDD /
   2050  Mstr_image:IMAGE // 25X,"TOTAL ORDERS",14X,
         "$"8DRDD / 54X,9("=")
   2060      STOP
   2070  !
   2080  !     ERROR TERMINATION ROUTINE
   2090  !
   2100  Dberr:DISP LIN(2);"STATUS ERROR ";VAL$(S(0));
         " IN LINE ";S(6)
   2170      END

Eloquence Sort Manual - 19 DEC 2002