From b87b3273a9339ce7469d969865416fbee1f458b4 Mon Sep 17 00:00:00 2001 From: Matthias Meusburger Date: Tue, 15 Sep 2009 16:59:37 +0200 Subject: [PATCH] MT 1443 : better reports on items --- C4/Reports/Guided.pm | 79 +++++++---- koha-tmpl/intranet-tmpl/prog/en/columns.def | 1 + .../modules/reports/guided_reports_start.tmpl | 123 ++++++++++++------ reports/guided_reports.pl | 31 ++++- 4 files changed, 164 insertions(+), 70 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 3c655c4546..a2efd533bc 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -29,6 +29,7 @@ use C4::Output; use C4::Dates; use XML::Simple; use XML::Dumper; +use Switch; use C4::Debug; # use Smart::Comments; # use Data::Dumper; @@ -81,7 +82,7 @@ $criteria{'1'} = [ 'items.dateaccessioned|date' ]; $criteria{'2'} = - [ 'items.holdingbranch', 'items.homebranch' ,'items.itemlost', 'items.location', 'items.ccode']; + [ 'items.itemnumber|textrange', 'items.biblionumber|textrange', 'items.barcode|textrange', 'items.holdingbranch', 'items.homebranch', 'items.onloan|daterange', 'items.ccode', 'items.itemcallnumber|textrange', 'items.itype', 'items.itemlost', 'items.location' ]; $criteria{'3'} = ['borrowers.branchcode']; $criteria{'4'} = ['aqorders.datereceived|date']; $criteria{'5'} = ['borrowers.branchcode']; @@ -305,31 +306,57 @@ sub get_criteria { foreach my $localcrit (@$crit) { my ( $value, $type ) = split( /\|/, $localcrit ); my ( $table, $column ) = split( /\./, $value ); - if ( $type eq 'date' ) { - my %temp; - $temp{'name'} = $value; - $temp{'date'} = 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; - while ( my $row = $sth->fetchrow_hashref() ) { - push @values, $row; - ### $row; - } - $sth->finish(); - my %temp; - $temp{'name'} = $value; - $temp{'description'} = $column_defs->{$value}; - $temp{'values'} = \@values; - push @criteria_array, \%temp; - } + switch ($type) { + case '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; + } + + case 'date' { + my %temp; + $temp{'name'} = $value; + $temp{'date'} = 1; + $temp{'description'} = $column_defs->{$value}; + push @criteria_array, \%temp; + } + + case '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; + while ( my $row = $sth->fetchrow_hashref() ) { + 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 ); } diff --git a/koha-tmpl/intranet-tmpl/prog/en/columns.def b/koha-tmpl/intranet-tmpl/prog/en/columns.def index d53685a7fb..2b7cf8260f 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/columns.def +++ b/koha-tmpl/intranet-tmpl/prog/en/columns.def @@ -64,6 +64,7 @@ items.datelastborrowed Date Item last issued items.datelastseen Date Item was last seen by Koha items.multivolume Volume Number (if part of a multivolume work) items.stack Is the item in the stack? +items.onloan On loan items.notforloan Item Not for loan items.itemlost Item Lost items.wthdrawn Item Cancelled diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl index a172fb494a..a2b333d9ac 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl @@ -158,8 +158,10 @@ canned reports and writing custom SQL reports.

- -
+ + + +
@@ -213,8 +215,9 @@ canned reports and writing custom SQL reports.

- - + + +
@@ -223,39 +226,79 @@ canned reports and writing custom SQL reports.

-" /> -" /> -" /> -
Step 4 of 6: Select Criteria to Limit on - - - - - - - - -
" value="" /> -_value" name="_value" value="" -/>/lib/calendar/cal.gif" id="buttonfrom" style="cursor: pointer;" alt="Show Calendar" title="Show Calendar" /> - -
-
+ " /> + " /> + " /> +
Step 4 of 6: Select Criteria to Limit on + + + + + + + + + + + + + + + + + + + + + + +
+ " value="" /> + + + _value" name="_value" value="" /> + /lib/calendar/cal.gif" id="buttonfrom" style="cursor: pointer;" alt="Show Calendar" title="Show Calendar" /> + +
from + _value" name="_value" value="" /> to + _value" name="_value" value="" /> +
from + _value" name="from__value" value="" /> + /lib/calendar/cal.gif" id="buttonfromfrom_" style="cursor: pointer;" alt="Show Calendar" title="Show Calendar" /> + + to + _value" name="to__value" value="" /> + /lib/calendar/cal.gif" id="buttonfromto_" style="cursor: pointer;" alt="Show Calendar" title="Show Calendar" /> + +
+ +
+
Dictionary Definitions @@ -267,7 +310,8 @@ TMPL_VAR NAME="id" -->" />
-
+ + @@ -298,7 +342,8 @@ NAME="name" -->">
-
+ + diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 913e317436..d0910de534 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -193,11 +193,32 @@ elsif ( $phase eq 'Choose these criteria' ) { my $query_criteria; foreach my $crit (@criteria) { my $value = $input->param( $crit . "_value" ); - ($value) or next; - if ($value =~ C4::Dates->regexp('syspref')) { - $value = C4::Dates->new($value)->output("iso"); - } - $query_criteria .= " AND $crit='$value'"; + + # If value is not defined, then it may be range values + if (!$value) { + + my $fromvalue = $input->param( "from_" . $crit . "_value" ); + my $tovalue = $input->param( "to_" . $crit . "_value" ); + + # If the range values are dates + if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { + $fromvalue = C4::Dates->new($fromvalue)->output("iso"); + $tovalue = C4::Dates->new($tovalue)->output("iso"); + } + + if ($fromvalue && $tovalue) { + $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'"; + } + + } else { + + # If value is a date + if ($value =~ C4::Dates->regexp('syspref')) { + $value = C4::Dates->new($value)->output("iso"); + } + $query_criteria .= " AND $crit='$value'"; + } + warn $query_criteria; } $template->param( -- 2.39.2