Bug 37077

Summary: SQL Reports - Picking only one option for each multiple selection results in wrong query
Product: Koha Reporter: Pedro Amorim <pedro.amorim>
Component: ReportsAssignee: Brendan Lawlor <blawlor>
Status: Signed Off --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: andrewfh, blawlor, caroline.cyr-la-rose, kebliss
Version: unspecified   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on: 35746    
Bug Blocks:    
Attachments: Bug 37077: Fix report muti select for zero and single selections
Bug 37077: Fix report muti select for zero and single selections
Bug 37077: Fix report muti select for zero and single selections
Bug 37077: Fix report muti select for zero and single selections

Description Pedro Amorim 2024-06-12 13:43:05 UTC
To reproduce:
1. Go to Reports and create a report from SQL
   select *
   from items
   where
   homebranch in <<Libraries|branches:in>>
   and
   itype in <<Item type|itemtypes:in>>
3. Run the report but pick only 1 library and 1 item type
4. Click the "Show SQL code"
5. Notice itype value is wrong. It's using the same value selected for library.
Comment 1 Brendan Lawlor 2024-06-13 17:44:55 UTC
This one is my bad. Thanks Pedro!

It's happening because the url query string is getting sent duplicate values when only one value is selected.

Then on the perl side the duplicate param for the first select gets inserted into the query where the second param should be.

The example query you wrote generates a url like this:

http://localhost:8081/cgi-bin/koha/reports/guided_reports.pl?id=1&param_name=Libraries%7Cbranches%3Ain&sql_params=CPL&sql_params=CPL&param_name=Item+type%7Citemtypes%3Ain&sql_params=BK&sql_params=BK&op=run

when it should be like this:

http://localhost:8081/cgi-bin/koha/reports/guided_reports.pl?id=1&param_name=Libraries%7Cbranches%3Ain&sql_params=CPL&param_name=Item+type%7Citemtypes%3Ain&sql_params=BK&op=run

This should be fixable in the javascript that overrides the form submission.
Comment 2 Brendan Lawlor 2024-06-14 19:23:15 UTC
Created attachment 167762 [details] [review]
Bug 37077: Fix report muti select for zero and single selections

This patch updates the javascript overriding the form submission when reports have multi select parameters.

When there are more than one multi selects, and the user selcts one value from each, it skips updating the value of the select, so it doens't send duplicate parameters anymore.

If there are no selections made it will pass '%' for all values. This allows the multi select to be optional in the report. If no selections are made it is assumed that you are not using that parameter to limit the report.
Comment 3 Brendan Lawlor 2024-06-14 19:31:30 UTC
Created attachment 167763 [details] [review]
Bug 37077: Fix report muti select for zero and single selections

This patch updates the javascript overriding the form submission when reports have multi select parameters.

When there are more than one multi selects, and the user selcts one value from each, it skips updating the value of the select, so it doens't send duplicate parameters anymore.

If there are no selections made it will pass '%' for all values. This allows the multi select to be optional in the report. If no selections are made it is assumed that you are not using that parameter to limit the report.

Test plan:
1. Go to Reports and create a report from SQL
   select *
   from items
   where
   homebranch in <<Libraries|branches:in>>
   and
   itype in <<Item type|itemtypes:in>>
3. Run the report but pick only 1 library and 1 item type
4. Click the "Show SQL code"
5. Notice that the two parameters were filled correctly
6. Run the report again with zero selections
7. Click the "Show SQL code"
8. Notice that the '%' is used for the parameter
9. Run the report again with combinations of single, multiple and zero selections
10. confirm the SQL code is generated as you would expect.
Comment 4 Brendan Lawlor 2024-06-14 19:39:19 UTC
Created attachment 167765 [details] [review]
Bug 37077: Fix report muti select for zero and single selections

This patch updates the javascript overriding the form submission when reports have multi select parameters.

When there are more than one multi selects, and the user selcts one value from each, it skips updating the value of the select, so it doens't send duplicate parameters anymore.

If there are no selections made it will pass '%' for all values. This allows the multi select to be optional in the report. If no selections are made it is assumed that you are not using that parameter to limit the report.

Test plan:
1. Go to Reports and create a report from SQL
   select *
   from items
   where
   homebranch in <<Libraries|branches:in>>
   and
   itype in <<Item type|itemtypes:in>>
3. Run the report but pick only 1 library and 1 item type
4. Click the "Show SQL code"
5. Notice that the two parameters were filled correctly
6. Run the report again with zero selections
7. Click the "Show SQL code"
8. Notice that the '%' is used for the parameter
9. Run the report again with combinations of single, multiple and zero selections
10. confirm the SQL code is generated as you would expect.
Comment 5 Pedro Amorim 2024-06-17 09:21:42 UTC
Created attachment 167785 [details] [review]
Bug 37077: Fix report muti select for zero and single selections

This patch updates the javascript overriding the form submission when reports have multi select parameters.

When there are more than one multi selects, and the user selcts one value from each, it skips updating the value of the select, so it doens't send duplicate parameters anymore.

If there are no selections made it will pass '%' for all values. This allows the multi select to be optional in the report. If no selections are made it is assumed that you are not using that parameter to limit the report.

Test plan:
1. Go to Reports and create a report from SQL
   select *
   from items
   where
   homebranch in <<Libraries|branches:in>>
   and
   itype in <<Item type|itemtypes:in>>
3. Run the report but pick only 1 library and 1 item type
4. Click the "Show SQL code"
5. Notice that the two parameters were filled correctly
6. Run the report again with zero selections
7. Click the "Show SQL code"
8. Notice that the '%' is used for the parameter
9. Run the report again with combinations of single, multiple and zero selections
10. confirm the SQL code is generated as you would expect.

Signed-off-by: Pedro Amorim <pedro.amorim@ptfs-europe.com>