From df72bdb26f81f0dc5d2acf2e7fc0585afef81e68 Mon Sep 17 00:00:00 2001 From: mbeaulieu <mbeaulieu@inlibro.com> Date: Tue, 5 Aug 2014 12:05:01 -0400 Subject: [PATCH] Bug 11371 - Follow-up to Comment 6 'Budgets' is now displayed as 'funds' in the Koha UI. Fixed the legend display issue. Improved POD. Use of the orderstatus column to filter canceled orders. GetActiveBudgetsReports has been merged with GetBudgetsReports( [$activity] ). GetActiveBudgets renamed to GetBudgetsByActivity Create templates for .CSV file generation. Removed CGI ui components. new file: koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt new file: koha-tmpl/intranet-tmpl/prog/en/modules/reports/csv/orders_by_budget.tt --- C4/Budgets.pm | 108 ++++++++++---------- .../csv_headers/reports/orders_by_budget.tt | 1 + .../en/modules/reports/csv/orders_by_budget.tt | 12 +++ .../prog/en/modules/reports/orders_by_budget.tt | 42 +++++--- .../prog/en/modules/reports/reports-home.tt | 2 +- reports/orders_by_budget.pl | 97 ++++++++---------- t/db_dependent/Acquisition.t | 8 +- t/db_dependent/Budgets.t | 4 +- 8 files changed, 140 insertions(+), 134 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 diff --git a/C4/Budgets.pm b/C4/Budgets.pm index 87ce5ed..09a330c 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -35,10 +35,9 @@ BEGIN { &GetBudgetByOrderNumber &GetBudgetByCode &GetBudgets - &GetActiveBudgets - &GetBudgetReport + &BudgetsByActivity &GetBudgetsReport - &GetActiveBudgetsReport + &GetBudgetReport &GetBudgetHierarchy &AddBudget &ModBudget @@ -443,6 +442,17 @@ sub GetBudgetPeriod { } # ------------------------------------------------------------------- +=head2 GetBudgetPeriodDescription + + $description = GetBudgetPeriodDescription($budget_id); + + Return value: hashref + + Returns the budget period description for a given budget id. + + Description is fetched from aqbudgetperiods, id is compared aqbudgets. (INNER JOIN on budget_period_id) + +=cut sub GetBudgetPeriodDescription { my ($budget_id) = @_; my $dbh = C4::Context->dbh; @@ -630,6 +640,7 @@ sub DelBudget { } +# ------------------------------------------------------------------- =head2 GetBudget &GetBudget($budget_id); @@ -638,7 +649,6 @@ get a specific budget =cut -# ------------------------------------------------------------------- sub GetBudget { my ( $budget_id ) = @_; my $dbh = C4::Context->dbh; @@ -653,6 +663,8 @@ sub GetBudget { return $result; } +# ------------------------------------------------------------------- + =head2 GetBudgetByOrderNumber &GetBudgetByOrderNumber($ordernumber); @@ -661,7 +673,6 @@ get a specific budget by order number =cut -# ------------------------------------------------------------------- sub GetBudgetByOrderNumber { my ( $ordernumber ) = @_; my $dbh = C4::Context->dbh; @@ -679,9 +690,11 @@ sub GetBudgetByOrderNumber { =head2 GetBudgetReport - &GetBudgetReport(); + &GetBudgetReport( [$budget_id] ); + +Get all orders for a specific budget, without cancelled orders. -Get one specific budget for reports without cancelled baskets. +Returns an array of hashrefs. =cut @@ -689,45 +702,18 @@ Get one specific budget for reports without cancelled baskets. sub GetBudgetReport { my ( $budget_id ) = @_; my $dbh = C4::Context->dbh; - my $query = " + 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 - "; + 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 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 ); @@ -735,11 +721,11 @@ sub GetBudgetsReport { return @results; } -=head2 GetActiveBudgets +=head2 GetBudgetsByActivity - &GetActiveBudgets( $budget_period_active ); + &GetBudgetsByActivity( $budget_period_active ); -Get all active budgets or all inactive budgets, depending of the value +Get all active or inactive budgets, depending of the value of the parameter. 1 = active @@ -748,7 +734,7 @@ of the parameter. =cut # -------------------------------------------------------------------- -sub GetActiveBudgets { +sub GetBudgetsByActivity { my ( $budget_period_active ) = @_; my $dbh = C4::Context->dbh; my $query = " @@ -766,31 +752,45 @@ sub GetActiveBudgets { } return @results; } +# -------------------------------------------------------------------- +=head2 GetBudgetsReport + + &GetBudgetsReport( [$activity] ); -=head2 GetActiveBudgetsReport +Get all but cancelled orders for all funds. - &GetActiveBudgetsReport(); +If the optionnal activity parameter is passed, returns orders for active/inactive budgets only. -Get all active budgets for reports without cancelled baskets. +active = 1 +inactive = 0 + +Returns an array of hashrefs. =cut -# -------------------------------------------------------------------- -sub GetActiveBudgetsReport { +sub GetBudgetsReport { + my ($activity) = @_; my $dbh = C4::Context->dbh; - my $query = " + 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 - "; + 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); - $sth->execute; + if($activity ne ''){ + $sth->execute($activity); + } + else{ + $sth->execute; + } my @results = (); while ( my $data = $sth->fetchrow_hashref ) { push( @results, $data ); 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 0000000..c80440f --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt @@ -0,0 +1 @@ +Fund[% sep %]Basket[% sep %]Basket by[% sep %]biblionumber[% 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 %]Notes 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 0000000..e025c5c --- /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 index eb365f1..c2e1089 100644 --- 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 @@ -1,12 +1,12 @@ [% INCLUDE 'doc-head-open.inc' %] -<title>Koha › Reports › Orders by budget</title> +<title>Koha › Reports › Orders by fund</title> [% INCLUDE 'doc-head-close.inc' %] </head> <body> [% INCLUDE 'header.inc' %] [% INCLUDE 'cat-search.inc' %] -<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> › <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a>[% IF ( get_orders ) %] › <a href="/cgi-bin/koha/reports/orders_by_budget.pl">Orders by budget</a> › Results[% ELSE %] › Orders by budget[% END %]</div> +<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> › <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a>[% IF ( get_orders ) %] › <a href="/cgi-bin/koha/reports/orders_by_budget.pl">Orders by fund</a> › Results[% ELSE %] › Orders by fund[% END %]</div> <div id="doc3" class="yui-t2"> @@ -14,9 +14,9 @@ <div id="yui-main"> <div class="yui-b"> [% IF ( current_budget_name ) %] -<h1>Orders for budget '[% current_budget_name %]'</h1> +<h1>Orders for fund '[% current_budget_name %]'</h1> [% ELSE %] -<h1>Orders by budget</h1> +<h1>Orders by fund</h1> [% END %] [% IF ( get_orders ) %] @@ -31,19 +31,19 @@ [% IF ( ordersloop ) %]<table> <tr> - <th>Budget</th> + <th>Fund</th> <th>Basket</th> - <th>Basket by</th> + <th>Basket By</th> <th>Title</th> <th>Currency</th> <th>Vendor Price</th> <th>RRP</th> - <th>Budgeted cost</th> + <th>Budgeted Cost</th> <th>Quantity</th> <th>Total RRP</th> - <th>Total cost</th> - <th>Entry date</th> - <th>Date received</th> + <th>Total Cost</th> + <th>Entry Date</th> + <th>Date Received</th> <th>Notes</th> </tr> [% FOREACH ordersloo IN ordersloop %] @@ -78,11 +78,11 @@ [% ELSE %] <form name="f" action="/cgi-bin/koha/reports/orders_by_budget.pl" method="post"> -<fieldset class="rows"><ol> +<fieldset class="rows"> <legend>Filters</legend> - <li><label for="budgetfilter">Budget: </label><select name="budgetfilter" id="budgetfilter"> - <option value="">All budgets</option> - <option value="activebudgets">All active budgets</option> + <ol><li><label for="budgetfilter">Fund (Budget): </label><select name="budgetfilter" id="budgetfilter"> + <option value="">All funds</option> + <option value="activebudgets">All active funds</option> [% FOREACH budgetsloo IN budgetsloop %] [% IF ( budgetsloo.selected ) %]<option value="[% budgetsloo.value %]" selected="selected">[% budgetsloo.description %] [% budgetsloo.period %]</option> [% ELSE %] @@ -96,8 +96,18 @@ <legend>Output</legend> <ol><li><label for="outputscreen">To screen into the browser: </label><input type="radio" checked="checked" name="output" id="outputscreen" value="screen" /> </li> <li><label for="outputfile">To a file:</label> <input type="radio" name="output" value="file" id="outputfile" /> <label class="inline" for="basename">Named: </label><input type="text" name="basename" id="basename" value="Export" /> <label class="inline" for="MIME">Into an application - </label>[% CGIextChoice %] - [% CGIsepChoice %]</li></ol> + </label> + <select id='MIME' name='MIME' size='1'> + [% FOREACH outputFormatloo IN outputFormatloop %] + <option value="[% outputFormatloo %]">[% outputFormatloo %]</option> + [% END %] + </select> + <select id='sep' name='sep' size='1'> + [% FOREACH delimiterloo IN delimiterloop %] + <option value="[% delimiterloo %]">[% delimiterloo %]</option> + [% END %] + </select> + </li></ol> </fieldset> <fieldset class="action"> <input type="submit" value="Submit" /> 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 900092f..b7680bc 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,7 +61,7 @@ <h2>Other</h2> <ul> <li><a href="/cgi-bin/koha/reports/itemslost.pl">Items lost</a></li> - <li><a href="/cgi-bin/koha/reports/orders_by_budget.pl">Orders by budget</a></li> + <li><a href="/cgi-bin/koha/reports/orders_by_budget.pl">Orders by fund</a></li> <li><a href="/cgi-bin/koha/reports/manager.pl?report_name=itemtypes">Catalog by itemtype</a></li> <li><a href="/cgi-bin/koha/reports/issues_avg_stats.pl">Average loan time</a></li> <li><a href="http://schema.koha-community.org/" target="blank">Koha database schema</a></li> diff --git a/reports/orders_by_budget.pl b/reports/orders_by_budget.pl index 8497e74..e31bb2d 100755 --- a/reports/orders_by_budget.pl +++ b/reports/orders_by_budget.pl @@ -74,7 +74,7 @@ if ( $get_orders ) { 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(); + my @active_budgets = C4::Budgets::GetBudgetsReport(1); foreach my $active_budget (@active_budgets) { push(@orders, $active_budget); @@ -142,16 +142,16 @@ if ( $get_orders ) { else { my $basename = $params->{"basename"}; my $sep = $params->{"sep"}; - $sep = "\t" if ($sep == 'tabulation'); + $sep = "\t" if ($sep eq 'tabulation'); print $query->header( - -type => 'application/vnd.sun.xml.calc', - -encoding => 'utf-8', - -attachment => "$basename.csv", - -name => "$basename.csv" + -type => 'application/vnd.sun.xml.calc', + -encoding => 'utf-8', + -attachment => "$basename.csv", + -name => "$basename.csv" ); - binmode STDOUT, ":encoding(UTF-8)"; + #binmode STDOUT, ":encoding(UTF-8)"; # Surrounds a string with double-quotes and escape the double-quotes inside sub _surround { @@ -159,67 +159,50 @@ if ( $get_orders ) { $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"; - + my @rows; 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"; + my @row; + push(@row, _surround($order->{'budget_name'})); + push(@row, _surround($order->{'basketno'})); + 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->{'notes'})); + push(@rows, \@row); } - print '"TOTAL"'. ($sep x 8); - print _surround($total_quantity) . $sep; - print _surround($total_rrp) . $sep; - print _surround($total_ecost); + 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 $CGIextChoice = CGI::scrolling_list( - -name => 'MIME', - -id => 'MIME', - -values => ['CSV'], # FIXME translation - -size => 1, - -multiple => 0 - ); - - my $CGIsepChoice = GetDelimiterChoices; + my @outputFormats = ('CSV'); + my @CSVdelimiters = qw(; tabulation , \\ / #); # getting all budgets # active budgets - my @active_budgets = C4::Budgets::GetActiveBudgets(1); + my @active_budgets = C4::Budgets::GetBudgetsByActivity(1); # non active budgets - my @non_active_budgets = C4::Budgets::GetActiveBudgets(0); + my @non_active_budgets = C4::Budgets::GetBudgetsByActivity(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}); @@ -241,8 +224,8 @@ else { } $template->param( budgetsloop => \@budgetsloop, - CGIextChoice => $CGIextChoice, - CGIsepChoice => $CGIsepChoice, + outputFormatloop => \@outputFormats, + delimiterloop => \@CSVdelimiters ); } diff --git a/t/db_dependent/Acquisition.t b/t/db_dependent/Acquisition.t index b9999e2..10cbada 100755 --- a/t/db_dependent/Acquisition.t +++ b/t/db_dependent/Acquisition.t @@ -940,10 +940,10 @@ my @report = GetBudgetReport($budget->{budget_id}); ok(@report == 3, "GetBudgetReport OK"); my $all_count = scalar GetBudgetsReport(); -ok($all_count >= 3, "GetBudgetsReport OK"); +ok($all_count >= 3, "GetBudgetReport OK"); -my $active_count = scalar GetActiveBudgetsReport(); -ok($active_count >= 3, "GetActiveBudgetsReport OK"); +my $active_count = scalar GetBudgetsReport(1); +ok($active_count >= 3, "GetBudgetsReport(1) OK"); # Deactivate budget period my $budgetperiod=GetBudgetPeriod($bpid); @@ -951,6 +951,6 @@ $$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."); +ok($active_count >= scalar GetBudgetsReport(1), "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 28414bf..db817b5 100755 --- a/t/db_dependent/Budgets.t +++ b/t/db_dependent/Budgets.t @@ -492,7 +492,7 @@ ok($budgets->[0]->{budget_name} lt $budgets->[1]->{budget_name}, 'default sort o ok(GetBudgetPeriodDescription($budget_id)->{budget_period_description} eq "MAPERI", "GetBudgetPeriodDescription OK"); -ok(GetActiveBudgets(1) > 0, +ok(GetBudgetsByActivity(1) > 0, "GetActiveBudgets can return active budgets"); # Deactivate budget period @@ -500,7 +500,7 @@ $budgetperiod=GetBudgetPeriod($bpid); $$budgetperiod{budget_period_active}=0; ModBudgetPeriod($budgetperiod); -ok(GetActiveBudgets(0) > 0, +ok(GetBudgetsByActivity(0) > 0, "GetActiveBudgets can return inactive budgets"); my $del_status; ok($del_status=DelBudget($budget_id), -- 1.7.9.5