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-11-10 15:33 UTC

↳ Parent: manager

Complexity

Conditions	1
Paths	1

Size

Total Lines	10
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	1
CRAP Score	1

Importance

Changes

Metric	Value
cc	1
eloc	7
nc	1
nop	1
dl	0
loc	10
ccs	1
cts	1
cp	1
crap	1
rs	10
c	0
b	0
f	0

<?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 \phpbb\user */
filter:
    dependency_paths: ["lib/*"]
	protected $user;

	/** @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    \phpbb\user                       $user               User 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, \phpbb\user $user, $ads_table, $ad_locations_table, $ad_group_table)
	{
		$this->db = $db;
		$this->config = $config;
		$this->user = $user;
		$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) . ')' : '';

		// Get user's current time and convert to UTC equivalent for comparison
		$user_now = $this->user->create_datetime();
		$sql_time = $this->user->get_timestamp_from_format('Y-m-d H:i:s', $user_now->format('Y-m-d H:i:s'), new \DateTimeZone('UTC'));

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

phpbb-extensions / ad-management

manager::get_ads_by_owner() A last analyzed 2025-11-10 15:33 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

manager::get_ads_by_owner() A
last analyzed 2025-11-10 15:33 UTC