public static int KiemTraTonTai(KhachHangDTO temp)
{
int kq = 0;
conn = DataProvider.OpenConnection();
string sTruyVan3 = "select * from KhachHang where tenDangNhap = '" + temp.TenDangNhap + "' ";
SqlCommand cmd3 = new SqlCommand(sTruyVan3, conn);
cmd3.Connection = conn;
cmd3.ExecuteNonQuery();
DataTable dt3 = DataProvider.GetDataTable(sTruyVan3, conn);
if (dt3.Rows.Count > 0)
{
kq = 4;
}
string sTruyVan = "select * from KhachHang where soCMND = '" + temp.SoCMND + "' ";
SqlCommand cmd = new SqlCommand(sTruyVan, conn);
cmd.ExecuteNonQuery();
DataTable dt = DataProvider.GetDataTable(sTruyVan, conn);
if (dt.Rows.Count > 0)
{
kq = 1;
}
string sTruyVan1 = "select * from KhachHang where email = '" + temp.Email + "' ";
SqlCommand cmd1 = new SqlCommand(sTruyVan1, conn);
cmd1.ExecuteNonQuery();
DataTable dt1 = DataProvider.GetDataTable(sTruyVan1, conn);
if (dt1.Rows.Count > 0)
{
kq = 2;
}
string sTruyVan2 = "select * from KhachHang where soDienThoai = '" + temp.SoDienThoai + "' ";
SqlCommand cmd2 = new SqlCommand(sTruyVan2, conn);
cmd2.Connection = conn;
cmd2.ExecuteNonQuery();
DataTable dt2 = DataProvider.GetDataTable(sTruyVan2, conn);
if (dt2.Rows.Count > 0)
{
kq = 3;
}
DataProvider.CloseConnection(conn);
return(kq);
}
public static int getSumStudent(string nameClass, string schoolYear)
{
string sCommand = @"Select IDStudent from Student_Class where nameClass ='" + nameClass + "' and schoolYear ='" + schoolYear + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
return(dt.Rows.Count);
}
public DataTable XemThongTinLichTrinhCaNhan(int maTaiXe)
{
dtp = new DataProvider();
dt = new DataTable();
string sqlSelect = string.Format(@"SELECT * FROM LichTrinh WHERE LichTrinh.TaiXe = {0}", maTaiXe);
dt = dtp.GetDataTable(sqlSelect);
return(dt);
}
// Xem điểm của học sinh
public static DataTable LoadMark(string id)
{
string sTruyVan = @"select a.NameSubject, b.Semester, b.FirstFifteenMinutes, b.SecondFifteenMinutes, b.ThirdFifteenMinutes, b.FirstFortyFiveMinutes, b.SecondFortyFiveMinutes, b.ThirdFortyFiveMinutes, b.SemesterMark, b.nameClass, b.schoolYear from Mark b join Subject a on b.IDSubject = a.IDSubject where b.IDStudent='" + id + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sTruyVan, con);
DataProvider.CloseConnection(con);
return(dt);
}
public static DataTable LoadDuLieuLoaiPhong(string s)
{
string sTruyVan = "select * from LoaiPhong where maKS = (select maKS from NhanVien where tenDangNhap= '" + s + "') ";
conn = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sTruyVan, conn);
DataProvider.CloseConnection(conn);
return(dt);
}
//xem thông tin giáo viên từ db
public static DataTable LoadInfo(string idTeacher)
{
string sCommand = @"Select * from Teacher where Teacher.IDTeacher = N'" + idTeacher + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
DataProvider.CloseConnection(con);
return(dt);
}
// Xem điểm sinh viên theo lớp, theo môn và theo năm học, theo học kỳ
public static DataTable LoadMark(string className, string nameSubject, string schoolYear, string Semester)
{
string sCommand = @"Select Student.IDStudent, Student.Name, Mark.FirstFifteenMinutes, Mark.SecondFifteenMinutes, Mark.ThirdFifteenMinutes, Mark.FirstFortyFiveMinutes, Mark.SecondFortyFiveMinutes, Mark.ThirdFortyFiveMinutes, Mark.SemesterMark
from Mark join Student on Mark.IDStudent = Student.IDStudent join Subject on Mark.IDSubject = Subject.IDSubject
where Mark.nameClass = '" + className + @"'and Mark.schoolYear = '" + schoolYear + @"'and Subject.NameSubject = N'" + nameSubject + @"'and Mark.Semester = '" + Semester + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
DataProvider.CloseConnection(con);
return(dt);
}
public static List <PeopleDTO> loadListStudent(string status)
{
string sCommand = "";
if (status == "System.Windows.Controls.ComboBoxItem: Active")
{
sCommand = @"Select* from Student where isActive = 'T'";
}
else if (status == "System.Windows.Controls.ComboBoxItem: Deactive")
{
sCommand = @"Select* from Student where isActive = 'F'";
}
else
{
sCommand = @"Select* from Student";
}
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
DataProvider.CloseConnection(con);
return(null);
}
else
{
List <PeopleDTO> result = new List <PeopleDTO>();
int n = dt.Rows.Count;
for (int i = 0; i < n; i++)
{
PeopleDTO student = new PeopleDTO();
student.Id = dt.Rows[i]["IDStudent"].ToString();
student.Name = dt.Rows[i]["Name"].ToString();
student.Gender = dt.Rows[i]["Gender"].ToString();
student.Email = dt.Rows[i]["Email"].ToString();
student.DateofBith = dt.Rows[i]["BirthDay"].ToString();
student.Password = dt.Rows[i]["Password"].ToString();
student.Phone = dt.Rows[i]["Phone"].ToString();
student.Type = "Student";
if (dt.Rows[i]["isActive"].ToString() == "T")
{
student.Status = "Active";
}
else
{
student.Status = "Deactive";
}
result.Add(student);
}
DataProvider.CloseConnection(con);
return(result);
}
}
public static int getCurrentStudent(string nameClass, string schoolYear)
{
string sCommand = string.Format(@"Select Count(Student.IDStudent) from Student_Class join Student on (Student_Class.IDStudent = Student.IDStudent) where nameClass = '{0}' and schoolYear = '{1}' and Student.isActive = 'T'", nameClass, schoolYear);
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
return(0);
}
return(int.Parse(dt.Rows[0][0].ToString()));
}
public static int getTotalStudent()
{
string sCommand = @"Select totalStudent from Role";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
return(0);
}
return(int.Parse(dt.Rows[0]["totalStudent"].ToString()));
}
public static bool isMaster(string id, string nameClass, string schoolYear)
{
string sCommand = @"Select* from Class where IDMaster ='" + id + "' and nameClass = '" + nameClass + "' and schoolYear ='" + schoolYear + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
return(false);
}
return(true);
}
public static int getPassScore()
{
string sCommand = @"Select passScore from Role";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
return(0);
}
return(int.Parse(dt.Rows[0]["passScore"].ToString()));
}
// Giao vien bo mon xem diem cua hoc sinh do giao vien do day
public static DataTable LoadMark(string idteacher)
{
string sTruyVan = @"select c.NameSubject, e.Name, d.nameClass, d.schoolYear, d.Semester, d.FirstFifteenMinutes, d.SecondFifteenMinutes, d.ThirdFifteenMinutes, d.FirstFortyFiveMinutes, d.SecondFortyFiveMinutes, d.ThirdFortyFiveMinutes, d.SemesterMark, d.IDStudent, d.IDSubject
from Teacher a join Teacher_Subject b on a.IDTeacher = b.IDTeacher
join Subject c on c.IDSubject = b.IDSubject
join Mark d on d.IDSubject = b.IDSubject and d.IDTeacher = b.IDTeacher
join Student e on d.IDStudent = e.IDStudent
where a.IDTeacher= '" + idteacher + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sTruyVan, con);
return(dt);
}
public static List <StudentDTO> LoadStudent(string nameClass, string schoolYear, string status)
{
string sCommand = "";
if (status == "System.Windows.Controls.ComboBoxItem: Active")
{
sCommand = @"Select * from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where SC.nameClass = '" + nameClass + @"' and SC.schoolYear = '" + schoolYear + "' and S.IsActive = 'T'";
}
else if (status == "System.Windows.Controls.ComboBoxItem: Deactive")
{
sCommand = @"Select * from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where SC.nameClass = '" + nameClass + @"' and SC.schoolYear = '" + schoolYear + "' and S.IsActive = 'F'";
}
else
{
sCommand = @"Select * from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where SC.nameClass = '" + nameClass + @"' and SC.schoolYear = '" + schoolYear + "'";
}
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <StudentDTO> result = new List <StudentDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
StudentDTO student = new StudentDTO();
student.Id = dt.Rows[i]["IDStudent"].ToString();
student.Name = dt.Rows[i]["Name"].ToString();
student.Gender = dt.Rows[i]["Gender"].ToString();
student.Email = dt.Rows[i]["Email"].ToString();
student.Phone = dt.Rows[i]["Phone"].ToString();
student.DateofBith = dt.Rows[i]["BirthDay"].ToString();
student.NameClass = dt.Rows[i]["nameClass"].ToString();
student.SchoolYear = dt.Rows[i]["schoolYear"].ToString();
if (dt.Rows[i]["isActive"].ToString() == "T")
{
student.Status = "Active";
}
else
{
student.Status = "Deactive";
}
result.Add(student);
}
DataProvider.CloseConnection(con);
return(result);
}
public static string getNameStudent(string idStudent)
{
string sCommand = @"Select Name from Student where IDStudent = '" + idStudent + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
return(null);
}
else
{
return(dt.Rows[0]["Name"].ToString());
}
}
public static bool isMaster(string IDTeacher, string schoolYear)
{
string sCommand = @"select* from Class where IDMaster = '" + IDTeacher + "' and schoolYear ='" + schoolYear + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
DataProvider.CloseConnection(con);
return(false);
}
else
{
return(true);
}
}
// Tìm sinh viên theo ID, trả về ID sinh viên
public static string FindStudentByID(string ID)
{
string sCommand = @"Select IDStudent from Student where IDStudent = '" + ID + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count == 0)
{
return(null);
}
string result;
result = dt.Rows[0][0].ToString();
DataProvider.CloseConnection(con);
return(result);
}
public static List <string> getNameClassWithIDStudent(string IDStudent)
{
string sCommand = string.Format(@"Select nameClass from Student_Class where IDStudent = '{0}'", IDStudent);
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
return(null);
}
List <string> result = new List <string>();
int n = dt.Rows.Count;
for (int i = 0; i < n; i++)
{
result.Add(dt.Rows[i][0].ToString());
}
return(result);
}
public static List <StudentDTO> searchStudentNotInClass(string textToSearch, string schoolYear)
{
string sCommand = @"select distinct S.*
from Student S, Student_Class SC
where S.IDStudent in (select IDStudent from Student) and S.IDStudent not in (select IDStudent from Student_Class) and SC.schoolYear ='" + schoolYear + "' and ((S.IDStudent like '%" + textToSearch + "%') or (S.Name like N'%" + textToSearch + "%'))";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
DataProvider.CloseConnection(con);
return(null);
}
int n = dt.Rows.Count;
List <StudentDTO> result = new List <StudentDTO>();
for (int i = 0; i < n; i++)
{
StudentDTO student = new StudentDTO();
student.Id = dt.Rows[i]["IDStudent"].ToString();
student.Name = dt.Rows[i]["Name"].ToString();
student.Gender = dt.Rows[i]["Gender"].ToString();
student.Email = dt.Rows[i]["Email"].ToString();
student.Phone = dt.Rows[i]["Phone"].ToString();
student.DateofBith = dt.Rows[i]["BirthDay"].ToString();
if (dt.Rows[i]["isActive"].ToString() == "T")
{
student.Status = "Active";
}
else
{
student.Status = "Deactive";
}
result.Add(student);
}
DataProvider.CloseConnection(con);
return(result);
}
public static int KiemTraTonTai(KhachHangDTO temp)
{
int kq = 0;
conn = DataProvider.OpenConnection();
string sTruyVan3 = "select * from KhachHang where soDienThoai = '" + temp.SoDienThoai + "' ";
SqlCommand cmd3 = new SqlCommand(sTruyVan3, conn);
cmd3.Connection = conn;
cmd3.ExecuteNonQuery();
DataTable dt3 = DataProvider.GetDataTable(sTruyVan3, conn);
if (dt3.Rows.Count > 0)
{
kq = 1;
}
DataProvider.CloseConnection(conn);
return(kq);
}
// Tìm sinh viên theo tên, trả về ID sinh viên
public static List <string> FindStudentByName(string nameStudent)
{
string sCommand = @"Select IDStudent from Student where Name = N'" + nameStudent + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <string> result = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
string id = dt.Rows[0][0].ToString();
result.Add(id);
}
DataProvider.CloseConnection(con);
return(result);
}
public static List <string> loadSchoolYear()
{
string sCommand = @"Select distinct schoolYear from dbo.Class";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <string> result = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
string schoolyear = dt.Rows[i]["schoolYear"].ToString();
result.Add(schoolyear);
}
DataProvider.CloseConnection(con);
return(result.Distinct().ToList());
}
public static List <StudentDTO> searchStudent(string textToSearch, string nameClass, string schoolYear)
{
string sCommand = @"Select* from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where ((S.Name like N'%" + textToSearch + "%') or (SC.IDStudent like '%" + textToSearch + "%')) and SC.nameClass = '" + nameClass + "' and SC.schoolYear = '" + schoolYear + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <StudentDTO> result = new List <StudentDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
StudentDTO student = new StudentDTO();
student.Id = dt.Rows[i]["IDStudent"].ToString();
student.Name = dt.Rows[i]["Name"].ToString();
student.Gender = dt.Rows[i]["Gender"].ToString();
student.Email = dt.Rows[i]["Email"].ToString();
student.Phone = dt.Rows[i]["Phone"].ToString();
student.DateofBith = dt.Rows[i]["BirthDay"].ToString();
student.NameClass = dt.Rows[i]["nameClass"].ToString();
student.SchoolYear = dt.Rows[i]["schoolYear"].ToString();
if (dt.Rows[i]["isActive"].ToString() == "T")
{
student.IsActive = "Active";
}
else
{
student.IsActive = "Deactive";
}
result.Add(student);
}
DataProvider.CloseConnection(con);
return(result);
}
public static AdminDTO Login(string id, string pw)
{
string sTruyVan = @"select * from Admin where ID= '" + id + @"' and PassWord = '******'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sTruyVan, con);
if (dt.Rows.Count == 0)
{
return(null);
}
AdminDTO admin = new AdminDTO();
admin.Id = dt.Rows[0]["ID"].ToString();
admin.Name = dt.Rows[0]["Name"].ToString();
admin.Gender = dt.Rows[0]["Gender"].ToString();
admin.Email = dt.Rows[0]["Email"].ToString();
admin.Phone = dt.Rows[0]["Phone"].ToString();
admin.DateofBith = dt.Rows[0]["BirthDay"].ToString();
admin.Type = "Admin";
DataProvider.CloseConnection(con);
return(admin);
}
public static List <string> loadListClassToComboBox(string idTeacher, string schoolYear)
{
string sCommand = @"select nameClass from Assign where IDTeacher = '" + idTeacher + "' and schoolYear = '" + schoolYear + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
List <string> result = new List <string>();
if (dt.Rows.Count <= 0)
{
return(null);
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string class_ = dt.Rows[i]["nameClass"].ToString();
result.Add(class_);
}
DataProvider.CloseConnection(con);
return(result.Distinct().ToList());
}
}
// Log in va load thông tin
public static StudentDTO Login(string id, string pw)
{
string sTruyVan = @"select * from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where S.IsActive ='T' and S.IDStudent= '" + id + @"' and S.PassWord = '******'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sTruyVan, con);
if (dt.Rows.Count == 0)
{
return(null);
}
StudentDTO student = new StudentDTO();
student.Id = dt.Rows[0]["IDStudent"].ToString();
student.Name = dt.Rows[0]["Name"].ToString();
student.Gender = dt.Rows[0]["Gender"].ToString();
student.Email = dt.Rows[0]["Email"].ToString();
student.Phone = dt.Rows[0]["Phone"].ToString();
student.DateofBith = dt.Rows[0]["BirthDay"].ToString();
student.NameClass = dt.Rows[0]["nameClass"].ToString();
student.SchoolYear = dt.Rows[0]["schoolYear"].ToString();
DataProvider.CloseConnection(con);
return(student);
}
public static List <ClassDTO> loadListClass(string schoolYear)
{
string sTruyVan = @"Select* from Class where schoolYear ='" + schoolYear + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sTruyVan, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <ClassDTO> result = new List <ClassDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
ClassDTO Class = new ClassDTO();
Class.Name = dt.Rows[i]["nameClass"].ToString();
Class.SchoolYear = dt.Rows[i]["schoolYear"].ToString();
Class.IdMaster = dt.Rows[i]["IDMaster"].ToString();
result.Add(Class);
}
DataProvider.CloseConnection(con);
return(result);
}
public static List <string> loadListSubjectToComboBox(string idTeacher, string nameClass, string schoolYear)
{
string sCommand = @"Select Subject.NameSubject from Subject join Assign on (Subject.IDSubject = Assign.IDSubject) where Assign.IDTeacher = '" + idTeacher + "' and Assign.nameClass = '" + nameClass + "' and schoolYear = '" + schoolYear + "'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
List <string> result = new List <string>();
if (dt.Rows.Count <= 0)
{
DataProvider.CloseConnection(con);
return(null);
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string nameSubject = dt.Rows[i]["nameSubject"].ToString();
result.Add(nameSubject);
}
DataProvider.CloseConnection(con);
return(result.Distinct().ToList());
}
}
public static List <TeacherDTO> loadListSubjectTeacher(string schoolYear)
{
string sCommand = "Select * from Teacher where IDTeacher in (select IDTeacher from Assign where schoolYear = '" + schoolYear + "')";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sCommand, con);
if (dt.Rows.Count <= 0)
{
DataProvider.CloseConnection(con);
return(null);
}
else
{
List <TeacherDTO> result = new List <TeacherDTO>();
int n = dt.Rows.Count;
for (int i = 0; i < n; i++)
{
TeacherDTO teacher = new TeacherDTO();
teacher.Id = dt.Rows[i]["IDTeacher"].ToString();
teacher.Name = dt.Rows[i]["Name"].ToString();
teacher.Gender = dt.Rows[i]["Gender"].ToString();
teacher.Email = dt.Rows[i]["Email"].ToString();
teacher.DateofBith = dt.Rows[i]["BirthDay"].ToString();
teacher.Type = dt.Rows[i]["TypeTeacher"].ToString();
teacher.NamePosition = "Subject Teacher";
teacher.Phone = dt.Rows[i]["Phone"].ToString();
result.Add(teacher);
}
DataProvider.CloseConnection(con);
return(result);
}
}
// Login va load thong tin
public static TeacherDTO Login(string id, string pw)
{
string sTruyVan = @"select * from Teacher where isActive ='T' and IDTeacher= '" + id + @"' and PassWord = '******'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.GetDataTable(sTruyVan, con);
if (dt.Rows.Count == 0)
{
return(null);
}
TeacherDTO teacher = new TeacherDTO();
teacher.Id = dt.Rows[0]["IDTeacher"].ToString();
teacher.Name = dt.Rows[0]["Name"].ToString();
teacher.Gender = dt.Rows[0]["Gender"].ToString();
teacher.Email = dt.Rows[0]["Email"].ToString();
teacher.Phone = dt.Rows[0]["Phone"].ToString();
teacher.DateofBith = dt.Rows[0]["BirthDay"].ToString();
teacher.Type = dt.Rows[0]["TypeTeacher"].ToString();
DataProvider.CloseConnection(con);
return(teacher);
}
