bp/app/Modules/CRM/Models/DealModel.php

<?php

namespace App\Modules\CRM\Models;

use CodeIgniter\Model;

class DealModel extends Model
{
    protected $table            = 'deals';
    protected $primaryKey       = 'id';
    protected $useSoftDeletes   = true;
    protected $useTimestamps    = true;
    protected $createdField     = 'created_at';
    protected $updatedField     = 'updated_at';
    protected $deletedField     = 'deleted_at';
    protected $returnType       = 'array';
    protected $allowedFields    = [
        'organization_id',
        'contact_id',
        'company_id',
        'title',
        'description',
        'amount',
        'currency',
        'stage_id',
        'assigned_user_id',
        'expected_close_date',
        'created_by',
    ];

    /**
     * Получить сделки с JOIN-ами для таблицы
     */
    public function getForTable(int $organizationId): array
    {
        return $this->select('
            deals.id,
            deals.title,
            deals.amount,
            deals.currency,
            deals.expected_close_date,
            deals.created_at,
            deals.deleted_at,
            ds.name as stage_name,
            ds.color as stage_color,
            ds.type as stage_type,
            ds.probability as stage_probability,
            c.name as contact_name,
            c.email as contact_email,
            oc.name as client_name,
            au.name as assigned_user_name,
            au.email as assigned_user_email,
            cb.name as created_by_name
        ')
            ->join('deal_stages ds', 'deals.stage_id = ds.id', 'left')
            ->join('contacts c', 'deals.contact_id = c.id', 'left')
            ->join('organizations_clients oc', 'deals.company_id = oc.id', 'left')
            ->join('users au', 'deals.assigned_user_id = au.id', 'left')
            ->join('users cb', 'deals.created_by = cb.id', 'left')
            ->where('deals.organization_id', $organizationId)
            ->orderBy('deals.created_at', 'DESC')
            ->findAll();
    }

    /**
     * Получить сделки организации с фильтрами
     */
    public function getDealsByOrganization(
        int $organizationId,
        ?int $stageId = null,
        ?int $assignedUserId = null,
        ?string $search = null,
        ?string $dateFrom = null,
        ?string $dateTo = null
    ): array {
        $builder = $this->where('organization_id', $organizationId);

        if ($stageId) {
            $builder->where('stage_id', $stageId);
        }

        if ($assignedUserId) {
            $builder->where('assigned_user_id', $assignedUserId);
        }

        if ($search) {
            $builder->groupStart()
                ->like('title', $search)
                ->orLike('description', $search)
                ->groupEnd();
        }

        if ($dateFrom) {
            $builder->where('expected_close_date >=', $dateFrom);
        }

        if ($dateTo) {
            $builder->where('expected_close_date <=', $dateTo);
        }

        return $builder->orderBy('created_at', 'DESC')->findAll();
    }

    /**
     * Получить сделки, сгруппированные по этапам (для Канбана)
     */
    public function getDealsGroupedByStage(int $organizationId): array
    {
        $deals = $this->select('deals.*, ds.name as stage_name, ds.color as stage_color, ds.type as stage_type, au.name as assigned_user_name')
            ->join('deal_stages ds', 'deals.stage_id = ds.id', 'left')
            ->join('users au', 'deals.assigned_user_id = au.id', 'left')
            ->join('contacts c', 'deals.contact_id = c.id', 'left')
            ->join('organizations_clients oc', 'deals.company_id = oc.id', 'left')
            ->where('deals.organization_id', $organizationId)
            ->where('deals.deleted_at', null)
            ->orderBy('ds.order_index', 'ASC')
            ->orderBy('deals.created_at', 'DESC')
            ->findAll();

        $grouped = [];
        foreach ($deals as $deal) {
            $stageId = $deal['stage_id'] ?? 0;
            if (!isset($grouped[$stageId])) {
                $grouped[$stageId] = [
                    'stage_name'    => $deal['stage_name'] ?? 'Без этапа',
                    'stage_color'   => $deal['stage_color'] ?? '#6B7280',
                    'stage_type'    => $deal['stage_type'] ?? 'progress',
                    'deals'         => [],
                    'total_amount'  => 0,
                ];
            }
            $grouped[$stageId]['deals'][] = $deal;
            $grouped[$stageId]['total_amount'] += (float) $deal['amount'];
        }

        return $grouped;
    }

    /**
     * Получить сделки для календаря (по дате закрытия)
     */
    public function getDealsForCalendar(int $organizationId, string $month): array
    {
        return $this->select('deals.*, ds.color as stage_color, ds.name as stage_name')
            ->join('deal_stages ds', 'deals.stage_id = ds.id', 'left')
            ->where('deals.organization_id', $organizationId)
            ->where('deals.deleted_at', null)
            ->where('deals.expected_close_date >=', date('Y-m-01', strtotime($month)))
            ->where('deals.expected_close_date <=', date('Y-m-t', strtotime($month)))
            ->orderBy('expected_close_date', 'ASC')
            ->findAll();
    }

    /**
     * Получить статистику по сделкам
     */
    public function getDealStats(int $organizationId): array
    {
        $openDeals = $this->select('COUNT(*) as count, COALESCE(SUM(amount), 0) as total')
            ->where('organization_id', $organizationId)
            ->where('deleted_at', null)
            ->whereIn('stage_id', function($builder) use ($organizationId) {
                return $builder->select('id')
                    ->from('deal_stages')
                    ->where('organization_id', $organizationId)
                    ->whereIn('type', ['progress']);
            })
            ->get()
            ->getRow();

        $wonDeals = $this->select('COUNT(*) as count, COALESCE(SUM(amount), 0) as total')
            ->where('organization_id', $organizationId)
            ->where('deleted_at', null)
            ->whereIn('stage_id', function($builder) use ($organizationId) {
                return $builder->select('id')
                    ->from('deal_stages')
                    ->where('organization_id', $organizationId)
                    ->where('type', 'won');
            })
            ->get()
            ->getRow();

        $lostDeals = $this->select('COUNT(*) as count')
            ->where('organization_id', $organizationId)
            ->where('deleted_at', null)
            ->whereIn('stage_id', function($builder) use ($organizationId) {
                return $builder->select('id')
                    ->from('deal_stages')
                    ->where('organization_id', $organizationId)
                    ->where('type', 'lost');
            })
            ->get()
            ->getRow();

        return [
            'open_count' => $openDeals->count,
            'open_total' => $openDeals->total,
            'won_count'  => $wonDeals->count,
            'won_total'  => $wonDeals->total,
            'lost_count' => $lostDeals->count,
        ];
    }

    /**
     * Получить сделку по ID с JOIN-ами
     */
    public function getWithJoins(int $dealId, int $organizationId): ?array
    {
        return $this->select('
            deals.*,
            ds.name as stage_name,
            ds.color as stage_color,
            ds.type as stage_type,
            ds.probability as stage_probability,
            c.name as contact_name,
            c.email as contact_email,
            oc.name as client_name,
            au.name as assigned_user_name,
            au.email as assigned_user_email
        ')
            ->join('deal_stages ds', 'deals.stage_id = ds.id', 'left')
            ->join('contacts c', 'deals.contact_id = c.id', 'left')
            ->join('organizations_clients oc', 'deals.company_id = oc.id', 'left')
            ->join('users au', 'deals.assigned_user_id = au.id', 'left')
            ->where('deals.id', $dealId)
            ->where('deals.organization_id', $organizationId)
            ->first();
    }
}