-
Notifications
You must be signed in to change notification settings - Fork 30
Cookbook: RA
Stefan Dombek edited this page Mar 4, 2024
·
18 revisions
RA reporting includes all reports relating to accessing resources. In this cookbook, some connections and specific SQL statements should be explained.
FOLIO stores all loan data within the Circulations app. This data must be combined with the information from other FOLIO apps. This is usually done via the corresponding UUIDs.
[top]
If you want to see the information about the loans, you only need the loans table. However, the table mainly only contains UUIDs. Therefore, the information from other tables must be used to make the information human readable. The following example shows how this could be done. This query is also available to you as derived table loans_items.
Example:
WITH locations_libraries AS (
SELECT
loccampus__t.id AS campus_id,
loccampus__t.name AS campus_name,
loccampus__t.code AS campus_code,
location__t.id AS location_id,
location__t.name AS location_name,
location__t.code AS location_code,
location__t.discovery_display_name AS discovery_display_name,
loclibrary__t.id AS library_id,
loclibrary__t.name AS library_name,
loclibrary__t.code AS library_code,
locinstitution__t.id AS institution_id,
locinstitution__t.name AS institution_name,
locinstitution__t.code AS institution_code
FROM
folio_inventory.loccampus__t
LEFT JOIN folio_inventory.location__t ON location__t.campus_id = loccampus__t.id
LEFT JOIN folio_inventory.locinstitution__t ON locinstitution__t.id = location__t.institution_id
LEFT JOIN folio_inventory.loclibrary__t ON loclibrary__t.id = location__t.library_id
)
SELECT
loan__t.id AS loan_id,
loan__t.item_id AS item_id,
loan__t.item_status AS item_status,
jsonb_extract_path_text(loan.jsonb, 'status', 'name') AS loan_status,
loan__t.loan_date AS loan_date,
loan__t.due_date AS loan_due_date,
loan__t.return_date AS loan_return_date,
loan__t.system_return_date AS system_return_date,
loan__t.checkin_service_point_id AS checkin_service_point_id,
checkin_service_point.discovery_display_name AS checkin_service_point_name,
loan__t.checkout_service_point_id AS checkout_service_point_id,
checkout_service_point.discovery_display_name AS checkout_service_point_name,
loan__t.item_effective_location_id_at_check_out AS item_effective_location_id_at_check_out,
effective_checkout_location.name AS item_effective_location_name_at_check_out,
jsonb_extract_path_text(item.jsonb, 'inTransitDestinationServicePointId')::uuid AS in_transit_destination_service_point_id,
transit_service_point.discovery_display_name AS in_transit_destination_service_point_name,
item__t.effective_location_id::uuid AS current_item_effective_location_id,
effective_current_location.name AS current_item_effective_location_name,
item__t.temporary_location_id AS current_item_temporary_location_id,
temporary_current_location.name AS current_item_temporary_location_name,
item__t.permanent_location_id AS current_item_permanent_location_id,
permanent_location.name AS current_item_permanent_location_name,
permantent_location_infos.library_id AS current_item_permanent_location_library_id,
permantent_location_infos.library_name AS current_item_permanent_location_library_name,
permantent_location_infos.campus_id AS current_item_permanent_location_campus_id,
permantent_location_infos.campus_name AS current_item_permanent_location_campus_name,
permantent_location_infos.institution_id AS current_item_permanent_location_institution_id,
permantent_location_infos.institution_name AS current_item_permanent_location_institution_name,
loan__t.loan_policy_id AS loan_policy_id,
loan_policy__t.name AS loan_policy_name,
loan__t.lost_item_policy_id AS lost_item_policy_id,
lost_item_fee_policy__t.name AS lost_item_policy_name,
loan__t.overdue_fine_policy_id AS overdue_fine_policy_id,
overdue_fine_policy__t.name AS overdue_fine_policy_name,
loan__t.patron_group_id_at_checkout AS patron_group_id_at_checkout,
user_groups.group AS patron_group_name,
loan__t.user_id AS user_id,
jsonb_extract_path_text(loan.jsonb, 'proxyUserId')::uuid AS proxy_user_id,
item__t.barcode AS barcode,
item__t.chronology AS chronology,
item__t.copy_number AS copy_number,
item__t.enumeration AS enumeration,
item__t.holdings_record_id::uuid AS holdings_record_id,
item__t.hrid AS hrid,
jsonb_extract_path_text(item.jsonb, 'itemLevelCallNumber') AS item_level_call_number,
item__t.material_type_id::uuid AS material_type_id,
item_material_type.name AS material_type_name,
jsonb_extract_path_text(item.jsonb, 'numberOfPieces') AS number_of_pieces,
item__t.permanent_loan_type_id::uuid AS permanent_loan_type_id,
permanent_loan_type.name AS permanent_loan_type_name,
jsonb_extract_path_text(item.jsonb, 'temporaryLoanTypeId')::uuid AS temporary_loan_type_id,
temporary_loan_type.name AS temporary_loan_type_name,
loan__t.renewal_count
FROM
folio_circulation.loan__t
LEFT JOIN folio_circulation.loan ON loan.id = loan__t.id
LEFT JOIN folio_inventory.service_point__t AS checkin_service_point ON checkin_service_point.id = loan__t.checkin_service_point_id
LEFT JOIN folio_inventory.service_point__t AS checkout_service_point ON checkout_service_point.id = loan__t.checkout_service_point_id
LEFT JOIN folio_inventory.item ON item.id = loan__t.item_id
LEFT JOIN folio_inventory.item__t ON item__t.id = loan__t.item_id
LEFT JOIN folio_feesfines.overdue_fine_policy__t ON overdue_fine_policy__t.id = loan__t.overdue_fine_policy_id
LEFT JOIN folio_users.groups__t AS user_groups ON user_groups.id = loan__t.patron_group_id_at_checkout
LEFT JOIN folio_inventory.location__t AS effective_checkout_location ON effective_checkout_location.id = loan__t.item_effective_location_id_at_check_out
LEFT JOIN folio_circulation.loan_policy__t ON loan_policy__t.id = loan__t.loan_policy_id
LEFT JOIN folio_feesfines.lost_item_fee_policy__t ON lost_item_fee_policy__t.id = jsonb_extract_path_text(loan.jsonb, 'lostItemPolicyId')::uuid
LEFT JOIN folio_inventory.location__t AS permanent_location ON permanent_location.id = item__t.permanent_location_id
LEFT JOIN folio_inventory.location__t AS effective_current_location ON effective_current_location.id = item__t.effective_location_id
LEFT JOIN folio_inventory.location__t AS temporary_current_location ON temporary_current_location.id = item__t.temporary_location_id
LEFT JOIN folio_inventory.service_point__t AS transit_service_point ON transit_service_point.id = jsonb_extract_path_text(item.jsonb, 'inTransitDestinationServicePointId')::uuid
LEFT JOIN folio_inventory.material_type__t AS item_material_type ON item_material_type.id = item__t.material_type_id
LEFT JOIN folio_inventory.loan_type__t AS permanent_loan_type ON permanent_loan_type.id = item__t.permanent_loan_type_id
LEFT JOIN folio_inventory.loan_type__t AS temporary_loan_type ON temporary_loan_type.id = jsonb_extract_path_text(item.jsonb, 'temporaryLoanTypeId')::uuid
LEFT JOIN locations_libraries AS permantent_location_infos ON permantent_location_infos.location_id = permanent_location.id
[top]
[top]
[top]
[top]
[top]