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

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-9 / +9 lines)
Lines 737-743 canned reports and writing custom SQL reports.</p> Link Here
737
            <input type="hidden" name="recordtype" value="biblio" />
737
            <input type="hidden" name="recordtype" value="biblio" />
738
            <input type="hidden" name="op" value="list" />
738
            <input type="hidden" name="op" value="list" />
739
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
739
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
740
            <textarea style="display:none" name="recordnumber_list" id="recordnumber_list">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'biblionumber' %][% SET batch_biblionumbers = 1 %]
740
            <textarea style="display:none" name="recordnumber_list" id="recordnumber_list">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'biblionumber' || header_types.item(header_row.$place.cell) == 'biblionumber' %][% SET batch_biblionumbers = 1 %]
741
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
741
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
742
        </form>
742
        </form>
743
743
Lines 745-751 canned reports and writing custom SQL reports.</p> Link Here
745
            <input type="hidden" name="recordtype" value="biblio" />
745
            <input type="hidden" name="recordtype" value="biblio" />
746
            <input type="hidden" name="op" value="list" />
746
            <input type="hidden" name="op" value="list" />
747
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
747
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
748
            <textarea style="display:none" name="recordnumber_list" id="recordnumber_list">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'biblionumber' %][% SET batch_biblionumbers = 1 %]
748
            <textarea style="display:none" name="recordnumber_list" id="recordnumber_list">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'biblionumber' || header_types.item(header_row.$place.cell) == 'biblionumber' %][% SET batch_biblionumbers = 1 %]
749
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
749
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
750
        </form>
750
        </form>
751
751
Lines 754-760 canned reports and writing custom SQL reports.</p> Link Here
754
            [% FOREACH result IN results %]
754
            [% FOREACH result IN results %]
755
                [% FOREACH cells IN result.cells %]
755
                [% FOREACH cells IN result.cells %]
756
                    [% place = loop.index %]
756
                    [% place = loop.index %]
757
                    [% IF header_row.$place.cell == 'itemnumber' %]
757
                    [% IF header_row.$place.cell == 'itemnumber' || header_types.item(header_row.$place.cell) == 'itemnumber'  %]
758
                        [% SET batch_itemnumbers = 1 %]
758
                        [% SET batch_itemnumbers = 1 %]
759
                        <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
759
                        <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
760
                    [% END %]
760
                    [% END %]
Lines 768-774 canned reports and writing custom SQL reports.</p> Link Here
768
            [% FOREACH result IN results %]
768
            [% FOREACH result IN results %]
769
                [% FOREACH cells IN result.cells %]
769
                [% FOREACH cells IN result.cells %]
770
                    [% place = loop.index %]
770
                    [% place = loop.index %]
771
                    [% IF header_row.$place.cell == 'itemnumber' %]
771
                    [% IF header_row.$place.cell == 'itemnumber' || header_types.item(header_row.$place.cell) == 'itemnumber'  %]
772
                        [% SET batch_itemnumbers = 1 %]
772
                        [% SET batch_itemnumbers = 1 %]
773
                        <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
773
                        <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
774
                    [% END %]
774
                    [% END %]
Lines 780-786 canned reports and writing custom SQL reports.</p> Link Here
780
            [% FOREACH result IN results %]
780
            [% FOREACH result IN results %]
781
                [% FOREACH cells IN result.cells %]
781
                [% FOREACH cells IN result.cells %]
782
                    [% place = loop.index %]
782
                    [% place = loop.index %]
783
                    [% IF header_row.$place.cell == 'biblionumber' %]
783
                    [% IF header_row.$place.cell == 'biblionumber' || header_types.item(header_row.$place.cell) == 'biblionumber' %]
784
                        [% SET batch_biblionumbers = 1 %]
784
                        [% SET batch_biblionumbers = 1 %]
785
                        <input type="hidden" class="bib_to_list" name="biblionumber" value="[% cells.cell | html %]" />
785
                        <input type="hidden" class="bib_to_list" name="biblionumber" value="[% cells.cell | html %]" />
786
                    [% END %]
786
                    [% END %]
Lines 791-797 canned reports and writing custom SQL reports.</p> Link Here
791
        <form method="POST" action="/cgi-bin/koha/tools/modborrowers.pl" id="batch_patron_modification">
791
        <form method="POST" action="/cgi-bin/koha/tools/modborrowers.pl" id="batch_patron_modification">
792
            <input type="hidden" name="op" value="show" />
792
            <input type="hidden" name="op" value="show" />
793
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
793
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
794
            <textarea style="display:none" name="cardnumberlist" id="cardnumberlist">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'cardnumber' %][% SET batch_cardnumbers = 1 %]
794
            <textarea style="display:none" name="cardnumberlist" id="cardnumberlist">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'cardnumber' || header_types.item(header_row.$place.cell) == 'cardnumber' %][% SET batch_cardnumbers = 1 %]
795
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
795
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
796
        </form>
796
        </form>
797
797
Lines 804-810 canned reports and writing custom SQL reports.</p> Link Here
804
                    </button>
804
                    </button>
805
                    <ul class="dropdown-menu" aria-labelledby="batch_mod_menu">
805
                    <ul class="dropdown-menu" aria-labelledby="batch_mod_menu">
806
                        [% FOREACH header_ro IN header_row %]
806
                        [% FOREACH header_ro IN header_row %]
807
                            [% IF header_ro.cell == 'biblionumber' %]
807
                            [% IF header_ro.cell == 'biblionumber' || header_types.item( header_ro.cell ) == 'biblionumber' %]
808
                                <li class="dropdown-header">Bibliographic records</li>
808
                                <li class="dropdown-header">Bibliographic records</li>
809
                                <li>
809
                                <li>
810
                                    <a href="#" data-submit="batch_record_modification" data-toggle="tooltip" data-placement="right" title="Send visible records to batch record modification" class="batch_op send_to_record_mod">Batch record modification</a>
810
                                    <a href="#" data-submit="batch_record_modification" data-toggle="tooltip" data-placement="right" title="Send visible records to batch record modification" class="batch_op send_to_record_mod">Batch record modification</a>
Lines 816-822 canned reports and writing custom SQL reports.</p> Link Here
816
                                    <a href="#" data-submit="batch_add_to_list" data-toggle="tooltip" data-placement="right" title="Send visible records to a list" class="batch_op send_to_list">Add to list</a>
816
                                    <a href="#" data-submit="batch_add_to_list" data-toggle="tooltip" data-placement="right" title="Send visible records to a list" class="batch_op send_to_list">Add to list</a>
817
                                </li>
817
                                </li>
818
                            [% END %]
818
                            [% END %]
819
                            [% IF header_ro.cell == 'itemnumber' %]
819
                            [% IF header_ro.cell == 'itemnumber' || header_types.item( header_ro.cell ) == 'itemnumber' %]
820
                                [% IF ( batch_biblionumbers ) %]
820
                                [% IF ( batch_biblionumbers ) %]
821
                                    <li role="separator" class="divider"></li>
821
                                    <li role="separator" class="divider"></li>
822
                                [% END %]
822
                                [% END %]
Lines 828-834 canned reports and writing custom SQL reports.</p> Link Here
828
                                    <a href="#" data-submit="batch_item_deletion" data-toggle="tooltip" data-placement="right" title="Send visible items to batch item deletion" class="batch_op send_to_item_del">Batch item deletion</a>
828
                                    <a href="#" data-submit="batch_item_deletion" data-toggle="tooltip" data-placement="right" title="Send visible items to batch item deletion" class="batch_op send_to_item_del">Batch item deletion</a>
829
                                </li>
829
                                </li>
830
                            [% END %]
830
                            [% END %]
831
                            [% IF header_ro.cell == 'cardnumber' %]
831
                            [% IF header_ro.cell == 'cardnumber' || header_types.item( header_ro.cell ) == 'cardnumber' %]
832
                                [% IF ( batch_biblionumbers || batch_itemnumbers ) %]
832
                                [% IF ( batch_biblionumbers || batch_itemnumbers ) %]
833
                                    <li role="separator" class="divider"></li>
833
                                    <li role="separator" class="divider"></li>
834
                                [% END %]
834
                                [% END %]
(-)a/opac/svc/report (+1 lines)
Lines 61-66 unless ($json_text) { Link Here
61
61
62
    # convert SQL parameters to placeholders
62
    # convert SQL parameters to placeholders
63
    $sql =~ s/(<<.*?>>)/\?/g;
63
    $sql =~ s/(<<.*?>>)/\?/g;
64
    $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g;
64
65
65
    my ( $sth, $errors ) =
66
    my ( $sth, $errors ) =
66
      execute_query( $sql, $offset, $limit, \@sql_params, $report_id );
67
      execute_query( $sql, $offset, $limit, \@sql_params, $report_id );
(-)a/reports/guided_reports.pl (-2 / +14 lines)
Lines 825-831 elsif ($phase eq 'Run this report'){ Link Here
825
                            'reports'      => $report_id,
825
                            'reports'      => $report_id,
826
                            );
826
                            );
827
        } else {
827
        } else {
828
            my $sql = get_prepped_report( $sql, \@param_names, \@sql_params);
828
            my ($sql,$header_types) = get_prepped_report( $sql, \@param_names, \@sql_params);
829
            $template->param(header_types => $header_types);
829
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
830
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
830
            my ($sth2, $errors2) = execute_query($sql);
831
            my ($sth2, $errors2) = execute_query($sql);
831
            my $total = nb_rows($sql) || 0;
832
            my $total = nb_rows($sql) || 0;
Lines 1076-1081 sub create_non_existing_group_and_subgroup { Link Here
1076
# pass $sth and sql_params, get back an executable query
1077
# pass $sth and sql_params, get back an executable query
1077
sub get_prepped_report {
1078
sub get_prepped_report {
1078
    my ($sql, $param_names, $sql_params ) = @_;
1079
    my ($sql, $param_names, $sql_params ) = @_;
1080
1081
    # First we split out the placeholders
1082
    my @split = split /\[\[|\]\]/,$sql;
1083
    my $headers;
1084
    for(my $i=0;$i<$#split/2;$i++){ #The placeholders are always the odd elements of the array
1085
        my ($type,$name) = split /\|/,$split[$i*2+1]; # We split them on '|'
1086
        $headers->{$name} = $type; # Store as a lookup for the template
1087
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; #Quote any special characters so we can replace the placeholders
1088
        $sql =~ s/\[\[$split[$i*2+1]\]\]/$type AS $name/; # Remove placeholders from SQL
1089
    }
1090
1079
    my %lookup;
1091
    my %lookup;
1080
    @lookup{@$param_names} = @$sql_params;
1092
    @lookup{@$param_names} = @$sql_params;
1081
    my @split = split /<<|>>/,$sql;
1093
    my @split = split /<<|>>/,$sql;
Lines 1090-1094 sub get_prepped_report { Link Here
1090
        $quoted = C4::Context->dbh->quote($quoted);
1102
        $quoted = C4::Context->dbh->quote($quoted);
1091
        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
1103
        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
1092
    }
1104
    }
1093
    return $sql;
1105
    return $sql,$headers;
1094
}
1106
}
(-)a/svc/report (-1 / +1 lines)
Lines 66-71 unless ($json_text) { Link Here
66
66
67
    # convert SQL parameters to placeholders
67
    # convert SQL parameters to placeholders
68
    $sql =~ s/(<<.*?>>)/\?/g;
68
    $sql =~ s/(<<.*?>>)/\?/g;
69
    $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g;
69
70
70
    my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, \@sql_params, $report_id );
71
    my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, \@sql_params, $report_id );
71
    if ($sth) {
72
    if ($sth) {
72
- 

Return to bug 23390