809 lines
45 KiB
PHP
809 lines
45 KiB
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
|
|
}
|
|
|
|
|
|
|
|
//////
|
|
}
|