public string SaveEvent(Event ev)
{
bool updated = false;
try
{
ttConnection = new SqlConnection(connection);
ttConnection.Open();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
return ex.Message;
}
try
{
// TODO ****** - make into a transaction
//ev.StartTime = new DateTime(1969, 1, 1).AddMilliseconds((ev.Time));
ev.StartTime = Event.JSTimeToNetTime(ev.Time);
// check that event doesn't already exist
string query = string.Format("SELECT * FROM events where DateTime ='{0}' and CourseID = '{1}'", TimeString(ev.StartTime), ev.CourseID);
using (SqlDataAdapter eventAdapter = new SqlDataAdapter(query, ttConnection))
{
dataEvents = new DataTable();
eventAdapter.Fill(dataEvents);
int length = dataEvents.Rows.Count;
if (length > 0)
{
int newID = 0;
events = new List<Event>();
for (int row = 0; row < length; row++)
{
DataRow dr = dataEvents.Rows[row];
newID = (int)dr["ID"];
if (ev.ID == newID)
// existing event has been modified, OK to continue
break;
}
if (ev.ID != newID)
{
string err = "Cannot save event, already an event at that time on same course";
Trace.WriteLine(err);
ttConnection.Close();
return err;
}
}
}
try
{
query = "set xact_abort on begin transaction eventsave\n\r";
if (ev.ID == 0) // new event
{
query += string.Format("insert into events (CourseID,DateTime,ClubId) values ('{0}','{1}','{2}')\n\r",
ev.CourseID, TimeString(ev.StartTime), ev.ClubID);
query += "DECLARE @ThisEventID Int\n\r SET @ThisEventID = SCOPE_IDENTITY()\n\r";
}
else
{
// start time may have been modified to sync with other timers
query += string.Format("update events set DateTime = '{0}' where Id = '{1}'\n\r", TimeString(ev.StartTime), ev.ID);
// must remove any existing entries in case they have been modified
query += string.Format("delete from entries where EventId = '{0}'\n\r", ev.ID);
query += string.Format("DECLARE @ThisEventID Int\n\r SET @ThisEventID = '{0}'\n\r", ev.ID);
updated = true;
}
if (ev.Entries.Length > 0)
{
query += "insert into entries (EventId,RiderId, Number,Start,Finish,Position)";
bool firstentry = true;
foreach (Entry e in ev.Entries)
{
//DateTime start = new DateTime(1969, 1, 1).AddMilliseconds((e.Start));
//DateTime finish = new DateTime(1969, 1, 1).AddMilliseconds((e.Finish));
DateTime start = Event.JSTimeToNetTime(e.Start);
DateTime finish = Event.JSTimeToNetTime(e.Finish);
if (firstentry)
firstentry = false;
else
query += " UNION ALL ";
query += string.Format("select @ThisEventID,'{0}','{1}','{2}','{3}','{4}'\n\r",
e.RiderID, e.Number, TimeString(start), TimeString(finish), e.Position);
}
}
query += "commit transaction eventsave\n\r";
using (System.Data.SqlClient.SqlCommand command = new SqlCommand(query, ttConnection))
{
command.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
Trace.WriteLine(ex.Message);
return ex.Message;
}
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
return ex.Message;
}
finally
{
ttConnection.Close();
}
string result = updated ? "Event updated OK" : "New event saved";
return result + " with " + ev.Entries.Length + " entries";
}
public IEnumerable<Entry> SeedEntries(Event ev)
{
try
{
ttConnection = new SqlConnection(connection);
ttConnection.Open();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
// first, get entry list in order of best times
string query = string.Format("SELECT * FROM entries JOIN riders ON entries.RiderId = riders.id WHERE entries.EventId='{0}' ORDER BY riders.best25", ev.ID);
using (SqlDataAdapter entryAdapter = new SqlDataAdapter(query, ttConnection))
{
dataEntries = new DataTable();
entryAdapter.Fill(dataEntries);
int length = dataEntries.Rows.Count;
entries = new List<Entry>();
for (int row = 0; row < length; row++)
{
DataRow dr = dataEntries.Rows[row];
DateTime start = (DateTime)dr["Start"];
//start = start.AddYears(-1969);
DateTime finish = (DateTime)dr["Finish"];
//finish = finish.AddYears(-1969);
int riderid = (int)dr["RiderId"];
int pos = (int)dr["Position"];
entries.Add(new Entry((int)dr["Number"], Event.NetTimeToJSTime(start), Event.NetTimeToJSTime(finish), riderid, pos));
}
}
ttConnection.Close();
ev.SeededSort(ref entries);
return entries;
}
// get a list of all events which match any parameters in argument event
public IEnumerable<Event> LoadEvents(Event ev)
{
DateTime date1 = Event.JSTimeToNetTime(ev.Time);
// select any events between first day and number of days (stored in OddData)
DateTime date2 = ev.OddData > 0 ? date1.AddDays(1 + ev.OddData) : DateTime.MaxValue;
int clubID = ev.ClubID;
int courseID = ev.CourseID;
try
{
ttConnection = new SqlConnection(connection);
ttConnection.Open();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
string query = string.Format("SELECT * FROM events where DateTime>'{0}' and DateTime < '{1}'", TimeString(date1), TimeString(date2));
if (clubID > 0)
query += string.Format(" and ClubID='{0}' ", clubID);
if (courseID > 0)
query += string.Format(" and CourseID='{0}' ", courseID);
query += " ORDER BY DateTime";
using (SqlDataAdapter eventAdapter = new SqlDataAdapter(query, ttConnection))
{
dataEvents = new DataTable();
eventAdapter.Fill(dataEvents);
int length = dataEvents.Rows.Count;
events = new List<Event>();
for (int row = 0; row < length; row++)
{
DataRow dr = dataEvents.Rows[row];
DateTime start = (DateTime)dr["DateTime"];
//start = start.AddYears(-1969); // .NET time starts AT 0001; Javascript time starts 1970
events.Add(new Event((int)dr["ID"], (int)dr["CourseID"], Event.NetTimeToJSTime(start), (int)dr["ClubId"], null));
}
}
ttConnection.Close();
return events;
}