iao_team/app/Http/Livewire/Reports.php

<?php

namespace App\Http\Livewire;

use Livewire\Component;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use App\Models\Receipt;
use App\Models\ReceiptRow;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Models\Course;
use App\Models\MemberCard;

class Reports extends Component
{
    public $type = 'anagrafica';
    public $yearFilter;

    public $courses = [];
    public $selectedCourse = null;
    protected $listeners = ['refreshData' => '$refresh'];

    public function mount()
    {
        if (Auth::user()->level != env('LEVEL_ADMIN', 0))
            return redirect()->to('/reports');

        if (isset($_GET["type"]))
            $this->type = $_GET["type"];

        $this->yearFilter = Carbon::now()->year;
        $this->courses = $this->getCoursesForSelect();
        $this->emit('dataUpdated');
    }

    public function render()
    {
        return view('livewire.reports');
    }
    public function updateCourseChart()
    {
        $this->emit('courseDataUpdated');
    }

    public function updatedSelectedCourse($value)
    {
        Log::info('Selected course changed to: ' . $value);
        $this->emit('courseDataUpdated', $value);
    }

    public function getTesseratiData()
    {
        $endYear = $this->yearFilter;
        return self::getMemberCountChartData($endYear);
    }

    public function change($type)
    {
        $this->type = $type;
    }

    public function setYearFilter($year)
    {
        $this->yearFilter = $year;
        $this->emit('dataUpdated');
    }

    public function getMonthlyTotals()
    {
        $year = $this->yearFilter;

        $months = range(1, 12);
        $monthNames = [];
        $incomeData = array_fill(0, 12, 0);
        $expenseData = array_fill(0, 12, 0);

        foreach ($months as $month) {
            $date = Carbon::createFromDate($year, $month, 1);
            $monthNames[] = ucfirst($date->locale('it')->monthName);
        }

        $incomeReceipts = DB::table('receipts')
            ->join('receipts_rows', 'receipts.id', '=', 'receipts_rows.receip_id')
            ->where('receipts.year', $year)
            ->where('receipts.type', 'IN')
            ->select(DB::raw('MONTH(receipts.date) as month_num'), DB::raw('SUM(receipts_rows.amount) as total'))
            ->groupBy('month_num')
            ->get();

        $expenseReceipts = DB::table('receipts')
            ->join('receipts_rows', 'receipts.id', '=', 'receipts_rows.receip_id')
            ->where('receipts.year', $year)
            ->where('receipts.type', 'OUT')
            ->select(DB::raw('MONTH(receipts.date) as month_num'), DB::raw('SUM(receipts_rows.amount) as total'))
            ->groupBy('month_num')
            ->get();

        foreach ($incomeReceipts as $receipt) {
            $index = $receipt->month_num - 1;
            if (isset($incomeData[$index])) {
                $incomeData[$index] = $receipt->total;
            }
        }

        foreach ($expenseReceipts as $receipt) {
            $index = $receipt->month_num - 1;
            if (isset($expenseData[$index])) {
                $expenseData[$index] = $receipt->total;
            }
        }


        return [
            'labels' => $monthNames,
            'datasets' => [
                [
                    'label' => 'Entrate',
                    'data' => $incomeData,
                    'backgroundColor' => 'rgba(54, 162, 235, 0.5)'
                ],
                [
                    'label' => 'Uscite',
                    'data' => $expenseData,
                    'backgroundColor' => 'rgba(255, 99, 132, 0.5)'
                ],
            ]
        ];
    }

    public function getYearlySummary()
    {
        $year = $this->yearFilter;

        $totalIncome = DB::table('receipts')
            ->join('receipts_rows', 'receipts.id', '=', 'receipts_rows.receip_id')
            ->where('receipts.year', $year)
            ->where('receipts.type', 'IN')
            ->sum('receipts_rows.amount');

        $totalExpenses = DB::table('receipts')
            ->join('receipts_rows', 'receipts.id', '=', 'receipts_rows.receip_id')
            ->where('receipts.year', $year)
            ->where('receipts.type', 'OUT')
            ->sum('receipts_rows.amount');

        $delta = $totalIncome - $totalExpenses;

        return [
            'totalIncome' => $totalIncome,
            'totalExpenses' => $totalExpenses,
            'delta' => $delta
        ];
    }

    public function getTopCausalsByAmount($limit = 10)
    {
        $year = $this->yearFilter;

        $query = DB::table('receipts_rows')
            ->join('receipts', 'receipts_rows.receip_id', '=', 'receipts.id')
            ->join('causals', 'receipts_rows.causal_id', '=', 'causals.id')
            ->where('receipts.year', $year);

        $query->where('receipts.type', 'IN');

        Log::info('Query: ' . $query->toSql());

        $causals = $query->select(
            'causals.id',
            'causals.name',
            'causals.parent_id',
            DB::raw('SUM(receipts_rows.amount) as total_amount')
        )
            ->groupBy('causals.id', 'causals.name', 'causals.parent_id')
            ->orderBy('total_amount', 'desc')
            ->limit($limit)
            ->get();

        Log::info('Causals: ' . json_encode($causals));

        $inData = [];

        foreach ($causals as $causal) {
            $tempCausal = new \App\Models\Causal();
            $tempCausal->id = $causal->id;
            $tempCausal->name = $causal->name;
            $tempCausal->parent_id = $causal->parent_id;

            $treeName = $tempCausal->getTree();

            $displayName = strlen($treeName) > 30 ? substr($treeName, 0, 27) . '...' : $treeName;

            $inData[] = [
                'label' => $displayName,
                'value' => $causal->total_amount,
                'fullName' => $treeName
            ];
        }

        usort($inData, function ($a, $b) {
            return $b['value'] <=> $a['value'];
        });

        $inData = array_slice($inData, 0, $limit);

        return [
            'inLabels' => array_column($inData, 'label'),
            'inData' => $inData,
            'datasets' => [
                [
                    'label' => 'Entrate per Causale',
                    'data' => array_column($inData, 'value'),
                ]
            ]
        ];
    }

    public function getCoursesForSelect()
    {
        $currentYear = date('Y');

        $courses = Course::with(['level', 'type', 'frequency'])
            ->where('active', true)
            ->where('year', 'like', '%' . $currentYear . '%')
            ->orderBy('name')
            ->get()
            ->map(function ($course) {
                $levelName = $course->level ? $course->level->name : 'No Level';
                $typeName = $course->type ? $course->type->name : 'No Type';
                $frequencyName = $course->frequency ? $course->frequency->name : 'No Frequency';
                $year = $course->year ?? '';

                return [
                    'id' => $course->id,
                    'name' => $course->name,
                    'full_name' => "{$course->name} - {$levelName} - {$typeName} - {$frequencyName} ({$year})",
                    'level_name' => $levelName,
                    'type_name' => $typeName,
                    'frequency_name' => $frequencyName,
                    'year' => $year
                ];
            })->toArray();

        return $courses;
    }


    public function getCourseMonthlyEarnings()
    {
        $courseId = $this->selectedCourse;
        Log::info('Getting earnings for course ID: ' . $courseId);

        if (empty($courseId)) {

            return [
                'labels' => ['Set', 'Ott', 'Nov', 'Dic', 'Gen', 'Feb', 'Mar', 'Apr', 'Mag', 'Giu', 'Lug', 'Ago'],
                'datasets' => [
                    [
                        'label' => 'Pagamenti Effettuati',
                        'backgroundColor' => 'rgba(0, 184, 148, 1)',
                        'data' => [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],
                        'type' => 'bar',
                        'order' => 3
                    ],
                    [
                        'label' => 'Pagamenti Totali',
                        'backgroundColor' => 'transparent',
                        'borderColor' => 'rgba(48, 51, 107, 1)',
                        'borderWidth' => 3,
                        'pointBackgroundColor' => 'rgba(48, 51, 107, 1)',
                        'pointRadius' => 5,
                        'data' => [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],
                        'type' => 'line',
                        'tension' => 0.2,
                        'order' => 2
                    ]
                ]
            ];
        }

        $monthOrder = [9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8];

        $monthlyData = [];
        foreach ($monthOrder as $i) {
            $monthlyData[$i] = [
                'earned' => 0,
                'total' => 0,
                'participants' => 0
            ];
        }

        $memberCourses = \App\Models\MemberCourse::where('course_id', $courseId)
            ->with('member')
            ->get();

        foreach ($memberCourses as $memberCourse) {
            $price = (float)($memberCourse->price ?? 0);

            if ($memberCourse->months) {
                $monthsData = json_decode($memberCourse->months, true);

                if (is_array($monthsData)) {
                    foreach ($monthsData as $monthData) {
                        $month = $monthData['m'] ?? null;
                        $status = $monthData['status'] ?? '';

                        if ($month !== null && isset($monthlyData[$month])) {
                            $monthlyData[$month]['total'] += $price;

                            if ($status === 1) {
                                $monthlyData[$month]['earned'] += $price;
                            }

                            $monthlyData[$month]['participants']++;
                        }
                    }
                }
            }
        }

        $monthNames = [
            9 => 'Set',
            10 => 'Ott',
            11 => 'Nov',
            12 => 'Dic',
            1 => 'Gen',
            2 => 'Feb',
            3 => 'Mar',
            4 => 'Apr',
            5 => 'Mag',
            6 => 'Giu',
            7 => 'Lug',
            8 => 'Ago',
        ];

        $labels = [];
        $earnedData = [];
        $totalData = [];
        $participantData = [];
        $missingData = [];

        foreach ($monthOrder as $month) {
            $labels[] = $monthNames[$month];
            $earnedData[] = round($monthlyData[$month]['earned'], 2);
            $totalData[] = round($monthlyData[$month]['total'], 2);
            $participantData[] = $monthlyData[$month]['participants'];
            $missingData[] = round($monthlyData[$month]['total'] - $monthlyData[$month]['earned'], 2);
        }

        return [
            'labels' => $labels,
            'datasets' => [
                [
                    'label' => 'Pagamenti Effettuati',
                    'backgroundColor' => 'rgba(0, 184, 148, 1)',
                    'data' => $earnedData,
                    'type' => 'bar',
                    'order' => 3
                ],
                [
                    'label' => 'Pagamenti Attesi',
                    'backgroundColor' => 'transparent',
                    'borderColor' => 'rgba(48, 51, 107, 1)',
                    'borderWidth' => 3,
                    'pointBackgroundColor' => 'rgba(48, 51, 107, 1)',
                    'pointRadius' => 5,
                    'data' => $totalData,
                    'type' => 'line',
                    'tension' => 0.2,
                    'order' => 2,
                    'participants' => $participantData,
                    'missing' => $missingData
                ]
            ]
        ];
    }

    public static function getMemberCountChartData($endYear = null, $span = 5)
    {
        if ($endYear === null) {
            $endYear = date('Y');
        }

        $startYear = $endYear - $span + 1;

        $memberCards = MemberCard::select('member_id', 'expire_date')
            ->whereNotNull('expire_date')
            ->whereNotNull('member_id')
            ->where('status', '!=', 'cancelled')
            ->whereRaw('YEAR(expire_date) >= ?', [$startYear])
            ->whereRaw('YEAR(expire_date) <= ?', [$endYear])
            ->get();

        $yearCounts = [];
        for ($year = $startYear; $year <= $endYear; $year++) {
            $yearPeriod = ($year - 1) . '-' . $year;
            $yearCounts[$yearPeriod] = [];
        }

        foreach ($memberCards as $card) {
            $expireYear = date('Y', strtotime($card->expire_date));

            $previousYear = $expireYear - 1;
            $yearPeriod = $previousYear . '-' . $expireYear;

            if (isset($yearCounts[$yearPeriod])) {
                $yearCounts[$yearPeriod][$card->member_id] = true;
            }
        }

        $yearLabels = [];
        $memberCountData = [];

        foreach ($yearCounts as $yearPeriod => $members) {
            $yearLabels[] = $yearPeriod;
            $memberCountData[] = count($members);
        }

        return [
            'labels' => $yearLabels,
            'datasets' => [
                [
                    'label' => 'Membri Tesserati',
                    'data' => $memberCountData,
                    'backgroundColor' => 'rgba(54, 162, 235, 0.2)',
                    'borderColor' => 'rgba(54, 162, 235, 1)',
                    'borderWidth' => 2,
                    'pointBackgroundColor' => 'rgba(54, 162, 235, 1)',
                    'pointRadius' => 4,
                    'tension' => 0.3,
                    'fill' => true
                ]
            ]
        ];
    }
}