From d76dbace5e6436ae5244876b52ffcdc4831f91db Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 13 Feb 2012 10:05:17 +0100 Subject: [PATCH] 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 | 32 ++++++++++-- C4/Bookseller.pm | 61 ++++++++++++++++------ acqui/basket.pl | 11 ++++ acqui/lateorders.pl | 27 +++++++--- acqui/supplier.pl | 2 + acqui/updatesupplier.pl | 1 + installer/data/mysql/kohastructure.sql | 1 + installer/data/mysql/updatedatabase.pl | 7 +++ .../intranet-tmpl/prog/en/modules/acqui/basket.tt | 1 + .../prog/en/modules/acqui/lateorders.tt | 43 ++++++++++++++- .../prog/en/modules/acqui/supplier.tt | 8 +++ 11 files changed, 166 insertions(+), 28 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 4fd5cc2722..bebc3a8619 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -1473,13 +1473,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, @@ -1495,6 +1497,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 @@ -1508,6 +1511,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 = ""; @@ -1515,9 +1519,12 @@ sub GetLateOrders { $select .= " aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity, (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, - DATEDIFF(CURDATE( ),closedate) AS latesince + DATEDIFF(CAST(now() AS date),closedate) AS latesince "; - $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) "; + if ( defined $delay ) { + $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ; + push @query_params, $delay; + } $having = " HAVING quantity <> 0 AND unitpricesupplier <> 0 @@ -1528,9 +1535,12 @@ sub GetLateOrders { $select .= " aqorders.quantity AS quantity, aqorders.quantity * aqorders.rrp AS subtotal, - (CURDATE - closedate) AS latesince + (CAST(now() AS date) - closedate) AS latesince "; - $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) "; + if ( defined $delay ) { + $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) "; + push @query_params, $delay; + } } if (defined $supplierid) { $from .= ' AND aqbasket.booksellerid = ? '; @@ -1540,6 +1550,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) <= CAST(now() AS date)'; + } if (C4::Context->preference("IndependantBranches") && C4::Context->userenv && C4::Context->userenv->{flags} != 1 ) { diff --git a/C4/Bookseller.pm b/C4/Bookseller.pm index f14bd02600..3fded7908f 100644 --- a/C4/Bookseller.pm +++ b/C4/Bookseller.pm @@ -102,19 +102,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(CAST(now() AS date),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) <= CAST(now() AS date)'; + } + + my $sth = $dbh->prepare($strsth); + $sth->execute( @query_params ); my %supplierlist; while ( my ( $id, $name ) = $sth->fetchrow ) { $supplierlist{$id} = $name; @@ -201,7 +230,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( @@ -218,7 +247,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 43861250be..de668917f5 100755 --- a/acqui/basket.pl +++ b/acqui/basket.pl @@ -34,6 +34,7 @@ use C4::Biblio; use C4::Members qw/GetMember/; #needed for permissions checking for changing basketgroup of a basket use C4::Items; use C4::Suggestions; +use Date::Calc qw/Add_Delta_Days/; =head1 NAME @@ -213,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} ); @@ -367,6 +377,7 @@ my $total_est_gste; authorisedby => $basket->{authorisedby}, authorisedbyname => $basket->{authorisedbyname}, closedate => $basket->{closedate}, + estimateddeliverydate=> $estimateddeliverydate, active => $bookseller->{'active'}, booksellerid => $bookseller->{'id'}, name => $bookseller->{'name'}, diff --git a/acqui/lateorders.pl b/acqui/lateorders.pl index 86b13002a9..21ee186678 100755 --- a/acqui/lateorders.pl +++ b/acqui/lateorders.pl @@ -66,14 +66,14 @@ my ($template, $loggedinuser, $cookie) = get_template_and_user({ my $booksellerid = $input->param('booksellerid') || 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, (($booksellerid and $booksellerid eq $_ ) ? @@ -104,7 +110,13 @@ $template->param(SUPPLIER_LOOP => \@sloopy); $template->param(Supplier=>$supplierlist{$booksellerid}) if ($booksellerid); $template->param(booksellerid=>$booksellerid) if ($booksellerid); -my @lateorders = GetLateOrders($delay,$booksellerid,$branch); +my @lateorders = GetLateOrders( + $delay, + $booksellerid, + $branch, + C4::Dates->new($estimateddeliverydatefrom)->output("iso"), + C4::Dates->new($estimateddeliverydateto)->output("iso") +); my $total; foreach (@lateorders){ @@ -122,7 +134,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 6746d8d444..0e2ab40302 100755 --- a/acqui/supplier.pl +++ b/acqui/supplier.pl @@ -103,6 +103,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, @@ -170,6 +171,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 844e504712..3dc69641a5 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 fda0bac627..70ddc23e06 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2602,6 +2602,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 c297677e09..3c570c5dc3 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5139,6 +5139,13 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { 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 401e484252..7b93de6818 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt @@ -181,6 +181,7 @@ [% IF ( authorisedbyname ) %]
  • Managed by: [% authorisedbyname %]
    • [% END %] [% IF ( creationdate ) %]
  • Opened on: [% creationdate | $KohaDates %]
    • [% END %] [% IF ( closedate ) %]
  • Closed on: [% closedate | $KohaDates %]
    • [% END %] + [% IF ( estimateddeliverydate ) %]
  • Estimated delivery date: [% estimateddeliverydate | $KohaDates %]
    • [% 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 33473f60e6..9305ae52f7 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt @@ -1,7 +1,9 @@ +[% USE KohaDates %] [% INCLUDE 'doc-head-open.inc' %] Koha › Acquisitions › Late orders [% INCLUDE 'doc-head-close.inc' %] +[% INCLUDE 'calendar.inc' %] +
    [% INCLUDE 'date-format.inc' %]
    + +
  • %
  • % (leave blank for default tax of [% default_gst_rate %]%)
    • +
  • + + days +
    • @@ -198,6 +202,10 @@ if (f.company.value == "") { [% discount %] %

    Tax rate: [% GST %]%[% UNLESS ( default_tax ) %] (default)[% END %]

    + [% IF deliverytime.defined %] +

    Delivery time: + [% deliverytime %] days

    + [% END %] [% IF ( notes ) %]

    Notes: [% notes %]

    [% END %] -- 2.11.0