function startXmlSession($sModifiedSince, $bCache, $bCachedesc, $bCachelog, $bUser, $bPicture, $bRemovedObject, $bPictureFromCachelog, $selection)
{
global $rootpath;
// session anlegen
XDb::xSql('INSERT INTO `xmlsession` (`last_use`, `modified_since`, `date_created`)
VALUES (NOW(), ?, NOW())', date('Y-m-d H:i:s', strtotime($sModifiedSince)));
$sessionid = XDb::xLastInsertId();
$recordcount['caches'] = 0;
$recordcount['cachedescs'] = 0;
$recordcount['cachelogs'] = 0;
$recordcount['users'] = 0;
$recordcount['pictures'] = 0;
$recordcount['removedobjects'] = 0;
if ($selection['type'] == 0) {
// ohne selection
if ($bCache == 1) {
$stmt = XDb::xSql("INSERT INTO xmlsession_data (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 2, `cache_id` FROM `caches`\n WHERE `last_modified` >= ? AND `status`!=5 AND `status`!=6 AND `status`!=4", $sModifiedSince);
$recordcount['caches'] = XDb::xNumRows($stmt);
}
if ($bCachedesc == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 3, `cache_desc`.`id`\n FROM `cache_desc` INNER JOIN `caches` ON `cache_desc`.`cache_id`=`caches`.`cache_id`\n WHERE `cache_desc`.`last_modified` >= ? AND `caches`.`status`!=5\n AND `status`!=6 AND `status`!=4", $sModifiedSince);
$recordcount['cachedescs'] = XDb::xNumRows($stmt);
}
if ($bCachelog == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 1, `cache_logs`.`id`\n FROM `cache_logs` INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n WHERE `cache_logs`.`last_modified` >= ? AND `caches`.`status`!=5\n AND `status`!=6 AND `status`!=4 AND `cache_logs`.`deleted`=0", $sModifiedSince);
$recordcount['cachelogs'] = XDb::xNumRows($stmt);
}
if ($bUser == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 4, `user_id` FROM `user` WHERE `last_modified` >= ? ", $sModifiedSince);
$recordcount['users'] = XDb::xNumRows($stmt);
}
if ($bPicture == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 6, `pictures`.`id`\n FROM `pictures`\n INNER JOIN `caches` ON `pictures`.`object_type`=2\n AND `pictures`.`object_id`=`caches`.`cache_id`\n WHERE `pictures`.`last_modified` >= ?\n AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4\n UNION DISTINCT\n SELECT {$sessionid}, 6, `pictures`.`id`\n FROM `pictures`\n INNER JOIN `cache_logs` ON `pictures`.`object_type`=1\n AND `pictures`.`object_id`=`cache_logs`.`id`\n INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n WHERE `pictures`.`last_modified` >= ?\n AND `caches`.`status`!=5 AND `status`!=6 AND `caches`.`status`!=4\n AND `cache_logs`.`deleted`=0", $sModifiedSince, $sModifiedSince);
$recordcount['pictures'] = XDb::xNumRows($stmt);
}
if ($bRemovedObject == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 7, `id` FROM `removed_objects`\n WHERE `removed_date` >= ? ", $sModifiedSince);
$recordcount['removedobjects'] = XDb::xNumRows($stmt);
}
} else {
$qWhere = '';
$qHaving = '';
if ($selection['type'] == 1) {
XDb::xSql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n SELECT DISTINCT `cache_countries`.`cache_id`\n FROM `caches`, `cache_countries`\n WHERE `caches`.`cache_id`=`cache_countries`.`cache_id` AND `cache_countries`.`country`= ?\n AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4", $selection['country']);
} else {
if ($selection['type'] == 2) {
require_once $rootpath . 'lib/search.inc.php';
XDb::xSql('CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), `distance` double, KEY (`cache_id`)) ENGINE=MEMORY
SELECT `cache_coordinates`.`cache_id`,' . getSqlDistanceFormula($selection['lon'], $selection['lat'], $selection['distance'], 'cache_coordinates') . ' `distance`
FROM `caches`, `cache_coordinates`
WHERE `cache_coordinates`.`cache_id`=`caches`.`cache_id` AND `caches`.`status`!=5
AND `status`!=6 AND `status`!=4
AND `cache_coordinates`.`latitude` > ' . getMinLat($selection['lon'], $selection['lat'], $selection['distance']) . '
AND `cache_coordinates`.`latitude` < ' . getMaxLat($selection['lon'], $selection['lat'], $selection['distance']) . '
AND `cache_coordinates`.`longitude` >' . getMinLon($selection['lon'], $selection['lat'], $selection['distance']) . '
AND `cache_coordinates`.`longitude` < ' . getMaxLon($selection['lon'], $selection['lat'], $selection['distance']) . '
HAVING `distance` < ' . ($selection['distance'] + 0));
} else {
if ($selection['type'] == 3) {
XDb::xSql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n SELECT " . $selection['cacheid'] . " AS cache_id");
}
}
}
if ($bCache == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 2, `tmpxmlSesssionCaches`.`cache_id`\n FROM `tmpxmlSesssionCaches`, `caches`\n WHERE `tmpxmlSesssionCaches`.`cache_id`=`caches`.`cache_id`\n AND `caches`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['caches'] = XDb::xNumRows($stmt);
}
if ($bCachedesc == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 3, `cache_desc`.`id`\n FROM `cache_desc`, `tmpxmlSesssionCaches`\n WHERE `cache_desc`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id`\n AND `cache_desc`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['cachedescs'] = XDb::xNumRows($stmt);
}
if ($bCachelog == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 1, `cache_logs`.`id`\n FROM `cache_logs`, `tmpxmlSesssionCaches`\n WHERE `cache_logs`.`deleted`=0\n AND `cache_logs`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id`\n AND `cache_logs`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['cachelogs'] = XDb::xNumRows($stmt);
}
if ($bPicture == 1) {
// cachebilder
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 6, `pictures`.`id`\n FROM `pictures`, `tmpxmlSesssionCaches`\n WHERE `pictures`.`object_id`=`tmpxmlSesssionCaches`.`cache_id`\n AND `pictures`.`object_type`=2\n AND `pictures`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['pictures'] = XDb::xNumRows($stmt);
// bilder von logs
if ($bPictureFromCachelog == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 6, `pictures`.id\n FROM `pictures` , `cache_logs`, `tmpxmlSesssionCaches`\n WHERE `tmpxmlSesssionCaches`.`cache_id`=`cache_logs`.`cache_id`\n AND `cache_logs`.`deleted`=0\n AND `pictures`.`object_type`=1\n AND `pictures`.`object_id`=`cache_logs`.`id`\n AND `pictures`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['pictures'] += XDb::xNumRows($stmt);
}
}
if ($bRemovedObject == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 7, `id`\n FROM `removed_objects`\n WHERE `removed_date` >= ? ", $sModifiedSince);
$recordcount['removedobjects'] = XDb::xNumRows($stmt);
}
}
XDb::xSql('UPDATE `xmlsession` SET `caches`= ?, `cachedescs`= ?, `cachelogs`= ?, `users`= ?, `pictures`= ?, `removedobjects`= ?
WHERE `id`= ? LIMIT 1', $recordcount['caches'], $recordcount['cachedescs'], $recordcount['cachelogs'], $recordcount['users'], $recordcount['pictures'], $recordcount['removedobjects'], $sessionid);
return $sessionid;
}
function startXmlSession($sModifiedSince, $bCache, $bCachedesc, $bCachelog, $bUser, $bPicture, $bRemovedObject, $bPictureFromCachelog, $selection)
{
global $rootpath;
// session anlegen
sql('INSERT INTO `xmlsession` (`last_use`, `modified_since`, `date_created`) VALUES (NOW(), \'&1\', NOW())', date('Y-m-d H:i:s', strtotime($sModifiedSince)));
$sessionid = mysql_insert_id();
$recordcount['caches'] = 0;
$recordcount['cachedescs'] = 0;
$recordcount['cachelogs'] = 0;
$recordcount['users'] = 0;
$recordcount['pictures'] = 0;
$recordcount['removedobjects'] = 0;
if ($selection['type'] == 0) {
// ohne selection
if ($bCache == 1) {
sql("INSERT INTO xmlsession_data (`session_id`, `object_type`, `object_id`)\n SELECT &1, 2, `cache_id` FROM `caches` WHERE `last_modified` >= '&2' AND `status`!=5 AND `status`!=6 AND `status`!=4", $sessionid, $sModifiedSince);
$recordcount['caches'] = mysql_affected_rows();
}
if ($bCachedesc == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 3, `cache_desc`.`id` FROM `cache_desc` INNER JOIN `caches` ON `cache_desc`.`cache_id`=`caches`.`cache_id` WHERE `cache_desc`.`last_modified` >= '&2' AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4", $sessionid, $sModifiedSince);
$recordcount['cachedescs'] = mysql_affected_rows();
}
if ($bCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 1, `cache_logs`.`id` FROM `cache_logs` INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id` WHERE `cache_logs`.`last_modified` >= '&2' AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4 AND `cache_logs`.`deleted`=0", $sessionid, $sModifiedSince);
$recordcount['cachelogs'] = mysql_affected_rows();
}
if ($bUser == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 4, `user_id` FROM `user` WHERE `last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['users'] = mysql_affected_rows();
}
if ($bPicture == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n `caches` ON `pictures`.`object_type`=2 AND\n `pictures`.`object_id`=`caches`.`cache_id`\n WHERE `pictures`.`last_modified` >= '&2' AND\n `caches`.`status`!=5 AND `status`!=6 AND `status`!=4\n UNION DISTINCT\n SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n `cache_logs` ON `pictures`.`object_type`=1 AND\n `pictures`.`object_id`=`cache_logs`.`id` INNER JOIN\n `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n WHERE `pictures`.`last_modified` >= '&2' AND\n `caches`.`status`!=5 AND `status`!=6 AND `status`!=4 AND `cache_logs`.`deleted`=0", $sessionid, $sModifiedSince);
$recordcount['pictures'] = mysql_affected_rows();
}
if ($bRemovedObject == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['removedobjects'] = mysql_affected_rows();
}
} else {
$sqlWhere = '';
$sqlHaving = '';
if ($selection['type'] == 1) {
sql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n SELECT DISTINCT `cache_countries`.`cache_id` FROM `caches`, `cache_countries` WHERE `caches`.`cache_id`=`cache_countries`.`cache_id` AND `cache_countries`.`country`='&1' AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4", $selection['country']);
} else {
if ($selection['type'] == 2) {
require_once $rootpath . 'lib/search.inc.php';
$sql = 'CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), `distance` double, KEY (`cache_id`)) ENGINE=MEMORY ';
$sql .= 'SELECT `cache_coordinates`.`cache_id`, ';
$sql .= getSqlDistanceFormula($selection['lon'], $selection['lat'], $selection['distance'], 'cache_coordinates') . ' `distance` ';
$sql .= 'FROM `caches`, `cache_coordinates` WHERE ';
$sql .= '`cache_coordinates`.`cache_id`=`caches`.`cache_id`';
$sql .= ' AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4';
$sql .= ' AND `cache_coordinates`.`latitude` > ' . getMinLat($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`latitude` < ' . getMaxLat($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`longitude` >' . getMinLon($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`longitude` < ' . getMaxLon($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' HAVING `distance` < ' . ($selection['distance'] + 0);
sql($sql);
} else {
if ($selection['type'] == 3) {
sql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n SELECT '&1' AS cache_id", $selection['cacheid'] + 0);
}
}
}
if ($bCache == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 2, `tmpxmlSesssionCaches`.`cache_id` FROM `tmpxmlSesssionCaches`, `caches`\n WHERE `tmpxmlSesssionCaches`.`cache_id`=`caches`.`cache_id` AND `caches`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['caches'] = mysql_affected_rows();
}
if ($bCachedesc == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 3, `cache_desc`.`id` FROM `cache_desc`, `tmpxmlSesssionCaches`\n WHERE `cache_desc`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id` AND `cache_desc`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['cachedescs'] = mysql_affected_rows();
}
if ($bCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 1, `cache_logs`.`id` FROM `cache_logs`, `tmpxmlSesssionCaches`\n WHERE `cache_logs`.`deleted`=0 AND `cache_logs`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id` AND `cache_logs`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['cachelogs'] = mysql_affected_rows();
}
if ($bPicture == 1) {
// cachebilder
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 6, `pictures`.`id` FROM `pictures`, `tmpxmlSesssionCaches`\n WHERE `pictures`.`object_id`=`tmpxmlSesssionCaches`.`cache_id` AND `pictures`.`object_type`=2 AND\n `pictures`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['pictures'] = mysql_affected_rows();
// bilder von logs
if ($bPictureFromCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 6, `pictures`.id FROM `pictures` , `cache_logs`, `tmpxmlSesssionCaches`\n WHERE `tmpxmlSesssionCaches`.`cache_id`=`cache_logs`.`cache_id` AND `cache_logs`.`deleted`=0 AND\n `pictures`.`object_type`=1 AND `pictures`.`object_id`=`cache_logs`.`id` AND\n `pictures`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['pictures'] += mysql_affected_rows();
}
}
if ($bRemovedObject == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['removedobjects'] = mysql_affected_rows();
}
}
sql('UPDATE `xmlsession` SET `caches`=&1, `cachedescs`=&2, `cachelogs`=&3, `users`=&4, `pictures`=&5, `removedobjects`=&6 WHERE `id`=&7 LIMIT 1', $recordcount['caches'], $recordcount['cachedescs'], $recordcount['cachelogs'], $recordcount['users'], $recordcount['pictures'], $recordcount['removedobjects'], $sessionid);
return $sessionid;
}
* ************************************************************************* */
$rootpath = '../../';
require_once $rootpath . 'lib/clicompatbase.inc.php';
require_once $rootpath . 'lib/search.inc.php';
require_once $rootpath . 'tpl/stdstyle/selectlocid.inc.php';
/* begin db connect */
db_connect();
if ($dblink === false) {
echo 'Unable to connect to database';
exit;
}
/* end db connect */
/* begin search index rebuild */
$rsLocations = sql("SELECT `uni`, `lat`, `lon`, `rc`, `cc1`, `adm1` FROM `gns_locations` WHERE `dsg` LIKE 'PPL%'");
while ($rLocations = sql_fetch_array($rsLocations)) {
$minlat = getMinLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlat = getMaxLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$minlon = getMinLon($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlon = getMaxLon($rLocations['lon'], $rLocations['lat'], 10, 1);
// den nächsgelegenen Ort in den geodb ermitteln
$sql = 'SELECT ' . getSqlDistanceFormula($rLocations['lon'], $rLocations['lat'], 10, 1, 'lon', 'lat', 'geodb_coordinates') . ' `distance`,
`geodb_coordinates`.`loc_id` `loc_id`
FROM `geodb_coordinates`
WHERE `lon` > ' . $minlon . ' AND
`lon` < ' . $maxlon . ' AND
`lat` > ' . $minlat . ' AND
`lat` < ' . $maxlat . '
HAVING `distance` < 10
ORDER BY `distance` ASC
LIMIT 1';
$rs = sql($sql);