public static async Task <List <EmployeeContactDetail> > Query(GetEmployeeContacts queryParameters, DapperContext ctx)
{
if (await IsValidEmployeeID(queryParameters.EmployeeID, ctx) == false)
{
throw new ArgumentException($"No employee record found where EmployeeId equals {queryParameters.EmployeeID}.");
}
var sql =
@"SELECT
PersonId, AgentId AS 'EmployeeId', FirstName, LastName, MiddleInitial, Telephone, Notes,
CONCAT(FirstName,' ',COALESCE(MiddleInitial,''),' ',LastName) AS FullName
FROM Shared.ContactPersons
WHERE AgentId = @ID
ORDER BY LastName, FirstName";
var parameters = new DynamicParameters();
parameters.Add("ID", queryParameters.EmployeeID, DbType.Guid);
using (var connection = ctx.CreateConnection())
{
var items = await connection.QueryAsync <EmployeeContactDetail>(sql, parameters);
return(items.ToList());
}
}
public static async Task <List <EmployeeAddressDetail> > Query(GetEmployeeAddresses queryParameters, DapperContext ctx)
{
if (await IsValidEmployeeID(queryParameters.EmployeeID, ctx) == false)
{
throw new ArgumentException($"No employee record found where EmployeeId equals {queryParameters.EmployeeID}.");
}
var sql =
@"SELECT
a.AddressId, AgentId AS 'EmployeeId', a.AddressLine1, a.AddressLine2, a.City, a.StateCode, a.Zipcode,
CONCAT(a.AddressLine1,' ',COALESCE(a.AddressLine2,''), ' ', a.City, ', ', a.StateCode, ' ', a.Zipcode) as FullAddress
FROM Shared.Addresses a
WHERE a.AgentId = @ID
ORDER BY a.City, a.StateCode, a.Zipcode, a.AddressLine1";
var parameters = new DynamicParameters();
parameters.Add("ID", queryParameters.EmployeeID, DbType.Guid);
using (var connection = ctx.CreateConnection())
{
var items = await connection.QueryAsync <EmployeeAddressDetail>(sql, parameters);
return(items.ToList());
}
}
public async static Task <EmployeeDependencyCheckResult> Query(DoEmployeeDependencyCheck queryParameters, DapperContext ctx)
{
if (await IsValidEmployeeID(queryParameters.EmployeeID, ctx) == false)
{
throw new ArgumentException($"No employee record found where EmployeeId equals {queryParameters.EmployeeID}.");
}
var sql =
@"SELECT
emp.EmployeeId, COUNT(DISTINCT addr.AddressId) AS 'Addresses', COUNT(DISTINCT con.PersonId) AS 'Contacts'
FROM HumanResources.Employees emp
INNER JOIN Shared.Addresses addr ON emp.EmployeeId = addr.AgentId
INNER JOIN Shared.ContactPersons con ON emp.EmployeeId = con.AgentId
WHERE emp.EmployeeId = @ID
GROUP BY emp.EmployeeId";
var parameters = new DynamicParameters();
parameters.Add("ID", queryParameters.EmployeeID, DbType.Guid);
using (var connection = ctx.CreateConnection())
{
return(await connection.QueryFirstOrDefaultAsync <EmployeeDependencyCheckResult>(sql, parameters));
}
}
private static async Task <bool> IsValidEmployeeID(Guid employeeId, DapperContext ctx)
{
string sql = $"SELECT EmployeeID FROM HumanResources.Employees WHERE EmployeeId = @ID";
var parameters = new DynamicParameters();
parameters.Add("ID", employeeId, DbType.Guid);
using (var connection = ctx.CreateConnection())
{
var result = await connection.QueryFirstOrDefaultAsync(sql, parameters);
return(result != null);
}
}
public static async Task <ReadOnlyCollection <SupervisorLookup> > Query(DapperContext ctx)
{
var sql = "SELECT ee.EmployeeId AS ManagerId,";
sql += " CONCAT(ee.FirstName,' ',COALESCE(ee.MiddleInitial,''),' ',ee.LastName) as ManagerName ";
sql += " FROM HumanResources.Employees ee";
sql += " WHERE ee.EmployeeId IN (SELECT DISTINCT SupervisorId FROM HumanResources.Employees)";
sql += " ORDER BY ee.LastName, ee.FirstName, ee.MiddleInitial";
using var connection = ctx.CreateConnection();
var items = await connection.QueryAsync <SupervisorLookup>(sql);
return(new ReadOnlyCollection <SupervisorLookup>(items.ToList()));
}
public static async Task <EmployeeDetail> Query(GetEmployee queryParameters, DapperContext ctx)
{
if (await IsValidEmployeeID(queryParameters.EmployeeID, ctx) == false)
{
throw new ArgumentException($"No employee record found where EmployeeId equals {queryParameters.EmployeeID}.");
}
var sql =
@"SELECT
ee.EmployeeId, ee.LastName, ee.FirstName, ee.MiddleInitial,
CONCAT(ee.FirstName,' ',COALESCE(ee.MiddleInitial,''),' ',ee.LastName) as EmployeeFullName, ee.Telephone, ee.IsActive,
ee.SupervisorId, CONCAT(supv.FirstName,' ',COALESCE(supv.MiddleInitial,''),' ',supv.LastName) as SupervisorFullName,
ee.SSN, ee.MaritalStatus, ee.Exemptions, ee.PayRate, ee.StartDate, ee.CreatedDate, ee.LastModifiedDate
FROM HumanResources.Employees ee
INNER JOIN
(
SELECT
EmployeeId, LastName, FirstName, MiddleInitial
FROM HumanResources.Employees emp
WHERE EmployeeId IN (SELECT DISTINCT SupervisorId FROM HumanResources.Employees)
) supv ON ee.SupervisorId = supv.EmployeeId
WHERE ee.EmployeeId = @ID";
var parameters = new DynamicParameters();
parameters.Add("ID", queryParameters.EmployeeID, DbType.Guid);
using (var connection = ctx.CreateConnection())
{
EmployeeDetail employee = await connection.QueryFirstOrDefaultAsync <EmployeeDetail>(sql, parameters);
employee.Addresses =
await GetEmployeeDetailAddresses.Query(new GetEmployeeAddresses { EmployeeID = queryParameters.EmployeeID }, ctx);
employee.Contacts =
await GetEmployeeDetailContacts.Query(new GetEmployeeContacts { EmployeeID = queryParameters.EmployeeID }, ctx);
return(employee);
}
}
public static async Task <PagedList <EmployeeListItem> > Query(GetEmployees queryParameters, DapperContext ctx)
{
var parameters = new DynamicParameters();
var sql = "SELECT";
sql += " ee.EmployeeId, ee.LastName, ee.FirstName, ee.MiddleInitial,";
sql += " CONCAT(ee.FirstName,' ',COALESCE(ee.MiddleInitial,''),' ',ee.LastName) as EmployeeFullName,";
sql += " ee.Telephone, ee.IsActive, ee.SupervisorId,";
sql += " supv.LastName AS ManagerLastName, supv.FirstName AS ManagerFirstName, supv.MiddleInitial AS ManagerMiddleInitial,";
sql += " CONCAT(supv.FirstName,' ',COALESCE(supv.MiddleInitial,''),' ',supv.LastName) as SupervisorFullName";
sql += " FROM HumanResources.Employees ee";
sql += " INNER JOIN";
sql += " (";
sql += " SELECT EmployeeId, LastName, FirstName, MiddleInitial";
sql += " FROM HumanResources.Employees emp";
sql += " WHERE EmployeeId IN (SELECT DISTINCT SupervisorId FROM HumanResources.Employees)";
sql += " ) supv ON ee.SupervisorId = supv.EmployeeId WHERE 1 = 1";
if (!string.IsNullOrEmpty(queryParameters.EmployeeLastName))
{
parameters.Add("EmployeeLastName", queryParameters.EmployeeLastName, DbType.String);
sql += " AND ee.LastName LIKE @EmployeeLastName + '%'";
}
if (!string.IsNullOrEmpty(queryParameters.SupervisorLastName))
{
parameters.Add("SupervisorLastName", queryParameters.SupervisorLastName, DbType.String);
sql += " AND supv.LastName LIKE @SupervisorLastName + '%'";
}
if (!string.IsNullOrEmpty(queryParameters.SortField))
{
if (!string.IsNullOrEmpty(queryParameters.SortOrder))
{
sql += queryParameters.SortOrder.ToUpper() == "ASC"
? $" ORDER BY {queryParameters.SortField} ASC"
: $" ORDER BY {queryParameters.SortField} DESC";
}
else
{
sql += $" ORDER BY {queryParameters.SortField}";
}
}
else
{
if (!string.IsNullOrEmpty(queryParameters.SortOrder))
{
sql += queryParameters.SortOrder.ToUpper() == "ASC"
? " ORDER BY ee.LastName ASC"
: " ORDER BY ee.LastName DESC";
}
else
{
sql += " ORDER BY ee.LastName";
}
}
parameters.Add("Offset", Offset(queryParameters.Page, queryParameters.PageSize), DbType.Int32);
parameters.Add("PageSize", queryParameters.PageSize, DbType.Int32);
string stmt = sql + " OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
using (var connection = ctx.CreateConnection())
{
var items = await connection.QueryAsync <EmployeeListItem>(stmt, parameters);
var totalItems = await connection.QueryAsync <EmployeeListItem>(sql, parameters);
int count = totalItems.ToList().Count;
var pagedList = PagedList <EmployeeListItem> .CreatePagedList(items.ToList(), count, queryParameters.Page, queryParameters.PageSize);
return(pagedList);
}
}
