# Example of how to delete a tree from a CTFS database
# Shameema Esufali March 2014
#
# To delete trees
# Create an excel sheet with one column with a heading tag
# List all the tags you want to delete
# Save it as a csv file from excel
# Replace the path and name of this file in the LOAD DATA line below
#
# Note: You need to create a Log record for every change to the database. 
# When deleting records, you must first create the log record so you
# do not lose information. Then you may delete the record.
#
# *******************************************************************************************************
# ***** LOAD IN THE TAGS YOU WANT TO DELETE 
# *******************************************************************************************************
DROP TABLE IF EXISTS tempdeletetrees;
CREATE TABLE tempdeletetrees (
Tag char(15)
)
ENGINE=INNODB;

LOAD DATA LOCAL INFILE '/home/asiaplots/Documents/databasemanagement/sampledatabases/deletetrees.csv' INTO TABLE tempdeletetrees FIELDS OPTIONALLY ENCLOSED BY '"' terminated by ',' IGNORE 1 LINES;

# *******************************************************************************************************
# ***** FIND THE CORRESPONDING STEMS 
# *******************************************************************************************************

DROP TABLE IF EXISTS tempdeletestems;

CREATE TABLE tempdeletestems AS
SELECT d.tag,v.treeid,v.stemid
FROM tempdeletetrees d,ViewFullTable v
WHERE d.tag=v.tag group by d.tag,v.treeid,v.stemid;

# *******************************************************************************************************
# ***** FIND THE CORRESPONDING DBH RECORDS 
# *******************************************************************************************************

DROP TABLE IF EXISTS tempdeletedbh;
CREATE TABLE tempdeletedbh AS
SELECT d.tag,v.treeid,v.stemid,v.dbhid 
FROM tempdeletetrees d,ViewFullTable v
WHERE d.tag=v.tag;


# *******************************************************************************************************
# ***** CREATE LOG RECORDS FOR EACH TABLE YOU ARE DELETING FROM 
# # Note: You should be able to use this code without any changes.
# *******************************************************************************************************

# 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 deleting these reocrds',"D",
concat_ws("","TreeID=",d.TreeID,",Tag=",d.Tag,",SpeciesID=",d.SpeciesID,
      ",SubSpeciesID=",d.SubSpeciesID) old
,"NA"
FROM Tree d JOIN tempdeletetrees USING (Tag);

# 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 deleting these reocrds',"D",
concat_ws("","StemID=",d.StemID,",TreeID=",d.TreeID,",StemTag=",d.StemTag,
      ",StemDescription=",d.StemDescription,",QuadratID=",d.QuadratID,",StemNumber=",d.StemNumber,
      ",Moved=",d.Moved,",GX=",d.GX,",GY=",d.GY,",GZ=",d.GZ,",PX=",d.PX,",PY=",d.PY,",PZ=",d.PZ,
      ",QX=",d.QX,",QY=",d.QY,",QZ=",d.QZ) old
,"NA" 
FROM Stem d JOIN tempdeletestems s 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 deleting these records',"D",
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) old
,"NA"
FROM DBH d JOIN tempdeletedbh s 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 deleting these reocrds',"D",
concat_ws("","CensusID=",d.CensusID,",TSMID=",d.TSMID,",DBHID=",d.DBHID,",DBHAttributeID=",d.DBHAttID) old
,"NA" 
FROM DBHAttributes d JOIN tempdeletedbh s USING (dbhid);



# *******************************************************************************************************
# ***** NOW DELETE ALL RECORDS BEGINNING WITH THE ATTRIBUTES
# *******************************************************************************************************

DELETE FROM DBHAttributes WHERE dbhid IN (SELECT DBHID FROM tempdeletedbh);
DELETE FROM DBH WHERE dbhid IN (SELECT DBHID FROM tempdeletedbh);
DELETE FROM Stem WHERE stemid IN (SELECT stemid FROM tempdeletestems);
DELETE FROM Tree WHERE treeid IN (SELECT distinct treeid FROM tempdeletestems);

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

DROP TABLE tempdeletedbh;
DROP TABLE tempdeletestems;
DROP TABLE tempdeletetrees;




