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 738-744 canned reports and writing custom SQL reports.</p> Link Here
738
            <input type="hidden" name="recordtype" value="biblio" />
738
            <input type="hidden" name="recordtype" value="biblio" />
739
            <input type="hidden" name="op" value="list" />
739
            <input type="hidden" name="op" value="list" />
740
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
740
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
741
            <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 %]
741
            <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 %]
742
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
742
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
743
        </form>
743
        </form>
744
744
Lines 746-752 canned reports and writing custom SQL reports.</p> Link Here
746
            <input type="hidden" name="recordtype" value="biblio" />
746
            <input type="hidden" name="recordtype" value="biblio" />
747
            <input type="hidden" name="op" value="list" />
747
            <input type="hidden" name="op" value="list" />
748
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
748
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
749
            <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 %]
749
            <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 %]
750
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
750
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
751
        </form>
751
        </form>
752
752
Lines 755-761 canned reports and writing custom SQL reports.</p> Link Here
755
            [% FOREACH result IN results %]
755
            [% FOREACH result IN results %]
756
                [% FOREACH cells IN result.cells %]
756
                [% FOREACH cells IN result.cells %]
757
                    [% place = loop.index %]
757
                    [% place = loop.index %]
758
                    [% IF header_row.$place.cell == 'itemnumber' %]
758
                    [% IF header_row.$place.cell == 'itemnumber' || header_types.item(header_row.$place.cell) == 'itemnumber'  %]
759
                        [% SET batch_itemnumbers = 1 %]
759
                        [% SET batch_itemnumbers = 1 %]
760
                        <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
760
                        <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
761
                    [% END %]
761
                    [% END %]
Lines 769-775 canned reports and writing custom SQL reports.</p> Link Here
769
            [% FOREACH result IN results %]
769
            [% FOREACH result IN results %]
770
                [% FOREACH cells IN result.cells %]
770
                [% FOREACH cells IN result.cells %]
771
                    [% place = loop.index %]
771
                    [% place = loop.index %]
772
                    [% IF header_row.$place.cell == 'itemnumber' %]
772
                    [% IF header_row.$place.cell == 'itemnumber' || header_types.item(header_row.$place.cell) == 'itemnumber'  %]
773
                        [% SET batch_itemnumbers = 1 %]
773
                        [% SET batch_itemnumbers = 1 %]
774
                        <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
774
                        <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
775
                    [% END %]
775
                    [% END %]
Lines 781-787 canned reports and writing custom SQL reports.</p> Link Here
781
            [% FOREACH result IN results %]
781
            [% FOREACH result IN results %]
782
                [% FOREACH cells IN result.cells %]
782
                [% FOREACH cells IN result.cells %]
783
                    [% place = loop.index %]
783
                    [% place = loop.index %]
784
                    [% IF header_row.$place.cell == 'biblionumber' %]
784
                    [% IF header_row.$place.cell == 'biblionumber' || header_types.item(header_row.$place.cell) == 'biblionumber' %]
785
                        [% SET batch_biblionumbers = 1 %]
785
                        [% SET batch_biblionumbers = 1 %]
786
                        <input type="hidden" class="bib_to_list" name="biblionumber" value="[% cells.cell | html %]" />
786
                        <input type="hidden" class="bib_to_list" name="biblionumber" value="[% cells.cell | html %]" />
787
                    [% END %]
787
                    [% END %]
Lines 792-798 canned reports and writing custom SQL reports.</p> Link Here
792
        <form method="POST" action="/cgi-bin/koha/tools/modborrowers.pl" id="batch_patron_modification">
792
        <form method="POST" action="/cgi-bin/koha/tools/modborrowers.pl" id="batch_patron_modification">
793
            <input type="hidden" name="op" value="show" />
793
            <input type="hidden" name="op" value="show" />
794
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
794
            [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
795
            <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 %]
795
            <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 %]
796
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
796
[% cells.cell | html %][% END %][% END %][% END %]</textarea>
797
        </form>
797
        </form>
798
798
Lines 805-811 canned reports and writing custom SQL reports.</p> Link Here
805
                    </button>
805
                    </button>
806
                    <ul class="dropdown-menu" aria-labelledby="batch_mod_menu">
806
                    <ul class="dropdown-menu" aria-labelledby="batch_mod_menu">
807
                        [% FOREACH header_ro IN header_row %]
807
                        [% FOREACH header_ro IN header_row %]
808
                            [% IF header_ro.cell == 'biblionumber' %]
808
                            [% IF header_ro.cell == 'biblionumber' || header_types.item( header_ro.cell ) == 'biblionumber' %]
809
                                <li class="dropdown-header">Bibliographic records</li>
809
                                <li class="dropdown-header">Bibliographic records</li>
810
                                <li>
810
                                <li>
811
                                    <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>
811
                                    <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 817-823 canned reports and writing custom SQL reports.</p> Link Here
817
                                    <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
                                    <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>
818
                                </li>
818
                                </li>
819
                            [% END %]
819
                            [% END %]
820
                            [% IF header_ro.cell == 'itemnumber' %]
820
                            [% IF header_ro.cell == 'itemnumber' || header_types.item( header_ro.cell ) == 'itemnumber' %]
821
                                [% IF ( batch_biblionumbers ) %]
821
                                [% IF ( batch_biblionumbers ) %]
822
                                    <li role="separator" class="divider"></li>
822
                                    <li role="separator" class="divider"></li>
823
                                [% END %]
823
                                [% END %]
Lines 829-835 canned reports and writing custom SQL reports.</p> Link Here
829
                                    <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
                                    <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>
830
                                </li>
830
                                </li>
831
                            [% END %]
831
                            [% END %]
832
                            [% IF header_ro.cell == 'cardnumber' %]
832
                            [% IF header_ro.cell == 'cardnumber' || header_types.item( header_ro.cell ) == 'cardnumber' %]
833
                                [% IF ( batch_biblionumbers || batch_itemnumbers ) %]
833
                                [% IF ( batch_biblionumbers || batch_itemnumbers ) %]
834
                                    <li role="separator" class="divider"></li>
834
                                    <li role="separator" class="divider"></li>
835
                                [% END %]
835
                                [% 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