bug 1276: Allow more name variants according to PracticeOnNames when signing a PGP key
[cacert-devel.git] / includes / lib / account.php
1 <?php
2 /*
3 LibreSSL - CAcert web application
4 Copyright (C) 2004-2008 CAcert Inc.
5
6 This program is free software; you can redistribute it and/or modify
7 it under the terms of the GNU General Public License as published by
8 the Free Software Foundation; version 2 of the License.
9
10 This program is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 GNU General Public License for more details.
14
15 You should have received a copy of the GNU General Public License
16 along with this program; if not, write to the Free Software
17 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
18 */
19
20 /**
21 * Function to recalculate the cached Assurer status
22 *
23 * @param int $userID
24 * if the user ID is not given the flag will be recalculated for all users
25 *
26 * @return bool
27 * false if there was an error on fixing the flag. This does NOT return the
28 * new value of the flag
29 */
30 function fix_assurer_flag($userID = NULL)
31 {
32 // Update Assurer-Flag on users table if 100 points and CATS passed.
33 //
34 // We may have some performance issues here if no userID is given
35 // there are ~150k assurances and ~220k users currently
36 // but the exists-clause on cats_passed should be a good filter
37 $sql = '
38 UPDATE `users` AS `u` SET `assurer` = 1
39 WHERE '.(
40 ($userID === NULL) ?
41 '`u`.`assurer` = 0' :
42 '`u`.`id` = \''.intval($userID).'\''
43 ).'
44 AND EXISTS(
45 SELECT 1 FROM `cats_passed` AS `cp`, `cats_variant` AS `cv`
46 WHERE `cp`.`variant_id` = `cv`.`id`
47 AND `cv`.`type_id` = 1
48 AND `cp`.`user_id` = `u`.`id`
49 )
50 AND (
51 SELECT SUM(`points`) FROM `notary` AS `n`
52 WHERE `n`.`to` = `u`.`id`
53 AND (`n`.`expire` > now()
54 OR `n`.`expire` IS NULL)
55 ) >= 100';
56
57 $query = mysql_query($sql);
58 if (!$query) {
59 return false;
60 }
61 // Challenge has been passed and non-expired points >= 100
62
63 // Reset flag if requirements are not met
64 //
65 // Also a bit performance critical but assurer flag is only set on
66 // ~5k accounts
67 $sql = '
68 UPDATE `users` AS `u` SET `assurer` = 0
69 WHERE '.(
70 ($userID === NULL) ?
71 '`u`.`assurer` <> 0' :
72 '`u`.`id` = \''.intval($userID).'\''
73 ).'
74 AND (
75 NOT EXISTS(
76 SELECT 1 FROM `cats_passed` AS `cp`,
77 `cats_variant` AS `cv`
78 WHERE `cp`.`variant_id` = `cv`.`id`
79 AND `cv`.`type_id` = 1
80 AND `cp`.`user_id` = `u`.`id`
81 )
82 OR (
83 SELECT SUM(`points`) FROM `notary` AS `n`
84 WHERE `n`.`to` = `u`.`id`
85 AND (
86 `n`.`expire` > now()
87 OR `n`.`expire` IS NULL
88 )
89 ) < 100
90 )';
91
92 $query = mysql_query($sql);
93 if (!$query) {
94 return false;
95 }
96
97 return true;
98 }