public AnexGRIDResponde Listar(AnexGRID grid, int usuario_id)
{
try
{
using (var ctx = new PortafolioContext())
{
ctx.Configuration.LazyLoadingEnabled = false;
grid.Inicializar();
var query = ctx.Habilidad.Where(x => x.Usuario_id == usuario_id);
// ordenamineto
if (grid.columna == "id")
{
query = grid.columna_orden == "DESC" ? query.OrderByDescending(x => x.id) : query.OrderBy(x => x.id);
}
if (grid.columna == "Nombre")
{
query = grid.columna_orden == "DESC" ? query.OrderByDescending(x => x.Nombre) : query.OrderBy(x => x.Nombre);
}
if (grid.columna == "Dominio")
{
query = grid.columna_orden == "DESC" ? query.OrderByDescending(x => x.Dominio) : query.OrderBy(x => x.Dominio);
}
// id, nombre, titulo, desde, hasta
var Habilidad = query.Skip(grid.pagina).Take(grid.limite).ToList();
var total = query.Count();
grid.SetData(Habilidad, total);
}
}
catch (Exception)
{
throw;
}
return(grid.responde());
}
public AnexGRIDResponde GetAll(AnexGRID grid)
{
grid.Inicializar();
try
{
using (var ctx = _dbContextScopeFactory.CreateReadOnly())
{
var user = ctx.GetEntity <ApplicationUser>();
var roles = ctx.GetEntity <ApplicationRole>();
var userRoles = ctx.GetEntity <ApplicationUserRole>();
var courses = ctx.GetEntity <Course>();
var userCourses = ctx.GetEntity <UsersPerCourse>();
var queryRoles = (
from r in roles
from ur in userRoles.Where(x => x.RoleId == r.Id)
select new
{
UserId = ur.UserId,
Role = r.Name
}
).AsQueryable(); //el sql todavia no es ejecuado queda en memoria
var query = (
from u in user
select new UserForGridView
{
Id = u.Id,
FullName = u.Name + " " + u.LastName,
Email = u.Email,
CoursesCreated = courses.Where(x => x.AuthorId == u.Id).Count(),
CoursesTaken = userCourses.Where(x => x.UserId == u.Id).Count(),
Roles = queryRoles.Where(x => x.UserId == u.Id).Select(x => x.Role).ToList()
}
).AsQueryable(); //query va a quedar pendiente
// Order by
if (grid.columna == "FullName")
{
query = grid.columna_orden == "DESC" ? query.OrderByDescending(x => x.FullName)
: query.OrderBy(x => x.FullName);
}
var data = query.Skip(grid.pagina)
.Take(grid.limite)
.ToList();
var total = query.Count();
grid.SetData(data, total);
}
}
catch (Exception e)
{
logger.Error(e.Message);
}
return(grid.responde());
}
public AnexGRIDResponde GetAll(AnexGRID grid)
{
grid.Inicializar();
try
{
using (var ctx = _dbContextScopeFactory.CreateReadOnly())
{
var courses = ctx.GetEntity <Course>();
var categories = ctx.GetEntity <Category>();
var students = ctx.GetEntity <UsersPerCourses>();
var queryStudents = (
from c in courses
from s in students.Where(x => x.CourseId == c.Id)
select new
{
UserId = s.UserId,
CategoryId = c.CategoryId
}
).AsQueryable();
var query = (
from c in categories
select new CategoryForGridView
{
Id = c.Id,
Icon = c.Icon,
Name = c.Name,
Courses = courses.Where(x => x.CategoryId == c.Id).Count(),
Students = queryStudents.Where(x => x.CategoryId == c.Id).Count()
}
).AsQueryable();
// Order by
if (grid.columna == "Name")
{
query = grid.columna_orden == "DESC" ? query.OrderByDescending(x => x.Name)
: query.OrderBy(x => x.Name);
}
var data = query.Skip(grid.pagina)
.Take(grid.limite)
.ToList();
var total = query.Count();
grid.SetData(data, total);
}
}
catch (Exception e)
{
logger.Error(e.Message);
}
return(grid.responde());
}
public AnexGRIDResponde Listar(AnexGRID grid, int idCategoria)
{
try
{
using (var ctx = new CmsContext())
{
ctx.Configuration.LazyLoadingEnabled = false;
grid.Inicializar();
var query = ctx.Contenido.Include("Usuario")
.Where(x => x.idEmpresa == idEmpresa && x.idCategoria == idCategoria);
// Ordenamiento
//if (grid.columna == "id")
//{
// query = grid.columna_orden == "DESC" ? query.OrderByDescending(x => x.id)
// : query.OrderBy(x => x.id);
//}
if (grid.columna == "Titulo")
{
query = grid.columna_orden == "DESC" ? query.OrderByDescending(x => x.Titulo)
: query.OrderBy(x => x.Titulo);
}
if (grid.columna == "Nombre")
{
query = grid.columna_orden == "DESC" ? query.OrderByDescending(x => x.Usuario.Nombre)
: query.OrderBy(x => x.Usuario.Nombre);
}
// id, Nombre, Titulo, Desde, Hasta
// Filtrar
foreach (var f in grid.filtros)
{
if (f.columna == "Titulo")
{
query = query.Where(x => x.Titulo.StartsWith(f.valor));
}
if (f.columna == "Nombre")
{
query = query.Where(x => x.Usuario.Nombre.StartsWith(f.valor) || x.Usuario.Apellido.Contains(f.valor));
}
if (f.columna == "Descripcion")
{
query = query.Where(x => x.Titulo.Contains(f.valor));
}
}
var contenido = query.Skip(grid.pagina)
.Take(grid.limite)
.ToList();
var total = query.Count();
grid.SetData(
from e in contenido
select new
{
e.idContenido,
e.Titulo,
e.Descripcion,
Usuario = new
{
Nombre = e.Usuario.Nombre + ' ' + e.Usuario.Apellido
}
}
, total);
}
}
catch (EntityException ex)
{
throw new Exception(ex.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(grid.responde());
}
