private async Task <MemoryStream> ClosedComplaintActionsCsvStreamAsync()
{
string query = "SELECT * FROM gora.ClosedComplaintActions ORDER BY ComplaintId, ActionDate";
var result = await DataSQLHelper.ExecSQL <ClosedComplaintActions>(query, _context, ExportTimeout);
return(await result.GetCsvMemoryStreamAsync());
}
private async Task <MemoryStream> OpenComplaintsCsvStreamAsync()
{
string query = "SELECT * FROM gora.OpenComplaints ORDER BY ComplaintId";
var result = await DataSQLHelper.ExecSQL <OpenComplaints>(query, _context, ExportTimeout);
return(await result.GetCsvMemoryStreamAsync());
}
public async Task <IActionResult> DaysToFollowUpByStaff(DateTime?beginDate, DateTime?endDate, string office)
{
var currentUser = await GetCurrentUserAsync();
if (string.IsNullOrEmpty(office) ||
!Guid.TryParse(office, out Guid officeId) ||
officeId == null ||
officeId == default ||
!(await _dal.OfficeExists(officeId)))
{
officeId = currentUser.OfficeId.Value;
}
var today = DateTime.Today;
if (!beginDate.HasValue)
{
beginDate = new DateTime(today.Year, today.Month, 1).AddMonths(-1);
}
if (!endDate.HasValue)
{
endDate = new DateTime(today.Year, today.Month, 1).AddDays(-1);
}
IEnumerable <ReportDaysToFollowUpByStaffViewModel.StaffList> staffList = null;
if (endDate < beginDate)
{
var msg = "The beginning date must precede the end date.";
ViewData["AlertMessage"] = new AlertViewModel(msg, AlertStatus.Error, "Error");
}
else
{
var officeStaff = _context.Users.AsNoTracking()
.Where(e => e.OfficeId == officeId);
staffList = await officeStaff
.OrderBy(e => e.LastName)
.ThenBy(e => e.FirstName)
.Select(e => new ReportDaysToFollowUpByStaffViewModel.StaffList(e))
.ToListAsync();
foreach (var user in staffList)
{
string query = $@"SELECT c.Id, l.Name AS ComplaintCounty, c.SourceFacilityName,
convert(DATE, c.DateReceived) AS DateReceived, a.MinActionDate
FROM Complaints c
INNER JOIN LookupCounties l ON c.ComplaintCountyId = l.Id
INNER JOIN
(SELECT c.Id, convert(DATE, min(a.ActionDate)) AS MinActionDate
FROM Complaints c
INNER JOIN ComplaintActions a ON c.Id = a.ComplaintId
GROUP BY c.Id) a ON c.Id = a.Id
WHERE c.Deleted = 0
AND c.CurrentOwnerId = '{user.Id}'
AND a.MinActionDate BETWEEN '{beginDate:d}' AND '{endDate:d}'
ORDER BY c.Id";
user.Complaints = await DataSQLHelper
.ExecSQL <ReportDaysToFollowUpByStaffViewModel.ComplaintList>(query, _context);
}
}
return(View("DaysToFollowUpByStaff", new ReportDaysToFollowUpByStaffViewModel
{
Title = "Days To Follow Up By Staff",
Staff = staffList,
BeginDate = beginDate,
EndDate = endDate,
OfficeSelectList = await _dal.GetOfficesSelectListAsync(),
Office = officeId.ToString(),
CurrentAction = nameof(DaysToFollowUpByStaff)
}));
}
