#!/bin/bash

#Read flat representation of data and export into file (uploadspectemp.data). Piped sed statement converts NULL text to NULL character value; not need for all downloads
mysql --user=readonlyuser --password=readonlypwd --host=targetdomain.edu -e "SELECT 2 AS collid, guid, s.AccessionNumber, t.scientific_name, t.family, CONCAT_WS(' ', t.unit_ind1, t.unit_name1) AS genus, CONCAT_WS(' ', t.unit_ind2, t.unit_name2) AS epithet, IFNULL(t.unit_ind4, t.unit_ind3) AS infrarank, IFNULL(t.unit_name4, t.unit_name3) AS infraepithet, t.author, d.determiner, d.det_date, d.cf, s.country, s.state_province, s.county_parish, s.locality, s.elevation_m, s.elev_max_m, s.lat_decimal, s.long_decimal, s.datum, coord_err, s.utm_northing, s.utm_easting, s.utm_zoning, s.date_collected, CONCAT_WS('-',day_collected, month_collected, year_collected), s.first_collector, s.other_collectors, s.collnumber_full, s.specimen_notes, s.plant_description, ty.type_status, t.security AS LocalitySecurity, s.dbsn, s.habitat, s.assoc_species, s.phenology, s.cultivated FROM ((DbSchemaName.tbl_taxa t INNER JOIN DbSchemaName.tbl_determinations d ON t.taxa_id = d.taxa_id) INNER JOIN DbSchemaName.tbl_specimens s ON d.dbsn = s.dbsn) LEFT JOIN DbSchemaName.tbl_types ty ON s.dbsn = ty.dbsn WHERE d.is_primary=1 AND (DATE_SUB(CURDATE(), INTERVAL 7 DAY) < s.date_entered OR DATE_SUB(CURDATE(), INTERVAL 7 DAY) < s.datelastmodified);" | sed "s/\tNULL/\t\\\N/g" > uploadspectemp.data

LINECNT=`cat uploadspectemp.data | wc -l`

#Import into Symbiota database. Source column positions above must match target column positions below. 
mysqlimport --replace --force --compress --local --ignore-lines=1 --user=SymbUserName --password=SymbPwd --host=localhost --columns=collid,occurrenceId,catalogNumber,SciName,Family,Genus,SpecificEpithet,taxonRank,infraspecificEpithet,scientificNameAuthorship,identifiedBy,dateIdentified,identificationQualifier,country,stateProvince,county,locality,minimumElevationInMeters,maximumElevationInMeters,decimalLatitude,decimalLongitude,geodeticDatum,coordinateUncertaintyInMeters,utmNorthing,utmEasting,utmZoning,eventDate,verbatimEventDate,recordedBy,associatedCollectors,recordNumber,occurrenceRemarks,attributes,typeStatus,localitySecurity,dbpk,habitat,associatedTaxa,reproductiveCondition,cultivationStatus symbiotaseinet uploadspectemp.data

#Delete temp text file 
rm -f uploadspectemp.data

echo "Transfer successful: $LINECNT records transferred"