CODEWITHSUNDEEP
phpmysqllaraveleloquent
Prince
Prince

Reputation: 39

how to get order item sales report in laravel?

I'm trying to get last month order items report. Here is my table structure

orders
    id - integer
    amount- double

order_items (pivot table)
    id - integer
    order_id - foreign
    item_id - foreign
    quantity
    price

Here is my Item model

public function orders()
{
    return $this->belongsToMany(Order::class, 'order_items', 'item_id',  'order_id')
                ->withPivot('quantity', 'price');
}

Here is my Order model

public function items()
{
    return $this->belongsToMany(Item::class, 'order_items', 'order_id', 'item_id')
                ->withPivot('quantity', 'price')
                ->withTimestamps();
}

Here is my controller where I got last month's all orders

$orders = Order::with('items')
               ->whereMonth('created_at', '=', Carbon::now()->subMonth()->month)
               ->get();

After foreach loop in blade,

@foreach ($orders as $order)
  <ul>
    @foreach($order->items as $item)
      <li>
        {{ $item->name }}, {{ $item->pivot->quantity }}, {{ $item->pivot->price }}
      </li>
    @endforeach
  </ul>
  <hr>
@endforeach

I'm getting data like this

Item Name  Quantity Price
Item A         20    600
Item A         15    400
Item A          5    200
Item B          5    100
Item B          5    100

But I don't want to show same item in this row, I want to show like this

Item Name  Quantity Price
Item A         40    1200
Item B         10    200

How to sum quantity and price if item is same?

Upvotes: 0

Views: 809

Answers (1)

Rateb Habbab
Rateb Habbab

Reputation: 1789

I think this could solve your problem:

$orders = Order::with('items')
            ->whereMonth('created_at', '=', Carbon::now()->subMonth()->month)
            ->get();

$orders->transform(function($order){
    $itemsArray = [];
    $order['items'] = $order['items']->groupBy('name')->map(function ($item) use ($itemsArray){
        array_push($itemsArray, [
            'name' => $item[0]['name'], 
            'qty' => $item->sum('qty'),
            'price' => $item->sum('price')
        ]);
        return $itemsArray[0];
    })->values();
    return $order;
});

Upvotes: 2

Related Questions