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';