From 2e5fdb3f1a0a4c0e28782d6df512d7b7f1b14f66 Mon Sep 17 00:00:00 2001 From: mbeaulieu Date: Mon, 4 Aug 2014 15:21:15 -0400 Subject: [PATCH] Bug 11731 - Fixed QA warnings and merge conflicts http://bugs.koha-community.org/show_bug.cgi?id=11371 --- C4/Budgets.pm | 146 ++++++++++++ .../prog/en/modules/reports/orders_by_budget.tt | 115 +++++++++ .../prog/en/modules/reports/reports-home.tt | 83 +++---- reports/orders_by_budget.pl | 250 ++++++++++++++++++++ t/db_dependent/Acquisition.t | 28 +++ t/db_dependent/Budgets.t | 66 +++++- 6 files changed, 646 insertions(+), 42 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 e0e8f72..87ce5ed 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -35,6 +35,10 @@ BEGIN { &GetBudgetByOrderNumber &GetBudgetByCode &GetBudgets + &GetActiveBudgets + &GetBudgetReport + &GetBudgetsReport + &GetActiveBudgetsReport &GetBudgetHierarchy &AddBudget &ModBudget @@ -55,6 +59,7 @@ BEGIN { &GetBudgetPeriods &ModBudgetPeriod &AddBudgetPeriod + &GetBudgetPeriodDescription &DelBudgetPeriod &ModBudgetPlan @@ -438,6 +443,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; @@ -652,6 +677,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 GetBudgetByCode my $budget = &GetBudgetByCode($budget_code); 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..eb365f1 --- /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..900092f 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 @@ -12,30 +12,30 @@
-
-

Reports

+
+

Reports

Guided reports

- - -
Reports dictionary
- + -

Statistics wizards

- +
Reports dictionary
+ + +

Statistics wizards

+ [% IF UseKohaPlugins %]

Report Plugins

@@ -47,26 +47,27 @@
+ + +

Inactive

+ + +

Other

+
diff --git a/reports/orders_by_budget.pl b/reports/orders_by_budget.pl new file mode 100755 index 0000000..8497e74 --- /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 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 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 $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, ":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\""; + } + + # 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 837024a..b9999e2 100755 --- a/t/db_dependent/Acquisition.t +++ b/t/db_dependent/Acquisition.t @@ -130,10 +130,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_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); @@ -925,4 +933,24 @@ is( $nonexistent_order, undef, 'GetOrder returns undef if no ordernumber is give $nonexistent_order = GetOrder( 424242424242 ); is( $nonexistent_order, undef, 'GetOrder returns undef if a nonexistent ordernumber is given' ); + +# 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 a565c8d..28414bf 100755 --- a/t/db_dependent/Budgets.t +++ b/t/db_dependent/Budgets.t @@ -1,5 +1,7 @@ use Modern::Perl; use Test::More tests => 107; +use strict; +use warnings; BEGIN { use_ok('C4::Budgets') @@ -406,6 +408,33 @@ $budget_period_id_cloned = C4::Budgets::CloneBudgetPeriod( mark_original_budget_as_inactive => 1, } ); +# Add A budget Period +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"); +} +else{ +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"); $budget_hierarchy = GetBudgetHierarchy($budget_period_id); $budget_hierarchy_cloned = GetBudgetHierarchy($budget_period_id_cloned); @@ -441,6 +470,41 @@ for my $budget (@$budget_hierarchy_cloned) { is( $number_of_budgets_not_reset, 0, 'CloneBudgetPeriod has reset all budgets (funds)' ); +my $budget_name = GetBudgetName( $budget_id ); +is($budget_name, $budget->{budget_name}, "Test the GetBudgetName routine"); + +my $second_budget_id; +ok($second_budget_id=AddBudget( + { budget_code => "ZZZZ", + budget_amount => "500.00", + budget_name => "Art", + budget_notes => "This is a note", + budget_description=> "Art", + budget_active => 1, + budget_period_id => $bpid, + } + ), + "AddBudget returned $second_budget_id"); + +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"); +my $del_status; +ok($del_status=DelBudget($budget_id), + "DelBudget returned $del_status"); # MoveOrders my $number_orders_moved = C4::Budgets::MoveOrders(); @@ -536,4 +600,4 @@ sub _get_budgetname_by_id { # C4::Context->userenv sub Mock_userenv { return $userenv; -} +}} -- 1.7.9.5