Source code taken from cacert-20120105.tar.bz2
authorMichael Tänzer <neo@nhng.de>
Tue, 10 Jan 2012 11:20:17 +0000 (12:20 +0100)
committerMichael Tänzer <neo@nhng.de>
Tue, 10 Jan 2012 11:20:17 +0000 (12:20 +0100)
pages/account/43.php
scripts/39uk-ate-manchester-email.txt [new file with mode: 0644]
scripts/39uk-ate-manchester-mail.php.txt [new file with mode: 0644]

index f058770..7bf6d04 100644 (file)
@@ -24,10 +24,10 @@ include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php");
     $assurance = mysql_escape_string(intval($_REQUEST['assurance']));
     $row = 0;
     $res = mysql_query("select `to` from `notary` where `id`='$assurance'");
-    if ($res) { 
+    if ($res) {
       $row = mysql_fetch_assoc($res);
     }
-    mysql_query("delete from `notary` where `id`='$assurance'");    
+    mysql_query("delete from `notary` where `id`='$assurance'");
     if ($row) {
       fix_assurer_flag($row['to']);
     }
@@ -485,6 +485,304 @@ include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php");
  //  End - Debug infos
 ?>
 
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+       <tr>
+               <td colspan="6" class="title"><?=_("Certificates")?></td>
+       </tr>
+
+       <tr>
+               <td class="DataTD"><?=_("Cert Type")?>:</td>
+               <td class="DataTD"><?=_("Total")?></td>
+               <td class="DataTD"><?=_("Valid")?></td>
+               <td class="DataTD"><?=_("Expired")?></td>
+               <td class="DataTD"><?=_("Revoked")?></td>
+               <td class="DataTD"><?=_("Latest Expire")?></td>
+       </tr>
+
+       <tr>
+               <td class="DataTD"><?=_("Server")?>:</td>
+       <?
+       $query = "select COUNT(*) as `total`,
+                        MAX(`domaincerts`.`expire`) as `maxexpire`
+                 from `domains` inner join `domaincerts`
+                      on `domains`.`id` = `domaincerts`.`domid`
+                 where `domains`.`memid` = '".intval($row['id'])."' ";
+       $dres = mysql_query($query);
+       $drow = mysql_fetch_assoc($dres);
+       $total = $drow['total'];
+       
+       $maxexpire = "0000-00-00 00:00:00";
+       if ($drow['maxexpire']) {
+               $maxexpire = $drow['maxexpire'];
+       }
+       
+       if($total > 0) {
+               $query = "select COUNT(*) as `valid`
+                         from `domains` inner join `domaincerts`
+                              on `domains`.`id` = `domaincerts`.`domid`
+                         where `domains`.`memid` = '".intval($row['id'])."'
+                               and `revoked` = '0000-00-00 00:00:00'
+                               and `expire` > NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $valid = $drow['valid'];
+               
+               $query = "select COUNT(*) as `expired`
+                         from `domains` inner join `domaincerts`
+                              on `domains`.`id` = `domaincerts`.`domid`
+                         where `domains`.`memid` = '".intval($row['id'])."'
+                               and `expire` <= NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $expired = $drow['expired'];
+               
+               $query = "select COUNT(*) as `revoked`
+                         from `domains` inner join `domaincerts`
+                              on `domains`.`id` = `domaincerts`.`domid`
+                         where `domains`.`memid` = '".intval($row['id'])."'
+                               and `revoked` != '0000-00-00 00:00:00'";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $revoked = $drow['revoked'];
+               ?>
+               <td class="DataTD"><?=intval($total)?></td>
+               <td class="DataTD"><?=intval($valid)?></td>
+               <td class="DataTD"><?=intval($expired)?></td>
+               <td class="DataTD"><?=intval($revoked)?></td>
+               <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
+                       substr($maxexpire, 0, 10) : _("Pending")?></td>
+               <?
+       } else { // $total > 0
+               ?>
+               <td colspan="5" class="DataTD"><?=_("None")?></td>
+               <?
+       } ?>
+       </tr>
+
+       <tr>
+               <td class="DataTD"><?=_("Client")?>:</td>
+       <?
+       $query = "select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
+                 from `emailcerts`
+                 where `memid` = '".intval($row['id'])."' ";
+       $dres = mysql_query($query);
+       $drow = mysql_fetch_assoc($dres);
+       $total = $drow['total'];
+       
+       $maxexpire = "0000-00-00 00:00:00";
+       if ($drow['maxexpire']) {
+               $maxexpire = $drow['maxexpire'];
+       }
+       
+       if($total > 0) {
+               $query = "select COUNT(*) as `valid`
+                         from `emailcerts`
+                         where `memid` = '".intval($row['id'])."'
+                               and `revoked` = '0000-00-00 00:00:00'
+                               and `expire` > NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $valid = $drow['valid'];
+               
+               $query = "select COUNT(*) as `expired`
+                         from `emailcerts`
+                         where `memid` = '".intval($row['id'])."'
+                               and `expire` <= NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $expired = $drow['expired'];
+               
+               $query = "select COUNT(*) as `revoked`
+                         from `emailcerts`
+                         where `memid` = '".intval($row['id'])."'
+                               and `revoked` != '0000-00-00 00:00:00'";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $revoked = $drow['revoked'];
+               ?>
+               <td class="DataTD"><?=intval($total)?></td>
+               <td class="DataTD"><?=intval($valid)?></td>
+               <td class="DataTD"><?=intval($expired)?></td>
+               <td class="DataTD"><?=intval($revoked)?></td>
+               <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
+                       substr($maxexpire, 0, 10) : _("Pending")?></td>
+               <?
+       } else { // $total > 0
+               ?>
+               <td colspan="5" class="DataTD"><?=_("None")?></td>
+               <?
+       } ?>
+       </tr>
+
+       <tr>
+               <td class="DataTD"><?=_("GPG")?>:</td>
+       <?
+       $query = "select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
+                 from `gpg`
+                 where `memid` = '".intval($row['id'])."' ";
+       $dres = mysql_query($query);
+       $drow = mysql_fetch_assoc($dres);
+       $total = $drow['total'];
+       
+       $maxexpire = "0000-00-00 00:00:00";
+       if ($drow['maxexpire']) {
+               $maxexpire = $drow['maxexpire'];
+       }
+       
+       if($total > 0) {
+               $query = "select COUNT(*) as `valid`
+                         from `gpg`
+                         where `memid` = '".intval($row['id'])."'
+                               and `expire` > NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $valid = $drow['valid'];
+               
+               $query = "select COUNT(*) as `expired`
+                         from `emailcerts`
+                         where `memid` = '".intval($row['id'])."'
+                               and `expire` <= NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $expired = $drow['expired'];
+               
+               ?>
+               <td class="DataTD"><?=intval($total)?></td>
+               <td class="DataTD"><?=intval($valid)?></td>
+               <td class="DataTD"><?=intval($expired)?></td>
+               <td class="DataTD"></td>
+               <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
+                       substr($maxexpire, 0, 10) : _("Pending")?></td>
+               <?
+       } else { // $total > 0
+               ?>
+               <td colspan="5" class="DataTD"><?=_("None")?></td>
+               <?
+       } ?>
+       </tr>
+
+       <tr>
+               <td class="DataTD"><?=_("Org Server")?>:</td>
+       <?
+       $query = "select COUNT(*) as `total`,
+                        MAX(`orgcerts`.`expire`) as `maxexpire`
+                 from `orgdomaincerts` as `orgcerts` inner join `org`
+                          on `orgcerts`.`orgid` = `org`.`orgid`
+                 where `org`.`memid` = '".intval($row['id'])."' ";
+       $dres = mysql_query($query);
+       $drow = mysql_fetch_assoc($dres);
+       $total = $drow['total'];
+       
+       $maxexpire = "0000-00-00 00:00:00";
+       if ($drow['maxexpire']) {
+               $maxexpire = $drow['maxexpire'];
+       }
+       
+       if($total > 0) {
+               $query = "select COUNT(*) as `valid`
+                         from `orgdomaincerts` as `orgcerts` inner join `org`
+                                  on `orgcerts`.`orgid` = `org`.`orgid`
+                         where `org`.`memid` = '".intval($row['id'])."'
+                               and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
+                               and `orgcerts`.`expire` > NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $valid = $drow['valid'];
+               
+               $query = "select COUNT(*) as `expired`
+                         from `orgdomaincerts` as `orgcerts` inner join `org`
+                                  on `orgcerts`.`orgid` = `org`.`orgid`
+                         where `org`.`memid` = '".intval($row['id'])."'
+                               and `orgcerts`.`expire` <= NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $expired = $drow['expired'];
+               
+               $query = "select COUNT(*) as `revoked`
+                         from `orgdomaincerts` as `orgcerts` inner join `org`
+                                  on `orgcerts`.`orgid` = `org`.`orgid`
+                         where `org`.`memid` = '".intval($row['id'])."'
+                               and `orgcerts`.`revoked` != '0000-00-00 00:00:00'";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $revoked = $drow['revoked'];
+               ?>
+               <td class="DataTD"><?=intval($total)?></td>
+               <td class="DataTD"><?=intval($valid)?></td>
+               <td class="DataTD"><?=intval($expired)?></td>
+               <td class="DataTD"><?=intval($revoked)?></td>
+               <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
+                       substr($maxexpire, 0, 10) : _("Pending")?></td>
+               <?
+       } else { // $total > 0
+               ?>
+               <td colspan="5" class="DataTD"><?=_("None")?></td>
+               <?
+       } ?>
+       </tr>
+
+       <tr>
+               <td class="DataTD"><?=_("Org Client")?>:</td>
+       <?
+       $query = "select COUNT(*) as `total`,
+                        MAX(`orgcerts`.`expire`) as `maxexpire`
+                 from `orgemailcerts` as `orgcerts` inner join `org`
+                          on `orgcerts`.`orgid` = `org`.`orgid`
+                 where `org`.`memid` = '".intval($row['id'])."' ";
+       $dres = mysql_query($query);
+       $drow = mysql_fetch_assoc($dres);
+       $total = $drow['total'];
+       
+       $maxexpire = "0000-00-00 00:00:00";
+       if ($drow['maxexpire']) {
+               $maxexpire = $drow['maxexpire'];
+       }
+       
+       if($total > 0) {
+               $query = "select COUNT(*) as `valid`
+                         from `orgemailcerts` as `orgcerts` inner join `org`
+                                  on `orgcerts`.`orgid` = `org`.`orgid`
+                         where `org`.`memid` = '".intval($row['id'])."'
+                               and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
+                               and `orgcerts`.`expire` > NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $valid = $drow['valid'];
+               
+               $query = "select COUNT(*) as `expired`
+                         from `orgemailcerts` as `orgcerts` inner join `org`
+                                  on `orgcerts`.`orgid` = `org`.`orgid`
+                         where `org`.`memid` = '".intval($row['id'])."'
+                               and `orgcerts`.`expire` <= NOW()";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $expired = $drow['expired'];
+               
+               $query = "select COUNT(*) as `revoked`
+                         from `orgemailcerts` as `orgcerts` inner join `org`
+                                  on `orgcerts`.`orgid` = `org`.`orgid`
+                         where `org`.`memid` = '".intval($row['id'])."'
+                               and `orgcerts`.`revoked` != '0000-00-00 00:00:00'";
+               $dres = mysql_query($query);
+               $drow = mysql_fetch_assoc($dres);
+               $revoked = $drow['revoked'];
+               ?>
+               <td class="DataTD"><?=intval($total)?></td>
+               <td class="DataTD"><?=intval($valid)?></td>
+               <td class="DataTD"><?=intval($expired)?></td>
+               <td class="DataTD"><?=intval($revoked)?></td>
+               <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
+                       substr($maxexpire, 0, 10) : _("Pending")?></td>
+               <?
+       } else { // $total > 0
+               ?>
+               <td colspan="5" class="DataTD"><?=_("None")?></td>
+               <?
+       } ?>
+       </tr>
+</table>
+<br>
+
 <a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto"><?=_("Show Assurances the user got")?></a>
  (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto15"><?=_("New calculation")?></a>)
 <br />
diff --git a/scripts/39uk-ate-manchester-email.txt b/scripts/39uk-ate-manchester-email.txt
new file mode 100644 (file)
index 0000000..6c709bf
--- /dev/null
@@ -0,0 +1,40 @@
+CAcert Assurer Training Event -- Crewe/Manchester, UK
+:::::::::::::::::::::::::::::::::::::::::::::::::::::
+
+Dear Member of the CAcert Community,
+
+Much has happened during recent 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 Assurer, and the Community:
+
+- What is the difference in Thawte notary and CAcert Assurance?
+- What do you have to add onto the CAP form if you assure minors?
+- What are the 2 essential CCA points you have to present an Assuree?
+- Who can access the Member's privacy information?
+
+Answers to these and many other questions typically faced by Assurers are given at the Assurer Training Events (ATEs).  Bring your ID for assurances.  Especially note that Tverify/Thawte people need to boost up their Assurance Points.
+
+ATE-Manchester takes place at:
+*  Saturday, January 14th, 2012
+*  The Venue
+*  Belong Village
+*  Brookhouse Drive
+*  Crewe
+*  Cheshire CW2 6NA
+*  11:30am - 3pm
+
+The venue is near to and easily accessible from the train station (Crewe is a major rail junction within 3-4 hours of most of the major cities!)
+
+For Registration please reply: 'I will attend ATE-Manchester'
+
+Don't forget your ID!
+
+We are looking forward to hearing from you.
+
+
+- Best regards from the Event Team!
+
+
+PS: Contact: events@cacert.org
+Location, Transportation and other event details at
+[https://wiki.cacert.org/Events/20120114ATE-Manchester]
diff --git a/scripts/39uk-ate-manchester-mail.php.txt b/scripts/39uk-ate-manchester-mail.php.txt
new file mode 100644 (file)
index 0000000..5c5d0d9
--- /dev/null
@@ -0,0 +1,108 @@
+#!/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("39uk-ate-manchester-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 = 350;
+
+
+// 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";
+
+
+       $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";
+
+?>