Make direct queries of ahopl ("Holds On Pull List") IDL class return distinct results

Branch:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/blake/LP1964986_Pull_list_can_lose_pagination_when_a_patron_has_more_than_one_penalty

I'm not able to duplicate what Blake describes. Our production system is running 3.7.2, and Blake's query returns 19 rows, which is the majority of our public libraries.

When viewing the pull list for those libraries, I am not seeing any loss of pagination functionality, and the number of rows displayed is consistent with the chosen row count.

Interestingly, when I scrape the ahopl query from the fm_IDL.xml and limit ahr.pickup_lib to one of those libraries, the database query retrieves FEWER rows than the pull list for that branch shows in the client.

This is a small tweak to the query to help get the "most likely" branches to have the issue:

select aou.shortname,count(*)
from
actor.org_unit aou
join action.hold_request ahr2 on (ahr2.pickup_lib=aou.id)
join
(
select ahr.id,count(*)
from
action.hold_request ahr
join actor.usr au on(au.id=ahr.usr)
join actor.usr_standing_penalty ausp on(ausp.usr=au.id and (ausp.stop_date IS NULL OR ausp.stop_date > NOW()))
LEFT JOIN config.standing_penalty csp
ON (
 csp.id = ausp.standing_penalty AND
 csp.block_list LIKE '%CAPTURE%' AND (
  (csp.org_depth IS NULL AND ahr.pickup_lib = ausp.org_unit) OR
  (csp.org_depth IS NOT NULL AND ahr.pickup_lib IN (
  SELECT id FROM actor.org_unit_descendants(ausp.org_unit, csp.org_depth))
  )
 )
)
where
ahr.capture_time IS NULL AND
ahr.cancel_time IS NULL AND
csp.id IS NULL AND
(ahr.expire_time is NULL OR ahr.expire_time > NOW()) AND
csp.id is null
group by 1
having count(*) > 1
) bug_ids
on (bug_ids.id=ahr2.id)
group by 1
order by 2 desc

Start testing from the first returned result.

I can replicate this for a large library on our system running 3.6.5. I also saw one case for a smaller library where there were 14 rows when the number of rows was set 25 and 15 when it was set 5 rows.

This bug might be related: https://bugs.launchpad.net/evergreen/+bug/1858698

I have tested this code and consent to signing off on it with my name, Jennifer Weston, and my email address, <email address hidden>

During Bug Squashing week on Terran's test server.
Tested with various combinations of patron home library, item owning library, and pickup library.
Tested with multiple patron non-alerting notes and alerting notes with no blocks at various depths (Everywhere, System, and Branch). All behaved as expected.

This bug report as stated is now moot on all supported versions of Evergreen, as the Angular version of the pull list does not use the ahopl IDL class, nor is there any other active code that uses that class directly. I.e., this is a bug with the AngularJS pull list.

(Also, the proposed fix is insufficient: if a hold has multiple hold notes, as is done in Concerto for staff-placed holds, the open-ils.fielder call that the AngularJS pull lists invokes does a left join on action.hold_request_note. That throws off pagination and retrieval by the grid regardless of the "DISTINCT" in ahopl.)

It is possible that there are reports still using the ahopl/"Holds On Pull List" source. However, since Clark effectively dedupes all report query output, an extra "DISTINCT" in ahopl would have no effect.

Nonetheless, I've pushed this patch, as theoretically there could be local code still out there making direct PCRUD queries against ahopl. I've taken the liberty of editing the commit message, and will also open a bug to propose a deprecation of ahopl.