bug 1138: added assurances to SE log, rework of assurance delete form delete assurance to deleted=Now()

9 files changed, 208 insertions, 41 deletions

diff --git a/includes/account.php b/includes/account.php
index ce1673a..1583f3c 100644
--- a/includes/account.php
+++ b/includes/account.php
@@ -1270,7 +1270,7 @@
 
 	if($oldid == 13 && $process != "")
 	{
-		$ddquery = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' 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'];
@@ -1340,7 +1340,7 @@
 		$_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']."' 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 d89c0e6..b1e1993 100644
--- a/includes/general.php
+++ b/includes/general.php
@@ -57,7 +57,7 @@
 		exit;
 	}
 
-	if(array_key_exists('HTTP_HOST',$_SERVER) && 
+	if(array_key_exists('HTTP_HOST',$_SERVER) &&
 			($_SERVER['HTTP_HOST'] == $_SESSION['_config']['securehostname'] ||
 			$_SERVER['HTTP_HOST'] == $_SESSION['_config']['tverify']))
 	{
@@ -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']."' 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'];
@@ -169,19 +169,19 @@
 			$points++;
 
 		//echo "Points due to length and charset: $points<br/>";
-		
+
 		// check for historical password proposal
 		if ($pwd === "Fr3d Sm|7h") {
 			return 0;
 		}
-		
+
 		return $points;
 	}
 
 	function checkpw($pwd, $email, $fname, $mname, $lname, $suffix)
 	{
 		$points = checkpwlight($pwd);
-		
+
 		if(@strstr(strtolower($pwd), strtolower($email)))
 			$points--;
 
@@ -232,7 +232,7 @@
 	{
 		$bits = explode(": ", $_SESSION['_config']['subject'], 2);
 		$bits = str_replace(", ", "|", str_replace("/", "|", array_key_exists('1',$bits)?$bits['1']:""));
-		$bits = explode("|", $bits);    
+		$bits = explode("|", $bits);
 
 		$_SESSION['_config']['cnc'] = $_SESSION['_config']['subaltc'] = 0;
 		$_SESSION['_config']['OU'] = "";
@@ -557,7 +557,7 @@
 				$fp = @fsockopen($domain,25,$errno,$errstr,5);
 				if($fp)
 				{
-				
+
 					$line = fgets($fp, 4096);
                                         while(substr($line, 0, 4) == "220-")
                                                $line = fgets($fp, 4096);
@@ -662,7 +662,7 @@
 		return $ticket;
 	}
 
-	function sanitizeHTML($input) 
+	function sanitizeHTML($input)
 	{
 		return htmlentities(strip_tags($input), ENT_QUOTES);
 		//In case of problems, please use the following line again:
@@ -732,7 +732,7 @@
 		$text=preg_replace("/[^\w-.@]/","",$text);
 		return($text);
 	}
-	
+
 
 	// returns text message to be shown to the user given the result of is_no_assurer
 	function no_assurer_text($Status)
@@ -775,7 +775,7 @@
 			$name="../$type/$kind/".intval($id/1000)."/$kind-".intval($id).".$type";
 			if (!is_dir("../csr")) { mkdir("../csr",0777); }
 			if (!is_dir("../crt")) { mkdir("../crt",0777); }
-			
+
 			if (!is_dir("../csr/$kind")) { mkdir("../csr/$kind",0777); }
 			if (!is_dir("../crt/$kind")) { mkdir("../crt/$kind",0777); }
 			if (!is_dir("../csr/$kind/".intval($id/1000))) { mkdir("../csr/$kind/".intval($id/1000)); }
diff --git a/includes/loggedin.php b/includes/loggedin.php
index 4f9b8e8..03de18c 100644
--- a/includes/loggedin.php
+++ b/includes/loggedin.php
@@ -49,7 +49,7 @@
 		else
 			unset($_SESSION['profile']);
 	}
-  
+
 	if($_SERVER['HTTP_HOST'] == $_SESSION['_config']['securehostname'] && ($_SESSION['profile']['id'] == 0 || $_SESSION['profile']['loggedin'] == 0))
 	{
 		$user_id = get_user_id_from_cert($_SERVER['SSL_CLIENT_M_SERIAL'],
@@ -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']."' 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 52789b4..fee5924 100644
--- a/includes/notary.inc.php
+++ b/includes/notary.inc.php
@@ -35,7 +35,7 @@
 	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)."' ");
+		     	WHERE `method` = 'Face to Face Meeting' AND `deleted`=0 AND `from`='".intval($userid)."' ");
 		$row = query_getnextrow($res);
 
 		return intval($row['list']);
@@ -44,7 +44,7 @@
 	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 `to`='".intval($userid)."' ");
+			WHERE (`method`='Trusted Third Parties' or `method`='TTP-Assisted') AND `deleted`=0 AND `to`='".intval($userid)."' ");
 		$row = query_getnextrow($res);
 
 		return intval($row['list']);
@@ -53,7 +53,7 @@
 	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)."' ");
+			WHERE `method` = 'Face to Face Meeting' AND `deleted`=0 AND `to`='".intval($userid)."' ");
 		$row = query_getnextrow($res);
 
 		return intval($row['list']);
@@ -75,27 +75,49 @@
 		return intval(query_get_number_of_rows($res)+1);
 	}
 
-	function get_given_assurances ($userid)
+	/**
+	 * get_given_assurances()
+	 *  returns the list of assurances given by the user
+	 * @param mixed $userid - user id for the account for report
+	 * @param integer $log - for log output = 1
+	 * @return
+	 */
+	function get_given_assurances ($userid, $log=0)
 	{
-		$res = query_init ("select * from `notary` where `from`='".intval($userid)."' and `from` != `to` order by `id` asc");
+		$deleted='';
+		if ($log == 0) {
+			$deleted = ' and `deleted` = 0 ';
+		}
+		$res = query_init ("select * from `notary` where `from`='".intval($userid)."' and `from` != `to` $deleted order by `id` asc");
 		return $res;
 	}
 
-	function get_received_assurances ($userid)
+	/**
+	 * get_received_assurances()
+	 *  returns the list of assurances received by the user
+	 * @param mixed $userid - user id for the account for report
+	 * @param integer $log - for log output = 1
+	 * @return
+	 */
+	function get_received_assurances ($userid, $log=0)
 	{
-		$res = query_init ("select * from `notary` where `to`='".intval($userid)."' and `from` != `to` order by `id` asc ");
+		$deleted='';
+		if ($log == 0) {
+			$deleted = ' and `deleted` = 0 ';
+		}
+		$res = query_init ("select * from `notary` where `to`='".intval($userid)."' and `from` != `to` $deleted 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");
+		$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)."' 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;
 	}
 
@@ -591,14 +613,14 @@
 		return $issue_points;
 	}
 
-	function output_given_assurances($userid,$support=0, $ticketno)
+	function output_given_assurances($userid, $support=0, $ticketno='')
 	{
 		output_assurances_header(_("Assurance Points You Issued"),$support);
 		output_given_assurances_content($userid,$points,$sum_experience,$support, $ticketno);
 		output_assurances_footer(_("Total Points Issued"),$points,_("Total Experience Points"),$sum_experience,$support);
 	}
 
-	function output_received_assurances($userid,$support=0, $ticketno)
+	function output_received_assurances($userid,$support=0, $ticketno='')
 	{
 		output_assurances_header(_("Your Assurance Points"),$support);
 		output_received_assurances_content($userid,$points,$sum_experience,$support, $ticketno);
@@ -1762,3 +1784,141 @@ function output_gpg_certs($row, $support=0){
 	</tr>
 	<?
 }
+
+/**
+ * output_log_given_assurances()
+ *  returns the list of all given assurances
+ * @param mixed $userid - user id for the output
+ * @param integer $support - support view = 1
+ * @return
+ */
+function output_log_given_assurances($userid, $support=0)
+{
+    output_assurances_header(_("Assurance given"),$support);
+    output_log_given_assurances_content($userid, $support);
+}
+
+/**
+ * output_log_given_assurances_content()
+ *
+ * @param mixed $userid
+ * @param mixed $support
+ * @return
+ */
+function output_log_given_assurances_content($userid, $support)
+{
+    $res = get_given_assurances(intval($userid), 1);
+    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']));
+        $revoked = '';
+        if ($row['date'] != 0) {
+            $revoked = $row['deleted'];
+        }
+        output_log_assurances_row(intval($row['id']),$row['date'],$row['when'],$email,$name,$apoints,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience,$userid,$support,$revoked);
+    }
+}
+
+/**
+ * output_log_received_assurances()
+ *
+ * @param mixed $userid
+ * @param integer $support
+ * @return
+ */
+function output_log_received_assurances($userid, $support=0)
+{
+    output_assurances_header(_("Assurance received"), $support);
+    output_log_received_assurances_content($userid, $support);
+}
+
+/**
+ * output_log_received_assurances_content()
+ *
+ * @param mixed $userid
+ * @param mixed $support
+ * @param mixed $points
+ * @param mixed $sum_experience
+ * @param mixed $ticketno
+ * @return
+ */
+function output_log_received_assurances_content($userid, $support)
+{
+    $res = get_received_assurances(intval($userid), 1);
+    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']));
+        $revoked = '';
+        if ($row['date'] != 0) {
+            $revoked = $revoked = $row['deleted'];
+        }
+        output_log_assurances_row(intval($row['id']),$row['date'],$row['when'],$email,$name,$awarded,intval($row['points']),$row['location'],$row['method']==""?"":_(sprintf("%s", $row['method'])),$experience,$userid,$support,$revoked);
+    }
+}
+
+/**
+ * output_log_assurances_row()
+ *
+ * @param mixed $assuranceid
+ * @param mixed $date
+ * @param mixed $when
+ * @param mixed $email
+ * @param mixed $name
+ * @param mixed $awarded
+ * @param mixed $points
+ * @param mixed $location
+ * @param mixed $method
+ * @param mixed $experience
+ * @param mixed $userid
+ * @param mixed $support
+ * @param mixed $revoked
+ * @return
+ */
+function output_log_assurances_row($assuranceid,$date,$when,$email,$name,$awarded,$points,$location,$method,$experience,$userid,$support,$revoked)
+{
+
+    $tdstyle="";
+    $emopen="";
+    $emclose="";
+
+    if ($awarded == $points)
+    {
+        if ($awarded == "0")
+        {
+            if ($when < "2006-09-01")
+            {
+                $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>
+        <td class="DataTD" <?=$tdstyle?>><?=$emopen?><?=$revoked?><?=$emclose?></td>
+    </tr>
+    <?
+}
+
diff --git a/pages/account/43.php b/pages/account/43.php
index 2c60226..73fb071 100644
--- a/pages/account/43.php
+++ b/pages/account/43.php
@@ -133,7 +133,7 @@ if(intval($_REQUEST['userid']) > 0) {
             if ($res) {
                 $trow = mysql_fetch_assoc($res);
             }
-            mysql_query("delete from `notary` where `id`='$assurance'");
+            mysql_query("update `notary` set `deleted`=NOW() where `id`='$assurance'");
             if ($trow) {
                 fix_assurer_flag($trow['to']);
                 write_se_log($userid, $_SESSION['profile']['id'], 'SE assurance revoke', $ticketno);
diff --git a/pages/account/59.php b/pages/account/59.php
index 0eaafc4..4cf81c0 100644
--- a/pages/account/59.php
+++ b/pages/account/59.php
@@ -261,6 +261,13 @@ if (1 == $support) {
 <br/>
 
 <?
+
+output_log_given_assurances($userid, $support);
+?><br/><?
+
+output_log_received_assurances($userid, $support);
+?><br/><?
+
 $dres = get_se_log($userid);
 $colspan = 2;
 if (1 == $support) {
diff --git a/pages/wot/10.php b/pages/wot/10.php
index bc76a86..e490af9 100644
--- a/pages/wot/10.php
+++ b/pages/wot/10.php
@@ -36,8 +36,8 @@
 			WHERE `users`.`id` = `notary`.`from` AND `notary`.`from` != `notary`.`to`
 			GROUP BY `notary`.`from` HAVING count(*) > '$rc' ORDER BY `notary`.`when` DESC";
 */
-	$query = "SELECT count(*) AS `list` FROM `users` 
-			inner join `notary` on `users`.`id` = `notary`.`from` 
+	$query = "SELECT count(*) AS `list` FROM `users`
+			inner join `notary` on `users`.`id` = `notary`.`from`
 			GROUP BY `notary`.`from` HAVING count(*) > '$rc'";
 
 	$rank = mysql_num_rows(mysql_query($query)) + 1;
@@ -64,7 +64,7 @@
     <td class="DataTD"><b><?=_("Method")?></b></td>
   </tr>
 <?
-	$query = "select * from `notary` where `to`='".intval($_SESSION['profile']['id'])."'";
+	$query = "select * from `notary` where `deleted`=0 and `to`='".intval($_SESSION['profile']['id'])."'";
 	$res = mysql_query($query);
 	while($row = mysql_fetch_assoc($res))
 	{
@@ -114,7 +114,7 @@ if ($thawte)
   </tr>
 <?
 	$points = 0;
-	$query = "select * from `notary` where `from`='".intval($_SESSION['profile']['id'])."' and `to`!='".intval($_SESSION['profile']['id'])."'";
+	$query = "select * from `notary` where `from`='".intval($_SESSION['profile']['id'])."' and `deleted`=0 and `to`!='".intval($_SESSION['profile']['id'])."'";
 	$res = mysql_query($query);
 	while($row = mysql_fetch_assoc($res))
 	{
diff --git a/www/index.php b/www/index.php
index c7cc03e..bb71a63 100644
--- a/www/index.php
+++ b/www/index.php
@@ -125,7 +125,7 @@ require_once('../includes/lib/l10n.php');
 				showfooter();
 				exit;
 			}
-		}		
+		}
 	}
 
 	if($oldid == 5 && $process != "")
@@ -153,13 +153,13 @@ require_once('../includes/lib/l10n.php');
 		include_once("../includes/lib/general.php");
 		$user_id = get_user_id_from_cert($_SERVER['SSL_CLIENT_M_SERIAL'],
 				$_SERVER['SSL_CLIENT_I_DN_CN']);
-		
+
 		if($user_id >= 0)
 		{
 			$_SESSION['profile'] = mysql_fetch_assoc(mysql_query(
-				"select * from `users` where 
+				"select * from `users` where
 				`id`='$user_id' and `deleted`=0 and `locked`=0"));
-			
+
 			if($_SESSION['profile']['id'] != 0)
 			{
 				$_SESSION['profile']['loggedin'] = 1;
@@ -319,7 +319,7 @@ require_once('../includes/lib/l10n.php');
 				L10n::set_translation($_SESSION['profile']['language']);
 				L10n::init_gettext();
 			}
-			$query = "select sum(`points`) as `total` from `notary` where `to`='".$_SESSION['profile']['id']."' 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'];
@@ -499,7 +499,7 @@ require_once('../includes/lib/l10n.php');
 			if($checkemail != "OK")
 			{
 				$id = 1;
-				if (substr($checkemail, 0, 1) == "4") 
+				if (substr($checkemail, 0, 1) == "4")
 				{
 					$_SESSION['_config']['errmsg'] .= _("The mail server responsible for your domain indicated a temporary failure. This may be due to anti-SPAM measures, such as greylisting. Please try again in a few minutes.");
 				} else {
@@ -566,9 +566,9 @@ require_once('../includes/lib/l10n.php');
 		$subject = stripslashes($_REQUEST['subject']);
 		$message = stripslashes($_REQUEST['message']);
 		$secrethash = $_REQUEST['secrethash2'];
-		
+
 		//check for spam via honeypot
-		if(!isset($_REQUEST['robotest']) || !empty($_REQUEST['robotest'])){ 
+		if(!isset($_REQUEST['robotest']) || !empty($_REQUEST['robotest'])){
 			echo _("Form could not be sent.");
 			showfooter();
 			exit;
@@ -641,7 +641,7 @@ require_once('../includes/lib/l10n.php');
 		$newUrl = $protocol . '://wiki.cacert.org/FAQ/AboutUs';
 		header('Location: '.$newUrl, true, 301); // 301 = Permanently Moved
 	}
-	
+
 	if ($id == 19)
 	{
 		$protocol = $_SERVER['HTTPS'] ? 'https' : 'http';
@@ -655,7 +655,7 @@ require_once('../includes/lib/l10n.php');
 		$newUrl = $protocol . '://wiki.cacert.org/Board';
 		header('Location: '.$newUrl, true, 301); // 301 = Permanently Moved
 	}
-	
+
 	showheader(_("Welcome to CAcert.org"));
 	includeit($id);
 	showfooter();
diff --git a/www/wot.php b/www/wot.php
index 8395a58..0805625 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
-							`to`='".$_SESSION['_config']['notarise']['id']."'";
+							`deleted`=0 and `to`='".$_SESSION['_config']['notarise']['id']."'";
 		$res = mysql_query($query);
 		if(mysql_num_rows($res) > 0)
 		{