private void CargarDatos()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = "SELECT * FROM area_servicio.ft_view_ultima_tasa_cambio();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
lblVenta.Text = pgDr.GetString("venta");
lblCompra.Text = pgDr.GetString("compra");
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de cargar la tasa de cambio. " + Exc.Message);
}
}
public static DateTime ObtenerHoraServidor(PgSqlConnection pConexion)
{
DateTime v_resultado = Convert.ToDateTime(null);
if (pConexion.State != System.Data.ConnectionState.Open)
{
pConexion.Open();
}
string sentencia = "SELECT * FROM arca_tesoros_conf.ft_view_variables_tiempo();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
v_resultado = pgDr.GetDateTime("fecha_hora_servidor");
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
pgComando = null;
return(v_resultado);
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, "Utilidades.cs", "ObtenerHoraServidor");
return(Convert.ToDateTime(null));
}
}
private void ObtenerActividad()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM arca_tesoros.ft_proc_obtener_actividad_del_dia (:p_id_area_atencion)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_area_atencion", PgSqlType.Int).Value = Pro_ID_Area_Atencion;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
Pro_ID_Actividad = pgDr.GetInt32("id_actividad");
}
pgDr.Close();
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "ObtenerActividad");
}
}
private void CargarDatosColaborador()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM arca_tesoros.ft_view_ficha_ingreso (:p_id_colaborador)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_colaborador", PgSqlType.Int).Value = Pro_ID_Colaborador;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
txtNombreColaborador.Text = pgDr.GetString("nombre_colaborador");
txtIdentidadColaborador.Text = pgDr.GetString("numero_identidad");
txtFuerzasEspeciales.Text = pgDr.GetString("area_atencion");
v_ruta_fotografia = pgDr.GetString("direccion_fotografia");
}
pgDr.Close();
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "CargarDatosColaborador");
}
}
public void CargarDatos()
{
v_lista_noticias.Clear();
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM area_servicio.ft_view_noticias_cliente (:p_id_cliente_servicio);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
while (pgDr.Read())
{
v_lista_noticias.Add(pgDr.GetString("texto_noticia"));
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de obtener lista de noticias. " + Exc.Message);
}
}
public List <Estatus> Estatus(DateTime fecha1, DateTime fecha2)
{
fecha1 = Convert.ToDateTime(fecha1.ToShortDateString());
fecha2 = Convert.ToDateTime(fecha2.ToShortDateString());
fecha1 = Convert.ToDateTime(fecha1);
fecha2 = Convert.ToDateTime(fecha2);
List <Estatus> lista = new List <Estatus>();
using (PgSqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = " select*from Modifica";
using (PgSqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read() == true)
{
DateTime fechaRegistro = dr.GetDateTime("fecha");
if ((fechaRegistro >= fecha1) && (fechaRegistro <= fecha2))
{
lista.Add(new Estatus(dr.GetDecimal("modificaciones"), dr.GetString("tipo"), dr.GetDateTime("fecha")));
}
}
dr.Close();
}
}
return(lista);
}
static void Count(PgSqlConnection connection)
{
DataTable datatable = new DataTable();
PgSqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT COUNT
(_uid)
FROM public._gps_log";
Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
Console.Write(Environment.NewLine);
while (myReader.Read())
{
DataRow dr = datatable.NewRow();
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetString(i) + "\t");
dr[i] = myReader.GetString(i);
}
datatable.Rows.Add(dr);
Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
}
finally
{
myReader.Close();
}
foreach (DataRow row in datatable.Rows) // Loop over the rows.
{
Console.WriteLine("--- Row ---"); // Print separator.
foreach (var item in row.ItemArray) // Loop over the items.
{
Console.Write("Item: "); // Print label.
Console.WriteLine(item); // Invokes ToString abstract method.
}
}
Console.WriteLine("############");
Console.WriteLine(datatable.Rows[0].ItemArray[0].ToString());
}
private void ObtenerFusiblesIndicadores()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = "SELECT * FROM arca_tesoros.ft_proc_obtiene_fusibles_indicadores();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
v_conteo_fusible_solicitudes = pgDr.GetInt32("conteo_solicitudes");
v_conteo_fusible_cumpleanieros = pgDr.GetInt32("conteo_cumpleanieros");
if (v_conteo_fusible_cumpleanieros == 0)
{
fusibleCumpleanios.Visible = false;
}
else
{
fusibleCumpleanios.Visible = true;
}
}
pgDr.Close();
}
catch (Exception Exc)
{
}
}
private void ObtenerInformacionActividad()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM arca_tesoros.ft_view_datos_actividades (
:p_id_actividad
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_actividad", PgSqlType.Int).Value = Pro_ID_Actividad;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
lblEncabezado.Text = "Lista de Asistencia para el día " + pgDr.GetDateTime("fecha").Date.ToShortDateString();
}
pgDr.Close();
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "ObtenerInformacionActividad");
}
}
public List <Ventas> ObtenerListaVENTASDelDía()
{
List <Ventas> listpro = new List <Ventas>();
DateTime fechaActual = DateTime.Today;
using (PgSqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = " SELECT * FROM ventas ORDER BY id";
///select *FROM ventas WHERE fecha_venta= '2015/12/12'
using (PgSqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read() == true)
{
DateTime fechaDeVenta = dr.GetDateTime("fecha_venta");
if (fechaDeVenta == fechaActual)
{
listpro.Add(new Ventas(dr.GetInt32("id_productos"), dr.GetInt32("cantidad"), dr.GetDecimal("precio_de_venta"), dr.GetDateTime("fecha_venta")));
}
}
dr.Close();
}
}
return(listpro);
}
private bool CargarDatosTicketPosicion()
{
string v_datos_posicion = null;
ValidarConexion();
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM area_servicio.ft_view_posicion_asignada (
:p_usuario,
:p_agencia,
:p_cliente
);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_usuario", PgSqlType.VarChar).Value = Pro_UsuarioEmpleado;
pgComando.Parameters.Add("p_agencia", PgSqlType.Int).Value = Pro_Sucursal;
pgComando.Parameters.Add("p_cliente", PgSqlType.Int).Value = Pro_Cliente;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
v_datos_posicion = ConfigurationSettings.AppSettings["TEXTO_DESCRIPTIVO"] + " " +
pgDr.GetString("posicion");
}
pgDr.Close();
pgDr = null;
pgComando.Dispose();
sentencia = null;
if (string.IsNullOrEmpty(v_datos_posicion))
{
return(false);
}
else
{
return(true);
}
}
catch (Exception Exc)
{
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"CargarDatosTicketPosicion()");
v_depurador = null;
MessageBox.Show(Exc.Message, "FLUCOL");
return(false);
}
}
private void CargarDatosEmpleadoParaEdicion(string pCodigoEmpleado)
{
ValidarConexion();
string sentencia = @"SELECT * FROM area_servicio.ft_view_datos_empleado_para_edicion(:pCodigoEmpleado);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("pCodigoEmpleado", PgSqlType.VarChar).Value = pCodigoEmpleado;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
int v_id_cargo = 0;
int v_id_agencia = 0;
while (pgDr.Read())
{
txtCodigoEmpleado.Text = pgDr.GetString("codigo_empleado");
txtPrimerNombre.Text = pgDr.GetString("primer_nombre");
txtSegundoNombre.Text = pgDr.GetString("segundo_nombre");
txtPrimerApellido.Text = pgDr.GetString("primer_apellido");
txtSegundoApellido.Text = pgDr.GetString("segundo_apellido");
txtIdentidadEmpleado.Text = pgDr.GetString("identidad_empleado");
v_id_cargo = pgDr.GetInt32("id_cargo");
v_id_agencia = pgDr.GetInt32("id_agencia");
}
foreach (dsConfiguraciones.dtCargosEmpleadosRow iterador in dsConfiguraciones1.dtCargosEmpleados)
{
if (iterador.id_cargo == v_id_cargo)
{
gridCargos.EditValue = iterador.id_cargo;
break;
}
}
foreach (dsConfiguraciones.dtAgenciasServicioRow iterador in dsConfiguraciones1.dtAgenciasServicio)
{
if (iterador.id_agencia_servicio == v_id_agencia)
{
gridAgencias.EditValue = iterador.id_agencia_servicio;
break;
}
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("ALGO SALIO EN EL MOMENTO DE REGISTRAR EL EMPLEADO. " + Exc.Message, "FLUCOL");
}
}
private void LlamadoTickets()
{
ValidarConexion();
PgSqlConnection vConexion = new PgSqlConnection(Pro_Conexion.ConnectionString);
vConexion.Password = Pro_Conexion.Password;
vConexion.Open();
string sentencia = @"SELECT * FROM area_servicio.ft_view_consulta_llamados_tickets (
:p_agencia_servicio,
:p_cliente_servicio
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, vConexion);
pgComando.Parameters.Add("p_agencia_servicio", PgSqlType.Int).Value = Pro_Sucursal;
pgComando.Parameters.Add("p_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
v_ticket = pgDr.GetString("ticket");
v_posicion = pgDr.GetString("posicion");
v_tipo_ticket = pgDr.GetInt32("tipo_ticket");
v_primera_letra = pgDr.GetString("primera_letra");
v_segunda_letra = pgDr.GetString("segunda_letra");
v_tercera_letra = pgDr.GetString("tercera_letra");
v_cuarta_letra = pgDr.GetString("cuarta_letra");
v_quinta_letra = pgDr.GetString("quinta_letra");
v_sexta_letra = pgDr.GetString("sexta_letra");
v_longitud_ticket = pgDr.GetInt32("longitud_ticket");
ReproducirAudioLlamadoTicket();
}
pgDr.Close();
pgDr = null;
pgComando.Dispose();
vConexion.Close();
vConexion.Dispose();
sentencia = null;
}
catch (Exception Exc)
{
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"LlamadoTickets()");
v_depurador = null;
}
}
private void GenerarTicket()
{
ValidarConexion();
splashScreenManager1.ShowWaitForm();
PgSqlTransaction pgTrans = Pro_Conexion.BeginTransaction();
string sentencia = @"SELECT * FROM configuracion.sp_proc_genera_correlativos_ticket (
:p_id_agencia_servicio,
:p_id_cliente_servicio,
:p_id_tipo_ticket_servicio,
:p_id_operacion_servicio,
:p_direccion_ip
);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio;
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio;
pgComando.Parameters.Add("p_id_tipo_ticket_servicio", PgSqlType.Int).Value = Pro_ID_Tipo_Ticket_Servicio;
pgComando.Parameters.Add("p_id_operacion_servicio", PgSqlType.Int).Value = Pro_ID_Operacion_Servicio;
pgComando.Parameters.Add("p_direccion_ip", PgSqlType.VarChar).Value = Pro_IP_Host;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
Pro_Ticket_Generado = pgDr.GetString("numero_ticket");
}
pgTrans.Commit();
pgDr.Close();
pgComando.Dispose();
sentencia = null;
splashScreenManager1.CloseWaitForm();
}
catch (Exception Exc)
{
splashScreenManager1.CloseWaitForm();
pgTrans.Rollback();
Pro_Ticket_Generado = null;
MessageBox.Show(Exc.Message, "FLUCOL");
}
}
private void ObtenerNombreSucursal()
{
if (Pro_Conexion.State != System.Data.ConnectionState.Open)
{
try
{
Pro_Conexion.Open();
}
catch (Exception Exc)
{
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"ObtenerNombreSucursal()");
v_depurador = null;
}
}
try
{
string sentencia = @"SELECT * FROM area_servicio.ft_view_nombre_agencia_servicio (
:p_id_agencia_servicio,
:p_id_cliente_servicio
);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio;
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_ClienteServicio;
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
Pro_NombreAgenciaServicio = pgDr.GetString("nombre_agencia");
}
pgDr.Close();
pgComando.Dispose();
sentencia = null;
}
catch (Exception Exc)
{
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"ObtenerNombreSucursal()");
v_depurador = null;
}
}
private int ObtenerEstadoTicket()
{
ValidarConexion();
int v_estado_ticket = 0;
string sentencia = @"SELECT * FROM area_servicio.ft_view_estado_ticket (
:p_id_ticket_servicio,
:p_id_cliente_servicio,
:p_id_agencia_servicio
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_ticket_servicio", PgSqlType.VarChar).Value = Pro_Ticket_Servicio;
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_ClienteServicio;
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
v_estado_ticket = pgDr.GetInt32("estado");
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
pgDr = null;
return(v_estado_ticket);
}
catch (Exception Exc)
{
MessageBox.Show("ALGO SALIÓ MAL EN EL MOMENTO DE OBTENER ESTADO DEL TICKET. " + Exc.Message);
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"ObtenerEstadoTicket()");
v_depurador = null;
return(v_estado_ticket);
}
}
private void CargarDatos()
{
PgSqlConnection v_conexion_temporal = new PgSqlConnection(Pro_Conexion.ConnectionString);
v_conexion_temporal.Password = Pro_Conexion.Password;
v_conexion_temporal.Open();
string sentencia = @"SELECT * FROM area_servicio.ft_view_dashboard_empleados_con_mas_tickets_atendidos(:p_id_cliente_servicio,
:p_id_agencia_servicio,
:p_desde,
:p_hasta);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, v_conexion_temporal);
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio;
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_Agencia_Servicio;
pgComando.Parameters.Add("p_desde", PgSqlType.Date).Value = Pro_Desde;
pgComando.Parameters.Add("p_hasta", PgSqlType.Date).Value = Pro_Hasta;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
lblNombreEmpleado.Text = pgDr.GetString("nombre_empleado");
lblNumeroTicketsAtendidos.Text = pgDr.GetString("numero_tickets_atendidos");
lblSucursalEmpleado.Text = pgDr.GetString("agencia_servicio");
}
pgDr.Close();
pgDr = null;
sentencia = null;
pgComando.Dispose();
v_conexion_temporal.Close();
v_conexion_temporal.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de cargar Dashboard \"EMPLEADO CON MAS TICKETS ATENDIDOS\"." + Exc.Message);
}
}
public List <Socios> ObtenerSocios()
{
List <Socios> list = new List <Socios>();
using (PgSqlCommand comand = conn.CreateCommand())
{
comand.CommandText = "SELECT * FROM socios";
using (PgSqlDataReader dr = comand.ExecuteReader())
{
while (dr.Read() == true)
{
list.Add(new Socios(dr.GetBoolean("administrador"), dr.GetString("nombre"), dr.GetString("contra"), dr.GetInt32("id")));
}
dr.Close();
}
return(list);
}
}
public List <Inventario> Inventariado()
{
List <Inventario> list = new List <Inventario>();
using (PgSqlCommand comand = conn.CreateCommand())
{
comand.CommandText = "SELECT p.codigo, p.descripcion, i.existencias FROM productos p INNER JOIN inventario i ON p.id= i.id ORDER BY existencias";
using (PgSqlDataReader dr = comand.ExecuteReader())
{
while (dr.Read() == true)
{
list.Add(new Inventario(dr.GetString("codigo"), dr.GetString("descripcion"), dr.GetInt32("existencias")));
}
dr.Close();
}
return(list);
}
}
public List <Estatus> Estatus()
{
List <Estatus> lista = new List <Estatus>();
using (PgSqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = " select*from Modifica";
using (PgSqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read() == true)
{
lista.Add(new Estatus(dr.GetDecimal("modificaciones"), dr.GetString("tipo"), dr.GetDateTime("fecha")));
}
dr.Close();
}
}
return(lista);
}
public Producto ObtenerProductoNombre(string Nombre)
{
Producto pro = new Producto();
using (PgSqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = " select *FROM productos WHERE descripcion='" + Nombre + "'";
///select *FROM ventas WHERE fecha_venta= '2015/12/12'
using (PgSqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read() == true)
{
pro = new Producto(dr.GetInt32("id"), dr.GetString("codigo"), dr.GetString("descripcion"), dr.GetDecimal("precio"));
}
dr.Close();
}
return(pro);
}
}
public int NumeroMaximoDeProductos()
{
int numeromaximo = 0;
using (PgSqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT max(id) from productos; ";
using (PgSqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read() == true)
{
numeromaximo = dr.GetInt32("max");
}
dr.Close();
}
}
return(numeromaximo + 1);
}
private void CargarDatos()
{
PgSqlConnection v_conexion_temporal = new PgSqlConnection(Pro_Conexion.ConnectionString);
v_conexion_temporal.Password = Pro_Conexion.Password;
v_conexion_temporal.Open();
string sentencia = @"SELECT * FROM area_servicio.ft_view_dashboard_promedio_atencion(
:p_id_cliente_servicio,
:p_id_agencia_servicio,
:p_desde,
:p_hasta);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, v_conexion_temporal);
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_ClienteServicio;
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio;
pgComando.Parameters.Add("p_desde", PgSqlType.Date).Value = Pro_Desde;
pgComando.Parameters.Add("p_hasta", PgSqlType.Date).Value = Pro_Hasta;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
lblPromedioAtencion.Text = pgDr.GetString("promedio_atencion");
}
pgDr.Close();
pgDr = null;
sentencia = null;
pgComando.Dispose();
v_conexion_temporal.Close();
v_conexion_temporal.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de cargar Dashboard \"PROMEDIO DE ATENCION\"." + Exc.Message);
}
}
public bool GetDecimal(string query, string field, out decimal value)
{
bool found = false;
value = 0;
using (PgSqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
using (PgSqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read() == true)
{
value = dr.GetDecimal(field);
found = true;
}
dr.Close();
}
}
return(found);
}
static void PrintDept(PgSqlConnection connection)
{
PgSqlCommand command = connection.CreateCommand();
command.CommandText = "select * from test";
//async
Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetName(i).ToString() + "\t");
}
Console.Write(Environment.NewLine);
while (myReader.Read())
{
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetString(i) + "\t");
}
Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
}
finally
{
myReader.Close();
}
/*
* // Call the Close method when you are finished using the PgSqlDataReader
* // to use the associated PgSqlConnection for any other purpose.
* // Or put the reader in the using block to call Close implicitly.
* //sync
* Console.WriteLine("Starting synchronous retrieval of data...");
* using (PgSqlDataReader reader = command.ExecuteReader())
* {
* // printing the column names
* for (int i = 0; i < reader.FieldCount; i++)
* Console.Write(reader.GetName(i).ToString() + "\t");
* Console.Write(Environment.NewLine);
* // Always call Read before accesing data
* while (reader.Read())
* {
* // printing the table content
* for (int i = 0; i < reader.FieldCount; i++)
* Console.Write(reader.GetValue(i).ToString() + "\t");
* Console.Write(Environment.NewLine);
* }
* }
* */
}
//For SELECT statements
public DataTable get_DataTable(string cmd)
{
try
{
if (pgSqlConnection != null && IsConnected)
{
DataTable datatable = new DataTable();
PgSqlCommand command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
Console.Write(Environment.NewLine);
while (myReader.Read())
{
DataRow dr = datatable.NewRow();
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetString(i) + "\t");
dr[i] = myReader.GetString(i);
}
datatable.Rows.Add(dr);
Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
}
finally
{
myReader.Close();
}
foreach (DataRow row in datatable.Rows) // Loop over the rows.
{
Console.WriteLine("--- Row ---"); // Print separator.
foreach (var item in row.ItemArray) // Loop over the items.
{
Console.Write("Item: "); // Print label.
Console.WriteLine(item); // Invokes ToString abstract method.
}
}
return(datatable);
}
else
{
return(null);
}
}
catch (PgSqlException ex)
{
Console.WriteLine("Exception occurs: {0}", ex.Error);
return(null);
}
}
//For SELECT statements
public DataTable get_DataTable(string cmd)
{
Stopwatch stopWatch = new Stopwatch();
PgSqlCommand command = null;
stopWatch.Start();
try
{
if (pgSqlConnection != null && IsConnected)
{
DataTable datatable = new DataTable();
command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
//Console.Write("In progress...");
//while (!cres.IsCompleted)
{
//Console.Write(".");
//Perform here any operation you need
}
//if (cres.IsCompleted)
//Console.WriteLine("Completed.");
//else
//Console.WriteLine("Have to wait for operation to complete...");
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
//Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
//Console.Write(Environment.NewLine);
while (myReader.Read())
{
DataRow dr = datatable.NewRow();
for (int i = 0; i < myReader.FieldCount; i++)
{
//Console.Write(myReader.GetString(i) + "\t");
dr[i] = myReader.GetString(i);
}
datatable.Rows.Add(dr);
//Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
}
finally
{
myReader.Close();
stopWatch.Stop();
// Get the elapsed time as a TimeSpan value.
TimeSpan ts = stopWatch.Elapsed;
// Format and display the TimeSpan value.
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
SiAuto.Main.AddCheckpoint(Level.Debug, "sql query take time:" + elapsedTime, cmd);
}
/*
* foreach (DataRow row in datatable.Rows) // Loop over the rows.
* {
* Console.WriteLine("--- Row ---"); // Print separator.
* foreach (var item in row.ItemArray) // Loop over the items.
* {
* Console.Write("Item: "); // Print label.
* Console.WriteLine(item); // Invokes ToString abstract method.
* }
* }
*/
if (command != null)
{
command.Dispose();
}
command = null;
return(datatable);
}
else
{
return(null);
}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("GetDataTable exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
log.Error("GetDataTable exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd);
Console.ResetColor();
if (command != null)
{
command.Dispose();
}
command = null;
return(null);
}
}
static void Test(PgSqlConnection con, string sql,
CommandType cmdType, CommandBehavior behavior,
string testDesc)
{
PgSqlCommand cmd = null;
PgSqlDataReader rdr = null;
int c;
int results = 0;
Console.WriteLine("Test: " + testDesc);
Console.WriteLine("[BEGIN SQL]");
Console.WriteLine(sql);
Console.WriteLine("[END SQL]");
cmd = new PgSqlCommand(sql, con);
cmd.CommandType = cmdType;
Console.WriteLine("ExecuteReader...");
rdr = cmd.ExecuteReader(behavior);
if (rdr == null)
{
Console.WriteLine("IDataReader has a Null Reference.");
}
else
{
do
{
// get the DataTable that holds
// the schema
DataTable dt = rdr.GetSchemaTable();
if (rdr.RecordsAffected != -1)
{
// Results for
// SQL INSERT, UPDATE, DELETE Commands
// have RecordsAffected >= 0
Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
}
else if (dt == null)
{
Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
}
else
{
// Results for
// SQL not INSERT, UPDATE, nor DELETE
// have RecordsAffected = -1
Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);
// Results for a SQL Command (CREATE TABLE, SET, etc)
// will have a null reference returned from GetSchemaTable()
//
// Results for a SQL SELECT Query
// will have a DataTable returned from GetSchemaTable()
results++;
Console.WriteLine("Result Set " + results + "...");
// number of columns in the table
Console.WriteLine(" Total Columns: " +
dt.Columns.Count);
// display the schema
foreach (DataRow schemaRow in dt.Rows)
{
foreach (DataColumn schemaCol in dt.Columns)
{
Console.WriteLine(schemaCol.ColumnName +
" = " +
schemaRow[schemaCol]);
}
Console.WriteLine();
}
int nRows = 0;
string output, metadataValue, dataValue;
// Read and display the rows
Console.WriteLine("Gonna do a Read() now...");
while (rdr.Read())
{
Console.WriteLine(" Row " + nRows + ": ");
for (c = 0; c < rdr.FieldCount; c++)
{
// column meta data
DataRow dr = dt.Rows[c];
metadataValue =
" Col " +
c + ": " +
dr["ColumnName"];
// column data
if (rdr.IsDBNull(c) == true)
{
dataValue = " is NULL";
}
else
{
dataValue =
": " +
rdr.GetValue(c);
}
// display column meta data and data
output = metadataValue + dataValue;
Console.WriteLine(output);
}
nRows++;
}
Console.WriteLine(" Total Rows: " +
nRows);
}
} while(rdr.NextResult());
Console.WriteLine("Total Result sets: " + results);
rdr.Close();
}
}
private bool ValidarUsuarioLogueo()
{
bool v_encontro_usuario = false;
ValidarConexion();
try
{
string sentencia = @"SELECT * FROM area_servicio.ft_proc_valida_usuario_acceso (
:p_usuario,
:p_contrasenia,
:p_id_agencia_servicio,
:p_id_cliente_servicio
);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_usuario", PgSqlType.VarChar).Value = txtUsuario.Text;
pgComando.Parameters.Add("p_contrasenia", PgSqlType.VarChar).Value = txtContrasenia.Text;
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_Sucursal;
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_Cliente;
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
Pro_NombreEmpleado = pgDr.GetString("nombre_empleado");
Pro_UsuarioEmpleado = pgDr.GetString("usuario_empleado");
Pro_ID_NivelAcceso = pgDr.GetInt32("id_nivel_acceso_empleado");
Pro_DescripcionNivelAcceso = pgDr.GetString("nivel_acceso_empleado");
Pro_CargoEmpleado = pgDr.GetString("cargo_empleado");
Pro_CodigoEmpleado = pgDr.GetString("codigo_empleado");
v_encontro_usuario = true;
}
pgDr.Close();
sentencia = null;
pgDr = null;
pgComando.Dispose();
if (v_encontro_usuario)
{
if ((NIVELES_ACCESO)Pro_ID_NivelAcceso == NIVELES_ACCESO.OPERACIONAL)
{
if (CargarDatosTicketPosicion())
{
return(true);
}
else
{
return(false);
}
}
else
{
return(true);
}
}
else
{
return(false);
}
}
catch (Exception Exc)
{
MessageBox.Show(Exc.Message, "FLUCOL");
txtUsuario.Focus();
return(false);
}
}
private bool GetRd(String strSQL)
{
int results = 0;
int c;
try
{
connPG.Open();
cmdPG.CommandText = strSQL;// "select * from users";
cmdPG.Connection = connPG;
rdr = cmdPG.ExecuteReader();
if (rdr == null)
{
Console.WriteLine("IDataReader has a Null Reference.");
}
else
{
do
{
// get the DataTable that holds
// the schema
DataTable dt = rdr.GetSchemaTable();
DataTable dtData = new DataTable();
DataView dv = new DataView();
dv.Table = dt;
if (rdr.RecordsAffected != -1)
{
// Results for
// SQL INSERT, UPDATE, DELETE Commands
// have RecordsAffected >= 0
System.Diagnostics.Debug.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
}
else if (dt == null)
{
System.Diagnostics.Debug.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
}
else
{
// Results for
// SQL not INSERT, UPDATE, nor DELETE
// have RecordsAffected = -1
System.Diagnostics.Debug.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);
// Results for a SQL Command (CREATE TABLE, SET, etc)
// will have a null reference returned from GetSchemaTable()
//
// Results for a SQL SELECT Query
// will have a DataTable returned from GetSchemaTable()
results++;
System.Diagnostics.Debug.WriteLine("Result Set " + results + "...");
// number of columns in the table
System.Diagnostics.Debug.WriteLine(" Total Columns: " +
dt.Columns.Count);
// display the schema
foreach (DataRow schemaRow in dt.Rows)
{
foreach (DataColumn schemaCol in dt.Columns)
{
System.Diagnostics.Debug.WriteLine(schemaCol.ColumnName +
" = " +
schemaRow[schemaCol]);
}
// System.Diagnostics.Debug.WriteLine();
}
int nRows = 0;
string output, metadataValue, dataValue;
// Read and display the rows
System.Diagnostics.Debug.WriteLine("Gonna do a Read() now...");
while (rdr.Read())
{
System.Diagnostics.Debug.WriteLine(" Row " + nRows + ": ");
for (c = 0; c < rdr.FieldCount; c++)
{
// column meta data
DataRow dr = dt.Rows[c];
metadataValue =
" Col " +
c + ": " +
dr["ColumnName"];
// column data
if (rdr.IsDBNull(c) == true)
{
dataValue = " is NULL";
}
else
{
dataValue =
": " +
rdr.GetValue(c);
}
// display column meta data and data
output = metadataValue + dataValue;
System.Diagnostics.Debug.WriteLine(output);
}
nRows++;
}
System.Diagnostics.Debug.WriteLine(" Total Rows: " +
nRows);
}
} while (rdr.NextResult());
Console.WriteLine("Total Result sets: " + results);
rdr.Close();
connPG.Close();
}
getMessage = "Успешно!";
return(true);
}
catch (Exception ex)
{
getMessage = "Неудачно!" + ex;
return(false);
}
}
