BudgetRepository

<?php

namespace Implements\Expense;

use App\Models\Expense\Budget;
use App\Models\Expense\Order;
use App\Models\User;
use Yajra\Datatables\Datatables;
use Illuminate\Support\Facades\Gate;
use League\Csv\Reader;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Models\Expense\MonthlyBudget;
use Illuminate\Contracts\Database\Eloquent\Builder;

class BudgetRepository
{
    public function datatable($year, $ccBy)
    {
        if($ccBy == 'all') {
            $budgets = Budget::with(['cc:id,cc', 'category', 'picExp', 'orders'])->where('year', $year)->get();

            /** Additional data - Untuk card di view budget */
            $tot_budget = Budget::where('year', $year)->selectRaw('SUM(amount) as tot_amount')->get();
            $tot_plan = Order::whereYear('updated_at', $year)->where('status', 'in-progress')->selectRaw('SUM(tot_price) as tot_plan')->get();
            $tot_actual = Order::whereYear('updated_at', $year)->where('status', 'finish')->selectRaw('SUM(tot_price) as tot_actual')->get();

        } else {
            $budgets = Budget::with(['cc:id,cc', 'category', 'picExp', 'orders'])->where('year', $year)->where('cc_id', $ccBy)->get();

            /** Additional data - Untuk card di view budget */
            $tot_budget = Budget::where('year', $year)->where('cc_id', $ccBy)->selectRaw('SUM(amount) as tot_amount')->get();
            $tot_plan = Order::whereYear('updated_at', $year)->where('status', 'in-progress')->where('cc_id', $ccBy)->selectRaw('SUM(tot_price) as tot_plan')->get();
            $tot_actual = Order::whereYear('updated_at', $year)->where('status', 'finish')->where('cc_id', $ccBy)->selectRaw('SUM(tot_price) as tot_actual')->get();

        }

        $tot_forecast = $tot_plan[0]->tot_plan + $tot_actual[0]->tot_actual;
        $balance = $tot_budget[0]->tot_amount - $tot_forecast;

        $tot_budget = number_format($tot_budget[0]->tot_amount, 0, '.', ',');
        $tot_forecast = number_format($tot_forecast, 0, '.', ',');
        $balance = number_format($balance, 0, '.', ',');

        // $orders = Order::groupBy('budget_id')->selectRaw('budget_id, SUM(tot_price) as tot_plan')->get();

        return Datatables::of($budgets)
            ->addIndexColumn()
            ->editColumn('cc_id', function($budgets) {
                return $budgets->cc->cc;
            })
            ->editColumn('category_id', function($budgets) {
                return $budgets->category->cat_name;
            })
            ->editColumn('pic', function($budgets) {
                return $budgets->picExp->name;
            })
            ->editColumn('amount', function($budgets) {
                return number_format($budgets->amount, '0', '.', ',');
            })
            ->addColumn('tot_forecast', function($budgets) {
                return number_format($budgets->tot_forecast, '0', '.', ',');
            })
            ->addColumn('tot_actual', function($budgets) {
                return number_format($budgets->tot_actual, '0', '.', ',');
            })
            ->addColumn('balance', function($budgets) {
                $balance = $budgets->amount - ($budgets->tot_forecast + $budgets->tot_actual);
                return number_format($balance, '0', '.', ',');
            })
            ->addColumn('action', function($budgets) {
                // $edit = Gate::allows('edit-budget', $budgets) ? '<i class="fa-regular fa-edit" onclick="editBudget(' . $budgets->id . ')"></i>' : '';
                $delete = Gate::allows('app-dev') ? '<i class="fa-regular fa-trash-can" onclick="deleteBudget(' . $budgets->id . ')"></i>' : '';

                $action = '<span style="cursor: pointer;">' . $delete . '</span>';

                return $action;
            })
            ->setRowClass(function($budgets) {
                $balance = $budgets->amount - ($budgets->tot_forecast + $budgets->tot_actual);
                return $balance >= 0 ? '' : 'text-danger';
            })
            ->setRowData([
                'dt_tot_budget' => function() use($tot_budget) {
                    return $tot_budget;
                },
                'dt_tot_forecast' => function() use($tot_forecast) {
                    return $tot_forecast;
                },
                'dt_balance' => function() use($balance) {
                    return $balance;
                },
            ])
            ->escapeColumns([])
            ->make(true);
    }

    public function upload($path, $delimiter, $is_new, $cc, $year)
    {
        $reader = Reader::createFromPath($path, 'r');
        $reader->setDelimiter($delimiter);
        $headers = $reader->fetchOne();
        $reader->setHeaderOffset(0);
        $data = $reader->getRecords();

        /* ------------------ Baru dan revise budget dalam 1 action ----------------- */
        /**
         * * Logic:
         * ? Get request apakah revise atau new
         * ? Revise >> Create
         * ? New >> Update
         *      TODO: Check data lama. Jika ada bukan baru
         *      TODO: STructure untuk update harus sama dengan yang ada dalam DB
         *      TODO: Budget >> Key menggunakan exp_no (dengan id budget dan year berdasarkan dari request)
         */
        /* ---------------------------------- -end- --------------------------------- */

        $csv_data = collect($data)[1] ?? null;

        if (! $csv_data) {
            /** Tidak ada data dari csv file */
            return false;
        }


        if ($is_new) {
            /** Data baru */

            $current_budget = Budget::where('cc_id', $cc)->where('year', $year)->get();

            if (count($current_budget) > 0) {
                return false;
            }
            // return dd($current_budget);
            try {
                DB::beginTransaction();
                foreach ($data as $b) {
                    $budget = Budget::create([
                        'cc_id' => $cc,
                        'exp_no' => $b['exp_no'],
                        'year' => $year,
                        'theme' => $b['theme'],
                        'category_id' => $b['category_id'],
                        'pic' => $b['pic'],
                        'amount' => $b['amount'],
                    ]);

                    $budget_id = $budget->id;

                    for ($i=1; $i < 13; $i++) {

                        if ($b[$i]) {
                            MonthlyBudget::create([
                                'budget_id' => $budget_id,
                                'month' => $i,
                                'amount' => $b[$i],
                            ]);
                        }
                    }
                }
                DB::commit();
            } catch (\Exception $e) {
                DB::rollBack();
                Log::error($e->getMessage());
                return false;
            }
        }

        if (! $is_new){
            /** Budget revise */
            // return dd(collect($data)[1]);
            // try {

                foreach ($data as $csv) {
                    $budget = Budget::where('cc_id', $cc)->where('year', $year)->where('exp_no', $csv['exp_no'])->get();
                    if (! $budget) {
                        return false;
                    }

                    $budget = Budget::where('cc_id', $cc)->where('year', $year)->where('exp_no', $csv['exp_no'])->update([
                        'theme' => $csv['theme'],
                        'category_id' => $csv['category_id'],
                        'pic' => $csv['pic'],
                        'amount' => $csv['amount'],
                    ]);

                    $monthly_budget = MonthlyBudget::where('budget_id', $budget)->delete();

                    for ($i=1; $i < 13; $i++) {

                        if ($csv[$i]) {
                            MonthlyBudget::create([
                                'budget_id' => $budget,
                                'month' => $i,
                                'amount' => $csv[$i],
                            ]);
                        }
                    }
                }
            // } catch (\Exception $e) {
            //     Log::error($e->getMessage());
            //     return false;
            // }
        }

        return true;
    }

    public function getData($id)
    {
        $budget = Budget::with([
                'cc:id,cc', 'picExp:id,nik,name', 'monthly:id,budget_id,month,amount'
            ])->find($id);

        return $budget;
    }

    public function update(array $data)
    {
        $budget = Budget::find($data['budget_id']);

        DB::beginTransaction();
        try {
            $budget->update(['amount' => $data['amount']]);
            for ($i=0; $i < 13; $i++) {
                if ($data['monthly'][$i]['amount'] > 0) {
                    $monthly_budget = MonthlyBudget::where('budget_id', $data['budget_id'])->where('month', $i + 1)->update([
                        'amount' => $data['monthly'][$i]['amount'],
                    ]);
                } else {
                    $monthly_budget = MonthlyBudget::where('budget_id', $data['budget_id'])->where('month', $i + 1)->delete();
                }
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error($e->getMessage());
            return ['error' => $e->getMessage()];
        }

        return $budget;
    }
}