manager::get_ads_by_owner() - Code Metrics - phpbb-extensions/ad-management - Measure and Improve Code Quality continuously with Scrutinizer

manager::get_ads_by_owner() A
last analyzed 2025-09-27 16:11 UTC

↳ Parent: manager

Complexity

Conditions	1
Paths	1

Size

Total Lines	10
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	7
CRAP Score	1

Importance

Changes

Metric	Value
cc	1
eloc	7
c	0
b	0
f	0
nc	1
nop	1
dl	0
loc	10
rs	10
ccs	7
cts	7
cp	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 */
filter:
    dependency_paths: ["lib/*"]
	protected $db;

	/** @var \phpbb\config\config */
filter:
    dependency_paths: ["lib/*"]
	protected $config;

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

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

	/** @var string */
	protected $ad_group_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
	 * @param    string                            $ad_group_table 	   Ad group table
	 */
	public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config, $ads_table, $ad_locations_table, $ad_group_table)
	{
		$this->db = $db;
		$this->config = $config;
		$this->ads_table = $ads_table;
		$this->ad_locations_table = $ad_locations_table;
		$this->ad_group_table = $ad_group_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 : [];
	}

	/**
	 * Get one ad per every location
	 *
	 * @param    array $ad_locations List of ad locations to fetch ads for
	 * @param    array $user_groups List of user groups
	 * @param    bool  $non_content_page Is current page non-content oriented (e.g.: login, UCP, MCP)? Default is false.
	 * @return    array    List of ad codes for each location
	 */
	public function get_ads($ad_locations, $user_groups, $non_content_page = false)
	{
		$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_where_non_content = $non_content_page ? 'AND a.ad_content_only = 0' : '';
		$sql_where_user_groups = !empty($user_groups) ? 'AND NOT EXISTS (SELECT ag.group_id FROM ' . $this->ad_group_table . ' ag WHERE ag.ad_id = a.ad_id AND ' . $this->db->sql_in_set('ag.group_id', $user_groups) . ')' : '';

		$sql = 'SELECT al.location_id, a.ad_id, a.ad_code, a.ad_centering
				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_start_date = 0
						OR a.ad_start_date < ' . time() . ')
					AND (a.ad_end_date = 0
						OR a.ad_end_date > ' . time() . ")
					$sql_where_views
					$sql_where_clicks
					$sql_where_non_content
					$sql_where_user_groups
					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);

		if (empty($data))
		{
			return [];
		}

		$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_start_date, 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_start_date, 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)
	{
		// extract ad groups here because it gets filtered in intersect_ad_data()
		$ad_groups = $data['ad_groups'];
		$data = $this->intersect_ad_data($data);

		// add a row to ads table
		$sql = 'INSERT INTO ' . $this->ads_table . ' ' . $this->db->sql_build_array('INSERT', $data);
		$this->db->sql_query($sql);
		$ad_id = (int) $this->db->sql_nextid();

		$this->insert_ad_group_data($ad_id, $ad_groups);

		return $ad_id;
	}

	/**
	 * 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)
	{
		// extract ad groups here because it gets filtered in intersect_ad_data()
		$ad_groups = $data['ad_groups'] ?? [];
		$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);
		$result = $this->db->sql_affectedrows();

		$this->remove_ad_group_data($ad_id);
		$this->insert_ad_group_data($ad_id, $ad_groups);

		return $result;
	}

	/**
	 * 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 = [];

		$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 = [];
		foreach ($ad_locations as $ad_location)
		{
			$sql_ary[] = [
				'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 = [];
		$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
	 *
	 * @param	int		$ad_id	Advertisement ID
	 * @return	array	List of groups
	 */
	public function load_groups($ad_id)
	{
		$sql = 'SELECT g.group_id, g.group_name, (
				SELECT COUNT(ad_id)
				FROM ' . $this->ad_group_table . ' ag
				WHERE ag.ad_id = ' . (int) $ad_id . '
					AND ag.group_id = g.group_id
			) as group_selected
			FROM ' . GROUPS_TABLE . " g

			WHERE g.group_name <> 'BOTS'
			ORDER BY g.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, [
			'ad_name'			=> '',
			'ad_note'			=> '',
			'ad_code'			=> '',
			'ad_enabled'		=> '',
			'ad_start_date'		=> '',
			'ad_end_date'		=> '',
			'ad_priority'		=> '',
			'ad_views_limit'	=> '',
			'ad_clicks_limit'	=> '',
			'ad_owner'			=> '',
			'ad_content_only'	=> '',
			'ad_centering'		=> '',
		]);
	}

	/**
	 * 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 'DBMS_RANDOM.VALUE';

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

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

			// https://improve.dk/weighted-random-selections-in-sql-server/
			case 'mssql':
			case 'mssql_odbc':
			case 'mssqlnative':
				return 'RAND(CAST(NEWID() AS VARBINARY))';

			default:
				return 'RAND()';
		}
	}

	/**
	 * Add rows to ad_group table.
	 *
	 * @param int   $ad_id     Advertisement ID
	 * @param array $ad_groups List of groups that should not see this ad
	 * @return void
	 */
	protected function insert_ad_group_data($ad_id, $ad_groups)
	{
		$sql_ary = [];
		foreach ($ad_groups as $group)
		{
			$sql_ary[] = [
				'ad_id'		=> $ad_id,
				'group_id'	=> $group,
			];
		}
		$this->db->sql_multi_insert($this->ad_group_table, $sql_ary);
	}

	/**
	 * Remove all rows of specified ad in ad_group table
	 *
	 * @param int	$ad_id	Advertisement ID
	 * @return void
	 */
	protected function remove_ad_group_data($ad_id)
	{
		$sql = 'DELETE FROM ' . $this->ad_group_table . '
			WHERE ad_id = ' . (int) $ad_id;
		$this->db->sql_query($sql);
	}
}


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 */
		0 ignored issues – show Bug introduced 2022-06-19 06:31 UTC by Report Bug Copy Issue Report The type `phpbb\db\driver\driver_interface` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
16		protected $db;
17
18		/** @var \phpbb\config\config */
		0 ignored issues – show Bug introduced 2022-06-19 06:31 UTC by Report Bug Copy Issue Report The type `phpbb\config\config` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
19		protected $config;
20
21		/** @var string */
22		protected $ads_table;
23
24		/** @var string */
25		protected $ad_locations_table;
26
27		/** @var string */
28		protected $ad_group_table;
29
30		/**
31		* Constructor
32		*
33		* @param \phpbb\db\driver\driver_interface $db DB driver interface
34		* @param \phpbb\config\config $config Config object
35		* @param string $ads_table Ads table
36		* @param string $ad_locations_table Ad locations table
37		* @param string $ad_group_table Ad group table
38		*/
39	57	public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config, $ads_table, $ad_locations_table, $ad_group_table)
40		{
41	57	$this->db = $db;
42	57	$this->config = $config;
43	57	$this->ads_table = $ads_table;
44	57	$this->ad_locations_table = $ad_locations_table;
45	57	$this->ad_group_table = $ad_group_table;
46	57	}
47
48		/**
49		* Get specific ad
50		*
51		* @param int $ad_id Advertisement ID
52		* @return array Array with advertisement data
53		*/
54	13	public function get_ad($ad_id)
55		{
56		$sql = 'SELECT *
57	13	FROM ' . $this->ads_table . '
58	13	WHERE ad_id = ' . (int) $ad_id;
59	13	$result = $this->db->sql_query($sql);
60	13	$data = $this->db->sql_fetchrow($result);
61	13	$this->db->sql_freeresult($result);
62
63	13	return $data !== false ? $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		* @param array $user_groups List of user groups
71		* @param bool $non_content_page Is current page non-content oriented (e.g.: login, UCP, MCP)? Default is false.
72		* @return array List of ad codes for each location
73		*/
74	8	public function get_ads($ad_locations, $user_groups, $non_content_page = false)
75		{
76	8	$sql_where_views = $this->config['phpbb_ads_enable_views'] ? 'AND (a.ad_views_limit = 0 OR a.ad_views_limit > a.ad_views)' : '';
77	8	$sql_where_clicks = $this->config['phpbb_ads_enable_clicks'] ? 'AND (a.ad_clicks_limit = 0 OR a.ad_clicks_limit > a.ad_clicks)' : '';
78	8	$sql_where_non_content = $non_content_page ? 'AND a.ad_content_only = 0' : '';
79	8	$sql_where_user_groups = !empty($user_groups) ? 'AND NOT EXISTS (SELECT ag.group_id FROM ' . $this->ad_group_table . ' ag WHERE ag.ad_id = a.ad_id AND ' . $this->db->sql_in_set('ag.group_id', $user_groups) . ')' : '';
80
81		$sql = 'SELECT al.location_id, a.ad_id, a.ad_code, a.ad_centering
82	8	FROM ' . $this->ad_locations_table . ' al
83	8	LEFT JOIN ' . $this->ads_table . ' a
84		ON (al.ad_id = a.ad_id)
85		WHERE a.ad_enabled = 1
86		AND (a.ad_start_date = 0
87	8	OR a.ad_start_date < ' . time() . ')
88		AND (a.ad_end_date = 0
89	8	OR a.ad_end_date > ' . time() . ")
90		$sql_where_views
91	8	$sql_where_clicks
92	8	$sql_where_non_content
93	8	$sql_where_user_groups
94	8	AND " . $this->db->sql_in_set('al.location_id', $ad_locations) . '
95	8	ORDER BY al.location_id, (' . $this->sql_random() . ' * a.ad_priority) DESC';
96	8	$result = $this->db->sql_query($sql);
97	8	$data = $this->db->sql_fetchrowset($result);
98	8	$this->db->sql_freeresult($result);
99
100	8	if (empty($data))
101	8	{
102	3	return [];
103		}
104
105	5	$current_location_id = '';
106	5	$data = array_filter($data, function ($row) use (&$current_location_id) {
107	5	$return = $current_location_id !== $row['location_id'];
108	5	$current_location_id = $row['location_id'];
109	5	return $return;
110	5	});
111
112	5	return $data;
113		}
114
115		/**
116		* Get all advertisements.
117		*
118		* @return array List of all ads
119		*/
120	2	public function get_all_ads()
121		{
122		$sql = 'SELECT ad_id, ad_priority, ad_name, ad_enabled, ad_start_date, ad_end_date, ad_views, ad_clicks, ad_views_limit, ad_clicks_limit
123	2	FROM ' . $this->ads_table;
124	2	$result = $this->db->sql_query($sql);
125	2	$data = $this->db->sql_fetchrowset($result);
126	2	$this->db->sql_freeresult($result);
127
128	2	return $data;
129		}
130
131		/**
132		* Get all owner's ads
133		*
134		* @param int $user_id Ad owner
135		* @return array List of owner's ads
136		*/
137	5	public function get_ads_by_owner($user_id)
138		{
139		$sql = 'SELECT ad_id, ad_name, ad_enabled, ad_start_date, ad_end_date, ad_views, ad_views_limit, ad_clicks, ad_clicks_limit
140	5	FROM ' . $this->ads_table . '
141	5	WHERE ad_owner = ' . (int) $user_id;
142	5	$result = $this->db->sql_query($sql);
143	5	$data = $this->db->sql_fetchrowset($result);
144	5	$this->db->sql_freeresult($result);
145
146	5	return $data;
147		}
148
149		/**
150		* Increment views for specified ads
151		*
152		* Note, that views are incremented only by one even when
153		* an ad is displayed multiple times on the same page.
154		*
155		* @param array $ad_ids IDs of ads to increment views
156		* @return void
157		*/
158	2	public function increment_ads_views($ad_ids)
159		{
160	2	if (!empty($ad_ids))
161	2	{
162	2	$sql = 'UPDATE ' . $this->ads_table . '
163		SET ad_views = ad_views + 1
164	2	WHERE ' . $this->db->sql_in_set('ad_id', $ad_ids);
165	2	$this->db->sql_query($sql);
166	2	}
167	2	}
168
169		/**
170		* Increment clicks for specified ad
171		*
172		* @param int $ad_id ID of an ad to increment clicks
173		* @return void
174		*/
175	2	public function increment_ad_clicks($ad_id)
176		{
177	2	$sql = 'UPDATE ' . $this->ads_table . '
178		SET ad_clicks = ad_clicks + 1
179	2	WHERE ad_id = ' . (int) $ad_id;
180	2	$this->db->sql_query($sql);
181	2	}
182
183		/**
184		* Insert new advertisement to the database
185		*
186		* @param array $data New ad data
187		* @return int New advertisement ID
188		*/
189	2	public function insert_ad($data)
190		{
191		// extract ad groups here because it gets filtered in intersect_ad_data()
192	2	$ad_groups = $data['ad_groups'];
193	2	$data = $this->intersect_ad_data($data);
194
195		// add a row to ads table
196	2	$sql = 'INSERT INTO ' . $this->ads_table . ' ' . $this->db->sql_build_array('INSERT', $data);
197	2	$this->db->sql_query($sql);
198	2	$ad_id = (int) $this->db->sql_nextid();
199
200	2	$this->insert_ad_group_data($ad_id, $ad_groups);
201
202	2	return $ad_id;
203		}
204
205		/**
206		* Update advertisement
207		*
208		* @param int $ad_id Advertisement ID
209		* @param array $data List of data to update in the database
210		* @return int Number of affected rows. Can be used to determine if any ad has been updated.
211		*/
212	4	public function update_ad($ad_id, $data)
213		{
214		// extract ad groups here because it gets filtered in intersect_ad_data()
215	4	$ad_groups = $data['ad_groups'] ?? [];
216	4	$data = $this->intersect_ad_data($data);
217
218	4	$sql = 'UPDATE ' . $this->ads_table . '
219	4	SET ' . $this->db->sql_build_array('UPDATE', $data) . '
220	4	WHERE ad_id = ' . (int) $ad_id;
221	4	$this->db->sql_query($sql);
222	4	$result = $this->db->sql_affectedrows();
223
224	4	$this->remove_ad_group_data($ad_id);
225	4	$this->insert_ad_group_data($ad_id, $ad_groups);
226
227	4	return $result;
228		}
229
230		/**
231		* Delete advertisement
232		*
233		* @param int $ad_id Advertisement ID
234		* @return int Number of affected rows. Can be used to determine if any ad has been deleted.
235		*/
236	1	public function delete_ad($ad_id)
237		{
238	1	$sql = 'DELETE FROM ' . $this->ads_table . '
239	1	WHERE ad_id = ' . (int) $ad_id;
240	1	$this->db->sql_query($sql);
241
242	1	return $this->db->sql_affectedrows();
243		}
244
245		/**
246		* Remove ad owner
247		*
248		* @param array $user_ids User IDs
249		* @return void
250		*/
251	3	public function remove_ad_owner(array $user_ids)
252		{
253	3	if (empty($user_ids))
254	3	{
255		return;
256		}
257
258	3	$sql = 'UPDATE ' . $this->ads_table . '
259		SET ad_owner = 0
260	3	WHERE ' . $this->db->sql_in_set('ad_owner', $user_ids);
261	3	$this->db->sql_query($sql);
262	3	}
263
264		/**
265		* Get all locations for specified advertisement
266		*
267		* @param int $ad_id Advertisement ID
268		* @return array List of template locations for specified ad
269		*/
270	7	public function get_ad_locations($ad_id)
271		{
272	7	$ad_locations = [];
273
274		$sql = 'SELECT location_id
275	7	FROM ' . $this->ad_locations_table . '
276	7	WHERE ad_id = ' . (int) $ad_id;
277	7	$result = $this->db->sql_query($sql);
278	7	while ($row = $this->db->sql_fetchrow($result))
279		{
280	5	$ad_locations[] = $row['location_id'];
281	5	}
282	7	$this->db->sql_freeresult($result);
283
284	7	return $ad_locations;
285		}
286
287		/**
288		* Insert advertisement locations
289		*
290		* @param int $ad_id Advertisement ID
291		* @param array $ad_locations List of template locations for this ad
292		* @return void
293		*/
294	2	public function insert_ad_locations($ad_id, $ad_locations)
295		{
296	2	$sql_ary = [];
297	2	foreach ($ad_locations as $ad_location)
298		{
299	2	$sql_ary[] = [
300	2	'ad_id' => $ad_id,
301	2	'location_id' => $ad_location,
302		];
303	2	}
304	2	$this->db->sql_multi_insert($this->ad_locations_table, $sql_ary);
305	2	}
306
307		/**
308		* Delete advertisement locations
309		*
310		* @param int $ad_id Advertisement ID
311		* @return void
312		*/
313	3	public function delete_ad_locations($ad_id)
314		{
315	3	$sql = 'DELETE FROM ' . $this->ad_locations_table . '
316	3	WHERE ad_id = ' . (int) $ad_id;
317	3	$this->db->sql_query($sql);
318	3	}
319
320		/**
321		* Load memberships of the user
322		*
323		* @param int $user_id User ID to load memberships
324		* @return array List of group IDs user is member of
325		*/
326	2	public function load_memberships($user_id)
327		{
328	2	$memberships = [];
329		$sql = 'SELECT group_id
330	2	FROM ' . USER_GROUP_TABLE . '
		0 ignored issues – show Bug introduced 2022-06-19 06:31 UTC by Report Bug Copy Issue Report The constant `phpbb\ads\ad\USER_GROUP_TABLE` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
331	2	WHERE user_id = ' . (int) $user_id . '
332	2	AND user_pending = 0';
333	2	$result = $this->db->sql_query($sql, 3600);
334	2	while ($row = $this->db->sql_fetchrow($result))
335		{
336	2	$memberships[] = $row['group_id'];
337	2	}
338	2	$this->db->sql_freeresult($result);
339	2	return $memberships;
340		}
341
342		/**
343		* Load all board groups
344		*
345		* @param int $ad_id Advertisement ID
346		* @return array List of groups
347		*/
348	1	public function load_groups($ad_id)
349		{
350		$sql = 'SELECT g.group_id, g.group_name, (
351		SELECT COUNT(ad_id)
352	1	FROM ' . $this->ad_group_table . ' ag
353	1	WHERE ag.ad_id = ' . (int) $ad_id . '
354		AND ag.group_id = g.group_id
355		) as group_selected
356	1	FROM ' . GROUPS_TABLE . " g
		0 ignored issues – show Bug introduced 2022-06-19 06:31 UTC by Report Bug Copy Issue Report The constant `phpbb\ads\ad\GROUPS_TABLE` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
357		WHERE g.group_name <> 'BOTS'
358	1	ORDER BY g.group_name ASC";
359	1	$result = $this->db->sql_query($sql);
360	1	$groups = $this->db->sql_fetchrowset($result);
361	1	$this->db->sql_freeresult($result);
362
363	1	return $groups;
364		}
365
366		/**
367		* Make sure only necessary data make their way to SQL query
368		*
369		* @param array $data List of data to query the database
370		* @return array Cleaned data that contain only valid keys
371		*/
372	6	protected function intersect_ad_data($data)
373		{
374	6	return array_intersect_key($data, [
375	6	'ad_name' => '',
376	6	'ad_note' => '',
377	6	'ad_code' => '',
378	6	'ad_enabled' => '',
379	6	'ad_start_date' => '',
380	6	'ad_end_date' => '',
381	6	'ad_priority' => '',
382	6	'ad_views_limit' => '',
383	6	'ad_clicks_limit' => '',
384	6	'ad_owner' => '',
385	6	'ad_content_only' => '',
386	6	'ad_centering' => '',
387	6	]);
388		}
389
390		/**
391		* Get the random statement for this database layer
392		* Random function should generate a float value between 0 and 1
393		*
394		* @return string Random statement for current database layer
395		*/
396	8	protected function sql_random()
397		{
398	8	switch ($this->db->get_sql_layer())
399		{
400	8	case 'oracle':
401		return 'DBMS_RANDOM.VALUE';
402
403	8	case 'postgres':
404		return 'RANDOM()';
405
406		// https://stackoverflow.com/a/35369410/2908600
407	8	case 'sqlite':
408	8	case 'sqlite3':
409		return '(0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2)';
410
411	8	// https://improve.dk/weighted-random-selections-in-sql-server/
412	8	case 'mssql':
413	8	case 'mssql_odbc':
414		case 'mssqlnative':
415		return 'RAND(CAST(NEWID() AS VARBINARY))';
416
417		default:
418		return 'RAND()';
419		}
420		}
421
422		/**
423	6	* Add rows to ad_group table.
424		*
425	6	* @param int $ad_id Advertisement ID
426	6	* @param array $ad_groups List of groups that should not see this ad
427		* @return void
428	2	*/
429	2	protected function insert_ad_group_data($ad_id, $ad_groups)
430	2	{
431		$sql_ary = [];
432	6	foreach ($ad_groups as $group)
433	6	{
434	6	$sql_ary[] = [
435		'ad_id' => $ad_id,
436		'group_id' => $group,
437		];
438		}
439		$this->db->sql_multi_insert($this->ad_group_table, $sql_ary);
440		}
441
442	4	/**
443		* Remove all rows of specified ad in ad_group table
444	4	*
445	4	* @param int $ad_id Advertisement ID
446	4	* @return void
447	4	*/
448		protected function remove_ad_group_data($ad_id)
449		{
450		$sql = 'DELETE FROM ' . $this->ad_group_table . '
451		WHERE ad_id = ' . (int) $ad_id;
452		$this->db->sql_query($sql);
453		}
454		}
455

phpbb-extensions / ad-management

manager::get_ads_by_owner() A last analyzed 2025-09-27 16:11 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

manager::get_ads_by_owner() A
last analyzed 2025-09-27 16:11 UTC