From b35b874af560b4c69cf952229b28bd1668dcf5d9 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Michael=20T=C3=A4nzer?= Date: Tue, 3 Apr 2012 16:03:09 +0200 Subject: Source code taken from cacert-20120329.tar.bz2 --- pages/account/40.php | 10 +++ scripts/40de-ate-hamburg-email.txt | 66 ++++++++++++++++++++ scripts/40de-ate-hamburg-mail.php.txt | 112 ++++++++++++++++++++++++++++++++++ scripts/41de-ate-jena-email.txt | 33 ++++++++++ scripts/41de-ate-jena-mail.php.txt | 109 +++++++++++++++++++++++++++++++++ scripts/cron/permissionreview.php | 102 +++++++++++++++++++++++++++++++ scripts/cron/removedead.php | 85 ++++++++++++++++++++++++++ scripts/cron/updatesort.php | 111 +++++++++++++++++++++++++++++++++ scripts/cron/warning.php | 100 ++++++++++++++++++++++++++++++ scripts/removedead.php | 85 -------------------------- scripts/updatesort.php | 77 ----------------------- scripts/warning.php | 100 ------------------------------ 12 files changed, 728 insertions(+), 262 deletions(-) create mode 100644 scripts/40de-ate-hamburg-email.txt create mode 100644 scripts/40de-ate-hamburg-mail.php.txt create mode 100644 scripts/41de-ate-jena-email.txt create mode 100644 scripts/41de-ate-jena-mail.php.txt create mode 100755 scripts/cron/permissionreview.php create mode 100755 scripts/cron/removedead.php create mode 100755 scripts/cron/updatesort.php create mode 100755 scripts/cron/warning.php delete mode 100755 scripts/removedead.php delete mode 100755 scripts/updatesort.php delete mode 100755 scripts/warning.php diff --git a/pages/account/40.php b/pages/account/40.php index 8391903..499bf4d 100644 --- a/pages/account/40.php +++ b/pages/account/40.php @@ -19,6 +19,15 @@ if(!array_key_exists('secrethash',$_SESSION['_config'])) $_SESSION['_config']['s ?>

+

support@cacert.org" + ) ?> +

+ +

@@ -84,3 +93,4 @@ Australia

document.form2.secrethash2.value = pagehash; --> +*/ diff --git a/scripts/40de-ate-hamburg-email.txt b/scripts/40de-ate-hamburg-email.txt new file mode 100644 index 0000000..ddf7c0f --- /dev/null +++ b/scripts/40de-ate-hamburg-email.txt @@ -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 index 0000000..54ec1b8 --- /dev/null +++ b/scripts/40de-ate-hamburg-mail.php.txt @@ -0,0 +1,112 @@ +#!/usr/bin/php -q + diff --git a/scripts/41de-ate-jena-email.txt b/scripts/41de-ate-jena-email.txt new file mode 100644 index 0000000..f70dc85 --- /dev/null +++ b/scripts/41de-ate-jena-email.txt @@ -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 index 0000000..5691f63 --- /dev/null +++ b/scripts/41de-ate-jena-mail.php.txt @@ -0,0 +1,109 @@ +#!/usr/bin/php -q + diff --git a/scripts/cron/permissionreview.php b/scripts/cron/permissionreview.php new file mode 100755 index 0000000..572c1fd --- /dev/null +++ b/scripts/cron/permissionreview.php @@ -0,0 +1,102 @@ +#!/usr/bin/php -q + '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 = << $description) { + $message .= <<= 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 index 0000000..498eda2 --- /dev/null +++ b/scripts/cron/updatesort.php @@ -0,0 +1,111 @@ +#!/usr/bin/php -q +'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 index 0000000..18e89da --- /dev/null +++ b/scripts/cron/warning.php @@ -0,0 +1,100 @@ +#!/usr/bin/php -q + "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 index 23c4cd9..0000000 --- a/scripts/removedead.php +++ /dev/null @@ -1,85 +0,0 @@ -#!/usr/bin/php -q -= 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`= 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 index 4d36bfc..0000000 --- a/scripts/updatesort.php +++ /dev/null @@ -1,77 +0,0 @@ -#!/usr/bin/php -q -=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 index b578c09..0000000 --- a/scripts/warning.php +++ /dev/null @@ -1,100 +0,0 @@ -#!/usr/bin/php -q - "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); - } - } -?> -- cgit v1.2.1