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