X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=circ%2Foverdue.pl;h=2360028e0ee6fe85faa186c0009ab8bcc2022d34;hb=846bef3f8590bf5c0cd253b5e1a2aa8a8517dc73;hp=760ba7ce869d7a3f5aa05be923f8391f31ccbfa1;hpb=606ecb532aa42562687bf0d05b4d6df6697300d7;p=koha.git diff --git a/circ/overdue.pl b/circ/overdue.pl index 760ba7ce86..2360028e0e 100755 --- a/circ/overdue.pl +++ b/circ/overdue.pl @@ -19,26 +19,26 @@ # Suite 330, Boston, MA 02111-1307 USA use strict; +# use warnings; # FIXME use C4::Context; use C4::Output; use CGI; use C4::Auth; use C4::Branch; -use C4::Date; +use C4::Dates qw/format_date/; +use Date::Calc qw/Today/; +use Text::CSV_XS; my $input = new CGI; -my $type = $input->param('type'); - -my $theme = $input->param('theme'); # only used if allowthemeoverride is set -my $order=$input->param('order'); -my $bornamefilter=$input->param('borname'); -my $borcatfilter=$input->param('borcat'); -my $itemtypefilter=$input->param('itemtype'); -my $borflagsfilter=$input->param('borflags') || " "; -my $branchfilter=$input->param('branch'); -my $showall=$input->param('showall'); -my $theme = $input->param('theme'); # only used if allowthemeoverride is set +my $order = $input->param( 'order' ) || ''; +my $showall = $input->param('showall'); +my $bornamefilter = $input->param( 'borname'); +my $borcatfilter = $input->param( 'borcat' ); +my $itemtypefilter = $input->param('itemtype'); +my $borflagsfilter = $input->param('borflags') || ""; +my $branchfilter = $input->param( 'branch' ); +my $op = $input->param( 'op' ) || ''; my ( $template, $loggedinuser, $cookie ) = get_template_and_user( { @@ -46,10 +46,11 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( query => $input, type => "intranet", authnotrequired => 0, - flagsrequired => { reports => 1, circulate => 1 }, + flagsrequired => { reports => 1, circulate => "circulate_remaining_permissions" }, debug => 1, } ); + my $dbh = C4::Context->dbh; my $req; @@ -57,146 +58,164 @@ $req = $dbh->prepare( "select categorycode, description from categories order by $req->execute; my @borcatloop; while (my ($catcode, $description) =$req->fetchrow) { - my $selected = 1 if $catcode eq $borcatfilter; - my %row =(value => $catcode, - selected => $selected, - catname => $description, - ); - push @borcatloop, \%row; + push @borcatloop, { + value => $catcode, + selected => $catcode eq $borcatfilter ? 1 : 0, + catname => $description, + }; } $req = $dbh->prepare( "select itemtype, description from itemtypes order by description"); $req->execute; my @itemtypeloop; while (my ($itemtype, $description) =$req->fetchrow) { - my $selected = 1 if $itemtype eq $itemtypefilter; - my %row =(value => $itemtype, - selected => $selected, + push @itemtypeloop, { + value => $itemtype, + selected => $itemtype eq $itemtypefilter ? 1 : 0, itemtypename => $description, - ); - push @itemtypeloop, \%row; + }; } my $onlymine=C4::Context->preference('IndependantBranches') && C4::Context->userenv && C4::Context->userenv->{flags}!=1 && C4::Context->userenv->{branch}; -my $branches = GetBranches($onlymine); -my @branchloop; -my @selectflags; -push @selectflags, " ";# -push @selectflags,"gonenoaddress";# -push @selectflags,"debarred";# -push @selectflags,"lost";# -my $CGIflags=CGI::scrolling_list( -name => 'borflags', - -id =>'borflags', - -values => \@selectflags, - -size => 1, - -multiple => 0 ); - -foreach my $thisbranch ( sort keys %$branches ) { - my %row = ( - value => $thisbranch, - branchname => $branches->{$thisbranch}->{'branchname'}, - selected => (C4::Context->userenv && $branches->{$thisbranch}->{'branchcode'} eq C4::Context->userenv->{'branch'}) - ); - push @branchloop, \%row; + +$branchfilter = C4::Context->userenv->{'branch'} if ($onlymine && !$branchfilter); + +$template->param( + branchloop => GetBranchesLoop($branchfilter, $onlymine), + branchfilter => $branchfilter, + borcatloop => \@borcatloop, + itemtypeloop => \@itemtypeloop, + borname => $bornamefilter, + order => $order, + showall => $showall, + csv_param_string => $input->query_string(), +); + +my @sort_roots = qw(borrower title barcode date_due); +push @sort_roots, map {$_ . " desc"} @sort_roots; +my @order_loop = ({selected => $order ? 0 : 1}); # initial blank row +foreach (@sort_roots) { + my $tmpl_name = $_; + $tmpl_name =~ s/\s/_/g; + push @order_loop, { + selected => $order eq $_ ? 1 : 0, + ordervalue => $_, + foo => $tmpl_name, + 'order_' . $tmpl_name => 1, + }; } -$branchfilter=C4::Context->userenv->{'branch'} if ($onlymine && !$branchfilter); - -$template->param( branchloop => \@branchloop ); -$template->param(borcatloop=> \@borcatloop, - itemtypeloop => \@itemtypeloop, - branchloop=> \@branchloop, - CGIflags => $CGIflags, - borname => $bornamefilter, - order => $order, - showall => $showall); - -my $duedate; -my $borrowernumber; -my $itemnum; -my $data1; -my $data2; -my $data3; -my $name; -my $phone; -my $email; -my $biblionumber; -my $title; -my $author; -my @datearr = localtime( time() ); -my $todaysdate = - ( 1900 + $datearr[5] ) . '-' - . sprintf( "%0.2d", ( $datearr[4] + 1 ) ) . '-' - . sprintf( "%0.2d", $datearr[3] ); +$template->param(ORDER_LOOP => \@order_loop); +my $todaysdate = sprintf("%-04.4d-%-02.2d-%02.2d", Today()); $bornamefilter =~s/\*/\%/g; $bornamefilter =~s/\?/\_/g; -my $strsth="select date_due,concat(surname,' ', firstname) as borrower, - borrowers.phone, borrowers.email,issues.itemnumber, items.barcode, biblio.title, biblio.author,borrowers.borrowernumber - from issues -LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber ) -LEFT JOIN items ON (issues.itemnumber=items.itemnumber) +my $strsth="SELECT date_due, + concat(surname,' ', firstname) as borrower, + borrowers.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 isnull(returndate) "; -$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) ; -$strsth.=" && biblioitems.itemtype = '".$itemtypefilter."' " if($itemtypefilter) ; -$strsth.=" && borrowers.flags = '".$borflagsfilter."' " if ($borflagsfilter ne " ") ; -$strsth.=" && issues.branchcode = '".$branchfilter."' " if($branchfilter) ; -if ($order eq "borrower"){ - $strsth.=" order by borrower,date_due " ; -} elsif ($order eq "title"){ - $strsth.=" order by title,date_due,borrower "; -} elsif ($order eq "barcode"){ - $strsth.=" order by items.barcode,date_due,borrower "; -}elsif ($order eq "borrower desc"){ - $strsth.=" order by borrower desc,date_due " ; -} elsif ($order eq "title desc"){ - $strsth.=" order by title desc,date_due,borrower "; -} elsif ($order eq "barcode desc"){ - $strsth.=" order by items.barcode desc,date_due,borrower "; -} elsif ($order eq "date_due desc"){ - $strsth.=" order by date_due desc,borrower "; -} else { - $strsth.=" order by date_due,borrower "; -} +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 "borrower" or $order eq "borrower desc") ? "$order, date_due" : + ($order eq "title" or $order eq "title desc") ? "$order, date_due, borrower" : + ($order eq "barcode" or $order eq "barcode desc") ? "items.$order, date_due, borrower" : + ($order eq "date_due desc") ? "date_due DESC, borrower" : + "date_due, borrower" # 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) { - $duedate=$data->{'date_due'}; - $duedate = format_date($duedate); - $itemnum=$data->{'itemnumber'}; - - $name=$data->{'borrower'}; - $phone=$data->{'phone'}; - $email=$data->{'email'}; - - $title=$data->{'title'}; - $author=$data->{'author'}; - push (@overduedata, { duedate => $duedate, - bornum => $data->{borrowernumber}, - barcode => $data->{barcode}, - itemnum => $itemnum, - name => $name, - phone => $phone, - email => $email, - biblionumber => $biblionumber, - title => $title, - author => $author }); - + push @overduedata, { + issuedate => format_date($data->{issuedate}), + duedate => format_date($data->{date_due}), + borrowernumber => $data->{borrowernumber}, + barcode => $data->{barcode}, + itemnum => $data->{itemnumber}, + itemcallnumber => $data->{itemcallnumber}, + name => $data->{borrower}, + 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}, + }; } $template->param( - todaysdate => $todaysdate, + todaysdate => format_date($todaysdate), overdueloop => \@overduedata ); +# download the complete CSV +if ($op eq 'csv') { + binmode(STDOUT, ":utf8"); + my $csv = build_csv(\@overduedata); + print $input->header(-type => 'application/vnd.sun.xml.calc', + -encoding => 'utf-8', + -attachment=>"overdues.csv", + -filename=>"overdues.csv" ); + print $csv; + exit; +} + output_html_with_http_headers $input, $cookie, $template->output; + + +sub build_csv { + my $overdues = shift; + + return "" if scalar(@$overdues) == 0; + + my @lines = (); + + # build header ... + my @keys = sort keys %{ $overdues->[0] }; + my $csv = Text::CSV_XS->new({ + sep_char => C4::Context->preference("delimiter") ? + C4::Context->preference("delimiter") : ';' , + }); + $csv->combine(@keys); + push @lines, $csv->string(); + + # ... and rest of report + foreach my $overdue ( @{ $overdues } ) { + push @lines, $csv->string() if $csv->combine(map { $overdue->{$_} } @keys); + } + + return join("\n", @lines) . "\n"; +}