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