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

(-)a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-toolbar.inc (+1 lines)
Lines 50-55 Link Here
50
        [% IF ( execute ) %]
50
        [% IF ( execute ) %]
51
            [% BLOCK params %]
51
            [% BLOCK params %]
52
                [%- FOREACH param IN sql_params %]&sql_params=[% param %][% END %]
52
                [%- FOREACH param IN sql_params %]&sql_params=[% param %][% END %]
53
                    [%- FOREACH param_name IN param_names %]&param_name=[% param_name %][% END %]
53
            [%- END %]
54
            [%- END %]
54
55
55
56
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (+1 lines)
Lines 663-668 canned reports and writing custom SQL reports.</p> Link Here
663
        <fieldset class="rows">
663
        <fieldset class="rows">
664
            <ol>
664
            <ol>
665
            [% FOREACH sql_param IN sql_params %]
665
            [% FOREACH sql_param IN sql_params %]
666
                <input name="param_name" value="[% sql_param.name %]" type="hidden" />
666
                [% IF sql_param.input == 'date' %]
667
                [% IF sql_param.input == 'date' %]
667
                    <li>
668
                    <li>
668
                    <label for="date_[% sql_param_entry %][% loop.count %]">[% sql_param.entry %]:</label> <input id="date_[% sql_param_entry %][% loop.count %]" type="text" value="" size="10" name="sql_params" class="datepicker" />
669
                    <label for="date_[% sql_param_entry %][% loop.count %]">[% sql_param.entry %]:</label> <input id="date_[% sql_param_entry %][% loop.count %]" type="text" value="" size="10" name="sql_params" class="datepicker" />
(-)a/reports/guided_reports.pl (-6 / +17 lines)
Lines 673-678 elsif ($phase eq 'Run this report'){ Link Here
673
    my $offset     = 0;
673
    my $offset     = 0;
674
    my $report_id  = $input->param('reports');
674
    my $report_id  = $input->param('reports');
675
    my @sql_params = $input->multi_param('sql_params');
675
    my @sql_params = $input->multi_param('sql_params');
676
    my @param_names = $input->multi_param('param_name');
677
676
    # offset algorithm
678
    # offset algorithm
677
    if ($input->param('page')) {
679
    if ($input->param('page')) {
678
        $offset = ($input->param('page') - 1) * $limit;
680
        $offset = ($input->param('page') - 1) * $limit;
Lines 696-703 elsif ($phase eq 'Run this report'){ Link Here
696
            my @split = split /<<|>>/,$sql;
698
            my @split = split /<<|>>/,$sql;
697
            my @tmpl_parameters;
699
            my @tmpl_parameters;
698
            my @authval_errors;
700
            my @authval_errors;
701
            my %uniq_params;
699
            for(my $i=0;$i<($#split/2);$i++) {
702
            for(my $i=0;$i<($#split/2);$i++) {
700
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
703
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
704
                my $sep = $authorised_value ? "|" : "";
705
                if( defined $uniq_params{$text.$sep.$authorised_value} ){
706
                    next;
707
                } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; }
701
                my $input;
708
                my $input;
702
                my $labelid;
709
                my $labelid;
703
                if ( not defined $authorised_value ) {
710
                if ( not defined $authorised_value ) {
Lines 789-795 elsif ($phase eq 'Run this report'){ Link Here
789
                    };
796
                    };
790
                }
797
                }
791
798
792
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
799
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value };
793
            }
800
            }
794
            $template->param('sql'         => $sql,
801
            $template->param('sql'         => $sql,
795
                            'name'         => $name,
802
                            'name'         => $name,
Lines 799-805 elsif ($phase eq 'Run this report'){ Link Here
799
                            'reports'      => $report_id,
806
                            'reports'      => $report_id,
800
                            );
807
                            );
801
        } else {
808
        } else {
802
            my $sql = get_prepped_report( $sql, @sql_params );
809
            my $sql = get_prepped_report( $sql, \@param_names, \@sql_params);
803
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
810
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
804
            my $total = nb_rows($sql) || 0;
811
            my $total = nb_rows($sql) || 0;
805
            unless ($sth) {
812
            unless ($sth) {
Lines 830-835 elsif ($phase eq 'Run this report'){ Link Here
830
                'pagination_bar'  => pagination_bar($url, $totpages, scalar $input->param('page')),
837
                'pagination_bar'  => pagination_bar($url, $totpages, scalar $input->param('page')),
831
                'unlimited_total' => $total,
838
                'unlimited_total' => $total,
832
                'sql_params'      => \@sql_params,
839
                'sql_params'      => \@sql_params,
840
                'param_names'     => \@param_names,
833
            );
841
            );
834
        }
842
        }
835
    }
843
    }
Lines 844-855 elsif ($phase eq 'Export'){ Link Here
844
    my $report_id      = $input->param('report_id');
852
    my $report_id      = $input->param('report_id');
845
    my $report         = get_saved_report($report_id);
853
    my $report         = get_saved_report($report_id);
846
    my $sql            = $report->{savedsql};
854
    my $sql            = $report->{savedsql};
855
    my @param_names    = $input->multi_param('param_name');
847
    my @sql_params     = $input->multi_param('sql_params');
856
    my @sql_params     = $input->multi_param('sql_params');
848
    my $format         = $input->param('format');
857
    my $format         = $input->param('format');
849
    my $reportname     = $input->param('reportname');
858
    my $reportname     = $input->param('reportname');
850
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
859
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
851
860
852
    $sql = get_prepped_report( $sql, @sql_params );
861
    $sql = get_prepped_report( $sql, \@param_names, \@sql_params );
853
	my ($sth, $q_errors) = execute_query($sql);
862
	my ($sth, $q_errors) = execute_query($sql);
854
    unless ($q_errors and @$q_errors) {
863
    unless ($q_errors and @$q_errors) {
855
        my ( $type, $content );
864
        my ( $type, $content );
Lines 1061-1070 sub create_non_existing_group_and_subgroup { Link Here
1061
1070
1062
# pass $sth and sql_params, get back an executable query
1071
# pass $sth and sql_params, get back an executable query
1063
sub get_prepped_report {
1072
sub get_prepped_report {
1064
    my ($sql, @sql_params ) = @_;
1073
    my ($sql, $param_names, $sql_params ) = @_;
1074
    my %lookup;
1075
    @lookup{@$param_names} = @$sql_params;
1065
    my @split = split /<<|>>/,$sql;
1076
    my @split = split /<<|>>/,$sql;
1077
    my @tmpl_parameters;
1066
    for(my $i=0;$i<$#split/2;$i++) {
1078
    for(my $i=0;$i<$#split/2;$i++) {
1067
        my $quoted = $sql_params[$i];
1079
        my $quoted = @$param_names ? $lookup{ $split[$i*2+1] } : @$sql_params[$i];
1068
        # if there are special regexp chars, we must \ them
1080
        # if there are special regexp chars, we must \ them
1069
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
1081
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
1070
        if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
1082
        if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
1071
- 

Return to bug 9634