TABLE OF CONTENTS
| Table of Contents |
|---|
Circ Snapshot for LDP
The first step in the process is to create the circsnapshot4.sql table in the LDP, which is where data updates will be appended and stored. Run the create_circsnapshot4.sql query to create the circsnapshot4 table in the local_core schema on the LDP. This query (run just once) finds all records in the loans_items table where the user_id is not null, and gets the demographics associated with that user via the user_users table and the users_departmnts_unpacked table. This makes the a starting table that is updated by the update_circsnapshot4 query daily automatically.
...
-- 1. In order to get to the department code in the departments__t table, we need to extract the department ID from the jsonb array in the users__ table
WITH depts AS
(SELECT
users__.id AS user_id,
departments.jsonb #>> '{}' AS department_id
FROM
folio_users.users__
CROSS JOIN jsonb_array_elements((users__.jsonb #> '{departments}')::jsonb) as departments
WHERE users__.__current = 'true'
)
-- 2. Use the results of the depts subquery above to link to the departments__t table and users__t table
SELECT
NOW() AS extract_date,
loan loan__t.id as loan_id,
loan__t.item_id,
item__t.barcode,
loan__t.loan_date::timestamptz,
loan__t.return_date::timestamptz as loan_return_date,
groups__t.group AS patron_group_name,
jsonb_extract_path_text (folio_users.users__.jsonb,'customFields','department') AS custom_fields__department,
jsonb_extract_path_text (folio_users.users__.jsonb,'customFields','college') AS custom_fields__college,
departments__t.code AS department_code
FROM
folio_users.users__
LEFT JOIN folio_users.users__t
ON users__.id = users__t.id
LEFT JOIN depts
ON users__.id = depts.user_id
LEFT JOIN folio_users.departments__t
ON depts.department_id::UUID = departments__t.id
LEFT JOIN folio_circulation.loan__t
ON loan__t.user_id = users__t.id
LEFT JOIN folio_users.groups__t
ON users__t.patron_group = groups__t.id
LEFT JOIN folio_inventory.item__t
ON loan__t.item_id = item__t.id
WHERE
loan loan__t.id NOT IN
(SELECT cs4.loan_id::UUID
FROM local_shared.sm_circ_snapshot4 as cs4
)
AND loan__t.user_id is not null
AND users__.__current = true
;
...