SQLs

These SQL should provide most of the statistics

Number of Planters under the CBOS

SELECT ent.id, ent."name", count(pla.id) as "Number of planters" FROM public.planter pla left join public.entity ent on pla.organization_id = ent.id WHERE pla.organization_id in (select entity_id from getEntityRelationshipChildren(178)) group by ent."name";

Number of Approved trees by CBO

SELECT ent."name", count(tre.id) as "Number of captures" FROM public.planter pla left join public.entity ent on pla.organization_id = ent.id left join public.trees tre on pla.id = tre.planter_id WHERE pla.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and tre.approved = true group by ent."name";

SELECT ALL CBO NAMES

select entity."name" from entity WHERE entity.id in (select entity_id from getEntityRelationshipChildren(178))

Total number of captures as of 1st of Sept 2021 (Phase 2)

SELECT count(*) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) AND a.time_created > '2021-09-01 00:00:01';

Total number of APPROVED Captures until 1st of Sept (Phase1)

SELECT count(*) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and a.approved = true AND a.time_created < '2021-09-01 00:00:01';

Total number of rejected Captures

SELECT count(*) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and a.rejection_reason notnull;

Number of Species verified

SELECT count(distinct(species_id)) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and a.species_id notnull

List of Planters and their associated CBOS also includes total of trees captured, rejected, approved and details (Phase 2)

SELECT p.id, p.first_name, p.last_name, p.phone, count(t.id), p.organization_id, p.organization, count() AS TOTAL, count() FILTER (WHERE t.rejection_reason isnull) AS VERIFIED, count() FILTER (WHERE t.approved = false) AS UNAPPROVED, count() FILTER (WHERE t.rejection_reason notnull) AS REJECTED FROM public.trees t join planter p on t.planter_id = p.id WHERE p.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and t.time_created > '2021-09-01 00:00:01' group by p.id order by count(t.id) desc;

Number of captures marked as dead (apply time filter for Phases)

SELECT count(*) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and rejection_reason = 'dead'

complete Dataset (filtered by date)

SELECT b.id as PlanterId, b.first_name, b.last_name, b.organization, a.id as treeID, a.lat, a.lon, a.image_url, a.active, a.planter_identifier, a.note, a.species, a.species_id, a.verified, a.capture_approval_tag, a.rejection_reason, a.time_created FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) AND a.time_created BETWEEN '2021-09-01 00:00:01' and '2021-11-01 00:00:01';