public List<Ins_ReportJobNameMonthlyEntity> ReportJobNameMonthly(string orgs, DateTime monthCheck, string codeEmp, List<Guid> socialInsPlaceIDs, string userLogin)
{
string status = string.Empty;
var preMonth = new DateTime(monthCheck.Year, monthCheck.AddMonths(-1).Month, InsuranceServices.PeriodInsuranceDayPreMonthDefault);
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoCat_SalaryClass = new Cat_SalaryClassRepository(unitOfWork);
var salaryClasses = repoCat_SalaryClass.FindBy(p => p.IsDelete == null).ToList();
var orgstructures = GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).Translate<Cat_OrgStructureEntity>().ToList();
var listObj = new List<object> { orgs, string.Empty, string.Empty };
var lstProfile = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status).Translate<Hre_ProfileEntity>();
#region lich su bao hiem thang chọn
var listInsMonthlyObj = new List<object> { orgs, monthCheck, null, 1, int.MaxValue - 1 };
if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
{
listInsMonthlyObj.Add("id");
}
var lstProfileInsuranceMonthlyInDb = GetData<Ins_ReportJobNameMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status);
#endregion
#region Lịch sử Bảo Hiểm tháng trước
var listInsPreMonthlyObj = new List<object> { orgs, monthCheck.AddMonths(-1), null, 1, int.MaxValue - 1 };
if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
{
listInsPreMonthlyObj.Add("id");
}
var lstProfileInsurancePreMonthlyInDb = GetData<Ins_ReportJobNameMonthlyEntity>(listInsPreMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status);
#endregion
#region đk lọc
if (!string.IsNullOrEmpty(codeEmp))
{
lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => p.CodeEmp == codeEmp).ToList();
}
if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any())
{
socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
if (socialInsPlaceIDs.Any())
{
lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList();
}
}
#endregion
//lịch sử NV đóng bảo hiểm
// lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => p.IsDecreaseWorkingDays == null || p.IsPregnant == null).ToList();
List<Ins_ReportJobNameMonthlyEntity> lstJobNameMonthlies = new List<Ins_ReportJobNameMonthlyEntity>();
foreach (var item in lstProfileInsuranceMonthlyInDb)
{
var profile = lstProfile.Where(p => p.ID == item.ProfileID).FirstOrDefault();
if (item.IsDecreaseWorkingDays.HasValue || item.IsPregnant.HasValue)
{
var inFirst = lstProfileInsurancePreMonthlyInDb.FirstOrDefault(p => p.ProfileID == item.ProfileID && p.MonthYear.HasValue && p.MonthYear.Value.Month == monthCheck.AddMonths(-1).Month);
var inNow = lstProfileInsuranceMonthlyInDb.FirstOrDefault(p => p.ProfileID == item.ProfileID && p.MonthYear.HasValue && p.MonthYear.Value.Month == monthCheck.Month);
#region Thiết lập
var inFirstIsPregnant = false;
var inFirstIs14days = false;
var inNowIsPregnant = false;
var inNowIs14days = false;
if (inNow != null)
{
inNowIsPregnant = inNow.IsPregnant ?? false;
inNowIs14days = inNow.IsDecreaseWorkingDays ?? false;
}
if (inFirst != null)
{
inFirstIsPregnant = inFirst.IsPregnant ?? false;
inFirstIs14days = inFirst.IsDecreaseWorkingDays ?? false;
}
if (inNowIsPregnant && inFirstIsPregnant && (!profile.DateQuit.HasValue))
{
//tháng trước nghỉ thai sản
continue;
}
if (inNowIs14days && inFirstIs14days && (!profile.DateQuit.HasValue ) )
{
//tháng trước nghỉ 14 ngày
continue;
}
}
#endregion
if (profile != null && profile.DateQuit.HasValue && profile.DateQuit.Value < preMonth)
{
//bắt đầu nghỉ việc từ tháng trước
continue;
}
var salaryClassObj = salaryClasses.FirstOrDefault(p => profile != null && profile.SalaryClassID.HasValue && p.ID == profile.SalaryClassID);
if (salaryClassObj != null)
{
//Mã Lương
item.Rank = salaryClassObj.SalaryClassName;
}
if (profile != null)
{
item.DateHire = profile.DateHire;
item.CostCentreName = profile.CostCentreName;
item.CostCentreID = profile.CostCentreID;
// item.Location = GetCodeOrgStructure(orgstructures, profile.OrgStructureID ?? Guid.Empty);
item.E_DEPARTMENT = profile.E_DEPARTMENT;
item.E_DIVISION = profile.E_DIVISION;
item.E_SECTION = profile.E_SECTION;
item.E_TEAM = profile.E_TEAM;
item.E_UNIT = profile.E_UNIT;
}
lstJobNameMonthlies.Add(item);
}
return lstJobNameMonthlies;
}
}
public DataTable Ins_ReportInsuranceTrackingMonthlyLoadData(string orgIds, DateTime? dtDateFrom, DateTime? dtDateTo, bool? isProfileQuit, string codeEmp, List<Guid> socialInsPlaceIDs, string userLogin)
{
var DtInsuranceTrackingMonthly = new DataTable();
var dateFrom = dtDateFrom.HasValue ? dtDateFrom.Value : new DateTime(1996, 1, 1);
var dateTo = dtDateTo.HasValue ? dtDateTo.Value : DateTime.Now;
using (var context = new VnrHrmDataContext())
{
var status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var repoCat_SalaryClass = new Cat_SalaryClassRepository(unitOfWork);
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
//Lay DS Ma Luong
var salaryClass = repoCat_SalaryClass.FindBy(p => p.IsDelete == null).ToList();
#region ds NV đóng Bảo Hiểm theo phòng ban và tháng
var listInsMonthlyObj = new List<object> { orgIds, dateFrom, dateTo, null, null, null, null, null, 1, int.MaxValue - 1 };
if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
{
listInsMonthlyObj.Add("id");
}
if (!string.IsNullOrEmpty(codeEmp))
{
listInsMonthlyObj[0] = null;
listInsMonthlyObj[3] = codeEmp;
}
var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthlyFromTo, userLogin, ref status).Translate<Ins_ProfileInsuranceMonthly>();
if (!lstProfileInsuranceMonthlyInDb.Any())
{
return new DataTable();
}
List<Guid> lstProfileIDs = lstProfileInsuranceMonthlyInDb.Select(p => p.ProfileID ?? Guid.Empty).Distinct().ToList();
#endregion
#region Get Profiles by orgs
#region DS Profile
int orgOrderNumber = 0;
var lstOrgOrderNumber = orgIds.Split(',').Where(p => int.TryParse(p, out orgOrderNumber)).Select(p => orgOrderNumber).ToList();
lstOrgOrderNumber = lstOrgOrderNumber.Where(p => p != 0).ToList();
var catOrgIDs = repoCat_OrgStructure.FindBy(p => p.IsDelete == null && lstOrgOrderNumber.Contains(p.OrderNumber)).Select(p => p.ID).ToList();
var lstProfile = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, p => catOrgIDs.Contains(p.OrgStructureID ?? Guid.Empty)).Select(p => new
{
p.ProfileName,
p.ID,
p.DateHire,
p.CodeEmp,
p.SocialInsNo,
p.DateQuit,
p.SalaryClassID,
}).ToList();
#endregion
lstProfile = lstProfile.Where(p => lstProfileIDs.Contains(p.ID)).ToList();
if (!string.IsNullOrEmpty(codeEmp))
{
lstProfile = lstProfile.Where(p => p.CodeEmp == codeEmp).ToList();
}
if (isProfileQuit.HasValue && isProfileQuit.Value)
{
//Nv Nghỉ Việc
lstProfile = lstProfile.Where(p => p.DateQuit.HasValue).ToList();
}
else
{
//NV đang làm việc
lstProfile = lstProfile.Where(p => p.DateQuit == null).ToList();
}
#endregion
//ds profileId theo phong ban
lstProfileIDs = lstProfile.Select(m => m.ID).ToList();
#region thiet lap column
DtInsuranceTrackingMonthly = GetReportNotHaveSocialSchema(dateFrom, dateTo);
#endregion
if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any())
{
socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList();
}
DataRow Row;
try
{
List<Guid> lstProfileIDTotal = lstProfile.Select(m => m.ID).ToList();
foreach (var lstProfileID in lstProfileIDTotal.Chunk(500))
{
lstProfile = lstProfile.Where(p => lstProfileID.Contains(p.ID)).ToList();
lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => p.ProfileID.HasValue && lstProfileID.Contains(p.ProfileID.Value)).ToList();
foreach (var profile in lstProfile)
{
var insuranceByProfiles = lstProfileInsuranceMonthlyInDb
.Where(m => m.ProfileID == profile.ID).ToList();
if (!insuranceByProfiles.Any())
{
continue;
}
Row = DtInsuranceTrackingMonthly.NewRow();
for (var i = dateFrom; i <= dateTo; i = i.AddMonths(1))
{
var lst = insuranceByProfiles.Where(m => m.MonthYearEffect.HasValue && m.MonthYearEffect.Value.Year == i.Year
&& m.MonthYearEffect.Value.Month == i.Month).OrderByDescending(m => m.MonthYearEffect).ToList();
var insuranceByProfile = lst.FirstOrDefault();
if (insuranceByProfile == null)
{
continue;
}
if (insuranceByProfile.MonthYearEffect.HasValue && insuranceByProfile.MonthYearEffect.Value == i)
{
var totalInsurancePerProfile = lst.Sum(m=>m.SalaryInsurance);
if (insuranceByProfile.IsPregnant != null && insuranceByProfile.IsPregnant.Value)
{
Row[i.ToString(MONTHYEAR)] = ConstantDisplay.HRM_Ins_ReportEmpHardJob_PregnantLeave.TranslateString();
}
if (insuranceByProfile.IsDecreaseWorkingDays.HasValue && insuranceByProfile.IsDecreaseWorkingDays.Value
&& (insuranceByProfile.IsPregnant == null || !insuranceByProfile.IsPregnant.Value))
{
Row[i.ToString(MONTHYEAR)] = ConstantDisplay.HRM_Ins_ReportEmpHardJob_DecreaseWorkingDays.TranslateString();
}
else if (totalInsurancePerProfile > 0)
{
if (profile.DateQuit.HasValue && profile.DateQuit.Value <= i)
{
Row[i.ToString(MONTHYEAR)] = DBNull.Value;
}
else
{
Row[i.ToString(MONTHYEAR)] = String.Format("{0:#,###,###.##}", totalInsurancePerProfile);
}
}
}
}
Row[ConstantDisplay.HRM_HR_Profile_DateHire] = profile.DateHire;
Row[ConstantDisplay.HRM_HR_Profile_CodeEmp] = profile.CodeEmp;
Row[ConstantDisplay.HRM_HR_Profile_ProfileName] = profile.ProfileName;
Row[ConstantDisplay.HRM_HR_Profile_SocialInsNo] = profile.SocialInsNo;
#region lấy mã lương theo nhan vien
var salaryClassProfileObj = salaryClass.Where(p => p.ID == profile.SalaryClassID).FirstOrDefault();
if (salaryClassProfileObj != null)
{
Row[ConstantDisplay.HRM_HR_Profile_SalaryClassID] = salaryClassProfileObj.Code;
}
#endregion
//them 1 row
DtInsuranceTrackingMonthly.Rows.Add(Row);
}
}
}
catch
{
return null;
}
}
var configs = new Dictionary<string, Dictionary<string, object>>();
var config = new Dictionary<string, object>();
config.Add("width", 80);
configs.Add(ConstantDisplay.HRM_HR_Profile_DateHire.TranslateString(), config);
return DtInsuranceTrackingMonthly.ConfigTable(configs);
}