# Example of how to insert a tree from a CTFS database
# Shameema Esufali revised June 2020

# This program calls the Utilities.sql script to parse DBHAttributes (codes)
#  separated by semi-colons.  The script is expected to be in the same folder
#  as this script. DO NOT ALTER THIS PROGRAM WITHOUT CONSULTING Author or CTFS
#  Systems support person. 
#   
# To insert trees
#  Create an excel sheet with the essential data.
#  The sheet should have 1 record at least
#  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 IGNORE 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.

-- **********************************************************************************************
-- 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 **********************************************************************  

# *******************************************************************************************************
# ***** LOAD IN THE DATA YOU WANT TO INSERT
# *******************************************************************************************************
# CHANGE TO APPROPRIATE VALUES FOR YOUR DATA.  PersonnelID is the primary key 
# from the Personnel table 
# for the person who is responsible for authenticating the change, 
SET @plotid=1, @personnelid=1;

-- First insert Trees, then Stems, DBH and DBHAttributes (Codes)

CREATE TABLE tempinserttrees (
censusno VARCHAR(5),
tag VARCHAR(15),
stemtag  VARCHAR(15),
stemnumber int,
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 '../datafiles/inserttrees.csv' 
 INTO TABLE tempinserttrees FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
 LINES TERMINATED BY '\n' IGNORE 1 LINES 
 (censusno,tag,stemtag,stemnumber,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 DBHAttid 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=@plotid, quadrat=lpad(quadrat,4,'0');
UPDATE tempinserttrees i, Census c SET i.censusid=c.censusid WHERE i.censusno=c.plotcensusnumber AND c.plotid=@plotid;
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 TRIM(leading '0' from i.tag)=TRIM(leading '0' from t.tag);

# Stems
INSERT INTO Stem (treeid,stemtag,stemnumber,quadratid,qx,qy)
 SELECT treeid,stemtag,stemnumber,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 TRIM(leading '0' from i.stemtag)=TRIM(leading '0' from 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
CREATE TABLE TempInsertCodes (codes varchar(25),DBHID int);
INSERT INTO TempInsertCodes (codes,DBHID) SELECT codes,dbhid FROM tempinserttrees WHERE (codes !=""  AND codes IS NOT NULL);

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.
# *******************************************************************************************************
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 comments in the SQL statements below
# Log record for Trees
TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) SELECT distinct TreeID,"ALL","reason for inserting these records" FROM tempinserttrees;
CALL logRecordSet(@personnelid,"Tree",@action) ; 

# Log record for Stems
TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) SELECT  StemID,"ALL","reason for inserting these records" FROM tempinserttrees WHERE stemid IS NOT NULL;
CALL logRecordSet(@personnelid,"Stem",@action); 

# Log record for DBH records
TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) SELECT  DBHID,"ALL","reason for inserting these records" FROM tempinserttrees WHERE DBHID IS NOT NULL;
CALL logRecordSet(@personnelid,"DBH",@action); 


# Log record for DBHattributes records.
TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) SELECT DBHAttID,"ALL","reason for inserting these records" FROM DBHAttributes  WHERE DBHID in (SELECT DBHID FROM TempInsertCodes);
;
CALL logRecordSet(@personnelid,"DBHAttributes",@action); 

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

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

# Run some statistics to see if it all worked as expected. 
SELECT count(*) FROM TempLogFeeder;
SELECT count(*) FROM TempInsertCodes;
SELECT @n2:=count(*) FROM Log;
SELECT "Added =",@n2-@n1;

# *******************************************************************************************************
# ***** FINALLY CLEAN UP THE DATABASE BY REMOVING ALL THE TEMP TABLES YOU CREATED
# *******************************************************************************************************

# DROP TABLE IF EXISTS TempInsertCodes;
# DROP TABLE IF EXISTS tempinserttrees;






