bug 1138: added assurances to SE log, rework of assurance delete form delete assuranc...
[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 function get_user_agreement_status($memid, $type="CCA"){
665 //returns 0 - no user agreement, 1- at least one entry
666 $query="SELECT u.`document` FROM `user_agreements` u
667 WHERE u.`document` = '".$type."' AND (u.`memid`=".$memid." or u.`secmemid`=".$memid.")" ;
668 $res = mysql_query($query);
669 if(mysql_num_rows($res) <=0){
670 return 0;
671 }else{
672 return 1;
673 }
674 }
675
676 function get_first_user_agreement($memid, $active=1, $type="CCA"){
677 //returns an array (`document`,`date`,`method`, `comment`,`active`)
678 if($active==1){
679 $filter="u.`memid`=".$memid;
680 }else{
681 $filter="u.`secmemid`=".$memid;
682 }
683 $query="SELECT u.`document`, u.`date`, u.`method`, u.`comment`, u.`active` FROM `user_agreements` u
684 WHERE u.`document` = '".$type."' AND ".$filter."
685 ORDER BY u.`date` Limit 1;";
686 $res = mysql_query($query);
687 if(mysql_num_rows($res) >0){
688 $row = mysql_fetch_assoc($res);
689 $rec['document']= $row['document'];
690 $rec['date']= $row['date'];
691 $rec['method']= $row['method'];
692 $rec['comment']= $row['comment'];
693 $rec['active']= $row['active'];
694 }else{
695 $rec=array();
696 }
697 return $rec;
698 }
699
700 function get_last_user_agreement($memid, $type="CCA"){
701 //returns an array (`document`,`date`,`method`, `comment`,`active`)
702 $query="(SELECT u.`document`, u.`date`, u.`method`, u.`comment`, 1 as `active` FROM user_agreements u WHERE u.`document` = '".$type."' AND (u.`memid`=".$memid." ) order by `date` desc limit 1)
703 union
704 (SELECT u.`document`, u.`date`, u.`method`, u.`comment`, 0 as `active` FROM user_agreements u WHERE u.`document` = '".$type."' AND ( u.`secmemid`=".$memid.")) order by `date` desc limit 1" ;
705 $res = mysql_query($query);
706 if(mysql_num_rows($res) >0){
707 $row = mysql_fetch_assoc($res);
708 $rec['document']= $row['document'];
709 $rec['date']= $row['date'];
710 $rec['method']= $row['method'];
711 $rec['comment']= $row['comment'];
712 $rec['active']= $row['active'];
713 }else{
714 $rec=array();
715 }
716 return $rec;
717 }
718
719 function get_user_agreement($memid){
720 $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` )
721 union
722 (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`)
723 union
724 (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`) " ;
725 $res = mysql_query($query);
726
727 return mysql_query($query);
728 }
729
730 function delete_user_agreement($memid, $type="CCA"){
731 //deletes all entries to an user for the given type of user agreements
732 mysql_query("delete from `user_agreements` where `memid`='".$memid."'");
733 mysql_query("delete from `user_agreements` where `secmemid`='".$memid."'");
734 }
735
736 // functions for 6.php (assure somebody)
737
738 function AssureHead($confirmation,$checkname)
739 {
740 ?>
741 <form method="post" action="wot.php">
742 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper" width="600">
743 <tr>
744 <td colspan="2" class="title"><?=$confirmation?></td>
745 </tr>
746 <tr>
747 <td class="DataTD" colspan="2" align="left"><?=$checkname?></td>
748 </tr>
749 <?
750 }
751
752 function AssureTextLine($field1,$field2)
753 {
754 ?>
755 <tr>
756 <td class="DataTD"><?=$field1.(empty($field1)?'':':')?></td>
757 <td class="DataTD"><?=$field2?></td>
758 </tr>
759 <?
760 }
761
762 function AssureBoxLine($type,$text,$checked)
763 {
764 ?>
765 <tr>
766 <td class="DataTD"><input type="checkbox" name="<?=$type?>" value="1" <?=$checked?"checked":""?>></td>
767 <td class="DataTD"><?=$text?></td>
768 </tr>
769 <?
770 }
771
772 function AssureMethodLine($text,$methods,$remark)
773 {
774 if (count($methods) != 1) {
775 ?>
776 <tr>
777 <td class="DataTD"><?=$text.(empty($text)?'':':')?></td>
778 <td class="DataTD">
779 <select name="method">
780 <?
781 foreach($methods as $val) {
782 ?>
783 <option value="<?=$val?>"><?=$val?></option>
784 <?
785 }
786 ?>
787 </select>
788 <br />
789 <?=$remark?>
790 </td>
791 </tr>
792 <?
793 } else {
794 ?>
795 <input type="hidden" name="<?=$val?>" value="<?=$methods[0]?>" />
796 <?
797 }
798 }
799
800 function AssureInboxLine($type,$field,$value,$description)
801 {
802 ?>
803 <tr>
804 <td class="DataTD"><?=$field.(empty($field)?'':':')?></td>
805 <td class="DataTD"><input type="text" name="<?=$type?>" value="<?=$value?>"><?=$description?></td>
806 </tr>
807 <?
808 }
809
810 function AssureFoot($oldid,$confirm)
811 {
812 ?>
813 <tr>
814 <td class="DataTD" colspan="2">
815 <input type="submit" name="process" value="<?=$confirm?>" />
816 <input type="submit" name="cancel" value="<?=_("Cancel")?>" />
817 </td>
818 </tr>
819 </table>
820 <input type="hidden" name="pagehash" value="<?=$_SESSION['_config']['wothash']?>" />
821 <input type="hidden" name="oldid" value="<?=$oldid?>" />
822 </form>
823 <?
824 }
825
826 function account_email_delete($mailid){
827 //deletes an email entry from an acount
828 //revolkes all certifcates for that email address
829 //called from www/account.php if($process != "" && $oldid == 2)
830 //called from www/diputes.php if($type == "reallyemail") / if($action == "accept")
831 //called from account_delete
832 $mailid = intval($mailid);
833 revoke_all_client_cert($mailid);
834 $query = "update `email` set `deleted`=NOW() where `id`='$mailid'";
835 mysql_query($query);
836 }
837
838 function account_domain_delete($domainid){
839 //deletes an domain entry from an acount
840 //revolkes all certifcates for that domain address
841 //called from www/account.php if($process != "" && $oldid == 9)
842 //called from www/diputes.php if($type == "reallydomain") / if($action == "accept")
843 //called from account_delete
844 $domainid = intval($domainid);
845 revoke_all_server_cert($domainid);
846 mysql_query(
847 "update `domains`
848 set `deleted`=NOW()
849 where `id` = '$domainid'");
850 }
851
852 function account_delete($id, $arbno, $adminid){
853 //deletes an account following the deleted account routnie V3
854 // called from www/account.php if($oldid == 50 && $process != "")
855 //change password
856 $id = intval($id);
857 $arbno = mysql_real_escape_string($arbno);
858 $adminid = intval($adminid);
859 $pool = 'abcdefghijklmnopqrstuvwxyz';
860 $pool .= '0123456789!()ยง';
861 $pool .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
862 srand ((double)microtime()*1000000);
863 $password="";
864 for($index = 0; $index < 30; $index++)
865 {
866 $password .= substr($pool,(rand()%(strlen ($pool))), 1);
867 }
868 mysql_query("update `users` set `password`=sha1('".$password."') where `id`='".$id."'");
869
870 //create new mail for arbitration number
871 $query = "insert into `email` set `email`='".$arbno."@cacert.org',`memid`='".$id."',`created`=NOW(),`modified`=NOW(), `attempts`=-1";
872 mysql_query($query);
873 $emailid = mysql_insert_id();
874
875 //set new mail as default
876 $query = "update `users` set `email`='".$arbno."@cacert.org' where `id`='".$id."'";
877 mysql_query($query);
878
879 //delete all other email address
880 $query = "select `id` from `email` where `memid`='".$id."' and `id`!='".$emailid."'" ;
881 $res=mysql_query($query);
882 while($row = mysql_fetch_assoc($res)){
883 account_email_delete($row['id']);
884 }
885
886 //delete all domains
887 $query = "select `id` from `domains` where `memid`='".$id."'";
888 $res=mysql_query($query);
889 while($row = mysql_fetch_assoc($res)){
890 account_domain_delete($row['id']);
891 }
892
893 //clear alert settings
894 mysql_query(
895 "update `alerts` set
896 `general`='0',
897 `country`='0',
898 `regional`='0',
899 `radius`='0'
900 where `memid`='$id'");
901
902 //set default location
903 $query = "update `users` set `locid`='2256755', `regid`='243', `ccid`='12' where `id`='".$id."'";
904 mysql_query($query);
905
906 //clear listings
907 $query = "update `users` set `listme`=' ',`contactinfo`=' ' where `id`='".$id."'";
908 mysql_query($query);
909
910 //set lanuage to default
911 //set default language
912 mysql_query("update `users` set `language`='en_AU' where `id`='".$id."'");
913 //delete secondary langugaes
914 mysql_query("delete from `addlang` where `userid`='".$id."'");
915
916 //change secret questions
917 for($i=1;$i<=5;$i++){
918 $q="";
919 $a="";
920 for($index = 0; $index < 30; $index++)
921 {
922 $q .= substr($pool,(rand()%(strlen ($pool))), 1);
923 $a .= substr($pool,(rand()%(strlen ($pool))), 1);
924 }
925 $query = "update `users` set `Q$i`='$q', `A$i`='$a' where `id`='".$id."'";
926 mysql_query($query);
927 }
928
929 //change personal information to arbitration number and DOB=1900-01-01
930 $query = "select `fname`,`mname`,`lname`,`suffix`,`dob` from `users` where `id`='$userid'";
931 $details = mysql_fetch_assoc(mysql_query($query));
932 $query = "insert into `adminlog` set `when`=NOW(),`old-lname`='${details['lname']}',`old-dob`='${details['dob']}',
933 `new-lname`='$arbno',`new-dob`='1900-01-01',`uid`='$id',`adminid`='".$adminid."'";
934 mysql_query($query);
935 $query = "update `users` set `fname`='".$arbno."',
936 `mname`='".$arbno."',
937 `lname`='".$arbno."',
938 `suffix`='".$arbno."',
939 `dob`='1900-01-01'
940 where `id`='".$id."'";
941 mysql_query($query);
942
943 //clear all admin and board flags
944 mysql_query(
945 "update `users` set
946 `assurer`='0',
947 `assurer_blocked`='0',
948 `codesign`='0',
949 `orgadmin`='0',
950 `ttpadmin`='0',
951 `locadmin`='0',
952 `admin`='0',
953 `adadmin`='0',
954 `tverify`='0',
955 `board`='0'
956 where `id`='$id'");
957
958 //block account
959 mysql_query("update `users` set `locked`='1' where `id`='$id'"); //, `deleted`=Now()
960 }
961
962
963 function check_email_exists($email){
964 // called from includes/account.php if($process != "" && $oldid == 1)
965 // called from includes/account.php if($oldid == 50 && $process != "")
966 $email = mysql_real_escape_string($email);
967 $query = "select 1 from `email` where `email`='$email' and `deleted`=0";
968 $res = mysql_query($query);
969 return mysql_num_rows($res) > 0;
970 }
971
972 function check_gpg_cert_running($uid,$cca=0){
973 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
974 // called from includes/account.php if($oldid == 50 && $process != "")
975 $uid = intval($uid);
976 if (0==$cca) {
977 $query = "select 1 from `gpg` where `memid`='$uid' and `expire`>NOW()";
978 }else{
979 $query = "select 1 from `gpg` where `memid`='$uid' and `expire`>(NOW()-90*86400)";
980 }
981 $res = mysql_query($query);
982 return mysql_num_rows($res) > 0;
983 }
984
985 function check_client_cert_running($uid,$cca=0){
986 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
987 // called from includes/account.php if($oldid == 50 && $process != "")
988 $uid = intval($uid);
989 if (0==$cca) {
990 $query1 = "select 1 from `emailcerts` where `memid`='$uid' and `expire`>NOW() and `revoked`<`created`";
991 $query2 = "select 1 from `emailcerts` where `memid`='$uid' and `revoked`>NOW()";
992 }else{
993 $query1 = "select 1 from `emailcerts` where `memid`='$uid' and `expire`>(NOW()-90*86400) and `revoked`<`created`";
994 $query2 = "select 1 from `emailcerts` where `memid`='$uid' and `revoked`>(NOW()-90*86400)";
995 }
996 $res = mysql_query($query1);
997 $r1 = mysql_num_rows($res)>0;
998 $res = mysql_query($query2);
999 $r2 = mysql_num_rows($res)>0;
1000 return !!($r1 || $r2);
1001 }
1002
1003 function check_server_cert_running($uid,$cca=0){
1004 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
1005 // called from includes/account.php if($oldid == 50 && $process != "")
1006 $uid = intval($uid);
1007 if (0==$cca) {
1008 $query1 = "
1009 select 1 from `domaincerts` join `domains`
1010 on `domaincerts`.`domid` = `domains`.`id`
1011 where `domains`.`memid` = '$uid'
1012 and `domaincerts`.`expire` > NOW()
1013 and `domaincerts`.`revoked` < `domaincerts`.`created`";
1014 $query2 = "
1015 select 1 from `domaincerts` join `domains`
1016 on `domaincerts`.`domid` = `domains`.`id`
1017 where `domains`.`memid` = '$uid'
1018 and `revoked`>NOW()";
1019 }else{
1020 $query1 = "
1021 select 1 from `domaincerts` join `domains`
1022 on `domaincerts`.`domid` = `domains`.`id`
1023 where `domains`.`memid` = '$uid'
1024 and `expire`>(NOW()-90*86400)
1025 and `revoked`<`created`";
1026 $query2 = "
1027 select 1 from `domaincerts` join `domains`
1028 on `domaincerts`.`domid` = `domains`.`id`
1029 where `domains`.`memid` = '$uid'
1030 and `revoked`>(NOW()-90*86400)";
1031 }
1032 $res = mysql_query($query1);
1033 $r1 = mysql_num_rows($res)>0;
1034 $res = mysql_query($query2);
1035 $r2 = mysql_num_rows($res)>0;
1036 return !!($r1 || $r2);
1037 }
1038
1039 function check_is_orgadmin($uid){
1040 // called from includes/account.php if($oldid == 50 && $process != "")
1041 $uid = intval($uid);
1042 $query = "select 1 from `org` where `memid`='$uid' and `deleted`=0";
1043 $res = mysql_query($query);
1044 return mysql_num_rows($res) > 0;
1045 }
1046
1047
1048 // revokation of certificates
1049 function revoke_all_client_cert($mailid){
1050 //revokes all client certificates for an email address
1051 $mailid = intval($mailid);
1052 $query = "select `emailcerts`.`id`
1053 from `emaillink`,`emailcerts` where
1054 `emaillink`.`emailid`='$mailid' and `emaillink`.`emailcertsid`=`emailcerts`.`id` and `emailcerts`.`revoked`=0
1055 group by `emailcerts`.`id`";
1056 $dres = mysql_query($query);
1057 while($drow = mysql_fetch_assoc($dres)){
1058 mysql_query("update `emailcerts` set `revoked`='1970-01-01 10:00:01', `disablelogin`=1 where `id`='".$drow['id']."'");
1059 }
1060 }
1061
1062 function revoke_all_server_cert($domainid){
1063 //revokes all server certs for an domain
1064 $domainid = intval($domainid);
1065 $query =
1066 "select `domaincerts`.`id`
1067 from `domaincerts`
1068 where `domaincerts`.`domid` = '$domainid'
1069 union distinct
1070 select `domaincerts`.`id`
1071 from `domaincerts`, `domlink`
1072 where `domaincerts`.`id` = `domlink`.`certid`
1073 and `domlink`.`domid` = '$domainid'";
1074 $dres = mysql_query($query);
1075 while($drow = mysql_fetch_assoc($dres))
1076 {
1077 mysql_query(
1078 "update `domaincerts`
1079 set `revoked`='1970-01-01 10:00:01'
1080 where `id` = '".$drow['id']."'
1081 and `revoked` = 0");
1082 }
1083 }
1084
1085 function revoke_all_private_cert($uid){
1086 //revokes all certificates linked to a personal accounts
1087 //gpg revokation needs to be added to a later point
1088 $uid=intval($uid);
1089 $query = "select `id` from `email` where `memid`='".$uid."'";
1090 $res=mysql_query($query);
1091 while($row = mysql_fetch_assoc($res)){
1092 revoke_all_client_cert($row['id']);
1093 }
1094
1095
1096 $query = "select `id` from `domains` where `memid`='".$uid."'";
1097 $res=mysql_query($query);
1098 while($row = mysql_fetch_assoc($res)){
1099 revoke_all_server_cert($row['id']);
1100 }
1101 }
1102
1103 /**
1104 * check_date_format()
1105 * checks if the date is entered in the right date format YYYY-MM-DD and
1106 * if the date is after the 1st January of the given year
1107 *
1108 * @param mixed $date
1109 * @param integer $year
1110 * @return
1111 */
1112 function check_date_format($date, $year=2000){
1113 if (!strpos($date,'-')) {
1114 return FALSE;
1115 }
1116 $arr=explode('-',$date);
1117
1118 if ((count($arr)!=3)) {
1119 return FALSE;
1120 }
1121 if (intval($arr[0])<=$year) {
1122 return FALSE;
1123 }
1124 if (intval($arr[1])>12 or intval($arr[1])<=0) {
1125 return FALSE;
1126 }
1127 if (intval($arr[2])>31 or intval($arr[2])<=0) {
1128 return FALSE;
1129 }
1130
1131 return checkdate( intval($arr[1]), intval($arr[2]), intval($arr[0]));
1132
1133 }
1134
1135 /**
1136 * check_date_difference()
1137 * returns false if the date is larger then today + time diffrence
1138 *
1139 * @param mixed $date
1140 * @param integer $diff
1141 * @return
1142 */
1143 function check_date_difference($date, $diff=1){
1144 return (strtotime($date)<=time()+$diff*86400);
1145 }
1146
1147 /**
1148 * write_se_log()
1149 * writes an information to the adminlog
1150 *
1151 * @param mixed $uid - id of the user account
1152 * @param mixed $adminid - id of the admin
1153 * @param mixed $type - what was changed
1154 * @param mixed $info - the ticket / arbitration no or other information
1155 * @return
1156 */
1157 function write_se_log($uid, $adminid, $type, $info){
1158 //records all support engineer actions changing a user account
1159 $uid = intval($uid);
1160 $adminid = intval($adminid);
1161 $type = mysql_real_escape_string($type);
1162 $info = mysql_real_escape_string($info);
1163 $query="insert into `adminlog` (`when`, `uid`, `adminid`,`type`,`information`) values
1164 (Now(), $uid, $adminid, '$type', '$info')";
1165 mysql_query($query);
1166 }
1167
1168 /**
1169 * valid_ticket_number()
1170 * checks if the entered information is a valid ticket or arbitration number
1171 * @param mixed $ticketno
1172 * @return
1173 */
1174 function valid_ticket_number($ticketno){
1175 //return if a given ticket number is valid
1176 //a arbitration case
1177 //d dispute action
1178 //s support case
1179 //m board motion
1180 $pattern='/[adsmADSM]\d{8}\./';
1181 if (preg_match($pattern, $ticketno)) {
1182 return true;
1183 }
1184 return false;
1185 }
1186
1187 // function for handling account/43.php
1188 /**
1189 * get_user_data()
1190 * returns all data of to an account given by the id
1191 * @param mixed $userid - account id
1192 * @param mixed $deleted - states if deleted data should be visible , default = 0 - not visible
1193 * @return
1194 */
1195 function get_user_data($userid, $deleted=0){
1196 $userid = intval($userid);
1197 $filter='';
1198 if (0==$deleted) {
1199 $filter=' and `users`.`deleted`=0';
1200 }
1201 $query = "select * from `users` where `users`.`id`='$userid' ".$filter;
1202 return mysql_query($query);
1203 }
1204
1205 /**
1206 * get_alerts()
1207 * retrns all alert settings for one user
1208 * @param mixed $userid for the requested account
1209 * @return
1210 */
1211 function get_alerts($userid){
1212 return mysql_fetch_assoc(mysql_query("select * from `alerts` where `memid`='".intval($userid)."'"));
1213 }
1214
1215 /**
1216 * get_email_address()
1217 * returns all email address linked to one account
1218 * @param mixed $userid
1219 * @param string $primary if given the primary email address is not retirned
1220 * @param integer $deleted - states if deleted data should be visible , default = 0 - not visible
1221 * @return
1222 */
1223 function get_email_address($userid, $primary,$deleted=0){
1224 //should be entered in account/2.php
1225 $userid = intval($userid);
1226 $filter='';
1227 if (0==$deleted) {
1228 $filter=' and `deleted`=0';
1229 }
1230 if ($primary) {
1231 $filter= $filter." and `email`!='".mysql_real_escape_string($primary)."'";
1232 }
1233 $query = "select * from `email` where `memid`='".$userid."'".$filter." order by `created`";
1234 return mysql_query($query);
1235 }
1236
1237 /**
1238 * get_domains()
1239 * returns all domains to an account
1240 * @param mixed $userid
1241 * @param integer $deleted - states if deleted data should be visible , default = 0 - not visible
1242 * @return
1243 */
1244 function get_domains($userid, $deleted=0){
1245 //should be entered in account/9.php
1246 $userid = intval($userid);
1247 $filter='';
1248 if (0==$deleted) {
1249 $filter=' and `deleted`=0';
1250 }
1251 $query = "select * from `domains` where `memid`='".$userid."' and `hash`=''".$filter." order by `created`";
1252 return mysql_query($query);
1253 }
1254
1255 /**
1256 * get_training_result()
1257 * returns all training results to an account
1258 * @param mixed $userid
1259 * @return
1260 */
1261 function get_training_result($userid){
1262 //should be entered in account/55.php
1263 $userid = intval($userid);
1264 $query = "SELECT `CP`.`pass_date`, `CT`.`type_text`, `CV`.`test_text` ".
1265 " FROM `cats_passed` AS CP, `cats_variant` AS CV, `cats_type` AS CT ".
1266 " WHERE `CP`.`variant_id`=`CV`.`id` AND `CV`.`type_id`=`CT`.`id` AND `CP`.`user_id` ='".$userid."'".
1267 " ORDER BY `CP`.`pass_date`";
1268 return mysql_query($query);
1269 }
1270
1271 /**
1272 * get_se_log()
1273 * returns all SE log entries to an account
1274 * @param mixed $userid
1275 * @return
1276 */
1277 function get_se_log($userid){
1278 $userid = intval($userid);
1279 $query = "SELECT `adminlog`.`when`, `adminlog`.`type`, `adminlog`.`information`, `users`.`fname`, `users`.`lname`
1280 FROM `adminlog`, `users`
1281 WHERE `adminlog`.`adminid` = `users`.`id` and `adminlog`.`uid`=".$userid."
1282 ORDER BY `adminlog`.`when`";
1283 return mysql_query($query);
1284 }
1285
1286 /**
1287 * get_client_certs()
1288 * returns all client certificates to an account
1289 * @param mixed $userid
1290 * @param integer $viewall- states if expired certs should be visible , default = 0 - not visible
1291 * @return
1292 */
1293 //add to account/5.php
1294 function get_client_certs($userid,$viewall=0){
1295 $userid = intval($userid);
1296 $query = "select UNIX_TIMESTAMP(`emailcerts`.`created`) as `created`,
1297 UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1298 UNIX_TIMESTAMP(`emailcerts`.`expire`) as `expired`,
1299 `emailcerts`.`expire` as `expires`,
1300 `emailcerts`.`revoked` as `revoke`,
1301 UNIX_TIMESTAMP(`emailcerts`.`revoked`) as `revoked`,
1302 `emailcerts`.`id`,
1303 `emailcerts`.`CN`,
1304 `emailcerts`.`serial`,
1305 `emailcerts`.`disablelogin` as `disablelogin`,
1306 `emailcerts`.`description`
1307 from `emailcerts`
1308 where `emailcerts`.`memid`='".$userid."'";
1309 if($viewall != 1)
1310 $query .= " AND `revoked`=0 AND `renewed`=0 ";
1311 $query .= " GROUP BY `emailcerts`.`id` ";
1312 if($viewall != 1)
1313 $query .= " HAVING `timeleft` > 0 ";
1314 $query .= " ORDER BY `emailcerts`.`modified` desc";
1315 return mysql_query($query);
1316 }
1317
1318 /**
1319 * get_server_certs()
1320 * returns all server certs to an account
1321 * @param mixed $userid
1322 * @param integer $viewall states if expired certs should be visible , default = 0 - not visible
1323 * @return
1324 */
1325 function get_server_certs($userid,$viewall=0){
1326 //add to account/12.php
1327 $userid = intval($userid);
1328 $query = "select UNIX_TIMESTAMP(`domaincerts`.`created`) as `created`,
1329 UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1330 UNIX_TIMESTAMP(`domaincerts`.`expire`) as `expired`,
1331 `domaincerts`.`expire` as `expires`, `revoked` as `revoke`,
1332 UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `domaincerts`.`serial`, `domaincerts`.`id` as `id`,
1333 `domaincerts`.`description`
1334 from `domaincerts`,`domains`
1335 where `memid`='".$userid."' and `domaincerts`.`domid`=`domains`.`id` ";
1336 if($viewall != 1)
1337 {
1338 $query .= "AND `revoked`=0 AND `renewed`=0 ";
1339 $query .= "HAVING `timeleft` > 0 ";
1340 }
1341 $query .= "ORDER BY `domaincerts`.`modified` desc";
1342 return mysql_query($query);
1343 }
1344
1345 /**
1346 * get_gpg_certs()
1347 * retruns all gpg certs to an account
1348 * @param mixed $userid
1349 * @param integer $viewall states if expired certs should be visible , default = 0 - not visible
1350 * @return
1351 */
1352 function get_gpg_certs($userid,$viewall=0){
1353 //add to gpg/2.php
1354 $userid = intval($userid);
1355 $query = $query = "select UNIX_TIMESTAMP(`issued`) as `issued`,
1356 UNIX_TIMESTAMP(`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1357 UNIX_TIMESTAMP(`expire`) as `expired`,
1358 `expire` as `expires`, `id`, `level`,
1359 `email`,`keyid`,`description` from `gpg` where `memid`='".$userid."'
1360 ORDER BY `issued` desc";
1361 return mysql_query($query);
1362 }
1363
1364
1365
1366 /**
1367 * output_log_email_header()
1368 * shows the table header to the email table
1369 * @return
1370 */
1371 function output_log_email_header(){
1372 ?>
1373 <tr>
1374 <td class="DataTD bold"><?= _("Email, primary bold") ?></td>
1375 <td class="DataTD bold"><?= _("Created") ?></td>
1376 <td class="DataTD bold"><?= _("Deleted") ?></td>
1377 </tr>
1378
1379 <?
1380 }
1381 /**
1382 * output_log_email()
1383 * shows all email data
1384 * @param mixed $row - sql-query array
1385 * @param mixed $primary - if given the primary address is highlighted
1386 * @return
1387 */
1388 function output_log_email($row,$primary){
1389 $italic='';
1390 $bold='';
1391 if (0==$row['deleted']) {
1392 $italic='italic ';
1393 }
1394 if ($primary==$row['email']) {
1395 $bold= 'bold ';
1396 }
1397 ?>
1398 <tr>
1399 <td class="DataTD <? $bold . $italic ?>"><?=$row['email']?></td>
1400 <td class="DataTD <? $bold . $italic ?>"><?=$row['created']?></td>
1401 <td class="DataTD <? $bold . $italic ?>"><?=$row['deleted']?></td>
1402 </tr>
1403 <?
1404 }
1405
1406 /**
1407 * output_log_domains_header()
1408 * shows the table header to the domains table
1409 * @return
1410 */
1411 function output_log_domains_header(){
1412 ?>
1413 <tr>
1414 <td class="DataTD bold"><?= _("Domain") ?></td>
1415 <td class="DataTD bold"><?= _("Created") ?></td>
1416 <td class="DataTD bold"><?= _("Deleted") ?></td>
1417 </tr>
1418
1419 <?
1420 }
1421
1422 /**
1423 * output_log_domains()
1424 * shows the domain data
1425 * @param mixed $row - sql-query array
1426 * @return
1427 */
1428 function output_log_domains($row){
1429 $italic='';
1430 if (0==$row['deleted']) {
1431 $italic='italic ';
1432 }
1433 ?>
1434 <tr>
1435 <td class="DataTD <? $italic ?>"><?=$row['domain']?></td>
1436 <td class="DataTD <? $italic ?>"><?=$row['created']?></td>
1437 <td class="DataTD <? $italic ?>"><?=$row['deleted']?></td>
1438 </tr>
1439 <?
1440 }
1441
1442 /**
1443 * output_log_agreement_header()
1444 * shows the table header to the user agreement table
1445 * @return
1446 */
1447 function output_log_agreement_header(){
1448 ?>
1449 <tr>
1450 <td class="DataTD bold"><?= _("Agreement") ?></td>
1451 <td class="DataTD bold"><?= _("Date") ?></td>
1452 <td class="DataTD bold"><?= _("Method") ?></td>
1453 <td class="DataTD bold"><?= _("Active ") ?></td>
1454 </tr>
1455 <?
1456 }
1457
1458 /**
1459 * output_log_agreement()
1460 * shows the agreement data
1461 * @param mixed $row - sql-query array
1462 * @return
1463 */
1464 function output_log_agreement($row){
1465 ?>
1466 <tr>
1467 <td class="DataTD" ><?=$row['document']?></td>
1468 <td class="DataTD" ><?=$row['date']?></td>
1469 <td class="DataTD" ><?=$row['method']?></td>
1470 <td class="DataTD"><?= ($row['active']==0)? _('passive'):_('active')?></td>
1471 </tr>
1472 <?
1473 }
1474
1475 /**
1476 * output_log_training_header()
1477 * shows the table header to the training table
1478 * @return
1479 */
1480 function output_log_training_header(){
1481 //should be entered in account/55.php
1482 ?>
1483 <tr>
1484 <td class="DataTD bold"><?= _("Agreement") ?></td>
1485 <td class="DataTD bold"><?= _("Test") ?></td>
1486 <td class="DataTD bold"><?= _("Variant") ?></td>
1487 </tr>
1488 <?
1489 }
1490
1491 /**
1492 * output_log_training()
1493 * shows the training data
1494 * @param mixed $row - sql-query array
1495 * @return
1496 */
1497 function output_log_training($row){
1498 //should be entered in account/55.php
1499 ?>
1500 <tr>
1501 <td class="DataTD"><?=$row['pass_date']?></td>
1502 <td class="DataTD"><?=$row['type_text']?></td>
1503 <td class="DataTD"><?=$row['test_text']?></td>
1504 </tr>
1505 <?
1506 }
1507
1508 /**
1509 * output_log_se_header()
1510 * shows the table header to the SE log table
1511 * @param integer $support - if support = 1 some columns ar not visible
1512 * @return
1513 */
1514 function output_log_se_header($support=0){
1515 ?>
1516 <tr>
1517 <td class="DataTD bold"><?= _("Date") ?></td>
1518 <td class="DataTD bold"><?= _("Type") ?></td>
1519 <?if (1==$support) {
1520 ?>
1521 <td class="DataTD bold"><?= _("Information") ?></td>
1522 <td class="DataTD bold"><?= _("Admin") ?></td>
1523 <?
1524 }?>
1525 </tr>
1526 <?
1527 }
1528
1529 /**
1530 * output_log_se()
1531 * show the SE log data
1532 * @param mixed $row - sql-query array
1533 * @param integer $support - if support = 1 some columns are added
1534 * @return
1535 */
1536 function output_log_se($row, $support=0){
1537 //should be entered in account/55.php
1538 ?>
1539 <tr>
1540 <td class="DataTD"><?=$row['when']?></td>
1541 <td class="DataTD"><?=$row['type']?></td>
1542 <?if (1==$support) {
1543 ?>
1544 <td class="DataTD"><?=$row['information']?></td>
1545 <td class="DataTD"><?=$row['fname'].' '.$row['lname']?></td>
1546 <?
1547 }?>
1548 </tr>
1549 <?
1550 }
1551
1552 /**
1553 * output_client_cert_header()
1554 * shows the table header to the cleint cert table
1555 * @param integer $support - if support = 1 some columns ar not visible
1556 * @return
1557 */
1558 function output_client_cert_header($support=0){
1559 //should be added to account/5.php
1560 ?>
1561 <tr>
1562 <?if ($support !=1) { ?>
1563 <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td>
1564 <? } ?>
1565 <td class="DataTD"><?=_("Status")?></td>
1566 <td class="DataTD"><?=_("Email Address")?></td>
1567 <td class="DataTD"><?=_("SerialNumber")?></td>
1568 <td class="DataTD"><?=_("Revoked")?></td>
1569 <td class="DataTD"><?=_("Expires")?></td>
1570 <td class="DataTD"><?=_("Login")?></td>
1571 <?if ($support !=1) { ?>
1572 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1573 <? } ?>
1574 </tr>
1575 <?
1576 }
1577
1578 /**
1579 * output_client_cert()
1580 * show the client cert data
1581 * @param mixed $row - sql-query array
1582 * @param integer $support - if support = 1 some columns are not visible
1583 * @return
1584 */
1585 function output_client_cert($row, $support=0){
1586 //should be entered in account/5.php
1587 $verified="";
1588 if($row['timeleft'] > 0)
1589 $verified = _("Valid");
1590 if($row['timeleft'] < 0)
1591 $verified = _("Expired");
1592 if($row['expired'] == 0)
1593 $verified = _("Pending");
1594 if($row['revoked'] > 0)
1595 $verified = _("Revoked");
1596 if($row['revoked'] == 0)
1597 $row['revoke'] = _("Not Revoked");
1598 ?>
1599 <tr>
1600 <?
1601 if($verified != _("Pending") && $verified != _("Revoked")) {
1602 if ($support !=1) { ?>
1603 <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=$row['id']?>"></td>
1604 <? } ?>
1605 <td class="DataTD"><?=$verified?></td>
1606 <? if ($support !=1) { ?>
1607 <td class="DataTD"><a href="account.php?id=6&amp;cert=<?=$row['id']?>"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></a></td>
1608 <? } ELSE {?>
1609 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1610 <? } ?>
1611 <? } else if($verified != _("Revoked")) {
1612 if ($support !=1) { ?>
1613 <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=$row['id']?>"></td>
1614 <? } ?>
1615 <td class="DataTD"><?=$verified?></td>
1616 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1617 <? } else {
1618 if ($support !=1) { ?>
1619 <td class="DataTD">&nbsp;</td>
1620 <? } ?>
1621 <td class="DataTD"><?=$verified?></td>
1622 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1623 <? } ?>
1624
1625 <td class="DataTD"><?=$row['serial']?></td>
1626 <td class="DataTD"><?=$row['revoke']?></td>
1627 <td class="DataTD"><?=$row['expires']?></td>
1628
1629 <? if ($support !=1) { ?>
1630 <td class="DataTD">
1631 <input type="checkbox" name="disablelogin_<?=$row['id']?>" value="1" <?=$row['disablelogin']?"":"checked='checked'"?>/>
1632 <input type="hidden" name="cert_<?=$row['id']?>" value="1" />
1633 </td>
1634 <? } ELSE { ?>
1635 <td class="DataTD">
1636 <input type="checkbox" name="disablelogin_<?=$row['id']?>" value="1" <?=$row['disablelogin']?"":"checked='checked'"?> DISABLED/>
1637 </td>
1638 <? }
1639 if ($support !=1) { ?>
1640 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1641 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1642 <? }?>
1643 </tr>
1644
1645 <?
1646 }
1647
1648 /**
1649 * output_log_server_certs_header()
1650 * shows the table header to the server cert table
1651 * @param integer $support - if support = 1 some columns ar not visible
1652 * @return
1653 */
1654 function output_log_server_certs_header($support=0){
1655 //should be entered in account/12.php
1656 ?>
1657 <tr>
1658 <?if ($support !=1) { ?>
1659 <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td>
1660 <? } ?>
1661 <td class="DataTD"><?=_("Status")?></td>
1662 <td class="DataTD"><?=_("CommonName")?></td>
1663 <td class="DataTD"><?=_("SerialNumber")?></td>
1664 <td class="DataTD"><?=_("Revoked")?></td>
1665 <td class="DataTD"><?=_("Expires")?></td>
1666 <?if ($support !=1) { ?>
1667 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1668 <? } ?>
1669 </tr>
1670 <?
1671 }
1672
1673 /**
1674 * output_log_server_certs()
1675 * show the server cert data
1676 * @param mixed $row - sql-query array
1677 * @param integer $support - if support = 1 some columns are not visible
1678 * @return
1679 */
1680 function output_log_server_certs($row, $support=0){
1681 //should be entered in account/12.php
1682 if($row['timeleft'] > 0)
1683 $verified = _("Valid");
1684 if($row['timeleft'] < 0)
1685 $verified = _("Expired");
1686 if($row['expired'] == 0)
1687 $verified = _("Pending");
1688 if($row['revoked'] > 0)
1689 $verified = _("Revoked");
1690 if($row['revoked'] == 0)
1691 $row['revoke'] = _("Not Revoked");
1692 ?>
1693 <tr>
1694 <? if ($support !=1) {
1695 if($verified != _("Pending") && $verified != _("Revoked")) { ?>
1696 <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=$row['id']?>"/></td>
1697 <? } else if($verified != _("Revoked")) { ?>
1698 <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=$row['id']?>"/></td>
1699 <? } else { ?>
1700 <td class="DataTD">&nbsp;</td>
1701 <? }
1702 }?>
1703 <td class="DataTD"><?=$verified?></td>
1704 <?if ($support !=1) { ?>
1705 <td class="DataTD"><a href="account.php?id=15&amp;cert=<?=$row['id']?>"><?=$row['CN']?></a></td>
1706 <? }ELSE{ ?>
1707 <td class="DataTD"><?=$row['CN']?></td>
1708 <?}?>
1709 <td class="DataTD"><?=$row['serial']?></td>
1710 <td class="DataTD"><?=$row['revoke']?></td>
1711 <td class="DataTD"><?=$row['expires']?></td>
1712 <?if ($support !=1) { ?>
1713 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1714 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1715 <?}?>
1716 </tr> <?
1717 }
1718
1719 /**
1720 * output_gpg_certs_header()
1721 * shows the table header to the gpg cert table
1722 * @param integer $support - if support = 1 some columns ar not visible
1723 * @return
1724 */
1725 function output_gpg_certs_header($support=0){
1726 ?>
1727 <tr>
1728 <td class="DataTD"><?=_("Status")?></td>
1729 <td class="DataTD"><?=_("Email Address")?></td>
1730 <td class="DataTD"><?=_("Expires")?></td>
1731 <td class="DataTD"><?=_("Key ID")?></td>
1732 <?if ($support !=1) { ?>
1733 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1734 <? }?>
1735 </tr>
1736 <?
1737 }
1738
1739 /**
1740 * output_gpg_certs()
1741 * show the gpg cert data
1742 * @param mixed $row - sql-query array
1743 * @param integer $support - if support = 1 some columns are not visible
1744 * @return
1745 */
1746 function output_gpg_certs($row, $support=0){
1747 //should be entered in account/55.php
1748 if($row['timeleft'] > 0)
1749 $verified = _("Valid");
1750 if($row['timeleft'] < 0)
1751 $verified = _("Expired");
1752 if($row['expired'] == 0)
1753 $verified = _("Pending");
1754 ?>
1755 <tr>
1756 <? if($verified == _("Valid")) { ?>
1757 <td class="DataTD"><?=$verified?></td>
1758 <?if ($support !=1) { ?>
1759 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['email']?></a></td>
1760 <? } else { ?>
1761 <td class="DataTD"><?=$row['email']?></td>
1762 <? } ?>
1763 <? } else if($verified == _("Pending")) { ?>
1764 <td class="DataTD"><?=$verified?></td>
1765 <td class="DataTD"><?=$row['email']?></td>
1766 <? } else { ?>
1767 <td class="DataTD"><?=$verified?></td>
1768 <?if ($support !=1) { ?>
1769 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['email']?></a></td>
1770 <? } else { ?>
1771 <td class="DataTD"><?=$row['email']?></td>
1772 <? } ?>
1773 <? } ?>
1774 <td class="DataTD"><?=$row['expires']?></td>
1775 <?if ($support != 1) { ?>
1776 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['keyid']?></a></td>
1777 <? } else { ?>
1778 <td class="DataTD"><?=$row['keyid']?></td>
1779 <? } ?>
1780 <?if ($support !=1) { ?>
1781 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1782 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1783 <? } ?>
1784 </tr>
1785 <?
1786 }
1787
1788 /**
1789 * output_log_given_assurances()
1790 * returns the list of all given assurances
1791 * @param mixed $userid - user id for the output
1792 * @param integer $support - support view = 1
1793 * @return
1794 */
1795 function output_log_given_assurances($userid, $support=0)
1796 {
1797 output_assurances_header(_("Assurance given"),$support);
1798 output_log_given_assurances_content($userid, $support);
1799 }
1800
1801 /**
1802 * output_log_given_assurances_content()
1803 *
1804 * @param mixed $userid
1805 * @param mixed $support
1806 * @return
1807 */
1808 function output_log_given_assurances_content($userid, $support)
1809 {
1810 $res = get_given_assurances(intval($userid), 1);
1811 while($row = mysql_fetch_assoc($res))
1812 {
1813 $fromuser = get_user (intval($row['to']));
1814 $apoints = calc_experience ($row,$points,$experience,$sum_experience,$revoked);
1815 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['to']));
1816 $email = show_email_link ($fromuser['email'],intval($row['to']));
1817 $revoked = '';
1818 if ($row['date'] != 0) {
1819 $revoked = $row['deleted'];
1820 }
1821 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);
1822 }
1823 }
1824
1825 /**
1826 * output_log_received_assurances()
1827 *
1828 * @param mixed $userid
1829 * @param integer $support
1830 * @return
1831 */
1832 function output_log_received_assurances($userid, $support=0)
1833 {
1834 output_assurances_header(_("Assurance received"), $support);
1835 output_log_received_assurances_content($userid, $support);
1836 }
1837
1838 /**
1839 * output_log_received_assurances_content()
1840 *
1841 * @param mixed $userid
1842 * @param mixed $support
1843 * @param mixed $points
1844 * @param mixed $sum_experience
1845 * @param mixed $ticketno
1846 * @return
1847 */
1848 function output_log_received_assurances_content($userid, $support)
1849 {
1850 $res = get_received_assurances(intval($userid), 1);
1851 while($row = mysql_fetch_assoc($res))
1852 {
1853 $fromuser = get_user (intval($row['from']));
1854 calc_assurances ($row,$points,$experience,$sum_experience,$awarded,$revoked);
1855 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['from']));
1856 $email = show_email_link ($fromuser['email'],intval($row['from']));
1857 $revoked = '';
1858 if ($row['date'] != 0) {
1859 $revoked = $revoked = $row['deleted'];
1860 }
1861 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);
1862 }
1863 }
1864
1865 /**
1866 * output_log_assurances_row()
1867 *
1868 * @param mixed $assuranceid
1869 * @param mixed $date
1870 * @param mixed $when
1871 * @param mixed $email
1872 * @param mixed $name
1873 * @param mixed $awarded
1874 * @param mixed $points
1875 * @param mixed $location
1876 * @param mixed $method
1877 * @param mixed $experience
1878 * @param mixed $userid
1879 * @param mixed $support
1880 * @param mixed $revoked
1881 * @return
1882 */
1883 function output_log_assurances_row($assuranceid,$date,$when,$email,$name,$awarded,$points,$location,$method,$experience,$userid,$support,$revoked)
1884 {
1885
1886 $tdstyle="";
1887 $emopen="";
1888 $emclose="";
1889
1890 if ($awarded == $points)
1891 {
1892 if ($awarded == "0")
1893 {
1894 if ($when < "2006-09-01")
1895 {
1896 $tdstyle="style='background-color: #ffff80'";
1897 $emopen="<em>";
1898 $emclose="</em>";
1899 }
1900 }
1901 }
1902 ?>
1903 <tr>
1904 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$assuranceid?><?=$emclose?></td>
1905 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$date?><?=$emclose?></td>
1906 <?
1907 if ($support == "1")
1908 {
1909 ?>
1910 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$when?><?=$emclose?></td>
1911 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$email?><?=$emclose?></td>
1912 <?
1913 }
1914 ?>
1915 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$name?><?=$emclose?></td>
1916 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$awarded?><?=$emclose?></td>
1917 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$location?><?=$emclose?></td>
1918 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$method?><?=$emclose?></td>
1919 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$experience?><?=$emclose?></td>
1920 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$revoked?><?=$emclose?></td>
1921 </tr>
1922 <?
1923 }
1924