This fix overdues to reformat the way to export in csv, create a function in C4::Overdues, and add a date filter.
Signed-off-by: Henri-Damien LAURENT <henridamien.laurent@biblibre.com>
&UpdateFine
&GetOverdueDelays
&GetOverduerules
+ &GetOverduesByBorrowers
&GetFine
&CreateItemAccountLine
&ReplacementCost2
return $sth->fetchall_arrayref({});
}
+=head2 GetOverduesByBorrowers
+
+@borrowers = GetOverduesByBorrowers();
+
+Returns an array with hashes, that contains all informations of borrowers, and another hash with overdues
+
+@borrower = [
+ {
+ 'surname' => ,
+ 'firstname' => ,
+ 'title' => ,
+ 'borrowernumber' => ,
+ 'address' => ,
+ 'city' => ,
+ 'zipcode' => ,
+ 'phone' => ,
+ 'email' => ,
+ 'branchcode' => ,
+ 'overdues' => {
+ 'biblionumber' => ,
+ 'title' => ,
+ 'author' => ,
+ 'issuedate' => ,
+ 'datedue' => ,
+ 'barcode' => ,
+ 'itemnumber' => ,
+ 'callnumber' => ,
+ }
+ }
+]
+
+=cut
+
+sub GetOverduesByBorrowers{
+ my ($branchcode, $category, $itemtype, $flags, $name, $order, $dateduefrom, $datedueto) = @_;
+
+ my @result = ();
+ my $dbh = C4::Context->dbh;
+
+ my $strsth = "
+ SELECT
+ borrowernumber,
+ surname,
+ firstname,
+ title,
+ CONCAT(borrowers.address, '\n', borrowers.address2) as address,
+ city,
+ zipcode,
+ email,
+ phone,
+ mobile,
+ phonepro,
+ branchcode
+ FROM
+ borrowers
+ WHERE borrowernumber IN (SELECT distinct(borrowernumber) FROM issues WHERE date_due < NOW() )
+ ";
+ $strsth.=" AND (borrowers.firstname like '".$name."%' or borrowers.surname like '".$name."%' or borrowers.cardnumber like '".$name."%')" if($name) ;
+ $strsth.=" AND borrowers.categorycode = '" . $category . "' " if $category;
+ $strsth.=" AND borrowers.flags = '" . $flags . "' " if $flags;
+ $strsth.=" AND borrowers.branchcode = '" . $branchcode . "' " if $branchcode;
+
+ $strsth.=" ORDER BY " . (
+ ($order eq "surname" or $order eq "surname desc") ? "$order" :
+ ($order eq "title" or $order eq "title desc") ? "$order, surname" :
+ "surname" # default sort order
+ );
+
+
+ my $strsthissues = "
+ SELECT
+ biblionumber,
+ issuedate,
+ date_due,
+ barcode,
+ itemnumber,
+ itemcallnumber,
+ title,
+ author
+ FROM
+ issues
+ LEFT JOIN items USING(itemnumber)
+ LEFT JOIN biblio USING(biblionumber)
+ LEFT JOIN biblioitems USING(biblionumber)
+ WHERE
+ borrowernumber = ?
+ ";
+
+ my @args;
+ my $itype = (C4::Context->preference("item-level_itypes")) ? "itype" : "itemtype" ;
+ if($itemtype){
+ $strsthissues .= " AND $itype = ? ";
+ push @args, $itemtype;
+ }
+ if($datedueto){
+ $strsthissues .= " AND date_due < ? ";
+ push @args, $datedueto;
+ }
+ if($dateduefrom){
+ $strsthissues .= " AND date_due > ? ";
+ push @args, $dateduefrom;
+ }
+ if(not ($datedueto or $dateduefrom)){
+ $strsthissues .= " AND date_due > NOW() ";
+ }
+ my $sthissues = $dbh->prepare($strsthissues);
+ my $sthbor = $dbh->prepare($strsth);
+ $sthbor->execute();
+
+ while (my $data=$sthbor->fetchrow_hashref) {
+ my $borrower = $data;
+ $sthissues->execute($data->{borrowernumber}, @args);
+
+ my @issues = ();
+ while(my $issuedata = $sthissues->fetchrow_hashref()){
+ push @issues, $issuedata;
+ }
+ $borrower->{overdues} = \@issues;
+ push @result, $borrower if scalar @{$borrower->{overdues}};
+ }
+ return \@result;
+}
=head2 checkoverdues
use CGI;
use C4::Auth;
use C4::Branch;
-use C4::Dates qw/format_date/;
+use C4::Overdues qw/GetOverduesByBorrowers/;
+use C4::Dates qw/format_date format_date_in_iso/;
use Date::Calc qw/Today/;
use Text::CSV_XS;
order => $order,
showall => $showall,
csv_param_string => $input->query_string(),
+ DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
+ dateduefrom => $input->param( 'dateduefrom' ),
+ datedueto => $input->param( 'datedueto' ),
);
my @sort_roots = qw(borrower title barcode date_due);
$bornamefilter =~s/\*/\%/g;
$bornamefilter =~s/\?/\_/g;
-my $strsth="SELECT date_due,
- surname,
- firstname,
- borrowers.title as borrowertitle,
- CONCAT(borrowers.address, '\n', borrowers.address2) as address,
- borrowers.city,
- borrowers.zipcode,
- borrowers.phone,
- borrowers.email,
- issues.itemnumber,
- issues.issuedate,
- items.barcode,
- items.itemcallnumber,
- biblio.title,
- biblio.author,
- borrowers.borrowernumber,
- biblio.biblionumber,
- borrowers.branchcode
- FROM issues
-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 1=1 "; # placeholder, since it is possible that none of the additional
- # conditions will be selected by user
-$strsth.=" AND date_due < NOW() " unless ($showall);
-$strsth.=" AND (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ;
-$strsth.=" AND borrowers.categorycode = '" . $borcatfilter . "' " if $borcatfilter;
-$strsth.=" AND biblioitems.itemtype = '" . $itemtypefilter . "' " if $itemtypefilter;
-$strsth.=" AND borrowers.flags = '" . $borflagsfilter . "' " if $borflagsfilter;
-$strsth.=" AND borrowers.branchcode = '" . $branchfilter . "' " if $branchfilter;
-$strsth.=" ORDER BY " . (
- ($order eq "surname" or $order eq "surname desc") ? "$order, date_due" :
- ($order eq "title" or $order eq "title desc") ? "$order, date_due, surname" :
- ($order eq "barcode" or $order eq "barcode desc") ? "items.$order, date_due, surname" :
- ($order eq "date_due desc") ? "date_due DESC, surname" :
- "date_due, surname" # default sort order
-);
-$template->param(sql=>$strsth);
-my $sth=$dbh->prepare($strsth);
-#warn "overdue.pl : query string ".$strsth;
-$sth->execute();
-
-my @overduedata;
-while (my $data=$sth->fetchrow_hashref) {
- push @overduedata, {
- issuedate => format_date($data->{issuedate}),
- duedate => format_date($data->{date_due}),
- surname => $data->{surname},
- firstname => $data->{firstname},
- borrowertitle => $data->{borrowertitle},
- borrowernumber => $data->{borrowernumber},
- barcode => $data->{barcode},
- itemnum => $data->{itemnumber},
- itemcallnumber => $data->{itemcallnumber},
- address => $data->{address},
- city => $data->{city},
- zipcode => $data->{zipcode},
- phone => $data->{phone},
- email => $data->{email},
- biblionumber => $data->{biblionumber},
- title => $data->{title},
- author => $data->{author},
- branchcode => $data->{branchcode},
- };
-}
+my $dateduefrom = format_date_in_iso($input->param( 'dateduefrom' ));
+my $datedueto = format_date_in_iso($input->param( 'datedueto' ));
+
+my @overduedata = @{GetOverduesByBorrowers($branchfilter, $borcatfilter, $itemtypefilter, $borflagsfilter, $bornamefilter, $order, $dateduefrom, $datedueto)};
$template->param(
todaysdate => format_date($todaysdate),
- overdueloop => \@overduedata
+ overdueloop => \@overduedata
);
# download the complete CSV
my @lines = ();
# build header ...
- my @keys = sort keys %{ $overdues->[0] };
+ my @keys = (
+ 'borrowernumber',
+ 'title',
+ 'firstname',
+ 'surname',
+ 'address',
+ 'city',
+ 'zipcode',
+ 'phone',
+ 'email',
+ 'branchcode',
+ 'overdues'
+ );
+
my $csv = Text::CSV_XS->new({
binary => 1,
sep_char => C4::Context->preference("delimiter") ?
# ... and rest of report
foreach my $overdue ( @{ $overdues } ) {
- push @lines, $csv->string() if $csv->combine(map { $overdue->{$_} } @keys);
+ my $issues;
+ foreach my $issue ( @{$overdue->{overdues} }){
+ $issues .= "$issue->{title} / $issue->{author} / $issue->{itemcallnumber} / $issue->{barcode} / ".format_date($issue->{issuedate}). " - " . format_date($issue->{date_due}) . " \r\n";
+ }
+ push @lines, $csv->string() if $csv->combine(
+ $overdue->{borrowernumber},
+ $overdue->{title},
+ $overdue->{firstname},
+ $overdue->{surname},
+ $overdue->{address},
+ $overdue->{city},
+ $overdue->{zipcode},
+ $overdue->{phone},
+ $overdue->{email},
+ $overdue->{branchcode},
+ $issues,
+ );
}
return join("\n", @lines) . "\n";
<style type="text/css">
.sql {display:none;}
</style>
+<!-- TMPL_INCLUDE NAME="calendar.inc" -->
</head>
<body>
<!-- TMPL_INCLUDE NAME="header.inc" -->
<div class="searchresults">
<table id="overduest">
<thead><tr>
- <th>Due Date</th>
<th>Patron</th>
<th>Library</th>
<th>Title</th>
<tbody><!-- TMPL_LOOP NAME="overdueloop" -->
<tr>
- <td><!-- TMPL_VAR NAME="duedate" --></td>
<td><a href="/cgi-bin/koha/members/moremember.pl?borrowernumber=<!-- TMPL_VAR name="borrowernumber"-->"><!-- TMPL_VAR NAME="surname" --> <!-- TMPL_VAR NAME="firstname" --></a>
<!-- TMPL_IF NAME="email" -->[<a href="mailto:<!-- TMPL_VAR NAME="email" -->?subject=Overdue: <!-- TMPL_VAR NAME="title" -->">email</a>]<!-- /TMPL_IF -->
(<!--TMPL_IF NAME="phone" --><!-- TMPL_VAR NAME="phone" --><!-- TMPL_ELSIF NAME="mobile" --><!-- TMPL_VAR NAME="mobile" --><!-- TMPL_ELSIF NAME="phonepro" --><!-- TMPL_VAR NAME="phonepro" --><!-- /TMPL_IF -->)</td>
<td><!-- TMPL_VAR name="branchcode" --></td>
- <td><!-- TMPL_IF name="BiblioDefaultViewmarc" -->
-<a href="/cgi-bin/koha/catalogue/MARCdetail.pl?biblionumber=<!-- TMPL_VAR NAME="biblionumber" ESCAPE="URL" -->"><!-- TMPL_VAR NAME="title" escape="html" --></a>
-<!-- TMPL_ELSIF NAME="BiblioDefaultViewisbd" -->
-<a href="/cgi-bin/koha/catalogue/ISBDdetail.pl?biblionumber=<!-- TMPL_VAR NAME="biblionumber" ESCAPE="URL" -->"><!-- TMPL_VAR NAME="title" escape="html" --></a>
-<!-- TMPL_ELSE -->
-<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=<!-- TMPL_VAR NAME="biblionumber" ESCAPE="URL" -->"><!-- TMPL_VAR NAME="title" escape="html" --> <!-- TMPL_VAR ESCAPE="HTML" NAME="subtitle" --></a><!-- /TMPL_IF --> <!-- TMPL_IF NAME="author" -->, by <!-- TMPL_VAR ESCAPE="HTML" NAME="author" --><!-- /TMPL_IF -->
+ <td>
+ <!-- TMPL_LOOP NAME="overdues" -->
+ <!-- TMPL_IF name="BiblioDefaultViewmarc" -->
+ <a href="/cgi-bin/koha/catalogue/MARCdetail.pl?biblionumber=<!-- TMPL_VAR NAME="biblionumber" ESCAPE="URL" -->">
+ <!-- TMPL_ELSIF NAME="BiblioDefaultViewisbd" -->
+ <a href="/cgi-bin/koha/catalogue/ISBDdetail.pl?biblionumber=<!-- TMPL_VAR NAME="biblionumber" ESCAPE="URL" -->">
+ <!-- TMPL_ELSE -->
+ <a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=<!-- TMPL_VAR NAME="biblionumber" ESCAPE="URL" -->">
+ <!-- /TMPL_IF -->
+ <!-- TMPL_VAR NAME="title" ESCAPE="HTML" -->
+ <!-- TMPL_VAR ESCAPE="HTML" NAME="subtitle" -->
+
+ </a>
+ <!-- TMPL_IF NAME="author" -->, by <!-- TMPL_VAR ESCAPE="HTML" NAME="author" --><!-- /TMPL_IF -->
+ <br/>
+ <!-- /TMPL_LOOP -->
</td>
</tr>
<!-- /TMPL_LOOP --></tbody>
<fieldset class="brief">
<h4>Filter On:</h4>
<ol>
+ <li style="border: dashed; border-width:1px;">Date due:
+ <label for="dateduefrom">From:
+ <img src="<!-- TMPL_VAR Name="themelang" -->/lib/calendar/cal.gif" id="dateduefrom_button" alt="Show Calendar" />
+ </label>
+ <input type="text" id="dateduefrom" name="dateduefrom" size="20" value="<!-- TMPL_VAR NAME="dateduefrom" -->" />
+ <script language="JavaScript" type="text/javascript">
+ Calendar.setup(
+ {
+ inputField : "dateduefrom",
+ ifFormat : "<!-- TMPL_VAR NAME="DHTMLcalendar_dateformat" -->",
+ button : "dateduefrom_button"
+ }
+ );
+ </script>
+
+ <label for="datedueto">To:
+ <img src="<!-- TMPL_VAR Name="themelang" -->/lib/calendar/cal.gif" id="datedueto_button" alt="Show Calendar" />
+ </label>
+ <input type="text" id="datedueto" name="datedueto" size="20" value="<!-- TMPL_VAR NAME="datedueto" -->" />
+ <script language="JavaScript" type="text/javascript">
+ Calendar.setup(
+ {
+ inputField : "datedueto",
+ ifFormat : "<!-- TMPL_VAR NAME="DHTMLcalendar_dateformat" -->",
+ button : "datedueto_button"
+ }
+ );
+ </script>
+ </li>
<li><label>Name or cardnumber:</label><input type="text" name="borname" value="<!--TMPL_VAR Name="borname"-->" /></li>
<li><label>Patron category:</label><select name="borcat" id="borcat"><option value="">Any</option>
<!-- TMPL_LOOP name="borcatloop" -->
projects / koha.git / commitdiff