From 9b017a1a165c5d50909c92f5cf659094aa8998bb Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Wed, 11 Dec 2013 11:13:01 -0500 Subject: [PATCH] Bug 6427 [Part 13] - Add database updates --- installer/data/mysql/kohastructure.sql | 134 ++++++++++++++++-------- installer/data/mysql/updatedatabase.pl | 182 ++++++++++++++++++++++++++++++++ 2 files changed, 272 insertions(+), 44 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 07160c8..4453833 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`), @@ -2678,50 +2679,6 @@ CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- 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; - --- --- 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; - --- -- Table structure for table `action_logs` -- @@ -3387,6 +3344,95 @@ CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- 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 + 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 19893b4..055a9e6 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -37,6 +37,7 @@ use Getopt::Long; use C4::Context; use C4::Installer; use C4::Dates; +use Koha::Database; use MARC::Record; use MARC::File::XML ( BinaryEncoding => 'utf8' ); @@ -7259,6 +7260,7 @@ if ( CheckVersion($DBversion) ) { $dbh->{AutoCommit} = 1; $dbh->{RaiseError} = 0; + SetVersion ($DBversion); } $DBversion = "3.13.00.031"; @@ -7824,6 +7826,186 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.15.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q{ + ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST + }); + $dbh->do(q{ + ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; + }); + $dbh->do(q{ + UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC + }); + + $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, + 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"; + SetVersion ($DBversion); +} =head1 FUNCTIONS -- 1.7.2.5