Gale Patron Data - Branch Growth

drop table if exists rogan.gale;

create table rogan.gale as
select aua.street1, aua.street2, aua.city, aua.state, aua.post_code, circ.id as circ_id, circ.xact_start,
acl.name as "copy_location", ac.circ_modifier
from action.circulation circ
join actor.usr au on au.id = circ.usr
join actor.usr_address aua on aua.id = au.mailing_address
join actor.org_unit child_org on child_org.id = au.home_ou
join actor.org_unit parent_org on parent_org.id = child_org.parent_ou
join asset.copy ac on ac.id = circ.target_copy
join asset.copy_location acl on acl.id = circ.copy_location
where child_org.shortname = 'YCL-FM'  and extract(year from circ.xact_start) in ('2010','2011','2012','2013','2014','2015')
and aua.county ilike 'York' and aua.post_code != '' and street1 != ''
;

-- be warned, in this stage you could add addresses that don't qualify the above criteria if you don't use the same filters
insert into rogan.gale (street1, street2, city, state, post_code, circ_id, xact_start, copy_location, circ_modifier)  (
        select aua.street1, aua.street2, aua.city, aua.state, aua.post_code, circ.id, circ.circ_time, nct.name, nct.name
        from action.non_cataloged_circulation circ
        join actor.usr au on au.id = circ.patron
        join actor.usr_address aua on aua.id = au.mailing_address
        join actor.org_unit child_org on child_org.id = au.home_ou
        join actor.org_unit parent_org on parent_org.id = child_org.parent_ou
        join config.non_cataloged_type nct on nct.id = circ.item_type
        where child_org.shortname = 'YCL-FM'  and extract(year from circ.circ_time) in ('2010','2011','2012','2013','2014','2015')
        and aua.county ilike 'York' and aua.post_code != '' and street1 != ''
);

update rogan.gale set copy_location = 'STATS ONLY' where circ_modifier = copy_location;

insert into rogan.gale (street1, street2, city, state, post_code, circ_id, xact_start, copy_location, circ_modifier)  (
        select aua.street1, aua.street2, aua.city, aua.state, aua.post_code, usr_act.id, usr_act.event_time, act_type.ewho, act_type.ewho
        from actor.usr_activity usr_act
        join actor.usr au on au.id = usr_act.usr
                join actor.usr_address aua on aua.id = au.mailing_address
                join actor.org_unit child_org on child_org.id = au.home_ou
                join actor.org_unit parent_org on parent_org.id = child_org.parent_ou
                join config.usr_activity_type act_type on act_type.id = usr_act.etype
                where child_org.shortname = 'YCL-FM' and extract(year from usr_act.event_time) in ('2010','2011','2012','2013','2014','2015')
                and aua.county ilike 'York' and aua.post_code != '' and street1 != ''
                and act_type.ehow = 'sip2' and act_type.ewhat = 'verify'
);


-- cleans up SIP activity not tied to any discernable account
-- should be redundant now with the criteria set above but still here just in case
delete from rogan.gale where post_code = '99999';

-- clean out PO boxes which don't map correctly
delete from rogan.gale where street1 ilike 'PO %' or street1 ilike 'P.O%';

-- cleanup for conformity
update rogan.gale set street2 = null where street2 = '';

-- ----------------------------------------------------------------------------------------------------------
-- Patron profiles gale report ------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------

select street1 as "ADDRESS", street2 as "SUITE (OPTIONAL)", city as "CITY", state as "STATE", post_code as "ZIP CODE",
count(circ_id) as "CHECKOUTS (OPTIONAL)", max(date(xact_start)) as "DATE"
from rogan.gale
-- where state ilike 'sc' or state ilike 'nc'
-- where post_code ilike '297%'
group by 1, 2, 3, 4, 5;