I have this Warehouse collection I got from the database

[
    {
        "id": 1,
        "warehouse": "India"
        "sales": [
            {
                "id": 1,
                "warehouse_id": 1,
                "price": "120.00",
                "quantity": 1000,
                "status": 1
            },
            {
                "id": 2,
                "warehouse_id": 1,
                "price": "20.00",
                "quantity": 100,
                "status": 1
            },
            {
                "id": 3,
                "warehouse_id": 1,
                "price": "40.00",
                "quantity": 1000,
                "status": 2
            }
        ]
    },
    {
        "id": 2,
        "warehouse": "Malaysia"
        "sales": [
            {
                "id": 4,
                "warehouse_id": 2,
                "price": "160.00",
                "quantity": 100,
                "status": 1
            }
        ]
    }
]

I want to calculate the total income for each warehouse

Total income is calculated based on the sale status attribute

If status = 1, the products are delivered so it should add price * quantity to the total income

If status = 2, the products are returned so it should subtract price * quantity from the total income

A basic example for India warehouse:

total_income = 120*1000   20*100 - 40*1000

And for Malaysia:

total_income = 160*100

I tried using Warehouse::withSum(); but it didn't get me anywhere. I'm wondering if there's a good way to do with collections

CodePudding user response：

I honestly would go the route below:

Obviously my solution would have to be modified slightly if you aren't using completely whole numbers (as in your example). The if/else can be broadened out as well if you have more then the 2 statuses.

$total_income = 0;

foreach($warehouses as $warehouse)
{
    foreach($warehouse->sales as $sale)
    {
        if($sale->status = 1)
        {
            $total_income  = ($sale->price * $sale->quantity);
        }else{
            $total_income -= ($sale->price * $sale->quantity); 
        }
    }
}

This is a crude example of what how I would do it. It seems that each 'warehouse' has a different location ex. India vs. Malaysia. My example is more about the grand total, but you could always save the results of each different warehouse in different variables, or as a key/value pair in an array (which is how I would go).

CodePudding user response：

You could just pass a few callbacks to the collection's sum() method:

$warehouses_collection->sum(function ($warehouse) {
    return collect($warehouse->sales)->sum(function ($sale) {
            ((int) $sale->price) * $sale->quantity * ($sale->status == 1 ? 1 : -1)
        });
});

WithSum is a bit tricky to use here but calling withAggregate works.

Warehouse::withAggregate(
    'sales as total_income',
    'sum(case when status = 1 then convert(int, price) * quantity when status = 2 then convert(int, price) * quantity * -1 else 0 end)'
)->get()