public DataTable GetAllStudentClass()
{
string query = "SELECT * FROM HOCSINH JOIN PHANLOP ON HOCSINH.MaHocSinh = PHANLOP.MaHocSinh ";
return(SqlProvider.ExecuteQuery(query));
}
public DataTable SoLopMoiKhoi()
{
string query = "SELECT KHOI.MaKhoi,count(LOP.MaLop) FROM KHOI JOIN LOP ON LOP.MaKhoi = KHOI.MaKhoi GROUP BY KHOI.MaKhoi";
DataTable dt = SqlProvider.ExecuteQuery(query);
return(dt);
}
bool CheckIDisUse(string id)
{
string query = string.Format("SELECT * From HOCSINH Where MaHocSinh = '{0}'", id);
DataTable dt = SqlProvider.ExecuteQuery(query);
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
public bool CheckAge(int age)
{
string query = "SELECT * FROM THAMSO";
DataTable dt = SqlProvider.ExecuteQuery(query);
int max_age = dt.Rows[0].Field <int>("TuoiHocSinhToiDa");
int min_age = dt.Rows[0].Field <int>("TuoiHocSinhToiThieu");
if (age >= min_age && age <= max_age)
{
return(true);
}
return(false);
}
// lấy các lớp thuộc khối
public DataTable GetClassInGrade(string MaKhoi)
{
string query = string.Format("SELECT * FROM LOP JOIN KHOI ON LOP.MaKhoi = KHOI.MaKhoi WHERE LOP.MaKhoi = {0}", MaKhoi);
return(SqlProvider.ExecuteQuery(query));
}
public DataTable GetAllClass()
{
string query = "SELECT * FROM LOP";
return(SqlProvider.ExecuteQuery(query));
}
public DataTable GetGRadeIdbyName(string name)
{
string query = string.Format("SELECT * FROM KHOI WHERE {0}", name);
return(SqlProvider.ExecuteQuery(query));
}
public DataTable GetAllGRade()
{
string query = "SELECT * FROM KHOI";
return(SqlProvider.ExecuteQuery(query));
}
public DataTable GetAllHocSinh()
{
string query = "SELECT * FROM HOCSINH";
return(SqlProvider.ExecuteQuery(query));
}
public void DeleteHocSinh(string id)
{
string query = string.Format("DELETE FROM HOCSINH WHERE MaHocSinh = '{0}'", id);
DataTable dt = SqlProvider.ExecuteQuery(query);
}
public DataTable GetAllStudentNotInClass()
{
string query = " SELECT * FROM DBO.HOCSINH HS WHERE HS.MaHocSinh NOT IN( SELECT HS1.MaHocSinh FROM DBO.HOCSINH HS1 JOIN PHANLOP PL ON HS1.MaHocSinh = PL.MaHocSinh JOIN LOP L ON L.MaLop = PL.MaLop)";
return(SqlProvider.ExecuteQuery(query));
}