transactions::query_sql_count() - Code Metrics - Inspection of "some little features" - Skouat/ext_paypal_donation - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#23)

by Mario

created 2016-01-23 18:46 UTC

transactions::query_sql_count() A

↳ Parent: transactions

Complexity

Conditions	2
Paths	2

Size

Total Lines	15
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	1

Metric	Value
c	1
b	0
f	1
dl	0
loc	15
rs	9.4285
cc	2
eloc	8
nc	2
nop	2

<?php
/**
 *
 * PayPal Donation extension for the phpBB Forum Software package.
 *
 * @copyright (c) 2015 Skouat
 * @license GNU General Public License, version 2 (GPL-2.0)
 *
 */

namespace skouat\ppde\operators;

use Symfony\Component\DependencyInjection\ContainerInterface;

class transactions
{
	protected $container;
	protected $db;
	protected $ppde_transactions_log_table;

	/**
	 * Constructor
	 *
	 * @param ContainerInterface                $container                   Service container interface
	 * @param \phpbb\db\driver\driver_interface $db                          Database connection
	 * @param string                            $ppde_transactions_log_table Table name
	 *
	 * @access public
	 */
	public function __construct(ContainerInterface $container, \phpbb\db\driver\driver_interface $db, $ppde_transactions_log_table)
	{
		$this->container = $container;
		$this->db = $db;
		$this->ppde_transactions_log_table = $ppde_transactions_log_table;
	}

	/**
	 * SQL Query to return Transaction log data table
	 *
	 * @param $transaction_id
	 *
	 * @return string
	 * @access public
	 */
	public function build_sql_data($transaction_id = 0)
	{
		// Build main sql request
		$sql_ary = array(
			'SELECT'    => '*, u.username',
			'FROM'      => array($this->ppde_transactions_log_table => 'txn'),
			'LEFT_JOIN' => array(
				array(
					'FROM' => array(USERS_TABLE => 'u'),
					'ON'   => 'u.user_id = txn.user_id',
				),
			),
			'ORDER_BY'  => 'txn.transaction_id',
		);

		// Use WHERE clause when $currency_id is different from 0
		if ((int) $transaction_id)
		{
			$sql_ary['WHERE'] = 'txn.transaction_id = ' . (int) $transaction_id;
		}

		// Return all transactions entities
		return $this->db->sql_build_query('SELECT', $sql_ary);
	}

	/**
	 * Returns the SQL Query for generation the donors list
	 *
	 * @param int    $max_txn_id Identifier of the transaction logged in the DB
	 * @param string $order_by
	 *
	 * @return array
	 * @access public
	 */
	public function get_sql_donorlist_ary($max_txn_id = 0, $order_by = '')
	{
		// Build main sql request
		$donorlist_sql_ary = array(
			'SELECT'    => 'txn.*, MAX(txn.transaction_id) AS max_txn_id, SUM(txn.mc_gross) AS amount, u.username, u.user_colour',
			'FROM'      => array($this->ppde_transactions_log_table => 'txn'),
			'LEFT_JOIN' => array(
				array(
					'FROM' => array(USERS_TABLE => 'u'),
					'ON'   => 'u.user_id = txn.user_id',
				),
			),
			'WHERE'     => 'txn.user_id <> ' . ANONYMOUS . "
							AND txn.payment_status = 'Completed'",
			'GROUP_BY'  => 'txn.user_id',
			'ORDER_BY'  => 'txn.transaction_id DESC',
		);

		if ($order_by)
		{
			$donorlist_sql_ary['ORDER_BY'] = $order_by;
		}

		if ($max_txn_id)
		{
			$donorlist_sql_ary['WHERE'] = 'txn.transaction_id = ' . $max_txn_id;
			unset($donorlist_sql_ary['GROUP_BY'], $donorlist_sql_ary['ORDER_BY']);
		}

		// Return all transactions entities
		return $donorlist_sql_ary;
	}

	/**
	 * SQL Query to return donors list details
	 *
	 * @return string
	 * @access public
	 */
	public function build_sql_donorlist_data($sql_donorlist_ary)
	{
		// Return all transactions entities
		return $this->db->sql_build_query('SELECT', $sql_donorlist_ary);
	}

	/**
	 * Returns total entries of selected field
	 *
	 * @param array  $count_sql_ary
	 * @param string $selected_field
	 *
	 * @return int
	 * @access public
	 */
	public function query_sql_count($count_sql_ary, $selected_field)
	{
		$count_sql_ary['SELECT'] = 'COUNT(' . $selected_field . ') AS total_entries';

		if (array_key_exists('GROUP_BY', $count_sql_ary))
		{
			$count_sql_ary['SELECT'] = 'COUNT(DISTINCT ' . $count_sql_ary['GROUP_BY'] . ') AS total_entries';
		}
		unset($count_sql_ary['ORDER_BY'], $count_sql_ary['GROUP_BY']);

		$sql = $this->db->sql_build_query('SELECT', $count_sql_ary);
		$this->db->sql_query($sql);

		return (int) $this->db->sql_fetchfield('total_entries');
	}

	/**
	 * Returns the SQL Query for displaying simple transactions details
	 *
	 * @param string  $keywords
	 * @param string  $sort_by
	 * @param integer $log_time
	 *
	 * @return array

	 * @access public
	 */
	public function get_logs_sql_ary($keywords, $sort_by, $log_time)
	{
		$sql_keywords = '';
		if (!empty($keywords))
		{
			// Get the SQL condition for our keywords
			$sql_keywords = $this->generate_sql_keyword($keywords);
		}

		$get_logs_sql_ary = array(
			'SELECT'   => 'txn.transaction_id, txn.txn_id, txn.confirmed, txn.payment_date, txn.payment_status, txn.user_id, u.username, u.user_colour',
			'FROM'     => array(
				$this->ppde_transactions_log_table => 'txn',
				USERS_TABLE                        => 'u',
			),
			'WHERE'    => 'txn.user_id = u.user_id ' . $sql_keywords,
			'ORDER_BY' => $sort_by,
		);

		if ($log_time)
		{
			$get_logs_sql_ary['WHERE'] = 'txn.payment_date >= ' . (int) $log_time . '
					AND ' . $get_logs_sql_ary['WHERE'];
		}

		return $get_logs_sql_ary;
	}

	/**
	 * Generates a sql condition for the specified keywords
	 *
	 * @param string $keywords           The keywords the user specified to search for
	 * @param string $statement_operator The operator used to prefix the statement ('AND' by default)
	 *
	 * @return string Returns the SQL condition searching for the keywords
	 * @access private
	 */
	private function generate_sql_keyword($keywords, $statement_operator = 'AND')
	{
		// Use no preg_quote for $keywords because this would lead to sole
		// backslashes being added. We also use an OR connection here for
		// spaces and the | string. Currently, regex is not supported for
		// searching (but may come later).
		$keywords = preg_split('#[\s|]+#u', utf8_strtolower($keywords), 0, PREG_SPLIT_NO_EMPTY);
		$sql_keywords = '';

		if (!empty($keywords))
		{
			$keywords_pattern = array();

			// Build pattern and keywords...
			for ($i = 0, $num_keywords = sizeof($keywords); $i < $num_keywords; $i++)
			{
				$keywords_pattern[] = preg_quote($keywords[$i], '#');
				$keywords[$i] = $this->db->sql_like_expression($this->db->get_any_char() . $keywords[$i] . $this->db->get_any_char());
			}

			$sql_keywords = ' ' . $statement_operator . ' (';
			$sql_lower = $this->db->sql_lower_text('txn.txn_id');
			$sql_keywords .= ' ' . $sql_lower . ' ' . implode(' OR ' . $sql_lower . ' ', $keywords) . ')';
		}

		return $sql_keywords;
	}

	/**
	 * Returns user information based on the ID of the donor or they email
	 *
	 * @param string $type
	 * @param int    $arg
	 *
	 * @return array|bool
	 * @access public
	 */
	public function query_donor_user_data($type = 'user', $arg = 1)
	{

		switch ($type)
		{
			case 'user':
				$sql_where = ' WHERE user_id = ' . (int) $arg;
				break;
			case 'email':
				$sql_where = ' WHERE user_email_hash = ' . crc32(strtolower($arg)) . strlen($arg);
				break;
			default:
				$sql_where = '';
		}

		$sql = 'SELECT user_id, username
			FROM ' . USERS_TABLE .
			$sql_where;
		$result = $this->db->sql_query($sql);

		return $this->db->sql_fetchrow($result);
	}

	/**
	 * Returns simple details of all PayPal transactions logged in the database
	 *
	 * @param array $get_logs_sql_ary
	 * @param array $url_ary
	 * @param int   $limit
	 * @param int   $last_page_offset
	 *
	 * @return array $log
	 * @access public
	 */
	public function build_log_ary($get_logs_sql_ary, $url_ary, $limit = 0, $last_page_offset = 0)
	{
		$sql = $this->db->sql_build_query('SELECT', $get_logs_sql_ary);
		$result = $this->db->sql_query_limit($sql, $limit, $last_page_offset);

		$i = 0;
		$log = array();

		while ($row = $this->db->sql_fetchrow($result))
		{
			$log[$i] = array(
				'transaction_id' => $row['transaction_id'],
				'txn_id'         => $this->build_transaction_url($row['transaction_id'], $row['txn_id'], $url_ary['txn_url'], $row['confirmed']),
				'confirmed'      => $row['confirmed'],
				'payment_status' => $row['payment_status'],
				'payment_date'   => $row['payment_date'],

				'username_full'  => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour'], false, $url_ary['profile_url']),
			);

			$i++;
		}

		return $log;
	}

	/**
	 * Build transaction url for placing into templates.
	 *
	 * @param int    $id         The users transaction id
	 * @param string $txn_id     The txn number id
	 * @param string $custom_url optional parameter to specify a profile url. The transaction id get appended to this
	 *                           url as &amp;id={id}
	 * @param bool   $colour
	 *
	 * @return string A string consisting of what is wanted.
	 * @access private
	 */
	private function build_transaction_url($id, $txn_id, $custom_url = '', $colour = false)
	{
		static $_profile_cache;

		// We cache some common variables we need within this function
		if (empty($_profile_cache))
		{
			$_profile_cache['tpl_nourl'] = '{TRANSACTION}';
			$_profile_cache['tpl_url'] = '<a href="{TXN_URL}">{TRANSACTION}</a>';
			$_profile_cache['tpl_url_colour'] = '<a href="{TXN_URL}" style="{TXN_COLOUR};">{TRANSACTION}</a>';
		}

		// Build correct transaction url
		$txn_url = '';
		if ($txn_id)
		{
			$txn_url = ($custom_url !== '') ? $custom_url . '&amp;action=view&amp;id=' . $id : $txn_id;
		}

		// Return

		if (!$txn_url)
		{
			return str_replace('{TRANSACTION}', $txn_id, $_profile_cache['tpl_nourl']);
		}

		return str_replace(array('{TXN_URL}', '{TXN_COLOUR}', '{TRANSACTION}'), array($txn_url, '#FF0000', $txn_id), (!$colour) ? $_profile_cache['tpl_url'] : $_profile_cache['tpl_url_colour']);
	}

	/**
	 * Returns SQL WHERE clause for all marked items
	 *
	 * @param $marked
	 *
	 * @return string
	 * @access public
	 */
	public function build_marked_where_sql($marked)
	{
		$sql_in = array();
		foreach ($marked as $mark)
		{
			$sql_in[] = $mark;
		}

		return ' WHERE ' . $this->db->sql_in_set('transaction_id', $sql_in);
	}
}


1			<?php
2			/**
3			*
4			* PayPal Donation extension for the phpBB Forum Software package.
5			*
6			* @copyright (c) 2015 Skouat
7			* @license GNU General Public License, version 2 (GPL-2.0)
8			*
9			*/
10
11			namespace skouat\ppde\operators;
12
13			use Symfony\Component\DependencyInjection\ContainerInterface;
14
15			class transactions
16			{
17			protected $container;
18			protected $db;
19			protected $ppde_transactions_log_table;
20
21			/**
22			* Constructor
23			*
24			* @param ContainerInterface $container Service container interface
25			* @param \phpbb\db\driver\driver_interface $db Database connection
26			* @param string $ppde_transactions_log_table Table name
27			*
28			* @access public
29			*/
30			public function __construct(ContainerInterface $container, \phpbb\db\driver\driver_interface $db, $ppde_transactions_log_table)
31			{
32			$this->container = $container;
33			$this->db = $db;
34			$this->ppde_transactions_log_table = $ppde_transactions_log_table;
35			}
36
37			/**
38			* SQL Query to return Transaction log data table
39			*
40			* @param $transaction_id
41			*
42			* @return string
43			* @access public
44			*/
45			public function build_sql_data($transaction_id = 0)
46			{
47			// Build main sql request
48			$sql_ary = array(
49			'SELECT' => '*, u.username',
50			'FROM' => array($this->ppde_transactions_log_table => 'txn'),
51			'LEFT_JOIN' => array(
52			array(
53			'FROM' => array(USERS_TABLE => 'u'),
54			'ON' => 'u.user_id = txn.user_id',
55			),
56			),
57			'ORDER_BY' => 'txn.transaction_id',
58			);
59
60			// Use WHERE clause when $currency_id is different from 0
61			if ((int) $transaction_id)
62			{
63			$sql_ary['WHERE'] = 'txn.transaction_id = ' . (int) $transaction_id;
64			}
65
66			// Return all transactions entities
67			return $this->db->sql_build_query('SELECT', $sql_ary);
68			}
69
70			/**
71			* Returns the SQL Query for generation the donors list
72			*
73			* @param int $max_txn_id Identifier of the transaction logged in the DB
74			* @param string $order_by
75			*
76			* @return array
77			* @access public
78			*/
79			public function get_sql_donorlist_ary($max_txn_id = 0, $order_by = '')
80			{
81			// Build main sql request
82			$donorlist_sql_ary = array(
83			'SELECT' => 'txn.*, MAX(txn.transaction_id) AS max_txn_id, SUM(txn.mc_gross) AS amount, u.username, u.user_colour',
84			'FROM' => array($this->ppde_transactions_log_table => 'txn'),
85			'LEFT_JOIN' => array(
86			array(
87			'FROM' => array(USERS_TABLE => 'u'),
88			'ON' => 'u.user_id = txn.user_id',
89			),
90			),
91			'WHERE' => 'txn.user_id <> ' . ANONYMOUS . "
92			AND txn.payment_status = 'Completed'",
93			'GROUP_BY' => 'txn.user_id',
94			'ORDER_BY' => 'txn.transaction_id DESC',
95			);
96
97			if ($order_by)
98			{
99			$donorlist_sql_ary['ORDER_BY'] = $order_by;
100			}
101
102			if ($max_txn_id)
103			{
104			$donorlist_sql_ary['WHERE'] = 'txn.transaction_id = ' . $max_txn_id;
105			unset($donorlist_sql_ary['GROUP_BY'], $donorlist_sql_ary['ORDER_BY']);
106			}
107
108			// Return all transactions entities
109			return $donorlist_sql_ary;
110			}
111
112			/**
113			* SQL Query to return donors list details
114			*
115			* @return string
116			* @access public
117			*/
118			public function build_sql_donorlist_data($sql_donorlist_ary)
119			{
120			// Return all transactions entities
121			return $this->db->sql_build_query('SELECT', $sql_donorlist_ary);
122			}
123
124			/**
125			* Returns total entries of selected field
126			*
127			* @param array $count_sql_ary
128			* @param string $selected_field
129			*
130			* @return int
131			* @access public
132			*/
133			public function query_sql_count($count_sql_ary, $selected_field)
134			{
135			$count_sql_ary['SELECT'] = 'COUNT(' . $selected_field . ') AS total_entries';
136
137			if (array_key_exists('GROUP_BY', $count_sql_ary))
138			{
139			$count_sql_ary['SELECT'] = 'COUNT(DISTINCT ' . $count_sql_ary['GROUP_BY'] . ') AS total_entries';
140			}
141			unset($count_sql_ary['ORDER_BY'], $count_sql_ary['GROUP_BY']);
142
143			$sql = $this->db->sql_build_query('SELECT', $count_sql_ary);
144			$this->db->sql_query($sql);
145
146			return (int) $this->db->sql_fetchfield('total_entries');
147			}
148
149			/**
150			* Returns the SQL Query for displaying simple transactions details
151			*
152			* @param string $keywords
153			* @param string $sort_by
154			* @param integer $log_time
155			*
156			* @return array
			1 ignored issue – show Documentation introduced 2016-01-23 20:45 UTC by Report Bug Copy Issue Report Consider making the return type a bit more specific; maybe use `array<string,string\|array<*,string>>`. This check looks for the generic type `array` as a return type and suggests a more specific type. This type is inferred from the actual code. Loading history...
157			* @access public
158			*/
159			public function get_logs_sql_ary($keywords, $sort_by, $log_time)
160			{
161			$sql_keywords = '';
162			if (!empty($keywords))
163			{
164			// Get the SQL condition for our keywords
165			$sql_keywords = $this->generate_sql_keyword($keywords);
166			}
167
168			$get_logs_sql_ary = array(
169			'SELECT' => 'txn.transaction_id, txn.txn_id, txn.confirmed, txn.payment_date, txn.payment_status, txn.user_id, u.username, u.user_colour',
170			'FROM' => array(
171			$this->ppde_transactions_log_table => 'txn',
172			USERS_TABLE => 'u',
173			),
174			'WHERE' => 'txn.user_id = u.user_id ' . $sql_keywords,
175			'ORDER_BY' => $sort_by,
176			);
177
178			if ($log_time)
179			{
180			$get_logs_sql_ary['WHERE'] = 'txn.payment_date >= ' . (int) $log_time . '
181			AND ' . $get_logs_sql_ary['WHERE'];
182			}
183
184			return $get_logs_sql_ary;
185			}
186
187			/**
188			* Generates a sql condition for the specified keywords
189			*
190			* @param string $keywords The keywords the user specified to search for
191			* @param string $statement_operator The operator used to prefix the statement ('AND' by default)
192			*
193			* @return string Returns the SQL condition searching for the keywords
194			* @access private
195			*/
196			private function generate_sql_keyword($keywords, $statement_operator = 'AND')
197			{
198			// Use no preg_quote for $keywords because this would lead to sole
199			// backslashes being added. We also use an OR connection here for
200			// spaces and the \| string. Currently, regex is not supported for
201			// searching (but may come later).
202			$keywords = preg_split('#[\s\|]+#u', utf8_strtolower($keywords), 0, PREG_SPLIT_NO_EMPTY);
203			$sql_keywords = '';
204
205			if (!empty($keywords))
206			{
207			$keywords_pattern = array();
208
209			// Build pattern and keywords...
210			for ($i = 0, $num_keywords = sizeof($keywords); $i < $num_keywords; $i++)
211			{
212			$keywords_pattern[] = preg_quote($keywords[$i], '#');
213			$keywords[$i] = $this->db->sql_like_expression($this->db->get_any_char() . $keywords[$i] . $this->db->get_any_char());
214			}
215
216			$sql_keywords = ' ' . $statement_operator . ' (';
217			$sql_lower = $this->db->sql_lower_text('txn.txn_id');
218			$sql_keywords .= ' ' . $sql_lower . ' ' . implode(' OR ' . $sql_lower . ' ', $keywords) . ')';
219			}
220
221			return $sql_keywords;
222			}
223
224			/**
225			* Returns user information based on the ID of the donor or they email
226			*
227			* @param string $type
228			* @param int $arg
229			*
230			* @return array\|bool
231			* @access public
232			*/
233			public function query_donor_user_data($type = 'user', $arg = 1)
234			{
235
236			switch ($type)
237			{
238			case 'user':
239			$sql_where = ' WHERE user_id = ' . (int) $arg;
240			break;
241			case 'email':
242			$sql_where = ' WHERE user_email_hash = ' . crc32(strtolower($arg)) . strlen($arg);
243			break;
244			default:
245			$sql_where = '';
246			}
247
248			$sql = 'SELECT user_id, username
249			FROM ' . USERS_TABLE .
250			$sql_where;
251			$result = $this->db->sql_query($sql);
252
253			return $this->db->sql_fetchrow($result);
254			}
255
256			/**
257			* Returns simple details of all PayPal transactions logged in the database
258			*
259			* @param array $get_logs_sql_ary
260			* @param array $url_ary
261			* @param int $limit
262			* @param int $last_page_offset
263			*
264			* @return array $log
265			* @access public
266			*/
267			public function build_log_ary($get_logs_sql_ary, $url_ary, $limit = 0, $last_page_offset = 0)
268			{
269			$sql = $this->db->sql_build_query('SELECT', $get_logs_sql_ary);
270			$result = $this->db->sql_query_limit($sql, $limit, $last_page_offset);
271
272			$i = 0;
273			$log = array();
274
275			while ($row = $this->db->sql_fetchrow($result))
276			{
277			$log[$i] = array(
278			'transaction_id' => $row['transaction_id'],
279			'txn_id' => $this->build_transaction_url($row['transaction_id'], $row['txn_id'], $url_ary['txn_url'], $row['confirmed']),
280			'confirmed' => $row['confirmed'],
281			'payment_status' => $row['payment_status'],
282			'payment_date' => $row['payment_date'],
283
284			'username_full' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour'], false, $url_ary['profile_url']),
285			);
286
287			$i++;
288			}
289
290			return $log;
291			}
292
293			/**
294			* Build transaction url for placing into templates.
295			*
296			* @param int $id The users transaction id
297			* @param string $txn_id The txn number id
298			* @param string $custom_url optional parameter to specify a profile url. The transaction id get appended to this
299			* url as &id={id}
300			* @param bool $colour
301			*
302			* @return string A string consisting of what is wanted.
303			* @access private
304			*/
305			private function build_transaction_url($id, $txn_id, $custom_url = '', $colour = false)
306			{
307			static $_profile_cache;
308
309			// We cache some common variables we need within this function
310			if (empty($_profile_cache))
311			{
312			$_profile_cache['tpl_nourl'] = '{TRANSACTION}';
313			$_profile_cache['tpl_url'] = '<a href="{TXN_URL}">{TRANSACTION}</a>';
314			$_profile_cache['tpl_url_colour'] = '<a href="{TXN_URL}" style="{TXN_COLOUR};">{TRANSACTION}</a>';
315			}
316
317			// Build correct transaction url
318			$txn_url = '';
319			if ($txn_id)
320			{
321			$txn_url = ($custom_url !== '') ? $custom_url . '&action=view&id=' . $id : $txn_id;
322			}
323
324			// Return
325
326			if (!$txn_url)
327			{
328			return str_replace('{TRANSACTION}', $txn_id, $_profile_cache['tpl_nourl']);
329			}
330
331			return str_replace(array('{TXN_URL}', '{TXN_COLOUR}', '{TRANSACTION}'), array($txn_url, '#FF0000', $txn_id), (!$colour) ? $_profile_cache['tpl_url'] : $_profile_cache['tpl_url_colour']);
332			}
333
334			/**
335			* Returns SQL WHERE clause for all marked items
336			*
337			* @param $marked
338			*
339			* @return string
340			* @access public
341			*/
342			public function build_marked_where_sql($marked)
343			{
344			$sql_in = array();
345			foreach ($marked as $mark)
346			{
347			$sql_in[] = $mark;
348			}
349
350			return ' WHERE ' . $this->db->sql_in_set('transaction_id', $sql_in);
351			}
352			}
353

Skouat / ext_paypal_donation

Pull Request — master (#23)

transactions::query_sql_count() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like