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