/*
 * "I was wondering if anyone had a SQL query for locating a fine paid at a specific location on a specific date within a certain time range.
 *  We had a patron pay a fine recently on a day when Sierra was acting a bit wonky, and our accounting folks are unable to match the payment
 *  with the patron.  We’re hoping if we can locate the fine in Sierra, we might be able to figure out who the patron is associated with it.
 *  Thanks in advance for your suggestions."  --Annie Wicks
 *
 * Bob Gaydos <bgaydos@starklibrary.org>
 * April 4, 2024
 */
SET search_path = 'sierra_view';
SELECT
    CASE
        WHEN rmp.record_num IS NULL
            THEN ''
        ELSE
            rmp.record_type_code || rmp.record_num::VARCHAR(10) || 'a'
    END AS patron_record_num,
    CASE
        WHEN rmi.record_num IS NULL
            THEN ''
        ELSE
            rmi.record_type_code || rmi.record_num::VARCHAR(10) || 'a'
    END AS item_record_num,
    fp.id,
    fp.fine_assessed_date_gmt::TIMESTAMP AS fine_assessed_date,
    fp.item_charge_amt,
    fp.processing_fee_amt,
    fp.billing_fee_amt,
    fp.charge_type_code,
    fp.charge_location_code,
    fp.paid_date_gmt::TIMESTAMP AS paid_date,
    fp.tty_num,
    fp.last_paid_amt,
    fp.iii_user_name,
    fp.fine_creation_mode_code,
    fp.print_bill_code,
    fp.checked_out_date_gmt::TIMESTAMP AS checked_out_date,
    fp.due_date_gmt::TIMESTAMP AS due_date,
    fp.returned_date_gmt::TIMESTAMP AS returned_date,
    fp.loan_rule_code_num,
    fp.description,
    fp.paid_now_amt,
    fp.payment_status_code,
    fp.payment_type_code,
    fp.payment_note,
    fp.transaction_id,
    fp.invoice_num,
    fp.old_invoice_num,
    rmp.creation_date_gmt::TIMESTAMP AS patron_creation_date,
    rmp.deletion_date_gmt::TIMESTAMP AS patron_deletion_date,
    rmp.record_last_updated_gmt::TIMESTAMP AS patron_last_updated,
    rmp.previous_last_updated_gmt::TIMESTAMP AS patron_previous_last_updated,
    rmp.campus_code AS patron_campus_code,
    rmp.agency_code_num AS patron_agency_code_num,
    rmp.num_revisions AS num_patron_revisions,
    rmi.creation_date_gmt::TIMESTAMP AS item_creation_date,
    rmi.deletion_date_gmt::TIMESTAMP AS item_deletion_date,
    rmi.record_last_updated_gmt::TIMESTAMP AS item_last_updated,
    rmi.previous_last_updated_gmt::TIMESTAMP AS item_previous_last_updated,
    rmi.campus_code AS item_campus_code,
    rmi.agency_code_num AS item_agency_code_num,
    rmi.num_revisions AS num_item_revisions
FROM fines_paid fp
LEFT OUTER JOIN record_metadata rmp
   ON rmp.id = fp.patron_record_metadata_id
LEFT OUTER JOIN record_metadata rmi
   ON rmi.id = fp.item_record_metadata_id
WHERE fp.paid_date_gmt::TIMESTAMP BETWEEN '2024-01-23 10:30:00' AND '2024-01-23 11:30:00'
--AND/OR fp.iii_user_name IN ('sierra-login-XYZ')
--AND/OR rmp.record_num = 1427316  -- patron p1427316a, if you know which patron you're looking for
--AND/OR rmi.record_num = 1427316  -- likewise with item record num
ORDER BY
    paid_date,
    patron_record_num,
    item_record_num
LIMIT 10000
;