289 lines
11 KiB
PHP
289 lines
11 KiB
PHP
|
<?php
|
||
|
|
/* NOTES TO ME */
|
||
|
|
/*
|
||
|
|
|
||
|
|
We are developing this library to be used as global for accounting systems for any software we are going to build
|
||
|
|
|
||
|
|
|
||
|
|
Currenyly, in this function we are drawing balances based on selected branch and selected fiscal year.
|
||
|
|
We are supposing branch and fiscalyear is set to session. if no session values are found, we are assuming, fiscalyear is 1 and branch is 1
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
*/
|
||
|
|
defined('BASEPATH') or exit('No direct script access allowed');
|
||
|
|
class acc
|
||
|
|
{
|
||
|
|
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;
|
||
|
|
}
|
||
|
|
function getAccountGroups(&$ACBalances = array())
|
||
|
|
{
|
||
|
|
$CI = &get_instance();
|
||
|
|
$t = "select * from tbl_acgroups where status=1";
|
||
|
|
$AccountGroups = $CI->db->query($t)->result();
|
||
|
|
foreach ($AccountGroups as $AccountGroup) :
|
||
|
|
$AccountGroup->dr = 0;
|
||
|
|
$AccountGroup->cr = 0;
|
||
|
|
$AccountGroup->openingdr = 0;
|
||
|
|
$AccountGroup->openingcr = 0;
|
||
|
|
$ACBalances = array();
|
||
|
|
$TotalBalance = $this->getGroupBalance($AccountGroup->acgroup_id, $ACBalances, 'getThisYearWithOpening');
|
||
|
|
$OpeningBalance = $this->getGroupBalance($AccountGroup->acgroup_id, $ACBalances, 'getOpeningOnly');
|
||
|
|
if ($AccountGroup->posting_side == "DR") {
|
||
|
|
$AccountGroup->dr = ($TotalBalance);
|
||
|
|
$AccountGroup->openingdr = ($OpeningBalance);
|
||
|
|
} else {
|
||
|
|
$AccountGroup->cr = ($TotalBalance);
|
||
|
|
$AccountGroup->openingcr = ($OpeningBalance);
|
||
|
|
}
|
||
|
|
endforeach;
|
||
|
|
return $AccountGroups;
|
||
|
|
}
|
||
|
|
function getAccountCategories($group_id = 0, $condition="showAll")
|
||
|
|
{
|
||
|
|
$ACBalances = array();
|
||
|
|
$CI = &get_instance();
|
||
|
|
if ($group_id == 0)
|
||
|
|
$t = "select * from tbl_accategories where status=1";
|
||
|
|
else
|
||
|
|
$t = "select * from tbl_accategories where status=1 and acgroup_id='$group_id'";
|
||
|
|
if($condition=="onlyParents")
|
||
|
|
{
|
||
|
|
$t.=" AND parent_category_id=0 ";
|
||
|
|
}
|
||
|
|
$AccountCategories = $CI->db->query($t)->result();
|
||
|
|
foreach ($AccountCategories as $AccountCategory) :
|
||
|
|
$AccountCategory->posting_side = getFieldfromValue("tbl_acgroups", "posting_side", "acgroup_id", $AccountCategory->acgroup_id);
|
||
|
|
$AccountCategory->dr = 0;
|
||
|
|
$AccountCategory->cr = 0;
|
||
|
|
$ACBalances = array();
|
||
|
|
$TotalBalance = $this->getCategoryBalance($AccountCategory->accategory_id, $ACBalances);
|
||
|
|
if ($AccountCategory->posting_side == "DR") {
|
||
|
|
$AccountCategory->dr = ($TotalBalance);
|
||
|
|
} else {
|
||
|
|
$AccountCategory->cr = ($TotalBalance);
|
||
|
|
}
|
||
|
|
endforeach;
|
||
|
|
return $AccountCategories;
|
||
|
|
}
|
||
|
|
function getAccountsByCategory($accategory_id)
|
||
|
|
{
|
||
|
|
$accounts = array();
|
||
|
|
$CI = &get_instance();
|
||
|
|
$t = "SELECT * FROM tbl_accategories WHERE accategory_id='$accategory_id'";
|
||
|
|
$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'";
|
||
|
|
$accountQuery = $CI->db->query($t);
|
||
|
|
$accounts = $accountQuery->result_array();
|
||
|
|
$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);
|
||
|
|
}
|
||
|
|
return $accounts;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
function getAccountBalanceByGroup($acgroup_id, $abs = true)
|
||
|
|
{
|
||
|
|
$total = 0;
|
||
|
|
$accounts = $this->getAccountsByGroup($acgroup_id);
|
||
|
|
foreach ($accounts as $account) {
|
||
|
|
if ($abs)
|
||
|
|
$total += (abs($account->balance));
|
||
|
|
else
|
||
|
|
$total += ($account->balance);
|
||
|
|
}
|
||
|
|
return $total;
|
||
|
|
}
|
||
|
|
function getAccountsByGroup($acgroup_id)
|
||
|
|
{
|
||
|
|
$CI = &get_instance();
|
||
|
|
$query = $CI->db->select('a.*')
|
||
|
|
->from('tbl_accounts a')
|
||
|
|
->join('tbl_accategories c', 'c.accategory_id = a.accategory_id')
|
||
|
|
->where('a.status', 1)
|
||
|
|
->where('c.acgroup_id', $acgroup_id)
|
||
|
|
->get();
|
||
|
|
$accounts = $query->result();
|
||
|
|
$ACBalances = array();
|
||
|
|
foreach ($accounts as $account) {
|
||
|
|
|
||
|
|
$account->balance = $this->getAccountBalance($account->account_id, $ACBalances);
|
||
|
|
}
|
||
|
|
|
||
|
|
return $accounts;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
function getGroupBalance($group_id, &$ACBalances, $condition = 'getAll')
|
||
|
|
{
|
||
|
|
/*
|
||
|
|
When $condition is set to 'getOpeningOnly', the function retrieves only the opening balances (where voucher_id = 0).
|
||
|
|
When $condition is set to 'getThisYear', the function retrieves balances for the current fiscal year (where voucher_id <> 0 and fiscalyear_id = $this->FY).
|
||
|
|
When $condition is set to 'getThisYearWithOpening', the function retrieves balances for the current fiscal year, including opening balances (where fiscalyear_id = $this->FY).
|
||
|
|
When $condition is set to 'getAll' and $this->FY is not set, the function retrieves overall balances (where voucher_id <> 0).
|
||
|
|
*/
|
||
|
|
$ci = &get_instance();
|
||
|
|
$t = "SELECT * FROM tbl_accounts WHERE accategory_id IN (SELECT accategory_id FROM tbl_accategories AS a WHERE a.acgroup_id = '$group_id' AND a.status = 1) AND status <> -1";
|
||
|
|
$Accounts = $ci->db->query($t)->result();
|
||
|
|
$DrBalance = 0;
|
||
|
|
$CrBalance = 0;
|
||
|
|
foreach ($Accounts as $Account) {
|
||
|
|
$t = "SELECT * FROM tbl_voucherdetails WHERE account_id = '$Account->account_id' AND status <> -1";
|
||
|
|
|
||
|
|
// Condition: Opening Only
|
||
|
|
if ($condition == 'getOpeningOnly') {
|
||
|
|
$t .= " AND voucher_id = 0";
|
||
|
|
}
|
||
|
|
|
||
|
|
// Condition: This Year
|
||
|
|
if ($condition == 'getThisYear') {
|
||
|
|
$t .= " AND voucher_id <> 0 AND fiscalyear_id = " . $this->FY;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Condition: This Year with Opening
|
||
|
|
if ($condition == 'getThisYearWithOpening') {
|
||
|
|
$t .= " AND fiscalyear_id = " . $this->FY;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Condition: Overall
|
||
|
|
if ($condition == 'getAll' && !isset($this->FY)) {
|
||
|
|
$t .= " AND voucher_id <> 0";
|
||
|
|
}
|
||
|
|
|
||
|
|
$t .= " AND branch_id = " . $this->Branch;
|
||
|
|
|
||
|
|
$Vouchers = $ci->db->query($t)->result();
|
||
|
|
foreach ($Vouchers as $Voucher) {
|
||
|
|
$DrBalance += $Voucher->dr;
|
||
|
|
$CrBalance += $Voucher->cr;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$ACBalances['DrBalance'] = $DrBalance;
|
||
|
|
$ACBalances['CrBalance'] = $CrBalance;
|
||
|
|
$ACBalances['Balance'] = ($DrBalance > $CrBalance) ? ($DrBalance - $CrBalance) : ($CrBalance - $DrBalance);
|
||
|
|
return $ACBalances['Balance'];
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
function getCategoryBalance($accategory_id, &$ACBalances)
|
||
|
|
{
|
||
|
|
$ci = &get_instance();
|
||
|
|
// $t = "SELECT * FROM tbl_accounts WHERE status=1 and accategory_id ='$accategory_id' or accategory_id in (select accategory_id from tbl_categories where parent_category_id='$accategory_id')";
|
||
|
|
// $Accounts = $ci->db->query($t)->result();
|
||
|
|
// $groupBalance = 0;
|
||
|
|
$DrBalance = 0;
|
||
|
|
$CrBalance = 0;
|
||
|
|
$Accounts = $this->getAccountsByCategory($accategory_id);
|
||
|
|
foreach ($Accounts as $Account) {
|
||
|
|
// $t = "SELECT * FROM tbl_voucherdetails WHERE account_id = '$Account->account_id' AND status <> -1";
|
||
|
|
$t = "SELECT * FROM tbl_voucherdetails WHERE account_id = '" . $Account['account_id'] . "' AND status <> -1";
|
||
|
|
$Vouchers = $ci->db->query($t)->result();
|
||
|
|
foreach ($Vouchers as $Voucher) {
|
||
|
|
$DrBalance += $Voucher->dr;
|
||
|
|
$CrBalance += $Voucher->cr;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$ACBalances['DrBalance'] = $DrBalance;
|
||
|
|
$ACBalances['CrBalance'] = $CrBalance;
|
||
|
|
$ACBalances['Balance'] = ($DrBalance > $CrBalance) ? ($DrBalance - $CrBalance) : ($CrBalance - $DrBalance);
|
||
|
|
return $ACBalances['Balance'];
|
||
|
|
}
|
||
|
|
function getAccountBalance($account_id, &$ACBalances)
|
||
|
|
{
|
||
|
|
$ci = &get_instance();
|
||
|
|
$t = "SELECT * FROM tbl_voucherdetails WHERE account_id = '$account_id' AND status <> -1";
|
||
|
|
$Vouchers = $ci->db->query($t)->result();
|
||
|
|
$DrBalance = 0;
|
||
|
|
$CrBalance = 0;
|
||
|
|
foreach ($Vouchers as $Voucher) {
|
||
|
|
$DrBalance += $Voucher->dr;
|
||
|
|
$CrBalance += $Voucher->cr;
|
||
|
|
}
|
||
|
|
$ACBalances['DrBalance'] = $DrBalance;
|
||
|
|
$ACBalances['CrBalance'] = $CrBalance;
|
||
|
|
$ACBalances['Balance'] = $DrBalance - $CrBalance;
|
||
|
|
return $ACBalances['Balance'];
|
||
|
|
}
|
||
|
|
|
||
|
|
function showTable($Accounts, $abs = true, $showHead = false)
|
||
|
|
{
|
||
|
|
$total = 0;
|
||
|
|
$accColWidth = "col-9";
|
||
|
|
$balanceColWidth = "col-3";
|
||
|
|
?>
|
||
|
|
<table class="table table-bordered table-hover">
|
||
|
|
<?php if ($showHead) : ?>
|
||
|
|
<thead>
|
||
|
|
<tr>
|
||
|
|
<th class="<?php echo $accColWidth; ?>">Account</th>
|
||
|
|
<th class="<?php echo $balanceColWidth; ?>">Balance</th>
|
||
|
|
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<?php endif; ?>
|
||
|
|
<tbody>
|
||
|
|
<?php foreach ($Accounts as $Account) :
|
||
|
|
if ($abs)
|
||
|
|
$total += abs($Account->balance);
|
||
|
|
else
|
||
|
|
$total += $Account->balance;
|
||
|
|
?>
|
||
|
|
<tr>
|
||
|
|
<td class="<?php echo $accColWidth; ?>"><?php echo $Account->account_name; ?></td>
|
||
|
|
<td class="<?php echo $balanceColWidth; ?>"><?php echo ($abs) ? myCurrency(abs($Account->balance)) : myCurrency($Account->balance); ?></td>
|
||
|
|
</tr>
|
||
|
|
<?php endforeach; ?>
|
||
|
|
|
||
|
|
</tbody>
|
||
|
|
<!-- <tfoot>
|
||
|
|
<tr>
|
||
|
|
<th>Total</th>
|
||
|
|
<th><?php echo myCurrency($total); ?></th>
|
||
|
|
|
||
|
|
</tr>
|
||
|
|
</tfoot> -->
|
||
|
|
</table>
|
||
|
|
|
||
|
|
<?php
|
||
|
|
}
|
||
|
|
}
|