From 3d47a903c240059efa9306eb551cc73e9481e3be Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 22 Jun 2012 11:10:57 +0200 Subject: [PATCH] Bug 7298 Export late orders as csv This patch allows to export late orders as csv. Test plan: - Go on the late orders page (acqui/lateorders.pl) - Select one or more order and click on the button "export as CSV". - The generated file should contains some information on the orders (order date, estimated delivery date, vendor name, information field, cost, basket name (and basketid), claims count and the claimed date) The last line of the file is the total of orders. - You are not allow to select order from different vendor. - The check/uncheck all links appears only if a vendor is selected. - Check that the check/uncheck works for all pages of the table. Signed-off-by: Nicole C. Engard Signed-off-by: Mathieu Saby Signed-off-by: Paul Poulain Signed-off-by: sonia Signed-off-by: Katrin Fischer Testing comments on last patch in this series. --- C4/Acquisition.pm | 47 +++++++-- acqui/lateorders-export.pl | 64 ++++++++++++ acqui/lateorders.pl | 3 +- .../prog/en/modules/acqui/lateorders.tt | 110 ++++++++++++-------- .../prog/en/modules/serials/claims.tt | 2 +- .../{lateissues-excel.pl => lateissues-export.pl} | 0 6 files changed, 173 insertions(+), 53 deletions(-) create mode 100755 acqui/lateorders-export.pl rename serials/{lateissues-excel.pl => lateissues-export.pl} (100%) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 8c40dcd..5c3a450 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -338,7 +338,7 @@ sub GetBasketAsCSV { =head3 GetBasketGroupAsCSV -=over 4 +=over &GetBasketGroupAsCSV($basketgroupid); @@ -966,17 +966,43 @@ 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 + aqorders.*, + biblio.title, + biblio.author, + aqbasket.basketname, + borrowers.branchcode, + biblioitems.publicationyear, + biblio.copyrightdate, + biblioitems.editionstatement, + biblioitems.isbn, + biblioitems.ean, + biblio.seriestitle, + biblioitems.publishercode, + 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 - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, + (aqorders.quantity - COALESCE(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; } @@ -2252,7 +2278,7 @@ sub GetContract { =head3 AddClaim -=over 4 +=over &AddClaim($ordernumber); @@ -2261,6 +2287,7 @@ Add a claim for an order =back =cut + sub AddClaim { my ($ordernumber) = @_; my $dbh = C4::Context->dbh; diff --git a/acqui/lateorders-export.pl b/acqui/lateorders-export.pl new file mode 100755 index 0000000..f328eac --- /dev/null +++ b/acqui/lateorders-export.pl @@ -0,0 +1,64 @@ +#!/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_to_receive} . " = " . $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 0832974..360674c 100755 --- a/acqui/lateorders.pl +++ b/acqui/lateorders.pl @@ -101,7 +101,7 @@ if ( $delay and not $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? @@ -109,6 +109,7 @@ if ($op and $op eq "send_alert"){ AddClaim ( $_ ) for @ordernums; } }; + if ( $@ ) { $template->param(error_claim => $@); } elsif ( ref $err and exists $err->{error} and $err->{error} eq "no_email" ) { 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 83dcbe5..4ffad8d 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt @@ -11,24 +11,55 @@ [% IF (dateformat == 'metric') %] dt_add_type_uk_date(); [% END %] + +var late_orderst; +function check_uncheck() { + var all_nodes = $(late_orderst.fnGetNodes()); + if ( $(all_nodes).find("input:checkbox[name=ordernumber]:checked").length > 0) { + var booksellerid = $(all_nodes).find("input:checkbox[name=ordernumber]:checked:first").attr("data-booksellerid"); + $(all_nodes).find("input:checkbox[name=ordernumber][data-booksellerid!="+booksellerid+"]").attr('disabled', 'disabled'); + } else { + $("input:checkbox[name=ordernumber]").removeAttr('disabled'); + } +} + $(document).ready(function() { - var late_orderst = $("#late_orders").dataTable($.extend(true, {}, dataTablesDefaults, { + + late_orderst = $("#late_orders").dataTable($.extend(true, {}, dataTablesDefaults, { "aoColumnDefs": [ - { "aTargets": [ -1 ], "bSortable": false, "bSearchable": false }, + { "aTargets": [ 0 ], "bSortable": false, "bSearchable": false }, ], - "sPaginationType": "four_button" + "sPaginationType": "four_button", + "bAutoWidth": false, + "fnDrawCallback": function() { + if ( typeof late_orderst != 'undefined' ) { + check_uncheck(); + $('input:checkbox[name=ordernumber]').bind('click', check_uncheck); + }; + } } ) ); + $('#CheckAll').click(function(){ $(late_orderst.fnGetNodes()).find("td").checkCheckboxes();}); + $('#CheckNone').click(function(){ $(late_orderst.fnGetNodes()).find("td").unCheckCheckboxes();}); + + // Generates a dynamic link for exporting the selection's data as CSV + $("#ExportSelected").click(function() { + var all_nodes = $(late_orderst.fnGetNodes()); + var selected = $(all_nodes).find("input[name='ordernumber']:checked"); + + if (selected.length == 0) { + alert(_("Please select at least one item to export.")); + return false; + } - $("input:checkbox[name=claim_for]").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); - } else { - $("input:checkbox[name=claim_for]").attr('disabled', false); + // Building the url from currently checked boxes + var url = '/cgi-bin/koha/acqui/lateorders-export.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; }); - $('#CheckAll').click(function(){ $("#late_orders td").checkCheckboxes();}); - $('#CheckNone').click(function(){ $("#late_orders td").unCheckCheckboxes();}); }); //]]> @@ -72,8 +103,13 @@ $(document).ready(function() {

[% END %] - + + [% IF Supplier %] + + [% ELSE %] + + [% END %] @@ -82,18 +118,16 @@ $(document).ready(function() { - [% IF Supplier %] - - [% ELSE %] - - [% END %] - - - [% FOREACH lateorder IN lateorders %] + + + [% FOREACH lateorder IN lateorders %] [% UNLESS ( loop.odd ) %] [% ELSE %][% END %] + - - - [% END %] - - - - - - - + [% END %] + + + + - - - - + - +
Check all
Uncheck all
Order date Estimated delivery date VendorBasket Claims count Claimed dateCheck all
Uncheck all
+ + ([% lateorder.supplierid %]) [% lateorder.orderdate %] ([% lateorder.latesince %] days) @@ -130,31 +164,25 @@ $(document).ready(function() { [% lateorder.claims_count %] [% lateorder.claimed_date %] - [% UNLESS lateorder.budget_lock %] - - [% END %] -
Total   
Total [% total %]    - -  
+
+ +

+ + [% UNLESS lateorder.budget_lock %] + + [% END %] +

[% ELSE %]

There are no late orders.

[% END %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt index f4de94c..c8392e3 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt @@ -41,7 +41,7 @@ } // Building the url from currently checked boxes - var url = '/cgi-bin/koha/serials/lateissues-excel.pl?supplierid=&op=claims'; + var url = '/cgi-bin/koha/serials/lateissues-export.pl?supplierid=&op=claims'; for (var i = 0; i < selected.length; i++) { url += '&serialid=' + selected[i].value; } diff --git a/serials/lateissues-excel.pl b/serials/lateissues-export.pl similarity index 100% rename from serials/lateissues-excel.pl rename to serials/lateissues-export.pl -- 1.7.10.4