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());
}
