|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* |
|
4
|
|
|
* Advertisement management. An extension for the phpBB Forum Software package. |
|
5
|
|
|
* |
|
6
|
|
|
* @copyright (c) 2017 phpBB Limited <https://www.phpbb.com> |
|
7
|
|
|
* @license GNU General Public License, version 2 (GPL-2.0) |
|
8
|
|
|
* |
|
9
|
|
|
*/ |
|
10
|
|
|
|
|
11
|
|
|
namespace phpbb\admanagement\ad; |
|
12
|
|
|
|
|
13
|
|
|
class manager |
|
14
|
|
|
{ |
|
15
|
|
|
/** @var \phpbb\db\driver\driver_interface */ |
|
16
|
|
|
protected $db; |
|
17
|
|
|
|
|
18
|
|
|
/** @var string */ |
|
19
|
|
|
protected $ads_table; |
|
20
|
|
|
|
|
21
|
|
|
/** @var string */ |
|
22
|
|
|
protected $ad_locations_table; |
|
23
|
|
|
|
|
24
|
|
|
/** |
|
25
|
|
|
* Constructor |
|
26
|
|
|
* |
|
27
|
|
|
* @param \phpbb\db\driver\driver_interface $db DB driver interface |
|
28
|
|
|
* @param string $ads_table Ads table |
|
29
|
|
|
* @param string $ad_locations_table Ad locations table |
|
30
|
|
|
*/ |
|
31
|
32 |
|
public function __construct(\phpbb\db\driver\driver_interface $db, $ads_table, $ad_locations_table) |
|
32
|
|
|
{ |
|
33
|
32 |
|
$this->db = $db; |
|
34
|
32 |
|
$this->ads_table = $ads_table; |
|
35
|
32 |
|
$this->ad_locations_table = $ad_locations_table; |
|
36
|
32 |
|
} |
|
37
|
|
|
|
|
38
|
|
|
/** |
|
39
|
|
|
* Get specific ad |
|
40
|
|
|
* |
|
41
|
|
|
* @param int $ad_id Advertisement ID |
|
42
|
|
|
* @return array Advertisement data |
|
43
|
|
|
*/ |
|
44
|
4 |
View Code Duplication |
public function get_ad($ad_id) |
|
|
|
|
|
|
45
|
|
|
{ |
|
46
|
|
|
$sql = 'SELECT * |
|
47
|
4 |
|
FROM ' . $this->ads_table . ' |
|
48
|
4 |
|
WHERE ad_id = ' . (int) $ad_id; |
|
49
|
4 |
|
$result = $this->db->sql_query($sql); |
|
50
|
4 |
|
$data = $this->db->sql_fetchrow($result); |
|
51
|
4 |
|
$this->db->sql_freeresult($result); |
|
52
|
|
|
|
|
53
|
4 |
|
return $data; |
|
54
|
|
|
} |
|
55
|
|
|
|
|
56
|
|
|
/** |
|
57
|
|
|
* Get one ad per every location |
|
58
|
|
|
* |
|
59
|
|
|
* @param array $ad_locations List of ad locations to fetch ads for |
|
60
|
|
|
* @return array List of ad codes for each location |
|
61
|
|
|
*/ |
|
62
|
1 |
|
public function get_ads($ad_locations) |
|
63
|
|
|
{ |
|
64
|
|
|
$sql = 'SELECT location_id, ad_code |
|
65
|
|
|
FROM ( |
|
66
|
|
|
SELECT al.location_id, a.ad_code |
|
67
|
1 |
|
FROM ' . $this->ad_locations_table . ' al |
|
68
|
1 |
|
LEFT JOIN ' . $this->ads_table . ' a |
|
69
|
|
|
ON (al.ad_id = a.ad_id) |
|
70
|
|
|
WHERE a.ad_enabled = 1 |
|
71
|
1 |
|
AND ' . $this->db->sql_in_set('al.location_id', $ad_locations) . ' |
|
72
|
1 |
|
ORDER BY ' . $this->sql_random() . ' |
|
73
|
|
|
) z |
|
74
|
1 |
|
ORDER BY z.location_id'; |
|
75
|
1 |
|
$result = $this->db->sql_query($sql); |
|
76
|
1 |
|
$data = $this->db->sql_fetchrowset($result); |
|
77
|
1 |
|
$this->db->sql_freeresult($result); |
|
78
|
|
|
|
|
79
|
1 |
|
return $data; |
|
80
|
|
|
} |
|
81
|
|
|
|
|
82
|
|
|
/** |
|
83
|
|
|
* Get all advertisements |
|
84
|
|
|
* |
|
85
|
|
|
* @return array List of all ads |
|
86
|
|
|
*/ |
|
87
|
1 |
View Code Duplication |
public function get_all_ads() |
|
|
|
|
|
|
88
|
|
|
{ |
|
89
|
|
|
$sql = 'SELECT ad_id, ad_name, ad_enabled |
|
90
|
1 |
|
FROM ' . $this->ads_table; |
|
91
|
1 |
|
$result = $this->db->sql_query($sql); |
|
92
|
1 |
|
$data = $this->db->sql_fetchrowset($result); |
|
93
|
1 |
|
$this->db->sql_freeresult($result); |
|
94
|
|
|
|
|
95
|
1 |
|
return $data; |
|
96
|
|
|
} |
|
97
|
|
|
|
|
98
|
|
|
/** |
|
99
|
|
|
* Insert new advertisement to the database |
|
100
|
|
|
* |
|
101
|
|
|
* @param array $data New ad data |
|
102
|
|
|
* @return int New advertisement ID |
|
103
|
|
|
*/ |
|
104
|
1 |
View Code Duplication |
public function insert_ad($data) |
|
|
|
|
|
|
105
|
|
|
{ |
|
106
|
1 |
|
$data = $this->intersect_ad_data($data); |
|
107
|
|
|
|
|
108
|
1 |
|
$sql = 'INSERT INTO ' . $this->ads_table . ' ' . $this->db->sql_build_array('INSERT', $data); |
|
109
|
1 |
|
$this->db->sql_query($sql); |
|
110
|
|
|
|
|
111
|
1 |
|
return $this->db->sql_nextid(); |
|
112
|
|
|
} |
|
113
|
|
|
|
|
114
|
|
|
/** |
|
115
|
|
|
* Update advertisement |
|
116
|
|
|
* |
|
117
|
|
|
* @param int $ad_id Advertisement ID |
|
118
|
|
|
* @param array $data List of data to update in the database |
|
119
|
|
|
* @return int Number of affected rows. Can be used to determine if any ad has been updated. |
|
120
|
|
|
*/ |
|
121
|
6 |
View Code Duplication |
public function update_ad($ad_id, $data) |
|
|
|
|
|
|
122
|
|
|
{ |
|
123
|
6 |
|
$data = $this->intersect_ad_data($data); |
|
124
|
|
|
|
|
125
|
6 |
|
$sql = 'UPDATE ' . $this->ads_table . ' |
|
126
|
6 |
|
SET ' . $this->db->sql_build_array('UPDATE', $data) . ' |
|
127
|
6 |
|
WHERE ad_id = ' . (int) $ad_id; |
|
128
|
6 |
|
$this->db->sql_query($sql); |
|
129
|
|
|
|
|
130
|
6 |
|
return $this->db->sql_affectedrows(); |
|
131
|
|
|
} |
|
132
|
|
|
|
|
133
|
|
|
/** |
|
134
|
|
|
* Delete advertisement |
|
135
|
|
|
* |
|
136
|
|
|
* @param int $ad_id Advertisement ID |
|
137
|
|
|
* @return int Number of affected rows. Can be used to determine if any ad has been deleted. |
|
138
|
|
|
*/ |
|
139
|
2 |
|
public function delete_ad($ad_id) |
|
140
|
|
|
{ |
|
141
|
2 |
|
$sql = 'DELETE FROM ' . $this->ads_table . ' |
|
142
|
2 |
|
WHERE ad_id = ' . (int) $ad_id; |
|
143
|
2 |
|
$this->db->sql_query($sql); |
|
144
|
|
|
|
|
145
|
2 |
|
return $this->db->sql_affectedrows(); |
|
146
|
|
|
} |
|
147
|
|
|
|
|
148
|
|
|
/** |
|
149
|
|
|
* Get all locations for specified advertisement |
|
150
|
|
|
* |
|
151
|
|
|
* @param int $ad_id Advertisement ID |
|
152
|
|
|
* @return array List of template locations for specified ad |
|
153
|
|
|
*/ |
|
154
|
1 |
|
public function get_ad_locations($ad_id) |
|
155
|
|
|
{ |
|
156
|
1 |
|
$ad_locations = array(); |
|
157
|
|
|
|
|
158
|
|
|
$sql = 'SELECT location_id |
|
159
|
1 |
|
FROM ' . $this->ad_locations_table . ' |
|
160
|
1 |
|
WHERE ad_id = ' . (int) $ad_id; |
|
161
|
1 |
|
$result = $this->db->sql_query($sql); |
|
162
|
1 |
|
while ($row = $this->db->sql_fetchrow($result)) |
|
163
|
|
|
{ |
|
164
|
1 |
|
$ad_locations[] = $row['location_id']; |
|
165
|
1 |
|
} |
|
166
|
1 |
|
$this->db->sql_freeresult($result); |
|
167
|
|
|
|
|
168
|
1 |
|
return $ad_locations; |
|
169
|
|
|
} |
|
170
|
|
|
|
|
171
|
|
|
/** |
|
172
|
|
|
* Insert advertisement locations |
|
173
|
|
|
* |
|
174
|
|
|
* @param int $ad_id Advertisement ID |
|
175
|
|
|
* @param array $ad_locations List of template locations for this ad |
|
176
|
|
|
* @return void |
|
177
|
|
|
*/ |
|
178
|
2 |
|
public function insert_ad_locations($ad_id, $ad_locations) |
|
179
|
|
|
{ |
|
180
|
2 |
|
$sql_ary = array(); |
|
181
|
2 |
|
foreach ($ad_locations as $ad_location) |
|
182
|
|
|
{ |
|
183
|
2 |
|
$sql_ary[] = array( |
|
184
|
2 |
|
'ad_id' => $ad_id, |
|
185
|
2 |
|
'location_id' => $ad_location, |
|
186
|
|
|
); |
|
187
|
2 |
|
} |
|
188
|
2 |
|
$this->db->sql_multi_insert($this->ad_locations_table, $sql_ary); |
|
189
|
2 |
|
} |
|
190
|
|
|
|
|
191
|
|
|
/** |
|
192
|
|
|
* Delete advertisement locations |
|
193
|
|
|
* |
|
194
|
|
|
* @param int $ad_id Advertisement ID |
|
195
|
|
|
* @return void |
|
196
|
|
|
*/ |
|
197
|
3 |
|
public function delete_ad_locations($ad_id) |
|
198
|
|
|
{ |
|
199
|
3 |
|
$sql = 'DELETE FROM ' . $this->ad_locations_table . ' |
|
200
|
3 |
|
WHERE ad_id = ' . (int) $ad_id; |
|
201
|
3 |
|
$this->db->sql_query($sql); |
|
202
|
3 |
|
} |
|
203
|
|
|
|
|
204
|
|
|
/** |
|
205
|
|
|
* Make sure only necessary data make their way to SQL query |
|
206
|
|
|
* |
|
207
|
|
|
* @param array $data List of data to query the database |
|
208
|
|
|
* @return array Cleaned data that contain only valid keys |
|
209
|
|
|
*/ |
|
210
|
7 |
|
protected function intersect_ad_data($data) |
|
211
|
|
|
{ |
|
212
|
7 |
|
return array_intersect_key($data, array( |
|
213
|
7 |
|
'ad_name' => '', |
|
214
|
7 |
|
'ad_note' => '', |
|
215
|
7 |
|
'ad_code' => '', |
|
216
|
7 |
|
'ad_enabled' => '', |
|
217
|
7 |
|
)); |
|
218
|
|
|
} |
|
219
|
|
|
|
|
220
|
|
|
/** |
|
221
|
|
|
* Get the random statement for this database layer |
|
222
|
|
|
* |
|
223
|
|
|
* @return string Random statement for current database layer |
|
224
|
|
|
*/ |
|
225
|
1 |
|
protected function sql_random() |
|
226
|
|
|
{ |
|
227
|
1 |
|
switch ($this->db->get_sql_layer()) |
|
228
|
|
|
{ |
|
229
|
1 |
|
case 'oracle': |
|
230
|
1 |
|
case 'postgres': |
|
231
|
1 |
|
case 'sqlite': |
|
232
|
1 |
|
case 'sqlite3': |
|
233
|
|
|
return 'RANDOM()'; |
|
234
|
|
|
|
|
235
|
|
|
/* All other cases should use the default |
|
|
|
|
|
|
236
|
|
|
case 'mssql': |
|
237
|
|
|
case 'mssql_odbc': |
|
238
|
|
|
case 'mssqlnative': |
|
239
|
|
|
case 'mysql': |
|
240
|
|
|
case 'mysqli':*/ |
|
241
|
1 |
|
default: |
|
242
|
1 |
|
return 'RAND()'; |
|
243
|
1 |
|
} |
|
244
|
|
|
} |
|
245
|
|
|
} |
|
246
|
|
|
|
phpbb-extensions /
ad-management
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.