<?php
ini_set("display_errors", 1);


// ouputs opening balance as on mindate, closing balance as on max date, transaction_info 
// either both offset and limit or limit all == no offset
// get Ledger Opening Bal Specific Date
// then check for limit
//  -- if not limit all
//      -- then fetch sum of transactions before offset entries , create as opening for the record
//      -- then fetch all transactions from offset to limit, prepare records
//      -- then get closingBalance on Specific 
//  -- if limit all
//      -- ledger_opening = opening for record
//      -- then fetch all transactions (no offset no limit)
//      -- then closingBalance is last balance
//

function displayTransaction($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $mindate, $maxdate, $limit, $offset, $narration_bool, $book_info,$transactiontype)
{
    $currency_format = $book_info['book_basic_info']['currency_format'];
    $number_of_decimal_places = $book_info['book_basic_info']['number_of_decimal_places'];
    $base_currency_symbol = $book_info['book_basic_info']['base_currency_symbol'];
    $book_opening_date = $book_info['book_basic_info']['book_opening_date'];

    //getting total rows from transactionrecords
    $sql_total = "Select count(*) as total_rows from act_transactionrecord where ledgerid = ? and workspace_id = ? and company_id =? and branch_id =? and book_id  = ? and date >= ? and date <= ? ";
    $stmt_total =  $specific_db_conn->prepare($sql_total);
    $stmt_total->execute(array($ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $mindate, $maxdate));
    $result_total = $stmt_total->fetchAll();

    $totalrows = $result_total[0]['total_rows'];

    $data = [];

    $debit_total = 0.00;
    $credit_total = 0.00;

    $debit_transactions_total = 0;
    $credit_transactions_total = 0;

    $transaction_opening_balance = 0;
    $closing_transaction_balance = 0;

    // getting transaction_settings
    // fetching book settings to identify if numbering is automatic or manual or automatic_override_manual
    $financial_year_startdate = $book_info['book_basic_info']['financial_year_startdate'];
    $sql = "Select transaction_numbering_system, prefix_financial_year, additional_transaction_prefix from act_book_settings where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ?";
    $fetch = $specific_db_conn->prepare($sql);
    $fetch->execute(array($workspace_id, $company_id, $branch_id, $book_id));
    $result_settings = $fetch->fetchAll();
    if (count($result_settings) == 1) {
        $transaction_numbering_system = json_decode($result_settings[0]['transaction_numbering_system'], true);
        $prefix_financial_year_bool = $result_settings[0]['prefix_financial_year'];
        $additional_transaction_prefix = $result_settings[0]['additional_transaction_prefix'];
    } else {
        // require_once '../../global_settings/transaction_settings/function_renumber_automatic_transaction.php';
        require_once  $GLOBALS['base_path'] .'modules/accounting/global_settings/transaction_settings/function_renumber_automatic_transaction.php';
        $transaction_numbering_system = array("manual" => [], "automatic" => $transactiontypes_array_settings);
        $prefix_financial_year_bool = 0;
        $additional_transaction_prefix = '';
    }

    //getting ledger opening balance
    $ledger_opening_bal = fetchLedgerOpeningBalanceSpecificDate($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, null, $mindate, $book_opening_date);

    $closing_balance = 0; // calculated at later stage


    // preparing query
    // getting other details for the transactions
    $sql_get_pair_rows = "Select ledgerid, amount, balancetype from act_transactionrecord where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and transaction_id = ? and ledgerid != ? order by amount desc, balancetype asc limit 3";
    $get_pair_rows = $specific_db_conn->prepare($sql_get_pair_rows);




    if ($limit != 'all') {
        // getting the sum of transactionrecords before this offset 
        if ($offset > 0) {
            $sql_get_transactions_openingbalance = "SELECT COALESCE(SUM(CASE WHEN balancetype = 'dr' THEN amount ELSE 0 END), 0) AS debit,
            COALESCE(SUM(CASE WHEN balancetype = 'cr' THEN amount ELSE 0 END), 0) AS credit
            FROM 
            ( 
                SELECT tr.amount as amount, tr.balancetype as balancetype from act_transactionrecord tr JOIN act_transactiondata td ON tr.transaction_id = td.id  where tr.workspace_id = ? and tr.company_id = ? and tr.branch_id = ? and tr.book_id = ? 
                and tr.ledgerid = ? and tr.date >= ? and tr.date <= ? ORDER BY tr.date, td.time asc LIMIT $offset
            ) subquery";
            

            $get_transactions_openingbalance = $specific_db_conn->prepare($sql_get_transactions_openingbalance);
            $get_transactions_openingbalance->execute(array($workspace_id, $company_id, $branch_id, $book_id, $ledgerid, $mindate, $maxdate));
            $result_get_transactions_openingbalance = $get_transactions_openingbalance->fetchAll();
            $transaction_opening_balance = $ledger_opening_bal + $result_get_transactions_openingbalance[0]['debit'] - $result_get_transactions_openingbalance[0]['credit'];

            $closing_transaction_balance = $transaction_opening_balance;

            $data[] = array(
                'transaction_id' => null,
                'unique_id' => null,
                'particulars' => "Opening Balance",
                'child_records' => [],
                'amount' => abs($transaction_opening_balance),
                'balancetype' => $transaction_opening_balance >= 0 ? 'dr' : 'cr',
                'date' => $mindate,
                'transactiontype' => null,
                'transactionLock' => null,
                'narration' => null,
                'closing_amount' => '',
                'closing_balancetype' => ''
            );
        } else {
            $transaction_opening_balance = $ledger_opening_bal;

            $closing_transaction_balance = $transaction_opening_balance;

            $data[] = array(
                'transaction_id' => null,
                'unique_id' => null,
                'particulars' => "Opening Balance",
                'child_records' => [],
                'amount' => $transaction_opening_balance,
                'balancetype' => $transaction_opening_balance >= 0 ? 'dr' : 'cr',
                'date' => $mindate,
                'transactiontype' => null,
                'transactionLock' => null,
                'narration' => null,
                'closing_amount' => '',
                'closing_balancetype' => ''
            );
        }

        $sql = "SELECT tr.amount as amount, tr.balancetype as balancetype, tr.date as date, tr.transaction_id , td.transactiontype as transactiontype, td.transactionLock as transactionLock, td.financial_year, td.unique_id, td.transaction_type_suffix";
        $narration_bool == 1 ? $sql .= ", td.narration as narration" : $sql .= "";
        $sql = $sql . " from act_transactionrecord tr JOIN act_transactiondata td ON tr.transaction_id = td.id  where tr.workspace_id = ? and tr.company_id = ? and tr.branch_id = ? and tr.book_id = ? and tr.ledgerid = ? and tr.date >= ? and tr.date <= ? ";
        $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $ledgerid, $mindate, $maxdate];
        //transaction type filter
        if ($transactiontype == 'all') {
            $sql .= "";
        } else if ($transactiontype !=  null  && $transactiontype != 'all') {
            $sql .= " AND td.transactiontype = ? ";
            $array_params[] = $transactiontype;
        }
        $sql .= "ORDER BY tr.date, td.time asc LIMIT $offset, $limit";
        
        $fetch_transactions = $specific_db_conn->prepare($sql);
        
        
        $fetch_transactions->execute($array_params);
        $result_transactions = $fetch_transactions->fetchAll();
        foreach ($result_transactions as $temp_transactions) {

            $unique_id =  getTransactionUniqueId($additional_transaction_prefix, $transaction_numbering_system, $temp_transactions['unique_id'], $temp_transactions['transactiontype'], $prefix_financial_year_bool, $temp_transactions['date'], $financial_year_startdate);

            $amount = $temp_transactions['amount'];
            $balancetype = $temp_transactions['balancetype'];
            $date = $temp_transactions['date'];
            $transactiontype = $temp_transactions['transactiontype'];
            $transactionLock = $temp_transactions['transactionLock'];
            if ($narration_bool == true) {
                $narration = $temp_transactions['narration'];
            } else {
                $narration = null;
            }
            $transaction_id = $temp_transactions['transaction_id'];

            $particulars = null;
            $child_records = [];

            // getting other details for the transactions
            $get_pair_rows->execute(array($workspace_id, $company_id, $branch_id, $book_id, $transaction_id, $ledgerid));
            $result_get_pair_rows = $get_pair_rows->fetchAll();
            if (count($result_get_pair_rows) == 1) {
                $particulars = getLedgerName($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $result_get_pair_rows[0]['ledgerid']);
            } else {
                $particulars = 'as per details';
                foreach ($result_get_pair_rows as $temp_pair_rows) {
                    $child_records[] = array(
                        'name' => getLedgerName($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $temp_pair_rows['ledgerid']),
                        'amount' => $temp_pair_rows['amount'],
                        'balancetype' => ucwords($temp_pair_rows['balancetype'])
                    );
                }
            }

            if ($balancetype == 'dr') {
                $closing_transaction_balance += $amount;
            } else $closing_transaction_balance -= $amount;

            $data[] = array(
                'transaction_id' => $transaction_id,
                'unique_id' => $unique_id,
                'particulars' => $particulars,
                'child_records' => $child_records,
                'amount' => $amount,
                'balancetype' => $balancetype,
                'date' => $date,
                'transactiontype' => $transactiontype,
                'transactionLock' => $transactionLock,
                'narration' => $narration,
                'closing_amount' => abs($closing_transaction_balance),
                'closing_balancetype' => $closing_transaction_balance > 0 ? 'Dr' : 'Cr'
            );

            if ($balancetype == 'dr') {
                $debit_transactions_total += $amount;
            } else {
                $credit_transactions_total += $amount;
            }
        }

        // get closing balance
        $closing_balance = fetchLedgerClosingBalanceSpecificDate($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $maxdate);

        // getting the debit and credit total of all transactions fromm mindate to maxdate 
        $sql_get_debit_credit_total = "SELECT COALESCE(SUM(CASE WHEN balancetype = 'dr' THEN amount ELSE 0 END), 0) AS debit,
        COALESCE(SUM(CASE WHEN balancetype = 'cr' THEN amount ELSE 0 END), 0) AS credit
        FROM 
        ( 
            Select SUM(amount) as amount, balancetype from act_transactionrecord where workspace_id = ? and company_id = 
            ? and branch_id = ? and book_id = ? and ledgerid = ? and date >= ? and date <= ? group by balancetype
        ) subquery";
        $get_debit_credit_total = $specific_db_conn->prepare($sql_get_debit_credit_total);
        $get_debit_credit_total->execute(array($workspace_id, $company_id, $branch_id, $book_id, $ledgerid, $mindate, $maxdate));
        $result_debit_credit_total = $get_debit_credit_total->fetchAll();
        $debit_total = $result_debit_credit_total[0]['debit'];
        $credit_total = $result_debit_credit_total[0]['credit'];
    } else {
        $transaction_opening_balance = $ledger_opening_bal;
        $closing_transaction_balance = $ledger_opening_bal;

        $data[] = array(
            'transaction_id' => null,
            'unique_id' => null,
            'particulars' => "Opening Balance",
            'child_records' => [],
            'amount' => $transaction_opening_balance,
            'balancetype' => $transaction_opening_balance >= 0 ? 'dr' : 'cr',
            'date' => $mindate,
            'transactiontype' => null,
            'transactionLock' => null,
            'narration' => null,
            'closing_amount' => '',
            'closing_balancetype' => ''
        );

        $sql = "SELECT tr.amount as amount, tr.balancetype as balancetype, tr.date as date, tr.transaction_id , td.transactiontype as transactiontype, td.transactionLock as transactionLock, td.financial_year, td.unique_id, td.transaction_type_suffix";
        $narration_bool == 1 ? $sql .= ", td.narration as narration" : $sql .= "";
        $sql = $sql . " from act_transactionrecord tr JOIN act_transactiondata td ON tr.transaction_id = td.id  where tr.workspace_id = ? and tr.company_id = ? and tr.branch_id = ? and tr.book_id = ? and tr.ledgerid = ? and tr.date >= ? and tr.date <= ? ORDER BY tr.date, td.time asc";
        $fetch_transactions = $specific_db_conn->prepare($sql);
        $fetch_transactions->execute(array($workspace_id, $company_id, $branch_id, $book_id, $ledgerid, $mindate, $maxdate));
        $result_transactions = $fetch_transactions->fetchAll();
        foreach ($result_transactions as $temp_transactions) {

            if (!empty($additional_transaction_prefix) or !empty($temp_transactions['financial_year'])) {
                $unique_id = $additional_transaction_prefix . '' . $temp_transactions['financial_year'] . '/' . $temp_transactions['unique_id'] . '' . $temp_transactions['transaction_type_suffix'];
            } else {
                $unique_id = $temp_transactions['unique_id'];
            }

            $amount = $temp_transactions['amount'];
            $balancetype = $temp_transactions['balancetype'];
            $date = $temp_transactions['date'];
            $transactiontype = $temp_transactions['transactiontype'];
            $transactionLock = $temp_transactions['transactionLock'];
            if ($narration_bool == true) {
                $narration = $temp_transactions['narration'];
            } else {
                $narration = null;
            }

            $transaction_id = $temp_transactions['transaction_id'];

            $particulars = null;
            $child_records = [];

            // getting other details for the transactions
            $get_pair_rows->execute(array($workspace_id, $company_id, $branch_id, $book_id, $transaction_id, $ledgerid));
            $result_get_pair_rows = $get_pair_rows->fetchAll();
            if (count($result_get_pair_rows) == 1) {
                $particulars = getLedgerName($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $result_get_pair_rows[0]['ledgerid']);
            } else {
                $particulars = 'as per details';
                foreach ($result_get_pair_rows as $temp_pair_rows) {
                    $child_records[] = array(
                        'name' => getLedgerName($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $temp_pair_rows['ledgerid']),
                        'amount' => $temp_pair_rows['amount'],
                        'balancetype' => ucwords($temp_pair_rows['balancetype'])
                    );
                }
            }

            if ($balancetype == 'dr') {
                $closing_transaction_balance += $amount;
            } else $closing_transaction_balance -= $amount;

            $data[] = array(
                'transaction_id' => $transaction_id,
                'unique_id' => $unique_id,
                'particulars' => $particulars,
                'child_records' => $child_records,
                'amount' => $amount,
                'balancetype' => $balancetype,
                'date' => $date,
                'transactiontype' => $transactiontype,
                'transactionLock' => $transactionLock,
                'narration' => $narration,
                'closing_amount' => abs($closing_transaction_balance),
                'closing_balancetype' => $closing_transaction_balance > 0 ? 'Dr' : 'Cr'
            );

            if ($balancetype == 'dr') {
                $debit_total += $amount;
                $debit_transactions_total += $amount;
            } else {
                $credit_total += $amount;
                $credit_transactions_total += $amount;
            }
        }

        // get closing balance
        $closing_balance = $closing_transaction_balance;
    }


    $stats = array(
        "opening" =>  displayAmount($ledger_opening_bal),
        'closing_total' => array('debit_total' => formatCurrency($debit_total, $currency_format, $number_of_decimal_places, null), 'credit_total' => formatCurrency($credit_total, $currency_format, $number_of_decimal_places, null)),
        'transaction_total' => array('debit_total' => formatCurrency($debit_transactions_total, $currency_format, $number_of_decimal_places, false), 'credit_total' => formatCurrency($credit_transactions_total, $currency_format, $number_of_decimal_places, false)),
        "closing" => displayAmount($closing_balance),
    );


    // need total_rows
    // transaction_info
    // total_balance as (opening, current(debit_balance, credit_balance), closing)
    return array('total_rows' => $totalrows, 'transaction_info' => $data, "stats" => $stats);
}
