public function newOrderList($viewed = '')
{
$merchant_id = Yii::app()->functions->getMerchantID();
$and = '';
/*if (is_numeric($viewed)){
$and.=" AND viewed='0'";
}*/
$db_ext = new DbExt();
$stmt = "\r\n \t SELECT * FROM\r\n \t {{order}}\r\n \t WHERE \t \t \r\n \t date_created like '" . date('Y-m-d') . "%'\r\n \t AND\r\n \t merchant_id ='{$merchant_id}'\r\n \t AND\r\n \t viewed='1'\r\n \t AND status NOT IN ('" . initialStatus() . "')\r\n \t ORDER BY date_created DESC\r\n \t";
if ($res = $db_ext->rst($stmt)) {
return $res;
}
return false;
}
public function rptAdminSalesRpt()
{
$and = '';
if (isset($this->data['start_date']) && isset($this->data['end_date'])) {
if (!empty($this->data['start_date']) && !empty($this->data['end_date'])) {
$and = " AND date_created BETWEEN '" . $this->data['start_date'] . " 00:00:00' AND \r\n\t \t\t '" . $this->data['end_date'] . " 23:59:00'\r\n\t \t\t ";
}
}
$order_status_id = '';
$or = '';
if (isset($this->data['stats_id'])) {
if (is_array($this->data['stats_id']) && count($this->data['stats_id']) >= 1) {
foreach ($this->data['stats_id'] as $stats_id) {
$order_status_id .= "'{$stats_id}',";
}
if (!empty($order_status_id)) {
$order_status_id = substr($order_status_id, 0, -1);
}
}
}
if (!empty($order_status_id)) {
$and .= " AND status IN ({$order_status_id})";
}
$DbExt = new DbExt();
$merchant_id = isset($this->data['merchant_id']) ? $this->data['merchant_id'] : '';
$stmt = "SELECT a.*,\r\n\t \t(\r\n\t \tselect concat(first_name,' ',last_name)\r\n\t \tfrom\r\n\t \t{{client}}\r\n\t \twhere\r\n\t \tclient_id=a.client_id\r\n\t \t) as client_name,\r\n\t \t\r\n\t \t(\r\n\t \tselect group_concat(item_name)\r\n\t \tfrom\r\n\t \t{{order_details}}\r\n\t \twhere\r\n\t \torder_id=a.order_id\r\n\t \t) as item\r\n\t \t\r\n\t \tFROM\r\n\t \t{{order}} a\r\n\t \tWHERE\r\n\t \tmerchant_id='{$merchant_id}'\r\n\t \tAND status NOT IN ('" . initialStatus() . "')\t \t\r\n\t \t{$and}\r\n\t \tORDER BY order_id DESC\r\n\t \tLIMIT 0,2000\r\n\t \t";
/*dump($this->data);
dump($stmt);*/
$_SESSION['kr_export_stmt'] = $stmt;
if ($res = $DbExt->rst($stmt)) {
foreach ($res as $val) {
$action = "<a data-id=\"" . $val['order_id'] . "\" class=\"edit-order\" href=\"javascript:\">" . Yii::t("default", "Edit") . "</a>";
$action .= "<a data-id=\"" . $val['order_id'] . "\" class=\"view-receipt\" href=\"javascript:\">" . Yii::t("default", "View") . "</a>";
/*$date=prettyDate($val['date_created'],true);
$date=Yii::app()->functions->translateDate($date);*/
$date = FormatDateTime($val['date_created']);
$feed_data['aaData'][] = array($val['order_id'], ucwords($val['client_name']), $val['item'], ucwords(Yii::t("default", $val['trans_type'])), strtoupper(Yii::t("default", $val['payment_type'])), prettyFormat($val['sub_total'], $merchant_id), prettyFormat($val['tax'], $merchant_id), prettyFormat($val['total_w_tax'], $merchant_id), ucwords($val['status']), $date);
}
$this->otableOutput($feed_data);
}
$this->otableNodata();
}
public function merchantCommission()
{
$and = '';
$and_date = '';
if (isset($this->data['start_date']) && isset($this->data['end_date'])) {
if (!empty($this->data['start_date']) && !empty($this->data['end_date'])) {
$and = " AND a.date_created BETWEEN '" . $this->data['start_date'] . " 00:00:00' AND \n\t \t\t '" . $this->data['end_date'] . " 23:59:00'\n\t \t\t ";
$and_date = " AND date_created BETWEEN '" . $this->data['start_date'] . " 00:00:00' AND \n\t \t\t '" . $this->data['end_date'] . " 23:59:00'\n\t \t\t ";
}
}
if ($this->data['query'] == "last15") {
$start_date = date("Y-m-d", strtotime('-15 days'));
$end_date = date("Y-m-d");
$and = " AND a.date_created BETWEEN '" . $start_date . " 00:00:00' AND \n\t\t \t\t '" . $end_date . " 23:59:00'\n\t\t \t\t ";
$and_date = " AND date_created BETWEEN '" . $start_date . " 00:00:00' AND \n\t\t \t\t '" . $end_date . " 23:59:00'\n\t\t \t\t ";
} elseif ($this->data['query'] == "last30") {
$start_date = date("Y-m-d", strtotime('-30 days'));
$end_date = date("Y-m-d");
$and = " AND a.date_created BETWEEN '" . $start_date . " 00:00:00' AND \n\t\t \t\t '" . $end_date . " 23:59:00'\n\t\t \t\t ";
$and_date = " AND date_created BETWEEN '" . $start_date . " 00:00:00' AND \n\t\t \t\t '" . $end_date . " 23:59:00'\n\t\t \t\t ";
} elseif ($this->data['query'] == "month") {
$query_date = $this->data['query_date'];
$start_date = date('Y-m-01', strtotime($query_date));
$end_date = date('Y-m-t', strtotime($query_date));
$and = " AND a.date_created BETWEEN '" . $start_date . " 00:00:00' AND \n\t\t \t\t '" . $end_date . " 23:59:00'\n\t\t \t\t ";
$and_date = " AND date_created BETWEEN '" . $start_date . " 00:00:00' AND \n\t\t \t\t '" . $end_date . " 23:59:00'\n\t\t \t\t ";
}
$order_status_id = '';
$or = '';
if (isset($this->data['stats_id'])) {
if (is_array($this->data['stats_id']) && count($this->data['stats_id']) >= 1) {
foreach ($this->data['stats_id'] as $stats_id) {
$order_status_id .= "'{$stats_id}',";
}
if (!empty($order_status_id)) {
$order_status_id = substr($order_status_id, 0, -1);
}
}
}
if (!empty($order_status_id)) {
$where = " WHERE a.status IN ({$order_status_id})";
$and_date .= " AND status IN ({$order_status_id})";
} else {
$where = " WHERE a.status NOT IN ('" . initialStatus() . "')";
$and_date .= "AND status NOT IN ('" . initialStatus() . "')";
}
if ($this->data['merchant_id'] >= 1) {
$and .= " AND a.merchant_id='" . $this->data['merchant_id'] . "' ";
}
if (isset($this->data['payment_type'])) {
if ($this->data['payment_type'] == 2) {
// cash
$and_date .= " AND payment_type IN ('cod','pyr','ccr') ";
$and .= " AND payment_type IN ('cod','pyr','ccr') ";
} else {
if ($this->data['payment_type'] == 3) {
// card
$and_date .= " AND payment_type NOT IN ('cod','pyr','ccr') ";
$and .= " AND payment_type NOT IN ('cod','pyr','ccr') ";
}
}
}
$DbExt = new DbExt();
$stmt = "SELECT a.*,b.is_commission,\n\t \t(\n\t \tselect restaurant_name \n\t \tfrom\n\t \t{{merchant}}\n\t \twhere merchant_id = a.merchant_id \n\t \t) as merchant_name,\n\t \t\n\t \t(\n\t \tselect sum(total_w_tax) \n\t \tfrom\n\t \t{{order}}\n\t \twhere merchant_id = a.merchant_id \t \n\t \t{$and_date} \t\t \n\t \t) as total_order,\n\t \t\n\t \t(\n\t \tselect sum(total_commission)\n\t \tfrom\n\t \t{{order}}\n\t \twhere merchant_id = a.merchant_id \t \n\t \t{$and_date} \t\t \n\t \t) as total_commission\n\t \t\n\t \tFROM\n\t \t{{order}} a\t \t \n\t \tleft join {{merchant}} b\n\t\t\tOn\n\t\t\ta.merchant_id=b.merchant_id\n\t \t \t \n\t \t{$where}\n\t \t{$and}\t \t\n\t \tAND b.is_commission='2'\n\t \t\n\t \tGROUP BY merchant_id\n\t \tORDER BY order_id DESC\n\t \tLIMIT 0,2000\n\t \t";
if (isset($_GET['debug'])) {
dump($this->data);
dump($stmt);
}
$_SESSION['kr_export_stmt'] = $stmt;
if ($res = $DbExt->rst($stmt)) {
if (isset($_GET['debug'])) {
dump($res);
}
$total_commission = 0;
foreach ($res as $val) {
$link = websiteUrl() . "/admin/merchantcommissiondetails";
$link .= "/mtid/" . $val['merchant_id'];
$link .= "/where/" . $where;
$link .= "/and/" . $and;
/*$action="<a class=\"view-details\" data-id=\"$val[merchant_id]\" href=\"javascript:;\" data-where=\"$where\" data-and=\"$and\">".
Yii::t("default","Details").
"</a>";*/
$total_commission += $val['total_commission'];
$action = "<a href=\"{$link}\" >" . Yii::t("default", "Details") . "</a>";
$date = prettyDate($val['date_created'], true);
$date = Yii::app()->functions->translateDate($date);
$feed_data['aaData'][] = array($val['merchant_id'], $val['merchant_name'], displayPrice(adminCurrencySymbol(), normalPrettyPrice($val['total_order'])), displayPrice(adminCurrencySymbol(), normalPrettyPrice($val['total_commission'])), $action);
}
$feed_data['total_commission'] = displayPrice(adminCurrencySymbol(), normalPrettyPrice($total_commission));
$this->otableOutput($feed_data);
}
$this->otableNodata();
}
