From 6316c053c40fea49f0d1258726169ae50fea6587 Mon Sep 17 00:00:00 2001 From: Jonathan Druart <jonathan.druart@biblibre.com> Date: Wed, 22 Feb 2012 17:24:26 +0100 Subject: [PATCH 1/1] Bug 7298: Export late orders as csv --- C4/Acquisition.pm | 33 +++++++-- acqui/lateorders-excel.pl | 65 +++++++++++++++++ acqui/lateorders.pl | 2 +- .../prog/en/modules/acqui/lateorders.tt | 77 +++++++++++++------- 4 files changed, 141 insertions(+), 36 deletions(-) create mode 100755 acqui/lateorders-excel.pl diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 0a96a02..7be1ba6 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -842,17 +842,34 @@ C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha d sub GetOrder { my ($ordernumber) = @_; my $dbh = C4::Context->dbh; - my $query = " - SELECT biblioitems.*, biblio.*, aqorders.* - FROM aqorders - LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber - LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber - WHERE aqorders.ordernumber=? - - "; + my $query = qq{SELECT biblio.*,biblioitems.*, + aqorders.*, + aqbudgets.*, + aqbasket.*, + aqorders.rrp AS unitpricesupplier, + aqorders.ecost AS unitpricelib, + aqorders.claims_count AS claims_count, + aqorders.claimed_date AS claimed_date, + aqbudgets.budget_name AS budget, + aqbooksellers.name AS supplier, + aqbooksellers.id AS supplierid, + biblioitems.publishercode AS publisher, + ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, + DATE(aqbasket.closedate) AS orderdate, + aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity, + (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, + DATEDIFF(CURDATE( ),closedate) AS latesince + FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber + LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, + aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber + LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id + WHERE aqorders.basketno = aqbasket.basketno + AND ordernumber=?}; my $sth= $dbh->prepare($query); $sth->execute($ordernumber); my $data = $sth->fetchrow_hashref; + $data->{orderdate} = format_date( $data->{orderdate} ); $sth->finish; return $data; } diff --git a/acqui/lateorders-excel.pl b/acqui/lateorders-excel.pl new file mode 100755 index 0000000..253ec78 --- /dev/null +++ b/acqui/lateorders-excel.pl @@ -0,0 +1,65 @@ +#!/usr/bin/perl + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; +use CGI; +use C4::Auth; +use C4::Serials; +use C4::Acquisition; +use C4::Output; +use C4::Context; + +use Text::CSV::Encoded; +use open qw/ :std :utf8 /; + +my $csv = Text::CSV::Encoded->new ({ + encoding => undef, + quote_char => '"', + escape_char => '"', + sep_char => ',', + binary => 1, + }); + +my $query = new CGI; +my @ordernumbers = $query->param('ordernumber'); + +print $query->header( + -type => 'text/csv', + -attachment => "lateorders.csv", +); + +print "LATE ORDERS\n\n"; +print "ORDER DATE,ESTIMATED DELIVERY DATE,VENDOR,INFORMATION,TOTAL COST,BASKET,CLAIMS COUNT,CLAIMED DATE\n"; + +for my $ordernumber ( @ordernumbers ) { + my $order = GetOrder $ordernumber; + $csv->combine( + "(" . $$order{supplierid} . ") " . $$order{orderdate} . " (" . $$order{latesince} . " days)", + $$order{estimateddeliverydate}, + $$order{supplier}, + $$order{title} . ( $$order{author} ? " Author: $$order{author}" : "" ) . ( $$order{publisher} ? " Published by: $$order{publisher}" : "" ), + $$order{unitpricesupplier} . "x" . $$order{quantity} . " = " . $$order{subtotal} . " (" . $$order{budget} . ")", + $$order{basketname} . " (" . $$order{basketno} . ")", + $$order{claims_count}, + $$order{claimed_date} + ); + my $string = $csv->string; + print $string, "\n"; +} + +print ",,Total Number Late, " . scalar @ordernumbers . "\n"; + diff --git a/acqui/lateorders.pl b/acqui/lateorders.pl index 86b1300..cf5a107 100755 --- a/acqui/lateorders.pl +++ b/acqui/lateorders.pl @@ -77,7 +77,7 @@ unless ($delay =~ /^\d{1,3}$/) { } if ($op and $op eq "send_alert"){ - my @ordernums = $input->param("claim_for");# FIXME: Fallback values? + my @ordernums = $input->param("ordernumber");# FIXME: Fallback values? my $err; eval { $err = SendAlerts( 'claimacquisition', \@ordernums, $input->param("letter_code") ); # FIXME: Fallback value? diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt index cdb8711..5ec0e09 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt @@ -5,16 +5,38 @@ <script type="text/javascript"> //<![CDATA[ $(document).ready(function() { - $("input:checkbox[name=claim_for]").click(function(){ + $("input:checkbox[name=ordernumber]").click(function(){ var booksellerid = $(this).attr('booksellerid'); - if ( $("input:checkbox[name=claim_for]:checked").length > 0) { - $("input:checkbox[name=claim_for][booksellerid!="+booksellerid+"]").attr('disabled', true); + if ( $("input:checkbox[name=ordernumber]:checked").length > 0) { + $("input:checkbox[name=ordernumber][booksellerid!="+booksellerid+"]").attr('disabled', true); } else { - $("input:checkbox[name=claim_for]").attr('disabled', false); + $("input:checkbox[name=ordernumber]").attr('disabled', false); } }); + + $("span.exportSelected").html("<a id=\"ExportSelected\" href=\"/cgi-bin/koha/acqui/lateorders.pl\"> "+_("Export selected items data") +"<\/a>"); + $('#CheckAll').click(function(){ $("#late_orders td").checkCheckboxes();}); $('#CheckNone').click(function(){ $("#late_orders td").unCheckCheckboxes();}); + + // Generates a dynamic link for exporting the selection's data as CSV + $("#ExportSelected").click(function() { + var selected = $("input[name='ordernumber']:checked"); + + if (selected.length == 0) { + alert(_('Please select at least one item to export.')); + return false; + } + + // Building the url from currently checked boxes + var url = '/cgi-bin/koha/acqui/lateorders-excel.pl?op=export'; + for (var i = 0; i < selected.length; i++) { + url += '&ordernumber=' + selected[i].value; + } + // And redirecting to the CSV page + location.href = url; + return false; + }); }); //]]> </script> @@ -59,6 +81,11 @@ $(document).ready(function() { [% END %] <table id="late_orders"> <tr> + [% IF Supplier %] + <th><a id="CheckAll" href="#">Check all</a><br /><a id="CheckNone" href="#">Uncheck all</a></th> + [% ELSE %] + <th></th> + [% END %] <th>Order Date</th> <th>Vendor</th> <th>Information</th> @@ -66,16 +93,14 @@ $(document).ready(function() { <th>Basket</th> <th>Claims count</th> <th>Claimed date</th> - [% IF Supplier %] - <th><a id="CheckAll" href="#">Check all</a><br /><a id="CheckNone" href="#">Uncheck all</a></th> - [% ELSE %] - <th></th> - [% END %] </tr> [% FOREACH lateorder IN lateorders %] [% UNLESS ( loop.odd ) %]<tr class="highlight"> [% ELSE %]<tr>[% END %] <td> + <input type="checkbox" value="[% lateorder.ordernumber %]" booksellerid="[% lateorder.supplierid %]" name="ordernumber"> + </td> + <td> ([% lateorder.supplierid %]) [% lateorder.orderdate %] ([% lateorder.latesince %] days) @@ -107,27 +132,25 @@ $(document).ready(function() { </td> <td>[% lateorder.claims_count %]</td> <td>[% lateorder.claimed_date %]</td> - <td> - [% UNLESS lateorder.budget_lock %] - <input type="checkbox" class="checkbox" name="claim_for" value="[% lateorder.ordernumber %]" booksellerid="[% lateorder.supplierid %]"/> - [% END %] - </td> - </td> </tr> [% END %] - <tr> - <th>Total</th> - <th colspan="2"> </th> - <th>[% total %]</th> - <th> </th> - <th> </th> - <th> </th> - <td> - <input type="submit" value="Claim Order" /> - </td> - </tr> + <tfoot> + <tr> + <th>Total</th> + <th colspan="3"> </th> + <th>[% total %]</th> + <th> </th> + <th> </th> + <th> </th> + </tr> + </tfoot> </table> - </form> + <div class="spacer"></div> + <p style="display:block;"><span class="exportSelected"></span></p> + [% UNLESS lateorder.budget_lock %] + <p style="display:block;"><input type="submit" value="Claim Order" /></p> + [% END %] +</form> [% ELSE %]<p>There are no late orders.</p> [% END %] </div> -- 1.7.7.3