bug 1391: fixed syntax in sql statement
[cacert-mgr.git] / manager / application / models / User.php
1 <?php
2 /**
3 * @author Michael Tänzer
4 */
5
6 class Default_Model_User {
7 protected $db;
8
9 protected $id;
10 protected $points = null;
11
12 protected function __construct(Zend_Db_Adapter_Abstract $db, $id) {
13 // Not allowed to create new users from within the manager
14
15 $this->db = $db;
16 $this->id = $id;
17 }
18
19 /**
20 * Get an user object for the given ID
21 *
22 * @param $id int
23 * @return Default_Model_User
24 */
25 public static function findById($id) {
26 // Get database connection
27 $config = new Zend_Config_Ini(
28 APPLICATION_PATH . '/configs/application.ini',
29 APPLICATION_ENV);
30 $db = Zend_Db::factory($config->ca_mgr->db->auth->pdo,
31 $config->ca_mgr->db->auth);
32
33 // Check if the ID is present on the test server
34 $query = 'select `id` from `users` where `id` = :user';
35 $query_params['user'] = $id;
36 $result = $db->query($query, $query_params);
37 if ($result->rowCount() !== 1) {
38 throw new Exception(
39 __METHOD__ . ': user ID not found in the data base');
40 }
41 $row = $result->fetch();
42
43 return new Default_Model_User($db, $row['id']);
44 }
45
46 /**
47 * Get an user object for the currently logged in user
48 *
49 * @return Default_Model_User
50 */
51 public static function findCurrentUser() {
52 $session = Zend_Registry::get('session');
53 if ($session->authdata['authed'] !== true) {
54 throw new Exception(
55 __METHOD__ . ': you need to log in to use this feature');
56 }
57
58 return self::findById($session->authdata['authed_id']);
59 }
60
61 /**
62 * Get the first assurer who didn't already assure the user
63 *
64 * @return Default_Model_User
65 */
66 public function findNewAssurer()
67 {
68 $query = 'select min(`id`) as `assurer` from `users` ' .
69 'where `email` like \'john.doe-___@example.com\' and ' .
70 '`id` not in (select `from` from `notary` where `to` = :user)';
71 $query_params['user'] = $this->id;
72 $row = $this->db->query($query, $query_params)->fetch();
73
74 if ($row['assurer'] === NULL) {
75 throw new Exception(
76 __METHOD__ . ': no more assurers that haven\'t already '.
77 'assured this account');
78 }
79
80 return new Default_Model_User($this->db, $row['assurer']);
81 }
82
83 /**
84 * Get the first assuree who hasn't already been assured by this user
85 *
86 * @return Default_Model_User
87 */
88 public function findNewAssuree() {
89 $query = 'select min(`id`) as `assuree` from `users` ' .
90 'where `email` like \'john.doe-___@example.com\' and ' .
91 '`id` not in (select `to` from `notary` where `from` = :user)';
92 $query_params['user'] = $this->id;
93 $row = $this->db->query($query, $query_params)->fetch();
94
95 if ($row['assuree'] === NULL) {
96 throw new Exception(
97 __METHOD__ . ': no more assurees that haven\'t already '.
98 'been assured by this account');
99 }
100
101 return new Default_Model_User($this->db, $row['assuree']);
102 }
103
104 /**
105 * Refresh the current value of points from the test server
106 *
107 * Needed if operations outside this class are made, that might affect the
108 * user's points
109 */
110 public function refreshPoints() {
111 $query = "SELECT SUM(`points`) AS `total` FROM `notary` " .
112 "WHERE `to` = :user AND `method` != 'Administrative Increase' AND `from` != `to`";
113 $query_params['user'] = $this->id;
114 $row = $this->db->query($query, $query_params)->fetch();
115 if ($row['total'] === null) $row['total'] = 0;
116
117 $this->points = $row['total'];
118
119 if($this->points < 100) return;
120
121 $this->points = 100;
122
123 $query = "SELECT COUNT(`points`) AS `total` FROM `notary` " .
124 "WHERE `from` = :user AND `method` = 'Face to Face Meeting' AND `from` != `to`";
125 $query_params['user'] = $this->id;
126 $row = $this->db->query($query, $query_params)->fetch();
127 if ($row['total'] === null) $row['total'] = 0;
128
129 $this->points += ($row['total'] > 25) ? 50 : $row['total'];
130
131 }
132
133 /**
134 * Get points of the user
135 *
136 * @return int
137 * The amount of points the user has
138 */
139 public function getPoints()
140 {
141 if ($this->points === null) {
142 $this->refreshPoints();
143 }
144
145 return $this->points;
146 }
147
148 /**
149 * Fix the assurer flag for the user
150 */
151 public function fixAssurerFlag()
152 {
153 // TODO: unset flag if requirements are not met
154
155 $query = 'UPDATE `users` SET `assurer` = 1 WHERE `users`.`id` = :user AND '.
156
157 'EXISTS(SELECT * FROM `cats_passed` AS `cp`, `cats_variant` AS `cv` '.
158 'WHERE `cp`.`variant_id` = `cv`.`id` AND `cv`.`type_id` = 1 AND '.
159 '`cp`.`user_id` = :user) AND '.
160
161 '(SELECT SUM(`points`) FROM `notary` WHERE `to` = :user AND '.
162 '`expire` < now()) >= 100';
163 $query_params['user'] = $this->id;
164 $this->db->query($query, $query_params);
165 }
166
167 /**
168 * @return boolean
169 */
170 public function getAssurerStatus() {
171 $query = 'SELECT 1 FROM `users` WHERE `users`.`id` = :user AND '.
172 '`assurer_blocked` = 0 AND '.
173
174 'EXISTS(SELECT * FROM `cats_passed` AS `cp`, `cats_variant` AS `cv` '.
175 'WHERE `cp`.`variant_id` = `cv`.`id` AND `cv`.`type_id` = 1 AND '.
176 '`cp`.`user_id` = :user) AND '.
177
178 '(SELECT SUM(`points`) FROM `notary` WHERE `to` = :user AND '.
179 '`expire` < now()) >= 100';
180 $query_params['user'] = $this->id;
181 $result = $this->db->query($query, $query_params);
182 if ($result->rowCount() === 1) {
183 return true;
184 }
185
186 return false;
187 }
188
189 /**
190 * @return Zend_Date
191 */
192 public function getDob() {
193 $query = 'select `dob` from `users` where `id` = :user';
194 $query_params['user'] = $this->id;
195 $row = $this->db->query($query, $query_params)->fetch();
196
197 return new Zend_Date($row['dob'], Zend_Date::ISO_8601);
198 }
199
200 /**
201 * @return int
202 */
203 public function getAge() {
204 $now = new Zend_Date();
205 $dob = $this->getDob();
206 $age = $now->get(Zend_Date::YEAR) - $dob->get(Zend_Date::YEAR);
207
208 // Did we have a happy birthday already this year?
209 $dob->setYear($now);
210 if ($dob->compare($now) > 0) {
211 $age -= 1;
212 }
213
214 return $age;
215 }
216
217 /**
218 * @return string
219 */
220 public function getPrimEmail() {
221 $query = 'select `email` from `users` where `id` = :user';
222 $query_params['user'] = $this->id;
223 $row = $this->db->query($query, $query_params)->fetch();
224
225 return $row['email'];
226 }
227
228 /**
229 * Assure another user. Usual restrictions apply
230 *
231 * @param $assuree Default_Model_User
232 * @param $points int
233 * @param $location string
234 * @param $date string
235 * @throws Exception
236 *
237 * @return int
238 * The amount of points that have been issued (might be less than
239 * $points)
240 */
241 public function assure(Default_Model_User $assuree, $points, $location, $date) {
242 // Sanitize inputs
243 $points = intval($points);
244 $location = stripslashes($location);
245 $date = stripslashes($date);
246
247 if (!$this->getAssurerStatus()) {
248 throw new Exception(
249 __METHOD__ . ': '.$this->id.' needs to be an assurer to do '.
250 'assurances');
251 }
252
253 if ($this->id === $assuree->id) {
254 throw new Exception(
255 __METHOD__ . ': '.$this->id.' is not allowed to assure '.
256 'himself');
257 }
258
259 $query = 'select * from `notary` where `from`= :assurer and '.
260 '`to`= :assuree';
261 $query_params['assurer'] = $this->id;
262 $query_params['assuree'] = $assuree->id;
263 $result = $this->db->query($query, $query_params);
264 if ($result->rowCount() > 0 && $this->getPoints() < 200) {
265 throw new Exception(
266 __METHOD__ . ': '.$this->id.' is not allowed to assure '.
267 $assuree->id .' more than once');
268 }
269
270 // Respect the maximum points
271 $max = $this->maxpoints();
272 $points = min($points, $max);
273
274 $rounddown = $points;
275 if ($max < 100) {
276 if ($assuree->getPoints() + $points > 100)
277 $rounddown = 100 - $assuree->getPoints();
278 } else {
279 if ($assuree->getPoints() + $points > $max)
280 $rounddown = $max - $assuree->getPoints();
281 }
282 if ($rounddown < 0) $rounddown = 0;
283
284 $query = 'select * from `notary` where `from` = :assurer and '.
285 '`to` = :assuree and `awarded` = :points and '.
286 '`location` = :location and `date` = :date';
287 $query_params['assurer'] = $this->id;
288 $query_params['assuree'] = $assuree->id;
289 $query_params['points'] = $points;
290 $query_params['location'] = $location;
291 $query_params['date'] = $date;
292 $result = $this->db->query($query, $query_params);
293 if ($result->rowCount() > 0) {
294 throw new Exception(
295 __METHOD__ . ': '.$this->id.' is not allowed to do the same '.
296 'assurance to '.$assuree->id.' more than once');
297 }
298
299 // Make sure it is empty
300 $assurance = array();
301 $assurance['from'] = $this->id;
302 $assurance['to'] = $assuree->id;
303 $assurance['points'] = $rounddown;
304 $assurance['awarded'] = $points;
305 $assurance['location'] = $location;
306 $assurance['date'] = $date;
307 $assurance['when'] = new Zend_Db_Expr('now()');
308
309 $this->db->insert('notary', $assurance);
310 $assuree->points += $rounddown;
311 $assuree->fixAssurerFlag();
312 return $rounddown;
313 }
314
315 /**
316 * Do an administrative increase
317 *
318 * @param $points int
319 * @param $location string
320 * @param $date string
321 */
322 public function adminIncrease($points, $location, $date) {
323 //Sanitize inputs
324 $points = intval($points);
325 $location = stripslashes($location);
326 $date = stripslashes($date);
327
328 $increase = array();
329 $increase['from'] = $this->id;
330 $increase['to'] = $this->id;
331 $increase['points'] = $points;
332 $increase['awarded'] = $points;
333 $increase['location'] = $location;
334 $increase['date'] = $date;
335 $increase['method'] = 'Administrative Increase';
336 $increase['when'] = new Zend_Db_Expr('now()');
337
338 $this->db->insert('notary', $increase);
339 $this->points += $points;
340
341 $this->fixAssurerFlag();
342 }
343
344 /**
345 * Maximum number of points the user may issue
346 *
347 * @return int
348 */
349 public function maxpoints() {
350 if (!$this->getAssurerStatus()) return 0;
351
352 if ($this->getAge() < 18) return 10;
353
354 $points = $this->getPoints();
355 if ($points >= 300) return 200;
356 if ($points >= 200) return 150;
357 if ($points >= 150) return 35;
358 if ($points >= 140) return 30;
359 if ($points >= 130) return 25;
360 if ($points >= 120) return 20;
361 if ($points >= 110) return 15;
362 if ($points >= 100) return 10;
363
364 // Should not get here
365 throw new Exception(
366 __METHOD__ . ': '.$this->id.' We have reached unreachable code');
367 }
368
369 /**
370 * Get the challenge types that are available in the database
371 *
372 * @param $db Zend_Db_Adapter_Abstract
373 * The database connection to use
374 *
375 * @return array(int => string)
376 */
377 public static function getAvailableChallengeTypes(
378 Zend_Db_Adapter_Abstract $db) {
379 $query = 'select `id`, `type_text` from `cats_type`';
380 return $db->fetchPairs($query);
381 }
382
383 /**
384 * Get the challenge variants for this type that are available in the
385 * database
386 *
387 * @param $db Zend_Db_Adapter_Abstract
388 * The database connection to use
389 * @param $type int
390 * The type of challenge you want to get the variants of
391 *
392 * @return array(int => string)
393 */
394 public static function getAvailableChallengeVariants(
395 Zend_Db_Adapter_Abstract $db, $type) {
396 $query = 'select `id`, `test_text` from `cats_variant`
397 where `type_id` = :type';
398 $query_params['type'] = $type;
399 return $db->fetchPairs($query, $query_params);
400 }
401
402 /**
403 * Assign the challenge to the user
404 *
405 * @param $type int
406 * The type of the challenge, has to be one of the keys returned by
407 * getAvailableChallengeTypes()
408 * @param $variant int
409 * The variant of the challenge, has to be one of the keys returned by
410 * getAvailableChallengeVariants()
411 * @param $date Zend_Date
412 * The date the challenge was passed, defaults to current time
413 */
414 public function assignChallenge($type, $variant, Zend_Date $date = null) {
415 $types = self::getAvailableChallengeTypes($this->db);
416 if (!isset($types[$type])) {
417 throw new Exception(
418 __METHOD__ . ': got wrong challenge type '.$type.' when '.
419 'assigning challenge to user '.$this->id);
420 }
421
422 $variants = self::getAvailableChallengeVariants($this->db, $type);
423 if (!isset($variants[$variant])) {
424 throw new Exception(
425 __METHOD__ . ': got wrong challenge variant '.$variant.' when '.
426 'assigning challenge to user '.$this->id);
427 }
428
429 $challenge = array();
430 $challenge['user_id'] = $this->id;
431 $challenge['variant_id'] = $variant;
432 if ($date !== null) {
433 $challenge['pass_date'] = $date->toString('Y-m-d H:i:s');
434 // otherwise default value of the database will be used
435 }
436
437 $this->db->insert('cats_passed', $challenge);
438
439 $this->fixAssurerFlag();
440 }
441
442 /**
443 * Get the flags that are set
444 *
445 * @return array (string => boolean)
446 */
447 public function getFlags() {
448 $flags = $this->db->select()->from('users', self::flags())
449 ->where('`id` = ?', $this->id)->query()->fetch();
450
451 foreach ($flags as $key => $value) {
452 if ($value === '0') {
453 $flags[$key] = false;
454 } else {
455 $flags[$key] = true;
456 }
457 }
458
459 return $flags;
460 }
461
462 /**
463 * Set the flags - to know which flags exist you might want to call
464 * getFlags() first
465 *
466 * @param $flags array (string => boolean)
467 * Currently unknown flags are silently ignored
468 */
469 public function setFlags(array $flags) {
470 $newflags = array();
471
472 // filter values
473 foreach (self::flags() as $flag) {
474 if (isset($flags[$flag])) {
475 if ($flags[$flag]) {
476 $newflags[$flag] = 1;
477 } else {
478 $newflags[$flag] = 0;
479 }
480 }
481 }
482
483 $where = $this->db->quoteInto('`id` = ?', $this->id, Zend_Db::INT_TYPE);
484 $this->db->update('users', $newflags, $where);
485 }
486
487 /**
488 * The flags from the `users` table that might be set
489 */
490 private static function flags() {
491 return array(
492 'verified',
493 'listme',
494 'codesign',
495 '1024bit',
496 'admin',
497 'orgadmin',
498 'ttpadmin',
499 'adadmin',
500 'board',
501 'tverify',
502 'locadmin',
503 'locked',
504 'assurer',
505 'assurer_blocked');
506 }
507 }