@@ -, +, @@ --- 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(-) --- a/C4/Acquisition.pm +++ a/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 ) { --- a/C4/Bookseller.pm +++ a/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; } --- a/acqui/basket.pl +++ a/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'}, --- a/acqui/lateorders.pl +++ a/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; --- a/acqui/supplier.pl +++ a/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, --- a/acqui/updatesupplier.pl +++ a/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'}){ --- a/installer/data/mysql/kohastructure.sql +++ a/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`), --- a/installer/data/mysql/updatedatabase.pl +++ a/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) --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt +++ a/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 %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt +++ a/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' %]
    + +
  • %
  • % (leave blank for default tax of [% default_gst_rate %]%)
  • +
  • + + days +
  • @@ -197,6 +201,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 %] --