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();
}
}
