diff options
| -rw-r--r-- | includes/notary.inc.php | 601 | ||||
| -rw-r--r-- | includes/wot.inc.php | 53 | ||||
| -rw-r--r-- | pages/account/12.php | 10 | ||||
| -rw-r--r-- | pages/account/18.php | 10 | ||||
| -rwxr-xr-x[-rw-r--r--] | pages/account/22.php | 12 | ||||
| -rwxr-xr-x[-rw-r--r--] | pages/account/25.php | 28 | ||||
| -rwxr-xr-x[-rw-r--r--] | pages/account/43.php | 243 | ||||
| -rw-r--r-- | pages/account/5.php | 7 | ||||
| -rw-r--r-- | pages/index/1.php | 42 | ||||
| -rw-r--r-- | scripts/37de-blit2011-email.txt | 18 | ||||
| -rw-r--r-- | scripts/37de-blit2011-mail.php.txt | 106 |
11 files changed, 1062 insertions, 68 deletions
diff --git a/includes/notary.inc.php b/includes/notary.inc.php new file mode 100644 index 0000000..95cd889 --- /dev/null +++ b/includes/notary.inc.php @@ -0,0 +1,601 @@ +<? /* + LibreSSL - CAcert web application + Copyright (C) 2004-2011 CAcert Inc. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA +*/ + + function query_init ($query) + { + return mysql_query($query); + } + + function query_getnextrow ($res) + { + $row1 = mysql_fetch_assoc($res); + return $row1; + } + + function query_get_number_of_rows ($resultset) + { + return intval(mysql_num_rows($resultset)); + } + + function get_number_of_assurances ($userid) + { + $res = query_init ("SELECT count(*) AS `list` FROM `notary` + WHERE `method` = 'Face to Face Meeting' AND `from`='".intval($userid)."' "); + $row = query_getnextrow($res); + + return intval($row['list']); + } + + function get_number_of_assurees ($userid) + { + $res = query_init ("SELECT count(*) AS `list` FROM `notary` + WHERE `method` = 'Face to Face Meeting' AND `to`='".intval($userid)."' "); + $row = query_getnextrow($res); + + return intval($row['list']); + } + + function get_top_assurer_position ($no_of_assurances) + { + $res = query_init ("SELECT count(*) AS `list` FROM `notary` + WHERE `method` = 'Face to Face Meeting' + GROUP BY `from` HAVING count(*) > '".intval($no_of_assurances)."'"); + return intval(query_get_number_of_rows($res)+1); + } + + function get_top_assuree_position ($no_of_assurees) + { + $res = query_init ("SELECT count(*) AS `list` FROM `notary` + WHERE `method` = 'Face to Face Meeting' + GROUP BY .`to` HAVING count(*) > '".intval($no_of_assurees)."'"); + return intval(query_get_number_of_rows($res)+1); + } + + function get_given_assurances ($userid) + { + $res = query_init ("select * from `notary` where `from`='".intval($userid)."' and `from` != `to` order by `id` asc"); + return $res; + } + + function get_received_assurances ($userid) + { + $res = query_init ("select * from `notary` where `to`='".intval($userid)."' and `from` != `to` order by `id` asc "); + return $res; + } + + function get_given_assurances_summary ($userid) + { + $res = query_init ("select count(*) as number,points,awarded,method from notary where `from`='".intval($userid)."' group by points,awarded,method"); + return $res; + } + + function get_received_assurances_summary ($userid) + { + $res = query_init ("select count(*) as number,points,awarded,method from notary where `to`='".intval($userid)."' group by points,awarded,method"); + return $res; + } + + function get_user ($userid) + { + $res = query_init ("select * from `users` where `id`='".intval($userid)."'"); + return mysql_fetch_assoc($res); + } + + function get_cats_state ($userid) + { + + $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 + WHERE `cats_passed`.`user_id` = '".intval($userid)."'"); + return mysql_num_rows($res); + } + + function calc_experience ($row,&$points,&$experience,&$sum_experience,&$revoked) + { + $points += $row['awarded']; + $experience = " "; + $revoked = false; # to be coded later (after DB-upgrade) + if ($row['method'] == "Face to Face Meeting") + { + $sum_experience = $sum_experience +2; + $experience = "2"; + } + return $row['awarded']; + } + + function calc_assurances ($row,&$points,&$experience,&$sumexperience,&$awarded,&$revoked) + { + $awarded = calc_points($row); + $revoked = false; + + if ($awarded > 100) + { + $experience = $awarded - 100; // needs to be fixed in the future (limit 50 pts and/or no experience if pts > 100) + $awarded = 100; + } + else + $experience = 0; + + switch ($row['method']) + { + case 'Thawte Points Transfer': + case 'CT Magazine - Germany': + case 'Temporary Increase': // Current usage of 'Temporary Increase' may break audit aspects, needs to be reimplemented + $awarded=sprintf("<strong style='color: red'>%s</strong>",_("Revoked")); + $experience=0; + $revoked=true; + break; + default: + $points += $awarded; + } + $sumexperience = $sumexperience + $experience; + } + + + function show_user_link ($name,$userid) + { + $name = trim($name); + if($name == "") + { + if ($userid == 0) + $name = _("System"); + else + $name = _("Deleted account"); + } + else + $name = "<a href='wot.php?id=9&userid=".intval($userid)."'>".sanitizeHTML($name)."</a>"; + return $name; + } + + function show_email_link ($email,$userid) + { + $email = trim($email); + if($email != "") + $email = "<a href='account.php?id=43&userid=".intval($userid)."'>".sanitizeHTML($email)."</a>"; + return $email; + } + + function get_assurer_ranking($userid,&$num_of_assurances,&$rank_of_assurer) + { + $num_of_assurances = get_number_of_assurances (intval($userid)); + $rank_of_assurer = get_top_assurer_position($num_of_assurances); + } + + function get_assuree_ranking($userid,&$num_of_assurees,&$rank_of_assuree) + { + $num_of_assurees = get_number_of_assurees (intval($userid)); + $rank_of_assuree = get_top_assuree_position($num_of_assurees); + } + + +// ************* html table definitions ****************** + + function output_ranking($userid) + { + get_assurer_ranking($userid,$num_of_assurances,$rank_of_assurer); + get_assuree_ranking($userid,$num_of_assurees,$rank_of_assuree); + +?> +<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> + <tr> + <td class="title"><?=_("Assurer Ranking")?></td> + </tr> + <tr> + <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> + </tr> + <tr> + <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> + </tr> +</table> +<br/> +<? + } + + function output_assurances_header($title,$support) + { +?> +<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> + <tr> +<? + if ($support == "1") + { +?> + <td colspan="10" class="title"><?=$title?></td> +<? + } else { +?> + <td colspan="7" class="title"><?=$title?></td> +<? } +?> + </tr> + <tr> + <td class="DataTD"><strong><?=_("ID")?></strong></td> + <td class="DataTD"><strong><?=_("Date")?></strong></td> +<? + if ($support == "1") + { +?> + <td class="DataTD"><strong><?=_("When")?></strong></td> + <td class="DataTD"><strong><?=_("Email")?></strong></td> +<? } ?> + <td class="DataTD"><strong><?=_("Who")?></strong></td> + <td class="DataTD"><strong><?=_("Points")?></strong></td> + <td class="DataTD"><strong><?=_("Location")?></strong></td> + <td class="DataTD"><strong><?=_("Method")?></strong></td> + <td class="DataTD"><strong><?=_("Experience Points")?></strong></td> +<? + if ($support == "1") + { +?> + <td class="DataTD"><strong><?=_("Revoke")?></strong></td> +<? + } +?> + </tr> +<? + } + + function output_assurances_footer($points_txt,$points,$experience_txt,$sumexperience,$support) + { +?> + <tr> + <td class="DataTD" colspan="5"><strong><?=$points_txt?>:</strong></td> + <td class="DataTD"><?=$points?></td> + <td class="DataTD"> </td> + <td class="DataTD"><strong><?=$experience_txt?>:</strong></td> + <td class="DataTD"><?=$sumexperience?></td> +<? + if ($support == "1") + { +?> + <td class="DataTD"> </td> +<? + } +?> + + </tr> +</table> +<br/> +<? + } + + function output_assurances_row($assuranceid,$date,$when,$email,$name,$awarded,$points,$location,$method,$experience,$userid,$support,$revoked) + { + + $tdstyle=""; + $emopen=""; + $emclose=""; + + if ($awarded == $points) + { + if ($awarded == "0") + { + $tdstyle="style='background-color: #ffff80'"; + $emopen="<em>"; + $emclose="</em>"; + } + } +?> + <tr> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$assuranceid?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$date?><?=$emclose?></td> +<? + if ($support == "1") + { +?> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$when?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$email?><?=$emclose?></td> +<? } +?> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$name?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$awarded?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$location?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$method?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$experience?><?=$emclose?></td> +<? + if ($support == "1") + { + if ($revoked == true) + { +?> + <td class="DataTD" <?=$tdstyle?>> </td> +<? } else { +?> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><a href="account.php?id=43&userid=<?=intval($userid)?>&assurance=<?=intval($assuranceid)?>&csrf=<?=make_csrf('admdelassurance')?>" onclick="return confirm('<?=_("Are you sure you want to revoke this assurance?")?>');"><?=_("Revoke")?></a><?=$emclose?></td> +<? + } + } +?> + </tr> +<? + } + + function output_summary_header() + { +?> +<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> + <tr> + <td colspan="4" class="title"><?=_("Summary of your Points")?></td> + </tr> + <tr> + <td class="DataTD"><strong><?=_("Description")?></strong></td> + <td class="DataTD"><strong><?=_("Points")?></strong></td> + <td class="DataTD"><strong><?=_("Countable Points")?></strong></td> + <td class="DataTD"><strong><?=_("Remark")?></strong></td> + </tr> +<? + } + + function output_summary_footer() + { +?> +</table> +<br/> +<? + } + + function output_summary_row($title,$points,$points_countable,$remark) + { +?> + <tr> + <td class="DataTD"><strong><?=$title?></strong></td> + <td class="DataTD"><?=$points?></td> + <td class="DataTD"><?=$points_countable?></td> + <td class="DataTD"><?=$remark?></td> + </tr> +<? + } + + +// ************* output given assurances ****************** + + function output_given_assurances_content($userid,&$points,&$sum_experience,$support) + { + $points = 0; + $sumexperience = 0; + $res = get_given_assurances(intval($userid)); + while($row = mysql_fetch_assoc($res)) + { + $fromuser = get_user (intval($row['to'])); + calc_experience ($row,$points,$experience,$sum_experience,$revoked); + $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['to'])); + $email = show_email_link ($fromuser['email'],intval($row['to'])); + output_assurances_row (intval($row['id']),$row['date'],$row['when'],$email,$name,intval($row['awarded']),intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience,$userid,$support,$revoked); + } + } + +// ************* output received assurances ****************** + + function output_received_assurances_content($userid,&$points,&$sum_experience,$support) + { + $points = 0; + $sumexperience = 0; + $res = get_received_assurances(intval($userid)); + while($row = mysql_fetch_assoc($res)) + { + $fromuser = get_user (intval($row['from'])); + calc_assurances ($row,$points,$experience,$sum_experience,$awarded,$revoked); + $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['from'])); + $email = show_email_link ($fromuser['email'],intval($row['from'])); + 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); + } + } + +// ************* output summary table ****************** + + function check_date_limit ($userid,$age) + { + $dob = date("Y-m-d", mktime(0,0,0,date("m"),date("d"),date("Y")-$age)); + $res = query_init ("select id from `users` where `id`='".$userid."' and `dob` < '$dob'"); + return intval(query_get_number_of_rows($res)); + } + + function calc_points($row) + { + $awarded = intval($row['awarded']); + if ($awarded == "") + $awarded = 0; + if (intval($row['points']) < $awarded) + $points = $awarded; // if 'sum of added points' > 100, awarded shows correct value + else + $points = intval($row['points']); // on very old assurances, awarded is '0' instead of correct value + switch ($row['method']) + { + case 'Thawte Points Transfer': // revoke all Thawte-points (as per arbitration) + case 'CT Magazine - Germany': // revoke c't (only one test-entry) + case 'Temporary Increase': // revoke 'temporary increase' (Current usage breaks audit aspects, needs to be reimplemented) + $points = 0; + break; + case 'Administrative Increase': // ignore AI with 2 points or less (historical for experiance points, now other calculation) + if ($points <= 2) // maybe limit to 35/50 pts in the future? + $points = 0; + break; + case 'Unknown': // to be revoked in the future? limit to max 50 pts? + case 'Trusted Third Parties': // to be revoked in the future? limit to max 35 pts? + case '': // to be revoked in the future? limit to max 50 pts? + case 'Face to Face Meeting': // normal assurances, limit to 35/50 pts in the future? + break; + default: // should never happen ... ;-) + $points = 0; + } + if ($points < 0) // ignore negative points (bug needs to be fixed) + $points = 0; + return $points; + } + + function max_points($userid) + { + return output_summary_content ($userid,0); + } + + function output_summary_content($userid,$display_output) + { + $sum_points = 0; + $sum_experience = 0; + $sum_experience_other = 0; + $max_points = 100; + $max_experience = 50; + + $experience_limit_reached_txt = _("Limit reached"); + + if (check_date_limit($userid,18) != 1) + { + $max_experience = 10; + $experience_limit_reached_txt = _("Limit given by PoJAM reached"); + } + if (check_date_limit($userid,14) != 1) + { + $max_experience = 0; + $experience_limit_reached_txt = _("Limit given by PoJAM reached"); + } + + $res = get_received_assurances_summary($userid); + while($row = mysql_fetch_assoc($res)) + { + $points = calc_points ($row); + + if ($points > $max_points) // limit to 100 points, above is experience (needs to be fixed) + { + $sum_experience_other = $sum_experience_other+($points-$max_points)*intval($row['number']); + $points = $max_points; + } + $sum_points += $points*intval($row['number']); + } + + $res = get_given_assurances_summary($userid); + while($row = mysql_fetch_assoc($res)) + { + switch ($row['method']) + { + case 'Face to Face Meeting': // count Face to Face only + $sum_experience += 2*intval($row['number']); + break; + } + + } + + if ($sum_points > $max_points) + { + $sum_points_countable = $max_points; + $remark_points = _("Limit reached"); + } + else + { + $sum_points_countable = $sum_points; + $remark_points = " "; + } + if ($sum_experience > $max_experience) + { + $sum_experience_countable = $max_experience; + $remark_experience = $experience_limit_reached_txt; + } + else + { + $sum_experience_countable = $sum_experience; + $remark_experience = " "; + } + + if ($sum_experience_countable + $sum_experience_other > $max_experience) + { + $sum_experience_other_countable = $max_experience-$sum_experience_countable; + $remark_experience_other = $experience_limit_reached_txt; + } + else + { + $sum_experience_other_countable = $sum_experience_other; + $remark_experience_other = " "; + } + + if ($sum_points_countable < $max_points) + { + if ($sum_experience_countable != 0) + $remark_experience = _("Points on hold due to less assurance points"); + $sum_experience_countable = 0; + if ($sum_experience_other_countable != 0) + $remark_experience_other = _("Points on hold due to less assurance points"); + $sum_experience_other_countable = 0; + } + + $issue_points = 0; + $cats_test_passed = get_cats_state ($userid); + if ($cats_test_passed == 0) + { + $issue_points_txt = "<strong style='color: red'>"._("You have to pass the CAcert Assurer Challenge (CATS-Test) to be an Assurer")."</strong>"; + if ($sum_points_countable < $max_points) + { + $issue_points_txt = "<strong style='color: red'>"; + $issue_points_txt .= sprintf(_("You need %s assurance points and the passed CATS-Test to be an Assurer"), intval($max_points)); + $issue_points_txt .= "</strong>"; + } + } + else + { + $experience_total = $sum_experience_countable+$sum_experience_other_countable; + $issue_points_txt = ""; + if ($sum_points_countable == $max_points) + $issue_points = 10; + if ($experience_total >= 10) + $issue_points = 15; + if ($experience_total >= 20) + $issue_points = 20; + if ($experience_total >= 30) + $issue_points = 25; + if ($experience_total >= 40) + $issue_points = 30; + if ($experience_total >= 50) + $issue_points = 35; + if ($issue_points != 0) + $issue_points_txt = sprintf(_("You may issue up to %s points"),$issue_points); + } + if ($display_output) + { + output_summary_row (_("Assurance Points you received"),$sum_points,$sum_points_countable,$remark_points); + output_summary_row (_("Total Experience Points by Assurance"),$sum_experience,$sum_experience_countable,$remark_experience); + output_summary_row (_("Total Experience Points (other ways)"),$sum_experience_other,$sum_experience_other_countable,$remark_experience_other); + output_summary_row (_("Total Points")," ",$sum_points_countable + $sum_experience_countable + $sum_experience_other_countable,$issue_points_txt); + } + return $issue_points; + } + + function output_given_assurances($userid,$support) + { + output_assurances_header(_("Assurance Points You Issued"),$support); + output_given_assurances_content($userid,$points,$sum_experience,$support); + output_assurances_footer(_("Total Points Issued"),$points,_("Total Experience Points"),$sum_experience,$support); + } + + function output_received_assurances($userid,$support) + { + output_assurances_header(_("Your Assurance Points"),$support); + output_received_assurances_content($userid,$points,$sum_experience,$support); + output_assurances_footer(_("Total Assurance Points"),$points,_("Total Experience Points"),$sum_experience,$support); + } + + function output_summary($userid) + { + output_summary_header(); + output_summary_content($userid,1); + output_summary_footer(); + } + + function output_end_of_page() + { +?> + <p>[ <a href='javascript:history.go(-1)'><?=_("Go Back")?></a> ]</p> +<? + } +?> diff --git a/includes/wot.inc.php b/includes/wot.inc.php index 05ce449..9cc100d 100644 --- a/includes/wot.inc.php +++ b/includes/wot.inc.php @@ -147,7 +147,12 @@ { $name = trim($name); if($name == "") - $name = _("Deleted before Verification"); + { + if ($userid == 0) + $name = _("System"); + else + $name = _("Deleted account"); + } else $name = "<a href='wot.php?id=9&userid=".intval($userid)."'>$name</a>"; return $name; @@ -223,17 +228,32 @@ <? } - function output_assurances_row($assuranceid,$date,$name,$points,$location,$method,$experience) + function output_assurances_row($assuranceid,$date,$name,$awarded,$points,$location,$method,$experience) + { + + $tdstyle=""; + $emopen=""; + $emclose=""; + + if ($awarded == $points) { + if ($awarded == "0") + { + $tdstyle="style='background-color: #ffff80'"; + $emopen="<em>"; + $emclose="</em>"; + } + } + ?> <tr> - <td class="DataTD"><?=$assuranceid?></td> - <td class="DataTD"><?=$date?></td> - <td class="DataTD"><?=$name?></td> - <td class="DataTD"><?=$points?></td> - <td class="DataTD"><?=$location?></td> - <td class="DataTD"><?=$method?></td> - <td class="DataTD"><?=$experience?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$assuranceid?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$date?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$name?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$awarded?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$location?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$method?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$experience?><?=$emclose?></td> </tr> <? } @@ -287,7 +307,7 @@ $fromuser = get_user (intval($row['to'])); calc_experience ($row,$points,$experience,$sum_experience); $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['to'])); - output_assurances_row (intval($row['id']),$row['date'],$name,intval($row['awarded']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience); + output_assurances_row (intval($row['id']),$row['date'],$name,intval($row['awarded']),intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience); } } @@ -303,7 +323,7 @@ $fromuser = get_user (intval($row['from'])); calc_assurances ($row,$points,$experience,$sum_experience,$awarded); $name = show_user_link ($fromuser['fname']." ".$fromuser['lname'],intval($row['from'])); - output_assurances_row (intval($row['id']),$row['date'],$name,$awarded,$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience); + output_assurances_row (intval($row['id']),$row['date'],$name,$awarded,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience); } } @@ -318,8 +338,11 @@ function calc_points($row) { - if (intval($row['points']) < intval($row['awarded'])) - $points = intval($row['awarded']); // if 'sum of added points' > 100, awarded shows correct value + $awarded = intval($row['awarded']); + if ($awarded == "") + $awarded = 0; + if (intval($row['points']) < $awarded) + $points = $awarded; // if 'sum of added points' > 100, awarded shows correct value else $points = intval($row['points']); // on very old assurances, awarded is '0' instead of correct value switch ($row['method']) @@ -333,8 +356,8 @@ if ($points <= 2) // maybe limit to 35/50 pts in the future? $points = 0; break; - case 'unknown': // to be revoked in the future? limit to max 50 pts? - case 'Trusted 3rd Parties': // to be revoked in the future? limit to max 35 pts? + case 'Unknown': // to be revoked in the future? limit to max 50 pts? + case 'Trusted Third Parties': // to be revoked in the future? limit to max 35 pts? case '': // to be revoked in the future? limit to max 50 pts? case 'Face to Face Meeting': // normal assurances, limit to 35/50 pts in the future? break; diff --git a/pages/account/12.php b/pages/account/12.php index 40135be..44926ca 100644 --- a/pages/account/12.php +++ b/pages/account/12.php @@ -19,12 +19,13 @@ <form method="post" action="account.php"> <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> <tr> - <td colspan="5" class="title"><?=_("Domain Certificates")?> - <a href="account.php?id=12&viewall=<?=!$viewall?>"><?=_("View all certificates")?></a></td> + <td colspan="6" class="title"><?=_("Domain Certificates")?> - <a href="account.php?id=12&viewall=<?=!$viewall?>"><?=_("View all certificates")?></a></td> </tr> <tr> <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td> <td class="DataTD"><?=_("Status")?></td> <td class="DataTD"><?=_("CommonName")?></td> + <td class="DataTD"><?=_("SerialNumber")?></td> <td class="DataTD"><?=_("Revoked")?></td> <td class="DataTD"><?=_("Expires")?></td> </tr> @@ -33,7 +34,7 @@ UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`, UNIX_TIMESTAMP(`domaincerts`.`expire`) as `expired`, `domaincerts`.`expire` as `expires`, `revoked` as `revoke`, - UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `domaincerts`.`id` as `id` + UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `domaincerts`.`serial`, `domaincerts`.`id` as `id` from `domaincerts`,`domains` where `memid`='".intval($_SESSION['profile']['id'])."' and `deleted`=0 and `domaincerts`.`domid`=`domains`.`id` "; if($viewall != 1) @@ -48,7 +49,7 @@ { ?> <tr> - <td colspan="5" class="DataTD"><?=_("No domains are currently listed.")?></td> + <td colspan="6" class="DataTD"><?=_("No domains are currently listed.")?></td> </tr> <? } else { while($row = mysql_fetch_assoc($res)) @@ -74,12 +75,13 @@ <? } ?> <td class="DataTD"><?=$verified?></td> <td class="DataTD"><a href="account.php?id=15&cert=<?=$row['id']?>"><?=$row['CN']?></a></td> + <td class="DataTD"><?=$row['serial']?></td> <td class="DataTD"><?=$row['revoke']?></td> <td class="DataTD"><?=$row['expires']?></td> </tr> <? } ?> <tr> - <td class="DataTD" colspan="5"><input type="submit" name="renew" value="<?=_("Renew")?>">     + <td class="DataTD" colspan="6"><input type="submit" name="renew" value="<?=_("Renew")?>">     <input type="submit" name="revoke" value="<?=_("Revoke/Delete")?>"></td> </tr> <? } ?> diff --git a/pages/account/18.php b/pages/account/18.php index 5ee1a3b..47fac9e 100644 --- a/pages/account/18.php +++ b/pages/account/18.php @@ -19,12 +19,13 @@ <form method="post" action="account.php"> <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> <tr> - <td colspan="5" class="title"><?=_("Client Certificates")?> - <a href="account.php?id=18&viewall=<?=!$viewall?>"><?=_("View all certificates")?></a></td> + <td colspan="6" class="title"><?=_("Client Certificates")?> - <a href="account.php?id=18&viewall=<?=!$viewall?>"><?=_("View all certificates")?></a></td> </tr> <tr> <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td> <td class="DataTD"><?=_("Status")?></td> <td class="DataTD"><?=_("CommonName")?></td> + <td class="DataTD"><?=_("SerialNumber")?></td> <td class="DataTD"><?=_("Revoked")?></td> <td class="DataTD"><?=_("Expires")?></td> @@ -33,7 +34,7 @@ UNIX_TIMESTAMP(`expire`) - UNIX_TIMESTAMP() as `timeleft`, UNIX_TIMESTAMP(`expire`) as `expired`, `expire` as `expires`, `revoked` as `revoke`, - UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `id` + UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `serial`, `id` from `orgemailcerts`, `org` where `memid`='".intval($_SESSION['profile']['id'])."' and `org`.`orgid`=`orgemailcerts`.`orgid` "; @@ -48,7 +49,7 @@ { ?> <tr> - <td colspan="5" class="DataTD"><?=_("No client certificates are currently listed.")?></td> + <td colspan="6" class="DataTD"><?=_("No client certificates are currently listed.")?></td> </tr> <? } else { while($row = mysql_fetch_assoc($res)) @@ -78,12 +79,13 @@ <td class="DataTD"><?=$verified?></td> <td class="DataTD"><a href="account.php?id=19&cert=<?=$row['id']?>"><?=$row['CN']?></a></td> <? } ?> + <td class="DataTD"><?=$row['serial']?></td> <td class="DataTD"><?=$row['revoke']?></td> <td class="DataTD"><?=$row['expires']?></td> </tr> <? } ?> <tr> - <td class="DataTD" colspan="5"><input type="submit" name="renew" value="<?=_("Renew")?>">     + <td class="DataTD" colspan="6"><input type="submit" name="renew" value="<?=_("Renew")?>">     <input type="submit" name="revoke" value="<?=_("Revoke/Delete")?>"></td> </tr> <? } ?> diff --git a/pages/account/22.php b/pages/account/22.php index 565cb5f..9df8200 100644..100755 --- a/pages/account/22.php +++ b/pages/account/22.php @@ -19,12 +19,13 @@ <form method="post" action="account.php"> <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> <tr> - <td colspan="5" class="title"><?=_("Domain Certificates")?> - <a href="account.php?id=22&viewall=<?=!$viewall?>"><?=_("View all certificates")?></a></td> + <td colspan="6" class="title"><?=_("Domain Certificates")?> - <a href="account.php?id=22&viewall=<?=!$viewall?>"><?=_("View all certificates")?></a></td> </tr> <tr> <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td> <td class="DataTD"><?=_("Status")?></td> <td class="DataTD"><?=_("CommonName")?></td> + <td class="DataTD"><?=_("SerialNumber")?></td> <td class="DataTD"><?=_("Revoked")?></td> <td class="DataTD"><?=_("Expires")?></td> @@ -33,7 +34,9 @@ UNIX_TIMESTAMP(`orgdomaincerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`, UNIX_TIMESTAMP(`orgdomaincerts`.`expire`) as `expired`, `orgdomaincerts`.`expire` as `expires`, `revoked` as `revoke`, - UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `orgdomaincerts`.`id` as `id` + UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, + `orgdomaincerts`.`serial`, + `orgdomaincerts`.`id` as `id` from `orgdomaincerts`,`org` where `org`.`memid`='".intval($_SESSION['profile']['id'])."' and `orgdomaincerts`.`orgid`=`org`.`orgid` "; if($viewall != 1) @@ -48,7 +51,7 @@ { ?> <tr> - <td colspan="5" class="DataTD"><?=_("No domains are currently listed.")?></td> + <td colspan="6" class="DataTD"><?=_("No domains are currently listed.")?></td> </tr> <? } else { while($row = mysql_fetch_assoc($res)) @@ -74,12 +77,13 @@ <? } ?> <td class="DataTD"><?=$verified?></td> <td class="DataTD"><a href="account.php?id=23&cert=<?=$row['id']?>"><?=$row['CN']?></a></td> + <td class="DataTD"><?=$row['serial']?></td> <td class="DataTD"><?=$row['revoke']?></td> <td class="DataTD"><?=$row['expires']?></td> </tr> <? } ?> <tr> - <td class="DataTD" colspan="5"><input type="submit" name="renew" value="<?=_("Renew")?>">     + <td class="DataTD" colspan="6"><input type="submit" name="renew" value="<?=_("Renew")?>">     <input type="submit" name="revoke" value="<?=_("Revoke/Delete")?>"></td> </tr> <? } ?> diff --git a/pages/account/25.php b/pages/account/25.php index ab0e6b2..a70f608 100644..100755 --- a/pages/account/25.php +++ b/pages/account/25.php @@ -19,6 +19,15 @@ <tr> <td colspan="5" class="title"><?=_("Organisations")?></td> </tr> + +<tr> + <td colspan="5" class="title"><?=_("Order by:")?> + <a href="account.php?id=25"><?=_("Id")?></a> - + <a href="account.php?id=25&ord=1"><?=_("Country")?></a> - + <a href="account.php?id=25&ord=2"><?=_("Name")?></a> + </td> +</tr> + <tr> <td class="DataTD" width="350"><?=_("Organisation")?></td> <td class="DataTD"><?=_("Domains")?></td> @@ -27,7 +36,24 @@ <td class="DataTD"><?=_("Delete")?></td> </tr> <? - $query = "select * from `orginfo` ORDER BY `id`"; + $order = 0; + if (array_key_exists('ord',$_REQUEST)) { + $order = intval($_REQUEST['ord']); + } + + $order_by = "`id`"; + switch ($order) { + case 1: + $order_by = "`C`,`O`"; + break; + case 2: + $order_by = "`O`"; + break; + // the 0 and default case are handled by the preset + } + + // Safe because $order_by only contains fixed strings + $query = sprintf("select * from `orginfo` ORDER BY %s", $order_by); $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { diff --git a/pages/account/43.php b/pages/account/43.php index a286ec6..f058770 100644..100755 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -16,6 +16,9 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA */ ?> <? +include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php"); + + if(array_key_exists('assurance',$_REQUEST) && $_REQUEST['assurance'] > 0) { $assurance = mysql_escape_string(intval($_REQUEST['assurance'])); @@ -38,14 +41,26 @@ //if(!strstr($email, "%")) // $emailsearch = "%$email%"; - if(intval($email) > 0) - $emailsearch = ""; - - $query = "select `users`.`id` as `id`, `email`.`email` as `email` from `users`,`email` - where `users`.`id`=`email`.`memid` and - (`email`.`email` like '$emailsearch' or `email`.`id`='$email' or `users`.`id`='$email') and - `email`.`hash`='' and `email`.`deleted`=0 and `users`.`deleted`=0 - group by `users`.`id` limit 100"; + // bug-975 ted+uli changes --- begin + if(preg_match("/^[0-9]+$/", $email)) { + // $email consists of digits only ==> search for IDs + // Be defensive here (outer join) if primary mail is not listed in email table + $query = "select `users`.`id` as `id`, `email`.`email` as `email` + from `users` left outer join `email` on (`users`.`id`=`email`.`memid`) + where (`email`.`id`='$email' or `users`.`id`='$email') + and `users`.`deleted`=0 + group by `users`.`id` limit 100"; + } else { + // $email contains non-digits ==> search for mail addresses + // Be defensive here (outer join) if primary mail is not listed in email table + $query = "select `users`.`id` as `id`, `email`.`email` as `email` + from `users` left outer join `email` on (`users`.`id`=`email`.`memid`) + where (`email`.`email` like '$emailsearch' + or `users`.`email` like '$emailsearch') + and `users`.`deleted`=0 + group by `users`.`id` limit 100"; + } + // bug-975 ted+uli changes --- end $res = mysql_query($query); if(mysql_num_rows($res) > 1) { ?> <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> @@ -138,7 +153,7 @@ { echo "<option"; if($day == $i) - echo " selected='selected'"; + echo " selected='selected'"; echo ">$i</option>"; } ?> @@ -178,7 +193,7 @@ <td class="DataTD"><a href="account.php?id=43&codesign=<?=$row['id']?>&csrf=<?=make_csrf('admcodesign')?>"><?=$row['codesign']?></a></td> </tr> <tr> - <td class="DataTD"><?=_("Org Admin")?>:</td> + <td class="DataTD"><?=_("Org Assurer")?>:</td> <td class="DataTD"><a href="account.php?id=43&orgadmin=<?=$row['id']?>&csrf=<?=make_csrf('admorgadmin')?>"><?=$row['orgadmin']?></a></td> </tr> <tr> @@ -317,16 +332,178 @@ </table> <br> <? } ?> +<? // Begin - Debug infos ?> +<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> + <tr> + <td colspan="2" class="title"><?=_("Account State")?></td> + </tr> -<? - if(array_key_exists('assuredto',$_GET) && $_GET['assuredto'] == "yes") { +<? + // --- bug-975 begin --- + // potential db inconsistency like in a20110804.1 + // Admin console -> don't list user account + // User login -> impossible + // Assurer, assure someone -> user displayed + /* regular user account search with regular settings + + --- Admin Console find user query + $query = "select `users`.`id` as `id`, `email`.`email` as `email` from `users`,`email` + where `users`.`id`=`email`.`memid` and + (`email`.`email` like '$emailsearch' or `email`.`id`='$email' or `users`.`id`='$email') and + `email`.`hash`='' and `email`.`deleted`=0 and `users`.`deleted`=0 + group by `users`.`id` limit 100"; + => requirements + 1. email.hash = '' + 2. email.deleted = 0 + 3. users.deleted = 0 + 4. email.email = primary-email (???) or'd + not covered by admin console find user routine, but may block users login + 5. users.verified = 0|1 + further "special settings" + 6. users.locked (setting displayed in display form) + 7. users.assurer_blocked (setting displayed in display form) + + --- User login user query + select * from `users` where `email`='$email' and (`password`=old_password('$pword') or `password`=sha1('$pword') or + `password`=password('$pword')) and `verified`=1 and `deleted`=0 and `locked`=0 + => requirements + 1. users.verified = 1 + 2. users.deleted = 0 + 3. users.locked = 0 + 4. users.email = primary-email + + --- Assurer, assure someone find user query + select * from `users` where `email`='".mysql_escape_string(stripslashes($_POST['email']))."' + and `deleted`=0 + => requirements + 1. users.deleted = 0 + 2. users.email = primary-email + Admin User Assurer + bit Console Login assure someone + + 1. email.hash = '' Yes No No + 2. email.deleted = 0 Yes No No + 3. users.deleted = 0 Yes Yes Yes + 4. users.verified = 1 No Yes No + 5. users.locked = 0 No Yes No + 6. users.email = prim-email No Yes Yes + 7. email.email = prim-email Yes No No + + full usable account needs all 7 requirements fulfilled + so if one setting isn't set/cleared there is an inconsistency either way + if eg email.email is not avail, admin console cannot open user info + but user can login and assurer can display user info + if user verified is not set to 1, admin console displays user record + but user cannot login, but assurer can search for the user and the data displays + + consistency check: + 1. search primary-email in users.email + 2. search primary-email in email.email + 3. userid = email.memid + 4. check settings from table 1. - 5. + + */ + + $inconsistency = 0; + $inconsistencydisp = ""; + $inccause = ""; + // current userid intval($row['id']) + $query = "select `email` as `uemail`, `deleted` as `udeleted`, `verified`, `locked` + from `users` where `id`='".intval($row['id'])."' "; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $uemail = $drow['uemail']; + $udeleted = $drow['udeleted']; + $uverified = $drow['verified']; + $ulocked = $drow['locked']; + + $query = "select `hash`, `email` as `eemail` from `email` + where `memid`='".intval($row['id'])."' and + `email` ='".$uemail."' and + `deleted` = 0"; + $dres = mysql_query($query); + if ($drow = mysql_fetch_assoc($dres)) { + $drow['edeleted'] = 0; + } else { + // try if there are deleted entries + $query = "select `hash`, `deleted` as `edeleted`, `email` as `eemail` from `email` + where `memid`='".intval($row['id'])."' and + `email` ='".$uemail."'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + } + + if ($drow) { + $eemail = $drow['eemail']; + $edeleted = $drow['edeleted']; + $ehash = $drow['hash']; + if ($udeleted!=0) { + $inconsistency += 1; + $inccause .= (empty($inccause)?"":"<br>")._("Users record set to deleted"); + } + if ($uverified!=1) { + $inconsistency += 2; + $inccause .= (empty($inccause)?"":"<br>")._("Users record verified not set"); + } + if ($ulocked!=0) { + $inconsistency += 4; + $inccause .= (empty($inccause)?"":"<br>")._("Users record locked set"); + } + if ($edeleted!=0) { + $inconsistency += 8; + $inccause .= (empty($inccause)?"":"<br>")._("Email record set deleted"); + } + if ($ehash!='') { + $inconsistency += 16; + $inccause .= (empty($inccause)?"":"<br>")._("Email record hash not unset"); + } + } else { + $inconsistency = 32; + $inccause = _("Prim. email, Email record doesn't exist"); + } + if ($inconsistency>0) { + // $inconsistencydisp = _("Yes"); ?> + <tr> + <td class="DataTD"><?=_("Account inconsistency")?>:</td> + <td class="DataTD"><?=$inccause?><br>code: <?=$inconsistency?></td> + </tr> + <tr> + <td colspan="2" class="DataTD" style="max-width: 75ex"> + <?=_("Account inconsistency can cause problems in daily account ". + "operations and needs to be fixed manually through arbitration/critical ". + "team.")?> + </td> + </tr> +<? } + + // --- bug-975 end --- +?> +</table> +<br> +<? + // End - Debug infos +?> + +<a href="account.php?id=43&userid=<?=$row['id']?>&shownotary=assuredto"><?=_("Show Assurances the user got")?></a> + (<a href="account.php?id=43&userid=<?=$row['id']?>&shownotary=assuredto15"><?=_("New calculation")?></a>) +<br /> +<a href="account.php?id=43&userid=<?=$row['id']?>&shownotary=assuredby"><?=_("Show Assurances the user gave")?></a> + (<a href="account.php?id=43&userid=<?=$row['id']?>&shownotary=assuredby15"><?=_("New calculation")?></a>) +<br /> + +<? +// if(array_key_exists('assuredto',$_GET) && $_GET['assuredto'] == "yes") { +function showassuredto() +{ +?> <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> <tr> - <td colspan="7" class="title"><?=_("Assurance Points")?></td> + <td colspan="8" class="title"><?=_("Assurance Points")?></td> </tr> <tr> + <td class="DataTD"><b><?=_("ID")?></b></td> <td class="DataTD"><b><?=_("Date")?></b></td> <td class="DataTD"><b><?=_("Who")?></b></td> <td class="DataTD"><b><?=_("Email")?></b></td> @@ -336,7 +513,7 @@ <td class="DataTD"><b><?=_("Revoke")?></b></td> </tr> <? - $query = "select * from `notary` where `to`='".intval($row['id'])."'"; + $query = "select * from `notary` where `to`='".intval($_GET['userid'])."'"; $dres = mysql_query($query); $points = 0; while($drow = mysql_fetch_assoc($dres)) @@ -345,9 +522,10 @@ $points += $drow['points']; ?> <tr> + <td class="DataTD"><?=$drow['id']?></td> <td class="DataTD"><?=sanitizeHTML($drow['date'])?></td> <td class="DataTD"><a href="wot.php?id=9&userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['fname'])." ".sanitizeHTML($fromuser['lname'])?></td> - <td class="DataTD"><a href="account.php?id=43&userid=<?=intval($drow['to'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td> + <td class="DataTD"><a href="account.php?id=43&userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td> <td class="DataTD"><?=intval($drow['points'])?></td> <td class="DataTD"><?=sanitizeHTML($drow['location'])?></td> <td class="DataTD"><?=sanitizeHTML($drow['method'])?></td> @@ -360,20 +538,18 @@ <td class="DataTD" colspan="3"> </td> </tr> </table> -<? } else { ?> - <tr> - <td class="DataTD" colspan="2"><a href="account.php?id=43&userid=<?=$row['id']?>&assuredto=yes"><?=_("Show Assurances the user got")?></a></td> - </tr> <? } ?> -<br> + <? - if(array_key_exists('assuredby',$_GET) && $_GET['assuredby'] == "yes") { +function showassuredby() +{ ?> <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> <tr> - <td colspan="7" class="title"><?=_("Assurance Points The User Issued")?></td> + <td colspan="8" class="title"><?=_("Assurance Points The User Issued")?></td> </tr> <tr> + <td class="DataTD"><b><?=_("ID")?></b></td> <td class="DataTD"><b><?=_("Date")?></b></td> <td class="DataTD"><b><?=_("Who")?></b></td> <td class="DataTD"><b><?=_("Email")?></b></td> @@ -383,7 +559,7 @@ <td class="DataTD"><b><?=_("Revoke")?></b></td> </tr> <? - $query = "select * from `notary` where `from`='".$row['id']."' and `to`!='".$row['id']."'"; + $query = "select * from `notary` where `from`='".intval($_GET['userid'])."'"; $dres = mysql_query($query); $points = 0; while($drow = mysql_fetch_assoc($dres)) @@ -392,6 +568,7 @@ $points += $drow['points']; ?> <tr> + <td class="DataTD"><?=$drow['id']?></td> <td class="DataTD"><?=$drow['date']?></td> <td class="DataTD"><a href="wot.php?id=9&userid=<?=$drow['to']?>"><?=$fromuser['fname']." ".$fromuser['lname']?></td> <td class="DataTD"><a href="account.php?id=43&userid=<?=intval($drow['to'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td> @@ -407,11 +584,21 @@ <td class="DataTD" colspan="3"> </td> </tr> </table> -<? } else { ?> - <tr> - <td class="DataTD" colspan="2"><a href="account.php?id=43&userid=<?=$row['id']?>&assuredby=yes"><?=_("Show Assurances the user gave")?></a></td> - </tr> <? } ?> <br><br> -<? } } ?> +<? } } +switch ($_GET['shownotary']) + { + case 'assuredto': showassuredto(); + break; + case 'assuredby': showassuredby(); + break; + case 'assuredto15': output_received_assurances(intval($_GET['userid']),1); + break; + case 'assuredby15': output_given_assurances(intval($_GET['userid']),1); + break; + } + + +?> diff --git a/pages/account/5.php b/pages/account/5.php index ee500c0..5c131ba 100644 --- a/pages/account/5.php +++ b/pages/account/5.php @@ -19,12 +19,13 @@ <form method="post" action="account.php"> <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper"> <tr> - <td colspan="6" class="title"><?=_("Client Certificates")?> - <a href="account.php?id=5&viewall=<?=!$viewall?>"><?=_("View all certificates")?></a></td> + <td colspan="7" class="title"><?=_("Client Certificates")?> - <a href="account.php?id=5&viewall=<?=!$viewall?>"><?=_("View all certificates")?></a></td> </tr> <tr> <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td> <td class="DataTD"><?=_("Status")?></td> <td class="DataTD"><?=_("Email Address")?></td> + <td class="DataTD"><?=_("SerialNumber")?></td> <td class="DataTD"><?=_("Revoked")?></td> <td class="DataTD"><?=_("Expires")?></td> <td class="DataTD"><?=_("Login")?></td> @@ -38,6 +39,7 @@ UNIX_TIMESTAMP(`emailcerts`.`revoked`) as `revoked`, `emailcerts`.`id`, `emailcerts`.`CN`, + `emailcerts`.`serial`, emailcerts.disablelogin as `disablelogin` from `emailcerts` where `emailcerts`.`memid`='".$_SESSION['profile']['id']."' @@ -54,7 +56,7 @@ { ?> <tr> - <td colspan="5" class="DataTD"><?=_("No client certificates are currently listed.")?></td> + <td colspan="7" class="DataTD"><?=_("No client certificates are currently listed.")?></td> </tr> <? } else { while($row = mysql_fetch_assoc($res)) @@ -84,6 +86,7 @@ <td class="DataTD"><?=$verified?></td> <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td> <? } ?> + <td class="DataTD"><?=$row['serial']?></td> <td class="DataTD"><?=$row['revoke']?></td> <td class="DataTD"><?=$row['expires']?></td> <td class="DataTD"> diff --git a/pages/index/1.php b/pages/index/1.php index f4343e7..a60a242 100644 --- a/pages/index/1.php +++ b/pages/index/1.php @@ -25,27 +25,33 @@ <form method="post" action="index.php" autocomplete="off"> <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper" width="400"> <tr> - <td colspan="2" class="title"><?=_("My Details")?></td> + <td colspan="3" class="title"><?=_("My Details")?></td> </tr> + <tr> <td class="DataTD" width="125"><?=_("First Name")?>: </td> <td class="DataTD" width="125"><input type="text" name="fname" value="<?=array_key_exists('fname',$_REQUEST)?sanitizeHTML($_REQUEST['fname']):""?>" autocomplete="off"></td> + <td rowspan="4" class="DataTD" width="125"><? printf(_("Help on Names %sin the wiki%s"),'<a href="//wiki.cacert.org/FAQ/HowToEnterNamesInJoinForm" target="_blank">','</a>')?></td> </tr> + <tr> <td class="DataTD" valign="top"><?=_("Middle Name(s)")?><br> (<?=_("optional")?>) </td> <td class="DataTD"><input type="text" name="mname" value="<?=array_key_exists('mname',$_REQUEST)?sanitizeHTML($_REQUEST['mname']):""?>" autocomplete="off"></td> </tr> + <tr> <td class="DataTD"><?=_("Last Name")?>: </td> <td class="DataTD"><input type="text" name="lname" value="<?=array_key_exists('lname',$_REQUEST)?sanitizeHTML($_REQUEST['lname']):""?>" autocomplete="off"></td> </tr> + <tr> <td class="DataTD"><?=_("Suffix")?><br> (<?=_("optional")?>)</td> - <td class="DataTD"><input type="text" name="suffix" value="<?=array_key_exists('suffix',$_REQUEST)?sanitizeHTML($_REQUEST['suffix']):""?>" autocomplete="off"><br><?=sprintf(_("Please only write %sName Suffixes%s into this field."),'<a href="http://en.wikipedia.org/wiki/Suffix_%28name%29" target="_blank">','</a>')?></td> + <td class="DataTD"><input type="text" name="suffix" value="<?=array_key_exists('suffix',$_REQUEST)?sanitizeHTML($_REQUEST['suffix']):""?>" autocomplete="off"><br><?=sprintf(_("Please only write Name Suffixes into this field."))?></td> </tr> + <tr> <td class="DataTD"><?=_("Date of Birth")?><br> (<?=_("dd/mm/yyyy")?>)</td> @@ -73,49 +79,63 @@ </select> <input type="text" name="year" value="<?=array_key_exists('year',$_SESSION['signup']) ? sanitizeHTML($_SESSION['signup']['year']):""?>" size="4" autocomplete="off"></nobr> </td> + <td class="DataTD"> </td> </tr> + <tr> <td class="DataTD"><?=_("Email Address")?>: </td> - <td class="DataTD"><input type="text" name="email" value="<?=array_key_exists('email',$_REQUEST)?sanitizeHTML($_REQUEST['email']):""?>" autocomplete="off"><br/><?=_("I own or am authorised to control this email address")?> -</td> + <td class="DataTD"><input type="text" name="email" value="<?=array_key_exists('email',$_REQUEST)?sanitizeHTML($_REQUEST['email']):""?>" autocomplete="off"></td> + <td class="DataTD"><?=_("I own or am authorised to control this email address")?></td> </tr> + <tr> <td class="DataTD"><?=_("Pass Phrase")?><font color="red">*</font>: </td> <td class="DataTD"><input type="password" name="pword1" autocomplete="off"></td> + <td class="DataTD" rowspan="2"> </td> </tr> <tr> <td class="DataTD"><?=_("Pass Phrase Again")?><font color="red">*</font>: </td> <td class="DataTD"><input type="password" name="pword2" autocomplete="off"></td> </tr> + <tr> - <td class="DataTD" colspan="2"><font color="red">*</font><?=_("Please note, in the interests of good security, the pass phrase must be made up of an upper case letter, lower case letter, number and symbol.")?></td> + <td class="DataTD" colspan="3"><font color="red">*</font><?=_("Please note, in the interests of good security, the pass phrase must be made up of an upper case letter, lower case letter, number and symbol.")?></td> </tr> + <tr> - <td class="DataTD" colspan="2"><?=_("Lost Pass Phrase Questions - Please enter five questions and your responses to be used for security verification.")?></td> + <td class="DataTD" colspan="3"><?=_("Lost Pass Phrase Questions - Please enter five questions and your responses to be used for security verification.")?></td> </tr> + <tr> <td class="DataTD">1) <input type="text" name="Q1" size="15" value="<?=array_key_exists('Q1',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q1']):""?>"></td> <td class="DataTD"><input type="text" name="A1" value="<?=array_key_exists('A1',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A1']):""?>" autocomplete="off"></td> + <td class="DataTD" rowspan="5"> </td> </tr> + <tr> <td class="DataTD">2) <input type="text" name="Q2" size="15" value="<?=array_key_exists('Q2',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q2']):""?>"></td> <td class="DataTD"><input type="text" name="A2" value="<?=array_key_exists('A2',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A2']):""?>" autocomplete="off"></td> </tr> + <tr> <td class="DataTD">3) <input type="text" name="Q3" size="15" value="<?=array_key_exists('Q3',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q3']):""?>"></td> <td class="DataTD"><input type="text" name="A3" value="<?=array_key_exists('A3',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A3']):""?>" autocomplete="off"></td> </tr> + <tr> <td class="DataTD">4) <input type="text" name="Q4" size="15" value="<?=array_key_exists('Q4',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q4']):""?>"></td> <td class="DataTD"><input type="text" name="A4" value="<?=array_key_exists('A4',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A4']):""?>" autcomplete="off"></td> </tr> + <tr> <td class="DataTD">5) <input type="text" name="Q5" size="15" value="<?=array_key_exists('Q5',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q5']):""?>"></td> <td class="DataTD"><input type="text" name="A5" value="<?=array_key_exists('A5',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A5']):""?>" autocomplete="off"></td> </tr> + <tr> - <td class="DataTD" colspan="2"><?=_("It's possible to get notifications of up and coming events and even just general announcements, untick any notifications you don't wish to receive. For country, regional and radius notifications to work you must choose your location once you've verified your account and logged in.")?></td> + <td class="DataTD" colspan="3"><?=_("It's possible to get notifications of up and coming events and even just general announcements, untick any notifications you don't wish to receive. For country, regional and radius notifications to work you must choose your location once you've verified your account and logged in.")?></td> </tr> + <tr> <td class="DataTD" valign="top"><?=_("Alert me if")?>: </td> <td class="DataTD" align="left"> @@ -123,16 +143,18 @@ <input type="checkbox" name="country" value="1" <?=array_key_exists('country',$_SESSION['signup'])? ($_SESSION['signup']['country'] == "0" ?"":"checked=\"checked\""):"checked=\"checked\"" ?>><?=_("Country Announcements")?><br> <input type="checkbox" name="regional" value="1" <?=array_key_exists('regional',$_SESSION['signup'])? ($_SESSION['signup']['regional'] == "0" ?"":"checked=\"checked\""):"checked=\"checked\"" ?>><?=_("Regional Announcements")?><br> <input type="checkbox" name="radius" value="1" <?=array_key_exists('radius',$_SESSION['signup'])? ($_SESSION['signup']['radius'] == "0" ?"":"checked=\"checked\""):"checked=\"checked\"" ?>><?=_("Within 200km Announcements")?></td> + <td class="DataTD"> </td> </tr> + <tr> - <td class="DataTD" colspan="2"><?=_("When you click on next, we will send a confirmation email to the email address you have entered above.")?></td> + <td class="DataTD" colspan="3"><?=_("When you click on next, we will send a confirmation email to the email address you have entered above.")?></td> </tr> <tr> - <td class="DataTD" colspan="2"><input type="checkbox" name="cca_agree" value="1" <?=array_key_exists('cca_agree',$_SESSION['signup'])? ($_SESSION['signup']['cca_agree'] == "1" ?"checked=\"checked\"":""):"" ?> ><?=_("I agree to the terms and conditions of the CAcert Community Agreement")?>: <a href="/policy/CAcertCommunityAgreement.php">http://www.cacert.org/policy/CAcertCommunityAgreement.php</a></td> + <td class="DataTD" colspan="3"><input type="checkbox" name="cca_agree" value="1" <?=array_key_exists('cca_agree',$_SESSION['signup'])? ($_SESSION['signup']['cca_agree'] == "1" ?"checked=\"checked\"":""):"" ?> ><?=_("I agree to the terms and conditions of the CAcert Community Agreement")?>: <a href="/policy/CAcertCommunityAgreement.php">http://www.cacert.org/policy/CAcertCommunityAgreement.php</a></td> </tr> <tr> - <td class="DataTD" colspan="2"><input type="submit" name="process" value="<?=_("Next")?>"></td> + <td class="DataTD" colspan="3"><input type="submit" name="process" value="<?=_("Next")?>"></td> </tr> </table> diff --git a/scripts/37de-blit2011-email.txt b/scripts/37de-blit2011-email.txt new file mode 100644 index 0000000..365bd5b --- /dev/null +++ b/scripts/37de-blit2011-email.txt @@ -0,0 +1,18 @@ +8. Brandenburger Linux-Infotag 2011 -- Helfer Gesucht +::::::::::::::::::::::::::::::::::::::::::::::::::::: + +Hallo CAcerties, + +am Samstag, dem 5. November 2011 moechte sich CAcert mit einem Stand auf dem 8. Brandenburger Linux-Infotag 2011 (Motto: "Freie Gedanken - Freie Systeme") praesentieren. Hierzu wurde nun im Wiki eine Organisationsseite eingerichtet, auf der Ihr Euch als Helfer eintragen koennt + http://wiki.cacert.org/events/BLIT2011 +Sofern Ihr aus Berlin, Potsdam sowie Umgebung oder von woanders kommt und Zeit wie auch Lust habt, dann tragt Euch bitte, gerne auch nur fuer einen +bestimmten Zeitraum, ein. Wer in den vergangenen Jahren dabei war, weiss vieviel Spass es allen gemacht hat! + +Auf der Veranstaltung sind Professoren, wissenschaftliche Mitarbeiter und in jedem Fall viele Studenten zu erwarten. Es waere daher super, wenn wir dort moeglichst viele von CAcert ueberzeugen koennen, um dort eine neue Keimzelle entstehen lassen zu koennen. Daher benoetigen wir mindestens drei Assurer, um 100 Punkte vergeben zu koennen. + +Wir freuen uns auf Eure Mithilfe. + + +Wiki Organisationsseite: [http://wiki.cacert.org/events/BLIT2011] + +Kontakt: events@cacert.org diff --git a/scripts/37de-blit2011-mail.php.txt b/scripts/37de-blit2011-mail.php.txt new file mode 100644 index 0000000..e0ecead --- /dev/null +++ b/scripts/37de-blit2011-mail.php.txt @@ -0,0 +1,106 @@ +#!/usr/bin/php -q +<? /* + LibreSSL - CAcert web application + Copyright (C) 2004-2009 CAcert Inc. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA +*/ + include_once("../includes/mysql.php"); + + $lines = ""; + $fp = fopen("37de-blit2011-email.txt", "r"); + while(!feof($fp)) + { + $line = trim(fgets($fp, 4096)); + $lines .= wordwrap($line, 75, "\n")."\n"; + } + fclose($fp); + + +// $locid = intval($_REQUEST['location']); +// $maxdist = intval($_REQUEST['maxdist']); +// maxdist in [Km] + $maxdist = 200; + + +// location location.ID +// verified: 29.4.09 u.schroeter +// $locid = 7902857; // Paris +// $locid = 238568; // Bielefeld +// $locid = 715191; // Hamburg +// $locid = 1102495; // London +// $locid = 520340; // Duesseldorf +// $locid = 1260319; // Muenchen +// $locid = 606058; // Frankfurt +// $locid = 1775784; // Stuttgart +// $locid = 228950; // Berlin +// $locid = 606058; // Frankfurt +// $locid = 599389; // Flensburg +// $locid = 61065; // Amsterdam, Eemnes +// $locid = 228950; // Berlin +// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, US +// $locid = 1486658; // Potsdam +// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden +// $locid = 2094781; // Mission Hills (Los Angeles), California, US +// $locid = 423655; // Copenhagen, Kobenhavn*, Denmark +// $locid = 2257312; // Sydney, New South Wales, Australia +// $locid = 572764; // Essen, Nordrhein-Westfalen, Germany +// $locid = 78; // Aachen, Nordrhein-Westfalen, Germany +// $locid = 266635; // Bonn, Nordrhein-Westfalen, Germany +// $locid = 2102723; // Washington (District of Columbia, ..., US +// $locid = 2177566; // New York (Bronx), New York, United States + +// BLIT2011 + $locid = 1486658; // Potsdam + $eventname = "8. Brandenburger Linux-Infotag 2011 - Potsdam"; + $city = "5. Nov 2011"; + + + $query = "select * from `locations` where `id`='$locid'"; + $loc = mysql_fetch_assoc(mysql_query($query)); + + $query = "SELECT ROUND(6378.137 * ACOS(0.9999999*((SIN(PI() * $loc[lat] / 180) * SIN(PI() * `locations`.`lat` / 180)) + + (COS(PI() * $loc[lat] / 180 ) * COS(PI() * `locations`.`lat` / 180) * + COS(PI() * `locations`.`long` / 180 - PI() * $loc[long] / 180)))), -1) AS `distance`, sum(`points`) as pts, `users`.* + FROM `locations` + inner join `users` on `users`.`locid` = `locations`.`id` + inner join `alerts` on `users`.`id`=`alerts`.`memid` + inner join `notary` on `users`.`id`=`notary`.`to` + WHERE (`alerts`.`general`=1 OR `alerts`.`country`=1 OR `alerts`.`regional`=1 OR `alerts`.`radius`=1) + GROUP BY `users`.`id` + HAVING `distance` <= '$maxdist' + ORDER BY `distance` "; + echo $query; + + // comment next line when starting to send mail not only to me + // $query = "select * from `users` where `email` like 'cacerttest%'"; + + $res = mysql_query($query); + $xrows = mysql_num_rows($res); + + while($row = mysql_fetch_assoc($res)) + { + // uncomment next line to send mails ... + sendmail($row['email'], "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1); + } + // 1x cc to events.cacert.org + sendmail("events@cacert.org", "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1); + // 1x mailing report to events.cacert.org + sendmail("events@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1); + + // 1x mailing report to Arbitrator of case http://wiki.cacert.org/wiki/Arbitrations/a20090525.1 + sendmail("p.dunkel@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1); + echo "invitation sent to $xrows recipients.\n"; + +?> |