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