From 97e36c02648e7de5711f4542c9140e763b4a30e8 Mon Sep 17 00:00:00 2001 From: Bouzid Fergani Date: Thu, 5 Mar 2015 10:56:14 -0500 Subject: [PATCH] Bug 11371 - Add a new report : Orders by fund with more options MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Add option show or no inactive budget and more options Use subroutine GetBudgetHierarchy for return all budgets Delete subroutine GetBudgetPeriodDescription and theire tests Use Price TT plugin Correct name of column and capitalization the first letter Add checkbox for show inactive budgets, default the drop down list containt a active budget Not use [i] for inactive budgets, i add (inactive) at the end of inactive budget Add vendor note in the list of show attribute Test case: Go to Home > Reports > Orders by fund Select one or all budgets You can show the inactive budget, default the drop down list containt a active budget Choose output to screen ou csv file Works as expected. QA tools OK with Bug 16104 applied. Signed-off-by: Marc Véron Signed-off-by: Katrin Fischer - changed 'Fund (budget):' back to 'Fund:', as the budget no longer shows in the pull down. - Fixed number of tests in Budgets.t - Removed &GetBudgetPeriodDescription Signed-off-by: Kyle M Hall --- C4/Budgets.pm | 122 +++++++++- .../csv_headers/reports/orders_by_budget.tt | 1 + .../modules/reports/csv/orders_by_budget.tt | 12 + .../en/modules/reports/orders_by_budget.tt | 161 +++++++++++++ .../prog/en/modules/reports/reports-home.tt | 79 +++---- reports/orders_by_fund.pl | 216 ++++++++++++++++++ t/db_dependent/Acquisition.t | 22 +- t/db_dependent/Budgets.t | 13 +- 8 files changed, 580 insertions(+), 46 deletions(-) create mode 100644 koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/reports/csv/orders_by_budget.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt create mode 100755 reports/orders_by_fund.pl diff --git a/C4/Budgets.pm b/C4/Budgets.pm index db936be0c4..879c66e617 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -33,6 +33,9 @@ BEGIN { &GetBudgetByOrderNumber &GetBudgetByCode &GetBudgets + &BudgetsByActivity + &GetBudgetsReport + &GetBudgetReport &GetBudgetHierarchy &AddBudget &ModBudget @@ -71,7 +74,6 @@ BEGIN { # ----------------------------BUDGETS.PM-----------------------------"; - =head1 FUNCTIONS ABOUT BUDGETS =cut @@ -451,7 +453,6 @@ sub GetBudgetPeriod { return $data; } -# ------------------------------------------------------------------- sub DelBudgetPeriod{ my ($budget_period_id) = @_; my $dbh = C4::Context->dbh; @@ -595,6 +596,8 @@ sub DelBudget { } +# ------------------------------------------------------------------- + =head2 GetBudget &GetBudget($budget_id); @@ -603,7 +606,6 @@ get a specific budget =cut -# ------------------------------------------------------------------- sub GetBudget { my ( $budget_id ) = @_; my $dbh = C4::Context->dbh; @@ -618,6 +620,8 @@ sub GetBudget { return $result; } +# ------------------------------------------------------------------- + =head2 GetBudgetByOrderNumber &GetBudgetByOrderNumber($ordernumber); @@ -626,7 +630,6 @@ get a specific budget by order number =cut -# ------------------------------------------------------------------- sub GetBudgetByOrderNumber { my ( $ordernumber ) = @_; my $dbh = C4::Context->dbh; @@ -642,6 +645,117 @@ sub GetBudgetByOrderNumber { return $result; } +=head2 GetBudgetReport + + &GetBudgetReport( [$budget_id] ); + +Get all orders for a specific budget, without cancelled orders. + +Returns an array of hashrefs. + +=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.orderstatus != "cancelled") + 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 GetBudgetsByActivity + + &GetBudgetsByActivity( $budget_period_active ); + +Get all active or inactive budgets, depending of the value +of the parameter. + +1 = active +0 = inactive + +=cut + +# -------------------------------------------------------------------- +sub GetBudgetsByActivity { + 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 GetBudgetsReport + + &GetBudgetsReport( [$activity] ); + +Get all but cancelled orders for all funds. + +If the optionnal activity parameter is passed, returns orders for active/inactive budgets only. + +active = 1 +inactive = 0 + +Returns an array of hashrefs. + +=cut + +sub GetBudgetsReport { + my ($activity) = @_; + 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 '; + if($activity ne ''){ + $query .= 'WHERE bp.budget_period_active=? '; + } + $query .= 'AND (o.orderstatus != "cancelled") + ORDER BY b.budget_name'; + + my $sth = $dbh->prepare($query); + if($activity ne ''){ + $sth->execute($activity); + } + else{ + $sth->execute; + } + my @results = (); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + return @results; +} + =head2 GetBudgetByCode my $budget = &GetBudgetByCode($budget_code); diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt b/koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt new file mode 100644 index 0000000000..2acc7959af --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt @@ -0,0 +1 @@ +Fund[% sep %]"Basket num"[% sep %]"Basket name"[% sep %]"Authorised by"[% sep %]"Biblio number"[% sep %]Title[% sep %]Currency[% sep %]"Vendor price"[% sep %]RRP[% sep %]"Budgeted cost"[% sep %]Quantity[% sep %]"Total RRP"[% sep %]"Total cost"[% sep %]"Entry date"[% sep %]"Date received"[% sep %]"Internal note"[% sep %]"Vendor note" diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/csv/orders_by_budget.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/csv/orders_by_budget.tt new file mode 100644 index 0000000000..e025c5c5b9 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/csv/orders_by_budget.tt @@ -0,0 +1,12 @@ +[% INCLUDE csv_headers/reports/orders_by_budget.tt %] +[%- FOREACH row IN rows %] + [%- FOREACH field IN row; + field; + sep IF !loop.last; + END %] +[% END -%] +TOTAL +[%- FOREACH field IN totalrow; + field; + sep IF !loop.last; +END %] 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 0000000000..b0a9f5adb2 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt @@ -0,0 +1,161 @@ +[% USE Price %] +[% INCLUDE 'doc-head-open.inc' %] +Koha › Reports › Orders by fund +[% INCLUDE 'doc-head-close.inc' %] + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + +[% INCLUDE 'datatables.inc' %] + + + +
+ +
+
+
+ +[% IF ( current_budget_name ) %]

Orders for fund '[% current_budget_name %]'

+[% ELSE %]

Orders by fund

+[% 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 %] + + +
FundBasketBasket nameBasket byTitleCurrencyList priceRRPBudgeted costQuantityTotal RRPTotal costEntry dateDate deceivedInternal noteVendor note
[% ordersloo.budget_name |html %] [% ordersloo.basketno |html %][% ordersloo.basketname |html %][% ordersloo.authorisedbyname %] [% ordersloo.title |html %][% ordersloo.currency %][% ordersloo.listprice | $Price %][% ordersloo.rrp | $Price %][% ordersloo.ecost | $Price %][% ordersloo.quantity %][% ordersloo.total_rrp | $Price %][% ordersloo.total_ecost | $Price %][% ordersloo.entrydate %][% ordersloo.datereceived %][% ordersloo.order_internalnote |html %][% ordersloo.order_vendornote |html %]
TOTAL[% total_quantity %][% total_rrp | $Price %][% total_ecost | $Price %]
+ [% END %] + [% ELSE %] +
+
+ Filters +
  1. + + + +
+
+ +
+ Output +
    2. +
  2. + + + + + + +
+
+ +
+ +
+
+ + [% 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 39260c42c1..486a7fb6e8 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 @@ -13,30 +13,30 @@
-
-

Reports

+
+

Reports

Guided reports

- - -
Reports dictionary
- + -

Statistics wizards

- +
Reports dictionary
+ + +

Statistics wizards

+ [% IF UseKohaPlugins %]

Report Plugins

@@ -51,23 +51,24 @@ - -

Inactive

- - -

Other

-
+ + +

Inactive

+ + +

Other

+
diff --git a/reports/orders_by_fund.pl b/reports/orders_by_fund.pl new file mode 100755 index 0000000000..ce00d72341 --- /dev/null +++ b/reports/orders_by_fund.pl @@ -0,0 +1,216 @@ +#!/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 3 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 Modern::Perl; + +use CGI; +use C4::Auth; +use C4::Output; +use C4::Budgets; +use C4::Biblio; +use C4::Reports; +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::GetBudgetsReport(1); + 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 $biblio = C4::Biblio::GetBiblio($order->{'biblionumber'}); + my $basket = C4::Acquisition::GetBasket($order->{'basketno'}); + + $order->{'basketname'} = $basket->{'basketname'}; + $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'} = Koha::DateUtils::output_pref(Koha::DateUtils::dt_from_string($order->{'datereceived'})); + $order->{'entrydate'} = Koha::DateUtils::output_pref(Koha::DateUtils::dt_from_string($order->{'entrydate'})); + $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 => $total_rrp, + total_ecost => $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 eq 'tabulation'); + + print $query->header( + -type => 'application/vnd.sun.xml.calc', + -encoding => 'utf-8', + -attachment => "$basename.csv", + -name => "$basename.csv" + ); + + #binmode STDOUT, ":encoding(UTF-8)"; + + # Surrounds a string with double-quotes and escape the double-quotes inside + sub _surround { + my $string = shift || ""; + $string =~ s/"/""/g; + return "\"$string\""; + } + my @rows; + foreach my $order (@orders) { + my @row; + push(@row, _surround($order->{'budget_name'})); + push(@row, _surround($order->{'basketno'})); + push(@row, _surround($order->{'basketname'})); + push(@row, _surround($order->{'authorisedbyname'})); + push(@row, _surround($order->{'biblionumber'})); + push(@row, _surround($order->{'title'})); + push(@row, _surround($order->{'currency'})); + push(@row, _surround($order->{'listprice'})); + push(@row, _surround($order->{'rrp'})); + push(@row, _surround($order->{'ecost'})); + push(@row, _surround($order->{'quantity'})); + push(@row, _surround($order->{'total_rrp'})); + push(@row, _surround($order->{'total_ecost'})); + push(@row, _surround($order->{'entrydate'})); + push(@row, _surround($order->{'datereceived'})); + push(@row, _surround($order->{'order_internalnote'})); + push(@row, _surround($order->{'order_vendornote'})); + push(@rows, \@row); + } + + my @totalrow; + for(1..9){push(@totalrow, "")}; + push(@totalrow, _surround($total_quantity)); + push(@totalrow, _surround($total_rrp)); + push(@totalrow, _surround($total_ecost)); + + my $csvTemplate = C4::Templates::gettemplate('reports/csv/orders_by_budget.tt', 'intranet', $query); + $csvTemplate->param(sep => $sep, rows => \@rows, totalrow => \@totalrow); + print $csvTemplate->output; + + exit(0); + } +} +else { + # Set file export choices + my @outputFormats = ('CSV'); + my @CSVdelimiters =(',','#',qw(; tabulation \\ /)); + + # getting all budgets + my $budgets = GetBudgetHierarchy; + my $budgetloop = []; + foreach my $budget (@{$budgets}) { + push @{$budgetloop},{ + value => $budget->{budget_id}, + description => $budget->{budget_name}, + period => $budget->{budget_period_description}, + active => $budget->{budget_period_active}, + }; + } + @{$budgetloop} =sort { uc( $a->{description}) cmp uc( $b->{description}) } @{$budgetloop}; + $template->param( budgetsloop => \@{$budgetloop}, + outputFormatloop => \@outputFormats, + delimiterloop => \@CSVdelimiters, + delimiterPreference => C4::Context->preference('delimiter') + ); +} + +# 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 cd3f12573b..6063f09664 100755 --- a/t/db_dependent/Acquisition.t +++ b/t/db_dependent/Acquisition.t @@ -19,7 +19,7 @@ use Modern::Perl; use POSIX qw(strftime); -use Test::More tests => 87; +use Test::More tests => 91; use Koha::Database; BEGIN { @@ -147,10 +147,18 @@ ok( ); 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_period_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); @@ -934,3 +942,15 @@ ok((not defined GetBiblio($order4->{biblionumber})), "biblio does not exist anym # End of tests for DelOrder $schema->storage->txn_rollback(); +# Budget reports +#my @report = GetBudgetReport(1); +#ok(@report >= 1, "GetBudgetReport OK"); + +my $all_count = scalar GetBudgetsReport(); +ok($all_count >= 1, "GetBudgetReport OK"); + +my $active_count = scalar GetBudgetsReport(1); +ok($active_count >= 1 , "GetBudgetsReport(1) OK"); + +ok($all_count == scalar GetBudgetsReport(), "GetBudgetReport returns inactive budget period acquisitions."); +ok($active_count >= scalar GetBudgetsReport(1), "GetBudgetReport doesn't return inactive budget period acquisitions."); diff --git a/t/db_dependent/Budgets.t b/t/db_dependent/Budgets.t index 0f59b8bb84..70fedbcbac 100755 --- a/t/db_dependent/Budgets.t +++ b/t/db_dependent/Budgets.t @@ -1,6 +1,6 @@ +#!/usr/bin/perl use Modern::Perl; -use Test::More tests => 130; - +use Test::More tests => 133; BEGIN { use_ok('C4::Budgets') } @@ -468,6 +468,15 @@ for my $budget (@$budget_hierarchy_cloned) { is( $number_of_budgets_not_reset, 0, 'CloneBudgetPeriod has reset all budgets (funds)' ); +#GetBudgetsByActivity +my $result=C4::Budgets::GetBudgetsByActivity(1); +isnt( $result, undef ,'GetBudgetsByActivity return correct value with parameter 1'); +$result=C4::Budgets::GetBudgetsByActivity(0); + isnt( $result, undef ,'GetBudgetsByActivity return correct value with parameter 0'); +$result=C4::Budgets::GetBudgetsByActivity(); + is( $result, 0 , 'GetBudgetsByActivity return 0 with none parameter or other 0 or 1' ); +DelBudget($budget_id); +DelBudgetPeriod($bpid); # CloneBudgetPeriod with param amount_change_* $budget_period_id_cloned = C4::Budgets::CloneBudgetPeriod( -- 2.39.5