Fee System Design Update

The fees subsystem workflow is:

1) Charge transactions are created for various charges to
   students. This transaction adds a record to the fees journal
   (fees_jrl) and fills in the id, studnum (student number),
   transaction date (trans_date), transaction type (trans_type is
   chg), and information about the charge (name, description,
   subtotal, and the shortnames and values for up to 4 taxes, and then
   a total for these values).

   The invoice (invoice_number, invoice_date) and paid (paid_id,
   paid_part_id, paid_part_amt) fields remain NULL.

2) The invoicing process will aggregate these charge transactions into
   single invoices (up to a certain number of transactions per
   invoice). This process will normaly result in invoice reporting to
   students and parents. In this process the invoice_number and
   invoice_date will be filled in. The invoice number will come from a
   file in the configuration file area (etc) and the invoice date will
   normally be the current date.

3) The payment process will then generate a new transaction record in
   the fees journal (fees_jrl). It will only fill in limited fields
   and leave the rest NULL. The fields added are id (auto), student
   number (studnum), date of payment (trans_date), and a transaction
   type (trans_type = pay). The name field will be 'Payment' (or
   alternate language equivalent). The 'total' field will store the
   amount of the payment. The description field will list all of the
   invoices paid, and a partial payment (if any).

   The invoice number field will store the receipt number for this
   payment (loaded from the receiptnumber file in the configuration
   file area).

   The other fields will remain NULL in the record (subtotal, all tax
   fields, invoice_date, and paid fields).

   All transaction records that are paid completely will have their
   paid_id field filled in with the id of the payment transaction that
   does this. 

   A partial payment on any transaction in any invoice will have
   amount paid stored in paid_part_amt and the payment id placed in
   the paid_part_id field. When the partially paid transaction is
   fully paid, the paid_id will be added, but the partial payment
   values will remain. (so they can be sucessfully read to calculate
   the transactions that the payment was made for, in order to reprint
   receipts).


Operations

1. Printing/Reprinting payment receipts.

a) Find all pay transactions for this student in descending date order.
b) Once a particular payment is selected, get it's id and total.
c) Find all transactions that have this id in their paid_id fields and
group them into invoices sorted by date.
d) Add them up and make sure they add to the total of the payment. If
not, then there is a partial payment as well. Search for partial
payments in the paid_part_id field for the id of the payment
record. Verify the total. If not flag an error.
e) Now with all of the charge transactions and the single payment
transaction, print out a receipt.


2. Printing a bill of outstanding transactions/invoices.

a) Find all transactions lacking a paid_id value (ie. NULL). This may
also include transactions with partial payments made against them
(since the paid_id field is not filled in until complete payment is
made).
b) print them out in date order and add to give an outstanding balance.


3. Printing a historical bill of transactions

a) Pick a starting date. Check for any outstanding values before this
date to figure out a starting balance owing for this child.
b) Print transactions in date order. If paid, the balance, if any,
will remain unchanged. Unpaid transactions will alter the printed
balance.
c) Print total outstanding.



CREATE TABLE fees_jrl (
  id int(11) not NULL auto_increment,
  studnum int(11) default NULL,
  trans_date date default NULL,
  trans_type varchar(8) default NULL,
  name char(16) default NULL,
  description text default NULL,
  subtotal double precision default NULL,
  tax1 double precision default NULL,
  tax1_name varchar(16) default NULL,
  tax2 double precision default NULL,
  tax2_name varchar(16) default NULL,
  tax3 double precision default NULL,
  tax3_name varchar(16) default NULL,
  tax4 double precision default NULL,
  tax4_name varchar(16) default NULL,
  total double precision default NULL,
  invoice_number int(11) default NULL,
  invoice_date date default NULL,
  paid_id int(11) default NULL,
  paid_part_id int(11) default NULL,
  paid_part_amt double precision default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;
#DEFAULT CHARSET=latin1;