From 829117d9901484cd7e600fcee739455ddf7a961b Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Fri, 6 Jan 2012 16:51:57 +0100 Subject: [PATCH] [SIGNED-OFF] Bug 5339: Invoices management improvement - New pages: - invoices.pl: allow to search in invoices on several criteria - invoice.pl: permit to view and modify invoice details - shipment date - billing date - shipment cost and budget used for shipment cost Invoice informations are now stored in their own sql table and aqorders have a link to it Signed-off-by: Kyle M Hall --- C4/Acquisition.pm | 385 +++++++++++++++++++- C4/Budgets.pm | 24 +- acqui/addorderiso2709.pl | 2 - acqui/finishreceive.pl | 12 +- acqui/invoice.pl | 213 +++++++++++ acqui/invoices.pl | 156 ++++++++ acqui/orderreceive.pl | 25 +- acqui/parcel.pl | 259 +++++++------ acqui/parcels.pl | 68 +++- acqui/spent.pl | 47 ++- catalogue/moredetail.pl | 8 +- installer/data/mysql/kohastructure.sql | 26 +- installer/data/mysql/updatedatabase.pl | 60 +++ .../prog/en/includes/acquisitions-menu.inc | 1 + .../prog/en/modules/acqui/histsearch.tt | 6 +- .../intranet-tmpl/prog/en/modules/acqui/invoice.tt | 173 +++++++++ .../prog/en/modules/acqui/invoices.tt | 232 ++++++++++++ .../prog/en/modules/acqui/orderreceive.tt | 7 +- .../intranet-tmpl/prog/en/modules/acqui/parcel.tt | 116 +++--- .../intranet-tmpl/prog/en/modules/acqui/parcels.tt | 47 ++- .../intranet-tmpl/prog/en/modules/acqui/spent.tt | 41 +-- .../prog/en/modules/catalogue/moredetail.tt | 8 +- 22 files changed, 1615 insertions(+), 301 deletions(-) create mode 100755 acqui/invoice.pl create mode 100755 acqui/invoices.pl create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/acqui/invoice.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/acqui/invoices.tt diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 9678ffc..a6988ee 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -64,6 +64,14 @@ BEGIN { &GetParcels &GetParcel &GetContracts &GetContract + &GetInvoices + &GetInvoice + &GetInvoiceDetails + &AddInvoice + &ModInvoice + &CloseInvoice + &ReopenInvoice + &GetItemnumbersFromOrder &AddClaim @@ -1224,15 +1232,14 @@ sub GetCancelledOrders { =head3 ModReceiveOrder &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user, - $unitprice, $booksellerinvoicenumber, $biblioitemnumber, - $freight, $bookfund, $rrp); + $unitprice, $invoiceid, $biblioitemnumber, + $bookfund, $rrp, \@received_itemnumbers); Updates an order, to reflect the fact that it was received, at least in part. All arguments not mentioned below update the fields with the same name in the aqorders table of the Koha database. -If a partial order is received, splits the order into two. The received -portion must have a booksellerinvoicenumber. +If a partial order is received, splits the order into two. Updates the order with bibilionumber C<$biblionumber> and ordernumber C<$ordernumber>. @@ -1243,7 +1250,7 @@ C<$ordernumber>. sub ModReceiveOrder { my ( $biblionumber, $ordernumber, $quantrec, $user, $cost, - $invoiceno, $freight, $rrp, $budget_id, $datereceived, $received_items + $invoiceid, $rrp, $budget_id, $datereceived, $received_items ) = @_; my $dbh = C4::Context->dbh; @@ -1269,14 +1276,13 @@ sub ModReceiveOrder { UPDATE aqorders SET quantityreceived=? , datereceived=? - , booksellerinvoicenumber=? + , invoiceid=? , unitprice=? - , freight=? , rrp=? , quantity=? WHERE biblionumber=? AND ordernumber=?"); - $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordernumber); + $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$quantrec,$biblionumber,$ordernumber); $sth->finish; # create a new order for the remaining items, and set its bookfund. @@ -1300,10 +1306,10 @@ sub ModReceiveOrder { } } else { $sth=$dbh->prepare("update aqorders - set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?, - unitprice=?,freight=?,rrp=? + set quantityreceived=?,datereceived=?,invoiceid=?, + unitprice=?,rrp=? where biblionumber=? and ordernumber=?"); - $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordernumber); + $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$biblionumber,$ordernumber); $sth->finish; } return $datereceived; @@ -1462,9 +1468,10 @@ sub GetParcel { LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber + LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid WHERE aqbasket.booksellerid = ? - AND aqorders.booksellerinvoicenumber LIKE ? + AND aqinvoices.invoicenumber LIKE ? AND aqorders.datereceived = ? "; my @query_params = ( $supplierid, $code, $datereceived ); @@ -1537,18 +1544,19 @@ sub GetParcels { my $dbh = C4::Context->dbh; my @query_params = (); my $strsth =" - SELECT aqorders.booksellerinvoicenumber, + SELECT aqinvoices.invoicenumber, datereceived,purchaseordernumber, count(DISTINCT biblionumber) AS biblio, sum(quantity) AS itemsexpected, sum(quantityreceived) AS itemsreceived FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno + LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL "; push @query_params, $bookseller; if ( defined $code ) { - $strsth .= ' and aqorders.booksellerinvoicenumber like ? '; + $strsth .= ' and aqinvoices.invoicenumber like ? '; # add a % to the end of the code to allow stemming. push @query_params, "$code%"; } @@ -1563,7 +1571,7 @@ sub GetParcels { push @query_params, $dateto; } - $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived "; + $strsth .= "group by aqinvoices.invoicenumber,datereceived "; # can't use a placeholder to place this column name. # but, we could probably be checking to make sure it is a column that will be fetched. @@ -1779,7 +1787,7 @@ sub GetHistory { aqorders.quantityreceived, aqorders.ecost, aqorders.ordernumber, - aqorders.booksellerinvoicenumber as invoicenumber, + aqinvoices.invoicenumber, aqbooksellers.id as id, aqorders.biblionumber FROM aqorders @@ -1787,7 +1795,8 @@ sub GetHistory { LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber - LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber"; + LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber + LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid"; $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber" if ( C4::Context->preference("IndependantBranches") ); @@ -1841,8 +1850,8 @@ sub GetHistory { } if ($booksellerinvoicenumber) { - $query .= " AND (aqorders.booksellerinvoicenumber LIKE ? OR aqbasket.booksellerinvoicenumber LIKE ?)"; - push @query_params, "%$booksellerinvoicenumber%", "%$booksellerinvoicenumber%"; + $query .= " AND aqinvoices.invoicenumber LIKE ? "; + push @query_params, "%$booksellerinvoicenumber%"; } if ( C4::Context->preference("IndependantBranches") ) { @@ -1983,7 +1992,345 @@ sub AddClaim { "; my $sth = $dbh->prepare($query); $sth->execute($ordernumber); +} + +=head3 GetInvoices + + my @invoices = GetInvoices( + invoicenumber => $invoicenumber, + suppliername => $suppliername, + shipmentdatefrom => $shipmentdatefrom, # ISO format + shipmentdateto => $shipmentdateto, # ISO format + billingdatefrom => $billingdatefrom, # ISO format + billingdateto => $billingdateto, # ISO format + isbneanissn => $isbn_or_ean_or_issn, + title => $title, + author => $author, + publisher => $publisher, + publicationyear => $publicationyear, + branchcode => $branchcode, + order_by => $order_by + ); + +Return a list of invoices that match all given criteria. + +$order_by is "column_name (asc|desc)", where column_name is any of +'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate', +'shipmentcost', 'shipmentcost_budgetid'. + +asc is the default if omitted + +=cut + +sub GetInvoices { + my %args = @_; + + my @columns = qw(invoicenumber booksellerid shipmentdate billingdate + closedate shipmentcost shipmentcost_budgetid); + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT aqinvoices.*, aqbooksellers.name AS suppliername, + COUNT( + DISTINCT IF( + aqorders.datereceived IS NOT NULL, + aqorders.biblionumber, + NULL + ) + ) AS receivedbiblios, + SUM(aqorders.quantityreceived) AS receiveditems + FROM aqinvoices + LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid + LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid + LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber + LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber + LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber + }; + + my @bind_args; + my @bind_strs; + if($args{supplierid}) { + push @bind_strs, " aqinvoices.booksellerid = ? "; + push @bind_args, $args{supplierid}; + } + if($args{invoicenumber}) { + push @bind_strs, " aqinvoices.invoicenumber LIKE ? "; + push @bind_args, "%$args{invoicenumber}%"; + } + if($args{suppliername}) { + push @bind_strs, " aqbooksellers.name LIKE ? "; + push @bind_args, "%$args{suppliername}%"; + } + if($args{shipmentdatefrom}) { + push @bind_strs, " aqinvoices.shipementdate >= ? "; + push @bind_args, $args{shipementdatefrom}; + } + if($args{shipmentdateto}) { + push @bind_strs, " aqinvoices.shipementdate <= ? "; + push @bind_args, $args{shipementdateto}; + } + if($args{billingdatefrom}) { + push @bind_strs, " aqinvoices.billingdate >= ? "; + push @bind_args, $args{billingdatefrom}; + } + if($args{billingdateto}) { + push @bind_strs, " aqinvoices.billingdate <= ? "; + push @bind_args, $args{billingdateto}; + } + if($args{isbneanissn}) { + push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) "; + push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn}; + } + if($args{title}) { + push @bind_strs, " biblio.title LIKE ? "; + push @bind_args, $args{title}; + } + if($args{author}) { + push @bind_strs, " biblio.author LIKE ? "; + push @bind_args, $args{author}; + } + if($args{publisher}) { + push @bind_strs, " biblioitems.publishercode LIKE ? "; + push @bind_args, $args{publisher}; + } + if($args{publicationyear}) { + push @bind_strs, " biblioitems.publicationyear = ? "; + push @bind_args, $args{publicationyear}; + } + if($args{branchcode}) { + push @bind_strs, " aqorders.branchcode = ? "; + push @bind_args, $args{branchcode}; + } + + $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs; + $query .= " GROUP BY aqinvoices.invoiceid "; + + if($args{order_by}) { + my ($column, $direction) = split / /, $args{order_by}; + if(grep /^$column$/, @columns) { + $direction ||= 'ASC'; + $query .= " ORDER BY $column $direction"; + } + } + my $sth = $dbh->prepare($query); + $sth->execute(@bind_args); + + my $results = $sth->fetchall_arrayref({}); + return @$results; +} + +=head3 GetInvoice + + my $invoice = GetInvoice($invoiceid); + +Get informations about invoice with given $invoiceid + +Return a hash filled with aqinvoices.* fields + +=cut + +sub GetInvoice { + my ($invoiceid) = @_; + my $invoice; + + return unless $invoiceid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT * + FROM aqinvoices + WHERE invoiceid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($invoiceid); + + $invoice = $sth->fetchrow_hashref; + return $invoice; +} + +=head3 GetInvoiceDetails + + my $invoice = GetInvoiceDetails($invoiceid) + +Return informations about an invoice + the list of related order lines + +Orders informations are in $invoice->{orders} (array ref) + +=cut + +sub GetInvoiceDetails { + my ($invoiceid) = @_; + my $invoice; + + return unless $invoiceid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT aqinvoices.*, aqbooksellers.name AS suppliername + FROM aqinvoices + LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id + WHERE invoiceid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($invoiceid); + + $invoice = $sth->fetchrow_hashref; + + $query = qq{ + SELECT aqorders.*, biblio.* + FROM aqorders + LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber + WHERE invoiceid = ? + }; + $sth = $dbh->prepare($query); + $sth->execute($invoiceid); + $invoice->{orders} = $sth->fetchall_arrayref({}); + $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails + + return $invoice; +} + +=head3 AddInvoice + + my $invoiceid = AddInvoice( + invoicenumber => $invoicenumber, + booksellerid => $booksellerid, + shipmentdate => $shipmentdate, + billingdate => $billingdate, + closedate => $closedate, + shipmentcost => $shipmentcost, + shipmentcost_budgetid => $shipmentcost_budgetid + ); + +Create a new invoice and return its id or undef if it fails. + +=cut + +sub AddInvoice { + my %invoice = @_; + + return unless(%invoice and $invoice{invoicenumber}); + + my @columns = qw(invoicenumber booksellerid shipmentdate billingdate + closedate shipmentcost shipmentcost_budgetid); + + my @set_strs; + my @set_args; + foreach my $key (keys %invoice) { + if(0 < grep(/^$key$/, @columns)) { + push @set_strs, "$key = ?"; + push @set_args, ($invoice{$key} || undef); + } + } + + my $rv; + if(@set_args > 0) { + my $dbh = C4::Context->dbh; + my $query = "INSERT INTO aqinvoices SET "; + $query .= join (",", @set_strs); + my $sth = $dbh->prepare($query); + $rv = $sth->execute(@set_args); + if($rv) { + $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef); + } + } + return $rv; +} + +=head3 ModInvoice + + ModInvoice( + invoiceid => $invoiceid, # Mandatory + invoicenumber => $invoicenumber, + booksellerid => $booksellerid, + shipmentdate => $shipmentdate, + billingdate => $billingdate, + closedate => $closedate, + shipmentcost => $shipmentcost, + shipmentcost_budgetid => $shipmentcost_budgetid + ); + +Modify an invoice, invoiceid is mandatory. + +Return undef if it fails. + +=cut + +sub ModInvoice { + my %invoice = @_; + + return unless(%invoice and $invoice{invoiceid}); + + my @columns = qw(invoicenumber booksellerid shipmentdate billingdate + closedate shipmentcost shipmentcost_budgetid); + + my @set_strs; + my @set_args; + foreach my $key (keys %invoice) { + if(0 < grep(/^$key$/, @columns)) { + push @set_strs, "$key = ?"; + push @set_args, ($invoice{$key} || undef); + } + } + + my $dbh = C4::Context->dbh; + my $query = "UPDATE aqinvoices SET "; + $query .= join(",", @set_strs); + $query .= " WHERE invoiceid = ?"; + + my $sth = $dbh->prepare($query); + $sth->execute(@set_args, $invoice{invoiceid}); +} + +=head3 CloseInvoice + + CloseInvoice($invoiceid); + +Close an invoice. + +Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef); + +=cut + +sub CloseInvoice { + my ($invoiceid) = @_; + + return unless $invoiceid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + UPDATE aqinvoices + SET closedate = CAST(NOW() AS DATE) + WHERE invoiceid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($invoiceid); +} + +=head3 ReopenInvoice + + ReopenInvoice($invoiceid); + +Reopen an invoice + +Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso')) + +=cut + +sub ReopenInvoice { + my ($invoiceid) = @_; + + return unless $invoiceid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + UPDATE aqinvoices + SET closedate = NULL + WHERE invoiceid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($invoiceid); } 1; diff --git a/C4/Budgets.pm b/C4/Budgets.pm index e9732ac..d07dc75 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -314,9 +314,19 @@ sub GetBudgetSpent { quantityreceived > 0 AND datecancellationprinted IS NULL |); - $sth->execute($budget_id); my $sum = $sth->fetchrow_array; + + $sth = $dbh->prepare(qq| + SELECT SUM(shipmentcost) AS sum + FROM aqinvoices + WHERE shipmentcost_budgetid = ? + AND closedate IS NOT NULL + |); + $sth->execute($budget_id); + my ($shipmentcost_sum) = $sth->fetchrow_array; + $sum += $shipmentcost_sum; + return $sum; } @@ -330,9 +340,19 @@ sub GetBudgetOrdered { quantityreceived = 0 AND datecancellationprinted IS NULL |); - $sth->execute($budget_id); my $sum = $sth->fetchrow_array; + + $sth = $dbh->prepare(qq| + SELECT SUM(shipmentcost) AS sum + FROM aqinvoices + WHERE shipmentcost_budgetid = ? + AND closedate IS NULL + |); + $sth->execute($budget_id); + my ($shipmentcost_sum) = $sth->fetchrow_array; + $sum += $shipmentcost_sum; + return $sum; } diff --git a/acqui/addorderiso2709.pl b/acqui/addorderiso2709.pl index 002fbf5..3051f77 100755 --- a/acqui/addorderiso2709.pl +++ b/acqui/addorderiso2709.pl @@ -191,13 +191,11 @@ if ($op eq ""){ # 3rd add order my $patron = C4::Members->GetMember( borrowernumber => $loggedinuser ); my $branch = C4::Branch->GetBranchDetail( $patron->{branchcode} ); - my ($invoice); # get quantity in the MARC record (1 if none) my $quantity = GetMarcQuantity($marcrecord, C4::Context->preference('marcflavour')) || 1; my %orderinfo = ( "biblionumber", $biblionumber, "basketno", $cgiparams->{'basketno'}, "quantity", $quantity, "branchcode", $branch, - "booksellerinvoicenumber", $invoice, "budget_id", $budget_id, "uncertainprice", 1, "sort1", $cgiparams->{'sort1'},"sort2", $cgiparams->{'sort2'}, "notes", $cgiparams->{'notes'}, "budget_id", $cgiparams->{'budget_id'}, diff --git a/acqui/finishreceive.pl b/acqui/finishreceive.pl index 84391f5..bae8ac3 100755 --- a/acqui/finishreceive.pl +++ b/acqui/finishreceive.pl @@ -45,14 +45,14 @@ my $origquantityrec=$input->param('origquantityrec'); my $quantityrec=$input->param('quantityrec'); my $quantity=$input->param('quantity'); my $unitprice=$input->param('cost'); -my $invoiceno=$input->param('invoice'); -my $datereceived=$input->param('datereceived'); +my $invoiceid = $input->param('invoiceid'); +my $invoice = GetInvoice($invoiceid); +my $invoiceno = $invoice->{invoicenumber}; +my $datereceived= $invoice->{shipmentdate}; my $replacement=$input->param('rrp'); my $gst=$input->param('gst'); -my $freight=$input->param('freight'); my $booksellerid = $input->param('booksellerid'); my $cnt=0; -my $error_url_str; my $ecost = $input->param('ecost'); my $note = $input->param("note"); @@ -101,12 +101,12 @@ if ($quantityrec > $origquantityrec ) { } # save the quantity received. - $datereceived = ModReceiveOrder($biblionumber,$ordernumber, $quantityrec ,$user,$unitprice,$invoiceno,$freight,$replacement,undef,$datereceived, \@received_items); + $datereceived = ModReceiveOrder($biblionumber,$ordernumber, $quantityrec ,$user,$unitprice,$invoice->{invoiceid},$replacement,undef,$datereceived, \@received_items); } update_item( $_ ) foreach GetItemnumbersFromOrder( $ordernumber ); -print $input->redirect("/cgi-bin/koha/acqui/parcel.pl?invoice=$invoiceno&booksellerid=$booksellerid&freight=$freight&gst=$gst&datereceived=$datereceived$error_url_str"); +print $input->redirect("/cgi-bin/koha/acqui/parcel.pl?invoiceid=$invoiceid"); ################################ End of script ################################ diff --git a/acqui/invoice.pl b/acqui/invoice.pl new file mode 100755 index 0000000..28f3841 --- /dev/null +++ b/acqui/invoice.pl @@ -0,0 +1,213 @@ +#!/usr/bin/perl + +# Copyright 2011 BibLibre SARL +# 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 2 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, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +=head1 NAME + +invoice.pl + +=head1 DESCRIPTION + +Invoice details + +=cut + +use strict; +use warnings; + +use CGI; +use C4::Auth; +use C4::Output; +use C4::Acquisition; +use C4::Bookseller qw/GetBookSellerFromId/; +use C4::Budgets; + +my $input = new CGI; +my ($template, $loggedinuser, $cookie, $flags) = get_template_and_user( { + template_name => 'acqui/invoice.tmpl', + query => $input, + type => 'intranet', + authnotrequired => 0, + flagsrequired => { 'acquisition' => '*' }, + debug => 1, +} ); + +my $invoiceid = $input->param('invoiceid'); +my $op = $input->param('op'); + +if($op && $op eq 'close') { + CloseInvoice($invoiceid); + my $referer = $input->param('referer'); + if($referer) { + print $input->redirect($referer); + exit 0; + } +}elsif($op && $op eq 'reopen') { + ReopenInvoice($invoiceid); + my $referer = $input->param('referer'); + if($referer) { + print $input->redirect($referer); + exit 0; + } +}elsif($op && $op eq 'mod') { + my $shipmentdate = $input->param('shipmentdate'); + my $billingdate = $input->param('billingdate'); + my $shipmentcost = $input->param('shipmentcost'); + my $shipment_budget_id = $input->param('shipment_budget_id'); + ModInvoice( + invoiceid => $invoiceid, + shipmentdate => C4::Dates->new($shipmentdate)->output("iso"), + billingdate => C4::Dates->new($billingdate)->output("iso"), + shipmentcost => $shipmentcost, + shipmentcost_budgetid => $shipment_budget_id + ); + $template->param(modified => 1); +} + +my $details = GetInvoiceDetails($invoiceid); +my $bookseller = GetBookSellerFromId($details->{booksellerid}); +my @orders_loop = (); +my $orders = $details->{'orders'}; +my $qty_total; +my @books_loop; +my @book_foot_loop; +my %foot; +my $total_quantity = 0; +my $total_rrp = 0; +my $total_est = 0; +foreach my $order (@$orders) { + my $line = get_infos( $order, $bookseller); + + $total_quantity += $$line{quantity}; + $total_rrp += $order->{quantity} * $order->{rrp}; + $total_est += $order->{quantity} * $order->{'ecost'}; + + my %row = (%$order, %$line); + push @orders_loop, \%row; +} + +my $gist = $bookseller->{gstrate} // C4::Context->preference("gist") // 0; +my $discount = $bookseller->{'discount'} ? ($bookseller->{discount} / 100) : 0; +my $total_est_gste; +my $total_est_gsti; +my $total_rrp_gsti; # RRP Total, GST included +my $total_rrp_gste; # RRP Total, GST excluded +my $gist_est; +my $gist_rrp; +if ($gist){ + # if we have GST + if ( $bookseller->{'listincgst'} ) { + # if prices already includes GST + + # we know $total_rrp_gsti + $total_rrp_gsti = $total_rrp; + # and can reverse compute other values + $total_rrp_gste = $total_rrp_gsti / ( $gist + 1 ); + + $gist_rrp = $total_rrp_gsti - $total_rrp_gste; + $total_est_gste = $total_rrp_gste - ( $total_rrp_gste * $discount ); + $total_est_gsti = $total_est; + } else { + # if prices does not include GST + + # then we use the common way to compute other values + $total_rrp_gste = $total_rrp; + $gist_rrp = $total_rrp_gste * $gist; + $total_rrp_gsti = $total_rrp_gste + $gist_rrp; + $total_est_gste = $total_est; + $total_est_gsti = $total_rrp_gsti - ( $total_rrp_gsti * $discount ); + } + $gist_est = $gist_rrp - ( $gist_rrp * $discount ); +} else { + $total_rrp_gste = $total_rrp_gsti = $total_rrp; + $total_est_gste = $total_est_gsti = $total_est; + $gist_rrp = $gist_est = 0; +} +my $total_gsti_shipment = $total_est_gsti + $details->{shipmentcost}; + +my $format = "%.2f"; +$template->param( + total_rrp_gste => sprintf($format, $total_rrp_gste), + total_rrp_gsti => sprintf($format, $total_rrp_gsti), + total_est_gste => sprintf($format, $total_est_gste), + total_est_gsti => sprintf($format, $total_est_gsti), + gist_rrp => sprintf($format, $gist_rrp), + gist_est => sprintf($format, $gist_est), + total_gsti_shipment => sprintf($format, $total_gsti_shipment), + gist => sprintf($format, $gist * 100), +); + +my $budgets = GetBudgets(); +my @budgets_loop; +my $shipmentcost_budgetid = $details->{shipmentcost_budgetid}; +foreach my $budget (@$budgets) { + next unless CanUserUseBudget($loggedinuser, $budget, $flags); + my %line = %{ $budget }; + if($shipmentcost_budgetid and $budget->{budget_id} == $shipmentcost_budgetid) { + $line{selected} = 1; + } + push @budgets_loop, \%line; +} + +$template->param( + invoiceid => $details->{'invoiceid'}, + invoicenumber => $details->{'invoicenumber'}, + suppliername => $details->{'suppliername'}, + supplierid => $details->{'booksellerid'}, + datereceived => $details->{'datereceived'}, + shipmentdate => $details->{'shipmentdate'}, + billingdate => $details->{'billingdate'}, + invoiceclosedate => $details->{'closedate'}, + shipmentcost => sprintf($format, $details->{'shipmentcost'} || 0), + orders_loop => \@orders_loop, + total_quantity => $total_quantity, + invoiceincgst => $bookseller->{invoiceincgst}, + currency => $bookseller->{listprice}, + DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), + budgets_loop => \@budgets_loop, +); + +sub get_infos { + my $order = shift; + my $bookseller = shift; + my $qty = $order->{'quantity'} || 0; + if ( !defined $order->{quantityreceived} ) { + $order->{quantityreceived} = 0; + } + my $budget = GetBudget( $order->{'budget_id'} ); + + my %line = %{ $order }; + $line{order_received} = ( $qty == $order->{'quantityreceived'} ); + $line{budget_name} = $budget->{budget_name}; + $line{total} = $qty * $order->{ecost}; + + if ( $line{uncertainprice} ) { + $line{rrp} .= ' (Uncertain)'; + } + if ( $line{'title'} ) { + my $volume = $order->{'volume'}; + my $seriestitle = $order->{'seriestitle'}; + $line{'title'} .= " / $seriestitle" if $seriestitle; + $line{'title'} .= " / $volume" if $volume; + } else { + $line{'title'} = "Deleted bibliographic notice, can't find title."; + } + + return \%line; +} + +output_html_with_http_headers $input, $cookie, $template->output; diff --git a/acqui/invoices.pl b/acqui/invoices.pl new file mode 100755 index 0000000..fab7837 --- /dev/null +++ b/acqui/invoices.pl @@ -0,0 +1,156 @@ +#!/usr/bin/perl + +# Copyright 2011 BibLibre SARL +# 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 2 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, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +=head1 NAME + +invoices.pl + +=head1 DESCRIPTION + +Search for invoices + +=cut + +use strict; +use warnings; + +use CGI; +use C4::Auth; +use C4::Output; + +use C4::Acquisition; +use C4::Bookseller qw/GetBookSeller/; +use C4::Branch; + +my $input = new CGI; +my ($template, $loggedinuser, $cookie, $flags) = get_template_and_user( { + template_name => 'acqui/invoices.tmpl', + query => $input, + type => 'intranet', + authnotrequired => 0, + flagsrequired => { 'acquisition' => '*' }, + debug => 1, +} ); + +my $invoicenumber = $input->param('invoicenumber'); +my $supplier = $input->param('supplier'); +my $shipmentdatefrom = $input->param('shipmentdatefrom'); +my $shipmentdateto = $input->param('shipmentdateto'); +my $billingdatefrom = $input->param('billingdatefrom'); +my $billingdateto = $input->param('billingdateto'); +my $isbneanissn = $input->param('isbneanissn'); +my $title = $input->param('title'); +my $author = $input->param('author'); +my $publisher = $input->param('publisher'); +my $publicationyear = $input->param('publicationyear'); +my $branch = $input->param('branch'); +my $op = $input->param('op'); + +my @results_loop = (); +if($op and $op eq "do_search") { + my $shipmentdatefrom_iso = C4::Dates->new($shipmentdatefrom)->output("iso"); + my $shipmentdateto_iso = C4::Dates->new($shipmentdateto)->output("iso"); + my $billingdatefrom_iso = C4::Dates->new($billingdatefrom)->output("iso"); + my $billingdateto_iso = C4::Dates->new($billingdateto)->output("iso"); + my @invoices = GetInvoices( + invoicenumber => $invoicenumber, + suppliername => $supplier, + shipmentdatefrom => $shipmentdatefrom_iso, + shipmentdateto => $shipmentdateto_iso, + billingdatefrom => $billingdatefrom_iso, + billingdateto => $billingdateto_iso, + isbneanissn => $isbneanissn, + title => $title, + author => $author, + publisher => $publisher, + publicationyear => $publicationyear, + branchcode => $branch + ); + foreach (@invoices) { + my %row = ( + invoiceid => $_->{invoiceid}, + billingdate => $_->{billingdate}, + invoicenumber => $_->{invoicenumber}, + suppliername => $_->{suppliername}, + receivedbiblios => $_->{receivedbiblios}, + receiveditems => $_->{receiveditems}, + subscriptionid => $_->{subscriptionid}, + closedate => $_->{closedate}, + ); + push @results_loop, \%row; + } +} + + +# Build suppliers list +my @suppliers = GetBookSeller(undef); +my @suppliers_loop = (); +my $suppliername; +foreach (@suppliers) { + my $selected = 0; + if ($supplier && $supplier == $_->{'id'}) { + $selected = 1; + $suppliername = $_->{'name'}; + } + my %row = ( + suppliername => $_->{'name'}, + supplierid => $_->{'id'}, + selected => $selected, + ); + push @suppliers_loop, \%row; +} + +# Build branches list +my $branches = GetBranches(); +my @branches_loop = (); +my $branchname; +foreach (sort keys %$branches) { + my $selected = 0; + if ($branch && $branch eq $_) { + $selected = 1; + $branchname = $branches->{$_}->{'branchname'}; + } + my %row = ( + branchcode => $_, + branchname => $branches->{$_}->{'branchname'}, + selected => $selected, + ); + push @branches_loop, \%row; +} + +$template->param( + do_search => ($op and $op eq "do_search") ? 1 : 0, + results_loop => \@results_loop, + invoicenumber => $invoicenumber, + supplier => $supplier, + suppliername => $suppliername, + billingdatefrom => $billingdatefrom, + billingdateto => $billingdateto, + isbneanissn => $isbneanissn, + title => $title, + author => $author, + publisher => $publisher, + publicationyear => $publicationyear, + branch => $branch, + branchname => $branchname, + suppliers_loop => \@suppliers_loop, + branches_loop => \@branches_loop, + DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), +); + +output_html_with_http_headers $input, $cookie, $template->output; diff --git a/acqui/orderreceive.pl b/acqui/orderreceive.pl index 85168d5..a0b933b 100755 --- a/acqui/orderreceive.pl +++ b/acqui/orderreceive.pl @@ -40,9 +40,9 @@ to know on what supplier this script has to display receive order. =item receive -=item invoice +=item invoiceid -the number of this invoice. +the id of this invoice. =item freight @@ -82,19 +82,18 @@ use C4::Suggestions; my $input = new CGI; my $dbh = C4::Context->dbh; -my $booksellerid = $input->param('booksellerid'); +my $invoiceid = $input->param('invoiceid'); +my $invoice = GetInvoice($invoiceid); +my $booksellerid = $invoice->{booksellerid}; +my $freight = $invoice->{shipmentcost}; +my $datereceived = $invoice->{shipmentdate}; my $ordernumber = $input->param('ordernumber'); my $search = $input->param('receive'); -my $invoice = $input->param('invoice'); -my $freight = $input->param('freight'); -my $datereceived = $input->param('datereceived'); - $datereceived = $datereceived ? C4::Dates->new($datereceived, 'iso') : C4::Dates->new(); my $bookseller = GetBookSellerFromId($booksellerid); -my $input_gst = ($input->param('gst') eq '' ? undef : $input->param('gst')); -my $gst= $input_gst // $bookseller->{gstrate} // C4::Context->preference("gist") // 0; +my $gst = $bookseller->{gstrate} // C4::Context->preference("gist") // 0; my $results = SearchOrder($ordernumber,$search); my ( $template, $loggedinuser, $cookie ) = get_template_and_user( @@ -195,7 +194,8 @@ if ( $count == 1 ) { unitprice => sprintf( "%.2f",$order->{'unitprice'}), memberfirstname => $member->{firstname} || "", membersurname => $member->{surname} || "", - invoice => $invoice, + invoiceid => $invoice->{invoiceid}, + invoice => $invoice->{invoicenumber}, datereceived => $datereceived->output(), datereceived_iso => $datereceived->output('iso'), notes => $order->{notes}, @@ -209,7 +209,7 @@ else { for ( my $i = 0 ; $i < $count ; $i++ ) { my %line = %{ @$results[$i] }; - $line{invoice} = $invoice; + $line{invoice} = $invoice->{invoicenumber}; $line{datereceived} = $datereceived->output(); $line{freight} = $freight; $line{gst} = $gst; @@ -221,7 +221,8 @@ else { $template->param( loop => \@loop, - booksellerid => $booksellerid, + booksellerid => $booksellerid, + invoiceid => $invoice->{invoiceid}, ); } my $op = $input->param('op'); diff --git a/acqui/parcel.pl b/acqui/parcel.pl index bf14ff0..3619a75 100755 --- a/acqui/parcel.pl +++ b/acqui/parcel.pl @@ -42,8 +42,6 @@ To know the supplier this script has to show orders. is the bookseller invoice number. -=item freight - =item gst @@ -57,7 +55,8 @@ To filter the results list on this given date. =cut use strict; -#use warnings; FIXME - Bug 2505 +use warnings; + use C4::Auth; use C4::Acquisition; use C4::Budgets; @@ -71,16 +70,13 @@ use C4::Suggestions; use JSON; my $input=new CGI; -my $booksellerid=$input->param('booksellerid'); -my $bookseller=GetBookSellerFromId($booksellerid); - -my $invoice=$input->param('invoice') || ''; -my $freight=$input->param('freight'); -my $input_gst = ($input->param('gst') eq '' ? undef : $input->param('gst')); -my $gst= $input_gst // $bookseller->{gstrate} // C4::Context->preference("gist") // 0; -my $datereceived = ($input->param('op') eq 'new') ? C4::Dates->new($input->param('datereceived')) - : C4::Dates->new($input->param('datereceived'), 'iso') ; -$datereceived = C4::Dates->new() unless $datereceived; + +my $invoiceid = $input->param('invoiceid'); +my $invoice = GetInvoiceDetails($invoiceid); +my $booksellerid = $invoice->{booksellerid}; +my $bookseller = GetBookSellerFromId($booksellerid); +my $gst = $bookseller->{gstrate} // C4::Context->preference("gist") // 0; +my $datereceived = C4::Dates->new(); my $code = $input->param('code'); my @rcv_err = $input->param('error'); my @rcv_err_barcode = $input->param('error_bc'); @@ -90,16 +86,17 @@ my $resultsperpage = $input->param('resultsperpage'); $resultsperpage = 20 unless ($resultsperpage); $startfrom=0 unless ($startfrom); -if($input->param('format') eq "json"){ +my $format = $input->param('format') || ''; +if($format eq "json"){ my ($template, $loggedinuser, $cookie) = get_template_and_user({template_name => "acqui/ajax.tmpl", query => $input, - type => "intranet", + type => "intranet", authnotrequired => 0, flagsrequired => {acquisition => 'order_receive'}, debug => 1, }); - + my @datas; my $search = $input->param('search') || ''; my $ean = $input->param('ean') || ''; @@ -111,7 +108,7 @@ if($input->param('format') eq "json"){ foreach my $order (@$orders) { if ( $order->{quantityreceived} < $order->{quantity} ) { my $data = {}; - + $data->{basketno} = $order->{basketno}; $data->{ordernumber} = $order->{ordernumber}; $data->{title} = $order->{title}; @@ -119,14 +116,13 @@ if($input->param('format') eq "json"){ $data->{isbn} = $order->{isbn}; $data->{booksellerid} = $order->{booksellerid}; $data->{biblionumber} = $order->{biblionumber}; - $data->{freight} = $order->{freight}; $data->{quantity} = $order->{quantity}; $data->{ecost} = $order->{ecost}; $data->{ordertotal} = sprintf("%.2f",$order->{ecost}*$order->{quantity}); push @datas, $data; } } - + my $json_text = to_json(\@datas); $template->param(return => $json_text); output_html_with_http_headers $input, $cookie, $template->output; @@ -136,7 +132,7 @@ if($input->param('format') eq "json"){ my ($template, $loggedinuser, $cookie) = get_template_and_user({template_name => "acqui/parcel.tmpl", query => $input, - type => "intranet", + type => "intranet", authnotrequired => 0, flagsrequired => {acquisition => 'order_receive'}, debug => 1, @@ -156,10 +152,9 @@ if( scalar(@rcv_err) ) { } my $cfstr = "%.2f"; # currency format string -- could get this from currency table. -my @parcelitems = GetParcel($booksellerid, $invoice, $datereceived->output('iso')); +my @parcelitems = @{ $invoice->{orders} }; my $countlines = scalar @parcelitems; my $totalprice = 0; -my $totalfreight = 0; my $totalquantity = 0; my $total; my $tototal; @@ -167,15 +162,14 @@ my @loop_received = (); for (my $i = 0 ; $i < $countlines ; $i++) { - #$total=($parcelitems[$i]->{'unitprice'} + $parcelitems[$i]->{'freight'}) * $parcelitems[$i]->{'quantityreceived'}; #weird, are the freight fees counted by book? (pierre) - $total = ($parcelitems[$i]->{'unitprice'}) * $parcelitems[$i]->{'quantityreceived'}; #weird, are the freight fees counted by book? (pierre) + $total = ($parcelitems[$i]->{'unitprice'}) * $parcelitems[$i]->{'quantityreceived'}; $parcelitems[$i]->{'unitprice'} += 0; my %line; %line = %{ $parcelitems[$i] }; - $line{invoice} = $invoice; + $line{invoice} = $invoice->{invoicenumber}; $line{gst} = $gst; $line{total} = sprintf($cfstr, $total); - $line{booksellerid} = $booksellerid; + $line{booksellerid} = $invoice->{booksellerid}; push @loop_received, \%line; $totalprice += $parcelitems[$i]->{'unitprice'}; $line{unitprice} = sprintf($cfstr, $parcelitems[$i]->{'unitprice'}); @@ -185,145 +179,138 @@ for (my $i = 0 ; $i < $countlines ; $i++) { $line{surnamesuggestedby} = $suggestion->{surnamesuggestedby}; $line{firstnamesuggestedby} = $suggestion->{firstnamesuggestedby}; - #double FIXME - totalfreight is redefined later. - -# FIXME - each order in a parcel holds the freight for the whole parcel. This means if you receive a parcel with items from multiple budgets, you'll see the freight charge in each budget.. - if ($i > 0 && $totalfreight != $parcelitems[$i]->{'freight'}) { - warn "FREIGHT CHARGE MISMATCH!!"; - } - $totalfreight = $parcelitems[$i]->{'freight'}; $totalquantity += $parcelitems[$i]->{'quantityreceived'}; $tototal += $total; } -my $pendingorders = GetPendingOrders($booksellerid); -my $countpendings = scalar @$pendingorders; - -# pending orders totals -my ($totalPunitprice, $totalPquantity, $totalPecost, $totalPqtyrcvd); -my $ordergrandtotal; -my @loop_orders = (); -for (my $i = 0 ; $i < $countpendings ; $i++) { - my %line; - %line = %{$pendingorders->[$i]}; - - $line{quantity}+=0; - $line{quantityreceived}+=0; - $line{unitprice}+=0; - $totalPunitprice += $line{unitprice}; - $totalPquantity +=$line{quantity}; - $totalPqtyrcvd +=$line{quantityreceived}; - $totalPecost += $line{ecost}; - $line{ecost} = sprintf("%.2f",$line{ecost}); - $line{ordertotal} = sprintf("%.2f",$line{ecost}*$line{quantity}); - $line{unitprice} = sprintf("%.2f",$line{unitprice}); - $line{invoice} = $invoice; - $line{gst} = $gst; - $line{total} = $total; - $line{booksellerid} = $booksellerid; - $ordergrandtotal += $line{ecost} * $line{quantity}; - - my $biblionumber = $line{'biblionumber'}; - my $countbiblio = CountBiblioInOrders($biblionumber); - my $ordernumber = $line{'ordernumber'}; - my @subscriptions = GetSubscriptionsId ($biblionumber); - my $itemcount = GetItemsCount($biblionumber); - my $holds = GetHolds ($biblionumber); - my @items = GetItemnumbersFromOrder( $ordernumber ); - my $itemholds; - foreach my $item (@items){ - my $nb = GetItemHolds($biblionumber, $item); - if ($nb){ - $itemholds += $nb; +if(!defined $invoice->{closedate}) { + my $pendingorders = GetPendingOrders($booksellerid); + my $countpendings = scalar @$pendingorders; + + # pending orders totals + my ($totalPunitprice, $totalPquantity, $totalPecost, $totalPqtyrcvd); + my $ordergrandtotal; + my @loop_orders = (); + for (my $i = 0 ; $i < $countpendings ; $i++) { + my %line; + %line = %{$pendingorders->[$i]}; + + $line{quantity}+=0; + $line{quantityreceived}+=0; + $line{unitprice}+=0; + $totalPunitprice += $line{unitprice}; + $totalPquantity +=$line{quantity}; + $totalPqtyrcvd +=$line{quantityreceived}; + $totalPecost += $line{ecost}; + $line{ecost} = sprintf("%.2f",$line{ecost}); + $line{ordertotal} = sprintf("%.2f",$line{ecost}*$line{quantity}); + $line{unitprice} = sprintf("%.2f",$line{unitprice}); + $line{invoice} = $invoice; + $line{gst} = $gst; + $line{total} = $total; + $line{booksellerid} = $booksellerid; + $ordergrandtotal += $line{ecost} * $line{quantity}; + + my $biblionumber = $line{'biblionumber'}; + my $countbiblio = CountBiblioInOrders($biblionumber); + my $ordernumber = $line{'ordernumber'}; + my @subscriptions = GetSubscriptionsId ($biblionumber); + my $itemcount = GetItemsCount($biblionumber); + my $holds = GetHolds ($biblionumber); + my @items = GetItemnumbersFromOrder( $ordernumber ); + my $itemholds; + foreach my $item (@items){ + my $nb = GetItemHolds($biblionumber, $item); + if ($nb){ + $itemholds += $nb; + } } - } - my $suggestion = GetSuggestionInfoFromBiblionumber($line{biblionumber}); - $line{suggestionid} = $suggestion->{suggestionid}; - $line{surnamesuggestedby} = $suggestion->{surnamesuggestedby}; - $line{firstnamesuggestedby} = $suggestion->{firstnamesuggestedby}; + my $suggestion = GetSuggestionInfoFromBiblionumber($line{biblionumber}); + $line{suggestionid} = $suggestion->{suggestionid}; + $line{surnamesuggestedby} = $suggestion->{surnamesuggestedby}; + $line{firstnamesuggestedby} = $suggestion->{firstnamesuggestedby}; + + # if the biblio is not in other orders and if there is no items elsewhere and no subscriptions and no holds we can then show the link "Delete order and Biblio" see bug 5680 + $line{can_del_bib} = 1 if $countbiblio <= 1 && $itemcount == scalar @items && !(@subscriptions) && !($holds); + $line{items} = ($itemcount) - (scalar @items); + $line{left_item} = 1 if $line{items} >= 1; + $line{left_biblio} = 1 if $countbiblio > 1; + $line{biblios} = $countbiblio - 1; + $line{left_subscription} = 1 if scalar @subscriptions >= 1; + $line{subscriptions} = scalar @subscriptions; + $line{left_holds} = ($holds >= 1) ? 1 : 0; + $line{left_holds_on_order} = 1 if $line{left_holds}==1 && ($line{items} == 0 || $itemholds ); + $line{holds} = $holds; + $line{holds_on_order} = $itemholds?$itemholds:$holds if $line{left_holds_on_order}; + + + push @loop_orders, \%line if ($i >= $startfrom and $i < $startfrom + $resultsperpage); + } - # if the biblio is not in other orders and if there is no items elsewhere and no subscriptions and no holds we can then show the link "Delete order and Biblio" see bug 5680 - $line{can_del_bib} = 1 if $countbiblio <= 1 && $itemcount == scalar @items && !(@subscriptions) && !($holds); - $line{items} = ($itemcount) - (scalar @items); - $line{left_item} = 1 if $line{items} >= 1; - $line{left_biblio} = 1 if $countbiblio > 1; - $line{biblios} = $countbiblio - 1; - $line{left_subscription} = 1 if scalar @subscriptions >= 1; - $line{subscriptions} = scalar @subscriptions; - $line{left_holds} = 1 if $holds >= 1; - $line{left_holds_on_order} = 1 if $line{left_holds}==1 && ($line{items} == 0 || $itemholds ); - $line{holds} = $holds; - $line{holds_on_order} = $itemholds?$itemholds:$holds if $line{left_holds_on_order}; - - - push @loop_orders, \%line if ($i >= $startfrom and $i < $startfrom + $resultsperpage); -} -$freight = $totalfreight unless $freight; + my $count = $countpendings; -my $count = $countpendings; + if ($count>$resultsperpage){ + my $displaynext=0; + my $displayprev=$startfrom; + if(($count - ($startfrom+$resultsperpage)) > 0 ) { + $displaynext = 1; + } -if ($count>$resultsperpage){ - my $displaynext=0; - my $displayprev=$startfrom; - if(($count - ($startfrom+$resultsperpage)) > 0 ) { - $displaynext = 1; - } + my @numbers = (); + for (my $i=1; $i<$count/$resultsperpage+1; $i++) { + my $highlight=0; + ($startfrom/$resultsperpage==($i-1)) && ($highlight=1); + push @numbers, { number => $i, + highlight => $highlight , + startfrom => ($i-1)*$resultsperpage}; + } - my @numbers = (); - for (my $i=1; $i<$count/$resultsperpage+1; $i++) { - my $highlight=0; - ($startfrom/$resultsperpage==($i-1)) && ($highlight=1); - push @numbers, { number => $i, - highlight => $highlight , - startfrom => ($i-1)*$resultsperpage}; + my $from = $startfrom*$resultsperpage+1; + my $to; + if($count < (($startfrom+1)*$resultsperpage)){ + $to = $count; + } else { + $to = (($startfrom+1)*$resultsperpage); + } + $template->param(numbers=>\@numbers, + displaynext=>$displaynext, + displayprev=>$displayprev, + nextstartfrom=>(($startfrom+$resultsperpage<$count)?$startfrom+$resultsperpage:$count), + prevstartfrom=>(($startfrom-$resultsperpage>0)?$startfrom-$resultsperpage:0) + ); } - my $from = $startfrom*$resultsperpage+1; - my $to; - if($count < (($startfrom+1)*$resultsperpage)){ - $to = $count; - } else { - $to = (($startfrom+1)*$resultsperpage); - } - $template->param(numbers=>\@numbers, - displaynext=>$displaynext, - displayprev=>$displayprev, - nextstartfrom=>(($startfrom+$resultsperpage<$count)?$startfrom+$resultsperpage:$count), - prevstartfrom=>(($startfrom-$resultsperpage>0)?$startfrom-$resultsperpage:0) - ); + $template->param( + countpending => $countpendings, + loop_orders => \@loop_orders, + ordergrandtotal => sprintf($cfstr, $ordergrandtotal), + totalPunitprice => sprintf("%.2f", $totalPunitprice), + totalPquantity => $totalPquantity, + totalPqtyrcvd => $totalPqtyrcvd, + totalPecost => sprintf("%.2f", $totalPecost), + ); } -#$totalfreight=$freight; -$tototal = $tototal + $freight; $template->param( - invoice => $invoice, + invoiceid => $invoice->{invoiceid}, + invoice => $invoice->{invoicenumber}, + invoiceclosedate => $invoice->{closedate}, datereceived => $datereceived->output('iso'), invoicedatereceived => $datereceived->output('iso'), formatteddatereceived => $datereceived->output(), name => $bookseller->{'name'}, - booksellerid => $booksellerid, + booksellerid => $bookseller->{id}, gst => $gst, - freight => $freight, - invoice => $invoice, countreceived => $countlines, loop_received => \@loop_received, - countpending => $countpendings, - loop_orders => \@loop_orders, totalprice => sprintf($cfstr, $totalprice), - totalfreight => $totalfreight, totalquantity => $totalquantity, tototal => sprintf($cfstr, $tototal), - ordergrandtotal => sprintf($cfstr, $ordergrandtotal), gst => $gst, grandtot => sprintf($cfstr, $tototal + $gst), - totalPunitprice => sprintf("%.2f", $totalPunitprice), - totalPquantity => $totalPquantity, - totalPqtyrcvd => $totalPqtyrcvd, - totalPecost => sprintf("%.2f", $totalPecost), resultsperpage => $resultsperpage, (uc(C4::Context->preference("marcflavour"))) => 1 ); output_html_with_http_headers $input, $cookie, $template->output; - diff --git a/acqui/parcels.pl b/acqui/parcels.pl index fdb46d5..4f2849d 100755 --- a/acqui/parcels.pl +++ b/acqui/parcels.pl @@ -44,9 +44,9 @@ To know the supplier this script has to show orders. sort list of order by 'orderby'. Orderby can be equals to * datereceived desc (default value) - * aqorders.booksellerinvoicenumber + * invoicenumber * datereceived - * aqorders.booksellerinvoicenumber desc + * invoicenumber desc =item filter @@ -75,6 +75,7 @@ use C4::Output; use C4::Dates qw/format_date/; use C4::Acquisition; use C4::Bookseller qw/ GetBookSellerFromId /; +use C4::Budgets; my $input = CGI->new; my $booksellerid = $input->param('booksellerid'); @@ -84,9 +85,10 @@ my $code = $input->param('filter'); my $datefrom = $input->param('datefrom'); my $dateto = $input->param('dateto'); my $resultsperpage = $input->param('resultsperpage'); +my $op = $input->param('op'); $resultsperpage ||= 20; -our ( $template, $loggedinuser, $cookie ) = get_template_and_user( +our ( $template, $loggedinuser, $cookie, $flags ) = get_template_and_user( { template_name => 'acqui/parcels.tmpl', query => $input, type => 'intranet', @@ -96,8 +98,38 @@ our ( $template, $loggedinuser, $cookie ) = get_template_and_user( } ); +if($op and $op eq 'new') { + my $invoicenumber = $input->param('invoice'); + my $shipmentdate = $input->param('shipmentdate'); + my $shipmentcost = $input->param('shipmentcost'); + my $shipmentcost_budgetid = $input->param('shipmentcost_budgetid'); + if($shipmentdate) { + $shipmentdate = C4::Dates->new($shipmentdate)->output('iso'); + } + my $invoiceid = AddInvoice( + invoicenumber => $invoicenumber, + booksellerid => $booksellerid, + shipmentdate => $shipmentdate, + shipmentcost => $shipmentcost, + shipmentcost_budgetid => $shipmentcost_budgetid, + ); + if(defined $invoiceid) { + # Successful 'Add' + print $input->redirect("/cgi-bin/koha/acqui/parcel.pl?invoiceid=$invoiceid"); + exit 0; + } else { + $template->param(error_failed_to_create_invoice => 1); + } +} + my $bookseller = GetBookSellerFromId($booksellerid); -my @parcels = GetParcels( $booksellerid, $order, $code, $datefrom, $dateto ); +my @parcels = GetInvoices( + supplierid => $booksellerid, + invoicenumber => $code, + shipmentdatefrom => $datefrom, + shipmentdateto => $dateto, + order_by => $order +); my $count_parcels = @parcels; # multi page display gestion @@ -114,19 +146,28 @@ for my $i ( $startfrom .. $last_row) { push @{$loopres}, { number => $i + 1, - code => $p->{booksellerinvoicenumber}, - nullcode => $p->{booksellerinvoicenumber} eq 'NULL', - emptycode => $p->{booksellerinvoicenumber} eq q{}, - raw_datereceived => $p->{datereceived}, - datereceived => format_date( $p->{datereceived} ), - bibcount => $p->{biblio}, - reccount => $p->{itemsreceived}, - itemcount => $p->{itemsexpected}, + invoiceid => $p->{invoiceid}, + code => $p->{invoicenumber}, + nullcode => $p->{invoicenumber} eq 'NULL', + emptycode => $p->{invoicenumber} eq q{}, + raw_datereceived => $p->{shipmentdate}, + datereceived => format_date( $p->{shipmentdate} ), + bibcount => $p->{receivedbiblios} || 0, + reccount => $p->{receiveditems} || 0, + itemcount => $p->{itemsexpected} || 0, }; } if ($count_parcels) { $template->param( searchresults => $loopres, count => $count_parcels ); } + +my $budgets = GetBudgets(); +my @budgets_loop; +foreach my $budget (@$budgets) { + next unless CanUserUseBudget($loggedinuser, $budget, $flags); + push @budgets_loop, $budget; +} + $template->param( orderby => $order, filter => $code, @@ -135,9 +176,10 @@ $template->param( resultsperpage => $resultsperpage, name => $bookseller->{'name'}, DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), - datereceived_today => C4::Dates->new()->output(), + shipmentdate_today => C4::Dates->new()->output(), booksellerid => $booksellerid, GST => C4::Context->preference('gist'), + budgets => \@budgets_loop, ); output_html_with_http_headers $input, $cookie, $template->output; diff --git a/acqui/spent.pl b/acqui/spent.pl index 0dcba98..2d555f9 100755 --- a/acqui/spent.pl +++ b/acqui/spent.pl @@ -60,10 +60,10 @@ SELECT aqbasket.booksellerid, itype, title, - aqorders.booksellerinvoicenumber, + aqorders.invoiceid, + aqinvoices.invoicenumber, quantityreceived, unitprice, - freight, datereceived, aqorders.biblionumber FROM (aqorders, aqbasket) @@ -73,6 +73,8 @@ LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber LEFT JOIN aqorders_items ON aqorders.ordernumber=aqorders_items.ordernumber +LEFT JOIN aqinvoices ON + aqorders.invoiceid = aqinvoices.invoiceid WHERE aqorders.basketno=aqbasket.basketno AND budget_id=? AND @@ -85,27 +87,48 @@ $sth->execute($bookfund); if ( $sth->err ) { die "An error occurred fetching records: " . $sth->errstr; } -my $total = 0; +my $subtotal = 0; my $toggle; my @spent; while ( my $data = $sth->fetchrow_hashref ) { my $recv = $data->{'quantityreceived'}; if ( $recv > 0 ) { - my $subtotal = $recv * ( $data->{'unitprice'} + $data->{'freight'} ); - $data->{'subtotal'} = sprintf( "%.2f", $subtotal ); - $data->{'freight'} = sprintf( "%.2f", $data->{'freight'} ); + my $rowtotal = $recv * $data->{'unitprice'}; + $data->{'rowtotal'} = sprintf( "%.2f", $rowtotal ); $data->{'unitprice'} = sprintf( "%.2f", $data->{'unitprice'} ); - $total += $subtotal; + $subtotal += $rowtotal; push @spent, $data; } } -$total = sprintf( "%.2f", $total ); -$template->{VARS}->{'fund'} = $bookfund; -$template->{VARS}->{'spent'} = \@spent; -$template->{VARS}->{'total'} = $total; -$template->{VARS}->{'fund_code'} = $fund_code; +my $total = $subtotal; +$query = qq{ + SELECT invoicenumber, shipmentcost + FROM aqinvoices + WHERE shipmentcost_budgetid = ? +}; +$sth = $dbh->prepare($query); +$sth->execute($bookfund); +my @shipmentcosts; +while (my $data = $sth->fetchrow_hashref) { + push @shipmentcosts, { + shipmentcost => sprintf("%.2f", $data->{shipmentcost}), + invoicenumber => $data->{invoicenumber} + }; + $total += $data->{shipmentcost}; +} $sth->finish; +$total = sprintf( "%.2f", $total ); + +$template->param( + fund => $bookfund, + spent => \@spent, + subtotal => $subtotal, + shipmentcosts => \@shipmentcosts, + total => $total, + fund_code => $fund_code +); + output_html_with_http_headers $input, $cookie, $template->output; diff --git a/catalogue/moredetail.pl b/catalogue/moredetail.pl index 809430c..e8df533 100755 --- a/catalogue/moredetail.pl +++ b/catalogue/moredetail.pl @@ -152,17 +152,19 @@ foreach my $item (@items){ } my $order = GetOrderFromItemnumber( $item->{'itemnumber'} ); - my $basket = GetBasket( $order->{'basketno'} ); - $item->{'booksellerid'} = $basket->{'booksellerid'}; $item->{'ordernumber'} = $order->{'ordernumber'}; $item->{'basketno'} = $order->{'basketno'}; - $item->{'booksellerinvoicenumber'} = $order->{'booksellerinvoicenumber'}; $item->{'orderdate'} = $order->{'entrydate'}; if ($item->{'basketno'}){ my $basket = GetBasket($item->{'basketno'}); my $bookseller = GetBookSellerFromId($basket->{'booksellerid'}); $item->{'vendor'} = $bookseller->{'name'}; } + $item->{'invoiceid'} = $order->{'invoiceid'}; + if($item->{invoiceid}) { + my $invoice = GetInvoice($item->{invoiceid}); + $item->{invoicenumber} = $invoice->{invoicenumber} if $invoice; + } $item->{'datereceived'} = $order->{'datereceived'}; if ($item->{notforloantext} or $item->{itemlost} or $item->{damaged} or $item->{wthdrawn}) { diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 69304e1..dccf83a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2744,7 +2744,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `listprice` decimal(28,6) default NULL, -- the vendor price for this line item `totalamount` decimal(28,6) default NULL, -- not used? always NULL `datereceived` date default NULL, -- the date this order was received - `booksellerinvoicenumber` mediumtext, -- the invoice number this line item was received on + invoiceid int(11) default NULL, -- id of invoice `freight` decimal(28,6) default NULL, -- shipping costs (not used) `unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far @@ -2776,7 +2776,8 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items KEY `biblionumber` (`biblionumber`), KEY `budget_id` (`budget_id`), CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2793,6 +2794,27 @@ CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisiti KEY `ordernumber` (`ordernumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table aqinvoices +-- + +DROP TABLE IF EXISTS aqinvoices; +CREATE TABLE aqinvoices ( + invoiceid int(11) NOT NULL AUTO_INCREMENT, -- ID of the invoice, primary key + invoicenumber mediumtext NOT NULL, -- Name of invoice + booksellerid int(11) NOT NULL, -- foreign key to aqbooksellers + shipmentdate date default NULL, -- date of shipment + billingdate date default NULL, -- date of billing + closedate date default NULL, -- invoice close date, NULL means the invoice is open + shipmentcost decimal(28,6) default NULL, -- shipment cost + shipmentcost_budgetid int(11) default NULL, -- foreign key to aqbudgets, link the shipment cost to a budget + PRIMARY KEY (invoiceid), + CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + -- -- Table structure for table `fieldmapping` -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index ed0c674..f501547 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5635,6 +5635,66 @@ if(C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(" + CREATE TABLE aqinvoices ( + invoiceid int(11) NOT NULL AUTO_INCREMENT, + invoicenumber mediumtext NOT NULL, + booksellerid int(11) NOT NULL, + shipmentdate date default NULL, + billingdate date default NULL, + closedate date default NULL, + shipmentcost decimal(28,6) default NULL, + shipmentcost_budgetid int(11) default NULL, + PRIMARY KEY (invoiceid), + CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 + "); + + # Fill this new table with existing invoices + my $sth = $dbh->prepare(" + SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid, aqorders.datereceived + FROM aqorders + LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno + WHERE aqorders.booksellerinvoicenumber IS NOT NULL + AND aqorders.booksellerinvoicenumber != '' + GROUP BY aqorders.booksellerinvoicenumber + "); + $sth->execute; + my $results = $sth->fetchall_arrayref({}); + $sth = $dbh->prepare(" + INSERT INTO aqinvoices (invoicenumber, booksellerid, shipmentdate) VALUES (?,?,?) + "); + foreach(@$results) { + $sth->execute($_->{invoicenumber}, $_->{booksellerid}, $_->{datereceived}); + } + + # Add the column in aqorders, fill it with correct value + # and then drop booksellerinvoicenumber column + $dbh->do(" + ALTER TABLE aqorders + ADD COLUMN invoiceid int(11) default NULL AFTER booksellerinvoicenumber, + ADD CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE + "); + + $dbh->do(" + UPDATE aqorders, aqinvoices + SET aqorders.invoiceid = aqinvoices.invoiceid + WHERE aqorders.booksellerinvoicenumber = aqinvoices.invoicenumber + "); + + $dbh->do(" + ALTER TABLE aqorders + DROP COLUMN booksellerinvoicenumber + "); + + print "Upgrade to $DBversion done (Add aqinvoices table) \n"; + SetVersion ($DBversion); +} + + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/acquisitions-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/acquisitions-menu.inc index ac1a7a4..439118d 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/acquisitions-menu.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/acquisitions-menu.inc @@ -1,6 +1,7 @@