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