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