public IActionResult UsuariosConRoles()
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ QUERY
//SELECT* FROM abmproyectofinal.roles r
//LEFT JOIN abmproyectofinal.usuarios u ON r.id = u.rolid
//JOIN SYNTAX LINQ
//from ... in outerSequence join... in innerSequence on outerKey equals innerKey select ...
var query = from r in db.Roles
join u in db.Usuarios on r.Id equals u.RolID
select new
{
Id = u.Id,
NombreUsuario = u.NombreUsuario,
Password = u.Password,
Email = u.Email,
RolID = u.RolID,
Descripcion = r.Descripcion,
};
return(Ok(query.ToList()));
}
}
public IActionResult byPlanilla(int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ
//select * from abmproyectofinal.empleados c where c.planillaId = 2;
var query = from x in db.Empleados
join y in db.EmpleadosInactivos on x.Id equals y.EmpleadoID into pp
from y in pp.DefaultIfEmpty()
where x.PlanillaID == id && y == null
select new Empleados
{
Id = x.Id,
Apellidos = x.Apellidos,
Nombres = x.Nombres,
Email = x.Email,
N_Cedula = x.N_Cedula,
Direccion = x.Direccion,
FechaIngreso = x.FechaIngreso,
FechaNac = x.FechaNac,
Genero = x.Genero,
SalarioBase = x.SalarioBase,
DepartamentoID = x.DepartamentoID,
PlanillaID = x.PlanillaID,
DiasLaborados = x.DiasLaborados,
Ingresos = x.Ingresos,
Deducciones = x.Deducciones,
TotalPagar = x.TotalPagar
};
return(Ok(query.ToList()));
}
}
public IActionResult Lista()
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ QUERY
//SELECT h.*, p.descripcion FROM abmproyectofinal.historial h
//LEFT JOIN abmproyectofinal.tipoplanillas p on h.PlanillaID where h.id = p.id;
//JOIN SYNTAX LINQ
//from ... in outerSequence join... in innerSequence on outerKey equals innerKey select ...
var query = from x in db.Historial
join y in db.TipoPlanillas on x.PlanillaID equals y.Id
select new
{
Id = x.Id,
FechaInicial = x.FechaInicio,
FechaFinal = x.FechaFinal,
TotalPlanilla = x.TotalPlanilla,
Archivo = x.Archivo,
Descripcion = y.Descripcion
};
return(Ok(query.ToList()));
}
}
public IActionResult SumTotalxFechaxPlanilla(DateTime inicial, DateTime final, int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ QUERY
//SELECT SUM(TotalPlanilla) AS Total FROM abmproyectofinal.historial WHERE FechaInicio >= "2021-09-01" and
// FechaFinal<= "2021-09-30" and planillaID = 8
var query = from x in db.Historial
where x.FechaInicio >= inicial
where x.FechaFinal <= final
where x.PlanillaID == id
select new
{
TotalPlanilla = x.TotalPlanilla
};
var query2 = from x in db.Historial
where x.FechaInicio >= inicial
where x.FechaFinal <= final
where x.PlanillaID == id
select new
{
Id = x.PlanillaID,
TotalPlanilla = query.Select(x => x.TotalPlanilla).Sum()
};
return(Ok(query2.ToList()));
}
}
public IEnumerable <Reservation> OrderBy([FromQuery] string filtro)
{
using (AplicationDBContext db = new AplicationDBContext())
{
List <Reservation> reserv = db.Reservations.ToList();
if (filtro == "dateasc")
{
reserv = reserv.OrderBy(r => r.Date).ToList();
return(reserv);
}
if (filtro == "datedesc")
{
reserv = reserv.OrderByDescending(r => r.Date).ToList();
return(reserv);
}
if (filtro == "alphasc")
{
reserv = reserv.OrderBy(r => r.Name).ToList();
return(reserv);
}
if (filtro == "alphdesc")
{
reserv = reserv.OrderByDescending(r => r.Name).ToList();
return(reserv);
}
if (filtro == "rate")
{
reserv = reserv.OrderBy(r => r.Rate).ToList();
return(reserv);
}
return(reserv);
}
}
public Person Create([FromQuery] string name, string type, string phone, string date)
{
using (AplicationDBContext db = new AplicationDBContext())
{
Person person = new Person();
Reservation reserv = new Reservation();
Description desc = new Description();
person.Name = name;
person.Phone = phone;
string format = "MM/dd/yyyy";
DateTime b = DateTime.ParseExact(date, format, CultureInfo.InvariantCulture);
person.BirthDate = b;
db.Add(person);
db.SaveChanges();
reserv.Name = name;
reserv.Date = b;
reserv.Favorite = true;
reserv.Rate = 3;
reserv.PersonId = person.Id;
desc.Type = "Contact Type 1";
desc.PersonId = person.Id;
db.Add(reserv);
db.Add(desc);
db.SaveChanges();
return(person);
}
}
public IActionResult InActivos()
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ QUERY
//SELECT m.*, c.id , c.nombres, c.apellidos FROM abmproyectofinal.empleados c
//LEFT JOIN abmproyectofinal.empleadosinactivos m ON m.empleadoID = c.Id where m.empleadoID > 0
//JOIN SYNTAX LINQ
//from ... in outerSequence join... in innerSequence on outerKey equals innerKey select ...
var query = from x in db.Empleados
join y in db.EmpleadosInactivos on x.Id equals y.EmpleadoID
where y.EmpleadoID > 0
select new
{
Nombres = x.Nombres,
Apellidos = x.Apellidos,
Id = y.Id,
Valor = y.Valor,
Motivo = y.Motivo,
Nota = y.Nota,
FechaSalida = y.FechaSalida,
EmpleadoID = y.EmpleadoID,
};
return(Ok(query.ToList()));
}
}
public IEnumerable <Description> Get()
{
using (AplicationDBContext db = new AplicationDBContext())
{
return(db.Descriptions.ToList());
}
}
public IActionResult Eventos()
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ QUERY
//SELECT e.nombres, e.apellidos, ev.* FROM abmproyectofinal.empleados e
//LEFT JOIN abmproyectofinal.eventos ev on e.id where e.id = ev.empleadosID;
//JOIN SYNTAX LINQ
//from ... in outerSequence join... in innerSequence on outerKey equals innerKey select ...
var query = from x in db.Empleados
join y in db.Eventos on x.Id equals y.EmpleadosID
select new
{
IdEmp = x.Id,
Apellidos = x.Apellidos,
Nombres = x.Nombres,
Id = y.Id,
FechaInicio = y.FechaInicio,
FechaFinal = y.FechaFinal,
Tipo = y.Tipo,
Comentario = y.Comentario,
Color = y.Color
};
return(Ok(query.ToList()));
}
}
public IEnumerable <Reservation> Get()
{
using (AplicationDBContext db = new AplicationDBContext())
{
var auth = db.Reservations.ToList();
return(auth);
}
}
public IActionResult CountEmpleadoInactivo(int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ
//select COUNT(*) as CantidadActivos from abmproyectofinal.empleados;
//var qry = (from x in y
// select x).Count()
var query = (from x in db.EmpleadosInactivos select x).Count();
return(Ok(query));
}
}
public Person Getpers([FromQuery] int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
var ss = db.Persons.Include(x => x.Descriptions).Where(x => x.Id == id).FirstOrDefault();
if (ss == null)
{
Person res = new Person();
return(res);
}
else
{
return(ss);
}
}
}
public Person Edit([FromQuery] string name, string type, string phone, string date, int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
var person = db.Persons.Include(x => x.Descriptions).Include(x => x.Reservations).Where(x => x.Id == id).FirstOrDefault();
person.Name = name;
person.Phone = phone;
string format = "MM/dd/yyyy";
DateTime b = DateTime.ParseExact(date, format, CultureInfo.InvariantCulture);
person.BirthDate = b;
person.Descriptions.ElementAt(0).Type = type;
db.Entry(person).State = EntityState.Modified;
db.SaveChanges();
return(person);
}
}
public IActionResult CountEventosByTipo(int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ
//select e.tipo, COUNT(*) as cantidad from abmproyectofinal.eventos e group by e.tipo;
var query = from x in db.Eventos
group x by x.Tipo into grp
select new { Tipo = grp.Key, Cantidad = grp.Count() };
return(Ok(query.ToList()));
}
}
public Reservation Favorite([FromQuery] int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
Reservation reserv = db.Reservations.Find(id);
if (reserv.Favorite == true)
{
reserv.Favorite = false;
}
else
{
reserv.Favorite = true;
}
db.SaveChanges();
return(reserv);
}
}
public IActionResult CountEmpleadobyDepto(int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ
//SELECT d.descripcion, COUNT(*) as cantidad
//FROM abmproyectofinal.empleados e, abmproyectofinal.departamentos d
//WHERE e.departamentoid = d.id group by d.descripcion;
var query = from x in db.Empleados
from y in db.Departamentos
where x.DepartamentoID == y.Id
group x by y.Descripcion into grp
select new { Descripcion = grp.Key, Cantidad = grp.Count() };
return(Ok(query.ToList()));
}
}
public IActionResult CountEmpleadoActivo(int id)
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ
//select COUNT(*) as cantidad
//from abmproyectofinal.empleados as a
//where a.id NOT IN(select b.empleadoId
// from abmproyectofinal.empleadosinactivos as b)
var query = (from x in db.Empleados
where !(from o in db.EmpleadosInactivos
select o.EmpleadoID)
.Contains(x.Id)
select x).Count();
return(Ok(query));
}
}
public IActionResult Activos()
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ QUERY
//SELECT c.id , c.nombres, c.apellidos, m.empleadoid FROM abmproyectofinal.empleados c
//LEFT JOIN abmproyectofinal.empleadosinactivos m ON c.Id = m.empleadoID where m.empleadoid is null;
//JOIN SYNTAX LINQ
//from ... in outerSequence join... in innerSequence on outerKey equals innerKey select ...
var query = from x in db.Empleados
from p in db.TipoPlanillas
from d in db.Departamentos
join y in db.EmpleadosInactivos on x.Id equals y.EmpleadoID into pp
from y in pp.DefaultIfEmpty()
where y == null && x.DepartamentoID == d.Id && x.PlanillaID == p.Id
select new
{
Id = x.Id,
Nombres = x.Nombres,
Apellidos = x.Apellidos,
Email = x.Email,
N_Cedula = x.N_Cedula,
Direccion = x.Direccion,
FechaNac = x.FechaNac,
FechaIngreso = x.FechaIngreso,
Genero = x.Genero,
SalarioBase = x.SalarioBase,
descriDepto = d.Descripcion,
tipoPlanilla = p.Tipo,
descriPlanilla = p.Descripcion,
};
return(Ok(query.ToList()));
}
}
public IActionResult byUser(string user)
{
using (AplicationDBContext db = new AplicationDBContext())
{
//LINQ
//select* from abmproyectofinal.usuarios where usuarios.nombreUsuario = "elmalo"
var query = from x in db.Usuarios
where x.NombreUsuario == user
select new
{
Id = x.Id,
NombreUsuario = x.NombreUsuario,
Password = x.Password,
Email = x.Email,
RolID = x.RolID
};
return(Ok(query.ToList()));
}
}
public TipoPlanillaController(AplicationDBContext tp_context)
{
_context = tp_context;
}
public UserService(IOptions <AppSettings> appSettings, AplicationDBContext context)
{
_appSettings = appSettings.Value;
_context = context;
}
public DetallePagoController(AplicationDBContext context)
{
_context = context;
}
public HistorialController(AplicationDBContext context)
{
_context = context;
}
public RegistroController(AplicationDBContext context)
{
_context = context;
}
public UsuarioController(AplicationDBContext context)
{
_context = context;
}
public EmpleadoController(AplicationDBContext context)
{
_context = context;
}
public ProdutosController(AplicationDBContext database)
{
_database = database;
}
/// <summary>
/// Constructor de Repositorio
/// </summary>
/// <param name="dBContext"></param>
public FlightRepositorio(AplicationDBContext dBContext)
{
this._dBContext = dBContext;
}
public RolController(AplicationDBContext context)
{
_context = context;
}
public ClientesController(AplicationDBContext context)
{
_context = context;
}
