<?php

namespace App\Http\Controllers;
use App\Models\Income;
use App\Models\Billing;
use App\Models\Branch;
use App\Models\Withdraw;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Gate;

class FinanceController extends Controller
{
    public function getTotalIncomeBranchFromBillingTable($method, $branchID, $month, $year)
    {
        $query = Billing::select(Billing::raw('sum(amount) as total'))->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                            ->where('enrolls.branch_id', '=', $branchID)->whereMonth('billings.date', '=', $month)->whereYear('billings.date', '=', $year)->where('method', '=', $method)
                            ->first();
        return $query->total;
    }

    public function getTotalIncomeBranchFromIncomeTable($method, $branchID, $month, $year)
    {
        $query = Income::select(Income::raw('sum(amount) as total'))
                        ->where('branch_id', '=', $branchID)->whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', $method)
                        ->first();
        return $query->total;
    }

    public function getFinance($month, $year)
    {
        if (Gate::allows('isSuperadmin')) {

            $branchs = Branch::select('id', 'name')->orderBy('code','asc')->get();

            for($i=0; $i<count($branchs); $i++) {
                // Incomes
                $incomeBranch[$i] = Billing::select('amount', 'billings.date as billDate', 'name', 'method')
                                            ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                            ->leftJoin('branchs', 'enrolls.branch_id', '=', 'branchs.id')
                                            ->where('branch_id', '=', $branchs[$i]->id)
                                            ->whereMonth('billings.date', '=', $month)
                                            ->whereYear('billings.date', '=', $year)
                                            ->orderBy('billings.date','asc')
                                            ->get();
                $totalIncomeBranch[$i] = Billing::select(Billing::raw('sum(amount) as total'))
                                                ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                                ->where('enrolls.branch_id', '=', $branchs[$i]->id)
                                                ->whereMonth('billings.date', '=', $month)
                                                ->whereYear('billings.date', '=', $year)
                                                ->first()->total;
                $totalIncomeBranchCash[$i] = $this->getTotalIncomeBranchFromBillingTable('เงินสด', $branchs[$i]->id, $month, $year);
                $totalIncomeBranchTransfer[$i] = $this->getTotalIncomeBranchFromBillingTable('โอน', $branchs[$i]->id, $month, $year);
                $totalIncomeBranchCredit[$i] = $this->getTotalIncomeBranchFromBillingTable('บัตรเครดิต', $branchs[$i]->id, $month, $year);
                // Incomes from another (menu รายรับ)
                $incomeAnotherBranch[$i] = Income::select('amount', 'date as billDate', 'name', 'method', 'title')
                                            ->leftJoin('branchs', 'branch_id', '=', 'branchs.id')
                                            ->where('branch_id', '=', $branchs[$i]->id)
                                            ->whereMonth('date', '=', $month)
                                            ->whereYear('date', '=', $year)
                                            ->orderBy('date','asc')
                                            ->orderBy('time','asc')
                                            ->get();
                $totalIncomeAnotherBranch[$i] = Income::select(Income::raw('sum(amount) as total'))
                                                ->where('branch_id', '=', $branchs[$i]->id)
                                                ->whereMonth('date', '=', $month)
                                                ->whereYear('date', '=', $year)
                                                ->first()->total;
                $totalIncomeAnotherBranchCash[$i] = $this->getTotalIncomeBranchFromIncomeTable('เงินสด', $branchs[$i]->id, $month, $year);
                $totalIncomeAnotherBranchTransfer[$i] = $this->getTotalIncomeBranchFromIncomeTable('โอน', $branchs[$i]->id, $month, $year);
                $totalIncomeAnotherBranchCredit[$i] = $this->getTotalIncomeBranchFromIncomeTable('บัตรเครดิต', $branchs[$i]->id, $month, $year);
                // Combine result incomeAllBranch and incomeAnotherAllBranch to single object
                foreach($incomeAnotherBranch[$i] as $x) {
                    $incomeBranch[$i]->add($x);
                }
                // Sort by billdate
                $incomeBranch[$i] = $incomeBranch[$i]->sortBy('billDate')->values()->all();

                $totalIncomeBranch[$i] += $totalIncomeAnotherBranch[$i];
                $totalIncomeBranchCash[$i] += $totalIncomeAnotherBranchCash[$i];
                $totalIncomeBranchTransfer[$i] += $totalIncomeAnotherBranchTransfer[$i];
                $totalIncomeBranchCredit[$i] += $totalIncomeAnotherBranchCredit[$i];

                // Expenses
                $expenseBranch[$i] = Withdraw::select('amount', 'date', 'name', 'method')
                                                ->leftJoin('branchs', 'withdraws.branch_id', '=', 'branchs.id')
                                                ->where('branch_id', '=', $branchs[$i]->id)
                                                ->whereMonth('withdraws.date', '=', $month)
                                                ->whereYear('withdraws.date', '=', $year)
                                                ->orderBy('withdraws.date','asc')
                                                ->get();
                $totalExpenseBranch[$i] = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                                ->where('withdraws.branch_id', '=', $branchs[$i]->id)
                                                ->whereMonth('date', '=', $month)
                                                ->whereYear('date', '=', $year)
                                                ->first();
                $totalExpenseBranchCash[$i] = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                                        ->where('branch_id', '=', $branchs[$i]->id)
                                                        ->whereMonth('date', '=', $month)
                                                        ->whereYear('date', '=', $year)
                                                        ->where('method', '=', 'เงินสด')
                                                        ->first();
                $totalExpenseBranchTransfer[$i] = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                                            ->where('branch_id', '=', $branchs[$i]->id)
                                                            ->whereMonth('date', '=', $month)
                                                            ->whereYear('date', '=', $year)
                                                            ->where('method', '=', 'โอน')
                                                            ->first();
            }

            // Incomes from register student
            $incomeAllBranch = Billing::select('amount', 'billings.date as billDate', 'name', 'method')
                                    ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                    ->leftJoin('branchs', 'enrolls.branch_id', '=', 'branchs.id')
                                    ->whereMonth('billings.date', '=', $month)
                                    ->whereYear('billings.date', '=', $year)
                                    ->orderBy('billings.date','asc')
                                    ->orderBy('billings.time','asc')
                                    ->get();
            $totalIncomeAllBranch = Billing::select(Billing::raw('sum(amount) as total'))->whereMonth('date', '=', $month)->whereYear('date', '=', $year)->first()->total;
            $totalIncomeAllBranchCash = Billing::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'เงินสด')->sum('amount');
            $totalIncomeAllBranchTransfer = Billing::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'โอน')->sum('amount');
            $totalIncomeAllBranchCredit = Billing::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'บัตรเครดิต')->sum('amount');

            // Incomes from another (menu รายรับ)
            $incomeAnotherAllBranch = Income::select('amount', 'date as billDate', 'name', 'method', 'title')
                                    ->leftJoin('branchs', 'incomes.branch_id', '=', 'branchs.id')
                                    ->whereMonth('date', '=', $month)
                                    ->whereYear('date', '=', $year)
                                    ->orderBy('date','asc')
                                    ->orderBy('time','asc')
                                    ->get();
            $totalIncomeAnotherAll = Income::select(Income::raw('sum(amount) as total'))->whereMonth('date', '=', $month)->whereYear('date', '=', $year)->first()->total;
            $totalIncomeAnotherAllCash = Income::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'เงินสด')->sum('amount');
            $totalIncomeAnotherAllTransfer = Income::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'โอน')->sum('amount');
            $totalIncomeAnotherAllCredit = Income::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'บัตรเครดิต')->sum('amount');

            // Combine result incomeAllBranch and incomeAnotherAllBranch to single object
            foreach($incomeAnotherAllBranch as $i) {
                $incomeAllBranch->add($i);
            }
            // Sort by billdate
            $incomeAllBranch = $incomeAllBranch->sortBy('billDate')->values()->all();

            $totalIncomeAllBranch += $totalIncomeAnotherAll;
            $totalIncomeAllBranchCash += $totalIncomeAnotherAllCash;
            $totalIncomeAllBranchTransfer += $totalIncomeAnotherAllTransfer;
            $totalIncomeAllBranchCredit += $totalIncomeAnotherAllCredit;

            // Expenses
            $expenseAllBranch = Withdraw::select('amount', 'date', 'name', 'method')
                                        ->leftJoin('branchs', 'withdraws.branch_id', '=', 'branchs.id')
                                        ->whereMonth('withdraws.date', '=', $month)
                                        ->whereYear('withdraws.date', '=', $year)
                                        ->orderBy('withdraws.date','asc')
                                        ->get();
            $totalExpenseAllBranch = Withdraw::select(Withdraw::raw('sum(amount) as total'))->whereMonth('date', '=', $month)->whereYear('date', '=', $year)->first();
            $totalExpenseAllBranchCash = Withdraw::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'เงินสด')->sum('amount');
            $totalExpenseAllBranchTransfer = Withdraw::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'โอน')->sum('amount');

            return view('finance.index')->with([
                'page_title' => 'การเงิน',
                'branchs' => $branchs,
                'month' => $month,
                'year' => $year,
                'income_all_branch' => $incomeAllBranch,
                'total_income_all_branch' => $totalIncomeAllBranch,
                'total_income_all_branch_cash' => $totalIncomeAllBranchCash,
                'total_income_all_branch_transfer' => $totalIncomeAllBranchTransfer,
                'total_income_all_branch_credit' => $totalIncomeAllBranchCredit,
                'income_branchs' => $incomeBranch,
                'total_income_branch' => $totalIncomeBranch,
                'total_income_branch_cash' => $totalIncomeBranchCash,
                'total_income_branch_transfer' => $totalIncomeBranchTransfer,
                'total_income_branch_credit' => $totalIncomeBranchCredit,
                'expense_all_branch' => $expenseAllBranch,
                'expense_branch' => $expenseBranch,
                'total_expense_branch_cash' => $totalExpenseBranchCash,
                'total_expense_branch_transfer' => $totalExpenseBranchTransfer,
                'total_expense_all_branch' => $totalExpenseAllBranch->total,
                'total_expense_all_branch_cash' => $totalExpenseAllBranchCash,
                'total_expense_all_branch_transfer' => $totalExpenseAllBranchTransfer,
                'total_expense_branch' => $totalExpenseBranch,
            ]);

        } else {   // Admin and user\
            $branch = auth()->user()->branch_id;
            $branchName = Branch::find($branch);
            // Incomes
            $income = Billing::select('amount', 'billings.date as billDate', 'name', 'method')
                                ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                ->leftJoin('branchs', 'enrolls.branch_id', '=', 'branchs.id')
                                ->where('branch_id', '=', $branch)
                                ->whereMonth('billings.date', '=', $month)
                                ->whereYear('billings.date', '=', $year)
                                ->orderBy('billings.date','asc')
                                ->get();
            $totalIncome = Billing::select(Billing::raw('sum(amount) as total'))
                                    ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                    ->where('enrolls.branch_id', '=', $branch)
                                    ->whereMonth('billings.date', '=', $month)
                                    ->whereYear('billings.date', '=', $year)
                                    ->first()->total;
            $totalIncomeCash = $this->getTotalIncomeBranchFromBillingTable('เงินสด', $branch, $month, $year);
            $totalIncomeTransfer = $this->getTotalIncomeBranchFromBillingTable('โอน', $branch, $month, $year);
            $totalIncomeCredit = $this->getTotalIncomeBranchFromBillingTable('บัตรเครดิต', $branch, $month, $year);
            // Incomes from another (menu รายรับ)
            $incomeAnother = Income::select('amount', 'date as billDate', 'name', 'method', 'title')
                                    ->leftJoin('branchs', 'branch_id', '=', 'branchs.id')
                                    ->where('branch_id', '=', $branch)
                                    ->whereMonth('date', '=', $month)
                                    ->whereYear('date', '=', $year)
                                    ->orderBy('date','asc')
                                    ->orderBy('time','asc')
                                    ->get();
            $totalIncomeAnother = Income::select(Income::raw('sum(amount) as total'))
                                        ->where('branch_id', '=', $branch)
                                        ->whereMonth('date', '=', $month)
                                        ->whereYear('date', '=', $year)
                                        ->first()->total;
            $totalIncomeAnotherCash = $this->getTotalIncomeBranchFromIncomeTable('เงินสด', $branch, $month, $year);
            $totalIncomeAnotherTransfer = $this->getTotalIncomeBranchFromIncomeTable('โอน', $branch, $month, $year);
            $totalIncomeAnotherCredit = $this->getTotalIncomeBranchFromIncomeTable('บัตรเครดิต', $branch, $month, $year);
            // Combine result incomeAllBranch and incomeAnotherAllBranch to single object
            foreach($incomeAnother as $x) {
                $income->add($x);
            }
            // Sort by billdate
            $income = $income->sortBy('billDate')->values()->all();

            $totalIncome += $totalIncomeAnother;
            $totalIncomeCash += $totalIncomeAnotherCash;
            $totalIncomeTransfer += $totalIncomeAnotherTransfer;
            $totalIncomeCredit += $totalIncomeAnotherCredit;

            // Expenses
            $expense = Withdraw::select('amount', 'date', 'name', 'method')
                                ->leftJoin('branchs', 'withdraws.branch_id', '=', 'branchs.id')
                                ->where('branch_id', '=', $branch)
                                ->whereMonth('date', '=', $month)
                                ->whereYear('date', '=', $year)
                                ->orderBy('date','asc')
                                ->get();
            $totalExpense = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                        ->where('branch_id', '=', $branch)
                                        ->whereMonth('date', '=', $month)
                                        ->whereYear('date', '=', $year)
                                        ->first();
            $totalExpenseCash = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                        ->whereMonth('date', '=', $month)
                                        ->whereYear('date', '=', $year)
                                        ->where('branch_id', '=', $branch)
                                        ->where('method', '=', 'เงินสด')
                                        ->first();
            $totalExpenseTransfer = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                            ->whereMonth('date', '=', $month)
                                            ->whereYear('date', '=', $year)
                                            ->where('branch_id', '=', $branch)
                                            ->where('method', '=', 'โอน')
                                            ->first();

            return view('finance.index')->with([
                'page_title' => 'การเงิน สาขา'.$branchName->name,
                'month' => $month,
                'year' => $year,
                'incomes' => $income,
                'total_income' => $totalIncome,
                'total_income_cash' => $totalIncomeCash,
                'total_income_transfer' => $totalIncomeTransfer,
                'total_income_credit' => $totalIncomeCredit,
                'expenses' => $expense,
                'total_expense' => $totalExpense,
                'total_expense_cash' => $totalExpenseCash,
                'total_expense_transfer' => $totalExpenseTransfer,
            ]);
        }
    }

    public function index()
    {
        return $this->getFinance(date('m'), date('Y'));
    }


    public function submit(Request $request)
    {
        if($request->monthYear != ""){
            $x = explode('/', $request->monthYear);
            $month = $x[0];
            $year = $x[1];
        }
        return $this->getFinance($month, $year);
    }

}
