Completed
Pull Request — master (#48)
by Jakub
11:14 queued 02:07
created

manager::get_ads_by_owner()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 11
Code Lines 8

Duplication

Lines 11
Ratio 100 %

Code Coverage

Tests 7
CRAP Score 1

Importance

Changes 0
Metric Value
c 0
b 0
f 0
dl 11
loc 11
ccs 7
cts 7
cp 1
rs 9.4285
cc 1
eloc 8
nc 1
nop 1
crap 1
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\ads\ad;
12
13
class manager
14
{
15
	/** @var \phpbb\db\driver\driver_interface */
16
	protected $db;
17
18
	/** @var \phpbb\config\config */
19
	protected $config;
20
21
	/** @var string */
22
	protected $ads_table;
23
24
	/** @var string */
25
	protected $ad_locations_table;
26
27
	/**
28
	 * Constructor
29
	 *
30
	 * @param    \phpbb\db\driver\driver_interface $db                 DB driver interface
31
	 * @param    \phpbb\config\config              $config             Config object
32
	 * @param    string                            $ads_table          Ads table
33
	 * @param    string                            $ad_locations_table Ad locations table
34
	 */
35 41
	public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config, $ads_table, $ad_locations_table)
36
	{
37 41
		$this->db = $db;
38 41
		$this->config = $config;
39 41
		$this->ads_table = $ads_table;
40 41
		$this->ad_locations_table = $ad_locations_table;
41 41
	}
42
43
	/**
44
	* Get specific ad
45
	*
46
	* @param	int		$ad_id	Advertisement ID
47
	* @return	mixed	Array with advertisement data, false if ad doesn't exist
48
	*/
49 13 View Code Duplication
	public function get_ad($ad_id)
1 ignored issue
show
Duplication introduced by
This method seems to be duplicated in your project.

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.

Loading history...
50
	{
51
		$sql = 'SELECT *
52 13
			FROM ' . $this->ads_table . '
53 13
			WHERE ad_id = ' . (int) $ad_id;
54 13
		$result = $this->db->sql_query($sql);
55 13
		$data = $this->db->sql_fetchrow($result);
56 13
		$this->db->sql_freeresult($result);
57
58 13
		return $data;
59
	}
60
61
	/**
62
	* Get one ad per every location
63
	*
64
	* @param	array	$ad_locations	List of ad locations to fetch ads for
65
	* @return	array	List of ad codes for each location
66
	*/
67 6
	public function get_ads($ad_locations)
68
	{
69 6
		$sql_where_views = $this->config['phpbb_ads_enable_views'] ? 'AND (a.ad_views_limit = 0 OR a.ad_views_limit > a.ad_views)' : '';
70 6
		$sql_where_clicks = $this->config['phpbb_ads_enable_clicks'] ? 'AND (a.ad_clicks_limit = 0 OR a.ad_clicks_limit > a.ad_clicks)' : '';
71
72
		$sql = 'SELECT location_id, ad_id, ad_code
73
			FROM (
74
				SELECT al.location_id, a.ad_id, a.ad_code
75 6
				FROM ' . $this->ad_locations_table . ' al
76 6
				LEFT JOIN ' . $this->ads_table . " a
77
					ON (al.ad_id = a.ad_id)
78
				WHERE a.ad_enabled = 1
79
					AND (a.ad_end_date = 0
80 6
						OR a.ad_end_date > " . time() . ")
81
					$sql_where_views
82 6
					$sql_where_clicks
83 6
					AND " . $this->db->sql_in_set('al.location_id', $ad_locations) . '
84 6
				ORDER BY (' . $this->sql_random() . ' * a.ad_priority) DESC
85
			) z
86 6
			ORDER BY z.location_id';
87 6
		$result = $this->db->sql_query($sql);
88 6
		$data = $this->db->sql_fetchrowset($result);
89 6
		$this->db->sql_freeresult($result);
90
91 6
		$current_location_id = '';
92 6
		$data = array_filter($data, function($row) use (&$current_location_id) {
93 4
			$return = $current_location_id != $row['location_id'];
94 4
			$current_location_id = $row['location_id'];
95 4
			return $return;
96 6
		});
97
98 6
		return $data;
99
	}
100
101
	/**
102
	 * Get all advertisements.
103
	 *
104
	 * @return	array	List of all ads
105
	 */
106 2 View Code Duplication
	public function get_all_ads()
1 ignored issue
show
Duplication introduced by
This method seems to be duplicated in your project.

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.

Loading history...
107
	{
108
		$sql = 'SELECT ad_id, ad_name, ad_enabled, ad_end_date, ad_views, ad_clicks, ad_views_limit, ad_clicks_limit
109 2
			FROM ' . $this->ads_table;
110 2
		$result = $this->db->sql_query($sql);
111 2
		$data = $this->db->sql_fetchrowset($result);
112 2
		$this->db->sql_freeresult($result);
113
114 2
		return $data;
115
	}
116
117
	/**
118
	 * Get all owner's ads
119
	 *
120
	 * @param	int	$user_id	Ad owner
121
	 * @return	array	List of owner's ads
122
	 */
123 2 View Code Duplication
	public function get_ads_by_owner($user_id)
1 ignored issue
show
Duplication introduced by
This method seems to be duplicated in your project.

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.

Loading history...
124
	{
125
		$sql = 'SELECT ad_name, ad_views, ad_clicks
126 2
			FROM ' . $this->ads_table . '
127 2
			WHERE ad_owner = ' . (int) $user_id;
128 2
		$result = $this->db->sql_query($sql);
129 2
		$data = $this->db->sql_fetchrowset($result);
130 2
		$this->db->sql_freeresult($result);
131
132 2
		return $data;
133
	}
134
135
	/**
136
	* Increment views for specified ads
137
	*
138
	* Note, that views are incremented only by one even when
139
	* an ad is displayed multiple times on the same page.
140
	*
141
	* @param	array	$ad_ids	IDs of ads to increment views
142
	* @return	void
143
	*/
144 2
	public function increment_ads_views($ad_ids)
145
	{
146 2
		if (!empty($ad_ids))
147 2
		{
148 2
			$sql = 'UPDATE ' . $this->ads_table . '
149
				SET ad_views = ad_views + 1
150 2
				WHERE ' . $this->db->sql_in_set('ad_id', $ad_ids);
151 2
			$this->db->sql_query($sql);
152 2
		}
153 2
	}
154
155
	/**
156
	* Increment clicks for specified ad
157
	*
158
	* @param	int	$ad_id	ID of an ad to increment clicks
159
	* @return	void
160
	*/
161 2
	public function increment_ad_clicks($ad_id)
162
	{
163 2
		$sql = 'UPDATE ' . $this->ads_table . '
164
			SET ad_clicks = ad_clicks + 1
165 2
			WHERE ad_id = ' . (int) $ad_id;
166 2
		$this->db->sql_query($sql);
167 2
	}
168
169
	/**
170
	* Insert new advertisement to the database
171
	*
172
	* @param	array	$data	New ad data
173
	* @return	int		New advertisement ID
174
	*/
175 2 View Code Duplication
	public function insert_ad($data)
176
	{
177 2
		$data = $this->intersect_ad_data($data);
178
179 2
		$sql = 'INSERT INTO ' . $this->ads_table . ' ' . $this->db->sql_build_array('INSERT', $data);
180 2
		$this->db->sql_query($sql);
181
182 2
		return $this->db->sql_nextid();
183
	}
184
185
	/**
186
	* Update advertisement
187
	*
188
	* @param	int		$ad_id	Advertisement ID
189
	* @param	array	$data	List of data to update in the database
190
	* @return	int		Number of affected rows. Can be used to determine if any ad has been updated.
191
	*/
192 4 View Code Duplication
	public function update_ad($ad_id, $data)
193
	{
194 4
		$data = $this->intersect_ad_data($data);
195
196 4
		$sql = 'UPDATE ' . $this->ads_table . '
197 4
			SET ' . $this->db->sql_build_array('UPDATE', $data) . '
198 4
			WHERE ad_id = ' . (int) $ad_id;
199 4
		$this->db->sql_query($sql);
200
201 4
		return $this->db->sql_affectedrows();
202
	}
203
204
	/**
205
	* Delete advertisement
206
	*
207
	* @param	int		$ad_id	Advertisement ID
208
	* @return	int		Number of affected rows. Can be used to determine if any ad has been deleted.
209
	*/
210 1
	public function delete_ad($ad_id)
211
	{
212 1
		$sql = 'DELETE FROM ' . $this->ads_table . '
213 1
			WHERE ad_id = ' . (int) $ad_id;
214 1
		$this->db->sql_query($sql);
215
216 1
		return $this->db->sql_affectedrows();
217
	}
218
219
	/**
220
	* Get all locations for specified advertisement
221
	*
222
	* @param	int		$ad_id	Advertisement ID
223
	* @return	array	List of template locations for specified ad
224
	*/
225 7 View Code Duplication
	public function get_ad_locations($ad_id)
226
	{
227 7
		$ad_locations = array();
228
229
		$sql = 'SELECT location_id
230 7
			FROM ' . $this->ad_locations_table . '
231 7
			WHERE ad_id = ' . (int) $ad_id;
232 7
		$result = $this->db->sql_query($sql);
233 7
		while ($row = $this->db->sql_fetchrow($result))
234
		{
235 5
			$ad_locations[] = $row['location_id'];
236 5
		}
237 7
		$this->db->sql_freeresult($result);
238
239 7
		return $ad_locations;
240
	}
241
242
	/**
243
	* Insert advertisement locations
244
	*
245
	* @param	int		$ad_id			Advertisement ID
246
	* @param	array	$ad_locations	List of template locations for this ad
247
	* @return	void
248
	*/
249 2
	public function insert_ad_locations($ad_id, $ad_locations)
250
	{
251 2
		$sql_ary = array();
252 2
		foreach ($ad_locations as $ad_location)
253
		{
254 2
			$sql_ary[] = array(
255 2
				'ad_id'			=> $ad_id,
256 2
				'location_id'	=> $ad_location,
257
			);
258 2
		}
259 2
		$this->db->sql_multi_insert($this->ad_locations_table, $sql_ary);
260 2
	}
261
262
	/**
263
	* Delete advertisement locations
264
	*
265
	* @param	int		$ad_id	Advertisement ID
266
	* @return	void
267
	*/
268 3
	public function delete_ad_locations($ad_id)
269
	{
270 3
		$sql = 'DELETE FROM ' . $this->ad_locations_table . '
271 3
			WHERE ad_id = ' . (int) $ad_id;
272 3
		$this->db->sql_query($sql);
273 3
	}
274
275
	/**
276
	* Load memberships of the user
277
	*
278
	* @param	int		$user_id	User ID to load memberships
279
	* @return	array	List of group IDs user is member of
280
	*/
281 7 View Code Duplication
	public function load_memberships($user_id)
282
	{
283 7
		$memberships = array();
284
		$sql = 'SELECT group_id
285 7
			FROM ' . USER_GROUP_TABLE . '
286 7
			WHERE user_id = ' . (int) $user_id . '
287 7
			AND user_pending = 0';
288 7
		$result = $this->db->sql_query($sql, 3600);
289 7
		while ($row = $this->db->sql_fetchrow($result))
290
		{
291 7
			$memberships[] = $row['group_id'];
292 7
		}
293 7
		$this->db->sql_freeresult($result);
294 7
		return $memberships;
295
	}
296
297
	/**
298
	* Load all board groups
299
	*
300
	* @return	array	List of groups
301
	*/
302 1 View Code Duplication
	public function load_groups()
303
	{
304
		$sql = 'SELECT group_id, group_name, group_type
305 1
			FROM ' . GROUPS_TABLE . '
306 1
			ORDER BY group_name ASC';
307 1
		$result = $this->db->sql_query($sql);
308 1
		$groups = $this->db->sql_fetchrowset($result);
309 1
		$this->db->sql_freeresult($result);
310
311 1
		return $groups;
312
	}
313
314
	/**
315
	* Make sure only necessary data make their way to SQL query
316
	*
317
	* @param	array	$data	List of data to query the database
318
	* @return	array	Cleaned data that contain only valid keys
319
	*/
320 6
	protected function intersect_ad_data($data)
321
	{
322 6
		return array_intersect_key($data, array(
323 6
			'ad_name'			=> '',
324 6
			'ad_note'			=> '',
325 6
			'ad_code'			=> '',
326 6
			'ad_enabled'		=> '',
327 6
			'ad_end_date'		=> '',
328 6
			'ad_priority'		=> '',
329 6
			'ad_views_limit'	=> '',
330 6
			'ad_clicks_limit'	=> '',
331 6
			'ad_owner'			=> '',
332 6
		));
333
	}
334
335
	/**
336
	* Get the random statement for this database layer
337
	* Random function should generate a float value between 0 and 1
338
	*
339
	* @return	string	Random statement for current database layer
340
	*/
341 6
	protected function sql_random()
342
	{
343 6
		switch ($this->db->get_sql_layer())
344
		{
345 6
			case 'oracle':
346
				return 'VALUE()';
347
348 6
			case 'postgres':
349
				return 'RANDOM()';
350
351
			// https://stackoverflow.com/a/35369410/2908600
352 6
			case 'sqlite':
353 6
			case 'sqlite3':
354
				return '(0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2)';
355
356
			/* All other cases should use the default
1 ignored issue
show
Unused Code Comprehensibility introduced by
44% of this comment could be valid code. Did you maybe forget this after debugging?

Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it.

The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production.

This check looks for comments that seem to be mostly valid code and reports them.

Loading history...
357
			case 'mssql':
358
			case 'mssql_odbc':
359
			case 'mssqlnative':
360
			case 'mysql':
361
			case 'mysqli':*/
362 6
			default:
363 6
				return 'RAND()';
364 6
		}
365
	}
366
}
367