void SetData(SLDocument document, EmployeeReportViewModel reportVm) { for (int i = 0; i < reportVm.Rows.Count; i++) { var currentRow = reportVm.Rows[i]; int rownum = i + 2; //document.SetCellValue(rownum, 1, currentRow.Name); //document.SetCellValue(rownum, 2, currentRow.Project); //document.SetCellValue(rownum, 3, currentRow.WorkObject); document.SetCellValue(rownum, 1, currentRow.Name); document.SetCellValue(rownum, 2, currentRow.WorkObject); document.SetCellValue(rownum, 3, currentRow.ProjectCode); document.SetCellValue(rownum, 4, currentRow.Project); for (int j = 0; j < currentRow.Hours.Count; j++) { document.SetCellValue(rownum, j + 5, currentRow.Hours[j]); } document.SetCellValue(rownum, reportVm.MyColumns.Count + 5, currentRow.TotalHours); document.SetCellValue(rownum, reportVm.MyColumns.Count + 6, currentRow.Money); } }
public string CreateFile(EmployeeReportViewModel reportVm) { string path = AppDomain.CurrentDomain.BaseDirectory + "Content/"; string fileName = Path.GetRandomFileName();//"Отчёт по сотрудникам.xlsx"; string fullPath = path + fileName; SLDocument sl = new SLDocument(); // set a boolean at "A1" //sl.SetCellValue("A1", true); SetColumns(sl, reportVm); SetData(sl, reportVm); try { if (File.Exists(fullPath)) { File.Delete(fullPath); } } catch (Exception) { var random = new Random(); fullPath = path + random.Next() + "_" + fileName; } sl.SaveAs(fullPath); return(fullPath); }
// GET: Document/EmployeeReport/{id} public ActionResult EmployeeReport(int?id) { if (id == null) { return(new HttpStatusCodeResult(HttpStatusCode.BadRequest)); } Company company = db.Companies.Find(id); if (company == null) { return(HttpNotFound()); } db.Entry(company).Collection(c => c.Products).Load(); db.Entry(company).Collection(c => c.CompanyReports).Load(); db.Entry(company).Collection(c => c.Employees).Load(); foreach (var product in company.Products) { db.Entry(product).Collection(p => p.ExistingProducts).Load(); db.Entry(product).Collection(p => p.ProductRatings).Load(); db.Entry(product).Collection(p => p.ProductComments).Load(); } var vm = EmployeeReportViewModel.CreateViewModel(company, DateTime.Now.Date); return(View(vm)); }
void SetColumns(SLDocument document, EmployeeReportViewModel reportVm) { SLStyle style = document.CreateStyle(); style.SetVerticalAlignment(VerticalAlignmentValues.Center); style.Alignment.Horizontal = HorizontalAlignmentValues.Left; style.Alignment.ReadingOrder = SLAlignmentReadingOrderValues.LeftToRight; style.Alignment.ShrinkToFit = true; style.Alignment.TextRotation = 90; //document.SetCellValue(1, 1, "Имя"); //document.SetCellValue(1, 2, "Проект"); //document.SetCellValue(1, 3, "Объект"); document.SetCellValue(1, 1, "Имя"); document.SetCellValue(1, 2, "Объект"); document.SetCellValue(1, 3, "Код проекта"); document.SetCellValue(1, 4, "Название проекта"); document.SetColumnWidth(1, 17); document.SetColumnWidth(2, 20); document.SetColumnWidth(3, 17); document.SetColumnWidth(4, 20); for (int i = 0; i < reportVm.MyColumns.Count; i++) { document.SetCellValue(1, i + 5, reportVm.MyColumns[i]); document.SetColumnWidth(i + 5, 4); // style.SetCellStyle(); document.SetCellStyle(1, i + 5, style); } document.SetCellValue(1, reportVm.MyColumns.Count + 5, "Всего часов"); document.SetColumnWidth(reportVm.MyColumns.Count + 5, 17); document.SetCellValue(1, reportVm.MyColumns.Count + 6, "Деньги"); }
public EmployeeReportViewModel GetData() { var result = new EmployeeReportViewModel(); var span = DateEnd - DateBegin; int countDays = span.Days; result.MyColumns = new List <string>(); for (int i = 0; i < countDays; i++) { result.MyColumns.Add(DateBegin.AddDays(i).ToShortDateString()); } var timesheetsInterval = _tabelContext.Timesheets.Include("Employee") .Include("Project") .Where(ts => ts.Date >= DateBegin && ts.Date < DateEnd); var neededData = timesheetsInterval.Select(ts => new { EmployeeName = ts.Employee.Name, ProjectName = ts.Project.Name, ProjectCode = ts.Project.Code, Rate = ts.Employee.Rate, ts.Project.WorkObject, ts.Hours, ts.Date }); List <DateTime> reportDays = new List <DateTime>(); for (int i = 0; i < countDays; i++) { reportDays.Add(DateBegin.AddDays(i)); } var allEmployees = neededData.Select(data => data.EmployeeName).Distinct(); var employeeProjectDict = new Dictionary <string, List <string> >(); foreach (var employee in allEmployees) { employeeProjectDict[employee] = neededData.Where(data => data.EmployeeName == employee).Select(data => data.ProjectName).Distinct().ToList(); } //Заполняем тут данные о сотрудниках и проектах List <ErReportEmployee> reportEmployee = new List <ErReportEmployee>(); foreach (var employeeProject in employeeProjectDict) { var newReportEmploy = new ErReportEmployee { EmployeeName = employeeProject.Key, ReportProject = new List <ErReportProject>() }; foreach (var project in employeeProject.Value) { newReportEmploy.ReportProject.Add(new ErReportProject() { ProjectName = project, ProjectCode = neededData.Where(data => data.ProjectName == project).Select(data => data.ProjectCode).FirstOrDefault() }); } newReportEmploy.SetBeginAndEndDate(DateBegin, DateEnd); reportEmployee.Add(newReportEmploy); } //Записываем данные о часах foreach (var employee in reportEmployee) { foreach (var project in employee.ReportProject) { var workingHours = neededData.Where( data => data.EmployeeName == employee.EmployeeName && data.ProjectName == project.ProjectName) .Select(data => new { WorkDate = data.Date, WorkHour = data.Hours, data.WorkObject, }).ToList(); project.WorkObject = workingHours.First().WorkObject; foreach (var workingHour in workingHours) { var roundedDate = new DateTime(workingHour.WorkDate.Year, workingHour.WorkDate.Month, workingHour.WorkDate.Day); project.Hours[roundedDate] = workingHour.WorkHour; } } } //Записываем данные о зарплате foreach (var employee in reportEmployee) { //var employees = reportEmployee.Where(data => data.EmployeeName == employee.EmployeeName); employee.Rate = neededData.Where(data => data.EmployeeName == employee.EmployeeName) .Select(data => data.Rate) .FirstOrDefault(); } //Переделываем в формат грида result.Rows = new List <ErEmployeeViewModel>(); var totalHoursDict = new Dictionary <DateTime, int>(); var totalMoney = 0.0; for (int i = 0; i < countDays; i++) { totalHoursDict[DateBegin.AddDays(i)] = 0; } foreach (var reportEmploy in reportEmployee) { var hoursDict = new Dictionary <DateTime, int>(); for (int i = 0; i < countDays; i++) { hoursDict[DateBegin.AddDays(i)] = 0; } foreach (var reportProject in reportEmploy.ReportProject) { var newEmployee = new ErEmployeeViewModel { Name = reportEmploy.EmployeeName, Rate = reportEmploy.Rate, Project = reportProject.ProjectName, ProjectCode = reportProject.ProjectCode, WorkObject = reportProject.WorkObject, Hours = reportProject.Hours.Values.ToList() }; result.Rows.Add(newEmployee); totalMoney += newEmployee.Money; foreach (var hour in reportProject.Hours) { if (!hoursDict.ContainsKey(hour.Key)) { hoursDict[hour.Key] = 0; } hoursDict[hour.Key] += hour.Value; if (!totalHoursDict.ContainsKey(hour.Key)) { totalHoursDict[hour.Key] = 0; } totalHoursDict[hour.Key] += hour.Value; } } result.Rows.Add(new ErEmployeeViewModel { Hours = hoursDict.Values.ToList(), Name = reportEmploy.EmployeeName + " Итого", //Project = "По всем проектам", //WorkObject = "По всем объектам", Rate = reportEmploy.Rate }); } result.Rows = result.Rows.OrderBy(row => row.Name).ThenBy(then => then.ProjectCode).ToList(); result.Employees = allEmployees.ToList(); result.Employees.Insert(0, "Все"); //result.Rows.Add(new ErEmployeeViewModel(totalMoney) //{ // Hours = totalHoursDict.Values.ToList(), // Name = "Итого", // // Project = "По всем проектам" //}); if (!string.IsNullOrEmpty(EmployeeFilter) && EmployeeFilter != "Все") { result.Rows.RemoveAll(item => item.Name != EmployeeFilter); } return(result); }