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 UserListingResponse GetUserList(PagerItemsII parameter)
{
return(repositoryInstance.GetUserList(parameter));
}
public UserListingReportsList GetUsersList(AllUserListModel reportType, PagerItemsII parameter)
{
var result = new UserListingReportsList()
{
PagerResource = new PagerItems()
};
var orderByField = string.Empty;
var sql = new StringBuilder();
sql.Append("SELECT * FROM(");
sql.Append("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.ExpiredUserSortField.Username)
{
sql.Append(" UserName "); sortSql.Append("UserName ");
}
else if (column.Data == Constants.ExpiredUserSortField.CreationDate)
{
sql.Append(" CreationDate "); sortSql.Append("CreationDate ");
}
else if (column.Data == Constants.ExpiredUserSortField.Firstname)
{
sql.Append(" FirstName "); sortSql.Append("FirstName ");
}
else if (column.Data == Constants.ExpiredUserSortField.Lastname)
{
sql.Append(" LastName "); sortSql.Append("LastName ");
}
else if (column.Data == Constants.ExpiredUserSortField.Email)
{
sql.Append(" Email "); sortSql.Append("Email ");
}
sql.Append(column.SortDirection == 0 ? " asc" : " desc");
sortSql.Append(column.SortDirection == 0 ? " asc" : " desc");
}
#endregion
#region localCommandParams
var userNameFilter = string.Empty;
var firstNameFilter = string.Empty;
var lastNameFilter = string.Empty;
var eMailFilter = string.Empty;
DateTime creationDateTimeFromFilter = DateTime.Now;
DateTime creationDateTimeFromTo = DateTime.Now;
#endregion
#region commented
var whereClause = new StringBuilder();
//whereClause.Append(" WHERE ");
var globalFilter = string.Empty;
var filter = string.Empty;
#region old search
foreach (var column in parameter.SearchColumns)
{
if (column.Data == Constants.ExpiredUserSortField.CreationDate && 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))
{
creationDateTimeFromFilter = DateTime.Parse(start, Thread.CurrentThread.CurrentCulture.DateTimeFormat);
creationDateTimeFromFilter = new DateTime(creationDateTimeFromFilter.Year, creationDateTimeFromFilter.Month, creationDateTimeFromFilter.Day, 00, 00, 00);
whereClause.AppendFormat(" AND ( CreationDate >= @creationDateTimeFromFilter) ");
}
if (!string.IsNullOrEmpty(end))
{
creationDateTimeFromTo = DateTime.Parse(end, Thread.CurrentThread.CurrentCulture.DateTimeFormat);
creationDateTimeFromTo = new DateTime(creationDateTimeFromTo.Year, creationDateTimeFromTo.Month, creationDateTimeFromTo.Day, 23, 59, 59);
whereClause.AppendFormat(" AND ( CreationDate <= @creationDateTimeFromTo)");
}
}
else if (column.Data == Constants.ExpiredUserSortField.Username && !string.IsNullOrEmpty(column.Search.Value))
{
userNameFilter = column.Search.Value.Trim().Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
userNameFilter = string.Format("%{0}%", userNameFilter);
whereClause.Append(" AND (username like @userNameFilter) ");
}
else if (column.Data == Constants.ExpiredUserSortField.Firstname && !string.IsNullOrEmpty(column.Search.Value))
{
firstNameFilter = column.Search.Value.Trim().Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
firstNameFilter = string.Format("%{0}%", firstNameFilter);
whereClause.Append(" AND (Firstname like @firstNameFilter) ");
}
else if (column.Data == Constants.ExpiredUserSortField.Lastname && !string.IsNullOrEmpty(column.Search.Value))
{
lastNameFilter = column.Search.Value.Trim().Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
lastNameFilter = string.Format("%{0}%", lastNameFilter);
whereClause.Append(" AND (Lastname like @lastNameFilter) ");
}
else if (column.Data == Constants.ExpiredUserSortField.Email && !string.IsNullOrEmpty(column.Search.Value))
{
eMailFilter = column.Search.Value.Trim().Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
eMailFilter = string.Format("%{0}%", eMailFilter);
whereClause.Append(" AND (Email like @eMailFilter) ");
}
}
#endregion
//if (whereClause.Length > 7)
//{
// whereClause.Remove(whereClause.Length - 4, 4);
//}
if (!string.IsNullOrEmpty(parameter.siteSearch))
{
globalFilter = parameter.siteSearch.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
globalFilter = string.Format("%{0}%", globalFilter);
whereClause.Append(" OR ((username LIKE @GlobalSearchFilter) OR (email LIKE @GlobalSearchFilter)) ");
}
#endregion
sql.AppendLine(") AS NUMBER,UserName, FirstName, LastName, Email, IsFirstLogin, CreationDate, LastLogInDate, IsDormented, AccountExpiryDate, IsLockedOut ");
var strcountAppend = new StringBuilder();
switch (reportType.ReportTypeEnum)
{
case ReportTypeEnum.AllUsers:
{
sql.AppendLine("From [User] ");
break;
}
case ReportTypeEnum.NewUser:
{
sql.AppendLine("From [User] ");
sql.Append("WHERE (IsFirstLogin = '******' )");
strcountAppend.Append("WHERE (IsFirstLogin = '******' )");
break;
}
case ReportTypeEnum.ExpiredAccount:
{
sql.AppendLine("From [User] ");
sql.Append("WHERE ( AccountExpiryDate <= GETDATE() )");
strcountAppend.Append("WHERE ( AccountExpiryDate <= GETDATE() )");
break;
}
case ReportTypeEnum.DormantUser:
{
sql.AppendLine("From [User] ");
sql.Append("WHERE ((CreationDate < GETDATE()-" + portalSettingsRepository.GetSettingByKey(Constants.PortalSettingsKeysConstants.NEWUSERIDDORMANTNUMBERDAYS).Value + " AND LastLogInDate IS NULL) OR IsDormented = '1')");
strcountAppend.Append("WHERE ((CreationDate < GETDATE()-" + portalSettingsRepository.GetSettingByKey(Constants.PortalSettingsKeysConstants.NEWUSERIDDORMANTNUMBERDAYS).Value + " AND LastLogInDate IS NULL) OR IsDormented = '1')");
break;
}
case ReportTypeEnum.DisabledUser:
{
sql.AppendLine("From [User] ");
sql.Append("WHERE ( IsLockedOut = '1' )");
strcountAppend.Append("WHERE ( IsLockedOut = '1' )");
break;
}
}
//var whereClause = new StringBuilder();
////whereClause.Append(" WHERE ");
//var globalFilter = string.Empty;
//var filter = string.Empty;
//if (!string.IsNullOrWhiteSpace(parameter.siteSearch.Trim()))
//{
// globalFilter = parameter.siteSearch.Trim().Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
// globalFilter = string.Format("%{0}%", globalFilter);
// whereClause.Append(" and ((username LIKE @GlobalSearchFilter) OR (firstname LIKE @GlobalSearchFilter) OR (LastName LIKE @GlobalSearchFilter) OR (email LIKE @GlobalSearchFilter)) ");
//}
#endregion
if (reportType.ReportTypeEnum.ToString() == "AllUsers")
{
whereClause = CheckWhereClauseForAllUsers(ReportTypeEnum.AllUsers.ToString(), whereClause);
sql.AppendLine(whereClause.Length > 5 ? whereClause.ToString() : string.Empty);
sql.AppendLine(") AS TBL ");
}
else
{
sql.AppendLine(whereClause.Length > 5 ? whereClause.ToString() : string.Empty);
sql.AppendLine(") AS TBL ");
}
#region ReportType
#endregion
//sql.AppendFormat("{0}) AS TBL ", whereClause.Length > 7 ? whereClause.ToString() : string.Empty);
#region SearchParms
sql.AppendLine("WHERE NUMBER BETWEEN @StartPage AND @EndPage ");
sql.AppendFormat("ORDER BY {0} ", sortSql.ToString());
#region RecordsCount
result.PagerResource.ResultCount = (int)_context.Query <Int64>(
string.Format("Select Count(Username) From [User] {0}{1} ", strcountAppend, whereClause.Length > 7 ? whereClause.ToString() : string.Empty),
new
{
StartPage = ((parameter.PageNumber - 1) * parameter.PageSize) + 1,
EndPage = (parameter.PageNumber * parameter.PageSize),
GlobalSearchFilter = globalFilter,
UserNameFilter = userNameFilter,
FirstNameFilter = firstNameFilter,
LastNameFilter = lastNameFilter,
eMailFilter = eMailFilter,
creationDateTimeFromFilter = creationDateTimeFromFilter,
creationDateTimeFromTo = creationDateTimeFromTo
}).First();
var sqlQuery = sql.ToString();
result.UserLstResult = _context.Query <UserListingReports>(
string.Format(sqlQuery),
new
{
StartPage = ((parameter.PageNumber - 1) * parameter.PageSize) + 1,
EndPage = (parameter.PageNumber * parameter.PageSize),
GlobalSearchFilter = globalFilter,
UserNameFilter = userNameFilter,
FirstNameFilter = firstNameFilter,
LastNameFilter = lastNameFilter,
EMailFilter = eMailFilter,
CreationDateTimeFromFilter = creationDateTimeFromFilter,
CreationDateTimeFromTo = creationDateTimeFromTo
}).ToList();
return(result);
#endregion
#endregion
}
public AuditChangeListingResponse GetAuditChange(PagerItemsII auditChangeparameter)
{
return(repositoryInstance.GetAuditChange(auditChangeparameter));
}
//public void CreateAuditChange(object ValueBefore, object ValueAfter, IDbTransaction dbTransaction)
//{
// repositoryInstance.CreateAuditChange(ValueBefore, ValueAfter, dbTransaction: dbTransaction);
// //if (dbTransaction != null)
// //{
// // repositoryInstance.CreateAuditChange(ValueBefore, ValueAfter, dbTransaction: dbTransaction);
// //}
//}
//private void Audit(AuditTrail log)
// {
// var status = true;
// AuditLog(new AuditTrail()
// {
// ActionDateTime = Helper.GetLocalDate(),
// AuditAction = string.Format("Email Sending Error - {0}"),
// AuditMessage = MessageText,
// AuditPage = "MailSender",
// AuditType = "MailSender",
// AuditVersion = "MailSender 1.0",
// LoggedInUser = Helper.GetLoggedInUser(),
// ClientIPAddress = Helper.GetIPAddress()
// }, true);
// }
public AuditTrailListingResponse GetAuditList(PagerItemsII auditparameter)
{
return(repositoryInstance.GetAuditList(auditparameter));
}
public UserListingResponse GetUserList(PagerItemsII parameter)
{
//Better paging query which works for SQL Server 2012 or higher
//SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
//FROM TB_EXAMPLE
//ORDER BY ID_EXAMPLE
//OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
//FETCH NEXT @RowspPage ROWS ONLY;
var result = new UserListingResponse()
{
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();
foreach (var column in parameter.SortColumns)
{
sortSql.Append(sortSql.Length > 0 ? "," : "");
if (column.Data == Constants.SortField.Username)
{
sql.Append("u.Username ");
sortSql.Append("Username ");
}
else if (column.Data == Constants.SortField.Role)
{
sql.Append("r.RoleName ");
sortSql.Append("RoleName ");
}
else if (column.Data == Constants.SortField.FirstName)
{
sql.Append("FirstName ");
sortSql.Append("FirstName ");
}
else if (column.Data == Constants.SortField.LastName)
{
sql.Append("LastName ");
sortSql.Append("LastName ");
}
else if (column.Data == Constants.SortField.Email)
{
sql.Append("Email ");
sortSql.Append("Email ");
}
sql.Append(column.SortDirection == 0 ? " asc" : " desc");
sortSql.Append(column.SortDirection == 0 ? " asc" : " desc");
}
var whereClause = string.Empty;
var filter = string.Empty;
if (!string.IsNullOrEmpty(parameter.siteSearch))
{
filter = parameter.siteSearch.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
filter = string.Format("%{0}%", filter);
whereClause = (" and " +
"((u.Username LIKE @SearchFilter) OR" +
" (FirstName LIKE @SearchFilter) " +
"OR (LastName LIKE @SearchFilter) OR" +
" ( r.RoleName LIKE @SearchFilter) OR" +
" (Email LIKE @SearchFilter)) ");
}
sql.AppendLine(") AS NUMBER, u.Username, FirstName, LastName, Email, ApprovalStatus, IsLockedOut, InitiatedBy, u.IsDeleted, ");
sql.AppendLine("r.RoleId as UserRoleID, r.RoleName as UserRole ");
sql.AppendLine("From [User] u inner join UsersInRoles ur on ur.Username = u.Username inner join Role r on ur.RoleID = r.RoleID ");
sql.AppendFormat("WHERE (u.ApprovalStatus = 'Pending' or (u.ApprovalStatus = 'Approved' and u.IsDeleted = 0)) {0}) AS TBL ", whereClause);
sql.AppendLine();
sql.AppendLine("WHERE NUMBER BETWEEN @StartPage AND @EndPage ");
sql.AppendFormat("ORDER BY {0} ", sortSql.ToString());
//if (!string.IsNullOrEmpty(parameter.siteSearch))
//{
// filter = parameter.siteSearch.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
// filter = string.Format("%{0}%", filter);
// whereClause = (" WHERE ((u.Username LIKE @SearchFilter) OR (FirstName LIKE @SearchFilter) OR (LastName LIKE @SearchFilter) OR ( r.RoleName LIKE @SearchFilter) OR (Email LIKE @SearchFilter)) ");
//}
//sql.AppendLine(") AS NUMBER, u.Username, FirstName, LastName, Email, ApprovalStatus, IsLockedOut, ");
//sql.AppendLine("r.RoleId as UserRoleID, r.RoleName as UserRole ");
//sql.AppendLine("From [User] u inner join UsersInRoles ur on ur.Username = u.Username inner join Role r on ur.RoleID = r.RoleID ");
//sql.AppendFormat("{0}) AS TBL ", whereClause);
//sql.AppendLine();
//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(u.Username) From [User] u INNER JOIN UsersInRoles ur on ur.Username = u.Username INNER JOIN Role r on ur.RoleID = r.RoleID {0} WHERE (u.ApprovalStatus = 'Pending' or (u.ApprovalStatus = 'Approved' and u.IsDeleted = 0))", whereClause),
new
{
StartPage = ((parameter.PageNumber - 1) * parameter.PageSize) + 1,
EndPage = (parameter.PageNumber * parameter.PageSize),
SearchFilter = filter
}).First();
result.UserListingResult = context.Query <UserListingDto>(sql.ToString(),
new
{
StartPage = ((parameter.PageNumber - 1) * parameter.PageSize) + 1,
EndPage = (parameter.PageNumber * parameter.PageSize),
SearchFilter = filter
}).ToList();
return(result);
}
public UserListingReportsList GetUsersList(AllUserListModel reportType, PagerItemsII parameters)
{
UserListingReportsList UserListingReportListResponse = _reportsRepo.GetUsersList(reportType, parameters);
return(UserListingReportListResponse);
}
public ReportsExceptionListingResponse GetExceptionList(PagerItemsII parameter)
{
return(_reportsRepo.GetExceptionList(parameter));
}
