public DataTable SearchSchedule(string GroupID)
{
string query = string.Format("SELECT * FROM [dbo].[Schedule ] WHERE GroupID like '%{0}%' ", GroupID);
DataTable data = DataProvider.GetInstance().ExcuteQuery(query);
return(data);
}
public DataTable GetListSlot()
{
string query = "SELECT * FROM [dbo].[Slot]";
DataTable data = DataProvider.GetInstance().ExcuteQuery(query);
return(data);
}
public bool DeleteUserSchedule(string GroupID, string index)
{
int result = DataProvider.GetInstance().ExcuteNonQuery("exec USP_DeleteScheduleTeacherAndStudent " +
" @idgroup , @indexSchedule ", new object[] { GroupID, index });
return(result > 0);
}
public DataTable GetCourseTypeNameByCourseType(int id)
{
string query = "SELECT ct.CourseTypeName FROM [dbo].[Course] as c , [dbo].[CourseType] as ct WHERE c.CourseType = " + id;
DataTable data = DataProvider.GetInstance().ExcuteQuery(query);
return(data);
}
public DataTable SearchCourseName(string name)
{
string query = string.Format("SELECT * FROM [dbo].[Course] WHERE CourseName like '%{0}%' ", name);
DataTable data = DataProvider.GetInstance().ExcuteQuery(query);
return(data);
}
public DataTable SearchTeacher(string name)
{
string query = string.Format("SELECT * From dbo.[User] where Name like '%'+'{0}'+'%' and Role = 'TEACHER' ", name);
DataTable data = DataProvider.GetInstance().ExcuteQuery(query);
return(data);
}
public DataTable SearchScheduleByDate(DateTime Fromdate, DateTime toDate)
{
//DataTable data;
//string dateString = date.ToString().Split(' ')[0];
//string query = String.Format("SELECT * FROM [dbo].[Schedule ] WHERE [Date] = '{0}' ",dateString);
//data = DataProvider.GetInstance().ExcuteQuery(query);
return(DataProvider.GetInstance().ExcuteQuery("exec USP_GetScheduleByDate @fromDate , @toDate ", new object[] { Fromdate, toDate }));
}
public bool DeleteSchedule(string index)
{
int result = 0;
string query = "DELETE [dbo].[Schedule ] WHERE [Index] = " + index;
result = DataProvider.GetInstance().ExcuteNonQuery(query);
return(result > 0);
}
public bool DeleteCourse(string ID)
{
int result = 0;
string query = "DELETE [dbo].Course WHERE CourseID = @id ";
result = DataProvider.GetInstance().ExcuteNonQuery(query, new object[] { ID });
return(result > 0);
}
public string GetCourseTypeNameByCourseTypeID(string idtype)
{
string query = "SELECT * FROM [dbo].[CourseType] WHERE CourseTypeID = @idtype ";
DataTable data = DataProvider.GetInstance().ExcuteQuery(query, new object[] { idtype });
foreach (DataRow item in data.Rows)
{
CourseType t = new CourseType(item);
return(t.CourseTypeName);
}
return(null);
}
public string GetCourseIDByCourseName(string name)
{
string query = "SELECT * FROM [dbo].[Course] WHERE CourseName = @name ";
DataTable data = DataProvider.GetInstance().ExcuteQuery(query, new object[] { name });
foreach (DataRow item in data.Rows)
{
Course t = new Course(item);
return(t.CourseID);
}
return(null);
}
public List <CourseType> loadCourseType()
{
List <CourseType> listCourseType = new List <CourseType>();
string query = " SELECT * FROM [dbo].[CourseType] ";
DataTable data = DataProvider.GetInstance().ExcuteQuery(query);
foreach (DataRow item in data.Rows)
{
CourseType ctype = new CourseType(item);
listCourseType.Add(ctype);
}
return(listCourseType);
}
public List <Course> GetListCourse()
{
List <Course> courses = new List <Course>();
string query = "SELECT * FROM [dbo].[Course] ";
DataTable data = DataProvider.GetInstance().ExcuteQuery(query);
foreach (DataRow item in data.Rows)
{
Course c = new Course(item);
courses.Add(c);
}
return(courses);
}
public bool EditCoursẹ(string ID, string Name, string type)
{
int result = 0;
string query = "UPDATE [dbo].Course SET CourseType = @type , CourseName = @name WHERE CourseID = @id ";
try
{
result = DataProvider.GetInstance().ExcuteNonQuery(query, new object[] { type, Name, ID });
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
return(result > 0);
}
public bool InsertCourse(string ID, string Name, string type)
{
int result = 0;
string query = string.Format("INSERT [dbo].[Course] VALUES ('{0}', '{1}' , '{2}' )", ID, Name, type);
try
{
result = DataProvider.GetInstance().ExcuteNonQuery(query);
}
catch (Exception)
{
throw new Exception("Duplicate data");
}
return(result > 0);
}
public bool AddSchedule(string date, string slotID, string GroupID, string CourseID, string UserNameBooker)
{
int result = 0;
string query = string.Format("INSERT [dbo].[Schedule ] VALUES ( '{0}','{1}','{2}','{3}','{4}', GETDATE() )", date, slotID,
GroupID, CourseID, UserNameBooker);
try
{
result = DataProvider.GetInstance().ExcuteNonQuery(query);
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
return(result > 0);
}
public bool EditSchedule(string date, string slotID, string GroupID, string CourseID, string UserNameBooker, string index)
{
int result = 0;
string query = string.Format("UPDATE [dbo].[Schedule ] SET SlotID = '{1}' ,CourseID = '{3}' , GroupID = '{2}' , [Date] = '{0}' , DateCreated = GETDATE() , UserNameBooker = '{4}' WHERE [Index] = '{5}'", date, slotID,
GroupID, CourseID, UserNameBooker, index);
try
{
result = DataProvider.GetInstance().ExcuteNonQuery(query);
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
return(result > 0);
}
public void InsertCourseTypeID(int id, string CoureTypeName)
{
DataProvider.GetInstance().ExcuteNonQuery("INSERT [dbo].CourseType VALUES ( '@CourseTypeID' , '@CourseTypeName' )", new object[] { id, CoureTypeName });
}
public bool AddUserSchedule(string groupId)
{
int result = DataProvider.GetInstance().ExcuteNonQuery("exec USP_AddUserSchedule @idgroup ", new object[] { groupId });
return(result > 0);
}
public bool AddTeacherSchedule(string username, string index)
{
int result = DataProvider.GetInstance().ExcuteNonQuery(" exec USP_AddTeacher @username , @index ", new object[] { username, index });
return(result > 0);
}
