c69ecb6f41707669889e72938a5cb8cb8390e36e
[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="<?=sanitizeHTML($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="<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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=<?=sanitizeHTML($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 while($drow = mysql_fetch_assoc($dres)) {
415 ?>
416 <tr>
417 <td class="DataTD"><?=_("Secondary Emails")?>:</td>
418 <td class="DataTD"><?=sanitizeHTML($drow['email'])?></td>
419 </tr>
420 <?
421 }
422 ?>
423 </table>
424 <br/>
425 <?
426 }
427
428 // list of domains
429 $dres=get_domains(intval($row['id']));
430 if(mysql_num_rows($dres) > 0) {
431 ?>
432 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
433 <tr>
434 <td colspan="5" class="title"><?=_("Verified Domains")?></td>
435 </tr>
436 <?
437 while($drow = mysql_fetch_assoc($dres)) {
438 ?>
439 <tr>
440 <td class="DataTD"><?=_("Domain")?>:</td>
441 <td class="DataTD"><?=sanitizeHTML($drow['domain'])?></td>
442 </tr>
443 <?
444 }
445 ?>
446 </table>
447 <br/>
448 <?
449 }
450 ?>
451 <? // Begin - Debug infos ?>
452 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
453 <tr>
454 <td colspan="2" class="title"><?=_("Account State")?></td>
455 </tr>
456
457 <?
458 // --- bug-975 begin ---
459 // potential db inconsistency like in a20110804.1
460 // Admin console -> don't list user account
461 // User login -> impossible
462 // Assurer, assure someone -> user displayed
463 /* regular user account search with regular settings
464
465 --- Admin Console find user query
466 $query = "select `users`.`id` as `id`, `email`.`email` as `email` from `users`,`email`
467 where `users`.`id`=`email`.`memid` and
468 (`email`.`email` like '$emailsearch' or `email`.`id`='$email' or `users`.`id`='$email') and
469 `email`.`hash`='' and `email`.`deleted`=0 and `users`.`deleted`=0
470 group by `users`.`id` limit 100";
471 => requirements
472 1. email.hash = ''
473 2. email.deleted = 0
474 3. users.deleted = 0
475 4. email.email = primary-email (???) or'd
476 not covered by admin console find user routine, but may block users login
477 5. users.verified = 0|1
478 further "special settings"
479 6. users.locked (setting displayed in display form)
480 7. users.assurer_blocked (setting displayed in display form)
481
482 --- User login user query
483 select * from `users` where `email`='$email' and (`password`=old_password('$pword') or `password`=sha1('$pword') or
484 `password`=password('$pword')) and `verified`=1 and `deleted`=0 and `locked`=0
485 => requirements
486 1. users.verified = 1
487 2. users.deleted = 0
488 3. users.locked = 0
489 4. users.email = primary-email
490
491 --- Assurer, assure someone find user query
492 select * from `users` where `email`='".mysql_real_escape_string(stripslashes($_POST['email']))."'
493 and `deleted`=0
494 => requirements
495 1. users.deleted = 0
496 2. users.email = primary-email
497
498 Admin User Assurer
499 bit Console Login assure someone
500
501 1. email.hash = '' Yes No No
502 2. email.deleted = 0 Yes No No
503 3. users.deleted = 0 Yes Yes Yes
504 4. users.verified = 1 No Yes No
505 5. users.locked = 0 No Yes No
506 6. users.email = prim-email No Yes Yes
507 7. email.email = prim-email Yes No No
508
509 full usable account needs all 7 requirements fulfilled
510 so if one setting isn't set/cleared there is an inconsistency either way
511 if eg email.email is not avail, admin console cannot open user info
512 but user can login and assurer can display user info
513 if user verified is not set to 1, admin console displays user record
514 but user cannot login, but assurer can search for the user and the data displays
515
516 consistency check:
517 1. search primary-email in users.email
518 2. search primary-email in email.email
519 3. userid = email.memid
520 4. check settings from table 1. - 5.
521
522 */
523
524 $inconsistency = 0;
525 $inconsistencydisp = "";
526 $inccause = "";
527
528 // current userid intval($row['id'])
529 $query = "select `email` as `uemail`, `deleted` as `udeleted`, `verified`, `locked`
530 from `users` where `id`='".intval($row['id'])."' ";
531 $dres = mysql_query($query);
532 $drow = mysql_fetch_assoc($dres);
533 $uemail = $drow['uemail'];
534 $udeleted = $drow['udeleted'];
535 $uverified = $drow['verified'];
536 $ulocked = $drow['locked'];
537
538 $query = "select `hash`, `email` as `eemail` from `email`
539 where `memid`='".intval($row['id'])."' and
540 `email` ='".$uemail."' and
541 `deleted` = 0";
542 $dres = mysql_query($query);
543 if ($drow = mysql_fetch_assoc($dres)) {
544 $drow['edeleted'] = 0;
545 } else {
546 // try if there are deleted entries
547 $query = "select `hash`, `deleted` as `edeleted`, `email` as `eemail` from `email`
548 where `memid`='".intval($row['id'])."' and
549 `email` ='".$uemail."'";
550 $dres = mysql_query($query);
551 $drow = mysql_fetch_assoc($dres);
552 }
553
554 if ($drow) {
555 $eemail = $drow['eemail'];
556 $edeleted = $drow['edeleted'];
557 $ehash = $drow['hash'];
558 if ($udeleted!=0) {
559 $inconsistency += 1;
560 $inccause .= (empty($inccause)?"":"<br>")._("Users record set to deleted");
561 }
562 if ($uverified!=1) {
563 $inconsistency += 2;
564 $inccause .= (empty($inccause)?"":"<br>")._("Users record verified not set");
565 }
566 if ($ulocked!=0) {
567 $inconsistency += 4;
568 $inccause .= (empty($inccause)?"":"<br>")._("Users record locked set");
569 }
570 if ($edeleted!=0) {
571 $inconsistency += 8;
572 $inccause .= (empty($inccause)?"":"<br>")._("Email record set deleted");
573 }
574 if ($ehash!='') {
575 $inconsistency += 16;
576 $inccause .= (empty($inccause)?"":"<br>")._("Email record hash not unset");
577 }
578 } else {
579 $inconsistency = 32;
580 $inccause = _("Prim. email, Email record doesn't exist");
581 }
582 if ($inconsistency>0) {
583 // $inconsistencydisp = _("Yes");
584 ?>
585 <tr>
586 <td class="DataTD"><?=_("Account inconsistency")?>:</td>
587 <td class="DataTD"><?=$inccause?><br>code: <?=$inconsistency?></td>
588 </tr>
589 <tr>
590 <td colspan="2" class="DataTD" style="max-width: 75ex;">
591 <?=_("Account inconsistency can cause problems in daily account operations and needs to be fixed manually through arbitration/critical team.")?>
592 </td>
593 </tr>
594 <?
595 }
596
597 // --- bug-975 end ---
598 ?>
599 </table>
600 <br />
601 <?
602 // End - Debug infos
603
604 // certificate overview
605 ?>
606
607 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
608 <tr>
609 <td colspan="6" class="title"><?=_("Certificates")?></td>
610 </tr>
611 <tr>
612 <td class="DataTD"><?=_("Cert Type")?>:</td>
613 <td class="DataTD"><?=_("Total")?></td>
614 <td class="DataTD"><?=_("Valid")?></td>
615 <td class="DataTD"><?=_("Expired")?></td>
616 <td class="DataTD"><?=_("Revoked")?></td>
617 <td class="DataTD"><?=_("Latest Expire")?></td>
618 </tr>
619 <!-- server certificates -->
620 <tr>
621 <td class="DataTD"><?=_("Server")?>:</td>
622 <?
623 $query = "
624 select COUNT(*) as `total`,
625 MAX(`domaincerts`.`expire`) as `maxexpire`
626 from `domains` inner join `domaincerts`
627 on `domains`.`id` = `domaincerts`.`domid`
628 where `domains`.`memid` = '".intval($row['id'])."'
629 ";
630 $dres = mysql_query($query);
631 $drow = mysql_fetch_assoc($dres);
632 $total = $drow['total'];
633
634 $maxexpire = "0000-00-00 00:00:00";
635 if ($drow['maxexpire']) {
636 $maxexpire = $drow['maxexpire'];
637 }
638
639 if($total > 0) {
640 $query = "
641 select COUNT(*) as `valid`
642 from `domains` inner join `domaincerts`
643 on `domains`.`id` = `domaincerts`.`domid`
644 where `domains`.`memid` = '".intval($row['id'])."'
645 and `revoked` = '0000-00-00 00:00:00'
646 and `expire` > NOW()
647 ";
648 $dres = mysql_query($query);
649 $drow = mysql_fetch_assoc($dres);
650 $valid = $drow['valid'];
651
652 $query = "
653 select COUNT(*) as `expired`
654 from `domains` inner join `domaincerts`
655 on `domains`.`id` = `domaincerts`.`domid`
656 where `domains`.`memid` = '".intval($row['id'])."'
657 and `expire` <= NOW()
658 ";
659 $dres = mysql_query($query);
660 $drow = mysql_fetch_assoc($dres);
661 $expired = $drow['expired'];
662
663 $query = "
664 select COUNT(*) as `revoked`
665 from `domains` inner join `domaincerts`
666 on `domains`.`id` = `domaincerts`.`domid`
667 where `domains`.`memid` = '".intval($row['id'])."'
668 and `revoked` != '0000-00-00 00:00:00'
669 ";
670 $dres = mysql_query($query);
671 $drow = mysql_fetch_assoc($dres);
672 $revoked = $drow['revoked'];
673 ?>
674 <td class="DataTD"><?=intval($total)?></td>
675 <td class="DataTD"><?=intval($valid)?></td>
676 <td class="DataTD"><?=intval($expired)?></td>
677 <td class="DataTD"><?=intval($revoked)?></td>
678 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
679 <?
680 } else { // $total > 0
681 ?>
682 <td colspan="5" class="DataTD"><?=_("None")?></td>
683 <?
684 }
685 ?>
686 </tr>
687 <!-- client certificates -->
688 <tr>
689 <td class="DataTD"><?=_("Client")?>:</td>
690 <?
691 $query = "
692 select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
693 from `emailcerts`
694 where `memid` = '".intval($row['id'])."'
695 ";
696 $dres = mysql_query($query);
697 $drow = mysql_fetch_assoc($dres);
698 $total = $drow['total'];
699
700 $maxexpire = "0000-00-00 00:00:00";
701 if ($drow['maxexpire']) {
702 $maxexpire = $drow['maxexpire'];
703 }
704
705 if($total > 0) {
706 $query = "
707 select COUNT(*) as `valid`
708 from `emailcerts`
709 where `memid` = '".intval($row['id'])."'
710 and `revoked` = '0000-00-00 00:00:00'
711 and `expire` > NOW()
712 ";
713 $dres = mysql_query($query);
714 $drow = mysql_fetch_assoc($dres);
715 $valid = $drow['valid'];
716
717 $query = "
718 select COUNT(*) as `expired`
719 from `emailcerts`
720 where `memid` = '".intval($row['id'])."'
721 and `expire` <= NOW()
722 ";
723 $dres = mysql_query($query);
724 $drow = mysql_fetch_assoc($dres);
725 $expired = $drow['expired'];
726
727 $query = "
728 select COUNT(*) as `revoked`
729 from `emailcerts`
730 where `memid` = '".intval($row['id'])."'
731 and `revoked` != '0000-00-00 00:00:00'
732 ";
733 $dres = mysql_query($query);
734 $drow = mysql_fetch_assoc($dres);
735 $revoked = $drow['revoked'];
736 ?>
737 <td class="DataTD"><?=intval($total)?></td>
738 <td class="DataTD"><?=intval($valid)?></td>
739 <td class="DataTD"><?=intval($expired)?></td>
740 <td class="DataTD"><?=intval($revoked)?></td>
741 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
742 <?
743 } else { // $total > 0
744 ?>
745 <td colspan="5" class="DataTD"><?=_("None")?></td>
746 <?
747 }
748 ?>
749 </tr>
750 <!-- gpg certificates -->
751 <tr>
752 <td class="DataTD"><?=_("GPG")?>:</td>
753 <?
754 $query = "
755 select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
756 from `gpg`
757 where `memid` = '".intval($row['id'])."'
758 ";
759 $dres = mysql_query($query);
760 $drow = mysql_fetch_assoc($dres);
761 $total = $drow['total'];
762
763 $maxexpire = "0000-00-00 00:00:00";
764 if ($drow['maxexpire']) {
765 $maxexpire = $drow['maxexpire'];
766 }
767
768 if($total > 0) {
769 $query = "
770 select COUNT(*) as `valid`
771 from `gpg`
772 where `memid` = '".intval($row['id'])."'
773 and `expire` > NOW()
774 ";
775 $dres = mysql_query($query);
776 $drow = mysql_fetch_assoc($dres);
777 $valid = $drow['valid'];
778
779 $query = "
780 select COUNT(*) as `expired`
781 from `gpg`
782 where `memid` = '".intval($row['id'])."'
783 and `expire` <= NOW()
784 ";
785 $dres = mysql_query($query);
786 $drow = mysql_fetch_assoc($dres);
787 $expired = $drow['expired'];
788 ?>
789 <td class="DataTD"><?=intval($total)?></td>
790 <td class="DataTD"><?=intval($valid)?></td>
791 <td class="DataTD"><?=intval($expired)?></td>
792 <td class="DataTD"></td>
793 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
794 <?
795 } else { // $total > 0
796 ?>
797 <td colspan="5" class="DataTD"><?=_("None")?></td>
798 <?
799 }
800 ?>
801 </tr>
802 <!-- org server certificates -->
803 <tr>
804 <td class="DataTD"><a href="account.php?id=58&amp;userid=<?=intval($row['id'])?>"><?=_("Org Server")?></a>:</td>
805 <?
806 $query = "
807 select COUNT(*) as `total`,
808 MAX(`orgcerts`.`expire`) as `maxexpire`
809 from `orgdomaincerts` as `orgcerts` inner join `org`
810 on `orgcerts`.`orgid` = `org`.`orgid`
811 where `org`.`memid` = '".intval($row['id'])."'
812 ";
813 $dres = mysql_query($query);
814 $drow = mysql_fetch_assoc($dres);
815 $total = $drow['total'];
816
817 $maxexpire = "0000-00-00 00:00:00";
818 if ($drow['maxexpire']) {
819 $maxexpire = $drow['maxexpire'];
820 }
821
822 if($total > 0) {
823 $query = "
824 select COUNT(*) as `valid`
825 from `orgdomaincerts` as `orgcerts` inner join `org`
826 on `orgcerts`.`orgid` = `org`.`orgid`
827 where `org`.`memid` = '".intval($row['id'])."'
828 and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
829 and `orgcerts`.`expire` > NOW()
830 ";
831 $dres = mysql_query($query);
832 $drow = mysql_fetch_assoc($dres);
833 $valid = $drow['valid'];
834
835 $query = "
836 select COUNT(*) as `expired`
837 from `orgdomaincerts` as `orgcerts` inner join `org`
838 on `orgcerts`.`orgid` = `org`.`orgid`
839 where `org`.`memid` = '".intval($row['id'])."'
840 and `orgcerts`.`expire` <= NOW()
841 ";
842 $dres = mysql_query($query);
843 $drow = mysql_fetch_assoc($dres);
844 $expired = $drow['expired'];
845
846 $query = "
847 select COUNT(*) as `revoked`
848 from `orgdomaincerts` as `orgcerts` inner join `org`
849 on `orgcerts`.`orgid` = `org`.`orgid`
850 where `org`.`memid` = '".intval($row['id'])."'
851 and `orgcerts`.`revoked` != '0000-00-00 00:00:00'
852 ";
853 $dres = mysql_query($query);
854 $drow = mysql_fetch_assoc($dres);
855 $revoked = $drow['revoked'];
856 ?>
857 <td class="DataTD"><?=intval($total)?></td>
858 <td class="DataTD"><?=intval($valid)?></td>
859 <td class="DataTD"><?=intval($expired)?></td>
860 <td class="DataTD"><?=intval($revoked)?></td>
861 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
862 <?
863 } else { // $total > 0
864 ?>
865 <td colspan="5" class="DataTD"><?=_("None")?></td>
866 <?
867 }
868 ?>
869 </tr>
870 <!-- org client certificates -->
871 <tr>
872 <td class="DataTD"><?=_("Org Client")?>:</td>
873 <?
874 $query = "
875 select COUNT(*) as `total`,
876 MAX(`orgcerts`.`expire`) as `maxexpire`
877 from `orgemailcerts` as `orgcerts` inner join `org`
878 on `orgcerts`.`orgid` = `org`.`orgid`
879 where `org`.`memid` = '".intval($row['id'])."'
880 ";
881 $dres = mysql_query($query);
882 $drow = mysql_fetch_assoc($dres);
883 $total = $drow['total'];
884
885 $maxexpire = "0000-00-00 00:00:00";
886 if ($drow['maxexpire']) {
887 $maxexpire = $drow['maxexpire'];
888 }
889
890 if($total > 0) {
891 $query = "
892 select COUNT(*) as `valid`
893 from `orgemailcerts` as `orgcerts` inner join `org`
894 on `orgcerts`.`orgid` = `org`.`orgid`
895 where `org`.`memid` = '".intval($row['id'])."'
896 and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
897 and `orgcerts`.`expire` > NOW()
898 ";
899 $dres = mysql_query($query);
900 $drow = mysql_fetch_assoc($dres);
901 $valid = $drow['valid'];
902
903 $query = "
904 select COUNT(*) as `expired`
905 from `orgemailcerts` as `orgcerts` inner join `org`
906 on `orgcerts`.`orgid` = `org`.`orgid`
907 where `org`.`memid` = '".intval($row['id'])."'
908 and `orgcerts`.`expire` <= NOW()
909 ";
910 $dres = mysql_query($query);
911 $drow = mysql_fetch_assoc($dres);
912 $expired = $drow['expired'];
913
914 $query = "
915 select COUNT(*) as `revoked`
916 from `orgemailcerts` as `orgcerts` inner join `org`
917 on `orgcerts`.`orgid` = `org`.`orgid`
918 where `org`.`memid` = '".intval($row['id'])."'
919 and `orgcerts`.`revoked` != '0000-00-00 00:00:00'
920 ";
921 $dres = mysql_query($query);
922 $drow = mysql_fetch_assoc($dres);
923 $revoked = $drow['revoked'];
924 ?>
925 <td class="DataTD"><?=intval($total)?></td>
926 <td class="DataTD"><?=intval($valid)?></td>
927 <td class="DataTD"><?=intval($expired)?></td>
928 <td class="DataTD"><?=intval($revoked)?></td>
929 <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
930 <?
931 } else { // $total > 0
932 ?>
933 <td colspan="5" class="DataTD"><?=_("None")?></td>
934 <?
935 }
936 ?>
937 </tr>
938 <tr>
939 <td colspan="6" class="title">
940 <form method="post" action="account.php" onSubmit="if(!confirm('<?=_("Are you sure you want to revoke all private certificates?")?>')) return false;">
941 <input type="hidden" name="action" value="revokecert">
942 <input type="hidden" name="oldid" value="43">
943 <input type="hidden" name="userid" value="<?=intval($userid)?>">
944 <input type="submit" value="<?=_('revoke certificates')?>">
945 <input type="hidden" name="ticketno" value="<?=sanitizeHTML($ticketno)?>"/>
946 </form>
947 </td>
948 </tr>
949 </table>
950 <br />
951 <? // list assurances ?>
952 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
953 <tr>
954 <td class="DataTD">
955 <a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Show Assurances the user got")?></a>
956 (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto15&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("New calculation")?></a>)
957 </td>
958 </tr>
959 <tr>
960 <td class="DataTD">
961 <a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredby&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Show Assurances the user gave")?></a>
962 (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredby15&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("New calculation")?></a>)
963 </td>
964 </tr>
965 </table>
966 <?
967 // if(array_key_exists('assuredto',$_GET) && $_GET['assuredto'] == "yes") {
968
969 function showassuredto($ticketno)
970 {
971 ?>
972 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
973 <tr>
974 <td colspan="8" class="title"><?=_("Assurance Points")?></td>
975 </tr>
976 <tr>
977 <td class="DataTD"><b><?=_("ID")?></b></td>
978 <td class="DataTD"><b><?=_("Date")?></b></td>
979 <td class="DataTD"><b><?=_("Who")?></b></td>
980 <td class="DataTD"><b><?=_("Email")?></b></td>
981 <td class="DataTD"><b><?=_("Points")?></b></td>
982 <td class="DataTD"><b><?=_("Location")?></b></td>
983 <td class="DataTD"><b><?=_("Method")?></b></td>
984 <td class="DataTD"><b><?=_("Revoke")?></b></td>
985 </tr>
986 <?
987 $query = "select * from `notary` where `to`='".intval($_GET['userid'])."' and `deleted` = 0";
988 $dres = mysql_query($query);
989 $points = 0;
990 while($drow = mysql_fetch_assoc($dres)) {
991 $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($drow['from'])."'"));
992 $points += $drow['points'];
993 ?>
994 <tr>
995 <td class="DataTD"><?=$drow['id']?></td>
996 <td class="DataTD"><?=sanitizeHTML($drow['date'])?></td>
997 <td class="DataTD"><a href="wot.php?id=9&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['fname'])." ".sanitizeHTML($fromuser['lname'])?></td>
998 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
999 <td class="DataTD"><?=intval($drow['points'])?></td>
1000 <td class="DataTD"><?=sanitizeHTML($drow['location'])?></td>
1001 <td class="DataTD"><?=sanitizeHTML($drow['method'])?></td>
1002 <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=<?=sanitizeHTML($ticketno)?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),$drow['id'])?>');"><?=_("Revoke")?></a></td>
1003 </tr>
1004 <?
1005 }
1006 ?>
1007 <tr>
1008 <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
1009 <td class="DataTD"><?=$points?></td>
1010 <td class="DataTD" colspan="3">&nbsp;</td>
1011 </tr>
1012 </table>
1013 <?
1014 }
1015
1016 function showassuredby($ticketno)
1017 {
1018 ?>
1019 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
1020 <tr>
1021 <td colspan="8" class="title"><?=_("Assurance Points The User Issued")?></td>
1022 </tr>
1023 <tr>
1024 <td class="DataTD"><b><?=_("ID")?></b></td>
1025 <td class="DataTD"><b><?=_("Date")?></b></td>
1026 <td class="DataTD"><b><?=_("Who")?></b></td>
1027 <td class="DataTD"><b><?=_("Email")?></b></td>
1028 <td class="DataTD"><b><?=_("Points")?></b></td>
1029 <td class="DataTD"><b><?=_("Location")?></b></td>
1030 <td class="DataTD"><b><?=_("Method")?></b></td>
1031 <td class="DataTD"><b><?=_("Revoke")?></b></td>
1032 </tr>
1033 <?
1034 $query = "select * from `notary` where `from`='".intval($_GET['userid'])."' and `deleted` = 0";
1035 $dres = mysql_query($query);
1036 $points = 0;
1037 while($drow = mysql_fetch_assoc($dres)) {
1038 $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".$drow['to']."'"));
1039 $points += $drow['points'];
1040 ?>
1041 <tr>
1042 <td class="DataTD"><?=$drow['id']?></td>
1043 <td class="DataTD"><?=$drow['date']?></td>
1044 <td class="DataTD"><a href="wot.php?id=9&userid=<?=$drow['to']?>"><?=$fromuser['fname']." ".$fromuser['lname']?></td>
1045 <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['to'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
1046 <td class="DataTD"><?=$drow['points']?></td>
1047 <td class="DataTD"><?=$drow['location']?></td>
1048 <td class="DataTD"><?=$drow['method']?></td>
1049 <td class="DataTD"><a href="account.php?id=43&userid=<?=$drow['from']?>&assurance=<?=$drow['id']?>&amp;csrf=<?=make_csrf('admdelassurance')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),$drow['id'])?>');"><?=_("Revoke")?></a></td>
1050 </tr>
1051 <?
1052 }
1053 ?>
1054 <tr>
1055 <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
1056 <td class="DataTD"><?=$points?></td>
1057 <td class="DataTD" colspan="3">&nbsp;</td>
1058 </tr>
1059 </table>
1060 <?} ?>
1061 <br/><br/>
1062 <?
1063 } }
1064
1065 if(isset($_GET['shownotary'])) {
1066 switch($_GET['shownotary']) {
1067 case 'assuredto':
1068 showassuredto($ticketno);
1069 break;
1070 case 'assuredby':
1071 showassuredby($ticketno);
1072 break;
1073 case 'assuredto15':
1074 output_received_assurances(intval($_GET['userid']),1,$ticketno);
1075 break;
1076 case 'assuredby15':
1077 output_given_assurances(intval($_GET['userid']),1, $ticketno);
1078 break;
1079 }
1080 }