public IEnumerable <Ride> GetRidesForDate(int date)
{
// get details of routes available for a given date for the next month (but not yet the GPX data)
// date represented by days since 01/01/1970
LogEntry log = new LogEntry("GetRidesForDate", Logdata.JSDateToDateTime(date).ToShortDateString());
List <Ride> rides = new List <Ride>();
if (gpxConnection.IsConnect())
{
try
{
string query = string.Format("SELECT rideID,routeID,date,time,meetingAt,leaderName,description,groupSize FROM rides where date >= {0} and date <= {1} order by date asc", date - 1, date + 31);
using (MySqlDataAdapter routeAdapter = new MySqlDataAdapter(query, gpxConnection.Connection))
{
dataRoutes = new DataTable();
routeAdapter.Fill(dataRoutes);
int length = dataRoutes.Rows.Count;
for (int row = 0; row < length; row++)
{
if (row >= 10)
{
break;
}
string meet = "", leader = "", descrip = "";
int time = 0, id, routeID = 0, size = 10;
DataRow dr = dataRoutes.Rows[row];
try
{
id = (int)dr["rideID"];
routeID = (int)dr["routeID"];
meet = (string)dr["meetingAt"];
date = (int)dr["date"];
time = (int)dr["time"];
leader = (string)dr["leadername"];
descrip = (string)dr["description"];
size = (int)dr["groupSize"];
rides.Add(new Ride(routeID, leader, id, date, time, meet, descrip, size));
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
log.Error = ex.Message;
}
}
}
}
catch (Exception ex2)
{
Trace.WriteLine(ex2.Message);
log.Error = ex2.Message;
}
finally
{
log.Result = rides.Count.ToString() + " rides for " + Logdata.JSDateToDateTime(date).ToShortDateString();
log.Save(gpxConnection);
gpxConnection.Close();
}
}
return(rides);
}
public string DeleteRoute(int routeID)
{
LogEntry log = new LogEntry("DeleteRoute ", routeID.ToString());
int successRows = 0;
string result = "";
if (gpxConnection.IsConnect())
{
try
{
// first check that there are no future rides connected with this route
// convert to our app date type
int appdays = Logdata.NowtoJSDate();
//DateTime today = DateTime.Now;
DateTime jan1970 = new DateTime(1970, 1, 1);
//TimeSpan appSpan = today - jan1970;
//int appdays = appSpan.Days;
string query = string.Format("SELECT rideID,date FROM rides where routeID = {0} and date > {1}", routeID, appdays);
int count = 0;
string now = Logdata.TimeString(DateTime.Now);
using (MySqlDataAdapter routeAdapter = new MySqlDataAdapter(query, gpxConnection.Connection))
{
dataRoutes = new DataTable();
routeAdapter.Fill(dataRoutes);
count = dataRoutes.Rows.Count;
if (count > 0)
{
DataRow dr = dataRoutes.Rows[0];
appdays = (int)dr["date"];
}
}
if (count > 0)
{
// convert app days back to c# date
//TimeSpan days = new TimeSpan(appdays, 0, 0, 0);
//DateTime when = jan1970 + days;
DateTime when = Logdata.JSDateToDateTime(appdays);
result = string.Format("There is at least one ride using this route in the future, on {0}. Please delete the ride first (if there are no riders signed up for it)", when.ToShortDateString());
}
else
{
//using (System.Net.WebClient client = new System.Net.WebClient())
{
query = string.Format("delete from routes where id = {0}", routeID);
using (MySqlCommand command = new MySqlCommand(query, gpxConnection.Connection))
{
successRows = command.ExecuteNonQuery();
}
result = "OK";
}
}
}
catch (Exception ex)
{
result = string.Format("Database error: {0}", ex.Message);
}
finally
{
log.Result = result;
log.Save(gpxConnection);
gpxConnection.Close();
}
}
else
{
return(DBConnection.ErrStr);
}
return(result);
}