SELECT '# specimen replicates' AS ___;
SELECT count(*) AS count
FROM specimenreplicate
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
;

SELECT '# families' AS ___;
SELECT count(DISTINCT plantname) AS count
FROM specimenreplicate
JOIN taxondetermination USING (taxonoccurrence_id)
JOIN plantconcept USING (plantconcept_id)
JOIN plantname
    ON plantname.plantname_id = plantconcept.plantname_id
    AND plantname.rank = 'Family'
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
AND taxondetermination.isoriginal
;

SELECT '# genera' AS ___;
SELECT count(DISTINCT plantname) AS count
FROM specimenreplicate
JOIN taxondetermination USING (taxonoccurrence_id)
JOIN plantconcept USING (plantconcept_id)
JOIN plantname
    ON plantname.plantname_id = plantconcept.plantname_id
    AND plantname.rank = 'Genus'
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
AND taxondetermination.isoriginal
;

SELECT '# species' AS ___;
SELECT
    count(DISTINCT genus_plantname.plantname||','||species_plantname.plantname)
        AS count
FROM specimenreplicate
JOIN taxondetermination AS genus_taxondet
    ON genus_taxondet.taxonoccurrence_id = specimenreplicate.taxonoccurrence_id
    AND genus_taxondet.isoriginal
JOIN plantconcept AS genus_plantconcept
    ON genus_plantconcept.plantconcept_id = genus_taxondet.plantconcept_id
JOIN plantname AS genus_plantname
    ON genus_plantname.plantname_id = genus_plantconcept.plantname_id
    AND genus_plantname.rank = 'Genus'
JOIN taxondetermination AS species_taxondet
    ON species_taxondet.taxonoccurrence_id =
        specimenreplicate.taxonoccurrence_id
    AND species_taxondet.isoriginal
JOIN plantconcept AS species_plantconcept
    ON species_plantconcept.plantconcept_id = species_taxondet.plantconcept_id
JOIN plantname AS species_plantname
    ON species_plantname.plantname_id = species_plantconcept.plantname_id
    AND species_plantname.rank = 'Species'
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
;

SELECT '# binomials' AS ___;
SELECT count(DISTINCT plantname) AS count
FROM specimenreplicate
JOIN taxondetermination USING (taxonoccurrence_id)
JOIN plantconcept USING (plantconcept_id)
JOIN plantname
    ON plantname.plantname_id = plantconcept.plantname_id
    AND plantname.rank = 'Binomial'
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
AND taxondetermination.isoriginal
;