public async Task <TenantEmployeeIdentityModel[]> Handle(GetTenantEmployeesForUserQuery request, CancellationToken cancellationToken)
{
var sql = @"
SELECT t.TenantId, e.Id AS EmployeeId FROM TenantAspNetUsers t WITH(NOLOCK)
LEFT JOIN Employees e WITH(NOLOCK) ON t.TenantId = e.TenantId AND t.AspNetUsersId = e.AspNetUsersId
WHERE t.AspNetUsersId = @AspNetUsersId";
var employeesForUserByTenant =
await facade.QueryAsync <TenantEmployeeIdentityModel>(sql, request);
return((
from e in employeesForUserByTenant
group e by e.TenantId into grouped
select grouped.First()).ToArray());
}
public async Task <UserStatusViewModel> Handle(GetUserStatusQuery request, CancellationToken cancellationToken)
{
var userStatus = await facade.QueryFirstOrDefaultAsync <UserStatusViewModel>(
@"
SELECT TOP 1
u.IsCustomer,
c.Id CustomerId,
CASE WHEN c.Id IS NULL THEN 0 ELSE 1 END HasProvidedCustomerInformation,
CASE WHEN e.Id IS NULL THEN 0 ELSE 1 END IsApprovedEmployee
FROM AspNetUsers u WITH(NOLOCK)
LEFT JOIN Customers c WITH(NOLOCK) ON c.AspNetUsersId = u.Id
LEFT JOIN Employees e WITH(NOLOCK) ON e.AspNetUsersId = u.Id AND (@TenantId IS NULL OR e.TenantId = @TenantId)
WHERE u.Id = @AspNetUsersId
", request);
userStatus.AuthorizedTenants = (await facade.QueryAsync <int>("SELECT TenantId FROM TenantAspNetUsers t WITH(NOLOCK) WHERE AspNetUsersId = @AspNetUsersId", request)).ToArray();
return(userStatus);
}
public Task <IReadOnlyList <GetEmployeeListViewModel> > Handle(GetEmployeeListQuery request, CancellationToken cancellationToken) =>
queryFacade.QueryAsync <GetEmployeeListViewModel>(
@"SELECT
e.Id,
e.LastName,
e.FirstName,
e.MiddleName,
e.Gender,
e.EmployeeLevel,
e.DateHired,
e.DateExited,
(SELECT COUNT(DISTINCT EmployeeId) FROM EmployeeManagers s WITH(NOLOCK) WHERE s.ManagerId = e.Id) AS NumSubordinates,
m.Id AS ManagerId,
m.LastName AS ManagerLastName,
m.FirstName AS ManagerFirstName,
m.MiddleName AS ManagerMiddleName
FROM Employees e WITH(NOLOCK)
LEFT JOIN EmployeeManagers em WITH(NOLOCK) ON em.EmployeeId = e.Id
LEFT JOIN Employees m WITH(NOLOCK) ON em.ManagerId = m.Id");
public async Task <PaidTimeOffRequestValidationResult> Handle(ValidateRequestedPaidTimeOffHoursQuery request, CancellationToken cancellationToken)
{
var viewModel = request.ValidationRequest;
var tentativeRequest = mapper.Map(viewModel);
var today = DateTime.Today;
var parms = new { request.AspNetUsersId, viewModel.EndDate, viewModel.StartDate, viewModel.ForEmployeeId, viewModel.HoursRequested, viewModel.TenantId };
// TODO: add domain validation/authorization if submitting on behalf of another employee (i.e. manager is submitting).
var paidTimeOffPolicy = await facade.QueryFirstOrDefaultAsync <PaidTimeOffPolicy>(@"
SELECT TOP 1 p.[Id]
,p.[AllowsUnlimitedPto]
,p.[EmployeeLevel]
,p.[IsDefaultForEmployeeLevel]
,p.[MaxPtoHours]
,p.[Name]
,p.[PtoAccrualRate]
FROM PaidTimeOffPolicies p WITH(NOLOCK)
JOIN Employees e WITH(NOLOCK) ON e.PaidTimeOffPolicyId = p.Id AND e.TenantId = p.TenantId
WHERE ((@ForEmployeeId IS NULL AND e.AspNetUsersId = @AspNetUsersId) OR e.Id = @ForEmployeeId) AND e.TenantId = @TenantId
", parms, cancellationToken : cancellationToken);
if (paidTimeOffPolicy == null)
{
throw new NotFoundException("PTO policy not found or invalid.");
}
var existingRequests = await facade.QueryAsync <PaidTimeOffRequest>(@"
SELECT p.[Id]
,p.[ApprovalStatus]
,p.[EndDate]
,p.[ForEmployeeId]
,p.[HoursRequested]
,p.[StartDate]
,p.[Paid]
,p.[SubmittedById]
FROM [PaidTimeOffRequests] p WITH(NOLOCK)
JOIN Employees e WITH(NOLOCK) ON e.Id = p.ForEmployeeId AND e.TenantId = p.TenantId
WHERE ((@ForEmployeeId IS NULL AND e.AspNetUsersId = @AspNetUsersId) OR e.Id = @ForEmployeeId) AND e.TenantId = @TenantId
", parms, cancellationToken : cancellationToken);
return(paidTimeOffRequestService.ValidatePaidTimeOffRequest(tentativeRequest, existingRequests, paidTimeOffPolicy, today));
}
public async Task <PaidTimeOffRequestValidationResult> Handle(ValidateRequestedPaidTimeOffHoursQuery request, CancellationToken cancellationToken)
{
var tentativeRequest = mapper.Map(request.ValidationRequest);
var today = DateTime.Today;
var paidTimeOffPolicy = await facade.QueryFirstOrDefaultAsync <PaidTimeOffPolicy>(@"
SELECT TOP 1 p.[Id]
,p.[AllowsUnlimitedPto]
,p.[EmployeeLevel]
,p.[IsDefaultForEmployeeLevel]
,p.[MaxPtoHours]
,p.[Name]
,p.[PtoAccrualRate]
FROM PaidTimeOffPolicies p WITH(NOLOCK)
JOIN Employees e WITH(NOLOCK) ON e.PaidTimeOffPolicyId = p.Id AND e.TenantId = p.TenantId
WHERE e.Id = @ForEmployeeId AND e.TenantId = @TenantId
", request.ValidationRequest, cancellationToken : cancellationToken);
if (paidTimeOffPolicy == null)
{
throw new NotFoundException("PTO policy not found or invalid.");
}
var existingRequests = await facade.QueryAsync <PaidTimeOffRequest>(@"
SELECT [Id]
,[ApprovalStatus]
,[EndDate]
,[ForEmployeeId]
,[HoursRequested]
,[StartDate]
,[Paid]
,[SubmittedById]
FROM [PaidTimeOffRequests] WITH(NOLOCK)
WHERE @ForEmployeeId = @ForEmployeeId AND TenantId = @TenantId
", request.ValidationRequest, cancellationToken : cancellationToken);
return(paidTimeOffRequestService.ValidatePaidTimeOffRequest(tentativeRequest, existingRequests, paidTimeOffPolicy, today));
}
public async Task <int[]> Handle(GetAuthorizedTenantsForUserQuery request, CancellationToken cancellationToken)
=> (await facade.QueryAsync <int>("SELECT TenantId FROM TenantAspNetUsers WITH(NOLOCK) WHERE AspNetUsersId = @AspNetUsersId", request)).ToArray();
public async Task <TenantViewModel[]> Handle(GetTenantsForCustomerQuery request, CancellationToken cancellationToken) =>
(await facade.QueryAsync <TenantViewModel>(
@"SELECT t.Id, t.AssignmentKey, t.Name, t.Slug FROM Tenants t WITH(NOLOCK)
JOIN Customers c WITH(NOLOCK) ON t.CustomerId = c.Id
WHERE c.AspNetUsersId = @AspNetUsersId
", request)).ToArray();
public Task <IReadOnlyList <GetPaidTimeOffPolicyListViewModel> > Handle(GetPaidTimeOffPolicyListQuery request, CancellationToken cancellationToken) =>
queryFacade.QueryAsync <GetPaidTimeOffPolicyListViewModel>("SELECT Id, Name, AllowsUnlimitedPto, EmployeeLevel, IsDefaultForEmployeeLevel FROM PaidTimeOffPolicies WITH(NOLOCK)");