From b347ff51a6eb8515fa9d9c37766bb45790a3eeab Mon Sep 17 00:00:00 2001
From: Chris Cormack <crc@liblime.com>
Date: Mon, 29 Oct 2007 13:19:48 -0500
Subject: [PATCH] Adding reports code

Signed-off-by: Chris Cormack <crc@liblime.com>
Signed-off-by: Joshua Ferraro <jmf@liblime.com>
---
 C4/Reports.pm                                      | 429 +++++++++++++++++++++
 .../en/modules/reports/guided_reports_start.tmpl   | 304 +++++++++++++++
 .../prog/en/modules/reports/reports-home.tmpl      |  21 +-
 reports/guided_reports.pl                          | 347 +++++++++++++++++
 4 files changed, 1092 insertions(+), 9 deletions(-)
 create mode 100644 C4/Reports.pm
 create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl
 create mode 100755 reports/guided_reports.pl

diff --git a/C4/Reports.pm b/C4/Reports.pm
new file mode 100644
index 0000000000..181ac35889
--- /dev/null
+++ b/C4/Reports.pm
@@ -0,0 +1,429 @@
+package C4::Reports;
+
+# Copyright 2007 Liblime Ltd
+#
+# This file is part of Koha.
+#
+# 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
+
+use strict;
+require Exporter;
+
+use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
+use C4::Context;
+use C4::Output;
+# use Smart::Comments;
+
+# set the version for version checking
+$VERSION = 0.01;
+
+@ISA = qw(Exporter);
+@EXPORT =
+  qw(get_report_types get_report_areas get_columns build_query get_criteria
+  save_report get_saved_reports execute_query get_saved_report create_compound run_compound);
+
+our %table_areas;
+$table_areas{'1'} =
+  [ 'borrowers', 'statistics','items', 'biblioitems' ];    # circulation
+$table_areas{'2'} = [ 'items', 'biblioitems', 'biblio' ];   # catalogue
+$table_areas{'3'} = [ 'borrowers', 'accountlines' ];        # patrons
+$table_areas{'4'} = ['aqorders', 'biblio', 'items'];        # acquisitions
+
+our %keys;
+$keys{'1'} = [
+    'statistics.borrowernumber=borrowers.borrowernumber',
+    'items.itemnumber = statistics.itemnumber',
+    'biblioitems.biblioitemnumber = items.biblioitemnumber'
+];
+$keys{'2'} = [
+    'items.biblioitemnumber=biblioitems.biblioitemnumber',
+    'biblioitems.biblionumber=biblio.biblionumber'
+];
+$keys{'3'} = ['borrowers.borrowernumber=accountlines.borrowernumber'];
+$keys{'4'} = [
+	'aqorders.biblionumber=biblio.biblionumber',
+	'biblio.biblionumber=items.biblionumber'
+];
+
+# have to do someting here to know if its dropdown, free text, date etc
+
+our %criteria;
+$criteria{'1'} = [
+    'statistics.type',   'borrowers.categorycode',
+    'statistics.branch', 'biblioitems.itemtype',
+    'biblioitems.publicationyear|date',
+    'items.dateaccessioned|date'
+];
+$criteria{'2'} =
+  [ 'biblioitems.itemtype', 'items.holdingbranch', 'items.homebranch' ,'items.itemlost'];
+$criteria{'3'} = ['borrowers.branchcode'];
+$criteria{'4'} = ['aqorders.datereceived|date'];
+
+
+our %columns;
+my $columns_def_file = "columns.def";
+my $htdocs = C4::Context->config('intrahtdocs');                       
+my $section='intranet';
+my ($theme, $lang) = themelanguage($htdocs, $columns_def_file, $section);                                                                                 
+
+my $columns_def_file="$htdocs/$theme/$lang/$columns_def_file";    
+open (COLUMNS,$columns_def_file);
+while (my $input = <COLUMNS>){
+	my @row =split(/\t/,$input);
+	$columns{$row[0]}=$row[1];
+}
+
+close COLUMNS;
+
+=head1 NAME
+   
+C4::Reports - Module for generating reports 
+
+=head1 SYNOPSIS
+
+  use C4::Reports;
+
+=head1 DESCRIPTION
+
+
+=head1 METHODS
+
+=over 2
+
+=cut
+
+=item get_report_types()
+
+This will return a list of all the available report types
+
+=cut
+
+sub get_report_types {
+    my $dbh = C4::Context->dbh();
+
+    # FIXME these should be in the database perhaps
+    my @reports = ( 'Tabular', 'Summary', 'Matrix' );
+    my @reports2;
+    for ( my $i = 0 ; $i < 3 ; $i++ ) {
+        my %hashrep;
+        $hashrep{id}   = $i + 1;
+        $hashrep{name} = $reports[$i];
+        push @reports2, \%hashrep;
+    }
+    return ( \@reports2 );
+
+}
+
+=item get_report_areas()
+
+This will return a list of all the available report areas
+
+=cut
+
+sub get_report_areas {
+    my $dbh = C4::Context->dbh();
+
+    # FIXME these should be in the database
+    my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions' );
+    my @reports2;
+    for ( my $i = 0 ; $i < 4 ; $i++ ) {
+        my %hashrep;
+        $hashrep{id}   = $i + 1;
+        $hashrep{name} = $reports[$i];
+        push @reports2, \%hashrep;
+    }
+    return ( \@reports2 );
+
+}
+
+=item get_all_tables()
+
+This will return a list of all tables in the database 
+
+=cut
+
+sub get_all_tables {
+    my $dbh   = C4::Context->dbh();
+    my $query = "SHOW TABLES";
+    my $sth   = $dbh->prepare($query);
+    $sth->execute();
+    my @tables;
+    while ( my $data = $sth->fetchrow_arrayref() ) {
+        push @tables, $data->[0];
+    }
+    $sth->finish();
+    return ( \@tables );
+
+}
+
+=item get_columns($area)
+
+This will return a list of all columns for a report area
+
+=cut
+
+sub get_columns {
+
+    # this calls the internal fucntion _get_columns
+    my ($area) = @_;
+    my $tables = $table_areas{$area};
+    my @allcolumns;
+    foreach my $table (@$tables) {
+        my @columns = _get_columns($table);
+        push @allcolumns, @columns;
+    }
+    return ( \@allcolumns );
+}
+
+sub _get_columns {
+    my ($tablename) = @_;
+    my $dbh         = C4::Context->dbh();
+    my $sth         = $dbh->prepare("show columns from $tablename");
+    $sth->execute();
+    my @columns;
+	my %tablehash;
+	$tablehash{'table'}=$tablename;
+	push @columns, \%tablehash;
+    while ( my $data = $sth->fetchrow_arrayref() ) {
+        my %temphash;
+        $temphash{'name'}        = "$tablename.$data->[0]";
+        $temphash{'description'} = $columns{"$tablename.$data->[0]"};
+        push @columns, \%temphash;
+    }
+    $sth->finish();
+    return (@columns);
+}
+
+=item build_query($columns,$criteria,$orderby,$area)
+
+This will build the sql needed to return the results asked for, 
+$columns is expected to be of the format tablename.columnname.
+This is what get_columns returns.
+
+=cut
+
+sub build_query {
+    my ( $columns, $criteria, $orderby, $area, $totals ) = @_;
+### $orderby
+    my $keys   = $keys{$area};
+    my $tables = $table_areas{$area};
+
+    my $sql =
+      _build_query( $tables, $columns, $criteria, $keys, $orderby, $totals );
+    return ($sql);
+}
+
+sub _build_query {
+    my ( $tables, $columns, $criteria, $keys, $orderby, $totals ) = @_;
+### $orderby
+    # $keys is an array of joining constraints
+    my $dbh           = C4::Context->dbh();
+    my $joinedtables  = join( ',', @$tables );
+    my $joinedcolumns = join( ',', @$columns );
+    my $joinedkeys    = join( ' AND ', @$keys );
+    my $query =
+      "SELECT $totals $joinedcolumns FROM $tables->[0] ";
+	for (my $i=1;$i<@$tables;$i++){
+		$query .= "LEFT JOIN $tables->[$i] on ($keys->[$i-1]) ";
+	}
+
+    if ($criteria) {
+		$criteria =~ s/AND/WHERE/;
+        $query .= " $criteria";
+    }
+    if ($totals) {
+        my $groupby;
+        my @totcolumns = split( ',', $totals );
+        foreach my $total (@totcolumns) {
+            if ( $total =~ /\((.*)\)/ ) {
+                if ( $groupby eq '' ) {
+                    $groupby = " GROUP BY $1";
+                }
+                else {
+                    $groupby .= ",$1";
+                }
+            }
+        }
+        $query .= $groupby;
+    }
+    if ($orderby) {
+        $query .= $orderby;
+    }
+    return ($query);
+}
+
+=item get_criteria($area);
+
+Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the criteria and available values.
+
+=cut
+
+sub get_criteria {
+    my ($area) = @_;
+    my $dbh    = C4::Context->dbh();
+    my $crit   = $criteria{$area};
+    my @criteria_array;
+    foreach my $localcrit (@$crit) {
+        my ( $value, $type )   = split( /\|/, $localcrit );
+        my ( $table, $column ) = split( /\./, $value );
+        if ( $type eq 'date' ) {
+			my %temp;
+            $temp{'name'}   = $value;
+            $temp{'date'}   = 1;
+			$temp{'description'} = $columns{$value};
+            push @criteria_array, \%temp;
+        }
+        else {
+
+            my $query =
+              "SELECT distinct($column) as availablevalues FROM $table";
+            my $sth = $dbh->prepare($query);
+            $sth->execute();
+            my @values;
+            while ( my $row = $sth->fetchrow_hashref() ) {
+                push @values, $row;
+                ### $row;
+            }
+            $sth->finish();
+            my %temp;
+            $temp{'name'}   = $value;
+			$temp{'description'} = $columns{$value};
+            $temp{'values'} = \@values;
+            push @criteria_array, \%temp;
+        }
+    }
+    return ( \@criteria_array );
+}
+
+sub execute_query {
+    my ( $sql, $type, $format ) = @_;
+    my $dbh = C4::Context->dbh();
+
+    # take this line out when in production
+    $sql .= " LIMIT 10";
+    my $sth = $dbh->prepare($sql);
+    $sth->execute();
+	my $colnames=$sth->{'NAME'};
+	my @results;
+	my $row = join ('</th><th>',@$colnames);
+	$row = "<tr><th>$row</th></tr>";
+	my %temphash;
+	$temphash{'row'} = $row;
+	push @results, \%temphash;
+    
+    my $string;
+    while ( my @data = $sth->fetchrow_array() ) {
+
+            # tabular
+            my %temphash;
+            my $row = join( '</td><td>', @data );
+            $row = "<tr><td>$row</td></tr>";
+            $temphash{'row'} = $row;
+            if ( $format eq 'text' ) {
+                $string .= "\n" . $row;
+            }
+			if ($format eq 'tab' ){
+				$row = join("\t",@data);
+				$string .="\n" . $row;
+			}
+			if ($format eq 'csv' ){
+				$row = join(",",@data);
+				$string .="\n" . $row;
+			}
+
+            push @results, \%temphash;
+#        }
+    }
+    $sth->finish();
+    if ( $format eq 'text' || $format eq 'tab' || $format eq 'csv') {
+        return $string;
+    }
+    else {
+        return ( \@results );
+    }
+}
+
+=item save_report($sql,$name,$type,$notes)
+
+Given some sql and a name this will saved it so that it can resued
+
+=cut
+
+sub save_report {
+    my ( $sql, $name, $type, $notes ) = @_;
+    my $dbh = C4::Context->dbh();
+    my $query =
+"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes)  VALUES (?,now(),now(),?,?,?,?)";
+    my $sth = $dbh->prepare($query);
+    $sth->execute( 0, $sql, $name, $type, $notes );
+    $sth->finish();
+
+}
+
+sub get_saved_reports {
+    my $dbh   = C4::Context->dbh();
+    my $query = "SELECT * FROM saved_sql ORDER by date_created";
+    my $sth   = $dbh->prepare($query);
+    $sth->execute();
+    my @reports;
+    while ( my $data = $sth->fetchrow_hashref() ) {
+        push @reports, $data;
+    }
+    $sth->finish();
+    return ( \@reports );
+}
+
+sub get_saved_report {
+    my ($id)  = @_;
+    my $dbh   = C4::Context->dbh();
+    my $query = " SELECT * FROM saved_sql WHERE id = ?";
+    my $sth   = $dbh->prepare($query);
+    $sth->execute($id);
+    my $data = $sth->fetchrow_hashref();
+    $sth->finish();
+    return ( $data->{'savedsql'}, $data->{'type'} );
+}
+
+=item create_compound($masterID,$subreportID)
+
+This will take 2 reports and create a compound report using both of them
+
+=cut
+
+sub create_compound {
+	my ($masterID,$subreportID) = @_;
+	my $dbh = C4::Context->dbh();
+	# get the reports
+	my ($mastersql,$mastertype) = get_saved_report($masterID);
+	my ($subsql,$subtype) = get_saved_report($subreportID);
+	
+	# now we have to do some checking to see how these two will fit together
+	# or if they will
+	my ($mastertables,$subtables);
+	if ($mastersql =~ / from (.*) where /i){ 
+		$mastertables = $1;
+	}
+	if ($subsql =~ / from (.*) where /i){
+		$subtables = $1;
+	}
+	return ($mastertables,$subtables);
+}
+
+=head1 AUTHOR
+
+Chris Cormack <crc@liblime.com>
+
+=cut
+
+1;
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl
new file mode 100644
index 0000000000..82a10c3024
--- /dev/null
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl
@@ -0,0 +1,304 @@
+<!-- TMPL_INCLUDE NAME="doc-head-open.inc" -->
+<title>Koha -- Reports</title>
+
+<!-- TMPL_INCLUDE NAME="doc-head-close.inc" -->
+<!-- TMPL_INCLUDE NAME="calendar.inc" -->
+</head>
+<body>
+<!-- TMPL_INCLUDE NAME="header.inc" -->
+<!-- TMPL_INCLUDE NAME="circ-search.inc" -->
+
+<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/circ/circulation.pl">Reports</a> &rsaquo; <strong>Guided Reports Wizard </strong></div>
+
+<div id="doc3" class="yui-t2">
+
+   <div id="bd">
+    <div id="yui-main">
+    <div class="yui-b">
+
+	<div class="details">
+
+<!-- TMPL_IF NAME="start" -->
+	<form action="/cgi-bin/koha/reports/guided_reports.pl">
+		<input type="submit" name="phase" value="Build new"/>
+		<input type="submit" name="phase" value="Used saved"/>
+		<input type="submit" name="phase" value="Create report from SQL"/>
+		<input type="submit" name="phase" value="Create Compound Report"/>
+	</form>
+<!-- /TMPL_IF -->
+
+<!-- TMPL_IF NAME="saved1" -->
+Choose the report from the list
+<form action="/cgi-bin/koha/reports/guided_reports.pl">
+<select name="reports">
+<!-- TMPL_LOOP NAME="savedreports" -->
+<option value="<!-- TMPL_VAR NAME="id" -->"><!-- TMPL_VAR NAME="report_name"--></option>
+<!-- /TMPL_LOOP -->
+</select>
+<input type="submit" name="phase" value="Run this report">
+</form>
+<!-- /TMPL_IF -->
+
+
+<!-- TMPL_IF NAME="build1" -->
+<h3>Step 1 of 6: Choose a Module to Report on</h3>
+<form action="/cgi-bin/koha/reports/guided_reports.pl">
+<select name="areas">
+<!-- TMPL_LOOP NAME="areas" -->
+<option value="<!-- TMPL_VAR NAME="id" -->"><!-- TMPL_VAR NAME="name"--></option>
+<!-- /TMPL_LOOP -->
+</select>
+<br />
+<input type="submit" name="phase" value="Report on this Area">
+</form>
+<!-- /TMPL_IF -->
+
+
+<!-- TMPL_IF NAME="build2" -->
+<h3>Step 2 of 6: Pick a Report Type</h3>
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+<input type="hidden" name="area" value="<!-- TMPL_VAR NAME="area" -->">
+<select name="types">
+<!-- TMPL_LOOP NAME="types" -->
+<option value="<!-- TMPL_VAR NAME="id" -->"><!-- TMPL_VAR NAME="name"--></option>
+<!-- /TMPL_LOOP -->
+</select>
+<br />
+<input type="submit" name="phase" value="Choose this type">
+<br />
+</form>
+Tabular:
+<img src="http://staff-crc.dev.kohalibrary.com/intranet-tmpl/prog/img/reports-tabular-graphic.gif" />
+Summary:
+<img src="http://staff-crc.dev.kohalibrary.com/intranet-tmpl/prog/img/reports-summary-graphic.gif" />
+Matrix:
+<img src="http://staff-crc.dev.kohalibrary.com/intranet-tmpl/prog/img/reports-matrix-graphic.gif" />
+<!-- /TMPL_IF -->
+
+<!-- TMPL_IF NAME="build3" -->
+<h3>Step 3 of 6: Select Columns for Display</h3>
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+	<input type="hidden" name="area" value="<!-- TMPL_VAR NAME="area" -->">
+	<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->">
+	<select id="availableColumns" name="oldcolumns2" multiple size="25" style="width:200px;height:300px;">
+<!-- TMPL_LOOP NAME="columns" -->
+<!-- TMPL_IF NAME="table" -->
+
+<!-- TMPL_IF NAME="__first__" -->
+<!-- TMPL_ELSE -->
+</optgroup>
+<!-- /TMPL_IF -->
+
+<optgroup label="<!-- TMPL_VAR NAME="table"-->">
+<!-- TMPL_ELSE -->
+<option value="<!-- TMPL_VAR NAME="name" -->">
+<!-- TMPL_IF NAME="description" --><!-- TMPL_VAR NAME="description" -->
+<!-- TMPL_ELSE -->
+<!-- TMPL_VAR NAME="name" -->
+<!-- /TMPL_IF -->
+<!-- /TMPL_IF -->
+</option>
+<!-- /TMPL_LOOP -->
+</select>
+<input type="button" name="Add" value="Add" class="button" onClick="addColumn()"/>
+<select id="selectedColumns" name="columns" multiple size="25" style="width:200px;height:300px;"></select>
+<input type="button" name="delete" value="delete" class="button" onClick="delColumn()"/>
+
+<input type="submit" name="phase" value="Choose these columns">
+</form>
+<!-- /TMPL_IF -->
+
+<!-- TMPL_IF NAME="build4" -->
+<h3>Step 4 of 6: Select Criteria to Limit on </h3>
+
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post" >
+<input type="hidden" name="area" value="<!-- TMPL_VAR NAME="area" -->">
+<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->">
+<input type="hidden" name="column" value="<!-- TMPL_VAR NAME="column" -->">
+<!-- TMPL_LOOP NAME="criteria" -->
+<input type=checkbox name="criteria_column" value="<!-- TMPL_VAR NAME="name" -->"><!-- TMPL_VAR NAME="description" --> = 
+<!-- TMPL_IF NAME="date" -->
+<input type="text" size="10" id="<!-- TMPL_VAR NAME="name" -->_value" name="<!-- TMPL_VAR NAME="name" -->_value" value="" >                                             
+<img src="<!-- TMPL_VAR Name="themelang" -->/includes/calendar/cal.gif" id="buttonfrom1" style="cursor: pointer;"  />                 
+<script type="text/javascript">   
+Calendar.setup({  
+inputField     : "<!-- TMPL_VAR NAME="name" -->_value",                               
+ifFormat       : "%Y-%m-%d",                               
+button         : "buttonfrom1",                               
+align          : "Tl" 
+});     
+</script>          
+
+<!-- TMPL_ELSE -->
+<select name="<!-- TMPL_VAR NAME="name" -->_value">
+<!-- TMPL_LOOP NAME="values" -->
+<option value="<!-- TMPL_VAR NAME="availablevalues"-->"><!-- TMPL_VAR NAME="availablevalues" --></option>
+<!-- /TMPL_LOOP -->
+</select>
+<!-- /TMPL_IF -->
+<br />
+<!-- /TMPL_LOOP -->
+<br />
+<input type="submit" name="phase" value="Choose these criteria">
+</form>
+<!-- /TMPL_IF -->
+
+
+<!-- TMPL_IF NAME="build5" -->
+<h3>Step 5 of 6: Pick which columns to total</h3>
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+<input type="hidden" name="area" value="<!-- TMPL_VAR NAME="area" -->">
+<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->">
+<input type="hidden" name="column" value="<!-- TMPL_VAR NAME="column" -->">
+<input type="hidden" name="criteria" value="<!-- TMPL_VAR NAME="criteriastring" -->">
+<!-- TMPL_LOOP NAME="total_by" -->
+<input type=checkbox name="total_by" value="<!-- TMPL_VAR NAME="name" -->"><!-- TMPL_VAR NAME="name"-->
+<select name="<!-- TMPL_VAR NAME="name" -->_tvalue">
+
+<!-- TMPL_LOOP NAME="select" -->
+<option value="<!-- TMPL_VAR NAME="value"-->"><!-- TMPL_VAR NAME="value" --></option>
+<!-- /TMPL_LOOP -->
+</select>
+
+<br />
+<!-- /TMPL_LOOP -->
+<br />
+<input type="submit" name="phase" value="Choose Totals">
+</form>
+<!-- /TMPL_IF -->
+
+
+<!-- TMPL_IF NAME="build6" -->
+<h3>Step 6 of 6: Select the ordering</h3>
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+<input type="hidden" name="area" value="<!-- TMPL_VAR NAME="area" -->">
+<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->">
+<input type="hidden" name="column" value="<!-- TMPL_VAR NAME="column" -->">
+<input type="hidden" name="criteria" value="<!-- TMPL_VAR NAME="criteriastring" -->">
+<input type="hidden" name="totals" value="<!-- TMPL_VAR NAME="totals" -->">
+<!-- TMPL_LOOP NAME="order_by" -->
+<input type=checkbox name="order_by" value="<!-- TMPL_VAR NAME="name" -->"><!-- TMPL_VAR NAME="name"-->
+<select name="<!-- TMPL_VAR NAME="name" -->_ovalue">
+
+<!-- TMPL_LOOP NAME="select" -->
+<option value="<!-- TMPL_VAR NAME="value"-->"><!-- TMPL_VAR NAME="value" --></option>
+<!-- /TMPL_LOOP -->
+</select>
+
+<br />
+<!-- /TMPL_LOOP -->
+<br />
+<input type="submit" name="phase" value="Build Report">
+</form>
+<!-- /TMPL_IF -->
+
+
+<!-- TMPL_IF NAME="showreport" -->
+<!-- TMPL_VAR NAME="sql" -->
+
+<p>
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+<input type="hidden" name="sql" value="<!-- TMPL_VAR NAME="sql" -->">
+<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->">
+<input type="submit" name="phase" value="Save">  <input type="submit" name="phase" value="Execute">
+</form>
+<!-- /TMPL_IF -->
+
+<!-- TMPL_IF NAME="save" -->
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+<input type="hidden" name="sql" value="<!-- TMPL_VAR NAME="sql" -->">
+<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->">
+Report Name: <input type="text" name="reportname"> <br />
+Notes: <textarea name="notes"></textarea><br />
+<input type="submit" name="phase" value="Save Report">
+</form>
+<!-- /TMPL_IF -->
+
+<!-- TMPL_IF NAME="execute" -->
+Show results
+<table>
+<!-- TMPL_LOOP NAME="results" -->
+<!-- TMPL_VAR NAME="row" -->
+<!-- /TMPL_LOOP -->
+</table>
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+<select name="format">
+<option value="csv">Comma Separated Text</option>
+<option value="tab">Tab Separated Text</option>
+</select>
+<input type="submit" name="phase" value="Export">
+<input type="hidden" name="sql" value="<!-- TMPL_VAR NAME="sql" -->">
+</form>
+<!-- /TMPL_IF -->
+
+<!-- TMPL_IF NAME="create" -->
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+Report Name: <input type="text" name="reportname"> <br />
+Type:
+<select name="types">
+<!-- TMPL_LOOP NAME="types" -->
+<option value="<!-- TMPL_VAR NAME="id" -->"><!-- TMPL_VAR NAME="name"--></option>
+<!-- /TMPL_LOOP -->
+</select>
+<br />
+SQl: <textarea name="sql" cols=30 rows=10></textarea><br />
+<input type="submit" name="phase" value="Save Report">
+</form>
+<!-- /TMPL_IF -->
+
+<!-- TMPL_IF NAME="compound" -->
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+Master: <select name="master">
+<!-- TMPL_LOOP NAME="savedreports" -->
+<option value="<!-- TMPL_VAR NAME="id" -->"><!-- TMPL_VAR NAME="report_name"--></option>
+<!-- /TMPL_LOOP -->
+</select>
+ &nbsp;
+Sub report:<select name="subreport">
+<!-- TMPL_LOOP NAME="savedreports" -->
+<option value="<!-- TMPL_VAR NAME="id" -->"><!-- TMPL_VAR NAME="report_name"--></option>
+<!-- /TMPL_LOOP -->
+</select>
+<br />
+<input type="submit" name="phase" value="Save Compound">
+</form>
+<!-- /TMPL_IF -->
+
+<!-- TMPL_IF NAME="save_compound" -->
+<!-- TMPL_VAR NAME="master" --> <br />
+<!-- TMPL_VAR NAME="subsql" -->
+<!-- /TMPL_IF -->
+</div>
+</div>
+</div>
+<div class="yui-b">
+<!-- TMPL_INCLUDE NAME="guided-reports-view.inc" -->
+</div>
+</div>
+<script type="text/javascript">
+function setObjects() {
+	selectedColumnsObj=document.getElementById('selectedColumns');
+	availableColumnsObj=document.getElementById('availableColumns');
+}
+setObjects();
+
+function addColumn() {
+	for (i=0;i<availableColumnsObj.length;i++) {
+		if (availableColumnsObj.options[i].selected==true) {
+			var newColumnObj=document.createElement("OPTION");
+			newColumnObj.value=availableColumnsObj.options[i].value;
+			newColumnObj.text=availableColumnsObj.options[i].text;
+			selectedColumnsObj.appendChild(newColumnObj);
+			newColumnObj.selected=true;
+		}
+	}
+}
+
+function delColumn() {
+	for (i=0;i<=selectedColumnsObj.options.length;i++) {
+		if (selectedColumnsObj.options.selectedIndex>=0)
+		selectedColumnsObj.remove(selectedColumnsObj.options.selectedIndex)
+	}
+}
+</script>
+<!-- TMPL_INCLUDE NAME="intranet-bottom.inc" -->
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tmpl
index b6ebbfc08d..9d0cb3a243 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tmpl
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tmpl
@@ -4,20 +4,22 @@
 </head>
 <body>
 <!-- TMPL_INCLUDE NAME="header.inc" -->
-<!-- TMPL_INCLUDE NAME="cat-search.inc" -->
+<!-- TMPL_INCLUDE NAME="circ-search.inc" -->
 
-<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; Reports</div>
+<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/circ/circulation.pl">Reports</a></div>
+<div id="doc3" class="yui-t2">
 
-<div id="doc3" class="yui-t7">
-   
    <div id="bd">
-	<div id="yui-main">
-	<div class="yui-b"><div class="yui-g"> 
+    <div id="yui-main">
+    <div class="yui-b">
 
 <h1>Reports</h1>
 
-<div class="yui-g first">
-<div class="yui-u first">	<h2>Statistics wizards</h2>
+	<h2>Guided Report Wizard</h2>
+	<ul>
+		<li><a href="/cgi-bin/koha/reports/guided_reports.pl">Guided Reports</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>
@@ -51,7 +53,8 @@
 
 </div>
 </div>
+<div class="yui-b">
+<!-- TMPL_INCLUDE NAME="guided-reports-view.inc" -->
 </div>
-
 </div>
 <!-- TMPL_INCLUDE NAME="intranet-bottom.inc" -->
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
new file mode 100755
index 0000000000..0c01539199
--- /dev/null
+++ b/reports/guided_reports.pl
@@ -0,0 +1,347 @@
+#!/usr/bin/perl
+
+# Copyright 2007 Liblime ltd
+#
+# This file is part of Koha.
+#
+# 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
+
+use strict;
+use C4::Auth;
+use CGI;
+use C4::Output;
+use C4::Reports;
+
+=head1 NAME
+
+Script to control the guided report creation
+
+=head1 DESCRIPTION
+
+
+=over2
+
+=cut
+
+my $input = new CGI;
+my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
+    {
+        template_name   => "reports/guided_reports_start.tmpl",
+        query           => $input,
+        type            => "intranet",
+        authnotrequired => 0,
+        flagsrequired   => { editcatalogue => 1 },
+        debug           => 1,
+    }
+);
+
+my $phase = $input->param('phase');
+my $no_html = 0; # this will be set if we dont want to print out an html::template
+
+if ( !$phase ) {
+    $template->param( 'start' => 1 );
+
+    # show welcome page
+}
+
+elsif ( $phase eq 'Build new' ) {
+
+    # build a new report
+    $template->param( 'build1' => 1 );
+
+    # get report areas
+    my $areas = C4::Reports::get_report_areas();
+    $template->param( 'areas' => $areas );
+
+}
+
+elsif ( $phase eq 'Used saved' ) {
+
+    # use a saved report
+    # get list of reports and display them
+    $template->param( 'saved1' => 1 );
+    my $reports = get_saved_reports();
+    $template->param( 'savedreports' => $reports );
+}
+
+elsif ( $phase eq 'Report on this Area' ) {
+
+    # they have choosen a new report and the area to report on
+    # get area
+    my $area = $input->param('areas');
+    $template->param(
+        'build2' => 1,
+        'area'   => $area
+    );
+
+    # get report types
+    my $types = C4::Reports::get_report_types();
+    $template->param( 'types' => $types );
+}
+
+elsif ( $phase eq 'Choose this type' ) {
+
+    # they have chosen type and area
+    # get area and type and pass them to the template
+    my $area = $input->param('area');
+    my $type = $input->param('types');
+    $template->param(
+        'build3' => 1,
+        'area'   => $area,
+        'type'   => $type,
+    );
+
+    # get columns
+    my $columns = get_columns($area);
+    $template->param( 'columns' => $columns );
+}
+
+elsif ( $phase eq 'Choose these columns' ) {
+
+    # we now know type, area, and columns
+    # next step is the constraints
+    my $area    = $input->param('area');
+    my $type    = $input->param('type');
+    my @columns = $input->param('columns');
+    my $column  = join( ',', @columns );
+    $template->param(
+        'build4' => 1,
+        'area'   => $area,
+        'type'   => $type,
+        'column' => $column,
+    );
+    my $criteria = get_criteria($area);
+    $template->param( 'criteria' => $criteria );
+}
+
+elsif ( $phase eq 'Choose these criteria' ) {
+    my $area     = $input->param('area');
+    my $type     = $input->param('type');
+    my $column   = $input->param('column');
+    my @criteria = $input->param('criteria_column');
+	my $query_criteria;
+    foreach my $crit (@criteria) {
+        my $value = $input->param( $crit . "_value" );
+        if ($value) {
+            $query_criteria .= " AND $crit='$value'";
+        }
+    }
+
+    $template->param(
+        'build5'         => 1,
+        'area'           => $area,
+        'type'           => $type,
+        'column'         => $column,
+        'criteriastring' => $query_criteria,
+    );
+
+    # get columns
+    my @columns = split( ',', $column );
+    my @total_by;
+
+    # build structue for use by tmpl_loop to choose columns to order by
+    # need to do something about the order of the order :)
+	# we also want to use the %columns hash to get the plain english names
+    foreach my $col (@columns) {
+        my %total;
+        $total{'name'} = $col;
+        my @selects;
+        my %select1;
+        $select1{'value'} = 'sum';
+        push @selects, \%select1;
+        my %select2;
+        $select2{'value'} = 'min';
+        push @selects, \%select2;
+        my %select3;
+        $select3{'value'} = 'max';
+        push @selects, \%select3;
+        my %select4;
+        $select4{'value'} = 'avg';
+        push @selects, \%select4;
+        my %select5;
+        $select5{'value'} = 'count';
+        push @selects, \%select5;
+
+        $total{'select'} = \@selects;
+        push @total_by, \%total;
+    }
+
+    $template->param( 'total_by' => \@total_by );
+}
+
+elsif ( $phase eq 'Choose Totals' ) {
+    my $area     = $input->param('area');
+    my $type     = $input->param('type');
+    my $column   = $input->param('column');
+    my $criteria = $input->param('criteria');
+    my @total_by = $input->param('total_by');
+    my $totals;
+    foreach my $total (@total_by) {
+        my $value = $input->param( $total . "_tvalue" );
+        $totals .= "$value($total),";
+    }
+
+    $template->param(
+        'build6'         => 1,
+        'area'           => $area,
+        'type'           => $type,
+        'column'         => $column,
+        'criteriastring' => $criteria,
+        'totals'         => $totals,
+    );
+
+    # get columns
+    my @columns = split( ',', $column );
+    my @order_by;
+
+    # build structue for use by tmpl_loop to choose columns to order by
+    # need to do something about the order of the order :)
+    foreach my $col (@columns) {
+        my %order;
+        $order{'name'} = $col;
+        my @selects;
+        my %select1;
+        $select1{'value'} = 'asc';
+        push @selects, \%select1;
+        my %select2;
+        $select2{'value'} = 'desc';
+        push @selects, \%select2;
+        $order{'select'} = \@selects;
+        push @order_by, \%order;
+    }
+
+    $template->param( 'order_by' => \@order_by );
+}
+
+elsif ( $phase eq 'Build Report' ) {
+
+    # now we have all the info we need and can build the sql
+    my $area     = $input->param('area');
+    my $type     = $input->param('type');
+    my $column   = $input->param('column');
+    my $crit     = $input->param('criteria');
+    my $totals   = $input->param('totals');
+#    my @criteria = split( ',', $crit );
+    my $query_criteria=$crit;
+    # split the columns up by ,
+    my @columns = split( ',', $column );
+    my @order_by = $input->param('order_by');
+
+    my $query_orderby;
+    foreach my $order (@order_by) {
+        my $value = $input->param( $order . "_ovalue" );
+        if ($query_orderby) {
+            $query_orderby .= ",$order $value";
+        }
+        else {
+            $query_orderby = " ORDER BY $order $value";
+        }
+    }
+
+    # get the sql
+    my $sql =
+      build_query( \@columns, $query_criteria, $query_orderby, $area, $totals );
+    $template->param(
+        'showreport' => 1,
+        'sql'        => $sql,
+        'type'       => $type
+    );
+}
+
+elsif ( $phase eq 'Save' ) {
+	# Save the report that has just been built
+    my $sql  = $input->param('sql');
+    my $type = $input->param('type');
+    $template->param(
+        'save' => 1,
+        'sql'  => $sql,
+        'type' => $type
+    );
+}
+
+elsif ( $phase eq 'Save Report' ) {
+    # save the sql pasted in by a user 
+    my $sql  = $input->param('sql');
+    my $name = $input->param('reportname');
+    my $type = $input->param('type');
+	my $notes = $input->param('notes');
+    save_report( $sql, $name, $type, $notes );
+}
+
+elsif ( $phase eq 'Execute' ) {
+	# run the sql, and output results in a template	
+    my $sql     = $input->param('sql');
+    my $type    = $input->param('type');
+    my $results = execute_query($sql,$type);
+    $template->param(
+        'results' => $results,
+		'sql' => $sql,
+        'execute' => 1
+    );
+}
+
+elsif ($phase eq 'Run this report'){
+    # execute a saved report
+	my $report = $input->param('reports');
+	my ($sql,$type) = get_saved_report($report);
+	my $results = execute_query($sql,$type);
+    $template->param(
+        'results' => $results,
+		'sql' => $sql,
+        'execute' => 1
+    );
+}	
+
+elsif ($phase eq 'Export'){
+	# export results to tab separated text
+	my $sql     = $input->param('sql');
+	$no_html=1;
+	print $input->header(   -type => 'application/octet-stream',
+	          -attachment=>'reportresults.csv');
+	my $format=$input->param('format');
+	my $results = execute_query($sql,1,$format);
+	print $results;
+	
+}
+
+elsif ($phase eq 'Create report from SQL'){
+	# alllow the user to paste in sql 
+	$template->param('create' => 1);
+	 my $types = C4::Reports::get_report_types();
+	$template->param( 'types' => $types ); 
+}
+
+elsif ($phase eq 'Create Compound Report'){
+	my $reports = get_saved_reports();  
+	$template->param( 'savedreports' => $reports,
+		'compound' => 1,
+	);
+}
+
+elsif ($phase eq 'Save Compound'){
+    my $master = $input->param('master');
+	my $subreport = $input->param('subreport');
+#	my $compound_report = create_compound($master,$subreport);
+#	my $results = run_compound($compound_report);
+	my ($mastertables,$subtables) = create_compound($master,$subreport);
+	$template->param( 'save_compound' => 1,
+		master=>$mastertables,
+		subsql=>$subtables
+	);
+}
+
+
+if (!$no_html){
+	output_html_with_http_headers $input, $cookie, $template->output;
+}
-- 
2.11.0