transactions::query_sql_count() - Code Metrics - Inspection of "[WIP] Module: Donors list" - Skouat/ext_paypal_donation - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — develop (#22)

by Mario

created 2015-12-29 21:43 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.4286
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 bool $anonymous
	 *
	 * @return array

	 * @access public
	 */
	public function get_sql_donorlist_ary($anonymous = false)
	{
		// Build main sql request
		$get_donorlist_sql_ary = array(
			'SELECT'    => '*, SUM(txn.mc_gross) AS amount, MAX(txn.payment_date) AS date, 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.payment_status = 'Completed'",
		);

		if (!$anonymous)
		{
			$get_donorlist_sql_ary['GROUP_BY'] = 'txn.user_id';
			$get_donorlist_sql_ary['ORDER_BY'] = 'txn.user_id';
			$get_donorlist_sql_ary['WHERE'] = 'txn.user_id <> ' . ANONYMOUS . ' AND ' . $get_donorlist_sql_ary['WHERE'];
		}

		// Return all transactions entities
		return $get_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 $keywords
	 * @param $sort_by
	 * @param $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 bool $anonymous
74			*
75			* @return array
			1 ignored issue – show Documentation introduced 2015-12-29 21:46 UTC by Report Bug Copy Issue Report Consider making the return type a bit more specific; maybe use `array<string,string\|arra...ay<*,string>\|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...
76			* @access public
77			*/
78			public function get_sql_donorlist_ary($anonymous = false)
79			{
80			// Build main sql request
81			$get_donorlist_sql_ary = array(
82			'SELECT' => '*, SUM(txn.mc_gross) AS amount, MAX(txn.payment_date) AS date, u.username, u.user_colour',
83			'FROM' => array($this->ppde_transactions_log_table => 'txn'),
84			'LEFT_JOIN' => array(
85			array(
86			'FROM' => array(USERS_TABLE => 'u'),
87			'ON' => 'u.user_id = txn.user_id',
88			),
89			),
90			'WHERE' => "txn.payment_status = 'Completed'",
91			);
92
93			if (!$anonymous)
94			{
95			$get_donorlist_sql_ary['GROUP_BY'] = 'txn.user_id';
96			$get_donorlist_sql_ary['ORDER_BY'] = 'txn.user_id';
97			$get_donorlist_sql_ary['WHERE'] = 'txn.user_id <> ' . ANONYMOUS . ' AND ' . $get_donorlist_sql_ary['WHERE'];
98			}
99
100			// Return all transactions entities
101			return $get_donorlist_sql_ary;
102			}
103
104			/**
105			* SQL Query to return donors list details
106			*
107			* @return string
108			* @access public
109			*/
110			public function build_sql_donorlist_data($sql_donorlist_ary)
111			{
112			// Return all transactions entities
113			return $this->db->sql_build_query('SELECT', $sql_donorlist_ary);
114			}
115
116			/**
117			* Returns total entries of selected field
118			*
119			* @param array $count_sql_ary
120			* @param string $selected_field
121			*
122			* @return int
123			* @access public
124			*/
125			public function query_sql_count($count_sql_ary, $selected_field)
126			{
127			$count_sql_ary['SELECT'] = 'COUNT(' . $selected_field . ') AS total_entries';
128
129			if (array_key_exists('GROUP_BY', $count_sql_ary))
130			{
131			$count_sql_ary['SELECT'] = 'COUNT(DISTINCT ' . $count_sql_ary['GROUP_BY'] . ') AS total_entries';
132			}
133			unset($count_sql_ary['ORDER_BY'], $count_sql_ary['GROUP_BY']);
134
135			$sql = $this->db->sql_build_query('SELECT', $count_sql_ary);
136			$this->db->sql_query($sql);
137
138			return (int) $this->db->sql_fetchfield('total_entries');
139			}
140
141			/**
142			* Returns the SQL Query for displaying simple transactions details
143			*
144			* @param $keywords
145			* @param $sort_by
146			* @param $log_time
147			*
148			* @return array
149			* @access public
150			*/
151			public function get_logs_sql_ary($keywords, $sort_by, $log_time)
152			{
153			$sql_keywords = '';
154			if (!empty($keywords))
155			{
156			// Get the SQL condition for our keywords
157			$sql_keywords = $this->generate_sql_keyword($keywords);
158			}
159
160			$get_logs_sql_ary = array(
161			'SELECT' => 'txn.transaction_id, txn.txn_id, txn.confirmed, txn.payment_date, txn.payment_status, txn.user_id, u.username, u.user_colour',
162			'FROM' => array(
163			$this->ppde_transactions_log_table => 'txn',
164			USERS_TABLE => 'u',
165			),
166			'WHERE' => 'txn.user_id = u.user_id ' . $sql_keywords,
167			'ORDER_BY' => $sort_by,
168			);
169
170			if ($log_time)
171			{
172			$get_logs_sql_ary['WHERE'] = 'txn.payment_date >= ' . (int) $log_time . '
173			AND ' . $get_logs_sql_ary['WHERE'];
174			}
175
176			return $get_logs_sql_ary;
177			}
178
179			/**
180			* Generates a sql condition for the specified keywords
181			*
182			* @param string $keywords The keywords the user specified to search for
183			* @param string $statement_operator The operator used to prefix the statement ('AND' by default)
184			*
185			* @return string Returns the SQL condition searching for the keywords
186			* @access private
187			*/
188			private function generate_sql_keyword($keywords, $statement_operator = 'AND')
189			{
190			// Use no preg_quote for $keywords because this would lead to sole
191			// backslashes being added. We also use an OR connection here for
192			// spaces and the \| string. Currently, regex is not supported for
193			// searching (but may come later).
194			$keywords = preg_split('#[\s\|]+#u', utf8_strtolower($keywords), 0, PREG_SPLIT_NO_EMPTY);
195			$sql_keywords = '';
196
197			if (!empty($keywords))
198			{
199			$keywords_pattern = array();
200
201			// Build pattern and keywords...
202			for ($i = 0, $num_keywords = sizeof($keywords); $i < $num_keywords; $i++)
203			{
204			$keywords_pattern[] = preg_quote($keywords[$i], '#');
205			$keywords[$i] = $this->db->sql_like_expression($this->db->get_any_char() . $keywords[$i] . $this->db->get_any_char());
206			}
207
208			$sql_keywords = ' ' . $statement_operator . ' (';
209			$sql_lower = $this->db->sql_lower_text('txn.txn_id');
210			$sql_keywords .= ' ' . $sql_lower . ' ' . implode(' OR ' . $sql_lower . ' ', $keywords) . ')';
211			}
212
213			return $sql_keywords;
214			}
215
216			/**
217			* Returns user information based on the ID of the donor or they email
218			*
219			* @param string $type
220			* @param int $arg
221			*
222			* @return array\|bool
223			* @access public
224			*/
225			public function query_donor_user_data($type = 'user', $arg = 1)
226			{
227
228			switch ($type)
229			{
230			case 'user':
231			$sql_where = ' WHERE user_id = ' . (int) $arg;
232			break;
233			case 'email':
234			$sql_where = ' WHERE user_email_hash = ' . crc32(strtolower($arg)) . strlen($arg);
235			break;
236			default:
237			$sql_where = '';
238			}
239
240			$sql = 'SELECT user_id, username
241			FROM ' . USERS_TABLE .
242			$sql_where;
243			$result = $this->db->sql_query($sql);
244
245			return $this->db->sql_fetchrow($result);
246			}
247
248			/**
249			* Returns simple details of all PayPal transactions logged in the database
250			*
251			* @param array $get_logs_sql_ary
252			* @param array $url_ary
253			* @param int $limit
254			* @param int $last_page_offset
255			*
256			* @return array $log
257			* @access public
258			*/
259			public function build_log_ary($get_logs_sql_ary, $url_ary, $limit = 0, $last_page_offset = 0)
260			{
261			$sql = $this->db->sql_build_query('SELECT', $get_logs_sql_ary);
262			$result = $this->db->sql_query_limit($sql, $limit, $last_page_offset);
263
264			$i = 0;
265			$log = array();
266
267			while ($row = $this->db->sql_fetchrow($result))
268			{
269			$log[$i] = array(
270			'transaction_id' => $row['transaction_id'],
271			'txn_id' => $this->build_transaction_url($row['transaction_id'], $row['txn_id'], $url_ary['txn_url'], $row['confirmed']),
272			'confirmed' => $row['confirmed'],
273			'payment_status' => $row['payment_status'],
274			'payment_date' => $row['payment_date'],
275
276			'username_full' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour'], false, $url_ary['profile_url']),
277			);
278
279			$i++;
280			}
281
282			return $log;
283			}
284
285			/**
286			* Build transaction url for placing into templates.
287			*
288			* @param int $id The users transaction id
289			* @param string $txn_id The txn number id
290			* @param string $custom_url optional parameter to specify a profile url. The transaction id get appended to this
291			* url as &id={id}
292			* @param bool $colour
293			*
294			* @return string A string consisting of what is wanted.
295			* @access private
296			*/
297			private function build_transaction_url($id, $txn_id, $custom_url = '', $colour = false)
298			{
299			static $_profile_cache;
300
301			// We cache some common variables we need within this function
302			if (empty($_profile_cache))
303			{
304			$_profile_cache['tpl_nourl'] = '{TRANSACTION}';
305			$_profile_cache['tpl_url'] = '<a href="{TXN_URL}">{TRANSACTION}</a>';
306			$_profile_cache['tpl_url_colour'] = '<a href="{TXN_URL}" style="{TXN_COLOUR};">{TRANSACTION}</a>';
307			}
308
309			// Build correct transaction url
310			$txn_url = '';
311			if ($txn_id)
312			{
313			$txn_url = ($custom_url !== '') ? $custom_url . '&action=view&id=' . $id : $txn_id;
314			}
315
316			// Return
317
318			if (!$txn_url)
319			{
320			return str_replace('{TRANSACTION}', $txn_id, $_profile_cache['tpl_nourl']);
321			}
322
323			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']);
324			}
325
326			/**
327			* Returns SQL WHERE clause for all marked items
328			*
329			* @param $marked
330			*
331			* @return string
332			* @access public
333			*/
334			public function build_marked_where_sql($marked)
335			{
336			$sql_in = array();
337			foreach ($marked as $mark)
338			{
339			$sql_in[] = $mark;
340			}
341
342			return ' WHERE ' . $this->db->sql_in_set('transaction_id', $sql_in);
343			}
344			}
345

Skouat / ext_paypal_donation

Pull Request — develop (#22)

transactions::query_sql_count() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like