finansePRO/app/Models/InstrumentModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class InstrumentModel extends Model
{
    protected $table         = 'inv_instruments';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $useTimestamps = true;
    protected $allowedFields = ['name'];

    protected $validationRules = [
        'name' => 'required|max_length[120]',
    ];

    /**
     * Czy instrument ma powiazane operacje lub wyceny (blokada usuniecia).
     */
    public function hasChildren(int $id): bool
    {
        $ops = (int) $this->db->table('inv_operations')->where('instrument_id', $id)->countAllResults();
        $val = (int) $this->db->table('inv_valuations')->where('instrument_id', $id)->countAllResults();

        return ($ops + $val) > 0;
    }

    /**
     * Ostatnia wycena per instrument: [instrument_id => value].
     * Ostatnia = najpozniejsza data, przy remisie najwyzsze id.
     */
    private function latestValuations(): array
    {
        $rows = $this->db->table('inv_valuations')
            ->select('instrument_id, value')
            ->orderBy('instrument_id', 'ASC')->orderBy('date', 'ASC')->orderBy('id', 'ASC')
            ->get()->getResultArray();

        $latest = [];
        foreach ($rows as $r) {
            $latest[(int) $r['instrument_id']] = (float) $r['value']; // ostatni wygrywa (posortowane rosnaco)
        }

        return $latest;
    }

    /**
     * Sumy wplat/wyplat per instrument: [instrument_id => ['deposit'=>..,'withdraw'=>..]].
     */
    private function operationSums(): array
    {
        $rows = $this->db->table('inv_operations')
            ->select('instrument_id')
            ->select("SUM(CASE WHEN type='deposit'  THEN amount ELSE 0 END) AS deposit")
            ->select("SUM(CASE WHEN type='withdraw' THEN amount ELSE 0 END) AS withdraw")
            ->groupBy('instrument_id')
            ->get()->getResultArray();

        $sums = [];
        foreach ($rows as $r) {
            $sums[(int) $r['instrument_id']] = [
                'deposit'  => (float) $r['deposit'],
                'withdraw' => (float) $r['withdraw'],
            ];
        }

        return $sums;
    }

    /**
     * Instrumenty ze statystykami:
     * wplacone_netto = Sumwplat - Sumwyplat; wartosc = ostatnia wycena;
     * zarobek = wartosc - wplacone_netto; procent = zarobek / wplacone_netto * 100 (gdy netto > 0).
     */
    public function withStats(): array
    {
        $instruments = $this->orderBy('name', 'ASC')->findAll();
        $latest      = $this->latestValuations();
        $sums        = $this->operationSums();

        foreach ($instruments as &$ins) {
            $id       = (int) $ins['id'];
            $deposit  = $sums[$id]['deposit']  ?? 0.0;
            $withdraw = $sums[$id]['withdraw'] ?? 0.0;
            $net      = $deposit - $withdraw;
            $value    = $latest[$id] ?? 0.0;
            $profit   = $value - $net;

            $ins['deposit']      = round($deposit, 2);
            $ins['withdraw']     = round($withdraw, 2);
            $ins['net_invested'] = round($net, 2);
            $ins['value']        = round($value, 2);
            $ins['has_value']    = array_key_exists($id, $latest);
            $ins['profit']       = round($profit, 2);
            $ins['percent']      = $net > 0 ? round($profit / $net * 100, 2) : null;
        }

        return $instruments;
    }

    /**
     * Szereg czasowy calego portfela do wykresu.
     * Dla kazdej daty wyceny: wartosc = suma ostatnich wycen (data <= d) po instrumentach;
     * wplacone_netto = Sum(deposit - withdraw) o dacie <= d; zarobek = wartosc - wplacone_netto.
     * Zwraca [ ['date'=>.., 'net'=>.., 'value'=>.., 'profit'=>..], ... ].
     */
    public function portfolioTimeline(): array
    {
        $dates = $this->db->table('inv_valuations')
            ->select('date')->distinct()->orderBy('date', 'ASC')
            ->get()->getResultArray();
        if (empty($dates)) {
            return [];
        }

        $valuations = $this->db->table('inv_valuations')
            ->select('instrument_id, date, value')
            ->orderBy('date', 'ASC')->orderBy('id', 'ASC')
            ->get()->getResultArray();

        $operations = $this->db->table('inv_operations')
            ->select('date, amount, type')
            ->get()->getResultArray();

        $out = [];
        foreach ($dates as $d) {
            $day = $d['date'];

            // Ostatnia wycena per instrument o dacie <= day
            $latest = [];
            foreach ($valuations as $v) {
                if ($v['date'] <= $day) {
                    $latest[(int) $v['instrument_id']] = (float) $v['value'];
                }
            }
            $value = array_sum($latest);

            // Wplacone netto o dacie <= day
            $net = 0.0;
            foreach ($operations as $o) {
                if ($o['date'] <= $day) {
                    $net += $o['type'] === 'deposit' ? (float) $o['amount'] : -(float) $o['amount'];
                }
            }

            $out[] = [
                'date'   => $day,
                'net'    => round($net, 2),
                'value'  => round($value, 2),
                'profit' => round($value - $net, 2),
            ];
        }

        return $out;
    }
}