""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   PRINTING A BILL
   """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   PRODUCT   :  R:BASE                  VERSION      :  3.1
   CATEGORY  :  PROGRAMMING             SUBCATEGORY  :  TOOLS
   """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   By using report expressions, you can print bills using a database that
   stores payments in one table and charges in another. For example, the
   database might have these tables:
 
     o  CUSTOMER with company names, addresses, and account numbers
     o  PAYMENTS with payment dates, amounts, and account numbers
     o  CHARGES with charge dates, charge amounts, and account numbers
 
 
   Bill Format
   """""""""""
   The bill format might look like this:
 
     The Wilson Foundation
     123 Main St
     Paris, WA 98303
 
     Account: 465365           Balance: $783.65
 
     Date               Charges        Payments
     --------           -------        --------
     01/01/90           $257.21
     01/05/90                           $100.00
     01/10/90           $150.00
     01/12/90                            $23.35
     01/15/90                           $200.58
     01/20/90                            $50.00
     03/01/90           $750.37
                       --------        --------
              Totals: $1,157.58         $373.93
 
 
   Create a View
   """""""""""""
   Before defining the report, create a view (BILLS) that combines the
   PAYMENTS and CHARGES tables, and puts all charges and payments into a
   single column (AMOUNT). Add an INTEGER column (AMTCODE) to distinguish
   between charges and payments. If AMTCODE is equal to 1, AMOUNT is a
   charge. If AMTCODE is equal to 2, AMOUNT is a payment. For example,
   use this CREATE VIEW command:
 
     CREATE VIEW bills +
       (custid, tdate, amount, amtcode) +
     AS SELECT custid, chgdate, charge, 1 +
       FROM charges +
     UNION SELECT custid, paydate, payment, 2 +
       FROM payments
 
   The first SELECT collects the charges. Then the UNION SELECT appends
   the payments.
 
   Define the Report Expressions
   """""""""""""""""""""""""""""
   Now create the report. Base it on the BILLS view. Define these
   expressions:
 
     vchgcur CURRENCY = (IFEQ(amtcode,1,amount,0))
     vpaycur CURRENCY = (IFEQ(amtcode,2,amount,0))
     vchgsum = (SUM(amount)) IN charges WHERE custid = custid
     vpaysum = (SUM(amount)) IN payments WHERE custid = custid
     vbal = (.vchgsum - .vpaysum)
     vchgtxt= (CTXT(.vchgcur))
     vpaytxt = (CTXT(.vpaycur))
     vcsumtxt = (CTXT(.vchgsum))
     vpsumtxt = (CTXT(.vpaysum))
     vchg = (IFEQ(.vchgcur,0,' ',.vchgtxt))
     vpay = (IFEQ(.vpaycur,0,' ',.vpaytxt))
     vcsum = (IFEQ(.vchgsum,0,' ',.vcsumtxt))
     vpsum = (IFEQ(.vpaysum,0,' ',.vpsumtxt))
     vcompany = company IN customer WHERE custid = custid
     vaddr = addr IN customer WHERE custid = custid
     vcity = city IN customer WHERE custid = custid
     vsz = (state&zipcode) IN customer WHERE custid = custid
     vcsz = (.vcity + ',' & .vsz)
 
   Customize the expressions to use your column and table names.
 
   The first expression (VCHGCUR) loads a charge into VCHGCUR. The second
   (VPAYCUR) loads a payment into VPAYCUR. The next two expressions
   (VCHGSUM and VPAYSUM) use SELECT functions to look up total charges
   and payments. For a full explanation of how to use this and other
   SELECT functions and expressions in report lookups, see "Wayne's
   Corner" in the upcoming January/February 1992 R:BASE EXCHANGE.
 
   The VBAL expression computes the customer's current balance. Then the
   next eight expressions ensure that the report prints blanks instead of
   zeros. The last few expressions look up name and address information.
 
 
   Create Breakpoints & Lay It Out
   """""""""""""""""""""""""""""""
   Make CUSTID Break1 and TDATE Break2, and add VCHGCUR, VPAYCUR,
   VCHGSUM, and VPAYSUM to the CUSTID (Break1) reset list, so they will
   recalculate for each new CUSTID. Then locate the fields. Your report
   layout might look like this:
 
     H1 S    vcompany      E
     H1 S    vaddr                E
     H1 S    vcsz                E
     H1
     H1 Account: S custid E     Balance: S vbal E
     H1
     H1 Date              Charges        Payments
     H1 --------         --------        --------
      D S tdate E        S vchg E        S vpay E
     F1                 ---------       ---------
     F1        Totals: S vcsum  E       S vpsum E
 
 
   Locate VCHG to show charges, VPAY to show payments, VCSUM to show
   total charges, and VPSUM to show total payments. Give all four fields
   a picture format: [>]%%%%%%%%% to right justify the value. Then locate
   VCOMPANY, VADDR, VCSZ, CUSTID, VBAL, and TDATE.
 
 
   Printing the Bills
   """"""""""""""""""
   Use these commands to print the bills:
 
     SET ZERO ON
     PRINT repname
 
   You don't need an ORDER BY clause because the breakpoints make R:BASE
   automatically sort by CUSTID and within CUSTID by TDATE.