-
Notifications
You must be signed in to change notification settings - Fork 30
Cookbook: RA
RA reporting includes all reports relating to accessing resources. In this cookbook, some connections and specific SQL statements should be explained.
Please note: Fees are in the cookbook for User Management (UM).
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]
The counters for renewals are in the record of a loan. It depends a lot on how you want to use the counters. This cookbook therefore only shows how you can query the counter for renewals for each individual loan.
Example:
SELECT
id AS loan_id,
jsonb_extract_path_text(loan.jsonb, 'renewalCount') :: INTEGER AS renewal_count
FROM
folio_circulation.loan
[top]
Users can create requests. The following example shows how you can query the data. You will find a query for this with such more data to a request as derived table requests_items.
Example:
SELECT
request__t.id AS request_id,
request__t.item_id,
request__t.request_date::DATE as request_date,
request__t.request_type,
request__t.status AS request_status,
request__t.pickup_service_point_id,
request__t.requester_id,
jsonb_extract_path_text(request.jsonb, 'fulfilmentPreference') AS fulfillment_preference
FROM
folio_circulation.request__t
LEFT JOIN folio_circulation.request ON request.id = request__t.id
[top]
Service points can be created in the settings from FOLIO. You can save additional opening hours for each service point.
[top]
Information about service points can be found in the Inventory module. In most cases the information is used in connection with items or loans.
Example:
SELECT
jsonb_extract_path_text(service_point.jsonb, 'id') AS id,
jsonb_extract_path_text(service_point.jsonb, 'discoveryDisplayName') AS display_name,
jsonb_extract_path_text(service_point.jsonb, 'name') AS name,
jsonb_extract_path_text(service_point.jsonb, 'code') AS code,
COALESCE(jsonb_extract_path_text(service_point.jsonb, 'pickupLocation'):: Boolean, FALSE) AS pickup_location,
jsonb_extract_path_text(service_point.jsonb, 'holdShelfExpiryPeriod', 'duration') AS expiry_period_duration,
jsonb_extract_path_text(service_point.jsonb, 'holdShelfExpiryPeriod', 'intervalId') AS expiry_period_interval,
jsonb_extract_path_text(service_point.jsonb, 'metadata', 'createdDate') AS created_date,
jsonb_extract_path_text(service_point.jsonb, 'metadata', 'updatedDate') AS updated_date
FROM
folio_inventory.service_point
ORDER BY
jsonb_extract_path_text(service_point.jsonb, 'name'),
jsonb_extract_path_text(service_point.jsonb, 'discoveryDisplayName')
[top]
You can add opening hours to each service point that are stored in the FOLIO module calendar. The connection to the service point can be established via the UUID of the service point.
The following example shows how you can query the opening times for all service points. It should be noted that a weekday index should be created first to enable better sorting or later calculation.
Example:
WITH weekday_index AS (
SELECT
normal_hours.id,
CASE
WHEN normal_hours.start_day = 'MONDAY' THEN 1
WHEN normal_hours.start_day = 'TUESDAY' THEN 2
WHEN normal_hours.start_day = 'WEDNESDAY' THEN 3
WHEN normal_hours.start_day = 'THURSDAY' THEN 4
WHEN normal_hours.start_day = 'FRIDAY' THEN 5
WHEN normal_hours.start_day = 'SATURDAY' THEN 6
WHEN normal_hours.start_day = 'SUNDAY' THEN 7
END AS weekday_index_start_day,
CASE
WHEN normal_hours.end_day = 'MONDAY' THEN 1
WHEN normal_hours.end_day = 'TUESDAY' THEN 2
WHEN normal_hours.end_day = 'WEDNESDAY' THEN 3
WHEN normal_hours.end_day = 'THURSDAY' THEN 4
WHEN normal_hours.end_day = 'FRIDAY' THEN 5
WHEN normal_hours.end_day = 'SATURDAY' THEN 6
WHEN normal_hours.end_day = 'SUNDAY' THEN 7
END AS weekday_index_end_day
FROM
folio_calendar.normal_hours
)
SELECT
service_point_calendars.service_point_id,
service_point_calendars.calendar_id,
calendars.name AS calendar_name,
normal_hours.start_day,
weekday_index.weekday_index_start_day,
normal_hours.start_time,
normal_hours.end_day,
weekday_index.weekday_index_end_day,
normal_hours.end_time
FROM
folio_calendar.service_point_calendars
LEFT JOIN folio_calendar.calendars ON calendars.id = service_point_calendars.calendar_id
LEFT JOIN folio_calendar.normal_hours ON normal_hours.calendar_id = service_point_calendars.calendar_id
LEFT JOIN weekday_index ON weekday_index.id = normal_hours.id
ORDER BY
service_point_calendars.service_point_id,
calendars.name,
weekday_index.weekday_index_start_day
[top]
The locations are part of the FOLIO modules inventory. Locations contain several pieces of information that are in different tables. The reason for this is that depending on how you look at it and you need it, the data is accessed differently. Locations are often used in conjunction with loans and items, e.g. to show the permanent, current, temporary or effective location.
If there is some kind of hierarchy for locations, it could be summarized like this. A location is part of a campus and can have one institution or more. A library is part of a institution.
| campus | location | institution | library |
|---|---|---|---|
| A | A | A | A |
| A | A | A | B |
| B | A | C | C |
| ... | ... | ... | ... |
The following example shows how to query the information. However, it is not the only variant. You will find this query as derived table locations_libraries.
Example:
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
There is also a connection between the locations and the service points. In the location records there is an array for service points. You can extract the data and append it to your query. You will find a query as derived table locations_service_points.
Example:
SELECT
id AS location_id,
jsonb_array_elements_text(jsonb_extract_path(location.jsonb, 'servicePointIds')) :: UUID AS service_point_id
FROM
folio_inventory.location
[top]
[top]