Bug 5697

Summary: Automatic linking in guided reports
Product: Koha Reporter: Owen Leonard <oleonard>
Component: ReportsAssignee: Owen Leonard <oleonard>
Status: RESOLVED FIXED QA Contact: Bugs List <koha-bugs>
Severity: enhancement    
Priority: P5 - low CC: 1joynelson, bdaeuber, david, dcook, fridolin.somers, jonathan.druart, kyle, lisettepalouse+koha, martin.renvoize, nick, nugged
Version: masterKeywords: Manual
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29679
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Ben Daeuber Documentation submission: https://gitlab.com/koha-community/koha-manual/-/merge_requests/770
Text to go in the release notes:
This patch adds a feature to automatically link certain database columns in report results. If your report returns itemnumber, biblionumber, cardnumber, or borrowernumber, that cell in the report will contain a menu: - borrowernumber: View, edit, check out - cardnumber: Check out - itemnumber: View, edit - biblionumber: View, edit A link at the top of the report results will toggle the new menus on and off in case you don't want to see the menus. Your choice will persist until you log out. The feature works with column name placeholders, so if you want the table column to be a human readable label you can still get automatic linking, for example: [[items.itemnumber|Item number]]
Version(s) released in:
22.05.00
Bug Depends on: 26458, 26473    
Bug Blocks:    
Attachments: Screenshot of proposed implementation
Bug 5697: Automatic linking in guided reports
Bug 5697: Automatic linking in guided reports
Bug 5697: Automatic linking in guided reports
Bug 5697: (follow-up) Save the user's choice to show or hide auto-links
Bug 5697: Automatic linking in guided reports
Bug 5697: (follow-up) Save the user's choice to show or hide auto-links
Bug 5697: Consolidate nodes construction and fix translation issue
testing performance on a report with many rows
Bug 5697: Automatic linking in guided reports
Bug 5697: Automatic linking in guided reports
Bug 5697: Automatic linking in guided reports

Description Owen Leonard 2011-02-04 20:28:55 UTC
I would be really useful if the screen output of guided reports was linked intelligently so that if certain fields were being output, the content in the results linked to the relevant record.

For instance, if you were selecting biblio.title, the screen output would link to that record. If you were selecting items.barcode, the screen output would link to moredetail.pl. Right now you can either use complicated CONCATs to get useful screen output or standard queries for useful export. Implementing this feature would give us useful output for both.
Comment 1 Owen Leonard 2020-09-14 19:22:14 UTC
Created attachment 110075 [details]
Screenshot of proposed implementation

I propose a solution that would automatically generate a dropdown menu for each result in itemnumber, biblionumber, cardnumber, and borrowernumber columns. Each menu would contain "View" and "Edit" options. The card number column would link to the checkout page.
Comment 2 David Nind 2020-09-15 11:42:51 UTC
Seems sensible and useful to me, although I'm not really a user of the guided reports.
Comment 3 Owen Leonard 2020-09-24 15:09:44 UTC Comment hidden (obsolete)
Comment 4 ByWater Sandboxes 2020-09-24 18:49:06 UTC Comment hidden (obsolete)
Comment 5 Lisette Scheer 2020-09-24 18:50:52 UTC
Worked swimmingly. We were glad that it didn't download as links but still gave the option for links in the browser. This will be very helpful. 
Lisette (and Koha-US Bug-a-palooza stream)
Comment 6 Katrin Fischer 2020-09-26 19:54:59 UTC
I think the feature is super nice, but there is a big issue for me as it will currently only work on the English column names. We usually never leave them like that and always translate using "itemnumber AS Exemplarnummer" or similar.

Could we imagine adding the columnname to the table heading as a class and use this for triggering the links? 

I think we are also missing checks on permissions - so Edit item will show, if you don't have the permission to edit.

For batch edit features we found a solution here:
Bug 23390 - Add placeholder syntax for column names
Comment 7 Katrin Fischer 2020-09-26 20:02:40 UTC
What i used for testing:

SELECT 
i.itemnumber,
i.itemnumber as Exemplarnummber,
[[i.itemnumber| itemnumber for batch]],
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', i.biblionumber, '&itemnumber=', i.itemnumber, '#edititem', '\">', i.itemnumber, '</a>' ) AS "itemnumber as edit link"
FROM items i
Comment 8 Owen Leonard 2020-09-26 20:08:05 UTC
(In reply to Katrin Fischer from comment #6)

> For batch edit features we found a solution here:
> Bug 23390 - Add placeholder syntax for column names

Thanks Katrin I wasn't aware that a solution was in place for this issue and batch edit features. I'll see if I can work with that.
Comment 9 David Cook 2020-09-27 23:27:53 UTC
I wonder if we could use SQL::Translator to translate the SQL into a JSON data structure that could be rendered by TT. I don't know enough about the module but *maybe* it has support for getting column names before they're aliased? Probably not but you never know?
Comment 10 Owen Leonard 2021-02-19 20:02:56 UTC Comment hidden (obsolete)
Comment 11 Owen Leonard 2021-02-19 20:07:57 UTC
(In reply to Katrin Fischer from comment #6)
> I think the feature is super nice, but there is a big issue for me as it
> will currently only work on the English column names.

I hadn't tested it with the new placeholder system, but after rebasing the patch and looking again my tests were successful, with one glitch: If there is a space inside the column name placeholder it won't work. So the SQL you tried:

SELECT 
i.itemnumber,
i.itemnumber as Exemplarnummber,
[[i.itemnumber| itemnumber for batch]],
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', i.biblionumber, '&itemnumber=', i.itemnumber, '#edititem', '\">', i.itemnumber, '</a>' ) AS "itemnumber as edit link"
FROM items i

"[[i.itemnumber| itemnumber for batch]]" has a space before "itemnumber for batch." With that space removed it works as expected. I think this must be considered a bug in the placeholder parsing, since this query will fail to trigger the expected batch item modification menu:

SELECT 
i.itemnumber AS ' itemnumber for batch'
FROM items i
Comment 12 Owen Leonard 2021-03-01 16:13:25 UTC
(In reply to Owen Leonard from comment #11)
> I think this must be
> considered a bug in the placeholder parsing

I have filed Bug 27824
Comment 13 Katrin Fischer 2021-03-04 15:12:54 UTC
I feel like it would be nice if the "toggle data menus" setting would be kept between multiple runs of the report or when paging, changing the number of results displayed etc. 

I think it would also make it a little more accessible to change the text and not just the icon. Maybe "Show data menus" / "Hide data menus"?

Really small complaints, I think this is really nice :) Owen, what do you think?
Comment 14 Katrin Fischer 2021-03-04 15:13:57 UTC
> I think it would also make it a little more accessible to change the text
> and not just the icon. Maybe "Show data menus" / "Hide data menus"?

Just realized - the hide/show is how the SQL button on the same page works as well!
Comment 15 Owen Leonard 2021-03-04 17:47:21 UTC Comment hidden (obsolete)
Comment 16 Katrin Fischer 2021-03-04 21:11:26 UTC Comment hidden (obsolete)
Comment 17 Katrin Fischer 2021-03-04 21:11:30 UTC
Created attachment 117766 [details] [review]
Bug 5697: (follow-up) Save the user's choice to show or hide auto-links

This patch adds local storage of the user's selection when they click
the "Show/hide data menus" control. The selection persists until the
user logs out.

To test, apply the patch and run an SQL report which will return
borrowernumber, cardnumber, itemnumber, or biblionumber.

- When the results display, confirm that these numbers are shown with
  the auto-link menu.
- Click the "Hide data menus" link. The text should change to "Show data
  menus" and the menus should disappear.
- Navigate away from the page and re-run the report.
- The menus should be automatically hidden in the results.
- Log out of the staff client, return to saved reports, and run the
  report again.
- The auto-link menus should appear.

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 18 Katrin Fischer 2021-03-04 21:11:52 UTC
Thx for the follow-up - love this even more now!
Comment 19 Katrin Fischer 2021-03-04 21:12:13 UTC
Please remember to add text for the release notes!
Comment 20 Jonathan Druart 2021-03-09 15:09:04 UTC
Why opening in a new window? (target="_blank")
Let's teach people middle-click exists! :D
Comment 21 Jonathan Druart 2021-03-09 16:02:47 UTC
Created attachment 117988 [details] [review]
Bug 5697: Consolidate nodes construction and fix translation issue

The way the buttons were created was not nice as it was a full HTML node
built with a JS string, on a single line.
I first tried to split the line and then realized but could put in a
function and reuse. Then noticed that the button's text was not
translatable.

I ended up with this patch. I don't know if it's more readable, but at
least it seems a bit more robust and maintainable in the long term.
Comment 22 Jonathan Druart 2021-03-09 16:03:25 UTC
Would you agree with this patch, Owen?
Comment 23 Jonathan Druart 2021-03-10 09:16:34 UTC
Hum, should we expect an impact on perfs with this last patch?
Comment 24 Jonathan Druart 2021-03-10 09:17:34 UTC
Martin, Nick, can you have a look at the last patch please?
Comment 25 Katrin Fischer 2021-03-10 11:53:56 UTC
(In reply to Jonathan Druart from comment #20)
> Why opening in a new window? (target="_blank")
> Let's teach people middle-click exists! :D

We always add target="_blank" because we don't want them to rerun the more complicated queries a lot of times because they don't know about the middle click or forget about it. I would prefer it opening in a new tab by default in this case! if you middle click it anyway, that would not hurt.
Comment 26 Nick Clemens 2021-03-10 12:49:28 UTC
Created attachment 118022 [details]
testing performance on a report with many rows

It looks like the initial patch almost doubles the time to load the page fully, and the final patch add about the same

I don't know how typical this many results is, and I really love the concept here, so I am not sure if this is a concern
Comment 27 Martin Renvoize 2021-03-10 14:05:46 UTC
I wonder how the performance would be affected if we built the links in the template instead of with a render function and then just hid/displayed with JS?
Comment 28 Owen Leonard 2021-11-09 13:17:53 UTC
Created attachment 127484 [details] [review]
Bug 5697: Automatic linking in guided reports

This patch modifies the output of reports so that if certain columns are
returned the data in the colums automatically offer menus of actions:

- borrowernumber: View, edit, check out
- cardnumber: Check out
- itemnumber: View, edit
- biblionumber: View, edit

Like the menu of batch operations, this functionality is available only
if the column is returned with its original name (e.g. biblionumber or
[[biblionumber|Biblio number]] but not "biblionumber AS `Biblio
number`).

To test, apply the patch and run a report which will return examples of
each kind of data. I used:

SELECT biblio.biblionumber , biblio.title, items.itemnumber,
items.itemcallnumber, items.barcode, borrowers.firstname,
borrowers.surname, borrowers.borrowernumber, borrowers.cardnumber
FROM issues
LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber
LEFT JOIN items ON issues.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
ORDER BY RAND() LIMIT 50

When the report runs you should see that the data in each affected
column is displayed as a link, each of which should trigger the correct
menu. Test each case to confirm that the correct page is opened.

Test the "toggle data menus" control. Clicking it should hide and show
the menus in the report results. The selection persists until the
user logs out.
Comment 29 David Nind 2021-12-05 16:56:09 UTC
Created attachment 128252 [details] [review]
Bug 5697: Automatic linking in guided reports

This patch modifies the output of reports so that if certain columns are
returned the data in the colums automatically offer menus of actions:

- borrowernumber: View, edit, check out
- cardnumber: Check out
- itemnumber: View, edit
- biblionumber: View, edit

Like the menu of batch operations, this functionality is available only
if the column is returned with its original name (e.g. biblionumber or
[[biblionumber|Biblio number]] but not "biblionumber AS `Biblio
number`).

To test, apply the patch and run a report which will return examples of
each kind of data. I used:

SELECT biblio.biblionumber , biblio.title, items.itemnumber,
items.itemcallnumber, items.barcode, borrowers.firstname,
borrowers.surname, borrowers.borrowernumber, borrowers.cardnumber
FROM issues
LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber
LEFT JOIN items ON issues.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
ORDER BY RAND() LIMIT 50

When the report runs you should see that the data in each affected
column is displayed as a link, each of which should trigger the correct
menu. Test each case to confirm that the correct page is opened.

Test the "toggle data menus" control. Clicking it should hide and show
the menus in the report results. The selection persists until the
user logs out.

Signed-off-by: David Nind <david@davidnind.com>
Comment 30 Jonathan Druart 2021-12-06 13:36:49 UTC
Created attachment 128277 [details] [review]
Bug 5697: Automatic linking in guided reports

This patch modifies the output of reports so that if certain columns are
returned the data in the colums automatically offer menus of actions:

- borrowernumber: View, edit, check out
- cardnumber: Check out
- itemnumber: View, edit
- biblionumber: View, edit

Like the menu of batch operations, this functionality is available only
if the column is returned with its original name (e.g. biblionumber or
[[biblionumber|Biblio number]] but not "biblionumber AS `Biblio
number`).

To test, apply the patch and run a report which will return examples of
each kind of data. I used:

SELECT biblio.biblionumber , biblio.title, items.itemnumber,
items.itemcallnumber, items.barcode, borrowers.firstname,
borrowers.surname, borrowers.borrowernumber, borrowers.cardnumber
FROM issues
LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber
LEFT JOIN items ON issues.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
ORDER BY RAND() LIMIT 50

When the report runs you should see that the data in each affected
column is displayed as a link, each of which should trigger the correct
menu. Test each case to confirm that the correct page is opened.

Test the "toggle data menus" control. Clicking it should hide and show
the menus in the report results. The selection persists until the
user logs out.

Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 31 Fridolin Somers 2021-12-10 20:43:13 UTC
Looks really great :D

I'm testing for push to master
Comment 32 Fridolin Somers 2021-12-10 20:50:25 UTC
Pushed to master for 22.05, thanks to everybody involved 🦄
Comment 33 Nick Clemens 2022-01-14 16:21:32 UTC
*** Bug 22535 has been marked as a duplicate of this bug. ***
Comment 34 Benjamin Daeuber 2023-12-13 21:21:07 UTC
Reopening to document. See https://gitlab.com/koha-community/koha-manual/-/merge_requests/770 for the merge request.