Author History

-- 2014-12-22
-- taking out the total circs
-- recent changes, adding a cut off to only look at records added within the last eight years
-- adding a variable to look for print materials via the 06a
-- no filtering by reference materials though


DROP FUNCTION rogan.author_history(VARCHAR);

CREATE OR REPLACE FUNCTION rogan.author_history(author_name varchar(255))
  RETURNS TABLE (
   rh_copies                    NUMERIC,
   rh_circs             NUMERIC,
   yk_copies                    NUMERIC,
   yk_circs             NUMERIC,
   fm_copies                    NUMERIC,
   fm_circs             NUMERIC,
   cl_copies                    NUMERIC,
   cl_circs             NUMERIC,
   lw_copies                    NUMERIC,
   lw_circs             NUMERIC,
   author               TEXT,
   title                TEXT,
   publisher                    TEXT,
   pubdate              TEXT,
   isbn                 _text,
   bib_id               int8,
   created_on                   DATE
  ) AS
$$
BEGIN

RETURN QUERY

SELECT
SUM(rh_copies.cont) AS "rh_copies", SUM(rh_circs.cont) AS "rh_circs",
SUM(yk_copies.cont) AS "yk_copies", SUM(yk_circs.cont) AS "yk_circs",
SUM(fm_copies.cont) AS "fm_copies", SUM(fm_circs.cont) AS "fm_circs",
SUM(cl_copies.cont) AS "cl_copies", SUM(cl_circs.cont) AS "cl_circs",
SUM(lw_copies.cont) AS "lw_copies", SUM(lw_circs.cont) AS "lw_circs",
msr.author, msr.title, msr.publisher, msr.pubdate, msr.isbn, bre.id AS "bib_id", date(bre.create_date) AS "created_on"
FROM reporter.materialized_simple_record msr
JOIN biblio.record_entry bre ON bre.id = msr.id
LEFT JOIN (
        SELECT COUNT(ac.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-YK' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) yk_copies ON yk_copies.bib = bre.id
LEFT JOIN (
        SELECT COUNT(ac.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-LW' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) lw_copies ON lw_copies.bib = bre.id
LEFT JOIN (
        SELECT COUNT(ac.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-FM' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) fm_copies ON fm_copies.bib = bre.id
LEFT JOIN (
        SELECT COUNT(ac.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-CL' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) cl_copies ON cl_copies.bib = bre.id
LEFT JOIN (
        SELECT COUNT(ac.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-RH' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) rh_copies ON rh_copies.bib = bre.id
LEFT JOIN (
        SELECT COUNT(circ.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT JOIN ACTION.circulation circ ON circ.target_copy = ac.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-RH' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) rh_circs ON rh_circs.bib = bre.id
LEFT JOIN (
        SELECT COUNT(circ.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT JOIN ACTION.circulation circ ON circ.target_copy = ac.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-YK' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) yk_circs ON yk_circs.bib = bre.id
LEFT JOIN (
        SELECT COUNT(circ.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT JOIN ACTION.circulation circ ON circ.target_copy = ac.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-FM' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) fm_circs ON fm_circs.bib = bre.id
LEFT JOIN (
        SELECT COUNT(circ.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT JOIN ACTION.circulation circ ON circ.target_copy = ac.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-LW' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) lw_circs ON lw_circs.bib = bre.id
LEFT JOIN (
        SELECT COUNT(circ.id) AS "cont", bre.id AS "bib"
        FROM reporter.materialized_simple_record msr
        JOIN biblio.record_entry bre ON bre.id = msr.id
        LEFT JOIN asset.call_number call ON call.record = bre.id
        LEFT JOIN asset.COPY ac ON ac.call_number = call.id
        JOIN actor.org_unit org ON ac.circ_lib = org.id
        LEFT JOIN ACTION.circulation circ ON circ.target_copy = ac.id
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name) AND org.shortname = 'YCL-CL' AND ac.circ_modifier != 'RENTAL'
        and ac.create_date > now() - interval '8 years'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        GROUP BY 2
) cl_circs ON cl_circs.bib = bre.id
WHERE msr.author ILIKE TRIM(TRAILING '.' FROM author_name)
GROUP BY 11, 12, 13, 14, 15, 16, 17
ORDER BY 17 DESC;

END;
$$
LANGUAGE plpgsql;

select * from rogan.author_history('rowling, j. k.');