From bb69e70d1a5602927a4f6adc5d87e83bd51294ae Mon Sep 17 00:00:00 2001 From: Kyle M Hall 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. --- C4/Accounts.pm | 59 ++++++++++++++++++-------------- installer/data/mysql/kohastructure.sql | 1 + installer/data/mysql/updatedatabase.pl | 7 ++++ 3 files changed, 41 insertions(+), 26 deletions(-) diff --git a/C4/Accounts.pm b/C4/Accounts.pm index c3d121d..65eeed1 100644 --- a/C4/Accounts.pm +++ b/C4/Accounts.pm @@ -126,9 +126,9 @@ sub recordpayment { my $usth = $dbh->prepare( "INSERT INTO accountlines (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding) - VALUES (?,?,now(),?,'Payment,thanks','Pay',?)" + VALUES (?,?,now(),?,'Payment,thanks','Pay',?,?)" ); - $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, 0 - $amountleft ); + $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, 0 - $amountleft, $branch ); $usth->finish; UpdateStats( $branch, 'payment', $data, '', '', '', $borrowernumber, $nextaccntno ); $sth->finish; @@ -157,7 +157,7 @@ sub makepayment { #here we update both the accountoffsets and the account lines #updated to check, if they are paying off a lost item, we return the item # from their card, and put a note on the item record - my ( $borrowernumber, $accountno, $amount, $user, $branch ) = @_; + my ( $borrowernumber, $accountno, $amount, $branchcode, $branch ) = @_; my $dbh = C4::Context->dbh; my $manager_id = 0; $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv; @@ -201,17 +201,17 @@ sub makepayment { my $ins = $dbh->prepare( "INSERT - INTO accountlines (borrowernumber, accountno, date, amount, itemnumber, description, accounttype, amountoutstanding, manager_id) - VALUES ( ?, ?, now(), ?, ?, 'Payment,thanks', 'Pay', 0, ?)" + INTO accountlines (borrowernumber, accountno, date, amount, itemnumber, description, accounttype, amountoutstanding, manager_id, branch) + VALUES ( ?, ?, now(), ?, ?, 'Payment,thanks', 'Pay', 0, ?, ?)" ); - $ins->execute($borrowernumber, $nextaccntno, $payment, $data->{'itemnumber'}, $manager_id); + $ins->execute($borrowernumber, $nextaccntno, $payment, $data->{'itemnumber'}, $manager_id, $branchcode); $ins->finish; } # FIXME - The second argument to &UpdateStats is supposed to be the # branch code. # UpdateStats is now being passed $accountno too. MTJ - UpdateStats( $user, 'payment', $amount, '', '', '', $borrowernumber, + UpdateStats( $branchcode, 'payment', $amount, '', '', '', $borrowernumber, $accountno ); #from perldoc: for SELECT only #$sth->finish; @@ -284,6 +284,7 @@ sub chargelostitem{ # FIXME : if no replacement price, borrower just doesn't get charged? my $dbh = C4::Context->dbh(); my ($borrowernumber, $itemnumber, $amount, $description) = @_; + my $branchcode = C4::Context->userenv->{'branch'}; # first make sure the borrower hasn't already been charged for this item my $sth1=$dbh->prepare("SELECT * from accountlines @@ -298,10 +299,10 @@ 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) - VALUES (?,?,now(),?,?,'L',?,?)"); + (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding,itemnumber,branchcode) + VALUES (?,?,now(),?,?,'L',?,?,?)"); $sth2->execute($borrowernumber,$accountno,$amount, - $description,$amount,$itemnumber); + $description,$amount,$itemnumber,$branchcode); $sth2->finish; # FIXME: Log this ? } @@ -337,6 +338,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; @@ -387,16 +389,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 ); } return 0; } @@ -713,10 +715,12 @@ sub recordpayment_selectaccts { } # create new line - $sql = 'INSERT INTO accountlines ' . - '(borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding) ' . - q|VALUES (?,?,now(),?,'Payment,thanks','Pay',?)|; - $dbh->do($sql,{},$borrowernumber, $nextaccntno, 0 - $amount, 0 - $amountleft ); + $sql = " + INSERT INTO accountlines + ( borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, branch) + VALUES (?,?,now(),?,'Payment,thanks','Pay',?,?) + "; + $dbh->do($sql,{},$borrowernumber, $nextaccntno, 0 - $amount, 0 - $amountleft, $branch ); UpdateStats( $branch, 'payment', $amount, '', '', '', $borrowernumber, $nextaccntno ); return; } @@ -739,17 +743,20 @@ sub makepartialpayment { 'SELECT * FROM accountlines WHERE borrowernumber=? AND accountno=?',undef,$borrowernumber,$accountno); my $new_outstanding = $data->{amountoutstanding} - $amount; - my $update = 'UPDATE accountlines SET amountoutstanding = ? WHERE borrowernumber = ? ' - . ' AND accountno = ?'; + my $update = " + UPDATE accountlines SET amountoutstanding = ? WHERE borrowernumber = ? AND accountno = ? + "; $dbh->do( $update, undef, $new_outstanding, $borrowernumber, $accountno); # create new line - my $insert = 'INSERT INTO accountlines (borrowernumber, accountno, date, amount, ' - . 'description, accounttype, amountoutstanding, itemnumber, manager_id) ' - . ' VALUES (?, ?, now(), ?, ?, ?, 0, ?, ?)'; + my $insert = " + INSERT INTO accountlines (borrowernumber, accountno, date, amount, + description, accounttype, amountoutstanding, itemnumber, manager_id, branchcode) + VALUES (?, ?, now(), ?, ?, ?, 0, ?, ?, ?) + "; $dbh->do( $insert, undef, $borrowernumber, $nextaccntno, $amount, - "Payment, thanks - $user", 'Pay', $data->{'itemnumber'}, $manager_id); + "Payment, thanks - $user", 'Pay', $data->{'itemnumber'}, $manager_id, $branch); UpdateStats( $user, 'payment', $amount, '', '', '', $borrowernumber, $accountno ); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 67149c9d..516797f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2360,6 +2360,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. KEY `acctsborridx` (`borrowernumber`), KEY `timeidx` (`timestamp`), KEY `itemnumber` (`itemnumber`), diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index d2d9d97..dbfaa59 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4712,6 +4712,13 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.07.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE accountlines ADD branchcode VARCHAR( 10 ) NULL DEFAULT NULL"); + print "Upgrade to $DBversion done (Added column branchcode to accountlines)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) -- 1.7.2.5