/// <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; }
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 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(); }
public List<Ins_InsuranceReportD02TailEntity> ReportD02Tail(string orgs, DateTime monthCheck, string userLogin) { string status = string.Empty; DateTime preMonth = monthCheck.AddMonths(-1); var d02Tails = new List<Ins_InsuranceReportD02TailEntity>(); #region Lấy DS Ins_ProfileInsuranceMonthly tháng trước (so với tháng kiểm tra) var listInsMonthlyObjPre = new List<object> { orgs, preMonth, null, 1, int.MaxValue - 1 }; if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString()) { listInsMonthlyObjPre.Add("id"); } var lstProfileInsuranceMonthlyInDbPre = GetData<Ins_ProfileInsuranceMonthly>(listInsMonthlyObjPre, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status).Translate<Ins_ProfileInsuranceMonthly>(); #endregion #region Lấy DS Ins_ProfileInsuranceMonthly tháng N (so với tháng kiểm tra) var listInsMonthlyObj = new List<object> { orgs, monthCheck, null, 1, int.MaxValue - 1 }; if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString()) { listInsMonthlyObjPre.Add("id"); } var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthly>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status).Translate<Ins_ProfileInsuranceMonthly>(); #endregion #region test var E_TANG_LD = TypeInsuranceD02TS.E_TANG_LD.ToString(); //giam var E_GIAM_LD = TypeInsuranceD02TS.E_GIAM_LD.ToString(); var E_TANG_BHYT = TypeInsuranceD02TS.E_TANG_BHYT.ToString(); var E_GIAM_BHYT = TypeInsuranceD02TS.E_GIAM_BHYT.ToString(); var E_GIAM_LD_BHYT = TypeInsuranceD02TS.E_GIAM_LD_BHYT.ToString(); var E_GIAM_LD_BHYT_KOKIPTHOI = TypeInsuranceD02TS.E_GIAM_LD_BHYT_KOKIPTHOI.ToString(); var E_GIAM_LD_NOT_BHYT = TypeInsuranceD02TS.E_GIAM_LD_NOT_BHYT.ToString(); var E_TANG_LUONG = TypeInsuranceD02TS.E_TANG_LUONG.ToString(); var E_TANG_LUONG_CHANGEJOBNAME = TypeInsuranceD02TS.E_TANG_LUONG_CHANGEJOBNAME.ToString(); var E_GIAM_LUONG = TypeInsuranceD02TS.E_GIAM_LUONG.ToString(); var E_GIAM_LUONG_CHANGEJOBNAME = TypeInsuranceD02TS.E_GIAM_LUONG_CHANGEJOBNAME.ToString(); //giam BHTN var E_GIAM_BHTN = TypeInsuranceD02TS.E_GIAM_BHTN.ToString(); var E_TANG_BHTN = TypeInsuranceD02TS.E_TANG_BHTN.ToString(); int tangBHXH = 0; int giamBHXH = 0; int tangBHYT = 0; int giamBHYT = 0; int tangBHTN = 0; int giamBHTN = 0; double tangLuongBHXH = 0; double giamLuongBHXH = 0; double tangLuongBHYT = 0; double giamLuongBHYT = 0; double tangLuongBHTN = 0; double giamLuongBHTN = 0; #endregion #region duyet profiles var profileIds = lstProfileInsuranceMonthlyInDb.Select(p => p.ProfileID).ToList(); foreach (var profileId in profileIds) { var profileMonthlys = lstProfileInsuranceMonthlyInDb.Where(p => p.ProfileID == profileId).OrderBy(p => p.MonthYear).ToList(); var profileMonthlyPrevious = lstProfileInsuranceMonthlyInDbPre.Where(p => p.ProfileID == profileId).OrderBy(p => p.MonthYear).ToList(); var inFirst = profileMonthlyPrevious.FirstOrDefault(); var inNow = profileMonthlys.FirstOrDefault(); if (inFirst == null) { inFirst = new Ins_ProfileInsuranceMonthly { IsHealthInsurance = false, IsSocialInsurance = false, IsUnEmpInsurance = false }; } if (inNow != null) { #region tang if (inFirst != null && inNow != null && (inFirst.IsSocialInsurance.HasValue && !inFirst.IsSocialInsurance.Value) && (inNow.IsSocialInsurance.HasValue && inNow.IsSocialInsurance.Value)) { tangBHXH++; tangLuongBHXH += inNow.SalaryInsurance ?? 0; } if (inFirst != null && inNow != null && (inFirst.IsHealthInsurance.HasValue && !inFirst.IsHealthInsurance.Value) && (inNow.IsHealthInsurance.HasValue && inNow.IsHealthInsurance.Value)) { tangBHYT++; tangLuongBHYT += inNow.SalaryInsurance ?? 0; } if (inFirst != null && inNow != null && (inFirst.IsUnEmpInsurance.HasValue && !inFirst.IsUnEmpInsurance.Value) && (inNow.IsUnEmpInsurance.HasValue && inNow.IsUnEmpInsurance.Value)) { tangBHTN++; tangLuongBHTN += inNow.SalaryInsurance ?? 0; } #endregion #region Giam if (inFirst != null && inNow != null && (inFirst.IsSocialInsurance.HasValue && inFirst.IsSocialInsurance.Value) && (inNow.IsSocialInsurance.HasValue && !inNow.IsSocialInsurance.Value)) { giamBHXH++; giamLuongBHXH += inNow.SalaryInsurance ?? 0; } if (inFirst != null && inNow != null && (inFirst.IsHealthInsurance.HasValue && inFirst.IsHealthInsurance.Value) && (inNow.IsHealthInsurance.HasValue && !inNow.IsHealthInsurance.Value)) { giamBHYT++; giamLuongBHYT += inNow.SalaryInsurance ?? 0; } #endregion } } #endregion #region xử lý Ins_InsuranceReportD02TailEntity row1 = new Ins_InsuranceReportD02TailEntity(); Ins_InsuranceReportD02TailEntity row2 = new Ins_InsuranceReportD02TailEntity(); #region Row1 //thang N-1 var prePeriodInsHealthAmount = lstProfileInsuranceMonthlyInDbPre.Where(p => p.IsHealthInsurance.HasValue && p.IsHealthInsurance.Value && p.IsDelete == null).Count(); var prePeriodInsSocialAmount = lstProfileInsuranceMonthlyInDbPre.Where(p => p.IsSocialInsurance.HasValue && p.IsSocialInsurance.Value && p.IsDelete == null).Count(); var prePeriodInsUnEmpAmount = lstProfileInsuranceMonthlyInDbPre.Where(p => p.IsUnEmpInsurance.HasValue && p.IsUnEmpInsurance.Value && p.IsDelete == null).Count(); row1 = new Ins_InsuranceReportD02TailEntity { STT = 1, Name = " Số lao động", InsSocialIncreaseAmount = tangBHXH, InsSocialDecreaseAmount = giamBHXH, InsHealthIncreaseAmount = tangBHYT, InsHealthDecreaseAmount = giamBHYT, InsUnEmpIncreaseAmount = tangBHTN, InsUnEmpDecreaseAmount = giamBHTN, PrePeriodInsHealthAmount = prePeriodInsHealthAmount, PrePeriodInsSocialAmount = prePeriodInsSocialAmount, PrePeriodInsUnEmpAmount = prePeriodInsUnEmpAmount, }; d02Tails.Add(row1); #endregion #region row2 //Thang N-1 var salaryInsurancePre = lstProfileInsuranceMonthlyInDbPre.Where(p => p.IsSocialInsurance.HasValue && p.IsSocialInsurance.Value && p.SalaryInsurance.HasValue && p.IsDelete == null).Sum(p => p.SalaryInsurance ?? 0); var salaryHealthInsurancePre = lstProfileInsuranceMonthlyInDbPre.Where(p => p.IsHealthInsurance.HasValue && p.IsHealthInsurance.Value && p.SalaryHealthInsurance.HasValue && p.IsDelete == null).Sum(p => p.SalaryHealthInsurance ?? 0); var salaryUnEmpInsurancePre = lstProfileInsuranceMonthlyInDbPre.Where(p => p.IsUnEmpInsurance.HasValue && p.IsUnEmpInsurance.Value && p.SalaryUnEmpInsurance.HasValue && p.IsDelete == null).Sum(p => p.SalaryUnEmpInsurance ?? 0); row2 = new Ins_InsuranceReportD02TailEntity { STT = 2, Name = "Quỹ Lương", InsSocialIncreaseAmount = tangLuongBHXH, InsSocialDecreaseAmount = giamLuongBHXH, InsHealthIncreaseAmount = tangLuongBHYT, InsHealthDecreaseAmount = giamLuongBHYT, InsUnEmpIncreaseAmount = tangLuongBHTN, InsUnEmpDecreaseAmount = giamLuongBHTN, PrePeriodInsHealthAmount = salaryHealthInsurancePre, PrePeriodInsSocialAmount = salaryInsurancePre, PrePeriodInsUnEmpAmount = salaryUnEmpInsurancePre, }; #endregion d02Tails.Add(row2); #endregion return d02Tails; }
public static Boolean IsDecreaseInsurance(Ins_ProfileInsuranceMonthly insPreMonth, Ins_ProfileInsuranceMonthly insNow) { if (insPreMonth == null) { return false; } if (insPreMonth != null && insNow != null && insNow.IsSocialInsurance != null && (insPreMonth.IsSocialInsurance != null && (insPreMonth.IsSocialInsurance.Value && !insNow.IsSocialInsurance.Value))) { return true; } return false; }
public static Boolean IsIncreaseInsurance(Ins_ProfileInsuranceMonthly insPreMonth , Ins_ProfileInsuranceMonthly insNow) { if (insPreMonth == null && insNow.IsSocialInsurance.HasValue && insNow.IsSocialInsurance.Value) { return true; } if (insPreMonth != null && insNow != null && insNow.IsSocialInsurance != null && (insPreMonth.IsSocialInsurance != null && (!insPreMonth.IsSocialInsurance.Value && insNow.IsSocialInsurance.Value))) { return true; } //Co them truong hop tang BHTN if (insPreMonth != null && insNow != null && insNow.IsUnEmpInsurance != null && (insPreMonth.IsUnEmpInsurance != null && (!insPreMonth.IsUnEmpInsurance.Value && insNow.IsUnEmpInsurance.Value))) { return true; } return false; }
/// <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 } }