Annual Report - Sections G & H - old

/*
Section G: LIBRARY USERS, VISITS, COMPUTER AND INTERNET

                        Registered Users
            G1 Adult__________________________
            G2 Juvenile__________________________
            G3 TOTAL (G1 + G2)__________________________

counts patron accounts under the age of 18 by the end of the fiscal year where the age is blank due to the dob
being null because we used to allow that.  we look to see if the account is labeled as juvenile in the description,
this will change after the Fall 2015 profile consolidaiton and we should be able to do it more cleanly off
pgt.name

I check for dob first becuase it could be an adult in a child account but I assume if the dob is null that it is on the
exception list above and a child, I look to see if it's marked deleted and if it was marked past the cut off
and if it was created in the last three years

*/

select a.library as "Library", a.adults as "Adults - G1", j.juveniles as "Juveniles - G2", a.adults+j.juveniles as "Total - G3"
from (
	SELECT org2.shortname as "library", COUNT(usr.id) AS "adults"
	FROM actor.usr usr
	JOIN actor.org_unit org ON org.id = usr.home_ou
	JOIN actor.org_unit org2 ON org2.id = org.parent_ou
	join permission.grp_tree pgt on pgt.id = usr.profile
	WHERE
	(
        	date(usr.create_date) >= '2012-07-01'
        	OR
        	(
                	usr.deleted = FALSE
            	    AND
        	        date(usr.expire_date) >= '2012-07-01'
    	    )
	)
	AND
	        (
                	date(usr.dob) <= date('1997-07-01')
            	    OR
        	        (dob IS NULL AND pgt.description not ilike '%juv%')
    	    )
	GROUP BY 1
	ORDER BY 1
) a
join (
	SELECT org2.shortname as "library", COUNT(usr.id) AS "juveniles"
	FROM actor.usr usr
	JOIN actor.org_unit org ON org.id = usr.home_ou
	JOIN actor.org_unit org2 ON org2.id = org.parent_ou
	join permission.grp_tree pgt on pgt.id = usr.profile
	WHERE
	(
    	    date(usr.create_date) >= '2012-07-01'
        	OR
        	(
            	    usr.deleted = FALSE
               		AND
                	date(usr.expire_date) >= '2012-07-01'
        	)
	)
	AND
        	(
	                date(usr.dob) >= date('1997-07-01')
    	            OR
        	        -- next year after next round we should be able to do this more cleanly off pgt.name
            	    (dob IS NULL AND pgt.description ilike '%juv%')
        	)
	GROUP BY 1
	ORDER BY 1
) j on j.library = a.library;



/*


Section H: REFERENCE, CIRCULATION

            Circulation Transactions and Interlibrary Loan H2 - H11 (In all sections, if no circulation to report, enter 0)
            Juvenile Circulation (may include Teen/YA circulation per library policy)
            H2 Print__________________________
            H3 Non-Print (not books or electronic/downloadable items)__________________________
            H4 TOTAL JUVENILE CIRCULATION (H2 + H3)__________________________

            Adult Circulation (may include Teen/YA circulation per library policy)
            H5 Print__________________________
            H6 Non-Print (not books or electronic/downloadable items)__________________________
            H7 TOTAL (H5 + H6)__________________________

*/

-------------------------------------- circ by material format from leader and audience
-------------------------------------- looks for a = language material = book
-------------------------------------- i = audio books, j = music, g = projected medium aka 'not a' = non-print
-------------------------------------- non-cat circs are all added to print materials
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

drop table if exists rogan.action_circulation;
drop table if exists rogan.circ_ldrs;

create table rogan.action_circulation as
select * from action.circulation circ
WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30';

create table rogan.circ_ldrs as
SELECT circ.id as "circ_id", SUBSTRING(meta.VALUE FROM 7 FOR 1) as "circ_ldr"
FROM rogan.action_circulation circ
JOIN asset.copy copy ON copy.id = circ.target_copy
JOIN asset.call_number call ON call.id = copy.call_number
LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
AND meta.tag = 'LDR'
;

SELECT juv_a.shortname as "Library",
juv_a.circs as "Juvenile Print Circs - H2", juv_not_a.circs as "Juvenile Non-Print Circs - H3",
juv_a.circs+juv_not_a.circs+coalesce(non.circs,0) as "Total Juvenile Circs - H4",
adult_a.circs as "Adult Print Circs - H5", adult_not_a.circs as "Adult Non-Print Circs - H6",
adult_a.circs+adult_not_a.circs+coalesce(non.circs,0) as "Total Adult Circs - H7"
FROM (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.copy copy ON copy.id = circ.target_copy
        JOIN asset.call_number call ON call.id = copy.call_number
        join asset.copy_location acl on acl.id = copy.location
        LEFT OUTER JOIN rogan.circ_ldrs ldrs ON ldrs.circ_id = circ.id
        WHERE ldrs.circ_ldr = 'a'
        and ( acl.name ilike '%juv%' or acl.name ilike '%young%' )
        GROUP BY 2
) juv_a
left join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.copy copy ON copy.id = circ.target_copy
        JOIN asset.call_number call ON call.id = copy.call_number
        join asset.copy_location acl on acl.id = copy.location
        LEFT OUTER JOIN rogan.circ_ldrs ldrs ON ldrs.circ_id = circ.id
        WHERE ldrs.circ_ldr = 'a'
        and ( acl.name not ilike '%juv%' or acl.name not ilike '%young%' )
        GROUP BY 2
) adult_a on adult_a.shortname = juv_a.shortname
left join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.copy copy ON copy.id = circ.target_copy
        JOIN asset.call_number call ON call.id = copy.call_number
        join asset.copy_location acl on acl.id = copy.location
        LEFT OUTER JOIN rogan.circ_ldrs ldrs ON ldrs.circ_id = circ.id
        WHERE ldrs.circ_ldr != 'a'
        and ( acl.name ilike '%juv%' or acl.name ilike '%young%' )
        GROUP BY 2
) juv_not_a on juv_not_a.shortname = juv_a.shortname
left join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.copy copy ON copy.id = circ.target_copy
        JOIN asset.call_number call ON call.id = copy.call_number
        join asset.copy_location acl on acl.id = copy.location
        LEFT OUTER JOIN rogan.circ_ldrs ldrs ON ldrs.circ_id = circ.id
        WHERE ldrs.circ_ldr != 'a'
        and ( acl.name not ilike '%juv%' or acl.name not ilike '%young%' )
        GROUP BY 2
) adult_not_a on adult_not_a.shortname = juv_a.shortname
left join (  SELECT coalesce(count(circ.id),0) AS circs, org2.shortname AS shortname
        FROM action.non_cataloged_circulation circ
        left JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        where date(circ.circ_time) >= '2014-07-01' AND date(circ.circ_time) <= '2015-06-30'
        GROUP BY 2
) non on non.shortname = juv_a.shortname
;

drop table if exists rogan.action_circulation;
drop table if exists rogan.circ_ldrs;

/*

-- original form before I created the other tables to query from so it wouldn't take so long to run

SELECT juv_a.shortname as "Library",
juv_a.circs as "Juvenile Print Circs - H2", juv_not_a.circs as "Juvenile Non-Print Circs - H3",
juv_a.circs+juv_not_a.circs as "Total Juvenile Circs - H4",
adult_a.circs as "Adult Print Circs - H5", adult_not_a.circs as "Juvenile Non-Print Circs - H6",
adult_a.circs+adult_not_a.circs as "Total Adult Circs - H7"
FROM (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM action.circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.copy copy ON copy.id = circ.target_copy
        JOIN asset.call_number call ON call.id = copy.call_number
        join asset.copy_location acl on acl.id = copy.location
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        and ( acl.name ilike '%juv%' or acl.name ilike '%young%' )
        GROUP BY 2
) juv_a
join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM action.circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.copy copy ON copy.id = circ.target_copy
        JOIN asset.call_number call ON call.id = copy.call_number
        join asset.copy_location acl on acl.id = copy.location
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
        and ( acl.name not ilike '%juv%' or acl.name not ilike '%young%' )
        GROUP BY 2
) adult_a on adult_a.shortname = juv_a.shortname
join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM action.circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.copy copy ON copy.id = circ.target_copy
        JOIN asset.call_number call ON call.id = copy.call_number
        join asset.copy_location acl on acl.id = copy.location
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) != 'a'
        and ( acl.name ilike '%juv%' or acl.name ilike '%young%' )
        GROUP BY 2
) juv_not_a on juv_not_a.shortname = juv_a.shortname
join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM action.circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.copy copy ON copy.id = circ.target_copy
        JOIN asset.call_number call ON call.id = copy.call_number
        join asset.copy_location acl on acl.id = copy.location
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) != 'a'
        and ( acl.name not ilike '%juv%' or acl.name not ilike '%young%' )
        GROUP BY 2
) adult_not_a on adult_not_a.shortname = juv_a.shortname
;
*/


/*

            Interlibrary Loans
            H14   Provided to another library__________________________
            H15   Received from another library__________________________

*/

/*
-- borrowers at other libraries, these are like reciprocal borrowers
select parent.shortname as "Borrowing Library", count(circ.id) as "circs" from action.circulation circ
join actor.usr au on au.id = circ.usr
join actor.org_unit aou on aou.id = au.home_ou
join actor.org_unit parent on parent.id = aou.parent_ou
join actor.org_unit aou2 on aou2.id = circ.circ_lib
join actor.org_unit parent2 on parent2.id = aou2.parent_ou
WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30' and parent.id != parent2.id
group by 1 order by 1
;
*/

select loaners.shortname, loaners.loans as "Provided to another library - H14", borrowers.borrowers as "Received from another library - H15"
from ( SELECT copyparent.shortname, COUNT(circ.id) as "loans"
	FROM ACTION.circulation circ
	JOIN asset.COPY ac ON ac.id = circ.target_copy
	JOIN actor.org_unit copychild ON copychild.id = ac.circ_lib
	JOIN actor.org_unit copyparent ON copyparent.id = copychild.parent_ou
	JOIN actor.org_unit circchild ON circchild.id = circ.circ_lib
	JOIN actor.org_unit circparent ON circparent.id = circchild.parent_ou
	WHERE copyparent.id != circparent.id AND date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-01'
	GROUP BY 1
) loaners
join (
SELECT circparent.shortname, COUNT(circ.id) as "borrowers"
	FROM ACTION.circulation circ
	JOIN asset.COPY ac ON ac.id = circ.target_copy
	JOIN actor.org_unit copychild ON copychild.id = ac.circ_lib
	JOIN actor.org_unit copyparent ON copyparent.id = copychild.parent_ou
	JOIN actor.org_unit circchild ON circchild.id = circ.circ_lib
	JOIN actor.org_unit circparent ON circparent.id = circchild.parent_ou
	WHERE copyparent.id != circparent.id AND date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-01'
	GROUP BY 1
) borrowers on borrowers.shortname = loaners.shortname
order by 1
;