public void addHV_LH(List <HocVienLopHocDTO> lstHVLH, LopHocDTO currentLopHoc, string maHV)
{
string maLop = currentLopHoc.id_LH;
if (maLop != null && maHV != null && maLop.Trim() != "" && maHV.Trim() != "")
{
if (findHocVienInDSHV(lstHVLH, maHV) == null && lstHVLH.Count < currentLopHoc.sucChua)
{
HocVienLopHocDTO hvlh = new HocVienLopHocDTO();
HocPhiDTO hp = new HocPhiDTO();
hp.ClassId = maLop;
hp.StudentId = maHV;
hvlh.StudentId = maHV;
hvlh.ClassId = maLop;
hocVienLopHocDAL.insertHV_LH(hvlh);
HocPhiBLL.Instance.insertHocVien(hp);
}
else if (lstHVLH.Count >= currentLopHoc.sucChua)
{
hienThongBao("Lớp học đã đầy!");
}
else
{
hienThongBao("Học viên này đã có trong lớp!");
}
}
else
{
hienThongBao("Bạn chưa chọn học viên nào!");
}
}
public LopHocDTO getLopHoc(string maLop)
{
conn.Open();
LopHocDTO dto = null;
string query = "select LOPHOC.*, PHONGHOC.succhua_PH from LOPHOC inner join PHONGHOC on LOPHOC.id_PH = PHONGHOC.id_PH where id_LH = @id_LH";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("id_LH", maLop);
try
{
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
dto = new LopHocDTO();
dto.id_LH = rd["id_LH"].ToString();
dto.id_KH = rd["id_KH"].ToString();
dto.ngayBatDau = DateTime.Parse(rd["ngaybatdau"].ToString());
dto.ngayKetThuc = DateTime.Parse(rd["ngayketthuc"].ToString());
dto.id_GV = rd["id_GV"].ToString();
dto.ten_LH = rd["ten_LH"].ToString();
dto.id_PH = rd["id_LH"].ToString();
dto.ghiChu_LH = rd["ghichu_LH"].ToString();
dto.siSo = int.Parse(rd["siso_LH"].ToString());
dto.sucChua = int.Parse(rd["succhua_PH"].ToString());
}
}
catch (SqlException e)
{
conn.Close();
throw e;
}
conn.Close();
return(dto);
}
private void loadLopInfo()
{
currentLopHoc = chiTietLopHocBLL.getLopHocBasicInfo(currentLopHoc.id_LH);
lbTenLop.Text = currentLopHoc.ten_LH;
lbSiSo.Text = "Sĩ số: " + currentLopHoc.siSo + "/" + currentLopHoc.sucChua;
lbMaLop.Text = currentLopHoc.id_LH;
}
public List <LopHocDTO> getsIdAndName()
{
conn.Open();
List <LopHocDTO> lhs = new List <LopHocDTO>();
string sql = "select id_LH, ten_LH, hocphi from lophoc";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LopHocDTO lh = new LopHocDTO();
lh.id_LH = dr["id_LH"].ToString();
lh.ten_LH = dr["ten_LH"].ToString();
try
{
lh.hocPhi = double.Parse(dr["hocphi"].ToString());
}
catch
{
lh.hocPhi = -1;
}
lhs.Add(lh);
}
conn.Close();
return(lhs);
}
private List <LopHocDTO> getLopHocFullInfo(SqlDataReader rd)
{
Console.WriteLine("da vao getlophocFullInfo");
List <LopHocDTO> lstLopHocDTOs = new List <LopHocDTO>();
while (rd.Read())
{
LopHocDTO dto = new LopHocDTO();
dto.id_LH = rd["id_LH"].ToString();
dto.id_KH = rd["id_KH"].ToString();
dto.ngayBatDau = DateTime.Parse(rd["ngaybatdau"].ToString());
dto.ngayKetThuc = DateTime.Parse(rd["ngayketthuc"].ToString());
dto.thoiGian = int.Parse(rd["thoigian"].ToString());
dto.id_GV = rd["id_GV"].ToString();
dto.ten_LH = rd["ten_LH"].ToString();
dto.id_PH = rd["id_PH"].ToString();
dto.ghiChu_LH = rd["ghichu_LH"].ToString();
dto.siSo = int.Parse(rd["siso_LH"].ToString());
dto.ten_GV = rd["ten_GV"].ToString();
dto.ten_PH = rd["ten_PH"].ToString();
dto.ten_KH = rd["ten_KH"].ToString();
dto.sucChua = int.Parse(rd["succhua_PH"].ToString());
dto.hocPhi = double.Parse(rd["hocphi"].ToString());
lstLopHocDTOs.Add(dto);
}
return(lstLopHocDTOs);
}
public void updateLopHoc(LopHocDTO lopHocDTO)
{
conn.Open();
string query = "update LOPHOC set " +
"id_KH = @id_KH," +
"ngaybatdau = @ngaybatdau, " +
"ngayketthuc = @ngayketthuc, " +
"id_GV = @id_GV, " +
"ten_LH = @ten_LH, " +
"id_PH = @id_PH, " +
"ghichu_LH = @ghichu_LH " +
"where id_LH = @id_LH";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("id_LH", lopHocDTO.id_LH);
cmd.Parameters.AddWithValue("id_KH", lopHocDTO.id_KH);
cmd.Parameters.AddWithValue("ngaybatdau", lopHocDTO.ngayBatDau);
cmd.Parameters.AddWithValue("ngayketthuc", lopHocDTO.ngayKetThuc);
cmd.Parameters.AddWithValue("id_GV", lopHocDTO.id_GV);
cmd.Parameters.AddWithValue("ten_LH", lopHocDTO.ten_LH);
cmd.Parameters.AddWithValue("id_PH", lopHocDTO.id_PH);
cmd.Parameters.AddWithValue("ghichu_LH", lopHocDTO.ghiChu_LH);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ee)
{
conn.Close();
throw ee;
}
conn.Close();
}
private void btSuaLopHoc_Click(object sender, EventArgs e)
{
LopHocDTO lopHocDTO = getLopHocOnForm();
quanLyLopHocBLL.updateLopHoc(lopHocDTO);
hienThiGrdLopHoc();
}
////////////////////
/// function created by Giangboy. use for HocVienLopHoc Module
/// /////
///
public List <LopHocDTO> getsIdAndNameByUsername(string username)
{
conn.Open();
List <LopHocDTO> lhs = new List <LopHocDTO>();
string sql = "select lophoc.id_LH, lophoc.ten_LH, lophoc.hocphi from lophoc inner join giangvien on lophoc.id_GV = giangvien.id_GV where giangvien.username = @un";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("un", username);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LopHocDTO lh = new LopHocDTO();
lh.id_LH = dr["id_LH"].ToString();
lh.ten_LH = dr["ten_LH"].ToString();
try
{
lh.hocPhi = double.Parse(dr["hocphi"].ToString());
}
catch
{
lh.hocPhi = -1;
}
lhs.Add(lh);
}
conn.Close();
return(lhs);
}
private void btnThemLH_Click(object sender, EventArgs e)
{
try
{
if (ValidateTextbox() == true)
{
MessageBox.Show("Một hoặc vài trường cần thêm dữ liệu không được bỏ trống.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
LopHocDTO LopHoc = new LopHocDTO()
{
MaLop = txtMaLop.Text,
TenLop = txtTenLop.Text,
MaGVCN = txtMaGVCN.Text
};
lopHoc_Bll.UpdateLopHoc(LopHoc);
this.btnShowDSLop_Click(null, null);
}
}
catch (Exception ex)
{
MessageBox.Show("Xảy ra lỗi: " + ex.Message);
ClearTextbox();
}
}
private void btnSuaLH_Click(object sender, EventArgs e)
{
try
{
if (txtMaLop.Text == "" || txtTenLop.Text == "" || txtMaGVCN.Text == "")
{
MessageBox.Show("Phải Nhập đủ Thông Tin");
}
else if (txtMaLop.Text != "")
{
LopHocDTO LopHoc = new LopHocDTO()
{
MaLop = txtMaLop.Text,
TenLop = txtTenLop.Text,
MaGVCN = txtMaGVCN.Text
};
lopHoc_Bll.UpdateLopHoc(LopHoc);
this.btnShowDSLop_Click(null, null);
}
else
{
MessageBox.Show("Hãy Nhập Thông Tin Vào Box ...", "Thông báo");
}
}
catch (Exception ex)
{
MessageBox.Show("Có Lỗi :" + ex.Message);
}
}
public void updateLopHoc(LopHocDTO lopHocDTO)
{
if (lopHocDTO != null)
{
lopHocDAL.updateLopHoc(lopHocDTO);
}
}
public LopHocDTO getLopHocExtraInfo(string maLop)
{
conn.Open();
LopHocDTO dto = null;
string query = "select LOPHOC.*, GIANGVIEN.ten_GV, PHONGHOC.ten_PH, PHONGHOC.succhua_PH, KHOAHOC.ten_KH from " +
"LOPHOC inner join GIANGVIEN on LOPHOC.id_GV = GIANGVIEN.id_GV " +
"inner join PHONGHOC on LOPHOC.id_PH = PHONGHOC.id_PH " +
"inner join KHOAHOC on LOPHOC.id_KH = KHOAHOC.id_KH " +
"where id_LH = @id_LH";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("id_LH", maLop);
try
{
SqlDataReader rd = cmd.ExecuteReader();
List <LopHocDTO> lst = getLopHocFullInfo(rd);
if (lst.Count != 0)
{
dto = lst.ElementAt(0);
}
}
catch (SqlException e)
{
conn.Close();
throw e;
}
conn.Close();
return(dto);
}
private void button2_Click(object sender, EventArgs e)
{
LopHocDTO a = new LopHocDTO(textBox1.Text, textBox3.Text, comboBox1.SelectedValue.ToString());
myLopbul.suamotLop(a);
hienthi();
}
public List <LopHocDTO> getClassList(GiangVienDTO gv)
{
List <LopHocDTO> lhs = new List <LopHocDTO>();
conn.Open();
string sql = "select lophoc.id_LH, lophoc.ten_LH, lophoc.siso_LH, phonghoc.ten_PH, lophoc.ngaybatdau, lophoc.ngayketthuc, khoahoc.ten_KH from lophoc inner join giangvien on lophoc.id_GV = giangvien.id_GV inner join phonghoc on lophoc.id_PH = phonghoc.id_PH inner join khoahoc on lophoc.id_KH = khoahoc.id_KH where lophoc.id_GV = @idgv";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("idgv", gv.Id);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LopHocDTO lh = new LopHocDTO();
lh.id_LH = dr["id_LH"].ToString();
lh.ten_LH = dr["ten_LH"].ToString();
lh.ten_KH = dr["ten_KH"].ToString();
lh.ten_PH = dr["ten_PH"].ToString();
lh.ngayBatDau = DateTime.Parse(dr["ngaybatdau"].ToString());
lh.ngayKetThuc = DateTime.Parse(dr["ngayketthuc"].ToString());
lh.siSo = int.Parse(dr["siso_LH"].ToString());
lhs.Add(lh);
}
conn.Close();
return(lhs);
}
public static DataTable LayDSLopHoc(LopHocDTO lh)
{
string query = "SELECT Ma_Lop FROM LopHoc";
SqlParameter[] param = new SqlParameter[0];
return(DataProvider.ExecuteSelectQuery(query, param));
}
public void insertLopHoc(LopHocDTO lopHocDTO)
{
conn.Open();
string query = "insert into LOPHOC " +
"(hocphi, id_LH, id_KH, ngaybatdau, ngayketthuc, thoigian, id_GV, ten_LH, id_PH, ghichu_LH, siso_LH) values " +
"(@hp, @id_LH, @id_KH, @ngaybatdau, @ngayketthuc, @thoigian, @id_GV, @ten_LH, @id_PH, @ghichu_LH, @siso_LH) ";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("id_LH", lopHocDTO.id_LH);
cmd.Parameters.AddWithValue("id_KH", lopHocDTO.id_KH);
cmd.Parameters.AddWithValue("ngaybatdau", lopHocDTO.ngayBatDau);
cmd.Parameters.AddWithValue("ngayketthuc", lopHocDTO.ngayKetThuc);
cmd.Parameters.AddWithValue("thoigian", lopHocDTO.thoiGian);
cmd.Parameters.AddWithValue("id_GV", lopHocDTO.id_GV);
cmd.Parameters.AddWithValue("ten_LH", lopHocDTO.ten_LH);
cmd.Parameters.AddWithValue("id_PH", lopHocDTO.id_PH);
cmd.Parameters.AddWithValue("ghichu_LH", lopHocDTO.ghiChu_LH);
cmd.Parameters.AddWithValue("siso_LH", 0);
cmd.Parameters.AddWithValue("hp", lopHocDTO.hocPhi);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ee)
{
conn.Close();
throw ee;
}
conn.Close();
}
public bool UpdateLopHoc(LopHocDTO LopHoc)
{
try
{
using (SqlConnection connection = DataConnection.GetConnection())
{
connection.Open();
string query = "UPDATE LopHoc SET TenLop = @TenLop, MaGVCN = @MaGVCN WHERE MaLop = @MaLop";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@MaLop", SqlDbType.NVarChar).Value = LopHoc.MaLop;
command.Parameters.Add("@TenLop", SqlDbType.NVarChar).Value = LopHoc.TenLop;
command.Parameters.Add("@MaGVCN", SqlDbType.NVarChar).Value = LopHoc.MaGVCN;
command.ExecuteNonQuery();
connection.Close();
connection.Dispose();
MessageBox.Show("Sửa thành công.!!", "Thông báo cho mà biết này", MessageBoxButtons.OK, MessageBoxIcon.Information);
return true;
}
}
catch (Exception ex)
{
MessageBox.Show("Lỗi : " + ex.Message);
return false;
}
}
public void insertLopHoc(LopHocDTO lopHocDTO)
{
if (lopHocDTO != null)
{
lopHocDTO.id_LH = getNewID_LH();
lopHocDAL.insertLopHoc(lopHocDTO);
}
}
public static bool XoaLop(LopHocDTO lh)
{
string query = "DELETE FROM LopHoc WHERE Ma_Lop=@Ma_Lop AND SoSinhVien = 0";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@Ma_Lop", lh.Ma_Lop);
return(DataProvider.ExecuteDeleteQuery(query, param) == 1);
}
public static bool CapNhatSoSinhVienKhiLamMoi(LopHocDTO lh)
{
string query = "UPDATE LopHoc SET SoSinhVien = 0 WHERE Ma_Lop = @Ma_Lop ";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@Ma_Lop", lh.Ma_Lop);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static LopHocDTO ConvertToDTO(DataRow dr)
{
LopHocDTO lh = new LopHocDTO();
lh.Ma_Lop = dr["Ma_Lop"].ToString();
lh.SoSinhVien = Convert.ToInt32(dr["SoSinhVien"]);
lh.TrangThai = Convert.ToBoolean(dr["TrangThai"]);
return(lh);
}
protected void ChonLop()
{
SinhVienDTO sv = new SinhVienDTO();
LopHocDTO lh = new LopHocDTO();
cboChonLop.DataSource = SinhVienBUS.LayDSLopHoc(sv);
cboChonLop.DisplayMember = "Ma_Lop";
cboChonLop.ValueMember = "Ma_Lop";
}
public static bool CapNhatLop(LopHocDTO lh)
{
string query = "UPDATE LopHoc SET TrangThai = @TrangThai WHERE Ma_Lop=@MaLop";
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@MaLop", lh.Ma_Lop);
param[1] = new SqlParameter("@TrangThai", lh.TrangThai);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static bool ThemLopHoc(LopHocDTO lh)
{
string query = "INSERT INTO LopHoc (Ma_Lop,SoSinhVien,TrangThai) VALUES (@Ma_Lop,@SoSinhVien,@TrangThai)";
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@Ma_Lop", lh.Ma_Lop);
param[1] = new SqlParameter("@SoSinhVien", lh.SoSinhVien);
param[2] = new SqlParameter("@TrangThai", lh.TrangThai);
return(DataProvider.ExecuteInsertQuery(query, param) == 1);
}
private void grdLopHoc_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
int index = grdLopHoc.SelectedCells[0].RowIndex;
DataGridViewRow row = grdLopHoc.Rows[index];
string maLopHoc = row.Cells[0].Value.ToString();
LopHocDTO currentLopHoc = quanLyLopHocBLL.findLopHocInList(maLopHoc, currentListLopHoc);
new Gui_ChiTietLopHoc(currentLopHoc.id_LH).ShowDialog();
hienThiGrdLopHoc();
}
public static bool ThemLop(LopHocDTO lh)
{
if (LopHocDAO.KTLopTonTai(lh.Ma_Lop))
{
return(false);
}
else
{
return(LopHocDAO.ThemLopHoc(lh));
}
}
public static bool CapNhatLopHoc(LopHocDTO lh)
{
if (LopHocDAO.KTLopTonTai(lh.Ma_Lop))
{
return(LopHocDAO.CapNhatLop(lh));
}
else
{
return(false);
}
}
public static bool XoaLopHoc(LopHocDTO lh)
{
if (!LopHocDAO.KTLopTonTai(lh.Ma_Lop))
{
return(false);
}
else
{
return(LopHocDAO.XoaLop(lh));
}
}
public static bool CapNhatSoLuongSinhVien(LopHocDTO lh)
{
if (LopHocDAO.KTLopTonTai(lh.Ma_Lop))
{
return(LopHocDAO.CapNhatSinhVien(lh));
}
else
{
return(false);
}
}
public static bool CapNhatSoSinhVienKhiXoa(LopHocDTO lh)
{
if (!LopHocDAO.KTLopTonTai(lh.Ma_Lop))
{
return(false);
}
else
{
return(LopHocDAO.CapNhatSoSinhVienKhiXoa(lh));
}
}
