bug 1138: added comments in 43.php as header for the different sections
[cacert-devel.git] / pages / account / 43.php
1 <? /*
2 LibreSSL - CAcert web application
3 Copyright (C) 2004-2008 CAcert Inc.
4
5 This program is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation; version 2 of the License.
8
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU General Public License for more details.
13
14 You should have received a copy of the GNU General Public License
15 along with this program; if not, write to the Free Software
16 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
17 */ ?>
18 <?
19 include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php");
20
21 //check if an assurance should be deleted
22 if(array_key_exists('assurance',$_REQUEST) && $_REQUEST['assurance'] > 0)
23 {
24 $assurance = mysql_escape_string(intval($_REQUEST['assurance']));
25 $row = 0;
26 $res = mysql_query("select `to` from `notary` where `id`='$assurance'");
27 if ($res) {
28 $row = mysql_fetch_assoc($res);
29 }
30 mysql_query("delete from `notary` where `id`='$assurance'");
31 if ($row) {
32 fix_assurer_flag($row['to']);
33 }
34 }
35
36 // search for an account by email search, if more than one is found display list to choose
37 if(intval(array_key_exists('userid',$_REQUEST)?$_REQUEST['userid']:0) <= 0)
38 {
39 $_REQUEST['userid'] = 0;
40
41 $emailsearch = $email = mysql_escape_string(stripslashes($_REQUEST['email']));
42
43 //Disabled to speed up the queries
44 //if(!strstr($email, "%"))
45 // $emailsearch = "%$email%";
46
47 // bug-975 ted+uli changes --- begin
48 if(preg_match("/^[0-9]+$/", $email)) {
49 // $email consists of digits only ==> search for IDs
50 // Be defensive here (outer join) if primary mail is not listed in email table
51 $query = "select `users`.`id` as `id`, `email`.`email` as `email`
52 from `users` left outer join `email` on (`users`.`id`=`email`.`memid`)
53 where (`email`.`id`='$email' or `users`.`id`='$email')
54 and `users`.`deleted`=0
55 group by `users`.`id` limit 100";
56 } else {
57 // $email contains non-digits ==> search for mail addresses
58 // Be defensive here (outer join) if primary mail is not listed in email table
59 $query = "select `users`.`id` as `id`, `email`.`email` as `email`
60 from `users` left outer join `email` on (`users`.`id`=`email`.`memid`)
61 where (`email`.`email` like '$emailsearch'
62 or `users`.`email` like '$emailsearch')
63 and `users`.`deleted`=0
64 group by `users`.`id` limit 100";
65 }
66 // bug-975 ted+uli changes --- end
67 $res = mysql_query($query);
68 if(mysql_num_rows($res) > 1) { ?>
69 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
70 <tr>
71 <td colspan="5" class="title"><?=_("Select Specific Account Details")?></td>
72 </tr>
73 <tr>
74 <td class="DataTD"><?=_("User ID")?></td>
75 <td class="DataTD"><?=_("Email")?></td>
76 </tr>
77 <?
78 while($row = mysql_fetch_assoc($res))
79 { ?>
80 <tr>
81 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>"><?=intval($row['id'])?></a></td>
82 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>"><?=sanitizeHTML($row['email'])?></a></td>
83 </tr>
84 <? } if(mysql_num_rows($res) >= 100) { ?>
85 <tr>
86 <td class="DataTD" colspan="2"><?=_("Only the first 100 rows are displayed.")?></td>
87 </tr>
88 <? } else { ?>
89 <tr>
90 <td class="DataTD" colspan="2"><? printf(_("%s rows displayed."), mysql_num_rows($res)); ?></td>
91 </tr>
92 <? } ?>
93 </table><br><br>
94 <? } elseif(mysql_num_rows($res) == 1) {
95 $row = mysql_fetch_assoc($res);
96 $_REQUEST['userid'] = $row['id'];
97 } else {
98 printf(_("No users found matching %s"), sanitizeHTML($email));
99 }
100 }
101
102 // display user information for given user id
103 if(intval($_REQUEST['userid']) > 0)
104 {
105 $userid = intval($_REQUEST['userid']);
106 $query = "select * from `users` where `users`.`id`='$userid' and `users`.`deleted`=0";
107 $res = mysql_query($query);
108 if(mysql_num_rows($res) <= 0)
109 {
110 echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are a foot!");
111 } else {
112 $row = mysql_fetch_assoc($res);
113 $query = "select sum(`points`) as `points` from `notary` where `to`='".intval($row['id'])."'";
114 $dres = mysql_query($query);
115 $drow = mysql_fetch_assoc($dres);
116 $alerts = mysql_fetch_assoc(mysql_query("select * from `alerts` where `memid`='".intval($row['id'])."'"));
117 //display account data
118 ?>
119 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
120 <tr>
121 <td colspan="5" class="title"><? printf(_("%s's Account Details"), sanitizeHTML($row['email'])); ?></td>
122 </tr>
123 <tr>
124 <td class="DataTD"><?=_("Email")?>:</td>
125 <td class="DataTD"><?=sanitizeHTML($row['email'])?></td>
126 </tr>
127 <tr>
128 <td class="DataTD"><?=_("First Name")?>:</td>
129 <td class="DataTD"><form method="post" action="account.php" onSubmit="if(!confirm('<?=_("Are you sure you want to modify this DOB and/or last name?")?>')) return false;">
130 <input type="hidden" name="csrf" value="<?=make_csrf('admchangepers')?>" />
131 <input type="text" name="fname" value="<?=sanitizeHTML($row['fname'])?>"></td>
132 </tr>
133 <tr>
134 <td class="DataTD"><?=_("Middle Name")?>:</td>
135 <td class="DataTD"><input type="text" name="mname" value="<?=sanitizeHTML($row['mname'])?>"></td>
136 </tr>
137 <tr>
138 <td class="DataTD"><?=_("Last Name")?>:</td>
139 <td class="DataTD"> <input type="hidden" name="oldid" value="43">
140 <input type="hidden" name="action" value="updatedob">
141 <input type="hidden" name="userid" value="<?=intval($userid)?>">
142 <input type="text" name="lname" value="<?=sanitizeHTML($row['lname'])?>"></td>
143 </tr>
144 <tr>
145 <td class="DataTD"><?=_("Suffix")?>:</td>
146 <td class="DataTD"><input type="text" name="suffix" value="<?=sanitizeHTML($row['suffix'])?>"></td>
147 </tr>
148 <tr>
149 <td class="DataTD"><?=_("Date of Birth")?>:</td>
150 <td class="DataTD">
151 <?
152 $year = intval(substr($row['dob'], 0, 4));
153 $month = intval(substr($row['dob'], 5, 2));
154 $day = intval(substr($row['dob'], 8, 2));
155 ?><nobr><select name="day">
156 <?
157 for($i = 1; $i <= 31; $i++)
158 {
159 echo "<option";
160 if($day == $i)
161 echo " selected='selected'";
162 echo ">$i</option>";
163 }
164 ?>
165 </select>
166 <select name="month">
167 <?
168 for($i = 1; $i <= 12; $i++)
169 {
170 echo "<option value='$i'";
171 if($month == $i)
172 echo " selected='selected'";
173 echo ">".ucwords(strftime("%B", mktime(0,0,0,$i,1,date("Y"))))."</option>";
174 }
175 ?>
176 </select>
177 <input type="text" name="year" value="<?=$year?>" size="4">
178 <input type="submit" value="Go"></form></nobr></td>
179 <? // list of flags ?>
180 </tr>
181 <tr>
182 <td class="DataTD"><?=_("CCA accepted")?>:</td>
183 <td class="DataTD"><a href="account.php?id=57&amp;userid=<?=intval($row['id'])?>"><?=intval(get_user_agreement_status($row['id'])) ? _("Yes") : _("No") ?></a></td>
184 </tr>
185 <tr>
186 <td class="DataTD"><?=_("Trainings")?>:</td>
187 <td class="DataTD"><a href="account.php?id=55&amp;userid=<?=intval($row['id'])?>">show</a></td>
188 </tr>
189 <tr>
190 <td class="DataTD"><?=_("Is Assurer")?>:</td>
191 <td class="DataTD"><a href="account.php?id=43&amp;assurer=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admsetassuret')?>"><?=$row['assurer']?></a></td>
192 </tr>
193 <tr>
194 <td class="DataTD"><?=_("Blocked Assurer")?>:</td>
195 <td class="DataTD"><a href="account.php?id=43&amp;assurer_blocked=<?=intval($row['id'])?>"><?=$row['assurer_blocked']?></a></td>
196 </tr>
197 <tr>
198 <td class="DataTD"><?=_("Account Locking")?>:</td>
199 <td class="DataTD"><a href="account.php?id=43&amp;locked=<?=$row['id']?>&amp;csrf=<?=make_csrf('admactlock')?>"><?=$row['locked']?></a></td>
200 </tr>
201 <tr>
202 <td class="DataTD"><?=_("Code Signing")?>:</td>
203 <td class="DataTD"><a href="account.php?id=43&amp;codesign=<?=$row['id']?>&amp;csrf=<?=make_csrf('admcodesign')?>"><?=$row['codesign']?></a></td>
204 </tr>
205 <tr>
206 <td class="DataTD"><?=_("Org Assurer")?>:</td>
207 <td class="DataTD"><a href="account.php?id=43&amp;orgadmin=<?=$row['id']?>&amp;csrf=<?=make_csrf('admorgadmin')?>"><?=$row['orgadmin']?></a></td>
208 </tr>
209 <tr>
210 <td class="DataTD"><?=_("TTP Admin")?>:</td>
211 <td class="DataTD"><a href="account.php?id=43&amp;ttpadmin=<?=$row['id']?>&amp;csrf=<?=make_csrf('admttpadmin')?>"><?=$row['ttpadmin']?></a></td>
212 </tr>
213 <tr>
214 <td class="DataTD"><?=_("Location Admin")?>:</td>
215 <td class="DataTD"><a href="account.php?id=43&amp;locadmin=<?=$row['id']?>"><?=$row['locadmin']?></a></td>
216 </tr>
217 <tr>
218 <td class="DataTD"><?=_("Admin")?>:</td>
219 <td class="DataTD"><a href="account.php?id=43&amp;admin=<?=$row['id']?>&amp;csrf=<?=make_csrf('admsetadmin')?>"><?=$row['admin']?></a></td>
220 </tr>
221 <tr>
222 <td class="DataTD"><?=_("Ad Admin")?>:</td>
223 <td class="DataTD"><a href="account.php?id=43&amp;adadmin=<?=$row['id']?>"><?=$row['adadmin']?></a> (0 = none, 1 = submit, 2 = approve)</td>
224 </tr>
225 <tr>
226 <td class="DataTD"><?=_("Tverify Account")?>:</td>
227 <td class="DataTD"><a href="account.php?id=43&amp;tverify=<?=$row['id']?>"><?=$row['tverify']?></a></td>
228 </tr>
229 <tr>
230 <td class="DataTD"><?=_("General Announcements")?>:</td>
231 <td class="DataTD"><a href="account.php?id=43&amp;general=<?=$row['id']?>"><?=$alerts['general']?></a></td>
232 </tr>
233 <tr>
234 <td class="DataTD"><?=_("Country Announcements")?>:</td>
235 <td class="DataTD"><a href="account.php?id=43&amp;country=<?=$row['id']?>"><?=$alerts['country']?></a></td>
236 </tr>
237 <tr>
238 <td class="DataTD"><?=_("Regional Announcements")?>:</td>
239 <td class="DataTD"><a href="account.php?id=43&amp;regional=<?=$row['id']?>"><?=$alerts['regional']?></a></td>
240 </tr>
241 <tr>
242 <td class="DataTD"><?=_("Within 200km Announcements")?>:</td>
243 <td class="DataTD"><a href="account.php?id=43&amp;radius=<?=$row['id']?>"><?=$alerts['radius']?></a></td>
244 </tr>
245 <? //change password, view secret questions and delete account section ?>
246 <tr>
247 <td class="DataTD"><?=_("Change Password")?>:</td>
248 <td class="DataTD"><a href="account.php?id=44&amp;userid=<?=$row['id']?>"><?=_("Change Password")?></a></td>
249 </tr>
250 <tr>
251 <td class="DataTD"><?=_("Delete Account")?>:</td>
252 <td class="DataTD"><a href="account.php?id=50&amp;userid=<?=$row['id']?>&amp;csrf=<?=make_csrf('admdelaccount')?>"><?=_("Delete Account")?></a></td>
253 </tr>
254 <?
255 // This is intensionally a $_GET for audit purposes. DO NOT CHANGE!!!
256 if(array_key_exists('showlostpw',$_GET) && $_GET['showlostpw'] == "yes") {
257 ?>
258 <tr>
259 <td class="DataTD"><?=_("Lost Password")?> - Q1:</td>
260 <td class="DataTD"><?=sanitizeHTML($row['Q1'])?></td>
261 </tr>
262 <tr>
263 <td class="DataTD"><?=_("Lost Password")?> - A1:</td>
264 <td class="DataTD"><?=sanitizeHTML($row['A1'])?></td>
265 </tr>
266 <tr>
267 <td class="DataTD"><?=_("Lost Password")?> - Q2:</td>
268 <td class="DataTD"><?=sanitizeHTML($row['Q2'])?></td>
269 </tr>
270 <tr>
271 <td class="DataTD"><?=_("Lost Password")?> - A2:</td>
272 <td class="DataTD"><?=sanitizeHTML($row['A2'])?></td>
273 </tr>
274 <tr>
275 <td class="DataTD"><?=_("Lost Password")?> - Q3:</td>
276 <td class="DataTD"><?=sanitizeHTML($row['Q3'])?></td>
277 </tr>
278 <tr>
279 <td class="DataTD"><?=_("Lost Password")?> - A3:</td>
280 <td class="DataTD"><?=sanitizeHTML($row['A3'])?></td>
281 </tr>
282 <tr>
283 <td class="DataTD"><?=_("Lost Password")?> - Q4:</td>
284 <td class="DataTD"><?=sanitizeHTML($row['Q4'])?></td>
285 </tr>
286 <tr>
287 <td class="DataTD"><?=_("Lost Password")?> - A4:</td>
288 <td class="DataTD"><?=sanitizeHTML($row['A4'])?></td>
289 </tr>
290 <tr>
291 <td class="DataTD"><?=_("Lost Password")?> - Q5:</td>
292 <td class="DataTD"><?=sanitizeHTML($row['Q5'])?></td>
293 </tr>
294 <tr>
295 <td class="DataTD"><?=_("Lost Password")?> - A5:</td>
296 <td class="DataTD"><?=sanitizeHTML($row['A5'])?></td>
297 </tr>
298 <? } else { ?>
299 <tr>
300 <td class="DataTD" colspan="2"><a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;showlostpw=yes"><?=_("Show Lost Password Details")?></a></td>
301 </tr>
302 <? }
303 // list assurance points
304 ?>
305 <tr>
306 <td class="DataTD"><?=_("Assurance Points")?>:</td>
307 <td class="DataTD"><?=intval($drow['points'])?></td>
308 </tr>
309 </table>
310 <br><?
311 //list secondary email addresses
312 $query = "select * from `email` where `memid`='".intval($row['id'])."' and `deleted`=0 and `hash`=''
313 and `email`!='".mysql_escape_string($row['email'])."'";
314 $dres = mysql_query($query);
315 if(mysql_num_rows($dres) > 0) { ?>
316 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
317 <tr>
318 <td colspan="5" class="title"><?=_("Alternate Verified Email Addresses")?></td>
319 </tr><?
320 $rc = mysql_num_rows($dres);
321 while($drow = mysql_fetch_assoc($dres))
322 { ?>
323 <tr>
324 <td class="DataTD"><?=_("Secondary Emails")?>:</td>
325 <td class="DataTD"><?=sanitizeHTML($drow['email'])?></td>
326 </tr>
327 <? } ?>
328 </table>
329 <br><? } ?>
330 <?
331 $query = "select * from `domains` where `memid`='".intval($row['id'])."' and `deleted`=0 and `hash`=''";
332 $dres = mysql_query($query);
333 if(mysql_num_rows($dres) > 0) { ?>
334 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
335 <tr>
336 <? // list of domains ?>
337 <td colspan="5" class="title"><?=_("Verified Domains")?></td>
338 </tr><?
339 $rc = mysql_num_rows($dres);
340 while($drow = mysql_fetch_assoc($dres))
341 { ?>
342 <tr>
343 <td class="DataTD"><?=_("Domain")?>:</td>
344 <td class="DataTD"><?=sanitizeHTML($drow['domain'])?></td>
345 </tr>
346 <? } ?>
347 </table>
348 <br>
349 <? } ?>
350 <? // Begin - Debug infos ?>
351 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
352 <tr>
353 <td colspan="2" class="title"><?=_("Account State")?></td>
354 </tr>
355
356 <?
357 // --- bug-975 begin ---
358 // potential db inconsistency like in a20110804.1
359 // Admin console -> don't list user account
360 // User login -> impossible
361 // Assurer, assure someone -> user displayed
362 /* regular user account search with regular settings
363
364 --- Admin Console find user query
365 $query = "select `users`.`id` as `id`, `email`.`email` as `email` from `users`,`email`
366 where `users`.`id`=`email`.`memid` and
367 (`email`.`email` like '$emailsearch' or `email`.`id`='$email' or `users`.`id`='$email') and
368 `email`.`hash`='' and `email`.`deleted`=0 and `users`.`deleted`=0
369 group by `users`.`id` limit 100";
370 => requirements
371 1. email.hash = ''
372 2. email.deleted = 0
373 3. users.deleted = 0
374 4. email.email = primary-email (???) or'd
375 not covered by admin console find user routine, but may block users login
376 5. users.verified = 0|1
377 further "special settings"
378 6. users.locked (setting displayed in display form)
379 7. users.assurer_blocked (setting displayed in display form)
380
381 --- User login user query
382 select * from `users` where `email`='$email' and (`password`=old_password('$pword') or `password`=sha1('$pword') or
383 `password`=password('$pword')) and `verified`=1 and `deleted`=0 and `locked`=0
384 => requirements
385 1. users.verified = 1
386 2. users.deleted = 0
387 3. users.locked = 0
388 4. users.email = primary-email
389
390 --- Assurer, assure someone find user query
391 select * from `users` where `email`='".mysql_escape_string(stripslashes($_POST['email']))."'
392 and `deleted`=0
393 => requirements
394 1. users.deleted = 0
395 2. users.email = primary-email
396 Admin User Assurer
397 bit Console Login assure someone
398
399 1. email.hash = '' Yes No No
400 2. email.deleted = 0 Yes No No
401 3. users.deleted = 0 Yes Yes Yes
402 4. users.verified = 1 No Yes No
403 5. users.locked = 0 No Yes No
404 6. users.email = prim-email No Yes Yes
405 7. email.email = prim-email Yes No No
406
407 full usable account needs all 7 requirements fulfilled
408 so if one setting isn't set/cleared there is an inconsistency either way
409 if eg email.email is not avail, admin console cannot open user info
410 but user can login and assurer can display user info
411 if user verified is not set to 1, admin console displays user record
412 but user cannot login, but assurer can search for the user and the data displays
413
414 consistency check:
415 1. search primary-email in users.email
416 2. search primary-email in email.email
417 3. userid = email.memid
418 4. check settings from table 1. - 5.
419
420 */
421
422 $inconsistency = 0;
423 $inconsistencydisp = "";
424 $inccause = "";
425 // current userid intval($row['id'])
426 $query = "select `email` as `uemail`, `deleted` as `udeleted`, `verified`, `locked`
427 from `users` where `id`='".intval($row['id'])."' ";
428 $dres = mysql_query($query);
429 $drow = mysql_fetch_assoc($dres);
430 $uemail = $drow['uemail'];
431 $udeleted = $drow['udeleted'];
432 $uverified = $drow['verified'];
433 $ulocked = $drow['locked'];
434
435 $query = "select `hash`, `email` as `eemail` from `email`
436 where `memid`='".intval($row['id'])."' and
437 `email` ='".$uemail."' and
438 `deleted` = 0";
439 $dres = mysql_query($query);
440 if ($drow = mysql_fetch_assoc($dres)) {
441 $drow['edeleted'] = 0;
442 } else {
443 // try if there are deleted entries
444 $query = "select `hash`, `deleted` as `edeleted`, `email` as `eemail` from `email`
445 where `memid`='".intval($row['id'])."' and
446 `email` ='".$uemail."'";
447 $dres = mysql_query($query);
448 $drow = mysql_fetch_assoc($dres);
449 }
450
451 if ($drow) {
452 $eemail = $drow['eemail'];
453 $edeleted = $drow['edeleted'];
454 $ehash = $drow['hash'];
455 if ($udeleted!=0) {
456 $inconsistency += 1;
457 $inccause .= (empty($inccause)?"":"<br>")._("Users record set to deleted");
458 }
459 if ($uverified!=1) {
460 $inconsistency += 2;
461 $inccause .= (empty($inccause)?"":"<br>")._("Users record verified not set");
462 }
463 if ($ulocked!=0) {
464 $inconsistency += 4;
465 $inccause .= (empty($inccause)?"":"<br>")._("Users record locked set");
466 }
467 if ($edeleted!=0) {
468 $inconsistency += 8;
469 $inccause .= (empty($inccause)?"":"<br>")._("Email record set deleted");
470 }
471 if ($ehash!='') {
472 $inconsistency += 16;
473 $inccause .= (empty($inccause)?"":"<br>")._("Email record hash not unset");
474 }
475 } else {
476 $inconsistency = 32;
477 $inccause = _("Prim. email, Email record doesn't exist");
478 }
479 if ($inconsistency>0) {
480 // $inconsistencydisp = _("Yes");
481 ?>
482 <tr>
483 <td class="DataTD"><?=_("Account inconsistency")?>:</td>
484 <td class="DataTD"><?=$inccause?><br>code: <?=$inconsistency?></td>
485 </tr>
486 <tr>
487 <td colspan="2" class="DataTD" style="max-width: 75ex">
488 <?=_("Account inconsistency can cause problems in daily account ".
489 "operations and needs to be fixed manually through arbitration/critical ".
490 "team.")?>
491 </td>
492 </tr>
493 <? }
494
495 // --- bug-975 end ---
496 ?>
497 </table>
498 <br>
499 <?
500 // End - Debug infos
501
502 // certificate overview
503 ?>
504
505 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
506 <tr>
507 <td colspan="6" class="title"><?=_("Certificates")?></td>
508 </tr>
509
510 <tr>
511 <td class="DataTD"><?=_("Cert Type")?>:</td>
512 <td class="DataTD"><?=_("Total")?></td>
513 <td class="DataTD"><?=_("Valid")?></td>
514 <td class="DataTD"><?=_("Expired")?></td>
515 <td class="DataTD"><?=_("Revoked")?></td>
516 <td class="DataTD"><?=_("Latest Expire")?></td>
517 </tr>
518
519 <tr>
520 <td class="DataTD"><?=_("Server")?>:</td>
521 <?
522 $query = "select COUNT(*) as `total`,
523 MAX(`domaincerts`.`expire`) as `maxexpire`
524 from `domains` inner join `domaincerts`
525 on `domains`.`id` = `domaincerts`.`domid`
526 where `domains`.`memid` = '".intval($row['id'])."' ";
527 $dres = mysql_query($query);
528 $drow = mysql_fetch_assoc($dres);
529 $total = $drow['total'];
530
531 $maxexpire = "0000-00-00 00:00:00";
532 if ($drow['maxexpire']) {
533 $maxexpire = $drow['maxexpire'];
534 }
535
536 if($total > 0) {
537 $query = "select COUNT(*) as `valid`
538 from `domains` inner join `domaincerts`
539 on `domains`.`id` = `domaincerts`.`domid`
540 where `domains`.`memid` = '".intval($row['id'])."'
541 and `revoked` = '0000-00-00 00:00:00'
542 and `expire` > NOW()";
543 $dres = mysql_query($query);
544 $drow = mysql_fetch_assoc($dres);
545 $valid = $drow['valid'];
546
547 $query = "select COUNT(*) as `expired`
548 from `domains` inner join `domaincerts`
549 on `domains`.`id` = `domaincerts`.`domid`
550 where `domains`.`memid` = '".intval($row['id'])."'
551 and `expire` <= NOW()";
552 $dres = mysql_query($query);
553 $drow = mysql_fetch_assoc($dres);
554 $expired = $drow['expired'];
555
556 $query = "select COUNT(*) as `revoked`
557 from `domains` inner join `domaincerts`
558 on `domains`.`id` = `domaincerts`.`domid`
559 where `domains`.`memid` = '".intval($row['id'])."'
560 and `revoked` != '0000-00-00 00:00:00'";
561 $dres = mysql_query($query);
562 $drow = mysql_fetch_assoc($dres);
563 $revoked = $drow['revoked'];
564 ?>
565 <td class="DataTD"><?=intval($total)?></td>
566 <td class="DataTD"><?=intval($valid)?></td>
567 <td class="DataTD"><?=intval($expired)?></td>
568 <td class="DataTD"><?=intval($revoked)?></td>
569 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
570 substr($maxexpire, 0, 10) : _("Pending")?></td>
571 <?
572 } else { // $total > 0
573 ?>
574 <td colspan="5" class="DataTD"><?=_("None")?></td>
575 <?
576 } ?>
577 </tr>
578
579 <tr>
580 <td class="DataTD"><?=_("Client")?>:</td>
581 <?
582 $query = "select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
583 from `emailcerts`
584 where `memid` = '".intval($row['id'])."' ";
585 $dres = mysql_query($query);
586 $drow = mysql_fetch_assoc($dres);
587 $total = $drow['total'];
588
589 $maxexpire = "0000-00-00 00:00:00";
590 if ($drow['maxexpire']) {
591 $maxexpire = $drow['maxexpire'];
592 }
593
594 if($total > 0) {
595 $query = "select COUNT(*) as `valid`
596 from `emailcerts`
597 where `memid` = '".intval($row['id'])."'
598 and `revoked` = '0000-00-00 00:00:00'
599 and `expire` > NOW()";
600 $dres = mysql_query($query);
601 $drow = mysql_fetch_assoc($dres);
602 $valid = $drow['valid'];
603
604 $query = "select COUNT(*) as `expired`
605 from `emailcerts`
606 where `memid` = '".intval($row['id'])."'
607 and `expire` <= NOW()";
608 $dres = mysql_query($query);
609 $drow = mysql_fetch_assoc($dres);
610 $expired = $drow['expired'];
611
612 $query = "select COUNT(*) as `revoked`
613 from `emailcerts`
614 where `memid` = '".intval($row['id'])."'
615 and `revoked` != '0000-00-00 00:00:00'";
616 $dres = mysql_query($query);
617 $drow = mysql_fetch_assoc($dres);
618 $revoked = $drow['revoked'];
619 ?>
620 <td class="DataTD"><?=intval($total)?></td>
621 <td class="DataTD"><?=intval($valid)?></td>
622 <td class="DataTD"><?=intval($expired)?></td>
623 <td class="DataTD"><?=intval($revoked)?></td>
624 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
625 substr($maxexpire, 0, 10) : _("Pending")?></td>
626 <?
627 } else { // $total > 0
628 ?>
629 <td colspan="5" class="DataTD"><?=_("None")?></td>
630 <?
631 } ?>
632 </tr>
633
634 <tr>
635 <td class="DataTD"><?=_("GPG")?>:</td>
636 <?
637 $query = "select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
638 from `gpg`
639 where `memid` = '".intval($row['id'])."' ";
640 $dres = mysql_query($query);
641 $drow = mysql_fetch_assoc($dres);
642 $total = $drow['total'];
643
644 $maxexpire = "0000-00-00 00:00:00";
645 if ($drow['maxexpire']) {
646 $maxexpire = $drow['maxexpire'];
647 }
648
649 if($total > 0) {
650 $query = "select COUNT(*) as `valid`
651 from `gpg`
652 where `memid` = '".intval($row['id'])."'
653 and `expire` > NOW()";
654 $dres = mysql_query($query);
655 $drow = mysql_fetch_assoc($dres);
656 $valid = $drow['valid'];
657
658 $query = "select COUNT(*) as `expired`
659 from `emailcerts`
660 where `memid` = '".intval($row['id'])."'
661 and `expire` <= NOW()";
662 $dres = mysql_query($query);
663 $drow = mysql_fetch_assoc($dres);
664 $expired = $drow['expired'];
665
666 ?>
667 <td class="DataTD"><?=intval($total)?></td>
668 <td class="DataTD"><?=intval($valid)?></td>
669 <td class="DataTD"><?=intval($expired)?></td>
670 <td class="DataTD"></td>
671 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
672 substr($maxexpire, 0, 10) : _("Pending")?></td>
673 <?
674 } else { // $total > 0
675 ?>
676 <td colspan="5" class="DataTD"><?=_("None")?></td>
677 <?
678 } ?>
679 </tr>
680
681 <tr>
682 <td class="DataTD"><a href="account.php?id=58&amp;userid=<?=intval($row['id'])?>"><?=_("Org Server")?></a>:</td>
683 <?
684 $query = "select COUNT(*) as `total`,
685 MAX(`orgcerts`.`expire`) as `maxexpire`
686 from `orgdomaincerts` as `orgcerts` inner join `org`
687 on `orgcerts`.`orgid` = `org`.`orgid`
688 where `org`.`memid` = '".intval($row['id'])."' ";
689 $dres = mysql_query($query);
690 $drow = mysql_fetch_assoc($dres);
691 $total = $drow['total'];
692
693 $maxexpire = "0000-00-00 00:00:00";
694 if ($drow['maxexpire']) {
695 $maxexpire = $drow['maxexpire'];
696 }
697
698 if($total > 0) {
699 $query = "select COUNT(*) as `valid`
700 from `orgdomaincerts` as `orgcerts` inner join `org`
701 on `orgcerts`.`orgid` = `org`.`orgid`
702 where `org`.`memid` = '".intval($row['id'])."'
703 and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
704 and `orgcerts`.`expire` > NOW()";
705 $dres = mysql_query($query);
706 $drow = mysql_fetch_assoc($dres);
707 $valid = $drow['valid'];
708
709 $query = "select COUNT(*) as `expired`
710 from `orgdomaincerts` as `orgcerts` inner join `org`
711 on `orgcerts`.`orgid` = `org`.`orgid`
712 where `org`.`memid` = '".intval($row['id'])."'
713 and `orgcerts`.`expire` <= NOW()";
714 $dres = mysql_query($query);
715 $drow = mysql_fetch_assoc($dres);
716 $expired = $drow['expired'];
717
718 $query = "select COUNT(*) as `revoked`
719 from `orgdomaincerts` as `orgcerts` inner join `org`
720 on `orgcerts`.`orgid` = `org`.`orgid`
721 where `org`.`memid` = '".intval($row['id'])."'
722 and `orgcerts`.`revoked` != '0000-00-00 00:00:00'";
723 $dres = mysql_query($query);
724 $drow = mysql_fetch_assoc($dres);
725 $revoked = $drow['revoked'];
726 ?>
727 <td class="DataTD"><?=intval($total)?></td>
728 <td class="DataTD"><?=intval($valid)?></td>
729 <td class="DataTD"><?=intval($expired)?></td>
730 <td class="DataTD"><?=intval($revoked)?></td>
731 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
732 substr($maxexpire, 0, 10) : _("Pending")?></td>
733 <?
734 } else { // $total > 0
735 ?>
736 <td colspan="5" class="DataTD"><?=_("None")?></td>
737 <?
738 } ?>
739 </tr>
740
741 <tr>
742 <td class="DataTD"><?=_("Org Client")?>:</td>
743 <?
744 $query = "select COUNT(*) as `total`,
745 MAX(`orgcerts`.`expire`) as `maxexpire`
746 from `orgemailcerts` as `orgcerts` inner join `org`
747 on `orgcerts`.`orgid` = `org`.`orgid`
748 where `org`.`memid` = '".intval($row['id'])."' ";
749 $dres = mysql_query($query);
750 $drow = mysql_fetch_assoc($dres);
751 $total = $drow['total'];
752
753 $maxexpire = "0000-00-00 00:00:00";
754 if ($drow['maxexpire']) {
755 $maxexpire = $drow['maxexpire'];
756 }
757
758 if($total > 0) {
759 $query = "select COUNT(*) as `valid`
760 from `orgemailcerts` as `orgcerts` inner join `org`
761 on `orgcerts`.`orgid` = `org`.`orgid`
762 where `org`.`memid` = '".intval($row['id'])."'
763 and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
764 and `orgcerts`.`expire` > NOW()";
765 $dres = mysql_query($query);
766 $drow = mysql_fetch_assoc($dres);
767 $valid = $drow['valid'];
768
769 $query = "select COUNT(*) as `expired`
770 from `orgemailcerts` as `orgcerts` inner join `org`
771 on `orgcerts`.`orgid` = `org`.`orgid`
772 where `org`.`memid` = '".intval($row['id'])."'
773 and `orgcerts`.`expire` <= NOW()";
774 $dres = mysql_query($query);
775 $drow = mysql_fetch_assoc($dres);
776 $expired = $drow['expired'];
777
778 $query = "select COUNT(*) as `revoked`
779 from `orgemailcerts` as `orgcerts` inner join `org`
780 on `orgcerts`.`orgid` = `org`.`orgid`
781 where `org`.`memid` = '".intval($row['id'])."'
782 and `orgcerts`.`revoked` != '0000-00-00 00:00:00'";
783 $dres = mysql_query($query);
784 $drow = mysql_fetch_assoc($dres);
785 $revoked = $drow['revoked'];
786 ?>
787 <td class="DataTD"><?=intval($total)?></td>
788 <td class="DataTD"><?=intval($valid)?></td>
789 <td class="DataTD"><?=intval($expired)?></td>
790 <td class="DataTD"><?=intval($revoked)?></td>
791 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
792 substr($maxexpire, 0, 10) : _("Pending")?></td>
793 <?
794 } else { // $total > 0
795 ?>
796 <td colspan="5" class="DataTD"><?=_("None")?></td>
797 <?
798 } ?>
799 </tr>
800 <tr>
801 <td colspan="6" class="title">
802 <form method="post" action="account.php" onSubmit="if(!confirm('<?=_("Are you sure you want to revoke all private certificates?")?>')) return false;">
803 <input type="hidden" name="action" value="revokecert">
804 <input type="hidden" name="oldid" value="43">
805 <input type="hidden" name="userid" value="<?=intval($userid)?>">
806 <input type="submit" value="<?=_('revoke certificates')?>">
807 </form>
808 </td>
809 </tr>
810 </table>
811 <br>
812 <? // list assurances ?>
813
814 <a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto"><?=_("Show Assurances the user got")?></a>
815 (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto15"><?=_("New calculation")?></a>)
816 <br />
817 <a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredby"><?=_("Show Assurances the user gave")?></a>
818 (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredby15"><?=_("New calculation")?></a>)
819 <br />
820
821 <?
822 // if(array_key_exists('assuredto',$_GET) && $_GET['assuredto'] == "yes") {
823
824 function showassuredto()
825 {
826 ?>
827 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
828 <tr>
829 <td colspan="8" class="title"><?=_("Assurance Points")?></td>
830 </tr>
831 <tr>
832 <td class="DataTD"><b><?=_("ID")?></b></td>
833 <td class="DataTD"><b><?=_("Date")?></b></td>
834 <td class="DataTD"><b><?=_("Who")?></b></td>
835 <td class="DataTD"><b><?=_("Email")?></b></td>
836 <td class="DataTD"><b><?=_("Points")?></b></td>
837 <td class="DataTD"><b><?=_("Location")?></b></td>
838 <td class="DataTD"><b><?=_("Method")?></b></td>
839 <td class="DataTD"><b><?=_("Revoke")?></b></td>
840 </tr>
841 <?
842 $query = "select * from `notary` where `to`='".intval($_GET['userid'])."'";
843 $dres = mysql_query($query);
844 $points = 0;
845 while($drow = mysql_fetch_assoc($dres))
846 {
847 $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($drow['from'])."'"));
848 $points += $drow['points'];
849 ?>
850 <tr>
851 <td class="DataTD"><?=$drow['id']?></td>
852 <td class="DataTD"><?=sanitizeHTML($drow['date'])?></td>
853 <td class="DataTD"><a href="wot.php?id=9&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['fname'])." ".sanitizeHTML($fromuser['lname'])?></td>
854 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
855 <td class="DataTD"><?=intval($drow['points'])?></td>
856 <td class="DataTD"><?=sanitizeHTML($drow['location'])?></td>
857 <td class="DataTD"><?=sanitizeHTML($drow['method'])?></td>
858 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['to'])?>&amp;assurance=<?=intval($drow['id'])?>&amp;csrf=<?=make_csrf('admdelassurance')?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),$drow['id'])?>');"><?=_("Revoke")?></a></td>
859 </tr>
860 <? } ?>
861 <tr>
862 <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
863 <td class="DataTD"><?=$points?></td>
864 <td class="DataTD" colspan="3">&nbsp;</td>
865 </tr>
866 </table>
867 <? } ?>
868
869 <?
870 function showassuredby()
871 {
872 ?>
873 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
874 <tr>
875 <td colspan="8" class="title"><?=_("Assurance Points The User Issued")?></td>
876 </tr>
877 <tr>
878 <td class="DataTD"><b><?=_("ID")?></b></td>
879 <td class="DataTD"><b><?=_("Date")?></b></td>
880 <td class="DataTD"><b><?=_("Who")?></b></td>
881 <td class="DataTD"><b><?=_("Email")?></b></td>
882 <td class="DataTD"><b><?=_("Points")?></b></td>
883 <td class="DataTD"><b><?=_("Location")?></b></td>
884 <td class="DataTD"><b><?=_("Method")?></b></td>
885 <td class="DataTD"><b><?=_("Revoke")?></b></td>
886 </tr>
887 <?
888 $query = "select * from `notary` where `from`='".intval($_GET['userid'])."'";
889 $dres = mysql_query($query);
890 $points = 0;
891 while($drow = mysql_fetch_assoc($dres))
892 {
893 $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".$drow['to']."'"));
894 $points += $drow['points'];
895 ?>
896 <tr>
897 <td class="DataTD"><?=$drow['id']?></td>
898 <td class="DataTD"><?=$drow['date']?></td>
899 <td class="DataTD"><a href="wot.php?id=9&userid=<?=$drow['to']?>"><?=$fromuser['fname']." ".$fromuser['lname']?></td>
900 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['to'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
901 <td class="DataTD"><?=$drow['points']?></td>
902 <td class="DataTD"><?=$drow['location']?></td>
903 <td class="DataTD"><?=$drow['method']?></td>
904 <td class="DataTD"><a href="account.php?id=43&userid=<?=$drow['from']?>&assurance=<?=$drow['id']?>&amp;csrf=<?=make_csrf('admdelassurance')?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),$drow['id'])?>');"><?=_("Revoke")?></a></td>
905 </tr>
906 <? } ?>
907 <tr>
908 <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
909 <td class="DataTD"><?=$points?></td>
910 <td class="DataTD" colspan="3">&nbsp;</td>
911 </tr>
912 </table>
913 <? } ?>
914 <br><br>
915 <? } }
916
917 if(isset($_GET['shownotary'])) {
918 switch($_GET['shownotary']) {
919 case 'assuredto':
920 showassuredto();
921 break;
922 case 'assuredby':
923 showassuredby();
924 break;
925 case 'assuredto15':
926 output_received_assurances(intval($_GET['userid']),1);
927 break;
928 case 'assuredby15':
929 output_given_assurances(intval($_GET['userid']),1);
930 break;
931 }
932 }