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