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');
}
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);
}
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');
}
<?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();