public DataTable checkTimeSlot(timeSlotList list)
{
try
{
bo = new ClassBo();
dt = new DataTable();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.checkTimeSlot, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(bo.P_id, list.id);
cmd.Parameters.AddWithValue(bo.P_day, list.day);
cmd.Parameters.AddWithValue(bo.P_timeFrom, list.timeFrom);
cmd.Parameters.AddWithValue(bo.P_timeTo, list.timeTo);
con.Open();
da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
return(dt);
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
public List <personList> getPerson(int id, string code)
{
bo = new ClassBo();
dt = new DataTable();
dt = da.getPerson(id, code);
List <personList> list = new List <personList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
personList itlist = new personList();
itlist.id = dr[bo.id].ToString();
itlist.code = dr[bo.code].ToString();
itlist.commonName = dr[bo.commonName].ToString();
itlist.givenName = dr[bo.givenName].ToString();
itlist.middleName = dr[bo.middleName].ToString();
itlist.contactNumber = dr[bo.contactNumber].ToString();
itlist.email = dr[bo.email].ToString();
itlist.status = dr[bo.status].ToString() == "I" ? "Inactive" : "Active";
itlist.gender = dr[bo.gender].ToString();
itlist.address1 = dr[bo.address1].ToString();
itlist.address2 = dr[bo.address2].ToString();
itlist.city = dr[bo.city].ToString();
itlist.country = dr[bo.country].ToString();
itlist.zip = dr[bo.zip].ToString();
itlist.dob = dr[bo.Dob].ToString().Length > 0 ? DateTime.Parse(dr[bo.Dob].ToString()).ToString("MM/dd/yyyy") : null;
itlist.CountryCode = dr[bo.CountryCode].ToString();
list.Add(itlist);
}
}
return(list);
}
public List <lecturerList> getLecturer(int id)
{
bo = new ClassBo();
dt = new DataTable();
dt = da.getLecturer(id);
List <lecturerList> list = new List <lecturerList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
lecturerList itlist = new lecturerList();
itlist.id = dr[bo.id].ToString();
itlist.universityID = dr[bo.universityID].ToString();
itlist.personalID = dr[bo.personalID].ToString();
itlist.personalCode = dr[bo.code].ToString();
itlist.position = dr[bo.position].ToString();
itlist.department = dr[bo.department].ToString();
itlist.description = dr[bo.description].ToString();
itlist.maximumLecturePerDay = dr[bo.maximumLecturePerDay].ToString();
itlist.maxLectureHoursPerDay = dr[bo.maxLectureHoursPerDay].ToString();
itlist.maximumLecturePerCycle = dr[bo.maximumLecturePerCycle].ToString();
itlist.qualification = dr[bo.qualification].ToString();
itlist.status = dr[bo.status].ToString() == "I" ? "Inactive" : "Active";
list.Add(itlist);
}
}
return(list);
}
public int saveLecturer(lecturerList list)
{
int rowCount = 0;
try
{
bo = new ClassBo();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.insLecturer, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(bo.P_id, Convert.ToInt32(list.id));
cmd.Parameters.AddWithValue(bo.P_universityID, list.universityID);
cmd.Parameters.AddWithValue(bo.P_personalID, list.personalID);
cmd.Parameters.AddWithValue(bo.P_position, list.position);
cmd.Parameters.AddWithValue(bo.P_department, Convert.ToInt32(list.department));
cmd.Parameters.AddWithValue(bo.P_description, list.description);
cmd.Parameters.AddWithValue(bo.P_maximumLecturePerDay, Convert.ToInt32(list.maximumLecturePerDay));
cmd.Parameters.AddWithValue(bo.P_maxLectureHoursPerDay, float.Parse(list.maxLectureHoursPerDay));
cmd.Parameters.AddWithValue(bo.P_maximumLecturePerCycle, Convert.ToInt32(list.maximumLecturePerCycle));
cmd.Parameters.AddWithValue(bo.P_qualification, list.qualification);
cmd.Parameters.AddWithValue(bo.P_status, list.status);
cmd.Parameters.AddWithValue(bo.P_By, 1);
con.Open();
rowCount = cmd.ExecuteNonQuery();
con.Close();
return(rowCount);
}
catch (Exception ex)
{
con.Close();
return(rowCount);
}
}
public int saveTimeSlot(timeSlotList list)
{
int rowCount = 0;
try
{
bo = new ClassBo();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.insTimeSlot, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(bo.P_id, Convert.ToInt32(list.id));
cmd.Parameters.AddWithValue(bo.P_lecturerToCoursesdId, list.lecturerToCoursesdId);
cmd.Parameters.AddWithValue(bo.P_timeFrom, list.timeFrom);
cmd.Parameters.AddWithValue(bo.P_timeTo, list.timeTo);
cmd.Parameters.AddWithValue(bo.P_room, list.room);
cmd.Parameters.AddWithValue(bo.P_day, list.day);
cmd.Parameters.AddWithValue(bo.status, list.status);
cmd.Parameters.AddWithValue(bo.P_By, 1);
con.Open();
rowCount = cmd.ExecuteNonQuery();
con.Close();
return(rowCount);
}
catch (Exception ex)
{
con.Close();
return(rowCount);
}
}
public int saveCourses(coursesList list)
{
int rowCount = 0;
try
{
bo = new ClassBo();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.insCourses, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(bo.P_id, Convert.ToInt32(list.id));
cmd.Parameters.AddWithValue(bo.P_courseCode, list.courseCode);
cmd.Parameters.AddWithValue(bo.P_name, list.name);
cmd.Parameters.AddWithValue(bo.P_types, list.types);
cmd.Parameters.AddWithValue(bo.P_ects, float.Parse(list.ects));
cmd.Parameters.AddWithValue(bo.P_description, list.description);
cmd.Parameters.AddWithValue(bo.P_labFlag, list.labFlag);
cmd.Parameters.AddWithValue(bo.P_maximumHoursPerCycle, float.Parse(list.maximumHoursPerCycle));
cmd.Parameters.AddWithValue(bo.P_maximumDaysPerCycle, Convert.ToInt32(list.maximumDaysPerCycle));
cmd.Parameters.AddWithValue(bo.P_maximumEnrollment, Convert.ToInt32(list.maximumEnrollment));
cmd.Parameters.AddWithValue(bo.P_maximumHoursPerDay, float.Parse(list.maximumHoursPerDay));
if (list.startingDate == null)
{
cmd.Parameters.AddWithValue(bo.P_startingDate, DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue(bo.P_startingDate, list.startingDate);
}
if (list.endingDate == null)
{
cmd.Parameters.AddWithValue(bo.P_endingDate, DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue(bo.P_endingDate, list.endingDate);
}
cmd.Parameters.AddWithValue(bo.P_By, 1);
cmd.Parameters.AddWithValue(bo.P_lcid, Convert.ToInt32(list.lcid));
cmd.Parameters.AddWithValue(bo.P_coursesID, list.coursesID);
cmd.Parameters.AddWithValue(bo.P_lectureID, list.lectureID);
cmd.Parameters.AddWithValue(bo.P_colorCode, list.colorCode);
cmd.Parameters.AddWithValue(bo.P_lcstatus, list.lcstatus);
cmd.Parameters.AddWithValue("@department", Convert.ToInt32(list.department));
cmd.Parameters.AddWithValue(bo.status, list.status);
con.Open();
rowCount = cmd.ExecuteNonQuery();
con.Close();
return(rowCount);
}
catch (Exception ex)
{
con.Close();
return(rowCount);
}
}
public List <timeTableList> getTimeTable()
{
bo = new ClassBo();
dt = new DataTable();
dt = da.getTimeTable();
List <timeTableList> list = new List <timeTableList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
timeTableList itlist = new timeTableList();
itlist.timeFrom = dr[bo.timeFrom].ToString().Substring(0, 5);
itlist.timeTo = dr[bo.timeTo].ToString().Substring(0, 5);
itlist.room = dr[bo.room].ToString();
itlist.Day = dr[bo.day].ToString();
switch (dr[bo.day].ToString())
{
case "1":
itlist.dayName = "Monday";
break;
case "2":
itlist.dayName = "Tuesday";
break;
case "3":
itlist.dayName = "Wednesday";
break;
case "4":
itlist.dayName = "Thursday";
break;
case "5":
itlist.dayName = "Friday";
break;
default:
break;
}
itlist.colorCode = dr[bo.colorCode].ToString();
itlist.courseCode = dr[bo.courseCode].ToString();
itlist.coursesName = dr[bo.name].ToString();
itlist.ects = dr[bo.ects].ToString();
itlist.givenName = dr[bo.givenName].ToString();
itlist.commonName = dr[bo.commonName].ToString();
itlist.middleName = dr[bo.middleName].ToString();
itlist.contactNumber = dr[bo.contactNumber].ToString();
itlist.email = dr[bo.email].ToString();
list.Add(itlist);
}
}
return(list);
}
public List <timeSlotList> getTimeSlot(int id)
{
bl = new ClassBl();
bo = new ClassBo();
dt = new DataTable();
dt = da.getTimeSlot(id);
List <timeSlotList> list = new List <timeSlotList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
timeSlotList itlist = new timeSlotList();
itlist.id = dr[bo.id].ToString();
itlist.lecturerToCoursesdId = dr[bo.lecturerToCoursesdId].ToString();
itlist.day = dr[bo.day].ToString();
itlist.timeFrom = dr[bo.timeFrom].ToString();
itlist.timeTo = dr[bo.timeTo].ToString();
itlist.room = dr[bo.room].ToString();
itlist.coursesID = dr[bo.coursesID].ToString();
itlist.coursesName = dr[bo.name].ToString();
itlist.commonName = dr[bo.commonName].ToString();
itlist.status = dr[bo.status].ToString() == "I" ? "Inactive" : "Active";
switch (dr[bo.day].ToString())
{
case "1":
itlist.dayName = "Monday";
break;
case "2":
itlist.dayName = "Tuesday";
break;
case "3":
itlist.dayName = "Wednesday";
break;
case "4":
itlist.dayName = "Thursday";
break;
case "5":
itlist.dayName = "Friday";
break;
default:
break;
}
list.Add(itlist);
}
}
return(list);
}
public int savePerson(personList list)
{
int rowCount = 0;
try
{
bo = new ClassBo();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.insPerson, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(bo.P_id, Convert.ToInt32(list.id));
cmd.Parameters.AddWithValue(bo.P_code, list.code);
cmd.Parameters.AddWithValue(bo.P_commonName, list.commonName);
cmd.Parameters.AddWithValue(bo.P_givenName, list.givenName);
cmd.Parameters.AddWithValue(bo.P_middleName, list.middleName);
cmd.Parameters.AddWithValue(bo.P_gender, list.gender);
if (list.dob == null)
{
cmd.Parameters.AddWithValue(bo.P_dob, DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue(bo.P_dob, list.dob);
}
cmd.Parameters.AddWithValue(bo.P_address1, list.address1);
cmd.Parameters.AddWithValue(bo.P_address2, list.address2);
cmd.Parameters.AddWithValue(bo.P_city, list.city);
cmd.Parameters.AddWithValue(bo.P_country, list.country);
cmd.Parameters.AddWithValue(bo.P_zip, list.zip);
cmd.Parameters.AddWithValue(bo.P_contactNumber, Convert.ToInt32(list.contactNumber));
cmd.Parameters.AddWithValue(bo.P_email, list.email);
cmd.Parameters.AddWithValue(bo.P_userName, list.userName);
cmd.Parameters.AddWithValue(bo.P_password, list.password);
cmd.Parameters.AddWithValue(bo.P_status, list.status);
cmd.Parameters.AddWithValue(bo.P_By, 1);
cmd.Parameters.AddWithValue(bo.P_CountryCode, Convert.ToInt32(list.CountryCode));
con.Open();
rowCount = cmd.ExecuteNonQuery();
con.Close();
return(rowCount);
}
catch (Exception ex)
{
con.Close();
return(rowCount);
}
}
public List <coursesList> selectCourse()
{
bo = new ClassBo();
dt = new DataTable();
dt = da.selectCourse();
List <coursesList> list = new List <coursesList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
coursesList itlist = new coursesList();
itlist.id = dr[bo.id].ToString();
itlist.courseCode = dr[bo.courseCode].ToString();
list.Add(itlist);
}
}
return(list);
}
public List <lecturerList> selectLecturer()
{
bo = new ClassBo();
dt = new DataTable();
dt = da.selectLecturer();
List <lecturerList> list = new List <lecturerList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
lecturerList itlist = new lecturerList();
itlist.id = dr[bo.id].ToString();
itlist.universityID = dr[bo.universityID].ToString();
itlist.personalID = dr[bo.commonName].ToString();
list.Add(itlist);
}
}
return(list);
}
public DataTable getTimeTable()
{
try
{
bo = new ClassBo();
dt = new DataTable();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.getTimeTable, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
return(dt);
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
public DataTable getTimeSlot(int id)
{
try
{
bo = new ClassBo();
dt = new DataTable();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.getTimeSlot, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(bo.P_id, id);
con.Open();
da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
return(dt);
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
public List <personList> serchPerson(string code)
{
bo = new ClassBo();
dt = new DataTable();
dt = da.serchPerson(code);
List <personList> list = new List <personList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
personList itlist = new personList();
itlist.id = dr[bo.id].ToString();
itlist.code = dr[bo.code].ToString();
itlist.commonName = dr[bo.commonName].ToString();
itlist.givenName = dr[bo.givenName].ToString();
itlist.middleName = dr[bo.middleName].ToString();
list.Add(itlist);
}
}
return(list);
}
public int deletePerson(int id)
{
int rowCount = 0;
try
{
bo = new ClassBo();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.deletePerson, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(bo.P_id, id);
con.Open();
rowCount = cmd.ExecuteNonQuery();
con.Close();
return(rowCount);
}
catch (Exception ex)
{
con.Close();
return(rowCount);
}
}
public DataTable serchPerson(string code)
{
try
{
bo = new ClassBo();
dt = new DataTable();
con.ConnectionString = connStr;
cmd = new SqlCommand(ClassBo.serchPerson, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(bo.P_code, code == null ? DBNull.Value.ToString() : code);
con.Open();
da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
return(dt);
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
public List <coursesList> getCourse(int id, string code)
{
bo = new ClassBo();
dt = new DataTable();
dt = da.getCourse(id, code);
List <coursesList> list = new List <coursesList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
coursesList itlist = new coursesList();
itlist.id = dr[bo.id].ToString();
itlist.courseCode = dr[bo.courseCode].ToString();
itlist.name = dr[bo.name].ToString();
itlist.types = dr[bo.types].ToString();
itlist.ects = dr[bo.ects].ToString();
itlist.department = dr[bo.department].ToString();
itlist.labFlag = dr[bo.labFlag].ToString();
itlist.maximumHoursPerCycle = dr[bo.maximumHoursPerCycle].ToString();
itlist.maximumDaysPerCycle = dr[bo.maximumDaysPerCycle].ToString();
itlist.maximumEnrollment = dr[bo.maximumEnrollment].ToString();
itlist.maximumHoursPerDay = dr[bo.maximumHoursPerDay].ToString();
itlist.description = dr[bo.description].ToString();
itlist.startingDate = dr[bo.startingDate].ToString().Length > 0 ? DateTime.Parse(dr[bo.startingDate].ToString()).ToString("MM/dd/yyyy") : null;
itlist.endingDate = dr[bo.endingDate].ToString().Length > 0 ? DateTime.Parse(dr[bo.endingDate].ToString()).ToString("MM/dd/yyyy") : null;
itlist.status = dr[bo.status].ToString() == "I" ? "Inactive" : "Active";
itlist.lcid = dr[bo.lcid].ToString();
itlist.coursesID = dr[bo.coursesID].ToString();
itlist.lectureID = dr[bo.lectureID].ToString();
itlist.colorCode = dr[bo.colorCode].ToString();
itlist.lcstatus = dr[bo.lcstatus].ToString() == "I" ? "Inactive" : "Active";
list.Add(itlist);
}
}
return(list);
}
public List <coursesList> selectedCourse(int id)
{
bo = new ClassBo();
dt = new DataTable();
dt = da.selectedCourse(id);
List <coursesList> list = new List <coursesList>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
coursesList itlist = new coursesList();
itlist.id = dr[bo.id].ToString();
itlist.courseCode = dr[bo.courseCode].ToString();
itlist.maximumDaysPerCycle = dr[bo.maximumDaysPerCycle].ToString();
itlist.maximumHoursPerCycle = dr[bo.maximumHoursPerCycle].ToString();
itlist.maximumHoursPerDay = dr[bo.maximumHoursPerDay].ToString();
itlist.lcid = dr[bo.lcid].ToString();
itlist.name = dr[bo.commonName].ToString();
list.Add(itlist);
}
}
return(list);
}
