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; }