Statistics - Circulation

/*
Feb 2015, ripping up and redoing this old work from a few years ago to make it cleaner and more flexible
will include pre-calculated circ tables by user profile, location and circ mod by org unit and month / fiscal year
will include add on data sources to add value, population, poverty rates, value of items
some templates will show it's power in the reporter but eventual goal is a director's dashboard
*/


/* eventually we could have this on a cron task to do it monthly and auto add each month for review */

/* let's create a table to define the fiscal years that we want to group by */

create table rogan.statistical_years (
    id      serial,
    type    text    not null,
    name    text    not null,
    year_start  timestamp with time zone    not null,
    year_end    timestamp with time zone    not null
);

insert into rogan.statistical_years (type,name,year_start,year_end) values ('fiscal','2008/09','2008-07-01 00:00:00.000000-05','2009-06-30 23:59:59.000000-05');
insert into rogan.statistical_years (type,name,year_start,year_end) values ('fiscal','2009/10','2009-07-01 00:00:00.000000-05','2010-06-30 23:59:59.000000-05');
insert into rogan.statistical_years (type,name,year_start,year_end) values ('fiscal','2010/11','2010-07-01 00:00:00.000000-05','2011-06-30 23:59:59.000000-05');
insert into rogan.statistical_years (type,name,year_start,year_end) values ('fiscal','2011/12','2011-07-01 00:00:00.000000-05','2012-06-30 23:59:59.000000-05');
insert into rogan.statistical_years (type,name,year_start,year_end) values ('fiscal','2012/13','2012-07-01 00:00:00.000000-05','2013-06-30 23:59:59.000000-05');
insert into rogan.statistical_years (type,name,year_start,year_end) values ('fiscal','2013/14','2013-07-01 00:00:00.000000-05','2014-06-30 23:59:59.000000-05');
insert into rogan.statistical_years (type,name,year_start,year_end) values ('fiscal','2014/15','2014-07-01 00:00:00.000000-05','2015-06-30 23:59:59.000000-05');

select * from rogan.statistical_years;

/* now let's design a table to hold monthly circulation stats */

create table rogan.stats_circs (
    id          serial,
    circs       int,
    circ_lib    int,
    circ_modifier   text,
    location    int,
    stats_year  int,
    month       int,
    usr_profile int
);


/* now we can insert each additional year by just using the y.id = to increment once the appropriate year is in the
statistical_year table */

insert into rogan.stats_circs (circs, circ_lib, circ_modifier, location, stats_year, month, usr_profile) (
    select count(acirc.id) as circs, acirc.circ_lib, ac.circ_modifier, acl.id as location, y.id as stats_year,
    extract(month from acirc.xact_start) as month, au.profile as usr_profile
    from action.circulation acirc
    join asset.copy ac on ac.id = acirc.target_copy
    join actor.org_unit aou on aou.id = acirc.circ_lib
    join rogan.statistical_years y on y.id = 6
    join asset.copy_location acl on acl.id = ac.location
    join actor.usr au on au.id = acirc.usr
    where acirc.xact_start >= y.year_start and acirc.xact_start <= y.year_end
    group by 2, 3, 4, 5, 6, 7
);

/* now let's add in the stats only checkouts */
insert into rogan.stats_circs (circs, circ_lib, circ_modifier, location, stats_year, month, usr_profile) (
    select count(ncc.id) as circs, ncc.circ_lib, nct.name as circ_modifier, 1 as location, y.id as stats_year,
    extract(month from ncc.circ_time) as month, au.profile as usr_profile
    from action.non_cataloged_circulation ncc
    join actor.org_unit aou on aou.id = ncc.circ_lib
    join config.non_cataloged_type nct on nct.id = ncc.item_type
    join rogan.statistical_years y on y.id = 6
    join actor.usr au on au.id = ncc.patron
    where ncc.circ_time >= y.year_start and ncc.circ_time <= y.year_end
    group by 2, 3, 4, 5, 6, 7
);