/** @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; }