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