//MAP HERE THE VALUES BETWEEN nativeness_fk, life_stage_fk and occurrence_status_fk between the original source and
//the SDR common data model
$sql = <<<SQL
insert into distribution_unit(occurrence_status_fk,start_day_in_year,end_day_in_year,distribution_fk,nativeness_fk,life_stage_fk,interpreted_geom,status_tags_fk)
select
CASE WHEN 1=1 THEN 1 ELSE 0 END as occurrence_status_fk,
start_day as start_day_in_year,
end_day as end_day_in_year,
d.id as distribution_fk,
CASE WHEN 1=1 THEN 6 ELSE 0 END as nativeness_fk,
CASE WHEN 1=1 THEN null ELSE 1 END as life_stage_fk,
the_geom as interpreted_geom,
st.id as status_tags_fk
from imported_shapefile as ish inner join distribution as d on ish.original_name_id=d.original_name_id left join status_tags as st on ish.status=st.tag
WHERE d.resource_fk={$resourceId}
SQL;
runSqlCommand($sql);
//remove temporary tables
echo "\n\nFINISHED!!!\n\n";
runSqlCommand("DROP TABLE imported_ecat_names");
runSqlCommand("DROP TABLE imported_shapefile");
runSqlCommand("vacuum analyze resource");
runSqlCommand("vacuum analyze distribution");
runSqlCommand("vacuum analyze status_tags");
runSqlCommand("vacuum analyze distribution_unit");
runSqlCommand("vacuum analyze name_usage");
function runSqlCommand($command)
{
global $psqlfol, $host, $dbname, $user, $pass;
exec("{$psqlfol}/psql -h{$host} -d{$dbname} -U{$user} -c\"{$command}\"");
}
\tWHEN name='Alberta' THEN 'CA-AB'
\tWHEN name='Manitoba' THEN 'CA-MB'
\tWHEN name='British Columbia' THEN 'CA-BC'
\tWHEN name='Yukon Territory' THEN 'CA-YT'
\tWHEN name='Saskatchewan' THEN 'CA-SK'
\tWHEN name='Quebec' THEN 'CA-QC'
\tWHEN name='New Brunswick' THEN 'CA-NB'
\tWHEN name='Northwest Territories' THEN 'CA-NT'
\tWHEN name='Nova Scotia' THEN 'CA-NS'
\tWHEN name='Ontario' THEN 'CA-ON'
\tWHEN name='Nunavut' THEN 'CA-NU'
\tWHEN name='Prince Edward Island' THEN 'CA-PE'
ELSE null END as area_code,
name as area_name,
{$namedAreareferenceId} as named_area_reference_fk
from imported_areas_shapefile WHERE name in ('Alberta','Manitoba','British Columbia','Yukon Territory','Saskatchewan','Quebec','New Brunswick','Northwest Territories','Nova Scotia','Ontario','Nunavut','Prince Edward Island') AND area_code
SQL;
$sql = "UPDATE named_area SET named_area_geom_fk = (SELECT id from named_area_geom WHERE temp_id=named_area.area_code) WHERE named_area_geom_fk is null";
runSqlCommand($sql);
runSqlCommand("ALTER TABLE named_area_geom DROP COLUMN temp_id");
runSqlCommand("DROP TABLE imported_areas_shapefile");
runSqlCommand("vacuum analyze named_area");
runSqlCommand("vacuum analyze named_area_geom");
echo "\n\nFINISHED!!!\n\n";
function runSqlCommand($command)
{
global $psqlfol, $host, $dbname, $user, $pass;
exec("{$psqlfol}/psql -h{$host} -d{$dbname} -U{$user} -c\"{$command}\"");
}
