From 19dc6bb5b3771c990c3bb88fe607239a98072ced Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Fr=C3=A9d=C3=A9rick?= Date: Tue, 10 Dec 2013 15:19:35 -0500 Subject: [PATCH] Add the "Orders by budget" report. --- C4/Budgets.pm | 146 ++++++++++++ .../prog/en/modules/reports/orders_by_budget.tt | 115 +++++++++ .../prog/en/modules/reports/reports-home.tt | 9 +- reports/orders_by_budget.pl | 250 ++++++++++++++++++++ t/db_dependent/Acquisition.t | 30 +++- t/db_dependent/Budgets.t | 22 ++- 6 files changed, 566 insertions(+), 6 deletions(-) create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt create mode 100755 reports/orders_by_budget.pl diff --git a/C4/Budgets.pm b/C4/Budgets.pm index 334e610..36dea23 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -36,6 +36,10 @@ BEGIN { &GetBudget &GetBudgetByOrderNumber &GetBudgets + &GetActiveBudgets + &GetBudgetReport + &GetBudgetsReport + &GetActiveBudgetsReport &GetBudgetHierarchy &AddBudget &ModBudget @@ -55,6 +59,7 @@ BEGIN { &GetBudgetPeriods &ModBudgetPeriod &AddBudgetPeriod + &GetBudgetPeriodDescription &DelBudgetPeriod &GetAuthvalueDropbox @@ -471,6 +476,26 @@ sub GetBudgetPeriod { } # ------------------------------------------------------------------- +sub GetBudgetPeriodDescription { + my ($budget_id) = @_; + my $dbh = C4::Context->dbh; + my $sth; + if ($budget_id) { + $sth = $dbh->prepare( + "SELECT budget_period_description + FROM aqbudgetperiods bp + INNER JOIN aqbudgets b + ON bp.budget_period_id = b.budget_period_id + WHERE b.budget_id=? + " + ); + $sth->execute($budget_id); + } + my $data = $sth->fetchrow_hashref; + return $data; +} + +# ------------------------------------------------------------------- sub DelBudgetPeriod{ my ($budget_period_id) = @_; my $dbh = C4::Context->dbh; @@ -697,6 +722,127 @@ sub GetBudgetByOrderNumber { return $result; } +=head2 GetBudgetReport + + &GetBudgetReport(); + +Get one specific budget for reports without cancelled baskets. + +=cut + +# -------------------------------------------------------------------- +sub GetBudgetReport { + my ( $budget_id ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT o.*, b.budget_name + FROM aqbudgets b + INNER JOIN aqorders o + ON b.budget_id = o.budget_id + WHERE b.budget_id=? + AND (o.datecancellationprinted IS NULL OR o.datecancellationprinted='0000-00-00') + ORDER BY b.budget_name + "; + my $sth = $dbh->prepare($query); + $sth->execute( $budget_id ); + my @results = (); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + return @results; +} + +=head2 GetBudgetsReport + + &GetBudgetsReport(); + +Get all budgets for reports without cancelled baskets. + +=cut + +# -------------------------------------------------------------------- +sub GetBudgetsReport { + my $dbh = C4::Context->dbh; + my $query = " + SELECT o.*, b.budget_name + FROM aqbudgets b + INNER JOIN aqorders o + ON b.budget_id = o.budget_id + WHERE (o.datecancellationprinted IS NULL OR o.datecancellationprinted='0000-00-00') + ORDER BY b.budget_name + "; + my $sth = $dbh->prepare($query); + $sth->execute; + my @results = (); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + return @results; +} + +=head2 GetActiveBudgets + + &GetActiveBudgets( $budget_period_active ); + +Get all active budgets or all inactive budgets, depending of the value +of the parameter. + +1 = active +0 = inactive + +=cut + +# -------------------------------------------------------------------- +sub GetActiveBudgets { + my ( $budget_period_active ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT DISTINCT b.* + FROM aqbudgetperiods bp + INNER JOIN aqbudgets b + ON bp.budget_period_id = b.budget_period_id + WHERE bp.budget_period_active=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( $budget_period_active ); + my @results = (); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + return @results; +} + +=head2 GetActiveBudgetsReport + + &GetActiveBudgetsReport(); + +Get all active budgets for reports without cancelled baskets. + +=cut + +# -------------------------------------------------------------------- +sub GetActiveBudgetsReport { + my $dbh = C4::Context->dbh; + my $query = " + SELECT o.*, b.budget_name + FROM aqbudgetperiods bp + INNER JOIN aqbudgets b + ON bp.budget_period_id = b.budget_period_id + INNER JOIN aqorders o + ON b.budget_id = o.budget_id + WHERE bp.budget_period_active=1 + AND (o.datecancellationprinted IS NULL OR o.datecancellationprinted='0000-00-00') + ORDER BY b.budget_name + "; + my $sth = $dbh->prepare($query); + $sth->execute; + my @results = (); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + return @results; +} + =head2 GetChildBudgetsSpent &GetChildBudgetsSpent($budget-id); diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt new file mode 100644 index 0000000..7ea97d1 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt @@ -0,0 +1,115 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Reports › Orders by budget +[% INCLUDE 'doc-head-close.inc' %] + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + + +
+ +
+
+
+[% IF ( current_budget_name ) %] +

Orders for budget '[% current_budget_name %]'

+[% ELSE %] +

Orders by budget

+[% END %] + +[% IF ( get_orders ) %] + +
+ [% IF ( total ) %] + Orders found: [% total %] + [% ELSE %] + No order found + [% END %] +
+ + [% IF ( ordersloop ) %] + + + + + + + + + + + + + + + + + [% FOREACH ordersloo IN ordersloop %] + [% UNLESS ( loop.odd ) %] + + [% ELSE %] + + [% END %] + + + + + + + + + + + + + + + + [% END %] + +
BudgetBasketBasket byTitleCurrencyVendor PriceRRPBudgeted costQuantityTotal RRPTotal costEntry dateDate receivedNotes
[% ordersloo.budget_name |html %] + [% ordersloo.basketno |html %] + [% ordersloo.authorisedbyname %] + [% ordersloo.title |html %] + [% ordersloo.currency %][% ordersloo.listprice %][% ordersloo.rrp %][% ordersloo.ecost %][% ordersloo.quantity %][% ordersloo.total_rrp %][% ordersloo.total_ecost %][% ordersloo.entrydate %][% ordersloo.datereceived %][% ordersloo.notes |html %]
TOTAL[% total_quantity %][% total_rrp %][% total_ecost %]
+ [% END %] + [% ELSE %] + +
+
    + Filters +
  1. +
+ +
+ Output +
  1. +
  2. [% CGIextChoice %] + [% CGIsepChoice %]
+
+ +
+
+
+ + [% END %] + +
+
+
+[% INCLUDE 'reports-menu.inc' %] +
+
+[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt index c7744fa..efdf539 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt @@ -61,11 +61,12 @@

Other

diff --git a/reports/orders_by_budget.pl b/reports/orders_by_budget.pl new file mode 100755 index 0000000..f266ade --- /dev/null +++ b/reports/orders_by_budget.pl @@ -0,0 +1,250 @@ +#!/usr/bin/perl + +# This file is part of Koha. +# +# Author : Frédérick Capovilla, 2011 - SYS-TECH +# Modified by : Élyse Morin, 2012 - Libéo +# +# 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., 59 Temple Place, +# Suite 330, Boston, MA 02111-1307 USA + + +=head1 orders_by_budget + +This script displays all orders associated to a selected budget. + +=cut + +use strict; +use warnings; + +use CGI; +use C4::Auth; +use C4::Output; +use C4::Budgets; +use C4::Biblio; +use C4::Reports; +use C4::Dates qw/format_date/; +use C4::SQLHelper qw<:all>; +use C4::Acquisition; #GetBasket() + + +my $query = new CGI; +my ( $template, $loggedinuser, $cookie ) = get_template_and_user( + { + template_name => "reports/orders_by_budget.tt", + query => $query, + type => "intranet", + authnotrequired => 0, + flagsrequired => { reports => '*' }, + debug => 1, + } +); + +my $params = $query->Vars; +my $get_orders = $params->{'get_orders'}; + +if ( $get_orders ) { + my $budgetfilter = $params->{'budgetfilter'} || undef; + my $total_quantity = 0; + my $total_rrp = 0; + my $total_ecost = 0; + my %budget_name; + + # Fetch the orders + my @orders; + unless($budgetfilter) { + # If no budget filter was selected, get the orders of all budgets + my @budgets = C4::Budgets::GetBudgetsReport(); + foreach my $budget (@budgets) { + push(@orders, $budget); + $budget_name{$budget->{'budget_id'}} = $budget->{'budget_name'}; + } + } + else { + if ($budgetfilter eq 'activebudgets') { + # If all active budgets's option was selected, get the orders of all active budgets + my @active_budgets = C4::Budgets::GetActiveBudgetsReport(); + foreach my $active_budget (@active_budgets) + { + push(@orders, $active_budget); + $budget_name{$active_budget->{'budget_id'}} = $active_budget->{'budget_name'}; + } + } + else { + # A budget filter was selected, only get the orders for the selected budget + my @filtered_budgets = C4::Budgets::GetBudgetReport($budgetfilter); + foreach my $budget (@filtered_budgets) + { + push(@orders, $budget); + $budget_name{$budget->{'budget_id'}} = $budget->{'budget_name'}; + } + if (@filtered_budgets[0]) { + $template->param( + current_budget_name => @filtered_budgets[0]->{'budget_name'}, + ); + } + } + } + + # Format the order's informations + foreach my $order (@orders) { + # Get the title of the ordered item + my (undef, $biblio) = C4::Biblio::GetBiblio($order->{'biblionumber'}); + my $basket = C4::Acquisition::GetBasket($order->{'basketno'}); + + $order->{'authorisedbyname'} = $basket->{'authorisedbyname'}; + + $order->{'title'} = $biblio->{'title'} || $order->{'biblionumber'}; + + $order->{'total_rrp'} = $order->{'quantity'} * $order->{'rrp'}; + $order->{'total_ecost'} = $order->{'quantity'} * $order->{'ecost'}; + + # Format the dates and currencies correctly + $order->{'datereceived'} = format_date($order->{'datereceived'}); + $order->{'entrydate'} = format_date($order->{'entrydate'}); + $order->{'listprice'} = sprintf( "%.2f", $order->{'listprice'} ); + $order->{'rrp'} = sprintf( "%.2f", $order->{'rrp'} ); + $order->{'ecost'} = sprintf( "%.2f", $order->{'ecost'}); + $order->{'total_rrp'} = sprintf( "%.2f", $order->{'total_rrp'}); + $order->{'total_ecost'} = sprintf( "%.2f", $order->{'total_ecost'}); + + $total_quantity += $order->{'quantity'}; + $total_rrp += $order->{'total_rrp'}; + $total_ecost += $order->{'total_ecost'}; + + # Get the budget's name + $order->{'budget_name'} = $budget_name{$order->{'budget_id'}}; + } + + # If we are outputting to screen, output to the template. + if($params->{"output"} eq 'screen') { + $template->param( + total => scalar @orders, + ordersloop => \@orders, + get_orders => $get_orders, + total_quantity => $total_quantity, + total_rrp => sprintf( "%.2f", $total_rrp ), + total_ecost => sprintf( "%.2f", $total_ecost ), + ); + } + # If we are outputting to a file, create it and exit. + else { + my $basename = $params->{"basename"}; + my $sep = $params->{"sep"}; + $sep = "\t" if ($sep == 'tabulation'); + + print $query->header( + -type => 'application/vnd.sun.xml.calc', + -encoding => 'utf-8', + -attachment => "$basename.csv", + -name => "$basename.csv" + ); + + binmode STDOUT, ":utf8"; + + # Surrounds a string with double-quotes and escape the double-quotes inside + sub _surround { + my $string = shift || ""; + $string =~ s/"/""/g; + return "\"$string\""; + } + + # Create the CSV file + print '"Budget"' . $sep; + print '"Basket"' . $sep; + print '"Basket by"' . $sep; + print '"biblionumber"' . $sep; + print '"Title"' . $sep; + print '"Currency"' . $sep; + print '"Vendor Price"' . $sep; + print '"RRP"' . $sep; + print '"Budgeted cost"' . $sep; + print '"Quantity"' . $sep; + print '"Total RRP"' . $sep; + print '"Total cost"' . $sep; + print '"Entry date"' . $sep; + print '"Date received"' . $sep; + print '"Notes"' . "\n"; + + foreach my $order (@orders) { + print _surround($order->{'budget_name'}) . $sep; + print _surround($order->{'basketno'}) . $sep; + print _surround($order->{'authorisedbyname'}) . $sep; + print _surround($order->{'biblionumber'}) . $sep; + print _surround($order->{'title'}) . $sep; + print _surround($order->{'currency'}) . $sep; + print _surround($order->{'listprice'}) . $sep; + print _surround($order->{'rrp'}) . $sep; + print _surround($order->{'ecost'}) . $sep; + print _surround($order->{'quantity'}) . $sep; + print _surround($order->{'total_rrp'}) . $sep; + print _surround($order->{'total_ecost'}) . $sep; + print _surround($order->{'entrydate'}) . $sep; + print _surround($order->{'datereceived'}) . $sep; + print _surround($order->{'notes'}) . "\n"; + } + + print '"TOTAL"'. ($sep x 8); + print _surround($total_quantity) . $sep; + print _surround($total_rrp) . $sep; + print _surround($total_ecost); + + exit(0); + } +} +else { + # Set file export choices + my $CGIextChoice = CGI::scrolling_list( + -name => 'MIME', + -id => 'MIME', + -values => ['CSV'], # FIXME translation + -size => 1, + -multiple => 0 + ); + + my $CGIsepChoice = GetDelimiterChoices; + + # getting all budgets + # active budgets + my @active_budgets = C4::Budgets::GetActiveBudgets(1); + # non active budgets + my @non_active_budgets = C4::Budgets::GetActiveBudgets(0); + my @budgetsloop; + foreach my $thisbudget ( sort {$a->{budget_name} cmp $b->{budget_name}} @active_budgets ) { + my $budget_period_desc = C4::Budgets::GetBudgetPeriodDescription($thisbudget->{budget_id}); + my %row = ( + value => $thisbudget->{budget_id}, + description => $thisbudget->{budget_name}, + period => "(" . $budget_period_desc->{budget_period_description} . ")", + ); + push @budgetsloop, \%row; + } + foreach my $thisbudget ( sort {$a->{budget_name} cmp $b->{budget_name}} @non_active_budgets ) { + my $budget_period_desc = C4::Budgets::GetBudgetPeriodDescription($thisbudget->{budget_id}); + my %row = ( + value => $thisbudget->{budget_id}, + description => "[i] ". $thisbudget->{budget_name}, + period => "(" . $budget_period_desc->{budget_period_description} . ")", + ); + push @budgetsloop, \%row; + } + + $template->param( budgetsloop => \@budgetsloop, + CGIextChoice => $CGIextChoice, + CGIsepChoice => $CGIsepChoice, + ); +} + +# writing the template +output_html_with_http_headers $query, $cookie, $template->output; diff --git a/t/db_dependent/Acquisition.t b/t/db_dependent/Acquisition.t index 2585802..356cedd 100755 --- a/t/db_dependent/Acquisition.t +++ b/t/db_dependent/Acquisition.t @@ -8,7 +8,7 @@ use POSIX qw(strftime); use C4::Bookseller qw( GetBookSellerFromId ); -use Test::More tests => 62; +use Test::More tests => 67; BEGIN { use_ok('C4::Acquisition'); @@ -40,10 +40,18 @@ my ($basket, $basketno); ok($basketno = NewBasket($booksellerid, 1), "NewBasket( $booksellerid , 1 ) returns $basketno"); ok($basket = GetBasket($basketno), "GetBasket($basketno) returns $basket"); +my $bpid=AddBudgetPeriod({ + budget_period_startdate => '2008-01-01' + , budget_period_enddate => '2008-12-31' + , budget_period_active => 1 + , budget_description => "MAPERI" +}); + my $budgetid = C4::Budgets::AddBudget( { budget_code => "budget_code_test_getordersbybib", budget_name => "budget_name_test_getordersbybib", + budget_period_id => $bpid, } ); my $budget = C4::Budgets::GetBudget( $budgetid ); @@ -223,4 +231,24 @@ is($order3->{'quantityreceived'}, 2, 'Order not split up'); is($order3->{'quantity'}, 2, '2 items on order'); is($order3->{'budget_id'}, $budgetid2, 'Budget has changed'); + +# Budget reports + +my @report = GetBudgetReport($budget->{budget_id}); +ok(@report == 3, "GetBudgetReport OK"); + +my $all_count = scalar GetBudgetsReport(); +ok($all_count >= 3, "GetBudgetsReport OK"); + +my $active_count = scalar GetActiveBudgetsReport(); +ok($active_count >= 3, "GetActiveBudgetsReport OK"); + +# Deactivate budget period +my $budgetperiod=GetBudgetPeriod($bpid); +$$budgetperiod{budget_period_active}=0; +ModBudgetPeriod($budgetperiod); + +ok($all_count == scalar GetBudgetsReport(), "GetBudgetReport returns inactive budget period acquisitions."); +ok($active_count >= scalar GetActiveBudgetsReport(), "GetBudgetReport doesn't return inactive budget period acquisitions."); + $dbh->rollback; diff --git a/t/db_dependent/Budgets.t b/t/db_dependent/Budgets.t index bfb910b..dd0bcf8 100755 --- a/t/db_dependent/Budgets.t +++ b/t/db_dependent/Budgets.t @@ -1,6 +1,6 @@ use strict; use warnings; -use Test::More tests=>20; +use Test::More tests=>23; BEGIN {use_ok('C4::Budgets') } use C4::Dates; @@ -46,12 +46,16 @@ if (C4::Context->preference('dateformat') eq "metric"){ ok($bpid=AddBudgetPeriod( { budget_period_startdate =>'01-01-2008' , budget_period_enddate =>'31-12-2008' + , budget_period_description =>"MAPERI" + , budget_period_active =>1 , budget_description =>"MAPERI"}), "AddBudgetPeriod returned $bpid"); } elsif (C4::Context->preference('dateformat') eq "us"){ ok($bpid=AddBudgetPeriod( { budget_period_startdate =>'01-01-2008' , budget_period_enddate =>'12-31-2008' + , budget_period_description =>"MAPERI" + , budget_period_active =>1 , budget_description =>"MAPERI"}), "AddBudgetPeriod returned $bpid"); } @@ -60,6 +64,8 @@ ok($bpid=AddBudgetPeriod( {budget_period_startdate=>'2008-01-01' ,budget_period_enddate =>'2008-12-31' ,budget_description =>"MAPERI" + ,budget_period_active =>1 + ,budget_period_description =>"MAPERI" }), "AddBudgetPeriod returned $bpid"); @@ -125,6 +131,20 @@ ok($second_budget_id=AddBudget( my $budgets = GetBudgets({ budget_period_id => $bpid}); ok($budgets->[0]->{budget_name} lt $budgets->[1]->{budget_name}, 'default sort order for GetBudgets is by name'); +ok(GetBudgetPeriodDescription($budget_id)->{budget_period_description} eq "MAPERI", + "GetBudgetPeriodDescription OK"); + +ok(GetActiveBudgets(1) > 0, + "GetActiveBudgets can return active budgets"); + +# Deactivate budget period +$budgetperiod=GetBudgetPeriod($bpid); +$$budgetperiod{budget_period_active}=0; +ModBudgetPeriod($budgetperiod); + +ok(GetActiveBudgets(0) > 0, + "GetActiveBudgets can return inactive budgets"); + ok($del_status=DelBudget($budget_id), "DelBudget returned $del_status"); -- 1.7.2.5