private void btnXuatDSDiemLopHC_Click(object sender, EventArgs e)
{
if (tbNamHoc_DSDiemLopHC.Text.Trim() == "")
{
MessageBox.Show("Năm học không được để trống!", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (tbMaLop_DSDiemLopHc.Text.Trim() == "")
{
MessageBox.Show("Mã lớp không được để trống!", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string namHoc = checkNamHoc(tbNamHoc_DSDiemLopHC.Text);
if (namHoc == "")
{
return;
}
DataTable tableMain = new DataTable();
tableMain.Columns.Add("maSV", typeof(string));
tableMain.Columns.Add("tenSV", typeof(string));
tableMain.Columns.Add("ngaySinh", typeof(DateTime));
tableMain.Columns.Add("gioiTinh", typeof(string));
tableMain.Columns.Add("diemTB", typeof(double));
tableMain.Columns.Add("tongSoTC", typeof(int));
tableMain.Columns.Add("soTCNo", typeof(int));
tableMain.Columns.Add("xepLoai", typeof(string));
DS_DIEM_LopHC_HocKy reportDSDiem = new DS_DIEM_LopHC_HocKy();
((TextObject)reportDSDiem.Section2.ReportObjects["tbLop"]).Text = tbMaLop_DSDiemLopHc.Text.Trim();
((TextObject)reportDSDiem.Section2.ReportObjects["tbHocKy"]).Text = numericUpDownHocKy_DSDiemLopHC.Value.ToString();
((TextObject)reportDSDiem.Section2.ReportObjects["tbNamHoc"]).Text = namHoc;
using (SqlConnection cnn = new SqlConnection(constr))
{
string query0 = @"select gv.tenGV
from LopHanhChinh lhc,GiangVien gv
where lhc.maGVChuNhiemFK=gv.maGV and lhc.maLopHC=N'" + tbMaLop_DSDiemLopHc.Text.Trim() + "'";
using (SqlCommand cmd = new SqlCommand(query0, cnn))
{
DataTable tbgv = new DataTable();
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(tbgv);
if (tbgv.Rows.Count > 0)
{
((TextObject)reportDSDiem.Section2.ReportObjects["tbTenGV"]).Text = tbgv.Rows[0]["tenGV"].ToString();
}
}
}
DataTable tb = new DataTable();
string querySVLopHC = @"select * from SinhVien where maLopHC_FK=N'" + tbMaLop_DSDiemLopHc.Text.Trim() + "'";
using (SqlCommand cmd = new SqlCommand(querySVLopHC, cnn))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(tb);
}
}
((TextObject)reportDSDiem.Section2.ReportObjects["tbSiso"]).Text = tb.Rows.Count.ToString();
int tongTinChi = 0, soTinChiNo = 0;
double diemTBCongAllMonHoc = 0.0, tongDiemTBC = 0.0;
foreach (DataRow row in tb.Rows)
{
DataRow rowNew = tableMain.NewRow();
rowNew["maSV"] = row["maSV"];
rowNew["tenSV"] = row["tenSV"];
rowNew["ngaySinh"] = row["ngaySinh"];
rowNew["gioiTinh"] = row["gioiTinh"];
tongTinChi = 0; soTinChiNo = 0;
//lấy ds điểm các môn trong học kỳ của 1 sv theo mã sv
string quyery2 = @"select diemTBC,soTinChi
from SinhVien sv,BangDiem bd,LopTinChi ltc,MonHoc mh
where sv.maSV=bd.maSV_PK and bd.maLopTC_PK=ltc.maLopTC and ltc.maMonFK=mh.maMon
and sv.maLopHC_FK=N'" + tbMaLop_DSDiemLopHc.Text.Trim() + "' and bd.maSV_PK=N'" + row["maSV"] +
"' and hocKy=" + numericUpDownHocKy_DSDiemLopHC.Value + " and namHoc=N'" + namHoc + "'";
DataTable tbDiemOfSV = new DataTable();
using (SqlCommand cmd = new SqlCommand(quyery2, cnn))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(tbDiemOfSV);
}
}
diemTBCongAllMonHoc = 0.0; tongDiemTBC = 0.0;
int j;
for (j = 0; j < tbDiemOfSV.Rows.Count; j++)
{
tongTinChi += Convert.ToInt32(tbDiemOfSV.Rows[j]["soTinChi"].ToString());
tongDiemTBC += (Convert.ToDouble(tbDiemOfSV.Rows[j]["diemTBC"].ToString()) * Convert.ToDouble(tbDiemOfSV.Rows[j]["soTinChi"]));
if (Convert.ToDouble(tbDiemOfSV.Rows[j]["diemTBC"].ToString()) < 4.0)
{
soTinChiNo += Convert.ToInt32(tbDiemOfSV.Rows[j]["soTinChi"]);
}
}
if (tongTinChi > 0)
{
diemTBCongAllMonHoc = tongDiemTBC / tongTinChi;
}
rowNew["diemTB"] = diemTBCongAllMonHoc;
rowNew["tongSoTC"] = tongTinChi;
rowNew["soTCNo"] = soTinChiNo;
if (diemTBCongAllMonHoc < 1.5)
{
rowNew["xepLoai"] = "Kém";
}
else if (diemTBCongAllMonHoc < 4.0)
{
rowNew["xepLoai"] = "Yếu";
}
else if (diemTBCongAllMonHoc < 6.5)
{
rowNew["xepLoai"] = "Trung Bình";
}
else if (diemTBCongAllMonHoc < 8.0)
{
rowNew["xepLoai"] = "Khá";
}
else if (diemTBCongAllMonHoc < 9.5)
{
rowNew["xepLoai"] = "Giỏi";
}
else
{
rowNew["xepLoai"] = "Xuất Sắc";
}
tableMain.Rows.Add(rowNew);
}
}
DataSetQuanLyDiemSV dsSV = new DataSetQuanLyDiemSV();
dsSV.Tables["DSDiemSVLopHC"].Merge(tableMain);
reportDSDiem.SetDataSource(dsSV.Tables["DSDiemSVLopHC"]);
InBaoCao bc = new InBaoCao();
bc.CrystalReportViewer1.ReportSource = reportDSDiem;
bc.ShowDialog();
}
