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