public List<ReporteCalles> ReporteCalles()
{
using (SRI con = new SRI())
{
List<ReporteCalles> lista = (from vc in con.VIA_CIRCULACION
join nomdc in con.DETALLE_CARACTERISTICA on vc.ID_NOMBRE_CALLE equals nomdc.ID_DETCAR
join oridc in con.DETALLE_CARACTERISTICA on vc.ID_ORIENTACION equals oridc.ID_DETCAR
join velmaxdc in con.DETALLE_CARACTERISTICA on vc.ID_VELOC_MAXIMA equals velmaxdc.ID_DETCAR
join sendc in con.DETALLE_CARACTERISTICA on vc.ID_SENTIDO equals sendc.ID_DETCAR
join secdc in con.DETALLE_CARACTERISTICA on vc.ID_SECTOR equals secdc.ID_DETCAR
where
nomdc.ID_CARACTERISTICA == 10 &&
oridc.ID_CARACTERISTICA == 5 &&
velmaxdc.ID_CARACTERISTICA == 11 &&
sendc.ID_CARACTERISTICA == 4 &&
secdc.ID_CARACTERISTICA == 7
select new ReporteCalles
{
ID_CALLE = vc.ID_VIA_CIRCULACION,
NOMBRE_CALLE = nomdc.DETALLE_CAR,
NUM_PISTAS = vc.CANT_PISTAS ?? 0,
ORIENTACION = oridc.DETALLE_CAR,
VEL_MAX = velmaxdc.DETALLE_CAR,
SENTIDO = sendc.DETALLE_CAR,
SECTOR = secdc.DETALLE_CAR
}
).ToList();
return lista;
}
}
public List<ReporteInfracciones> ReporteInfracciones()
{
using (SRI con = new SRI())
{
List<ReporteInfracciones> lista = (from inf in con.INFRACCION
join dc in con.DETALLE_CARACTERISTICA on inf.ID_DETALLE_INFRACCION equals dc.ID_DETCAR
join dcg in con.DETALLE_CARACTERISTICA on inf.ID_GRAVEDAD equals dcg.ID_DETCAR
join dcm in con.DETALLE_CARACTERISTICA on inf.ID_TIPO_MONEDA equals dcm.ID_DETCAR
where
dc.ID_CARACTERISTICA == 9 &&
dcg.ID_CARACTERISTICA == 3 &&
dcm.ID_CARACTERISTICA == 12
select new ReporteInfracciones
{
ID_INF = inf.ID_INFRACCION,
GRAVEDAD = dcg.DETALLE_CAR,
TIPO_MONEDA = dcm.DETALLE_CAR,
VALOR = inf.MONTO,
DESCR_INF = dc.DETALLE_CAR
}
).ToList();
return lista;
}
}
public void ReporteInfractores()
{
DataTable dt = new DataTable();
using (SRI con = new SRI())
{
var drt = ( from m in con.MULTA
join a in con.APELACION on m.ID_MULTA equals a.ID_MULTA
join i in con.INFRACTOR on m.ID_INFRACTOR equals i.ID_INFRACTOR
join mm in con.MONTO_MULTA on m.ID_MONTO equals mm.ID_MONTO
select new{
ESTADO = a.ESTADO,
RUT_INF = i.RUT_INFR,
INFR = i.NOMBRE_INFR,
GRAVEDAD = m.ID_GRAVEDAD_INFRACCION,
VALOR = mm.VALOR_PESOS + m.MONTO_ADICIONAL ,
FECHA = m.FECHA_INTEGRACION
}
);
//dt = drt;
}
}
public int idRestriccion()
{
using(SRI con = new SRI())
{
return (int)con.RESTRICCION.DefaultIfEmpty().Max(r => r==null ? 0 : r.ID_RESTRICCION);
}
}
public List<ReporteInfractor> ReporteInfractores(String rut)
{
using (SRI con = new SRI())
{
List<ReporteInfractor> lista = (from m in con.MULTA
join a in con.APELACION on m.ID_MULTA equals a.ID_MULTA
join i in con.INFRACTOR on m.ID_INFRACTOR equals i.ID_INFRACTOR
join mm in con.MONEDA on m.ID_MONEDA equals mm.ID_MONEDA
join inf in con.INFRACCION on m.ID_INFRACCION equals inf.ID_INFRACCION
where i.RUT_INFR == rut
select new ReporteInfractor
{
ESTADO = a.ESTADO ?? 0,
RUT_INF = i.RUT_INFR,
INFR = i.NOMBRE_INFR + " " + i.APPAT_INFR + " " + i.APMAT_INFR,
GRAVEDAD = inf.ID_GRAVEDAD,
VALOR = m.MONTO_ADICIONAL ?? 0 + mm.VALOR_PESOS,
FECHA = m.FECHA_INTEGRACION
}
).ToList();
return lista;
}
}
public int tipoMoneda(int idMotivo)
{
using (SRI con = new SRI())
{
INFRACCION objinf = new INFRACCION();
objinf = con.INFRACCION.Where(i => i.ID_DETALLE_INFRACCION == idMotivo).FirstOrDefault();
return (int)objinf.ID_TIPO_MONEDA;
}
}
public int idInfraccion(int idMotivo)
{
using (SRI con = new SRI())
{
INFRACCION objinf = new INFRACCION();
objinf = con.INFRACCION.Where(i => i.ID_DETALLE_INFRACCION == idMotivo).FirstOrDefault();
return (int)objinf.ID_INFRACCION;
}
}
private int RetornarNuevoId()
{
int id = 0;
using (SRI sri = new SRI())
{
id = (int)sri.VIA_CIRCULACION.Max(p => p.ID_VIA_CIRCULACION) + 1;
return id;
}
}
public List<VIA_CIRCULACION> BuscarCalleVC(int idcalle)
{
using (SRI contex = new SRI())
{
List<VIA_CIRCULACION> lbuscar = new List<VIA_CIRCULACION>();
lbuscar = contex.VIA_CIRCULACION.Where(a => a.ID_NOMBRE_CALLE == idcalle).ToList();
return lbuscar;
}
}
public List<DETALLE_CARACTERISTICA> listarCategoria()
{
using (SRI contex = new SRI())
{
List<DETALLE_CARACTERISTICA> lCategoria = new List<DETALLE_CARACTERISTICA>();
lCategoria = contex.DETALLE_CARACTERISTICA.Where(a => a.ID_CARACTERISTICA ==8 ).ToList();
return lCategoria;
}
}
private int RetornarNuevoId()
{
int id = 0;
using (SRI sri = new SRI())
{
id = (int)sri.VIA_CIRCULACION.DefaultIfEmpty().Max(p => p == null ? 0 : p.ID_VIA_CIRCULACION) + 1;
return id;
}
}
private int retornarNuevoId()
{
int id = 0;
using (SRI sri = new SRI())
{
id = (int)sri.PERSONAL_SECTOR.DefaultIfEmpty().Max(p => p == null ? 0 : p.ID_PERSONAL_SECTOR) + 1;
return id;
}
}
private int retornarNuevoId()
{
int id = 0;
using (SRI sri = new SRI())
{
id = (int)sri.PERSONAL.Max(p => p.ID_PERSONAL) + 1;
return id;
}
}
private int RetornarNuevoId()
{
int id = 0;
using (SRI sri = new SRI())
{
id = (int)sri.INFRACTOR.Max(p => p.RUT_INFR) + 1;
return id;
}
}
public List<PERSONAL> BuscarUsuario(String rut)
{
using (SRI contex = new SRI())
{
List<PERSONAL> lbuscar = new List<PERSONAL>();
lbuscar = contex.PERSONAL.Where(a => a.RUT_PER== rut).ToList();
return lbuscar;
}
}
public List<INFRACTOR> buscarInfractor(String rut)
{
using (SRI contex = new SRI())
{
List<INFRACTOR> lbuscar = new List<INFRACTOR>();
lbuscar = contex.INFRACTOR.Where(a => a.RUT_INFR == rut).ToList();
return lbuscar;
}
}
public INFRACTOR buscarInfractorPat(int rut)
{
using (SRI contex = new SRI())
{
INFRACTOR lbuscar = new INFRACTOR();
lbuscar = contex.INFRACTOR.Where(a => a.ID_VEHICULO == rut).FirstOrDefault();
return lbuscar;
}
}
private int retornarNuevoId()
{
int id = 0;
using (SRI sri = new SRI())
{
id = (int)sri.VEHICULO.DefaultIfEmpty().Max(p => p == null ? 0 : p.ID_VEHICULO) + 1;
return id;
}
}
public VEHICULO buscarPatente(String pat)
{
using (SRI contex = new SRI())
{
VEHICULO lbuscar = new VEHICULO();
lbuscar = contex.VEHICULO.Where(a => a.PATENTE == pat).FirstOrDefault();
return lbuscar;
}
}
public List<DETALLE_CARACTERISTICA> ListarCalle()
{
using (SRI contex = new SRI())
{
List<DETALLE_CARACTERISTICA> lCalle = new List<DETALLE_CARACTERISTICA>();
lCalle = contex.DETALLE_CARACTERISTICA.Where(a => a.ID_CARACTERISTICA == 10).ToList();
return lCalle;
}
}
public int buscarInspector(String rut)
{
using (SRI con = new SRI())
{
PERSONAL pe = new PERSONAL();
pe = con.PERSONAL.Where(p => p.RUT_PER == rut).FirstOrDefault();
return (int)pe.ID_PERSONAL;
}
}
public List<DETALLE_CARACTERISTICA> BuscarCalle(String nomCalle)
{
using (SRI contex = new SRI())
{
List<DETALLE_CARACTERISTICA> lbuscar = new List<DETALLE_CARACTERISTICA>();
lbuscar = contex.DETALLE_CARACTERISTICA.Where(a => a.DETALLE_CAR.Equals(nomCalle)).ToList();
return lbuscar;
}
}
public List<DETALLE_CARACTERISTICA> BuscarInfraccion(int idinfr)
{
using (SRI contex = new SRI())
{
List<DETALLE_CARACTERISTICA> lbuscar = new List<DETALLE_CARACTERISTICA>();
lbuscar = contex.DETALLE_CARACTERISTICA.Where(a => a.ID_DETCAR == idinfr).ToList();
return lbuscar;
}
}
public List<DETALLE_CARACTERISTICA> listarOrientacion()
{
using(SRI contex = new SRI())
{
List<DETALLE_CARACTERISTICA> lOrientacion = new List<DETALLE_CARACTERISTICA>();
lOrientacion = contex.DETALLE_CARACTERISTICA.Where(a => a.ID_CARACTERISTICA ==5).ToList();
return lOrientacion;
}
}
public List<INFRACTOR> sessionInfractor(String rut, String clave)
{
List<INFRACTOR> lsession = new List<INFRACTOR>();
using (SRI contex = new SRI())
{
lsession = contex.INFRACTOR.Where(i => i.RUT_INFR == rut && i.PASSWORD_INFR == clave).ToList();
}
return lsession;
}
public List<DETALLE_CARACTERISTICA> listarGravedad()
{
using (SRI contex = new SRI())
{
List<DETALLE_CARACTERISTICA> lGravedad = new List<DETALLE_CARACTERISTICA>();
lGravedad = contex.DETALLE_CARACTERISTICA.Where(a => a.ID_CARACTERISTICA == 3).ToList();
return lGravedad;
}
}
private int RetornarNuevoId()
{
int id = 0;
using (SRI sri = new SRI())
{
id = (int)sri.ADJUNTOS_APELACION.DefaultIfEmpty().Max(p => p ==null ? 0 : p.ID_ADJUNTO)+1;
return id;
}
}
public int buscarSector(int sector)
{
using (SRI con = new SRI())
{
SECTOR sec = new SECTOR();
sec = con.SECTOR.Where(s => s.ID_NOMBRE_SECTOR == sector).FirstOrDefault();
return (int)sec.ID_SECTOR;
}
}
public String traeAdjunto(int idAdjunto)
{
String nombreAdjunto = "";
using (SRI con = new SRI())
{
ADJUNTOS_APELACION adjunto = new ADJUNTOS_APELACION();
adjunto = con.ADJUNTOS_APELACION.Where(a => a.ID_ADJUNTO == idAdjunto).FirstOrDefault();
nombreAdjunto = adjunto.ADJUNTO.ToString();
}
return nombreAdjunto;
}
public Boolean verificaPaginaInfractor(String rut)
{
List<INFRACTOR> lsession = new List<INFRACTOR>();
using (SRI contex = new SRI())
{
lsession = contex.INFRACTOR.Where(i => i.RUT_INFR == rut).ToList();
if (lsession.Count > 0)
return true;
else
return false;
}
}