source code taken from cacert-20100204.tar.bz2
[cacert.git] / cacert / www / stats.php
1 <? /*
2 LibreSSL - CAcert web application
3 Copyright (C) 2004-2008 CAcert Inc.
4
5 This program is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation; version 2 of the License.
8
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU General Public License for more details.
13
14 You should have received a copy of the GNU General Public License
15 along with this program; if not, write to the Free Software
16 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
17 */
18 define('MAX_CACHE_TTL', 36000);
19
20 loadem("index");
21 showheader(_("Welcome to CAcert.org"));
22
23 function tc($sql)
24 {
25 $row = mysql_fetch_assoc($sql);
26 return($row['count']);
27 }
28
29 /**
30 * writes new data to cache, create cache or update existing cache, set current
31 * time stamp
32 * @return boolean
33 */
34 function updateCache($stats) {
35 $sql = 'insert into statscache (timestamp, cache) values ("' . time() . '", ' .
36 '"' . mysql_real_escape_string(serialize($stats)) . '")';
37 mysql_query($sql);
38 }
39
40 /**
41 * get statistics data from current cache, return result of getDataFromLive if no cache file exists
42 * @return array
43 */
44 function getData() {
45 $sql = 'select * from statscache order by timestamp desc limit 1';
46 $res = mysql_query($sql);
47 if ($res && mysql_numrows($res) > 0) {
48 $ar = mysql_fetch_assoc($res);
49 $stats = unserialize($ar['cache']);
50 $stats['timestamp'] = $ar['timestamp'];
51 if ($ar['timestamp'] + MAX_CACHE_TTL < time())
52 {
53 $stats=getDataFromLive();
54 updateCache($stats);
55 }
56 return $stats;
57 }
58 $stats=getDataFromLive();
59 updateCache($stats);
60 return $stats;
61 }
62
63 /**
64 * get statistics data from live tables, takes a long time so please try to use the
65 * cache
66 * @return array
67 */
68 function getDataFromLive() {
69 $stats = array();
70 $stats['verified_users'] = number_format(tc(mysql_query("select count(`id`) as `count` from `users` where `verified`=1")));
71 $stats['verified_emails'] = number_format(tc(mysql_query("select count(`id`) as `count` from `email` where `hash`='' and `deleted`=0")));
72 $stats['verified_domains'] = number_format(tc(mysql_query("select count(`id`) as `count` from `domains` where `hash`='' and `deleted`=0")));
73 $certs = tc(mysql_query("select count(`id`) as `count` from `domaincerts`"));
74 $certs += tc(mysql_query("select count(`id`) as `count` from `emailcerts`"));
75 $certs += tc(mysql_query("select count(`id`) as `count` from `gpg`"));
76 $certs += tc(mysql_query("select count(`id`) as `count` from `orgdomaincerts`"));
77 $certs += tc(mysql_query("select count(`id`) as `count` from `orgemailcerts`"));
78 $stats['verified_certificates'] = number_format($certs);
79 $certs = tc(mysql_query("select count(`id`) as `count` from `domaincerts` where `revoked`=0 and `expire`>NOW()"));
80 $certs += tc(mysql_query("select count(`id`) as `count` from `emailcerts` where `revoked`=0 and `expire`>NOW()"));
81 $certs += tc(mysql_query("select count(`id`) as `count` from `gpg` where `expire`<=NOW()"));
82 $certs += tc(mysql_query("select count(`id`) as `count` from `orgdomaincerts` where `revoked`=0 and `expire`>NOW()"));
83 $certs += tc(mysql_query("select count(`id`) as `count` from `orgemailcerts` where `revoked`=0 and `expire`>NOW()"));
84 $stats['valid_certificates'] = number_format($certs);
85 $stats['assurances_made'] = number_format(tc(mysql_query("select count(`id`) as `count` from `notary`")));
86 $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")));
87 $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")));
88 $stats['assurer_candidates'] = number_format(tc(mysql_query("select count(*) as `count` from `users` where ".
89 "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 ".
90 "(select sum(`points`) from `notary` where `to`=`users`.`id`) >= 100")));
91 $stats['aussurers_with_test'] = number_format(tc(mysql_query("select count(*) as `count` from `users` where ".
92 "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 ".
93 "(select sum(`points`) from `notary` where `to`=`users`.`id`) >= 100")));
94 $stats['points_issued'] = number_format(tc(mysql_query("select sum(`points`) as `count` from `notary`")));
95
96 $totalusers=0;
97 $totassurers=0;
98 $totalcerts=0;
99 for($i = 0; $i < 12; $i++) {
100 $tmp_arr = array();
101 $tmp_arr['date'] = date("Y-m", mktime(0,0,0,date("m") - $i,1,date("Y")));
102 $date = date("Y-m", mktime(0,0,0,date("m") - $i,1,date("Y")));
103 $totalusers += $users = tc(mysql_query("select count(`id`) as `count` from `users` where `created` like '$date%' and `verified`=1"));
104 $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"));
105 $certs = tc(mysql_query("select count(`id`) as `count` from `domaincerts` where `created` like '$date%'"));
106 $certs += tc(mysql_query("select count(`id`) as `count` from `emailcerts` where `created` like '$date%'"));
107 $certs += tc(mysql_query("select count(`id`) as `count` from `gpg` where `issued` like '$date%'"));
108 $certs += tc(mysql_query("select count(`id`) as `count` from `orgdomaincerts` where `created` like '$date%'"));
109 $certs += tc(mysql_query("select count(`id`) as `count` from `orgemailcerts` where `created` like '$date%'"));
110 $totalcerts += $certs;
111
112 $tmp_arr['new_users'] = number_format($users);
113 $tmp_arr['new_assurers'] = number_format($assurers);
114 $tmp_arr['new_certificates'] = number_format($certs);
115
116 $stats['growth_last_12m'][] = $tmp_arr;
117 }
118 $stats['growth_last_12m_total'] = array('new_users' => number_format($totalusers),
119 'new_assurers' => number_format($totassurers),
120 'new_certificates' => number_format($totalcerts));
121
122 $totalcerts = 0;
123 $totalusers = 0;
124 $totassurers = 0;
125 for($i = date("Y"); $i >= 2002; $i--) {
126 $tmp_arr = array();
127 $tmp_arr['date'] = $i;
128 $totalusers += $users = tc(mysql_query("select count(`id`) as `count` from `users` where `created` like '$i%' and `verified`=1"));
129 $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"));
130 $certs = tc(mysql_query("select count(`id`) as `count` from `domaincerts` where `created` like '$i%'"));
131 $certs += tc(mysql_query("select count(`id`) as `count` from `emailcerts` where `created` like '$i%'"));
132 $certs += tc(mysql_query("select count(`id`) as `count` from `gpg` where `issued` like '$i%'"));
133 $certs += tc(mysql_query("select count(`id`) as `count` from `orgdomaincerts` where `created` like '$i%'"));
134 $certs += tc(mysql_query("select count(`id`) as `count` from `orgemailcerts` where `created` like '$i%'"));
135 $totalcerts += $certs;
136
137 $tmp_arr['new_users'] = number_format($users);
138 $tmp_arr['new_assurers'] = number_format($assurers);
139 $tmp_arr['new_certificates'] = number_format($certs);
140
141 $stats['growth_last_years'][] = $tmp_arr;
142 }
143 $stats['growth_last_years_total'] = array('new_users' => number_format($totalusers),
144 'new_assurers' => number_format($totassurers),
145 'new_certificates' => number_format($totalcerts));
146
147 return $stats;
148 }
149
150 $stats = getData();
151 ?>
152 <h1>CAcert.org <?=_("Statistics")?></h1>
153
154 <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">
155 <tr>
156 <td colspan="2" class="title">CAcert.org <?=_("Statistics")?></td>
157 </tr>
158 <tr>
159 <td class="DataTD"><?=_("Verified Users")?>:</td>
160 <td class="DataTD"><?=$stats['verified_users'];?></td>
161 </tr>
162 <tr>
163 <td class="DataTD"><?=_("Verified Emails")?>:</td>
164 <td class="DataTD"><?=$stats['verified_emails'];?></td>
165 </tr>
166 <tr>
167 <td class="DataTD"><?=_("Verified Domains")?>:</td>
168 <td class="DataTD"><?=$stats['verified_domains'];?></td>
169 </tr>
170 <tr>
171 <td class="DataTD"><?=_("Certificates Issued")?>:</td>
172 <td class="DataTD"><?=$stats['verified_certificates'];?></td>
173 </tr>
174 <tr>
175 <td class="DataTD"><?=_("Valid Certificates")?>:</td>
176 <td class="DataTD"><?=$stats['valid_certificates'];?></td>
177 </tr>
178 <tr>
179 <td class="DataTD"><?=_("Assurances Made")?>:</td>
180 <td class="DataTD"><?=$stats['assurances_made'];?></td>
181 </tr>
182 <tr>
183 <td class="DataTD"><?=_("Users with 1-49 Points")?>:</td>
184 <td class="DataTD"><?=$stats['users_1to49'];?></td>
185 </tr>
186 <tr>
187 <td class="DataTD"><?=_("Users with 50-99 Points")?>:</td>
188 <td class="DataTD"><?=$stats['users_50to99'];?></td>
189 </tr>
190 <tr>
191 <td class="DataTD"><?=_("Assurer Candidates")?>:</td>
192 <td class="DataTD"><?=$stats['assurer_candidates'];?></td>
193 </tr>
194 <tr>
195 <td class="DataTD"><?=_("Assurers with test")?>:</td>
196 <td class="DataTD"><?=$stats['aussurers_with_test'];?></td>
197 </tr>
198 <tr>
199 <td class="DataTD"><?=_("Points Issued")?>:</td>
200 <td class="DataTD"><?=$stats['points_issued'];?></td>
201 </tr>
202 </table>
203 <br>
204 <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">
205 <tr>
206 <td colspan="4" class="title">CAcert.org <?=_("Growth in the last 12 months")?></td>
207 </tr>
208 <tr>
209 <td class="DataTD"><b><?=_("Date")?></b>
210 <td class="DataTD"><b><?=_("New Users")?></b>
211 <td class="DataTD"><b><?=_("New Assurers")?></b>
212 <td class="DataTD"><b><?=_("New Certificates")?></b>
213 </tr>
214 <?
215 for($i = 0; $i < 12; $i++) {
216 ?>
217 <tr>
218 <td class="DataTD"><?=$stats['growth_last_12m'][$i]['date'];?></td>
219 <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_users'];?></td>
220 <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_assurers'];?></td>
221 <td class="DataTD"><?=$stats['growth_last_12m'][$i]['new_certificates'];?></td>
222 </tr>
223 <? } ?>
224 <tr>
225 <td class="DataTD">N/A</td>
226 <td class="DataTD"><?=$stats['growth_last_12m_total']['new_users'];?></td>
227 <td class="DataTD"><?=$stats['growth_last_12m_total']['new_assurers'];?></td>
228 <td class="DataTD"><?=$stats['growth_last_12m_total']['new_certificates'];?></td>
229 </tr>
230 </table>
231 <br>
232 <table align="center" border="0" cellspacing="0" cellpadding="0" class="wrapper">
233 <tr>
234 <td colspan="4" class="title">CAcert.org <?=_("Growth by year")?></td>
235 </tr>
236 <tr>
237 <td class="DataTD"><b><?=_("Date")?></b>
238 <td class="DataTD"><b><?=_("New Users")?></b>
239 <td class="DataTD"><b><?=_("New Assurers")?></b>
240 <td class="DataTD"><b><?=_("New Certificates")?></b>
241 </tr>
242 <?
243 for($i = 0; $i < count($stats['growth_last_years']); $i++) {
244 ?>
245 <tr>
246 <td class="DataTD"><?=$stats['growth_last_years'][$i]['date'];?></td>
247 <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_users'];?></td>
248 <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_assurers'];?></td>
249 <td class="DataTD"><?=$stats['growth_last_years'][$i]['new_certificates'];?></td>
250 </tr>
251 <? } ?>
252 <tr>
253 <td class="DataTD">N/A</td>
254 <td class="DataTD"><?=$stats['growth_last_years_total']['new_users'];?></td>
255 <td class="DataTD"><?=$stats['growth_last_years_total']['new_assurers'];?></td>
256 <td class="DataTD"><?=$stats['growth_last_years_total']['new_certificates'];?></td>
257 </tr>
258 </table>
259 <br>
260 <?php
261 if (isset($stats['timestamp'])) {
262 ?>
263 <div style="text-align: center;font-size: small;"><?=_("Statistical data from cache, created at ") . date('Y-m-d H:i:s', $stats['timestamp']);?></div>
264 <?php
265 }
266 ?>
267 <? showfooter(); ?>
268