public void CalculateInsurancePayBack(List<Guid> payBackIds, DateTime monthYear,string userLogin)
{
/*
* Goal(tính toán Truy Lĩnh Bảo Hiểm - Lưu bảng Ins_ProfileInsuranceMonthly)
* Steps :
* Step1 : lấy ds InsuranceSalaryPayback
* Step2 : Duyệt InsuranceSalaryPayback
* Step3 : Duyệt fromMonthEffect đến ToMonthEffect (để insert vào Ins_ProfileMonthly tương ứng với những tháng đó)
* Step3 : Lưu vào DB
*/
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var repoInsReportD02 = new Ins_ReportD02Repository(unitOfWork);
var repoInsReportD02Item = new Ins_ReportD02ItemRepository(unitOfWork);
var repoInsuranceSalaryPayback = new Ins_InsuranceSalaryPaybackRepository(unitOfWork);
string status = string.Empty;
DateTime endDayOfMonthYear = new DateTime(monthYear.Year, monthYear.Month, DateTime.DaysInMonth(monthYear.Year, monthYear.Month));
var reportD02 = new Ins_ReportD02();
List<Ins_ProfileInsuranceMonthly> lstProfileMonthly = new List<Ins_ProfileInsuranceMonthly>();
List<Ins_ProfileInsuranceMonthly> lstProfileMonthlyEditing = new List<Ins_ProfileInsuranceMonthly>();
List<Ins_ReportD02Item> lstD02Item = new List<Ins_ReportD02Item>();
List<Ins_ReportD02Item> lstD02ItemEditing = new List<Ins_ReportD02Item>();
var insPayBacks = unitOfWork.CreateQueryable<Ins_InsuranceSalaryPayback>(Guid.Empty, m => payBackIds.Contains(m.ID)).ToList();
var insTypeD02Ids = insPayBacks.Select(m => m.TypeID).ToList();
var insTypeD02s = unitOfWork.CreateQueryable<Ins_TypeD02>(Guid.Empty, m => insTypeD02Ids.Contains(m.ID)).ToList();
//lấy tỉ lệ bảo hiểm với ngày áp dụng trước ngày kiểm tra
var lstInsRate = unitOfWork.CreateQueryable<Cat_RateInsurance>(Guid.Empty, m => m.ApplyFrom <= endDayOfMonthYear).ToList();
Cat_RateInsurance rateInsurance = GetInsuranceRate(endDayOfMonthYear, lstInsRate);
var insMonthYearFrom = monthYear.AddMonths(-1);
insMonthYearFrom = new DateTime(insMonthYearFrom.Year, insMonthYearFrom.Month,PeriodInsuranceDayPreMonthDefault);
var insMonthYearTo = new DateTime(monthYear.Year, monthYear.Month,PeriodInsuranceDayCurrentMonthDefault );
var beginMonthYear = insPayBacks.Min(m => m.FromMonthEffect);
var endMonthYear = insPayBacks.Max(m => m.ToMonthEffect);
#region ds NV đóng Bảo Hiểm theo thang
var listInsMonthlyObj = new List<object> { null, beginMonthYear, endMonthYear, null, null, null, null, null, 1, int.MaxValue - 1 };
if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
{
listInsMonthlyObj.Add("id");
}
var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthlyFromTo,userLogin, ref status).Translate<Ins_ProfileInsuranceMonthly>(); ;
var profileIds = insPayBacks.Select(m => m.ProfileID).ToList();
var hreProfiles = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, m => m.SocialInsPlaceID.HasValue && profileIds.Contains(m.ID)).Select(m => new { ID = m.ID, SocialInsPlaceID = m.SocialInsPlaceID.Value }).ToList();
if (profileIds.Any())
{
lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(m => profileIds.Contains(m.ProfileID)).ToList();
}
#endregion
foreach (var payBack in insPayBacks)
{
if (payBack.FromMonthEffect.HasValue && payBack.ToMonthEffect.HasValue)
{
payBack.IsCallPayBack = true;
//duyet tung nv
var profileID = payBack.ProfileID;
#region xoa truoc khi tinh toan truy lĩnh
var profMonthlyDelete = unitOfWork.CreateQueryable<Ins_ProfileInsuranceMonthly>(Guid.Empty, m => m.ProfileID == profileID
&& m.MonthYear == payBack.MonthYear && (m.IsPayback == null || (m.IsPayback.HasValue && m.IsPayback.Value ==false)) ).FirstOrDefault();
if (profMonthlyDelete != null)
{
unitOfWork.RemoveObject(profMonthlyDelete);
unitOfWork.SaveChanges();
}
#endregion
#region lấy nơi đóng bảo hiểm của NV
var prof = hreProfiles.Where(m => m.ID == profileID).FirstOrDefault();
Guid? socialInsPlaceID = null;
if (prof != null)
{
socialInsPlaceID = prof.SocialInsPlaceID;
}
#endregion
for (var i = payBack.FromMonthEffect.Value; i <= payBack.ToMonthEffect.Value; i = i.AddMonths(1))
{
#region Ins_ProfileInsuranceMonthly
#region edit
var monthlyExist = lstProfileInsuranceMonthlyInDb.Where(m => m.ProfileID == profileID && m.MonthYearEffect == i && m.IsPayback == true).FirstOrDefault();
if (monthlyExist != null)
{
monthlyExist.IsSocialInsurance = payBack.IsSocialIns;
monthlyExist.IsHealthInsurance = payBack.IsMedicalIns;
monthlyExist.IsUnEmpInsurance = payBack.IsUnemploymentIns;
monthlyExist.MonthYear = payBack.MonthYear;
monthlyExist.MonthYearEffect = i;
monthlyExist.Allowance1 = 0;
monthlyExist.Allowance2 = 0;
monthlyExist.Allowance3 = 0;
monthlyExist.Allowance4 = 0;
monthlyExist.AllowanceAdditional = 0;
monthlyExist.JobName = payBack.JobtitleName;
monthlyExist.AmountHDTIns = payBack.AmoutHDTInsPayBack - payBack.AmoutHDTIns;
monthlyExist.PaybackID = payBack.ID;
monthlyExist.SalaryInsurance = payBack.InsSalaryAdjust;
monthlyExist.SalaryHealthInsurance = payBack.InsSalaryAdjust;
monthlyExist.SalaryUnEmpInsurance = payBack.InsSalaryAdjust;
monthlyExist.SocialInsPlaceID = socialInsPlaceID;
if (rateInsurance != null)
{
monthlyExist.SocialInsComRate = rateInsurance.SocialInsCompRate;
monthlyExist.SocialInsEmpRate = rateInsurance.SocialInsEmpRate;
monthlyExist.HealthInsComRate = rateInsurance.HealthInsCompRate;
monthlyExist.HealthInsEmpRate = rateInsurance.HealthInsEmpRate;
monthlyExist.UnemployComRate = rateInsurance.UnemployInsCompRate;
monthlyExist.UnemployEmpRate = rateInsurance.UnemployInsEmpRate;
var moneyInsuranceTotal = payBack.InsSalaryAdjust ?? 0 + payBack.AmoutHDTIns ?? 0;
#region Set tỉ lệ và tiền đóng BHXH,BHYT,BHTN
if (monthlyExist.IsSocialInsurance == true)
{
//tong ti le BHXH do NSDLĐ và NLĐ đóng
double rate = (rateInsurance.SocialInsCompRate + rateInsurance.SocialInsEmpRate);
monthlyExist.MoneySocialInsurance = (float)(moneyInsuranceTotal * rate);
#region [Tung.Ly ]: ti le BHXH cty dong va ti le BHXH nv dong
//Số tiền BHXH do cty đóng
monthlyExist.SocialInsComAmount = (double)(moneyInsuranceTotal * monthlyExist.SocialInsComRate);
//số tiền BHXH do NV đóng
monthlyExist.SocialInsEmpAmount = (double)(moneyInsuranceTotal * monthlyExist.SocialInsEmpRate);
#endregion
}
if (monthlyExist.IsHealthInsurance == true)
{
//tong ti le BHYT do NSDLĐ và NLĐ đóng
double rate = (rateInsurance.HealthInsCompRate + rateInsurance.HealthInsEmpRate);
monthlyExist.MoneyHealthInsurance = (float)(moneyInsuranceTotal * rate);
#region [Tung.Ly ]: ti le BHXH cty dong va ti le BHXH nv dong
//Số tiền BHYT do cty đóng
monthlyExist.HealthInsComAmount = (double)(moneyInsuranceTotal * monthlyExist.HealthInsComRate);
//số tiền BHYT do NV đóng
monthlyExist.HealthInsEmpAmount = (double)(moneyInsuranceTotal * monthlyExist.HealthInsEmpRate);
#endregion
}
if (monthlyExist.IsUnEmpInsurance == true)
{
//tong ti le BHTN do NSDLĐ và NLĐ đóng
double rate = (rateInsurance.UnemployInsCompRate + rateInsurance.UnemployInsEmpRate);
monthlyExist.MoneyUnEmpInsurance = (float)(moneyInsuranceTotal * rate);
#region [Tung.Ly ]: ti le BHTN cty dong va ti le BHXH nv dong
//Số tiền BHTN do cty đóng
monthlyExist.UnemployComAmount = (double)(moneyInsuranceTotal * monthlyExist.UnemployComRate);
//số tiền BHTN do NV đóng
monthlyExist.UnemployEmpAmount = (double)(moneyInsuranceTotal * monthlyExist.UnemployEmpRate);
#endregion
}
monthlyExist.AmountChargeIns = monthlyExist.Allowance1 + monthlyExist.Allowance2 +
monthlyExist.Allowance3 + monthlyExist.Allowance4 + moneyInsuranceTotal;
#endregion
}
lstProfileMonthlyEditing.Add(monthlyExist);
}
#endregion
#region them moi
else
{
Ins_ProfileInsuranceMonthly proMonthly = new Ins_ProfileInsuranceMonthly();
proMonthly.MonthYear = payBack.MonthYear;
proMonthly.ProfileID = payBack.ProfileID;
proMonthly.MonthYearEffect = i;
proMonthly.IsPayback = true;
proMonthly.IsSocialInsurance = payBack.IsSocialIns;
proMonthly.IsHealthInsurance = payBack.IsMedicalIns;
proMonthly.IsUnEmpInsurance = payBack.IsUnemploymentIns;
proMonthly.SalaryInsurance = payBack.InsSalaryAdjust;
proMonthly.SalaryHealthInsurance = payBack.InsSalaryAdjust;
proMonthly.SalaryUnEmpInsurance = payBack.InsSalaryAdjust;
proMonthly.JobName = payBack.JobtitleName;
proMonthly.AmountHDTIns = payBack.AmoutHDTInsPayBack - payBack.AmoutHDTIns;
proMonthly.PaybackID = payBack.ID;
proMonthly.SocialInsPlaceID = socialInsPlaceID;
if (rateInsurance != null)
{
proMonthly.SocialInsComRate = rateInsurance.SocialInsCompRate;
proMonthly.SocialInsEmpRate = rateInsurance.SocialInsEmpRate;
proMonthly.HealthInsComRate = rateInsurance.HealthInsCompRate;
proMonthly.HealthInsEmpRate = rateInsurance.HealthInsEmpRate;
proMonthly.UnemployComRate = rateInsurance.UnemployInsCompRate;
proMonthly.UnemployEmpRate = rateInsurance.UnemployInsEmpRate;
var moneyInsuranceTotal = (payBack.InsSalaryAdjust ?? 0) + (payBack.AmoutHDTIns ?? 0);
#region Set tỉ lệ và tiền đóng BHXH,BHYT,BHTN
if (proMonthly.IsSocialInsurance == true)
{
//tong ti le BHXH do NSDLĐ và NLĐ đóng
double rate = (rateInsurance.SocialInsCompRate + rateInsurance.SocialInsEmpRate);
proMonthly.MoneySocialInsurance = (float)(moneyInsuranceTotal * rate);
#region [Tung.Ly ]: ti le BHXH cty dong va ti le BHXH nv dong
//Số tiền BHXH do cty đóng
proMonthly.SocialInsComAmount = (double)(moneyInsuranceTotal * proMonthly.SocialInsComRate);
//số tiền BHXH do NV đóng
proMonthly.SocialInsEmpAmount = (double)(moneyInsuranceTotal * proMonthly.SocialInsEmpRate);
#endregion
}
if (proMonthly.IsHealthInsurance == true)
{
//tong ti le BHYT do NSDLĐ và NLĐ đóng
double rate = (rateInsurance.HealthInsCompRate + rateInsurance.HealthInsEmpRate);
proMonthly.MoneyHealthInsurance = (float)(moneyInsuranceTotal * rate);
#region [Tung.Ly ]: ti le BHXH cty dong va ti le BHXH nv dong
//Số tiền BHYT do cty đóng
proMonthly.HealthInsComAmount = (double)(moneyInsuranceTotal * proMonthly.HealthInsComRate);
//số tiền BHYT do NV đóng
proMonthly.HealthInsEmpAmount = (double)(moneyInsuranceTotal * proMonthly.HealthInsEmpRate);
#endregion
}
if (proMonthly.IsUnEmpInsurance == true)
{
//tong ti le BHTN do NSDLĐ và NLĐ đóng
double rate = (rateInsurance.UnemployInsCompRate + rateInsurance.UnemployInsEmpRate);
proMonthly.MoneyUnEmpInsurance = (float)(moneyInsuranceTotal * rate);
#region [Tung.Ly ]: ti le BHTN cty dong va ti le BHXH nv dong
//Số tiền BHTN do cty đóng
proMonthly.UnemployComAmount = (double)(moneyInsuranceTotal * proMonthly.UnemployComRate);
//số tiền BHTN do NV đóng
proMonthly.UnemployEmpAmount = (double)(moneyInsuranceTotal * proMonthly.UnemployEmpRate);
#endregion
}
proMonthly.AmountChargeIns = proMonthly.Allowance1 + proMonthly.Allowance2 +
proMonthly.Allowance3 + proMonthly.Allowance4 + moneyInsuranceTotal;
#endregion
}
proMonthly.ID = Guid.NewGuid();
lstProfileMonthly.Add(proMonthly);
}
#endregion
#endregion
}
#region Ins_ReportD02
#region d02
var periodD02 = EnumDropDown.GetEnumDescription<PeriodInsurance>(PeriodInsurance.GETTEMP);
var cutOfDuration = "Kỳ " + periodD02 + " - " + payBack.MonthYear.Value.ToString(ConstantFormat.HRM_Format_MonthYear);
var reportD02Existed = unitOfWork.CreateQueryable<Ins_ReportD02>(p => p.DateMonth.HasValue
&& p.DateMonth.Value.Month == payBack.MonthYear.Value.Month && p.DateMonth.Value.Year == payBack.MonthYear.Value.Year
&& p.ReportD02Name == cutOfDuration).FirstOrDefault();
var reportD02ID = Guid.Empty;
#endregion
if (reportD02Existed == null)
{
//insert D02Report
reportD02 = new Ins_ReportD02
{
DateReport = DateTime.Now,
DateStart = insMonthYearFrom,
DateEnd = insMonthYearTo,
DateMonth = payBack.MonthYear,
ReportD02Name = cutOfDuration,
MaxSalary = 0,
MinSalary = 0,
SociaInsCountPro = 0,
SociaInsTotalSalary = 0,
HealthInsCountPro = 0,
HealthInsTotalSalary = 0,
UnEmpInsCountPro = 0,
Type = PeriodInsurance.GETTEMP.ToString(),
UnEmpInsTotalSalary = 0
};
repoInsReportD02.Add(reportD02);
var statusCode = repoInsReportD02.SaveChanges();
reportD02ID = reportD02.ID;
reportD02Existed = unitOfWork.CreateQueryable<Ins_ReportD02>(Guid.Empty, m => m.ID == reportD02ID).FirstOrDefault();
}
if (reportD02Existed != null)
{
var insTypeD02 = insTypeD02s.Where(m => m.ID == payBack.TypeID).FirstOrDefault();
var reportD02ItemExisted = unitOfWork.CreateQueryable<Ins_ReportD02Item>(m => m.ReportD02ID == reportD02Existed.ID && m.PayBackID == payBack.ID).FirstOrDefault();
var monthFrom = payBack.MonthYear.Value.AddMonths(-1);
monthFrom = new DateTime(monthFrom.Year, monthFrom.Month,PeriodInsuranceDayPreMonthDefault );
var monthTo = new DateTime(payBack.MonthYear.Value.Year, payBack.MonthYear.Value.Month, PeriodInsuranceDayCurrentMonthDefault);
#region delete d02 truoc khi tinh truy lĩnh
#region xoa D02Item truoc khi tinh toan truy lĩnh
if (reportD02Existed != null && reportD02Existed.ID != Guid.Empty)
{
var reportD02ItemDelete = unitOfWork.CreateQueryable<Ins_ReportD02Item>(Guid.Empty, m => m.ReportD02ID == reportD02Existed.ID && m.ProfileID == profileID && m.IsPayBack == null).ToList();
if (reportD02ItemDelete != null)
{
unitOfWork.RemoveObject(typeof(Ins_ReportD02Item), reportD02ItemDelete.ToArray());
unitOfWork.SaveChanges();
}
}
#endregion
if (reportD02ItemExisted != null)
{
#region Edit
reportD02ItemExisted.ProfileID = profileID;
reportD02ItemExisted.ReportD02ID = reportD02Existed.ID;
reportD02ItemExisted.OldBasicSalary = payBack.InsSalary;
reportD02ItemExisted.NewBasicSalary = payBack.InsSalaryAdjust;
reportD02ItemExisted.SocialInsPlaceID = socialInsPlaceID;
reportD02ItemExisted.IsPayBack = true;
reportD02ItemExisted.PayBackID = payBack.ID;
reportD02ItemExisted.MonthFrom = payBack.FromMonthEffect;
reportD02ItemExisted.MonthTo = payBack.FromMonthEffect;
reportD02ItemExisted.RateHealthIns = payBack.HealthInsComRate + payBack.HealthInsEmpRate;
reportD02ItemExisted.RateSocialIns = payBack.SocialInsComRate + payBack.SocialInsEmpRate;
reportD02ItemExisted.RateUnEmpIns = payBack.UnemployComRate + payBack.UnemployEmpRate;
reportD02ItemExisted.JobName = payBack.JobtitleName;
if (insTypeD02 != null)
{
reportD02ItemExisted.Status = insTypeD02.CommentCode;
reportD02ItemExisted.Type = insTypeD02.TypeCode;
reportD02ItemExisted.Comment = payBack.Comment;
}
if (!lstD02ItemEditing.Any(m => m.ID == reportD02ItemExisted.ID))
{
lstD02ItemEditing.Add(reportD02ItemExisted);
}
#endregion
}
else
{
#region Add new
var d02Item = new Ins_ReportD02Item();
d02Item.ProfileID = profileID;
d02Item.ReportD02ID = reportD02Existed.ID;
d02Item.OldBasicSalary = payBack.InsSalary;
d02Item.NewBasicSalary = payBack.InsSalaryAdjust;
d02Item.MonthFrom = payBack.FromMonthEffect;
d02Item.MonthTo = payBack.FromMonthEffect;
d02Item.IsPayBack = true;
d02Item.PayBackID = payBack.ID;
d02Item.JobName = payBack.JobtitleName;
d02Item.SocialInsPlaceID = socialInsPlaceID;
d02Item.RateHealthIns = payBack.HealthInsComRate + payBack.HealthInsEmpRate;
d02Item.RateSocialIns = payBack.SocialInsComRate + payBack.SocialInsEmpRate;
d02Item.RateUnEmpIns = payBack.UnemployComRate + payBack.UnemployEmpRate;
if (insTypeD02 != null)
{
d02Item.Status = insTypeD02.CommentCode;
d02Item.Type = insTypeD02.TypeCode;
d02Item.Comment = payBack.Comment;
}
lstD02Item.Add(d02Item);
#endregion
}
}
#endregion
#endregion
}
#region Save Change
if (lstProfileMonthly.Any())
{
repoInsMonthly.Add(lstProfileMonthly);
}
if (lstProfileMonthlyEditing.Any())
{
repoInsMonthly.Edit(lstProfileMonthlyEditing);
}
if (lstD02Item.Any())
{
repoInsReportD02Item.Add(lstD02Item);
}
if (lstD02ItemEditing.Any())
{
repoInsReportD02Item.Edit(lstD02ItemEditing);
}
repoInsMonthly.SaveChanges();
#endregion
}
}
}
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);
}
}
/// <summary> Compute D02 và xuat bao cao D02 </summary>
/// <param name="all"></param>
/// <param name="increase"></param>
/// <param name="descrease"></param>
/// <param name="dtMonthYear"></param>
/// <param name="orgIds"></param>
/// <returns></returns>
public List<Ins_InsuranceReportD02Entity> LoadData(bool? all, bool? increase, bool? descrease, DateTime? dtMonthYear, string orgIds, string searchNoteType, string searchStatus, string codeEmp, List<Guid> socialInsPlaceIDs, string userLogin)
{
var monthYear = dtMonthYear ?? DateTime.Now;
DateTime dateFrom = monthYear.AddMonths(-1);
dateFrom = new DateTime(dateFrom.Year, dateFrom.Month, InsuranceServices.PeriodInsuranceDayPreMonthDefault);
DateTime dateTo = new DateTime(monthYear.Year, monthYear.Month, InsuranceServices.PeriodInsuranceDayCurrentMonthDefault);
var status = string.Empty;
var allCheck = all ?? false;
var inCreaseCheck = increase ?? false;
var descreaseCheck = descrease ?? false;
DateTime dateFromNewHONDA = new DateTime(dateFrom.Year, dateFrom.Month, PeriodInsuranceDayPreMonth);//18
DateTime dateToNewHONDA = new DateTime(dateTo.Year, dateTo.Month, PeriodInsuranceDayCurrentMonth);//17
//
int? sociaInsCountPro = 0;
double? sociaInsTotalSalary = 0;
int? healthInsCountPro = 0;
double? healthInsTotalSalary = 0;
int? unEmpInsCountPro = 0;
double? unEmpInsTotalSalary = 0;
double? maxSalary = long.MinValue + 1;
double? minSalary = long.MaxValue - 1;
//
//lay ds Ins_ProfileInsuranceMonthly
//List<object> listInsMonthlyObj = new List<object>();
//listInsMonthlyObj.Add(orgs);
//listInsMonthlyObj.Add(monthYear);
//var profileInsMonthlys = GetData<Hre_ProfileEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, ref status);
Hashtable htbData = new Hashtable();
var d02Reports = new List<Ins_InsuranceReportD02Entity>();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var profileInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var orgStructureRepo = new Cat_OrgStructureRepository(unitOfWork);
var insInsuranceRecord = new Ins_InsuranceRecordRepository(unitOfWork);
var hre_ContractRepo = new Hre_ContractRepository(unitOfWork);
var cat_ContractTypeRepo = new Cat_ContractTypeRepository(unitOfWork);
var att_LeaveDayRepo = new Att_LeavedayRepository(unitOfWork);
#region Lấy DS Ins_ProfileInsuranceMonthly (lấy tất cả)
var listInsMonthlyObj = new List<object> { orgIds, null, null, 1, int.MaxValue - 1 };
if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
{
listInsMonthlyObj.Add("id");
}
var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status);
#endregion
//get profiles trong Ins_ProfileInsuranceMonthly theo thang kiểm tra
var lstProfile = lstProfileInsuranceMonthlyInDb.Where(s => (s.IsDelete == null || s.IsDelete.Value==false) && s.MonthYear.HasValue
&& (s.MonthYear.Value.Year == dateFrom.Year || s.MonthYear.Value.Year == dateTo.Year)
&& (s.MonthYear.Value.Month == dateFrom.Month || s.MonthYear.Value.Month == dateTo.Month)
&& s.MonthYearEffect.HasValue && s.MonthYear == s.MonthYearEffect).ToList().Translate<Ins_ProfileInsuranceMonthly>();
#region lay ins_ProfileInsuranceMonthly de tinh thai san 1 nam lien ke
//Lấy Ds Ins_ProfileInsuranceMonthly 12 tháng trước so với tháng kiểm tra (de tinh thai san 1 nam lien ke)
List<Ins_ProfileInsuranceMonthlyEntity> lstProfilePregnant = null;
#endregion
//var lstProfile = profileInsMonthly.FindBy(s => s.IsDelete == null && s.MonthYear.HasValue
// && (s.MonthYear.Value.Year == dateFrom.Year || s.MonthYear.Value.Year == dateTo.Year)
// && (s.MonthYear.Value.Month == dateFrom.Month || s.MonthYear.Value.Month == dateTo.Month)).ToList();
//get InsuranceRecord (Chứng từ bảo hiểm)
var lstInsuranceRecord = insInsuranceRecord.GetAll().Where(p => p.IsDelete == null).ToList();// lay tat ca chứng từ bảo hiểm (sau nay sửa lai sau)
#region Lay ds Phong Ban
var orgs = GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).ToList();
List<Cat_OrgStructureEntity> orgStructures = orgs.Translate<Cat_OrgStructureEntity>();
#endregion
#region Lấy profile theo phong ban
List<object> listObj = new List<object>();
listObj.Add(orgIds);
listObj.Add(string.Empty);
listObj.Add(string.Empty);
var profilebyOrgs = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status);
if (!string.IsNullOrEmpty(codeEmp))
{
profilebyOrgs = profilebyOrgs.Where(p => p.CodeEmp == codeEmp).ToList();
}
else if (profilebyOrgs != null)
{
profilebyOrgs = profilebyOrgs.Where(p => p.DateHire != null
&& (p.DateQuit == null || p.DateQuit > dateFrom)
&& p.DateHire <= monthYear).ToList();
}
var profileIds = profilebyOrgs.Select(p => p.ID).ToList();
#endregion
#region lay ds hop dong đã ký so với tháng chọn (Loại HD có đóng BH)
var contractTypeObjs = cat_ContractTypeRepo.FindBy(p => p.IsDelete == null).ToList();
//Ds loại HĐ đóng BHXH
var contractSocialTypeIDObjs = contractTypeObjs.Where(p => p.IsSocialInsurance.HasValue && p.IsSocialInsurance.Value).ToList();
//Ds loại HĐ đóng BHTN
var contractUnEmpTypeIDObjs = contractTypeObjs.Where(p => p.IsUnEmployInsurance.HasValue && p.IsUnEmployInsurance.Value).ToList();
var contracts = new List<Hre_Contract>();
var contractSocials = new List<Hre_Contract>();
var contractUnEmps = new List<Hre_Contract>();
if (contractSocialTypeIDObjs.Any())
{
var contractSocialTypeIds = contractSocialTypeIDObjs.Select(p => p.ID).ToList();
var contractUnEmpTypeIds = contractUnEmpTypeIDObjs.Select(p => p.ID).ToList();
//Ds Hop Đồng của cac NV
contracts = hre_ContractRepo.FindBy(p => p.IsDelete == null && profileIds.Contains(p.ProfileID)).OrderBy(p => p.DateStart).ToList();
//Ds Hop đồng thuộc loại đóng BHXH
contractSocials = contracts.Where(p => contractSocialTypeIds.Contains(p.ContractTypeID)).OrderBy(p => p.DateStart).ToList();
//Ds Hop đồng thuộc loại đóng BHTN
contractUnEmps = contracts.Where(p => contractUnEmpTypeIds.Contains(p.ContractTypeID)).OrderBy(p => p.DateStart).ToList();
}
#endregion
#region lấy Leaveday
string E_APPROVED = LeaveDayStatus.E_APPROVED.ToString();
string E_PREGNANCY_SUCKLE = InsuranceRecordType.E_PREGNANCY_SUCKLE.ToString();
var lstLeaveday = att_LeaveDayRepo.FindBy(m => m.IsDelete == null && m.Status == E_APPROVED && m.DateStart <= dateTo && m.DateEnd >= dateFrom && m.Cat_LeaveDayType != null
&& m.Cat_LeaveDayType.InsuranceType == E_PREGNANCY_SUCKLE && profileIds.Contains(m.ProfileID)).ToList();
#endregion
List<object> salInsuranceSalaryParams = new List<object>();
salInsuranceSalaryParams.Add(null);
salInsuranceSalaryParams.Add(null);
salInsuranceSalaryParams.Add(orgIds);
salInsuranceSalaryParams.Add(null);
salInsuranceSalaryParams.Add(null);
salInsuranceSalaryParams.Add(null);
salInsuranceSalaryParams.Add(null);
salInsuranceSalaryParams.Add(1);
salInsuranceSalaryParams.Add(Int32.MaxValue - 1);
var dateEnd = new DateTime(dateTo.Year, dateTo.Month, DateTime.DaysInMonth(dateTo.Year, dateTo.Month));
var lstInsuranceSalaryByProfile = GetData<Sal_InsuranceSalary>(salInsuranceSalaryParams, ConstantSql.hrm_sal_sp_get_InsuranceSalary, userLogin, ref status)
.Where(p => p.DateEffect <= dateEnd && profileIds.Contains(p.ProfileID ?? Guid.Empty) && p.InsuranceAmount.HasValue).ToList();
if (socialInsPlaceIDs != null)
{
socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any())
{
lstProfile = lstProfile.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList();
}
}
foreach (var profile in profilebyOrgs)
{
//lay các chung tu bao hiem cua nv
List<Ins_InsuranceRecord> lstInsRecordProfile = lstInsuranceRecord.Where(rec => rec.ProfileID == profile.ID).ToList();
var lstleavePregByProfile = lstLeaveday.Where(rec => rec.ProfileID == profile.ID).ToList();
var lstInsRecordPregByProfile = lstInsRecordProfile.Where(m => m.InsuranceType == E_PREGNANCY_SUCKLE).ToList();
var profileMonthlys = lstProfile.Where(p => p.ProfileID == profile.ID).OrderBy(p => p.MonthYear).ToList();
var inFirst = profileMonthlys.FirstOrDefault(p => p.MonthYear.HasValue && p.MonthYear.Value.Month == dateFrom.Month);
var inNow = profileMonthlys.LastOrDefault(p => p.MonthYear.HasValue && p.MonthYear.Value.Month == dateTo.Month);
if (inFirst == null)
{
inFirst = new Ins_ProfileInsuranceMonthly
{
IsHealthInsurance = false,
IsSocialInsurance = false,
IsUnEmpInsurance = false
};
}
if (inNow != null)
{
profile.JobName = inNow.JobName;
#region set Phu cap
if (inNow != null)
{
profile.Allowance1 = inNow.Allowance1;
profile.Allowance2 = inNow.Allowance2;
profile.Allowance3 = inNow.Allowance3;
profile.Allowance4 = inNow.Allowance4;
}
#endregion
#region Thống kê số lượng người đóng BHXH,BHYT,BHTN và tổng lương BHXH,BHTN,BHTN
if (inNow.IsSocialInsurance.HasValue && inNow.IsSocialInsurance.Value)
{
sociaInsCountPro++;
sociaInsTotalSalary += inNow.MoneySocialInsurance;
}
if (inNow.IsHealthInsurance.HasValue && inNow.IsHealthInsurance.Value)
{
healthInsCountPro++;
healthInsTotalSalary += inNow.MoneyHealthInsurance;
}
if (inNow.IsUnEmpInsurance.HasValue && inNow.IsUnEmpInsurance.Value)
{
unEmpInsCountPro++;
unEmpInsTotalSalary += inNow.MoneyUnEmpInsurance;
}
if (inNow.SalaryInsurance.HasValue && inNow.SalaryInsurance.Value > maxSalary)
{
maxSalary = inNow.SalaryInsurance.Value;
}
if (inNow.SalaryInsurance.HasValue && inNow.SalaryInsurance.Value < minSalary)
{
minSalary = inNow.SalaryInsurance.Value;
}
#endregion
#region tang
if (IsIncreaseInsurance(inFirst, inNow) && (inCreaseCheck == true || allCheck == true))
{
String strPre = InsuranceRecordType.E_PREGNANCY_SUCKLE.ToString();
List<Ins_InsuranceRecord> lstInsPre = lstInsRecordProfile.Where(ins => ins.InsuranceType == strPre && ins.DateEnd > dateFrom && ins.DateEnd < dateTo).ToList();
String strSickLong = InsuranceRecordType.E_SICK_LONG.ToString();
String strSickShort = InsuranceRecordType.E_SICK_SHORT.ToString();
List<Ins_InsuranceRecord> lstInsSick = lstInsRecordProfile.Where(ins => ins.InsuranceType == strSickLong || ins.InsuranceType == strSickShort && ins.DayCount > 30
&& ins.DateEnd > dateFrom && ins.DateEnd < dateTo).ToList();
#region Tăng LĐ
//lay ds hop dong cua NV
var contractProfile = contractSocials.Where(p => p.ProfileID == profile.ID).OrderBy(p => p.DateStart).FirstOrDefault();
Hre_Contract contract = null;
//lay hd moi nhat cua nv nằm trong khoảng 16-[N-1] đến 15-[N]
if (contractProfile != null && dateFromNewHONDA <= contractProfile.DateStart && contractProfile.DateStart <= dateToNewHONDA)
{
contract = contractProfile;
}
#region Tăng do Nghỉ 14 ngày đi làm lại
//Kiểm tra Tháng N-1 có ký HD ?
//tang moi lao dong (Tháng N-1 ký HD nhưng chưa đóng BH do nghỉ hơn 14 ngày , Tháng N đóng bảo hiểm => thuoc loại tăng mới LĐ
if (inFirst.IsDecreaseWorkingDays.HasValue && inFirst.IsDecreaseWorkingDays.Value)
{
var contractDatefrom = new DateTime(dateFrom.Year, dateFrom.Month, 1);
var contractDateTo = contractDatefrom.AddMonths(1).AddDays(-1);
if (contractProfile != null && contractDatefrom <= contractProfile.DateStart && contractProfile.DateStart <= contractDateTo)
{
//ký hợp đồng tháng trước
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_LD.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "I.1");
}
else
{
//tang do nghỉ 14 ngày đi làm lại
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_LEAVE_14WORKINGDAYS.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "I.1");
}
}
#endregion
else if (contract != null)
{
//lay hd moi nhat cua nv nằm trong khoảng 16-[N-1] đến 15-[N]=>tăng lao động
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_LD.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "I.1");
}
#endregion
#region Tăng Thai Sản
else if (HasPregnant(dateTo.AddMonths(-1), lstleavePregByProfile, lstInsRecordPregByProfile))
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_TS.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "I.1");
}
#endregion
#region Tăng Bệnh
else if (lstInsSick.Count() > 0 /* && allCheck == false*/)
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_BENH.ToString(), inFirst.MoneySocialInsurance, inNow.MoneySocialInsurance, monthYear, orgStructures, "I.1");
}
#endregion
#region Honda - Tăng Đóng BHTN
else if ((!inFirst.IsUnEmpInsurance.HasValue || (inFirst.IsUnEmpInsurance.HasValue &&
!inFirst.IsUnEmpInsurance.Value))
&& inNow.IsUnEmpInsurance.HasValue && inNow.IsUnEmpInsurance.Value)
{
var contractUnEmpProfile = contractUnEmps.Where(p => p.ProfileID == profile.ID).OrderBy(p => p.DateStart).FirstOrDefault();
if (contractUnEmpProfile != null && dateFrom <= contractUnEmpProfile.DateStart && contractUnEmpProfile.DateStart <= dateTo)
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_BHTN.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "I.2");
}
}
#endregion
#region Tăng BHYT
else if ((!inFirst.IsHealthInsurance.HasValue || (inFirst.IsHealthInsurance.HasValue &&
!inFirst.IsHealthInsurance.Value))
&& inNow.IsHealthInsurance.HasValue && inNow.IsHealthInsurance.Value)
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_BHYT.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "I.1");
}
#endregion
}
#endregion
#region giam
if (IsDecreaseInsurance(inFirst, inNow) && (descreaseCheck == true || allCheck == true))
{
String strPre = InsuranceRecordType.E_PREGNANCY_SUCKLE.ToString();
List<Ins_InsuranceRecord> lstInsPre = lstInsRecordProfile.Where(ins => ins.InsuranceType == strPre && ins.DateEnd > dateFrom && ins.DateStart < dateTo).ToList();
String strSickLong = InsuranceRecordType.E_SICK_LONG.ToString();
String strSickShort = InsuranceRecordType.E_SICK_SHORT.ToString();
List<Ins_InsuranceRecord> lstInsSick = lstInsRecordProfile.Where(ins => ins.InsuranceType == strSickLong && ins.InsuranceType == strSickShort
&& ins.DayCount > 30
&& ins.DateEnd > dateFrom && ins.DateEnd < dateTo).ToList();
if (inFirst.IsDecreaseWorkingDays == null)
{
inFirst.IsDecreaseWorkingDays = false;
}
if (inNow.IsDecreaseWorkingDays == null)
{
inNow.IsDecreaseWorkingDays = false;
}
#region giam
//neu NV nghỉ từ 16 tháng [N-1] đến 15 tháng N => Tạm Hoãn hoặc nghỉ luôn
if (profile.DateQuit.HasValue && dateFrom <= profile.DateQuit && profile.DateQuit <= dateTo)
{
#region Set trạng thái trả thẻ
var statusGiam_BHYT = string.Empty;
if (profile.ReceiveHealthIns.HasValue && profile.ReceiveHealthIns.Value)
{
// Giam LD tra the
statusGiam_BHYT = TypeInsuranceD02TS.E_GIAM_LD_BHYT.ToString();
}
else if ((!profile.ReceiveHealthIns.HasValue || profile.ReceiveHealthIns == false))
{
// Giảm Lao Động Không Trả Thẻ
statusGiam_BHYT = TypeInsuranceD02TS.E_GIAM_LD_NOT_BHYT.ToString();
}
else
{
// Giảm LĐ
statusGiam_BHYT = TypeInsuranceD02TS.E_GIAM_LD.ToString();
}
//HonDa
statusGiam_BHYT = TypeInsuranceD02TS.E_GIAM_LD_BHYT.ToString();
#endregion
if (profile.StopWorkType == EnumDropDown.StopWorkType.E_SUSPENSE.ToString())
{
//tạm hoãn
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_QUIT_SUSPENSE.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
else if (!string.IsNullOrEmpty(statusGiam_BHYT))
{
#region Nghỉ Việc Sau Khi Nghỉ Sinh Con
if (HasPregnant(dateTo.AddMonths(-1), lstleavePregByProfile, lstInsRecordPregByProfile))
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_TS_QUIT.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
else
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), statusGiam_BHYT, inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
#endregion
}
}
#region Giam Thai San
else if (HasPregnant(dateTo, lstleavePregByProfile, lstInsRecordPregByProfile)
&& (!profile.DateQuit.HasValue || profile.DateQuit.Value > dateTo))
{
if (lstProfilePregnant == null)
{
//chỉ lay 1 lần
#region lay ins_ProfileInsuranceMonthly de tinh thai san 1 nam lien ke
var dtPreDateCheck = dateFrom.AddYears(-1);
//Lấy Ds Ins_ProfileInsuranceMonthly 12 tháng trước so với tháng kiểm tra (de tinh thai san 1 nam lien ke)
lstProfilePregnant = lstProfileInsuranceMonthlyInDb.Where(s => s.MonthYear.HasValue
&& (dtPreDateCheck <= s.MonthYear.Value && s.MonthYear <= dateFrom)).ToList();
#endregion
}
// Nhân viên đóng bảo hiểm đủ 6 tháng trong vòng 12 tháng liền kề
var insCount = lstProfilePregnant.Where(p => p.ProfileID == profile.ID && p.IsSocialInsurance.HasValue && p.IsSocialInsurance.Value).Count();
if (insCount >= 6)
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_TS.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
else
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_LEAVE_14WORKINGDAYS.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
}
#endregion
#region Giam benh
else if (lstInsSick.Count() > 0 /*&& allCheck == false*/)
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_BENH.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
#endregion
#region giam do Nghỉ 14 ngày đi làm lại
// tháng N là false (hoac null) => Giảm do nghỉ >= 14 ngày
else if (inNow.IsDecreaseWorkingDays.HasValue && inNow.IsDecreaseWorkingDays.Value)
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_LEAVE_14WORKINGDAYS.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
#endregion
#endregion
}//ket thuc giảm
#endregion
#region Thay doi luong
else if (inFirst != null && inFirst.SalaryInsurance != inNow.SalaryInsurance)
{
//-------Giam luong
if (inFirst != null && inFirst.SalaryInsurance > inNow.SalaryInsurance)
{
#region giảm lương và thay đổi chức danh nghề
if (inFirst.JobName != inNow.JobName)
{
//giảm lương và thay đổi chức danh nghề
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM_LUONG_CHANGEJOBNAME.ToString(), TypeInsuranceD02TS.E_GIAM_LUONG_CHANGEJOBNAME.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "IV.");
}
#endregion
#region Giảm Lương
else
{
//giảm lương
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_LUONG.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "IV.");
}
#endregion
}
//------------Tang luong-----------
else if (inFirst != null && inFirst.SalaryInsurance < inNow.SalaryInsurance)
{
#region tăng lương và thay đổi chức danh nghề
if (inFirst.JobName != inNow.JobName)
{
//tăng lương và thay đổi chức danh nghề
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG_LUONG_CHANGEJOBNAME.ToString(), TypeInsuranceD02TS.E_TANG_LUONG_CHANGEJOBNAME.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "III.");
}
#endregion
#region tăng lương
else
{
//tăng lương
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_LUONG.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "III.");
}
#endregion
}
}
#endregion
#region Thay đổi chức danh nghề (không đổi lương)
//Thay đổi chức danh nghề (không đổi lương)
if (inFirst != null && inNow != null && inFirst.SalaryInsurance == inNow.SalaryInsurance && inFirst.JobName != inNow.JobName)
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_CHANGEJOBNAME.ToString(), TypeInsuranceD02TS.E_CHANGEJOBNAME.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "V.");
}
#endregion
#region Trường Hợp đặc biệt (Honda)
#region Tháng N-1 nghỉ Thai Sản và tháng N nghỉ luon =>Nghỉ luôn sau thai sản
if (inFirst != null && inFirst.IsPregnant.HasValue && inFirst.IsPregnant.Value
&& inNow != null && (inNow.IsPregnant == null || inNow.IsPregnant.Value == false)
&& profile.DateQuit.HasValue && dateFrom <= profile.DateQuit && profile.DateQuit <= dateTo)
{
//>Nghỉ luôn sau thai sản
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_TS_QUIT.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
#region Tháng N-1 nghỉ Thai Sản và tháng N nghỉ hơn 14 ngày => Nghỉ 14 ngày sau thai sản
else if (inNow != null && inNow.IsDecreaseWorkingDays.HasValue && inNow.IsDecreaseWorkingDays.Value
&& HasPregnant(dateTo.AddMonths(-1), lstleavePregByProfile, lstInsRecordPregByProfile)
&& (inNow.IsPregnant == null || inNow.IsPregnant.Value == false))
{
//Giảm do nghỉ hơn 14 ngày sau thai sản
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_PREGNANT_14WORKINGDAYS.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
#endregion
#endregion
#region tháng N-1 giảm 14 ngày và tháng này nghỉ việc
if (profile != null && inFirst != null && inNow != null
&& !HasPregnant(dateTo.AddMonths(-1), lstleavePregByProfile, lstInsRecordPregByProfile)
&& inFirst.IsDecreaseWorkingDays.HasValue && inFirst.IsDecreaseWorkingDays.Value
&& profile.DateQuit.HasValue && dateFrom <= profile.DateQuit && profile.DateQuit <= dateTo)
{
//Nghỉ việc mà trước đó nghỉ >=14 ngày
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_LEAVE_PREMONTH_14WORKINGDAYS.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
#endregion
#region Nghiệp Vụ Chuyển Nơi Đóng BH (Honda)
if (inFirst != null && inNow != null
&& (inFirst.SocialInsPlaceID.HasValue && inFirst.SocialInsPlaceID.Value != Guid.Empty)
&& (inNow.SocialInsPlaceID.HasValue && inNow.SocialInsPlaceID.Value != Guid.Empty)
&& (inFirst.SocialInsPlaceID.Value != inNow.SocialInsPlaceID.Value))
{
//thang truoc giam do chuyen noi dong bao hiem
profile.JobName = string.Copy(inFirst.JobName);
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_LD_CHANGE_INSPLACE.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "I.1");
//thang check tang do chuyen noi dong bao hiem
profile.JobName = string.Copy(inNow.JobName);
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_LD_CHANGE_INSPLACE.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II.1");
}
#endregion
#endregion
}
}//ket thuc foreach profile
}
//duyet hashtable => danh sach nv theo loai bao hiem (tangLd,tangBHXH,.....)
var result = new List<Ins_InsuranceReportD02Entity>();
var tang = TypeInsuranceD02TS.E_TANG.ToString();
var giam = TypeInsuranceD02TS.E_GIAM.ToString();
var tangLD = TypeInsuranceD02TS.E_GIAM_LD.ToString();
foreach (DictionaryEntry entry in htbData)
{
var lst = new List<Ins_InsuranceReportD02Entity>();
lst = (List<Ins_InsuranceReportD02Entity>)entry.Value;
var d02Entity = lst.FirstOrDefault(p => p.OrderGroup != null);
var orderGroup = string.Empty;
if (d02Entity != null)
{
orderGroup = d02Entity.OrderGroup;
}
var proName = entry.Key.ToString();
if (!string.IsNullOrEmpty(proName))
{
proName = GetDescription(proName);
}
var title = new Ins_InsuranceReportD02Entity()
{
ProfileID = Guid.Empty,
ProfileName = proName,
OrderGroup = orderGroup,
CodeEmp = orderGroup,
BOLD = true.ToString()
};
lst.Insert(0, title);
if (lst.Any(p => p.Status1 == tang) && !result.Any(p => p.Status == tangLD) && !result.Any(p => p.ProfileName == "Tăng"))
{
lst.Insert(0, new Ins_InsuranceReportD02Entity { ProfileID = Guid.Empty, ProfileName = "Tăng", CodeEmp = "I", OrderGroup = "I" });
}
if (lst.Any(p => p.Status1 == giam) && !result.Any(p => p.ProfileName == "Giảm"))
{
lst.Insert(0, new Ins_InsuranceReportD02Entity { ProfileID = Guid.Empty, ProfileName = "Giảm", CodeEmp = "II", OrderGroup = "II" });
}
//if (lst.Any(p => p.Status == TypeInsuranceD02TS.E_GIAM_LD.ToString()) && !result.Any(p => p.Status1 == giam) && !result.Any(p => p.ProfileName == "Giảm Lao Động"))
//{
// lst.Insert(0, new Ins_InsuranceReportD02Entity { ProfileName = "Giảm Lao Động", CodeEmp = "II", OrderGroup = "II" });
//}
result.AddRange(lst);
}
result = result.OrderBy(p => p.OrderGroup).ToList();
#region compute D02
var periodIns = EnumDropDown.GetEnumDescription<PeriodInsurance>(PeriodInsurance.GETTEMP);
var d02 = new Ins_ReportD02Entity
{
DateReport = DateTime.Now,
SociaInsCountPro = sociaInsCountPro,
SociaInsTotalSalary = sociaInsTotalSalary,
HealthInsCountPro = healthInsCountPro,
HealthInsTotalSalary = healthInsTotalSalary,
UnEmpInsCountPro = unEmpInsCountPro,
UnEmpInsTotalSalary = unEmpInsTotalSalary,
MaxSalary = maxSalary,
MinSalary = minSalary,
Type = PeriodInsurance.GETTEMP.ToString()
};
//luu D02
ComputeD02(result, d02, monthYear, dateFrom, dateTo, periodIns);
#endregion
return result;
}
/// <summary> Tìm kiếm bc D02TS (Hien tai chua dùng toi method này) </summary>
/// <param name="all"></param>
/// <param name="increase"></param>
/// <param name="descrease"></param>
/// <param name="dtMonthYear"></param>
/// <param name="orgIds"></param>
/// <returns></returns>
public List<Ins_InsuranceReportD02Entity> SearchD02TS(bool? all, bool? increase, bool? descrease, DateTime? dtMonthYear, string orgIds, string searchNoteType, string searchStatus, string codeEmp, List<Guid> socialInsPlaceIDs, string userLogin)
{
var periodIns = EnumDropDown.GetEnumDescription<PeriodInsurance>(PeriodInsurance.GETTEMP);
List<Ins_InsuranceReportD02Entity> lstInsuranceReportD02 = new List<Ins_InsuranceReportD02Entity>();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoReportD02 = new Ins_ReportD02Repository(unitOfWork);
var repoReportD02Item = new Ins_ReportD02ItemRepository(unitOfWork);
var repoProfileInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
string status = string.Empty;
var monthYear = dtMonthYear ?? DateTime.Now;
DateTime dateFrom = monthYear.AddMonths(-1);
dateFrom = new DateTime(dateFrom.Year, dateFrom.Month, InsuranceServices.PeriodInsuranceDayPreMonthDefault);
DateTime dateTo = new DateTime(monthYear.Year, monthYear.Month, InsuranceServices.PeriodInsuranceDayCurrentMonthDefault);
#region Lấy DS Ins_ProfileInsuranceMonthly (lấy tất cả)
var listInsMonthlyObj = new List<object> { orgIds, monthYear, null, 1, int.MaxValue - 1 };
if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
{
listInsMonthlyObj.Add("id");
}
var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status);
#endregion
var lstProfileInsMonthly = lstProfileInsuranceMonthlyInDb.Where(s => s.MonthYear.HasValue
&& (s.MonthYear.Value.Year == dateFrom.Year || s.MonthYear.Value.Year == dateTo.Year)
&& (s.MonthYear.Value.Month == dateFrom.Month || s.MonthYear.Value.Month == dateTo.Month)).ToList();
//tự sinh ra kỳ
var cutOfDuration = "Kỳ " + periodIns + " - " + monthYear.ToString(ConstantFormat.HRM_Format_MonthYear);
#region Get D02
List<object> listD02Obj = new List<object>();
listD02Obj.Add(null);
listD02Obj.Add(dtMonthYear);
listD02Obj.Add(cutOfDuration);
var d02 = GetData<Ins_ReportD02>(listD02Obj, ConstantSql.hrm_ins_sp_get_D02, userLogin, ref status).FirstOrDefault();
#endregion
if (d02 != null)
{
List<object> listD02ItemObj = new List<object>();
listD02ItemObj.Add(d02.ID);
listD02ItemObj.Add(codeEmp);
var d02Item = GetData<Ins_ReportD02ItemEntity>(listD02ItemObj, ConstantSql.hrm_ins_sp_get_D02ItemByD02ID, userLogin, ref status);
if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any())
{
socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
if (socialInsPlaceIDs.Any())
{
d02Item = d02Item.Where(p => socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList();
}
}
var monthYear1 = new DateTime(monthYear.Year, monthYear.Month, 1);
var isHonda = true;
foreach (var insReportD02Item in d02Item)
{
var profileMonthlys = lstProfileInsMonthly.Where(p => p.ProfileID == insReportD02Item.ProfileID && p.MonthYear == monthYear1).FirstOrDefault();
var d02Status = insReportD02Item.Status;
if (profileMonthlys != null)
{
#region Honda
if (isHonda)
{
if (insReportD02Item.Status == TypeInsuranceD02TS.E_TANG_TS.ToString() || insReportD02Item.Status == TypeInsuranceD02TS.E_TANG_LEAVE_14WORKINGDAYS.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_TANG_BENH.ToString() || insReportD02Item.Status == TypeInsuranceD02TS.E_TANG_BHYT.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_TANG_LEAVE_14WORKINGDAYS.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_TANG_LD_CHANGE_INSPLACE.ToString())
{
d02Status = TypeInsuranceD02TS.E_TANG_LD.ToString();
}
if (insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_TS_QUIT.ToString() || insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_TS.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_BENH.ToString() || insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_LD_BHYT.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_LD_NOT_BHYT.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_LEAVE_PREMONTH_14WORKINGDAYS.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_LEAVE_14WORKINGDAYS.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_QUIT_SUSPENSE.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_PREGNANT_14WORKINGDAYS.ToString()
|| insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_LD_CHANGE_INSPLACE.ToString())
{
d02Status = TypeInsuranceD02TS.E_GIAM_LD.ToString();
}
if (insReportD02Item.Status == TypeInsuranceD02TS.E_GIAM_LUONG_CHANGEJOBNAME.ToString())
{
d02Status = TypeInsuranceD02TS.E_GIAM_LUONG.ToString();
}
if (insReportD02Item.Status == TypeInsuranceD02TS.E_TANG_LUONG_CHANGEJOBNAME.ToString())
{
d02Status = TypeInsuranceD02TS.E_TANG_LUONG.ToString();
}
}
#endregion
var insuranceReportD02 = new Ins_InsuranceReportD02Entity
{
Comment = insReportD02Item.Comment,
ProfileID = insReportD02Item.ProfileID,
Status = d02Status,
Status1 = insReportD02Item.Type,
STT = insReportD02Item.ItemOrder.HasValue ? insReportD02Item.ItemOrder.Value.ToString() : null,
ItemOrder = insReportD02Item.ItemOrder ?? 0,
RateBHXH = insReportD02Item.RateSocialIns,
RateBHYT = insReportD02Item.RateHealthIns,
RateBHTN = insReportD02Item.RateUnEmpIns,
NewBasicSalary = insReportD02Item.NewBasicSalary,
NotCardHealth = insReportD02Item.NotCardHealth.HasValue ? insReportD02Item.NotCardHealth.Value.ToString() : "false",
ProfileName = insReportD02Item.ProfileName,
CodeEmp = insReportD02Item.CodeEmp,
SocialInsNo = insReportD02Item.SocialInsNo,
JobName = insReportD02Item.JobName,
Allowance1 = profileMonthlys.Allowance1,
Allowance2 = profileMonthlys.Allowance2,
Allowance3 = profileMonthlys.Allowance3,
Allowance4 = profileMonthlys.Allowance4,
AllowanceAdditional = profileMonthlys.AllowanceAdditional,
FromMonth = insReportD02Item.MonthTo,
ToMonth = insReportD02Item.MonthTo,
PayBackID = insReportD02Item.PayBackID,
IsPayBack = insReportD02Item.IsPayBack
};
lstInsuranceReportD02.Add(insuranceReportD02);
}
}
}
}
lstInsuranceReportD02 = lstInsuranceReportD02.OrderBy(p => p.Status).ToList();
var tang = TypeInsuranceD02TS.E_TANG.ToString();
var giam = TypeInsuranceD02TS.E_GIAM.ToString();
List<Ins_InsuranceReportD02Entity> lst = new List<Ins_InsuranceReportD02Entity>();
var statuses = new List<string> {
TypeInsuranceD02TS.E_TANG.ToString(),
TypeInsuranceD02TS.E_TANG_LD.ToString(),
TypeInsuranceD02TS.E_TANG_BHTN.ToString(),
TypeInsuranceD02TS.E_GIAM.ToString(),
TypeInsuranceD02TS.E_GIAM_LD.ToString(),
TypeInsuranceD02TS.E_TANG_LUONG.ToString(),
TypeInsuranceD02TS.E_GIAM_LUONG.ToString(),
TypeInsuranceD02TS.E_Dieu_Chinh.ToString(),
TypeInsuranceD02TS.E_DieuChinhTang.ToString(),
TypeInsuranceD02TS.E_DieuChinhGiam.ToString(),
};
foreach (var item in statuses)
{
var lstInsuranceByStatus = lstInsuranceReportD02.Where(p => p.Status == item).OrderBy(p => p.ItemOrder).ToList();
if (lstInsuranceByStatus.Any())
{
Ins_InsuranceReportD02Entity ins_InsuranceReportD02 = new Ins_InsuranceReportD02Entity
{
ProfileName = GetDescription(item),
CodeEmp = GetOrderStatus(item)
};
lstInsuranceByStatus = lstInsuranceByStatus.OrderBy(p => p.Comment).ToList();
int stt = 1;
foreach (var insuranceStatus in lstInsuranceByStatus)
{
insuranceStatus.STT = stt.ToString();
stt++;
}
lstInsuranceByStatus.Insert(0, ins_InsuranceReportD02);
lst.AddRange(lstInsuranceByStatus);
}
else
{
Ins_InsuranceReportD02Entity ins_InsuranceReportItemD02 = new Ins_InsuranceReportD02Entity
{
ProfileName = GetDescription(item),
CodeEmp = GetOrderStatus(item)
};
lst.Add(ins_InsuranceReportItemD02);
}
}
#region filter searchNoteType
if (searchNoteType != "All")
{
var statusSearch = searchNoteType;
searchNoteType = GetDescription(searchNoteType);
if (statusSearch == TypeInsuranceD02TS.E_DieuChinhTang.ToString() ||
statusSearch == TypeInsuranceD02TS.E_DieuChinhGiam.ToString() ||
statusSearch == TypeInsuranceD02TS.E_Dieu_Chinh.ToString()
)
{
lst = lst.Where(p => (p.Comment == null || p.Comment == "") || p.Status == statusSearch).ToList();
}
else
{
lst = lst.Where(p => (p.Comment == null || p.Comment == "") || p.Comment == searchNoteType).ToList();
}
}
else
{
var lstSearch = new List<string>();
if (searchStatus == TypeInsuranceD02TS.E_TANG.ToString())
{
lstSearch = new List<string> {
GetDescription(TypeInsuranceD02TS.E_TANG_LD.ToString()),
GetDescription(TypeInsuranceD02TS.E_TANG_TS.ToString()),
//GetDescription(TypeInsuranceD02TS.E_TANG_BENH.ToString()),
//GetDescription(TypeInsuranceD02TS.E_TANG_BHYT.ToString()),
GetDescription(TypeInsuranceD02TS.E_TANG_BHTN.ToString()),
GetDescription(TypeInsuranceD02TS.E_TANG_LEAVE_14WORKINGDAYS.ToString() )
};
}
if (searchStatus == TypeInsuranceD02TS.E_GIAM.ToString())
{
lstSearch = new List<string> {
GetDescription(TypeInsuranceD02TS.E_GIAM_LD_BHYT.ToString()),
GetDescription(TypeInsuranceD02TS.E_GIAM_QUIT_SUSPENSE.ToString()),
GetDescription(TypeInsuranceD02TS.E_GIAM_TS.ToString()),
GetDescription(TypeInsuranceD02TS.E_GIAM_LEAVE_14WORKINGDAYS.ToString()),
GetDescription(TypeInsuranceD02TS.E_GIAM_LEAVE_PREMONTH_14WORKINGDAYS.ToString()),
//GetDescription(TypeInsuranceD02TS.E_GIAM_BENH.ToString())
};
}
if (searchStatus == TypeInsuranceD02TS.E_GIAM_LUONG.ToString())
{
lstSearch = new List<string> {
GetDescription( TypeInsuranceD02TS.E_GIAM_LUONG.ToString()),
GetDescription(TypeInsuranceD02TS.E_GIAM_LUONG_CHANGEJOBNAME.ToString())
};
}
if (searchStatus == TypeInsuranceD02TS.E_TANG_LUONG.ToString())
{
lstSearch = new List<string> {
GetDescription(TypeInsuranceD02TS.E_TANG_LUONG.ToString()),
GetDescription(TypeInsuranceD02TS.E_TANG_LUONG_CHANGEJOBNAME.ToString())
};
}
if (searchStatus == TypeInsuranceD02TS.E_CHANGEJOBNAME.ToString())
{
lstSearch = new List<string> {
GetDescription(TypeInsuranceD02TS.E_CHANGEJOBNAME.ToString()),
};
}
if (searchStatus == TypeInsuranceD02TS.E_Dieu_Chinh.ToString())
{
lstSearch = new List<string> {
TypeInsuranceD02TS.E_Dieu_Chinh.ToString(),
GetDescription(TypeInsuranceD02TS.E_DieuChinhGiam.ToString()),
GetDescription(TypeInsuranceD02TS.E_DieuChinhTang.ToString()),
};
}
if (lstSearch.Any())
{
lst = lst.Where(p => (p.Comment == null || p.Comment == "") || lstSearch.Contains(p.Comment)).ToList();
}
}
#endregion
return lst;
}
/// <summary> BC NV huong tro cap nang nhoc, doc hai, nguy hiem </summary>
/// <param name="DateStart"></param>
/// <param name="DateEnd"></param>
/// <param name="orgIds"></param>
/// <returns></returns>
public DataTable BC_EmpHardJob(DateTime DateStart, DateTime DateEnd, string orgIds)
{
using (var context = new VnrHrmDataContext())
{
var status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoCatExchangeRate = new Cat_ExchangeRateRepository(unitOfWork);
var repoCatValueEntity = new Cat_ValueEntityRepository(unitOfWork);
var repoSal_InsuranceSalary = new Sal_InsuranceSalaryRepository(unitOfWork);
var repoIns_ProfileInsuranceMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
var repoIns_InsuranceRecord = new Ins_InsuranceRecordRepository(unitOfWork);
#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 = unitOfWork.CreateQueryable<Cat_OrgStructure>(Guid.Empty, p => p.IsDelete == null && lstOrgOrderNumber.Contains(p.OrderNumber)).Select(p => p.ID).ToList();
var lstProfiles = 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
DataTable dt = CreateReportEmpHardJobScheme(DateStart, DateEnd);
List<Guid> lstProfileIDTotal = lstProfiles.Select(m => m.ID).ToList();
foreach (var lstProfileID in lstProfileIDTotal.Chunk(500))
{
//Lấy ds trích nộp BH (được hưởng tiền HDTJob) theo NV vào khoảng thời gian truyền vào
var lstProfileMonthly = unitOfWork.CreateQueryable<Ins_ProfileInsuranceMonthly>(Guid.Empty,
m => m.IsDelete == null && m.ProfileID != null
&& lstProfileID.Contains(m.ProfileID.Value)
&& m.MonthYear >= DateStart
&& m.MonthYear <= DateEnd
&& m.MonthYear != null
&& (m.AmountHDTIns != null && m.AmountHDTIns.Value > 0)
)
.Select(m => new CustomProfileMonthlyEntity()
{
ProfileID = m.ProfileID,
SalaryInsurance = m.SalaryInsurance,
IsSocialInsurance = m.IsSocialInsurance,
MonthYear = m.MonthYear.Value,
MoneyHDTJob = m.AmountHDTIns,
IsPregnant = m.IsPregnant,
IsDecreaseWorkingDays = m.IsDecreaseWorkingDays
})
.ToList();
int stt = 0;
foreach (var profileID in lstProfileID)
{
stt++;
var hasValue = false;
DataRow dr = null;
for (DateTime datecheck = DateStart; datecheck <= DateEnd; datecheck = datecheck.AddMonths(1))
{
if (dr == null)
{
dr = dt.NewRow();
}
#region Kiểm tra xem có đóng HDT Job ko
var Ins = lstProfileMonthly.Where(p => p.MonthYear == datecheck && p.ProfileID == profileID).FirstOrDefault();
if (Ins != null)
{
if (Ins.MoneyHDTJob != null && Ins.MoneyHDTJob > 0)
{
if (Ins.IsPregnant != null && Ins.IsPregnant.Value)
{
dr[datecheck.ToString(MONTHYEAR)] = ConstantDisplay.HRM_Ins_ReportEmpHardJob_PregnantLeave.TranslateString();
hasValue = true;
}
else if (Ins.IsDecreaseWorkingDays != null && Ins.IsDecreaseWorkingDays.Value)
{
dr[datecheck.ToString(MONTHYEAR)] = ConstantDisplay.HRM_Ins_ReportEmpHardJob_DecreaseWorkingDays.TranslateString();
hasValue = true;
}
else
{
dr[datecheck.ToString(MONTHYEAR)] = String.Format("{0:#,###,###.##}", Ins.MoneyHDTJob);
hasValue = true;
}
}
}
#endregion
}
if (hasValue)
{
var profile = lstProfiles.Where(m => m.ID == profileID).FirstOrDefault();
if (profile != null)
{
dr[Ins_ReportEmpHardJoblEntity.FieldNames.CodeEmp] = profile.CodeEmp;
dr[Ins_ReportEmpHardJoblEntity.FieldNames.ProfileName] = profile.ProfileName;
}
dt.Rows.Add(dr);
}
}
}
return dt.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);
}
}
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);
}
public List<Ins_InsuranceReportD03Entity> LoadDataD03(bool? all, bool? increase, bool? descrease, DateTime? dtMonthYear, string orgIds, string searchNoteType, string searchStatus, string codeEmp, List<Guid> workPlaceIDs, string userLogin)
{
var monthYear = dtMonthYear ?? DateTime.Now;
DateTime dateFrom = monthYear.AddMonths(-1);
dateFrom = new DateTime(dateFrom.Year, dateFrom.Month, InsuranceServices.PeriodInsuranceDayPreMonthDefault);
DateTime dateTo = new DateTime(monthYear.Year, monthYear.Month, InsuranceServices.PeriodInsuranceDayCurrentMonthDefault);
var status = string.Empty;
var allCheck = all ?? false;
var inCreaseCheck = increase ?? false;
var descreaseCheck = descrease ?? false;
//lay ds Ins_ProfileInsuranceMonthly
//List<object> listInsMonthlyObj = new List<object>();
//listInsMonthlyObj.Add(orgs);
//listInsMonthlyObj.Add(monthYear);
//var profileInsMonthlys = GetData<Hre_ProfileEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, ref status);
Hashtable htbData = new Hashtable();
var d02Reports = new List<Ins_InsuranceReportD02Entity>();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var profileInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var orgStructureRepo = new Cat_OrgStructureRepository(unitOfWork);
var insInsuranceRecord = new Ins_InsuranceRecordRepository(unitOfWork);
var hre_ContractRepo = new Hre_ContractRepository(unitOfWork);
var cat_ContractTypeRepo = new Cat_ContractTypeRepository(unitOfWork);
var att_LeaveDayRepo = new Att_LeavedayRepository(unitOfWork);
#region Lấy DS Ins_ProfileInsuranceMonthly (lấy tất cả)
var listInsMonthlyObj = new List<object> { orgIds, null, null, 1, int.MaxValue - 1 };
if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
{
listInsMonthlyObj.Add("id");
}
var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status);
#endregion
//get profiles trong Ins_ProfileInsuranceMonthly theo thang kiểm tra
var lstProfile = lstProfileInsuranceMonthlyInDb.Where(s => s.MonthYear.HasValue
&& (s.MonthYear.Value.Year == dateFrom.Year || s.MonthYear.Value.Year == dateTo.Year)
&& (s.MonthYear.Value.Month == dateFrom.Month || s.MonthYear.Value.Month == dateTo.Month))
.ToList().Translate<Ins_ProfileInsuranceMonthly>();
#region lay ins_ProfileInsuranceMonthly de tinh thai san 1 nam lien ke
var dtPreDateCheck = dateFrom.AddYears(-1);
//Lấy Ds Ins_ProfileInsuranceMonthly 12 tháng trước so với tháng kiểm tra (de tinh thai san 1 nam lien ke)
var lstProfilePregnant = lstProfileInsuranceMonthlyInDb.Where(s => s.MonthYear.HasValue
&& (dtPreDateCheck <= s.MonthYear.Value && s.MonthYear <= dateFrom)).ToList();
#endregion
//var lstProfile = profileInsMonthly.FindBy(s => s.IsDelete == null && s.MonthYear.HasValue
// && (s.MonthYear.Value.Year == dateFrom.Year || s.MonthYear.Value.Year == dateTo.Year)
// && (s.MonthYear.Value.Month == dateFrom.Month || s.MonthYear.Value.Month == dateTo.Month)).ToList();
//get InsuranceRecord (Chứng từ bảo hiểm)
//var lstInsuranceRecord = insInsuranceRecord.GetAll().Where(p => p.IsDelete == null).ToList();// lay tat ca chứng từ bảo hiểm (sau nay sửa lai sau)
#region Lay ds Phong Ban
var orgs = GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).ToList();
List<Cat_OrgStructureEntity> orgStructures = orgs.Translate<Cat_OrgStructureEntity>();
// var orgs = orgStructureRepo.GetAll();
// var orgIds = orgs.Select(p => p.ID).ToList();
#endregion
#region Lấy profile theo phong ban
List<object> listObj = new List<object>();
listObj.Add(orgIds);
listObj.Add(string.Empty);
listObj.Add(string.Empty);
var profilebyOrgs = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status);
if (profilebyOrgs != null)
{
profilebyOrgs = profilebyOrgs.Where(p => p.DateHire != null
&& (p.DateQuit == null || p.DateQuit > dateFrom)
&& p.DateHire < monthYear).ToList();
}
if (!string.IsNullOrEmpty(codeEmp))
{
profilebyOrgs = profilebyOrgs.Where(p => p.CodeEmp == codeEmp).ToList();
}
var profileIds = profilebyOrgs.Select(p => p.ID).ToList();
#endregion
#region lay ds hop dong đã ký so với tháng chọn (Loại HD có đóng BH)
//var contractTypeObjs = cat_ContractTypeRepo.FindBy(p => p.IsDelete == null).ToList();
////Ds loại HĐ đóng BHXH
//var contractSocialTypeIDObjs = contractTypeObjs.Where(p => p.IsSocialInsurance.HasValue && p.IsSocialInsurance.Value).ToList();
////Ds loại HĐ đóng BHTN
//var contractUnEmpTypeIDObjs = contractTypeObjs.Where(p => p.IsUnEmployInsurance.HasValue && p.IsUnEmployInsurance.Value).ToList();
//var contracts = new List<Hre_Contract>();
//var contractSocials = new List<Hre_Contract>();
//var contractUnEmps = new List<Hre_Contract>();
//if (contractSocialTypeIDObjs.Any())
//{
// var contractSocialTypeIds = contractSocialTypeIDObjs.Select(p => p.ID).ToList();
// var contractUnEmpTypeIds = contractUnEmpTypeIDObjs.Select(p => p.ID).ToList();
// //Ds Hop Đồng của cac NV
// contracts = hre_ContractRepo.FindBy(p => p.IsDelete == null && profileIds.Contains(p.ProfileID)).OrderBy(p => p.DateStart).ToList();
// //Ds Hop đồng thuộc loại đóng BHXH
// contractSocials = contracts.Where(p => contractSocialTypeIds.Contains(p.ContractTypeID)).OrderBy(p => p.DateStart).ToList();
// //Ds Hop đồng thuộc loại đóng BHTN
// contractUnEmps = contracts.Where(p => contractUnEmpTypeIds.Contains(p.ContractTypeID)).OrderBy(p => p.DateStart).ToList();
//}
#endregion
#region lấy Leaveday
//string E_APPROVED = LeaveDayStatus.E_APPROVED.ToString();
//string E_PREGNANCY_SUCKLE = InsuranceRecordType.E_PREGNANCY_SUCKLE.ToString();
//var lstLeaveday = att_LeaveDayRepo.FindBy(m => m.IsDelete == null && m.Status == E_APPROVED && m.DateStart <= dateTo && m.DateEnd >= dateFrom && m.Cat_LeaveDayType != null
// && m.Cat_LeaveDayType.InsuranceType == E_PREGNANCY_SUCKLE && profileIds.Contains(m.ProfileID)).ToList();
#endregion
//List<object> salInsuranceSalaryParams = new List<object>();
//salInsuranceSalaryParams.Add(null);
//salInsuranceSalaryParams.Add(null);
//salInsuranceSalaryParams.Add(orgIds);
//salInsuranceSalaryParams.Add(null);
//salInsuranceSalaryParams.Add(null);
//salInsuranceSalaryParams.Add(null);
//salInsuranceSalaryParams.Add(null);
//salInsuranceSalaryParams.Add(1);
//salInsuranceSalaryParams.Add(50000);
//var lstInsuranceSalaryByProfile = GetData<Sal_InsuranceSalary>(salInsuranceSalaryParams, ConstantSql.hrm_sal_sp_get_InsuranceSalary, ref status)
// .Where(p => p.DateEffect <= dateTo && profileIds.Contains(p.ProfileID ?? Guid.Empty) && p.InsuranceAmount.HasValue).ToList();
if (workPlaceIDs != null && workPlaceIDs.Any())
{
workPlaceIDs = workPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
if (workPlaceIDs.Any())
{
lstProfile = lstProfile.Where(p => workPlaceIDs != null && workPlaceIDs.Count > 0 && workPlaceIDs.Contains(p.WorkPlaceID ?? Guid.Empty)).ToList();
}
}
// workPlaceID = workPlaceID.Where(p => p != null).ToList();
foreach (var profile in profilebyOrgs)
{
//lay các chung tu bao hiem cua nv
// List<Ins_InsuranceRecord> lstInsRecordProfile = lstInsuranceRecord.Where(rec => rec.ProfileID == profile.ID).ToList();
// var lstleavePregByProfile = lstLeaveday.Where(rec => rec.ProfileID == profile.ID).ToList();
// var lstInsRecordPregByProfile = lstInsRecordProfile.Where(m => m.InsuranceType == E_PREGNANCY_SUCKLE).ToList();
var profileMonthlys = lstProfile.Where(p => p.ProfileID == profile.ID).OrderBy(p => p.MonthYear).ToList();
var inFirst = profileMonthlys.FirstOrDefault(p => p.MonthYear.HasValue && p.MonthYear.Value.Month == dateFrom.Month);
var inNow = profileMonthlys.LastOrDefault(p => p.MonthYear.HasValue && p.MonthYear.Value.Month == dateTo.Month);
if (inFirst == null)
{
inFirst = new Ins_ProfileInsuranceMonthly
{
IsHealthInsurance = false,
IsSocialInsurance = false,
IsUnEmpInsurance = false
};
}
if (inNow != null)
{
profile.JobName = inNow.JobName;
#region set Phu cap
if (inNow != null)
{
profile.Allowance1 = inNow.Allowance1;
profile.Allowance2 = inNow.Allowance2;
profile.Allowance3 = inNow.Allowance3;
profile.Allowance4 = inNow.Allowance4;
}
#endregion
#region tang
if (IsIncreaseInsurance(inFirst, inNow) && (inCreaseCheck == true || allCheck == true))
{
#region Tăng BHYT
if ((!inFirst.IsHealthInsurance.HasValue || (inFirst.IsHealthInsurance.HasValue &&
!inFirst.IsHealthInsurance.Value))
&& inNow.IsHealthInsurance.HasValue && inNow.IsHealthInsurance.Value)
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_TANG.ToString(), TypeInsuranceD02TS.E_TANG_BHYT.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "I");
}
#endregion
}
#endregion
#region giam
if (IsDecreaseInsurance(inFirst, inNow) && (descreaseCheck == true || allCheck == true))
{
#region giam
if ((inFirst.IsHealthInsurance.HasValue && inFirst.IsHealthInsurance.Value)
&& !inNow.IsHealthInsurance.HasValue || (inNow.IsHealthInsurance.HasValue && !inNow.IsHealthInsurance.Value))
{
SetHashtableData(htbData, profile, ref d02Reports, TypeInsuranceD02TS.E_GIAM.ToString(), TypeInsuranceD02TS.E_GIAM_BHYT.ToString(), inFirst.SalaryInsurance, inNow.SalaryInsurance, monthYear, orgStructures, "II");
}
#endregion
}
#endregion
}
}
}
//duyet hashtable => danh sach nv theo loai bao hiem (tangLd,tangBHXH,.....)
var result = new List<Ins_InsuranceReportD02Entity>();
var tang = TypeInsuranceD02TS.E_TANG.ToString();
var giam = TypeInsuranceD02TS.E_GIAM.ToString();
var tangLD = TypeInsuranceD02TS.E_GIAM_LD.ToString();
foreach (DictionaryEntry entry in htbData)
{
var lst = new List<Ins_InsuranceReportD02Entity>();
lst = (List<Ins_InsuranceReportD02Entity>)entry.Value;
// lst = lst.OrderBy(p => p.STT).ToList();
var d02Entity = lst.FirstOrDefault(p => p.OrderGroup != null);
var orderGroup = string.Empty;
if (d02Entity != null)
{
orderGroup = d02Entity.OrderGroup;
}
var proName = entry.Key.ToString();
if (!string.IsNullOrEmpty(proName))
{
proName = GetDescription(proName);
}
var title = new Ins_InsuranceReportD02Entity()
{
ProfileID = Guid.Empty,
ProfileName = proName,
OrderGroup = orderGroup,
CodeEmp = orderGroup,
BOLD = true.ToString()
};
lst.Insert(0, title);
if (lst.Any(p => p.Status1 == tang) && !result.Any(p => p.Status == tangLD) && !result.Any(p => p.ProfileName == "Tăng"))
{
lst.Insert(0, new Ins_InsuranceReportD02Entity { ProfileID = Guid.Empty, ProfileName = "Tăng", CodeEmp = "I", OrderGroup = "I" });
}
if (lst.Any(p => p.Status1 == giam) && !result.Any(p => p.ProfileName == "Giảm"))
{
lst.Insert(0, new Ins_InsuranceReportD02Entity { ProfileID = Guid.Empty, ProfileName = "Giảm", CodeEmp = "II", OrderGroup = "II" });
}
//if (lst.Any(p => p.Status == TypeInsuranceD02TS.E_GIAM_LD.ToString()) && !result.Any(p => p.Status1 == giam) && !result.Any(p => p.ProfileName == "Giảm Lao Động"))
//{
// lst.Insert(0, new Ins_InsuranceReportD02Entity { ProfileName = "Giảm Lao Động", CodeEmp = "II", OrderGroup = "II" });
//}
result.AddRange(lst);
}
result = result.Where(p => p.ProfileName != "Tăng Lao Động").ToList();
result = result.Where(p => p.ProfileName != "Giảm BHYT").ToList();
//var result = LoadData(all, increase, descrease, dtMonthYear, orgIds, searchNoteType, searchStatus, codeEmp, workPlaceIDs);
//var E_TANG_BHYT = TypeInsuranceD02TS.E_TANG_BHYT.ToString();
//var E_GIAM_BHYT = TypeInsuranceD02TS.E_GIAM_BHYT.ToString();
//result = result.Where(p => p.Status == E_TANG_BHYT || p.Status == E_GIAM_BHYT).ToList();
var listD03 = new List<Ins_InsuranceReportD03Entity>();
foreach (var item in result)
{
var d03 = item.Copy<Ins_InsuranceReportD03Entity>();
listD03.Add(d03);
}
return listD03.OrderBy(p => p.OrderGroup).ToList();
}
/// <summary> Hàm tính toán và save lại bảo hiểm của từng người theo tháng </summary>
/// <param name="orgs"></param>
/// <param name="monthCheck">Vd: 01/12/2014</param>
/// <param name="periodInsurance">Loại tạm thời , Loại Chính Thức</param>
public void AnalyzeAndSaveInsuranceByMonth(string orgs, DateTime monthCheck, string periodInsurance, string codeEmp, List<Guid> socialInsPlaceIDs,string userLogin)
{
/*
* Goal(tính toán và save lại bảo hiểm của từng NV theo tháng và phòng ban (Tạo mới hoặc update Ins_ProfileInsuranceMonthly))
* Steps :
* - Step1 : Lấy DS NV theo phòng ban và theo tháng được chọn
* - Step2 : xử lý tính ra tháng bắt đầu join đóng BHXH,BHYT,BHTN
* - Step3 : Set giá trị cho IsLeaveNonWorkday trong profile (cho nghỉ >=14 ngày)
* - Step4 : kiểm tra xem có đóng BHXH,BHYT,BHTN
* - Step5 : Tính lương và mức đóng của BHXH,BHYT,BHTN
* - Step6 : Tạo mới hoặc cập nhật dữ liệu vào bảng Ins_ProfileInsuranceMonthly
*/
//ngày 1 tháng N
var beginMonth = new DateTime(monthCheck.Year, monthCheck.Month, 1);
//ngày 31 tháng N
DateTime endMonth = beginMonth.AddMonths(1).AddMinutes(-1);
var step = 200;
monthCheck = new DateTime(monthCheck.Year, monthCheck.Month, 1);
var lstProfile = new List<Hre_ProfileEntity>();
using (var context = new VnrHrmDataContext())
{
#region Khai báo
var status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var repoSalInsuranceSalary = new Sal_InsuranceSalaryRepository(unitOfWork);
var repoAtt_WorkDay = new Att_WorkDayRepository(unitOfWork);
var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoAtt_Roster = new Att_RosterRepository(unitOfWork);
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var repoIns_InsuranceRecord = new Ins_InsuranceRecordRepository(unitOfWork);
var profileIDByCodeEmp = Guid.Empty;
#endregion
#region phân tích theo mã NV (từ đk tìm kiếm)
if (!string.IsNullOrEmpty(codeEmp))
{
var profileByCodeEmp = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, p => p.IsDelete == null && p.CodeEmp == codeEmp).FirstOrDefault();
if (profileByCodeEmp != null)
{
profileIDByCodeEmp = profileByCodeEmp.ID;
}
else
{
return;
}
}
#endregion
//lay ds ngay nghi
var dayOffMonthChecks = unitOfWork.CreateQueryable<Cat_DayOff>(Guid.Empty, m => m.DateOff.Year == monthCheck.Year
&& m.DateOff.Month == monthCheck.Month).ToList();
#region lay hop dong theo phòng ban
//List<object> contractPara = new List<object>();
//contractPara.AddRange(new object[16]);
//contractPara[0] = null;
//contractPara[1] = null;
//contractPara[2] = orgs;
//contractPara[3] = null;
//contractPara[4] = null;
//contractPara[5] = null;
//contractPara[6] = null;
//contractPara[7] = null;
//contractPara[8] = null;
//contractPara[9] = null;
//contractPara[10] = null;
//contractPara[11] = null;
//contractPara[12] = null;
//contractPara[13] = null;
//contractPara[14] = 1;
//contractPara[15] = int.MaxValue;
//if (profileIDByCodeEmp != Guid.Empty)
//{
// contractPara[1] = codeEmp;
// contractPara[2] = null;
//}
//var contracts = GetData<Hre_ContractEntity>(contractPara, ConstantSql.hrm_hr_sp_get_Contract, ref status).Translate<Hre_Contract>().ToList();
var contracts = new List<Hre_Contract>();
#endregion
#region Get Profiles by orgs
var listObj = new List<object> { orgs, string.Empty, string.Empty };
if (profileIDByCodeEmp != Guid.Empty)
{
listObj[0] = null;
listObj[2] = codeEmp;
}
if (!string.IsNullOrEmpty(codeEmp))
{
lstProfile = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, m => m.CodeEmp == codeEmp).ToList().Translate<Hre_ProfileEntity>();
}
else if (orgs != null)
{
var lstOrderNumberORG = orgs.Split(',').Select(s => int.Parse(s)).Distinct().ToList();
var orgObj = unitOfWork.CreateQueryable<Cat_OrgStructure>(Guid.Empty).Select(p => p.ID).ToList();
var orgStructureIDs = unitOfWork.CreateQueryable<Cat_OrgStructure>(Guid.Empty, m => lstOrderNumberORG.Contains(m.OrderNumber)).Select(m => m.ID).ToList();
lstProfile = new List<Hre_ProfileEntity>();
foreach (var orgIds in orgStructureIDs.Chunk(step))
{
var profileByOrgs = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, m => m.OrgStructureID.HasValue && orgIds.Contains(m.OrgStructureID.Value)).ToList().Translate<Hre_ProfileEntity>();
lstProfile.AddRange(profileByOrgs);
}
}
#endregion
if (socialInsPlaceIDs != null)
{
socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any())
{
lstProfile = lstProfile.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList();
}
}
#region lay ds inusuranceSalary by profile list
//ds profileId theo phong ban
List<Guid> lstProfileIDs = lstProfile.Select(m => m.ID).ToList();
var lstInsuranceSalaryByProfile = new List<Sal_InsuranceSalary>();
foreach (var profileIds in lstProfileIDs.Chunk(step))
{
var lstInsuranceSalaryByProfileTemp = unitOfWork.CreateQueryable<Sal_InsuranceSalary>(Guid.Empty, m => m.InsuranceAmount != null && m.ProfileID != null
&& m.DateEffect <= endMonth && profileIds.Contains(m.ProfileID.Value)).ToList();
lstInsuranceSalaryByProfile.AddRange(lstInsuranceSalaryByProfileTemp);
var contractTemp = unitOfWork.CreateQueryable<Hre_Contract>(Guid.Empty, m => profileIds.Contains(m.ProfileID)).ToList();
contracts.AddRange(contractTemp);
}
if (lstInsuranceSalaryByProfile.Any())
{
lstProfileIDs = lstInsuranceSalaryByProfile.Select(p => p.ProfileID ?? Guid.Empty).ToList();
lstProfile = lstProfile.Where(p => lstProfileIDs.Contains(p.ID)).ToList();
}
#endregion
#region ds NV đóng Bảo Hiểm theo phòng ban và tháng (Ins_ProfileInsuranceMonthly)
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_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly,userLogin, ref status).Translate<Ins_ProfileInsuranceMonthly>();
// lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(m => m.PaybackID == null ).ToList();
#endregion
#region Tháng bắt đầu tham gia bảo hiểm
SetMonthJoinInsuranceByProfile(lstProfile, lstInsuranceSalaryByProfile, endMonth);
#endregion
#region Kiem tra co dong bao hiem ko?
//Kiểm tra xem co đóng BHXH,BHYT,BHTN
SetIsHaveInsurnceByProfileByMonth(lstProfile, monthCheck, contracts,userLogin);
if (lstProfile.Any())
{
lstProfileIDs = lstProfile.Select(p => p.ID).ToList();
}
#endregion
#region Nghỉ 14 ngày
List<Hre_ProfileEntity> lstProfileEntities = new List<Hre_ProfileEntity>();
List<string> lstLeaveInsuranceType = String.Join(",", leaveDayInsuranceTypes).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
if (HasLeaveGreater14day)
{
string E_APPROVE = HDTJobStatus.E_APPROVE.ToString();
var lstProfilesIdChunks = lstProfileIDs;
var leaveDayTypeIDs = unitOfWork.CreateQueryable<Cat_LeaveDayType>(Guid.Empty, m => m.PaidRate == 0 || lstLeaveInsuranceType.Contains(m.InsuranceType)).Select(m => m.ID).ToList();
foreach (var profileIDs in lstProfilesIdChunks.Chunk(step))
{
var lstProfileEntitiesTemp = lstProfile.Where(p => profileIDs.Contains(p.ID)).ToList();
#region workday từ 1->31 tháng N theo các NV (không có quẹt thẻ)
var lstWorkday = unitOfWork.CreateQueryable<Att_Workday>(Guid.Empty, m => m.InTime1 == null && m.OutTime1 == null && m.WorkDate >= beginMonth
&& m.WorkDate <= endMonth && profileIDs.Contains(m.ProfileID)).Select(m => new CustomWorkDayEntity() { ProfileID = m.ProfileID, InTime1 = m.InTime1, OutTime1 = m.OutTime1, WorkDate = m.WorkDate }).ToList();
#endregion
#region Leaveday (ngày nghỉ trong khoảng 1->31 tháng N) theo NV
string app = LeaveDayStatus.E_APPROVED.ToString();
var lstLeaveDay = unitOfWork.CreateQueryable<Att_LeaveDay>(Guid.Empty, m => m.Status == app && m.DateStart <= endMonth && m.DateEnd >= beginMonth && profileIDs.Contains(m.ProfileID))
.Select(m => new CustomLeavedayEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart, DateEnd = m.DateEnd, LeaveDayTypeID = m.LeaveDayTypeID }).ToList();
#endregion
//paidrate = 0 :ko tra luong
var lstLeavedayTypeRateZero = unitOfWork.CreateQueryable<Cat_LeaveDayType>(Guid.Empty, p => p.PaidRate == 0).ToList();
#region lấy ds chứng từ bảo hiểm
var insRecords = unitOfWork.CreateQueryable<Ins_InsuranceRecord>(Guid.Empty, m =>
m.DateStart != null && m.DateEnd != null
&& m.DateStart <= endMonth && m.DateEnd >= beginMonth && profileIDs.Contains(m.ProfileID))
.Select(m => new CustomInsuranceRecordEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart.Value, DateEnd = m.DateEnd.Value, InsuranceType = m.InsuranceType }).ToList();
#endregion
var approved = RosterStatus.E_APPROVED.ToString();
var rosters = unitOfWork.CreateQueryable<Att_Roster>(Guid.Empty, p => p.Status == approved
&& (p.DateStart <= endMonth && p.DateEnd >= beginMonth)
&& profileIDs.Contains(p.ProfileID))
.Select(m => new CustomRosterEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart, DateEnd = m.DateEnd, MonShiftID = m.MonShiftID, TueShiftID = m.TueShiftID, WedShiftID = m.WedShiftID, ThuShiftID = m.ThuShiftID, FriShiftID = m.FriShiftID, SatShiftID = m.SatShiftID, SunShiftID = m.SunShift2ID })
.ToList();
//kt co nghỉ hơn 14 ngay ko?
SetLeaveNonWorkdayByProfile(lstProfileEntitiesTemp, beginMonth, endMonth, lstWorkday, lstLeaveDay, lstLeavedayTypeRateZero, insRecords, rosters, dayOffMonthChecks);
//Hàm do Triet.Mai Hỗ trợ. tính ra số ngày không làm HDTJOb
// "SC, SU, SM, SD, DP, D, M, AL, TSC,TAS"
lstLeaveDay = lstLeaveDay.Where(m => m.LeaveDayTypeID != null && leaveDayTypeIDs.Contains(m.LeaveDayTypeID.Value)).ToList();
var lstHDTJob = unitOfWork.CreateQueryable<Hre_HDTJob>(Guid.Empty, m => m.Status == E_APPROVE && m.DateFrom <= endMonth && m.DateTo >= beginMonth
&& m.ProfileID.HasValue && profileIDs.Contains(m.ProfileID.Value)).ToList();
GetHDTJobDayByProfile(lstProfileEntitiesTemp, leaveDayTypeIDs, monthCheck, rosters, lstLeaveDay, lstHDTJob);
lstProfileEntities.AddRange(lstProfileEntitiesTemp);
}
}
lstProfile = lstProfileEntities;
#endregion
#region tính lương , mức đóng BHXH,BHYT,BHTN
SetMoneyInsuranceByProfileByMonth(lstProfile, lstInsuranceSalaryByProfile, endMonth, orgs, contracts,userLogin);
#endregion
#region Save Change
var lstProfileInsuranceMonthlyAddNew = new List<Ins_ProfileInsuranceMonthly>();
var lstProfileInsuranceMonthlyModify = new List<Ins_ProfileInsuranceMonthly>();
foreach (var profile in lstProfile)
{
if (lstProfileInsuranceMonthlyInDb.Where(m => m.ProfileID == profile.ID && m.PaybackID != null).FirstOrDefault() != null)
{
continue;
}
Ins_ProfileInsuranceMonthly insuranceByProfile = lstProfileInsuranceMonthlyInDb
.FirstOrDefault(m => m.ProfileID == profile.ID && m.PaybackID == null);
if (insuranceByProfile != null) //Update cái dữ liệu cũ
{
insuranceByProfile.WorkPlaceID = profile.WorkPlaceID;
insuranceByProfile.SocialInsPlaceID = profile.SocialInsPlaceID;
insuranceByProfile.IsSocialInsurance = profile.IsHaveInsSocial;
insuranceByProfile.IsHealthInsurance = profile.IsHaveInsHealth;
insuranceByProfile.IsUnEmpInsurance = profile.IsHaveInsUnEmp;
insuranceByProfile.IsPregnant = profile.IsPregnant;
insuranceByProfile.MoneySocialInsurance = profile.MoneyInsuranceSocial;
insuranceByProfile.MoneyHealthInsurance = profile.MoneyInsuranceHealth;
insuranceByProfile.MoneyUnEmpInsurance = profile.MoneyInsuranceUnEmp;
insuranceByProfile.SalaryInsurance = profile.MoneyInsuranceTotal;
insuranceByProfile.SalaryHealthInsurance = profile.MoneyInsuranceHealthTotal;
insuranceByProfile.SalaryUnEmpInsurance = profile.MoneyInsuranceUnEmpTotal;
insuranceByProfile.IsDecreaseWorkingDays = profile.IsDecreaseWorkingDays;
insuranceByProfile.JobName = profile.JobName;
insuranceByProfile.AmountHDTIns = profile.AmountHDTIns;
insuranceByProfile.HDTGroupCode = profile.HDTJobGroupCode;
insuranceByProfile.TypeGetData = periodInsurance;
#region Tung.Ly set các tỉ lệ và mức đóng
insuranceByProfile.Allowance1 = profile.Allowance1;
insuranceByProfile.Allowance2 = profile.Allowance2;
insuranceByProfile.Allowance3 = profile.Allowance3;
insuranceByProfile.Allowance4 = profile.Allowance4;
insuranceByProfile.AmountChargeIns = profile.AmountChargeIns;
insuranceByProfile.SocialInsComRate = profile.SocialInsComRate;
insuranceByProfile.SocialInsComAmount = profile.SocialInsComAmount;
insuranceByProfile.SocialInsEmpRate = profile.SocialInsEmpRate;
insuranceByProfile.SocialInsEmpAmount = profile.SocialInsEmpAmount;
insuranceByProfile.HealthInsComRate = profile.HealthInsComRate;
insuranceByProfile.HealthInsComAmount = profile.HealthInsComAmount;
insuranceByProfile.HealthInsEmpRate = profile.HealthInsEmpRate;
insuranceByProfile.HealthInsEmpAmount = profile.HealthInsEmpAmount;
insuranceByProfile.UnemployComRate = profile.UnemployComRate;
insuranceByProfile.UnemployComAmount = profile.UnemployComAmount;
insuranceByProfile.UnemployEmpRate = profile.UnemployEmpRate;
insuranceByProfile.UnemployEmpAmount = profile.UnemployEmpAmount;
insuranceByProfile.IsPayback = false;
insuranceByProfile.MonthYearEffect = monthCheck;
#endregion
lstProfileInsuranceMonthlyModify.Add(insuranceByProfile);//add vao list (Modify)
}
else //Tạo mới
{
var newInsurance = new Ins_ProfileInsuranceMonthly
{
ProfileID = profile.ID,
WorkPlaceID = profile.WorkPlaceID,
SocialInsPlaceID = profile.SocialInsPlaceID,
IsSocialInsurance = profile.IsHaveInsSocial,
IsHealthInsurance = profile.IsHaveInsHealth,
IsUnEmpInsurance = profile.IsHaveInsUnEmp,
IsPregnant = profile.IsPregnant,
MoneySocialInsurance = profile.MoneyInsuranceSocial,
MoneyHealthInsurance = profile.MoneyInsuranceHealth,
MoneyUnEmpInsurance = profile.MoneyInsuranceUnEmp,
IsDecreaseWorkingDays = profile.IsDecreaseWorkingDays,
SalaryInsurance = profile.MoneyInsuranceTotal,
AmountHDTIns = profile.AmountHDTIns,
HDTGroupCode = profile.HDTJobGroupCode,
SalaryHealthInsurance = profile.MoneyInsuranceHealthTotal,
SalaryUnEmpInsurance = profile.MoneyInsuranceUnEmpTotal,
TypeGetData = periodInsurance,
MonthYear = monthCheck,
IsPayback = false,
MonthYearEffect = monthCheck,
#region Tung.Ly set các tỉ lệ và mức đóng
Allowance1 = profile.Allowance1,
Allowance2 = profile.Allowance2,
Allowance3 = profile.Allowance3,
Allowance4 = profile.Allowance4,
AmountChargeIns = profile.AmountChargeIns,
SocialInsComRate = profile.SocialInsComRate,
SocialInsComAmount = profile.SocialInsComAmount,
SocialInsEmpRate = profile.SocialInsEmpRate,
SocialInsEmpAmount = profile.SocialInsEmpAmount,
HealthInsComRate = profile.HealthInsComRate,
HealthInsComAmount = profile.HealthInsComAmount,
HealthInsEmpRate = profile.HealthInsEmpRate,
HealthInsEmpAmount = profile.HealthInsEmpAmount,
UnemployComRate = profile.UnemployComRate,
UnemployComAmount = profile.UnemployComAmount,
UnemployEmpRate = profile.UnemployEmpRate,
UnemployEmpAmount = profile.UnemployEmpAmount,
JobName = profile.JobName,
#endregion
};
lstProfileInsuranceMonthlyAddNew.Add(newInsurance);//add new
}
}
if (lstProfileInsuranceMonthlyAddNew.Any())
{
repoInsMonthly.Add(lstProfileInsuranceMonthlyAddNew);
}
if (lstProfileInsuranceMonthlyModify.Any())
{
repoInsMonthly.Edit(lstProfileInsuranceMonthlyModify);
}
repoInsMonthly.SaveChanges();
#endregion
}
}
