From 2f03b732e3184e1bb7434686496d4c23f85623f8 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Thu, 4 Jul 2013 16:52:58 +0200 Subject: [PATCH] [SIGNED-OFF] Bug 11425: Add item search form in staff interface Item search is available at catalogue/itemsearch.pl (link is in catalogue/search.pl) It only uses SQL (not Zebra) * Use DataTables and server-side processing to be able to filter on individual columns after the first search is done. * Allow to export results in CSV * With Javascript disabled, search form still works (and CSV export too) There is the possibility to define "Custom search fields" in a new admin page admin/items_search_fields.pl (link is in admin/admin-home.pl) A custom item search field is defined by: * a name: its unique identifier * a label: the text displayed to the user * a MARC field/subfield: the field/subfield to query (it uses ExtractValue) * an authorised values list (optional): if defined the list is displayed in the search form New Perl dependency: Template::Plugin::JSON::Escape Test plan: 1/ Apply the patch and run updatedatabase.pl 2/ Go to advanced search (staff interface), then click on "Go to item search" 3/ Play with the search form! :) In the 3rd fieldset you can add as many fields as you want and combine them with boolean operators (AND, OR). You can use SQL jokers characters (%, _) You can output to screen (in a DataTables table) or to a CSV file. 4/ In the DataTables table, play with filters and try sorting columns. 5/ Disable Javascript (with Firefox: extensions NoScript or YesScript, or in about:config 'javascript.enabled' = false 6/ Reload the search page and do some searches on screen output. (there is no sorting or filtering features, but there is still pagination) 7/ Try again CSV output. 8/ You can re-enable Javascript. 9/ Go to Administration > Items search fields 10/ Add a new field. Example for title (in UNIMARC): Name: title Label: Title MARC field: 200 MARC subfield: a Authorised values category: None (add another field with an authorised values category to see the difference). 11/ As you are there try to update and delete some fields. 12/ Go back to items search form. You can see in the 3rd fieldset that your fields have appeared in the selects. 13/ Try searching on them. 14/ I think you're done :) Signed-off-by: Bernardo Gonzalez Kriegel Work as described. Good new option. No koha-qa errors --- C4/Installer/PerlDependencies.pm | 5 + C4/Items.pm | 197 ++++++++- C4/SQLHelper.pm | 33 ++ Koha/Item/Search/Field.pm | 107 +++++ acqui/check_uniqueness.pl | 2 +- admin/items_search_field.pl | 63 +++ admin/items_search_fields.pl | 81 ++++ catalogue/itemsearch.pl | 298 ++++++++++++++ installer/data/mysql/kohastructure.sql | 17 + installer/data/mysql/updatedatabase.pl | 20 + .../intranet-tmpl/prog/en/css/itemsearchform.css | 44 ++ .../en/includes/admin-items-search-field-form.inc | 58 +++ .../intranet-tmpl/prog/en/includes/admin-menu.inc | 1 + .../en/includes/catalogue/itemsearch_item.csv.inc | 4 + .../prog/en/includes/catalogue/itemsearch_item.inc | 23 ++ .../en/includes/catalogue/itemsearch_item.json.inc | 18 + .../en/includes/catalogue/itemsearch_items.inc | 49 +++ .../prog/en/modules/admin/admin-home.tt | 2 + .../prog/en/modules/admin/items_search_field.tt | 41 ++ .../prog/en/modules/admin/items_search_fields.tt | 95 +++++ .../prog/en/modules/catalogue/advsearch.tt | 1 + .../prog/en/modules/catalogue/itemsearch.csv.tt | 4 + .../prog/en/modules/catalogue/itemsearch.json.tt | 12 + .../prog/en/modules/catalogue/itemsearch.tt | 425 ++++++++++++++++++++ 24 files changed, 1579 insertions(+), 21 deletions(-) create mode 100644 Koha/Item/Search/Field.pm create mode 100755 admin/items_search_field.pl create mode 100755 admin/items_search_fields.pl create mode 100755 catalogue/itemsearch.pl create mode 100644 koha-tmpl/intranet-tmpl/prog/en/css/itemsearchform.css create mode 100644 koha-tmpl/intranet-tmpl/prog/en/includes/admin-items-search-field-form.inc create mode 100644 koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.csv.inc create mode 100644 koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.inc create mode 100644 koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.json.inc create mode 100644 koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_items.inc create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/items_search_field.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/items_search_fields.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/catalogue/itemsearch.csv.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/catalogue/itemsearch.json.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/catalogue/itemsearch.tt diff --git a/C4/Installer/PerlDependencies.pm b/C4/Installer/PerlDependencies.pm index 2bea1c6..29a84e4 100644 --- a/C4/Installer/PerlDependencies.pm +++ b/C4/Installer/PerlDependencies.pm @@ -539,6 +539,11 @@ our $PERL_DEPS = { 'required' => '1', 'min_ver' => '0.03', }, + 'Template::Plugin::JSON::Escape' => { + 'usage' => 'Core', + 'required' => '1', + 'min_ver' => '0.02', + }, 'Data::Paginator' => { 'usage' => 'Core', 'required' => '0', diff --git a/C4/Items.pm b/C4/Items.pm index 12dce85..adafe47 100644 --- a/C4/Items.pm +++ b/C4/Items.pm @@ -35,6 +35,7 @@ use DateTime::Format::MySQL; use Data::Dumper; # used as part of logging item record changes, not just for # debugging; so please don't remove this use Koha::DateUtils qw/dt_from_string/; +use C4::SQLHelper qw(GetColumns); use vars qw($VERSION @ISA @EXPORT); @@ -85,6 +86,7 @@ BEGIN { GetAnalyticsCount GetItemHolds + SearchItemsByField SearchItems PrepareItemrecordDisplay @@ -2549,39 +2551,194 @@ sub GetItemHolds { return $holds; } -# Return the list of the column names of items table -sub _get_items_columns { - my $dbh = C4::Context->dbh; - my $sth = $dbh->column_info(undef, undef, 'items', '%'); - $sth->execute; - my $results = $sth->fetchall_hashref('COLUMN_NAME'); - return keys %$results; +=head2 SearchItemsByField + + my $items = SearchItemsByField($field, $value); + +SearchItemsByField will search for items on a specific given field. +For instance you can search all items with a specific stocknumber like this: + + my $items = SearchItemsByField('stocknumber', $stocknumber); + +=cut + +sub SearchItemsByField { + my ($field, $value) = @_; + + my $filters = [ { + field => $field, + query => $value, + } ]; + + my ($results) = SearchItems($filters); + return $results; +} + +sub _SearchItems_build_where_fragment { + my ($filter) = @_; + + my $where_fragment; + if (exists($filter->{conjunction})) { + my (@where_strs, @where_args); + foreach my $f (@{ $filter->{filters} }) { + my $fragment = _SearchItems_build_where_fragment($f); + if ($fragment) { + push @where_strs, $fragment->{str}; + push @where_args, @{ $fragment->{args} }; + } + } + my $where_str = ''; + if (@where_strs) { + $where_str = '(' . join (' ' . $filter->{conjunction} . ' ', @where_strs) . ')'; + $where_fragment = { + str => $where_str, + args => \@where_args, + }; + } + } else { + my @columns = GetColumns('items'); + push @columns, GetColumns('biblio'); + push @columns, GetColumns('biblioitems'); + my @operators = qw(= != > < >= <= like); + my $field = $filter->{field}; + if ( (0 < grep /^$field$/, @columns) or (substr($field, 0, 5) eq 'marc:') ) { + my $op = $filter->{operator}; + my $query = $filter->{query}; + + if (!$op or (0 == grep /^$op$/, @operators)) { + $op = '='; # default operator + } + + my $column; + if ($field =~ /^marc:(\d{3})(?:\$(\w))?$/) { + my $marcfield = $1; + my $marcsubfield = $2; + my $xpath; + if ($marcfield < 10) { + $xpath = "//record/controlfield[\@tag=\"$marcfield\"]"; + } else { + $xpath = "//record/datafield[\@tag=\"$marcfield\"]/subfield[\@code=\"$marcsubfield\"]"; + } + $column = "ExtractValue(marcxml, '$xpath')"; + } else { + $column = $field; + } + + if (ref $query eq 'ARRAY') { + if ($op eq '=') { + $op = 'IN'; + } elsif ($op eq '!=') { + $op = 'NOT IN'; + } + $where_fragment = { + str => "$column $op (" . join (',', ('?') x @$query) . ")", + args => $query, + }; + } else { + $where_fragment = { + str => "$column $op ?", + args => [ $query ], + }; + } + } + } + + return $where_fragment; } =head2 SearchItems - my $items = SearchItems($field, $value); + my ($items, $total) = SearchItemsByField($filters, $params); -SearchItems will search for items on a specific given field. -For instance you can search all items with a specific stocknumber like this: +Perform a search among items - my $items = SearchItems('stocknumber', $stocknumber); +$filters is a reference to an array of filters, where each filter is a hash with +the following keys: + +=over 2 + +=item * field: the name of a SQL column in table items + +=item * query: the value to search in this column + +=item * operator: comparison operator. Can be one of = != > < >= <= like + +=back + +A logical AND is used to combine filters. + +$params is a reference to a hash that can contain the following parameters: + +=over 2 + +=item * rows: Number of items to return. 0 returns everything (default: 0) + +=item * page: Page to return (return items from (page-1)*rows to (page*rows)-1) + (default: 1) + +=item * sortby: A SQL column name in items table to sort on + +=item * sortorder: 'ASC' or 'DESC' + +=back =cut sub SearchItems { - my ($field, $value) = @_; + my ($filter, $params) = @_; + + $filter //= {}; + $params //= {}; + return unless ref $filter eq 'HASH'; + return unless ref $params eq 'HASH'; + + # Default parameters + $params->{rows} ||= 0; + $params->{page} ||= 1; + $params->{sortby} ||= 'itemnumber'; + $params->{sortorder} ||= 'ASC'; + + my ($where_str, @where_args); + my $where_fragment = _SearchItems_build_where_fragment($filter); + if ($where_fragment) { + $where_str = $where_fragment->{str}; + @where_args = @{ $where_fragment->{args} }; + } my $dbh = C4::Context->dbh; - my @columns = _get_items_columns; - my $results = []; - if(0 < grep /^$field$/, @columns) { - my $query = "SELECT $field FROM items WHERE $field = ?"; - my $sth = $dbh->prepare( $query ); - $sth->execute( $value ); - $results = $sth->fetchall_arrayref({}); + my $query = q{ + SELECT SQL_CALC_FOUND_ROWS items.* + FROM items + LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber + }; + if (defined $where_str and $where_str ne '') { + $query .= qq{ WHERE $where_str }; } - return $results; + + my @columns = GetColumns('items'); + push @columns, GetColumns('biblio'); + push @columns, GetColumns('biblioitems'); + my $sortby = (0 < grep {$params->{sortby} eq $_} @columns) + ? $params->{sortby} : 'itemnumber'; + my $sortorder = (uc($params->{sortorder}) eq 'ASC') ? 'ASC' : 'DESC'; + $query .= qq{ ORDER BY $sortby $sortorder }; + + my $rows = $params->{rows}; + my @limit_args; + if ($rows > 0) { + my $offset = $rows * ($params->{page}-1); + $query .= qq { LIMIT ?, ? }; + push @limit_args, $offset, $rows; + } + + my $sth = $dbh->prepare($query); + my $rv = $sth->execute(@where_args, @limit_args); + + return unless ($rv); + my ($total_rows) = $dbh->selectrow_array(q{ SELECT FOUND_ROWS() }); + + return ($sth->fetchall_arrayref({}), $total_rows); } diff --git a/C4/SQLHelper.pm b/C4/SQLHelper.pm index b867094..55204eb 100644 --- a/C4/SQLHelper.pm +++ b/C4/SQLHelper.pm @@ -56,6 +56,7 @@ BEGIN { UpdateInTable GetPrimaryKeys clear_columns_cache + GetColumns ); %EXPORT_TAGS = ( all =>[qw( InsertInTable DeleteInTable SearchInTable UpdateInTable GetPrimaryKeys)] ); @@ -296,6 +297,38 @@ sub _get_columns { return $hashref->{$tablename}; } +=head2 GetColumns + + my @columns = GetColumns($tablename); + +Given a tablename, returns an array of columns names. + +=cut + +sub GetColumns { + my ($tablename) = @_; + + return unless $tablename; + return unless $tablename =~ /^[a-zA-Z0-9_]+$/; + + # Get the database handle. + my $dbh = C4::Context->dbh; + + # Pure ANSI SQL goodness. + my $sql = "SELECT * FROM $tablename WHERE 1=0;"; + + # Run the SQL statement to load STH's readonly properties. + my $sth = $dbh->prepare($sql); + my $rv = $sth->execute(); + + my @data; + if ($rv) { + @data = @{$sth->{NAME}}; + } + + return @data; +} + =head2 _filter_columns =over 4 diff --git a/Koha/Item/Search/Field.pm b/Koha/Item/Search/Field.pm new file mode 100644 index 0000000..6b1e137 --- /dev/null +++ b/Koha/Item/Search/Field.pm @@ -0,0 +1,107 @@ +package Koha::Item::Search::Field; + +use Modern::Perl; +use base qw( Exporter ); + +our @EXPORT_OK = qw( + AddItemSearchField + ModItemSearchField + DelItemSearchField + GetItemSearchField + GetItemSearchFields +); + +use C4::Context; + +sub AddItemSearchField { + my ($field) = @_; + + my ( $name, $label, $tagfield, $tagsubfield, $av_category ) = + @$field{qw(name label tagfield tagsubfield authorised_values_category)}; + + my $dbh = C4::Context->dbh; + my $query = q{ + INSERT INTO items_search_fields (name, label, tagfield, tagsubfield, authorised_values_category) + VALUES (?, ?, ?, ?, ?) + }; + my $sth = $dbh->prepare($query); + my $rv = $sth->execute($name, $label, $tagfield, $tagsubfield, $av_category); + + return ($rv) ? $field : undef; +} + +sub ModItemSearchField { + my ($field) = @_; + + my ( $name, $label, $tagfield, $tagsubfield, $av_category ) = + @$field{qw(name label tagfield tagsubfield authorised_values_category)}; + + my $dbh = C4::Context->dbh; + my $query = q{ + UPDATE items_search_fields + SET label = ?, + tagfield = ?, + tagsubfield = ?, + authorised_values_category = ? + WHERE name = ? + }; + my $sth = $dbh->prepare($query); + my $rv = $sth->execute($label, $tagfield, $tagsubfield, $av_category, $name); + + return ($rv) ? $field : undef; +} + +sub DelItemSearchField { + my ($name) = @_; + + my $dbh = C4::Context->dbh; + my $query = q{ + DELETE FROM items_search_fields + WHERE name = ? + }; + my $sth = $dbh->prepare($query); + my $rv = $sth->execute($name); + + my $is_deleted = $rv ? int($rv) : 0; + if (!$is_deleted) { + warn "DelItemSearchField: Field '$name' doesn't exist"; + } + + return $is_deleted; +} + +sub GetItemSearchField { + my ($name) = @_; + + my $dbh = C4::Context->dbh; + my $query = q{ + SELECT * FROM items_search_fields + WHERE name = ? + }; + my $sth = $dbh->prepare($query); + my $rv = $sth->execute($name); + + my $field; + if ($rv) { + $field = $sth->fetchrow_hashref; + } + + return $field; +} + +sub GetItemSearchFields { + my $dbh = C4::Context->dbh; + my $query = q{ + SELECT * FROM items_search_fields + }; + my $sth = $dbh->prepare($query); + my $rv = $sth->execute(); + + my @fields; + if ($rv) { + my $fields = $sth->fetchall_arrayref( {} ); + @fields = @$fields; + } + + return @fields; +} diff --git a/acqui/check_uniqueness.pl b/acqui/check_uniqueness.pl index 8deb195..d8f29fa 100755 --- a/acqui/check_uniqueness.pl +++ b/acqui/check_uniqueness.pl @@ -43,7 +43,7 @@ my @value = $input->param('value[]'); my $r = {}; my $i = 0; for ( my $i=0; $i<@field; $i++ ) { - my $items = C4::Items::SearchItems($field[$i], $value[$i]); + my $items = C4::Items::SearchItemsByField($field[$i], $value[$i]); if ( @$items ) { push @{ $r->{$field[$i]} }, $value[$i]; diff --git a/admin/items_search_field.pl b/admin/items_search_field.pl new file mode 100755 index 0000000..a544959 --- /dev/null +++ b/admin/items_search_field.pl @@ -0,0 +1,63 @@ +#!/usr/bin/perl +# Copyright 2013 BibLibre +# +# This file is part of Koha +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# 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 CGI; + +use C4::Auth; +use C4::Output; +use C4::Koha; + +use Koha::Item::Search::Field qw(GetItemSearchField ModItemSearchField); + +my $cgi = new CGI; + +my ($template, $borrowernumber, $cookie) = get_template_and_user({ + template_name => 'admin/items_search_field.tt', + query => $cgi, + type => 'intranet', + authnotrequired => 0, + flagsrequired => { catalogue => 1 }, +}); + +my $op = $cgi->param('op') || ''; +my $name = $cgi->param('name'); + +if ($op eq 'mod') { + my %vars = $cgi->Vars; + my $field = { name => $name }; + my @params = qw(label tagfield tagsubfield authorised_values_category); + @$field{@params} = @vars{@params}; + if ( $field->{authorised_values_category} eq '' ) { + $field->{authorised_values_category} = undef; + } + $field = ModItemSearchField($field); + my $updated = ($field) ? 1 : 0; + print $cgi->redirect('/cgi-bin/koha/admin/items_search_fields.pl?updated=' . $updated); + exit; +} + +my $field = GetItemSearchField($name); +my $authorised_values_categories = C4::Koha::GetAuthorisedValueCategories(); + +$template->param( + field => $field, + authorised_values_categories => $authorised_values_categories, +); + +output_html_with_http_headers $cgi, $cookie, $template->output; diff --git a/admin/items_search_fields.pl b/admin/items_search_fields.pl new file mode 100755 index 0000000..1ebf243 --- /dev/null +++ b/admin/items_search_fields.pl @@ -0,0 +1,81 @@ +#!/usr/bin/perl +# Copyright 2013 BibLibre +# +# This file is part of Koha +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# 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 CGI; + +use C4::Auth; +use C4::Output; +use C4::Koha; + +use Koha::Item::Search::Field qw(AddItemSearchField GetItemSearchFields DelItemSearchField); + +my $cgi = new CGI; + +my ($template, $borrowernumber, $cookie) = get_template_and_user({ + template_name => 'admin/items_search_fields.tt', + query => $cgi, + type => 'intranet', + authnotrequired => 0, + flagsrequired => { catalogue => 1 }, +}); + +my $op = $cgi->param('op') || ''; + +if ($op eq 'add') { + my %vars = $cgi->Vars; + my $field; + my @params = qw(name label tagfield tagsubfield authorised_values_category); + @$field{@params} = @vars{@params}; + if ( $field->{authorised_values_category} eq '' ) { + $field->{authorised_values_category} = undef; + } + $field = AddItemSearchField($field); + if ($field) { + $template->param(field_added => $field); + } else { + $template->param(field_not_added => 1); + } +} elsif ($op eq 'del') { + my $name = $cgi->param('name'); + my $rv = DelItemSearchField($name); + if ($rv) { + $template->param(field_deleted => 1); + } else { + $template->param(field_not_deleted => 1); + } +} else { + my $updated = $cgi->param('updated'); + if (defined $updated) { + if ($updated) { + $template->param(field_updated => 1); + } else { + $template->param(field_not_updated => 1); + } + } +} + +my @fields = GetItemSearchFields(); +my $authorised_values_categories = C4::Koha::GetAuthorisedValueCategories(); + +$template->param( + fields => \@fields, + authorised_values_categories => $authorised_values_categories, +); + +output_html_with_http_headers $cgi, $cookie, $template->output; diff --git a/catalogue/itemsearch.pl b/catalogue/itemsearch.pl new file mode 100755 index 0000000..cb025a5 --- /dev/null +++ b/catalogue/itemsearch.pl @@ -0,0 +1,298 @@ +#!/usr/bin/perl +# Copyright 2013 BibLibre +# +# This file is part of Koha +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# 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 CGI; + +use JSON; + +use C4::Auth; +use C4::Output; +use C4::Items; +use C4::Biblio; +use C4::Branch; +use C4::Koha; +use C4::ItemType; + +use Koha::Item::Search::Field qw(GetItemSearchFields); + +my $cgi = new CGI; +my %params = $cgi->Vars; + +my $format = $cgi->param('format'); +my ($template_name, $content_type); +if (defined $format and $format eq 'json') { + $template_name = 'catalogue/itemsearch.json.tt'; + $content_type = 'json'; + + # Map DataTables parameters with 'regular' parameters + $cgi->param('rows', $cgi->param('iDisplayLength')); + $cgi->param('page', ($cgi->param('iDisplayStart') / $cgi->param('iDisplayLength')) + 1); + my @columns = split /,/, $cgi->param('sColumns'); + $cgi->param('sortby', $columns[ $cgi->param('iSortCol_0') ]); + $cgi->param('sortorder', $cgi->param('sSortDir_0')); + + my @f = $cgi->param('f'); + my @q = $cgi->param('q'); + push @q, '' if @q == 0; + my @op = $cgi->param('op'); + my @c = $cgi->param('c'); + foreach my $i (0 .. ($cgi->param('iColumns') - 1)) { + my $sSearch = $cgi->param("sSearch_$i"); + if ($sSearch) { + my @words = split /\s+/, $sSearch; + foreach my $word (@words) { + push @f, $columns[$i]; + push @q, "%$word%"; + push @op, 'like'; + push @c, 'and'; + } + } + } + $cgi->param('f', @f); + $cgi->param('q', @q); + $cgi->param('op', @op); + $cgi->param('c', @c); +} elsif (defined $format and $format eq 'csv') { + $template_name = 'catalogue/itemsearch.csv.tt'; + + # Retrieve all results + $cgi->param('rows', 0); +} else { + $format = 'html'; + $template_name = 'catalogue/itemsearch.tt'; + $content_type = 'html'; +} + +my ($template, $borrowernumber, $cookie) = get_template_and_user({ + template_name => $template_name, + query => $cgi, + type => 'intranet', + authnotrequired => 0, + flagsrequired => { catalogue => 1 }, +}); + +my $notforloan_avcode = GetAuthValCode('items.notforloan'); +my $notforloan_values = GetAuthorisedValues($notforloan_avcode); + +if (scalar keys %params > 0) { + # Parameters given, it's a search + + my $filter = { + conjunction => 'AND', + filters => [], + }; + + foreach my $p (qw(homebranch location itype ccode issues datelastborrowed)) { + if (my @q = $cgi->param($p)) { + if ($q[0] ne '') { + my $f = { + field => $p, + query => \@q, + }; + if (my $op = $cgi->param($p . '_op')) { + $f->{operator} = $op; + } + push @{ $filter->{filters} }, $f; + } + } + } + + my @c = $cgi->param('c'); + my @fields = $cgi->param('f'); + my @q = $cgi->param('q'); + my @op = $cgi->param('op'); + + my $f; + for (my $i = 0; $i < @fields; $i++) { + my $field = $fields[$i]; + my $q = shift @q; + my $op = shift @op; + if (defined $q and $q ne '') { + if ($i == 0) { + $f = { + field => $field, + query => $q, + operator => $op, + }; + } else { + my $c = shift @c; + $f = { + conjunction => $c, + filters => [ + $f, { + field => $field, + query => $q, + operator => $op, + } + ], + }; + } + } + } + push @{ $filter->{filters} }, $f; + + # Yes/No parameters + foreach my $p (qw(damaged itemlost)) { + my $v = $cgi->param($p) // ''; + my $f = { + field => $p, + query => 0, + }; + if ($v eq 'yes') { + $f->{operator} = '!='; + push @{ $filter->{filters} }, $f; + } elsif ($v eq 'no') { + $f->{operator} = '='; + push @{ $filter->{filters} }, $f; + } + } + + if (my $itemcallnumber_from = $cgi->param('itemcallnumber_from')) { + push @{ $filter->{filters} }, { + field => 'itemcallnumber', + query => $itemcallnumber_from, + operator => '>=', + }; + } + if (my $itemcallnumber_to = $cgi->param('itemcallnumber_to')) { + push @{ $filter->{filters} }, { + field => 'itemcallnumber', + query => $itemcallnumber_to, + operator => '<=', + }; + } + + my $search_params = { + rows => $cgi->param('rows') // 20, + page => $cgi->param('page') || 1, + sortby => $cgi->param('sortby') || 'itemnumber', + sortorder => $cgi->param('sortorder') || 'asc', + }; + + my ($results, $total_rows) = SearchItems($filter, $search_params); + if ($results) { + # Get notforloan labels + my $notforloan_map = {}; + foreach my $nfl_value (@$notforloan_values) { + $notforloan_map->{$nfl_value->{authorised_value}} = $nfl_value->{lib}; + } + + foreach my $item (@$results) { + $item->{biblio} = GetBiblio($item->{biblionumber}); + ($item->{biblioitem}) = GetBiblioItemByBiblioNumber($item->{biblionumber}); + $item->{status} = $notforloan_map->{$item->{notforloan}}; + } + } + + $template->param( + filter => $filter, + search_params => $search_params, + results => $results, + total_rows => $total_rows, + search_done => 1, + ); + + if ($format eq 'html') { + # Build pagination bar + my $url = $cgi->url(-absolute => 1); + my @params; + foreach my $p (keys %params) { + my @v = $cgi->param($p); + push @params, map { "$p=" . $_ } @v; + } + $url .= '?' . join ('&', @params); + my $nb_pages = 1 + int($total_rows / $search_params->{rows}); + my $current_page = $search_params->{page}; + my $pagination_bar = pagination_bar($url, $nb_pages, $current_page, 'page'); + + $template->param(pagination_bar => $pagination_bar); + } +} + +if ($format eq 'html') { + # Retrieve data required for the form. + + my $branches = GetBranches(); + my @branches; + foreach my $branchcode (keys %$branches) { + push @branches, { + value => $branchcode, + label => $branches->{$branchcode}->{branchname}, + }; + } + my $locations = GetAuthorisedValues('LOC'); + my @locations; + foreach my $location (@$locations) { + push @locations, { + value => $location->{authorised_value}, + label => $location->{lib}, + }; + } + my @itemtypes = C4::ItemType->all(); + foreach my $itemtype (@itemtypes) { + $itemtype->{value} = $itemtype->{itemtype}; + $itemtype->{label} = $itemtype->{description}; + } + my $ccode_avcode = GetAuthValCode('items.ccode') || 'CCODE'; + my $ccodes = GetAuthorisedValues($ccode_avcode); + my @ccodes; + foreach my $ccode (@$ccodes) { + push @ccodes, { + value => $ccode->{authorised_value}, + label => $ccode->{lib}, + }; + } + + my @notforloans; + foreach my $value (@$notforloan_values) { + push @notforloans, { + value => $value->{authorised_value}, + label => $value->{lib}, + }; + } + + my @items_search_fields = GetItemSearchFields(); + + my $authorised_values = {}; + foreach my $field (@items_search_fields) { + if (my $category = ($field->{authorised_values_category})) { + $authorised_values->{$category} = GetAuthorisedValues($category); + } + } + + $template->param( + branches => \@branches, + locations => \@locations, + itemtypes => \@itemtypes, + ccodes => \@ccodes, + notforloans => \@notforloans, + items_search_fields => \@items_search_fields, + authorised_values_json => to_json($authorised_values), + ); +} + +if ($format eq 'csv') { + print $cgi->header({ + type => 'text/csv', + attachment => 'items.csv', + }); + print $template->output; +} else { + output_with_http_headers $cgi, $cookie, $template->output, $content_type; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 57cc3b8..fb055e8 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3378,6 +3378,23 @@ CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table 'items_search_fields' +-- + +DROP TABLE IF EXISTS items_search_fields; +CREATE TABLE items_search_fields ( + name VARCHAR(255) NOT NULL, + label VARCHAR(255) NOT NULL, + tagfield CHAR(3) NOT NULL, + tagsubfield CHAR(1) NULL DEFAULT NULL, + authorised_values_category VARCHAR(16) NULL DEFAULT NULL, + PRIMARY KEY(name), + CONSTRAINT items_search_fields_authorised_values_category + FOREIGN KEY (authorised_values_category) REFERENCES authorised_values (category) + ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 6db5569..0d1cded 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8120,6 +8120,26 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q{ + CREATE TABLE IF NOT EXISTS items_search_fields ( + name VARCHAR(255) NOT NULL, + label VARCHAR(255) NOT NULL, + tagfield CHAR(3) NOT NULL, + tagsubfield CHAR(1) NULL DEFAULT NULL, + authorised_values_category VARCHAR(16) NULL DEFAULT NULL, + PRIMARY KEY(name), + CONSTRAINT items_search_fields_authorised_values_category + FOREIGN KEY (authorised_values_category) REFERENCES authorised_values (category) + ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + }); + print "Upgrade to $DBversion done (Bug 11425: Add items_search_fields table)\n"; + SetVersion($DBversion); +} + + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/css/itemsearchform.css b/koha-tmpl/intranet-tmpl/prog/en/css/itemsearchform.css new file mode 100644 index 0000000..34c76de --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/css/itemsearchform.css @@ -0,0 +1,44 @@ +.form-field { + margin: 5px 0; +} + +.form-field > * { + vertical-align: middle; +} + +.form-field-label { + display: inline-block; + text-align: right; + width: 10em; +} + +.form-field-conjunction[disabled] { + visibility: hidden; +} + +.form-field-radio > * { + vertical-align: middle; +} + +.form-actions { + margin-top: 20px; +} + +th.active { + padding-right: 21px; + background-repeat: no-repeat; + background-position: 100% 50%; +} + +th.sort-asc { + background-image: url('../../img/asc.gif'); +} + +th.sort-desc { + background-image: url('../../img/desc.gif'); +} + +th select { + width: 100%; + font-weight: normal; +} diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/admin-items-search-field-form.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/admin-items-search-field-form.inc new file mode 100644 index 0000000..995e1f6 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/admin-items-search-field-form.inc @@ -0,0 +1,58 @@ + diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc index ebd345c..2ca1d44 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc @@ -45,6 +45,7 @@
  • Classification sources
  • Record matching rules
  • OAI sets configuration
    • +
  • Items search fields
    • Acquisition parameters
    diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.csv.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.csv.inc new file mode 100644 index 0000000..fd6e835 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.csv.inc @@ -0,0 +1,4 @@ +[%- USE Branches -%] +[%- biblio = item.biblio -%] +[%- biblioitem = item.biblioitem -%] +"[% biblio.title |html %] by [% biblio.author |html %]", "[% biblioitem.publicationyear |html %]", "[% biblioitem.publishercode |html %]", "[% biblioitem.collectiontitle |html %]", "[% item.barcode |html %]", "[% item.itemcallnumber |html %]", "[% Branches.GetName(item.homebranch) |html %]", "[% Branches.GetName(item.holdingbranch) |html %]", "[% item.location |html %]", "[% item.stocknumber |html %]", "[% item.status |html %]", "[% (item.issues || 0) |html %]" diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.inc new file mode 100644 index 0000000..bb5bd41 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.inc @@ -0,0 +1,23 @@ +[%- USE Branches -%] +[% biblio = item.biblio %] +[% biblioitem = item.biblioitem %] + + + [% biblio.title %] + by [% biblio.author %] + + [% biblioitem.publicationyear %] + [% biblioitem.publishercode %] + [% biblioitem.collectiontitle %] + + [% item.barcode %] + + [% item.itemcallnumber %] + [% Branches.GetName(item.homebranch) %] + [% Branches.GetName(item.holdingbranch) %] + [% item.location %] + [% item.stocknumber %] + [% item.status %] + [% item.issues || 0 %] + Modify + diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.json.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.json.inc new file mode 100644 index 0000000..9115ead --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_item.json.inc @@ -0,0 +1,18 @@ +[%- USE Branches -%] +[%- biblio = item.biblio -%] +[%- biblioitem = item.biblioitem -%] +[ + "[% biblio.title |html %] by [% biblio.author |html %]", + "[% biblioitem.publicationyear |html %]", + "[% biblioitem.publishercode |html %]", + "[% biblioitem.collectiontitle |html %]", + "[% item.barcode |html %]", + "[% item.itemcallnumber |html %]", + "[% Branches.GetName(item.homebranch) |html %]", + "[% Branches.GetName(item.holdingbranch) |html %]", + "[% item.location |html %]", + "[% item.stocknumber |html %]", + "[% item.status |html %]", + "[% (item.issues || 0) |html %]", + "Modify" +] diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_items.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_items.inc new file mode 100644 index 0000000..33b4c36 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/catalogue/itemsearch_items.inc @@ -0,0 +1,49 @@ +[% names = CGI.param() %] +[% params = [] %] +[% FOREACH name IN names %] + [% IF name != 'sortby' AND name != 'sortorder' %] + [% params.push(name _ "=" _ CGI.param(name)) %] + [% END %] +[% END %] +[% base_url = "/cgi-bin/koha/catalogue/itemsearch.pl?" _ params.join('&') %] + +[% BLOCK itemsearch_header %] + [% sortorder = 'asc' %] + [% classes = [] %] + [% IF CGI.param('sortby') == name %] + [% classes.push('active') %] + [% classes.push('sort-' _ CGI.param('sortorder')) %] + [% IF CGI.param('sortorder') == 'asc' %] + [% sortorder = 'desc' %] + [% END %] + [% END %] + [% url = base_url _ '&sortby=' _ name _ '&sortorder=' _ sortorder %] + + [% text %] + +[% END %] + + + + + [% INCLUDE itemsearch_header name='title' label='title' text='Bibliographic reference' %] + [% INCLUDE itemsearch_header name='publicationyear' label='publication date' text='Publication date' %] + [% INCLUDE itemsearch_header name='publishercode' label='publisher' text='Publisher' %] + [% INCLUDE itemsearch_header name='collectiontitle' label='collection' text='Collection' %] + [% INCLUDE itemsearch_header name='barcode' label='barcode' text='Barcode' %] + [% INCLUDE itemsearch_header name='itemcallnumber' label='callnumber' text='Callnumber' %] + [% INCLUDE itemsearch_header name='homebranch' label='home branch' text='Home branch' %] + [% INCLUDE itemsearch_header name='holdingbranch' label='holding branch' text='Holding branch' %] + [% INCLUDE itemsearch_header name='location' label='location' text='Location' %] + [% INCLUDE itemsearch_header name='stocknumber' label='stock number' text='Stock number' %] + [% INCLUDE itemsearch_header name='notforloan' label='status' text='Status' %] + [% INCLUDE itemsearch_header name='issues' label='issues' text='Issues' %] + + + + + [% FOREACH item IN items %] + [% INCLUDE 'catalogue/itemsearch_item.inc' item = item %] + [% END %] + +
    diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt index 217b069..6772b01 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt @@ -77,6 +77,8 @@
    Manage rules for automatically matching MARC records during record imports.
    OAI sets configuration
    Manage OAI Sets
    +
    Items search fields
    +
    Manage custom fields for items search
    [% IF ( SearchEngine != 'Zebra' ) %]
    Search engine configuration
    Manage indexes, facets, and their mappings to MARC fields and subfields.
    diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/items_search_field.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/items_search_field.tt new file mode 100644 index 0000000..6e0f373 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/items_search_field.tt @@ -0,0 +1,41 @@ +[% INCLUDE 'doc-head-open.inc' %] + Koha › Administration › Items search fields + [% INCLUDE 'doc-head-close.inc' %] + + + [% INCLUDE 'header.inc' %] + [% INCLUDE 'cat-search.inc' %] + + +
    +
    +
    +
    +

    Items search field: [% field.label %]

    + +
    +
    + Edit field + [% INCLUDE 'admin-items-search-field-form.inc' field=field %] +
    + +
    +
    + +
    +
    +
    + Return to items search fields overview page +
    +
    +
    + [% INCLUDE 'admin-menu.inc' %] +
    +
    + + [% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/items_search_fields.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/items_search_fields.tt new file mode 100644 index 0000000..200e614 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/items_search_fields.tt @@ -0,0 +1,95 @@ +[% INCLUDE 'doc-head-open.inc' %] + Koha › Administration › Items search fields + [% INCLUDE 'doc-head-close.inc' %] + + + [% INCLUDE 'header.inc' %] + [% INCLUDE 'cat-search.inc' %] + + +
    +
    +
    +
    + [% IF field_added %] +
    + Field successfully added: [% field_added.label %] +
    + [% ELSIF field_not_added %] +
    +

    Failed to add field. Please check if the field name doesn't already exist.

    +

    Check logs for more details.

    +
    + [% ELSIF field_deleted %] +
    + Field successfully deleted. +
    + [% ELSIF field_not_deleted %] +
    +

    Failed to delete field.

    +

    Check logs for more details.

    +
    + [% ELSIF field_updated %] +
    + Field successfully updated: [% field_updated.label %] +
    + [% ELSIF field_not_updated %] +
    +

    Failed to update field.

    +

    Check logs for more details.

    +
    + [% END %] +

    Items search fields

    + [% IF fields.size %] + + + + + + + + + + + + + [% FOREACH field IN fields %] + + + + + + + + + [% END %] + +
    NameLabelMARC fieldMARC subfieldAuthorised values categoryOperations
    [% field.name %][% field.label %][% field.tagfield %][% field.tagsubfield %][% field.authorised_values_category %] + Edit + Delete +
    + [% END %] +
    +
    + Add a new field + [% INCLUDE 'admin-items-search-field-form.inc' field=undef %] +
    + +
    +
    + +
    +
    +
    +
    +
    +
    + [% INCLUDE 'admin-menu.inc' %] +
    +
    + + [% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/catalogue/advsearch.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/catalogue/advsearch.tt index c982eb4..b41690d 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/catalogue/advsearch.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/catalogue/advsearch.tt @@ -30,6 +30,7 @@