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 Detfactura getDetfacturaById(Int64 pId)
{
Detfactura item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from detfactura where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
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))
);
}
_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 Matricula getMatriculaById(Int64 pId)
{
Matricula item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from matricula where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
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 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 <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 <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 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 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 <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 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 <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 <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 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 AcsSucursal getAcsSucursalById(Int64 pId)
{
AcsSucursal item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from acssucursal where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
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))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
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 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 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);
}
public static Empleado getEmpleadoById(Int64 pId)
{
Empleado item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from empleado where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
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 Evaluacion getEvaluacionById(Int64 pId)
{
Evaluacion item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from evaluacion where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
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))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static List <Calificacion> getCalificacionesByEvaluacion(Int64 pIdEvaluacion)
{
List <Calificacion> lista = new List <Calificacion>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from calificacion where IdEvaluacion =@pIdEvaluacion order by Id asc limit @pLimit", _con);
comando.Parameters.AddWithValue("@pIdEvaluacion", pIdEvaluacion);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Calificacion item = new Calificacion(
_reader.GetInt32(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetInt64(3),
_reader.GetInt64(4)
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static Egreso getEgresoById(Int64 pId)
{
Egreso item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from egreso where Id=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Egreso(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetString(3),
_reader.GetDecimal(4),
_reader.GetString(5),
_reader.GetInt64(6)
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static Cv getCvByIdEmpleado(Int64 pId)
{
Cv item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from cv where IdEmpleado=@pId", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Cv(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetInt32(3),
(byte[])_reader[4],
_reader.GetInt64(5)
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static Persona getPersonaById(Int64 pId)
{
Persona item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdBuscarPersona = new MySqlCommand("select * from persona where Id=@pId", _con);
cmdBuscarPersona.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdBuscarPersona.ExecuteReader();
while (_reader.Read())
{
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")
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
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 Year getCurrentYear()
{
Year item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
_con.Open();
try
{
MySqlCommand cmdGetItemById = new MySqlCommand("select * from year where YEAR(Desde)= YEAR(now())", _con);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Year(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2)
);
}
if (item == null)
{
insertCurrentYear();
getCurrentYear();
}
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static Detcurso getDetCursoById(Int64 pId)
{
Detcurso item = null;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand cmdGetItemById = new MySqlCommand("select * from detcurso where Id=@pId and Estado='A';", _con);
cmdGetItemById.Parameters.AddWithValue("@pId", pId);
MySqlDataReader _reader = cmdGetItemById.ExecuteReader();
while (_reader.Read())
{
item = new Detcurso(
_reader.GetInt64(0),
_reader.GetString(1),
_reader.GetString(2),
_reader.GetInt64(3),
_reader.GetInt64(4),
LibroDAL.getLibroById(_reader.GetInt64(3))
);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}
public static List <Year> getYears(int pLimit)
{
List <Year> lista = new List <Year>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select * from year order by Id desc limit @pLimit", _con);
comando.Parameters.AddWithValue("@pLimit", pLimit);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
Year item = new Year(
_reader.GetInt64(0),
_reader.GetDateTime(1).ToString("yyyy"),
_reader.GetString(2)
);
lista.Add(item);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(lista);
}
public static decimal getTotalFacturasNoParametro(string pText, Int64 pIdSucursal, int pLimit)
{
decimal item = 0;
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select sum(df.Total)-SUM(df.Descuento) from producto as prod right join detfactura as df on df.IdProducto=prod.Id " +
" right join factura as f on f.Id=df.Id right join persona as per on per.Id=f.IdPersona where (per.Nombre like '%" + "" + "%' or " +
"prod.Nombre like '%" + "" + "%' or df.Concepto like '%" + "" + "%' or df.Total like '%" + "" + "%' or " +
"f.Total like '%" + "" + "%' or f.Observacion or f.NFactura like '%" + "" + "%'" +
") and f.IdSucursal=@pIdSucursal and f.Estado='C' order by f.Id asc", _con);
comando.Parameters.AddWithValue("@pIdSucursal", pIdSucursal);
comando.Parameters.AddWithValue("@pLimit", pLimit);
MySqlDataReader _reader = comando.ExecuteReader();
while (_reader.Read())
{
item = _reader.IsDBNull(0) ? 0 : _reader.GetDecimal(0);
}
_reader.Close();
}
catch (Exception ex)
{
_con.Close();
throw ex;
}
finally
{
_con.Close();
}
}
return(item);
}