# Example of how to insert a tree from a CTFS database
# Shameema Esufali March 2014
#
# To insert trees
# Create an excel sheet with the essential data.
# The sheet should have tag, stemtag*, spcode,quadrat,qx,qy,dbh,codes,hom and dateofmeasure
# to create a valid census entry.
# Save it as a csv file from excel.
#
# Replace the path and name of this file in the LOAD DATA line below
# Please note the detail of the FIELDS clause of the LOAD DATA command and make sure 
# that they match your csv file.  For example if the columns are separated by a comma then 
# replace "\t" with ",".  Check the number of header lines and change the INGNORE clause if you need to.
#
# Note: You need to create a Log record for every change to the database. 
# You must first insert into the permanent tables and then create the log entries with the new data.
#
# *******************************************************************************************************
# ***** LOAD IN THE DATA YOU WANT TO INSERT
# *******************************************************************************************************
/*
Change database name below to your database
*/

USE blue;

/*
Do not remove the command below.  It is a procedure that creates DBHAttributes
from codes separated by a semicolon. Change the path to reflect your directory path.
The storedprocgetcodes.sql file must be downloaded and stored. 

Your input table name must not be changed from tempinserttrees.  

The following columns must exist in the table tempinserttrees. 
codes varchar(20),
dbhid int,
censusid int,
*/
SOURCE  ~/Documents/databasemanagement/errormanagement/utilities.sql;

CREATE TABLE tempinserttrees (
censusno VARCHAR(5),
tag VARCHAR(15),
stemtag  VARCHAR(15),
spcode  VARCHAR(25),
quadrat VARCHAR(10),
qx VARCHAR(15),
qy VARCHAR(15),
dbh VARCHAR(15),
codes VARCHAR(25),
hom VARCHAR(15),
exactdate VARCHAR(25),
comments VARCHAR(128)
)
ENGINE=INNODB;

LOAD DATA LOCAL INFILE '/home/asiaplots/Documents/databasemanagement/sampledatabases/inserttrees.csv' 
 INTO TABLE tempinserttrees FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' 
 LINES TERMINATED BY '\n' IGNORE 1 LINES 
 (censusno,tag,stemtag,spcode,quadrat,qx,qy,dbh,codes,hom,exactdate,comments);

ALTER TABLE tempinserttrees 
ADD COLUMN quadratid int, ADD COLUMN speciesid int, 
ADD COLUMN treeid int, ADD COLUMN stemid int, 
ADD COLUMN DBHID int, ADD COLUMN DBHAttributeid int, ADD COLUMN primarystem char(15),
ADD COLUMN censusid int, ADD COLUMN plotid int;

# Collect all the IDs required for inserting the data. Note; If your quadrat or census data is 
# wrong you will have null values in the id column and the inserts could fail. 
# 
UPDATE tempinserttrees SET plotid=1, quadrat=lpad(quadrat,4,'0');
UPDATE tempinserttrees i, Census c SET i.censusid=c.censusid WHERE i.censusno=c.plotcensusnumber;
UPDATE tempinserttrees i, Quadrat q SET i.quadratid=q.quadratid WHERE i.quadrat=q.quadratname;
UPDATE tempinserttrees i,Species s SET i.speciesid=s.speciesid WHERE i.spcode=s.mnemonic;
UPDATE tempinserttrees i SET i.primarystem="main" WHERE i.codes LIKE "%main%";

# *******************************************************************************************************
# ***** INSERT RECORDS INTO Tree,Stem,DBH and DBHAttributes IN THAT ORDER
# *******************************************************************************************************

# Trees
INSERT INTO Tree(tag,speciesid)
 SELECT DISTINCT tag,speciesid FROM tempinserttrees;
# Collect the treeid for the next step. 
UPDATE tempinserttrees i,Tree t SET i.treeid =t.treeid WHERE i.tag=t.tag;

# Stems
INSERT INTO Stem (treeid,stemtag,quadratid,qx,qy)
 SELECT treeid,stemtag,quadratid,qx,qy FROM tempinserttrees;
# Collect the stemid for the next step. 
UPDATE tempinserttrees i, Stem s SET i.stemid=s.stemid WHERE i.treeid=s.treeid and i.stemtag=s.stemtag;

# DBH
INSERT INTO DBH (censusid,stemid,dbh,hom,primarystem,exactdate,comments)
 SELECT censusid,stemid,dbh,hom,primarystem,exactdate,comments FROM tempinserttrees;
 # Collect the dbhid for the next step. 
UPDATE tempinserttrees i, DBH d SET i.dbhid=d.dbhid WHERE i.stemid=d.stemid AND i.censusid=d.censusid;


# DBHAttributes
# Because dbh codes may need to be split up this insert has to be done
# using a program in the 

CALL insertdbhattributes();


# *******************************************************************************************************
# ***** CREATE LOG RECORDS FOR EACH TABLE YOU ARE INSERTING INTO
#  Note: You should be able to use this code without any changes 
#  except the description or reason for insert.
# *******************************************************************************************************

# Log record for Trees
INSERT INTO Log (logid,personnelid,changedtable,primarykey,changedcolumn,changedate,description,action,old,new)
SELECT null,1,"Tree","Treeid","ALL",curdate(),'reason for inserting these reocrds',"I","NA",
concat_ws("","TreeID=",t.TreeID,",Tag=",t.Tag,",SpeciesID=",t.SpeciesID,
      ",SubSpeciesID=",t.SubSpeciesID) new
FROM Tree t JOIN tempinserttrees  USING (treeid);


# Log record for Stems
INSERT INTO Log (logid,personnelid,changedtable,primarykey,changedcolumn,changedate,description,action,old,new)
SELECT null,1,"Stem","StemID","ALL",curdate(),'reason for inserting these reocrds',"I","NA",
concat_ws("","StemID=",s.StemID,",TreeID=",s.TreeID,",StemTag=",s.StemTag,
      ",StemDescription=",s.StemDescription,",QuadratID=",s.QuadratID,",StemNumber=",s.StemNumber,
      ",Moved=",s.Moved,",GX=",s.GX,",GY=",s.GY,",GZ=",s.GZ,",PX=",s.PX,",PY=",s.PY,",PZ=",s.PZ,
      ",QX=",s.QX,",QY=",s.QY,",QZ=",s.QZ) new
FROM Stem s JOIN tempinserttrees  USING (stemid);

# Log record for DBH records
INSERT INTO Log (logid,personnelid,changedtable,primarykey,changedcolumn,changedate,description,action,old,new)
SELECT null,1,"DBH","DBHID","ALL",curdate(),'reason for inserting these records',"I","NA",
concat_ws("","CensusID=",d.CensusID,",StemID=",d.StemID,",DBH=",d.DBH,",HOM=",d.HOM,
",PrimaryStem=",d.PrimaryStem,",ExactDate=",d.ExactDate,",DBHID=",d.DBHID,",Comments=",d.Comments) new
FROM DBH d JOIN tempinserttrees USING (dbhid);

# Log record for DBHattributes records.
INSERT INTO Log (logid,personnelid,changedtable,primarykey,changedcolumn,changedate,description,action,old,new)
SELECT null,1,"DBH","DBHID","ALL",curdate(),'reason for inserting these reocrds',"I","NA",
concat_ws("","CensusID=",d.CensusID,",TSMID=",d.TSMID,",DBHID=",d.DBHID,",DBHAttributeID=",d.DBHAttID) new
FROM DBHAttributes d JOIN tempinserttrees  USING (dbhid);






