From b0904b2bda37b292fa5d29d5d3b7ec64d100ad16 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Michael=20T=C3=A4nzer?= Date: Thu, 4 Dec 2014 18:26:23 +0100 Subject: Source code taken from cacert-20141124.tar.bz2 --- scripts/54at-ate-linz-email.txt | 91 ++++++++++++ scripts/54at-ate-linz-mail.php.txt | 140 ++++++++++++++++++ scripts/55de-ate-wiesbaden-email.txt | 46 ++++++ scripts/55de-ate-wiesbaden-mail.php.txt | 122 ++++++++++++++++ scripts/56at-ate-oberwart-email.txt | 93 ++++++++++++ scripts/56at-ate-oberwart-mail.php.txt | 147 +++++++++++++++++++ scripts/57at-ate-graz-email.txt | 91 ++++++++++++ scripts/57at-ate-graz-mail.php.txt | 130 +++++++++++++++++ scripts/58at-ate-wien-email.txt | 91 ++++++++++++ scripts/58at-ate-wien-mail.php.txt | 134 +++++++++++++++++ scripts/cron/refresh_stats.php | 55 +++---- scripts/cron/warning.php | 8 +- scripts/db_migrations/version5.sh | 249 ++++++++++++++++++++++++++++++++ scripts/send_policy_cca_20140916.php | 137 ++++++++++++++++++ 14 files changed, 1505 insertions(+), 29 deletions(-) create mode 100644 scripts/54at-ate-linz-email.txt create mode 100644 scripts/54at-ate-linz-mail.php.txt create mode 100644 scripts/55de-ate-wiesbaden-email.txt create mode 100644 scripts/55de-ate-wiesbaden-mail.php.txt create mode 100644 scripts/56at-ate-oberwart-email.txt create mode 100644 scripts/56at-ate-oberwart-mail.php.txt create mode 100644 scripts/57at-ate-graz-email.txt create mode 100644 scripts/57at-ate-graz-mail.php.txt create mode 100644 scripts/58at-ate-wien-email.txt create mode 100644 scripts/58at-ate-wien-mail.php.txt create mode 100755 scripts/db_migrations/version5.sh create mode 100644 scripts/send_policy_cca_20140916.php (limited to 'scripts') diff --git a/scripts/54at-ate-linz-email.txt b/scripts/54at-ate-linz-email.txt new file mode 100644 index 0000000..1e9020e --- /dev/null +++ b/scripts/54at-ate-linz-email.txt @@ -0,0 +1,91 @@ +[Deutsch] + +Es hat sich viel getan in den letzten Jahren. 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: + +- Welcher Satz fehlt auf alten CAP Formularen? +- Warum soll ich mir R/L/O einpraegen? +- Wie verhaelst du dich, + wenn du ein fremdes Ausweisdokument das erste Mal pruefst? + +Antworten auf diese und weitere Fragen erhaelst du bei den +Assurer Training Events (ATEs). + +Darueberhinaus wird beim ATE der Vorgang der Identitaetsueberpruefung +trainiert und auditiert, um die Qualitaet der Assurances in der +taeglichen Praxis zu erfassen. Dabei gilt es moegliche Fehler und +Fallstricke zu erkennen und aufzudecken. Die Assurer haben also die +Moeglichkeit, sich mit den Fehlern auseinanderzusetzen und zu erfahren, +wie diese vermieden werden koennen. + +Wie IanG sagte: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers, and include parts which contribute +directly to our audit. Come and find out how you can also contribute. + +Die kommende Veranstaltung in deiner Naehe findet statt am: + +- Freitag, den 16. Mai 2014 +- in der Zeit von: 19:00 - ca. 22:00 Uhr +- Fachhochschule Oberoesterreich, Hauptgebaeude, 1.Stock, Raum SR A-103 +- Garnisonstrasse 21 +- 4020 Linz + + +Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im +Wiki [https://wiki.cacert.org/Events/2014-05-16-ATELinz] + + +Teilnehmer Registrierung mit Rueckantwort: + 'Ich moechte am ATE-Linz teilnehmen' + +Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme. + +Kontakt: events@cacert.org + + + +[English] + +During the last years many changes took place inside CAcert. Many "oral" +rules have been put into Policies. New procedures +(e.g. Assurer Challenge) and obligations +(e.g. CAcert Community Agreement) have been put into live. +The Assurer Training Events (ATE) try to spread this information: + +- What is missing on the "old" CAP forms? +- Why should I remember R/L/O? +- What can you do if an Assuree shows an ID document unknown to you? + +These and more questions will be answered during the +Assurer Training Events (ATEs) + +Furthermore, the ATE trains how to do assurances and audits assurances, +to measure the quality of assurances in the daily routine. Here are some +possible errors and pitfalls which need to be found. Assurers have the +opportunity to see those errors and how to avoid them. + +As IanG said: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers and includes parts which contribute +directly to our audit. Come and find out how you can also contribute. + +The next event held in your area will be: + +- Friday, May 16th 2014 +- during: 19:00 - ca. 22:00 +- University of Applied Sciences Upper Austria, Room SR A-103 +- Garnisonstrasse 21 +- 4020 Linz + + +Details to the location can be found: +Wiki [https://wiki.cacert.org/Events/2014-05-16-ATELinz] + +User reply for registration: 'I will attend the ATE-Linz' + +The event team is looking forward for your attendance: + +Contact: events@cacert.org diff --git a/scripts/54at-ate-linz-mail.php.txt b/scripts/54at-ate-linz-mail.php.txt new file mode 100644 index 0000000..5ffdb24 --- /dev/null +++ b/scripts/54at-ate-linz-mail.php.txt @@ -0,0 +1,140 @@ +#!/usr/bin/php -q + diff --git a/scripts/55de-ate-wiesbaden-email.txt b/scripts/55de-ate-wiesbaden-email.txt new file mode 100644 index 0000000..4880388 --- /dev/null +++ b/scripts/55de-ate-wiesbaden-email.txt @@ -0,0 +1,46 @@ +Es hat sich viel getan in den letzten Jahren. 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: + +- Welcher Satz fehlt auf alten CAP Formularen? +- Warum soll ich mir R/L/O einpraegen? +- Wie verhaelst du dich, + wenn du ein fremdes Ausweisdokument das erste Mal pruefst? + +Antworten auf diese und weitere Fragen erhaelst du bei den +Assurer Training Events (ATEs). + +Darueberhinaus wird beim ATE der Vorgang der Identitaetsueberpruefung +trainiert und auditiert, um die Qualitaet der Assurances in der +taeglichen Praxis zu erfassen. Dabei gilt es moegliche Fehler und +Fallstricke zu erkennen und aufzudecken. Die Assurer haben also die +Moeglichkeit, sich mit den Fehlern auseinanderzusetzen und zu erfahren, +wie diese vermieden werden koennen. + +Wie IanG sagte: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers, and include parts which contribute +directly to our audit. Come and find out how you can also contribute. + +Die kommende Veranstaltung in deiner Naehe findet statt am: + +- Donnerstag, den 22. Mai 2014 +- in der Zeit von: 19:00 - ca. 22:00 Uhr +- CCCMZ e.V. +- Sedanplatz 7 +- 65183 Wiesbaden + + + +Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im Wiki +[https://wiki.cacert.org/Events/2014-05-22ATE-Wiesbaden] und Blog +[https://www.cccmz.de/cacert-assurer-training-event-22-mai-2014-in-wiesbaden] + +Teilnehmer Registrierung mit Rueckantwort: + 'Ich moechte am ATE-Wiesbaden teilnehmen' + +Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme. + +Kontakt: events@cacert.org diff --git a/scripts/55de-ate-wiesbaden-mail.php.txt b/scripts/55de-ate-wiesbaden-mail.php.txt new file mode 100644 index 0000000..26666e4 --- /dev/null +++ b/scripts/55de-ate-wiesbaden-mail.php.txt @@ -0,0 +1,122 @@ +#!/usr/bin/php -q + diff --git a/scripts/56at-ate-oberwart-email.txt b/scripts/56at-ate-oberwart-email.txt new file mode 100644 index 0000000..b15f82e --- /dev/null +++ b/scripts/56at-ate-oberwart-email.txt @@ -0,0 +1,93 @@ +[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: + +- Welcher Satz fehlt auf alten CAP Formularen? +- Warum soll ich mir R/L/O einpraegen? +- Wie verhaelst du dich, + wenn du ein fremdes Ausweisdokument das ersteMal pruefst? + +Antworten auf diese und weitere Fragen erhaelst du bei den +Assurer Training Events (ATEs). + +Darueberhinaus wird beim ATE der Vorgang der Identitaetsueberpruefung +trainiert und auditiert, um die Qualitaet der Assurances in der +taeglichen Praxis zu erfassen. Dabei gilt es moegliche Fehler und +Fallstricke zu erkennen und aufzudecken. Die Assurer haben also die +Moeglichkeit, sich mit den Fehlern auseinanderzusetzen und zu erfahren, +wie diese vermieden werden koennen. + +Wie IanG sagte: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers, and include parts which contribute +directly to our audit. Come and find out how you can also contribute. + +Die kommende Veranstaltung in deiner Naehe findet statt am: + +- Freitag, den 27. Juni 2014 +- in der Zeit von: 19:00 - ca. 22:00 Uhr +- Rotes Kreuz Schulungszentrum Süd (Hotel zur Pinka), Seminarraum "Pinkasaal" +- Grazerstrasse 71 +- A-7400 Oberwart + +Die Arbeitssprache der Veranstaltung ist Deutsch + +Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im +Wiki [https://wiki.cacert.org/Events/2014-06-27-ATEOberwart] + + +Teilnehmer Registrierung mit Rueckantwort: + 'Ich moechte am ATE-Oberwart teilnehmen' + +Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme. + +Kontakt: events@cacert.org + + + +[English] + +During the last year many changes took place inside CAcert. Many "oral" +rules have been put into Policies. New procedures +(e.g. Assurer Challenge) and obligations +(e.g. CAcert Community Agreement) have been put into live. +The Assurer Training Events (ATE) try to spread this information: + +- What is missing on the "old" CAP forms? +- Why should I remember R/L/O? +- What can you do if an Assuree shows an ID document unknown to you? + +These and more questions will be answered during the +Assurer Training Events (ATEs) + +Furthermore, the ATE trains how to do assurances and audits assurances, +to measure the quality of assurances in the daily routine. Here are some +possible errors and pitfalls which need to be found. Assurers have the +opportunity to see those errors and how to avoid them. + +As IanG said: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers and includes parts which contribute +directly to our audit. Come and find out how you can also contribute. + +The next event held in your area will be: + +- Friday, May 16th 2014 +- during: 19:00 - ca. 22:00 +- Rotes Kreuz Schulungszentrum Süd (Hotel zur Pinka), Seminarraum "Pinkasaal" +- Grazerstrasse 71 +- A-7400 Oberwart + +The working language of this event is GERMAN + +Details to the location can be found: +Wiki [https://wiki.cacert.org/Events/2014-06-27-ATEOberwart] + +User reply for registration: 'I will attend the ATE-Oberwart' + +The event team is looking forward for your attendance: + +Contact: events@cacert.org diff --git a/scripts/56at-ate-oberwart-mail.php.txt b/scripts/56at-ate-oberwart-mail.php.txt new file mode 100644 index 0000000..1035f17 --- /dev/null +++ b/scripts/56at-ate-oberwart-mail.php.txt @@ -0,0 +1,147 @@ +#!/usr/bin/php -q + diff --git a/scripts/57at-ate-graz-email.txt b/scripts/57at-ate-graz-email.txt new file mode 100644 index 0000000..e9b4a63 --- /dev/null +++ b/scripts/57at-ate-graz-email.txt @@ -0,0 +1,91 @@ +[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: + +- Welcher Satz fehlt auf alten CAP Formularen? +- Warum soll ich mir R/L/O einpraegen? +- Wie verhaelst du dich, + wenn du ein fremdes Ausweisdokument das erste Mal pruefst? + +Antworten auf diese und weitere Fragen erhaelst du bei den +Assurer Training Events (ATEs). + +Darueberhinaus wird beim ATE der Vorgang der Identitaetsueberpruefung +trainiert und auditiert, um die Qualitaet der Assurances in der +taeglichen Praxis zu erfassen. Dabei gilt es moegliche Fehler und +Fallstricke zu erkennen und aufzudecken. Die Assurer haben also die +Moeglichkeit, sich mit den Fehlern auseinanderzusetzen und zu erfahren, +wie diese vermieden werden koennen. + +Wie IanG sagte: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers, and include parts which contribute +directly to our audit. Come and find out how you can also contribute. + +Die kommende Veranstaltung in deiner Naehe findet statt am: + +- Donnerstag, den 13. November 2014 +- in der Zeit von: 19:00 - ca. 22:00 Uhr +- realraum +- Brockmanngasse 15 +- 8010 Graz + + +Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im +Wiki [http://wiki.cacert.org/Events/2014-11-13-ATEGraz] +Blog [http://blog.cacert.org/2014/10/ate-graz-2014-11-13/] + +Teilnehmer Registrierung mit Rueckantwort: + 'Ich moechte am ATE-Graz teilnehmen' + +Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme. + +Kontakt: events@cacert.org + + + +[English] + +During the last year many changes took place inside CAcert. Many "oral" +rules have been put into Policies. New procedures +(e.g. Assurer Challenge) and obligations +(e.g. CAcert Community Agreement) have been put into live. +The Assurer Training Events (ATE) try to spread this information: + +- What is missing on the "old" CAP forms? +- Why should I remember R/L/O? +- What can you do if an Assuree shows an ID document unknown to you? + +These and more questions will be answered during the +Assurer Training Events (ATEs) + +Furthermore, the ATE trains how to do assurances and audits assurances, +to measure the quality of assurances in the daily routine. Here are some +possible errors and pitfalls which need to be found. Assurers have the +opportunity to see those errors and how to avoid them. + +As IanG said: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers and includes parts which contribute +directly to our audit. Come and find out how you can also contribute. + +The next event held in your area will be: + +- Thursday, November 13th 2014 +- during: 19:00 - ca. 22:00 +- realraum +- Brockmanngasse 15 +- 8010 Graz + +Details to the location can be found: +Wiki [http://wiki.cacert.org/Events/2014-11-13-ATEGraz] +Blog [http://blog.cacert.org/2014/10/ate-graz-2014-11-13/] + +User reply for registration: 'I will attend the ATE-Graz' + +The event team is looking forward for your attendance: + +Contact: events@cacert.org diff --git a/scripts/57at-ate-graz-mail.php.txt b/scripts/57at-ate-graz-mail.php.txt new file mode 100644 index 0000000..0e6786f --- /dev/null +++ b/scripts/57at-ate-graz-mail.php.txt @@ -0,0 +1,130 @@ +#!/usr/bin/php -q + diff --git a/scripts/58at-ate-wien-email.txt b/scripts/58at-ate-wien-email.txt new file mode 100644 index 0000000..4e55b56 --- /dev/null +++ b/scripts/58at-ate-wien-email.txt @@ -0,0 +1,91 @@ +[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: + +- Welcher Satz fehlt auf alten CAP Formularen? +- Warum soll ich mir R/L/O einpraegen? +- Wie verhaelst du dich, + wenn du ein fremdes Ausweisdokument das erste Mal pruefst? + +Antworten auf diese und weitere Fragen erhaelst du bei den +Assurer Training Events (ATEs). + +Darueberhinaus wird beim ATE der Vorgang der Identitaetsueberpruefung +trainiert und auditiert, um die Qualitaet der Assurances in der +taeglichen Praxis zu erfassen. Dabei gilt es moegliche Fehler und +Fallstricke zu erkennen und aufzudecken. Die Assurer haben also die +Moeglichkeit, sich mit den Fehlern auseinanderzusetzen und zu erfahren, +wie diese vermieden werden koennen. + +Wie IanG sagte: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers, and include parts which contribute +directly to our audit. Come and find out how you can also contribute. + +Die kommende Veranstaltung in deiner Naehe findet statt am: + +- Mittwoch, den 19. November 2014 +- in der Zeit von: 19:00 - ca. 22:00 Uhr +- Metalab Wien +- Rathausstrasse 6 +- 1010 Wien + + +Details zum Veranstaltungsort und Anfahrthinweise findet Ihr im +Wiki [http://wiki.cacert.org/Events/2014-11-19-ATEWien] +Blog [http://blog.cacert.org/2014/10/ate-wien-2014-11-19/] + +Teilnehmer Registrierung mit Rueckantwort: + 'Ich moechte am ATE-Wien teilnehmen' + +Das Veranstaltungs-Team freut sich schon auf Eure Teilnahme. + +Kontakt: events@cacert.org + + + +[English] + +During the last year many changes took place inside CAcert. Many "oral" +rules have been put into Policies. New procedures +(e.g. Assurer Challenge) and obligations +(e.g. CAcert Community Agreement) have been put into live. +The Assurer Training Events (ATE) try to spread this information: + +- What is missing on the "old" CAP forms? +- Why should I remember R/L/O? +- What can you do if an Assuree shows an ID document unknown to you? + +These and more questions will be answered during the +Assurer Training Events (ATEs) + +Furthermore, the ATE trains how to do assurances and audits assurances, +to measure the quality of assurances in the daily routine. Here are some +possible errors and pitfalls which need to be found. Assurers have the +opportunity to see those errors and how to avoid them. + +As IanG said: The ATE or Assurer Training Event is exceptionally +recommended for all Assurers and includes parts which contribute +directly to our audit. Come and find out how you can also contribute. + +The next event held in your area will be: + +- Wednesday, November 19th 2014 +- during: 19:00 - ca. 22:00 +- Metalab Vienna +- Rathausstrasse 6 +- 1010 Vienna + +Details to the location can be found: +Wiki [http://wiki.cacert.org/Events/2014-11-19-ATEWien] +Blog [http://blog.cacert.org/2014/10/ate-wien-2014-11-19/] + +User reply for registration: 'I will attend the ATE-Vienna' + +The event team is looking forward for your attendance: + +Contact: events@cacert.org diff --git a/scripts/58at-ate-wien-mail.php.txt b/scripts/58at-ate-wien-mail.php.txt new file mode 100644 index 0000000..fe95455 --- /dev/null +++ b/scripts/58at-ate-wien-mail.php.txt @@ -0,0 +1,134 @@ +#!/usr/bin/php -q + diff --git a/scripts/cron/refresh_stats.php b/scripts/cron/refresh_stats.php index 2a3d2b5..3b446ba 100755 --- a/scripts/cron/refresh_stats.php +++ b/scripts/cron/refresh_stats.php @@ -23,7 +23,7 @@ require_once(dirname(__FILE__).'/../../includes/mysql.php'); /** * Wrapper around mysql_query() to provide some error handling. Prints an error * message and dies if query fails - * + * * @param string $sql * the SQL statement to execute * @return resource|boolean @@ -35,7 +35,7 @@ function sql_query($sql) { fwrite(STDERR, "MySQL query failed:\n\"$sql\"\n".mysql_error()); die(1); } - + return $res; } @@ -54,7 +54,7 @@ function updateCache($stats) { $sql = "insert into `statscache` (`timestamp`, `cache`) values ('$timestamp', '".mysql_real_escape_string(serialize($stats))."')"; sql_query($sql); - + // Make sure the new statistic was inserted successfully $res = sql_query( "select 1 from `statscache` where `timestamp` = '$timestamp'"); @@ -62,7 +62,7 @@ function updateCache($stats) { fwrite(STDERR, "Error on inserting the new statistic"); return false; } - + sql_query("delete from `statscache` where `timestamp` != '$timestamp'"); return true; } @@ -74,22 +74,22 @@ function updateCache($stats) { */ function getDataFromLive() { echo "Calculating current statistics\n"; - + $stats = array(); $stats['verified_users'] = number_format(tc( "select count(*) as `count` from `users` where `verified` = 1 and `deleted` = 0 and `locked` = 0")); - + $stats['verified_emails'] = number_format(tc( "select count(*) as `count` from `email` where `hash` = '' and `deleted` = 0")); - + $stats['verified_domains'] = number_format(tc( "select count(*) as `count` from `domains` where `hash` = '' and `deleted` = 0")); - + $certs = tc("select count(*) as `count` from `domaincerts` where `expire` != 0"); $certs += tc("select count(*) as `count` from `emailcerts` @@ -101,7 +101,7 @@ function getDataFromLive() { $certs += tc("select count(*) as `count` from `orgemailcerts` where `expire` != 0"); $stats['verified_certificates'] = number_format($certs); - + $certs = tc("select count(*) as `count` from `domaincerts` where `revoked` = 0 and `expire` > NOW()"); $certs += tc("select count(*) as `count` from `emailcerts` @@ -113,11 +113,12 @@ function getDataFromLive() { $certs += tc("select count(*) as `count` from `orgemailcerts` where `revoked` = 0 and `expire` > NOW()"); $stats['valid_certificates'] = number_format($certs); - + $stats['assurances_made'] = number_format(tc( "select count(*) as `count` from `notary` - where `method` = '' or `method` = 'Face to Face Meeting'")); - + where (`method` = '' or `method` = 'Face to Face Meeting') + and `deleted` = 0")); + $stats['users_1to49'] = number_format(tc( "select count(*) as `count` from ( select 1 from `notary` @@ -125,7 +126,7 @@ function getDataFromLive() { group by `to` having sum(`points`) > 0 and sum(`points`) < 50 ) as `low_points`")); - + $stats['users_50to99'] = number_format(tc( "select count(*) as `count` from ( select 1 from `notary` @@ -133,7 +134,7 @@ function getDataFromLive() { group by `to` having sum(`points`) >= 50 and sum(`points`) < 100 ) as `high_points`")); - + $stats['assurer_candidates'] = number_format(tc( "select count(*) as `count` from `users` where ( @@ -148,7 +149,7 @@ function getDataFromLive() { and `cv`.`type_id`=1 )" )); - + $stats['aussurers_with_test'] = number_format(tc( "select count(*) as `count` from `users` where ( @@ -163,7 +164,7 @@ function getDataFromLive() { and `cv`.`type_id`=1 )" )); - + $stats['points_issued'] = number_format(tc( "select sum(greatest(`points`, `awarded`)) as `count` from `notary` where `deleted` = 0 @@ -177,16 +178,16 @@ function getDataFromLive() { $next_month_ts = mktime(0, 0, 0, date("m") - $i + 1, 1, date("Y")); $first = date("Y-m-d", $first_ts); $next_month = date("Y-m-d", $next_month_ts); - + echo "Calculating statistics for month $first\n"; - + $totalusers += $users = tc( - "select count(*) as `count` from `users` + "select count(*) as `count` from `users` where `created` >= '$first' and `created` < '$next_month' and `verified` = 1 and `deleted` = 0 and `locked` = 0"); - + $totassurers += $assurers = tc( "select count(*) as `count` from ( select 1 from `notary` @@ -195,7 +196,7 @@ function getDataFromLive() { and `deleted` = 0 group by `to` having sum(`points`) >= 100 ) as `assurer_candidates`"); - + $certs = tc( "select count(*) as `count` from `domaincerts` where `created` >= '$first' and `created` < '$next_month' @@ -240,16 +241,16 @@ function getDataFromLive() { $next_year_ts = mktime(0, 0, 0, 1, 1, $i + 1); $first = date("Y-m-d", $first_ts); $next_year = date("Y-m-d", $next_year_ts); - + echo "Calculating statistics for year $i\n"; - + $totalusers += $users = tc( - "select count(*) as `count` from `users` + "select count(*) as `count` from `users` where `created` >= '$first' and `created` < '$next_year' and `verified` = 1 and `deleted` = 0 and `locked` = 0"); - + $totassurers += $assurers = tc( "select count(*) as `count` from ( select 1 from `notary` @@ -258,7 +259,7 @@ function getDataFromLive() { and `deleted` = 0 group by `to` having sum(`points`) >= 100 ) as `assurer_candidates`"); - + $certs = tc( "select count(*) as `count` from `domaincerts` where `created` >= '$first' and `created` < '$next_year' @@ -286,7 +287,7 @@ function getDataFromLive() { $tmp_arr['new_users'] = number_format($users); $tmp_arr['new_assurers'] = number_format($assurers); $tmp_arr['new_certificates'] = number_format($certs); - + $stats['growth_last_years'][] = $tmp_arr; } $stats['growth_last_years_total'] = array( diff --git a/scripts/cron/warning.php b/scripts/cron/warning.php index 8f607cd..59b1aa2 100755 --- a/scripts/cron/warning.php +++ b/scripts/cron/warning.php @@ -1,5 +1,5 @@ #!/usr/bin/php -q - "3", "15" => "2", "30" => "1", "45" => "0"); @@ -34,12 +36,13 @@ $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { + L10n::set_recipient_language(intval($row['id'])); if($row['subject'] == "") { $row['crt_name'] = str_replace("../", "www/", $row['crt_name']); $row['crt_name'] = "/home/cacert/".$row['crt_name']; $crt_name = escapeshellarg($row['crt_name']); - $subject = `openssl x509 -in $crt_name -text -noout|grep Subject:`; + $subject = runCommand("openssl x509 -in $crt_name -text -noout|grep Subject:"); $bits = explode("/", $subject); foreach($bits as $val) { @@ -107,6 +110,7 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { + L10n::set_recipient_language(intval($row['memid'])); if($row['subject'] == "") $row['subject'] = $row['CN']; diff --git a/scripts/db_migrations/version5.sh b/scripts/db_migrations/version5.sh new file mode 100755 index 0000000..f18f8c6 --- /dev/null +++ b/scripts/db_migrations/version5.sh @@ -0,0 +1,249 @@ +#!/bin/sh +# LibreSSL - CAcert web application +# Copyright (C) 2004-2011 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 + + + +# script to do database migrations + +set -e # script fails if any command fails + +STDIN=0 +STDOUT=1 +STDERR=2 + +if [ "$1" = "--help" ]; then + cat >&$STDERR <<- USAGE + Usage: $0 [MYSQL_OPTIONS] + You have to specify all options needed by "mysql" as if you had started + the MySQL command line client directly (including the name of the + database to operate on). The MySQL user used has to have enough + privileges to do all necessary operations (among others CREATE, ALTER, + DROP, UPDATE, INSERT, DELETE). + You might need to enter the mysql password multiple times if you + specify the -p option. + USAGE + exit 1 +fi + +mysql_opt=" --batch --skip-column-names $@" + +schema_version=$( mysql $mysql_opt <<- 'SQL' + + SELECT MAX(`version`) FROM `schema_version`; +SQL +) +if [ $schema_version != 4 ]; then + cat >&$STDERR <<- ERROR + Error: database schema is not in the right version to do the migration! + Expected version: 4 + ERROR + exit 2 +fi + +mysql $mysql_opt <<- 'SQL' + +-- Move myISAM to InnoDB bug #1172 + +ALTER TABLE `abusereports` ENGINE=INNODB; +system echo "table abusereports altered to InnoDB" + + +ALTER TABLE `addlang` ENGINE=INNODB; +system echo "table addlang altered to InnoDB" + + +ALTER TABLE `adminlog` ENGINE=INNODB; +system echo "table adminlog altered to InnoDB" + + +ALTER TABLE `advertising` ENGINE=INNODB; +system echo "table advertising altered to InnoDB" + + +ALTER TABLE `alerts` ENGINE=INNODB; +system echo "table alerts altered to InnoDB" + + +ALTER TABLE `baddomains` ENGINE=INNODB; +system echo "table baddomains altered to InnoDB" + + +ALTER TABLE `cats_passed` ENGINE=INNODB; +system echo "table cats_passed altered to InnoDB" + + +ALTER TABLE `cats_type` ENGINE=INNODB; +system echo "table cats_type altered to InnoDB" + + +ALTER TABLE `cats_variant` ENGINE=INNODB; +system echo "table cats_variant altered to InnoDB" + + +ALTER TABLE `countries` ENGINE=INNODB; +system echo "table countries altered to InnoDB" + + +ALTER TABLE `disputedomain` ENGINE=INNODB; +system echo "table disputedomain altered to InnoDB" + + +ALTER TABLE `disputeemail` ENGINE=INNODB; +system echo "table disputeemail altered to InnoDB" + +ALTER TABLE `domaincerts` ENGINE=INNODB; +system echo "table domainderts altered to InnoDB" + + +ALTER TABLE `domains` ENGINE=INNODB; +system echo "table domains altered to InnoDB" + + +ALTER TABLE `domlink` ENGINE=INNODB; +system echo "table domlink altered to InnoDB" + + +ALTER TABLE `email` ENGINE=INNODB; +system echo "table email altered to InnoDB" + + +ALTER TABLE `emailcerts` ENGINE=INNODB; +system echo "table emailcerts altered to InnoDB" + + +ALTER TABLE `emaillink` ENGINE=INNODB; +system echo "table emaillink altered to InnoDB" + + +ALTER TABLE `gpg` ENGINE=INNODB; +system echo "table gpg altered to InnoDB" + + +ALTER TABLE `languages` ENGINE=INNODB; +system echo "table languages altered to InnoDB" + + +ALTER TABLE `localias` ENGINE=INNODB; +system echo "table localias altered to InnoDB" + + +ALTER TABLE `locations` ENGINE=INNODB; +system echo "table locations altered to InnoDB" + + +ALTER TABLE `news` ENGINE=INNODB; +system echo "table news altered to InnoDB" + + +ALTER TABLE `notary` ENGINE=INNODB; +system echo "table notary altered to InnoDB" + + +ALTER TABLE `org` ENGINE=INNODB; +system echo "table org altered to InnoDB" + + +ALTER TABLE `orgadminlog` ENGINE=INNODB; +system echo "table orgadminlog altered to InnoDB" + + +ALTER TABLE `orgdomaincerts` ENGINE=INNODB; +system echo "table orgdomaincerts altered to InnoDB" + + +ALTER TABLE `orgdomains` ENGINE=INNODB; +system echo "table orgdomains altered to InnoDB" + + +ALTER TABLE `orgdomlink` ENGINE=INNODB; +system echo "table orgdomlink altered to InnoDB" + + +ALTER TABLE `orgemailcerts` ENGINE=INNODB; +system echo "table orgemailcerts altered to InnoDB" + + +ALTER TABLE `orgemaillink` ENGINE=INNODB; +system echo "table orgemaillink altered to InnoDB" + + +ALTER TABLE `orginfo` ENGINE=INNODB; +system echo "table orginfo altered to InnoDB" + + +ALTER TABLE `otphashes` ENGINE=INNODB; +system echo "table otphashes altered to InnoDB" + + +ALTER TABLE `pinglog` ENGINE=INNODB; +system echo "table pinglog altered to InnoDB" + + +ALTER TABLE `regions` ENGINE=INNODB; +system echo "table regions altered to InnoDB" + + +ALTER TABLE `root_certs` ENGINE=INNODB; +system echo "table root_certs altered to InnoDB" + + +ALTER TABLE `schema_version` ENGINE=INNODB; +system echo "table schema_version altered to InnoDB" + + +ALTER TABLE `stampcache` ENGINE=INNODB; +system echo "table stampcache altered to InnoDB" + + +ALTER TABLE `statscache` ENGINE=INNODB; +system echo "table statscache altered to InnoDB" + + +ALTER TABLE `tickets` ENGINE=INNODB; +system echo "table tickets altered to InnoDB" + + +ALTER TABLE `tverify` ENGINE=INNODB; +system echo "table tverify altered to InnoDB" + + +ALTER TABLE `tverify-vote` ENGINE=INNODB; +system echo "table tverify-vote altered to InnoDB" + + +ALTER TABLE `user_agreements` ENGINE=INNODB; +system echo "table user_agreements altered to InnoDB" + + +ALTER TABLE `userlocations` ENGINE=INNODB; +system echo "table userlocations altered to InnoDB" + + +ALTER TABLE `users` ENGINE=INNODB; +system echo "table users altered to InnoDB" + + + -- Update schema version number + INSERT INTO `schema_version` + (`version`, `when`) VALUES + ('5' , NOW() ); +SQL + + +echo "Database successfully migrated to version 5" +exit 0 + diff --git a/scripts/send_policy_cca_20140916.php b/scripts/send_policy_cca_20140916.php new file mode 100644 index 0000000..6de5207 --- /dev/null +++ b/scripts/send_policy_cca_20140916.php @@ -0,0 +1,137 @@ +#!/usr/bin/php -q + COD1) + * Configuration-Control Specification ("CCS" => COD2) + * Certification Practice Statement ("CPS" => COD6) + * Dispute Resolution Policy ("DRP" => COD7) + * Security Policy ("SP" => COD8) + * Organisation Assurance Policy ("OAP" => COD11) + * Root Distribution License ("RDL" => COD14) + * Organisation Assurance Subsidary Policy - Germany (COD11.DE) + * Organisation Assurance Subsidary Policy - Europe (COD11.EU) + * Organisation Assurance Subsidary Policy - Australia (COD11.AU) + * TTP-Assisted Assurance Policy ("TTP-Assist" => COD13.2) + +We are working hard to update all the documents so that they show their policy status. As the content has not been changed and will remain the same, we decided to inform you about this even though we have not finished, yet. + +You can find the above polices at: +https://svn.cacert.org/CAcert/Policies/ControlledDocumentList.html + +As most of those polices have not been reviewed for a while, you probably will see some more updates to CAcert policies coming soon. Those changes will be an important step to be able to pass an audit, which is one of our current goals. + +Every CAcert member interested in participating in the design of our policies, is invited to join our policy group mailing list at: +https://lists.cacert.org/wws/info/cacert-policy + +Major changes for the CCA: + * The CCA was changed to clearly be a general terms and conditions what makes it easier to join and exit as CAcert member. For CAcert it was obvious to do the change, because all CAcert members sign the same conditions without the possibility to strike or add personal clauses. + * More ways to accept the CCA were added. + * Termination of membership was clarified some more. Some other options beside the ruling of an Arbitrator were cautiously added. + * You have a new obligation to answer in arbitration cases. This seems to be obvious, but you never signed it before. In the past this was derived from some points within our Dispute Resolution Policy (DRP). + * Sharing of accounts and credentials was banned more clearly. Also the obligation to only use a certificate in the appropriate contexts was added. It was already part of the Certification Practice Statement (CPS). + * Some kinds of contributions as personal data are now excepted from the non-exclusive non-restrictive non-revocable transfer of licence to CAcert. + * Official communication with CAcert was simplified. + * Some deprecated references were removed. + +A version with all changes can be found at: +https://svn.cacert.org/CAcert/Policies/CAcertCommunityAgreement_20140708.html + +Sincerely, +Eva Stöwe +CAcert Policy Officer +EOF; + +$lines_EN = wordwrap($lines_EN, 75, "\n"); +$lines_EN = mb_convert_encoding($lines_EN, "HTML-ENTITIES", "UTF-8"); + + +// read last used id +$lastid = 0; +if (file_exists("send_policy_cca20140915_lastid.txt")) +{ + $fp = fopen("send_policy_cca20140915_lastid.txt", "r"); + $lastid = trim(fgets($fp, 4096)); + fclose($fp); +} + +echo "ID now: $lastid\n"; + + +$count = 0; + +$query = " + + SELECT `fname`, `lname`, `email` + FROM `users` + WHERE `deleted` = '0000-00-00 00:00:00' + AND `modified` != '0000-00-00 00:00:00' + AND `verified` = '1' + + ORDER BY `id`"; + +$res = mysql_query($query); + +while($row = mysql_fetch_assoc($res)) +{ + $mailtxt = "Dear ${row["fname"]} ${row["lname"]},\n".$lines_EN."\n\n"; + + sendmail($row['email'], "[CAcert.org] CAcert Community Agreement (CCA)", $mailtxt, "support@cacert.org", "", "", "CAcert", "returns@cacert.org", ""); + + $fp = fopen("send_policy_cca20140915_lastid.txt", "w"); + fputs($fp, $row["id"]."\n"); + fclose($fp); + + $count++; + echo "Sent ${count}th mail. User ID: ${row["id"]}\n"; + + if(0 == $count % 5) { + sleep (1); + } +} -- cgit v1.2.1