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