' . MENU_REPORT_SALES_BY_REP . '
';
$excelContent .= MENU_REPORT_SALES_BY_REP."\n\n";
if($_POST['date_from']!='') {
$msg .= REPORT_FROM.': '.$_POST['date_from'];
$excelContent .= REPORT_FROM.': '.$_POST['date_from'];
}
if($_POST['date_to']!='') {
$msg .= ' '.REPORT_TO.': '.$_POST['date_to'];
$excelContent .= ' '.REPORT_TO.': '.$_POST['date_to']."\n\n";
}
echo $this->element('/print/header-report',array('msg'=>$msg));
if($_POST['is_free'] == '1'){
$tableQty = 'Qty Free';
} else if($_POST['is_free'] == '0') {
$tableQty = 'Qty';
} else {
$tableQty = 'Qty + Free';
}
$excelContent .= TABLE_NO."\t".TABLE_TYPE."\t".TABLE_DATE."\t".TABLE_INVOICE_CODE."\t".TABLE_PRODUCT_NAME."\t".TABLE_LOCATION."\t".$tableQty."\t".TABLE_UOM."\t".TABLE_UNIT_PRICE."\t".GENERAL_DISCOUNT."\t".GENERAL_AMOUNT;
?>
|
|
|
|
|
|
|
|
|
|
|
= DATE(tbl.order_date)';
}
$condition != '' ? $condition .= ' AND ' : '';
if ($_POST['status'] == '') {
$condition .= 'tbl.status>-1';
} else {
$condition .= 'tbl.status=' . $_POST['status'];
}
if ($_POST['company_id'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'tbl.company_id=' . $_POST['company_id'];
} else {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'tbl.company_id IN (SELECT company_id FROM user_companies WHERE user_id = '.$user['User']['id'].')';
}
if ($_POST['branch_id'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'tbl.branch_id=' . $_POST['branch_id'];
} else {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'tbl.branch_id IN (SELECT branch_id FROM user_branches WHERE user_id = '.$user['User']['id'].')';
}
if ($_POST['location_group_id'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'tbl.location_group_id=' . $_POST['location_group_id'];
}
if ($_POST['created_by'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'tbl.created_by=' . $_POST['created_by'];
}
if ($_POST['salesman'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'tbl.sales_rep_id=' . $_POST['salesman'];
}
if ($_POST['cgroup_id'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= $_POST['cgroup_id'] . ' IN (SELECT cgroup_id FROM customer_cgroups WHERE customer_id = tbl.customer_id)';
}
if ($_POST['customer_id'] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'tbl.customer_id = '.$_POST['customer_id'];
}
$grandTotalAmount=0;
?>
| Product |
0, CONCAT_WS(' ',employees.employee_code,employees.name), 'No Rep') AS rep_name,
sales_orders.customer_id,
CONCAT_WS(' ',customers.customer_code,customers.name) AS customer_name,
sales_orders.order_date AS order_date,
sales_orders.so_code AS code,
CONCAT_WS(' ',products.code,products.name) AS product_name,
(SELECT name FROM location_groups WHERE id = sales_orders.location_group_id) AS location_name,
sales_order_details.qty AS qty,
sales_order_details.qty_free AS qty_free,
sales_order_details.conversion AS conversion,
products.price_uom_id AS qty_uom_id,
uoms.abbr AS qty_uom_name,
sales_order_details.unit_price AS unit_price,
".($_POST['is_free']=='1'?'0 AS discount_amount,':'sales_order_details.discount_amount AS discount_amount,')."
".($_POST['is_free']=='1'?'0 AS total_price':'sales_order_details.total_price AS total_price')."
FROM sales_orders
INNER JOIN sales_order_details ON sales_orders.id=sales_order_details.sales_order_id
INNER JOIN customers ON customers.id = sales_orders.customer_id
INNER JOIN products ON products.id = sales_order_details.product_id
INNER JOIN uoms ON uoms.id = products.price_uom_id
INNER JOIN employees ON employees.id = sales_orders.sales_rep_id
WHERE "
. str_replace('tbl.','sales_orders.',$condition)
. ($_POST['pgroup_id'] != ''?' AND product_id IN (SELECT product_id FROM product_pgroups WHERE pgroup_id=' . $_POST['pgroup_id'] . ')':'')
. ($_POST['parent_id'] != ''?' AND (SELECT parent_id FROM products WHERE id=product_id)=' . $_POST['parent_id'] :'')
. ($_POST['product_id'] != ''?' AND product_id=' . $_POST['product_id'] :'')
. ($_POST['is_free'] != ''?($_POST['is_free'] != '1'?' AND sales_order_details.qty > 0':' AND sales_order_details.qty_free > 0'):'')
. "
UNION ALL
SELECT
credit_memos.id,
'Credit Memo' AS trans_type,
IFNULL(sales_orders.sales_rep_id, 0) AS rep_id,
IF(sales_orders.sales_rep_id > 0, CONCAT_WS(' ',employees.employee_code,employees.name), 'No Rep') AS rep_name,
credit_memos.customer_id,
CONCAT_WS(' ',customers.customer_code,customers.name) AS customer_name,
credit_memos.order_date AS order_date,
credit_memos.cm_code AS code,
CONCAT_WS(' ',products.code,products.name) AS product_name,
(SELECT name FROM location_groups WHERE id = credit_memos.location_group_id) AS location_name,
credit_memo_details.qty * -1 AS qty,
credit_memo_details.qty_free * -1 AS qty_free,
credit_memo_details.conversion AS conversion,
products.price_uom_id AS qty_uom_id,
uoms.abbr AS qty_uom_name,
credit_memo_details.unit_price * -1 AS unit_price,
".($_POST['is_free']=='1'?'0 AS discount_amount,':'credit_memo_details.discount_amount * -1 AS discount_amount,')."
".($_POST['is_free']=='1'?'0 AS total_price':'credit_memo_details.total_price * -1 AS total_price')."
FROM credit_memos
INNER JOIN credit_memo_details ON credit_memos.id = credit_memo_details.credit_memo_id
INNER JOIN sales_orders ON sales_orders.id = credit_memos.sales_order_id
INNER JOIN customers ON customers.id = credit_memos.customer_id
INNER JOIN products ON products.id = credit_memo_details.product_id
INNER JOIN uoms ON uoms.id = products.price_uom_id
INNER JOIN employees ON employees.id = sales_orders.sales_rep_id
WHERE "
. str_replace(array('tbl.is_pos=0 AND','tbl.'),array('','credit_memos.'),$condition)
. ($_POST['pgroup_id'] != ''?' AND product_id IN (SELECT product_id FROM product_pgroups WHERE pgroup_id=' . $_POST['pgroup_id'] . ')':'')
. ($_POST['parent_id'] != ''?' AND (SELECT parent_id FROM products WHERE id=product_id)=' . $_POST['parent_id'] :'')
. ($_POST['product_id'] != ''?' AND product_id=' . $_POST['product_id'] :'')
. ($_POST['is_free'] != ''?($_POST['is_free'] != '1'?' AND credit_memo_details.qty > 0':' AND credit_memo_details.qty_free > 0'):'')
. "
ORDER BY rep_name,customer_name,order_date") or die(mysql_error());
while($data=mysql_fetch_array($query)){
$arrCode[] = $data['code'];
$arrProduct[] = $data['product_name'];
$arrLocation[] = $data['location_name'];
if($data['customer_id']!=$oldCustomerId){
if($oldCustomerName!=''){
$excelContent .= "\n\t".'Total '.$oldCustomerName."\t\t\t\t\t".number_format($subTotalQty, 0)."\t".$dataSmallestUom['name']."\t\t\t".number_format($subTotalAmount, $priceDecimal); ?>
|
Total |
|
|
|
|
|
| Total |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total |
|
|
|
|
|
| Total |
|
|
|
|
|
| Total Product |
|
|
|
|
|
| |
| Service |
0':' AND sales_order_services.qty_free > 0'):'')
. "
UNION ALL
SELECT
credit_memos.id,
'Credit Memo' AS trans_type,
CONCAT_WS('','S',sales_orders.sales_rep_id) AS rep_id,
CONCAT_WS(' ',employees.employee_code,employees.name) AS rep_name,
CONCAT_WS('','S',sales_orders.customer_id) AS customer_id,
CONCAT_WS(' ',customers.customer_code,customers.name) AS customer_name,
credit_memos.order_date AS order_date,
credit_memos.cm_code AS code,
CONCAT_WS(' ',services.code,services.name) AS product_name,
(SELECT name FROM location_groups WHERE id = credit_memos.location_group_id) AS location_name,
credit_memo_services.qty * -1 AS qty,
credit_memo_services.qty_free * -1 AS qty_free,
uoms.abbr AS qty_uom_name,
credit_memo_services.unit_price * -1 AS unit_price,
".($_POST['is_free']=='1'?'0 AS discount_amount,':'credit_memo_services.discount_amount * -1 AS discount_amount,')."
".($_POST['is_free']=='1'?'0 AS total_price':'credit_memo_services.total_price * -1 AS total_price')."
FROM credit_memos
INNER JOIN credit_memo_services ON credit_memos.id = credit_memo_services.credit_memo_id
INNER JOIN sales_orders ON sales_orders.id = credit_memos.sales_order_id
INNER JOIN customers ON customers.id = credit_memos.customer_id
INNER JOIN services ON services.id = credit_memo_services.service_id
INNER JOIN uoms ON uoms.id = services.uom_id
INNER JOIN employees ON employees.id = sales_orders.sales_rep_id
WHERE "
. str_replace(array('tbl.is_pos=0 AND','tbl.'),array('','credit_memos.'),$condition)
. ($_POST['is_free'] != ''?($_POST['is_free'] != '1'?' AND sales_order_services.qty > 0':' AND sales_order_services.qty_free > 0'):'')
. "
ORDER BY rep_name,customer_name,order_date");
while($data=mysql_fetch_array($query)){
$arrCode[] = $data['code'];
$arrProduct[] = $data['product_name'];
$arrLocation[] = $data['location_name'];
if($data['customer_id']!=$oldCustomerId){
if($oldCustomerName!=''){
$excelContent .= "\n\t".'Total '.$oldCustomerName."\t\t\t\t\t".number_format($subTotalQty, 0)."\t".$data['qty_uom_name']."\t\t\t".number_format($subTotalAmount, $priceDecimal); ?>
|
Total |
|
|
|
|
|
| Total |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total |
|
|
|
|
|
| Total |
|
|
|
|
|
| Total Product |
|
|
|
|
|
| |
| Grand Total Amount |
|
|
|
|
|