<?php
/*=======================================================\
|                 Point of Sales Module                  |
|--------------------------------------------------------|
|   Creator: Phương                                      |
|   Date :   18-Dec-2017                                 |
|   Description: ERP Point of Sales Extension            |
|   Website: http://notrinos.com                         |
|                                                        |
\=======================================================*/

function pos_exclude_items($code) {
	$sql = "INSERT INTO ".TB_PREF."pos_stock_exclude (stock_id) VALUES (".db_escape($code).") ON DUPLICATE KEY UPDATE stock_id = ".db_escape($code);
	db_query($sql, 'item could not be added');
}

function pos_include_item($code) {
	$sql = "DELETE FROM ".TB_PREF."pos_stock_exclude WHERE stock_id = ".db_escape($code);
	db_query($sql, 'could not delete stock item');
}

function pos_exclude_returnable_items($code) {
	$sql = "INSERT INTO ".TB_PREF."pos_returnable_exclude (stock_id) VALUES (".db_escape($code).") ON DUPLICATE KEY UPDATE stock_id = ".db_escape($code);
	db_query($sql, 'item could not be added');
}

function pos_include_returnable_item($code) {
	$sql = "DELETE FROM ".TB_PREF."pos_returnable_exclude WHERE stock_id = ".db_escape($code);
	db_query($sql, 'could not delete stock item');
}

function is_returnable_item($item_code) {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_returnable_exclude WHERE stock_id = ".db_escape($item_code));
}

function get_pos_items($category=false) {

	$sales_type = get_company_pref('pos_def_sales_type');

	$sql = "SELECT i.stock_id, i.description, s.units, SUM(p.price) as price, i.item_code, i.is_foreign, i.quantity, count(*)>1 as kit, p.sales_type_id FROM ".TB_PREF."item_codes i LEFT JOIN ".TB_PREF."pos_stock_exclude e ON i.item_code = e.stock_id, ".TB_PREF."stock_master s LEFT JOIN ".TB_PREF."prices p ON p.stock_id = s.stock_id AND p.sales_type_id = ".db_escape($sales_type)." WHERE i.stock_id = s.stock_id AND e.stock_id IS NULL AND !s.inactive AND !s.no_sale AND s.mb_flag != 'F'";
	if($category)
		$sql .= " AND s.category_id =".db_escape($category);
	$sql .= " GROUP BY i.item_code";

	return db_query($sql, 'Could not get stock data');
}

function get_pos_item_price($stock_id) {

	$sales_type = get_company_pref('pos_def_sales_type');

	$sql = "SELECT p.price FROM ".TB_PREF."prices p WHERE p.sales_type_id = ".db_escape($sales_type)." AND p.stock_id = ".db_escape($stock_id);
	$result = db_query($sql, 'could not retreived item price');

	$row = db_fetch($result);

	return !empty($row[0]) ? $row[0] : 0;
}

function get_pos_item_price_after_taxes($item_code, $price, $tax_included=false) {

	$item_tax_type = get_item_tax_type_for_item($item_code);

	if($item_tax_type['exempt'] || $tax_included)
		return $price;

	$tax_type_exemptions_db = get_item_tax_type_exemptions($item_tax_type['id']);
	$item_tax_type_exemptions = array();

	while($item_tax_type_exemp = db_fetch($tax_type_exemptions_db)) {
		$item_tax_type_exemptions[] = $item_tax_type_exemp['tax_type_id'];
	}
	
	$ret_tax_array = array();

	$all_tax_types_db = get_all_tax_types_simple();
	$all_tax_types = array();

	while($tax_type_row = db_fetch($all_tax_types_db)) {
		$all_tax_types[] = $tax_type_row;
	}

	foreach($all_tax_types as $tax_item) { 
		
		$skip = false;
		
		// if it's in the exemptions, skip
		foreach ($item_tax_type_exemptions as $exemption) 
		{
			if (($tax_item['id'] == $exemption)) 
			{
    			$skip = true;
    			break;
			}
		}
		
		if (!$skip) {
			$index = $tax_item['id'];
			$ret_tax_array[$index] = $tax_item;
		}
	}

	$price_after_tax = $price;

	foreach($ret_tax_array as $tax) {
		$price_after_tax += round2($price * $tax['rate']/100, user_price_dec());
	}

	return $price_after_tax;
}

function save_pos_trans($trans_no, $cash, $discount, $order_type='', $table_id='', $trans_time, $pos_id) {

	$sql = "INSERT INTO ".TB_PREF."pos_trans (trans_no, cash_amount, discount";

	if(!empty($order_type))
		$sql .= ",order_type";
	if(!empty($table_id))
		$sql .= ",table_id";

	$sql .= ", trans_time, pos_id) VALUES (".db_escape($trans_no).",".db_escape($cash).",".db_escape($discount);

	if(!empty($order_type))
		$sql .= ",".db_escape($order_type);
	if(!empty($table_id))
		$sql .= ",".db_escape($table_id);

	$sql .= ",'".$trans_time."',".db_escape($pos_id).")";
	
	db_query($sql, 'could not save cash tendered data');
}

function get_pos_trans($trans_no) {
	$sql = "SELECT cash_amount, discount, order_type, table_id, trans_time, pos_id FROM ".TB_PREF."pos_trans WHERE trans_no = ".db_escape($trans_no);
	$result = db_query($sql, 'could not get pos trans data');

	return db_fetch($result);
}

function pos_default_payment_term() {
	$sql = "SELECT MIN(terms_indicator) FROM ".TB_PREF."payment_terms WHERE days_before_due > 0 AND !inactive";
	$result = db_query($sql, 'could not get payment term');

	$row = db_fetch($result);
	return $row[0];
}

function is_pos_trans($trans_no) {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_trans WHERE trans_no = ".db_escape($trans_no));
}

//--------------------------------------------------------------------------

function db_has_pos_salesman() {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."salaryscale");
}

function write_salesman($id=false, $name, $phone, $email) {
	
	if(!$id)
		$sql = "INSERT INTO ".TB_PREF."pos_salesman(name,phone,email) VALUES (".db_escape($name).",".db_escape($phone).",".db_escape($email).")";
	else
		$sql = "UPDATE ".TB_PREF."pos_salesman SET name = ".db_escape($name).", phone = ".db_escape($phone).", email = ".db_escape($email)." WHERE id = ".db_escape($id);
	
	db_query($sql, 'Could not write salesman data');
}

function delete_pos_salesman($id) {
	$sql = "DELETE FROM ".TB_PREF."pos_salesman WHERE id = ".db_escape($id);;
	db_query($sql, 'could not delete salesman');
}

function get_pos_salesman($id=false, $all=false) {
	
	$sql = "SELECT * FROM ".TB_PREF."pos_salesman";
	$where = array();
	
	if(!$all)
		$where[] = "!inactive";
	if($id)
		$where[] = "id=".db_escape($id);
		
	if(count($where))
		$sql .= ' WHERE '.implode(' AND ', $where);

	$sql .= ' ORDER BY id';

	$result = db_query($sql, 'Could not get salesman data.');
	
	if($id)
		return db_fetch($result);
	else
		return $sql;
}

function salesman_used($id) {
	return  check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_salesman_trans WHERE salesman_id = ".db_escape($id));
}

//--------------------------------------------------------------------------

function db_has_pos_commission() {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_commission");
}

function write_pos_commission($id = false, $name, $commission) {
	
	if(!$id)
		$sql = "INSERT INTO ".TB_PREF."pos_commission(name, commission) VALUES (".db_escape($name).",".db_escape($commission).")";
	else
		$sql = "UPDATE ".TB_PREF."pos_commission SET name = ".db_escape($name).", commission = ".db_escape($commission)." WHERE id = ".db_escape($id);
	
	db_query($sql, 'Could not write commission data');
}

function delete_pos_commission($id) {
	
	$sql = "DELETE FROM ".TB_PREF."pos_commission WHERE id = ".db_escape($id);;
	db_query($sql, 'could not delete commission');
}

function get_pos_commission($id=false, $all=false) {
	
	$sql = "SELECT * FROM ".TB_PREF."pos_commission";
	$where = array();
	
	if(!$all)
		$where[] = "!inactive";
	if($id)
		$where[] = "id=".db_escape($id);
		
	if(count($where))
		$sql .= ' WHERE '.implode(' AND ', $where);

	$sql .= ' ORDER BY id';

	$result = db_query($sql, 'Could not get commission data.');
	
	if($id)
		return db_fetch($result);
	else
		return $sql;
}

function commission_used($id) {
	return  check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_salesman_trans WHERE commission_id = ".db_escape($id));
}

function save_salesman_trans_details($salesman_id, $trans_no, $stock_id, $quantity, $unit_price, $commission) {
	$sql = "INSERT INTO ".TB_PREF."pos_salesman_trans (salesman_id, debtor_trans_no, stock_id, quantity, unit_price, commission) VALUES (".db_escape($salesman_id).",".db_escape($trans_no).",".db_escape($stock_id).",".db_escape($quantity).",".db_escape($unit_price).",".db_escape($commission).")";

	db_query($sql, 'could not insert salesman trans details');
}

function db_has_credit_accounts() {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."bank_accounts WHERE account_type = ".BT_CREDIT);
}

function add_pos_credit_account($pos_id, $account) {
	$sql = "INSERT INTO ".TB_PREF."pos_credit_account(pos_id, account) VALUES (".db_escape($pos_id).",".db_escape($account).")";
	db_query($sql, 'could not insert pos acount');
}

function write_pos_credit_account($pos_id, $account) {
	$exist = check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_credit_account WHERE pos_id = ".db_escape($pos_id));

	if($exist)
		$sql = "UPDATE ".TB_PREF."pos_credit_account SET account = ".db_escape($account)." WHERE pos_id = ".db_escape($pos_id);
	else
		$sql = "INSERT INTO ".TB_PREF."pos_credit_account(pos_id, account) VALUES (".db_escape($pos_id).",".db_escape($account).")";

	db_query($sql, 'could not add/updated pos account');
}

function get_pos_credit_account($pos_id) {
	$sql = "SELECT p.pos_id, p.account, b.bank_account_name FROM ".TB_PREF."pos_credit_account AS p LEFT JOIN ".TB_PREF."bank_accounts AS b on p.account = b.id WHERE pos_id = ".db_escape($pos_id);
	$result = db_query($sql, 'could not get pos account');

	return db_fetch($result);
}

function delete_table_order($pos_id, $table_id) {
	$sql = "DELETE FROM ".TB_PREF."pos_orders WHERE pos_id = ".db_escape($pos_id)." AND table_id = ".db_escape($table_id);
	db_query($sql, 'could not delete pos order details');
}

function delete_table_item($pos_id, $table_id, $item_code) {
	$sql = "DELETE FROM ".TB_PREF."pos_orders WHERE pos_id = ".db_escape($pos_id)." AND table_id = ".db_escape($table_id)." AND item_code = ".db_escape($item_code);
	db_query($sql, 'could not delete pos order item');
}

function save_pos_order_item($pos_id, $table_id=0, $user_id, $item_code, $item_description, $units, $quantity, $price, $discount=0, $created_time, $ordered=0) {

	$sql = "INSERT INTO ".TB_PREF."pos_orders(pos_id, table_id, user_id, item_code, item_description, units, quantity, price, discount_percent, created_time, ordered) VALUES (".db_escape($pos_id).",".db_escape($table_id).",".db_escape($user_id).",".db_escape($item_code).",".db_escape($item_description).",".db_escape($units).",".db_escape($quantity).",".db_escape($price).",".db_escape($discount).",'".$created_time."',".$ordered.")";

	db_query($sql, 'could not write pos order details');
}

function get_table_order_details($pos_id, $table_id) {
	$sql = "SELECT table_id, user_id, item_code, item_description, units, quantity, price, discount_percent, created_time, ordered FROM ".TB_PREF."pos_orders WHERE pos_id = ".db_escape($pos_id)." AND table_id = ".db_escape($table_id);
	return  db_query($sql, 'could not get pos order details');
}

function get_ordered_item($pos_id, $table_id, $item_code, $price=false) {
	$sql = "SELECT quantity, ordered FROM ".TB_PREF."pos_orders WHERE pos_id = ".db_escape($pos_id)." AND table_id = ".db_escape($table_id)." AND item_code = ".db_escape($item_code);

	if($price)
		$sql .= "AND price = ".db_escape($price);

	$result = db_query($sql, 'could not get pos item details');
	return db_fetch($result);
}

function update_ordered_qty($pos_id, $table_id, $item_code, $qty, $old_qty, $price) {
	$sql = "UPDATE ".TB_PREF."pos_orders SET ordered = ".db_escape($old_qty).", quantity = ".db_escape($qty)." WHERE pos_id = ".db_escape($pos_id)." AND table_id = ".db_escape($table_id)." AND item_code = ".db_escape($item_code)." AND price = ".db_escape($price);
	db_query($sql, 'could not update pos order item');
}

function check_open_table($pos_id, $table_id) {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_orders WHERE pos_id = ".db_escape($pos_id)." AND table_id = ".db_escape($table_id));
}

function db_has_pos_shift() {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_shift");
}

function pos_shift_used($shift_id) {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_trans WHERE shift_id = ".db_escape($shift_id));
}

function write_pos_shift($id=false, $name, $start, $end) {
	if(!empty($id))
		$sql = "UPDATE ".TB_PREF."pos_shift SET shift_name = ".db_escape($name).", start_time = ".db_escape($start).", end_time = ".db_escape($end)." WHERE id = ".db_escape($id);
	else
		$sql = "INSERT INTO ".TB_PREF."pos_shift(shift_name, start_time, end_time) VALUES (".db_escape($name).", ".db_escape($start).", ".db_escape($end).")";
		
	db_query($sql, 'Could not write pos timing data');
}

function get_pos_shifts($id=false, $all=false) {
	$sql = "SELECT id, shift_name, start_time, end_time, TIME_FORMAT(start_time, '%H') AS start_hour, TIME_FORMAT(start_time, '%i') AS start_minute, TIME_FORMAT(end_time, '%H') AS end_hour, TIME_FORMAT(end_time, '%i') AS end_minute, inactive FROM ".TB_PREF."pos_shift";

	$where = array();
	
	if (!$all)
		$where[] = "!inactive";
	if (!empty($id))
		$where[] = "id=".db_escape($id);

	if (count($where))
		$sql .= ' WHERE '.implode(' AND ', $where);
		
	$result = db_query($sql, 'could not get pos timing');
	
	if(!empty($id))
		return db_fetch($result);
	else
		return $result;
}

function delete_pos_shift($id) {
	$sql = "DELETE FROM ".TB_PREF."pos_shift WHERE id = ".db_escape($id);
	db_query($sql, 'could not delete pos shift');
}

function check_duplicated_shift($id, $from, $to) {
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."pos_shift WHERE (TIME_FORMAT('".$from."', '%H:%i:%s') BETWEEN start_time AND end_time OR TIME_FORMAT('".$to."', '%H:%i:%s') BETWEEN start_time AND end_time) AND id != ".db_escape($id));
}

function get_pos_sales_sql($pos_id=false, $order_type=false, $customer_id=false, $from, $to, $item=false, $ref='') {
	$sql = "SELECT trans.trans_no, trans.reference, u.user_id, pt.order_type, debtor.name, pt.trans_time, DATE_FORMAT(pt.trans_time,'%H:%i:%s'), (trans.ov_amount + trans.ov_gst + trans.ov_freight 
			+ trans.ov_freight_tax + trans.ov_discount)	AS TotalAmount, trans.type, trans.debtor_no AS debtor_no, Sum(line.quantity-line.qty_done) AS Outstanding 
			FROM "
			.TB_PREF."debtor_trans as trans, "
			.TB_PREF."debtor_trans_details as line, "
			.TB_PREF."debtors_master as debtor, "
			.TB_PREF."pos_trans as pt, "
			.TB_PREF."audit_trail at, "
			.TB_PREF."users u 
			WHERE debtor.debtor_no = trans.debtor_no 
			AND trans.trans_no = line.debtor_trans_no AND trans.type = line.debtor_trans_type 
			AND pt.trans_no = trans.trans_no 
			AND at.trans_no = trans.trans_no 
			AND at.type = ".ST_SALESINVOICE." 
			AND at.user = u.id 
			AND trans.type = ".ST_SALESINVOICE." 
			AND trans.tran_date >= '".date2sql($from)."'
			AND trans.tran_date <= '".date2sql($to)."'";
	if(!empty($pos_id))
		$sql .= " AND pt.pos_id = ".db_escape($pos_id);
	if(!empty($customer_id))
		$sql .= " AND trans.debtor_no = ".db_escape($customer_id);
	if(!empty($item))
		$sql .= " AND line.stock_id = ".db_escape($item);
	if(!empty($order_type))
		$sql .= " AND pt.order_type = ".db_escape($order_type);
	if(!empty($ref))
		$sql .= " AND (trans.trans_no LIKE ".db_escape('%'.$ref.'%')." OR trans.reference LIKE ".db_escape('%'.$ref.'%')." OR trans.debtor_no LIKE ".db_escape('%'.$ref.'%')." OR debtor.name LIKE ".db_escape('%'.$ref.'%')." OR u.user_id LIKE ".db_escape('%'.$ref.'%').")";
	$sql .= " GROUP BY trans.trans_no";

	return $sql;
}

function root_folder_name() {
	$this_dir = explode('/modules', dirname($_SERVER['PHP_SELF']));
	return $this_dir[0];
}

function save_pos_bank_trans($bank_trans, $trans_no, $trans_type) {
	$sql = "INSERT INTO ".TB_PREF."pos_bank_trans(bank_trans_no, pos_trans_no, trans_type) VALUES (".db_escape($bank_trans).",".db_escape($trans_no).",".db_escape($trans_type).")";
	db_query($sql, 'could not insert transaction row');
}
