public ActionResult ListOfIssues()
{
bool hasAccess = workerroleactionpermissionnewRepository.HasPermission(CurrentLoggedInWorkerRoleIDs, Constants.Areas.Reporting, Constants.Controllers.Report, Constants.Actions.ListOfIssues, true);
if (!hasAccess)
{
WebHelper.CurrentSession.Content.ErrorMessage = "You are not eligible to do this action";
return(RedirectToAction(Constants.Actions.AccessDenied, Constants.Controllers.Home, new { Area = String.Empty }));
}
ListOfIssuesVM model = new ListOfIssuesVM();
return(View(model));
}
public DataTable ListOfIssues(ListOfIssuesVM model)
{
string constr = ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString;
StringBuilder sqlquery = new StringBuilder();
sqlquery.Append("SELECT CM.CaseID AS FamilyID,C.EnrollDate AS EnrollmentDate,R.Name AS Region,JK.Name AS Jamatkhana ");
sqlquery.Append(",CM.ID AS MemberID, CM.LastUpdateDate AS MemberLastUpdateDate,QC.Name AS QOLCategory ");
sqlquery.Append(",QS.Name AS QOLSubCategory,Q.Name AS QOLIssue ");
sqlquery.Append("FROM [Case] AS C ");
sqlquery.Append("INNER JOIN CaseMember AS CM ON C.ID = CM.CaseID ");
sqlquery.Append("INNER JOIN Region AS R ON C.RegionID = R.ID ");
sqlquery.Append("INNER JOIN CaseAssessment AS CAS ON CM.ID = CAS.CaseMemberID ");
sqlquery.Append("INNER JOIN CaseAssessmentLivingCondition AS CAL ON CAS.ID = CAL.CaseAssessmentID ");
sqlquery.Append("INNER JOIN QualityOfLife AS Q ON CAL.QualityOfLifeID = Q.ID ");
sqlquery.Append("INNER JOIN QualityOfLifeSubCategory AS QS ON Q.QualityOfLifeSubCategoryID = QS.ID ");
sqlquery.Append("INNER JOIN QualityOfLifeCategory AS QC ON QS.QualityOfLifeCategoryID = QC.ID ");
sqlquery.Append("LEFT JOIN Jamatkhana AS JK ON C.JamatkhanaID = JK.ID ");
sqlquery.Append("WHERE C.EnrollDate BETWEEN @FromDate AND @ToDate ");
sqlquery.Append("ORDER BY C.EnrollDate");
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sqlquery.ToString()))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
cmd.Parameters.Add(new SqlParameter("@FromDate", model.StartDate));
cmd.Parameters.Add(new SqlParameter("@ToDate", model.EndDate));
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return(dt);
}
}
}
}
}
public ActionResult ListOfIssues(ListOfIssuesVM model)
{
bool hasAccess = workerroleactionpermissionnewRepository.HasPermission(CurrentLoggedInWorkerRoleIDs, Constants.Areas.Reporting, Constants.Controllers.Report, Constants.Actions.ListOfIssues, true);
if (!hasAccess)
{
WebHelper.CurrentSession.Content.ErrorMessage = "You are not eligible to do this action";
return(RedirectToAction(Constants.Actions.AccessDenied, Constants.Controllers.Home, new { Area = String.Empty }));
}
try
{
if (ModelState.IsValid)
{
DataTable table = reportRepository.ListOfIssues(model);
using (XLWorkbook wb = new XLWorkbook())
{
table.TableName = "ListOfIssues";
//Add DataTable as Worksheet.
wb.Worksheets.Add(table);
//Export the Excel file.
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=ListOfIssues.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
else
{
foreach (var modelStateValue in ViewData.ModelState.Values)
{
foreach (var error in modelStateValue.Errors)
{
model.ErrorMessage = error.ErrorMessage;
break;
}
if (model.ErrorMessage.IsNotNullOrEmpty())
{
break;
}
}
}
}
catch (CustomException ex)
{
model.ErrorMessage = ex.UserDefinedMessage;
}
catch (Exception ex)
{
ExceptionManager.Manage(ex);
model.ErrorMessage = Constants.Messages.UnhandelledError;
}
return(View(model));
//RedirectToAction("ListOfIssues", model);
}