4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
27 use C4::Dates qw/format_date/;
30 my $type = $input->param('type');
31 my $theme = $input->param('theme'); # only used if allowthemeoverride is set
32 my $order = $input->param('order');
33 my $showall = $input->param('showall');
35 my $bornamefilter = $input->param('borname');
36 my $borcatfilter = $input->param('borcat');
37 my $itemtypefilter = $input->param('itemtype');
38 my $borflagsfilter = $input->param('borflags') || " ";
39 my $branchfilter = $input->param('branch');
40 my $op = $input->param('op');
42 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
44 template_name => "circ/overdue.tmpl",
48 flagsrequired => { reports => 1, circulate => 1 },
52 my $dbh = C4::Context->dbh;
55 # download the complete CSV
57 warn "BRANCH : $branchfilter";
58 my $csv = `../misc/cronjobs/overduenotices-csv.pl -c -n -b $branchfilter`;
59 print $input->header(-type => 'application/vnd.sun.xml.calc',
61 -attachment=>"overdues.csv",
62 -filename=>"overdues.csv" );
67 $req = $dbh->prepare( "select categorycode, description from categories order by description");
70 while (my ($catcode, $description) =$req->fetchrow) {
71 my $selected = 1 if $catcode eq $borcatfilter;
72 my %row =(value => $catcode,
73 selected => $selected,
74 catname => $description,
76 push @borcatloop, \%row;
79 $req = $dbh->prepare( "select itemtype, description from itemtypes order by description");
82 while (my ($itemtype, $description) =$req->fetchrow) {
83 my $selected = 1 if $itemtype eq $itemtypefilter;
84 my %row =(value => $itemtype,
85 selected => $selected,
86 itemtypename => $description,
88 push @itemtypeloop, \%row;
90 my $onlymine=C4::Context->preference('IndependantBranches') &&
91 C4::Context->userenv &&
92 C4::Context->userenv->{flags}!=1 &&
93 C4::Context->userenv->{branch};
94 my $branches = GetBranches($onlymine);
97 push @selectflags, " ";#
98 push @selectflags,"gonenoaddress";#
99 push @selectflags,"debarred";#
100 push @selectflags,"lost";#
101 my $CGIflags=CGI::scrolling_list( -name => 'borflags',
103 -values => \@selectflags,
107 foreach my $thisbranch ( sort keys %$branches ) {
109 value => $thisbranch,
110 branchname => $branches->{$thisbranch}->{'branchname'},
111 selected => ($branches->{$thisbranch}->{'branchcode'} eq $branchfilter)
113 push @branchloop, \%row;
115 $branchfilter=C4::Context->userenv->{'branch'} if ($onlymine && !$branchfilter);
117 $template->param( branchloop => \@branchloop,
118 branchfilter => $branchfilter);
119 $template->param(borcatloop=> \@borcatloop,
120 itemtypeloop => \@itemtypeloop,
121 branchloop=> \@branchloop,
122 CGIflags => $CGIflags,
123 borname => $bornamefilter,
125 showall => $showall);
138 my @datearr = localtime( time() );
140 ( 1900 + $datearr[5] ) . '-'
141 . sprintf( "%0.2d", ( $datearr[4] + 1 ) ) . '-'
142 . sprintf( "%0.2d", $datearr[3] );
145 $bornamefilter =~s/\*/\%/g;
146 $bornamefilter =~s/\?/\_/g;
148 my $strsth="SELECT date_due,concat(surname,' ', firstname) as borrower,
149 borrowers.phone, borrowers.email,issues.itemnumber, items.barcode, biblio.title, biblio.author,borrowers.borrowernumber,biblio.biblionumber,borrowers.branchcode
151 LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber )
152 LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
153 LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
154 LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber )
155 WHERE 1=1 "; # placeholder, since it is possible that none of the additional
156 # conditions will be selected by user
157 $strsth.= " && date_due<'".$todaysdate."' " unless ($showall);
158 $strsth.=" && (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ;
159 $strsth.=" && borrowers.categorycode = '".$borcatfilter."' " if($borcatfilter) ;
160 $strsth.=" && biblioitems.itemtype = '".$itemtypefilter."' " if($itemtypefilter) ;
161 $strsth.=" && borrowers.flags = '".$borflagsfilter."' " if ($borflagsfilter ne " ") ;
162 $strsth.=" && borrowers.branchcode = '".$branchfilter."' " if($branchfilter) ;
163 if ($order eq "borrower"){
164 $strsth.=" ORDER BY borrower,date_due " ;
165 } elsif ($order eq "title"){
166 $strsth.=" ORDER BY title,date_due,borrower ";
167 } elsif ($order eq "barcode"){
168 $strsth.=" ORDER BY items.barcode,date_due,borrower ";
169 }elsif ($order eq "borrower DESC"){
170 $strsth.=" ORDER BY borrower desc,date_due " ;
171 } elsif ($order eq "title DESC"){
172 $strsth.=" ORDER BY title desc,date_due,borrower ";
173 } elsif ($order eq "barcode DESC"){
174 $strsth.=" ORDER BY items.barcode desc,date_due,borrower ";
175 } elsif ($order eq "date_due DESC"){
176 $strsth.=" ORDER BY date_due DESC,borrower ";
178 $strsth.=" ORDER BY date_due,borrower ";
180 my $sth=$dbh->prepare($strsth);
181 #warn "overdue.pl : query string ".$strsth;
185 while (my $data=$sth->fetchrow_hashref) {
186 $duedate=$data->{'date_due'};
187 $duedate = format_date($duedate);
188 $itemnum=$data->{'itemnumber'};
190 $name=$data->{'borrower'};
191 $phone=$data->{'phone'};
192 $email=$data->{'email'};
194 $title=$data->{'title'};
195 $author=$data->{'author'};
196 push (@overduedata, {
198 borrowernumber => $data->{borrowernumber},
199 barcode => $data->{barcode},
204 biblionumber => $data->{'biblionumber'},
207 branchcode => $data->{'branchcode'} });
211 todaysdate => $todaysdate,
212 overdueloop => \@overduedata
215 output_html_with_http_headers $input, $cookie, $template->output;