Server : Apache System : Linux server2.corals.io 4.18.0-348.2.1.el8_5.x86_64 #1 SMP Mon Nov 15 09:17:08 EST 2021 x86_64 User : corals ( 1002) PHP Version : 7.4.33 Disable Function : exec,passthru,shell_exec,system Directory :  /home/corals/ts.corals.io/corals-api/Corals/modules/Timesheet/Reports/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php

namespace Corals\Modules\Timesheet\Reports;

use Corals\Foundation\Models\BaseModel;
use Corals\Modules\Timesheet\Models\Expense;
use Corals\Modules\Timesheet\Models\Income;
use Corals\Modules\Timesheet\Models\Invoice;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class ExpensesAndIncomesReport
{
    protected $year = null;
    protected $status = null;
    protected $report = null;

    protected $colors = ['incomes' => '#28a745', 'expenses' => '#dc3545'];

    public function getExpensesAndIncomesReportData(Request $request, $report): array
    {
        $this->year = $request->year;
        $this->status = $request->status;
        $this->report = $report;

        switch ($report) {
            case 'expenses':
            case 'incomes':
                $targetModelClass = $report == 'expenses' ? Expense::class : Income::class;

                $allCategoriesData = $this->getTableData($targetModelClass);

                if ($report == 'incomes') {
                    $invoicesRow = $this->getInvoiceData();
                    $allCategoriesData[] = $invoicesRow;
                }

                $totalRow = $this->calculateTotalRow($allCategoriesData);
                $totalRow['_rowVariant'] = 'primary';
                $allCategoriesData[] = $totalRow;
                $chartData = $this->getChartData($totalRow, $report);
                break;
            case 'expenses-vs-incomes':
                $expense_table_data = $this->getTableData(Expense::class, 'danger');
                $expense_total = $this->calculateTotalRow($expense_table_data);
                $expense_chartData = $this->getChartData($expense_total, $report, true);

                $income_table_data = $this->getTableData(Income::class, 'success');
                $income_table_data[] = array_merge($this->getInvoiceData(), ['_rowVariant' => 'success']);

                $income_total = $this->calculateTotalRow($income_table_data);
                $income_chartData = $this->getChartData($income_total, $report, true);

                $expense_income_table_data = array_merge($expense_table_data, $income_table_data);

                $totalRow = [];
                $totalRow['Category'] = 'Total (USD)';

                foreach ($this->getMonthsStructure() as $monthData) {
                    $monthTotal = $income_total[$monthData['label']] - $expense_total[$monthData['label']];
                    $totalRow[$monthData['label']] = $monthTotal;
                }

                $totalRow['_rowVariant'] = 'primary';

                $expense_income_table_data[] = $totalRow;

                $allCategoriesData = $expense_income_table_data;

                $chartData = [];

                $chartData['labels'] = array_column($this->getMonthsStructure(), 'label');
                $chartData['datasets'] = [
                    [
                        'label' => 'Expenses',
                        'fill' => false,
                        'backgroundColor' => '#808080',
                        'borderColor' => $this->colors['expenses'],
                        'data' => $expense_chartData
                    ],
                    [
                        'label' => 'incomes',
                        'fill' => false,
                        'backgroundColor' => '#808080',
                        'borderColor' => $this->colors['incomes'],
                        'data' => $income_chartData
                    ]
                ];
                break;
        }

        return [
            'table_data' => $allCategoriesData ?? [],
            'chart_data' => $chartData ?? []
        ];
    }

    /**
     * @param $targetModelClass
     * @param $dateColumn
     * @return array
     */
    public function getTableData($targetModelClass, $rowVariant = null)
    {
        /**
         * @var BaseModel $model
         */
        $model = (new $targetModelClass);

        $targetTable = $model->getTable();
        $dateColumn = strtolower(class_basename($targetModelClass)) . '_date';

        $status = $this->status;

        $result = $model->query()
            ->whereYear($dateColumn, $this->year)
            ->join('utility_categories', 'utility_categories.id', $targetTable . '.category_id')
            ->when($status != 'all', function (Builder $builder) use ($targetTable, $status) {
                $builder->where($targetTable . '.status', '=', $status);
            })
            ->select(
                DB::raw('SUM(amount) as `amount`'),
                "category_id",
                "utility_categories.name as category_name",
                DB::raw('MONTH(' . $dateColumn . ') month')
            )->groupby('category_id', 'month')
            ->get()
            //collection groupBy
            ->groupBy('category_name');

        $allCategoriesData = [];

        foreach ($result as $categoryName => $months) {
            $categoryData = [];

            $categoryData['Category'] = $categoryName;

            $categoryMonths = collect($months)->pluck('amount', 'month')->toArray();

            foreach ($this->getMonthsStructure() as $monthNumber => $monthDetails) {
                $categoryData[$monthDetails['label']] = $categoryMonths[$monthNumber] ?? 0;
            }

            if ($rowVariant) {
                $categoryData['_rowVariant'] = $rowVariant;
            }

            $allCategoriesData[] = $categoryData;
        }
        return $allCategoriesData;
    }

    public function calculateTotalRow($allCategoriesData): array
    {
        $totalRow = [];
        $totalRow['Category'] = 'Total (USD)';

        foreach ($this->getMonthsStructure() as $monthData) {
            $sum = array_sum(array_column($allCategoriesData, $monthData['label']));
            $totalRow[$monthData['label']] = $sum;
        }

        return $totalRow;
    }

    public function getChartData($totalRow, $reportName, $onlyData = false): array
    {
        $chartData = [];
        $chartData['labels'] = [];
        $data = [];

        foreach ($this->getMonthsStructure() as $monthData) {
            $chartData['labels'][] = $monthData['label'];
            $data[] = $totalRow[$monthData['label']];
        }

        if ($onlyData) {
            return $data;
        }

        $chartData['datasets'] = [[
            'label' => Str::title($reportName),
            'fill' => false,
            'backgroundColor' => '#808080',
            'borderColor' => $this->colors[$this->report],
            'data' => $data
        ]];

        return $chartData;
    }

    public function getInvoiceData()
    {
        $status = $this->status;

        $invoiceMonths = Invoice::query()
            ->whereYear('invoice_date', $this->year)
            ->when($status != 'all', function (Builder $builder) use ($status) {
                $builder->where('status', '=', $status);
            })
            ->select(
                DB::raw('SUM(total) as `total`'),
                DB::raw('MONTH(invoice_date) month')
            )->groupby('month')
            ->pluck('total', 'month')
            ->toArray();

        $invoicesRow = [];
        $invoicesRow['Category'] = 'Invoices';

        foreach ($this->getMonthsStructure() as $monthNumber => $monthDetails) {
            $invoicesRow[$monthDetails['label']] = $invoiceMonths[$monthNumber] ?? 0;
        }

        return $invoicesRow;

    }

    public function getMonthsStructure()
    {
        return [
            '1' => ['label' => 'January'],
            '2' => ['label' => 'February'],
            '3' => ['label' => 'March'],
            '4' => ['label' => 'April'],
            '5' => ['label' => 'May'],
            '6' => ['label' => 'June'],
            '7' => ['label' => 'July'],
            '8' => ['label' => 'August'],
            '9' => ['label' => 'September'],
            '10' => ['label' => 'October'],
            '11' => ['label' => 'November'],
            '12' => ['label' => 'December'],
        ];
    }
}