public int GetSpeciesID(String CommonName, String Genus, String Species, String Phylum, String Class, String Order, String Family, String FBcode)
{
String speciesName = (Genus.Trim() + " " + Species.Trim()).Trim();
int wormsid = 0;
int speciesid = GetExistingSpeciesID(CommonName, Genus, Species, Phylum, Class, Order, Family, FBcode);
if (speciesid > 0)
{
return(speciesid);
}
GenericList gl = new GenericList(Context);
String query = String.Format("SELECT * FROM TblSpeciesLookup WHERE fScienceNameAlts LIKE '%{0}%' OR fCommonNameAlts LIKE '%{1}%'", speciesName.Replace("'", "''"), CommonName.Replace("'", "''"));
using (SqlConnection con = new SqlConnection(DataSources.dbConSpecies))
{
con.Open();
using (SqlCommand command = new SqlCommand(query, con))
{
using (SqlDataReader set = command.ExecuteReader())
{
if (set.Read())
{
wormsid = (int)set["fWoRMID"];
speciesid = GetSpeciesID(wormsid);
if (speciesid > 0)
{
return(speciesid);
}
// add new species
int nTaxonID = int.Parse(gl.getTaxonID(wormsid, CommonName));
SuperSQL sql = new SuperSQL(con, null, Response);
sql.add("fTaxonomyID", nTaxonID);
sql.add("fLSID", wormsid);
sql.add("fWoRMID", wormsid);
sql.add("fSpeciesName", set["fSpeciesLookupName"].ToString());
sql.add("fCommonName", set["fCommonName"].ToString());
sql.add("fFeatures", set["fFeatures"].ToString());
sql.add("fColour", set["fColour"].ToString());
sql.add("fSize", set["fSize"].ToString());
sql.add("fDistribution", set["fDistribution"].ToString());
sql.add("fSimilar", set["fSimilar"].ToString());
sql.add("fReferences", set["fReferences"].ToString());
sql.add("fNotes", set["fNotes"].ToString());
sql.add("fFAFFCode", set["fFAFFCode"].ToString());
sql.add("fHabitat", set["fHabitat"].ToString());
sql.add("fFishBoard", FBcode);
sql.add("fGenus", Genus);
sql.add("fPhylum", Phylum);
sql.add("fClass", Class);
sql.add("fOrder", Order);
sql.add("fFamily", Family);
sql.add("fSpecies", Species);
set.Close();
sql.insert("TblSpecies");
return(GetSpeciesID(wormsid));
}
}
}
// check if we can resolve against worms
wormsid = GetWormsID(speciesName, CommonName);
Response.Write("wormslookup: " + wormsid + "<br>");
if (wormsid > 0)
{
// add worm to species lookup
SuperSQL sql = new SuperSQL(con, null, Response);
sql.add("fWoRMID", wormsid);
sql.add("fSpeciesLookupName", speciesName);
sql.add("fCommonName", CommonName);
sql.add("fScienceNameAlts", speciesName);
sql.add("fCommonNameAlts", CommonName);
sql.insert("TblSpeciesLookup");
return(GetSpeciesID(CommonName, Genus, Species, Phylum, Class, Order, Family, FBcode));
}
// add the species without worms id
SuperSQL sq2 = new SuperSQL(con, null, Response);
sq2.add("fLSID", 0);
sq2.add("fWoRMID", 0);
sq2.add("fSpeciesName", speciesName);
sq2.add("fCommonName", CommonName);
sq2.add("fFishBoard", FBcode);
sq2.add("fGenus", Genus);
sq2.add("fPhylum", Phylum);
sq2.add("fClass", Class);
sq2.add("fOrder", Order);
sq2.add("fFamily", Family);
sq2.add("fSpecies", Species);
return(sq2.insert("TblSpecies", "fSpeciesID"));
}
}
public void ImportSpreadsheet(String path)
{
const int fSpeciesID = 1;
const int fTaxonomyID = 2;
const int fLSID = 3;
const int fWoRMID = 4;
const int fSpeciesName = 5;
const int fCommonName = 6;
const int fFeatures = 7;
const int fColour = 8;
const int fSize = 9;
const int fDistribution = 10;
const int fHabitat = 11;
const int fSimilar = 12;
const int fReferences = 13;
const int fNotes = 14;
const int fOrangeToRed = 15;
Dictionary <int, SpeciesLookup> speciesLookups = new Dictionary <int, SpeciesLookup>();
Dictionary <int, Row> rows = ReadSpreadsheet(path);
foreach (int r in rows.Keys)
{
Row row = rows[r];
if (r > 1)
{
int SpeciesID = int.Parse(row.cells[fSpeciesID].Trim());
int TaxonomyID = int.Parse(row.cells[fTaxonomyID].Trim());
int LSID = int.Parse(row.cells[fLSID].Trim());
int WoRMID = int.Parse(row.cells[fWoRMID].Trim());
String SpeciesName = row.cells[fSpeciesName];
String CommonName = row.cells[fCommonName];
String Features = row.cells[fFeatures];
String Colour = row.cells[fColour];
String Size = row.cells[fSize];
String Distribution = row.cells[fDistribution];
String Habitat = row.cells[fHabitat];
String Similar = row.cells[fSimilar];
String References = row.cells[fReferences];
String Notes = row.cells[fNotes];
String OrangeToRed = row.cells[fOrangeToRed];
String query = String.Format("SELECT * FROM TblSpeciesLookup WHERE fWoRMID = {0}", WoRMID);
using (SqlConnection connection = new SqlConnection(DataSources.dbConSpecies))
{
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader set = command.ExecuteReader())
{
SuperSQL sql = new SuperSQL(connection, null, Response);
if (set.Read())
{
Response.Write("mod<br>");
if (IsValidDescriber(CommonName))
{
String[] alts = set["fCommonNameAlts"].ToString().ToLower().Split('|');
List <String> alternatives = alts.ToList();
if (alternatives.IndexOf(CommonName) == -1)
{
String altNames = set["fCommonNameAlts"].ToString();
if (altNames != "")
{
altNames += "|";
}
altNames += CommonName;
sql.add("fCommonNameAlts", altNames);
}
}
if (IsValidDescriber(SpeciesName))
{
String[] alts = set["fScienceNameAlts"].ToString().ToLower().Split('|');
List <String> alternatives = alts.ToList();
if (alternatives.IndexOf(SpeciesName) == -1)
{
String altNames = set["fScienceNameAlts"].ToString();
if (altNames != "")
{
altNames += "|";
}
altNames += SpeciesName;
sql.add("fScienceNameAlts", altNames);
}
}
if (IsValidDescriber(Features))
{
sql.add("fFeatures", Features);
}
if (IsValidDescriber(Colour))
{
sql.add("fColour", Colour);
}
if (IsValidDescriber(Size))
{
sql.add("fSize", Size);
}
if (IsValidDescriber(Distribution))
{
sql.add("fDistribution", Distribution);
}
if (IsValidDescriber(Habitat))
{
sql.add("fHabitat", Habitat);
}
if (IsValidDescriber(Similar))
{
sql.add("fSimilar", Similar);
}
if (IsValidDescriber(References))
{
sql.add("fReferences", References);
}
if (IsValidDescriber(Notes))
{
sql.add("fNotes", Notes);
}
if (IsValidDescriber(OrangeToRed))
{
sql.add("fFAFFCode", OrangeToRed);
}
set.Close();
sql.modify("TblSpeciesLookup", "fWoRMID = " + WoRMID);
}
else
{
Response.Write("add<br>");
set.Close();
sql.add("fWoRMID", WoRMID);
sql.add("fSpeciesLookupName", SpeciesName);
sql.add("fCommonName", CommonName);
sql.add("fScienceNameAlts", SpeciesName);
sql.add("fCommonNameAlts", CommonName);
sql.add("fFeatures", FormatDescriber(Features));
sql.add("fColour", FormatDescriber(Colour));
sql.add("fSize", FormatDescriber(Size));
sql.add("fDistribution", FormatDescriber(Distribution));
sql.add("fSimilar", FormatDescriber(Similar));
sql.add("fReferences", FormatDescriber(References));
sql.add("fNotes", FormatDescriber(Notes));
sql.add("fFAFFCode", FormatDescriber(OrangeToRed));
sql.insert("TblSpeciesLookup");
}
}
}
}
}
}
}