Bug 11425

Summary: Search form for items
Product: Koha Reporter: Julian Maurice <julian.maurice>
Component: SearchingAssignee: Julian Maurice <julian.maurice>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: new feature    
Priority: P5 - low CC: abl, bgkriegel, francois.pichenot, jonathan.druart, katrin.fischer, kyle, m.de.rooy, mathsabypro, nengard, pascale.nalon, robin, tomascohen, veron
Version: Main   
Hardware: All   
OS: All   
See Also: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13561
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13564
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13577
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16115
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16485
Change sponsored?: --- Patch complexity: Large patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 8684, 13476, 13487, 13562, 13949, 14263, 14908, 15384, 15887, 18633    
Attachments: Bug 11425: Add item search form in staff interface
Bug 11425: Add unit tests
Bug 11425: Add item search form in staff interface
Bug 11425: Add unit tests
Bug 11425: Add item search form in staff interface
Bug 11425: Add unit tests
Bug 11425: Add filter on items.notforloan column
[SIGNED-OFF] Bug 11425: Add item search form in staff interface
[SIGNED-OFF] Bug 11425: Add unit tests
[SIGNED-OFF] Bug 11425: Add filter on items.notforloan column
Bug 11425: Get column names using DBIX
Bug 11425: Add item search form in staff interface
Bug 11425: Add unit tests
Bug 11425: Add filter on items.notforloan column
Bug 11425: Get column names using DBIX
Bug 11425: QA fixes
Bug 11425: Display location label instead of code
Bug 11425: Add item search form in staff interface
Bug 11425: Add unit tests
Bug 11425: Add filter on items.notforloan column
Bug 11425: Get column names using DBIX
Bug 11425: QA fixes
Bug 11425: Display location label instead of code
Bug 11425: Add item search form in staff interface
Bug 11425: Add unit tests
Bug 11425: Add filter on items.notforloan column
Bug 11425: Get column names using DBIX
Bug 11425: QA fixes
Bug 11425: Display location label instead of code
[PASSED QA] Bug 11425: Add item search form in staff interface
[PASSED QA] Bug 11425: Add unit tests
[PASSED QA] Bug 11425: Add filter on items.notforloan column
[PASSED QA] Bug 11425: Get column names using DBIX
[PASSED QA] Bug 11425: QA fixes
[PASSED QA] Bug 11425: Display location label instead of code
[PASSED QA] Bug 11425 [QA Followup] - Fix number of unit tests
Bug 11425 [Template follow-up] Search form for items
Bug 11425 [Template follow-up] Search form for items
Bug 11425 [Template follow-up] Search form for items
[PASSED QA] Bug 11425 [Template follow-up] Search form for items

Description Julian Maurice 2013-12-19 13:27:07 UTC
This will provide a new search form for items, using MySQL as backend.
Comment 1 Julian Maurice 2013-12-19 13:29:32 UTC Comment hidden (obsolete)
Comment 2 Julian Maurice 2013-12-19 13:29:39 UTC Comment hidden (obsolete)
Comment 3 Julian Maurice 2013-12-19 13:33:19 UTC
I'm not satisfied of the way we can access the item search page (a tiny link in advanced search page), so suggestions are very welcome! :)
Comment 4 Mathieu Saby 2014-01-01 11:07:52 UTC
Hello
Do you think it will be possible to improve this enh later to use Zebra instead of SQL directly ? I suppose it would improve performance?

Mathieu
Comment 5 Julian Maurice 2014-03-14 10:00:39 UTC Comment hidden (obsolete)
Comment 6 Julian Maurice 2014-03-14 10:00:54 UTC Comment hidden (obsolete)
Comment 7 Pascale Nalon 2014-03-14 13:57:54 UTC
test on sandbox and then :
Software error:

Template process failed: plugin error - JSON.Escape: plugin not found at /home/koha/src/C4/Templates.pm line 132.
For help, please send mail to the webmaster (webmaster@matrice-koha36-sandbox), giving this error message and the time and date of the error.
Comment 8 Owen Leonard 2014-03-14 16:06:27 UTC
(In reply to Pascale Nalon from comment #7)
> test on sandbox and then :
> Software error:
> 
> Template process failed: plugin error - JSON.Escape: plugin not found

I notice also that the bug number in updatedatabase.pl is missing:

print "Upgrade to $DBversion done (Bug XXXXX: Add items_search_fields table)\n";
Comment 9 Julian Maurice 2014-03-25 10:47:12 UTC Comment hidden (obsolete)
Comment 10 Julian Maurice 2014-03-25 10:47:26 UTC Comment hidden (obsolete)
Comment 11 Julian Maurice 2014-03-25 10:47:39 UTC Comment hidden (obsolete)
Comment 12 Julian Maurice 2014-03-25 10:48:54 UTC
(In reply to Pascale Nalon from comment #7)
> test on sandbox and then :
> Software error:
> 
> Template process failed: plugin error - JSON.Escape: plugin not found at
> /home/koha/src/C4/Templates.pm line 132.
> For help, please send mail to the webmaster
> (webmaster@matrice-koha36-sandbox), giving this error message and the time
> and date of the error.

This development has additional dependencies which are not installed on sandboxes. Please tell me which sandbox you are using and I will install the required dependencies on it.
Comment 13 Bernardo Gonzalez Kriegel 2014-04-14 23:33:54 UTC Comment hidden (obsolete)
Comment 14 Bernardo Gonzalez Kriegel 2014-04-14 23:34:05 UTC Comment hidden (obsolete)
Comment 15 Bernardo Gonzalez Kriegel 2014-04-14 23:34:16 UTC Comment hidden (obsolete)
Comment 16 Bernardo Gonzalez Kriegel 2014-04-14 23:37:16 UTC
(In reply to Julian Maurice from comment #3)
> I'm not satisfied of the way we can access the item search page (a tiny link
> in advanced search page), so suggestions are very welcome! :)

Perhaps with two tabs, left for Advanced search, right for item search :)

Only annoying thing (may be inevitable) is loosing browser history,
a back on item search results lands you on advanced search, click on a record or item view/edit, press back and lands on item search, loosing results

Good work!
Comment 17 Katrin Fischer 2014-04-15 08:23:13 UTC
I really like the idea of this new feature, but I am a bit worried about adding new code to SQLHelper now that we have DBIX and in ligth of bug 11385.
Comment 18 Jonathan Druart 2014-04-16 10:26:03 UTC Comment hidden (obsolete)
Comment 19 Kyle M Hall 2014-04-25 14:48:05 UTC
Please change "joker" to "wildcard". It may be good to also document that % matches any number of chracters and that _ matches only a single character.

Another issue is that it's a bit confusing that when I ran a search, the first then I say was my search form again! I would suggest that when there are results, change the form title from "Item search" to "Item search parameters" or something like that, and make it a link that will expand and collapse the form using jquery.
Comment 20 Kyle M Hall 2014-04-25 14:49:31 UTC
On more thing, please change the "modify" links in the results to "edit" so they match up with the rest of Koha.
Comment 21 Julian Maurice 2014-05-06 14:37:08 UTC Comment hidden (obsolete)
Comment 22 Julian Maurice 2014-05-06 14:37:25 UTC Comment hidden (obsolete)
Comment 23 Julian Maurice 2014-05-06 14:37:39 UTC Comment hidden (obsolete)
Comment 24 Julian Maurice 2014-05-06 14:37:47 UTC Comment hidden (obsolete)
Comment 25 Julian Maurice 2014-05-06 14:37:55 UTC Comment hidden (obsolete)
Comment 26 Julian Maurice 2014-05-06 14:41:05 UTC
> Another issue is that it's a bit confusing that when I ran a search, the
> first then I say was my search form again! I would suggest that when there
> are results, change the form title from "Item search" to "Item search
> parameters" or something like that, and make it a link that will expand and
> collapse the form using jquery.
This is already the case, search form is automatically collapsed after submit.
But the bad path of columnFilter plugin was certainly causing a JS error, causing the following javascript code to not be executed.
Comment 27 Julian Maurice 2014-07-23 08:57:54 UTC Comment hidden (obsolete)
Comment 28 Julian Maurice 2014-07-23 08:58:19 UTC
The last patch needs a signoff.
Comment 29 Bernardo Gonzalez Kriegel 2014-07-28 12:26:09 UTC Comment hidden (obsolete)
Comment 30 Bernardo Gonzalez Kriegel 2014-07-28 12:26:15 UTC Comment hidden (obsolete)
Comment 31 Bernardo Gonzalez Kriegel 2014-07-28 12:26:22 UTC Comment hidden (obsolete)
Comment 32 Bernardo Gonzalez Kriegel 2014-07-28 12:26:33 UTC Comment hidden (obsolete)
Comment 33 Bernardo Gonzalez Kriegel 2014-07-28 12:26:41 UTC Comment hidden (obsolete)
Comment 34 Bernardo Gonzalez Kriegel 2014-07-28 12:26:52 UTC Comment hidden (obsolete)
Comment 35 Marcel de Rooy 2014-10-13 12:02:21 UTC
CONFLICT (content): Merge conflict in koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt
Auto-merging koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc
Auto-merging installer/data/mysql/updatedatabase.pl
CONFLICT (content): Merge conflict in installer/data/mysql/updatedatabase.pl
Auto-merging installer/data/mysql/kohastructure.sql
CONFLICT (content): Merge conflict in installer/data/mysql/kohastructure.sql
Auto-merging C4/Items.pm
Auto-merging C4/Installer/PerlDependencies.pm
CONFLICT (content): Merge conflict in C4/Installer/PerlDependencies.pm
Comment 36 Chris Cormack 2014-10-14 13:35:13 UTC Comment hidden (obsolete)
Comment 37 Chris Cormack 2014-10-14 13:35:40 UTC Comment hidden (obsolete)
Comment 38 Chris Cormack 2014-10-14 13:35:53 UTC Comment hidden (obsolete)
Comment 39 Chris Cormack 2014-10-14 13:36:09 UTC Comment hidden (obsolete)
Comment 40 Chris Cormack 2014-10-14 13:36:45 UTC Comment hidden (obsolete)
Comment 41 Chris Cormack 2014-10-14 13:37:00 UTC Comment hidden (obsolete)
Comment 42 Owen Leonard 2014-10-14 14:37:37 UTC
It looks like there is no package for Template::Plugin::JSON::Escape in Ubuntu <= 12.04.
Comment 43 Kyle M Hall 2014-10-24 13:58:10 UTC
Created attachment 32669 [details] [review]
[PASSED QA] 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 <bgkriegel@gmail.com>
Work as described. Good new option.
No koha-qa errors

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 44 Kyle M Hall 2014-10-24 13:58:19 UTC
Created attachment 32670 [details] [review]
[PASSED QA] Bug 11425: Add unit tests

package Koha::Item::Search::Field
function C4::SQLHelper::GetColumns
function C4::Items::SearchItems

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Tests run without error

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 45 Kyle M Hall 2014-10-24 13:58:24 UTC
Created attachment 32671 [details] [review]
[PASSED QA] Bug 11425: Add filter on items.notforloan column

and display the 'Processing ...' box when datatables is loading new
data.

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Nice box

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 46 Kyle M Hall 2014-10-24 13:58:29 UTC
Created attachment 32672 [details] [review]
[PASSED QA] Bug 11425: Get column names using DBIX

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 47 Kyle M Hall 2014-10-24 13:58:32 UTC
Created attachment 32673 [details] [review]
[PASSED QA] Bug 11425: QA fixes

- Reword 'joker' to 'wildcard'
- Explain what wildcards do
- Reword 'Modify' to 'Edit'

Also fix jQuery plugin columnFilter path

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 48 Kyle M Hall 2014-10-24 13:58:35 UTC
Created attachment 32674 [details] [review]
[PASSED QA] Bug 11425: Display location label instead of code

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>

Minor conflicts solved
Last three patches signed
No koha-qa errors.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 49 Kyle M Hall 2014-10-24 13:58:38 UTC
Created attachment 32675 [details] [review]
[PASSED QA] Bug 11425 [QA Followup] - Fix number of unit tests

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 50 Kyle M Hall 2014-10-24 13:59:15 UTC
QA note: it would be nice if the item's holding branch was part of the search form, but I see this is a further enhancement and no reason to fail qa.
Comment 51 Katrin Fischer 2014-10-25 21:15:14 UTC
Adding Robin, because of comment #42:
> It looks like there is no package for Template::Plugin::JSON::Escape in
> Ubuntu <= 12.04.
Comment 52 Robin Sheat 2014-10-27 22:35:21 UTC
(In reply to Katrin Fischer from comment #51)
> Adding Robin, because of comment #42:
> > It looks like there is no package for Template::Plugin::JSON::Escape in
> > Ubuntu <= 12.04.

It's in wheezy and above:

https://packages.debian.org/search?keywords=libtemplate-plugin-json-escape-perl&searchon=names&suite=all&section=all

That'll make it easy to backport...
Comment 53 Robin Sheat 2014-10-28 02:42:28 UTC
(In reply to Robin Sheat from comment #52)
> It's in wheezy and above:
> 
> https://packages.debian.org/search?keywords=libtemplate-plugin-json-escape-
> perl&searchon=names&suite=all&section=all
> 
> That'll make it easy to backport...

Famous last words, but it's in there now.
Comment 54 Owen Leonard 2014-10-29 13:41:06 UTC
I notice this feature will happily allow you to submit an empty search which returns all items. Is that okay?
Comment 55 Owen Leonard 2014-10-29 14:39:27 UTC Comment hidden (obsolete)
Comment 56 Marc Véron 2014-10-29 15:36:46 UTC
I got a merge error for installer/data/mysql/updatedatabase.pl

After resolving it I got errors about missing curly brackets et EOF while updating the database. 

Seems to be something with the quotes after line 8892
$DBversion = "3.17.00.XXX";
Comment 57 Owen Leonard 2014-10-29 15:38:48 UTC
(In reply to Marc Véron from comment #56)
> After resolving it I got errors about missing curly brackets et EOF while
> updating the database. 

I too had to fix merge problems with updatedatbase.pl but I didn't get any errors. Can you please double-check your changes?
Comment 58 Marc Véron 2014-10-29 16:11:39 UTC
Ah, found  :-)
Comment 59 Marc Véron 2014-10-29 16:35:52 UTC Comment hidden (obsolete)
Comment 60 Katrin Fischer 2014-10-29 22:38:45 UTC
The follow up is looking good. Thinking about terminology, I think we should rename stock number to be inventory number to match how it's called in the frameworks. But this shouldn't be a blocker here.
Comment 61 Katrin Fischer 2014-10-29 22:39:24 UTC Comment hidden (obsolete)
Comment 62 Katrin Fischer 2014-10-29 22:40:52 UTC
Created attachment 32948 [details] [review]
[PASSED QA] Bug 11425 [Template follow-up] Search form for items

This template followup makes several changes to markup and text:

- Replace "Bibliographic reference" with "Title"
- Replace "Callnumber" with "Call number"
- Replace "Home branch" with "Home library"
- Replace "Holding branch" with "Current location"
- Replace "Location" with "Shelving location"
- Replace "Issues" with "Checkouts"
- Replace "Indifferent" with "Ignore"
- Replace "&" with "&amp;" where necessary
- Correct page title and breadcrumbs
- Remove label "for" attribute from labels which do not directly target
  a form field
- Correct label "for" attribute on labels which should target a form
  field
- Correct date format hint

This patch also makes one change to the way the page display works:

The search results section is now in a separate div which uses a
different grid rule, allowing it to take up the full width of the
screen. To keep the page headers from looking weird I've changed the
markup so that each section has its own header.

To test, perform a variety of searches and confirm that the output in
both Screen and CSV views looks correct.

Confirm that search results which are output to the screen are formatted
nicely and that the "edit search" links work correctly to show the
search form. Submitting a new search after editing a search should hide
the form and redisplay the search results table.

Followed test plan, works as expected.
Signed-off-by: Marc Véron <veron@veron.ch>

Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de>
Works as described, improves display.
Comment 63 Katrin Fischer 2014-10-29 22:42:14 UTC
Hm and current location maybe current library?
Comment 64 Tomás Cohen Arazi 2014-11-05 11:36:27 UTC
Patches pushed to master.

Thanks Julian!
Comment 65 Jonathan Druart 2014-12-04 12:23:28 UTC
I have just updated 2 sandboxes and got:

DBD::mysql::db do failed: Can't create table 'test16.items_search_fields' (errno: 150) at installer/data/mysql/updatedatabase.pl line 8991.
Upgrade to 3.17.00.046 done (Bug 11425: Add items_search_fields table)
Comment 66 Katrin Fischer 2014-12-06 09:02:03 UTC
I ran an update from 3.16.2 on my development installation at work on Friday and had no errors. It looked quite good - could it be the database already had the change somehow?
Comment 67 Jonathan Druart 2014-12-08 08:56:34 UTC
No, the table does not exist yet.
Actually I got the error on both sandboxes used for bug 11944.
It comes from the FK, I am able to create the table if I remove the constraint.
Comment 68 Jonathan Druart 2014-12-08 08:57:45 UTC
Ok, got it!
It comes from the different charset collate introduced by 11944.
Sorry for the noise.
Comment 69 Jonathan Druart 2015-01-14 09:00:53 UTC
(In reply to Jonathan Druart from comment #68)
> Ok, got it!
> It comes from the different charset collate introduced by 11944.
> Sorry for the noise.

I should have cared more about this problem, see bug 13577 for a fix.
Comment 70 Jonathan Druart 2015-04-07 12:24:42 UTC
This introduced a regression on checking for duplicate, see bug 8684 for a fix.
Comment 71 Jonathan Druart 2017-05-18 20:07:39 UTC
The use of the CGI TT plugin floods logs with:

itemsearch.pl: CGI::param called in list context from package Template::Document line 118, this can lead to vulnerabilities. See the warning in "Fetching the value or values of a single named parameter" at /usr/share/perl5/CGI.pm line 436., referer: http://xxx/cgi-bin/koha/about.pl

Julian, can you check how we can fix that?
Comment 72 Jonathan Druart 2017-05-18 20:09:54 UTC
See bug 18633