## Using table PanamaTreeList in NCEASStylePanCR to work with species in the Tree Guide, setting up
# table PanamaTreeList to designate book species and Nefer's codes for the photo names

USE NCEASstyleCRPan;

# Reading in the table Rolando created. This is not to be repeated.
CREATE TEMPORARY TABLE BookList
(
 tmp1 VARCHAR(100),
 Fullname VARCHAR(100),
 photocode VARCHAR(100),
 photo VARCHAR(100),
 description VARCHAR(100),
 KEY(Fullname)
) type=MyISAM;

LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/Perez.Listaoriglibro.csv"
INTO TABLE BookList IGNORE 1 LINES;
UPDATE BookList SET fullname="Calliandra magdalenae" where fullname="Calliandra ???";
# ALTER TABLE PanamaTreeList ADD COLUMN Book INT DEFAULT 0;
UPDATE PanamaTreeList JOIN BookList USING(fullname) SET Book=1;

# Now create a table of those in the book with updated family
SELECT Family, TL.Genus, TL.Fullname, BL.photocode, BL.photo, BL.description,
       TL.Exotic, TL.IncompleteID, CRRecords.cnt AS CostaRicaRecords, PanRecords.cnt AS PanamaRecords
INTO OUTFILE "/var/lib/mysql/NCEASstyleCRPan/BookSpecies.txt"
FROM PanamaTreeList TL JOIN BookList BL ON BL.Fullname=TL.Fullname
LEFT JOIN
 (SELECT treeID, fullname, count(*) AS cnt FROM PanamaTreeList L JOIN BIENrecords B USING(fullname)
  WHERE Country="Panama" GROUP BY treeID, fullname) AS PanRecords ON TL.treeID=PanRecords.treeID
LEFT JOIN
 (SELECT treeID, fullname, count(*) AS cnt FROM PanamaTreeList L JOIN BIENrecords B USING(fullname)
  WHERE Country="Costa Rica" GROUP BY treeID, fullname) AS CRRecords ON TL.treeID=CRRecords.treeID
LEFT JOIN Genus ON TL.genus=Genus.genus
LEFT JOIN Family ON Family.familyID=Genus.familyID
# sudo mv /var/lib/mysql/NCEASstyleCRPan/BookSpecies.txt /home/condit/manuscripts/TreeGuide/


# The following are Panama plot names which have been written variously. The corrected version is what
# we have in db_bci. See also many changes in BIENMerge.sql
UPDATE PanamaTreeList SET fullname="Swartzia aff.sumorum" WHERE fullname="Swartzia sumorum.aff";
DELETE FROM PanamaTreeList WHERE fullname="Swartzia 'raquis_alado'";
UPDATE PanamaTreeList SET fullname="Beilschmiedia aff.tilaranensis" WHERE fullname="Beilschmiedia tilaranensis.aff";
UPDATE PanamaTreeList SET fullname="Nectandra fuzzy" WHERE fullname="Nectandra 'fuzzy'";
UPDATE PanamaTreeList SET fullname="Pleurothyrium aff.hexaglandulosum" WHERE fullname="Pleurothyrium hexaglandulosum.aff";
UPDATE PanamaTreeList SET fullname="Eschweilera aff.angustifolia" WHERE fullname="Eschweilera angustifolia.aff";
UPDATE PanamaTreeList SET fullname="Drypetes aff.amazonica" WHERE fullname="Drypetes amazonica.aff";
UPDATE PanamaTreeList SET fullname="Chromolucuma aff.rubriflora" WHERE fullname="Chromolucuma rubriflora.aff";

UPDATE PanamaTreeList SET Book=0 WHERE fullname="Malouetia guatemalensis";
UPDATE PanamaTreeList SET Book=1 WHERE fullname="Malouetia isthmica";
UPDATE PanamaTreeList SET Exotic=1 WHERE fullname="Carica papaya";
UPDATE PanamaTreeList SET Exotic=1 WHERE fullname="Ateleia gummifera";

## Have to get photo and code information (19 Sept 2009), based on PerezListaLibro. This was not included
# when first importing BookList above
CREATE TEMPORARY TABLE photocode
(
 latin VARCHAR(100),
 code VARCHAR(10),
 photo CHAR(2)
) TYPE=MyISAM;

LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/photocode.csv"
INTO TABLE photocode IGNORE 1 LINES;

## Loading file lists from Nefer's DVDs, 19 Sept 2009
CREATE TEMPORARY TABLE NeferDVD
(
 a VARCHAR(25),
 b VARCHAR(25),
 c VARCHAR(25),
 d VARCHAR(25),
 e VARCHAR(25),
 createdate DATE,
 f VARCHAR(25),
 filename VARCHAR(25)
) TYPE=MyISAM;

/*
LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/NeferCD1.txt" INTO TABLE NeferDVD FIELDS TERMINATED BY ' ' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/NeferCD2.txt" INTO TABLE NeferDVD FIELDS TERMINATED BY ' ' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/NeferCD3.txt" INTO TABLE NeferDVD FIELDS TERMINATED BY ' ' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/NeferCD4.txt" INTO TABLE NeferDVD FIELDS TERMINATED BY ' ' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/NeferCD5.txt" INTO TABLE NeferDVD FIELDS TERMINATED BY ' ' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/NeferCD6.txt" INTO TABLE NeferDVD FIELDS TERMINATED BY ' ' IGNORE 1 LINES;
*/

SELECT B.Fullname,N.filename FROM bookspecies B LEFT JOIN NeferDVD N ON N.filename=B.Photoname 
WHERE B.Photoname<>'' AND createdate IS NULL;
SELECT B.Fullname,N.filename FROM NeferDVD N LEFT JOIN bookspecies B ON N.filename=B.Photoname 
WHERE Genus IS NULL;

ALTER TABLE PanamaTreeList ADD COLUMN mnemonic VARCHAR(6), ADD COLUMN photo SMALLINT(6);
UPDATE PanamaTreeList PTL JOIN photocode P ON P.Latin=PTL.fullname SET PTL.mnemonic=P.code;
UPDATE PanamaTreeList PTL JOIN photocode P ON P.Latin=PTL.fullname SET PTL.photo=1 WHERE P.photo='x';
UPDATE PanamaTreeList SET photo=0 WHERE photo IS NULL;
UPDATE PanamaTreeList SET mnemonic="" WHERE mnemonic IS NULL;
UPDATE PanamaTreeList SET photo=0 WHERE fullname="Tectona grandis";
UPDATE PanamaTreeList SET mnemonic="protte2" WHERE fullname="Protium tenuifolium";
UPDATE PanamaTreeList SET mnemonic="pogosp" WHERE fullname="Pogonopus exsertus";
UPDATE PanamaTreeList SET mnemonic="sap1sa" WHERE fullname="Sapindus saponaria";
UPDATE PanamaTreeList SET mnemonic="cupasy" WHERE fullname="Cupania seemannii";
UPDATE PanamaTreeList SET mnemonic="neeam" WHERE fullname="Neea amplifolia";
# But after this, photo for Ocotea tonduzii was removed, as wrong species? And it is no longer in the book
UPDATE PanamaTreeList SET book=1, photo=1, mnemonic="ocotto" WHERE fullname="Ocotea tonduzii";

UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Acacia melanoceras";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Schizolobium parahyba";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Unonopsis sp. nov.";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Inga mucuna";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Inga punctata";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Mimosa tenuiflora";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Conostegia speciosa";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Miconia impetiolaris";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Psidium guajava";
UPDATE PanamaTreeList SET book=1, photo=1 WHERE fullname="Cecropia insignis";

UPDATE PanamaTreeList SET book=0, photo=0 WHERE fullname="Flemingia strobilifera";
UPDATE PanamaTreeList SET book=0, photo=0 WHERE fullname="Cecropia heterochroma";
UPDATE PanamaTreeList SET book=0, photo=0 WHERE fullname="Leucaena leucocephala";


CREATE TEMPORARY TABLE photo SELECT * FROM PanamaTreeList WHERE mnemonic IS NOT NULL;
SELECT * FROM photocode PC LEFT JOIN photo P ON P.Fullname=PC.latin WHERE P.Fullname IS NULL;

# Errors detected by Barbara Clausen while copy-editing. 
# The first 3 do not appear in the text, but I listed as described without photo. They probably once had descriptions
# that I since removed. The last 4 are pairs where I had the wrong species marked as in the book. Neither has photo, and Syzygium
# has no map, but I have to redo the map for G. cuneata
UPDATE PanamaTreeList SET book=0 WHERE fullname="Myrospermum frutescens";
UPDATE PanamaTreeList SET book=0 WHERE fullname="Hirtella latifolia";
UPDATE PanamaTreeList SET book=0 WHERE fullname="Clarisia biflora";
UPDATE PanamaTreeList SET book=0 WHERE fullname="Geonoma deversa";
UPDATE PanamaTreeList SET book=1 WHERE fullname="Geonoma cuneata";
UPDATE PanamaTreeList SET book=0 WHERE fullname="Syzygium jambos";
UPDATE PanamaTreeList SET book=1 WHERE fullname="Syzygium malaccense";
UPDATE PanamaTreeList SET book=1 WHERE fullname="Miconia oinochrophylla";
UPDATE PanamaTreeList SET book=1 WHERE fullname="Peltophorum pterocarpum";
UPDATE PanamaTreeList SET Authority="(Kunth) Cortés" WHERE fullname="Elaeis oleifera";
UPDATE PanamaTreeList SET Authority="(Kunth) Hammel" WHERE fullname="Garcinia madruno";
UPDATE PanamaTreeList SET Authority="Erkens & Maas" WHERE fullname="Guatteria aberrans";

USE NCEASstyleCRPan;

# Now create a table of trees in the book, or in all Panama, with updated family
CREATE TEMPORARY TABLE treespecieslist
SELECT Family, TL.Genus, TL.Fullname, TL.Authority, TL.GrowthForm, TL.Exotic, TL.IncompleteID, TL.Book, Mnemonic, Photo,
       CRRecords.cnt AS CostaRicaRecords, PanRecords.cnt AS PanamaRecords
 FROM PanamaTreeList TL LEFT JOIN
 (SELECT treeID, fullname, count(*) AS cnt FROM PanamaTreeList L JOIN BIENrecords B USING(fullname)
  WHERE Country="Panama" GROUP BY treeID, fullname) AS PanRecords ON TL.treeID=PanRecords.treeID
 LEFT JOIN
 (SELECT treeID, fullname, count(*) AS cnt FROM PanamaTreeList L JOIN BIENrecords B USING(fullname)
  WHERE Country="Costa Rica" GROUP BY treeID, fullname) AS CRRecords ON TL.treeID=CRRecords.treeID
 LEFT JOIN Genus ON TL.genus=Genus.genus
 LEFT JOIN Family ON Family.familyID=Genus.familyID
ORDER BY Family, TL.Fullname;
ALTER TABLE treespecieslist ADD COLUMN Photoname VARCHAR(12) AFTER Fullname, ADD COLUMN Mapname VARCHAR(175) AFTER Photoname;
UPDATE treespecieslist SET CostaRicaRecords=0 WHERE CostaRicaRecords IS NULL;
UPDATE treespecieslist SET PanamaRecords=0 WHERE PanamaRecords IS NULL;
UPDATE treespecieslist SET Photoname=CONCAT(mnemonic,".tif") WHERE Photo=1;
UPDATE treespecieslist SET Mapname=CONCAT(Family,"/",fullname,".tif") WHERE Exotic=0;
UPDATE treespecieslist SET Photoname="" WHERE Photo=0;
UPDATE treespecieslist SET Mapname="" WHERE Exotic=1;
# ALTER TABLE treespecieslist DROP COLUMN Mnemonic, DROP COLUMN Photo, DROP COLUMN IncompleteID, DROP COLUMN Genus, DROP COLUMN Exotic;

SELECT Family, COUNT(*) AS NativeSp FROM treespecieslist WHERE Exotic=0 AND IncompleteID=0 GROUP BY Family ORDER BY NativeSp DESC;

## For book species:
/*
(SELECT "Family", "Fullname", "Photo (DVD)", "Map (DVD)")
UNION
(SELECT Family, Fullname, Photoname, Mapname INTO OUTFILE "/var/lib/mysql/NCEASstyleCRPan/treespecieslist.txt" 
 FROM treespecieslist WHERE Book=1);
# sudo mv /var/lib/mysql/NCEASstyleCRPan/treespecieslist.txt /home/condit/manuscripts/TreeGuide/BookSpeciesJan10.txt
*/

## For all Panama species
(SELECT "Family","Genus","Latin","Authority","GrowthForm","Exotic","IncompleteID","Book","CRRecords","PanRecords")
UNION
(SELECT Family,Genus,Fullname,Authority,GrowthForm,Exotic,IncompleteID,Book,CostaRicaRecords,PanamaRecords 
 INTO OUTFILE "/var/lib/mysql/NCEASstyleCRPan/treespecieslist.txt" FROM treespecieslist);
# sudo mv /var/lib/mysql/NCEASstyleCRPan/treespecieslist.txt /home/condit/data/treedatabase/PanamaTreeSpecies.txt

## For a list of photo names:
(SELECT "Photo", "Family", "Latin")
UNION
(SELECT CONCAT(mnemonic,".tif") AS Photo, Family, Fullname
 INTO OUTFILE "/var/lib/mysql/NCEASstyleCRPan/PhotoList.txt"
  FROM PanamaTreeList TL JOIN Genus ON TL.genus=Genus.genus
   JOIN Family ON Family.familyID=Genus.familyID
  WHERE TL.photo=1 ORDER BY Family, Fullname);
# sudo mv /var/lib/mysql/NCEASstyleCRPan/PhotoList.txt /home/condit/manuscripts/TreeGuide


# Now using Suzanne's neotropicaltree database to find synonyms for all the book species
# Suzanne had already copied table PanamaTreeList into neotropicaltree, but I here repeat
# because of many recent updates, with codes and photos noted (20 Sept 2009)


# The maps that Dimitri created from my file are in separate files (in princetonmaps), and I read
# the file list into a text file.

CREATE TEMPORARY TABLE PrincetonMaps
(
 DimitriName VARCHAR(60),
 Latin VARCHAR(60)
) ENGINE MyISAM;
LOAD DATA LOCAL INFILE "/home/condit/manuscripts/TreeGuide/princetonmaps/princetonmaps.csv" 
INTO TABLE PrincetonMaps IGNORE 1 LINES;

# Maps I sent Dimitri which are not actually in the book:
SELECT TreeID, DimitriName, Latin, Fullname, book FROM PrincetonMaps PM LEFT JOIN PanamaTreeList PTL ON PM.Latin=PTL.FullName 
WHERE treeID IS NULL OR book=0;
SELECT TreeID, DimitriName, Latin, Fullname, book FROM PrincetonMaps PM RIGHT JOIN PanamaTreeList PTL ON PM.Latin=PTL.FullName 
WHERE DimitriName IS NULL AND book=1;

# The following table became the final list of 494 species, in Revised Species Included.ods (20 Jan 2010)
CREATE TEMPORARY TABLE BookFinal
SELECT Fullname, Family, DimitriName AS Map, Photo FROM PrincetonMaps PM RIGHT JOIN
(SELECT Fullname, Family, Photo FROM treespecieslist WHERE book=1) AS Bk
ON PM.Latin=Bk.Fullname
# WHERE DimitriName IS NULL
ORDER BY Family, Fullname, DimitriName;
SELECT * INTO OUTFILE "/var/lib/mysql/tempfiles/BookFinalJan10.txt" FROM BookFinal;
sudo mv /var/lib/mysql/tempfiles/BookFinalJan10.txt /home/condit/manuscripts/TreeGuide/EditedVersion
