# Add Species 
# This script describes how to add Species to the CTFS/ForestGeo database
# 1. ADD a whole species list from a file to a new database
# 2. ADD a single species records or several new species 


/*

spcode, genus and species are required, the rest optional but highly recommended.

spcode* – also known as mnemonic, a code used in the field to identify the 
         species of the tree. At ForestGEO, most sites use 6 letter codes 
         where the first 4 letters are from the Genus name and the last two 
         from the species.  If two species yield the same code, then another
         letter or a number in the last character may be used to break the tie. 

         For example, the first character of the species names together with 
         a unique character was used to form the following 6 character code.
 
         For Shorea macroptera subsp. baillonii and Shorea macrophylla, both 
         would be SHORMA. The species codes ended up being SHORMB and SHORMC, 
         respectively.

         You should use a similar naming convention for each morphospecies 
         incorporating details you know. For example, using LITSBL 
         (Litsea “Big Leaf”) or APORS1 (Aporosa sp. 1) are fine – 
         as long as each code applies to only one morphospecies.  
         These can be changed once identification is more complete. 

         Other combinations are also acceptable. Some sites use 3 letters 
         from the genus and 3 from the species, while others use 4 letters 
         instead of 6 (2 letters from the genus and 2 from the species).

genus*       – the taxonomic genus name (if unknown genus, use “Unidentified”)
species*     – the taxonomic species name
family	    – the taxonomic family name identified in the field.  
              note: the database will assign the family based on the genus  
authority   – (optional) author of the species name
idlevel 	– to indicate to which level the species or morphospecies has been 
              identified. Options are as follows:

    • species  – species known.
    • subsp    – subspecies known. 
    • genus    - genus known, species unknown but single morphospecies
    • family   - family known, genus and sp unknown but single morphospecies
    • none     –  no identification has been done but single morphospecies
    • multiple – species code represents more than a single morphospecies 
                 eg UNIDEN (for all species yet to be identified)
    • description  - notes from the field or taxonomist   
    • lifeform     - valid values are   Emergent Tree,Tree,Midcanopy Tree,
                                       Understory Tree,Shrub,Herb,Liana     
           
*/ 
-- **********************************************************************************************
-- Set up the database.  Replace the test database "blue" with the last frozen 
-- version of your database. Eg USE current_yoursite;  SOURCE ../bci_current_20August2020.sql;

DROP DATABASE IF EXISTS blue;
CREATE DATABASE IF NOT EXISTS blue;
USE blue;
SOURCE ../sampledatabases/blue.sql;


-- You must run the code below in your database.  Once you have run it for one script it is 
-- not necessary to run it for others but it will not cause a problem if you do. 
-- Do not remove the commands below. Change the path to reflect your directory path.
# 1. Installs a procedure that creates DBHAttributes from codes separated by a semicolon.
# The following columns must exist in the table tempinserttrees. 
# codes varchar(20),
# dbhid int,
# 2. Installs Log Procedures. 
SOURCE Procedures/LogProcedures.sql;
SOURCE Procedures/Utilities.sql;

# Some older databases have the old and new columns as NOT NULL
# the statement below corrects the error. 
ALTER TABLE Log MODIFY old VARCHAR(2048), MODIFY new VARCHAR(2048);
SELECT @n1:=count(*) FROM Log;

# This table is required in the database. 
CREATE TABLE TempLogFeeder(
  ChangedTableID int(10),
  ChangedColumn varchar(64),
  ChangeReason VARCHAR(256)
  );
  
-- end database setup **********************************************************************  

#
----------------------------------------------------------------------------------------------------------
-- 1. ADD a whole species list
---------------------------------------------------------------------------------------------------------- 
# *******************************************************************************************************
#  LOAD IN THE DATA YOU WANT TO INSERT
# *******************************************************************************************************
# This is a key table in the system so take the time to review the file 
# for spelling errors, etc.  Make sure the IDLevels are filled in. There
# should be at least one species code for unidentified species.  

# Species file with the columns listed above. 

CREATE TABLE IF NOT EXISTS tempaddspecies (
  Mnemonic char(10) DEFAULT NULL,
  Genus varchar(32) DEFAULT NULL,
  SpeciesName char(64) DEFAULT NULL,
  FieldFamily char(32) DEFAULT NULL,
  Authority varchar(128) DEFAULT NULL,
  IDLevel enum('subspecies','species','superspecies','genus','family','multiple','none','variety') DEFAULT NULL,
  Description varchar(128) DEFAULT NULL,
  Lifeform enum('Emergent Tree','Tree','Midcanopy Tree','Understory Tree','Shrub','Herb','Liana') DEFAULT NULL,
  LocalName VARCHAR(128)
) ; 

LOAD DATA LOCAL INFILE "../datafiles/addspecies.csv" 
 INTO TABLE tempaddspecies
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
 LINES TERMINATED BY '\n' IGNORE 1 LINES 
 (Mnemonic, Genus, SpeciesName, FieldFamily, Authority, IDLevel,Description, lifeform,localname);

INSERT INTO Species (Mnemonic,Speciesname,GenusID,  Fieldfamily,Authority, 
 IDLevel,Description, Lifeform, LocalName, CurrentTaxonFlag,ObsoleteTaxonFlag)
 SELECT a.mnemonic, a.speciesname, b.genusid, a.fieldfamily,a.authority,
 a.idlevel,a.description,a.lifeform,a.localname,1,0
 FROM tempaddspecies a, Genus b WHERE a.genus=b.genus;
 
 
# *******************************************************************************************************
#  CREATE LOG RECORDS FOR EACH TABLE YOU ARE INSERTING INTO
# *******************************************************************************************************
SET @personnelid=1, @action="I";

# Note you may choose to replace the hardcoded change reason with the comments from tempinserttrees 
# just replace "reason for inserting these records" with a comments column in your temp table
#  in the SQL statements below
# Log record for Species

TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) SELECT  b.SpeciesID,"ALL","reason for inserting these records" FROM tempaddspecies a, Species b WHERE a.mnemonic=b.mnemonic;
CALL logRecordSet(@personnelid,"Species",@action) ; 


# *******************************************************************************************************
#  Rebuild Views. Replace the first parameter with your database name 
# *******************************************************************************************************

CALL ctfsweb_webuser.CreateViewTaxonomy("blue","'foo'");
CALL ctfsweb_webuser.CreateFullView( "blue","ViewFullTable");


----------------------------------------------------------------------------------------------------------
-- 2. ADD  individual species
---------------------------------------------------------------------------------------------------------- 


SELECT @genusid:=genusid FROM Genus WHERE genus= "Unidentified"; # REPLACE WITH YOUR GENUS
SET @idlevel="multiple";  # REPLACE WITH YOUR IDLEVEL
SET  @lifeform="tree";    # REPLACE WITH YOUR LIFEFORM
# Replace all variable values 
SET @spcode="test", @speciesname="testus", @fieldfamily="Fabaceae";
SET @authority="Some Great Botan.", @desc="something about the species";

INSERT INTO Species (mnemonic,speciesname,genusid, fieldfamily,authority, 
  idlevel,description, lifeform, currenttaxonflag, obsoletetaxonflag)
  SELECT @spcode,@speciesname,@genusid,@fieldfamily,@authority,
  @idlevel,@desc,@lifeform,1,0;
 
# NOTE:  NULL (no quotes) is a valid value for non-mandatory fields.


-- Log the changes
SET @personnelid=1, @action="I";

SELECT "Inserting a new species into the log";
TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) 
  SELECT speciesid,"All","new species found" FROM Species WHERE mnemonic=@spcode;
CALL logRecordSet(@personnelid,"Species",@action) ; 

 

# *******************************************************************************************************
# Rebuild Views. Replace the first parameter with your database name 
# *******************************************************************************************************

CALL ctfsweb_webuser.CreateViewTaxonomy("blue","'foo'");
CALL ctfsweb_webuser.CreateFullView( "blue","ViewFullTable");

