Patron Stat Cats and Circulation

[Tim Spindler]

I have written a sql script to grab patron stat cat information for
circulation. This script creates a table in the "extend reporter"
schema and a view that can be used as a datasource for reporting on
circulation statistics by patron stat cat. I had posted this to the
developers list but it may be more appropriate here for those who
would want to use it. If you search the developers list archive, you
will find the attached script in a file. Below is the script.

--------------


/*
* Copyright (C) 2011 C/W MARS.
* Created by Tim Spindler
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
*/

CREATE TABLE extend_reporter.aged_patron_stat_cats (
id bigserial NOT NULL,
aged_circ_id bigint NOT NULL,
stat_cat text NOT NULL,
stat_cat_entry text NOT NULL
);

ALTER TABLE ONLY extend_reporter.aged_patron_stat_cats
ADD CONSTRAINT aged_stat_cats_pkey PRIMARY KEY (id);

CREATE OR REPLACE VIEW extend_reporter.circulation_patron_stat_cats AS
SELECT c.id, s.name, m.stat_cat_entry
FROM action.circulation c JOIN (actor.stat_cat_entry_usr_map m JOIN
actor.stat_cat s ON m.stat_cat = s.id) ON c.usr = m.target_usr
UNION ALL
SELECT aged_circ_id, stat_cat, stat_cat_entry FROM
extend_reporter.aged_patron_stat_cats;


CREATE OR REPLACE FUNCTION extend_reporter.age_circ_stat_cat_on_delete
() RETURNS TRIGGER AS $$
DECLARE
found char := 'N';
BEGIN
-- If there are any renewals for this circulation, don't archive
or delete
-- it yet. We'll do so later, when we archive and delete the
renewals.
SELECT 'Y' INTO found
FROM action.circulation
WHERE parent_circ = OLD.id
LIMIT 1;

IF found = 'Y' THEN
RETURN NULL; -- don't delete
END IF;

-- Archive a copy of the old row to extend_reporter.aged_stat_cats

INSERT INTO extend_reporter.aged_patron_stat_cats
(aged_circ_id, stat_cat, stat_cat_entry)
SELECT id, name, stat_cat_entry
FROM extend_reporter.circulation_patron_stat_cats WHERE id = OLD.id;

RETURN OLD;


END;

$$ LANGUAGE 'plpgsql';

CREATE TRIGGER action_circulation_aging_stat_cat_tgr
BEFORE DELETE ON action.circulation
FOR EACH ROW
EXECUTE PROCEDURE extend_reporter.age_circ_stat_cat_on_delete ();

CREATE OR REPLACE VIEW extend_reporter.all_circulation_sg AS
SELECT aged_circulation.id, aged_circulation.usr_post_code,
aged_circulation.usr_home_ou, aged_circulation.usr_profile,
aged_circulation.usr_birth_year, aged_circulation.copy_call_number,
aged_circulation.copy_location, aged_circulation.copy_owning_lib,
aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record,
aged_circulation.xact_start, aged_circulation.xact_finish,
aged_circulation.target_copy, aged_circulation.circ_lib,
aged_circulation.circ_staff, aged_circulation.checkin_staff,
aged_circulation.checkin_lib, aged_circulation.renewal_remaining,
aged_circulation.due_date, aged_circulation.stop_fines_time,
aged_circulation.checkin_time, aged_circulation.create_time,
aged_circulation.duration, aged_circulation.fine_interval,
aged_circulation.recurring_fine, aged_circulation.max_fine,
aged_circulation.phone_renewal, aged_circulation.desk_renewal,
aged_circulation.opac_renewal, aged_circulation.duration_rule,
aged_circulation.recurring_fine_rule, aged_circulation.max_fine_rule,
aged_circulation.stop_fines, aged_circulation.workstation,
aged_circulation.checkin_workstation,
aged_circulation.checkin_scan_time, aged_circulation.parent_circ,
scats.stat_cat_entry
FROM (action.aged_circulation JOIN
extend_reporter.aged_patron_stat_cats scats ON
((aged_circulation.id=scats.aged_circ_id)))
UNION ALL
SELECT DISTINCT circ.id, COALESCE(a.post_code, b.post_code) AS
usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile,
(date_part('year'::text, p.dob))::integer AS usr_birth_year,
cp.call_number AS copy_call_number, cp.location AS copy_location,
cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish,
circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
circ.checkin_lib, circ.renewal_remaining, circ.due_date,
circ.stop_fines_time, circ.checkin_time, circ.create_time,
circ.duration, circ.fine_interval, circ.recurring_fine, circ.max_fine,
circ.phone_renewal, circ.desk_renewal, circ.opac_renewal,
circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule,
circ.stop_fines, circ.workstation, circ.checkin_workstation,
circ.checkin_scan_time, circ.parent_circ, sm.stat_cat_entry
FROM ((((((action.circulation circ
JOIN asset.copy cp ON ((circ.target_copy = cp.id)))
JOIN asset.call_number cn ON ((cp.call_number = cn.id)))
JOIN actor.usr p ON ((circ.usr = p.id)))
LEFT JOIN actor.stat_cat_entry_usr_map sm ON
((sm.target_usr=circ.usr)))
LEFT JOIN actor.usr_address a ON ((p.mailing_address = a.id)))
LEFT JOIN actor.usr_address b ON ((p.billing_address = a.id)));