From df72bdb26f81f0dc5d2acf2e7fc0585afef81e68 Mon Sep 17 00:00:00 2001 From: mbeaulieu 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' %] -Koha › Reports › Orders by budget +Koha › Reports › Orders by fund [% INCLUDE 'doc-head-close.inc' %] [% INCLUDE 'header.inc' %] [% INCLUDE 'cat-search.inc' %] - +
@@ -14,9 +14,9 @@
[% IF ( current_budget_name ) %] -

Orders for budget '[% current_budget_name %]'

+

Orders for fund '[% current_budget_name %]'

[% ELSE %] -

Orders by budget

+

Orders by fund

[% END %] [% IF ( get_orders ) %] @@ -31,19 +31,19 @@ [% IF ( ordersloop ) %] - + - + - + - - - + + + [% FOREACH ordersloo IN ordersloop %] @@ -78,11 +78,11 @@ [% ELSE %] -
    +
    Filters -
  1. + + [% FOREACH budgetsloo IN budgetsloop %] [% IF ( budgetsloo.selected ) %] [% ELSE %] @@ -96,8 +96,18 @@ Output
    1. [% CGIextChoice %] - [% CGIsepChoice %]
    + + + +
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 @@

Other

  • Items lost
  • -
  • Orders by budget
  • +
  • Orders by fund
  • Catalog by itemtype
  • Average loan time
  • Koha database schema
  • 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
BudgetFund BasketBasket byBasket By Title Currency Vendor Price RRPBudgeted costBudgeted Cost Quantity Total RRPTotal costEntry dateDate receivedTotal CostEntry DateDate Received Notes