Annual State Statistics - Sections G & H

/*
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 circ mod and shelving location
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

drop table if exists rogan.action_circulation;

/* create a table and map per circulation */

create table rogan.action_circulation as
select * from action.circulation where date(create_time) between '2014-07-01' and '2015-06-30';

alter table rogan.action_circulation add column print boolean default TRUE;

/* map print = true based on these circ mods */

update rogan.action_circulation set print = false where target_copy in (
	select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
	where ac.circ_modifier in ('ELECTRONIC EQUIPMENT','VIDEO','AUDIO','EREADER','PLAYAWAY','AUDIO BOOK',
	'THEME KIT','EQUIPMENT','KIT','INTERNET','GAME','LEASE DVD')
);

/* then it got a lot more manual looking at shelving locations.  I made a review of a sample of five thousand shelving
location entries and based on that assumed that copies with shelving locations saying AUDIO or VIDEO or EQUIPMENT
should be changed to non-print then I re-ran it with exclusions and found more like DVD */

update rogan.action_circulation set print = false where target_copy in (
	select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
	join asset.copy_location acl on acl.id = ac.location
	where ac.circ_modifier in ('ELECTRONIC EQUIPMENT','VIDEO','AUDIO','EREADER','PLAYAWAY','AUDIO BOOK',
	'THEME KIT','EQUIPMENT','KIT','INTERNET','GAME','LEASE DVD')
	and (acl.name ilike '%audio%' or acl.name ilike '%video%' or acl.name ilike '%equipment%' or acl.name ilike '%dvd%'
	or acl.name ilike '%books on cd%' or acl.name ilike '%books on cassette%' or acl.name ilike '%music cd%' or
	acl.name ilike '%adult 360%' or acl.name ilike '%adult ps2%')
);

/* now I will go through all distinct occurances of the library systems and these circ mods with other shelving
location names excluding those that had audio, video or unknown in the name */

select DISTINCT aou.parent_ou, ac.circ_modifier, acl.name
from asset.copy ac
join actor.org_unit aou on aou.id = ac.circ_lib
join asset.copy_location acl on acl.id = ac.location
where ac.circ_modifier in ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')
and acl.name not ilike '%audio%' and acl.name not ilike '%audio%' and acl.name not ilike '%video%'
and acl.name not ilike '%equipment%' and acl.name not ilike '%dvd%' and acl.name not ilike '%books on cd%'
and acl.name not ilike '%books on cassette%' and acl.name not ilike '%music cd%' and
acl.name not ilike '%adult 360%' and acl.name not ilike '%adult ps2%'
order by 1, 2;

alter table rogan.action_circulation add column adult boolean default true;

update rogan.action_circulation set adult = false where target_copy in (
	select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
	join asset.copy_location acl on acl.id = ac.location
	where acl.name ilike '%juv%' or acl.name ilike '%young%'
);

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+coalesce(non.circs,0) 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
        WHERE circ.print = true and circ.adult = false
        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
        WHERE circ.print = true and circ.adult = true
        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
        WHERE circ.print = false and circ.adult = false
        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
        WHERE circ.print = false and circ.adult = true
        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
;



/*

            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-30'
	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-30'
	GROUP BY 1
) borrowers on borrowers.shortname = loaners.shortname
order by 1
;