customer.php (13163B)
1 <?php 2 class ModelExtensionReportCustomer extends Model { 3 public function getTotalCustomersByDay() { 4 $customer_data = array(); 5 6 for ($i = 0; $i < 24; $i++) { 7 $customer_data[$i] = array( 8 'hour' => $i, 9 'total' => 0 10 ); 11 } 12 13 $query = $this->db->query("SELECT COUNT(*) AS total, HOUR(date_added) AS hour FROM `" . DB_PREFIX . "customer` WHERE DATE(date_added) = DATE(NOW()) GROUP BY HOUR(date_added) ORDER BY date_added ASC"); 14 15 foreach ($query->rows as $result) { 16 $customer_data[$result['hour']] = array( 17 'hour' => $result['hour'], 18 'total' => $result['total'] 19 ); 20 } 21 22 return $customer_data; 23 } 24 25 public function getTotalCustomersByWeek() { 26 $customer_data = array(); 27 28 $date_start = strtotime('-' . date('w') . ' days'); 29 30 for ($i = 0; $i < 7; $i++) { 31 $date = date('Y-m-d', $date_start + ($i * 86400)); 32 33 $customer_data[date('w', strtotime($date))] = array( 34 'day' => date('D', strtotime($date)), 35 'total' => 0 36 ); 37 } 38 39 $query = $this->db->query("SELECT COUNT(*) AS total, date_added FROM `" . DB_PREFIX . "customer` WHERE DATE(date_added) >= DATE('" . $this->db->escape(date('Y-m-d', $date_start)) . "') GROUP BY DAYNAME(date_added)"); 40 41 foreach ($query->rows as $result) { 42 $customer_data[date('w', strtotime($result['date_added']))] = array( 43 'day' => date('D', strtotime($result['date_added'])), 44 'total' => $result['total'] 45 ); 46 } 47 48 return $customer_data; 49 } 50 51 public function getTotalCustomersByMonth() { 52 $customer_data = array(); 53 54 for ($i = 1; $i <= date('t'); $i++) { 55 $date = date('Y') . '-' . date('m') . '-' . $i; 56 57 $customer_data[date('j', strtotime($date))] = array( 58 'day' => date('d', strtotime($date)), 59 'total' => 0 60 ); 61 } 62 63 $query = $this->db->query("SELECT COUNT(*) AS total, date_added FROM `" . DB_PREFIX . "customer` WHERE DATE(date_added) >= '" . $this->db->escape(date('Y') . '-' . date('m') . '-1') . "' GROUP BY DATE(date_added)"); 64 65 foreach ($query->rows as $result) { 66 $customer_data[date('j', strtotime($result['date_added']))] = array( 67 'day' => date('d', strtotime($result['date_added'])), 68 'total' => $result['total'] 69 ); 70 } 71 72 return $customer_data; 73 } 74 75 public function getTotalCustomersByYear() { 76 $customer_data = array(); 77 78 for ($i = 1; $i <= 12; $i++) { 79 $customer_data[$i] = array( 80 'month' => date('M', mktime(0, 0, 0, $i)), 81 'total' => 0 82 ); 83 } 84 85 $query = $this->db->query("SELECT COUNT(*) AS total, date_added FROM `" . DB_PREFIX . "customer` WHERE YEAR(date_added) = YEAR(NOW()) GROUP BY MONTH(date_added)"); 86 87 foreach ($query->rows as $result) { 88 $customer_data[date('n', strtotime($result['date_added']))] = array( 89 'month' => date('M', strtotime($result['date_added'])), 90 'total' => $result['total'] 91 ); 92 } 93 94 return $customer_data; 95 } 96 97 public function getOrders($data = array()) { 98 $sql = "SELECT c.customer_id, CONCAT(c.firstname, ' ', c.lastname) AS customer, c.email, cgd.name AS customer_group, c.status, o.order_id, SUM(op.quantity) as products, o.total AS total FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "order_product` op ON (o.order_id = op.order_id) LEFT JOIN `" . DB_PREFIX . "customer` c ON (o.customer_id = c.customer_id) LEFT JOIN `" . DB_PREFIX . "customer_group_description` cgd ON (c.customer_group_id = cgd.customer_group_id) WHERE o.customer_id > 0 AND cgd.language_id = '" . (int)$this->config->get('config_language_id') . "'"; 99 100 if (!empty($data['filter_date_start'])) { 101 $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 102 } 103 104 if (!empty($data['filter_date_end'])) { 105 $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 106 } 107 108 if (!empty($data['filter_customer'])) { 109 $sql .= " AND CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'"; 110 } 111 112 if (!empty($data['filter_order_status_id'])) { 113 $sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 114 } else { 115 $sql .= " AND o.order_status_id > '0'"; 116 } 117 118 $sql .= " GROUP BY o.order_id"; 119 120 $sql = "SELECT t.customer_id, t.customer, t.email, t.customer_group, t.status, COUNT(DISTINCT t.order_id) AS orders, SUM(t.products) AS products, SUM(t.total) AS total FROM (" . $sql . ") AS t GROUP BY t.customer_id ORDER BY total DESC"; 121 122 if (isset($data['start']) || isset($data['limit'])) { 123 if ($data['start'] < 0) { 124 $data['start'] = 0; 125 } 126 127 if ($data['limit'] < 1) { 128 $data['limit'] = 20; 129 } 130 131 $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; 132 } 133 134 $query = $this->db->query($sql); 135 136 return $query->rows; 137 } 138 139 public function getTotalOrders($data = array()) { 140 $sql = "SELECT COUNT(DISTINCT o.customer_id) AS total FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "customer` c ON (o.customer_id = c.customer_id) WHERE o.customer_id > '0'"; 141 142 if (!empty($data['filter_date_start'])) { 143 $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 144 } 145 146 if (!empty($data['filter_date_end'])) { 147 $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 148 } 149 150 if (!empty($data['filter_customer'])) { 151 $sql .= " AND CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'"; 152 } 153 154 if (!empty($data['filter_order_status_id'])) { 155 $sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 156 } else { 157 $sql .= " AND o.order_status_id > '0'"; 158 } 159 160 $query = $this->db->query($sql); 161 162 return $query->row['total']; 163 } 164 165 public function getRewardPoints($data = array()) { 166 $sql = "SELECT cr.customer_id, CONCAT(c.firstname, ' ', c.lastname) AS customer, c.email, cgd.name AS customer_group, c.status, SUM(cr.points) AS points, COUNT(o.order_id) AS orders, SUM(o.total) AS total FROM " . DB_PREFIX . "customer_reward cr LEFT JOIN `" . DB_PREFIX . "customer` c ON (cr.customer_id = c.customer_id) LEFT JOIN " . DB_PREFIX . "customer_group_description cgd ON (c.customer_group_id = cgd.customer_group_id) LEFT JOIN `" . DB_PREFIX . "order` o ON (cr.order_id = o.order_id) WHERE cgd.language_id = '" . (int)$this->config->get('config_language_id') . "'"; 167 168 if (!empty($data['filter_date_start'])) { 169 $sql .= " AND DATE(cr.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 170 } 171 172 if (!empty($data['filter_date_end'])) { 173 $sql .= " AND DATE(cr.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 174 } 175 176 if (!empty($data['filter_customer'])) { 177 $sql .= " AND CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'"; 178 } 179 180 $sql .= " GROUP BY cr.customer_id ORDER BY points DESC"; 181 182 if (isset($data['start']) || isset($data['limit'])) { 183 if ($data['start'] < 0) { 184 $data['start'] = 0; 185 } 186 187 if ($data['limit'] < 1) { 188 $data['limit'] = 20; 189 } 190 191 $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; 192 } 193 194 $query = $this->db->query($sql); 195 196 return $query->rows; 197 } 198 199 public function getTotalRewardPoints($data = array()) { 200 $sql = "SELECT COUNT(DISTINCT cr.customer_id) AS total FROM `" . DB_PREFIX . "customer_reward` cr LEFT JOIN `" . DB_PREFIX . "customer` c ON (cr.customer_id = c.customer_id)"; 201 202 $implode = array(); 203 204 if (!empty($data['filter_date_start'])) { 205 $implode[] = "DATE(cr.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 206 } 207 208 if (!empty($data['filter_date_end'])) { 209 $implode[] = "DATE(cr.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 210 } 211 212 if (!empty($data['filter_customer'])) { 213 $implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'"; 214 } 215 216 if ($implode) { 217 $sql .= " WHERE " . implode(" AND ", $implode); 218 } 219 220 $query = $this->db->query($sql); 221 222 return $query->row['total']; 223 } 224 225 public function getCustomerActivities($data = array()) { 226 $sql = "SELECT ca.customer_activity_id, ca.customer_id, ca.key, ca.data, ca.ip, ca.date_added FROM " . DB_PREFIX . "customer_activity ca LEFT JOIN " . DB_PREFIX . "customer c ON (ca.customer_id = c.customer_id)"; 227 228 $implode = array(); 229 230 if (!empty($data['filter_date_start'])) { 231 $implode[] = "DATE(ca.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 232 } 233 234 if (!empty($data['filter_date_end'])) { 235 $implode[] = "DATE(ca.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 236 } 237 238 if (!empty($data['filter_customer'])) { 239 $implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'"; 240 } 241 242 if (!empty($data['filter_ip'])) { 243 $implode[] = "ca.ip LIKE '" . $this->db->escape($data['filter_ip']) . "'"; 244 } 245 246 if ($implode) { 247 $sql .= " WHERE " . implode(" AND ", $implode); 248 } 249 250 $sql .= " ORDER BY ca.date_added DESC"; 251 252 if (isset($data['start']) || isset($data['limit'])) { 253 if ($data['start'] < 0) { 254 $data['start'] = 0; 255 } 256 257 if ($data['limit'] < 1) { 258 $data['limit'] = 20; 259 } 260 261 $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; 262 } 263 264 $query = $this->db->query($sql); 265 266 return $query->rows; 267 } 268 269 public function getTotalCustomerActivities($data = array()) { 270 $sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "customer_activity` ca LEFT JOIN " . DB_PREFIX . "customer c ON (ca.customer_id = c.customer_id)"; 271 272 $implode = array(); 273 274 if (!empty($data['filter_date_start'])) { 275 $implode[] = "DATE(ca.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 276 } 277 278 if (!empty($data['filter_date_end'])) { 279 $implode[] = "DATE(ca.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 280 } 281 282 if (!empty($data['filter_customer'])) { 283 $implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'"; 284 } 285 286 if (!empty($data['filter_ip'])) { 287 $implode[] = "ca.ip LIKE '" . $this->db->escape($data['filter_ip']) . "'"; 288 } 289 290 if ($implode) { 291 $sql .= " WHERE " . implode(" AND ", $implode); 292 } 293 294 $query = $this->db->query($sql); 295 296 return $query->row['total']; 297 } 298 299 public function getCustomerSearches($data = array()) { 300 $sql = "SELECT cs.customer_id, cs.keyword, cs.category_id, cs.products, cs.ip, cs.date_added, CONCAT(c.firstname, ' ', c.lastname) AS customer FROM " . DB_PREFIX . "customer_search cs LEFT JOIN " . DB_PREFIX . "customer c ON (cs.customer_id = c.customer_id)"; 301 302 $implode = array(); 303 304 if (!empty($data['filter_date_start'])) { 305 $implode[] = "DATE(cs.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 306 } 307 308 if (!empty($data['filter_date_end'])) { 309 $implode[] = "DATE(cs.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 310 } 311 312 if (!empty($data['filter_keyword'])) { 313 $implode[] = "cs.keyword LIKE '" . $this->db->escape($data['filter_keyword']) . "%'"; 314 } 315 316 if (!empty($data['filter_customer'])) { 317 $implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'"; 318 } 319 320 if (!empty($data['filter_ip'])) { 321 $implode[] = "cs.ip LIKE '" . $this->db->escape($data['filter_ip']) . "'"; 322 } 323 324 if ($implode) { 325 $sql .= " WHERE " . implode(" AND ", $implode); 326 } 327 328 $sql .= " ORDER BY cs.date_added DESC"; 329 330 if (isset($data['start']) || isset($data['limit'])) { 331 if ($data['start'] < 0) { 332 $data['start'] = 0; 333 } 334 335 if ($data['limit'] < 1) { 336 $data['limit'] = 20; 337 } 338 339 $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; 340 } 341 342 $query = $this->db->query($sql); 343 344 return $query->rows; 345 } 346 347 public function getTotalCustomerSearches($data = array()) { 348 $sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "customer_search` cs LEFT JOIN " . DB_PREFIX . "customer c ON (cs.customer_id = c.customer_id)"; 349 350 $implode = array(); 351 352 if (!empty($data['filter_date_start'])) { 353 $implode[] = "DATE(cs.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 354 } 355 356 if (!empty($data['filter_date_end'])) { 357 $implode[] = "DATE(cs.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 358 } 359 360 if (!empty($data['filter_keyword'])) { 361 $implode[] = "cs.keyword LIKE '" . $this->db->escape($data['filter_keyword']) . "%'"; 362 } 363 364 if (!empty($data['filter_customer'])) { 365 $implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'"; 366 } 367 368 if (!empty($data['filter_ip'])) { 369 $implode[] = "cs.ip LIKE '" . $this->db->escape($data['filter_ip']) . "'"; 370 } 371 372 if ($implode) { 373 $sql .= " WHERE " . implode(" AND ", $implode); 374 } 375 376 $query = $this->db->query($sql); 377 378 return $query->row['total']; 379 } 380 }