public JsonResult UserData([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel, string reportType)// string reportType
{
var defaultSortBy = Constants.SortField.Username;
var pagingParameter = MVCExtensionMethods.GetPagingParametersII(requestModel, defaultSortBy, Constants.SortOrder.Ascending.ToLower());
AllUserListModel model = new AllUserListModel();
if (reportType.ToLower() == ReportTypeEnum.DormantUser.ToString().ToLower())
{
model.ReportTypeEnum = ReportTypeEnum.DormantUser;
}
else if (reportType.ToLower() == ReportTypeEnum.NewUser.ToString().ToLower())
{
model.ReportTypeEnum = ReportTypeEnum.NewUser;
}
else if (reportType.ToLower() == ReportTypeEnum.DisabledUser.ToString().ToLower())
{
model.ReportTypeEnum = ReportTypeEnum.DisabledUser;
}
else if (reportType.ToLower() == ReportTypeEnum.ExpiredAccount.ToString().ToLower())
{
model.ReportTypeEnum = ReportTypeEnum.ExpiredAccount;
}
UserListingReportsList userListingReportsList = _reportService.GetUsersList(model, pagingParameter);
var data = userListingReportsList.UserLstResult;
return(Json(new DataTablesResponse((int)requestModel.Draw, data, userListingReportsList.PagerResource.ResultCount, userListingReportsList.PagerResource.ResultCount), JsonRequestBehavior.AllowGet));
}
public string GetExcepionMessageById(string id)
{
var result = new List <string>(); //new List<string>();
var sql = new StringBuilder();
sql.AppendLine("SELECT ExceptionDetails");
sql.AppendLine("From [exceptionlog] ");
sql.AppendLine("WHERE exceptionid = @exceptionid");
result = _context.Query <string>(sql.ToString(), new { ExceptionId = id }).ToList();
return(result.FirstOrDefault());
}
public UserListingReportsList GetUsersListSingleSearch(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.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;
#endregion
#region commented
#region old search Comment
//foreach (var column in parameter.siteSearch)
//{
// if (column.Data == Constants.ExpiredUserSortField.Username && !string.IsNullOrEmpty(column.Search.Value))
// {
// userNameFilter = column.Search.Value.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
// userNameFilter = string.Format("%{0}%", userNameFilter);
// whereClause.Append(" (username like @userNameFilter) AND ");
// }
// else if (column.Data == Constants.ExpiredUserSortField.Firstname && !string.IsNullOrEmpty(column.Search.Value))
// {
// firstNameFilter = column.Search.Value.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
// firstNameFilter = string.Format("%{0}%", firstNameFilter);
// whereClause.Append(" (username like @firstNameFilter) AND ");
// }
// else if (column.Data == Constants.ExpiredUserSortField.Lastname && !string.IsNullOrEmpty(column.Search.Value))
// {
// lastNameFilter = column.Search.Value.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
// lastNameFilter = string.Format("%{0}%", lastNameFilter);
// whereClause.Append(" (username like @lastNameFilter) AND ");
// }
// else if (column.Data == Constants.ExpiredUserSortField.Email && !string.IsNullOrEmpty(column.Search.Value))
// {
// eMailFilter = column.Search.Value.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
// eMailFilter = string.Format("%{0}%", eMailFilter);
// whereClause.Append(" (username like @eMailFilter) AND ");
// }
//}
#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 ");
sql.AppendLine("From [User] ");
var strcountAppend = new StringBuilder();
switch (reportType.ReportTypeEnum)
{
case ReportTypeEnum.NewUser:
{
sql.Append("WHERE (IsFirstLogin = '******' )");
strcountAppend.Append("WHERE (IsFirstLogin = '******' )");
break;
}
case ReportTypeEnum.ExpiredAccount:
{
sql.Append("WHERE ( AccountExpiryDate <= GETDATE() )");
strcountAppend.Append("WHERE ( AccountExpiryDate <= GETDATE() )");
break;
}
case ReportTypeEnum.DormantUser:
{
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.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
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
}).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
}).ToList();
return(result);
#endregion
#endregion
}
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 UserListingReportsList GetUsersList(AllUserListModel reportType, PagerItemsII parameters)
{
UserListingReportsList UserListingReportListResponse = _reportsRepo.GetUsersList(reportType, parameters);
return(UserListingReportListResponse);
}