Merge branch 'release' into bug-1221
authorMichael Tänzer <neo@nhng.de>
Wed, 20 Nov 2013 21:28:30 +0000 (22:28 +0100)
committerMichael Tänzer <neo@nhng.de>
Wed, 20 Nov 2013 21:28:30 +0000 (22:28 +0100)
Conflicts:
www/stats.php

Signed-off-by: Michael Tänzer <neo@nhng.de>
1  2 
includes/account.php
scripts/cron/refresh_stats.php
www/stats.php

Simple merge
index 0000000,2a3d2b5..3b446ba
mode 000000,100755..100755
--- /dev/null
@@@ -1,0 -1,307 +1,308 @@@
 - * 
+ #!/usr/bin/php -q
+ <?php
+ /*
+ LibreSSL - CAcert web application
+ Copyright (C) 2004-2012  CAcert Inc.
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ GNU General Public License for more details.
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
+ */
+ require_once(dirname(__FILE__).'/../../includes/mysql.php');
+ /**
+  * 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
+  *            the MySQL result set
+  */
+ function sql_query($sql) {
+       $res = mysql_query($sql);
+       if (!$res) {
+               fwrite(STDERR, "MySQL query failed:\n\"$sql\"\n".mysql_error());
+               die(1);
+       }
 -      
++
+       return $res;
+ }
+ function tc($sql) {
+       $row = mysql_fetch_assoc(sql_query($sql));
+       return(intval($row['count']));
+ }
+ /**
+ * writes new data to cache, create cache or update existing cache, set current
+ * time stamp
+ * @return boolean
+ */
+ function updateCache($stats) {
+       $timestamp = time();
+       $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'");
+       if (mysql_num_rows($res) !== 1) {
+               fwrite(STDERR, "Error on inserting the new statistic");
+               return false;
+       }
 -      
++
+       sql_query("delete from `statscache` where `timestamp` != '$timestamp'");
+       return true;
+ }
+ /**
+ * get statistics data from live tables, takes a long time so please try to use the
+ * cache
+ * @return array
+ */
+ 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`
+                       where `expire` != 0");
+       $certs += tc("select count(*) as `count` from `gpg`
+                       where `expire` != 0");
+       $certs += tc("select count(*) as `count` from `orgdomaincerts`
+                       where `expire` != 0");
+       $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`
+               where `revoked` = 0 and `expire` > NOW()");
+       $certs += tc("select count(*) as `count` from `gpg`
+               where `expire` > NOW()");
+       $certs += tc("select count(*) as `count` from `orgdomaincerts`
+               where `revoked` = 0 and `expire` > NOW()");
+       $certs += tc("select count(*) as `count` from `orgemailcerts`
+               where `revoked` = 0 and `expire` > NOW()");
+       $stats['valid_certificates'] = number_format($certs);
 -                      where `method` = '' or `method` = 'Face to Face Meeting'"));
 -      
++
+       $stats['assurances_made'] = number_format(tc(
+               "select count(*) as `count` from `notary`
 -      
++                      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`
+                               where `deleted` = 0
+                               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`
+                               where `deleted` = 0
+                               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 (
+                               select sum(`points`) from `notary`
+                                       where `to`=`users`.`id`
+                                       and `deleted` = 0
+                               ) >= 100
+                       and not exists(
+                               select 1 from `cats_passed` as `cp`, `cats_variant` as `cv`
+                                       where `cp`.`user_id`=`users`.`id`
+                                       and `cp`.`variant_id`=`cv`.`id`
+                                       and `cv`.`type_id`=1
+                               )"
+               ));
 -      
++
+       $stats['aussurers_with_test'] = number_format(tc(
+               "select count(*) as `count` from `users`
+                       where (
+                               select sum(`points`) from `notary`
+                                       where `to`=`users`.`id`
+                                       and `deleted` = 0
+                               ) >= 100
+                       and exists(
+                               select 1 from `cats_passed` as `cp`, `cats_variant` as `cv`
+                                       where `cp`.`user_id`=`users`.`id`
+                                       and `cp`.`variant_id`=`cv`.`id`
+                                       and `cv`.`type_id`=1
+                               )"
+               ));
 -              
++
+       $stats['points_issued'] = number_format(tc(
+               "select sum(greatest(`points`, `awarded`)) as `count` from `notary`
+                       where `deleted` = 0
+                       and `method` = 'Face to Face Meeting'"));
+       $totalusers=0;
+       $totassurers=0;
+       $totalcerts=0;
+       for($i = 0; $i < 12; $i++) {
+               $first_ts = mktime(0, 0, 0, date("m") - $i, 1, date("Y"));
+               $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";
 -                      "select count(*) as `count` from `users` 
++
+               $totalusers += $users = tc(
 -              
++                      "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`
+                                       where `when` >= '$first' and `when` < '$next_month'
+                                       and `method`!='Administrative Increase'
+                                       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'
+                               and `expire` != 0");
+               $certs += tc(
+                       "select count(*) as `count` from `emailcerts`
+                               where `created` >= '$first' and `created` < '$next_month'
+                               and `expire` != 0");
+               $certs += tc(
+                       "select count(*) as `count` from `gpg`
+                               where `issued` >= '$first' and `issued` < '$next_month'
+                               and `expire` != 0");
+               $certs += tc(
+                       "select count(*) as `count` from `orgdomaincerts`
+                               where `created` >= '$first' and `created` < '$next_month'
+                               and `expire` != 0");
+               $certs += tc(
+                       "select count(*) as `count` from `orgemailcerts`
+                               where `created` >= '$first' and `created` < '$next_month'
+                               and `expire` != 0");
+               $totalcerts += $certs;
+               $tmp_arr = array();
+               $tmp_arr['date'] = date("Y-m", $first_ts);
+               $tmp_arr['new_users'] = number_format($users);
+               $tmp_arr['new_assurers'] = number_format($assurers);
+               $tmp_arr['new_certificates'] = number_format($certs);
+               $stats['growth_last_12m'][] = $tmp_arr;
+       }
+       $stats['growth_last_12m_total'] = array(
+                       'new_users' => number_format($totalusers),
+                       'new_assurers' => number_format($totassurers),
+                       'new_certificates' => number_format($totalcerts),
+               );
+       $totalcerts = 0;
+       $totalusers = 0;
+       $totassurers = 0;
+       for($i = date("Y"); $i >= 2002; $i--) {
+               $first_ts = mktime(0, 0, 0, 1, 1, $i);
+               $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";
 -                      "select count(*) as `count` from `users` 
++
+               $totalusers += $users = tc(
 -              
++                      "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`
+                                       where `when` >= '$first' and `when` < '$next_year'
+                                       and `method`!='Administrative Increase'
+                                       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'
+                               and `expire` != 0");
+               $certs += tc(
+                       "select count(*) as `count` from `emailcerts`
+                               where `created` >= '$first' and `created` < '$next_year'
+                               and `expire` != 0");
+               $certs += tc(
+                       "select count(*) as `count` from `gpg`
+                               where `issued` >= '$first' and `issued` < '$next_year'
+                               and `expire` != 0");
+               $certs += tc(
+                       "select count(*) as `count` from `orgdomaincerts`
+                               where `created` >= '$first' and `created` < '$next_year'
+                               and `expire` != 0");
+               $certs += tc(
+                       "select count(*) as `count` from `orgemailcerts`
+                               where `created` >= '$first' and `created` < '$next_year'
+                               and `expire` != 0");
+               $totalcerts += $certs;
+               $tmp_arr = array();
+               $tmp_arr['date'] = $i;
+               $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(
+                       'new_users' => number_format($totalusers),
+                       'new_assurers' => number_format($totassurers),
+                       'new_certificates' => number_format($totalcerts),
+               );
+       return $stats;
+ }
+ $stats = getDataFromLive();
+ if (! updateCache($stats) ) {
+       fwrite(STDERR,
+               "An error occured. The statistics were not successfully updated!");
+       die(1);
+ }
diff --cc www/stats.php
- <? /*\r
-     LibreSSL - CAcert web application\r
-     Copyright (C) 2004-2008  CAcert Inc.\r
\r
-     This program is free software; you can redistribute it and/or modify\r
-     it under the terms of the GNU General Public License as published by\r
-     the Free Software Foundation; version 2 of the License.\r
\r
-     This program is distributed in the hope that it will be useful,\r
-     but WITHOUT ANY WARRANTY; without even the implied warranty of\r
-     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the\r
-     GNU General Public License for more details.\r
\r
-     You should have received a copy of the GNU General Public License\r
-     along with this program; if not, write to the Free Software\r
-     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA\r
- */\r
-       define('MAX_CACHE_TTL', 36000);\r
\r
-       loadem("index");\r
-       showheader(_("Welcome to CAcert.org"));\r
\r
-       function tc($sql)\r
-       {\r
-               $row = mysql_fetch_assoc($sql);\r
-               return($row['count']);\r
-       }\r
\r
-       /**\r
-        * writes new data to cache, create cache or update existing cache, set current\r
-        * time stamp\r
-        * @return boolean\r
-        */\r
-       function updateCache($stats) {\r
-               $sql = 'insert into statscache (timestamp, cache) values ("' . time() . '", ' .\r
-                       '"' . mysql_real_escape_string(serialize($stats)) . '")';\r
-               mysql_query($sql);\r
-       }\r
\r
-     /**\r
-      * get statistics data from current cache, return result of getDataFromLive if no cache file exists\r
-      * @return array\r
-      */\r
-       function getData() {\r
-               $sql = 'select * from statscache order by timestamp desc limit 1';\r
-               $res = mysql_query($sql);\r
-               if ($res && mysql_numrows($res) > 0) {\r
-                       $ar = mysql_fetch_assoc($res);\r
-                       $stats = unserialize($ar['cache']);\r
-                       $stats['timestamp'] = $ar['timestamp'];\r
-                       if ($ar['timestamp'] + MAX_CACHE_TTL < time())\r
-                       {\r
-                               $stats=getDataFromLive();\r
-                               updateCache($stats);\r
-                       }\r
-                       return $stats;\r
-               }\r
-               $stats=getDataFromLive();\r
-               updateCache($stats);\r
-               return $stats;\r
-       }\r
\r
-       /**\r
-      * get statistics data from live tables, takes a long time so please try to use the\r
-      * cache\r
-      * @return array\r
-      */\r
-       function getDataFromLive() {\r
-         $stats = array();\r
-               $stats['verified_users'] = number_format(tc(mysql_query("select count(`id`) as `count` from `users` where `verified`=1")));\r
-               $stats['verified_emails'] = number_format(tc(mysql_query("select count(`id`) as `count` from `email` where `hash`='' and `deleted`=0")));\r
-               $stats['verified_domains'] = number_format(tc(mysql_query("select count(`id`) as `count` from `domains` where `hash`='' and `deleted`=0")));\r
-               $certs = tc(mysql_query("select count(`id`) as `count` from `domaincerts`"));\r
-               $certs += tc(mysql_query("select count(`id`) as `count` from `emailcerts`"));\r
-               $certs += tc(mysql_query("select count(`id`) as `count` from `gpg`"));\r
-               $certs += tc(mysql_query("select count(`id`) as `count` from `orgdomaincerts`"));\r
-               $certs += tc(mysql_query("select count(`id`) as `count` from `orgemailcerts`"));\r
-               $stats['verified_certificates'] = number_format($certs);\r
-               $certs = tc(mysql_query("select count(`id`) as `count` from `domaincerts` where `revoked`=0 and `expire`>NOW()"));\r
-               $certs += tc(mysql_query("select count(`id`) as `count` from `emailcerts` where `revoked`=0 and `expire`>NOW()"));\r
-               $certs += tc(mysql_query("select count(`id`) as `count` from `gpg` where `expire`<=NOW()"));\r
-               $certs += tc(mysql_query("select count(`id`) as `count` from `orgdomaincerts` where `revoked`=0 and `expire`>NOW()"));\r
-               $certs += tc(mysql_query("select count(`id`) as `count` from `orgemailcerts` where `revoked`=0 and `expire`>NOW()"));\r
-               $stats['valid_certificates'] = number_format($certs);\r
-               $stats['assurances_made'] = number_format(tc(mysql_query("select count(`id`) as `count` from `notary` where `notary`.`deleted`=0")));
-               $stats['users_1to49'] = number_format(mysql_num_rows(mysql_query("select `to` from `notary`  where `notary`.`deleted`=0 group by `to` having sum(`points`) > 0 and sum(`points`) < 50")));
-               $stats['users_50to99'] = number_format(mysql_num_rows(mysql_query("select `to` from `notary`  where `notary`.`deleted`=0 group by `to` having sum(`points`) >= 50 and sum(`points`) < 100")));
-               $stats['assurer_candidates'] = number_format(tc(mysql_query("select count(*) as `count` from `users` where ".\r
-                                     "not exists(select 1 from `cats_passed` as `cp`, `cats_variant` as `cv` where `cp`.`user_id`=`users`.`id` and `cp`.`variant_id`=`cv`.`id` and `cv`.`type_id`=1) and ".\r
-                                     "(select sum(`points`) from `notary` where `to`=`users`.`id` and `notary`.`deleted`=0) >= 100 ")));
-               $stats['aussurers_with_test'] = number_format(tc(mysql_query("select count(*) as `count` from `users` where ".\r
-                                     "exists(select 1 from `cats_passed` as `cp`, `cats_variant` as `cv` where `cp`.`user_id`=`users`.`id` and `cp`.`variant_id`=`cv`.`id` and `cv`.`type_id`=1) and ".\r
-                                     "(select sum(`points`) from `notary` where `to`=`users`.`id` and where `notary`.`deleted`=0) >= 100")));
-               $stats['points_issued'] = number_format(tc(mysql_query("select sum(`points`) as `count` from `notary` where `notary`.`deleted`=0")));
\r
-               $totalusers=0;\r
-               $totassurers=0;\r
-               $totalcerts=0;\r
-               for($i = 0; $i < 12; $i++) {\r
-                       $tmp_arr = array();\r
-                       $tmp_arr['date'] = date("Y-m", mktime(0,0,0,date("m") - $i,1,date("Y")));\r
-                       $date = date("Y-m", mktime(0,0,0,date("m") - $i,1,date("Y")));\r
-                       $totalusers += $users = tc(mysql_query("select count(`id`) as `count` from `users` where `created` like '$date%' and `verified`=1"));\r
-                       $totassurers += $assurers = mysql_num_rows(mysql_query("select `to` from `notary` where `when` like '$date%' and `method`!='Administrative Increase' and `notary`.`deleted`=0 group by `to` having sum(`points`) >= 100"));
-                       $certs = tc(mysql_query("select count(`id`) as `count` from `domaincerts` where `created` like '$date%'"));\r
-                       $certs += tc(mysql_query("select count(`id`) as `count` from `emailcerts` where `created` like '$date%'"));\r
-                       $certs += tc(mysql_query("select count(`id`) as `count` from `gpg` where `issued` like '$date%'"));\r
-                       $certs += tc(mysql_query("select count(`id`) as `count` from `orgdomaincerts` where `created` like '$date%'"));\r
-                       $certs += tc(mysql_query("select count(`id`) as `count` from `orgemailcerts` where `created` like '$date%'"));\r
-                       $totalcerts += $certs;\r
\r
-                       $tmp_arr['new_users'] = number_format($users);\r
-                       $tmp_arr['new_assurers'] = number_format($assurers);\r
-                       $tmp_arr['new_certificates'] = number_format($certs);\r
\r
-                       $stats['growth_last_12m'][] = $tmp_arr;\r
-               }\r
-               $stats['growth_last_12m_total'] = array('new_users' => number_format($totalusers),\r
-                                                                                               'new_assurers' => number_format($totassurers),\r
-                                                                                               'new_certificates' => number_format($totalcerts));\r
\r
-               $totalcerts = 0;\r
-               $totalusers = 0;\r
-               $totassurers = 0;\r
-               for($i = date("Y"); $i >= 2002; $i--) {\r
-                       $tmp_arr = array();\r
-                       $tmp_arr['date'] = $i;\r
-                       $totalusers += $users = tc(mysql_query("select count(`id`) as `count` from `users` where `created` like '$i%' and `verified`=1"));\r
-                       $totassurers += $assurers = mysql_num_rows(mysql_query("select `to` from `notary` where `when` like '$i%' and `method`!='Administrative Increase' and `notary`.`deleted`=0 group by `to` having sum(`points`) >= 100"));
-                       $certs = tc(mysql_query("select count(`id`) as `count` from `domaincerts` where `created` like '$i%'"));\r
-                       $certs += tc(mysql_query("select count(`id`) as `count` from `emailcerts` where `created` like '$i%'"));\r
-                       $certs += tc(mysql_query("select count(`id`) as `count` from `gpg` where `issued` like '$i%'"));\r
-                       $certs += tc(mysql_query("select count(`id`) as `count` from `orgdomaincerts` where `created` like '$i%'"));\r
-                       $certs += tc(mysql_query("select count(`id`) as `count` from `orgemailcerts` where `created` like '$i%'"));\r
-                       $totalcerts += $certs;\r
\r
-                       $tmp_arr['new_users'] = number_format($users);\r
-                       $tmp_arr['new_assurers'] = number_format($assurers);\r
-                       $tmp_arr['new_certificates'] = number_format($certs);\r
\r
-                       $stats['growth_last_years'][] = $tmp_arr;\r
-               }\r
-               $stats['growth_last_years_total'] = array('new_users' => number_format($totalusers),\r
-                                                                                                 'new_assurers' => number_format($totassurers),\r
-                                                                                                 'new_certificates' => number_format($totalcerts));\r
\r
-               return $stats;\r
-       }\r
\r
-       $stats = getData();\r
- ?>\r
- <h1>CAcert.org <?=_("Statistics")?></h1>\r
\r
- <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">\r
-   <tr>\r
-     <td colspan="2" class="title">CAcert.org <?=_("Statistics")?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Verified Users")?>:</td>\r
-     <td class="DataTD"><?=$stats['verified_users'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Verified Emails")?>:</td>\r
-     <td class="DataTD"><?=$stats['verified_emails'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Verified Domains")?>:</td>\r
-     <td class="DataTD"><?=$stats['verified_domains'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Certificates Issued")?>:</td>\r
-     <td class="DataTD"><?=$stats['verified_certificates'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Valid Certificates")?>:</td>\r
-     <td class="DataTD"><?=$stats['valid_certificates'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Assurances Made")?>:</td>\r
-     <td class="DataTD"><?=$stats['assurances_made'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Users with 1-49 Points")?>:</td>\r
-     <td class="DataTD"><?=$stats['users_1to49'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Users with 50-99 Points")?>:</td>\r
-     <td class="DataTD"><?=$stats['users_50to99'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Assurer Candidates")?>:</td>\r
-     <td class="DataTD"><?=$stats['assurer_candidates'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Assurers with test")?>:</td>\r
-     <td class="DataTD"><?=$stats['aussurers_with_test'];?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><?=_("Points Issued")?>:</td>\r
-     <td class="DataTD"><?=$stats['points_issued'];?></td>\r
-   </tr>\r
- </table>\r
- <br>\r
- <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">\r
-   <tr>\r
-     <td colspan="4" class="title">CAcert.org <?=_("Growth in the last 12 months")?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><b><?=_("Date")?></b>\r
-     <td class="DataTD"><b><?=_("New Users")?></b>\r
-     <td class="DataTD"><b><?=_("New Assurers")?></b>\r
-     <td class="DataTD"><b><?=_("New Certificates")?></b>\r
-   </tr>\r
- <?\r
-       for($i = 0; $i < 12; $i++) {\r
- ?>\r
-   <tr>\r
-     <td class="DataTD"><?=$stats['growth_last_12m'][$i]['date'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_users'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_assurers'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_certificates'];?></td>\r
-   </tr>\r
- <? } ?>\r
-   <tr>\r
-     <td class="DataTD">N/A</td>\r
-     <td class="DataTD"><?=$stats['growth_last_12m_total']['new_users'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_12m_total']['new_assurers'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_12m_total']['new_certificates'];?></td>\r
-   </tr>\r
- </table>\r
- <br>\r
- <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">\r
-   <tr>\r
-     <td colspan="4" class="title">CAcert.org <?=_("Growth by year")?></td>\r
-   </tr>\r
-   <tr>\r
-     <td class="DataTD"><b><?=_("Date")?></b>\r
-     <td class="DataTD"><b><?=_("New Users")?></b>\r
-     <td class="DataTD"><b><?=_("New Assurers")?></b>\r
-     <td class="DataTD"><b><?=_("New Certificates")?></b>\r
-   </tr>\r
- <?\r
-       for($i = 0; $i < count($stats['growth_last_years']); $i++) {\r
- ?>\r
-   <tr>\r
-     <td class="DataTD"><?=$stats['growth_last_years'][$i]['date'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_users'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_assurers'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_certificates'];?></td>\r
-   </tr>\r
- <? } ?>\r
-   <tr>\r
-     <td class="DataTD">N/A</td>\r
-     <td class="DataTD"><?=$stats['growth_last_years_total']['new_users'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_years_total']['new_assurers'];?></td>\r
-     <td class="DataTD"><?=$stats['growth_last_years_total']['new_certificates'];?></td>\r
-   </tr>\r
- </table>\r
- <br>\r
- <?php\r
-       if (isset($stats['timestamp'])) {\r
- ?>\r
- <div style="text-align: center;font-size: small;"><?=_("Statistical data from cache, created at ") . date('Y-m-d H:i:s', $stats['timestamp']);?></div>\r
- <?php\r
-       }\r
- ?>\r
- <? showfooter(); ?>\r
\r
+ <? /*
+     LibreSSL - CAcert web application
+     Copyright (C) 2004-2008  CAcert Inc.
+     This program is free software; you can redistribute it and/or modify
+     it under the terms of the GNU General Public License as published by
+     the Free Software Foundation; version 2 of the License.
+     This program is distributed in the hope that it will be useful,
+     but WITHOUT ANY WARRANTY; without even the implied warranty of
+     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+     GNU General Public License for more details.
+     You should have received a copy of the GNU General Public License
+     along with this program; if not, write to the Free Software
+     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
+ */
+       loadem("index");
+       showheader(_("Welcome to CAcert.org"));
+     /**
+      * get statistics data from current cache, return result of getDataFromLive if no cache file exists
+      * @return array
+      */
+       function getData() {
+               $sql = 'select * from `statscache` order by `timestamp` desc limit 1';
+               $res = mysql_query($sql);
+               if ($res && mysql_numrows($res) > 0) {
+                       $ar = mysql_fetch_assoc($res);
+                       $stats = unserialize($ar['cache']);
+                       $stats['timestamp'] = $ar['timestamp'];
+                       return $stats;
+               }
 -              
++
+               return null;
+       }
+       $stats = getData();
+       if ($stats === null) {
+               echo '<p>', _("Error while retrieving the statistics!"), '</p>';
+               showfooter();
 -              die(); 
++              die();
+       }
+ ?>
+ <h1>CAcert.org <?=_("Statistics")?></h1>
+ <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+   <tr>
+     <td colspan="2" class="title">CAcert.org <?=_("Statistics")?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Verified Users")?>:</td>
+     <td class="DataTD"><?=$stats['verified_users'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Verified Emails")?>:</td>
+     <td class="DataTD"><?=$stats['verified_emails'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Verified Domains")?>:</td>
+     <td class="DataTD"><?=$stats['verified_domains'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Certificates Issued")?>:</td>
+     <td class="DataTD"><?=$stats['verified_certificates'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Valid Certificates")?>:</td>
+     <td class="DataTD"><?=$stats['valid_certificates'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Assurances Made")?>:</td>
+     <td class="DataTD"><?=$stats['assurances_made'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Users with 1-49 Points")?>:</td>
+     <td class="DataTD"><?=$stats['users_1to49'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Users with 50-99 Points")?>:</td>
+     <td class="DataTD"><?=$stats['users_50to99'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Assurer Candidates")?>:</td>
+     <td class="DataTD"><?=$stats['assurer_candidates'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Assurers with test")?>:</td>
+     <td class="DataTD"><?=$stats['aussurers_with_test'];?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><?=_("Points Issued")?>:</td>
+     <td class="DataTD"><?=$stats['points_issued'];?></td>
+   </tr>
+ </table>
+ <br>
+ <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+   <tr>
+     <td colspan="4" class="title">CAcert.org <?=_("Growth in the last 12 months")?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><b><?=_("Date")?></b>
+     <td class="DataTD"><b><?=_("New Users")?></b>
+     <td class="DataTD"><b><?=_("New Assurers")?></b>
+     <td class="DataTD"><b><?=_("New Certificates")?></b>
+   </tr>
+ <?
+       for($i = 0; $i < 12; $i++) {
+ ?>
+   <tr>
+     <td class="DataTD"><?=$stats['growth_last_12m'][$i]['date'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_users'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_assurers'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_certificates'];?></td>
+   </tr>
+ <? } ?>
+   <tr>
+     <td class="DataTD"><?=_("Total")?></td>
+     <td class="DataTD"><?=$stats['growth_last_12m_total']['new_users'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_12m_total']['new_assurers'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_12m_total']['new_certificates'];?></td>
+   </tr>
+ </table>
+ <br>
+ <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+   <tr>
+     <td colspan="4" class="title">CAcert.org <?=_("Growth by year")?></td>
+   </tr>
+   <tr>
+     <td class="DataTD"><b><?=_("Date")?></b>
+     <td class="DataTD"><b><?=_("New Users")?></b>
+     <td class="DataTD"><b><?=_("New Assurers")?></b>
+     <td class="DataTD"><b><?=_("New Certificates")?></b>
+   </tr>
+ <?
+       for($i = 0; $i < count($stats['growth_last_years']); $i++) {
+ ?>
+   <tr>
+     <td class="DataTD"><?=$stats['growth_last_years'][$i]['date'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_users'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_assurers'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_certificates'];?></td>
+   </tr>
+ <? } ?>
+   <tr>
+     <td class="DataTD"><?=_("Total")?></td>
+     <td class="DataTD"><?=$stats['growth_last_years_total']['new_users'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_years_total']['new_assurers'];?></td>
+     <td class="DataTD"><?=$stats['growth_last_years_total']['new_certificates'];?></td>
+   </tr>
+ </table>
+ <br>
+ <div style="text-align: center;font-size: small;"><?
+       printf(_("Last updated: %s"), date('Y-m-d H:i:s', $stats['timestamp']));?>
+ </div>
+ <? showfooter(); ?>
 -