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 mysqli_query($_SESSION['mconn'], ) 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 = mysqli_query($_SESSION['mconn'], $sql);
  34         if (!$res) {
  35                 fwrite(STDERR, "MySQL query failed:\n\"$sql\"\n".mysqli_error($_SESSION['mconn']));
  36                 die(1);
  37         }
  38
  39         return $res;
  40 }
  41
  42 function tc($sql) {
  43         $row = mysqli_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', '".mysqli_real_escape_string($_SESSION['mconn'], 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 (mysqli_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 }