Example #1
0
        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);
        }
Example #2
0
        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);
        }
Example #3
0
        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);
        }