Exemplo n.º 1
0
        public List<Ins_ReportJobNameMonthlyEntity> ReportJobNameMonthly(string orgs, DateTime monthCheck, string codeEmp, List<Guid> socialInsPlaceIDs, string userLogin)
        {
            string status = string.Empty;
            var preMonth = new DateTime(monthCheck.Year, monthCheck.AddMonths(-1).Month, InsuranceServices.PeriodInsuranceDayPreMonthDefault);
            using (var context = new VnrHrmDataContext())
            {
                var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
                var repoCat_SalaryClass = new Cat_SalaryClassRepository(unitOfWork);

                var salaryClasses = repoCat_SalaryClass.FindBy(p => p.IsDelete == null).ToList();


                var orgstructures = GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).Translate<Cat_OrgStructureEntity>().ToList();

                var listObj = new List<object> { orgs, string.Empty, string.Empty };
                var lstProfile = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status).Translate<Hre_ProfileEntity>();

                #region lich su bao hiem thang chọn
                var listInsMonthlyObj = new List<object> { orgs, monthCheck, null, 1, int.MaxValue - 1 };
                if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
                {
                    listInsMonthlyObj.Add("id");
                }
                var lstProfileInsuranceMonthlyInDb = GetData<Ins_ReportJobNameMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status);
                #endregion

                #region Lịch sử Bảo Hiểm tháng trước
                var listInsPreMonthlyObj = new List<object> { orgs, monthCheck.AddMonths(-1), null, 1, int.MaxValue - 1 };
                if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
                {
                    listInsPreMonthlyObj.Add("id");
                }
                var lstProfileInsurancePreMonthlyInDb = GetData<Ins_ReportJobNameMonthlyEntity>(listInsPreMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly, userLogin, ref status);
                #endregion


                #region đk lọc
                if (!string.IsNullOrEmpty(codeEmp))
                {
                    lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => p.CodeEmp == codeEmp).ToList();
                }

                if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any())
                {
                    socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
                    if (socialInsPlaceIDs.Any())
                    {
                        lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList();
                    }
                }
                #endregion

                //lịch sử NV đóng bảo hiểm
                //  lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => p.IsDecreaseWorkingDays == null || p.IsPregnant == null).ToList();
                List<Ins_ReportJobNameMonthlyEntity> lstJobNameMonthlies = new List<Ins_ReportJobNameMonthlyEntity>();
                foreach (var item in lstProfileInsuranceMonthlyInDb)
                {
                    var profile = lstProfile.Where(p => p.ID == item.ProfileID).FirstOrDefault();

                    if (item.IsDecreaseWorkingDays.HasValue || item.IsPregnant.HasValue)
                    {
                        var inFirst = lstProfileInsurancePreMonthlyInDb.FirstOrDefault(p => p.ProfileID == item.ProfileID && p.MonthYear.HasValue && p.MonthYear.Value.Month == monthCheck.AddMonths(-1).Month);
                        var inNow = lstProfileInsuranceMonthlyInDb.FirstOrDefault(p => p.ProfileID == item.ProfileID && p.MonthYear.HasValue && p.MonthYear.Value.Month == monthCheck.Month);
                        #region Thiết lập
                        var inFirstIsPregnant = false;
                        var inFirstIs14days = false;
                        var inNowIsPregnant = false;
                        var inNowIs14days = false;
                        if (inNow != null)
                        {
                            inNowIsPregnant = inNow.IsPregnant ?? false;
                            inNowIs14days = inNow.IsDecreaseWorkingDays ?? false;
                        }
                        if (inFirst != null)
                        {
                            inFirstIsPregnant = inFirst.IsPregnant ?? false;
                            inFirstIs14days = inFirst.IsDecreaseWorkingDays ?? false;
                        }
                        if (inNowIsPregnant && inFirstIsPregnant && (!profile.DateQuit.HasValue))
                        {
                            //tháng trước nghỉ thai sản
                            continue;
                        }
                        if (inNowIs14days && inFirstIs14days && (!profile.DateQuit.HasValue ) )
                        {
                            //tháng trước nghỉ 14 ngày
                            continue;
                        }
                    }
                        #endregion


                    if (profile != null && profile.DateQuit.HasValue && profile.DateQuit.Value < preMonth)
                    {
                        //bắt đầu nghỉ việc từ tháng trước
                        continue;
                    }


                    var salaryClassObj = salaryClasses.FirstOrDefault(p => profile != null && profile.SalaryClassID.HasValue && p.ID == profile.SalaryClassID);
                    if (salaryClassObj != null)
                    {
                        //Mã Lương
                        item.Rank = salaryClassObj.SalaryClassName;
                    }

                    if (profile != null)
                    {
                        item.DateHire = profile.DateHire;
                        item.CostCentreName = profile.CostCentreName;
                        item.CostCentreID = profile.CostCentreID;
                        
                        //  item.Location = GetCodeOrgStructure(orgstructures, profile.OrgStructureID ?? Guid.Empty);
                        item.E_DEPARTMENT = profile.E_DEPARTMENT;
                        item.E_DIVISION = profile.E_DIVISION;
                        item.E_SECTION = profile.E_SECTION;
                        item.E_TEAM = profile.E_TEAM;
                        item.E_UNIT = profile.E_UNIT;
                    }
                    lstJobNameMonthlies.Add(item);
                }
                return lstJobNameMonthlies;
            }

        }
Exemplo n.º 2
0
        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);
        }