How to read sum of these rows in a different tables

I want to read Sum of Buying Price and sum of Selling Price as costOfGoodsSold and Revenue in my database. I have four Models which are connected 1.SoldInventory

class SoldInventory extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['customer_id','store_id','date_of_sale','tracking_no','reference','description','user_id'];

    public function soldInventoryItems()
    {
        return $this->hasMany(SoldInventoryItems::class);
    }

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function salesInvoice()
    {
        return $this->hasOne(SalesInvoice::class);
    }

    public function store()
    {
        return $this->belongsTo(Store::class);
    }
}
2. SoldInventoryItem
class SoldInventoryItems extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['inventory_in_store_id','quantity','unit_price','sub_total'];

//    public function inventoryInSale()
//    {
//        return $this->belongsTo(InventoryInSale::class);
//    }

    public function soldInventory()
    {
        return $this->belongsTo(SoldInventory::class);
    }

    public function inventoryInStore() : BelongsTo
    {
        return $this->belongsTo(InventoryInStore::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}
3.InventoryInStore
class InventoryInStore extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['inventory_item_id','quantity','store_id'];

    public function store()
    {
        return $this->belongsTo(Store::class);
    }

    public function inventoryItem()
    {
        return $this->belongsTo(InventoryItem::class);
    }

    public function takenInventories()
    {
        return $this->hasMany(TakenInventory::class);
    }

    public function usedInventories()
    {
        return $this->hasMany(UsedInventory::class);
    }

    public function broughtInventories()
    {
        return $this->hasMany(BroughtInventory::class);
    }

//    public function inventoryInSale()
//    {
//        return $this->hasOne(InventoryInSale::class);
//    }

    public function soldInventoryItems()
    {
        return $this->hasMany(SoldInventoryItems::class);
    }
}
4.Store
class Store extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    public function users()
    {
        return $this->belongsToMany(User::class);
    }

    public function inventoryInStores()
    {
        return $this->hasMany(InventoryInStore::class);
    }

    public function soldInventories() :HasMany
    {
        return $this->hasMany(SoldInventory::class);
    }

}

So I want to read 1.sum of ( Buying_price from InventoryInStore * Quantity from soldInventoryItems) as costOfGoodsSold 2. sum of ( Selling_price from InventoryInStore * Quantity from soldInventoryItems) as Revenue

and I want to calculate Profit and loss by subtracting Revenue - CostOf Goods sold and name as Net Income

start from (date_of_sale ) to (date_of_sale ) by selecting date of sale from specific period in soldInventory date_of_sale row.

So I want to be able to select specific store if I want from soldInventory store.

for example I can say store X in soldInventory calculate Net Income from (date_of_sale Y to date_of_sale Z).

I am using laravel livewire to do this and also I want to be able to download this as PDF I am using DomPDF. Thanks

LaryAI

9 months ago

Best Answer

Level 58

Certainly! Here’s how you can approach this in Laravel, using Eloquent relationships and query builder. I’ll break it down step by step:

1. Database Structure Assumptions

InventoryInStore has buying_price and selling_price columns.
SoldInventory has date_of_sale and store_id.
SoldInventoryItems has quantity and links to inventory_in_store_id.

If your table/column names differ, adjust accordingly.

2. Querying the Data

You want to:

Filter SoldInventory by date range and store.
For each SoldInventory, sum:
- Cost of Goods Sold: buying_price * quantity
- Revenue: selling_price * quantity
Calculate Net Income: Revenue - Cost of Goods Sold.

Here’s how you can do it efficiently:

use App\Models\SoldInventory;
use Illuminate\Support\Facades\DB;

function getStoreReport($storeId, $startDate, $endDate)
{
    $result = DB::table('sold_inventories')
        ->join('sold_inventory_items', 'sold_inventories.id', '=', 'sold_inventory_items.sold_inventory_id')
        ->join('inventory_in_stores', 'sold_inventory_items.inventory_in_store_id', '=', 'inventory_in_stores.id')
        ->where('sold_inventories.store_id', $storeId)
        ->whereBetween('sold_inventories.date_of_sale', [$startDate, $endDate])
        ->selectRaw('
            SUM(inventory_in_stores.buying_price * sold_inventory_items.quantity) as cost_of_goods_sold,
            SUM(inventory_in_stores.selling_price * sold_inventory_items.quantity) as revenue
        ')
        ->first();

    $costOfGoodsSold = $result->cost_of_goods_sold ?? 0;
    $revenue = $result->revenue ?? 0;
    $netIncome = $revenue - $costOfGoodsSold;

    return [
        'costOfGoodsSold' => $costOfGoodsSold,
        'revenue' => $revenue,
        'netIncome' => $netIncome,
    ];
}

3. Using in Livewire

In your Livewire component:

public $storeId;
public $startDate;
public $endDate;
public $report = [];

public function generateReport()
{
    $this->report = getStoreReport($this->storeId, $this->startDate, $this->endDate);
}

4. Exporting as PDF (DomPDF)

You can use a Blade view for the report and generate a PDF:

use Barryvdh\DomPDF\Facade\Pdf;

public function downloadReport()
{
    $data = getStoreReport($this->storeId, $this->startDate, $this->endDate);

    $pdf = Pdf::loadView('reports.store_report', compact('data'));
    return $pdf->download('store_report.pdf');
}

5. Blade Example (`resources/views/reports/store_report.blade.php`)

<table>
    <tr>
        <th>Cost of Goods Sold</th>
        <td>{{ number_format($data['costOfGoodsSold'], 2) }}</td>
    </tr>
    <tr>
        <th>Revenue</th>
        <td>{{ number_format($data['revenue'], 2) }}</td>
    </tr>
    <tr>
        <th>Net Income</th>
        <td>{{ number_format($data['netIncome'], 2) }}</td>
    </tr>
</table>

6. Summary

Use a query with joins and SUM() to get your totals.
Filter by store and date range.
Use Livewire for interactivity.
Use DomPDF to export the report.

If you need to adjust for different column names or relationships, just update the query accordingly.

Let me know if you need help integrating this into your Livewire component or customizing the PDF output!

yonka

9 months ago

Level 2

Perfect! But there is little problem, when I choose storeId it will work fine, but what about if I want to get all stores not specific store

Please or to participate in this conversation.