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

(-)a/C4/Reports/Guided.pm (-51 / +74 lines)
Lines 17-24 package C4::Reports::Guided; Link Here
17
# with Koha; if not, write to the Free Software Foundation, Inc.,
17
# with Koha; if not, write to the Free Software Foundation, Inc.,
18
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
18
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
19
19
20
use strict;
20
use Modern::Perl;
21
#use warnings; FIXME - Bug 2505 this module needs a lot of repair to run clean under warnings
22
use CGI;
21
use CGI;
23
use Carp;
22
use Carp;
24
23
Lines 49-54 BEGIN { Link Here
49
      GetParametersFromSQL
48
      GetParametersFromSQL
50
      IsAuthorisedValueValid
49
      IsAuthorisedValueValid
51
      ValidateSQLParameters
50
      ValidateSQLParameters
51
      nb_rows update_sql
52
    );
52
    );
53
}
53
}
54
54
Lines 78-83 my @REPORT_AREA = ( Link Here
78
    [PAT  => "Patrons"],
78
    [PAT  => "Patrons"],
79
    [ACQ  => "Acquisition"],
79
    [ACQ  => "Acquisition"],
80
    [ACC  => "Accounts"],
80
    [ACC  => "Accounts"],
81
    [SER  => "Serials"],
81
);
82
);
82
my $AREA_NAME_SQL_SNIPPET
83
my $AREA_NAME_SQL_SNIPPET
83
  = "CASE report_area " .
84
  = "CASE report_area " .
Lines 93-98 my %table_areas = ( Link Here
93
    PAT  => ['borrowers'],
94
    PAT  => ['borrowers'],
94
    ACQ  => [ 'aqorders', 'biblio', 'items' ],
95
    ACQ  => [ 'aqorders', 'biblio', 'items' ],
95
    ACC  => [ 'borrowers', 'accountlines' ],
96
    ACC  => [ 'borrowers', 'accountlines' ],
97
    SER  => [ 'serial', 'serialitems', 'subscription', 'subscriptionhistory', 'subscriptionroutinglist', 'biblioitems', 'biblio', 'aqbooksellers' ],
96
);
98
);
97
my %keys = (
99
my %keys = (
98
    CIRC => [ 'statistics.borrowernumber=borrowers.borrowernumber',
100
    CIRC => [ 'statistics.borrowernumber=borrowers.borrowernumber',
Lines 104-109 my %keys = ( Link Here
104
    ACQ  => [ 'aqorders.biblionumber=biblio.biblionumber',
106
    ACQ  => [ 'aqorders.biblionumber=biblio.biblionumber',
105
              'biblio.biblionumber=items.biblionumber' ],
107
              'biblio.biblionumber=items.biblionumber' ],
106
    ACC  => ['borrowers.borrowernumber=accountlines.borrowernumber'],
108
    ACC  => ['borrowers.borrowernumber=accountlines.borrowernumber'],
109
    SER  => [ 'serial.serialid=serialitems.serialid', 'serial.subscriptionid=subscription.subscriptionid', 'serial.subscriptionid=subscriptionhistory.subscriptionid', 'serial.subscriptionid=subscriptionroutinglist.subscriptionid', 'biblioitems.biblionumber=serial.biblionumber', 'biblio.biblionumber=biblioitems.biblionumber', 'subscription.aqbooksellerid=aqbooksellers.id'],
107
);
110
);
108
111
109
# have to do someting here to know if its dropdown, free text, date etc
112
# have to do someting here to know if its dropdown, free text, date etc
Lines 120-125 my %criteria = ( Link Here
120
    PAT  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
123
    PAT  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
121
    ACQ  => ['aqorders.datereceived|date'],
124
    ACQ  => ['aqorders.datereceived|date'],
122
    ACC  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
125
    ACC  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
126
    SER  => ['subscription.startdate|date', 'subscription.enddate|date', 'subscription.periodicity', 'subscription.callnumber', 'subscription.location', 'subscription.branchcode'],
123
);
127
);
124
128
125
# Adds itemtypes to criteria, according to the syspref
129
# Adds itemtypes to criteria, according to the syspref
Lines 333-391 sub get_criteria { Link Here
333
    foreach my $localcrit (@$crit) {
337
    foreach my $localcrit (@$crit) {
334
        my ( $value, $type )   = split( /\|/, $localcrit );
338
        my ( $value, $type )   = split( /\|/, $localcrit );
335
        my ( $table, $column ) = split( /\./, $value );
339
        my ( $table, $column ) = split( /\./, $value );
336
        if ($type eq 'textrange') {
340
        for ( $type ) {
337
            my %temp;
341
            when ( 'textrange' ) {
338
            $temp{'name'}        = $value;
342
                my %temp;
339
            $temp{'from'}        = "from_" . $value;
343
                $temp{'name'}        = $value;
340
            $temp{'to'}          = "to_" . $value;
344
                $temp{'from'}        = "from_" . $value;
341
            $temp{'textrange'}   = 1;
345
                $temp{'to'}          = "to_" . $value;
342
            $temp{'description'} = $column_defs->{$value};
346
                $temp{'textrange'}   = 1;
343
            push @criteria_array, \%temp;
347
                $temp{'description'} = $column_defs->{$value};
344
        }
348
                push @criteria_array, \%temp;
345
        elsif ($type eq 'date') {
346
            my %temp;
347
            $temp{'name'}        = $value;
348
            $temp{'date'}        = 1;
349
            $temp{'description'} = $column_defs->{$value};
350
            push @criteria_array, \%temp;
351
        }
352
        elsif ($type eq 'daterange') {
353
            my %temp;
354
            $temp{'name'}        = $value;
355
            $temp{'from'}        = "from_" . $value;
356
            $temp{'to'}          = "to_" . $value;
357
            $temp{'daterange'}   = 1;
358
            $temp{'description'} = $column_defs->{$value};
359
            push @criteria_array, \%temp;
360
        }
361
        else {
362
            my $query =
363
            "SELECT distinct($column) as availablevalues FROM $table";
364
            my $sth = $dbh->prepare($query);
365
            $sth->execute();
366
            my @values;
367
            # push the runtime choosing option
368
            my $list;
369
            $list='branches' if $column eq 'branchcode' or $column eq 'holdingbranch' or $column eq 'homebranch';
370
            $list='categorycode' if $column eq 'categorycode';
371
            $list='itemtype' if $column eq 'itype';
372
            $list='ccode' if $column eq 'ccode';
373
            # TODO : improve to let the librarian choose the description at runtime
374
            push @values, { availablevalues => "<<$column".($list?"|$list":'').">>" };
375
            while ( my $row = $sth->fetchrow_hashref() ) {
376
                push @values, $row;
377
                if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 };
378
            }
349
            }
379
            $sth->finish();
350
            when ( 'date' ) {
351
                my %temp;
352
                $temp{'name'}        = $value;
353
                $temp{'date'}        = 1;
354
                $temp{'description'} = $column_defs->{$value};
355
                push @criteria_array, \%temp;
356
            }
357
            when ( 'daterange' ) {
358
                my %temp;
359
                $temp{'name'}        = $value;
360
                $temp{'from'}        = "from_" . $value;
361
                $temp{'to'}          = "to_" . $value;
362
                $temp{'daterange'}   = 1;
363
                $temp{'description'} = $column_defs->{$value};
364
                push @criteria_array, \%temp;
365
            }
366
            default {
367
                my $query =
368
                "SELECT distinct($column) as availablevalues FROM $table";
369
                my $sth = $dbh->prepare($query);
370
                $sth->execute();
371
                my @values;
372
                # push the runtime choosing option
373
                my $list;
374
                $list='branches' if $column eq 'branchcode' or $column eq 'holdingbranch' or $column eq 'homebranch';
375
                $list='categorycode' if $column eq 'categorycode';
376
                $list='itemtype' if $column eq 'itype';
377
                $list='ccode' if $column eq 'ccode';
378
                # TODO : improve to let the librarian choose the description at runtime
379
                push @values, {
380
                    availablevalues => "<<$column" . ( $list ? "|$list" : '' ) . ">>",
381
                    display_value   => "<<$column" . ( $list ? "|$list" : '' ) . ">>",
382
                };
383
                while ( my $row = $sth->fetchrow_hashref() ) {
384
                    if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 }
385
                    else { $row->{display_value} = _get_display_value( $row->{'availablevalues'}, $column ); }
386
                    push @values, $row;
387
                }
388
                $sth->finish();
380
389
381
            my %temp;
390
                my %temp;
382
            $temp{'name'}        = $value;
391
                $temp{'name'}        = $value;
383
            $temp{'description'} = $column_defs->{$value};
392
                $temp{'description'} = $column_defs->{$value};
384
            $temp{'values'}      = \@values;
393
                $temp{'values'}      = \@values;
385
394
386
            push @criteria_array, \%temp;
395
                push @criteria_array, \%temp;
396
            }
387
        }
397
        }
388
389
    }
398
    }
390
    return ( \@criteria_array );
399
    return ( \@criteria_array );
391
}
400
}
Lines 1008-1013 sub ValidateSQLParameters { Link Here
1008
    return \@problematic_parameters;
1017
    return \@problematic_parameters;
1009
}
1018
}
1010
1019
1020
sub _get_display_value {
1021
    my ( $original_value, $column ) = @_;
1022
    for ( $column ) {
1023
        when ("periodicity") {
1024
            my $dbh = C4::Context->dbh();
1025
            my $query = "SELECT description FROM subscription_frequencies WHERE id = ?";
1026
            my $sth   = $dbh->prepare($query);
1027
            $sth->execute($original_value);
1028
            return $sth->fetchrow;
1029
        }
1030
    }
1031
    return $original_value;
1032
}
1033
1011
1;
1034
1;
1012
__END__
1035
__END__
1013
1036
(-)a/koha-tmpl/intranet-tmpl/prog/en/columns.def (+6 lines)
Lines 139-141 biblioitems.lccn LCCN Link Here
139
biblioitems.marcxml	MARC blob
139
biblioitems.marcxml	MARC blob
140
biblioitems.url	URL
140
biblioitems.url	URL
141
biblioitems.title	Title
141
biblioitems.title	Title
142
subscription.startdate	Start date
143
subscription.enddate	End date
144
subscription.periodicity	Periodicity
145
subscription.callnumber	Call number
146
subscription.location	Location
147
subscription.branchcode	Library
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-2 / +1 lines)
Lines 557-563 canned reports and writing custom SQL reports.</p> Link Here
557
            <td>
557
            <td>
558
                <select name="[% criteri.name %]_value">
558
                <select name="[% criteri.name %]_value">
559
                [% FOREACH value IN criteri.values %]
559
                [% FOREACH value IN criteri.values %]
560
                <option value="[% value.availablevalues %]">[% IF ( value.default ) %]Default[% ELSE %][% value.availablevalues |html %][% END %]</option>
560
                <option value="[% value.availablevalues %]">[% IF ( value.default ) %]Default[% ELSE %][% value.display_value |html %][% END %]</option>
561
                [% END %]
561
                [% END %]
562
                </select>
562
                </select>
563
            </td>
563
            </td>
564
- 

Return to bug 5338