<?php
// used only in balancesheet only


// required to call the bs_groups_structure and connect file

// this file just gives the net report for bs_ group_structure
/*
array(
    'bs_group_name' => 'Current Assets',
    'children' => [
        // list of ledgerheads for current assets
    ],
    'default'=> 'dr'
);
*/

/*
get ledgerheads
-- if ledgerhead == group_name_bs
    -- get ledgers count if less than 10 then get their group_summary total of debit, total of credit, total net : else get total for ledher
-- if ledgerhead != group_name_bs ()
    -- get summary direct for total_ledgerhead, total of debit, total of credit, total net
*/

// getting the count of ledgers


// i need ledgerid, ledgername, max(date), closing_balance, from these tables where ledgerhead = ?, workspa........ group by ledgerid limit 11

// for getting ledger summary
$sql_get_ledger_summary = "SELECT
ld.ledger_name,
lb.ledgerid,
lb.closing_balance
FROM
act_ledgerbalances lb
INNER JOIN (
SELECT
    lb1.ledgerid AS ledgerid,
    MAX(lb1.date) AS max_date
FROM
    act_ledgerbalances lb1
WHERE
    lb1.workspace_id = ?
    AND lb1.company_id = ?
    AND lb1.branch_id = ?
    AND lb1.book_id = ?
    AND lb1.main_ledgerhead = ?
    AND lb1.date <= ?
GROUP BY
    lb1.ledgerid
LIMIT 11
) AS abc
ON
lb.ledgerid = abc.ledgerid
AND lb.date = abc.max_date
INNER JOIN act_ledgerdetails ld ON ld.id = lb.ledgerid;
";
$get_ledger_summary = $specific_db_conn->prepare($sql_get_ledger_summary);

// get ledgerhead summary
// $sql_get_sum_ledgerheads = "SELECT SUM(Case when closing_balance < 0 THEN closing_balance ELSE 0 END) as credit_closing_balance, SUM(Case when closing_balance > 0 THEN closing_balance ELSE 0 END) as debit_closing_balance 
// FROM act_ledgerbalances where (date, ledgerid) IN 
//     (Select MAX(date), ledgerid from act_ledgerbalances where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and main_ledgerhead = ? and date <= ? group by ledgerid) ";
$sql_get_sum_ledgerheads = "SELECT SUM(closing_balance) as closing_balance 
FROM act_ledgerbalances where (date, ledgerid) IN 
    (Select MAX(date), ledgerid from act_ledgerbalances where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and main_ledgerhead = ? and date <= ? group by ledgerid) ";
$get_sum_ledgerheads = $specific_db_conn->prepare($sql_get_sum_ledgerheads);

function getBSGroupSummary($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $maxdate, $bs_group_name)
{

    // getting the children of the asked group_name
    $summary = [];
    $total_closing = 0.00;
    $clickable = null;
    $bs_type = null;
    foreach ($GLOBALS['default_bs_groups'] as $bs_group_selected) {
        if ($bs_group_selected['bs_group_name'] == $bs_group_name) {
            $clickable = $bs_group_selected['clickable'];
            $bs_type = $bs_group_selected['type'];

            // traversing through each child ledgerhead
            foreach ($bs_group_selected['children'] as $ledgerhead) {

                // checking if ledgerhead == group_name
                if ($ledgerhead == $bs_group_name) {
                    // get the count of ledgers
                    $GLOBALS['get_ledger_summary']->execute(array($workspace_id, $company_id, $branch_id, $book_id, $ledgerhead, $maxdate));
                    $result_get_ledger_summary = $GLOBALS['get_ledger_summary']->fetchAll();
                    if (count($result_get_ledger_summary) > 10) {
                        $get_ledgerhead_sum_bool = 1;
                    } else $get_ledgerhead_sum_bool = 0;
                } else {
                    $get_ledgerhead_sum_bool = 1;
                }

                if ($get_ledgerhead_sum_bool == 1) {

                    // directly get the sum of the child ledgerhead
                    $GLOBALS['get_sum_ledgerheads']->execute(array($workspace_id, $company_id, $branch_id, $book_id, $ledgerhead, $maxdate));
                    $result_sum_ledgerheads = $GLOBALS['get_sum_ledgerheads']->fetchAll();

                    if ($result_sum_ledgerheads[0]['closing_balance'] != 0 and $result_sum_ledgerheads[0]['closing_balance'] != null) {
                        // only add if not 0
                        $summary[] = array('type' => 'group', 'clickable' => true, 'name' => $ledgerhead, 'net_closing_balance' => $result_sum_ledgerheads[0]['closing_balance']);

                        $total_closing += $result_sum_ledgerheads[0]['closing_balance'];
                    }
                } else {
                    // get the ledgers


                    foreach ($result_get_ledger_summary as $row_temp_ledgers) {

                        if ($row_temp_ledgers['closing_balance'] != 0) {

                            $summary[] = array('type' => 'ledger', 'clickable' => true, 'ledger_id' => $row_temp_ledgers['ledgerid'], 'name' => $row_temp_ledgers['ledger_name'], 'net_closing_balance' => $row_temp_ledgers['closing_balance']);

                            $total_closing += $row_temp_ledgers['closing_balance'];
                        }
                    }
                }
            }
            break;
        }
    }
    return array('bs_group_name' => $bs_group_name, 'data' => $summary, 'total_closing' => $total_closing, 'clickable' => $clickable, 'type' => $bs_type);
}
