Bug 5697 - Automatic linking in guided reports
Summary: Automatic linking in guided reports
Status: Passed QA
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Owen Leonard
QA Contact: Bugs List
URL:
Keywords: Manual
Depends on: 26458 26473
Blocks:
  Show dependency treegraph
 
Reported: 2011-02-04 20:28 UTC by Owen Leonard
Modified: 2021-03-05 14:04 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
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, e.g. [[items.itemnumber|Item number]]
Version(s) released in:


Attachments
Screenshot of proposed implementation (26.61 KB, image/png)
2020-09-14 19:22 UTC, Owen Leonard
Details
Bug 5697: Automatic linking in guided reports (10.75 KB, patch)
2020-09-24 15:09 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 5697: Automatic linking in guided reports (10.81 KB, patch)
2020-09-24 18:49 UTC, ByWater Sandboxes
Details | Diff | Splinter Review
Bug 5697: Automatic linking in guided reports (9.98 KB, patch)
2021-02-19 20:02 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 5697: (follow-up) Save the user's choice to show or hide auto-links (3.79 KB, patch)
2021-03-04 17:47 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 5697: Automatic linking in guided reports (10.10 KB, patch)
2021-03-04 21:11 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 5697: (follow-up) Save the user's choice to show or hide auto-links (3.90 KB, patch)
2021-03-04 21:11 UTC, Katrin Fischer
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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
Created attachment 117765 [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, 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.

Signed-off-by: Lisette Scheer <lisetteslatah@gmail.com>

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

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
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!