Esempio n. 1
0
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;
}
Esempio n. 2
0
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;
}
Esempio n. 3
0
 * ************************************************************************* */
$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);