From 3122d1fdbf9318aeb5eb3c4a36a91a0f1af4e553 Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Mon, 29 Oct 2007 13:19:53 -0500 Subject: [PATCH] Can now add definitions to reports Signed-off-by: Chris Cormack Signed-off-by: Joshua Ferraro --- C4/Reports.pm | 38 +++++++++++++++++-- .../modules/reports/guided_reports_start.tmpl | 8 ++++ reports/guided_reports.pl | 12 +++++- 3 files changed, 52 insertions(+), 6 deletions(-) diff --git a/C4/Reports.pm b/C4/Reports.pm index 509af85b9b..336be04636 100644 --- a/C4/Reports.pm +++ b/C4/Reports.pm @@ -217,18 +217,18 @@ This is what get_columns returns. =cut sub build_query { - my ( $columns, $criteria, $orderby, $area, $totals ) = @_; + my ( $columns, $criteria, $orderby, $area, $totals, $definition ) = @_; ### $orderby my $keys = $keys{$area}; my $tables = $table_areas{$area}; my $sql = - _build_query( $tables, $columns, $criteria, $keys, $orderby, $totals ); + _build_query( $tables, $columns, $criteria, $keys, $orderby, $totals, $definition ); return ($sql); } sub _build_query { - my ( $tables, $columns, $criteria, $keys, $orderby, $totals ) = @_; + my ( $tables, $columns, $criteria, $keys, $orderby, $totals, $definition) = @_; ### $orderby # $keys is an array of joining constraints my $dbh = C4::Context->dbh(); @@ -245,6 +245,21 @@ sub _build_query { $criteria =~ s/AND/WHERE/; $query .= " $criteria"; } + if ($definition){ + my @definitions = split(',',$definition); + my $deftext; + foreach my $def (@definitions){ + my $defin=get_from_dictionary('',$def); + $deftext .=" ".$defin->[0]->{'saved_sql'}; + } + if ($query =~ /WHERE/i){ + $query .= $deftext; + } + else { + $deftext =~ s/AND/WHERE/; + $query .= $deftext; + } + } if ($totals) { my $groupby; my @totcolumns = split( ',', $totals ); @@ -487,10 +502,25 @@ sub save_dictionary { } sub get_from_dictionary { + my ($area,$id) = @_; my $dbh = C4::Context->dbh(); my $query = "SELECT * FROM reports_dictionary"; + if ($area){ + $query.= " WHERE area = ?"; + } + elsif ($id){ + $query.= " WHERE id = ?" + } my $sth = $dbh->prepare($query); - $sth->execute; + if ($id){ + $sth->execute($id); + } + elsif ($area) { + $sth->execute($area); + } + else { + $sth->execute(); + } my @loop; while (my $data = $sth->fetchrow_hashref()){ push @loop,$data; 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 2db2d778e1..cd0c0777f1 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 @@ -140,6 +140,12 @@ align : "Tl"

+ +Dictionary Definitions
+ +">
+ + @@ -151,6 +157,7 @@ align : "Tl" "> "> "> +"> "> "> @@ -176,6 +183,7 @@ align : "Tl" "> "> "> +"> "> "> diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index eb9929019b..2b508027a4 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -115,6 +115,7 @@ elsif ( $phase eq 'Choose these columns' ) { my $type = $input->param('type'); my @columns = $input->param('columns'); my $column = join( ',', @columns ); + my $definitions = get_from_dictionary($area); $template->param( 'build4' => 1, 'area' => $area, @@ -122,13 +123,16 @@ elsif ( $phase eq 'Choose these columns' ) { 'column' => $column, ); my $criteria = get_criteria($area); - $template->param( 'criteria' => $criteria ); + $template->param( 'criteria' => $criteria, + 'definitions' => $definitions); } elsif ( $phase eq 'Choose these criteria' ) { my $area = $input->param('area'); my $type = $input->param('type'); my $column = $input->param('column'); + my @definitions = $input->param('definition'); + my $definition = join (',',@definitions); my @criteria = $input->param('criteria_column'); my $query_criteria; foreach my $crit (@criteria) { @@ -143,6 +147,7 @@ elsif ( $phase eq 'Choose these criteria' ) { 'area' => $area, 'type' => $type, 'column' => $column, + 'definition' => $definition, 'criteriastring' => $query_criteria, ); @@ -185,6 +190,7 @@ elsif ( $phase eq 'Choose Totals' ) { my $type = $input->param('type'); my $column = $input->param('column'); my $criteria = $input->param('criteria'); + my $definition = $input->param('definition'); my @total_by = $input->param('total_by'); my $totals; foreach my $total (@total_by) { @@ -199,6 +205,7 @@ elsif ( $phase eq 'Choose Totals' ) { 'column' => $column, 'criteriastring' => $criteria, 'totals' => $totals, + 'definition' => $definition, ); # get columns @@ -232,6 +239,7 @@ elsif ( $phase eq 'Build Report' ) { my $column = $input->param('column'); my $crit = $input->param('criteria'); my $totals = $input->param('totals'); + my $definition = $input->param('definition'); # my @criteria = split( ',', $crit ); my $query_criteria=$crit; # split the columns up by , @@ -251,7 +259,7 @@ elsif ( $phase eq 'Build Report' ) { # get the sql my $sql = - build_query( \@columns, $query_criteria, $query_orderby, $area, $totals ); + build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition ); $template->param( 'showreport' => 1, 'sql' => $sql, -- 2.39.2