Merge remote-tracking branch 'remotes/CAcertTestserver/release' into bug-782

5 files changed, 447 insertions, 0 deletions

diff --git a/scripts/db_migrations/version2.sh b/scripts/db_migrations/version2.sh
new file mode 100644
index 0000000..7d940ff
--- /dev/null
+++ b/scripts/db_migrations/version2.sh
@@ -0,0 +1,96 @@
+#!/bin/sh
+# LibreSSL - CAcert web application
+# Copyright (C) 2004-2011  CAcert Inc.
+# 
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+# 
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+# 
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
+
+
+
+# script to do database migrations
+
+# This particular version migrates from the preversioned state to version 1
+# If you want to reuse it for further migrations you probably should pay special 
+# attention because you have to adjust it a bit
+
+set -e # script fails if any command fails
+
+STDIN=0
+STDOUT=1
+STDERR=2
+
+if [ "$1" = "--help" ]; then
+	cat >&$STDERR <<- USAGE
+		Usage: $0 [MYSQL_OPTIONS]
+		You have to specify all options needed by "mysql" as if you had started
+		the MySQL command line client directly (including the name of the
+		database to operate on). The MySQL user used has to have enough
+		privileges to do all necessary operations (among others CREATE, ALTER,
+		DROP, UPDATE, INSERT, DELETE).
+		You might need to enter the mysql password multiple times if you
+		specify the -p option.
+	USAGE
+	exit 1
+fi
+
+mysql_opt=" --batch --skip-column-names $@"
+
+schema_version=$( mysql $mysql_opt <<- 'SQL'
+
+	SELECT MAX(`version`) FROM `schema_version`;
+SQL
+)
+if [ $schema_version != 1 ]; then
+	cat >&$STDERR <<- ERROR
+		Error: database schema is not in the right version to do the migration!
+		Expected version: 1 (i.e. the version before there was versioning)
+	ERROR
+	exit 2
+fi
+
+mysql $mysql_opt <<- 'SQL'
+
+	-- Organisation Assurance bug #1118
+	ALTER TABLE `orgemailcerts` ADD `ou` varchar(50) NOT NULL
+		DEFAULT '';
+	
+	
+	-- Bugs #855, #863, #864, #888, #1118
+	ALTER TABLE `notary`
+		-- add "TTP-Assisted" as method for point transfers (for TTP)
+		MODIFY `method`
+			enum(
+				'Face to Face Meeting',
+				'Trusted Third Parties',
+				'Thawte Points Transfer',
+				'Administrative Increase',
+				'CT Magazine - Germany',
+				'Temporary Increase',
+				'Unknown',
+				'TOPUP',
+				'TTP-Assisted'
+			) NOT NULL DEFAULT 'Face to Face Meeting';
+	
+	
+
+	
+	-- Update schema version number
+	INSERT INTO `schema_version`
+		(`version`, `when`) VALUES
+		('2'      , NOW() );
+SQL
+
+
+echo "Database successfully migrated to version 2"
+exit 0
+
diff --git a/scripts/oa02-mailingtextCats.txt b/scripts/oa02-mailingtextCats.txt
new file mode 100644
index 0000000..044b49b
--- /dev/null
+++ b/scripts/oa02-mailingtextCats.txt
@@ -0,0 +1,71 @@
+(German see below)
+
+Hello <name>,
+
+In preparation for an internal test prior to the audit, we checked if the data 
+entries in the Organisation Accounts are valid. As you are listed as an 
+Organisation Administrator for at least one organisation account, I found out 
+that you currently do not match the requirements to be an Organisation 
+Administrator - in that you need to be an CAcert Assurer. 
+
+The premise to be a CAcert Assurer are that you have:
+ - got at least 100 Assurance Points
+ - passed the CATS test
+        
+It is possible that you may have missed some changes within the CAcert 
+assurance system.
+
+In 2009 the CATS challenge [1] was introduced. You have to pass it before you 
+get the CAcert Assurer status.
+
+In addition, CAcert will be changing the points calculation in the near future. 
+This was announced in 2011 [2].
+
+As you are only missing the CATS challenge, I ask you to do the challenge within 
+the next 2 months. If you have not passed the CATS test by then, I will have 
+to delete you as Organisation Administrator from the appropriate accounts. 
+
+[1] https://wiki.cacert.org/AssurerChallenge
+[2] https://wiki.cacert.org/PR/News/NewPointsCalculation
+
+Thank you for your support.
+
+Marcus Maengel
+CAcert Organisation Assurance Officer
+ 
+---------------------------------------------------
+
+[German Version]
+
+Hallo <name>,
+
+bei der Vorbereitung eines internen Tests vor dem Audit haben wir ueberprueft, 
+ob die Datensaetze in den Organisationskonten gueltig sind. Da Sie als 
+Organisations Administrator fuer mindestens ein Organisationskonto eingetragen 
+sind, habe ich festgestellt, dass Sie zurzeit kein CAcert Assurer sind, was die 
+Voraussetzung fuer die Taetigkeit als Organisations Administrator ist.
+
+Als CAcert Assurer muessen Sie die folgenden Vorausetzungen erfuellen:
+ - Mindestens 100 Assurance-Punkte
+ - Bestandene Assurer-Pruefung (CATS-Test)
+
+Moeglicherweise haben Sie einige aenderungen bei CAcert verpasst.
+
+Im Jahre 2009 wurde die Assurer-Pruefung (Assurer Challenge, CATS Test) [1] 
+eingefuehrt. Die muessen Sie bestehen, um den Assurer-Status zu erhalten.
+
+CAcert wird die Punkte-Berechnung in naher Zukunft aendern. Dies wurde bereits 
+2011 angekuendigt [2].
+
+Da Ihnen lediglich die Assurer-Pruefung fehlt, fordere ich Sie auf, diese 
+Pruefung innerhalb der naechsten 2 Monate nachzuholen. Wenn Sie diese Pruefung 
+bis dahin nicht abgelegt haben, werde ich Sie als Organisations Administrator 
+aus den betreffenden Konten austragen.
+
+[1] https://wiki.cacert.org/AssurerChallenge
+[2] https://wiki.cacert.org/PR/News/NewPointsCalculation
+
+Vielen Dank fuer Ihre Unterstuetzung.
+
+Marcus Maengel
+CAcert Organisation Assurance Officer
\ No newline at end of file
diff --git a/scripts/oa02-mailingtextPoints.txt b/scripts/oa02-mailingtextPoints.txt
new file mode 100644
index 0000000..5e0eadf
--- /dev/null
+++ b/scripts/oa02-mailingtextPoints.txt
@@ -0,0 +1,79 @@
+(German see below)
+
+Hello <name>,
+
+In preparation for an internal test prior to the audit, we checked if the data 
+entries in the Organisation Accounts are valid. As you are listed as an 
+Organisation Administrator for at least one organisation account, I found out 
+that you currently do not match the requirements to be an Organisation 
+Administrator - in that you need to be an CAcert Assurer. 
+
+The premise to be a CAcert Assurer are that you have:
+ - got at least 100 Assurance Points
+ - passed the CATS test
+        
+It is possible that you may have missed some changes within the CAcert 
+assurance system.
+
+In 2009 the CATS challenge [1] was introduced. You have to pass it before you 
+get the CAcert Assurer status.
+
+In addition, CAcert will be changing the points calculation in the near future. 
+This was announced in 2011 [2].
+
+As you are missing the 100 Assurance Points requirement (which might be due to 
+the new points calculation) I ask you to get assured during the next 3 months to 
+reach the 100 Assurance Points. If you have problems in getting assured, please 
+let me know, so I can see what can be done to help you. If you have not reached 
+the 100 points by then (or after an additional grace period in cases of 
+hardship), I will have to delete you as Organisation Administrator from the 
+appropriate accounts. 
+
+[1] https://wiki.cacert.org/AssurerChallenge
+[2] https://wiki.cacert.org/PR/News/NewPointsCalculation
+
+Thank you for your support.
+
+Marcus Maengel
+CAcert Organisation Assurance Officer
+ 
+---------------------------------------------------
+
+[German Version]
+
+Hallo <name>,
+
+bei der Vorbereitung eines internen Tests vor dem Audit haben wir ueberprueft, 
+ob die Datensaetze in den Organisationskonten gueltig sind. Da Sie als 
+Organisations Administrator fuer mindestens ein Organisationskonto eingetragen 
+sind, habe ich festgestellt, dass Sie zurzeit kein CAcert Assurer sind, was die 
+Voraussetzung fuer die Taetigkeit als Organisations Administrator ist.
+
+Als CAcert Assurer muessen Sie die folgenden Vorausetzungen erfuellen:
+ - Mindestens 100 Assurance-Punkte
+ - Bestandene Assurer-Pruefung (CATS-Test)
+
+Moeglicherweise haben Sie einige aenderungen bei CAcert verpasst.
+
+Im Jahre 2009 wurde die Assurer-Pruefung (Assurer Challenge, CATS Test) [1] 
+eingefuehrt. Die muessen Sie bestehen, um den Assurer-Status zu erhalten.
+
+CAcert wird die Punkte-Berechnung in naher Zukunft aendern. Dies wurde bereits 
+2011 angekuendigt [2].
+
+Da Sie die 100 Assurance-Punkte nicht erreicht haben, was auch an der neuen 
+Punkteberechnung liegen kann, fordere ich Sie auf, sich innerhalb der naechsten 
+3 Monate assuren zu lassen, um die 100 Punkte zu erreichen. Wenn Sie Probleme 
+haben, sich assuren zu lassen, teilen Sie mir das bitte mit und wir werden 
+sehen, wie wir Ihnen dabei helfen koennen. Wenn Sie bis dahin (oder nach einer 
+zusaetzlichen Gnadenfrist in schwierigen Faellen) die 100 Punkte nicht erreicht 
+haben, werde ich Sie als Organisations Administrator aus den betreffenden Konten 
+austragen.
+
+[1] https://wiki.cacert.org/AssurerChallenge
+[2] https://wiki.cacert.org/PR/News/NewPointsCalculation
+
+Vielen Dank fuer Ihre Unterstuetzung.
+
+Marcus Maengel
+CAcert Organisation Assurance Officer
\ No newline at end of file
diff --git a/scripts/oa02-mailingtextPointsCats.txt b/scripts/oa02-mailingtextPointsCats.txt
new file mode 100644
index 0000000..67bc5d3
--- /dev/null
+++ b/scripts/oa02-mailingtextPointsCats.txt
@@ -0,0 +1,82 @@
+(German see below)
+
+Hello <name>,
+
+In preparation for an internal test prior to the audit, we checked if the data 
+entries in the Organisation Accounts are valid. As you are listed as an 
+Organisation Administrator for at least one organisation account, I found out 
+that you currently do not match the requirements to be an Organisation 
+Administrator - in that you need to be an CAcert Assurer. 
+
+The premise to be a CAcert Assurer are that you have:
+ - got at least 100 Assurance Points
+ - passed the CATS test
+        
+It is possible that you may have missed some changes within the CAcert 
+assurance system.
+
+In 2009 the CATS challenge [1] was introduced. You have to pass it before you 
+get the CAcert Assurer status.
+
+In addition, CAcert will be changing the points calculation in the near future. 
+This was announced in 2011 [2].
+
+As you are missing the 100 Assurance Points requirement (which might be due to 
+the new points calculation) and the CATS challenge, I ask you to get assured 
+during the next 3 months to reach the 100 Assurance Points and do the CATS test. 
+If you have problems in getting assured, please let me know, so I can see what 
+can be done to help you. If you have not passed the CATS test and reached the 
+100 points by then (or after an additional grace period in cases of hardship), 
+I will have to delete you as Organisation Administrator from the appropriate 
+accounts. 
+
+[1] https://wiki.cacert.org/AssurerChallenge
+[2] https://wiki.cacert.org/PR/News/NewPointsCalculation
+
+Thank you for your support.
+
+Marcus Maengel
+CAcert Organisation Assurance Officer
+ 
+---------------------------------------------------
+
+[German Version]
+
+Hallo <name>,
+
+bei der Vorbereitung eines internen Tests vor dem Audit haben wir ueberprueft, 
+ob die Datensaetze in den Organisationskonten gueltig sind. Da Sie als 
+Organisations Administrator fuer mindestens ein Organisationskonto eingetragen 
+sind, habe ich festgestellt, dass Sie zurzeit kein CAcert Assurer sind, was die 
+Voraussetzung fuer die Taetigkeit als Organisations Administrator ist.
+
+Als CAcert Assurer muessen Sie die folgenden Vorausetzungen erfuellen:
+ - Mindestens 100 Assurance-Punkte
+ - Bestandene Assurer-Pruefung (CATS-Test)
+
+Moeglicherweise haben Sie einige aenderungen bei CAcert verpasst.
+
+Im Jahre 2009 wurde die Assurer-Pruefung (Assurer Challenge, CATS Test) [1] 
+eingefuehrt. Die muessen Sie bestehen, um den Assurer-Status zu erhalten.
+
+CAcert wird die Punkte-Berechnung in naher Zukunft aendern. Dies wurde bereits 
+2011 angekuendigt [2].
+
+Da Sie die 100 Assurance-Punkte nicht erreicht haben, was auch an der neuen 
+Punkteberechnung liegen kann, und auch die Assurer-Pruefung nicht abgelegt 
+haben, fordere ich Sie auf, sich innerhalb der naechsten 3 Monate assuren zu 
+lassen, um die 100 Punkte zu erreichen, und die Assurer-Pruefung abzulegen. 
+Wenn Sie Probleme haben, sich assuren zu lassen, teilen Sie mir das bitte mit 
+und wir werden sehen, wie wir Ihnen dabei helfen koennen. Wenn Sie bis dahin 
+(oder nach einer zusaetzlichen Gnadenfrist in schwierigen Faellen) die 
+100 Punkte nicht erreicht haben oder die Assurer-Pruefung nicht abgelegt haben, 
+werde ich Sie als Organisations-Administrator aus den betreffenden Konten 
+austragen.
+
+[1] https://wiki.cacert.org/AssurerChallenge
+[2] https://wiki.cacert.org/PR/News/NewPointsCalculation
+
+Vielen Dank fuer Ihre Unterstuetzung.
+
+Marcus Maengel
+CAcert Organisation Assurance Officer
\ No newline at end of file
diff --git a/scripts/oa02-orgainformation.php.txt b/scripts/oa02-orgainformation.php.txt
new file mode 100644
index 0000000..e0d8ca2
--- /dev/null
+++ b/scripts/oa02-orgainformation.php.txt
@@ -0,0 +1,119 @@
+#!/usr/bin/php -q
+<? /*
+    LibreSSL - CAcert web application
+    Copyright (C) 2004-2009  CAcert Inc.
+
+    This program is free software; you can redistribute it and/or modify
+    it under the terms of the GNU General Public License as published by
+    the Free Software Foundation; version 2 of the License.
+
+    This program is distributed in the hope that it will be useful,
+    but WITHOUT ANY WARRANTY; without even the implied warranty of
+    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+    GNU General Public License for more details.
+
+    You should have received a copy of the GNU General Public License
+    along with this program; if not, write to the Free Software
+    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
+*/
+	include_once("../includes/mysql.php");
+	include_once("../includes/lib/general.php");
+
+	//default mail text
+	$lines = "";
+	$fp = fopen("oa02-mailingtextPoints.txt", "r");
+	while(!feof($fp))
+	{
+		$line = trim(fgets($fp, 4096));
+		$lines .= wordwrap($line, 75, "\n")."\n";
+	}
+	fclose($fp);
+
+	//first variant of mail text
+	$lines1 = "";
+	$fp = fopen("oa02-mailingtextCats.txt", "r");
+	while(!feof($fp))
+	{
+		$line = trim(fgets($fp, 4096));
+		$lines1 .= wordwrap($line, 75, "\n")."\n";
+	}
+	fclose($fp);
+
+	//second variant of mail text
+	$lines2 = "";
+	$fp = fopen("oa02-mailingtextPointsCats.txt", "r");
+	while(!feof($fp))
+	{
+		$line = trim(fgets($fp, 4096));
+		$lines2 .= wordwrap($line, 75, "\n")."\n";
+	}
+	fclose($fp);
+
+	// comment next line when starting to send mail not only to me 
+	//	$query = "select * from `users` where `email` like 'cacerttest%'";
+
+	$query = "
+	SELECT
+		`users`.`email`,
+		`users`.`fname`,
+		`users`.`lname`,
+		`orginfo`.`O`,
+		`orginfo`.`id`,
+		`org`.`memid`
+	FROM
+		`users`,
+		`orginfo`,
+		`org`
+	WHERE	`org`.`orgid` = `orginfo`.`id`
+	AND	`org`.`memid` = `users`.`id`
+	AND	`users`.`assurer` = 0
+	AND	`org`.`deleted` = 0
+	ORDER BY
+		`users`.`email`,
+		`orginfo`.`O`
+	";
+
+	$res = mysql_query($query);
+	$xrows = mysql_num_rows($res);
+
+	$report = "";
+	$report1 = "";
+	$report2 = "";
+	$report3 = "";
+
+	while($row = mysql_fetch_assoc($res))
+	{
+		//    uncomment next line to send mails ...
+		$fullname = $row['fname']." ".$row['lname'];
+		$status = get_assurer_status($row['memid']);
+		if (3 == $status) {
+			$mail = str_replace("<name>",$fullname,$lines);
+			sendmail($row['email'], "[CAcert.org] Assurer Status Requirement Check on Organisation Adminstrators", $mail, "support@cacert.org", "", "", "CAcert Organisation Assurer", "returns@cacert.org", 1);
+			$report = $row['email'].", ".$fullname.", ".$row['O']."\n";
+		} elseif (5 == $status) {
+			$mail = str_replace("<name>",$fullname,$lines1);
+			sendmail($row['email'], "[CAcert.org] Assurer Status Requirement Check on Organisation Adminstrators", $mail, "support@cacert.org", "", "", "CAcert Organisation Assurer", "returns@cacert.org", 1);
+			$report1 = $row['email'].", ".$fullname.", ".$row['O']."\n";
+		} elseif (7 == $status){
+			$mail = str_replace("<name>",$fullname,$lines2);
+			sendmail($row['email'], "[CAcert.org] Assurer Status Requirement Check on Organisation Adminstrators", $mail, "support@cacert.org", "", "", "CAcert Organisation Assurer", "returns@cacert.org", 1);
+			$report2 = $row['email'].", ".$fullname.", ".$row['O']."\n";
+		} else {
+			$report3 = $row['email'].", ".$fullname.", ".$row['O']."\n";
+		}
+	}
+
+	// 1x cc to oao.cacert.org
+	sendmail("oao@cacert.org", "[CAcert.org] Assurer Status Requirement Check on Organisation Adminstrators", $lines."\n\n".$lines1."\n\n".$lines2, "oao@cacert.org", "", "", "CAcert OA Support", "returns@cacert.org", 1);
+
+	// 1x mailing report to oao.cacert.org
+	sendmail("oao@cacert.org", "[CAcert.org] Assurer Status Requirement Check on Organisation Adminstrators - Report", "oa-mailing sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert OA Support", "returns@cacert.org", 1);
+
+	// 1x detailed mailing report to OAO
+	$reporttotal = "Result of the Mailing \n\n Organisation Administrators with missing points \n".$report."\n\n Organisation Adminstrators with missing points ".$report1."\n\n Organisation Adminstrators with missing points and CATS ".$report2."\n\n Organisation Adminstrators with blocked assurer ".$report3;
+	sendmail("oao@cacert.org", "[CAcert.org] Assurer Status Requirement Check on Organisation Adminstrators - Report", $reporttotal, "support@cacert.org", "", "", "CAcert Organisation Assurer", "returns@cacert.org", 1);
+
+	// 1x mailing report to Arbitrator of case http://wiki.cacert.org/wiki/Arbitrations/a20110608.1
+	sendmail("bernhard@cacert.org", "[CAcert.org] Assurer Status Requirement Check on Organisation Adminstrators - Report", "oa-mailing sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert OA Support", "returns@cacert.org", 1);
+
+	echo "oa-mailing sent to $xrows recipients.\n";