public async Task <RequestListResponse> ListAllRequests(RequestSearchFilter requestSearchFilter)
{
RequestListResponse response = new RequestListResponse();
try
{
requestSearchFilter.CurrentPage = (requestSearchFilter.CurrentPage - 1) * requestSearchFilter.PageSize;
if (requestSearchFilter.CurrentPage <= 0)
{
requestSearchFilter.CurrentPage = 0;
}
response.requestViews = await _requestRepository.GetRequests(requestSearchFilter);
response.IsSuccess = response.requestViews.Any();
if (!response.IsSuccess)
{
response.Message = "No requests found retrieved from given filters / query.";
}
}
catch (Exception e)
{
response.Message = "Error in retreiving request list with filters.";
response.IsSuccess = false;
}
return(response);
}
public async Task <List <RequestReportModel> > GetRequestDetailsForReport(RequestSearchFilter requestSearchFilter)
{
//List<RequestReportModel> RequestDetailsList = new List<RequestReportModel>();
try
{
string query = PrepareRequestQueryWithFilters(SqlQueries.GetRequestDetailsForReport, requestSearchFilter, isExport: true);
/*string append = @") [GetRID]
* LEFT OUTER JOIN
* (SELECT[Rq].[YorbitRequestId] FROM[Request] as [Rq] INNER JOIN
*
* [AssignmentAttempt] as [asa] ON
* [Rq].[Id] = [asa].[RequestId])
* [AA]
* ON
* [AA].[YorbitRequestId] = [GetRID].[RequestId]";*/
//string ExecutableQuery = query+append;
List <RequestReportModel> RequestDetailsList = (await _dataRepository.QueryAsync <RequestReportModel>(query))?.ToList();
return(RequestDetailsList);
}
catch (Exception e)
{
return(null);
}
}
public async Task <IActionResult> GetRequestsFiltered([FromBody] RequestSearchFilter requestSearchFilter, bool IsExport)
{
//IsExport = true;
if (requestSearchFilter == null || !ModelState.IsValid)
{
return(BadRequest("Invalid parameters"));
}
var response = await _requestManager.ListAllRequests(requestSearchFilter);
if (IsExport)
{
var ExcelReportStream = await _requestManager.ListRequestDetailsForReport(requestSearchFilter);
try
{
FileStreamResult fr = File(ExcelReportStream, "application/vnd.openxmlformats");
return(fr);
}
catch (Exception e)
{
return(NotFound());
}
}
if (response.IsSuccess)
{
return(Ok(response.requestViews));
}
return(NoContent());
}
public async Task GetRequestsFiltered_Test_Ok()
{
requestSearchFilter = new RequestSearchFilter();
var result = await requestController.GetRequestsFiltered(requestSearchFilter);
var status = (result as OkObjectResult).StatusCode;
Assert.AreEqual(status, 200);
}
//[DataRow(12, 3, 4)]
//[DataRow(12, 2, 6)]
//[DataRow(12, 4, 3)]
public async Task GetRequestsFiltered_Test_NoContent()
{
requestSearchFilter = new RequestSearchFilter();
var result = await requestController.GetRequestsFiltered(requestSearchFilter);
var status = (result as NoContentResult).StatusCode;
Assert.AreEqual(expected: status, actual: 204);
}
/// <summary>
///
/// </summary>
/// <param name="searchFilter"></param>
/// <returns></returns>
public async Task <IEnumerable <RequestView> > GetRequests(RequestSearchFilter requestSearchFilter)
{
try
{
string query = PrepareRequestQueryWithFilters(SqlQueries.GetRequestList, requestSearchFilter);
return(await _dataRepository.QueryAsync <RequestView>(query));
}
catch (Exception ex)
{
Logging.Logger.LogException(ex);
return(Enumerable.Empty <RequestView>());
}
}
/// <summary>
/// To generate Excel request report
/// </summary>
/// <param name="requestSearchFilter"></param>
/// <returns></returns>
public async Task <MemoryStream> ListRequestDetailsForReport(RequestSearchFilter requestSearchFilter)
{
List <RequestReportModel> requestReportModelList;
//List<RequestView> requestCurrentStatusModel;
RequestListResponse response = new RequestListResponse();
try
{
requestSearchFilter.CurrentPage = (requestSearchFilter.CurrentPage - 1) * requestSearchFilter.PageSize;
//if (requestSearchFilter.CurrentPage <= 0)
requestSearchFilter.CurrentPage = 0;
requestSearchFilter.PageSize = 900;
requestSearchFilter.Sort = E_SORT_ORDER.SUBMISSION_DATE_ASC;
var currentRequestStatus = await _requestRepository.GetRequests(requestSearchFilter);
var currentRequestStatusList = currentRequestStatus.ToList();
requestSearchFilter.CurrentPage = 0;
requestSearchFilter.Filter = E_RESULT_FILTER.ALL;
requestReportModelList = await _requestRepository.GetRequestDetailsForReport(requestSearchFilter);
List <RequestReportModel> finalRequestReportModelList = new List <RequestReportModel>();
if (requestReportModelList == null)
{
return(null);
}
var requestReportList = new List <RequestReportExcelModel>();
foreach (var filteredRequest in requestReportModelList)
{
foreach (var checkRequest in currentRequestStatusList)
{
if (checkRequest.YorbitRequestId.Equals(filteredRequest.RequestID))
{
finalRequestReportModelList.Add(filteredRequest);
}
}
}
var groupedRequests = finalRequestReportModelList.GroupBy(x => x.RequestID);
foreach (var request in groupedRequests)
{
var requestReport = new RequestReportExcelModel();
//requestReport.RequestID = request[0].RequestID;
//requestReport.Mid = request[0].Mid;
int i = 0;
foreach (var attempt in request)
{
bool fillEvaluatorDownloadDate = false;
bool fillEvaluationResultDate = false;
bool fillSubmissionDate = false;
if (!attempt.SubmissionDate.ToString().Equals("1/1/0001 12:00:00 AM"))
{
fillSubmissionDate = true;
}
if (!attempt.EvaluatorAssignmentDownloadDate.ToString().Equals("1/1/0001 12:00:00 AM"))
{
fillEvaluatorDownloadDate = true;
}
if (!attempt.EvaluationResultDate.ToString().Equals("1/1/0001 12:00:00 AM"))
{
fillEvaluationResultDate = true;
}
if (i == 0)
{
requestReport.RequestID = attempt.RequestID;
requestReport.Mid = attempt.Mid;
requestReport.Name = attempt.Name;
requestReport.EmailID = attempt.EmailID;
requestReport.CourseID = attempt.CourseID;
requestReport.CourseName = attempt.CourseName;
requestReport.Academy = attempt.Academy;
requestReport.ApprovedDate = attempt.ApprovedDate.ToString("MM/dd/yyyy");
requestReport.AssignmentDueDate = attempt.AssignmentDueDate.Date.ToString("MM/dd/yyyy");
requestReport.EvaluatorMailId = attempt.EvaluatorMailId;
requestReport.EvaluatorType = attempt.EvaluatorType;
requestReport.Vendor = attempt.Vendor;
if (fillSubmissionDate)
{
requestReport.SubmissionDate = attempt.SubmissionDate.Date.ToString("MM/dd/yyyy");
}
requestReport.LearnerTAT = attempt.LearnerTAT;
if (fillEvaluatorDownloadDate)
{
requestReport.FirstEvaluatorAssignmentDownloadDate = attempt.EvaluatorAssignmentDownloadDate.Date.ToString("MM/dd/yyyy");
}
if (fillEvaluationResultDate)
{
requestReport.FirstEvaluationResultDate = attempt.EvaluationResultDate.Date.ToString("MM/dd/yyyy");
requestReport.FirstEvaluatorTAT = attempt.EvaluatorTAT;
}
requestReport.FirstScore = attempt.Score;
requestReport.FirstRequestStatus = attempt.RequestStatus;
requestReport.FirstEvaluatorComments = attempt.EvaluatorComments;
}
else if (i == 1)
{
requestReport.FirstResubmissionDate = attempt.SubmissionDate.Date.ToString("MM/dd/yyyy");
if (fillEvaluatorDownloadDate)
{
requestReport.SecondEvaluatorAssignmentDownloadDate = attempt.EvaluatorAssignmentDownloadDate.Date.ToString("MM/dd/yyyy");
}
if (fillEvaluationResultDate)
{
requestReport.SecondEvaluationResultDate = attempt.EvaluationResultDate.Date.ToString("MM/dd/yyyy");
requestReport.SecondEvaluatorTAT = attempt.EvaluatorTAT;
}
requestReport.SecondScore = attempt.Score;
requestReport.SecondRequestStatus = attempt.RequestStatus;
requestReport.SecondEvaluatorComments = attempt.EvaluatorComments;
}
else
{
requestReport.SecondResubmissionDate = attempt.SubmissionDate.Date.ToString("MM/dd/yyyy");
if (fillEvaluatorDownloadDate)
{
requestReport.ThirdEvaluatorAssignmentDownloadDate = attempt.EvaluatorAssignmentDownloadDate.Date.ToString("MM/dd/yyyy");
}
if (fillEvaluationResultDate)
{
requestReport.ThirdEvaluationResultDate = attempt.EvaluationResultDate.Date.ToString("MM/dd/yyyy");
requestReport.ThirdEvaluatorTAT = attempt.EvaluatorTAT;
}
requestReport.ThirdScore = attempt.Score;
requestReport.ThirdRequestStatus = attempt.RequestStatus;
requestReport.ThirdEvaluatorComments = attempt.EvaluatorComments;
}
i++;
}
requestReportList.Add(requestReport);
}
MemoryStream memS = new MemoryStream();
ExcelPackage excel = new ExcelPackage();
List <string> listOfHeader = new List <string> {
"Request ID",
"MID",
"Name",
"Email id",
"Course Name",
"Course ID",
"Academy",
"Approved date",
"Due date for submission",
"Evaluator e-mail ID",
"Internal / Extenal",
"Vendor",
"Assignment submitted date(Tool to capture)",
"TAT by learner",
"Assignment downloaded date by evaluator",
"Date of evaluation results submitted by evaluator",
"Turn around time for evaluation",
"Score",
"Status(Cleared / Not cleared)",
"Comment by evaluator",
"Resubmission date",
"Assignment downloaded date by evaluator",
"Date of evaluation results submitted by evaluator",
"Turn around time for evaluation",
"Score",
"Status(Cleared / Not cleared)",
"Comment by evaluator",
"Resubmission date",
"Assignment downloaded date by evaluator",
"Date of evaluation results submitted by evaluator",
"Turn around time for evaluation",
"Score",
"Status(Cleared / Not cleared)",
"Comment by evaluator"
};
var worksheet = excel.Workbook.Worksheets.Add("Requests");
if (requestReportList.Any())
{
worksheet.Column(20).Style.WrapText = true;
worksheet.Column(20).AutoFit();
worksheet.Column(27).Style.WrapText = true;
worksheet.Column(27).AutoFit();
worksheet.Column(34).Style.WrapText = true;
worksheet.Column(34).AutoFit();
worksheet.Cells["M1:T1"].Merge = true;
worksheet.Cells["M1"].Value = "1st Attempt";
worksheet.Cells["M1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["M1"].Style.Fill.BackgroundColor.SetColor(Color.AliceBlue);
worksheet.Cells["M1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells["U1:AA1"].Merge = true;
worksheet.Cells["U1"].Value = "2nd Attempt";
worksheet.Cells["U1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["U1"].Style.Fill.BackgroundColor.SetColor(Color.SkyBlue);
worksheet.Cells["U1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells["AB1:AH1"].Merge = true;
worksheet.Cells["AB1"].Value = "3rd Attempt";
worksheet.Cells["AB1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["AB1"].Style.Fill.BackgroundColor.SetColor(Color.DeepSkyBlue);
worksheet.Cells["AB1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells["A2"].LoadFromArrays(new List <string[]>(new[] { listOfHeader.ToArray() }));
worksheet.Cells["A3"].LoadFromCollection(requestReportList, false);
int colCount = worksheet.Dimension.End.Column;
int rowCount = worksheet.Dimension.End.Row;
worksheet.Cells[1, 13, 1, colCount].Style.Font.Bold = true;
worksheet.Cells[1, 13, 1, colCount].AutoFilter = false;
worksheet.Cells[1, 13, rowCount, colCount].AutoFitColumns();
worksheet.Cells[1, 13, 1, colCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[2, 1, 2, colCount].Style.Font.Bold = true;
worksheet.Cells[2, 1, 2, colCount].AutoFitColumns();
worksheet.Cells[2, 1, 2, colCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[2, 1, 2, colCount].AutoFilter = false;
worksheet.Cells[2, 1, rowCount, colCount].AutoFitColumns();
excel.SaveAs(memS);
excel.Dispose();
memS.Position = 0;
return(memS);
}
return(null);
}
catch (Exception e)
{
return(null);
}
}
private string PrepareRequestQueryWithFilters(string query, RequestSearchFilter requestSearchFilter, bool isExport = false)
{
ApplicationContext context = _contextProvidor.Context;
Guid EvaluatorId = context.CurrentUser.Id;
StringBuilder dynamicQuery = new StringBuilder();
dynamicQuery.Append(query);
dynamicQuery.Append(" ");
if (context.CurrentUser.RoleId == Apsis.Models.Constants.Constants.EvaluatorRoleId)
{
dynamicQuery.Append($"AND [A].ResultId = '7002ED9E-47D4-4C68-86FF-39EEE95ACEB4' AND [R].EvaluatorId = '{EvaluatorId}'");
}
dynamicQuery.Append($" AND [U].[Mid] != '{context.CurrentUser.Mid}' ");
//====================== Where clause - based on Status for Assignment ===================
dynamicQuery.Append(" ");
if (requestSearchFilter.Filter == E_RESULT_FILTER.Project_Not_Cleared)
{
dynamicQuery.Append($"AND [A].[ResultId] = '{RequestResultCodes.Project_Not_Cleared}'");
}
else if (requestSearchFilter.Filter == E_RESULT_FILTER.Result_Awaited)
{
dynamicQuery.Append($"AND [A].[ResultId] = '{RequestResultCodes.Result_Awaited}'");
}
else if (requestSearchFilter.Filter == E_RESULT_FILTER.Project_Cleared)
{
dynamicQuery.Append($"AND [A].[ResultId] = '{RequestResultCodes.Project_Cleared}'");
}
else if (requestSearchFilter.Filter == E_RESULT_FILTER.ERROR)
{
dynamicQuery.Append($"AND [A].[ResultId] = '{RequestResultCodes.ERROR}'");
}
else if (requestSearchFilter.Filter == E_RESULT_FILTER.YET_TO_SUBMIT)
{
dynamicQuery.Append($"AND [A].[SubmissionDate] IS NULL");
}
else if (requestSearchFilter.Filter == E_RESULT_FILTER.YET_TO_PUBLISHED)
{
dynamicQuery.Append($"AND [R].[isPublished] =0 AND [A].[ResultId] IN('{RequestResultCodes.Project_Cleared}','{RequestResultCodes.Project_Not_Cleared}')");
}
//=============================== Keyword ======================================
if (requestSearchFilter.Keyword != null && requestSearchFilter.Keyword.Length > 0)
{
StringBuilder subKeywordSearchConditions = new StringBuilder();
String escapedKeyword = EscapeCharacters(requestSearchFilter.Keyword.Trim());
DateTime dateTime;
if (DateTime.TryParse(escapedKeyword, out dateTime))
{
subKeywordSearchConditions.Append($" CONVERT(NVARCHAR(50), [A].[SubmissionDate], 126) LIKE '%{dateTime.ToString("yyyy-MM-dd")}%' ESCAPE '\\' ");
subKeywordSearchConditions.Append(" OR ");
subKeywordSearchConditions.Append($" CONVERT(NVARCHAR(50), [R].[AssignmentDueDate], 126) LIKE '%{dateTime.ToString("yyyy-MM-dd")}%' ESCAPE '\\' ");
}
else
{
string keyword = $"'%{escapedKeyword}%'";
subKeywordSearchConditions.Append($" [R].[YorbitRequestId] LIKE {keyword} ESCAPE '\\' OR ");
subKeywordSearchConditions.Append($" [U].[Name] LIKE {keyword} ESCAPE '\\' OR ");
subKeywordSearchConditions.Append($" [U].[MID] LIKE {keyword} ESCAPE '\\' OR ");
subKeywordSearchConditions.Append($" [C].[Academy] LIKE {keyword} ESCAPE '\\' OR ");
subKeywordSearchConditions.Append($" [C].[Name] LIKE {keyword} ESCAPE '\\' OR ");
//subKeywordSearchConditions.Append($" [C].[YorbitCourseId] LIKE {keyword} ESCAPE '\\' OR ");
subKeywordSearchConditions.Append($" CAST([A].[SubmissionDate] as Date) LIKE {keyword} ESCAPE '\\' OR ");
subKeywordSearchConditions.Append($" CAST([R].[AssignmentDueDate] as Date) LIKE {keyword} ESCAPE '\\'");
}
dynamicQuery.Append(" ");
dynamicQuery.Append($"AND ({subKeywordSearchConditions.ToString()})");
}
//=========================== SORT ORDER ================================
string order;
switch (requestSearchFilter.Sort)
{
//---------------YORBIT REQUEST ID-----------------------
case Models.Constants.E_SORT_ORDER.YORBIT_ID_ASC:
order = "[R].[YorbitRequestId] ASC";
break;
case Models.Constants.E_SORT_ORDER.YORBIT_ID_DESC:
order = "[R].[YorbitRequestId] DESC";
break;
//---------------COURSE_NAME-----------------------
case Models.Constants.E_SORT_ORDER.COURSE_NAME_ASC:
order = "[C].[Name] ASC";
break;
case Models.Constants.E_SORT_ORDER.COURSE_NAME_DESC:
order = "[C].[Name] DESC";
break;
//---------------COURSE_NAME-----------------------
case Models.Constants.E_SORT_ORDER.LEARNER_NAME_ASC:
order = "[U].[Name] ASC";
break;
case Models.Constants.E_SORT_ORDER.LEARNER_NAME_DESC:
order = "[U].[Name] DESC";
break;
//---------------COURSE_NAME-----------------------
case Models.Constants.E_SORT_ORDER.LEARNER_MID_ASC:
order = "[U].[MID] ASC";
break;
case Models.Constants.E_SORT_ORDER.LEARNER_MID_DESC:
order = "[U].[MID] DESC";
break;
//---------------ACADEMY_NAME-----------------------
case Models.Constants.E_SORT_ORDER.ACADEMY_NAME_ASC:
order = "[C].[Academy] ASC";
break;
case Models.Constants.E_SORT_ORDER.ACADEMY_NAME_DESC:
order = "[C].[Academy] DESC";
break;
//---------------EVALUATION_DATE-----------------------
case Models.Constants.E_SORT_ORDER.EVALUATION_DATE_ASC:
order = "[R].[AssignmentDueDate] ASC";
break;
case Models.Constants.E_SORT_ORDER.EVALUATION_DATE_DESC:
order = "[R].[AssignmentDueDate] DESC";
break;
//---------------SUBMISSION_DATE-----------------------
case Models.Constants.E_SORT_ORDER.SUBMISSION_DATE_ASC:
order = "[A].[SubmissionDate] ASC";
break;
case Models.Constants.E_SORT_ORDER.SUBMISSION_DATE_DESC:
order = "[A].[SubmissionDate] DESC";
break;
//---------------SUBMISSION_DATE-----------------------
default:
order = "[R].[AssignmentDueDate] DESC";
break;
}
order = "ORDER BY " + order;
dynamicQuery.Append(" ");
dynamicQuery.Append(order);
dynamicQuery.Append(" ");
if (!isExport)
{
dynamicQuery.Append($"offset {requestSearchFilter.CurrentPage} rows fetch next {requestSearchFilter.PageSize} rows only;");
}
return(dynamicQuery.ToString());
}