1e48d85857913f304dbd4344e1f7aca1e10d0d74
[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 = "update `users` set `fname`='".$arbno."',
945 `mname`='".$arbno."',
946 `lname`='".$arbno."',
947 `suffix`='".$arbno."',
948 `dob`='1900-01-01'
949 where `id`='".$id."'";
950 mysql_query($query);
951
952 //clear all admin and board flags
953 mysql_query(
954 "update `users` set
955 `assurer`='0',
956 `assurer_blocked`='0',
957 `codesign`='0',
958 `orgadmin`='0',
959 `ttpadmin`='0',
960 `locadmin`='0',
961 `admin`='0',
962 `adadmin`='0',
963 `tverify`='0',
964 `board`='0'
965 where `id`='$id'");
966
967 //block account
968 mysql_query("update `users` set `locked`='1' where `id`='$id'"); //, `deleted`=Now()
969 }
970
971
972 function check_email_exists($email){
973 // called from includes/account.php if($process != "" && $oldid == 1)
974 // called from includes/account.php if($oldid == 50 && $process != "")
975 $email = mysql_real_escape_string($email);
976 $query = "select 1 from `email` where `email`='$email' and `deleted`=0";
977 $res = mysql_query($query);
978 return mysql_num_rows($res) > 0;
979 }
980
981 function check_gpg_cert_running($uid,$cca=0){
982 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
983 // called from includes/account.php if($oldid == 50 && $process != "")
984 $uid = intval($uid);
985 if (0==$cca) {
986 $query = "select 1 from `gpg` where `memid`='$uid' and `expire`>NOW()";
987 }else{
988 $query = "select 1 from `gpg` where `memid`='$uid' and `expire`>(NOW()-90*86400)";
989 }
990 $res = mysql_query($query);
991 return mysql_num_rows($res) > 0;
992 }
993
994 function check_client_cert_running($uid,$cca=0){
995 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
996 // called from includes/account.php if($oldid == 50 && $process != "")
997 $uid = intval($uid);
998 if (0==$cca) {
999 $query1 = "select 1 from `emailcerts` where `memid`='$uid' and `expire`>NOW() and `revoked`<`created`";
1000 $query2 = "select 1 from `emailcerts` where `memid`='$uid' and `revoked`>NOW()";
1001 }else{
1002 $query1 = "select 1 from `emailcerts` where `memid`='$uid' and `expire`>(NOW()-90*86400) and `revoked`<`created`";
1003 $query2 = "select 1 from `emailcerts` where `memid`='$uid' and `revoked`>(NOW()-90*86400)";
1004 }
1005 $res = mysql_query($query1);
1006 $r1 = mysql_num_rows($res)>0;
1007 $res = mysql_query($query2);
1008 $r2 = mysql_num_rows($res)>0;
1009 return !!($r1 || $r2);
1010 }
1011
1012 function check_server_cert_running($uid,$cca=0){
1013 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
1014 // called from includes/account.php if($oldid == 50 && $process != "")
1015 $uid = intval($uid);
1016 if (0==$cca) {
1017 $query1 = "
1018 select 1 from `domaincerts` join `domains`
1019 on `domaincerts`.`domid` = `domains`.`id`
1020 where `domains`.`memid` = '$uid'
1021 and `domaincerts`.`expire` > NOW()
1022 and `domaincerts`.`revoked` < `domaincerts`.`created`";
1023 $query2 = "
1024 select 1 from `domaincerts` join `domains`
1025 on `domaincerts`.`domid` = `domains`.`id`
1026 where `domains`.`memid` = '$uid'
1027 and `revoked`>NOW()";
1028 }else{
1029 $query1 = "
1030 select 1 from `domaincerts` join `domains`
1031 on `domaincerts`.`domid` = `domains`.`id`
1032 where `domains`.`memid` = '$uid'
1033 and `expire`>(NOW()-90*86400)
1034 and `revoked`<`created`";
1035 $query2 = "
1036 select 1 from `domaincerts` join `domains`
1037 on `domaincerts`.`domid` = `domains`.`id`
1038 where `domains`.`memid` = '$uid'
1039 and `revoked`>(NOW()-90*86400)";
1040 }
1041 $res = mysql_query($query1);
1042 $r1 = mysql_num_rows($res)>0;
1043 $res = mysql_query($query2);
1044 $r2 = mysql_num_rows($res)>0;
1045 return !!($r1 || $r2);
1046 }
1047
1048 function check_is_orgadmin($uid){
1049 // called from includes/account.php if($oldid == 50 && $process != "")
1050 $uid = intval($uid);
1051 $query = "select 1 from `org` where `memid`='$uid' and `deleted`=0";
1052 $res = mysql_query($query);
1053 return mysql_num_rows($res) > 0;
1054 }
1055
1056
1057 // revokation of certificates
1058 function revoke_all_client_cert($mailid){
1059 //revokes all client certificates for an email address
1060 $mailid = intval($mailid);
1061 $query = "select `emailcerts`.`id`
1062 from `emaillink`,`emailcerts` where
1063 `emaillink`.`emailid`='$mailid' and `emaillink`.`emailcertsid`=`emailcerts`.`id` and `emailcerts`.`revoked`=0
1064 group by `emailcerts`.`id`";
1065 $dres = mysql_query($query);
1066 while($drow = mysql_fetch_assoc($dres)){
1067 mysql_query("update `emailcerts` set `revoked`='1970-01-01 10:00:01', `disablelogin`=1 where `id`='".$drow['id']."'");
1068 }
1069 }
1070
1071 function revoke_all_server_cert($domainid){
1072 //revokes all server certs for an domain
1073 $domainid = intval($domainid);
1074 $query =
1075 "select `domaincerts`.`id`
1076 from `domaincerts`
1077 where `domaincerts`.`domid` = '$domainid'
1078 union distinct
1079 select `domaincerts`.`id`
1080 from `domaincerts`, `domlink`
1081 where `domaincerts`.`id` = `domlink`.`certid`
1082 and `domlink`.`domid` = '$domainid'";
1083 $dres = mysql_query($query);
1084 while($drow = mysql_fetch_assoc($dres))
1085 {
1086 mysql_query(
1087 "update `domaincerts`
1088 set `revoked`='1970-01-01 10:00:01'
1089 where `id` = '".$drow['id']."'
1090 and `revoked` = 0");
1091 }
1092 }
1093
1094 function revoke_all_private_cert($uid){
1095 //revokes all certificates linked to a personal accounts
1096 //gpg revokation needs to be added to a later point
1097 $uid=intval($uid);
1098 $query = "select `id` from `email` where `memid`='".$uid."'";
1099 $res=mysql_query($query);
1100 while($row = mysql_fetch_assoc($res)){
1101 revoke_all_client_cert($row['id']);
1102 }
1103
1104
1105 $query = "select `id` from `domains` where `memid`='".$uid."'";
1106 $res=mysql_query($query);
1107 while($row = mysql_fetch_assoc($res)){
1108 revoke_all_server_cert($row['id']);
1109 }
1110 }
1111
1112 /**
1113 * check_date_format()
1114 * checks if the date is entered in the right date format YYYY-MM-DD and
1115 * if the date is after the 1st January of the given year
1116 *
1117 * @param mixed $date
1118 * @param integer $year
1119 * @return
1120 */
1121 function check_date_format($date, $year=2000){
1122 if (!strpos($date,'-')) {
1123 return FALSE;
1124 }
1125 $arr=explode('-',$date);
1126
1127 if ((count($arr)!=3)) {
1128 return FALSE;
1129 }
1130 if (intval($arr[0])<=$year) {
1131 return FALSE;
1132 }
1133 if (intval($arr[1])>12 or intval($arr[1])<=0) {
1134 return FALSE;
1135 }
1136 if (intval($arr[2])>31 or intval($arr[2])<=0) {
1137 return FALSE;
1138 }
1139
1140 return checkdate( intval($arr[1]), intval($arr[2]), intval($arr[0]));
1141
1142 }
1143
1144 /**
1145 * check_date_difference()
1146 * returns false if the date is larger then today + time diffrence
1147 *
1148 * @param mixed $date
1149 * @param integer $diff
1150 * @return
1151 */
1152 function check_date_difference($date, $diff=1){
1153 return (strtotime($date)<=time()+$diff*86400);
1154 }
1155
1156 /**
1157 * write_se_log()
1158 * writes an information to the adminlog
1159 *
1160 * @param mixed $uid - id of the user account
1161 * @param mixed $adminid - id of the admin
1162 * @param mixed $type - what was changed
1163 * @param mixed $info - the ticket / arbitration no or other information
1164 * @return
1165 */
1166 function write_se_log($uid, $adminid, $type, $info){
1167 //records all support engineer actions changing a user account
1168 $uid = intval($uid);
1169 $adminid = intval($adminid);
1170 $type = mysql_real_escape_string($type);
1171 $info = mysql_real_escape_string($info);
1172 $query="insert into `adminlog` (`when`, `uid`, `adminid`,`type`,`information`) values
1173 (Now(), $uid, $adminid, '$type', '$info')";
1174 mysql_query($query);
1175 }
1176
1177 /**
1178 * valid_ticket_number()
1179 * checks if the entered information is a valid ticket or arbitration number
1180 * @param mixed $ticketno
1181 * @return
1182 */
1183 function valid_ticket_number($ticketno){
1184 //return if a given ticket number is valid
1185 //a arbitration case
1186 //d dispute action
1187 //s support case
1188 //m board motion
1189 $pattern='/[adsmADSM]\d{8}\./';
1190 if (preg_match($pattern, $ticketno)) {
1191 return true;
1192 }
1193 return false;
1194 }
1195
1196 // function for handling account/43.php
1197 /**
1198 * get_user_data()
1199 * returns all data of to an account given by the id
1200 * @param mixed $userid - account id
1201 * @param mixed $deleted - states if deleted data should be visible , default = 0 - not visible
1202 * @return
1203 */
1204 function get_user_data($userid, $deleted=0){
1205 $userid = intval($userid);
1206 $filter='';
1207 if (0==$deleted) {
1208 $filter=' and `users`.`deleted`=0';
1209 }
1210 $query = "select * from `users` where `users`.`id`='$userid' ".$filter;
1211 return mysql_query($query);
1212 }
1213
1214 /**
1215 * get_alerts()
1216 * retrns all alert settings for one user
1217 * @param mixed $userid for the requested account
1218 * @return
1219 */
1220 function get_alerts($userid){
1221 return mysql_fetch_assoc(mysql_query("select * from `alerts` where `memid`='".intval($userid)."'"));
1222 }
1223
1224 /**
1225 * get_email_address()
1226 * returns all email address linked to one account
1227 * @param mixed $userid
1228 * @param string $primary if given the primary email address is not retirned
1229 * @param integer $deleted - states if deleted data should be visible , default = 0 - not visible
1230 * @return
1231 */
1232 function get_email_address($userid, $primary,$deleted=0){
1233 //should be entered in account/2.php
1234 $userid = intval($userid);
1235 $filter='';
1236 if (0==$deleted) {
1237 $filter=' and `deleted`=0';
1238 }
1239 if ($primary) {
1240 $filter= $filter." and `email`!='".mysql_real_escape_string($primary)."'";
1241 }
1242 $query = "select * from `email` where `memid`='".$userid."'".$filter." order by `created`";
1243 return mysql_query($query);
1244 }
1245
1246 /**
1247 * get_domains()
1248 * returns all domains to an account
1249 * @param mixed $userid
1250 * @param integer $deleted - states if deleted data should be visible , default = 0 - not visible
1251 * @return
1252 */
1253 function get_domains($userid, $deleted=0){
1254 //should be entered in account/9.php
1255 $userid = intval($userid);
1256 $filter='';
1257 if (0==$deleted) {
1258 $filter=' and `deleted`=0';
1259 }
1260 $query = "select * from `domains` where `memid`='".$userid."' and `hash`=''".$filter." order by `created`";
1261 return mysql_query($query);
1262 }
1263
1264 /**
1265 * get_training_result()
1266 * returns all training results to an account
1267 * @param mixed $userid
1268 * @return
1269 */
1270 function get_training_result($userid){
1271 //should be entered in account/55.php
1272 $userid = intval($userid);
1273 $query = "SELECT `CP`.`pass_date`, `CT`.`type_text`, `CV`.`test_text` ".
1274 " FROM `cats_passed` AS CP, `cats_variant` AS CV, `cats_type` AS CT ".
1275 " WHERE `CP`.`variant_id`=`CV`.`id` AND `CV`.`type_id`=`CT`.`id` AND `CP`.`user_id` ='".$userid."'".
1276 " ORDER BY `CP`.`pass_date`";
1277 return mysql_query($query);
1278 }
1279
1280 /**
1281 * get_se_log()
1282 * returns all SE log entries to an account
1283 * @param mixed $userid
1284 * @return
1285 */
1286 function get_se_log($userid){
1287 $userid = intval($userid);
1288 $query = "SELECT `adminlog`.`when`, `adminlog`.`type`, `adminlog`.`information`, `users`.`fname`, `users`.`lname`
1289 FROM `adminlog`, `users`
1290 WHERE `adminlog`.`adminid` = `users`.`id` and `adminlog`.`uid`=".$userid."
1291 ORDER BY `adminlog`.`when`";
1292 return mysql_query($query);
1293 }
1294
1295 /**
1296 * get_client_certs()
1297 * returns all client certificates to an account
1298 * @param mixed $userid
1299 * @param integer $viewall- states if expired certs should be visible , default = 0 - not visible
1300 * @return
1301 */
1302 //add to account/5.php
1303 function get_client_certs($userid,$viewall=0){
1304 $userid = intval($userid);
1305 $query = "select UNIX_TIMESTAMP(`emailcerts`.`created`) as `created`,
1306 UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1307 UNIX_TIMESTAMP(`emailcerts`.`expire`) as `expired`,
1308 `emailcerts`.`expire` as `expires`,
1309 `emailcerts`.`revoked` as `revoke`,
1310 UNIX_TIMESTAMP(`emailcerts`.`revoked`) as `revoked`,
1311 `emailcerts`.`id`,
1312 `emailcerts`.`CN`,
1313 `emailcerts`.`serial`,
1314 `emailcerts`.`disablelogin` as `disablelogin`,
1315 `emailcerts`.`description`
1316 from `emailcerts`
1317 where `emailcerts`.`memid`='".$userid."'";
1318 if($viewall != 1)
1319 $query .= " AND `revoked`=0 AND `renewed`=0 ";
1320 $query .= " GROUP BY `emailcerts`.`id` ";
1321 if($viewall != 1)
1322 $query .= " HAVING `timeleft` > 0 ";
1323 $query .= " ORDER BY `emailcerts`.`modified` desc";
1324 return mysql_query($query);
1325 }
1326
1327 /**
1328 * get_server_certs()
1329 * returns all server certs to an account
1330 * @param mixed $userid
1331 * @param integer $viewall states if expired certs should be visible , default = 0 - not visible
1332 * @return
1333 */
1334 function get_server_certs($userid,$viewall=0){
1335 //add to account/12.php
1336 $userid = intval($userid);
1337 $query = "select UNIX_TIMESTAMP(`domaincerts`.`created`) as `created`,
1338 UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1339 UNIX_TIMESTAMP(`domaincerts`.`expire`) as `expired`,
1340 `domaincerts`.`expire` as `expires`, `revoked` as `revoke`,
1341 UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `domaincerts`.`serial`, `domaincerts`.`id` as `id`,
1342 `domaincerts`.`description`
1343 from `domaincerts`,`domains`
1344 where `memid`='".$userid."' and `domaincerts`.`domid`=`domains`.`id` ";
1345 if($viewall != 1)
1346 {
1347 $query .= "AND `revoked`=0 AND `renewed`=0 ";
1348 $query .= "HAVING `timeleft` > 0 ";
1349 }
1350 $query .= "ORDER BY `domaincerts`.`modified` desc";
1351 return mysql_query($query);
1352 }
1353
1354 /**
1355 * get_gpg_certs()
1356 * retruns all gpg certs to an account
1357 * @param mixed $userid
1358 * @param integer $viewall states if expired certs should be visible , default = 0 - not visible
1359 * @return
1360 */
1361 function get_gpg_certs($userid,$viewall=0){
1362 //add to gpg/2.php
1363 $userid = intval($userid);
1364 $query = $query = "select UNIX_TIMESTAMP(`issued`) as `issued`,
1365 UNIX_TIMESTAMP(`expire`) - UNIX_TIMESTAMP() as `timeleft`,
1366 UNIX_TIMESTAMP(`expire`) as `expired`,
1367 `expire` as `expires`, `id`, `level`,
1368 `email`,`keyid`,`description` from `gpg` where `memid`='".$userid."'
1369 ORDER BY `issued` desc";
1370 return mysql_query($query);
1371 }
1372
1373
1374
1375 /**
1376 * output_log_email_header()
1377 * shows the table header to the email table
1378 * @return
1379 */
1380 function output_log_email_header(){
1381 ?>
1382 <tr>
1383 <td class="DataTD bold"><?= _("Email, primary bold") ?></td>
1384 <td class="DataTD bold"><?= _("Created") ?></td>
1385 <td class="DataTD bold"><?= _("Deleted") ?></td>
1386 </tr>
1387
1388 <?
1389 }
1390 /**
1391 * output_log_email()
1392 * shows all email data
1393 * @param mixed $row - sql-query array
1394 * @param mixed $primary - if given the primary address is highlighted
1395 * @return
1396 */
1397 function output_log_email($row,$primary){
1398 $primaryemailaddress='';
1399 $deletedemailaddress='';
1400 if ($row['deleted'] > 0) {
1401 $deletedemailaddress='deletedemailaddress ';
1402 }
1403 if ($primary==$row['email'] && $row['deleted'] == 0) {
1404 $primaryemailaddress= 'primaryemailaddress ';
1405 }
1406 ?>
1407 <tr>
1408 <td class="DataTD<?= $primaryemailaddress . $deletedemailaddress ?>"><?=$row['email']?></td>
1409 <td class="DataTD<?= $primaryemailaddress . $deletedemailaddress ?>"><?=$row['created']?></td>
1410 <td class="DataTD<?= $primaryemailaddress . $deletedemailaddress ?>"><?=$row['deleted']?></td>
1411 </tr>
1412 <?
1413 }
1414
1415 /**
1416 * output_log_domains_header()
1417 * shows the table header to the domains table
1418 * @return
1419 */
1420 function output_log_domains_header(){
1421 ?>
1422 <tr>
1423 <td class="DataTD bold"><?= _("Domain") ?></td>
1424 <td class="DataTD bold"><?= _("Created") ?></td>
1425 <td class="DataTD bold"><?= _("Deleted") ?></td>
1426 </tr>
1427
1428 <?
1429 }
1430
1431 /**
1432 * output_log_domains()
1433 * shows the domain data
1434 * @param mixed $row - sql-query array
1435 * @return
1436 */
1437 function output_log_domains($row){
1438 $italic='';
1439 if (0==$row['deleted']) {
1440 $italic='italic ';
1441 }
1442 ?>
1443 <tr>
1444 <td class="DataTD <? $italic ?>"><?=$row['domain']?></td>
1445 <td class="DataTD <? $italic ?>"><?=$row['created']?></td>
1446 <td class="DataTD <? $italic ?>"><?=$row['deleted']?></td>
1447 </tr>
1448 <?
1449 }
1450
1451 /**
1452 * output_log_agreement_header()
1453 * shows the table header to the user agreement table
1454 * @return
1455 */
1456 function output_log_agreement_header(){
1457 ?>
1458 <tr>
1459 <td class="DataTD bold"><?= _("Agreement") ?></td>
1460 <td class="DataTD bold"><?= _("Date") ?></td>
1461 <td class="DataTD bold"><?= _("Method") ?></td>
1462 <td class="DataTD bold"><?= _("Active ") ?></td>
1463 </tr>
1464 <?
1465 }
1466
1467 /**
1468 * output_log_agreement()
1469 * shows the agreement data
1470 * @param mixed $row - sql-query array
1471 * @return
1472 */
1473 function output_log_agreement($row){
1474 ?>
1475 <tr>
1476 <td class="DataTD" ><?=$row['document']?></td>
1477 <td class="DataTD" ><?=$row['date']?></td>
1478 <td class="DataTD" ><?=$row['method']?></td>
1479 <td class="DataTD"><?= ($row['active']==0)? _('passive'):_('active')?></td>
1480 </tr>
1481 <?
1482 }
1483
1484 /**
1485 * output_log_training_header()
1486 * shows the table header to the training table
1487 * @return
1488 */
1489 function output_log_training_header(){
1490 //should be entered in account/55.php
1491 ?>
1492 <tr>
1493 <td class="DataTD bold"><?= _("Agreement") ?></td>
1494 <td class="DataTD bold"><?= _("Test") ?></td>
1495 <td class="DataTD bold"><?= _("Variant") ?></td>
1496 </tr>
1497 <?
1498 }
1499
1500 /**
1501 * output_log_training()
1502 * shows the training data
1503 * @param mixed $row - sql-query array
1504 * @return
1505 */
1506 function output_log_training($row){
1507 //should be entered in account/55.php
1508 ?>
1509 <tr>
1510 <td class="DataTD"><?=$row['pass_date']?></td>
1511 <td class="DataTD"><?=$row['type_text']?></td>
1512 <td class="DataTD"><?=$row['test_text']?></td>
1513 </tr>
1514 <?
1515 }
1516
1517 /**
1518 * output_log_se_header()
1519 * shows the table header to the SE log table
1520 * @param integer $support - if support = 1 some columns ar not visible
1521 * @return
1522 */
1523 function output_log_se_header($support=0){
1524 ?>
1525 <tr>
1526 <td class="DataTD bold"><?= _("Date") ?></td>
1527 <td class="DataTD bold"><?= _("Type") ?></td>
1528 <?if (1==$support) {
1529 ?>
1530 <td class="DataTD bold"><?= _("Information") ?></td>
1531 <td class="DataTD bold"><?= _("Admin") ?></td>
1532 <?
1533 }?>
1534 </tr>
1535 <?
1536 }
1537
1538 /**
1539 * output_log_se()
1540 * show the SE log data
1541 * @param mixed $row - sql-query array
1542 * @param integer $support - if support = 1 some columns are added
1543 * @return
1544 */
1545 function output_log_se($row, $support=0){
1546 //should be entered in account/55.php
1547 ?>
1548 <tr>
1549 <td class="DataTD"><?=$row['when']?></td>
1550 <td class="DataTD"><?=$row['type']?></td>
1551 <?if (1==$support) {
1552 ?>
1553 <td class="DataTD"><?=$row['information']?></td>
1554 <td class="DataTD"><?=$row['fname'].' '.$row['lname']?></td>
1555 <?
1556 }?>
1557 </tr>
1558 <?
1559 }
1560
1561 /**
1562 * output_client_cert_header()
1563 * shows the table header to the cleint cert table
1564 * @param integer $support - if support = 1 some columns ar not visible
1565 * @return
1566 */
1567 function output_client_cert_header($support=0){
1568 //should be added to account/5.php
1569 ?>
1570 <tr>
1571 <?if ($support !=1) { ?>
1572 <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td>
1573 <? } ?>
1574 <td class="DataTD"><?=_("Status")?></td>
1575 <td class="DataTD"><?=_("Email Address")?></td>
1576 <td class="DataTD"><?=_("SerialNumber")?></td>
1577 <td class="DataTD"><?=_("Revoked")?></td>
1578 <td class="DataTD"><?=_("Expires")?></td>
1579 <td class="DataTD"><?=_("Login")?></td>
1580 <?if ($support !=1) { ?>
1581 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1582 <? } ?>
1583 </tr>
1584 <?
1585 }
1586
1587 /**
1588 * output_client_cert()
1589 * show the client cert data
1590 * @param mixed $row - sql-query array
1591 * @param integer $support - if support = 1 some columns are not visible
1592 * @return
1593 */
1594 function output_client_cert($row, $support=0){
1595 //should be entered in account/5.php
1596 $verified="";
1597 if($row['timeleft'] > 0)
1598 $verified = _("Valid");
1599 if($row['timeleft'] < 0)
1600 $verified = _("Expired");
1601 if($row['expired'] == 0)
1602 $verified = _("Pending");
1603 if($row['revoked'] > 0)
1604 $verified = _("Revoked");
1605 if($row['revoked'] == 0)
1606 $row['revoke'] = _("Not Revoked");
1607 ?>
1608 <tr>
1609 <?
1610 if($verified != _("Pending") && $verified != _("Revoked")) {
1611 if ($support !=1) { ?>
1612 <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=$row['id']?>"></td>
1613 <? } ?>
1614 <td class="DataTD"><?=$verified?></td>
1615 <? if ($support !=1) { ?>
1616 <td class="DataTD"><a href="account.php?id=6&amp;cert=<?=$row['id']?>"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></a></td>
1617 <? } ELSE {?>
1618 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1619 <? } ?>
1620 <? } else if($verified != _("Revoked")) {
1621 if ($support !=1) { ?>
1622 <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=$row['id']?>"></td>
1623 <? } ?>
1624 <td class="DataTD"><?=$verified?></td>
1625 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1626 <? } else {
1627 if ($support !=1) { ?>
1628 <td class="DataTD">&nbsp;</td>
1629 <? } ?>
1630 <td class="DataTD"><?=$verified?></td>
1631 <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
1632 <? } ?>
1633
1634 <td class="DataTD"><?=$row['serial']?></td>
1635 <td class="DataTD"><?=$row['revoke']?></td>
1636 <td class="DataTD"><?=$row['expires']?></td>
1637
1638 <? if ($support !=1) { ?>
1639 <td class="DataTD">
1640 <input type="checkbox" name="disablelogin_<?=$row['id']?>" value="1" <?=$row['disablelogin']?"":"checked='checked'"?>/>
1641 <input type="hidden" name="cert_<?=$row['id']?>" value="1" />
1642 </td>
1643 <? } ELSE { ?>
1644 <td class="DataTD">
1645 <input type="checkbox" name="disablelogin_<?=$row['id']?>" value="1" <?=$row['disablelogin']?"":"checked='checked'"?> DISABLED/>
1646 </td>
1647 <? }
1648 if ($support !=1) { ?>
1649 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1650 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1651 <? }?>
1652 </tr>
1653
1654 <?
1655 }
1656
1657 /**
1658 * output_log_server_certs_header()
1659 * shows the table header to the server cert table
1660 * @param integer $support - if support = 1 some columns ar not visible
1661 * @return
1662 */
1663 function output_log_server_certs_header($support=0){
1664 //should be entered in account/12.php
1665 ?>
1666 <tr>
1667 <?if ($support !=1) { ?>
1668 <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td>
1669 <? } ?>
1670 <td class="DataTD"><?=_("Status")?></td>
1671 <td class="DataTD"><?=_("CommonName")?></td>
1672 <td class="DataTD"><?=_("SerialNumber")?></td>
1673 <td class="DataTD"><?=_("Revoked")?></td>
1674 <td class="DataTD"><?=_("Expires")?></td>
1675 <?if ($support !=1) { ?>
1676 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1677 <? } ?>
1678 </tr>
1679 <?
1680 }
1681
1682 /**
1683 * output_log_server_certs()
1684 * show the server cert data
1685 * @param mixed $row - sql-query array
1686 * @param integer $support - if support = 1 some columns are not visible
1687 * @return
1688 */
1689 function output_log_server_certs($row, $support=0){
1690 //should be entered in account/12.php
1691 if($row['timeleft'] > 0)
1692 $verified = _("Valid");
1693 if($row['timeleft'] < 0)
1694 $verified = _("Expired");
1695 if($row['expired'] == 0)
1696 $verified = _("Pending");
1697 if($row['revoked'] > 0)
1698 $verified = _("Revoked");
1699 if($row['revoked'] == 0)
1700 $row['revoke'] = _("Not Revoked");
1701 ?>
1702 <tr>
1703 <? if ($support !=1) {
1704 if($verified != _("Pending") && $verified != _("Revoked")) { ?>
1705 <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=$row['id']?>"/></td>
1706 <? } else if($verified != _("Revoked")) { ?>
1707 <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=$row['id']?>"/></td>
1708 <? } else { ?>
1709 <td class="DataTD">&nbsp;</td>
1710 <? }
1711 }?>
1712 <td class="DataTD"><?=$verified?></td>
1713 <?if ($support !=1) { ?>
1714 <td class="DataTD"><a href="account.php?id=15&amp;cert=<?=$row['id']?>"><?=$row['CN']?></a></td>
1715 <? }ELSE{ ?>
1716 <td class="DataTD"><?=$row['CN']?></td>
1717 <?}?>
1718 <td class="DataTD"><?=$row['serial']?></td>
1719 <td class="DataTD"><?=$row['revoke']?></td>
1720 <td class="DataTD"><?=$row['expires']?></td>
1721 <?if ($support !=1) { ?>
1722 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1723 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1724 <?}?>
1725 </tr> <?
1726 }
1727
1728 /**
1729 * output_gpg_certs_header()
1730 * shows the table header to the gpg cert table
1731 * @param integer $support - if support = 1 some columns ar not visible
1732 * @return
1733 */
1734 function output_gpg_certs_header($support=0){
1735 ?>
1736 <tr>
1737 <td class="DataTD"><?=_("Status")?></td>
1738 <td class="DataTD"><?=_("Email Address")?></td>
1739 <td class="DataTD"><?=_("Expires")?></td>
1740 <td class="DataTD"><?=_("Key ID")?></td>
1741 <?if ($support !=1) { ?>
1742 <td colspan="2" class="DataTD"><?=_("Comment *")?></td>
1743 <? }?>
1744 </tr>
1745 <?
1746 }
1747
1748 /**
1749 * output_gpg_certs()
1750 * show the gpg cert data
1751 * @param mixed $row - sql-query array
1752 * @param integer $support - if support = 1 some columns are not visible
1753 * @return
1754 */
1755 function output_gpg_certs($row, $support=0){
1756 //should be entered in account/55.php
1757 if($row['timeleft'] > 0)
1758 $verified = _("Valid");
1759 if($row['timeleft'] < 0)
1760 $verified = _("Expired");
1761 if($row['expired'] == 0)
1762 $verified = _("Pending");
1763 ?>
1764 <tr>
1765 <? if($verified == _("Valid")) { ?>
1766 <td class="DataTD"><?=$verified?></td>
1767 <?if ($support !=1) { ?>
1768 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['email']?></a></td>
1769 <? } else { ?>
1770 <td class="DataTD"><?=$row['email']?></td>
1771 <? } ?>
1772 <? } else if($verified == _("Pending")) { ?>
1773 <td class="DataTD"><?=$verified?></td>
1774 <td class="DataTD"><?=$row['email']?></td>
1775 <? } else { ?>
1776 <td class="DataTD"><?=$verified?></td>
1777 <?if ($support !=1) { ?>
1778 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['email']?></a></td>
1779 <? } else { ?>
1780 <td class="DataTD"><?=$row['email']?></td>
1781 <? } ?>
1782 <? } ?>
1783 <td class="DataTD"><?=$row['expires']?></td>
1784 <?if ($support != 1) { ?>
1785 <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['keyid']?></a></td>
1786 <? } else { ?>
1787 <td class="DataTD"><?=$row['keyid']?></td>
1788 <? } ?>
1789 <?if ($support !=1) { ?>
1790 <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
1791 <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
1792 <? } ?>
1793 </tr>
1794 <?
1795 }
1796
1797 /**
1798 * output_log_given_assurances()
1799 * returns the list of all given assurances
1800 * @param mixed $userid - user id for the output
1801 * @param integer $support - support view = 1
1802 * @return
1803 */
1804 function output_log_given_assurances($userid, $support=0)
1805 {
1806 output_assurances_header(_("Assurance given"),$support);
1807 output_log_given_assurances_content($userid, $support);
1808 }
1809
1810 /**
1811 * output_log_given_assurances_content()
1812 *
1813 * @param mixed $userid
1814 * @param mixed $support
1815 * @return
1816 */
1817 function output_log_given_assurances_content($userid, $support)
1818 {
1819 $res = get_given_assurances(intval($userid), 1);
1820 while($row = mysql_fetch_assoc($res))
1821 {
1822 $fromuser = get_user (intval($row['to']));
1823 $apoints = calc_experience ($row,$points,$experience,$sum_experience,$revoked);
1824 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['to']));
1825 $email = show_email_link ($fromuser['email'],intval($row['to']));
1826 $revoked = '';
1827 if ($row['date'] != 0) {
1828 $revoked = $row['deleted'];
1829 }
1830 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);
1831 }
1832 }
1833
1834 /**
1835 * output_log_received_assurances()
1836 *
1837 * @param mixed $userid
1838 * @param integer $support
1839 * @return
1840 */
1841 function output_log_received_assurances($userid, $support=0)
1842 {
1843 output_assurances_header(_("Assurance received"), $support);
1844 output_log_received_assurances_content($userid, $support);
1845 }
1846
1847 /**
1848 * output_log_received_assurances_content()
1849 *
1850 * @param mixed $userid
1851 * @param mixed $support
1852 * @param mixed $points
1853 * @param mixed $sum_experience
1854 * @param mixed $ticketno
1855 * @return
1856 */
1857 function output_log_received_assurances_content($userid, $support)
1858 {
1859 $res = get_received_assurances(intval($userid), 1);
1860 while($row = mysql_fetch_assoc($res))
1861 {
1862 $fromuser = get_user (intval($row['from']));
1863 calc_assurances ($row,$points,$experience,$sum_experience,$awarded,$revoked);
1864 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['from']));
1865 $email = show_email_link ($fromuser['email'],intval($row['from']));
1866 $revoked = '';
1867 if ($row['date'] != 0) {
1868 $revoked = $revoked = $row['deleted'];
1869 }
1870 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);
1871 }
1872 }
1873
1874 /**
1875 * output_log_assurances_row()
1876 *
1877 * @param mixed $assuranceid
1878 * @param mixed $date
1879 * @param mixed $when
1880 * @param mixed $email
1881 * @param mixed $name
1882 * @param mixed $awarded
1883 * @param mixed $points
1884 * @param mixed $location
1885 * @param mixed $method
1886 * @param mixed $experience
1887 * @param mixed $userid
1888 * @param mixed $support
1889 * @param mixed $revoked
1890 * @return
1891 */
1892 function output_log_assurances_row($assuranceid,$date,$when,$email,$name,$awarded,$points,$location,$method,$experience,$userid,$support,$revoked)
1893 {
1894
1895 $tdstyle="";
1896 $emopen="";
1897 $emclose="";
1898
1899 if ($awarded == $points)
1900 {
1901 if ($awarded == "0")
1902 {
1903 if ($when < "2006-09-01")
1904 {
1905 $tdstyle="style='background-color: #ffff80'";
1906 $emopen="<em>";
1907 $emclose="</em>";
1908 }
1909 }
1910 }
1911 ?>
1912 <tr>
1913 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$assuranceid?><?=$emclose?></td>
1914 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$date?><?=$emclose?></td>
1915 <?
1916 if ($support == "1")
1917 {
1918 ?>
1919 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$when?><?=$emclose?></td>
1920 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$email?><?=$emclose?></td>
1921 <?
1922 }
1923 ?>
1924 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$name?><?=$emclose?></td>
1925 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$awarded?><?=$emclose?></td>
1926 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$location?><?=$emclose?></td>
1927 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$method?><?=$emclose?></td>
1928 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$experience?><?=$emclose?></td>
1929 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$revoked?><?=$emclose?></td>
1930 </tr>
1931 <?
1932 }
1933