From f1931eb8f2e91182b242b54fe289b2e386231d82 Mon Sep 17 00:00:00 2001
From: simith <simith@inlibro.com>
Date: Thu, 5 Mar 2015 10:56:14 -0500
Subject: [PATCH] Bug 11371 - Add a new report : Orders by budget
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This patch adds the "Orders by budget" report to the reports page.

This report can generate the list of all orders on a specific budget. The "All budgets" and "All active budgets" options are also available to have different views of all your orders.

A "[i]" is also added before the names of inactive budgets in the budget selection box.

How to test:
apply patch
run updatedatabase.pl
Go to Home > Reports > Orders by budget
Select one or all budgets
Output to Scrren or CSV file
Set a budget to inactive
Verify that this budget is marked wit [i] in drop down list (Filters)

Sponsored-by: ccsr

http://bugs.koha-community.org/show_bug.cgi?id=11371

 Author:    bouzid <bouzid.fergani@inlibro.com>

Followed test plan, works as expected.
Signed-off-by: Marc Véron <veron@veron.ch>
---
 C4/Budgets.pm                                      |  156 ++++++++++++-
 .../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    |  143 ++++++++++++
 .../prog/en/modules/reports/reports-home.tt        |   79 +++----
 reports/orders_by_fund.pl                          |  238 ++++++++++++++++++++
 t/db_dependent/Acquisition.t                       |   23 +-
 t/db_dependent/Budgets.t                           |   31 ++-
 8 files changed, 638 insertions(+), 45 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 79012ba..fc0e8af 100644
--- a/C4/Budgets.pm
+++ b/C4/Budgets.pm
@@ -35,6 +35,9 @@ BEGIN {
         &GetBudgetByOrderNumber
         &GetBudgetByCode
         &GetBudgets
+        &BudgetsByActivity
+        &GetBudgetsReport
+        &GetBudgetReport
         &GetBudgetHierarchy
 	    &AddBudget
         &ModBudget
@@ -55,6 +58,7 @@ BEGIN {
         &GetBudgetPeriods
         &ModBudgetPeriod
         &AddBudgetPeriod
+        &GetBudgetPeriodDescription
 	    &DelBudgetPeriod
 
         &ModBudgetPlan
@@ -77,7 +81,6 @@ BEGIN {
 
 # ----------------------------BUDGETS.PM-----------------------------";
 
-
 =head1 FUNCTIONS ABOUT BUDGETS
 
 =cut
@@ -458,6 +461,39 @@ 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;
+    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;
@@ -601,6 +637,8 @@ sub DelBudget {
 }
 
 
+# -------------------------------------------------------------------
+
 =head2 GetBudget
 
   &GetBudget($budget_id);
@@ -609,7 +647,6 @@ get a specific budget
 
 =cut
 
-# -------------------------------------------------------------------
 sub GetBudget {
     my ( $budget_id ) = @_;
     my $dbh = C4::Context->dbh;
@@ -624,6 +661,8 @@ sub GetBudget {
     return $result;
 }
 
+# -------------------------------------------------------------------
+
 =head2 GetBudgetByOrderNumber
 
   &GetBudgetByOrderNumber($ordernumber);
@@ -632,7 +671,6 @@ get a specific budget by order number
 
 =cut
 
-# -------------------------------------------------------------------
 sub GetBudgetByOrderNumber {
     my ( $ordernumber ) = @_;
     my $dbh = C4::Context->dbh;
@@ -648,6 +686,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);
@@ -985,7 +1134,6 @@ sub ConvertCurrency {
     return ( $price / $cur );
 }
 
-
 =head2 CloneBudgetPeriod
 
   my $new_budget_period_id = CloneBudgetPeriod({
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..49dfc9d
--- /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 %]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
new file mode 100644
index 0000000..8b9a025
--- /dev/null
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt
@@ -0,0 +1,143 @@
+[% INCLUDE 'doc-head-open.inc' %]
+<title>Koha &rsaquo; Reports &rsaquo; Orders by fund</title>
+[% INCLUDE 'doc-head-close.inc' %]
+</head>
+<body>
+[% INCLUDE 'header.inc' %]
+[% INCLUDE 'cat-search.inc' %]
+
+<link rel="stylesheet" type="text/css" href="[% themelang %]/css/datatables.css" />
+[% INCLUDE 'datatables.inc' %]
+<script type="text/javascript">
+    $(document).ready( function () {
+        $('#funds').DataTable($.extend(true, {}, dataTablesDefaults,{"sPaginationType": "full_numbers"}));
+    } );
+</script>
+<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a>[% IF ( get_orders ) %] &rsaquo; <a href="/cgi-bin/koha/reports/orders_by_fund.pl">Orders by fund</a> &rsaquo; Results[% ELSE %] &rsaquo; Orders by fund[% END %]</div>
+
+<div id="doc3" class="yui-t2">
+
+<div id="bd">
+        <div id="yui-main">
+        <div class="yui-b">
+
+[% IF ( current_budget_name ) %]<h1>Orders for fund '[% current_budget_name %]'</h1>
+[% ELSE %]<h1>Orders by fund</h1>
+[% END %]
+
+[% IF ( get_orders ) %]
+    <div class="results">
+        [% IF ( total ) %]
+            Orders found: [% total %]
+        [% ELSE %]
+            No order found
+        [% END %]
+    </div>
+
+    [% IF ( ordersloop ) %]<table id="funds">
+        <thead>
+        <tr>
+        <th>Fund</th>
+        <th>Basket</th>
+        <th>Basket Name</th>
+        <th>Basket By</th>
+        <th>Title</th>
+        <th>Currency</th>
+        <th>Vendor Price</th>
+        <th>RRP</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>Notes</th>
+        </tr>
+        </thead>
+        <tbody>
+        [% FOREACH ordersloo IN ordersloop %]
+            [% UNLESS ( loop.odd ) %]<tr class="highlight">
+            [% ELSE %] <tr>
+            [% END %]
+            <td>[% ordersloo.budget_name |html %]</td>
+            <td><a href="/cgi-bin/koha/acqui/basket.pl?basketno=[% ordersloo.basketno %]"> [% ordersloo.basketno |html %]</a></td>
+            <td>[% ordersloo.basketname |html %]</td>
+            <td>[% ordersloo.authorisedbyname %]</td>
+            <td><a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=[% ordersloo.biblionumber %]"> [% ordersloo.title |html %]</a></td>
+            <td>[% ordersloo.currency %]</td>
+            <td>[% ordersloo.listprice %]</td>
+            <td>[% ordersloo.rrp %]</td>
+            <td>[% ordersloo.ecost %]</td>
+            <td>[% ordersloo.quantity %]</td>
+            <td>[% ordersloo.total_rrp %]</td>
+            <td>[% ordersloo.total_ecost %]</td>
+            <td>[% ordersloo.entrydate %]</td>
+            <td>[% ordersloo.datereceived %]</td>
+            <td>[% ordersloo.order_internalnote |html %]</td>
+            </tr>
+        [% END %]
+        </tbody>
+        <tfoot><tr><th>TOTAL</th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th>[% total_quantity %]</th><th>[% total_rrp %]</th><th>[% total_ecost %]</th><th></th><th></th><th></th></tr></tfoot>
+        </table>
+    [% END %]
+    [% ELSE %]
+        <form name="f" action="/cgi-bin/koha/reports/orders_by_fund.pl" method="post">
+        <fieldset class="rows">
+        <legend>Filters</legend>
+        <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 %]
+                <option value="[% budgetsloo.value %]">[% budgetsloo.description %] [% budgetsloo.period %]</option>
+            [% END %]
+        [% END %]
+        </select>
+        </li></ol>
+        </fieldset>
+
+        <fieldset class="rows">
+        <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>
+                <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 %]
+                    [% IF delimiterloo == delimiterPreference %]
+                        <option value="[% delimiterloo %]">[% delimiterloo %]</option>
+                    [% END %]
+                [% END %]
+                [% FOREACH delimiterloo IN delimiterloop %]
+                    [% IF delimiterloo != delimiterPreference %]
+                        <option value="[% delimiterloo %]">[% delimiterloo %]</option>
+                    [% END %]
+                [% END %]
+                </select>
+        </li></ol>
+        </fieldset>
+
+        <fieldset class="action">
+        <input type="submit" value="Submit" />
+        <input type="hidden" name="get_orders" value="1" /></fieldset>
+        </form>
+
+    [% END %]
+
+</div>
+</div>
+<div class="yui-b">
+[% INCLUDE 'reports-menu.inc' %]
+</div>
+</div>
+[% 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 fde9ad3..05f7f8d 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 @@
    <div id="bd">
     <div id="yui-main">
 
-	<div class="yui-g">
-	<h1>Reports</h1>
+    <div class="yui-g">
+    <h1>Reports</h1>
     <div class="yui-u first"><h2>Guided reports</h2>
-	<ul>
-        <li><a href="/cgi-bin/koha/reports/guided_reports.pl">Guided reports wizard</a></li>
-        [% IF ( CAN_user_reports_create_reports ) %]<li><a href="/cgi-bin/koha/reports/guided_reports.pl?phase=Build%20new">Build new</a></li>[% END %]
-        [% IF ( CAN_user_reports_execute_reports ) %]<li><a href="/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved">Use saved</a></li>[% END %]
-		[% IF ( CAN_user_reports_create_reports ) %]<li><a href="/cgi-bin/koha/reports/guided_reports.pl?phase=Create%20report%20from%20SQL">Create from SQL</a></li>[% END %]
-	</ul>
-	
-    <h5>Reports dictionary</h5>
-	<ul>
-        <li><a href="/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary">View dictionary</a></li>
-	</ul>
+        <ul>
+            <li><a href="/cgi-bin/koha/reports/guided_reports.pl">Guided reports wizard</a></li>
+            [% IF ( CAN_user_reports_create_reports ) %]<li><a href="/cgi-bin/koha/reports/guided_reports.pl?phase=Build%20new">Build new</a></li>[% END %]
+            [% IF ( CAN_user_reports_execute_reports ) %]<li><a href="/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved">Use saved</a></li>[% END %]
+            [% IF ( CAN_user_reports_create_reports ) %]<li><a href="/cgi-bin/koha/reports/guided_reports.pl?phase=Create%20report%20from%20SQL">Create from SQL</a></li>[% END %]
+        </ul>
 
-	<h2>Statistics wizards</h2>
-	<ul>
-		<li><a href="/cgi-bin/koha/reports/acquisitions_stats.pl">Acquisitions</a></li>
-		<li><a href="/cgi-bin/koha/reports/borrowers_stats.pl">Patrons</a></li>
-		<li><a href="/cgi-bin/koha/reports/catalogue_stats.pl">Catalog</a></li>
-		<li><a href="/cgi-bin/koha/reports/issues_stats.pl">Circulation</a></li>
-		<li><a href="/cgi-bin/koha/reports/serials_stats.pl">Serials</a></li>
-		<li><a href="/cgi-bin/koha/reports/reserves_stats.pl">Holds</a></li>
-      </ul>
+        <h5>Reports dictionary</h5>
+        <ul>
+            <li><a href="/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary">View dictionary</a></li>
+        </ul>
+
+        <h2>Statistics wizards</h2>
+        <ul>
+            <li><a href="/cgi-bin/koha/reports/acquisitions_stats.pl">Acquisitions</a></li>
+            <li><a href="/cgi-bin/koha/reports/borrowers_stats.pl">Patrons</a></li>
+            <li><a href="/cgi-bin/koha/reports/catalogue_stats.pl">Catalog</a></li>
+            <li><a href="/cgi-bin/koha/reports/issues_stats.pl">Circulation</a></li>
+            <li><a href="/cgi-bin/koha/reports/serials_stats.pl">Serials</a></li>
+            <li><a href="/cgi-bin/koha/reports/reserves_stats.pl">Holds</a></li>
+        </ul>
 
         [% IF UseKohaPlugins %]
         <h2>Report Plugins</h2>
@@ -50,23 +50,24 @@
 	<ul>
         <li><a href="/cgi-bin/koha/reports/bor_issues_top.pl">Patrons with the most checkouts</a></li>
         <li><a href="/cgi-bin/koha/reports/cat_issues_top.pl">Most-circulated items</a></li>
-	</ul>	
-	
-	<h2>Inactive</h2>
-	<ul>
-		<li><a href="/cgi-bin/koha/reports/borrowers_out.pl">Patrons who haven't checked out</a></li>
-		<li><a href="/cgi-bin/koha/reports/catalogue_out.pl">Items with no checkouts</a></li>
-	</ul>
-	
-	<h2>Other</h2>
-	<ul>
-		<li><a href="/cgi-bin/koha/reports/itemslost.pl">Items lost</a></li>
-        <li><a href="/cgi-bin/koha/reports/manager.pl?report_name=itemtypes">Catalog by item type</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>
-        <li><a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library" target="blank">Koha reports library</a></li>
-        <!--<li><a href="/cgi-bin/koha/reports/stats.screen.pl">Till reconciliation</a></li> -->
-	</ul></div>
+    </ul>
+
+    <h2>Inactive</h2>
+    <ul>
+        <li><a href="/cgi-bin/koha/reports/borrowers_out.pl">Patrons who haven't checked out</a></li>
+        <li><a href="/cgi-bin/koha/reports/catalogue_out.pl">Items with no checkouts</a></li>
+    </ul>
+
+    <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_fund.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>
+                <li><a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library" target="blank">Koha reports library</a></li>
+                <!--<li><a href="/cgi-bin/koha/reports/stats.screen.pl">Till reconciliation</a></li> -->
+</ul></div>
 </div>
 
 </div>
diff --git a/reports/orders_by_fund.pl b/reports/orders_by_fund.pl
new file mode 100755
index 0000000..2a8a7cb
--- /dev/null
+++ b/reports/orders_by_fund.pl
@@ -0,0 +1,238 @@
+#!/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::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'}));
+        $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 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;
+            my $sep='';
+            if ($order->{'order_vendornote'}){
+            $sep='. Note2:'
+            }
+            $order->{'order_internalnote'} .= $sep .$order->{'order_vendornote'};
+            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(@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
+    # active budgets
+    my @active_budgets = C4::Budgets::GetBudgetsByActivity(1);
+    # non active budgets
+    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});
+        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,
+        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 83486d2..e60f6e0 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);
@@ -922,3 +930,16 @@ 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 9c34a8f..85eb749 100755
--- a/t/db_dependent/Budgets.t
+++ b/t/db_dependent/Budgets.t
@@ -1,5 +1,6 @@
+#!/usr/bin/perl
 use Modern::Perl;
-use Test::More tests => 122;
+use Test::More ;
 
 BEGIN {
     use_ok('C4::Budgets')
@@ -468,6 +469,34 @@ for my $budget (@$budget_hierarchy_cloned) {
 is( $number_of_budgets_not_reset, 0,
     'CloneBudgetPeriod has reset all budgets (funds)' );
 
+#GetBudgetPeriodDescription
+$my_budgetperiod = {
+     budget_period_startdate   => '2008-01-01',
+     budget_period_enddate     => '2008-12-31',
+     budget_period_description => 'MAPERI',
+     budget_period_active      => 0,
+};
+$bpid = AddBudgetPeriod($my_budgetperiod);
+$my_budget = {
+     budget_code      => 'ABCD',
+     budget_amount    => '123.132000',
+     budget_name      => 'Periodiques',
+     budget_notes     => 'This is a note',
+     budget_period_id => $bpid,
+};
+$budget_id = AddBudget($my_budget);
+my $data=GetBudgetPeriodDescription($budget_id);
+is( $data->{budget_period_description}, 'MAPERI' ,'GetBudgetPeriodDescription return right value');
+
+#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);
 
 # MoveOrders
 my $number_orders_moved = C4::Budgets::MoveOrders();
-- 
1.7.10.4