scripts/cron/updatesort.php

   1 #!/usr/bin/php -q
   2 <? /*
   3     LibreSSL - CAcert web application
   4     Copyright (C) 2004-2008  CAcert Inc.
   5
   6     This program is free software; you can redistribute it and/or modify
   7     it under the terms of the GNU General Public License as published by
   8     the Free Software Foundation; version 2 of the License.
   9
  10     This program is distributed in the hope that it will be useful,
  11     but WITHOUT ANY WARRANTY; without even the implied warranty of
  12     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13     GNU General Public License for more details.
  14
  15     You should have received a copy of the GNU General Public License
  16     along with this program; if not, write to the Free Software
  17     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  18 */
  19         require_once(dirname(__FILE__).'/../../includes/mysql.php');
  20
  21
  22
  23         /* Set assurer flag for accounts who miss it
  24
  25            See also includes/lib/account.php, function fix_assurer_flag($userID)
  26
  27            We may have some performance problems here, there are 150k assurances and 220k users
  28            in the production database. The exists-clause on cats_passed should be a good filter... */
  29
  30   /* Synchronisation of assurer flag currently deactivated, see https://bugs.cacert.org/view.php?id=1003
  31      and https://bugs.cacert.org/view.php?id=1024 */
  32 /*
  33         $query = "select `n`.`to` as `uid` from `notary` as `n`, `users` as `u` ".
  34                  "  where `n`.`to`=`u`.`id` and `u`.`assurer`<>'1' ".
  35                  "    and (`n`.`expire` > now() OR `n`.`expire` IS NULL) ".
  36                  "    and exists(select 1 from `cats_passed` as `cp`, `cats_variant` as `cv` ".
  37                  "                 where `cp`.`variant_id`=`cv`.`id` and `cv`.`type_id` = 1 and `cp`.`user_id`=`n`.`to`)".
  38                  "  group by `n`.`to` having sum(`n`.`points`)>=100";
  39
  40         $res = mysql_query($query);
  41         while($row = mysql_fetch_assoc($res))
  42         {
  43                 $query = "update users set `assurer`='1' where `id`='${row['uid']}'";
  44                 //echo $query."\n";
  45                 mysql_query($query);
  46         }
  47 */
  48         /* Remove assurer flag from accounts not eligible.
  49
  50            Also a bit performance critical, but assurer flag is only set at 5k accounts
  51
  52         */
  53   /* Synchronisation of assurer flag currently deactivated, see https://bugs.cacert.org/view.php?id=1003
  54      and https://bugs.cacert.org/view.php?id=1024 */
  55 /*
  56     $query = "select `u`.id as `uid` from `users` as `u` " .
  57                  "  where `u`.`assurer` = '1' ".
  58                  "    and (not exists(select 1 from `cats_passed` as `cp`, `cats_variant` as `cv` ".
  59                  "                     where `cp`.`variant_id`=`cv`.`id` and `cv`.`type_id` = 1 and `cp`.`user_id`=`u`.`id`) ".
  60                  "         or (select sum(`n`.`points`) from `notary` as `n` where `n`.`to`=`u`.`id` and (`n`.`expire` > now() OR `n`.`expire` IS NULL)) < 100) ";
  61         $res = mysql_query($query);
  62         while($row = mysql_fetch_assoc($res))
  63         {
  64                 $query = "update users set `assurer`='0' where `id`='${row['uid']}'";
  65                 //echo $query."\n";
  66                 mysql_query($query);
  67         }
  68 */
  69
  70         mysql_query("update `locations` set `acount`=0");
  71         $query = "SELECT `users`.`locid` AS `locid`, count(*) AS `total` FROM `users`
  72                         WHERE users.assurer='1' AND `users`.`locid` != 0 and users.listme=1
  73                         GROUP BY `users`.`locid`";
  74         $res = mysql_query($query);
  75         while($row = mysql_fetch_assoc($res))
  76         {
  77                 $query = "update `locations` set `acount`='${row['total']}' where `id`='${row['locid']}'";
  78                 echo $query."\n";
  79                 mysql_query($query);
  80         }
  81
  82
  83         mysql_query("update `regions` set `acount`=0");
  84         $query = "SELECT `users`.`regid` AS `regid`, count(*) AS `total` FROM `users`
  85                         WHERE users.assurer='1' AND `users`.`regid` != 0 and users.listme=1
  86                         GROUP BY `users`.`regid`";
  87         $res = mysql_query($query);
  88         while($row = mysql_fetch_assoc($res))
  89         {
  90                 $query = "update `regions` set `acount`='${row['total']}' where `id`='${row['regid']}'";
  91                 echo $query."\n";
  92                 mysql_query($query);
  93         }
  94
  95
  96
  97
  98         mysql_query("update `countries` set `acount`=0");
  99         $query = "SELECT `users`.`ccid` AS `ccid`, count(*) AS `total` FROM `users`
 100                         WHERE users.assurer='1' AND `users`.`ccid` != 0 and users.listme=1
 101                         GROUP BY `users`.`ccid`";
 102         $res = mysql_query($query);
 103         while($row = mysql_fetch_assoc($res))
 104         {
 105                 $query = "update `countries` set `acount`='${row['total']}' where `id`='${row['ccid']}'";
 106                 echo $query."\n";
 107                 mysql_query($query);
 108         }
 109
 110
 111 ?>