From b93fad9ad379ef9b3b7f3c4b693a96fb3719618d Mon Sep 17 00:00:00 2001
From: Aleisha Amohia <aleishaamohia@hotmail.com>
Date: Wed, 16 Dec 2020 18:57:42 +1300
Subject: [PATCH] Bug 15348: WIP

Working on pre-filling values
---
 acqui/basket.pl                                    |  3 ++-
 ...15348-add_aqorders.estimated_delivery_date.perl | 29 ++++++++++++++++++++++
 installer/data/mysql/kohastructure.sql             |  1 +
 .../intranet-tmpl/prog/en/modules/acqui/basket.tt  |  4 +++
 4 files changed, 36 insertions(+), 1 deletion(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_15348-add_aqorders.estimated_delivery_date.perl

diff --git a/acqui/basket.pl b/acqui/basket.pl
index f77d86b88c..4e46a6e591 100755
--- a/acqui/basket.pl
+++ b/acqui/basket.pl
@@ -501,12 +501,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} || '2020-12-12';
+
     foreach my $key (qw(transferred_from transferred_to)) {
         if ($line{$key}) {
             my $order = GetOrder($line{$key});
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..277a4c05c7
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_15348-add_aqorders.estimated_delivery_date.perl
@@ -0,0 +1,29 @@
+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 });
+        my $orders = $dbh->selectall_arrayref("SELECT ordernumber, orderstatus, basketno FROM aqorders", { Slice => {} });
+        foreach my $order ( @$orders ) {
+            my $bookseller = $dbh->selectrow_hashref("SELECT deliverytime FROM aqbooksellers WHERE id =
+                (SELECT booksellerid FROM aqbasket WHERE basketno =
+                    (SELECT basketno FROM aqorders WHERE ordernumber = $order->{ordernumber})
+                )
+            ");
+            my $deliverytime = $bookseller->{deliverytime} || 0;
+            if ( $order->{orderstatus} eq 'ordered' or $order->{orderstatus} eq 'complete' ) {
+                # the basket has been closed and will have a closedate
+                my $basket = $dbh->selectrow_hashref("SELECT closedate FROM aqbasket WHERE basketno = $order->{basketno}");
+                my ($year, $month, $day) = ( $basket->{closedate} =~ /(\d+)-(\d+)-(\d+)/ );
+                ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $deliverytime);
+                my $estimated_delivery_date = sprintf( "%04d-%02d-%02d", $year, $month, $day );
+                $dbh->do(qq{ UPDATE aqorders SET estimated_delivery_date = $estimated_delivery_date WHERE ordernumber = $order->{ordernumber} });
+            } else {
+                # we have no closedate to use in calculation, so use today's date
+                $dbh->do(qq{ UPDATE aqorders SET estimated_delivery_date = DATE_ADD(NOW(), INTERVAL $deliverytime DAYS) WHERE ordernumber = $order->{ordernumber} });
+            }
+        }
+
+        NewVersion( $DBversion, 15348, "Add new column aqorders.estimated_delivery_date and pre-fill with calculated dates" );
+    }
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index ef3bff04b0..49cd889934 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -3319,6 +3319,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items
   suppliers_reference_number varchar(35) default NULL, -- Suppliers unique edifact quote ref
   suppliers_reference_qualifier varchar(3) default NULL, -- Type of number above usually 'QLI'
   `suppliers_report` MEDIUMTEXT COLLATE utf8mb4_unicode_ci, -- 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 ddd8df99ae..9025c6d97b 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt
@@ -412,6 +412,7 @@
                         <th>GST</th>
                         <th>Fund</th>
                         <th>Supplier report</th>
+                        <th>Estimated delivery date</th>
                         [% IF ( active ) %]
                             [% UNLESS ( closedate ) %]
                                 <th class="NoSort">Modify</th>
@@ -439,6 +440,7 @@
                         <th>[% foot_loo.tax_value | $Price %]</th>
                         <th>&nbsp;</th>
                         <th>&nbsp;</th>
+                        <th>[% foot_loo.estimated_delivery_date | $KohaDates | html %]</th>
                         [% IF ( active ) %]
                             [% UNLESS ( closedate ) %]
                                 <th>&nbsp;</th>
@@ -464,6 +466,7 @@
                     <th>[% total_tax_value | $Price %]</th>
                     <th>&nbsp;</th>
                     <th>&nbsp;</th>
+                    <th>[% estimated_delivery_date | $KohaDates | html %]</th>
                     [% IF ( active ) %]
                         [% UNLESS ( closedate ) %]
                             <th>&nbsp;</th>
@@ -548,6 +551,7 @@
                         <td class="number [% IF books_loo.tax_value.search(zero_regex) %]error[% END %]">[% books_loo.tax_value | $Price %]</td>
                         <td>[% books_loo.budget_name | html %]</td>
                         <td>[% books_loo.suppliers_report | html %]</td>
+                        <td>[% books_loo.estimated_delivery_date | $KohaDates | html %]</td>
                         [% IF ( active ) %]
                             [% UNLESS ( closedate ) %]
                             <td>
-- 
2.11.0