manager::sql_random() - Code Metrics - Inspection of "Improve Scrutinizer score" - phpbb-extensions/ad-management - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#75)

by Jakub

created 2017-08-20 19:17 UTC

manager::sql_random() B

↳ Parent: manager

Complexity

Conditions	5
Paths	5

Size

Total Lines	19
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	9
CRAP Score	5.3906

Importance

Changes

Metric	Value
dl	0
loc	19
ccs	9
cts	12
cp	0.75
rs	8.8571
c	0
b	0
f	0
cc	5
eloc	11
nc	5
nop	0
crap	5.3906

<?php
/**
 *
 * Advertisement management. An extension for the phpBB Forum Software package.
 *
 * @copyright (c) 2017 phpBB Limited <https://www.phpbb.com>
 * @license GNU General Public License, version 2 (GPL-2.0)
 *
 */

namespace phpbb\ads\ad;

class manager
{
	/** @var \phpbb\db\driver\driver_interface */
	protected $db;

	/** @var \phpbb\config\config */
	protected $config;

	/** @var string */
	protected $ads_table;

	/** @var string */
	protected $ad_locations_table;

	/**
	 * Constructor
	 *
	 * @param    \phpbb\db\driver\driver_interface $db                 DB driver interface
	 * @param    \phpbb\config\config              $config             Config object
	 * @param    string                            $ads_table          Ads table
	 * @param    string                            $ad_locations_table Ad locations table
	 */
	public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config, $ads_table, $ad_locations_table)
	{
		$this->db = $db;
		$this->config = $config;
		$this->ads_table = $ads_table;
		$this->ad_locations_table = $ad_locations_table;
	}

	/**
	 * Get specific ad
	 *
	 * @param	int	$ad_id	Advertisement ID
	 * @return	array	Array with advertisement data
	 */
	public function get_ad($ad_id)
	{
		$sql = 'SELECT *
			FROM ' . $this->ads_table . '
			WHERE ad_id = ' . (int) $ad_id;
		$result = $this->db->sql_query($sql);
		$data = $this->db->sql_fetchrow($result);
		$this->db->sql_freeresult($result);

		return $data !== false ? $data : array();
	}

	/**
	 * Get one ad per every location
	 *
	 * @param    array $ad_locations List of ad locations to fetch ads for
	 * @return    array    List of ad codes for each location
	 */
	public function get_ads($ad_locations)
	{
		$sql_where_views = $this->config['phpbb_ads_enable_views'] ? 'AND (a.ad_views_limit = 0 OR a.ad_views_limit > a.ad_views)' : '';
		$sql_where_clicks = $this->config['phpbb_ads_enable_clicks'] ? 'AND (a.ad_clicks_limit = 0 OR a.ad_clicks_limit > a.ad_clicks)' : '';

		$sql = 'SELECT location_id, ad_id, ad_code
			FROM (
				SELECT al.location_id, a.ad_id, a.ad_code
				FROM ' . $this->ad_locations_table . ' al
				LEFT JOIN ' . $this->ads_table . ' a
					ON (al.ad_id = a.ad_id)
				WHERE a.ad_enabled = 1
					AND (a.ad_end_date = 0
						OR a.ad_end_date > ' . time() . ")
					$sql_where_views
					$sql_where_clicks
					AND " . $this->db->sql_in_set('al.location_id', $ad_locations) . '
				ORDER BY (' . $this->sql_random() . ' * a.ad_priority) DESC
			) z
			ORDER BY z.location_id';
		$result = $this->db->sql_query($sql);
		$data = $this->db->sql_fetchrowset($result);
		$this->db->sql_freeresult($result);

		$current_location_id = '';
		$data = array_filter($data, function ($row) use (&$current_location_id) {
			$return = $current_location_id != $row['location_id'];
			$current_location_id = $row['location_id'];
			return $return;
		});

		return $data;
	}

	/**
	 * Get all advertisements.
	 *
	 * @return    array    List of all ads
	 */
	public function get_all_ads()
	{
		$sql = 'SELECT ad_id, ad_priority, ad_name, ad_enabled, ad_end_date, ad_views, ad_clicks, ad_views_limit, ad_clicks_limit
			FROM ' . $this->ads_table;
		$result = $this->db->sql_query($sql);
		$data = $this->db->sql_fetchrowset($result);
		$this->db->sql_freeresult($result);

		return $data;
	}

	/**
	 * Get all owner's ads
	 *
	 * @param    int $user_id Ad owner
	 * @return    array    List of owner's ads
	 */
	public function get_ads_by_owner($user_id)
	{
		$sql = 'SELECT ad_name, ad_views, ad_clicks
			FROM ' . $this->ads_table . '
			WHERE ad_owner = ' . (int) $user_id;
		$result = $this->db->sql_query($sql);
		$data = $this->db->sql_fetchrowset($result);
		$this->db->sql_freeresult($result);

		return $data;
	}

	/**
	 * Increment views for specified ads
	 *
	 * Note, that views are incremented only by one even when
	 * an ad is displayed multiple times on the same page.
	 *
	 * @param    array $ad_ids IDs of ads to increment views
	 * @return    void
	 */
	public function increment_ads_views($ad_ids)
	{
		if (!empty($ad_ids))
		{
			$sql = 'UPDATE ' . $this->ads_table . '
				SET ad_views = ad_views + 1
				WHERE ' . $this->db->sql_in_set('ad_id', $ad_ids);
			$this->db->sql_query($sql);
		}
	}

	/**
	 * Increment clicks for specified ad
	 *
	 * @param    int $ad_id ID of an ad to increment clicks
	 * @return    void
	 */
	public function increment_ad_clicks($ad_id)
	{
		$sql = 'UPDATE ' . $this->ads_table . '
			SET ad_clicks = ad_clicks + 1
			WHERE ad_id = ' . (int) $ad_id;
		$this->db->sql_query($sql);
	}

	/**
	 * Insert new advertisement to the database
	 *
	 * @param    array $data New ad data
	 * @return    int        New advertisement ID
	 */
	public function insert_ad($data)
	{
		$data = $this->intersect_ad_data($data);

		$sql = 'INSERT INTO ' . $this->ads_table . ' ' . $this->db->sql_build_array('INSERT', $data);
		$this->db->sql_query($sql);

		return $this->db->sql_nextid();
	}

	/**
	 * Update advertisement
	 *
	 * @param    int   $ad_id Advertisement ID
	 * @param    array $data  List of data to update in the database
	 * @return    int        Number of affected rows. Can be used to determine if any ad has been updated.
	 */
	public function update_ad($ad_id, $data)
	{
		$data = $this->intersect_ad_data($data);

		$sql = 'UPDATE ' . $this->ads_table . '
			SET ' . $this->db->sql_build_array('UPDATE', $data) . '
			WHERE ad_id = ' . (int) $ad_id;
		$this->db->sql_query($sql);

		return $this->db->sql_affectedrows();
	}

	/**
	 * Delete advertisement
	 *
	 * @param    int $ad_id Advertisement ID
	 * @return    int        Number of affected rows. Can be used to determine if any ad has been deleted.
	 */
	public function delete_ad($ad_id)
	{
		$sql = 'DELETE FROM ' . $this->ads_table . '
			WHERE ad_id = ' . (int) $ad_id;
		$this->db->sql_query($sql);

		return $this->db->sql_affectedrows();
	}

	/**
	 * Remove ad owner
	 *
	 * @param    array $user_ids User IDs
	 * @return    void
	 */
	public function remove_ad_owner(array $user_ids)
	{
		if (empty($user_ids))
		{
			return;
		}

		$sql = 'UPDATE ' . $this->ads_table . '
			SET ad_owner = 0
			WHERE ' . $this->db->sql_in_set('ad_owner', $user_ids);
		$this->db->sql_query($sql);
	}

	/**
	 * Get all locations for specified advertisement
	 *
	 * @param	int		$ad_id	Advertisement ID
	 * @return	array	List of template locations for specified ad
	 */
	public function get_ad_locations($ad_id)
	{
		$ad_locations = array();

		$sql = 'SELECT location_id
			FROM ' . $this->ad_locations_table . '
			WHERE ad_id = ' . (int) $ad_id;
		$result = $this->db->sql_query($sql);
		while ($row = $this->db->sql_fetchrow($result))
		{
			$ad_locations[] = $row['location_id'];
		}
		$this->db->sql_freeresult($result);

		return $ad_locations;
	}

	/**
	 * Insert advertisement locations
	 *
	 * @param	int		$ad_id			Advertisement ID
	 * @param	array	$ad_locations	List of template locations for this ad
	 * @return	void
	 */
	public function insert_ad_locations($ad_id, $ad_locations)
	{
		$sql_ary = array();
		foreach ($ad_locations as $ad_location)
		{
			$sql_ary[] = array(
				'ad_id'			=> $ad_id,
				'location_id'	=> $ad_location,
			);
		}
		$this->db->sql_multi_insert($this->ad_locations_table, $sql_ary);
	}

	/**
	 * Delete advertisement locations
	 *
	 * @param	int		$ad_id	Advertisement ID
	 * @return	void
	 */
	public function delete_ad_locations($ad_id)
	{
		$sql = 'DELETE FROM ' . $this->ad_locations_table . '
			WHERE ad_id = ' . (int) $ad_id;
		$this->db->sql_query($sql);
	}

	/**
	 * Load memberships of the user
	 *
	 * @param	int		$user_id	User ID to load memberships
	 * @return	array	List of group IDs user is member of
	 */
	public function load_memberships($user_id)
	{
		$memberships = array();
		$sql = 'SELECT group_id
			FROM ' . USER_GROUP_TABLE . '
			WHERE user_id = ' . (int) $user_id . '
			AND user_pending = 0';
		$result = $this->db->sql_query($sql, 3600);
		while ($row = $this->db->sql_fetchrow($result))
		{
			$memberships[] = $row['group_id'];
		}
		$this->db->sql_freeresult($result);
		return $memberships;
	}

	/**
	 * Load all board groups
	 *
	 * @return	array	List of groups
	 */
	public function load_groups()
	{
		$sql = 'SELECT group_id, group_name, group_type
			FROM ' . GROUPS_TABLE . "
			WHERE group_name <> 'BOTS'
			ORDER BY group_name ASC";
		$result = $this->db->sql_query($sql);
		$groups = $this->db->sql_fetchrowset($result);
		$this->db->sql_freeresult($result);

		return $groups;
	}

	/**
	 * Make sure only necessary data make their way to SQL query
	 *
	 * @param	array	$data	List of data to query the database
	 * @return	array	Cleaned data that contain only valid keys
	 */
	protected function intersect_ad_data($data)
	{
		return array_intersect_key($data, array(
			'ad_name'			=> '',
			'ad_note'			=> '',
			'ad_code'			=> '',
			'ad_enabled'		=> '',
			'ad_end_date'		=> '',
			'ad_priority'		=> '',
			'ad_views_limit'	=> '',
			'ad_clicks_limit'	=> '',
			'ad_owner'			=> '',
		));
	}

	/**
	 * Get the random statement for this database layer
	 * Random function should generate a float value between 0 and 1
	 *
	 * @return	string	Random statement for current database layer
	 */
	protected function sql_random()
	{
		switch ($this->db->get_sql_layer())
		{
			case 'oracle':
				return 'VALUE()';

			case 'postgres':
				return 'RANDOM()';

			// https://stackoverflow.com/a/35369410/2908600
			case 'sqlite':
			case 'sqlite3':
				return '(0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2)';

			default:
				return 'RAND()';
		}
	}
}


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	53		public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config, $ads_table, $ad_locations_table)
36			{
37	53		$this->db = $db;
38	53		$this->config = $config;
39	53		$this->ads_table = $ads_table;
40	53		$this->ad_locations_table = $ad_locations_table;
41	53		}
42
43			/**
44			* Get specific ad
45			*
46			* @param int $ad_id Advertisement ID
47			* @return array Array with advertisement data
48			*/
49	13	View Code Duplication	public function get_ad($ad_id)
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 !== false ? $data : array();
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()
107			{
108			$sql = 'SELECT ad_id, ad_priority, 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	5	View Code Duplication	public function get_ads_by_owner($user_id)
124			{
125			$sql = 'SELECT ad_name, ad_views, ad_clicks
126	5		FROM ' . $this->ads_table . '
127	5		WHERE ad_owner = ' . (int) $user_id;
128	5		$result = $this->db->sql_query($sql);
129	5		$data = $this->db->sql_fetchrowset($result);
130	5		$this->db->sql_freeresult($result);
131
132	5		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	View Code Duplication	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			* Remove ad owner
221			*
222			* @param array $user_ids User IDs
223			* @return void
224			*/
225	3	View Code Duplication	public function remove_ad_owner(array $user_ids)
226			{
227	3		if (empty($user_ids))
228	3		{
229			return;
230			}
231
232	3		$sql = 'UPDATE ' . $this->ads_table . '
233			SET ad_owner = 0
234	3		WHERE ' . $this->db->sql_in_set('ad_owner', $user_ids);
235	3		$this->db->sql_query($sql);
236	3		}
237
238			/**
239			* Get all locations for specified advertisement
240			*
241			* @param int $ad_id Advertisement ID
242			* @return array List of template locations for specified ad
243			*/
244	7	View Code Duplication	public function get_ad_locations($ad_id)
245			{
246	7		$ad_locations = array();
247
248			$sql = 'SELECT location_id
249	7		FROM ' . $this->ad_locations_table . '
250	7		WHERE ad_id = ' . (int) $ad_id;
251	7		$result = $this->db->sql_query($sql);
252	7		while ($row = $this->db->sql_fetchrow($result))
253			{
254	5		$ad_locations[] = $row['location_id'];
255	5		}
256	7		$this->db->sql_freeresult($result);
257
258	7		return $ad_locations;
259			}
260
261			/**
262			* Insert advertisement locations
263			*
264			* @param int $ad_id Advertisement ID
265			* @param array $ad_locations List of template locations for this ad
266			* @return void
267			*/
268	2		public function insert_ad_locations($ad_id, $ad_locations)
269			{
270	2		$sql_ary = array();
271	2		foreach ($ad_locations as $ad_location)
272			{
273	2		$sql_ary[] = array(
274	2		'ad_id' => $ad_id,
275	2		'location_id' => $ad_location,
276			);
277	2		}
278	2		$this->db->sql_multi_insert($this->ad_locations_table, $sql_ary);
279	2		}
280
281			/**
282			* Delete advertisement locations
283			*
284			* @param int $ad_id Advertisement ID
285			* @return void
286			*/
287	3		public function delete_ad_locations($ad_id)
288			{
289	3		$sql = 'DELETE FROM ' . $this->ad_locations_table . '
290	3		WHERE ad_id = ' . (int) $ad_id;
291	3		$this->db->sql_query($sql);
292	3		}
293
294			/**
295			* Load memberships of the user
296			*
297			* @param int $user_id User ID to load memberships
298			* @return array List of group IDs user is member of
299			*/
300	5	View Code Duplication	public function load_memberships($user_id)
301			{
302	5		$memberships = array();
303			$sql = 'SELECT group_id
304	5		FROM ' . USER_GROUP_TABLE . '
305	5		WHERE user_id = ' . (int) $user_id . '
306	5		AND user_pending = 0';
307	5		$result = $this->db->sql_query($sql, 3600);
308	5		while ($row = $this->db->sql_fetchrow($result))
309			{
310	5		$memberships[] = $row['group_id'];
311	5		}
312	5		$this->db->sql_freeresult($result);
313	5		return $memberships;
314			}
315
316			/**
317			* Load all board groups
318			*
319			* @return array List of groups
320			*/
321	1	View Code Duplication	public function load_groups()
322			{
323			$sql = 'SELECT group_id, group_name, group_type
324	1		FROM ' . GROUPS_TABLE . "
325			WHERE group_name <> 'BOTS'
326	1		ORDER BY group_name ASC";
327	1		$result = $this->db->sql_query($sql);
328	1		$groups = $this->db->sql_fetchrowset($result);
329	1		$this->db->sql_freeresult($result);
330
331	1		return $groups;
332			}
333
334			/**
335			* Make sure only necessary data make their way to SQL query
336			*
337			* @param array $data List of data to query the database
338			* @return array Cleaned data that contain only valid keys
339			*/
340	6		protected function intersect_ad_data($data)
341			{
342	6		return array_intersect_key($data, array(
343	6		'ad_name' => '',
344	6		'ad_note' => '',
345	6		'ad_code' => '',
346	6		'ad_enabled' => '',
347	6		'ad_end_date' => '',
348	6		'ad_priority' => '',
349	6		'ad_views_limit' => '',
350	6		'ad_clicks_limit' => '',
351	6		'ad_owner' => '',
352	6		));
353			}
354
355			/**
356			* Get the random statement for this database layer
357			* Random function should generate a float value between 0 and 1
358			*
359			* @return string Random statement for current database layer
360			*/
361	6		protected function sql_random()
362			{
363	6		switch ($this->db->get_sql_layer())
364			{
365	6		case 'oracle':
366			return 'VALUE()';
367
368	6		case 'postgres':
369			return 'RANDOM()';
370
371			// https://stackoverflow.com/a/35369410/2908600
372	6		case 'sqlite':
373	6		case 'sqlite3':
374			return '(0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2)';
375
376	6		default:
377	6		return 'RAND()';
378	6		}
379			}
380			}
381

phpbb-extensions / ad-management

Pull Request — master (#75)

manager::sql_random() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like