public static DataSet GetBoPhanPhongBan()
{
DataTable tb = new DataTable();
string query1 = "select * from BoPhan bp order by bp.Mabp";
string query2 = "select * from PhongBan pb join BoPhan bp on pb.MaBP=bp.MaBP order by bp.Mabp";
DataProvider dataProvider = new DataProvider();
try
{
DataSet ds = new DataSet();
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query1);
DataTable dts1 = new DataTable();
dts1 = tb.Copy();
dts1.TableName = "BoPhan";
ds.Tables.Add(dts1);
tb = dataProvider.ExecuteQuery_DataTble(query2);
DataTable dts2 = new DataTable();
dts2 = tb.Copy();
dts2.TableName = "BoPhanPhongBan";
ds.Tables.Add(dts2);
return(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
/// <summary>
/// lấy thông tin nhân viên bị kỷ luật
/// </summary>
/// <returns></returns>
public static DataTable GetNhanVienBiKyLuat()
{
DataTable tb = new DataTable();
string query = @"SELECT nv.MaNV,nv.HoTen,ctnv.NgaySinh,gt.TenGT,bp.TenBP,pb.TenPB,ctnv.DienThoai,ctnv.Email,ctkl.NguyenNhan,ctkl.HinhThuc,ctkl.NgayKL
FROM NhanVien nv join ThongTinChiTietNhanVien ctnv ON nv.MaNV = ctnv.MaNV
join GioiTinh gt ON gt.MaGT = ctnv.MaGT join PhongBan pb ON pb.MaPB = nv.MaPB
join BoPhan bp ON bp.MaBP = pb.MaBP join ChiTietKiLuat ctkl ON ctkl.MaNV = nv.MaNV";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static bool CheckExistsChamCong(DTO.ChamCongDTO cc)
{
DataTable tb = new DataTable();
string query = "Select TOP 1 cc.MaChamCong From ChamCong cc where cc.Thang=" + cc.Thang + " and cc.Nam=" + cc.Nam + " Order by cc.MaChamCong DESC";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
dataProvider.disconnect();
if (tb.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(false);
}
public static void UpdateHopDongTimeOut()
{
DataTable tb = new DataTable();
string date = DateTime.Today.ToString("yyyy-MM-dd");
string query = "Select MaHD from HopDong where MaTTHD <>3 and NgayHetHan<'" + date + "' and MaHD not in (select hdhh.MaHD from HopDongHetHan hdhh)";
//string query = @"Update HopDong Set MaTTHD = 3 Where MaNV = '" + hd.MaNV + "' AND MaTTHD=1";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
if (tb.Rows.Count > 0)
{
query = "";
foreach (DataRow row in tb.Rows)
{
query += @"INSERT INTO HopDongHetHan (MaHD, TinhTrangGiaiQuyet) VALUES('" + row[0].ToString() + "', 0); ";
}
dataProvider.ExecuteUpdateQuery(query); query = "";
foreach (DataRow row in tb.Rows)
{
query += @"UPDATE HopDong SET HopDong.MaTTHD=2 Where MaHD= '" + row[0].ToString() + "' ";
}
dataProvider.ExecuteUpdateQuery(query);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Lấy thông tin nhân viên theo một điều kiện XXX
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public static DataTable GetNhanVienByWhere(string strWhere)
{
DataTable tb = new DataTable();
string query = @"Select nv.MaNV,nv.HoTen,ctnv.NgaySinh,gt.TenGT,bp.TenBP,pb.TenPB,ctnv.DienThoai,ctnv.Email
From NhanVien nv join ThongTinChiTietNhanVien ctnv on nv.MaNV = ctnv.MaNV
join GioiTinh gt on gt.MaGT = ctnv.MaGT join PhongBan pb on pb.MaPB = nv.MaPB
join BoPhan bp on bp.MaBP = pb.MaBP " + strWhere;
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataSet GetDescriptionForOrganizationTree()
{
DataSet dtSet = new DataSet();
string queryNV = @"select st.name[Table], sc.name[Column], sep.value[Description]
from sys.tables st inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id and sc.column_id = sep.minor_id and sep.name = 'MS_Description'
where st.name = 'BoPhan' ";
string queryTTCT = @"select st.name[Table], sc.name[Column], sep.value[Description]
from sys.tables st inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id and sc.column_id = sep.minor_id and sep.name = 'MS_Description'
where st.name = 'PhongBan' ";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
DataTable tb = dataProvider.ExecuteQuery_DataTble(queryNV).Copy();
tb.TableName = "DesBoPhan";
dtSet.Tables.Add(tb);
tb = dataProvider.ExecuteQuery_DataTble(queryTTCT).Copy();
tb.TableName = "DesPhongBan";
dtSet.Tables.Add(tb);
dataProvider.disconnect();
return(dtSet);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
//public static SqlDataAdapter daLayoff;
public static DataTable GetNhanVien(TreesSearchModel TreeSearchViewModel)
{
DataTable tb = new DataTable();
string query = "Select nv.MaNV, nv.HoTen";
bool exists = true;
int i = 0;
for (int j = 0; j < TreeSearchViewModel.SearchObjects[i].SearchElements.Count; j++)
{
for (int n = 0; n < TreeSearchViewModel.SearchObjects[i].SearchElements[j].SearchElementDetails.Count; n++)
{
if (TreeSearchViewModel.SearchObjects[i].SearchElements[j].SearchElementDetails[n].IsCheckedDetail == true)
{
if (TreeSearchViewModel.SearchObjects[i].SearchElements[j].SearchElementDetails[n].StrSearch.Trim() != "nv.HoTen" && TreeSearchViewModel.SearchObjects[i].SearchElements[j].SearchElementDetails[n].StrSearch.Trim() != "nv.MaNV" && TreeSearchViewModel.SearchObjects[i].SearchElements[j].SearchElementDetails[n].StrSearch.Trim() != "tt.MaNV")
{
query += ", " + TreeSearchViewModel.SearchObjects[i].SearchElements[j].SearchElementDetails[n].StrSearch;
}
}
}
}
query += @" From NhanVien nv join ThongTinChiTietNhanVien tt on nv.MaNV = tt.MaNV
join QuocGia qg on tt.QuocGia = qg.MaQG join TinhTP ttp on tt.TinhTP = ttp.MaTinh
join DanToc dt on tt.MaDT = dt.MaDT join TonGiao tg on tt.MaTG=tg.MaTG
join QuanHuyen qh on tt.QuanHuyen = qh.MaQuan join GioiTinh gt on tt.MaGT=gt.MaGT join PhongBan pb on nv.MaPB = pb.MaPB
join TinhTrangNhanVien ttnv on nv.MaTT=ttnv.MaTT";
if (exists == true)
{
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
}
return(null);
}
public static DataTable GetNhanVienByElementForLayoff(DataTable tbTemp)
{
DataTable tb = new DataTable();
bool isnull = true;
string query = @"Select * From NhanVien nv join ThongTinChiTietNhanVien tt on nv.MaNV = tt.MaNV join HopDong hd on nv.MaNV = hd.MaNV ";
query += " where nv.MaNV IN (";
for (int i = 0; i < tbTemp.Rows.Count; i++)
{
if (isnull == true)
{
query += "'" + tbTemp.Rows[i][0].ToString().Trim() + "'";
isnull = false;
}
else
{
query += ", '" + tbTemp.Rows[i][0].ToString().Trim() + "'";
}
}
query += ") and hd.NgayKyHD >= ALL(Select hd2.NgayKyHD From HopDong hd2 Where hd2.MaNV=nv.MaNV) Order by nv.MaNV";
DataProvider dataProvider = new DataProvider();
try
{
if (isnull == false)
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
}
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetCongThucTinhLuong()
{
DataTable tb = new DataTable();
string query = "select CongThuc from CongThucTinhLuong";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetBangLuong(string manv)
{
DataTable tb = new DataTable();
string query = " select cc.Thang as 'Tháng', cc.Nam as 'Năm', bl.LuongCB as 'Lương căn bản', ctcc.NgayCong as 'Ngày công', bl.PhuCap as 'Phụ cấp', bl.HoaDon as 'Hóa đơn chi trả', bl.TongLuong as 'Tổng lương đã tính thuế' from BangLuong bl join ChamCong cc on bl.MaChamCong = cc.MaChamCong join ChiTietChamCong ctcc on cc.MaChamCong=ctcc.MaChamCong where bl.MaNV = ctcc.MaNV and bl.MaNV = '" + manv + "'";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetLastDangNhap(string username)
{
DataProvider dataprovider = new DataProvider();
DataTable dt = new DataTable();
string query = "Select TOP 1 * From GhiChuDangNhap Where Username = '******' order by ID DESC";
try
{
dataprovider.connect();
dt = dataprovider.ExecuteQuery_DataTble(query);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
finally
{
dataprovider.disconnect();
}
return(null);
}
public static DataTable DangNhap(string username)
{
DataProvider dataprovider = new DataProvider();
DataTable dt = new DataTable();
string query = "Select ND.Password,ND.MaPQ,NV.MaNV,NV.HoTen,NV.HinhAnh From NguoiDung ND join NhanVien NV on ND.MaNV = NV.MaNV Where ND.Username = '******'";
try
{
dataprovider.connect();
dt = dataprovider.ExecuteQuery_DataTble(query);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
finally
{
dataprovider.disconnect();
}
return(null);
}
public static DataTable GetAudit(string where)
{
DataTable tb = new DataTable();
string query = " Select MaTheoDoi as 'ID', HanhDong as 'Hành động', Bang as 'Bảng', ID as 'Mã thay đổi', TruongThayDoi as 'Trường thay đổi', GiaTriCu as 'Giá trị cũ', GiaTriMoi as 'Giá trị mới', NgayGhiLai as 'Ngày ghi lại', NguoiDung as 'Người thay đổi' from Audit " + where + " order by MaTheoDoi DESC ";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetPhongBanByWhere(string where)
{
DataTable tb = new DataTable();
string query = "Select * From PhongBan pb " + where;
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetBoPhan()
{
DataTable tb = new DataTable();
string query = "Select MaBP, TenBP From BoPhan";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetNhanVienPhuCap(string manv)
{
DataTable tb = new DataTable();
string query = @"Select * From PhuCap pc where pc.MaNV='" + manv + "'";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetNhanVienTongHoaDon(string manv, string thang, string nam)
{
DataTable tb = new DataTable();
string query = " select sum(hd.Tien) as TongHoaDon from HoaDon hd where hd.MaNV = '" + manv + "' and hd.Thang = " + thang + " and hd.Nam = " + nam;
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetCTCCByMANV(string manv)
{
DataTable tb = new DataTable();
string query = "Select * from ChiTietChamCong Where MaNV='" + manv + "' and TrangThai = 0";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetNhanVienLuongCB(DataTable tbTemp)
{
DataTable tb = new DataTable();
bool isnull = true;
string query = @"Select nv.MaNV, nv.LuongCB From NhanVien nv ";
query += "where nv.MaNV IN (";
for (int i = 0; i < tbTemp.Rows.Count; i++)
{
if (isnull == true)
{
query += "'" + tbTemp.Rows[i][0].ToString().Trim() + "'";
isnull = false;
}
else
{
query += ", '" + tbTemp.Rows[i][0].ToString().Trim() + "'";
}
}
query += ") and nv.LuongCB>0 ";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetLastPhongBan()
{
DataTable tb = new DataTable();
string query = "Select TOP 1 pb.MaPB From PhongBan pb Order by pb.MaPB DESC";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetTinhTrangHopDong()
{
DataTable tb = new DataTable();
string query = "Select MaTTHD, TenTinhTrang From TinhTrangHopDong";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetHopDongTimeOut()
{
DataTable tb = new DataTable();
string query = "Select * From HopDong hd join NhanVien nv on hd.MaNV = nv.MaNV join TinhTrangHopDong tthd on hd.MaTTHD=tthd.MaTTHD join HopDongHetHan hdhh on hdhh.MaHD=hd.MaHD Where hdhh.TinhTrangGiaiQuyet=0";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetLastChamCong()
{
DataTable tb = new DataTable();
string query = "Select TOP 1 cc.MaChamCong From ChamCong cc Order by cc.MaChamCong DESC";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetChamCongByMaCC(string macc)
{
DataTable tb = new DataTable();
string query = "Select * From ChamCong cc Where cc.MaChamCong='" + macc + "'";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetNhanVienForChoose()
{
DataTable tb = new DataTable();
string query = "Select nv.MaNV, nv.HoTen From NhanVien nv";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetDanToc()
{
DataTable tb = new DataTable();
string query = "Select MaDT, TenDT From DanToc";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetNotDefault()
{
DataTable tb = new DataTable();
string query = "Select nv.MaNV as 'Mã nhân viên', nv.HoTen as 'Họ tên' From NhanVien nv Where nv.MaNV NOT IN (select ccmd.MaNV from ChamCongMacDinh ccmd) ";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static int GetHopDongCountTimeOut()
{
DataTable tb = new DataTable();
string query = "Select count(MaHD) From HopDongHetHan Where TinhTrangGiaiQuyet=0";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(Int32.Parse(tb.Rows[0][0].ToString()));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(0);
}
public static DataTable GetQuanHuyen()
{
DataTable tb = new DataTable();
string query = "Select MaQuan, TenQuan, MaTinh From QuanHuyen Order by TenQuan";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}
public static DataTable GetLastNhanVien()
{
DataTable tb = new DataTable();
string query = "Select TOP 1 nv.MaNV From NhanVien nv Order by nv.MaNV DESC";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
return(tb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
return(null);
}