public List <TrinhDoNgoaiNguDTO> DemTrinhDoTheoTen()
{
List <TrinhDoNgoaiNguDTO> ds = new List <TrinhDoNgoaiNguDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select ngoaingu,count(*) as 'tong' from TrinhDoNgoaiNgu group by ngoaingu";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TrinhDoNgoaiNguDTO nn = new TrinhDoNgoaiNguDTO(dr["ngoaingu"].ToString(), Convert.ToInt32(dr["tong"]));
ds.Add(nn);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public DataTable LayThongTinQuaTrinhCongTacTheoMaNV(int maNV)
{
DataTable dtQuaTrinhCongTac = new DataTable();
KetNoiCSDL.MoKetNoi();
string sqlselect = "select QuaTrinhCongTac.MaNhanVien,{ fn concat(hodem + ' ',TenDem) } as 'Hoten', " +
"QuaTrinhCongTac.TuNgay, QuaTrinhCongTac.DenNgay, QuaTrinhCongTac.NoiCongTac," +
" QuaTrinhCongTac.ChucVu from QuaTrinhCongTac" +
" inner join HoSo on QuaTrinhCongTac.MaNhanVien=HoSo.MaNhanVien where QuaTrinhCongTac.MaNhanVien=@ma";
SqlDataAdapter adQuaTrinhCongTac = new SqlDataAdapter(sqlselect, KetNoiCSDL.KetNoi);
adQuaTrinhCongTac.SelectCommand.Parameters.AddWithValue("ma", maNV);
adQuaTrinhCongTac.Fill(dtQuaTrinhCongTac);
KetNoiCSDL.DongKetNoi();
return(dtQuaTrinhCongTac);
}
public List <TrinhDoChuyenMonDTO> DemTrinhDoTheoTen()
{
List <TrinhDoChuyenMonDTO> ds = new List <TrinhDoChuyenMonDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select trinhdo,count(*) as 'tong' from trinhdochuyenmon group by TrinhDo";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TrinhDoChuyenMonDTO cm = new TrinhDoChuyenMonDTO(dr["trinhdo"].ToString(), Convert.ToInt32(dr["tong"]));
ds.Add(cm);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public List <PhongBanDTO> LayPhongBan()
{
List <PhongBanDTO> ds = new List <PhongBanDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select * from phongban";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DateTime dt = DateTime.Parse(dr["ngaylap"].ToString());
PhongBanDTO pb = new PhongBanDTO(Convert.ToInt32(dr["maphongban"]), dr["tenphongban"].ToString(), dt.ToString("dd/MM/yyyy"));
ds.Add(pb);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public List <TrinhDoChuyenMonDTO> LayDSTrinhDo()
{
List <TrinhDoChuyenMonDTO> ds = new List <TrinhDoChuyenMonDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select trinhdo from trinhdochuyenmon group by trinhdo ";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TrinhDoChuyenMonDTO td = new TrinhDoChuyenMonDTO(dr["trinhdo"].ToString());
ds.Add(td);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public bool xoaHoSo(string manv)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlDelete = "delete from HoSo where manhanvien=@ma";
SqlCommand cmd = new SqlCommand(sqlDelete, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", manv);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception e)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool XoaPhongBan(int maPB)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlDelete = "delete from phongban where maphongban=@mapb";
SqlCommand cmd = new SqlCommand(sqlDelete, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("mapb", maPB);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool XoaChucVu(int maCV)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlDelete = "delete from ChucVu where MaChucVu=@ma";
SqlCommand cmd = new SqlCommand(sqlDelete, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maCV);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool XoaQuaTrinhCongTac(int maNhanVien, string tuNgay, string denNgay, string noiCongTac, string chucvu)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlDelete = "delete QuaTrinhCongTac where maNhanVien=@maNV and tungay=@tungay";
SqlCommand cmd = new SqlCommand(sqlDelete, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("maNV", maNhanVien);
cmd.Parameters.Add("tungay", tuNgay);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool ThemChucVu(string tenChucVu, float heSoPhuCap)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlInsert = "Insert into ChucVu values(@tenCV,@heSo)";
SqlCommand cmd = new SqlCommand(sqlInsert, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("tenCV", tenChucVu);
cmd.Parameters.AddWithValue("heSo", heSoPhuCap);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public string LayTenTheoMaNhanVien(int maNV)
{
List <HoSoDTO> ds = new List <HoSoDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select hodem,tendem from hoso where manhanvien=@ma ";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("ma", maNV);
SqlDataReader dr = cmd.ExecuteReader();
string ten = "";
while (dr.Read())
{
ten = dr["hodem"].ToString() + " " + dr["tendem"].ToString();
}
KetNoiCSDL.DongKetNoi();
string x = ten;
return(ten);
}
public PhongBanDTO LayPhongBanTheoMaNV(int manv)
{
PhongBanDTO pb = null;
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select phongban.maphongban,tenphongban,ngaylap from hoso " +
"inner join phongban on hoso.maphongban=phongban.maphongban " +
"where manhanvien=@ma";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("ma", manv);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
pb = new PhongBanDTO(Convert.ToInt32(dr["maphongban"]),
dr["tenphongban"].ToString(),
dr["ngaylap"].ToString());
KetNoiCSDL.DongKetNoi();
return(pb);
}
public bool XoaNhanVienChucVu(int maNV, int maCV, string tuNgay)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlDelete = "delete from nhanvien_chucvu where manhanvien=@maNV and machucvu=@maCV and tungay=@tu";
SqlCommand cmd = new SqlCommand(sqlDelete, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("maNV", maNV);
cmd.Parameters.Add("maCV", maCV);
cmd.Parameters.Add("tu", tuNgay);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool SuaChucVu(int maCV, string tenCV, float heSoPhuCap)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlUpdate = "update ChucVu set TenChucVu=@ten,HeSoPhuCap=@heSo where MaChucVu=@ma";
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maCV);
cmd.Parameters.AddWithValue("ten", tenCV);
cmd.Parameters.AddWithValue("heSo", heSoPhuCap);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public List <ChucVuDTO> LayBangChucVu()
{
List <ChucVuDTO> ds = new List <ChucVuDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select * from chucvu";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ChucVuDTO cv = new ChucVuDTO(
Convert.ToInt32(dr["machucvu"]),
dr["tenchucvu"].ToString(),
float.Parse(dr["HeSoPhuCap"].ToString())
);
ds.Add(cv);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public bool xoaTDNN(int maNV, string ngoaingu)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlDelete = "delete trinhdongoaingu where manhanvien=@ma and ngoaingu=@ngoaingu";
SqlCommand cmd = new SqlCommand(sqlDelete, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maNV);
cmd.Parameters.AddWithValue("ngoaingu", ngoaingu);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool ThemPhongBan(string tenPhongBan, string ngayLap)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlInsert = "insert into PhongBan values(@tenPB,@nl)";
SqlCommand cmd = new SqlCommand(sqlInsert, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("tenPB", tenPhongBan);
cmd.Parameters.Add("nl", ngayLap);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool SuaPhongBan(int maPB, string tenPB, string ngayLap)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlUpdate = "update PhongBan set tenphongban = @ten, ngaylap = @nl where maphongban=@maPB";
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("maPB", maPB);
cmd.Parameters.AddWithValue("ten", tenPB);
cmd.Parameters.AddWithValue("nl", ngayLap);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public bool themTDNN(int maNV, string ngoaingu, string trinhdo)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlInsert = "insert into TrinhDoNgoaiNgu values (@ma, @ngoaingu, @trinhdo)";
SqlCommand cmd = new SqlCommand(sqlInsert, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maNV);
cmd.Parameters.AddWithValue("ngoaingu", ngoaingu);
cmd.Parameters.AddWithValue("trinhdo", trinhdo);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public List <TrinhDoNgoaiNguDTO> LayThongTinTDNNtheoMa(int maNV)
{
List <TrinhDoNgoaiNguDTO> ds = new List <TrinhDoNgoaiNguDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select * from trinhdongoaingu where manhanvien=@ma ";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maNV);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TrinhDoNgoaiNguDTO td = new TrinhDoNgoaiNguDTO(
Convert.ToInt32(dr["manhanvien"]),
dr["ngoaingu"].ToString(),
dr["trinhdo"].ToString()
);
ds.Add(td);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public bool suaTDNN(int maNV, string ngoaingu, string trinhdo)
{
try
{
KetNoiCSDL.MoKetNoi();
string sqlUpdate = "Update TrinhDoNgoaiNgu set trinhdo=@trinhdo where manhanvien=@ma and ngoaingu=@ngoaingu";
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.KetNoi);
cmd.Parameters.AddWithValue("ma", maNV);
cmd.Parameters.AddWithValue("ngoaingu", ngoaingu);
cmd.Parameters.AddWithValue("trinhdo", trinhdo);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
KetNoiCSDL.DongKetNoi();
}
}
public List <TrinhDoNgoaiNguDTO> LayBangTrinhDoNgoaiNgu()
{
List <TrinhDoNgoaiNguDTO> ds = new List <TrinhDoNgoaiNguDTO>();
KetNoiCSDL.MoKetNoi();
string sqlSelect = "select ngoaingu from trinhdongoaingu group by ngoaingu";
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.KetNoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TrinhDoNgoaiNguDTO nn = new TrinhDoNgoaiNguDTO(
0,
dr["ngoaingu"].ToString(),
""
);
ds.Add(nn);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
public List <ChucVuDTO> LayDSChucVuTheoMaCV(int maCV)
{
List <ChucVuDTO> ds = new List <ChucVuDTO>();
KetNoiCSDL.MoKetNoi();
string sqlselect = "select *from ChucVu where MaChucVu=@ma";
SqlCommand cmd = new SqlCommand(sqlselect, KetNoiCSDL.KetNoi);
cmd.Parameters.Add("ma", maCV);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ChucVuDTO cv = new ChucVuDTO(
Convert.ToInt32(dr["MaChucVu"]),
dr["TenChucVu"].ToString(),
float.Parse(dr["HeSoPhuCap"].ToString())
);
ds.Add(cv);
}
KetNoiCSDL.DongKetNoi();
return(ds);
}
