public static List <Contrato> getContratos(int pLimit)
{
List <Contrato> lista = new List <Contrato>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from contrato order by Id desc limit @pLimit", _con);
comando.Parameters.AddWithValue("@pLimit", pLimit);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Contrato item = new Contrato(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.IsDBNull(3) ? null : _reader.GetString(3),
_reader.GetString(4),
_reader.GetInt64(5),
_reader.GetInt64(6),
_reader.GetInt64(7),
CargoDAL.getCargoById(_reader.GetInt64(5)),
EmpleadoDAL.getEmpleadoById(_reader.GetInt64(6))
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static List <Egreso> getEgresosIndexerParametro(Int64 pYear, string pMonth, string pText, Int64 pIdSucursal, Int64 pNumber1, Int64 pNumber2)
{
List <Egreso> lista = new List <Egreso>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from egreso where (Upper(Nombre) like '%" + pText.ToUpper() + "%' or Upper(Tipo) like '%" + pText.ToUpper() +
"%') and (YEAR(FhRegistro)=@pYear and MONTH(FhRegistro)=@pMonth and IdSucursal=@pIdSucursal) and Estado='C' order by Id desc limit @pNumber1,@pNumber2", _con);
comando.Parameters.AddWithValue("@pNumber1", pNumber1 == 0 ? pNumber1 : pNumber1 * pNumber2);
comando.Parameters.AddWithValue("@pNumber2", pNumber2);
comando.Parameters.AddWithValue("@pYear", pYear);
comando.Parameters.AddWithValue("@pMonth", pMonth);
comando.Parameters.AddWithValue("@pIdSucursal", pIdSucursal);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Egreso item = new Egreso(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetDecimal(4),
_reader.GetString(5),
_reader.GetInt64(6)
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static Curso getCursoById(Int64 pId)
{
Curso item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from curso where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Curso(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetString(4),
_reader.GetString(5),
_reader.GetString(6),
_reader.GetString(7),
_reader.GetInt64(8),
_reader.GetInt64(9),
_reader.GetInt64(10),
ContratoDAL.getContratoById(_reader.GetInt64(8)),
YearDAL.getYearById(_reader.GetInt64(10)),
DiasDAL.getDiasByIdCurso(_reader.GetInt64(0)),
DetCursoDAL.getDetscursoByIdCurso(_reader.GetInt64(0))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static bool insertProducto(Producto item, Useremp pUser)
{
bool result = true;
using (MySqlConnection _con = new Conexion().Conectar())
{
_con.Open();
MySqlTransaction _trans = _con.BeginTransaction();
try
{
MySqlCommand cmdInsertProducto = new MySqlCommand("Insert into producto (Nombre, Precio) values (@Nombre, @Precio)", _con, _trans);
cmdInsertProducto.Parameters.AddWithValue("@Nombre", item.Nombre);
cmdInsertProducto.Parameters.AddWithValue("@Precio", item.Precio);
if (cmdInsertProducto.ExecuteNonQuery() <= 0)
{
result = false;
}
else
{
MySqlCommand cmdUltimoId = new MySqlCommand("select last_insert_id() as id;", _con);
cmdUltimoId.Transaction = _trans;
item.Id = Convert.ToInt32(cmdUltimoId.ExecuteScalar());
}
if (result)
{
_trans.Commit();
}
else
{
_trans.Rollback();
}
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(result);
}
public static List <Detfactura> getDetsfacturaByIdFactura(Int64 pIdFactura)
{
List <Detfactura> lista = new List <Detfactura>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from detfactura where IdFactura=@pIdFactura order by Id asc", _con);
comando.Parameters.AddWithValue("@pIdFactura", pIdFactura);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Detfactura item = new Detfactura(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetDecimal(2),
_reader.GetDecimal(3),
_reader.GetString(4),
_reader.IsDBNull(5) ? null : _reader.GetString(5),
_reader.GetInt64(6),
_reader.GetInt64(7),
ProductoDAL.getProductoById(_reader.GetInt64(7)),
MatricdetfacDAL.getMatricdetfacByIdDetFactura(_reader.GetInt64(0))
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static List <List <Int64> > getIdsEgresosNoParametro(string pText, Int64 pIdSucursal, int pLimit)
{
List <List <Int64> > lista = new List <List <Int64> >();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from egreso where (Upper(Nombre) like '%" + pText.ToUpper() + "%' or Upper(Tipo) like '%" + pText.ToUpper() +
"%') and IdSucursal=@pIdSucursal and Estado='C' order by Id desc", _con);
comando.Parameters.AddWithValue("@pIdSucursal", pIdSucursal);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
if (lista.Count == 0)
{
lista.Add(new List <Int64>());
}
if (lista[lista.Count - 1].Count == pLimit)
{
lista.Add(new List <Int64>());
lista[lista.Count - 1].Add(1);
}
else
{
lista[lista.Count - 1].Add(1);
}
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static Estudiante getEstudent(string pNombre)
{
Estudiante item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select e.* from persona as p inner join estudiante as e on e.IdPersona=p.Id where Upper(Nombre)=Upper(@pNombre)", _con);
cmdGetItemById.Parameters.AddWithValue("@pNombre", pNombre);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Estudiante(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetString(4),
_reader.GetString(5),
_reader.GetString(6),
_reader.GetString(7),
_reader.GetString(8),
_reader.GetInt64(9),
PersonaDAL.getPersonaById(_reader.GetInt64(9))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static List <Encargado> searchEncargados(string Text, int pLimit)
{
List <Encargado> lista = new List <Encargado>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select distinct e.* from encargado as e inner join persona as p " +
"on p.Id=e.IdPersona where( Upper(p.Nombre) like '%" + Text.ToUpper() + "%' or Upper(e.Telefono) like '%" + Text.ToUpper() +
"%' or Upper(e.LugarTrabajo) like '%" + Text.ToUpper() + "%' or Upper(e.Trabajo)) like '%" + Text.ToUpper() + "%'" +
" order by e.Id asc limit @pLimit", _con);
comando.Parameters.AddWithValue("@pLimit", pLimit);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Encargado item = new Encargado(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetInt64(4),
PersonaDAL.getPersonaById(_reader.GetInt64(4))
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static Matricula verificarMatriculado(Int64 pIdCurso, Int64 IdEstudiante)
{
Matricula item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from matricula where IdCurso=@pIdCurso and IdEstudiante=@IdEstudiante", _con);
cmdGetItemById.Parameters.AddWithValue("@pIdCurso", pIdCurso);
cmdGetItemById.Parameters.AddWithValue("@IdEstudiante", IdEstudiante);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Matricula(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetInt32(3),
_reader.GetString(4),
_reader.GetInt64(5),
_reader.GetInt64(6),
EstudianteDAL.getEstudianteById(_reader.GetInt64(6)),
CuotaDAL.getCuotasByIdMatricula(_reader.GetInt64(0), 50),
DetMatriculaDAL.getDetsmatriculaByIdMatricula(_reader.GetInt64(0), 2)
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static bool insertPersona(Persona item, Useremp pUser)
{
bool result = true;
using (MySqlConnection _con = new Conexion().Conectar())
{
_con.Open();
MySqlTransaction _trans = _con.BeginTransaction();
try
{
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.Nombre);
cmdInsertPersona.Parameters.AddWithValue("@Dui", "");
cmdInsertPersona.Parameters.AddWithValue("@Nit", "");
cmdInsertPersona.Parameters.AddWithValue("@FechaNac", DateTime.Today.ToString("yyyy/MM/dd"));
cmdInsertPersona.Parameters.AddWithValue("@Direccion", "");
if (cmdInsertPersona.ExecuteNonQuery() <= 0)
{
result = false;
}
if (result)
{
_trans.Commit();
}
else
{
_trans.Rollback();
}
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(result);
}
public static Contrato getContratoById(Int64 pId)
{
Contrato item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from contrato where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Contrato(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.IsDBNull(3) ? null : _reader.GetString(3),
_reader.GetString(4),
_reader.GetInt64(5),
_reader.GetInt64(6),
_reader.GetInt64(7),
CargoDAL.getCargoById(_reader.GetInt64(5)),
EmpleadoDAL.getEmpleadoById(_reader.GetInt64(6))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static List <AcsSucursal> getAcsSucursalesByIdUser(Int64 pId)
{
List <AcsSucursal> lista = new List <AcsSucursal>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from acssucursal where IdUserEmp=@pId order by Id asc", _con);
comando.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
AcsSucursal item = new AcsSucursal(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetInt32(2),
_reader.GetInt32(3),
_reader.GetInt64(4),
RolDAL.getRolById(_reader.GetInt32(3)),
SucursalDAL.getSucursaloById(_reader.GetInt64(4)),
LstPermisoDAL.getLstPermisosByIdAcs(_reader.GetInt64(0))
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static Estudiante getEstudianteById(Int64 pId)
{
Estudiante item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from estudiante where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Estudiante(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetString(4),
_reader.GetString(5),
_reader.GetString(6),
_reader.GetString(7),
_reader.GetString(8),
_reader.GetInt64(9),
PersonaDAL.getPersonaById(_reader.GetInt64(9))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
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 Factura getFacturaById(Int64 pId)
{
Factura item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from factura where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Factura(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetDecimal(4),
_reader.GetString(5),
_reader.GetInt64(6),
_reader.GetInt64(7),
DetFacturaDAL.getDetsfacturaByIdFactura(_reader.GetInt64(0))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static List <Evaluacion> getEvaluacionByIdDetCurso(Int64 pIdDetCurso)
{
List <Evaluacion> lista = new List <Evaluacion>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from evaluacion where IdDetCurso=@pIdDetCurso order by Id asc", _con);
comando.Parameters.AddWithValue("@pIdDetCurso", pIdDetCurso);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Evaluacion item = new Evaluacion(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetString(4),
_reader.GetInt64(5),
CalificacionDAL.getCalificacionesByEvaluacion(_reader.GetInt64(0))
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static Empleado getEmpleadoByDuiNit(string Dui,string Nit)
{
Empleado item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select e.* from empleado as e inner join persona as p on p.Id=e.IdPersona where Dui=@Dui or Nit=@Nit", _con);
cmdGetItemById.Parameters.AddWithValue("@Dui", Dui);
cmdGetItemById.Parameters.AddWithValue("@Nit", Nit);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Empleado(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
CvDAL.getCvById(_reader.GetInt64(0)),
_reader.GetInt64(4),
PersonaDAL.getPersonaById(_reader.GetInt64(4))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return item;
}
public static List <Producto> getProductosReservados(Int64 pIdPersona, int pLimit)
{
List <Producto> lista = new List <Producto>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from producto as p right join detfactura as df " +
" on df.IdProducto=p.Id right join factura as f on f.IdPersona=p.Id where f.IdPersona=@pIdPersona " +
" and (df.Total+df.Descuento)<p.Precio order by Id asc limit @pLimit", _con);
comando.Parameters.AddWithValue("@pLimit", pLimit);
comando.Parameters.AddWithValue("@pIdPersona", pIdPersona);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Producto item = new Producto(
_reader.GetInt64(0),
_reader.GetString(1),
Decimal.Round(_reader.GetDecimal(2), 2)
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static List <Libro> getLibros(int pLimit)
{
List <Libro> lista = new List <Libro>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from libro order by Id asc limit @pLimit", _con);
comando.Parameters.AddWithValue("@pLimit", pLimit);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Libro item = new Libro(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetInt32(4),
_reader.GetInt32(5)
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static List <Cuota> getCuotasByIdMatricula(Int64 pIdMatricula, int pLimit)
{
List <Cuota> lista = new List <Cuota>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from cuota where IdMatricula=@pIdMatricula and Total <Precio order by Id asc limit @pLimit", _con);
comando.Parameters.AddWithValue("@pIdMatricula", pIdMatricula);
comando.Parameters.AddWithValue("@pLimit", pLimit);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Cuota item = new Cuota(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetDecimal(2),
_reader.GetDecimal(3),
_reader.GetInt64(4)
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static List <Detcurso> getDetscursoByIdCurso(Int64 pIdCurso)
{
List <Detcurso> lista = new List <Detcurso>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from detcurso where IdCurso=@pIdCurso and Estado='A' order by Id asc", _con);
comando.Parameters.AddWithValue("@pIdCurso", pIdCurso);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Detcurso item = new Detcurso(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetInt64(3),
_reader.GetInt64(4),
LibroDAL.getLibroById(_reader.GetInt64(3))
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static List <Dia> getDiasByIdCurso(Int64 pIdCurso)
{
List <Dia> lista = new List <Dia>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from dias where IdCurso= @pIdCurso order by Id desc", _con);
comando.Parameters.AddWithValue("@pIdCurso", pIdCurso);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Dia item = new Dia(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetInt64(4)
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static Useremp getUseremp(string pLogin, string pPass)
{
Useremp item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from useremp where Login=@pLogin and Pass=md5(@pPass)", _con);
cmdGetItemById.Parameters.AddWithValue("@pLogin", pLogin);
cmdGetItemById.Parameters.AddWithValue("@pPass", pPass);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Useremp(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetInt64(4),
ContratoDAL.getContratoById(_reader.GetInt64(4)),
AcsSucursalDAL.getAcsSucursalesByIdUser(_reader.GetInt64(0))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static List <Detmatricula> getDetsmatriculaByIdMatricula(Int64 pIdMatricula, int pLimit)
{
List <Detmatricula> lista = new List <Detmatricula>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from detmatricula where IdMatricula=@pIdMatricula order by Id asc limit @pLimit", _con);
comando.Parameters.AddWithValue("@pLimit", pLimit);
comando.Parameters.AddWithValue("@pIdMatricula", pIdMatricula);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Detmatricula item = new Detmatricula(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetInt64(2),
_reader.GetInt64(3),
EncargadoDAL.getEncargadoById(_reader.GetInt64(3))
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static bool InsertSucursalConf(Sucursal item)
{
bool result = true;
using (MySqlConnection _con = new Conexion().Conectar())
{
_con.Open();
MySqlTransaction _trans = _con.BeginTransaction();
try
{
MySqlCommand cmdInsertSucursal = new MySqlCommand("Insert into sucursal (Nombre,Direccion) values (@Nombre,@Direccion)", _con, _trans);
cmdInsertSucursal.Parameters.AddWithValue("@Nombre", item.Nombre);
cmdInsertSucursal.Parameters.AddWithValue("@Direccion", item.Direccion);
if (cmdInsertSucursal.ExecuteNonQuery() <= 0)
{
result = false;
}
if (result)
{
_trans.Commit();
}
else
{
_trans.Rollback();
}
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(result);
}
public static List <Persona> searchPersonaNoParametro(string pText, int pLimit)
{
List <Persona> lista = new List <Persona>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from persona where Upper(Nombre) like '%" + pText.ToUpper() + "%' order by Id asc", _con);
comando.Parameters.AddWithValue("@pLimit", pLimit);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Persona item = new Persona(
_reader.GetInt64("Id"),
_reader.GetString("Nombre"),
_reader.GetString("Dui"),
_reader.GetString("Nit"),
_reader.GetString("Direccion"),
(_reader.IsDBNull(4)) ? null : _reader.GetString("FechaNac"));
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public Userest getUserestById(int pId)
{
Userest item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from userest where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Userest(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetString(4),
_reader.GetInt64(5),
_reader.GetInt64(6),
EstudianteDAL.getEstudianteById(_reader.GetInt64(6))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static Cuota getCuotaByMonth(Int64 pIdMatricula, int pMonth, int pYear)
{
Cuota item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from cuota where IdMatricula=@pIdMatricula and month(FhRegistro)=@pMonth and year(FhRegistro)=@pYear ", _con);
cmdGetItemById.Parameters.AddWithValue("@pIdMatricula", pIdMatricula);
cmdGetItemById.Parameters.AddWithValue("@pMonth", pMonth);
cmdGetItemById.Parameters.AddWithValue("@pYear", pYear);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Cuota(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetDecimal(2),
_reader.GetDecimal(3),
_reader.GetInt64(4)
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static bool insertCurrentYear()
{
bool result = true;
using (MySqlConnection _con = new Conexion().Conectar())
{
_con.Open();
MySqlTransaction _trans = _con.BeginTransaction();
try
{
MySqlCommand cmdInsertYear = new MySqlCommand("Insert into year (Desde,Hasta) values (CONCAT(YEAR(now()),'-01-01'),CONCAT(YEAR(now()),'-12-31'))", _con, _trans);
if (cmdInsertYear.ExecuteNonQuery() <= 0)
{
result = false;
}
if (result)
{
_trans.Commit();
}
else
{
_trans.Rollback();
}
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(result);
}
public static List <Useremp> getUsersemp()
{
List <Useremp> lista = new List <Useremp>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from useremp", _con);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Useremp item = new Useremp(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetInt64(4),
ContratoDAL.getContratoById(_reader.GetInt64(4)),
AcsSucursalDAL.getAcsSucursalesByIdUser(_reader.GetInt64(0))
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}