public static int sumDau_Rot()
{
int i = 0;
string Query = " select ho_ten, THI_SINH.so_bao_danh,sum(diem_thi) sum_diem_thi , ma_nganh from KET_QUA_CHAM_THI, THI_SINH , PHIEU_DKDT where THI_SINH.so_bao_danh = KET_QUA_CHAM_THI.so_bao_danh and PHIEU_DKDT.ho_va_ten = THI_SINH.ho_ten group by ho_ten,THI_SINH.so_bao_danh , ma_nganh";
string nameTable = "KET_QUA_CHAM_THI, THI_SINH , PHIEU_DKDT";
DataTable dt = sqlDataTable.getDataTable(Query, nameTable);
foreach (DataRow row in dt.Rows)
{
if ((double)row["sum_diem_thi"] >= DiemChuan.getDiemChuan((string)row["ma_nganh"]))
{
i++;
}
}
return(i);
}
public static bool Dau_Rot(string so_bao_danh)
{
string Query = " select ho_ten, THI_SINH.so_bao_danh,sum(diem_thi) sum_diem_thi , ma_nganh from KET_QUA_CHAM_THI, THI_SINH , PHIEU_DKDT where THI_SINH.so_bao_danh = KET_QUA_CHAM_THI.so_bao_danh and PHIEU_DKDT.ho_va_ten = THI_SINH.ho_ten group by ho_ten,THI_SINH.so_bao_danh , ma_nganh";
string nameTable = "DIEM_CHUAN";
DataTable dt = sqlDataTable.getDataTable(Query, nameTable);
foreach (DataRow row in dt.Rows)
{
if ((string)row["so_bao_danh"] == so_bao_danh)
{
if ((double)row["sum_diem_thi"] >= DiemChuan.getDiemChuan((string)row["ma_nganh"]))
{
return(true);
}
else
{
return(false);
}
}
}
return(false);
}
public static void insertKET_QUA_TUYEN_SINH()
{
SqlConnection con = new SqlConnection(sqlConnection.CONNECTION);
//con.ConnectionString = ConfigurationManager.ConnectionStrings[@"Data Source=PCHIEU\SQLEXPRESS;Initial Catalog=QLTuyenSinh;Integrated Security=True"].ConnectionString;
con.Open();
SqlCommand cmd = con.CreateCommand();
string Query = "select THI_SINH.so_bao_danh,sum(diem_thi) sum_diem_thi , ma_nganh from KET_QUA_CHAM_THI, THI_SINH , PHIEU_DKDT where THI_SINH.so_bao_danh = KET_QUA_CHAM_THI.so_bao_danh and PHIEU_DKDT.ho_va_ten = THI_SINH.ho_ten group by THI_SINH.so_bao_danh , ma_nganh order by sum(diem_thi) desc";
cmd.CommandText = Query;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("KET_QUA_CHAM_THI, THI_SINH , PHIEU_DKDT");
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
string so_bao_danh = (string)row["so_bao_danh"];
string ma_nganh = (string)row["ma_nganh"];
double sum_diem_thi = (double)row["sum_diem_thi"];
string confirm_DAU_ROT = "";
if (DiemChuan.Dau_Rot(so_bao_danh) == true)
{
confirm_DAU_ROT = "ĐẬU";
}
else
{
confirm_DAU_ROT = "RỚT";
}
string INSERT = string.Format("insert into KET_QUA_TUYEN_SINH values ('{0}','{1}',{2},N'{3}')", so_bao_danh, ma_nganh, sum_diem_thi, confirm_DAU_ROT);
cmd.CommandText = INSERT;
cmd.ExecuteNonQuery();
}
}