@@ -, +, @@
---
C4/Budgets.pm | 148 ++++++++++++
.../prog/en/modules/reports/orders_by_budget.tt | 115 +++++++++
.../prog/en/modules/reports/reports-home.tt | 9 +-
reports/orders_by_budget.pl | 250 ++++++++++++++++++++
4 files changed, 518 insertions(+), 4 deletions(-)
create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt
create mode 100755 reports/orders_by_budget.pl
--- a/C4/Budgets.pm
+++ a/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,129 @@ 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
+ INNER JOIN aqorders o
+ ON b.budget_id = o.budget_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);
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt
+++ a/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 ) %]
+
+ Budget |
+ Basket |
+ Basket by |
+ Title |
+ Currency |
+ Vendor Price |
+ RRP |
+ Budgeted cost |
+ Quantity |
+ Total RRP |
+ Total cost |
+ Entry date |
+ Date received |
+ Notes |
+
+ [% FOREACH ordersloo IN ordersloop %]
+ [% UNLESS ( loop.odd ) %]
+
+ [% ELSE %]
+
+ [% END %]
+ [% 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 %] |
+
+ [% END %]
+ TOTAL | | | | | | | | [% total_quantity %] | [% total_rrp %] | [% total_ecost %] | | | |
+
+ [% END %]
+ [% ELSE %]
+
+
+
+ [% END %]
+
+
+
+
+[% INCLUDE 'reports-menu.inc' %]
+
+
+[% INCLUDE 'intranet-bottom.inc' %]
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt
+++ a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt
@@ -61,11 +61,12 @@
Other
--- a/reports/orders_by_budget.pl
+++ a/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;
--