public async Task <IEnumerable <Employee> > GetByFilterAsync()
{
int yearStart, monthStart, yearEnd, monthEnd;
yearStart = DateTime.Now.Year;
monthStart = DateTime.Now.Month;
yearEnd = DateTime.Now.AddMonths(5).Year;
monthEnd = DateTime.Now.AddMonths(5).Month;
var employeeDictionary = new Dictionary <Guid, Employee>();
StringBuilder sql = new StringBuilder();
// sql.AppendLine("SELECT e.Id, e.Name, e.Email, CONVERT(varchar(40),a.EmployeeId) + CONVERT(varchar(10),a.Year) + CONVERT(varchar(10),a.Month) as AID, a.Year, a.Month, a.Precentage FROM Employees e");
// sql.AppendLine("LEFT OUTER JOIN EmployeeAvailabilities a on a.EmployeeId = e.Id AND (a.Year BETWEEN @YearStart AND @YearEnd) AND (a.Month BETWEEN @MonthStart AND @MonthEnd)");
sql.AppendLine("SELECT e.Id, e.Name, e.Email, ");
sql.AppendLine("CONVERT(varchar(40),a.EmployeeId) + CONVERT(varchar(10),a.Year) + CONVERT(varchar(10),a.Month) as AID, ");
sql.AppendLine("a.Year * 100 + a.Month as ACACL, a.Year, a.Month, a.Precentage, ");
sql.AppendLine("r.Id, r.Name, ");
sql.AppendLine("wp.Id, wp.Country, wp.City, wp.Name, ");
sql.AppendLine("manager.Id, manager.Name ");
sql.AppendLine("FROM Employees e");
sql.AppendLine("LEFT OUTER JOIN EmployeeAvailabilities a on a.EmployeeId = e.Id AND ((a.Year * 100 + a.Month) BETWEEN @YearStart * 100 + @MonthStart AND @YearEnd * 100 + @MonthEnd)");
sql.AppendLine("LEFT OUTER JOIN EmployeeRoles er on er.EmployeeId = e.Id ");
sql.AppendLine("LEFT OUTER JOIN Roles r on r.Id = er.RoleId ");
sql.AppendLine("LEFT OUTER JOIN WorkPlaces wp on wp.Id = e.WorkPlaceId ");
sql.AppendLine("LEFT OUTER JOIN Employees manager on manager.Id = e.ManagerId ");
sql.AppendLine("ORDER BY e.Name ");
using (var c = this.OpenConnection)
{
var r = await c.QueryAsync <Employee, AvailabilityEntry, EmployeeRole, WorkPlace, Manager, Employee>(
sql.ToString(),
(employee, availability, employeeRole, workPlace, manager) =>
{
Employee employeeEntry = null;
if (!employeeDictionary.TryGetValue(employee.Id, out employeeEntry))
{
employeeEntry = employee;
employeeDictionary.Add(employeeEntry.Id, employeeEntry);
}
if (manager != null)
{
employee.SetManager(manager);
}
if (availability != null)
{
if (!employeeEntry.Availability.Any(a => a.Year == availability.Year && a.Month == availability.Month))
{
employeeEntry.AddAvailability(availability.Year, availability.Month, availability.Precentage);
}
}
if (employeeRole != null && !string.IsNullOrEmpty(employeeRole.Name))
{
if (!employeeEntry.Roles.Any(er => er.Name == employeeRole.Name))
{
employeeEntry.AddRole(employeeRole.Id, employeeRole.Name, null);
}
}
if (workPlace != null)
{
employeeEntry.SetWorkPlace(workPlace);
}
return(employeeEntry);
},
splitOn : "AID, Id, Id, Id",
param : new
{
YearStart = yearStart,
MonthStart = monthStart,
YearEnd = yearEnd,
MonthEnd = monthEnd
}
);
var items = employeeDictionary.Values.ToList();
return(items);
}
}
