public void addEntry(TrackingEntry entry)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandText = "INSERT INTO tracking_entry VALUES (NULL, @taggerID, @type, @isTagged,"
+ " @taggerName, @entryDate, @tagNumber, @species, @city, @state, @country,"
+ " @longitude, @latitude, @temperature, @precipitation, @windSpeed,"
+ " @windDirection);";
cmd.Prepare();
cmd.Parameters.AddWithValue("@taggerID", entry.taggerID);
cmd.Parameters.AddWithValue("@type", entry.type);
cmd.Parameters.AddWithValue("@isTagged", entry.isTagged);
cmd.Parameters.AddWithValue("@taggerName", entry.taggerName);
cmd.Parameters.AddWithValue("@entryDate", entry.entryDate);
cmd.Parameters.AddWithValue("@tagNumber", entry.tagNumber);
cmd.Parameters.AddWithValue("@species", entry.species);
cmd.Parameters.AddWithValue("@city", entry.city);
cmd.Parameters.AddWithValue("@state", entry.state);
cmd.Parameters.AddWithValue("@country", entry.country);
cmd.Parameters.AddWithValue("@longitude", entry.longitude);
cmd.Parameters.AddWithValue("@latitude", entry.latitude);
cmd.Parameters.AddWithValue("@temperature", entry.temperature);
cmd.Parameters.AddWithValue("@precipitation", entry.precipitation);
cmd.Parameters.AddWithValue("@windSpeed", entry.windSpeed);
cmd.Parameters.AddWithValue("@windDirection", entry.windDirection);
cmd.ExecuteNonQuery();
}
public TrackingEntry createTrackingEntry(int taggerID, int entryID, char type,
string taggerName, string entryDate, int tagNumber, string species,
string city, string state, string country, string longitude, string latitude,
string temperature, string precipitation, string windSpeed, string windDirection)
{
TrackingEntry entry = new TrackingEntry();
entry.taggerID = taggerID;
entry.entryID = entryID;
entry.type = type;
entry.taggerName = taggerName;
entry.entryDate = entryDate;
entry.tagNumber = tagNumber;
entry.species = species;
entry.city = city;
entry.state = state;
entry.country = country;
entry.longitude = longitude;
entry.latitude = latitude;
entry.temperature = temperature;
entry.precipitation = precipitation;
entry.windSpeed = windSpeed;
entry.windDirection = windDirection;
return entry;
}
// Pre-condition - all necessary fields in entry have valid information
public void addTrackingEntry(TrackingEntry entry)
{
updateButterfly(entry);
updateLocation(entry);
updateDate(entry);
query.addEntry(entry);
}
public bool unitTestAddEntry(TrackingEntry entry)
{
try
{
query.addEntry(entry);
}
catch(MySqlException ex)
{
Console.WriteLine(ex.Message);
return false;
}
return true;
}
private void onSubmit_Click(object sender, EventArgs e)
{
//Initialize variables
TrackingEntry newEntry = new TrackingEntry();
TrackingProcessing newProcess = new TrackingProcessing(connection);
//Parse and validate input
if (!ParseInput(ref newEntry))
{
//Show error message
MessageBox.Show("Could not add entry to the repository!\nPlease check all fields for errors.", "Error Message:", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
//Add entry to repository
newProcess.addTrackingEntry(newEntry);
//Show success message
MessageBox.Show("Entry successfully added to the repository!", "Entry Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private TrackingEntry readEntry(MySqlDataReader reader)
{
TrackingEntry currentEntry = new TrackingEntry();
currentEntry.entryID = reader.GetInt32(0);
currentEntry.taggerID = reader.GetInt32(1);
currentEntry.type = reader.GetChar(2);
currentEntry.isTagged = reader.GetBoolean(3);
currentEntry.taggerName = reader.GetString(4);
currentEntry.entryDate = ((DateTime)reader.GetMySqlDateTime(5)).ToString("yyyy-MM-dd HH:mm:ss");
if (reader.IsDBNull(6))
currentEntry.tagNumber = -1;
else
currentEntry.tagNumber = reader.GetInt32(6);
currentEntry.species = reader.GetString(7);
if (reader.IsDBNull(8))
currentEntry.city = "";
else
currentEntry.city = reader.GetString(8);
if (reader.IsDBNull(9))
currentEntry.state = "";
else
currentEntry.state = reader.GetString(9);
if (reader.IsDBNull(10))
currentEntry.country = "";
else
currentEntry.country = reader.GetString(10);
if (reader.IsDBNull(11))
currentEntry.longitude = "";
else
currentEntry.longitude = reader.GetString(11);
if (reader.IsDBNull(12))
currentEntry.latitude = "";
else
currentEntry.latitude = reader.GetString(12);
if (reader.IsDBNull(13))
currentEntry.temperature = "";
else
currentEntry.temperature = reader.GetString(13);
if (reader.IsDBNull(14))
currentEntry.precipitation = "";
else
currentEntry.precipitation = reader.GetString(14);
if (reader.IsDBNull(15))
currentEntry.windSpeed = "";
else
currentEntry.windSpeed = reader.GetString(15);
if (reader.IsDBNull(16))
currentEntry.windDirection = "";
else
currentEntry.windDirection = reader.GetString(16);
return currentEntry;
}
public TrackingEntry getEntry(int entryID)
{
TrackingEntry entry = new TrackingEntry();
MySqlCommand cmd = new MySqlCommand();
MySqlDataReader reader;
cmd.Connection = connection;
cmd.CommandText = "SELECT * FROM tracking_entry WHERE entry_id = @id";
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", entryID);
reader = cmd.ExecuteReader();
if (reader.Read())
{
entry = readEntry(reader);
reader.Close();
return entry;
}
else
{
reader.Close();
return null;
}
}
public bool unitTestGetEntry(int entryID)
{
TrackingEntry entry = new TrackingEntry();
entry = query.getEntry(entryID);
if (entry != null)
{
Console.WriteLine(" Entry ID: " + entry.entryID + " Type: " + entry.type);
Console.Write(" Tagger ID: " + entry.taggerID + " Tagger: " + entry.taggerName);
Console.WriteLine(" Species: " + entry.species + " Date: " + entry.entryDate);
return true;
}
else
return false;
}
private bool ParseInput(ref TrackingEntry newEntry)
{
//Initialize variables
int taggerID;
string errorCode = "";
string formattedDate = "";
//Check tagger ID
Int32.TryParse(tfTaggerID.Text, out taggerID);
newEntry.taggerName = DataValidation.validateTaggerID(taggerID, connection);
if (tfTaggerID.Text == (""))
{
errorCode += "Invalid Tagger ID, ";
}
else if(newEntry.taggerName == "")
{
errorCode += "Invalid Tagger ID, ";
}
else
{
newEntry.taggerID = taggerID;
}
//Check date
formattedDate = dpDate.Value.Year + "-" + String.Format("{0:00}", dpDate.Value.Month) + "-" + String.Format("{0:00}", dpDate.Value.Day)
+ " " + String.Format("{0:00}", dateTimePicker1.Value.Hour) + ":" + String.Format("{0:00}", dateTimePicker1.Value.Minute) + ":"
+ String.Format("{0:00}", dateTimePicker1.Value.Second);
if (!DataValidation.validateDateTime(formattedDate))
{
errorCode += "Invalid Date, ";
}
else
{
newEntry.entryDate = formattedDate;
}
//Check city
if (!DataValidation.validateCity(tbCity.Text))
{
errorCode += "Invalid City, ";
}
else
{
newEntry.city = tbCity.Text;
}
//Check state
if (!DataValidation.validateState(tbState.Text))
{
errorCode += "Invalid State, ";
}
else
{
newEntry.state = tbState.Text;
}
//Check country
if (!DataValidation.validateCountry(tbCountry.Text))
{
errorCode += "Invalid Country, ";
}
else
{
newEntry.country = tbCountry.Text;
}
//Check for some sort of location information
if(tbCity.Text == "" && tbState.Text == "" && tbCountry.Text == "")
{
if(tbLatitude.Text == "" || tbLongitude.Text == "")
{
return false;
}
else
{
//Check latitude
if (!DataValidation.validateLatitude(tbLatitude.Text))
{
errorCode += "Invalid Latitude, ";
}
else
{
newEntry.latitude = tbLatitude.Text;
}
//Check longitude
if (!DataValidation.validateLongitude(tbLongitude.Text))
{
errorCode += "Invalid Longitude, ";
}
else
{
newEntry.longitude = tbLongitude.Text;
}
}
}
if(tbLatitude.Text == "" && tbLongitude.Text == "")
{
if(tbCity.Text == "" && tbState.Text == "" && tbCountry.Text == "")
{
return false;
}
}
//Check species
if (!DataValidation.validateSpecies(tbSpecies.Text))
{
errorCode += "Invalid Species, ";
}
else
{
newEntry.species = tbSpecies.Text;
}
if (cbTag.Checked)
{
int tagNumber = 0;
if (!Int32.TryParse(tfTagNumber.Text, out tagNumber))
{
//MessageBox.Show("Invalid input for tag number");
errorCode += "Invalid Tag Number, ";
}
//Check tag number
if (tfTagNumber.Text == "")
{
newEntry.isTagged = false;
newEntry.tagNumber = -1;
}
else if (!DataValidation.validateTagNumber(tagNumber))
{
errorCode += "Invalid Tag Number, ";
}
else
{
newEntry.isTagged = true;
newEntry.tagNumber = tagNumber;
}
}
//Check temperature
if (!DataValidation.validateTemp(tfTemperature.Text))
{
errorCode += "Invalid Temperature, ";
}
else
{
newEntry.temperature = tfTemperature.Text;
}
//Check precipitation
if (!DataValidation.validatePrecipitation(tfPrecipitation.Text))
{
errorCode += "Invalid Precipitation, ";
}
else
{
newEntry.precipitation = tfPrecipitation.Text;
}
//Check wind speed
if (!DataValidation.validateWindSpeed(tfWindSpeed.Text))
{
errorCode += "Invalid Wind Speed, ";
}
else
{
newEntry.windSpeed = tfWindSpeed.Text;
}
//Check wind direction
if (!DataValidation.validateWindDirection(tfWindDirection.Text))
{
errorCode += "Invalid Wind Direction";
}
else
{
newEntry.windDirection = tfWindDirection.Text;
}
//Invalid entry if any errors occurred
if (errorCode != "")
{
//TEST: Print error codes
Console.WriteLine(errorCode);
return false;
}
//Valid entry if no errors occurred
else
{
return true;
}
}
//Class methods
//Import from selected file into database
public static void Import(string fileName, MySqlConnection connection)
{
//Initialize variables
int importCount = 0; //Number of entries attempted for import
string entryString = ""; //Next entry string to be imported
string importedEntries = ""; //Line number of each imported entry
string failedEntries = ""; //Line number of each failed entry
List<int> entriesImported = new List<int>(); //List of imported entry numbers
List<int> entriesFailed = new List<int>(); //List of failed entry numbers
StreamReader fileStream = new StreamReader(fileName); //Reader for getting input
TrackingEntry newEntry = new TrackingEntry(connection); //Entry to be added to the database
TrackingProcessing newProcess = new TrackingProcessing(connection); //Processing class for database queries
//Validate the file sequence number, header & trailer, and the number of entries in the file
if(ValidateFile(fileStream))
{
//Move to the first entry string after validating file
GetHeader(entryString, fileStream);
//Get the next line of the file
entryString = fileStream.ReadLine();
//Iterate through entries until the trailer is reached
while (!entryString.StartsWith("TR "))
{
//Parse the new entry, and validate data
if(newEntry.Parse(entryString) != false)
{
Console.WriteLine(newEntry.tagNumber);
//Add entry to repository
newProcess.addTrackingEntry(newEntry);
importCount++;
entryString = fileStream.ReadLine();
//Store successfully imported entries
entriesImported.Add(importCount);
}
//Error return if one or more fields are invalid
else
{
importCount++;
entryString = fileStream.ReadLine();
//Store unsuccessfully imported entries
entriesFailed.Add(importCount);
}
}//while
//Build the imported and failed string of entries
for(int i = 0; i < entriesImported.Count; i++)
{
importedEntries += Convert.ToString(entriesImported[i]) + ", ";
}//for
for(int i = 0; i < entriesFailed.Count; i++)
{
failedEntries += Convert.ToString(entriesFailed[i]) + ", ";
}//for
//Show success message
MessageBox.Show("Successfully imported file: " + fileName + "\nThe next file in sequence is " + String.Format("{0:0000}", (seqNum + 1))
+ ".\nImported entries #: " + importedEntries + "\nFailed entries #: " + failedEntries,"Import Succeeded",
MessageBoxButtons.OK, MessageBoxIcon.Information);
//Log a success and close the file
Log.Write("Successfully imported file: " + fileName);
fileStream.Close();
}
//Show error box if file cannot be validated
else
{
//Show error message
MessageBox.Show("Could not import file!\nThis is due to an invalid header or trailer line.", "Error Message:", MessageBoxButtons.OK, MessageBoxIcon.Warning);
//Log a failure and close the file
Log.Write("Failed to import file: " + fileName);
fileStream.Close();
}
}
public void updateLocation(TrackingEntry entry)
{
string city = entry.city;
string state = entry.state;
string country = entry.country;
Location location;
if (city != "" && state != "" && country != "")
{
location = locationQuery.getLocation(city, state, country);
if (location == null)
{
locationQuery.addLocation(city, state, country);
}
else
{
locationQuery.incrementSightings(location.id);
}
}
else if (state != "" && country != "")
{
location = locationQuery.getLocation(state, country);
if (location == null)
{
locationQuery.addLocation(state, country);
}
else
{
locationQuery.incrementSightings(location.id);
}
}
else if (country != "")
{
location = locationQuery.getLocation(country);
if (location == null)
{
locationQuery.addLocation(country);
}
else
{
locationQuery.incrementSightings(location.id);
}
}
else
{
// Error
}
}
public void updateDate(TrackingEntry entry)
{
string date = entry.entryDate;
Date trackingDate = new Date();
DateQuery dateQuery = new DateQuery(connection);
trackingDate = dateQuery.getDate(date);
if(trackingDate == null)
{
trackingDate = new Date();
trackingDate.date = date;
dateQuery.addDate(trackingDate);
}
else
{
dateQuery.incrementSightings(trackingDate.id);
}
}
public void updateButterfly(TrackingEntry entry)
{
int tagNumber = entry.tagNumber;
if (tagNumber == -1)
return;
else
{
Butterfly butterfly = new Butterfly();
ButterflyQuery butterflyQuery = new ButterflyQuery(connection);
butterfly = butterflyQuery.getButterfly(tagNumber);
if(butterfly == null)
{
butterfly = new Butterfly();
butterfly.tagNumber = tagNumber;
butterfly.species = entry.species;
butterflyQuery.addButterfly(butterfly);
}
else
{
butterflyQuery.incrementSightings(butterfly.tagNumber);
}
}
}
public bool unitTestAddEntry(TrackingEntry entry)
{
process.addTrackingEntry(entry);
return true;
}
public static void trackingProcessingTests(MySqlConnection connection, bool testEntry,
bool testButterfly, bool testLocation, bool testDate)
{
TrackingProcessingUnitTest test = new TrackingProcessingUnitTest(connection);
TrackingEntry entry = new TrackingEntry();
TrackingProcessing process = new TrackingProcessing(connection);
if (testEntry)
{
entry = process.createTrackingEntry(1, -1, 'T', "Sean Gallagher", "2015-12-13 12:56:45", 1,
"Monarch", "Dearborn", "MI", "USA", "", "", "", "", "", "");
test.unitTestAddEntry(entry);
Console.WriteLine("Done");
}
if (testButterfly)
{
entry = process.createTrackingEntry(1, -1, 'T', "Sean Gallagher", "2015-12-13 01:40:00", 1,
"Monarch", "Dearborn", "MI", "USA", "", "", "", "", "", "");
test.unitTestAddEntry(entry);
Console.WriteLine("Done");
entry = process.createTrackingEntry(1, -1, 'T', "Sean Gallagher", "2015-12-13 01:40:00", 16,
"Tiger Swallowtail", "Dearborn", "MI", "USA", "", "", "", "", "", "");
test.unitTestAddEntry(entry);
Console.WriteLine("Done");
}
if (testLocation)
{
entry = process.createTrackingEntry(1, -1, 'T', "Sean Gallagher", "2015-12-13 01:40:00", 16,
"Tiger Swallowtail", "", "", "USA", "", "", "", "", "", "");
test.unitTestAddEntry(entry);
Console.WriteLine("Done");
}
if (testDate)
{
entry = process.createTrackingEntry(1, -1, 'T', "Sean Gallagher", "2015-12-13 12:48:10", 16,
"Tiger Swallowtail", "", "", "USA", "", "", "", "", "", "");
test.unitTestAddEntry(entry);
Console.WriteLine("Done");
}
}
public static void trackingEntryTests(DataManager dm, bool add, bool getByEntryID, bool getAll,
bool getByTag, bool getByTagOrdered, bool getInterval, bool getOn, bool getBySpecies,
bool getByLocation, bool getByLocationOrdered, bool getTagged, bool getNotTagged, bool getByType,
bool getByLocationCountry, bool getByLocationStateCountry)
{
TrackingEntryUnitTest test = new TrackingEntryUnitTest(dm.getConnection());
TrackingEntry entry = new TrackingEntry();
entry.taggerID = 1;
entry.type = 'T';
entry.isTagged = true;
entry.taggerName = "Sean Gallagher";
entry.entryDate = "2015-12-12 03:19:00";
entry.species = "Monarch";
entry.tagNumber = 1;
Console.WriteLine("\n--TRACKING ENTRY UNIT TESTS--");
if (add)
{
Console.WriteLine("Test 1: Add Tracking Entry");
if (test.unitTestAddEntry(entry))
Console.WriteLine(" Added successfully");
else
Console.WriteLine(" Not added");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getByEntryID)
{
Console.WriteLine("Test 2: Get Tracking Entry by entry ID");
if (test.unitTestGetEntry(4))
Console.WriteLine("Test 2: Success");
else
Console.WriteLine("Test 2: Entry not found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getAll)
{
Console.WriteLine("Test 3: Get all tracking entries");
if (test.unitTestGetAll())
Console.WriteLine("Test 3: Success");
else
Console.WriteLine("Test 3: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getByTag)
{
Console.WriteLine("Test 4: Get all by tag number");
if (test.unitTestGetByTag(126))
Console.WriteLine("Test 4: Success");
else
Console.WriteLine("Test 4: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getByTagOrdered)
{
Console.WriteLine("Test 5: Get all by tag number in ascending order");
if (test.unitTestGetByTagOrdered(126))
Console.WriteLine("Test 5: Success");
else
Console.WriteLine("Test 5: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getInterval)
{
Console.WriteLine("Test 6: Get all entries in an interval");
if (test.unitTestGetInterval("2015-11-1", "2015-11-20"))
Console.WriteLine("Test 6: Success");
else
Console.WriteLine("Test 6: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getOn)
{
Console.WriteLine("Test 7: Get all entries on a specific date");
if (test.unitTestGetOn("2015-11-1"))
Console.WriteLine("Test 7: Success");
else
Console.WriteLine("Test 7: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getBySpecies)
{
Console.WriteLine("Test 8: Get all entries by species");
if (test.unitTestGetBySpecies("Monarch"))
Console.WriteLine("Test 8: Success");
else
Console.WriteLine("Test 8: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
Console.WriteLine("Test 8: Get all entries by species and date");
if (test.unitTestGetBySpecies("Monarch", "2015-12-13"))
Console.WriteLine("Test 8: Success");
else
Console.WriteLine("Test 8: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getByLocation)
{
Console.WriteLine("Test 9: Get all entries by location");
if (test.unitTestGetByLocation("Dearborn", "MI", "USA"))
Console.WriteLine("Test 9: Success");
else
Console.WriteLine("Test 9: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getByLocationOrdered)
{
Console.WriteLine("Test 10: Get all entries by location in ascending order by date");
if (test.unitTestGetByLocationOrdered("Dearborn", "MI", "USA"))
Console.WriteLine("Test 10: Success");
else
Console.WriteLine("Test 10: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
Console.WriteLine("Test 10: Get all entries by location (state and country only)"
+ " in ascending order by date");
if (test.unitTestGetByLocationOrdered("MI", "USA"))
Console.WriteLine("Test 10: Success");
else
Console.WriteLine("Test 10: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
Console.WriteLine("Test 10: Get all entries by location (country only) in ascending order by date");
if (test.unitTestGetByLocationOrdered("USA"))
Console.WriteLine("Test 10: Success");
else
Console.WriteLine("Test 10: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getTagged)
{
Console.WriteLine("Test 11: Get all tagged entries");
if (test.unitTestGetTagged(true))
Console.WriteLine("Test 11: Success");
else
Console.WriteLine("Test 11: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getNotTagged)
{
Console.WriteLine("Test 12: Get all entries that are not tagged");
if (test.unitTestGetTagged(false))
Console.WriteLine("Test 12: Success");
else
Console.WriteLine("Test 12: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getByType)
{
Console.WriteLine("Test 13: Get all entries by type T");
if (test.unitTestGetByType('T'))
Console.WriteLine("Test 13: Success");
else
Console.WriteLine("Test 13: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
Console.WriteLine("Test 14: Get all entries by type S");
if (test.unitTestGetByType('S'))
Console.WriteLine("Test 14: Success");
else
Console.WriteLine("Test 14: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getByLocationCountry)
{
Console.WriteLine("Test 15: Get all entries by location (country only)");
if (test.unitTestGetByLocation("USA"))
Console.WriteLine("Test 15: Success");
else
Console.WriteLine("Test 15: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
if (getByLocationStateCountry)
{
Console.WriteLine("Test 16: Get all entries by location (state and country only");
if (test.unitTestGetByLocation("MI", "USA"))
Console.WriteLine("Test 16: Success");
else
Console.WriteLine("Test 16: No entries found");
Console.Write("-----------------------------------------");
Console.WriteLine("-----------------------------------------");
}
}