manager::intersect_ad_data() - Code Metrics - Inspection of "Views & Clicks" - phpbb-extensions/ad-management - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#43)

by Jakub

created 2017-07-11 19:30 UTC

manager::intersect_ad_data() A

↳ Parent: manager

Complexity

Conditions	1
Paths	1

Size

Total Lines	13
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	1

Importance

Changes

Metric	Value
c	0
b	0
f	0
dl	0
loc	13
ccs	3
cts	3
cp	1
rs	9.4285
cc	1
eloc	10
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 string */
	protected $ads_table;

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

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

	/**
	* Get specific ad
	*
	* @param	int		$ad_id	Advertisement ID
	* @return	mixed	Array with advertisement data, false if ad doesn't exist
	*/
	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;
	}

	/**
	* 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 = '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() . ')
					AND (a.ad_views_limit = 0
						OR a.ad_views_limit > a.ad_views)
					AND (a.ad_clicks_limit = 0
						OR a.ad_clicks_limit > a.ad_clicks)
					AND ' . $this->db->sql_in_set('al.location_id', $ad_locations) . '
				ORDER BY (' . $this->sql_random() . ' * a.ad_priority)
			) z
			ORDER BY z.location_id';
		$result = $this->db->sql_query($sql);
		$data = $this->db->sql_fetchrowset($result);
		$this->db->sql_freeresult($result);

		return $data;
	}

	/**
	* Get all advertisements
	*
	* @return	array	List of all ads
	*/
	public function get_all_ads()

	{
		$sql = 'SELECT ad_id, 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;
	}

	/**
	* 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);
			$result = $this->db->sql_query($sql);
$myVar = 'Value';
$higher = false;

if (rand(1, 6) > 3) {
    $higher = true;
} else {
    $higher = false;
}
		}
	}

	/**
	* Increment clicks for specified ad
	*
	* @param	array	$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 = ' . $ad_id;
		$result = $this->db->sql_query($sql);
$myVar = 'Value';
$higher = false;

if (rand(1, 6) > 3) {
    $higher = true;
} else {
    $higher = false;
}
	}

	/**
	* 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();
	}

	/**
	* 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 . '
			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'	=> '',
		));
	}

	/**
	* 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)';

			/* All other cases should use the default

			case 'mssql':
			case 'mssql_odbc':
			case 'mssqlnative':
			case 'mysql':
			case 'mysqli':*/
			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 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	46		public function __construct(\phpbb\db\driver\driver_interface $db, $ads_table, $ad_locations_table)
32			{
33	46		$this->db = $db;
34	46		$this->ads_table = $ads_table;
35	46		$this->ad_locations_table = $ad_locations_table;
36	46		}
37
38			/**
39			* Get specific ad
40			*
41			* @param int $ad_id Advertisement ID
42			* @return mixed Array with advertisement data, false if ad doesn't exist
43			*/
44	4		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	2		public function get_ads($ad_locations)
63			{
64			$sql = 'SELECT location_id, ad_id, ad_code
65			FROM (
66			SELECT al.location_id, a.ad_id, a.ad_code
67	2		FROM ' . $this->ad_locations_table . ' al
68	2		LEFT JOIN ' . $this->ads_table . ' a
69			ON (al.ad_id = a.ad_id)
70			WHERE a.ad_enabled = 1
71			AND (a.ad_end_date = 0
72	2		OR a.ad_end_date > ' . time() . ')
73	2		AND (a.ad_views_limit = 0
74	2		OR a.ad_views_limit > a.ad_views)
75			AND (a.ad_clicks_limit = 0
76	2		OR a.ad_clicks_limit > a.ad_clicks)
77	2		AND ' . $this->db->sql_in_set('al.location_id', $ad_locations) . '
78	2		ORDER BY (' . $this->sql_random() . ' * a.ad_priority)
79	2		) z
80			ORDER BY z.location_id';
81	2		$result = $this->db->sql_query($sql);
82			$data = $this->db->sql_fetchrowset($result);
83			$this->db->sql_freeresult($result);
84
85			return $data;
86			}
87
88			/**
89	1		* Get all advertisements
90			*
91			* @return array List of all ads
92	1		*/
93	1	View Code Duplication	public function get_all_ads()
			1 ignored issue – show Duplication introduced 2017-07-11 15:51 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...
94	1		{
95	1		$sql = 'SELECT ad_id, ad_name, ad_enabled, ad_end_date, ad_views, ad_clicks, ad_views_limit, ad_clicks_limit
96			FROM ' . $this->ads_table;
97	1		$result = $this->db->sql_query($sql);
98			$data = $this->db->sql_fetchrowset($result);
99			$this->db->sql_freeresult($result);
100
101			return $data;
102			}
103
104			/**
105			* Increment views for specified ads
106	1		*
107			* Note, that views are incremented only by one even when
108	1		* an ad is displayed multiple times on the same page.
109			*
110	1		* @param array $ad_ids IDs of ads to increment views
111	1		* @return void
112			*/
113	1		public function increment_ads_views($ad_ids)
114			{
115			if (!empty($ad_ids))
116			{
117			$sql = 'UPDATE ' . $this->ads_table . '
118			SET ad_views = ad_views + 1
119			WHERE ' . $this->db->sql_in_set('ad_id', $ad_ids);
120			$result = $this->db->sql_query($sql);
			0 ignored issues – show Unused Code introduced 2017-07-11 15:51 UTC by Report Bug Copy Issue Report `$result` is not used, you could remove the assignment. This check looks for variable assignements that are either overwritten by other assignments or where the variable is not used subsequently. $myVar = 'Value'; $higher = false; if (rand(1, 6) > 3) { $higher = true; } else { $higher = false; } Both the `$myVar` assignment in line 1 and the `$higher` assignment in line 2 are dead. The first because `$myVar` is never used and the second because `$higher` is always overwritten for every possible time line. Loading history...
121			}
122			}
123	7
124			/**
125	7		* Increment clicks for specified ad
126			*
127	7		* @param array $ad_id ID of an ad to increment clicks
128	7		* @return void
129	7		*/
130	7	View Code Duplication	public function increment_ad_clicks($ad_id)
			1 ignored issue – show Duplication introduced 2017-07-11 15:51 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...
131			{
132	7		$sql = 'UPDATE ' . $this->ads_table . '
133			SET ad_clicks = ad_clicks + 1
134			WHERE ad_id = ' . $ad_id;
135			$result = $this->db->sql_query($sql);
			0 ignored issues – show Unused Code introduced 2017-07-11 15:51 UTC by Report Bug Copy Issue Report `$result` is not used, you could remove the assignment. This check looks for variable assignements that are either overwritten by other assignments or where the variable is not used subsequently. $myVar = 'Value'; $higher = false; if (rand(1, 6) > 3) { $higher = true; } else { $higher = false; } Both the `$myVar` assignment in line 1 and the `$higher` assignment in line 2 are dead. The first because `$myVar` is never used and the second because `$higher` is always overwritten for every possible time line. Loading history...
136			}
137
138			/**
139			* Insert new advertisement to the database
140			*
141	2		* @param array $data New ad data
142			* @return int New advertisement ID
143	2		*/
144	2	View Code Duplication	public function insert_ad($data)
145	2		{
146			$data = $this->intersect_ad_data($data);
147	2
148			$sql = 'INSERT INTO ' . $this->ads_table . ' ' . $this->db->sql_build_array('INSERT', $data);
149			$this->db->sql_query($sql);
150
151			return $this->db->sql_nextid();
152			}
153
154			/**
155			* Update advertisement
156	1		*
157			* @param int $ad_id Advertisement ID
158	1		* @param array $data List of data to update in the database
159			* @return int Number of affected rows. Can be used to determine if any ad has been updated.
160			*/
161	1	View Code Duplication	public function update_ad($ad_id, $data)
162	1		{
163	1		$data = $this->intersect_ad_data($data);
164	1
165			$sql = 'UPDATE ' . $this->ads_table . '
166	1		SET ' . $this->db->sql_build_array('UPDATE', $data) . '
167	1		WHERE ad_id = ' . (int) $ad_id;
168	1		$this->db->sql_query($sql);
169
170	1		return $this->db->sql_affectedrows();
171			}
172
173			/**
174			* Delete advertisement
175			*
176			* @param int $ad_id Advertisement ID
177			* @return int Number of affected rows. Can be used to determine if any ad has been deleted.
178			*/
179		View Code Duplication	public function delete_ad($ad_id)
180	2		{
181			$sql = 'DELETE FROM ' . $this->ads_table . '
182	2		WHERE ad_id = ' . (int) $ad_id;
183	2		$this->db->sql_query($sql);
184
185	2		return $this->db->sql_affectedrows();
186	2		}
187	2
188			/**
189	2		* Get all locations for specified advertisement
190	2		*
191	2		* @param int $ad_id Advertisement ID
192			* @return array List of template locations for specified ad
193			*/
194		View Code Duplication	public function get_ad_locations($ad_id)
195			{
196			$ad_locations = array();
197
198			$sql = 'SELECT location_id
199	3		FROM ' . $this->ad_locations_table . '
200			WHERE ad_id = ' . (int) $ad_id;
201	3		$result = $this->db->sql_query($sql);
202	3		while ($row = $this->db->sql_fetchrow($result))
203	3		{
204	3		$ad_locations[] = $row['location_id'];
205			}
206			$this->db->sql_freeresult($result);
207
208			return $ad_locations;
209			}
210
211			/**
212	4		* Insert advertisement locations
213			*
214	4		* @param int $ad_id Advertisement ID
215			* @param array $ad_locations List of template locations for this ad
216	4		* @return void
217	4		*/
218	4		public function insert_ad_locations($ad_id, $ad_locations)
219	4		{
220	4		$sql_ary = array();
221			foreach ($ad_locations as $ad_location)
222	4		{
223	4		$sql_ary[] = array(
224	4		'ad_id' => $ad_id,
225	4		'location_id' => $ad_location,
226			);
227			}
228			$this->db->sql_multi_insert($this->ad_locations_table, $sql_ary);
229			}
230
231			/**
232			* Delete advertisement locations
233	2		*
234			* @param int $ad_id Advertisement ID
235			* @return void
236	2		*/
237	2		public function delete_ad_locations($ad_id)
238	2		{
239	2		$sql = 'DELETE FROM ' . $this->ad_locations_table . '
240	2		WHERE ad_id = ' . (int) $ad_id;
241			$this->db->sql_query($sql);
242	2		}
243
244			/**
245			* Load memberships of the user
246			*
247			* @param int $user_id User ID to load memberships
248			* @return array List of group IDs user is member of
249			*/
250		View Code Duplication	public function load_memberships($user_id)
251	8		{
252			$memberships = array();
253	8		$sql = 'SELECT group_id
254	8		FROM ' . USER_GROUP_TABLE . '
255	8		WHERE user_id = ' . (int) $user_id . '
256	8		AND user_pending = 0';
257	8		$result = $this->db->sql_query($sql, 3600);
258	8		while ($row = $this->db->sql_fetchrow($result))
259	8		{
260	8		$memberships[] = $row['group_id'];
261			}
262			$this->db->sql_freeresult($result);
263			return $memberships;
264			}
265
266			/**
267			* Load all board groups
268			*
269	2		* @return array List of groups
270			*/
271	2	View Code Duplication	public function load_groups()
272			{
273	2		$sql = 'SELECT group_id, group_name, group_type
274			FROM ' . GROUPS_TABLE . '
275			ORDER BY group_name ASC';
276	2		$result = $this->db->sql_query($sql);
277			$groups = $this->db->sql_fetchrowset($result);
278			$this->db->sql_freeresult($result);
279
280	2		return $groups;
281	2		}
282
283			/**
284			* Make sure only necessary data make their way to SQL query
285			*
286			* @param array $data List of data to query the database
287			* @return array Cleaned data that contain only valid keys
288			*/
289			protected function intersect_ad_data($data)
290	2		{
291	2		return array_intersect_key($data, array(
292	2		'ad_name' => '',
293			'ad_note' => '',
294			'ad_code' => '',
295			'ad_enabled' => '',
296			'ad_end_date' => '',
297			'ad_priority' => '',
298			'ad_views_limit' => '',
299			'ad_clicks_limit' => '',
300			));
301			}
302
303			/**
304			* Get the random statement for this database layer
305			* Random function should generate a float value between 0 and 1
306			*
307			* @return string Random statement for current database layer
308			*/
309			protected function sql_random()
310			{
311			switch ($this->db->get_sql_layer())
312			{
313			case 'oracle':
314			return 'VALUE()';
315
316			case 'postgres':
317			return 'RANDOM()';
318
319			// https://stackoverflow.com/a/35369410/2908600
320			case 'sqlite':
321			case 'sqlite3':
322			return '(0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2)';
323
324			/* All other cases should use the default
			1 ignored issue – show Unused Code Comprehensibility introduced 2017-05-31 10:41 UTC by Report Bug Copy Issue Report `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...
325			case 'mssql':
326			case 'mssql_odbc':
327			case 'mssqlnative':
328			case 'mysql':
329			case 'mysqli':*/
330			default:
331			return 'RAND()';
332			}
333			}
334			}
335

phpbb-extensions / ad-management

Pull Request — master (#43)

manager::intersect_ad_data() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like