phpbb-extensions / ad-management

ad/manager.php

<?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 */

The type phpbb\db\driver\driver_interface was not found. Maybe you did not declare it correctly or list all dependencies?

	protected $db;

	/** @var \phpbb\config\config */

The type phpbb\config\config was not found. Maybe you did not declare it correctly or list all dependencies?

	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 . '

The constant phpbb\ads\ad\USER_GROUP_TABLE was not found. Maybe you did not declare it correctly or list all dependencies?

			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

The constant phpbb\ads\ad\GROUPS_TABLE was not found. Maybe you did not declare it correctly or list all dependencies?

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