summaryrefslogtreecommitdiff
path: root/scripts
diff options
context:
space:
mode:
Diffstat (limited to 'scripts')
-rw-r--r--scripts/49de-lt2013-berlin-email.txt17
-rw-r--r--scripts/49de-lt2013-berlin-mail.php.txt119
-rw-r--r--scripts/50de-ate-luebeck-email.txt91
-rw-r--r--scripts/50de-ate-luebeck-mail.php.txt123
-rw-r--r--scripts/51at-ate-graz-email.txt91
-rw-r--r--scripts/51at-ate-graz-mail.php.txt126
-rw-r--r--scripts/52at-ate-wien-email.txt91
-rw-r--r--scripts/52at-ate-wien-mail.php.txt130
-rwxr-xr-xscripts/cron/permissionreview.php87
-rwxr-xr-xscripts/cron/refresh_stats.php307
-rw-r--r--scripts/mail-weak-keys.php12
11 files changed, 1161 insertions, 33 deletions
diff --git a/scripts/49de-lt2013-berlin-email.txt b/scripts/49de-lt2013-berlin-email.txt
new file mode 100644
index 0000000..3759160
--- /dev/null
+++ b/scripts/49de-lt2013-berlin-email.txt
@@ -0,0 +1,17 @@
+Hallo CAcert-Mitglieder und Assurer,
+
+Der diesjaehrige LinuxTag auf der Messe Berlin findet von Mittwoch, 22. Mai bis Samstag, 25. Mai statt und steht unter dem Motto "Open minds create effective solutions!"
+
+Effektive Loesungen finden bei CAcert besonders gut dann statt, wenn ein reger Informationsaustausch stattfindet. Dafuer steht CAcert mit einem Stand auf der Messe am Stand 140 in Halle 7.1a. Wir freuen uns dabei nicht nur auf Besucher, sondern wir sind auch in hohem Masse auf die Mithilfe der Community angewiesen. Wir freuen uns deshalb besonders, auch Dich am Stand als Standbetreuer, Assurer und Netzwerker begruessen zu koennen. Bei dieser Gelegenheit koennen wir uns auch gerne ueber die aktuellen Entwicklungen bei CAcert unterhalten.
+
+Weitere Details stehen in unserem Wiki unter
+[https://wiki.cacert.org/Events/LinuxTag2013]
+
+Fuer Assurer, die als Standbetreuer mithelfen moechten bitte
+Rueckantwort bitte an events@cacert.org
+ 'Ich moechte beim Linuxtag mithelfen'
+Dafuer koennen wir auch kostenlose Eintrittskarten zur Verfuegung stellen, rechtzeitige Absprache ist dafuer natuerlich Voraussetzung.
+
+Vielen Dank fuer eure Unterstuetzung!
+
+Kontakt: events@cacert.org
diff --git a/scripts/49de-lt2013-berlin-mail.php.txt b/scripts/49de-lt2013-berlin-mail.php.txt
new file mode 100644
index 0000000..a5bef69
--- /dev/null
+++ b/scripts/49de-lt2013-berlin-mail.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");
+
+ $lines = "";
+ $fp = fopen("49de-lt2013-berlin-email.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+
+// $locid = intval($_REQUEST['location']);
+// $maxdist = intval($_REQUEST['maxdist']);
+// maxdist in [Km]
+ $maxdist = 50;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $locid = 1486658; // Potsdam
+// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+// $locid = 2094781; // Mission Hills (Los Angeles), California, United States
+// $locid = 423655; // Copenhagen, Kobenhavn*, Denmark
+// $locid = 2093625; // Los Angeles, CA ???
+// $locid = 2094326 // Los Angeles (Los Angeles), California, United States
+// $locid = 2257312; // Sydney, New South Wales, Australia
+// $locid = 572764; // Essen, Nordrhein-Westfalen, Germany
+// $locid = 78; // Aachen, Nordrhein-Westfalen, Germany
+// $locid = 1260319; // Muenchen
+// $locid = 266635; // Bonn, Nordrhein-Westfalen, Germany
+// $locid = 873779; // Karlsruhe, Baden-Wuerttemberg, Germany
+// $locid = 520340; // Dusseldorf, Nordrhein-Westfalen, Germany
+// $locid = 2262656; // Melbourne, Victoria, Australia
+// $locid = 2185076; // Raleigh (Wake), North Carolina, United States
+
+// CAcert Assurance and Keysigning event at FUDcon, Lawrence, KS, Jan 19th 2013
+// $locid = 2126955; // Lawrence (Douglas), Kansas, United States
+// $eventname = "CAcert Assurance and Keysigning at FUDcon Lawrence, KS";
+// $city = "January 19th 2013";
+
+// ATE-Kiel 2013-02-11
+// $locid = 919560; // Kiel, Schleswig-Holstein, Germany
+// $eventname = "ATE-Kiel";
+// $city = "11. Februar 2013";
+
+// Linuxtag, Berlin, May 22-25, 2013,
+ $locid = 228950; // Berlin
+ $eventname = "Linuxtag Berlin";
+ $city = "22.-25. Mai, 2013";
+
+
+ $query = "select * from `locations` where `id`='$locid'";
+ $loc = mysql_fetch_assoc(mysql_query($query));
+
+ $query = "SELECT ROUND(6378.137 * ACOS(0.9999999*((SIN(PI() * $loc[lat] / 180) * SIN(PI() * `locations`.`lat` / 180)) +
+ (COS(PI() * $loc[lat] / 180 ) * COS(PI() * `locations`.`lat` / 180) *
+ COS(PI() * `locations`.`long` / 180 - PI() * $loc[long] / 180)))), -1) AS `distance`, sum(`points`) as pts, `users`.*
+ FROM `locations`
+ inner join `users` on `users`.`locid` = `locations`.`id`
+ inner join `alerts` on `users`.`id`=`alerts`.`memid`
+ inner join `notary` on `users`.`id`=`notary`.`to`
+ WHERE (`alerts`.`general`=1 OR `alerts`.`country`=1 OR `alerts`.`regional`=1 OR `alerts`.`radius`=1)
+ GROUP BY `users`.`id`
+ HAVING `distance` <= '$maxdist'
+ ORDER BY `distance` ";
+ echo $query;
+
+ // comment next line when starting to send mail not only to me
+ // $query = "select * from `users` where `email` like 'cacerttest%'";
+
+ $res = mysql_query($query);
+ $xrows = mysql_num_rows($res);
+
+ while($row = mysql_fetch_assoc($res))
+ {
+ // uncomment next line to send mails ...
+ sendmail($row['email'], "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ // 1x mailing report to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ // 1x mailing report to Arbitrator of case http://wiki.cacert.org/wiki/Arbitrations/a20090525.1
+ sendmail("p.dunkel@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ echo "invitation sent to $xrows recipients.\n";
+
+?>
diff --git a/scripts/50de-ate-luebeck-email.txt b/scripts/50de-ate-luebeck-email.txt
new file mode 100644
index 0000000..5bbfb70
--- /dev/null
+++ b/scripts/50de-ate-luebeck-email.txt
@@ -0,0 +1,91 @@
+[Deutsch]
+
+Es hat sich viel getan im letzten Jahr. Eine ganze Reihe von bisher
+eher "muendlich ueberlieferten" Regeln wurden in Policies gegossen.
+Neue Prozeduren (z.B. die Assurer Challenge) und Verpflichtungen (z.B.
+in dem CAcert Community Agreement) wurden beschlossen. Die Assurer
+Training Events wollen versuchen, die ganzen Informationen unter's
+Volk zu bringen:
+
+- Welcher Satz fehlt auf alten CAP Formularen?
+- Warum soll ich mir R/L/O einpraegen?
+- Wie verhaelst du dich,
+ wenn du ein fremdes Ausweisdokument das ersteMal pruefst?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den
+Assurer Training Events (ATEs).
+
+Darueberhinaus wird beim ATE der Vorgang der Identitaetsueberpruefung
+trainiert und auditiert, um die Qualitaet der Assurances in der
+taeglichen Praxis zu erfassen. Dabei gilt es moegliche Fehler und
+Fallstricke zu erkennen und aufzudecken. Die Assurer haben also die
+Moeglichkeit, sich mit den Fehlern auseinanderzusetzen und zu erfahren,
+wie diese vermieden werden koennen.
+
+Wie IanG sagte: The ATE or Assurer Training Event is exceptionally
+recommended for all Assurers, and include parts which contribute
+directly to our audit. Come and find out how you can also contribute.
+
+Die kommende Veranstaltung in deiner Naehe findet statt am:
+
+- Freitag, den 7. Juni 2013
+- in der Zeit von: 19:00 - ca. 22:00 Uhr
+- Jugendzentrum Burgtor
+- Grosse Burgstrasse 2
+- 23539 Luebeck
+
+
+Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im
+Wiki [http://wiki.cacert.org/events/2013-06-07ATE-Luebeck]
+Blog [http://blog.cacert.org/2013/05/595.html]
+
+Teilnehmer Registrierung mit Rueckantwort:
+ 'Ich moechte am ATE-Luebeck teilnehmen'
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Kontakt: events@cacert.org
+
+
+
+[English]
+
+During the last year many changes took place inside CAcert. Many "oral"
+rules have been put into Policies. New procedures
+(e.g. Assurer Challenge) and obligations
+(e.g. CAcert Community Agreement) have been put into live.
+The Assurer Training Events (ATE) try to spread this information:
+
+- What is missing on the "old" CAP forms?
+- Why should I remember R/L/O?
+- What can you do if an Assuree shows an ID document unknown to you?
+
+These and more questions will be answered during the
+Assurer Training Events (ATEs)
+
+Furthermore, the ATE trains how to do assurances and audits assurances,
+to measure the quality of assurances in the daily routine. Here are some
+possible errors and pitfalls which need to be found. Assurers have the
+opportunity to see those errors and how to avoid them.
+
+As IanG said: The ATE or Assurer Training Event is exceptionally
+recommended for all Assurers and includes parts which contribute
+directly to our audit. Come and find out how you can also contribute.
+
+The next event held in your area will be:
+
+- Friday 07. June 2013
+- during 19:00 - ca. 22:00
+- Jugendzentrum Burgtor
+- Grosse Burgstrasse 2
+- 23539 Luebeck
+
+Details to the location can be found:
+Wiki [http://wiki.cacert.org/events/2013-06-07ATE-Luebeck]
+Blog [http://blog.cacert.org/2013/05/595.html]
+
+User reply for registration: 'I will attend the ATE-Luebeck'
+
+The event team is looking forward for your attendance:
+
+Contact: events@cacert.org
diff --git a/scripts/50de-ate-luebeck-mail.php.txt b/scripts/50de-ate-luebeck-mail.php.txt
new file mode 100644
index 0000000..41721c5
--- /dev/null
+++ b/scripts/50de-ate-luebeck-mail.php.txt
@@ -0,0 +1,123 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2013 CAcert Inc.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
+*/
+ include_once("../includes/mysql.php");
+
+ $lines = "";
+ $fp = fopen("50de-ate-luebeck-email.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+
+// $locid = intval($_REQUEST['location']);
+// $maxdist = intval($_REQUEST['maxdist']);
+// maxdist in [Km]
+ $maxdist = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $locid = 1486658; // Potsdam
+// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+// $locid = 2094781; // Mission Hills (Los Angeles), California, United States
+// $locid = 423655; // Copenhagen, Kobenhavn*, Denmark
+// $locid = 2093625; // Los Angeles, CA ???
+// $locid = 2094326 // Los Angeles (Los Angeles), California, United States
+// $locid = 2257312; // Sydney, New South Wales, Australia
+// $locid = 572764; // Essen, Nordrhein-Westfalen, Germany
+// $locid = 78; // Aachen, Nordrhein-Westfalen, Germany
+// $locid = 1260319; // Muenchen
+// $locid = 266635; // Bonn, Nordrhein-Westfalen, Germany
+// $locid = 873779; // Karlsruhe, Baden-Wuerttemberg, Germany
+// $locid = 520340; // Dusseldorf, Nordrhein-Westfalen, Germany
+// $locid = 2262656; // Melbourne, Victoria, Australia
+// $locid = 2185076; // Raleigh (Wake), North Carolina, United States
+
+// CAcert Assurance and Keysigning event at FUDcon, Lawrence, KS, Jan 19th 2013
+// $locid = 2126955; // Lawrence (Douglas), Kansas, United States
+// $eventname = "CAcert Assurance and Keysigning at FUDcon Lawrence, KS";
+// $city = "January 19th 2013";
+
+// ATE-Kiel 2013-02-11
+// $locid = 919560; // Kiel, Schleswig-Holstein, Germany
+// $eventname = "ATE-Kiel";
+// $city = "11. Februar 2013";
+
+// Linuxtag, Berlin, May 22-25, 2013,
+// $locid = 228950; // Berlin
+// $eventname = "Linuxtag Berlin";
+// $city = "22.-25. Mai, 2013";
+
+ $locid = 1117395; // Lubeck Hansestadt, Schleswig-Holstein, Germany
+ $eventname = "ATE-Luebeck";
+ $city = "07. Juni 2013";
+
+
+ $query = "select * from `locations` where `id`='$locid'";
+ $loc = mysql_fetch_assoc(mysql_query($query));
+
+ $query = "SELECT ROUND(6378.137 * ACOS(0.9999999*((SIN(PI() * $loc[lat] / 180) * SIN(PI() * `locations`.`lat` / 180)) +
+ (COS(PI() * $loc[lat] / 180 ) * COS(PI() * `locations`.`lat` / 180) *
+ COS(PI() * `locations`.`long` / 180 - PI() * $loc[long] / 180)))), -1) AS `distance`, sum(`points`) as pts, `users`.*
+ FROM `locations`
+ inner join `users` on `users`.`locid` = `locations`.`id`
+ inner join `alerts` on `users`.`id`=`alerts`.`memid`
+ inner join `notary` on `users`.`id`=`notary`.`to`
+ WHERE (`alerts`.`general`=1 OR `alerts`.`country`=1 OR `alerts`.`regional`=1 OR `alerts`.`radius`=1)
+ GROUP BY `users`.`id`
+ HAVING `distance` <= '$maxdist'
+ ORDER BY `distance` ";
+ echo $query;
+
+ // comment next line when starting to send mail not only to me
+ // $query = "select * from `users` where `email` like 'cacerttest%'";
+
+ $res = mysql_query($query);
+ $xrows = mysql_num_rows($res);
+
+ while($row = mysql_fetch_assoc($res))
+ {
+ // uncomment next line to send mails ...
+ sendmail($row['email'], "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ // 1x mailing report to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ // 1x mailing report to Arbitrator of case http://wiki.cacert.org/wiki/Arbitrations/a20090525.1
+ sendmail("p.dunkel@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ echo "invitation sent to $xrows recipients.\n";
+
+?>
diff --git a/scripts/51at-ate-graz-email.txt b/scripts/51at-ate-graz-email.txt
new file mode 100644
index 0000000..0d77dfe
--- /dev/null
+++ b/scripts/51at-ate-graz-email.txt
@@ -0,0 +1,91 @@
+[Deutsch]
+
+Es hat sich viel getan im letzten Jahr. Eine ganze Reihe von bisher
+eher "muendlich ueberlieferten" Regeln wurden in Policies gegossen.
+Neue Prozeduren (z.B. die Assurer Challenge) und Verpflichtungen (z.B.
+in dem CAcert Community Agreement) wurden beschlossen. Die Assurer
+Training Events wollen versuchen, die ganzen Informationen unter's
+Volk zu bringen:
+
+- Welcher Satz fehlt auf alten CAP Formularen?
+- Warum soll ich mir R/L/O einpraegen?
+- Wie verhaelst du dich,
+ wenn du ein fremdes Ausweisdokument das ersteMal pruefst?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den
+Assurer Training Events (ATEs).
+
+Darueberhinaus wird beim ATE der Vorgang der Identitaetsueberpruefung
+trainiert und auditiert, um die Qualitaet der Assurances in der
+taeglichen Praxis zu erfassen. Dabei gilt es moegliche Fehler und
+Fallstricke zu erkennen und aufzudecken. Die Assurer haben also die
+Moeglichkeit, sich mit den Fehlern auseinanderzusetzen und zu erfahren,
+wie diese vermieden werden koennen.
+
+Wie IanG sagte: The ATE or Assurer Training Event is exceptionally
+recommended for all Assurers, and include parts which contribute
+directly to our audit. Come and find out how you can also contribute.
+
+Die kommende Veranstaltung in deiner Naehe findet statt am:
+
+- Freitag, den 16. August 2013
+- in der Zeit von: 19:00 - ca. 22:00 Uhr
+- Realraum Graz
+- Jakomistraße 16
+- 8010 Graz
+
+
+Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im
+Wiki [http://wiki.cacert.org/Events/2013-08-16ATE-Graz]
+Blog [http://blog.cacert.org/2013/07/ate-graz-at-2013-08-16/]
+
+Teilnehmer Registrierung mit Rueckantwort:
+ 'Ich moechte am ATE-Graz teilnehmen'
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Kontakt: events@cacert.org
+
+
+
+[English]
+
+During the last year many changes took place inside CAcert. Many "oral"
+rules have been put into Policies. New procedures
+(e.g. Assurer Challenge) and obligations
+(e.g. CAcert Community Agreement) have been put into live.
+The Assurer Training Events (ATE) try to spread this information:
+
+- What is missing on the "old" CAP forms?
+- Why should I remember R/L/O?
+- What can you do if an Assuree shows an ID document unknown to you?
+
+These and more questions will be answered during the
+Assurer Training Events (ATEs)
+
+Furthermore, the ATE trains how to do assurances and audits assurances,
+to measure the quality of assurances in the daily routine. Here are some
+possible errors and pitfalls which need to be found. Assurers have the
+opportunity to see those errors and how to avoid them.
+
+As IanG said: The ATE or Assurer Training Event is exceptionally
+recommended for all Assurers and includes parts which contribute
+directly to our audit. Come and find out how you can also contribute.
+
+The next event held in your area will be:
+
+- Friday 16. August 2013
+- during 19:00 - ca. 22:00
+- Realraum Graz
+- Jakomistraße 16
+- 8010 Graz
+
+Details to the location can be found:
+Wiki [http://wiki.cacert.org/Events/2013-08-16ATE-Graz]
+Blog [http://blog.cacert.org/2013/07/ate-graz-at-2013-08-16/]
+
+User reply for registration: 'I will attend the ATE-Graz'
+
+The event team is looking forward for your attendance:
+
+Contact: events@cacert.org
diff --git a/scripts/51at-ate-graz-mail.php.txt b/scripts/51at-ate-graz-mail.php.txt
new file mode 100644
index 0000000..56dd4ff
--- /dev/null
+++ b/scripts/51at-ate-graz-mail.php.txt
@@ -0,0 +1,126 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2013 CAcert Inc.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
+*/
+ include_once("../includes/mysql.php");
+
+ $lines = "";
+ $fp = fopen("51at-ate-graz-email.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+
+// $locid = intval($_REQUEST['location']);
+// $maxdist = intval($_REQUEST['maxdist']);
+// maxdist in [Km]
+ $maxdist = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $locid = 1486658; // Potsdam
+// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+// $locid = 2094781; // Mission Hills (Los Angeles), California, United States
+// $locid = 423655; // Copenhagen, Kobenhavn*, Denmark
+// $locid = 2093625; // Los Angeles, CA ???
+// $locid = 2094326 // Los Angeles (Los Angeles), California, United States
+// $locid = 2257312; // Sydney, New South Wales, Australia
+// $locid = 572764; // Essen, Nordrhein-Westfalen, Germany
+// $locid = 78; // Aachen, Nordrhein-Westfalen, Germany
+// $locid = 1260319; // Muenchen
+// $locid = 266635; // Bonn, Nordrhein-Westfalen, Germany
+// $locid = 873779; // Karlsruhe, Baden-Wuerttemberg, Germany
+// $locid = 520340; // Dusseldorf, Nordrhein-Westfalen, Germany
+// $locid = 2262656; // Melbourne, Victoria, Australia
+// $locid = 2185076; // Raleigh (Wake), North Carolina, United States
+
+// CAcert Assurance and Keysigning event at FUDcon, Lawrence, KS, Jan 19th 2013
+// $locid = 2126955; // Lawrence (Douglas), Kansas, United States
+// $eventname = "CAcert Assurance and Keysigning at FUDcon Lawrence, KS";
+// $city = "January 19th 2013";
+
+// ATE-Kiel 2013-02-11
+// $locid = 919560; // Kiel, Schleswig-Holstein, Germany
+// $eventname = "ATE-Kiel";
+// $city = "11. Februar 2013";
+
+// Linuxtag, Berlin, May 22-25, 2013,
+// $locid = 228950; // Berlin
+// $eventname = "Linuxtag Berlin";
+// $city = "22.-25. Mai, 2013";
+
+// $locid = 1117395; // Lubeck Hansestadt, Schleswig-Holstein, Germany
+// $eventname = "ATE-Luebeck";
+// $city = "07. Juni 2013";
+
+ $locid = 675661; // Graz, Steiermark, Austria
+ $eventname = "ATE-Graz";
+ $city = "16. August 2013";
+
+ $query = "select * from `locations` where `id`='$locid'";
+ $loc = mysql_fetch_assoc(mysql_query($query));
+
+ $query = "SELECT ROUND(6378.137 * ACOS(0.9999999*((SIN(PI() * $loc[lat] / 180) * SIN(PI() * `locations`.`lat` / 180)) +
+ (COS(PI() * $loc[lat] / 180 ) * COS(PI() * `locations`.`lat` / 180) *
+ COS(PI() * `locations`.`long` / 180 - PI() * $loc[long] / 180)))), -1) AS `distance`, sum(`points`) as pts, `users`.*
+ FROM `locations`
+ inner join `users` on `users`.`locid` = `locations`.`id`
+ inner join `alerts` on `users`.`id`=`alerts`.`memid`
+ inner join `notary` on `users`.`id`=`notary`.`to`
+ WHERE (`alerts`.`general`=1 OR `alerts`.`country`=1 OR `alerts`.`regional`=1 OR `alerts`.`radius`=1)
+ GROUP BY `users`.`id`
+ HAVING `distance` <= '$maxdist'
+ ORDER BY `distance` ";
+ echo $query;
+
+ // comment next line when starting to send mail not only to me
+ // $query = "select * from `users` where `email` like 'cacerttest%'";
+
+ $res = mysql_query($query);
+ $xrows = mysql_num_rows($res);
+
+ while($row = mysql_fetch_assoc($res))
+ {
+ // uncomment next line to send mails ...
+ sendmail($row['email'], "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ // 1x mailing report to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ // 1x mailing report to Arbitrator of case http://wiki.cacert.org/wiki/Arbitrations/a20090525.1
+ sendmail("p.dunkel@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ echo "invitation sent to $xrows recipients.\n";
+
+?>
diff --git a/scripts/52at-ate-wien-email.txt b/scripts/52at-ate-wien-email.txt
new file mode 100644
index 0000000..bb4ce4c
--- /dev/null
+++ b/scripts/52at-ate-wien-email.txt
@@ -0,0 +1,91 @@
+[Deutsch]
+
+Es hat sich viel getan im letzten Jahr. Eine ganze Reihe von bisher
+eher "muendlich ueberlieferten" Regeln wurden in Policies gegossen.
+Neue Prozeduren (z.B. die Assurer Challenge) und Verpflichtungen (z.B.
+in dem CAcert Community Agreement) wurden beschlossen. Die Assurer
+Training Events wollen versuchen, die ganzen Informationen unter's
+Volk zu bringen:
+
+- Welcher Satz fehlt auf alten CAP Formularen?
+- Warum soll ich mir R/L/O einpraegen?
+- Wie verhaelst du dich,
+ wenn du ein fremdes Ausweisdokument das erste Mal pruefst?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den
+Assurer Training Events (ATEs).
+
+Darueberhinaus wird beim ATE der Vorgang der Identitaetsueberpruefung
+trainiert und auditiert, um die Qualitaet der Assurances in der
+taeglichen Praxis zu erfassen. Dabei gilt es moegliche Fehler und
+Fallstricke zu erkennen und aufzudecken. Die Assurer haben also die
+Moeglichkeit, sich mit den Fehlern auseinanderzusetzen und zu erfahren,
+wie diese vermieden werden koennen.
+
+Wie IanG sagte: The ATE or Assurer Training Event is exceptionally
+recommended for all Assurers, and include parts which contribute
+directly to our audit. Come and find out how you can also contribute.
+
+Die kommende Veranstaltung in deiner Naehe findet statt am:
+
+- Dienstag, den 15. Oktober 2013
+- in der Zeit von: 18:00 - ca. 22:00 Uhr
+- Wirtschaftskammer Oesterreich, Raum B3 1 Besprechungsraum
+- Wiedner Hauptstraße 63
+- 1045 Wien
+
+
+Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im
+Wiki [https://wiki.cacert.org/Events/2013-10-15ATE-Wien]
+Blog [http://blog.cacert.org/2013/09/ate-wien-at-2013-10-15/]
+
+Teilnehmer Registrierung mit Rueckantwort:
+ 'Ich moechte am ATE-Graz teilnehmen'
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Kontakt: events@cacert.org
+
+
+
+[English]
+
+During the last year many changes took place inside CAcert. Many "oral"
+rules have been put into Policies. New procedures
+(e.g. Assurer Challenge) and obligations
+(e.g. CAcert Community Agreement) have been put into live.
+The Assurer Training Events (ATE) try to spread this information:
+
+- What is missing on the "old" CAP forms?
+- Why should I remember R/L/O?
+- What can you do if an Assuree shows an ID document unknown to you?
+
+These and more questions will be answered during the
+Assurer Training Events (ATEs)
+
+Furthermore, the ATE trains how to do assurances and audits assurances,
+to measure the quality of assurances in the daily routine. Here are some
+possible errors and pitfalls which need to be found. Assurers have the
+opportunity to see those errors and how to avoid them.
+
+As IanG said: The ATE or Assurer Training Event is exceptionally
+recommended for all Assurers and includes parts which contribute
+directly to our audit. Come and find out how you can also contribute.
+
+The next event held in your area will be:
+
+- Friday, October 15th 2013
+- during: 18:00 - ca. 22:00
+- Austrian Chamber of Commerce, Meetingroom B3 1
+- Wiedner Hauptstraße 63
+- 1045 Vienna
+
+Details to the location can be found:
+Wiki [https://wiki.cacert.org/Events/2013-10-15ATE-Wien]
+Blog [http://blog.cacert.org/2013/09/ate-wien-at-2013-10-15/]
+
+User reply for registration: 'I will attend the ATE-Graz'
+
+The event team is looking forward for your attendance:
+
+Contact: events@cacert.org
diff --git a/scripts/52at-ate-wien-mail.php.txt b/scripts/52at-ate-wien-mail.php.txt
new file mode 100644
index 0000000..109bb81
--- /dev/null
+++ b/scripts/52at-ate-wien-mail.php.txt
@@ -0,0 +1,130 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2013 CAcert Inc.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
+*/
+ include_once("../includes/mysql.php");
+
+ $lines = "";
+ $fp = fopen("52at-ate-wien-email.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+
+// $locid = intval($_REQUEST['location']);
+// $maxdist = intval($_REQUEST['maxdist']);
+// maxdist in [Km]
+ $maxdist = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $locid = 1486658; // Potsdam
+// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+// $locid = 2094781; // Mission Hills (Los Angeles), California, United States
+// $locid = 423655; // Copenhagen, Kobenhavn*, Denmark
+// $locid = 2093625; // Los Angeles, CA ???
+// $locid = 2094326 // Los Angeles (Los Angeles), California, United States
+// $locid = 2257312; // Sydney, New South Wales, Australia
+// $locid = 572764; // Essen, Nordrhein-Westfalen, Germany
+// $locid = 78; // Aachen, Nordrhein-Westfalen, Germany
+// $locid = 1260319; // Muenchen
+// $locid = 266635; // Bonn, Nordrhein-Westfalen, Germany
+// $locid = 873779; // Karlsruhe, Baden-Wuerttemberg, Germany
+// $locid = 520340; // Dusseldorf, Nordrhein-Westfalen, Germany
+// $locid = 2262656; // Melbourne, Victoria, Australia
+// $locid = 2185076; // Raleigh (Wake), North Carolina, United States
+
+// CAcert Assurance and Keysigning event at FUDcon, Lawrence, KS, Jan 19th 2013
+// $locid = 2126955; // Lawrence (Douglas), Kansas, United States
+// $eventname = "CAcert Assurance and Keysigning at FUDcon Lawrence, KS";
+// $city = "January 19th 2013";
+
+// ATE-Kiel 2013-02-11
+// $locid = 919560; // Kiel, Schleswig-Holstein, Germany
+// $eventname = "ATE-Kiel";
+// $city = "11. Februar 2013";
+
+// Linuxtag, Berlin, May 22-25, 2013,
+// $locid = 228950; // Berlin
+// $eventname = "Linuxtag Berlin";
+// $city = "22.-25. Mai, 2013";
+
+// $locid = 1117395; // Lubeck Hansestadt, Schleswig-Holstein, Germany
+// $eventname = "ATE-Luebeck";
+// $city = "07. Juni 2013";
+
+// $locid = 675661; // Graz, Steiermark, Austria
+// $eventname = "ATE-Graz";
+// $city = "16. August 2013";
+
+ $locid = 1992733; // Wien, Wien, Austria
+ $eventname = "ATE-Wien";
+ $city = "15. Oktober 2013";
+
+ $query = "select * from `locations` where `id`='$locid'";
+ $loc = mysql_fetch_assoc(mysql_query($query));
+
+ $query = "SELECT ROUND(6378.137 * ACOS(0.9999999*((SIN(PI() * $loc[lat] / 180) * SIN(PI() * `locations`.`lat` / 180)) +
+ (COS(PI() * $loc[lat] / 180 ) * COS(PI() * `locations`.`lat` / 180) *
+ COS(PI() * `locations`.`long` / 180 - PI() * $loc[long] / 180)))), -1) AS `distance`, sum(`points`) as pts, `users`.*
+ FROM `locations`
+ inner join `users` on `users`.`locid` = `locations`.`id`
+ inner join `alerts` on `users`.`id`=`alerts`.`memid`
+ inner join `notary` on `users`.`id`=`notary`.`to`
+ WHERE (`alerts`.`general`=1 OR `alerts`.`country`=1 OR `alerts`.`regional`=1 OR `alerts`.`radius`=1)
+ GROUP BY `users`.`id`
+ HAVING `distance` <= '$maxdist'
+ ORDER BY `distance` ";
+ echo $query;
+
+ // comment next line when starting to send mail not only to me
+ // $query = "select * from `users` where `email` like 'cacerttest%'";
+
+ $res = mysql_query($query);
+ $xrows = mysql_num_rows($res);
+
+ while($row = mysql_fetch_assoc($res))
+ {
+ // uncomment next line to send mails ...
+ sendmail($row['email'], "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ // 1x mailing report to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ // 1x mailing report to Arbitrator of case http://wiki.cacert.org/wiki/Arbitrations/a20090525.1
+ sendmail("p.dunkel@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ echo "invitation sent to $xrows recipients.\n";
+
+?>
diff --git a/scripts/cron/permissionreview.php b/scripts/cron/permissionreview.php
index 0f2fc2e..ca95f18 100755
--- a/scripts/cron/permissionreview.php
+++ b/scripts/cron/permissionreview.php
@@ -27,7 +27,7 @@ $ORGANISATION_ASSURANCE_OFFICER = 'oao@cacert.org';
//defines to whom to send the lists
$flags = array(
- 'admin' => array(
+ 'admin=1' => array(
'name' => 'Support Engineer',
'own' => false, //Don't send twice
'board' => true,
@@ -35,8 +35,8 @@ $flags = array(
'ao' => false,
'oao' => false
),
-
- 'orgadmin' => array(
+
+ 'orgadmin=1' => array(
'name' => 'Organisation Assurer',
'own' => true,
'board' => true,
@@ -44,8 +44,8 @@ $flags = array(
'ao' => true,
'oao' => true
),
-
- 'board' => array(
+
+ 'board=1' => array(
'name' => 'Board Member',
'own' => false,
'board' => true,
@@ -53,8 +53,8 @@ $flags = array(
'ao' => true,
'oao' => false
),
-
- 'ttpadmin' => array(
+
+ 'ttpadmin=1' => array(
'name' => 'Trusted Third Party Admin',
'own' => true,
'board' => true,
@@ -62,8 +62,17 @@ $flags = array(
'ao' => true,
'oao' => true
),
-
- 'tverify' => array(
+
+ 'ttpadmin=2' => array(
+ 'name' => 'Trusted Third Party TOPUP Admin',
+ 'own' => true,
+ 'board' => true,
+ 'support' => true,
+ 'ao' => true,
+ 'oao' => true
+ ),
+
+ 'tverify=1' => array(
'name' => 'Tverify Admin',
'own' => false,
'board' => true,
@@ -71,8 +80,8 @@ $flags = array(
'ao' => true,
'oao' => false
),
-
- 'locadmin' => array(
+
+ 'locadmin=1' => array(
'name' => 'Location Admin',
'own' => false,
'board' => true,
@@ -80,30 +89,51 @@ $flags = array(
'ao' => false,
'oao' => false
),
+
+ 'adadmin=1' => array(
+ 'name' => 'submit status for Advertising Admin',
+ 'own' => false,
+ 'board' => true,
+ 'support' => true,
+ 'ao' => false,
+ 'oao' => false
+ ),
+
+ 'adadmin=2' => array(
+ 'name' => 'approve status for Advertising Admin',
+ 'own' => false,
+ 'board' => true,
+ 'support' => true,
+ 'ao' => false,
+ 'oao' => false
+ ),
+
+
);
// Build up list of various admins
$adminlist = array();
foreach ($flags as $flag => $flag_properties) {
- $query = "select `fname`, `lname`, `email` from `users` where `$flag` = 1";
+ $flagname = explode('=', $flag, 2 );
+ $query = "select `fname`, `lname`, `email` from `users` where `$flagname[0]` = '$flagname[1]'";
if(! $res = mysql_query($query) ) {
fwrite(STDERR,
"MySQL query for flag $flag failed:\n".
"\"$query\"\n".
mysql_error()
);
-
+
continue;
}
-
+
$adminlist[$flag] = array();
-
+
while ($row = mysql_fetch_assoc($res)) {
$adminlist[$flag][] = $row;
}
-
-
+
+
// Send mail to admins of this group if 'own' is set
if ($flag_properties['own']) {
foreach ($adminlist[$flag] as $admin) {
@@ -117,19 +147,20 @@ and report to the responsible team leader or board
EOF;
-
+
foreach ($adminlist[$flag] as $colleague) {
$message .= "$colleague[fname] $colleague[lname] $colleague[email]\n";
}
-
+
$message .= <<<EOF
Best Regards,
CAcert Support
EOF;
-
+
sendmail($admin['email'], "Permissions Review", $message, 'support@cacert.org');
+ echo "Sent $flag_properties[name] mail to $admin[email]\n";
}
}
}
@@ -152,7 +183,7 @@ foreach ($flags as $flag => $flag_properties) {
foreach ($adminlist[$flag] as $colleague) {
$message .= "$colleague[fname] $colleague[lname] $colleague[email]\n";
}
-
+
$message .= "\n\n";
}
}
@@ -163,12 +194,13 @@ Best Regards,
CAcert Support
EOF;
-foreach ($adminlist['admin'] as $support_engineer) {
+foreach ($adminlist['admin=1'] as $support_engineer) {
sendmail(
$support_engineer['email'],
"Permissions Review",
$message,
'support@cacert.org');
+ echo "Sent Support Engineer mail to $support_engineer[email]\n";
}
@@ -188,14 +220,14 @@ foreach (array(
Dear $values[description],
it's time for the permission review again. Here is the list of privileged users
-in the CAcert web application. Please review them and also ask the persons
+in the CAcert web application. Please review them and also ask the persons
responsible for an up-to-date copy of access lists not directly recorded in the
-web application (critical admins, software assessors etc.)
+web application (critical admins, software assessors etc.)
EOF;
-
+
foreach ($flags as $flag => $flag_properties) {
if ($flag_properties[$key]) {
$message .= "List of $flag_properties[name]s:\n\n";
@@ -205,13 +237,14 @@ EOF;
$message .= "\n\n";
}
}
-
+
$message .= <<<EOF
Best Regards,
CAcert Support
EOF;
-
+
sendmail($values['email'], "Permissions Review", $message, 'support@cacert.org');
+ echo "Sent $values[description] mail to $values[email]\n";
}
diff --git a/scripts/cron/refresh_stats.php b/scripts/cron/refresh_stats.php
new file mode 100755
index 0000000..2a3d2b5
--- /dev/null
+++ b/scripts/cron/refresh_stats.php
@@ -0,0 +1,307 @@
+#!/usr/bin/php -q
+<?php
+/*
+LibreSSL - CAcert web application
+Copyright (C) 2004-2012 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
+*/
+
+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
+ * the MySQL result set
+ */
+function sql_query($sql) {
+ $res = mysql_query($sql);
+ if (!$res) {
+ fwrite(STDERR, "MySQL query failed:\n\"$sql\"\n".mysql_error());
+ die(1);
+ }
+
+ return $res;
+}
+
+function tc($sql) {
+ $row = mysql_fetch_assoc(sql_query($sql));
+ return(intval($row['count']));
+}
+
+/**
+* writes new data to cache, create cache or update existing cache, set current
+* time stamp
+* @return boolean
+*/
+function updateCache($stats) {
+ $timestamp = time();
+ $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'");
+ if (mysql_num_rows($res) !== 1) {
+ fwrite(STDERR, "Error on inserting the new statistic");
+ return false;
+ }
+
+ sql_query("delete from `statscache` where `timestamp` != '$timestamp'");
+ return true;
+}
+
+/**
+* get statistics data from live tables, takes a long time so please try to use the
+* cache
+* @return array
+*/
+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`
+ where `expire` != 0");
+ $certs += tc("select count(*) as `count` from `gpg`
+ where `expire` != 0");
+ $certs += tc("select count(*) as `count` from `orgdomaincerts`
+ where `expire` != 0");
+ $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`
+ where `revoked` = 0 and `expire` > NOW()");
+ $certs += tc("select count(*) as `count` from `gpg`
+ where `expire` > NOW()");
+ $certs += tc("select count(*) as `count` from `orgdomaincerts`
+ where `revoked` = 0 and `expire` > NOW()");
+ $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'"));
+
+ $stats['users_1to49'] = number_format(tc(
+ "select count(*) as `count` from (
+ select 1 from `notary`
+ where `deleted` = 0
+ 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`
+ where `deleted` = 0
+ 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 (
+ select sum(`points`) from `notary`
+ where `to`=`users`.`id`
+ and `deleted` = 0
+ ) >= 100
+ and not exists(
+ select 1 from `cats_passed` as `cp`, `cats_variant` as `cv`
+ where `cp`.`user_id`=`users`.`id`
+ and `cp`.`variant_id`=`cv`.`id`
+ and `cv`.`type_id`=1
+ )"
+ ));
+
+ $stats['aussurers_with_test'] = number_format(tc(
+ "select count(*) as `count` from `users`
+ where (
+ select sum(`points`) from `notary`
+ where `to`=`users`.`id`
+ and `deleted` = 0
+ ) >= 100
+ and exists(
+ select 1 from `cats_passed` as `cp`, `cats_variant` as `cv`
+ where `cp`.`user_id`=`users`.`id`
+ and `cp`.`variant_id`=`cv`.`id`
+ and `cv`.`type_id`=1
+ )"
+ ));
+
+ $stats['points_issued'] = number_format(tc(
+ "select sum(greatest(`points`, `awarded`)) as `count` from `notary`
+ where `deleted` = 0
+ and `method` = 'Face to Face Meeting'"));
+
+ $totalusers=0;
+ $totassurers=0;
+ $totalcerts=0;
+ for($i = 0; $i < 12; $i++) {
+ $first_ts = mktime(0, 0, 0, date("m") - $i, 1, date("Y"));
+ $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`
+ 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`
+ where `when` >= '$first' and `when` < '$next_month'
+ and `method`!='Administrative Increase'
+ 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'
+ and `expire` != 0");
+ $certs += tc(
+ "select count(*) as `count` from `emailcerts`
+ where `created` >= '$first' and `created` < '$next_month'
+ and `expire` != 0");
+ $certs += tc(
+ "select count(*) as `count` from `gpg`
+ where `issued` >= '$first' and `issued` < '$next_month'
+ and `expire` != 0");
+ $certs += tc(
+ "select count(*) as `count` from `orgdomaincerts`
+ where `created` >= '$first' and `created` < '$next_month'
+ and `expire` != 0");
+ $certs += tc(
+ "select count(*) as `count` from `orgemailcerts`
+ where `created` >= '$first' and `created` < '$next_month'
+ and `expire` != 0");
+ $totalcerts += $certs;
+
+ $tmp_arr = array();
+ $tmp_arr['date'] = date("Y-m", $first_ts);
+ $tmp_arr['new_users'] = number_format($users);
+ $tmp_arr['new_assurers'] = number_format($assurers);
+ $tmp_arr['new_certificates'] = number_format($certs);
+
+ $stats['growth_last_12m'][] = $tmp_arr;
+ }
+ $stats['growth_last_12m_total'] = array(
+ 'new_users' => number_format($totalusers),
+ 'new_assurers' => number_format($totassurers),
+ 'new_certificates' => number_format($totalcerts),
+ );
+
+ $totalcerts = 0;
+ $totalusers = 0;
+ $totassurers = 0;
+ for($i = date("Y"); $i >= 2002; $i--) {
+ $first_ts = mktime(0, 0, 0, 1, 1, $i);
+ $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`
+ 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`
+ where `when` >= '$first' and `when` < '$next_year'
+ and `method`!='Administrative Increase'
+ 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'
+ and `expire` != 0");
+ $certs += tc(
+ "select count(*) as `count` from `emailcerts`
+ where `created` >= '$first' and `created` < '$next_year'
+ and `expire` != 0");
+ $certs += tc(
+ "select count(*) as `count` from `gpg`
+ where `issued` >= '$first' and `issued` < '$next_year'
+ and `expire` != 0");
+ $certs += tc(
+ "select count(*) as `count` from `orgdomaincerts`
+ where `created` >= '$first' and `created` < '$next_year'
+ and `expire` != 0");
+ $certs += tc(
+ "select count(*) as `count` from `orgemailcerts`
+ where `created` >= '$first' and `created` < '$next_year'
+ and `expire` != 0");
+ $totalcerts += $certs;
+
+ $tmp_arr = array();
+ $tmp_arr['date'] = $i;
+ $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(
+ 'new_users' => number_format($totalusers),
+ 'new_assurers' => number_format($totassurers),
+ 'new_certificates' => number_format($totalcerts),
+ );
+
+ return $stats;
+}
+
+
+$stats = getDataFromLive();
+if (! updateCache($stats) ) {
+ fwrite(STDERR,
+ "An error occured. The statistics were not successfully updated!");
+ die(1);
+}
diff --git a/scripts/mail-weak-keys.php b/scripts/mail-weak-keys.php
index 95c0e4f..c85aec8 100644
--- a/scripts/mail-weak-keys.php
+++ b/scripts/mail-weak-keys.php
@@ -18,7 +18,7 @@ You received this email because a certificate issued to you is vulnerable:
Server Certificate, Serial $cert_serial, expiring $cert_expire, CN $cert_CN
To rectify the problem CAcert will revoke all vulnerable certificates (including yours) on $action_date.
-CAcert will no longer accept vulnerable certificate requests for signing. In future all Certficate
+CAcert will no longer accept vulnerable certificate requests for signing. In future all Certificate
Signing Requests must be backed by private keys with a key length at least 2048 bits and no other known vulnerabilities.
You should submit a new Certificate Signing Request of acceptable strength as soon as possible
@@ -51,13 +51,13 @@ Client Certificate, Serial $cert_serial, expiring $cert_expire, CN $cert_CN
To rectify the problem CAcert will revoke all vulnerable certificates (including yours) on $action_date.
CAcert will no longer accept vulnerable certificate requests for signing. In future all
-client certficates must be backed by private keys with a key length at least 1024 bits
+client certificates must be backed by private keys with a key length at least 1024 bits
and no other known vulnerabilities.
This means that you should replace your current certificate with a new one of acceptable strength.
If you use Firefox or Chrome, select 'Keysize: High Grade' before 'Create Certificate Request'.
If you use Internet Explorer, select 'Microsoft Strong Cryptographic Provider'. If you select an
-option that generates a weak key (eg 'Microsoft Base Cryptographic Provider v1.0') your certficate
+option that generates a weak key (eg 'Microsoft Base Cryptographic Provider v1.0') your certificate
request will be rejected.
Kind regards
@@ -83,7 +83,7 @@ You received this email because a certificate issued to you is vulnerable:
Organisation Server Certificate, Serial $cert_serial, expiring $cert_expire, CN $cert_CN
To rectify the problem CAcert will revoke all vulnerable certificates (including yours) on $action_date.
-CAcert will no longer accept vulnerable certificate requests for signing. In future all Certficate
+CAcert will no longer accept vulnerable certificate requests for signing. In future all Certificate
Signing Requests must be backed by private keys with a key length at least 2048 bits and no other known vulnerabilities.
You should submit a new Certificate Signing Request of acceptable strength as soon as possible
@@ -116,13 +116,13 @@ Organisation Client Certificate, Serial $cert_serial, expiring $cert_expire, CN
To rectify the problem CAcert will revoke all vulnerable certificates (including yours) on $action_date.
CAcert will no longer accept vulnerable certificate requests for signing. In future all
-client certficates must be backed by private keys with a key length at least 1024 bits
+client certificates must be backed by private keys with a key length at least 1024 bits
and no other known vulnerabilities.
This means that you should replace your current certificate with a new one of acceptable strength.
If you use Firefox or Chrome, select 'Keysize: High Grade' before 'Create Certificate Request'.
If you use Internet Explorer, select 'Microsoft Strong Cryptographic Provider'. If you select an
-option that generates a weak key (eg 'Microsoft Base Cryptographic Provider v1.0') your certficate
+option that generates a weak key (eg 'Microsoft Base Cryptographic Provider v1.0') your certificate
request will be rejected.
Kind regards