public DataTable GetPerformEvaDetail(Guid PerformanceID, Guid PerformanceEvaID, int OrderEva,string userLogin) { DtPerformEvaDetail = new DataTable(); Eva_PerformanceEvaServices service1 = new Eva_PerformanceEvaServices(); var Columns = service1.GetlistPerformanceEva(PerformanceID, PerformanceEvaID).ToList(); if (Columns == null || Columns.Count == 0) return null; //cot nv // DtPerformEvaDetail.Columns.Add(new DataColumn(ConstantDisplay.HRM_Evaluation_Evaluator.TranslateString())); //cot tieu chi DtPerformEvaDetail.Columns.Add(new DataColumn(ConstantDisplay.HRM_Eva_PerformanceEvaWaitingEva_KPIName.TranslateString())); //cot Mo ta DtPerformEvaDetail.Columns.Add(new DataColumn(ConstantDisplay.HRM_Eva_PerformanceEvaWaitingEva_DescriptionKP.TranslateString())); //cot Màu chữ KPI DtPerformEvaDetail.Columns.Add(new DataColumn("KPIColor")); foreach (var i in Columns) { DtPerformEvaDetail.Columns.Add(new DataColumn(ConstantDisplay.HRM_Eva_PerformanceEvaWaitingEva_Evaluation.TranslateString() + " " + i.OrderEva)); DtPerformEvaDetail.Columns.Add(new DataColumn(ConstantDisplay.HRM_Eva_PerformanceEvaWaitingEva_Mark.TranslateString() + " " + i.OrderEva)); } DataRow Row; try { string status = string.Empty; var baseService = new BaseService(); var objs = new List<object>(); objs.Add(PerformanceID); objs.Add(OrderEva); objs.Add(1); objs.Add(2000); var result = baseService.GetData<Eva_PerformanceEvaDetailEntity>(objs, ConstantSql.hrm_eva_sp_get_PerformanceEvaDetailAllSelfByPerformanceEvaID,userLogin, ref status); if (result == null || result.Count == 0) return null; List<int> listOrderEva = result.OrderByDescending(x => x.OrderEva).Select(x => x.OrderEva.Value).Distinct().ToList(); var result1 = new List<Eva_PerformanceEvaDetailEntity>(); foreach (var y in CountKPI(PerformanceID)) { Row = DtPerformEvaDetail.NewRow(); result1 = result.Where(x => x.KPIID == y).ToList(); if (result1 != null && result1.Count != 0) { Row[ConstantDisplay.HRM_Eva_PerformanceEvaWaitingEva_KPIName.TranslateString()] = result1.FirstOrDefault().KPIName; Row[ConstantDisplay.HRM_Eva_PerformanceEvaWaitingEva_DescriptionKP.TranslateString()] = result1.FirstOrDefault().DescriptionKP; Row["KPIColor"] = result1.FirstOrDefault().KPIColor; string Evaluator = ""; foreach (var x in result1) { foreach (var k in listOrderEva) { if (k == x.OrderEva) { Evaluator += x.Evaluator + "; "; Row[ConstantDisplay.HRM_Eva_PerformanceEvaWaitingEva_Evaluation.TranslateString() + " " + k] = x.Evaluation; Row[ConstantDisplay.HRM_Eva_PerformanceEvaWaitingEva_Mark.TranslateString() + " " + k] = x.Mark; } } } // Evaluator = Evaluator.Substring(0, Evaluator.Length - 2); // Row[ConstantDisplay.HRM_Evaluation_Evaluator.TranslateString()] = Evaluator; } DtPerformEvaDetail.Rows.Add(Row); } } catch { return null; } var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); config.Add("hidden", true); if (!configs.ContainsKey("KPIColor")) { configs.Add("KPIColor", config); } return DtPerformEvaDetail.ConfigTable(configs); }
public ContentResult ImportResultInterview([DataSourceRequest] DataSourceRequest request, CatImportModel model) { var _fileName = Common.GetPath(Common.TemplateURL) + model.TemplateFile; _fileName = _fileName.Replace("/", "\\"); Rec_ImportInterviewResultService importService = new Rec_ImportInterviewResultService { FileName = _fileName, UserID = model.UserID, }; DataTable dataTableInvalid = new DataTable("InvalidData"); DataTable dataTable = new DataTable("ImportData"); try { importService.ImportInterviewResult(); dataTable = importService.GetImportObject(); dataTableInvalid = importService.GetInvalidObject(); } catch (Exception ex) { model.Description = ex.Message; } var importConfigs = new Dictionary<string, Dictionary<string, object>>(); foreach (DataColumn item in dataTable.Columns) { var displayField = importService.FieldMappings.Where(d => d.Value == item.ColumnName).Select(d => d.Key).FirstOrDefault(); if (string.IsNullOrWhiteSpace(displayField) || importService.ListInvisibleField.Contains(item.ColumnName)) { var config = new Dictionary<string, object>(); config.Add("hidden", true); importConfigs.Add(item.ColumnName, config); } else { var config = new Dictionary<string, object>(); config.Add("title", displayField); importConfigs.Add(item.ColumnName, config); } } model.ListImportData = dataTable.ConfigTable(importConfigs).ToDataSourceResult(request); model.ListInvalidData = dataTableInvalid.ConfigTable().ToDataSourceResult(request); var serializer = new JavaScriptSerializer(); serializer.MaxJsonLength = Int32.MaxValue - 1; var result = new ContentResult(); result.Content = serializer.Serialize(model); result.ContentType = "text/json"; return result; }
/// <summary> /// Tho.Bui: Khởi tạo datatable cho báo cáo thống kê ngày nghĩ năm /// </summary> /// <param name="MonthStart"></param> /// <param name="MonthEnd"></param> /// <param name="dicSchemma"></param> /// <returns></returns>\ /// DataTable GetSchema(DateTime MonthStart, DateTime MonthEnd, out Dictionary<string, string> dicSchemma) { using (var context = new VnrHrmDataContext()) { var configs = new Dictionary<string, Dictionary<string, object>>(); var confighiden = new Dictionary<string, object>(); var config60 = new Dictionary<string, object>(); config60.Add("width", 60); var config100 = new Dictionary<string, object>(); config100.Add("width", 100); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoCat_LeaveDayType = new CustomBaseRepository<Cat_LeaveDayType>(unitOfWork); string status = string.Empty; dicSchemma = new Dictionary<string, string>(); var lstLeavedayType = repoCat_LeaveDayType.FindBy(s => s.Code != null).Select(s => s.Code).Distinct().ToList(); DataTable tb = new DataTable("Att_ReportSickLeaveEntity"); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.CodeEmp); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.ProfileName); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.DepartmentCode); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.BranchCode); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.TeamCode); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.CodeSection); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.OrgName); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.SectionName); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.CodePosition); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.CodeJobtitle); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.BranchName); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.TotalP); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.TotalSC); Dictionary<string, string> dicDataLocation = new Dictionary<string, string>(); int Stt = 0; for (DateTime MonthCheck = MonthStart; MonthCheck <= MonthEnd; MonthCheck = MonthCheck.AddMonths(1)) { foreach (string code in lstLeavedayType) { Stt++; string nametempt = code + MonthCheck.Month; if (!dicDataLocation.ContainsKey(nametempt)) { dicDataLocation.Add(code + MonthCheck.Month, Att_ReportSickLeaveEntity.FieldNames.Data + Stt); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.Data + Stt, typeof(double)); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.DataHeader + Stt, typeof(string)); } if (!configs.ContainsKey(Att_ReportSickLeaveEntity.FieldNames.Data + Stt)) configs.Add(Att_ReportSickLeaveEntity.FieldNames.Data + Stt, config60); if (!configs.ContainsKey(Att_ReportSickLeaveEntity.FieldNames.DataHeader + Stt)) configs.Add(Att_ReportSickLeaveEntity.FieldNames.DataHeader + Stt, config100); } } Stt++; dicDataLocation.Add(Att_ReportSickLeaveEntity.FieldNames.SumANL, Att_ReportSickLeaveEntity.FieldNames.Data + Stt); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.Data + Stt, typeof(double)); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.DataHeader + Stt, typeof(string)); Stt++; dicDataLocation.Add(Att_ReportSickLeaveEntity.FieldNames.SumSICK, Att_ReportSickLeaveEntity.FieldNames.Data + Stt); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.Data + Stt, typeof(double)); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.DataHeader + Stt, typeof(string)); if (!configs.ContainsKey(Att_ReportSickLeaveEntity.FieldNames.Data + Stt)) configs.Add(Att_ReportSickLeaveEntity.FieldNames.Data + Stt, config60); if (!configs.ContainsKey(Att_ReportSickLeaveEntity.FieldNames.DataHeader + Stt)) configs.Add(Att_ReportSickLeaveEntity.FieldNames.DataHeader + Stt, config100); dicSchemma = dicDataLocation; tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.DateFrom, typeof(DateTime)); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.DateTo, typeof(DateTime)); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.UserExport); tb.Columns.Add(Att_ReportSickLeaveEntity.FieldNames.DateExport, typeof(DateTime)); var config45 = new Dictionary<string, object>(); config45.Add("width", 45); var config50 = new Dictionary<string, object>(); config50.Add("width", 50); var config55 = new Dictionary<string, object>(); config55.Add("width", 55); //var config60 = new Dictionary<string, object>(); //config60.Add("width", 60); var config65 = new Dictionary<string, object>(); config65.Add("width", 65); var config70 = new Dictionary<string, object>(); config70.Add("width", 70); var config75 = new Dictionary<string, object>(); config75.Add("width", 75); var config80 = new Dictionary<string, object>(); config80.Add("width", 80); var config85 = new Dictionary<string, object>(); config85.Add("width", 85); var config90 = new Dictionary<string, object>(); config90.Add("width", 90); var config95 = new Dictionary<string, object>(); config95.Add("width", 95); var config110 = new Dictionary<string, object>(); config110.Add("width", 110); var config160 = new Dictionary<string, object>(); config160.Add("width", 160); if (!configs.ContainsKey("CodeEmp")) configs.Add("CodeEmp", config85); if (!configs.ContainsKey("BranchCode")) configs.Add("BranchCode", config90); if (!configs.ContainsKey("DepartmentCode")) configs.Add("DepartmentCode", config90); if (!configs.ContainsKey("TeamCode")) configs.Add("TeamCode", config65); if (!configs.ContainsKey("CodeSection")) configs.Add("CodeSection", config75); if (!configs.ContainsKey("CodePosition")) configs.Add("CodePosition", config80); if (!configs.ContainsKey("CodeJobtitle")) configs.Add("CodeJobtitle", config90); if (!configs.ContainsKey("BranchName")) configs.Add("BranchName", config75); if (!configs.ContainsKey("OrgName")) configs.Add("OrgName", config70); if (!configs.ContainsKey("SectionName")) configs.Add("SectionName", config45); if (!configs.ContainsKey("TotalP")) configs.Add("TotalP", config160); if (!configs.ContainsKey("TotalSC")) configs.Add("TotalSC", config160); confighiden.Add("hidden", true); if (!configs.ContainsKey("DateFrom")) configs.Add("DateFrom", confighiden); if (!configs.ContainsKey("DateTo")) configs.Add("DateTo", confighiden); if (!configs.ContainsKey("UserExport")) configs.Add("UserExport", confighiden); if (!configs.ContainsKey("DateExport")) configs.Add("DateExport", confighiden); return tb.ConfigTable(configs); } }
public DataTable GetReportAttendanceByMonthV2(List<Guid> lstProfileId, DateTime? Month, bool? _IsCreateTemplate) { DataTable dt = new DataTable(); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var lstAttendance = unitOfWork.CreateQueryable<Att_AttendanceTable>(m => m.MonthYear == Month && lstProfileId.Contains(m.ProfileID)) .Select(m => new { m.ID, m.ProfileID, m.StdWorkDayCount, m.LeaveDay1Type, m.LeaveDay1Hours, m.LeaveDay2Type, m.LeaveDay2Hours, m.LeaveDay3Type, m.LeaveDay3Hours, m.LeaveDay4Type, m.LeaveDay4Hours, m.LateEarlyTotal }).ToList(); var lstAttendanceID = lstAttendance.Select(m => m.ID).ToList(); var lstAttendanceItem = unitOfWork.CreateQueryable<Att_AttendanceTableItem>(m => lstAttendanceID.Contains(m.AttendanceTableID)) .Select(m => new { m.WorkDate, m.ShiftID, m.AttendanceTableID, m.WorkPaidHours, m.OvertimeHours, m.OvertimeTypeID, m.LeaveHours, m.ExtraOvertimeHours, m.ExtraOvertimeHours2, m.ExtraOvertimeHours3, m.ExtraOvertimeTypeID, m.ExtraOvertimeType2ID, m.ExtraOvertimeType3ID }).ToList(); var lstProfile = unitOfWork.CreateQueryable<Hre_Profile>(m => lstProfileId.Contains(m.ID)) .Select(m => new { m.ID, m.ProfileName, m.CodeEmp, m.OrgStructureID, m.DateHire, m.PositionID, m.DateQuit }).ToList(); var lstOrgID = lstProfile.Where(m => m.OrgStructureID != null).Select(m => m.OrgStructureID.Value).Distinct().ToList(); var lstOrgAll = unitOfWork.CreateQueryable<Cat_OrgStructure>().ToList(); var lstPosition = unitOfWork.CreateQueryable<Cat_Position>().Select(m => new { m.ID, m.PositionName }).ToList(); var lstLeaveType = unitOfWork.CreateQueryable<Cat_LeaveDayType>().Select(m => new { m.ID, m.Code, m.CodeStatistic }).ToList(); var lstShift = unitOfWork.CreateQueryable<Cat_Shift>().Select(m => new { m.ID, m.IsNightShift, m.Code }).ToList(); var lstNightShiftID = lstShift.Where(m => m.IsNightShift == true).Select(m => m.ID).ToList(); dt = GetSchemaReportAttendanceByMonthV2(lstLeaveType.Select(m => m.Code).ToList(), lstShift.Select(m=>m.Code).ToList()); if (_IsCreateTemplate==true) return dt; Dictionary<Guid, OrgNameClass> DicOrgFull = (new Hre_ProfileServices()).GetOrgFullLink(lstOrgID, lstOrgAll); foreach (var profileID in lstProfileId) { DataRow dr = dt.NewRow(); var profile = lstProfile.Where(m => m.ID == profileID).FirstOrDefault(); if (profile == null) continue; dr[Att_ReportMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp; dr[Att_ReportMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName; Guid? OrgID = profile.OrgStructureID; if (OrgID != null && DicOrgFull.ContainsKey(OrgID.Value)) { OrgNameClass OrgName = DicOrgFull[OrgID.Value]; if (OrgName != null ) { if(OrgName.DepartmentName!=null) dr[Att_ReportMonthlyEntity.FieldNames.DepartmentName] = OrgName.DepartmentName; if (OrgName.SectionName != null) dr[Att_ReportMonthlyEntity.FieldNames.SectionName] = OrgName.SectionName; } } var position = lstPosition.Where(m => m.ID == profile.PositionID).FirstOrDefault(); if (position != null && position.PositionName!=null) { dr[Att_ReportMonthlyEntity.FieldNames.PositionName] = position.PositionName; } if (profile.DateHire != null) { dr[Att_ReportMonthlyEntity.FieldNames.DateHire] = profile.DateHire; } if (profile.DateQuit != null) { dr[Att_ReportMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit; } var AttendanceTableByProfile = lstAttendance.Where(m => m.ProfileID == profileID).FirstOrDefault(); if (AttendanceTableByProfile != null) { var lstAttendanceTableItemByProfile = lstAttendanceItem.Where(m => m.AttendanceTableID == AttendanceTableByProfile.ID).OrderBy(m => m.WorkDate).ToList(); int i = 0; foreach (var item in lstAttendanceTableItemByProfile) { i++; dr["Data" + i] = item.WorkPaidHours; } //Trừ đi ngày nghỉ không lương dr[Att_ReportMonthlyEntity.FieldNames.TotalDayHavePaid] = lstAttendanceTableItemByProfile.Count(m => m.WorkPaidHours > 0); //Công chuẩn trừ đi nghỉ ko luong dr[Att_ReportMonthlyEntity.FieldNames.RealdayWorking] = AttendanceTableByProfile.StdWorkDayCount - lstAttendanceTableItemByProfile.Count(m => m.WorkPaidHours == 0 && m.LeaveHours != null && m.LeaveHours > 0); foreach (var leave in lstLeaveType) { double HourLeave = 0; HourLeave += (AttendanceTableByProfile.LeaveDay1Type != null && AttendanceTableByProfile.LeaveDay1Type == leave.ID) ? AttendanceTableByProfile.LeaveDay1Hours : 0; HourLeave += (AttendanceTableByProfile.LeaveDay2Type != null && AttendanceTableByProfile.LeaveDay2Type == leave.ID) ? AttendanceTableByProfile.LeaveDay2Hours : 0; HourLeave += (AttendanceTableByProfile.LeaveDay3Type != null && AttendanceTableByProfile.LeaveDay3Type == leave.ID) ? AttendanceTableByProfile.LeaveDay3Hours : 0; HourLeave += (AttendanceTableByProfile.LeaveDay4Type != null && AttendanceTableByProfile.LeaveDay4Type == leave.ID) ? AttendanceTableByProfile.LeaveDay4Hours : 0; dr[leave.Code.Trim()] = HourLeave; } if (AttendanceTableByProfile.LateEarlyTotal!=null) dr[Att_ReportMonthlyEntity.FieldNames.LateEarlyTotal] = AttendanceTableByProfile.LateEarlyTotal; dr[Att_ReportMonthlyEntity.FieldNames.TotalNightShiftHourOver8] = lstAttendanceTableItemByProfile.Where(m => m.ShiftID != null && lstNightShiftID.Contains(m.ShiftID.Value) && m.WorkPaidHours >= 8).Sum(m => m.WorkPaidHours); dr[Att_ReportMonthlyEntity.FieldNames.TotalNightShiftHourUnder8] = lstAttendanceTableItemByProfile.Where(m => m.ShiftID != null && lstNightShiftID.Contains(m.ShiftID.Value) && m.WorkPaidHours < 8).Sum(m => m.WorkPaidHours); foreach (var shift in lstShift) { dr["TotalShiftHourOver8" + "_" + shift.Code.Trim()] = lstAttendanceTableItemByProfile.Where(m => m.ShiftID != null && m.ShiftID == shift.ID && m.WorkPaidHours >= 8).Sum(m => m.WorkPaidHours); } dr[Att_ReportMonthlyEntity.FieldNames.HourOvertimeOver8PerDay] = lstAttendanceTableItemByProfile.Where(m => (m.OvertimeHours + m.ExtraOvertimeHours + m.ExtraOvertimeHours2 + m.ExtraOvertimeHours3) > 8).Select(m => (m.OvertimeHours + m.ExtraOvertimeHours + m.ExtraOvertimeHours2 + m.ExtraOvertimeHours3)).FirstOrDefault(); } dt.Rows.Add(dr); } } return dt.ConfigTable(); }
public ContentResult Import([DataSourceRequest] DataSourceRequest request, CatImportModel model) { var _fileName = Common.GetPath(Common.TemplateURL) + model.TemplateFile; _fileName = _fileName.Replace("/", "\\"); ImportService importService = new ImportService { FileName = _fileName, UserID = model.UserID, ImportTemplateID = model.ID, DateTimeFormat = model.FormatDate, ImportMode = model.ProcessDuplicateData == HRM.Infrastructure.Utilities.EnumDropDown.DuplicateData.E_INSERT.ToString() ? ImportDataMode.Insert : model.ProcessDuplicateData == HRM.Infrastructure.Utilities.EnumDropDown.DuplicateData.E_UPDATE.ToString() ? ImportDataMode.Update : ImportDataMode.Skip }; importService.ProgressChanged += importService_ProgressChanged; DataTable dataTableInvalid = new DataTable("InvalidData"); DataTable dataTable = new DataTable("ImportData"); string[] lstFieldInvalid = new string[] { "DataField", "InvalidValue", "ExcelField", "ExcelValue", "ValueType", "Desciption" }; try { importService.Import(); dataTable = importService.ListImportData.Translate(importService.ListValueField.ToArray()); dataTableInvalid = importService.ListInvalidData.Translate(lstFieldInvalid); if (model.UserID != Guid.Empty) { if (ListImportDataTemp == null) { ListImportDataTemp = new Dictionary<Guid, IList>(); } if (ListImportDataTemp.ContainsKey(model.UserID)) { ListImportDataTemp[model.UserID] = importService.ListImportData; } else { ListImportDataTemp.Add(model.UserID, importService.ListImportData); } if (ListInvalidDataTemp == null) { ListInvalidDataTemp = new Dictionary<Guid, IList>(); } if (ListInvalidDataTemp.ContainsKey(model.UserID)) { ListInvalidDataTemp[model.UserID] = importService.ListInvalidData.Select(d => d.ImportData).Distinct().ToList(); } else { ListInvalidDataTemp.Add(model.UserID, importService.ListInvalidData.Select(d => d.ImportData).Distinct().ToList()); } } else { model.Description = "Người dùng ảo"; } if (model.IsImportAndSave) { if (importService.Save(model.UserID, importService.ListImportData, UserLogin)) { model.Description = NotificationType.Success.ToString(); } else { model.Description = NotificationType.Error.ToString(); } } } catch (Exception ex) { model.Description = ex.Message; } model.ListImportData = dataTable.ConfigTable().ToDataSourceResult(request); model.ListInvalidData = dataTableInvalid.ConfigTable().ToDataSourceResult(request); model.UrlInvalidFileName = ExportInvalidData(model.UserID, model.ID, importService); model.ListValueField = importService.ListValueField; model.ListDisplayField = lstFieldInvalid.ToList(); var serializer = new JavaScriptSerializer(); serializer.MaxJsonLength = Int32.MaxValue - 1; var result = new ContentResult(); result.Content = serializer.Serialize(model); result.ContentType = "text/json"; return result; }
public DataTable Ins_ReportInsuranceTrackingMonthlyLoadData(string orgIds, DateTime? dtDateFrom, DateTime? dtDateTo, bool? isProfileQuit, string codeEmp, List<Guid> socialInsPlaceIDs, string userLogin) { var DtInsuranceTrackingMonthly = new DataTable(); var dateFrom = dtDateFrom.HasValue ? dtDateFrom.Value : new DateTime(1996, 1, 1); var dateTo = dtDateTo.HasValue ? dtDateTo.Value : DateTime.Now; using (var context = new VnrHrmDataContext()) { var status = string.Empty; var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork); var repoCat_SalaryClass = new Cat_SalaryClassRepository(unitOfWork); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); //Lay DS Ma Luong var salaryClass = repoCat_SalaryClass.FindBy(p => p.IsDelete == null).ToList(); #region ds NV đóng Bảo Hiểm theo phòng ban và tháng var listInsMonthlyObj = new List<object> { orgIds, dateFrom, dateTo, null, null, null, null, null, 1, int.MaxValue - 1 }; if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString()) { listInsMonthlyObj.Add("id"); } if (!string.IsNullOrEmpty(codeEmp)) { listInsMonthlyObj[0] = null; listInsMonthlyObj[3] = codeEmp; } var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthlyFromTo, userLogin, ref status).Translate<Ins_ProfileInsuranceMonthly>(); if (!lstProfileInsuranceMonthlyInDb.Any()) { return new DataTable(); } List<Guid> lstProfileIDs = lstProfileInsuranceMonthlyInDb.Select(p => p.ProfileID ?? Guid.Empty).Distinct().ToList(); #endregion #region Get Profiles by orgs #region DS Profile int orgOrderNumber = 0; var lstOrgOrderNumber = orgIds.Split(',').Where(p => int.TryParse(p, out orgOrderNumber)).Select(p => orgOrderNumber).ToList(); lstOrgOrderNumber = lstOrgOrderNumber.Where(p => p != 0).ToList(); var catOrgIDs = repoCat_OrgStructure.FindBy(p => p.IsDelete == null && lstOrgOrderNumber.Contains(p.OrderNumber)).Select(p => p.ID).ToList(); var lstProfile = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, p => catOrgIDs.Contains(p.OrgStructureID ?? Guid.Empty)).Select(p => new { p.ProfileName, p.ID, p.DateHire, p.CodeEmp, p.SocialInsNo, p.DateQuit, p.SalaryClassID, }).ToList(); #endregion lstProfile = lstProfile.Where(p => lstProfileIDs.Contains(p.ID)).ToList(); if (!string.IsNullOrEmpty(codeEmp)) { lstProfile = lstProfile.Where(p => p.CodeEmp == codeEmp).ToList(); } if (isProfileQuit.HasValue && isProfileQuit.Value) { //Nv Nghỉ Việc lstProfile = lstProfile.Where(p => p.DateQuit.HasValue).ToList(); } else { //NV đang làm việc lstProfile = lstProfile.Where(p => p.DateQuit == null).ToList(); } #endregion //ds profileId theo phong ban lstProfileIDs = lstProfile.Select(m => m.ID).ToList(); #region thiet lap column DtInsuranceTrackingMonthly = GetReportNotHaveSocialSchema(dateFrom, dateTo); #endregion if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any()) { socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList(); lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList(); } DataRow Row; try { List<Guid> lstProfileIDTotal = lstProfile.Select(m => m.ID).ToList(); foreach (var lstProfileID in lstProfileIDTotal.Chunk(500)) { lstProfile = lstProfile.Where(p => lstProfileID.Contains(p.ID)).ToList(); lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(p => p.ProfileID.HasValue && lstProfileID.Contains(p.ProfileID.Value)).ToList(); foreach (var profile in lstProfile) { var insuranceByProfiles = lstProfileInsuranceMonthlyInDb .Where(m => m.ProfileID == profile.ID).ToList(); if (!insuranceByProfiles.Any()) { continue; } Row = DtInsuranceTrackingMonthly.NewRow(); for (var i = dateFrom; i <= dateTo; i = i.AddMonths(1)) { var lst = insuranceByProfiles.Where(m => m.MonthYearEffect.HasValue && m.MonthYearEffect.Value.Year == i.Year && m.MonthYearEffect.Value.Month == i.Month).OrderByDescending(m => m.MonthYearEffect).ToList(); var insuranceByProfile = lst.FirstOrDefault(); if (insuranceByProfile == null) { continue; } if (insuranceByProfile.MonthYearEffect.HasValue && insuranceByProfile.MonthYearEffect.Value == i) { var totalInsurancePerProfile = lst.Sum(m=>m.SalaryInsurance); if (insuranceByProfile.IsPregnant != null && insuranceByProfile.IsPregnant.Value) { Row[i.ToString(MONTHYEAR)] = ConstantDisplay.HRM_Ins_ReportEmpHardJob_PregnantLeave.TranslateString(); } if (insuranceByProfile.IsDecreaseWorkingDays.HasValue && insuranceByProfile.IsDecreaseWorkingDays.Value && (insuranceByProfile.IsPregnant == null || !insuranceByProfile.IsPregnant.Value)) { Row[i.ToString(MONTHYEAR)] = ConstantDisplay.HRM_Ins_ReportEmpHardJob_DecreaseWorkingDays.TranslateString(); } else if (totalInsurancePerProfile > 0) { if (profile.DateQuit.HasValue && profile.DateQuit.Value <= i) { Row[i.ToString(MONTHYEAR)] = DBNull.Value; } else { Row[i.ToString(MONTHYEAR)] = String.Format("{0:#,###,###.##}", totalInsurancePerProfile); } } } } Row[ConstantDisplay.HRM_HR_Profile_DateHire] = profile.DateHire; Row[ConstantDisplay.HRM_HR_Profile_CodeEmp] = profile.CodeEmp; Row[ConstantDisplay.HRM_HR_Profile_ProfileName] = profile.ProfileName; Row[ConstantDisplay.HRM_HR_Profile_SocialInsNo] = profile.SocialInsNo; #region lấy mã lương theo nhan vien var salaryClassProfileObj = salaryClass.Where(p => p.ID == profile.SalaryClassID).FirstOrDefault(); if (salaryClassProfileObj != null) { Row[ConstantDisplay.HRM_HR_Profile_SalaryClassID] = salaryClassProfileObj.Code; } #endregion //them 1 row DtInsuranceTrackingMonthly.Rows.Add(Row); } } } catch { return null; } } var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); config.Add("width", 80); configs.Add(ConstantDisplay.HRM_HR_Profile_DateHire.TranslateString(), config); return DtInsuranceTrackingMonthly.ConfigTable(configs); }