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.
CREATE TABLE local_core.circ_snapshot4
AS
SELECT
now() AS extract_date,
li.loan_id,
li.item_id,
li.barcode,
li.loan_date,
li.loan_return_date,
li.patron_group_name,
uu.custom_fields__department,
uu.custom_fields__college,
udu.department_code
FROM folio_reporting.loans_items AS li
LEFT JOIN user_users AS uu
ON li.user_id = uu.id
LEFT JOIN folio_reporting.users_departments_unpacked AS udu
ON li.user_id = udu.user_id
WHERE li.user_id IS NOT NULL
;
The daily_update_circ_demographics task runs daily at 7am. This task runs a query called update_circsnapshot4.sql. The update_circ_snapshot4.sql query (in the LOCAL_AUTOMATED folder in DBeaver) runs the following code on the LDP, which uses the INSERT function to get the new checkouts and add them to the local_core.circsnapshot4 table daily automatically. The local_core.circsnapshot4 table is used in circulation queries to generate reports that need the PII data that is removed everyday from circulation data in the FOLIO system.
INSERT INTO local_core.circ_snapshot4
SELECT
now() AS extract_date,
li.loan_id,
li.item_id,
li.barcode,
li.loan_date,
li.loan_return_date,
li.patron_group_name,
uu.custom_fields__department,
uu.custom_fields__college,
udu.department_code
FROM folio_reporting.loans_items AS li
LEFT JOIN user_users AS uu
ON li.user_id = uu.id
LEFT JOIN folio_reporting.users_departments_unpacked AS udu
ON li.user_id = udu.user_id
WHERE
li.loan_id NOT IN
(SELECT cs4.loan_id
FROM local_core.circ_snapshot4 AS cs4
)
AND li.user_id IS NOT NULL
;
Here is a screenshot of the local_core.circsnapshot4 table in the LDP.