public static function checkIfChildren($parentIds, $id)
{
$parentIds = implode(',', $parentIds);
if (empty($parentIds)) {
return false;
}
$table = ipTable('page');
$sql = "SELECT `id` FROM {$table} WHERE `parentId` IN ({$parentIds}) AND `isVisible` = 1 AND `isDeleted` = 0 ";
$results = ipDb()->fetchAll($sql);
$ids = array();
$found = false;
foreach ($results as $result) {
$ids[] = $result['id'];
if ($result['id'] == $id) {
$found = true;
break;
}
}
if ($found) {
return true;
} elseif (!empty($ids)) {
return self::checkIfChildren($ids, $id);
} else {
return false;
}
}
public static function ipWidgetDuplicated($data)
{
$oldId = $data['oldWidgetId'];
$newId = $data['newWidgetId'];
$newRevisionId = ipDb()->selectValue('widget', 'revisionId', array('id' => $newId));
$widgetTable = ipTable('widget');
$sql = "\n UPDATE\n {$widgetTable}\n SET\n `blockName` = REPLACE(`blockName`, 'block_" . (int) $oldId . "', 'block_" . (int) $newId . "')\n WHERE\n `revisionId` = :newRevisionId\n ";
ipDb()->execute($sql, array('newRevisionId' => $newRevisionId));
}
/**
* Drops the datatable repository db table
*/
private function dropDataTableRepository()
{
$ipTable = ipTable(TableRepository::DATA_TABLE_REPOSITORY);
$sql = "\n DROP TABLE {$ipTable}\n ";
try {
ipDb()->execute($sql);
} catch (\Ip\Exception\Db $e) {
ipLog()->error("Could not drop repository table. Statement: {$sql}, Message: " . $e->getMessage());
}
}
public static function unbindFile($file, $plugin, $instanceId, $baseDir = 'file/repository/')
{
$condition = array('fileName' => $file, 'plugin' => $plugin, 'instanceId' => $instanceId, 'baseDir' => $baseDir);
$sql = 'DELETE FROM ' . ipTable('repository_file') . '
WHERE filename = :fileName
AND plugin = :plugin
AND instanceId = :instanceId
AND baseDir = :baseDir
LIMIT 1';
// it is important to delete only one record
ipDb()->execute($sql, $condition);
$usages = self::whoUsesFile($file);
if (empty($usages)) {
$reflectionModel = ReflectionModel::instance();
$reflectionModel->removeReflections($file);
}
}
public function activate()
{
$sql = '
CREATE TABLE IF NOT EXISTS
' . ipTable(\Plugin\MasonryGrid\Config::TABLE_NAME) . '
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`itemOrder` double,
`widgetId` int(11),
`title` varchar(255),
`image` varchar(255),
`description` text,
`url` varchar(255),
`isVisible` int(1),
PRIMARY KEY (`id`)
)';
ipDb()->execute($sql);
}
public static function getKeywordData()
{
$pageTable = ipTable('page');
$keywordlistTable = ipTable(self::TABLE_NAME);
$langCode = ipContent()->getCurrentLanguage()->getCode();
$excludeFields = ' AND p.IsVisible = 1 AND p.IsDisabled = 0 AND p.IsDeleted = 0';
$securedPages = ipGetOption('Keywordlist.securedPages') == 1 ? '' : 'AND p.IsSecured = 0';
if (ipGetOption('Keywordlist.useMetadata') == 1) {
$sql = "SELECT p.id, p.title, p.urlPath, p.keywords, p.description FROM {$pageTable} p WHERE p.languageCode = '{$langCode}' AND p.keywords != '' {$excludeFields} {$securedPages}";
} else {
$sql = "SELECT p.id, p.title, p.urlPath, k.keywords, k.description FROM {$pageTable} p, {$keywordlistTable} k WHERE p.id = k.pageid AND p.languageCode = '{$langCode}' AND k.keywords != '' {$excludeFields} {$securedPages}";
}
$keywordArray = array();
$entries = array();
$entries = ipDb()->fetchAll($sql);
$existingChars = array();
if (!empty($entries)) {
// prepare array with keywords and pages
$keywordArray = array();
$charSource = array('ä', 'ö', 'ü', 'ß', 'Ä', 'Ö', 'Ü');
$charReplace = array('a', 'o', 'u', 's', 'A', 'O', 'U');
foreach ($entries as $entry) {
$keywords = array_map('trim', explode(",", $entry['keywords']));
foreach ($keywords as $keyword) {
$firstChar = strtoupper(str_replace($charSource, $charReplace, mb_substr($keyword, 0, 1, 'UTF-8')));
if (is_numeric($firstChar)) {
$firstChar = '0-9';
}
$keywordArray[] = array('keyword' => $keyword, 'char' => $firstChar, 'id' => $entry['id'], 'title' => $entry['title'], 'description' => $entry['description'], 'urlPath' => $entry['urlPath']);
}
}
// sorting keywordarray depending first char of keyword
foreach ($keywordArray as $key => $row) {
$char[$key] = $row['char'];
}
array_multisort($char, SORT_ASC, SORT_STRING, $keywordArray);
$existingChars = array_unique($char);
}
$keywordData = array();
$keywordData['entries'] = $keywordArray;
$keywordData['chars'] = $existingChars;
return $keywordData;
}
public function activate()
{
$sql = '
CREATE TABLE IF NOT EXISTS
' . ipTable('slideshow') . '
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`widgetId` int(11),
`slideshowOrder` double,
`title` varchar(255),
`text` varchar(255),
`url` varchar(255),
`Enabled` boolean,
`image` varchar(255),
`lang` varchar(3),
`itemOrder` double,
`isVisible` int(1),
PRIMARY KEY (`id`)
)';
ipDb()->execute($sql);
}
protected static function updateTableUrl($oldUrl, $newUrl, $table, $keyFields)
{
$old = parse_url($oldUrl);
$new = parse_url($newUrl);
$oldPart = $old['host'] . $old['path'];
$newPart = $new['host'] . $new['path'];
$quotedPart = substr(ipDb()->getConnection()->quote('://' . $oldPart), 1, -1);
$search = '%' . addslashes($quotedPart) . '%';
$tableWithPrefix = ipTable($table);
$records = ipDb()->fetchAll("SELECT * FROM {$tableWithPrefix} WHERE `value` LIKE ?", array($search));
if (!$records) {
return;
}
if ($newUrl == ipConfig()->baseUrl()) {
//the website has been moved
$search = '%\\b(https?://)' . preg_quote($oldPart, '%') . '%';
} else {
//internal page url has changed
// \b - start at word boundary
// (https?://) - protocol
// (/?) - allow optional slash at the end of url
// (?= ) - symbols expected after url
// \Z - end of subject or end of line
$search = '%\\b(https?://)' . preg_quote($oldPart, '%') . '(/?)(?=["\'?]|\\s|\\Z)%';
}
foreach ($records as $row) {
// ${1} - protocol, ${2} - optional '/'
$after = preg_replace($search, '${1}' . $newPart . '${2}', $row['value']);
if ($after != $row['value']) {
$where = array();
foreach ($keyFields as $keyField) {
$where[$keyField] = $row[$keyField];
}
ipDb()->update($table, array('value' => $after), $where);
}
}
}
/**
* @param null $from
* @param null $till
* @param string $orderBy
* @param string $direction
* @return \Ip\Page[]
* @throws Exception
*/
public function getChildren($from = null, $till = null, $orderBy = 'pageOrder', $direction = 'ASC')
{
switch ($orderBy) {
case 'pageOrder':
case 'title':
case 'metaTitle':
case 'createdAt':
case 'updatedAt':
case 'deletedAt':
//do nothing;
break;
default:
throw new \Ip\Exception("getChildren can't accept " . esc($orderBy) . " as an order field.");
}
if (strtoupper($direction) == 'ASC') {
$direction = 'ASC';
} else {
$direction = 'DESC';
}
$table = ipTable('page');
$sql = "\n SELECT\n *\n FROM\n {$table}\n WHERE\n parentId = :parentId AND\n isVisible = 1 AND\n isDeleted = 0\n ORDER BY\n " . $orderBy . "\n " . $direction . "\n ";
$params = array('parentId' => $this->id);
if ($from !== null || $till !== null) {
$sql .= " LIMIT " . (int) $from . " , " . (int) $till;
}
$list = ipDb()->fetchAll($sql, $params);
return static::createList($list);
}
/**
* Removes pages that were deleted before given time
*
* @param string $timestamp in mysql format.
* @return int Count of deleted pages.
*/
public static function removeDeletedBefore($timestamp)
{
$table = ipTable('page');
$pages = ipDb()->fetchAll("SELECT `id` FROM {$table} WHERE `isDeleted` = 1 AND `deletedAt` < ?", array($timestamp));
foreach ($pages as $page) {
static::removeDeletedPage($page['id']);
}
}
public static function sentOrLockedCount($minutes)
{
$table = ipTable('email_queue');
$sql = "select count(*) as `sent` from {$table} where\n (`send` is not NULL and " . ipDb()->sqlMaxAge('send', $minutes, 'MINUTE') . ")\n or (`lock` is not NULL and send is null) ";
return ipDb()->fetchValue($sql);
}
/**
*
* Delete all not published revisions that are older than X days.
* @param int $days
*/
public static function removeOldRevisions($days)
{
//
// 1) Dynamic Widgets (including revisions)
// Dynamic widgets have an associated revision.
// That revision's creation time and publication
// state indicates if a widget should be removed
// or not from corresponding db table 'ip_widget'.
//
$table = ipTable('revision');
$sql = "\n SELECT `revisionId` FROM {$table}\n WHERE (" . ipDb()->sqlMinAge('createdAt', $days * 24, 'HOUR') . ") AND `isPublished` = 0\n ";
$revisionList = ipDb()->fetchColumn($sql);
foreach ($revisionList as $revisionId) {
\Ip\Internal\Content\Service::removeRevision($revisionId);
}
//
// 2) Static Widgets (from static blocks only!)
// Static widgets are presisted with revisionId=0.
// Therefore, we've to make the time check on widget's
// 'createdAt' column combined with 'isDeleted=1' flag
// and 'revisionId=0' indicating widget's removal state.
//
$table = ipTable('widget');
$sql = $sql = "\n SELECT `id` FROM {$table}\n WHERE (" . ipDb()->sqlMinAge('createdAt', $days * 24, 'HOUR') . ") \n AND `revisionId` = 0 AND `isDeleted` = 1 AND `deletedAt` IS NOT NULL\n ";
$staticWidgetList = ipDb()->fetchColumn($sql);
foreach ($staticWidgetList as $staticWidgetId) {
\Ip\Internal\Content\Service::removeWidget($staticWidgetId);
}
}
/**
* Creates the DB table
*/
private function createDbTableIfNotExists()
{
$ipTable = ipTable($this->_name);
$attributeDefinition = '';
foreach ($this->_columns as $column) {
$attributeDefinition .= ' `' . $column['attribute'] . '` varchar(255),';
}
$sql = "\n CREATE TABLE IF NOT EXISTS {$ipTable}\n (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n {$attributeDefinition}\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
try {
ipDb()->execute($sql);
} catch (\Ip\Exception\Db $e) {
ipLog()->error("Could not create data table. Statement: {$sql}, Message: " . $e->getMessage());
throw $e;
}
}
private function removeReflectionRecords($file)
{
$dbh = ipDb()->getConnection();
$sql = "\n DELETE FROM\n " . ipTable('repository_reflection') . "\n WHERE\n original = :original\n ";
$params = array('original' => $file);
$q = $dbh->prepare($sql);
$q->execute($params);
}
/**
* @param int $revisionId
* @return string
*/
protected static function revisionFingerprint($revisionId)
{
$table = ipTable('widget');
// compare revision content
$sql = "\n SELECT\n `name`\n FROM\n {$table}\n WHERE\n `revisionId` = :revisionId\n AND\n `name` != 'Columns'\n AND\n `isDeleted` = 0\n ORDER BY\n blockName, `position`\n ";
$params = array('revisionId' => $revisionId);
$widgetNames = ipDb()->fetchColumn($sql, $params);
// compare revision content
$sql = "\n SELECT\n `data`\n FROM\n {$table}\n WHERE\n `revisionId` = :revisionId\n AND\n `name` != 'Columns'\n AND\n `isDeleted` = 0\n ORDER BY\n blockName, `position`\n ";
$params = array('revisionId' => $revisionId);
$widgetData = ipDb()->fetchColumn($sql, $params);
$fingerprint = implode('***|***', $widgetNames) . '|||' . implode('***|***', $widgetData);
return $fingerprint;
}
public static function sendUsageStatistics($data = array(), $timeout = 3)
{
if (!function_exists('curl_init')) {
return;
}
if (!isset($data['action'])) {
$data['action'] = 'Ping.default';
}
if (!isset($data['php'])) {
$data['php'] = phpversion();
}
if (!isset($data['db'])) {
$data['db'] = null;
// todo: make a db type/version check stable to work during install and later on
// if (class_exists('PDO')) {
// $pdo = ipDb()->getConnection();
// if ($pdo) {
// $data['db'] = $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION);
// }
// }
}
if (!isset($data['developmentEnvironment'])) {
$data['developmentEnvironment'] = ipConfig()->get('developmentEnvironment');
}
if (!isset($data['showErrors'])) {
$data['showErrors'] = ipConfig()->get('showErrors');
}
if (!isset($data['debugMode'])) {
$data['debugMode'] = ipConfig()->get('debugMode');
}
if (!isset($data['timezone'])) {
$data['timezone'] = ipConfig()->get('timezone');
}
if (!isset($data['data'])) {
$data['data'] = array();
}
if (!isset($data['websiteId'])) {
$data['websiteId'] = ipStorage()->get('Ip', 'websiteId');
}
if (!isset($data['websiteUrl'])) {
$data['websiteUrl'] = ipConfig()->baseUrl();
}
if (!isset($data['version'])) {
$data['version'] = \Ip\Application::getVersion();
}
if (!isset($data['locale'])) {
$data['locale'] = \Ip\ServiceLocator::translator()->getAdminLocale();
}
if (!isset($data['doSupport'])) {
$data['doSupport'] = ipStorage()->get('Ip', 'getImpressPagesSupport');
}
if (!isset($data['administrators'])) {
$administrators = \Ip\Internal\Administrators\Model::getAll();
$adminCollection = array();
foreach ($administrators as $admin) {
$permissions = \Ip\Internal\AdminPermissionsModel::getUserPermissions($admin['id']);
$adminCollection[] = array('id' => $admin['id'], 'email' => $admin['email'], 'permissions' => $permissions);
}
$data['administrators'] = $adminCollection;
}
if (!isset($data['themes'])) {
$data['themes'] = array('active' => ipConfig()->theme(), 'all' => \Ip\Internal\Design\Model::instance()->getAvailableThemes());
}
if (!isset($data['plugins'])) {
$plugins = \Ip\Internal\Plugins\Model::getAllPluginNames();
$activePlugins = \Ip\Internal\Plugins\Service::getActivePluginNames();
$pluginCollection = array();
foreach ($plugins as $pluginName) {
$pluginCollection[] = array('name' => $pluginName, 'active' => in_array($pluginName, $activePlugins) ? true : false);
}
$data['plugins'] = $pluginCollection;
}
if (!isset($data['languages'])) {
$data['languages'] = ipContent()->getLanguages();
}
if (!isset($data['pages'])) {
$result = array();
try {
$table = ipTable('page');
$sql = "\n SELECT\n `languageCode` AS `language`, COUNT( 1 ) AS `quantity`\n FROM\n {$table}\n GROUP BY\n `languageCode`\n ";
$result = ipDb()->fetchAll($sql);
} catch (\Exception $e) {
// Do nothing.
}
$data['pages'] = $result;
}
$postFields = 'data=' . urlencode(serialize($data));
// Use sockets instead of CURL
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, ipConfig()->get('usageStatisticsUrl', 'http://service.impresspages.org/stats'));
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postFields);
// curl_setopt($ch, CURLOPT_REFERER, ipConfig()->baseUrl());
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $timeout);
curl_setopt($ch, CURLOPT_TIMEOUT, $timeout);
curl_exec($ch);
}
private static function getRevisionId($pageId)
{
$revisionTable = ipTable('revision');
$sql = "\n SELECT * FROM {$revisionTable}\n WHERE\n `pageId` = ? AND\n `isPublished` = 1\n ORDER BY `createdAt` DESC, `revisionId` DESC\n ";
$revision = ipDb()->fetchRow($sql, array($pageId));
if ($revision) {
return $revision['revisionId'];
} else {
return false;
}
}
/**
* Update table records
*
* Execute query, updates values from associative array
* @param string $table
* @param array $update
* @param array|int $condition
* @return int count of rows updated
*/
public function update($table, $update, $condition)
{
if (empty($update)) {
return false;
}
$sql = 'UPDATE ' . ipTable($table) . ' SET ';
$params = array();
foreach ($update as $column => $value) {
$sql .= "`{$column}` = ? , ";
if (is_bool($value)) {
$value = $value ? 1 : 0;
}
$params[] = $value;
}
$sql = substr($sql, 0, -2);
$sql .= " WHERE ";
$sql .= $this->buildConditions($condition, $params);
return $this->execute($sql, $params);
}
/**
* Trash size
*
* @return int Number of trash pages.
*/
public static function trashSize()
{
$table = ipTable('page');
$sql = "SELECT COUNT(*)\n FROM {$table}\n WHERE `isDeleted` > 0";
return ipDb()->fetchValue($sql);
}
/**
* @param $sourceId
*/
private static function decrementUsageCounter($sourceId)
{
$ipTable = ipTable(self::DATA_TABLE_REPOSITORY);
$sql = "UPDATE {$ipTable} SET `usageCounter`=`usageCounter`-1 WHERE `sourceId` = '{$sourceId}';";
try {
ipDb()->execute($sql);
} catch (\Ip\Exception\Db $e) {
ipLog()->error("Could not decrement table usage counter. Statement: {$sql}, Message: " . $e->getMessage());
}
}
public static function update_2()
{
$table = ipTable('widget');
$sql = "ALTER TABLE {$table} CHANGE `layout` `skin` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL";
ipDb()->execute($sql);
}
/**
* Delete global value
*
* @param string $key
*/
public function deleteGlobalValue($key)
{
$dbh = ipDb()->getConnection();
$sql = '
DELETE FROM
' . ipTable('inline_value_global') . '
WHERE
`plugin` = :module
AND `key` = :key
';
$params = array(':module' => $this->module, ':key' => $key);
$q = $dbh->prepare($sql);
$q->execute($params);
}
public static function deleteOldLogs($days)
{
$logTable = ipTable('log');
ipDb()->execute("delete from {$logTable} where (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(`time`)) > ?", array($days * 24 * 60 * 60));
}
public static function getActivePluginNames()
{
if (\Ip\Internal\Admin\Service::isSafeMode()) {
return array();
}
$dbh = ipDb()->getConnection();
$sql = '
SELECT
`name`
FROM
' . ipTable('plugin') . '
WHERE
`isActive` = 1
';
$params = array();
$q = $dbh->prepare($sql);
$q->execute($params);
$data = $q->fetchAll(\PDO::FETCH_COLUMN);
//fetch all rows as an array
return $data;
}
public function activate()
{
$table = ipTable(\Plugin\Keywordlist\Model::TABLE_NAME);
$sql = "\n CREATE TABLE IF NOT EXISTS\n {$table}\n (\n `pageId` int(11),\n `keywords` mediumtext,\n `description` mediumtext,\n PRIMARY KEY(`pageId`)\n )";
ipDb()->execute($sql);
}
public function languageTableName()
{
$tableName = $this->rawTableName() . '_language';
if (!empty($this->config['languageTable'])) {
$tableName = $this->config['languageTable'];
}
return ipTable(str_replace("`", "", $tableName));
}
protected function updateDb($table, $update, $id, $languageCode = null)
{
if (empty($update)) {
return false;
}
$db = new Db($this->subgridConfig, $this->statusVariables);
$db->setDefaultLanguageCode($languageCode);
$sql = "UPDATE " . ipTable($table) . " " . $db->joinQuery() . " SET ";
$params = array();
foreach ($update as $column => $value) {
if ($column == $this->subgridConfig->idField()) {
continue;
//don't update id field
}
$sql .= "`{$column}` = ? , ";
if (is_bool($value)) {
$value = $value ? 1 : 0;
}
$params[] = $value;
}
$sql = substr($sql, 0, -2);
$sql .= " WHERE ";
$sql .= " " . ipTable($table) . ".`" . $this->subgridConfig->idField() . "` = ? ";
$params[] = $id;
$result = ipDb()->execute($sql, $params);
return $result;
}
public function remove()
{
$sql = 'DROP TABLE IF EXISTS ' . ipTable('comments');
ipDb()->execute($sql);
}
/**
* Remove all storage values for the plugin
*
* @param string $pluginName Plugin name
*/
public function removeAll($pluginName)
{
$sql = '
DELETE FROM
' . ipTable('storage') . '
WHERE
`plugin` = :plugin
';
$params = array(':plugin' => $pluginName);
ipDb()->execute($sql, $params);
}
