summaryrefslogtreecommitdiff
path: root/scripts
diff options
context:
space:
mode:
authorMarkus Warg <mw@it-sls.de>2010-03-29 09:54:06 +0200
committerMarkus Warg <mw@it-sls.de>2010-03-29 09:54:06 +0200
commit9dceece06fbdc98add6f76f0b1aec05891a394c4 (patch)
treef7227c28ca5f79f30c2ec81ba1a09a4fe3972436 /scripts
parent5b68967def224a00f54eb54946ff17301bbd3cdb (diff)
downloadcacert-9dceece06fbdc98add6f76f0b1aec05891a394c4.tar.gz
cacert-9dceece06fbdc98add6f76f0b1aec05891a394c4.tar.xz
cacert-9dceece06fbdc98add6f76f0b1aec05891a394c4.zip
remove cacert/ prefix
Diffstat (limited to 'scripts')
-rw-r--r--scripts/CVS/Entries53
-rw-r--r--scripts/CVS/Repository1
-rw-r--r--scripts/CVS/Root1
-rw-r--r--scripts/Makefile14
-rwxr-xr-xscripts/addpoints.php54
-rwxr-xr-xscripts/areacheck.php64
-rw-r--r--scripts/assurer.php53
-rw-r--r--scripts/assurer.txt47
-rw-r--r--scripts/ate-bi-email.txt124
-rw-r--r--scripts/ate-bi-mail.php78
-rw-r--r--scripts/ate-d-email.txt127
-rw-r--r--scripts/ate-d-mail.php.txt85
-rw-r--r--scripts/ate-de09-email.txt117
-rw-r--r--scripts/ate-de09-mail.php.txt88
-rw-r--r--scripts/ate-de11-email.txt37
-rw-r--r--scripts/ate-de11-mail.php.txt92
-rw-r--r--scripts/ate-f-email.txt40
-rw-r--r--scripts/ate-f-mail.php.txt83
-rw-r--r--scripts/ate-goteborg-s16-email.txt58
-rw-r--r--scripts/ate-goteborg-s16-mail.php.txt110
-rw-r--r--scripts/ate-hh-email.txt40
-rw-r--r--scripts/ate-hh-mail.php.txt164
-rw-r--r--scripts/ate-l-email.txt40
-rw-r--r--scripts/ate-l-mail.php.txt84
-rw-r--r--scripts/ate-m-email.txt36
-rw-r--r--scripts/ate-m-mail.php.txt83
-rw-r--r--scripts/ate-nl01-email.txt88
-rw-r--r--scripts/ate-nl01-mail.php.txt89
-rw-r--r--scripts/ate-s-email.txt40
-rw-r--r--scripts/ate-s-mail.php.txt83
-rw-r--r--scripts/ate-sydney-au20-email.txt30
-rw-r--r--scripts/ate-sydney-au20-mail.php.txt130
-rw-r--r--scripts/ate-us02-email.txt27
-rw-r--r--scripts/ate-us02-mail.php.txt97
-rw-r--r--scripts/blit-de15-email.txt23
-rw-r--r--scripts/blit-de15-mail.php.txt105
-rw-r--r--scripts/cebitemail.txt36
-rw-r--r--scripts/cleanthem.pl24
-rwxr-xr-xscripts/clientcerts.php1
-rwxr-xr-xscripts/consistence.php105
-rwxr-xr-xscripts/country.php28
-rw-r--r--scripts/findexp3.pl14
-rw-r--r--scripts/findnull.pl14
-rwxr-xr-xscripts/gpgcerts.php1
-rw-r--r--scripts/gpgcheck3.php245
-rw-r--r--scripts/gpgfillmissingemail.php88
-rw-r--r--scripts/gpgfillmissingkeyid.php73
-rw-r--r--scripts/koelnemail.txt28
-rw-r--r--scripts/lisa-us13-email.txt12
-rw-r--r--scripts/lisa-us13-mail.php.txt96
-rw-r--r--scripts/mission-hills-ca-us17-email.txt17
-rw-r--r--scripts/mission-hills-ca-us17-mail.php.txt115
-rwxr-xr-xscripts/nearest.php34
-rwxr-xr-xscripts/newsletter.php50
-rwxr-xr-xscripts/newslettercebit.php47
-rwxr-xr-xscripts/notify.php42
-rw-r--r--scripts/osd-copenhagen-dk18-email.txt35
-rw-r--r--scripts/osd-copenhagen-dk18-mail.php.txt120
-rw-r--r--scripts/ost-de14-email.txt22
-rw-r--r--scripts/ost-de14-mail.php.txt100
-rwxr-xr-xscripts/removedead.php86
-rw-r--r--scripts/runclient.c13
-rw-r--r--scripts/rungpg.c13
-rw-r--r--scripts/runserver.c13
-rw-r--r--scripts/scale8x-los-angeles-ca-us19-email.txt36
-rw-r--r--scripts/scale8x-los-angeles-ca-us19-mail.php.txt125
-rwxr-xr-xscripts/scanforexponents.php127
-rwxr-xr-xscripts/servercerts.php1
-rw-r--r--scripts/sfd-de12-email.txt24
-rw-r--r--scripts/sfd-de12-mail.php.txt94
-rw-r--r--scripts/test.c9
-rwxr-xr-xscripts/updatesort.php77
-rwxr-xr-xscripts/warning.php100
73 files changed, 4550 insertions, 0 deletions
diff --git a/scripts/CVS/Entries b/scripts/CVS/Entries
new file mode 100644
index 0000000..f106e2e
--- /dev/null
+++ b/scripts/CVS/Entries
@@ -0,0 +1,53 @@
+/Makefile/1.4/Sun Feb 5 15:11:42 2006//
+/runclient.c/1.2/Thu Jun 9 15:21:55 2005//
+/rungpg.c/1.1/Fri Feb 3 18:45:23 2006//
+/runserver.c/1.2/Thu Jun 9 15:21:55 2005//
+/test.c/1.1/Sun Mar 5 11:18:16 2006//
+/clientcerts.php/1.14/Wed Feb 28 17:27:22 2007//
+/gpgcerts.php/1.8/Wed Feb 28 17:27:22 2007//
+/servercerts.php/1.17/Wed Feb 28 17:27:22 2007//
+/cebitemail.txt/1.1/Sun Jan 13 14:04:23 2008//
+/findexp3.pl/1.1/Sun Jan 13 13:55:32 2008//
+/areacheck.php/1.2/Sun Apr 6 19:45:25 2008//
+/country.php/1.2/Sun Apr 6 19:45:25 2008//
+/gpgcheck3.php/1.2/Sun Apr 6 19:45:25 2008//
+/gpgfillmissingemail.php/1.2/Sun Apr 6 19:45:25 2008//
+/gpgfillmissingkeyid.php/1.2/Sun Apr 6 19:45:25 2008//
+/nearest.php/1.2/Sun Apr 6 19:45:25 2008//
+/newslettercebit.php/1.3/Sun Apr 6 19:45:25 2008//
+/notify.php/1.2/Sun Apr 6 19:45:25 2008//
+/scanforexponents.php/1.2/Sun Apr 6 19:45:25 2008//
+/updatesort.php/1.5/Sun Apr 6 19:45:25 2008//
+/warning.php/1.6/Sun Apr 6 19:45:25 2008//
+/newsletter.php/1.1/Thu May 22 23:01:10 2008//
+/koelnemail.txt/1.2/Sat Jun 7 02:28:57 2008//
+/addpoints.php/1.3/Sun Apr 5 00:44:04 2009//
+/removedead.php/1.9/Sun Apr 5 00:44:04 2009//
+/ate-bi-email.txt/1.1/Fri May 22 05:09:53 2009//
+/ate-bi-mail.php/1.1/Fri May 22 05:09:54 2009//
+/ate-d-email.txt/1.1/Fri May 22 05:09:54 2009//
+/ate-d-mail.php.txt/1.1/Fri May 22 05:09:54 2009//
+/ate-f-email.txt/1.1/Fri May 22 05:09:54 2009//
+/ate-f-mail.php.txt/1.1/Fri May 22 05:09:55 2009//
+/ate-hh-email.txt/1.1/Fri May 22 05:09:55 2009//
+/ate-hh-mail.php.txt/1.1/Fri May 22 05:09:55 2009//
+/ate-l-email.txt/1.1/Fri May 22 05:09:55 2009//
+/ate-l-mail.php.txt/1.1/Fri May 22 05:09:56 2009//
+/ate-m-email.txt/1.1/Fri May 22 05:09:56 2009//
+/ate-m-mail.php.txt/1.1/Fri May 22 05:09:57 2009//
+/ate-s-email.txt/1.1/Fri May 22 05:09:57 2009//
+/ate-s-mail.php.txt/1.1/Fri May 22 05:09:57 2009//
+/assurer.php/1.1/Sun May 31 00:41:03 2009//
+/assurer.txt/1.1/Sun May 31 00:41:03 2009//
+/ate-de09-email.txt/1.1/Thu Jun 11 08:04:05 2009//
+/ate-de09-mail.php.txt/1.1/Thu Jun 11 08:04:04 2009//
+/ate-nl01-email.txt/1.1/Thu Jun 11 15:10:27 2009//
+/ate-nl01-mail.php.txt/1.1/Thu Jun 11 15:10:27 2009//
+/ate-us02-email.txt/1.1/Mon Jun 15 09:33:47 2009//
+/ate-us02-mail.php.txt/1.1/Mon Jun 15 09:33:47 2009//
+/ate-de11-email.txt/1.1/Tue Jun 30 07:52:41 2009//
+/ate-de11-mail.php.txt/1.1/Tue Jun 30 07:52:42 2009//
+/findnull.pl/1.1/Fri Jul 31 21:24:37 2009//
+/sfd-de12-email.txt/1.1/Sat Sep 12 19:10:32 2009//
+/sfd-de12-mail.php.txt/1.1/Sat Sep 12 19:10:33 2009//
+D
diff --git a/scripts/CVS/Repository b/scripts/CVS/Repository
new file mode 100644
index 0000000..8b2e44f
--- /dev/null
+++ b/scripts/CVS/Repository
@@ -0,0 +1 @@
+cacert/scripts
diff --git a/scripts/CVS/Root b/scripts/CVS/Root
new file mode 100644
index 0000000..a363882
--- /dev/null
+++ b/scripts/CVS/Root
@@ -0,0 +1 @@
+/var/lib/cvs
diff --git a/scripts/Makefile b/scripts/Makefile
new file mode 100644
index 0000000..38cff8e
--- /dev/null
+++ b/scripts/Makefile
@@ -0,0 +1,14 @@
+all: runserver.c runclient.c
+ gcc -O2 -o runserver runserver.c
+ gcc -O2 -o runclient runclient.c
+ gcc -O2 -o rungpg rungpg.c
+ chown root:chrapach runserver runclient rungpg
+ chmod 4710 runserver runclient rungpg
+
+clean:
+ rm -f runserver runclient rungpg test
+
+test: test.c
+ gcc -O2 -o test test.c
+ chown root:chrapach test
+ chmod 4710 test
diff --git a/scripts/addpoints.php b/scripts/addpoints.php
new file mode 100755
index 0000000..a518ceb
--- /dev/null
+++ b/scripts/addpoints.php
@@ -0,0 +1,54 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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
+*/
+
+// This script seems to add points for assurances that didn't received their points automatically before.
+
+ include_once("../includes/mysql.php");
+
+ $query = "select * from `notary` group by `from`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "select *,sum(`points`) as `points` from `users`, `notary` where `users`.`id`=`notary`.`to` and `users`.`id`='".$row['from']."' group by `notary`.`to`";
+ $drow = mysql_fetch_assoc(mysql_query($query));
+ if($drow['points'] < 100 || $drow['points'] >= 150)
+ continue;
+ $query = "select * from `notary` where `from`='".$drow['id']."' and `to`='".$drow['id']."'";
+ $num = mysql_num_rows(mysql_query($query));
+ $query = "select * from `notary` where `from`='".$drow['id']."' and `to`!='".$drow['id']."'";
+ $newnum = mysql_num_rows(mysql_query($query));
+ if($num < $newnum)
+ {
+ echo $drow['fname']." ".$drow['lname']." <".$drow['email']."> (memid: ".$drow['id']." points: ".$drow['points']." - num: $num newnum: $newnum)\n";
+ for($i = $newnum; $i > $num; $i--)
+ {
+ $newpoints = 2;
+ if($drow['points'] + 1>= 150)
+ break;
+ if($drow['points'] + 2 > 150)
+ $newpoints = 1;
+ $query = "insert into `notary` set `from`='".$drow['id']."', `to`='".$drow['id']."',
+ `points`='$newpoints', `method`='Administrative Increase', `date`=NOW()";
+ mysql_query($query);
+ $drow['points'] += $newpoints;
+ fix_assurer_flag($drow['id'])
+ }
+ }
+ }
+?>
diff --git a/scripts/areacheck.php b/scripts/areacheck.php
new file mode 100755
index 0000000..4d59e9b
--- /dev/null
+++ b/scripts/areacheck.php
@@ -0,0 +1,64 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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");
+
+ $sendmail = 0;
+ $locid = 2189758;
+
+ $fp = fopen("email.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+ $query = "select * from `locations` where `id`='$locid'";
+ $loc = mysql_fetch_assoc(mysql_query($query));
+ $query = "select * from `locations` where (`lat` > ".$loc['lat']."-10 and `lat`<".$loc['lat']."+10 and
+ `long`>".$loc['long']."-10 and `long`<".$loc['long']."+10) or `regid`=4576";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "select * from `users` where `id`='1'";
+ $query = "select * from `users`,`alerts` where `users`.`locid`='".$row['id']."' and `users`.`id`=`alerts`.`memid` and
+ (`general`='1' or `country`='1' or `regional`='1' or `radius`='1')";
+ $dres = mysql_query($query);
+ if(mysql_num_rows($dres) > 0)
+ {
+ $ddres = mysql_query("select * from `regions` where `id`='$row[regid]'");
+ $ddrow = mysql_fetch_assoc($ddres);
+ echo "Location: ".$row['name'].", $ddrow[name]\n";
+ }
+ while($user = mysql_fetch_assoc($dres))
+ {
+ $ddres = mysql_query("select sum(`points`) as `tp` from `notary` where `to`='".$user['id']."'");
+ $ddrow = mysql_fetch_assoc($ddres);
+ echo $user['fname']." ".$user['lname']." (".$user['email'].") - ".$user['radius']."/$ddrow[tp]\n";
+
+ if($sendmail == 1)
+ {
+echo "Mail sent!\n";
+ $body = "Hi ".$user['fname'].",\n\n".$lines;
+ sendmail($user['email'], "[CAcert.org] CAcert Assurers at Ohio Linux Festival", $body, "duane@cacert.org", "", "", "CAcert Team");
+ }
+ }
+ }
+?>
diff --git a/scripts/assurer.php b/scripts/assurer.php
new file mode 100644
index 0000000..c649fbf
--- /dev/null
+++ b/scripts/assurer.php
@@ -0,0 +1,53 @@
+#!/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("assurer.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+ $query = "
+select u.email, fname, lname, sum(n.points) from users u, notary n
+ where n.to=u.id
+ and not exists(select 1 from cats_passed cp where cp.user_id=u.id)
+ and exists(select 1 from notary n2 where n2.from=u.id and year(n2.`when`)>2007)
+ and (select count(*) from notary n3 where n3.from=u.id) > 1
+ group by email, fname, lname
+ having sum(points)>99;
+ ";
+// echo $query;
+// comment next line when starting to send mail not only to me
+ $res = mysql_query($query);
+ $xrows = mysql_num_rows($res);
+ while($row = mysql_fetch_assoc($res))
+ {
+ echo $row['pts']."..".$row['email']."...\n";
+ // uncomment next line to send mails ...
+ sendmail($row['email'], "[CAcert.org] Assurer Test", $lines, "teus@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("philipp@cacert.org", "[CAcert.org] Assurer Test", $lines, "teus@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ // 1x mailing report to events.cacert.org
+ sendmail("philipp@cacert.org", "[CAcert.org] Assurer Report", "assurer information sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Assurer Organisation", "returns@cacert.org", 1);
+?>
diff --git a/scripts/assurer.txt b/scripts/assurer.txt
new file mode 100644
index 0000000..318ff90
--- /dev/null
+++ b/scripts/assurer.txt
@@ -0,0 +1,47 @@
+Dear Valued CAcert Member,
+
+CAcert has implemented a policy for certifying assurers, which was
+announced on 30th of May 2008. Since the implementation of the
+CAcert Assurer Challenge Test System (CATS) last year more than
+1600 Assurers have taken and passed the challenge and are now
+official CAcert Assurers, fully compliant with the CAcert Assurance
+Policy. This email is being sent to those of you who have been
+active in performing assurances in the past year, but who have not
+taken and passed the Assurer Challenge Test yet.
+
+It is no longer permissible for anyone to assure for CAcert who
+has not passed the CATS. Please visit the CAcert Wiki documentation
+pages for the Assurer-Challenge
+<http://wiki.cacert.org/wiki/AssurerChallenge> and follow the
+instructions. Make sure you have installed your CAcert client
+certificate into your browser as this system only allows you to
+login with a client certificate. The Assurers Handbook
+<http://wiki.cacert.org/wiki/AssuranceHandbook2> will provide you
+with more detailed information about CAcert Assurances and how to
+make those assurances successful.
+
+Why is CAcert doing all this? CAcert has been and still is
+undergoing many changes since early 2008. CAcert has become a
+Community with policies in place to issue secure, free and highly
+qualified signed certificates. CAcert has become a recognized and
+accepted Certificate Authority on the internet. To maintain this
+status CAcert will be audited, and the audit must validate that
+assurers have an understanding of the CAcert policies and the
+assurance process. A successful audit has the potential for our
+new CAcert Root Key to be included into the Mozilla CA list this
+year. It is our hope that all our assurers will complete the
+CATS, the audit will be successful and CAcert will be featured
+on Mozilla.
+
+You have been involved with CAcert and have helped to increase
+the assured member community we have today. Your contribution
+is greatly appreciated because CAcert success and acceptance
+depends on you. We will be very happy to give you any support
+you may need to complete this process, so please let us know if
+you have any questions (support@cacert.org).
+
+Thank you,
+
+CAcert Inc. Committee
+Teus Hagen
+President
diff --git a/scripts/ate-bi-email.txt b/scripts/ate-bi-email.txt
new file mode 100644
index 0000000..5d3c21d
--- /dev/null
+++ b/scripts/ate-bi-email.txt
@@ -0,0 +1,124 @@
+CAcert auf der "mac at camp - on tour" in Bielefeld=0D
+=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
+
+Im Rahmen des "mac at camp - on tour" Bielefeld, 1.-3. Mai 2009
+ mac at camp ist Europas groesstes Mac-User-Treffen
+wird CAcert am Samstag, 2. Mai mit 2 Veranstaltungen dabei sein.
+
+* CAcert Workshop "CAcert, Was ist das?" (13:00-14:00)
+=20=20=20=20Speaker: Markus Hardt
+* CAcert Workshop "Assurer Training Event" (19:30-21:30)
+=20=20=20=20Speaker: Mario Lipinski, Dirk Astrath, Ulrich Schroeter
+
+Im Rahmen der CAcert Weiterbildungsreihe "Assurer Training Events (ATE's)"
+das in diesem Fruehjahr in vielen Deutschen und
+Europaeischen Staedten stattfindet wird auch ein ebensolches
+Assurer Training Event in Bielefeld angeboten.
+
+Das Assurer Training Event findet am
+Samstag, 2. Mai in der Zeit von 19:30 bis 21:30 statt.
+
+Fuer Besucher des "mac at camp - on tour" sind
+die Workshop und ATE Veranstaltungen im
+Ticket-Preis eingeschlossen.
+Fuer Nicht-Teilnehmer des "mac at camp - on tour" wird es einen
+reduzierten Ticket-Preis nur fuer diese Abendveranstaltung
+in Hoehe von 15,- Euro geben.
+Es gibt dann dafuer auch ein offizielles ATE-Ticket.
+
+
+Zielgruppe
+----------
+Primaere Zielgruppe sind Assurer mit oder ohne abgeschlossene
+Assurer Challenge und solche die es noch werden wollen.
+Auch "alte Hasen" sind explizit angesprochen, denn wer die Diskussionen
+auf den Mailinglisten nicht dauernd mitverfolgen kann wird sicher viele
+Informationen nicht mitbekommen haben.
+
+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 der Community Agreement) wurden beschlossen. Die Assurer
+Training Events wollen versuchen die ganzen Informationen "unter's
+Volk" zu bringen.
+
+Und natuerlich sind auch "Assurees" die lediglich Assurance Punkte
+sammeln wollen gerne gesehen. Idealerweise welche mit ungewoehnlichen
+Ausweispapieren.
+
+
+Workshop "CAcert, Was ist das?"
+-------------------------------
+Die Veranstaltung um 13 Uhr ist fuer das breitere Publikum und als
+Assurance Event bestimmt, um CAcert einer weiteren Oeffentlichkeit
+Vorzustellen und Assurances durchzufuehren.
+Interressierte sollten bitte einen gueltigen Lichtbilsausweis
+(Ausweis, Reisepass) dabeihaben und am besten noch ein zweites
+Amtliches Dokument (Fuehrerschein, EC Karte, Dienstausweis, Bahncard, etc.)
+alles weitere wird dann gerne am Stand erklaert.
+Siehe Veranstaltungs Seite:=0D
+http://www.macatcamp.de/index.php?option=3Dcom_content&view=3Darticle&id=3D118&Itemid=3D93 (http://bit.ly/F2TBh)
+
+
+Basis Informationen ATE:
+------------------------
+=20* Zeit: 19:30 - 21:30, Assurer Training Event - Bielefeld
+=20* Datum: Samstag 2. Mai.
+=20* Ort: Jugendgaestehaus und Bildungszentrum;
+=20=20=20=20=20=20=20=20Carl-Schmidt-Straße 11;
+=20=20=20=20=20=20=20=2033602 Bielefeld
+=20* Web: "Jugendgaestehaus und Bildungszentrum;"=0D
+=20=20=20http://www.macatcamp.de/index.php?option=3Dcom_content&view=3Darticle&id=3D74&Itemid=3D75 (http://bit.ly/7Qjsl)
+
+=20* Web "Mac At Camp On Tour - Bielefeld"=0D
+=20=20=20http://www.macatcamp.de/index.php?option=3Dcom_content&view=3Darticle&id=3D119&Itemid=3D73 (http://bit.ly/ReyVW)
+
+
+Agenda
+------
+19:30 - 20:30, jeder Vortrag ca. 5 min.
+- CAcert und das Audit
+- Die Unterschiede zwischem "altem" und "neuem" CAP Formular
+- Was verbirgt sich hinter der CCA?
+- Koennen "alte" CAP Formulare akzeptiert werden?
+- The Purpose of an Assurance
+- Fremde Ausweise (Update)
+- Wie sind Namens-Umschreibungen zu behandeln?
+- Namen und die Praxis
+- Unterschriften
+- Wichtige Dokumente
+- Dispute handling, Arbitration
+- Organisations Assurances Ueberblick
+20:30 - 21:30
+- Diskussion
+- Audited Assurances
+
+
+Speakers (u.a.)
+---------------
+Mario Lipinski, Dirk Astrath, Ulrich Schroeter
+
+
+Weitere Infos
+-------------
+Auf den CAcert Wiki Seiten
+=20ATE-Bielefeld http://wiki.cacert.org/wiki/Events/20090503ATE-Bielefeld
+=20ATE's Uebersicht http://wiki.cacert.org/wiki/Events/AssurerTrainingEvents
+
+Im CAcert Blog=0A
+=20http://blog.cacert.org/2009/04/382.html
+Auf der mac at camp Veranstaltungsseite=0A
+=20http://www.macatcamp.de/index.php?option=3Dcom_content&view=3Darticle&id=3D119&Itemid=3D73 (http://bit.ly/ReyVW)
+
+
+Rueckmeldungen
+--------------
+Fuer die ungefaehre Eventplanung benoetigen wir von den Teilnehmern eine Rueckmeldung.
+Unverbindliche Anmeldung:=0A
+mailto:events@cacert.org?subject=3DATE-attend-BI&body=3DI%20will%20attend:%20Bielefeld
+
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+
+Allgemeiner Kontakt fuer Veranstaltungen: events@cacert.org
diff --git a/scripts/ate-bi-mail.php b/scripts/ate-bi-mail.php
new file mode 100644
index 0000000..9d3ba11
--- /dev/null
+++ b/scripts/ate-bi-mail.php
@@ -0,0 +1,78 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-bi-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 = 200;
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+ $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+
+
+
+ $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`='cacert@astrath.de'";
+ $res = mysql_query($query);
+ $xrows = mysql_num_rows($res);
+ while($row = mysql_fetch_assoc($res))
+ {
+ // echo $row['pts']."..".$row['email']."...\n";
+ // uncomment next line to send mails ...
+ sendmail($row['email'], "[CAcert.org] ATE-Bielefeld 2.5.2009", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-Bielefeld 2.5.2009", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ // 1x mailing report to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-Bielefeld Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+?>
diff --git a/scripts/ate-d-email.txt b/scripts/ate-d-email.txt
new file mode 100644
index 0000000..fbfe60f
--- /dev/null
+++ b/scripts/ate-d-email.txt
@@ -0,0 +1,127 @@
+CAcert Assurer Training Event Düsseldorf
+::::::::::::::::::::::::::::::::::::::::
+
+(Dutch and English version below)
+
+Es hat sich viel getan im letzten Jahr. Eine ganze Reihe von bisher
+eher "mündlich überlieferten" 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:
+
+- Wovor schützt die CCA jedes CAcert-Community-Mitglied und somit auch dich?
+- Kannst du die 5 Statements der "Purpose of Assurance" aufzählen?
+- Kannst du auf Anhieb 10 Sicherheitsmerkmale des deutschen/
+ niederländischen Personalausweises/IDcard aufzählen?
+
+Antworten auf diese und weitere Fragen erhälst du bei den Assurer Training Events (ATE's).
+
+Die kommenden Veranstaltungen finden statt:
+
+- Hamburg, Donnerstag 7. Mai [http://wiki.cacert.org/wiki/Events/20090507ATE-Hamburg]
+- Düsseldorf, Dienstag 12. Mai [http://wiki.cacert.org/wiki/Events/20090514ATE-Duesseldorf]
+- München, Samstag 16. Mai [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining]
+- Stuttgart, Dienstag 26. Mai [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart]
+
+Die Termine für Frankfurt/M. und Berlin stehen noch nicht fest.
+Die Teilnahme der Veranstaltungen ist kostenlos, Spenden werden aber gerne gesehen.
+
+
+Düsseldorf:
+-----------
+Das ATE-Düsseldorf [http://wiki.cacert.org/wiki/Events/20090514ATE-Duesseldorf]
+findet statt am:
+- Dienstag, 12. Mai in der Zeit von 20:00 bis 22:00
+- im Gasthof Burchartz, Meerbusch [http://www.gasthof-burchartz.de/]
+
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Anmeldung und Kontakt: events@cacert.org
+
+
+
+
+
+CAcert Assurer Training Event Düsseldorf
+::::::::::::::::::::::::::::::::::::::::
+
+Er is het laatste jaar veel gebeurt. Een hele reeks van tot nu toe
+veelal "mondeling overgedragen" regels, zijn in Policies gegoten.
+Nieuwe Procedures (b.v. de Assurer Challenge) en verplichtingen
+(b.v. in de CAcert Community Agreement) werden vastgesteld. De Assurer
+Training Events trachten de volledige informatie "onder het volk"
+te berengen.
+
+- Waartegen beschermt de CCA ider CAcert-Community-Lid en dus ook jou?
+- Kan je de 5 Statements van de "Purpose of Assurance" opnoemen?
+- Kan je onmiddelijk 10 veiligheidskenmerken van het duitse/
+ nederlandse paspoort/identiteits kaart opnoemen?
+
+Antwoorden op deze en verdere vragen krijg je bij de Assurer Taraining Events (ATE's)
+
+De komende evenementen vinden plaats:
+
+- Hamburg, Donderdag 7. Mei [http://wiki.cacert.org/wiki/Events/20090507ATE-Hamburg]
+- Düsseldorf, Dinsdag 12. Mei [http://wiki.cacert.org/wiki/Events/20090514ATE-Duesseldorf]
+- München, Zaterdag 16. Mei [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining]
+- Stuttgart, Dindtag 26. Mai [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart]
+
+De data voor Frankfurt/M. en Berlijn liggen nog niet vast.
+De deelname aan de evenementen is gratis, bijdragen worden echter gaarne tegemoedgezien.
+
+Düsseldorf:
+-----------
+De ATE-Düsseldorf [http://wiki.cacert.org/wiki/Events/20090514ATE-Duesseldorf]
+vindt plaats op:
+- Dinstag, 12. Mei van 20:00 tot 22:00 uur.
+- in Gasthof Burchartz, Meerbusch [http://www.gasthof-burchartz.de/]
+
+
+Het evenementen-team veheugd zich al op jullie deelname.
+
+Aanmelding en contact: events@cacert.org
+
+
+
+
+
+CAcert Assurer Training Event Düsseldorf
+::::::::::::::::::::::::::::::::::::::::
+
+Much has happened during the past year. A list of up till now
+mostly "orally transmitted" rules have been cast in policies.
+New procedures (e.g. the Assurer Challenge) and obligations
+(e.g. in the CAcert Community Agreement) have been decided. The
+Assurer Training Events try to bring all this informations to "the
+people":
+
+- To what, does the CCA protect every CAcert-Community-Member and as such also you?
+- Can you recount the 5 statements of the "Purpose of Assurance"?
+- Can you at least recount 10 security marks of the German/Dutch
+ passport/Identity card?
+
+Answers to these and following questions are given at the Assurer Training Events (ATE's).
+
+The following events are planned:
+
+- Hamburg, Thursday. May 7 [http://wiki.cacert.org/wiki/Events/20090507ATE-Hamburg]
+- Düsseldorf, Tuesday. May 12 [http://wiki.cacert.org/wiki/Events/20090514ATE-Duesseldorf]
+- München, Saturday. May 16 [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining]
+- Stuttgart, Tuesday. May 26 [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart]
+
+The dates for Frankfurt/M. and Berlin have not yet been fixed.
+Participation in the events is free, Contributions are however appreciated.
+
+Düsseldorf:
+-----------
+The ATE-Düsseldorf [http://wiki.cacert.org/wiki/Events/20090514ATE-Duesseldorf]
+takes place on:
+- Tuesday,Mai 12 from 20:00 till 22:00
+- at Gasthof Burchartz, Meerbusch [http://www.gasthof-burchartz.de/]
+
+
+The Event-Team is already excited about your participation.
+
+Registration and contact: events@cacert.org
diff --git a/scripts/ate-d-mail.php.txt b/scripts/ate-d-mail.php.txt
new file mode 100644
index 0000000..5a9bca1
--- /dev/null
+++ b/scripts/ate-d-mail.php.txt
@@ -0,0 +1,85 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-d-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+
+ $locid = 520340; // Duesseldorf
+ $city = "Duesseldorf 12.5.2009";
+
+
+ $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))
+ {
+ echo $row['pts']."..".$row['email']."...\n";
+ // uncomment next line to send mails ...
+ sendmail($row['email'], "[CAcert.org] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-$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] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+?>
diff --git a/scripts/ate-de09-email.txt b/scripts/ate-de09-email.txt
new file mode 100644
index 0000000..4f2f1bf
--- /dev/null
+++ b/scripts/ate-de09-email.txt
@@ -0,0 +1,117 @@
+CAcert auf der CBLOS in Flensburg am 12. + 13. Juni 2009
+::::::::::::::::::::::::::::::::::::::::::::::::::::::::
+
+CAcert ist mit einem Stand und 2 Vortraegen auf der CBLOS vertreten.
+
+Samstag, 13. Juni, 10:45 - 11:30 Workshop: CAcert - Was ist das?
+Samstag, 13. Juni, 14:00 - 16:00 Assurer Training Event - Flensburg
+
+Workshop: 10:45 - 11:30
+:::::::::::::::::::::::
+Neben einer Vorstellung 'Was ist CAcert' wird es auch Informationen
+zu E-Mail Signaturen, E-Mail Verschluesselung und Server SSL Zertifikate
+geben.
+Hierzu sind alle Interessierten eingeladen.
+
+Assurer Training Event: 14:00 - 16:00
+:::::::::::::::::::::::::::::::::::::
+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:
+
+- Wovor schuetzt die CCA jedes CAcert-Community-Mitglied und somit auch dich?
+- Kannst du die 5 Statements der "Purpose of Assurance" aufzaehlen?
+- Kannst du auf Anhieb 10 Sicherheitsmerkmale des deutschen/daenischen
+ Personalausweises aufzaehlen?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den Assurer Training Events (ATEs).
+
+Die kommenden Veranstaltungen finden statt:
+
+- Flensburg, Samstag 13. Mai
+ [http://wiki.cacert.org/wiki/Events/20090613ATE-Flensburg]
+
+Die Teilnahme an den Veranstaltungen ist kostenlos.
+Anmeldungsregistrierung muss aber beim Veranstalter erfolgen.
+ [http://www.cblos.de/anmeldung.html]
+
+
+Flensburg:
+----------
+
+Das ATE Flensburg [http://wiki.cacert.org/wiki/Events/20090613ATE-Flensburg] findet statt am
+
+ Samstag, den 13. Juni in der Zeit von 14:00 Uhr bis ca. 16:00 Uhr
+ Wirtschaftsfoerderungs- und Regionalentwicklungsgesellschaft mbH
+ Lise-Meitner-Strasse 2; D-24941 Flensburg
+ [http://www.cblos.de/anfahrt.html]
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Unverbindliche Anmeldung fuer das ATE: Rueckantwort mit 'Ich moechte teilnehmen'
+Kontakt: events@cacert.org
+
+Zusaetzlich muss eine Registrierung beim Veranstalter erfolgen.
+Sowohl fuer den Workshop, alsauch fuer das Assurer Training Event:
+ [http://www.cblos.de/anmeldung.html]
+
+
+
+CAcert at CBLOS Flensburg at June, 12th and 13th 2009
+:::::::::::::::::::::::::::::::::::::::::::::::::::::
+
+CAcert will be present at CBLOS with a booth and 2 presentations.
+
+Saturday, June 13th, 10:45 - 11:30 Workshop: CAcert - What is that?
+Saturday, June 13th, 14:00 - 16:00 Assurer Training Event - Flensburg
+
+Workshop:
+:::::::::
+You'll get an introduction about CAcert and his products and informations
+about Email signatures, Email encryption and Server SSL certificatesZertifikate.
+All interested peoples are invited.
+
+Assurer Training Event:
+:::::::::::::::::::::::
+
+Much has happened during the past year. A list of up till now
+mostly "orally transmitted" rules have been cast in policies.
+New procedures (e.g. the Assurer Challenge) and obligations
+(e.g. in the CAcert Community Agreement) have been decided. The
+Assurer Training Events try to bring all this informations to "the
+people":
+
+- To what, does the CCA protect every CAcert-Community-Member and as such also you?
+- Can you recount the 5 statements of the "Purpose of Assurance"?
+- Can you at least recount 10 security marks of the German/Dansk
+ passport/Identity card?
+
+Answers to these and following questions are given at the Assurer Training Events (ATE's).
+
+The following events are planned:
+
+- Flensburg, Saturday, June 13th
+ [http://wiki.cacert.org/wiki/Events/20090613ATE-Flensburg]
+
+Participation in the events is free.
+An additional events registration is needed.
+ [http://www.cblos.de/anmeldung.html]
+
+
+Flensburg:
+----------
+
+The ATE-Flensburg [http://wiki.cacert.org/wiki/Events/20090613ATE-Flensburg] takes place on:
+
+ Saturday, June 13th between 14:00 and 16:00
+ Wirtschaftsfoerderungs- und Regionalentwicklungsgesellschaft mbH
+ Lise-Meitner-Strasse 2; D-24941 Flensburg
+ [http://www.cblos.de/anfahrt.html]
+
+The Event-Team is already excited about your participation.
+
+Registration by a reply with 'I will attend Workshop CBLOS'
+or 'I will attend ATE CBLOS'
+Kontakt: events@cacert.org
+
+An additional events registration is needed for both
+the Workshop and the Assurer Training Event:
+ [http://www.cblos.de/anmeldung.html]
diff --git a/scripts/ate-de09-mail.php.txt b/scripts/ate-de09-mail.php.txt
new file mode 100644
index 0000000..b8d58cc
--- /dev/null
+++ b/scripts/ate-de09-mail.php.txt
@@ -0,0 +1,88 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-de09-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+
+ $locid = 599389; // Flensburg
+
+ $city = "Flensburg, 12.+13.6.2009";
+
+ $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] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-$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] ATE-$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] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+?>
diff --git a/scripts/ate-de11-email.txt b/scripts/ate-de11-email.txt
new file mode 100644
index 0000000..21ac13a
--- /dev/null
+++ b/scripts/ate-de11-email.txt
@@ -0,0 +1,37 @@
+CAcert Assurer Training Event Berlin-Brandenburg
+::::::::::::::::::::::::::::::::::::::::::::::::
+
+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 nun versuchen, die ganzen Informationen "unter's Volk" zu bringen:
+
+- Wovor schuetzt die CCA jedes CAcert-Community-Mitglied und somit auch
+ dich?
+- Kannst du die 5 Statements der "Purpose of Assurance" aufzaehlen?
+- Kannst du auf Anhieb 10 Sicherheitsmerkmale des deutschen Personal-
+ ausweises aufzaehlen?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den Assurer
+Training Events (ATEs).
+
+Der Termin fuer Berlin steht nun fest.
+
+Berlin-Wilmerdorf,
+Donnerstag 09. Juli 2009 in der Zeit von 19:00 bis 21:00 Uhr
+Restaurant Prometheus
+Schlangenbader Strasse
+12345 Berlin-Wilmerdorf
+[http://wiki.cacert.org/wiki/Events/20090709ATE-Berlin]
+
+Anmeldungen erfolgen bitte ausschliesslich ueber folgende Seite:
+[http://cacert-berlin.mixxt.de/networks/events/show_event.7105]
+
+Die Teilnahme an der Veranstaltung ist kostenlos, Spenden werden aber
+gerne gesehen.
+
+Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im Wiki und
+bei mixxt.de, siehe die Links oben.
+
+Kontakt: events@cacert.org
diff --git a/scripts/ate-de11-mail.php.txt b/scripts/ate-de11-mail.php.txt
new file mode 100644
index 0000000..7021e5f
--- /dev/null
+++ b/scripts/ate-de11-mail.php.txt
@@ -0,0 +1,92 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-de11-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+
+ $locid = 228950; // Berlin
+
+
+ $city = "Berlin 9. Juli 2009";
+
+ $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] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-$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] ATE-$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] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ echo "invitation sent to $xrows recipients.";
+?>
diff --git a/scripts/ate-f-email.txt b/scripts/ate-f-email.txt
new file mode 100644
index 0000000..53ac19e
--- /dev/null
+++ b/scripts/ate-f-email.txt
@@ -0,0 +1,40 @@
+CAcert Assurer Training Event Frankfurt
+:::::::::::::::::::::::::::::::::::::::
+
+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:
+
+- Wovor schuetzt die CCA jedes CAcert-Community-Mitglied und somit auch dich?
+- Kannst du die 5 Statements der "Purpose of Assurance" aufzaehlen?
+- Kannst du auf Anhieb 10 Sicherheitsmerkmale des deutschen
+ Personalausweises aufzaehlen?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den Assurer Training Events (ATEs).
+
+Die kommenden Veranstaltungen finden statt:
+
+- Muenchen, Samstag 16. Mai
+ [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining]
+- Stuttgart, Dienstag 26. Mai
+ [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart]
+- Frankfurt, Donnerstag 28. Mai
+ [http://wiki.cacert.org/wiki/Events/20090528ATE-Frankfurt]
+
+Der Termin fuer Berlin steht noch nicht fest.
+Die Teilnahme an den Veranstaltungen ist kostenlos, Spenden werden aber gerne gesehen.
+
+
+Frankfurt:
+---------
+
+Das ATE Frankfurt [http://wiki.cacert.org/wiki/Events/20090528ATE-Frankfurt] findet statt am
+
+ Donnerstag, den 28.Mai in der Zeit von 20:00 Uhr bis ca. 22:00 Uhr
+ im "Tower Cafe" am Alten Flugplatz in Frankfurt-Bonames.
+ [http://www.tower-cafe.de/]
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im Wiki, siehe der Link oben.
+
+Unverbindliche Anmeldung und Registrierung: Rueckantwort mit 'Ich moechte teilnehmen'
+Kontakt: events@cacert.org
diff --git a/scripts/ate-f-mail.php.txt b/scripts/ate-f-mail.php.txt
new file mode 100644
index 0000000..7a2dd64
--- /dev/null
+++ b/scripts/ate-f-mail.php.txt
@@ -0,0 +1,83 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-f-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+
+ $locid = 606058; // Frankfurt
+ $city = "Frankfurt/M., 28.5.2009";
+
+ $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] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-$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] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+?>
diff --git a/scripts/ate-goteborg-s16-email.txt b/scripts/ate-goteborg-s16-email.txt
new file mode 100644
index 0000000..e1153f7
--- /dev/null
+++ b/scripts/ate-goteborg-s16-email.txt
@@ -0,0 +1,58 @@
+CAcert Assurer Training Event Goteborg
+::::::::::::::::::::::::::::::::::::::::::::::::::
+
+Much has happened during the past year. A list of up till now mostly
+"orally transmitted" rules have been cast in policies. New procedures
+(e.g. the Assurer Challenge) and obligations (e.g. in the CAcert
+Community Agreement) have been decided. The Assurer Training Events
+try to bring all this informations to "the people":
+
+- To what, does the CCA protect every CAcert-Community-Member and as
+ such also you?
+- Can you recount the 5 statements of the "Purpose of Assurance"?
+- Can you at least recount 10 security marks of the Swedish passport
+ /Identity card?
+
+Answers to these and following questions are given at the Assurer
+Training Events (ATE’s).
+Participation in the events is free, Contributions are however
+appreciated.
+
+
+The ATE-Goteborg takes place on:
+- Wednesday, Dec 16th from 20:00 till 22:00
+- at
+- [http://www.gothiatowers.com/]
+- Massans gata 24
+- 402 26 Goteborg, Sweden
+
+The Event-Team is already excited about your participation.
+
+Registration ATE-Goteborg
+[http://wiki.cacert.org/Events/20091216ATE-Goteborg]
+
+Remark from Leif-Joran:
+I have a backup room at my department just a couple of
+hundred meters from Gothia Towers. L307, Lennart Torstenssons-gatan 8
+
+Remarks from Ted:
+As I understand it, it will probably be harder to find a room inside the
+hotel than to find the hotel itself, it boasts to be the biggest hotel
+in Skandinavia...
+
+My mobile phone number is +49 151 52634367. I'm there on a probably
+quite busy business trip, so it may be that I have to cancel the whole
+thing on short notice.
+I propose that you try to contact me by phone before leaving home, so I
+can confirm that I will be there.
+
+Also I don't have much time to prepare, so please don't expect something
+like a professional presentation from me. I hope it will be a nice
+little meeting, we will assure each others, you'll ask questions you
+always wanted to ask about CACert and I'll tell you about all the things
+that have recently changed at CAcert, at least the important ones. And
+maybe I'll try to trick you into making a false assurance, just to show
+you where problems are expected. :-)
+
+
+contact: events@cacert.org
diff --git a/scripts/ate-goteborg-s16-mail.php.txt b/scripts/ate-goteborg-s16-mail.php.txt
new file mode 100644
index 0000000..6e4a7c0
--- /dev/null
+++ b/scripts/ate-goteborg-s16-mail.php.txt
@@ -0,0 +1,110 @@
+#!/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("ate-goteborg-s16-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 = 50;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+// $locid = 715191; // Hamburg
+
+// LISA2009 Baltimore, 1.11.2009
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $city = "Baltimore, MD - Nov. 3rd 2009";
+
+// OpenSourceTreffen-Muenchen, 20.11.2009
+// $locid = 1260319; // Muenchen
+// $city = "Muenchen - 20. Nov 2009";
+
+// BLIT2009, Brandenburger Linux-Infotag, 21.11.2009
+// $locid = 1486658; // Potsdam
+// $eventname = "Brandenburger Linux-Infotag (BLIT2009)";
+// $city = "Potsdam - 21. Nov 2009";
+
+// ATE-Goteborg, 16.12.2009
+ $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+ $eventname = "ATE-Goteborg";
+ $city = "Goteborg - Dec 16th 2009";
+
+
+ $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.";
+?>
diff --git a/scripts/ate-hh-email.txt b/scripts/ate-hh-email.txt
new file mode 100644
index 0000000..f294ad6
--- /dev/null
+++ b/scripts/ate-hh-email.txt
@@ -0,0 +1,40 @@
+CAcert Assurer Training Event im Rahmen der GUUG Hamburg
+---------------------------------------------------------
+
+Es hat sich viel getan im letzten Jahr. Eine ganze Reihe von bisher
+eher "mündlich überlieferten" 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:
+
+- Wovor schützt die CCA jedes CAcert-Community-Mitglied und somit auch dich?
+- Kannst du die 5 Statements der "Purpose of Assurance" aufzählen?
+- Kannst du auf Anhieb 10 Sicherheitsmerkmale des deutschen
+ Personalausweises aufzählen?
+
+Antworten auf diese und weitere Fragen erhälst du bei den Assurer Training Events (ATE's).
+
+Die kommenden Veranstaltungen finden statt:
+
+- Hamburg, Donnerstag 7. Mai [http://wiki.cacert.org/wiki/Events/20090507ATE-Hamburg]
+- Düsseldorf, Dienstag 12. Mai [http://wiki.cacert.org/wiki/Events/20090514ATE-Duesseldorf]
+- München, Samstag 16. Mai [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining]
+- Stuttgart, Dienstag 26. Mai [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart]
+
+Die Termine für Frankfurt/M. und Berlin stehen noch nicht fest.
+Die Teilnahme der Veranstaltungen ist kostenlos, Spenden werden aber gerne gesehen.
+
+
+Hamburg:
+--------
+Im Rahmen der regelmässigen Veranstaltungsreihe der GUUG
+(German Unix User Group) Hamburg [http://www.guug.de/lokal/hamburg/]
+findet das ATE-Hamburg statt am:
+- Donnerstag, 7. Mai in der Zeit von 20:00 bis 22:00
+- in der Lehmanns Fachbuchhandlung, Kurze Mühren 6, Hamburg
+
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Anmeldung und Kontakt: events@cacert.org
diff --git a/scripts/ate-hh-mail.php.txt b/scripts/ate-hh-mail.php.txt
new file mode 100644
index 0000000..1935df4
--- /dev/null
+++ b/scripts/ate-hh-mail.php.txt
@@ -0,0 +1,164 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-hh-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+
+ $locid = 715191; // Hamburg
+ $city = "Hamburg 7.5.2009";
+
+
+ $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))
+ {
+ echo $row['pts']."..".$row['email']."...\n";
+ // uncomment next line to send mails ...
+ sendEmail($row['email'], "[CAcert.org] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendEmail("events@cacert.org", "[CAcert.org] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ // 1x mailing report to events.cacert.org
+ sendEmail("events@cacert.org", "[CAcert.org] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+
+
+ function sendEmail($to, $subject, $message, $from, $replyto = "", $toname = "", $fromname = "", $errorsto = "returns@cacert.org", $extra="")
+ {
+ $lines = explode('\n', $message);
+ $message = "";
+ foreach($lines as $line)
+ {
+ $line = trim($line);
+ if($line == ".")
+ $message .= " .\n";
+ else
+ $message .= $line."\n";
+ }
+
+ if($fromname == "")
+ $fromname = $from;
+
+ $bits = explode(",", $from);
+ $from = addslashes($bits['0']);
+ $fromname = addslashes($fromname);
+
+ $smtp = fsockopen("localhost", 25);
+ if(!$smtp)
+ {
+ echo("Could not connect to mailserver at localhost:25\n");
+ return;
+ }
+ $InputBuffer = fgets($smtp, 1024);
+ fputs($smtp, "HELO hlin.cacert.org\r\n");
+ $InputBuffer = fgets($smtp, 1024);
+ fputs($smtp, "MAIL FROM: <returns@cacert.org>\r\n");
+ $InputBuffer = fgets($smtp, 1024);
+ $bits = explode(",", $to);
+ foreach($bits as $user)
+ fputs($smtp, "RCPT TO: <".trim($user).">\r\n");
+ $InputBuffer = fgets($smtp, 1024);
+ fputs($smtp, "DATA\r\n");
+ $InputBuffer = fgets($smtp, 1024);
+ fputs($smtp, "X-Mailer: CAcert.org Website\r\n");
+ fputs($smtp, "X-OriginatingIP: ".$_SERVER["REMOTE_ADDR"]."\r\n");
+ fputs($smtp, "Sender: $errorsto\r\n");
+ fputs($smtp, "Errors-To: $errorsto\r\n");
+ if($replyto != "")
+ fputs($smtp, "Reply-To: $replyto\r\n");
+ else
+ fputs($smtp, "Reply-To: $from\r\n");
+ fputs($smtp, "From: $from\r\n");
+ fputs($smtp, "To: $to\r\n");
+ if(preg_match("/[^a-zA-Z0-9 .-\[\]!_@]/",$subject))
+ {
+ fputs($smtp, "Subject: =?utf-8?B?".base64_encode( $subject)."?=\r\n");
+// fputs($smtp, "Subject: =?utf-8?B?".base64_encode(recode("html..utf-8", $subject))."?=\r\n");
+ }
+ else
+ {
+ fputs($smtp, "Subject: $subject\r\n");
+ }
+ fputs($smtp, "Mime-Version: 1.0\r\n");
+ if($extra == "")
+ {
+ fputs($smtp, "Content-Type: text/plain; charset=\"utf-8\"\r\n");
+// fputs($smtp, "Content-Transfer-Encoding: 8bit\r\n");
+ } else {
+ fputs($smtp, "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n");
+// fputs($smtp, "Content-Transfer-Encoding: quoted-printable\r\n");
+ fputs($smtp, "Content-Disposition: inline\r\n");
+ }
+ fputs($smtp, "Content-Transfer-Encoding: BASE64\r\n");
+ fputs($smtp, "\r\n");
+ fputs($smtp, chunk_split(base64_encode($message))."\r\n.\r\n");
+// fputs($smtp, chunk_split(base64_encode(recode("html..utf-8", $message)))."\r\n.\r\n");
+// fputs($smtp, recode("html..utf-8", $message)."\r\n.\r\n");
+// fputs($smtp, $message."\r\n.\r\n");
+ fputs($smtp, "QUIT\n");
+ $InputBuffer = fgets($smtp, 1024);
+ fclose($smtp);
+ }
+
+?>
diff --git a/scripts/ate-l-email.txt b/scripts/ate-l-email.txt
new file mode 100644
index 0000000..458deb1
--- /dev/null
+++ b/scripts/ate-l-email.txt
@@ -0,0 +1,40 @@
+CAcert Assurer Training Event London
+::::::::::::::::::::::::::::::::::::
+
+Much has happened during the past year. A list of up till now
+mostly "orally transmitted" rules have been cast in policies.
+New procedures (e.g. the Assurer Challenge) and obligations
+(e.g. in the CAcert Community Agreement) have been decided. The
+Assurer Training Events try to bring all this informations to "the
+people":
+
+- To what, does the CCA protect every CAcert-Community-Member and as such also you?
+- Can you recount the 5 statements of the "Purpose of Assurance"?
+- Can you at least recount 10 security marks of your own passport/Identity card?
+
+Answers to these and following questions are given at the Assurer Training Events (ATE's).
+
+The following events are planned:
+
+- London - Thursday, May 12 [http://wiki.cacert.org/wiki/Events/20090512London]
+- Duesseldorf - Tuesday, May 12 [http://wiki.cacert.org/wiki/Events/20090514ATE-Duesseldorf]
+- Muenchen - Saturday, May 16 [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining]
+- Stuttgart - Tuesday, May 26 [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart]
+- Frankfurt - Thursday, May 28 [http://wiki.cacert.org/wiki/Events/20090528ATE-Frankfurt]
+
+The date for Berlin have not yet been fixed.
+Participation in the events is free, Contributions are however appreciated.
+
+London:
+-----------
+The ATE-London [http://wiki.cacert.org/wiki/Events/20090512London]
+takes place on:
+- Tuesday, May 12 from 5:30pm till 7:30pm
+- at the Red Lion's [http://www.fancyapint.com/pubs/pub2823.html]
+ Bank
+ 8 Lombard Court, EC3V 9BJ
+
+The Event-Team is already excited about your participation.
+
+Registration: please reply 'I will attend: London'
+Contact: events@cacert.org
diff --git a/scripts/ate-l-mail.php.txt b/scripts/ate-l-mail.php.txt
new file mode 100644
index 0000000..3c2637d
--- /dev/null
+++ b/scripts/ate-l-mail.php.txt
@@ -0,0 +1,84 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-l-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 = 100;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+
+ $locid = 1102495; // London
+ $city = "London, May 12th, 2009";
+
+
+ $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] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-$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] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+?>
diff --git a/scripts/ate-m-email.txt b/scripts/ate-m-email.txt
new file mode 100644
index 0000000..1805e55
--- /dev/null
+++ b/scripts/ate-m-email.txt
@@ -0,0 +1,36 @@
+CAcert Assurer Training Event Muenchen
+::::::::::::::::::::::::::::::::::::::
+
+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:
+
+- Wovor schuetzt die CCA jedes CAcert-Community-Mitglied und somit auch dich?
+- Kannst du die 5 Statements der "Purpose of Assurance" aufzaehlen?
+- Kannst du auf Anhieb 10 Sicherheitsmerkmale des deutschen
+ Personalausweises aufzaehlen?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den Assurer Training Events (ATE's).
+
+Die kommenden Veranstaltungen finden statt:
+
+- Muenchen, Samstag 16. Mai
+ [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining]
+- Stuttgart, Dienstag 26. Mai
+ [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart]
+- Frankfurt, Donnerstag 28. Mai
+ [http://wiki.cacert.org/wiki/Events/20090528ATE-Frankfurt]
+
+Der Termin fuer Berlin steht noch nicht fest.
+Die Teilnahme der Veranstaltungen ist kostenlos, Spenden werden aber gerne gesehen.
+
+
+Muenchen:
+---------
+Das ATE-Muenchen [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining] findet statt am:
+- Samstag, 16. Mai in der Zeit von 13:00 bis ca. 16:00
+- Leonrodstr. 68, bei der Fa. convey im Untergeschoss
+
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Unverbindliche Anmeldung und Registrierung: Rueckantwort mit 'Ich moechte teilnehmen'
+Kontakt: events@cacert.org
diff --git a/scripts/ate-m-mail.php.txt b/scripts/ate-m-mail.php.txt
new file mode 100644
index 0000000..fd6d9be
--- /dev/null
+++ b/scripts/ate-m-mail.php.txt
@@ -0,0 +1,83 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-m-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+
+ $locid = 1260319; // Muenchen
+ $city = "Muenchen, 16.5.2009";
+
+ $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] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-$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] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+?>
diff --git a/scripts/ate-nl01-email.txt b/scripts/ate-nl01-email.txt
new file mode 100644
index 0000000..2d2164d
--- /dev/null
+++ b/scripts/ate-nl01-email.txt
@@ -0,0 +1,88 @@
+CAcert Assurer Training Event Amsterdam and Eemnes
+::::::::::::::::::::::::::::::::::::::::::::::::::
+Er is het laatste jaar veel gebeurd. Een hele reeks van tot nu toe
+veelal "mondeling overgedragen" regels, zijn in Policies gegoten. Nieuwe
+Procedures (b.v. de Assurer Challenge) en verplichtingen (b.v. in de
+CAcert Community Agreement) werden vastgesteld. De Assurer Training
+Events trachten de volledige informatie "onder het volk" te brengen.
+
+- Waartegen beschermt de CCA ieder CAcert-Community-Lid en dus ook jou?
+- Kan je de 5 Statements van de "Purpose of Assurance" opnoemen?
+- Kan je onmiddellijk 10 veiligheidskenmerken van het Nederlandse
+ paspoort/identiteits kaart opnoemen?
+
+Antwoorden op deze en verdere vragen krijg je bij de Assurer Training
+Events (ATE’s)
+De deelname aan de evenementen is gratis, bijdragen worden echter gaarne
+tegemoet gezien.
+
+De ATE-Amsterdam vindt plaats op:
+- Maandag, 15. Juni van 20:00 tot 22:00 uur.
+- in SCHUBERG PHILIS
+Star Parc
+Boeing Avenue 271
+1119 PD Schiphol-Rijk
+
+De ATE-Eemnes vindt plaats op:
+- Zaterdag, 20. Juni van 10:30 tot 12:30 uur, met uitloop tot 15:30
+ voor gewone waarmerkingen
+- in de Hilt
+Hasselaarlaan 1c
+3755 AV EemnesHet evenementen-team verheugt zich al op jullie deelname.
+
+
+CAcert Assurer Training Event Amsterdam
+Aanmelding ATE-Amsterdam
+[http://wiki.cacert.org/wiki/Events/20090615ATE-Amsterdam]
+
+CAcert Assurer Training Event Eemnes
+Aanmelding ATE-Eemnes
+[http://wiki.cacert.org/wiki/Events/20090620ATE-Eemnes]
+
+contact: events@cacert.org
+
+
+
+CAcert Assurer Training Event Amsterdam and Eemnes
+::::::::::::::::::::::::::::::::::::::::::::::::::
+Much has happened during the past year. A list of up till now mostly
+"orally transmitted" rules have been cast in policies. New procedures
+(e.g. the Assurer Challenge) and obligations (e.g. in the CAcert
+Community Agreement) have been decided. The Assurer Training Events
+try to bring all this informations to "the people":
+
+- To what, does the CCA protect every CAcert-Community-Member and as
+ such also you?
+- Can you recount the 5 statements of the "Purpose of Assurance"?
+- Can you at least recount 10 security marks of the Dutch passport
+ /Identity card?
+
+Answers to these and following questions are given at the Assurer
+Training Events (ATE’s).
+Participation in the events is free, Contributions are however
+appreciated.
+
+
+The ATE-Amsterdam takes place on:
+- Monday, June 15th from 20:00 till 22:00
+- at SCHUBERG PHILIS
+Star Parc
+Boeing Avenue 271
+1119 PD Schiphol-Rijk
+
+The ATE-Eemnes takes place on:
+- Saturday, June 20th from 10:30 till 12:30, followed by normal
+ assurances till 15:30
+- in de Hilt
+Hasselaarlaan 1c
+3755 AV Eemnes
+
+The Event-Team is already excited about your participation.
+
+Registration ATE-Amsterdam
+[http://wiki.cacert.org/wiki/Events/20090615ATE-Amsterdam]
+
+Registration ATE-Eemnes
+[http://wiki.cacert.org/wiki/Events/20090620ATE-Eemnes]
+
+contact: events@cacert.org
diff --git a/scripts/ate-nl01-mail.php.txt b/scripts/ate-nl01-mail.php.txt
new file mode 100644
index 0000000..a7c44a2
--- /dev/null
+++ b/scripts/ate-nl01-mail.php.txt
@@ -0,0 +1,89 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-nl01-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+
+ $locid = 61065; // Amsterdam, Eemnes
+
+ $city = "Amsterdam June 15th, Eemnes June 20th 2009";
+
+ $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] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-$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] ATE-$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] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+?>
diff --git a/scripts/ate-s-email.txt b/scripts/ate-s-email.txt
new file mode 100644
index 0000000..709c4b2
--- /dev/null
+++ b/scripts/ate-s-email.txt
@@ -0,0 +1,40 @@
+CAcert Assurer Training Event Stuttgart
+:::::::::::::::::::::::::::::::::::::::
+
+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:
+
+- Wovor schuetzt die CCA jedes CAcert-Community-Mitglied und somit auch dich?
+- Kannst du die 5 Statements der "Purpose of Assurance" aufzaehlen?
+- Kannst du auf Anhieb 10 Sicherheitsmerkmale des deutschen
+ Personalausweises aufzaehlen?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den Assurer Training Events (ATEs).
+
+Die kommenden Veranstaltungen finden statt:
+
+- Muenchen, Samstag 16. Mai
+ [http://wiki.cacert.org/wiki/Events/20090509muc_AssurerTraining]
+- Stuttgart, Dienstag 26. Mai
+ [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart]
+- Frankfurt, Donnerstag 28. Mai
+ [http://wiki.cacert.org/wiki/Events/20090528ATE-Frankfurt]
+
+Der Termin fuer Berlin steht noch nicht fest.
+Die Teilnahme an den Veranstaltungen ist kostenlos, Spenden werden aber gerne gesehen.
+
+
+Stuttgart:
+---------
+
+Das ATE Stuttgart [http://wiki.cacert.org/wiki/Events/20090526ATE-Stuttgart] findet statt am
+
+ Dienstag, den 26.Mai in der Zeit von 20:00 Uhr bis ca. 22:00 Uhr
+ in der "Troedlerburg" (Burgschenke) in Esslingen.
+
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im Wiki, siehe der Link oben.
+
+Unverbindliche Anmeldung und Registrierung: Rueckantwort mit 'Ich moechte teilnehmen'
+Kontakt: events@cacert.org
diff --git a/scripts/ate-s-mail.php.txt b/scripts/ate-s-mail.php.txt
new file mode 100644
index 0000000..736ee96
--- /dev/null
+++ b/scripts/ate-s-mail.php.txt
@@ -0,0 +1,83 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-s-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+
+ $locid = 1775784; // Stuttgart
+ $city = "Stuttgart/Esslingen, 26.5.2009";
+
+ $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] ATE-$city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] ATE-$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] ATE-$city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+?>
diff --git a/scripts/ate-sydney-au20-email.txt b/scripts/ate-sydney-au20-email.txt
new file mode 100644
index 0000000..6979191
--- /dev/null
+++ b/scripts/ate-sydney-au20-email.txt
@@ -0,0 +1,30 @@
+CAcert Assurer Training Event Sdney
+::::::::::::::::::::::::::::::::::::::::::::::::::
+
+Much has happened during the past 3 years. The old way of
+orally-transmitted procedures has now gone, and our rules have been cast
+into formal policies. New procedures (e.g. the Assurer Challenge) and
+obligations (e.g. in the CAcert Community Agreement) have been approved.
+The Assurer Training Events bring all this to you, the Community:
+
+- How does the CCA protect you and every CAcert Community Member?
+- Can you recall the 5 parts of the Assurance Statement?
+- Can you tick off the security features of the Australian passport?
+- What do you when something goes drastically wrong?
+
+Answers to these and many other questions are given at the Assurer
+Training Events (ATEs).
+
+ATE-Sydney takes place on:
+- Wednesday, March 24th from 6:00pm till 9:30pm
+- Sydney University School of IT, Building J12
+ Ground Floor Lecture Room, room 123.
+- directions: [http://www.cs.usyd.edu.au/about/directions.shtml]
+
+The Event-Team is looking forward to hearing from you. Participation in
+ATEs is free, contributions are however appreciated.
+
+Registration for ATE-Sydney: please reply 'I will attend: Sydney'
+contact: events@cacert.org
+
+web: [http://wiki.cacert.org/events/20100324Sydney]
diff --git a/scripts/ate-sydney-au20-mail.php.txt b/scripts/ate-sydney-au20-mail.php.txt
new file mode 100644
index 0000000..32ef1ab
--- /dev/null
+++ b/scripts/ate-sydney-au20-mail.php.txt
@@ -0,0 +1,130 @@
+#!/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("ate-sydney-au20-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 = 200;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+// $locid = 715191; // Hamburg
+
+// LISA2009 Baltimore, 1.11.2009
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $city = "Baltimore, MD - Nov. 3rd 2009";
+
+// OpenSourceTreffen-Muenchen, 20.11.2009
+// $locid = 1260319; // Muenchen
+// $city = "Muenchen - 20. Nov 2009";
+
+// BLIT2009, Brandenburger Linux-Infotag, 21.11.2009
+// $locid = 1486658; // Potsdam
+// $eventname = "Brandenburger Linux-Infotag (BLIT2009)";
+// $city = "Potsdam - 21. Nov 2009";
+
+// ATE-Goteborg, 16.12.2009
+// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+// $eventname = "ATE-Goteborg";
+// $city = "Goteborg - Dec 16th 2009";
+
+// Assurance Event Mission Hills CA, 15.01.2010
+// $locid = 2094781; // Mission Hills (Los Angeles), California, United States
+// $eventname = "Assurance Event";
+// $city = "Mission Hills CA - Jan 15th 2010";
+
+// Assurance Event OSD Copenhagen DK, 5.03.2010
+// $locid = 423655; // Copenhagen, Kobenhavn*, Denmark
+// $eventname = "Assurance Event OpenSource-Days 2010";
+// $city = "Copenhagen DK - March 5th/6th 2010";
+
+// SCALE 8x Los Angeles, CA, Feb 19-21 2010
+// $locid = 2093625; // Copenhagen, Kobenhavn*, Denmark
+// $eventname = "SCALE 8x 2010";
+// $city = "Los Angeles, CA - February 19-21 2010";
+
+// ATE Sydney, AU, Mar 24 2010
+ $locid = 2257312; // Sydney, New South Wales, Australia
+ $eventname = "ATE-Sydney";
+ $city = "March 24, 2010";
+
+
+ $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/ate-us02-email.txt b/scripts/ate-us02-email.txt
new file mode 100644
index 0000000..3f1b6b9
--- /dev/null
+++ b/scripts/ate-us02-email.txt
@@ -0,0 +1,27 @@
+Dear Member,
+There is going to be a CAcert Assurance Event next week in San Diego. It will be held at the USENIX'09 Technical Conference at the Town & Country Hotel. Here is an opportunity to get the points you still need for your CAcert account, and learn more about what CAcert has to offer.
+
+This will be more than just an Assurance event, it will also be a "Report to the Community", Assurance Training Event (ATE), and Assurance Event. It is also an opportunity for us to hear from you.
+
+Please email me gstark@cacert.org if you decide to attend and can help out. Not to worry, if you are not registered for the conference itself, the Birds of a Feather (BoF) is open to all. It will be an opportunity to meet others in the CAcert community. PGTP/GPG signing is being done.
+
+Please remember to bring your filled out forms, and two government issued photo ID's, and if you are doing PGTP/GPG signing, have those printed out too.
+
+Regards,
+Greg Stark
+
+
+USENIX'09 Annual Technical Conference
+CAcert Birds of a Feather(BoF) Session
+Thursday, June 18th
+Royal Palm Salon #1-2, 8-10PM
+http://wiki.cacert.org/wiki/Events/090614_Usenix09-San-Diego_user
+http://www.usenix.org/events/usenix09/bofs.html
+
+
+Town & Country Hotel
+500 Hotel Circle North
+San Diego, CA 92108
+Telephone (toll free): 800.77.ATLAS
+Telephone (local): 619.291.7131
+http://maps.google.com/maps?q=3D500+Hotel+Circle+North,+San+Diego,+CA+9210
diff --git a/scripts/ate-us02-mail.php.txt b/scripts/ate-us02-mail.php.txt
new file mode 100644
index 0000000..42a38a1
--- /dev/null
+++ b/scripts/ate-us02-mail.php.txt
@@ -0,0 +1,97 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("ate-us02-email.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+// Duesseldorf 51.2167N 6.7667E
+// VELBERT 51.333 7.050; FRANKFURT AM MAIN 50.117 8.683
+
+// $locid = intval($_REQUEST['location']);
+// $maxdist = intval($_REQUEST['maxdist']);
+ // $maxdist = 200; // km
+ $maxdist = 450; // 300 miles * 1,5 => km
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+
+// $locid = 2096325; // San Diego
+
+
+ $locid = 2096325; // San Diego
+ $city = "San Diego CA, May, 14th-19th 2009";
+
+
+ $query = "select * from `locations` where `id`='$locid'";
+ $loc = mysql_fetch_assoc(mysql_query($query));
+
+ // `users`.`assurer`=0 => did NOT passed assurer challenge
+ $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` ";
+ // and sum(points) < 101
+
+ 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))
+ {
+ // echo $row['pts']."..".$row['email']."...\n";
+ // uncomment next line to send mails ...
+ sendmail($row['email'], "[CAcert.org] $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] $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] $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] $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ echo "invitation sent to $xrows recipients.";
+?>
diff --git a/scripts/blit-de15-email.txt b/scripts/blit-de15-email.txt
new file mode 100644
index 0000000..28044ee
--- /dev/null
+++ b/scripts/blit-de15-email.txt
@@ -0,0 +1,23 @@
+Hallo CAcerties,
+
+kommenden Samstag, 21. November sind wir mit einem Stand und einem Vortrag auf
+dem Brandenburger Linux-Infotag vertreten.
+Bisher haben sich nur zwei CAcert-Repraesentanten auf unserer Wiki Seite
+als Helfer eingetragen.
+Sollte jemand aus Potsdam, Berlin oder sonstwo (also du) Lust und Zeit haben,
+dabei zu sein, tragt euch bitte ein!
+
+Warum wir das nicht alleine schaffen?
+Warum wir gerade dich brauchen?
+Deine Hilfe?
+Wenn wir dort Lehrer, Professoren, wissenschaftliche Mitarbeiter,
+Klassensprecher, Fachschaftler, irgend jemand von CAcert ueberzeugen
+koennen, dann wollen wir 100 Punkte vergeben koennen, dass neue
+Keimzellen entstehen. Und dazu muessen wir mindestens zu dritt sein.
+
+Du bist Assurer?
+Prima! Dann freuen wir uns darauf, dich am Samstag zu sehen!
+
+Wiki Organisationsseite: [http://wiki.cacert.org/events/blit2009]
+
+Kontakt: events@cacert.org
diff --git a/scripts/blit-de15-mail.php.txt b/scripts/blit-de15-mail.php.txt
new file mode 100644
index 0000000..87666ea
--- /dev/null
+++ b/scripts/blit-de15-mail.php.txt
@@ -0,0 +1,105 @@
+#!/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("blit-de15-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 = 50;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+// $locid = 715191; // Hamburg
+
+// LISA2009 Baltimore, 1.11.2009
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $city = "Baltimore, MD - Nov. 3rd 2009";
+
+// OpenSourceTreffen-Muenchen, 20.11.2009
+// $locid = 1260319; // Muenchen
+// $city = "Muenchen - 20. Nov 2009";
+
+// BLIT2009, Brandenburger Linux-Infotag, 21.11.2009
+ $locid = 1486658; // Potsdam
+ $eventname = "Brandenburger Linux-Infotag (BLIT2009)";
+ $city = "Potsdam - 21. Nov 2009";
+
+
+ $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.";
+?>
diff --git a/scripts/cebitemail.txt b/scripts/cebitemail.txt
new file mode 100644
index 0000000..5379f44
--- /dev/null
+++ b/scripts/cebitemail.txt
@@ -0,0 +1,36 @@
+English version below.
+
+Sehr geehrter CAcert Benutzer
+
+Nach dem gro=DFen Erfolg vergangenes Jahr auf der CeBIT, wird CAcert auch d=
+ieses=20
+Jahr wieder auf der CeBIT 2006 mit einem Messestand vertreten sein:
+
+Halle 5, Stand Nr. E 64/7
+
+Wir brauchen noch:
+=2D =DCbernachtungsm=F6glichkeiten (in und um Hannover)
+=2D Helfer (auch nur an einzelnen Tagen)
+
+Wer mithelfen kann, bekommt n=E4here Informationen unter
+http://wiki.cacert.org/wiki/CeBit=20
+
+Mit freundlichen Gr=FC=DFen,
+CAcert Support
+
+Dear CAcert user,
+
+after the huge success at the CeBIT 2005, CAcert will be again=20
+this year on the CeBIT 2006:
+
+Hall 5, booth E 64/7
+
+We still need:
+=2D places to sleep (in and around Hannover)
+=2D people to help at the booth
+
+If you can help, please read further details on
+http://wiki.cacert.org/wiki/CeBit=20
+
+Best regards,
+CAcert Support
diff --git a/scripts/cleanthem.pl b/scripts/cleanthem.pl
new file mode 100644
index 0000000..18d68b0
--- /dev/null
+++ b/scripts/cleanthem.pl
@@ -0,0 +1,24 @@
+#!/usr/bin/perl -w
+
+my %mysqltables=("gpg"=>"gpg","client"=>"emailcerts","server"=>"domaincerts","orgclient"=>"orgemailcerts","orgserver"=>"orgdomaincerts");
+
+sub mysql_query($)
+{
+ $dbh->do($_[0]);
+}
+
+my $aktiv=1;
+
+foreach my $dir (("../csr","../crt"))
+{
+ my $dirhandle;
+ opendir($dirhandle,"../csr");
+ while($_=readdir($dirhandle))
+ {
+ if(! -s "$dir/$_" and -f "$dir/$_" and !-d "$dir/$_")
+ {
+ print "Loesche $dir/$_\n";
+ unlink "$dir/$_";
+ }
+ }
+}
diff --git a/scripts/clientcerts.php b/scripts/clientcerts.php
new file mode 100755
index 0000000..44f7bac
--- /dev/null
+++ b/scripts/clientcerts.php
@@ -0,0 +1 @@
+<? sleep(30); ?>
diff --git a/scripts/consistence.php b/scripts/consistence.php
new file mode 100755
index 0000000..8d6b39b
--- /dev/null
+++ b/scripts/consistence.php
@@ -0,0 +1,105 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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");
+
+
+if(0)
+{
+ $query = "select locations.id from locations, regions where locations.regid=regions.id and locations.ccid!=regions.ccid;";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "update users set `assurer`='1' where `id`='${row['uid']}'";
+ echo "inconsistence in location ".$row['locations.id']."\n";
+ //mysql_query($query);
+ }
+}
+
+if(0)
+{
+ $query = "select id from locations where regid<1 or ccid<1;";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ //$query = "update users set `assurer`='1' where `id`='${row['uid']}'";
+ echo "inconsistence in location ".$row['id']."\n";
+ //mysql_query($query);
+ }
+}
+if(1)
+{
+ $query = "select users.id, locations.regid from users inner join locations on users.locid=locations.id where users.regid!=locations.regid or users.ccid!=locations.ccid;";
+ $res = mysql_query($query);
+ echo mysql_error();
+ while($row = mysql_fetch_assoc($res))
+ {
+ echo "inconsistence in user #".$row['id']."\n";
+ $query = "update users set regid=".$row['regid']." where `id`=".$row['id'].";";
+
+ echo "query: $query\n";
+ if($row['regid']=="1182") mysql_query($query);
+ }
+}
+
+exit();
+
+ mysql_query("update `locations` set `acount`=0");
+ $query = "SELECT `users`.`locid` AS `locid`, count(*) AS `total` FROM `users`
+ WHERE users.assurer='1' AND `users`.`locid` != 0 and users.listme=1
+ GROUP BY `users`.`locid`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "update `locations` set `acount`='${row['total']}' where `id`='${row['locid']}'";
+ echo $query."\n";
+ mysql_query($query);
+ }
+
+
+ mysql_query("update `regions` set `acount`=0");
+ $query = "SELECT `users`.`regid` AS `regid`, count(*) AS `total` FROM `users`
+ WHERE users.assurer='1' AND `users`.`regid` != 0 and users.listme=1
+ GROUP BY `users`.`regid`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "update `regions` set `acount`='${row['total']}' where `id`='${row['regid']}'";
+ echo $query."\n";
+ mysql_query($query);
+ }
+
+
+
+
+ mysql_query("update `countries` set `acount`=0");
+ $query = "SELECT `users`.`ccid` AS `ccid`, count(*) AS `total` FROM `users`
+ WHERE users.assurer='1' AND `users`.`ccid` != 0 and users.listme=1
+ GROUP BY `users`.`ccid`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "update `countries` set `acount`='${row['total']}' where `id`='${row['ccid']}'";
+ echo $query."\n";
+ mysql_query($query);
+ }
+
+
+
+
+?>
diff --git a/scripts/country.php b/scripts/country.php
new file mode 100755
index 0000000..0c5fc4f
--- /dev/null
+++ b/scripts/country.php
@@ -0,0 +1,28 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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");
+
+ $query = "select * from `users` where ccid=13 OR email like '%.at'";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['id']." ccid: ".$row['ccid'].")\n";
+ }
+?>
diff --git a/scripts/findexp3.pl b/scripts/findexp3.pl
new file mode 100644
index 0000000..9fcb60d
--- /dev/null
+++ b/scripts/findexp3.pl
@@ -0,0 +1,14 @@
+#!/usr/bin/perl -w
+
+foreach (<../crt/*>)
+{
+ my $res=`openssl x509 -in $_ -text -noout`;
+ if($res=~m/Exponent: 65537 /)
+ {
+ print "Alert: $_ is affected!\n";
+ }
+ else
+ {
+ #print "$_ not affected\n";
+ }
+}
diff --git a/scripts/findnull.pl b/scripts/findnull.pl
new file mode 100644
index 0000000..02df02f
--- /dev/null
+++ b/scripts/findnull.pl
@@ -0,0 +1,14 @@
+#!/usr/bin/perl -w
+
+foreach (<../crt/*>)
+{
+ my $res=`openssl x509 -in $_ -text -noout -inform der`;
+ if($res=~m/\\x00/)
+ {
+ print "Alert: $_ is affected!\n";
+ }
+ else
+ {
+ #print "$_ not affected\n";
+ }
+}
diff --git a/scripts/gpgcerts.php b/scripts/gpgcerts.php
new file mode 100755
index 0000000..93ee4b3
--- /dev/null
+++ b/scripts/gpgcerts.php
@@ -0,0 +1 @@
+<? sleep(3); ?>
diff --git a/scripts/gpgcheck3.php b/scripts/gpgcheck3.php
new file mode 100644
index 0000000..a6f6097
--- /dev/null
+++ b/scripts/gpgcheck3.php
@@ -0,0 +1,245 @@
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("../includes/mysql.php"); //general.php");
+
+ echo("Content-Type: text/csv; charset=utf-8");
+
+function mb_replace($str, $match, $replacement) {
+ if ($match == "") { return $str; }
+ $mlen = mb_strlen($match, "UTF-8");
+ while (($pos = mb_strpos($str, $match, 0, "UTF-8")) != "") {
+ //print "$str, $match, $replacement, $pos\n";
+ $str = mb_substr($str, 0, $pos, "UTF-8")
+ . ":" . $replacement . ":"
+ . mb_substr($str, $pos + $mlen,
+ mb_strlen($str, "UTF-8") - $pos - $mlen,
+ "UTF-8");
+ //$pos = mb_strpos($str, $match, 0, "UTF-8");
+ //print "$str, $match, $replacement, $pos\n";
+ }
+ $replacement .= "__C_A_S_E__M_I_S_M_A_T_C_H";
+ while ($_GET['case']
+ && ($pos = mb_stripos($str, $match, 0, "UTF-8")) != "") {
+ $str = mb_substr($str, 0, $pos, "UTF-8")
+ . ":" . $replacement . ":"
+ . mb_substr($str, $pos + $mlen,
+ mb_strlen($str, "UTF-8") - $pos - $mlen,
+ "UTF-8");
+ }
+ return $str;
+}
+
+function deUmlaut($str) {
+ return iconv("UTF-8", "US-ASCII//TRANSLIT",
+ mb_convert_encoding($str, "UTF-8"));
+}
+
+function deUmlaut2($str) {
+ return mb_ereg_replace(iconv("ISO-8859-1", "UTF-8", "Ä"), "Ae",
+ mb_ereg_replace(iconv("ISO-8859-1", "UTF-8", "Ö"), "Oe",
+ mb_ereg_replace(iconv("ISO-8859-1", "UTF-8", "Ü"), "Ue",
+ mb_ereg_replace(iconv("ISO-8859-1", "UTF-8", "ä"), "ae",
+ mb_ereg_replace(iconv("ISO-8859-1", "UTF-8", "ö"), "oe",
+ mb_ereg_replace(iconv("ISO-8859-1", "UTF-8", "ü"), "ue",
+ mb_ereg_replace(iconv("ISO-8859-1", "UTF-8", "ß"), "ss",
+ mb_convert_encoding($str, "UTF-8"))))))));
+}
+
+function csvize($str) {
+ if (strpos($str, "\"") != "" || strpos($str, ",") != "") {
+ return "\"" . str_replace("\"", "\"\"", $str) . "\"";
+ }
+ return $str;
+}
+ mb_regex_encoding("UTF-8");
+
+ $res = mysql_query("SELECT id, memid FROM gpg WHERE crt != ''");
+ if (!$res) {
+ echo "Query FROM gpg failed!\n";
+ exit;
+ }
+
+ $keys = array();
+ while ($row = mysql_fetch_row($res)) {
+ array_push($keys, $row);
+ }
+ mysql_free_result($res);
+
+ foreach ($keys as $key) {
+ $crt = "../crt/gpg-" . $key[0] . ".crt";
+ if (!is_file($crt)) {
+ echo "Missing cert $crt!\n";
+ continue;
+ }
+
+ $res = mysql_query("SELECT fname, mname, lname, suffix FROM users WHERE id = " . $key[1]);
+ if (!$res) {
+ echo "Query FROM users failed!\n";
+ exit;
+ }
+ $user = mysql_fetch_assoc($res);
+ if (!$user) {
+ echo "User #" . $key[1] . " not found?!\n";
+ continue;
+ }
+ mysql_free_result($res);
+
+ $res = mysql_query("SELECT email FROM email WHERE hash = '' AND memid = " . $key[1]);
+ if (!$res) {
+ echo "Query FROM email failed!\n";
+ exit;
+ }
+ $addrs = array();
+ while ($addr = mysql_fetch_row($res)) {
+ array_push($addrs, $addr[0]);
+ }
+ mysql_free_result($res);
+
+ $gpg = `gpg --with-colons --homedir /tmp $crt 2>/dev/null`;
+ //echo "gpg says\n".htmlspecialchars($gpg);
+ foreach (explode("\n", $gpg) as $line) {
+ $bits = explode(":", $line);
+ if ($bits[0] != "pub" && $bits[0] != "uid") {
+ continue;
+ }
+ $match = false;
+ $problem = "";
+ $uid = " ".preg_replace('~\\\\x([0-9a-f])([0-9a-f])~ei', 'chr(hexdec("\\1\\2"))', $bits[9]);
+ //print "$uid\n";
+ if (iconv("UTF-8", "UTF-8", $uid)) {
+ $uid = mb_ereg_replace("\\\\", "\\x5c", $uid);
+ $uid = mb_ereg_replace("\\:", "\\x3a", $uid);
+ } else {
+ if ($tmp = iconv("ISO-8859-1", "UTF-8", $uid)) {
+ $problem = ":BAD_ENCODING:";
+ $uid = $tmp;
+ $uid = mb_ereg_replace("\\\\", "\\x5c",
+ $uid);
+ $uid = mb_ereg_replace("\\:", "\\x3a",
+ $uid);
+ } else {
+ $problem = ":UNKNOWN_ENCODING:";
+ $uid = $bits[9];
+ }
+ }
+ //print "$uid\n";
+ foreach ($addrs as $addr) {
+ //print "$uid, $addr\n";
+ //print mb_convert_encoding($addr, "UTF-8")."\n";
+ $uid = mb_replace($uid,
+ mb_convert_encoding($addr,
+ "UTF-8"),
+ "V_A_L_I_D__E_M_A_I_L");
+ }
+ //print "$uid\n";
+ $uid = mb_replace($uid,
+ mb_convert_encoding($user['lname'],
+ "UTF-8"),
+ "L_N_A_M_E");
+ $uid = mb_replace($uid,
+ mb_convert_encoding($user['fname'],
+ "UTF-8"),
+ "F_N_A_M_E");
+ $uid = mb_replace($uid,
+ mb_convert_encoding($user['mname'],
+ "UTF-8"),
+ "M_N_A_M_E");
+ $uid = mb_replace($uid,
+ mb_convert_encoding($user['suffix'],
+ "UTF-8"),
+ "S_U_F_F_I_X");
+ $uid = mb_replace($uid, deUmlaut($user['lname']),
+ "L_N_A_M_E__U_M_L_A_U_T");
+ $uid = mb_replace($uid, deUmlaut($user['fname']),
+ "F_N_A_M_E__U_M_L_A_U_T");
+ $uid = mb_replace($uid, deUmlaut($user['mname']),
+ "M_N_A_M_E__U_M_L_A_U_T");
+ $uid = mb_replace($uid, deUmlaut($user['suffix']),
+ "S_U_F_F_I_X__U_M_L_A_U_T");
+//print deUmlaut2($user['lname'])."\n";
+ $uid = mb_replace($uid, deUmlaut2($user['lname']),
+ "L_N_A_M_E__U_M_L_A_U_T");
+ $uid = mb_replace($uid, deUmlaut2($user['fname']),
+ "F_N_A_M_E__U_M_L_A_U_T");
+ $uid = mb_replace($uid, deUmlaut2($user['mname']),
+ "M_N_A_M_E__U_M_L_A_U_T");
+ $uid = mb_replace($uid, deUmlaut2($user['suffix']),
+ "S_U_F_F_I_X__U_M_L_A_U_T");
+ if (strlen($user['mname']) > 0) {
+ $uid = mb_replace($uid,
+ mb_convert_encoding(substr($user['mname'], 0, 1) . ".",
+ "UTF-8"),
+ "M_N_A_M_E__I_N_I_T_I_A_L");
+ }
+ if (strlen($user['fname']) > 0) {
+ $uid = mb_replace($uid,
+ mb_convert_encoding(substr($user['fname'], 0, 1) . ".",
+ "UTF-8"),
+ "F_N_A_M_E__I_N_I_T_I_A_L");
+ }
+ $nameRegEx = "^ (:F_N_A_M_E([^:]*):\s+"
+ . "(:M_N_A_M_E([^:]*):\s+)?"
+ . ":L_N_A_M_E([^:]*):"
+ . "(\s+:S_U_F_F_I_X([^:]*):)?)?"
+ . "(\s*\(.*\))?" // optional comment
+ . "(\s*<?:V_A_L_I_D__E_M_A_I_L([^:]*):>?)?"
+ . "\$";
+ if (!mb_ereg_search_init($uid, $nameRegEx)) {
+ $problem .= ":REGEX_FAILED:";
+ }
+ $res = mb_ereg_search_regs();
+ if ($res) {
+ $match = true;
+ if ($res[8] != "") {
+ $problem .= ":UNPARSED_COMMENT:";
+ }
+ if ($res[2] != "") {
+ $problem .= ":".$res[2].":";
+ }
+ if ($res[4] != "") {
+ $problem .= ":".$res[4].":";
+ }
+ if ($res[5] != "") {
+ $problem .= ":".$res[5].":";
+ }
+ if ($res[7] != "") {
+ $problem .= ":".$res[7].":";
+ }
+//print $res[0].",".$res[1].",".$res[2].",".$res[3].",".$res[4].","
+// .$res[5].",".$res[6].",".$res[7].",".$res[8].",".$res[9].","
+// .$res[10]."\n";
+ } else {
+ $problem = ":MISMATCH:$problem";
+ }
+ if (!$match || $problem != "") {
+ print $key[0] . "," . csvize($problem) . ","
+ . csvize($uid) . ","
+ . csvize(preg_replace('/([^ -~])/ei', '"\\\\x".sprintf("%02x", ord("\\1"))', $bits[9])) . ","
+ . mb_convert_encoding(csvize($user['fname']), "UTF-8") . ","
+ . mb_convert_encoding(csvize($user['mname']), "UTF-8") . ","
+ . mb_convert_encoding(csvize($user['lname']), "UTF-8") . ","
+ . mb_convert_encoding(csvize($user['suffix']), "UTF-8");
+ foreach ($addrs as $addr) {
+ print "," . mb_convert_encoding(csvize($addr), "UTF-8");
+ }
+ print "\n";
+ }
+ }
+ }
+
+?>
diff --git a/scripts/gpgfillmissingemail.php b/scripts/gpgfillmissingemail.php
new file mode 100644
index 0000000..f328876
--- /dev/null
+++ b/scripts/gpgfillmissingemail.php
@@ -0,0 +1,88 @@
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("../includes/mysql.php"); //general.php");
+ //include "../includes/general.php";
+
+function hex2bin($data)
+ {
+ while(strstr($data, "\\x"))
+ {
+ $pos = strlen($data) - strlen(strstr($data, "\\x"));
+ $before = substr($data, 0, $pos);
+ $char = chr(hexdec(substr($data, $pos + 2, 2)));
+ $after = substr($data, $pos + 4);
+ $data = $before.$char.$after;
+ }
+ return(utf8_decode($data));
+ }
+
+
+function csvize($str)
+{
+ if (strpos($str, "\"") != "" || strpos($str, ",") != "") {
+ return "\"" . str_replace("\"", "\"\"", $str) . "\"";
+ }
+ return $str;
+}
+ mb_regex_encoding("UTF-8");
+
+echo "Seaching ...\n";
+ $res = mysql_query("SELECT * FROM gpg WHERE crt != '' and email=''");
+ if (!$res) {
+ echo "Query FROM gpg failed!\n";
+ exit;
+ }
+echo "Found:\n";
+
+ $keys = array();
+ while ($row = mysql_fetch_assoc($res)) {
+ echo "ID: ".$row["id"]."\n";
+ $crt=$row["crt"];
+
+ $gpg = `gpg --with-colons --homedir /tmp $crt 2>/dev/null`;
+ //echo "gpg says\n".htmlspecialchars($gpg);
+ foreach (explode("\n", $gpg) as $line)
+ {
+ $bits = explode(":", $line);
+ if ($bits[0] != "pub" && $bits[0] != "uid") {
+ continue;
+ }
+ if($bits[0] == "pub")
+ {
+
+
+ if (preg_match("/<([\w.-]*\@[\w.-]*)>/", $bits[9],$match))
+ {
+ //echo "Found: ".$match[1];
+ $mail = trim(hex2bin($match[1]));
+
+
+ echo "EMail: *$mail**\n";
+ echo "update gpg set email='$mail' where id=$row[id]\n";
+ mysql_query("update gpg set email='$mail' where id=$row[id];");
+ }
+ }
+ }
+
+
+ }
+ echo "Done\n";
+ mysql_free_result($res);
+
+
+?>
diff --git a/scripts/gpgfillmissingkeyid.php b/scripts/gpgfillmissingkeyid.php
new file mode 100644
index 0000000..8c5ce4a
--- /dev/null
+++ b/scripts/gpgfillmissingkeyid.php
@@ -0,0 +1,73 @@
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("../includes/mysql.php"); //general.php");
+
+function csvize($str)
+{
+ if (strpos($str, "\"") != "" || strpos($str, ",") != "") {
+ return "\"" . str_replace("\"", "\"\"", $str) . "\"";
+ }
+ return $str;
+}
+ mb_regex_encoding("UTF-8");
+
+echo "Seaching ...\n";
+ $res = mysql_query("SELECT * FROM gpg WHERE crt != '' and keyid is null");
+ if (!$res) {
+ echo "Query FROM gpg failed!\n";
+ exit;
+ }
+echo "Found:\n";
+
+ $keys = array();
+ while ($row = mysql_fetch_assoc($res)) {
+ echo "ID: ".$row["id"]."\n";
+ $crt=$row["crt"];
+
+ $gpg = `gpg --with-colons --homedir /tmp $crt 2>/dev/null`;
+ echo "gpg says\n".htmlspecialchars($gpg);
+ foreach (explode("\n", $gpg) as $line)
+ {
+ $bits = explode(":", $line);
+ if ($bits[0] != "pub" && $bits[0] != "uid") {
+ continue;
+ }
+ if($bits[0] == "pub")
+ {
+ echo "KeyID: ".$bits[4]."\n";
+ echo "update gpg set keyid='$bits[4]' where id=$row[id]\n";
+ echo "laenge: ".strlen($bits[4])."\n";
+ if($row[id]>=1 && $row[id]<=100000 && strlen($bits[4])==16)
+ {
+ mysql_query("update gpg set keyid='$bits[4]' where id=$row[id]\n");
+
+ }
+ }
+ $match = false;
+ $problem = "";
+ $uid = " ".preg_replace('~\\\\x([0-9a-f])([0-9a-f])~ei', 'chr(hexdec("\\1\\2"))', $bits[9]);
+ print "UID: $uid\n";
+ }
+
+
+ }
+ echo "Done\n";
+ mysql_free_result($res);
+
+
+?>
diff --git a/scripts/koelnemail.txt b/scripts/koelnemail.txt
new file mode 100644
index 0000000..3b78e22
--- /dev/null
+++ b/scripts/koelnemail.txt
@@ -0,0 +1,28 @@
+Hallo,
+
+am Freitag, den 30.Mai.2008 1800 Uhr treffen wir uns wieder in Koeln an der
+Studentenbibliothek im
+Physikalischen Institut zum Assuren/Keysignen fuer CAcert.
+
+Stift und zwei amtliche Lichtbildausweise nicht vergessen.
+
+Ziel der Veranstaltung ist diesmal das Assuren eines VIP.
+Schaut Euch vorher genau die Sicherheitsmerkmale deutscher und rumaenischer
+Fuehrerscheine und Personalausweise an.
+Be prepared.
+
+Um 1830 ist es auch schon wieder vorbei.
+
+Bitte kurze Zusage per Mail,
+
+http://www.uni-koeln.de/uni/gebaeude/321.html
+
+Am Eingang des Hoersaal 3 vorbei laeuft man auf den Eingang der
+Studentenbibliothek zu. Dann links durch die Glastuere.
+http://www.uni-koeln.de/uni/gebaeude/plaene/physik_eg.html
+
+Gruesse,
+
+Jonas Stein
+cacert@mail.jonasstein.de
+
diff --git a/scripts/lisa-us13-email.txt b/scripts/lisa-us13-email.txt
new file mode 100644
index 0000000..9222af3
--- /dev/null
+++ b/scripts/lisa-us13-email.txt
@@ -0,0 +1,12 @@
+LISA'09: CACert BoF session, Tues, Need a few assurers to help
+get things rolling.
+
+Greeting,
+
+I will be leading GPG Key signing and CACert.org BoF sessions next Tuesday evening at the LISA 2009 conference in Baltimore, MD. At LISA'04 in Atlanta, I led a GPG key signing BoF and I attended a CACert BoF. I became an assurer at that conference. I helped 35 people gain assurance points at the 2004 conference. I hope we can add a good number of new assurers at this years conference.
+
+In order to do that, I need some help from a few qualified assurers. With as few as three already qualified assurers, we can bring several new people into the community. More assurers means we can split the work and move through everything quicker and easier. So far, I have just myself, so I really NEED some help. If you will be available, please contact me.
+
+Ken Schumacher
+
+Event Listing: http://wiki.cacert.org/events/LISA2009
diff --git a/scripts/lisa-us13-mail.php.txt b/scripts/lisa-us13-mail.php.txt
new file mode 100644
index 0000000..cddde83
--- /dev/null
+++ b/scripts/lisa-us13-mail.php.txt
@@ -0,0 +1,96 @@
+#!/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("lisa-us13-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 = 250;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+// $locid = 715191; // Hamburg
+
+// LISA2009 Baltimore, 1.11.2009
+ $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+ $city = "Baltimore, MD - Nov. 3rd 2009";
+
+
+ $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] LISA2009 - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] LISA2009 - $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] LISA2009 - $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] LISA2009 - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ echo "invitation sent to $xrows recipients.";
+?>
diff --git a/scripts/mission-hills-ca-us17-email.txt b/scripts/mission-hills-ca-us17-email.txt
new file mode 100644
index 0000000..9389c07
--- /dev/null
+++ b/scripts/mission-hills-ca-us17-email.txt
@@ -0,0 +1,17 @@
+Hello,
+This email is to let you know there will be a number of assurers gathering in
+your area. Please bring several (4-6) printed CAP forms and your photo ID's.
+
+Friday Jan 15th, 6:30PM,
+Presidente Mexican Restaurant
+11451 Sepulveda Boulevard
+Mission Hills, CA 91345-1117
+
+If you can make it, remember it will be your opertunity to gain assurances
+for yourself and boost the number of points you can assure to.
+
+Please RVSP thank you.
+
+Regards,
+Greg Stark
+gstark@electrorent.com
diff --git a/scripts/mission-hills-ca-us17-mail.php.txt b/scripts/mission-hills-ca-us17-mail.php.txt
new file mode 100644
index 0000000..c52d87c
--- /dev/null
+++ b/scripts/mission-hills-ca-us17-mail.php.txt
@@ -0,0 +1,115 @@
+#!/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("mission-hills-ca-us17-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 = 50;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+// $locid = 715191; // Hamburg
+
+// LISA2009 Baltimore, 1.11.2009
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $city = "Baltimore, MD - Nov. 3rd 2009";
+
+// OpenSourceTreffen-Muenchen, 20.11.2009
+// $locid = 1260319; // Muenchen
+// $city = "Muenchen - 20. Nov 2009";
+
+// BLIT2009, Brandenburger Linux-Infotag, 21.11.2009
+// $locid = 1486658; // Potsdam
+// $eventname = "Brandenburger Linux-Infotag (BLIT2009)";
+// $city = "Potsdam - 21. Nov 2009";
+
+// ATE-Goteborg, 16.12.2009
+// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+// $eventname = "ATE-Goteborg";
+// $city = "Goteborg - Dec 16th 2009";
+
+// Assurance Event Mission Hills CA, 15.01.2010
+ $locid = 2094781; // Mission Hills (Los Angeles), California, United States
+ $eventname = "Assurance Event";
+ $city = "Mission Hills CA - Jan 15th 2010";
+
+
+ $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/nearest.php b/scripts/nearest.php
new file mode 100755
index 0000000..c5d0578
--- /dev/null
+++ b/scripts/nearest.php
@@ -0,0 +1,34 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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
+*/
+
+// Searches for all assurers in a certain region
+
+ include_once("../includes/mysql.php");
+
+ $query = "select * from `users` where `locid`=33572 OR `regid`=870";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $ass = mysql_num_rows(mysql_query("select * from `notary` where `from`='".$row['id']."' and `from`!=`to`"));
+ $dres = mysql_query("select sum(`points`) as `tp` from `notary` where `to`='".$row['id']."'");
+ $drow = mysql_fetch_assoc($dres);
+ if($drow['tp'] < 100)
+ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['id']." assurances: $ass tp: ".$drow['tp'].")\n";
+ }
+?>
diff --git a/scripts/newsletter.php b/scripts/newsletter.php
new file mode 100755
index 0000000..04897d2
--- /dev/null
+++ b/scripts/newsletter.php
@@ -0,0 +1,50 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("koelnemail.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+ $query = "select * from `locations` where `id`='417638'";
+ $loc = mysql_fetch_assoc(mysql_query($query));
+ $query = "select `users`.* from `users`,`alerts`,`locations` where
+ ((`lat` > ".$loc['lat']."-0.1 and `lat`<".$loc['lat']."+0.1 and `long`>".$loc['long']."-0.1 and `long`<".$loc['long']."+0.1)
+)and
+ (`alerts`.`general`=1 OR `alerts`.`country`=1 OR `alerts`.`regional`=1 OR `alerts`.`radius`=1) AND
+ `locations`.`id` = `users`.`locid` and `users`.`id`=`alerts`.`memid`";
+ //$query = "select * from `users` where `email`='pg@futureware.at'";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ sendmail($row['email'], "[CAcert.org] Keysigningparty Koeln", $lines, "support@cacert.org", "", "", "CAcert Support", "returns@cacert.org", 1);
+echo $row['email']."\n";
+ }
+
+
+// OR `users`.`email` like '%.at' OR `users`.`email` like '%.fr' OR `users`.`email` like '%.de' OR
+// `users`.`email` like '%.nl' OR `users`.`email` like '%.be' OR `users`.`email` like '%.ch'
+// OR `users`.`email` like '%.es' OR `users`.`email` like '%.pt' OR `users`.`email` like '%.ee'
+
+?>
diff --git a/scripts/newslettercebit.php b/scripts/newslettercebit.php
new file mode 100755
index 0000000..9fd64d1
--- /dev/null
+++ b/scripts/newslettercebit.php
@@ -0,0 +1,47 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("cebitemail.txt", "r");
+ while(!feof($fp))
+ {
+ $line = trim(fgets($fp, 4096));
+ $lines .= wordwrap($line, 75, "\n")."\n";
+ }
+ fclose($fp);
+
+ $query = "select * from `users` where `id`='1'";
+ $query = "select * from `locations` where `id`='718475'";
+ $loc = mysql_fetch_assoc(mysql_query($query));
+ $query = "select `users`.* from `users`,`alerts`,`locations` where
+ ((`lat` > ".$loc['lat']."-5 and `lat`<".$loc['lat']."+5 and `long`>".$loc['long']."-5 and `long`<".$loc['long']."+5) OR
+ `users`.`email` like '%.at' OR `users`.`email` like '%.fr' OR `users`.`email` like '%.de' OR
+ `users`.`email` like '%.nl' OR `users`.`email` like '%.be' OR `users`.`email` like '%.ch'
+ OR `users`.`email` like '%.es' OR `users`.`email` like '%.pt' OR `users`.`email` like '%.ee')and
+ (`alerts`.`general`=1 OR `alerts`.`country`=1 OR `alerts`.`regional`=1 OR `alerts`.`radius`=1) AND
+ `locations`.`id` = `users`.`locid` and `users`.`id`=`alerts`.`memid`";
+// $query = "select * from `users` where `email`='eg@linuxkun.de'";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ sendmail($row['email'], "[CAcert.org] CeBIT 2006", $lines, "support@cacert.org", "", "", "CAcert Support", "returns@cacert.org", 1);
+echo $row['email']."\n";
+ }
+?>
diff --git a/scripts/notify.php b/scripts/notify.php
new file mode 100755
index 0000000..9a96372
--- /dev/null
+++ b/scripts/notify.php
@@ -0,0 +1,42 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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");
+
+ $query = "select `users`.`fname` as `fname`, `email`.`id` as `id`, `email`.`email` as `email` from `users`,`email`
+ where `users`.`verified`=0 and
+ (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`users`.`created`)) >= 300 and
+ `users`.`id`=`email`.`memid` and `users`.`email`=`email`.`email`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $rnd = fopen("/dev/urandom", "r");
+ $hash = md5(fgets($rnd, 64));
+ fclose($rnd);
+
+ mysql_query("update `email` set `hash`='$hash' where `id`='".$row['id']."'");
+
+ $body = "Hi ".$row['fname']."\n\n";
+ $body .= "Due to some bugs with the new website we initially had issues with emails being sent out. This email is being sent to those effected so they can be re-sent their email probe to over come earlier issues. We apologise for any inconvenience this may have cause. To verify your account, simply click on the link below.\n\n";
+ $body .= "http://www.cacert.org/verify.php?type=email&emailid=".$row['id']."&hash=$hash\n\n";
+ $body .= "Best Regards\nCAcert Support Team";
+echo $row['email']."\n";
+ sendmail($row['email'], "[CAcert.org] Email Probe", $body, "support@cacert.org", "", "", "CAcert Support");
+ }
+?>
diff --git a/scripts/osd-copenhagen-dk18-email.txt b/scripts/osd-copenhagen-dk18-email.txt
new file mode 100644
index 0000000..5301472
--- /dev/null
+++ b/scripts/osd-copenhagen-dk18-email.txt
@@ -0,0 +1,35 @@
+Open Source Days 2010 - Copenhagen, DK - March 5th and 6th 2010
+:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
+
+Dear Assurers,
+
+CAcert is trying to get a booth at the
+Open Source Days 2010 conference at Copenhagen DK
+March 5th and 6th 2010
+http://www.opensourcedays.org/2010
+
+The call for participation deadline is Jan 20th 2010
+
+As we have another event at the same time (Cebit 2010), we are in need
+of assistance from additional local assurers who can make this thing happen.
+
+We are looking for assurers who are available to organize
+CAcert's participation at Open Source Days 2010.
+
+As organizer for this event you get all assistance you'll need
+from the events team leader.
+
+If you are interested in helping please respond
+to this email so we can trigger the the call for participation
+before the deadline.
+
+Thanks for your assistance in advance.
+
+
+--
+mit freundlichen Gruessen / best regards
+Ulrich Schroeter - CAcert Event Organisation Officer, CAcert Case Manager, CAcert Arbitrator
+
+CAcert.org - Free Certificates
+E-Mail: Events@CAcert.org & Ulrich@CAcert.org
+
diff --git a/scripts/osd-copenhagen-dk18-mail.php.txt b/scripts/osd-copenhagen-dk18-mail.php.txt
new file mode 100644
index 0000000..e99d96c
--- /dev/null
+++ b/scripts/osd-copenhagen-dk18-mail.php.txt
@@ -0,0 +1,120 @@
+#!/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("osd-copenhagen-dk18-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 = 50;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+// $locid = 715191; // Hamburg
+
+// LISA2009 Baltimore, 1.11.2009
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $city = "Baltimore, MD - Nov. 3rd 2009";
+
+// OpenSourceTreffen-Muenchen, 20.11.2009
+// $locid = 1260319; // Muenchen
+// $city = "Muenchen - 20. Nov 2009";
+
+// BLIT2009, Brandenburger Linux-Infotag, 21.11.2009
+// $locid = 1486658; // Potsdam
+// $eventname = "Brandenburger Linux-Infotag (BLIT2009)";
+// $city = "Potsdam - 21. Nov 2009";
+
+// ATE-Goteborg, 16.12.2009
+// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+// $eventname = "ATE-Goteborg";
+// $city = "Goteborg - Dec 16th 2009";
+
+// Assurance Event Mission Hills CA, 15.01.2010
+// $locid = 2094781; // Mission Hills (Los Angeles), California, United States
+// $eventname = "Assurance Event";
+// $city = "Mission Hills CA - Jan 15th 2010";
+
+// Assurance Event OSD Copenhagen DK, 5.03.2010
+ $locid = 423655; // Copenhagen, Kobenhavn*, Denmark
+ $eventname = "Assurance Event OpenSource-Days 2010";
+ $city = "Copenhagen DK - March 5th/6th 2010";
+
+
+ $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/ost-de14-email.txt b/scripts/ost-de14-email.txt
new file mode 100644
index 0000000..30862ff
--- /dev/null
+++ b/scripts/ost-de14-email.txt
@@ -0,0 +1,22 @@
+Sicherheit im Zeitalter der IT und des Internets - aufwendig und teuer?
+Das muss nicht sein!
+CAcert zeigt Dir, wie Du einfach und preiswert E-Mails signierst und
+Internet-Verbindungen verschluesselst.
+
+Die so genannten Zertifikate von CAcert lassen sich in Programmen
+wie Thunderbird und vielen anderen E-Mail-Programmen oder in
+ausgewachsenen Office-Programmen wie z.B. OpenOffice.org benutzen.
+Zur Verschluesselung von Internet-Verbindungen koennen die Zertifikate
+in Webservern und als Login-Moeglichkeit bei entsprechenden Gegenstellen
+in Webbrowsern eingesetzt werden.
+
+Auch Du kannst mitmachen, dazu brauchst Du nur Deinen Personalausweis
+und idealerweise einen Fuehrerschein oder Reisepass mitzubringen.
+
+Freitag, den 20. November, ab 17 Uhr im Café Netzwerk
+http://www.cafe-netzwerk.de/ in Muenchen
+
+CAcert Vorstellung im Rahmen der Open-Source Treffen
+http://www.opensourcetreffen.de/
+
+Kontakt: events@cacert.org
diff --git a/scripts/ost-de14-mail.php.txt b/scripts/ost-de14-mail.php.txt
new file mode 100644
index 0000000..af98ed8
--- /dev/null
+++ b/scripts/ost-de14-mail.php.txt
@@ -0,0 +1,100 @@
+#!/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("ost-de14-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 = 50;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+// $locid = 715191; // Hamburg
+
+// LISA2009 Baltimore, 1.11.2009
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $city = "Baltimore, MD - Nov. 3rd 2009";
+
+// OpenSourceTreffen-Muenchen, 20.11.2009
+ $locid = 1260319; // Muenchen
+ $city = "Muenchen - 20. Nov 2009";
+
+
+ $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] Open-Source Treffen - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] Open-Source Treffen - $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] Open-Source Treffen - $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] Open-Source Treffen - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ echo "invitation sent to $xrows recipients.";
+?>
diff --git a/scripts/removedead.php b/scripts/removedead.php
new file mode 100755
index 0000000..7e47c7f
--- /dev/null
+++ b/scripts/removedead.php
@@ -0,0 +1,86 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("/home/cacert/www/includes/mysql.php");
+
+ $query = "select * from `users` where `users`.`verified`=0 and
+ (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`users`.`created`)) >= 172800";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ mysql_query("delete from `email` where `memid`='".$row['id']."'");
+ mysql_query("delete from `users` where `id`='".$row['id']."'");
+ }
+
+ $query = "delete from `domains` where `hash`!='' and
+ (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`created`)) >= 172800";
+ mysql_query($query);
+
+ $query = "delete from `email` where `hash`!='' and
+ (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`created`)) >= 172800";
+ mysql_query($query);
+
+ $query = "delete from `disputedomain` where `hash`!='' and
+ (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`created`)) >= 21600";
+ mysql_query($query);
+
+ $query = "delete from `disputeemail` where `hash`!='' and
+ (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`created`)) >= 21600";
+ mysql_query($query);
+
+ $query = "select * from `notary` where `expire`!=0 and `expire`<NOW()";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "select sum(`points`) as `points` from `notary` where `to`='$row[to]' and `expire`=0 group by `to`";
+ $dres = mysql_query($query);
+ $drow = mysql_fetch_assoc($dres);
+ if($drow['points'] >= 150)
+ {
+ $query = "update `notary` set `expire`=0, `points`='0' where `to`='$row[to]' and `from`='$row[from]' and `expire`='$row[expire]'";
+ } else {
+ $newpoints = 150 - $drow['points'];
+ $query = "update `notary` set `expire`=0, `points`='0' where `to`='$row[to]' and `from`='$row[from]' and `expire`='$row[expire]'";
+ mysql_query($query);
+ $query = "insert into `notary` set `expire`=0, `points`='$newpoints', `to`='$row[to]', `from`='$row[from]', `when`=NOW(), `method`='Administrative Increase', `date`=NOW()";
+ }
+
+ $data = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='$row[to]'"));
+ $body = sprintf("%s %s (%s) had a temporary increase, but this has just expired and they have been reduced to 150 points.", $data['fname'], $data['lname'], $data['email'])."\n\n";
+ sendmail("cacert-board@lists.cacert.org", "[CAcert.org] Temporary Increase Expired.", $body, "website@cacert.org", "", "", "CAcert Website");
+
+ if($data['language'] != "")
+ {
+ $userlang = $data['language'];
+ putenv("LANG=".$userlang);
+ setlocale(LC_ALL, $userlang);
+ }
+
+ $body = _("You are receiving this email because you had a temporary increase to 200 points. This has since expired and you have been reduced to 150 points.")."\n\n";
+ $body = _("If you needed more time or any other extenuating circumstances you should contact us immediately so this situation can be dealt with immediately.")."\n\n";
+
+ $body .= _("Best regards")."\n";
+ $body .= _("CAcert Support Team");
+
+ sendmail($data['email'], "[CAcert.org] "._("Temporary points increase has expired."), $body, "support@cacert.org", "", "", "CAcert Website");
+
+ mysql_query($query);
+ fix_assurer_flag($row[to]);
+ }
+?>
diff --git a/scripts/runclient.c b/scripts/runclient.c
new file mode 100644
index 0000000..d0e4213
--- /dev/null
+++ b/scripts/runclient.c
@@ -0,0 +1,13 @@
+#include<stdio.h>
+
+int main(int argc, char *argv[])
+{
+ char *args[1];
+
+ args[0] = NULL;
+
+ setuid(0);
+ setgid(0);
+
+ execv("/www/scripts/clientcerts.php", args);
+}
diff --git a/scripts/rungpg.c b/scripts/rungpg.c
new file mode 100644
index 0000000..6b2caad
--- /dev/null
+++ b/scripts/rungpg.c
@@ -0,0 +1,13 @@
+#include<stdio.h>
+
+int main(int argc, char *argv[])
+{
+ char *args[1];
+
+ args[0] = NULL;
+
+ setuid(0);
+ setgid(0);
+
+ execv("../scripts/gpgcerts.php", args);
+}
diff --git a/scripts/runserver.c b/scripts/runserver.c
new file mode 100644
index 0000000..d3cb985
--- /dev/null
+++ b/scripts/runserver.c
@@ -0,0 +1,13 @@
+#include<stdio.h>
+
+int main(int argc, char *argv[])
+{
+ char *args[1];
+
+ args[0] = NULL;
+
+ setuid(0);
+ setgid(0);
+
+ execv("/www/scripts/servercerts.php", args);
+}
diff --git a/scripts/scale8x-los-angeles-ca-us19-email.txt b/scripts/scale8x-los-angeles-ca-us19-email.txt
new file mode 100644
index 0000000..dcc1d89
--- /dev/null
+++ b/scripts/scale8x-los-angeles-ca-us19-email.txt
@@ -0,0 +1,36 @@
+SCALE, the premier open source conference in Los Angeles is open
+for the 8th year.
+It will be held at
+
+5400 West Century Blvd,
+Los Angeles, 90045.
+
+It will be held this coming
+* February 19-21
+at the Los Angeles Airport Westin Hotel.
+
+More details can be found at the website,
+https://www.socallinuxexpo.org/scale8x/
+
+
+Any assurers that are available are welcome to cover any of the slots below.
+If you signup for a slot, please ensure you can stay for the entire time.
+
+
+Sat 09:30 - 14:00
+Sat 14:00 - 18:00
+
+
+Sun 09:30 - 13:00
+Sun 13:00 - 16:00
+
+
+https://wiki.cacert.org/events/SCaLE8x
+has the details on our organization.
+
+
+Thanks,
+Steve
+Event Organiser
+
+contact: events@cacert.org
diff --git a/scripts/scale8x-los-angeles-ca-us19-mail.php.txt b/scripts/scale8x-los-angeles-ca-us19-mail.php.txt
new file mode 100644
index 0000000..a6fb524
--- /dev/null
+++ b/scripts/scale8x-los-angeles-ca-us19-mail.php.txt
@@ -0,0 +1,125 @@
+#!/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("scale8x-los-angeles-ca-us19-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 = 100;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+// $locid = 715191; // Hamburg
+
+// LISA2009 Baltimore, 1.11.2009
+// $locid = 2138880; // Baltimore (Baltimore (city)), Maryland, United States
+// $city = "Baltimore, MD - Nov. 3rd 2009";
+
+// OpenSourceTreffen-Muenchen, 20.11.2009
+// $locid = 1260319; // Muenchen
+// $city = "Muenchen - 20. Nov 2009";
+
+// BLIT2009, Brandenburger Linux-Infotag, 21.11.2009
+// $locid = 1486658; // Potsdam
+// $eventname = "Brandenburger Linux-Infotag (BLIT2009)";
+// $city = "Potsdam - 21. Nov 2009";
+
+// ATE-Goteborg, 16.12.2009
+// $locid = 664715; // Goteborg, Vastra Gotaland, Sweden
+// $eventname = "ATE-Goteborg";
+// $city = "Goteborg - Dec 16th 2009";
+
+// Assurance Event Mission Hills CA, 15.01.2010
+// $locid = 2094781; // Mission Hills (Los Angeles), California, United States
+// $eventname = "Assurance Event";
+// $city = "Mission Hills CA - Jan 15th 2010";
+
+// Assurance Event OSD Copenhagen DK, 5.03.2010
+ $locid = 423655; // Copenhagen, Kobenhavn*, Denmark
+ $eventname = "Assurance Event OpenSource-Days 2010";
+ $city = "Copenhagen DK - March 5th/6th 2010";
+
+// SCALE 8x Los Angeles, CA, Feb 19-21 2010
+ $locid = 2093625; // Los Angeles, California, United States
+ $eventname = "SCALE 8x 2010";
+ $city = "Los Angeles, CA - February 19-21 2010";
+
+
+ $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/scanforexponents.php b/scripts/scanforexponents.php
new file mode 100755
index 0000000..7136723
--- /dev/null
+++ b/scripts/scanforexponents.php
@@ -0,0 +1,127 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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");
+
+ $fp = fopen("exp-report.txt", "w");
+
+ $d = dir("../crt/");
+ while (false !== ($entry = $d->read()))
+ {
+ if(substr($entry, 0, 3) == "gpg")
+ continue;
+ $file = "../crt/$entry";
+ if(!is_file($file))
+ continue;
+
+ if(substr($file, -3) == "der")
+ $do = trim(`openssl x509 -inform der -in $file -text -noout 2>&1 |grep 'Exponent'`);
+ else
+ $do = trim(`openssl x509 -in $file -text -noout 2>&1 |grep 'Exponent'`);
+
+ if($do == "")
+ continue;
+
+ list($crud, $exp, $crud) = explode(" ", $do);
+ if($exp >= 65537)
+ continue;
+
+ list($a, $crud) = explode(".", $entry, 2);
+ list($type, $id) = explode("-", $a);
+
+ $id = intval($id);
+
+ if($type == "client")
+ {
+ $query = "select `memid`,`serial`,`CN`,`subject`,`keytype`,`emailcerts`.`codesign` as `codesign`,`crt_name`,
+ `emailcerts`.`created` as `created`,`emailcerts`.`revoked` as `revoked`,
+ `emailcerts`.`expire` as `expire`, `rootcert`, `md`, `fname`, `lname`, `language`
+ from `emailcerts`,`users` where `emailcerts`.`id`='$id' and `users`.`id`=`emailcerts`.`memid`";
+ $res = mysql_query($query);
+ if(mysql_num_rows($res) <= 0)
+ {
+ echo $query."\n";
+ echo "$file: $do\n";
+ continue;
+ }
+
+ $row = mysql_fetch_assoc($res);
+ $email = $row['email'];
+ } else if($type == "orgclient") {
+ $query = "select `memid`,`serial`,`CN`,`subject`,`keytype`,`orgemailcerts`.`codesign` as `codesign`,`crt_name`,
+ `orgemailcerts`.`created` as `created`,`orgemailcerts`.`revoked` as `revoked`,
+ `orgemailcerts`.`expire` as `expire`, `rootcert`, `md`, `fname`, `lname`, `language`
+ from `orgemailcerts`,`org`,`users` where `orgemailcerts`.`id`='$id' and
+ `orgemailcerts`.`orgid`=`org`.`id` and `users`.`id`=`org`.`memid`";
+ $res = mysql_query($query);
+ if(mysql_num_rows($res) <= 0)
+ {
+ echo $query."\n";
+ echo "$file: $do\n";
+ continue;
+ }
+
+ $row = mysql_fetch_assoc($res);
+ $email = $row['email'];
+ } else if($type == "server") {
+ $query = "select `memid`,`serial`,`CN`,`subject`,`crt_name`,
+ `domaincerts`.`created` as `created`,`domaincerts`.`revoked` as `revoked`,
+ `domaincerts`.`expire` as `expire`, `rootcert`, `md`, `fname`, `lname`, `language`
+ from `domaincerts`,`domains`,`users` where `domaincerts`.`id`='$id' and
+ `domains`.`id`=`domaincerts`.`domid` and `users`.`id`=`domains`.`memid`";
+ $res = mysql_query($query);
+ if(mysql_num_rows($res) <= 0)
+ {
+ echo $query."\n";
+ echo "$file: $do\n";
+ continue;
+ }
+
+ $row = mysql_fetch_assoc($res);
+ $email = $row['email'];
+ } else if($type == "orgserver") {
+ $query = "select `memid`,`serial`,`CN`,`subject`,`crt_name`,
+ `orgdomaincerts`.`created` as `created`,`orgdomaincerts`.`revoked` as `revoked`,
+ `orgdomaincerts`.`expire` as `expire`, `rootcert`, `md`, `fname`, `lname`, `language`
+ from `orgdomaincerts`,`org`,`users` where `orgdomaincerts`.`id`='$id' and
+ `orgdomaincerts`.`orgid`=`org`.`id` and `users`.`id`=`org`.`memid`";
+ $res = mysql_query($query);
+ if(mysql_num_rows($res) <= 0)
+ {
+ echo $query."\n";
+ echo "$file: $do\n";
+ continue;
+ }
+
+ $row = mysql_fetch_assoc($res);
+ $email = $row['email'];
+ } else {
+ echo "$file: $do\n";
+ continue;
+ }
+
+ $body = "New Report:\n\n$do\n";
+
+ foreach($row as $key => $val)
+ $body .= "$key: $val\n";
+
+ $body .= "\n\n".file_get_contents($file);
+ fputs($fp, $body."\n\n===============================================================\n\n");
+ echo "$file: $do\n";
+ }
+?>
diff --git a/scripts/servercerts.php b/scripts/servercerts.php
new file mode 100755
index 0000000..44f7bac
--- /dev/null
+++ b/scripts/servercerts.php
@@ -0,0 +1 @@
+<? sleep(30); ?>
diff --git a/scripts/sfd-de12-email.txt b/scripts/sfd-de12-email.txt
new file mode 100644
index 0000000..7211e63
--- /dev/null
+++ b/scripts/sfd-de12-email.txt
@@ -0,0 +1,24 @@
+Software Freedom Day am 19. September in Hamburg
+::::::::::::::::::::::::::::::::::::::::::::::::
+
+Hallo CAcert'ler aus dem Raum Hamburg,
+
+sicherlich habt Ihr vom "Software Freedom Day (SFD)" am 19. September im
+Buergerhaus in Hamburg-Barmbeck gehoert!?
+Der SFD ist einer der groessten Veranstaltungen zu diesem Thema ueberhaupt.
+Auch wir, CAcert ist dabei. Wir wuerden uns freuen wenn wir Dich an
+unserem Stand begruessen duerfen um uns ueber die eine oder andere
+CAcert-Sache zu unterhalten.
+Aber auch die anderen Dinge sind für Dich auf dem SFD bestimmt
+interessant! Komme doch einfach am Samstag, den 19. September mal vorbei!
+Wir freuen uns auf Deinen Besuch!
+
+Der Link zur Veranstaltung: [http://www.lug-balista.de/sfd.html]
+
+Wir koennten auch noch einige Hilfe am Stand gebrauchen.
+Die Koordination findet ueber unsere Wiki Seite
+[http://wiki.cacert.org/wiki/events/SoftwareFreedomDayHamburg090919]
+statt. Wenn du mithelfen moechtest trage dich dort bitte als Assurer ein.
+Vielen Dank.
+
+Kontakt: events@cacert.org
diff --git a/scripts/sfd-de12-mail.php.txt b/scripts/sfd-de12-mail.php.txt
new file mode 100644
index 0000000..791d111
--- /dev/null
+++ b/scripts/sfd-de12-mail.php.txt
@@ -0,0 +1,94 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("sfd-de12-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 = 70;
+
+
+// location location.ID
+// verified: 29.4.09 u.schroeter
+// $locid = 7902857; // Paris
+// $locid = 238568; // Bielefeld
+// $locid = 715191; // Hamburg
+// $locid = 1102495; // London
+// $locid = 520340; // Duesseldorf
+// $locid = 1260319; // Muenchen
+// $locid = 606058; // Frankfurt
+// $locid = 1775784; // Stuttgart
+// $locid = 228950; // Berlin
+// $locid = 606058; // Frankfurt
+// $locid = 599389; // Flensburg
+// $locid = 61065; // Amsterdam, Eemnes
+// $locid = 228950; // Berlin
+
+// Software Freedom Day 19. Sept 2009
+ $locid = 715191; // Hamburg
+
+
+ $city = "Hamburg 19. Sep 2009";
+
+ $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] Software Freedom Day - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+ }
+ // 1x cc to events.cacert.org
+ sendmail("events@cacert.org", "[CAcert.org] Software Freedom Day - $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] Software Freedom Day - $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] Software Freedom Day - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+
+ echo "invitation sent to $xrows recipients.";
+?>
diff --git a/scripts/test.c b/scripts/test.c
new file mode 100644
index 0000000..00f1704
--- /dev/null
+++ b/scripts/test.c
@@ -0,0 +1,9 @@
+#include<stdio.h>
+
+int main(int argc, char *argv[])
+{
+ setuid(0);
+ setgid(0);
+
+ execv("whoami", argv);
+}
diff --git a/scripts/updatesort.php b/scripts/updatesort.php
new file mode 100755
index 0000000..4d36bfc
--- /dev/null
+++ b/scripts/updatesort.php
@@ -0,0 +1,77 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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");
+
+
+
+ //mysql_query("update users set assurer=0");
+ $query = "select notary.`to` as uid from notary group by notary.`to` having sum(points)>=100;";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "update users set `assurer`='1' where `id`='${row['uid']}'";
+ //echo $query."\n";
+ mysql_query($query);
+ }
+
+
+ mysql_query("update `locations` set `acount`=0");
+ $query = "SELECT `users`.`locid` AS `locid`, count(*) AS `total` FROM `users`
+ WHERE users.assurer='1' AND `users`.`locid` != 0 and users.listme=1
+ GROUP BY `users`.`locid`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "update `locations` set `acount`='${row['total']}' where `id`='${row['locid']}'";
+ echo $query."\n";
+ mysql_query($query);
+ }
+
+
+ mysql_query("update `regions` set `acount`=0");
+ $query = "SELECT `users`.`regid` AS `regid`, count(*) AS `total` FROM `users`
+ WHERE users.assurer='1' AND `users`.`regid` != 0 and users.listme=1
+ GROUP BY `users`.`regid`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "update `regions` set `acount`='${row['total']}' where `id`='${row['regid']}'";
+ echo $query."\n";
+ mysql_query($query);
+ }
+
+
+
+
+ mysql_query("update `countries` set `acount`=0");
+ $query = "SELECT `users`.`ccid` AS `ccid`, count(*) AS `total` FROM `users`
+ WHERE users.assurer='1' AND `users`.`ccid` != 0 and users.listme=1
+ GROUP BY `users`.`ccid`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ $query = "update `countries` set `acount`='${row['total']}' where `id`='${row['ccid']}'";
+ echo $query."\n";
+ mysql_query($query);
+ }
+
+
+
+
+?>
diff --git a/scripts/warning.php b/scripts/warning.php
new file mode 100755
index 0000000..b578c09
--- /dev/null
+++ b/scripts/warning.php
@@ -0,0 +1,100 @@
+#!/usr/bin/php -q
+<? /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2008 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("/home/cacert/www/includes/mysql.php");
+
+ $days = array("1" => "3", "15" => "2", "30" => "1", "45" => "0");
+
+ foreach($days as $day => $warning)
+ {
+ $query = "SELECT `emailcerts`.`id`,`users`.`fname`,`users`.`lname`,`users`.`email`,`emailcerts`.`memid`,
+ `emailcerts`.`subject`, `emailcerts`.`crt_name`,`emailcerts`.`CN`,
+ (UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 as `daysleft`
+ FROM `users`,`emailcerts`
+ WHERE UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 and
+ UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < $day * 86400 and
+ `emailcerts`.`renewed`=0 and `emailcerts`.`warning` <= '$warning' and
+ `emailcerts`.`revoked`=0 and `users`.`id`=`emailcerts`.`memid`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ if($row['subject'] == "")
+ {
+ $row['crt_name'] = str_replace("../", "www/", $row['crt_name']);
+ $row['crt_name'] = "/home/cacert/".$row['crt_name'];
+ $subject = `openssl x509 -in '$row[crt_name]' -text -noout|grep Subject:`;
+ $bits = explode("/", $subject);
+ foreach($bits as $val)
+ {
+ $sub = explode("=", trim($val));
+ if($sub['0'] == "emailAddress")
+ {
+ $row['subject'] = "/CN=".$row['CN']."/emailAddress=".$sub['1'];
+ break;
+ }
+ }
+ }
+ if($row['subject'] == "")
+ $row['subject'] = "/CN=".$row['CN'];
+ $row['daysleft'] = ceil($row['daysleft']);
+ $body = sprintf(_("Hi %s"), $row['fname']).",\n\n";
+ $body .= _("You are receiving this email as you are the listed contact for:")."\n\n";
+ $body .= $row['subject']."\n\n";
+ $body .= sprintf(_("Your certificate is set to expire in approximately %s days time, you can renew this by going to the following URL:"), $row['daysleft'])."\n\n";
+ $body .= "https://www.cacert.org/account.php?id=5\n\n";
+ $body .= _("Best Regards")."\n"._("CAcert Support");
+ sendmail($row['email'], "[CAcert.org] "._("Your Certificate is about to expire"), $body, "support@cacert.org", "", "", "CAcert Support");
+echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid']." Subj: ".$row['subject']." timeleft: ".$row['daysleft'].")\n";
+ $query = "update `emailcerts` set `warning`='".($warning+1)."' where `id`='".$row['id']."'";
+ mysql_query($query);
+ }
+ }
+
+ foreach($days as $day => $warning)
+ {
+ $query = "SELECT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`,
+ `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`,
+ `domaincerts`.`CN`,
+ (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft`
+ FROM `users`, `domaincerts`, `domlink`, `domains`
+ WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND
+ UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < $day * 86400 AND
+ `domaincerts`.`renewed`=0 AND `domaincerts`.`warning` <= '$warning' AND
+ `domaincerts`.`revoked`=0 AND `users`.`id` = `domains`.`memid` AND
+ `domlink`.`certid` = `domaincerts`.`id` AND `domains`.`id` = `domlink`.`domid`";
+ $res = mysql_query($query);
+ while($row = mysql_fetch_assoc($res))
+ {
+ if($row['subject'] == "")
+ $row['subject'] = $row['CN'];
+
+ $row['daysleft'] = ceil($row['daysleft']);
+ $body = sprintf(_("Hi %s"), $row['fname']).",\n\n";
+ $body .= _("You are receiving this email as you are the listed contact for:")."\n\n";
+ $body .= $row['subject']."\n\n";
+ $body .= sprintf(_("Your certificate is set to expire in approximately %s days time, you can renew this by going to the following URL:"), $row['daysleft'])."\n\n";
+ $body .= "https://www.cacert.org/account.php?id=12\n\n";
+ $body .= _("Best Regards")."\n"._("CAcert Support");
+ sendmail($row['email'], "[CAcert.org] "._("Your Certificate is about to expire"), $body, "support@cacert.org", "", "", "CAcert Support");
+echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid']." Subj: ".$row['CN']." timeleft: ".$row['daysleft'].")\n";
+ $query = "update `domaincerts` set `warning`='".($warning+1)."' where `id`='".$row['id']."'";
+ mysql_query($query);
+ }
+ }
+?>