X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=circ%2Foverdue.pl;h=7ca21e5fa273d053a9d1d1d1a5c66003dc81e360;hb=b081ab0310dae5e0ab46d3b2186b2a51a41d5c31;hp=86e9501d49de7bfed33b9230ec0972eea3965f2e;hpb=827203132c99d2877db7f5625e66e6276c2c6cee;p=koha.git diff --git a/circ/overdue.pl b/circ/overdue.pl index 86e9501d49..7ca21e5fa2 100755 --- a/circ/overdue.pl +++ b/circ/overdue.pl @@ -19,6 +19,7 @@ # Suite 330, Boston, MA 02111-1307 USA use strict; +# use warnings; # FIXME use C4::Context; use C4::Output; use CGI; @@ -26,19 +27,18 @@ use C4::Auth; use C4::Branch; 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 $order = $input->param( 'order' ) || ''; my $showall = $input->param('showall'); -my $bornamefilter = $input->param('borname'); -my $borcatfilter = $input->param('borcat'); +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 $borflagsfilter = $input->param('borflags') || ""; +my $branchfilter = $input->param( 'branch' ); +my $op = $input->param( 'op' ) || ''; my ( $template, $loggedinuser, $cookie ) = get_template_and_user( { @@ -46,151 +46,124 @@ 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 $dbh = C4::Context->dbh; -# download the complete CSV -if ($op eq 'csv') { -warn "BRANCH : $branchfilter"; - my $csv = `../misc/cronjobs/overduenotices-csv.pl -c -n -b $branchfilter`; - print $input->header(-type => 'application/vnd.sun.xml.calc', - -encoding => 'utf-8', - -attachment=>"overdues.csv", - -filename=>"overdues.csv" ); - print $csv; - exit; -} my $req; $req = $dbh->prepare( "select categorycode, description from categories order by description"); $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; - -foreach my $thisbranch ( sort keys %$branches ) { - my %row = ( - value => $thisbranch, - branchname => $branches->{$thisbranch}->{'branchname'}, - selected => ($branches->{$thisbranch}->{'branchcode'} eq $branchfilter) - ); - 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, - branchfilter => $branchfilter); -$template->param(borcatloop=> \@borcatloop, - itemtypeloop => \@itemtypeloop, - branchloop=> \@branchloop, - 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 $title; -my $author; +$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,biblio.biblionumber,borrowers.branchcode +my $strsth="SELECT date_due, + concat(surname,' ', firstname) as borrower, + borrowers.phone, + borrowers.email, + issues.itemnumber, + items.barcode, + 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 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 ) +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.= " && 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.=" && borrowers.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 "; -} +$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, - borrowernumber => $data->{borrowernumber}, - barcode => $data->{barcode}, - itemnum => $itemnum, - name => $name, - phone => $phone, - email => $email, - biblionumber => $data->{'biblionumber'}, - title => $title, - author => $author, - branchcode => $data->{'branchcode'} }); + push @overduedata, { + duedate => format_date($data->{date_due}), + borrowernumber => $data->{borrowernumber}, + barcode => $data->{barcode}, + itemnum => $data->{itemnumber}, + name => $data->{borrower}, + phone => $data->{phone}, + email => $data->{email}, + biblionumber => $data->{biblionumber}, + title => $data->{title}, + author => $data->{author}, + branchcode => $data->{branchcode}, + }; } $template->param( @@ -198,4 +171,41 @@ $template->param( 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"; +}