Statistic module¶
Setup¶
To enable statistics module needed to do following:
- Setup datasource described at environment setup if not was set up before
- Enable statistic module via visualVm at Qi4j > StreamflowServer > Application > Statistics > Service > statistics > Configuration. Set
enabled
totrue
- Enable LiquiBase service(needed for generation of DB structure) at Qi4j > StreamflowServer > Domain > Database > Service > liquibase > Configuration. Set
enabled
totrue
and if needed regenerate from scratch setlastEventDate
value to0
Usage¶
SQL queries¶
Case count for all closed cases, built on closing organization, between 2 dates:
select count(case_id) from cases where created_on >= "2011-01-01 00:00:01" and created_on <= "2011-12-31 23:59:59" and assigned_organization in (select id from organization where organization.left>=(select organization.left from organization where name="Jönköping") and organization.right <=(select organization.right from organization where name="Jönköping"))
Case count for all closed cases, built on case type owner, between 2 dates: (Works as long as all case type owners are organizational unit. If there are case type owners that are projects we have to dig one level deeper!)
select count(case_id) from cases where created_on >= "2011-01-01 00:00:01" and created_on <= "2011-12-31 23:59:59" and casetype_owner in (select id from organization where organization.left>=(select organization.left from organization where name="Jönköping") and organization.right <=(select organization.right from organization where name="Jönköping"))
Check if all case types are owned by OU’s: (Expected result - empty set)
select distinct casetype_owner from cases where casetype_owner not in (select id from organization);
Check cases without case type:
select count(case_id) from cases where casetype is null and created_on >= "2011-03-01 00:00:01" and created_on <= "2012-01-31 23:59:59";
List of case counts for a certain period for all case type owners( regardless if case type owner is OU or project):
select descriptions.description, count(case_id) as numbers from cases, descriptions where cases.casetype_owner = descriptions.id and created_on >= "2011-03-01 00:00:01" and created_on <= "2012-01-31 23:59:59" group by casetype_owner order by numbers desc;
List of case counts per case type for a certain period:
select descriptions.description, count(case_id) as numbers from cases, descriptions where cases.casetype = descriptions.id and created_on >= "2011-03-01 00:00:01" and created_on <= "2012-01-31 23:59:59" group by casetype order by numbers desc;
Some other queries run for Oskarshamn statistic.
select date_format( closed_on, '%Y-%m' ) as period, count(case_id) as number from casesdescriptions where closed_on >= '2011-01-01' and closed_on <= '2012-01-31 23:59:59' group by period order by periodselect date_format( closed_on, '%Y-%m' ) as period, count(case_id) as number from casesdescriptions where closed_on >= '2011-01-01' and closed_on <= '2012-01-31 23:59:59' and casetype is not null group by period order by periodselect date_format( cases.closed_on, '%Y-%m' ) as period, count(case_id) as number from cases where cases.casetype_owner in ( select id from organization where organization.left >= 1 and organization.right <= 6 ) and closed_on >= '2011-01-01' and closed_on <= '2012-01-31 23:59:59' and casetype_owner is not null group by period order by periodselect casetype_owner, date_format(closed_on, '%Y-%m' ) as period, count(case_id) as number from casesdescriptions where closed_on >= '2011-01-01' and closed_on <= '2012-01-31 23:59:59' and casetype_owner is not null group by casetype_owner, period order by casetype_owner, period