953 lines
33 KiB
PHP
Executable File
953 lines
33 KiB
PHP
Executable File
<?php
|
|
|
|
namespace Franzz\Livetrail;
|
|
use Franzz\Objects\Db;
|
|
use Franzz\Objects\Main;
|
|
use Franzz\Objects\Translator;
|
|
use Franzz\Objects\ToolBox;
|
|
use \Settings;
|
|
|
|
/* Timezones
|
|
* ---------
|
|
* Site Time: Timestamp converted to the Timezone from which the user is viewing the Site (default PHP/SQL Timezone)
|
|
* Local Time: Timestamp converted to the Timezone from which the content (media/post/message) has been sent (Local Timezone stored in timezone field)
|
|
*
|
|
* - Feeds (table `feeds`):
|
|
* - last_update: timestamp in Site Time
|
|
* - Spot Messages (table `messages`):
|
|
* - unix_time: UNIX (int) in UTC
|
|
* - site_time: timestamp in Site Time
|
|
* - iso_time: raw ISO 8601 in UTC or Local Time (spot messages are unreliable, timezone is then calculated from GPS coordinates)
|
|
* - posted_on: timestamp in Site Time
|
|
* - timezone: Local Timezone
|
|
* - Medias (table `medias`):
|
|
* - posted_on: timestamp in Site Time
|
|
* - taken_on: timestamp in Site Time
|
|
* - timezone: Local Timezone
|
|
* - Posts (table `posts`):
|
|
* - site_time: timestamp in Site Time
|
|
* - timezone: Local Timezone
|
|
* - Users (table `users`):
|
|
* - timezone: Site Timezone (stored user's timezone for emails)
|
|
*/
|
|
|
|
class Livetrail extends Main {
|
|
//Database
|
|
public const POST_TABLE = 'posts';
|
|
private const REF_TYPES = ['post', 'media', 'message'];
|
|
|
|
private const FEED_CHUNK_SIZE = 15;
|
|
private const MAIL_CHUNK_SIZE = 5;
|
|
|
|
public const DEFAULT_LANG = 'en';
|
|
public const PROJECT_NAME = 'LiveTrail';
|
|
|
|
private const MAIN_PAGE = 'index';
|
|
private const VITE_APP = 'src/app.js';
|
|
|
|
private Project $oProject;
|
|
private Media $oMedia;
|
|
private User $oUser;
|
|
private Map $oMap;
|
|
|
|
public function __construct($sProcessPage, $sTimezone) {
|
|
parent::__construct($sProcessPage, true, $sTimezone);
|
|
|
|
$this->oUser = new User($this->oDb);
|
|
|
|
$this->oLang = new Translator('', self::DEFAULT_LANG);
|
|
|
|
$this->oProject = new Project($this->oDb);
|
|
$this->oMedia = new Media($this->oDb, $this->oProject);
|
|
|
|
$this->oMap = new Map($this->oDb);
|
|
}
|
|
|
|
protected function install() {
|
|
//Install DB
|
|
$this->oDb->install();
|
|
|
|
//Add first user
|
|
$iUserId = $this->oDb->insertRow(User::USER_TABLE, [
|
|
'name' => 'Admin',
|
|
'email' => 'admin@admin.com',
|
|
'language' => self::DEFAULT_LANG,
|
|
'timezone' => date_default_timezone_get(),
|
|
'subscribed'=> User::USER_SUBSCRIBED,
|
|
'clearance' => User::CLEARANCE_ADMIN
|
|
]);
|
|
$this->oUser->setUserId($iUserId);
|
|
}
|
|
|
|
protected function getSqlOptions() {
|
|
return
|
|
[
|
|
'tables' =>
|
|
[
|
|
Feed::MSG_TABLE => ['ref_msg_id', Db::getId(Feed::FEED_TABLE), 'type', 'latitude', 'longitude', 'iso_time', 'site_time', 'timezone', 'unix_time', 'content', 'battery_state', 'posted_on', 'weather_icon', 'weather_cond', 'weather_temp', 'display'],
|
|
Feed::FEED_TABLE => ['ref_feed_id', Db::getId(Feed::SPOT_TABLE), Db::getId(Project::PROJ_TABLE), 'name', 'description', 'status', 'last_update'],
|
|
Feed::SPOT_TABLE => ['ref_spot_id', 'name', 'model'],
|
|
Project::PROJ_TABLE => ['name', 'codename', 'active_from', 'active_to'],
|
|
self::POST_TABLE => [Db::getId(Project::PROJ_TABLE), Db::getId(User::USER_TABLE), 'name', 'content', 'site_time', 'timezone', 'ref_id', 'ref_type'],
|
|
Media::MEDIA_TABLE => [Db::getId(Project::PROJ_TABLE), 'filename', 'type', 'taken_on', 'posted_on', 'timezone', 'latitude', 'longitude', 'altitude', 'width', 'height', 'rotate', 'comment'],
|
|
User::USER_TABLE => ['name', 'email', 'password', 'token', 'token_exp', 'gravatar', 'language', 'timezone', 'subscribed', 'clearance'],
|
|
Map::MAP_TABLE => ['codename', 'pattern', 'token', 'tile_size', 'min_zoom', 'max_zoom', 'attribution'],
|
|
Map::MAPPING_TABLE => [Db::getId(Map::MAP_TABLE) , Db::getId(Project::PROJ_TABLE)]
|
|
],
|
|
'types' =>
|
|
[
|
|
'clearance' => 'TINYINT(1) DEFAULT '.User::CLEARANCE_USER,
|
|
'active_from' => 'TIMESTAMP DEFAULT 0',
|
|
'active_to' => 'TIMESTAMP DEFAULT 0',
|
|
'battery_state' => 'VARCHAR(10)',
|
|
'codename' => 'VARCHAR(100)',
|
|
'content' => 'LONGTEXT',
|
|
'comment' => 'LONGTEXT',
|
|
'description' => 'VARCHAR(100)',
|
|
'email' => 'VARCHAR(320) NOT NULL',
|
|
'filename' => 'VARCHAR(100) NOT NULL',
|
|
'iso_time' => 'VARCHAR(24)',
|
|
'language' => 'VARCHAR(2)',
|
|
'last_update' => 'TIMESTAMP DEFAULT 0',
|
|
'latitude' => 'DECIMAL(8,6)',
|
|
'longitude' => 'DECIMAL(9,6)',
|
|
'altitude' => 'SMALLINT',
|
|
'model' => 'VARCHAR(20)',
|
|
'name' => 'VARCHAR(100)',
|
|
'pattern' => 'VARCHAR(200) NOT NULL',
|
|
'password' => "VARCHAR(255) NOT NULL DEFAULT ''",
|
|
'posted_on' => 'TIMESTAMP DEFAULT 0',
|
|
'ref_feed_id' => 'VARCHAR(40)',
|
|
'ref_msg_id' => 'VARCHAR(15)',
|
|
'ref_spot_id' => 'VARCHAR(10)',
|
|
'ref_id' => 'INT UNSIGNED',
|
|
'ref_type' => 'VARCHAR(20)',
|
|
'rotate' => 'SMALLINT',
|
|
'site_time' => 'TIMESTAMP DEFAULT 0', //DEFAULT 0 removes auto-set to current time
|
|
'status' => 'VARCHAR(10)',
|
|
'subscribed' => 'BOOLEAN DEFAULT '.User::USER_UNSUBSCRIBED,
|
|
'taken_on' => 'TIMESTAMP DEFAULT 0',
|
|
'timezone' => 'CHAR(64) NOT NULL', //see mysql.time_zone_name
|
|
'token' => 'VARCHAR(4096)',
|
|
'token_exp' => 'TIMESTAMP DEFAULT 0',
|
|
'type' => 'VARCHAR(20)',
|
|
'unix_time' => 'INT',
|
|
'min_zoom' => 'TINYINT UNSIGNED',
|
|
'max_zoom' => 'TINYINT UNSIGNED',
|
|
'attribution' => 'VARCHAR(100)',
|
|
'gravatar' => 'LONGTEXT',
|
|
'weather_icon' => 'VARCHAR(30)',
|
|
'weather_cond' => 'VARCHAR(30)',
|
|
'weather_temp' => 'DECIMAL(3,1)',
|
|
'tile_size' => 'SMALLINT UNSIGNED DEFAULT 256',
|
|
'width' => 'INT',
|
|
'height' => 'INT',
|
|
'display' => 'BOOLEAN DEFAULT '.Feed::MSG_DISPLAYED
|
|
],
|
|
'constraints' =>
|
|
[
|
|
Feed::MSG_TABLE => ['UNIQUE KEY `uni_ref_msg_id` (`ref_msg_id`)', 'INDEX(`ref_msg_id`)'],
|
|
Feed::FEED_TABLE => ['UNIQUE KEY `uni_ref_feed_id` (`ref_feed_id`)', 'INDEX(`ref_feed_id`)'],
|
|
Feed::SPOT_TABLE => ['UNIQUE KEY `uni_ref_spot_id` (`ref_spot_id`)', 'INDEX(`ref_spot_id`)'],
|
|
Project::PROJ_TABLE => 'UNIQUE KEY `uni_proj_name` (`codename`)',
|
|
Media::MEDIA_TABLE => 'UNIQUE KEY `uni_file_name` (`filename`)',
|
|
User::USER_TABLE => 'UNIQUE KEY `uni_email` (`email`)',
|
|
Map::MAP_TABLE => 'UNIQUE KEY `uni_map_name` (`codename`)',
|
|
Map::MAPPING_TABLE => 'default_on_generic_map_only CHECK (`default_map` = 0 OR `id_project` IS NULL)'
|
|
],
|
|
'cascading_delete' =>
|
|
[
|
|
Feed::SPOT_TABLE => [Feed::FEED_TABLE],
|
|
Feed::FEED_TABLE => [Feed::MSG_TABLE],
|
|
Project::PROJ_TABLE => [Feed::FEED_TABLE, Media::MEDIA_TABLE, self::POST_TABLE, Map::MAPPING_TABLE],
|
|
Map::MAP_TABLE => [Map::MAPPING_TABLE]
|
|
]
|
|
];
|
|
}
|
|
|
|
public function getAppMainPage(string $sCsrfToken='') {
|
|
$asViteAssets = $this->getViteAssets();
|
|
|
|
return parent::getMainPage(
|
|
[
|
|
'projects' => $this->oProject->getProjects(),
|
|
'user' => $this->oUser->getUserInfo(),
|
|
'consts' => [
|
|
'modes' => Project::MODES,
|
|
'clearances' => User::CLEARANCES,
|
|
'default_timezone' => Settings::TIMEZONE,
|
|
'default_maps' => $this->oMap->getProjectMaps(-1),
|
|
'chunk_size' => self::FEED_CHUNK_SIZE,
|
|
'hash_sep' => '-',
|
|
'title' => self::PROJECT_NAME,
|
|
'default_page' => 'project',
|
|
'csrf_token' => $sCsrfToken
|
|
]
|
|
],
|
|
self::MAIN_PAGE,
|
|
[
|
|
'tags' => [
|
|
'language' => $this->oLang->getLanguage(),
|
|
'title' => self::PROJECT_NAME,
|
|
'app_entry' => $asViteAssets['app']
|
|
],
|
|
'instances' => [
|
|
'css' => $asViteAssets['css'],
|
|
'module' => $asViteAssets['module']
|
|
]
|
|
]
|
|
);
|
|
}
|
|
|
|
private function getViteAssets() {
|
|
$asManifest = json_decode(file_get_contents(__DIR__.'/../public/.vite/manifest.json'), true);
|
|
$asAppImport = $asManifest[self::VITE_APP];
|
|
|
|
//Recursive search for chunk imports
|
|
$asImports = [];
|
|
$asSeenImports = [self::VITE_APP => true];
|
|
$this->appendViteImportedChunks($asManifest, $asAppImport, $asSeenImports, $asImports);
|
|
|
|
//CSS
|
|
$asCssFiles = [];
|
|
foreach(array_merge([$asAppImport], $asImports) as $asChunk) {
|
|
foreach($asChunk['css'] ?? [] as $sCssFile) $asCssFiles[] = $sCssFile;
|
|
}
|
|
|
|
//Modules
|
|
$asModuleFiles = [];
|
|
foreach($asImports as $asImport) {
|
|
if(str_ends_with($asImport['file'] ?? '', '.js')) $asModuleFiles[] = $asImport['file'];
|
|
}
|
|
|
|
return [
|
|
'app' => $asAppImport['file'],
|
|
'css' => $this->getViteAssetInstances($asCssFiles),
|
|
'module' => $this->getViteAssetInstances($asModuleFiles)
|
|
];
|
|
}
|
|
|
|
private function appendViteImportedChunks($asManifest, $asChunk, &$asSeenImports, &$asImports) {
|
|
foreach($asChunk['imports'] ?? [] as $sImport) {
|
|
if(isset($asSeenImports[$sImport]) || !isset($asManifest[$sImport])) continue;
|
|
|
|
$asSeenImports[$sImport] = true;
|
|
$this->appendViteImportedChunks($asManifest, $asManifest[$sImport], $asSeenImports, $asImports);
|
|
$asImports[] = $asManifest[$sImport];
|
|
}
|
|
}
|
|
|
|
private function getViteAssetInstances($asFilePaths) {
|
|
return array_map(
|
|
function($sFilePath) { return ['filename' => $sFilePath]; },
|
|
$asFilePaths
|
|
);
|
|
}
|
|
|
|
public function checkUserClearance($iClearance) {
|
|
return $this->oUser->checkUserClearance($iClearance);
|
|
}
|
|
|
|
/* Managing projects */
|
|
|
|
public function setProjectId($iProjectId=0) {
|
|
$this->oProject->setProjectId($iProjectId);
|
|
}
|
|
|
|
public function updateProject() {
|
|
$bNewMsg = false;
|
|
$bSuccess = true;
|
|
$sLangId = '';
|
|
|
|
//Update all feeds belonging to the project
|
|
$asFeeds = $this->oProject->getFeedIds();
|
|
foreach($asFeeds as $iFeedId) {
|
|
$oFeed = new Feed($this->oDb, $iFeedId);
|
|
$bNewMsg = $bNewMsg || $oFeed->checkUpdateFeed($this->oProject->getMode());
|
|
}
|
|
|
|
//Send Update Email
|
|
if($bNewMsg) {
|
|
$bSuccess = $this->sendEmail();
|
|
$sLangId = $bSuccess?'email.sent':'email.failure';
|
|
}
|
|
else $sLangId = 'spot.no_new_msg';
|
|
|
|
return self::getJsonResult($bSuccess, $sLangId);
|
|
}
|
|
|
|
private function sendEmail() {
|
|
$oEmail = new Email($this->asContext['serv_name'], 'email.update');
|
|
$oEmail->setDestInfo($this->oUser->getSubscribedUsersInfo());
|
|
|
|
//Add Position
|
|
$asSpotMessages = $this->getSpotMessages([$this->oProject->getLastMessageId($this->getFeedConstraints(Feed::MSG_TABLE))]);
|
|
$asLastMessage = array_shift($asSpotMessages);
|
|
$oEmail->oTemplate->setTags($asLastMessage);
|
|
$oEmail->oTemplate->setTag('date_time', 'time:'.$asLastMessage['unix_time'], 'd/m/Y, H:i');
|
|
|
|
//Add latest news feed
|
|
$asNews = $this->getNextFeed(0, true);
|
|
$iPostCount = 0;
|
|
foreach($asNews as $asPost) {
|
|
if($asPost['type'] != 'message') {
|
|
$oEmail->oTemplate->newInstance('news');
|
|
$oEmail->oTemplate->setInstanceTags('news', [
|
|
'local_server' => $this->asContext['serv_name'],
|
|
'project' => $this->oProject->getProjectCodeName(),
|
|
'type' => $asPost['type'],
|
|
'id' => $asPost['id_'.$asPost['type']]]
|
|
);
|
|
$oEmail->oTemplate->addInstance($asPost['type'], $asPost);
|
|
$oEmail->oTemplate->setInstanceTag($asPost['type'], 'local_server', $this->asContext['serv_name']);
|
|
$iPostCount++;
|
|
}
|
|
if($iPostCount == self::MAIL_CHUNK_SIZE) break;
|
|
}
|
|
|
|
return $oEmail->send();
|
|
}
|
|
|
|
public function getMarkers($asMessageIds=[], $asMediaIds=[], $bInternal=false) {
|
|
//Get messages
|
|
$asMessages = $this->getSpotMessages($asMessageIds);
|
|
foreach($asMessages as &$asMessage) {
|
|
$asMessage['id'] = $asMessage[Db::getId(Feed::MSG_TABLE)];
|
|
$asMessage['type'] = 'message';
|
|
$asMessage['subtype'] = 'message';
|
|
}
|
|
|
|
//Get Geo-positioned Medias
|
|
//FIXME Make more efficient than requesting images twice from DB
|
|
$asMedias = $this->getMedias('taken_on', $asMediaIds);
|
|
$asGeoMedias = $this->getMedias('posted_on', $asMediaIds, true);
|
|
foreach($asGeoMedias as &$asGeoMedia) {
|
|
$iId = $asGeoMedia[Db::getId(Media::MEDIA_TABLE)];
|
|
unset($asGeoMedia[Db::getId(Media::MEDIA_TABLE)]);
|
|
|
|
$asGeoMedia['id'] = $iId;
|
|
$asGeoMedia['type'] = 'media';
|
|
$asGeoMedia['medias'] = array_values(array_filter($asMedias, function($asMedia) use ($iId) {
|
|
return $asMedia['id_media'] == $iId;
|
|
}));
|
|
}
|
|
|
|
//Assign medias to closest message
|
|
if(!empty($asMessages)) {
|
|
usort($asMessages, function($a, $b) {return (int) $a['unix_time'] <=> (int) $b['unix_time'];});
|
|
usort($asMedias, function($a, $b) {return (int) $a['unix_time'] <=> (int) $b['unix_time'];});
|
|
|
|
$iIndex = 0;
|
|
$iMaxIndex = count($asMessages) - 1;
|
|
foreach($asMedias as $asMedia) {
|
|
while($iIndex <= $iMaxIndex && $asMedia['unix_time'] > $asMessages[$iIndex]['unix_time']) $iIndex++;
|
|
|
|
//All medias before first message or after last message are assigned to first/last message respectively
|
|
if($iIndex == 0) $iMsgIndex = $iIndex;
|
|
elseif($iIndex > $iMaxIndex) $iMsgIndex = $iMaxIndex;
|
|
else {
|
|
$iHalfWayPoint = ($asMessages[$iIndex - 1]['unix_time'] + $asMessages[$iIndex]['unix_time'])/2;
|
|
$iMsgIndex = ($asMedia['unix_time'] >= $iHalfWayPoint)?$iIndex:($iIndex - 1);
|
|
}
|
|
|
|
$asMessages[$iMsgIndex]['medias'][] = $asMedia;
|
|
}
|
|
}
|
|
|
|
//Combine markers
|
|
$asMarkers = [...$asMessages, ...$asGeoMedias];
|
|
usort($asMarkers, function($a, $b) {return (int) $a['unix_time'] <=> (int) $b['unix_time'];});
|
|
|
|
$asResult = [
|
|
'markers' => $asMarkers,
|
|
'maps' => $this->oMap->getProjectMaps($this->oProject->getProjectId())
|
|
];
|
|
|
|
return $bInternal?$asResult:self::getJsonResult(true, '', $asResult);
|
|
}
|
|
|
|
public function getLastUpdate() {
|
|
$asLastUpdate = [];
|
|
$this->addTimeStamp($asLastUpdate, $this->oProject->getLastUpdate());
|
|
return self::getJsonResult(true, '', $asLastUpdate);
|
|
}
|
|
|
|
public function login($sEmail, $sPassword, $sNickName) {
|
|
$asResult = $this->oUser->login($sEmail, $sPassword, $this->oLang->getLanguage(), date_default_timezone_get(), $sNickName);
|
|
|
|
if($asResult['result'] && $asResult['data']['subscribe']) return $this->subscribe();
|
|
else return self::getJsonResult($asResult['result'], $asResult['desc_lang_id'], $this->oUser->getUserInfo(), $asResult['desc_lang_params']);
|
|
}
|
|
|
|
public function logout() {
|
|
$asResult = $this->oUser->logout();
|
|
return self::getJsonResult($asResult['result'], $asResult['desc_lang_id'], User::DEFAULT_USER, $asResult['desc_lang_params']);
|
|
}
|
|
|
|
public function subscribe() {
|
|
$asResult = $this->oUser->setSubscription(true);
|
|
$asUserInfo = $this->oUser->getUserInfo();
|
|
if($asResult['result'] && !Settings::DEBUG) {
|
|
$oConfEmail = new Email($this->asContext['serv_name'], 'email.confirmation');
|
|
$oConfEmail->setDestInfo($asUserInfo);
|
|
$oConfEmail->send();
|
|
}
|
|
return self::getJsonResult($asResult['result'], $asResult['desc_lang_id'], $asUserInfo, $asResult['desc_lang_params']);
|
|
}
|
|
|
|
public function unsubscribe() {
|
|
$asResult = $this->oUser->setSubscription(false);
|
|
return self::getJsonResult($asResult['result'], $asResult['desc_lang_id'], $this->oUser->getUserInfo(), $asResult['desc_lang_params']);
|
|
}
|
|
|
|
private function getSpotMessages($asMsgIds=[]) {
|
|
$asConstraints = $this->getFeedConstraints(Feed::MSG_TABLE);
|
|
if(!empty($asMsgIds)) {
|
|
$asConstraints['constraint'][Db::getId(Feed::MSG_TABLE)] = $asMsgIds;
|
|
$asConstraints['constOpe'][Db::getId(Feed::MSG_TABLE)] = 'IN';
|
|
}
|
|
|
|
$asCombinedMessages = [];
|
|
|
|
//Get messages from all feeds belonging to the project
|
|
$asFeeds = $this->oProject->getFeedIds();
|
|
foreach($asFeeds as $iFeedId) {
|
|
$oFeed = new Feed($this->oDb, $iFeedId);
|
|
$asMessages = $oFeed->getMessages($asConstraints);
|
|
foreach($asMessages as $asMessage) {
|
|
$asMessage['latitude'] = floatval($asMessage['latitude']);
|
|
$asMessage['longitude'] = floatval($asMessage['longitude']);
|
|
$asMessage['lat_dms'] = self::decToDms($asMessage['latitude'], 'lat');
|
|
$asMessage['lon_dms'] = self::decToDms($asMessage['longitude'], 'lon');
|
|
$asMessage['displayed_id'] = $asMessage[Db::getId(Feed::MSG_TABLE)];
|
|
$asMessage['static_img_url'] = $this->oMap->getMapUrl('static', ['x'=>$asMessage['longitude'], 'y'=>$asMessage['latitude']]);
|
|
$asMessage['marker_img_url'] = $this->oMap->getMapUrl('static_marker', ['x'=>$asMessage['longitude'], 'y'=>$asMessage['latitude']]);
|
|
|
|
$this->addTimeStamp($asMessage, $asMessage['unix_time'], $asMessage['timezone']);
|
|
$asCombinedMessages[] = $asMessage;
|
|
}
|
|
}
|
|
|
|
return $asCombinedMessages;
|
|
}
|
|
|
|
/**
|
|
* Get valid medias based on $sTimeRefField:
|
|
* - taken_on: Date/time on which the media was taken
|
|
* - posted_on: Date/time on which the media was uploaded
|
|
* @param String $sTimeRefField Field to calculate relative times: 'taken_on' or 'posted_on'
|
|
* @return Array Medias info
|
|
*/
|
|
private function getMedias($sTimeRefField, $asMediaIds=[], $bOnlyGeoMedia=false) {
|
|
//Constraints
|
|
$asConstraints = $this->getFeedConstraints(Media::MEDIA_TABLE, $sTimeRefField);
|
|
if(!empty($asMediaIds)) {
|
|
$asConstraints['constOpe'][Db::getId(Media::MEDIA_TABLE)] = 'IN';
|
|
$asConstraints['constraint'][Db::getId(Media::MEDIA_TABLE)] = $asMediaIds;
|
|
}
|
|
if($bOnlyGeoMedia) {
|
|
$asConstraints['constOpe']['latitude'] = ' IS NOT ';
|
|
$asConstraints['constraint']['latitude'] = 'NULL';
|
|
$asConstraints['constOpe']['longitude'] = ' IS NOT ';
|
|
$asConstraints['constraint']['longitude'] = 'NULL';
|
|
}
|
|
|
|
$asMedias = $this->oMedia->getMediasInfo($asConstraints);
|
|
foreach($asMedias as &$asMedia) {
|
|
$asMedia['displayed_id'] = $asMedia[Db::getId(Media::MEDIA_TABLE)];
|
|
|
|
$this->addTimeStamp($asMedia, strtotime($asMedia[$sTimeRefField]), $asMedia['timezone']);
|
|
$this->addTimeStamp($asMedia, strtotime($asMedia['taken_on']), $asMedia['timezone'], 'taken_on');
|
|
$this->addTimeStamp($asMedia, strtotime($asMedia['posted_on']), $asMedia['timezone'], 'posted_on');
|
|
|
|
if($asMedia['latitude'] != '' && $asMedia['longitude'] != '') {
|
|
$asMedia['lat_dms'] = self::decToDms($asMedia['latitude'], 'lat');
|
|
$asMedia['lon_dms'] = self::decToDms($asMedia['longitude'], 'lon');
|
|
}
|
|
|
|
unset($asMedia['taken_on']);
|
|
unset($asMedia['posted_on']);
|
|
}
|
|
|
|
return $asMedias;
|
|
}
|
|
|
|
private function getPosts($asPostIds=[], $bResolveRef=true) {
|
|
$asInfo = [
|
|
'select' => [Db::getFullColumnName(self::POST_TABLE, '*'), 'gravatar'],
|
|
'from' => self::POST_TABLE,
|
|
'join' => [User::USER_TABLE => Db::getId(User::USER_TABLE)]
|
|
];
|
|
$asInfo = array_merge($asInfo, $this->getFeedConstraints(self::POST_TABLE));
|
|
|
|
if(!empty($asPostIds)) {
|
|
$asInfo['constraint'][Db::getId(self::POST_TABLE)] = $asPostIds;
|
|
$asInfo['constOpe'][Db::getId(self::POST_TABLE)] = 'IN';
|
|
}
|
|
$asPosts = $this->oDb->selectRows($asInfo);
|
|
|
|
foreach($asPosts as &$asPost) {
|
|
$iUnixTimeStamp = strtotime($asPost['site_time']); //assumes site timezone
|
|
$asPost['formatted_name'] = Toolbox::mb_ucwords($asPost['name']);
|
|
unset($asPost[Db::getId(User::USER_TABLE)]);
|
|
|
|
$this->addTimeStamp($asPost, $iUnixTimeStamp, $asPost['timezone']);
|
|
|
|
if($bResolveRef && !empty($asPost['ref_type']) && !empty($asPost['ref_id'])) {
|
|
switch($asPost['ref_type']) {
|
|
case 'post': $asRefs = $this->getPosts([$asPost['ref_id']], false); break;
|
|
case 'media': $asRefs = $this->getMedias('posted_on', [$asPost['ref_id']]); break;
|
|
case 'message': $asRefs = $this->getSpotMessages([$asPost['ref_id']]); break;
|
|
default: $asRefs = [];
|
|
}
|
|
$asPost['ref_item'] = reset($asRefs);
|
|
$asPost['ref_item']['type'] = $asPost['ref_type'];
|
|
$asPost['ref_item']['id'] = $asPost['ref_id'];
|
|
}
|
|
unset($asPost['ref_id']);
|
|
unset($asPost['ref_type']);
|
|
}
|
|
|
|
return $asPosts;
|
|
}
|
|
|
|
private function addTimeStamp(&$asData, $iTime, $sTimeZone='', $sPrefix='') {
|
|
if($sPrefix != '') $sPrefix = $sPrefix.'_';
|
|
|
|
$asData[$sPrefix.'unix_time'] = (int) $iTime;
|
|
$asData[$sPrefix.'relative_time'] = Toolbox::getDateTimeDesc($iTime, $this->oLang->getLanguage());
|
|
$asData[$sPrefix.'formatted_time'] = $this->getTimeFormat($iTime);
|
|
|
|
if($sTimeZone != '') {
|
|
$asData[$sPrefix.'formatted_time_local'] = $this->getTimeFormat($iTime, $sTimeZone);
|
|
$asData[$sPrefix.'day_offset'] = self::getTimeZoneDayOffset($iTime, $sTimeZone);
|
|
}
|
|
}
|
|
|
|
private function getFeedConstraints($sType, $sTimeField='site_time', $sReturnFormat='array') {
|
|
$asConsArray = [];
|
|
$sConsSql = '';
|
|
$asActPeriod = $this->oProject->getActivePeriod();
|
|
|
|
//Filter on Project ID
|
|
$sConsSql = 'WHERE '.Db::getId(Project::PROJ_TABLE).' = '.$this->oProject->getProjectId();
|
|
$asConsArray = [
|
|
'constraint'=> [Db::getId(Project::PROJ_TABLE) => $this->oProject->getProjectId()],
|
|
'constOpe' => [Db::getId(Project::PROJ_TABLE) => '=']
|
|
];
|
|
|
|
//Time Filter
|
|
switch($sType) {
|
|
case Feed::MSG_TABLE:
|
|
$asConsArray['constraint'][$sTimeField] = $asActPeriod;
|
|
$asConsArray['constOpe'][$sTimeField] = 'BETWEEN';
|
|
$asConsArray['constraint']['display'] = Feed::MSG_DISPLAYED;
|
|
$asConsArray['constOpe']['display'] = '=';
|
|
$sConsSql .= ' AND '.$sTimeField." BETWEEN '".$asActPeriod['from']."' AND '".$asActPeriod['to']."' AND display = ".Feed::MSG_DISPLAYED;
|
|
break;
|
|
case Media::MEDIA_TABLE:
|
|
$asConsArray['constraint'][$sTimeField] = $asActPeriod['to'];
|
|
$asConsArray['constOpe'][$sTimeField] = '<=';
|
|
$sConsSql .= ' AND '.$sTimeField." <= '".$asActPeriod['to']."'";
|
|
break;
|
|
case self::POST_TABLE:
|
|
$asConsArray['constraint'][$sTimeField] = $asActPeriod['to'];
|
|
$asConsArray['constOpe'][$sTimeField] = '<=';
|
|
$sConsSql .= ' AND '.$sTimeField." <= '".$asActPeriod['to']."'";
|
|
break;
|
|
}
|
|
|
|
return ($sReturnFormat=='array')?$asConsArray:$sConsSql;
|
|
}
|
|
|
|
public function getNewFeed($iRefIdFirst) {
|
|
$asResult = [];
|
|
$sLangId = '';
|
|
|
|
if($this->oProject->isEditable()) {
|
|
$asMessageIds = $asMediaIds = [];
|
|
|
|
//New Feed Items
|
|
$asResult = $this->getFeed($iRefIdFirst, '>', 'DESC');
|
|
foreach($asResult['feed'] as $asItem) {
|
|
switch($asItem['type']) {
|
|
case 'message':
|
|
$asMessageIds[] = $asItem['id'];
|
|
break;
|
|
case 'media':
|
|
$asMediaIds[] = $asItem['id'];
|
|
break;
|
|
}
|
|
}
|
|
|
|
//New Markers
|
|
$asMarkers = $this->getMarkers(
|
|
empty($asMessageIds)?[0]:$asMessageIds,
|
|
empty($asMediaIds)?[0]:$asMediaIds,
|
|
true
|
|
);
|
|
|
|
$asResult = array_merge($asResult, $asMarkers);
|
|
}
|
|
else $sLangId = 'project.modes.histo';
|
|
|
|
return self::getJsonResult(true, $sLangId, $asResult);
|
|
}
|
|
|
|
public function getNextFeed($iRefIdLast=0, $bInternal=false) {
|
|
if($this->oProject->getMode() == Project::MODE_HISTO) {
|
|
$sDirection = '>';
|
|
$sSort = 'ASC';
|
|
}
|
|
else {
|
|
$sDirection = '<';
|
|
$sSort = 'DESC';
|
|
}
|
|
$asResult = $this->getFeed($iRefIdLast, $sDirection, $sSort);
|
|
return $bInternal?$asResult['feed']:self::getJsonResult(true, '', $asResult);
|
|
}
|
|
|
|
private function getFeed($iRefId, $sDirection, $sSort) {
|
|
$sRefId = is_scalar($iRefId) && preg_match('/^\d+(?:\.\d+)?$/D', (string) $iRefId) ? (string) $iRefId : '0';
|
|
$sDirection = ($sDirection === '>')?'>':'<';
|
|
$sSort = ($sSort === 'ASC')?'ASC':'DESC';
|
|
|
|
$sProjectIdField = Db::getId(Project::PROJ_TABLE);
|
|
$sMsgIdField = Db::getId(Feed::MSG_TABLE);
|
|
$sMediaIdField = Db::getId(Media::MEDIA_TABLE);
|
|
$sPostIdField = Db::getId(self::POST_TABLE);
|
|
$sFeedIdField = Db::getId(Feed::FEED_TABLE);
|
|
$sQuery = implode(' ', [
|
|
'SELECT type, id, ref',
|
|
'FROM (',
|
|
"SELECT {$sProjectIdField}, {$sMsgIdField} AS id, 'message' AS type, CONCAT(UNIX_TIMESTAMP(site_time), '.0', {$sMsgIdField}) AS ref",
|
|
'FROM '.Feed::MSG_TABLE,
|
|
'INNER JOIN '.Feed::FEED_TABLE." USING({$sFeedIdField})",
|
|
$this->getFeedConstraints(Feed::MSG_TABLE, 'site_time', 'sql'),
|
|
'UNION',
|
|
"SELECT {$sProjectIdField}, {$sMediaIdField} AS id, 'media' AS type, CONCAT(UNIX_TIMESTAMP(posted_on), '.1', {$sMediaIdField}) AS ref",
|
|
'FROM '.Media::MEDIA_TABLE,
|
|
$this->getFeedConstraints(Media::MEDIA_TABLE, 'posted_on', 'sql'),
|
|
'UNION',
|
|
"SELECT {$sProjectIdField}, {$sPostIdField} AS id, 'post' AS type, CONCAT(UNIX_TIMESTAMP(site_time), '.2', {$sPostIdField}) AS ref",
|
|
'FROM '.self::POST_TABLE,
|
|
$this->getFeedConstraints(self::POST_TABLE, 'site_time', 'sql'),
|
|
') AS items',
|
|
($sRefId !== '0')?('WHERE ref '.$sDirection.' '.$sRefId):'',
|
|
'ORDER BY ref '.$sSort,
|
|
'LIMIT '.self::FEED_CHUNK_SIZE
|
|
]);
|
|
|
|
//Get new chunk
|
|
$asItems = $this->oDb->getArrayQuery($sQuery, true);
|
|
|
|
//Update Reference Point with latest/earliest value
|
|
$iRefIdFirst = $iRefIdLast = 0;
|
|
if(!empty($asItems)) {
|
|
$iRefIdLast = end($asItems)['ref'];
|
|
$iRefIdFirst = reset($asItems)['ref'];
|
|
}
|
|
|
|
//Sort Table IDs by type & Get attributes
|
|
$asFeedIds = ['message'=>[], 'media'=>[], 'post'=>[]];
|
|
foreach($asItems as $asItem) {
|
|
$asFeedIds[$asItem['type']][$asItem['id']] = $asItem;
|
|
}
|
|
$asFeedAttrs = [
|
|
'message' => empty($asFeedIds['message'])?[]:$this->getSpotMessages(array_keys($asFeedIds['message'])),
|
|
'media' => empty($asFeedIds['media'])?[]:$this->getMedias('posted_on', array_keys($asFeedIds['media'])),
|
|
'post' => empty($asFeedIds['post'])?[]:$this->getPosts(array_keys($asFeedIds['post']))
|
|
];
|
|
|
|
//Replace Array Key with Item ID
|
|
$asFeeds = [];
|
|
foreach($asFeedAttrs as $sType=>$asFeedAttr) {
|
|
foreach($asFeedAttr as $asFeed) {
|
|
$asFeeds[$sType][$asFeed['id_'.$sType]] = $asFeed;
|
|
}
|
|
}
|
|
|
|
//Assign
|
|
foreach($asItems as &$asItem) {
|
|
$asItem = array_merge($asFeeds[$asItem['type']][$asItem['id']], $asItem);
|
|
}
|
|
|
|
return ['ref_id_last'=>$iRefIdLast, 'ref_id_first'=>$iRefIdFirst, 'sort'=>$sSort, 'feed'=>$asItems];
|
|
}
|
|
|
|
public function addPost($sName, $sPost, $sRefType='', $iRefId=0) {
|
|
$iPostId = 0;
|
|
$sLangId = '';
|
|
|
|
if($this->oProject->isEditable()) {
|
|
$bHasRef = ($iRefId > 0) && in_array($sRefType, self::REF_TYPES, true);
|
|
$asData = [
|
|
Db::getId(Project::PROJ_TABLE) => $this->oProject->getProjectId(),
|
|
'name' => mb_strtolower(trim($sName)),
|
|
'content' => trim($sPost),
|
|
'site_time' => date(Db::TIMESTAMP_FORMAT), //Now in Site Time
|
|
'timezone' => date_default_timezone_get(), //Site Time Zone
|
|
'ref_id' => $bHasRef?$iRefId:'NULL',
|
|
'ref_type' => $bHasRef?$sRefType:'NULL'
|
|
];
|
|
if($this->oUser->getUserId() > 0) $asData[Db::getId(User::USER_TABLE)] = $this->oUser->getUserId();
|
|
|
|
$iPostId = $this->oDb->insertRow(self::POST_TABLE, $asData);
|
|
if($iPostId == 0) $sLangId = 'error.commit_db';
|
|
|
|
$this->oUser->updateNickname($sName);
|
|
}
|
|
else $sLangId = 'project.modes.histo';
|
|
|
|
return self::getJsonResult(($iPostId > 0), $sLangId);
|
|
}
|
|
|
|
public function upload() {
|
|
$oUploader = new Uploader($this->oMedia);
|
|
|
|
return $oUploader->sBody;
|
|
}
|
|
|
|
public function addComment($iMediaId, $sComment) {
|
|
$oMedia = new Media($this->oDb, $this->oProject, $iMediaId);
|
|
$asResult = $oMedia->setComment($sComment);
|
|
return self::getJsonResult($asResult['result'], $asResult['desc_lang_id'], $asResult['data'], $asResult['desc_lang_params']);
|
|
}
|
|
|
|
public function addPosition($sLat, $sLng, $iTimestamp) {
|
|
$oFeed = new Feed($this->oDb, $this->oProject->getFeedIds()[0]);
|
|
$bSuccess = ($oFeed->addManualPosition($sLat, $sLng, $iTimestamp) > 0);
|
|
|
|
if($bSuccess) {
|
|
$bSuccess = $this->sendEmail();
|
|
$sLangId = $bSuccess?'email.sent':'email.failure';
|
|
}
|
|
else $sLangId = 'error.commit_db';
|
|
|
|
return self::getJsonResult($bSuccess, $sLangId);
|
|
}
|
|
|
|
public function getAdminSettings() {
|
|
$oFeed = new Feed($this->oDb);
|
|
$asData = [
|
|
'project' => $this->oProject->getProjects(),
|
|
'feed' => $oFeed->getFeeds(),
|
|
'spot' => $oFeed->getSpots(),
|
|
'user' => $this->oUser->getSubscribedUsersInfo()
|
|
];
|
|
|
|
foreach($asData['project'] as &$asProject) {
|
|
$asProject['active_from'] = substr($asProject['active_from'], 0, 10);
|
|
$asProject['active_to'] = substr($asProject['active_to'], 0, 10);
|
|
}
|
|
|
|
return self::getJsonResult(true, '', $asData);
|
|
}
|
|
|
|
public function setAdminSettings($sType, $iId, $sField, $sValue) {
|
|
$bSuccess = false;
|
|
$sLangId = '';
|
|
$asLangParams = [];
|
|
$asResult = [];
|
|
|
|
if($this->oDb->isId($sField) && $sValue <= 0) return self::getJsonResult(false, 'error.impossible_value', [], [$sValue, $sField]);
|
|
|
|
switch($sType) {
|
|
case 'project':
|
|
$oProject = new Project($this->oDb, $iId);
|
|
|
|
switch($sField) {
|
|
case 'name':
|
|
$bSuccess = $oProject->setProjectName($sValue);
|
|
break;
|
|
case 'codename':
|
|
$bSuccess = $oProject->setProjectCodeName($sValue);
|
|
break;
|
|
case 'active_from':
|
|
$bSuccess = $oProject->setActivePeriod($sValue.' 00:00:00', 'from');
|
|
break;
|
|
case 'active_to':
|
|
$bSuccess = $oProject->setActivePeriod($sValue.' 23:59:59', 'to');
|
|
break;
|
|
default:
|
|
$sLangId = 'error.unknown_field';
|
|
$asLangParams = [$sField];
|
|
}
|
|
|
|
//Identify missing GPX file
|
|
$sProjectCodeName = ($sField == 'codename')?$sValue:$oProject->getProjectCodeName();
|
|
if(!Converter::hasGpxFile($sProjectCodeName)) {
|
|
$bSuccess = true;
|
|
$sLangId = 'error.file_missing';
|
|
$asLangParams = ['GPX', $sProjectCodeName.Gpx::EXT];
|
|
}
|
|
|
|
$asResult = $oProject->getProject();
|
|
$asResult['active_from'] = substr($asResult['active_from'], 0, 10);
|
|
$asResult['active_to'] = substr($asResult['active_to'], 0, 10);
|
|
break;
|
|
case 'feed':
|
|
$oFeed = new Feed($this->oDb, $iId);
|
|
switch($sField) {
|
|
case 'ref_feed_id':
|
|
$bSuccess = $oFeed->setRefFeedId($sValue);
|
|
break;
|
|
case 'id_spot':
|
|
$bSuccess = $oFeed->setSpotId($sValue);
|
|
break;
|
|
case 'id_project':
|
|
$bSuccess = $oFeed->setProjectId($sValue);
|
|
break;
|
|
default:
|
|
$sLangId = 'error.unknown_field';
|
|
$asLangParams = [$sField];
|
|
}
|
|
$asResult = $oFeed->getFeed();
|
|
break;
|
|
case 'user':
|
|
switch($sField) {
|
|
case 'clearance':
|
|
$asReturnCode = $this->oUser->setUserClearance($iId, $sValue);
|
|
$bSuccess = $asReturnCode['result'];
|
|
$sLangId = $asReturnCode['desc_lang_id'];
|
|
$asLangParams = $asReturnCode['desc_lang_params'];
|
|
break;
|
|
default:
|
|
$sLangId = 'error.unknown_field';
|
|
$asLangParams = [$sField];
|
|
}
|
|
$asResult = $this->oUser->getUserById($iId);
|
|
break;
|
|
}
|
|
if(!$bSuccess && $sLangId=='') $sLangId = 'error.commit_db';
|
|
|
|
return self::getJsonResult($bSuccess, $sLangId, [$sType=>[$asResult]], $asLangParams);
|
|
}
|
|
|
|
public function createAdminSettings($sType) {
|
|
$bSuccess = false;
|
|
$sLangId = '';
|
|
$asResult = [];
|
|
|
|
switch($sType) {
|
|
case 'project':
|
|
$oProject = new Project($this->oDb);
|
|
$iNewProjectId = $oProject->createProjectId();
|
|
|
|
$oFeed = new Feed($this->oDb);
|
|
$oFeed->createFeedId($iNewProjectId);
|
|
|
|
$bSuccess = $iNewProjectId > 0;
|
|
$asResult = [
|
|
'project' => [$oProject->getProject()],
|
|
'feed' => [$oFeed->getFeed()]
|
|
];
|
|
break;
|
|
case 'feed':
|
|
$oFeed = new Feed($this->oDb);
|
|
$iNewFeedId = $oFeed->createFeedId($this->oProject->getProjectId());
|
|
$bSuccess = $iNewFeedId > 0;
|
|
$asResult = [
|
|
'feed' => [$oFeed->getFeed()]
|
|
];
|
|
break;
|
|
}
|
|
if(!$bSuccess && $sLangId=='') $sLangId = 'error.commit_db';
|
|
|
|
return self::getJsonResult($bSuccess, $sLangId, $asResult);
|
|
}
|
|
|
|
public function deleteAdminSettings($sType, $iId) {
|
|
$bSuccess = false;
|
|
$sLangId = '';
|
|
$asLangParams = [];
|
|
$asResult = [];
|
|
|
|
switch($sType) {
|
|
case 'project':
|
|
$oProject = new Project($this->oDb, $iId);
|
|
$asResult = $oProject->delete();
|
|
$sLangId = $asResult['project'][0]['desc_lang_id'];
|
|
$asLangParams = $asResult['project'][0]['desc_lang_params'];
|
|
$bSuccess = $asResult['project'][0]['del'];
|
|
break;
|
|
case 'feed':
|
|
$oFeed = new Feed($this->oDb, $iId);
|
|
$asResult = ['feed' => [$oFeed->delete()]];
|
|
$sLangId = $asResult['feed'][0]['desc_lang_id'];
|
|
$asLangParams = $asResult['feed'][0]['desc_lang_params'];
|
|
$bSuccess = $asResult['feed'][0]['result'];
|
|
break;
|
|
}
|
|
if(!$bSuccess && $sLangId=='') $sLangId = 'error.commit_db';
|
|
|
|
return self::getJsonResult($bSuccess, $sLangId, $asResult, $asLangParams);
|
|
}
|
|
|
|
public static function decToDms($dValue, $sType) {
|
|
if($sType=='lat') $sDirection = ($dValue >= 0)?'N':'S'; //Latitude
|
|
else $sDirection = ($dValue >= 0)?'E':'W'; //Longitude
|
|
|
|
$dLeft = abs($dValue);
|
|
|
|
//Degrees
|
|
$iDegree = floor($dLeft);
|
|
$dLeft -= $iDegree;
|
|
|
|
//Minutes
|
|
$iMinute = floor($dLeft * 60);
|
|
$dLeft -= $iMinute / 60;
|
|
|
|
//Seconds
|
|
$fSecond = round($dLeft * 3600, 1);
|
|
|
|
return
|
|
$iDegree.'°'.
|
|
self::getNumberWithLeadingZeros($iMinute, 2, 0)."'".
|
|
self::getNumberWithLeadingZeros($fSecond, 2, 1).'"'.
|
|
$sDirection;
|
|
}
|
|
|
|
public static function getNumberWithLeadingZeros($fValue, $iNbLeadingZeros, $iNbDigits) {
|
|
$sDecimalSeparator = '.';
|
|
if($iNbDigits > 0) $iNbLeadingZeros += mb_strlen($sDecimalSeparator) + $iNbDigits;
|
|
$sPattern = '%0'.$iNbLeadingZeros.$sDecimalSeparator.$iNbDigits.'f';
|
|
return sprintf($sPattern, $fValue);
|
|
}
|
|
|
|
public function getTimeFormat($iTime, $sTimeZone='') {
|
|
if($sTimeZone == '') $sTimeZone = date_default_timezone_get();
|
|
|
|
$oDate = new \DateTime('@'.$iTime);
|
|
$oDate->setTimezone(new \DateTimeZone($sTimeZone));
|
|
|
|
$sDate = $oDate->format('d/m/Y');
|
|
$sTime = $oDate->format('H:i');
|
|
return $this->oLang->getTranslation('time.date_time', [$sDate, $sTime]);
|
|
}
|
|
|
|
public static function getTimeZoneDayOffset($iTime, $sLocalTimeZone) {
|
|
$sSiteTimeZone = date_default_timezone_get();
|
|
$iLocalDate = (int) (new \DateTime('@'.$iTime))->setTimezone(new \DateTimeZone($sLocalTimeZone))->format('Ymd');
|
|
$iSiteDate = (int) (new \DateTime('@'.$iTime))->setTimezone(new \DateTimeZone($sSiteTimeZone ))->format('Ymd');
|
|
|
|
return ($iLocalDate == $iSiteDate)?'0':(($iLocalDate < $iSiteDate)?'+1':'-1');
|
|
}
|
|
|
|
public static function getTimeZoneFromDate($sDate) {
|
|
$sTimeZone = null;
|
|
|
|
preg_match('/(?<timezone>(\+|\-)\d{2}:?(\d{2}|))$/', $sDate, $asMatch);
|
|
if(array_key_exists('timezone', $asMatch)) {
|
|
$sTimeZone = $asMatch['timezone'];
|
|
|
|
//Complete short form: +12 => +1200
|
|
if(strlen($sTimeZone) == 3) $sTimeZone .= '00';
|
|
|
|
//Add colon: +1200 => +12:00
|
|
if(!strpos($sTimeZone, ':')) $sTimeZone = substr_replace($sTimeZone, ':', 3, 0);
|
|
}
|
|
return $sTimeZone;
|
|
}
|
|
}
|