/*written by Michael Matschiner, 06/16/2009*/
/*send feedback to michael.matschiner-at-unibas.ch*/

/*This code is intended for import of data into the 'specimen' table of the MySQL
database 'gene_sample' running on evo-lutra.zoo.unibas.ch. Data is imported from
the file addToSpecimen.csv, which needs to be correctly formatted for successful
import.
A template Excel file with individual sheets for every database table can be found at
www.evolution.unibas.ch/salzburger/protocols.htm. You'll need to save the respective
sheet in csv format and change all filenames specified below accordingly.*/

LOAD DATA LOCAL INFILE "/Users/michaelmatschiner/Documents/addToSpecimen.csv"
INTO TABLE specimen FIELDS TERMINATED BY "," ENCLOSED BY "\""
LINES TERMINATED BY "\r"
IGNORE 5 LINES
(name, collector, external_owner, external_name, alternative_names, sex, @date_born, @date_died, standard_length, terminal_length, weight, n_lower_phar_teeth, n_upper_oral_teeth, n_lower_oral_teeth, organism_certain, @organism.latin_name, @field_trip.name, @station.name, @dummy1, @dummy2)
SET organism_id = (SELECT id FROM organism WHERE latin_name = @organism.latin_name),
    station_id = (SELECT station.id FROM station INNER JOIN field_trip ON station.field_trip_id = field_trip.id
                  WHERE station.name = @station.name
                  AND field_trip.name = @field_trip.name
                  /*AND station.date_visited = @date_died*/),
    date_born = str_to_date(@date_born,'%Y-%m-%d'),
    date_died = str_to_date(@date_died,'%Y-%m-%d');

LOAD DATA LOCAL INFILE "/Users/michaelmatschiner/Documents/addToSpecimen.csv"
INTO TABLE specimen2specimen FIELDS TERMINATED BY "," ENCLOSED BY "\""
LINES TERMINATED BY "\r"
IGNORE 5 LINES
(@name, @d3, @d4, @d5, @d6, @d7, @d8, @d9, @d10, @d11, @d12, @d13, @d14, @d15, @d16, @d17, @d18, ,@d19, @parent_name, @d20)
SET parent_id = (SELECT id FROM specimen WHERE name = @parent_name),
    offspring_id =(SELECT id FROM specimen WHERE name = @name)

LOAD DATA LOCAL INFILE "/Users/michaelmatschiner/Documents/addToSpecimen.csv"
INTO TABLE specimen2specimen FIELDS TERMINATED BY "," ENCLOSED BY "\""
LINES TERMINATED BY "\r"
IGNORE 5 LINES
(@name, @d21, @d22, @d23, @d24, @d25, @d26, @d27, @d28, @d29, @d30, @d31, @d32, @d33, @d34, @d35, @d36, @d37, @d38, @parent_name)
SET parent_id = (SELECT id FROM specimen WHERE name = @parent_name),
    offspring_id =(SELECT id FROM specimen WHERE name = @name)