From 211e3950a08e651ac1d8c39d8d346f98a45b60e2 Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Wed, 11 Dec 2013 11:13:01 -0500
Subject: [PATCH] Bug 6427 [Part 13] - Add database updates

---
 installer/data/mysql/kohastructure.sql |  128 +++++++++++++++--------
 installer/data/mysql/updatedatabase.pl |  176 ++++++++++++++++++++++++++++++++
 2 files changed, 260 insertions(+), 44 deletions(-)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 07160c8..1cd0d95 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,89 @@ 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
+    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)
+) 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;
+
+--
+-- 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
+    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)
+) 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;
+
+--
+-- 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..c1746b7 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,180 @@ 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,
+            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)
+        ) 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,
+            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)
+        ) 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;
+    ");
+    $dbh->do("
+        ALTER TABLE `account_debits`
+          ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) 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