bug 1138: added function get_domains
[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 </table>
314 <br/><?
315 //ticket number to track SE log ?>
316 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
317 <tr>
318 <td td colspan="5" class="title"><?=_("Ticket/Arbitration No, needs to be entered to apply any changes")?></td>
319 </tr>
320 <tr>
321 <td class="DataTD"><?=_('Ticket/Arbitration No')?></td>
322 <td class="DataTD"><input name="ticketno" /></td>
323 </tr>
324 </table>
325 <br/>
326 <?
327 //list secondary email addresses
328 // comment to be deleted before release
329 // $query = "select * from `email` where `memid`='".intval($row['id'])."' and `deleted`=0 and `hash`=''
330 // and `email`!='".mysql_escape_string($row['email'])."'";
331 // $dres = mysql_query($query);
332 $dres = get_email_address(intval($row['id']),mysql_real_escape_string($row['email']));
333 if(mysql_num_rows($dres) > 0) { ?>
334 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
335 <tr>
336 <td colspan="5" class="title"><?=_("Alternate Verified Email Addresses")?></td>
337 </tr><?
338 $rc = mysql_num_rows($dres);
339 while($drow = mysql_fetch_assoc($dres))
340 { ?>
341 <tr>
342 <td class="DataTD"><?=_("Secondary Emails")?>:</td>
343 <td class="DataTD"><?=sanitizeHTML($drow['email'])?></td>
344 </tr>
345 <? } ?>
346 </table>
347 <br><? } ?>
348 <?
349 // comment to be deleted before release
350 // $query = "select * from `domains` where `memid`='".intval($row['id'])."' and `deleted`=0 and `hash`=''";
351 // $dres = mysql_query($query);
352 $dres=get_domains(intval($row['id']));
353 if(mysql_num_rows($dres) > 0) { ?>
354 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
355 <tr>
356 <? // list of domains ?>
357 <td colspan="5" class="title"><?=_("Verified Domains")?></td>
358 </tr><?
359 $rc = mysql_num_rows($dres);
360 while($drow = mysql_fetch_assoc($dres))
361 { ?>
362 <tr>
363 <td class="DataTD"><?=_("Domain")?>:</td>
364 <td class="DataTD"><?=sanitizeHTML($drow['domain'])?></td>
365 </tr>
366 <? } ?>
367 </table>
368 <br>
369 <? } ?>
370 <? // Begin - Debug infos ?>
371 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
372 <tr>
373 <td colspan="2" class="title"><?=_("Account State")?></td>
374 </tr>
375
376 <?
377 // --- bug-975 begin ---
378 // potential db inconsistency like in a20110804.1
379 // Admin console -> don't list user account
380 // User login -> impossible
381 // Assurer, assure someone -> user displayed
382 /* regular user account search with regular settings
383
384 --- Admin Console find user query
385 $query = "select `users`.`id` as `id`, `email`.`email` as `email` from `users`,`email`
386 where `users`.`id`=`email`.`memid` and
387 (`email`.`email` like '$emailsearch' or `email`.`id`='$email' or `users`.`id`='$email') and
388 `email`.`hash`='' and `email`.`deleted`=0 and `users`.`deleted`=0
389 group by `users`.`id` limit 100";
390 => requirements
391 1. email.hash = ''
392 2. email.deleted = 0
393 3. users.deleted = 0
394 4. email.email = primary-email (???) or'd
395 not covered by admin console find user routine, but may block users login
396 5. users.verified = 0|1
397 further "special settings"
398 6. users.locked (setting displayed in display form)
399 7. users.assurer_blocked (setting displayed in display form)
400
401 --- User login user query
402 select * from `users` where `email`='$email' and (`password`=old_password('$pword') or `password`=sha1('$pword') or
403 `password`=password('$pword')) and `verified`=1 and `deleted`=0 and `locked`=0
404 => requirements
405 1. users.verified = 1
406 2. users.deleted = 0
407 3. users.locked = 0
408 4. users.email = primary-email
409
410 --- Assurer, assure someone find user query
411 select * from `users` where `email`='".mysql_escape_string(stripslashes($_POST['email']))."'
412 and `deleted`=0
413 => requirements
414 1. users.deleted = 0
415 2. users.email = primary-email
416 Admin User Assurer
417 bit Console Login assure someone
418
419 1. email.hash = '' Yes No No
420 2. email.deleted = 0 Yes No No
421 3. users.deleted = 0 Yes Yes Yes
422 4. users.verified = 1 No Yes No
423 5. users.locked = 0 No Yes No
424 6. users.email = prim-email No Yes Yes
425 7. email.email = prim-email Yes No No
426
427 full usable account needs all 7 requirements fulfilled
428 so if one setting isn't set/cleared there is an inconsistency either way
429 if eg email.email is not avail, admin console cannot open user info
430 but user can login and assurer can display user info
431 if user verified is not set to 1, admin console displays user record
432 but user cannot login, but assurer can search for the user and the data displays
433
434 consistency check:
435 1. search primary-email in users.email
436 2. search primary-email in email.email
437 3. userid = email.memid
438 4. check settings from table 1. - 5.
439
440 */
441
442 $inconsistency = 0;
443 $inconsistencydisp = "";
444 $inccause = "";
445 // current userid intval($row['id'])
446 $query = "select `email` as `uemail`, `deleted` as `udeleted`, `verified`, `locked`
447 from `users` where `id`='".intval($row['id'])."' ";
448 $dres = mysql_query($query);
449 $drow = mysql_fetch_assoc($dres);
450 $uemail = $drow['uemail'];
451 $udeleted = $drow['udeleted'];
452 $uverified = $drow['verified'];
453 $ulocked = $drow['locked'];
454
455 $query = "select `hash`, `email` as `eemail` from `email`
456 where `memid`='".intval($row['id'])."' and
457 `email` ='".$uemail."' and
458 `deleted` = 0";
459 $dres = mysql_query($query);
460 if ($drow = mysql_fetch_assoc($dres)) {
461 $drow['edeleted'] = 0;
462 } else {
463 // try if there are deleted entries
464 $query = "select `hash`, `deleted` as `edeleted`, `email` as `eemail` from `email`
465 where `memid`='".intval($row['id'])."' and
466 `email` ='".$uemail."'";
467 $dres = mysql_query($query);
468 $drow = mysql_fetch_assoc($dres);
469 }
470
471 if ($drow) {
472 $eemail = $drow['eemail'];
473 $edeleted = $drow['edeleted'];
474 $ehash = $drow['hash'];
475 if ($udeleted!=0) {
476 $inconsistency += 1;
477 $inccause .= (empty($inccause)?"":"<br>")._("Users record set to deleted");
478 }
479 if ($uverified!=1) {
480 $inconsistency += 2;
481 $inccause .= (empty($inccause)?"":"<br>")._("Users record verified not set");
482 }
483 if ($ulocked!=0) {
484 $inconsistency += 4;
485 $inccause .= (empty($inccause)?"":"<br>")._("Users record locked set");
486 }
487 if ($edeleted!=0) {
488 $inconsistency += 8;
489 $inccause .= (empty($inccause)?"":"<br>")._("Email record set deleted");
490 }
491 if ($ehash!='') {
492 $inconsistency += 16;
493 $inccause .= (empty($inccause)?"":"<br>")._("Email record hash not unset");
494 }
495 } else {
496 $inconsistency = 32;
497 $inccause = _("Prim. email, Email record doesn't exist");
498 }
499 if ($inconsistency>0) {
500 // $inconsistencydisp = _("Yes");
501 ?>
502 <tr>
503 <td class="DataTD"><?=_("Account inconsistency")?>:</td>
504 <td class="DataTD"><?=$inccause?><br>code: <?=$inconsistency?></td>
505 </tr>
506 <tr>
507 <td colspan="2" class="DataTD" style="max-width: 75ex">
508 <?=_("Account inconsistency can cause problems in daily account ".
509 "operations and needs to be fixed manually through arbitration/critical ".
510 "team.")?>
511 </td>
512 </tr>
513 <? }
514
515 // --- bug-975 end ---
516 ?>
517 </table>
518 <br>
519 <?
520 // End - Debug infos
521
522 // certificate overview
523 ?>
524
525 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
526 <tr>
527 <td colspan="6" class="title"><?=_("Certificates")?></td>
528 </tr>
529
530 <tr>
531 <td class="DataTD"><?=_("Cert Type")?>:</td>
532 <td class="DataTD"><?=_("Total")?></td>
533 <td class="DataTD"><?=_("Valid")?></td>
534 <td class="DataTD"><?=_("Expired")?></td>
535 <td class="DataTD"><?=_("Revoked")?></td>
536 <td class="DataTD"><?=_("Latest Expire")?></td>
537 </tr>
538
539 <tr>
540 <td class="DataTD"><?=_("Server")?>:</td>
541 <?
542 $query = "select COUNT(*) as `total`,
543 MAX(`domaincerts`.`expire`) as `maxexpire`
544 from `domains` inner join `domaincerts`
545 on `domains`.`id` = `domaincerts`.`domid`
546 where `domains`.`memid` = '".intval($row['id'])."' ";
547 $dres = mysql_query($query);
548 $drow = mysql_fetch_assoc($dres);
549 $total = $drow['total'];
550
551 $maxexpire = "0000-00-00 00:00:00";
552 if ($drow['maxexpire']) {
553 $maxexpire = $drow['maxexpire'];
554 }
555
556 if($total > 0) {
557 $query = "select COUNT(*) as `valid`
558 from `domains` inner join `domaincerts`
559 on `domains`.`id` = `domaincerts`.`domid`
560 where `domains`.`memid` = '".intval($row['id'])."'
561 and `revoked` = '0000-00-00 00:00:00'
562 and `expire` > NOW()";
563 $dres = mysql_query($query);
564 $drow = mysql_fetch_assoc($dres);
565 $valid = $drow['valid'];
566
567 $query = "select COUNT(*) as `expired`
568 from `domains` inner join `domaincerts`
569 on `domains`.`id` = `domaincerts`.`domid`
570 where `domains`.`memid` = '".intval($row['id'])."'
571 and `expire` <= NOW()";
572 $dres = mysql_query($query);
573 $drow = mysql_fetch_assoc($dres);
574 $expired = $drow['expired'];
575
576 $query = "select COUNT(*) as `revoked`
577 from `domains` inner join `domaincerts`
578 on `domains`.`id` = `domaincerts`.`domid`
579 where `domains`.`memid` = '".intval($row['id'])."'
580 and `revoked` != '0000-00-00 00:00:00'";
581 $dres = mysql_query($query);
582 $drow = mysql_fetch_assoc($dres);
583 $revoked = $drow['revoked'];
584 ?>
585 <td class="DataTD"><?=intval($total)?></td>
586 <td class="DataTD"><?=intval($valid)?></td>
587 <td class="DataTD"><?=intval($expired)?></td>
588 <td class="DataTD"><?=intval($revoked)?></td>
589 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
590 substr($maxexpire, 0, 10) : _("Pending")?></td>
591 <?
592 } else { // $total > 0
593 ?>
594 <td colspan="5" class="DataTD"><?=_("None")?></td>
595 <?
596 } ?>
597 </tr>
598
599 <tr>
600 <td class="DataTD"><?=_("Client")?>:</td>
601 <?
602 $query = "select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
603 from `emailcerts`
604 where `memid` = '".intval($row['id'])."' ";
605 $dres = mysql_query($query);
606 $drow = mysql_fetch_assoc($dres);
607 $total = $drow['total'];
608
609 $maxexpire = "0000-00-00 00:00:00";
610 if ($drow['maxexpire']) {
611 $maxexpire = $drow['maxexpire'];
612 }
613
614 if($total > 0) {
615 $query = "select COUNT(*) as `valid`
616 from `emailcerts`
617 where `memid` = '".intval($row['id'])."'
618 and `revoked` = '0000-00-00 00:00:00'
619 and `expire` > NOW()";
620 $dres = mysql_query($query);
621 $drow = mysql_fetch_assoc($dres);
622 $valid = $drow['valid'];
623
624 $query = "select COUNT(*) as `expired`
625 from `emailcerts`
626 where `memid` = '".intval($row['id'])."'
627 and `expire` <= NOW()";
628 $dres = mysql_query($query);
629 $drow = mysql_fetch_assoc($dres);
630 $expired = $drow['expired'];
631
632 $query = "select COUNT(*) as `revoked`
633 from `emailcerts`
634 where `memid` = '".intval($row['id'])."'
635 and `revoked` != '0000-00-00 00:00:00'";
636 $dres = mysql_query($query);
637 $drow = mysql_fetch_assoc($dres);
638 $revoked = $drow['revoked'];
639 ?>
640 <td class="DataTD"><?=intval($total)?></td>
641 <td class="DataTD"><?=intval($valid)?></td>
642 <td class="DataTD"><?=intval($expired)?></td>
643 <td class="DataTD"><?=intval($revoked)?></td>
644 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
645 substr($maxexpire, 0, 10) : _("Pending")?></td>
646 <?
647 } else { // $total > 0
648 ?>
649 <td colspan="5" class="DataTD"><?=_("None")?></td>
650 <?
651 } ?>
652 </tr>
653
654 <tr>
655 <td class="DataTD"><?=_("GPG")?>:</td>
656 <?
657 $query = "select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
658 from `gpg`
659 where `memid` = '".intval($row['id'])."' ";
660 $dres = mysql_query($query);
661 $drow = mysql_fetch_assoc($dres);
662 $total = $drow['total'];
663
664 $maxexpire = "0000-00-00 00:00:00";
665 if ($drow['maxexpire']) {
666 $maxexpire = $drow['maxexpire'];
667 }
668
669 if($total > 0) {
670 $query = "select COUNT(*) as `valid`
671 from `gpg`
672 where `memid` = '".intval($row['id'])."'
673 and `expire` > NOW()";
674 $dres = mysql_query($query);
675 $drow = mysql_fetch_assoc($dres);
676 $valid = $drow['valid'];
677
678 $query = "select COUNT(*) as `expired`
679 from `emailcerts`
680 where `memid` = '".intval($row['id'])."'
681 and `expire` <= NOW()";
682 $dres = mysql_query($query);
683 $drow = mysql_fetch_assoc($dres);
684 $expired = $drow['expired'];
685
686 ?>
687 <td class="DataTD"><?=intval($total)?></td>
688 <td class="DataTD"><?=intval($valid)?></td>
689 <td class="DataTD"><?=intval($expired)?></td>
690 <td class="DataTD"></td>
691 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
692 substr($maxexpire, 0, 10) : _("Pending")?></td>
693 <?
694 } else { // $total > 0
695 ?>
696 <td colspan="5" class="DataTD"><?=_("None")?></td>
697 <?
698 } ?>
699 </tr>
700
701 <tr>
702 <td class="DataTD"><a href="account.php?id=58&amp;userid=<?=intval($row['id'])?>"><?=_("Org Server")?></a>:</td>
703 <?
704 $query = "select COUNT(*) as `total`,
705 MAX(`orgcerts`.`expire`) as `maxexpire`
706 from `orgdomaincerts` as `orgcerts` inner join `org`
707 on `orgcerts`.`orgid` = `org`.`orgid`
708 where `org`.`memid` = '".intval($row['id'])."' ";
709 $dres = mysql_query($query);
710 $drow = mysql_fetch_assoc($dres);
711 $total = $drow['total'];
712
713 $maxexpire = "0000-00-00 00:00:00";
714 if ($drow['maxexpire']) {
715 $maxexpire = $drow['maxexpire'];
716 }
717
718 if($total > 0) {
719 $query = "select COUNT(*) as `valid`
720 from `orgdomaincerts` as `orgcerts` inner join `org`
721 on `orgcerts`.`orgid` = `org`.`orgid`
722 where `org`.`memid` = '".intval($row['id'])."'
723 and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
724 and `orgcerts`.`expire` > NOW()";
725 $dres = mysql_query($query);
726 $drow = mysql_fetch_assoc($dres);
727 $valid = $drow['valid'];
728
729 $query = "select COUNT(*) as `expired`
730 from `orgdomaincerts` as `orgcerts` inner join `org`
731 on `orgcerts`.`orgid` = `org`.`orgid`
732 where `org`.`memid` = '".intval($row['id'])."'
733 and `orgcerts`.`expire` <= NOW()";
734 $dres = mysql_query($query);
735 $drow = mysql_fetch_assoc($dres);
736 $expired = $drow['expired'];
737
738 $query = "select COUNT(*) as `revoked`
739 from `orgdomaincerts` as `orgcerts` inner join `org`
740 on `orgcerts`.`orgid` = `org`.`orgid`
741 where `org`.`memid` = '".intval($row['id'])."'
742 and `orgcerts`.`revoked` != '0000-00-00 00:00:00'";
743 $dres = mysql_query($query);
744 $drow = mysql_fetch_assoc($dres);
745 $revoked = $drow['revoked'];
746 ?>
747 <td class="DataTD"><?=intval($total)?></td>
748 <td class="DataTD"><?=intval($valid)?></td>
749 <td class="DataTD"><?=intval($expired)?></td>
750 <td class="DataTD"><?=intval($revoked)?></td>
751 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
752 substr($maxexpire, 0, 10) : _("Pending")?></td>
753 <?
754 } else { // $total > 0
755 ?>
756 <td colspan="5" class="DataTD"><?=_("None")?></td>
757 <?
758 } ?>
759 </tr>
760
761 <tr>
762 <td class="DataTD"><?=_("Org Client")?>:</td>
763 <?
764 $query = "select COUNT(*) as `total`,
765 MAX(`orgcerts`.`expire`) as `maxexpire`
766 from `orgemailcerts` as `orgcerts` inner join `org`
767 on `orgcerts`.`orgid` = `org`.`orgid`
768 where `org`.`memid` = '".intval($row['id'])."' ";
769 $dres = mysql_query($query);
770 $drow = mysql_fetch_assoc($dres);
771 $total = $drow['total'];
772
773 $maxexpire = "0000-00-00 00:00:00";
774 if ($drow['maxexpire']) {
775 $maxexpire = $drow['maxexpire'];
776 }
777
778 if($total > 0) {
779 $query = "select COUNT(*) as `valid`
780 from `orgemailcerts` as `orgcerts` inner join `org`
781 on `orgcerts`.`orgid` = `org`.`orgid`
782 where `org`.`memid` = '".intval($row['id'])."'
783 and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
784 and `orgcerts`.`expire` > NOW()";
785 $dres = mysql_query($query);
786 $drow = mysql_fetch_assoc($dres);
787 $valid = $drow['valid'];
788
789 $query = "select COUNT(*) as `expired`
790 from `orgemailcerts` as `orgcerts` inner join `org`
791 on `orgcerts`.`orgid` = `org`.`orgid`
792 where `org`.`memid` = '".intval($row['id'])."'
793 and `orgcerts`.`expire` <= NOW()";
794 $dres = mysql_query($query);
795 $drow = mysql_fetch_assoc($dres);
796 $expired = $drow['expired'];
797
798 $query = "select COUNT(*) as `revoked`
799 from `orgemailcerts` as `orgcerts` inner join `org`
800 on `orgcerts`.`orgid` = `org`.`orgid`
801 where `org`.`memid` = '".intval($row['id'])."'
802 and `orgcerts`.`revoked` != '0000-00-00 00:00:00'";
803 $dres = mysql_query($query);
804 $drow = mysql_fetch_assoc($dres);
805 $revoked = $drow['revoked'];
806 ?>
807 <td class="DataTD"><?=intval($total)?></td>
808 <td class="DataTD"><?=intval($valid)?></td>
809 <td class="DataTD"><?=intval($expired)?></td>
810 <td class="DataTD"><?=intval($revoked)?></td>
811 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
812 substr($maxexpire, 0, 10) : _("Pending")?></td>
813 <?
814 } else { // $total > 0
815 ?>
816 <td colspan="5" class="DataTD"><?=_("None")?></td>
817 <?
818 } ?>
819 </tr>
820 <tr>
821 <td colspan="6" class="title">
822 <form method="post" action="account.php" onSubmit="if(!confirm('<?=_("Are you sure you want to revoke all private certificates?")?>')) return false;">
823 <input type="hidden" name="action" value="revokecert">
824 <input type="hidden" name="oldid" value="43">
825 <input type="hidden" name="userid" value="<?=intval($userid)?>">
826 <input type="submit" value="<?=_('revoke certificates')?>">
827 </form>
828 </td>
829 </tr>
830 </table>
831 <br>
832 <? // list assurances ?>
833 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
834 <tr>
835 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto"><?=_("Show Assurances the user got")?></a>
836 (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto15"><?=_("New calculation")?></a>)</td>
837 </tr>
838 <tr>
839 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredby"><?=_("Show Assurances the user gave")?></a>
840 (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredby15"><?=_("New calculation")?></a>)</td>
841 </tr>
842 </table>
843 <?
844 // if(array_key_exists('assuredto',$_GET) && $_GET['assuredto'] == "yes") {
845
846 function showassuredto()
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")?></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 `to`='".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`='".intval($drow['from'])."'"));
870 $points += $drow['points'];
871 ?>
872 <tr>
873 <td class="DataTD"><?=$drow['id']?></td>
874 <td class="DataTD"><?=sanitizeHTML($drow['date'])?></td>
875 <td class="DataTD"><a href="wot.php?id=9&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['fname'])." ".sanitizeHTML($fromuser['lname'])?></td>
876 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
877 <td class="DataTD"><?=intval($drow['points'])?></td>
878 <td class="DataTD"><?=sanitizeHTML($drow['location'])?></td>
879 <td class="DataTD"><?=sanitizeHTML($drow['method'])?></td>
880 <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>
881 </tr>
882 <? } ?>
883 <tr>
884 <td class="DataTD" colspan="4"><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
891 <?
892 function showassuredby()
893 {
894 ?>
895 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
896 <tr>
897 <td colspan="8" class="title"><?=_("Assurance Points The User Issued")?></td>
898 </tr>
899 <tr>
900 <td class="DataTD"><b><?=_("ID")?></b></td>
901 <td class="DataTD"><b><?=_("Date")?></b></td>
902 <td class="DataTD"><b><?=_("Who")?></b></td>
903 <td class="DataTD"><b><?=_("Email")?></b></td>
904 <td class="DataTD"><b><?=_("Points")?></b></td>
905 <td class="DataTD"><b><?=_("Location")?></b></td>
906 <td class="DataTD"><b><?=_("Method")?></b></td>
907 <td class="DataTD"><b><?=_("Revoke")?></b></td>
908 </tr>
909 <?
910 $query = "select * from `notary` where `from`='".intval($_GET['userid'])."'";
911 $dres = mysql_query($query);
912 $points = 0;
913 while($drow = mysql_fetch_assoc($dres))
914 {
915 $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".$drow['to']."'"));
916 $points += $drow['points'];
917 ?>
918 <tr>
919 <td class="DataTD"><?=$drow['id']?></td>
920 <td class="DataTD"><?=$drow['date']?></td>
921 <td class="DataTD"><a href="wot.php?id=9&userid=<?=$drow['to']?>"><?=$fromuser['fname']." ".$fromuser['lname']?></td>
922 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['to'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
923 <td class="DataTD"><?=$drow['points']?></td>
924 <td class="DataTD"><?=$drow['location']?></td>
925 <td class="DataTD"><?=$drow['method']?></td>
926 <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>
927 </tr>
928 <? } ?>
929 <tr>
930 <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
931 <td class="DataTD"><?=$points?></td>
932 <td class="DataTD" colspan="3">&nbsp;</td>
933 </tr>
934 </table>
935 <? } ?>
936 <br><br>
937 <? } }
938
939 if(isset($_GET['shownotary'])) {
940 switch($_GET['shownotary']) {
941 case 'assuredto':
942 showassuredto();
943 break;
944 case 'assuredby':
945 showassuredby();
946 break;
947 case 'assuredto15':
948 output_received_assurances(intval($_GET['userid']),1);
949 break;
950 case 'assuredby15':
951 output_given_assurances(intval($_GET['userid']),1);
952 break;
953 }
954 }