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 IEnumerable <Ride> GetRecentRides()
{
// get a list of all dates (in future or recent past) that have rides attached
// date represented by days since 01/01/1970
LogEntry log = new LogEntry("GetDatesWithRides", "");
List <Ride> rides = new List <Ride>();
// get JS date for a month ago
int appdays = Logdata.NowtoJSDate() - 31;
if (gpxConnection.IsConnect())
{
try
{
string query = string.Format("SELECT date,leaderName,routeID FROM rides where date > {0}", appdays);
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++)
{
string leader = "";
int routeID = 0, date = 0;
try
{
DataRow dr = dataRoutes.Rows[row];
routeID = (int)dr["routeID"];
try { date = (int)dr["date"]; } catch { }
try { leader = (string)dr["leadername"]; } catch { }
//DateTime dt = Logdata.JSDateToDateTime(date);
rides.Add(new Ride(routeID, leader, 0, date, 0, "", "", Ride.maxRiders));
}
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() + " future and recent rides found ";
log.Save(gpxConnection);
gpxConnection.Close();
}
}
return(rides);
}
public string ChangeAccount(Login login)
{
LogEntry log = new LogEntry("ChangeAccount", login.Name);
string query;
string result = "";
if (gpxConnection.IsConnect())
{
try
{
if (login.PW != string.Empty) // has actually been changed
{
string hash = Logdata.GetHash(login.PW);
query = string.Format("update logins set pw = '{0}' where id = {1}", hash, login.ID);
var cmd = new MySqlCommand(query, gpxConnection.Connection);
cmd.ExecuteNonQuery();
}
if (login.Email != string.Empty) // has actually been changed
{
query = string.Format("update logins set email = '{0}' where id = {1}", login.Email, login.ID);
var cmd = new MySqlCommand(query, gpxConnection.Connection);
cmd.ExecuteNonQuery();
}
if (login.Name != string.Empty) // has actually been changed
{
query = string.Format("update logins set name = '{0}' where id = {1}", login.Name, login.ID);
var cmd = new MySqlCommand(query, gpxConnection.Connection);
cmd.ExecuteNonQuery();
}
if (true)
{
query = string.Format("update logins set units = '{0}', climbs={1} where id = {2}", login.Units, login.Climbs, login.ID);
var cmd = new MySqlCommand(query, gpxConnection.Connection);
cmd.ExecuteNonQuery();
}
result = "OK";
}
catch (Exception ex2)
{
result = "There is a database error, some details not changed, please try again: " + ex2.Message;
log.Error = ex2.Message;
}
finally
{
log.Result = result;
log.Save(gpxConnection);
gpxConnection.Close();
}
}
else
{
return(DBConnection.ErrStr);
}
return(result);
}
public string Register(Login login)
{
LogEntry log = new LogEntry("Register2", login.Name + " " + login.EmailCode);
string result = "";
if (login.EmailCode == Logdata.GetHash(login.Name + login.Name))
{
string query = string.Format("update logins set role = 1 where name = '{0}'",
login.Name);
if (gpxConnection.IsConnect())
{
try
{
var cmd = new MySqlCommand(query, gpxConnection.Connection);
cmd.ExecuteNonQuery();
result = "Thank you, you have now registered";
}
catch (Exception ex)
{
result = "There is a database error, please try again:" + ex.Message;
log.Error = ex.Message;
}
finally
{
log.Result = login.Name;
log.Save(gpxConnection);
gpxConnection.Close();
}
}
else
{
return(DBConnection.ErrStr);
}
return(result);
}
else
{
return("Error with email or code, sorry");
}
}
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 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 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 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);
}
/// <summary>
/// Log in to the system
/// </summary>
/// <param name="login">login object with just a username and password</param>
/// <returns>login object with details of role and user id</returns>
public Login Login(Login login)
{
string hash = Logdata.GetHash(login.PW);
LogEntry log = new LogEntry("Login", login.Name);
string result = "";
// can now login with either username or email
string query = string.Format("SELECT Id, name, pw, email, role, units, climbs FROM logins where name = '{0}' or email = '{0}'", login.Name);
if (gpxConnection.IsConnect())
{
try
{
using (MySqlDataAdapter loginAdapter = new MySqlDataAdapter(query, gpxConnection.Connection))
{
dataLogins = new DataTable();
loginAdapter.Fill(dataLogins);
int length = dataLogins.Rows.Count;
for (int row = 0; row < length; row++)
{
DataRow dr = dataLogins.Rows[row];
string dbname = (string)dr["name"];
dbname = dbname.Trim();
string dbpw = (string)dr["pw"];
dbpw = dbpw.Trim();
string dbemail = (string)dr["email"];
dbemail = dbemail.Trim();
// login with either username or email
if ((dbname == login.Name && dbpw == hash) || (dbemail == login.Name && dbpw == hash))
{
if (dbemail == login.Name)
{
// change back to actual login name
login.Name = dbname;
}
login.Role = (int)dr["role"];
login.ID = (int)dr["id"];
login.Email = (string)dr["email"];
login.Units = ((string)dr["units"])[0];
login.Climbs = (int)dr["climbs"];
// don't need to return the password
login.PW = String.Empty;
break;
}
}
}
}
catch (Exception ex)
{
result = "There is a database error, please try again:" + ex.Message;
log.Error = ex.Message;
}
finally
{
log.Result = login.Name;
log.Save(gpxConnection);
gpxConnection.Close();
}
return(login);
}
return(null);
}
public string ForgetPassword(string email)
{
LogEntry log = new LogEntry("ForgetPassword", email);
string result = "OK, now please wait for an email and click the link to set a new password";
string username = "";
MailAddress emailAddr;
try
{
emailAddr = new MailAddress(email);
// Valid address
}
catch
{
return("This email address appears to be invalid");
}
if (gpxConnection.IsConnect())
{
string query = string.Format("SELECT Id, name, email FROM logins where email = '{0}'", email);
try
{
using (MySqlDataAdapter loginAdapter = new MySqlDataAdapter(query, gpxConnection.Connection))
{
dataLogins = new DataTable();
loginAdapter.Fill(dataLogins);
int count = dataLogins.Rows.Count;
if (count == 1)
{
DataRow dr = dataLogins.Rows[0];
string dbname = (string)dr["name"];
username = dbname.Trim();
}
else if (count == 0)
{
return(string.Format("Error: cannot find an account with that email"));
}
else
{
return(string.Format("Error: {0} users found with that email", dataLogins.Rows.Count));
}
}
}
catch (Exception ex)
{
log.Error = ex.Message;
return("DB error: " + ex.Message);
}
// create and send an email
try
{
// create a code based on data
string emailCode = Logdata.GetHash(username + username);
// string URLstr = string.Format("https://quilkin.co.uk/tccrides?pwuser={0}®code={1}", username, emailCode);
string URLstr = string.Format(Connections.serviceURL + "?pwuser={0}®code={1}", username, emailCode);
EmailConnection ec = new EmailConnection();
MailAddress from = new MailAddress("*****@*****.**");
MailMessage message = new MailMessage(from, emailAddr)
{
Subject = "TCC rides forgotten password",
Body = string.Format("Please click {0} to reset your password or other details.\n\rFor security, this link will expire in 15 minutes!", URLstr)
};
try
{
SmtpClient client = new System.Net.Mail.SmtpClient(ec.Server)
{
Credentials = new System.Net.NetworkCredential(ec.User, ec.PW)
};
client.Send(message);
// save the time this message was delivered
query = string.Format("update logins set messagetime = '{0}' where email = '{1}'", Logdata.DBTimeString(DateTime.Now), email);
try
{
var cmd = new MySqlCommand(query, gpxConnection.Connection);
cmd.ExecuteNonQuery();
}
catch (Exception ex2)
{
result = "There is a database error, please try again:" + ex2.Message;
log.Error = ex2.Message;
}
result = "OK, now please wait for an email and click the link to set a new password";
}
catch (Exception ex)
{
result = "Sorry, there is an error with the email service: " + ex.Message;
log.Error = ex.Message;
}
}
catch (Exception ex2)
{
return("Error: " + ex2.Message);
}
finally
{
log.Result = result;
log.Save(gpxConnection);
gpxConnection.Close();
}
return(result);
}
else
{
return(DBConnection.ErrStr);
}
}
public string Signup(Login login)
{
string hash = Logdata.GetHash(login.PW);
LogEntry log = new LogEntry("Signup", login.Name);
MailAddress emailAddr;
string result = "OK, now please wait for an email and click the link to complete your registration";
try
{
emailAddr = new MailAddress(login.Email);
// Valid address
}
catch
{
return("This email address appears to be invalid");
}
if (login.PW.Length < 4 || login.PW.Length > 10)
{
return("Password must be between 4 and 10 characters");
}
if (gpxConnection.IsConnect())
{
// check username and email
string query = "SELECT Id, name, pw, email FROM logins";
try
{
using (MySqlDataAdapter loginAdapter = new MySqlDataAdapter(query, gpxConnection.Connection))
{
dataLogins = new DataTable();
loginAdapter.Fill(dataLogins);
int length = dataLogins.Rows.Count;
for (int row = 0; row < length; row++)
{
DataRow dr = dataLogins.Rows[row];
string dbname = (string)dr["name"];
dbname = dbname.Trim();
string dbpw = (string)dr["pw"];
dbpw = dbpw.Trim();
string dbemail = (string)dr["email"];
dbemail = dbemail.Trim();
if (dbname.ToLower() == login.Name.ToLower())
{
return("Sorry, this username has already been taken");
}
if (dbemail == login.Email)
{
return("Sorry, only one login allowed per email address");
}
if (EmailConnection.IsValidEmail(dbemail) == false)
{
return("Sorry, this email doesn't appear to be valid");
}
}
}
}
catch (Exception ex)
{
log.Error = ex.Message;
return("DB error: " + ex.Message);
}
// create and send an email
try
{
// create a code based on data
login.EmailCode = Logdata.GetHash(login.Name + login.Name);
string URLstr = string.Format(Connections.serviceURL + "?user={0}®code={1}", login.Name, login.EmailCode);
//string URLstr = string.Format("http://localhost/routes/www?user={0}®code={1}",login.Name, login.EmailCode);
EmailConnection ec = new EmailConnection();
MailAddress from = new MailAddress("*****@*****.**");
MailMessage message = new MailMessage(from, emailAddr)
{
Subject = "TCC rides signup",
Body = string.Format("Please click {0} to complete your registration", URLstr)
};
try
{
SmtpClient client = new System.Net.Mail.SmtpClient(ec.Server)
{
Credentials = new System.Net.NetworkCredential(ec.User, ec.PW)
};
client.Send(message);
// save the login details but with role as zero so login won't yet work
log = new LogEntry("Register1", login.Name + " " + login.EmailCode);
query = string.Format("insert into logins (name, pw, email,role,messagetime,units,climbs) values ('{0}','{1}','{2}',{3},'{4}','{5}',{6})",
login.Name, hash, login.Email, 0, Logdata.DBTimeString(DateTime.Now), login.Units, login.Climbs);
try
{
var cmd = new MySqlCommand(query, gpxConnection.Connection);
cmd.ExecuteNonQuery();
result = "Thank you, please wait for an email and click link to complete registration";
}
catch (Exception ex2)
{
result = "There is a database error, please try again:" + ex2.Message;;
}
}
catch (Exception ex)
{
result = "Sorry, there is an error with the email service: " + ex.Message;
}
}
catch (Exception ex2)
{
result = "Error: " + ex2.Message;
log.Error = ex2.Message;
}
finally
{
log.Result = result;
log.Save(gpxConnection);
gpxConnection.Close();
}
return(result);
}
else
{
return(DBConnection.ErrStr);
}
}
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);
}
public string SaveCafe(Cafe cafe)
{
cafe.Name = GetRidOfApostrophes(cafe.Name);
cafe.Notes = GetRidOfApostrophes(cafe.Notes);
cafe.PlaceName = GetRidOfApostrophes(cafe.PlaceName);
cafe.TimesOpen = GetRidOfApostrophes(cafe.TimesOpen);
cafe.DaysOpen = GetRidOfApostrophes(cafe.DaysOpen);
LogEntry log = new LogEntry("SaveCafe", cafe.ID + " " + cafe.Name + " " + cafe.User);
string result = "";
if (gpxConnection.IsConnect())
{
try
{
string query;
//// 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.");
//}
// using (System.Net.WebClient client = new System.Net.WebClient())
{
if (cafe.ID > 0)
{
// already exists, just update
query = string.Format("update cafes set name = '{0}',placename = '{1}', notes = '{2}',daysopen = '{3}',timesopen ='{4}',user ='******', updated = '{6}' where id = {7} ",
cafe.Name, cafe.PlaceName, cafe.Notes, cafe.DaysOpen, cafe.TimesOpen, cafe.User, Logdata.DBTimeString(DateTime.Now), cafe.ID);
using (MySqlCommand command = new MySqlCommand(query, gpxConnection.Connection))
{
command.ExecuteNonQuery();
}
result = "0";
}
else
{
query = string.Format("insert into cafes (lat,lng,name,placename,notes,daysopen,timesopen,user,updated) values ({0},{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
cafe.Lat, cafe.Lng, cafe.Name, cafe.PlaceName, cafe.Notes, cafe.DaysOpen, cafe.TimesOpen, cafe.User, Logdata.DBTimeString(DateTime.Now));
// get new ride ID
query += "; SELECT CAST(LAST_INSERT_ID() AS int)";
object cafeID = null;
using (MySqlCommand command = new MySqlCommand(query, gpxConnection.Connection))
{
cafeID = command.ExecuteScalar();
}
// return id of new cafe
result = cafeID.ToString();
}
}
}
catch (Exception ex)
{
result = string.Format("Database error: ride \"{0}\" not saved: {1}", cafe.Name, ex.Message);
log.Error = ex.Message;
}
finally
{
log.Result = result;
log.Save(gpxConnection);
gpxConnection.Close();
}
}
else
{
return(DBConnection.ErrStr);
}
return(result);
}
