// GET: ProjectReport
public ActionResult Index(string id)
{
var projectId = ObjectId.Parse(id);
var model = new ProjectReportViewModel(projectId);
return(View(model));
}
void SetData(SLDocument document, ProjectReportViewModel reportVm)
{
for (int i = 0; i < reportVm.Rows.Count; i++)
{
var currentRow = reportVm.Rows[i];
int rownum = i + 2;
document.SetCellValue(rownum, 1, currentRow.WorkObject);
document.SetCellValue(rownum, 2, currentRow.ProjectCode);
document.SetCellValue(rownum, 3, currentRow.ProjectName);
document.SetCellValue(rownum, 4, currentRow.Name);
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(ProjectReportViewModel 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);
}
public void UpdateReportNotAccessByLoginIdRptId(ProjectReportViewModel model)
{
var strsql_update = "delete from report_not_access ";
strsql_update = strsql_update + " where login_id = '" + model.Admin_login_id + "' and fmrs_system = '" + model.Modules + "'";
var sql = "select id, rpt_name, g.display_order from report r, report_group g";
sql = sql + " where group_id = " + model.Group_id;
sql = sql + " and report_id = id ";
sql = sql + " and hosp_ind = 'Y' ";
sql = sql + " order by g.display_order ";
string report_id = "";
int access_right_cnt = 0;
DataSet ds = new DataSet();
using (SqlConnection sqlConn = new SqlConnection(Context.Database.GetDbConnection().ConnectionString))
{
using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
{
SqlDataAdapter adp = new SqlDataAdapter(sqlCmd);
adp.Fill(ds);
if (ds != null && ds.Tables.Count != 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
report_id = dr["id"].ToString();
int st = model.Access_right.IndexOf("Access_right_" + report_id + ":") + 14 + report_id.Length + 1;
access_right_cnt = Convert.ToInt32(model.Access_right.Substring(st, 1));
if (access_right_cnt == 0)
{
strsql_update = strsql_update + "insert into report_not_access ";
strsql_update = strsql_update + " (login_id, report_id, fmrs_system) ";
strsql_update = strsql_update + " values ";
strsql_update = strsql_update + "('" + model.Admin_login_id + "', " + report_id + ", '" + model.Modules + "')";
}
}
}
}
}
using (SqlConnection sqlConn = new SqlConnection(Context.Database.GetDbConnection().ConnectionString))
{
using (SqlCommand sqlCmd = new SqlCommand(strsql_update, sqlConn))
{
sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
}
}
}
public IActionResult UpdateProjectReport(ProjectReportViewModel model)
{
try
{
AdminService.UpdateProjectReport(model);
TempData["SuccessMessage"] = Resource.SaveSuccess;
return(RedirectToAction("ProjectReport", new { modules = model.Modules, admin_login_id = model.Admin_login_id, group_id = model.Group_id }));
}
catch (Exception ex)
{
TempData["ErrorMessage"] = Resource.SaveFail + ex;
return(RedirectToAction("ProjectReport", new { modules = model.Modules, admin_login_id = model.Admin_login_id, group_id = model.Group_id }));
}
}
public IActionResult ProjectReport(string modules, string admin_login_id, int group_id)
{
Session.SetString("current_sys", modules);
var user_group = GetUserGroup(modules);
SetMenuDropDownList(user_group);
ViewBag.IsHtmlMessage = true;
ViewBag.SuccessMessage = TempData["SuccessMessage"] != null ? TempData["SuccessMessage"] : ViewBag.SuccessMessage;
ViewBag.ErrorMessage = TempData["ErrorMessage"] != null ? TempData["ErrorMessage"] : ViewBag.ErrorMessage;
ProjectReportViewModel model = new ProjectReportViewModel();
model.Report_list = AdminService.GetProjectReportList(modules, admin_login_id, group_id);
model.Admin_login_id = admin_login_id;
model.Group_id = group_id;
model.Modules = modules;
return(View(model));
}
void SetColumns(SLDocument document, ProjectReportViewModel 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, 4, "Имя");
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, 17);
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 async Task <IActionResult> GetProjectReport(int id)
{
ProjectReport pr = await _projectReportRepository.Get(id);
IEnumerable <WorkPackageReportSnapshot> allWPRS = await _workPackageReportSnapshotRepository.GetAll();
IEnumerable <WorkPackageReportSnapshot> lowWPRS = allWPRS.Where(x => x.ProjectReportId == id && !x.IsHighWorkPackage); //lowWorkPackage
IEnumerable <WorkPackageReportSnapshot> highWPRS = allWPRS.Where(x => x.ProjectReportId == id && x.IsHighWorkPackage); //highWP
IEnumerable <EmployeeProjectAssignment> allEmpProjectAssignments = await _employeeProjectAssignmentRepository.GetAll();
IEnumerable <EmployeeProjectAssignment> projectAssignments = allEmpProjectAssignments.Where(x => x.ProjectId == pr.ProjectId);
IEnumerable <Employee> employees = await _employeeRepository.GetAll();
List <EmployeeNameViewModel> engineers = new List <EmployeeNameViewModel>();
EmployeeNameViewModel pm = null;
foreach (EmployeeProjectAssignment e in projectAssignments)
{
if (e.IsProjectManager)
{
pm = new EmployeeNameViewModel(employees.Where(x => x.EmployeeId == e.EmployeeId).FirstOrDefault());
}
else
{
engineers.Add(new EmployeeNameViewModel(employees.Where(x => x.EmployeeId == e.EmployeeId).FirstOrDefault()));
}
}
ProjectReportViewModel projectReportView = new ProjectReportViewModel(pr, pm, engineers, lowWPRS.ToList(), highWPRS.ToList());
return(Ok(projectReportView));
}
public void UpdateProjectReport(ProjectReportViewModel model)
{
ReportNotAccessRepository.UpdateReportNotAccessByLoginIdRptId(model);
}
public ProjectReportViewModel GetData()
{
var result = new ProjectReportViewModel();
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 allProjects = neededData.Select(data => data.ProjectCode).Distinct();
var projectEmployeeDict = new Dictionary <string, List <string> >();
//у этого проекта такие то сотрудники
foreach (var project in allProjects)
{
projectEmployeeDict[project] = neededData.Where(data => data.ProjectCode == project).Select(data => data.EmployeeName).Distinct().ToList();
}
//Заполняем тут данные о сотрудниках и проектах
List <PrReportProject> reportProject = new List <PrReportProject>();
foreach (var projectEmployee in projectEmployeeDict)
{
var newReportProject = new PrReportProject
{
// ProjectName = projectEmployee.Key,
ProjectCode = projectEmployee.Key,
ReportEmployee = new List <PrReportEmployee>()
};
foreach (var employee in projectEmployee.Value)
{
newReportProject.ReportEmployee.Add(new PrReportEmployee()
{
EmployeeName = employee
});
}
newReportProject.SetBeginAndEndDate(DateBegin, DateEnd);
reportProject.Add(newReportProject);
}
//Записываем данные о часах
foreach (var project in reportProject)
{
foreach (var employee in project.ReportEmployee)
{
var workingHours =
neededData.Where(
data => data.EmployeeName == employee.EmployeeName && data.ProjectCode == project.ProjectCode)
.Select(data =>
new
{
WorkDate = data.Date,
WorkHour = data.Hours,
data.WorkObject,
data.ProjectCode
}).ToList();
project.WorkObject = workingHours.First().WorkObject;
project.ProjectCode = workingHours.First().ProjectCode;
foreach (var workingHour in workingHours)
{
var roundedDate = new DateTime(workingHour.WorkDate.Year, workingHour.WorkDate.Month, workingHour.WorkDate.Day);
employee.Hours[roundedDate] = workingHour.WorkHour;
}
}
}
//Записываем данные о зарплате НЕДОДЕЛКА
foreach (var project in reportProject)
{
foreach (var employee in project.ReportEmployee)
{
employee.Rate = neededData.Where(data => data.EmployeeName == employee.EmployeeName)
.Select(data => data.Rate)
.FirstOrDefault();
}
}
//Переделываем в формат грида
result.Rows = new List <PrProjectViewModel>();
var totalHoursDict = new Dictionary <DateTime, int>();
var totalMoney = 0.0;
for (int i = 0; i < countDays; i++)
{
totalHoursDict[DateBegin.AddDays(i)] = 0;
}
foreach (var reportPrj in reportProject)
{
var hoursDict = new Dictionary <DateTime, int>();
var neededProject =
neededData.FirstOrDefault(data => data.ProjectCode == reportPrj.ProjectCode);
if (neededProject != null)
{
reportPrj.ProjectName = neededProject.ProjectName;
}
for (int i = 0; i < countDays; i++)
{
hoursDict[DateBegin.AddDays(i)] = 0;
}
double projectMoney = 0;
foreach (var reportEmploy in reportPrj.ReportEmployee)
{
var newEmployee = new PrProjectViewModel
{
Name = reportEmploy.EmployeeName,
Rate = reportEmploy.Rate,
ProjectName = reportPrj.ProjectName,
WorkObject = reportPrj.WorkObject,
ProjectCode = reportPrj.ProjectCode,
Hours = reportEmploy.Hours.Values.ToList()
};
result.Rows.Add(newEmployee);
totalMoney += newEmployee.Money;
foreach (var hour in reportEmploy.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;
}
projectMoney += newEmployee.Money;
}
result.Rows = result.Rows.OrderBy(row => row.ProjectCode).ToList();
result.Rows.Add(new PrProjectViewModel(projectMoney)
{
Hours = hoursDict.Values.ToList(),
ProjectCode = reportPrj.ProjectCode + " Итого",
ProjectName = reportPrj.ProjectName + " Итого",
// Rate = reportPrj.Rate НЕДОДЕЛКА
});
}
result.Projects = allProjects.ToList();
result.Projects.Insert(0, "Все");
if (!string.IsNullOrEmpty(ProjectFilter) && ProjectFilter != "Все")
{
result.Rows.RemoveAll(item => item.ProjectCode != ProjectFilter);
}
return(result);
}
