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'];
}
private function getGeocacherDays(User $user)
{
$db = OcDb::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';
$s = $db->multiVariableQuery($query, $user->getUserId());
$dbResult = $db->dbResultFetchOneRowOnly($s);
return $dbResult['months'];
}
private function getPlacedCount(User $user)
{
$db = OcDb::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';
$s = $db->multiVariableQuery($placedCountQuery, $this->conditions['minimumAltitude'], $user->getUserId(), $this->buildCacheTypesSqlString(), \lib\Objects\GeoCache\GeoCache::STATUS_READY, $this->dateIntroduced);
$dbResult = $db->dbResultFetchOneRowOnly($s);
return $dbResult['cacheCount'];
}
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'];
}
public function checkConditionsForUser(User $user)
{
if (!in_array($this->config->getOcNodeId(), $this->conditions['ocNodeId'])) {
/* this medal is not available in current node */
return;
}
$db = OcDb::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();
$s = $db->multiVariableQuery($queryFound, $user->getUserId(), $cacheTypes);
$foundMaxAltitudeRaw = $db->dbResultFetchOneRowOnly($s);
$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';
$s = $db->multiVariableQuery($queryPlaced, $user->getUserId(), $cacheTypes, \cache::STATUS_READY);
$placedMaxAltitudeRaw = $db->dbResultFetchOneRowOnly($s);
$placedMaxAltitude = (int) $placedMaxAltitudeRaw['maxAltitude'];
$this->findLevel($foundMaxAltitude, $placedMaxAltitude);
$this->storeMedalStatus($user);
}
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 ';
$db = OcDb::instance();
$s = $db->multiVariableQuery($query, $user->getUserId(), $this->conditions['geoPath']['geoPathId']);
$cacheCountArr = $db->dbResultFetchOneRowOnly($s);
if ($cacheCountArr['completedLogCount'] == 1) {
$this->prizedTime = date($this->config->getDbDateTimeFormat());
$this->level = 0;
} else {
$this->prizedTime = false;
}
$this->storeMedalStatus($user);
}
public function storeInDb()
{
$db = OcDb::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 {
if ($this->type === self::TYPE_ADD_WARNING && $this->user->getIsAdmin() === false) {
return false;
/* regular user is not allowed to add entery of this type */
}
$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();
return true;
}
public static function sendRemoveLogNotification($emailTemplateFile, GeoCacheLog $log, User $loggedUser)
{
$formattedMessage = new EmailFormatter($emailTemplateFile);
$formattedMessage->setVariable("log_owner", $log->getUser()->getUserName());
$formattedMessage->setVariable("waypointId", $log->getGeoCache()->getWaypointId());
$formattedMessage->setVariable("serviceUrl", OcConfig::getAbsolute_server_URI());
$formattedMessage->setVariable("logRemover", $loggedUser->getUserName());
$formattedMessage->setVariable("logRemoverId", $loggedUser->getUserId());
$formattedMessage->setVariable("cache_name", $log->getGeoCache()->getCacheName());
$formattedMessage->setVariable("log_entry", $log->getText());
$formattedMessage->setVariable("removedLog_01", tr('removedLog_01'));
$formattedMessage->setVariable("removedLog_02", tr('removedLog_02'));
$formattedMessage->addFooterAndHeader($log->getUser()->getUserName());
$email = new Email();
$email->addToAddr($log->getUser()->getEmail());
$email->setReplyToAddr(OcConfig::getNoreplyEmailAddress());
$email->setFromAddr(OcConfig::getNoreplyEmailAddress());
$email->setSubject(tr('removed_log_title'));
$email->setBody($formattedMessage->getEmailContent(), true);
$email->send();
}
private function addMedalToUserMedalsDb(User $user)
{
$query = 'INSERT INTO `medals`(`user_id`, `medal_type`, `prized_time`, `medal_level`) VALUES (:1, :2, :3, :4)';
$db = OcDb::instance();
$db->multiVariableQuery($query, $user->getUserId(), $this->medalId, $this->prizedTime, $this->level);
}
public function isAlreadyConquestedByUser(\lib\Objects\User\User $user)
{
$db = OcDb::instance();
$mySqlRequest = 'SELECT count(*) AS `ptConquestCount` FROM `PowerTrail_comments` WHERE `commentType` =2 AND `deleted` =0 AND `userId` =:1 AND `PowerTrailId` = :2';
$s = $db->multiVariableQuery($mySqlRequest, $user->getUserId(), $this->getId());
$mySqlResult = $db->dbResultFetch($s);
if ($mySqlResult['ptConquestCount'] > 0) {
return true;
} else {
return false;
}
}
