[OPEN-ILS-DEV] Logging report results

[Josh Stompro]

Hello Everyone, I was looking for a solution to the following problem and came up with a solution.  But I'm curious if there is a better way.  We have reports where staff want to know if each item has shown up on the report before, and how many times.

For example, we have an unfillable holds report that goes out 4 times a month, half the time it goes directly to branches and half the time it goes to our Collection Development Librarian with slightly different parameters.  Our Collection Dev Lib wanted to know when something was previously on the list since it takes time for her to check to see if something is still available for purchase, and she understandably didn't want to keep doing that for the same titles.  Sometimes our branch staff don't take care of the holds right away, so they stay on the report, and we want them to stay on the report until staff take action.  I could see taking some automatic action in the future though, based on the number of times the item has shown up, like automatically canceling the hold and adding a note/message to the patrons record.

Probably for many cases I could use a timestamp window for some of this, but for this report there isn't anything to go off of that is stable.  A hold can show up on the list at any time, any time there are no more holdable copies.

So I created a logging table and a function that creates a new entry every time it is called, and returns the last time the item was seen, and the number of times it has appeared on the report in the past.  You have to be careful not to have duplicate data because I couldn't figure out how to update if exists, else insert, which seems to be a common issue.  Hopefully upsert in PG 9.5 will make that easy.

SQL is at https://gist.github.com/stompro/c35e6c346be63fbc1f6434538d7a7d2f

Unfillable holds SQL that uses it at https://gist.github.com/stompro/8363f4de22587bd126ca

Is this a reasonable approach?  Anyone have any suggestions?  I'm not that familiar with writing postgresql stored procedures which is why I stuck with 'LANGUAGE SQL'.
Thanks
Josh


Lake Agassiz Regional Library - Moorhead MN larl.org
Josh Stompro     | Office 218.233.3757 EXT-139
LARL IT Director | Cell 218.790.2110

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-dev/attachments/20160707/1aee9dd3/attachment.html>