-- Name: _plots_01_count_of_projects; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT count(*) AS projects

   FROM projects;

-- Name: _plots_02_list_of_project_names; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT projects.project_name

   FROM projects;

-- Name: _plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT count(DISTINCT (pm."SiteCode")::text) AS plots

   FROM (projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)));

-- Name: _plots_04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT p.project_name, 

    count(*) AS plots

   FROM (projects p

-- Name: _plots_05_list_of_plot_codes_by_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM (projects p

-- Name: _plots_06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM (((projects p

-- Name: _plots_06a_list_of_stems; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    (s.stem_id)::text AS stem_id

-- Name: _plots_07_list_of_plots_which_use_counts_of_indiv_per_species; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM ((projects p

-- Name: _plots_08_list_of_plots_which_use_percent_cover; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM ((projects p

-- Name: _plots_09_list_of_plots_which_use_line_intercept; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM ((projects p

-- Name: _plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    sum(po."NoInd") AS individuals

-- Name: _plots_10a_aggregate_observation_individual_counts; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    (po."PlotObsID")::text AS individual_id, 

-- Name: _plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    count(DISTINCT s.stem_id) AS stems

-- Name: _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT a.project_name, 

    a.plot_code, 

    count(a.taxon) AS taxa

-- Name: _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon

-- Name: _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 

-- Name: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 

-- Name: _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 

-- Name: _plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    count(DISTINCT po."Line") AS subplots

-- Name: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    po."Line" AS subplot_code

-- Name: _plots_19_count_of_censuses_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    count(DISTINCT ARRAY[po.census_date]) AS inventories

if self_not_included; then

func_override export___schema.pg.sql.run

{

	echo_func

	#need explicit schema so matching tables from other schemas are not included

	export___schema.pg.sql.run --table="\"$schema\"._[[:alpha:]]+_\d*" "$@"

}

fi