manager::insert_ad() - 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 17:52 UTC

manager::insert_ad() A

↳ Parent: manager

Complexity

Conditions	1
Paths	1

Size

Total Lines	9
Code Lines	5

Duplication

Lines	9
Ratio	100 %

Code Coverage

Tests	5
CRAP Score	1

Importance

Changes

Metric	Value
dl	9
loc	9
ccs	5
cts	5
cp	1
rs	9.6666
c	0
b	0
f	0
cc	1
eloc	5
nc	1
nop	1
crap	1

<?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);

		if ($data === false)
		{
			return array();
		}

		return $data;
	}

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

phpbb-extensions / ad-management

Pull Request — master (#75)

manager::insert_ad() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like