shop.balmet.com

Unnamed repository; edit this file 'description' to name the repository.
Log | Files | Refs | README

customer_transaction.php (2338B)

      1 <?php
      2 class ModelExtensionReportCustomerTransaction extends Model {
      3 	public function getTransactions($data = array()) {
      4 		$sql = "SELECT ct.customer_id, CONCAT(c.firstname, ' ', c.lastname) AS customer, c.email, cgd.name AS customer_group, c.status, SUM(ct.amount) AS total FROM `" . DB_PREFIX . "customer_transaction` ct LEFT JOIN `" . DB_PREFIX . "customer` c ON (ct.customer_id = c.customer_id) LEFT JOIN `" . DB_PREFIX . "customer_group_description` cgd ON (c.customer_group_id = cgd.customer_group_id) WHERE cgd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
      5 
      6 		if (!empty($data['filter_date_start'])) {
      7 			$sql .= " AND DATE(ct.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
      8 		}
      9 
     10 		if (!empty($data['filter_date_end'])) {
     11 			$sql .= " AND DATE(ct.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
     12 		}
     13 
     14 		if (!empty($data['filter_customer'])) {
     15 			$sql .= " AND CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'";
     16 		}
     17 
     18 		$sql .= " GROUP BY ct.customer_id ORDER BY total DESC";
     19 
     20 		if (isset($data['start']) || isset($data['limit'])) {
     21 			if ($data['start'] < 0) {
     22 				$data['start'] = 0;
     23 			}
     24 
     25 			if ($data['limit'] < 1) {
     26 				$data['limit'] = 20;
     27 			}
     28 
     29 			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
     30 		}
     31 
     32 		$query = $this->db->query($sql);
     33 
     34 		return $query->rows;
     35 	}
     36 
     37 	public function getTotalTransactions($data = array()) {
     38 		$sql = "SELECT COUNT(DISTINCT ct.customer_id) AS total FROM `" . DB_PREFIX . "customer_transaction` ct LEFT JOIN `" . DB_PREFIX . "customer` c ON (ct.customer_id = c.customer_id)";
     39 
     40 		$implode = array();
     41 
     42 		if (!empty($data['filter_date_start'])) {
     43 			$implode[] = "DATE(ct.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
     44 		}
     45 
     46 		if (!empty($data['filter_date_end'])) {
     47 			$implode[] = "DATE(ct.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
     48 		}
     49 
     50 		if (!empty($data['filter_customer'])) {
     51 			$implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'";
     52 		}
     53 
     54 		if ($implode) {
     55 			$sql .= " WHERE " . implode(" AND ", $implode);
     56 		}
     57 
     58 		$query = $this->db->query($sql);
     59 
     60 		return $query->row['total'];
     61 	}
     62 }