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);
}
