Sum of the multiplication of the quantity by value of the product in daily sales

1

I have a page of orders, we will take as base a mercantile, where the person buys 3 soft drinks each with the value of $ 6.00 and 2 cookies in the amount of $ 2.00 each.

I first need to multiply these values (3 * 6) and (2 * 2) and then add their results, which would give a total purchase value of $ 22.00 as well as bring the name of the customer who made the purchase.

I'm using Laravel's Eloquent, but I've been finding some difficulties in crafting the query, if you can help me, follow the code and the image of the relationship

$vendas = Venda::where('data', date('Y-d-m'))
        ->join('clientes', 'vendas.cliente_id', '=', 'clientes.id')
        ->join('produtos_venda', 'vendas.id', '=', 'produtos_venda.venda_id')
        ->select('vendas.*', 'clientes.nome as nome_cliente', 'produtos_venda.quantidade', 'produtos_venda.valor')
        ->sum('produtos_venda.valor * produtos_venda.quantidade as valor_total')->get();

    
asked by anonymous 07.10.2017 / 19:11

1 answer

2

The correct way would be:

$select = "sum(produtos_venda.valor*produtos_venda.quantidade) as total,";
$select .= "vendas.cliente_id, clientes.nome, vendas.data";

$vendas = Venda::where('data', date('Y-d-m'))
        ->join('clientes', 'vendas.cliente_id', '=', 'clientes.id')
        ->join('produtos_venda', 'vendas.id', '=', 'produtos_venda.venda_id')
        ->groupBy('vendas.cliente_id', 'clientes.nome', 'vendas.data')
        ->select(\DB::raw($select))
        ->get();

  • In the product table, I change the field type preco to decimal(10,2)
  • In the Sales table, I change the field type data to date
  • In the sales_products table, change the type of the value field to decimal(10,2)
  • If you find that decimal(10,2) is insufficient increase the number 10 to 12

These observations can and should be followed if they have the corresponding type because they use a type that can disrupt even operations of addition, multiplication, etc. and filters with dates, em>

07.10.2017 / 23:31