public static List <Detfactura> getDetsfacturaByIdPersona(Int64 pIdPersona)
{
List <Detfactura> lista = new List <Detfactura>();
using (MySqlConnection _con = new Conexion().Conectar())
{
try
{
_con.Open();
MySqlCommand comando = new MySqlCommand("select df.* from detfactura as df inner join factura as f " +
"on f.Id=df.IdFactura inner join Producto as p on p.Id = df.IdProducto where f.IdPersona = @pIdPersona " +
" and df.Tipo = 'R' and (SELECT sum(dfs.Total) from detfactura as dfs inner join factura fs on fs.Id=dfs.IdFactura where dfs.RefNFactura=df.RefNFactura and fs.Estado='C')" +
" < p.Precio and f.Estado='C' order by Id desc", _con);
comando.Parameters.AddWithValue("@pIdPersona", pIdPersona);
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 <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 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);
}