5 # Copyright 2000-2002 Katipo Communications
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it under the
10 # terms of the GNU General Public License as published by the Free Software
11 # Foundation; either version 2 of the License, or (at your option) any later
14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
16 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License along with
19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
20 # Suite 330, Boston, MA 02111-1307 USA
31 my $type = $input->param('type');
33 my $theme = $input->param('theme'); # only used if allowthemeoverride is set
34 my $order=$input->param('order');
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 $showall=$input->param('showall');
41 my $theme = $input->param('theme'); # only used if allowthemeoverride is set
44 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
46 template_name => "circ/overdue.tmpl",
50 flagsrequired => { reports => 1, circulate => 1 },
54 my $dbh = C4::Context->dbh;
57 $req = $dbh->prepare( "select categorycode, description from categories order by description");
60 while (my ($catcode, $description) =$req->fetchrow) {
61 my $selected = 1 if $catcode eq $borcatfilter;
62 my %row =(value => $catcode,
63 selected => $selected,
64 catname => $description,
66 push @borcatloop, \%row;
69 $req = $dbh->prepare( "select itemtype, description from itemtypes order by description");
72 while (my ($itemtype, $description) =$req->fetchrow) {
73 my $selected = 1 if $itemtype eq $itemtypefilter;
74 my %row =(value => $itemtype,
75 selected => $selected,
76 itemtypename => $description,
78 push @itemtypeloop, \%row;
80 my $onlymine=C4::Context->preference('IndependantBranches') &&
81 C4::Context->userenv &&
82 C4::Context->userenv->{flags}!=1 &&
83 C4::Context->userenv->{branch};
84 my $branches = GetBranches($onlymine);
87 push @selectflags, " ";#
88 push @selectflags,"gonenoaddress";#
89 push @selectflags,"debarred";#
90 push @selectflags,"lost";#
91 my $CGIflags=CGI::scrolling_list( -name => 'borflags',
93 -values => \@selectflags,
97 foreach my $thisbranch ( sort keys %$branches ) {
100 branchname => $branches->{$thisbranch}->{'branchname'},
101 selected => (C4::Context->userenv && $branches->{$thisbranch}->{'branchcode'} eq C4::Context->userenv->{'branch'})
103 push @branchloop, \%row;
105 $branchfilter=C4::Context->userenv->{'branch'} if ($onlymine && !$branchfilter);
107 $template->param( branchloop => \@branchloop );
108 $template->param(borcatloop=> \@borcatloop,
109 itemtypeloop => \@itemtypeloop,
110 branchloop=> \@branchloop,
111 CGIflags => $CGIflags,
112 borname => $bornamefilter,
114 showall => $showall);
128 my @datearr = localtime( time() );
130 ( 1900 + $datearr[5] ) . '-'
131 . sprintf( "%0.2d", ( $datearr[4] + 1 ) ) . '-'
132 . sprintf( "%0.2d", $datearr[3] );
135 $bornamefilter =~s/\*/\%/g;
136 $bornamefilter =~s/\?/\_/g;
138 my $strsth="select date_due,concat(surname,' ', firstname) as borrower,
139 borrowers.phone, borrowers.email,issues.itemnumber, items.barcode, biblio.title, biblio.author,borrowers.borrowernumber
141 LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber )
142 LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
143 LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
144 LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber )
145 where isnull(returndate) ";
146 $strsth.= " && date_due<'".$todaysdate."' " unless ($showall);
147 $strsth.=" && (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ;
148 $strsth.=" && borrowers.categorycode = '".$borcatfilter."' " if($borcatfilter) ;
149 $strsth.=" && biblioitems.itemtype = '".$itemtypefilter."' " if($itemtypefilter) ;
150 $strsth.=" && borrowers.flags = '".$borflagsfilter."' " if ($borflagsfilter ne " ") ;
151 $strsth.=" && issues.branchcode = '".$branchfilter."' " if($branchfilter) ;
152 if ($order eq "borrower"){
153 $strsth.=" order by borrower,date_due " ;
154 } elsif ($order eq "title"){
155 $strsth.=" order by title,date_due,borrower ";
156 } elsif ($order eq "barcode"){
157 $strsth.=" order by items.barcode,date_due,borrower ";
158 }elsif ($order eq "borrower desc"){
159 $strsth.=" order by borrower desc,date_due " ;
160 } elsif ($order eq "title desc"){
161 $strsth.=" order by title desc,date_due,borrower ";
162 } elsif ($order eq "barcode desc"){
163 $strsth.=" order by items.barcode desc,date_due,borrower ";
164 } elsif ($order eq "date_due desc"){
165 $strsth.=" order by date_due desc,borrower ";
167 $strsth.=" order by date_due,borrower ";
169 my $sth=$dbh->prepare($strsth);
170 #warn "overdue.pl : query string ".$strsth;
174 while (my $data=$sth->fetchrow_hashref) {
175 $duedate=$data->{'date_due'};
176 $duedate = format_date($duedate);
177 $itemnum=$data->{'itemnumber'};
179 $name=$data->{'borrower'};
180 $phone=$data->{'phone'};
181 $email=$data->{'email'};
183 $title=$data->{'title'};
184 $author=$data->{'author'};
185 push (@overduedata, { duedate => $duedate,
186 bornum => $data->{borrowernumber},
187 barcode => $data->{barcode},
192 biblionumber => $biblionumber,
194 author => $author });
199 todaysdate => $todaysdate,
200 overdueloop => \@overduedata
203 output_html_with_http_headers $input, $cookie, $template->output;