/** @var $result mysqli_result */
require_once '../includes/funcs.inc.php';
$_title = getReportTitle();
if (isset($_GET['report'])) {
updateDate('rpt_cash_movement_start', $_GET['date_start']);
updateDate('rpt_cash_movement_end', $_GET['date_end']);
$data = array();
$tdata = array();
$accounts_classes = array('BANKS' => 'Karachi Region', 'LHR BANKS' => 'Lahore Region');
foreach ($accounts_classes as $accounts_class => $accounts_class_name) {
$sdata = array();
$openingBalance = (double) 0;
$sql = "SELECT coa.account_ref_id FROM chart_of_account coa WHERE coa.account_class = '{$accounts_class}' AND coa.company_ref_id = {$_SESSION['company_id']}";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
$aob = getOpeningBalance($row['account_ref_id'], $_GET['date_start']);
$openingBalance += $aob;
}
$sdata[] = array('account_name' => $accounts_class . " OPEN BAL", 'amount' => $openingBalance);
$runningBalance = (double) 0;
$sql = "" . "SELECT IFNULL(SUM(vd.debit_amount), 0) bal\n" . " FROM voucher_detail vd\n" . " INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id\n" . " WHERE TO_DAYS(vm.voucher_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "')\n" . " AND TO_DAYS(vm.voucher_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "')\n" . " AND vd.account_ref_id IN (SELECT coa.account_ref_id FROM chart_of_account coa WHERE coa.account_class = '{$accounts_class}' AND coa.company_ref_id = vm.company_ref_id)\n" . " AND vm.company_ref_id = {$_SESSION['company_id']}\n";
$result = $mysqli->query($sql);
if ($row = $result->fetch_assoc()) {
$runningBalance += $row['bal'] * 1;
}
$sdata[] = array('account_name' => $accounts_class, 'amount' => $runningBalance);
$sdata[] = array('account_name' => 'Total Available Cash', 'amount' => $openingBalance + $runningBalance);
$sdata[] = array('account_name' => '', 'amount' => '');
$cashOut = (double) 0;
$sql = "" . "SELECT c.account_name, IFNULL(SUM(v.debit_amount),0) bal\n" . " FROM voucher_detail v\n" . " INNER JOIN chart_of_account c ON c.account_ref_id = v.account_ref_id\n" . " INNER JOIN sys_codes sc ON sc.sys_codes_ref_id = c.account_category\n" . " WHERE v.voucher_id IN (\n" . " SELECT vd.voucher_id\n" . " FROM voucher_detail vd\n" . " INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id\n" . " INNER JOIN chart_of_account coa ON coa.account_ref_id = vd.account_ref_id\n" . " WHERE TO_DAYS(vm.voucher_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "')\n" . " AND TO_DAYS(vm.voucher_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "')\n" . " AND coa.account_class = '{$accounts_class}'\n" . " AND vm.company_ref_id = {$_SESSION['company_id']}\n" . " AND vd.debit_amount = 0\n" . " )\n" . " AND v.credit_amount = 0\n" . "GROUP BY c.account_ref_id\n" . "ORDER BY c.account_name\n";
$result = $mysqli->query($sql);
function getOutstandingData($account_hidden, $report = "pdf", $date_start = "", $date_end = "", $party = false, $aclass = false)
{
global $mysqli;
/** @var $result mysqli_result */
$account_name = "";
$psql = "q.account_category = {$account_hidden}";
if ($party === true) {
$account_name = getIVal("account_name", "chart_of_account", "account_ref_id = {$account_hidden}");
$psql = "q.account_ref_id = {$account_hidden}";
} else {
if ($aclass === true) {
$psql = "q.account_class = '{$account_hidden}'";
}
}
$sql = "" . " SELECT q.account_ref_id,\n" . " q.account_name,\n" . " q.account_type,\n" . " p.party_city,\n" . " p.payment_term\n" . " FROM chart_of_account q\n" . " LEFT OUTER JOIN party p ON p.account_ref_id = q.account_ref_id\n" . " WHERE q.company_ref_id = {$_SESSION['company_id']}\n" . " AND {$psql}\n" . "ORDER BY q.account_name";
//dump($sql);
$result = $mysqli->query($sql);
$data = array();
$dr = 0;
$cr = 0;
$aob = 0;
while ($row = $result->fetch_assoc()) {
$aob = getOpeningBalance($row['account_ref_id'], $date_start);
$trans = getOutstandingDataTransactions($row['account_ref_id'], $date_start, $date_end);
$row['debit'] = $trans['debit'];
$row['credit'] = $trans['credit'];
$dr += $row['debit'];
$cr += $row['credit'];
if ($report == 'pdf') {
if ($row['account_type'] == 'Dr') {
$row['balance'] = number_format($aob + $row['debit'] - $row['credit']);
} else {
if ($row['account_type'] == 'Cr') {
$row['balance'] = number_format($aob + $row['credit'] - $row['debit']);
}
}
$row['debit'] = number_format($row['debit']);
$row['credit'] = number_format($row['credit']);
} else {
$row['balance'] = $aob;
}
$data[] = $row;
}
$result->close();
/*if (count($data) == 0) {
$sql = "" .
"SELECT coa.account_ref_id, coa.account_name, coa.account_type, p.party_city, p.payment_term\n" .
" FROM chart_of_account coa\n" .
" INNER JOIN party p ON p.account_ref_id = coa.account_ref_id\n" .
" WHERE coa.account_ref_id = $account_hidden";
$result = $mysqli->query($sql);
if($row = $result->fetch_assoc()) {
$data[] = array(
'account_ref_id' => $row['account_ref_id'],
'account_name' => $row['account_name'],
'account_type' => $row['account_type'],
'party_city' => $row['party_city'],
'debit' => '0',
'credit' => '0',
'payment_term' => $row['payment_term'],
'balance' => getOpeningBalance($row['account_ref_id'], $date_start)
);
}
$result->close();
}*/
$ret = array();
$ret['account_name'] = $account_name;
$ret['aob'] = $aob;
$ret['dr'] = $dr;
$ret['cr'] = $cr;
$ret['data'] = $data;
return $ret;
}
