public DataSet MostrarDatosDePerfiles()
{
CadenaDeConexion cdc = new CadenaDeConexion();
DataSet ds = new DataSet();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_perf_cod_encabezado as Codigo, perf_nombre as Nombre FROM tbl_perfil_encabezado";
OdbcDataAdapter m_datos = new OdbcDataAdapter(cmd);
ds = new DataSet();
m_datos.Fill(ds);
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(ds);
}
public DataSet MostrarDatosDePerfilesDeUsuarios(string usuario)
{
CadenaDeConexion cdc = new CadenaDeConexion();
DataSet ds = new DataSet();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_perf_cod_encabezado as Codigo, perf_nombre as Nombre FROM tbl_perfil_encabezado WHERE PK_Perf_cod_encabezado IN (SELECT FK_Perf_codigo from tbl_relacion_usuario_perfil WHERE FK_Usu_codigo = (SELECT PK_Usu_Codigo from tbl_usuario where usu_nickname = '" + usuario + "'))";
OdbcDataAdapter m_datos = new OdbcDataAdapter(cmd);
ds = new DataSet();
m_datos.Fill(ds);
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(ds);
}
public void EliminarRelacionPerfilUsuario(string usuario, string perfil)
{
CadenaDeConexion cdc = new CadenaDeConexion();
string username = usuario;
string perfname = perfil;
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "DELETE FROM tbl_relacion_usuario_perfil WHERE FK_Usu_codigo = " + usuario + " AND FK_Perf_codigo = " + perfil + "";
cmd.ExecuteNonQuery();
MessageBox.Show("Removido");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public String ExtrarCodigoDeUsuario(string usuario)
{
string datos = "";
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
OdbcDataReader Reader;
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_Usu_codigo FROM tbl_usuario WHERE usu_nickname = '" + usuario + "'";
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
datos = (Reader["PK_Usu_codigo"].ToString());
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ERROR");
}
return(datos);
}
public DataSet ConsultarDatosDeUsuarios1()
{
CadenaDeConexion cdc = new CadenaDeConexion();
DataSet ds = new DataSet();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_Usu_codigo as Codigo, usu_nickname as Nombre, usu_estado as Estado FROM tbl_usuario WHERE usu_estado = 1";
OdbcDataAdapter m_datos = new OdbcDataAdapter(cmd);
ds = new DataSet();
m_datos.Fill(ds);
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(ds);
}
public void CrearRelacionPerfilUsuario(string usuario, string perfil)
{
CadenaDeConexion cdc = new CadenaDeConexion();
string username = usuario;
string perfname = perfil;
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO tbl_relacion_usuario_perfil(FK_Usu_codigo, FK_Perf_codigo) values(" + username + "," + perfname + ");";
cmd.ExecuteNonQuery();
MessageBox.Show("Insertado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void InsertarDatosDeUsuarios(string nickname, string password)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO tbl_usuario(usu_nickname, usu_password, usu_estado) VALUES('" + nickname + "','" + password + "',1)";
cmd.ExecuteNonQuery();
MessageBox.Show("Insertado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public string[] cargarDatos1(string codigomodulo)
{
string[] datos;
datos = new string[2];
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
OdbcDataReader Reader;
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_Modulo_codigo, modulo_nombre FROM tbl_modulo WHERE PK_Modulo_codigo = '" + codigomodulo + "'";
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
datos[0] = (Reader["PK_Modulo_codigo"].ToString());
datos[1] = (Reader["modulo_nombre"].ToString());
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ERROR");
}
return(datos);
}
//CREA PERFIL_DETALLE
public bool createPerfDetalle(int perfCod, int api_codigo, int ingresar, int editar, int guardar, int borrar, int consultar)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO TBL_perfil_detalle VALUES('','" + perfCod + "','" + api_codigo + "','" + ingresar + "','" + editar + "','" + guardar + "','" + borrar + "','" + consultar + "')";
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(false);
}
}
public string[] cargarDatos1(string usuario)
{
string[] datos;
datos = new string[3];
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
OdbcDataReader Reader;
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT usu_nickname, usu_password, status FROM tbl_usuario WHERE usu_nickname = '" + usuario + "'";
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
datos[0] = (Reader["usu_nickname"].ToString());
datos[1] = (Reader["usu_password"].ToString());
datos[2] = (Reader["status"].ToString());
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ERROR");
}
return(datos);
}
//CREA ENCABEZADO_PERFIL
public bool createEncaPerf(int perfCod, string perfNom)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
int estado = 1;
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO TBL_perfil_encabezado VALUES('" + perfCod + "','" + perfNom + "','" + estado + "')";
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
}
}
catch (Exception ex)
{
return(false);
}
}
//DELETE APP PERFIL
public void deleteAppPerfil(int perfCod, int appCod)
{
CadenaDeConexion cdc = new CadenaDeConexion();
int correlativo = getCorrelativo(perfCod, appCod);
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "DELETE FROM TBL_perfil_detalle WHERE PK_correlativo = '" + correlativo + "'";
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
catch (Exception ex)
{
}
}
//GET CORRELATIVO
public int getCorrelativo(int perfCod, int appCod)
{
CadenaDeConexion cdc = new CadenaDeConexion();
OdbcDataReader dr = null;
int correlativo = 0;
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_correlativo FROM TBL_perfil_detalle WHERE perf_cod_encabezado = '" + perfCod + "' AND api_codigo = '" + appCod + "'";
dr = cmd.ExecuteReader();
dr.Read();
correlativo = Convert.ToInt32(dr["PK_correlativo"].ToString());
dr.Close();
conn.Close();
return(correlativo);
}
}
}
catch (Exception ex)
{
return(correlativo);
}
}
//SEARCH COD_MODULO
public int searchCodModulo(string nomModulo)
{
CadenaDeConexion cdc = new CadenaDeConexion();
OdbcDataReader dr = null;
int cod = 0;
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_Modulo_codigo FROM TBL_modulo WHERE modulo_nombre = '" + nomModulo + "'";
dr = cmd.ExecuteReader();
dr.Read();
cod = Convert.ToInt32(dr["PK_Modulo_codigo"].ToString());
dr.Close();
conn.Close();
return(cod);
}
}
}
catch (Exception ex)
{
return(-1);
}
}
public void ModificarDatosDeUsuarios(string usuarioActual, string usuarioNuevo, string password, string estado)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "UPDATE tbl_usuario SET usu_nickname = '" + usuarioNuevo + "', usu_password = '******', usu_estado = " + estado + " WHERE usu_nickname = '" + usuarioActual + "';";
cmd.ExecuteNonQuery();
MessageBox.Show("Modificado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public DataSet ConsultarDatosModulo()
{
CadenaDeConexion cdc = new CadenaDeConexion();
DataSet ds = new DataSet();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_Modulo_codigo as Codigo, modulo_nombre as Nombre FROM tbl_modulo where modulo_estado = 1";
OdbcDataAdapter m_datos = new OdbcDataAdapter(cmd);
ds = new DataSet();
m_datos.Fill(ds);
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(ds);
}
public void InsertarDatosModulos(string codigomodulo, string nombremodulo)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection(/*cdc.Conexion()*/ "dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO tbl_modulo(PK_Modulo_codigo, modulo_nombre, modulo_estado) VALUES(" + codigomodulo + ",'" + nombremodulo + "',1)";
cmd.ExecuteNonQuery();
MessageBox.Show("Insertado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void EliminarDocumento(string codigoaplicacionactual)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn = colchoneria "))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "UPDATE tbl_doc_asociado SET doc_estado = 0 where id_documento = " + codigoaplicacionactual + ";";
cmd.ExecuteNonQuery();
MessageBox.Show("Eliminado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
//GET APPNAME
public string getAppName(int code)
{
CadenaDeConexion cdc = new CadenaDeConexion();
OdbcDataReader dr = null;
string name = "";
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT api_descripcion FROM TBL_aplicacion WHERE PK_Api_codigo = '" + code + "'";
dr = cmd.ExecuteReader();
dr.Read();
name = dr["api_descripcion"].ToString();
dr.Close();
conn.Close();
return(name);
}
}
}
catch (Exception ex)
{
return(name);
}
}
public void InsertarDatosDocumento(string codigodoc, string nombredoc, string rutadoc, string codigoapli)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO tbl_doc_asociado(PK_Id_Documento, Nombre_doc, Ruta, FK_Api_codigo, doc_estado) VALUES(" + codigodoc + ",'" + nombredoc + "','" + rutadoc + "'," + codigoapli + ", 1)";
cmd.ExecuteNonQuery();
MessageBox.Show("Insertado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void ModificarDatosModulo(string codigoaplicacionactual, string codigoaplicacion, string nombreaplicacion, string codigomodulo)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "UPDATE tbl_aplicacion SET PK_Api_codigo = " + codigoaplicacion + ", api_descripcion = '" + nombreaplicacion + "', FK_Codigo_modulo = " + codigomodulo + " WHERE PK_Api_codigo = '" + codigoaplicacionactual + "';";
cmd.ExecuteNonQuery();
MessageBox.Show("Modificado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public DataSet ConsultarDatosDocumentos()
{
CadenaDeConexion cdc = new CadenaDeConexion();
DataSet ds = new DataSet();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_Id_Documento as Codigo, Nombre_doc as Nombre, Ruta as Ruta from tbl_doc_asociado WHERE doc_estado = 1";
OdbcDataAdapter m_datos = new OdbcDataAdapter(cmd);
ds = new DataSet();
m_datos.Fill(ds);
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(ds);
}
public String ExtraerCodigoDeAplicacion(string nombreaplicacion)
{
string datos = "";
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
OdbcDataReader Reader;
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT PK_Api_codigo FROM tbl_aplicacion WHERE api_descripcion = '" + nombreaplicacion + "'";
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
datos = (Reader["PK_Api_codigo"].ToString());
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ERROR");
}
return(datos);
}
public string[] cargarDatos1(string codigoapi)
{
string[] datos;
datos = new string[3];
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
OdbcDataReader Reader;
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT tbl_aplicacion.PK_Api_codigo, tbl_aplicacion.api_descripcion , tbl_modulo.modulo_nombre FROM tbl_modulo, tbl_aplicacion WHERE tbl_modulo.PK_Modulo_codigo = tbl_aplicacion.FK_Codigo_modulo AND tbl_aplicacion.PK_Api_codigo = " + codigoapi + " AND api_estado = 1";
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
datos[0] = (Reader["PK_Api_codigo"].ToString());
datos[1] = (Reader["api_descripcion"].ToString());
datos[2] = (Reader["modulo_nombre"].ToString());
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ERROR");
}
return(datos);
}
public DataSet ConsultarDatosAplicacion()
{
CadenaDeConexion cdc = new CadenaDeConexion();
DataSet ds = new DataSet();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT tbl_aplicacion.PK_Api_codigo as Codigo, tbl_aplicacion.api_descripcion as Aplicacion, tbl_modulo.modulo_nombre as Modulo FROM tbl_modulo, tbl_aplicacion WHERE tbl_modulo.PK_Modulo_codigo = tbl_aplicacion.FK_Codigo_modulo AND api_estado = 1";
OdbcDataAdapter m_datos = new OdbcDataAdapter(cmd);
ds = new DataSet();
m_datos.Fill(ds);
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(ds);
}
public void InsertarDatosAplicaciones(string codigoaplicacion, string nombreaplicacion, string codigomodulo)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO tbl_aplicacion(PK_Api_codigo, api_descripcion, FK_Codigo_modulo, api_estado) VALUES(" + codigoaplicacion + ",'" + nombreaplicacion + "'," + codigomodulo + ",1)";
cmd.ExecuteNonQuery();
MessageBox.Show("Insertado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void EliminarDatosModulos(string codigoapliacionactual)
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "Update tbl_aplicacion set api_estado = 0 WHERE PK_Api_codigo = " + codigoapliacionactual + ";";
cmd.ExecuteNonQuery();
MessageBox.Show("Eliminado");
}
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void cargarDatosaTxt_nombre_Modulo()
{
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
OdbcDataReader Reader;
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT modulo_nombre FROM tbl_modulo";
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
Cbo_nombre_modulo.Items.Add(Reader["modulo_nombre"].ToString());
}
}
}
conn.Close();
}
Cbo_nombre_modulo.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ERROR");
}
}
private void InterfazAsignacionDePerfiles_Load(object sender, EventArgs e)
{
CapaDiseño_Opciones cd = new CapaDiseño_Opciones();
Colores cl = cd.obtenerColores();
try
{
if (cl.ID.ToString() != "")
{
pnl_Titulo.BackColor = Color.FromArgb(Convert.ToInt32(cl.BarraDeTituloAplicaciones));
pnl_Inferior.BackColor = Color.FromArgb(Convert.ToInt32(cl.BarraDeTituloAplicaciones));
this.BackColor = Color.FromArgb(Convert.ToInt32(cl.FondoAplicaciones));
//Lbl_asignacionPerfiles.ForeColor = Color.FromArgb(Convert.ToInt32(cl.FunteDeTexto4));
}
}
catch (Exception ex) { }
DatosAsignacionDePerfiles dadp = new DatosAsignacionDePerfiles();
DataSet ds = dadp.MostrarDatosDePerfiles();
Dgv_asignacion.DataSource = ds.Tables[0];
////
Cbo_usuarios.Items.Clear();
CadenaDeConexion cdc = new CadenaDeConexion();
try
{
using (var conn = new OdbcConnection("dsn=colchoneria"))
{
OdbcDataReader Reader;
conn.Open();
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT usu_nickname FROM tbl_usuario";
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
Cbo_usuarios.Items.Add(Reader["usu_nickname"].ToString());
}
}
}
conn.Close();
}
Cbo_usuarios.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ERROR");
}
}
public Usuario obtenerObjUsuario(string usuario, string contrasena)
{
CadenaDeConexion cadenaConexion = new CadenaDeConexion();
try
{
Usuario user = new Usuario();
using (var conn = new OdbcConnection(cadenaConexion.Conexion()))
{
OdbcDataReader reader;
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from usuario where usu_nickname='" + usuario + "' and usu_password='******'";
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
return(null);
}
while (reader.Read())
{
user.nickName = (reader["usu_nickname"].ToString());
user.codigoUsuario = Convert.ToInt32(reader["usu_codigo"].ToString());
}
conn.Close();
}
}
if (user.nickName == "" && user.codigoUsuario == 0)
{
return(null);
}
return(user);
}
catch (Exception exception)
{
return(null);
}
return(null);
}