<?php

ini_set('display_errors', 1);


/*

1. Check if the group is main ledgerhead
    -- true
        -- go and fetch all immediate child groups 
            // recursively get their children group sum
            // get sum of all ledgers direct children of the main ledgerhead that are direct children
    -- false
        -- Check if the group exists in group_name
            -- false give error
            --true
                -- go and fetch all immediate child groups 
                // recursively get their children group sum
                // get sum of all ledgers direct children of the main ledgerhead that are direct children

                select MAX(b.date), l.ledgerid, l.ledger_name, b.closing_balance 
from act_ledger_details l, act_ledgerbalances b
where b.ledgerid=l.ledgerid 
and l.workspace_id=?
and l.branch_id=?
and l.book_id=?
and l.ledger_parent_group=?
and b.date <= ?


*/

$sql_ngs_validate_main_ledgerhead = "Select ledgerhead_name from act_ledgerheads_basic where ledgerhead_name = ?";
$ngs_validate_main_ledgerhead = $specific_db_conn->prepare($sql_ngs_validate_main_ledgerhead);

$sql_ngs_validate_group_name = "Select group_name, main_ledgerhead from act_group_info where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and group_name = ?";
$ngs_validate_group_name = $specific_db_conn->prepare($sql_ngs_validate_group_name);

$sql_ngs_get_child_groups = "Select group_name from act_group_info where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and parent_lvl1 = ? and main_ledgerhead = ?";
$ngs_get_child_groups = $specific_db_conn->prepare($sql_ngs_get_child_groups);

$sql_ngs_get_child_groups_children = "Select group_name from act_group_info where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and (parent_lvl1 = ? or parent_lvl2 = ? and parent_lvl3 = ? and parent_lvl4 = ? and parent_lvl5 = ?)"; // this one is used to create of all ledger groups under the type
$ngs_get_child_groups_children = $specific_db_conn->prepare($sql_ngs_get_child_groups_children);


$sql_ngs_get_ledger_children_nonpl = "SELECT ld.id AS ledgerid, ld.ledger_name as ledger_name, ld.ledger_opening_bal + COALESCE(dr.recordtotal_amount, 0) - COALESCE(cr.total_amount, 0) AS 
ledger_closing FROM act_ledgerdetails ld LEFT JOIN ( SELECT ledgerid, SUM(amount) AS recordtotal_amount FROM act_transactionrecord td WHERE td.date <=  ? and td.date >= ? and  balancetype = 'dr' GROUP BY ledgerid ) dr ON ld.id = dr.ledgerid LEFT JOIN ( SELECT ledgerid, SUM(amount) AS total_amount FROM act_transactionrecord td WHERE td.date <=  ? and td.date >= ? and balancetype = 'cr' GROUP BY ledgerid) cr ON ld.id = cr.ledgerid WHERE ld.workspace_id = ? and ld.company_id =  ? and ld.branch_id = ? and ld.book_id = ? and ld.ledger_parent_group = ?";
$ngs_get_ledger_children_nonpl = $specific_db_conn->prepare($sql_ngs_get_ledger_children_nonpl);

$sql_ngs_get_ledger_children_pl = "SELECT ld.id AS ledgerid, ld.ledger_name as ledger_name, COALESCE(dr.recordtotal_amount, 0) - COALESCE(cr.total_amount, 0) AS ledger_closing FROM act_ledgerdetails ld LEFT JOIN ( SELECT ledgerid, SUM(amount) AS recordtotal_amount FROM act_transactionrecord td WHERE td.date <=  ? and td.date >= ? and  balancetype = 'dr' GROUP BY ledgerid ) dr ON ld.id = dr.ledgerid LEFT JOIN ( SELECT ledgerid, SUM(amount) AS total_amount FROM act_transactionrecord td WHERE td.date <=  ? and td.date >= ? and balancetype = 'cr' GROUP BY ledgerid) cr ON ld.id = cr.ledgerid WHERE ld.workspace_id = ? and ld.company_id =  ? and ld.branch_id = ? and ld.book_id = ? and ld.ledger_parent_group = ?";
$ngs_get_ledger_children_pl = $specific_db_conn->prepare($sql_ngs_get_ledger_children_pl);

// pass aqrray of ledger_parent_group ()


function netGroupSummary($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $input_group_name, $maxdate, $mindate, $book_opening_date)
{

    $data = [];
    $total_closing = 0.00;


    $pl_group_bool = 0; // initiall set to 0;

    $main_ledgerhead_bool = 0; // set to false initially;
    // checking if is main_ledgerhead
    $GLOBALS['ngs_validate_main_ledgerhead']->execute(array($input_group_name));
    $result_validate_main_ledgerhead = $GLOBALS['ngs_validate_main_ledgerhead']->fetchAll();
    if (count($result_validate_main_ledgerhead) == 1) {
        // main ledgerhead
        $main_ledgerhead = $input_group_name;
        $main_ledgerhead_bool = 1;
    } else {
        // not a main ledgerhead
        // validating is the group_exists
        $GLOBALS['ngs_validate_group_name']->execute(array($workspace_id, $company_id, $branch_id, $book_id, $input_group_name));
        $result_validate_group_name = $GLOBALS['ngs_validate_group_name']->fetchAll();
        if (count($result_validate_group_name) != 1) {
            errorreturn("Group does not exist.");
        } else {
            $main_ledgerhead = $result_validate_group_name[0]['main_ledgerhead'];
        }
    }

    // checking if it is a pl sub account or not
    $pl_main_ledgerhead_array = [
        'Income (Direct)', 'Income (Indirect)', 'Expenses (Direct)', 'Expenses (Indirect)', 'Indirect Expenses', 'Purchase Account', 'Sales Account', 'Direct Expenses'
    ];
    if (in_array($main_ledgerhead, $pl_main_ledgerhead_array)) {
        $pl_group_bool = 1;
    }

    //getting the immediate / direct child groups
    if ($main_ledgerhead_bool == 1) {
        $GLOBALS['ngs_get_child_groups']->execute(array($workspace_id, $company_id, $branch_id, $book_id, null, $main_ledgerhead));
    } else {

        $GLOBALS['ngs_get_child_groups']->execute(array($workspace_id, $company_id, $branch_id, $book_id, $input_group_name, $main_ledgerhead));
    }
    $result_nsg_get_child_groups = $GLOBALS['ngs_get_child_groups']->fetchAll();


    // get the sum of this child group of the received input group including recursive group run for children of this specific child row
    foreach ($result_nsg_get_child_groups as $row_child_groups) {

        $GLOBALS['ngs_get_child_groups_children']->execute(array($workspace_id, $company_id, $branch_id, $book_id, $row_child_groups['group_name'], $row_child_groups['group_name'], $row_child_groups['group_name'], $row_child_groups['group_name'], $row_child_groups['group_name']));

        $get_sum_array_child_group = [$row_child_groups['group_name']];
        $ngs_get_child_groups_children_result = $GLOBALS['ngs_get_child_groups_children']->fetchAll();
        foreach ($ngs_get_child_groups_children_result as $row_temp_subgroups) {
            $get_sum_array_child_group[] = $row_temp_subgroups['group_name'];
        }

        $temp_balance_subgroup = 0.00;

        if ($pl_group_bool == 0 or ($pl_group_bool == 1 and $mindate == $book_opening_date)) { // use this cache method of getting ledgerbalance on any specific date only if it is a non_pl_group_bool
            $sql_ngs_get_sum_child_group_nonpl = "Select SUM(closing_balance) as total from act_ledgerbalances where (id, ledgerid, date) IN (Select id, ledgerid, MAX(date) from act_ledgerbalances where date <= ?  and ledgerid IN (Select id from act_ledgerdetails where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and ledger_parent_group IN ('" . implode("', '", $get_sum_array_child_group) . "')) group by ledgerid)";

            $ngs_get_sum_child_group_nonpl = $specific_db_conn->prepare($sql_ngs_get_sum_child_group_nonpl);
            $ngs_get_sum_child_group_nonpl->execute(array($maxdate, $workspace_id, $company_id, $branch_id, $book_id));
            $temp_balance_subgroup = $ngs_get_sum_child_group_nonpl->fetchAll()[0]['total'];

            if ($temp_balance_subgroup != 0.00 and $temp_balance_subgroup != null) {
                $data[] = array('type' => 'group', 'clickable' => true, 'name' => $row_child_groups['group_name'], 'balance' => $temp_balance_subgroup);
            }
        } else {
            // this needs to be written
            // supposing this is a pl so we need to calculate through transaction sums
            // $sql_ngs_get_sum_child_group_pl = " Select SUM(amount) as amount, balancetype from act_transactionrecord where date <= ? and date >= ? ledgerid IN (Select id from act_ledgerdetails where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and ledger_parent_group IN ('" . implode("', '", $get_sum_array_child_group) . "')) group by balancetype)";

            $sql_ngs_get_sum_child_group_pl = "Select SUM(Case when balancetype = 'cr' then -amount else amount end) as total from act_transactionrecord where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and date >= ? and date <=? and ledger_parent_group IN ('" . implode("', '", $get_sum_array_child_group) . "')";


            $ngs_get_sum_child_group_pl = $specific_db_conn->prepare($sql_ngs_get_sum_child_group_pl);
            $ngs_get_sum_child_group_pl->execute(array($workspace_id, $company_id, $branch_id, $book_id, $mindate, $maxdate));
            $temp_balance_subgroup = $ngs_get_sum_child_group_pl->fetchAll()[0]['total'];

            if ($temp_balance_subgroup != 0.00 and $temp_balance_subgroup != null) {
                $data[] = array('type' => 'group', 'clickable' => true, 'name' => $row_child_groups['group_name'], 'balance' => $temp_balance_subgroup);
            }
        }

        $total_closing += $temp_balance_subgroup;
    }


    // get the list of ledgers and their balance that are direct child to input_group
    // sum query is conditional to pl or non pl
    if ($pl_group_bool == 0 or ($pl_group_bool == 1 and $mindate == $book_opening_date)) { // use this cache method of getting ledgerbalance on any specific date only if it is a non_pl_group_bool
        $GLOBALS['ngs_get_ledger_children_nonpl']->execute(array($maxdate, $mindate, $maxdate, $mindate, $workspace_id, $company_id, $branch_id, $book_id, $input_group_name));
        $ngs_get_ledger_children_nonpl_result = $GLOBALS['ngs_get_ledger_children_nonpl']->fetchAll();

        foreach ($ngs_get_ledger_children_nonpl_result as $row_temp_ledgers) {
            $total_closing += $row_temp_ledgers['ledger_closing'];
            // chgecking if last one was 0 then stripping it off
            if ($row_temp_ledgers['ledger_closing'] != 0.00) {
                // push into data
                $data[] = array('type' => 'ledger', 'clickable' => true, 'ledger_id' => $row_temp_ledgers['ledgerid'], 'name' => $row_temp_ledgers['ledger_name'], 'balance' => $row_temp_ledgers['ledger_closing']);
            }
        }
    } else {
        $GLOBALS['ngs_get_ledger_children_pl']->execute(array($maxdate, $mindate, $maxdate, $mindate, $workspace_id, $company_id, $branch_id, $book_id, $input_group_name));

        $ngs_get_ledger_children_pl_result = $GLOBALS['ngs_get_ledger_children_pl']->fetchAll();

        foreach ($ngs_get_ledger_children_pl_result as $row_temp_ledgers) {

            $total_closing += $row_temp_ledgers['ledger_closing'];
            // chgecking if last one was 0 then stripping it off
            if ($row_temp_ledgers['ledger_closing'] != 0.00) {
                // push into data
                $data[] = array('type' => 'ledger', 'clickable' => true, 'ledger_id' => $row_temp_ledgers['ledgerid'], 'name' => $row_temp_ledgers['ledger_name'], 'balance' => $row_temp_ledgers['ledger_closing']);
            }
        }
    }

    return array('status' => 1, 'data' => $data, 'total_closing' => $total_closing);
}
