public DataSet ReporteFrecuenciaLlamadosPorComuna(int mes, int año)
{
CnxBase myBase = new CnxBase();
string reqSQL = @"select clave as tipo,
get_frecuencia_llamados_comuna(" + mes + "," + año + @",clave,'EL BOSQUE') as el_bosque,
get_frecuencia_llamados_comuna(" + mes + "," + año + @",clave,'LA CISTERNA') as la_cisterna,
get_frecuencia_llamados_comuna(" + mes + "," + año + @",clave,'LO ESPEJO') as lo_espejo,
get_frecuencia_llamados_comuna(" + mes + "," + año + @",clave,'PEDRO AGUIRRE CERDA') as pac,
get_frecuencia_llamados_comuna(" + mes + "," + año + @",clave,'SAN MIGUEL') as san_miguel,
get_frecuencia_llamados_comuna(" + mes + "," + año + @",clave,'SAN JOAQUIN') as san_joaquin,
get_frecuencia_llamados_comuna(" + mes + "," + año + @",clave,'-') as fuera,
get_frecuencia_llamados(" + mes + "," + año + @",clave) as total
from z_llamados where codigo_llamado<100";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public z_llamados getObjectz_llamados_id(System.Int32 myID)
{
z_llamados myz_llamados = new z_llamados();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_llamado,codigo_llamado,clave,descripcion, max_b, incendio, rest_incendio FROM z_llamados WHERE (id_llamado=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_llamados.id_llamado = Convert.ToInt32(myReader[0]);
myz_llamados.codigo_llamado = Convert.ToInt32(myReader[1]);
myz_llamados.clave = myReader[2].ToString();
myz_llamados.descripcion = myReader[3].ToString();
myz_llamados.max_b = Convert.ToInt32(myReader[4]);
myz_llamados.incendio = Convert.ToBoolean(myReader[5]);
myz_llamados.rest_incendio = Convert.ToBoolean(myReader[6]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(myz_llamados);
}
public bool ExistenciaCarroBitacoraLlamados(int id_carro, int id_expediente, string evento)
{
//
CnxBase myBase = new CnxBase();
bool existe = false;
string reqSQL = "select fecha from bitacora_llamados where id_carro = " + id_carro + " and id_expediente = " + id_expediente + " and evento like '%" + evento + "%'";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
if (myResult.Tables[0].Rows.Count > 0)
{
existe = true;
}
else
{
existe = false;
}
return(existe);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public a_agenda_detalle getObjecta_agenda_detalle(System.Int32 myID)
{
a_agenda_detalle mya_agenda_detalle = new a_agenda_detalle();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_subcat,nombre,telefono,id_empresa FROM a_agenda_detalle WHERE (id_empresa=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
mya_agenda_detalle.id_subcat = Convert.ToInt32(myReader[0]);
mya_agenda_detalle.nombre = myReader[1].ToString();
mya_agenda_detalle.telefono = myReader[2].ToString();
mya_agenda_detalle.id_empresa = Convert.ToInt32(myReader[3]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(mya_agenda_detalle);
}
public z_conductores getObjectz_conductores(string codigo)
{
z_conductores myz_conductores = new z_conductores();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_conductor,id_tipo_conductor,id_cuart_vol,tipo_licencia,licencia_vence,codigo_conductor,id_carros,disponible,temporal FROM z_conductores WHERE (codigo_conductor='" + codigo + "')";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_conductores.id_conductor = Convert.ToInt32(myReader[0]);
myz_conductores.id_tipo_conductor = Convert.ToInt32(myReader[1]);
myz_conductores.id_cuart_vol = Convert.ToInt32(myReader[2]);
myz_conductores.tipo_licencia = myReader[3].ToString();
myz_conductores.licencia_vence = Convert.ToDateTime(myReader[4]);
myz_conductores.codigo_conductor = myReader[5].ToString();
myz_conductores.id_carros = myReader[6].ToString();
myz_conductores.disponible = Convert.ToBoolean(myReader[7]);
myz_conductores.temporal = Convert.ToBoolean(myReader[8]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(myz_conductores);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public z_orden getObjectz_orden(System.Int32 myID)
{
z_orden myz_orden = new z_orden();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_orden,codigo_llamado,orden_numero,id_tipo_carro FROM z_orden WHERE (id_orden=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_orden.id_orden = Convert.ToInt32(myReader[0]);
myz_orden.codigo_llamado = Convert.ToInt32(myReader[1]);
myz_orden.orden_numero = Convert.ToInt32(myReader[2]);
myz_orden.id_tipo_carro = Convert.ToInt32(myReader[3]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(myz_orden);
}
public int ObtieneOrdenTipoDistinto(int cod_llamado, string IdCarros)
{
CnxBase myBase = new CnxBase();
ArrayList aOrden = new ArrayList();
int IdGrupoArea = 0;
string reqSQL;
reqSQL = "SELECT id_grupo_area FROM x_orden_tipo_distinto WHERE id_carro IN(" + IdCarros + ") AND id_llamado = " + cod_llamado + " LIMIT 1";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
//retornoTipoCarro = new int[myResult.Tables[0].Rows.Count];
foreach (DataRow ot_carro in myResult.Tables[0].Rows)
{
IdGrupoArea = Convert.ToInt32(ot_carro["id_grupo_area"].ToString());
}
return(IdGrupoArea);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public void addz_despacho_inhabil(List <KeyValuePair <string, string> > columnas)
{
CnxBase myBase = new CnxBase();
string reqSQL = "INSERT INTO z_despacho_inhabil ";
// preparar
string cols = "", vals = "";
foreach (KeyValuePair <string, string> k in columnas)
{
cols += k.Key + ",";
vals += k.Value + ",";
}
cols = "(" + cols.Trim(',') + ")";
vals = "(" + vals.Trim(',') + ")";
reqSQL += cols + " VALUES " + vals;
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
myCommand.ExecuteNonQuery();
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public m001_config ObjConfigM001(System.Int32 myID)
{
m001_config mym001_config = new m001_config();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT cnf.id_llamado, cnf.radio, cnf.id_compania, cnf.id_carro, round(st_x(cia.geom)) As pto_x, round(st_y(cia.geom)) As pto_y ";
reqSQL += "FROM m001_config cnf, m001_companias cia ";
reqSQL += "WHERE (cnf.id_llamado = " + myID + ") ";
reqSQL += "AND cnf.id_compania = cia.id_compania";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
mym001_config.id_llamado = Convert.ToInt32(myReader[0]);
mym001_config.radio = Convert.ToInt32(myReader[1]);
mym001_config.id_compania = Convert.ToInt32(myReader[2]);
mym001_config.id_carro = Convert.ToInt32(myReader[3]);
mym001_config.x_cor = Convert.ToInt32(myReader[4]);
mym001_config.y_cor = Convert.ToInt32(myReader[5]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(mym001_config);
}
public z_tipo_carro getObjectz_tipo_carro(Int32 myID)
{
z_tipo_carro myz_tipo_carro = new z_tipo_carro();
CnxBase myBase = new CnxBase();
string reqSQL =
"SELECT id_tipo_carro,tipo_carro_letra,tipo_carro_descripcion FROM z_tipo_carro WHERE (id_tipo_carro=" +
myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_tipo_carro.id_tipo_carro = Convert.ToInt32(myReader[0]);
myz_tipo_carro.tipo_carro_letra = Convert.ToString(myReader[1]);
myz_tipo_carro.tipo_carro_descripcion = Convert.ToString(myReader[2]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr + reqSQL));
}
return(myz_tipo_carro);
}
public void modifyz_despacho_inhabil(int id_despacho, List <KeyValuePair <string, string> > columnas)
{
CnxBase myBase = new CnxBase();
string reqSQL = "UPDATE z_despacho_inhabil SET ";
// construir comando
string cols = "";
foreach (KeyValuePair <string, string> k in columnas)
{
cols += k.Key + "=" + k.Value + ",";
}
cols = cols.Trim(',');
reqSQL += cols + " WHERE id_despacho=" + id_despacho;
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
myCommand.ExecuteNonQuery();
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public d_departamento_detalle getObjectd_departamento_detalle(System.Int32 myID)
{
d_departamento_detalle myd_departamento_detalle = new d_departamento_detalle();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_detalle,id_departamento,nombre,cargo,codigo,fono_fijo,fono_movil FROM d_departamento_detalle WHERE (id_detalle=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myd_departamento_detalle.id_detalle = Convert.ToInt32(myReader[0]);
myd_departamento_detalle.id_departamento = Convert.ToInt32(myReader[1]);
myd_departamento_detalle.nombre = myReader[2].ToString();
myd_departamento_detalle.cargo = myReader[3].ToString();
myd_departamento_detalle.codigo = myReader[4].ToString();
myd_departamento_detalle.fono_fijo = myReader[5].ToString();
myd_departamento_detalle.fono_movil = myReader[6].ToString();
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(myd_departamento_detalle);
}
public e_recursos_empresas getObjecte_recursos_empresas(System.Int32 id_expediente, int id_empresa)
{
e_recursos_empresas mye_recursos_empresas = new e_recursos_empresas();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_expediente,id_empresa,estado FROM e_recursos_empresas WHERE (id_empresa=" + id_empresa + " and id_expediente=" + id_expediente + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
mye_recursos_empresas.id_expediente = Convert.ToInt32(myReader[0]);
mye_recursos_empresas.id_empresa = Convert.ToInt32(myReader[1]);
mye_recursos_empresas.estado = Convert.ToBoolean(myReader[2]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(mye_recursos_empresas);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public z_prueba_equipo getObjectz_prueba_equipo(System.Int32 myID)
{
z_prueba_equipo myz_prueba_equipo = new z_prueba_equipo();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_prueba_equipo,fecha,id_carro,estado FROM z_prueba_equipo WHERE (id_prueba_equipo=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_prueba_equipo.id_prueba_equipo = Convert.ToInt32(myReader[0]);
myz_prueba_equipo.fecha = Convert.ToDateTime(myReader[1]);
myz_prueba_equipo.id_carro = Convert.ToInt32(myReader[2]);
myz_prueba_equipo.estado = Convert.ToBoolean(myReader[3]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(myz_prueba_equipo);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public z_operadoras getObjectz_operadoras(System.Int32 myID)
{
z_operadoras myz_operadoras = new z_operadoras();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_operadora,nombres,apellidos,rut,fecha_nacimiento,direccion,comuna,telefono,celular FROM z_operadoras WHERE (id_operadora=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_operadoras.id_operadora = Convert.ToInt32(myReader[0]);
myz_operadoras.nombres = myReader[1].ToString();
myz_operadoras.apellidos = myReader[2].ToString();
myz_operadoras.rut = myReader[3].ToString();
myz_operadoras.fecha_nacimiento = Convert.ToDateTime(myReader[4]);
myz_operadoras.direccion = myReader[5].ToString();
myz_operadoras.comuna = myReader[6].ToString();
myz_operadoras.telefono = myReader[7].ToString();
myz_operadoras.celular = myReader[8].ToString();
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(myz_operadoras);
}
public int HayExpEnRadio(PointD centro, int radio, PointD pt_exp)
{
int intCarro = 0;
PointD x1 = new PointD(centro.X - radio, centro.Y - radio);
PointD x2 = new PointD(centro.X + radio, centro.Y + radio);
var myBase = new CnxBase();
var myD4MCnx = new CnxBase();
string reqSQL = "SELECT cnf.id_carro As carrosel FROM m001_companias cia, m001_config cnf ";
reqSQL += "WHERE geom && st_setsrid('BOX3D(" + x1.ToString() + "," + x2.ToString() + ")'::box3d,32719) ";
reqSQL += "AND st_Distance(st_GeometryFromText('POINT(" + centro.ToString() + ")',32719),st_GeometryFromText('POINT(" + pt_exp.ToString() + ")',32719)) < " + radio.ToString() + " ";
reqSQL += "AND cia.id_compania = cnf.id_compania ";
try
{
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
foreach (DataRow dr in myResult.Tables[0].Rows)
{
intCarro = Convert.ToInt32(dr["carrosel"].ToString());
}
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(intCarro);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public p_interes getObjectp_interes(System.Int32 myID)
{
p_interes myp_interes = new p_interes();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_puntos,radio_text,label1,combo1,label2,tabla FROM p_interes WHERE (id_puntos=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myp_interes.id_puntos = Convert.ToInt32(myReader[0]);
myp_interes.radio_text = myReader[1].ToString();
myp_interes.label1 = myReader[2].ToString();
myp_interes.combo1 = myReader[3].ToString();
myp_interes.label2 = myReader[4].ToString();
myp_interes.tabla = myReader[5].ToString();
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(myp_interes);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public p_policia getObjectp_policia(System.Int32 myID)
{
p_policia myp_policia = new p_policia();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT gid,sgc1,sgc2,geom FROM p_policia WHERE (gid=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myp_policia.gid = Convert.ToInt32(myReader[0]);
myp_policia.sgc1 = Convert.ToInt64(myReader[1]);
myp_policia.sgc2 = myReader[2].ToString();
myp_policia.the_geom = myReader[3].ToString();
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(myp_policia);
}
//### X_ORDEN_GRUPO_DISTINTO
public int xOrdenGrupoDistinto(int codigoLlamado, int idArea)
{
CnxBase myD4MCnx = new CnxBase();
int codigoRetorno = 0;
string reqSQL = "SELECT id_area, id_llamado, id_distinto FROM x_orden_grupo_distinto WHERE id_area = " + idArea + " and id_llamado = " + codigoLlamado + "";
try
{
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
if (myResult.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr_row in myResult.Tables[0].Rows)
{
if (codigoLlamado.ToString() == dr_row["id_llamado"].ToString() && idArea.ToString() == dr_row["id_area"].ToString())
{
codigoRetorno = Convert.ToInt32(dr_row["id_distinto"].ToString());
}
else
{
codigoRetorno = 0;
}
}
}
else
{
codigoRetorno = 0;
}
return(codigoRetorno);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public k_grifos getObjectk_grifos(System.Int32 myID)
{
k_grifos myk_grifos = new k_grifos();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT gid,identifica,comuna_,direccion,the_geom FROM k_grifos WHERE (gid=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myk_grifos.gid = Convert.ToInt32(myReader[0]);
myk_grifos.identifica = Convert.ToInt32(myReader[1]);
myk_grifos.comuna_ = myReader[2].ToString();
myk_grifos.direccion = myReader[3].ToString();
myk_grifos.the_geom = myReader[4].ToString();
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(myk_grifos);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public k_areas getObjectk_areas(System.Int32 myID)
{
k_areas myk_areas = new k_areas();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT gid,id_area,count,geom FROM k_areas WHERE (id_area=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myk_areas.gid = Convert.ToInt32(myReader[0]);
myk_areas.id_area = Convert.ToInt32(myReader[1]);
myk_areas.count = Convert.ToInt64(myReader[2]);
myk_areas.the_geom = myReader[3].ToString();
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(myk_areas);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public z_cuarteleros getObjectz_cuarteleros(System.Int32 myID)
{
z_cuarteleros myz_cuarteleros = new z_cuarteleros();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_cuartelero,id_compania,nombres,apellidos,rut,fecha_nacimiento,telefono,celular,tipo_sangre,alergia,padece FROM z_cuarteleros WHERE (id_cuartelero=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_cuarteleros.id_cuartelero = Convert.ToInt32(myReader[0]);
myz_cuarteleros.id_compania = Convert.ToInt32(myReader[1]);
myz_cuarteleros.nombres = myReader[2].ToString();
myz_cuarteleros.apellidos = myReader[3].ToString();
myz_cuarteleros.rut = myReader[4].ToString();
myz_cuarteleros.fecha_nacimiento = Convert.ToDateTime(myReader[5]);
myz_cuarteleros.telefono = myReader[6].ToString();
myz_cuarteleros.celular = myReader[7].ToString();
myz_cuarteleros.tipo_sangre = myReader[8].ToString();
myz_cuarteleros.alergia = myReader[9].ToString();
myz_cuarteleros.padece = myReader[10].ToString();
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(myz_cuarteleros);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public dh_herramientas getObject(System.Int32 myID)
{
dh_herramientas dh_herram = new dh_herramientas();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_herramienta,id_subcategoria,herramienta FROM dh_herramientas WHERE (id_herramienta=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
dh_herram.id_herramienta = Convert.ToInt32(myReader[0]);
dh_herram.id_subcategoria = Convert.ToInt32(myReader[1]);
dh_herram.herramienta = myReader[2].ToString();
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(dh_herram);
}
public string[] get_ListaCarrosCompania()
{
string[] lista;
CnxBase myBase = new CnxBase();
string SQL = "SELECT nombre_compania FROM z_companias order by nombre_compania";
try
{
string nombre = "";
int i = 0;
DataSet ds_ncompania = new DataSet();
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlDataAdapter da_da = new NpgsqlDataAdapter(SQL, myConn);
da_da.Fill(ds_ncompania);
lista = new string[ds_ncompania.Tables[0].Rows.Count];
foreach (DataRow rs_rs in ds_ncompania.Tables[0].Rows)
{
lista[i] = rs_rs["nombre_compania"].ToString();
i++;
}
return(lista);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + SQL));
}
}
//### Nueva Interfaz de Material Mayor
//public DataSet GetNombreConductor(int id_conductor)
//{
// CnxBase myBase = new CnxBase();
// string reqSQL = "select zv.apellidos || '' || zv.nombres as nombre_voluntario from z_conductores zc left join z_voluntarios zv on zv.id_voluntario = zc.id_cuart_vol where zc.id_conductor = " + id_conductor;
// try
// {
// CnxBase myD4MCnx = new CnxBase();
// DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
// return myResult;
// }
// catch (Exception myErr)
// {
// throw (new Exception(myErr.ToString() + reqSQL));
// }
//}
public DataSet GetNombreConductor(int id_conductor, int id_tipo_conductor)
{
CnxBase myBase = new CnxBase();
string reqSQL = "";
if (id_tipo_conductor == 1)
{
reqSQL = "select zv.apellidos || ' ' || zv.nombres as nombre_voluntario from z_conductores zc left join z_cuarteleros zv on zv.id_cuartelero = zc.id_cuart_vol where zc.id_conductor = " + id_conductor;
}
else
{
reqSQL = "select zv.apellidos || ' ' || zv.nombres as nombre_voluntario from z_conductores zc left join z_voluntarios zv on zv.id_voluntario = zc.id_cuart_vol where zc.id_conductor = " + id_conductor;
}
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public z_locutores getObjectz_locutores(System.Int32 myID)
{
z_locutores myz_locutores = new z_locutores();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_locutor,login,password,admin,id_tipo_locutor, id_op_vol FROM z_locutores WHERE (id_locutor=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_locutores.id_locutor = Convert.ToInt32(myReader[0]);
myz_locutores.login = myReader[1].ToString();
myz_locutores.password = myReader[2].ToString();
myz_locutores.admin = Convert.ToBoolean(myReader[3]);
myz_locutores.id_tipo_locutor = Convert.ToInt32(myReader[4]);
myz_locutores.id_op_vol = Convert.ToInt32(myReader[5]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(myz_locutores);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public bitacora_llamados getObjectbitacora_llamados(System.Int32 myID)
{
bitacora_llamados mybitacora_llamados = new bitacora_llamados();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_evento,fecha,evento,id_operadora,id_carro, id_expediente, tipo FROM bitacora_llamados WHERE (id_evento=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
mybitacora_llamados.id_evento = Convert.ToInt32(myReader[0]);
mybitacora_llamados.fecha = Convert.ToDateTime(myReader[1]);
mybitacora_llamados.evento = myReader[2].ToString();
mybitacora_llamados.id_operadora = Convert.ToInt32(myReader[3]);
mybitacora_llamados.id_carro = Convert.ToInt32(myReader[4]);
mybitacora_llamados.id_expediente = Convert.ToInt32(myReader[5]);
mybitacora_llamados.tipo = Convert.ToString(myReader[6]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(mybitacora_llamados);
}
public z_locutores Login(string user, string password)
{
z_locutores myz_locutores = null;
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_locutor,login,password,admin FROM z_locutores WHERE (login='******' and password='******')";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myz_locutores = new z_locutores();
myz_locutores.id_locutor = Convert.ToInt32(myReader[0]);
myz_locutores.login = myReader[1].ToString();
myz_locutores.password = myReader[2].ToString();
myz_locutores.admin = Convert.ToBoolean(myReader[3]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(myz_locutores);
}
/// <summary>
/// get an instance of object
/// </summary>
/// <param name="myID"></param>
public s_bomba getObjects_bomba(System.Int32 myID)
{
s_bomba mys_bomba = new s_bomba();
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_especialidad,id_sector,prioridad,contacto,id_central FROM s_bomba WHERE (id_especialidad=" + myID + ")";
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
mys_bomba.id_especialidad = Convert.ToInt32(myReader[0]);
mys_bomba.id_sector = Convert.ToInt32(myReader[1]);
mys_bomba.prioridad = Convert.ToInt32(myReader[2]);
mys_bomba.contacto = myReader[3].ToString();
mys_bomba.id_central = Convert.ToInt32(myReader[4]);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
return(mys_bomba);
}
public DataSet ReporteFrecuenciaLlamadosPorCompañia(int mes, int año)
{
CnxBase myBase = new CnxBase();
string reqSQL = @"select clave as tipo, get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'1') as c1,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'2') as c2,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'3') as c3,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'4') as c4,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'5') as c5,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'6') as c6,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'7') as c7,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'8') as c8,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'9') as c9,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'10') as c10,
get_frecuencia_llamados_compania(" + mes + "," + año + @",clave,'11') as c11
from z_llamados where codigo_llamado<100";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
