-
Notifications
You must be signed in to change notification settings - Fork 0
/
DB.php
182 lines (145 loc) · 6.73 KB
/
DB.php
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
180
181
182
<?php
class DB {
private $link;
private $results;
function __construct() {
require(dirname(__FILE__) . '/config.php'); // this works
$this->link = mysql_connect('localhost', $dbusername, $dbpassword, true);
mysql_select_db($dbname, $this->link);
return;
}
function are_email_notifications_enabled($uid) {
$query = sprintf("SELECT disable_email_notifications FROM users WHERE id=%d", $uid);
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
return (!(bool)$row['disable_email_notifications']);
}
function award_exp($uid,$act,$exp_value) {
$query = sprintf("INSERT INTO experience (uid,act,value) VALUES (%d,'%s',%d)", $uid,mysql_real_escape_string($act),$exp_value);
mysql_query($query);
return;
}
function calculate_leaderboard() {
$query = sprintf("set @cnt :=0");
mysql_query($query);
$query = sprintf("select @cnt := @cnt+1 as rank,dt.* FROM (SELECT users.username,sum(experience.value) as score from experience,users where users.id=experience.uid group by uid order by score desc limit 50) as dt");
$res = mysql_query($query);
$ret = array();
while($row = mysql_fetch_assoc($res)) {
array_push($ret, $row);
}
return $ret;
}
function calculate_rank($uid) {
$scoreboard_query = sprintf("select count(distinct uid) as scoreboard_size from experience");
$scoreboard_res = mysql_query($scoreboard_query);
$scoreboard_row = mysql_fetch_assoc($scoreboard_res);
$leaderboard_creation_query = sprintf("create temporary table leaderboard (rank int not null, uid int not null, score int not null default 0) engine=memory");
mysql_query($leaderboard_creation_query);
$count_initialization_query = sprintf("set @cnt :=0");
mysql_query($count_initialization_query);
// $leaderboard_insertion_query = sprintf("insert into leaderboard(rank,uid,score) select @cnt := @cnt+1 as rank,uid,sum(value) as score from experience group by uid order by score desc");
$leaderboard_insertion_query = sprintf("insert into leaderboard(rank,uid,score) select @cnt := @cnt+1 as rank,dt.* FROM (SELECT experience.uid,sum(experience.value) as score from experience group by uid order by score desc limit 50) as dt");
$leaderboard_result = mysql_query($leaderboard_insertion_query);
$rank_query = sprintf("select rank from leaderboard where uid=%d", $uid);
$rank_result = mysql_query($rank_query);
$rank_row = mysql_fetch_assoc($rank_result);
$drop_leaderboard_query = sprintf("drop table leaderboard");
mysql_query($drop_leaderboard_query);
return array($rank_row['rank'],$scoreboard_row['scoreboard_size']);
}
function calculate_score($uid) {
$query = sprintf("SELECT sum(value) AS score FROM experience WHERE uid=%d", $uid);
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
return $row['score'];
}
function create_user($email, $pw, $username, $source) {
$query = sprintf("INSERT INTO users (email,pw,username,source,last_login) VALUES ('%s','%s','%s','%s',now())", mysql_real_escape_string($email), md5($pw), mysql_real_escape_string($username), mysql_real_escape_string($source));
mysql_query($query);
return mysql_insert_id();
}
function dashboard_get_registration_count() {
$query = "select count(*) as registration_count,date_format(reg_date,'%M %e %Y') as registration_date from users group by registration_date";
$res = mysql_query($query);
$ret = array();
while($row = mysql_fetch_assoc($res)) {
array_push($ret,$row);
}
return $ret;
}
function get_email_address($user) {
$query = sprintf("SELECT email FROM users WHERE id=%d", $user);
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
return $row['email'];
}
function get_username($user) {
$query = sprintf("SELECT username FROM users WHERE id=%d", $user);
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
return $row['username'];
}
function get_notifications($uid) {
$query = sprintf("SELECT message,add_date FROM notification_queue WHERE uid=%d AND is_read=0 ORDER BY add_date desc", $uid);
$res = mysql_query($query);
$ret = array();
while($row = mysql_fetch_assoc($res)) {
array_push($ret, array('message'=>$row['message'], 'date'=>$row['add_date']));
}
$this->mark_notifications_read($uid);
return $ret;
}
function get_notification_count($uid) {
$query = sprintf("SELECT COUNT(*) as notification_count FROM notification_queue WHERE is_read=0 AND uid=%d", $uid);
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
return $row['notification_count'];
}
function get_ant_for_review($uid) {
$query = sprintf("SELECT ant,event,u_distortions,u_uid FROM ant_queue WHERE u_uid!=%d AND r_uid!=%d ORDER BY review_count ASC LIMIT 1", $uid, $uid);
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
return $row;
}
function insert_notification($uid,$message) {
$query = sprintf("INSERT INTO notification_queue (uid,message) VALUES (%d,'%s')", $uid, mysql_real_escape_string($message));
mysql_query($query);
return;
}
function login_user($email, $pw) {
$query = sprintf("SELECT id,username FROM users WHERE (email='%s' AND pw='%s') OR (username='%s' AND pw='%s')", mysql_real_escape_string($email),md5($pw), mysql_real_escape_string($email),md5($pw));
$res = mysql_query($query);
if(mysql_num_rows($res)>0) {
$query = sprintf("UPDATE users SET last_login=now() WHERE email='%s'", mysql_real_escape_string($email));
mysql_query($query);
$row = mysql_fetch_assoc($res);
return array('uid' => $row['id'], 'username' => $row['username']);
}
else {
return false;
}
}
private function mark_notifications_read($uid) {
$query = sprintf("UPDATE notification_queue SET is_read=1,read_date=now() WHERE is_read=0 AND uid=%d", $uid);
$res = mysql_query($query);
return;
}
function save_ant($uid,$ant,$event,$distortions) {
$query = sprintf("INSERT INTO ant_queue (u_uid,ant,event,u_distortions) VALUES (%d,'%s','%s','%s')", $uid, mysql_real_escape_string($ant),mysql_real_escape_string($event),mysql_real_escape_string($distortions));
mysql_query($query);
return;
}
function update_ant($u_uid, $ant,$event,$u_distortions, $r_uid, $r_distortions, $r_comments) {
$query = sprintf("UPDATE ant_queue SET r_uid=%d,r_distortions='%s',r_comments='%s',review_count=review_count+1 WHERE u_uid=%d AND event='%s' AND u_distortions='%s'", $r_uid,mysql_real_escape_string($r_distortions),mysql_real_escape_string($r_comments),$u_uid,mysql_real_escape_string($event),mysql_real_escape_string($u_distortions));
mysql_query($query);
return;
}
function update_email_settings($user, $disable_emails) {
$query = sprintf("UPDATE users SET disable_email_notifications=%d WHERE id=%d", $disable_emails, $user);
echo $query;
mysql_query($query);
return;
}
}
?>