<?php

namespace Accounting\setup;

use Accounting\ParamSetup;
use Base\DBQueries;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

use alhimik1986\PhpExcelTemplator\params\CallbackParam;
use alhimik1986\PhpExcelTemplator\params\ExcelParam;
use alhimik1986\PhpExcelTemplator\PhpExcelTemplator as Excel;
use alhimik1986\PhpExcelTemplator\setters\CellSetterArrayValueSpecial;
use alhimik1986\PhpExcelTemplator\setters\CellSetterStringValue;
use Matrix\Builder;
use PhpOffice\PhpSpreadsheet\IOFactory;

define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

class ChartOfAccounts extends ParamSetup
{
    private string $tbl = 'tbl_chart_of_accounts';
    private Request $request;
    private ?string $parent;
    private ?string $accountCode;
    private ?bool $isSubBook = false;
    private string $taxProvisionAccount = '318-0000';
    private string $retainedEarningsAccount = '503-0000';

    public function setParams(): array { return ['request' => $this->request, 'table' => $this->tbl];}

    public function main(Request $request) : JsonResponse {
        $this->request = $request;
        $this->accountCode = null;
        try {
            if($request->type === 'code'){
                $values = $request->conditions; //Include code under query in request_fields table: chart of accounts, field: code
                return successResponse("Success", $this->generateAccountCode($values['code']));
            }
            else if($request->type === 'balance'){
                $values = $request->conditions;
                $dates = explode(':', $values['params']);
                $response = DB::table('tbl_chart_of_accounts')->orderBy('code')->get();
                $response = $response->map(function ($response) use($dates) {
                    $details['customCode'] = $response->custom_code;
                    $details['code'] = $response->code;
                    $details['accTitle'] = $response->title;
                    if($dates[0] === $dates[1]){
                        $beginning = DB::table('tbl_general_ledgers')->where('acc_code', $response->code)
                            ->where('posted_date', '>=', $dates[0].' 00:00:00')->orderBy('code')->first();
                        $details['beginning'] = $beginning ? $beginning->beginning_balance != 0 ? formatAccountingCurrency($beginning->beginning_balance) : '' : '';
                    }
                    else {
                        $beginning = DB::table('tbl_general_ledgers')->where('acc_code', $response->code)
                            ->where('posted_date', '<=', $dates[0].' 00:00:00')->orderByDesc('code')->first();
                        $details['beginning'] = $beginning ? $beginning->ending_balance != 0 ? formatAccountingCurrency($beginning->ending_balance) : '' : '';

                    }
                    $ending = DB::table('tbl_general_ledgers')->where('acc_code', $response->code)
                        ->where('posted_date','<=', $dates[1].' 23:59:59')->orderByDesc('code')->first();
                    $details['ending'] = $ending ?  $ending->ending_balance != 0 ? formatAccountingCurrency($ending->ending_balance) : '' : '';
                    return $details;
                });
                return successResponse("Success", $response);
            }
            else if($request->type === 'trial_balance'){
                $values = $request->conditions;
                $dates = explode(':', $values['params']);
                $response = $this->generateTrialBalance($dates);
                $total['debit'] = 0;
                $total['credit'] = 0;
                $total['variance'] = "-";
                foreach($response as $value){
                    if($value['normal_balance'] === 'Dr'){
                        $total['debit'] = $total['debit'] + $value['amount'];
                    }
                    else {
                        $total['credit'] = $total['credit'] + $value['amount'];
                    }
                }
                if($total['credit'] > $total['debit']) $total['variance'] = formatAccountingCurrency($total['credit'] - $total['debit'])."(Cr)";
                else if($total['debit'] > $total['credit']) $total['variance'] = formatAccountingCurrency($total['debit'] - $total['credit'])."(Dr)";
                $total['debit'] = formatAccountingCurrency($total['debit']);
                $total['credit'] = formatAccountingCurrency($total['credit']);
                $data['accounts'] = $response;
                $data['total'] = $total;
                return successResponse("Success", $data);
            }
            else if($request->type === 'income_statement'){
                if($this->isSubBook) $params = $this->parseReportParamsSubBooks($request->conditions);
                else $params = $this->parseReportParams($request->conditions);
                return successResponse("Success", $this->generateIncomeStatement($params));
            }
            else if($request->type === 'balance_sheet'){
                if($this->isSubBook) $params = $this->parseReportParamsSubBooks($request->conditions);
                else $params = $this->parseReportParams($request->conditions);
                return successResponse("Success", $this->generateBalanceSheet($params));
            }
            else if($request->type === 'posting'){
                $values = $request->conditions;
                $data = explode(':', $values['params']);
                if($data[0] === 'monthly'){
                    $startDate = $data[1].'-'.$data[2].'-01';
                    $endDate = date("Y-m-t", strtotime($startDate));
                    $previousDate = date('Y-m-d', strtotime('-1 day', strtotime($startDate)));
                }
                else {
                    $startDate = $data[1].'-01-01';
                    $endDate = $data[1].'-12-31';
                    $previousDate = ($data[1] - 1).'-12-31';
                }
                $params['previous'] = $previousDate;
                $params['from'] = $startDate;
                $params['to'] = $endDate;
                $params['option'] = $data[3];
                $params['period'] = $data[0];
                $ledgerCounts = DB::table('tbl_ledgers')->where('trans_from', $params['from'])->where('trans_to', $params['to'])
                    ->where('period', $params['period'])->count();
                if($ledgerCounts){
                    $params['status'] = "Posted";
                    $accounts = DB::table('tbl_chart_of_accounts')->whereNot('parent','0')->get();
                    $accounts = $accounts->map(function ($accounts) use($params){
                        $beginningBalance = 0;
                        $endingBalance = 0;
                        $ledger = DB::table('tbl_ledgers')->where('acc_code', $accounts->code)->where('period', $params['period'])
                            ->where('trans_from', $params['from'])->where('trans_to', $params['to'])->first();
                        if($ledger){
                            $beginningBalance = $ledger->beginning_balance;
                            $endingBalance = $ledger->ending_balance;
                        }
                        return $this->createPostingEntries($accounts,$params,$beginningBalance,$endingBalance);
                    });
                }
                else {
                    $params['status'] = "Not posted";
                    $accounts = DB::table('tbl_chart_of_accounts')->whereNot('parent','0')->get();
                    $accounts = $accounts->map(function ($accounts) use($params){
                        $beginningBalance = 0;
                        $endingBalance = 0;
                        $ledger = DB::table('tbl_ledgers')->where('acc_code', $accounts->code)
                            ->where('trans_to', $params['previous'])->where('period', $params['period'])->first();
                        if($ledger) $beginningBalance = $ledger->ending_balance;
                        else {
                            $beginning = DB::table('tbl_general_ledgers')->where('acc_code', $accounts->code)
                                ->whereDate('posted_date','<=', $params['previous'].' 23:59:59')
                                ->where('status','posted')->orderByDesc('code')->first();
                            if($beginning) $beginningBalance = $beginning->ending_balance;
                        }
                        $ending = DB::table('tbl_general_ledgers')->where('acc_code', $accounts->code)
                            ->whereDate('posted_date','<=', $params['to'].' 23:59:59')
                            ->where('status','posted')->orderByDesc('code')->first();
                        if($ending) $endingBalance = $ending->ending_balance;
                        return $this->createPostingEntries($accounts,$params,$beginningBalance,$endingBalance);
                    });
                    foreach ($accounts as $account){
                        if($account) {
                            if($params['option'] === 'post'){
                                unset($account['custom_code']);
                                unset($account['acc_title']);
                                DB::table('tbl_ledgers')->insert($account);
                            }
                        }
                    }
                }
                $res['accounts'] = $accounts;
                $res['params'] = $params;
                $res['status'] =  $params['status'];
                return successResponse("Success", $res);
            }
            else if($request->type === 'print'){
                $params = $request->conditions;
                $params['file'] = base64_encode(file_get_contents($params['params'].".xlsx"));
                return successResponse("Success", $params);
            }
            else {
                if ($this->generateParams()) {
                    $this->additionalFields();
                    $action = $this->action;
                    $add = new DBQueries($this->table, $this->values, $this->conditions, $this->sort, $this->limit, $this->key);
                    $this->response = $add->$action();
                    return $this->getResponse($action);
                }
            }
        }
        catch (\Exception $ex){
            return failedResponse("ERROR", ["msg" => $ex->getMessage()], 422);
        }
        return failedResponse("ERROR", ["msg" => "Unknown Error!"], 400);
    }
    private function parseReportParams($values) : array {
        $data = explode(':', $values['params']);
        if($data[0] === 'period'){
            $startDate = $data[1].'-'.$data[2].'-01';
            $endDate = date("Y-m-t", strtotime($startDate));
            $previousDate = date('Y-m-d', strtotime('-1 day', strtotime($startDate)));
        }
        else {
            $startDate = $data[1].'-01-01';
            //$requestDate = $data[1].'-'.$data[2].'-01';
            //$endDate = date("Y-m-t", strtotime($requestDate));
            $endDate =  $data[2];
            $previousDate = ($data[1] - 1).'-12-31';
        }
        $params['previous'] = $previousDate;
        $params['from'] = $startDate;
        $params['to'] = $endDate;
        $params['period'] = $data[0];
        return $params;
    }
    private function parseReportParamsSubBooks($values) : array {
        $data = explode(':', $values['params']);
        if($data[0] === 'period'){
            $startDate = $data[1].'-'.$data[2].'-01';
            $endDate = date("Y-m-t", strtotime($startDate));
            $previousDateTo = date('Y-m-d', strtotime('-1 day', strtotime($startDate)));
            $previousDates = explode('-',$previousDateTo);
            $previousDateFrom = $previousDates[0].'-'.$previousDates[1].'-01';

        }
        else {
            $startDate = $data[1].'-01-01';
            $endDate =  $data[2];
            $previousDateFrom = ($data[1] - 1).'-01-01';
            $previousDateTo = ($data[1] - 1).'-12-31';
        }
        $params['previousFrom'] = $previousDateFrom;
        $params['previousTo'] = $previousDateTo;
        $params['from'] = $startDate;
        $params['to'] = $endDate;
        $params['period'] = $data[0];
        return $params;
    }
    private function parseFinancialStatements($params, $accountTypes, $tax) : array {
        $response = array();
        $accountData = array();
        $debitCredit = [
            "assets" => "Dr",
            "liabilities" => "Cr",
            "equity" => "Cr",
            "income" => "Cr",
            "expense" => "Dr",
        ];
        $ledgerCounts = DB::table('tbl_ledgers')->where('trans_from', $params['from'])->where('trans_to', $params['to'])->count();
        if($ledgerCounts){
            $response['status'] = "Posted";
            foreach($accountTypes as $types){
                $subTotal = 0;
                $accounts = DB::table('tbl_chart_of_accounts')->where('type',$types)->orderBy('code')->get();
                    //->whereNot('parent','0')->orderBy('code')->get();
                $accounts = $accounts->map(function ($accounts) use($params, $tax, $debitCredit, $types){
                    $endingBalance = 0;
                    $endingLedger = DB::table('tbl_ledgers')->where('acc_code', $accounts->code)
                        ->where('trans_from', $params['from'])
                        ->where('trans_to', $params['to'])->first();
                    if($endingLedger)  $endingBalance = $endingLedger->ending_balance;
                    // TODO : Display Contra Accounts as negative values
                    if($debitCredit[$types] !== $accounts->normal_balance){
                        $endingBalance = $endingBalance * -1;
                    }
                    return $this->createFinancialAccount($accounts,$endingBalance,$tax);
                });
                foreach($accounts as $value){
                    //TODO : Display Contra Accounts as negative values
                    //if($debitCredit[$types] !== $value['normal_balance']) $subTotal = $subTotal - $value['amount'];
                    //else $subTotal = $subTotal +  $value['amount'];
                    $subTotal = $subTotal +  $value['amount'];
                }
                $res['normalBalance'] = $debitCredit[$types];
                $res['accounts'] = $accounts;
                $res['total'] = $subTotal;
                $res['totalDisplay'] = $subTotal ? formatAccountingCurrency((float)$subTotal) : '';
                $accountData[$types] = $res;
            }
        }
        else {
            $response['status'] = "Not Posted";
            foreach($accountTypes as $types){
                $subTotal = 0;
                $accounts = DB::table('tbl_chart_of_accounts')->where('type',$types)->orderBy('code')->get();
                    //->whereNot('parent','0')->orderBy('code')->get();
                $accounts = $accounts->map(function ($accounts) use($params, $tax, $debitCredit, $types){
                    $endingBalance = 0;
                    $endingLedger = DB::table('tbl_general_ledgers')->where('acc_code', $accounts->code)
                        //->whereRaw("(select value from tbl_ledger_details where tbl_ledger_details.ledger_id = tbl_general_ledgers.id and tbl_ledger_details.key = 'branch') = 'HOF'")
                        ->where('status', 'posted')->where('posted_date', '<=', $params['to'].' 23:59:59')
                        ->orderByDesc('code')->first();
                    if($endingLedger)  $endingBalance = $endingLedger->ending_balance;
                    // TODO : Display Contra Accounts as negative values
                    if($debitCredit[$types] !== $accounts->normal_balance){
                        $endingBalance = $endingBalance * -1;
                    }

                    return $this->createFinancialAccount($accounts,$endingBalance,$tax);
                });
                foreach($accounts as $value){
                    //TODO : Display Contra Accounts as negative values
                    //if($debitCredit[$types] !== $value['normal_balance']) $subTotal = $subTotal - $value['amount'];
                    //else $subTotal = $subTotal +  $value['amount'];
                    $subTotal = $subTotal +  $value['amount'];
                }
                $res['normalBalance'] = $debitCredit[$types];
                $res['accounts'] = $accounts;
                $res['total'] = $subTotal;
                $res['totalDisplay'] = $subTotal ? formatAccountingCurrency((float)$subTotal) : '';
                $accountData[$types] = $res;
            }
        }
        $response['accounts'] = $accountData;
        return $response;
    }
    private function parseFinancialStatementSubBook($params, $accountTypes, $tax) : array {
        $response = array();
        $accountData = array();
        $debitCredit = [
            "assets" => "Dr",
            "liabilities" => "Cr",
            "equity" => "Cr",
            "income" => "Cr",
            "expense" => "Dr",
        ];
        $ledgerCounts = DB::table('tbl_ledgers')->where('trans_from', $params['from'])->where('trans_to', $params['to'])->count();
        if($ledgerCounts){
            $response['status'] = "Posted";
            foreach($accountTypes as $types){
                $subTotal = 0;
                $accounts = DB::table('tbl_chart_of_accounts')->where('type',$types)->orderBy('code')->get();
                    //->whereNot('parent','0')->orderBy('code')->get();
                $accounts = $accounts->map(function ($accounts) use($params, $tax, $debitCredit, $types){
                    $branches = $this->getBranches();
                    //$branches = "('MKI')";
                    $endingBalance = 0;
                    $beginningBalance = 0;
                    $beginningLedger = DB::table('tbl_ledgers')->where('acc_code', $accounts->code)
                        ->where('trans_from', $params['from'])
                        ->where('trans_to', $params['to'])->first();
                    if($beginningLedger)  $beginningBalance = $beginningLedger->ending_balance;
                    // TODO: Get Total Amount from beginning to ending of posted dates
                    $ledgerValues = DB::table('tbl_general_ledgers')->where('acc_code', $accounts->code)
                        ->whereRaw("(select value from tbl_ledger_details where tbl_ledger_details.ledger_id = tbl_general_ledgers.id and tbl_ledger_details.key = 'branch') = 'HOF'")
                        ->where('status', 'posted')->where('posted_date', '<=', $params['to'].' 23:59:59')
                        ->orderByDesc('code')->sum('amount');
                    if($ledgerValues) $endingBalance = $beginningBalance + $ledgerValues;
                    // TODO : Display Contra Accounts as negative values
                    if($debitCredit[$types] !== $accounts->normal_balance){
                        $endingBalance = $endingBalance * -1;
                    }
                    return $this->createFinancialAccount($accounts,$endingBalance,$tax);
                });
                foreach($accounts as $value){
                    //TODO : Display Contra Accounts as negative values
                    //if($debitCredit[$types] !== $value['normal_balance']) $subTotal = $subTotal - $value['amount'];
                    //else $subTotal = $subTotal +  $value['amount'];
                    $subTotal = $subTotal +  $value['amount'];
                }
                $res['normalBalance'] = $debitCredit[$types];
                $res['accounts'] = $accounts;
                $res['total'] = $subTotal;
                $res['totalDisplay'] = $subTotal ? formatAccountingCurrency((float)$subTotal) : '';
                $accountData[$types] = $res;
            }
        }
        else {
            $response['status'] = "Not Posted";
            foreach($accountTypes as $types){
                $subTotal = 0;
                $accounts = DB::table('tbl_chart_of_accounts')->where('type',$types)->orderBy('code')->get();
                    //->whereNot('parent','0')->orderBy('code')->get();
                $accounts = $accounts->map(function ($accounts) use($params, $tax, $debitCredit, $types){
                    $branches = $this->getBranches();
                    //$branches = "('MKI')";
                    $beginningBalance = 0;
                    $endingBalance = 0;
                    $beginningLedger = DB::table('tbl_ledgers')->where('acc_code', $accounts->code)->orderByDesc('trans_to')->first();
                    if($beginningLedger)  $beginningBalance = $beginningLedger->ending_balance;
                    $ledgerSum = DB::table('tbl_general_ledgers')->where('acc_code', $accounts->code)
                        ->whereRaw("(select value from tbl_ledger_details where tbl_ledger_details.ledger_id = tbl_general_ledgers.id and tbl_ledger_details.key = 'branch') in (".$branches.")")
                        ->where('status', 'posted')->where('posted_date', '<=', $params['to'].' 23:59:59')
                        ->orderByDesc('code')->sum('amount');
                    if($ledgerSum)  $endingBalance = $beginningBalance + $ledgerSum;
                    // TODO : Display Contra Accounts as negative values
                    if($debitCredit[$types] !== $accounts->normal_balance){
                        $endingBalance = $endingBalance * -1;
                    }

                    return $this->createFinancialAccount($accounts,$endingBalance,$tax);
                });
                foreach($accounts as $value){
                    //TODO : Display Contra Accounts as negative values
                    //if($debitCredit[$types] !== $value['normal_balance']) $subTotal = $subTotal - $value['amount'];
                    //else $subTotal = $subTotal +  $value['amount'];
                    $subTotal = $subTotal +  $value['amount'];
                }
                $res['normalBalance'] = $debitCredit[$types];
                $res['accounts'] = $accounts;
                $res['total'] = $subTotal;
                $res['totalDisplay'] = $subTotal ? formatAccountingCurrency((float)$subTotal) : '';
                $accountData[$types] = $res;
            }
        }
        $response['accounts'] = $accountData;
        return $response;
    }
    //TODO: generate "as of" Current financial standing - from general ledger table
    private function createFinancialAccount($accounts,$endingBalance,$tax) : array {
        $account['level'] = 'node-'.$accounts->level;
        $account['customCode'] = $accounts->parent !== '0' ? $accounts->custom_code : '';
        $account['accTitle'] = $accounts->title;
        $account['normal_balance'] = $accounts->normal_balance;
        $account['amount'] = (float)$endingBalance;
        $account['amountValue'] = $account['amount'] != 0 ? formatAccountingCurrency($account['amount']) : '';
        // TODO : FOR TAX PROVISION - Remove if not used / Final Tax Payable
        if($accounts->code === $this->taxProvisionAccount){
            $account['amount'] = $tax;
            $account['amountValue'] = $tax !== 0 ? formatAccountingCurrency($tax) : '';
        }
        return $account;
    }
    private function createPostingEntries($accounts, $params, $beginningBalance, $endingBalance) : array {
        $account['acc_code'] = $accounts->code;
        $account['custom_code'] = $accounts->custom_code;
        $account['acc_title'] = $accounts->title;
        $account['period'] = $params['period'];
        $account['posted_date'] = date('Y-m-d H:i:s');
        $account['trans_from'] = $params['from'];
        $account['trans_to'] = $params['to'];
        $account['beginning_balance'] = $beginningBalance;
        $account['ending_balance'] = $endingBalance;
        $account['normal_balance'] = $accounts->normal_balance;
        $account['status'] = 1;
        return $account;
    }
    private function generateIncomeStatement($params) : array {
        $response = array();
        $accountTypes = ["income","expense"];
        if($this->isSubBook) $parseData = $this->parseFinancialStatementSubBook($params, $accountTypes, 0);
        else $parseData = $this->parseFinancialStatements($params, $accountTypes, 0);
        $accountData = $parseData['accounts'];
        $taxRate = 30;
        $netIncome = $accountData['income']['total'] - $accountData['expense']['total'];
        if($netIncome > 0) $taxProvision = round(($netIncome * ($taxRate / 100)), 2);
        else $taxProvision = 0;
        $netIncomeAfter = $netIncome - $taxProvision;

        $totals['net'] = formatAccountingCurrency($netIncome);
        $totals['tax_rate'] = $taxRate.'%';
        $totals['tax'] = $taxProvision > 0 ? formatAccountingCurrency($taxProvision) : '';
        $totals['net_tax'] = formatAccountingCurrency($netIncomeAfter);
        $amounts['net'] = $netIncome;
        $amounts['tax'] = $taxProvision;
        $amounts['net_tax'] = $netIncomeAfter;
        $response['list'] = $accountData;
        $response['totals'] = $totals;
        $response['amounts'] = $amounts;
        $response['params'] = $params;
        $response['status'] = $parseData['status'];
        $this->createIncomeStatementExcel($accountData, $totals);
        return $response;
    }
    private function generateBalanceSheet($params) : array {
        $response = array();
        $incomeStatement = $this->generateIncomeStatement($params);
        $accountTypes = ["assets","liabilities","equity"];
        if($this->isSubBook) $parseData = $this->parseFinancialStatementSubBook($params, $accountTypes, $incomeStatement['amounts']['tax']);
        else $parseData = $this->parseFinancialStatements($params, $accountTypes, $incomeStatement['amounts']['tax']);
        $accountData = $parseData['accounts'];
        // TODO: Tagging for Tax Provision to balance sheet
        // TODO : Retained Earnings Mapping Here - Manually tagged account code (Can be done thru setup)
        $retainedEarningsEnding = DB::table('tbl_general_ledgers')->where('acc_code', $this->retainedEarningsAccount)
            ->where('transaction_date','<=', $params['to'])->orderByDesc('code')->first();
        // TODO: Change to net if no Tax Provision to balance sheet
        $netIncome = $incomeStatement['amounts']['net_tax'];
        $retainedEarnings = $retainedEarningsEnding ? $retainedEarningsEnding->ending_balance : 0;
        $retainedEarnings = $retainedEarnings + $netIncome;
        $totalAssets = $accountData['assets']['total'];
        $totalEquity = $accountData['equity']['total'] + $netIncome;
        $totalEquityLiability = $totalEquity + $accountData['liabilities']['total'];
        $difference = $totalAssets - $totalEquityLiability;
        $response['data'] = $parseData;
        $response['list'] = $accountData;
        $response['totals']['net_income'] = formatAccountingCurrency($netIncome);
        $response['totals']['retained_earnings'] = formatAccountingCurrency($retainedEarnings);
        $response['totals']['asset'] = formatAccountingCurrency($totalAssets);
        $response['totals']['equity'] = formatAccountingCurrency($totalEquity);
        $response['totals']['equity_liability'] = formatAccountingCurrency($totalEquityLiability);
        $response['totals']['variance_nb'] = (float)$difference != 0 ? ($difference > 0 ? '(Dr)' : '(Cr)') : '';
        $response['totals']['variance'] = $difference ? number_format(abs($difference),2,'.',',') : '-';
        $response['params'] = $params;
        $response['status'] = $parseData['status'];
        $this->createBalanceSheetExcel($accountData, $response['totals']);
        return $response;
    }
    private function generateTrialBalance($dates) {
        $response = DB::table('tbl_chart_of_accounts')->orderBy('code')->get();
        return $response->map(function ($response) use($dates) {
            $details['level'] = 'node-'.$response->level;
            $details['code'] = $response->code;
            $details['customCode'] = $response->custom_code;
            $details['parentCode'] = $response->parent;
            $details['accTitle'] = $response->title;
            $ending = DB::table('tbl_general_ledgers')->where('acc_code', $response->code)
                ->where('transaction_date','<=', $dates[1])->orderByDesc('code')->first();
            $balance = $ending ? $ending->ending_balance != 0 ? formatAccountingCurrency($ending->ending_balance) : '' : '';
            $details['normal_balance'] = $response->normal_balance;
            $details['amount'] = $ending ? $ending->ending_balance : 0;
            $details['debit'] = "";
            $details['credit'] = "";
            if($response->normal_balance === 'Dr') $details['debit'] = $balance;
            else $details['credit'] = $balance;
            return $details;
        });
    }
    private function additionalFields() : void {
        if($this->action === 'dbInsert') {
            $values = $this->values;
            $this->parent = null;
            try {
                if ($values['parent'] === '0') {
                    $values['node'] = 1;
                    $values['status'] = 1;
                    $values['level'] = 1;
                }
                else {
                    $parent = DB::table('tbl_chart_of_accounts')->select()->where('code', $values['parent'])->first();
                    $accounts = DB::table('tbl_chart_of_accounts')->select()->where('parent', $values['parent'])->orderByDesc('code')->first();
                    if ($accounts) {
                        $this->parent = $accounts->parent;
                        $values['level'] = (int)$accounts->level;
                    }
                    else {
                        $this->parent = $parent->code;
                        $values['level'] = $parent->level + 1;
                    }
                    $values['node'] = 1;
                    $values['status'] = 1;
                }
                $this->values = $values;
            } catch (\Exception) {
                $this->values = null;
            }
        }
    }
    private function generateAccountCode($parentCode) : ?array {

        $parent = DB::table('tbl_chart_of_accounts')->select()->where('code', $parentCode)->first();
        $accounts = DB::table('tbl_chart_of_accounts')->select()->where('parent', $parentCode)->orderByDesc('code')->first();
        if ($accounts) {
            $customParent = DB::table('tbl_chart_of_accounts')->select()->where('code', $parentCode)->first();
            $response['normal_balance'] = $accounts->normal_balance;
            $response['code'] = $accounts->code;
            $response['type'] = $accounts->type;
            $response['custom_parent_code'] = $customParent->custom_code;
            $level = (int)$accounts->level;
        } else {
            $response['normal_balance'] = $parent->normal_balance;
            $response['code'] = $parent->code;
            $response['type'] = $parent->type;
            $response['custom_parent_code'] = $parent->custom_code;
            $level = $parent->level + 1;
        }
        $response['code'] = $this->codeGenerator($response['code'] , $level);
        return $response;
    }
    private function getResponse($action) : ?JsonResponse{
        $this->filterTable = 'coa';
        $this->filterResponse();
        if($this->response) {
            if ($action === 'dbInsert') {
                $this->updateParentNode($this->parent);
                return successResponse("Success", ["account_code" => $this->accountCode]);
            }
            else if ($action === 'dbGetAll') {
                $response = $this->response;
                $response = $response->map(function ($response){
                    $parentCode = DB::table('tbl_chart_of_accounts')->select('custom_code')->where('code', $response->parent)->first();
                    $details['customCode'] = $response->custom_code;
                    $details['customParentCode'] = $parentCode ? $parentCode->custom_code : '-';
                    $details['code'] = $response->code;
                    $details['parentCode'] = $response->parent;
                    $details['accTitle'] = $response->title;
                    $details['accType'] = $response->type;
                    $details['normalBalance'] = $response->normal_balance;
                    $details['status'] = (bool)$response->status;
                    $details['order'] = $response->order;
                    // TODO: Query beginning and ending balance by date range
                    $beginning = DB::table('tbl_general_ledgers')->where('acc_code', $response->code)->orderByDesc('code')->first();
                    $ending = DB::table('tbl_general_ledgers')->where('acc_code', $response->code)->orderByDesc('code')->first();
                    $details['beginning'] = $beginning ? number_format($beginning->beginning_balance,2,'.',',') : '0.00';
                    $details['ending'] = $ending ?  number_format($ending->ending_balance,2,'.',',') : '0.00';
                    return $details;
                });
                return successResponse("Success", $response);
            }
            else if ($action === 'dbUpdate') {
                $response = $this->response;
                return successResponse("Success", $response);
            }
            else return successResponse("Success", $this->response);
        }
        else return successResponse("No Data Found!", $this->response);
    }
    private function createBalanceSheetExcel($accounts, $totals) : void {

        $assets['codes'] = array();
        $assets['titles'] = array();
        $assets['value'] = array();
        $liabilities['codes'] = array();
        $liabilities['titles'] = array();
        $liabilities['value'] = array();
        $equity['codes'] = array();
        $equity['titles'] = array();
        $equity['value'] = array();
        foreach($accounts as $key => $account){
            if($key === 'assets'){
                foreach($account['accounts'] as $value){
                    $assets['codes'][] = $value['customCode'];
                    $assets['titles'][] = $value['accTitle'];
                    $assets['value'][] = $value['amountValue'];
                }
                $assets['total'] = $account['totalDisplay'];
            }
            else if($key === 'liabilities'){
                foreach($account['accounts'] as $value){
                    $liabilities['codes'][] = $value['customCode'];
                    $liabilities['titles'][] = $value['accTitle'];
                    $liabilities['value'][] = $value['amountValue'];
                }
                $liabilities['total'] = $account['totalDisplay'];
            }
            else if($key === 'equity'){
                foreach($account['accounts'] as $value){
                    $equity['codes'][] = $value['customCode'];
                    $equity['titles'][] = $value['accTitle'];
                    $equity['value'][] = $value['amountValue'];
                }
                $equity['total'] = $account['totalDisplay'];
            }
        }

        $events = [];
        $callbacks = [];
        $params['[assets_code]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $assets['codes']);
        $params['[assets_title]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $assets['titles']);
        $params['[assets_balance]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $assets['value']);
        $params['{assets_total}'] = $assets['total'];

        $params['[liabilities_code]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $liabilities['codes']);
        $params['[liabilities_title]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $liabilities['titles']);
        $params['[liabilities_balance]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $liabilities['value']);
        $params['{liabilities_total}'] = $liabilities['total'];

        $params['[equity_code]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $equity['codes']);
        $params['[equity_title]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $equity['titles']);
        $params['[equity_balance]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $equity['value']);


        $params['{net_income}'] = $totals['net_income'];
        $params['{retained_earnings}'] = $totals['retained_earnings'];
        $params['{equity_total}'] = $totals['equity'];
        $params['{equity_liability}'] = $totals['equity_liability'];


        $templatePath = public_path("template/balance_sheet_template.xlsx");
        $spreadsheet = IOFactory::load($templatePath);
        //$templateSheetNames = $spreadsheet->getSheetNames();
        Excel::saveToFile($templatePath, "balance_sheet.xlsx", $params, $callbacks, $events);

    }
    private function createIncomeStatementExcel($accounts, $totals) : void {

        $income['codes'] = array();
        $income['titles'] = array();
        $income['value'] = array();
        $expense['codes'] = array();
        $expense['titles'] = array();
        $expense['value'] = array();
        foreach($accounts as $key => $account){
            if($key === 'income'){
                foreach($account['accounts'] as $value){
                    $income['codes'][] = $value['customCode'];
                    $income['titles'][] = $value['accTitle'];
                    $income['value'][] = $value['amountValue'];
                }
                $income['total'] = $account['totalDisplay'];
            }
            else if($key === 'expense'){
                foreach($account['accounts'] as $value){
                    $expense['codes'][] = $value['customCode'];
                    $expense['titles'][] = $value['accTitle'];
                    $expense['value'][] = $value['amountValue'];
                }
                $expense['total'] = $account['totalDisplay'];
            }
        }

        $events = [];
        $callbacks = [];
        $params['[income_code]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $income['codes']);
        $params['[income_title]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $income['titles']);
        $params['[income_balance]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $income['value']);
        $params['{income_total}'] = $income['total'];

        $params['[expense_code]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $expense['codes']);
        $params['[expense_title]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $expense['titles']);
        $params['[expense_balance]'] = new ExcelParam(SPECIAL_ARRAY_TYPE, $expense['value']);
        $params['{expense_total}'] = $expense['total'];

        $params['{net_before}'] = $totals['net'];
        $params['{tax_rate}'] = $totals['tax_rate'];
        $params['{tax}'] = $totals['tax'];
        $params['{net_after}'] = $totals['net_tax'];

        $templatePath = public_path("template/income_statement_template.xlsx");
        Excel::saveToFile($templatePath, "income_statement.xlsx", $params, $callbacks, $events);

    }
    private function getBranches() : ?string {
        $br = DB::table('tbl_operations')->where('type','branch')->pluck('code');
        $branches = "";
        foreach($br as $key => $branch){
            if($key < (sizeof($br) - 1)) $branches = $branches."'".$branch."',";
            if($key === (sizeof($br) - 1)) $branches = $branches."'".$branch."'";
            else $branches = $branches."'".$branch."',";
        }
        return $branches;
    }

}
