Fees System Design # 3

The design goals are simplicity and flexibility. It is all too easy to create something too complex to maintain, etc. To that end, I have dropped several fields that were really unnecessary, given other logical contraints. The whole idea of invoicing wasn't really necessary since it is more of a hold over from paper based accounting. Each charge transaction can stand on its own as the smallest atomic unit of a transaction. Invoice numbers would just get in the way.

Similarly for the concept of partial payments. The concept of "received on account" would still be necessary even if we had partial payments. We can handle the same problems of partial payments using the roa (received on account) idea and it is consistent with normal accounting principles.

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 paid (paid_id) field remain NULL.

2. The invoicing process is simply a reporting process. This is no separate aggregation stage, any more. Invoices will normally be run and mailed. The parent site should also allow viewing of outstanding charges, etc.

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 may list id's of the transaction paid.

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

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

   There will be no "partial payments"

Operations

• 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 (both charge and also roa transaction as well).
  d) Add them up and make sure they add to the total of the payment. Verify the total. If not flag an error.
  e) Now with all of the charge/roa transactions and the single payment transaction, print out a receipt.
• Printing a bill of outstanding transactions/invoices
  a) Find all transactions lacking a paid_id value (ie. NULL). These will all be charge (chg) types. The roa transactions will automatically have the id of their matching payment trasaction, and payment transaction will have the paid_id fields set to -1 since they are payments.
  b) print them out in date order and add to give an outstanding balance. ROA transactions may also be included since they will reduce this outstanding balance.
• 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.
  

  Table Structures

  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 varchar(255) default NULL,
    description text,
    subtotal decimal(10,2) default NULL,
    tax1 decimal(10,2) default NULL,
    tax1_name varchar(16) default NULL,
    tax2 decimal(10,2) default NULL,
    tax2_name varchar(16) default NULL,
    tax3 decimal(10,2) default NULL,
    tax3_name varchar(16) default NULL,
    tax4 decimal(10,2) default NULL,
    tax4_name varchar(16) default NULL,
    total decimal(10,2) default NULL,
    paid_id int(11) default NULL,
    PRIMARY KEY  (id)
  ) TYPE=MyISAM;
  

  The second table stores predefined fees to allow quick application to particular students or student groups.

  CREATE TABLE fees_predefined (
    id int(11) NOT NULL auto_increment,
    name varchar(16) default NULL,
    description varchar(255) default NULL,
    amount double default NULL,
    discount double default NULL,
    tax1_flag char(1) default NULL,
    tax2_flag char(1) default NULL,
    tax3_flag char(1) default NULL,
    tax4_flag char(1) default NULL,
    group_name varchar(32) default NULL,
    group_value varchar(32) default NULL,
    profile varchar(32) default NULL,
    PRIMARY KEY  (id)
  ) TYPE=MyISAM;