Merge branch 'bug-1394' into testserver-stable
[cacert-devel.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 AND `method` != 'Administrative Increase' AND `from` != `to`
126 group by `to`
127 having sum(`awarded`) > 0 and sum(`awarded`) < 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 AND `method` != 'Administrative Increase' AND `from` != `to`
134 group by `to`
135 having sum(`awarded`) >= 50 and sum(`awarded`) < 100
136 ) as `high_points`"));
137
138 $startdate = date("Y-m-d", mktime(0, 0, 0, 1, 1, 2002));
139 $enddate = date("Y-m-d", mktime(0, 0, 0, 1, 1, date("Y") + 1));
140
141 $assurercount = assurer_count($startdate, $enddate, 1);
142 $stats['assurer_with_test'] = number_format($assurercount);
143
144 $stats['assurer_candidates'] = number_format(assurer_count($startdate, $enddate,0) - $assurercount);
145
146 $stats['points_issued'] = number_format(tc(
147 "select sum(greatest(`points`, `awarded`)) as `count` from `notary`
148 where `deleted` = 0
149 and `method` = 'Face to Face Meeting'"));
150
151 $totalcerts = 0;
152 $totalusers = 0;
153 $totalcandidates = 0;
154 $totalassurers = 0;
155
156 for($i = 0; $i < 12; $i++) {
157 $first_ts = mktime(0, 0, 0, date("m") - $i, 1, date("Y"));
158 $next_month_ts = mktime(0, 0, 0, date("m") - $i + 1, 1, date("Y"));
159 $first = date("Y-m-d", $first_ts);
160 $next_month = date("Y-m-d", $next_month_ts);
161
162 echo "Calculating statistics for month $first\n";
163
164 $totalusers += $users = tc(
165 "select count(*) as `count` from `users`
166 where `created` >= '$first' and `created` < '$next_month'
167 and `verified` = 1
168 and `deleted` = 0
169 and `locked` = 0");
170
171 $totalcandidates += $candidates = assurer_count($first, $next_month, 0);
172 $totalassurers += $assurers = assurer_count($first, $next_month, 1);
173
174 $certs = tc(
175 "select count(*) as `count` from `domaincerts`
176 where `created` >= '$first' and `created` < '$next_month'
177 and `expire` != 0");
178 $certs += tc(
179 "select count(*) as `count` from `emailcerts`
180 where `created` >= '$first' and `created` < '$next_month'
181 and `expire` != 0");
182 $certs += tc(
183 "select count(*) as `count` from `gpg`
184 where `issued` >= '$first' and `issued` < '$next_month'
185 and `expire` != 0");
186 $certs += tc(
187 "select count(*) as `count` from `orgdomaincerts`
188 where `created` >= '$first' and `created` < '$next_month'
189 and `expire` != 0");
190 $certs += tc(
191 "select count(*) as `count` from `orgemailcerts`
192 where `created` >= '$first' and `created` < '$next_month'
193 and `expire` != 0");
194 $totalcerts += $certs;
195
196 $tmp_arr = array();
197 $tmp_arr['date'] = date("Y-m", $first_ts);
198 $tmp_arr['new_users'] = number_format($users);
199 $tmp_arr['new_candidates'] = number_format($candidates);
200 $tmp_arr['new_assurers'] = number_format($assurers);
201 $tmp_arr['new_certificates'] = number_format($certs);
202
203 $stats['growth_last_12m'][] = $tmp_arr;
204 }
205 $stats['growth_last_12m_total'] = array(
206 'new_users' => number_format($totalusers),
207 'new_candidates' => number_format($totalcandidates),
208 'new_assurers' => number_format($totalassurers),
209 'new_certificates' => number_format($totalcerts),
210 );
211
212 $totalcerts = 0;
213 $totalusers = 0;
214 $totalcandidates = 0;
215 $totalassurers = 0;
216
217 for($i = date("Y"); $i >= 2002; $i--) {
218 $first_ts = mktime(0, 0, 0, 1, 1, $i);
219 $next_year_ts = mktime(0, 0, 0, 1, 1, $i + 1);
220 $first = date("Y-m-d", $first_ts);
221 $next_year = date("Y-m-d", $next_year_ts);
222
223 echo "Calculating statistics for year $i\n";
224
225 $totalusers += $users = tc(
226 "select count(*) as `count` from `users`
227 where `created` >= '$first' and `created` < '$next_year'
228 and `verified` = 1
229 and `deleted` = 0
230 and `locked` = 0");
231
232 $totalcandidates += $candidates = assurer_count($first, $next_year, 0);
233 $totalassurers += $assurers = assurer_count($first, $next_year, 1);
234
235 $certs = tc(
236 "select count(*) as `count` from `domaincerts`
237 where `created` >= '$first' and `created` < '$next_year'
238 and `expire` != 0");
239 $certs += tc(
240 "select count(*) as `count` from `emailcerts`
241 where `created` >= '$first' and `created` < '$next_year'
242 and `expire` != 0");
243 $certs += tc(
244 "select count(*) as `count` from `gpg`
245 where `issued` >= '$first' and `issued` < '$next_year'
246 and `expire` != 0");
247 $certs += tc(
248 "select count(*) as `count` from `orgdomaincerts`
249 where `created` >= '$first' and `created` < '$next_year'
250 and `expire` != 0");
251 $certs += tc(
252 "select count(*) as `count` from `orgemailcerts`
253 where `created` >= '$first' and `created` < '$next_year'
254 and `expire` != 0");
255 $totalcerts += $certs;
256
257 $tmp_arr = array();
258 $tmp_arr['date'] = $i;
259 $tmp_arr['new_users'] = number_format($users);
260 $tmp_arr['new_candidates'] = number_format($candidates);
261 $tmp_arr['new_assurers'] = number_format($assurers);
262 $tmp_arr['new_certificates'] = number_format($certs);
263
264 $stats['growth_last_years'][] = $tmp_arr;
265 }
266 $stats['growth_last_years_total'] = array(
267 'new_users' => number_format($totalusers),
268 'new_candidates' => number_format($totalcandidates),
269 'new_assurers' => number_format($totalassurers),
270 'new_certificates' => number_format($totalcerts),
271 );
272
273 return $stats;
274 }
275
276 /**
277 * assurer_count()
278 * returns the number of new assurer in period given through from and to, type is defining the CATS type that is used as definition to be assurer
279 * @param mixed $from
280 * @param mixed $to
281 * @param integer $type
282 * @return
283 */
284 function assurer_count($from, $to, $type = 1){
285 if ($type == 0) {
286 $atype = "";
287 $btype = "";
288 } else {
289 $atype = " AND n.`to` in (SELECT c.user_id FROM cats_passed as c, cats_variant as v WHERE c.variant_id = v.id and v.type_id = $type and pass_date < @a) ";
290 $btype = " AND n.`to` in (SELECT c.user_id FROM cats_passed as c, cats_variant as v WHERE c.variant_id = v.id and v.type_id = $type and pass_date < @b) ";
291 }
292
293 $query1 = "SET @a = '$from';";
294
295 $query2 = "SET @b = '$to';";
296
297 $query3 = "CREATE TEMPORARY TABLE a
298 SELECT n.`to`, sum(n.awarded) as `received_pts`, max(n.`when`) as `last_assurance`
299 FROM cacert.notary as n
300 WHERE 1
301 AND n.`from` != n.`to`
302 AND (n.`deleted` = '0000-00-00 00:00:00')
303 AND n.`when` < @a
304 $atype
305 GROUP by n.`to`
306 HAVING 1
307 AND `received_pts` >= 100
308 ORDER by `last_assurance` DESC;";
309
310 $query4 = "CREATE TEMPORARY TABLE b
311 SELECT n.`to`, sum(n.awarded) as `received_pts`, max(n.`when`) as `last_assurance`
312 FROM cacert.notary as n
313 WHERE 1
314 AND n.`from` != n.`to`
315 AND (n.`deleted` = '0000-00-00 00:00:00')
316 AND n.`when` < @b
317 $btype
318 GROUP by n.`to`
319 HAVING 1
320 AND `received_pts` >= 100
321 ORDER by `last_assurance` DESC;";
322
323 $query5 = "SELECT count(*) as `count` FROM b WHERE b.`to` NOT IN (SELECT a.`to` FROM a);";
324
325 $query6 = "DROP TEMPORARY TABLE a;";
326
327 $query7 = "DROP TEMPORARY TABLE b;";
328
329
330 sql_query($query1);
331 sql_query($query2);
332 sql_query($query3);
333 sql_query($query4);
334
335 $row = mysql_fetch_assoc(sql_query($query5));
336
337 sql_query($query6);
338 sql_query($query7);
339
340 return(intval($row['count']));
341 }
342
343
344 $stats = getDataFromLive();
345 if (! updateCache($stats) ) {
346 fwrite(STDERR,
347 "An error occured. The statistics were not successfully updated!");
348 die(1);
349 }