Source code taken from cacert-20120329.tar.bz2
authorMichael Tänzer <neo@nhng.de>
Tue, 3 Apr 2012 14:03:09 +0000 (16:03 +0200)
committerMichael Tänzer <neo@nhng.de>
Tue, 3 Apr 2012 14:03:09 +0000 (16:03 +0200)
12 files changed:
pages/account/40.php
scripts/40de-ate-hamburg-email.txt [new file with mode: 0644]
scripts/40de-ate-hamburg-mail.php.txt [new file with mode: 0644]
scripts/41de-ate-jena-email.txt [new file with mode: 0644]
scripts/41de-ate-jena-mail.php.txt [new file with mode: 0644]
scripts/cron/permissionreview.php [new file with mode: 0755]
scripts/cron/removedead.php [new file with mode: 0755]
scripts/cron/updatesort.php [new file with mode: 0755]
scripts/cron/warning.php [new file with mode: 0755]
scripts/removedead.php [deleted file]
scripts/updatesort.php [deleted file]
scripts/warning.php [deleted file]

index 8391903..499bf4d 100644 (file)
@@ -19,6 +19,15 @@ if(!array_key_exists('secrethash',$_SESSION['_config'])) $_SESSION['_config']['s
 ?>
 <H3><?=_("Contact Us")?></H3>
 
+<p><? printf(_("To contact us please log out and then use the contact form ".
+               "there or send us an email to %s. We are working to fix this ".
+               "situation so you may contact us while staying logged in again."),
+               
+               "<a href='mailto:support@cacert.org'>support@cacert.org</a>"
+       ) ?>
+</p>
+
+<?/*
 <p><b><?=_("General Questions")?></b></p>
 <p><b><?=_("PLEASE NOTE: Due to the large amounts of support questions, incorrectly directed emails may be over looked, this is a volunteer effort and directing general questions to the right place will help everyone, including yourself as you will get a reply quicker.")?></b></p>
 <p><b><?=_("If you are contacting us about advertising, please use the form at the bottom of the website, the first contact form is not the correct place.")?></b></p>
@@ -84,3 +93,4 @@ Australia</p>
        document.form2.secrethash2.value = pagehash;
 -->
 </script>
+*/
diff --git a/scripts/40de-ate-hamburg-email.txt b/scripts/40de-ate-hamburg-email.txt
new file mode 100644 (file)
index 0000000..ddf7c0f
--- /dev/null
@@ -0,0 +1,66 @@
+CAcert Assurer Training Event Hamburg [Deutsch]
+::::::::::::::::::::::::::::::::::::::::::::::::::
+
+Es hat sich viel getan im letzten Jahr. Eine ganze Reihe von bisher eher "muendlich ueberlieferten" Regeln wurden in Policies gegossen. Neue Prozeduren (z.B. die Assurer Challenge) und Verpflichtungen (z.B. in dem CAcert Community Agreement) wurden beschlossen. Die Assurer Training Events wollen versuchen, die ganzen Informationen unter’s Volk zu bringen:
+
+- Was hast du auf dem CAP Formular hinzuzufuegen, wenn du Minderjaehrige ueberpruefst ?
+- Was sind die 2 wesentlichen Punkte der CCA die du einem Assuree vermitteln koennen sollst ?
+- Unter welchen Umstaenden koennen z.Bsp. niederlaendische Rufnamen akzeptiert werden? 
+
+Antworten auf diese und weitere Fragen erhaelst du bei den Assurer Training Events (ATEs).
+
+Die kommende Veranstaltung in deiner Naehe findet statt am:
+
+Dienstag den 14. Februar 2012, 19:00 - 22:00
+
+Attraktor e.V.
+Mexikoring 21
+22297 Hamburg
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im
+Wiki [https://wiki.cacert.org/events/20120214-ATE-Hamburg-3]
+Blog [http://blog.cacert.org/2012/02/547.html]
+
+
+
+Unverbindliche Anmeldung und Registrierung:
+Rueckantwort mit 'Ich moechte teilnehmen: ATE-Hamburg'
+
+Kontakt: events@cacert.org
+
+
+CAcert Assurer Training Event Hamburg [English]
+::::::::::::::::::::::::::::::::::::::::::::::::::
+
+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:
+
+- What you have to add onto the CAP form if you assure U18 people ?
+- What are the 2 essential topics regarding CCA you have to present an Assuree ?
+- When you can accept i.e. a Dutch "roepnaam" ?
+
+Answers to these and many other questions are given at the Assurer 
+Training Events (ATEs).
+
+ATE-Hamburg takes place on:
+Tuesday, Feb 14, 2012, 19:00 - 22:00
+
+Attraktor e.V.
+Mexikoring 21
+22297 Hamburg
+
+The Event-Team is looking forward to hearing from you.
+
+Details on Location and Transportation you will find under
+Wiki [https://wiki.cacert.org/events/20120214-ATE-Hamburg-3]
+Blog [http://blog.cacert.org/2012/02/547.html]
+
+Registration for ATE-Hamburg: please reply
+'I will attend: ATE-Hamburg'
+
+Contact: events@cacert.org
diff --git a/scripts/40de-ate-hamburg-mail.php.txt b/scripts/40de-ate-hamburg-mail.php.txt
new file mode 100644 (file)
index 0000000..54ec1b8
--- /dev/null
@@ -0,0 +1,112 @@
+#!/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("40de-ate-hamburg-email.txt", "r");
+       while(!feof($fp))
+       {
+               $line = trim(fgets($fp, 4096));
+               $lines .= wordwrap($line, 75, "\n")."\n";
+       }
+       fclose($fp);
+
+
+//     $locid = intval($_REQUEST['location']);
+//     $maxdist = intval($_REQUEST['maxdist']);
+//  maxdist in [Km] - changed to 350 to include glasgow + london
+  $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  
+//     $locid = 2138880;      // Baltimore (Baltimore (city)), Maryland, US
+//       $locid = 1486658;       // Potsdam
+//       $locid = 664715;       // Goteborg, Vastra Gotaland, Sweden
+//       $locid = 2094781;       // Mission Hills (Los Angeles), California, US
+//       $locid = 423655;       // Copenhagen, Kobenhavn*, Denmark 
+//       $locid = 2257312;       // Sydney, New South Wales, Australia
+//       $locid = 572764;       //  Essen, Nordrhein-Westfalen, Germany 
+//       $locid = 78;       //  Aachen, Nordrhein-Westfalen, Germany 
+//       $locid = 266635;       // Bonn, Nordrhein-Westfalen, Germany
+//       $locid = 2102723;       // Washington (District of Columbia, ..., US
+//       $locid = 2177566;       // New York (Bronx), New York, United States
+//       $locid = 1486658;       // Potsdam  BLIT2011
+//       $locid = 2237836;  // Blacksburg (Montgomery), Virginia, United States
+
+// ATE Manchester 2012
+//       $locid = 1161640;  // Manchester, Manchester, United Kingdom
+//       $eventname = "ATE-Manchester";
+//       $city = "Manchester/Crewe UK";
+
+// ATE Hamburg, DE, Feb 14 2012 
+       $locid = 715191;        // Hamburg
+       $eventname = "ATE-Hamburg";
+       $city = "Feb 14, 2012";
+
+       $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/41de-ate-jena-email.txt b/scripts/41de-ate-jena-email.txt
new file mode 100644 (file)
index 0000000..f70dc85
--- /dev/null
@@ -0,0 +1,33 @@
+CAcert Assurer Training Event Jena [Deutsch]
+::::::::::::::::::::::::::::::::::::::::::::
+
+Es hat sich viel getan im letzten Jahr. Eine ganze Reihe von bisher eher "muendlich ueberlieferten" Regeln wurden in Policies gegossen. Neue Prozeduren (z.B. die Assurer Challenge) und Verpflichtungen (z.B. in dem CAcert Community Agreement) wurden beschlossen. Die Assurer Training Events wollen versuchen, die ganzen Informationen unter’s Volk zu bringen:
+
+- Was hast du auf dem CAP Formular hinzuzufuegen, wenn du Minderjaehrige ueberpruefst ?
+- Warum solltest du dir R/L/O einpraegen ?
+- Wie verhaelst du dich, wenn du ein fremdes Ausweis Dokument das erste mal pruefst ?
+
+Antworten auf diese und weitere Fragen erhaelst du bei den Assurer Training Events (ATEs).
+
+Die kommende Veranstaltung in deiner Naehe findet statt am:
+
+Donnerstag den 29. Maerz 2012, 19:00 - 22:00
+
+Adresse: Am Johannisfriedhof 2, Jena
+Gebaeude: Universitaetsrechenzentrum der Friedrich-Schiller-Universitaet Jena
+OSM: [http://osm.org/go/0MAe9XhNp-]
+Webseite: [http://www.uni-jena.de/URZ.html]
+
+
+Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme.
+
+Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im
+Wiki [https://wiki.cacert.org/Events/2012-03-29ATE-Jena]
+Blog [http://blog.cacert.org/2012/03/550.html]
+
+
+
+Unverbindliche Anmeldung und Registrierung:
+Rueckantwort mit 'Ich moechte teilnehmen: ATE-Jena'
+
+Kontakt: events@cacert.org
diff --git a/scripts/41de-ate-jena-mail.php.txt b/scripts/41de-ate-jena-mail.php.txt
new file mode 100644 (file)
index 0000000..5691f63
--- /dev/null
@@ -0,0 +1,109 @@
+#!/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("41de-ate-jena-email.txt", "r");
+       while(!feof($fp))
+       {
+               $line = trim(fgets($fp, 4096));
+               $lines .= wordwrap($line, 75, "\n")."\n";
+       }
+       fclose($fp);
+
+
+//     $locid = intval($_REQUEST['location']);
+//     $maxdist = intval($_REQUEST['maxdist']);
+//  maxdist in [Km] - changed to 350 to include glasgow + london
+  $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  
+//     $locid = 2138880;      // Baltimore (Baltimore (city)), Maryland, US
+//       $locid = 1486658;       // Potsdam
+//       $locid = 664715;       // Goteborg, Vastra Gotaland, Sweden
+//       $locid = 2094781;       // Mission Hills (Los Angeles), California, US
+//       $locid = 423655;       // Copenhagen, Kobenhavn*, Denmark 
+//       $locid = 2257312;       // Sydney, New South Wales, Australia
+//       $locid = 572764;       //  Essen, Nordrhein-Westfalen, Germany 
+//       $locid = 78;       //  Aachen, Nordrhein-Westfalen, Germany 
+//       $locid = 266635;       // Bonn, Nordrhein-Westfalen, Germany
+//       $locid = 2102723;       // Washington (District of Columbia, ..., US
+//       $locid = 2177566;       // New York (Bronx), New York, United States
+//       $locid = 1486658;       // Potsdam  BLIT2011
+//       $locid = 2237836;  // Blacksburg (Montgomery), Virginia, United States
+//       $locid = 1161640;  // Manchester, Manchester, United Kingdom
+
+// ATE Jena, DE, Mar 29 2012 
+       $locid = 803095;        // Jena, Thueringen, Germany
+       $eventname = "ATE-Jena";
+       $city = "Mar 29, 2012";
+
+
+       $query = "select * from `locations` where `id`='$locid'";
+        $loc = mysql_fetch_assoc(mysql_query($query));
+
+       $query = "SELECT ROUND(6378.137 * ACOS(0.9999999*((SIN(PI() * $loc[lat] / 180) * SIN(PI() * `locations`.`lat` / 180)) + 
+                       (COS(PI() * $loc[lat] / 180 ) * COS(PI() * `locations`.`lat` / 180) * 
+                        COS(PI() * `locations`.`long` / 180 - PI() * $loc[long] / 180)))), -1) AS `distance`, sum(`points`) as pts, `users`.* 
+                       FROM `locations`
+                               inner join `users`  on `users`.`locid` = `locations`.`id` 
+                               inner join `alerts` on `users`.`id`=`alerts`.`memid`
+                               inner join `notary` on `users`.`id`=`notary`.`to`
+                       WHERE   (`alerts`.`general`=1 OR `alerts`.`country`=1 OR `alerts`.`regional`=1 OR `alerts`.`radius`=1)
+                       GROUP BY `users`.`id`
+                       HAVING `distance` <= '$maxdist' 
+                       ORDER BY `distance` ";
+        echo $query;
+
+    // comment next line when starting to send mail not only to me 
+    // $query = "select * from `users` where `email` like 'cacerttest%'";
+
+       $res = mysql_query($query);
+       $xrows = mysql_num_rows($res);
+
+       while($row = mysql_fetch_assoc($res))
+       {
+    //    uncomment next line to send mails ...
+               sendmail($row['email'], "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+       }
+  // 1x cc to events.cacert.org
+       sendmail("events@cacert.org", "[CAcert.org] $eventname - $city", $lines, "events@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);
+       // 1x mailing report to events.cacert.org
+  sendmail("events@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);       
+
+       // 1x mailing report to Arbitrator of case http://wiki.cacert.org/wiki/Arbitrations/a20090525.1
+  sendmail("p.dunkel@cacert.org", "[CAcert.org] $eventname - $city Report", "invitation sent to $xrows recipients.", "support@cacert.org", "", "", "CAcert Events Organisation", "returns@cacert.org", 1);     
+       echo "invitation sent to $xrows recipients.\n";
+
+?>
diff --git a/scripts/cron/permissionreview.php b/scripts/cron/permissionreview.php
new file mode 100755 (executable)
index 0000000..572c1fd
--- /dev/null
@@ -0,0 +1,102 @@
+#!/usr/bin/php -q
+<?php
+/*
+LibreSSL - CAcert web application
+Copyright (C) 2004-2012  CAcert Inc.
+
+This program is free software; you can redistribute it and/or modify
+it under the terms of the GNU General Public License as published by
+the Free Software Foundation; version 2 of the License.
+
+This program is distributed in the hope that it will be useful,
+but WITHOUT ANY WARRANTY; without even the implied warranty of
+MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+GNU General Public License for more details.
+
+You should have received a copy of the GNU General Public License
+along with this program; if not, write to the Free Software
+Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
+*/
+
+require_once(dirname(__FILE__).'/../../includes/mysql.php');
+
+$BOARD_PRIVATE = 'cacert-board-private@lists.cacert.org';
+
+$flags = array(
+       'admin' => 'Support Engineer',
+       'orgadmin' => 'Organisation Assurer',
+       'board' => 'Board Member',
+       'ttpadmin' => 'Trusted Third Party Admin',
+       'tverify' => 'Tverify Admin',
+       'locadmin' => 'Location Admin'
+       );
+
+$adminlist = array();
+
+foreach ($flags as $flag => $description) {
+       $query = "select `fname`, `lname`, `email` from `users` where `$flag` = 1";
+       if(! $res = mysql_query($query) ) {
+               fwrite(STDERR,
+                               "MySQL query for flag $flag failed:\n".
+                               "\"$query\"\n".
+                               mysql_error()
+                       );
+               
+               continue;
+       }
+       
+       $admins = array();
+       $adminlist[$flag] = "";
+       
+       while ($row = mysql_fetch_assoc($res)) {
+               $admins[] = $row;
+               $adminlist[$flag] .= "$row[fname] $row[lname] $row[email]\n";
+       }
+       
+       foreach ($admins as $admin) {
+               $message = <<<EOF
+Hello $admin[fname],
+
+you get this message, because you are listed as $description on
+CAcert.org. Please review the following list of persons with the same privilege
+and report to the responsible team leader or board
+($BOARD_PRIVATE) if you spot any errors.
+
+$adminlist[$flag]
+
+
+Best Regards,
+CAcert Support
+EOF;
+               sendmail($admin['email'], "Permissions Review", $message, 'support@cacert.org');
+       }
+}
+
+
+
+$message = <<<EOF
+Dear Board Members,
+
+it's time for the permission review again. Here is the list of privileged users
+in the CAcert web application. Please review them and also ask the persons 
+responsible for an up-to-date copy of access lists not directly recorded in the
+web application (critical admins, software assessors etc.) 
+
+
+EOF;
+
+foreach ($flags as $flag => $description) {
+       $message .= <<<EOF
+List of ${description}s:
+$adminlist[$flag]
+
+EOF;
+}
+
+$message .= <<<EOF
+
+Best Regards,
+CAcert Support
+EOF;
+
+sendmail($BOARD_PRIVATE, "Permissions Review", $message, 'support@cacert.org');
diff --git a/scripts/cron/removedead.php b/scripts/cron/removedead.php
new file mode 100755 (executable)
index 0000000..aadda81
--- /dev/null
@@ -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
+*/
+
+       require_once(dirname(__FILE__).'/../../includes/mysql.php');
+       require_once(dirname(__FILE__).'/../../includes/lib/l10n.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'] != "")
+                {
+                        L10n::set_translation($data['language']);
+                }
+
+                $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/cron/updatesort.php b/scripts/cron/updatesort.php
new file mode 100755 (executable)
index 0000000..498eda2
--- /dev/null
@@ -0,0 +1,111 @@
+#!/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
+*/
+       require_once(dirname(__FILE__).'/../../includes/mysql.php');
+
+
+
+       /* Set assurer flag for accounts who miss it
+
+          See also includes/lib/account.php, function fix_assurer_flag($userID)
+
+          We may have some performance problems here, there are 150k assurances and 220k users
+          in the production database. The exists-clause on cats_passed should be a good filter... */
+          
+  /* Synchronisation of assurer flag currently deactivated, see https://bugs.cacert.org/view.php?id=1003
+     and https://bugs.cacert.org/view.php?id=1024 */
+/*
+       $query = "select `n`.`to` as `uid` from `notary` as `n`, `users` as `u` ".
+                "  where `n`.`to`=`u`.`id` and `u`.`assurer`<>'1' ".
+                "    and (`n`.`expire` > now() OR `n`.`expire` IS NULL) ".
+                "    and exists(select 1 from `cats_passed` as `cp`, `cats_variant` as `cv` ".
+                "                 where `cp`.`variant_id`=`cv`.`id` and `cv`.`type_id` = 1 and `cp`.`user_id`=`n`.`to`)".
+                "  group by `n`.`to` having sum(`n`.`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);
+       }
+*/
+       /* Remove assurer flag from accounts not eligible.
+
+          Also a bit performance critical, but assurer flag is only set at 5k accounts
+
+       */
+  /* Synchronisation of assurer flag currently deactivated, see https://bugs.cacert.org/view.php?id=1003
+     and https://bugs.cacert.org/view.php?id=1024 */
+/*
+    $query = "select `u`.id as `uid` from `users` as `u` " .
+                "  where `u`.`assurer` = '1' ".
+                "    and (not exists(select 1 from `cats_passed` as `cp`, `cats_variant` as `cv` ".
+                "                     where `cp`.`variant_id`=`cv`.`id` and `cv`.`type_id` = 1 and `cp`.`user_id`=`u`.`id`) ".
+                "         or (select sum(`n`.`points`) from `notary` as `n` where `n`.`to`=`u`.`id` and (`n`.`expire` > now() OR `n`.`expire` IS NULL)) < 100) ";
+       $res = mysql_query($query);
+       while($row = mysql_fetch_assoc($res))
+       {
+               $query = "update users set `assurer`='0' 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/cron/warning.php b/scripts/cron/warning.php
new file mode 100755 (executable)
index 0000000..18e89da
--- /dev/null
@@ -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
+*/
+
+       require_once(dirname(__FILE__).'/../../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);
+               }
+       }
+?>
diff --git a/scripts/removedead.php b/scripts/removedead.php
deleted file mode 100755 (executable)
index 23c4cd9..0000000
+++ /dev/null
@@ -1,85 +0,0 @@
-#!/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");
-       require_once('/home/cacert/www/includes/lib/l10n.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'] != "")
-                {
-                        L10n::set_translation($data['language']);
-                }
-
-                $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/updatesort.php b/scripts/updatesort.php
deleted file mode 100755 (executable)
index 4d36bfc..0000000
+++ /dev/null
@@ -1,77 +0,0 @@
-#!/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
deleted file mode 100755 (executable)
index b578c09..0000000
+++ /dev/null
@@ -1,100 +0,0 @@
-#!/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);
-               }
-       }
-?>