From 42880be738a67438ea011ebecfb7264542007b8d Mon Sep 17 00:00:00 2001
From: Jonathan Druart
Date: Mon, 13 Feb 2012 10:05:17 +0100
Subject: [PATCH 1/1] Bug 7291: Adds new field aqbooksellers.deliverytime
New field deliverytime in aqbooksellers table. It is an estimated
delivery time for orders (in days).
You can set this delay on the supplier modification page.
It is used in the late orders search.
The order estimated date is the aqbasket.closedate +
aqbooksellers.deliverytime
If you set a delay, the query check if closedate <= today - delay
If you set a "delivery time from" and a "delivery time to", the query check if
$delivery_time_from <= aqbooksellers.deliverytime is not NULL and if
closedate + deliverytime >= $delivery_time_to
if there is not a time_to then $delivery_time_to = the current date.
---
C4/Acquisition.pm | 28 ++++++++-
C4/Bookseller.pm | 61 +++++++++++++++-----
acqui/basket.pl | 12 ++++
acqui/lateorders.pl | 21 +++++--
acqui/supplier.pl | 2 +
acqui/updatesupplier.pl | 1 +
installer/data/mysql/kohastructure.sql | 1 +
installer/data/mysql/updatedatabase.pl | 8 +++
.../intranet-tmpl/prog/en/modules/acqui/basket.tt | 1 +
.../prog/en/modules/acqui/lateorders.tt | 40 ++++++++++++-
.../prog/en/modules/acqui/supplier.tt | 8 +++
11 files changed, 157 insertions(+), 26 deletions(-)
diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
index 0a96a02..c6220c1 100644
--- a/C4/Acquisition.pm
+++ b/C4/Acquisition.pm
@@ -1438,13 +1438,15 @@ sub GetLateOrders {
my $delay = shift;
my $supplierid = shift;
my $branch = shift;
+ my $estimateddeliverydatefrom = shift;
+ my $estimateddeliverydateto = shift;
my $dbh = C4::Context->dbh;
#BEWARE, order of parenthesis and LEFT JOIN is important for speed
my $dbdriver = C4::Context->config("db_scheme") || "mysql";
- my @query_params = ($delay); # delay is the first argument regardless
+ my @query_params = ();
my $select = "
SELECT aqbasket.basketno,
aqorders.ordernumber,
@@ -1460,6 +1462,7 @@ sub GetLateOrders {
biblio.author, biblio.title,
biblioitems.publishercode AS publisher,
biblioitems.publicationyear,
+ ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
";
my $from = "
FROM
@@ -1473,6 +1476,7 @@ sub GetLateOrders {
OR datereceived IS NULL
OR aqorders.quantityreceived < aqorders.quantity
)
+ AND aqbasket.closedate IS NOT NULL
AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
";
my $having = "";
@@ -1482,7 +1486,10 @@ sub GetLateOrders {
(aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
DATEDIFF(CURDATE( ),closedate) AS latesince
";
- $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
+ if ( defined $delay ) {
+ $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) " ;
+ push @query_params, $delay;
+ }
$having = "
HAVING quantity <> 0
AND unitpricesupplier <> 0
@@ -1495,7 +1502,10 @@ sub GetLateOrders {
aqorders.quantity * aqorders.rrp AS subtotal,
(CURDATE - closedate) AS latesince
";
- $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
+ if ( defined $delay ) {
+ $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
+ push @query_params, $delay;
+ }
}
if (defined $supplierid) {
$from .= ' AND aqbasket.booksellerid = ? ';
@@ -1505,6 +1515,18 @@ sub GetLateOrders {
$from .= ' AND borrowers.branchcode LIKE ? ';
push @query_params, $branch;
}
+ if ( defined $estimateddeliverydatefrom ) {
+ $from .= '
+ AND aqbooksellers.deliverytime IS NOT NULL
+ AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
+ push @query_params, $estimateddeliverydatefrom;
+ }
+ if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
+ $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
+ push @query_params, $estimateddeliverydateto;
+ } elsif ( defined $estimateddeliverydatefrom ) {
+ $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CURDATE()';
+ }
if (C4::Context->preference("IndependantBranches")
&& C4::Context->userenv
&& C4::Context->userenv->{flags} != 1 ) {
diff --git a/C4/Bookseller.pm b/C4/Bookseller.pm
index 6a91f99..f4db133 100644
--- a/C4/Bookseller.pm
+++ b/C4/Bookseller.pm
@@ -99,19 +99,48 @@ Searches for suppliers with late orders.
=cut
sub GetBooksellersWithLateOrders {
- my $delay = shift;
- my $dbh = C4::Context->dbh;
-
- # TODO delay should be verified
- my $query_string =
- "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
- FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
- LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
- WHERE (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY)
- AND (datereceived = '' OR datereceived IS NULL))";
-
- my $sth = $dbh->prepare($query_string);
- $sth->execute;
+ my ( $delay, $branch, $estimateddeliverydatefrom, $estimateddeliverydateto ) = @_; # FIXME: Branch argument unused.
+ my $dbh = C4::Context->dbh;
+
+ # FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
+ # should be tested with other DBMs
+
+ my $strsth;
+ my @query_params = ();
+ my $dbdriver = C4::Context->config("db_scheme") || "mysql";
+ $strsth = "
+ SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
+ FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
+ LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
+ WHERE
+ ( datereceived = ''
+ OR datereceived IS NULL
+ OR aqorders.quantityreceived < aqorders.quantity
+ )
+ AND aqorders.rrp <> 0
+ AND aqorders.ecost <> 0
+ AND aqorders.quantity - IFNULL(aqorders.quantityreceived,0) <> 0
+ AND aqbasket.closedate IS NOT NULL
+ ";
+ if ( defined $delay ) {
+ $strsth .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
+ push @query_params, $delay;
+ }
+ if ( defined $estimateddeliverydatefrom ) {
+ $strsth .= '
+ AND aqbooksellers.deliverytime IS NOT NULL
+ AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
+ push @query_params, $estimateddeliverydatefrom;
+ }
+ if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
+ $strsth .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
+ push @query_params, $estimateddeliverydateto;
+ } elsif ( defined $estimateddeliverydatefrom ) {
+ $strsth .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CURDATE()';
+ }
+
+ my $sth = $dbh->prepare($strsth);
+ $sth->execute( @query_params );
my %supplierlist;
while ( my ( $id, $name ) = $sth->fetchrow ) {
$supplierlist{$id} = $name;
@@ -198,7 +227,7 @@ sub ModBookseller {
contphone=?,contfax=?,contaltphone=?,contemail=?,
contnotes=?,active=?,listprice=?, invoiceprice=?,
gstreg=?,listincgst=?,invoiceincgst=?,
- discount=?,notes=?,gstrate=?
+ discount=?,notes=?,gstrate=?,deliverytime=?
WHERE id=?';
my $sth = $dbh->prepare($query);
$sth->execute(
@@ -215,7 +244,9 @@ sub ModBookseller {
$data->{'invoiceprice'}, $data->{'gstreg'},
$data->{'listincgst'}, $data->{'invoiceincgst'},
$data->{'discount'}, $data->{'notes'},
- $data->{'gstrate'}, $data->{'id'}
+ $data->{'gstrate'},
+ $data->{deliverytime},
+ $data->{'id'}
);
return;
}
diff --git a/acqui/basket.pl b/acqui/basket.pl
index c528156..5a1c01f 100755
--- a/acqui/basket.pl
+++ b/acqui/basket.pl
@@ -34,6 +34,8 @@ use C4::Debug;
use C4::Biblio;
use C4::Members qw/GetMember/; #needed for permissions checking for changing basketgroup of a basket
use C4::Items;
+use Date::Calc qw/Add_Delta_Days/;
+
=head1 NAME
basket.pl
@@ -212,6 +214,15 @@ if ( $op eq 'delete_confirm' ) {
}
unshift( @$basketgroups, \%emptygroup );
}
+
+ # if the basket is closed, calculate estimated delivery date
+ my $estimateddeliverydate;
+ if( $basket->{closedate} ) {
+ my ($year, $month, $day) = ($basket->{closedate} =~ /(\d+)-(\d+)-(\d+)/);
+ ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $bookseller->{deliverytime});
+ $estimateddeliverydate = "$year-$month-$day";
+ }
+
# if new basket, pre-fill infos
$basket->{creationdate} = "" unless ( $basket->{creationdate} );
$basket->{authorisedby} = $loggedinuser unless ( $basket->{authorisedby} );
@@ -359,6 +370,7 @@ my $total_est_gste;
authorisedby => $basket->{authorisedby},
authorisedbyname => $basket->{authorisedbyname},
closedate => C4::Dates->new($basket->{closedate},'iso')->output,
+ estimateddeliverydate => C4::Dates->new( $estimateddeliverydate, 'iso' )->output,
active => $bookseller->{'active'},
booksellerid => $bookseller->{'id'},
name => $bookseller->{'name'},
diff --git a/acqui/lateorders.pl b/acqui/lateorders.pl
index 55031ec..60dba97 100755
--- a/acqui/lateorders.pl
+++ b/acqui/lateorders.pl
@@ -66,14 +66,14 @@ my ($template, $loggedinuser, $cookie) = get_template_and_user({
my $supplierid = $input->param('supplierid') || undef; # we don't want "" or 0
my $delay = $input->param('delay');
+my $estimateddeliverydatefrom = $input->param('estimateddeliverydatefrom');
+my $estimateddeliverydateto = $input->param('estimateddeliverydateto');
my $branch = $input->param('branch');
my $op = $input->param('op');
my @errors = ();
-$delay = 30 unless defined $delay;
-unless ($delay =~ /^\d{1,3}$/) {
- push @errors, {delay_digits => 1, bad_delay => $delay};
- $delay = 30; #default value for delay
+if ( defined $delay and not $delay =~ /^\d{1,3}$/ ) {
+ push @errors, {delay_digits => 1, bad_delay => $delay};
}
if ($op and $op eq "send_alert"){
@@ -92,7 +92,13 @@ if ($op and $op eq "send_alert"){
}
}
-my %supplierlist = GetBooksellersWithLateOrders($delay);
+my %supplierlist = GetBooksellersWithLateOrders(
+ $delay,
+ $branch,
+ C4::Dates->new($estimateddeliverydatefrom)->output("iso"),
+ C4::Dates->new($estimateddeliverydateto)->output("iso")
+);
+
my (@sloopy); # supplier loop
foreach (keys %supplierlist){
push @sloopy, (($supplierid and $supplierid eq $_ ) ?
@@ -103,7 +109,7 @@ $template->param(SUPPLIER_LOOP => \@sloopy);
$template->param(Supplier=>$supplierlist{$supplierid}) if ($supplierid);
$template->param(SupplierId=>$supplierid) if ($supplierid);
-my @lateorders = GetLateOrders($delay,$supplierid,$branch);
+my @lateorders = GetLateOrders( $delay, $supplierid, $branch, $estimateddeliverydatefrom, $estimateddeliverydateto );
my $total;
foreach (@lateorders){
@@ -121,7 +127,10 @@ $template->param(ERROR_LOOP => \@errors) if (@errors);
$template->param(
lateorders => \@lateorders,
delay => $delay,
+ estimateddeliverydatefrom => $estimateddeliverydatefrom,
+ estimateddeliverydateto => $estimateddeliverydateto,
total => $total,
intranetcolorstylesheet => C4::Context->preference("intranetcolorstylesheet"),
+ DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
);
output_html_with_http_headers $input, $cookie, $template->output;
diff --git a/acqui/supplier.pl b/acqui/supplier.pl
index 3faa560..8ceef42 100755
--- a/acqui/supplier.pl
+++ b/acqui/supplier.pl
@@ -109,6 +109,7 @@ if ( $op eq 'display' ) {
listincgst => $supplier->{'listincgst'},
invoiceincgst => $supplier->{'invoiceincgst'},
discount => $supplier->{'discount'},
+ deliverytime => $supplier->{deliverytime},
invoiceprice => $supplier->{'invoiceprice'},
listprice => $supplier->{'listprice'},
GST => $tax_rate,
@@ -171,6 +172,7 @@ if ( $op eq 'display' ) {
invoiceincgst => $supplier->{'invoiceincgst'},
gstrate => $gstrate,
discount => $supplier->{'discount'},
+ deliverytime => $supplier->{deliverytime},
loop_currency => $loop_currency,
GST => $tax_rate,
enter => 1,
diff --git a/acqui/updatesupplier.pl b/acqui/updatesupplier.pl
index bd59c6b..8f8d237 100755
--- a/acqui/updatesupplier.pl
+++ b/acqui/updatesupplier.pl
@@ -107,6 +107,7 @@ if ($gstrate eq '') {
$data{'gstrate'} = $input->param('gstrate')/100;
}
$data{'discount'}=$input->param('discount');
+$data{deliverytime} = $input->param('deliverytime');
$data{'active'}=$input->param('status');
if($data{'name'}) {
if ($data{'id'}){
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 37113c2..40f2916 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2495,6 +2495,7 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis
`gstrate` decimal(6,4) default NULL, -- the tax rate the library is charged
`discount` float(6,4) default NULL, -- discount offered on all items ordered from this vendor
`fax` varchar(50) default NULL, -- vendor fax number
+ `deliverytime` int(11) default NULL, -- vendor delivery time
PRIMARY KEY (`id`),
KEY `listprice` (`listprice`),
KEY `invoiceprice` (`invoiceprice`),
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 099dfb9..fede7c1 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -4663,6 +4663,14 @@ ENDOFRENEWAL
print "Upgrade to $DBversion done (Added a system preference to allow renewal of Patron account either from todays date or from existing expiry date in the patrons account.)\n";
SetVersion($DBversion);
}
+
+$DBversion = "3.07.00.XXX";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do("ALTER TABLE aqbooksellers ADD deliverytime INT DEFAULT NULL");
+ print "Upgrade to $DBversion done (Add deliverytime field in aqbooksellers table)";
+ SetVersion($DBversion);
+}
+
=head1 FUNCTIONS
=head2 DropAllForeignKeys($table)
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 aaea6c0..e4ee782 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt
@@ -180,6 +180,7 @@
[% IF ( authorisedbyname ) %]Managed by: [% authorisedbyname %][% END %]
[% IF ( creationdate ) %]Opened on: [% creationdate %][% END %]
[% IF ( closedate ) %]Closed on: [% closedate %][% END %]
+ [% IF ( estimateddeliverydate ) %]Estimated delivery date: [% estimateddeliverydate %][% END %]
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 3eaf8a6..7ce8de3 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt
@@ -2,6 +2,7 @@
Koha › Acquisitions › Late orders
[% INCLUDE 'doc-head-close.inc' %]
+[% INCLUDE 'calendar.inc' %]
+ [% INCLUDE 'date-format.inc' %]
+
+
Tax rate:
[% GST %]%[% UNLESS ( default_tax ) %] (default)[% END %]
+ [% IF deliverytime.defined %]
+ Delivery time:
+ [% deliverytime %] days
+ [% END %]
[% IF ( notes ) %]Notes:
[% notes %]
[% END %]
--
1.7.7.3