public DataTable GetReportProfile(List<Hre_ProfileEntity> lstprofile, Guid _CutOffDurationID, string UserLogin) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (UnitOfWork)(new UnitOfWork(context)); DataTable table = CreateReportProfileScheme(); if (lstprofile == null) return table; //var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); //var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); //var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); //var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); //hop dong string status = string.Empty; var hreServiceContract = new Hre_ContractServices(); List<object> paraContract = new List<object>(); paraContract.AddRange(new object[21]); paraContract[19] = 1; paraContract[20] = int.MaxValue; var lstContract = hreServiceContract.GetData<Hre_ContractEntity>(paraContract, ConstantSql.hrm_hr_sp_get_Contract, UserLogin, ref status); //ky cong string cutoff = Common.DotNetToOracle(_CutOffDurationID.ToString()); var baseService = new BaseService(); var objAtt_CutOffDurationEntity = baseService .GetData<Att_CutOffDurationEntity>(cutoff, ConstantSql.hrm_att_sp_get_CutOffDurationById, UserLogin, ref status) .FirstOrDefault(); // DateTime _dateFrom = DateTime.MinValue; DateTime _dateTo = DateTime.MaxValue; if (objAtt_CutOffDurationEntity != null) { if (objAtt_CutOffDurationEntity.DateStart != null) _dateFrom = objAtt_CutOffDurationEntity.DateStart; if (objAtt_CutOffDurationEntity.DateEnd != null) _dateTo = objAtt_CutOffDurationEntity.DateEnd; } var salServiceBasicSalary = new Sal_BasicSalaryServices(); List<object> paraBasicSalary = new List<object>(); paraBasicSalary.AddRange(new object[4]); paraBasicSalary[0] = _dateFrom; paraBasicSalary[1] = _dateTo; paraBasicSalary[2] = 1; paraBasicSalary[3] = int.MaxValue - 1; var lstBasicSalary = salServiceBasicSalary.GetData<Sal_BasicSalaryEntity>(paraBasicSalary, ConstantSql.hrm_sal_sp_getdata_BasicSalaryByCutOff, UserLogin, ref status); //Hre_ProfilePartyUnion var repoHre_ProfilePartyUnion = new Hre_ProfilePartyUnionRepository(unitOfWork); var lstProfilePartyUnion = repoHre_ProfilePartyUnion.FindBy(s => s.IsDelete == null && s.IsTradeUnionist == true && s.YouthUnionEnrolledDate <= _dateTo).ToList(); //Hre_StopWorking List<object> paraStopWorking = new List<object>(); paraStopWorking.AddRange(new object[17]); paraStopWorking[6] = _dateFrom; paraStopWorking[7] = _dateTo; paraStopWorking[15] = 1; paraStopWorking[16] = int.MaxValue - 1; var hreServiceStopWorking = new Hre_StopWorkingServices(); var lstStopWorking = hreServiceStopWorking.GetData<Hre_StopWorkingEntity>(paraStopWorking, ConstantSql.hrm_hr_sp_get_StopWorking, UserLogin, ref status); //Ins_ProfileInsuranceMonthly var repoProfileInsuranceMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork); var lstProfileInsuranceMonthly = repoProfileInsuranceMonthly.FindBy(s => s.IsDelete == null && s.MonthYear >= _dateFrom && s.MonthYear <= _dateTo).ToList(); foreach (var profile in lstprofile) { if (profile != null) { var objContract = lstContract.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.DateUpdate).FirstOrDefault(); var objBasicSalary = lstBasicSalary.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.DateOfEffect).FirstOrDefault(); var objProfilePartyUnion = lstProfilePartyUnion.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.YouthUnionEnrolledDate).FirstOrDefault(); var objStopWorking = lstStopWorking.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.DateStop).FirstOrDefault(); var objProfileInsuranceMonthly = lstProfileInsuranceMonthly.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.MonthYear).FirstOrDefault(); DataRow row = table.NewRow(); if (profile.CodeEmp != null) row[Sal_ReportProfileEntity.FieldNames.CodeEmp] = profile.CodeEmp; if (profile.ProfileName != null) row[Sal_ReportProfileEntity.FieldNames.ProfileName] = profile.ProfileName; if (profile.UnitNameOrg != null) row[Sal_ReportProfileEntity.FieldNames.UnitNameOrg] = profile.UnitNameOrg; if (profile.DepartmentNameOrg != null) row[Sal_ReportProfileEntity.FieldNames.DepartmentNameOrg] = profile.DepartmentNameOrg; if (profile.SectionNameOrg != null) row[Sal_ReportProfileEntity.FieldNames.SectionNameOrg] = profile.SectionNameOrg; if (profile.TeamNameOrg != null) row[Sal_ReportProfileEntity.FieldNames.TeamNameOrg] = profile.TeamNameOrg; if (profile.WorkPlaceName != null) row[Sal_ReportProfileEntity.FieldNames.WorkPlaceName] = profile.WorkPlaceName; if (profile.StatusSyn != null) row[Sal_ReportProfileEntity.FieldNames.StatusSyn] = profile.StatusSyn; if (objBasicSalary != null && objBasicSalary.SalaryClassName != null) row[Sal_ReportProfileEntity.FieldNames.SalaryClassName] = objBasicSalary.SalaryClassName; if (objBasicSalary != null && objBasicSalary.GrossAmount != null) row[Sal_ReportProfileEntity.FieldNames.BasicSalary] = objBasicSalary.GrossAmount; if (objBasicSalary != null && objBasicSalary.SalaryRankName != null) row[Sal_ReportProfileEntity.FieldNames.SalaryRankName] = objBasicSalary.SalaryRankName; if (profile.CostCentreCode != null) row[Sal_ReportProfileEntity.FieldNames.CostCentreCode] = profile.CostCentreCode; if (profile.IDNo != null) row[Sal_ReportProfileEntity.FieldNames.IDNo] = profile.IDNo; if (profile.DateOfBirth != null) row[Sal_ReportProfileEntity.FieldNames.DateOfBirth] = profile.DateOfBirth; if (profile.DateHire != null) row[Sal_ReportProfileEntity.FieldNames.DateHire] = profile.DateHire; if (objContract != null) { if (objContract.DateStart != null) row[Sal_ReportProfileEntity.FieldNames.DateStart] = objContract.DateStart; if (objContract.DateEnd != null) row[Sal_ReportProfileEntity.FieldNames.DateEnd] = objContract.DateEnd; } //if (profile.WorkingPlace != null) // row[Sal_ReportProfileEntity.FieldNames.WorkingPlace] = profile.WorkingPlace; if (objProfileInsuranceMonthly != null && objProfileInsuranceMonthly.IsUnEmpInsurance != null) row[Sal_ReportProfileEntity.FieldNames.IsUnEmpInsurance] = objProfileInsuranceMonthly.IsUnEmpInsurance; if (objProfileInsuranceMonthly != null && objProfileInsuranceMonthly.IsSocialInsurance != null) row[Sal_ReportProfileEntity.FieldNames.IsSocialInsurance] = objProfileInsuranceMonthly.IsSocialInsurance; if (objProfileInsuranceMonthly != null && objProfileInsuranceMonthly.IsHealthInsurance != null) row[Sal_ReportProfileEntity.FieldNames.IsHealthInsurance] = objProfileInsuranceMonthly.IsHealthInsurance; if (objProfilePartyUnion != null && objProfilePartyUnion.IsTradeUnionist != null) row[Sal_ReportProfileEntity.FieldNames.IsTradeUnionist] = objProfilePartyUnion.IsTradeUnionist; if (objStopWorking != null && objStopWorking.DateStop != null) row[Sal_ReportProfileEntity.FieldNames.DateStop] = objStopWorking.DateStop; if (profile.DateHire != null) row[Sal_ReportProfileEntity.FieldNames.DateHire] = profile.DateHire; table.Rows.Add(row); } } return table.ConfigTable(true); } }
public DataTable BC_INSURANCETOTAL(DateTime DateStart, DateTime DateEnd, string orgs, List<Guid> socialInsPlaceIDs, string userLogin) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoProfileInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork); var repoCat_Region = new Cat_RegionRepository(unitOfWork); var repoCat_Province = new Cat_ProvinceRepository(unitOfWork); string status = string.Empty; #region get province var provinces = repoCat_Province.FindBy(p => p.IsDelete == null).ToList(); #endregion #region Get Profiles by orgs var listObj = new List<object> { orgs, string.Empty, string.Empty }; var lstProfiles = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status); #endregion List<Guid> lstProfileID = lstProfiles.Select(m => m.ID).ToList(); // Cần phải where thêm điều kiện là lấy 1 trong 2 loại (có một loại là chính và một loại là tạm - 2 loại này dùng để ra báo cáo chênh lệch) //Ps. Hàm bên dưới thiếu điều kiện where đó. List<Ins_ProfileInsuranceMonthly> lstProfileInsuranceMonthly = repoProfileInsMonthly.FindBy(m => m.ProfileID != null && m.MonthYear >= DateStart && m.MonthYear <= DateEnd).ToList(); lstProfileInsuranceMonthly = lstProfileInsuranceMonthly.Where(m => lstProfileID.Contains(m.ProfileID.Value)).ToList(); if (socialInsPlaceIDs != null) { socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList(); if (socialInsPlaceIDs.Any()) { lstProfileInsuranceMonthly = lstProfileInsuranceMonthly.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList(); } } var socialInsPlaceObjs = lstProfileInsuranceMonthly.Select(p => p.SocialInsPlaceID).Distinct().ToList(); provinces = provinces.Where(p => socialInsPlaceObjs.Contains(p.ID)).ToList(); DataTable dt = CreateReportInsuranceScheme(); int stt = 0; for (DateTime DateCheck = DateStart; DateCheck <= DateEnd; DateCheck = DateCheck.AddMonths(1)) { //danh sach du lieu BH tháng năm đang kiểm tra var lstProfileInsuranceMonthly_ByMonth = lstProfileInsuranceMonthly.Where(m => m.IsSocialInsurance.HasValue && m.IsSocialInsurance.Value && m.MonthYear.HasValue && m.MonthYear.Value.Year == DateCheck.Year && m.MonthYear.Value.Month == DateCheck.Month).ToList(); if (!lstProfileInsuranceMonthly_ByMonth.Any()) { continue; } // stt++; DataRow dr = dt.NewRow(); #region Thêm dòng đầu (dòng ngày tháng và tổng số lượng NV , tổng tiền đóng BH) stt++; dr[Ins_ReportInsuranceTotalEntity.FieldNames.Stt] = stt; dr[Ins_ReportInsuranceTotalEntity.FieldNames.Name] = DateCheck.ToString(ConstantFormat.HRM_Format_MonthYear); var TotalEmp = lstProfileInsuranceMonthly_ByMonth.Select(m => m.ProfileID).Distinct().ToList().Count; dr[Ins_ReportInsuranceTotalEntity.FieldNames.TotalEmp] = TotalEmp; #region Tổng Lương BHXH +BHYT+ BHTN (khong cộng phần điều chỉnh) var totalSocial = lstProfileInsuranceMonthly_ByMonth.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.SocialInsComAmount + m.SocialInsEmpAmount); var totalHealth = lstProfileInsuranceMonthly_ByMonth.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.HealthInsComAmount + m.HealthInsEmpAmount); var totalUnEmp = lstProfileInsuranceMonthly_ByMonth.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.UnemployComAmount + m.UnemployEmpAmount); #endregion dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHXH] = lstProfileInsuranceMonthly_ByMonth.Sum(m => m.SocialInsComAmount + m.SocialInsEmpAmount); dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHYT] = lstProfileInsuranceMonthly_ByMonth.Sum(m => m.HealthInsComAmount + m.HealthInsEmpAmount); ; dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHTN] = lstProfileInsuranceMonthly_ByMonth.Sum(m => m.UnemployComAmount + m.UnemployEmpAmount); ; dr[Ins_ReportInsuranceTotalEntity.FieldNames.TotalAll] = totalSocial + totalHealth + totalUnEmp; dt.Rows.Add(dr); #endregion #region Ds nơi đóng BH theo tháng năm của NV var tempSocialInsPlaceIds = lstProfileInsuranceMonthly_ByMonth.Select(p => p.SocialInsPlaceID).Distinct().ToList(); var provinceByInsProfileMonthlys = provinces.Where(p => tempSocialInsPlaceIds.Contains(p.ID)).ToList(); foreach (var insSocialPlace in provinceByInsProfileMonthlys) { dr = dt.NewRow(); var profileInsuranceMonthly_ByMonths = lstProfileInsuranceMonthly_ByMonth.Where(p => p.SocialInsPlaceID == insSocialPlace.ID).ToList(); dr[Ins_ReportInsuranceTotalEntity.FieldNames.Stt] = DBNull.Value; dr[Ins_ReportInsuranceTotalEntity.FieldNames.Name] = insSocialPlace.ProvinceName; dr[Ins_ReportInsuranceTotalEntity.FieldNames.TotalEmp] = profileInsuranceMonthly_ByMonths.Select(m => m.ProfileID).Distinct().ToList().Count; #region Tổng Lương BHXH +BHYT+ BHTN (khong cộng phần điều chỉnh) var wtotalSocial = profileInsuranceMonthly_ByMonths.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.SocialInsComAmount + m.SocialInsEmpAmount); var wtotalHealth = profileInsuranceMonthly_ByMonths.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.HealthInsComAmount + m.HealthInsEmpAmount); var wtotalUnEmp = profileInsuranceMonthly_ByMonths.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.UnemployComAmount + m.UnemployEmpAmount); #endregion dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHXH] = profileInsuranceMonthly_ByMonths.Sum(m => m.SocialInsComAmount + m.SocialInsEmpAmount); ; dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHYT] = profileInsuranceMonthly_ByMonths.Sum(m => m.HealthInsComAmount + m.HealthInsEmpAmount); ; dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHTN] = profileInsuranceMonthly_ByMonths.Sum(m => m.UnemployComAmount + m.UnemployEmpAmount); ; dr[Ins_ReportInsuranceTotalEntity.FieldNames.TotalAll] = wtotalSocial+wtotalHealth+wtotalUnEmp; dt.Rows.Add(dr); } stt = 0; #endregion } return dt.ConfigTable(true); } }