' . MENU_GENERAL_LEDGER . '
';
if($_POST['date_from']!='') {
$msg .= REPORT_FROM.': '.$_POST['date_from'];
}
if($_POST['date_to']!='') {
$msg .= ' '.REPORT_TO.': '.$_POST['date_to'];
}
echo $this->element('/print/header-report',array('msg'=>$msg));
?>
|
|
|
|
|
|
|
|
|
|
|
|
element('check_access');
$allowViewAll = checkAccess($user['User']['id'], 'general_ledgers', 'viewAll');
// Fiscal period
$sqlFp = mysql_query("SELECT date_value FROM s_module_detail_settings WHERE id = 33");
$rowFp = mysql_fetch_array($sqlFp);
$dateFiscal = explode("-", $rowFp[0]);
$startDate = dateConvert($_POST['date_from']);
$d=date_parse_from_format('Y-m-d', $startDate);
$month = $d['month'];
$year = $d['year'];
if($dateFiscal[1] < $month){
$yearRE = $year;
$dateRE = $yearRE."-".$dateFiscal[1]."-".$dateFiscal[2];
} else {
if($dateFiscal[2] < $d['day']){
$yearRE = $year;
$dateRE = $yearRE."-".$dateFiscal[1]."-".$dateFiscal[2];
} else {
$yearRE = $year - 1;
$dateRE = $yearRE."-".$dateFiscal[1]."-".$dateFiscal[2];
}
}
// Delete Retained Earning Record
mysql_query("DELETE FROM general_ledger_details WHERE general_ledger_id IN (SELECT id FROM general_ledgers WHERE is_retained_earnings = 1);");
mysql_query("DELETE FROM general_ledgers WHERE is_retained_earnings = 1;");
// Date Start
$sqlStart = mysql_query("SELECT YEAR(date) FROM general_ledgers WHERE is_active = 1 ORDER BY date ASC LIMIT 1");
if(mysql_num_rows($sqlStart)){
$rowStart = mysql_fetch_array($sqlStart);
$sqlEnd = mysql_query("SELECT YEAR(date) FROM general_ledgers WHERE is_active = 1 ORDER BY date DESC LIMIT 1");
$rowEnd = mysql_fetch_array($sqlEnd);
if($rowStart[0] == $rowEnd[0]){
$dateEnd = $rowEnd[0]."-".$dateFiscal[1]."-".$dateFiscal[2];
$dateRetained = date('Y-m', strtotime("+1 months", strtotime($dateEnd)));
// Net Income Or Loss
$sqlPL = mysql_query("SELECT SUM(credit - debit) AS total, branch_id FROM general_ledger_details INNER JOIN general_ledgers ON general_ledgers.id = general_ledger_details.general_ledger_id INNER JOIN chart_accounts ON chart_accounts.id = general_ledger_details.chart_account_id WHERE general_ledgers.is_active = 1 AND chart_accounts.chart_account_type_id > 10 AND general_ledgers.date <= '".$rowEnd[0]."-".$dateFiscal[1]."-".$dateFiscal[2]."' GROUP BY branch_id;");
while($rowPl = mysql_fetch_array($sqlPL)){
// GL
mysql_query("INSERT INTO `general_ledgers` (`date`, `reference`, `created`, `created_by`, `modified`, `is_retained_earnings`, `is_sys`)
VALUES ('".$dateRetained."-01', '".$dateRetained."-01', '".date("Y-m-d H:i:s")."', ".$user['User']['id'].", '".date("Y-m-d H:i:s")."', 1, 1);");
$glId = mysql_insert_id();
if($rowPl[0] > 0){
// Retained Earning
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 22, 1, ".$rowPl['branch_id'].", 0, ".$rowPl['total'].", 'Retained Earning', 'Forward Net Income Balance ".$rowEnd[0]."');");
// Net Income or Loss
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 21, 1, ".$rowPl['branch_id'].", ".$rowPl['total'].", 0, 'Retained Earning', 'Forward Net Income Balance ".$rowEnd[0]."');");
} else {
// Retained Earning
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 22, 1, ".$rowPl['branch_id'].", ".abs($rowPl['total']).", 0, 'Retained Earning', 'Forward Net Income Balance ".$rowEnd[0]."');");
// Net Income or Loss
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 21, 1, ".$rowPl['branch_id'].", 0, ".abs($rowPl['total']).", 'Retained Earning', 'Forward Net Income Balance ".$rowEnd[0]."');");
}
}
// Dividends
$sqlDv = mysql_query("SELECT SUM(debit - credit) AS total, branch_id FROM general_ledger_details INNER JOIN general_ledgers ON general_ledgers.id = general_ledger_details.general_ledger_id WHERE general_ledgers.is_active = 1 AND general_ledger_details.chart_account_id = 23 AND general_ledgers.date <= '".$rowEnd[0]."-".$dateFiscal[1]."-".$dateFiscal[2]."' GROUP BY branch_id;");
while($rowDv = mysql_fetch_array($sqlDv)){
// GL
mysql_query("INSERT INTO `general_ledgers` (`date`, `reference`, `created`, `created_by`, `modified`, `is_retained_earnings`, `is_sys`)
VALUES ('".$dateRetained."-01', '".$dateRetained."-01', '".date("Y-m-d H:i:s")."', ".$user['User']['id'].", '".date("Y-m-d H:i:s")."', 1, 1);");
if($rowDv[0] > 0){
// Retained Earning
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 22, 1, ".$rowPl['branch_id'].", ".$rowPl['total'].", 0, 'Retained Earning', 'Forward Dividends Balance ".$rowEnd[0]."');");
// Net Income or Loss
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 23, 1, ".$rowPl['branch_id'].", 0, ".$rowPl['total'].", 'Retained Earning', 'Forward Dividends Balance ".$rowEnd[0]."');");
} else {
// Retained Earning
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 22, 1, ".$rowPl['branch_id'].", 0, ".abs($rowPl['total']).", 'Retained Earning', 'Forward Dividends Balance ".$rowEnd[0]."');");
// Net Income or Loss
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 23, 1, ".$rowPl['branch_id'].", ".abs($rowPl['total']).", 0, 'Retained Earning', 'Forward Dividends Balance ".$rowEnd[0]."');");
}
}
} else {
$startDate = ($rowStart[0]-1).'-'.$dateFiscal[1].'-'.$dateFiscal[2];
for($y = $rowStart[0]; $y <= $rowEnd[0]; $y++){
$endDate = $y."-".$dateFiscal[1]."-".$dateFiscal[2];
$dateRetained = date('Y-m', strtotime("+1 months", strtotime($endDate)));
// Net Income
$sqlPL = mysql_query("SELECT SUM(credit - debit) AS total, branch_id FROM general_ledger_details INNER JOIN general_ledgers ON general_ledgers.id = general_ledger_details.general_ledger_id INNER JOIN chart_accounts ON chart_accounts.id = general_ledger_details.chart_account_id WHERE general_ledgers.is_active = 1 AND chart_accounts.chart_account_type_id > 10 AND general_ledgers.date > '".$startDate."' AND general_ledgers.date <= '".$endDate."' GROUP BY branch_id;");
while($rowPl = mysql_fetch_array($sqlPL)){
// GL
mysql_query("INSERT INTO `general_ledgers` (`date`, `reference`, `created`, `created_by`, `modified`, `is_retained_earnings`)
VALUES ('".$dateRetained."-01', '".$dateRetained."-01', '".date("Y-m-d H:i:s")."', ".$user['User']['id'].", '".date("Y-m-d H:i:s")."', 1);");
$glId = mysql_insert_id();
if($rowPl[0] > 0){
// Retained Earning
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 22, 1, ".$rowPl['branch_id'].", 0, ".$rowPl['total'].", 'Retained Earning', 'Forward Net Income Balance ".$y."');");
// Net Income or Loss
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 21, 1, ".$rowPl['branch_id'].", ".$rowPl['total'].", 0, 'Retained Earning', 'Forward Net Income Balance ".$y."');");
} else {
// Retained Earning
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 22, 1, ".$rowPl['branch_id'].", ".abs($rowPl['total']).", 0, 'Retained Earning', 'Forward Net Income Balance ".$y."');");
// Net Income or Loss
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 21, 1, ".$rowPl['branch_id'].", 0, ".abs($rowPl['total']).", 'Retained Earning', 'Forward Net Income Balance ".$y."');");
}
}
// Dividends
$sqlDv = mysql_query("SELECT SUM(debit - credit) AS total, branch_id FROM general_ledger_details INNER JOIN general_ledgers ON general_ledgers.id = general_ledger_details.general_ledger_id WHERE general_ledgers.is_active = 1 AND general_ledger_details.chart_account_id = 23 AND general_ledgers.date > '".$startDate."' AND general_ledgers.date <= '".$endDate."' GROUP BY branch_id;");
while($rowDv = mysql_fetch_array($sqlDv)){
// GL
mysql_query("INSERT INTO `general_ledgers` (`date`, `reference`, `created`, `created_by`, `modified`, `is_retained_earnings`, `is_sys`)
VALUES ('".$dateRetained."-01', '".$dateRetained."-01', '".date("Y-m-d H:i:s")."', ".$user['User']['id'].", '".date("Y-m-d H:i:s")."', 1, 1);");
if($rowDv[0] > 0){
// Retained Earning
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 22, 1, ".$rowPl['branch_id'].", ".$rowPl['total'].", 0, 'Retained Earning', 'Forward Dividends Balance ".$rowEnd[0]."');");
// Net Income or Loss
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 23, 1, ".$rowPl['branch_id'].", 0, ".$rowPl['total'].", 'Retained Earning', 'Forward Dividends Balance ".$rowEnd[0]."');");
} else {
// Retained Earning
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 22, 1, ".$rowPl['branch_id'].", 0, ".abs($rowPl['total']).", 'Retained Earning', 'Forward Dividends Balance ".$rowEnd[0]."');");
// Net Income or Loss
mysql_query("INSERT INTO `general_ledger_details` (`general_ledger_id`, `chart_account_id`, `company_id`, `branch_id`, `debit`, `credit`, `type`, `memo`)
VALUES (".$glId.", 23, 1, ".$rowPl['branch_id'].", ".abs($rowPl['total']).", 0, 'Retained Earning', 'Forward Dividends Balance ".$rowEnd[0]."');");
}
}
$startDate = $y."-".$dateFiscal[1]."-".$dateFiscal[2];
}
}
}
// Tmp Condition
$tmpGlCondition = "";
$tmpCondition = "";
$condition = "gl.is_approve=1 AND gl.is_active=1";
if ($_POST['date_from'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= '"' . dateConvert($_POST['date_from']) . '" <= gl.date';
}
if ($_POST['date_to'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= '"' . dateConvert($_POST['date_to']) . '" >= gl.date';
}
if ($_POST['branch_id'] != '') {
$tmpCondition .= ' AND general_ledger_details.branch_id=' . $_POST['branch_id'];
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'gld.branch_id=' . $_POST['branch_id'];
} else {
$tmpCondition .= ' AND general_ledger_details.branch_id IN (SELECT branch_id FROM user_branches WHERE user_id = '.$user['User']['id'].')';
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'gld.branch_id IN (SELECT branch_id FROM user_branches WHERE user_id = '.$user['User']['id'].')';
}
if ($_POST['customer_id'] != '') {
$tmpCondition .= ' AND general_ledger_details.customer_id=' . $_POST['customer_id'];
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'gld.customer_id=' . $_POST['customer_id'];
}
if ($_POST['vendor_id'] != '') {
$tmpCondition .= ' AND general_ledger_details.vendor_id=' . $_POST['vendor_id'];
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'gld.vendor_id=' . $_POST['vendor_id'];
}
if ($_POST['chart_account_id'] != '') {
$tmpCondition .= ' AND general_ledger_details.chart_account_id=' . $_POST['chart_account_id'];
}
// Tmp General Ledger
$tableName = "general_ledger_tmp_lg" . $user['User']['id'];
mysql_query("DROP TABLE ".$tableName);
mysql_query("SET max_heap_table_size = 1024*1024*1024");
mysql_query("CREATE TABLE `".$tableName."` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`sys_code` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`credit_memo_with_sale_id` INT(11) NULL DEFAULT NULL,
`invoice_pbc_with_pbs_id` INT(11) NULL DEFAULT NULL,
`sales_order_id` INT(11) NULL DEFAULT NULL,
`sales_order_receipt_id` INT(11) NULL DEFAULT NULL,
`credit_memo_id` INT(11) NULL DEFAULT NULL,
`credit_memo_receipt_id` INT(11) NULL DEFAULT NULL,
`purchase_order_id` INT(11) NULL DEFAULT NULL,
`pv_id` INT(11) NULL DEFAULT NULL,
`purchase_return_id` INT(11) NULL DEFAULT NULL,
`purchase_return_receipt_id` INT(11) NULL DEFAULT NULL,
`ar_ap_gl_id` INT(11) NULL DEFAULT NULL,
`cycle_product_id` INT(11) NULL DEFAULT NULL,
`receive_payment_id` INT(11) NULL DEFAULT NULL,
`pay_bill_id` INT(11) NULL DEFAULT NULL,
`ar_aging_id` INT(11) NULL DEFAULT NULL,
`ap_aging_id` INT(11) NULL DEFAULT NULL,
`apply_to_id` INT(11) NULL DEFAULT NULL COMMENT 'Purchase Order; Purchase Bill; Quote; Sales Invoice',
`landing_cost_id` INT(11) NULL DEFAULT NULL,
`landing_cost_receipt_id` INT(11) NULL DEFAULT NULL,
`expense_id` INT(11) NULL DEFAULT NULL,
`other_income_id` INT(11) NULL DEFAULT NULL,
`inventory_physical_id` INT(11) NULL DEFAULT NULL,
`delivery_id` INT(11) NULL DEFAULT NULL,
`purchase_receive_result_id` INT(11) NULL DEFAULT NULL,
`apply_reference` VARCHAR(150) NULL DEFAULT NULL COMMENT 'Purchase Order; Purchase Bill; Quote; Sales Invoice Code' COLLATE 'utf8_unicode_ci',
`receive_from_id` INT(11) NULL DEFAULT NULL COMMENT 'Vendor or Customer Id',
`receive_from_name` VARCHAR(150) NULL DEFAULT NULL COMMENT 'Vendor or Customer Name' COLLATE 'utf8_unicode_ci',
`date` DATE NULL DEFAULT NULL,
`reference` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`total_deposit` DECIMAL(15,3) NULL DEFAULT NULL,
`note` TEXT NULL COLLATE 'utf8_unicode_ci',
`created` DATETIME NULL DEFAULT NULL,
`created_by` INT(11) NULL DEFAULT NULL,
`modified` DATETIME NULL DEFAULT NULL,
`modified_by` INT(11) NULL DEFAULT NULL,
`is_sys` TINYINT(4) NULL DEFAULT '0',
`is_adj` TINYINT(4) NULL DEFAULT '0',
`is_approve` TINYINT(4) NULL DEFAULT '1',
`is_depreciated` TINYINT(4) NULL DEFAULT '0',
`is_retained_earnings` TINYINT(4) NULL DEFAULT '0',
`deposit_type` TINYINT(4) NULL DEFAULT '0' COMMENT '0:Journal; 1: Normal; 2: Purchase Order; 3: Purchase Bill; 4: Quote; 5: Invoice',
`is_active` TINYINT(4) NULL DEFAULT '1',
PRIMARY KEY (`id`),
INDEX `key_filter` (`sales_order_id`, `sales_order_receipt_id`, `credit_memo_id`, `credit_memo_receipt_id`, `purchase_order_id`, `pv_id`),
INDEX `key_filter_second` (`purchase_return_id`, `purchase_return_receipt_id`, `ar_ap_gl_id`, `cycle_product_id`, `receive_payment_id`, `pay_bill_id`, `ar_aging_id`, `ap_aging_id`),
INDEX `key_filter_third` (`apply_to_id`, `receive_from_id`, `date`, `reference`, `is_approve`, `deposit_type`, `is_active`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;");
mysql_query("TRUNCATE `".$tableName."`;");
// Tmp General Ledger Detail
$tblGlDeail = "general_ledger_detail_tmp_lg" . $user['User']['id'];
mysql_query("DROP TABLE ".$tblGlDeail);
mysql_query("SET max_heap_table_size = 1024*1024*1024");
mysql_query("CREATE TABLE `".$tblGlDeail."` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`general_ledger_id` INT(11) NULL DEFAULT NULL,
`main_gl_id` INT(11) NULL DEFAULT NULL,
`chart_account_id` INT(11) NULL DEFAULT NULL,
`company_id` INT(11) NULL DEFAULT NULL,
`branch_id` INT(11) NULL DEFAULT NULL,
`location_group_id` INT(11) NULL DEFAULT NULL,
`location_id` INT(11) NULL DEFAULT NULL,
`product_id` INT(11) NULL DEFAULT NULL,
`service_id` INT(11) NULL DEFAULT NULL,
`is_free` TINYINT(4) NOT NULL DEFAULT '0',
`inventory_valuation_id` INT(11) NULL DEFAULT NULL,
`inventory_valuation_is_debit` TINYINT(4) NULL DEFAULT NULL,
`purchase_receive_id` INT(11) NULL DEFAULT NULL,
`sales_order_detail_id` INT(11) NULL DEFAULT NULL,
`credit_memo_detail_id` INT(11) NULL DEFAULT NULL,
`type` VARCHAR(50) NULL DEFAULT 'General Journal' COLLATE 'utf8_unicode_ci',
`debit` DECIMAL(20,9) NULL DEFAULT '0.000000000',
`credit` DECIMAL(20,9) NULL DEFAULT '0.000000000',
`memo` TEXT NULL COLLATE 'utf8_unicode_ci',
`customer_id` INT(11) NULL DEFAULT NULL,
`vendor_id` INT(11) NULL DEFAULT NULL,
`employee_id` INT(11) NULL DEFAULT NULL,
`other_id` INT(11) NULL DEFAULT NULL,
`class_id` INT(11) NULL DEFAULT NULL,
`is_reconcile` TINYINT(4) NULL DEFAULT '0',
`reconcile_id` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `key_filter_second` (`location_group_id`, `location_id`, `product_id`, `service_id`, `inventory_valuation_id`),
INDEX `key_filter_third` (`customer_id`, `vendor_id`, `employee_id`, `other_id`, `class_id`),
INDEX `key_filter` (`general_ledger_id`, `main_gl_id`, `chart_account_id`, `company_id`, `branch_id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;");
mysql_query("TRUNCATE `".$tblGlDeail."`;");
// Insert GL
mysql_query("INSERT INTO ".$tableName." SELECT * FROM general_ledgers WHERE is_active = 1 AND is_approve = 1 AND date >= '".dateConvert($_POST['date_from'])."' AND date <= '".dateConvert($_POST['date_to'])."'".$tmpGlCondition);
// Insert GL Detail
mysql_query("INSERT INTO ".$tblGlDeail." SELECT * FROM general_ledger_details WHERE general_ledger_id IN (SELECT id FROM general_ledgers WHERE is_active = 1 AND is_approve = 1 AND date >= '".dateConvert($_POST['date_from'])."' AND date <= '".dateConvert($_POST['date_to'])."'".$tmpGlCondition.")".$tmpCondition.";");
// Tmp Chart Account
$tblAccount = "chart_account_gl_tmp" . $user['User']['id'];
mysql_query("DROP TABLE ".$tblAccount);
mysql_query("SET max_heap_table_size = 1024*1024*1024");
mysql_query("CREATE TABLE `".$tblAccount."` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`debit` DECIMAL(20,9) NULL DEFAULT '0.000000000',
`credit` DECIMAL(20,9) NULL DEFAULT '0.000000000',
`chart_account_id` INT(11) NULL DEFAULT NULL,
`date` DATE NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `chart_account_id` (`chart_account_id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;");
mysql_query("TRUNCATE `".$tblAccount."`;");
// Insert Chart Account
mysql_query("INSERT INTO `".$tblAccount."` (`debit`, `credit`, `chart_account_id`, `date`) SELECT SUM(general_ledger_details.debit) AS debit, SUM(general_ledger_details.credit) AS credit, general_ledger_details.chart_account_id AS chart_account_id, general_ledgers.date FROM general_ledger_details INNER JOIN general_ledgers ON general_ledgers.id = general_ledger_details.general_ledger_id AND general_ledgers.is_approve=1 AND general_ledgers.date < '" . dateConvert($_POST['date_from']) . "'".$tmpGlCondition." WHERE general_ledgers.is_active=1".$tmpCondition." GROUP BY general_ledger_details.chart_account_id, general_ledgers.date");
/**
* export to excel
*/
$filename="public/report/ledger_" . $user['User']['id'] . ".csv";
$fp=fopen($filename,"wb");
$excelContent = MENU_GENERAL_LEDGER . "\n\n";
if($_POST['date_from']!='') {
$excelContent .= REPORT_FROM . ': ' . str_replace('|||','/',$_POST['date_from']);
}
if($_POST['date_to']!='') {
$excelContent .= ' '.REPORT_TO . ': ' . str_replace('|||','/',$_POST['date_to']);
}
$excelContent .= "\n\n".TABLE_NO."\t".TABLE_BRANCH."\t".TABLE_DATE."\t".TABLE_CREATED_BY."\t".TABLE_REFERENCE."\t".TABLE_TYPE."\tAccount Code\t".TABLE_MEMO."\t".GENERAL_DEBIT."\t".GENERAL_CREDIT."\t".GENERAL_BALANCE;
$sqlChartAccount = mysql_query("SELECT * FROM chart_accounts WHERE is_active IN (1, 4) AND id != 21 ORDER BY account_codes ASC;");
if(mysql_num_rows($sqlChartAccount)){
$netDebit = 0;
$netCredit = 0;
$netBalance = 0;
while($rowAccount = mysql_fetch_array($sqlChartAccount)){
$bbiCon = "";
if($rowAccount['chart_account_type_id'] > 10 || $rowAccount['id'] == 23){
$bbiCon = " AND date > '".$dateRE."'";
}
$queryBBI = mysql_query("SELECT IFNULL((SELECT SUM(debit) FROM ".$tblAccount." WHERE chart_account_id='" . $rowAccount['id'] . "'".$bbiCon."),0)-IFNULL((SELECT SUM(credit) FROM ".$tblAccount." WHERE chart_account_id='" . $rowAccount['id'] . "'".$bbiCon."),0)");
$dataBBI = mysql_fetch_array($queryBBI);
$totalDebit = 0;
$totalCredit = 0;
$totalBalance = $dataBBI[0];
if($rowAccount['chart_account_type_id'] == 1 || $rowAccount['chart_account_type_id'] == 2 || $rowAccount['chart_account_type_id'] == 3 || $rowAccount['chart_account_type_id'] == 4 || $rowAccount['chart_account_type_id'] == 12 || $rowAccount['chart_account_type_id'] == 13 || $rowAccount['chart_account_type_id'] == 15){
$totalDebit = abs($dataBBI[0]);
} else {
$totalCredit = abs($dataBBI[0]);
}
// $netDebit += $totalDebit;
// $netCredit += $totalCredit;
?>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Total |
|
|
|
|
TOTAL |
|
|
|
|
element('report_footer'); ?>