Merge branch 'bug-922' into release
authorBenny Baumann <BenBE@geshi.org>
Wed, 31 Oct 2012 17:33:53 +0000 (18:33 +0100)
committerBenny Baumann <BenBE@geshi.org>
Wed, 31 Oct 2012 17:33:53 +0000 (18:33 +0100)
includes/account.php
scripts/cron/warning.php

index f14b593..ee5d3f8 100644 (file)
                                {
                                        $row = mysql_fetch_assoc($res);
                                        echo $row['domain']."<br>\n";
-                                       mysql_query("update `domains` set `deleted`=NOW() where `id`='$id'");
-                                       $dres = mysql_query("select * from `domlink` where `domid`='$id'");
+                                       
+                                       $dres = mysql_query(
+                                               "select distinct `domaincerts`.`id`
+                                                       from `domaincerts`, `domlink`
+                                                       where `domaincerts`.`domid` = '$id'
+                                                       or (
+                                                               `domaincerts`.`id` = `domlink`.`certid`
+                                                               and `domlink`.`domid` = '$id'
+                                                               )");
                                        while($drow = mysql_fetch_assoc($dres))
-                                               mysql_query("update `domaincerts` set `revoked`='1970-01-01 10:00:01' where `id`='".$drow['certid']."' and `revoked`=0 and UNIX_TIMESTAMP(`expire`)-UNIX_TIMESTAMP() > 0");
+                                       {
+                                               mysql_query(
+                                                       "update `domaincerts`
+                                                               set `revoked`='1970-01-01 10:00:01'
+                                                               where `id` = '".$drow['id']."'
+                                                               and `revoked` = 0
+                                                               and UNIX_TIMESTAMP(`expire`) -
+                                                                               UNIX_TIMESTAMP() > 0");
+                                       }
+                                       
+                                       mysql_query(
+                                               "update `domains`
+                                                       set `deleted`=NOW()
+                                                       where `id` = '$id'");
                                }
                        }
                }
index 18e89da..5cf7c31 100755 (executable)
@@ -24,7 +24,7 @@
        foreach($days as $day => $warning)
        {
                $query = "SELECT `emailcerts`.`id`,`users`.`fname`,`users`.`lname`,`users`.`email`,`emailcerts`.`memid`,
-                               `emailcerts`.`subject`, `emailcerts`.`crt_name`,`emailcerts`.`CN`,
+                               `emailcerts`.`subject`, `emailcerts`.`crt_name`,`emailcerts`.`CN`, `emailcerts`.`serial`,
                                (UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 as `daysleft`
                                FROM `users`,`emailcerts`
                                WHERE UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 and
                        $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 .= sprintf(_("Your certificate with the serial number %s is ".
+                                               "set to expire in approximately %s days time. You can ".
+                                               "renew it by going to the following URL:"),
+                                       $row['serial'],
+                                       $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");
@@ -68,16 +72,32 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid
 
        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`
+               $query = 
+                       "SELECT DISTINCT `domaincerts`.`id`,
+                                       `users`.`fname`, `users`.`lname`, `users`.`email`,
+                                       `domains`.`memid`,
+                                       `domaincerts`.`subject`, `domaincerts`.`crt_name`,
+                                       `domaincerts`.`CN`,
+                                       `domaincerts`.`serial`,
+                                       (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`";
+                               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 (
+                                       `domaincerts`.`domid` = `domains`.`id`
+                                       OR (
+                                               `domaincerts`.`id` = `domlink`.`certid`
+                                               AND `domlink`.`domid` = `domains`.`id`
+                                               )
+                                       )
+                               AND `domains`.`memid` = `users`.`id`";
                $res = mysql_query($query);
                while($row = mysql_fetch_assoc($res))
                {
@@ -88,7 +108,11 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid
                        $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 .= sprintf(_("Your certificate with the serial number %s is ".
+                                               "set to expire in approximately %s days time. You can ".
+                                               "renew it by going to the following URL:"),
+                                       $row['serial'],
+                                       $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");