public List <BHDONGBAOHIEMTHANGInfo> GetBHDONGBAOHIEMTHANG(string madonvi, int idbangluong, bool locquanlyBH, int start, int limit, string searkey, out int count) { string[] s1 = new DM_DONVIController().GetAllMaDonVi(madonvi).Split(','); string[] s2 = dataContext.BHNHANVIEN_BAOHIEMs.Select(p => p.MaNhanVien).ToArray(); List <BHDONGBAOHIEMTHANGInfo> rs = (from p in dataContext.DanhSachBangLuongs.Where(p => p.ID == idbangluong) join q in dataContext.BangThanhToanLuongs on p.ID equals q.IdBangLuong join h in dataContext.HOSOs on q.MaCB equals h.MA_CB where (madonvi != "" ? s1.Contains(h.MA_DONVI) : 1 == 1) && (string.IsNullOrEmpty(searkey) ? 1 == 1 : System.Data.Linq.SqlClient.SqlMethods.Like(h.HO_TEN, searkey) || System.Data.Linq.SqlClient.SqlMethods.Like(h.MA_CB, searkey)) && (!locquanlyBH || s2.Contains(h.MA_CB)) orderby h.TEN_CB, h.HO_TEN select new BHDONGBAOHIEMTHANGInfo { ID = q.ID, SoTienDVBHTN = q.DVBHTN, SoTienDVBHXH = q.DVBHXH, SoTienDVBHYT = q.DVBHYT, SoTienNVBHTN = q.GiamTruBHTN, SoTienNVBHXH = q.GiamTruBHXH, SoTienNVBHYT = q.GiamTruBHYT, HoTen = h.HO_TEN, MaNhanVien = h.MA_CB, LuongDongBH = q.LuongBaoHiem, TongTienDongBHNV = q.GiamTruBHTN + q.GiamTruBHXH + q.GiamTruBHYT, TongTienDongBHDV = q.DVBHTN + q.DVBHXH + q.DVBHYT, TongCong = q.GiamTruBHTN + q.GiamTruBHXH + q.GiamTruBHYT + q.DVBHTN + q.DVBHXH + q.DVBHYT }).ToList(); count = rs.Count; return(rs.Skip(start).Take(limit).ToList()); }
public List <DanhSachBienDongInfo> GetDanhSachBienDong(string madonvi, int start, int limit, string searchkey, DateTime tungay, DateTime denngay, out int count) { string[] ds = new DM_DONVIController().GetAllMaDonVi(madonvi).Split(','); List <DanhSachBienDongInfo> result = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.MaNhanVien equals q.MA_CB join x in dataContext.BHBIENDONGBAOHIEMs on t.IDNhanVien_BaoHiem equals x.IDNhanVien_BaoHiem where ds.Contains(q.MA_DONVI) && (string.IsNullOrEmpty(searchkey) ? 1 == 1 : (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey))) && x.ThangDangKy >= tungay && x.ThangDangKy <= denngay select new DanhSachBienDongInfo { IDBienDongBaoHiem = x.IDBienDongBaoHiem, IDQuyDinhBienDong = x.IDQuyDinhBienDong, IDNhanVien_BaoHiem = x.IDNhanVien_BaoHiem, MaCanBo = x.MaNhanVien, HoTen = x.HoTen, Ten = x.HoTen, MaSo = t.SoSoBHXH, GioiTinh = !(bool)x.GioiTinh, NgaySinh = x.NgaySinh, Loai = x.Loai, TuNgay = x.TuNgay, DenNgay = x.DenNgay, ThangDangKy = x.ThangDangKy, SoNgay = ((TimeSpan)(x.DenNgay.Value - x.TuNgay)).Days, TienLuongCu = x.TienLuongCu, TongPhuCapCu = x.PhuCapCVCu + x.PhuCapNgheCu + x.PhuCapTNVKCu, TienLuongMoi = x.TienLuongMoi, TongPhuCapMoi = x.PhuCapCVMoi + x.PhuCapTNNgheMoi + x.PhuCapTNVKMoi, KhongTraThe = x.KhongTraThe, DaCoSo = x.DaCoSo, DienGiai = x.DienGiai, }).ToList(); foreach (var item in result) { switch (item.Loai) { case "TLD": item.TrangThai = "1.Tăng lao động"; break; case "GLD": item.TrangThai = "3.Giảm lao động"; break; case "TMD": item.TrangThai = "2.Tăng mức đóng"; break; case "GMD": item.TrangThai = "4.Giảm mức đóng"; break; } } result = (from t in result orderby t.TrangThai, t.Ten, t.HoTen select t).ToList(); count = result.Count; return(result.Skip(start).Take(limit).ToList()); }
/// <summary> /// Get data for filling to the grid /// </summary> /// <param name="maPhong"></param> /// <param name="maTo"></param> /// <param name="maDonVi"></param> /// <param name="start"></param> /// <param name="limit"></param> /// <param name="searchKey"></param> /// <param name="count"></param> /// <returns></returns> public IEnumerable <HosoLuongInfo> GetAll(string maDonVi, int start, int limit, string searchKey, out int count, string trangThai, int userID, int menuID) { string dsDonVi = new DepartmentRoleController().GetMaBoPhanByRole(userID, menuID); string[] dvList = new DM_DONVIController().GetAllMaDonVi(maDonVi).Split(','); string[] ds = dsDonVi.Split(','); var rs = from t in dataContext.HOSO_LUONGs join hs in dataContext.HOSOs on t.PrKeyHoSo equals hs.PR_KEY where ds.Contains(hs.MA_DONVI) && //System.Data.Linq.SqlClient.SqlMethods.Like("%" + str + "%", "%," + t.HOSO1.MA_DONVI + ",%") && (string.IsNullOrEmpty(maDonVi) || dvList.Contains(hs.MA_DONVI)) && (string.IsNullOrEmpty(trangThai) || System.Data.Linq.SqlClient.SqlMethods.Like(t.TrangThai, "%" + trangThai + "%")) && (string.IsNullOrEmpty(searchKey) || System.Data.Linq.SqlClient.SqlMethods.Like(hs.HO_TEN, "%" + searchKey + "%") || System.Data.Linq.SqlClient.SqlMethods.Like(hs.MA_CB, "%" + searchKey + "%")) select new HosoLuongInfo { BacLuong = t.BacLuong, BacLuongNB = t.BacLuongNB, GhiChu = t.GhiChu, GioiTinh = hs.MA_GIOITINH, HeSoLuong = t.HeSoLuong, HoTen = hs.HO_TEN, TenBoPhan = (from p in dataContext.DM_DONVIs where p.MA_DONVI == hs.MA_DONVI select p).FirstOrDefault().TEN_DONVI, ID = t.ID, LoaiLuong = t.DM_LOAI_LUONG.TEN_LOAI_LUONG, LuongCung = t.LuongCung, LuongDongBHXH = t.LuongDongBHXH, LuongTrachNhiem = t.LuongTrachNhiem, MaCB = hs.MA_CB, NgayHieuLuc = t.NgayHieuLuc, NgayHuongLuong = t.NgayHuongLuong, NgayHuongLuongNB = t.NgayHuongLuongNB, NgayKetThucHieuLuc = t.NgayKetThucHieuLuc, NgayQuyetDinh = t.NgayQuyetDinh, NgaySinh = hs.NGAY_SINH, NguoiQuyetDinh = t.HOSO.HO_TEN, IdNguoiQuyetDinhPhuCap = t.HOSO.PR_KEY, PhanTramHuongLuong = t.PhanTramHuongLuong, SoQuyetDinh = t.SoQuyetDinh, TrangThai = t.TrangThai, TepTinDinhKem = t.TepTinDinhKem, HasPhuCap = t.HOSO_PHUCAPs.Count() > 0 }; count = rs.Count(); return(rs.OrderByDescending(p => p.NgayHieuLuc).Skip(start).Take(limit)); }
/// <summary> /// Sửa dụng cho combobox search ca làm việc /// </summary> /// <param name="start"></param> /// <param name="limit"></param> /// <param name="query"></param> /// <param name="count">Tổng số lượng bản ghi</param> /// <returns></returns> public List <DanhSachCaInfo> MiniGetAll(int start, int limit, string query, string maDonVi, out int count) { string[] dsDonVi = new DM_DONVIController().GetAllMaDonVi(maDonVi).Split(','); var rs = (from t in dataContext.DanhSachCas where (string.IsNullOrEmpty(query) || System.Data.Linq.SqlClient.SqlMethods.Like(t.TenCa, query) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaCa, query)) && dsDonVi.Contains(t.MaDonVi) select new DanhSachCaInfo { ID = t.ID, MaCa = t.MaCa, TenCa = t.TenCa, GioVao = t.GioVao, GioRa = t.GioRa }).ToList(); count = rs.Count; return(rs.OrderBy(t => t.TenCa).Skip(start).Take(limit).ToList()); }
public List <HoSoInfo> GetHoSoMaChamCong(string madonvi, List <string> macanbo, bool choncanbo)//=true: chỉ lấy những cán bộ trong danh sách được chọn, false: cán bộ trong bộ phận được chọn { string[] dsDonVi = new DM_DONVIController().GetAllMaDonVi(madonvi).Split(','); var rs = (from t in dataContext.HOSOs join dv in dataContext.DM_DONVIs on t.MA_DONVI equals dv.MA_DONVI where (choncanbo == false ? dsDonVi.Contains(t.MA_DONVI) && (t.DA_NGHI == false || t.DA_NGHI.HasValue == false) : macanbo.Contains(t.MA_CB)) select new HoSoInfo { PRKEY = t.PR_KEY, HOTEN = t.HO_TEN, MACB = t.MA_CB, PHONGBAN = dv.TEN_DONVI, MaChamCong = t.ID_MAY_CHAMCONG }); //count = rs.Count(); return(rs.ToList()); }
public List <LuongKhoanSanPhamInfo> GetLuongKhoanSanPham(int start, int limit, int month, int year, string maDonVi, string searchKey, int userID, int menuID, out int count) { string[] dsdv = new DepartmentRoleController().GetMaBoPhanByRole(userID, menuID).Split(','); string[] listSelected = new DM_DONVIController().GetAllMaDonVi(maDonVi).Split(','); var rs = from hs in dataContext.HOSOs join tmpVaoRa in dataContext.ChamCongKhoanAustfeeds on hs.MA_CB equals tmpVaoRa.MaCB into tmp1 from vr in tmp1.DefaultIfEmpty() join tmpChucVu in dataContext.DM_CHUCVUs on hs.MA_CHUCVU equals tmpChucVu.MA_CHUCVU into tmp2 from cv in tmp2.DefaultIfEmpty() join donvi in dataContext.DM_DONVIs on hs.MA_DONVI equals donvi.MA_DONVI into tmp3 from dv in tmp3.DefaultIfEmpty() where (string.IsNullOrEmpty(searchKey) || System.Data.Linq.SqlClient.SqlMethods.Like(hs.MA_CB, searchKey) || System.Data.Linq.SqlClient.SqlMethods.Like(hs.HO_TEN, searchKey)) && vr.MonthYear.Month == month && vr.MonthYear.Year == year && (string.IsNullOrEmpty(maDonVi) || listSelected.Contains(hs.MA_DONVI)) && dsdv.Contains(hs.MA_DONVI) let SalaryInfo = (from hsl in dataContext.HOSO_LUONGs where hsl.PrKeyHoSo == hs.PR_KEY select hsl).OrderByDescending(p => p.NgayHieuLuc).FirstOrDefault() select new LuongKhoanSanPhamInfo { MaCanBo = hs.MA_CB, HoTen = hs.HO_TEN, TenCanBo = hs.TEN_CB, ChucVu = cv.TEN_CHUCVU, LuongQuyetDinh = SalaryInfo == null ? 0 : SalaryInfo.LuongCung, SoGioDangKy = vr.SoGioDangKy, SoGioLamViec = vr.SoGioLamViec, SanPhamChinh = vr.SanPhamChinh, SanPhamPhu = vr.SanPhamPhu, LuongSanPham = vr.LuongSanPham, LuongCongNhat = vr.LuongCongNhat, LuongKhac = vr.LuongKhac, LuongHoTro = vr.LuongHoTro, TongLuong = vr.LuongSanPham + vr.LuongCongNhat + (decimal)(vr.LuongHoTro * (SalaryInfo.LuongDongBHXH == null ? 0 : SalaryInfo.LuongDongBHXH) / 100) + vr.LuongKhac, Ngay = vr.MonthYear.Day, MaDonVi = dv.MA_DONVI, TenDonVi = dv.TEN_DONVI }; count = rs.ToList().Count; return(rs.OrderBy(t => t.TenCanBo).ToList()); }
public List <DanhSachBangLuongInfo> GetAll(string madonvi, int start, int limit, out int count) { List <string> limadonvi = new DM_DONVIController().getChildID(madonvi, true); var result = (from t in dataContext.DanhSachBangLuongs join q in dataContext.Users on t.CreatedBy equals q.ID into tq from x in tq.DefaultIfEmpty() where limadonvi.Contains(t.MA_DONVI) orderby t.Year descending, t.Month descending select new DanhSachBangLuongInfo { CreatedBy = x != null ? x.DisplayName : "", CreatedDate = t.CreatedDate, ID = t.ID, Month = t.Month, Title = t.Title, Year = t.Year }).ToList(); count = result.Count; return(result.Skip(start).Take(limit).ToList()); }
public List <NhanVienCheDoTheoThangInfo> GetNhanVienCheDoTheoThang(string madonvi, int thang, int nam, int start, int limit, string searchkey, out int count) { string[] s1 = new DM_DONVIController().GetAllMaDonVi(madonvi).Split(','); var result = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.IDNhanVien_BaoHiem equals q.PR_KEY join x in dataContext.BHCHITIETCHEDOBAOHIEMs on t.IDNhanVien_BaoHiem equals x.IDNhanVien_BaoHiem join u in dataContext.BHBANGTINHCHEDOBAOHIEMs on x.IDBangTinhCheDoBaoHiem equals u.IDBangTinhCheDoBaoHiem join e in dataContext.BHCHEDOBAOHIEMs on u.IDCheDoBaoHiem equals e.IDCheDoBaoHiem join y in dataContext.DM_DONVIs on q.MA_DONVI equals y.MA_DONVI where (string.IsNullOrEmpty(madonvi) || s1.Contains(q.MA_DONVI)) && (string.IsNullOrEmpty(searchkey) || (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey))) && (nam == 0 || x.NgayKetThuc.Year == nam) && (thang == 0 || x.NgayKetThuc.Month == thang) orderby x.NgayKetThuc descending, t.Ten, t.HoTen select new NhanVienCheDoTheoThangInfo { IDChiTietCheDoBaoHiem = x.IDChiTietCheDoBaoHiem, IDBangTinhCheDoBaoHiem = x.IDBangTinhCheDoBaoHiem, IDNhanVien_BaoHiem = x.IDNhanVien_BaoHiem, MaCheDo = e.MaCheDoBaohiem, TenCheDo = e.TenCheDoBaoHiem, DieuKienHuong = u.TenDieuKienHuong, MaNhanVien = t.MaNhanVien, HoTen = t.HoTen, Ten = t.Ten, PhongBan = y.TEN_DONVI, NgayBatDau = x.NgayBatDau, NgayKetThuc = x.NgayKetThuc, SoNgayNghi = x.SoNgayNghi, SoTienDeNghi = x.SoTienDeNghi, TinhTrangThanhToan = x.TinhTrangThanhToan, TienLuongTinhHuong = x.TienLuongTinhHuong, Thang = x.NgayKetThuc.Month >= 10 ? "Tháng " + x.NgayKetThuc.Month + "/" + x.NgayKetThuc.Year : "Tháng 0" + x.NgayKetThuc.Month + "/" + x.NgayKetThuc.Year, GhiChu = x.GhiChu }).ToList(); count = result.Count; return(result.Skip(start).Take(limit).ToList()); }
public List <BHNHANVIEN_BAOHIEMLiteInfo> GetNhanVienBaoHiemCBBSearch(string MaDonVi, int start, int limit, string searchkey, out int count) { string[] s1 = new DM_DONVIController().GetAllMaDonVi(MaDonVi).Split(','); var rs = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.MaNhanVien equals q.MA_CB join x in dataContext.DM_DONVIs on q.MA_DONVI equals x.MA_DONVI into tq from abc in tq.DefaultIfEmpty() where (string.IsNullOrEmpty(searchkey) ? 1 == 1 : (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey))) && s1.Contains(t.MaDonVi) select new BHNHANVIEN_BAOHIEMLiteInfo { HoTen = t.HoTen, MaNhanVien = t.MaNhanVien, NgaySinh = t.NgaySinh, PR_KEY = t.IDNhanVien_BaoHiem, SoSoBHXH = t.SoSoBHXH, SoTheBHYT = t.SoTheBHYT, TenPhong = abc != null ? abc.TEN_DONVI : "", }).ToList(); count = rs.Count; return(rs.Skip(start).Take(limit).ToList()); }
private void GenerateNhanSuTheoDonVi(int height, string dsDonVi) { try { hcFrutas.Title = new Title(GlobalResourceManager.GetInstance().GetDesktopValue("chart_by_unit")); hcFrutas.Height = height; //Danh sách giới tinh theo đơn vị List <NhanSu> nhansu = new ChartController().GetBaoCaoGioiTinhTheoDonVi(dsDonVi, userID, menuID); List <DM_DONVI> dvList = new DM_DONVIController().GetByDS(dsDonVi); int total = dvList.Count(); object[] dvdata = new object[total]; object[] MaleData = new object[total]; object[] FemaleData = new object[total]; for (int i = 0; i < dvList.Count(); i++) { dvdata[i] = dvList[i].TEN_DONVI; string[] dv = new DM_DONVIController().GetAllMaDonVi(dvList[i].MA_DONVI).Split(','); MaleData[i] = (from t in nhansu where dv.Contains(t.MaDonVi) && t.MaGioiTinh == "M" select t).ToList().Count(); //lấy số lượng nhân viên nam FemaleData[i] = (from p in nhansu where dv.Contains(p.MaDonVi) && p.MaGioiTinh == "F" select p).ToList().Count(); //lấy số lượng nhân viên nữ } //definições de eixos hcFrutas.YAxis.Add(new YAxisItem { title = new Title("Số lượng") }); hcFrutas.XAxis.Add(new XAxisItem { categories = dvdata }); //dados var series = new Collection <Serie>(); series.Add(new Serie { name = "Nam", data = MaleData }); series.Add(new Serie { name = "Nữ", data = FemaleData }); hcFrutas.PlotOptions = new PlotOptionsColumn() { borderColor = "#dedede", borderRadius = 4, dataLabels = new DataLabels() { enabled = true, }, }; hcFrutas.Legend = new Legend() { layout = Highcharts.Core.Layout.horizontal, align = Align.left, verticalAlign = Highcharts.Core.VerticalAlign.top, x = 70, y = -5, floating = true, shadow = true, backgroundColor = "#FFF", }; hcFrutas.Exporting.enabled = true; hcFrutas.DataSource = series; hcFrutas.DataBind(); } catch { throw; } }
public List <ThongTinNhanVienBaoHiemInfo> GetNhanVienBaoHiem(string madonvi, int start, int limit, string searchkey, out int count) { var cm = new CommonUtil(); string[] s1 = new DM_DONVIController().GetAllMaDonVi(madonvi).Split(','); #region Đăng ký trong tháng List <ThongTinNhanVienBaoHiemInfo> rs1 = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.IDNhanVien_BaoHiem equals q.PR_KEY into q1 from q2 in q1.DefaultIfEmpty() join x in dataContext.DM_DONVIs on q2.MA_DONVI equals x.MA_DONVI into x1 from x2 in x1.DefaultIfEmpty() join dm in dataContext.DM_NOICAP_BHXHs on t.NoiCapSoBHXH equals dm.MA_NOICAP_BHXH into dm1 from dmnc in dm1.DefaultIfEmpty() where (string.IsNullOrEmpty(madonvi) || s1.Contains(q2.MA_DONVI)) && (string.IsNullOrEmpty(searchkey) || (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.SoSoBHXH, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.SoTheBHYT, searchkey))) && (t.NgayDangKyBHXH.Value.Month == DateTime.Now.Month && t.NgayDangKyBHXH.Value.Year == DateTime.Now.Year) && (t.DaNghi == false) select new ThongTinNhanVienBaoHiemInfo { MaNhanVien = t.MaNhanVien, HoTen = t.HoTen, HoDem = cm.GetLastNameFromFullName(t.HoTen), Ten = cm.GetFirstNamFromFullName(t.HoTen), PhongBan = x2.TEN_DONVI, LuongBaoHiem = t.LuongBaoHiem, PhuCapCV = t.PhuCapCV, PhuCapVuotKhung = t.PhuCapTNVK, PhuCapKhac = t.PhuCapKhac, PhuCapNghe = t.PhuCapTNN, NgayDangKyBHXH = t.NgayDangKyBHXH, SoSoBHXH = t.SoSoBHXH, ThoiGianDongBaoHiem = t.ThoiGianDongBaoHiem, DangDongBHXH = t.DangDongBHXH, DangDongBHYT = t.DangDongBHYT, DangDongBHTN = t.DangDongBHTN, SoTheBHYT = t.SoTheBHYT, TuThangBHYT = t.TuThangBHYT, DenThangBHYT = t.DenThangBHYT, BHXHTrangThaiDangKyCQBH = t.BHXHTrangThaiDangKyCQBH, TrangThai = "1.Thêm mới trong tháng " + "(" + DateTime.Now.ToString("MM/yyyy") + ")", NoiCapBHXH = dmnc.TEN_NOICAP_BHXH == null ? t.NoiCapSoBHXH : dmnc.TEN_NOICAP_BHXH, } ).ToList(); #endregion #region Thay đổi thông tin List <ThongTinNhanVienBaoHiemInfo> rs2 = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.IDNhanVien_BaoHiem equals q.PR_KEY into q1 from q2 in q1.DefaultIfEmpty() join x in dataContext.DM_DONVIs on q2.MA_DONVI equals x.MA_DONVI into x1 from x2 in x1.DefaultIfEmpty() join dm in dataContext.DM_NOICAP_BHXHs on t.NoiCapSoBHXH equals dm.MA_NOICAP_BHXH into dm1 from dmnc in dm1.DefaultIfEmpty() where (string.IsNullOrEmpty(madonvi) || s1.Contains(q2.MA_DONVI)) && (t.DaNghi == false) && (string.IsNullOrEmpty(searchkey) ? 1 == 1 : (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.SoSoBHXH, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.SoTheBHYT, searchkey))) && (t.HoTen != q2.HO_TEN.ToString() || t.NgaySinh != q2.NGAY_SINH || t.GioiTinh != (q2.MA_GIOITINH != "F") || t.SoCMTND != q2.SO_CMND.ToString() || t.NgayCapCMTND != q2.NGAYCAP_CMND || t.NoiCapCMTND != q2.MA_NOICAP_CMND.ToString() || t.DiaChiLienHe != q2.DIA_CHI_LH.ToString() || t.NoiDangKyKCB != q2.MA_NOI_KCB) select new ThongTinNhanVienBaoHiemInfo { MaNhanVien = t.MaNhanVien, HoTen = t.HoTen, HoDem = cm.GetLastNameFromFullName(t.HoTen), Ten = cm.GetFirstNamFromFullName(t.HoTen), PhongBan = x2.TEN_DONVI, LuongBaoHiem = t.LuongBaoHiem, PhuCapCV = t.PhuCapCV, PhuCapVuotKhung = t.PhuCapTNVK, PhuCapKhac = t.PhuCapKhac, PhuCapNghe = t.PhuCapTNN, NgayDangKyBHXH = t.NgayDangKyBHXH, SoSoBHXH = t.SoSoBHXH, ThoiGianDongBaoHiem = t.ThoiGianDongBaoHiem, DangDongBHXH = t.DangDongBHXH, DangDongBHYT = t.DangDongBHYT, DangDongBHTN = t.DangDongBHTN, SoTheBHYT = t.SoTheBHYT, TuThangBHYT = t.TuThangBHYT, DenThangBHYT = t.DenThangBHYT, BHXHTrangThaiDangKyCQBH = t.BHXHTrangThaiDangKyCQBH, NoiCapBHXH = dmnc.TEN_NOICAP_BHXH == null ? t.NoiCapSoBHXH : dmnc.TEN_NOICAP_BHXH, TrangThai = "2.Thay đổi thông tin" }).ToList(); #endregion #region Không thay đổi thông tin List <ThongTinNhanVienBaoHiemInfo> rs3 = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.IDNhanVien_BaoHiem equals q.PR_KEY into q1 from q2 in q1.DefaultIfEmpty() join x in dataContext.DM_DONVIs on q2.MA_DONVI equals x.MA_DONVI into x1 from x2 in x1.DefaultIfEmpty() join dm in dataContext.DM_NOICAP_BHXHs on t.NoiCapSoBHXH equals dm.MA_NOICAP_BHXH into dm1 from dmnc in dm1.DefaultIfEmpty() where (string.IsNullOrEmpty(madonvi) || s1.Contains(q2.MA_DONVI)) && (string.IsNullOrEmpty(searchkey) || (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.SoSoBHXH, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.SoTheBHYT, searchkey))) //&& (!(((from y in rs1 select y.MaNhanVien).Union(from z in rs2 select z.MaNhanVien)).ToList().Contains(t.MaNhanVien))) && (!((from y in rs1 select y.MaNhanVien).ToList().Contains(t.MaNhanVien) || (from y in rs2 select y.MaNhanVien).ToList().Contains(t.MaNhanVien))) // && (t.DaNghi == null || t.DaNghi == false) select new ThongTinNhanVienBaoHiemInfo { MaNhanVien = t.MaNhanVien, HoTen = t.HoTen, HoDem = cm.GetLastNameFromFullName(t.HoTen), Ten = cm.GetFirstNamFromFullName(t.HoTen), PhongBan = x2.TEN_DONVI, LuongBaoHiem = t.LuongBaoHiem, PhuCapCV = t.PhuCapCV, PhuCapVuotKhung = t.PhuCapTNVK, PhuCapKhac = t.PhuCapKhac, PhuCapNghe = t.PhuCapTNN, NgayDangKyBHXH = t.NgayDangKyBHXH, SoSoBHXH = t.SoSoBHXH, ThoiGianDongBaoHiem = t.ThoiGianDongBaoHiem, DangDongBHXH = t.DangDongBHXH, DangDongBHYT = t.DangDongBHYT, DangDongBHTN = t.DangDongBHTN, SoTheBHYT = t.SoTheBHYT, TuThangBHYT = t.TuThangBHYT, DenThangBHYT = t.DenThangBHYT, BHXHTrangThaiDangKyCQBH = t.BHXHTrangThaiDangKyCQBH, NoiCapBHXH = dmnc.TEN_NOICAP_BHXH == null ? t.NoiCapSoBHXH : dmnc.TEN_NOICAP_BHXH, TrangThai = t.DaNghi ? "4.Đã nghỉ" : "3.Không thay đổi thông tin" }).ToList(); #endregion var rs = (rs1.Concat(rs2.Concat(rs3))).ToList(); rs = (from t in rs orderby t.TrangThai, t.Ten, t.HoDem select t ).ToList(); count = rs.Count; return(rs.Skip(start).Take(limit).ToList()); }
public IEnumerable <DanhSachCanBoThoiViecInfo> GetAll(int start, int limit, string maDonVi, string searchKey, int tinhTrang, int daTraThe, int daTraSo, int hoanTatCongNo, int banGiaoTaiSan, int userID, int menuID, out int count) { // get role string[] departmentList = new DepartmentRoleController().GetMaBoPhanByRole(userID, menuID).Split(','); string[] dsDonVi = new DM_DONVIController().GetAllMaDonVi(maDonVi).Split(','); var rs = from t in dataContext.DanhSachCanBoThoiViecs let isHoanTatCongNo = (from ts in dataContext.BangTamUngs where ts.PrKeyHoSo == t.PrKeyHoSo && ts.SoTienDaTra < ts.SoTien select ts).ToList().Count > 0 ? 0 : 1 let isBanGiaoTaiSan = (from ts in dataContext.HOSO_TAISANs where ts.FR_KEY == t.PrKeyHoSo && (ts.NgayBanGiao == null || ts.NgayBanGiao.ToString().Contains("1900") || ts.NgayBanGiao.ToString().Contains("0001")) select ts).ToList().Count > 0 ? 0 : 1 join lydoNghi in dataContext.DM_LYDO_NGHIs on t.MaLyDoNghi equals lydoNghi.MA_LYDO_NGHI into j1 from j2 in j1.DefaultIfEmpty() where (dsDonVi.Contains(t.HOSO.MA_DONVI) || maDonVi == "") && (System.Data.Linq.SqlClient.SqlMethods.Like(t.HOSO.HO_TEN, searchKey) || string.IsNullOrEmpty(searchKey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.HOSO.MA_CB, searchKey)) && (t.DaHoanThanhThuTuc == (tinhTrang == 1 ? true : false) || tinhTrang == -1) && (t.DaTraTheBHYT == (daTraThe == 1 ? true : false) || daTraThe == -1) && (t.DaTraSoBHXH == (daTraSo == 1 ? true : false) || daTraSo == -1) && (hoanTatCongNo == -1 || hoanTatCongNo == isHoanTatCongNo) && (banGiaoTaiSan == -1 || banGiaoTaiSan == isBanGiaoTaiSan) && (departmentList.Count() == 0 || departmentList.Contains(t.HOSO.MA_DONVI)) select new DanhSachCanBoThoiViecInfo { DaHoanThanhThuTuc = t.DaHoanThanhThuTuc, ID = t.ID, FrKeyHoSo = t.PrKeyHoSo, DaTraSoBHXH = t.DaTraSoBHXH, DaTraTheBHYT = t.DaTraTheBHYT, HoTen = t.HOSO.HO_TEN, LyDoNghi = j2.TEN_LYDO_NGHI, MaCB = t.HOSO.MA_CB, NgayHoanThanhThuTuc = t.NgayHoanThanhThuTuc, NgayTraSoBHXH = t.NgayTraSo, NgayTraTheBHYT = t.NgayTraThe, HoanTatCongNo = isHoanTatCongNo == 1 ? true : false, BanGiaoTaiSan = isBanGiaoTaiSan == 1 ? true : false, PhongBan = (from tp in dataContext.HOSOs join p in dataContext.DM_DONVIs on tp.MA_DONVI equals p.MA_DONVI where tp.PR_KEY == t.PrKeyHoSo select p.TEN_DONVI).FirstOrDefault(), NgayNghi = t.NgayNghi, IsBelongToBlackList = t.IsBelongToBlackList, DiaChiLienHe = t.HOSO.DIA_CHI_LH, Email = t.HOSO.EMAIL, DiDong = t.HOSO.DI_DONG, ChucVu = t.HOSO.DM_CHUCVU.TEN_CHUCVU, ViTriCongViec = t.HOSO.DM_CONGVIEC.TEN_CONGVIEC, NgaySinh = t.HOSO.NGAY_SINH, GioiTinh = t.HOSO.MA_GIOITINH, NgayLamChinhThuc = t.HOSO.NGAY_TUYEN_CHINHTHUC, NgayThuViec = t.HOSO.NGAY_TUYEN_DTIEN, CanBoDuyetNghi = t.HOSO1.HO_TEN, Photo = t.HOSO.PHOTO, SoQuyetDinh = t.SoQuyetDinh, AttachFile = string.IsNullOrEmpty(t.AttachFile) ? "" : t.AttachFile, GhiChu = t.GhiChu, }; count = rs.Count(); return(rs.Skip(start).Take(limit)); }
// vĩ sửa public List <BHNHANVIEN_BAOHIEMInfo> GetDangKyDongMoi(int start, int limit, string searchKey, string MaDonVi, out int count, bool cotheohopdong, bool loaihd) { //cotheohopdong là biến nếu true thì lấy lương ghi trên hợp đồng và các tham số đi kèm, nếu false thì lấy lương mới nhất var cm = new CommonUtil(); string[] s1 = new DM_DONVIController().GetAllMaDonVi(MaDonVi).Split(','); //Bảng hoso chỉ có 2 trường là id và manhanvien var hoso2cot = (from t in dataContext.HOSOs where (MaDonVi == "" || s1.Contains(t.MA_DONVI)) select new tmpCB { MaCB = t.MA_CB, PrKey = t.PR_KEY }).ToList(); #region Lấy ra danh mục loại hợp đồng ko keos ddc linq nen kho the nay day :( List <DanhMucLoaiHopDongInfo> dmLoaiHDong = new List <DanhMucLoaiHopDongInfo>(); DataTable a = DataController.DataHandler.GetInstance().ExecuteDataTable(String.Format("SELECT MA_LOAI_HDONG,TEN_LOAI_HDONG,BHXH,BHYT,BHTN FROM DM_LOAI_HDONG ")); foreach (DataRow dr in a.Rows) { var dm = new DanhMucLoaiHopDongInfo(); dm.MaLoaiHopDong = dr["MA_LOAI_HDONG"].ToString(); dm.TenLoaiHopDong = dr["TEN_LOAI_HDONG"].ToString(); //dm.BHXH = bool.Parse(dr["BHXH"].ToString()); //dm.BHYT = bool.Parse(dr["BHYT"].ToString()); //dm.BHTN = bool.Parse(dr["BHTN"].ToString()); dmLoaiHDong.Add(dm); } #endregion #region lấy những nhân viên trong bảng hồ sơ có chưa kết thúc hợp đồng // và loại hợp đồng đc đóng bhxh hoặc bhyt var hoso_hoso_hopdong = (from t in dataContext.HOSOs join q in dataContext.HOSO_HOPDONGs on t.PR_KEY equals q.FR_KEY // join x in dmLoaiHDong on q.MA_LOAI_HDONG equals x.MaLoaiHopDong where (MaDonVi == "" || s1.Contains(t.MA_DONVI)) && (q.NGAYKT_HDONG == null || (DateTime.Now - q.NGAYKT_HDONG.Value).Days < 0) select new { t.MA_CB, PR_KEY = q.FR_KEY, q.MA_LOAI_HDONG } ).Distinct().ToList(); if (loaihd) { hoso2cot = (from t in hoso_hoso_hopdong join x in dmLoaiHDong on t.MA_LOAI_HDONG equals x.MaLoaiHopDong where (x.BHXH == true || x.BHYT == true) select new tmpCB { MaCB = t.MA_CB, PrKey = t.PR_KEY }).ToList(); } else { hoso2cot = (from t in hoso_hoso_hopdong select new tmpCB { MaCB = t.MA_CB, PrKey = t.PR_KEY }).ToList(); } #endregion #region loại bỏ những nhân viên đã có trong bảng nhân viên bảo hiểm lấy prkey int[] NhanvienBaohiemID = dataContext.BHNHANVIEN_BAOHIEMs.Select(x => x.IDNhanVien_BaoHiem).ToArray(); hoso2cot = (from t in hoso2cot where (!NhanvienBaohiemID.Contains((int)t.PrKey)) select new tmpCB { MaCB = t.MaCB, PrKey = t.PrKey }).ToList(); #endregion #region kiểm tra điều kiện chấm công tháng //chuyển từ pr(trong temp2) thành Ma) string[] ma = (from t in hoso2cot select t.MaCB).ToArray(); //hoso2cot = hoso2cot.Where(p => listMaCanBo.Contains(p.MaCB)).ToList(); #endregion // bảng hồ sơ chỉ chứa mã cán bộ trong list var hoso = (from t in dataContext.HOSOs join q in dataContext.DM_DONVIs on t.MA_DONVI equals q.MA_DONVI into tq from abc in tq.DefaultIfEmpty() join x in dataContext.DM_CHUCVUs on t.MA_CHUCVU equals x.MA_CHUCVU into tqx from xyz in tqx.DefaultIfEmpty() where (ma.Contains(t.MA_CB) && (string.IsNullOrEmpty(searchKey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MA_CB, searchKey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.HO_TEN, searchKey))) //|| System.Data.Linq.SqlClient.SqlMethods.Like(t.TenLoaiHopDong, searchKey)) orderby t.TEN_CB, t.HO_CB select new { t.PR_KEY, t.MA_CB, t.HO_TEN, t.MA_DONVI, abc.TEN_DONVI, t.MA_CHUCVU, xyz.TEN_CHUCVU } ).ToList(); decimal[] listIDCanBo = hoso.Select(p => p.PR_KEY).ToArray(); //bây giờ mình đã có listMaCanBo và listIDCanBo lưu những nhân viên đc show ra gid //xử lý nốt lương và phụ cấp //lấy hợp đồng có ngày hiệu lực lớn nhất var hoso_hopdong = (from t in dataContext.HOSO_HOPDONGs group t by t.FR_KEY into grp let maxDate = grp.Max(p => p.NgayCoHieuLuc) from t in grp where (maxDate == null || t.NgayCoHieuLuc == maxDate) && listIDCanBo.Contains(t.FR_KEY) select t).ToList(); //trả về kết quả //lấy ngày hiệu lực gần nhất của nhân viên var result = (from t1 in hoso join t3 in hoso_hopdong on t1.PR_KEY equals t3.FR_KEY join t4 in dmLoaiHDong on t3.MA_LOAI_HDONG equals t4.MaLoaiHopDong select new BHNHANVIEN_BAOHIEMInfo { IDNhanVien_BaoHiem = (int)t1.PR_KEY, MaNhanVien = t1.MA_CB, HoTen = t1.HO_TEN, Ho = cm.GetLastNameFromFullName(t1.HO_TEN), Ten = cm.GetFirstNamFromFullName(t1.HO_TEN), MaBoPhan = t1.MA_DONVI, TenBoPhan = t1.TEN_DONVI, MaChucVu = t1.MA_CHUCVU, TenChucVu = t1.TEN_CHUCVU, LuongBaoHiem = 0, NgayBatDauHopDong = t3.NGAY_HDONG, TenLoaiHopDong = t4.TenLoaiHopDong, DangDongBHXH = t4.BHXH, DangDongBHYT = t4.BHYT, DangDongBHTN = t4.BHTN, PhuCapCV = 0, PhuCapTNN = 0, PhuCapKhac = 0, PhuCapTNVK = 0 }).ToList(); int IDNhanVienBaoHiem; string soquyetdinh; string tenquyetdinh; DateTime?ngayky; DateTime?Ngayhieuluc; DateTime?Hethieuluc; decimal? luongbaohiem; decimal? phucapcv; decimal? phucaptnn; decimal? phucaptnvk; decimal? phucapkhac; foreach (var item in result) { IDNhanVienBaoHiem = item.IDNhanVien_BaoHiem; new BaoHiemController().TTQuyetDinhLuongMoiNhat(IDNhanVienBaoHiem, out soquyetdinh, out tenquyetdinh, out ngayky, out Ngayhieuluc, out Hethieuluc, out luongbaohiem, out phucapcv, out phucaptnn, out phucaptnvk, out phucapkhac); item.PhuCapCV = phucapcv; item.PhuCapTNVK = phucaptnvk; item.PhuCapTNN = phucaptnn; item.PhuCapKhac = phucapkhac; item.LuongBaoHiem = luongbaohiem; } var kq = (from t in result select t).ToList(); count = kq.Count; return(kq.Skip(start).Take(limit).ToList()); }
public List <NhanVienBienDongInfo> GetNhanVienBienDong(string madonvi, int start, int limit, string searchkey, out int count) { string[] ds = new DM_DONVIController().GetAllMaDonVi(madonvi).Split(','); #region Có biến động bảo hiểm trong tháng List <NhanVienBienDongInfo> rs1 = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.MaNhanVien equals q.MA_CB join x in dataContext.BHBIENDONGBAOHIEMs on t.IDNhanVien_BaoHiem equals x.IDNhanVien_BaoHiem join y in dataContext.DM_DONVIs on q.MA_DONVI equals y.MA_DONVI where ds.Contains(q.MA_DONVI) && (string.IsNullOrEmpty(searchkey) ? 1 == 1 : (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey))) && (x.TuNgay.Month == DateTime.Now.Month && x.TuNgay.Year == DateTime.Now.Year) && (t.DaNghi == null || t.DaNghi == false) group t by new { t.IDNhanVien_BaoHiem, t.MaNhanVien, t.HoTen, t.Ten, y.TEN_DONVI, t.ThoiGianDongBaoHiem, t.LuongBaoHiem, t.PhuCapCV, t.PhuCapTNN, t.PhuCapTNVK, t.PhuCapKhac, t.DangDongBHXH, t.DangDongBHYT, t.DangDongBHTN, t.DaNghi } into g select new NhanVienBienDongInfo { IDNhanVien_BaoHiem = g.Key.IDNhanVien_BaoHiem, MaNhanVien = g.Key.MaNhanVien, HoTen = g.Key.HoTen, Ten = g.Key.Ten, PhongBan = g.Key.TEN_DONVI, ThoiGianDongBaoHiem = g.Key.ThoiGianDongBaoHiem, LuongBaoHiem = g.Key.LuongBaoHiem, PhuCapCV = g.Key.PhuCapCV, PhuCapTNN = g.Key.PhuCapTNN, PhuCapTNVK = g.Key.PhuCapTNVK, PhuCapKhac = g.Key.PhuCapKhac, DangDongBHXH = g.Key.DangDongBHXH, DangDongBHYT = g.Key.DangDongBHYT, DangDongBHTN = g.Key.DangDongBHTN, TrangThai = "1.Có biến động bảo hiểm trong tháng", DaNghi = g.Key.DaNghi }).ToList(); #endregion #region Không có biến động bảo hiểm trong tháng List <NhanVienBienDongInfo> rs2 = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.MaNhanVien equals q.MA_CB join y in dataContext.DM_DONVIs on q.MA_DONVI equals y.MA_DONVI where ds.Contains(q.MA_DONVI) && (string.IsNullOrEmpty(searchkey) ? 1 == 1 : (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey))) && !(from x in rs1 select x.MaNhanVien).ToList().Contains(t.MaNhanVien) select new NhanVienBienDongInfo { IDNhanVien_BaoHiem = t.IDNhanVien_BaoHiem, MaNhanVien = t.MaNhanVien, HoTen = t.HoTen, Ten = t.Ten, PhongBan = y.TEN_DONVI, ThoiGianDongBaoHiem = t.ThoiGianDongBaoHiem, LuongBaoHiem = t.LuongBaoHiem, PhuCapCV = t.PhuCapCV, PhuCapTNN = t.PhuCapTNN, PhuCapTNVK = t.PhuCapTNVK, PhuCapKhac = t.PhuCapKhac, DangDongBHXH = t.DangDongBHXH, DangDongBHYT = t.DangDongBHYT, DangDongBHTN = t.DangDongBHTN, TrangThai = t.DaNghi == true ? "3.Đã nghỉ" : "2.Không có biến động bảo hiểm trong tháng", DaNghi = t.DaNghi }).ToList(); #endregion var rs = (rs1.Concat(rs2)).ToList(); rs = (from t in rs orderby t.TrangThai, t.Ten, t.HoTen select t ).ToList(); count = rs.Count; return(rs.Skip(start).Take(limit).ToList()); }
public List <BienDongTheoThangInfo> GetBienDongTheoThang(string madonvi, int thang, int nam, int start, int limit, string searchkey, out int count) { string[] ds = new DM_DONVIController().GetAllMaDonVi(madonvi).Split(','); List <BienDongTheoThangInfo> result = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.MaNhanVien equals q.MA_CB join x in dataContext.BHBIENDONGBAOHIEMs on t.IDNhanVien_BaoHiem equals x.IDNhanVien_BaoHiem join u in dataContext.BHQUYDINHBIENDONGs on x.IDQuyDinhBienDong equals u.IDQuyDinhBienDong join y in dataContext.DM_DONVIs on q.MA_DONVI equals y.MA_DONVI where (string.IsNullOrEmpty(madonvi) || ds.Contains(q.MA_DONVI)) && (string.IsNullOrEmpty(searchkey) ? 1 == 1 : (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey))) && (nam == 0 ? 1 == 1 : x.TuNgay.Year == nam) && (thang == 0 ? 1 == 1 : x.TuNgay.Month == thang) orderby x.TuNgay descending, t.Ten, t.HoTen select new BienDongTheoThangInfo { IDBienDongBaoHiem = x.IDBienDongBaoHiem, IDQuyDinhBienDong = x.IDQuyDinhBienDong, IDNhanVien_BaoHiem = x.IDNhanVien_BaoHiem, MaCanBo = x.MaNhanVien, HoTen = x.HoTen, PhongBan = y.TEN_DONVI, TenBienDong = u.TenBienDong, Loai = x.Loai, TuNgay = x.TuNgay, DenNgay = x.DenNgay, SoNgay = ((TimeSpan)(x.DenNgay.Value - x.TuNgay)).Days, TienLuongCu = x.TienLuongCu, TongPhuCapCu = x.PhuCapCVCu + x.PhuCapNgheCu + x.PhuCapTNVKCu, TienLuongMoi = x.TienLuongMoi, TongPhuCapMoi = x.PhuCapCVMoi + x.PhuCapTNNgheMoi + x.PhuCapTNVKMoi, KhongTraThe = x.KhongTraThe, DaCoSo = x.DaCoSo, DienGiai = x.DienGiai, Thang = x.TuNgay.Month >= 10 ? "Tháng " + x.TuNgay.Month + "/" + x.TuNgay.Year : "Tháng 0" + x.TuNgay.Month + "/" + x.TuNgay.Year, }).ToList(); count = result.Count; return(result.Skip(start).Take(limit).ToList()); }
public List <NhanVienCheDoInfo> GetNhanVienCheDo(string madonvi, int start, int limit, string searchkey, out int count) { var cm = new CommonUtil(); string[] s1 = new DM_DONVIController().GetAllMaDonVi(madonvi).Split(','); int[] a1 = dataContext.BHCHEDOBAOHIEMs.Where(p => p.ParentID == dataContext.BHCHEDOBAOHIEMs.SingleOrDefault(x => x.MaCheDoBaohiem == "NOD").IDCheDoBaoHiem).Select(y => y.IDCheDoBaoHiem).ToArray(); int[] a2 = dataContext.BHCHEDOBAOHIEMs.Where(p => p.ParentID == dataContext.BHCHEDOBAOHIEMs.SingleOrDefault(x => x.MaCheDoBaohiem == "TS").IDCheDoBaoHiem).Select(y => y.IDCheDoBaoHiem).ToArray(); int[] a3 = dataContext.BHCHEDOBAOHIEMs.Where(p => p.ParentID == dataContext.BHCHEDOBAOHIEMs.SingleOrDefault(x => x.MaCheDoBaohiem == "DSPHSK").IDCheDoBaoHiem).Select(y => y.IDCheDoBaoHiem).ToArray(); var result = (from t in dataContext.BHNHANVIEN_BAOHIEMs join q in dataContext.HOSOs on t.IDNhanVien_BaoHiem equals q.PR_KEY join x in dataContext.DM_DONVIs on q.MA_DONVI equals x.MA_DONVI where (string.IsNullOrEmpty(searchkey) || (System.Data.Linq.SqlClient.SqlMethods.Like(t.HoTen, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.MaNhanVien, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.SoSoBHXH, searchkey) || System.Data.Linq.SqlClient.SqlMethods.Like(t.SoTheBHYT, searchkey) )) && (string.IsNullOrEmpty(madonvi) || s1.Contains(q.MA_DONVI)) select new NhanVienCheDoInfo { IDNhanVien_BaoHiem = t.IDNhanVien_BaoHiem, MaNhanVien = t.MaNhanVien, TenNhanVien = t.HoTen, PhongBan = x.TEN_DONVI, HoDem = cm.GetLastNameFromFullName(t.HoTen), Ten = cm.GetFirstNamFromFullName(t.HoTen), SoSoBHXH = t.SoSoBHXH, SoTheBHYT = t.SoTheBHYT, SoThangDongBH = t.ThoiGianDongBaoHiem, SoNgayNghiOmDau = 0, SoNgayNghiThaiSan = 0, SoNgayNghiDSPHSK = 0, TongTienThanhToan = 0, TrangThai = "3.Đã nghỉ", DaNghi = t.DaNghi }).ToList(); int[] loc = result.Select(t => t.IDNhanVien_BaoHiem).ToArray(); var temp = (from t in dataContext.BHCHITIETCHEDOBAOHIEMs where loc.Contains(t.IDNhanVien_BaoHiem) && (t.NgayKetThuc > new DateTime(DateTime.Now.Year, 1, 1) && t.NgayBatDau < new DateTime(DateTime.Now.Year, 12, 31)) // && (t.TinhTrangThanhToan == true) select t).ToList(); var temp1 = temp.Where(t => t.TinhTrangThanhToan == true).ToList(); #region Tính số ngày nghỉ và tổng tiền thanh toán foreach (var item in result) { var SoNgayNghiDSPHSK = (from t in temp1 where a3.Contains(t.BHBANGTINHCHEDOBAOHIEM.IDCheDoBaoHiem) && t.IDNhanVien_BaoHiem == item.IDNhanVien_BaoHiem group t by new { t.IDNhanVien_BaoHiem } into y select new { songaynghi = y.Sum(t => t.SoNgayNghi) } ).SingleOrDefault(); var SoNgayNghiThaiSan = (from t in temp1 where a1.Contains(t.BHBANGTINHCHEDOBAOHIEM.IDCheDoBaoHiem) && t.IDNhanVien_BaoHiem == item.IDNhanVien_BaoHiem group t by new { t.IDNhanVien_BaoHiem } into y select new { songaynghi = y.Sum(t => t.SoNgayNghi) } ).SingleOrDefault(); var SoNgayNghiOmDau = (from t in temp1 where a2.Contains(t.BHBANGTINHCHEDOBAOHIEM.IDCheDoBaoHiem) && t.IDNhanVien_BaoHiem == item.IDNhanVien_BaoHiem group t by new { t.IDNhanVien_BaoHiem } into y select new { songaynghi = y.Sum(t => t.SoNgayNghi) } ).SingleOrDefault(); var TongTienThanhToan = (from t in temp1 where t.IDNhanVien_BaoHiem == item.IDNhanVien_BaoHiem group t by new { t.IDNhanVien_BaoHiem } into y select new { TongTienThanhToan = y.Sum(t => t.SoTienDeNghi) } ).SingleOrDefault(); var tmp = temp.Where(x => x.IDNhanVien_BaoHiem == item.IDNhanVien_BaoHiem && x.NgayKetThuc.Year == DateTime.Now.Year && x.NgayKetThuc.Month == DateTime.Now.Month).ToList(); item.SoNgayNghiDSPHSK = SoNgayNghiDSPHSK == null ? 0 : (int)SoNgayNghiDSPHSK.songaynghi; item.SoNgayNghiOmDau = SoNgayNghiThaiSan == null ? 0 : (int)SoNgayNghiThaiSan.songaynghi; item.SoNgayNghiThaiSan = SoNgayNghiOmDau == null ? 0 : (int)SoNgayNghiOmDau.songaynghi; item.TongTienThanhToan = TongTienThanhToan == null ? 0 : (decimal)TongTienThanhToan.TongTienThanhToan; item.TrangThai = (item.DaNghi == null || item.DaNghi == false) ? (tmp.Count > 0 ? "1.Phát sinh chế độ trong tháng" : "2.Không phát sinh chế độ trong tháng") : item.TrangThai = item.TrangThai; } #endregion count = result.Count; result = (from t in result orderby t.TrangThai, t.Ten, t.HoDem select t).ToList(); return(result.Skip(start).Take(limit).ToList()); }