function storeAlitudeToDb($altitudes, $caches, &$cachesAltitudeCount) { $status = (string) $altitudes->status; if ($status !== 'OK') { print 'error occured'; d($caches, $altitudes); return; } $db = \lib\Database\DataBaseSingleton::Instance(); $i = 0; foreach ($altitudes->result as $key => $value) { $lat = (string) $value->location->lat; $lon = (string) $value->location->lng; $alt = (string) $value->elevation; $altInt = (int) round($alt); if (round($caches[$i]['latitude'], 7) == $lat && round($caches[$i]['longitude'], 7) == $lon) { $query2 = 'INSERT INTO caches_additions (cache_id, altitude) VALUES (:2, :1) '; $db->multiVariableQuery($query2, $altInt, $caches[$i]['cache_id']); } // d($altInt, $key, $value, $lat, $lon, $caches[$i]); $i++; $cachesAltitudeCount++; } return $cachesAltitudeCount; }
public static function isTitled($cacheId) { $queryPt = 'SELECT ratio FROM cache_titled WHERE cache_id=:1'; $db = \lib\Database\DataBaseSingleton::Instance(); $db->multiVariableQuery($queryPt, $cacheId); return $db->rowCount(); }
private function freeCacheCandidates() { $db = DataBaseSingleton::Instance(); $query = 'DELETE FROM `PowerTrail_cacheCandidate` WHERE `date` < DATE_SUB(curdate(), INTERVAL 2 WEEK)'; $db->simpleQuery($query); $db->reset(); }
private function getPlacedCacheCount(User $user) { $query = 'SELECT count(caches.cache_id) as cacheCount FROM `caches` ' . 'WHERE `caches`.`user_id` = :1 AND `caches`.`status` IN ( :2 ) AND `caches`.`date_created` > :3 ' . 'AND `caches`.`type` IN ( :4 ) '; $db = DataBaseSingleton::Instance(); $db->multiVariableQuery($query, $user->getUserId(), $this->buildCacheStatusSqlString(), $this->dateIntroduced, $this->buildCacheTypesSqlString()); $dbResult = $db->dbResultFetchOneRowOnly(); return $dbResult['cacheCount']; }
private function getGeocacherDays(User $user) { $db = \lib\Database\DataBaseSingleton::Instance(); $query = 'SELECT period_diff(date_format(now(), "%Y%m"), date_format( `date_created`, "%Y%m")) as months FROM `user` WHERE user_id = :1 LIMIT 1'; $db->multiVariableQuery($query, $user->getUserId()); $dbResult = $db->dbResultFetchOneRowOnly(); return $dbResult['months']; }
private function getPlacedCount(User $user) { $db = \lib\Database\DataBaseSingleton::Instance(); $placedCountQuery = 'SELECT count(*) as cacheCount FROM `caches_additions`, caches WHERE caches_additions.`altitude` > :1 AND caches.cache_id = caches_additions.cache_id AND caches.user_id = :2 AND caches.type IN (:3) AND status = :4 AND `caches`.`date_created` > :5'; $db->multiVariableQuery($placedCountQuery, $this->conditions['minimumAltitude'], $user->getUserId(), $this->buildCacheTypesSqlString(), \lib\Objects\GeoCache\GeoCache::STATUS_READY, $this->dateIntroduced); $dbResult = $db->dbResultFetchOneRowOnly(); return $dbResult['cacheCount']; }
private function storeAlitudeInDb() { $query = 'INSERT INTO `caches_additions` (`cache_id`, `altitude`, `altitude_update_datetime`) VALUES (:2, :1, NOW()) ON DUPLICATE KEY UPDATE `altitude` = :1, altitude_update_datetime = NOW()'; $db = \lib\Database\DataBaseSingleton::Instance(); $db->multiVariableQuery($query, $this->altitude, $this->geoCache->getCacheId()); }
private function buildMedals() { $db = \lib\Database\DataBaseSingleton::Instance(); $query = 'SELECT `medal_type`, `prized_time` FROM `medals` WHERE `user_id`=:1'; $db->multiVariableQuery($query, $this->userId); $medalsDb = $db->dbResultFetchAll(); $this->medals = new \ArrayObject(); foreach ($medalsDb as $medalRow) { $this->medals[] = new \lib\Medals\Medal(array('prizedTime' => $medalRow['prized_time'], 'type' => (int) $medalRow['medal_type'])); } }
private function getPlacedCacheCount(User $user) { $query = 'SELECT count(caches.cache_id) as cacheCount FROM `caches`, `cache_location` ' . 'WHERE `caches`.`user_id` = :1 ' . $this->buildLocationCode4QueryString(5) . ' ' . 'AND `caches`.`status` = :2 AND `caches`.`date_created` > :3 AND cache_location.code3 = :4 ' . 'AND `caches`.`type` IN (' . $this->buildCacheTypesSqlString() . ') ' . 'AND cache_location.cache_id = caches.cache_id '; $db = DataBaseSingleton::Instance(); $code4 = isset($this->conditions['cacheLocation']['code4']) ? $this->conditions['cacheLocation']['code4'] : false; if ($code4) { $db->multiVariableQuery($query, $user->getUserId(), \cache::STATUS_READY, $this->dateIntroduced, $this->conditions['cacheLocation']['code3'], $code4); } else { $db->multiVariableQuery($query, $user->getUserId(), \cache::STATUS_READY, $this->dateIntroduced, $this->conditions['cacheLocation']['code3']); } $dbResult = $db->dbResultFetchOneRowOnly(); return $dbResult['cacheCount']; }
/** * get all today's active users */ public function checkAllUsersMedals() { $query = 'SELECT user_id, username, founds_count, notfounds_count, hidden_count, latitude, longitude, country, email FROM `user` WHERE (`last_login` BETWEEN DATE_SUB(NOW(), INTERVAL 24 HOUR) AND NOW()) '; /* @var $db \dataBase */ $db = \lib\Database\DataBaseSingleton::Instance(); $db->simpleQuery($query); d($db->rowCount()); $timeStart = microtime(); $usersToCheck = $db->dbResultFetchAll(); foreach ($usersToCheck as $userDbRow) { $user = new \lib\Objects\User\User(array('userDbRow' => $userDbRow)); $user->loadMedalsFromDb(); $this->checkMedalConditions($user); } $timeEnd = microtime() - $timeStart; d($timeEnd); }
public function checkConditionsForUser(\lib\Objects\User\User $user) { if (!in_array($this->config->getOcNodeId(), $this->conditions['ocNodeId'])) { /* this medal is not available in current node */ return; } $query = 'SELECT count(`id`) as `completedLogCount` FROM `PowerTrail_comments` WHERE `deleted` = 0 AND `userId` = :1 AND `PowerTrailId` = :2 '; /* @var $db \dataBase */ $db = \lib\Database\DataBaseSingleton::Instance(); $db->multiVariableQuery($query, $user->getUserId(), $this->conditions['geoPath']['geoPathId']); $cacheCountArr = $db->dbResultFetchOneRowOnly(); if ($cacheCountArr['completedLogCount'] == 1) { $this->prizedTime = date($this->config->getDbDateTimeFormat()); $this->level = 0; } else { $this->prizedTime = false; } $this->storeMedalStatus($user); }
function emailCacheOwner($ptId, $cacheId, $linkCode) { global $octeam_email, $usr, $absolute_server_URI, $site_name, $siteDateFormat, $siteDateTimeFormat; $owners = powerTrailBase::getPtOwners($ptId); $ptDbRow = powerTrailBase::getPtDbRow($ptId); $query = 'SELECT `caches` . * , `user`.`email`, `user`.`username` FROM `caches` , `user` WHERE `cache_id` =:1 AND `caches`.`user_id` = `user`.`user_id` LIMIT 1'; $db = \lib\Database\DataBaseSingleton::Instance(); $db->multiVariableQuery($query, $cacheId); $cacheData = $db->dbResultFetch(); //remove images $headers = 'MIME-Version: 1.0' . "\r\n"; $headers .= 'Content-type: text/html; charset=UTF-8 ' . "\r\n"; $headers .= "From: {$site_name} <" . $octeam_email . ">\r\n"; $headers .= "Reply-To: " . $octeam_email . "\r\n"; $mailbody = read_file(dirname(__FILE__) . '/candidateEmail.html'); $mailbody = mb_ereg_replace('{cacheOwnerName}', $cacheData['username'], $mailbody); $mailbody = mb_ereg_replace('{ptName}', $ptDbRow['name'], $mailbody); $mailbody = mb_ereg_replace('{ptId}', $ptId, $mailbody); $mailbody = mb_ereg_replace('{cacheName}', $cacheData['name'], $mailbody); $mailbody = mb_ereg_replace('{dateTime}', date($siteDateFormat), $mailbody); $mailbody = mb_ereg_replace('{userId}', $usr['userid'], $mailbody); $mailbody = mb_ereg_replace('{userName}', $usr['username'], $mailbody); $mailbody = mb_ereg_replace('{absolute_server_URI}', $absolute_server_URI, $mailbody); $mailbody = mb_ereg_replace('{linkCode}', $linkCode, $mailbody); $mailbody = mb_ereg_replace('{runwatch14}', tr('runwatch14'), $mailbody); $mailbody = mb_ereg_replace('{cacheWaypoint}', $cacheData['wp_oc'], $mailbody); $mailbody = mb_ereg_replace('{pt183}', tr('pt183'), $mailbody); $mailbody = mb_ereg_replace('{pt184}', tr('pt184'), $mailbody); $mailbody = mb_ereg_replace('{pt185}', tr('pt185'), $mailbody); $mailbody = mb_ereg_replace('{pt189}', tr('pt189'), $mailbody); $mailbody = mb_ereg_replace('{pt186}', tr('pt186'), $mailbody); $mailbody = mb_ereg_replace('{pt187}', tr('pt187'), $mailbody); $mailbody = mb_ereg_replace('{pt188}', tr('pt188'), $mailbody); $mailbody = mb_ereg_replace('{pt190}', tr('pt190'), $mailbody); mb_send_mail($cacheData['email'], tr('pt183'), $mailbody, $headers); // for debug only // mb_send_mail('*****@*****.**', tr('pt183'), $mailbody, $headers); }
public function checkConditionsForUser(User $user) { if (!in_array($this->config->getOcNodeId(), $this->conditions['ocNodeId'])) { /* this medal is not available in current node */ return; } /* @var $db \dataBase */ $db = DataBaseSingleton::Instance(); $queryFound = 'SELECT MAX(`altitude`) as maxAltitude FROM `caches`, `caches_additions`, cache_logs WHERE caches.`cache_id` = caches_additions.`cache_id` AND cache_logs.cache_id = caches.`cache_id` AND cache_logs.type = 1 AND cache_logs.user_id = :1 AND caches.type IN(:2)'; $cacheTypes = $this->buildCacheTypesSqlString(); $db->multiVariableQuery($queryFound, $user->getUserId(), $cacheTypes); $foundMaxAltitudeRaw = $db->dbResultFetchOneRowOnly(); $foundMaxAltitude = (int) $foundMaxAltitudeRaw['maxAltitude']; $queryPlaced = 'SELECT MAX(`altitude`) as maxAltitude FROM `caches`, `caches_additions` WHERE caches.`cache_id` = caches_additions.`cache_id` AND cache.user_id = :1 AND caches.type IN(:2) AND cache.status = :3'; $db->multiVariableQuery($queryPlaced, $user->getUserId(), $cacheTypes, \cache::STATUS_READY); $placedMaxAltitudeRaw = $db->dbResultFetchOneRowOnly(); $placedMaxAltitude = (int) $placedMaxAltitudeRaw['maxAltitude']; $this->findLevel($foundMaxAltitude, $placedMaxAltitude); $this->storeMedalStatus($user); }
/** * archive all events older than 2 months */ function ArchEvent() { /* @var $db dataBase */ $db = \lib\Database\DataBaseSingleton::Instance(); $sql = "UPDATE caches SET status = 3 WHERE status<>3 AND type = 6 AND date_hidden < now() - interval 2 month"; $db->simpleQuery($sql); }
private function getUserPTs() { $query = "SELECT * FROM `PowerTrail`, PowerTrail_owners WHERE PowerTrail_owners.userId = :1 AND PowerTrail_owners.PowerTrailId = PowerTrail.id"; $db = \lib\Database\DataBaseSingleton::Instance(); $db->multiVariableQuery($query, $this->user['userid']); $userPTs = $db->dbResultFetchAll(); $this->userPTs = $userPTs; }
public function loadMedalsFromDb() { $db = \lib\Database\DataBaseSingleton::Instance(); $query = 'SELECT `medal_type`, `prized_time`, `medal_level` FROM `medals` WHERE `user_id`=:1'; $db->multiVariableQuery($query, $this->userId); $medalsDb = $db->dbResultFetchAll(); $this->medals = new \ArrayObject(); $medalController = new MedalsController(); foreach ($medalsDb as $medalRow) { $this->medals[] = $medalController->getMedal(array('prizedTime' => $medalRow['prized_time'], 'medalId' => (int) $medalRow['medal_type'], 'level' => $medalRow['medal_level'])); // $this->medals[] = new \lib\Objects\Medals\Medal(array('prizedTime' => $medalRow['prized_time'], 'medalId' => (int) $medalRow['medal_type'], 'level' => $medalRow['medal_level'])); } }
private function loadCacheLocation() { $db = DataBaseSingleton::Instance(); $query = 'SELECT `code1`, `code2`, `code3`, `code4` FROM `cache_location` WHERE `cache_id` =:1 LIMIT 1'; $db->multiVariableQuery($query, $this->id); $dbResult = $db->dbResultFetch(); $this->cacheLocation = $dbResult; }
public function setAndStoreStatus($status) { $this->status = $status; $db = \lib\Database\DataBaseSingleton::Instance(); $query = 'UPDATE `PowerTrail` SET `status` = :1 WHERE `PowerTrail`.`id` = :2 '; $db->multiVariableQuery($query, $status, $this->id); }
public function storeInDb() { $db = DataBaseSingleton::Instance(); if ($_REQUEST['type'] == Log::TYPE_CONQUESTED && $this->powerTrail->isAlreadyConquestedByUser($this->user)) { /* atempt to add second 'conquested' log */ return false; } if ($this->id) { ddd('TODO'); } else { d($this); $query = 'INSERT INTO `PowerTrail_comments`(`userId`, `PowerTrailId`, `commentType`, `commentText`, `logDateTime`, `dbInsertDateTime`, `deleted`) VALUES (:1, :2, :3, :4, :5, NOW(),0)'; $db->multiVariableQuery($query, $this->user->getUserId(), $this->powerTrail->getId(), $this->type, $this->text, $this->dateTime->format('Y-m-d H:i:s')); if ($this->type == self::TYPE_CONQUESTED) { $this->powerTrail->increaseConquestedCount(); } } $this->changePowerTrailStatusAfterLog(); }
public static function getAllPt($filter) { $sortOder = 'ASC'; $sortBy = 'name'; $q = 'SELECT * FROM `PowerTrail` WHERE cacheCount >= ' . self::historicMinimumCacheCount() . ' ' . $filter . ' ORDER BY ' . $sortBy . ' ' . $sortOder . ' '; $db = \lib\Database\DataBaseSingleton::Instance(); $db->multiVariableQuery($q); return $db->dbResultFetchAll(); }
function addCacheToCacheCandidate($cacheId, $ptId) { $linkCode = randomPassword(50); $q = "INSERT INTO `PowerTrail_cacheCandidate`(`PowerTrailId`, `cacheId`, `link`, `date`) VALUES (:1,:2,:3,NOW())"; $db = \lib\Database\DataBaseSingleton::Instance(); $db->multiVariableQuery($q, $ptId, $cacheId, $linkCode); require_once 'sendEmailCacheCandidate.php'; emailCacheOwner($ptId, $cacheId, $linkCode); print 'cache added as cache candidate'; exit; }
//prepare the templates and include all neccessary global $site_name, $absolute_server_URI; if (!isset($rootpath)) { $rootpath = './'; } require_once './lib/common.inc.php'; $ocWP = $GLOBALS['oc_waypoint']; $no_tpl_build = false; //Preprocessing if ($error == false) { if ($usr == false) { //user logged in? $target = urlencode(tpl_get_current_page()); tpl_redirect('login.php?target=' . $target); } else { $db = \lib\Database\DataBaseSingleton::Instance(); $user = new \lib\Objects\User\User(array('userId' => $usr['userid'])); $user->loadExtendedSettings(); $default_country = getDefaultCountry($usr, $lang); if (isset($_REQUEST['newcache_info'])) { $newcache_info = $_REQUEST['newcache_info']; } else { $newcache_info = 1; } if ($newcache_info == 1) { // display info about register new cache $tplname = 'newcache_info'; } else { //set here the template to process $tplname = 'newcache'; }
private function addMedalToUserMedalsDb(User $user) { $query = 'INSERT INTO `medals`(`user_id`, `medal_type`, `prized_time`, `medal_level`) VALUES (:1, :2, :3, :4)'; $db = DataBaseSingleton::Instance(); $db->multiVariableQuery($query, $user->getUserId(), $this->medalId, $this->prizedTime, $this->level); }