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