Attachment #11775 for bug #7993

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





=item execute_query

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


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

sub get_saved_report {
    my ($id) = @_;
    my $dbh   = C4::Context->dbh();
    my $query;
    my $report_arg;
    if ($#_ == 0 && ref $_[0] ne 'HASH') {
        ($report_arg) = @_;
        $query = " SELECT * FROM saved_sql WHERE id = ?";
    } elsif (ref $_[0] eq 'HASH') {
        my ($selector) = @_;
        if ($selector->{name}) {
            $query = " SELECT * FROM saved_sql WHERE report_name = ?";
            $report_arg = $selector->{name};
        } elsif ($selector->{id} || $selector->{id} eq '0') {
            $query = " SELECT * FROM saved_sql WHERE id = ?";
            $report_arg = $selector->{id};
        } else {
            return;
        }
    } else {
        return;
    }
    return $dbh->selectrow_hashref($query, undef, $report_arg);
}

=item create_compound($masterID,$subreportID)

Lines 33-39 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/dictionary.tt (-1 / +1 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="area">	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 517-523 canned reports and writing custom SQL reports.</p> Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-4 / +20 lines)
517	[% IF groups_with_subgroups %]	517	[% IF groups_with_subgroups %]
518	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">	518	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
519	[% FOR g IN groups_with_subgroups %]	519	[% FOR g IN groups_with_subgroups %]
520	<option value="[% g.id %]" [% IF g.selected %]selected[% END %]>[% g.name %]</option>	520	[% IF g.selected %]
		521	<option value="[% g.id %]" selected="selected">[% g.name %]</option>
		522	[% ELSE %]
		523	<option value="[% g.id %]">[% g.name %]</option>
		524	[% END %]
521	<script type="text/javascript">	525	<script type="text/javascript">
522	var g_sg = new Array();	526	var g_sg = new Array();
523	[% FOR sg IN g.subgroups %]	527	[% FOR sg IN g.subgroups %]
Lines 607-613 $(document).ready(function() { Link Here
607	[% IF groups_with_subgroups %]	611	[% IF groups_with_subgroups %]
608	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">	612	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
609	[% FOR g IN groups_with_subgroups %]	613	[% FOR g IN groups_with_subgroups %]
610	<option value="[% g.id %]" [% IF g.selected %]selected[% END %]>[% g.name %]</option>	614	[% IF g.selected %]
		615	<option value="[% g.id %]" selected="selected">[% g.name %]</option>
		616	[% ELSE %]
		617	<option value="[% g.id %]">[% g.name %]</option>
		618	[% END %]
611	<script type="text/javascript">	619	<script type="text/javascript">
612	var g_sg = new Array();	620	var g_sg = new Array();
613	[% FOR sg IN g.subgroups %]	621	[% FOR sg IN g.subgroups %]
Lines 724-730 $(document).ready(function() { Link Here
724	[% IF groups_with_subgroups %]	732	[% IF groups_with_subgroups %]
725	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">	733	<li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
726	[% FOR g IN groups_with_subgroups %]	734	[% FOR g IN groups_with_subgroups %]
727	<option value="[% g.id %]" [% IF g.selected %]selected[% END %]>[% g.name %]</option>	735	[% IF g.selected %]
		736	<option value="[% g.id %]" selected="selected">[% g.name %]</option>
		737	[% ELSE %]
		738	<option value="[% g.id %]">[% g.name %]</option>
		739	[% END %]
728	<script type="text/javascript">	740	<script type="text/javascript">
729	var g_sg = new Array();	741	var g_sg = new Array();
730	[% FOR sg IN g.subgroups %]	742	[% FOR sg IN g.subgroups %]
Lines 825-831 $(document).ready(function() { Link Here
825	<select name="group" id="group" onChange="load_group_subgroups();">	837	<select name="group" id="group" onChange="load_group_subgroups();">
826	<option value="">-- All --</option>	838	<option value="">-- All --</option>
827	[% FOR g IN groups_with_subgroups %]	839	[% FOR g IN groups_with_subgroups %]
828	<option value="[% g.id %]" [% IF g.selected %]selected[% END %]>[% g.name %]</option>	840	[% IF g.selected %]
		841	<option value="[% g.id %]" selected="selected">[% g.name %]</option>
		842	[% ELSE %]
		843	<option value="[% g.id %]">[% g.name %]</option>
		844	[% END %]
829	<script type="text/javascript">	845	<script type="text/javascript">
830	var g_sg = new Array();	846	var g_sg = new Array();
831	[% FOR sg IN g.subgroups %]	847	[% FOR sg IN g.subgroups %]




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 } );
my $sql;
my $type;
my $notes;
my $cache_expiry;
my $public;

my $report_rec = get_saved_report($report_id);
die "Sorry this report is not public\n" unless $report_rec->{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);
}

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

if (Koha::Cache->is_cache_active) {
    $cache->set_in_cache( "opac:report:$report_id", $json_text, $report_rec->{cache_expiry} );
}

Lines 126-133 elsif ( $phase eq 'Show SQL'){ Link Here

(-)a/reports/guided_reports.pl (-2 / +2 lines)
126	'id' => $id,	126	'id' => $id,
127	'reportname' => $report->{report_name},	127	'reportname' => $report->{report_name},
128	'notes' => $report->{notes},	128	'notes' => $report->{notes},
129	'sql' => $report->{savedsql},	129	'sql' => $report->{savedsql},
130	'showsql' => 1,	130	'showsql' => 1,
131	);	131	);
132	}	132	}
133		133




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

unless ($json_text) {
    my $report_rec = get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id });
    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;

# $public isnt used for intranet
my $report_rec = get_saved_report($report_id);
my $offset = 0;
my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
my ( $sth, $errors ) = execute_query( $report_rec->{sql}, $offset, $limit );
my $lines     = $sth->fetchall_arrayref;
my $json_text = to_json($lines);
print $json_text;

if (Koha::Cache->is_cache_active) {
    $cache->set_in_cache( "intranet:report:$report_id", $json_text, $report_rec->{cache_expiry} );
}
- 

Return to bug 7993

Lines 400-406 sub nb_rows($) { Link Here

(-)a/C4/Reports/Guided.pm (-3 / +21 lines)
400		400
401	=item execute_query	401	=item execute_query
402		402
403	($results, $total, $error) = execute_query($sql, $offset, $limit)	403	($results, $error) = execute_query($sql, $offset, $limit)
404		404
405		405
406	When passed C<$sql>, this function returns an array ref containing a result set	406	When passed C<$sql>, this function returns an array ref containing a result set
Lines 648-655 sub get_saved_reports { Link Here
648	sub get_saved_report {	648	sub get_saved_report {
649	my ($id) = @_;	649	my ($id) = @_;
650	my $dbh = C4::Context->dbh();	650	my $dbh = C4::Context->dbh();
651	my $query = " SELECT * FROM saved_sql WHERE id = ?";	651	my $query;
652	return $dbh->selectrow_hashref($query, undef, $id);	652	my $report_arg;
		653	if ($#_ == 0 && ref $_[0] ne 'HASH') {
		654	($report_arg) = @_;
		655	$query = " SELECT * FROM saved_sql WHERE id = ?";
		656	} elsif (ref $_[0] eq 'HASH') {
		657	my ($selector) = @_;
		658	if ($selector->{name}) {
		659	$query = " SELECT * FROM saved_sql WHERE report_name = ?";
		660	$report_arg = $selector->{name};
		661	} elsif ($selector->{id} \|\| $selector->{id} eq '0') {
		662	$query = " SELECT * FROM saved_sql WHERE id = ?";
		663	$report_arg = $selector->{id};
		664	} else {
		665	return;
		666	}
		667	} else {
		668	return;
		669	}
		670	return $dbh->selectrow_hashref($query, undef, $report_arg);
653	}	671	}
654		672
655	=item create_compound($masterID,$subreportID)	673	=item create_compound($masterID,$subreportID)

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

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

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

(-)a/svc/report (-33 / +22 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-83 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) {	52	}
64	print $query->header;	53
65	print $page;	54	unless ($json_text) {
66	exit;	55	my $report_rec = get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id });
		56	my $offset = 0;
		57	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;
		58	my ( $sth, $errors ) = execute_query( $report_rec->{savedsql}, $offset, $limit );
		59	if ($sth) {
		60	my $lines = $sth->fetchall_arrayref;
		61	$json_text = to_json($lines);
		62
		63	if ($cache_active) {
		64	$cache->set_in_cache( $cache_key, $json_text, $report_rec->{cache_expiry} );
		65	}
		66	}
		67	else {
		68	$json_text = to_json($errors);
67	}	69	}
68	}	70	}
69		71
70	print $query->header;	72	print $query->header;
71
72	# $public isnt used for intranet
73	my $report_rec = get_saved_report($report_id);
74	my $offset = 0;
75	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;
76	my ( $sth, $errors ) = execute_query( $report_rec->{sql}, $offset, $limit );
77	my $lines = $sth->fetchall_arrayref;
78	my $json_text = to_json($lines);
79	print $json_text;	73	print $json_text;
80
81	if (Koha::Cache->is_cache_active) {
82	$cache->set_in_cache( "intranet:report:$report_id", $json_text, $report_rec->{cache_expiry} );
83	}
84	-