//agrupar detalles por lote
public List <ReporteAgricolaDto> GetAgriculaGroup(int presupuestoId)
{
var list = new List <ReporteAgricolaDto>();
try
{
using (var ctx = new BIContext())
{
list = (from g in ctx.SP_GroupByGrupo
join c in ctx.SP_GroupByCategoria on g.categoria equals c.categoria
where g.presupuestoId == presupuestoId
group c by new { c.categoria, c.nombre_categoria, c.total_referencia } into hh
select new
{
nombre_categoria = hh.Key.nombre_categoria,
total_categoria = hh.Key.total_referencia,
total = hh.Sum(x => x.total_referencia),
grupos = ctx.SP_GroupByGrupo.Where(x => x.categoria == hh.Key.categoria).ToList()
}).Select(x => new ReporteAgricolaDto
{
nombre_categoria = x.nombre_categoria,
total_categoria = x.total_categoria,
total = x.total,
grupos = x.grupos
}).ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}
public List <ListDetallDto> GetDetalleAgricola(int presupuestoId, int fincaId)
{
var list = new List <ListDetallDto>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <ListDetallDto>("select * from DETALLE_P_AGRICOLA WHERE presupuestoId IN (@p0) and fincaId IN (@p1)", presupuestoId, fincaId).ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}
public List <ListDetallDto> GetDetalleGroupByCategoria(int presupuestoId, int fincaId)
{
var list = new List <ListDetallDto>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <ListDetallDto>("select * from SP_GroupByCategoria WHERE presupuestoId IN (@p0) and fincaId IN (@p1)", presupuestoId, fincaId).ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}
public List <CuentasContables> GetAll()
{
var list = new List <CuentasContables>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <CuentasContables>("Select * from cuentas_contables")
.ToList();
}
}catch (Exception e)
{
throw e;
}
return(list);
}
public List <PSAgricola> GetByCategoria(string categoriaId)
{
var list = new List <PSAgricola>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <PSAgricola>("Select * from PS_AGRICOLA WHERE categoria IN (@p0)", categoriaId)
.ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}
public List <PSAgricola> GetAllProductos()
{
var list = new List <PSAgricola>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <PSAgricola>("Select * from PS_AGRICOLA")
.ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}
public List <PSVentas> GetByCategoria()
{
var list = new List <PSVentas>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <PSVentas>("Select * from PS_VENTA")
.ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}
public List <PSVentas> GetAll(string grupoId)
{
var list = new List <PSVentas>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <PSVentas>("Select * from PS_VENTA WHERE codigo_grupo IN (@P0) ", grupoId)
.ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}
public List <GrupoProducto> GetAll(string categoriaId)
{
var list = new List <GrupoProducto>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <GrupoProducto>("Select * from Grupo_Producto WHERE categoria IN (@p0)", categoriaId)
.ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}
public List <CategoriaAgricola> GetAll()
{
var list = new List <CategoriaAgricola>();
try
{
using (var ctx = new BIContext())
{
list = ctx.Database.SqlQuery <CategoriaAgricola>("Select * from CATEGORIA where codigo != 12")
.ToList();
}
}
catch (Exception e)
{
throw e;
}
return(list);
}