public string GetADEmployeeIDByEmployeeIDInDB(int id) { string userADEmployeeID = ""; Employee employee = RepositoryFactory.GetRepository <IEmployeeRepository>().GetById(id); if (employee != null) { if (String.IsNullOrEmpty(employee.ADEmployeeID) == false) { userADEmployeeID = employee.ADEmployeeID; } else { userADEmployeeID = ADHelper.GetADEmployeeIDBySearchStringInAD(RPCSHelper.NormalizeAndTrimString(employee.FullName)); } } return(userADEmployeeID); }
public FileContentResult ExportApproveHoursToExcel(string hoursStartDate, string hoursEndDate, string projectId, TSRecordStatus?tsRecordStatus) { //Получение пользователя int userEmployeeID = _userService.GetEmployeeForCurrentUser().ID; int intProjectId; DateTime startDate = DateTime.Today; DateTime endDate = DateTime.Today; DateTime.TryParse(hoursStartDate, out startDate); DateTime.TryParse(hoursEndDate, out endDate); startDate = startDate == DateTime.MinValue ? DateTime.MinValue : startDate; endDate = endDate == DateTime.MinValue ? DateTime.MaxValue : endDate; int.TryParse(projectId, out intProjectId); var tsHoursRecordStatus = TSRecordStatus.All; if (tsRecordStatus != null) { tsHoursRecordStatus = (TSRecordStatus)tsRecordStatus; } var recordList = _tsHoursRecordService.GetTSRecordsForApproval(userEmployeeID, startDate, endDate, intProjectId, tsHoursRecordStatus); var projectsFullName = recordList.Select(x => x.Project.ShortName) .GroupBy(x => x).Select(group => group.FirstOrDefault()).OrderBy(x => x).ToList(); var listEmployeesId = recordList.GroupBy(x => x.EmployeeID) .Select(x => x.Key).ToList(); var listEmployees = _employeeService.GetCurrentEmployees(new DateTimeRange(startDate, endDate)).Where(x => listEmployeesId.Any(y => y.Value == x.ID)).OrderBy(x => x.LastName); var projectList = string.Join(",", projectsFullName); byte[] binData = null; DataTable dataTable = new DataTable(); dataTable.Columns.Add("EmployeeFullName", typeof(string)).Caption = "Сотрудник"; dataTable.Columns["EmployeeFullName"].ExtendedProperties["Width"] = (double)40; dataTable.Columns.Add("RecordDate", typeof(DateTime)).Caption = "Отчетная дата"; dataTable.Columns["RecordDate"].ExtendedProperties["Width"] = (double)12; dataTable.Columns.Add("Project", typeof(string)).Caption = "Проект"; dataTable.Columns["Project"].ExtendedProperties["Width"] = (double)40; dataTable.Columns.Add("Hours", typeof(double)).Caption = "Трудозатраты (ч)"; dataTable.Columns["Hours"].ExtendedProperties["Width"] = (double)15; dataTable.Columns.Add("Description", typeof(string)).Caption = "Состав работ"; dataTable.Columns["Description"].ExtendedProperties["Width"] = (double)90; dataTable.Columns.Add("RecordStatus", typeof(string)).Caption = "Статус"; dataTable.Columns["RecordStatus"].ExtendedProperties["Width"] = (double)20; //Может быть много сотрудников, для созгласования трудозатрат foreach (var employee in listEmployees) { foreach (var employeeHoursRecord in recordList) { if (employeeHoursRecord.EmployeeID == employee.ID) { dataTable.Rows.Add(employee.FullName, employeeHoursRecord.RecordDate.Value, employeeHoursRecord.Project.ShortName, employeeHoursRecord.Hours, RPCSHelper.NormalizeAndTrimString(employeeHoursRecord.Description), employeeHoursRecord.RecordStatus.GetAttributeOfType <DisplayAttribute>().Name); } } } using (MemoryStream stream = new MemoryStream()) { using (SpreadsheetDocument doc = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = ExcelHelper.CreateWorkbookPart(doc, "Трудозатраты сотрудников"); WorksheetPart rId1 = ExcelHelper.CreateWorksheetPartAndImportDataTable(workbookPart, "rId1", 1, 1, (uint)dataTable.Columns.Count, "Трудозатраты по проектам: " + projectList + ", на даты: " + hoursStartDate + " - " + hoursEndDate + ", со статусом: " + tsRecordStatus.GetAttributeOfType <DisplayAttribute>().Name, dataTable, 3, 1); doc.WorkbookPart.Workbook.Save(); } stream.Position = 0; BinaryReader b = new BinaryReader(stream); binData = b.ReadBytes((int)stream.Length); } return(File(binData, ExcelHelper.ExcelContentType, "ApproveHours" + DateTime.Now.ToString("ddMMyyHHmmss") + ".xlsx")); }