manager::get_ad() - Code Metrics - Inspection of "Simplify SQL query in get_ads" - phpbb-extensions/ad-management - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#110)

by Jakub

created 2018-01-14 15:25 UTC

manager::get_ad() A

↳ Parent: manager

Complexity

Conditions	2
Paths	2

Size

Total Lines	11
Code Lines	8

Duplication

Lines	11
Ratio	100 %

Code Coverage

Tests	7
CRAP Score	2

Importance

Changes

Metric	Value
dl	11
loc	11
ccs	7
cts	7
cp	1
rs	9.4285
c	0
b	0
f	0
cc	2
eloc	8
nc	2
nop	1
crap	2

<?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 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 al.location_id, (' . $this->sql_random() . ' * a.ad_priority) DESC';
		$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_id, ad_name, ad_enabled, ad_end_date, ad_views, ad_views_limit, ad_clicks, ad_clicks_limit
			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	58		public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config, $ads_table, $ad_locations_table)
36			{
37	58		$this->db = $db;
38	58		$this->config = $config;
39	58		$this->ads_table = $ads_table;
40	58		$this->ad_locations_table = $ad_locations_table;
41	58		}
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 al.location_id, a.ad_id, a.ad_code
73	6		FROM ' . $this->ad_locations_table . ' al
74	6		LEFT JOIN ' . $this->ads_table . ' a
75			ON (al.ad_id = a.ad_id)
76			WHERE a.ad_enabled = 1
77			AND (a.ad_end_date = 0
78	6		OR a.ad_end_date > ' . time() . ")
79			$sql_where_views
80	6		$sql_where_clicks
81	6		AND " . $this->db->sql_in_set('al.location_id', $ad_locations) . '
82	6		ORDER BY al.location_id, (' . $this->sql_random() . ' * a.ad_priority) DESC';
83	6		$result = $this->db->sql_query($sql);
84	6		$data = $this->db->sql_fetchrowset($result);
85	6		$this->db->sql_freeresult($result);
86
87	6		$current_location_id = '';
88	6		$data = array_filter($data, function ($row) use (&$current_location_id) {
89	4		$return = $current_location_id !== $row['location_id'];
90	4		$current_location_id = $row['location_id'];
91	4		return $return;
92	6		});
93
94	6		return $data;
95			}
96
97			/**
98			* Get all advertisements.
99			*
100			* @return array List of all ads
101			*/
102	2	View Code Duplication	public function get_all_ads()
103			{
104			$sql = 'SELECT ad_id, ad_priority, ad_name, ad_enabled, ad_end_date, ad_views, ad_clicks, ad_views_limit, ad_clicks_limit
105	2		FROM ' . $this->ads_table;
106	2		$result = $this->db->sql_query($sql);
107	2		$data = $this->db->sql_fetchrowset($result);
108	2		$this->db->sql_freeresult($result);
109
110	2		return $data;
111			}
112
113			/**
114			* Get all owner's ads
115			*
116			* @param int $user_id Ad owner
117			* @return array List of owner's ads
118			*/
119	5	View Code Duplication	public function get_ads_by_owner($user_id)
			0 ignored issues – show Duplication introduced 2017-10-03 15:20 UTC by Report Bug Copy Issue Report 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...
120			{
121			$sql = 'SELECT ad_id, ad_name, ad_enabled, ad_end_date, ad_views, ad_views_limit, ad_clicks, ad_clicks_limit
122	5		FROM ' . $this->ads_table . '
123	5		WHERE ad_owner = ' . (int) $user_id;
124	5		$result = $this->db->sql_query($sql);
125	5		$data = $this->db->sql_fetchrowset($result);
126	5		$this->db->sql_freeresult($result);
127
128	5		return $data;
129			}
130
131			/**
132			* Increment views for specified ads
133			*
134			* Note, that views are incremented only by one even when
135			* an ad is displayed multiple times on the same page.
136			*
137			* @param array $ad_ids IDs of ads to increment views
138			* @return void
139			*/
140	2	View Code Duplication	public function increment_ads_views($ad_ids)
141			{
142	2		if (!empty($ad_ids))
143	2		{
144	2		$sql = 'UPDATE ' . $this->ads_table . '
145			SET ad_views = ad_views + 1
146	2		WHERE ' . $this->db->sql_in_set('ad_id', $ad_ids);
147	2		$this->db->sql_query($sql);
148	2		}
149	2		}
150
151			/**
152			* Increment clicks for specified ad
153			*
154			* @param int $ad_id ID of an ad to increment clicks
155			* @return void
156			*/
157	2		public function increment_ad_clicks($ad_id)
158			{
159	2		$sql = 'UPDATE ' . $this->ads_table . '
160			SET ad_clicks = ad_clicks + 1
161	2		WHERE ad_id = ' . (int) $ad_id;
162	2		$this->db->sql_query($sql);
163	2		}
164
165			/**
166			* Insert new advertisement to the database
167			*
168			* @param array $data New ad data
169			* @return int New advertisement ID
170			*/
171	2	View Code Duplication	public function insert_ad($data)
172			{
173	2		$data = $this->intersect_ad_data($data);
174
175	2		$sql = 'INSERT INTO ' . $this->ads_table . ' ' . $this->db->sql_build_array('INSERT', $data);
176	2		$this->db->sql_query($sql);
177
178	2		return $this->db->sql_nextid();
179			}
180
181			/**
182			* Update advertisement
183			*
184			* @param int $ad_id Advertisement ID
185			* @param array $data List of data to update in the database
186			* @return int Number of affected rows. Can be used to determine if any ad has been updated.
187			*/
188	4	View Code Duplication	public function update_ad($ad_id, $data)
189			{
190	4		$data = $this->intersect_ad_data($data);
191
192	4		$sql = 'UPDATE ' . $this->ads_table . '
193	4		SET ' . $this->db->sql_build_array('UPDATE', $data) . '
194	4		WHERE ad_id = ' . (int) $ad_id;
195	4		$this->db->sql_query($sql);
196
197	4		return $this->db->sql_affectedrows();
198			}
199
200			/**
201			* Delete advertisement
202			*
203			* @param int $ad_id Advertisement ID
204			* @return int Number of affected rows. Can be used to determine if any ad has been deleted.
205			*/
206	1		public function delete_ad($ad_id)
207			{
208	1		$sql = 'DELETE FROM ' . $this->ads_table . '
209	1		WHERE ad_id = ' . (int) $ad_id;
210	1		$this->db->sql_query($sql);
211
212	1		return $this->db->sql_affectedrows();
213			}
214
215			/**
216			* Remove ad owner
217			*
218			* @param array $user_ids User IDs
219			* @return void
220			*/
221	3	View Code Duplication	public function remove_ad_owner(array $user_ids)
222			{
223	3		if (empty($user_ids))
224	3		{
225			return;
226			}
227
228	3		$sql = 'UPDATE ' . $this->ads_table . '
229			SET ad_owner = 0
230	3		WHERE ' . $this->db->sql_in_set('ad_owner', $user_ids);
231	3		$this->db->sql_query($sql);
232	3		}
233
234			/**
235			* Get all locations for specified advertisement
236			*
237			* @param int $ad_id Advertisement ID
238			* @return array List of template locations for specified ad
239			*/
240	7	View Code Duplication	public function get_ad_locations($ad_id)
241			{
242	7		$ad_locations = array();
243
244			$sql = 'SELECT location_id
245	7		FROM ' . $this->ad_locations_table . '
246	7		WHERE ad_id = ' . (int) $ad_id;
247	7		$result = $this->db->sql_query($sql);
248	7		while ($row = $this->db->sql_fetchrow($result))
249			{
250	5		$ad_locations[] = $row['location_id'];
251	5		}
252	7		$this->db->sql_freeresult($result);
253
254	7		return $ad_locations;
255			}
256
257			/**
258			* Insert advertisement locations
259			*
260			* @param int $ad_id Advertisement ID
261			* @param array $ad_locations List of template locations for this ad
262			* @return void
263			*/
264	2		public function insert_ad_locations($ad_id, $ad_locations)
265			{
266	2		$sql_ary = array();
267	2		foreach ($ad_locations as $ad_location)
268			{
269	2		$sql_ary[] = array(
270	2		'ad_id' => $ad_id,
271	2		'location_id' => $ad_location,
272			);
273	2		}
274	2		$this->db->sql_multi_insert($this->ad_locations_table, $sql_ary);
275	2		}
276
277			/**
278			* Delete advertisement locations
279			*
280			* @param int $ad_id Advertisement ID
281			* @return void
282			*/
283	3		public function delete_ad_locations($ad_id)
284			{
285	3		$sql = 'DELETE FROM ' . $this->ad_locations_table . '
286	3		WHERE ad_id = ' . (int) $ad_id;
287	3		$this->db->sql_query($sql);
288	3		}
289
290			/**
291			* Load memberships of the user
292			*
293			* @param int $user_id User ID to load memberships
294			* @return array List of group IDs user is member of
295			*/
296	8	View Code Duplication	public function load_memberships($user_id)
297			{
298	8		$memberships = array();
299			$sql = 'SELECT group_id
300	8		FROM ' . USER_GROUP_TABLE . '
301	8		WHERE user_id = ' . (int) $user_id . '
302	8		AND user_pending = 0';
303	8		$result = $this->db->sql_query($sql, 3600);
304	8		while ($row = $this->db->sql_fetchrow($result))
305			{
306	8		$memberships[] = $row['group_id'];
307	8		}
308	8		$this->db->sql_freeresult($result);
309	8		return $memberships;
310			}
311
312			/**
313			* Load all board groups
314			*
315			* @return array List of groups
316			*/
317	1	View Code Duplication	public function load_groups()
318			{
319			$sql = 'SELECT group_id, group_name, group_type
320	1		FROM ' . GROUPS_TABLE . "
321			WHERE group_name <> 'BOTS'
322	1		ORDER BY group_name ASC";
323	1		$result = $this->db->sql_query($sql);
324	1		$groups = $this->db->sql_fetchrowset($result);
325	1		$this->db->sql_freeresult($result);
326
327	1		return $groups;
328			}
329
330			/**
331			* Make sure only necessary data make their way to SQL query
332			*
333			* @param array $data List of data to query the database
334			* @return array Cleaned data that contain only valid keys
335			*/
336	6		protected function intersect_ad_data($data)
337			{
338	6		return array_intersect_key($data, array(
339	6		'ad_name' => '',
340	6		'ad_note' => '',
341	6		'ad_code' => '',
342	6		'ad_enabled' => '',
343	6		'ad_end_date' => '',
344	6		'ad_priority' => '',
345	6		'ad_views_limit' => '',
346	6		'ad_clicks_limit' => '',
347	6		'ad_owner' => '',
348	6		));
349			}
350
351			/**
352			* Get the random statement for this database layer
353			* Random function should generate a float value between 0 and 1
354			*
355			* @return string Random statement for current database layer
356			*/
357	6		protected function sql_random()
358			{
359	6		switch ($this->db->get_sql_layer())
360			{
361	6		case 'oracle':
362			return 'VALUE()';
363
364	6		case 'postgres':
365			return 'RANDOM()';
366
367			// https://stackoverflow.com/a/35369410/2908600
368	6		case 'sqlite':
369	6		case 'sqlite3':
370			return '(0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2)';
371
372	6		default:
373	6		return 'RAND()';
374	6		}
375			}
376			}
377

phpbb-extensions / ad-management

Pull Request — master (#110)

manager::get_ad() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like