public TimesheetImportHoursFromExcelResult ProcessLongRunningAction(string userIdentityName, string fileId, DataTable timesheetHoursRecordSheetDataTable, int reportMonth,
int reportHoursInMonth, int reportYear, bool onlyValidate, bool rewriteTSHoursRecords, string currentUserName, string currentUserSID)
{
var htmlReport = string.Empty;
var htmlErrorReport = string.Empty;
taskId = fileId;
LongRunningTaskReport report = null;
try
{
SetStatus(0, "Старт загрузки...");
SetStatus(1, "Обработка файла Excel");
report = ImportTSHoursRecords(timesheetHoursRecordSheetDataTable, reportMonth, reportYear, reportHoursInMonth, onlyValidate, rewriteTSHoursRecords, currentUserName, currentUserSID);
SetStatus(100, "Загрузка завершена");
}
catch (Exception e)
{
SetStatus(-1, "Ошибка: " + e.Message);
htmlErrorReport += "<br>" + e.Message + "<br>" + e.StackTrace + "<br>" + e.TargetSite;
}
try
{
if (report != null)
{
htmlReport = report.GenerateHtmlReport();
}
}
catch (Exception e)
{
SetStatus(-1, "Ошибка: " + e.Message);
htmlErrorReport += "<br>" + e.Message + "<br>" + e.StackTrace + "<br>" + e.TargetSite.ToString();
}
return(new TimesheetImportHoursFromExcelResult()
{
userInitiationgReport = userIdentityName,
fileId = fileId,
fileHtmlReport = new List <string>()
{
htmlReport, htmlErrorReport
}
});
}
public BudgetLimitImportRecordsFromExcelResult ProcessLongRunningAction(string userIdentityName, string fileId, DataTable budgetLimitTable, int reportYear, bool onlyValidate)
{
var htmlReport = string.Empty;
var htmlErrorReport = string.Empty;
taskId = fileId;
LongRunningTaskReport report = null;
var execludedColumns = new string[] { "Income", "Total" };
try
{
SetStatus(0, "Старт загрузки...");
SetStatus(1, "Обработка файла Excel");
report = ImportBudgetLimitRecords(budgetLimitTable, reportYear, onlyValidate, execludedColumns);
SetStatus(100, "Загрузка завершена");
}
catch (Exception e)
{
SetStatus(-1, "Ошибка: " + e.Message);
htmlErrorReport += "<br>" + e.Message + "<br>" + e.StackTrace + "<br>" + e.TargetSite;
}
try
{
if (report != null)
{
htmlReport = report.GenerateHtmlReport();
}
}
catch (Exception e)
{
SetStatus(-1, "Ошибка: " + e.Message);
htmlErrorReport += "<br>" + e.Message + "<br>" + e.StackTrace + "<br>" + e.TargetSite.ToString();
}
return(new BudgetLimitImportRecordsFromExcelResult()
{
UserInitiationgReport = userIdentityName,
FileId = fileId,
FileHtmlReport = new List <string>()
{
htmlReport, htmlErrorReport
}
});
}
public DBDataProcessingTaskResult ProcessLongRunningAction(string userIdentityName, string id)
{
var htmlReport = string.Empty;
var htmlErrorReport = string.Empty;
taskId = id;
taskReport = new LongRunningTaskReport("Отчет о обработки данных с БД", "");
SetStatus(1, "Обновление данных с БД - старт", true);
try
{
ProcessEmployee();
SetStatus(100, "Синхронизация завершена", true);
try
{
if (taskReport != null)
{
htmlReport = taskReport.GenerateHtmlReport();
}
}
catch (Exception ex)
{
SetStatus(-1, "Ошибка: " + ex.Message);
htmlErrorReport += "<br>" + ex.Message + "<br>" + ex.StackTrace + "<br>" + ex.TargetSite.ToString();
}
}
catch (Exception ex)
{
SetStatus(-1, "Ошибка: " + ex.Message);
htmlErrorReport += "<br>" + ex.Message + "<br>" + ex.StackTrace + "<br>" + ex.TargetSite.ToString();
}
return(new DBDataProcessingTaskResult()
{
fileId = id, fileHtmlReport = new List <string>()
{
htmlReport, htmlErrorReport
}
});
}
private LongRunningTaskReport ImportBudgetLimitRecords(DataTable budgetLimitTable, int year, bool onlyValidate, string[] execludedColumns)
{
LongRunningTaskReport report = new LongRunningTaskReport("Отчет о загрузке данных о лимитах за месяц", "");
Dictionary <string, int> mounthDict = GetMounth();
double countRow = budgetLimitTable.Rows.Count;
for (int i = 0; i < budgetLimitTable.Rows.Count; i++)
{
int percent = i == 0 ? 1 : (int)((i / countRow) * 100);
SetStatus(percent, "Обработано данных");
var row = budgetLimitTable.Rows[i];
var section = row["Section"].ToString();
if (!section.Trim().ToLower().Equals("цфо", StringComparison.OrdinalIgnoreCase))
{
continue;
}
var costSubItemShortName = row["CostSubItemShortName"].ToString();
var departmentShortTitle = row["DepartmentShortTitle"].ToString();
BudgetLimit[] budgetLimitArrayFromDataTable = Deserialize(row, mounthDict, year);
foreach (var budgetLimit in budgetLimitArrayFromDataTable)
{
var results = _budgetLimitService
.Get(query => query
.Where(b => b.Month == budgetLimit.Month &&
b.Year == budgetLimit.Year &&
b.CostSubItem.ShortName.Equals(costSubItemShortName.Trim(), StringComparison.OrdinalIgnoreCase) &&
b.Department.ShortTitle.Equals(departmentShortTitle.Trim(), StringComparison.OrdinalIgnoreCase))
.ToList());
if (results.Count == 0) // добавляем
{
var costSubItem = _costSubItemService.Get(query => query.Where(c => c.ShortName.Equals(costSubItemShortName.Trim(), StringComparison.OrdinalIgnoreCase)).ToList()).FirstOrDefault();
if (costSubItem == null)
{
if (!onlyValidate)
{
report.AddReportEvent($"Не удалось добавить лимит: {budgetLimit.LimitAmount}, на месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}, не найдена подстатья: {costSubItemShortName}");
}
else
{
report.AddReportEvent($"Не найдена подстатья: {costSubItemShortName}, для лимита: {budgetLimit.LimitAmount}, на месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}");
}
continue;
}
var department = _departmentService.Get(query => query.Where(d => d.ShortTitle.Equals(departmentShortTitle.Trim(), StringComparison.OrdinalIgnoreCase)).ToList()).FirstOrDefault();
if (department == null)
{
if (!onlyValidate)
{
report.AddReportEvent($"Не удалось добавить лимит: {budgetLimit.LimitAmount}, на месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}, не найдено подразделение: {departmentShortTitle}");
}
else
{
report.AddReportEvent($"Не найдено подразделение: {departmentShortTitle}, для лимита: {budgetLimit.LimitAmount}, на месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}");
}
continue;
}
budgetLimit.CostSubItemID = costSubItem.ID;
budgetLimit.DepartmentID = department.ID;
if (!onlyValidate)
{
var limitDto = _budgetLimitService.GetLimitData(budgetLimit.CostSubItemID.Value, budgetLimit.DepartmentID.Value, budgetLimit.ProjectID, budgetLimit.Year.Value, budgetLimit.Month.Value);
if (limitDto != null)
{
budgetLimit.LimitAmountApproved = limitDto.LimitAmountReserved;
budgetLimit.FundsExpendedAmount = limitDto.LimitAmountActuallySpent;
}
else
{
budgetLimit.LimitAmountApproved = 0;
budgetLimit.FundsExpendedAmount = 0;
}
_budgetLimitService.Add(budgetLimit);
report.AddReportEvent($"Добавлен лимит: {budgetLimit.LimitAmount}, на месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}, подстатья: {costSubItemShortName}, подразделение: {departmentShortTitle}");
}
else
{
report.AddReportEvent($"Лимит еще не создан: {budgetLimit.LimitAmount}, на месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}, подстатья: {costSubItemShortName}, подразделение: {departmentShortTitle}");
}
}
else // обновляем
{
if (!onlyValidate)
{
var budgetLimitFromDB = results.FirstOrDefault();
decimal?limitAmountOldValue = budgetLimitFromDB.LimitAmount;
if (budgetLimitFromDB.LimitAmount != budgetLimit.LimitAmount.Value)
{
budgetLimitFromDB.LimitAmount = budgetLimit.LimitAmount.Value;
_budgetLimitService.Update(budgetLimitFromDB);
report.AddReportEvent($"Обновлен лимит: {budgetLimit.LimitAmount}, (старое значение: {limitAmountOldValue}), месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}, подстатья: {costSubItemShortName}, подразделение: {departmentShortTitle}");
}
else
{
report.AddReportEvent($"Не изменилось значение лимита: {budgetLimit.LimitAmount}, месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}, подстатья: {costSubItemShortName}, подразделение: {departmentShortTitle}");
}
}
else
{
report.AddReportEvent($"Найден лимит: {results.FirstOrDefault().LimitAmount}, (новое значение: {budgetLimit.LimitAmount}), месяц: {CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(budgetLimit.Month.Value)}, подстатья: {costSubItemShortName}, подразделение: {departmentShortTitle}");
}
}
}
}
return(report);
}
private LongRunningTaskReport ImportTSHoursRecords(DataTable timesheetHoursRecordSheetDataTable, int reportMonth, int reportHoursInMonth,
int reportYear, bool onlyValidate, bool rewriteTSHoursRecords, string currentUserName, string currentUserSID)
{
LongRunningTaskReport report = new LongRunningTaskReport("Отчет о загрузке трудозатрат за месяц", "");
var dateNow = DateTime.Now;
var countEmployee = 0;
DateTime recordDate = _productionCalendarService.GetLastWorkDayInMonth(reportYear, reportMonth).CalendarDate;
Hashtable projectIdsByShortName = new Hashtable();
for (int k = 1; k <= timesheetHoursRecordSheetDataTable.Columns.Count - 1; k++)
{
try
{
var projectShortName = timesheetHoursRecordSheetDataTable.Rows[0][k].ToString().Trim();
Project project = _projectService.GetByShortName(projectShortName);
if (project != null)
{
projectIdsByShortName[project.ShortName] = project.ID;
}
else
{
report.AddReportEvent("Проект: " + projectShortName + " не найден в БД Проекты");
}
}
catch (Exception)
{
}
}
List <TSHoursRecord> existTSHoursRecords = _tsHoursRecordService.GetAll(x => x.RecordSource == TSRecordSource.ExcelImportByMonth &&
x.RecordDate == recordDate).ToList();
for (int i = 1; i <= timesheetHoursRecordSheetDataTable.Rows.Count - 1; i++)
{
var employeeFullName = timesheetHoursRecordSheetDataTable.Rows[i][0].ToString().Trim();
if (!string.IsNullOrEmpty(employeeFullName))
{
SetStatus(60 + countEmployee * 39 / timesheetHoursRecordSheetDataTable.Rows.Count, "Обработка записей по сотруднику: " + employeeFullName);
Employee employee = _employeeService.FindEmployeeByFullName(employeeFullName);
if (employee != null)
{
if (onlyValidate == false)
{
for (int j = 1; j <= timesheetHoursRecordSheetDataTable.Columns.Count - 1; j++)
{
try
{
var projectShortName = timesheetHoursRecordSheetDataTable.Rows[0][j].ToString().Trim();
var hours = timesheetHoursRecordSheetDataTable.Rows[i][j].ToString().Trim();
SetStatus(60 + countEmployee * 39 / timesheetHoursRecordSheetDataTable.Rows.Count, "Обработка записи по сотруднику: " + employeeFullName + " на проект: " + projectShortName);
if (!string.IsNullOrEmpty(employeeFullName) && !string.IsNullOrEmpty(hours))
{
if (projectIdsByShortName.ContainsKey(projectShortName))
{
int projectID = Convert.ToInt32(projectIdsByShortName[projectShortName]);
var isHours = double.TryParse(hours.Replace('.', ','), out double doubleHours);
if (isHours == false)
{
report.AddReportEvent("Для сотрудника: " + employeeFullName + " на проект: " + projectShortName + " значение часов - не число.");
}
else
{
//Удаление записи если уставновлена галочка
if (rewriteTSHoursRecords)
{
var tsHoursRecord = existTSHoursRecords.Where(x =>
x.EmployeeID == employee.ID
/*&& x.RecordSource == TSRecordSource.ExcelImportByMonth*/
&& x.ProjectID == projectID
/*&& x.RecordDate == recordDate*/)
.FirstOrDefault();
if (tsHoursRecord != null)
{
_tsHoursRecordService.Delete(tsHoursRecord.ID);
report.AddReportEvent(
"Успешное удаление записи трудозатрат для сотрудника: " + employeeFullName + ", проект: " +
projectShortName + ", часы: " + tsHoursRecord.Hours);
}
}
if (doubleHours != 0)
{
_tsHoursRecordService.Add(
new TSHoursRecord()
{
EmployeeID = employee.ID,
ProjectID = projectID,
RecordDate = recordDate,
Hours = doubleHours,
Description = "Импорт трудозатрат из Excel: " + dateNow,
RecordStatus = TSRecordStatus.PMApproved,
RecordSource = TSRecordSource.ExcelImportByMonth
}, currentUserName, currentUserSID);
report.AddReportEvent("Запись добавлена. Сотрудник: " + employeeFullName + ", проект: " + projectShortName + ", часы: " + doubleHours);
if (doubleHours > reportHoursInMonth)
{
report.AddReportEvent("Указанное количество часов для " + employeeFullName + " на проект: " + projectShortName + " превышает количество рабочих часов в месяце.");
}
}
}
}
}
}
catch (Exception e)
{
throw e;
}
}
}
}
else
{
report.AddReportEvent("Сотрудник: " + employeeFullName + " не найден в БД Сотрудники");
}
countEmployee++;
}
}
return(report);
}
