From db73f4f9468cf8056803e141786fd5db3cffd54e Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 15 Jul 2014 10:32:16 -0400 Subject: [PATCH] Bug 6427 - Update database This patch set re-engineers Koha's accounting system to be faster, more stable, and have a better historical accounting of fees and payments while also unifying much of the payment logic and simplifying the code behind it. Three new tables have been created: account_debits stores fines and fees owed by the patron. account_credits stores payments, write-offs, and other line items that reduce the amount owed by a patron. account_offsets stores data about changes to debits and credits, including which credits paid which debits. borrowers.account_balance stores the current balance of the patron, so it does not have to be calculated each time it needs to be displayed. issues.issue_id has been added so that individual fines can be directly linked to the issue that caused them without ambiguity. The various accounting code strings ( F, FU, Rent, etc ), some of which have little or no documentation have been replaced by what enumerated data types which are documented via POD. There are three, one for each of Debits ( fees/fines ), Credits ( payments ), and Offsets. The system continues to allow custom fee types ( moving MANUAL_INV to MANUAL_DEBIT ) while additionally allowing custom payment types ( MANUAL_CREDIT ). The system also allows for overpayment, the the outstanding credit being applied to future fees as they are created. The system also allows for a librarian to accept a different amount of money than what is to be paid, and will calculate the change to be given. Test Plan: 1) Apply this patch 2) Create some manual fees 3) Test the fee payments * Pay one fee * Pay multiple fees * Make a partial payment * Write off a fee 4) View the fees/payments, verify they are correct 5) Check out some items as overdue that should have fines 6) Run fines.pl, verify fines were generated 7) Pay on an accruing fine 8) Run fines.pl again, verify the fine hasn't changed 9) Increase the daily fine in issuing rules 10) Run fines.pl again, verify the fine has increased 11) Decrease the daily fine in issuing rules 12) Run fines.pl again, verify the fine has decreased 13) Test dropbox mode 14) Test WhenLostChargeReplacementFee both ways 15) Test RefundLostItemFeeOnReturn both ways 16) Test WhenLostForgiveFine both ways 17) Test CalculateFinesOnReturn both ways Signed-off-by: Kyle M Hall Signed-off-by: Sean McGarvey --- installer/data/mysql/atomicupdate/bug_6427.perl | 173 +++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 134 ++++++++++++------ installer/data/mysql/updatedatabase.pl | 5 +- 3 files changed, 266 insertions(+), 46 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_6427.perl diff --git a/installer/data/mysql/atomicupdate/bug_6427.perl b/installer/data/mysql/atomicupdate/bug_6427.perl new file mode 100644 index 0000000..b34ffd8 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_6427.perl @@ -0,0 +1,173 @@ +my $dbh = C4::Context->dbh(); + +$dbh->do(q{ + UPDATE accountlines a LEFT JOIN issues i USING ( borrowernumber, itemnumber ) SET accounttype = 'F' WHERE i.issue_id IS NULL +}); + +$dbh->do(" + CREATE TABLE IF NOT EXISTS account_credits ( + credit_id int(11) NOT NULL AUTO_INCREMENT, + borrowernumber int(11) NOT NULL, + `type` varchar(255) NOT NULL, + amount_received decimal(28,6) DEFAULT NULL, + amount_paid decimal(28,6) NOT NULL, + amount_remaining decimal(28,6) NOT NULL, + amount_voided decimal(28,6) NULL DEFAULT NULL, + notes text, + branchcode VARCHAR( 10 ) NULL DEFAULT NULL, + manager_id int(11) DEFAULT NULL, + created_on timestamp NULL DEFAULT NULL, + updated_on timestamp NULL DEFAULT NULL, + PRIMARY KEY (credit_id), + KEY borrowernumber (borrowernumber), + KEY branchcode (branchcode) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +"); +$dbh->do(" + CREATE TABLE IF NOT EXISTS account_debits ( + debit_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(255) NOT NULL, + 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, + branchcode VARCHAR( 10 ) NULL DEFAULT NULL, + manager_id int(11) DEFAULT NULL, + created_on timestamp NULL DEFAULT NULL, + updated_on timestamp NULL DEFAULT NULL, + PRIMARY KEY (debit_id), + KEY acctsborridx (borrowernumber), + KEY itemnumber (itemnumber), + KEY borrowernumber (borrowernumber), + KEY issue_id (issue_id), + KEY branchcode (branchcode) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +"); + +$dbh->do(" + CREATE TABLE account_offsets ( + offset_id int(11) NOT NULL AUTO_INCREMENT, + debit_id int(11) DEFAULT NULL, + credit_id int(11) DEFAULT NULL, + `type` varchar(255) DEFAULT NULL, + amount decimal(28,6) NOT NULL COMMENT 'A positive number here represents a payment, a negative is a increase in a fine.', + created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (offset_id), + KEY fee_id (debit_id), + KEY payment_id (credit_id) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +"); + +$dbh->do(" + ALTER TABLE `account_credits` + ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; +"); +$dbh->do(" + ALTER TABLE `account_debits` + ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; +"); +$dbh->do(" + ALTER TABLE `account_offsets` + ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE; +"); + +$dbh->do(" + ALTER TABLE borrowers ADD account_balance DECIMAL( 28, 6 ) NOT NULL; +"); + +my $schema = Koha::Database->new()->schema; +my $debit_rs = $schema->resultset('AccountDebit'); +my $credit_rs = $schema->resultset('AccountCredit'); +my $issues_rs = $schema->resultset('Issue'); + +use Koha::Accounts::DebitTypes; +use Koha::Accounts::CreditTypes; + +my $debit_types_map = { + 'A' => Koha::Accounts::DebitTypes::AccountManagementFee, + 'F' => Koha::Accounts::DebitTypes::Fine, + 'FU' => Koha::Accounts::DebitTypes::Fine, + 'L' => Koha::Accounts::DebitTypes::Lost, + 'M' => Koha::Accounts::DebitTypes::Sundry, + 'N' => Koha::Accounts::DebitTypes::NewCard, + 'Rent' => Koha::Accounts::DebitTypes::Rental, +}; + +my $credit_types_map = { + 'FOR' => Koha::Accounts::CreditTypes::Forgiven, + 'LR' => Koha::Accounts::CreditTypes::Found, + 'Pay' => Koha::Accounts::CreditTypes::Payment, + 'PAY' => Koha::Accounts::CreditTypes::Payment, + 'WO' => Koha::Accounts::CreditTypes::WriteOff, + 'W' => Koha::Accounts::CreditTypes::WriteOff, + 'C' => Koha::Accounts::CreditTypes::Credit, + 'CR' => Koha::Accounts::CreditTypes::Credit, +}; + +my $sth = $dbh->prepare("SELECT * FROM accountlines"); +$sth->execute(); +while ( my $a = $sth->fetchrow_hashref() ) { + if ( $debit_types_map->{ $a->{accounttype} } ) { + $debit_rs->create( + { + borrowernumber => $a->{borrowernumber}, + itemnumber => $a->{itemnumber}, + amount_original => $a->{amount}, + amount_outstanding => $a->{amountoutstanding}, + created_on => $a->{timestamp}, + description => $a->{description}, + notes => $a->{note}, + manager_id => $a->{manager_id}, + accruing => $a->{accounttype} eq 'FU', + type => $debit_types_map->{ $a->{accounttype} }, + issue_id => $a->{accounttype} eq 'FU' + ? $issues_rs->single( + { + borrowernumber => $a->{borrowernumber}, + itemnumber => $a->{itemnumber}, + } + )->issue_id() + : undef, + } + ); + } + elsif ( $credit_types_map->{ $a->{accounttype} } ) { + $credit_rs->create( + { + borrowernumber => $a->{borrowernumber}, + amount_paid => $a->{amount} * -1, + amount_remaining => $a->{amountoutstanding} * -1, + created_on => $a->{timestamp}, + notes => $a->{note}, + manager_id => $a->{manager_id}, + type => $credit_types_map->{ $a->{accounttype} }, + } + ); + } + else { + # Everything else must be a MANUAL_INV + $debit_rs->create( + { + borrowernumber => $a->{borrowernumber}, + itemnumber => $a->{itemnumber}, + amount_original => $a->{amount}, + amount_outstanding => $a->{amountoutstanding}, + created_on => $a->{timestamp}, + description => $a->{description}, + notes => $a->{note}, + manager_id => $a->{manager_id}, + type => Koha::Accounts::DebitTypes::Sundry, + } + ); + } +} + +print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n"; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index dd6bac9..ab9bb57 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -265,6 +265,7 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history + `account_balance` decimal(28,6) NOT NULL, UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), @@ -2726,50 +2727,6 @@ CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- --- Table structure for table `accountlines` --- - -DROP TABLE IF EXISTS `accountlines`; -CREATE TABLE `accountlines` ( - `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, - `borrowernumber` int(11) NOT NULL default 0, - `accountno` smallint(6) NOT NULL default 0, - `itemnumber` int(11) default NULL, - `date` date default NULL, - `amount` decimal(28,6) default NULL, - `description` mediumtext, - `dispute` mediumtext, - `accounttype` varchar(5) default NULL, - `amountoutstanding` decimal(28,6) default NULL, - `lastincrement` decimal(28,6) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `notify_id` int(11) NOT NULL default 0, - `notify_level` int(2) NOT NULL default 0, - `note` text NULL default NULL, - `manager_id` int(11) NULL, - PRIMARY KEY (`accountlines_id`), - KEY `acctsborridx` (`borrowernumber`), - KEY `timeidx` (`timestamp`), - KEY `itemnumber` (`itemnumber`), - CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- --- Table structure for table `accountoffsets` --- - -DROP TABLE IF EXISTS `accountoffsets`; -CREATE TABLE `accountoffsets` ( - `borrowernumber` int(11) NOT NULL default 0, - `accountno` smallint(6) NOT NULL default 0, - `offsetaccount` smallint(6) NOT NULL default 0, - `offsetamount` decimal(28,6) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- -- Table structure for table `action_logs` -- @@ -3519,6 +3476,95 @@ CREATE TABLE discharges ( CONSTRAINT borrower_discharges_ibfk1 FOREIGN KEY (borrower) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +-- +-- Table structure for table 'account_credits' +-- +DROP TABLE IF EXISTS account_credits; +CREATE TABLE IF account_credits ( + credit_id int(11) NOT NULL AUTO_INCREMENT, -- The unique id for this credit + borrowernumber int(11) NOT NULL, -- The borrower this credit applies to + `type` varchar(255) NOT NULL, -- The type of credit this is ( defined by Koha::Accounts::CreditTypes ) + amount_received decimal(28,6) DEFAULT NULL, -- If this was a cash payment, the amount of money given + amount_paid decimal(28,6) NOT NULL, -- The actual ammount paid, if less than amount_recieved, change was given back + amount_remaining decimal(28,6) NOT NULL, -- The amount of this credit that has not been applied to outstanding debits + amount_voided decimal(28,6) NULL DEFAULT NULL, -- The amount of this credit was for before it was voided + notes text, -- Misc notes for this credit + branchcode VARCHAR( 10 ) NULL DEFAULT NULL, -- Branchcode where the credit was created ( if any ) + manager_id int(11) DEFAULT NULL, -- The borrowernumber of the user who created this credit ( if any ) + created_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was created + updated_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was last modified + PRIMARY KEY (credit_id), + KEY borrowernumber (borrowernumber), + KEY branchcode (branchcode) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Constraints for table `account_credits` +-- +ALTER TABLE `account_credits` + ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Table structure for table 'account_debits' +-- + +DROP TABLE IF EXISTS account_debits; +CREATE TABLE account_debits ( + debit_id int(11) NOT NULL AUTO_INCREMENT, -- The unique id for this debit + borrowernumber int(11) NOT NULL DEFAULT '0', -- The borrower this debit applies to + itemnumber int(11) DEFAULT NULL, -- The item related to this debit ( for fines, lost fees, etc ) + issue_id int(11) DEFAULT NULL, -- The checkout this debit is related to ( again, for fines, lost fees, etc ) + `type` varchar(255) NOT NULL, -- The type of debit this is ( defined by Koha::Accounts::DebitTypes ) + accruing tinyint(1) NOT NULL DEFAULT '0', -- Boolean flag, tells of if this is a fine that is still accruing + amount_original decimal(28,6) DEFAULT NULL, -- The total amount of this debit + amount_outstanding decimal(28,6) DEFAULT NULL, -- The amount still owed on this debit + amount_last_increment decimal(28,6) DEFAULT NULL, -- The amount by which this debit last changed + description mediumtext, -- The description for this debit + notes text, -- Misc notes for this debit + branchcode VARCHAR( 10 ) NULL DEFAULT NULL, -- Branchcode where the debit was created ( if any ) + manager_id int(11) DEFAULT NULL, -- The borrowernumber of the user who created this debit ( if any ) + created_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was created + updated_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was last modified + PRIMARY KEY (debit_id), + KEY acctsborridx (borrowernumber), + KEY itemnumber (itemnumber), + KEY borrowernumber (borrowernumber), + KEY issue_id (issue_id), + KEY branchcode (branchcode) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Constraints for table `account_debits` +-- +ALTER TABLE `account_debits` + ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Table structure for table 'account_offsets' +-- + +DROP TABLE IF EXISTS account_offsets; +CREATE TABLE account_offsets ( + offset_id int(11) NOT NULL AUTO_INCREMENT, -- Unique id for this offset + debit_id int(11) DEFAULT NULL, -- Related debit + credit_id int(11) DEFAULT NULL, -- Related credit ( if any ) + `type` varchar(255) DEFAULT NULL, -- The type of this offset ( defined by Koha::Accounts::OffsetTypes ), if any + amount decimal(28,6) NOT NULL, -- The amount of the offset, positive means patron owes more, negative means patron owes less + created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp for when this offset was created + PRIMARY KEY (offset_id), + KEY fee_id (debit_id), + KEY payment_id (credit_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Constraints for table `account_offsets` +-- +ALTER TABLE `account_offsets` + ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 394e4ba..745d45b 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -38,7 +38,6 @@ use C4::Context; use C4::Installer; use C4::Dates; use Koha::Database; - use Koha; use MARC::Record; @@ -7304,6 +7303,7 @@ if ( CheckVersion($DBversion) ) { $dbh->{AutoCommit} = 1; $dbh->{RaiseError} = 0; + SetVersion ($DBversion); } $DBversion = "3.13.00.031"; @@ -8861,7 +8861,7 @@ if ( CheckVersion($DBversion) ) { $dbh->do("ALTER TABLE `biblioitems` CHANGE `cn_sort` `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL"); $dbh->do("ALTER TABLE `deletedbiblioitems` CHANGE `cn_sort` `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL"); print "Upgrade to $DBversion done (Bug 12424 - ddc sorting of call numbers truncates long Cutter parts)\n"; - SetVersion ($DBversion); + SetVersion($DBversion); } $DBversion = "3.17.00.030"; @@ -10406,6 +10406,7 @@ foreach my $file ( sort readdir $dirh ) { my $rv = $installer->load_sql( $update_dir . $file ) ? 0 : 1; } elsif ( $file =~ /\.perl$/ ) { do $update_dir . $file; + warn $@ if $@; } } -- 1.7.2.5