836f0398dc199dda835e16fe443a04f1fdcf728f
[cacert-devel.git] / includes / notary.inc.php
1 <? /*
2 LibreSSL - CAcert web application
3 Copyright (C) 2004-2011 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 function query_init ($query)
20 {
21 return mysql_query($query);
22 }
23
24 function query_getnextrow ($res)
25 {
26 $row1 = mysql_fetch_assoc($res);
27 return $row1;
28 }
29
30 function query_get_number_of_rows ($resultset)
31 {
32 return intval(mysql_num_rows($resultset));
33 }
34
35 function get_number_of_assurances ($userid)
36 {
37 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
38 WHERE `method` = 'Face to Face Meeting' AND `deleted`=0 AND `from`='".intval($userid)."' ");
39 $row = query_getnextrow($res);
40
41 return intval($row['list']);
42 }
43
44 function get_number_of_ttpassurances ($userid)
45 {
46 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
47 WHERE (`method`='Trusted Third Parties' or `method`='TTP-Assisted') AND `deleted`=0 AND `to`='".intval($userid)."' ");
48 $row = query_getnextrow($res);
49
50 return intval($row['list']);
51 }
52
53 function get_number_of_assurees ($userid)
54 {
55 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
56 WHERE `method` = 'Face to Face Meeting' AND `deleted`=0 AND `to`='".intval($userid)."' ");
57 $row = query_getnextrow($res);
58
59 return intval($row['list']);
60 }
61
62 function get_top_assurer_position ($no_of_assurances)
63 {
64 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
65 WHERE `method` = 'Face to Face Meeting'
66 GROUP BY `from` HAVING count(*) > '".intval($no_of_assurances)."'");
67 return intval(query_get_number_of_rows($res)+1);
68 }
69
70 function get_top_assuree_position ($no_of_assurees)
71 {
72 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
73 WHERE `method` = 'Face to Face Meeting'
74 GROUP BY `to` HAVING count(*) > '".intval($no_of_assurees)."'");
75 return intval(query_get_number_of_rows($res)+1);
76 }
77
78 /**
79 * get_given_assurances()
80 * returns the list of assurances given by the user
81 * @param mixed $userid - user id for the account for report
82 * @param integer $log - for log output = 1
83 * @return
84 */
85 function get_given_assurances ($userid, $log=0)
86 {
87 $deleted='';
88 if ($log == 0) {
89 $deleted = ' and `deleted` = 0 ';
90 }
91 $res = query_init ("select * from `notary` where `from`='".intval($userid)."' and `from` != `to` $deleted order by `id` asc");
92 return $res;
93 }
94
95 /**
96 * get_received_assurances()
97 * returns the list of assurances received by the user
98 * @param mixed $userid - user id for the account for report
99 * @param integer $log - for log output = 1
100 * @return
101 */
102 function get_received_assurances ($userid, $log=0)
103 {
104 $deleted='';
105 if ($log == 0) {
106 $deleted = ' and `deleted` = 0 ';
107 }
108 $res = query_init ("select * from `notary` where `to`='".intval($userid)."' and `from` != `to` $deleted order by `id` asc ");
109 return $res;
110 }
111
112 function get_given_assurances_summary ($userid)
113 {
114 $res = query_init ("select count(*) as number,points,awarded,method from notary where `from`='".intval($userid)."' AND `deleted`=0 group by points,awarded,method");
115 return $res;
116 }
117
118 function get_received_assurances_summary ($userid)
119 {
120 $res = query_init ("select count(*) as number,points,awarded,method from notary where `to`='".intval($userid)."' AND `deleted`=0 group by points,awarded,method");
121 return $res;
122 }
123
124 function get_user ($userid)
125 {
126 $res = query_init ("select * from `users` where `id`='".intval($userid)."'");
127 return mysql_fetch_assoc($res);
128 }
129
130 function get_cats_state ($userid)
131 {
132
133 $res = query_init ("select * from `cats_passed` inner join `cats_variant` on `cats_passed`.`variant_id` = `cats_variant`.`id` and `cats_variant`.`type_id` = 1
134 WHERE `cats_passed`.`user_id` = '".intval($userid)."'");
135 return mysql_num_rows($res);
136 }
137
138 function calc_experience ($row,&$points,&$experience,&$sum_experience,&$revoked)
139 {
140 $apoints = max($row['points'], $row['awarded']);
141 $points += $apoints;
142 $experience = "&nbsp;";
143 $revoked = false; # to be coded later (after DB-upgrade)
144 if ($row['method'] == "Face to Face Meeting")
145 {
146 $sum_experience = $sum_experience +2;
147 $experience = "2";
148 }
149 return $apoints;
150 }
151
152 function calc_assurances ($row,&$points,&$experience,&$sumexperience,&$awarded,&$revoked)
153 {
154 $awarded = calc_points($row);
155 $revoked = false;
156
157 if ($awarded > 100)
158 {
159 $experience = $awarded - 100; // needs to be fixed in the future (limit 50 pts and/or no experience if pts > 100)
160 $awarded = 100;
161 }
162 else
163 $experience = 0;
164
165 switch ($row['method'])
166 {
167 case 'Thawte Points Transfer':
168 case 'CT Magazine - Germany':
169 case 'Temporary Increase': // Current usage of 'Temporary Increase' may break audit aspects, needs to be reimplemented
170 $awarded=sprintf("<strong style='color: red'>%s</strong>",_("Revoked"));
171 $experience=0;
172 $revoked=true;
173 break;
174 default:
175 $points += $awarded;
176 }
177 $sumexperience = $sumexperience + $experience;
178 }
179
180
181 function show_user_link ($name,$userid)
182 {
183 $name = trim($name);
184 if($name == "")
185 {
186 if ($userid == 0)
187 $name = _("System");
188 else
189 $name = _("Deleted account");
190 }
191 else
192 $name = "<a href='wot.php?id=9&amp;userid=".intval($userid)."'>".sanitizeHTML($name)."</a>";
193 return $name;
194 }
195
196 function show_email_link ($email,$userid)
197 {
198 $email = trim($email);
199 if($email != "")
200 $email = "<a href='account.php?id=43&amp;userid=".intval($userid)."'>".sanitizeHTML($email)."</a>";
201 return $email;
202 }
203
204 function get_assurer_ranking($userid,&$num_of_assurances,&$rank_of_assurer)
205 {
206 $num_of_assurances = get_number_of_assurances (intval($userid));
207 $rank_of_assurer = get_top_assurer_position($num_of_assurances);
208 }
209
210 function get_assuree_ranking($userid,&$num_of_assurees,&$rank_of_assuree)
211 {
212 $num_of_assurees = get_number_of_assurees (intval($userid));
213 $rank_of_assuree = get_top_assuree_position($num_of_assurees);
214 }
215
216
217 // ************* html table definitions ******************
218
219 function output_ranking($userid)
220 {
221 get_assurer_ranking($userid,$num_of_assurances,$rank_of_assurer);
222 get_assuree_ranking($userid,$num_of_assurees,$rank_of_assuree);
223
224 ?>
225 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
226 <tr>
227 <td class="title"><?=_("Assurer Ranking")?></td>
228 </tr>
229 <tr>
230 <td class="DataTD"><?=sprintf(_("You have made %s assurances which ranks you as the #%s top assurer."), intval($num_of_assurances), intval($rank_of_assurer) )?></td>
231 </tr>
232 <tr>
233 <td class="DataTD"><?=sprintf(_("You have received %s assurances which ranks you as the #%s top assuree."), intval($num_of_assurees), intval($rank_of_assuree) )?></td>
234 </tr>
235 </table>
236 <br/>
237 <?
238 }
239
240 function output_assurances_header($title,$support)
241 {
242 ?>
243 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
244 <tr>
245 <?
246 if ($support == "1")
247 {
248 ?>
249 <td colspan="10" class="title"><?=$title?></td>
250 <?
251 } else {
252 ?>
253 <td colspan="7" class="title"><?=$title?></td>
254 <?
255 }
256 ?>
257 </tr>
258 <tr>
259 <td class="DataTD"><strong><?=_("ID")?></strong></td>
260 <td class="DataTD"><strong><?=_("Date")?></strong></td>
261 <?
262 if ($support == "1")
263 {
264 ?>
265 <td class="DataTD"><strong><?=_("When")?></strong></td>
266 <td class="DataTD"><strong><?=_("Email")?></strong></td>
267 <?
268 }
269 ?>
270 <td class="DataTD"><strong><?=_("Who")?></strong></td>
271 <td class="DataTD"><strong><?=_("Points")?></strong></td>
272 <td class="DataTD"><strong><?=_("Location")?></strong></td>
273 <td class="DataTD"><strong><?=_("Method")?></strong></td>
274 <td class="DataTD"><strong><?=_("Experience Points")?></strong></td>
275 <?
276 if ($support == "1")
277 {
278 ?>
279 <td class="DataTD"><strong><?=_("Revoke")?></strong></td>
280 <?
281 }
282 ?>
283 </tr>
284 <?
285 }
286
287 function output_assurances_footer($points_txt,$points,$experience_txt,$sumexperience,$support)
288 {
289 ?>
290 <tr>
291 <td<?=($support == "1")?' colspan="5"':' colspan="3"'?> class="DataTD"><strong><?=$points_txt?>:</strong></td>
292 <td class="DataTD"><?=$points?></td>
293 <td class="DataTD">&nbsp;</td>
294 <td class="DataTD"><strong><?=$experience_txt?>:</strong></td>
295 <td class="DataTD"><?=$sumexperience?></td>
296 <?
297 if ($support == "1")
298 {
299 ?>
300 <td class="DataTD">&nbsp;</td>
301 <?
302 }
303 ?>
304
305 </tr>
306 </table>
307 <br/>
308 <?
309 }
310
311 function output_assurances_row($assuranceid,$date,$when,$email,$name,$awarded,$points,$location,$method,$experience,$userid,$support,$revoked, $ticketno)
312 {
313
314 $tdstyle="";
315 $emopen="";
316 $emclose="";
317
318 if ($awarded == $points)
319 {
320 if ($awarded == "0")
321 {
322 if ($when < "2006-09-01")
323 {
324 $tdstyle="style='background-color: #ffff80'";
325 $emopen="<em>";
326 $emclose="</em>";
327 }
328 }
329 }
330 ?>
331 <tr>
332 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$assuranceid?><?=$emclose?></td>
333 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$date?><?=$emclose?></td>
334 <?
335 if ($support == "1")
336 {
337 ?>
338 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$when?><?=$emclose?></td>
339 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$email?><?=$emclose?></td>
340 <?
341 }
342 ?>
343 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$name?><?=$emclose?></td>
344 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$awarded?><?=$emclose?></td>
345 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$location?><?=$emclose?></td>
346 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$method?><?=$emclose?></td>
347 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$experience?><?=$emclose?></td>
348 <?
349 if ($support == "1")
350 {
351 if ($revoked == true)
352 {
353 ?>
354 <td class="DataTD" <?=$tdstyle?>>&nbsp;</td>
355 <?
356 } else {
357 ?>
358 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><a href="account.php?id=43&amp;userid=<?=intval($userid)?>&amp;assurance=<?=intval($assuranceid)?>&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;?"),$assuranceid)?>');"><?=_("Revoke")?></a><?=$emclose?></td>
359 <?
360 }
361 }
362 ?>
363 </tr>
364 <?
365 }
366
367 function output_summary_header()
368 {
369 ?>
370 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
371 <tr>
372 <td colspan="4" class="title"><?=_("Summary of your Points")?></td>
373 </tr>
374 <tr>
375 <td class="DataTD"><strong><?=_("Description")?></strong></td>
376 <td class="DataTD"><strong><?=_("Points")?></strong></td>
377 <td class="DataTD"><strong><?=_("Countable Points")?></strong></td>
378 <td class="DataTD"><strong><?=_("Remark")?></strong></td>
379 </tr>
380 <?
381 }
382
383 function output_summary_footer()
384 {
385 ?>
386 </table>
387 <br/>
388 <?
389 }
390
391 function output_summary_row($title,$points,$points_countable,$remark)
392 {
393 ?>
394 <tr>
395 <td class="DataTD"><strong><?=$title?></strong></td>
396 <td class="DataTD"><?=$points?></td>
397 <td class="DataTD"><?=$points_countable?></td>
398 <td class="DataTD"><?=$remark?></td>
399 </tr>
400 <?
401 }
402
403
404 // ************* output given assurances ******************
405
406 function output_given_assurances_content($userid,&$points,&$sum_experience,$support, $ticketno)
407 {
408 $points = 0;
409 $sumexperience = 0;
410 $res = get_given_assurances(intval($userid));
411 while($row = mysql_fetch_assoc($res))
412 {
413 $fromuser = get_user (intval($row['to']));
414 $apoints = calc_experience ($row,$points,$experience,$sum_experience,$revoked);
415 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['to']));
416 $email = show_email_link ($fromuser['email'],intval($row['to']));
417 output_assurances_row (intval($row['id']),$row['date'],$row['when'],$email,$name,$apoints,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience,$userid,$support,$revoked, $ticketno);
418 }
419 }
420
421 // ************* output received assurances ******************
422
423 function output_received_assurances_content($userid,&$points,&$sum_experience,$support, $ticketno)
424 {
425 $points = 0;
426 $sumexperience = 0;
427 $res = get_received_assurances(intval($userid));
428 while($row = mysql_fetch_assoc($res))
429 {
430 $fromuser = get_user (intval($row['from']));
431 calc_assurances ($row,$points,$experience,$sum_experience,$awarded,$revoked);
432 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['from']));
433 $email = show_email_link ($fromuser['email'],intval($row['from']));
434 output_assurances_row (intval($row['id']),$row['date'],$row['when'],$email,$name,$awarded,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience,$userid,$support,$revoked, $ticketno);
435 }
436 }
437
438 // ************* output summary table ******************
439
440 function check_date_limit ($userid,$age)
441 {
442 $dob = date("Y-m-d", mktime(0,0,0,date("m"),date("d"),date("Y")-$age));
443 $res = query_init ("select id from `users` where `id`='".$userid."' and `dob` < '$dob'");
444 return intval(query_get_number_of_rows($res));
445 }
446
447 function calc_points($row)
448 {
449 $awarded = intval($row['awarded']);
450 if ($awarded == "")
451 $awarded = 0;
452 if (intval($row['points']) < $awarded)
453 $points = $awarded; // if 'sum of added points' > 100, awarded shows correct value
454 else
455 $points = intval($row['points']); // on very old assurances, awarded is '0' instead of correct value
456 switch ($row['method'])
457 {
458 case 'Thawte Points Transfer': // revoke all Thawte-points (as per arbitration)
459 case 'CT Magazine - Germany': // revoke c't (only one test-entry)
460 case 'Temporary Increase': // revoke 'temporary increase' (Current usage breaks audit aspects, needs to be reimplemented)
461 $points = 0;
462 break;
463 case 'Administrative Increase': // ignore AI with 2 points or less (historical for experiance points, now other calculation)
464 if ($points <= 2) // maybe limit to 35/50 pts in the future?
465 $points = 0;
466 break;
467 case 'Unknown': // to be revoked in the future? limit to max 50 pts?
468 case 'Trusted Third Parties': // to be revoked in the future? limit to max 35 pts?
469 case 'TTP-Assisted': // TTP assurances, limit to 35
470 case 'TOPUP': // TOPUP to be delevoped in the future, limit to 30
471 case '': // to be revoked in the future? limit to max 50 pts?
472 case 'Face to Face Meeting': // normal assurances, limit to 35/50 pts in the future?
473 break;
474 default: // should never happen ... ;-)
475 $points = 0;
476 }
477 if ($points < 0) // ignore negative points (bug needs to be fixed)
478 $points = 0;
479 return $points;
480 }
481
482 function max_points($userid)
483 {
484 return output_summary_content ($userid,0);
485 }
486
487 function output_summary_content($userid,$display_output)
488 {
489 $sum_points = 0;
490 $sum_experience = 0;
491 $sum_experience_other = 0;
492 $max_points = 100;
493 $max_experience = 50;
494
495 $experience_limit_reached_txt = _("Limit reached");
496
497 if (check_date_limit($userid,18) != 1)
498 {
499 $max_experience = 10;
500 $experience_limit_reached_txt = _("Limit given by PoJAM reached");
501 }
502 if (check_date_limit($userid,14) != 1)
503 {
504 $max_experience = 0;
505 $experience_limit_reached_txt = _("Limit given by PoJAM reached");
506 }
507
508 $res = get_received_assurances_summary($userid);
509 while($row = mysql_fetch_assoc($res))
510 {
511 $points = calc_points ($row);
512
513 if ($points > $max_points) // limit to 100 points, above is experience (needs to be fixed)
514 {
515 $sum_experience_other = $sum_experience_other+($points-$max_points)*intval($row['number']);
516 $points = $max_points;
517 }
518 $sum_points += $points*intval($row['number']);
519 }
520
521 $res = get_given_assurances_summary($userid);
522 while($row = mysql_fetch_assoc($res))
523 {
524 switch ($row['method'])
525 {
526 case 'Face to Face Meeting': // count Face to Face only
527 $sum_experience += 2*intval($row['number']);
528 break;
529 }
530
531 }
532
533 if ($sum_points > $max_points)
534 {
535 $sum_points_countable = $max_points;
536 $remark_points = _("Limit reached");
537 }
538 else
539 {
540 $sum_points_countable = $sum_points;
541 $remark_points = "&nbsp;";
542 }
543 if ($sum_experience > $max_experience)
544 {
545 $sum_experience_countable = $max_experience;
546 $remark_experience = $experience_limit_reached_txt;
547 }
548 else
549 {
550 $sum_experience_countable = $sum_experience;
551 $remark_experience = "&nbsp;";
552 }
553
554 if ($sum_experience_countable + $sum_experience_other > $max_experience)
555 {
556 $sum_experience_other_countable = $max_experience-$sum_experience_countable;
557 $remark_experience_other = $experience_limit_reached_txt;
558 }
559 else
560 {
561 $sum_experience_other_countable = $sum_experience_other;
562 $remark_experience_other = "&nbsp;";
563 }
564
565 if ($sum_points_countable < $max_points)
566 {
567 if ($sum_experience_countable != 0)
568 $remark_experience = _("Points on hold due to less assurance points");
569 $sum_experience_countable = 0;
570 if ($sum_experience_other_countable != 0)
571 $remark_experience_other = _("Points on hold due to less assurance points");
572 $sum_experience_other_countable = 0;
573 }
574
575 $issue_points = 0;
576 $cats_test_passed = get_cats_state ($userid);
577 if ($cats_test_passed == 0)
578 {
579 $issue_points_txt = "<strong style='color: red'>"._("You have to pass the CAcert Assurer Challenge (CATS-Test) to be an Assurer")."</strong>";
580 if ($sum_points_countable < $max_points)
581 {
582 $issue_points_txt = "<strong style='color: red'>";
583 $issue_points_txt .= sprintf(_("You need %s assurance points and the passed CATS-Test to be an Assurer"), intval($max_points));
584 $issue_points_txt .= "</strong>";
585 }
586 }
587 else
588 {
589 $experience_total = $sum_experience_countable+$sum_experience_other_countable;
590 $issue_points_txt = "";
591 if ($sum_points_countable == $max_points)
592 $issue_points = 10;
593 if ($experience_total >= 10)
594 $issue_points = 15;
595 if ($experience_total >= 20)
596 $issue_points = 20;
597 if ($experience_total >= 30)
598 $issue_points = 25;
599 if ($experience_total >= 40)
600 $issue_points = 30;
601 if ($experience_total >= 50)
602 $issue_points = 35;
603 if ($issue_points != 0)
604 $issue_points_txt = sprintf(_("You may issue up to %s points"),$issue_points);
605 }
606 if ($display_output)
607 {
608 output_summary_row (_("Assurance Points you received"),$sum_points,$sum_points_countable,$remark_points);
609 output_summary_row (_("Total Experience Points by Assurance"),$sum_experience,$sum_experience_countable,$remark_experience);
610 output_summary_row (_("Total Experience Points (other ways)"),$sum_experience_other,$sum_experience_other_countable,$remark_experience_other);
611 output_summary_row (_("Total Points"),"&nbsp;",$sum_points_countable + $sum_experience_countable + $sum_experience_other_countable,$issue_points_txt);
612 }
613 return $issue_points;
614 }
615
616 function output_given_assurances($userid, $support=0, $ticketno='')
617 {
618 output_assurances_header(_("Assurance Points You Issued"),$support);
619 output_given_assurances_content($userid,$points,$sum_experience,$support, $ticketno);
620 output_assurances_footer(_("Total Points Issued"),$points,_("Total Experience Points"),$sum_experience,$support);
621 }
622
623 function output_received_assurances($userid,$support=0, $ticketno='')
624 {
625 output_assurances_header(_("Your Assurance Points"),$support);
626 output_received_assurances_content($userid,$points,$sum_experience,$support, $ticketno);
627 output_assurances_footer(_("Total Assurance Points"),$points,_("Total Experience Points"),$sum_experience,$support);
628 }
629
630 function output_summary($userid)
631 {
632 output_summary_header();
633 output_summary_content($userid,1);
634 output_summary_footer();
635 }
636
637 function output_end_of_page()
638 {
639 ?>
640 <p>[ <a href='javascript:history.go(-1)'><?=_("Go Back")?></a> ]</p>
641 <?
642 }
643
644 //functions to do with recording user agreements
645 /**
646 * write_user_agreement()
647 * writes a new record to the table user_agreement
648 *
649 * @param mixed $memid
650 * @param mixed $document
651 * @param mixed $method
652 * @param mixed $comment
653 * @param integer $active
654 * @param integer $secmemid
655 * @return
656 */
657 function write_user_agreement($memid, $document, $method, $comment, $active=1, $secmemid=0){
658 // write a new record to the table user_agreement
659 $query="insert into `user_agreements` set `memid`=".intval($memid).", `secmemid`=".intval($secmemid).
660 ",`document`='".mysql_real_escape_string($document)."',`date`=NOW(), `active`=".intval($active).",`method`='".mysql_real_escape_string($method)."',`comment`='".mysql_real_escape_string($comment)."'" ;
661 $res = mysql_query($query);
662 }
663
664 /**
665 * get_user_agreement_status()
666 * returns 1 if the user has an entry for the given type in user_agreement, 0 if no entry is recorded
667 * @param mixed $memid
668 * @param string $type
669 * @return
670 */
671 function get_user_agreement_status($memid, $type="CCA"){
672 $query="SELECT u.`document` FROM `user_agreements` u
673 WHERE u.`document` = '" . mysql_real_escape_string($type) . "' AND u.`memid`=" . intval($memid) ;
674 $res = mysql_query($query);
675 if(mysql_num_rows($res) <=0){
676 return 0;
677 }else{
678 return 1;
679 }
680 }
681
682 /**
683 * get_first_user_agreement()
684 * returns the first user_agreement entry of the requested type depending on thes status of active of a given user
685 * @param mixed $memid
686 * @param integer $active, 0 - passive, 1 -active
687 * @param string $type
688 * @return
689 */
690 function get_first_user_agreement($memid, $active=1, $type="CCA"){
691 //returns an array (`document`,`date`,`method`, `comment`,`active`)
692 $query="SELECT u.`document`, u.`date`, u.`method`, u.`comment`, u.`active` FROM `user_agreements` AS u
693 WHERE u.`document` = '" . mysql_real_escape_string($type) . "' AND u.`memid`=" . intval($memid) . " AND u.`active`=" . intval($active) .
694 " ORDER BY u.`date` Limit 1;";
695 $res = mysql_query($query);
696 if(mysql_num_rows($res) >0){
697 $rec = mysql_fetch_assoc($res);
698 }else{
699 $rec=array();
700 }
701 return $rec;
702 }
703
704 /**
705 * get_last_user_agreement()
706 * returns the last user_agreement entry of a given type and of a given user
707 * @param mixed $memid
708 * @param string $type
709 * @return
710 */
711 function get_last_user_agreement($memid, $type="CCA"){
712 //returns an array (`document`,`date`,`method`, `comment`,`active`)
713 $query="SELECT u.`document`, u.`date`, u.`method`, u.`comment`, u.`active` FROM user_agreements u WHERE u.`document` = '" . mysql_real_escape_string($type) . "' AND (u.`memid`=" . intval($memid) . " ) order by `date` desc limit 1 " ;
714 $res = mysql_query($query);
715 if(mysql_num_rows($res) >0){
716 $rec = mysql_fetch_assoc($res);
717 }else{
718 $rec=array();
719 }
720 return $rec;
721 }
722
723 function get_user_agreement($memid){
724 $query="(SELECT u.`document`, u.`date`, u.`method`, u.`comment`, 1 as `active` FROM user_agreements u WHERE u.`document` = 'CCA' AND (u.`memid`=".$memid." ) order by u.`date` )
725 union
726 (SELECT u.`document`, u.`date`, u.`method`, u.`comment`, 0 as `active` FROM user_agreements u WHERE u.`document` = 'CCA' AND ( u.`secmemid`=".$memid.") order by u.`date`)
727 union
728 (SELECT u.`document`, u.`date`, u.`method`, u.`comment`, 0 as `active` FROM user_agreements u WHERE u.`document` != 'CCA' AND ( u.`memid`=".$memid.") order by u.u.`document`, u.`date`) " ;
729 $res = mysql_query($query);
730
731 return mysql_query($query);
732 }
733
734 /**
735 * delete_user_agreement()
736 * deletes all entries for a given type from user_agreement of a given user, if type is not given all
737 * @param mixed $memid
738 * @param string $type
739 * @return
740 */
741 function delete_user_agreement($memid, $type=false){
742 if ($type === false) {
743 $filter = '';
744 } else {
745 $filter = " and `document` = '" . mysql_real_escape_string($type) . "'";
746 }
747 mysql_query("delete from `user_agreements` where `memid`=" . intval($memid) . $filter );
748 }
749
750 // functions for 6.php (assure somebody)
751
752 function AssureHead($confirmation,$checkname)
753 {
754 ?>
755 <form method="post" action="wot.php">
756 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper" width="600">
757 <tr>
758 <td colspan="2" class="title"><?=$confirmation?></td>
759 </tr>
760 <tr>
761 <td class="DataTD" colspan="2" align="left"><?=$checkname?></td>
762 </tr>
763 <?
764 }
765
766 function AssureTextLine($field1,$field2)
767 {
768 ?>
769 <tr>
770 <td class="DataTD"><?=$field1.(empty($field1)?'':':')?></td>
771 <td class="DataTD"><?=$field2?></td>
772 </tr>
773 <?
774 }
775
776 function AssureBoxLine($type,$text,$checked)
777 {
778 ?>
779 <tr>
780 <td class="DataTD"><input type="checkbox" name="<?=$type?>" value="1" <?=$checked?"checked":""?>></td>
781 <td class="DataTD"><?=$text?></td>
782 </tr>
783 <?
784 }
785
786 function AssureMethodLine($text,$methods,$remark)
787 {
788 if (count($methods) != 1) {
789 ?>
790 <tr>
791 <td class="DataTD"><?=$text.(empty($text)?'':':')?></td>
792 <td class="DataTD">
793 <select name="method">
794 <?
795 foreach($methods as $val) {
796 ?>
797 <option value="<?=$val?>"><?=$val?></option>
798 <?
799 }
800 ?>
801 </select>
802 <br />
803 <?=$remark?>
804 </td>
805 </tr>
806 <?
807 } else {
808 ?>
809 <input type="hidden" name="<?=$val?>" value="<?=$methods[0]?>" />
810 <?
811 }
812 }
813
814 function AssureInboxLine($type,$field,$value,$description)
815 {
816 ?>
817 <tr>
818 <td class="DataTD"><?=$field.(empty($field)?'':':')?></td>
819 <td class="DataTD"><input type="text" name="<?=$type?>" value="<?=$value?>"><?=$description?></td>
820 </tr>
821 <?
822 }
823
824 function AssureFoot($oldid,$confirm)
825 {
826 ?>
827 <tr>
828 <td class="DataTD" colspan="2">
829 <input type="submit" name="process" value="<?=$confirm?>" />
830 <input type="submit" name="cancel" value="<?=_("Cancel")?>" />
831 </td>
832 </tr>
833 </table>
834 <input type="hidden" name="pagehash" value="<?=$_SESSION['_config']['wothash']?>" />
835 <input type="hidden" name="oldid" value="<?=$oldid?>" />
836 </form>
837 <?
838 }
839
840 function account_email_delete($mailid){
841 //deletes an email entry from an acount
842 //revolkes all certifcates for that email address
843 //called from www/account.php if($process != "" && $oldid == 2)
844 //called from www/diputes.php if($type == "reallyemail") / if($action == "accept")
845 //called from account_delete
846 $mailid = intval($mailid);
847 revoke_all_client_cert($mailid);
848 $query = "update `email` set `deleted`=NOW() where `id`='$mailid'";
849 mysql_query($query);
850 }
851
852 function account_domain_delete($domainid){
853 //deletes an domain entry from an acount
854 //revolkes all certifcates for that domain address
855 //called from www/account.php if($process != "" && $oldid == 9)
856 //called from www/diputes.php if($type == "reallydomain") / if($action == "accept")
857 //called from account_delete
858 $domainid = intval($domainid);
859 revoke_all_server_cert($domainid);
860 mysql_query(
861 "update `domains`
862 set `deleted`=NOW()
863 where `id` = '$domainid'");
864 }
865
866 function account_delete($id, $arbno, $adminid){
867 //deletes an account following the deleted account routnie V3
868 // called from www/account.php if($oldid == 50 && $process != "")
869 //change password
870 $id = intval($id);
871 $arbno = mysql_real_escape_string($arbno);
872 $adminid = intval($adminid);
873 $pool = 'abcdefghijklmnopqrstuvwxyz';
874 $pool .= '0123456789!()ยง';
875 $pool .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
876 srand ((double)microtime()*1000000);
877 $password="";
878 for($index = 0; $index < 30; $index++)
879 {
880 $password .= substr($pool,(rand()%(strlen ($pool))), 1);
881 }
882 mysql_query("update `users` set `password`=sha1('".$password."') where `id`='".$id."'");
883
884 //create new mail for arbitration number
885 $query = "insert into `email` set `email`='".$arbno."@cacert.org',`memid`='".$id."',`created`=NOW(),`modified`=NOW(), `attempts`=-1";
886 mysql_query($query);
887 $emailid = mysql_insert_id();
888
889 //set new mail as default
890 $query = "update `users` set `email`='".$arbno."@cacert.org' where `id`='".$id."'";
891 mysql_query($query);
892
893 //delete all other email address
894 $query = "select `id` from `email` where `memid`='".$id."' and `id`!='".$emailid."'" ;
895 $res=mysql_query($query);
896 while($row = mysql_fetch_assoc($res)){
897 account_email_delete($row['id']);
898 }
899
900 //delete all domains
901 $query = "select `id` from `domains` where `memid`='".$id."'";
902 $res=mysql_query($query);
903 while($row = mysql_fetch_assoc($res)){
904 account_domain_delete($row['id']);
905 }
906
907 //clear alert settings
908 mysql_query(
909 "update `alerts` set
910 `general`='0',
911 `country`='0',
912 `regional`='0',
913 `radius`='0'
914 where `memid`='$id'");
915
916 //set default location
917 $query = "update `users` set `locid`='2256755', `regid`='243', `ccid`='12' where `id`='".$id."'";
918 mysql_query($query);
919
920 //clear listings
921 $query = "update `users` set `listme`=' ',`contactinfo`=' ' where `id`='".$id."'";
922 mysql_query($query);
923
924 //set lanuage to default
925 //set default language
926 mysql_query("update `users` set `language`='en_AU' where `id`='".$id."'");
927 //delete secondary langugaes
928 mysql_query("delete from `addlang` where `userid`='".$id."'");
929
930 //change secret questions
931 for($i=1;$i<=5;$i++){
932 $q="";
933 $a="";
934 for($index = 0; $index < 30; $index++)
935 {
936 $q .= substr($pool,(rand()%(strlen ($pool))), 1);
937 $a .= substr($pool,(rand()%(strlen ($pool))), 1);
938 }
939 $query = "update `users` set `Q$i`='$q', `A$i`='$a' where `id`='".$id."'";
940 mysql_query($query);
941 }
942
943 //change personal information to arbitration number and DOB=1900-01-01
944 $query = "select `fname`,`mname`,`lname`,`suffix`,`dob` from `users` where `id`='$userid'";
945 $details = mysql_fetch_assoc(mysql_query($query));
946 $query = "insert into `adminlog` set `when`=NOW(),`old-lname`='${details['lname']}',`old-dob`='${details['dob']}',
947 `new-lname`='$arbno',`new-dob`='1900-01-01',`uid`='$id',`adminid`='".$adminid."'";
948 mysql_query($query);
949 $query = "update `users` set `fname`='".$arbno."',
950 `mname`='".$arbno."',
951 `lname`='".$arbno."',
952 `suffix`='".$arbno."',
953 `dob`='1900-01-01'
954 where `id`='".$id."'";
955 mysql_query($query);
956
957 //clear all admin and board flags
958 mysql_query(
959 "update `users` set
960 `assurer`='0',
961 `assurer_blocked`='0',
962 `codesign`='0',
963 `orgadmin`='0',
964 `ttpadmin`='0',
965 `locadmin`='0',
966 `admin`='0',
967 `adadmin`='0',
968 `tverify`='0',
969 `board`='0'
970 where `id`='$id'");
971
972 //block account
973 mysql_query("update `users` set `locked`='1' where `id`='$id'"); //, `deleted`=Now()
974 }
975
976
977 function check_email_exists($email){
978 // called from includes/account.php if($process != "" && $oldid == 1)
979 // called from includes/account.php if($oldid == 50 && $process != "")
980 $email = mysql_real_escape_string($email);
981 $query = "select 1 from `email` where `email`='$email' and `deleted`=0";
982 $res = mysql_query($query);
983 return mysql_num_rows($res) > 0;
984 }
985
986 function check_gpg_cert_running($uid,$cca=0){
987 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
988 // called from includes/account.php if($oldid == 50 && $process != "")
989 $uid = intval($uid);
990 if (0==$cca) {
991 $query = "select 1 from `gpg` where `memid`='$uid' and `expire`>NOW()";
992 }else{
993 $query = "select 1 from `gpg` where `memid`='$uid' and `expire`>(NOW()-90*86400)";
994 }
995 $res = mysql_query($query);
996 return mysql_num_rows($res) > 0;
997 }
998
999 function check_client_cert_running($uid,$cca=0){
1000 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
1001 // called from includes/account.php if($oldid == 50 && $process != "")
1002 $uid = intval($uid);
1003 if (0==$cca) {
1004 $query1 = "select 1 from `emailcerts` where `memid`='$uid' and `expire`>NOW() and `revoked`<`created`";
1005 $query2 = "select 1 from `emailcerts` where `memid`='$uid' and `revoked`>NOW()";
1006 }else{
1007 $query1 = "select 1 from `emailcerts` where `memid`='$uid' and `expire`>(NOW()-90*86400) and `revoked`<`created`";
1008 $query2 = "select 1 from `emailcerts` where `memid`='$uid' and `revoked`>(NOW()-90*86400)";
1009 }
1010 $res = mysql_query($query1);
1011 $r1 = mysql_num_rows($res)>0;
1012 $res = mysql_query($query2);
1013 $r2 = mysql_num_rows($res)>0;
1014 return !!($r1 || $r2);
1015 }
1016
1017 function check_server_cert_running($uid,$cca=0){
1018 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
1019 // called from includes/account.php if($oldid == 50 && $process != "")
1020 $uid = intval($uid);
1021 if (0==$cca) {
1022 $query1 = "
1023 select 1 from `domaincerts` join `domains`
1024 on `domaincerts`.`domid` = `domains`.`id`
1025 where `domains`.`memid` = '$uid'
1026 and `domaincerts`.`expire` > NOW()
1027 and `domaincerts`.`revoked` < `domaincerts`.`created`";
1028 $query2 = "
1029 select 1 from `domaincerts` join `domains`
1030 on `domaincerts`.`domid` = `domains`.`id`
1031 where `domains`.`memid` = '$uid'
1032 and `revoked`>NOW()";
1033 }else{
1034 $query1 = "
1035 select 1 from `domaincerts` join `domains`
1036 on `domaincerts`.`domid` = `domains`.`id`
1037 where `domains`.`memid` = '$uid'
1038 and `expire`>(NOW()-90*86400)
1039 and `revoked`<`created`";
1040 $query2 = "
1041 select 1 from `domaincerts` join `domains`
1042 on `domaincerts`.`domid` = `domains`.`id`
1043 where `domains`.`memid` = '$uid'
1044 and `revoked`>(NOW()-90*86400)";
1045 }
1046 $res = mysql_query($query1);
1047 $r1 = mysql_num_rows($res)>0;
1048 $res = mysql_query($query2);
1049 $r2 = mysql_num_rows($res)>0;
1050 return !!($r1 || $r2);
1051 }
1052
1053 function check_is_orgadmin($uid){
1054 // called from includes/account.php if($oldid == 50 && $process != "")
1055 $uid = intval($uid);
1056 $query = "select 1 from `org` where `memid`='$uid' and `deleted`=0";
1057 $res = mysql_query($query);
1058 return mysql_num_rows($res) > 0;
1059 }
1060
1061
1062 // revokation of certificates
1063 function revoke_all_client_cert($mailid){
1064 //revokes all client certificates for an email address
1065 $mailid = intval($mailid);
1066 $query = "select `emailcerts`.`id`
1067 from `emaillink`,`emailcerts` where
1068 `emaillink`.`emailid`='$mailid' and `emaillink`.`emailcertsid`=`emailcerts`.`id` and `emailcerts`.`revoked`=0
1069 group by `emailcerts`.`id`";
1070 $dres = mysql_query($query);
1071 while($drow = mysql_fetch_assoc($dres)){
1072 mysql_query("update `emailcerts` set `revoked`='1970-01-01 10:00:01', `disablelogin`=1 where `id`='".$drow['id']."'");
1073 }
1074 }
1075
1076 function revoke_all_server_cert($domainid){
1077 //revokes all server certs for an domain
1078 $domainid = intval($domainid);
1079 $query =
1080 "select `domaincerts`.`id`
1081 from `domaincerts`
1082 where `domaincerts`.`domid` = '$domainid'
1083 union distinct
1084 select `domaincerts`.`id`
1085 from `domaincerts`, `domlink`
1086 where `domaincerts`.`id` = `domlink`.`certid`
1087 and `domlink`.`domid` = '$domainid'";
1088 $dres = mysql_query($query);
1089 while($drow = mysql_fetch_assoc($dres))
1090 {
1091 mysql_query(
1092 "update `domaincerts`
1093 set `revoked`='1970-01-01 10:00:01'
1094 where `id` = '".$drow['id']."'
1095 and `revoked` = 0");
1096 }
1097 }
1098
1099 function revoke_all_private_cert($uid){
1100 //revokes all certificates linked to a personal accounts
1101 //gpg revokation needs to be added to a later point
1102 $uid=intval($uid);
1103 $query = "select `id` from `email` where `memid`='".$uid."'";
1104 $res=mysql_query($query);
1105 while($row = mysql_fetch_assoc($res)){
1106 revoke_all_client_cert($row['id']);
1107 }
1108
1109
1110 $query = "select `id` from `domains` where `memid`='".$uid."'";
1111 $res=mysql_query($query);
1112 while($row = mysql_fetch_assoc($res)){
1113 revoke_all_server_cert($row['id']);
1114 }
1115 }
1116
1117 /**
1118 * check_date_format()
1119 * checks if the date is entered in the right date format YYYY-MM-DD and
1120 * if the date is after the 1st January of the given year
1121 *
1122 * @param mixed $date
1123 * @param integer $year
1124 * @return
1125 */
1126 function check_date_format($date, $year=2000){
1127 if (!strpos($date,'-')) {
1128 return FALSE;
1129 }
1130 $arr=explode('-',$date);
1131
1132 if ((count($arr)!=3)) {
1133 return FALSE;
1134 }
1135 if (intval($arr[0])<=$year) {
1136 return FALSE;
1137 }
1138 if (intval($arr[1])>12 or intval($arr[1])<=0) {
1139 return FALSE;
1140 }
1141 if (intval($arr[2])>31 or intval($arr[2])<=0) {
1142 return FALSE;
1143 }
1144
1145 return checkdate( intval($arr[1]), intval($arr[2]), intval($arr[0]));
1146
1147 }
1148
1149 /**
1150 * check_date_difference()
1151 * returns false if the date is larger then today + time diffrence
1152 *
1153 * @param mixed $date
1154 * @param integer $diff
1155 * @return
1156 */
1157 function check_date_difference($date, $diff=1){
1158 return (strtotime($date)<=time()+$diff*86400);
1159 }
1160
1161 /**
1162 * write_se_log()
1163 * writes an information to the adminlog
1164 *
1165 * @param mixed $uid - id of the user account
1166 * @param mixed $adminid - id of the admin
1167 * @param mixed $type - what was changed
1168 * @param mixed $info - the ticket / arbitration no or other information
1169 * @return
1170 */
1171 function write_se_log($uid, $adminid, $type, $info){
1172 //records all support engineer actions changing a user account
1173 $uid = intval($uid);
1174 $adminid = intval($adminid);
1175 $type = mysql_real_escape_string($type);
1176 $info = mysql_real_escape_string($info);
1177 $query="insert into `adminlog` (`when`, `uid`, `adminid`,`type`,`information`) values
1178 (Now(), $uid, $adminid, '$type', '$info')";
1179 mysql_query($query);
1180 }
1181
1182 /**
1183 * valid_ticket_number()
1184 * checks if the entered information is a valid ticket or arbitration number
1185 * @param mixed $ticketno
1186 * @return
1187 */
1188 function valid_ticket_number($ticketno){
1189 //return if a given ticket number is valid
1190 //a arbitration case
1191 //d dispute action
1192 //s support case
1193 //m board motion
1194 $pattern='/[adsmADSM]\d{8}\./';
1195 if (preg_match($pattern, $ticketno)) {
1196 return true;
1197 }
1198 return false;
1199 }
1200
1201 // function for handling account/43.php
1202 /**
1203 * get_user_data()
1204 * returns all data of to an account given by the id
1205 * @param mixed $userid - account id
1206 * @param mixed $deleted - states if deleted data should be visible , default = 0 - not visible
1207 * @return
1208 */
1209 function get_user_data($userid, $deleted=0){
1210 $userid = intval($userid);
1211 $filter='';
1212 if (0==$deleted) {
1213 $filter=' and `users`.`deleted`=0';
1214 }
1215 $query = "select * from `users` where `users`.`id`='$userid' ".$filter;
1216 return mysql_query($query);
1217 }
1218
1219 /**
1220 * get_alerts()
1221 * retrns all alert settings for one user
1222 * @param mixed $userid for the requested account
1223 * @return
1224 */
1225 function get_alerts($userid){
1226 return mysql_fetch_assoc(mysql_query("select * from `alerts` where `memid`='".intval($userid)."'"));
1227 }
1228
1229 /**
1230 * get_email_address()
1231 * returns all email address linked to one account
1232 * @param mixed $userid
1233 * @param string $primary if given the primary email address is not retirned
1234 * @param integer $deleted - states if deleted data should be visible , default = 0 - not visible
1235 * @return
1236 */
1237 function get_email_address($userid, $primary,$deleted=0){
1238 //should be entered in account/2.php
1239 $userid = intval($userid);
1240 $filter='';
1241 if (0==$deleted) {
1242 $filter=' and `deleted`=0';
1243 }
1244 if ($primary) {
1245 $filter= $filter." and `email`!='".mysql_real_escape_string($primary)."'";
1246 }
1247 $query = "select * from `email` where `memid`='".$userid."'".$filter." order by `created`";
1248 return mysql_query($query);
1249 }
1250
1251 /**
1252 * get_domains()
1253 * returns all domains to an account
1254 * @param mixed $userid
1255 * @param integer $deleted - states if deleted data should be visible , default = 0 - not visible
1256 * @return
1257 */
1258 function get_domains($userid, $deleted=0){
1259 //should be entered in account/9.php
1260 $userid = intval($userid);
1261 $filter='';
1262 if (0==$deleted) {
1263 $filter=' and `deleted`=0';
1264 }
1265 $query = "select * from `domains` where `memid`='".$userid."' and `hash`=''".$filter." order by `created`";
1266 return mysql_query($query);
1267 }
1268
1269 /**
1270 * get_training_result()
1271 * returns all training results to an account
1272 * @param mixed $userid
1273 * @return
1274 */
1275 function get_training_result($userid){
1276 //should be entered in account/55.php
1277 $userid = intval($userid);
1278 $query = "SELECT `CP`.`pass_date`, `CT`.`type_text`, `CV`.`test_text` ".
1279 " FROM `cats_passed` AS CP, `cats_variant` AS CV, `cats_type` AS CT ".
1280 " WHERE `CP`.`variant_id`=`CV`.`id` AND `CV`.`type_id`=`CT`.`id` AND `CP`.`user_id` ='".$userid."'".
1281 " ORDER BY `CP`.`pass_date`";
1282 return mysql_query($query);
1283 }
1284
1285 /**
1286 * get_se_log()
1287 * returns all SE log entries to an account
1288 * @param mixed $userid
1289 * @return
1290 */
1291 function get_se_log($userid){
1292 $userid = intval($userid);
1293 $query = "SELECT `adminlog`.`when`, `adminlog`.`type`, `adminlog`.`information`, `users`.`fname`, `users`.`lname`
1294 FROM `adminlog`, `users`
1295 WHERE `adminlog`.`adminid` = `users`.`id` and `adminlog`.`uid`=".$userid."
1296 ORDER BY `adminlog`.`when`";
1297 return mysql_query($query);
1298 }
1299
1300 /**
1301 * get_client_certs()
1302 * returns all client certificates to an account
1303 * @param mixed $userid
1304 * @param integer $viewall- states if expired certs should be visible , default = 0 - not visible
1305 * @return
1306 */
1307 //add to account/5.php
1308 function get_client_certs($userid,$viewall=0){
1309 $userid = intval($userid);
1310 $query = "select UNIX_TIMESTAMP(`emailcerts`.`created`) as `created`,
1311 UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1312 UNIX_TIMESTAMP(`emailcerts`.`expire`) as `expired`,
1313 `emailcerts`.`expire` as `expires`,
1314 `emailcerts`.`revoked` as `revoke`,
1315 UNIX_TIMESTAMP(`emailcerts`.`revoked`) as `revoked`,
1316 `emailcerts`.`id`,
1317 `emailcerts`.`CN`,
1318 `emailcerts`.`serial`,
1319 `emailcerts`.`disablelogin` as `disablelogin`,
1320 `emailcerts`.`description`
1321 from `emailcerts`
1322 where `emailcerts`.`memid`='".$userid."'";
1323 if($viewall != 1)
1324 $query .= " AND `revoked`=0 AND `renewed`=0 ";
1325 $query .= " GROUP BY `emailcerts`.`id` ";
1326 if($viewall != 1)
1327 $query .= " HAVING `timeleft` > 0 ";
1328 $query .= " ORDER BY `emailcerts`.`modified` desc";
1329 return mysql_query($query);
1330 }
1331
1332 /**
1333 * get_server_certs()
1334 * returns all server certs to an account
1335 * @param mixed $userid
1336 * @param integer $viewall states if expired certs should be visible , default = 0 - not visible
1337 * @return
1338 */
1339 function get_server_certs($userid,$viewall=0){
1340 //add to account/12.php
1341 $userid = intval($userid);
1342 $query = "select UNIX_TIMESTAMP(`domaincerts`.`created`) as `created`,
1343 UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1344 UNIX_TIMESTAMP(`domaincerts`.`expire`) as `expired`,
1345 `domaincerts`.`expire` as `expires`, `revoked` as `revoke`,
1346 UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `domaincerts`.`serial`, `domaincerts`.`id` as `id`,
1347 `domaincerts`.`description`
1348 from `domaincerts`,`domains`
1349 where `memid`='".$userid."' and `domaincerts`.`domid`=`domains`.`id` ";
1350 if($viewall != 1)
1351 {
1352 $query .= "AND `revoked`=0 AND `renewed`=0 ";
1353 $query .= "HAVING `timeleft` > 0 ";
1354 }
1355 $query .= "ORDER BY `domaincerts`.`modified` desc";
1356 return mysql_query($query);
1357 }
1358
1359 /**
1360 * get_gpg_certs()
1361 * retruns all gpg certs to an account
1362 * @param mixed $userid
1363 * @param integer $viewall states if expired certs should be visible , default = 0 - not visible
1364 * @return
1365 */
1366 function get_gpg_certs($userid,$viewall=0){
1367 //add to gpg/2.php
1368 $userid = intval($userid);
1369 $query = $query = "select UNIX_TIMESTAMP(`issued`) as `issued`,
1370 UNIX_TIMESTAMP(`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1371 UNIX_TIMESTAMP(`expire`) as `expired`,
1372 `expire` as `expires`, `id`, `level`,
1373 `email`,`keyid`,`description` from `gpg` where `memid`='".$userid."'
1374 ORDER BY `issued` desc";
1375 return mysql_query($query);
1376 }
1377
1378
1379
1380 /**
1381 * output_log_email_header()
1382 * shows the table header to the email table
1383 * @return
1384 */
1385 function output_log_email_header(){
1386 ?>
1387 <tr>
1388 <td class="DataTD bold"><?= _("Email, primary bold") ?></td>
1389 <td class="DataTD bold"><?= _("Created") ?></td>
1390 <td class="DataTD bold"><?= _("Deleted") ?></td>
1391 </tr>
1392
1393 <?
1394 }
1395 /**
1396 * output_log_email()
1397 * shows all email data
1398 * @param mixed $row - sql-query array
1399 * @param mixed $primary - if given the primary address is highlighted
1400 * @return
1401 */
1402 function output_log_email($row,$primary){
1403 $italic='';
1404 $bold='';
1405 if ($row['deleted'] > 0) {
1406 $italic=' italic';
1407 }
1408 if ($primary==$row['email'] && $row['deleted'] == 0) {
1409 $bold= ' bold';
1410 }
1411 ?>
1412 <tr>
1413 <td class="DataTD<?= $bold . $italic ?>"><?=$row['email']?></td>
1414 <td class="DataTD<?= $bold . $italic ?>"><?=$row['created']?></td>
1415 <td class="DataTD<?= $bold . $italic ?>"><?=$row['deleted']?></td>
1416 </tr>
1417 <?
1418 }
1419
1420 /**
1421 * output_log_domains_header()
1422 * shows the table header to the domains table
1423 * @return
1424 */
1425 function output_log_domains_header(){
1426 ?>
1427 <tr>
1428 <td class="DataTD bold"><?= _("Domain") ?></td>
1429 <td class="DataTD bold"><?= _("Created") ?></td>
1430 <td class="DataTD bold"><?= _("Deleted") ?></td>
1431 </tr>
1432
1433 <?
1434 }
1435
1436 /**
1437 * output_log_domains()
1438 * shows the domain data
1439 * @param mixed $row - sql-query array
1440 * @return
1441 */
1442 function output_log_domains($row){
1443 $italic='';
1444 if (0==$row['deleted']) {
1445 $italic='italic ';
1446 }
1447 ?>
1448 <tr>
1449 <td class="DataTD <? $italic ?>"><?=$row['domain']?></td>
1450 <td class="DataTD <? $italic ?>"><?=$row['created']?></td>
1451 <td class="DataTD <? $italic ?>"><?=$row['deleted']?></td>
1452 </tr>
1453 <?
1454 }
1455
1456 /**
1457 * output_log_agreement_header()
1458 * shows the table header to the user agreement table
1459 * @return
1460 */
1461 function output_log_agreement_header(){
1462 ?>
1463 <tr>
1464 <td class="DataTD bold"><?= _("Agreement") ?></td>
1465 <td class="DataTD bold"><?= _("Date") ?></td>
1466 <td class="DataTD bold"><?= _("Method") ?></td>
1467 <td class="DataTD bold"><?= _("Active ") ?></td>
1468 </tr>
1469 <?
1470 }
1471
1472 /**
1473 * output_log_agreement()
1474 * shows the agreement data
1475 * @param mixed $row - sql-query array
1476 * @return
1477 */
1478 function output_log_agreement($row){
1479 ?>
1480 <tr>
1481 <td class="DataTD" ><?=$row['document']?></td>
1482 <td class="DataTD" ><?=$row['date']?></td>
1483 <td class="DataTD" ><?=$row['method']?></td>
1484 <td class="DataTD"><?= ($row['active']==0)? _('passive'):_('active')?></td>
1485 </tr>
1486 <?
1487 }
1488
1489 /**
1490 * output_log_training_header()
1491 * shows the table header to the training table
1492 * @return
1493 */
1494 function output_log_training_header(){
1495 //should be entered in account/55.php
1496 ?>
1497 <tr>
1498 <td class="DataTD bold"><?= _("Agreement") ?></td>
1499 <td class="DataTD bold"><?= _("Test") ?></td>
1500 <td class="DataTD bold"><?= _("Variant") ?></td>
1501 </tr>
1502 <?
1503 }
1504
1505 /**
1506 * output_log_training()
1507 * shows the training data
1508 * @param mixed $row - sql-query array
1509 * @return
1510 */
1511 function output_log_training($row){
1512 //should be entered in account/55.php
1513 ?>
1514 <tr>
1515 <td class="DataTD"><?=$row['pass_date']?></td>
1516 <td class="DataTD"><?=$row['type_text']?></td>
1517 <td class="DataTD"><?=$row['test_text']?></td>
1518 </tr>
1519 <?
1520 }
1521
1522 /**
1523 * output_log_se_header()
1524 * shows the table header to the SE log table
1525 * @param integer $support - if support = 1 some columns ar not visible
1526 * @return
1527 */
1528 function output_log_se_header($support=0){
1529 ?>
1530 <tr>
1531 <td class="DataTD bold"><?= _("Date") ?></td>
1532 <td class="DataTD bold"><?= _("Type") ?></td>
1533 <?if (1==$support) {
1534 ?>
1535 <td class="DataTD bold"><?= _("Information") ?></td>
1536 <td class="DataTD bold"><?= _("Admin") ?></td>
1537 <?
1538 }?>
1539 </tr>
1540 <?
1541 }
1542
1543 /**
1544 * output_log_se()
1545 * show the SE log data
1546 * @param mixed $row - sql-query array
1547 * @param integer $support - if support = 1 some columns are added
1548 * @return
1549 */
1550 function output_log_se($row, $support=0){
1551 //should be entered in account/55.php
1552 ?>
1553 <tr>
1554 <td class="DataTD"><?=$row['when']?></td>
1555 <td class="DataTD"><?=$row['type']?></td>
1556 <?if (1==$support) {
1557 ?>
1558 <td class="DataTD"><?=$row['information']?></td>
1559 <td class="DataTD"><?=$row['fname'].' '.$row['lname']?></td>
1560 <?
1561 }?>
1562 </tr>
1563 <?
1564 }
1565
1566 /**
1567 * output_client_cert_header()
1568 * shows the table header to the cleint cert table
1569 * @param integer $support - if support = 1 some columns ar not visible
1570 * @return
1571 */
1572 function output_client_cert_header($support=0){
1573 //should be added to account/5.php
1574 ?>
1575 <tr>
1576 <?if ($support !=1) { ?>
1577 <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td>
1578 <? } ?>
1579 <td class="DataTD"><?=_("Status")?></td>
1580 <td class="DataTD"><?=_("Email Address")?></td>
1581 <td class="DataTD"><?=_("SerialNumber")?></td>
1582 <td class="DataTD"><?=_("Revoked")?></td>
1583 <td class="DataTD"><?=_("Expires")?></td>
1584 <td class="DataTD"><?=_("Login")?></td>
1585 <?if ($support !=1) { ?>
1586 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1587 <? } ?>
1588 </tr>
1589 <?
1590 }
1591
1592 /**
1593 * output_client_cert()
1594 * show the client cert data
1595 * @param mixed $row - sql-query array
1596 * @param integer $support - if support = 1 some columns are not visible
1597 * @return
1598 */
1599 function output_client_cert($row, $support=0){
1600 //should be entered in account/5.php
1601 $verified="";
1602 if($row['timeleft'] > 0)
1603 $verified = _("Valid");
1604 if($row['timeleft'] < 0)
1605 $verified = _("Expired");
1606 if($row['expired'] == 0)
1607 $verified = _("Pending");
1608 if($row['revoked'] > 0)
1609 $verified = _("Revoked");
1610 if($row['revoked'] == 0)
1611 $row['revoke'] = _("Not Revoked");
1612 ?>
1613 <tr>
1614 <?
1615 if($verified != _("Pending") && $verified != _("Revoked")) {
1616 if ($support !=1) { ?>
1617 <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=$row['id']?>"></td>
1618 <? } ?>
1619 <td class="DataTD"><?=$verified?></td>
1620 <? if ($support !=1) { ?>
1621 <td class="DataTD"><a href="account.php?id=6&amp;cert=<?=$row['id']?>"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></a></td>
1622 <? } ELSE {?>
1623 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1624 <? } ?>
1625 <? } else if($verified != _("Revoked")) {
1626 if ($support !=1) { ?>
1627 <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=$row['id']?>"></td>
1628 <? } ?>
1629 <td class="DataTD"><?=$verified?></td>
1630 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1631 <? } else {
1632 if ($support !=1) { ?>
1633 <td class="DataTD">&nbsp;</td>
1634 <? } ?>
1635 <td class="DataTD"><?=$verified?></td>
1636 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1637 <? } ?>
1638
1639 <td class="DataTD"><?=$row['serial']?></td>
1640 <td class="DataTD"><?=$row['revoke']?></td>
1641 <td class="DataTD"><?=$row['expires']?></td>
1642
1643 <? if ($support !=1) { ?>
1644 <td class="DataTD">
1645 <input type="checkbox" name="disablelogin_<?=$row['id']?>" value="1" <?=$row['disablelogin']?"":"checked='checked'"?>/>
1646 <input type="hidden" name="cert_<?=$row['id']?>" value="1" />
1647 </td>
1648 <? } ELSE { ?>
1649 <td class="DataTD">
1650 <input type="checkbox" name="disablelogin_<?=$row['id']?>" value="1" <?=$row['disablelogin']?"":"checked='checked'"?> DISABLED/>
1651 </td>
1652 <? }
1653 if ($support !=1) { ?>
1654 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1655 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1656 <? }?>
1657 </tr>
1658
1659 <?
1660 }
1661
1662 /**
1663 * output_log_server_certs_header()
1664 * shows the table header to the server cert table
1665 * @param integer $support - if support = 1 some columns ar not visible
1666 * @return
1667 */
1668 function output_log_server_certs_header($support=0){
1669 //should be entered in account/12.php
1670 ?>
1671 <tr>
1672 <?if ($support !=1) { ?>
1673 <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td>
1674 <? } ?>
1675 <td class="DataTD"><?=_("Status")?></td>
1676 <td class="DataTD"><?=_("CommonName")?></td>
1677 <td class="DataTD"><?=_("SerialNumber")?></td>
1678 <td class="DataTD"><?=_("Revoked")?></td>
1679 <td class="DataTD"><?=_("Expires")?></td>
1680 <?if ($support !=1) { ?>
1681 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1682 <? } ?>
1683 </tr>
1684 <?
1685 }
1686
1687 /**
1688 * output_log_server_certs()
1689 * show the server cert data
1690 * @param mixed $row - sql-query array
1691 * @param integer $support - if support = 1 some columns are not visible
1692 * @return
1693 */
1694 function output_log_server_certs($row, $support=0){
1695 //should be entered in account/12.php
1696 if($row['timeleft'] > 0)
1697 $verified = _("Valid");
1698 if($row['timeleft'] < 0)
1699 $verified = _("Expired");
1700 if($row['expired'] == 0)
1701 $verified = _("Pending");
1702 if($row['revoked'] > 0)
1703 $verified = _("Revoked");
1704 if($row['revoked'] == 0)
1705 $row['revoke'] = _("Not Revoked");
1706 ?>
1707 <tr>
1708 <? if ($support !=1) {
1709 if($verified != _("Pending") && $verified != _("Revoked")) { ?>
1710 <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=$row['id']?>"/></td>
1711 <? } else if($verified != _("Revoked")) { ?>
1712 <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=$row['id']?>"/></td>
1713 <? } else { ?>
1714 <td class="DataTD">&nbsp;</td>
1715 <? }
1716 }?>
1717 <td class="DataTD"><?=$verified?></td>
1718 <?if ($support !=1) { ?>
1719 <td class="DataTD"><a href="account.php?id=15&amp;cert=<?=$row['id']?>"><?=$row['CN']?></a></td>
1720 <? }ELSE{ ?>
1721 <td class="DataTD"><?=$row['CN']?></td>
1722 <?}?>
1723 <td class="DataTD"><?=$row['serial']?></td>
1724 <td class="DataTD"><?=$row['revoke']?></td>
1725 <td class="DataTD"><?=$row['expires']?></td>
1726 <?if ($support !=1) { ?>
1727 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1728 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1729 <?}?>
1730 </tr> <?
1731 }
1732
1733 /**
1734 * output_gpg_certs_header()
1735 * shows the table header to the gpg cert table
1736 * @param integer $support - if support = 1 some columns ar not visible
1737 * @return
1738 */
1739 function output_gpg_certs_header($support=0){
1740 ?>
1741 <tr>
1742 <td class="DataTD"><?=_("Status")?></td>
1743 <td class="DataTD"><?=_("Email Address")?></td>
1744 <td class="DataTD"><?=_("Expires")?></td>
1745 <td class="DataTD"><?=_("Key ID")?></td>
1746 <?if ($support !=1) { ?>
1747 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1748 <? }?>
1749 </tr>
1750 <?
1751 }
1752
1753 /**
1754 * output_gpg_certs()
1755 * show the gpg cert data
1756 * @param mixed $row - sql-query array
1757 * @param integer $support - if support = 1 some columns are not visible
1758 * @return
1759 */
1760 function output_gpg_certs($row, $support=0){
1761 //should be entered in account/55.php
1762 if($row['timeleft'] > 0)
1763 $verified = _("Valid");
1764 if($row['timeleft'] < 0)
1765 $verified = _("Expired");
1766 if($row['expired'] == 0)
1767 $verified = _("Pending");
1768 ?>
1769 <tr>
1770 <? if($verified == _("Valid")) { ?>
1771 <td class="DataTD"><?=$verified?></td>
1772 <?if ($support !=1) { ?>
1773 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['email']?></a></td>
1774 <? } else { ?>
1775 <td class="DataTD"><?=$row['email']?></td>
1776 <? } ?>
1777 <? } else if($verified == _("Pending")) { ?>
1778 <td class="DataTD"><?=$verified?></td>
1779 <td class="DataTD"><?=$row['email']?></td>
1780 <? } else { ?>
1781 <td class="DataTD"><?=$verified?></td>
1782 <?if ($support !=1) { ?>
1783 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['email']?></a></td>
1784 <? } else { ?>
1785 <td class="DataTD"><?=$row['email']?></td>
1786 <? } ?>
1787 <? } ?>
1788 <td class="DataTD"><?=$row['expires']?></td>
1789 <?if ($support != 1) { ?>
1790 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['keyid']?></a></td>
1791 <? } else { ?>
1792 <td class="DataTD"><?=$row['keyid']?></td>
1793 <? } ?>
1794 <?if ($support !=1) { ?>
1795 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1796 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1797 <? } ?>
1798 </tr>
1799 <?
1800 }
1801
1802 /**
1803 * output_log_given_assurances()
1804 * returns the list of all given assurances
1805 * @param mixed $userid - user id for the output
1806 * @param integer $support - support view = 1
1807 * @return
1808 */
1809 function output_log_given_assurances($userid, $support=0)
1810 {
1811 output_assurances_header(_("Assurance given"),$support);
1812 output_log_given_assurances_content($userid, $support);
1813 }
1814
1815 /**
1816 * output_log_given_assurances_content()
1817 *
1818 * @param mixed $userid
1819 * @param mixed $support
1820 * @return
1821 */
1822 function output_log_given_assurances_content($userid, $support)
1823 {
1824 $res = get_given_assurances(intval($userid), 1);
1825 while($row = mysql_fetch_assoc($res))
1826 {
1827 $fromuser = get_user (intval($row['to']));
1828 $apoints = calc_experience ($row,$points,$experience,$sum_experience,$revoked);
1829 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['to']));
1830 $email = show_email_link ($fromuser['email'],intval($row['to']));
1831 $revoked = '';
1832 if ($row['date'] != 0) {
1833 $revoked = $row['deleted'];
1834 }
1835 output_log_assurances_row(intval($row['id']),$row['date'],$row['when'],$email,$name,$apoints,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience,$userid,$support,$revoked);
1836 }
1837 }
1838
1839 /**
1840 * output_log_received_assurances()
1841 *
1842 * @param mixed $userid
1843 * @param integer $support
1844 * @return
1845 */
1846 function output_log_received_assurances($userid, $support=0)
1847 {
1848 output_assurances_header(_("Assurance received"), $support);
1849 output_log_received_assurances_content($userid, $support);
1850 }
1851
1852 /**
1853 * output_log_received_assurances_content()
1854 *
1855 * @param mixed $userid
1856 * @param mixed $support
1857 * @param mixed $points
1858 * @param mixed $sum_experience
1859 * @param mixed $ticketno
1860 * @return
1861 */
1862 function output_log_received_assurances_content($userid, $support)
1863 {
1864 $res = get_received_assurances(intval($userid), 1);
1865 while($row = mysql_fetch_assoc($res))
1866 {
1867 $fromuser = get_user (intval($row['from']));
1868 calc_assurances ($row,$points,$experience,$sum_experience,$awarded,$revoked);
1869 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['from']));
1870 $email = show_email_link ($fromuser['email'],intval($row['from']));
1871 $revoked = '';
1872 if ($row['date'] != 0) {
1873 $revoked = $revoked = $row['deleted'];
1874 }
1875 output_log_assurances_row(intval($row['id']),$row['date'],$row['when'],$email,$name,$awarded,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience,$userid,$support,$revoked);
1876 }
1877 }
1878
1879 /**
1880 * output_log_assurances_row()
1881 *
1882 * @param mixed $assuranceid
1883 * @param mixed $date
1884 * @param mixed $when
1885 * @param mixed $email
1886 * @param mixed $name
1887 * @param mixed $awarded
1888 * @param mixed $points
1889 * @param mixed $location
1890 * @param mixed $method
1891 * @param mixed $experience
1892 * @param mixed $userid
1893 * @param mixed $support
1894 * @param mixed $revoked
1895 * @return
1896 */
1897 function output_log_assurances_row($assuranceid,$date,$when,$email,$name,$awarded,$points,$location,$method,$experience,$userid,$support,$revoked)
1898 {
1899
1900 $tdstyle="";
1901 $emopen="";
1902 $emclose="";
1903
1904 if ($awarded == $points)
1905 {
1906 if ($awarded == "0")
1907 {
1908 if ($when < "2006-09-01")
1909 {
1910 $tdstyle="style='background-color: #ffff80'";
1911 $emopen="<em>";
1912 $emclose="</em>";
1913 }
1914 }
1915 }
1916 ?>
1917 <tr>
1918 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$assuranceid?><?=$emclose?></td>
1919 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$date?><?=$emclose?></td>
1920 <?
1921 if ($support == "1")
1922 {
1923 ?>
1924 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$when?><?=$emclose?></td>
1925 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$email?><?=$emclose?></td>
1926 <?
1927 }
1928 ?>
1929 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$name?><?=$emclose?></td>
1930 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$awarded?><?=$emclose?></td>
1931 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$location?><?=$emclose?></td>
1932 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$method?><?=$emclose?></td>
1933 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$experience?><?=$emclose?></td>
1934 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$revoked?><?=$emclose?></td>
1935 </tr>
1936 <?
1937 }
1938