public Task <EmployeeViewModel> Handle(GetEmployeeQuery request, CancellationToken cancellationToken) =>
queryFacade.QueryFirstOrDefaultAsync <EmployeeViewModel>(@"
SELECT TOP 1
t.AssignmentKey,
Address1,
Address2,
City,
CurrencyCode,
DateTerminated,
DateHired,
DateOfBirth,
EmployeeLevel,
ExternalEmployeeId,
FirstName,
Gender,
e.Id,
LastName,
MiddleName,
PaidTimeOffPolicyId,
PtoHoursRemaining,
Salary,
State,
ZipCode,
SocialSecurityNumber
FROM Employees e
JOIN [dbo].[AspNetUsers] u WITH(NOLOCK) ON u.Id = e.AspNetUsersId
JOIN [dbo].[Tenants] t WITH(NOLOCK) ON e.TenantId = t.Id
WHERE AspNetUsersId = @AspNetUsersId", request);
public async Task <int> Handle(GetTenantIdFromSlugQuery request, CancellationToken cancellationToken)
{
var id = await facade.QueryFirstOrDefaultAsync <int?>(@"SELECT TOP 1 Id FROM Tenants WITH(NOLOCK) WHERE Slug = @Slug", request, null, cancellationToken);
if (id == null)
{
throw new NotFoundException($"Tenant not found for slug '{request.Slug}'.");
}
return((int)id);
}
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 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 <bool> Handle(GetHasTenantAccessQuery request, CancellationToken cancellationToken)
{
var sql = @"SELECT TOP 1 TenantId FROM TenantAspNetUsers WITH(NOLOCK) WHERE TenantId = @TenantId AND AspNetUsersId = @AspNetUsersId";
return((await facade.QueryFirstOrDefaultAsync <int?>(sql, request)) != null);
}
public Task <int?> Handle(GetCustomerIdQuery request, CancellationToken cancellationToken)
{
return(facade.QueryFirstOrDefaultAsync <int?>("SELECT TOP 1 Id FROM Customers WITH(NOLOCK) WHERE AspNetUsersId = @AspNetUsersId", request));
}