Ejemplo n.º 1
0
 public function run()
 {
     // cleanup XML session data
     sql_temp_table('tmpsessiondata');
     sql('CREATE TEMPORARY TABLE &tmpsessiondata ENGINE=MEMORY
          SELECT DISTINCT `xmlsession_data`.`session_id` FROM `xmlsession_data`
          LEFT JOIN `xmlsession` ON `xmlsession`.`id`=`xmlsession_data`.`session_id`
          WHERE `xmlsession`.`id` IS NULL');
     $count = sql_value('SELECT COUNT(*) FROM `xmlsession_data`
          WHERE `session_id` IN (SELECT `session_id` FROM &tmpsessiondata)', 0);
     if ($count) {
         sql("DELETE FROM `xmlsession_data`\n                 WHERE `session_id` IN (SELECT `session_id` FROM &tmpsessiondata)");
         echo 'orphan_cleanup: dropped ' . $count . " record(s) from xmlsession_data\n";
     }
     sql_drop_temp_table('tmpsessiondata');
     // cleanup map data
     sql_temp_table('tmpsessiondata');
     sql('CREATE TEMPORARY TABLE &tmpsessiondata ENGINE=MEMORY
          SELECT DISTINCT `map2_data`.`result_id` FROM `map2_data`
          LEFT JOIN `map2_result` ON `map2_result`.`result_id`=`map2_data`.`result_id`
          WHERE `map2_result`.`result_id` IS NULL');
     $count = sql_value("SELECT COUNT(*) FROM `map2_data`\n             WHERE `result_id` IN (SELECT `result_id` FROM &tmpsessiondata)", 0);
     if ($count) {
         sql("DELETE FROM `map2_data`\n                 WHERE `result_id` IN (SELECT `result_id` FROM &tmpsessiondata)");
         echo 'orphan_cleanup: dropped ' . $count . " record(s) from map2_data\n";
     }
     sql_drop_temp_table('tmpsessiondata');
 }
 function run()
 {
     sql("DELETE FROM `rating_tops`");
     sql_temp_table('topLocationCaches');
     sql_temp_table('topRatings');
     sql_temp_table('topResult');
     sql("CREATE TEMPORARY TABLE &topLocationCaches (`cache_id` INT(11) PRIMARY KEY) ENGINE=MEMORY");
     sql("CREATE TEMPORARY TABLE &topRatings (`cache_id` INT(11) PRIMARY KEY, `ratings` INT(11)) ENGINE=MEMORY");
     sql("CREATE TEMPORARY TABLE &topResult (`idx` INT(11), `cache_id` INT(11) PRIMARY KEY, `ratings` INT(11), `founds` INT(11)) ENGINE=MEMORY");
     $rsCountry = sql('SELECT SQL_BUFFER_RESULT SQL_SMALL_RESULT DISTINCT `country` FROM `caches`');
     while ($rCountry = sql_fetch_assoc($rsCountry)) {
         $rsAdm3 = sql("\n\t\t\t\tSELECT SQL_BUFFER_RESULT SQL_SMALL_RESULT DISTINCT \n\t\t\t\t  IF(`cache_location`.`code1`=`caches`.`country`,`cache_location`.`code3`,NULL) `code3`\n\t\t\t\tFROM `caches`\n\t\t\t\tLEFT JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id`\n\t\t\t\tWHERE `caches`.`country`='&1'", $rCountry['country']);
         while ($rAdm3 = sql_fetch_assoc($rsAdm3)) {
             sql("TRUNCATE TABLE &topLocationCaches");
             sql("TRUNCATE TABLE &topRatings");
             sql("TRUNCATE TABLE &topResult");
             // Alle Caches für diese Gruppe finden
             if ($rAdm3['code3'] == null) {
                 sql("\n\t\t\t\t\t\tINSERT INTO &topLocationCaches (`cache_id`)\n\t\t\t\t\t\t\tSELECT `caches`.`cache_id`\n\t\t\t\t\t\t\tFROM `cache_location`\n\t\t\t\t\t\t\tINNER JOIN `caches` ON `caches`.`cache_id`=`cache_location`.`cache_id`\n\t\t\t\t\t\t\tLEFT JOIN `stat_caches` ON `caches`.`cache_id`=`stat_caches`.`cache_id`\n\t\t\t\t\t\t\tWHERE IFNULL(`stat_caches`.`toprating`,0)>0 AND `cache_location`.`code1`='&1' AND ISNULL(`cache_location`.`code3`) AND `caches`.`status`=1", $rCountry['country']);
             } else {
                 sql("\n\t\t\t\t\t\tINSERT INTO &topLocationCaches (`cache_id`)\n\t\t\t\t\t\tSELECT `caches`.`cache_id`\n\t\t\t\t\t\tFROM `cache_location`\n\t\t\t\t\t\tINNER JOIN `caches` ON `caches`.`cache_id`=`cache_location`.`cache_id`\n\t\t\t\t\t\tLEFT JOIN `stat_caches` ON `caches`.`cache_id`=`stat_caches`.`cache_id`\n\t\t\t\t\t\tWHERE IFNULL(`stat_caches`.`toprating`,0)>0 AND `cache_location`.`code1`='&1' AND `cache_location`.`code3`='&2' AND `caches`.`status`=1", $rCountry['country'], $rAdm3['code3']);
             }
             sql("\n\t\t\t\t\tINSERT INTO &topRatings (`cache_id`, `ratings`)\n\t\t\t\t\tSELECT `cache_rating`.`cache_id`, COUNT(`cache_rating`.`cache_id`) AS `ratings`\n\t\t\t\t\tFROM `cache_rating`\n\t\t\t\t\tINNER JOIN &topLocationCaches ON `cache_rating`.`cache_id`=&topLocationCaches.`cache_id`\n\t\t\t\t\tINNER JOIN `caches` ON `cache_rating`.`cache_id`=`caches`.`cache_id`\n\t\t\t\t\tWHERE `cache_rating`.`user_id`!=`caches`.`user_id`\n\t\t\t\t\tGROUP BY `cache_rating`.`cache_id`");
             sql("INSERT INTO &topResult (`idx`, `cache_id`, `ratings`, `founds`) \n\t\t\t\t     SELECT SQL_SMALL_RESULT (&topRatings.`ratings`+1)*(&topRatings.`ratings`+1)/(IFNULL(`stat_caches`.`found`, 0)/10+1)*100 AS `idx`, \n\t\t\t\t            &topRatings.`cache_id`,\n\t\t\t\t            &topRatings.`ratings`, \n\t\t\t\t            IFNULL(`stat_caches`.`found`, 0) AS founds\n\t\t\t\t       FROM &topRatings\n\t\t\t\t INNER JOIN `caches` ON &topRatings.`cache_id`=`caches`.`cache_id`\n\t\t\t\t  LEFT JOIN `stat_caches` ON `stat_caches`.`cache_id`=`caches`.`cache_id`\n\t\t\t\t   ORDER BY `idx` DESC LIMIT 15");
             if (sql_value("SELECT COUNT(*) FROM &topResult", 0) > 10) {
                 $min_idx = sql_value("SELECT `idx` FROM &topResult ORDER BY idx DESC LIMIT 9, 1", 0);
                 sql("DELETE FROM &topResult WHERE `idx`<'&1'", $min_idx);
             }
             sql("INSERT INTO `rating_tops` (`cache_id`, `rating`)\n\t\t\t\t     SELECT SQL_BUFFER_RESULT &topResult.`cache_id`, \n\t\t\t\t\t\t        &topResult.`idx` AS `rating`\n\t\t\t\t       FROM &topResult\n\t\t\t\t   ORDER BY `rating` DESC");
         }
         sql_free_result($rsAdm3);
     }
     sql_free_result($rsCountry);
     sql_drop_temp_table('topLocationCaches');
     sql_drop_temp_table('topRatings');
     sql_drop_temp_table('topResult');
 }
Ejemplo n.º 3
0
function resetIds()
{
    global $translang, $tpl;
    if (sql_connect_root() == false) {
        $tpl->error(ERROR_DB_NO_ROOT);
    }
    // clean up dead refs
    sql_temp_table('transDeadIds');
    sql("CREATE TEMPORARY TABLE &transDeadIds (`trans_id` INT(11) PRIMARY KEY) SELECT `sys_trans_ref`.`trans_id` FROM `sys_trans_ref` LEFT JOIN `sys_trans` ON `sys_trans_ref`.`trans_id`=`sys_trans`.`id` WHERE ISNULL(`sys_trans`.`id`)");
    sql("DELETE `sys_trans_ref` FROM `sys_trans_ref`, &transDeadIds WHERE `sys_trans_ref`.`trans_id`=&transDeadIds.`trans_id`");
    sql_drop_temp_table('transDeadIds');
    sql_temp_table('transDeadIds');
    sql("CREATE TEMPORARY TABLE &transDeadIds (`trans_id` INT(11) PRIMARY KEY) SELECT `sys_trans_text`.`trans_id` FROM `sys_trans_text` LEFT JOIN `sys_trans` ON `sys_trans_text`.`trans_id`=`sys_trans`.`id` WHERE ISNULL(`sys_trans`.`id`)");
    sql("DELETE `sys_trans_text` FROM `sys_trans_text`, &transDeadIds WHERE `sys_trans_text`.`trans_id`=&transDeadIds.`trans_id`");
    sql_drop_temp_table('transDeadIds');
    // table sys_trans
    if (sql_value("SELECT COUNT(*) FROM `sys_trans` WHERE `id`=1", 0) == 0) {
        useId(1);
    }
    $lastId = sql_value("SELECT MAX(`id`) FROM `sys_trans`", 0);
    while ($id = sql_value("SELECT `s1`.`id`+1 FROM `sys_trans` AS `s1` LEFT JOIN `sys_trans` AS `s2` ON `s1`.`id`+1=`s2`.`id` WHERE ISNULL(`s2`.`id`) AND `s1`.`id`<'&1' ORDER BY `s1`.`id` LIMIT 1", 0, $lastId)) {
        if ($lastId + 1 == $id) {
            break;
        }
        setId($lastId, $id);
        $lastId = sql_value("SELECT MAX(`id`) FROM `sys_trans`", 0);
    }
    // need alter privileges
    $lastId = sql_value("SELECT MAX(`id`) FROM `sys_trans`", 0);
    sql("ALTER TABLE `sys_trans` AUTO_INCREMENT = &1", $lastId + 1);
    $tpl->redirect('translate.php?translang=' . $translang);
}
Ejemplo n.º 4
0
function copy_english_texts()
{
    sql_temp_table('transtmp');
    sql("\n\t\t\tCREATE TEMPORARY TABLE &transtmp\n\t\t\tSELECT `st`.`id` AS `trans_id`, 'EN' AS `lang`, `st`.`text`\n\t\t\tFROM `sys_trans` `st`\n\t    LEFT JOIN `sys_trans_text` `stt` ON `stt`.`trans_id`=`st`.`id` AND `stt`.`lang`='EN'\n\t    WHERE `stt`.`trans_id` IS NULL");
    sql("\n\t\t\tINSERT INTO `sys_trans_text`\n\t\t\tSELECT *,NULL FROM &transtmp");
    sql_drop_temp_table('transtmp');
}
Ejemplo n.º 5
0
<?php

/***************************************************************************
 *  For license information see doc/license.txt
 *
 *  Unicode Reminder メモ
 ***************************************************************************/
require 'lib2/web.inc.php';
require 'lib2/logic/oconly81.inc.php';
$showall = @$_REQUEST['showall'] == 1;
$tpl->name = 'oconly81';
$tpl->menuitem = MNU_CACHES_OCONLY81;
$tpl->caching = true;
$tpl->cache_lifetime = 900;
$tpl->cache_id = $showall ? 1 : 0;
$login->verify();
sql_temp_table('oconly81');
sql("\n\t\tCREATE TEMPORARY TABLE &oconly81 ENGINE=MEMORY\n\t\tSELECT DISTINCT `user`.`user_id`, `caches`.`terrain`, `caches`.`difficulty`\n\t\tFROM `user`\n\t\tINNER JOIN `cache_logs` ON `cache_logs`.`user_id`=`user`.`user_id` AND `cache_logs`.`type` IN (1,7)\n\t\tINNER JOIN `caches` ON `caches`.`cache_id`=`cache_logs`.`cache_id`\n\t\tINNER JOIN `caches_attributes` ON `caches_attributes`.`cache_id`=`cache_logs`.`cache_id` AND `caches_attributes`.`attrib_id`=6\n\t\tINNER JOIN `user_options` ON `user_options`.`user_id`=`user`.`user_id`\n\t\tWHERE `user_options`.`option_id`=13 AND `user_options`.`option_value`='1'");
// users with 0 OConly founds are filtered out here
$rs = sql("\n\t\tSELECT `user`.`username`, `user`.`user_id`, COUNT(*) AS `count`\n\t\tFROM `user`\n\t\tINNER JOIN &oconly81 ON &oconly81.`user_id`=`user`.`user_id`\n\t\tGROUP BY `user`.`user_id`\n\t\tORDER BY `count` DESC, `username` ASC " . ($showall ? "" : "LIMIT " . sql_escape($opt['logic']['oconly81']['default_maxusers'] + 1)));
$tpl->assign_rs('users', $rs);
sql_free_result($rs);
sql_drop_temp_table('oconly81');
set_oconly81_tpldata(0);
$tpl->assign('default_maxusers', $opt['logic']['oconly81']['default_maxusers']);
$tpl->assign('showall', $showall);
$tpl->display();