public bool SpoofSightings(int amount, string user, string bird) { Random rnd = new Random(); DateTime now = DateTime.Now; List <Specie> species = GetSpecies(); List <Tower> towers = GetTowers(); List <BirdSighting> spoofed = new List <BirdSighting>(); for (int i = 0; i < amount; i++) { Tower spoofLatitude = towers[rnd.Next(towers.Count)]; Tower spoofLongitude = towers[rnd.Next(towers.Count)]; var sighting = new BirdSighting { username = user, comment = "chiterboi", specie = bird, latitudecoord = spoofLatitude.getLatitude(), longitudecoord = spoofLongitude.getLongitude(), timestamp = now }; spoofed.Add(sighting); } foreach (BirdSighting s in spoofed) { DbConnector.UpdateSightingsToDB(s); } return(true); }
public IEnumerable <BirdSighting> GetSightingsFromUser(string user) { DataTable resultdt = DbConnector.GetSightingsFromDB(user); if (resultdt == null) { return(null); } var SightingList = new List <BirdSighting>(); foreach (DataRow row in resultdt.Rows) { var sighting = new BirdSighting { username = row["username"].ToString(), specie = row["specie"].ToString(), longitudecoord = Convert.ToDouble(row["longitudecoord"]), latitudecoord = Convert.ToDouble(row["latitudecoord"]), comment = row["comment"].ToString(), timestamp = Convert.ToDateTime(row["timestamp"]) }; SightingList.Add(sighting); } return(SightingList); }
public static bool updateSightings() { DateTime now = DateTime.Now; DataTable resultdt = DbConnector.GetSpeciesFromDB(); if (resultdt == null) { return(false); } foreach (DataRow row in resultdt.Rows) { String birdTableName = "testdb.bird" + row["speciename"].ToString(); birdTableName = birdTableName.Replace("-", ""); String clearQuery = "DELETE from " + birdTableName + ";"; executeNonQuery(clearQuery); string sightingsQuery = "SELECT * FROM testdb.sightings WHERE specie = '@name';"; sightingsQuery = sightingsQuery.Replace("@name", row["speciename"].ToString()); DataTable birds = executeQuery(sightingsQuery); if (birds == null) { return(false); } var freshBirds = new List <BirdSighting>(); foreach (DataRow birbRow in birds.Rows) { DateTime time = Convert.ToDateTime(birbRow["timestamp"]); TimeSpan span = now.Subtract(time); double lon = Convert.ToDouble(birbRow["longitudecoord"]); double lat = Convert.ToDouble(birbRow["latitudecoord"]); if (span.CompareTo(TimeSpan.FromDays(FRESH_BIRD_TIMESPAN)) < 0) { var sighting = new BirdSighting { username = "", specie = row["speciename"].ToString(), longitudecoord = lon, latitudecoord = lat, comment = "", timestamp = now }; freshBirds.Add(sighting); } } double[][] clusters = KekMeansLocationProviderAdapter.ClusterPartitions(freshBirds.ToList <LocationProvider>(), KekMeansLocationProviderAdapter.DEFAULT_CLUSTER_AMOUNT); string insertQuery = "INSERT INTO " + birdTableName + " VALUES(@latitude, @longtitude);"; for (int i = 0; i < clusters.Length; i++) { string insertCopy = String.Copy(insertQuery); insertCopy = insertCopy.Replace("@latitude", clusters[i][0].ToString()); insertCopy = insertCopy.Replace("@longtitude", clusters[i][1].ToString()); executeNonQuery(insertCopy); } } return(true); }
public IEnumerable <BirdSighting> GetSightingsForBird(string bird) { DataTable resultdt = DbConnector.GetSightingsForBird(bird); var SightingList = new List <BirdSighting>(); foreach (DataRow row in resultdt.Rows) { var sighting = new BirdSighting { username = "", specie = "", longitudecoord = Double.Parse(row["longtitude"].ToString()), latitudecoord = Double.Parse(row["latitude"].ToString()), comment = "", timestamp = DateTime.Now }; SightingList.Add(sighting); } return(SightingList); }
//--------------------------------------------- //ADD NEW SIGHTINGS TO DB public static bool UpdateSightingsToDB(BirdSighting sighting) { var connectionString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM); var query = "INSERT INTO sightings (username, specie, longitudecoord, latitudecoord, comment, timestamp)" + " VALUES('@username', '@specie', @longitudecoord, @latitudecoord,'@comment',@timestamp)"; query = query.Replace("@username", sighting.username) //ADD empty comment if none provided .Replace("@specie", sighting.specie) //.Replace("@longitudecoord", sighting.longitudecoord) //.Replace("@latitudecoord", sighting.latitudecoord) .Replace("@comment", sighting.comment); //.Replace("@timestamp", sighting.timestamp.ToString()); MySqlConnection connection = new MySqlConnection(connectionString); try { connection.Open(); MySqlCommand command = new MySqlCommand(query, connection); command.Parameters.AddWithValue("@timestamp", DateTime.Now); command.Parameters.AddWithValue("@longitudecoord", sighting.longitudecoord); command.Parameters.AddWithValue("@latitudecoord", sighting.latitudecoord); command.ExecuteNonQuery(); command.Dispose(); connection.Close(); return(true); } catch (Exception) { //throw return(false); } }
public bool SaveSighting(BirdSighting birdsighting) { return(DbConnector.UpdateSightingsToDB(birdsighting)); }