Newly Entered Reports Part2 ( )

[csaundrs]

There are a few new reports that I added. I have checked them, but please double check the results and the layout of the output. If they look good, they can be moved to production:

universal_borrowing => ubstat4b_details_by_pickup_location.report local_circulation_statistics => other_circ_activity_statistics => call_slip_unfulfilled_requests.report acquisitions => funds_and_ledgers => funds_by_ledger_name.report local_cat_maint=> bib_records=>titles=>bibs_without_indexed_titles.report local_cat_maint.=>bib_records=>general=>identifying_bibs_with_empty_index_entries.report local_cat_maint => bib_records=>control_numbers=>bib_record_035_contains_pbk_maybe_ISBN local_cat_maint=> bib_records=>control_numbers=>bib_record_035_A_contains_double_slash

[patrickzurek]

I'm getting errors from celery when I run:

local_cat_maint => bib_records=>control_numbers=>bib_record_035_contains_pbk_maybe_ISBN local_cat_maint=> bib_records=>control_numbers=>bib_record_035_A_contains_double_slash

I will see if I can fix the SQL.

When I run:

universal_borrowing => ubstat4b_details_by_pickup_location.report local_circulation_statistics => other_circ_activity_statistics => call_slip_unfulfilled_requests.report

I get an error on the front-end from datatables. I'm looking into these errors as well.

The others seem to run ok, although I think the "Funds by Ledger Name" report can be improved with a pre-populated query similar to Fund Transactions - All. I will try adding this using Fund Transactions - All as a guide.

[patrickzurek]

local_cat_maint => bib_records=>control_numbers=>bib_record_035_contains_pbk_maybe_ISBN was troublesome to fix but it should be working now, however I'm not getting any rows returned for DPU so the validity of the report still needs to be checked against Access.

The first problem was celery was complaining about invalid characters at the positions where the LIKE clause was used. I had to escape all the % symbols by preceding them with another % symbol. So this:

WHERE (((BIB_INDEX.INDEX_CODE)='0350') AND ((BIB_INDEX.DISPLAY_HEADING) Like '%pbk%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%PBK%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%paper%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%alk%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%hb%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%hard%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%hc%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%cloth%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%cased%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%sc%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%cover%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%trade%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%lib%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%set%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%electr%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%$%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%:%'));

became:

WHERE (((BIB_INDEX.INDEX_CODE)='0350') AND ((BIB_INDEX.DISPLAY_HEADING) Like '%%pbk%%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%%PBK%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%paper%%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%%alk%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%hb%%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%%hard%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%hc%%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%%cloth%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%cased%%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%%sc%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%cover%%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%%trade%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%lib%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%set%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%electr%%'
Or (BIB_INDEX.DISPLAY_HEADING) Like '%%\$%%' Or (BIB_INDEX.DISPLAY_HEADING) Like '%%:%%'));

The second, and bigger, problem was the query contained a character we chose to reserve for our own use: "$". If you recall, the $ is used to indicate the database to be used in the query (i.e. DPU, etc.). The way I fixed this was to indicate with a backslash ('\') before the '$' that the '$' character was not to be substituted by the database instance. Then, I modified the backend code to look for instances of '\$' and instead of replacing it with the database instance, it simply removed the backslash from the sql string.

There's three things to take away from this:

1) I made some changes to the backend code that potentially could have undesirable consequences and while I don't think I did, it's possible I introduced a bug that could affect all queries since I messed with the query substitution handling. So be on the look out for queries that aren't running anymore (most likely, they will fail completely, rather than run but say, introduce a small change in the output). A good way to check if a report has failed to run is to look at the celery log: 'cat /var/log/celery/worker1.log'

You should see something that looks like this at the bottom:

[2016-06-10 10:38:58,819: ERROR/MainProcess] Task main.tasks.run_query[6e9015f1-a208-4022-a43a-f86ea3a36d15] raised unexpected: DatabaseError('<cx_Oracle._Error object at 0x7f27f0a8e670>',) Traceback (most recent call last):

DatabaseError: **2**) Be mindful that you need to escape % symbols in queries. You do this by prefixing them with another % symbol. **3**) Also, be mindful that when a $ symbol is used in a query (most likely as part of a LIKE clause), any instances will also need to be escaped by prefixing them with a backslash ('\'). See near the bottom of the 'after' instance of the SQL I copied above for what the $ escaping should look like.

[patrickzurek]

The other three reports were easier to fix.

For the second query giving a celery error, I fixed it by escaping the % symbols present like I mentioned in my last reply.

For the two queries giving dataTables errors, both of them had an extra empty column box duo. I removed the empty column box duos using the admin interface.

For universal_borrowing => ubstat4b_details_by_pickup_location.report I changed the system name of the third column to CountOfUB_ROUTING_ID to match that in the SQL.

For local_circulation_statistics => other_circ_activity_statistics => call_slip_unfulfilled_requests.report I changed the first column system name to DateProcessed to match that in the SQL.

Finally, I tidied up the column names displayed to the user. For example, STATUS_DESC got changed to Status Description, etc.

[patrickzurek]

Lastly, acquisitions => funds_and_ledgers => funds_by_ledger_name.report had an empty column box pair that was causing dataTables errors too, so I removed it in the admin interface.

I also changed the two parameters to be pre-populated and it looked like this when I was done:

I also tidied up the displayed column names from "FUND_TYPE" to "Fund Type", etc. for most of the columns. There's two you may want to check though:

I changed FUNDLINE to Fund Line and EXPEND_PENDING to Expenditures Pending. Is that correct?

[gibsonjc]

These five can be moved to Production: local_circulation_statistics => other_circ_activity_statistics => call_slip_unfulfilled_requests.report acquisitions => funds_and_ledgers => funds_by_ledger_name.report local_cat_maint.=>bib_records=>general=>identifying_bibs_with_empty_index_entries.report local_cat_maint => bib_records=>control_numbers=>bib_record_035_contains_pbk_maybe_ISBN local_cat_maint=> bib_records=>control_numbers=>bib_record_035_A_contains_double_slash

These two have problems and should not go to Prod yet. I've made new Issues for them: universal_borrowing => ubstat4b_details_by_pickup_location.report #84 local_cat_maint=> bib_records=>titles=>bibs_without_indexed_titles.report #85

[gibsonjc]

Also, to be technically correct, call_slip_unfulfilled_requests.report should be renamed call_slip_unfilled_requests.report on devel before moving to prod.

unfulfilled should be unfilled

[patrickzurek]

I changed the name of call_slip_unfilled_requests.report using linux 'mv.' Then I pushed the five reports above to prod.