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