public static List <LstPermiso> getLstPermisosByIdRol(Int64 pId) { List <LstPermiso> lista = new List <LstPermiso>(); using (MySqlConnection _con = new Conexion().Conectar()) { try { _con.Open(); MySqlCommand comando = new MySqlCommand("select IdPermiso from lstpermisoRol where IdRol=@pId order by Id asc", _con); comando.Parameters.AddWithValue("@pId", pId); MySqlDataReader _reader = comando.ExecuteReader(); while (_reader.Read()) { LstPermiso item = new LstPermiso( 0, null, true, _reader.GetInt32(0), 0, PermisoDAL.getPermisoById(_reader.GetInt32(0)) ); lista.Add(item); } _reader.Close(); } catch (Exception ex) { _con.Close(); throw ex; } finally { _con.Close(); } } return(lista); }
public static LstPermiso getLstPermisoById(Int64 pId) { LstPermiso item = null; using (MySqlConnection _con = new Conexion().Conectar()) { try { _con.Open(); MySqlCommand cmdGetItemById = new MySqlCommand("select * from lstpermiso where Id=@pId", _con); cmdGetItemById.Parameters.AddWithValue("@pId", pId); MySqlDataReader _reader = cmdGetItemById.ExecuteReader(); while (_reader.Read()) { item = new LstPermiso( _reader.GetInt64(0), _reader.GetString(1), _reader.GetInt32(2) == 1?true:false, _reader.GetInt32(3), _reader.GetInt32(4), PermisoDAL.getPermisoById(_reader.GetInt32(3)) ); } _reader.Close(); } catch (Exception ex) { _con.Close(); throw ex; } finally { _con.Close(); } } return(item); }
public static bool InsertUserEmpConf(Useremp item, Sucursal itemSucursal) { bool result = true; using (MySqlConnection _con = new Conexion().Conectar()) { _con.Open(); MySqlTransaction _trans = _con.BeginTransaction(); try { MySqlCommand cmdInsertRol = new MySqlCommand("Insert into rol (Nombre) values (@Nombre)", _con, _trans); cmdInsertRol.Parameters.AddWithValue("@Nombre", item.Sucursales[0].Rol.Nombre); if (cmdInsertRol.ExecuteNonQuery() <= 0) { result = false; } else { MySqlCommand cmdUltimoId = new MySqlCommand("select last_insert_id() as id;", _con); cmdUltimoId.Transaction = _trans; item.Sucursales[0].IdRol = Convert.ToInt32(cmdUltimoId.ExecuteScalar()); item.Sucursales[0].Rol.Id = item.Sucursales[0].IdRol; } MySqlCommand cmdInsertRoles = new MySqlCommand("Insert into rol values (2,'Secretario/a'),(3,'Docente'),(4,'Administrador')", _con, _trans); if (cmdInsertRoles.ExecuteNonQuery() <= 0) { result = false; } MySqlCommand cmdInsertSucursal = new MySqlCommand("Insert into sucursal (Nombre,Direccion) values (@Nombre,@Direccion)", _con, _trans); cmdInsertRol.Parameters.AddWithValue("@Nombre", itemSucursal.Nombre); cmdInsertRol.Parameters.AddWithValue("@Direccion", itemSucursal.Direccion); if (cmdInsertRol.ExecuteNonQuery() <= 0) { result = false; } else { MySqlCommand cmdUltimoId = new MySqlCommand("select last_insert_id() as id;", _con); cmdUltimoId.Transaction = _trans; item.Sucursales[0].IdSucursal = Convert.ToInt32(cmdUltimoId.ExecuteScalar()); item.Sucursales[0].Sucursal.Id = item.Sucursales[0].IdSucursal; } MySqlCommand cmdInsertPersona = new MySqlCommand("Insert into persona (Nombre,Dui,Nit,FechaNac,Direccion) values (@Nombre,@Dui,@Nit,@FechaNac,@Direccion)", _con, _trans); cmdInsertPersona.Parameters.AddWithValue("@Nombre", item.Contrato.Empleado.Persona.Nombre); cmdInsertPersona.Parameters.AddWithValue("@Dui", item.Contrato.Empleado.Persona.Dui); cmdInsertPersona.Parameters.AddWithValue("@Nit", item.Contrato.Empleado.Persona.Nit); cmdInsertPersona.Parameters.AddWithValue("@FechaNac", item.Contrato.Empleado.Persona.FechaNac); cmdInsertPersona.Parameters.AddWithValue("@Direccion", item.Contrato.Empleado.Persona.Direccion); if (cmdInsertPersona.ExecuteNonQuery() <= 0) { result = false; } else { MySqlCommand cmdUltimoId = new MySqlCommand("select last_insert_id() as id;", _con); cmdUltimoId.Transaction = _trans; item.Contrato.Empleado.Persona.Id = Convert.ToInt32(cmdUltimoId.ExecuteScalar()); item.Contrato.Empleado.IdPersona = item.Contrato.Empleado.Persona.Id; } MySqlCommand cmdInsertEmpleado = new MySqlCommand("Insert into empleado (Telefono,Correo,IdPersona) values (@Telefono,@Correo,@IdPersona)", _con, _trans); cmdInsertEmpleado.Parameters.AddWithValue("@Telefono", item.Contrato.Empleado.Telefono); cmdInsertEmpleado.Parameters.AddWithValue("@Correo", item.Contrato.Empleado.Correo); cmdInsertEmpleado.Parameters.AddWithValue("@IdPersona", item.Contrato.Empleado.Persona.Id); if (cmdInsertEmpleado.ExecuteNonQuery() <= 0) { result = false; } else { MySqlCommand cmdUltimoId = new MySqlCommand("select last_insert_id() as id;", _con); cmdUltimoId.Transaction = _trans; item.Contrato.Empleado.Id = Convert.ToInt32(cmdUltimoId.ExecuteScalar()); item.Contrato.IdEmpleado = item.Contrato.Empleado.Id; } MySqlCommand cmdInsertCargo = new MySqlCommand("Insert into cargo (Nombre) values (@Nombre)", _con, _trans); cmdInsertCargo.Parameters.AddWithValue("@Nombre", item.Contrato.Cargo.Nombre); if (cmdInsertCargo.ExecuteNonQuery() <= 0) { result = false; } else { MySqlCommand cmdUltimoId = new MySqlCommand("select last_insert_id() as id;", _con); cmdUltimoId.Transaction = _trans; item.Contrato.Cargo.Id = Convert.ToInt32(cmdUltimoId.ExecuteScalar()); item.Contrato.IdCargo = item.Contrato.Cargo.Id; } MySqlCommand cmdInsertContrato = new MySqlCommand("Insert into contrato (FhInicio,Estado,IdCargo,IdEmpleado) values (@FhInicio,@Estado,@IdCargo,@IdEmpleado)", _con, _trans); cmdInsertContrato.Parameters.AddWithValue("@FhInicio", DateTime.Now.ToString("yyyy-MM-dd")); cmdInsertContrato.Parameters.AddWithValue("@Estado", "A"); cmdInsertContrato.Parameters.AddWithValue("@IdCargo", item.Contrato.IdCargo); cmdInsertContrato.Parameters.AddWithValue("@IdEmpleado", item.Contrato.Empleado.Id); if (cmdInsertContrato.ExecuteNonQuery() <= 0) { result = false; } else { MySqlCommand cmdUltimoId = new MySqlCommand("select last_insert_id() as id;", _con); cmdUltimoId.Transaction = _trans; item.IdContrato = Convert.ToInt32(cmdUltimoId.ExecuteScalar()); item.Contrato.Id = Convert.ToInt32(cmdUltimoId.ExecuteScalar()); } MySqlCommand cmdInsertUsuarioEmp = new MySqlCommand("Insert into useremp(Login,Pass,IdContrato)" + " values (@Login,md5(@Pass),@IdContrato)", _con, _trans); cmdInsertUsuarioEmp.Parameters.AddWithValue("@Login", item.Login); cmdInsertUsuarioEmp.Parameters.AddWithValue("@Pass", item.Pass); cmdInsertUsuarioEmp.Parameters.AddWithValue("@IdContrato", item.IdContrato); if (cmdInsertUsuarioEmp.ExecuteNonQuery() <= 0) { result = false; } MySqlCommand cmdInsertCargos = new MySqlCommand("Insert into cargo (Nombre) values ('Director');Insert into cargo (Nombre) values ('Docente');Insert into cargo (Nombre) values ('Secretario/a')", _con, _trans); if (cmdInsertCargos.ExecuteNonQuery() <= 0) { result = false; } MySqlCommand cmdInsertAcsSucursal = new MySqlCommand("Insert into acssucursal (IdUserEmp,IdRol,IdSucursal) values (@IdUserEmp,@IdRol,@IdSucursal)", _con, _trans); cmdInsertAcsSucursal.Parameters.AddWithValue("@IdUserEmp", item.Id); cmdInsertAcsSucursal.Parameters.AddWithValue("@IdRol", item.Sucursales[0].IdRol); cmdInsertAcsSucursal.Parameters.AddWithValue("@IdSucursal", item.Sucursales[0].IdSucursal); if (cmdInsertAcsSucursal.ExecuteNonQuery() <= 0) { result = false; } else { MySqlCommand cmdUltimoId = new MySqlCommand("select last_insert_id() as id;", _con); cmdUltimoId.Transaction = _trans; item.Sucursales[0].Id = Convert.ToInt32(cmdUltimoId.ExecuteScalar()); } // asignacion de permisos al acceso de sucursal MySqlCommand cmdInsertPermisos = new MySqlCommand("Insert into permiso values" + "(1,'Gestionar Matriculas','Gestionar Matriculas')," + "(2,'Matricular Estudiantes','Gestionar Matriculas')," + "(3,'Editar Matriculas','Gestionar Matriculas')," + "(4,'Desertar Estudiantes','Gestionar Matriculas')," + "(5,'Gestionar Empleados','Gestionar Empleados')," + "(6,'Crear Empleados','Gestionar Empleados')," + "(7,'Editar Empleados','Gestionar Empleados')," + "(8,'Crear Contratos','Gestionar Empleados')," + "(9,'Editar Contratos','Gestionar Empleados')," + "(10,'Terminar Contratos','Gestionar Empleados')," + "(11,'Gestionar Cursos','Gestionar Cursos')," + "(12,'Crear Cursos','Gestionar Cursos')," + "(13,'Editar Cursos','Gestionar Cursos')," + "(14,'Anular Cursos','Gestionar Cursos')," + "(15,'Gestionar Ingresos','Gestionar Ingresos')," + "(16,'Registrar Ingresos','Gestionar Ingresos')," + "(17,'Anular Ingresos','Gestionar Ingresos')," + "(18,'Exportar Ingresos y Egresos del mes','Gestionar Ingresos')," + "(19,'Gestionar Egresos','Gestionar Egresos')," + "(20,'Registrar Egresos','Gestionar Egresos')," + "(21,'Editar Egresos','Gestionar Egresos')," + "(22,'Anular Egresos','Gestionar Egresos')," + "(23,'Gestionar Ajustes del Sistema','Gestionar Ajustes del Sistema')," + "(24,'Configurar Conexión de base de datos','Gestionar Ajustes del Sistema')," + "(25,'Importar base de datos','Gestionar Ajustes del Sistema')," + "(26,'Exportar base de datos','Gestionar Ajustes del Sistema')" , _con, _trans); if (cmdInsertUsuarioEmp.ExecuteNonQuery() <= 0) { result = false; } List <Permiso> permisos = PermisoDAL.getPermisos(); foreach (Permiso obj in permisos) { MySqlCommand cmdInsertLstPermiso = new MySqlCommand("Insert into lstpermiso values (null,CURRENT_TIMESTAMP,1,@Permiso,@AcsSucursal)", _con, _trans); cmdInsertContrato.Parameters.AddWithValue("@Permiso", obj.Id); cmdInsertContrato.Parameters.AddWithValue("@AcsSucursal", item.Sucursales[0].Id); if (cmdInsertCargos.ExecuteNonQuery() <= 0) { result = false; } } MySqlCommand cmdInsertLstPermisoRol1 = new MySqlCommand("Insert into lstpermisorol values " + "(null,CURRENT_TIMESTAMP,1,1)," + "(null,CURRENT_TIMESTAMP,1,2)," + "(null,CURRENT_TIMESTAMP,1,3)," + "(null,CURRENT_TIMESTAMP,1,4)," + "(null,CURRENT_TIMESTAMP,1,5)," + "(null,CURRENT_TIMESTAMP,1,6)," + "(null,CURRENT_TIMESTAMP,1,7)," + "(null,CURRENT_TIMESTAMP,1,8)," + "(null,CURRENT_TIMESTAMP,1,9)," + "(null,CURRENT_TIMESTAMP,1,10)," + "(null,CURRENT_TIMESTAMP,1,11)," + "(null,CURRENT_TIMESTAMP,1,12)," + "(null,CURRENT_TIMESTAMP,1,13)," + "(null,CURRENT_TIMESTAMP,1,14)," + "(null,CURRENT_TIMESTAMP,1,15)," + "(null,CURRENT_TIMESTAMP,1,16)," + "(null,CURRENT_TIMESTAMP,1,17)," + "(null,CURRENT_TIMESTAMP,1,18)," + "(null,CURRENT_TIMESTAMP,1,19)," + "(null,CURRENT_TIMESTAMP,1,20)," + "(null,CURRENT_TIMESTAMP,1,21)," + "(null,CURRENT_TIMESTAMP,1,22)," + "(null,CURRENT_TIMESTAMP,1,23)," + "(null,CURRENT_TIMESTAMP,1,24)," + "(null,CURRENT_TIMESTAMP,1,25)," + "(null,CURRENT_TIMESTAMP,1,26)" , _con, _trans); if (cmdInsertLstPermisoRol1.ExecuteNonQuery() <= 0) { result = false; } MySqlCommand cmdInsertLstPermisoRol2 = new MySqlCommand("Insert into lstpermisorol values " + "(null,CURRENT_TIMESTAMP,2,1)," + "(null,CURRENT_TIMESTAMP,2,2)," + "(null,CURRENT_TIMESTAMP,2,3)," + "(null,CURRENT_TIMESTAMP,2,4)," + "(null,CURRENT_TIMESTAMP,2,5)," + "(null,CURRENT_TIMESTAMP,2,6)," + "(null,CURRENT_TIMESTAMP,2,7)," + "(null,CURRENT_TIMESTAMP,2,8)," + "(null,CURRENT_TIMESTAMP,2,9)," + "(null,CURRENT_TIMESTAMP,2,11)," + "(null,CURRENT_TIMESTAMP,2,12)," + "(null,CURRENT_TIMESTAMP,2,13)," + "(null,CURRENT_TIMESTAMP,2,15)," + "(null,CURRENT_TIMESTAMP,2,16)," + "(null,CURRENT_TIMESTAMP,2,18)," + "(null,CURRENT_TIMESTAMP,2,19)," + "(null,CURRENT_TIMESTAMP,2,20)," + "(null,CURRENT_TIMESTAMP,2,21)," + "(null,CURRENT_TIMESTAMP,2,22)," + "(null,CURRENT_TIMESTAMP,2,23)" , _con, _trans); if (cmdInsertLstPermisoRol2.ExecuteNonQuery() <= 0) { result = false; } if (result) { _trans.Commit(); } else { _trans.Rollback(); } } catch (Exception ex) { _con.Close(); throw ex; } finally { _con.Close(); } } return(result); }