Attachment #23299 for bug #5338

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




# with Koha; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

use Modern::Perl;
#use warnings; FIXME - Bug 2505 this module needs a lot of repair to run clean under warnings
use CGI;
use Carp;


      GetParametersFromSQL
      IsAuthorisedValueValid
      ValidateSQLParameters
      nb_rows update_sql
    );
}


    [PAT  => "Patrons"],
    [ACQ  => "Acquisition"],
    [ACC  => "Accounts"],
    [SER  => "Serials"],
);
my $AREA_NAME_SQL_SNIPPET
  = "CASE report_area " .

    PAT  => ['borrowers'],
    ACQ  => [ 'aqorders', 'biblio', 'items' ],
    ACC  => [ 'borrowers', 'accountlines' ],
    SER  => [ 'serial', 'serialitems', 'subscription', 'subscriptionhistory', 'subscriptionroutinglist', 'biblioitems', 'biblio', 'aqbooksellers' ],
);
my %keys = (
    CIRC => [ 'statistics.borrowernumber=borrowers.borrowernumber',

    ACQ  => [ 'aqorders.biblionumber=biblio.biblionumber',
              'biblio.biblionumber=items.biblionumber' ],
    ACC  => ['borrowers.borrowernumber=accountlines.borrowernumber'],
    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'],
);

# have to do someting here to know if its dropdown, free text, date etc

    PAT  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
    ACQ  => ['aqorders.datereceived|date'],
    ACC  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
    SER  => ['subscription.startdate|date', 'subscription.enddate|date', 'subscription.periodicity', 'subscription.callnumber', 'subscription.location', 'subscription.branchcode'],
);

# Adds itemtypes to criteria, according to the syspref

    foreach my $localcrit (@$crit) {
        my ( $value, $type )   = split( /\|/, $localcrit );
        my ( $table, $column ) = split( /\./, $value );
        for ( $type ) {
            when ( 'textrange' ) {
                my %temp;
                $temp{'name'}        = $value;
                $temp{'from'}        = "from_" . $value;
                $temp{'to'}          = "to_" . $value;
                $temp{'textrange'}   = 1;
                $temp{'description'} = $column_defs->{$value};
                push @criteria_array, \%temp;
        elsif ($type eq 'date') {
            my %temp;
            $temp{'name'}        = $value;
            $temp{'date'}        = 1;
            $temp{'description'} = $column_defs->{$value};
            push @criteria_array, \%temp;
        }
        elsif ($type eq 'daterange') {
            my %temp;
            $temp{'name'}        = $value;
            $temp{'from'}        = "from_" . $value;
            $temp{'to'}          = "to_" . $value;
            $temp{'daterange'}   = 1;
            $temp{'description'} = $column_defs->{$value};
            push @criteria_array, \%temp;
        }
        else {
            my $query =
            "SELECT distinct($column) as availablevalues FROM $table";
            my $sth = $dbh->prepare($query);
            $sth->execute();
            my @values;
            # push the runtime choosing option
            my $list;
            $list='branches' if $column eq 'branchcode' or $column eq 'holdingbranch' or $column eq 'homebranch';
            $list='categorycode' if $column eq 'categorycode';
            $list='itemtype' if $column eq 'itype';
            $list='ccode' if $column eq 'ccode';
            # TODO : improve to let the librarian choose the description at runtime
            push @values, { availablevalues => "<<$column".($list?"|$list":'').">>" };
            while ( my $row = $sth->fetchrow_hashref() ) {
                push @values, $row;
                if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 };
            }
            when ( 'date' ) {
                my %temp;
                $temp{'name'}        = $value;
                $temp{'date'}        = 1;
                $temp{'description'} = $column_defs->{$value};
                push @criteria_array, \%temp;
            }
            when ( 'daterange' ) {
                my %temp;
                $temp{'name'}        = $value;
                $temp{'from'}        = "from_" . $value;
                $temp{'to'}          = "to_" . $value;
                $temp{'daterange'}   = 1;
                $temp{'description'} = $column_defs->{$value};
                push @criteria_array, \%temp;
            }
            default {
                my $query =
                "SELECT distinct($column) as availablevalues FROM $table";
                my $sth = $dbh->prepare($query);
                $sth->execute();
                my @values;
                # push the runtime choosing option
                my $list;
                $list='branches' if $column eq 'branchcode' or $column eq 'holdingbranch' or $column eq 'homebranch';
                $list='categorycode' if $column eq 'categorycode';
                $list='itemtype' if $column eq 'itype';
                $list='ccode' if $column eq 'ccode';
                # TODO : improve to let the librarian choose the description at runtime
                push @values, {
                    availablevalues => "<<$column" . ( $list ? "|$list" : '' ) . ">>",
                    display_value   => "<<$column" . ( $list ? "|$list" : '' ) . ">>",
                };
                while ( my $row = $sth->fetchrow_hashref() ) {
                    if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 }
                    else { $row->{display_value} = _get_display_value( $row->{'availablevalues'}, $column ); }
                    push @values, $row;
                }
                $sth->finish();

                my %temp;
                $temp{'name'}        = $value;
                $temp{'description'} = $column_defs->{$value};
                $temp{'values'}      = \@values;

                push @criteria_array, \%temp;
            }
        }

    }
    return ( \@criteria_array );
}

    return \@problematic_parameters;
}

sub _get_display_value {
    my ( $original_value, $column ) = @_;
    for ( $column ) {
        when ("periodicity") {
            my $dbh = C4::Context->dbh();
            my $query = "SELECT description FROM subscription_frequencies WHERE id = ?";
            my $sth   = $dbh->prepare($query);
            $sth->execute($original_value);
            return $sth->fetchrow;
        }
    }
    return $original_value;
}

1;
__END__


Lines 139-141 biblioitems.lccn LCCN Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/columns.def (+6 lines)
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

Lines 557-563 canned reports and writing custom SQL reports.</p> Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-2 / +1 lines)
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

Lines 17-24 package C4::Reports::Guided; Link Here

(-)a/C4/Reports/Guided.pm (-51 / +74 lines)
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