public DataTable ReportUnusualPay(DateTime monthYear, List<Guid> listProfileIDs, Guid? payrollGroupID, Boolean isIncludeQuitEmp, string codeEmp, string orderNumber, string UserLogin) { try { using (var context = new VnrHrmDataContext()) { #region " Load dữ liệu" string status = string.Empty; var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); //kỳ lương (hiện tại mặc định đầu tháng -> cuối tháng) DateTime from = new DateTime(monthYear.Year, monthYear.Month, 1); DateTime to = new DateTime(monthYear.Year, monthYear.Month, DateTime.DaysInMonth(monthYear.Year, monthYear.Month)); //Ds tất cả phòng ban var orgServices = new Cat_OrgStructureServices(); var lstObjOrg = new List<object>(); // var reposOrgStructure = new CustomBaseRepository<Cat_OrgStructure>(unitOfWork); var listOrgAll = orgServices.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList(); var lstObjOrgNumber = new List<object>(); lstObjOrgNumber.Add(orderNumber); var lstOrgID = orgServices.GetData<Cat_OrgStructureEntity>(lstObjOrgNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => s.ID).ToList(); //Ds cửa Hàng var shopServices = new Cat_ShopServices(); var lstObjShop = new List<object>(); lstObjShop.Add(null); lstObjShop.Add(null); lstObjShop.Add(null); lstObjShop.Add(1); lstObjShop.Add(int.MaxValue - 1); var lstShop = shopServices.GetData<Cat_ShopEntity>(lstObjShop, ConstantSql.hrm_cat_sp_get_Shop, UserLogin, ref status).ToList(); //Ds tất cả loại PB var orgTypeServices = new Cat_OrgStructureTypeServices(); var lstObjOrgType = new List<object>(); lstObjOrgType.Add(string.Empty); lstObjOrgType.Add(string.Empty); lstObjOrgType.Add(1); lstObjOrgType.Add(int.MaxValue - 1); // var reposOrgStructureType = new CustomBaseRepository<Cat_OrgStructureType>(unitOfWork); var listOrgType = orgTypeServices.GetData<Cat_OrgStructureType>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, UserLogin, ref status).ToList(); //Ds nhân viên var reposProfile = new CustomBaseRepository<Hre_Profile>(unitOfWork); var profileServices = new Hre_ProfileServices(); var lstObjProfile = new List<object>(); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(string.Empty); lstObjProfile.Add(1); lstObjProfile.Add(int.MaxValue - 1); //lọc theo phòng ban //var listProfile = reposProfile.GetAll().Where(hr => hr.IsDelete == null // && hr.OrgStructureID.HasValue // && listOrgIDs.Contains(hr.OrgStructureID.Value)).ToList(); var listProfile = reposProfile.GetAll().Where(s => s.IsDelete != true).ToList(); if (listProfileIDs != null) { listProfile = listProfile.Where(s => listProfileIDs.Contains(s.ID)).ToList(); } //lọc theo nhóm lương // listProfile = listProfile.Where(hr => hr.PayrollGroupID.HasValue && listPrGroupIDs.Contains(hr.PayrollGroupID.Value)).ToList(); List<Guid> listProfileID = listProfile.Select(s => s.ID).Distinct().ToList(); //lọc nhân viên nghỉ việc if (!isIncludeQuitEmp) { listProfile = listProfile.Where(pro => pro.DateHire <= to && (pro.DateQuit == null || pro.DateQuit.Value > from)).ToList(); } if (payrollGroupID != null && payrollGroupID != Guid.Empty) { listProfile = listProfile.Where(s => s.PayrollGroupID == payrollGroupID).ToList(); } //lọc theo tên nhân viên if (!string.IsNullOrEmpty(codeEmp)) { listProfile = listProfile.Where(s => s.CodeEmp == codeEmp).ToList(); } //ds chế độ lương var saleGradeServices = new Sal_GradeServices(); var lstObjSalGrade = new List<object>(); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(1); lstObjSalGrade.Add(int.MaxValue - 1); var reposSalGrade = new CustomBaseRepository<Sal_Grade>(unitOfWork); var lstGradeAll = reposSalGrade.GetAll().Where(s => s.IsDelete != true).ToList(); // var lstGradeAll = saleGradeServices.GetData<Sal_Grade>(lstObjSalGrade, ConstantSql.hrm_sal_sp_get_Sal_Grade, UserLogin,ref status).ToList(); //ds thông tin lương var salInfoServices = new Sal_SalaryInformationServices(); var lstObjSalInfo = new List<object>(); lstObjSalInfo.Add(string.Empty); lstObjSalInfo.Add(string.Empty); lstObjSalInfo.Add(string.Empty); lstObjSalInfo.Add(string.Empty); lstObjSalInfo.Add(string.Empty); lstObjSalInfo.Add(string.Empty); lstObjSalInfo.Add(1); lstObjSalInfo.Add(int.MaxValue - 1); var salInfoServices1 = new Sal_SalaryInformationServices(); var lstSalaryInformation = salInfoServices.GetData<Sal_SalaryInformationEntity>(lstObjSalInfo, ConstantSql.hrm_sal_sp_get_Sal_SalaryInformation, UserLogin, ref status).OrderByDescending(sal => sal.DateUpdate).ToList().Translate<Sal_SalaryInformation>(); //var reposSalaryInformation = new CustomBaseRepository<Sal_SalaryInformation>(unitOfWork); //var lstSalaryInformation = salInfoServices.GetData<Sal_SalaryInformation>(lstObjSalInfo, ConstantSql.hrm_sal_sp_get_Sal_SalaryInformation,UserLogin,ref status).OrderByDescending(sal => sal.DateUpdate).ToList(); // var lstSalaryInformation = new Sal_SalaryInformation(); //ds hợp đồng var contractServices = new Hre_ContractServices(); var lstObjContract = new List<object>(); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(string.Empty); lstObjContract.Add(1); lstObjContract.Add(int.MaxValue - 1); var reposHreContract = new CustomBaseRepository<Hre_Contract>(unitOfWork); // var lstHreContractAll = contractServices.GetData<Hre_ContractEntity>(lstObjContract, ConstantSql.hrm_hr_sp_get_Contract, UserLogin,ref status).ToList().Translate<Hre_Contract>(); var lstHreContractAll = reposHreContract.GetAll().Where(s => s.IsDelete != true).ToList(); //ds mức lươn64864866498*-*/-*/7896543564555555978g var salaryRankServices = new Cat_SalaryRankServices(); var lstObjSalaryRank = new List<object>(); lstObjSalaryRank.Add(string.Empty); lstObjSalaryRank.Add(null); lstObjSalaryRank.Add(1); lstObjSalaryRank.Add(int.MaxValue - 1); var reposSalaryRank = new CustomBaseRepository<Cat_SalaryRank>(unitOfWork); var lstSalaryRankAll = salaryRankServices.GetData<Cat_SalaryRankEntity>(lstObjSalaryRank, ConstantSql.hrm_cat_sp_get_SalaryRank, UserLogin, ref status).ToList().Translate<Cat_SalaryRank>(); var revenueProfileServices = new Sal_RevenueForProfileServices(); var lstObjRevenueForProfile = new List<object>(); lstObjRevenueForProfile.Add(null); lstObjRevenueForProfile.Add(1); lstObjRevenueForProfile.Add(int.MaxValue - 1); var lstRevenueForProfile = revenueProfileServices.GetData<Sal_RevenueForProfileEntity>(lstObjRevenueForProfile, ConstantSql.hrm_sal_sp_get_RevenueForProfile, UserLogin, ref status).ToList(); //Ds phần tử lương var elementServices = new Cat_ElementServices(); var methodPayroll = MethodPayroll.E_ADNVANCE_PAYMENT.ToString(); var lstObjElement = new List<object>(); lstObjElement.Add(""); lstObjElement.Add(""); lstObjElement.Add(""); lstObjElement.Add(1); lstObjElement.Add(int.MaxValue - 1); var reposPayrollElement = new CustomBaseRepository<Cat_Element>(unitOfWork); //var reposPayrollElement = new CustomBaseRepository<Cat_ElementMultiEntity>(unitOfWork); // var listPayrollElement = elementServices.GetData<Cat_ElementMultiEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element,UserLogin,ref status).ToList(); var listPayrollElement = reposPayrollElement.GetAll().Where(s => s.MethodPayroll == methodPayroll && s.IsDelete != true).ToList(); // ds Bảng Ứng Lương var repoUnusualPay = new CustomBaseRepository<Sal_UnusualPay>(unitOfWork); var lstUnusualPay = repoUnusualPay.GetAll().Where(s => s.IsDelete != true && s.MonthYear == monthYear && listProfileID.Contains(s.ProfileID)).ToList(); List<Guid> lstUnusualPayIDs = lstUnusualPay.Select(s => s.ID).ToList(); //Ds Bảng Ứng Lương Chi Tiết var repoUnusualPayItem = new CustomBaseRepository<Sal_UnusualPayItem>(unitOfWork); var lstUnusualPayItem = repoUnusualPayItem.GetAll().Where(s => s.IsDelete != true && lstUnusualPayIDs.Contains(s.UnusualPayID)).ToList(); // //ds bảng lương // var payrollTableServices = new Sal_PayrollTableServices(); // var lstObjPayrollTable = new List<object>(); // lstObjPayrollTable.Add(string.Empty); // lstObjPayrollTable.Add(string.Empty); // lstObjPayrollTable.Add(1); // lstObjPayrollTable.Add(int.MaxValue -1); // var reposSalPayrollTb = new CustomBaseRepository<Sal_PayrollTable>(unitOfWork); //// var lstSalPayrollTb = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin,ref status).Where(pr => pr.IsDelete == null && pr.MonthYear == monthYear && listProfileID.Contains(pr.ProfileID)).ToList(); // var lstSalPayrollTb = reposSalPayrollTb.GetAll().Where(pr => pr.IsDelete == null && pr.MonthYear == monthYear && listProfileID.Contains(pr.ProfileID)).ToList(); // List<Guid> listSalPayrollIDs = lstSalPayrollTb.Select(p => p.ID).ToList(); // //order theo pb // lstSalPayrollTb = lstSalPayrollTb.OrderBy(m => m.Hre_Profile.Cat_OrgStructure.OrderOrg != null ? m.Hre_Profile.Cat_OrgStructure.OrderOrg.Value : 0).ToList(); // //ds bảng lương chi tiết // var payrollTableItemServices = new Sal_PayrollTableItemServices(); // var lstObjPayrollTableItem = new List<object>(); // lstObjPayrollTableItem.Add(string.Empty); // lstObjPayrollTableItem.Add(string.Empty); // lstObjPayrollTableItem.Add(1); // lstObjPayrollTableItem.Add(int.MaxValue -1); // var reposSalPayrollTbItem = new CustomBaseRepository<Sal_PayrollTableItem>(unitOfWork); //// var lstSalPayrollTbItem = payrollTableItemServices.GetData<Sal_PayrollTableItem>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin,ref status).Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList(); // var lstSalPayrollTbItem = reposSalPayrollTbItem.GetAll().Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList(); //Tạo cột dữ liệu cho table DataTable tblData = GetSchemaReportUnusualPay(); #endregion #region " Không có dữ liệu Sal_PayrollTable" if (lstUnusualPay == null || lstUnusualPay.Count <= 0) { foreach (Hre_Profile profile in listProfile) { DataRow dr = tblData.NewRow(); dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.MonthYear] = monthYear; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeAttendance] = profile.CodeAttendance; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.LaborType] = profile.LaborType; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.Cat_EmployeeType != null ? profile.Cat_EmployeeType.EmployeeTypeName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor != null ? profile.Supervisor : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor != null ? profile.HighSupervisor : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.Cat_WorkPlace != null ? profile.Cat_WorkPlace.WorkPlaceName : string.Empty; // dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkingPlace] = profile.WorkingPlace != null ? profile.WorkingPlace : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax != null ? profile.CodeTax : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo != null ? profile.IDNo : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.CostCentreName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.Code : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.Cat_Position != null ? profile.Cat_Position.PositionName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.JobTitleName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email != null ? profile.Email : string.Empty; if (profile.DateHire != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateHire] = profile.DateHire.Value; if (profile.DateQuit != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit; if (profile.DateEndProbation != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo != null ? profile.SocialInsNo : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.Cat_PayrollGroup != null ? profile.Cat_PayrollGroup.PayrollGroupName : string.Empty; if (profile.OrgStructureID != null) { var orgName = GetParentOrg(listOrgAll, listOrgType, profile.OrgStructureID); if (orgName.Count < 3) { orgName.Insert(0, string.Empty); if (orgName.Count < 3) { orgName.Insert(0, string.Empty); } } dr[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2]; dr[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1]; dr[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0]; //var orgBranch = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_BRANCH, listOrgAll, listOrgType); //var orgOrg = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_DEPARTMENT, listOrgAll, listOrgType); //var orgTeam = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_TEAM, listOrgAll, listOrgType); //var orgSection = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_SECTION, listOrgAll, listOrgType); //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; } Hre_Contract hrcontract = lstHreContractAll.Where(hr => hr.ProfileID == profile.ID && hr.IsDelete == null).FirstOrDefault(); if (hrcontract != null && hrcontract.RankRateID != null) { Cat_SalaryRank salrank = lstSalaryRankAll.Where(rk => rk.ID == hrcontract.RankRateID.Value && rk.IsDelete == null).FirstOrDefault(); dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryRankName] = salrank != null ? salrank.SalaryRankName : string.Empty; } #region Insurance, Bank var salaryInformationList = lstSalaryInformation.Where(sal => sal.ProfileID == profile.ID).ToList(); if (salaryInformationList.Count > 0) { Sal_SalaryInformation bankSalary = salaryInformationList[0]; string accountTemp = string.Empty; string accountNo = string.Empty; string accountNo2 = string.Empty; string bankNameTemp = string.Empty; string bankCode = string.Empty; accountTemp = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo; accountNo = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo; accountNo2 = bankSalary.AccountNo2 == null ? string.Empty : bankSalary.AccountNo2; bankNameTemp = bankSalary.Cat_Bank == null ? string.Empty : bankSalary.Cat_Bank.BankName; bankCode = bankSalary.Cat_Bank == null ? string.Empty : bankSalary.Cat_Bank.BankCode; if (!string.IsNullOrEmpty(accountTemp)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankAccountNo] = accountTemp.Trim(); if (!string.IsNullOrEmpty(accountNo)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo] = accountNo.Trim(); if (!string.IsNullOrEmpty(accountNo2)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo2] = accountNo2.Trim(); if (!string.IsNullOrEmpty(bankCode)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankCode] = bankCode.Trim(); if (!string.IsNullOrEmpty(bankNameTemp)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankName] = bankNameTemp.Trim(); } #endregion tblData.Rows.Add(dr); } } #endregion #region " Có dữ liệu tính lương Sal_PayrollTable" else { foreach (Sal_UnusualPay payroll in lstUnusualPay) { Hre_Profile profile = listProfile.Where(s => s.ID == payroll.ProfileID).FirstOrDefault(); // var lstRevenueByProfileId = lstRevenueForProfile.Where(s => s.ProfileID == payroll.ProfileID).FirstOrDefault(); // var lstRankByProfileId = lstShop.Where(s => s.ID == profile.ShopID).FirstOrDefault(); List<Sal_UnusualPayItem> listItem = lstUnusualPayItem.Where(pi => pi.UnusualPayID == payroll.ID).ToList(); if (profile == null) continue; DataRow dr = tblData.NewRow(); dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.MonthYear] = monthYear; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeAttendance] = profile.CodeAttendance; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.LaborType] = profile.LaborType; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.Cat_EmployeeType != null ? profile.Cat_EmployeeType.EmployeeTypeName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor != null ? profile.Supervisor : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor != null ? profile.HighSupervisor : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.Cat_WorkPlace != null ? profile.Cat_WorkPlace.WorkPlaceName : string.Empty; // dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkingPlace] = profile.WorkingPlace != null ? profile.WorkingPlace : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax != null ? profile.CodeTax : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo != null ? profile.IDNo : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.CostCentreName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.Code : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.Cat_Position != null ? profile.Cat_Position.PositionName : string.Empty; dr["PositionCode"] = profile.Cat_Position != null ? profile.Cat_Position.Code : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.JobTitleName : string.Empty; dr["JobtitleCode"] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.Code : string.Empty; // dr["Rank"] = lstRankByProfileId != null ? lstRankByProfileId.Rank : string.Empty; // dr["Target"] = lstRevenueByProfileId != null ? lstRevenueByProfileId.Target : 0; // dr["Actual"] = lstRevenueByProfileId != null ? lstRevenueByProfileId.Actual : 0; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email != null ? profile.Email : string.Empty; if (profile.DateHire != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateHire] = profile.DateHire.Value; if (profile.DateQuit != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit; if (profile.DateEndProbation != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo != null ? profile.SocialInsNo : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.Cat_PayrollGroup != null ? profile.Cat_PayrollGroup.PayrollGroupName : string.Empty; if (profile.OrgStructureID != null) { var orgName = GetParentOrgNameForShiseido(listOrgAll, listOrgType, profile.OrgStructureID); if (orgName.Count < 5) { orgName.Insert(0, string.Empty); if (orgName.Count < 5) { orgName.Insert(0, string.Empty); } if (orgName.Count < 5) { orgName.Insert(0, string.Empty); } if (orgName.Count < 5) { orgName.Insert(0, string.Empty); } if (orgName.Count < 5) { orgName.Insert(0, string.Empty); } } dr["Công Ty"] = orgName[4]; dr["Chi Nhánh"] = orgName[3]; dr[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2]; dr[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1]; dr[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0]; var orgCode = GetParentOrgCodeForShiseido(listOrgAll, listOrgType, profile.OrgStructureID); if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); } if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); } if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); } if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); } } dr["Mã Công Ty"] = orgCode[4]; dr["Mã Chi Nhánh"] = orgCode[3]; dr["Mã Channel"] = orgCode[2]; dr["Mã Region"] = orgCode[1]; dr["Mã Area"] = orgCode[0]; //var orgBranch = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_BRANCH, listOrgAll, listOrgType); //var orgOrg = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_DEPARTMENT, listOrgAll, listOrgType); //var orgTeam = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_TEAM, listOrgAll, listOrgType); //var orgSection = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_SECTION, listOrgAll, listOrgType); //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; } Hre_Contract hrcontract = lstHreContractAll.Where(hr => hr.ProfileID == profile.ID && hr.IsDelete == null).FirstOrDefault(); if (hrcontract != null && hrcontract.RankRateID != null) { Cat_SalaryRank salrank = lstSalaryRankAll.Where(rk => rk.ID == hrcontract.RankRateID.Value && rk.IsDelete == null).FirstOrDefault(); if (salrank != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryRankName] = salrank.SalaryRankName; } #region Insurance, Bank List<Sal_SalaryInformation> salaryInformationList = lstSalaryInformation.Where(sal => sal.ProfileID == profile.ID).ToList(); if (salaryInformationList.Count > 0) { Sal_SalaryInformation bankSalary = salaryInformationList[0]; string accountTemp = string.Empty; string accountNo = string.Empty; string accountNo2 = string.Empty; string bankNameTemp = string.Empty; string bankCode = string.Empty; accountTemp = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo; accountNo = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo; accountNo2 = bankSalary.AccountNo2 == null ? string.Empty : bankSalary.AccountNo2; bankNameTemp = bankSalary.Cat_Bank == null ? string.Empty : bankSalary.Cat_Bank.BankName; bankCode = bankSalary.Cat_Bank == null ? string.Empty : bankSalary.Cat_Bank.BankCode; if (!string.IsNullOrEmpty(accountTemp)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankAccountNo] = accountTemp.Trim(); if (!string.IsNullOrEmpty(accountNo)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo] = accountNo.Trim(); if (!string.IsNullOrEmpty(accountNo2)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo2] = accountNo2.Trim(); if (!string.IsNullOrEmpty(bankCode)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankCode] = bankCode.Trim(); if (!string.IsNullOrEmpty(bankNameTemp)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankName] = bankNameTemp.Trim(); } #endregion #region sal grade // Sal_Grade grade = lstGradeAll.Where(gr => gr.IsDelete == null && gr.ProfileID == profile.ID // && gr.MonthStart < to).OrderByDescending(gr => gr.MonthStart).FirstOrDefault(); // if (grade != null) // { // Cat_GradePayroll gradepayroll = grade.Cat_GradePayroll1; // if (gradepayroll != null && gradepayroll.IsFormulaSalary == true) // { try { var lstSalPrItem = lstUnusualPayItem.Where(sal => sal.UnusualPayID == payroll.ID).ToList(); // var lstelement = listPayrollElement.Where(pr => pr.GradePayrollID == gradepayroll.ID).ToList(); if (listPayrollElement != null && listPayrollElement.Count > 0) { foreach (Cat_Element payrollElement in listPayrollElement) { if (payrollElement != null && !String.IsNullOrEmpty(payrollElement.ElementCode)) { //Add phần tử vào cột dữ liệu nếu chưa có if (!tblData.Columns.Contains(payrollElement.ElementCode)) { tblData.Columns.Add(payrollElement.ElementCode, typeof(Double)); } //Lấy value của phần tử if (tblData.Columns.Contains(payrollElement.ElementCode)) { Sal_UnusualPayItem item = lstSalPrItem.Where(it => it.Element == payrollElement.ElementCode).FirstOrDefault(); Double value = 0; if (item != null) { //if (item.ValueType == typeof(Double).Name) // { Double.TryParse(item.Amount.ToString(), out value); // } } dr[payrollElement.ElementCode] = value; } } } } } catch { } // } // } #endregion tblData.Rows.Add(dr); } } #endregion return tblData.ConfigTable(true); } } catch (Exception ex) { return new DataTable(); } }
/// <summary> /// BC bảng lương /// <param name="dateStartCutOffDuration">Ngày bắt đầu của kỳ lương</param> /// <param name="monthYear">Tháng</param> /// <param name="listOrgIDs">Ds phòng ban</param> /// <param name="listPrGroupIDs">Ds nhóm Lương</param> /// <param name="isIncludeQuitEmp">Bao gồm NV nghỉ việc</param> /// </sumary> public DataTable RefreshData(DateTime dateStartCutOffDuration, DateTime dateEndCutOffDuration, DateTime monthYear, List<Hre_ProfileEntity> listProfile, Guid? gradePayrollID, Boolean isIncludeQuitEmp, string codeEmp, string orderNumber, string Transfer, Guid[] workingPlaceID, Guid[] costcenterIds, string UserLogin) { try { using (var context = new VnrHrmDataContext()) { #region " Load dữ liệu" string status = string.Empty; var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); //kỳ lương (hiện tại mặc định đầu tháng -> cuối tháng) DateTime from = new DateTime(monthYear.Year, monthYear.Month, 1); DateTime to = new DateTime(monthYear.Year, monthYear.Month, DateTime.DaysInMonth(monthYear.Year, monthYear.Month)); //Ds tất cả phòng ban var orgServices = new Cat_OrgStructureServices(); var lstObjOrg = new List<object>(); // var reposOrgStructure = new CustomBaseRepository<Cat_OrgStructure>(unitOfWork); var listOrgAll = orgServices.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList(); var lstObjOrgNumber = new List<object>(); lstObjOrgNumber.Add(orderNumber); var lstOrgID = orgServices.GetData<Cat_OrgStructureEntity>(lstObjOrgNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => s.ID).ToList(); //Ds cửa Hàng var shopServices = new Cat_ShopServices(); var lstObjShop = new List<object>(); lstObjShop.Add(null); lstObjShop.Add(null); lstObjShop.Add(null); lstObjShop.Add(1); lstObjShop.Add(int.MaxValue - 1); var lstShop = shopServices.GetData<Cat_ShopEntity>(lstObjShop, ConstantSql.hrm_cat_sp_get_Shop, UserLogin, ref status).ToList(); //Ds tất cả loại PB var orgTypeServices = new Cat_OrgStructureTypeServices(); var lstObjOrgType = new List<object>(); lstObjOrgType.Add(string.Empty); lstObjOrgType.Add(string.Empty); lstObjOrgType.Add(1); lstObjOrgType.Add(int.MaxValue - 1); var listOrgType = orgTypeServices.GetData<Cat_OrgStructureType>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, UserLogin, ref status).ToList(); //Ds nhân viên var reposProfile = new CustomBaseRepository<Hre_Profile>(unitOfWork); var profileServices = new Hre_ProfileServices(); var lstObjProfile = new List<object>(); lstObjProfile.AddRange(new object[17]); lstObjProfile[15] = 1; lstObjProfile[16] = int.MaxValue - 1; var lstModel = new List<object>(); lstModel.AddRange(new object[10]); //lstModel[4] = CutOffDuration.DateStart; //lstModel[5] = CutOffDuration.DateEnd; lstModel[8] = 1; lstModel[9] = Int32.MaxValue - 1; List<Sal_HoldSalaryEntity> listHoldSalary = orgTypeServices.GetData<Sal_HoldSalaryEntity>(lstModel, ConstantSql.hrm_sal_sp_get_HoldSalary, UserLogin, ref status).Where(m => m.MonthSalary <= dateEndCutOffDuration && (m.MonthEndSalary == null || m.MonthEndSalary >= dateStartCutOffDuration)).ToList(); //bỏ những nhân viên đang bị hold lương listProfile = listProfile.Where(m => !listHoldSalary.Any(t => t.ProfileID == m.ID)).ToList(); bool isGroup = profileServices.IsGroupByOrgProfileQuit(); //lọc theo nhóm lương List<Guid> listProfileID = listProfile.Select(s => s.ID).Distinct().ToList(); //lọc nhân viên nghỉ việc if (!isIncludeQuitEmp) { listProfile = listProfile.Where(pro => pro.DateQuit == null || pro.DateQuit > from).ToList(); } if (workingPlaceID != null) { listProfile = listProfile.Where(pro => pro.WorkPlaceID != null && workingPlaceID.Contains(pro.WorkPlaceID.Value)).ToList(); } if (costcenterIds != null) { listProfile = listProfile.Where(pro => pro.CostCentreID != null && costcenterIds.Contains(pro.CostCentreID.Value)).ToList(); } //lọc theo tên nhân viên if (!string.IsNullOrEmpty(codeEmp)) { listProfile = listProfile.Where(s => s.CodeEmp != null && s.CodeEmp.Contains(codeEmp)).ToList(); } //ds chế độ lương var saleGradeServices = new Sal_GradeServices(); var lstObjSalGrade = new List<object>(); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(string.Empty); lstObjSalGrade.Add(1); lstObjSalGrade.Add(int.MaxValue - 1); var reposSalGrade = new CustomBaseRepository<Sal_Grade>(unitOfWork); var lstGradeAll = reposSalGrade.FindBy(s => s.IsDelete != true).ToList(); //var lstGradeAll = GetData<Sal_GradeEntity>(lstObjSalGrade, ConstantSql.hrm_sal_sp_get_Sal_Grade, UserLogin,ref status); //ds thông tin lương var salInfoServices = new Sal_SalaryInformationServices(); var lstObjSalInfo = new List<object>(); lstObjSalInfo.Add(null); lstObjSalInfo.Add(null); lstObjSalInfo.Add(null); lstObjSalInfo.Add(null); lstObjSalInfo.Add(null); lstObjSalInfo.Add(null); lstObjSalInfo.Add(1); lstObjSalInfo.Add(int.MaxValue - 1); var salInfoServices1 = new Sal_SalaryInformationServices(); var lstProfileIDs = new List<Guid>(); var lstSalaryInformation = salInfoServices.GetData<Sal_SalaryInformationEntity>(lstObjSalInfo, ConstantSql.hrm_sal_sp_get_Sal_SalaryInformation, UserLogin, ref status).ToList(); if (Transfer == EnumDropDown.Transfer.E_TRANSFER.ToString()) { lstSalaryInformation = lstSalaryInformation.Where(s => s.IsCash == true).ToList(); lstProfileIDs = lstSalaryInformation.Select(s => s.ProfileID).ToList(); } if (Transfer == EnumDropDown.Transfer.E_CASH.ToString()) { lstSalaryInformation = lstSalaryInformation.Where(s => s.IsCash == false).ToList(); lstProfileIDs = lstSalaryInformation.Select(s => s.ProfileID).ToList(); } if (Transfer == EnumDropDown.Transfer.E_OTHER.ToString()) { lstProfileIDs = lstSalaryInformation.Select(s => s.ProfileID).ToList(); } //ds loại mã lương var salaryClassServices = new Cat_SalaryClassServices(); var lstObjSalaryClass = new List<object>(); lstObjSalaryClass.Add(null); lstObjSalaryClass.Add(1); lstObjSalaryClass.Add(int.MaxValue - 1); var lstSalaryClass = salaryClassServices.GetData<Cat_SalaryClassEntity>(lstObjSalaryClass, ConstantSql.hrm_cat_sp_get_SalaryClass, UserLogin, ref status).ToList().Translate<Cat_SalaryClass>(); var revenueProfileServices = new Sal_RevenueForProfileServices(); var lstObjRevenueForProfile = new List<object>(); lstObjRevenueForProfile.Add(null); lstObjRevenueForProfile.Add(1); lstObjRevenueForProfile.Add(int.MaxValue - 1); var lstRevenueForProfile = revenueProfileServices.GetData<Sal_RevenueForProfileEntity>(lstObjRevenueForProfile, ConstantSql.hrm_sal_sp_get_RevenueForProfile, UserLogin, ref status).ToList(); //Ds phần tử lương var elementServices = new Cat_ElementServices(); var methodPayroll = MethodPayroll.E_NORMAL.ToString(); var lstObjElement = new List<object>(); lstObjElement.AddRange(new object[8]); lstObjElement[6] = 1; lstObjElement[7] = int.MaxValue - 1; var reposPayrollElement = new CustomBaseRepository<Cat_Element>(unitOfWork); var listPayrollElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_ElementByMethod, UserLogin, ref status).Where(s => s.MethodPayroll == methodPayroll).ToList().Translate<Cat_Element>(); #region Chọn lấy back up bảng lương - Tung.Ly 20150513 var salComputePayrollService = new Sal_ComputePayrollServices(); var lstSalPayrollTb = new List<Sal_PayrollTableEntity>(); var lstSalPayrollTbItem = new List<Sal_PayrollTableItemEntity>(); #region param store bang luong var payrollTableServices = new Sal_PayrollTableServices(); var lstObjPayrollTable = new List<object>(); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(from); lstObjPayrollTable.Add(to); lstObjPayrollTable.Add(1); lstObjPayrollTable.Add(int.MaxValue - 1); #endregion #region param store bảng lương chi tiết var payrollTableItemServices = new Sal_PayrollTableItemServices(); var lstObjPayrollTableItem = new List<object>(); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(from); lstObjPayrollTableItem.Add(to); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(1); lstObjPayrollTableItem.Add(int.MaxValue - 1); #endregion //kiem ra co du lieu backup không theo kỳ lương if (salComputePayrollService.CheckDataIsBackUp(TypeDataBKInScheduleTask.E_PAYROLL_BK.ToString(), dateStartCutOffDuration)) { //Du liệu backup #region ds bảng lương(backcup) lstSalPayrollTb = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTableBK, UserLogin, ref status) .Where(pr => pr.IsDelete == null && pr.MonthYear == monthYear && listProfileID.Contains(pr.ProfileID)).ToList(); List<Guid> listSalPayrollIDs = lstSalPayrollTb.Select(p => p.ID).ToList(); #endregion #region ds bảng lương chi tiết(backup) lstSalPayrollTbItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItemBK, UserLogin, ref status) .Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList(); #endregion } else { #region ds bảng lương // var reposSalPayrollTb = new CustomBaseRepository<Sal_PayrollTable>(unitOfWork); lstSalPayrollTb = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status) .Where(pr => pr.IsDelete == null && pr.MonthYear == monthYear && listProfileID.Contains(pr.ProfileID)).ToList(); List<Guid> listSalPayrollIDs = lstSalPayrollTb.Select(p => p.ID).ToList(); #endregion #region ds bảng lương chi tiết // var reposSalPayrollTbItem = new CustomBaseRepository<Sal_PayrollTableItem>(unitOfWork); lstSalPayrollTbItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status) .Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList(); #endregion } #endregion // var lstSalPayrollTbItem = reposSalPayrollTbItem.GetAll().Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList(); //Tạo cột dữ liệu cho table DataTable tblData = GetSchemaOverallPayroll(); #endregion #region " Không có dữ liệu Sal_PayrollTable" if (lstSalPayrollTb == null || lstSalPayrollTb.Count <= 0) { foreach (Hre_ProfileEntity profile in listProfile) { var lstRankByProfileId = lstShop.Where(s => s.ID == profile.ShopID).FirstOrDefault(); DataRow dr = tblData.NewRow(); dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.MonthYear] = monthYear; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeAttendance] = profile.CodeAttendance; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.LaborType] = profile.LaborType; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.OrgStructureName] = profile.OrgStructureName; dr["ShopGroupName"] = lstRankByProfileId != null ? lstRankByProfileId.ShopGroupName : string.Empty; dr["OrgStructureType"] = profile.E_COMPANY_CODE + "->" + profile.E_BRANCH_CODE + "->" + profile.E_UNIT_CODE + "->" + profile.E_DIVISION_CODE + "->" + profile.E_DEPARTMENT_CODE + "->" + profile.E_TEAM_CODE + "->" + profile.E_SECTION_CODE; //Sal_BasicSalaryEntity BasicSalaryByProfile = listBasicSalary.SingleOrDefault(m => m.ProfileID == profile.ID); //dr["SalaryRankName"] = BasicSalaryByProfile != null ? BasicSalaryByProfile.SalaryRankName : ""; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.Cat_EmployeeType != null ? profile.Cat_EmployeeType.EmployeeTypeName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.EmployeeTypeName; //dr["CostCenterCodePayrollTable"] = profile.CostCentreNamePayrollTable; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor != null ? profile.Supervisor : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor != null ? profile.HighSupervisor : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.Cat_WorkPlace != null ? profile.Cat_WorkPlace.WorkPlaceName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.WorkPlaceName; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax != null ? profile.CodeTax : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo != null ? profile.IDNo : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.CostCentreName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.CostCentreName; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.Code : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.CostCentreCode; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.Cat_Position != null ? profile.Cat_Position.PositionName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.PositionName; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.JobTitleName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.JobTitleName; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email != null ? profile.Email : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email; if (profile.DateHire != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateHire] = profile.DateHire.Value; if (profile.DateQuit != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit; if (profile.DateEndProbation != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo != null ? profile.SocialInsNo : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.Cat_PayrollGroup != null ? profile.Cat_PayrollGroup.PayrollGroupName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.PayrollGroupName; dr["CostSource"] = profile.CostSourceName; if (profile.SalaryClassID != null) { Cat_SalaryClass salClass = lstSalaryClass.Where(s => s.ID == profile.SalaryClassID.Value && s.IsDelete == null).FirstOrDefault(); if (salClass != null) { dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryClassName] = salClass.SalaryClassName; } } if (profile.OrgStructureID != null) { var orgName = GetParentOrg(listOrgAll, listOrgType, profile.OrgStructureID); if (orgName.Count < 3) { orgName.Insert(0, string.Empty); if (orgName.Count < 3) { orgName.Insert(0, string.Empty); } } dr[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2]; dr[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1]; dr[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0]; } // Hre_Contract hrcontract = lstHreContractAll.Where(hr => hr.ProfileID == profile.ID && hr.IsDelete == null).FirstOrDefault(); //if (hrcontract != null && hrcontract.RankRateID != null) //{ // Cat_SalaryRank salrank = lstSalaryRankAll.Where(rk => rk.ID == hrcontract.RankRateID.Value && rk.IsDelete == null).FirstOrDefault(); // dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryRankName] = salrank != null ? salrank.SalaryRankName : string.Empty; //} #region Insurance, Bank var salaryInformationList = lstSalaryInformation.Where(sal => sal.ProfileID == profile.ID).ToList(); if (salaryInformationList.Count > 0) { Sal_SalaryInformationEntity bankSalary = salaryInformationList[0]; string accountTemp = string.Empty; string accountNo = string.Empty; string accountNo2 = string.Empty; string bankNameTemp = string.Empty; string bankCode = string.Empty; accountTemp = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo; accountNo = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo; accountNo2 = bankSalary.AccountNo2 == null ? string.Empty : bankSalary.AccountNo2; bankNameTemp = bankSalary.BankName == null ? string.Empty : bankSalary.BankName; bankCode = bankSalary.BankCode1 == null ? string.Empty : bankSalary.BankCode1; if (!string.IsNullOrEmpty(accountTemp)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankAccountNo] = accountTemp.Trim(); if (!string.IsNullOrEmpty(accountNo)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo] = accountNo.Trim(); if (!string.IsNullOrEmpty(accountNo2)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo2] = accountNo2.Trim(); if (!string.IsNullOrEmpty(bankCode)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankCode] = bankCode.Trim(); if (!string.IsNullOrEmpty(bankNameTemp)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankName] = bankNameTemp.Trim(); } #endregion tblData.Rows.Add(dr); } } #endregion #region " Có dữ liệu tính lương Sal_PayrollTable" else { foreach (Sal_PayrollTableEntity payroll in lstSalPayrollTb) { if (Transfer != null) { listProfile = listProfile.Where(s => lstProfileIDs.Contains(s.ID)).ToList(); } Hre_ProfileEntity profile = listProfile.Where(s => s.ID == payroll.ProfileID).FirstOrDefault(); if (profile == null) continue; var lstRevenueByProfileId = lstRevenueForProfile.Where(s => s.ProfileID == payroll.ProfileID).FirstOrDefault(); var lstRankByProfileId = lstShop.Where(s => s.ID == profile.ShopID).FirstOrDefault(); //var unusualByProfileId = lstUnusual.Where(s => s.ProfileID == profile.ID).FirstOrDefault(); List<Sal_PayrollTableItemEntity> listItem = lstSalPayrollTbItem.Where(pi => pi.PayrollTableID == payroll.ID).ToList(); DataRow dr = tblData.NewRow(); dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ID] = payroll.ID; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.MonthYear] = monthYear; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeAttendance] = profile.CodeAttendance; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.OrgStructureName] = profile.OrgStructureName; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IsPaid] = payroll.IsPaid == null ? false : payroll.IsPaid.Value; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.LaborType] = profile.LaborType; dr["OrgStructureType"] = profile.E_COMPANY_CODE + "->" + profile.E_BRANCH_CODE + "->" + profile.E_UNIT_CODE + "->" + profile.E_DIVISION_CODE + "->" + profile.E_DEPARTMENT_CODE + "->" + profile.E_TEAM_CODE + "->" + profile.E_SECTION_CODE; dr["ShopGroupName"] = lstRankByProfileId != null ? lstRankByProfileId.ShopGroupName : string.Empty; //Sal_BasicSalaryEntity BasicSalaryByProfile = listBasicSalary.SingleOrDefault(m => m.ProfileID == profile.ID); //dr["SalaryRankName"] = BasicSalaryByProfile != null ? BasicSalaryByProfile.SalaryRankName : ""; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.Cat_EmployeeType != null ? profile.Cat_EmployeeType.EmployeeTypeName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.EmployeeTypeName; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor != null ? profile.Supervisor : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor != null ? profile.HighSupervisor : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.Cat_WorkPlace != null ? profile.Cat_WorkPlace.WorkPlaceName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.WorkPlaceName; // dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkingPlace] = profile.WorkingPlace != null ? profile.WorkingPlace : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax != null ? profile.CodeTax : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo != null ? profile.IDNo : string.Empty; dr["CostCenterCodePayrollTable"] = payroll.CostCentreNamePayrollTable; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.CostCentreName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.CostCentreName; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.Code : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.CostCentreCode; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.Cat_Position != null ? profile.Cat_Position.PositionName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.PositionName; //dr["PositionCode"] = profile.Cat_Position != null ? profile.Cat_Position.Code : string.Empty; dr["PositionCode"] = profile.PositionCode; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.JobTitleName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.JobTitleName; //dr["JobtitleCode"] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.Code : string.Empty; dr["JobtitleCode"] = profile.JobTitleCode; dr["CostSource"] = profile.CostSourceName; //dr["Rank"] = lstRankByProfileId != null ? lstRankByProfileId.Rank : string.Empty; //dr["Target"] = lstRevenueByProfileId != null ? lstRevenueByProfileId.Target : 0; //dr["Actual"] = lstRevenueByProfileId != null ? lstRevenueByProfileId.Actual : 0; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email != null ? profile.Email : string.Empty; if (profile.DateHire != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateHire] = profile.DateHire.Value; if (profile.DateQuit != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit; if (profile.DateEndProbation != null) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value; //if (unusualByProfileId != null) //{ // if (unusualByProfileId.MonthStart != null) // { // dr["MonthStart"] = unusualByProfileId.MonthStart; // } //} //if (unusualByProfileId != null) //{ // if (unusualByProfileId.MonthEnd != null) // { // dr["MonthEnd"] = unusualByProfileId.MonthEnd; // } //} //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo != null ? profile.SocialInsNo : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.Cat_PayrollGroup != null ? profile.Cat_PayrollGroup.PayrollGroupName : string.Empty; dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.PayrollGroupName; if (profile.OrgStructureID != null) { var orgName = new List<string>(); var orgCode = new List<string>(); if (isGroup) { var orgEntity = listOrgAll.Where(s => s.ID == profile.OrgStructureID).FirstOrDefault(); if (orgEntity != null) { orgName = GetParentOrgNameForShiseido(listOrgAll, listOrgType, profile.OrgStructureID); if (orgName.Count < 5) { orgName.Insert(0, string.Empty); if (orgName.Count < 5) { orgName.Insert(0, string.Empty); } if (orgName.Count < 5) { orgName.Insert(0, string.Empty); } if (orgName.Count < 5) { orgName.Insert(0, string.Empty); } if (orgName.Count < 5) { orgName.Insert(0, string.Empty); } } orgCode = GetParentOrgCodeForShiseido(listOrgAll, listOrgType, profile.OrgStructureID); if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); } if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); } if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); } if (orgCode.Count < 5) { orgCode.Insert(0, string.Empty); } } } dr["Công Ty"] = orgName[4]; dr["Chi Nhánh"] = orgName[3]; dr[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2]; dr[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1]; dr[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0]; dr["Mã Công Ty"] = orgCode[4]; dr["Mã Chi Nhánh"] = orgCode[3]; dr["Mã Channel"] = orgCode[2]; dr["Mã Region"] = orgCode[1]; dr["Mã Area"] = orgCode[0]; } //var orgBranch = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_BRANCH, listOrgAll, listOrgType); //var orgOrg = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_DEPARTMENT, listOrgAll, listOrgType); //var orgTeam = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_TEAM, listOrgAll, listOrgType); //var orgSection = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_SECTION, listOrgAll, listOrgType); //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; //dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; } // Hre_Contract hrcontract = lstHreContractAll.Where(hr => hr.ProfileID == profile.ID && hr.IsDelete == null).FirstOrDefault(); if (profile.SalaryClassID != null) { Cat_SalaryClass salClass = lstSalaryClass.Where(s => s.ID == profile.SalaryClassID.Value && s.IsDelete == null).FirstOrDefault(); if (salClass != null) { dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryClassName] = salClass.SalaryClassName; } } //if (hrcontract != null && hrcontract.RankRateID != null) //{ // if (profile.SalaryClassID != null) // { // Cat_SalaryRank salrank = lstSalaryRankAll.Where(rk => rk.SalaryClassID == profile.SalaryClassID.Value && hrcontract.RankRateID == rk.ID && rk.IsDelete == null).FirstOrDefault(); // if (salrank != null) // dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryRankName] = salrank.SalaryRankName; // } //} #region Insurance, Bank var salaryInformationList = lstSalaryInformation.Where(sal => sal.ProfileID == profile.ID).ToList(); if (salaryInformationList.Count > 0) { Sal_SalaryInformationEntity bankSalary = salaryInformationList[0]; string accountTemp = string.Empty; string accountNo = string.Empty; string accountNo2 = string.Empty; string bankNameTemp = string.Empty; string bankCode = string.Empty; accountTemp = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo; accountNo = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo; accountNo2 = bankSalary.AccountNo2 == null ? string.Empty : bankSalary.AccountNo2; bankNameTemp = bankSalary.BankName == null ? string.Empty : bankSalary.BankName; bankCode = bankSalary.BankCode1 == null ? string.Empty : bankSalary.BankCode1; if (!string.IsNullOrEmpty(accountTemp)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankAccountNo] = accountTemp.Trim(); if (!string.IsNullOrEmpty(accountNo)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo] = accountNo.Trim(); if (!string.IsNullOrEmpty(accountNo2)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo2] = accountNo2.Trim(); if (!string.IsNullOrEmpty(bankCode)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankCode] = bankCode.Trim(); if (!string.IsNullOrEmpty(bankNameTemp)) dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankName] = bankNameTemp.Trim(); } #endregion #region sal grade Sal_Grade grade = new Sal_Grade(); if (gradePayrollID != null) { grade = lstGradeAll.Where(gr => gr.IsDelete == null && gr.ProfileID == profile.ID && gradePayrollID.Value == gr.GradePayrollID && gr.MonthStart < to).OrderByDescending(gr => gr.MonthStart).FirstOrDefault(); } else { grade = lstGradeAll.Where(gr => gr.IsDelete == null && gr.ProfileID == profile.ID && gr.MonthStart < to).OrderByDescending(gr => gr.MonthStart).FirstOrDefault(); } if (grade != null) { Cat_GradePayroll gradepayroll = grade.Cat_GradePayroll; if (gradepayroll != null && gradepayroll.IsFormulaSalary == true) { try { var lstSalPrItem = lstSalPayrollTbItem.Where(sal => sal.PayrollTableID == payroll.ID).ToList(); var lstelement = listPayrollElement.Where(pr => pr.GradePayrollID == gradepayroll.ID).ToList(); if (lstelement != null && lstelement.Count > 0) { foreach (Cat_Element payrollElement in lstelement) { if (payrollElement != null && !String.IsNullOrEmpty(payrollElement.ElementCode)) { if (payrollElement.Type == "Nvarchar") { //Add phần tử vào cột dữ liệu nếu chưa có if (!tblData.Columns.Contains(payrollElement.ElementCode)) { tblData.Columns.Add(payrollElement.ElementCode); } } else { //Add phần tử vào cột dữ liệu nếu chưa có if (!tblData.Columns.Contains(payrollElement.ElementCode)) { tblData.Columns.Add(payrollElement.ElementCode, typeof(Double)); } } //Lấy value của phần tử if (tblData.Columns.Contains(payrollElement.ElementCode)) { Sal_PayrollTableItemEntity item = lstSalPrItem.Where(it => it.Code == payrollElement.ElementCode).FirstOrDefault(); Double value = 0; if (item != null) { if (item.ValueType == typeof(Double).Name) { Double.TryParse(item.Value, out value); dr[payrollElement.ElementCode] = value; } if (item.ValueType == EnumDropDown.DataType.Nvarchar.ToString()) { dr[payrollElement.ElementCode] = item.Value; } } } } } } } catch { } } tblData.Rows.Add(dr); } #endregion } } #endregion var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); config.Add("hidden", true); configs.Add("ID", config); return tblData.ConfigTable(configs, true); } } catch (Exception ex) { return new DataTable(); } }