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