public bool suaTDCM(int maNV, string nganh, string trinhdo, string loaiHinhDT, string TruongDT)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlUpdate = "update TrinhDoChuyenMon set trinhdo=@trinhdo, loaihinhdaotao=@loaihinhDT, truongdaotao=@truongdt where manhanvien=@ma and nganh=@nganh";
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maNV);
cmd.Parameters.AddWithValue("nganh", nganh);
cmd.Parameters.AddWithValue("trinhdo", trinhdo);
cmd.Parameters.AddWithValue("loaihinhDT", loaiHinhDT);
cmd.Parameters.AddWithValue("truongdt", TruongDT);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public List <ChucVuDTO> LayTenChucVuTheoMaNhanVien(int manv)
{
List <ChucVuDTO> ds = new List <ChucVuDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select chucvu.machucvu as 'machucvu',tenchucvu from nhanvien_chucvu " +
"inner join chucvu on nhanvien_chucvu.machucvu=chucvu.machucvu " +
"where nhanvien_chucvu.manhanvien=@ma " +
"group by chucvu.machucvu,TenChucVu";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("ma", manv);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ChucVuDTO nvcv = new ChucVuDTO(
Convert.ToInt32(dr["machucvu"]),
dr["tenchucvu"].ToString()
);
ds.Add(nvcv);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public List <NhanVienChucVuDTO> LayNhanVienChucVuTheoMaNVMaCV(int manv, int macv)
{
List <NhanVienChucVuDTO> ds = new List <NhanVienChucVuDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select tungay from nhanvien_chucvu where manhanvien=@manv and machucvu=@macv";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("manv", manv);
cmd.Parameters.Add("macv", macv);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DateTime dt = DateTime.Parse(dr["tungay"].ToString());
NhanVienChucVuDTO nv = new NhanVienChucVuDTO(
manv, macv,
dt.ToString("dd/MM/yyyy"),
""
);
ds.Add(nv);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public NhanVienChucVuDTO LayNhanVienChucVuTheoMaNVMaCVTuNgay(int maNV, int maCV, string tungay)
{
NhanVienChucVuDTO nhanVienChucVu = new NhanVienChucVuDTO();
KetNoiCSDL.MoKetNoi();
DateTime dt = DateTime.ParseExact(tungay, "dd/MM/yyyy", null);
string sqlSelect = "Select * from nhanvien_chucvu where manhanvien=@manv and machucvu=@macv and tungay=@ngay";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("manv", maNV);
cmd.Parameters.Add("macv", maCV);
cmd.Parameters.Add("ngay", dt.ToString("MM/dd/yyyy"));
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DateTime date = DateTime.Parse(dr["denngay"].ToString());
nhanVienChucVu.MaNhanVien = maNV;
nhanVienChucVu.MaChucVu = maCV;
nhanVienChucVu.TuNgay = tungay;
nhanVienChucVu.DenNgay = date.ToString("dd/MM/yyyy");
}
KetNoiCSDL.DongKetNoi();
return(nhanVienChucVu);
}
public bool ThemQuaTrinhCongTac(int maNhanVien, string tuNgay, string denNgay, string noiCongTac, string chucvu)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlInsert = "insert into QuaTrinhCongTac values(@maNV,@tuNgay,@denNgay, @noict, @chucvu)";
SqlCommand cmd = new SqlCommand(sqlInsert, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("maNV", maNhanVien);
cmd.Parameters.Add("tuNgay", tuNgay);
cmd.Parameters.Add("denNgay", denNgay);
cmd.Parameters.Add("noict", noiCongTac);
cmd.Parameters.Add("chucvu", chucvu);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public List <DoanhSoTheoNgay> layDoanhSo(string thang, string nam)
{
List <DoanhSoTheoNgay> arr = new List <DoanhSoTheoNgay>();
KetNoiCSDL.moKetNoi();
string get = "SELECT ngayLap,SUM(soLuong*donGia) as 'gia' FROM HoaDon inner join HoaDonChiTiet on HoaDon.maHoaDon = HoaDonChiTiet.maHoaDon inner join Hang on Hang.maHang = HoaDonChiTiet.maHang where MONTH(ngayLap) = @thang and YEAR(ngayLap) = @nam group by ngayLap";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("thang", int.Parse(thang));
cmd.Parameters.AddWithValue("nam", int.Parse(nam));
SqlDataReader dr = cmd.ExecuteReader();
int stt = 1;
while (dr.Read())
{
DoanhSoTheoNgay ds = new DoanhSoTheoNgay(stt + "", ((DateTime)dr["ngayLap"]).ToString("dd/MM/yyyy"), float.Parse(dr["gia"] + ""));
arr.Add(ds);
stt++;
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public bool themTDCM(int maNV, string nganh, string trinhdo, string loaiHinhDT, string TruongDT)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlInsert = "insert into TrinhDoChuyenMon values (@ma, @nganh, @trinhdo, @loaihinhDT, @truongdt)";
SqlCommand cmd = new SqlCommand(sqlInsert, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maNV);
cmd.Parameters.AddWithValue("nganh", nganh);
cmd.Parameters.AddWithValue("trinhdo", trinhdo);
cmd.Parameters.AddWithValue("loaihinhDT", loaiHinhDT);
cmd.Parameters.AddWithValue("truongdt", TruongDT);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public List <QuaTrinhCongTacDTO> LayDSQuaTrinhCongTacTheoMaNV(int maNV)
{
List <QuaTrinhCongTacDTO> ds = new List <QuaTrinhCongTacDTO>();
KetNoiCSDL.MoKetNoi();
string sqlselect = "select * from QuaTrinhCongTac" +
" inner join HoSo on QuaTrinhCongTac.MaNhanVien=HoSo.MaNhanVien where QuaTrinhCongTac.MaNhanVien=@ma";
SqlCommand cmd = new SqlCommand(sqlselect, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("ma", maNV);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DateTime dt = DateTime.Parse(dr["tungay"].ToString());
DateTime dt2 = DateTime.Parse(dr["denngay"].ToString());
QuaTrinhCongTacDTO qt = new QuaTrinhCongTacDTO(
Convert.ToInt32(dr["manhanvien"].ToString()),
dt.ToString("dd/MM/yyyy"),
dt2.ToString("dd/MM/yyyy"),
dr["noicongtac"].ToString(),
dr["chucvu"].ToString()
);
ds.Add(qt);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public bool suaHoSo(string manv, string hoDem, string ten, string ngaySinh, string gioiTinh, string sdt, string email, string ngayTuyenDung, int maPhongBan)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlUpdate = "update HoSo set hodem =@ho, tendem= @ten, ngaysinh=@ns, gioitinh=@gt, sodienthoai=@sdt,email=@email, ngaytuyendung=@ntd, maphongban=@mapb where manhanvien=@ma";
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", manv);
cmd.Parameters.AddWithValue("ho", hoDem);
cmd.Parameters.AddWithValue("ten", ten);
cmd.Parameters.AddWithValue("ns", ngaySinh);
cmd.Parameters.AddWithValue("gt", gioiTinh);
cmd.Parameters.AddWithValue("sdt", sdt);
cmd.Parameters.AddWithValue("email", email);
cmd.Parameters.AddWithValue("ntd", ngayTuyenDung);
cmd.Parameters.AddWithValue("mapb", maPhongBan);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception e)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public QuaTrinhCongTacDTO LayBangQuaTrinhCongTacTheoMaNVTuNgay(int maNV, string tuNgay)
{
QuaTrinhCongTacDTO ds = null;
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select * from QuaTrinhcongTac where manhanvien=@ma and tungay=@tungay";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("ma", maNV);
cmd.Parameters.Add("tungay", tuNgay);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DateTime date1 = DateTime.Parse(dr["tungay"].ToString());
DateTime date2 = DateTime.Parse(dr["denngay"].ToString());
ds = new QuaTrinhCongTacDTO(
Convert.ToInt32(dr["manhanvien"]),
date1.ToString("dd/MM/yyyy"),
date2.ToString("dd/MM/yyyy"),
dr["noicongtac"].ToString(),
dr["chucvu"].ToString());
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public List <HoSoDTO> LayBangHoSo()
{
List <HoSoDTO> ds = new List <HoSoDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select * from hoso";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DateTime date2 = DateTime.Parse(dr["ngaytuyendung"].ToString());
DateTime date3 = DateTime.Parse(dr["ngaysinh"].ToString());
HoSoDTO hs = new HoSoDTO(
Convert.ToInt32(dr["manhanvien"]),
dr["hodem"].ToString(),
dr["tendem"].ToString(),
date3.ToString("dd/MM/yyyy"),
dr["gioitinh"].ToString(),
dr["sodienthoai"].ToString(),
dr["email"].ToString(),
date2.ToString("dd/MM/yyyy"),
Convert.ToInt32(dr["maphongban"])
);
ds.Add(hs);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public HoSoDTO LayNhanVienTheoMa(int maNhanVien)
{
HoSoDTO hs = null;
KetNoiCSDL.MoKetNoi();
string sqlSelect = "Select * from hoso where manhanvien=@ma";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maNhanVien);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
hs = new HoSoDTO(
Convert.ToInt32(dr["manhanvien"]),
dr["hodem"].ToString(),
dr["tendem"].ToString(),
dr["ngaysinh"].ToString(),
dr["gioitinh"].ToString(),
dr["sodienthoai"].ToString(),
dr["email"].ToString(),
dr["ngaytuyendung"].ToString(),
Convert.ToInt32(dr["maphongban"])
);
KetNoiCSDL.DongKetNoi();
return(hs);
}
public bool themHoSo(string hoDem, string ten, string ngaySinh, string gioiTinh, string sdt, string email, string ngayTuyenDung, int maPhongBan)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlInsert = "insert into HoSo values (@ho, @ten, @ns, @gt, @sdt, @email, @ntd, @mapb)";
SqlCommand cmd = new SqlCommand(sqlInsert, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ho", hoDem);
cmd.Parameters.AddWithValue("ten", ten);
cmd.Parameters.AddWithValue("ns", ngaySinh);
cmd.Parameters.AddWithValue("gt", gioiTinh);
cmd.Parameters.AddWithValue("sdt", sdt);
cmd.Parameters.AddWithValue("email", email);
cmd.Parameters.AddWithValue("ntd", ngayTuyenDung);
cmd.Parameters.AddWithValue("mapb", maPhongBan);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public List <TrinhDoChuyenMonDTO> LayDSTDCMtheoMa(int maNV)
{
List <TrinhDoChuyenMonDTO> ds = new List <TrinhDoChuyenMonDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select * from trinhdochuyenmon where manhanvien = @ma";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maNV);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TrinhDoChuyenMonDTO td = new TrinhDoChuyenMonDTO(
Convert.ToInt32(dr["manhanvien"]),
dr["nganh"].ToString(),
dr["trinhdo"].ToString(),
dr["loaihinhdaotao"].ToString(),
dr["truongdaotao"].ToString()
);
ds.Add(td);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public bool ThemNhanVienChucVu(int maNhanVien, int maChucVu, string tuNgay, string denNgay)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlInsert = "insert into nhanvien_chucvu values(@maNV,@maCV,@tuNgay,@denNgay)";
SqlCommand cmd = new SqlCommand(sqlInsert, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("maNV", maNhanVien);
cmd.Parameters.Add("maCV", maChucVu);
cmd.Parameters.Add("tuNgay", tuNgay);
cmd.Parameters.Add("denNgay", denNgay);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool SuaQuaTrinhCongTac(int maNhanVien, string tuNgay, string denNgay, string noiCongTac, string chucvu)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlUpdate = "update QuaTrinhCongTac set denNgay=@denNgay, noicongtac=@noict, chucVu= @chucvu where maNhanVien=@maNV and tuNgay=@tuNgay";
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("maNV", maNhanVien);
cmd.Parameters.Add("tuNgay", tuNgay);
cmd.Parameters.Add("denNgay", denNgay);
cmd.Parameters.Add("noict", noiCongTac);
cmd.Parameters.Add("chucvu", chucvu);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public void xoaNhaCungCap(NhaCungCap ncc)
{
KetNoiCSDL.moKetNoi();
string xoa = "delete from NhaCungCap where maNhaCungCap=@maNhaCungCap";
SqlCommand cmd = new SqlCommand(xoa, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhaCungCap", ncc.MaNhaCungCap);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaNhanVien(NhanVien nv)
{
KetNoiCSDL.moKetNoi();
string xoa = "delete from NhanVien where maNhanVien=@maNhanVien";
SqlCommand cmd = new SqlCommand(xoa, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhanVien", nv.MaNhanVien);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaLop(Lop l)
{
string sqlUpdate = "delete from Lop where maLop=@maLop";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLop", l.MaLop);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaLoaiHang(LoaiHang lh)
{
string sqlUpdate = "delete from LoaiHang where maLoai=@maLoai";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLoai", lh.MaLoai);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaMonHoc(MonHoc mh)
{
string sqlUpdate = "delete from MonHoc where maMon=@maMon";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maMon", mh.MaMon);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaLichSuGia(LichSuGia lsg)
{
KetNoiCSDL.moKetNoi();
string del = "delete from LichSuGia where maHang=@maHang and ngayBatDau=@ngayBatDau ";
SqlCommand cmd = new SqlCommand(del, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHang", lsg.MaHang);
cmd.Parameters.AddWithValue("ngayBatDau", lsg.NgayBatDau);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaHang(Hang h)
{
string sqlUpdate = "delete from Hang where maHang=@maHang";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHang", h.MaHang);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaCanBoGiaoVien(CanBoGiaoVien cb)
{
string sqlUpdate = "delete from CanBoGiaoVien where maCBGV=@maCBGV";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maCBGV", cb.MaCBGV);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaHoSoHocSinh(HoSoHocSinh hs)
{
string sqlUpdate = "delete from HoSoHocSinh where maHocSinh=@maHocSinh";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHocSinh", hs.MaHocSinh);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public DataTable layHoSoHocSinhCoTenLop()
{
KetNoiCSDL.moKetNoi();
string get = "select maHocSinh ,hoTen ,ngaySinh, gioiTinh, diaChi, diemVaoTruong, hoTenBoMe, soDienThoai ,tenLop from HoSoHocSinh inner join Lop on HoSoHocSinh.maLop=Lop.maLop";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
public DataTable LayThongTinTrinhDoCHuyenMongopLHDT()
{
DataTable dtTrinhDo = new DataTable();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select loaihinhdaotao from trinhdochuyenmon group by loaihinhdaotao";
SqlDataAdapter daTrinhDo = new SqlDataAdapter(sqlSelect, KetNoiCSDL.KetNoi);
daTrinhDo.Fill(dtTrinhDo);
KetNoiCSDL.DongKetNoi();
return(dtTrinhDo);
}
public DataTable LayThongTinTrinhDoNNGopNN()
{
DataTable dtTrinhDo = new DataTable();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select ngoaingu from trinhdongoaingu group by ngoaingu";
SqlDataAdapter daTrinhDo = new SqlDataAdapter(sqlSelect, KetNoiCSDL.KetNoi);
daTrinhDo.Fill(dtTrinhDo);
KetNoiCSDL.DongKetNoi();
return(dtTrinhDo);
}
/* public List<PhanCongGiangDay> layMonHoc()
* {
* List<PhanCongGiangDay> arr = new List<PhanCongGiangDay>();
* KetNoiCSDL.moKetNoi();
* string get = "select * from PhanCongGiangDay";
* SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
* SqlDataReader dr = cmd.ExecuteReader();
* while (dr.Read())
* {
* PhanCongGiangDay x = new PhanCongGiangDay(dr["maLop"] + "", dr["maMon"] + "", dr["maCBGV"] + "", ((DateTime)dr["ngayPhanCong"]).ToString("dd/MM/yyyy"));
* arr.Add(x);
* }
* KetNoiCSDL.dongKetNoi();
* return arr;
* }*/
public DataTable layPhanCongGiangDayTheoTen()
{
KetNoiCSDL.moKetNoi();
string get = "select tenLop ,tenMon ,hoTen, ngayPhanCong from PhanCongGiangDay inner join MonHoc on PhanCongGiangDay.maMon=MonHoc.maMon inner join CanBoGiaoVien on CanBoGiaoVien.maCBGV = PhanCongGiangDay.maCBGV inner join Lop on Lop.maLop = PhanCongGiangDay.maLop";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
public DataTable layLopCoTen()
{
KetNoiCSDL.moKetNoi();
string get = "select maLop ,tenLop ,nienKhoa, siSo, hoTen from CanBoGiaoVien inner join Lop on CanBoGiaoVien.maCBGV=Lop.maCBGV";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}