<?php

namespace App\Http\Controllers\Admin;

use App\Helpers\CustomDateFilter;
use App\Helpers\Helper;
use App\Helpers\JournalAuto;
use App\Http\Controllers\Controller;
use App\Models\Accounting\ChartOfAccount;
use App\Models\Accounting\Journal;
use App\Models\AccReceivableDetails;
use App\Models\Loan;
use App\Models\Reschedule;
use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class ClosingControler extends Controller
{

    function __construct()
    {
        $this->middleware('permission:Closing Report', ['only' => ['index']]);
        $this->middleware('permission:Submit Closing Report', ['only' => ['store']]);
    }

    public function loanDisbursment($request)
    {
        $data = Loan::select(
            DB::raw('SUM(loan_out) AS LoanDisbursed'),
            DB::raw('SUM(total_service) AS totalService'),
            DB::raw('SUM(total_collection) AS totalCollection'),
            DB::raw('SUM(total_insurance) AS totalInsurance'),
            // DB::raw('COUNT(id) AS count_loan'),
            'chart_of_acc_id',
        )
        ->where(function($query) use ($request){
            $query->whereIn('loan_status_id',[2,3]);
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if($request->today == null && $request->date==[]){
                $query->where('closing_date',null);
            }
            if($request->today == null){
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
            }
            if($request->today != null){
                $query->where('closing_date',null);
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"),$request->today);
            }
            if(!$request->co_id ==null){
                $query->where('co_id',$request->co_id);
            }
        });
        return $data;
    }

    public function getJournalCash(){
        $data = Journal::select(
            DB::raw("SUM(journals.debit-journals.credit) AS totalDebit"),
            'journals.journal_date',
        )
        ->leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
        ->where(function($query){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
        })
        ->groupBy('journals.chart_of_acc_id');
        return $data;
    }

    public function getJournalStart($request)
    {
        $data = $this->getJournalCash($request)
            ->where(function($query) use ($request){
                if($request->date==[]) {
                    $query->where(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"),'<',date('Y-m-d'));
                } else{
                    $query->where(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"),'<',date('Y-m-d',strtotime($request->date[1])));
                }
            });
        return $data;
    }

    public function getJournalLast($request)
    {
        $data = $this->getJournalCash($request)
            ->where(function($query) use($request){
                if($request->date==[]) {
                    $query->where(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"),'<=',date('Y-m-d'));
                }else{
                    $query->where(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"),'<=',date('Y-m-d',strtotime($request->date[1])));
                }
            });
            return $data;
    }

    public function funReceived($request)
    {
        $data = AccReceivableDetails::select(
            DB::raw('SUM(paid_principal) as paidPrincipal'),
            DB::raw('SUM(paid_interest) as paidInterest'),
            DB::raw('SUM(paid_risk) as paidRisk'),
            DB::raw('SUM(paid_maintenance) as paidMaintenance'),
            DB::raw('SUM(paid_penalty) as paidPenalty'),
            DB::raw('SUM(received) as sumReceived'),
            'chart_of_acc_id',
        )
        ->where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('status',2);
            if(!$request->co_id ==null){
                $query->where('co_id',$request->co_id);
            }
        });
        return $data;
    }

    public function accDetailNotClosing(Request $request)
    {
        $data = AccReceivableDetails::select(
            DB::raw('SUM(received) as sumReceived'),
            DB::raw('SUM(paid_principal) as sumPrincipal'),
            DB::raw('SUM(paid_interest) as sumInterest'),
            DB::raw('SUM(paid_penalty) as paidPenalty'),
        )
        ->where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('status',2);
            if($request->today == null){
                $query->whereBetween(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
            }
            if($request->today == null && $request->date==[]) {
                $query->where('closing_date',null);
            }
            if($request->today != null){
                $query->where('closing_date',null);
                $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"),$request->today);
            }
            if(!$request->co_id ==null){
                $query->where('co_id',$request->co_id);
            }
        });
        return $data;
    }

    public function paymentToday(Request $request)
    {
        $today = (empty($request->today)?date('Y-m-d'):date('Y-m-d',strtotime($request->today)));
        $data = AccReceivableDetails::select(
            DB::raw('SUM(acc_receivable_details.paid_principal) AS TodayPaidPrincipal'),
            DB::raw('SUM(acc_receivable_details.paid_interest) AS TodayPaidInterest'),
            DB::raw('SUM(acc_receivable_details.paid_risk) AS TodayPaidRisk'),
            DB::raw('SUM(acc_receivable_details.paid_maintenance) AS TodayPaidMaintenance'),
            DB::raw('SUM(acc_receivable_details.paid_penalty) AS TodayPaidPenalty'),
            'acc_receivable_details.chart_of_acc_id'
        )
        ->leftjoin('acc_receivables','acc_receivables.id','acc_receivable_details.acc_id')
        ->leftjoin('loans','loans.id','acc_receivables.loan_id')
        ->where(function($query) use ($request){
            $query->whereIn('acc_receivable_details.branch_id',Helper::byBranch());
            $query->where('acc_receivable_details.currency_id',Helper::byCurrency());
            $query->whereNotIn('acc_receivable_details.condition_payment',[6,8]);
            if($request->today == null && $request->date==[]){
                $query->where('acc_receivable_details.closing_date',null);
            }
            if($request->today == null){
                $query->whereBetween(DB::raw("DATE_FORMAT(acc_receivable_details.received_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
            }
            if($request->today != null){
                $query->where('acc_receivable_details.closing_date',null);
                $query->where(DB::raw("DATE_FORMAT(acc_receivable_details.received_date, '%Y-%m-%d')"),$request->today);
            }
            if(!$request->co_id ==null){
                $query->where('acc_receivable_details.co_id',$request->co_id);
            }
        })
        ->where(function($query) use($today){
            $query->whereNotIn('loans.id', [
                DB::raw("SELECT loan_id FROM acc_receivables
                    WHERE payment_status_id IN (2,3)
                    GROUP BY loan_id
                    HAVING max(payment_date) < '$today'")
                ]
            );
            $query->where('loans.client_type',1);
        })
        ->where('acc_receivable_details.status',2)
        ->where('acc_receivable_details.received_date',$today)
        ->where('acc_receivables.payment_date','<=',$today)
        ->whereNotIn('acc_receivables.payment_status_id',[6,7]);
        return $data;
    }

    public function paymentPrepaid(Request $request)
    {
        $today = (empty($request->today)?date('Y-m-d'):date('Y-m-d',strtotime($request->today)));
        $data = AccReceivableDetails::select(
            DB::raw('SUM(acc_receivable_details.received)+SUM(acc_receivable_details.paid_penalty) AS total_prepaid'),
            'acc_receivable_details.chart_of_acc_id'
        )
        ->leftjoin('acc_receivables','acc_receivables.id','acc_receivable_details.acc_id')
        ->leftjoin('loans','loans.id','acc_receivables.loan_id')
        ->where(function($query) use ($request){
            $query->whereIn('acc_receivable_details.branch_id',Helper::byBranch());
            $query->where('acc_receivable_details.currency_id',Helper::byCurrency());
            $query->whereNotIn('acc_receivable_details.condition_payment',[6,8]);
            if($request->today == null && $request->date==[]){
                $query->where('acc_receivable_details.closing_date',null);
            }
            if($request->today == null){
                $query->whereBetween(DB::raw("DATE_FORMAT(acc_receivable_details.received_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
            }
            if($request->today != null){
                $query->where('acc_receivable_details.closing_date',null);
                $query->where(DB::raw("DATE_FORMAT(acc_receivable_details.received_date, '%Y-%m-%d')"),$request->today);
            }
            if(!$request->co_id ==null){
                $query->where('acc_receivable_details.co_id',$request->co_id);
            }
        })
        ->where(function($query) use($today){
            $query->whereNotIn('loans.id', [
                DB::raw("SELECT loan_id FROM acc_receivables
                    WHERE payment_status_id IN (2,3)
                    GROUP BY loan_id
                    HAVING max(payment_date) < '$today'")
                ]
            );
            $query->where('loans.client_type',1);
        })
        ->where('acc_receivable_details.status',2)
        ->where('acc_receivable_details.received_date',$today)
        ->where('acc_receivables.payment_date','>',$today);
        return $data;
    }

    public function index(Request $request)
    {

        // ******* ដើមគ្រា *******//
        $initail_amount = $this->getJournalStart($request)
            ->whereHas('chartOfAcc',function($query){
                $query->where('chart_code','LIKE' , '1%');
                $query->whereIn('payment',[1,2]);
            })
            ->get()->map(function($value){
                return [
                    'total' => $value->totalDebit,
                ];
            });

        $total_original_amount = 0;
        foreach ($initail_amount as $val) {
            $total_original_amount += $val["total"];
        }
        // ******* End ដើមគ្រា *******

        $Disbursed = $this->loanDisbursment($request)->first();

        $arr_loan_disburst = $this->loanDisbursment($request)->groupBy('chart_of_acc_id')->get()->map(function($value){
            return [
                'chart_of_acc_id' => $value->chart_of_acc_id,
                'chart_code_name' => $value->disburstVia->chart_code.'-'.$value->disburstVia->chart_name,
                'loan_out'        => $value->LoanDisbursed,
                'total_service'   => $value->totalService,
                'total_collection'=> $value->totalCollection,
                'total_insurance' => $value->totalInsurance,
            ];
        });

        // All Received(Cash on hand, Acleda,...)
        $accDetailAllReceived = $this->funReceived($request)
            ->where(function($query) use ($request){
                if($request->today == null){
                    $query->whereBetween(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
                }
                if($request->today == null && $request->date==[]) {
                    $query->where('closing_date',null);
                }
                if($request->today != null){
                    $query->where('closing_date',null);
                    $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"),$request->today);
                }
            })
            ->first();

        $totalRepayClient = $accDetailAllReceived->sumReceived + $accDetailAllReceived->paidPenalty;  // All received with penalty
        $TotalReceived    = $totalRepayClient + $Disbursed->totalCollection + $Disbursed->totalService + $Disbursed->totalInsurance;  // All received with collection,serivce,insurance

        $arr_received_datails = $this->funReceived($request)
            ->where(function($query) use ($request){
                if($request->today == null){
                    $query->whereBetween(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
                }
                if($request->today == null && $request->date==[]) {
                    $query->where('closing_date',null);
                }
                if($request->today != null){
                    $query->where('closing_date',null);
                    $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"),$request->today);
                }
            })
            ->groupBy('chart_of_acc_id')->get()->map(function($value){
                return [
                    'chart_of_acc_id' => $value->chart_of_acc_id,
                    'chart_code_name' => $value->payment_methods->chart_code.'-'.$value->payment_methods->chart_name,
                    'total_received'  => $value->sumReceived + $value->paidPenalty,
                    'paid_principal'  => $value->paidPrincipal,
                    'paid_interest'   => $value->paidInterest,
                    'paid_risk'       => $value->paidRisk,
                    'paid_maintenance'=> $value->paidMaintenance,
                    'paid_penalty'    => $value->paidPenalty,
                ];
            });


        // ******* ចុងគ្រា *******//
        $last_amount = $this->getJournalLast($request)
            ->whereHas('chartOfAcc',function($query){
                $query->where('chart_code','LIKE' , '1%');
                $query->whereIn('payment',[1,2]);
            })
            ->get()->map(function($value){
                return [
                    'total' => $value->totalDebit,
                ];
            });
        $sum_last_amount = 0;
        foreach($last_amount as $val){
            $sum_last_amount += $val["total"];
        }
        // ******* End ចុងគ្រា *******

        $disbNotClosing = Loan::select(
            DB::raw('SUM(loan_out) AS LoanDisbursed'),
            DB::raw('SUM(total_service) AS totalService'),
            DB::raw('SUM(total_insurance) AS totalInsurance')
        )
        ->where(function($query) use ($request){
            $query->where('loan_status_id',2);
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('closing_date',null);
           if(!$request->date==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
            } else{
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), CustomDateFilter::thisDay());
            }
        })
        ->first();

        $accDetailNotClosing = $this->accDetailNotClosing($request)
        ->whereHas('payment_methods',function($query){
            $query->where('chart_code','LIKE' , '1%');
            $query->whereIn('payment',[1,2]);
        })
        ->first();

        $totalRepayClient_not = $accDetailNotClosing->sumReceived + $accDetailNotClosing->paidPenalty;  // All received and paid penalty (Cash on hand)

        $total_all_received = $totalRepayClient_not + $disbNotClosing->totalInsurance +$disbNotClosing->totalService; // All received, insurnace, service

        if(!$request->date==[]) {
            $total_last_sum_amount = $sum_last_amount + ($total_all_received - $disbNotClosing->LoanDisbursed);
        } else{
            $total_last_sum_amount = $sum_last_amount + ($TotalReceived - $Disbursed->LoanDisbursed);
        }

        $assets =  Journal::select(
            DB::raw("SUM(journals.debit-journals.credit) AS total"),
            'journals.chart_of_acc_id',
        )
        ->leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
        ->where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"), $request->date);
            }else{
                if(!$request->isEdit)
                {
                    $query->where(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"),'<=',date('Y-m-d'));
                }else{
                    $query->where(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"),'<',date('Y-m-d'));
                }
            }
        })
        ->whereHas('chartOfAcc',function($query){
            $query->where('chart_code','LIKE' , '1%');
            $query->whereIn('payment',[1,2]);
        })
        ->groupBy('journals.chart_of_acc_id')
        ->orderBy('chart_of_accounts.chart_code', 'ASC')
        ->get()->map(function($value){
            return [
                'total'           => $value->total,
                'chart_of_acc_id' => $value->chartOfAcc->id??'',
                'chart_code_name' => $value->chartOfAcc->chart_code.'-'.$value->chartOfAcc->chart_name,
            ];
        });

        // ************ Today, Prepaid, Payoff ************//
        $data_today = $this->paymentToday($request)->first();
        $arr_today  = $this->paymentToday($request)->groupBy('chart_of_acc_id')->get()->map(function($value){
            return [
                'chart_of_acc_id' => $value->chart_of_acc_id,
                'chart_code_name' => $value->payment_methods->chart_code.'-'.$value->payment_methods->chart_name,
                'paid_principal'  => $value->TodayPaidPrincipal,
                'paid_interest'   => $value->TodayPaidInterest,
                'paid_risk'       => $value->TodayPaidRisk,
                'paid_maintenance'=> $value->TodayPaidMaintenance,
                'paid_penalty'    => $value->TodayPaidPenalty,
            ];
        });

        $data_prepaid = $this->paymentPrepaid($request)->first();
        $arr_prepaid  = $this->paymentPrepaid($request)->groupBy('chart_of_acc_id')->get()->map(function($value){
            return [
                'chart_of_acc_id'=> $value->chart_of_acc_id,
                'chart_code_name'=> $value->payment_methods->chart_code.'-'.$value->payment_methods->chart_name,
                'total_prepaid'  => $value->total_prepaid,
            ];
        });

        $accDetailNotClosingPayoff = $this->accDetailNotClosing($request)
        ->whereHas('payment_methods',function($query){
            $query->where('chart_code','LIKE' , '1%');
            $query->whereIn('payment',[1,2]);
        })
        ->whereIn('condition_payment',[6,8])
        ->first();

        $totalPayOff = $accDetailNotClosingPayoff->sumReceived + $accDetailNotClosingPayoff->paidPenalty;


        $accDetailWriteOff = $this->accDetailNotClosing($request)
        ->whereHas('payment_methods',function($query){
            $query->where('chart_code','LIKE' , '1%');
            $query->whereIn('payment',[1,2]);
        })
        ->whereHas('loan',function($query){
            $query->where('client_type',2);
        })
        ->first();

        $totalReceiveWriteOff = $accDetailWriteOff->sumReceived + $accDetailWriteOff->paidPenalty;
        $totalDailyPayment = $data_today->TodayPaidPrincipal + $data_today->TodayPaidInterest + $data_today->TodayPaidRisk + $data_today->TodayPaidMaintenance;

        $co = User::find($request->co_id);

        return array(
            'totalOriginalAmount' => $total_original_amount,
            'LoanDisbursed'       => $Disbursed->LoanDisbursed?$Disbursed->LoanDisbursed:0,
            // 'countLoan'           => $Disbursed->count_loan?$Disbursed->count_loan:0,
            'arr_loan_disburst'   => $arr_loan_disburst??[],
            'TotalReceived'       => $TotalReceived?$TotalReceived:0,
            'arr_received_datails'=> $arr_received_datails??[],
            'totalService'        => $Disbursed->totalService?$Disbursed->totalService:0,
            'totalCollection'     => $Disbursed->totalCollection?$Disbursed->totalCollection:0,
            'totalInsurance'      => $Disbursed->totalInsurance?$Disbursed->totalInsurance:0,
            'totalRepayClient'    => $totalRepayClient?$totalRepayClient:0,
            'paidPrincipal'       => $accDetailAllReceived->paidPrincipal?$accDetailAllReceived->paidPrincipal:0,
            'paidInterest'        => $accDetailAllReceived->paidInterest?$accDetailAllReceived->paidInterest:0,
            'paidRisk'            => $accDetailAllReceived->paidRisk?$accDetailAllReceived->paidRisk:0,
            'paidMaintenance'     => $accDetailAllReceived->paidMaintenance?$accDetailAllReceived->paidMaintenance:0,
            'paidPenalty'         => $accDetailAllReceived->paidPenalty?$accDetailAllReceived->paidPenalty:0,

            'arr_today'           => $arr_today??[],
            'totalLastSumAmount'  => $total_last_sum_amount,
            'totalPrincipalToday' => $data_today->TodayPaidPrincipal?$data_today->TodayPaidPrincipal:0,
            'totalInterestToday'  => $data_today->TodayPaidInterest?$data_today->TodayPaidInterest:0,
            'totalRiskToday'      => $data_today->TodayPaidRisk?$data_today->TodayPaidRisk:0,
            'totalMaintenanceToday'=> $data_today->TodayPaidMaintenance?$data_today->TodayPaidMaintenance:0,
            'totalPenaltyToday'   => $data_today->TodayPaidPenalty?$data_today->TodayPaidPenalty:0,
            'totalDailyPayment'   => $totalDailyPayment??0,
            'total_prepaid'       => $data_prepaid->total_prepaid?$data_prepaid->total_prepaid:0,
            'arr_prepaid'         => $arr_prepaid??[],

            'totalRepayClient_not'=> $totalRepayClient_not?$totalRepayClient_not:0,
            'totalPayOff'         => $totalPayOff?$totalPayOff:0,
            'totalReceiveWriteOff'=> $totalReceiveWriteOff?$totalReceiveWriteOff:0,
            'co_name'             => $co->name??'',
            'assets'              => $assets,
        );
    }

    public function store(Request $request)
    {
        $today = (empty($request->params['today'])?date('Y-m-d'):date('Y-m-d',strtotime($request->params['today'])));
        // បញ្ចេញកម្ចី
        $LoanDisbursed   = $request->dataPayments['LoanDisbursed'];
        // សេវាប្រមូលម្តងហើយ
        $totalService    = $request->dataPayments['totalService'];
        $totalInsurance  = $request->dataPayments['totalInsurance'];
        $totalCollection = $request->dataPayments['totalCollection'];
        // Collection
        $paidPrincipal   = $request->dataPayments['paidPrincipal'];
        $paidInterest    = $request->dataPayments['paidInterest'] ;
        $paidRisk        = $request->dataPayments['paidRisk'];
        $paidMaintenance = $request->dataPayments['paidMaintenance'];
        $paidPenalty     = $request->dataPayments['paidPenalty'];
        // Array
        $arrayLoanDisburst = $request->dataPayments['arr_loan_disburst'];
        $arrayReceived = $request->dataPayments['arr_received_datails'];

        $client_id = $request->client_id;
        $co_id     = $request->co_id;
        $date      = $request->params['today'].' '.date('H:i:s');
        $user = User::find(Auth::user()->id);
        $user_name = $user->name;

        try{
            DB::beginTransaction();

            // Update Loans
            Loan::where(function($query) use ($today,$client_id,$co_id){
                $query->where('closing_date',null);
                $query->whereIn('loan_status_id',[2,3]);
                $query->whereIn('branch_id',Helper::byBranch());
                $query->where('currency_id',Helper::byCurrency());
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), $today);
                if(!$client_id ==[]) {
                    $query->whereIn('client_id',$client_id);
                }
                if(!$co_id ==[]){
                    $query->whereIn('co_id',$co_id);
                }
            })
            ->update([
                'closing_date' => empty($request->params['today'])?date('Y-m-d H:i:s'):date('Y-m-d H:i:s',strtotime($date)),
                'closing_by' => Auth::user()->id,
            ]);
            // Update Loans
            Reschedule::where(function($query) use ($today,$client_id,$co_id){
                $query->where('closing_date',null);
                $query->where('status',3);
                $query->whereIn('branch_id',Helper::byBranch());
                $query->where('currency_id',Helper::byCurrency());
                $query->where(DB::raw("DATE_FORMAT(date, '%Y-%m-%d')"), $today);
                if(!$client_id ==[]){
                    $query->whereIn('client_id',$client_id);
                }
                if(!$co_id ==[])
                {
                    $query->whereIn('co_id',$co_id);
                }
            })
            ->update([
                'closing_date' => empty($request->params['today'])?date('Y-m-d H:i:s'):date('Y-m-d H:i:s',strtotime($date)),
                'closing_by' => Auth::user()->id,
            ]);
            // Update AccReceivableDetails
            AccReceivableDetails::where(function($query) use ($today,$client_id,$co_id){
                $query->where('closing_date',null);
                $query->where('status',2);
                $query->whereIn('branch_id',Helper::byBranch());
                $query->where('currency_id',Helper::byCurrency());
                $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $today);
                if(!$client_id ==[]){
                    $query->whereIn('client_id',$client_id);
                }
                if(!$co_id ==[]){
                    $query->whereIn('co_id',$co_id);
                }
            })
            ->update([
                'closing_date' => empty($request->params['today'])?date('Y-m-d H:i:s'):date('Y-m-d H:i:s',strtotime($date)),
                'closing_by' => Auth::user()->id,
            ]);

            $accRe             = ChartOfAccount::where('chart_code','10006')->first();
            $interest          = ChartOfAccount::where('chart_code','40002')->first();
            $risk              = ChartOfAccount::where('chart_code','40003')->first();
            $service           = ChartOfAccount::where('chart_code','40004')->first();
            $security_service  = ChartOfAccount::where('chart_code','40005')->first();
            $penalty           = ChartOfAccount::where('chart_code','40006')->first();
            $operating_serivce = ChartOfAccount::where('chart_code','40007')->first();
            $chart_insurance   = ChartOfAccount::where('chart_code','40008')->first();

            $model_type   = 'App\Models\Accounting\ChartOfAccount';
            $journal_date = $today;
            $branch_id    = Auth::user()->branch_id;
            $created_by   = Auth::user()->id;
            $currency_id  = Auth::user()->currency_id;
            $status       = 1;

            if($LoanDisbursed>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណាយទឹកប្រាក់កម្ចីផ្ដល់អោយអតិថិជន និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayLoanDisburst as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => $value['loan_out'],
                        'debit'             => 0,
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // A/R
                $obj['debit']  = $LoanDisbursed;
                $obj['credit'] = 0;
                $obj['chart_of_acc_id']  = $accRe->id;
                $obj['payment_model_id'] = $accRe->id;
                JournalAuto::fInsertJournal($obj);
            }

            if($totalService>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណូលបានមកពីសេវារដ្ឋបាល និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayLoanDisburst as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => 0,
                        'debit'             => $value['total_service'],
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // Administrative service income
                $obj['debit']  = 0;
                $obj['credit'] = $totalService;
                $obj['chart_of_acc_id']  = $service->id;
                $obj['payment_model_id'] = $service->id;
                JournalAuto::fInsertJournal($obj);
            }
            if($totalInsurance>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណូលបានមកពីសេវាធានារ៉ាប់រង និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayLoanDisburst as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => 0,
                        'debit'             => $value['total_insurance'],
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // Insurance income
                $obj['debit']  = 0;
                $obj['credit'] = $totalInsurance;
                $obj['chart_of_acc_id']  = $chart_insurance->id;
                $obj['payment_model_id'] = $chart_insurance->id;
                JournalAuto::fInsertJournal($obj);
            }
            if($totalCollection>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណូលបានមកពីសេវាហានិភ័យ និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayLoanDisburst as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => 0,
                        'debit'             => $value['total_collection'],
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // Risk income
                $obj['debit']  = 0;
                $obj['credit'] = $totalCollection;
                $obj['chart_of_acc_id']  = $risk->id;
                $obj['payment_model_id'] = $risk->id;
                JournalAuto::fInsertJournal($obj);
            }

            if($paidPrincipal>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណូលបានមកពីប្រាក់ដើម និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayReceived as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => 0,
                        'debit'             => $value['paid_principal'],
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // A/R
                $obj['debit']  = 0;
                $obj['credit'] = $paidPrincipal;
                $obj['chart_of_acc_id']  = $accRe->id;
                $obj['payment_model_id'] = $accRe->id;
                JournalAuto::fInsertJournal($obj);
            }

            if($paidInterest>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណូលបានមកពីការប្រាក់ និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayReceived as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => 0,
                        'debit'             => $value['paid_interest'],
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // Interest income
                $obj['debit']  = 0;
                $obj['credit'] = $paidInterest;
                $obj['chart_of_acc_id']  = $interest->id;
                $obj['payment_model_id'] = $interest->id;
                JournalAuto::fInsertJournal($obj);
            }

            if($paidRisk>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណូលបានមកពីសេវាប្រតិបត្តិការ និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayReceived as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => 0,
                        'debit'             => $value['paid_risk'],
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // Risk service income
                $obj['debit']  = 0;
                $obj['credit'] = $paidRisk;
                $obj['chart_of_acc_id']  = $operating_serivce->id;
                $obj['payment_model_id'] = $operating_serivce->id;
                JournalAuto::fInsertJournal($obj);
            }

            if($paidMaintenance>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណូលបានមកពីសេវាសុវត្ថិភាព និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayReceived as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => 0,
                        'debit'             => $value['paid_maintenance'],
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // Maintenance income
                $obj['debit']  = 0;
                $obj['credit'] = $paidMaintenance;
                $obj['chart_of_acc_id']  = $security_service->id;
                $obj['payment_model_id'] = $security_service->id;
                JournalAuto::fInsertJournal($obj);
            }

            if($paidPenalty>0) {
                $parent_id = JournalAuto::findParentId();
                $desc = 'ចំណូលបានមកពីប្រាក់ពិន័យ និងបិទបញ្ជីដោយ '.$user_name.'';
                // Payment
                foreach($arrayReceived as $value){
                    $obj = [
                        'payment_model_type'=> $model_type,
                        'payment_model_id'  => $value['chart_of_acc_id'],
                        'branch_id'         => $branch_id,
                        'created_by'        => $created_by,
                        'currency_id'       => $currency_id,
                        'journal_date'      => $journal_date,
                        'parent_id'         => $parent_id,
                        'chart_of_acc_id'   => $value['chart_of_acc_id'],
                        'credit'            => 0,
                        'debit'             => $value['paid_penalty'],
                        'desc'              => $desc,
                        'status'            => $status,
                    ];
                    JournalAuto::fInsertJournal($obj);
                }
                // Penalty income
                $obj['debit']  = 0;
                $obj['credit'] = $paidPenalty;
                $obj['chart_of_acc_id']  = $penalty->id;
                $obj['payment_model_id'] = $penalty->id;
                JournalAuto::fInsertJournal($obj);
            }

            DB::commit();
            $res = [
                'status' => 1,
                'message'=> 'បិទបញ្ជីបានដោយជោគជ័យ',
                'data'   => [],
            ];
        }catch(\Exception $e){
            DB::rollBack();
            $res = [
                'status'  => 0,
                'message' => 'បិទបញ្ជីមិនជោគជ័យ',
                'data'    => $e->getMessage(),
            ];
        }
        return response()->json($res,200);
    }

    public function show($id)
    {
        //
    }

    public function edit($id)
    {
        //
    }

    public function update(Request $request, $id)
    {
        //
    }

    public function destroy($id)
    {
        //
    }
}
