public int EditUser(Model.User user)
{
AuditRepository ar = new AuditRepository(dapperContext);
var dbVersion = GetUser(user.Username);
SetDormentStatus(dbVersion);
SetMissingValues(user, dbVersion);//, new string[] { "Password", "ConfirmPassword" });
//Get The NewUser and ActiveUser DormentDays
int newUserDormentDays = 0, activeUserDormentDays = 0, accountExpiryDays;
int.TryParse(portalSettingsRepository.GetSettingByKey(Constants.PortalSettingsKeysConstants.NEWUSERIDDORMANTNUMBERDAYS).Value, out newUserDormentDays);
int.TryParse(portalSettingsRepository.GetSettingByKey(Constants.PortalSettingsKeysConstants.ACTIVEUSERIDDORMANTNUMBERDAYS).Value, out activeUserDormentDays);
int.TryParse(portalSettingsRepository.GetSettingByKey(Constants.PortalSettingsKeysConstants.ACCOUNTEXPIRYNUMBERDAYS).Value, out accountExpiryDays);
IDbTransaction dbTransaction = dapperContext.GetTransaction();
//To handle maker checker functions
//maker checker can return the db version of an object depending on user action
user = ar.MakerCheckerHandller <User>(dbVersion, user, Constants.OperationType.Edit, Constants.Modules.UserSetup, user.Username, user.Username, dbTransaction);
UpdateEditedStatus(dbVersion, user, newUserDormentDays, activeUserDormentDays, accountExpiryDays);
var sql = ("UPDATE [User] SET CreationDate=@CreationDate, Email = @Email, FirstName = @FirstName, LastName = @LastName, Username = @Username, Telephone = @Telephone, Initial = @Initial, ApprovalStatus = @ApprovalStatus, ApprovedBy = @ApprovedBy, InitiatedBy = @InitiatedBy, ApprovalLogID = @ApprovalLogID, BadPasswordCount = @BadPasswordCount, IsDeleted = @IsDeleted, isAccountExpired = @IsAccountExpired, IsDormented = @IsDormented,IsLockedOut = @IsLockedOut, AccountExpiryDate = @AccountExpiryDate, LastLogInDate = @LastLogInDate Where Username = @Username AND CONVERT(bigint,RowVersionNo) = @RowVersionNo2;");
var rowAffected = context.Execute(sql.ToString(), user, transaction: dbTransaction);
sql = ("UPDATE UsersInRoles SET RoleId = @RoleId, IsDeleted = @IsDeleted WHERE Username = @Username;");
context.Execute(sql.ToString(), user, transaction: dbTransaction);
//To Create an Audit Record
ar.CreateAuditChange(dbVersion, user, dbTransaction, user.Username,
new string[] { "Email", "FirstName", "LastName", "Username", "Telephone",
"Initial", "ApprovalStatus", "ApprovedBy", "InitiatedBy",
"ApprovalLogID", "IsDeleted", "UserRole.RoleName",
"IsDormented", "IsAccountExpired", "IsLockedOut", "IsDeleted",
"CreationDate", "LastLogInDate", "AccountExpiryDate" }
);
dapperContext.CommitTransaction();
//if transaction commits, then send notification
return(rowAffected);
}
public bool IsRepeatingPassword(PasswordHistoryModel passwordHistoryModel, out int unUsablePreviousPasswordCount)
{
int constValue = 0;
int.TryParse(Constants.PortalSettingsKeyFallBackValues.UNUSABLEPREVIOUSPASSWORDSNUMBER, out constValue);
unUsablePreviousPasswordCount = constValue;
PortalSetting portalSetting = portalSettingsRepository.GetSettingByKey(Constants.PortalSettingsKeysConstants.UNUSABLEPREVIOUSPASSWORDSNUMBER);
if (!string.IsNullOrWhiteSpace(portalSetting.Value))
{
int.TryParse(portalSetting.Value, out unUsablePreviousPasswordCount);
if (unUsablePreviousPasswordCount == 0)
{
unUsablePreviousPasswordCount = constValue;
}
}
return(passwordHistoryRepository.IsRepeatingPassword(passwordHistoryModel, unUsablePreviousPasswordCount));
}
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
}