From 31597c6762c0f13a0f59af250cb520e4eeb6bbdf Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Mon, 29 Oct 2007 13:19:52 -0500 Subject: [PATCH] Dictionary now working Have to integrate with the rest of guided reports Signed-off-by: Chris Cormack Signed-off-by: Joshua Ferraro --- C4/Reports.pm | 90 ++++++++++++- koha-tmpl/intranet-tmpl/prog/en/columns.def | 117 ++++++++++++++++ reports/guided_reports.pl | 139 +++++++++++++++++++- 3 files changed, 338 insertions(+), 8 deletions(-) create mode 100644 koha-tmpl/intranet-tmpl/prog/en/columns.def diff --git a/C4/Reports.pm b/C4/Reports.pm index 181ac35889..509af85b9b 100644 --- a/C4/Reports.pm +++ b/C4/Reports.pm @@ -24,6 +24,7 @@ use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); use C4::Context; use C4::Output; # use Smart::Comments; +# use Data::Dumper; # set the version for version checking $VERSION = 0.01; @@ -31,7 +32,9 @@ $VERSION = 0.01; @ISA = qw(Exporter); @EXPORT = qw(get_report_types get_report_areas get_columns build_query get_criteria - save_report get_saved_reports execute_query get_saved_report create_compound run_compound); + save_report get_saved_reports execute_query get_saved_report create_compound run_compound + get_column_type get_distinct_values save_dictionary get_from_dictionary + delete_definition); our %table_areas; $table_areas{'1'} = @@ -420,6 +423,91 @@ sub create_compound { return ($mastertables,$subtables); } +=item get_column_type($column) + +This takes a column name of the format table.column and will return what type it is +(free text, set values, date) + +=cut + +sub get_column_type { + my ($tablecolumn) = @_; + my ($table,$column) = split(/\./,$tablecolumn); + my $dbh = C4::Context->dbh(); + my $catalog; + my $schema; + + # mysql doesnt support a column selection, set column to % + my $tempcolumn='%'; + my $sth = $dbh->column_info( $catalog, $schema, $table, $tempcolumn ) || die $dbh->errstr; + while (my $info = $sth->fetchrow_hashref()){ + if ($info->{'COLUMN_NAME'} eq $column){ + #column we want + if ($info->{'TYPE_NAME'} eq 'CHAR'){ + $info->{'TYPE_NAME'} = 'distinct'; + } + return $info->{'TYPE_NAME'}; + } + } + $sth->finish(); +} + +=item get_distinct_values($column) + +Given a column name, return an arrary ref of hashrefs suitable for use as a tmpl_loop +with the distinct values of the column + +=cut + +sub get_distinct_values { + my ($tablecolumn) = @_; + my ($table,$column) = split(/\./,$tablecolumn); + my $dbh = C4::Context->dbh(); + 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(); + return \@values; +} + +sub save_dictionary { + my ($name,$description,$sql,$area) = @_; + my $dbh = C4::Context->dbh(); + my $query = "INSERT INTO reports_dictionary (name,description,saved_sql,area,date_created,date_modified) + VALUES (?,?,?,?,now(),now())"; + my $sth = $dbh->prepare($query); + $sth->execute($name,$description,$sql,$area) || return 0; + $sth->finish(); + return 1; +} + +sub get_from_dictionary { + my $dbh = C4::Context->dbh(); + my $query = "SELECT * FROM reports_dictionary"; + my $sth = $dbh->prepare($query); + $sth->execute; + my @loop; + while (my $data = $sth->fetchrow_hashref()){ + push @loop,$data; + + } + $sth->finish(); + return (\@loop); +} + +sub delete_definition { + my ($id) = @_; + my $dbh = C4::Context->dbh(); + my $query = "DELETE FROM reports_dictionary WHERE id = ?"; + my $sth = $dbh->prepare($query); + $sth->execute($id); + $sth->finish(); + } =head1 AUTHOR Chris Cormack diff --git a/koha-tmpl/intranet-tmpl/prog/en/columns.def b/koha-tmpl/intranet-tmpl/prog/en/columns.def new file mode 100644 index 0000000000..2a2f1b57ec --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/columns.def @@ -0,0 +1,117 @@ +borrowers.borrowernumber Borrower Number +borrowers.cardnumber Card Number +borrowers.surname Surname +borrowers.firstname Firstname +borrowers.title Title +borrowers.othernames Other Names +borrowers.initials Initials +borrowers.streetaddress Street Address +borrowers.suburb Suburb +borrowers.city City +borrowers.phone Phone +borrowers.emailaddress Email +borrowers.faxnumber Fax +borrowers.textmessaging Opac Message +borrowers.altstreetaddress Alternative Street Address +borrowers.altsuburb Alternative Suburb +borrowers.altcity Alternative City +borrowers.altphone Alternative Phone +borrowers.dateofbirth Date of Birth +borrowers.branchcode Branch Code +borrowers.categorycode Borrowers Category +borrowers.dateenrolled Date Enrolled +borrowers.gonenoaddress Address Missing +borrowers.lost Lost Card +borrowers.debarred Debarred +borrowers.studentnumber Student Id number +borrowers.school School +borrowers.contactname Contact Name +borrowers.borrowernotes Circulation Notes +borrowers.guarantor Guarantor(parent) +borrowers.area Area +borrowers.ethnicity Ethnicity +borrowers.ethnotes Ethnicity Notes +borrowers.sex Gender +borrowers.expiry Expiry Date +borrowers.altnotes Alternative Contact Notes +borrowers.altrelationship Relationship of Alternative Contact +borrowers.streetcity Streetcity?? +borrowers.phoneday Work Phone +borrowers.preferredcont Preferred method of Contact +borrowers.physstreet Physical Street Address +borrowers.password Password +borrowers.flags Warnings +borrowers.userid Login ID +borrowers.homezipcode Zip/Post Code +borrowers.zipcode Zip/Post Code 2? +borrowers.sort1 Sorting Column +borrowers.sort2 Sorting Column 2 +borrowers.cellph Cellphone number +borrowers.borlog +borrowers.checkhist +items.itemnumber Item Number (koha internal) +items.biblionumber Biblio Number (koha internal) +items.multivolumepart Item one part of a Multi volume set +items.biblioitemnumber Biblioitem Number (koha internal) +items.barcode Barcode +items.dateaccessioned Accession Date +items.booksellerid Supplier ID +items.homebranch Permanent Branch Code +items.price Price +items.replacementprice Replacement Price +items.replacementpricedate Date Replacement Price was set +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.notforloan Item Not for loan +items.itemlost Item Lost +items.wthdrawn Item Cancelled +items.itemcallnumber Item Call Number +items.issues Issues Count +items.renewals Renewals Count +items.reserves Reserves Count +items.restricted Item Restricted +items.binding Item needs binding +items.itemnotes Item Notes +items.holdingbranch Current Branch +items.paidfor Has Item been lost and then paid for +items.timestamp Timestamp +items.location Location +items.spystatus +items.spydescr +items.itemlog +items.spycopydata +statistics.datetime Statistics Date and Time +statistics.branch Branch Code +statistics.proccode Type of Procedure +statistics.value Value +statistics.type Type +statistics.other +statistics.usercode User Code +statistics.itemnumber Item Number +statistics.itemtype Item Type +statistics.borrowernumber Borrower Number +biblioitems.biblioitemnumber Biblioitem Number +biblioitems.biblionumber Biblio Number +biblioitems.volume Volume Number +biblioitems.number Number +biblioitems.classification Classification +biblioitems.itemtype Itemtype +biblioitems.isbn ISBN +biblioitems.issn ISSN +biblioitems.dewey Dewey/Classification +biblioitems.subclass Sub Classification +biblioitems.publicationyear Publication Date +biblioitems.publishercode Publisher +biblioitems.volumedate Volume Date +biblioitems.volumeddesc Volume Information +biblioitems.timestamp Timestamp +biblioitems.illus Illustrator +biblioitems.pages Number of Pages +biblioitems.notes Notes +biblioitems.size Size +biblioitems.place Place of Publication +biblioitems.lccn LCCN +biblioitems.marc MARC Blob +biblioitems.url URL diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index fe5bc03049..eb9929019b 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -16,13 +16,13 @@ # You should have received a copy of the GNU General Public License along with # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, # Suite 330, Boston, MA 02111-1307 USA - +use CGI::Carp qw(fatalsToBrowser); use strict; use C4::Auth; use CGI; use C4::Output; use C4::Reports; -use CGI::Carp qw(fatalsToBrowser); + =head1 NAME Script to control the guided report creation @@ -354,8 +354,10 @@ elsif ($phase eq 'View Dictionary'){ } ); my $areas = C4::Reports::get_report_areas(); + my $definitions = get_from_dictionary(); $template->param( 'areas' => $areas , - 'start_dictionary' => 1, + 'start_dictionary' => 1, + 'definitions' => $definitions, ); } elsif ($phase eq 'Add New Definition'){ @@ -370,8 +372,9 @@ elsif ($phase eq 'Add New Definition'){ debug => 1, } ); + $template->param( 'new_dictionary' => 1, - ); + ); } elsif ($phase eq 'New Term step 2'){ @@ -387,11 +390,13 @@ elsif ($phase eq 'New Term step 2'){ } ); my $areas = C4::Reports::get_report_areas(); + my $definition_name=$input->param('definition_name'); + my $definition_description=$input->param('definition_description'); $template->param( 'step_2' => 1, 'areas' => $areas, + 'definition_name' => $definition_name, + 'definition_description' => $definition_description, ); - my $definition_name=$input->param('definition_name'); - my $definition_description=$input->param('definition_description'); } elsif ($phase eq 'New Term step 3'){ @@ -408,14 +413,18 @@ elsif ($phase eq 'New Term step 3'){ ); my $area = $input->param('areas'); my $columns = get_columns($area); + my $definition_name=$input->param('definition_name'); + my $definition_description=$input->param('definition_description'); $template->param( 'step_3' => 1, 'area' => $area, 'columns' => $columns, + 'definition_name' => $definition_name, + 'definition_description' => $definition_description, ); } elsif ($phase eq 'New Term step 4'){ - # Choosing the columns + # Choosing the values ( $template, $borrowernumber, $cookie ) = get_template_and_user( { template_name => "reports/dictionary.tmpl", @@ -426,10 +435,126 @@ elsif ($phase eq 'New Term step 4'){ debug => 1, } ); + my $area=$input->param('area'); + my $definition_name=$input->param('definition_name'); + my $definition_description=$input->param('definition_description'); + my @columns = $input->param('columns'); + my $columnstring = join (',',@columns); + my @column_loop; + foreach my $column (@columns){ + my %tmp_hash; + $tmp_hash{'name'}=$column; + my $type =get_column_type($column); + if ($type eq 'distinct'){ + my $values = get_distinct_values($column); + $tmp_hash{'values'} = $values; + $tmp_hash{'distinct'} = 1; + + } + if ($type eq 'DATE'){ + $tmp_hash{'date'}=1; + } +# else { +# die $type;# +# } + push @column_loop,\%tmp_hash; + } + $template->param( 'step_4' => 1, + 'area' => $area, + 'definition_name' => $definition_name, + 'definition_description' => $definition_description, + 'columns' => \@column_loop, + 'columnstring' => $columnstring, + + ); +} + +elsif ($phase eq 'New Term step 5'){ + # Confirmation screen + ( $template, $borrowernumber, $cookie ) = get_template_and_user( + { + template_name => "reports/dictionary.tmpl", + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => { editcatalogue => 1 }, + debug => 1, + } + ); + my $area = $input->param('area'); + my $columnstring = $input->param('columnstring'); + my $definition_name=$input->param('definition_name'); + my $definition_description=$input->param('definition_description'); + my @criteria = $input->param('criteria_column'); + my $query_criteria; + my @criteria_loop; + foreach my $crit (@criteria) { + my $value = $input->param( $crit . "_value" ); + if ($value) { + $query_criteria .= " AND $crit='$value'"; + my %tmp_hash; + $tmp_hash{'name'}=$crit; + $tmp_hash{'value'} = $value; + push @criteria_loop,\%tmp_hash; + } + + $value = $input->param( $crit . "_start_value" ); + if ($value) { + $query_criteria .= " AND $crit > '$value'"; + my %tmp_hash; + $tmp_hash{'name'}="$crit Start"; + $tmp_hash{'value'} = $value; + push @criteria_loop,\%tmp_hash; + } + $value = $input->param( $crit . "_end_value" ); + if ($value) { + $query_criteria .= " AND $crit <= '$value'"; + my %tmp_hash; + $tmp_hash{'name'}="$crit End"; + $tmp_hash{'value'} = $value; + push @criteria_loop,\%tmp_hash; + } + } + $template->param( 'step_5' => 1, + 'area' => $area, + 'definition_name' => $definition_name, + 'definition_description' => $definition_description, + 'query' => $query_criteria, + 'columnstring' => $columnstring, + 'criteria_loop' => \@criteria_loop, ); } +elsif ($phase eq 'New Term step 6'){ + # Choosing the columns + ( $template, $borrowernumber, $cookie ) = get_template_and_user( + { + template_name => "reports/dictionary.tmpl", + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => { editcatalogue => 1 }, + debug => 1, + } + ); + my $area = $input->param('area'); + my $definition_name=$input->param('definition_name'); + my $definition_description=$input->param('definition_description'); + my $sql=$input->param('sql'); + save_dictionary($definition_name,$definition_description,$sql,$area); + $template->param( 'step_6' => 1, + 'area' => $area, + 'definition_name' => $definition_name, + 'definition_description' => $definition_description, + ); +} +elsif ($phase eq 'Delete Definition'){ + $no_html=1; + my $id = $input->param('id'); + delete_definition($id); + print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=View%20Dictionary"); + } if (!$no_html){ output_html_with_http_headers $input, $cookie, $template->output; -- 2.20.1