points.php 9.58 KB
Newer Older
Scott committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
<?php
/*
	Question2Answer by Gideon Greenspan and contributors
	http://www.question2answer.org/

	Description: Database-level access to user points and statistics


	This program is free software; you can redistribute it and/or
	modify it under the terms of the GNU General Public License
	as published by the Free Software Foundation; either version 2
	of the License, or (at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	More about this license: http://www.question2answer.org/license.php
*/

Scott committed
22
if (!defined('QA_VERSION')) { // don't allow this page to be requested directly from browser
23
	header('Location: ../../');
Scott committed
24 25 26 27 28 29
	exit;
}


/**
 * Returns an array of option names required to perform calculations in userpoints table
30
 * @return mixed
Scott committed
31 32 33 34 35 36 37 38
 */
function qa_db_points_option_names()
{
	if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

	return array(
		'points_post_q', 'points_select_a', 'points_per_q_voted_up', 'points_per_q_voted_down', 'points_q_voted_max_gain', 'points_q_voted_max_loss',
		'points_post_a', 'points_a_selected', 'points_per_a_voted_up', 'points_per_a_voted_down', 'points_a_voted_max_gain', 'points_a_voted_max_loss',
Scott committed
39
		'points_per_c_voted_up', 'points_per_c_voted_down', 'points_c_voted_max_gain', 'points_c_voted_max_loss',
Scott committed
40 41 42 43 44 45 46 47 48 49 50 51 52
		'points_vote_up_q', 'points_vote_down_q', 'points_vote_up_a', 'points_vote_down_a',

		'points_multiple', 'points_base',
	);
}


/**
 * Returns an array containing all the calculation formulae for the userpoints table. Each element of this
 * array is for one column - the key contains the column name, and the value is a further array of two elements.
 * The element 'formula' contains the SQL fragment that calculates the columns value for one or more users,
 * where the ~ symbol within the fragment is substituted for a constraint on which users we are interested in.
 * The element 'multiple' specifies what to multiply each column by to create the final sum in the points column.
53
 * @return mixed
Scott committed
54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
 */
function qa_db_points_calculations()
{
	if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

	require_once QA_INCLUDE_DIR . 'app/options.php';

	$options = qa_get_options(qa_db_points_option_names());

	return array(
		'qposts' => array(
			'multiple' => $options['points_multiple'] * $options['points_post_q'],
			'formula' => "COUNT(*) AS qposts FROM ^posts AS userid_src WHERE userid~ AND type='Q'",
		),

		'aposts' => array(
			'multiple' => $options['points_multiple'] * $options['points_post_a'],
			'formula' => "COUNT(*) AS aposts FROM ^posts AS userid_src WHERE userid~ AND type='A'",
		),

		'cposts' => array(
			'multiple' => 0,
			'formula' => "COUNT(*) AS cposts FROM ^posts AS userid_src WHERE userid~ AND type='C'",
		),

		'aselects' => array(
			'multiple' => $options['points_multiple'] * $options['points_select_a'],
			'formula' => "COUNT(*) AS aselects FROM ^posts AS userid_src WHERE userid~ AND type='Q' AND selchildid IS NOT NULL",
		),

		'aselecteds' => array(
			'multiple' => $options['points_multiple'] * $options['points_a_selected'],
			'formula' => "COUNT(*) AS aselecteds FROM ^posts AS userid_src JOIN ^posts AS questions ON questions.selchildid=userid_src.postid WHERE userid_src.userid~ AND userid_src.type='A' AND NOT (questions.userid<=>userid_src.userid)",
		),

		'qupvotes' => array(
			'multiple' => $options['points_multiple'] * $options['points_vote_up_q'],
			'formula' => "COUNT(*) AS qupvotes FROM ^uservotes AS userid_src JOIN ^posts ON userid_src.postid=^posts.postid WHERE userid_src.userid~ AND LEFT(^posts.type, 1)='Q' AND userid_src.vote>0",
		),

		'qdownvotes' => array(
			'multiple' => $options['points_multiple'] * $options['points_vote_down_q'],
			'formula' => "COUNT(*) AS qdownvotes FROM ^uservotes AS userid_src JOIN ^posts ON userid_src.postid=^posts.postid WHERE userid_src.userid~ AND LEFT(^posts.type, 1)='Q' AND userid_src.vote<0",
		),

		'aupvotes' => array(
			'multiple' => $options['points_multiple'] * $options['points_vote_up_a'],
			'formula' => "COUNT(*) AS aupvotes FROM ^uservotes AS userid_src JOIN ^posts ON userid_src.postid=^posts.postid WHERE userid_src.userid~ AND LEFT(^posts.type, 1)='A' AND userid_src.vote>0",
		),

		'adownvotes' => array(
			'multiple' => $options['points_multiple'] * $options['points_vote_down_a'],
			'formula' => "COUNT(*) AS adownvotes FROM ^uservotes AS userid_src JOIN ^posts ON userid_src.postid=^posts.postid WHERE userid_src.userid~ AND LEFT(^posts.type, 1)='A' AND userid_src.vote<0",
		),

Scott committed
109 110 111 112 113 114 115 116 117 118
		'cupvotes' => array(
			'multiple' => 0,
			'formula' => "COUNT(*) AS cupvotes FROM ^uservotes AS userid_src JOIN ^posts ON userid_src.postid=^posts.postid WHERE userid_src.userid~ AND LEFT(^posts.type, 1)='C' AND userid_src.vote>0",
		),

		'cdownvotes' => array(
			'multiple' => 0,
			'formula' => "COUNT(*) AS cdownvotes FROM ^uservotes AS userid_src JOIN ^posts ON userid_src.postid=^posts.postid WHERE userid_src.userid~ AND LEFT(^posts.type, 1)='C' AND userid_src.vote<0",
		),

Scott committed
119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
		'qvoteds' => array(
			'multiple' => $options['points_multiple'],
			'formula' => "COALESCE(SUM(" .
				"LEAST(" . ((int)$options['points_per_q_voted_up']) . "*upvotes," . ((int)$options['points_q_voted_max_gain']) . ")" .
				"-" .
				"LEAST(" . ((int)$options['points_per_q_voted_down']) . "*downvotes," . ((int)$options['points_q_voted_max_loss']) . ")" .
				"), 0) AS qvoteds FROM ^posts AS userid_src WHERE LEFT(type, 1)='Q' AND userid~",
		),

		'avoteds' => array(
			'multiple' => $options['points_multiple'],
			'formula' => "COALESCE(SUM(" .
				"LEAST(" . ((int)$options['points_per_a_voted_up']) . "*upvotes," . ((int)$options['points_a_voted_max_gain']) . ")" .
				"-" .
				"LEAST(" . ((int)$options['points_per_a_voted_down']) . "*downvotes," . ((int)$options['points_a_voted_max_loss']) . ")" .
				"), 0) AS avoteds FROM ^posts AS userid_src WHERE LEFT(type, 1)='A' AND userid~",
		),

Scott committed
137 138 139 140 141 142 143 144 145
		'cvoteds' => array(
			'multiple' => $options['points_multiple'],
			'formula' => "COALESCE(SUM(" .
				"LEAST(" . ((int)$options['points_per_c_voted_up']) . "*upvotes," . ((int)$options['points_c_voted_max_gain']) . ")" .
				"-" .
				"LEAST(" . ((int)$options['points_per_c_voted_down']) . "*downvotes," . ((int)$options['points_c_voted_max_loss']) . ")" .
				"), 0) AS cvoteds FROM ^posts AS userid_src WHERE LEFT(type, 1)='C' AND userid~",
		),

Scott committed
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160
		'upvoteds' => array(
			'multiple' => 0,
			'formula' => "COALESCE(SUM(upvotes), 0) AS upvoteds FROM ^posts AS userid_src WHERE userid~",
		),

		'downvoteds' => array(
			'multiple' => 0,
			'formula' => "COALESCE(SUM(downvotes), 0) AS downvoteds FROM ^posts AS userid_src WHERE userid~",
		),
	);
}


/**
 * Update the userpoints table in the database for $userid and $columns, plus the summary points column.
161
 * Set $columns to true for all, false for none, an array for several, or a single value for one.
Scott committed
162
 * This dynamically builds some fairly crazy looking SQL, but it works, and saves repeat calculations.
163 164
 * @param mixed $userid
 * @param bool|string|array $columns
Scott committed
165 166 167 168 169 170 171
 * @return mixed
 */
function qa_db_points_update_ifuser($userid, $columns)
{
	if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

	if (qa_should_update_counts() && isset($userid)) {
Scott committed
172 173
		require_once QA_INCLUDE_DIR . 'app/options.php';
		require_once QA_INCLUDE_DIR . 'app/cookies.php';
Scott committed
174

Scott committed
175
		$calculations = qa_db_points_calculations();
Scott committed
176

177
		if ($columns === true) {
Scott committed
178
			$keycolumns = $calculations;
179
		} elseif (empty($columns)) {
Scott committed
180
			$keycolumns = array();
181
		} elseif (is_array($columns)) {
Scott committed
182
			$keycolumns = array_flip($columns);
183
		} else {
Scott committed
184
			$keycolumns = array($columns => true);
185
		}
Scott committed
186

Scott committed
187 188 189
		$insertfields = 'userid, ';
		$insertvalues = '$, ';
		$insertpoints = (int)qa_opt('points_base');
Scott committed
190

Scott committed
191 192
		$updates = '';
		$updatepoints = $insertpoints;
Scott committed
193 194

		foreach ($calculations as $field => $calculation) {
Scott committed
195
			$multiple = (int)$calculation['multiple'];
Scott committed
196 197

			if (isset($keycolumns[$field])) {
Scott committed
198 199 200 201
				$insertfields .= $field . ', ';
				$insertvalues .= '@_' . $field . ':=(SELECT ' . $calculation['formula'] . '), ';
				$updates .= $field . '=@_' . $field . ', ';
				$insertpoints .= '+(' . (int)$multiple . '*@_' . $field . ')';
Scott committed
202 203
			}

Scott committed
204
			$updatepoints .= '+(' . $multiple . '*' . (isset($keycolumns[$field]) ? '@_' : '') . $field . ')';
Scott committed
205 206
		}

Scott committed
207 208
		$query = 'INSERT INTO ^userpoints (' . $insertfields . 'points) VALUES (' . $insertvalues . $insertpoints . ') ' .
			'ON DUPLICATE KEY UPDATE ' . $updates . 'points=' . $updatepoints . '+bonus';
Scott committed
209

Scott committed
210
		// build like this so that a #, $ or ^ character in the $userid (if external integration) isn't substituted
211
		$result = qa_db_query_raw(str_replace('~', "='" . qa_db_escape_string($userid) . "'", qa_db_apply_sub($query, array($userid))));
Scott committed
212

213
		if ($result->affectedRows() > 0) {
Scott committed
214
			qa_db_userpointscount_update();
215
		}
Scott committed
216 217 218 219 220 221
	}
}


/**
 * Set the number of explicit bonus points for $userid to $bonus
222 223
 * @param mixed $userid
 * @param int $bonus
Scott committed
224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239
 */
function qa_db_points_set_bonus($userid, $bonus)
{
	qa_db_query_sub(
		"INSERT INTO ^userpoints (userid, bonus) VALUES ($, #) ON DUPLICATE KEY UPDATE bonus=#",
		$userid, $bonus, $bonus
	);
}


/**
 * Update the cached count in the database of the number of rows in the userpoints table
 */
function qa_db_userpointscount_update()
{
	if (qa_should_update_counts()) {
Scott committed
240
		qa_db_query_sub(
Scott committed
241 242 243
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_userpointscount', COUNT(*) FROM ^userpoints " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
Scott committed
244 245
		);
	}
Scott committed
246
}