public List <HocVien_DTO> LoadDanhSachHVDaNghiTheoMSHV(string mshv)
{
SqlConnection connect = DataProvider.TaoKetNoi();
List <HocVien_DTO> hocVien = new List <HocVien_DTO>();
string truyVan = "SELECT * from HocVien, KhoaHoc, Lop where HocVien.MSHV LIKE N'%{0}%' AND HocVien.MaKH = KhoaHoc.MaKH AND HocVien.MaLop = Lop.MaLop AND HocVien.TrangThai = 0";
SqlDataReader reader = DataProvider.TruyVanDuLieu(string.Format(truyVan, mshv), connect);
while (reader.Read())
{
HocVien_DTO hv = new HocVien_DTO()
{
MSHV = reader["MSHV"].ToString(),
Ho = reader["Ho"].ToString(),
Ten = reader["Ten"].ToString(),
GioiTinh = reader["GioiTinh"].ToString(),
NgaySinh = DateTime.Parse(reader["NgaySinh"].ToString()),
SDT = reader["SDT"].ToString(),
Email = reader["Email"].ToString(),
DiaChi = reader["DiaChi"].ToString(),
TenKH = reader["TenKH"].ToString(),
MaLop = reader["MaLop"].ToString(),
TrangThai = int.Parse(reader["TrangThai"].ToString())
};
hocVien.Add(hv);
}
reader.Close();
connect.Close();
return(hocVien);
}
private void GetDetail()
{
if (hvDTO != null)
{
hvDTO = new HocVien_DTO();
}
hvDTO.MSHV = txt_mshv.Text;
hvDTO.Ho = txt_ho.Text;
hvDTO.Ten = txt_ten.Text;
hvDTO.GioiTinh = cbo_gioiTinh.Text;
hvDTO.NgaySinh = DateTime.Parse(dt_ngaySinh.EditValue.ToString());
hvDTO.SDT = txt_sdt.Text;
hvDTO.Email = txt_email.Text;
hvDTO.DiaChi = txt_diaChi.Text;
hvDTO.MaKH = lke_khoaHoc.Text;
hvDTO.MaLop = lke_lopHoc.Text;
}
public int AddHocVien(HocVien_DTO hvDTO)
{
string insert = "INSERT INTO HocVien(MSHV, Ho, Ten, GioiTinh, NgaySinh, SDT, Email, DiaChi, MaKH, MaLop, TrangThai) VALUES(@MSHV, @Ho, @Ten, @GioiTinh, @NgaySinh, @SDT, @Email, @DiaChi, @MaKH, @MaLop, 1)";
SqlParameter[] parameter = new SqlParameter[10];
parameter[0] = new SqlParameter("@MSHV", hvDTO.MSHV);
parameter[1] = new SqlParameter("@Ho", hvDTO.Ho);
parameter[2] = new SqlParameter("@Ten", hvDTO.Ten);
parameter[3] = new SqlParameter("@GioiTinh", hvDTO.GioiTinh);
parameter[4] = new SqlParameter("@NgaySinh", hvDTO.NgaySinh);
parameter[5] = new SqlParameter("@SDT", hvDTO.SDT);
parameter[6] = new SqlParameter("@Email", hvDTO.Email);
parameter[7] = new SqlParameter("@DiaChi", hvDTO.DiaChi);
parameter[8] = new SqlParameter("@MaKH", hvDTO.MaKH);
parameter[9] = new SqlParameter("@MaLop", hvDTO.MaLop);
SqlConnection connect = DataProvider.TaoKetNoi();
int ketQua = DataProvider.ExecuteInsertQuery(insert, parameter, connect);
connect.Close();
return(ketQua);
}
public int UpdateHocVien(HocVien_DTO hvDTO)
{
string update = "UPDATE HocVien SET Ho = @Ho, Ten = @Ten, GioiTinh = @GioiTinh, NgaySinh = @NgaySinh, SDT = @SDT, Email = @Email, DiaChi = @DiaChi, MaKH = @MaKH, MaLop = @MaLop WHERE MSHV = @MSHV";
SqlParameter[] parameter = new SqlParameter[10];
parameter[0] = new SqlParameter("@MSHV", hvDTO.MSHV);
parameter[1] = new SqlParameter("@Ho", hvDTO.Ho);
parameter[2] = new SqlParameter("@Ten", hvDTO.Ten);
parameter[3] = new SqlParameter("@GioiTinh", hvDTO.GioiTinh);
parameter[4] = new SqlParameter("@NgaySinh", hvDTO.NgaySinh);
parameter[5] = new SqlParameter("@SDT", hvDTO.SDT);
parameter[6] = new SqlParameter("@Email", hvDTO.Email);
parameter[7] = new SqlParameter("@DiaChi", hvDTO.DiaChi);
parameter[8] = new SqlParameter("@MaKH", hvDTO.MaKH);
parameter[9] = new SqlParameter("@MaLop", hvDTO.MaLop);
SqlConnection connect = DataProvider.TaoKetNoi();
int ketQua = DataProvider.ExecuteUpdateQuery(update, parameter, connect);
connect.Close();
return(ketQua);
}
public List <HocVien_DTO> LoadDanhSachHV()
{
SqlConnection connect = DataProvider.TaoKetNoi();
List <HocVien_DTO> hocVien = new List <HocVien_DTO>();
string truyVan = "SELECT * from HocVien, KhoaHoc, Lop WHERE HocVien.KhoaHoc = KhoaHoc.MaKH AND HocVien.MaLop = Lop.MaLop AND HocVien.TrangThai = 1";
SqlDataReader reader = DataProvider.TruyVanDuLieu(truyVan, connect);
if (hocVien != null)
{
hocVien.Clear();
}
while (reader.Read())
{
HocVien_DTO hv = new HocVien_DTO()
{
ID = int.Parse(reader["ID"].ToString()),
MSHV = reader["MSHV"].ToString(),
Ho = reader["Ho"].ToString(),
Ten = reader["Ten"].ToString(),
GioiTinh = reader["GioiTinh"].ToString(),
NgaySinh = DateTime.Parse(reader["NgaySinh"].ToString()),
SDT = reader["SDT"].ToString(),
Email = reader["Email"].ToString(),
DiaChi = reader["DiaChi"].ToString(),
TenKH = reader["TenKH"].ToString(),
TenLop = reader["MaLop"].ToString(),
HocPhi = int.Parse(reader["HocPhi"].ToString()),
TrangThai = int.Parse(reader["TrangThai"].ToString())
};
hocVien.Add(hv);
}
reader.Close();
connect.Close();
return(hocVien);
}
public bool AddHocVien(HocVien_DTO hocVien_DTO)
{
string insert = "INSERT INTO HocVien(MSHV, Ho, Ten, GioiTinh, NgaySinh, SDT, Email, DiaChi, KhoaHoc, MaLop, HocPhi, TrangThai) VALUES(@MSHV, @Ho, @Ten, @GioiTinh, @NgaySinh, @SDT, @Email, @DiaChi, @KhoaHoc, @MaLop, 1, 1)";
List <SqlParameter> lsPara = new List <SqlParameter>();
lsPara.Add(new SqlParameter("@MSHV", hocVien_DTO.MSHV));
lsPara.Add(new SqlParameter("@Ho", hocVien_DTO.Ho));
lsPara.Add(new SqlParameter("@Ten", hocVien_DTO.Ten));
lsPara.Add(new SqlParameter("@GioiTinh", hocVien_DTO.GioiTinh));
lsPara.Add(new SqlParameter("@NgaySinh", hocVien_DTO.NgaySinh));
lsPara.Add(new SqlParameter("@SDT", hocVien_DTO.SDT));
lsPara.Add(new SqlParameter("@Email", hocVien_DTO.Email));
lsPara.Add(new SqlParameter("@DiaChi", hocVien_DTO.DiaChi));
lsPara.Add(new SqlParameter("@KhoaHoc", hocVien_DTO.KhoaHoc));
lsPara.Add(new SqlParameter("@MaLop", hocVien_DTO.MaLop));
SqlConnection connect = DataProvider.TaoKetNoi();
bool ketQua = DataProvider.ThucThiCauLenh(insert, lsPara.ToArray(), connect);
connect.Close();
return(ketQua);
}
private void GetDetail()
{
if (HV_DTO == null)
{
HV_DTO = new HocVien_DTO();
}
HV_DTO.MSHV = txt_ma.Text;
HV_DTO.Ho = txt_ho.Text;
HV_DTO.Ten = txt_ten.Text;
if (cb_gioitinh.EditValue.ToString() == "Nam")
{
HV_DTO.GioiTinh = "Nam";
}
else
{
HV_DTO.GioiTinh = "Nữ";
}
HV_DTO.NgaySinh = DateTime.Parse(dt_ngaysinh.EditValue.ToString());
HV_DTO.SDT = txt_sdt.Text;
HV_DTO.Email = txt_email.Text;
HV_DTO.DiaChi = txt_diachi.Text;
if (cb_khoahoc.EditValue.ToString() == "IELTS")
{
HV_DTO.KhoaHoc = "KH01";
}
else if (cb_khoahoc.EditValue.ToString() == "IELTS_ADVANCED")
{
HV_DTO.KhoaHoc = "KH02";
}
else if (cb_khoahoc.EditValue.ToString() == "TOEFL-IBT")
{
HV_DTO.KhoaHoc = "KH03";
}
else if (cb_khoahoc.EditValue.ToString() == "TOEFL-IBT_ADVANCED")
{
HV_DTO.KhoaHoc = "KH04";
}
else if (cb_khoahoc.EditValue.ToString() == "TOEIC")
{
HV_DTO.KhoaHoc = "KH05";
}
else if (cb_khoahoc.EditValue.ToString() == "TOEIC_ADVANCED")
{
HV_DTO.KhoaHoc = "KH06";
}
if (cb_malop.EditValue.ToString() == "LH01")
{
HV_DTO.MaLop = "LH01";
}
else if (cb_malop.EditValue.ToString() == "LH02")
{
HV_DTO.MaLop = "LH02";
}
else if (cb_malop.EditValue.ToString() == "LH03")
{
HV_DTO.MaLop = "LH03";
}
else if (cb_malop.EditValue.ToString() == "LH04")
{
HV_DTO.MaLop = "LH04";
}
else if (cb_malop.EditValue.ToString() == "LH05")
{
HV_DTO.MaLop = "LH05";
}
else if (cb_malop.EditValue.ToString() == "LH06")
{
HV_DTO.MaLop = "LH06";
}
//HV_DTO = new HocVien_DTO();
}
public int AddHV(HocVien_DTO hvDTO)
{
HocVien_DAO hvDAO = new HocVien_DAO();
return(hvDAO.AddHocVien(hvDTO));
}
public int UpdateHV(HocVien_DTO hvDTO)
{
HocVien_DAO hvDAO = new HocVien_DAO();
return(hvDAO.UpdateHocVien(hvDTO));
}
public bool AddHV(HocVien_DTO hvDTO)
{
return(hvDAO.AddHocVien(hvDTO));
}
