From b4ffd9e37b3d7f2d83c702c7e2b323ddc778b38d Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Fri, 24 Feb 2012 14:26:05 -0500
Subject: [PATCH] Bug 7595 - Add branchcode to accountlines

Adds a branchcode column to the accountlines table. For payments,
this inserts the branchcode of the library where the payment was
recorded. For manual invoices, it insert the branchcode of the
library where the manual invoice was created.

NOTE: Rebased
---
 C4/Accounts.pm                         | 51 ++++++++++++++++++----------------
 C4/Circulation.pm                      | 18 ++++++------
 C4/Overdues.pm                         |  9 +++---
 C4/Reserves.pm                         |  6 ++--
 installer/data/mysql/kohastructure.sql |  1 +
 installer/data/mysql/updatedatabase.pl |  8 ++++++
 6 files changed, 53 insertions(+), 40 deletions(-)

diff --git a/C4/Accounts.pm b/C4/Accounts.pm
index c98013a..877db60 100644
--- a/C4/Accounts.pm
+++ b/C4/Accounts.pm
@@ -93,7 +93,8 @@ sub recordpayment {
     my $dbh        = C4::Context->dbh;
     my $newamtos   = 0;
     my $accdata    = "";
-    my $branch     = C4::Context->userenv->{'branch'};
+    my $branch     = C4::Context->userenv ?
+                         C4::Context->userenv->{'branch'} : undef;
     my $amountleft = $data;
     my $manager_id = 0;
     $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv;
@@ -149,13 +150,13 @@ sub recordpayment {
     # create new line
     my $usth = $dbh->prepare(
         "INSERT INTO accountlines
-  (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding,manager_id, note)
-  VALUES (?,?,now(),?,'',?,?,?,?)"
+  (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding,manager_id, note,branchcode)
+  VALUES (?,?,now(),?,'',?,?,?,?,?)"
     );
 
     my $paytype = "Pay";
     $paytype .= $sip_paytype if defined $sip_paytype;
-    $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, $paytype, 0 - $amountleft, $manager_id, $payment_note );
+    $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, $paytype, 0 - $amountleft, $manager_id, $payment_note, $branch );
     $usth->finish;
 
     UpdateStats({
@@ -244,10 +245,10 @@ sub makepayment {
         my $ins = 
             $dbh->prepare( 
                 "INSERT 
-                    INTO accountlines (borrowernumber, accountno, date, amount, itemnumber, description, accounttype, amountoutstanding, manager_id, note)
-                    VALUES ( ?, ?, now(), ?, ?, '', 'Pay', 0, ?, ?)"
+                    INTO accountlines (borrowernumber, accountno, date, amount, itemnumber, description, accounttype, amountoutstanding, manager_id, note, branchcode)
+                    VALUES ( ?, ?, now(), ?, ?, 'Payment,thanks', 'Pay', 0, ?, ?, ?)"
             );
-        $ins->execute($borrowernumber, $nextaccntno, $payment, $data->{'itemnumber'}, $manager_id, $payment_note);
+        $ins->execute($borrowernumber, $nextaccntno, $payment, $data->{'itemnumber'}, $manager_id, $payment_note,  C4::Context->userenv->{'branch'});
     }
 
     if ( C4::Context->preference("FinesLog") ) {
@@ -370,10 +371,11 @@ sub chargelostitem{
         #  process (or person) to update it, since we don't handle any defaults for replacement prices.
         my $accountno = getnextacctno($borrowernumber);
         my $sth2=$dbh->prepare("INSERT INTO accountlines
-        (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding,itemnumber,manager_id)
-        VALUES (?,?,now(),?,?,'L',?,?,?)");
-        $sth2->execute($borrowernumber,$accountno,$amount,
-        $description,$amount,$itemnumber,$manager_id);
+        (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding,itemnumber,manager_id,branchcode)
+        VALUES (?,?,now(),?,?,'L',?,?,?,?)");
+        my $branchcode = C4::Context->userenv ? C4::Context->userenv->{'branch'} : undef;
+        $sth2->execute( $borrowernumber, $accountno, $amount, $description,
+            $amount, $itemnumber, $manager_id, $branchcode );
 
         if ( C4::Context->preference("FinesLog") ) {
             logaction("FINES", 'CREATE', $borrowernumber, Dumper({
@@ -422,6 +424,7 @@ sub manualinvoice {
     my ( $borrowernumber, $itemnum, $desc, $type, $amount, $note ) = @_;
     my $manager_id = 0;
     $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv;
+    my $branchcode = C4::Context->userenv->{'branch'};
     my $dbh      = C4::Context->dbh;
     my $notifyid = 0;
     my $insert;
@@ -441,16 +444,16 @@ sub manualinvoice {
         $desc .= ' ' . $itemnum;
         my $sth = $dbh->prepare(
             'INSERT INTO  accountlines
-                        (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,notify_id, note, manager_id)
-        VALUES (?, ?, now(), ?,?, ?,?,?,?,?,?)');
-     $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid, $note, $manager_id) || return $sth->errstr;
+                        (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,notify_id, note, manager_id, branchcode)
+        VALUES (?, ?, now(), ?,?, ?,?,?,?,?,?,?)');
+     $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid, $note, $manager_id, $branchcode) || return $sth->errstr;
   } else {
     my $sth=$dbh->prepare("INSERT INTO  accountlines
-            (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,notify_id, note, manager_id)
-            VALUES (?, ?, now(), ?, ?, ?, ?,?,?,?)"
+            (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,notify_id, note, manager_id, branchcode)
+            VALUES (?, ?, now(), ?, ?, ?, ?,?,?,?,?)"
         );
         $sth->execute( $borrowernumber, $accountno, $amount, $desc, $type,
-            $amountleft, $notifyid, $note, $manager_id );
+            $amountleft, $notifyid, $note, $manager_id, $branchcode );
     }
 
     if ( C4::Context->preference("FinesLog") ) {
@@ -655,9 +658,9 @@ sub recordpayment_selectaccts {
 
     # create new line
     $sql = 'INSERT INTO accountlines ' .
-    '(borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding,manager_id,note) ' .
-    q|VALUES (?,?,now(),?,'','Pay',?,?,?)|;
-    $dbh->do($sql,{},$borrowernumber, $nextaccntno, 0 - $amount, 0 - $amountleft, $manager_id, $note );
+    '(borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding,manager_id,note,branchcode) ' .
+    q|VALUES (?,?,now(),?,'','Pay',?,?,?,?)|;
+    $dbh->do($sql,{},$borrowernumber, $nextaccntno, 0 - $amount, 0 - $amountleft, $manager_id, $note, $branch );
     UpdateStats({
                 branch => $branch,
                 type => 'payment',
@@ -701,7 +704,7 @@ sub makepartialpayment {
         'SELECT * FROM accountlines WHERE  accountlines_id=?',undef,$accountlines_id);
     my $new_outstanding = $data->{amountoutstanding} - $amount;
 
-    my $update = 'UPDATE  accountlines SET amountoutstanding = ?  WHERE   accountlines_id = ? ';
+    my $update = 'UPDATE accountlines SET amountoutstanding = ? WHERE accountlines_id = ? ';
     $dbh->do( $update, undef, $new_outstanding, $accountlines_id);
 
     if ( C4::Context->preference("FinesLog") ) {
@@ -719,11 +722,11 @@ sub makepartialpayment {
 
     # create new line
     my $insert = 'INSERT INTO accountlines (borrowernumber, accountno, date, amount, '
-    .  'description, accounttype, amountoutstanding, itemnumber, manager_id, note) '
-    . ' VALUES (?, ?, now(), ?, ?, ?, 0, ?, ?, ?)';
+    .  'description, accounttype, amountoutstanding, itemnumber, manager_id, note, branchcode) '
+    . ' VALUES (?, ?, now(), ?, ?, ?, 0, ?, ?, ?, ?)';
 
     $dbh->do(  $insert, undef, $borrowernumber, $nextaccntno, $amount,
-        '', 'Pay', $data->{'itemnumber'}, $manager_id, $payment_note);
+        '', 'Pay', $data->{'itemnumber'}, $manager_id, $payment_note, C4::Context->userenv->{'branch'});
 
     UpdateStats({
                 branch => $user,
diff --git a/C4/Circulation.pm b/C4/Circulation.pm
index 41b6dea..bffe9af 100644
--- a/C4/Circulation.pm
+++ b/C4/Circulation.pm
@@ -2323,9 +2323,9 @@ sub _FixAccountForLostAndReturned {
     $amountleft *= -1 if ($amountleft > 0);
     my $desc = "Item Returned " . $item_id;
     $usth = $dbh->prepare("INSERT INTO accountlines
-        (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
-        VALUES (?,?,now(),?,?,'CR',?)");
-    $usth->execute($data->{'borrowernumber'},$nextaccntno,0-$amount,$desc,$amountleft);
+        (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding,branchcode)
+        VALUES (?,?,now(),?,?,'CR',?,?)");
+    $usth->execute($data->{'borrowernumber'},$nextaccntno,0-$amount,$desc,$amountleft,C4::Context->userenv->{'branch'});
     if ($borrowernumber) {
         # FIXME: same as query above.  use 1 sth for both
         $usth = $dbh->prepare("INSERT INTO accountoffsets
@@ -2836,12 +2836,12 @@ sub AddRenewal {
         $sth = $dbh->prepare(
                 "INSERT INTO accountlines
                     (date, borrowernumber, accountno, amount, manager_id,
-                    description,accounttype, amountoutstanding, itemnumber)
-                    VALUES (now(),?,?,?,?,?,?,?,?)"
+                    description,accounttype, amountoutstanding, itemnumber, branch)
+                    VALUES (now(),?,?,?,?,?,?,?,?,?)"
         );
         $sth->execute( $borrowernumber, $accountno, $charge, $manager_id,
             "Renewal of Rental Item $item->{'title'} $item->{'barcode'}",
-            'Rent', $charge, $itemnumber );
+            'Rent', $charge, $itemnumber, C4::Context->userenv->{'branch'} );
     }
 
     # Send a renewal slip according to checkout alert preferencei
@@ -3081,11 +3081,11 @@ sub AddIssuingCharge {
         INSERT INTO accountlines
             (borrowernumber, itemnumber, accountno,
             date, amount, description, accounttype,
-            amountoutstanding, manager_id)
-        VALUES (?, ?, ?,now(), ?, 'Rental', 'Rent',?,?)
+            amountoutstanding, manager_id, branchcode)
+        VALUES (?, ?, ?,now(), ?, 'Rental', 'Rent',?,?,?)
     ";
     my $sth = $dbh->prepare($query);
-    $sth->execute( $borrowernumber, $itemnumber, $nextaccntno, $charge, $charge, $manager_id );
+    $sth->execute( $borrowernumber, $itemnumber, $nextaccntno, $charge, $charge, $manager_id, C4::Context->userenv->{'branch'} );
 }
 
 =head2 GetTransfers
diff --git a/C4/Overdues.pm b/C4/Overdues.pm
index 914168b..2a5be89 100644
--- a/C4/Overdues.pm
+++ b/C4/Overdues.pm
@@ -592,11 +592,12 @@ sub UpdateFine {
             my $desc = ( $type ? "$type " : '' ) . "$title $due";    # FIXEDME, avoid whitespace prefix on empty $type
 
             my $query = "INSERT INTO accountlines
-                         (borrowernumber,itemnumber,date,amount,description,accounttype,amountoutstanding,lastincrement,accountno)
-                         VALUES (?,?,now(),?,?,'FU',?,?,?)";
+                         (borrowernumber,itemnumber,date,amount,description,accounttype,amountoutstanding,lastincrement,accountno,branchcode)
+                         VALUES (?,?,now(),?,?,'FU',?,?,?,?)";
             my $sth2 = $dbh->prepare($query);
-            $debug and print STDERR "UpdateFine query: $query\nw/ args: $borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno\n";
-            $sth2->execute( $borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno );
+            my $branch = C4::Context->userenv ? C4::Context->userenv->{'branch'} : undef;
+            $debug and print STDERR "UpdateFine query: $query\nw/ args: $borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno, $branch\n";
+            $sth2->execute( $borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno, $branch );
         }
     }
     # logging action
diff --git a/C4/Reserves.pm b/C4/Reserves.pm
index b82387f..0e74ce2 100644
--- a/C4/Reserves.pm
+++ b/C4/Reserves.pm
@@ -185,13 +185,13 @@ sub AddReserve {
         my $nextacctno = &getnextacctno( $borrowernumber );
         my $query      = qq{
         INSERT INTO accountlines
-            (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
+            (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding,branchcode)
         VALUES
-            (?,?,now(),?,?,'Res',?)
+            (?,?,now(),?,?,'Res',?,?)
     };
         my $usth = $dbh->prepare($query);
         $usth->execute( $borrowernumber, $nextacctno, $fee,
-            "Reserve Charge - $title", $fee );
+            "Reserve Charge - $title", $fee, C4::Context->userenv->{'branch'} );
     }
 
     #if ($const eq 'a'){
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index a74d81a..03dcbe1 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2740,6 +2740,7 @@ CREATE TABLE `accountlines` (
   `notify_level` int(2) NOT NULL default 0,
   `note` text NULL default NULL,
   `manager_id` int(11) NULL,
+  `branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc.
   PRIMARY KEY (`accountlines_id`),
   KEY `acctsborridx` (`borrowernumber`),
   KEY `timeidx` (`timestamp`),
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index ca27cde..8fa3087 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -10035,6 +10035,13 @@ if ( CheckVersion($DBversion) ) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "3.19.00.XXX";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("ALTER TABLE accountlines ADD branchcode VARCHAR( 10 ) NULL DEFAULT NULL AFTER manager_id");
+    print "Upgrade to $DBversion done (Added column branchcode to accountlines)\n";
+    SetVersion($DBversion);
+}
+
 # DEVELOPER PROCESS, search for anything to execute in the db_update directory
 # SEE bug 13068
 # if there is anything in the atomicupdate, read and execute it.
@@ -10048,6 +10055,7 @@ while ( my $file = readdir $dirh ) {
     my $rv = $installer->load_sql( $update_dir . $file ) ? 0 : 1;
 }
 
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
-- 
1.9.1