BBnepal-Accounts/account/application/libraries/BIBAccounts1.php

<?php
/* 
                                        NOTES TO ME



*/
defined('BASEPATH') or exit('No direct script access allowed');
class bibaccounts
{
    private $FY;
    private $Branch;
    public function __construct()
    {
        $CI = &get_instance(); // Get the CodeIgniter instance

        $CI->load->library('session'); // Assuming CodeIgniter's session library is already loaded

        // Check if Branch is set in session
        if ($CI->session->has_userdata('BranchID')) {
            $this->Branch = $CI->session->userdata('BranchID');
        } else {
            $this->Branch = 1; // Default value if not found in session
        }

        // Check if FY is set in session
        if ($CI->session->has_userdata('FiscalYearID')) {
            $this->FY = $CI->session->userdata('FiscalYearID');
        } else {
            $this->FY = 1; // Default value if not found in session
        }
        // echo $this->FY;die;
    }
    /**
     * Retrieves the account groups with balances, optionally filtered by group ID.
     *
     * This function queries the database to fetch the account groups from the "tbl_acgroups" table and calculates the totals of balances for each group by summing the account balances within each group. If the optional "acgroup_id" parameter is provided, the function returns only the group with the specified ID. Otherwise, it returns an array of stdClass objects representing each account group with the corresponding balance totals.
     *
     * @param int|null $acgroup_id (Optional) The ID of the account group to retrieve balances for.
     * @return array|stdClass|null An array of stdClass objects representing the account groups with balance totals, or a single stdClass object for the specified group ID, or null if no group is found with the provided ID.
     */
    function getAccountGroupsWithBalances($acgroup_id = null)
    {
        $CI = &get_instance();
        $result = [];

        $whereClause = "";
        if ($acgroup_id !== null) {
            $whereClause = "WHERE acgroup_id = '$acgroup_id' AND status=1";
        }

        $t = "SELECT * FROM tbl_acgroups $whereClause";
        $accountGroups = $CI->db->query($t)->result();

        foreach ($accountGroups as $group) {
            $t = "SELECT * FROM tbl_accounts 
              WHERE (accategory_id IN (SELECT accategory_id FROM tbl_accategories WHERE acgroup_id = '{$group->acgroup_id}') 
                     OR accategory_id IN (SELECT parent_category_id FROM tbl_accategories WHERE accategory_id IN (SELECT accategory_id FROM tbl_accategories WHERE acgroup_id = '{$group->acgroup_id}')))
                    AND status <> -1";

            $accounts = $CI->db->query($t)->result();

            $group->dr_total = 0;
            $group->cr_total = 0;
            $group->regular_balance_dr = 0;
            $group->regular_balance_cr = 0;
            $group->opening_balance_dr = 0;
            $group->opening_balance_cr = 0;

            foreach ($accounts as $account) {
                $accountBalances = $this->getAccountBalances($account->account_id);
                //    pre($accountBalances);die;
                $group->opening_balance_dr += $accountBalances['opening_balance_dr'];
                $group->opening_balance_cr += $accountBalances['opening_balance_cr'];
                $group->dr_total += $accountBalances['dr_total'];
                $group->cr_total += $accountBalances['cr_total'];
                $group->regular_balance_dr += $accountBalances['regular_balance_dr'];
                $group->regular_balance_cr += $accountBalances['regular_balance_cr'];
            }
            if (
                $group->dr_total > 0 &&
                $group->cr_total > 0 &&
                $group->regular_balance_dr > 0 &&
                $group->regular_balance_cr > 0 &&
                $group->opening_balance_dr > 0 &&
                $group->opening_balance_cr > 0
            ) $group->isZero = 1;
            else $group->isZero = 0;
            $group->opening_balance = ($group->posting_side == "DR") ? $group->opening_balance_dr - $group->opening_balance_cr : $group->opening_balance_cr - $group->opening_balance_dr;
            $group->closing_balance = ($group->posting_side == "DR") ? $group->dr_total - $group->cr_total : $group->cr_total - $group->dr_total;
            $group->regular_balance = ($group->posting_side == "DR") ? $group->regular_balance_dr - $group->regular_balance_cr : $group->regular_balance_cr - $group->regular_balance_dr;

            $group->accounts = $accounts;
            $result[] = $group;
        }
        // pre($result);die;
        return $result;
    }


    function getAccountCategoriesWithBalances($acgroup_id = null)
    {
        $CI = &get_instance();
        $result = [];

        $whereClause = "";
        if ($acgroup_id !== null) {
            $whereClause = "WHERE acgroup_id = '$acgroup_id' AND status=1";
        }

        $t = "SELECT * FROM tbl_accategories $whereClause";
        $categories = $CI->db->query($t)->result();

        foreach ($categories as $category) {
            $t = "SELECT * FROM tbl_accounts 
                  WHERE accategory_id = '{$category->accategory_id}'
                  AND status <> -1";

            $accounts = $CI->db->query($t)->result();
            $category->isParent = ($CI->db->query("SELECT * FROM tbl_accategories WHERE parent_category_id = '" . $category->accategory_id . "'")->num_rows() > 0) ? true : false;
            $category->dr_total = 0;
            $category->cr_total = 0;
            $category->regular_balance_dr = 0;
            $category->regular_balance_cr = 0;
            $category->opening_balance_dr = 0;
            $category->opening_balance_cr = 0;

            foreach ($accounts as $account) {
                $accountBalances = $this->getAccountBalances($account->account_id);
                $category->opening_balance_dr += $accountBalances['opening_balance_dr'];
                $category->opening_balance_cr += $accountBalances['opening_balance_cr'];
                $category->dr_total += $accountBalances['dr_total'];
                $category->cr_total += $accountBalances['cr_total'];
                $category->regular_balance_dr += $accountBalances['regular_balance_dr'];
                $category->regular_balance_cr += $accountBalances['regular_balance_cr'];
            }
            $category->posting_side = $CI->db->query("SELECT posting_side FROM tbl_acgroups WHERE acgroup_id=(SELECT acgroup_id FROM tbl_accategories WHERE accategory_id='" . $category->accategory_id . "')")->row()->posting_side;

            $category->opening_balance = ($category->posting_side == "DR") ? $category->opening_balance_dr - $category->opening_balance_cr : $category->opening_balance_cr - $category->opening_balance_dr;
            $category->closing_balance = ($category->posting_side == "DR") ? $category->dr_total - $category->cr_total : $category->cr_total - $category->dr_total;
            $category->regular_balance = ($category->posting_side == "DR") ? $category->regular_balance_dr - $category->regular_balance_cr : $category->regular_balance_cr - $category->regular_balance_dr;

            $category->accounts = $accounts;
            $result[] = $category;
        }

        return $result;
    }

    function getAccountsByCategory($accategory_id)
    {
        $accounts = array();
        $CI = &get_instance();
        $t = "SELECT * FROM tbl_accategories WHERE accategory_id='$accategory_id' and status=1";
        $AccountCategory = $CI->db->query($t)->row();
        if ($AccountCategory) {
            $accounts = $this->getAccountsRecursive($AccountCategory);
        }
        return $accounts;
    }

    function getAccountsRecursive($category)
    {
        $CI = &get_instance();
        $t = "SELECT * FROM tbl_accounts WHERE accategory_id='$category->accategory_id' and status=1";
        $accountQuery = $CI->db->query($t);
        $accounts = $accountQuery->result();
        $t = "SELECT * FROM tbl_accategories WHERE parent_category_id='$category->accategory_id'";
        $subcategories = $CI->db->query($t)->result();
        foreach ($subcategories as $subcategory) {
            $subcategoryAccounts = $this->getAccountsRecursive($subcategory);
            $accounts = array_merge($accounts, $subcategoryAccounts);
        }
        // Remove duplicate accounts based on account_id
        $uniqueAccounts = array_column($accounts, null, 'account_id');
        return array_values($uniqueAccounts);
    }

    function getRootCategoriesWithBalances($acgroup_id = null)
    {
        $CI = &get_instance();
        $result = [];

        $whereClause = "WHERE parent_category_id = '0' AND status = '1'";
        if ($acgroup_id !== null) {
            $whereClause .= " AND acgroup_id = '$acgroup_id'";
        }

        $t = "SELECT * FROM tbl_accategories $whereClause";
        $categories = $CI->db->query($t)->result();

        foreach ($categories as $category) {
            $accounts = $this->getAccountsByCategory($category->accategory_id);
            $category->isParent = ($CI->db->query("SELECT * FROM tbl_accategories WHERE parent_category_id = '" . $category->accategory_id . "'")->num_rows() > 0) ? true : false;
            $category->dr_total = 0;
            $category->cr_total = 0;
            $category->regular_balance_dr = 0;
            $category->regular_balance_cr = 0;
            $category->opening_balance_dr = 0;
            $category->opening_balance_cr = 0;

            foreach ($accounts as $account) {
                // pre($account);
                $accountBalances = $this->getAccountBalances($account->account_id);
                $category->opening_balance_dr += $accountBalances['opening_balance_dr'];
                $category->opening_balance_cr += $accountBalances['opening_balance_cr'];
                $category->dr_total += $accountBalances['dr_total'];
                $category->cr_total += $accountBalances['cr_total'];
                $category->regular_balance_dr += $accountBalances['regular_balance_dr'];
                $category->regular_balance_cr += $accountBalances['regular_balance_cr'];
            }
            $category->posting_side = $CI->db->query("SELECT posting_side FROM tbl_acgroups WHERE acgroup_id=(SELECT acgroup_id FROM tbl_accategories WHERE accategory_id='" . $category->accategory_id . "')")->row()->posting_side;

            $category->opening_balance = ($category->posting_side == "DR") ? $category->opening_balance_dr - $category->opening_balance_cr : $category->opening_balance_cr - $category->opening_balance_dr;
            $category->closing_balance = ($category->posting_side == "DR") ? $category->dr_total - $category->cr_total : $category->cr_total - $category->dr_total;
            $category->regular_balance = ($category->posting_side == "DR") ? $category->regular_balance_dr - $category->regular_balance_cr : $category->regular_balance_cr - $category->regular_balance_dr;
            $category->isZero = ($category->dr_total == 0 && $category->cr_total == 0 && $category->opening_balance_dr == 0 && $category->opening_balance_cr == 0 && $category->regular_balance_dr == 0 && $category->regular_balance_cr == 0) ? 1 : 0;

            $category->accounts = $accounts;
            $result[] = $category;
        }

        return $result;
    }


    function getChildCategoriesWithBalances($accategory_id)
    {
        $CI = &get_instance();
        $result = [];

        $t = "SELECT * FROM tbl_accategories WHERE parent_category_id = '$accategory_id' AND status=1";
        $categories = $CI->db->query($t)->result();

        foreach ($categories as $category) {
            $t = "SELECT * FROM tbl_accounts 
                  WHERE accategory_id = '{$category->accategory_id}'
                  AND status <> -1";

            $accounts = $CI->db->query($t)->result();
            $category->isParent = ($CI->db->query("SELECT * FROM tbl_accategories WHERE parent_category_id = '" . $category->accategory_id . "'")->num_rows() > 0) ? true : false;
            $category->dr_total = 0;
            $category->cr_total = 0;
            $category->regular_balance_dr = 0;
            $category->regular_balance_cr = 0;
            $category->opening_balance_dr = 0;
            $category->opening_balance_cr = 0;

            foreach ($accounts as $account) {
                $accountBalances = $this->getAccountBalances($account->account_id);
                $category->opening_balance_dr += $accountBalances['opening_balance_dr'];
                $category->opening_balance_cr += $accountBalances['opening_balance_cr'];
                $category->dr_total += $accountBalances['dr_total'];
                $category->cr_total += $accountBalances['cr_total'];
                $category->regular_balance_dr += $accountBalances['regular_balance_dr'];
                $category->regular_balance_cr += $accountBalances['regular_balance_cr'];
            }
            $category->posting_side = $CI->db->query("SELECT posting_side FROM tbl_acgroups WHERE acgroup_id=(SELECT acgroup_id FROM tbl_accategories WHERE accategory_id='" . $category->accategory_id . "')")->row()->posting_side;

            $category->opening_balance = ($category->posting_side == "DR") ? $category->opening_balance_dr - $category->opening_balance_cr : $category->opening_balance_cr - $category->opening_balance_dr;
            $category->closing_balance = ($category->posting_side == "DR") ? $category->dr_total - $category->cr_total : $category->cr_total - $category->dr_total;
            $category->regular_balance = ($category->posting_side == "DR") ? $category->regular_balance_dr - $category->regular_balance_cr : $category->regular_balance_cr - $category->regular_balance_dr;

            $category->isZero = ($category->dr_total == 0 && $category->cr_total == 0 && $category->opening_balance_dr == 0 && $category->opening_balance_cr == 0 && $category->regular_balance_dr == 0 && $category->regular_balance_cr == 0) ? 1 : 0;

            $category->accounts = $accounts;
            $result[] = $category;
        }


        return $result;
    }


    /**
     * Retrieves the categories with balances that fall under the provided account group ID and optionally filters by parent categories.
     *
     * This function queries the database to fetch the categories from the "tbl_accategories" table associated with the provided account group ID (`acgroup_id`) and calculates the totals of balances for each category by summing the account balances within each category. If the `$acgroup_id` is not specified, it fetches all categories. It returns an array of stdClass objects representing the categories with their respective balance totals.
     *
     * @param int|null $acgroup_id Optional. The ID of the account group to retrieve categories and balances for. If not specified, it fetches categories from all groups.
     * @param bool $onlyParents Optional. Specifies whether to include only parent categories (categories with parent_category_id = 0). Default is false.
     * @return array An array of stdClass objects representing the categories with balance totals under the specified account group ID or all groups if not specified.
     */
    function getCategoriesWithBalances($acgroup_id = null, $onlyParents = false)
    {
        $CI = &get_instance();
        $t = "SELECT * FROM tbl_accategories";

        if ($acgroup_id !== null) {
            $t .= " WHERE acgroup_id='$acgroup_id'";
        }

        if ($onlyParents) {
            $t .= " AND parent_category_id = 0";
        }

        $categories = $CI->db->query($t)->result();
        $result = [];

        foreach ($categories as $category) {
            $category->accounts = $this->generateAccountsByCategory($category->accategory_id); // Call the generateAccountsByCategory function to retrieve the accounts with balances

            // Calculate category balances based on the account balances
            $category->dr_total = 0;
            $category->cr_total = 0;
            $category->dr_balance = 0;
            $category->cr_balance = 0;
            $category->opening_balance_dr = 0;
            $category->opening_balance_cr = 0;
            $category->regular_balance_dr = 0;
            $category->regular_balance_cr = 0;
            $category->total_balance_dr = 0;
            $category->total_balance_cr = 0;

            foreach ($category->accounts as $account) {
                $balances = $this->getAccountBalances($account->account_id);

                $category->dr_total += $balances['dr_total'];
                $category->cr_total += $balances['cr_total'];
                $category->dr_balance += $balances['dr_balance'];
                $category->cr_balance += $balances['cr_balance'];

                $category->opening_balance_dr += $balances['opening_balance_dr'];
                $category->opening_balance_cr += $balances['opening_balance_cr'];
                $category->regular_balance_dr += $balances['regular_balance_dr'];
                $category->regular_balance_cr += $balances['regular_balance_cr'];
                $category->total_balance_dr += $balances['total_balance_dr'];
                $category->total_balance_cr += $balances['total_balance_cr'];
            }

            $result[] = $category;
        }

        return $result;
    }
    /**
     * Retrieves the child categories with balances based on the provided parent category ID.
     *
     * This function queries the database to fetch the child categories from the "tbl_accategories" table associated with the provided parent category ID (`accategory_id`) and includes the balances for each category by calling the `getAccountBalances` function. It returns an array of stdClass objects representing the child categories with their respective balances.
     *
     * @param int $accategory_id The ID of the parent category to retrieve child categories for.
     * @return array An array of stdClass objects representing the child categories with balances under the specified parent category ID.
     */
    function getChildCategories($accategory_id)
    {
        $CI = &get_instance();
        $t = "SELECT * FROM tbl_accategories WHERE parent_category_id='$accategory_id'";

        $childCategories = $CI->db->query($t)->result();

        foreach ($childCategories as $category) {
            $category->accounts = $this->generateAccountsByCategory($category->accategory_id); // Call the generateAccountsByCategory function to retrieve the accounts with balances

            // Initialize category balances
            $category->dr_total = 0;
            $category->cr_total = 0;
            $category->dr_balance = 0;
            $category->cr_balance = 0;
            $category->opening_balance_dr = 0;
            $category->opening_balance_cr = 0;
            $category->regular_balance_dr = 0;
            $category->regular_balance_cr = 0;
            $category->total_balance_dr = 0;
            $category->total_balance_cr = 0;

            foreach ($category->accounts as $account) {
                $accountBalances = $this->getAccountBalances($account->account_id);

                $category->dr_total += $accountBalances['dr_total'];
                $category->cr_total += $accountBalances['cr_total'];
                $category->dr_balance += $accountBalances['dr_balance'];
                $category->cr_balance += $accountBalances['cr_balance'];
                $category->opening_balance_dr += $accountBalances['opening_balance_dr'];
                $category->opening_balance_cr += $accountBalances['opening_balance_cr'];
                $category->regular_balance_dr += $accountBalances['regular_balance_dr'];
                $category->regular_balance_cr += $accountBalances['regular_balance_cr'];
                $category->total_balance_dr += $accountBalances['total_balance_dr'];
                $category->total_balance_cr += $accountBalances['total_balance_cr'];
            }
        }

        return $childCategories;
    }


    /**
     * Generates the account hierarchy based on the provided group ID, fiscal year ID, and branch ID.
     *
     * This function retrieves the accounts and categories associated with the provided group ID (`$group_id`) and generates a hierarchical structure using stdClass objects. It uses recursion to handle any depth of child categories. It includes the account balances in each account object.
     *
     * @param int $group_id The group ID to start the hierarchy generation from.
     * @param string $fiscal_year_id Optional. The fiscal year ID to filter the accounts. Default is an empty string.
     * @param string $branch_id Optional. The branch ID to filter the accounts. Default is an empty string.
     * @return array An array of stdClass objects representing the account hierarchy with account balances.
     */
    function generateAccountsByGroup($group_id, $fiscal_year_id = "", $branch_id = "")
    {
        $CI = &get_instance();
        $t = "SELECT * FROM tbl_accategories WHERE acgroup_id='$group_id'";
        $categories = $CI->db->query($t)->result();

        $result = [];
        foreach ($categories as $category) {
            $category->accounts = $this->generateAccountsByCategory($category->accategory_id, $fiscal_year_id, $branch_id); // Call the generateAccountsByCategory function to retrieve the accounts with balances

            // Calculate category balances based on the account balances
            $category->dr_total = 0;
            $category->cr_total = 0;
            $category->dr_balance = 0;
            $category->cr_balance = 0;
            $category->opening_balance_dr = 0;
            $category->opening_balance_cr = 0;
            $category->regular_balance_dr = 0;
            $category->regular_balance_cr = 0;

            foreach ($category->accounts as $account) {
                $category->dr_total += $account->dr_total;
                $category->cr_total += $account->cr_total;
                $category->dr_balance += $account->dr_balance;
                $category->cr_balance += $account->cr_balance;
                $category->opening_balance_dr += $account->opening_balance_dr;
                $category->opening_balance_cr += $account->opening_balance_cr;
                $category->regular_balance_dr += $account->regular_balance_dr;
                $category->regular_balance_cr += $account->regular_balance_cr;
            }
            //pre($category);die;

            $result[] = $category;
        }

        return $result;
    }




    function generateAccountsByCategory($accategory_id, $fiscal_year_id = "", $branch_id = "")
    {
        $CI = &get_instance();
        $t = "SELECT * FROM tbl_accounts WHERE accategory_id='$accategory_id' and status=1";
        $accounts = $CI->db->query($t)->result();

        $result = [];
        foreach ($accounts as $account) {
            $accountBalances = $this->getAccountBalances($account->account_id, $fiscal_year_id ?: $this->FY, $branch_id ?: $this->Branch);
            $account->dr_total = $accountBalances['dr_total'];
            $account->cr_total = $accountBalances['cr_total'];
            $account->dr_balance = $accountBalances['dr_balance'];
            $account->cr_balance = $accountBalances['cr_balance'];
            $account->opening_balance_dr = $accountBalances['opening_balance_dr'];
            $account->opening_balance_cr = $accountBalances['opening_balance_cr'];
            $account->regular_balance_dr = $accountBalances['regular_balance_dr'];
            $account->regular_balance_cr = $accountBalances['regular_balance_cr'];
            $result[] = $account;
        }

        $t = "SELECT * FROM tbl_accategories WHERE parent_category_id='$accategory_id'";
        $categories = $CI->db->query($t)->result();

        foreach ($categories as $category) {
            $category->accounts = $this->generateAccountsByCategory($category->accategory_id, $fiscal_year_id, $branch_id);

            $category->dr_total = 0;
            $category->cr_total = 0;
            $category->dr_balance = 0;
            $category->cr_balance = 0;
            $category->opening_balance_dr = 0;
            $category->opening_balance_cr = 0;
            $category->regular_balance_dr = 0;
            $category->regular_balance_cr = 0;

            foreach ($category->accounts as $account) {
                $category->dr_total += $account->dr_total;
                $category->cr_total += $account->cr_total;
                $category->dr_balance += $account->dr_balance;
                $category->cr_balance += $account->cr_balance;
                $category->opening_balance_dr += $account->opening_balance_dr;
                $category->opening_balance_cr += $account->opening_balance_cr;
                $category->regular_balance_dr += $account->regular_balance_dr;
                $category->regular_balance_cr += $account->regular_balance_cr;
            }

            $result[] = $category;
        }

        return $result;
    }
    function generateAccountsBySingleCategory($accategory_id, $fiscal_year_id = "", $branch_id = "")
    {
        $CI = &get_instance();
        $t = "SELECT * FROM tbl_accounts WHERE accategory_id='$accategory_id' and status=1";
        $accounts = $CI->db->query($t)->result();

        foreach ($accounts as $account) {
            $accountBalances = $this->getAccountBalances($account->account_id, $fiscal_year_id ?: $this->FY, $branch_id ?: $this->Branch);
            $account->dr_total = $accountBalances['dr_total'];
            $account->cr_total = $accountBalances['cr_total'];
            $account->dr_balance = $accountBalances['dr_balance'];
            $account->cr_balance = $accountBalances['cr_balance'];
            $account->opening_balance_dr = $accountBalances['opening_balance_dr'];
            $account->opening_balance_cr = $accountBalances['opening_balance_cr'];
            $account->regular_balance_dr = $accountBalances['regular_balance_dr'];
            $account->regular_balance_cr = $accountBalances['regular_balance_cr'];

            $account->posting_side = $CI->db->query("SELECT posting_side FROM tbl_acgroups WHERE acgroup_id=(SELECT acgroup_id FROM tbl_accategories WHERE accategory_id='" . $account->accategory_id . "')")->row()->posting_side;

            $account->opening_balance = ($account->posting_side == "DR") ? $account->opening_balance_dr - $account->opening_balance_cr : $account->opening_balance_cr - $account->opening_balance_dr;
            $account->closing_balance = ($account->posting_side == "DR") ? $account->dr_total - $account->cr_total : $account->cr_total - $account->dr_total;
            $account->regular_balance = ($account->posting_side == "DR") ? $account->regular_balance_dr - $account->regular_balance_cr : $account->regular_balance_cr - $account->regular_balance_dr;

            $account->isZero = ($account->dr_total == 0 && $account->cr_total == 0 && $account->opening_balance_dr == 0 && $account->opening_balance_cr == 0 && $account->regular_balance_dr == 0 && $account->regular_balance_cr == 0) ? 1 : 0;
        }

        return $accounts;
    }



    /**
     * Retrieves the balance of an account based on the provided account ID.
     *
     * This function calculates the balance of an account by summing the "dr" and "cr" columns from the "tbl_voucherdetails" table associated with the provided account ID. It also calculates the `dr_balance` and `cr_balance` based on the `posting_side` from the related tables. Additionally, it retrieves the opening balance by considering the records with `voucher_id` equal to 0 as opening balances. It returns an associative array with the balance values.
     *
     * @param int $account_id The account ID to retrieve the balance for.
     * @param int $fiscal_year_id The fiscal year ID to filter the voucher details.
     * @param int $branch_id The branch ID to filter the voucher details.
     * @return array An associative array with the balance of the account, including the total of the "dr" column as 'dr_total', the total of the "cr" column as 'cr_total', the calculated "dr_balance", the calculated "cr_balance", the opening balance as 'opening_balance_dr', the opening balance as 'opening_balance_cr', the regular balance as 'regular_balance_dr', and the regular balance as 'regular_balance_cr'.
     */
    function getAccountBalances($account_id, $fiscal_year_id = "", $branch_id = "")
    {
        $fiscal_year_id = ($fiscal_year_id == "") ? $this->FY : $fiscal_year_id;
        $branch_id = ($branch_id == "") ? $this->Branch : $branch_id;
        $CI = &get_instance();
        $query = $CI->db->query("SELECT SUM(dr) AS dr_total, SUM(cr) AS cr_total FROM tbl_voucherdetails WHERE account_id='$account_id' AND status=1 AND fiscalyear_id='$fiscal_year_id' AND branch_id='$branch_id'");
        $result = $query->row();

        $drTotal = $result->dr_total;
        $crTotal = $result->cr_total;

        $posting_side = $CI->db->query("SELECT posting_side FROM tbl_acgroups WHERE acgroup_id=(SELECT acgroup_id FROM tbl_accategories WHERE accategory_id=(SELECT accategory_id FROM tbl_accounts WHERE account_id='$account_id'))")->row()->posting_side;

        $drBalance = 0;
        $crBalance = 0;

        if ($posting_side == "DR") {
            $drBalance = $drTotal - $crTotal;
        } else {
            $crBalance = $crTotal - $drTotal;
        }

        $openingBalanceQuery = $CI->db->query("SELECT SUM(dr) AS opening_balance_dr, SUM(cr) AS opening_balance_cr FROM tbl_voucherdetails WHERE account_id='$account_id' AND voucher_id=0 AND status=1 AND fiscalyear_id='$fiscal_year_id' AND branch_id='$branch_id'");
        $openingBalanceResult = $openingBalanceQuery->row();

        $openingBalanceDr = $openingBalanceResult->opening_balance_dr;
        $openingBalanceCr = $openingBalanceResult->opening_balance_cr;

        $regularBalanceQuery = $CI->db->query("SELECT SUM(dr) AS regular_balance_dr, SUM(cr) AS regular_balance_cr FROM tbl_voucherdetails WHERE account_id='$account_id' AND voucher_id<>0 AND status=1 AND fiscalyear_id='$fiscal_year_id' AND branch_id='$branch_id'");
        $regularBalanceResult = $regularBalanceQuery->row();

        $regularBalanceDr = $regularBalanceResult->regular_balance_dr;
        $regularBalanceCr = $regularBalanceResult->regular_balance_cr;

        return array(
            'dr_total' => $drTotal,
            'cr_total' => $crTotal,
            'dr_balance' => $drBalance,
            'cr_balance' => $crBalance,
            'opening_balance_dr' => $openingBalanceDr,
            'opening_balance_cr' => $openingBalanceCr,
            'regular_balance_dr' => $regularBalanceDr,
            'regular_balance_cr' => $regularBalanceCr,
        );
    }



    //////
    function renderAccountCategoriesTable($accountCategories, $ReportOptions, $displayHeadings = true, $parentCategories = [])
    {
        if (!isset($ReportOptions['AmountColWidth'])) $ReportOptions['AmountColWidth'] = 180;
?>
        <table class="table table-sm">
            <?php if ($displayHeadings) { ?>
                <thead>
                    <tr>
                        <th>Particulars</th>
                        <?php if ($ReportOptions['showOB']) : ?>
                            <th class="dr opening-dr">Opening (Dr)</th>
                            <th class="cr opening-cr">Opening (Cr)</th>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showPeriod']) : ?>
                            <th class="dr this-year-dr">This Year (Dr)</th>
                            <th class="cr this-year-cr">This Year (Cr)</th>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showClosing']) : ?>
                            <th class="dr closing-dr">Closing (Dr)</th>
                            <th class="cr closing-cr">Closing (Cr)</th>
                        <?php endif; ?>
                    </tr>
                </thead>
            <?php } ?>
            <tbody>
                <?php foreach ($accountCategories as $accountCategory) : ?>

                    <?php if ($ReportOptions['showZeroBalances'] && $accountCategory->isZero == true) continue; ?>
                    <tr class="<?php echo ($accountCategory->isParent) ? 'bg-darker parent-row' : ''; ?>" data-toggle="collapse" data-target="#accategory_<?php echo $accountCategory->accategory_id; ?>">
                        <td class="group-name"><?php echo $accountCategory->accategory_name; ?></td>
                        <?php if ($ReportOptions['showOB']) : ?>
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->opening_balance : 0); ?></td>
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'CR') ? $accountCategory->opening_balance : 0); ?></td>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showPeriod']) : ?>
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->regular_balance : 0); ?></td>
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'CR') ? $accountCategory->regular_balance : 0); ?></td>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showClosing']) : ?>
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->closing_balance : 0); ?></td>
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'CR') ? $accountCategory->closing_balance : 0); ?></td>
                        <?php endif; ?>
                    </tr>
                    <?php if ($accountCategory->isParent == 0) : ?>
                        <?php $Accounts = $this->generateAccountsBySingleCategory($accountCategory->accategory_id); ?>
                        <tr>
                            <td colspan="7">
                                <div id="accategory_<?php echo $accountCategory->accategory_id; ?>" class="collapse">
                                    <?php $this->renderAccountsTable($Accounts, $ReportOptions, false); ?>
                                </div>
                            </td>
                        </tr>
                    <?php endif; ?>

                    <?php if ($accountCategory->isParent == 1) : ?>
                        <?php $Accounts = $this->generateAccountsBySingleCategory($accountCategory->accategory_id); ?>
                        <?php if ($Accounts) : ?>
                            <tr>
                                <td colspan="7">
                                    <div id="accategory_<?php echo $accountCategory->accategory_id; ?>" class="collapse">
                                        <?php $this->renderAccountsTable($Accounts,  $ReportOptions, false); ?>
                                    </div>
                                </td>
                            </tr>
                        <?php endif; ?>
                        <tr class="">
                            <td colspan="7">
                                <div id="accategory_<?php echo $accountCategory->accategory_id; ?>" class="collapse">
                                    <?php $childCategories = $this->getChildCategoriesWithBalances($accountCategory->accategory_id); ?>
                                    <?php $this->renderAccountCategoriesTable($childCategories, $ReportOptions, false, $parentCategories); ?>
                                </div>
                            </td>
                        </tr>

                    <?php endif; ?>
                <?php endforeach; ?>
            </tbody>


        </table>
    <?php
    }
    function renderAccountCategoriesTableForBS($accountCategories, $ReportOptions, $displayHeadings = true, $parentCategories = [])
    {
        if (!isset($ReportOptions['AmountColWidth'])) $ReportOptions['AmountColWidth'] = 180;
    ?>
        <table class="table table-hover table-sm">

            <tbody>

                <?php foreach ($accountCategories as $accountCategory) : ?>

                    <?php if ($ReportOptions['showZeroBalances'] == false && $accountCategory->isZero == true) continue; ?>
                    <tr class="<?php echo ($accountCategory->isParent) ? 'bg-darker parent-row' : ''; ?>" data-toggle="collapse" data-target="#accategory_<?php echo $accountCategory->accategory_id; ?>">
                        <td class="group-name"><?php echo $accountCategory->accategory_name; ?></td>
                        <?php if ($ReportOptions['showOB']) : ?>
                            <!-- <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->opening_balance : 0); ?></td> -->
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'CR') ? $accountCategory->opening_balance : 0); ?> <?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->opening_balance : 0); ?></td>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showPeriod']) : ?>
                            <!-- <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->regular_balance : 0); ?></td> -->
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'CR') ? $accountCategory->regular_balance : 0); ?> <?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->regular_balance : 0); ?></td>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showClosing']) : ?>
                            <!-- <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->closing_balance : 0); ?></td> -->
                            <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($accountCategory->posting_side == 'CR') ? $accountCategory->closing_balance : 0); ?> <?php echo myCurrency(($accountCategory->posting_side == 'DR') ? $accountCategory->closing_balance : 0); ?></td>
                        <?php endif; ?>
                    </tr>
                    <?php if ($accountCategory->isParent == 0) : ?>
                        <?php $Accounts = $this->generateAccountsBySingleCategory($accountCategory->accategory_id); ?>
                        <tr>
                            <td colspan="7">
                                <div id="accategory_<?php echo $accountCategory->accategory_id; ?>" class="collapse">
                                    <?php $this->renderAccountsTableForBS($Accounts, $ReportOptions, false); ?>
                                </div>
                            </td>
                        </tr>
                    <?php endif; ?>

                    <?php if ($accountCategory->isParent == 1) : ?>
                        <?php $Accounts = $this->generateAccountsBySingleCategory($accountCategory->accategory_id); ?>
                        <?php if ($Accounts) : ?>
                            <tr>
                                <td colspan="7">
                                    <div id="accategory_<?php echo $accountCategory->accategory_id; ?>" class="collapse">
                                        <?php $this->renderAccountsTableForBS($Accounts,  $ReportOptions, false); ?>
                                    </div>
                                </td>
                            </tr>
                        <?php endif; ?>
                        <tr class="">
                            <td colspan="7">
                                <div id="accategory_<?php echo $accountCategory->accategory_id; ?>" class="collapse">
                                    <?php $childCategories = $this->getChildCategoriesWithBalances($accountCategory->accategory_id); ?>
                                    <?php $this->renderAccountCategoriesTableForBS($childCategories, $ReportOptions, false, $parentCategories); ?>
                                </div>
                            </td>
                        </tr>

                    <?php endif; ?>
                <?php endforeach; ?>
            </tbody>


        </table>
    <?php
    }

    function renderAccountsTable($Accounts, $ReportOptions, $displayHeadings = false)
    {
        if (!isset($ReportOptions['AmountColWidth'])) $ReportOptions['AmountColWidth'] = 180;
    ?>
        <table class="table table-hover table-gray">
            <?php if ($displayHeadings) { ?>
                <thead>
                    <tr>
                        <th>Particulars</th>
                        <?php if ($ReportOptions['showOB']) : ?>
                            <th class="dr opening-dr">Opening (Dr)</th>
                            <th class="cr opening-cr">Opening (Cr)</th>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showPeriod']) : ?>
                            <th class="dr this-year-dr">This Year (Dr)</th>
                            <th class="cr this-year-cr">This Year (Cr)</th>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showClosing']) : ?>
                            <th class="dr closing-dr">Closing (Dr)</th>
                            <th class="cr closing-cr">Closing (Cr)</th>
                        <?php endif; ?>
                    </tr>
                </thead>
            <?php } ?>
            <tbody>
                <?php foreach ($Accounts as $Account) : ?>
                    <?php if ($ReportOptions['showZeroBalances'] == false && $Account->isZero == true) continue; ?>
                    <tr>
                        <td class="group-name"><?php echo $Account->account_name; ?></td>
                        <?php if ($ReportOptions['showOB']) : ?>
                            <td class="group-total"><?php echo myCurrency(($Account->posting_side == 'DR') ? $Account->opening_balance : 0); ?></td>
                            <td class="group-total"><?php echo myCurrency(($Account->posting_side == 'CR') ? $Account->opening_balance : 0); ?></td>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showPeriod']) : ?>
                            <td class="group-total"><?php echo myCurrency(($Account->posting_side == 'DR') ? $Account->regular_balance : 0); ?></td>
                            <td class="group-total"><?php echo myCurrency(($Account->posting_side == 'CR') ? $Account->regular_balance : 0); ?></td>
                        <?php endif; ?>
                        <?php if ($ReportOptions['showClosing']) : ?>
                            <td class="group-total"><?php echo myCurrency(($Account->posting_side == 'DR') ? $Account->closing_balance : 0); ?></td>
                            <td class="group-total"><?php echo myCurrency(($Account->posting_side == 'CR') ? $Account->closing_balance : 0); ?></td>
                        <?php endif; ?>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>

    <?php
    }
    function renderAccountsTableForBS($Accounts, $ReportOptions, $displayHeadings = false)
    {
        if (!isset($ReportOptions['AmountColWidth'])) $ReportOptions['AmountColWidth'] = 180;
    ?>
        <table class="table table-hover table-gray">


            <?php foreach ($Accounts as $Account) : ?>
                <?php if ($ReportOptions['showZeroBalances'] == false && $Account->isZero == 1) continue; ?>
                <tr>
                    <td class="group-name"><?php echo $Account->account_name; ?></td>
                    <?php if ($ReportOptions['showOB']) : ?>
                        <!-- <td class="group-total"></td> -->
                        <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($Account->posting_side == 'CR') ? $Account->opening_balance : 0); ?><?php echo myCurrency(($Account->posting_side == 'DR') ? $Account->opening_balance : 0); ?></td>
                    <?php endif; ?>
                    <?php if ($ReportOptions['showPeriod']) : ?>
                        <!-- <td class="group-total"></td> -->
                        <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($Account->posting_side == 'CR') ? $Account->regular_balance : 0); ?><?php echo myCurrency(($Account->posting_side == 'DR') ? $Account->regular_balance : 0); ?></td>
                    <?php endif; ?>
                    <?php if ($ReportOptions['showClosing']) : ?>
                        <!-- <td class="group-total"></td> -->
                        <td class="group-total" width="<?php echo $ReportOptions['AmountColWidth']; ?>"><?php echo myCurrency(($Account->posting_side == 'CR') ? $Account->closing_balance : 0); ?><?php echo myCurrency(($Account->posting_side == 'DR') ? $Account->closing_balance : 0); ?></td>
                    <?php endif; ?>
                </tr>
            <?php endforeach; ?>

        </table>

<?php
    }
}