Report when item status = Checked out but not loan-storage record

[shelleydoljack]

We need to figure out the scope for when mod-inventory has item status = Checked out but there is no corresponding loan record in mod-circulation. I started a slack conversation in reporting-general and Niassib offered some sql that we could try: https://folio-project.slack.com/archives/C4WCA12BW/p1710518341363149. Another route could be to query the mod-circulation-storage API GET /loan-storage/loans?query=itemId=="${item UUID}" and status.name=="Open" and report out when the response is a 404 (maybe do not include loan status or maybe report if status is closed but item is checked out :shrug: ).

[shelleydoljack]

Context in this thread: https://stanfordlib.slack.com/archives/C03MB1KHR89/p1710439445568599

[shelleydoljack]

Using metadb:

select count(*) from folio_inventory.item as item where jsonb_extract_path_text(item.jsonb, 'status', 'name') = 'Checked out';
count
------
53,545

select count(*) from folio_circulation.loan as loan where jsonb_extract_path_text(loan.jsonb, 'status', 'name') = 'Open';
count
------
64,482

[shelleydoljack]

Trying to run some query counts in folio-stage db. I would think this should return 0 since if the loan is closed, then the item status should no longer be "checked out":

select count(*) from sul_mod_inventory_storage.item as item
left join sul_mod_circulation_storage.loan as loan on jsonb_extract_path_text(loan.jsonb, 'itemId') = item.id::text
where jsonb_extract_path_text(item.jsonb, 'status', 'name') = 'Checked out'
and jsonb_extract_path_text(loan.jsonb, 'status', 'name') = 'Closed';
 count 
-------
  2317

This one is of items with "checked out" status and an open loan.

select count(*) from sul_mod_inventory_storage.item as item
left join sul_mod_circulation_storage.loan as loan on jsonb_extract_path_text(loan.jsonb, 'itemId') = item.id::text
where jsonb_extract_path_text(item.jsonb, 'status', 'name') = 'Checked out'
and jsonb_extract_path_text(loan.jsonb, 'status', 'name') = 'Open';
 count 
-------
 40421

This is of items with "checked out" status and a loan record (could be open or closed):

select count(*) from sul_mod_inventory_storage.item as item
left join sul_mod_circulation_storage.loan as loan on jsonb_extract_path_text(loan.jsonb, 'itemId') = item.id::text
where jsonb_extract_path_text(item.jsonb, 'status', 'name') = 'Checked out';
 count 
-------
 42742

Adding the Open and Closed loans together, there are 4 items with "checked out" status but no loan status? (42,742-40,421-2,317=4)

select count(*) from sul_mod_inventory_storage.item as item
left join sul_mod_circulation_storage.loan as loan on jsonb_extract_path_text(loan.jsonb, 'itemId') = item.id::text
where jsonb_extract_path_text(item.jsonb, 'status', 'name') = 'Checked out'
and loan.id is null;
 count 
-------
     4

Here is a sql query we could run to find where mod-inventory says the item is checked out but there is no loan record:

select item.id AS item_id,
jsonb_extract_path_text(item.jsonb, 'barcode') AS item_barcode,
jsonb_extract_path_text(item.jsonb, 'status', 'name') AS item_status,
jsonb_extract_path_text(loan.jsonb, 'status', 'name') as loan_status
from sul_mod_inventory_storage.item as item
left join sul_mod_circulation_storage.loan as loan on jsonb_extract_path_text(loan.jsonb, 'itemId') = item.id::text
where jsonb_extract_path_text(item.jsonb, 'status', 'name') = 'Checked out'
and loan.id is null;

Here are the results running the count and data queries in folio-prod:

select count(*) from sul_mod_inventory_storage.item as item
left join sul_mod_circulation_storage.loan as loan on jsonb_extract_path_text(loan.jsonb, 'itemId') = item.id::text
where jsonb_extract_path_text(item.jsonb, 'status', 'name') = 'Checked out'
and loan.id is null;
 count 
-------
     6

select item.id AS item_id,
jsonb_extract_path_text(item.jsonb, 'barcode') AS item_barcode,
jsonb_extract_path_text(item.jsonb, 'status', 'name') AS item_status,
jsonb_extract_path_text(loan.jsonb, 'status', 'name') as loan_status
from sul_mod_inventory_storage.item as item
left join sul_mod_circulation_storage.loan as loan on jsonb_extract_path_text(loan.jsonb, 'itemId') = item.id::text
where jsonb_extract_path_text(item.jsonb, 'status', 'name') = 'Checked out'
and loan.id is null;
               item_id                |  item_barcode  | item_status | loan_status 
--------------------------------------+----------------+-------------+-------------
 f8f3627e-5667-5110-ade8-1c91fc6127a4 | 36105011733479 | Checked out | 
 abad3f83-b2c8-537b-989b-c070f554ce07 | 36105114237931 | Checked out | 
 565cb25e-ea7c-59f9-af60-16481f43e1b0 | 36105025576153 | Checked out | 
 f2aff27a-1524-5399-b760-46ce62842af5 | 36105229955138 | Checked out | 
 aa54743f-a556-5a10-9855-30d890531172 | 36105229367359 | Checked out | 
 48399c2b-4f2b-5942-b56e-179a3b4cecce | 36105111909516 | Checked out | 

[shelleydoljack]

Moved to backlog for now.