public JsonResult ListExceptionData([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
{
var defaultSortBy = Constants.SortField.Username;
var pagingParameter = MVCExtensionMethods.GetPagingParametersII(requestModel, defaultSortBy, Constants.SortOrder.Ascending.ToLower());
ReportsExceptionListingResponse exceptionList = _reportService.GetExceptionList(pagingParameter);
var data = exceptionList.UserListingResult;
return(Json(new DataTablesResponse((int)requestModel.Draw, data, exceptionList.PagerResource.ResultCount, exceptionList.PagerResource.ResultCount), JsonRequestBehavior.AllowGet));
}
public ReportsExceptionListingResponse GetExceptionList(PagerItemsII parameter)
{
var result = new ReportsExceptionListingResponse()
{
PagerResource = new PagerItems()
};
var orderByField = string.Empty;
var sql = new StringBuilder();
sql.AppendLine("SELECT * FROM (");
sql.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY ");
var sortSql = new StringBuilder();
#region sortColumns
foreach (var column in parameter.SortColumns)
{
sortSql.Append(sortSql.Length > 0 ? "," : "");
if ((column.Data == "0") || column.Data == Constants.ExceptionSortField.ExceptionId)
{
sql.Append("ExceptionId ");
sortSql.Append("ExceptionId ");
}
else if (column.Data == Constants.ExceptionSortField.ExceptionDateTime)
{
sql.Append("ExceptionDateTime ");
sortSql.Append("ExceptionDateTime ");
}
else if (column.Data == Constants.ExceptionSortField.ExceptionPage)
{
sql.Append("ExceptionPage ");
sortSql.Append("ExceptionPage ");
}
else if (column.Data == Constants.ExceptionSortField.LoggedInUser)
{
sql.Append("LoggedInUser ");
sortSql.Append("LoggedInUser ");
}
else if (column.Data == Constants.ExceptionSortField.ExceptionType)
{
sql.Append("ExceptionType ");
sortSql.Append("ExceptionType ");
}
else if (column.Data == Constants.ExceptionSortField.ExceptionMessage)
{
sql.Append("ExceptionMessage ");
sortSql.Append("ExceptionMessage ");
}
else if (column.Data == Constants.ExceptionSortField.ExceptionVersion)
{
sql.Append("ExceptionVersion ");
sortSql.Append("ExceptionVersion ");
}
sql.Append(column.SortDirection == 0 ? " asc" : " desc");
sortSql.Append(column.SortDirection == 0 ? " asc" : " desc");
}
#endregion
#region localParams
var exceptionIdFilter = string.Empty;
var exceptionTypeFilter = string.Empty;
DateTime exceptionDateFromFilter = DateTime.Now.AddDays(-365);
DateTime exceptionDateTimeToFilter = DateTime.Now;
#endregion
var whereClause = new StringBuilder();
whereClause.Append(" WHERE ");
var filter = string.Empty;
foreach (var column in parameter.SearchColumns)
{
if (column.Data == Constants.ExceptionSortField.ExceptionDateTime && column.Search.Value != Constants.General.YadcfDelimiter)
{
var dateFilter = column.Search.Value.Split(Constants.General.YadcfDelimiter.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
var start = column.Search.Value.StartsWith(Constants.General.YadcfDelimiter) ? string.Empty : dateFilter[0];
var end = column.Search.Value.EndsWith(Constants.General.YadcfDelimiter) ? string.Empty : dateFilter.Length > 1 ? dateFilter[1] : dateFilter[0];
if (!string.IsNullOrEmpty(start))
{
exceptionDateFromFilter = DateTime.Parse(start, Thread.CurrentThread.CurrentCulture.DateTimeFormat);
exceptionDateFromFilter = new DateTime(exceptionDateFromFilter.Year, exceptionDateFromFilter.Month, exceptionDateFromFilter.Day, 00, 00, 00);
whereClause.AppendFormat("(ExceptionDateTime >= @exceptionDateFromFilter) AND ");
}
if (!string.IsNullOrEmpty(end))
{
exceptionDateTimeToFilter = DateTime.Parse(end, Thread.CurrentThread.CurrentCulture.DateTimeFormat);
exceptionDateTimeToFilter = new DateTime(exceptionDateFromFilter.Year, exceptionDateTimeToFilter.Month, exceptionDateTimeToFilter.Day, 23, 59, 59);
whereClause.AppendFormat("(ExceptionDateTime <= @exceptionDateToFilter) AND ");
}
}
else if (column.Data == Constants.ExceptionSortField.ExceptionId && !string.IsNullOrEmpty(column.Search.Value))
{
exceptionIdFilter = column.Search.Value.Trim().Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
exceptionIdFilter = string.Format("%{0}%", exceptionIdFilter);
whereClause.Append(" (exceptionId LIKE @exceptionIdFilter) AND ");
}
else if (column.Data == Constants.ExceptionSortField.ExceptionMessage && !string.IsNullOrEmpty(column.Search.Value))
{
exceptionTypeFilter = column.Search.Value.Trim().Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
exceptionTypeFilter = string.Format("%{0}%", exceptionTypeFilter);
whereClause.Append(" (ExceptionMessage LIKE @ExceptionTypeFilter) AND ");
}
}
if (whereClause.Length > 7)
{
whereClause.Remove(whereClause.Length - 4, 4);
}
var globalFilter = string.Empty;
if (!string.IsNullOrEmpty(parameter.siteSearch))
{
globalFilter = parameter.siteSearch.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
globalFilter = string.Format("%{0}%", globalFilter);
whereClause.Append(" OR ((ExceptionID LIKE @GlobalSearchFilter) OR (ExceptionType LIKE @GlobalSearchFilter)) ");
}
sql.AppendLine(") AS NUMBER, ExceptionId, ExceptionDateTime, ExceptionDetails, ExceptionPage, LoggedInUser, ExceptionType, ExceptionMessage, ExceptionVersion ");
sql.AppendLine("From [exceptionlog] ");
sql.AppendFormat("{0}) AS TBL ", whereClause.Length > 7 ? whereClause.ToString() : string.Empty);
sql.AppendLine("WHERE NUMBER BETWEEN @StartPage AND @EndPage ");
sql.AppendFormat("ORDER BY {0} ", sortSql.ToString());
result.PagerResource.ResultCount = (int)_context.Query <Int64>(
string.Format("Select Count(ExceptionId) From [exceptionlog] {0} ", whereClause.Length > 7 ? whereClause.ToString() : string.Empty),
new
{
StartPage = ((parameter.PageNumber - 1) * parameter.PageSize) + 1,
EndPage = (parameter.PageNumber * parameter.PageSize),
GlobalSearchFilter = globalFilter,
ExceptionIDFilter = exceptionIdFilter,
ExceptionTypeFilter = exceptionTypeFilter,
ExceptionDateFromFilter = exceptionDateFromFilter,
exceptionDateToFilter = exceptionDateTimeToFilter
}).First();
result.UserListingResult = _context.Query <ReportsExceptionDto>(sql.ToString(), //+ whereClause,
new
{
StartPage = ((parameter.PageNumber - 1) * parameter.PageSize) + 1,
EndPage = (parameter.PageNumber * parameter.PageSize),
GlobalSearchFilter = globalFilter,
ExceptionIDFilter = exceptionIdFilter,
ExceptionTypeFilter = exceptionTypeFilter,
ExceptionDateFromFilter = exceptionDateFromFilter,
ExceptionDateToFilter = exceptionDateTimeToFilter
}).ToList();
return(result);
}
public ActionResult ExceptionList()
{
var exceptionList = new ReportsExceptionListingResponse();
return(View(exceptionList));
}
