' . MENU_REPORT_OPEN_BILL . '
';
$excelContent .= MENU_REPORT_OPEN_BILL."\n\n";
if($_POST['date']!='') {
$msg .= TABLE_DATE.': '.$_POST['date'];
$excelContent .= TABLE_DATE.': '.$_POST['date']."\n\n";
}
echo $this->element('/print/header-report',array('msg'=>$msg));
$excelContent .= TABLE_NO."\t".TABLE_TYPE."\t".TABLE_DATE."\t".TABLE_CODE."\t".TABLE_DUE_DATE."\t".GENERAL_AGING."\t".TABLE_OPEN_BALANCE." (".$rowSym[0].")";
?>
|
|
|
|
|
|
() |
0';
if ($data[0] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= '"' . $date . '" >= DATE(order_date)';
}
if ($data[1] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'vendor_id IN (SELECT vendor_id FROM vendor_vgroups WHERE vgroup_id=' . $data[1] . ')';
}
if ($data[2] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'vendor_id=' . $data[2];
}
$index = 1;
$oldVendorId = '';
$oldVendorName = '';
$subTotalBalance = 0;
$totalBalance = 0;
$grandTotalBalance = 0;
$query=mysql_query("SELECT
'Purchase Bill' AS trans_type,
vendors.id AS vendor_id,
purchase_orders.id AS id,
purchase_orders.order_date AS order_date,
purchase_orders.po_code AS purchase_code,
CONCAT_WS(' - ', vendors.vendor_code, vendors.name) AS vendor_name,
purchase_orders.balance + IFNULL((SELECT SUM(IFNULL(amount_us,0)+IFNULL(amount_other/(SELECT rate_to_sell FROM exchange_rates WHERE id=pvs.exchange_rate_id),0)) FROM pvs WHERE DATE_SUB(DATE(IF(pay_date IS NULL,(SELECT date FROM general_ledgers WHERE pv_id=pvs.id),pay_date)), INTERVAL 1 DAY) >= '" . $date . "' AND purchase_order_id=purchase_orders.id AND is_void=0),0) + IFNULL((SELECT SUM(total_cost) FROM invoice_pbc_with_pbs WHERE status=1 AND purchase_order_id=purchase_orders.id AND DATE_SUB(DATE(IF(apply_date IS NULL,(SELECT order_date FROM purchase_returns WHERE id=invoice_pbc_with_pbs.purchase_return_id),apply_date)), INTERVAL 1 DAY) >= '" . $date . "'),0) AS balance,
IF(purchase_orders.balance > 0 AND DATEDIFF('".$date."',order_date)>0, DATEDIFF('".$date."',order_date), '-') AS aging
FROM purchase_orders
LEFT JOIN vendors ON vendors.id = purchase_orders.vendor_id
WHERE "
. $condition
. " AND purchase_orders.balance + IFNULL((SELECT SUM(IFNULL(amount_us,0)+IFNULL(amount_other/(SELECT rate_to_sell FROM exchange_rates WHERE id=pvs.exchange_rate_id),0)) FROM pvs WHERE DATE_SUB(DATE(IF(pay_date IS NULL,(SELECT date FROM general_ledgers WHERE pv_id=pvs.id),pay_date)), INTERVAL 1 DAY) >= '" . $date . "' AND purchase_order_id=purchase_orders.id AND is_void=0),0) + IFNULL((SELECT SUM(total_cost) FROM invoice_pbc_with_pbs WHERE status=1 AND purchase_order_id=purchase_orders.id AND DATE_SUB(DATE(IF(apply_date IS NULL,(SELECT order_date FROM purchase_returns WHERE id=invoice_pbc_with_pbs.purchase_return_id),apply_date)), INTERVAL 1 DAY) >= '" . $date . "'),0) > 0.001"
. "
UNION
SELECT
'Purchase Return' AS trans_type,
vendors.id AS vendor_id,
purchase_returns.id AS id,
purchase_returns.order_date AS order_date,
purchase_returns.pr_code AS purchase_code,
CONCAT_WS(' - ', vendors.vendor_code, vendors.name) AS vendor_name,
(purchase_returns.balance + IFNULL((SELECT SUM(IFNULL(amount_us,0)+IFNULL(amount_other/(SELECT rate_to_sell FROM exchange_rates WHERE id=purchase_return_receipts.exchange_rate_id),0)) FROM purchase_return_receipts WHERE DATE_SUB(DATE(IF(pay_date IS NULL,(SELECT date FROM general_ledgers WHERE credit_memo_receipt_id=purchase_return_receipts.id),pay_date)), INTERVAL 1 DAY) >= '" . $date . "' AND purchase_return_id=purchase_returns.id AND is_void=0),0) + IFNULL((SELECT SUM(total_cost) FROM invoice_pbc_with_pbs WHERE status=1 AND purchase_return_id=purchase_returns.id AND DATE_SUB(DATE(IF(apply_date IS NULL,(SELECT order_date FROM purchase_orders WHERE id=invoice_pbc_with_pbs.purchase_order_id),apply_date)), INTERVAL 1 DAY) >= '" . $date . "'),0))*-1 AS balance,
IF(purchase_returns.balance>0 AND DATEDIFF('".$date."',order_date)>0,DATEDIFF('".$date."',order_date),'-') AS aging
FROM purchase_returns
LEFT JOIN vendors ON vendors.id = purchase_returns.vendor_id
WHERE "
. $condition
. " AND purchase_returns.balance + IFNULL((SELECT SUM(IFNULL(amount_us,0)+IFNULL(amount_other/(SELECT rate_to_sell FROM exchange_rates WHERE id=purchase_return_receipts.exchange_rate_id),0)) FROM purchase_return_receipts WHERE DATE_SUB(DATE(IF(pay_date IS NULL,(SELECT date FROM general_ledgers WHERE credit_memo_receipt_id=purchase_return_receipts.id),pay_date)), INTERVAL 1 DAY) >= '" . $date . "' AND purchase_return_id=purchase_returns.id AND is_void=0),0) + IFNULL((SELECT SUM(total_cost) FROM invoice_pbc_with_pbs WHERE status=1 AND purchase_return_id=purchase_returns.id AND DATE_SUB(DATE(IF(apply_date IS NULL,(SELECT order_date FROM purchase_orders WHERE id=invoice_pbc_with_pbs.purchase_order_id),apply_date)), INTERVAL 1 DAY) >= '" . $date . "'),0) > 0.001"
. "
ORDER BY vendor_name,order_date") or die(mysql_error());
while($data=mysql_fetch_array($query)){
if($data['vendor_id'] != $oldVendorId){
if($oldVendorName!=''){
$excelContent .= "\n".'Total '.$oldVendorName."\t\t\t\t\t\t".number_format($subTotalBalance, 2); ?>
| Total |
|
|
|
|
|
|
|
|
|
| Total |
|
| |
| Grand Total Amount |
|