Source code taken from cacert-20141124.tar.bz2
[cacert.git] / scripts / cron / refresh_stats.php
1 #!/usr/bin/php -q
2 <?php
3 /*
4 LibreSSL - CAcert web application
5 Copyright (C) 2004-2012 CAcert Inc.
6
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.
10
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.
15
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
19 */
20
21 require_once(dirname(__FILE__).'/../../includes/mysql.php');
22
23 /**
24 * Wrapper around mysql_query() to provide some error handling. Prints an error
25 * message and dies if query fails
26 *
27 * @param string $sql
28 * the SQL statement to execute
29 * @return resource|boolean
30 * the MySQL result set
31 */
32 function sql_query($sql) {
33 $res = mysql_query($sql);
34 if (!$res) {
35 fwrite(STDERR, "MySQL query failed:\n\"$sql\"\n".mysql_error());
36 die(1);
37 }
38
39 return $res;
40 }
41
42 function tc($sql) {
43 $row = mysql_fetch_assoc(sql_query($sql));
44 return(intval($row['count']));
45 }
46
47 /**
48 * writes new data to cache, create cache or update existing cache, set current
49 * time stamp
50 * @return boolean
51 */
52 function updateCache($stats) {
53 $timestamp = time();
54 $sql = "insert into `statscache` (`timestamp`, `cache`) values
55 ('$timestamp', '".mysql_real_escape_string(serialize($stats))."')";
56 sql_query($sql);
57
58 // Make sure the new statistic was inserted successfully
59 $res = sql_query(
60 "select 1 from `statscache` where `timestamp` = '$timestamp'");
61 if (mysql_num_rows($res) !== 1) {
62 fwrite(STDERR, "Error on inserting the new statistic");
63 return false;
64 }
65
66 sql_query("delete from `statscache` where `timestamp` != '$timestamp'");
67 return true;
68 }
69
70 /**
71 * get statistics data from live tables, takes a long time so please try to use the
72 * cache
73 * @return array
74 */
75 function getDataFromLive() {
76 echo "Calculating current statistics\n";
77
78 $stats = array();
79 $stats['verified_users'] = number_format(tc(
80 "select count(*) as `count` from `users`
81 where `verified` = 1
82 and `deleted` = 0
83 and `locked` = 0"));
84
85 $stats['verified_emails'] = number_format(tc(
86 "select count(*) as `count` from `email`
87 where `hash` = '' and `deleted` = 0"));
88
89 $stats['verified_domains'] = number_format(tc(
90 "select count(*) as `count` from `domains`
91 where `hash` = '' and `deleted` = 0"));
92
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);
104
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);
116
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"));
121
122 $stats['users_1to49'] = number_format(tc(
123 "select count(*) as `count` from (
124 select 1 from `notary`
125 where `deleted` = 0
126 group by `to`
127 having sum(`points`) > 0 and sum(`points`) < 50
128 ) as `low_points`"));
129
130 $stats['users_50to99'] = number_format(tc(
131 "select count(*) as `count` from (
132 select 1 from `notary`
133 where `deleted` = 0
134 group by `to`
135 having sum(`points`) >= 50 and sum(`points`) < 100
136 ) as `high_points`"));
137
138 $stats['assurer_candidates'] = number_format(tc(
139 "select count(*) as `count` from `users`
140 where (
141 select sum(`points`) from `notary`
142 where `to`=`users`.`id`
143 and `deleted` = 0
144 ) >= 100
145 and not exists(
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`
149 and `cv`.`type_id`=1
150 )"
151 ));
152
153 $stats['aussurers_with_test'] = number_format(tc(
154 "select count(*) as `count` from `users`
155 where (
156 select sum(`points`) from `notary`
157 where `to`=`users`.`id`
158 and `deleted` = 0
159 ) >= 100
160 and exists(
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`
164 and `cv`.`type_id`=1
165 )"
166 ));
167
168 $stats['points_issued'] = number_format(tc(
169 "select sum(greatest(`points`, `awarded`)) as `count` from `notary`
170 where `deleted` = 0
171 and `method` = 'Face to Face Meeting'"));
172
173 $totalusers=0;
174 $totassurers=0;
175 $totalcerts=0;
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);
181
182 echo "Calculating statistics for month $first\n";
183
184 $totalusers += $users = tc(
185 "select count(*) as `count` from `users`
186 where `created` >= '$first' and `created` < '$next_month'
187 and `verified` = 1
188 and `deleted` = 0
189 and `locked` = 0");
190
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'
196 and `deleted` = 0
197 group by `to` having sum(`points`) >= 100
198 ) as `assurer_candidates`");
199
200 $certs = tc(
201 "select count(*) as `count` from `domaincerts`
202 where `created` >= '$first' and `created` < '$next_month'
203 and `expire` != 0");
204 $certs += tc(
205 "select count(*) as `count` from `emailcerts`
206 where `created` >= '$first' and `created` < '$next_month'
207 and `expire` != 0");
208 $certs += tc(
209 "select count(*) as `count` from `gpg`
210 where `issued` >= '$first' and `issued` < '$next_month'
211 and `expire` != 0");
212 $certs += tc(
213 "select count(*) as `count` from `orgdomaincerts`
214 where `created` >= '$first' and `created` < '$next_month'
215 and `expire` != 0");
216 $certs += tc(
217 "select count(*) as `count` from `orgemailcerts`
218 where `created` >= '$first' and `created` < '$next_month'
219 and `expire` != 0");
220 $totalcerts += $certs;
221
222 $tmp_arr = array();
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);
227
228 $stats['growth_last_12m'][] = $tmp_arr;
229 }
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),
234 );
235
236 $totalcerts = 0;
237 $totalusers = 0;
238 $totassurers = 0;
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);
244
245 echo "Calculating statistics for year $i\n";
246
247 $totalusers += $users = tc(
248 "select count(*) as `count` from `users`
249 where `created` >= '$first' and `created` < '$next_year'
250 and `verified` = 1
251 and `deleted` = 0
252 and `locked` = 0");
253
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'
259 and `deleted` = 0
260 group by `to` having sum(`points`) >= 100
261 ) as `assurer_candidates`");
262
263 $certs = tc(
264 "select count(*) as `count` from `domaincerts`
265 where `created` >= '$first' and `created` < '$next_year'
266 and `expire` != 0");
267 $certs += tc(
268 "select count(*) as `count` from `emailcerts`
269 where `created` >= '$first' and `created` < '$next_year'
270 and `expire` != 0");
271 $certs += tc(
272 "select count(*) as `count` from `gpg`
273 where `issued` >= '$first' and `issued` < '$next_year'
274 and `expire` != 0");
275 $certs += tc(
276 "select count(*) as `count` from `orgdomaincerts`
277 where `created` >= '$first' and `created` < '$next_year'
278 and `expire` != 0");
279 $certs += tc(
280 "select count(*) as `count` from `orgemailcerts`
281 where `created` >= '$first' and `created` < '$next_year'
282 and `expire` != 0");
283 $totalcerts += $certs;
284
285 $tmp_arr = array();
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);
290
291 $stats['growth_last_years'][] = $tmp_arr;
292 }
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),
297 );
298
299 return $stats;
300 }
301
302
303 $stats = getDataFromLive();
304 if (! updateCache($stats) ) {
305 fwrite(STDERR,
306 "An error occured. The statistics were not successfully updated!");
307 die(1);
308 }