1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
<?php
/*
Question2Answer by Gideon Greenspan and contributors
http://www.question2answer.org/
Description: Database-level access to votes tables
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
*/
if (!defined('QA_VERSION')) { // don't allow this page to be requested directly from browser
header('Location: ../../');
exit;
}
/**
* Set the vote for $userid on $postid to $vote in the database
* @param $postid
* @param $userid
* @param $vote
*/
function qa_db_uservote_set($postid, $userid, $vote)
{
$vote = max(min(($vote), 1), -1);
qa_db_query_sub(
'INSERT INTO ^uservotes (postid, userid, vote, flag, votecreated) VALUES (#, #, #, 0, NOW()) ON DUPLICATE KEY UPDATE vote=#, voteupdated=NOW()',
$postid, $userid, $vote, $vote
);
}
/**
* Get the vote for $userid on $postid from the database (or NULL if none)
* @param $postid
* @param $userid
* @return mixed|null
*/
function qa_db_uservote_get($postid, $userid)
{
return qa_db_read_one_value(qa_db_query_sub(
'SELECT vote FROM ^uservotes WHERE postid=# AND userid=#',
$postid, $userid
), true);
}
/**
* Set the flag for $userid on $postid to $flag (true or false) in the database
* @param $postid
* @param $userid
* @param $flag
*/
function qa_db_userflag_set($postid, $userid, $flag)
{
$flag = $flag ? 1 : 0;
qa_db_query_sub(
'INSERT INTO ^uservotes (postid, userid, vote, flag) VALUES (#, #, 0, #) ON DUPLICATE KEY UPDATE flag=#',
$postid, $userid, $flag, $flag
);
}
/**
* Clear all flags for $postid in the database
* @param $postid
*/
function qa_db_userflags_clear_all($postid)
{
qa_db_query_sub(
'UPDATE ^uservotes SET flag=0 WHERE postid=#',
$postid
);
}
/**
* Recalculate the cached count of upvotes, downvotes and netvotes for $postid in the database
* @param $postid
*/
function qa_db_post_recount_votes($postid)
{
if (qa_should_update_counts()) {
qa_db_query_sub(
'UPDATE ^posts AS x, (SELECT COALESCE(SUM(GREATEST(0,vote)),0) AS upvotes, -COALESCE(SUM(LEAST(0,vote)),0) AS downvotes FROM ^uservotes WHERE postid=#) AS a SET x.upvotes=a.upvotes, x.downvotes=a.downvotes, x.netvotes=a.upvotes-a.downvotes WHERE x.postid=#',
$postid, $postid
);
}
}
/**
* Recalculate the cached count of flags for $postid in the database
* @param $postid
*/
function qa_db_post_recount_flags($postid)
{
if (qa_should_update_counts()) {
qa_db_query_sub(
'UPDATE ^posts AS x, (SELECT COALESCE(SUM(IF(flag, 1, 0)),0) AS flagcount FROM ^uservotes WHERE postid=#) AS a SET x.flagcount=a.flagcount WHERE x.postid=#',
$postid, $postid
);
}
}
/**
* Returns all non-zero votes on post $postid from the database as an array of [userid] => [vote]
* @param $postid
* @return array
*/
function qa_db_uservote_post_get($postid)
{
return qa_db_read_all_assoc(qa_db_query_sub(
'SELECT userid, vote FROM ^uservotes WHERE postid=# AND vote!=0',
$postid
), 'userid', 'vote');
}
/**
* Returns all the postids from the database for posts that $userid has voted on or flagged
* @param $userid
* @return array
*/
function qa_db_uservoteflag_user_get($userid)
{
return qa_db_read_all_values(qa_db_query_sub(
'SELECT postid FROM ^uservotes WHERE userid=# AND (vote!=0 OR flag!=0)',
$userid
));
}
/**
* Return information about all the non-zero votes and/or flags on the posts in postids, including user handles for internal user management
* @param $postids
* @return array
*/
function qa_db_uservoteflag_posts_get($postids)
{
if (QA_FINAL_EXTERNAL_USERS) {
return qa_db_read_all_assoc(qa_db_query_sub(
'SELECT postid, userid, vote, flag, votecreated, voteupdated FROM ^uservotes WHERE postid IN (#) AND (vote!=0 OR flag!=0)',
$postids
));
} else {
return qa_db_read_all_assoc(qa_db_query_sub(
'SELECT postid, handle, vote, flag, votecreated, voteupdated FROM ^uservotes LEFT JOIN ^users ON ^uservotes.userid=^users.userid WHERE postid IN (#) AND (vote!=0 OR flag!=0)',
$postids
));
}
}
/**
* Remove all votes assigned to a post that had been cast by the owner of the post.
*
* @param int $postid The post ID from which the owner's votes will be removed.
*/
function qa_db_uservote_remove_own($postid)
{
qa_db_query_sub(
'DELETE uv FROM ^uservotes uv JOIN ^posts p ON uv.postid=p.postid AND uv.userid=p.userid WHERE uv.postid=#', $postid
);
}