Exemple #1
0
 public async Task<JsonResult> DriveDataTable(jQueryDataTableParamModel param)
 {
     return Json(await repo.GetPagedAspNetUserItems(param));
 }
Exemple #2
0
 public async Task<JsonResult> DriveDataTable(jQueryDataTableParamModel param)
 {
     return Json(await repo.GetPagedTopEntitiesItems(param));
 }
Exemple #3
0
        public async Task<object> GetPagedAssignmentItems(jQueryDataTableParamModel param)
        {
            var assignmentsCount = await db.Assignments.CountAsync();
            var assignments = from r in db.Assignments
                               select r;

            if (param.searchColumn != null)
            {
                if (param.searchColumn.Length > 0 && !String.IsNullOrWhiteSpace(param.searchColumn[0]))
                {
                    var temp = param.searchColumn[0].ToUpper();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        assignments = assignments.Where(r => SqlFunctions.PatIndex(temp, r.AssignmentId.ToString()) > 0);
                    else if (temp.StartsWith("<"))
                    {
                        int tempToNumber = 0;
                        Int32.TryParse(temp.Substring(1), out tempToNumber);
                        assignments = assignments.Where(r => r.AssignmentId < tempToNumber);
                    }
                    else if (temp.StartsWith(">"))
                    {
                        int tempToNumber = 0;
                        Int32.TryParse(temp.Substring(1), out tempToNumber);
                        assignments = assignments.Where(r => r.AssignmentId > tempToNumber);
                    }
                    else if (temp.StartsWith("~"))
                        assignments = assignments.Where(r => r.AssignmentId.ToString().ToLower() != temp.Substring(1));
                    else
                        assignments = assignments.Where(r => r.AssignmentId.ToString().ToUpper() == temp);

                }
                if (param.searchColumn.Length > 1 && !String.IsNullOrWhiteSpace(param.searchColumn[1]))
                {
                    var temp = param.searchColumn[1].ToLower();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        assignments = assignments.Where(r => SqlFunctions.PatIndex(temp, r.UserId.ToString().ToLower()) > 0);
                    
                    else if (temp.StartsWith("~"))
                        assignments = assignments.Where(r => r.UserId.ToString().ToLower() != temp.Substring(1));
                    else
                        assignments = assignments.Where(r => r.UserId.ToString().ToLower() == temp);

                }

                if (param.searchColumn.Length > 2 && !String.IsNullOrWhiteSpace(param.searchColumn[2]))
                {
                    var temp = param.searchColumn[2].ToUpper();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        assignments = assignments.Where(r => SqlFunctions.PatIndex(temp, r.Key) > 0);
                    else if (temp.StartsWith("<"))
                        assignments = assignments.Where(r => String.Compare(r.Key.ToUpper(), temp.Substring(1)) < 0);
                    else if (temp.StartsWith(">"))
                        assignments = assignments.Where(r => String.Compare(r.Key.ToUpper(), temp.Substring(1)) > 0);
                    else if (temp.StartsWith("~"))
                        assignments = assignments.Where(r => r.Key.ToUpper() != temp.Substring(1));
                    else
                        assignments = assignments.Where(r => r.Key.ToUpper() == temp);
                }
            }

            switch (param.orderByCol)
            {
                case 0:
                    assignments = param.orderDirection == "asc" ? assignments.OrderBy(r => r.AssignmentId) : assignments.OrderByDescending(r => r.AssignmentId);
                    break;
                case 1:
                    assignments = param.orderDirection == "asc" ? assignments.OrderBy(r => r.UserId) : assignments.OrderByDescending(r => r.UserId);
                    break;
                case 2:
                    assignments = param.orderDirection == "asc" ? assignments.OrderBy(r => r.Key) : assignments.OrderByDescending(r => r.Key);
                    break;
                
            }
            var filter = assignments;
            var filterCount = await filter.CountAsync();
            assignments = assignments.Skip(param.start).Take(param.length);
            var data = await assignments.ToListAsync();
            var dataFormatted = data.Select(x => new string[]  {
                x.AssignmentId.ToString(),
                x.UserId.ToString(),
                x.Key,                
                ""
             });
            return new
            {
                recordsTotal = assignmentsCount,
                recordsFiltered = filterCount,
                data = dataFormatted
            };
        }
Exemple #4
0
        public async Task<object> GetPagedAspNetUserItems(jQueryDataTableParamModel param)
        {
            var usersCount = await db.AspNetUsers.CountAsync();

            var users = from r in db.AspNetUsers
                        select r;

            if (param.searchColumn != null)
            {
                if (param.searchColumn.Length > 0 && !String.IsNullOrWhiteSpace(param.searchColumn[0]))
                {
                    var temp = param.searchColumn[0].ToLower();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        users = users.Where(r => SqlFunctions.PatIndex(temp, r.Id.ToLower()) > 0);
                    else if (temp.StartsWith("<"))
                        users = users.Where(r => String.Compare(r.Id.ToLower(), temp.Substring(1)) < 0);
                    else if (temp.StartsWith(">"))
                        users = users.Where(r => String.Compare(r.Id.ToLower(), temp.Substring(1)) > 0);
                    else if (temp.StartsWith("~"))
                        users = users.Where(r => r.Id.ToLower() != temp.Substring(1));
                    else
                        users = users.Where(r => r.Id.ToLower() == temp);

                }
                if (param.searchColumn.Length > 1 && !String.IsNullOrWhiteSpace(param.searchColumn[1]))
                {
                    var temp = param.searchColumn[1].ToLowerInvariant();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        users = users.Where(r => SqlFunctions.PatIndex(temp, r.UserName) > 0);
                    else if (temp.StartsWith("<"))
                        users = users.Where(r => String.Compare(r.UserName.ToLower(), temp.Substring(1)) < 0);
                    else if (temp.StartsWith(">"))
                        users = users.Where(r => String.Compare(r.UserName.ToLower(), temp.Substring(1)) > 0);
                    else if (temp.StartsWith("~"))
                        users = users.Where(r => r.UserName.ToLower() != temp.Substring(1));
                    else
                        users = users.Where(r => r.UserName.ToLower() == temp);

                }
                if (param.searchColumn.Length > 2 && !String.IsNullOrWhiteSpace(param.searchColumn[2]))
                {
                    var temp = param.searchColumn[2].ToLower();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        users = users.Where(r => SqlFunctions.PatIndex(temp, r.Email) > 0);
                    else if (temp.StartsWith("<"))
                        users = users.Where(r => String.Compare(r.Email.ToLower(), temp.Substring(1)) < 0);
                    else if (temp.StartsWith(">"))
                        users = users.Where(r => String.Compare(r.Email.ToLower(), temp.Substring(1)) > 0);
                    else if (temp.StartsWith("~"))
                        users = users.Where(r => r.Email.ToLower() != temp.Substring(1));
                    else
                        users = users.Where(r => r.Email.ToLower() == temp);

                }
                if (param.searchColumn.Length > 3 && !String.IsNullOrWhiteSpace(param.searchColumn[3]))
                {
                    var temp = param.searchColumn[3].ToLower();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        users = users.Where(r => SqlFunctions.PatIndex(temp, r.PhoneNumber) > 0);
                    else if (temp.StartsWith("<"))
                        users = users.Where(r => String.Compare(r.PhoneNumber.ToLower(), temp.Substring(1)) < 0);
                    else if (temp.StartsWith(">"))
                        users = users.Where(r => String.Compare(r.PhoneNumber.ToLower(), temp.Substring(1)) > 0);
                    else if (temp.StartsWith("~"))
                        users = users.Where(r => r.PhoneNumber.ToLower() != temp.Substring(1));
                    else
                        users = users.Where(r => r.PhoneNumber.ToLower() == temp);

                }
                if (param.searchColumn.Length > 4 && !String.IsNullOrWhiteSpace(param.searchColumn[4]))
                {
                    var temp = param.searchColumn[4].ToLower();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        users = users.Where(r => SqlFunctions.PatIndex(temp, r.AccessFailedCount.ToString()) > 0);
                    else if (temp.StartsWith("<"))
                    {
                        int tempToNumber = 0;
                        Int32.TryParse(temp.Substring(1), out tempToNumber);
                        users = users.Where(r => r.AccessFailedCount < tempToNumber);
                    }
                    else if (temp.StartsWith(">"))
                    {
                        int tempToNumber = 0;
                        Int32.TryParse(temp.Substring(1), out tempToNumber);
                        users = users.Where(r => r.AccessFailedCount > tempToNumber);
                    }
                    else if (temp.StartsWith("~"))
                    {
                        users = users.Where(r => r.AccessFailedCount.ToString() != temp.Substring(1));
                    }
                    else
                        users = users.Where(r => r.AccessFailedCount.ToString().ToLower().Contains(temp));

                }
            }

            switch (param.orderByCol)
            {
                case 0:
                    users = param.orderDirection == "asc" ? users.OrderBy(r => r.Id) : users.OrderByDescending(r => r.Id);
                    break;
                case 1:
                    users = param.orderDirection == "asc" ? users.OrderBy(r => r.UserName) : users.OrderByDescending(r => r.UserName);
                    break;
                case 2:
                    users = param.orderDirection == "asc" ? users.OrderBy(r => r.Email) : users.OrderByDescending(r => r.Email);
                    break;
                case 3:
                    users = param.orderDirection == "asc" ? users.OrderBy(r => r.PhoneNumber) : users.OrderByDescending(r => r.PhoneNumber);
                    break;
                case 4:
                    users = param.orderDirection == "asc" ? users.OrderBy(r => r.AccessFailedCount) : users.OrderByDescending(r => r.AccessFailedCount);
                    break;

            }
            var filter = users;
            var filterCount = await filter.CountAsync();
            users = users.Skip(param.start).Take(param.length);
            var data = await users.ToListAsync();
            var dataFormatted = data.Select(x => new string[]  {
                x.Id,
                x.UserName,
                x.Email,
                x.PhoneNumber,
                x.AccessFailedCount.ToString(),
                ""
             });
            return new
            {
                recordsTotal = usersCount,
                recordsFiltered = filterCount,
                data = dataFormatted
            };
        }
Exemple #5
0
        public async Task<object> GetPagedTopEntitiesItems(jQueryDataTableParamModel param)
        {
            var rAdminCount = await db.Entities.CountAsync();
            var rAdmin = from r in db.Entities
                         select r;

            if (param.searchColumn != null)
            {
                if (param.searchColumn.Length > 0 && !String.IsNullOrWhiteSpace(param.searchColumn[0]))
                {
                    var temp = param.searchColumn[0].ToLower();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        rAdmin = rAdmin.Where(r => SqlFunctions.PatIndex(temp, r.Key.ToLower()) > 0);
                    else if (temp.StartsWith("<"))
                        rAdmin = rAdmin.Where(r => String.Compare(r.Key.ToLower(), temp.Substring(1)) < 0);
                    else if (temp.StartsWith(">"))
                        rAdmin = rAdmin.Where(r => String.Compare(r.Key.ToLower(), temp.Substring(1)) > 0);
                    else if (temp.StartsWith("~"))
                        rAdmin = rAdmin.Where(r => r.Key.ToLower() != temp.Substring(1));
                    else
                        rAdmin = rAdmin.Where(r => r.Key.ToLower() == temp);

                }
                if (param.searchColumn.Length > 1 && !String.IsNullOrWhiteSpace(param.searchColumn[1]))
                {
                    var temp = param.searchColumn[1].ToLowerInvariant();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        rAdmin = rAdmin.Where(r => SqlFunctions.PatIndex(temp, r.Attributes) > 0);
                    else if (temp.StartsWith("<"))
                        rAdmin = rAdmin.Where(r => String.Compare(r.Attributes.ToLower(), temp.Substring(1)) < 0);
                    else if (temp.StartsWith(">"))
                        rAdmin = rAdmin.Where(r => String.Compare(r.Attributes.ToLower(), temp.Substring(1)) > 0);
                    else if (temp.StartsWith("~"))
                        rAdmin = rAdmin.Where(r => r.Attributes.ToLower() != temp.Substring(1));
                    else
                        rAdmin = rAdmin.Where(r => r.Attributes.ToLower() == temp);

                }
                if (param.searchColumn.Length > 2 && !String.IsNullOrWhiteSpace(param.searchColumn[2]))
                {
                    var temp = param.searchColumn[2].ToLowerInvariant();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        rAdmin = rAdmin.Where(r => SqlFunctions.PatIndex(temp, r.Type) > 0);
                    else if (temp.StartsWith("<"))
                        rAdmin = rAdmin.Where(r => String.Compare(r.Type.ToLower(), temp.Substring(1)) < 0);
                    else if (temp.StartsWith(">"))
                        rAdmin = rAdmin.Where(r => String.Compare(r.Type.ToLower(), temp.Substring(1)) > 0);
                    else if (temp.StartsWith("~"))
                        rAdmin = rAdmin.Where(r => r.Type.ToLower() != temp.Substring(1));
                    else
                        rAdmin = rAdmin.Where(r => r.Type.ToLower() == temp);

                }

                if (param.searchColumn.Length > 3 && !String.IsNullOrWhiteSpace(param.searchColumn[3]))
                {
                    var temp = param.searchColumn[3].ToUpper();
                    if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
                        rAdmin = rAdmin.Where(r => SqlFunctions.PatIndex(temp, r.SortKey.ToString()) > 0);
                    else if (temp.StartsWith("<"))
                    {
                        int tempToNumber = 0;
                        Int32.TryParse(temp.Substring(1), out tempToNumber);
                        rAdmin = rAdmin.Where(r => r.SortKey < tempToNumber);
                    }
                    else if (temp.StartsWith(">"))
                    {
                        int tempToNumber = 0;
                        Int32.TryParse(temp.Substring(1), out tempToNumber);
                        rAdmin = rAdmin.Where(r => r.SortKey > tempToNumber);
                    }
                    else if (temp.StartsWith("~"))
                        rAdmin = rAdmin.Where(r => r.SortKey.ToString().ToLower() != temp.Substring(1));
                    else
                        rAdmin = rAdmin.Where(r => r.SortKey.ToString().ToUpper() == temp);

                }


            }

            switch (param.orderByCol)
            {
                case 0:
                    rAdmin = param.orderDirection == "asc" ? rAdmin.OrderBy(r => r.Key) : rAdmin.OrderByDescending(r => r.Key);
                    break;
                case 1:
                    rAdmin = param.orderDirection == "asc" ? rAdmin.OrderBy(r => r.Attributes) : rAdmin.OrderByDescending(r => r.Attributes);
                    break;
                case 2:
                    rAdmin = param.orderDirection == "asc" ? rAdmin.OrderBy(r => r.Type) : rAdmin.OrderByDescending(r => r.Type);
                    break;
                case 3:
                    rAdmin = param.orderDirection == "asc" ? rAdmin.OrderBy(x => x.SortKey) : rAdmin.OrderByDescending(x => x.SortKey);
                    break;
            }
            var filter = rAdmin;
            var filterCount = await filter.CountAsync();
            rAdmin = rAdmin.Skip(param.start).Take(param.length);
            var data = await rAdmin.ToListAsync();
            var dataFormatted = data.Select(x => new string[]  {
                x.Key,
                x.Attributes != null? ( x.Attributes.Length > 100 ? x.Attributes.Substring(0,100) + " ...": x.Attributes):"" ,
                x.Type,
                x.SortKey.ToString(),
                ""
             });
            return new
            {
                recordsTotal = rAdminCount,
                recordsFiltered = filterCount,
                data = dataFormatted
            };
        }