예제 #1
0
        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));
        }
예제 #2
0
        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());
        }
예제 #3
0
        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
        }
예제 #4
0
        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
        }
예제 #5
0
        public UserListingReportsList GetUsersList(AllUserListModel reportType, PagerItemsII parameters)
        {
            UserListingReportsList UserListingReportListResponse = _reportsRepo.GetUsersList(reportType, parameters);

            return(UserListingReportListResponse);
        }