From 448af7f3bfcadf460ca01e796a4c07295aa0447f Mon Sep 17 00:00:00 2001 From: Srdjan Jankovic Date: Fri, 25 Nov 2011 15:21:58 +1300 Subject: [PATCH] bug_6504: Reintroduced shipping costs Shipping cost are per parcel, not per item. Enter shipping costs when receiving orders. Display and take in account shipping costs on order and budget pages. 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 | 2 +- .../intranet-tmpl/prog/en/modules/acqui/parcel.tt | 21 +++---- .../intranet-tmpl/prog/en/modules/acqui/parcels.tt | 5 +- 12 files changed, 102 insertions(+), 107 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 d7f7e96..bab1ea9 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..104ec80 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 += $_->{items} || 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",$line{freight}); $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 68c290a..cf8e019 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 7147b19..ce41f6e 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 @@ -
    @@ -127,6 +126,7 @@ [% ELSE %] [% 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 45eb591..784767e 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