public void Save(DBConnection conn)
{
if (Error.Length < 2)
{
return;
}
try
{
if (conn.Connection.State == System.Data.ConnectionState.Open)
{
// prevent char ' messing up the query
Result = Result.Replace("'", "''");
Error = Error.Replace("'", "''");
string query = string.Format("insert into log (time,ip,func,args,result,error) values ('{0}','{1}','{2}','{3}','{4}','{5}')",
Logdata.TimeString(DateTime.Now), GetIP(), this.Function, this.Args, this.Result, this.Error);
using (MySqlCommand command = new MySqlCommand(query, conn.Connection))
{
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public string SaveRide(Ride ride)
{
// ride.MeetAt = ride.MeetAt.Replace("'", "''");
ride.MeetAt = GetRidOfApostrophes(ride.MeetAt);
ride.Descrip = GetRidOfApostrophes(ride.Descrip);
LogEntry log = new LogEntry("SaveRide", ride.Date + " " + ride.routeID);
//int successRows = 0;
string result = "";
if (gpxConnection.IsConnect())
{
try
{
// check ride with same leader and date isn't already there ***************
string query = string.Format("SELECT dest FROM rides where date= '{0}' and leaderName = '{1}'", ride.Date, ride.LeaderName);
bool exists = true;
string now = Logdata.TimeString(DateTime.Now);
string rideDest = "";
using (MySqlDataAdapter routeAdapter = new MySqlDataAdapter(query, gpxConnection.Connection))
{
dataRoutes = new DataTable();
routeAdapter.Fill(dataRoutes);
if (dataRoutes.Rows.Count == 0)
{
exists = false;
}
else
{
DataRow dr = dataRoutes.Rows[0];
try { rideDest = (string)dr["dest"]; } catch { }
}
}
if (exists)
{
result = string.Format("There is already a ride with you as leader on the same date. Please choose another date.");
}
else
{
//using (System.Net.WebClient client = new System.Net.WebClient())
{
query = string.Format("insert into rides (routeID,leaderName,date,time,meetingAt,description,groupSize) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
ride.routeID, ride.LeaderName, ride.Date, ride.Time, ride.MeetAt, ride.Descrip, ride.GroupSize);
// get new ride ID
query += "; SELECT CAST(LAST_INSERT_ID() AS int)";
object rideID = null;
using (MySqlCommand command = new MySqlCommand(query, gpxConnection.Connection))
{
rideID = command.ExecuteScalar();
}
// return id of new route
result = rideID.ToString();
}
//}
}
}
catch (Exception ex)
{
result = string.Format("Database error: ride \"{0}\" not saved: {1}", ride.ID, ex.Message);
log.Error = ex.Message;
}
finally
{
log.Result = result;
log.Save(gpxConnection);
gpxConnection.Close();
}
}
else
{
return(DBConnection.ErrStr);
}
return(result);
}
public string SaveParticipant(Participant pp)
{
LogEntry log = new LogEntry("SaveParticipant", pp.Rider + " " + pp.rideID);
int successRows = 0;
string result = "";
if (gpxConnection.IsConnect())
{
try
{
// check this isn't already there ***************
string query = string.Format("SELECT rider FROM Participants where rideID = '{0}' and rider = '{1}'", pp.rideID, pp.Rider);
bool exists = true;
string now = Logdata.TimeString(DateTime.Now);
using (MySqlDataAdapter routeAdapter = new MySqlDataAdapter(query, gpxConnection.Connection))
{
dataRoutes = new DataTable();
routeAdapter.Fill(dataRoutes);
if (dataRoutes.Rows.Count == 0)
{
exists = false;
}
}
if (exists)
{
result = "You are aleady booked onto this ride. Please choose another ride";
}
else
{
// todo: this string is now redundant
string riders = "*";
query = string.Format("insert into Participants (rider, rideID) values ('{0}','{1}')", pp.Rider, pp.rideID);
using (MySqlCommand command = new MySqlCommand(query, gpxConnection.Connection))
{
successRows = command.ExecuteNonQuery();
}
result = riders;
}
}
catch (Exception ex)
{
result = string.Format("Database error: {0}", ex.Message);
log.Error = ex.Message;
}
finally
{
log.Result = result;
log.Save(gpxConnection);
gpxConnection.Close();
}
}
else
{
return(DBConnection.ErrStr);
}
return(result);
}
public string LeaveParticipant(Participant pp)
{
LogEntry log = new LogEntry("LeaveParticipant", pp.Rider + " " + pp.rideID);
int successRows = 0;
string result = "";
if (gpxConnection.IsConnect())
{
try
{
// check this is already there ***************
string query = string.Format("SELECT rider FROM Participants where rideID = '{0}' and rider = '{1}'", pp.rideID, pp.Rider);
bool exists = true;
string now = Logdata.TimeString(DateTime.Now);
using (MySqlDataAdapter routeAdapter = new MySqlDataAdapter(query, gpxConnection.Connection))
{
dataRoutes = new DataTable();
routeAdapter.Fill(dataRoutes);
if (dataRoutes.Rows.Count == 0)
{
exists = false;
}
}
if (exists == false)
{
result = "Error: You are not booked onto this ride.";
}
else
{
query = string.Format("delete from Participants where rider = '{0}'and rideID = {1}", pp.Rider, pp.rideID);
using (MySqlCommand command = new MySqlCommand(query, gpxConnection.Connection))
{
successRows = command.ExecuteNonQuery();
}
result = "OK";
}
}
catch (Exception ex)
{
result = string.Format("Database error: {0}", ex.Message);
log.Error = ex.Message;
}
finally
{
log.Result = result;
log.Save(gpxConnection);
gpxConnection.Close();
}
}
else
{
return(DBConnection.ErrStr);
}
return(result);
}
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);
}