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));
}