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);
}
/// <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);
}
}