Attachment #12126 for bug #7993

View | Details | Raw Unified | Return to bug 7993
Collapse All | Expand All




use C4::Context;
use C4::Dates qw/format_date format_date_in_iso/;
use C4::Templates qw/themelanguage/;
use C4::Koha;
use C4::Output;
use XML::Simple;
use XML::Dumper;
use C4::Debug;

# use Data::Dumper;

BEGIN {
    # set the version for version checking
    $VERSION = 3.07.00.049;
    require Exporter;
    @ISA    = qw(Exporter);
    @EXPORT = qw(
      get_report_types get_report_areas get_report_groups get_columns build_query get_criteria
      save_report get_saved_reports execute_query get_saved_report create_compound run_compound
      get_column_type get_distinct_values save_dictionary get_from_dictionary
      delete_definition delete_report format_results get_sql
      nb_rows update_sql build_authorised_value_list
    );
}

=item get_report_areas()
$table_areas{'1'} =
  [ 'borrowers', 'statistics','items', 'biblioitems' ];    # circulation
$table_areas{'2'} = [ 'items', 'biblioitems', 'biblio' ];   # catalogue
$table_areas{'3'} = [ 'borrowers' ];        # patrons
$table_areas{'4'} = ['aqorders', 'biblio', 'items'];        # acquisitions
$table_areas{'5'} = [ 'borrowers', 'accountlines' ];        # accounts
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'} = [ ];
$keys{'4'} = [
	'aqorders.biblionumber=biblio.biblionumber',
	'biblio.biblionumber=items.biblionumber'
];
$keys{'5'} = ['borrowers.borrowernumber=accountlines.borrowernumber'];

This will return a list of all the available report areas

=cut

my @REPORT_AREA = (
    [CIRC => "Circulation"],
    [CAT  => "Catalogue"],
    [PAT  => "Patrons"],
    [ACQ  => "Acquisition"],
    [ACC  => "Accounts"],
);
my $AREA_NAME_SQL_SNIPPET
  = "CASE report_area " .
    join (" ", map "WHEN '$_->[0]' THEN '$_->[1]'", @REPORT_AREA) .
    " END AS areaname";
sub get_report_areas {
    return \@REPORT_AREA
}

my %table_areas = (
    CIRC => [ 'borrowers', 'statistics', 'items', 'biblioitems' ],
    CAT  => [ 'items', 'biblioitems', 'biblio' ],
    PAT  => ['borrowers'],
    ACQ  => [ 'aqorders', 'biblio', 'items' ],
    ACC  => [ 'borrowers', 'accountlines' ],
);
my %keys = (
    CIRC => [ 'statistics.borrowernumber=borrowers.borrowernumber',
              'items.itemnumber = statistics.itemnumber',
              'biblioitems.biblioitemnumber = items.biblioitemnumber' ],
    CAT  => [ 'items.biblioitemnumber=biblioitems.biblioitemnumber',
              'biblioitems.biblionumber=biblio.biblionumber' ],
    PAT  => [],
    ACQ  => [ 'aqorders.biblionumber=biblio.biblionumber',
              'biblio.biblionumber=items.biblionumber' ],
    ACC  => ['borrowers.borrowernumber=accountlines.borrowernumber'],
);

# have to do someting here to know if its dropdown, free text, date etc
my %criteria = (
    CIRC => [ 'statistics.type', 'borrowers.categorycode', 'statistics.branch',
              'biblioitems.publicationyear|date', 'items.dateaccessioned|date' ],
    CAT  => [ 'items.itemnumber|textrange', 'items.biblionumber|textrange',
              'items.barcode|textrange', 'biblio.frameworkcode',
              'items.holdingbranch', 'items.homebranch',
              'biblio.datecreated|daterange', 'biblio.timestamp|daterange',
              'items.onloan|daterange', 'items.ccode',
              'items.itemcallnumber|textrange', 'items.itype', 'items.itemlost',
              'items.location' ],
    PAT  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
    ACQ  => ['aqorders.datereceived|date'],
    ACC  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
);

# Adds itemtypes to criteria, according to the syspref
if ( C4::Context->preference('item-level_itypes') ) {
    unshift @{ $criteria{'CIRC'} }, 'items.itype';
    unshift @{ $criteria{'CAT'} }, 'items.itype';
} else {
    unshift @{ $criteria{'CIRC'} }, 'biblioitems.itemtype';
    unshift @{ $criteria{'CAT'} }, 'biblioitems.itemtype';
}

=head1 NAME


}

=item get_report_groups()

This will return a list of all the available report areas with groups

=cut

sub get_report_groups {
    my $dbh = C4::Context->dbh();

    my $groups = GetAuthorisedValues('REPORT_GROUP');
    my $subgroups = GetAuthorisedValues('REPORT_SUBGROUP');

    my %groups_with_subgroups = map { $_->{authorised_value} => {
                        name => $_->{lib},
                        groups => {}
                    } } @$groups;
    foreach (@$subgroups) {
        my $sg = $_->{authorised_value};
        my $g = $_->{lib_opac}
          or warn( qq{REPORT_SUBGROUP "$sg" without REPORT_GROUP (lib_opac)} ),
             next;
        my $g_sg = $groups_with_subgroups{$g}
          or warn( qq{REPORT_SUBGROUP "$sg" with invalid REPORT_GROUP "$g"} ),
             next;
        $g_sg->{subgroups}{$sg} = $_->{lib};
    }
    return \%groups_with_subgroups

}

=item get_all_tables()

sub get_columns {

    # this calls the internal fucntion _get_columns
    my ( $area, $cgi ) = @_;
    my $tables = $table_areas{$area}
      or die qq{Unsuported report area "$area"};

    my @allcolumns;
    my $first = 1;
    foreach my $table (@$tables) {


=item execute_query

  ($results, $error) = execute_query($sql, $offset, $limit)


When passed C<$sql>, this function returns an array ref containing a result set

=cut

sub save_report {
    my ($fields) = @_;
    my $borrowernumber = $fields->{borrowernumber};
    my $sql = $fields->{sql};
    my $name = $fields->{name};
    my $type = $fields->{type};
    my $notes = $fields->{notes};
    my $area = $fields->{area};
    my $group = $fields->{group};
    my $subgroup = $fields->{subgroup};
    my $cache_expiry = $fields->{cache_expiry} || 300;
    my $public = $fields->{public};

    my $dbh = C4::Context->dbh();
    $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
    my $query = "INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,report_area,report_group,report_subgroup,type,notes,cache_expiry,public)  VALUES (?,now(),now(),?,?,?,?,?,?,?,?,?)";
    $dbh->do($query, undef, $borrowernumber, $sql, $name, $area, $group, $subgroup, $type, $notes, $cache_expiry, $public);

    my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
                                   $borrowernumber, $name);
    return $id;
}

sub update_sql {
    my $id         = shift || croak "No Id given";
    my $fields     = shift;
    my $sql = $fields->{sql};
    my $name = $fields->{name};
    my $notes = $fields->{notes};
    my $group = $fields->{group};
    my $subgroup = $fields->{subgroup};
    my $cache_expiry = $fields->{cache_expiry};
    my $public = $fields->{public};

    if( $cache_expiry >= 2592000 ){
      die "Please specify a cache expiry less than 30 days\n";
    }

    my $dbh        = C4::Context->dbh();
    $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, report_group = ?, report_subgroup = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
    $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id );
    $sth->execute( $sql, $reportname, $notes, $cache_expiry, $public, $id );
    $sth->finish();
}

sub store_results {

}	

sub delete_report {
    my ($id)  = @_;
    my $dbh   = C4::Context->dbh();
    my $query = "DELETE FROM saved_sql WHERE id = ?";
    my $sth   = $dbh->prepare($query);
    $sth->execute($id);
}	


my $SAVED_REPORTS_BASE_QRY = <<EOQ;
SELECT s.*, r.report, r.date_run, $AREA_NAME_SQL_SNIPPET, av_g.lib AS groupname, av_sg.lib AS subgroupname,
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
FROM saved_sql s
LEFT JOIN saved_reports r ON r.report_id = s.id
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup)
LEFT OUTER JOIN borrowers b USING (borrowernumber)
EOQ
my $DATE_FORMAT = "%d/%m/%Y";
sub get_saved_reports {
# $filter is either { date => $d, author => $a, keyword => $kw, }
# or $keyword. Optional.
    my ($filter) = @_;
    $filter = { keyword => $filter } if $filter && !ref( $filter );
    my ($group, $subgroup) = @_;

    my $dbh   = C4::Context->dbh();
    my $query = $SAVED_REPORTS_BASE_QRY;
    my (@cond,@args);
    my $query = "SELECT saved_sql.id, report_id, report,
                        date_run, date_created, last_modified, savedsql, last_run,
                        report_name, type, notes,
                        borrowernumber, surname as borrowersurname, firstname as borrowerfirstname,
                        cache_expiry, public
                 FROM saved_sql 
                 LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
                 LEFT OUTER JOIN borrowers USING (borrowernumber)";
    if ($filter) {
        if (my $date = $filter->{date}) {
            $date = format_date_in_iso($date);

                         savedsql LIKE ?";
            push @args, $keyword, $keyword, $keyword, $keyword;
        }
        if ($filter->{group}) {
            push @cond, "report_group = ?";
            push @args, $filter->{group};
        }
        if ($filter->{subgroup}) {
            push @cond, "report_subgroup = ?";
            push @args, $filter->{subgroup};
        }
    }
    $query .= " WHERE ".join( " AND ", map "($_)", @cond ) if @cond;
    $query .= " ORDER by date_created";

sub get_saved_report {
    my $dbh   = C4::Context->dbh();
    my $query;
    my $sth;
    my $report_arg;
    if ($#_ == 0 && ref $_[0] ne 'HASH') {
        ($report_arg) = @_;

    } else {
        return;
    }
    return $dbh->selectrow_hashref($query, undef, $report_arg);
    $sth->execute($report_arg);
    my $data = $sth->fetchrow_hashref();
    return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'}, $data->{'cache_expiry'}, $data->{'public'}, $data->{'id'} );
}

=item create_compound($masterID,$subreportID)

=cut

sub create_compound {
    my ( $masterID, $subreportID ) = @_;
    my $dbh = C4::Context->dbh();

    # get the reports
    my $master = get_saved_report($masterID);
    my $mastersql = $master->{savedsql};
    my $mastertype = $master->{type};
    my $sub = get_saved_report($subreportID);
    my $subsql = $master->{savedsql};
    my $subtype = $master->{type};

    # 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 );
}

=item get_column_type($column)

}	

sub save_dictionary {
    my ( $name, $description, $sql, $area ) = @_;
    my $dbh   = C4::Context->dbh();
    my $query = "INSERT INTO reports_dictionary (name,description,saved_sql,report_area,date_created,date_modified)
  VALUES (?,?,?,?,now(),now())";
    my $sth = $dbh->prepare($query);
    $sth->execute($name,$description,$sql,$area) || return 0;
    return 1;
}

my $DICTIONARY_BASE_QRY = <<EOQ;
SELECT d.*, $AREA_NAME_SQL_SNIPPET
FROM reports_dictionary d
EOQ
sub get_from_dictionary {
    my ( $area, $id ) = @_;
    my $dbh   = C4::Context->dbh();
    my $query = $DICTIONARY_BASE_QRY;
    if ($area) {
        $query .= " WHERE report_area = ?";
    } elsif ($id) {
        $query .= " WHERE id = ?";
    }
    my $sth = $dbh->prepare($query);
    if ($id) {
        $sth->execute($id);
    } elsif ($area) {
        $sth->execute($area);
    } else {
        $sth->execute();
    }
    my @loop;
    while ( my $data = $sth->fetchrow_hashref() ) {
        push @loop, $data;
    }
    return ( \@loop );
	while (my $data = $sth->fetchrow_hashref()){
		$data->{'areaname'}=$reports[$data->{'area'}-1];
		push @loop,$data;
		
	}
	return (\@loop);
}

sub delete_definition {

	close COLUMNS;
	return \%columns;
}

=item build_authorised_value_list($authorised_value)

Returns an arrayref - hashref pair. The hashref consists of
various code => name lists depending on the $authorised_value.
The arrayref is the hashref keys, in appropriate order

=cut

sub build_authorised_value_list {
    my ( $authorised_value ) = @_;

    my $dbh = C4::Context->dbh;
    my @authorised_values;
    my %authorised_lib;

    # builds list, depending on authorised value...
    if ( $authorised_value eq "branches" ) {
        my $branches = GetBranchesLoop();
        foreach my $thisbranch (@$branches) {
            push @authorised_values, $thisbranch->{value};
            $authorised_lib{ $thisbranch->{value} } = $thisbranch->{branchname};
        }
    } elsif ( $authorised_value eq "itemtypes" ) {
        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
        $sth->execute;
        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
            push @authorised_values, $itemtype;
            $authorised_lib{$itemtype} = $description;
        }
    } elsif ( $authorised_value eq "cn_source" ) {
        my $class_sources  = GetClassSources();
        my $default_source = C4::Context->preference("DefaultClassificationSource");
        foreach my $class_source ( sort keys %$class_sources ) {
            next
              unless $class_sources->{$class_source}->{'used'}
                  or ( $class_source eq $default_source );
            push @authorised_values, $class_source;
            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
        }
    } elsif ( $authorised_value eq "categorycode" ) {
        my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
        $sth->execute;
        while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
            push @authorised_values, $categorycode;
            $authorised_lib{$categorycode} = $description;
        }

        #---- "true" authorised value
    } else {
        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");

        $authorised_values_sth->execute($authorised_value);

        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
            push @authorised_values, $value;
            $authorised_lib{$value} = $lib;

            # For item location, we show the code and the libelle
            $authorised_lib{$value} = $lib;
        }
    }

    return (\@authorised_values, \%authorised_lib);
}

1;
__END__





exit 0;

sub default_form {
    # build categories list
    my $sth = $dbh->prepare("select distinct category from authorised_values");
    $sth->execute;
    my @category_list;
    my %categories;    # a hash, to check that some hardcoded categories exist.
    while ( my ($category) = $sth->fetchrow_array ) {
        push( @category_list, $category );
        $categories{$category} = 1;
    }

    # push koha system categories
    foreach (qw(Asort1 Asort2 Bsort1 Bsort2 SUGGEST DAMAGED LOST REPORT_GROUP REPORT_SUBGROUP)) {
        push @category_list, $_ unless $categories{$_};
    }


Lines 97-103 CREATE TABLE `auth_types` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-3 / +8 lines)
97	DROP TABLE IF EXISTS `authorised_values`;	97	DROP TABLE IF EXISTS `authorised_values`;
98	CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values	98	CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values
99	`id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value	99	`id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value
100	`category` varchar(10) NOT NULL default '', -- key used to identify the authorized value category	100	`category` varchar(16) NOT NULL default '', -- key used to identify the authorized value category
101	`authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value	101	`authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value
102	`lib` varchar(80) default NULL, -- authorized value description as printed in the staff client	102	`lib` varchar(80) default NULL, -- authorized value description as printed in the staff client
103	`lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC	103	`lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC
Lines 1626-1633 CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for Link Here
1626	`date_created` datetime default NULL, -- date and time this definition was created	1626	`date_created` datetime default NULL, -- date and time this definition was created
1627	`date_modified` datetime default NULL, -- date and time this definition was last modified	1627	`date_modified` datetime default NULL, -- date and time this definition was last modified
1628	`saved_sql` text, -- SQL snippet for us in reports	1628	`saved_sql` text, -- SQL snippet for us in reports
1629	`area` int(11) default NULL, -- Koha module this definition is for (1 = Circulation, 2 = Catalog, 3 = Patrons, 4 = Acquistions, 5 = Accounts)	1629	report_area varchar(6) DEFAULT NULL, -- Koha module this definition is for Circulation, Catalog, Patrons, Acquistions, Accounts)
1630	PRIMARY KEY (`id`)	1630	PRIMARY KEY (id),
		1631	KEY dictionary_area_idx (report_area)
1631	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	1632	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1632		1633
1633	--	1634	--
Lines 1725-1731 CREATE TABLE saved_sql ( -- saved sql reports Link Here
1725	`notes` text, -- the notes or description given to this report	1726	`notes` text, -- the notes or description given to this report
1726	`cache_expiry` int NOT NULL default 300,	1727	`cache_expiry` int NOT NULL default 300,
1727	`public` boolean NOT NULL default FALSE,	1728	`public` boolean NOT NULL default FALSE,
		1729	report_area varchar(6) default NULL,
		1730	report_group varchar(80) default NULL,
		1731	report_subgroup varchar(80) default NULL,
1728	PRIMARY KEY (`id`),	1732	PRIMARY KEY (`id`),
		1733	KEY sql_area_group_idx (report_group, report_subgroup),
1729	KEY boridx (`borrowernumber`)	1734	KEY boridx (`borrowernumber`)
1730	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	1735	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1731		1736




    SetVersion($DBversion);
}



$DBversion = "3.09.00.XXX";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
    $dbh->do("ALTER TABLE authorised_values MODIFY category varchar(16) NOT NULL DEFAULT '';");
    $dbh->do("INSERT INTO authorised_values (category, authorised_value, lib) VALUES
              ('REPORT_GROUP', 'CIRC', 'Circulation'),
              ('REPORT_GROUP', 'CAT', 'Catalog'),
              ('REPORT_GROUP', 'PAT', 'Patrons'),
              ('REPORT_GROUP', 'ACQ', 'Acquisitions'),
              ('REPORT_GROUP', 'ACC', 'Accounts');");

    $dbh->do("ALTER TABLE reports_dictionary ADD report_area varchar(6) DEFAULT NULL;");
    $dbh->do("UPDATE reports_dictionary SET report_area = CASE area
                  WHEN 1 THEN 'CIRC'
                  WHEN 2 THEN 'CAT'
                  WHEN 3 THEN 'PAT'
                  WHEN 4 THEN 'ACQ'
                  WHEN 5 THEN 'ACC'
                  END;");
    $dbh->do("ALTER TABLE reports_dictionary DROP area;");
    $dbh->do("ALTER TABLE reports_dictionary ADD KEY dictionary_area_idx (report_area);");

    $dbh->do("ALTER TABLE saved_sql ADD report_area varchar(6) DEFAULT NULL;");
    $dbh->do("ALTER TABLE saved_sql ADD report_group varchar(80) DEFAULT NULL;");
    $dbh->do("ALTER TABLE saved_sql ADD report_subgroup varchar(80) DEFAULT NULL;");
    $dbh->do("ALTER TABLE saved_sql ADD KEY sql_area_group_idx (report_group, report_subgroup);");

    print "Upgrade to $DBversion done saved_sql new fields report_group and report_area; authorised_values.category 16 char \n";
    SetVersion($DBversion);
}

=head1 FUNCTIONS

=head2 TableExists($table)

Lines 33-39 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/dictionary.tt (-2 / +2 lines)
33	<form action="/cgi-bin/koha/reports/dictionary.pl" method="post">	33	<form action="/cgi-bin/koha/reports/dictionary.pl" method="post">
34	<input type="hidden" name="phase" value="View Dictionary" />	34	<input type="hidden" name="phase" value="View Dictionary" />
35	[% IF ( areas ) %]	35	[% IF ( areas ) %]
36	Filter by area <select name="areas">	36	Filter by area <select name="area">
37	<option value="">All</option>	37	<option value="">All</option>
38	[% FOREACH area IN areas %]	38	[% FOREACH area IN areas %]
39	[% IF ( area.selected ) %]	39	[% IF ( area.selected ) %]
Lines 103-109 Link Here
103	<ol><li><input type="hidden" name="phase" value="New Term step 3" />	103	<ol><li><input type="hidden" name="phase" value="New Term step 3" />
104	<input type="hidden" name="definition_name" value="[% definition_name %]" />	104	<input type="hidden" name="definition_name" value="[% definition_name %]" />
105	<input type="hidden" name="definition_description" value="[% definition_description %]" />	105	<input type="hidden" name="definition_description" value="[% definition_description %]" />
106	<label for="areas">Select table </label><select name="areas" id="areas">	106	<label for="area">Select table </label><select name="area" id="area">
107	[% FOREACH area IN areas %]	107	[% FOREACH area IN areas %]
108	<option value="[% area.id %]">[% area.name %]</option>	108	<option value="[% area.id %]">[% area.name %]</option>
109	[% END %]	109	[% END %]





<script type="text/javascript">
//<![CDATA[
var group_subgroups = {};
var no_subgroup_label = _( "(None)" );
function load_group_subgroups () {
    var group = $("#group").val();
    var sg = $("#subgroup");
    var has_subgroups = false;
    $(sg).empty().append('<option value="">' + no_subgroup_label + '</option>');
    if (group) {
        $.each( group_subgroups[group], function(index, value) {
                has_subgroups = true;
            $('<option value="' + value[0] + '">' + value[1] + '</option>').appendTo(sg);
        } );
    }
    if (has_subgroups) {
        $(sg).show();
    } else {
        $(sg).hide();
    }
}
$(document).ready(function(){
[% IF ( showsql ) %]
    $("#sql").focus(function() {

  <th>ID</th>
  <th>Report name</th>
  <th>Type</th>
  <th>Area</th>
  <th>Group</th>
  <th>Subgroup</th>
  <th>Notes</th>
  <th>Author</th>
  <th>Creation date</th>

<td>[% savedreport.id %]</td>
<td>[% savedreport.report_name %]</td>
<td>[% savedreport.type %]</td>
<td>[% savedreport.areaname %]</td>
<td>[% savedreport.groupname %]</td>
<td>[% savedreport.subgroupname %]</td>
<td>[% savedreport.notes %]</td>
<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
<td>[% savedreport.date_created %]</td>

<form action="/cgi-bin/koha/reports/guided_reports.pl">
<fieldset class="rows">
<legend>Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
<ol><li><label for="area">Choose: </label><select name="area" id="area">
[% FOREACH area IN areas %]
<option value="[% area.id %]">[% area.name %]</option>
[% END %]

<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
<input type="hidden" name="sql" value="[% sql |html %]" />
<input type="hidden" name="type" value="[% type %]" />
<input type="hidden" name="area" value="[% area %]" />
<input type="hidden" name="public" value="[% public %]" />
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
<fieldset class="rows">
<legend>Save your custom report</legend>
<ol>
    <li><label for="reportname">Report name: </label><input type="text" id="reportname" name="reportname" /></li>
    [% IF groups_with_subgroups %]
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
        [% FOR g IN groups_with_subgroups %]
            [% IF g.selected %]
    <option value="[% g.id %]" selected>[% g.name %]</option>
            [% ELSE %]
    <option value="[% g.id %]">[% g.name %]</option>
            [% END %]
    <script type="text/javascript">
        var g_sg = new Array();
            [% FOR sg IN g.subgroups %]
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
                [% IF sg.selected %]
        $(document).ready(function() {
            $("#subgroup").val("[% sg.id %]");
        });
                [% END %]
            [% END %]
        group_subgroups["[% g.id %]"] = g_sg;
    </script>
        [% END %]
    </select></li>
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
    </select></li>
    [% END %]
    <li><label for="notes">Notes:</label> <textarea name="notes" id="notes"></textarea></li>
</ol></fieldset>
<fieldset class="action"><input type="hidden" name="phase" value="Save Report" />

[% END %]

[% IF ( create ) %]
<script type="text/javascript">
$(document).ready(function() {
    load_group_subgroups();
});
</script>
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
<fieldset class="rows">
<legend>Create report from SQL</legend>

        [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
    </li>
    [% IF groups_with_subgroups %]
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
        [% FOR g IN groups_with_subgroups %]
            [% IF g.selected %]
    <option value="[% g.id %]" selected>[% g.name %]</option>
            [% ELSE %]
    <option value="[% g.id %]">[% g.name %]</option>
            [% END %]
    <script type="text/javascript">
        var g_sg = new Array();
            [% FOR sg IN g.subgroups %]
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
                [% IF sg.selected %]
        $(document).ready(function() {
            $("#subgroup").val("[% sg.id %]");
        });
                [% END %]
            [% END %]
        group_subgroups["[% g.id %]"] = g_sg;
    </script>
        [% END %]
    </select></li>
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
    </select></li>
    [% END %]
[% IF (public) %]
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
[% ELSE %]

[% END %]

[% IF ( editsql ) %]
<script type="text/javascript">
$(document).ready(function() {
    load_group_subgroups();
});
</script>
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
<input type="hidden" name="phase" value="Update SQL" />
<input type="hidden" name="id" value="[% id %]"/>

<legend>Edit SQL report</legend>
<ol>
<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>
    [% IF groups_with_subgroups %]
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
        [% FOR g IN groups_with_subgroups %]
            [% IF g.selected %]
    <option value="[% g.id %]" selected>[% g.name %]</option>
            [% ELSE %]
    <option value="[% g.id %]">[% g.name %]</option>
            [% END %]
    <script type="text/javascript">
        var g_sg = new Array();
            [% FOR sg IN g.subgroups %]
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
                [% IF sg.selected %]
        $(document).ready(function() {
            $("#subgroup").val("[% sg.id %]");
        });
                [% END %]
            [% END %]
        group_subgroups["[% g.id %]"] = g_sg;
    </script>
        [% END %]
    </select></li>
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
    </select></li>
    [% END %]
[% IF (public) %]
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
[% ELSE %]


[% IF ( saved1 ) %]
<div id="saved-reports-filter">
<script type="text/javascript">
$(document).ready(function() {
    no_subgroup_label = _( "-- All --" );
    load_group_subgroups();
});
</script>
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="get">
  <input type="hidden" name="phase" value="Use saved" />
  <input type="hidden" name="filter_set" value="1" />
  <fieldset class="brief">
  <h3>Filter</h3>
  <ol>
    <li><label for="group">Choose Group and Subgroup: </label>
    <select name="group" id="group" onChange="load_group_subgroups();">
        <option value="">-- All --</option>
    [% FOR g IN groups_with_subgroups %]
        [% IF g.selected %]
        <option value="[% g.id %]" selected>[% g.name %]</option>
        [% ELSE %]
        <option value="[% g.id %]">[% g.name %]</option>
        [% END %]
        <script type="text/javascript">
            var g_sg = new Array();
        [% FOR sg IN g.subgroups %]
            g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
            [% IF sg.selected %]
            $(document).ready(function() {
                $("#subgroup").val("[% sg.id %]");
            });
            [% END %]
        [% END %]
            group_subgroups["[% g.id %]"] = g_sg;
        </script>
    [% END %]
    </select>
    <select name="subgroup" id="subgroup"></select>
    </li>
    <li><label for="filter_date">Date:</label> <input type="text" id="filter_date" name="filter_date" size="10" value="[% filter_date %]" class="datepicker" />
    <div class="hint">[% INCLUDE 'date-format.inc' %]</div>


Lines 186-199 unless (scalar(@ARGV)) { Link Here

(-)a/misc/cronjobs/runreport.pl (-5 / +17 lines)
186	($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";	186	($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
187		187
188		188
189	foreach my $report (@ARGV) {	189	foreach my $report_id (@ARGV) {
190	my ($sql, $type) = get_saved_report($report);	190	my $report = get_saved_report($report_id);
191	unless ($sql) {	191	unless ($report) {
192	carp "ERROR: No saved report $report found";	192	warn "ERROR: No saved report $report_id found";
193	next;	193	next;
194	}	194	}
		195	my $sql => $report->{savedsql};
		196	my $report_name => $report->{report_name};
		197	my $type => $report->{type};
		198
195	$verbose and print "SQL: $sql\n\n";	199	$verbose and print "SQL: $sql\n\n";
196	# my $results = execute_query($sql, undef, 0, 99999, $format, $report);	200	if (defined($report_name) and $report_name ne "")
		201	{
		202	$subject = $report_name ;
		203	}
		204	else
		205	{
		206	$subject = 'Koha Saved Report';
		207	}
		208	# my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
197	my ($sth) = execute_query($sql);	209	my ($sth) = execute_query($sql);
198	# execute_query(sql, , 0, 20, , )	210	# execute_query(sql, , 0, 20, , )
199	my $count = scalar($sth->rows);	211	my $count = scalar($sth->rows);




my $report_id = $query->param('id');
my $report_name = $query->param('name');

my $report_rec = get_saved_report( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
die "Sorry this report is not public\n" unless $report_rec->{public};
my $type;
my $notes;
my $cache_expiry;
my $public;

( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
  get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
die "Sorry this report is not public\n" unless $public;

my $cache_active = Koha::Cache->is_cache_active;
my ($cache_key, $cache, $json_text);
if ($cache_active) {
    $cache_key = "opac:report:".($report_name ? "name:$report_name" : "id:$report_id");
    $cache = Koha::Cache->new();
    $json_text = $cache->get_from_cache($cache_key);
        print $page;
        exit;
    }
}

unless ($json_text) {
if ($sql) {
    my $offset = 0;
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
    my ( $sth, $errors ) = execute_query( $report_rec->{savedsql}, $offset, $limit );
    if ($sth) {
        my $lines     = $sth->fetchall_arrayref;
        $json_text = to_json($lines);

        if ($cache_active) {
            $cache->set_in_cache( $cache_key, $json_text, $report_rec->{cache_expiry} );
        }
    }
    else {
        $json_text = to_json($errors);
    }
}

print $query->header;
print $json_text;




my $referer = $input->referer();

my $phase = $input->param('phase') || 'View Dictionary';
my $definition_name        = $input->param('definition_name');
my $definition_description = $input->param('definition_description');
my $area  = $input->param('area') || '';
my $no_html = 0;    # this will be set if we dont want to print out an html::template
my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
    {   template_name   => "reports/dictionary.tmpl",
        query           => $input,
        type            => "intranet",
        authnotrequired => 0,

	);

if ($phase eq 'View Dictionary'){
    # view the dictionary we use to set up abstract variables such as all borrowers over fifty who live in a certain town
    my $definitions = get_from_dictionary($area);
    $template->param(
        'areas'=> areas(),
        'start_dictionary' => 1,
        'definitions'      => $definitions,
    );
} elsif ( $phase eq 'Add New Definition' ) {

    # display form allowing them to add a new definition
    $template->param( 'new_dictionary' => 1, );
elsif ($phase eq 'Add New Definition'){
	# display form allowing them to add a new definition
	$template->param( 'new_dictionary' => 1,
		);
}

elsif ( $phase eq 'New Term step 2' ) {

    # Choosing the area
    $template->param(
        'step_2'                 => 1,
        'areas'                  => areas(),
        'definition_name'        => $definition_name,
        'definition_description' => $definition_description,
    );
	);
}

elsif ( $phase eq 'New Term step 3' ) {

    # Choosing the columns
    my $columns                = get_columns( $area, $input );
    $template->param(
        'step_3'                 => 1,
        'area'                   => $area,
        'columns'                => $columns,
        'definition_name'        => $definition_name,
        'definition_description' => $definition_description,
    );
	);
}

elsif ( $phase eq 'New Term step 4' ) {

    # Choosing the values
    my @columns                = $input->param('columns');
    my $columnstring           = join( ',', @columns );
    my @column_loop;
    foreach my $column (@columns) {
        my %tmp_hash;
        $tmp_hash{'name'} = $column;
        my $type = get_column_type($column);
        if ( $type eq 'distinct' ) {
            my $values = get_distinct_values($column);
            $tmp_hash{'values'}   = $values;
            $tmp_hash{'distinct'} = 1;

        }
        if ( $type eq 'DATE' || $type eq 'DATETIME' ) {
            $tmp_hash{'date'} = 1;
        }
        if ( $type eq 'TEXT' ) {
            $tmp_hash{'text'} = 1;
        }

        #		else {
        #			warn $type;#
        #			}
        push @column_loop, \%tmp_hash;
    }
		}

	$template->param( 'step_4' => 1,
		'area' => $area,

	);
}

elsif ( $phase eq 'New Term step 5' ) {
    # Confirmation screen
    my $columnstring           = $input->param('columnstring');
    my @criteria               = $input->param('criteria_column');
    my $query_criteria;
    my @criteria_loop;

    foreach my $crit (@criteria) {
        my $value = $input->param( $crit . "_value" );
        if ($value) {
            my %tmp_hash;
            $tmp_hash{'name'}  = $crit;
            $tmp_hash{'value'} = $value;
            push @criteria_loop, \%tmp_hash;
            if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
                my $date = C4::Dates->new($value);
                $value = $date->output("iso");
            }
            $query_criteria .= " AND $crit='$value'";
        }
        $value = $input->param( $crit . "_start_value" );
        if ($value) {
            my %tmp_hash;
            $tmp_hash{'name'}  = "$crit Start";
            $tmp_hash{'value'} = $value;
            push @criteria_loop, \%tmp_hash;
            if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
                my $date = C4::Dates->new($value);
                $value = $date->output("iso");
            }
            $query_criteria .= " AND $crit >= '$value'";
        }
        $value = $input->param( $crit . "_end_value" );
        if ($value) {
            my %tmp_hash;
            $tmp_hash{'name'}  = "$crit End";
            $tmp_hash{'value'} = $value;
            push @criteria_loop, \%tmp_hash;
            if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
                my $date = C4::Dates->new($value);
                $value = $date->output("iso");
            }
            $query_criteria .= " AND $crit <= '$value'";
        }
    }
    my %report_areas = map @$_, get_report_areas();
    $template->param(
        'step_5'                 => 1,
        'area'                   => $area,
        'areaname'               => $report_areas{$area},
        'definition_name'        => $definition_name,
        'definition_description' => $definition_description,
        'query'                  => $query_criteria,
        'columnstring'           => $columnstring,
        'criteria_loop'          => \@criteria_loop,
    );
		'criteria_loop' => \@criteria_loop,
	);
}

elsif ( $phase eq 'New Term step 6' ) {
    # Saving
    my $area                   = $input->param('area');
    my $sql                    = $input->param('sql');
    save_dictionary( $definition_name, $definition_description, $sql, $area );
    $no_html = 1;
    print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");

} elsif ( $phase eq 'Delete Definition' ) {
    $no_html = 1;
    my $id = $input->param('id');
    delete_definition($id);
    print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");
}
elsif ($phase eq 'Delete Definition'){
	$no_html=1;
	my $id = $input->param('id');
	delete_definition($id);
	print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");
	}

$template->param( 'referer' => $referer );


if (!$no_html){
	output_html_with_http_headers $input, $cookie, $template->output;
}

sub areas {
    my $areas = get_report_areas();
    my @a;
    foreach (@$areas) {
        push @a, {
            id => $_->[0],
            name => $_->[1],
            selected => ($_->[0] eq $area),
        };
    }
    return \@a;
}




# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

use strict;
use warnings;

use CGI;
use Text::CSV;
use URI::Escape;
use C4::Reports::Guided;
use C4::Auth qw/:DEFAULT get_session/;
use C4::Output;
use C4::Dates qw/format_date/;
use C4::Debug;
use C4::Branch; # XXX subfield_is_koha_internal_p
use Koha::Cache;

my $filter;
if ( $input->param("filter_set") ) {
    $filter = {};
    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/;
    $session->param('report_filter', $filter) if $session;
    $template->param( 'filter_set' => 1 );
}

elsif ( $phase eq 'Build new' ) {
    # build a new report
    $template->param( 'build1' => 1 );
    my $areas = get_report_areas();
    $template->param(
        'areas' => [map { id => $_->[0], name => $_->[1] }, @$areas],
        'usecache' => $usecache,
        'cache_expiry' => 300,
        'public' => '0',
    );
} elsif ( $phase eq 'Use saved' ) {

    # use a saved report
    # get list of reports and display them
    my $group = $input->param('group');
    my $subgroup = $input->param('subgroup');
    $filter->{group} = $group;
    $filter->{subgroup} = $subgroup;
    $template->param(
        'saved1' => 1,
        'savedreports' => get_saved_reports($filter),
        'usecache' => $usecache,
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
    );
    if ($filter) {
        while ( my ($k, $v) = each %$filter ) {
            $template->param( "filter_$k" => $v ) if $v;
        }
    }
}

elsif ( $phase eq 'Delete Saved') {


elsif ( $phase eq 'Show SQL'){
	
    my $id = $input->param('reports');
    my $report = get_saved_report($id);
    $template->param(
        'id'      => $id,
        'reportname' => $report->{report_name},
        'notes'      => $report->{notes},
	'sql'     => $report->{savedsql},
	'showsql' => 1,
    );
}

elsif ( $phase eq 'Edit SQL'){
	
    my $id = $input->param('reports');
    my $report = get_saved_report($id);
    my $group = $report->{report_group};
    my $subgroup  = $report->{report_subgroup};
    $template->param(
        'sql'        => $report->{savedsql},
        'reportname' => $report->{report_name},
        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
        'notes'      => $report->{notes},
        'id'         => $id,
        'cache_expiry' => $report->{cache_expiry},
        'public' => $report->{public},
        'usecache' => $usecache,
        'editsql'    => 1,
    );
}


    my $id         = $input->param('id');
    my $sql        = $input->param('sql');
    my $reportname = $input->param('reportname');
    my $group      = $input->param('group');
    my $subgroup   = $input->param('subgroup');
    my $notes      = $input->param('notes');
    my $cache_expiry = $input->param('cache_expiry');
    my $cache_expiry_units = $input->param('cache_expiry_units');

            'errors'    => \@errors,
            'sql'       => $sql,
        );
    } else {
        update_sql( $id, {
                sql => $sql,
                name => $reportname,
                group => $group,
                subgroup => $subgroup,
                notes => $notes,
                cache_expiry => $cache_expiry,
                public => $public,
        } );
        $template->param(
            'save_successful'       => 1,
            'reportname'            => $reportname,
            'id'                    => $id,
        );
    }
    
}

elsif ($phase eq 'retrieve results') {

      # they have choosen a new report and the area to report on
      $template->param(
          'build2' => 1,
          'area'   => $input->param('area'),
          'types'  => get_report_types(),
          'cache_expiry' => $cache_expiry,
          'public' => $input->param('public'),

    my $area     = $input->param('area');
    my $type     = $input->param('type');
    my $column   = $input->param('column');
    my @definitions = $input->param('definition');
    my $definition = join (',',@definitions);
    my @criteria = $input->param('criteria_column');
    my $query_criteria;
    foreach my $crit (@criteria) {
        my $value = $input->param( $crit . "_value" );

        # If value is not defined, then it may be range values
        if (!defined $value) {

            my $fromvalue = $input->param( "from_" . $crit . "_value" );
            my $tovalue   = $input->param( "to_"   . $crit . "_value" );

            # If the range values are dates
            if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { 
                $fromvalue = C4::Dates->new($fromvalue)->output("iso");
                $tovalue = C4::Dates->new($tovalue)->output("iso");
            }

            if ($fromvalue && $tovalue) {
                $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
            }

	} else {

	    # If value is a date
	    if ($value =~ C4::Dates->regexp('syspref')) { 
		$value = C4::Dates->new($value)->output("iso");
	    }
        # don't escape runtime parameters, they'll be at runtime
        if ($value =~ /<<.*>>/) {
            $query_criteria .= " AND $crit=$value";
        } else {

            # If value is a date
            if ($value =~ C4::Dates->regexp('syspref')) { 
                $value = C4::Dates->new($value)->output("iso");
            }
            # don't escape runtime parameters, they'll be at runtime
            if ($value =~ /<<.*>>/) {
                $query_criteria .= " AND $crit=$value";
            } else {
                $query_criteria .= " AND $crit='$value'";
            }
        }
	}
    }
    $template->param(
        'build5'         => 1,

      build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
    $template->param(
        'showreport' => 1,
        'area'       => $area,
        'sql'        => $sql,
        'type'       => $type,
        'cache_expiry' => $input->param('cache_expiry'),

}

elsif ( $phase eq 'Save' ) {
    # Save the report that has just been built
    my $area           = $input->param('area');
    my $sql  = $input->param('sql');
    my $type = $input->param('type');
    $template->param(
        'save' => 1,
        'area'  => $area,
        'sql'  => $sql,
        'type' => $type,
        'cache_expiry' => $input->param('cache_expiry'),
        'public' => $input->param('public'),
        'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area
    );
}

elsif ( $phase eq 'Save Report' ) {
    # save the sql pasted in by a user
    my $area  = $input->param('area');
    my $group = $input->param('group');
    my $subgroup = $input->param('subgroup');
    my $sql   = $input->param('sql');
    my $name  = $input->param('reportname');
    my $type  = $input->param('types');
    my $notes = $input->param('notes');
    my $cache_expiry = $input->param('cache_expiry');
    my $cache_expiry_units = $input->param('cache_expiry_units');

      }
    }
    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
    if( $cache_expiry && $cache_expiry >= 2592000 ){
      push @errors, {cache_expiry => $cache_expiry};
    }
    ## FIXME this is AFTER entering a name to save the report under

        push @errors, {sqlerr => $1};
    }
    elsif ($sql !~ /^(SELECT)/i) {
        push @errors, {queryerr => "No SELECT"};
    }
    if (@errors) {
        $template->param(

        );
    }
    else {
        save_report( {
                borrowernumber => $borrowernumber,
                sql            => $sql,
                name           => $name,
                area           => $area,
                group          => $group,
                subgroup       => $subgroup,
                type           => $type,
                notes          => $notes,
                cache_expiry   => $cache_expiry,
                public         => $public,
            } );
        $template->param( 'save_successful' => 1, );
    }
}

elsif ($phase eq 'Run this report'){
    # execute a saved report
    my $limit      = 20; # page size. # TODO: move to DB or syspref?
    my $offset     = 0;
    my $report_id  = $input->param('reports');
    my @sql_params = $input->param('sql_params');
    # offset algorithm
    if ($input->param('page')) {
        $offset = ($input->param('page') - 1) * $limit;
    }

    my ( $sql, $type, $name, $notes );
    if (my $report = get_saved_report($report_id)) {
        $sql   = $report->{savedsql};
        $name  = $report->{report_name};
        $notes = $report->{notes};

        my @rows = ();
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
        if ($sql =~ /<</ && !@sql_params) {
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
            my @split = split /<<|>>/,$sql;
            my @tmpl_parameters;
            for(my $i=0;$i<($#split/2);$i++) {
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
                my $input;
                my $labelid;
                if ($authorised_value eq "date") {
                   $input = 'date';
                my @authorised_values;
                my %authorised_lib;
                # builds list, depending on authorised value...
                if ( $authorised_value eq "branches" ) {
                    my $branches = GetBranchesLoop();
                    foreach my $thisbranch (@$branches) {
                        push @authorised_values, $thisbranch->{value};
                        $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
                    }
                }
                elsif ($authorised_value) {
                    my $dbh=C4::Context->dbh;
                    my @authorised_values;
                    my %authorised_lib;
                    # builds list, depending on authorised value...
                    if ( $authorised_value eq "branches" ) {
                        my $branches = GetBranchesLoop();
                        foreach my $thisbranch (@$branches) {
                            push @authorised_values, $thisbranch->{value};
                            $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
                        }
                    }
                    elsif ( $authorised_value eq "itemtypes" ) {
                        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
                        $sth->execute;
                        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
                            push @authorised_values, $itemtype;
                            $authorised_lib{$itemtype} = $description;
                        }
                        push @authorised_values, $class_source;
                        $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
                    }
                    elsif ( $authorised_value eq "cn_source" ) {
                        my $class_sources = GetClassSources();
                        my $default_source = C4::Context->preference("DefaultClassificationSource");
                        foreach my $class_source (sort keys %$class_sources) {
                            next unless $class_sources->{$class_source}->{'used'} or
                                        ($class_source eq $default_source);
                            push @authorised_values, $class_source;
                            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
                        }
                    }
                    elsif ( $authorised_value eq "categorycode" ) {
                        my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
                        $sth->execute;
                        while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
                            push @authorised_values, $categorycode;
                            $authorised_lib{$categorycode} = $description;
                        }

                        #---- "true" authorised value
                    }
                    else {
                        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");

                        $authorised_values_sth->execute( $authorised_value);
                }
                else {
                    my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");

                    $authorised_values_sth->execute( $authorised_value);

                        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
                            push @authorised_values, $value;
                            $authorised_lib{$value} = $lib;
                            # For item location, we show the code and the libelle
                            $authorised_lib{$value} = $lib;
                        }
                    }
                    $labelid = $text;
                    $labelid =~ s/\W//g;
                    $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
                        -name     => "sql_params",
                        -id       => "sql_params_".$labelid,
                        -values   => \@authorised_values,
                    -values   => \@authorised_values,
#                     -default  => $value,
                        -labels   => \%authorised_lib,
                        -override => 1,
                        -size     => 1,
                        -multiple => 0,
                        -tabindex => 1,
                    );
                } else {
                    $input = "text";
                }
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
            }
            $template->param('sql'         => $sql,
                            'name'         => $name,
                            'sql_params'   => \@tmpl_parameters,
                            'enter_params' => 1,
                            'reports'      => $report_id,
                            );
                        'reports'      => $report,
                        );
    } else {
        # OK, we have parameters, or there are none, we run the report
        # if there were parameters, replace before running
        # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
        my @split = split /<<|>>/,$sql;
        my @tmpl_parameters;
        for(my $i=0;$i<$#split/2;$i++) {
            my $quoted = C4::Context->dbh->quote($sql_params[$i]);
            # if there are special regexp chars, we must \ them
            $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
        }
        my ($sth, $errors) = execute_query($sql, $offset, $limit);
        my $total = nb_rows($sql) || 0;
        unless ($sth) {
            die "execute_query failed to return sth for report $report: $sql";
        } else {
            # OK, we have parameters, or there are none, we run the report
            # if there were parameters, replace before running
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
            my @split = split /<<|>>/,$sql;
            my @tmpl_parameters;
            for(my $i=0;$i<$#split/2;$i++) {
                my $quoted = C4::Context->dbh->quote($sql_params[$i]);
                # if there are special regexp chars, we must \ them
                $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
                $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
            }
            my ($sth, $errors) = execute_query($sql, $offset, $limit);
            my $total = nb_rows($sql) || 0;
            unless ($sth) {
                die "execute_query failed to return sth for report $report_id: $sql";
            } else {
                my $headref = $sth->{NAME} || [];
                my @headers = map { +{ cell => $_ } } @$headref;
                $template->param(header_row => \@headers);
                while (my $row = $sth->fetchrow_arrayref()) {
                    my @cells = map { +{ cell => $_ } } @$row;
                    push @rows, { cells => \@cells };
                }
            }
        }

            my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
            my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&amp;phase=Run%20this%20report";
            if (@sql_params) {
                $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
            }
            $template->param(
                'results' => \@rows,
                'sql'     => $sql,
                'id'      => $report_id,
                'execute' => 1,
                'name'    => $name,
                'notes'   => $notes,
                'errors'  => $errors,
                'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
                'unlimited_total' => $total,
            );
        }
    }
    else {
        push @errors, { no_sql_for_id => $report_id };
            'id'      => $report,
            'execute' => 1,
            'name'    => $name,
            'notes'   => $notes,
            'errors'  => $errors,
            'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
            'unlimited_total' => $total,
        );
    }
}


    );
}

elsif ( $phase eq 'Create report from SQL' ) {

    my ($group, $subgroup);
    # allow the user to paste in sql
    if ( $input->param('sql') ) {
        $group = $input->param('report_group');
        $subgroup  = $input->param('report_subgroup');
        $template->param(
            'sql'           => $input->param('sql'),
            'reportname'    => $input->param('reportname'),
            'notes'         => $input->param('notes'),
        );
    }
    $template->param(
        'create' => 1,
        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
        'public' => '0',
        'cache_expiry' => 300,
        'usecache' => $usecache,
    );
}

elsif ($phase eq 'Create Compound Report'){

                );

output_html_with_http_headers $input, $cookie, $template->output;

sub groups_with_subgroups {
    my ($group, $subgroup) = @_;

    my $groups_with_subgroups = get_report_groups();
    my @g_sg;
    while (my ($g_id, $v) = each %$groups_with_subgroups) {
        my @subgroups;
        if (my $sg = $v->{subgroups}) {
            while (my ($sg_id, $n) = each %$sg) {
                push @subgroups, {
                    id => $sg_id,
                    name => $n,
                    selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ),
                };
            }
        }
        push @g_sg, {
            id => $g_id,
            name => $v->{name},
            selected => ($group && $g_id eq $group),
            subgroups => \@subgroups,
        };
    }
    return \@g_sg;
}




my $report_id = $query->param('id');
my $report_name = $query->param('name');

my $cache;
my $sql;
my $type;
my $notes;
my $cache_expiry;
my $public;

my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
    {
        template_name   => "intranet-main.tmpl",

    }
);

my $cache_active = Koha::Cache->is_cache_active;
my ($cache_key, $cache, $json_text);
if ($cache_active) {
    $cache_key = "intranet:report:".($report_name ? "name:$report_name" : "id:$report_id");
        ( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
            get_saved_report( { 'name' => $report_name } );
    }

    $cache = Koha::Cache->new();
    $json_text = $cache->get_from_cache($cache_key);
    if ($page) {
        print $query->header;
        print $page;
        exit;
    }
}

unless ($json_text) {
    my $report_rec = get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id });
# $public isnt used for intranet
unless ($sql) {
    ( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
        get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
}
if ($sql) {
    my $offset = 0;
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
    my ( $sth, $errors ) = execute_query( $report_rec->{savedsql}, $offset, $limit );
    if ($sth) {
        my $lines     = $sth->fetchall_arrayref;
        $json_text = to_json($lines);

        if ($cache_active) {
            $cache->set_in_cache( $cache_key, $json_text, $report_rec->{cache_expiry} );
        }
    }
    else {
        $json_text = to_json($errors);
    }
}

print $query->header;
print $json_text;

Return to bug 7993

Lines 5696-5701 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+32 lines)
5696	SetVersion($DBversion);	5696	SetVersion($DBversion);
5697	}	5697	}
5698		5698
		5699
		5700
		5701	$DBversion = "3.09.00.XXX";
		5702	if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
		5703	$dbh->do("ALTER TABLE authorised_values MODIFY category varchar(16) NOT NULL DEFAULT '';");
		5704	$dbh->do("INSERT INTO authorised_values (category, authorised_value, lib) VALUES
		5705	('REPORT_GROUP', 'CIRC', 'Circulation'),
		5706	('REPORT_GROUP', 'CAT', 'Catalog'),
		5707	('REPORT_GROUP', 'PAT', 'Patrons'),
		5708	('REPORT_GROUP', 'ACQ', 'Acquisitions'),
		5709	('REPORT_GROUP', 'ACC', 'Accounts');");
		5710
		5711	$dbh->do("ALTER TABLE reports_dictionary ADD report_area varchar(6) DEFAULT NULL;");
		5712	$dbh->do("UPDATE reports_dictionary SET report_area = CASE area
		5713	WHEN 1 THEN 'CIRC'
		5714	WHEN 2 THEN 'CAT'
		5715	WHEN 3 THEN 'PAT'
		5716	WHEN 4 THEN 'ACQ'
		5717	WHEN 5 THEN 'ACC'
		5718	END;");
		5719	$dbh->do("ALTER TABLE reports_dictionary DROP area;");
		5720	$dbh->do("ALTER TABLE reports_dictionary ADD KEY dictionary_area_idx (report_area);");
		5721
		5722	$dbh->do("ALTER TABLE saved_sql ADD report_area varchar(6) DEFAULT NULL;");
		5723	$dbh->do("ALTER TABLE saved_sql ADD report_group varchar(80) DEFAULT NULL;");
		5724	$dbh->do("ALTER TABLE saved_sql ADD report_subgroup varchar(80) DEFAULT NULL;");
		5725	$dbh->do("ALTER TABLE saved_sql ADD KEY sql_area_group_idx (report_group, report_subgroup);");
		5726
		5727	print "Upgrade to $DBversion done saved_sql new fields report_group and report_area; authorised_values.category 16 char \n";
		5728	SetVersion($DBversion);
		5729	}
		5730
5699	=head1 FUNCTIONS	5731	=head1 FUNCTIONS
5700		5732
5701	=head2 TableExists($table)	5733	=head2 TableExists($table)

Lines 188-204 output_html_with_http_headers $input, $cookie, $template->output; Link Here

(-)a/admin/authorised_values.pl (-11 / +12 lines)
188	exit 0;	188	exit 0;
189		189
190	sub default_form {	190	sub default_form {
191	# build categories list	191	# build categories list
192	my $sth = $dbh->prepare("select distinct category from authorised_values");	192	my $sth = $dbh->prepare("select distinct category from authorised_values");
193	$sth->execute;	193	$sth->execute;
194	my @category_list;	194	my @category_list;
195	my %categories; # a hash, to check that some hardcoded categories exist.	195	my %categories; # a hash, to check that some hardcoded categories exist.
196	while ( my ($category) = $sth->fetchrow_array) {	196	while ( my ($category) = $sth->fetchrow_array ) {
197	push(@category_list,$category);	197	push( @category_list, $category );
198	$categories{$category} = 1;	198	$categories{$category} = 1;
199	}	199	}
200	# push koha system categories	200
201	foreach (qw(Asort1 Asort2 Bsort1 Bsort2 SUGGEST DAMAGED LOST)) {	201	# push koha system categories
		202	foreach (qw(Asort1 Asort2 Bsort1 Bsort2 SUGGEST DAMAGED LOST REPORT_GROUP REPORT_SUBGROUP)) {
202	push @category_list, $_ unless $categories{$_};	203	push @category_list, $_ unless $categories{$_};
203	}	204	}
204		205

Lines 24-29 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-1 / +143 lines)
24		24
25	<script type="text/javascript">	25	<script type="text/javascript">
26	//<![CDATA[	26	//<![CDATA[
		27	var group_subgroups = {};
		28	var no_subgroup_label = _( "(None)" );
		29	function load_group_subgroups () {
		30	var group = $("#group").val();
		31	var sg = $("#subgroup");
		32	var has_subgroups = false;
		33	$(sg).empty().append('<option value="">' + no_subgroup_label + '</option>');
		34	if (group) {
		35	$.each( group_subgroups[group], function(index, value) {
		36	has_subgroups = true;
		37	$('<option value="' + value[0] + '">' + value[1] + '</option>').appendTo(sg);
		38	} );
		39	}
		40	if (has_subgroups) {
		41	$(sg).show();
		42	} else {
		43	$(sg).hide();
		44	}
		45	}
27	$(document).ready(function(){	46	$(document).ready(function(){
28	[% IF ( showsql ) %]	47	[% IF ( showsql ) %]
29	$("#sql").focus(function() {	48	$("#sql").focus(function() {
Lines 138-143 canned reports and writing custom SQL reports.</p> Link Here
138	<th>ID</th>	157	<th>ID</th>
139	<th>Report name</th>	158	<th>Report name</th>
140	<th>Type</th>	159	<th>Type</th>
		160	<th>Area</th>
		161	<th>Group</th>
		162	<th>Subgroup</th>
141	<th>Notes</th>	163	<th>Notes</th>
142	<th>Author</th>	164	<th>Author</th>
143	<th>Creation date</th>	165	<th>Creation date</th>
Lines 155-160 canned reports and writing custom SQL reports.</p> Link Here
155	<td>[% savedreport.id %]</td>	177	<td>[% savedreport.id %]</td>
156	<td>[% savedreport.report_name %]</td>	178	<td>[% savedreport.report_name %]</td>
157	<td>[% savedreport.type %]</td>	179	<td>[% savedreport.type %]</td>
		180	<td>[% savedreport.areaname %]</td>
		181	<td>[% savedreport.groupname %]</td>
		182	<td>[% savedreport.subgroupname %]</td>
158	<td>[% savedreport.notes %]</td>	183	<td>[% savedreport.notes %]</td>
159	<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>	184	<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
160	<td>[% savedreport.date_created %]</td>	185	<td>[% savedreport.date_created %]</td>
Lines 219-225 canned reports and writing custom SQL reports.</p> Link Here
219	<form action="/cgi-bin/koha/reports/guided_reports.pl">	244	<form action="/cgi-bin/koha/reports/guided_reports.pl">
220	<fieldset class="rows">	245	<fieldset class="rows">
221	<legend>Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>	246	<legend>Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
222	<ol><li><label for="areas">Choose: </label><select name="areas" id="areas">	247	<ol><li><label for="area">Choose: </label><select name="area" id="area">
223	[% FOREACH area IN areas %]	248	[% FOREACH area IN areas %]
224	<option value="[% area.id %]">[% area.name %]</option>	249	<option value="[% area.id %]">[% area.name %]</option>
225	[% END %]	250	[% END %]
Lines 485-496 canned reports and writing custom SQL reports.</p> Link Here
485	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">	510	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
486	<input type="hidden" name="sql" value="[% sql \|html %]" />	511	<input type="hidden" name="sql" value="[% sql \|html %]" />
487	<input type="hidden" name="type" value="[% type %]" />	512	<input type="hidden" name="type" value="[% type %]" />
		513	<input type="hidden" name="area" value="[% area %]" />
488	<input type="hidden" name="public" value="[% public %]" />	514	<input type="hidden" name="public" value="[% public %]" />
489	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />	515	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
490	<fieldset class="rows">	516	<fieldset class="rows">
491	<legend>Save your custom report</legend>	517	<legend>Save your custom report</legend>
492	<ol>	518	<ol>
493	<li><label for="reportname">Report name: </label><input type="text" id="reportname" name="reportname" /></li>	519	<li><label for="reportname">Report name: </label><input type="text" id="reportname" name="reportname" /></li>
		520	[% IF groups_with_subgroups %]
		521	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
		522	[% FOR g IN groups_with_subgroups %]
		523	[% IF g.selected %]
		524	<option value="[% g.id %]" selected>[% g.name %]</option>
		525	[% ELSE %]
		526	<option value="[% g.id %]">[% g.name %]</option>
		527	[% END %]
		528	<script type="text/javascript">
		529	var g_sg = new Array();
		530	[% FOR sg IN g.subgroups %]
		531	g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
		532	[% IF sg.selected %]
		533	$(document).ready(function() {
		534	$("#subgroup").val("[% sg.id %]");
		535	});
		536	[% END %]
		537	[% END %]
		538	group_subgroups["[% g.id %]"] = g_sg;
		539	</script>
		540	[% END %]
		541	</select></li>
		542	<li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
		543	</select></li>
		544	[% END %]
494	<li><label for="notes">Notes:</label> <textarea name="notes" id="notes"></textarea></li>	545	<li><label for="notes">Notes:</label> <textarea name="notes" id="notes"></textarea></li>
495	</ol></fieldset>	546	</ol></fieldset>
496	<fieldset class="action"><input type="hidden" name="phase" value="Save Report" />	547	<fieldset class="action"><input type="hidden" name="phase" value="Save Report" />
Lines 553-558 canned reports and writing custom SQL reports.</p> Link Here
553	[% END %]	604	[% END %]
554		605
555	[% IF ( create ) %]	606	[% IF ( create ) %]
		607	<script type="text/javascript">
		608	$(document).ready(function() {
		609	load_group_subgroups();
		610	});
		611	</script>
556	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">	612	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
557	<fieldset class="rows">	613	<fieldset class="rows">
558	<legend>Create report from SQL</legend>	614	<legend>Create report from SQL</legend>
Lines 561-566 canned reports and writing custom SQL reports.</p> Link Here
561	[% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />	617	[% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
562	[% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %]	618	[% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %]
563	</li>	619	</li>
		620	[% IF groups_with_subgroups %]
		621	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
		622	[% FOR g IN groups_with_subgroups %]
		623	[% IF g.selected %]
		624	<option value="[% g.id %]" selected>[% g.name %]</option>
		625	[% ELSE %]
		626	<option value="[% g.id %]">[% g.name %]</option>
		627	[% END %]
		628	<script type="text/javascript">
		629	var g_sg = new Array();
		630	[% FOR sg IN g.subgroups %]
		631	g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
		632	[% IF sg.selected %]
		633	$(document).ready(function() {
		634	$("#subgroup").val("[% sg.id %]");
		635	});
		636	[% END %]
		637	[% END %]
		638	group_subgroups["[% g.id %]"] = g_sg;
		639	</script>
		640	[% END %]
		641	</select></li>
		642	<li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
		643	</select></li>
		644	[% END %]
564	[% IF (public) %]	645	[% IF (public) %]
565	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>	646	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
566	[% ELSE %]	647	[% ELSE %]
Lines 645-650 Sub report:<select name="subreport"> Link Here
645	[% END %]	726	[% END %]
646		727
647	[% IF ( editsql ) %]	728	[% IF ( editsql ) %]
		729	<script type="text/javascript">
		730	$(document).ready(function() {
		731	load_group_subgroups();
		732	});
		733	</script>
648	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">	734	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
649	<input type="hidden" name="phase" value="Update SQL" />	735	<input type="hidden" name="phase" value="Update SQL" />
650	<input type="hidden" name="id" value="[% id %]"/>	736	<input type="hidden" name="id" value="[% id %]"/>
Lines 652-657 Sub report:<select name="subreport"> Link Here
652	<legend>Edit SQL report</legend>	738	<legend>Edit SQL report</legend>
653	<ol>	739	<ol>
654	<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>	740	<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>
		741	[% IF groups_with_subgroups %]
		742	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
		743	[% FOR g IN groups_with_subgroups %]
		744	[% IF g.selected %]
		745	<option value="[% g.id %]" selected>[% g.name %]</option>
		746	[% ELSE %]
		747	<option value="[% g.id %]">[% g.name %]</option>
		748	[% END %]
		749	<script type="text/javascript">
		750	var g_sg = new Array();
		751	[% FOR sg IN g.subgroups %]
		752	g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
		753	[% IF sg.selected %]
		754	$(document).ready(function() {
		755	$("#subgroup").val("[% sg.id %]");
		756	});
		757	[% END %]
		758	[% END %]
		759	group_subgroups["[% g.id %]"] = g_sg;
		760	</script>
		761	[% END %]
		762	</select></li>
		763	<li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
		764	</select></li>
		765	[% END %]
655	[% IF (public) %]	766	[% IF (public) %]
656	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>	767	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
657	[% ELSE %]	768	[% ELSE %]
Lines 719-730 Sub report:<select name="subreport"> Link Here
719		830
720	[% IF ( saved1 ) %]	831	[% IF ( saved1 ) %]
721	<div id="saved-reports-filter">	832	<div id="saved-reports-filter">
		833	<script type="text/javascript">
		834	$(document).ready(function() {
		835	no_subgroup_label = _( "-- All --" );
		836	load_group_subgroups();
		837	});
		838	</script>
722	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="get">	839	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="get">
723	<input type="hidden" name="phase" value="Use saved" />	840	<input type="hidden" name="phase" value="Use saved" />
724	<input type="hidden" name="filter_set" value="1" />	841	<input type="hidden" name="filter_set" value="1" />
725	<fieldset class="brief">	842	<fieldset class="brief">
726	<h3>Filter</h3>	843	<h3>Filter</h3>
727	<ol>	844	<ol>
		845	<li><label for="group">Choose Group and Subgroup: </label>
		846	<select name="group" id="group" onChange="load_group_subgroups();">
		847	<option value="">-- All --</option>
		848	[% FOR g IN groups_with_subgroups %]
		849	[% IF g.selected %]
		850	<option value="[% g.id %]" selected>[% g.name %]</option>
		851	[% ELSE %]
		852	<option value="[% g.id %]">[% g.name %]</option>
		853	[% END %]
		854	<script type="text/javascript">
		855	var g_sg = new Array();
		856	[% FOR sg IN g.subgroups %]
		857	g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
		858	[% IF sg.selected %]
		859	$(document).ready(function() {
		860	$("#subgroup").val("[% sg.id %]");
		861	});
		862	[% END %]
		863	[% END %]
		864	group_subgroups["[% g.id %]"] = g_sg;
		865	</script>
		866	[% END %]
		867	</select>
		868	<select name="subgroup" id="subgroup"></select>
		869	</li>
728	<li><label for="filter_date">Date:</label> <input type="text" id="filter_date" name="filter_date" size="10" value="[% filter_date %]" class="datepicker" />	870	<li><label for="filter_date">Date:</label> <input type="text" id="filter_date" name="filter_date" size="10" value="[% filter_date %]" class="datepicker" />
729	<div class="hint">[% INCLUDE 'date-format.inc' %]</div>	871	<div class="hint">[% INCLUDE 'date-format.inc' %]</div>
730		872

Lines 31-68 my $query = CGI->new(); Link Here

(-)a/opac/svc/report (-26 / +22 lines)
31	my $report_id = $query->param('id');	31	my $report_id = $query->param('id');
32	my $report_name = $query->param('name');	32	my $report_name = $query->param('name');
33		33
34	my $cache;	34	my $report_rec = get_saved_report( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
35	my $sql;	35	die "Sorry this report is not public\n" unless $report_rec->{public};
36	my $type;
37	my $notes;
38	my $cache_expiry;
39	my $public;
40		36
41	( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
42	get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
43	die "Sorry this report is not public\n" unless $public;
44		37
45	if (Koha::Cache->is_cache_active) {	38	my $cache_active = Koha::Cache->is_cache_active;
46	$cache = Koha::Cache->new(	39	my ($cache_key, $cache, $json_text);
47	);	40	if ($cache_active) {
48	my $page = $cache->get_from_cache("opac:report:$report_id");	41	$cache_key = "opac:report:".($report_name ? "name:$report_name" : "id:$report_id");
49	if ($page) {	42	$cache = Koha::Cache->new();
50	print $query->header;	43	$json_text = $cache->get_from_cache($cache_key);
51	print $page;
52	exit;
53	}
54	}	44	}
55		45
56	print $query->header;	46	unless ($json_text) {
57	if ($sql) {
58	my $offset = 0;	47	my $offset = 0;
59	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;	48	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;
60	my ( $sth, $errors ) = execute_query( $sql, $offset, $limit );	49	my ( $sth, $errors ) = execute_query( $report_rec->{savedsql}, $offset, $limit );
61	my $lines = $sth->fetchall_arrayref;	50	if ($sth) {
62	my $json_text = to_json($lines);	51	my $lines = $sth->fetchall_arrayref;
63	print $json_text;	52	$json_text = to_json($lines);
64		53
65	if (Koha::Cache->is_cache_active) {	54	if ($cache_active) {
66	$cache->set_in_cache( "opac:report:$report_id", $json_text, $cache_expiry );	55	$cache->set_in_cache( $cache_key, $json_text, $report_rec->{cache_expiry} );
		56	}
		57	}
		58	else {
		59	$json_text = to_json($errors);
67	}	60	}
68	}	61	}
		62
		63	print $query->header;
		64	print $json_text;

Lines 37-47 my $input = new CGI; Link Here

(-)a/reports/dictionary.pl (-145 / +147 lines)
37	my $referer = $input->referer();	37	my $referer = $input->referer();
38		38
39	my $phase = $input->param('phase') \|\| 'View Dictionary';	39	my $phase = $input->param('phase') \|\| 'View Dictionary';
40	my $area = $input->param('areas') \|\| '';	40	my $definition_name = $input->param('definition_name');
41	my $no_html = 0; # this will be set if we dont want to print out an html::template	41	my $definition_description = $input->param('definition_description');
42	my ( $template, $borrowernumber, $cookie ) = get_template_and_user(	42	my $area = $input->param('area') \|\| '';
43	{	43	my $no_html = 0; # this will be set if we dont want to print out an html::template
44	template_name => "reports/dictionary.tmpl",	44	my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
		45	{ template_name => "reports/dictionary.tmpl",
45	query => $input,	46	query => $input,
46	type => "intranet",	47	type => "intranet",
47	authnotrequired => 0,	48	authnotrequired => 0,
Lines 51-128 my ( $template, $borrowernumber, $cookie ) = get_template_and_user( Link Here
51	);	52	);
52		53
53	if ($phase eq 'View Dictionary'){	54	if ($phase eq 'View Dictionary'){
54	# view the dictionary we use to set up abstract variables such as all borrowers over fifty who live in a certain town	55	# view the dictionary we use to set up abstract variables such as all borrowers over fifty who live in a certain town
55	my $areas = get_report_areas();	56	my $definitions = get_from_dictionary($area);
56	foreach (@{ $areas }) {	57	$template->param(
57	$_->{selected} = 1 if $_->{id} eq $area; # mark active area	58	'areas'=> areas(),
58	}	59	'start_dictionary' => 1,
59	my $definitions = get_from_dictionary($area);	60	'definitions' => $definitions,
60	$template->param( 'areas' => $areas ,	61	);
61	'start_dictionary' => 1,	62	} elsif ( $phase eq 'Add New Definition' ) {
62	'definitions' => $definitions,	63
63	);	64	# display form allowing them to add a new definition
64	}	65	$template->param( 'new_dictionary' => 1, );
65	elsif ($phase eq 'Add New Definition'){
66	# display form allowing them to add a new definition
67	$template->param( 'new_dictionary' => 1,
68	);
69	}	66	}
70		67
71	elsif ($phase eq 'New Term step 2'){	68	elsif ( $phase eq 'New Term step 2' ) {
72	# Choosing the area	69
73	my $areas = C4::Reports::Guided::get_report_areas();	70	# Choosing the area
74	my $definition_name=$input->param('definition_name');	71	$template->param(
75	my $definition_description=$input->param('definition_description');	72	'step_2' => 1,
76	$template->param( 'step_2' => 1,	73	'areas' => areas(),
77	'areas' => $areas,	74	'definition_name' => $definition_name,
78	'definition_name' => $definition_name,	75	'definition_description' => $definition_description,
79	'definition_description' => $definition_description,	76	);
80	);
81	}	77	}
82		78
83	elsif ($phase eq 'New Term step 3'){	79	elsif ( $phase eq 'New Term step 3' ) {
84	# Choosing the columns	80
85	my $area = $input->param('areas');	81	# Choosing the columns
86	my $columns = get_columns($area,$input);	82	my $columns = get_columns( $area, $input );
87	my $definition_name=$input->param('definition_name');	83	$template->param(
88	my $definition_description=$input->param('definition_description');	84	'step_3' => 1,
89	$template->param( 'step_3' => 1,	85	'area' => $area,
90	'area' => $area,	86	'columns' => $columns,
91	'columns' => $columns,	87	'definition_name' => $definition_name,
92	'definition_name' => $definition_name,	88	'definition_description' => $definition_description,
93	'definition_description' => $definition_description,	89	);
94	);
95	}	90	}
96		91
97	elsif ($phase eq 'New Term step 4'){	92	elsif ( $phase eq 'New Term step 4' ) {
98	# Choosing the values	93
99	my $area=$input->param('area');	94	# Choosing the values
100	my $definition_name=$input->param('definition_name');	95	my @columns = $input->param('columns');
101	my $definition_description=$input->param('definition_description');	96	my $columnstring = join( ',', @columns );
102	my @columns = $input->param('columns');	97	my @column_loop;
103	my $columnstring = join (',',@columns);	98	foreach my $column (@columns) {
104	my @column_loop;	99	my %tmp_hash;
105	foreach my $column (@columns){	100	$tmp_hash{'name'} = $column;
106	my %tmp_hash;	101	my $type = get_column_type($column);
107	$tmp_hash{'name'}=$column;	102	if ( $type eq 'distinct' ) {
108	my $type =get_column_type($column);	103	my $values = get_distinct_values($column);
109	if ($type eq 'distinct'){	104	$tmp_hash{'values'} = $values;
110	my $values = get_distinct_values($column);	105	$tmp_hash{'distinct'} = 1;
111	$tmp_hash{'values'} = $values;	106
112	$tmp_hash{'distinct'} = 1;	107	}
113		108	if ( $type eq 'DATE' \|\| $type eq 'DATETIME' ) {
114	}	109	$tmp_hash{'date'} = 1;
115	if ($type eq 'DATE' \|\| $type eq 'DATETIME'){	110	}
116	$tmp_hash{'date'}=1;	111	if ( $type eq 'TEXT' ) {
117	}	112	$tmp_hash{'text'} = 1;
118	if ($type eq 'TEXT' \|\| $type eq 'MEDIUMTEXT'){	113	}
119	$tmp_hash{'text'}=1;	114
120	}	115	# else {
121	# else {	116	# warn $type;#
122	# warn $type;#	117	# }
123	# }	118	push @column_loop, \%tmp_hash;
124	push @column_loop,\%tmp_hash;	119	}
125	}
126		120
127	$template->param( 'step_4' => 1,	121	$template->param( 'step_4' => 1,
128	'area' => $area,	122	'area' => $area,
Lines 134-216 elsif ($phase eq 'New Term step 4'){ Link Here
134	);	128	);
135	}	129	}
136		130
137	elsif ($phase eq 'New Term step 5'){	131	elsif ( $phase eq 'New Term step 5' ) {
138	# Confirmation screen	132	# Confirmation screen
139	my $areas = C4::Reports::Guided::get_report_areas();	133	my $columnstring = $input->param('columnstring');
140	my $area = $input->param('area');	134	my @criteria = $input->param('criteria_column');
141	my $areaname = $areas->[$area - 1]->{'name'};	135	my $query_criteria;
142	my $columnstring = $input->param('columnstring');	136	my @criteria_loop;
143	my $definition_name=$input->param('definition_name');	137
144	my $definition_description=$input->param('definition_description');	138	foreach my $crit (@criteria) {
145	my @criteria = $input->param('criteria_column');	139	my $value = $input->param( $crit . "_value" );
146	my $query_criteria;	140	if ($value) {
147	my @criteria_loop;	141	my %tmp_hash;
148	foreach my $crit (@criteria) {	142	$tmp_hash{'name'} = $crit;
149	my $value = $input->param( $crit . "_value" );	143	$tmp_hash{'value'} = $value;
150	if ($value) {	144	push @criteria_loop, \%tmp_hash;
151	my %tmp_hash;	145	if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
152	$tmp_hash{'name'}=$crit;	146	my $date = C4::Dates->new($value);
153	$tmp_hash{'value'} = $value;	147	$value = $date->output("iso");
154	push @criteria_loop,\%tmp_hash;	148	}
155	if ($value =~ C4::Dates->regexp(C4::Context->preference('dateformat'))) {	149	$query_criteria .= " AND $crit='$value'";
156	my $date = C4::Dates->new($value);	150	}
157	$value = $date->output("iso");	151	$value = $input->param( $crit . "_start_value" );
158	}	152	if ($value) {
159	$query_criteria .= " AND $crit='$value'";	153	my %tmp_hash;
160	}	154	$tmp_hash{'name'} = "$crit Start";
161	$value = $input->param( $crit . "_start_value" );	155	$tmp_hash{'value'} = $value;
162	if ($value) {	156	push @criteria_loop, \%tmp_hash;
163	my %tmp_hash;	157	if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
164	$tmp_hash{'name'}="$crit Start";	158	my $date = C4::Dates->new($value);
165	$tmp_hash{'value'} = $value;	159	$value = $date->output("iso");
166	push @criteria_loop,\%tmp_hash;	160	}
167	if ($value =~ C4::Dates->regexp(C4::Context->preference('dateformat'))) {	161	$query_criteria .= " AND $crit >= '$value'";
168	my $date = C4::Dates->new($value);	162	}
169	$value = $date->output("iso");	163	$value = $input->param( $crit . "_end_value" );
170	}	164	if ($value) {
171	$query_criteria .= " AND $crit >= '$value'";	165	my %tmp_hash;
172	}	166	$tmp_hash{'name'} = "$crit End";
173	$value = $input->param( $crit . "_end_value" );	167	$tmp_hash{'value'} = $value;
174	if ($value) {	168	push @criteria_loop, \%tmp_hash;
175	my %tmp_hash;	169	if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
176	$tmp_hash{'name'}="$crit End";	170	my $date = C4::Dates->new($value);
177	$tmp_hash{'value'} = $value;	171	$value = $date->output("iso");
178	push @criteria_loop,\%tmp_hash;	172	}
179	if ($value =~ C4::Dates->regexp(C4::Context->preference('dateformat'))) {	173	$query_criteria .= " AND $crit <= '$value'";
180	my $date = C4::Dates->new($value);	174	}
181	$value = $date->output("iso");	175	}
182	}	176	my %report_areas = map @$_, get_report_areas();
183	$query_criteria .= " AND $crit <= '$value'";	177	$template->param(
184	}	178	'step_5' => 1,
185	}	179	'area' => $area,
186	$template->param( 'step_5' => 1,	180	'areaname' => $report_areas{$area},
187	'area' => $area,	181	'definition_name' => $definition_name,
188	'areaname' => $areaname,	182	'definition_description' => $definition_description,
189	'definition_name' => $definition_name,	183	'query' => $query_criteria,
190	'definition_description' => $definition_description,	184	'columnstring' => $columnstring,
191	'query' => $query_criteria,	185	'criteria_loop' => \@criteria_loop,
192	'columnstring' => $columnstring,	186	);
193	'criteria_loop' => \@criteria_loop,
194	);
195	}	187	}
196		188
197	elsif ($phase eq 'New Term step 6'){	189	elsif ( $phase eq 'New Term step 6' ) {
198	# Saving	190	# Saving
199	my $area = $input->param('area');	191	my $area = $input->param('area');
200	my $definition_name=$input->param('definition_name');	192	my $sql = $input->param('sql');
201	my $definition_description=$input->param('definition_description');	193	save_dictionary( $definition_name, $definition_description, $sql, $area );
202	my $sql=$input->param('sql');	194	$no_html = 1;
203	save_dictionary($definition_name,$definition_description,$sql,$area);	195	print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");
204	$no_html=1;	196
205	print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");	197	} elsif ( $phase eq 'Delete Definition' ) {
206		198	$no_html = 1;
		199	my $id = $input->param('id');
		200	delete_definition($id);
		201	print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");
207	}	202	}
208	elsif ($phase eq 'Delete Definition'){
209	$no_html=1;
210	my $id = $input->param('id');
211	delete_definition($id);
212	print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");
213	}
214		203
215	$template->param( 'referer' => $referer );	204	$template->param( 'referer' => $referer );
216		205
Lines 218-220 $template->param( 'referer' => $referer ); Link Here
218	if (!$no_html){	207	if (!$no_html){
219	output_html_with_http_headers $input, $cookie, $template->output;	208	output_html_with_http_headers $input, $cookie, $template->output;
220	}	209	}
		210
		211	sub areas {
		212	my $areas = get_report_areas();
		213	my @a;
		214	foreach (@$areas) {
		215	push @a, {
		216	id => $_->[0],
		217	name => $_->[1],
		218	selected => ($_->[0] eq $area),
		219	};
		220	}
		221	return \@a;
		222	}

Lines 33-45 my $query = CGI->new(); Link Here

(-)a/svc/report (-36 / +20 lines)
33	my $report_id = $query->param('id');	33	my $report_id = $query->param('id');
34	my $report_name = $query->param('name');	34	my $report_name = $query->param('name');
35		35
36	my $cache;
37	my $sql;
38	my $type;
39	my $notes;
40	my $cache_expiry;
41	my $public;
42
43	my ( $template, $loggedinuser, $cookie ) = get_template_and_user(	36	my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
44	{	37	{
45	template_name => "intranet-main.tmpl",	38	template_name => "intranet-main.tmpl",
Lines 50-88 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
50	}	43	}
51	);	44	);
52		45
53	if (Koha::Cache->is_cache_active) {	46	my $cache_active = Koha::Cache->is_cache_active;
54	if ($report_name) { # When retrieving by name, we have to hit the	47	my ($cache_key, $cache, $json_text);
55	# database to get the ID before we can check	48	if ($cache_active) {
56	# the cache. Yuck.	49	$cache_key = "intranet:report:".($report_name ? "name:$report_name" : "id:$report_id");
57	( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
58	get_saved_report( { 'name' => $report_name } );
59	}
60
61	$cache = Koha::Cache->new();	50	$cache = Koha::Cache->new();
62	my $page = $cache->get_from_cache("intranet:report:$report_id");	51	$json_text = $cache->get_from_cache($cache_key);
63	if ($page) {
64	print $query->header;
65	print $page;
66	exit;
67	}
68	}	52	}
69		53
70	print $query->header;	54	unless ($json_text) {
71		55	my $report_rec = get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id });
72	# $public isnt used for intranet
73	unless ($sql) {
74	( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
75	get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
76	}
77	if ($sql) {
78	my $offset = 0;	56	my $offset = 0;
79	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;	57	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;
80	my ( $sth, $errors ) = execute_query( $sql, $offset, $limit );	58	my ( $sth, $errors ) = execute_query( $report_rec->{savedsql}, $offset, $limit );
81	my $lines = $sth->fetchall_arrayref;	59	if ($sth) {
82	my $json_text = to_json($lines);	60	my $lines = $sth->fetchall_arrayref;
83	print $json_text;	61	$json_text = to_json($lines);
84		62
85	if (Koha::Cache->is_cache_active) {	63	if ($cache_active) {
86	$cache->set_in_cache( "intranet:report:$report_id", $json_text, $cache_expiry );	64	$cache->set_in_cache( $cache_key, $json_text, $report_rec->{cache_expiry} );
		65	}
		66	}
		67	else {
		68	$json_text = to_json($errors);
87	}	69	}
88	}	70	}
89	-	71
		72	print $query->header;
		73	print $json_text;