# Example of how to update a DBH Measurement on an existing Stem in  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 update  DBH values for existing Stems
#  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 current_yoursite; 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 UPDATE
# *******************************************************************************************************
# 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, action is U for update. 
SET @plotid=1, @personnelid=1, @action="U";
# Begin program. 

SELECT "Importing data";
CREATE TABLE tempupdatedbh (
 censusno VARCHAR(5),
 tag VARCHAR(15),
 stemtag  VARCHAR(15),
 dbh decimal (10,2),
 newdbh VARCHAR(15),
 codes VARCHAR(25),
 newcodes VARCHAR(25),
 hom VARCHAR(15),
 exactdate VARCHAR(25),
 comments VARCHAR(128)
)
ENGINE=INNODB;

LOAD DATA LOCAL INFILE '../datafiles/updatedbh.csv' 
 INTO TABLE tempupdatedbh FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
 LINES TERMINATED BY '\n' IGNORE 1 LINES ;

ALTER TABLE tempupdatedbh ADD COLUMN Treeid int, ADD COLUMN Stemid int, ADD COLUMN DBHID int;
ALTER TABLE tempupdatedbh ADD COLUMN DBHAttributeid int, ADD COLUMN censusid int;

# Collect all the IDs required for updating the data. 
# 
UPDATE tempupdatedbh a, Census b SET a.censusid=b.censusid WHERE a.censusno=b.plotcensusnumber AND b.plotid=@plotid;
UPDATE tempupdatedbh a, Tree b SET a.treeid=b.treeid WHERE a.tag=b.tag;
UPDATE tempupdatedbh a, Stem b SET a.stemid=b.Stemid WHERE a.treeid=b.treeid AND a.stemtag=b.stemtag;
UPDATE tempupdatedbh a, DBH b SET a.dbhid=b.dbhid WHERE a.stemid=b.stemid AND a.censusid=b.censusid; 


# *******************************************************************************************************
# ***** UPDATE RECORDS DBH and INSERT DBHAttributes. CREATE LOG RECORDS FOR EACH TABLE YOU ARE INSERTING INTO
# *******************************************************************************************************
#  For updates and deletes, log the update before updating the table. 
#  For inserts log the insert after the data is in the table. 

-- Update DBH
SET @plotid=1, @personnelid=1, @action="U";
# Log record for DBH records
TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) SELECT  DBHID,"ALL",comments FROM tempupdatedbh;
CALL logRecordSet(@personnelid,"DBH",@action); 

# Update table DBH
UPDATE DBH a,tempupdatedbh b
 SET a.dbh=b.newdbh WHERE a.dbhid=b.dbhid;
 

# 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 newcodes,dbhid FROM tempupdatedbh 
 WHERE (newcodes !=""  AND newcodes IS NOT NULL);
CALL insertdbhattributes();


# Log record for DBHattributes records.
SET @action="I";

TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) SELECT DBHAttID,"ALL","" FROM DBHAttributes  WHERE DBHID in (SELECT DBHID FROM tempupdatedbh);

CALL logRecordSet(@personnelid,"DBHAttributes",@action); 


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

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

# DROP TABLE IF EXISTS TempInsertCodes;
# DROP TABLE IF EXISTS tempupdatedbh;



