public TutoringTime GetExactTutoringTime(TutoringTime tt)
{
string sqlDate = tt.Date.ToString("yyyy/MM/dd");
string w = "date = '" + sqlDate + "' AND time = '" + tt.Time + "' AND teacherId = '" + tt.Teacher.Id + "'";
return SingleWhere(w, true);
}
public int CreateTutoringTime(TutoringTime tt)
{
try
{
cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO TutoringTime(date, time, teacherId) VALUES(@date, @time, @teacherId)";
cmd.Parameters.AddWithValue("date", tt.Date);
cmd.Parameters.AddWithValue("time", tt.Time);
cmd.Parameters.AddWithValue("teacherId", tt.Teacher.Id);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cmd.Connection.Close();
}
return result;
}
public int RegisterBooking(int ttId, int childId)
{
TutoringTime tt = new TutoringTime();
tt.Id = ttId;
tt.Child = new Person(childId);
ttDb = new TutoringTimeDb();
return ttDb.RegisterBooking(tt);
}
public int RemoveTutoringTime(DateTime date, string time, int teacherId)
{
TutoringTime tt = new TutoringTime();
tt.Date = date;
tt.Time = time;
tt.Teacher = new Person(teacherId);
ttDb = new TutoringTimeDb();
return ttDb.RemoveTutoringTime(tt);
}
public TutoringTime GetExactTutoringTime(DateTime date, string time, int teacherId)
{
ttDb = new TutoringTimeDb();
TutoringTime tt = new TutoringTime();
tt.Date = date;
tt.Time = time;
tt.Teacher = new Person(teacherId);
return ttDb.GetExactTutoringTime(tt);
}
public int CreateTutoringTime(DateTime date, int teacherId, string time)
{
TutoringTime tt = new TutoringTime();
tt.Date = date;
tt.Teacher = new Teacher(teacherId);
tt.Time = time;
TutoringTimeDb ttDb = new TutoringTimeDb();
return ttDb.CreateTutoringTime(tt);
}
public int RegisterBooking(TutoringTime tt)
{
dbCon = new DbConnection();
using (dbCon.GetConnection())
{
dbCon.GetConnection().Open();
SqlTransaction sqlTransaction = dbCon.GetConnection().BeginTransaction(IsolationLevel.Serializable);
cmd = dbCon.GetConnection().CreateCommand();
cmd.Transaction = sqlTransaction;
try
{
if (GetTutoringTime(tt.Id).Child == null)
{
cmd.CommandText = "UPDATE TutoringTime SET childId=(@childId) WHERE ttid=(@tutoringTimeId)";
cmd.Parameters.AddWithValue("childId", tt.Child.Id);
cmd.Parameters.AddWithValue("tutoringTimeId", tt.Id);
cmd.CommandType = CommandType.Text;
result = cmd.ExecuteNonQuery();
if (result == 1)
{
sqlTransaction.Commit();
}
}
}
catch (Exception)
{
try
{
sqlTransaction.Rollback();
}
catch (Exception)
{
throw;
}
}
finally
{
dbCon.GetConnection().Close();
}
}
return result;
}
//Gets all the TutoringTime objects that has childId = null (which means that it
//is available to book)
public List<TutoringTime> GetAllAvailableTutoringTimes()
{
List<TutoringTime> ttTimes = new List<TutoringTime>();
try
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM TutoringTime WHERE childId is null";
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
TutoringTime tt = new TutoringTime();
tt.Id = Convert.ToInt32(dr["tid"]);
tt.Time = Convert.ToString(dr["time"]);
tt.Date = Convert.ToDateTime(dr["date"]);
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dr["teacherId"]);
tt.Teacher = teacher;
ttTimes.Add(tt);
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return ttTimes;
}
private TutoringTime BuildTutoringTime(SqlDataReader dr)
{
TutoringTime tt = null;
try
{
tt = new TutoringTime();
tt.Id = Convert.ToInt32(dr["ttid"]);
tt.Date = Convert.ToDateTime(dr["date"]);
tt.Time = dr["time"].ToString();
tt.Teacher = new Person(Convert.ToInt32(dr["teacherId"]));
if (dr["childId"] != DBNull.Value)
{
tt.Child = new Person(Convert.ToInt32(dr["childId"]));
}
}
catch
{
throw;
}
return tt;
}
public int RemoveTutoringTime(TutoringTime tt)
{
string sqlDate = tt.Date.ToString("yyyy/MM/dd");
try
{
cmd = new SqlCommand();
cmd.CommandText = "DELETE FROM TutoringTime WHERE date =(@date) AND time = (@time) AND teacherId = (@teacherId)";
cmd.Parameters.AddWithValue("date", sqlDate);
cmd.Parameters.AddWithValue("time", tt.Time);
cmd.Parameters.AddWithValue("teacherId", tt.Teacher.Id);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cmd.Connection.Close();
}
return result;
}
public List<TutoringTime> GetTtByDate(DateTime date)
{
List<TutoringTime> ttTimes = new List<TutoringTime>();
string testDate = date.ToString("yyyy/MM/dd");
try
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM TutoringTime WHERE date = '" + testDate + "'";
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
TutoringTime tt = new TutoringTime();
tt.Id = Convert.ToInt32(dr["tid"]);
tt.Time = Convert.ToString(dr["time"]);
tt.Date = Convert.ToDateTime(dr["date"]);
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dr["teacherId"]);
tt.Teacher = teacher;
ttTimes.Add(tt);
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return ttTimes;
}
public int RegisterBooking(TutoringTime tt)
{
SqlTransaction sqlTrans = null;
try
{
comm = new SqlCommand();
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
sqlTrans = comm.Connection.BeginTransaction(IsolationLevel.Serializable);
using (comm)
{
comm.CommandText = "UPDATE TutoringTime set childId=(@childId) WHERE tid =(@tutoringTimeId)";
comm.Parameters.AddWithValue("childId", tt.Child.Id);
comm.Parameters.AddWithValue("tutoringTimeId", tt.Id);
comm.CommandType = CommandType.Text;
comm.Transaction = sqlTrans;
result = comm.ExecuteNonQuery();
if (result == 1)
{
sqlTrans.Commit();
}
}
}
catch (Exception)
{
sqlTrans.Rollback();
throw;
}
finally
{
comm.Connection.Close();
}
return result;
}
public TutoringTime GetTtTimesByTime(DateTime date, string time, int teacherId)
{
try
{
comm = new SqlCommand();
string testDate = date.ToString("yyyy/MM/dd");
comm.CommandText = "SELECT * FROM TutoringTime WHERE date = '" + testDate + "'" + "AND time= '" + time + "'" + "AND teacherId= '" + teacherId + "'";
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
TutoringTime tt = new TutoringTime();
tt.Id = Convert.ToInt32(dr["tid"]);
tt.Time = Convert.ToString(dr["time"]);
tt.Date = Convert.ToDateTime(dr["date"]);
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dr["teacherId"]);
tt.Teacher = teacher;
return tt;
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return null;
}
public TutoringTime GetTtByTtId(int ttId)
{
SqlTransaction sqlTrans = null;
try
{
comm = new SqlCommand();
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
sqlTrans = comm.Connection.BeginTransaction(IsolationLevel.Serializable);
using (comm)
{
comm.CommandText = "SELECT * FROM TutoringTime WHERE tid = '" + ttId + "'";
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
TutoringTime tt = new TutoringTime();
while (dr.Read())
{
tt.Id = Convert.ToInt32(dr["tid"]);
}
return tt;
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
}
public int RegisterBooking(int childId, int tutoringTimeId)
{
TutoringTime tt = new TutoringTime();
tt.Id = tutoringTimeId;
tt.Child = new Child(childId);
TutoringTimeDb ttDb = new TutoringTimeDb();
return ttDb.RegisterBooking(tt);
}
public TutoringTime GetTtTimesByTime(DateTime date, string time, int teacherId)
{
TutoringTimeDb ttDb = new TutoringTimeDb();
//Constructs and adds complete Teacher object to TutoringTime
//Returns improved object
PersonCtrl pCtrl = new PersonCtrl();
TutoringTime tt = ttDb.GetTtTimesByTime(date, time, teacherId);
TutoringTime completeTt = new TutoringTime();
completeTt = tt;
if (tt != null)
{
completeTt.Teacher = (Teacher)pCtrl.GetPerson(teacherId);
}
return completeTt;
}
