public int RecuperaUltimoIDRecado()
{
CnxBase myBase = new CnxBase();
int idUltimo = 0;
string reqSQL = "SELECT max(id_recado_cab) as recado FROM m10_recado_cab";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
foreach (DataRow r in myResult.Tables[0].Rows)
{
if (r["recado"].ToString() == "")
{
idUltimo = 1;
}
else
{
idUltimo = Convert.ToInt32(r["recado"].ToString());
}
}
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(idUltimo);
}
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 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));
}
}
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));
}
}
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);
}
//### 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));
}
}
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));
}
}
//### 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));
}
}
public static DataSet Query(string sql)
{
try
{
CnxBase myD4MCnx = new CnxBase();
myD4MCnx.setQueryCnx();
DataSet myResult = myD4MCnx.GetDataSet(sql, myD4MCnx);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString()));
}
}
public DataSet Getz_tipo_carro_despacho()
{
string reqSQL = "SELECT * FROM z_tipo_carro order by id_tipo_carro";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr + reqSQL));
}
}
public DataSet Getz_tipo_carro(string texto)
{
string reqSQL = "SELECT DISTINCT alias, 'lalalal' as tipo_nom FROM x_grupo_alias ORDER BY alias";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr + reqSQL));
}
}
public DataSet GetColumnas(string relname)
{
string reqSQL = "SELECT c.oid, a.attname as nombre, col_description(c.oid, a.attnum) as descripcion FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname ='" + relname + "' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet GetDistincts(string tabla, string columna)
{
string reqSQL = "SELECT DISTINCT " + columna + " as distintos FROM " + tabla;
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getbitacora_llamados_expediente(int id_expediente)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT fecha, z_locutores.login, z_carros.nombre, bitacora_llamados.evento, bitacora_llamados.tipo, z_carros.id_carro FROM bitacora_llamados left join z_carros on bitacora_llamados.id_carro=z_carros.id_carro join z_locutores on bitacora_llamados.id_operadora=z_locutores.id_locutor where bitacora_llamados.id_expediente=" + id_expediente + " order by fecha desc";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getbitacora_llamados_carro_limit(int id_carro)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT fecha, z_locutores.login, z_carros.nombre, bitacora_llamados.evento FROM bitacora_llamados, z_carros, z_locutores where bitacora_llamados.id_carro=" + id_carro + " and bitacora_llamados.id_operadora=z_locutores.id_locutor and bitacora_llamados.id_carro=z_carros.id_carro" + " order by fecha desc limit 100";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getz_llamados_clave(string clave)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT *, (clave || ' ' || descripcion) as desc FROM z_llamados WHERE clave='" + clave + "' order by codigo_llamado";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getz_llamados_codigo(int codigo_llamado)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT codigo_llamado, (clave || ' ' || descripcion) as desc FROM z_llamados WHERE codigo_llamado between " + (codigo_llamado * 100) + " and " + (codigo_llamado * 100 + 99) + " order by codigo_llamado";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getz_llamados_principal()
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT codigo_llamado, (clave || ' ' || descripcion) as desc,clave FROM z_llamados WHERE codigo_llamado<100 order by codigo_llamado";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getz_llamados_incendio()
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT * FROM z_llamados WHERE incendio=true order by codigo_llamado";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet GetListz_conductores(int id_compania)
{
CnxBase myBase = new CnxBase();
string reqSQL = "select id_conductor, apellidos||' '||nombres as nombre_completo from z_voluntarios, z_conductores where id_tipo_conductor=2 and id_cuart_vol=id_voluntario and id_compania=" + id_compania + " UNION select id_conductor, apellidos||' '||nombres as nombre_completo from z_cuarteleros, z_conductores where id_tipo_conductor=1 and id_cuart_vol=id_cuartelero and id_compania=" + id_compania;
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getz_operadorasLista()
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT *, apellidos||' '||nombres as nombre_completo FROM z_operadoras";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
/// <summary>
/// get a DataSet from records
/// </summary>
public DataSet Getz_conductores()
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT * FROM z_conductores where temporal=false";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
//### Listado de Conductores Bloqueados
public DataSet GetListz_conductoresBloqueados()
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT id_conductor, apellidos||' '||nombres as nombre_completo, codigo_conductor FROM z_voluntarios, z_conductores WHERE id_tipo_conductor=2 AND id_cuart_vol=id_voluntario AND disponible=false UNION SELECT id_conductor, apellidos||' '||nombres as nombre_completo, codigo_conductor FROM z_cuarteleros, z_conductores WHERE id_tipo_conductor=1 AND id_cuart_vol=id_cuartelero AND disponible=false ORDER BY nombre_completo";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet GetDataSet(int id_subcategoria)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT * FROM dh_herramientas WHERE id_subcategoria=" + id_subcategoria;
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getz_operadorasLista2()
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT *, 'OP ' || id_operadora as nombre_completo FROM z_operadoras ORDER BY id_operadora";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
/// <summary>
/// get a DataSet from records
/// </summary>
public DataSet Gets_bomba()
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT * FROM s_bomba";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw(new Exception(myErr.ToString() + reqSQL));
}
}
/// <summary>
/// get a DataSet from records
/// </summary>
public DataSet Getz_orden(int codigo_llamado)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT * FROM z_orden where codigo_llamado=" + codigo_llamado + " order by id_tipo_carro asc";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Getz_orden_tipo(int codigo_llamado)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT z_tipo_carro.id_tipo_carro,tipo_carro_letra,id_orden FROM z_orden, z_tipo_carro where codigo_llamado=" + codigo_llamado + " and z_orden.id_tipo_carro=z_tipo_carro.id_tipo_carro order by orden_numero asc";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Gets_bomba_sector(int id_sector)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT s_bomba.*,s_central.* FROM s_bomba,s_central WHERE id_sector=" + id_sector + " and s_bomba.id_central=s_central.id_central order by prioridad asc";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
public DataSet Geta_agenda_detalle(int id)
{
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT * FROM a_agenda_detalle where id_subcat=" + id + " order by id_empresa";
try
{
CnxBase myD4MCnx = new CnxBase();
DataSet myResult = myD4MCnx.GetDataSet(reqSQL);
return(myResult);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
}
