public static PersonalReport GetPersonalReport(this UnitOfWork unit, int employeeId,
int year, int month, ModelFactory factory)
{
var emp = unit.Employees.Get().Where(x => x.Id == employeeId)
.ToList()
.Select(e => factory.Create(e))
.FirstOrDefault();
if (emp == null)
{
return(null);
}
var days = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year &&
x.Date.Month == month)
.Select(x => new PersonalReportDays()
{
Date = x.Date,
Type = x.Type.ToString(),
Comment = x.Comment,
Hours = x.Hours,
OvertimeHours = x.Hours - 8,
Tasks = x.Tasks.Select(y => new PersonalReportTask()
{
Hours = y.Hours,
Description = y.Description,
Project = y.Project.Name
})
.ToList()
})
.OrderBy(y => y.Date)
.ToList();
var workingDays = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.WorkingDay &&
x.Date.Year == year && x.Date.Month == month)
.Count();
var businessAbsence = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.BusinessAbsence &&
x.Date.Year == year && x.Date.Month == month)
.Count();
var otherAbsence = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.OtherAbsence &&
x.Date.Year == year && x.Date.Month == month)
.Count();
var publicHoliday = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.PublicHoliday &&
x.Date.Year == year && x.Date.Month == month)
.Count();
var religiousDay = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.ReligiousDay &&
x.Date.Year == year && x.Date.Month == month)
.Count();
var sickLeave = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.SickLeave &&
x.Date.Year == year && x.Date.Month == month)
.Count();
var vacation = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.Vacation &&
x.Date.Year == year && x.Date.Month == month)
.Count();
var totalHours = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.WorkingDay &&
x.Date.Year == year && x.Date.Month == month)
.Select(x => (int?)x.Hours)
.Sum() ?? 0;
var overtimeQuery = unit.Days.Get()
.Where(x => x.Employee.Id == employeeId && x.Type == DayType.WorkingDay &&
x.Date.Year == year && x.Date.Month == month &&
x.Hours > 8);
var workingDaysInMonth = noDaysInMonth(year, month);
int overtimeDays = overtimeQuery.Count();
decimal overtimeHoursSum = overtimeQuery.Select(x => (int?)x.Hours).Sum() ?? 0;
decimal overtimeHours = overtimeHoursSum - 8 * overtimeDays;
int missingEntries = Math.Abs(unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year && x.Date.Month == month)
.Count() - noDaysInMonth(year, month));
decimal utilization = Math.Round(((decimal)workingDays / (decimal)noDaysInMonth(year, month)) * 100, 2);
var report = new PersonalReport()
{
Employee = emp,
Days = days,
WorkingDays = workingDays,
BusinessAbscenceDays = businessAbsence,
OtherDays = otherAbsence,
PublicHolidayDay = publicHoliday,
ReligiousDays = religiousDay,
SickLeavesDays = sickLeave,
VacationDays = vacation,
WorkingDaysInMonth = workingDaysInMonth,
TotalHours = totalHours,
OvertimeHours = overtimeHours,
MissingEntries = missingEntries,
Utilization = utilization
};
return(report);
}
public static PersonalReport GetPersonalReport(this UnitOfWork unit, int employeeId, int year, int month, ModelFactory factory)
{
var emp = (FullEmployeeModel)unit.Employees.Get().Where(x => x.Id == employeeId).ToList()
.Select(x => factory.Create(x)).FirstOrDefault();
if (emp == null)
{
return(null);
}
decimal?overtime = unit.Engagements.Get().Select(x => x.Employee).SelectMany(y => y.Days).Where(x => x.Employee.Id == emp.Id &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month && x.Hours > 8)
.Select(x => x.Hours).DefaultIfEmpty(0).Sum();
int overtimeCount = unit.Engagements.Get().Select(x => x.Employee).SelectMany(y => y.Days)
.Where(x => x.Employee.Id == emp.Id &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month && x.Hours > 8).Select(x => x.Hours).Count();
overtime -= overtimeCount * 8;
var days = unit.Days.Get().Where(x => x.EmployeeId == employeeId && x.Date.Value.Year == year && x.Date.Value.Month == month)
.Select(x => new PersonalReportDay()
{
Id = x.Id,
Date = x.Date,
Type = x.Category.Description,
Comment = x.Comment,
Hours = x.Hours,
OvertimeHours = overtime,
Ordinal = x.Date.Value.Day,
Tasks = x.Assignments.Select(y => new PersonalReportTask()
{
Hours = y.Hours,
Description = y.Description,
Project = y.Project.Name
}).ToList()
}).OrderBy(y => y.Date).ToList();
int workingDays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month &&
x.Category.Description == "Working day").Count();
int vacationDays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month &&
x.Category.Description == "Vacation").Count();
int businessAbscenceDays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month &&
x.Category.Description == "Business Absence").Count();
int publicHolidays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month &&
x.Category.Description == "Public holiday").Count();
int sickLeaveDays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month &&
x.Category.Description == "Sick leave").Count();
int religiousDays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month &&
x.Category.Description == "Religious day").Count();
int otherAbsenceDays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month &&
x.Category.Description == "Other absence").Count();
decimal?totalhoursss = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Value.Year == year &&
x.Date.Value.Month == month && x.Category.Description == "Working day").Select(x => x.Hours).DefaultIfEmpty(0).Sum();
List <int> listDates = null;
var date = new DateTime(year, month, 1);
if (emp.BeginDate.Value.Year == date.Year && emp.BeginDate.Value.Month == date.Month &&
emp.BeginDate.Value.Day < DateTime.DaysInMonth(year, month))
{
listDates = DateTimeHelper.ListOfWorkingDays(year, month, emp.BeginDate.Value.Day).ToList();
}
else
{
listDates = DateTimeHelper.ListOfWorkingDays(year, month).ToList();
}
List <PersonalReportProjectModel> projects = new List <PersonalReportProjectModel>();
var allprojects = unit.Engagements.Get().Where(e => e.Employee.Id == employeeId).Select(t => t.Team).SelectMany(p => p.Projects).ToList();
foreach (var project in allprojects)
{
decimal?hoursAtProject = unit.Days.Get()
.Where(d => d.Employee.Id == employeeId && d.Date.Value.Year == year && d.Date.Value.Month == month)
.SelectMany(a => a.Assignments).Where(a => a.Project.Id == project.Id).Select(h => h.Hours).DefaultIfEmpty(0).Sum();
projects.Add(new PersonalReportProjectModel()
{
Id = project.Id,
Name = project.Name,
Hours = hoursAtProject
});
}
var report = new PersonalReport()
{
Employee = emp,
WorkingDays = workingDays,
VacationDays = vacationDays,
BusinessAbscenceDays = businessAbscenceDays,
PublicHolidays = publicHolidays,
SickLeaveDays = sickLeaveDays,
ReligiousDays = religiousDays,
OtherAbscenceDays = otherAbsenceDays,
PercentageOfWorkingDays = Math.Round(100 * (double)workingDays / listDates.Count(), 2),
MissingEntries = (emp.BeginDate.Value.Date > date.Date) ? 0 : listDates.Except(days.Select(x => x.Date.Value.Day)).Count(),
OvertimeHours = days.Sum(x => x.OvertimeHours),
Days = days,
TotalHours = totalhoursss.Value,
Year = year,
Month = month,
TotalHoursInYear = unit.Days.Get().Where(x => x.Employee.Id == employeeId && x.Date.Value.Year == year && x.Category.Description == "Working day").Select(x => x.Hours).Sum(),
TotalPossibleHours = listDates.Count() * 8,
Projects = projects
};
return(report);
}
public static PersonalReport GetPersonalReport(this UnitOfWork unit, int employeeId, int year, int month, ModelFactory factory)
{
var emp = (EmployeeModel)unit.Employees.Get().Where(x => x.Id == employeeId).ToList()
.Select(x => factory.Create(x, false)).FirstOrDefault();
if (emp == null)
{
return(null);
}
var days = unit.Days.Get().Where(x => x.Employee.Id == employeeId && x.Date.Year == year && x.Date.Month == month)
.Select(x => new PersonalReportsDay()
{
Date = x.Date,
Type = x.Type.ToString(),
Hours = x.Hours,
Comment = x.Comment,
OvertimeHours = x.Hours - 8,
Tasks = x.Tasks.Select(y => new PersonalReportTask()
{
Hours = y.Hours,
Description = y.Description,
Project = y.Project.Name
}).ToList()
}).OrderBy(y => y.Date).ToList();
int workingDays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year &&
x.Date.Month == month &&
(x.Type == DayType.WorkingDay)).Count();
int vacationDays = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year &&
x.Date.Month == month &&
(x.Type == DayType.Vacation)).Count();
int publicHoliday = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year &&
x.Date.Month == month &&
(x.Type == DayType.PublicHoliday)).Count();
int religiousDay = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year &&
x.Date.Month == month &&
(x.Type == DayType.ReligiousDay)).Count();
int sickLeave = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year &&
x.Date.Month == month &&
(x.Type == DayType.SickLeave)).Count();
int businessAbsence = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year &&
x.Date.Month == month &&
(x.Type == DayType.BusinessAbsence)).Count();
int otherAbsence = unit.Days.Get().Where(x => x.Employee.Id == employeeId &&
x.Date.Year == year &&
x.Date.Month == month &&
(x.Type == DayType.OtherAbsence)).Count();
List <int> listDates = null;
var date = new DateTime(year, month, 1);
if (emp.BeginDate.Year == date.Year &&
emp.BeginDate.Month == date.Month &&
emp.BeginDate.Day < DateTime.DaysInMonth(year, month))
{
listDates = DateTimeHelper.ListOfWorkingDays(year, month, emp.BeginDate.Day).ToList();
}
else
{
listDates = DateTimeHelper.ListOfWorkingDays(year, month).ToList();
}
/*
* var distinct = unit.Days.Get().Where(x => x.Employee.Id == employeeId && x.Date.Year == year && x.Date.Month == month)
* .Select(x => new
* {
* Name = x.Tasks.GroupBy(dp => dp.Project.Name)
* });
*/
var DistinctProjects = unit.Tasks.Get().GroupBy(dp => dp.Project.Name).ToList().Select(y => new PersonalReportDistinct()
{
Name = y.Key,
TotalHours = 0
}).ToList();
/* Dictionary<string, int> groupedCustomerList = unit.Tasks.Get().GroupBy(u => u.Project.Name)
* .Select(grp => new { name = grp.Key })
* .ToDictionary(grp => grp, grp => 0);
*/
//Dictionary<string, int> tasks = new Dictionary<string, int>();
List <PersonalReportDistinct> projectsList = new List <PersonalReportDistinct>();
PersonalReportDistinct projects = new PersonalReportDistinct();
foreach (var project in DistinctProjects)
{
foreach (var day in days)
{
foreach (var task in day.Tasks)
{
if (project.Name == task.Project)
{
project.TotalHours += task.Hours;
}
}
}
}
var report = new PersonalReport()
{
DistinctProjects = DistinctProjects,
Employee = emp,
WorkingDays = workingDays,
VacationDays = vacationDays,
BussinessAbsenceDays = businessAbsence,
PublicHolidayDays = publicHoliday,
SickLeaveDays = sickLeave,
ReligiousDays = religiousDay,
OtherAbsenceDays = otherAbsence,
PercentageOfWorkingDays = Math.Round(100 * (double)workingDays / listDates.Count(), 2),
MissingEntries = (emp.BeginDate.Date > date.Date) ? 0 : listDates.Except(days.Select(x => x.Date.Day)).Count(),
OvertimeHours = days.Sum(x => x.OvertimeHours),
Days = days,
NonWorkingDaysTotal = days.Count() - (workingDays + listDates.Except(days.Select(x => x.Date.Day)).Count())
};
return(report);
}
private List <PersonalReport> ProcessPersonalMonthlyReports(PlanFilter filter)
{
ViewBag.PlanFilter = filter;
var plans = planMonthService.GetQuery(x =>
(filter.Month == 0 || x.PlanMonth == filter.Month) &&
x.PlanYear == filter.Year);
switch (filter.SummaryByType)
{
case SummaryByType.ByUser:
plans = plans.Where(x => x.CRMPlanProgMonth.CRMPLanSale.SalesId == filter.Id);
var usersList = usersServices.GetAllSales(CurrenUser, false).ToList();
if (CurrenUser.IsAdmin())
{
usersList.Add(CurrenUser);
}
ViewBag.AlldeptSalseList = new SelectList(usersList.OrderBy(x => x.FullName), "Id", "FullName");
break;
case SummaryByType.ByDeparment:
plans = plans.Where(x => x.CRMPlanProgMonth.CRMPLanSale.Sales.DeptId == filter.Id);
ViewBag.AlldeptSalseList = new SelectList(usersServices.GetAllDepartmentActive(CurrenUser), "Id", "DeptName");
break;
case SummaryByType.ByOffice:
plans = plans.Where(x => x.CRMPlanProgMonth.CRMPLanSale.Sales.ComId == filter.Id);
ViewBag.AlldeptSalseList = new SelectList(usersServices.GetCompanies(CurrenUser), "Id", "CompanyName");
break;
}
var reports = new List <PersonalReport>();
var summary = crmCustomerService.SummarysList(CurrenUser, filter);
var totalShipmentExc = summary.Sum(x => x.TotalShippments); //So lo thanh cong
var totalSuccessExc = summary.Sum(x => x.SuccessFully); // khach hang moi thanh cong
var totalVistedExc = summary.Sum(x => x.TotalVisitedSuccess); //Vieng tham
var totaldocdExc = summary.Sum(x => x.TotalDocument); //Bai viet
var totalEmailExc = summary.Sum(x => x.TotalFirstSendEmail); //Guid bao gia
var hatMonthShipmentExc = 0; //So lo thanh cong
var hatMonthSuccessExc = 0; // khach hang moi thanh cong
var hatMonthVistedExc = 0; //Vieng tham
var hatMonthdocdExc = 0; //Bai viet
var hatMonthEmailExc = 0; //Guid bao gia
// report for personal monthly
if (filter.ReportType == ReportType.Monthly && filter.Month > 0)
{
var nextTime = new DateTime(filter.Year, filter.Month, 1).AddMonths(1);
filter.NextMonth = nextTime.Month;
filter.NextYear = nextTime.Year;
var cusHatMonth = summary.Where(x => x.CreatedDate <= new DateTime(filter.Year, filter.Month, 15, 23, 59, 59)).ToList();
hatMonthShipmentExc = cusHatMonth.Sum(x => x.TotalShippments); //So lo thanh cong
hatMonthSuccessExc = cusHatMonth.Sum(x => x.SuccessFully); // khach hang moi thanh cong
hatMonthVistedExc = cusHatMonth.Sum(x => x.TotalVisitedSuccess); //Vieng tham
hatMonthdocdExc = cusHatMonth.Sum(x => x.TotalDocument); //Bai viet
hatMonthEmailExc = cusHatMonth.Sum(x => x.TotalFirstSendEmail); //Guid bao gia
}
var planMonths = plans.Where(x => (filter.Month == 0 || x.PlanMonth == filter.Month) && x.PlanYear == filter.Year).ToList();
foreach (var p in planMonths)
{
var report = new PersonalReport
{
PlanName = p.CRMPlanProgMonth.CRMPlanProgram.Name,
PlanValue = p.PlanValue,
Month = p.PlanMonth,
Year = p.PlanYear
};
if (filter.ReportType == ReportType.Monthly && filter.Month > 0)
{
var next = planMonthService.FindEntity(x => x.ProgramMonthId == p.Id && x.PlanMonth == filter.NextMonth && filter.NextYear == x.PlanYear);
var nextPlan = next != null ? next.PlanValue : 0;
report.PlanValueNextMonth = nextPlan;
}
switch (p.CRMPlanProgMonth.ProgramId)
{
case 1:
report.TotalExc = totalSuccessExc;
report.FistExc = hatMonthSuccessExc;
report.LastExc = totalSuccessExc - hatMonthSuccessExc;
break;
case 2:
report.TotalExc = totalEmailExc;
report.FistExc = hatMonthEmailExc;
report.LastExc = totalEmailExc - hatMonthEmailExc;
break;
case 3:
report.TotalExc = totalVistedExc;
report.FistExc = hatMonthVistedExc;
report.LastExc = totalVistedExc - hatMonthVistedExc;
break;
case 4:
report.TotalExc = totalShipmentExc;
report.FistExc = hatMonthShipmentExc;
report.LastExc = totalShipmentExc - hatMonthShipmentExc;
break;
case 5:
report.TotalExc = totaldocdExc;
report.FistExc = hatMonthdocdExc;
report.LastExc = totaldocdExc - hatMonthdocdExc;
break;
}
report.Odds = report.TotalExc - report.PlanValue;
reports.Add(report);
}
ViewBag.PlanMount = plans.ToList();
return(reports);
}