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