public List <eRubro> Listar() { List <eRubro> list = new List <eRubro>(); eRubro rubro = null; OracleConnection conn = D_Conexion.conectar(); try { OracleCommand command = conn.CreateCommand(); command.CommandText = "SP_SELECT_RUBRO"; command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add(new OracleParameter("P_CURSOR", OracleDbType.RefCursor)).Direction = System.Data.ParameterDirection.Output; using (OracleDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { rubro = new eRubro(); rubro.idRubro = Convert.ToInt32(dr["IDRUBRO"]); rubro.descripcionRubro = dr["IDRUBRO"].ToString(); list.Add(rubro); } } } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Close(); conn.Dispose(); } return(list); }
public DataTable obtieneRubrosporId(int idRubro) { OracleConnection conn = D_Conexion.conectar(); try { OracleCommand command = conn.CreateCommand(); command.CommandText = "select idrubro, descripcionrubro from rubro where idrubro = :idRubro order by descripcionrubro"; command.Parameters.Add(":idRubro", OracleDbType.Varchar2).Value = idRubro; OracleDataAdapter da = new OracleDataAdapter(command); DataTable dt = new DataTable("Rubro"); da.Fill(dt); return(dt); } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Close(); conn.Dispose(); } }
public DataTable obtieneComunacb() { OracleConnection conn = D_Conexion.conectar(); try { OracleCommand command = conn.CreateCommand(); command.CommandText = "select cmm.IDCOMUNA,cmm.NOMBRECOMUNA, cmm.IDCIUDAD, cd.idciudad,cd.nombreciudad, rg.idregion, rg.nombreregion from comuna cmm inner join ciudad cd on cd.idciudad = cmm.idciudad inner join region rg on rg.idregion = cd.idregion order by rg.idregion "; OracleDataAdapter da = new OracleDataAdapter(command); DataTable dt = new DataTable("Comunas"); da.Fill(dt); return(dt); } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Close(); conn.Dispose(); } }
public DataTable obtieneRubros() { OracleConnection conn = D_Conexion.conectar(); try { OracleCommand command = conn.CreateCommand(); command.CommandText = "select idrubro, descripcionrubro from rubro order by descripcionrubro"; OracleDataAdapter da = new OracleDataAdapter(command); DataTable dt = new DataTable("Rubro"); da.Fill(dt); return(dt); } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Close(); conn.Dispose(); } }
public DataTable obtieneMaxCliente() { OracleConnection cnn = D_Conexion.conectar(); try { OracleCommand command = cnn.CreateCommand(); command.CommandText = "select idcliente,nombre,appaterno,apmaterno,rut,dverificador,idrubro,direccion,codcomuna,codciudad,codregion,telefono,email from cliente where idcliente = (select max(idcliente) from cliente)"; OracleDataAdapter da = new OracleDataAdapter(command); DataTable dt = new DataTable("Cliente"); da.Fill(dt); return(dt); } catch (Exception ex) { throw new Exception("Se produjo un error en obtieneTipoUsuario" + ex.Message); } finally { cnn.Close(); cnn.Dispose(); } }
public void creaCliente(eCliente cl) { OracleConnection cnn = D_Conexion.conectar(); try { OracleCommand command = new OracleCommand("SP_INSERTACLIENTE", cnn); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add("nom", OracleDbType.Varchar2).Value = cl.nombre; command.Parameters.Add("paterno", OracleDbType.Varchar2).Value = cl.apPaterno; command.Parameters.Add("materno", OracleDbType.Varchar2).Value = cl.apMaterno; command.Parameters.Add("rut", OracleDbType.Int32).Value = cl.rut; command.Parameters.Add("dverificador", OracleDbType.Varchar2).Value = cl.dvVerificador; command.Parameters.Add("idrubro", OracleDbType.Int32).Value = cl.idRubro; command.Parameters.Add("direccion", OracleDbType.Varchar2).Value = cl.direccion; command.Parameters.Add("codcomuna", OracleDbType.Int32).Value = cl.codComuna; command.Parameters.Add("codciudad", OracleDbType.Int32).Value = cl.codCiudad; command.Parameters.Add("codregion", OracleDbType.Int32).Value = cl.codRegion; command.Parameters.Add("telefono", OracleDbType.Int32).Value = cl.telefono; command.Parameters.Add("mail", OracleDbType.Varchar2).Value = cl.email; command.ExecuteNonQuery(); command.Dispose(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cnn.Close(); cnn.Dispose(); } }
public List <eCliente> getListaClientePorRutorName(int rut, string nombre) { OracleConnection conn = D_Conexion.conectar(); List <eCliente> listaCliente = new List <eCliente>(); try { OracleCommand command = conn.CreateCommand(); //StringBuilder queryCliente = new StringBuilder(); //queryCliente.Append("select idcliente,nombre,appaterno,apmaterno,rut,dverificador,idrubro,direccion,codcomuna,codciudad,codregion,telefono,email from cliente cl inner join comuna cm on cm.idcomuna = cl.codcomuna inner join ciudad cd on cd.idciudad = cm.idciudad inner join region rg on rg.idregion = cd.idregion where (cl.rut = :rut or UPPER(cl.nombre|| ' '|| cl.appaterno ||' ' ||cl.apmaterno) like "); //queryCliente.Append(nombre); //queryCliente.Append(" )"); command.CommandText = "select idcliente,nombre,appaterno,apmaterno,rut,dverificador,idrubro,direccion,codcomuna,codciudad,codregion,telefono,email from cliente cl inner join comuna cm on cm.idcomuna = cl.codcomuna inner join ciudad cd on cd.idciudad = cm.idciudad inner join region rg on rg.idregion = cd.idregion where (cl.rut = :rut or UPPER(cl.nombre|| ' '|| cl.appaterno ||' ' ||cl.apmaterno) = :nombre ) "; //command.CommandText = queryCliente.ToString(); command.Parameters.Add(":rut", OracleDbType.Int32).Value = rut; command.Parameters.Add(":nombre", OracleDbType.Varchar2).Value = nombre.ToUpper(); OracleDataReader reader = command.ExecuteReader(); while (reader.Read()) { eCliente clienteGrid = new eCliente(); clienteGrid.idcliente = int.Parse(reader["IDCLIENTE"].ToString()); clienteGrid.nombre = reader["NOMBRE"].ToString(); //usuarioGrid.estado = short.Parse(reader["ISACTIVO"].ToString()) == 1 ? "Activo" : "Inactivo"; clienteGrid.apPaterno = reader["APPATERNO"].ToString().ToUpper(); clienteGrid.apMaterno = reader["APMATERNO"].ToString().ToUpper(); clienteGrid.rut = int.Parse(reader["RUT"].ToString()); clienteGrid.dvVerificador = reader["dverificador"].ToString(); clienteGrid.idRubro = int.Parse(reader["IDRUBRO"].ToString()); clienteGrid.direccion = reader["DIRECCION"].ToString().ToUpper(); clienteGrid.codComuna = int.Parse(reader["CODCOMUNA"].ToString()); clienteGrid.codCiudad = int.Parse(reader["CODCIUDAD"].ToString()); clienteGrid.codRegion = int.Parse(reader["CODREGION"].ToString()); clienteGrid.telefono = int.Parse(reader["TELEFONO"].ToString()); clienteGrid.email = reader["EMAIL"].ToString(); listaCliente.Add(clienteGrid); } command.Dispose(); reader.Close(); reader.Dispose(); return(listaCliente); } catch (Exception e) { throw new Exception(e.Message); } finally { conn.Close(); conn.Dispose(); } }
public DataTable obtieneTipoUsuario() { OracleConnection cnn = D_Conexion.conectar(); try { OracleCommand command = cnn.CreateCommand(); command.CommandText = "select idtipousuario,descripciontipousuario from tipousuario"; OracleDataAdapter da = new OracleDataAdapter(command); DataTable dt = new DataTable("TipoUusario"); da.Fill(dt); return(dt); } catch (Exception ex) { throw new Exception("Se produjo un error en obtieneTipoUsuario" + ex.Message); } finally { cnn.Close(); cnn.Dispose(); } }
public void creaUsuario(eUsuario usu) { OracleConnection cnn = D_Conexion.conectar(); try { OracleCommand command = new OracleCommand("SP_INSERTAUSUARIO", cnn); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add("idtpousuario", OracleDbType.Varchar2).Value = usu.idTipoUsuario; command.Parameters.Add("usuario", OracleDbType.Varchar2).Value = usu.usuario; command.Parameters.Add("clave", OracleDbType.Varchar2).Value = usu.clave; command.Parameters.Add("activo", OracleDbType.Int32).Value = usu.activo; command.Parameters.Add("idcliente", OracleDbType.Varchar2).Value = usu.idcliente; command.Parameters.Add("idprofesional", OracleDbType.Int32).Value = null; command.ExecuteNonQuery(); command.Dispose(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cnn.Close(); cnn.Dispose(); } }
public List <eCliente> getListaCliente() { OracleConnection conn = D_Conexion.conectar(); List <eCliente> listaCliente = new List <eCliente>(); try { OracleCommand command = conn.CreateCommand(); command.CommandText = "select idcliente,nombre,appaterno,apmaterno,rut,dverificador,idrubro,direccion,codcomuna,codciudad,codregion,telefono,email from cliente cl inner join comuna cm on cm.idcomuna = cl.codcomuna inner join ciudad cd on cd.idciudad = cm.idciudad inner join region rg on rg.idregion = cd.idregion"; OracleDataReader reader = command.ExecuteReader(); while (reader.Read()) { eCliente clienteGrid = new eCliente(); clienteGrid.idcliente = int.Parse(reader["IDCLIENTE"].ToString()); clienteGrid.nombre = reader["NOMBRE"].ToString(); //usuarioGrid.estado = short.Parse(reader["ISACTIVO"].ToString()) == 1 ? "Activo" : "Inactivo"; clienteGrid.apPaterno = reader["APPATERNO"].ToString().ToUpper(); clienteGrid.apMaterno = reader["APMATERNO"].ToString().ToUpper(); clienteGrid.rut = int.Parse(reader["RUT"].ToString()); clienteGrid.dvVerificador = reader["dverificador"].ToString(); clienteGrid.idRubro = int.Parse(reader["IDRUBRO"].ToString()); clienteGrid.direccion = reader["DIRECCION"].ToString().ToUpper(); clienteGrid.codComuna = int.Parse(reader["CODCOMUNA"].ToString()); clienteGrid.codCiudad = int.Parse(reader["CODCIUDAD"].ToString()); clienteGrid.codRegion = int.Parse(reader["CODREGION"].ToString()); clienteGrid.telefono = int.Parse(reader["TELEFONO"].ToString()); clienteGrid.email = reader["EMAIL"].ToString(); listaCliente.Add(clienteGrid); } command.Dispose(); reader.Close(); reader.Dispose(); return(listaCliente); } catch (Exception e) { throw new Exception(e.Message); } finally { conn.Close(); conn.Dispose(); } }
public void eliminaCliente(Int32 idCliente) { OracleConnection cnn = D_Conexion.conectar(); try { OracleCommand command = cnn.CreateCommand(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "SP_DELETE_CLIENTE"; command.Parameters.Add("IDCLIENTE", OracleDbType.Int16).Value = idCliente; OracleDataReader dr = command.ExecuteReader(); dr.Close(); command.Dispose(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cnn.Close(); cnn.Dispose(); } }
public void editaCliente(eCliente cl) { OracleConnection conn = D_Conexion.conectar(); try { OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "SP_UPDATE_CLIENTE"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("p_idCliente", OracleDbType.Int32).Value = cl.idcliente; cmd.Parameters.Add("p_nombre", OracleDbType.Varchar2).Value = cl.nombre; cmd.Parameters.Add("p_apPaterno", OracleDbType.Varchar2).Value = cl.apPaterno; cmd.Parameters.Add("p_apMaterno", OracleDbType.Varchar2).Value = cl.apMaterno; cmd.Parameters.Add("p_rut", OracleDbType.Int32).Value = cl.rut; cmd.Parameters.Add("p_dvVerificador", OracleDbType.Varchar2).Value = cl.dvVerificador; cmd.Parameters.Add("p_idrubro", OracleDbType.Int32).Value = cl.idRubro; cmd.Parameters.Add("p_direccion", OracleDbType.Varchar2).Value = cl.direccion; cmd.Parameters.Add("p_codcomuna", OracleDbType.Varchar2).Value = cl.codComuna; cmd.Parameters.Add("p_codciudad", OracleDbType.Int32).Value = cl.codCiudad; cmd.Parameters.Add("p_codregion", OracleDbType.Int32).Value = cl.codRegion; cmd.Parameters.Add("P_TELEFONO", OracleDbType.Int32).Value = cl.telefono; cmd.Parameters.Add("P_email", OracleDbType.Varchar2).Value = cl.email; cmd.ExecuteNonQuery(); //result = Convert.ToString(cmd.Parameters["P_RESULT"].Value); cmd.Dispose(); } catch (Exception ex) { throw new Exception("Se produjo un error en sp_update_cliente" + ex.Message); } finally { conn.Close(); conn.Dispose(); } }
public void creaPdf() { DateTime fecha = DateTime.Now; string fechaActual = fecha.ToString("dd-MM-yyyy"); //string ruta = 'C:\Users\M.ibarraO\Documents\DUOC\Aplicaciones\GeneraPDF\Listado_ClienteActual_' + fechaActual+'.pdf'; PdfWriter pdfwriter = new PdfWriter("C:\\Listado_ClienteActual_" + fechaActual + ".pdf"); //Crear documento PdfDocument pdf = new PdfDocument(pdfwriter); Document documento = new Document(pdf, PageSize.LETTER); documento.SetMargins(60, 20, 55, 20); PdfFont fontColumnas = PdfFontFactory.CreateFont(StandardFonts.HELVETICA_BOLD); PdfFont fontContenido = PdfFontFactory.CreateFont(StandardFonts.HELVETICA); string[] columnas = { "nombre", "paterno", "materno", "rut", "dv", "rubro", "direccion", "codcomuna", "telefono", "mail" }; float[] tamanios = { 10, 10, 10, 10, 1, 10, 20, 10, 9, 20 }; Table tabla = new Table(UnitValue.CreatePercentArray(tamanios)); tabla.SetWidth(UnitValue.CreatePercentValue(100)); foreach (string columna in columnas) { tabla.AddHeaderCell(new Cell().Add(new Paragraph(columna).SetFont(fontColumnas))); } /*CONSULTA SQL*/ OracleConnection conn = D_Conexion.conectar(); List <eCliente> listaCliente = new List <eCliente>(); try { OracleCommand command = conn.CreateCommand(); command.CommandText = "select nombre,appaterno,apmaterno,rut,dverificador,descripcionrubro,direccion,nombrecomuna,telefono,email from cliente cl inner join comuna cm on cm.idcomuna = cl.codcomuna inner join ciudad cd on cd.idciudad = cm.idciudad inner join region rg on rg.idregion = cd.idregion inner join rubro rb on rb.idrubro = cl.idrubro"; OracleDataReader reader = command.ExecuteReader(); while (reader.Read()) { // tabla.AddCell(new Cell().Add(new Paragraph(reader["IDCLIENTE"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["NOMBRE"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["APPATERNO"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["APMATERNO"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["RUT"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["DVERIFICADOR"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["descripcionrubro"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["DIRECCION"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["NOMBRECOMUNA"].ToString()))); //tabla.AddCell(new Cell().Add(new Paragraph(reader["NOMBRECIUDAD"].ToString()))); //tabla.AddCell(new Cell().Add(new Paragraph(reader["NOMBREREGION"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["TELEFONO"].ToString()))); tabla.AddCell(new Cell().Add(new Paragraph(reader["EMAIL"].ToString()))); } command.Dispose(); reader.Close(); reader.Dispose(); //Agrega en la tablapdf documento.Add(tabla); documento.Close(); } catch (Exception e) { throw new Exception(e.Message); } finally { conn.Close(); conn.Dispose(); } }