public bool insert(GiangVienDTO gv)
{
try {
conn.Open();
string sql = "insert into giangvien values (@id, @name, @un, @dob, @sdt, @addr, @note, @created, @updated)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("id", gv.Id);
cmd.Parameters.AddWithValue("name", gv.Name);
cmd.Parameters.AddWithValue("un", gv.Username);
cmd.Parameters.AddWithValue("dob", gv.DayOfBirth);
cmd.Parameters.AddWithValue("sdt", gv.Phone);
cmd.Parameters.AddWithValue("addr", gv.Address);
cmd.Parameters.AddWithValue("note", gv.Note);
cmd.Parameters.AddWithValue("created", gv.Created);
cmd.Parameters.AddWithValue("updated", gv.Updated);
int rowEffect = cmd.ExecuteNonQuery();
conn.Close();
if (rowEffect == 0)
{
return(false);
}
return(true);
}
catch
{
conn.Close();
return(false);
}
}
public static IList GetList()
{
ArrayList arrList = new ArrayList();
GiangVienDTO giangVienDTO = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from GIANGVIEN";
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
giangVienDTO = new GiangVienDTO();
giangVienDTO.MaGiangVien = (int)reader["MaGiangVien"];
giangVienDTO.TenGiangVien = (string)reader["TenGiangVien"];
giangVienDTO.DiaChi = (string)reader["DiaChi"];
giangVienDTO.SoDienThoai = (string)reader["SoDienThoai"];
giangVienDTO.Email = (string)reader["Email"];
arrList.Add(giangVienDTO);
}
reader.Close();
connection.Close();
return(arrList);
}
public static GiangVienDTO TimTheoMaGiangVien(int maGiangVien)
{
ArrayList arrList = new ArrayList();
GiangVienDTO giangVienDTO = new GiangVienDTO();
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from GIANGVIEN where MaGiangVien = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaGiangVien", OleDbType.Integer);
command.Parameters["@MaGiangVien"].Value = maGiangVien;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
giangVienDTO.MaGiangVien = (int)reader["MaGiangVien"];
giangVienDTO.TenGiangVien = (string)reader["TenGiangVien"];
giangVienDTO.DiaChi = (string)reader["DiaChi"];
giangVienDTO.SoDienThoai = (string)reader["SoDienThoai"];
giangVienDTO.Email = (string)reader["Email"];
arrList.Add(giangVienDTO);
}
reader.Close();
connection.Close();
return(giangVienDTO);
}
public bool update(GiangVienDTO gv)
{
try
{
conn.Open();
string sql = "update giangvien set ten_GV = @name, username = @un, ngaysinh_GV = @dob, sodt_GV = @sdt, diachi_GV = @addr, ghichu_GV = @note, ngaysua = @updated where id_GV = @id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("id", gv.Id);
cmd.Parameters.AddWithValue("name", gv.Name);
cmd.Parameters.AddWithValue("un", gv.Username);
cmd.Parameters.AddWithValue("dob", gv.DayOfBirth);
cmd.Parameters.AddWithValue("sdt", gv.Phone);
cmd.Parameters.AddWithValue("addr", gv.Address);
cmd.Parameters.AddWithValue("note", gv.Note);
cmd.Parameters.AddWithValue("updated", gv.Updated);
int rowEffect = cmd.ExecuteNonQuery();
conn.Close();
if (rowEffect == 0)
{
return(false);
}
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
conn.Close();
return(false);
}
}
private bool Them(bool error)
{
if (type == 0 && !error)
{
GiangVienDTO gv = new GiangVienDTO();
gv.Name = tb_ten.Text.Trim();
gv.Username = cb_username.Text;
gv.Note = tb_ghichu.Text;
gv.Phone = tb_sdt.Text;
gv.Address = tb_diachi.Text;
gv.DayOfBirth = dateTimePicker1.Value;
if (GiangVienBLL.Instance.insert(gv))
{
return(true);
}
}
else if (type == 1 && !error)
{
GiangVienDTO gv = new GiangVienDTO();
gv.Id = ma;
gv.Name = tb_ten.Text.Trim();
gv.Username = cb_username.Text;
gv.Note = tb_ghichu.Text;
gv.Phone = tb_sdt.Text;
gv.Address = tb_diachi.Text;
gv.DayOfBirth = dateTimePicker1.Value;
if (GiangVienBLL.Instance.update(gv))
{
return(true);
}
}
return(false);
}
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 bool insert(GiangVienDTO gv)
{
string ma = "GV_" + TimingDTO.getTimeString("dd", "MM", "yy", "HH", "mm", "ss");
gv.Id = ma;
gv.Created = DateTime.Now;
gv.Updated = gv.Created;
return(GiangVienDAL.Instance.insert(gv));
}
private void Dgv_GV_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
GiangVienDTO gv = new GiangVienDTO();
int index = dgv_GV.CurrentCell.RowIndex;
gv.Id = dgv_GV.Rows[index].Cells["id_GV"].Value.ToString();
gv.Name = dgv_GV.Rows[index].Cells["ten_GV"].Value.ToString();
gv.Note = dgv_GV.Rows[index].Cells["ghichu_GV"].Value.ToString();
gv.Phone = dgv_GV.Rows[index].Cells["sodt_GV"].Value.ToString();
gv.DayOfBirth = DateTime.Parse(dgv_GV.Rows[index].Cells["ngaysinh_GV"].Value.ToString());
gv.Address = dgv_GV.Rows[index].Cells["diachi_GV"].Value.ToString();
new GiangVienDialog_chiTiet(gv).ShowDialog();
}
private void GiangVienDialog_InsertUpdate_Load(object sender, EventArgs e)
{
dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = "dd/MM/yyyy";
List <string> freeUsername = GiangVienBLL.Instance.getFreeUsername();
if (type == 0)
{
this.lb_title.Text = "Thêm giảng viên";
bt_ok.Text = "Thêm";
if (freeUsername.Count == 0)
{
MessageBox.Show("Hiện tại chưa thể thêm giảng viên mới. Vui lòng thêm mới Username trước!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
else
{
foreach (string un in freeUsername)
{
cb_username.Items.Add(un);
}
cb_username.SelectedIndex = 0;
}
}
else
{
this.lb_title.Text = "Sửa giảng viên";
bt_ok.Text = "Sửa";
GiangVienDTO gv = GiangVienBLL.Instance.findById(ma);
tb_ten.Text = gv.Name;
tb_sdt.Text = gv.Phone;
dateTimePicker1.Value = gv.DayOfBirth;
tb_ghichu.Text = gv.Note;
tb_diachi.Text = gv.Address;
cb_username.Items.Add(gv.Username);
cb_username.SelectedIndex = 0;
foreach (string un in freeUsername)
{
cb_username.Items.Add(un);
}
}
}
public GiangVienDTO findById(string ma)
{
conn.Open();
string sql = "select * from giangvien where id_GV = @ma";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("ma", ma);
SqlDataReader dr = cmd.ExecuteReader();
GiangVienDTO gv = null;
while (dr.Read())
{
gv = GiangVienMapper.map(dr);
}
conn.Close();
return(gv);
}
private void listGiangVien_MouseClick(object sender, MouseEventArgs e)
{
try
{
if (listGiangVien.SelectedIndex >= 0)
{
giangVienUpdateDTO = GiangVienBUS.TimTheoMaGiangVien(Int32.Parse(listGiangVien.SelectedValue.ToString()));
txtTenGiangVienUpdate.Text = giangVienUpdateDTO.TenGiangVien;
txtSoDienThoaiUpdate.Text = giangVienUpdateDTO.SoDienThoai;
txtDiaChiUpdate.Text = giangVienUpdateDTO.DiaChi;
txtEmailUpdate.Text = giangVienUpdateDTO.Email;
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List <GiangVienDTO> findByName(string ten)
{
conn.Open();
List <GiangVienDTO> gvs = new List <GiangVienDTO>();
string sql = "select * from giangvien where ten_GV like concat('%',@ten,'%')";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("ten", ten);
SqlDataReader dr = cmd.ExecuteReader();
GiangVienDTO gv = null;
while (dr.Read())
{
gv = GiangVienMapper.map(dr);
gvs.Add(gv);
}
conn.Close();
return(gvs);
}
public static void Insert(GiangVienDTO giangVienDTO)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Insert into GIANGVIEN(TenGiangVien, SoDienThoai, DiaChi, Email) values(?,?,?,?)";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@TenGiangVien", OleDbType.WChar);
command.Parameters.Add("@SoDienThoai", OleDbType.WChar);
command.Parameters.Add("@DiaChi", OleDbType.WChar);
command.Parameters.Add("@Email", OleDbType.WChar);
command.Parameters["@TenGiangVien"].Value = giangVienDTO.TenGiangVien;
command.Parameters["@SoDienThoai"].Value = giangVienDTO.SoDienThoai;
command.Parameters["@DiaChi"].Value = giangVienDTO.DiaChi;
command.Parameters["@Email"].Value = giangVienDTO.Email;
command.ExecuteNonQuery();
command.CommandText = "select @@IDENTITY";
giangVienDTO.MaGiangVien = (int)command.ExecuteScalar();
connection.Close();
}
public static void UpdateRecord(GiangVienDTO giangVienDTO)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Update GIANGVIEN Set [TenGiangVien] = ?, [DiaChi] = ?, [SoDienThoai] = ?, [Email] = ? Where [MaGiangVien] = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@TenGiangVien", OleDbType.WChar);
command.Parameters.Add("@DiaChi", OleDbType.WChar);
command.Parameters.Add("@SoDienThoai", OleDbType.WChar);
command.Parameters.Add("@Email", OleDbType.WChar);
command.Parameters.Add("@MaGiangVien", OleDbType.Integer);
command.Parameters["@TenGiangVien"].Value = giangVienDTO.TenGiangVien;
command.Parameters["@DiaChi"].Value = giangVienDTO.DiaChi;
command.Parameters["@SoDienThoai"].Value = giangVienDTO.SoDienThoai;
command.Parameters["@Email"].Value = giangVienDTO.Email;
command.Parameters["@MaGiangVien"].Value = giangVienDTO.MaGiangVien;
command.ExecuteNonQuery();
connection.Close();
}
private void btnThemGiangVien_Click(object sender, EventArgs e)
{
if (txtTenGiangVienThem.Text == "")
{
MessageBox.Show("Phải nhập tên Giảng Viên");
return;
}
try
{
DialogResult result = MessageBox.Show("Bạn có chắc muốn thêm Giảng Viên này không?",
"Question",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question,
MessageBoxDefaultButton.Button1);
if (result == DialogResult.Yes)
{
GiangVienDTO giangVienDTO = new GiangVienDTO();
giangVienDTO.TenGiangVien = txtTenGiangVienThem.Text;
giangVienDTO.SoDienThoai = txtSoDienThoaiThem.Text;
giangVienDTO.DiaChi = txtDiaChiThem.Text;
giangVienDTO.Email = txtEmailThem.Text;
GiangVienBUS.Insert(giangVienDTO);
MessageBox.Show("Thêm Giảng Viên thành công");
txtTenGiangVienThem.Text = "";
txtSoDienThoaiThem.Text = "";
txtDiaChiThem.Text = "";
txtEmailThem.Text = "";
layDanhSachGiangVien();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List <GiangVienDTO> getAll(string sortBy = null, string sortType = null)
{
if (sortBy == "ghichu_GV")
{
sortBy = "CAST(" + sortBy + " AS NVARCHAR(100))";
}
List <GiangVienDTO> gvs = new List <GiangVienDTO>();
conn.Open();
string sql = "";
SqlCommand cmd;
if (sortBy != null && sortType != null)
{
sql = "select * from giangvien order by " + sortBy + " " + sortType;
cmd = new SqlCommand(sql, conn);
//cmd.Parameters.AddWithValue("sort", sortBy);
//cmd.Parameters.AddWithValue("type", sortType);
}
else
{
sql = "select * from giangvien";
cmd = new SqlCommand(sql, conn);
}
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
GiangVienDTO gv = GiangVienMapper.map(dr);
gvs.Add(gv);
}
conn.Close();
return(gvs);
}
public static List <GiangVienDTO> LoadGiangVien()
{
DataTable dt = DAL.GiangVienDAL.LoadGiangVien();
if (dt == null)
{
return(null);
}
List <GiangVienDTO> list = new List <GiangVienDTO>();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
GiangVienDTO obj = new GiangVienDTO();
obj.MAGV = dt.Rows[i]["MAGV"].ToString();
obj.HO = dt.Rows[i]["HO"].ToString();
obj.TEN = dt.Rows[i]["TEN"].ToString();
obj.MAKH = dt.Rows[i]["MAKH"].ToString();
list.Add(obj);
}
}
return(list);
}
public static void Insert(GiangVienDTO giangVienDTO)
{
GiangVienDAO.Insert(giangVienDTO);
}
public GiangVienDialog_chiTiet(GiangVienDTO g)
{
gv = g;
InitializeComponent();
}
public bool update(GiangVienDTO gv)
{
gv.Updated = DateTime.Now;
return(GiangVienDAL.Instance.update(gv));
}
public List <LopHocDTO> getClassList(GiangVienDTO gv)
{
return(GiangVienDAL.Instance.getClassList(gv));
}
public static void UpdateRecord(GiangVienDTO giangVienDTO)
{
GiangVienDAO.UpdateRecord(giangVienDTO);
}
