Source code taken from cacert-20141124.tar.bz2
[cacert.git] / www / stats.php
index 93f693e..d4d892d 100644 (file)
-<? /*\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`")));\r
-               $stats['users_1to49'] = number_format(mysql_num_rows(mysql_query("select `to` from `notary` group by `to` having sum(`points`) > 0 and sum(`points`) < 50")));\r
-               $stats['users_50to99'] = number_format(mysql_num_rows(mysql_query("select `to` from `notary` group by `to` having sum(`points`) >= 50 and sum(`points`) < 100")));\r
-               $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`) >= 100")));\r
-               $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`) >= 100")));\r
-               $stats['points_issued'] = number_format(tc(mysql_query("select sum(`points`) as `count` from `notary`")));\r
-\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' group by `to` having sum(`points`) >= 100"));\r
-                       $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' group by `to` having sum(`points`) >= 100"));\r
-                       $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();
+       }
+?>
+<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(); ?>