From f37fae7e066188325f4e3a6e87cff68cb1e7d9f5 Mon Sep 17 00:00:00 2001 From: Srdjan Jankovic Date: Thu, 8 Dec 2011 11:17:01 +1300 Subject: [PATCH] bug_6504: Reintroduced shipping costs when receiving orders Calculate freight per item Replaced GetBudgetSpent() and GetBudgetOrdered with a subquery() --- C4/Acquisition.pm | 4 +- C4/Budgets.pm | 69 ++++++------------- acqui/acqui-home.pl | 2 - acqui/addorder.pl | 3 + acqui/basket.pl | 41 ++++++++---- acqui/neworderempty.pl | 1 + acqui/parcel.pl | 49 ++++++-------- .../intranet-tmpl/prog/en/modules/acqui/basket.tt | 6 ++ .../prog/en/modules/acqui/neworderempty.tt | 6 ++ .../prog/en/modules/acqui/orderreceive.tt | 5 +- .../intranet-tmpl/prog/en/modules/acqui/parcel.tt | 21 +++---- .../intranet-tmpl/prog/en/modules/acqui/parcels.tt | 5 +- 12 files changed, 104 insertions(+), 108 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 7127f24..37c250b 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -712,7 +712,8 @@ sub GetPendingOrders { my $strsth = " SELECT ".($grouped?"count(*),":"")."aqbasket.basketno, surname,firstname,aqorders.*,biblio.*,biblioitems.isbn, - aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname + aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname, + (SELECT count(*) FROM aqorders_items WHERE aqorders_items.ordernumber=aqorders.ordernumber) AS items FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber @@ -1266,6 +1267,7 @@ sub GetParcel { aqorders.listprice, aqorders.rrp, aqorders.ecost, + aqorders.freight, biblio.title FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno diff --git a/C4/Budgets.pm b/C4/Budgets.pm index 7c867e0..86dd2e6 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -39,8 +39,6 @@ BEGIN { &AddBudget &ModBudget &DelBudget - &GetBudgetSpent - &GetBudgetOrdered &GetPeriodsCount &GetChildBudgetsSpent @@ -300,38 +298,6 @@ sub ModBudgetPlan { } # ------------------------------------------------------------------- -sub GetBudgetSpent { - my ($budget_id) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare(qq| - SELECT SUM( COALESCE(unitprice, ecost) * quantity ) AS sum FROM aqorders - WHERE budget_id = ? AND - quantityreceived > 0 AND - datecancellationprinted IS NULL - |); - - $sth->execute($budget_id); - my $sum = $sth->fetchrow_array; - return $sum; -} - -# ------------------------------------------------------------------- -sub GetBudgetOrdered { - my ($budget_id) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare(qq| - SELECT SUM(ecost * quantity) AS sum FROM aqorders - WHERE budget_id = ? AND - quantityreceived = 0 AND - datecancellationprinted IS NULL - |); - - $sth->execute($budget_id); - my $sum = $sth->fetchrow_array; - return $sum; -} - -# ------------------------------------------------------------------- sub GetBudgetAuthCats { my ($budget_period_id) = shift; # now, populate the auth_cats_loop used in the budget planning button @@ -431,14 +397,27 @@ sub ModBudgetPeriod { } # ------------------------------------------------------------------- +my $AQORDERS_CUMULATIVE_SUBQUERY = < 0 THEN COALESCE(freight, 0) + ELSE 0 + END ) AS budget_spent, + SUM( CASE WHEN quantityreceived > 0 THEN 0 + ELSE ecost * quantity + COALESCE(freight, 0) + END ) AS budget_ordered, + budget_id +FROM aqorders GROUP BY budget_id +EOQ sub GetBudgetHierarchy { my ( $budget_period_id, $branchcode, $owner ) = @_; my @bind_params; my $dbh = C4::Context->dbh; my $query = qq| - SELECT aqbudgets.*, aqbudgetperiods.budget_period_active + SELECT aqbudgets.*, aqbudgetperiods.budget_period_active, aqord.budget_spent, aqord.budget_ordered FROM aqbudgets - JOIN aqbudgetperiods USING (budget_period_id)|; + JOIN aqbudgetperiods USING (budget_period_id) + LEFT OUTER JOIN ( $AQORDERS_CUMULATIVE_SUBQUERY ) aqord USING (budget_id) + |; my @where_strings; # show only period X if requested @@ -548,18 +527,12 @@ sub GetBudgetHierarchy { $moo =~ s/\ /\ \;/g; $r->{'budget_name_indent'} = $moo; - $r->{'budget_spent'} = GetBudgetSpent( $r->{'budget_id'} ); - $r->{'budget_amount_total'} = $r->{'budget_amount'}; - # foreach sub-levels - my $unalloc_count ; - foreach my $sub (@subs_arr) { my $sub_budget = GetBudget($sub); - $r->{budget_spent_sublevel} += GetBudgetSpent( $sub_budget->{'budget_id'} ); - $unalloc_count += $sub_budget->{'budget_amount'}; + $r->{budget_spent_sublevel} += $sub_budget->{'budget_spent'}; } } return \@sort; @@ -603,6 +576,7 @@ sub GetBudget { my $query = " SELECT * FROM aqbudgets + LEFT OUTER JOIN ( $AQORDERS_CUMULATIVE_SUBQUERY ) aqord USING (budget_id) WHERE budget_id=? "; my $sth = $dbh->prepare($query); @@ -626,14 +600,15 @@ sub GetChildBudgetsSpent { my $query = " SELECT * FROM aqbudgets - WHERE budget_parent_id=? + LEFT OUTER JOIN ( $AQORDERS_CUMULATIVE_SUBQUERY ) aqord USING (budget_id) + WHERE budget_id=? OR budget_parent_id=? "; my $sth = $dbh->prepare($query); - $sth->execute( $budget_id ); + $sth->execute( $budget_id, $budget_id, ); my $result = $sth->fetchall_arrayref({}); - my $total_spent = GetBudgetSpent($budget_id); + my $total_spent = 0; if ($result){ - $total_spent += GetChildBudgetsSpent($_->{"budget_id"}) foreach @$result; + $total_spent += $_->{"budget_spent"} foreach @$result; } return $total_spent; } diff --git a/acqui/acqui-home.pl b/acqui/acqui-home.pl index 69482a8..96a747e 100755 --- a/acqui/acqui-home.pl +++ b/acqui/acqui-home.pl @@ -110,8 +110,6 @@ foreach my $budget ( @{$budget_arr} ) { $budget->{budget_amount} = 0; } - $budget->{'budget_ordered'} = GetBudgetOrdered( $budget->{'budget_id'} ); - $budget->{'budget_spent'} = GetBudgetSpent( $budget->{'budget_id'} ); if ( !defined $budget->{budget_spent} ) { $budget->{budget_spent} = 0; } diff --git a/acqui/addorder.pl b/acqui/addorder.pl index 121d20d..8e85067 100755 --- a/acqui/addorder.pl +++ b/acqui/addorder.pl @@ -103,6 +103,8 @@ budget_id used to pay this order. =item C +=item C + =item C =item C @@ -174,6 +176,7 @@ $orderinfo->{'uncertainprice'} ||= 0; #my $gst = $input->param('GST'); #my $budget = $input->param('budget'); #my $cost = $input->param('cost'); +#my $freight = $input->param('freight'); #my $sub = $input->param('sub'); #my $purchaseorder = $input->param('purchaseordernumber'); #my $invoice = $input->param('invoice'); diff --git a/acqui/basket.pl b/acqui/basket.pl index b836280..d34c744 100755 --- a/acqui/basket.pl +++ b/acqui/basket.pl @@ -234,12 +234,15 @@ if ( $op eq 'delete_confirm' ) { my $total_rrp_gste; # RRP Total, GST excluded my $gist_rrp; my $total_rrp_est; + my $total_freight; + my $total_freight_gsti; # Freight Total, GST included + my $total_freight_gste; # Freight Total, GST excluded + my $gist_freight; my $qty_total; my @books_loop; for my $order ( @results ) { - my $rrp = $order->{'listprice'} || 0; my $qty = $order->{'quantity'} || 0; if (!defined $order->{quantityreceived}) { $order->{quantityreceived} = 0; @@ -251,10 +254,10 @@ if ( $op eq 'delete_confirm' ) { } my $budget = GetBudget( $order->{'budget_id'} ); - $rrp = ConvertCurrency( $order->{'currency'}, $rrp ); $total_rrp += $qty * $order->{'rrp'}; - my $line_total = $qty * $order->{'ecost'}; + $total_freight += $order->{'freight'} || 0; + my $line_total = $qty * $order->{'ecost'} + $order->{'freight'} || 0; $total_rrp_est += $qty * $order->{'ecost'}; # FIXME: what about the "actual cost" field? $qty_total += $qty; @@ -285,11 +288,12 @@ if ( $op eq 'delete_confirm' ) { $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}; - $line{order_received} = ( $qty == $order->{'quantityreceived'} ); + $line{order_received} = ( $qty == $order->{quantityreceived} ); $line{basketno} = $basketno; $line{budget_name} = $budget->{budget_name}; - $line{rrp} = sprintf( "%.2f", $line{'rrp'} ); - $line{ecost} = sprintf( "%.2f", $line{'ecost'} ); + $line{rrp} = sprintf( "%.2f", $line{rrp} ); + $line{ecost} = sprintf( "%.2f", $line{ecost} ); + $line{freight} = sprintf( "%.2f", $line{freight} ); $line{line_total} = sprintf( "%.2f", $line_total ); if ($line{uncertainprice}) { $template->param( uncertainprices => 1 ); @@ -314,19 +318,26 @@ my $total_est_gste; $total_rrp_gsti = $total_rrp; # we know $total_rrp_gsti $total_rrp_gste = $total_rrp_gsti / ( $gist + 1 ); # and can reverse compute other values $gist_rrp = $total_rrp_gsti - $total_rrp_gste; # - $total_est_gste = $total_rrp_gste - ( $total_rrp_gste * $discount ); - $total_est_gsti = $total_rrp_est; + $total_freight_gsti = $total_freight; + $total_freight_gste = $total_freight_gsti / ( $gist + 1 ); + $gist_freight = $total_freight_gsti - $total_freight_gste; # + $total_est_gste = $total_rrp_gste - ( $total_rrp_gste * $discount ) + $total_freight_gste; + $total_est_gsti = $total_rrp_est + $total_freight_gsti; } else { # if prices does not include GST $total_rrp_gste = $total_rrp; # then we use the common way to compute other values $gist_rrp = $total_rrp_gste * $gist; # $total_rrp_gsti = $total_rrp_gste + $gist_rrp; # - $total_est_gste = $total_rrp_est; - $total_est_gsti = $total_rrp_gsti - ( $total_rrp_gsti * $discount ); + $total_freight_gste = $total_freight; + $gist_freight = $total_freight_gste * $gist; + $total_freight_gsti = $total_freight_gste + $gist_freight; + $total_est_gste = $total_rrp_est + $total_freight_gste; + $total_est_gsti = $total_rrp_gsti - ( $total_rrp_gsti * $discount ) + $total_freight_gsti; } - $gist_est = $gist_rrp - ( $gist_rrp * $discount ); + $gist_est = $gist_rrp - ( $gist_rrp * $discount ) + $gist_freight; } else { $total_rrp_gsti = $total_rrp; - $total_est_gsti = $total_rrp_est; + $total_freight_gsti = $total_freight; + $total_est_gsti = $total_rrp_est + $total_freight_gsti; } my $contract = &GetContract($basket->{contractnumber}); @@ -361,13 +372,15 @@ my $total_est_gste; books_loop => \@books_loop, gist_rate => sprintf( "%.2f", $gist * 100 ) . '%', total_rrp_gste => sprintf( "%.2f", $total_rrp_gste ), + total_freight_gste => sprintf( "%.2f", $total_freight_gste ), total_est_gste => sprintf( "%.2f", $total_est_gste ), gist_est => sprintf( "%.2f", $gist_est ), gist_rrp => sprintf( "%.2f", $gist_rrp ), + gist_freight => sprintf( "%.2f", $gist_freight ), total_rrp_gsti => sprintf( "%.2f", $total_rrp_gsti ), + total_freight_gsti => sprintf( "%.2f", $total_freight_gsti ), total_est_gsti => sprintf( "%.2f", $total_est_gsti ), -# currency => $bookseller->{'listprice'}, - currency => $cur->{'currency'}, + currency => $cur->{'currency'}, qty_total => $qty_total, GST => $gist, basketgroups => $basketgroups, diff --git a/acqui/neworderempty.pl b/acqui/neworderempty.pl index 459dfe8..f3cd048 100755 --- a/acqui/neworderempty.pl +++ b/acqui/neworderempty.pl @@ -384,6 +384,7 @@ $template->param( quantityrec => $data->{'quantity'}, rrp => $data->{'rrp'}, listprice => sprintf("%.2f", $data->{'listprice'}||$data->{'price'}||$listprice), + freight => sprintf("%.2f", $data->{'freight'}||0), total => sprintf("%.2f", ($data->{'ecost'}||0)*($data->{'quantity'}||0) ), ecost => $data->{'ecost'}, unitprice => sprintf("%.2f", $data->{'unitprice'}), diff --git a/acqui/parcel.pl b/acqui/parcel.pl index c256c60..a00cfeb 100755 --- a/acqui/parcel.pl +++ b/acqui/parcel.pl @@ -166,39 +166,38 @@ 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]->{'freight'}; $parcelitems[$i]->{'unitprice'} += 0; my %line; %line = %{ $parcelitems[$i] }; $line{invoice} = $invoice; $line{gst} = $gst; + $line{freight} = sprintf($cfstr, $line{freight}); $line{total} = sprintf($cfstr, $total); $line{supplierid} = $supplierid; push @loop_received, \%line; $totalprice += $parcelitems[$i]->{'unitprice'}; $line{unitprice} = sprintf($cfstr, $parcelitems[$i]->{'unitprice'}); - #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'}; + $totalfreight += $parcelitems[$i]->{'freight'}; $totalquantity += $parcelitems[$i]->{'quantityreceived'}; $tototal += $total; } my $pendingorders = GetPendingOrders($supplierid); -my $countpendings = scalar @$pendingorders; +my $countpendingitems = 0; +if ($pendingorders) { + $countpendingitems += $_->{quantity} || 1 foreach @$pendingorders; +} +my $freight_per_item = $freight && $countpendingitems ? $freight/$countpendingitems : 0; # 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]}; +for (my $i = $startfrom; $i < $startfrom + $resultsperpage; $i++) { + last unless $pendingorders->[$i]; + my %line = %{$pendingorders->[$i]}; $line{quantity}+=0; $line{quantityreceived}+=0; @@ -210,11 +209,12 @@ for (my $i = 0 ; $i < $countpendings ; $i++) { $line{ecost} = sprintf("%.2f",$line{ecost}); $line{ordertotal} = sprintf("%.2f",$line{ecost}*$line{quantity}); $line{unitprice} = sprintf("%.2f",$line{unitprice}); + $line{freight} = sprintf("%.2f",$freight_per_item * $line{quantity}); $line{invoice} = $invoice; $line{gst} = $gst; $line{total} = $total; $line{supplierid} = $supplierid; - $ordergrandtotal += $line{ecost} * $line{quantity}; + $ordergrandtotal += $line{ecost} * $line{quantity} + $line{freight} || 0; my $biblionumber = $line{'biblionumber'}; my $countbiblio = CountBiblioInOrders($biblionumber); @@ -244,22 +244,20 @@ for (my $i = 0 ; $i < $countpendings ; $i++) { $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); + push @loop_orders, \%line; } -$freight = $totalfreight unless $freight; -my $count = $countpendings; +my $countpendings = $pendingorders ? scalar (@$pendingorders) : 0; -if ($count>$resultsperpage){ +if ($countpendings>$resultsperpage){ my $displaynext=0; my $displayprev=$startfrom; - if(($count - ($startfrom+$resultsperpage)) > 0 ) { + if(($countpendings - ($startfrom+$resultsperpage)) > 0 ) { $displaynext = 1; } my @numbers = (); - for (my $i=1; $i<$count/$resultsperpage+1; $i++) { + for (my $i=1; $i<$countpendings/$resultsperpage+1; $i++) { my $highlight=0; ($startfrom/$resultsperpage==($i-1)) && ($highlight=1); push @numbers, { number => $i, @@ -269,22 +267,19 @@ if ($count>$resultsperpage){ my $from = $startfrom*$resultsperpage+1; my $to; - if($count < (($startfrom+1)*$resultsperpage)){ - $to = $count; + if($countpendings < (($startfrom+1)*$resultsperpage)){ + $to = $countpendings; } else { $to = (($startfrom+1)*$resultsperpage); } $template->param(numbers=>\@numbers, displaynext=>$displaynext, displayprev=>$displayprev, - nextstartfrom=>(($startfrom+$resultsperpage<$count)?$startfrom+$resultsperpage:$count), + nextstartfrom=>(($startfrom+$resultsperpage<$countpendings)?$startfrom+$resultsperpage:$countpendings), prevstartfrom=>(($startfrom-$resultsperpage>0)?$startfrom-$resultsperpage:0) ); } -#$totalfreight=$freight; -$tototal = $tototal + $freight; - $template->param( invoice => $invoice, datereceived => $datereceived->output('iso'), @@ -300,7 +295,7 @@ $template->param( countpending => $countpendings, loop_orders => \@loop_orders, totalprice => sprintf($cfstr, $totalprice), - totalfreight => $totalfreight, + totalfreight => sprintf($cfstr, $totalfreight), totalquantity => $totalquantity, tototal => sprintf($cfstr, $tototal), ordergrandtotal => sprintf($cfstr, $ordergrandtotal), 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 c0b62f2..d0bd241 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt @@ -203,6 +203,7 @@ RRP Est. Qty. + Shipping Total Fund [% IF ( active ) %] @@ -220,6 +221,7 @@ [% total_rrp_gste %]   [% qty_total %] + [% total_freight_gste %] [% total_est_gste %] [% IF ( active ) %] [% IF ( closedate ) %] @@ -234,6 +236,7 @@ [% gist_rrp %]     + [% gist_freight %] [% gist_est %] @@ -241,6 +244,7 @@ [% total_rrp_gsti %]   [% qty_total %] + [% total_freight_gsti %] [% total_est_gsti %] [% ELSE %] @@ -249,6 +253,7 @@ [% total_rrp_gsti %]   [% qty_total %] + [% total_freight_gsti %] [% total_est_gsti %] [% END %] @@ -269,6 +274,7 @@ [% books_loo.rrp %] [% books_loo.ecost %] [% books_loo.quantity %] + [% books_loo.freight %] [% books_loo.line_total %] [% books_loo.budget_name %] [% IF ( active ) %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt index d58582e..7bbeb3c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt @@ -464,6 +464,12 @@ $(document).ready(function() [% END %] + [% IF ( quantityrec ) %] +
  • + + +
  • + [% END %]
  • diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/orderreceive.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/orderreceive.tt index ac39141..60018b0 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/orderreceive.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/orderreceive.tt @@ -82,7 +82,6 @@ -
    @@ -126,7 +125,9 @@ [% ELSE %] - [% END %]
  • + [% END %] +
  • + diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/parcel.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/parcel.tt index eb5492e..c787c90 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/parcel.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/parcel.tt @@ -213,6 +213,7 @@ View Record Quantity Unit cost + Shipping Order cost     @@ -222,6 +223,7 @@ TOTAL [% totalPquantity %]   +   [% ordergrandtotal %]     @@ -245,6 +247,7 @@ MARC | Card [% loop_order.quantity %] [% loop_order.ecost %] + [% loop_order.freight %] [% loop_order.ordertotal %] Receive @@ -310,6 +313,7 @@ Quantity Est cost Actual cost + Shipping TOTAL @@ -318,18 +322,10 @@ SUBTOTAL   [% totalprice %] + [% totalfreight %] [% tototal %] - - [% IF ( totalfreight ) %] - -   - - Shipping - [% totalfreight %] - - [% END %] - [% IF ( gst ) %] + [% IF ( gst ) %]

    @@ -340,11 +336,11 @@ Tax rate [% gst %] - [% END %] + [% END %] TOTAL [% totalquantity %] -   +   [% grandtot %] @@ -366,6 +362,7 @@ [% loop_receive.quantityreceived %] [% loop_receive.ecost %] [% loop_receive.unitprice %] + [% loop_receive.freight %] [% loop_receive.total %] [% END %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/parcels.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/parcels.tt index a7d704d..4478f34 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/parcels.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/parcels.tt @@ -96,11 +96,10 @@ [% END %] - +

  • Show Calendar -- 1.6.5