45532e323e00406eac5793f04a8d61c762f64578
[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 define('NULL_DATETIME', '0000-00-00 00:00:00');
20
21 function query_init ($query)
22 {
23 return mysql_query($query);
24 }
25
26 function query_getnextrow ($res)
27 {
28 $row1 = mysql_fetch_assoc($res);
29 return $row1;
30 }
31
32 function query_get_number_of_rows ($resultset)
33 {
34 return intval(mysql_num_rows($resultset));
35 }
36
37 function get_number_of_assurances ($userid)
38 {
39 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
40 WHERE `method` = 'Face to Face Meeting' AND `from`='".intval($userid)."' and `deleted` = 0");
41 $row = query_getnextrow($res);
42
43 return intval($row['list']);
44 }
45
46 function get_number_of_ttpassurances ($userid)
47 {
48 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
49 WHERE (`method`='Trusted Third Parties' or `method`='TTP-Assisted') AND `to`='".intval($userid)."' and `deleted` = 0");
50 $row = query_getnextrow($res);
51
52 return intval($row['list']);
53 }
54
55 function get_number_of_assurees ($userid)
56 {
57 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
58 WHERE `method` = 'Face to Face Meeting' AND `to`='".intval($userid)."' and `deleted` = 0");
59 $row = query_getnextrow($res);
60
61 return intval($row['list']);
62 }
63
64 function get_top_assurer_position ($no_of_assurances)
65 {
66 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
67 WHERE `method` = 'Face to Face Meeting' and `deleted` = 0
68 GROUP BY `from` HAVING count(*) > '".intval($no_of_assurances)."'");
69 return intval(query_get_number_of_rows($res)+1);
70 }
71
72 function get_top_assuree_position ($no_of_assurees)
73 {
74 $res = query_init ("SELECT count(*) AS `list` FROM `notary`
75 WHERE `method` = 'Face to Face Meeting' and `deleted` = 0
76 GROUP BY `to` HAVING count(*) > '".intval($no_of_assurees)."'");
77 return intval(query_get_number_of_rows($res)+1);
78 }
79
80 function get_given_assurances ($userid)
81 {
82 $res = query_init ("select * from `notary` where `from`='".intval($userid)."' and `from` != `to` and `deleted` = 0 order by `id` asc");
83 return $res;
84 }
85
86 function get_received_assurances ($userid)
87 {
88 $res = query_init ("select * from `notary` where `to`='".intval($userid)."' and `from` != `to` and `deleted` = 0 order by `id` asc ");
89 return $res;
90 }
91
92 function get_given_assurances_summary ($userid)
93 {
94 $res = query_init ("select count(*) as number,points,awarded,method from notary where `from`='".intval($userid)."' and `deleted` = 0 group by points,awarded,method");
95 return $res;
96 }
97
98 function get_received_assurances_summary ($userid)
99 {
100 $res = query_init ("select count(*) as number,points,awarded,method from notary where `to`='".intval($userid)."' and `deleted` = 0 group by points,awarded,method");
101 return $res;
102 }
103
104 function get_user ($userid)
105 {
106 $res = query_init ("select * from `users` where `id`='".intval($userid)."'");
107 return mysql_fetch_assoc($res);
108 }
109
110 function get_cats_state ($userid)
111 {
112
113 $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
114 WHERE `cats_passed`.`user_id` = '".intval($userid)."'");
115 return mysql_num_rows($res);
116 }
117
118
119 /**
120 * Calculate awarded points (corrects some issues like out of range points
121 * or points that were issued by means that have been deprecated)
122 *
123 * @param array $row - associative array containing the data from the
124 * `notary` table
125 * @return int - the awarded points for this assurance
126 */
127 function calc_awarded($row)
128 {
129 // Back in the old days there was no `awarded` column => is now zero,
130 // there the `points` column contained that data
131 $points = max(intval($row['awarded']), intval($row['points']));
132
133 // Set negative points to zero, yes there are such things in the database
134 $points = max($points, 0);
135
136 switch ($row['method'])
137 {
138 // These programmes have been revoked
139 case 'Thawte Points Transfer': // revoke all Thawte-points (as per arbitration)
140 case 'CT Magazine - Germany': // revoke c't (only one test-entry)
141 case 'Temporary Increase': // revoke 'temporary increase' (Current usage breaks audit aspects, needs to be reimplemented)
142 $points = 0;
143 break;
144
145 case 'Administrative Increase': // ignore AI with 2 points or less (historical for experiance points, now other calculation)
146 if ($points <= 2) // maybe limit to 35/50 pts in the future?
147 $points = 0;
148 break;
149
150 // TTP assurances, limit to 35
151 case 'TTP-Assisted':
152 $points = min($points, 35);
153 break;
154
155 // TTP TOPUP, limit to 30
156 case 'TOPUP':
157 $points = min($points, 30);
158
159 // All these should be preserved for the time being
160 case 'Unknown': // to be revoked in the future? limit to max 50 pts?
161 case 'Trusted Third Parties': // to be revoked in the future? limit to max 35 pts?
162 case '': // to be revoked in the future? limit to max 50 pts?
163 case 'Face to Face Meeting': // normal assurances (and superassurances?), limit to 35/50 pts in the future?
164 break;
165
166 default: // should never happen ... ;-)
167 $points = 0;
168 }
169
170 return $points;
171 }
172
173
174 /**
175 * Calculate the experience points from a given Assurance
176 * @param array $row - [inout] associative array containing the data from
177 * the `notary` table
178 * @param int $sum_points - [inout] the sum of already counted assurance
179 * points the assurer issued
180 * @param int $sum_experience - [inout] the sum of already counted
181 * experience points that were awarded to the assurer
182 * @return int - the assurance points that were awarded for this assurance
183 */
184 function calc_experience(&$row, &$sum_points, &$sum_experience)
185 {
186 $awarded = calc_awarded($row);
187
188 // Don't count revoked assurances even if we are displaying them
189 if ($row['deleted'] !== NULL_DATETIME) {
190 $row['experience'] = 0;
191 return $awarded;
192 }
193
194 $experience = 0;
195 if ($row['method'] == "Face to Face Meeting")
196 {
197 $experience = 2;
198 }
199 $sum_experience += $experience;
200 $row['experience'] = $experience;
201
202 $sum_points += $awarded;
203 return $awarded;
204 }
205
206 function calc_assurances ($row,&$points,&$experience,&$sumexperience,&$awarded,&$revoked)
207 {
208 $awarded = calc_awarded($row);
209 $revoked = false;
210
211 if ($awarded > 100)
212 {
213 $experience = $awarded - 100; // needs to be fixed in the future (limit 50 pts and/or no experience if pts > 100)
214 $awarded = 100;
215 }
216 else
217 $experience = 0;
218
219 switch ($row['method'])
220 {
221 case 'Thawte Points Transfer':
222 case 'CT Magazine - Germany':
223 case 'Temporary Increase': // Current usage of 'Temporary Increase' may break audit aspects, needs to be reimplemented
224 $awarded=sprintf("<strong style='color: red'>%s</strong>",_("Revoked"));
225 $experience=0;
226 $revoked=true;
227 break;
228 default:
229 $points += $awarded;
230 }
231 $sumexperience = $sumexperience + $experience;
232 }
233
234
235 function show_user_link ($name,$userid)
236 {
237 $name = trim($name);
238 if($name == "")
239 {
240 if ($userid == 0)
241 $name = _("System");
242 else
243 $name = _("Deleted account");
244 }
245 else
246 $name = "<a href='wot.php?id=9&amp;userid=".intval($userid)."'>".sanitizeHTML($name)."</a>";
247 return $name;
248 }
249
250 function show_email_link ($email,$userid)
251 {
252 $email = trim($email);
253 if($email != "")
254 $email = "<a href='account.php?id=43&amp;userid=".intval($userid)."'>".sanitizeHTML($email)."</a>";
255 return $email;
256 }
257
258 function get_assurer_ranking($userid,&$num_of_assurances,&$rank_of_assurer)
259 {
260 $num_of_assurances = get_number_of_assurances (intval($userid));
261 $rank_of_assurer = get_top_assurer_position($num_of_assurances);
262 }
263
264 function get_assuree_ranking($userid,&$num_of_assurees,&$rank_of_assuree)
265 {
266 $num_of_assurees = get_number_of_assurees (intval($userid));
267 $rank_of_assuree = get_top_assuree_position($num_of_assurees);
268 }
269
270
271 // ************* html table definitions ******************
272
273 function output_ranking($userid)
274 {
275 get_assurer_ranking($userid,$num_of_assurances,$rank_of_assurer);
276 get_assuree_ranking($userid,$num_of_assurees,$rank_of_assuree);
277
278 ?>
279 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
280 <tr>
281 <td class="title"><?=_("Assurer Ranking")?></td>
282 </tr>
283 <tr>
284 <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>
285 </tr>
286 <tr>
287 <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>
288 </tr>
289 </table>
290 <br/>
291 <?
292 }
293
294 function output_assurances_header($title,$support)
295 {
296 ?>
297 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
298 <tr>
299 <?
300 if ($support == "1")
301 {
302 ?>
303 <td colspan="10" class="title"><?=$title?></td>
304 <?
305 } else {
306 ?>
307 <td colspan="7" class="title"><?=$title?></td>
308 <?
309 }
310 ?>
311 </tr>
312 <tr>
313 <td class="DataTD"><strong><?=_("ID")?></strong></td>
314 <td class="DataTD"><strong><?=_("Date")?></strong></td>
315 <?
316 if ($support == "1")
317 {
318 ?>
319 <td class="DataTD"><strong><?=_("When")?></strong></td>
320 <td class="DataTD"><strong><?=_("Email")?></strong></td>
321 <?
322 }
323 ?>
324 <td class="DataTD"><strong><?=_("Who")?></strong></td>
325 <td class="DataTD"><strong><?=_("Points")?></strong></td>
326 <td class="DataTD"><strong><?=_("Location")?></strong></td>
327 <td class="DataTD"><strong><?=_("Method")?></strong></td>
328 <td class="DataTD"><strong><?=_("Experience Points")?></strong></td>
329 <?
330 if ($support == "1")
331 {
332 ?>
333 <td class="DataTD"><strong><?=_("Revoke")?></strong></td>
334 <?
335 }
336 ?>
337 </tr>
338 <?
339 }
340
341 function output_assurances_footer($points_txt,$points,$experience_txt,$sumexperience,$support)
342 {
343 ?>
344 <tr>
345 <td<?=($support == "1")?' colspan="5"':' colspan="3"'?> class="DataTD"><strong><?=$points_txt?>:</strong></td>
346 <td class="DataTD"><?=$points?></td>
347 <td class="DataTD">&nbsp;</td>
348 <td class="DataTD"><strong><?=$experience_txt?>:</strong></td>
349 <td class="DataTD"><?=$sumexperience?></td>
350 <?
351 if ($support == "1")
352 {
353 ?>
354 <td class="DataTD">&nbsp;</td>
355 <?
356 }
357 ?>
358
359 </tr>
360 </table>
361 <br/>
362 <?
363 }
364
365 function output_assurances_row($assuranceid,$date,$when,$email,$name,$awarded,$points,$location,$method,$experience,$userid,$support,$revoked)
366 {
367
368 $tdstyle="";
369 $emopen="";
370 $emclose="";
371
372 if ($awarded == $points)
373 {
374 if ($awarded == "0")
375 {
376 if ($when < "2006-09-01")
377 {
378 $tdstyle="style='background-color: #ffff80'";
379 $emopen="<em>";
380 $emclose="</em>";
381 }
382 }
383 }
384 ?>
385 <tr>
386 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$assuranceid?><?=$emclose?></td>
387 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$date?><?=$emclose?></td>
388 <?
389 if ($support == "1")
390 {
391 ?>
392 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$when?><?=$emclose?></td>
393 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$email?><?=$emclose?></td>
394 <?
395 }
396 ?>
397 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$name?><?=$emclose?></td>
398 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$awarded?><?=$emclose?></td>
399 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$location?><?=$emclose?></td>
400 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$method?><?=$emclose?></td>
401 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$experience?$experience:'&nbsp;'?><?=$emclose?></td>
402 <?
403 if ($support == "1")
404 {
405 if ($revoked == true)
406 {
407 ?>
408 <td class="DataTD" <?=$tdstyle?>>&nbsp;</td>
409 <?
410 } else {
411 ?>
412 <td class="DataTD" <?=$tdstyle?>><?=$emopen?><a href="account.php?id=43&amp;userid=<?=intval($userid)?>&amp;assurance=<?=intval($assuranceid)?>&amp;csrf=<?=make_csrf('admdelassurance')?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),$assuranceid)?>');"><?=_("Revoke")?></a><?=$emclose?></td>
413 <?
414 }
415 }
416 ?>
417 </tr>
418 <?
419 }
420
421 function output_summary_header()
422 {
423 ?>
424 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
425 <tr>
426 <td colspan="4" class="title"><?=_("Summary of your Points")?></td>
427 </tr>
428 <tr>
429 <td class="DataTD"><strong><?=_("Description")?></strong></td>
430 <td class="DataTD"><strong><?=_("Points")?></strong></td>
431 <td class="DataTD"><strong><?=_("Countable Points")?></strong></td>
432 <td class="DataTD"><strong><?=_("Remark")?></strong></td>
433 </tr>
434 <?
435 }
436
437 function output_summary_footer()
438 {
439 ?>
440 </table>
441 <br/>
442 <?
443 }
444
445 function output_summary_row($title,$points,$points_countable,$remark)
446 {
447 ?>
448 <tr>
449 <td class="DataTD"><strong><?=$title?></strong></td>
450 <td class="DataTD"><?=$points?></td>
451 <td class="DataTD"><?=$points_countable?></td>
452 <td class="DataTD"><?=$remark?></td>
453 </tr>
454 <?
455 }
456
457
458 // ************* output given assurances ******************
459
460 function output_given_assurances_content($userid,&$sum_points,&$sum_experience,$support)
461 {
462 $sum_points = 0;
463 $sumexperience = 0;
464 $res = get_given_assurances(intval($userid));
465 while($row = mysql_fetch_assoc($res))
466 {
467 $assuree = get_user (intval($row['to']));
468 $apoints = calc_experience($row, $sum_points, $sum_experience);
469 $name = show_user_link ($assuree['fname']." ".$assuree['lname'],intval($row['to']));
470 $email = show_email_link ($assuree['email'],intval($row['to']));
471 output_assurances_row (intval($row['id']),$row['date'],$row['when'],$email,$name,$apoints,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$row['experience'],$userid,$support,$row['deleted']!==NULL_DATETIME);
472 }
473 }
474
475 // ************* output received assurances ******************
476
477 function output_received_assurances_content($userid,&$points,&$sum_experience,$support)
478 {
479 $points = 0;
480 $sumexperience = 0;
481 $res = get_received_assurances(intval($userid));
482 while($row = mysql_fetch_assoc($res))
483 {
484 $fromuser = get_user (intval($row['from']));
485 calc_assurances ($row,$points,$experience,$sum_experience,$awarded,$revoked);
486 $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['from']));
487 $email = show_email_link ($fromuser['email'],intval($row['from']));
488 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);
489 }
490 }
491
492 // ************* output summary table ******************
493
494 function check_date_limit ($userid,$age)
495 {
496 $dob = date("Y-m-d", mktime(0,0,0,date("m"),date("d"),date("Y")-$age));
497 $res = query_init ("select id from `users` where `id`='".$userid."' and `dob` < '$dob'");
498 return intval(query_get_number_of_rows($res));
499 }
500
501 function max_points($userid)
502 {
503 return output_summary_content ($userid,0);
504 }
505
506 function output_summary_content($userid,$display_output)
507 {
508 $sum_points = 0;
509 $sum_experience = 0;
510 $sum_experience_other = 0;
511 $max_points = 100;
512 $max_experience = 50;
513
514 $experience_limit_reached_txt = _("Limit reached");
515
516 if (check_date_limit($userid,18) != 1)
517 {
518 $max_experience = 10;
519 $experience_limit_reached_txt = _("Limit given by PoJAM reached");
520 }
521 if (check_date_limit($userid,14) != 1)
522 {
523 $max_experience = 0;
524 $experience_limit_reached_txt = _("Limit given by PoJAM reached");
525 }
526
527 $res = get_received_assurances_summary($userid);
528 while($row = mysql_fetch_assoc($res))
529 {
530 $points = calc_awarded($row);
531
532 if ($points > $max_points) // limit to 100 points, above is experience (needs to be fixed)
533 {
534 $sum_experience_other = $sum_experience_other+($points-$max_points)*intval($row['number']);
535 $points = $max_points;
536 }
537 $sum_points += $points*intval($row['number']);
538 }
539
540 $res = get_given_assurances_summary($userid);
541 while($row = mysql_fetch_assoc($res))
542 {
543 switch ($row['method'])
544 {
545 case 'Face to Face Meeting': // count Face to Face only
546 $sum_experience += 2*intval($row['number']);
547 break;
548 }
549
550 }
551
552 if ($sum_points > $max_points)
553 {
554 $sum_points_countable = $max_points;
555 $remark_points = _("Limit reached");
556 }
557 else
558 {
559 $sum_points_countable = $sum_points;
560 $remark_points = "&nbsp;";
561 }
562 if ($sum_experience > $max_experience)
563 {
564 $sum_experience_countable = $max_experience;
565 $remark_experience = $experience_limit_reached_txt;
566 }
567 else
568 {
569 $sum_experience_countable = $sum_experience;
570 $remark_experience = "&nbsp;";
571 }
572
573 if ($sum_experience_countable + $sum_experience_other > $max_experience)
574 {
575 $sum_experience_other_countable = $max_experience-$sum_experience_countable;
576 $remark_experience_other = $experience_limit_reached_txt;
577 }
578 else
579 {
580 $sum_experience_other_countable = $sum_experience_other;
581 $remark_experience_other = "&nbsp;";
582 }
583
584 if ($sum_points_countable < $max_points)
585 {
586 if ($sum_experience_countable != 0)
587 $remark_experience = _("Points on hold due to less assurance points");
588 $sum_experience_countable = 0;
589 if ($sum_experience_other_countable != 0)
590 $remark_experience_other = _("Points on hold due to less assurance points");
591 $sum_experience_other_countable = 0;
592 }
593
594 $issue_points = 0;
595 $cats_test_passed = get_cats_state ($userid);
596 if ($cats_test_passed == 0)
597 {
598 $issue_points_txt = "<strong style='color: red'>"._("You have to pass the CAcert Assurer Challenge (CATS-Test) to be an Assurer")."</strong>";
599 if ($sum_points_countable < $max_points)
600 {
601 $issue_points_txt = "<strong style='color: red'>";
602 $issue_points_txt .= sprintf(_("You need %s assurance points and the passed CATS-Test to be an Assurer"), intval($max_points));
603 $issue_points_txt .= "</strong>";
604 }
605 }
606 else
607 {
608 $experience_total = $sum_experience_countable+$sum_experience_other_countable;
609 $issue_points_txt = "";
610 if ($sum_points_countable == $max_points)
611 $issue_points = 10;
612 if ($experience_total >= 10)
613 $issue_points = 15;
614 if ($experience_total >= 20)
615 $issue_points = 20;
616 if ($experience_total >= 30)
617 $issue_points = 25;
618 if ($experience_total >= 40)
619 $issue_points = 30;
620 if ($experience_total >= 50)
621 $issue_points = 35;
622 if ($issue_points != 0)
623 $issue_points_txt = sprintf(_("You may issue up to %s points"),$issue_points);
624 }
625 if ($display_output)
626 {
627 output_summary_row (_("Assurance Points you received"),$sum_points,$sum_points_countable,$remark_points);
628 output_summary_row (_("Total Experience Points by Assurance"),$sum_experience,$sum_experience_countable,$remark_experience);
629 output_summary_row (_("Total Experience Points (other ways)"),$sum_experience_other,$sum_experience_other_countable,$remark_experience_other);
630 output_summary_row (_("Total Points"),"&nbsp;",$sum_points_countable + $sum_experience_countable + $sum_experience_other_countable,$issue_points_txt);
631 }
632 return $issue_points;
633 }
634
635 function output_given_assurances($userid,$support=0)
636 {
637 output_assurances_header(_("Assurance Points You Issued"),$support);
638 output_given_assurances_content($userid,$points,$sum_experience,$support);
639 output_assurances_footer(_("Total Points Issued"),$points,_("Total Experience Points"),$sum_experience,$support);
640 }
641
642 function output_received_assurances($userid,$support=0)
643 {
644 output_assurances_header(_("Your Assurance Points"),$support);
645 output_received_assurances_content($userid,$points,$sum_experience,$support);
646 output_assurances_footer(_("Total Assurance Points"),$points,_("Total Experience Points"),$sum_experience,$support);
647 }
648
649 function output_summary($userid)
650 {
651 output_summary_header();
652 output_summary_content($userid,1);
653 output_summary_footer();
654 }
655
656 function output_end_of_page()
657 {
658 ?>
659 <p>[ <a href='javascript:history.go(-1)'><?=_("Go Back")?></a> ]</p>
660 <?
661 }
662
663 //functions to do with recording user agreements
664 /**
665 * write_user_agreement()
666 * writes a new record to the table user_agreement
667 *
668 * @param mixed $memid
669 * @param mixed $document
670 * @param mixed $method
671 * @param mixed $comment
672 * @param integer $active
673 * @param integer $secmemid
674 * @return
675 */
676 function write_user_agreement($memid, $document, $method, $comment, $active=1, $secmemid=0){
677 // write a new record to the table user_agreement
678 $query="insert into `user_agreements` set `memid`=".intval($memid).", `secmemid`=".intval($secmemid).
679 ",`document`='".mysql_real_escape_string($document)."',`date`=NOW(), `active`=".intval($active).",`method`='".mysql_real_escape_string($method)."',`comment`='".mysql_real_escape_string($comment)."'" ;
680 $res = mysql_query($query);
681 }
682
683 /**
684 * get_user_agreement_status()
685 * returns 1 if the user has an entry for the given type in user_agreement, 0 if no entry is recorded
686 * @param mixed $memid
687 * @param string $type
688 * @return
689 */
690 function get_user_agreement_status($memid, $type="CCA"){
691 $query="SELECT u.`document` FROM `user_agreements` u
692 WHERE u.`document` = '" . mysql_real_escape_string($type) . "' AND u.`memid`=" . intval($memid) ;
693 $res = mysql_query($query);
694 if(mysql_num_rows($res) <=0){
695 return 0;
696 }else{
697 return 1;
698 }
699 }
700
701 /**
702 * get_first_user_agreement()
703 * returns the first user_agreement entry of the requested type depending on thes status of active of a given user
704 * @param mixed $memid
705 * @param integer $active, 0 - passive, 1 -active
706 * @param string $type
707 * @return
708 */
709 function get_first_user_agreement($memid, $active=1, $type="CCA"){
710 //returns an array (`document`,`date`,`method`, `comment`,`active`)
711 $query="SELECT u.`document`, u.`date`, u.`method`, u.`comment`, u.`active` FROM `user_agreements` AS u
712 WHERE u.`document` = '" . mysql_real_escape_string($type) . "' AND u.`memid`=" . intval($memid) . " AND u.`active`=" . intval($active) .
713 " ORDER BY u.`date` 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 /**
724 * get_last_user_agreement()
725 * returns the last user_agreement entry of a given type and of a given user
726 * @param mixed $memid
727 * @param string $type
728 * @return
729 */
730 function get_last_user_agreement($memid, $type="CCA"){
731 //returns an array (`document`,`date`,`method`, `comment`,`active`)
732 $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 " ;
733 $res = mysql_query($query);
734 if(mysql_num_rows($res) >0){
735 $rec = mysql_fetch_assoc($res);
736 }else{
737 $rec=array();
738 }
739 return $rec;
740 }
741
742 /**
743 * delete_user_agreement()
744 * deletes all entries for a given type from user_agreement of a given user, if type is not given all
745 * @param mixed $memid
746 * @param string $type
747 * @return
748 */
749 function delete_user_agreement($memid, $type=false){
750 //deletes all entries to an user for the given type of user agreements
751 if ($type === false) {
752 $filter = '';
753 } else {
754 $filter = " and `document` = '" . mysql_real_escape_string($type) . "'";
755 }
756 mysql_query("delete from `user_agreements` where `memid`=" . intval($memid) . $filter );
757 }
758
759 // functions for 6.php (assure somebody)
760
761 function AssureHead($confirmation,$checkname)
762 {
763 ?>
764 <form method="post" action="wot.php">
765 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper" width="600">
766 <tr>
767 <td colspan="2" class="title"><?=$confirmation?></td>
768 </tr>
769 <tr>
770 <td class="DataTD" colspan="2" align="left"><?=$checkname?></td>
771 </tr>
772 <?
773 }
774
775 function AssureTextLine($field1,$field2)
776 {
777 ?>
778 <tr>
779 <td class="DataTD"><?=$field1.(empty($field1)?'':':')?></td>
780 <td class="DataTD"><?=$field2?></td>
781 </tr>
782 <?
783 }
784
785 function AssureBoxLine($type,$text,$checked)
786 {
787 ?>
788 <tr>
789 <td class="DataTD"><input type="checkbox" name="<?=$type?>" value="1" <?=$checked?"checked":""?>></td>
790 <td class="DataTD"><?=$text?></td>
791 </tr>
792 <?
793 }
794
795 function AssureMethodLine($text,$methods,$remark)
796 {
797 if (count($methods) != 1) {
798 ?>
799 <tr>
800 <td class="DataTD"><?=$text.(empty($text)?'':':')?></td>
801 <td class="DataTD">
802 <select name="method">
803 <?
804 foreach($methods as $val) {
805 ?>
806 <option value="<?=$val?>"><?=$val?></option>
807 <?
808 }
809 ?>
810 </select>
811 <br />
812 <?=$remark?>
813 </td>
814 </tr>
815 <?
816 } else {
817 ?>
818 <input type="hidden" name="<?=$val?>" value="<?=$methods[0]?>" />
819 <?
820 }
821 }
822
823 function AssureInboxLine($type,$field,$value,$description)
824 {
825 ?>
826 <tr>
827 <td class="DataTD"><?=$field.(empty($field)?'':':')?></td>
828 <td class="DataTD"><input type="text" name="<?=$type?>" value="<?=$value?>"><?=$description?></td>
829 </tr>
830 <?
831 }
832
833 function AssureFoot($oldid,$confirm)
834 {
835 ?>
836 <tr>
837 <td class="DataTD" colspan="2">
838 <input type="submit" name="process" value="<?=$confirm?>" />
839 <input type="submit" name="cancel" value="<?=_("Cancel")?>" />
840 </td>
841 </tr>
842 </table>
843 <input type="hidden" name="pagehash" value="<?=$_SESSION['_config']['wothash']?>" />
844 <input type="hidden" name="oldid" value="<?=$oldid?>" />
845 </form>
846 <?
847 }
848
849 function account_email_delete($mailid){
850 //deletes an email entry from an acount
851 //revolkes all certifcates for that email address
852 //called from www/account.php if($process != "" && $oldid == 2)
853 //called from www/diputes.php if($type == "reallyemail") / if($action == "accept")
854 //called from account_delete
855 $mailid = intval($mailid);
856 revoke_all_client_cert($mailid);
857 $query = "update `email` set `deleted`=NOW() where `id`='$mailid'";
858 mysql_query($query);
859 }
860
861 function account_domain_delete($domainid){
862 //deletes an domain entry from an acount
863 //revolkes all certifcates for that domain address
864 //called from www/account.php if($process != "" && $oldid == 9)
865 //called from www/diputes.php if($type == "reallydomain") / if($action == "accept")
866 //called from account_delete
867 $domainid = intval($domainid);
868 revoke_all_server_cert($domainid);
869 mysql_query(
870 "update `domains`
871 set `deleted`=NOW()
872 where `id` = '$domainid'");
873 }
874
875 function account_delete($id, $arbno, $adminid){
876 //deletes an account following the deleted account routnie V3
877 // called from www/account.php if($oldid == 50 && $process != "")
878 //change password
879 $id = intval($id);
880 $arbno = mysql_real_escape_string($arbno);
881 $adminid = intval($adminid);
882 $pool = 'abcdefghijklmnopqrstuvwxyz';
883 $pool .= '0123456789!()ยง';
884 $pool .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
885 srand ((double)microtime()*1000000);
886 $password="";
887 for($index = 0; $index < 30; $index++)
888 {
889 $password .= substr($pool,(rand()%(strlen ($pool))), 1);
890 }
891 mysql_query("update `users` set `password`=sha1('".$password."') where `id`='".$id."'");
892
893 //create new mail for arbitration number
894 $query = "insert into `email` set `email`='".$arbno."@cacert.org',`memid`='".$id."',`created`=NOW(),`modified`=NOW(), `attempts`=-1";
895 mysql_query($query);
896 $emailid = mysql_insert_id();
897
898 //set new mail as default
899 $query = "update `users` set `email`='".$arbno."@cacert.org' where `id`='".$id."'";
900 mysql_query($query);
901
902 //delete all other email address
903 $query = "select `id` from `email` where `memid`='".$id."' and `id`!='".$emailid."'" ;
904 $res=mysql_query($query);
905 while($row = mysql_fetch_assoc($res)){
906 account_email_delete($row['id']);
907 }
908
909 //delete all domains
910 $query = "select `id` from `domains` where `memid`='".$id."'";
911 $res=mysql_query($query);
912 while($row = mysql_fetch_assoc($res)){
913 account_domain_delete($row['id']);
914 }
915
916 //clear alert settings
917 mysql_query(
918 "update `alerts` set
919 `general`='0',
920 `country`='0',
921 `regional`='0',
922 `radius`='0'
923 where `memid`='$id'");
924
925 //set default location
926 $query = "update `users` set `locid`='2256755', `regid`='243', `ccid`='12' where `id`='".$id."'";
927 mysql_query($query);
928
929 //clear listings
930 $query = "update `users` set `listme`=' ',`contactinfo`=' ' where `id`='".$id."'";
931 mysql_query($query);
932
933 //set lanuage to default
934 //set default language
935 mysql_query("update `users` set `language`='en_AU' where `id`='".$id."'");
936 //delete secondary langugaes
937 mysql_query("delete from `addlang` where `userid`='".$id."'");
938
939 //change secret questions
940 for($i=1;$i<=5;$i++){
941 $q="";
942 $a="";
943 for($index = 0; $index < 30; $index++)
944 {
945 $q .= substr($pool,(rand()%(strlen ($pool))), 1);
946 $a .= substr($pool,(rand()%(strlen ($pool))), 1);
947 }
948 $query = "update `users` set `Q$i`='$q', `A$i`='$a' where `id`='".$id."'";
949 mysql_query($query);
950 }
951
952 //change personal information to arbitration number and DOB=1900-01-01
953 $query = "select `fname`,`mname`,`lname`,`suffix`,`dob` from `users` where `id`='$userid'";
954 $details = mysql_fetch_assoc(mysql_query($query));
955 $query = "insert into `adminlog` set `when`=NOW(),`old-lname`='${details['lname']}',`old-dob`='${details['dob']}',
956 `new-lname`='$arbno',`new-dob`='1900-01-01',`uid`='$id',`adminid`='".$adminid."'";
957 mysql_query($query);
958 $query = "update `users` set `fname`='".$arbno."',
959 `mname`='".$arbno."',
960 `lname`='".$arbno."',
961 `suffix`='".$arbno."',
962 `dob`='1900-01-01'
963 where `id`='".$id."'";
964 mysql_query($query);
965
966 //clear all admin and board flags
967 mysql_query(
968 "update `users` set
969 `assurer`='0',
970 `assurer_blocked`='0',
971 `codesign`='0',
972 `orgadmin`='0',
973 `ttpadmin`='0',
974 `locadmin`='0',
975 `admin`='0',
976 `adadmin`='0',
977 `tverify`='0',
978 `board`='0'
979 where `id`='$id'");
980
981 //block account
982 mysql_query("update `users` set `locked`='1' where `id`='$id'"); //, `deleted`=Now()
983 }
984
985
986 function check_email_exists($email){
987 // called from includes/account.php if($process != "" && $oldid == 1)
988 // called from includes/account.php if($oldid == 50 && $process != "")
989 $email = mysql_real_escape_string($email);
990 $query = "select 1 from `email` where `email`='$email' and `deleted`=0";
991 $res = mysql_query($query);
992 return mysql_num_rows($res) > 0;
993 }
994
995 function check_gpg_cert_running($uid,$cca=0){
996 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
997 // called from includes/account.php if($oldid == 50 && $process != "")
998 $uid = intval($uid);
999 if (0==$cca) {
1000 $query = "select 1 from `gpg` where `memid`='$uid' and `expire`>NOW()";
1001 }else{
1002 $query = "select 1 from `gpg` where `memid`='$uid' and `expire`>(NOW()-90*86400)";
1003 }
1004 $res = mysql_query($query);
1005 return mysql_num_rows($res) > 0;
1006 }
1007
1008 function check_client_cert_running($uid,$cca=0){
1009 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
1010 // called from includes/account.php if($oldid == 50 && $process != "")
1011 $uid = intval($uid);
1012 if (0==$cca) {
1013 $query1 = "select 1 from `emailcerts` where `memid`='$uid' and `expire`>NOW() and `revoked`<`created`";
1014 $query2 = "select 1 from `emailcerts` where `memid`='$uid' and `revoked`>NOW()";
1015 }else{
1016 $query1 = "select 1 from `emailcerts` where `memid`='$uid' and `expire`>(NOW()-90*86400) and `revoked`<`created`";
1017 $query2 = "select 1 from `emailcerts` where `memid`='$uid' and `revoked`>(NOW()-90*86400)";
1018 }
1019 $res = mysql_query($query1);
1020 $r1 = mysql_num_rows($res)>0;
1021 $res = mysql_query($query2);
1022 $r2 = mysql_num_rows($res)>0;
1023 return !!($r1 || $r2);
1024 }
1025
1026 function check_server_cert_running($uid,$cca=0){
1027 //if $cca =0 if just expired, =1 if CCA retention +3 month should be obeyed
1028 // called from includes/account.php if($oldid == 50 && $process != "")
1029 $uid = intval($uid);
1030 if (0==$cca) {
1031 $query1 = "
1032 select 1 from `domaincerts` join `domains`
1033 on `domaincerts`.`domid` = `domains`.`id`
1034 where `domains`.`memid` = '$uid'
1035 and `domaincerts`.`expire` > NOW()
1036 and `domaincerts`.`revoked` < `domaincerts`.`created`";
1037 $query2 = "
1038 select 1 from `domaincerts` join `domains`
1039 on `domaincerts`.`domid` = `domains`.`id`
1040 where `domains`.`memid` = '$uid'
1041 and `revoked`>NOW()";
1042 }else{
1043 $query1 = "
1044 select 1 from `domaincerts` join `domains`
1045 on `domaincerts`.`domid` = `domains`.`id`
1046 where `domains`.`memid` = '$uid'
1047 and `expire`>(NOW()-90*86400)
1048 and `revoked`<`created`";
1049 $query2 = "
1050 select 1 from `domaincerts` join `domains`
1051 on `domaincerts`.`domid` = `domains`.`id`
1052 where `domains`.`memid` = '$uid'
1053 and `revoked`>(NOW()-90*86400)";
1054 }
1055 $res = mysql_query($query1);
1056 $r1 = mysql_num_rows($res)>0;
1057 $res = mysql_query($query2);
1058 $r2 = mysql_num_rows($res)>0;
1059 return !!($r1 || $r2);
1060 }
1061
1062 function check_is_orgadmin($uid){
1063 // called from includes/account.php if($oldid == 50 && $process != "")
1064 $uid = intval($uid);
1065 $query = "select 1 from `org` where `memid`='$uid' and `deleted`=0";
1066 $res = mysql_query($query);
1067 return mysql_num_rows($res) > 0;
1068 }
1069
1070
1071 // revokation of certificates
1072 function revoke_all_client_cert($mailid){
1073 //revokes all client certificates for an email address
1074 $mailid = intval($mailid);
1075 $query = "select `emailcerts`.`id`
1076 from `emaillink`,`emailcerts` where
1077 `emaillink`.`emailid`='$mailid' and `emaillink`.`emailcertsid`=`emailcerts`.`id` and `emailcerts`.`revoked`=0
1078 group by `emailcerts`.`id`";
1079 $dres = mysql_query($query);
1080 while($drow = mysql_fetch_assoc($dres)){
1081 mysql_query("update `emailcerts` set `revoked`='1970-01-01 10:00:01', `disablelogin`=1 where `id`='".$drow['id']."'");
1082 }
1083 }
1084
1085 function revoke_all_server_cert($domainid){
1086 //revokes all server certs for an domain
1087 $domainid = intval($domainid);
1088 $query =
1089 "select `domaincerts`.`id`
1090 from `domaincerts`
1091 where `domaincerts`.`domid` = '$domainid'
1092 union distinct
1093 select `domaincerts`.`id`
1094 from `domaincerts`, `domlink`
1095 where `domaincerts`.`id` = `domlink`.`certid`
1096 and `domlink`.`domid` = '$domainid'";
1097 $dres = mysql_query($query);
1098 while($drow = mysql_fetch_assoc($dres))
1099 {
1100 mysql_query(
1101 "update `domaincerts`
1102 set `revoked`='1970-01-01 10:00:01'
1103 where `id` = '".$drow['id']."'
1104 and `revoked` = 0");
1105 }
1106 }
1107
1108 function revoke_all_private_cert($uid){
1109 //revokes all certificates linked to a personal accounts
1110 //gpg revokation needs to be added to a later point
1111 $uid=intval($uid);
1112 $query = "select `id` from `email` where `memid`='".$uid."'";
1113 $res=mysql_query($query);
1114 while($row = mysql_fetch_assoc($res)){
1115 revoke_all_client_cert($row['id']);
1116 }
1117
1118
1119 $query = "select `id` from `domains` where `memid`='".$uid."'";
1120 $res=mysql_query($query);
1121 while($row = mysql_fetch_assoc($res)){
1122 revoke_all_server_cert($row['id']);
1123 }
1124 }
1125
1126 /**
1127 * check_date_format()
1128 * checks if the date is entered in the right date format YYYY-MM-DD and
1129 * if the date is after the 1st January of the given year
1130 *
1131 * @param mixed $date
1132 * @param integer $year
1133 * @return
1134 */
1135 function check_date_format($date, $year=2000){
1136 if (!strpos($date,'-')) {
1137 return FALSE;
1138 }
1139 $arr=explode('-',$date);
1140
1141 if ((count($arr)!=3)) {
1142 return FALSE;
1143 }
1144 if (intval($arr[0])<=$year) {
1145 return FALSE;
1146 }
1147 if (intval($arr[1])>12 or intval($arr[1])<=0) {
1148 return FALSE;
1149 }
1150 if (intval($arr[2])>31 or intval($arr[2])<=0) {
1151 return FALSE;
1152 }
1153
1154 return checkdate( intval($arr[1]), intval($arr[2]), intval($arr[0]));
1155
1156 }
1157
1158 /**
1159 * check_date_difference()
1160 * returns false if the date is larger then today + time diffrence
1161 *
1162 * @param mixed $date
1163 * @param integer $diff
1164 * @return
1165 */
1166 function check_date_difference($date, $diff=1){
1167 return (strtotime($date)<=time()+$diff*86400);
1168 }