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