diff options
| -rwxr-xr-x | CommModule/client.pl | 2 | ||||
| -rwxr-xr-x | CommModule/usbclient.pl | 2 | ||||
| -rw-r--r-- | includes/account.php | 4 | ||||
| -rw-r--r-- | includes/general.php | 4 | ||||
| -rw-r--r-- | includes/lib/account.php | 20 | ||||
| -rw-r--r-- | includes/lib/general.php | 37 | ||||
| -rw-r--r-- | includes/loggedin.php | 2 | ||||
| -rw-r--r-- | includes/notary.inc.php | 211 | ||||
| -rw-r--r-- | pages/account/43.php | 14 | ||||
| -rw-r--r-- | pages/account/52.php | 10 | ||||
| -rw-r--r-- | pages/account/55.php | 2 | ||||
| -rw-r--r-- | pages/wot/1.php | 4 | ||||
| -rw-r--r-- | pages/wot/10.php | 10 | ||||
| -rw-r--r-- | pages/wot/15.php | 2 | ||||
| -rw-r--r-- | pages/wot/9.php | 6 | ||||
| -rwxr-xr-x | scripts/cron/refresh_stats.php | 55 | ||||
| -rw-r--r-- | stamp/common.php | 2 | ||||
| -rw-r--r-- | www/api/ccsr.php | 6 | ||||
| -rw-r--r-- | www/api/cemails.php | 2 | ||||
| -rw-r--r-- | www/stats.php | 5 | ||||
| -rw-r--r-- | www/wot.php | 9 |
21 files changed, 234 insertions, 175 deletions
diff --git a/CommModule/client.pl b/CommModule/client.pl index bd3eb02..532761e 100755 --- a/CommModule/client.pl +++ b/CommModule/client.pl @@ -441,7 +441,7 @@ sub calculateDays($) { if($_[0]) { - my @sum = $dbh->selectrow_array("select sum(`points`) as `total` from `notary` where `to`='".$_[0]."' group by `to`"); + my @sum = $dbh->selectrow_array("select sum(`points`) as `total` from `notary` where `to`='".$_[0]."' and `deleted`=0 group by `to`"); SysLog("Summe: $sum[0]\n") if($debug); return ($sum[0]>=50)?730:180; diff --git a/CommModule/usbclient.pl b/CommModule/usbclient.pl index 3cbe2c3..6cbc111 100755 --- a/CommModule/usbclient.pl +++ b/CommModule/usbclient.pl @@ -425,7 +425,7 @@ sub calculateDays($) { if($_[0]) { - my @sum = $dbh->selectrow_array("select sum(`points`) as `total` from `notary` where `to`='".$_[0]."' group by `to`"); + my @sum = $dbh->selectrow_array("select sum(`points`) as `total` from `notary` where `to`='".$_[0]."' and `deleted`=0 group by `to`"); SysLog("Summe: $sum[0]\n") if($debug); return ($sum[0]>=50)?730:180; diff --git a/includes/account.php b/includes/account.php index a718f6f..17427a2 100644 --- a/includes/account.php +++ b/includes/account.php @@ -1266,7 +1266,7 @@ function buildSubjectFromSession() { if($oldid == 13 && $process != "") { - $ddquery = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' and `deleted`=0 group by `to`"; + $ddquery = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' and `deleted` = 0 group by `to`"; $ddres = mysql_query($ddquery); $ddrow = mysql_fetch_assoc($ddres); $_SESSION['profile']['points'] = $ddrow['total']; @@ -1338,7 +1338,7 @@ function buildSubjectFromSession() { $_SESSION['profile'] = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".$_SESSION['profile']['id']."'")); $_SESSION['profile']['loggedin'] = 1; - $ddquery = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' and `deleted`=0 group by `to`"; + $ddquery = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' and `deleted` = 0 group by `to`"; $ddres = mysql_query($ddquery); $ddrow = mysql_fetch_assoc($ddres); $_SESSION['profile']['points'] = $ddrow['total']; diff --git a/includes/general.php b/includes/general.php index b1e1993..a504d65 100644 --- a/includes/general.php +++ b/includes/general.php @@ -82,7 +82,7 @@ $locked = mysql_fetch_assoc(mysql_query("select `locked` from `users` where `id`='".$_SESSION['profile']['id']."'")); if($locked['locked'] == 0) { - $query = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' and `deleted`=0 group by `to`"; + $query = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' and `deleted` = 0 group by `to`"; $res = mysql_query($query); $row = mysql_fetch_assoc($res); $_SESSION['profile']['points'] = $row['total']; @@ -477,7 +477,7 @@ if($id <= 0) $id = $_SESSION['profile']['id']; - $query = "select sum(`points`) as `points` from `notary` where `to`='$id' group by `to`"; + $query = "select sum(`points`) as `points` from `notary` where `to`='$id' and `deleted` = 0 group by `to`"; $row = mysql_fetch_assoc(mysql_query($query)); $points = $row['points']; diff --git a/includes/lib/account.php b/includes/lib/account.php index e311668..8590213 100644 --- a/includes/lib/account.php +++ b/includes/lib/account.php @@ -19,10 +19,10 @@ /** * Function to recalculate the cached Assurer status - * + * * @param int $userID * if the user ID is not given the flag will be recalculated for all users - * + * * @return bool * false if there was an error on fixing the flag. This does NOT return the * new value of the flag @@ -30,7 +30,7 @@ function fix_assurer_flag($userID = NULL) { // Update Assurer-Flag on users table if 100 points and CATS passed. - // + // // We may have some performance issues here if no userID is given // there are ~150k assurances and ~220k users currently // but the exists-clause on cats_passed should be a good filter @@ -46,20 +46,21 @@ function fix_assurer_flag($userID = NULL) WHERE `cp`.`variant_id` = `cv`.`id` AND `cv`.`type_id` = 1 AND `cp`.`user_id` = `u`.`id` - ) + ) AND ( SELECT SUM(`points`) FROM `notary` AS `n` WHERE `n`.`to` = `u`.`id` AND (`n`.`expire` > now() - OR `n`.`expire` IS NULL) + OR `n`.`expire` IS NULL) + AND `n`.`deleted` = 0 ) >= 100'; - + $query = mysql_query($sql); if (!$query) { return false; } // Challenge has been passed and non-expired points >= 100 - + // Reset flag if requirements are not met // // Also a bit performance critical but assurer flag is only set on @@ -86,13 +87,14 @@ function fix_assurer_flag($userID = NULL) `n`.`expire` > now() OR `n`.`expire` IS NULL ) + AND `n`.`deleted` = 0 ) < 100 )'; - + $query = mysql_query($sql); if (!$query) { return false; } - + return true; }
\ No newline at end of file diff --git a/includes/lib/general.php b/includes/lib/general.php index 85b132d..127c6b7 100644 --- a/includes/lib/general.php +++ b/includes/lib/general.php @@ -18,10 +18,10 @@ /** * Checks if the user may log in and retrieve the user id - * + * * Usually called with $_SERVER['SSL_CLIENT_M_SERIAL'] and * $_SERVER['SSL_CLIENT_I_DN_CN'] - * + * * @param $serial string * usually $_SERVER['SSL_CLIENT_M_SERIAL'] * @param $issuer_cn string @@ -43,7 +43,7 @@ function get_user_id_from_cert($serial, $issuer_cn) $row = mysql_fetch_assoc($res); return intval($row['memid']); } - + return -1; } @@ -71,7 +71,7 @@ function failWithId($errormessage) { /** * Runs a command on the shell and return it's exit code and output - * + * * @param string $command * The command to run. Make sure that you escapeshellarg() any non-constant * parts as this is executed on a shell! @@ -85,7 +85,7 @@ function failWithId($errormessage) { * @param string|bool $errors * The output the command wrote to STDERR (this is passed as reference), * if true (default) the output will be written to the real STDERR - * + * * @return int|bool * The exit code of the command, true if the execution of the command * failed (true because then @@ -93,38 +93,38 @@ function failWithId($errormessage) { */ function runCommand($command, $input = "", &$output = null, &$errors = true) { $descriptorspec = array(); - + if ($input !== true) { $descriptorspec[0] = array("pipe", "r"); // STDIN for child } - + if ($output !== true) { $descriptorspec[1] = array("pipe", "w"); // STDOUT for child } - + if ($errors !== true) { $descriptorspec[2] = array("pipe", "w"); // STDERR for child } - + $proc = proc_open($command, $descriptorspec, $pipes); - + if (is_resource($proc)) { if ($input !== true) { fwrite($pipes[0], $input); fclose($pipes[0]); } - + if ($output !== true) { $output = stream_get_contents($pipes[1]); } - + if ($errors !== true) { $errors = stream_get_contents($pipes[2]); } - + return proc_close($proc); - + } else { return true; } @@ -145,19 +145,18 @@ function runCommand($command, $input = "", &$output = null, &$errors = true) { { $Result |= 5; } - - $query = mysql_query('SELECT SUM(`points`) AS `points` FROM `notary` AS `n` WHERE `n`.`to` = \''.(int)intval($userID).'\' AND `n`.`expire` < now()'); + + $query = mysql_query('SELECT SUM(`points`) AS `points` FROM `notary` AS `n` WHERE `n`.`to` = \''.(int)intval($userID).'\' AND `n`.`expire` < now() and `deleted` = 0'); $row = mysql_fetch_assoc($query); if ($row['points'] < 100) { $Result |= 3; } - + $query = mysql_query('SELECT `assurer_blocked` FROM `users` WHERE `id` = \''.(int)intval($userID).'\''); $row = mysql_fetch_assoc($query); if ($row['assurer_blocked'] > 0) { $Result |= 9; } - + return $Result; } -
\ No newline at end of file diff --git a/includes/loggedin.php b/includes/loggedin.php index 03de18c..46f0659 100644 --- a/includes/loggedin.php +++ b/includes/loggedin.php @@ -113,7 +113,7 @@ if($_SERVER['HTTP_HOST'] == $_SESSION['_config']['securehostname'] && $_SESSION['profile']['id'] > 0 && $_SESSION['profile']['loggedin'] > 0) { - $query = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' and `deleted`=0 group by `to`"; + $query = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' and `deleted` = 0 group by `to`"; $res = mysql_query($query); $row = mysql_fetch_assoc($res); $_SESSION['profile']['points'] = $row['total']; diff --git a/includes/notary.inc.php b/includes/notary.inc.php index 01e90a6..7b73e73 100644 --- a/includes/notary.inc.php +++ b/includes/notary.inc.php @@ -17,6 +17,7 @@ */ define('NULL_DATETIME', '0000-00-00 00:00:00'); +define('THAWTE_REVOCATION_DATETIME', '2010-11-16 00:00:00'); function query_init ($query) { @@ -37,7 +38,7 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); function get_number_of_assurances ($userid) { $res = query_init ("SELECT count(*) AS `list` FROM `notary` - WHERE `method` = 'Face to Face Meeting' AND `deleted`=0 AND `from`='".intval($userid)."' "); + WHERE `method` = 'Face to Face Meeting' AND `from`='".intval($userid)."' and `deleted` = 0"); $row = query_getnextrow($res); return intval($row['list']); @@ -46,7 +47,7 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); function get_number_of_ttpassurances ($userid) { $res = query_init ("SELECT count(*) AS `list` FROM `notary` - WHERE (`method`='Trusted Third Parties' or `method`='TTP-Assisted') AND `deleted`=0 AND `to`='".intval($userid)."' "); + WHERE (`method`='Trusted Third Parties' or `method`='TTP-Assisted') AND `to`='".intval($userid)."' and `deleted` = 0"); $row = query_getnextrow($res); return intval($row['list']); @@ -55,7 +56,7 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); function get_number_of_assurees ($userid) { $res = query_init ("SELECT count(*) AS `list` FROM `notary` - WHERE `method` = 'Face to Face Meeting' AND `deleted`=0 AND `to`='".intval($userid)."' "); + WHERE `method` = 'Face to Face Meeting' AND `to`='".intval($userid)."' and `deleted` = 0"); $row = query_getnextrow($res); return intval($row['list']); @@ -64,7 +65,7 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); function get_top_assurer_position ($no_of_assurances) { $res = query_init ("SELECT count(*) AS `list` FROM `notary` - WHERE `method` = 'Face to Face Meeting' + WHERE `method` = 'Face to Face Meeting' and `deleted` = 0 GROUP BY `from` HAVING count(*) > '".intval($no_of_assurances)."'"); return intval(query_get_number_of_rows($res)+1); } @@ -72,7 +73,7 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); function get_top_assuree_position ($no_of_assurees) { $res = query_init ("SELECT count(*) AS `list` FROM `notary` - WHERE `method` = 'Face to Face Meeting' + WHERE `method` = 'Face to Face Meeting' and `deleted` = 0 GROUP BY `to` HAVING count(*) > '".intval($no_of_assurees)."'"); return intval(query_get_number_of_rows($res)+1); } @@ -111,13 +112,13 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); function get_given_assurances_summary ($userid) { - $res = query_init ("select count(*) as number,points,awarded,method from notary where `from`='".intval($userid)."' AND `deleted`=0 group by points,awarded,method"); + $res = query_init ("select count(*) as number,points,awarded,method from notary where `from`='".intval($userid)."' and `deleted` = 0 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)."' AND `deleted`=0 group by points,awarded,method"); + $res = query_init ("select count(*) as number,points,awarded,method from notary where `to`='".intval($userid)."' and `deleted` = 0 group by points,awarded,method"); return $res; } @@ -135,46 +136,137 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); return mysql_num_rows($res); } - function calc_experience ($row,&$points,&$experience,&$sum_experience,&$revoked) + + /** + * Calculate awarded points (corrects some issues like out of range points + * or points that were issued by means that have been deprecated) + * + * @param array $row - associative array containing the data from the + * `notary` table + * @return int - the awarded points for this assurance + */ + function calc_awarded($row) + { + // Back in the old days there was no `awarded` column => is now zero, + // there the `points` column contained that data + $points = max(intval($row['awarded']), intval($row['points'])); + + // Set negative points to zero, yes there are such things in the database + $points = max($points, 0); + + switch ($row['method']) + { + // These programmes have been revoked + 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; + + // TTP assurances, limit to 35 + case 'TTP-Assisted': + $points = min($points, 35); + break; + + // TTP TOPUP, limit to 30 + case 'TOPUP': + $points = min($points, 30); + + // All these should be preserved for the time being + 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 (and superassurances?), limit to 35/50 pts in the future? + break; + + default: // should never happen ... ;-) + $points = 0; + } + + return $points; + } + + + /** + * Calculate the experience points from a given Assurance + * @param array $row - [inout] associative array containing the data from + * the `notary` table, a key 'experience' will be added + * @param int $sum_points - [inout] the sum of already counted assurance + * points the assurer issued + * @param int $sum_experience - [inout] the sum of already counted + * experience points that were awarded to the assurer + * @return int - the assurance points that were awarded for this assurance + */ + function calc_experience(&$row, &$sum_points, &$sum_experience) { - $apoints = max($row['points'], $row['awarded']); - $points += $apoints; - $experience = " "; - $revoked = false; # to be coded later (after DB-upgrade) + $awarded = calc_awarded($row); + + // Don't count revoked assurances even if we are displaying them + if ($row['deleted'] !== NULL_DATETIME) { + $row['experience'] = 0; + return $awarded; + } + + $experience = 0; if ($row['method'] == "Face to Face Meeting") { - $sum_experience = $sum_experience +2; - $experience = "2"; + $experience = 2; } - return $apoints; + $sum_experience += $experience; + $row['experience'] = $experience; + + $sum_points += $awarded; + return $awarded; } - function calc_assurances ($row,&$points,&$experience,&$sumexperience,&$awarded,&$revoked) + /** + * Calculate the points received from a received Assurance + * @param array $row - [inout] associative array containing the data from + * the `notary` table, a key 'experience' will be added + * @param int $sum_points - [inout] the sum of already counted assurance + * points the assuree received + * @param int $sum_experience - [inout] the sum of already counted + * experience points that were awarded to the assurer + * @return int - the assurance points that were counted for this assurance + */ + function calc_assurances(&$row, &$sum_points, &$sum_experience) { - $awarded = calc_points($row); - $revoked = false; + $awarded = calc_awarded($row); + $experience = 0; + // High point values mean that some of them are experience points 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; + $experience = 0; + $row['deleted'] = THAWTE_REVOCATION_DATETIME; break; - default: - $points += $awarded; } - $sumexperience = $sumexperience + $experience; + + // Don't count revoked assurances even if we are displaying them + if ($row['deleted'] !== NULL_DATETIME) { + $row['experience'] = 0; + return $awarded; + } + + $sum_experience += $experience; + $row['experience'] = $experience; + $sum_points += $awarded; + + return $awarded; } @@ -358,10 +450,10 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); } ?> <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$name?><?=$emclose?></td> - <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$awarded?><?=$emclose?></td> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$revoked ? sprintf("<strong style='color: red'>%s</strong>",_("Revoked")) : $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> + <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$experience?$experience:' '?><?=$emclose?></td> <? if ($support == 1) { @@ -423,23 +515,23 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); /** * Helper function to render assurances given by the user * @param int $userid - * @param int& $points - [out] sum of given points + * @param int& $sum_points - [out] sum of given points * @param int& $sum_experience - [out] sum of experience points gained * @param int $support - set to 1 if the output is for the support interface * @param string $ticketno - the ticket number set in the support interface */ - function output_given_assurances_content($userid,&$points,&$sum_experience,$support, $ticketno) + function output_given_assurances_content($userid,&$sum_points,&$sum_experience,$support, $ticketno) { - $points = 0; + $sum_points = 0; $sumexperience = 0; $res = get_given_assurances(intval($userid)); while($row = mysql_fetch_assoc($res)) { - $fromuser = get_user (intval($row['to'])); - $apoints = 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,$apoints,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience,$userid,$support,$revoked, $ticketno); + $assuree = get_user (intval($row['to'])); + $apoints = calc_experience($row, $sum_points, $sum_experience); + $name = show_user_link ($assuree['fname']." ".$assuree['lname'],intval($row['to'])); + $email = show_email_link ($assuree['email'],intval($row['to'])); + output_assurances_row (intval($row['id']),$row['date'],$row['when'],$email,$name,$apoints,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$row['experience'],$userid,$support,$row['deleted']!==NULL_DATETIME, $ticketno); } } @@ -448,23 +540,23 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); /** * Helper function to render assurances received by the user * @param int $userid - * @param int& $points - [out] sum of received points + * @param int& $sum_points - [out] sum of received points * @param int& $sum_experience - [out] sum of experience points the assurers gained * @param int $support - set to 1 if the output is for the support interface * @param string $ticketno - the ticket number set in the support interface */ - function output_received_assurances_content($userid,&$points,&$sum_experience,$support, $ticketno) + function output_received_assurances_content($userid,&$sum_points,&$sum_experience,$support, $ticketno) { - $points = 0; + $sum_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); + $awarded = calc_assurances($row, $sum_points, $sum_experience); $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, $ticketno); + output_assurances_row (intval($row['id']),$row['date'],$row['when'],$email,$name,$awarded,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$row['experience'],$userid,$support,$row['deleted']!==NULL_DATETIME, $ticketno); } } @@ -477,41 +569,6 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); 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 'TTP-Assisted': // TTP assurances, limit to 35 - case 'TOPUP': // TOPUP to be delevoped in the future, limit to 30 - 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); @@ -541,7 +598,7 @@ define('NULL_DATETIME', '0000-00-00 00:00:00'); $res = get_received_assurances_summary($userid); while($row = mysql_fetch_assoc($res)) { - $points = calc_points ($row); + $points = calc_awarded($row); if ($points > $max_points) // limit to 100 points, above is experience (needs to be fixed) { diff --git a/pages/account/43.php b/pages/account/43.php index 9a9d75f..dcfa378 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -21,7 +21,6 @@ include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php"); $ticketno=''; $ticketvalidation=FALSE; - if (isset($_SESSION['ticketno'])) { $ticketno = $_SESSION['ticketno']; $ticketvalidation = valid_ticket_number($ticketno); @@ -32,6 +31,7 @@ if (isset($_SESSION['ticketmsg'])) { $ticketmsg = ''; } + // search for an account by email search, if more than one is found display list to choose if(intval(array_key_exists('userid',$_REQUEST)?$_REQUEST['userid']:0) <= 0) { @@ -132,14 +132,13 @@ if(intval($_REQUEST['userid']) > 0) { } else { $assurance = mysql_escape_string(intval($_REQUEST['assurance'])); $trow = 0; - $res = mysql_query("select `to` from `notary` where `id`='$assurance'"); + $res = mysql_query("select `to` from `notary` where `id`='$assurance' and `deleted` = 0"); if ($res) { $trow = mysql_fetch_assoc($res); - } - - mysql_query("update `notary` set `deleted`=NOW() where `id`='$assurance'"); - if ($trow) { - fix_assurer_flag($trow['to']); + mysql_query("update `notary` set `deleted`=NOW() where `id`='$assurance'"); + if ($trow) { + fix_assurer_flag($trow['to']); + } } } } elseif(array_key_exists('assurance',$_REQUEST) && $_REQUEST['assurance'] > 0 && $ticketvalidation == FALSE) { @@ -966,6 +965,7 @@ if(intval($_REQUEST['userid']) > 0) { <? // if(array_key_exists('assuredto',$_GET) && $_GET['assuredto'] == "yes") { + function showassuredto($ticketno) { ?> diff --git a/pages/account/52.php b/pages/account/52.php index 77a3bae..ce2025f 100644 --- a/pages/account/52.php +++ b/pages/account/52.php @@ -35,7 +35,7 @@ exit; } - $query = "select sum(`points`) as `points` from `notary` where `to`='$memid'"; + $query = "select sum(`points`) as `points` from `notary` where `to`='$memid' and `deleted` = 0"; $notary = mysql_fetch_assoc(mysql_query($query)); $query = "select * from `users` where `id`='$memid'"; $user = mysql_fetch_assoc(mysql_query($query)); @@ -65,7 +65,7 @@ <input type="hidden" name="oldid" value="<?=intval($_GET['id'])?>"> <input type="hidden" name="uid" value="<?=$uid?>"> </form> -<? } else { +<? } else { $query = "select * from `tverify` where `id`='$uid' and `modified`=1"; $res = mysql_query($query); if(mysql_num_rows($res) > 0) @@ -74,7 +74,7 @@ } else { if($uid) echo _("Unable to locate a valid request for that UID.")."<br/>"; } - + // Search for open requests: $query = "select * from `tverify` where `modified`=0"; $res = mysql_query($query); @@ -83,14 +83,14 @@ echo "<br/>"._("The following requests are still open:")."<br/><ul>"; while($row = mysql_fetch_assoc($res)) { - $uid=intval($row['id']); + $uid=intval($row['id']); $query3 = "select * from `tverify-vote` where `tverify`='$uid' and `memid`='".intval($_SESSION['profile']['id'])."'"; $rc3 = mysql_num_rows(mysql_query($query3)); if($rc3 <= 0) { echo "<li><a href='account.php?id=52&uid=".intval($row['id'])."'>".intval($row['id'])."</a></li>\n"; } - } + } echo "</ul>\n<br>\n"; } else diff --git a/pages/account/55.php b/pages/account/55.php index 38e9fb7..7e9710c 100644 --- a/pages/account/55.php +++ b/pages/account/55.php @@ -87,7 +87,7 @@ <tr><td colspan="3" class="DataTD"><a href="account.php?id=43&userid=<?=$user_id ?>">back</a></td></tr>
<? } else {
$query = 'SELECT `u`.id, `u`.`assurer`, SUM(`points`) FROM `users` AS `u`, `notary` AS `n` '.
- ' WHERE `u`.`id` = \''.(int)intval($_SESSION['profile']['id']).'\' AND `n`.`to` = `u`.`id` AND `expire` < now() '.
+ ' WHERE `u`.`id` = \''.(int)intval($_SESSION['profile']['id']).'\' AND `n`.`to` = `u`.`id` AND `expire` < now() and and `n`.`deleted` = 0'. ' GROUP BY `u`.id, `u`.`assurer`';
$res = mysql_query($query);
if (!$res) {
diff --git a/pages/wot/1.php b/pages/wot/1.php index a45b5df..99c2b9f 100644 --- a/pages/wot/1.php +++ b/pages/wot/1.php @@ -91,7 +91,7 @@ { $query = "select *, `users`.`id` as `id` from `users`,`notary` where `listme`='1' and `ccid`='".$ccid."' and `regid`='".$regid."' and - `locid`='".$locid."' and `users`.`id`=`notary`.`to` + `locid`='".$locid."' and `users`.`id`=`notary`.`to` and `notary`.`deleted`=0 group by `notary`.`to` HAVING SUM(`points`) >= 100 order by `points` desc"; $list = mysql_query($query); if(mysql_num_rows($list) > 0) @@ -115,7 +115,7 @@ <td class="DataTD"><?=$row['assurer']?_("Yes"):("<font color=\"#ff0000\">"._("Not yet!")."</font>")?></td> </tr> -<? } +<? } } ?> </table> diff --git a/pages/wot/10.php b/pages/wot/10.php index e490af9..c7e1ff1 100644 --- a/pages/wot/10.php +++ b/pages/wot/10.php @@ -24,7 +24,7 @@ <td colspan="5" class="title"><?=_("Assurer Ranking")?></td> </tr> <tr> -<? +<?// the rank calculation is not adjusted to the new deletion method $query = "SELECT `users`. *, count(*) AS `list` FROM `users`, `notary` WHERE `users`.`id` = `notary`.`from` AND `notary`.`from` != `notary`.`to` AND `from`='".intval($_SESSION['profile']['id'])."' GROUP BY `notary`.`from`"; @@ -64,11 +64,11 @@ <td class="DataTD"><b><?=_("Method")?></b></td> </tr> <? - $query = "select * from `notary` where `deleted`=0 and `to`='".intval($_SESSION['profile']['id'])."'"; + $query = "select `id`, `date`, `from`, `points`, `location`, `method` from `notary` where `to`='".intval($_SESSION['profile']['id'])."' and `deleted`=0"; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { - $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($row['from'])."'")); + $fromuser = mysql_fetch_assoc(mysql_query("select `fname`, `lname` from `users` where `id`='".intval($row['from'])."'")); ?> <tr> <td class="DataTD"><?=$row['id']?></td> @@ -114,11 +114,11 @@ if ($thawte) </tr> <? $points = 0; - $query = "select * from `notary` where `from`='".intval($_SESSION['profile']['id'])."' and `deleted`=0 and `to`!='".intval($_SESSION['profile']['id'])."'"; + $query = "select `id`, `date`, `points`, `to`, `location`, `method` from `notary` where `from`='".intval($_SESSION['profile']['id'])."' and `to`!='".intval($_SESSION['profile']['id'])."' and `deleted`=0" ; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { - $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($row['to'])."'")); + $fromuser = mysql_fetch_assoc(mysql_query("select `fname`, `lname` from `users` where `id`='".intval($row['to'])."'")); $points += $row['points']; $name = trim($fromuser['fname']." ".$fromuser['lname']); if($name == "") diff --git a/pages/wot/15.php b/pages/wot/15.php index cca2702..c1f3e0f 100644 --- a/pages/wot/15.php +++ b/pages/wot/15.php @@ -14,7 +14,7 @@ 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 -*/ +*/ require_once($_SESSION['_config']['filepath']."/includes/notary.inc.php"); diff --git a/pages/wot/9.php b/pages/wot/9.php index bfa7a98..b492ff6 100644 --- a/pages/wot/9.php +++ b/pages/wot/9.php @@ -15,9 +15,9 @@ along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA */ - + require_once($_SESSION['_config']['filepath'].'/includes/lib/l10n.php'); - + $res = mysql_query("select * from `users` where `id`='".intval($_REQUEST['userid'])."' and `listme`='1'"); if(mysql_num_rows($res) <= 0) @@ -28,7 +28,7 @@ $user = mysql_fetch_array($res); $userlang = $user['language']; $points = mysql_num_rows(mysql_query("select sum(`points`) as `total` from `notary` - where `to`='".$user['id']."' group by `to` HAVING SUM(`points`) > 0")); + where `to`='".$user['id']."' and `deleted`=0 group by `to` HAVING SUM(`points`) > 0")); if($points <= 0) { echo _("Sorry, I was unable to locate that user."); diff --git a/scripts/cron/refresh_stats.php b/scripts/cron/refresh_stats.php index 2a3d2b5..3b446ba 100755 --- a/scripts/cron/refresh_stats.php +++ b/scripts/cron/refresh_stats.php @@ -23,7 +23,7 @@ require_once(dirname(__FILE__).'/../../includes/mysql.php'); /** * Wrapper around mysql_query() to provide some error handling. Prints an error * message and dies if query fails - * + * * @param string $sql * the SQL statement to execute * @return resource|boolean @@ -35,7 +35,7 @@ function sql_query($sql) { fwrite(STDERR, "MySQL query failed:\n\"$sql\"\n".mysql_error()); die(1); } - + return $res; } @@ -54,7 +54,7 @@ function updateCache($stats) { $sql = "insert into `statscache` (`timestamp`, `cache`) values ('$timestamp', '".mysql_real_escape_string(serialize($stats))."')"; sql_query($sql); - + // Make sure the new statistic was inserted successfully $res = sql_query( "select 1 from `statscache` where `timestamp` = '$timestamp'"); @@ -62,7 +62,7 @@ function updateCache($stats) { fwrite(STDERR, "Error on inserting the new statistic"); return false; } - + sql_query("delete from `statscache` where `timestamp` != '$timestamp'"); return true; } @@ -74,22 +74,22 @@ function updateCache($stats) { */ function getDataFromLive() { echo "Calculating current statistics\n"; - + $stats = array(); $stats['verified_users'] = number_format(tc( "select count(*) as `count` from `users` where `verified` = 1 and `deleted` = 0 and `locked` = 0")); - + $stats['verified_emails'] = number_format(tc( "select count(*) as `count` from `email` where `hash` = '' and `deleted` = 0")); - + $stats['verified_domains'] = number_format(tc( "select count(*) as `count` from `domains` where `hash` = '' and `deleted` = 0")); - + $certs = tc("select count(*) as `count` from `domaincerts` where `expire` != 0"); $certs += tc("select count(*) as `count` from `emailcerts` @@ -101,7 +101,7 @@ function getDataFromLive() { $certs += tc("select count(*) as `count` from `orgemailcerts` where `expire` != 0"); $stats['verified_certificates'] = number_format($certs); - + $certs = tc("select count(*) as `count` from `domaincerts` where `revoked` = 0 and `expire` > NOW()"); $certs += tc("select count(*) as `count` from `emailcerts` @@ -113,11 +113,12 @@ function getDataFromLive() { $certs += tc("select count(*) as `count` from `orgemailcerts` where `revoked` = 0 and `expire` > NOW()"); $stats['valid_certificates'] = number_format($certs); - + $stats['assurances_made'] = number_format(tc( "select count(*) as `count` from `notary` - where `method` = '' or `method` = 'Face to Face Meeting'")); - + where (`method` = '' or `method` = 'Face to Face Meeting') + and `deleted` = 0")); + $stats['users_1to49'] = number_format(tc( "select count(*) as `count` from ( select 1 from `notary` @@ -125,7 +126,7 @@ function getDataFromLive() { group by `to` having sum(`points`) > 0 and sum(`points`) < 50 ) as `low_points`")); - + $stats['users_50to99'] = number_format(tc( "select count(*) as `count` from ( select 1 from `notary` @@ -133,7 +134,7 @@ function getDataFromLive() { group by `to` having sum(`points`) >= 50 and sum(`points`) < 100 ) as `high_points`")); - + $stats['assurer_candidates'] = number_format(tc( "select count(*) as `count` from `users` where ( @@ -148,7 +149,7 @@ function getDataFromLive() { and `cv`.`type_id`=1 )" )); - + $stats['aussurers_with_test'] = number_format(tc( "select count(*) as `count` from `users` where ( @@ -163,7 +164,7 @@ function getDataFromLive() { and `cv`.`type_id`=1 )" )); - + $stats['points_issued'] = number_format(tc( "select sum(greatest(`points`, `awarded`)) as `count` from `notary` where `deleted` = 0 @@ -177,16 +178,16 @@ function getDataFromLive() { $next_month_ts = mktime(0, 0, 0, date("m") - $i + 1, 1, date("Y")); $first = date("Y-m-d", $first_ts); $next_month = date("Y-m-d", $next_month_ts); - + echo "Calculating statistics for month $first\n"; - + $totalusers += $users = tc( - "select count(*) as `count` from `users` + "select count(*) as `count` from `users` where `created` >= '$first' and `created` < '$next_month' and `verified` = 1 and `deleted` = 0 and `locked` = 0"); - + $totassurers += $assurers = tc( "select count(*) as `count` from ( select 1 from `notary` @@ -195,7 +196,7 @@ function getDataFromLive() { and `deleted` = 0 group by `to` having sum(`points`) >= 100 ) as `assurer_candidates`"); - + $certs = tc( "select count(*) as `count` from `domaincerts` where `created` >= '$first' and `created` < '$next_month' @@ -240,16 +241,16 @@ function getDataFromLive() { $next_year_ts = mktime(0, 0, 0, 1, 1, $i + 1); $first = date("Y-m-d", $first_ts); $next_year = date("Y-m-d", $next_year_ts); - + echo "Calculating statistics for year $i\n"; - + $totalusers += $users = tc( - "select count(*) as `count` from `users` + "select count(*) as `count` from `users` where `created` >= '$first' and `created` < '$next_year' and `verified` = 1 and `deleted` = 0 and `locked` = 0"); - + $totassurers += $assurers = tc( "select count(*) as `count` from ( select 1 from `notary` @@ -258,7 +259,7 @@ function getDataFromLive() { and `deleted` = 0 group by `to` having sum(`points`) >= 100 ) as `assurer_candidates`"); - + $certs = tc( "select count(*) as `count` from `domaincerts` where `created` >= '$first' and `created` < '$next_year' @@ -286,7 +287,7 @@ function getDataFromLive() { $tmp_arr['new_users'] = number_format($users); $tmp_arr['new_assurers'] = number_format($assurers); $tmp_arr['new_certificates'] = number_format($certs); - + $stats['growth_last_years'][] = $tmp_arr; } $stats['growth_last_years_total'] = array( diff --git a/stamp/common.php b/stamp/common.php index ff814dd..d0a71f4 100644 --- a/stamp/common.php +++ b/stamp/common.php @@ -117,7 +117,7 @@ if($org == 0) { $query = "SELECT *, sum(`points`) AS `total` FROM `users`, `notary` WHERE `users`.`id` = '$cert[memid]' AND - `notary`.`to` = `users`.`id` and `notary`.`when` <= '$cert[issued]' GROUP BY `notary`.`to`"; + `notary`.`to` = `users`.`id` and `notary`.`when` <= '$cert[issued]' and `notary`.`deleted`=0 GROUP BY `notary`.`to`"; $user = mysql_fetch_assoc(mysql_query($query)); } else { $query = "select * from `orginfo` where `id`='$cert[orgid]'"; diff --git a/www/api/ccsr.php b/www/api/ccsr.php index 7efdf8d..aa33baa 100644 --- a/www/api/ccsr.php +++ b/www/api/ccsr.php @@ -42,7 +42,7 @@ require_once '../../includes/lib/check_weak_key.php'; } if(count($emails) <= 0) die("404,Wasn't able to match any emails sent against your account"); - $query = "select sum(`points`) as `points` from `notary` where `to`='$memid' group by `to`"; + $query = "select sum(`points`) as `points` from `notary` where `to`='$memid' and `notary`.`deleted`=0 group by `to`"; $row = mysql_fetch_assoc(mysql_query($query)); $points = $row['points']; @@ -62,12 +62,12 @@ require_once '../../includes/lib/check_weak_key.php'; $codesign = 1; $CSR = trim($_REQUEST['optionalCSR']); - + if (($weakKey = checkWeakKeyCSR($CSR)) !== "") { die("403, $weakKey"); } - + $incsr = tempnam("/tmp", "ccsrIn"); $checkedcsr = tempnam("/tmp", "ccsrOut"); $fp = fopen($incsr, "w"); diff --git a/www/api/cemails.php b/www/api/cemails.php index 0d067ea..260ca4f 100644 --- a/www/api/cemails.php +++ b/www/api/cemails.php @@ -25,7 +25,7 @@ echo "200,Authentication Ok\n"; $user = mysql_fetch_assoc($res); $memid = $user['id']; - $query = "select sum(`points`) as `points` from `notary` where `to`='$memid' group by `to`"; + $query = "select sum(`points`) as `points` from `notary` where `to`='$memid' and `notary`.`deleted`=0 group by `to`"; $row = mysql_fetch_assoc(mysql_query($query)); $points = $row['points']; echo "CS=".intval($user['codesign'])."\n"; diff --git a/www/stats.php b/www/stats.php index 1599e17..d4d892d 100644 --- a/www/stats.php +++ b/www/stats.php @@ -32,7 +32,7 @@ $stats['timestamp'] = $ar['timestamp']; return $stats; } - + return null; } @@ -40,7 +40,7 @@ if ($stats === null) { echo '<p>', _("Error while retrieving the statistics!"), '</p>'; showfooter(); - die(); + die(); } ?> <h1>CAcert.org <?=_("Statistics")?></h1> @@ -157,4 +157,3 @@ </div> <? showfooter(); ?> - diff --git a/www/wot.php b/www/wot.php index fb229b9..dec4246 100644 --- a/www/wot.php +++ b/www/wot.php @@ -225,7 +225,7 @@ function send_reminder() } $query = "select * from `notary` where `from`='".$_SESSION['profile']['id']."' and - `deleted`=0 and `to`='".$_SESSION['_config']['notarise']['id']."'"; + `to`='".$_SESSION['_config']['notarise']['id']."' and `deleted` = 0"; $res = mysql_query($query); if(mysql_num_rows($res) > 0) { @@ -332,7 +332,7 @@ $iecho= "c"; if($newpoints < 0) $newpoints = $awarded = 0; - $query = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['_config']['notarise']['id']."' group by `to`"; + $query = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['_config']['notarise']['id']."' and `deleted` = 0 group by `to`"; $res = mysql_query($query); $drow = mysql_fetch_assoc($res); @@ -352,7 +352,8 @@ $iecho= "c"; `to`='".$_SESSION['_config']['notarise']['id']."' AND `awarded`='$awarded' AND `location`='".mysql_escape_string(stripslashes($_POST['location']))."' AND - `date`='".mysql_escape_string(stripslashes($_POST['date']))."'"; + `date`='".mysql_escape_string(stripslashes($_POST['date']))."' AND + `deleted`=0"; $res = mysql_query($query); if(mysql_num_rows($res) > 0) { @@ -509,7 +510,7 @@ $iecho= "c"; $userid = intval($_REQUEST['userid']); $user = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='$userid' and `listme`=1")); $points = mysql_num_rows(mysql_query("select sum(`points`) as `total` from `notary` - where `to`='".$user['id']."' group by `to` HAVING SUM(`points`) > 0")); + where `to`='".$user['id']."' and `deleted` = 0 group by `to` HAVING SUM(`points`) > 0")); if($points > 0) { $my_translation = L10n::get_translation(); |