function __construct()
{
parent::__construct();
$this->IS_AUTH = true;
$this->_conn = Connection::getInstance();
$cfg = new Config();
$this->filesettings = $cfg->getFileSettings();
}
public function getTableStructure($table)
{
$table = filter_var(trim($table), FILTER_SANITIZE_STRING);
if (!$table) {
throw $this->throwException(1000);
}
$table = Connection::getInstance()->escape_string($table);
$sql = "SHOW COLUMNS FROM {$table}";
$fields = $this->conn->getRows($sql);
$result = array();
foreach ($fields as $field) {
if (strstr($field->Extra, 'auto_increment') === false) {
// Skip autoincrement values;
$result[] = (object) array('label' => $field->Field, 'type' => $this->_getType($field->Type));
}
}
return $result;
}
private function _setFields(OTemplate $template)
{
$sql = 'DELETE FROM `itemdefinitions` WHERE `itemType`=' . (int) $template->id;
$this->_conn->deleteRow($sql);
$index = 0;
if (count($template->fields) == 0) {
return;
}
$sql = 'INSERT INTO `itemdefinitions` (`itemType`,`label`,`displaylabel`,`index`,`fieldType`, `contenttype`, `data`,`searchable`) VALUES ';
foreach ($template->fields as $field) {
$field = (object) $field;
$searchable = $field->searchable ? 1 : 0;
$sql .= "(" . $template->id . ", " . "'" . Connection::getInstance()->escape_string($field->label) . "', " . "'" . Connection::getInstance()->escape_string($field->displaylabel) . "', " . (int) $field->index . ", " . "'" . Connection::getInstance()->escape_string($field->type) . "', " . "'" . Connection::getInstance()->escape_string($field->contenttype) . "', " . "'" . Connection::getInstance()->escape_string($field->data) . "', " . $searchable . "), ";
$index++;
}
$sql = substr($sql, 0, strlen($sql) - 2);
$this->_conn->insertRow($sql);
}
/**
* Updates a administrator<br/>
* Required permissions:<br/>
* <ul>
* <li>IS_AUTH</li>
* <li>MANAGE_ADMIN</li>
* </ul>
* @param OAdministratorObject $administrator The administrator to update
* @return array An array of OAdministrators
* @throws \Exception
*/
public function updateAdministrator($administrator)
{
if (!$this->IS_AUTH) {
throw $this->throwException(AuthenticationException::NO_USER_AUTH);
}
$curAdministrator = $this->getAdministrator();
if (!$this->MANAGE_ADMIN && $curAdministrator->id != $administrator->id) {
throw $this->throwException(1005);
}
$result = $this->_conn->getRow("SELECT count(id) as ids " . "FROM administrators " . "WHERE email = '" . Connection::getInstance()->escape_string($administrator->email) . "' " . "AND administrators.id <> " . (int) $administrator->id);
if ($result->ids > 0) {
throw $this->throwException(1006);
}
$curAdministrator = $this->getAdministrator();
// Update the administrator
$query = "UPDATE administrators " . "SET email = '" . Connection::getInstance()->escape_string($administrator->email) . "', " . "name = '" . Connection::getInstance()->escape_string($administrator->name) . "' ";
if ($curAdministrator->id == $administrator->id) {
$query .= ", password='******' ";
}
$query .= "WHERE id = " . (int) $administrator->id;
$update = $this->_conn->updateRow($query);
//Only update permissions when manage_administrator is set
if ($this->MANAGE_ADMIN) {
$this->_setPermissionsToDb($administrator);
}
// Is the updated administrator the current administrator?
if ($curAdministrator->id == $administrator->id) {
$this->updatePermissions($administrator->permissions);
}
return $this->getAdministrators();
}
/**
* Changes dates with timestamp 0 (zero) to null values
* @param \stdClass $item The item to insert in the database
* @param array $fields The fields to nullify
*/
public static function nullifydate(&$item, $fields)
{
foreach ($fields as $field) {
if (!isset($item->{$field}) || (int) $item->{$field} == null || (int) $item->{$field} == 0) {
$item->{$field} = 'null';
} else {
$item->{$field} = "'" . Connection::getInstance()->escape_string($item->{$field}) . "'";
}
}
}
/**
* Saves a full poly
* @param OPoly $poly
* @return OPoly
*/
public function setFullPoly($poly)
{
$page = new Page();
$p = $page->setPage($poly, false, false);
if ($poly->pageId == 0) {
$poly->pageId = $p->pageId;
}
$sql = 'UPDATE `gm_polys` ' . 'SET `layer`=' . (int) $poly->layer . ', ' . '`uselayercolor`=' . (int) $poly->uselayercolor . ', ' . '`enabled`=' . (int) $poly->enabled . ', ' . '`pageId`=' . (int) $poly->pageId . ', ' . '`color`=' . (int) $poly->color . ', ' . '`label`=\'' . Connection::getInstance()->escape_string($poly->label) . '\', ' . '`search`=\'' . Connection::getInstance()->escape_string(BrightUtils::createSearchString($poly)) . '\' ' . 'WHERE `polyId`=' . (int) $poly->polyId;
$this->_conn->updateRow($sql);
$cache = new Cache();
$cache->deleteCacheByPrefix('marker');
return $this->getPoly($poly->polyId, true);
}
private function _getEventsByIdRangeAlt($start = 0, $limit = 20, $filter = null, $orderField = 'calendarId', $order = 'DESC')
{
$additionalFields = $this->_getAdditionalCalendarFields();
$fieldSql = '';
$joins = array();
if (count($additionalFields) != 0) {
$fields = array();
foreach ($additionalFields as $field) {
$field = Connection::getInstance()->escape_string($field);
$fields[] = " COALESCE(co{$field}.value, '') as `{$field}` ";
$joins[] = "LEFT JOIN calendarcontent co{$field} ON cn.calendarId = co{$field}.calendarId AND co{$field}.`lang`='nl' AND co{$field}.`field`='{$field}' ";
}
$fieldSql .= ', ' . join(', ', $fields);
}
$fromDate = 'NOW()';
if ($filter != null) {
if (!is_string($filter)) {
$filter = (object) $filter;
if (isset($filter->datestart) && isset($filter->dateend)) {
$dateStart = (double) $filter->datestart;
$dateEnd = (double) $filter->dateend;
$filter = $filter->filter;
if ($dateStart > 0 && $dateEnd > 0 && $dateEnd > $dateStart) {
$fromDate = "FROM_UNIXTIME({$dateStart})";
$joins[] = "INNER JOIN calendareventsnew cen ON cen.calendarId=cn.calendarId AND cen.starttime < FROM_UNIXTIME({$dateEnd}) AND cen.endtime > FROM_UNIXTIME({$dateStart})";
}
} else {
// Invalid object
$filter = '';
}
}
if ($filter != '' && $filter != null) {
if ((int) $filter > 0) {
// Search by id
$filter = (int) $filter;
$joins[] = "INNER JOIN calendarnew ci ON ci.calendarId = cn.calendarId AND ci.calendarId={$filter}";
} else {
$filter = Connection::getInstance()->escape_string($filter);
if (strpos($filter, '*') === false) {
$filter = '*' . $filter . '*';
}
$joins[] = "INNER JOIN calendarindex ci ON ci.calendarId = cn.calendarId AND MATCH(`ci`.`search`) AGAINST('{$filter}' IN BOOLEAN MODE) ";
}
}
}
// Reverse the array, since the inner joins might reduce the searching set significantly,
// so they should come first
$joins = array_reverse($joins);
$joinSql = join("\r\n", $joins) . "\r\n";
$limit++;
$sql = "SELECT\n\t\t\t\tcn.calendarId,\n\t\t\t\tcn.itemType,\n\t\t\t\tcn.label,\n\t\t\t\tcn.enabled,\n\t\t\t\tcn.modifiedby as modifiedby,\n\t\t\t\tcn.createdby as createdby,\n\t\t\t\tUNIX_TIMESTAMP(cn.creationdate) as creationdate,\n\t\t\t\tUNIX_TIMESTAMP(cn.modificationdate) as modificationdate,\n\t\t\t\tUNIX_TIMESTAMP(cee.starttime) as publicationdate,\n\t\t\t\tUNIX_TIMESTAMP(cee.endtime) as expirationdate,\n\t\t\t\tgmm.label as location\n\t\t\t\t{$fieldSql}\n\t\t\t\tFROM calendarnew cn\n\n\t\t\t\tLEFT JOIN `gm_markers` gmm ON cn.locationId = gmm.pageId\n LEFT JOIN ((SELECT calendarId, eventId, MIN(starttime) as starttime, endtime\n\t\t\t\t\t\t\t\tFROM calendareventsnew WHERE starttime > {$fromDate}\n\t\t\t\t\t\t\t\tGROUP BY calendarId,eventId)\n\t\t\t\t\t\tUNION\n\t\t\t\t\t\t\t\t(SELECT calendarId, eventId, MAX(starttime) as starttime, endtime\n\t\t\t\t\t\t\t\tFROM calendareventsnew WHERE starttime <= {$fromDate}\n\t\t\t\t\t\t\t\tGROUP BY calendarId,eventId)) AS cee ON cee.calendarId = cn.calendarId\n\t\t\t\t{$joinSql}\n\t\t\t\tWHERE cee.starttime IS NOT NULL\n\t\t\t\tGROUP BY cn.calendarId\n\t\t\t\tORDER BY {$orderField} {$order}\n\t\t\t\tLIMIT {$start},{$limit}";
$rows = $this->_conn->getRows($sql, 'OCalendarEvent');
$hasMoreResults = count($rows) == $limit;
$total = $start + count($rows);
if ($hasMoreResults) {
array_pop($rows);
}
$result = (object) array('result' => $rows, 'total' => $total);
return $result;
}
/**
* Updates a user<br/>
* @param OUserObject $user The user to update
* @param boolean $updatePassword When false, the password property is ignored
* @param boolean $updateActivated When false, the activated property is ignored
* @return int The id of the updated user, null on failure
* @throws \Exception
*/
private function _updateUser($user, $updatePassword = true, $updateActivated = true)
{
if (filter_var($user->email, FILTER_VALIDATE_EMAIL) === false) {
throw $this->throwException(ParameterException::EMAIL_EXCEPTION);
}
$user->email = Connection::getInstance()->escape_string($user->email);
$result = $this->conn->getRow("SELECT count(userId) as ids FROM `user`\n\t\t\t\t\t\t\t\t\t\t\tWHERE `email` = '{$user->email}' \n\t\t\t\t\t\t\t\t\t\t\tAND (`deleted` IS NULL OR YEAR(deleted)=0) AND user.userId <> " . (int) $user->userId);
if ($result->ids > 0) {
throw $this->throwException(UserException::DUPLICATE_EMAIL);
}
$query = 'UPDATE user ' . "SET `label` = '" . Connection::getInstance()->escape_string($user->label) . "',\n\t\t\t\t`email` = '" . Connection::getInstance()->escape_string($user->email) . "',\n\t\t\t\t`itemType` = " . (int) $user->itemType . ", ";
if ($updatePassword) {
$query .= "`password` = '" . Connection::getInstance()->escape_string($this->_hashPassword($user->password, $user->email)) . "', ";
}
if ($updateActivated) {
$query .= "activated = " . (int) $user->activated . ", ";
}
$del = (int) $user->deleted == 0 ? 'null' : 'NOW()';
$query .= "deleted = {$del}, modificationdate = UNIX_TIMESTAMP(NOW()) WHERE userId = " . (int) $user->userId;
$update = $this->conn->updateRow($query);
return $user->userId;
}
/**
* Deletes a custom setting<br/>
* Required permissions:<br/>
* <ul>
* <li>IS_AUTH</li>
* <li>MANAGE_SETTINGS</li>
* </ul>
* @param string $name The name of the setting
* @return array An array of custom settings
* @throws \Exception
*/
public function deleteSetting($name)
{
if (!$this->IS_AUTH) {
throw $this->throwException(AuthenticationException::NO_USER_AUTH);
}
if (!$this->MANAGE_SETTINGS) {
throw $this->throwException(3002);
}
$sql = 'DELETE FROM `settings` WHERE `name`=\'' . Connection::getInstance()->escape_string($name) . '\'';
$this->_conn->deleteRow($sql);
return $this->getSettings();
}
/**
* Adds a mailing to the mailqueue
* @param int $pid The pageId holding the mail template
* @param array $userGroups The usergroups to send the mailing to
* @return bool
* @throws \Exception
*/
public function sendMass($pid, $userGroups)
{
if (!$this->IS_AUTH) {
throw $this->throwException(AuthenticationException::NO_USER_AUTH);
}
sort($userGroups);
$sql = 'SELECT count(id) FROM mailqueue WHERE pageId=' . (int) $pid . ' AND `groups`=\'' . Connection::getInstance()->escape_string(join(',', $userGroups)) . '\' AND issend=0';
if ((int) $this->_conn->getField($sql) == 0) {
$sql = 'INSERT INTO mailqueue (`pageId`, `groups`, `dateadded`, `issend`) VALUES ' . '(' . (int) $pid . ', ' . '\'' . Connection::getInstance()->escape_string(join(',', $userGroups)) . '\', ' . 'NOW(), ' . '0)';
$res = $this->_conn->insertRow($sql);
}
return true;
}
/**
* Saves a element
* @param OPage $element The element to save
* @param bool $returnall
* @return \stdClass An object containing element, the just saved element and elements, an array of all elements
* @throws \Exception
*/
public function setElement($element, $returnall = true)
{
if (!$this->IS_AUTH) {
throw $this->throwException(AuthenticationException::NO_USER_AUTH);
}
if (method_exists($this->_hook, 'preSetElement')) {
$element = $this->_hook->preSetElement($element);
}
$element = $this->_page->setPage($element, false, false);
if (method_exists($this->_hook, 'postSetElement')) {
$this->_hook->postSetElement($element);
}
$c = new Cache();
$c->deleteCacheByPrefix("element_filter_");
$search = BrightUtils::createSearchString($element);
$search = Connection::getInstance()->escape_string($search);
$sql = "INSERT INTO pageindex (pageId, search) VALUES ({$element->pageId}, '{$search}') ON DUPLICATE KEY UPDATE search='{$search}' ";
Connection::getInstance()->insertRow($sql);
if (!$returnall) {
return $element;
}
return $this->_page->getPages(4, null, true);
}
/**
* Gets the settings for the currently authenticated administrator
* @return Object
*/
public function getSettings()
{
if (!isset($_SESSION['administratorId'])) {
return null;
}
$sql = 'SELECT `settings` FROM `administrators` WHERE `id`=' . (int) $_SESSION['administratorId'];
$current = Connection::getInstance()->getField($sql);
if ($current) {
$current = json_decode($current);
} else {
$current = new \stdClass();
}
return $current;
}
/**
* Checks if database updates are needed
* @param string $version The version string from the Frontend
*/
public function check($version)
{
$permissions = $this->getPermissions();
$this->updatePermissions(array('IS_AUTH', 'MANAGE_ADMIN', 'MANAGE_USER', 'CREATE_PAGE', 'DELETE_PAGE', 'EDIT_PAGE', 'MOVE_PAGE', 'DELETE_FILE', 'MANAGE_TEMPLATE', 'MANAGE_SETTINGS', 'UPLOAD_FILE', 'MANAGE_MAILINGS', 'MANAGE_CALENDARS', 'MANAGE_ELEMENTS', 'MANAGE_MAPS'));
$varr = explode(' ', $version);
$build = (int) array_pop($varr);
if (file_exists(BASEPATH . 'bright/site/hooks/UpdateHook.php')) {
require_once BASEPATH . 'bright/site/hooks/UpdateHook.php';
$ch = new \UpdateHook();
if (method_exists($ch, 'update')) {
$ch->update($build);
}
}
$prevbuild = $build - 1;
$this->_conn->updateRow("UPDATE `update` SET `build`={$prevbuild} WHERE `build`=99999");
$prevbuild = (int) $this->_conn->getField('SELECT MAX(`build`) FROM `update`');
if ($prevbuild >= $build) {
return;
}
$sqla[] = 'CREATE TABLE IF NOT EXISTS `treeaccess` (
`treeId` int(11) NOT NULL,
`groupId` int(11) NOT NULL,
KEY `treeId` (`treeId`,`groupId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;';
$sqla[] = 'CREATE TABLE IF NOT EXISTS `mailqueue` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pageId` int(11) NOT NULL,
`groups` varchar(255) CHARACTER SET utf8 NOT NULL,
`dateadded` datetime NOT NULL,
`issend` tinyint(4) NOT NULL DEFAULT \'0\',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;';
$sqla[] = 'CREATE TABLE IF NOT EXISTS `parsers` (
`parserId` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`label` VARCHAR( 255 ) NOT NULL ,
UNIQUE (`label`)
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;';
$colcheck = "SHOW COLUMNS FROM `user` WHERE `field`='deleted'";
$field = $this->_conn->getRow($colcheck);
if (strpos($field->Type, 'tinyint') !== false) {
$sqla[] = "ALTER TABLE `user` CHANGE `deleted` `deleted` TINYINT( 1 ) NULL DEFAULT '0'";
$sqla[] = "UPDATE `user` SET `deleted`= null WHERE `deleted`=0";
$sqla[] = "ALTER TABLE `user` CHANGE `deleted` `deleted` VARCHAR( 50 ) NULL DEFAULT NULL";
$sqla[] = "UPDATE `user` SET `deleted`= NOW() WHERE `deleted`='1'";
$sqla[] = "ALTER TABLE `user` CHANGE `deleted` `deleted` DATETIME NULL DEFAULT NULL";
$sqla[] = "ALTER TABLE `user` ADD UNIQUE (`email` ,`deleted`)";
}
$colcheck = "SHOW COLUMNS FROM `userfields` WHERE `field`='lang'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sqla[] = "ALTER TABLE `userfields` ADD `lang` VARCHAR( 3 ) NOT NULL DEFAULT 'tpl' AFTER `userId`";
$sqla[] = "ALTER TABLE `userfields` ADD `index` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `value`";
}
$colcheck = "SHOW COLUMNS FROM `page` WHERE `field`='alwayspublished'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sqla[] = "ALTER TABLE `page` CHANGE `allwayspublished` `alwayspublished` TINYINT( 1 ) NOT NULL ;";
$sqla[] = "UPDATE administrators SET settings = REPLACE(settings, 'allwayspublished', 'alwayspublished') WHERE settings LIKE '%allwayspublished%';";
}
$colcheck = "SHOW COLUMNS FROM `content` WHERE `field`='deleted'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sqla[] = "ALTER TABLE `content` ADD UNIQUE (`pageId` ,`lang` ,`field` ,`index`);";
$sqla[] = "ALTER TABLE `userfields` ADD UNIQUE (`userId` ,`lang` ,`field` ,`index`);";
$sqla[] = "ALTER TABLE `content` ADD `deleted` TINYINT( 1 ) NOT NULL DEFAULT '0'";
}
$sqla[] = "CREATE TABLE IF NOT EXISTS `calendarnew` (\n\t\t\t\t\t `calendarId` int(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t `itemType` int(11) NOT NULL,\n\t\t\t\t\t `label` varchar(255) NOT NULL,\n\t\t\t\t\t `recur` varchar(255) DEFAULT NULL,\n\t\t\t\t\t `until` datetime DEFAULT NULL,\n\t\t\t\t\t `deleted` datetime DEFAULT NULL,\n\t\t\t\t\t `creationdate` timestamp NULL DEFAULT NULL,\n\t\t\t\t\t `modificationdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\t\t\t\t\t `createdby` int(11) DEFAULT NULL,\n\t\t\t\t\t `modifiedby` int(11) DEFAULT NULL,\n\t\t\t\t\t PRIMARY KEY (`calendarId`)\n\t\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
$sqla[] = "CREATE TABLE IF NOT EXISTS `calendardates` (\n\t\t\t\t `dateId` int(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t `calendarId` int(11) NOT NULL,\n\t\t\t\t `starttime` TIMESTAMP NULL DEFAULT NULL,\n\t\t\t\t `endtime` TIMESTAMP NULL DEFAULT NULL,\n\t\t\t\t `allday` tinyint(1) NOT NULL DEFAULT '0',\n\t\t\t\t `deleted` tinyint(1) NOT NULL DEFAULT '0',\n\t\t\t\t PRIMARY KEY (`dateId`),\n\t\t\t\t UNIQUE KEY `calendarId` (`calendarId`,`starttime`,`endtime`)\n\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8";
$sqla[] = "CREATE TABLE IF NOT EXISTS `calendarcontent` (\n\t\t\t\t `contentId` int(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t `calendarId` int(11) NOT NULL,\n\t\t\t\t `lang` varchar(3) NOT NULL DEFAULT 'ALL',\n\t\t\t\t `field` varchar(20) NOT NULL,\n\t\t\t\t `value` longtext NOT NULL,\n\t\t\t\t `index` int(11) NOT NULL DEFAULT '0',\n\t\t\t\t `deleted` tinyint(1) NOT NULL DEFAULT '0',\n\t\t\t\t `searchable` tinyint(1) NOT NULL DEFAULT '0',\n\t\t\t\t PRIMARY KEY (`contentId`),\n\t\t\t\t UNIQUE KEY `callangfield` (`calendarId`,`lang`,`field`, `index`),\n\t\t\t\t KEY `lang` (`lang`,`field`),\n\t\t\t\t FULLTEXT KEY `value` (`value`)\n\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
$sqla[] = "CREATE TABLE IF NOT EXISTS `calendareventsnew` (\n\t\t\t\t `eventId` int(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t `calendarId` int(11) NOT NULL,\n\t\t\t\t `starttime` TIMESTAMP NULL DEFAULT NULL,\n\t\t\t\t `endtime` TIMESTAMP NULL DEFAULT NULL,\n\t\t\t\t `deleted` tinyint(1) NOT NULL,\n\t\t\t\t PRIMARY KEY (`eventId`),\n \t\t\t\t `allday` TINYINT( 1 ) NOT NULL DEFAULT '0',\n\t\t\t\t UNIQUE KEY `calendarId` (`calendarId`,`starttime`,`endtime`),\n\t\t\t\t KEY `calendarId2` (`calendarId`),\n\t\t\t\t KEY `starttime` (`starttime`)\n\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
$this->_performQueries($sqla);
$tblcheck = "show tables like 'calendareventsnew'";
if ($this->_conn->getField($tblcheck)) {
$colcheck = "SHOW COLUMNS FROM `calendareventsnew` WHERE `field`='allday'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sqla[] = "ALTER TABLE `calendareventsnew` ADD `allday` TINYINT( 1 ) NOT NULL DEFAULT '0'";
$sqla[] = "ALTER TABLE `calendareventsnew` CHANGE `starttime` `starttime` TIMESTAMP NULL DEFAULT NULL ,CHANGE `endtime` `endtime` TIMESTAMP NULL DEFAULT NULL";
}
$colcheck = "SHOW COLUMNS FROM `calendareventsnew` WHERE `field`='noend'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sqla[] = "ALTER TABLE `calendareventsnew` ADD `noend` TINYINT( 1 ) NOT NULL DEFAULT '0'";
}
}
$tblcheck = "show tables like 'calendardates'";
if ($this->_conn->getField($tblcheck)) {
$colcheck = "SHOW COLUMNS FROM `calendardates` WHERE `field`='noend'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sqla[] = "ALTER TABLE `calendardates` ADD `noend` TINYINT( 1 ) NOT NULL DEFAULT '0'";
}
}
$colcheck = "SHOW COLUMNS FROM `calendarnew` WHERE `field`='enabled'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sql = "ALTER TABLE `calendarnew` ADD `enabled` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `until` , ADD INDEX ( `enabled` )";
$this->_conn->updateRow($sql);
}
$colcheck = "SHOW COLUMNS FROM `calendarnew` WHERE `field`='locationId'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sqla[] = "ALTER TABLE `calendarnew` ADD `locationId` INT( 11 ) NULL DEFAULT NULL AFTER `calendarId`, ADD INDEX ( `locationId` )";
}
$colcheck = "SHOW COLUMNS FROM `gm_markers` WHERE `field`='enabled'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sql = "ALTER TABLE `gm_markers` ADD `enabled` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `deleted` , ADD INDEX ( `enabled` )";
$this->_conn->updateRow($sql);
$sql = "ALTER TABLE `gm_polys` ADD `enabled` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `deleted` , ADD INDEX ( `enabled` )";
$this->_conn->updateRow($sql);
}
$colcheck = "SHOW COLUMNS FROM `gm_markers` WHERE `field`='street'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sql = "ALTER TABLE `gm_markers` ADD `street` VARCHAR( 255 ) NULL DEFAULT NULL ,\n\t\t\t\t\t\tADD `number` VARCHAR( 255 ) NULL DEFAULT NULL ,\n\t\t\t\t\t\tADD `zip` VARCHAR( 255 ) NULL DEFAULT NULL ,\n\t\t\t\t\t\tADD `city` VARCHAR( 255 ) NULL DEFAULT NULL ,\n\t\t\t\t\t\tADD `country` INT( 11 ) NULL DEFAULT NULL";
$this->_conn->updateRow($sql);
}
$colcheck = "SHOW COLUMNS FROM `gm_polys` WHERE `field`='search'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sql = "ALTER TABLE `gm_polys` ADD `search` LONGTEXT NULL , ADD FULLTEXT (`search`)";
$this->_conn->updateRow($sql);
}
$colcheck = "SHOW COLUMNS FROM `gm_markers` WHERE `field`='search'";
$hasField = $this->_conn->getField($colcheck);
if ($hasField == null) {
$sqla[] = "ALTER TABLE `gm_polys` CHANGE `pageId` `pageId` INT( 11 ) NULL DEFAULT NULL ,\nCHANGE `label` `label` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL";
$sql = "ALTER TABLE `gm_markers` ADD `search` LONGTEXT NULL , ADD FULLTEXT (`search`)";
$this->_conn->updateRow($sql);
$maps = new Maps();
$lay = new Layers();
$layers = $lay->getLayers();
$markers = $this->_conn->getRows("SELECT markerId, pageId FROM gm_markers");
foreach ($markers as $marker) {
if ($marker->pageId) {
$sql = "SELECT `value` FROM content WHERE pageId = {$marker->pageId}";
$rows = $this->_conn->getFields($sql);
$search = implode("\r\n", $rows);
$search = Connection::getInstance()->escape_string($search);
$sql = "UPDATE gm_markers SET `search`='{$search}' WHERE markerId={$marker->markerId}";
$this->_conn->updateRow($sql);
}
}
}
$colcheck = "SHOW COLUMNS FROM `page` WHERE `field`='creationdate'";
if ($this->_conn->getField($colcheck) == null) {
$sqla[] = "ALTER TABLE `page` ADD `creationdate` TIMESTAMP NULL DEFAULT NULL ,\n\t\t\t\t\t\tADD `createdby` INT( 11 ) NULL DEFAULT NULL ,\n\t\t\t\t\t\tADD `modifiedby` INT( 11 ) NULL DEFAULT NULL";
}
$colcheck = "SHOW COLUMNS FROM `backup` WHERE `field`='content'";
$c = $this->_conn->getRow($colcheck);
if ($c->Type == 'text') {
$sqla[] = "ALTER TABLE `backup` CHANGE `content` `content` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL";
}
$tblcheck = "SHOW TABLES LIKE 'calendarindex'";
if (!$this->_conn->getField($tblcheck)) {
$this->_conn->insertRow("CREATE TABLE IF NOT EXISTS `calendarindex` (\n\t\t\t\t\t\t\t\t\t\t `calendarId` int(11) NOT NULL DEFAULT '0',\n\t\t\t\t\t\t\t\t\t\t `search` text,\n\t\t\t\t\t\t\t\t\t\t PRIMARY KEY (`calendarId`),\n\t\t\t\t\t\t\t\t\t\t FULLTEXT KEY `search` (`search`)\n\t\t\t\t\t\t\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8;");
$cal = new Calendar();
$ids = $this->_conn->getFields("SELECT calendarId FROM calendarnew");
$sqlc = "INSERT INTO calendarindex (calendarId, search) VALUES";
$sqlca = array();
foreach ($ids as $id) {
$ev = $cal->getEvent($id);
$search = BrightUtils::createSearchString($ev);
if ((int) $ev->locationId > 0) {
$search .= $this->_conn->getField("SELECT search FROM gm_markers WHERE pageId={$ev->locationId}");
}
$search = Connection::getInstance()->escape_string($search);
$sqlca[] = "({$ev->calendarId}, '{$search}')";
}
if (count($sqlca) > 0) {
$sqlc .= implode(",\r\n", $sqlca);
$sqla[] = $sqlc;
}
$sqla[] = "ALTER TABLE `calendareventsnew` ADD INDEX ( `starttime` )";
}
$tblcheck = "SHOW TABLES LIKE 'pageindex'";
if (!$this->_conn->getField($tblcheck)) {
$this->_conn->insertRow("CREATE TABLE IF NOT EXISTS `pageindex` (\n\t\t\t\t\t\t\t\t\t\t `pageId` int(11) NOT NULL DEFAULT '0',\n\t\t\t\t\t\t\t\t\t\t `search` text,\n\t\t\t\t\t\t\t\t\t\t PRIMARY KEY (`pageId`),\n\t\t\t\t\t\t\t\t\t\t FULLTEXT KEY `search` (`search`)\n\t\t\t\t\t\t\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8;");
$el = new Element();
$page = new Page();
$ids = $el->getElements(false);
$sqle = "INSERT INTO pageindex (pageId, search) VALUES";
$sqlea = array();
foreach ($ids as $elm) {
$ev = $page->getPageById($elm->pageId);
$search = BrightUtils::createSearchString($ev);
$search = Connection::getInstance()->escape_string($search);
$sqlea[] = "({$ev->pageId}, '{$search}')";
}
if (count($sqlea) > 0) {
$sqle .= implode(",\r\n", $sqlea);
$sqla[] = $sqle;
}
}
if ($prevbuild < 7098) {
// Update user settings, this fixes a bug with AmfPHP 2.x,
// which does not correctly deserialize flex.messaging.io.objectproxy to php stdClass objects
$rows = Connection::getInstance()->getRows("SELECT id, settings FROM `administrators`");
foreach ($rows as $row) {
$settings = json_decode($row->settings);
if ($settings) {
if (isset($settings->_externalizedData)) {
$settings = $settings->_externalizedData;
}
// Clean up settings object
foreach ($settings as $key => $value) {
if (strpos($key, 'pageDivider_') === 0) {
unset($settings->{$key});
}
}
$settings = Connection::getInstance()->escape_string(json_encode($settings));
$sql = "UPDATE administrators SET settings='{$settings}' WHERE id={$row->id}";
Connection::getInstance()->updateRow($sql);
}
}
}
// Update to latest version
$sqla[] = 'TRUNCATE `update`';
$sqla[] = 'INSERT INTO `update` (`build`) VALUES (' . $build . ')';
$this->_performQueries($sqla);
$sql = "SHOW TABLES LIKE 'calendar'";
$rows = $this->_conn->getRow($sql);
if ($rows) {
$sql = 'SELECT * FROM calendar';
$rows = $this->_conn->getRows($sql);
if ($rows) {
$page = new Page();
$cal = new Calendar();
$ids = array();
foreach ($rows as $row) {
$ids[] = $row->pageId;
$ev = $page->getPageById($row->pageId);
$cdo = new OCalendarDateObject();
$cdo->starttime = $ev->publicationdate;
$cdo->endtime = $ev->expirationdate;
$cdo->allday = date('d-m-Y', $cdo->starttime) != date('d-m-Y', $cdo->endtime) || $row->allday;
if (date('H', $cdo->starttime) == 22) {
$cdo->starttime += 7200;
$cdo->endtime += 7200;
$cdo->allday = 1;
}
if (date('H', $cdo->starttime) == 23) {
$cdo->starttime += 3600;
$cdo->endtime += 3600;
$cdo->allday = 1;
}
if (date('H', $cdo->endtime) == 22) {
$cdo->starttime += 7200;
$cdo->endtime += 7200;
$cdo->allday = 1;
}
if (date('H', $cdo->endtime) == 23) {
$cdo->starttime += 3600;
$cdo->endtime += 3600;
$cdo->allday = 1;
}
$cestring = serialize($ev);
$cestring = str_replace('O:5:"OPage"', 'O:14:"OCalendarEvent"', $cestring);
$cestring = str_replace('s:13:"_explicitType";s:5:"OPage"', 's:13:"_explicitType";s:14:"OCalendarEvent"', $cestring);
$ev = unserialize($cestring);
$ev->dates = array($cdo);
$cal->setEvent($ev);
}
$page->deletePages($ids);
$sql = 'DELETE FROM calendar';
$rows = $this->_conn->deleteRow($sql);
$sql = 'DELETE FROM calendarevents';
$rows = $this->_conn->deleteRow($sql);
}
}
$this->updatePermissions($permissions);
}
function __construct()
{
parent::__construct();
$this->_conn = Connection::getInstance();
}
/**
* Creates a new entry in the backup
* @param OPage $page
* @param string $table
*/
public function setBackup($page, $table)
{
try {
$p = Connection::getInstance()->escape_string(base64_encode(@json_encode($page)));
$pid = (int) $page->pageId;
$sql = "INSERT INTO `backup` (`pid`, `table`, `content`, `date`) VALUES ({$pid}, '{$table}', '{$p}', NOW())";
$this->_conn->insertRow($sql);
$sql = 'DELETE FROM `backup` WHERE `date` < (NOW() - INTERVAL 2 MONTH)';
$this->_conn->deleteRow($sql);
} catch (\Exception $e) {
// Cannot json encode or other error
mail(SYSMAIL, "Backup error in " . SITENAME, "Cannot create backup, reason:\r\n" . $e->getTraceAsString());
}
}
/**
* Gets all the treenodes with the specified pageId
* @param int $pageId The id of the page
* @return array A multidimensional array, with treeId and path
* @throws \Exception
* @since 2.3 - 8 sep 2010
*/
public function getCanonical($pageId)
{
if (!is_numeric($pageId)) {
throw $this->throwException(ParameterException::INTEGER_EXCEPTION);
}
$sql = 'SELECT treeId FROM tree WHERE pageId=' . (int) $pageId . ' ORDER BY treeId';
$conn = Connection::getInstance();
$arr = $conn->getFields($sql);
$nodes = array();
foreach ($arr as $treeId) {
$nodes[] = array('treeId' => $treeId, 'path' => $this->getPath($treeId));
}
return $nodes;
}
/**
* Saves the (language specific) content of a layer
* @param stdClass $layer The layer object
*/
private function _setContent($layer)
{
$sql = 'SELECT MAX(`contentId`) FROM `gm_layer_content` WHERE `layerId`=' . (int) $layer->layerId;
$oldId = $this->_conn->getField($sql);
if (!$oldId) {
$oldId = 0;
}
$sql = 'INSERT INTO `gm_layer_content` (`layerId`, `lang`, `field`, `value`) VALUES ';
foreach ($layer->content as $field => $langs) {
foreach ($langs as $lang => $val) {
$sql .= '(' . $layer->layerId . ', ' . "'" . Connection::getInstance()->escape_string($lang) . "', " . "'" . Connection::getInstance()->escape_string($field) . "', " . "'" . Connection::getInstance()->escape_string($val) . "'), ";
}
}
$sql = substr($sql, 0, strlen($sql) - 2);
$result = $this->_conn->insertRow($sql);
if ($result !== false && $result > 0) {
// All is well, clean up old data
$sql = 'DELETE FROM `gm_layer_content` WHERE `contentId` <= ' . $oldId . ' AND `layerId`=' . (int) $layer->layerId;
$this->_conn->deleteRow($sql);
}
}
/**
* Checks if a label is changed
* @param OPage $page The page
* @return bool true when the label is different and the page is present in the navigationtree
* @throws \Exception
*/
private function _cachebleChanged($page)
{
if (!is_numeric($page->pageId)) {
throw $this->throwException(ParameterException::INTEGER_EXCEPTION);
}
$sql = 'SELECT COUNT(p.pageId)
FROM `page` p
RIGHT JOIN tree t ON t.pageId=p.pageId
WHERE p.pageId=' . $page->pageId . '
AND (`label`<>\'' . Connection::getInstance()->escape_string($page->label) . '\'
OR `showinnavigation` <> ' . (int) $page->showinnavigation . '
OR `alwayspublished` <> ' . (int) $page->alwayspublished . '
OR UNIX_TIMESTAMP(`publicationdate`) <> ' . (int) $page->publicationdate . '
OR UNIX_TIMESTAMP(`expirationdate`) <> ' . (int) $page->expirationdate . ')';
$res = $this->conn->getField($sql);
return (int) $res > 0;
}
/**
* Creates a new usergroup, or renames an existing one
* @param object $group The group to create or update
* @return \stdClass The created / updated group
* @throws \Exception
* @since 1.2
* @todo Implement update
*/
public function setUserGroup($group)
{
if (!$this->IS_AUTH) {
throw $this->throwException(AuthenticationException::NO_USER_AUTH);
}
if (!$this->MANAGE_USER) {
throw $this->throwException(UserException::MANAGE_USER);
}
$group = (object) $group;
if (!isset($group->groupname)) {
throw $this->throwException(8006);
}
$sql = "SELECT groupname FROM usergroups WHERE groupname='" . Connection::getInstance()->escape_string($group->groupname) . "'";
$res = $this->_conn->getField($sql);
if ($res) {
throw $this->throwException(8005);
}
$sql = "INSERT INTO usergroups (groupname) VALUES ('" . Connection::getInstance()->escape_string($group->groupname) . "')";
$id = $this->_conn->insertRow($sql);
return (object) array('groupId' => (int) $id, 'groupname' => $group->groupname);
}
/**
* Checks if a label is changed
* @param array $titles An array of lang > titles
* @param int $pageId The Id of the page
* @return boolean true when (any of) the title(s) is / are different and the page is present in the navigationtree
*/
private function _titleChanged($titles, $pageId)
{
$joins = '';
$i = 0;
foreach ($titles as $lang => $value) {
$joins .= 'RIGHT JOIN content c' . $i . ' ON c' . $i . '.pageId=p.pageId AND c' . $i . '.`field`=\'title\' AND c' . $i . '.`value` <>\'' . Connection::getInstance()->escape_string($value) . '\'' . "\r\n";
$i++;
}
$sql = 'SELECT COUNT(p.pageId)
FROM `page` p' . "\r\n" . $joins . 'RIGHT JOIN tree t ON t.pageId=p.pageId
WHERE p.pageId=' . $pageId;
$res = $this->conn->getField($sql);
return (int) $res > 0;
}
