function run()
{
$db = new dataBase();
$db->switchDebug(false);
$sql = "SELECT user_id FROM user where user_id >= 0 ";
$params = array();
if (isset($_GET['user_id'])) {
$sql .= ' and user_id=:user_id';
$params['user_id']['value'] = intval($_GET['user_id']);
$params['user_id']['data_type'] = 'integer';
}
$db->paramQuery($sql, $params);
$users = $db->dbResultFetchAll();
set_time_limit(3600);
$total_touched = 0;
foreach ($users as $user) {
$user_id = $user['user_id'];
// repair founds
$founds_count = $db->multiVariableQueryValue("SELECT count(id) FROM cache_logs WHERE deleted=0 AND user_id = :1 AND type=1", 0, $user_id);
$notfounds_count = $db->multiVariableQueryValue("SELECT count(id) FROM cache_logs WHERE deleted=0 AND user_id = :1 AND type=2", 0, $user_id);
$log_notes_count = $db->multiVariableQueryValue("SELECT count(id) FROM cache_logs WHERE deleted=0 AND user_id = :1 AND type=3", 0, $user_id);
$cache_watches = $db->multiVariableQueryValue("SELECT count(id) FROM cache_watches WHERE user_id = :1", 0, $user_id);
$cache_ignores = $db->multiVariableQueryValue("SELECT count(id) FROM cache_ignore WHERE user_id = :1", 0, $user_id);
$hidden_count = $db->multiVariableQueryValue("select count(cache_id) from caches where status in (1,2,3) and user_id = :1", 0, $user_id);
$sql = "\n UPDATE user\n SET\n hidden_count=:new_hidden_count,\n cache_ignores=:new_cache_ignores,\n log_notes_count=:new_log_notes_count,\n founds_count=:new_founds_count,\n notfounds_count=:new_notfounds_count,\n cache_watches=:new_cache_watches\n WHERE\n user_id=:user_id\n AND (\n hidden_count is null\n OR cache_ignores is null\n OR log_notes_count is null\n OR founds_count is null\n OR notfounds_count is null\n OR cache_watches is null\n OR hidden_count!=:new_hidden_count\n OR cache_ignores!=:new_cache_ignores\n OR log_notes_count!=:new_log_notes_count\n OR founds_count!=:new_founds_count\n OR notfounds_count!=:new_notfounds_count\n OR cache_watches!=:new_cache_watches\n )\n ";
$params = array();
$params['new_hidden_count']['value'] = intval($hidden_count);
$params['new_hidden_count']['data_type'] = 'integer';
$params['new_cache_ignores']['value'] = intval($cache_ignores);
$params['new_cache_ignores']['data_type'] = 'integer';
$params['new_log_notes_count']['value'] = intval($log_notes_count);
$params['new_log_notes_count']['data_type'] = 'integer';
$params['new_founds_count']['value'] = intval($founds_count);
$params['new_founds_count']['data_type'] = 'integer';
$params['new_notfounds_count']['value'] = intval($notfounds_count);
$params['new_notfounds_count']['data_type'] = 'integer';
$params['new_cache_watches']['value'] = intval($cache_watches);
$params['new_cache_watches']['data_type'] = 'integer';
$params['user_id']['value'] = intval($user_id);
$params['user_id']['data_type'] = 'integer';
$db->paramQuery($sql, $params);
if ($db->rowCount() > 0) {
echo "<b>user_id={$user_id}</b><br>";
echo "hidden_count={$hidden_count}<br>cache_ignores={$cache_ignores}<br>";
echo "log_notes_count={$log_notes_count}<br>founds_count={$founds_count}<br>";
echo "notfounds_count={$notfounds_count}<br>cache_watches={$cache_watches}<br>";
$total_touched++;
}
$db->closeCursor();
}
set_time_limit(60);
unset($db);
echo "-----------------------------------<br>total_touched={$total_touched}<br>";
}
function run()
{
$db = new dataBase();
$db->switchDebug(false);
$sql = "delete from CACHE_ACCESS_LOGS where date_sub(now(), INTERVAL 5 DAY) > event_date";
set_time_limit(360);
$db->simpleQuery($sql);
$total_deleted = $db->rowCount();
set_time_limit(60);
unset($db);
echo "total_deleted={$total_deleted}\n";
}
function run()
{
$db = new dataBase();
$db->switchDebug(false);
$sql = "SELECT cache_id, status FROM caches";
$params = array();
if (isset($_GET['cache_id'])) {
$sql .= ' where cache_id=:cache_id';
$params['cache_id']['value'] = intval($_GET['cache_id']);
$params['cache_id']['data_type'] = 'integer';
}
$db->paramQuery($sql, $params);
$caches = $db->dbResultFetchAll();
set_time_limit(3600);
$total_touched = 0;
foreach ($caches as $cache) {
$cache_id = $cache['cache_id'];
// usuniecie falszywych ocen
//echo "cache_logs.cache_id=".sql_escape($rs['cache_id']).", user.username="******"<br />";
//$sql = "DELETE FROM scores WHERE cache_id = '".sql_escape($rs['cache_id'])."' AND user_id = '".sql_escape($rs['user_id'])."'";
//mysql_query($sql);
$db->multiVariableQuery("delete from scores where cache_id = :1 and user_id not in (\n select user_id from cache_logs where deleted=0 and cache_id = :2\n )", $cache_id, $cache_id);
// zliczenie ocen po usunieciu
$db->multiVariableQuery("SELECT avg(score) as avg_score, count(score) as votes FROM scores WHERE cache_id = :1", $cache_id);
$row = $db->dbResultFetch();
if ($row == false) {
$liczba = 0;
$srednia = 0;
} else {
$liczba = $row['votes'];
if ($liczba > 0) {
$srednia = round($row['avg_score'], 4);
} else {
$srednia = 0;
}
}
unset($row);
$db->closeCursor();
// repair founds
$founds = $db->multiVariableQueryValue("SELECT count(*) FROM cache_logs WHERE deleted=0 AND cache_id = :1 AND (type=1 OR type=7)", 0, $cache_id);
$notfounds = $db->multiVariableQueryValue("SELECT count(*) FROM cache_logs WHERE deleted=0 AND cache_id = :1 AND (type=2 OR type=8)", 0, $cache_id);
$notes = $db->multiVariableQueryValue("SELECT count(*) FROM cache_logs WHERE deleted=0 AND cache_id = :1 AND type=3", 0, $cache_id);
$watchers = $db->multiVariableQueryValue("SELECT count(*) FROM cache_watches WHERE cache_id = :1", 0, $cache_id);
$ignorers = $db->multiVariableQueryValue("SELECT count(*) FROM cache_ignore WHERE cache_id = :1", 0, $cache_id);
$sql = "\n UPDATE caches\n SET\n votes=:new_votes,\n score=:new_score,\n founds=:new_founds,\n notfounds=:new_notfounds,\n notes=:new_notes,\n watcher=:new_watchers,\n ignorer_count=:new_ignorers\n WHERE\n cache_id=:cache_id\n AND (\n votes is null\n OR score is null\n OR founds is null\n OR notfounds is null\n OR notes is null\n OR watcher is null\n OR ignorer_count is null\n OR votes!=:new_votes\n OR abs(score-:new_score)>0.0001\n OR founds!=:new_founds\n OR notfounds!=:new_notfounds\n OR notes!=:new_notes\n OR watcher!=:new_watchers\n OR ignorer_count!=:new_ignorers\n )\n ";
$params = array();
$params['new_votes']['value'] = intval($liczba);
$params['new_votes']['data_type'] = 'integer';
$params['new_score']['value'] = strval($srednia);
$params['new_score']['data_type'] = 'string';
$params['new_founds']['value'] = intval($founds);
$params['new_founds']['data_type'] = 'integer';
$params['new_notfounds']['value'] = intval($notfounds);
$params['new_notfounds']['data_type'] = 'integer';
$params['new_notes']['value'] = intval($notes);
$params['new_notes']['data_type'] = 'integer';
$params['new_watchers']['value'] = intval($watchers);
$params['new_watchers']['data_type'] = 'integer';
$params['new_ignorers']['value'] = intval($ignorers);
$params['new_ignorers']['data_type'] = 'integer';
$params['cache_id']['value'] = intval($cache_id);
$params['cache_id']['data_type'] = 'integer';
$db->paramQuery($sql, $params);
if ($db->rowCount() > 0) {
echo "<b>cache_id={$cache_id}</b><br>";
echo "ratings={$liczba}<br>rating={$srednia}<br>";
echo "founds={$founds}<br>notfounds={$notfounds}<br>";
echo "notes={$notes}<br>watchers={$watchers}<br>";
echo "ignorers={$ignorers}<br>";
$total_touched++;
}
$db->closeCursor();
}
set_time_limit(60);
unset($db);
echo "-----------------------------------<br>total_touched={$total_touched}<br>";
}