From 9222f7a6ca80d2a74cf09872e362b116835fc02b Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Thu, 13 Mar 2008 17:18:00 -0500 Subject: [PATCH] created old_issues and old_reserves tables The new tables have the same structure and constraints as the tables they archive with the following exceptions: * borrowernumber and biblionumber in old_reserves can be NULL * the FK constraints (e.g., for itemnumber) on old_reserves set the child column to NULL if the parent row is deleted instead of deleting the child row. * there is no FK constraint on old_issues.branchcode, allowing a branch to be deleted without changing archived requests. Some miscellaneous cleanup was done as part of this patch: * GetMemberIssuesAndFines (C4::Members) now uses bind variables * fixed POD for GetMemberIssuesAndFines Signed-off-by: Joshua Ferraro --- C4/Accounts.pm | 17 +--- C4/Circulation.pm | 108 +++++++++++++++++-------- C4/Items.pm | 15 ++-- C4/Members.pm | 61 +++++++------- C4/Overdues.pm | 22 +++-- C4/Reserves.pm | 106 ++++++++++++++++-------- catalogue/detail.pl | 2 +- catalogue/updateitem.pl | 2 +- cataloguing/additem.pl | 4 +- circ/bookcount.pl | 36 ++++++--- circ/overdue.pl | 3 +- circ/pendingreserves.pl | 3 +- installer/data/mysql/kohastructure.sql | 58 +++++++++++++ installer/data/mysql/updatedatabase.pl | 58 +++++++++++++ kohaversion.pl | 2 +- misc/cronjobs/longoverdue.pl | 2 +- misc/cronjobs/notifyMailsOp.pl | 2 - misc/cronjobs/overduenotices-30.pl | 4 +- misc/cronjobs/overduenotices-csv.pl | 4 +- misc/cronjobs/overduenotices.pl | 4 +- misc/cronjobs/reservefix.pl | 8 +- misc/cronjobs/update_items.pl | 2 +- opac/opac-reserve.pl | 2 +- reports/issues_avg_stats.pl | 22 ++--- reserve/request.pl | 2 +- serials/routing-preview.pl | 3 +- tools/inventory.pl | 2 +- 27 files changed, 372 insertions(+), 182 deletions(-) diff --git a/C4/Accounts.pm b/C4/Accounts.pm index e46e39df4a..f173225f41 100644 --- a/C4/Accounts.pm +++ b/C4/Accounts.pm @@ -23,8 +23,8 @@ use C4::Context; use C4::Stats; use C4::Members; use C4::Items; +use C4::Circulation; -#use C4::Circulation; use vars qw($VERSION @ISA @EXPORT); BEGIN { @@ -266,22 +266,13 @@ sub fixaccounts { EOT } -# FIXME - Never used, but not exported, either. sub returnlost { my ( $borrowernumber, $itemnum ) = @_; - my $dbh = C4::Context->dbh; + MarkIssueReturned( $borrowernumber, $itemnum ); my $borrower = C4::Members::GetMember( $borrowernumber, 'borrowernumber' ); - my $sth = $dbh->prepare( - "UPDATE issues SET returndate=now() WHERE - borrowernumber=? AND itemnumber=? AND returndate IS NULL" - ); - $sth->execute( $borrowernumber, $itemnum ); - $sth->finish; my @datearr = localtime(time); - my $date = - ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3]; - my $bor = -"$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}"; + my $date = ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3]; + my $bor = "$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}"; ModItem({ paidfor => "Paid for by $bor $date" }, undef, $itemnum); } diff --git a/C4/Circulation.pm b/C4/Circulation.pm index 82e868ca8c..85409364ae 100644 --- a/C4/Circulation.pm +++ b/C4/Circulation.pm @@ -75,6 +75,7 @@ BEGIN { # subs to deal with returns push @EXPORT, qw( &AddReturn + &MarkIssueReturned ); # subs to deal with transfers @@ -412,7 +413,6 @@ sub TooMany { my $query2 = "SELECT COUNT(*) FROM issues i, biblioitems s1, items s2 WHERE i.borrowernumber = ? - AND i.returndate IS NULL AND i.itemnumber = s2.itemnumber AND s1.biblioitemnumber = s2.biblioitemnumber"; if (C4::Context->preference('item-level_itypes')){ @@ -424,8 +424,7 @@ sub TooMany { my $sth3 = $dbh->prepare( 'SELECT COUNT(*) FROM issues - WHERE borrowernumber = ? - AND returndate IS NULL' + WHERE borrowernumber = ?' ); my $alreadyissued; @@ -599,7 +598,6 @@ sub itemissues { "SELECT * FROM issues LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber WHERE itemnumber = ? - AND returndate IS NULL " ); @@ -617,10 +615,9 @@ sub itemissues { # Find the last 3 people who borrowed this item. $sth2 = $dbh->prepare( - "SELECT * FROM issues + "SELECT * FROM old_issues LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber WHERE itemnumber = ? - AND returndate IS NOT NULL ORDER BY returndate DESC,timestamp DESC" ); @@ -1197,12 +1194,7 @@ sub AddReturn { # case of a return of document (deal with issues and holdingbranch) if ($doreturn) { - my $sth = - $dbh->prepare( - "UPDATE issues SET returndate = now() WHERE (borrowernumber = ?) AND (itemnumber = ?) AND (returndate IS NULL)" - ); - $sth->execute( $borrower->{'borrowernumber'}, - $iteminformation->{'itemnumber'} ); + MarkIssueReturned($borrower->{'borrowernumber'}, $iteminformation->{'itemnumber'}); $messages->{'WasReturned'} = 1; # FIXME is the "= 1" right? } @@ -1296,6 +1288,43 @@ sub AddReturn { return ( $doreturn, $messages, $iteminformation, $borrower ); } +=head2 MarkIssueReturned + +=over 4 + +MarkIssueReturned($borrowernumber, $itemnumber); + +=back + +Unconditionally marks an issue as being returned by +moving the C row to C and +setting C to the current date. + +Ideally, this function would be internal to C, +not exported, but it is currently needed by one +routine in C. + +=cut + +sub MarkIssueReturned { + my ($borrowernumber, $itemnumber) = @_; + + my $dbh = C4::Context->dbh; + # FIXME transaction + my $sth_upd = $dbh->prepare("UPDATE issues SET returndate = now() + WHERE borrowernumber = ? + AND itemnumber = ?"); + $sth_upd->execute($borrowernumber, $itemnumber); + my $sth_copy = $dbh->prepare("INSERT INTO old_issues SELECT * FROM issues + WHERE borrowernumber = ? + AND itemnumber = ?"); + $sth_copy->execute($borrowernumber, $itemnumber); + my $sth_del = $dbh->prepare("DELETE FROM issues + WHERE borrowernumber = ? + AND itemnumber = ?"); + $sth_del->execute($borrowernumber, $itemnumber); +} + =head2 FixOverduesOnReturn &FixOverduesOnReturn($brn,$itm, $exemptfine); @@ -1453,7 +1482,7 @@ sub GetItemIssue { "SELECT * FROM issues LEFT JOIN items ON issues.itemnumber=items.itemnumber WHERE - issues.itemnumber=? AND returndate IS NULL "); + issues.itemnumber=?"); $sth->execute($itemnumber); my $data = $sth->fetchrow_hashref; my $datedue = $data->{'date_due'}; @@ -1487,15 +1516,24 @@ sub GetItemIssues { # get today date my $today = POSIX::strftime("%Y%m%d", localtime); - my $sth = $dbh->prepare( - "SELECT * FROM issues - LEFT JOIN borrowers ON borrowers.borrowernumber - LEFT JOIN items ON items.itemnumber=issues.itemnumber - WHERE - issues.itemnumber=?".($history?"":" AND returndate IS NULL "). - "ORDER BY issues.date_due DESC" - ); - $sth->execute($itemnumber); + my $sql = "SELECT * FROM issues + JOIN borrowers USING (borrowernumber) + JOIN items USING (itemnumber) + WHERE issues.itemnumber = ? "; + if ($history) { + $sql .= "UNION ALL + SELECT * FROM old_issues + LEFT JOIN borrowers USING (borrowernumber) + JOIN items USING (itemnumber) + WHERE old_issues.itemnumber = ? "; + } + $sql .= "ORDER BY date_due DESC"; + my $sth = $dbh->prepare($sql); + if ($history) { + $sth->execute($itemnumber, $itemnumber); + } else { + $sth->execute($itemnumber); + } while ( my $data = $sth->fetchrow_hashref ) { my $datedue = $data->{'date_due'}; $datedue =~ s/-//g; @@ -1533,10 +1571,18 @@ sub GetBiblioIssues { LEFT JOIN biblioitems ON items.itemnumber = biblioitems.biblioitemnumber LEFT JOIN biblio ON biblio.biblionumber = items.biblioitemnumber WHERE biblio.biblionumber = ? - ORDER BY issues.timestamp + UNION ALL + SELECT old_issues.*,items.barcode,biblio.biblionumber,biblio.title, biblio.author,borrowers.cardnumber,borrowers.surname,borrowers.firstname + FROM old_issues + LEFT JOIN borrowers ON borrowers.borrowernumber = old_issues.borrowernumber + LEFT JOIN items ON old_issues.itemnumber = items.itemnumber + LEFT JOIN biblioitems ON items.itemnumber = biblioitems.biblioitemnumber + LEFT JOIN biblio ON biblio.biblionumber = items.biblioitemnumber + WHERE biblio.biblionumber = ? + ORDER BY timestamp "; my $sth = $dbh->prepare($query); - $sth->execute($biblionumber); + $sth->execute($biblionumber, $biblionumber); my @issues; while ( my $data = $sth->fetchrow_hashref ) { @@ -1581,8 +1627,7 @@ sub CanBookBeRenewed { my $sth1 = $dbh->prepare( "SELECT * FROM issues WHERE borrowernumber = ? - AND itemnumber = ? - AND returndate IS NULL" + AND itemnumber = ?" ); $sth1->execute( $borrowernumber, $itemnumber ); if ( my $data1 = $sth1->fetchrow_hashref ) { @@ -1672,8 +1717,7 @@ sub AddRenewal { my $sth = $dbh->prepare("SELECT * FROM issues WHERE borrowernumber=? - AND itemnumber=? - AND returndate IS NULL" + AND itemnumber=?" ); $sth->execute( $borrowernumber, $itemnumber ); my $issuedata = $sth->fetchrow_hashref; @@ -1684,8 +1728,7 @@ sub AddRenewal { my $renews = $issuedata->{'renewals'} + 1; $sth = $dbh->prepare("UPDATE issues SET date_due = ?, renewals = ? WHERE borrowernumber=? - AND itemnumber=? - AND returndate IS NULL" + AND itemnumber=?" ); $sth->execute( $datedue->output('iso'), $renews, $borrowernumber, $itemnumber ); $sth->finish; @@ -1728,8 +1771,7 @@ sub GetRenewCount { # FIXME - I think this function could be redone to use only one SQL call. my $sth = $dbh->prepare("select * from issues where (borrowernumber = ?) - and (itemnumber = ?) - and returndate is null"); + and (itemnumber = ?)"); $sth->execute($bornum,$itemno); my $data = $sth->fetchrow_hashref; $renewcount = $data->{'renewals'} if $data->{'renewals'}; @@ -1916,7 +1958,7 @@ sub AnonymiseIssueHistory { my $borrowernumber = shift; my $dbh = C4::Context->dbh; my $query = " - UPDATE issues + UPDATE old_issues SET borrowernumber = NULL WHERE returndate < '".$date."' AND borrowernumber IS NOT NULL diff --git a/C4/Items.pm b/C4/Items.pm index 2b892709f4..d0c0fb389a 100644 --- a/C4/Items.pm +++ b/C4/Items.pm @@ -1037,7 +1037,6 @@ sub GetItemsByBiblioitemnumber { # Foreach item, get circulation information my $sth2 = $dbh->prepare( "SELECT * FROM issues,borrowers WHERE itemnumber = ? - AND returndate is NULL AND issues.borrowernumber = borrowers.borrowernumber" ); $sth2->execute( $data->{'itemnumber'} ); @@ -1053,9 +1052,8 @@ sub GetItemsByBiblioitemnumber { } # else $sth2->finish; # Find the last 3 people who borrowed this item. - my $query2 = "SELECT * FROM issues, borrowers WHERE itemnumber = ? - AND issues.borrowernumber = borrowers.borrowernumber - AND returndate is not NULL + my $query2 = "SELECT * FROM old_issues, borrowers WHERE itemnumber = ? + AND old_issues.borrowernumber = borrowers.borrowernumber ORDER BY returndate desc,timestamp desc LIMIT 3"; $sth2 = $dbh->prepare($query2) || die $dbh->errstr; $sth2->execute( $data->{'itemnumber'} ) || die $sth2->errstr; @@ -1143,8 +1141,7 @@ sub GetItemsInfo { my $isth = $dbh->prepare( "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode FROM issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber - WHERE itemnumber = ? - AND returndate IS NULL" + WHERE itemnumber = ?" ); my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=? "); while ( my $data = $sth->fetchrow_hashref ) { @@ -1234,10 +1231,10 @@ sub GetItemsInfo { $data->{stack} = $lib; } # Find the last 3 people who borrowed this item. - my $sth2 = $dbh->prepare("SELECT * FROM issues,borrowers + my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers WHERE itemnumber = ? - AND issues.borrowernumber = borrowers.borrowernumber - AND returndate IS NOT NULL LIMIT 3"); + AND old_issues.borrowernumber = borrowers.borrowernumber + LIMIT 3"); $sth2->execute($data->{'itemnumber'}); my $ii = 0; while (my $data2 = $sth2->fetchrow_hashref()) { diff --git a/C4/Members.pm b/C4/Members.pm index f01fb4e3af..151c4159d5 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -530,14 +530,14 @@ LEFT JOIN categories on borrowers.categorycode=categories.categorycode =item GetMemberIssuesAndFines - ($borrowed, $due, $fine) = &GetMemberIssuesAndFines($borrowernumber); + ($overdue_count, $issue_count, $total_fines) = &GetMemberIssuesAndFines($borrowernumber); Returns aggregate data about items borrowed by the patron with the given borrowernumber. -C<&GetMemberIssuesAndFines> returns a three-element array. C<$borrowed> is the -number of books the patron currently has borrowed. C<$due> is the -number of overdue items the patron currently has borrowed. C<$fine> is +C<&GetMemberIssuesAndFines> returns a three-element array. C<$overdue_count> is the +number of overdue items the patron currently has borrowed. C<$issue_count> is the +number of books the patron currently has borrowed. C<$total_fines> is the total fine currently due by the borrower. =cut @@ -546,32 +546,29 @@ the total fine currently due by the borrower. sub GetMemberIssuesAndFines { my ( $borrowernumber ) = @_; my $dbh = C4::Context->dbh; - my $query = - "Select count(*) from issues where borrowernumber='$borrowernumber' and - returndate is NULL"; + my $query = "SELECT COUNT(*) FROM issues WHERE borrowernumber = ?"; $debug and warn $query."\n"; my $sth = $dbh->prepare($query); - $sth->execute; - my $data = $sth->fetchrow_hashref; + $sth->execute($borrowernumber); + my $issue_count = $sth->fetchrow_arrayref->[0]; $sth->finish; + $sth = $dbh->prepare( - "Select count(*) from issues where - borrowernumber='$borrowernumber' and date_due < now() and returndate is NULL" + "SELECT COUNT(*) FROM issues + WHERE borrowernumber = ? + AND date_due < now()" ); - $sth->execute; - my $data2 = $sth->fetchrow_hashref; + $sth->execute($borrowernumber); + my $overdue_count = $sth->fetchrow_arrayref->[0]; $sth->finish; - $sth = $dbh->prepare( - "Select sum(amountoutstanding) from accountlines where - borrowernumber='$borrowernumber'" - ); - $sth->execute; - my $data3 = $sth->fetchrow_hashref; + + $sth = $dbh->prepare("SELECT SUM(amountoutstanding) FROM accountlines WHERE borrowernumber = ?"); + $sth->execute($borrowernumber); + my $total_fines = $sth->fetchrow_arrayref->[0]; $sth->finish; - return ( $data2->{'count(*)'}, $data->{'count(*)'}, - $data3->{'sum(amountoutstanding)'} ); + return ($overdue_count, $issue_count, $total_fines); } =head2 @@ -965,7 +962,6 @@ sub GetPendingIssues { LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber WHERE borrowernumber=? - AND returndate IS NULL ORDER BY issues.issuedate" ); $sth->execute($borrowernumber); @@ -1011,12 +1007,19 @@ sub GetAllIssues { my $dbh = C4::Context->dbh; my $count = 0; my $query = - "Select *,items.timestamp AS itemstimestamp from - issues + "SELECT *,items.timestamp AS itemstimestamp + FROM issues LEFT JOIN items on items.itemnumber=issues.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber - where borrowernumber=? + WHERE borrowernumber=? + UNION ALL + SELECT *,items.timestamp AS itemstimestamp + FROM old_issues + LEFT JOIN items on items.itemnumber=old_issues.itemnumber + LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber + LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber + WHERE borrowernumber=? order by $order"; if ( $limit != 0 ) { $query .= " limit $limit"; @@ -1024,7 +1027,7 @@ sub GetAllIssues { #print $query; my $sth = $dbh->prepare($query); - $sth->execute($borrowernumber); + $sth->execute($borrowernumber, $borrowernumber); my @result; my $i = 0; while ( my $data = $sth->fetchrow_hashref ) { @@ -1037,6 +1040,10 @@ sub GetAllIssues { # large chunk of older issues data put into table oldissues # to speed up db calls for issuing items if ( C4::Context->preference("ReadingHistory") ) { + # FIXME oldissues (not to be confused with old_issues) is + # apparently specific to HLT. Not sure if the ReadingHistory + # syspref is still required, as old_issues by design + # is no longer checked with each loan. my $query2 = "SELECT * FROM oldissues LEFT JOIN items ON items.itemnumber=oldissues.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber @@ -1860,7 +1867,7 @@ sub GetBorrowersWithIssuesHistoryOlderThan { return unless $date; # date is mandatory. my $query = " SELECT count(borrowernumber) as n,borrowernumber - FROM issues + FROM old_issues WHERE returndate < ? AND borrowernumber IS NOT NULL GROUP BY borrowernumber diff --git a/C4/Overdues.pm b/C4/Overdues.pm index 777577b488..9db5c6df7a 100644 --- a/C4/Overdues.pm +++ b/C4/Overdues.pm @@ -116,17 +116,16 @@ sub Getoverdues { my $sth = (C4::Context->preference('item-level_itypes')) ? $dbh->prepare( "SELECT issues.*,items.itype as itemtype FROM issues - LEFT JOIN items USING (itemnumber) - WHERE date_due < now() - AND returndate IS NULL ORDER BY borrowernumber " ) + LEFT JOIN items USING (itemnumber) + WHERE date_due < now() + ORDER BY borrowernumber " ) : $dbh->prepare( "SELECT issues.*,biblioitems.itemtype,items.itype FROM issues - LEFT JOIN items USING (itemnumber) - LEFT JOIN biblioitems USING (biblioitemnumber) - WHERE date_due < now() - AND returndate IS - NULL ORDER BY borrowernumber " ); + LEFT JOIN items USING (itemnumber) + LEFT JOIN biblioitems USING (biblioitemnumber) + WHERE date_due < now() + ORDER BY borrowernumber " ); $sth->execute; my @results; @@ -162,7 +161,6 @@ sub checkoverdues { LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber WHERE issues.borrowernumber = ? - AND issues.returndate is NULL AND issues.date_due < ?" ); $sth->execute( $borrowernumber, $today ); @@ -1179,8 +1177,7 @@ sub GetOverduesForBranch { LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link LEFT JOIN branches ON branches.branchcode = issues.branchcode - WHERE ( issues.returndate is null) - AND ( accountlines.amountoutstanding != '0.000000') + WHERE ( accountlines.amountoutstanding != '0.000000') AND ( accountlines.accounttype = 'FU') AND (issues.branchcode = ?) AND (issues.date_due <= NOW()) @@ -1230,8 +1227,7 @@ sub GetOverduesForBranch { LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link LEFT JOIN branches ON branches.branchcode = issues.branchcode - WHERE ( issues.returndate is null ) - AND ( accountlines.amountoutstanding != '0.000000') + WHERE ( accountlines.amountoutstanding != '0.000000') AND ( accountlines.accounttype = 'FU') AND (issues.branchcode = ? AND items.location = ?) AND (issues.date_due <= NOW()) diff --git a/C4/Reserves.pm b/C4/Reserves.pm index df06370091..2588b04e7d 100644 --- a/C4/Reserves.pm +++ b/C4/Reserves.pm @@ -218,9 +218,7 @@ sub GetReservesFromBiblionumber { itemnumber, reservenotes FROM reserves - WHERE cancellationdate IS NULL - AND (found <> \'F\' OR found IS NULL) - AND biblionumber = ? + WHERE biblionumber = ? ORDER BY priority"; my $sth = $dbh->prepare($query); $sth->execute($biblionumber); @@ -289,8 +287,6 @@ sub GetReservesFromItemnumber { SELECT reservedate,borrowernumber,branchcode FROM reserves WHERE itemnumber=? - AND cancellationdate IS NULL - AND (found <> 'F' OR found IS NULL) "; my $sth_res = $dbh->prepare($query); $sth_res->execute($itemnumber); @@ -315,7 +311,6 @@ sub GetReservesFromBorrowernumber { SELECT * FROM reserves WHERE borrowernumber=? - AND cancellationdate IS NULL AND found =? ORDER BY reservedate "); @@ -325,8 +320,6 @@ sub GetReservesFromBorrowernumber { SELECT * FROM reserves WHERE borrowernumber=? - AND cancellationdate IS NULL - AND (found != 'F' or found is null) ORDER BY reservedate "); $sth->execute($borrowernumber); @@ -353,8 +346,6 @@ sub GetReserveCount { SELECT COUNT(*) AS counter FROM reserves WHERE borrowernumber = ? - AND cancellationdate IS NULL - AND (found != \'F\' OR found IS NULL) '; my $sth = $dbh->prepare($query); $sth->execute($borrowernumber); @@ -492,8 +483,7 @@ sub GetReserveFee { while ( my $itdata = $sth2->fetchrow_hashref ) { my $sth3 = $dbh->prepare( "SELECT * FROM issues - WHERE itemnumber = ? - AND returndate IS NULL" + WHERE itemnumber = ?" ); $sth3->execute( $itdata->{'itemnumber'} ); if ( my $isdata = $sth3->fetchrow_hashref ) { @@ -532,9 +522,8 @@ sub GetReservesToBranch { my $sth = $dbh->prepare( "SELECT borrowernumber,reservedate,itemnumber,timestamp FROM reserves - WHERE priority='0' AND cancellationdate is null - AND branchcode=? - AND found IS NULL " + WHERE priority='0' + AND branchcode=?" ); $sth->execute( $frombranch ); my @transreserv; @@ -559,7 +548,6 @@ sub GetReservesForBranch { my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate FROM reserves WHERE priority='0' - AND cancellationdate IS NULL AND found='W' "; if ($frombranch){ $query .= " AND branchcode=? "; @@ -725,6 +713,21 @@ sub CancelReserve { my $sth = $dbh->prepare($query); $sth->execute( $item, $borr ); $sth->finish; + $query = " + INSERT INTO old_reserves + SELECT * FROM reserves + WHERE itemnumber = ? + AND borrowernumber = ? + "; + $sth = $dbh->prepare($query); + $sth->execute( $item, $borr ); + $query = " + DELETE FROM reserves + WHERE itemnumber = ? + AND borrowernumber = ? + "; + $sth = $dbh->prepare($query); + $sth->execute( $item, $borr ); } else { # removing a reserve record.... @@ -736,7 +739,6 @@ sub CancelReserve { AND borrowernumber = ? AND cancellationdate IS NULL AND itemnumber IS NULL - AND (found <> 'F' OR found IS NULL) /; my $sth = $dbh->prepare($query); $sth->execute( $biblio, $borr ); @@ -749,8 +751,6 @@ sub CancelReserve { priority = 0 WHERE biblionumber = ? AND borrowernumber = ? - AND cancellationdate IS NULL - AND (found <> 'F' or found IS NULL) /; # update the database, removing the record... @@ -758,6 +758,23 @@ sub CancelReserve { $sth->execute( $biblio, $borr ); $sth->finish; + $query = qq/ + INSERT INTO old_reserves + SELECT * FROM reserves + WHERE biblionumber = ? + AND borrowernumber = ? + /; + $sth = $dbh->prepare($query); + $sth->execute( $biblio, $borr ); + + $query = qq/ + DELETE FROM reserves + WHERE biblionumber = ? + AND borrowernumber = ? + /; + $sth = $dbh->prepare($query); + $sth->execute( $biblio, $borr ); + # now fix the priority on the others.... _FixPriority( $priority, $biblio ); } @@ -781,12 +798,26 @@ sub ModReserve { SET cancellationdate=now() WHERE biblionumber = ? AND borrowernumber = ? - AND cancellationdate is NULL - AND (found <> 'F' or found is NULL) /; my $sth = $dbh->prepare($query); $sth->execute( $biblio, $borrower ); $sth->finish; + $query = qq/ + INSERT INTO old_reserves + SELECT * + FROM reserves + WHERE biblionumber = ? + AND borrowernumber = ? + /; + $sth = $dbh->prepare($query); + $sth->execute( $biblio, $borrower ); + $query = qq/ + DELETE FROM reserves + WHERE biblionumber = ? + AND borrowernumber = ? + /; + $sth = $dbh->prepare($query); + $sth->execute( $biblio, $borrower ); } else { @@ -794,8 +825,6 @@ sub ModReserve { UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL WHERE biblionumber = ? AND borrowernumber = ? - AND cancellationdate is NULL - AND (found <> 'F' or found is NULL) /; my $sth = $dbh->prepare($query); $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower); @@ -848,6 +877,23 @@ sub ModReserveFill { $sth->execute( $biblionumber, $resdate, $borrowernumber ); $sth->finish; + # move to old_reserves + $query = "INSERT INTO old_reserves + SELECT * FROM reserves + WHERE biblionumber = ? + AND reservedate = ? + AND borrowernumber = ? + "; + $sth = $dbh->prepare($query); + $sth->execute( $biblionumber, $resdate, $borrowernumber ); + $query = "DELETE FROM reserves + WHERE biblionumber = ? + AND reservedate = ? + AND borrowernumber = ? + "; + $sth = $dbh->prepare($query); + $sth->execute( $biblionumber, $resdate, $borrowernumber ); + # now fix the priority on the others (if the priority wasn't # already sorted!).... unless ( $priority == 0 ) { @@ -915,8 +961,6 @@ sub ModReserveAffect { itemnumber = ? WHERE borrowernumber = ? AND biblionumber = ? - AND reserves.cancellationdate IS NULL - AND (reserves.found <> 'F' OR reserves.found IS NULL) "; } else { @@ -929,8 +973,6 @@ sub ModReserveAffect { itemnumber = ? WHERE borrowernumber = ? AND biblionumber = ? - AND reserves.cancellationdate IS NULL - AND (reserves.found <> 'F' OR reserves.found IS NULL) "; } $sth = $dbh->prepare($query); @@ -977,8 +1019,7 @@ sub ModReserveMinusPriority { my $query = " UPDATE reserves SET priority = 0 , itemnumber = ? - WHERE cancellationdate IS NULL - AND borrowernumber=? + WHERE borrowernumber=? AND biblionumber=? "; my $sth_upd = $dbh->prepare($query); @@ -990,7 +1031,6 @@ sub ModReserveMinusPriority { SET priority = priority-1 WHERE biblionumber = ? AND priority > 0 - AND cancellationdate IS NULL "; $sth_upd = $dbh->prepare($query); $sth_upd->execute( $biblionumber ); @@ -1025,7 +1065,6 @@ sub _FixPriority { SET priority = 0 WHERE biblionumber = ? AND borrowernumber = ? - AND cancellationdate IS NULL AND found ='W' /; my $sth = $dbh->prepare($query); @@ -1043,8 +1082,7 @@ sub _FixPriority { SELECT borrowernumber, reservedate, constrainttype FROM reserves WHERE biblionumber = ? - AND cancellationdate IS NULL - AND ((found <> 'F' and found <> 'W') or found is NULL) + AND ((found <> 'W') or found is NULL) ORDER BY priority ASC /; my $sth = $dbh->prepare($query); @@ -1132,8 +1170,6 @@ sub _Findgroupreserve { AND reserves.borrowernumber = reserveconstraints.borrowernumber AND reserves.reservedate =reserveconstraints.reservedate ) OR reserves.constrainttype='a' ) - AND reserves.cancellationdate is NULL - AND (reserves.found <> 'F' or reserves.found is NULL) /; my $sth = $dbh->prepare($query); $sth->execute( $biblio, $bibitem ); diff --git a/catalogue/detail.pl b/catalogue/detail.pl index 2ee1b3740a..ac596706df 100755 --- a/catalogue/detail.pl +++ b/catalogue/detail.pl @@ -144,7 +144,7 @@ foreach my $item (@items) { } # FIXME: move this to a pm, check waiting status for holds - my $sth2 = $dbh->prepare("SELECT * FROM reserves WHERE borrowernumber=? AND itemnumber=? AND found='W' AND cancellationdate IS NULL"); + my $sth2 = $dbh->prepare("SELECT * FROM reserves WHERE borrowernumber=? AND itemnumber=? AND found='W'"); $sth2->execute($item->{ReservedForBorrowernumber},$item->{itemnumber}); while (my $wait_hashref = $sth2->fetchrow_hashref) { $item->{waitingdate} = format_date($wait_hashref->{waitingdate}); diff --git a/catalogue/updateitem.pl b/catalogue/updateitem.pl index d48f845fdb..24aef677c9 100755 --- a/catalogue/updateitem.pl +++ b/catalogue/updateitem.pl @@ -81,7 +81,7 @@ ModItem($item_changes, $biblionumber, $itemnumber); # a charge has been added # FIXME : if no replacement price, borrower just doesn't get charged? if ($itemlost==1) { - my $sth=$dbh->prepare("SELECT * FROM issues WHERE (itemnumber=? AND returndate IS NULL)"); + my $sth=$dbh->prepare("SELECT * FROM issues WHERE itemnumber=?"); $sth->execute($itemnumber); my $issues=$sth->fetchrow_hashref(); diff --git a/cataloguing/additem.pl b/cataloguing/additem.pl index f9d77ac942..79b2264311 100755 --- a/cataloguing/additem.pl +++ b/cataloguing/additem.pl @@ -133,7 +133,7 @@ if ($op eq "additem") { } elsif ($op eq "delitem") { #------------------------------------------------------------------------------- # check that there is no issue on this item before deletion. - my $sth=$dbh->prepare("select * from issues i where i.returndate is null and i.itemnumber=?"); + my $sth=$dbh->prepare("select * from issues i where i.itemnumber=?"); $sth->execute($itemnumber); my $onloan=$sth->fetchrow; $sth->finish(); @@ -142,7 +142,7 @@ if ($op eq "additem") { $nextop="additem"; } else { # check it doesnt have a waiting reserve - $sth=$dbh->prepare("SELECT * FROM reserves WHERE found = 'W' AND cancellationdate IS NULL AND itemnumber = ?"); + $sth=$dbh->prepare("SELECT * FROM reserves WHERE found = 'W' AND itemnumber = ?"); $sth->execute($itemnumber); my $reserve=$sth->fetchrow; if ($reserve){ diff --git a/circ/bookcount.pl b/circ/bookcount.pl index fd98d0159b..eeeff4173d 100755 --- a/circ/bookcount.pl +++ b/circ/bookcount.pl @@ -132,22 +132,27 @@ sub issuessince { my ( $itemnumber, $date ) = @_; my $dbh = C4::Context->dbh; my $sth = - $dbh->prepare( -"Select count(*) from issues where issues.itemnumber=? and issues.timestamp > ?" - ); - $sth->execute( $itemnumber, $date ); - my $count = $sth->fetchrow_hashref; + $dbh->prepare("SELECT SUM(count) FROM ( + SELECT COUNT(*) AS count FROM issues WHERE itemnumber = ? and timestamp > ? + UNION ALL + SELECT COUNT(*) AS count FROM old_issues WHERE itemnumber = ? and timestamp > ? + ) tmp"); + $sth->execute( $itemnumber, $date, $itemnumber, $date ); + my $count = $sth->fetchrow_arrayref->[0]; $sth->finish; - return ( $count->{'count(*)'} ); + return ( $count ); } sub issuesat { my ( $itemnumber, $brcd ) = @_; my $dbh = C4::Context->dbh; my $sth = - $dbh->prepare( - "Select count(*) from issues where itemnumber=? and branchcode = ?"); - $sth->execute( $itemnumber, $brcd ); + $dbh->prepare("SELECT SUM(count) FROM ( + SELECT COUNT(*) AS count FROM issues WHERE itemnumber = ? and branchcode = ? + UNION ALL + SELECT COUNT(*) AS count FROM old_issues WHERE itemnumber = ? and branchcode = ? + ) tmp"); + $sth->execute( $itemnumber, $brcd, $itemnumber, $brcd ); my ($count) = $sth->fetchrow_array; $sth->finish; return ($count); @@ -157,10 +162,12 @@ sub lastseenat { my ( $itm, $brc ) = @_; my $dbh = C4::Context->dbh; my $sth = - $dbh->prepare( -"Select max(timestamp) from issues where itemnumber=? and branchcode = ?" - ); - $sth->execute( $itm, $brc ); + $dbh->prepare("SELECT MAX(tstamp) FROM ( + SELECT MAX(timestamp) AS tstamp FROM issues WHERE itemnumber = ? and branchcode = ? + UNION ALL + SELECT MAX(timestamp) AS tstamp FROM old_issues WHERE itemnumber = ? and branchcode = ? + ) tmp"); + $sth->execute( $itm, $brc, $itm, $brc ); my ($date1) = $sth->fetchrow_array; $sth->finish; $sth = @@ -172,6 +179,9 @@ sub lastseenat { $sth->finish; #FIXME: MJR thinks unsafe + $date1 =~ s/-//g; + $date1 =~ s/://g; + $date1 =~ s/ //g; $date2 =~ s/-//g; $date2 =~ s/://g; $date2 =~ s/ //g; diff --git a/circ/overdue.pl b/circ/overdue.pl index 178d5816f2..83739085f5 100755 --- a/circ/overdue.pl +++ b/circ/overdue.pl @@ -152,7 +152,8 @@ LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber ) LEFT JOIN items ON (issues.itemnumber=items.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber ) -WHERE isnull(returndate) "; +WHERE 1=1 "; # placeholder, since it is possible that none of the additional + # conditions will be selected by user $strsth.= " && date_due<'".$todaysdate."' " unless ($showall); $strsth.=" && (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ; $strsth.=" && borrowers.categorycode = '".$borcatfilter."' " if($borcatfilter) ; diff --git a/circ/pendingreserves.pl b/circ/pendingreserves.pl index 0f5fc0e0ef..d05c8cfe5f 100755 --- a/circ/pendingreserves.pl +++ b/circ/pendingreserves.pl @@ -105,9 +105,8 @@ my $strsth = LEFT JOIN items ON items.biblionumber=reserves.biblionumber LEFT JOIN borrowers ON reserves.borrowernumber=borrowers.borrowernumber LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber - WHERE isnull(cancellationdate) + WHERE reserves.found is NULL $sqldatewhere - AND reserves.found is NULL AND reserves.itemnumber is NULL"; if (C4::Context->preference('IndependantBranches')){ diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index d6cac20fda..788bd913e1 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1344,6 +1344,7 @@ CREATE TABLE `notifys` ( -- -- Table structure for table `nozebra` -- + DROP TABLE IF EXISTS `nozebra`; CREATE TABLE `nozebra` ( `server` varchar(20) NOT NULL, @@ -1354,6 +1355,63 @@ CREATE TABLE `nozebra` ( KEY `value` (`server`,`value`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `old_issues` +-- + +DROP TABLE IF EXISTS `old_issues`; +CREATE TABLE `old_issues` ( + `borrowernumber` int(11) default NULL, + `itemnumber` int(11) default NULL, + `date_due` date default NULL, + `branchcode` varchar(10) default NULL, + `issuingbranch` varchar(18) default NULL, + `returndate` date default NULL, + `lastreneweddate` date default NULL, + `return` varchar(4) default NULL, + `renewals` tinyint(4) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `issuedate` date default NULL, + KEY `old_issuesborridx` (`borrowernumber`), + KEY `old_issuesitemidx` (`itemnumber`), + KEY `old_bordate` (`borrowernumber`,`timestamp`), + CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_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 `old_reserves` +-- +DROP TABLE IF EXISTS `old_reserves`; +CREATE TABLE `old_reserves` ( + `borrowernumber` int(11) default NULL, + `reservedate` date default NULL, + `biblionumber` int(11) default NULL, + `constrainttype` varchar(1) default NULL, + `branchcode` varchar(10) default NULL, + `notificationdate` date default NULL, + `reminderdate` date default NULL, + `cancellationdate` date default NULL, + `reservenotes` mediumtext, + `priority` smallint(6) default NULL, + `found` varchar(1) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `itemnumber` int(11) default NULL, + `waitingdate` date default NULL, + KEY `old_reserves_borrowernumber` (`borrowernumber`), + KEY `old_reserves_biblionumber` (`biblionumber`), + KEY `old_reserves_itemnumber` (`itemnumber`), + KEY `old_reserves_branchcode` (`branchcode`), + CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) + ON DELETE SET NULL ON UPDATE SET NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `opac_news` -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 92ad818e26..89de88e629 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -1116,6 +1116,64 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "3.00.00.062"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("CREATE TABLE `old_issues` ( + `borrowernumber` int(11) default NULL, + `itemnumber` int(11) default NULL, + `date_due` date default NULL, + `branchcode` varchar(10) default NULL, + `issuingbranch` varchar(18) default NULL, + `returndate` date default NULL, + `lastreneweddate` date default NULL, + `return` varchar(4) default NULL, + `renewals` tinyint(4) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `issuedate` date default NULL, + KEY `old_issuesborridx` (`borrowernumber`), + KEY `old_issuesitemidx` (`itemnumber`), + KEY `old_bordate` (`borrowernumber`,`timestamp`), + CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) + ON DELETE SET NULL ON UPDATE SET NULL + ) ENGINE=InnoDB DEFAULT CHARSET=utf8"); + $dbh->do("CREATE TABLE `old_reserves` ( + `borrowernumber` int(11) default NULL, + `reservedate` date default NULL, + `biblionumber` int(11) default NULL, + `constrainttype` varchar(1) default NULL, + `branchcode` varchar(10) default NULL, + `notificationdate` date default NULL, + `reminderdate` date default NULL, + `cancellationdate` date default NULL, + `reservenotes` mediumtext, + `priority` smallint(6) default NULL, + `found` varchar(1) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `itemnumber` int(11) default NULL, + `waitingdate` date default NULL, + KEY `old_reserves_borrowernumber` (`borrowernumber`), + KEY `old_reserves_biblionumber` (`biblionumber`), + KEY `old_reserves_itemnumber` (`itemnumber`), + KEY `old_reserves_branchcode` (`branchcode`), + CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) + ON DELETE SET NULL ON UPDATE SET NULL + ) ENGINE=InnoDB DEFAULT CHARSET=utf8"); + + # move closed transactions to old_* tables + $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL"); + $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL"); + $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'"); + $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'"); + + print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n"; + SetVersion ($DBversion); +} =item DropAllForeignKeys($table) diff --git a/kohaversion.pl b/kohaversion.pl index ba359b662f..e791f8d17d 100644 --- a/kohaversion.pl +++ b/kohaversion.pl @@ -10,7 +10,7 @@ use strict; sub kohaversion { - our $VERSION = "3.00.00.061"; + our $VERSION = "3.00.00.062"; # version needs to be set this way # so that it can be picked up by Makefile.PL # during install diff --git a/misc/cronjobs/longoverdue.pl b/misc/cronjobs/longoverdue.pl index b47923b3ea..56b834faf3 100755 --- a/misc/cronjobs/longoverdue.pl +++ b/misc/cronjobs/longoverdue.pl @@ -24,7 +24,7 @@ use C4::Items; my $dbh = C4::Context->dbh; -my $itemnos_sth=$dbh->prepare("SELECT items.itemnumber FROM issues,items WHERE items.itemnumber=issues.itemnumber AND DATE_SUB(CURDATE(),INTERVAL 90 DAY) > date_due AND returndate IS NULL AND (itemlost=0 OR itemlost IS NULL)"); +my $itemnos_sth=$dbh->prepare("SELECT items.itemnumber FROM issues,items WHERE items.itemnumber=issues.itemnumber AND DATE_SUB(CURDATE(),INTERVAL 90 DAY) > date_due AND (itemlost=0 OR itemlost IS NULL)"); # get itemnumbers of items more than 90 days overdue $itemnos_sth->execute(); diff --git a/misc/cronjobs/notifyMailsOp.pl b/misc/cronjobs/notifyMailsOp.pl index 720ffbc077..1293a87f7c 100755 --- a/misc/cronjobs/notifyMailsOp.pl +++ b/misc/cronjobs/notifyMailsOp.pl @@ -47,8 +47,6 @@ sub GetBorrowerNotifys{ AND (notifys.borrowernumber=issues.borrowernumber AND notifys.itemnumber=issues.itemnumber) AND - issues.returndate IS NULL - AND notifys.borrowernumber=? AND notify_send_date IS NULL"); $sth2->execute($borrowernumber); diff --git a/misc/cronjobs/overduenotices-30.pl b/misc/cronjobs/overduenotices-30.pl index 1fbc27980d..543f176be2 100755 --- a/misc/cronjobs/overduenotices-30.pl +++ b/misc/cronjobs/overduenotices-30.pl @@ -145,7 +145,6 @@ foreach my $branchcode (@branches) { WHERE items.itemnumber=issues.itemnumber AND biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? - AND returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN ? and ? "); my $rqoverduerules = $dbh->prepare("SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = ? "); $rqoverduerules->execute($branchcode); @@ -186,8 +185,7 @@ foreach my $branchcode (@branches) { my $strsth = " SELECT COUNT(*), issues.borrowernumber,firstname,surname,address,address2,city,zipcode, email, MIN(date_due) as longest_issue FROM issues,borrowers,categories - WHERE returndate IS NULL - AND issues.borrowernumber=borrowers.borrowernumber + WHERE issues.borrowernumber=borrowers.borrowernumber AND borrowers.categorycode=categories.categorycode "; $strsth .= "\n\tAND issues.branchcode='$branchcode' " if ($branchcode); $strsth .= "\n\tAND borrowers.categorycode='".$data->{categorycode}."' " if ($data->{categorycode}); diff --git a/misc/cronjobs/overduenotices-csv.pl b/misc/cronjobs/overduenotices-csv.pl index aa6df0ba64..feabaf7a3b 100755 --- a/misc/cronjobs/overduenotices-csv.pl +++ b/misc/cronjobs/overduenotices-csv.pl @@ -86,13 +86,13 @@ print OUTFILE "2title;2author;2barcode;2issue_date;2return_date;3title;3author;3 # set your own mail server name here my $dbh = C4::Context->dbh; -my $query = "SELECT COUNT(*), issues.borrowernumber,firstname,surname,address,address2,city,zipcode,email FROM issues,borrowers ,categories WHERE returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500 AND issues.borrowernumber=borrowers.borrowernumber and borrowers.categorycode=categories.categorycode and (categories.overduenoticerequired = 1)"; +my $query = "SELECT COUNT(*), issues.borrowernumber,firstname,surname,address,address2,city,zipcode,email FROM issues,borrowers ,categories WHERE TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500 AND issues.borrowernumber=borrowers.borrowernumber and borrowers.categorycode=categories.categorycode and (categories.overduenoticerequired = 1)"; $query .= " AND borrowers.branchcode=".$dbh->quote($branch) if $branch; $query .=" GROUP BY issues.borrowernumber"; my $sth = $dbh->prepare ($query); warn "Q : $query"; -my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode, issues.timestamp, issues.date_due FROM issues,items,biblio WHERE items.itemnumber=issues.itemnumber and biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? AND returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500"); +my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode, issues.timestamp, issues.date_due FROM issues,items,biblio WHERE items.itemnumber=issues.itemnumber and biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500"); $sth->execute; # diff --git a/misc/cronjobs/overduenotices.pl b/misc/cronjobs/overduenotices.pl index 9bfdecb32e..24e93f2e3e 100755 --- a/misc/cronjobs/overduenotices.pl +++ b/misc/cronjobs/overduenotices.pl @@ -91,8 +91,8 @@ unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , $smtpserver; # set your own mail server name here my $dbh = C4::Context->dbh; -my $sth = $dbh->prepare ("SELECT COUNT(*), issues.borrowernumber,firstname,surname,streetaddress,physstreet,city,zipcode,emailaddress FROM issues,borrowers,categories WHERE returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500 AND issues.borrowernumber=borrowers.borrowernumber and borrowers.categorycode=categories.categorycode and categories.overduenoticerequired=1 group by issues.borrowernumber"); -my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode FROM issues,items,biblio WHERE items.itemnumber=issues.itemnumber and biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? AND returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500"); +my $sth = $dbh->prepare ("SELECT COUNT(*), issues.borrowernumber,firstname,surname,streetaddress,physstreet,city,zipcode,emailaddress FROM issues,borrowers,categories WHERE TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500 AND issues.borrowernumber=borrowers.borrowernumber and borrowers.categorycode=categories.categorycode and categories.overduenoticerequired=1 group by issues.borrowernumber"); +my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode FROM issues,items,biblio WHERE items.itemnumber=issues.itemnumber and biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500"); $sth->execute; # diff --git a/misc/cronjobs/reservefix.pl b/misc/cronjobs/reservefix.pl index aac1cfd365..8e6d612e22 100755 --- a/misc/cronjobs/reservefix.pl +++ b/misc/cronjobs/reservefix.pl @@ -25,11 +25,11 @@ use Mail::Sendmail; my $dbh = C4::Context->dbh; my $message; # e-mail message -my $admin = "root@localhost"; #To -my @library = "root@localhost"; #From +my $admin = 'root@localhost'; #To +my @library = 'root@localhost'; #From # get biblionumbers of unfilled reserves -my $bibnos_sth=$dbh->prepare("SELECT DISTINCT biblionumber FROM reserves WHERE cancellationdate IS NULL AND found IS NULL AND priority>0"); -my $get_sth=$dbh->prepare("SELECT * FROM reserves WHERE biblionumber=? AND cancellationdate IS NULL AND found IS NULL ORDER BY reservedate,priority"); +my $bibnos_sth=$dbh->prepare("SELECT DISTINCT biblionumber FROM reserves WHERE found IS NULL AND priority>0"); +my $get_sth=$dbh->prepare("SELECT * FROM reserves WHERE biblionumber=? AND found IS NULL ORDER BY reservedate,priority"); # checking reservedate avoids overwriting legitimate duplicate reserves my $put_sth=$dbh->prepare("UPDATE reserves SET priority=? WHERE biblionumber=? AND borrowernumber=? AND reservedate=?"); my $count_sth=$dbh->prepare("SELECT COUNT(itemnumber) FROM items WHERE biblionumber=?"); diff --git a/misc/cronjobs/update_items.pl b/misc/cronjobs/update_items.pl index 2e43bdf373..bee893a45a 100755 --- a/misc/cronjobs/update_items.pl +++ b/misc/cronjobs/update_items.pl @@ -133,7 +133,7 @@ while (my $biblionumber=$biblionumber_sth->fetchrow) { my $item_data_hashref = $item_data_sth->fetchrow_hashref(); # create a new MARC::Field object and put a date_due in it (from issues table) - my $date_due_sth = $dbh->prepare("SELECT date_due FROM issues WHERE itemnumber=? AND returndate IS NULL"); + my $date_due_sth = $dbh->prepare("SELECT date_due FROM issues WHERE itemnumber=?"); $date_due_sth->execute($itemnumber); my ($date_due) = $date_due_sth->fetchrow(); $date_due = "0000-00-00" unless ($date_due); diff --git a/opac/opac-reserve.pl b/opac/opac-reserve.pl index dd60968bc3..e5226c148d 100755 --- a/opac/opac-reserve.pl +++ b/opac/opac-reserve.pl @@ -414,7 +414,7 @@ foreach my $biblioitemnumber (@biblioitemnumbers) { # FIXME: move this to a pm my $dbh = C4::Context->dbh; - my $sth2 = $dbh->prepare("SELECT * FROM reserves WHERE borrowernumber=? AND itemnumber=? AND found='W' AND cancellationdate IS NULL"); + my $sth2 = $dbh->prepare("SELECT * FROM reserves WHERE borrowernumber=? AND itemnumber=? AND found='W'"); $sth2->execute($item->{ReservedForBorrowernumber},$item->{itemnumber}); while (my $wait_hashref = $sth2->fetchrow_hashref) { $item->{waitingdate} = format_date($wait_hashref->{waitingdate}); diff --git a/reports/issues_avg_stats.pl b/reports/issues_avg_stats.pl index abcbd18d41..8d573f1de5 100755 --- a/reports/issues_avg_stats.pl +++ b/reports/issues_avg_stats.pl @@ -292,7 +292,7 @@ sub calculate { # warn "filtres ".@filters[1]; # warn "filtres ".@filters[2]; # warn "filtres ".@filters[3]; - $line = "issues.".$line if ($line=~/branchcode/) or ($line=~/timestamp/); + $line = "old_issues.".$line if ($line=~/branchcode/) or ($line=~/timestamp/); $line = "biblioitems.".$line if $line=~/itemtype/; $linefilter[0] = @$filters[0] if ($line =~ /timestamp/ ) ; @@ -311,7 +311,7 @@ sub calculate { $linefilter[0] = @$filters[12] if ($line =~ /sort2/ ) ; #warn "filtre lignes".$linefilter[0]." ".$linefilter[1]; # - $column = "issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/)); + $column = "old_issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/)); $column = "biblioitems.".$column if $column=~/itemtype/; my @colfilter ; $colfilter[0] = @$filters[0] if ($column =~ /timestamp/ ) ; @@ -354,7 +354,7 @@ sub calculate { } my $strsth; - $strsth .= "select distinctrow $linefield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strsth .= "select distinctrow $linefield FROM `old_issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=categorycode) WHERE old_issues.itemnumber=items.itemnumber AND old_issues.borrowernumber=borrowers.borrowernumber"; if (($line=~/timestamp/) or ($line=~/returndate/)){ if ($linefilter[1] and ($linefilter[0])){ @@ -376,7 +376,7 @@ sub calculate { } $strsth .=" group by $linefield"; $strsth .=" order by $lineorder"; - + my $sth = $dbh->prepare( $strsth ); $sth->execute; @@ -416,7 +416,7 @@ sub calculate { } my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strsth2 .= "select distinctrow $colfield FROM `old_issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=categorycode) WHERE old_issues.itemnumber=items.itemnumber AND old_issues.borrowernumber=borrowers.borrowernumber"; if (($column=~/timestamp/) or ($column=~/returndate/)){ if ($colfilter[1] and ($colfilter[0])){ @@ -483,22 +483,22 @@ sub calculate { # Processing average loanperiods $strcalc .= "SELECT $linefield, $colfield, "; - $strcalc .= " issuedate, returndate, timestamp, COUNT(*), date_due, issues.renewals, issuelength FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber AND returndate IS NOT NULL"; + $strcalc .= " issuedate, returndate, old_issues.timestamp, COUNT(*), date_due, old_issues.renewals, issuelength FROM `old_issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=categorycode) WHERE old_issues.itemnumber=items.itemnumber AND old_issues.borrowernumber=borrowers.borrowernumber"; @$filters[0]=~ s/\*/%/g if (@$filters[0]); - $strcalc .= " AND issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] ); + $strcalc .= " AND old_issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] ); @$filters[1]=~ s/\*/%/g if (@$filters[1]); - $strcalc .= " AND issues.timestamp < '" . @$filters[1] ."'" if ( @$filters[1] ); + $strcalc .= " AND old_issues.timestamp < '" . @$filters[1] ."'" if ( @$filters[1] ); @$filters[4]=~ s/\*/%/g if (@$filters[4]); - $strcalc .= " AND issues.returndate > '" . @$filters[4] ."'" if ( @$filters[4] ); + $strcalc .= " AND old_issues.returndate > '" . @$filters[4] ."'" if ( @$filters[4] ); @$filters[5]=~ s/\*/%/g if (@$filters[5]); - $strcalc .= " AND issues.returndate < '" . @$filters[5] ."'" if ( @$filters[5] ); + $strcalc .= " AND old_issues.returndate < '" . @$filters[5] ."'" if ( @$filters[5] ); @$filters[8]=~ s/\*/%/g if (@$filters[8]); $strcalc .= " AND borrowers.categorycode like '" . @$filters[8] ."'" if ( @$filters[8] ); @$filters[9]=~ s/\*/%/g if (@$filters[9]); $strcalc .= " AND biblioitems.itemtype like '" . @$filters[9] ."'" if ( @$filters[9] ); @$filters[10]=~ s/\*/%/g if (@$filters[10]); - $strcalc .= " AND issues.branchcode like '" . @$filters[10] ."'" if ( @$filters[10] ); + $strcalc .= " AND old_issues.branchcode like '" . @$filters[10] ."'" if ( @$filters[10] ); @$filters[11]=~ s/\*/%/g if (@$filters[11]); $strcalc .= " AND borrowers.sort1 like '" . @$filters[11] ."'" if ( @$filters[11] ); @$filters[12]=~ s/\*/%/g if (@$filters[12]); diff --git a/reserve/request.pl b/reserve/request.pl index dd78e9d169..6db630947b 100755 --- a/reserve/request.pl +++ b/reserve/request.pl @@ -356,7 +356,7 @@ foreach my $biblioitemnumber (@biblioitemnumbers) { } # FIXME: move this to a pm - my $sth2 = $dbh->prepare("SELECT * FROM reserves WHERE borrowernumber=? AND itemnumber=? AND found='W' AND cancellationdate IS NULL"); + my $sth2 = $dbh->prepare("SELECT * FROM reserves WHERE borrowernumber=? AND itemnumber=? AND found='W'"); $sth2->execute($item->{ReservedForBorrowernumber},$item->{itemnumber}); while (my $wait_hashref = $sth2->fetchrow_hashref) { $item->{waitingdate} = format_date($wait_hashref->{waitingdate}); diff --git a/serials/routing-preview.pl b/serials/routing-preview.pl index 3cc56776ad..7a33010d10 100755 --- a/serials/routing-preview.pl +++ b/serials/routing-preview.pl @@ -62,8 +62,7 @@ if($ok){ my $notes; my $title = $subs->{'bibliotitle'}; for(my $i=0;$i<$routing;$i++){ - my $sth = $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ? AND borrowernumber = ? - AND cancellationdate is NULL AND (found <> 'F' or found is NULL)"); + my $sth = $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ? AND borrowernumber = ?"); $sth->execute($biblio,$routinglist[$i]->{'borrowernumber'}); my $data = $sth->fetchrow_hashref; diff --git a/tools/inventory.pl b/tools/inventory.pl index e74a19242d..f123163819 100755 --- a/tools/inventory.pl +++ b/tools/inventory.pl @@ -114,7 +114,7 @@ if ($uploadbarcodes && length($uploadbarcodes)>0){ my $dbh=C4::Context->dbh; my $date = format_date_in_iso($input->param('setdate')) || C4::Dates->today('iso'); # warn "$date"; - my $strsth="select * from issues, items where items.itemnumber=issues.itemnumber and items.barcode =? and issues.returndate is null"; + my $strsth="select * from issues, items where items.itemnumber=issues.itemnumber and items.barcode =?"; my $qonloan = $dbh->prepare($strsth); $strsth="select * from items where items.barcode =? and issues.wthdrawn=1"; my $qwthdrawn = $dbh->prepare($strsth); -- 2.39.5