Пример #1
0
        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();
            }
        }
Пример #2
0
        /// <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();
            }
        }