' . MENU_REPORT_STATEMENT_BY_REP . '
';
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));
?>
|
Amount Due |
Amount Enc. |
|
|
|
| Date |
Transaction |
Amount |
Balance |
0,debit,credit*-1) AS amount
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE gl.is_approve=1 AND gl.is_active=1 AND gld.chart_account_id IN (SELECT id FROM chart_accounts WHERE chart_account_type_id=2)
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND date <= '" . dateConvert($_POST['date_to']) . "' AND (sales_order_id IS NOT NULL OR credit_memo_id IS NOT NULL)
ORDER BY date") or die(mysql_error());
while($dataTransaction=mysql_fetch_array($queryTransaction)){
$amount += $dataTransaction['amount'];
if (strtotime($dataTransaction['date']) < strtotime(dateConvert($_POST['date_from']))) {
$displayBalanceForward=1;
}else{
if($displayBalanceForward == 1){
$displayBalanceForward = 0;
?>
|
Balance forward |
|
$ |
|
- |
|
|
| Current |
1 - 30 |
31 - 60 |
61 - 90 |
> 90 |
Amount Due |
0
AND credit_memo_receipt_id IS NULL") or die(mysql_error());
$data1=mysql_fetch_array($query1);
$query2=mysql_query(" SELECT SUM(credit) AS amount,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND date<='" . dateConvert($_POST['date']) . "'
AND credit>0
AND (
sales_order_id IN (" . ($data1['arr_sales_order_id']!=""?$data1['arr_sales_order_id']:-1) . ")
OR
main_gl_id IN (" . ($data1['arr_main_gl_id']!=""?$data1['arr_main_gl_id']:-1) . ")
)");
$data2=mysql_fetch_array($query2);
$query3=mysql_query(" SELECT SUM(credit) AS amount,GROUP_CONCAT(credit_memo_id) AS arr_credit_memo_id,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND date='" . dateConvert($_POST['date']) . "'
AND date<='" . dateConvert($_POST['date']) . "'
AND credit>0
AND sales_order_id IS NULL
AND main_gl_id IS NULL");
$data3=mysql_fetch_array($query3);
$query4=mysql_query(" SELECT SUM(debit) AS amount,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND date<='" . dateConvert($_POST['date']) . "'
AND debit>0
AND (
credit_memo_id IN (" . ($data3['arr_credit_memo_id']!=""?$data3['arr_credit_memo_id']:-1) . ")
)");
$data4=mysql_fetch_array($query4);
$amount=$data1['amount']+$data4['amount']-$data2['amount']-$data3['amount'];
$amount=number_format($amount,2,".","");
$total_row+=$amount;
$total_col[$colIndex]+=$amount;
$glIdList=explode(",", ($data1['arr_gl_id']!=''?$data1['arr_gl_id'].',':'').($data2['arr_gl_id']!=''?$data2['arr_gl_id'].',':'').($data3['arr_gl_id']!=''?$data3['arr_gl_id'].',':'').($data4['arr_gl_id']!=''?$data4['arr_gl_id'].',':''));
$glIdListAll[$colIndex].=implode("-", $glIdList);
?>
| "> |
0
AND credit_memo_receipt_id IS NULL");
$data1=mysql_fetch_array($query1);
$query2=mysql_query(" SELECT SUM(credit) AS amount,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND date<='" . dateConvert($_POST['date']) . "'
AND credit>0
AND (
sales_order_id IN (" . ($data1['arr_sales_order_id']!=""?$data1['arr_sales_order_id']:-1) . ")
OR
main_gl_id IN (" . ($data1['arr_main_gl_id']!=""?$data1['arr_main_gl_id']:-1) . ")
)");
$data2=mysql_fetch_array($query2);
$query3=mysql_query(" SELECT SUM(credit) AS amount,GROUP_CONCAT(credit_memo_id) AS arr_credit_memo_id,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND DATEDIFF('" . dateConvert($_POST['date']) . "',date) BETWEEN " . $from . " AND " . $to . "
AND date<='" . dateConvert($_POST['date']) . "'
AND credit>0
AND sales_order_id IS NULL
AND main_gl_id IS NULL");
$data3=mysql_fetch_array($query3);
$query4=mysql_query(" SELECT SUM(debit) AS amount,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND date<='" . dateConvert($_POST['date']) . "'
AND debit>0
AND (
credit_memo_id IN (" . ($data3['arr_credit_memo_id']!=""?$data3['arr_credit_memo_id']:-1) . ")
)");
$data4=mysql_fetch_array($query4);
$amount=$data1['amount']+$data4['amount']-$data2['amount']-$data3['amount'];
$amount=number_format($amount,2,".","");
$total_row+=$amount;
$total_col[++$colIndex]+=$amount;
$glIdList=explode(",", ($data1['arr_gl_id']!=''?$data1['arr_gl_id'].',':'').($data2['arr_gl_id']!=''?$data2['arr_gl_id'].',':'').($data3['arr_gl_id']!=''?$data3['arr_gl_id'].',':'').($data4['arr_gl_id']!=''?$data4['arr_gl_id'].',':''));
$glIdListAll[$colIndex].=implode("-", $glIdList);
?>
"> |
" . $_POST['through'] . "
AND date<='" . dateConvert($_POST['date']) . "'
AND debit>0
AND credit_memo_receipt_id IS NULL");
$data1=mysql_fetch_array($query1);
$query2=mysql_query(" SELECT SUM(credit) AS amount,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND date<='" . dateConvert($_POST['date']) . "'
AND credit>0
AND (
sales_order_id IN (" . ($data1['arr_sales_order_id']!=""?$data1['arr_sales_order_id']:-1) . ")
OR
main_gl_id IN (" . ($data1['arr_main_gl_id']!=""?$data1['arr_main_gl_id']:-1) . ")
)");
$data2=mysql_fetch_array($query2);
$query3=mysql_query(" SELECT SUM(credit) AS amount,GROUP_CONCAT(credit_memo_id) AS arr_credit_memo_id,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND DATEDIFF('" . dateConvert($_POST['date']) . "',date) > " . $_POST['through'] . "
AND date<='" . dateConvert($_POST['date']) . "'
AND credit>0
AND sales_order_id IS NULL
AND main_gl_id IS NULL");
$data3=mysql_fetch_array($query3);
$query4=mysql_query(" SELECT SUM(debit) AS amount,
GROUP_CONCAT(gl.id) AS arr_gl_id
FROM general_ledgers gl
INNER JOIN general_ledger_details gld ON gl.id=gld.general_ledger_id
WHERE is_active=1
AND chart_account_id IN (" . implode(",", $arrCoAIdList) . ")
AND IFNULL((SELECT sales_rep_id FROM sales_orders WHERE id = gl.sales_order_id), (SELECT sales_rep_id FROM sales_orders WHERE id = (SELECT sales_order_id FROM credit_memos WHERE id = gl.credit_memo_id))) = ".$_POST['employee_id']."
AND date<='" . dateConvert($_POST['date']) . "'
AND debit>0
AND (
credit_memo_id IN (" . ($data3['arr_credit_memo_id']!=""?$data3['arr_credit_memo_id']:-1) . ")
)");
$data4=mysql_fetch_array($query4);
$amount=$data1['amount']+$data4['amount']-$data2['amount']-$data3['amount'];
$amount=number_format($amount,2,".","");
$total_row+=$amount;
$total_col[++$colIndex]+=$amount;
$glIdList=explode(",", ($data1['arr_gl_id']!=''?$data1['arr_gl_id'].',':'').($data2['arr_gl_id']!=''?$data2['arr_gl_id'].',':'').($data3['arr_gl_id']!=''?$data3['arr_gl_id'].',':'').($data4['arr_gl_id']!=''?$data4['arr_gl_id'].',':''));
$glIdListAll[$colIndex].=implode("-", $glIdList);
?>
"> |
|