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
;

SELECT 'collection codes' AS ___;
SELECT DISTINCT collectioncode_dwc
FROM specimenreplicate
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
;

SELECT '# catalog numbers' AS ___;
SELECT count(DISTINCT catalognumber_dwc) AS count
FROM specimenreplicate
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
;

SELECT 'duplicate catalog numbers' AS ___;
SELECT catalognumber_dwc, count(*) AS count
FROM specimenreplicate
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
GROUP BY catalognumber_dwc
HAVING count(*) > 1
ORDER BY catalognumber_dwc NULLS FIRST/* to match MySQL */
;

SELECT '# counties' AS ___;
SELECT count(DISTINCT
    COALESCE(country_namedplace.placename, '')
    ||','|| COALESCE(state_namedplace.placename, '')
    ||','|| COALESCE(county_namedplace.placename, '')
    ) AS count
FROM specimenreplicate
JOIN taxonoccurrence USING (taxonoccurrence_id)
JOIN locationevent USING (locationevent_id)
JOIN locationdetermination AS country_locationdet
    ON country_locationdet.location_id = locationevent.location_id
JOIN namedplace AS country_namedplace
    ON country_namedplace.namedplace_id = country_locationdet.namedplace_id
    AND country_namedplace.placesystem = 'area|country|territory'
JOIN locationdetermination AS state_locationdet
    ON state_locationdet.location_id = locationevent.location_id
JOIN namedplace AS state_namedplace
    ON state_namedplace.namedplace_id = state_locationdet.namedplace_id
    AND state_namedplace.placesystem = 'region|state|province'
JOIN locationdetermination AS county_locationdet
    ON county_locationdet.location_id = locationevent.location_id
JOIN namedplace AS county_namedplace
    ON county_namedplace.namedplace_id = county_locationdet.namedplace_id
    AND county_namedplace.placesystem = 'county'
WHERE specimenreplicate.datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
;