1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
<?php
class ModelLocalisationGeoZone extends Model {
public function addGeoZone($data) {
$this->db->query("INSERT INTO " . DB_PREFIX . "geo_zone SET name = '" . $this->db->escape($data['name']) . "', description = '" . $this->db->escape($data['description']) . "', date_added = NOW()");
$geo_zone_id = $this->db->getLastId();
if (isset($data['zone_to_geo_zone'])) {
foreach ($data['zone_to_geo_zone'] as $value) {
$this->db->query("DELETE FROM " . DB_PREFIX . "zone_to_geo_zone WHERE geo_zone_id = '" . (int)$geo_zone_id . "' AND country_id = '" . (int)$value['country_id'] . "' AND zone_id = '" . (int)$value['zone_id'] . "'");
$this->db->query("INSERT INTO " . DB_PREFIX . "zone_to_geo_zone SET country_id = '" . (int)$value['country_id'] . "', zone_id = '" . (int)$value['zone_id'] . "', geo_zone_id = '" . (int)$geo_zone_id . "', date_added = NOW()");
}
}
$this->cache->delete('geo_zone');
return $geo_zone_id;
}
public function editGeoZone($geo_zone_id, $data) {
$this->db->query("UPDATE " . DB_PREFIX . "geo_zone SET name = '" . $this->db->escape($data['name']) . "', description = '" . $this->db->escape($data['description']) . "', date_modified = NOW() WHERE geo_zone_id = '" . (int)$geo_zone_id . "'");
$this->db->query("DELETE FROM " . DB_PREFIX . "zone_to_geo_zone WHERE geo_zone_id = '" . (int)$geo_zone_id . "'");
if (isset($data['zone_to_geo_zone'])) {
foreach ($data['zone_to_geo_zone'] as $value) {
$this->db->query("DELETE FROM " . DB_PREFIX . "zone_to_geo_zone WHERE geo_zone_id = '" . (int)$geo_zone_id . "' AND country_id = '" . (int)$value['country_id'] . "' AND zone_id = '" . (int)$value['zone_id'] . "'");
$this->db->query("INSERT INTO " . DB_PREFIX . "zone_to_geo_zone SET country_id = '" . (int)$value['country_id'] . "', zone_id = '" . (int)$value['zone_id'] . "', geo_zone_id = '" . (int)$geo_zone_id . "', date_added = NOW()");
}
}
$this->cache->delete('geo_zone');
}
public function deleteGeoZone($geo_zone_id) {
$this->db->query("DELETE FROM " . DB_PREFIX . "geo_zone WHERE geo_zone_id = '" . (int)$geo_zone_id . "'");
$this->db->query("DELETE FROM " . DB_PREFIX . "zone_to_geo_zone WHERE geo_zone_id = '" . (int)$geo_zone_id . "'");
$this->cache->delete('geo_zone');
}
public function getGeoZone($geo_zone_id) {
$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "geo_zone WHERE geo_zone_id = '" . (int)$geo_zone_id . "'");
return $query->row;
}
public function getGeoZones($data = array()) {
if ($data) {
$sql = "SELECT * FROM " . DB_PREFIX . "geo_zone";
$sort_data = array(
'name',
'description'
);
if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
$sql .= " ORDER BY " . $data['sort'];
} else {
$sql .= " ORDER BY name";
}
if (isset($data['order']) && ($data['order'] == 'DESC')) {
$sql .= " DESC";
} else {
$sql .= " ASC";
}
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;
} else {
$geo_zone_data = $this->cache->get('geo_zone');
if (!$geo_zone_data) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "geo_zone ORDER BY name ASC");
$geo_zone_data = $query->rows;
$this->cache->set('geo_zone', $geo_zone_data);
}
return $geo_zone_data;
}
}
public function getTotalGeoZones() {
$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "geo_zone");
return $query->row['total'];
}
public function getZoneToGeoZones($geo_zone_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "zone_to_geo_zone WHERE geo_zone_id = '" . (int)$geo_zone_id . "'");
return $query->rows;
}
public function getTotalZoneToGeoZoneByGeoZoneId($geo_zone_id) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "zone_to_geo_zone WHERE geo_zone_id = '" . (int)$geo_zone_id . "'");
return $query->row['total'];
}
public function getTotalZoneToGeoZoneByCountryId($country_id) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "zone_to_geo_zone WHERE country_id = '" . (int)$country_id . "'");
return $query->row['total'];
}
public function getTotalZoneToGeoZoneByZoneId($zone_id) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "zone_to_geo_zone WHERE zone_id = '" . (int)$zone_id . "'");
return $query->row['total'];
}
}
|