public List <Diem_DTO> LoadDiemHV(string malop)
{
SqlConnection connect = DataProvider.TaoKetNoi();
List <Diem_DTO> lsDiem = new List <Diem_DTO>();
string truyVan = "SELECT HocVien.MSHV, HocVien.Ho, HocVien.Ten, HocVien.MaKH, HocVien.MaLop, Diem.Nghe, Diem.Noi, Diem.Doc, Diem.Viet, Diem.DiemTB FROM Diem RIGHT JOIN HocVien ON Diem.MSHV = HocVien.MSHV AND Diem.MaKH = HocVien.MaKH WHERE HocVien.MaLop = @MaLop";
SqlParameter[] par = new SqlParameter[1];
par[0] = new SqlParameter("@MaLop", malop);
SqlDataReader reader = DataProvider.TruyVanDuLieu(truyVan, par, connect);
while (reader.Read())
{
Diem_DTO diem = new Diem_DTO();
diem.MSHV = reader["MSHV"].ToString().Trim();
diem.MaKH = reader["MaKH"].ToString().Trim();
diem.Ho = reader["Ho"].ToString().Trim();
diem.Ten = reader["Ten"].ToString().Trim();
diem.MaLop = reader["MaLop"].ToString().Trim();
diem.Nghe = float.Parse(Utilities.ConvertToString(reader["Nghe"].ToString(), "0"));
diem.Noi = float.Parse(Utilities.ConvertToString(reader["Noi"].ToString(), "0"));
diem.Doc = float.Parse(Utilities.ConvertToString(reader["Doc"].ToString(), "0"));
diem.Viet = float.Parse(Utilities.ConvertToString(reader["Viet"].ToString(), "0"));
diem.DiemTB = float.Parse(Utilities.ConvertToString(reader["DiemTB"].ToString(), "0"));
lsDiem.Add(diem);
}
reader.Close();
connect.Close();
return(lsDiem);
}
public List <Diem_DTO> LayDiemTheoHS(int id, int MaHK)
{
List <Diem_DTO> DSDiem = new List <Diem_DTO>();
string query = string.Format("select DiemMon.*,TenMonHoc, TenHocKy from DiemMon join MonHoc on DiemMon.MaMonHoc = MonHoc.MaMonHoc join HocKi on HocKi.MaHocKy = DiemMon.MaHocKy where MaHocSinh = {0} and DiemMon.MaHocKy = {1}", id, MaHK);
DataTable data = DataProvider.Instance.ExecuteQuery(query);
foreach (DataRow item in data.Rows)
{
Diem_DTO diem = new Diem_DTO(item);
DSDiem.Add(diem);
}
return(DSDiem);
}
private void GetDiem()
{
if (diemDTO != null)
{
diemDTO = new Diem_DTO();
}
diemDTO.MSHV = txt_mshv.Text;
diemDTO.MaKH = txt_makh.Text;
diemDTO.MaLop = txt_maLop.Text;
diemDTO.Nghe = float.Parse(txt_diemNghe.Text);
diemDTO.Noi = float.Parse(txt_diemNoi.Text);
diemDTO.Doc = float.Parse(txt_diemDoc.Text);
diemDTO.Viet = float.Parse(txt_diemViet.Text);
diemDTO.DiemTB = TinhDiemTB(float.Parse(txt_diemNghe.Text), float.Parse(txt_diemNoi.Text), float.Parse(txt_diemDoc.Text), float.Parse(txt_diemViet.Text));
}
public int UpdateDiem(Diem_DTO diem)
{
string update = "UPDATE Diem SET Nghe = @Nghe, Noi = @Noi, Doc = @Doc, Viet = @Viet, DiemTB = @DiemTB WHERE MSHV = @MSHV AND MaKH = @MaKH";
SqlParameter[] par = new SqlParameter[7];
par[0] = new SqlParameter("@MSHV", diem.MSHV);
par[1] = new SqlParameter("@MaKH", diem.MaKH);
par[2] = new SqlParameter("@Nghe", diem.Nghe);
par[3] = new SqlParameter("@Noi", diem.Noi);
par[4] = new SqlParameter("@Doc", diem.Doc);
par[5] = new SqlParameter("@Viet", diem.Viet);
par[6] = new SqlParameter("@DiemTB", diem.DiemTB);
SqlConnection connect = DataProvider.TaoKetNoi();
int ketQua = DataProvider.ExecuteUpdateQuery(update, par, connect);
connect.Close();
return(ketQua);
}
public int AddDiem(Diem_DTO diem)
{
string insert = "INSERT INTO Diem (MSHV, MaKH, MaLop, Nghe, Noi, Doc, Viet, DiemTB, TrangThai) VALUES(@MSHV, @MaKH, @MaLop, @Nghe, @Noi, @Doc, @Viet, @DiemTB, 1)";
SqlParameter[] par = new SqlParameter[8];
par[0] = new SqlParameter("@MSHV", diem.MSHV);
par[1] = new SqlParameter("@MaKH", diem.MaKH);
par[2] = new SqlParameter("@MaLop", diem.MaLop);
par[3] = new SqlParameter("@Nghe", diem.Nghe);
par[4] = new SqlParameter("@Noi", diem.Noi);
par[5] = new SqlParameter("@Doc", diem.Doc);
par[6] = new SqlParameter("@Viet", diem.Viet);
par[7] = new SqlParameter("@DiemTB", diem.DiemTB);
SqlConnection connect = DataProvider.TaoKetNoi();
int ketQua = DataProvider.ExecuteInsertQuery(insert, par, connect);
connect.Close();
return(ketQua);
}
public List <Diem_DTO> LoadDiemHV()
{
SqlConnection con = DataProvider.TaoKetNoi();
List <Diem_DTO> listD = new List <Diem_DTO>();
string truyvan = @"Select * From NhanVien Where TrangThai = 1 AND ChucVu = 'CV04'";
SqlDataReader sdr = DataProvider.TruyVanDuLieu(truyvan, con);
while (sdr.Read())
{
Diem_DTO diem = new Diem_DTO()
{
MaHV = sdr["MaHV"].ToString(),
MaKhoaHoc = sdr["MaKhoaHoc"].ToString(),
DiemHV = (float)sdr["DiemHV"],
TrangThai = int.Parse(sdr["TrangThai"].ToString())
};
listD.Add(diem);
}
sdr.Close();
con.Close();
return(listD);
}
public int updateDiem(Diem_DTO diem)
{
Diem_DAO dDao = new Diem_DAO();
return(dDao.UpdateDiem(diem));
}
public int addDiem(Diem_DTO diem)
{
Diem_DAO dDao = new Diem_DAO();
return(dDao.AddDiem(diem));
}