Accounting system rewrite

Accounting System Rewrite

Status:	unknown
Sponsored by:	ByWater Solutions
Developed by:	Kyle M Hall
Expected for:	2013-04-15
Bug number:	Bug 10087
Work in progress repository:	http://git.bywatersolutions.com/koha.git/shortlog/refs/heads/WIP-Bug_10087
Description:	It's time to rewrite Koha's accounting system!

This is a proposal to build a new and improved accounting system for Koha.

This proposal is to remove most of the existing accounts-related code and replace it using modern Koha development practices. New tables will be created and accessed using data objects based on DBIx::Class. Appropriate code will be moved to the class file, and to Koha::Accounts where applicable. The utmost care will be taken to keep the code simple and DRY.

New Proposed Database Tables

Fees - All fees and fines will be stored in this table

CREATE TABLE IF NOT EXISTS account_fees (
  fee_id int(11) NOT NULL AUTO_INCREMENT,
  borrowernumber int(11) NOT NULL DEFAULT '0',
  itemnumber int(11) DEFAULT NULL,
  issue_id int(11) DEFAULT NULL,
  type VARCHAR(20) DEFAULT 'F',
  accruing tinyint(1) NOT NULL DEFAULT '0',
  amount_original decimal(28,6) DEFAULT NULL,
  amount_outstanding decimal(28,6) DEFAULT NULL,
  amount_last_increment decimal(28,6) DEFAULT NULL,
  description mediumtext,
  notes text,
  dispute mediumtext,
  notify_id int(11) NOT NULL DEFAULT '0',
  notify_level int(2) NOT NULL DEFAULT '0',
  manager_id int(11) DEFAULT NULL,
  created_on timestamp NULL DEFAULT NULL,
  updated_on timestamp NULL DEFAULT NULL,
  PRIMARY KEY (fee_id),
  KEY acctsborridx (borrowernumber),
  KEY itemnumber (itemnumber),
  KEY borrowernumber (borrowernumber),
  KEY issue_id (issue_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Katrin: I think some of these columns are no longer used in current versions of Koha, namely: notify_id, notify_level and dispute. Do we keep description for backwards compatibility? The way it's currently used is creating lots of problems for non-English installations. What about FK?

Payments - All payments will be stored in this table

This table is replacement for inserting payments into the accountlines table

CREATE TABLE IF NOT EXISTS `payments` (
`payment_id` int(11) NOT NULL AUTO_INCREMENT,
`borrowernumber` int(11) NOT NULL,
`amount` decimal(28,6) NOT NULL,
`notes` text,
`created_on` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Katrin: I wonder if we should add the 'payment branch'? Or will this kind of information solely be kept in the action_logs/statistics?

Fee Payments - Each payment may affect one or more fees, this table is a join table between the fees tables and the payments table

This table has no equivalent in Koha currently. This table will allow us to track which payments have paid which fees.

CREATE TABLE `fee_payments` (
`fee_payment_id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`fee_id` INT NOT NULL ,
`payment_id` INT NOT NULL ,
`created_on` TIMESTAMP NOT NULL ,
PRIMARY KEY ( `fee_payment_id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Katrin: Wouldn't fee_payments.created_on always be the same as payments.created_on?

Fee Modifications - Any time a fee amount is incremented, a line will be added to this table referencing it

This table is a replacement for the accountoffsets table

CREATE TABLE IF NOT EXISTS `fee_modifications` (
`fee_modification_id` int(11) NOT NULL AUTO_INCREMENT,
`borrowernumber` int(11) NOT NULL,
`fee_id` int(11) NOT NULL,
`amount` decimal(28,6) NOT NULL DEFAULT '0.000000',
`created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`fee_modification_id`),
KEY `accountoffsets_ibfk_1` (`borrowernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

New Proposed Additions to Exiting Database Tables

borrowers.fees

This new column in the borrowers table would store the current total amount owed ( or due to ) the patron. It would be updated when fees or payments are added to the database. It can also be forcefully recalculated based on the new fee and payment tables.

ALTER TABLE  `borrowers` ADD  `fees` DECIMAL( 28, 6 ) NOT NULL DEFAULT  '0'

New Accounting Behaviors

• If a patron has a negative amount owed, pay off new fees & fines from this balance first