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);
}
