From 2a1d85fc32a8b0df446bc32e92cbc38c3190ce7e Mon Sep 17 00:00:00 2001 From: Aleisha Amohia Date: Wed, 16 Dec 2020 18:57:42 +1300 Subject: [PATCH] Bug 15348: Add estimated delivery date field to individual orders MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit This patch allows you to specify an estimated delivery date per order. The specified estimated delivery date is also considered when searching for late orders and exporting late orders. You can also edit the estimated delivery date from the late orders page. Test plan: 1. Update database, rebuild schema, restart services 2. Go to Acquisitions, search for a vendor, and create a new basket 3. Add an order to the basket. When filling in the accounting details, notice the new 'estimated delivery date' field, but don't add a date. Save the order. 4. Confirm no date shows in the estimated delivery date column in the orders table. 5. Modify the order. Add a date in the estimated delivery date field and save the order. 6. Confirm the date now shows in the orders table. 7. Close the basket. 8. Go to the Late Orders page. 9. Put estimated delivery date from and to parameters in the search filters on the left. Ensure the from and to dates encapsulate the date you entered in the estimated delivery date field for the order. 10. Click Filter and ensure the order shows. 11. Select the checkbox next to the order. Click the Export as CSV button. 12. Open the CSV and confirm the estimated delivery date that you entered shows as expected in the file. 13. Click Edit under the estimated delivery date. Confirm the estimated delivery date modal pops up with the correct order line number in the modal header. 14. Remove the estimated delivery date and click Save. 15. Confirm that the estimated delivery date calculated by Koha now shows in the late orders table. 16. Select the checkbox next to the order. Click the Export as CSV button. 17. Open the CSV and confirm the calculated estimated delivery date shows in the CSV. 18. Confirm tests pass: t/db_dependent/Koha/Acquisition/Order.t Sponsored-by: Bibliotheksservice-Zentrum Baden-Württemberg (BSZ) Signed-off-by: Christian Stelzenmüller Signed-off-by: Martin Renvoize Signed-off-by: Christian Stelzenmüller Signed-off-by: Katrin Fischer Signed-off-by: Martin Renvoize Signed-off-by: Tomas Cohen Arazi --- C4/Acquisition.pm | 2 +- Koha/Acquisition/Orders.pm | 23 ++++- acqui/addorder.pl | 3 + acqui/basket.pl | 3 +- acqui/lateorders-export.pl | 2 +- acqui/moddeliverydate.pl | 83 +++++++++++++++++++ acqui/neworderempty.pl | 3 +- ...-add_aqorders.estimated_delivery_date.perl | 9 ++ installer/data/mysql/kohastructure.sql | 1 + .../prog/en/modules/acqui/basket.tt | 4 + .../prog/en/modules/acqui/lateorders.tt | 65 +++++++++++++-- .../prog/en/modules/acqui/moddeliverydate.tt | 45 ++++++++++ .../prog/en/modules/acqui/neworderempty.tt | 7 ++ t/db_dependent/Koha/Acquisition/Order.t | 34 +++++++- 14 files changed, 270 insertions(+), 14 deletions(-) create mode 100755 acqui/moddeliverydate.pl create mode 100644 installer/data/mysql/atomicupdate/bug_15348-add_aqorders.estimated_delivery_date.perl create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/acqui/moddeliverydate.tt diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 899dcc076a..bbd01687a9 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -1242,7 +1242,7 @@ sub GetOrder { aqbooksellers.name AS supplier, aqbooksellers.id AS supplierid, biblioitems.publishercode AS publisher, - ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, + ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS calculateddeliverydate, 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, diff --git a/Koha/Acquisition/Orders.pm b/Koha/Acquisition/Orders.pm index a646dac802..e345df38f7 100644 --- a/Koha/Acquisition/Orders.pm +++ b/Koha/Acquisition/Orders.pm @@ -69,17 +69,22 @@ sub filter_by_lates { my @delivery_time_conditions; my $date_add = "DATE_ADD(basketno.closedate, INTERVAL COALESCE(booksellerid.deliverytime, booksellerid.deliverytime, 0) day)"; + my @estimated_delivery_time_conditions; if ( defined $estimated_from or defined $estimated_to ) { push @delivery_time_conditions, \[ "$date_add IS NOT NULL" ]; + push @estimated_delivery_time_conditions, \[ "estimated_delivery_date IS NOT NULL" ]; } if ( defined $estimated_from ) { push @delivery_time_conditions, \[ "$date_add >= ?", $dtf->format_date($estimated_from) ]; + push @estimated_delivery_time_conditions, \[ "estimated_delivery_date >= ?", $dtf->format_date($estimated_from) ]; } if ( defined $estimated_to ) { push @delivery_time_conditions, \[ "$date_add <= ?", $dtf->format_date($estimated_to) ]; + push @estimated_delivery_time_conditions, \[ "estimated_delivery_date <= ?", $dtf->format_date($estimated_to) ]; } if ( defined $estimated_from and not defined $estimated_to ) { push @delivery_time_conditions, \[ "$date_add <= ?", $dtf->format_date(dt_from_string) ]; + push @estimated_delivery_time_conditions, \[ "estimated_delivery_date <= ?", $dtf->format_date(dt_from_string) ]; } $self->search( @@ -110,7 +115,15 @@ sub filter_by_lates { # ( $branchcode ? ('borrower.branchcode')) # FIXME branch is not a filter we may not need to implement this - ( @delivery_time_conditions ? ( -and => \@delivery_time_conditions ) : ()), + ( ( @delivery_time_conditions and @estimated_delivery_time_conditions ) ? + ( -or => + [ + -and => \@delivery_time_conditions, + -and => \@estimated_delivery_time_conditions + ] + ) + : () + ), ( C4::Context->preference('IndependentBranches') && !C4::Context->IsSuperLibrarian @@ -122,8 +135,12 @@ sub filter_by_lates { }, { - '+select' => [\"DATE_ADD(basketno.closedate, INTERVAL COALESCE(booksellerid.deliverytime, booksellerid.deliverytime, 0) day)"], - '+as' => ['estimated_delivery_date'], + '+select' => [ + \"DATE_ADD(basketno.closedate, INTERVAL COALESCE(booksellerid.deliverytime, booksellerid.deliverytime, 0) day)", + ], + '+as' => [qw/ + calculated_estimated_delivery_date + /], join => { 'basketno' => 'booksellerid' }, prefetch => {'basketno' => 'booksellerid'}, } diff --git a/acqui/addorder.pl b/acqui/addorder.pl index ad1dd97b7b..edea7a1cb8 100755 --- a/acqui/addorder.pl +++ b/acqui/addorder.pl @@ -137,6 +137,7 @@ use Koha::Acquisition::Currencies qw( get_active ); use Koha::Acquisition::Orders; use Koha::Acquisition::Baskets; use C4::Barcodes; +use Koha::DateUtils; ### "-------------------- addorder.pl ----------" @@ -326,9 +327,11 @@ if ( $basket->{is_standing} || $orderinfo->{quantity} ne '0' ) { } $orderinfo->{unitprice} = $orderinfo->{ecost} if not defined $orderinfo->{unitprice} or $orderinfo->{unitprice} eq ''; + $orderinfo->{estimated_delivery_date} = $orderinfo->{estimated_delivery_date} ? dt_from_string($orderinfo->{estimated_delivery_date}) : undef; my $order; my $log_action_name; + if ( $orderinfo->{ordernumber} ) { $order = Koha::Acquisition::Orders->find($orderinfo->{ordernumber}); $order->set($orderinfo); diff --git a/acqui/basket.pl b/acqui/basket.pl index c028dc0219..80c633cf56 100755 --- a/acqui/basket.pl +++ b/acqui/basket.pl @@ -496,12 +496,13 @@ sub get_order_infos { $line{order_object} = $order; } - my $suggestion = GetSuggestionInfoFromBiblionumber($line{biblionumber}); $line{suggestionid} = $$suggestion{suggestionid}; $line{surnamesuggestedby} = $$suggestion{surnamesuggestedby}; $line{firstnamesuggestedby} = $$suggestion{firstnamesuggestedby}; + $line{estimated_delivery_date} = $order->{estimated_delivery_date}; + foreach my $key (qw(transferred_from transferred_to)) { if ($line{$key}) { my $order = GetOrder($line{$key}); diff --git a/acqui/lateorders-export.pl b/acqui/lateorders-export.pl index a871777435..f86f724939 100755 --- a/acqui/lateorders-export.pl +++ b/acqui/lateorders-export.pl @@ -44,7 +44,7 @@ unless ( $csv_profile_id ) { push @orders, { orderdate => $order->{orderdate}, latesince => $order->{latesince}, - estimateddeliverydate => $order->{estimateddeliverydate}, + estimateddeliverydate => $order->{estimated_delivery_date} ? $order->{estimated_delivery_date} : $order->{calculateddeliverydate}, supplier => $order->{supplier}, supplierid => $order->{supplierid}, title => $order->{title}, diff --git a/acqui/moddeliverydate.pl b/acqui/moddeliverydate.pl new file mode 100755 index 0000000000..48d5b5bbb8 --- /dev/null +++ b/acqui/moddeliverydate.pl @@ -0,0 +1,83 @@ +#!/usr/bin/perl + +# Copyright 2021 Aleisha Amohia +# +# 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 3 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, see . + +=head1 NAME + +moddeliverydate.pl + +=head1 DESCRIPTION + +Modify just the estimated delivery date of an individual order when +its basket is closed. + +=cut + +use Modern::Perl; + +use CGI qw ( -utf8 ); +use C4::Auth; +use C4::Output; +use C4::Acquisition; + +use Koha::Acquisition::Booksellers; +use Koha::DateUtils; + +my $input = CGI->new; +my ($template, $loggedinuser, $cookie, $flags) = get_template_and_user( { + template_name => 'acqui/moddeliverydate.tt', + query => $input, + type => 'intranet', + flagsrequired => { 'acquisition' => '*' }, + debug => 1, +} ); + +my $op = $input->param('op'); +my $ordernumber = $input->param('ordernumber'); +my $referrer = $input->param('referrer') || $input->referer(); +my $order = GetOrder($ordernumber); +my $basket = GetBasket($order->{basketno}); +my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} ); + +if($op and $op eq 'save') { + my $estimated_delivery_date = $input->param('estimated_delivery_date'); + $order->{'estimated_delivery_date'} = dt_from_string( $estimated_delivery_date ); + ModOrder($order); + print $input->redirect($referrer); + exit; +} else { + $template->param( + estimated_delivery_date => $order->{'estimated_delivery_date'} + ); +} + +if($op) { + $template->param($op => 1); +} + +$template->param( + basketname => $basket->{'basketname'}, + basketno => $order->{basketno}, + booksellerid => $bookseller->id, + booksellername => $bookseller->name, + ordernumber => $ordernumber, + referrer => $referrer, +); + + +output_html_with_http_headers $input, $cookie, $template->output; diff --git a/acqui/neworderempty.pl b/acqui/neworderempty.pl index 182a04eccb..e4f8a9f175 100755 --- a/acqui/neworderempty.pl +++ b/acqui/neworderempty.pl @@ -480,7 +480,8 @@ $template->param( acqcreate => $basketobj->effective_create_items eq "ordering" ? 1 : "", users_ids => join(':', @order_user_ids), users => \@order_users, - (uc(C4::Context->preference("marcflavour"))) => 1 + (uc(C4::Context->preference("marcflavour"))) => 1, + estimated_delivery_date => $data->{estimated_delivery_date}, ); output_html_with_http_headers $input, $cookie, $template->output; diff --git a/installer/data/mysql/atomicupdate/bug_15348-add_aqorders.estimated_delivery_date.perl b/installer/data/mysql/atomicupdate/bug_15348-add_aqorders.estimated_delivery_date.perl new file mode 100644 index 0000000000..f7c9840ef2 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_15348-add_aqorders.estimated_delivery_date.perl @@ -0,0 +1,9 @@ +use Date::Calc qw( Add_Delta_Days ); +$DBversion = 'XXX'; +if( CheckVersion( $DBversion ) ) { + unless( column_exists( 'aqorders', 'estimated_delivery_date' ) ) { + $dbh->do(q{ ALTER TABLE aqorders ADD estimated_delivery_date date default null AFTER suppliers_report }); + + NewVersion( $DBversion, 15348, "Add new column aqorders.estimated_delivery_date" ); + } +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index f10556169e..32b30f1786 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -693,6 +693,7 @@ CREATE TABLE `aqorders` ( `suppliers_reference_number` varchar(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Suppliers unique edifact quote ref', `suppliers_reference_qualifier` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Type of number above usually ''QLI''', `suppliers_report` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'reports received from suppliers', + `estimated_delivery_date` date default null, PRIMARY KEY (`ordernumber`), KEY `basketno` (`basketno`), KEY `biblionumber` (`biblionumber`), diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt index 9d2687881d..d655992ca7 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt @@ -451,6 +451,7 @@ [% IF Koha.Preference('EDIFACT') && ediaccount %] Supplier report [% END %] + Estimated delivery date [% IF ( active ) %] [% UNLESS ( closedate ) %] Modify @@ -480,6 +481,7 @@ [% IF Koha.Preference('EDIFACT') && ediaccount %]   [% END %] + [% foot_loo.estimated_delivery_date | $KohaDates | html %] [% IF ( active ) %] [% UNLESS ( closedate ) %]   @@ -507,6 +509,7 @@ [% IF Koha.Preference('EDIFACT') && ediaccount %]   [% END %] + [% estimated_delivery_date | $KohaDates | html %] [% IF ( active ) %] [% UNLESS ( closedate ) %]   @@ -593,6 +596,7 @@ [% IF Koha.Preference('EDIFACT') && ediaccount %] [% books_loo.suppliers_report | html %] [% END %] + [% books_loo.estimated_delivery_date | $KohaDates | html %] [% IF ( active ) %] [% UNLESS ( closedate ) %] 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 03862a2261..6e2c17c1a1 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt @@ -102,9 +102,18 @@ [% lateorder.basket.closedate | $KohaDates %] ([% lateorder.basket.late_since_days | html %] days) - [% SET estimated_delivery_date = lateorder.get_column('estimated_delivery_date') %] + [% IF lateorder.get_column('estimated_delivery_date') %] + [% SET estimated_delivery_date = lateorder.get_column('estimated_delivery_date') %] + [% ELSIF lateorder.get_column('calculated_estimated_delivery_date') %] + [% SET estimated_delivery_date = lateorder.get_column('calculated_estimated_delivery_date') %] + [% END %] - [% estimated_delivery_date | $KohaDates %] +

+ [% estimated_delivery_date | $KohaDates %] + + Edit + +

[% lateorder.basket.bookseller.name | html %] @@ -190,7 +199,7 @@ - Total + Total [% total_quantity | html %] [% total | $Price %]   @@ -271,7 +280,7 @@
- +
@@ -284,6 +293,30 @@ + + + [% MACRO jsinclude BLOCK %] [% Asset.js("js/acquisitions-menu.js") | $raw %] [% INCLUDE 'datatables.inc' %] @@ -355,7 +388,7 @@ var modalTitle = $(this).attr("title") + " (order number " + ordernumber + ")"; var note_text = $( "#" + note_type + "-note-" + ordernumber ).html(); $("#noteEditor .modal-title").text(modalTitle); - $("#ordernumber").val( ordernumber ); + $("#notes_ordernumber").val( ordernumber ); $("#ordernotes").html( note_text ); $("#type").val( note_type ); $("#noteEditor").modal("show"); @@ -366,9 +399,29 @@ $("#noteEditorLabel").html(""); $("#noteEditor .modal-title").text(""); $("#ordernotes").html( "" ); - $("#ordernumber").val(""); + $("#notes_ordernumber").val(""); $("#type").val(""); }); + + $("#estimated_delivery_date").datepicker(); + + $(".edit_delivery_date").on("click", function(e) { + e.preventDefault(); + var ordernumber = $(this).data("ordernumber"); + var modalTitle = $(this).attr("title") + " (order number " + ordernumber + ")"; + var date_text = $( "#delivery_date_" + ordernumber ).html(); + $("#dateEditor .modal-title").text(modalTitle); + $("#date_ordernumber").val(ordernumber); + $("#estimated_delivery_date").html( date_text ); + $("#dateEditor").modal("show"); + }); + + $("#dateEditor").on('hidden.bs.modal', function (e) { + $("#dateEditorLabel").html(""); + $("#dateEditor .modal-title").text(""); + $("#estimated_delivery_date").html( "" ); + $("#date_ordernumber").val(""); + }); }); [% END %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/moddeliverydate.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/moddeliverydate.tt new file mode 100644 index 0000000000..5d50976119 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/moddeliverydate.tt @@ -0,0 +1,45 @@ +[% SET footerjs = 1 %] +[% INCLUDE 'doc-head-open.inc' %] +Koha › Acquisition › + Change estimated delivery date + +[% INCLUDE 'doc-head-close.inc' %] + + + +[% INCLUDE 'header.inc' %] + + + +
+
+
+ +

Change estimated delivery date

+
+
+ + +
[% INCLUDE 'date-format.inc' %]
+
+ + + +
+ + Cancel +
+
+
+
+ +[% MACRO jsinclude BLOCK %] + [% INCLUDE 'calendar.inc' %] + +[% END %] + +[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt index ce9dcd2324..ed97015c28 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt @@ -481,6 +481,11 @@ +
  • + + +
    [% INCLUDE 'date-format.inc' %]
    +
  • The 2 following fields are available for your own usage. They can be useful for statistical purposes
    @@ -681,6 +686,8 @@ getAuthValueDropbox( 'sort2', sort2_authcat, destination_sort2, sort2 ); }); $("#budget_id").change(); + + $("#estimated_delivery_date").datepicker(); }); function UserSearchPopup(f) { diff --git a/t/db_dependent/Koha/Acquisition/Order.t b/t/db_dependent/Koha/Acquisition/Order.t index 44f3c89845..26b6cd6856 100755 --- a/t/db_dependent/Koha/Acquisition/Order.t +++ b/t/db_dependent/Koha/Acquisition/Order.t @@ -389,7 +389,7 @@ subtest 'claim*' => sub { }; subtest 'filter_by_late' => sub { - plan tests => 16; + plan tests => 17; $schema->storage->txn_begin; my $now = dt_from_string; @@ -415,6 +415,7 @@ subtest 'filter_by_late' => sub { basketno => $basket_1->basketno, datereceived => undef, datecancellationprinted => undef, + estimated_delivery_date => undef, } } ); @@ -434,6 +435,7 @@ subtest 'filter_by_late' => sub { basketno => $basket_2->basketno, datereceived => undef, datecancellationprinted => undef, + estimated_delivery_date => undef, } } ); @@ -453,6 +455,7 @@ subtest 'filter_by_late' => sub { basketno => $basket_3->basketno, datereceived => undef, datecancellationprinted => undef, + estimated_delivery_date => undef, } } ); @@ -472,6 +475,7 @@ subtest 'filter_by_late' => sub { basketno => $basket_4->basketno, datereceived => undef, datecancellationprinted => undef, + estimated_delivery_date => undef, } } ); @@ -539,6 +543,34 @@ subtest 'filter_by_late' => sub { ); is( $late_orders->count, 1 ); + my $basket_5 = $builder->build_object( # closed today + { + class => 'Koha::Acquisition::Baskets', + value => { + booksellerid => $bookseller->id, + closedate => $now, + } + } + ); + my $order_5 = $builder->build_object( + { + class => 'Koha::Acquisition::Orders', + value => { + basketno => $basket_4->basketno, + datereceived => undef, + datecancellationprinted => undef, + estimated_delivery_date => $now->clone->subtract( days => 2 ), + } + } + ); + $late_orders = $orders->filter_by_lates( + { + estimated_from => $now->clone->subtract( days => 3 ), + estimated_to => $now->clone->subtract( days => 2 ) + } + ); + is( $late_orders->count, 1 ); + $schema->storage->txn_rollback; }; -- 2.39.5