public function getCouponsReport($data = array(), $mode = 'default')
{
$filter = isset($data['filter']) ? $data['filter'] : array();
$total_sql = '';
if ($mode == 'total_only') {
$total_sql = "COUNT(DISTINCT o.coupon_id) AS total ";
} else {
//condition if coupon is deleted
$total_sql = "\tIF(cd.name IS NULL OR cd.name = '', ot.title, cd.name) as coupon_name,\n\t\t\t\t\t\t\tc.code, \n\t\t\t\t\t\t\tCOUNT(DISTINCT o.order_id), \n\t\t\t\t\t\t\tSUM(o.total) AS total, \n\t\t\t\t\t\t\tSUM(ot.value) AS discount_total, \n\t\t\t\t\t\t\tCOUNT(o.order_id) AS orders ";
}
$sql = "SELECT {$total_sql} FROM `" . $this->db->table("orders") . "` o \n\t\t\t\t\tLEFT JOIN `" . $this->db->table("coupons") . "` c ON (o.coupon_id = c.coupon_id) \n\t\t\t\t\tLEFT JOIN `" . $this->db->table("coupon_descriptions") . "` cd ON (c.coupon_id = cd.coupon_id) \n\t\t\t\t\tLEFT JOIN `" . $this->db->table("order_totals") . "` ot ON (o.order_id = ot.order_id) \n\t\t\t\t\tWHERE ot.type = 'discount' ";
if (isset($filter['date_start'])) {
$date_start = dateDisplay2ISO($filter['date_start'], $this->language->get('date_format_short'));
} else {
$date_start = Jdate::toJalali();
}
if (isset($filter['date_end'])) {
$date_end = dateDisplay2ISO($filter['date_end'], $this->language->get('date_format_short'));
} else {
$date_end = Jdate::date();
}
$sql .= " AND (DATE_FORMAT(o.date_added,'%Y-%m-%d') >= DATE_FORMAT('" . $this->db->escape($date_start) . "','%Y-%m-%d') \n\t\t\t\t AND DATE_FORMAT(o.date_added,'%Y-%m-%d') <= DATE_FORMAT('" . $this->db->escape($date_end) . "','%Y-%m-%d') )";
//If for total, we done bulding the query
if ($mode == 'total_only') {
$query = $this->db->query($sql);
return $query->row['total'];
}
$sql .= " GROUP BY o.coupon_id ";
$sort_data = array('coupon_name' => 'cd.name', 'code' => 'c.code', 'orders' => 'COUNT(o.order_id)', 'total' => 'SUM(o.total)', 'discount_total' => 'SUM(ot.value)');
if (isset($data['sort']) && array_key_exists($data['sort'], $sort_data)) {
$sql .= " ORDER BY " . $sort_data[$data['sort']];
} else {
$sql .= " ORDER BY c.coupon_id";
}
$sql .= " " . $this->db->escape($data['order']);
if (isset($data['start']) || isset($data['limit'])) {
if ($data['start'] < 0) {
$data['start'] = 0;
}
if ($data['limit'] < 1) {
$data['limit'] = 20;
}
$sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
}
$query = $this->db->query($sql);
return $query->rows;
}
