includes/notary.inc.php

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