//check login public int CheckLogin(String ID, String Pass) { //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = "select ID,Pass,MaCapQuanLi,MaNhanVien from NhanVien where ID=@ID and Pass=@Pass"; // String query1 = "select ID,Pass from NhanVien "; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@ID", ID); command.Parameters.AddWithValue("@Pass", Pass); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); if (dt.Rows.Count == 0) { return 0; } else { UserID = dt.Rows[0][1].ToString(); MaNV=Convert.ToInt32(dt.Rows[0][3].ToString()); return Int32.Parse(dt.Rows[0][2].ToString()); } }
//Doi mat khau public bool ChangePass(int manhanvien,string passCu,string passMoi ) { try { DBConnect cn = new DBConnect(); cn.conn.Open(); string sql = "select count(*) as MaxNumber from NhanVien where Pass=@Pass and MaNhanVien=@MaNhanVien"; SqlCommand cmd = new SqlCommand(sql, cn.conn); cmd.Parameters.AddWithValue("@MaNhanVien", manhanvien); cmd.Parameters.AddWithValue("@Pass", passCu); cmd.ExecuteNonQuery(); int rows = Convert.ToInt32(cmd.ExecuteScalar()); cn.conn.Close(); if (rows == 1) { cn.conn.Open(); sql = "update NhanVien set Pass=@PassMoi where Pass=@Pass and MaNhanVien=@MaNhanVien"; cmd = new SqlCommand(sql, cn.conn); cmd.Parameters.AddWithValue("@MaNhanVien", manhanvien); cmd.Parameters.AddWithValue("@Pass", passCu); cmd.Parameters.AddWithValue("@PassMoi", passMoi); cmd.ExecuteNonQuery(); cn.conn.Close(); return true; } else { return false; } } catch (Exception ex) { return false; } }
private void btnDangNhap_Click(object sender, EventArgs e) { //aa DBConnect cn = new DBConnect(); string txtID = txtId.Text; string txtPass = txtPASS.Text; EmployeeManagement employee = new EmployeeManagement(); cn.conn.Open(); if (employee.CheckLogin(txtID, txtPass) == 3) { frmNhanVien NV = new frmNhanVien(); NV.Show(); } else if (employee.CheckLogin(txtID, txtPass) == 0) { MessageBox.Show("Dữ liệu không hợp lệ"); } else { frmQuanLi QL = new frmQuanLi(); QL.Show(); } cn.conn.Close(); }
// load du lieu bang thay doi public DataTable LoadThayDoi() { DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"select [NgayThayDoi],[NoiDung] ,[ID] ,[TenCongTac] from ThayDoi "; SqlCommand command = new SqlCommand(query, cn.conn); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); return dt; }
public void PassData(List<MaQuanLi> list) { //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = "select ID,MaNhanVien from NhanVien "; // String query1 = "select ID,Pass from NhanVien "; SqlCommand command = new SqlCommand(query, cn.conn); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); for (int i = 0; i < dt.Rows.Count; i++) { MaQuanLi a = new MaQuanLi(dt.Rows[i]["ID"].ToString(), Convert.ToInt32(dt.Rows[i]["MaNhanVien"].ToString())); list.Add(a); } cn.conn.Close(); }
//insert data NV public void InsertdataNV(String ID,String Pass, String Hoten, String SDT, String DC, String GT,String email,int maQL,int maNQL) { try { //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); //DataTable dt = new DataTable(); String query = @"insert into NhanVien([HoTen] ,[SDT] ,[DiaChi] ,[GioiTinh] ,[Email] ,[MaNguoiQuanLi] ,[MaCapQuanLi] ,[ID] ,[Pass]) values (@HoTen ,@SDT ,@DiaChi ,@GioiTinh ,@Email ,@MaNguoiQuanLi ,@MaCapQuanLi ,@ID ,@Pass) "; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@ID", ID); command.Parameters.AddWithValue("@Hoten", Hoten); command.Parameters.AddWithValue("@SDT", SDT); command.Parameters.AddWithValue("@DiaChi", DC); command.Parameters.AddWithValue("@GioiTinh", GT); command.Parameters.AddWithValue("@Email", email); command.Parameters.AddWithValue("@MaCapQuanLi", maQL); command.Parameters.AddWithValue("@MaNguoiQuanLi", maNQL); command.Parameters.AddWithValue("@Pass", Pass); command.ExecuteNonQuery(); // SqlDataAdapter da = new SqlDataAdapter(command); // da.Fill(dt); cn.conn.Close(); //return dt; MessageBox.Show("Thêm thành công", "Thông báo"); } catch (Exception ex) { MessageBox.Show("Xảy ra lỗi"+ex,"Thông báo"); } }
//Lay du lieu NV public void LoadNVintoCombo(ComboBox combo) { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @"select MaNhanVien from NhanVien where [MaCapQuanLi]<3"; SqlCommand command = new SqlCommand(query, cn.conn); //command.ExecuteNonQuery(); SqlDataReader sqlReader = command.ExecuteReader(); while (sqlReader.Read()) { combo.Items.Add(sqlReader["MaNhanVien"].ToString()); } cn.conn.Close(); }
//Update data Nhan Vien public void UpdatedataNV(String ID,String Hoten,String SDT,String DC,String GT) { //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); //DataTable dt = new DataTable(); String query = @"update NhanVien set Hoten=@Hoten ,SDT=@SDT,DiaChi=@DiaChi,GioiTinh=@GioiTinh where ID=@ID"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@ID", ID); command.Parameters.AddWithValue("@Hoten", Hoten); command.Parameters.AddWithValue("@SDT", SDT); command.Parameters.AddWithValue("@DiaChi", DC); command.Parameters.AddWithValue("@GioiTinh", GT); command.ExecuteNonQuery(); // SqlDataAdapter da = new SqlDataAdapter(command); // da.Fill(dt); cn.conn.Close(); //return dt; }
//them du lieu bang phu NV_NN public void insertNV_NN(int manhanvien,int manhacnho) { try { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @"insert into NhacNho_NhanVien([MaNhanVien],[MaNhacNho],CapDoNhac) values(@MaNhanVien,@MaNhacNho,@CapDoNhac) "; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@MaNhanVien", manhanvien); command.Parameters.AddWithValue("@MaNhacNho", manhacnho); command.Parameters.AddWithValue("@CapDoNhac", 1); command.ExecuteNonQuery(); cn.conn.Close(); } catch (Exception ex) { MessageBox.Show("Thất bại!"+ex, "Thông báo"); } }
//load data Nhan Vien public DataTable LoaddataNV(String ID) { //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = "select Hoten,SDT,DiaChi,GioiTinh from NhanVien where ID=@ID"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@ID", ID); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); return dt; }
//Them CT public bool ThemCongTac(string TenCongTac, DateTime NgayBatDau, DateTime NgayKetThuc, string DiaDiem, string NoiDung, int MaLoaiCongTac) { try { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @"insert into LichCongTac(TenCongTac,NgayBatDau,NgayKetThuc,DiaDiem,NoiDung,MaLoaiCongTac) values(@TenCongTac,@NgayBatDau,@NgayKetThuc,@DiaDiem,@NoiDung,@MaLoaiCongTac)"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@TenCongTac", TenCongTac); command.Parameters.AddWithValue("@NgayBatDau", NgayBatDau); command.Parameters.AddWithValue("@NgayKetThuc", NgayKetThuc); command.Parameters.AddWithValue("@DiaDiem", DiaDiem); command.Parameters.AddWithValue("@NoiDung", NoiDung); command.Parameters.AddWithValue("@MaLoaiCongTac", MaLoaiCongTac); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); cn.conn.Close(); return true; }catch(Exception ex){ return false; } }
//load du lieu lich public DataTable LoadDataLich() { //select TenCongTac,NgayBatDau,NgayKetThuc,NoiDung from LichCongTac l , //NhanVien nv,NhanVien_CongTac nvct //where nv.ID='nam' and nv.MaNhanVien=nvct.MaNhanVien and nvct.MaCongTac=l.MaCongTac //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"select MaCongTac,TenCongTac,NgayBatDau,NgayKetThuc,DiaDiem,NoiDung from LichCongTac "; SqlCommand command = new SqlCommand(query, cn.conn); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); return dt; }
// public DataTable LoaddataNN(String ID) { //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"select nn.MaNhacNho,NgayNhac,TenNhacNho ,NoiDung,MaCongTac,NguoiGui from NhacNho nn,NhanVien nv, NhacNho_NhanVien n where nv.MaNhanVien=n.MaNhanVien and n.MaNhacNho=nn.MaNhacNho and nv.ID=@ID and n.GhiChu is null"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@ID", ID); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); return dt; }
//Load data LichCT public DataTable LoadDataLich(String ID,List<LichCongTac> ListLich) { //select TenCongTac,NgayBatDau,NgayKetThuc,NoiDung from LichCongTac l , //NhanVien nv,NhanVien_CongTac nvct //where nv.ID='nam' and nv.MaNhanVien=nvct.MaNhanVien and nvct.MaCongTac=l.MaCongTac //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"select TenCongTac,NgayBatDau,NgayKetThuc,DiaDiem,NoiDung from LichCongTac l ,NhanVien nv,NhanVien_CongTac nvct where nv.MaNhanVien=nvct.MaNhanVien and nvct.MaCongTac=l.MaCongTac and ID=@ID"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@ID", ID); command.ExecuteNonQuery(); //lich. SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); //return dt; for (int i = 0; i<dt.Rows.Count; i++) { LichCongTac a = new LichCongTac(dt.Rows[i][0].ToString(), DateTime.Parse(dt.Rows[i][1].ToString()), DateTime.Parse(dt.Rows[i][2].ToString()), dt.Rows[i][3].ToString(), dt.Rows[i][4].ToString()); ListLich.Add(a); } return dt; }
//Lay du lieu CT public void LoadCTintoCombo(ComboBox combo) { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @"select MaCongTac from LichCongTac"; SqlCommand command = new SqlCommand(query, cn.conn); //command.ExecuteNonQuery(); SqlDataReader sqlReader = command.ExecuteReader(); while (sqlReader.Read()) { combo.Items.Add(sqlReader["MaCongTac"].ToString()); } cn.conn.Close(); }
public string insertNhacNho(DateTime ngaynhac, string tieude, string noidung, int macongtac) { try { DBConnect cn = new DBConnect(); cn.conn.Open(); SqlCommand cmd =new SqlCommand(); cmd.Connection = cn.conn; SqlDataReader rdr = null; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "pro_insertNN"; cmd.Parameters.AddWithValue("@NgayNhac", ngaynhac); cmd.Parameters.AddWithValue("@TenNhacNho", tieude); cmd.Parameters.AddWithValue("@NoiDung", noidung); cmd.Parameters.AddWithValue("@MaCongTac", macongtac); cmd.Parameters.AddWithValue("@NguoiGui", EmployeeManagement.UserID); cmd.Parameters.Add("@MaNhacNho", SqlDbType.VarChar, 100); cmd.Parameters["@MaNhacNho"].Direction = ParameterDirection.Output; rdr=cmd.ExecuteReader(); string outputValue = cmd.Parameters["@MaNhacNho"].Value.ToString(); cn.conn.Close(); return outputValue; } catch (Exception ex) { MessageBox.Show("Thất bại!"+ex, "Thông báo"); return ""; } }
//Sua CT public bool SuaCongTac(int id ,string TenCongTac, DateTime NgayBatDau, DateTime NgayKetThuc, string DiaDiem, string NoiDung, int MaLoaiCongTac) { try { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @"update LichCongTac set TenCongTac=@TenCongTac,NgayBatDau=@NgayBatDau, NgayKetThuc=@NgayKetThuc,DiaDiem=@DiaDiem,NoiDung=@NoiDung,MaLoaiCongTac=@MaLoaiCongTac where MaCongTac=@id "; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@TenCongTac", TenCongTac); command.Parameters.AddWithValue("@NgayBatDau", NgayBatDau); command.Parameters.AddWithValue("@NgayKetThuc", NgayKetThuc); command.Parameters.AddWithValue("@DiaDiem", DiaDiem); command.Parameters.AddWithValue("@NoiDung", NoiDung); command.Parameters.AddWithValue("@MaLoaiCongTac", MaLoaiCongTac); command.Parameters.AddWithValue("@id", id); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); cn.conn.Close(); return true; } catch (Exception ex) { return false; } }
//load du lieu nhan vien public DataTable LoadNV() { DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"select * from NhanVien "; SqlCommand command = new SqlCommand(query, cn.conn); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); return dt; }
//insert du lieu bang thay doi public void ThayDoi(DateTime ngaythaydoi,string noidung,string id,string tencongtac) { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @"insert into ThayDoi([NgayThayDoi] ,[NoiDung] ,[ID] ,[TenCongTac]) values(@NgayThayDoi, @NoiDung,@id,@TenCongTac)"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@NgayThayDoi", ngaythaydoi); command.Parameters.AddWithValue("@NoiDung", noidung); command.Parameters.AddWithValue("@id", id); command.Parameters.AddWithValue("@TenCongTac", tencongtac); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); cn.conn.Close(); }
//Load NV can phan hoi public void UpdateNVCanPH(int manhanvien) { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @" update NhacNho_NhanVien set CapDoNhac=CapDoNhac+1 where MaNhanVien=@MaNhanVien"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@MaNhanVien", manhanvien); command.ExecuteNonQuery(); cn.conn.Close(); }
//Thong bao cap tren public string ThongbaoNVCanPH(int manhanvien) { try { DBConnect cn = new DBConnect(); cn.conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn.conn; SqlDataReader rdr = null; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "pro_insertNN"; cmd.Parameters.AddWithValue("@NgayNhac", DateTime.Now); cmd.Parameters.AddWithValue("@TenNhacNho", "Nhac Nho Nhan Vien"); cmd.Parameters.AddWithValue("@NoiDung", "Nhan vien Ma so :"+manhanvien+"chua hoan thanh nhiem vu"); cmd.Parameters.AddWithValue("@MaCongTac", 1); cmd.Parameters.AddWithValue("@NguoiGui", EmployeeManagement.UserID); cmd.Parameters.Add("@MaNhacNho", SqlDbType.VarChar, 100); cmd.Parameters["@MaNhacNho"].Direction = ParameterDirection.Output; rdr = cmd.ExecuteReader(); string outputValue = cmd.Parameters["@MaNhacNho"].Value.ToString(); cn.conn.Close(); return outputValue; } catch (Exception ex) { MessageBox.Show("Thất bại!" + ex, "Thông báo"); return ""; } }
public void DeletedataNV(int MaNV) { try { //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); //DataTable dt = new DataTable(); String query = @"delete from NhanVien where MaNhanVien=@MaNV"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@MaNV", MaNV); command.ExecuteNonQuery(); // SqlDataAdapter da = new SqlDataAdapter(command); // da.Fill(dt); cn.conn.Close(); //return dt; MessageBox.Show("Xóa thành công", "Thông báo"); } catch (Exception ex) { MessageBox.Show("Xảy ra lỗi"+ex,"Thông báo"); } }
//LoadNV public DataTable LoadNV(int maCT) { //aaa DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"select * from NhanVien nv, LichCongTac l, NhanVien_CongTac n where nv.MaNhanVien=n.MaNhanVien and n.MaCongTac=l.MaCongTac and l.MaCongTac=@maCT"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@maCT", maCT); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); return dt; }
//Tham so khac public void insertNV_CT(int manhanvien, int macongtac) { try { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @"insert into NhanVien_CongTac([MaNhanVien],[MaCongTac]) values(@MaNhanVien,@MaCongTac) "; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@MaNhanVien", manhanvien); command.Parameters.AddWithValue("@MaCongTac", macongtac); command.ExecuteNonQuery(); cn.conn.Close(); } catch (Exception ex) { MessageBox.Show("Thất bại!", "Thông báo"); } }
//xoa cong tac public bool XoaCongTac(int id) { try { DBConnect cn = new DBConnect(); cn.conn.Open(); String query = @"delete LichCongTac where MaCongTac=@id "; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@id", id); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); cn.conn.Close(); return true; } catch (Exception ex) { return false; } }
//Nhan vien duoi quyen public DataTable LoadNVCanPH() { DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"select distinct nv.MaNhanVien,nv.ID,nv.MaCapQuanLi,nv.MaNguoiQuanLi,n.CapDoNhac from NhacNho nn,NhanVien nv, NhacNho_NhanVien n where nv.MaNhanVien=n.MaNhanVien and n.MaNhacNho=nn.MaNhacNho and nn.NguoiGui=@NguoiGui and n.GhiChu is null"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@NguoiGui", EmployeeManagement.UserID); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); return dt; }
//Nhan vien can phan hoi public DataTable LoadNVDuoiQuyen() { DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"select * from NhanVien where MaNguoiQuanLi=@MaNguoiQuanLi"; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@MaNguoiQuanLi", EmployeeManagement.MaNV); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); return dt; }
public void GuiPhanHoi(int manhacnho ,string GhiChu) { try { //string setthuoctinh = @""; DBConnect cn = new DBConnect(); cn.conn.Open(); DataTable dt = new DataTable(); String query = @"update NhacNho_NhanVien set GhiChu=@GhiChu, CapDoNhac=@CapDoNhac where MaNhacNho=@MaNhacNho and MaNhanVien=@MaNhanVien "; SqlCommand command = new SqlCommand(query, cn.conn); command.Parameters.AddWithValue("@MaNhacNho", manhacnho); command.Parameters.AddWithValue("@MaNhanVien", EmployeeManagement.MaNV); command.Parameters.AddWithValue("@GhiChu", GhiChu); command.Parameters.AddWithValue("@CapDoNhac", 0); command.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); cn.conn.Close(); MessageBox.Show("Phản hồi thành công" , "Thông báo"); } catch(Exception ex){ MessageBox.Show("Thất bại!" + ex, "Thông báo"); } }