/// <summary>
/// Save the Call
/// </summary>
public override void Save()
{
if (!MarkForSave && !_markedForDeletion)
{
return;
}
string query = "";
if (_markedForDeletion)
{
//now properly delete it
ServiceUser.Calls.Remove(this);
foreach (Worker worker in Workers)
{
worker.Calls.Remove(this);
}
Workers.Clear();
CallManager.Instance.Calls.Remove(this);
//first delete all current coverage
query = "DELETE FROM tbl_Worker_Calls WHERE idCall = " + _id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
//now delete the call
query = "DELETE FROM tbl_Calls WHERE id = " + _id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
//insert change
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_Calls", _id, this, ChangeTracker.ChangeType.Delete);
return;
}
//save it here!
bool isInsert = _id == 0;
try
{
//do the save here to the database.
if (isInsert)
{
query = "INSERT INTO tbl_Calls(idServiceUser, timeofCall, duration, required_workers, flagcode, notes, traveltime)";
query += " VALUES (@0, @1, @2, @3, @4, @5, @6)";
object[] prms = new object [] { ServiceUser.id,
time,
duration_mins,
required_workers,
((int)flag),
notes,
traveltime_mins };
DataTable dt = Common.Database.ExecuteDatabaseQuery(query, prms, true);
if (dt.Rows.Count == 0)
{
throw new Exception("Failed To Insert Call");
}
_id = Convert.ToInt32(dt.Rows[0][0]);
//insert change
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_Calls", _id, this, ChangeTracker.ChangeType.Insert);
ConstructOld();
}
else
{
query = "UPDATE tbl_Calls SET";
query += " timeofCall= @0";
query += ",duration= @1";
query += ",required_workers = @2";
query += ",flagcode = @3";
query += ",notes = @4";
query += ",traveltime= @5";
query += " WHERE id = @6";
object[] prms = new object[] { time,
duration_mins,
required_workers,
((int)flag),
notes,
traveltime_mins,
_id };
Common.Database.ExecuteDatabaseStatement(query, prms);
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_Calls", _id, this, ChangeTracker.ChangeType.Update);
//update the olds!
this.Old["id"] = _id;
this.Old["idServiceUser"] = idServiceUser;
this.Old["duration_mins"] = duration_mins;
this.Old["required_workers"] = required_workers;
this.Old["time"] = time;
this.Old["flagcode"] = flag;
this.Old["notes"] = notes;
this.Old["traveltime_mins"] = traveltime_mins;
this.OldWorkers.Clear();
this.OldWorkers.AddRange(Workers);
}
}
catch (Exception ex)
{
throw new Exception("Call Save Failed", ex);
}
//Now do the worker cover saves
try
{
//do the save here to the database.
query = "DELETE FROM tbl_Worker_Calls";
query += " WHERE idCall = " + _id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
foreach (Worker worker in Workers)
{
query = "INSERT INTO tbl_Worker_Calls(idCall, idWorker)";
query += " VALUES (@0, @1)";
Common.Database.ExecuteDatabaseStatement(query, new object[] { _id, worker.id });
}
}
catch (Exception ex)
{
throw new Exception("Failed to Save All The Worker Calls", ex);
}
MarkForSave = false;
}
/// <summary>
/// Save this worker to the database.
/// If this is the first save then id will be given a value.
/// </summary>
public void Save()
{
if (!MarkForSave)
{
return;
}
bool isInsert = id == 0;
try
{
//do the save here to the database.
string query = "";
if (isInsert)
{
query = "INSERT INTO tbl_ServiceUser(Firstname, Surname, idStatus, PNumber, dob, postcode, add1, add2, Notes, contactno_primary, " +
"contactno_secondary, idGender, location_longitude, location_latitude, medicalinfo, serviceperiodstart, serviceperiodweeks, isNotesImportant)";
query += " VALUES(@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, @17)";
object[] prms = new object[] { firstname, surname,
Convert.ToInt32(idStatus), PNumber,
dob_Clean,
postcode, add1, add2,
notes,
contactno_primary, contactno_secondary,
Convert.ToInt32(idGender),
LongLatCoords.Longatude, LongLatCoords.Latatude,
medicalinfo,
ServiceBegins_Clean, PeriodWeekCount,
isNotesImportant };
DataTable dt = Common.Database.ExecuteDatabaseQuery(query, prms, true);
if (dt.Rows.Count == 0)
{
throw new Exception("Failed To Insert Service User");
}
id = Convert.ToInt32(dt.Rows[0][0]);
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_ServiceUser", id, this, ChangeTracker.ChangeType.Insert);
ConstructOld();
}
else
{
query += "UPDATE tbl_ServiceUser SET";
query += " Firstname = @0";
query += ",Surname= @1";
query += ",idStatus= @2";
query += ",PNumber = @3";
query += ",dob = @4";
query += ",postcode = @5";
query += ",add1 = @6";
query += ",add2 = @7";
query += ",Notes = @8";
query += ",contactno_primary = @9";
query += ",contactno_secondary = @10";
query += ",idGender= @11";
query += ",location_longitude = @12";
query += ",location_latitude = @13";
query += ",medicalinfo = @14";
query += ",serviceperiodstart = @15";
query += ",serviceperiodweeks = @16";
query += ",isNotesImportant = @17";
query += " WHERE id = @18";
object[] prms = new object[] { firstname, surname,
Convert.ToInt32(idStatus), PNumber,
dob_Clean,
postcode, add1, add2,
notes,
contactno_primary, contactno_secondary,
Convert.ToInt32(idGender),
LongLatCoords.Longatude, LongLatCoords.Latatude,
medicalinfo,
ServiceBegins_Clean, PeriodWeekCount,
isNotesImportant,
id };
Common.Database.ExecuteDatabaseStatement(query, prms);
if (idStatus == Status.Deleted)
{
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_ServiceUser", id, this, ChangeTracker.ChangeType.Delete);
}
else
{
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_ServiceUser", id, this, ChangeTracker.ChangeType.Update);
}
this.Old["id"] = id;
this.Old["pnumber"] = PNumber;
this.Old["firstname"] = firstname;
this.Old["surname"] = surname;
this.Old["idstatus"] = idStatus;
this.Old["idgender"] = idGender;
this.Old["dob"] = dob;
this.Old["contactno_primary"] = contactno_primary;
this.Old["contactno_secondary"] = contactno_secondary;
this.Old["postcode"] = postcode;
this.Old["add1"] = add1;
this.Old["add2"] = add2;
this.Old["medicalinfo"] = medicalinfo;
this.Old["notes"] = notes;
this.Old["servicebegins"] = ServiceBegins;
this.Old["periodweekcount"] = PeriodWeekCount;
this.Old["LongLatCoords"] = LongLatCoords;
this.OldKeyWorkers.Clear();
this.OldKeyWorkers.AddRange(KeyWorkers);
}
//Now do the key workers
query = "DELETE FROM tbl_KeyWorkers WHERE idServiceUser = "******"INSERT INTO tbl_KeyWorkers(idServiceUser, idWorker)";
query += "VALUES (@0, @1)";
Common.Database.ExecuteDatabaseStatement(query, new object[] { id, worker.id });
}
}
catch (Exception ex)
{
throw new Exception("Service User Save Failed", ex);
}
MarkForSave = false;
}
/// <summary>
/// Save this worker to the database.
/// If this is the first save then id will be given a value.
/// </summary>
public void Save()
{
if (!MarkForSave)
{
return;
}
bool isInsert = id == 0;
try
{
//do the save here to the database.
string query = "";
if (isInsert)
{
query = "INSERT INTO tbl_Worker(Firstname, Surname, idStatus, contactno_primary, contactno_secondary, postcode, add1, add2, notes, isDriver, location_longitude, location_latitude, ENumber)";
query += " VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12)";
object[] prms = new object[] {
firstname, surname,
Convert.ToInt32(idStatus),
contactno_primary, contactno_secondary,
postcode, add1, add2, notes, isDriver,
LongLatCoords.Longatude, LongLatCoords.Latatude,
ENumber
};
DataTable dt = Common.Database.ExecuteDatabaseQuery(query, prms, true);
if (dt.Rows.Count == 0)
{
throw new Exception("Failed To Insert Worker");
}
id = Convert.ToInt32(dt.Rows[0][0]);
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_Worker", id, this, ChangeTracker.ChangeType.Insert);
ConstructOld();
}
else
{
query += "UPDATE tbl_Worker SET";
query += " Firstname = @0";
query += ",Surname= @1";
query += ",idStatus= @2";
query += ",contactno_primary = @3";
query += ",contactno_secondary= @4";
query += ",postcode = @5";
query += ",add1 = @6";
query += ",add2 = @7";
query += ",notes = @8";
query += ",isDriver = @9";
query += ",location_longitude = @10";
query += ",location_latitude = @11";
query += ",Enumber = @12";
query += " WHERE id = @13";
object[] prms = new object[] {
firstname, surname,
Convert.ToInt32(idStatus),
contactno_primary, contactno_secondary,
postcode, add1, add2, notes, isDriver,
LongLatCoords.Longatude, LongLatCoords.Latatude,
ENumber, id
};
Common.Database.ExecuteDatabaseStatement(query, prms);
if (idStatus == Status.Deleted)
{
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_Worker", id, this, ChangeTracker.ChangeType.Delete);
}
else
{
ChangeTracker.InsertChange(System.Environment.UserName, DateTime.Now, "tbl_Worker", id, this, ChangeTracker.ChangeType.Update);
}
this.Old["id"] = id;
this.Old["ENumber"] = ENumber;
this.Old["firstname"] = firstname;
this.Old["surname"] = surname;
this.Old["idStatus"] = idStatus;
this.Old["contactno_primary"] = contactno_primary;
this.Old["contactno_secondary"] = contactno_secondary;
this.Old["postcode"] = postcode;
this.Old["add1"] = add1;
this.Old["add2"] = add2;
this.Old["isDriver"] = isDriver;
this.Old["notes"] = notes;
this.Old["LongLatCoords"] = LongLatCoords;
this.OldKeyServiceUsers.Clear();
this.OldKeyServiceUsers.AddRange(KeyServiceUsers);
}
query = "DELETE FROM tbl_KeyWorkers WHERE idWorker = " + id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
foreach (ServiceUser serviceuser in KeyServiceUsers)
{
query = "INSERT INTO tbl_KeyWorkers(idServiceUser, idWorker)";
query += "VALUES (@0, @1)";
Common.Database.ExecuteDatabaseStatement(query, new object[] { serviceuser.id, id });
}
//now save the availability
query = "DELETE FROM tbl_Worker_Availability";
query += " WHERE idWorker = " + id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
foreach (Availability avail in _WorkingHours)
{
query = "INSERT INTO tbl_Worker_Availability(idWorker, dayno, timefrom, timeto)";
query += " VALUES (@0, @1, @2, @3)";
Common.Database.ExecuteDatabaseStatement(query, new object[] { id, ((int)avail.day), avail.timeFrom.Hours.ToString().PadLeft(2, '0') + ":" + avail.timeFrom.Minutes.ToString().PadLeft(2, '0'), avail.timeTo.Hours.ToString().PadLeft(2, '0') + ":" + avail.timeTo.Minutes.ToString().PadLeft(2, '0') });
}
query = "DELETE FROM tbl_Worker_DaysOff";
query += " WHERE idWorker = " + id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
foreach (AvailabilityCollection.DayOff dayoff in _WorkingHours.daysOff)
{
query = "INSERT INTO tbl_Worker_DaysOff(idWorker, rotaweek, dayofweek)";
query += " VALUES (@0, @1, @2)";
Common.Database.ExecuteDatabaseStatement(query, new object[] { id, dayoff.week, ((int)dayoff.day) });
}
//now save the holidays
query = "DELETE FROM tbl_Worker_Holidays";
query += " WHERE idWorker = " + id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
foreach (Absence absence in _holidays)
{
query = "INSERT INTO tbl_Worker_Holidays(idWorker, dateHoliday, duration_mins)";
query += " VALUES (@0, @1, @2)";
Common.Database.ExecuteDatabaseStatement(query, new object[] { id, absence.DateStart.ToString("yyyy-MM-dd HH:mm"), absence.Duration });
}
//now save the sickdays
query = "DELETE FROM tbl_Worker_Sickness";
query += " WHERE idWorker = " + id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
foreach (Absence absence in _sickdays)
{
query = "INSERT INTO tbl_Worker_Sickness(idWorker, dateSick, duration_mins)";
query += " VALUES (@0, @1, @2)";
Common.Database.ExecuteDatabaseStatement(query, new object[] { id, absence.DateStart.ToString("yyyy-MM-dd HH:mm"), absence.Duration });
}
//now save the training
query = "DELETE FROM tbl_Worker_Training";
query += " WHERE idWorker = " + id.ToString();
Common.Database.ExecuteDatabaseStatement(query);
foreach (Absence absence in _training)
{
query = "INSERT INTO tbl_Worker_Training(idWorker, dateTraining, duration_mins)";
query += " VALUES (@0, @1, @2)";
Common.Database.ExecuteDatabaseStatement(query, new object[] { id, absence.DateStart.ToString("yyyy-MM-dd HH:mm"), absence.Duration });
}
}
catch (Exception ex)
{
throw new Exception("Worker Save Failed", ex);
}
MarkForSave = false;
}