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