Attachment #74869 for bug #20495

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




    @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 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

    my (@ids) = @_;
    return unless @ids;
    foreach my $id (@ids) {
        my $data = Koha::Reports->find($id);
        logaction( "REPORTS", "DELETE", $id, $data->report_name." | ".$data->savedsql ) if C4::Context->preference("ReportsLog");
    }
    my $dbh = C4::Context->dbh;
    my $query = 'DELETE FROM saved_sql WHERE id IN (' . join( ',', ('?') x @ids ) . ')';

    return $result;
}

sub get_saved_report {
    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);
}

=head2 create_compound($masterID,$subreportID)

This will take 2 reports and create a compound report using both of them

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

    # get the reports
    my $master = Koha::Reports->find($masterID);
    my $mastersql = $master->savedsql;
    my $mastertype = $master->type;
    my $sub = Koha::Reports->find($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




use Modern::Perl;

use C4::Reports::Guided; # 0.12
use Koha::Reports;
use C4::Context;
use C4::Log;
use Koha::Email;

my $date = $today->ymd();

foreach my $report_id (@ARGV) {
    my $report = Koha::Reports->find( $report_id );
    unless ($report) {
        warn "ERROR: No saved report $report_id found";
        next;
    }
    my $sql         = $report->savedsql;
    my $report_name = $report->report_name;
    my $type        = $report->type;

    $verbose and print "SQL: $sql\n\n";
    if ( $subject eq "" )




use Modern::Perl;

use C4::Reports::Guided;
use Koha::Reports;
use JSON;
use CGI qw ( -utf8 );


my $report_name = $query->param('name');
my $report_annotation = $query->param('annotated');

my $report_rec = Koha::Reports->find( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
if (!$report_rec) { die "There is no such report.\n"; }

die "Sorry this report is not public\n" unless $report_rec->public;

my @sql_params  = $query->param('sql_params');


unless ($json_text) {
    my $offset = 0;
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
    my $sql = $report_rec->savedsql;

    # convert SQL parameters to placeholders
    $sql =~ s/(<<.*?>>)/\?/g;


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




use File::Temp;
use File::Basename qw( dirname );
use C4::Reports::Guided;
use Koha::Reports;
use C4::Auth qw/:DEFAULT get_session/;
use C4::Output;
use C4::Debug;


    if ( $op eq 'convert' ) {
        my $report_id = $input->param('report_id');
        my $report    = Koha::Reports->find($report_id);
        if ($report) {
            my $updated_sql = C4::Reports::Guided::convert_sql( $report->savedsql );
            C4::Reports::Guided::update_sql(
                $report_id,
                {
                    sql          => $updated_sql,
                    name         => $report->report_name,
                    group        => $report->report_group,
                    subgroup     => $report->report_subgroup,
                    notes        => $report->notes,
                    public       => $report->public,
                    cache_expiry => $report->cache_expiry,
                }
            );
            $template->param( report_converted => $report->report_name );
        }
    }


elsif ( $phase eq 'Show SQL'){
	
    my $id = $input->param('reports');
    my $report = Koha::Reports->find($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 = Koha::Reports->find($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 ( $sql, $original_sql, $type, $name, $notes );
    if (my $report = Koha::Reports->find($report_id)) {
        $sql   = $original_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


	# export results to tab separated text or CSV
    my $report_id      = $input->param('report_id');
    my $report         = Koha::Reports->find($report_id);
    my $sql            = $report->savedsql;
    my @param_names    = $input->multi_param('param_name');
    my @sql_params     = $input->multi_param('sql_params');
    my $format         = $input->param('format');





use C4::Auth;
use C4::Reports::Guided;
use Koha::Reports;
use C4::Output;
use CGI qw ( -utf8 );


    }
);

my $report = Koha::Reports->find( $report_id );

my $params;
if ( $report ) {
    my $sql = $report->savedsql;
    my $updated_sql = C4::Reports::Guided::convert_sql( $sql );
    $params = { msg => 'can_be_updated', updated_sql => $updated_sql, current_sql => $sql };
} else {





use C4::Auth;
use C4::Reports::Guided;
use Koha::Reports;
use JSON;
use CGI qw ( -utf8 );


my $report_name = $query->param('name');
my $report_annotation = $query->param('annotated');

my $report_rec = Koha::Reports->find( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
if (!$report_rec) { die "There is no such report.\n"; }

my @sql_params  = $query->param('sql_params');

unless ($json_text) {
    my $offset = 0;
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
    my $sql = $report_rec->savedsql;

    # convert SQL parameters to placeholders
    $sql =~ s/(<<.*?>>)/\?/g;

        $json_text = encode_json($lines);

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

Lines 24-29 use Test::Warn; Link Here

(-)a/t/db_dependent/Reports/Guided.t (-1 / +1 lines)
24	use t::lib::TestBuilder;	24	use t::lib::TestBuilder;
25	use C4::Context;	25	use C4::Context;
26	use Koha::Database;	26	use Koha::Database;
		27	use Koha::Reports;
27		28
28	use_ok('C4::Reports::Guided');	29	use_ok('C4::Reports::Guided');
29	can_ok(	30	can_ok(
30	-

Return to bug 20495

Lines 39-45 BEGIN { Link Here

(-)a/C4/Reports/Guided.pm (-33 / +9 lines)
39	@ISA = qw(Exporter);	39	@ISA = qw(Exporter);
40	@EXPORT = qw(	40	@EXPORT = qw(
41	get_report_types get_report_areas get_report_groups get_columns build_query get_criteria	41	get_report_types get_report_areas get_report_groups get_columns build_query get_criteria
42	save_report get_saved_reports execute_query get_saved_report create_compound run_compound	42	save_report get_saved_reports execute_query create_compound run_compound
43	get_column_type get_distinct_values save_dictionary get_from_dictionary	43	get_column_type get_distinct_values save_dictionary get_from_dictionary
44	delete_definition delete_report format_results get_sql	44	delete_definition delete_report format_results get_sql
45	nb_rows update_sql	45	nb_rows update_sql
Lines 622-629 sub delete_report { Link Here
622	my (@ids) = @_;	622	my (@ids) = @_;
623	return unless @ids;	623	return unless @ids;
624	foreach my $id (@ids) {	624	foreach my $id (@ids) {
625	my $data = get_saved_report($id);	625	my $data = Koha::Reports->find($id);
626	logaction( "REPORTS", "DELETE", $id, "$data->{'report_name'} \| $data->{'savedsql'} " ) if C4::Context->preference("ReportsLog");	626	logaction( "REPORTS", "DELETE", $id, $data->report_name." \| ".$data->savedsql ) if C4::Context->preference("ReportsLog");
627	}	627	}
628	my $dbh = C4::Context->dbh;	628	my $dbh = C4::Context->dbh;
629	my $query = 'DELETE FROM saved_sql WHERE id IN (' . join( ',', ('?') x @ids ) . ')';	629	my $query = 'DELETE FROM saved_sql WHERE id IN (' . join( ',', ('?') x @ids ) . ')';
Lines 694-723 sub get_saved_reports { Link Here
694	return $result;	694	return $result;
695	}	695	}
696		696
697	sub get_saved_report {
698	my $dbh = C4::Context->dbh();
699	my $query;
700	my $report_arg;
701	if ($#_ == 0 && ref $_[0] ne 'HASH') {
702	($report_arg) = @_;
703	$query = " SELECT * FROM saved_sql WHERE id = ?";
704	} elsif (ref $_[0] eq 'HASH') {
705	my ($selector) = @_;
706	if ($selector->{name}) {
707	$query = " SELECT * FROM saved_sql WHERE report_name = ?";
708	$report_arg = $selector->{name};
709	} elsif ($selector->{id} \|\| $selector->{id} eq '0') {
710	$query = " SELECT * FROM saved_sql WHERE id = ?";
711	$report_arg = $selector->{id};
712	} else {
713	return;
714	}
715	} else {
716	return;
717	}
718	return $dbh->selectrow_hashref($query, undef, $report_arg);
719	}
720
721	=head2 create_compound($masterID,$subreportID)	697	=head2 create_compound($masterID,$subreportID)
722		698
723	This will take 2 reports and create a compound report using both of them	699	This will take 2 reports and create a compound report using both of them
Lines 729-740 sub create_compound { Link Here
729	my $dbh = C4::Context->dbh();	705	my $dbh = C4::Context->dbh();
730		706
731	# get the reports	707	# get the reports
732	my $master = get_saved_report($masterID);	708	my $master = Koha::Reports->find($masterID);
733	my $mastersql = $master->{savedsql};	709	my $mastersql = $master->savedsql;
734	my $mastertype = $master->{type};	710	my $mastertype = $master->type;
735	my $sub = get_saved_report($subreportID);	711	my $sub = Koha::Reports->find($subreportID);
736	my $subsql = $master->{savedsql};	712	my $subsql = $master->savedsql;
737	my $subtype = $master->{type};	713	my $subtype = $master->type;
738		714
739	# now we have to do some checking to see how these two will fit together	715	# now we have to do some checking to see how these two will fit together
740	# or if they will	716	# or if they will

Lines 21-26 Link Here

(-)a/misc/cronjobs/runreport.pl (-4 / +5 lines)
21	use Modern::Perl;	21	use Modern::Perl;
22		22
23	use C4::Reports::Guided; # 0.12	23	use C4::Reports::Guided; # 0.12
		24	use Koha::Reports;
24	use C4::Context;	25	use C4::Context;
25	use C4::Log;	26	use C4::Log;
26	use Koha::Email;	27	use Koha::Email;
Lines 231-244 my $today = dt_from_string(); Link Here
231	my $date = $today->ymd();	232	my $date = $today->ymd();
232		233
233	foreach my $report_id (@ARGV) {	234	foreach my $report_id (@ARGV) {
234	my $report = get_saved_report($report_id);	235	my $report = Koha::Reports->find( $report_id );
235	unless ($report) {	236	unless ($report) {
236	warn "ERROR: No saved report $report_id found";	237	warn "ERROR: No saved report $report_id found";
237	next;	238	next;
238	}	239	}
239	my $sql = $report->{savedsql};	240	my $sql = $report->savedsql;
240	my $report_name = $report->{report_name};	241	my $report_name = $report->report_name;
241	my $type = $report->{type};	242	my $type = $report->type;
242		243
243	$verbose and print "SQL: $sql\n\n";	244	$verbose and print "SQL: $sql\n\n";
244	if ( $subject eq "" )	245	if ( $subject eq "" )

Lines 24-29 Link Here

(-)a/opac/svc/report (-4 / +5 lines)
24	use Modern::Perl;	24	use Modern::Perl;
25		25
26	use C4::Reports::Guided;	26	use C4::Reports::Guided;
		27	use Koha::Reports;
27	use JSON;	28	use JSON;
28	use CGI qw ( -utf8 );	29	use CGI qw ( -utf8 );
29		30
Lines 34-43 my $report_id = $query->param('id'); Link Here
34	my $report_name = $query->param('name');	35	my $report_name = $query->param('name');
35	my $report_annotation = $query->param('annotated');	36	my $report_annotation = $query->param('annotated');
36		37
37	my $report_rec = get_saved_report( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );	38	my $report_rec = Koha::Reports->find( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
38	if (!$report_rec) { die "There is no such report.\n"; }	39	if (!$report_rec) { die "There is no such report.\n"; }
39		40
40	die "Sorry this report is not public\n" unless $report_rec->{public};	41	die "Sorry this report is not public\n" unless $report_rec->public;
41		42
42	my @sql_params = $query->param('sql_params');	43	my @sql_params = $query->param('sql_params');
43		44
Lines 55-61 if ($cache_active) { Link Here
55	unless ($json_text) {	56	unless ($json_text) {
56	my $offset = 0;	57	my $offset = 0;
57	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;	58	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;
58	my $sql = $report_rec->{savedsql};	59	my $sql = $report_rec->savedsql;
59		60
60	# convert SQL parameters to placeholders	61	# convert SQL parameters to placeholders
61	$sql =~ s/(<<.*?>>)/\?/g;	62	$sql =~ s/(<<.*?>>)/\?/g;
Lines 74-80 unless ($json_text) { Link Here
74		75
75	if ($cache_active) {	76	if ($cache_active) {
76	$cache->set_in_cache( $cache_key, $json_text,	77	$cache->set_in_cache( $cache_key, $json_text,
77	{ expiry => $report_rec->{cache_expiry} } );	78	{ expiry => $report_rec->cache_expiry } );
78	}	79	}
79	}	80	}
80	else {	81	else {

Lines 25-30 use URI::Escape; Link Here

(-)a/reports/guided_reports.pl (-28 / +29 lines)
25	use File::Temp;	25	use File::Temp;
26	use File::Basename qw( dirname );	26	use File::Basename qw( dirname );
27	use C4::Reports::Guided;	27	use C4::Reports::Guided;
		28	use Koha::Reports;
28	use C4::Auth qw/:DEFAULT get_session/;	29	use C4::Auth qw/:DEFAULT get_session/;
29	use C4::Output;	30	use C4::Output;
30	use C4::Debug;	31	use C4::Debug;
Lines 109-130 elsif ( $phase eq 'Build new' ) { Link Here
109		110
110	if ( $op eq 'convert' ) {	111	if ( $op eq 'convert' ) {
111	my $report_id = $input->param('report_id');	112	my $report_id = $input->param('report_id');
112	my $report = C4::Reports::Guided::get_saved_report($report_id);	113	my $report = Koha::Reports->find($report_id);
113	if ($report) {	114	if ($report) {
114	my $updated_sql = C4::Reports::Guided::convert_sql( $report->{savedsql} );	115	my $updated_sql = C4::Reports::Guided::convert_sql( $report->savedsql );
115	C4::Reports::Guided::update_sql(	116	C4::Reports::Guided::update_sql(
116	$report_id,	117	$report_id,
117	{	118	{
118	sql => $updated_sql,	119	sql => $updated_sql,
119	name => $report->{report_name},	120	name => $report->report_name,
120	group => $report->{report_group},	121	group => $report->report_group,
121	subgroup => $report->{report_subgroup},	122	subgroup => $report->report_subgroup,
122	notes => $report->{notes},	123	notes => $report->notes,
123	public => $report->{public},	124	public => $report->public,
124	cache_expiry => $report->{cache_expiry},	125	cache_expiry => $report->cache_expiry,
125	}	126	}
126	);	127	);
127	$template->param( report_converted => $report->{report_name} );	128	$template->param( report_converted => $report->report_name );
128	}	129	}
129	}	130	}
130		131
Lines 172-200 elsif ( $phase eq 'Delete Saved') { Link Here
172	elsif ( $phase eq 'Show SQL'){	173	elsif ( $phase eq 'Show SQL'){
173		174
174	my $id = $input->param('reports');	175	my $id = $input->param('reports');
175	my $report = get_saved_report($id);	176	my $report = Koha::Reports->find($id);
176	$template->param(	177	$template->param(
177	'id' => $id,	178	'id' => $id,
178	'reportname' => $report->{report_name},	179	'reportname' => $report->report_name,
179	'notes' => $report->{notes},	180	'notes' => $report->notes,
180	'sql' => $report->{savedsql},	181	'sql' => $report->savedsql,
181	'showsql' => 1,	182	'showsql' => 1,
182	);	183	);
183	}	184	}
184		185
185	elsif ( $phase eq 'Edit SQL'){	186	elsif ( $phase eq 'Edit SQL'){
186	my $id = $input->param('reports');	187	my $id = $input->param('reports');
187	my $report = get_saved_report($id);	188	my $report = Koha::Reports->find($id);
188	my $group = $report->{report_group};	189	my $group = $report->report_group;
189	my $subgroup = $report->{report_subgroup};	190	my $subgroup = $report->report_subgroup;
190	$template->param(	191	$template->param(
191	'sql' => $report->{savedsql},	192	'sql' => $report->savedsql,
192	'reportname' => $report->{report_name},	193	'reportname' => $report->report_name,
193	'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),	194	'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
194	'notes' => $report->{notes},	195	'notes' => $report->notes,
195	'id' => $id,	196	'id' => $id,
196	'cache_expiry' => $report->{cache_expiry},	197	'cache_expiry' => $report->cache_expiry,
197	'public' => $report->{public},	198	'public' => $report->public,
198	'usecache' => $usecache,	199	'usecache' => $usecache,
199	'editsql' => 1,	200	'editsql' => 1,
200	);	201	);
Lines 686-695 elsif ($phase eq 'Run this report'){ Link Here
686	);	687	);
687		688
688	my ( $sql, $original_sql, $type, $name, $notes );	689	my ( $sql, $original_sql, $type, $name, $notes );
689	if (my $report = get_saved_report($report_id)) {	690	if (my $report = Koha::Reports->find($report_id)) {
690	$sql = $original_sql = $report->{savedsql};	691	$sql = $original_sql = $report->savedsql;
691	$name = $report->{report_name};	692	$name = $report->report_name;
692	$notes = $report->{notes};	693	$notes = $report->notes;
693		694
694	my @rows = ();	695	my @rows = ();
695	# if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters	696	# if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
Lines 850-857 elsif ($phase eq 'Export'){ Link Here
850		851
851	# export results to tab separated text or CSV	852	# export results to tab separated text or CSV
852	my $report_id = $input->param('report_id');	853	my $report_id = $input->param('report_id');
853	my $report = get_saved_report($report_id);	854	my $report = Koha::Reports->find($report_id);
854	my $sql = $report->{savedsql};	855	my $sql = $report->savedsql;
855	my @param_names = $input->multi_param('param_name');	856	my @param_names = $input->multi_param('param_name');
856	my @sql_params = $input->multi_param('sql_params');	857	my @sql_params = $input->multi_param('sql_params');
857	my $format = $input->param('format');	858	my $format = $input->param('format');

Lines 21-26 use Modern::Perl; Link Here

(-)a/svc/convert_report (-2 / +3 lines)
21		21
22	use C4::Auth;	22	use C4::Auth;
23	use C4::Reports::Guided;	23	use C4::Reports::Guided;
		24	use Koha::Reports;
24	use C4::Output;	25	use C4::Output;
25	use CGI qw ( -utf8 );	26	use CGI qw ( -utf8 );
26		27
Lines 37-47 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
37	}	38	}
38	);	39	);
39		40
40	my $report = get_saved_report( $report_id );	41	my $report = Koha::Reports->find( $report_id );
41		42
42	my $params;	43	my $params;
43	if ( $report ) {	44	if ( $report ) {
44	my $sql = $report->{savedsql};	45	my $sql = $report->savedsql;
45	my $updated_sql = C4::Reports::Guided::convert_sql( $sql );	46	my $updated_sql = C4::Reports::Guided::convert_sql( $sql );
46	$params = { msg => 'can_be_updated', updated_sql => $updated_sql, current_sql => $sql };	47	$params = { msg => 'can_be_updated', updated_sql => $updated_sql, current_sql => $sql };
47	} else {	48	} else {

Lines 22-27 use Modern::Perl; Link Here

(-)a/svc/report (-3 / +4 lines)
22		22
23	use C4::Auth;	23	use C4::Auth;
24	use C4::Reports::Guided;	24	use C4::Reports::Guided;
		25	use Koha::Reports;
25	use JSON;	26	use JSON;
26	use CGI qw ( -utf8 );	27	use CGI qw ( -utf8 );
27		28
Lines 33-39 my $report_id = $query->param('id'); Link Here
33	my $report_name = $query->param('name');	34	my $report_name = $query->param('name');
34	my $report_annotation = $query->param('annotated');	35	my $report_annotation = $query->param('annotated');
35		36
36	my $report_rec = get_saved_report( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );	37	my $report_rec = Koha::Reports->find( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
37	if (!$report_rec) { die "There is no such report.\n"; }	38	if (!$report_rec) { die "There is no such report.\n"; }
38		39
39	my @sql_params = $query->param('sql_params');	40	my @sql_params = $query->param('sql_params');
Lines 60-66 if ($cache_active) { Link Here
60	unless ($json_text) {	61	unless ($json_text) {
61	my $offset = 0;	62	my $offset = 0;
62	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;	63	my $limit = C4::Context->preference("SvcMaxReportRows") \|\| 10;
63	my $sql = $report_rec->{savedsql};	64	my $sql = $report_rec->savedsql;
64		65
65	# convert SQL parameters to placeholders	66	# convert SQL parameters to placeholders
66	$sql =~ s/(<<.*?>>)/\?/g;	67	$sql =~ s/(<<.*?>>)/\?/g;
Lines 77-83 unless ($json_text) { Link Here
77	$json_text = encode_json($lines);	78	$json_text = encode_json($lines);
78		79
79	if ($cache_active) {	80	if ($cache_active) {
80	$cache->set_in_cache( $cache_key, $json_text, { expiry => $report_rec->{cache_expiry} } );	81	$cache->set_in_cache( $cache_key, $json_text, { expiry => $report_rec->cache_expiry } );
81	}	82	}
82	}	83	}
83	else {	84	else {