4 LibreSSL - CAcert web application
5 Copyright (C) 2004-2012 CAcert Inc.
7 This program is free software; you can redistribute it and/or modify
8 it under the terms of the GNU General Public License as published by
9 the Free Software Foundation; version 2 of the License.
11 This program is distributed in the hope that it will be useful,
12 but WITHOUT ANY WARRANTY; without even the implied warranty of
13 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 GNU General Public License for more details.
16 You should have received a copy of the GNU General Public License
17 along with this program; if not, write to the Free Software
18 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
21 require_once(dirname(__FILE__
).'/../../includes/mysql.php');
24 * Wrapper around mysqli_query($_SESSION['mconn'], ) to provide some error handling. Prints an error
25 * message and dies if query fails
28 * the SQL statement to execute
29 * @return resource|boolean
30 * the MySQL result set
32 function sql_query($sql) {
33 $res = mysqli_query($_SESSION['mconn'], $sql);
35 fwrite(STDERR
, "MySQL query failed:\n\"$sql\"\n".mysqli_error($_SESSION['mconn']));
43 $row = mysqli_fetch_assoc(sql_query($sql));
44 return(intval($row['count']));
48 * writes new data to cache, create cache or update existing cache, set current
52 function updateCache($stats) {
54 $sql = "insert into `statscache` (`timestamp`, `cache`) values
55 ('$timestamp', '".mysqli_real_escape_string($_SESSION['mconn'], serialize($stats))."')";
58 // Make sure the new statistic was inserted successfully
60 "select 1 from `statscache` where `timestamp` = '$timestamp'");
61 if (mysqli_num_rows($res) !== 1) {
62 fwrite(STDERR
, "Error on inserting the new statistic");
66 sql_query("delete from `statscache` where `timestamp` != '$timestamp'");
71 * get statistics data from live tables, takes a long time so please try to use the
75 function getDataFromLive() {
76 echo "Calculating current statistics\n";
79 $stats['verified_users'] = number_format(tc(
80 "select count(*) as `count` from `users`
85 $stats['verified_emails'] = number_format(tc(
86 "select count(*) as `count` from `email`
87 where `hash` = '' and `deleted` = 0"));
89 $stats['verified_domains'] = number_format(tc(
90 "select count(*) as `count` from `domains`
91 where `hash` = '' and `deleted` = 0"));
93 $certs = tc("select count(*) as `count` from `domaincerts`
94 where `expire` != 0");
95 $certs +
= tc("select count(*) as `count` from `emailcerts`
96 where `expire` != 0");
97 $certs +
= tc("select count(*) as `count` from `gpg`
98 where `expire` != 0");
99 $certs +
= tc("select count(*) as `count` from `orgdomaincerts`
100 where `expire` != 0");
101 $certs +
= tc("select count(*) as `count` from `orgemailcerts`
102 where `expire` != 0");
103 $stats['verified_certificates'] = number_format($certs);
105 $certs = tc("select count(*) as `count` from `domaincerts`
106 where `revoked` = 0 and `expire` > NOW()");
107 $certs +
= tc("select count(*) as `count` from `emailcerts`
108 where `revoked` = 0 and `expire` > NOW()");
109 $certs +
= tc("select count(*) as `count` from `gpg`
110 where `expire` > NOW()");
111 $certs +
= tc("select count(*) as `count` from `orgdomaincerts`
112 where `revoked` = 0 and `expire` > NOW()");
113 $certs +
= tc("select count(*) as `count` from `orgemailcerts`
114 where `revoked` = 0 and `expire` > NOW()");
115 $stats['valid_certificates'] = number_format($certs);
117 $stats['assurances_made'] = number_format(tc(
118 "select count(*) as `count` from `notary`
119 where (`method` = '' or `method` = 'Face to Face Meeting')
120 and `deleted` = 0"));
122 $stats['users_1to49'] = number_format(tc(
123 "select count(*) as `count` from (
124 select 1 from `notary`
127 having sum(`points`) > 0 and sum(`points`) < 50
128 ) as `low_points`"));
130 $stats['users_50to99'] = number_format(tc(
131 "select count(*) as `count` from (
132 select 1 from `notary`
135 having sum(`points`) >= 50 and sum(`points`) < 100
136 ) as `high_points`"));
138 $stats['assurer_candidates'] = number_format(tc(
139 "select count(*) as `count` from `users`
141 select sum(`points`) from `notary`
142 where `to`=`users`.`id`
146 select 1 from `cats_passed` as `cp`, `cats_variant` as `cv`
147 where `cp`.`user_id`=`users`.`id`
148 and `cp`.`variant_id`=`cv`.`id`
153 $stats['aussurers_with_test'] = number_format(tc(
154 "select count(*) as `count` from `users`
156 select sum(`points`) from `notary`
157 where `to`=`users`.`id`
161 select 1 from `cats_passed` as `cp`, `cats_variant` as `cv`
162 where `cp`.`user_id`=`users`.`id`
163 and `cp`.`variant_id`=`cv`.`id`
168 $stats['points_issued'] = number_format(tc(
169 "select sum(greatest(`points`, `awarded`)) as `count` from `notary`
171 and `method` = 'Face to Face Meeting'"));
176 for($i = 0; $i < 12; $i++
) {
177 $first_ts = mktime(0, 0, 0, date("m") - $i, 1, date("Y"));
178 $next_month_ts = mktime(0, 0, 0, date("m") - $i +
1, 1, date("Y"));
179 $first = date("Y-m-d", $first_ts);
180 $next_month = date("Y-m-d", $next_month_ts);
182 echo "Calculating statistics for month $first\n";
184 $totalusers +
= $users = tc(
185 "select count(*) as `count` from `users`
186 where `created` >= '$first' and `created` < '$next_month'
191 $totassurers +
= $assurers = tc(
192 "select count(*) as `count` from (
193 select 1 from `notary`
194 where `when` >= '$first' and `when` < '$next_month'
195 and `method`!='Administrative Increase'
197 group by `to` having sum(`points`) >= 100
198 ) as `assurer_candidates`");
201 "select count(*) as `count` from `domaincerts`
202 where `created` >= '$first' and `created` < '$next_month'
205 "select count(*) as `count` from `emailcerts`
206 where `created` >= '$first' and `created` < '$next_month'
209 "select count(*) as `count` from `gpg`
210 where `issued` >= '$first' and `issued` < '$next_month'
213 "select count(*) as `count` from `orgdomaincerts`
214 where `created` >= '$first' and `created` < '$next_month'
217 "select count(*) as `count` from `orgemailcerts`
218 where `created` >= '$first' and `created` < '$next_month'
220 $totalcerts +
= $certs;
223 $tmp_arr['date'] = date("Y-m", $first_ts);
224 $tmp_arr['new_users'] = number_format($users);
225 $tmp_arr['new_assurers'] = number_format($assurers);
226 $tmp_arr['new_certificates'] = number_format($certs);
228 $stats['growth_last_12m'][] = $tmp_arr;
230 $stats['growth_last_12m_total'] = array(
231 'new_users' => number_format($totalusers),
232 'new_assurers' => number_format($totassurers),
233 'new_certificates' => number_format($totalcerts),
239 for($i = date("Y"); $i >= 2002; $i--) {
240 $first_ts = mktime(0, 0, 0, 1, 1, $i);
241 $next_year_ts = mktime(0, 0, 0, 1, 1, $i +
1);
242 $first = date("Y-m-d", $first_ts);
243 $next_year = date("Y-m-d", $next_year_ts);
245 echo "Calculating statistics for year $i\n";
247 $totalusers +
= $users = tc(
248 "select count(*) as `count` from `users`
249 where `created` >= '$first' and `created` < '$next_year'
254 $totassurers +
= $assurers = tc(
255 "select count(*) as `count` from (
256 select 1 from `notary`
257 where `when` >= '$first' and `when` < '$next_year'
258 and `method`!='Administrative Increase'
260 group by `to` having sum(`points`) >= 100
261 ) as `assurer_candidates`");
264 "select count(*) as `count` from `domaincerts`
265 where `created` >= '$first' and `created` < '$next_year'
268 "select count(*) as `count` from `emailcerts`
269 where `created` >= '$first' and `created` < '$next_year'
272 "select count(*) as `count` from `gpg`
273 where `issued` >= '$first' and `issued` < '$next_year'
276 "select count(*) as `count` from `orgdomaincerts`
277 where `created` >= '$first' and `created` < '$next_year'
280 "select count(*) as `count` from `orgemailcerts`
281 where `created` >= '$first' and `created` < '$next_year'
283 $totalcerts +
= $certs;
286 $tmp_arr['date'] = $i;
287 $tmp_arr['new_users'] = number_format($users);
288 $tmp_arr['new_assurers'] = number_format($assurers);
289 $tmp_arr['new_certificates'] = number_format($certs);
291 $stats['growth_last_years'][] = $tmp_arr;
293 $stats['growth_last_years_total'] = array(
294 'new_users' => number_format($totalusers),
295 'new_assurers' => number_format($totassurers),
296 'new_certificates' => number_format($totalcerts),
303 $stats = getDataFromLive();
304 if (! updateCache($stats) ) {
306 "An error occured. The statistics were not successfully updated!");