summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMichael Tänzer <neo@nhng.de>2014-04-29 16:13:59 +0200
committerMichael Tänzer <neo@nhng.de>2014-04-29 16:13:59 +0200
commitbb11f3a0645a6b96767c9c318fa239a12c3351e5 (patch)
tree2dba41fa37855e6f1165c89d91c04c1bc18d6f3d
parent4f555bd41ae6340c21f199512090e1e47d08fa3a (diff)
parent1a119ab85f3bd5b352223ceef6ba8f29151130b2 (diff)
downloadcacert-devel-bb11f3a0645a6b96767c9c318fa239a12c3351e5.tar.gz
cacert-devel-bb11f3a0645a6b96767c9c318fa239a12c3351e5.tar.xz
cacert-devel-bb11f3a0645a6b96767c9c318fa239a12c3351e5.zip
Merge branch 'bug-1221' into bug-1138
Conflicts: includes/account.php includes/general.php includes/loggedin.php includes/notary.inc.php pages/account/43.php pages/account/55.php pages/wot/10.php www/index.php www/wot.php Signed-off-by: Michael Tänzer <neo@nhng.de>
-rwxr-xr-xCommModule/client.pl2
-rwxr-xr-xCommModule/usbclient.pl2
-rw-r--r--includes/account.php4
-rw-r--r--includes/general.php4
-rw-r--r--includes/lib/account.php20
-rw-r--r--includes/lib/general.php37
-rw-r--r--includes/loggedin.php2
-rw-r--r--includes/notary.inc.php211
-rw-r--r--pages/account/43.php14
-rw-r--r--pages/account/52.php10
-rw-r--r--pages/account/55.php2
-rw-r--r--pages/wot/1.php4
-rw-r--r--pages/wot/10.php10
-rw-r--r--pages/wot/15.php2
-rw-r--r--pages/wot/9.php6
-rwxr-xr-xscripts/cron/refresh_stats.php55
-rw-r--r--stamp/common.php2
-rw-r--r--www/api/ccsr.php6
-rw-r--r--www/api/cemails.php2
-rw-r--r--www/stats.php5
-rw-r--r--www/wot.php9
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 = "&nbsp;";
- $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:'&nbsp;'?><?=$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&amp;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&amp;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();