public static function query($sql, $bUserFetchAssoc = true)
{
Trace::addMessage(__CLASS__, sprintf('Query:<br>%s', $sql));
$result = parent::query($sql, $bUserFetchAssoc);
Trace::addMessage(__CLASS__, sprintf('Query finished'));
return $result;
}
/**
* Генерирует файл сайтмапа
*/
public static function generate()
{
$register = new SystemRegister('System/Sitemap');
if ($register->get('sitemap.xml')->value == 0) {
return;
}
// Получаем все url сайта
$sql = 'SELECT * FROM `' . SITEMAP_TABLE . '` where `visible`="1"';
$aSitemap = DB::query($sql);
//
$xmlDocument = new DOMDocument('1.0', 'utf-8');
//
$urlset = $xmlDocument->createElement('urlset');
$urlset->setAttribute('xmlns', 'http://www.sitemaps.org/schemas/sitemap/0.9');
foreach ($aSitemap as $row) {
$url = $xmlDocument->createElement('url');
$loc = $xmlDocument->createElement('loc');
$lastmod = $xmlDocument->createElement('lastmod');
$changefreq = $xmlDocument->createElement('changefreq');
$priority = $xmlDocument->createElement('priority');
$loc->nodeValue = \Extasy\CMS::getWWWRoot() . substr($row['full_url'], 1);
$lastmod->nodeValue = $row['date_updated'];
$changefreq->nodeValue = $row['sitemap_xml_change'];
$priority->nodeValue = $row['sitemap_xml_priority'];
$url->appendChild($loc);
$url->appendChild($lastmod);
$url->appendChild($changefreq);
$url->appendChild($priority);
$urlset->appendChild($url);
}
$xmlDocument->appendChild($urlset);
// Пишем в папку xml
$xmlContents = $xmlDocument->saveXML();
file_put_contents(FILE_PATH . 'sitemap.xml', $xmlContents);
}
public static function checkLoginOrEmailExists($login, $email)
{
$sql = 'SELECT * FROM `%s` WHERE STRCMP(`login`,"%s") = 0 or (LENGTH("%s") > 0 and STRCMP(`email`,"%s") = 0) ';
$sql = sprintf($sql, USERS_TABLE, DB::escape($login), DB::escape($email), DB::escape($email));
$aResult = DB::query($sql);
if (!empty($aResult)) {
throw new Exception('Login or email already registered. Login - ' . $login . ' Email:' . $email);
}
}
/**
* Возвращает все записи из лога
*/
public static function selectAll()
{
$sql = 'select * from %s order by `id` desc';
$sql = sprintf($sql, self::TableName);
$data = DB::query($sql);
$result = array();
foreach ($data as $row) {
$result[] = new EmailLogModel($row);
}
return $result;
}
public function loadLinkedData()
{
$sql = <<<SQL
\t\tselect %s as valueId, linkedModel.* from %s as linkedModel
\t\tleft join %s as map
\t\ton linkedModel.id = map.%s and map.%s = "%d"
\t\torder by linkedModel.name asc
SQL;
$sql = sprintf($sql, $this->innerKey, call_user_func(array($this->linkedModel, 'getTableName')), $this->tableName, $this->foreighnKey, $this->innerKey, $this->document->id->getValue());
$data = DB::query($sql);
$result = array();
foreach ($data as $row) {
$model = new $this->linkedModel($row);
$result[] = array('checked' => !empty($row['valueId']), 'model' => $model->getParseData());
}
return $result;
}
/**
* Loads sitemap data for collected data
*/
public function loadSitemapData()
{
if (empty($this->data)) {
return;
}
//
$sqlFilter = array();
foreach ($this->data as $row) {
$sqlFilter[] = intval($row['id']);
}
//
$sql = 'SELECT * FROM `%s` WHERE `document_id` IN (%s) and STRCMP(`document_name`,"%s") = 0 and visible = 1';
$sql = sprintf($sql, SITEMAP_TABLE, implode($sqlFilter, ','), $this->modelName);
$tmp = DB::query($sql);
$this->sitemapInfoMap = array();
foreach ($tmp as $row) {
$this->sitemapInfoMap[$row['document_id']] = $row;
}
return $this;
}
/**
* Осуществляет сортировку по определенному полю у конкретного документа
*/
public static function sortByField($nId, $szDocumentName, $szFieldName, $bAsc = true)
{
//
$nId = IntegerHelper::toNatural($nId);
$szDocumentName = \Faid\DB::escape($szDocumentName);
$szFieldName = \Faid\DB::escape($szFieldName);
$bAsc = intval($bAsc);
//
// Получаем всех детей элемента, которые имеют указанный документ в кач. имени
//
$szTable = call_user_func([$szDocumentName, 'getTableName']);
$sql = <<<SQL
\tSELECT `sitemap`.id,`sitemap`.document_id
\tFROM `%s` as `sitemap`
\tINNER JOIN `%s` as `document`
\tON `sitemap`.`parent` = %d and `sitemap`.document_id = `document`.id and `sitemap`.document_name = "%s"
\tORDER BY `document`.%s %s
SQL;
$sql = sprintf($sql, SITEMAP_TABLE, $szTable, $nId, $szDocumentName, $szFieldName, $bAsc ? ' ASC' : ' DESC');
$aDocumentsData = DB::query($sql);
// Получаем всех остальных детей
$sql = 'SELECT * FROM `%s` WHERE `parent`="%d" and STRCMP(`document_name`,"%s") <> 0 ORDER by `order`';
$sql = sprintf($sql, SITEMAP_TABLE, $nId, $szDocumentName);
$aOtherData = DB::query($sql);
// Выстраиваем детей совмещаем массивы, неотсортированные элементы остаются на своих местах
$aResult = array();
$nOrder = 0;
while (sizeof($aDocumentsData) > 0 || sizeof($aOtherData) > 0) {
if (!empty($aOtherData) && $aOtherData[0]['order'] == $nOrder) {
$aElement = array_shift($aOtherData);
$aResult[] = $aElement['id'];
} else {
//
$aElement = array_shift($aDocumentsData);
$aResult[] = $aElement['id'];
}
$nOrder++;
}
//
Sitemap::manualOrder($nId, $aResult);
}
public function update($group, $permissions)
{
if (empty($group)) {
$this->AddAlert('Группа пользователей не выбрана');
$this->jump('./');
}
set_time_limit(0);
$page = 0;
$groupInfo = DBSimple::get(ACL_TABLE, ['name' => $group]);
if (empty($groupInfo)) {
throw new \InvalidArgumentException(sprintf('Group "%s" not found, failed to update group permissions', htmlspecialchars($group)));
}
$permissions = $this->filterPermissionsByGroup($group, $permissions);
do {
// получаем пользователей
$sql = <<<SQL
\tselect u.* from %s as u
\tinner join %s as r
\ton r.entity = concat("%s", u.id)
\twhere r.actionId = "%s"
\torder by id asc
\tlimit %d,%d
SQL;
$sql = sprintf($sql, \UserAccount::getTableName(), \ACL_GRANT_TABLE, \Faid\DB::escape(\UserAccount::ModelName), $groupInfo['id'], $page * self::Limit, self::Limit);
$data = DB::query($sql);
// устанавливаем каждому права
foreach ($data as $row) {
$user = new \UserAccount($row);
$tmp = $user->rights->getValue();
$tmp = array_merge($tmp, $permissions);
$user->rights = $tmp;
$user->update();
}
$page++;
} while (sizeof($data) != 0);
$this->addAlert('Права обновлены');
$this->jump('./');
}
<?php
use Faid\DB;
use Faid\DBSimple;
use Extasy\Audit\Record;
use Extasy\Audit\Log;
DB::post('TRUNCATE audit_logs');
DB::post('TRUNCATE audit_records');
//
$sql = 'select distinct category from cms_log order by category asc';
$data = DB::query($sql);
foreach ($data as $row) {
$log = createLog($row);
importMessages($log, $row['category']);
}
function createLog($row)
{
$log = new Log();
$log->name = 'Developer.' . $row['category'];
$log->enable_logging = true;
if (CMSLog::RuntimeErrors == $row['category']) {
$log->critical = true;
}
$log->insert();
return $log;
}
function importMessages($log, $category)
{
$data = selectMessages($category);
foreach ($data as $record) {
Record::add($log->name, $record['message'], $record['message']);
protected function action()
{
set_time_limit(0);
$sitemapIndex = new DOMDocument('1.0', 'utf-8');
$el = $sitemapIndex->createElement('sitemapindex');
$el->setAttribute('xmlns', "http://www.sitemaps.org/schemas/sitemap/0.9");
$sitemapIndex->appendChild($el);
$sitemapUrlCount = DB::getField('select count(*) as `count` from `sitemap`', 'count');
$counter = 0;
while ($counter < $sitemapUrlCount) {
$sql = 'select * from `sitemap` where `document_name` <> "statistic_page" and `document_name` <> "externallink" order by `id` asc limit %d,%d';
$sql = sprintf($sql, $counter, self::SitemapLimit);
$urlList = DB::query($sql);
// Создаем первый документ
$sitemap = $this->createSitemapDocument();
// Обрабатываем его
$this->processUrls($sitemapIndex, $sitemap, $urlList);
// СОхраняем результат
$this->storeSitemap($sitemapIndex, $sitemap);
$counter += self::SitemapLimit;
}
file_put_contents(FILE_PATH . 'sitemap.xml', $sitemapIndex->saveXML());
self::restart();
}
/**
* -------------------------------------------------------------------------------------------
* Загружает во внутренний кеш всю таблицу сайта
* @return
* -------------------------------------------------------------------------------------------
*/
public static function loadRows()
{
$sql = 'SELECT * FROM `%s` ORDER by `id` ASC ';
$sql = sprintF($sql, SITEMAP_TABLE);
self::$aItem = DB::query($sql);
}
public static function selectAllGrantsForEntity($entity)
{
$sql = <<<SQL
\t\tselect a.fullPath FROM `%s` AS g
\t\tINNER JOIN `%s` AS a
\t\tON a.id = g.actionId
\t\tWHERE g.entity = "%s"
SQL;
$sql = sprintf($sql, ACL_GRANT_TABLE, ACL_TABLE, \Faid\DB::escape($entity));
$data = DB::query($sql);
$result = array();
foreach ($data as $row) {
$result[$row['fullPath']] = true;
}
return $result;
}
/**
* -------------------------------------------------------------------------------------------
* Метод осуществляет поиск по карте сайте
* @param string $szKeyword строка поисковая ключа
* @return array
* -------------------------------------------------------------------------------------------
*/
public static function selectPaged($nParent, $nStart, $nLimit)
{
$nParent = intval($nParent);
$nStart = intval($nStart);
$nLimit = intval($nLimit);
//
$sql = 'SELECT SQL_CALC_FOUND_ROWS * FROM `%s` WHERE `parent`="%d" ORDER by `order` LIMIT %d,%d';
$sql = sprintf($sql, SITEMAP_TABLE, $nParent, $nStart, $nLimit);
$aResult = DB::query($sql);
$sql = 'SELECT FOUND_ROWS() as `totalcount`';
$aFound = DB::Get($sql);
self::$nItemCount = ceil($aFound['totalcount'] / $nLimit);
return $aResult;
}
protected static function selectDocumentScriptParents($documentName)
{
// Получаем скрипты, где может быть дочерним этот документ
$sql = 'SELECT * FROM `%s` WHERE `script` in (SELECT `path` FROM `%s` WHERE `document_name` = "%s")';
$sql = sprintf($sql, SITEMAP_TABLE, SITEMAP_SCRIPT_CHILD_TABLE, \Faid\DB::escape($documentName));
return DB::query($sql);
}
/**
* Проверяет что урл доступен
*/
protected static function checkUrlAvailable($sitemapId, $parentId, $urlKey)
{
$sitemapId = IntegerHelper::toNatural($sitemapId);
$parentId = IntegerHelper::toNatural($parentId);
$urlKey = \Faid\DB::escape($urlKey);
// Получаем все документы с указанным url_key и parent
$sql = 'select * from `%s` where parent = "%d" and `url_key`="%s" ';
$sql = sprintf($sql, SITEMAP_TABLE, $parentId, $urlKey);
$data = DB::query($sql);
// Если их 0, то всё ок
if (sizeof($data) == 0) {
return;
}
// Если их больше 1
if (sizeof($data) > 1) {
// бросаем исключение
$message = 'Sitemap tree conflict in parent_id=%d and url_key=%s ';
$message = sprintf($message, $parentId, $urlKey);
throw new SiteMapException($message);
}
// Если одна, то сравниваем индексы
$data = $data[0];
if ($data['id'] != $sitemapId) {
// не совпадают
// бросаем исключение
$message = 'Sitemap tree conflict for sitemap_id=%d in parent_id=%d and url_key=%s url already used by sitemap_id="%d"';
$message = sprintf($message, $sitemapId, $parentId, $urlKey, $data['id']);
throw new SiteMapException($message);
}
}
/**
* Отыскивает пользователя по части его логина
* @return
*/
public static function searchByLogin($szKey)
{
$szKey = trim($szKey);
if (strlen($szKey) < 2) {
throw new Exception('Search key too small');
}
$sql = 'SELECT id,login,email FROM `%s` WHERE `login` LIKE "%%%s%%" order by `login` ASC';
$sql = sprintf($sql, USERS_TABLE, to_search_string($szKey));
return DB::query($sql);
}
/**
* Возвращает список всех пользователей системы
*/
public static function selectAllUsers()
{
$sql = 'select * from `%s` WHERE `id` > 2 order by `login` ';
$sql = sprintf($sql, CMS_AUTH_TABLE);
return DB::query($sql);
}
protected function execQuery($sql, $bUseAssoc = true)
{
$aResult = DB::query($sql, $bUseAssoc);
return $aResult;
}
protected function loadControls()
{
// Загружаем контролы
$sql = 'select * from `%s` WHERE `schemaId`="%s" ORDER by `order` ASC';
$sql = sprintf($sql, CCONFIG_CONTROL_TABLE, $this->id);
$data = DB::query($sql);
// Конвертируем в объекты
$result = array();
foreach ($data as $row) {
$result[] = CConfigControlManager::createControl($row['id'], $row['name'], $row['xtype'], $row['title'], $row['config'], $row['value'], $this);
}
$this->controls = $result;
}
public static function select(SearchRequest $request)
{
selF::$lastSearchRequest = $request;
$request->validate();
$sql = <<<SQL
\tselect SQL_CALC_FOUND_ROWS a.*,l.name as event
\tfrom %s as a
\tinner join %s as l
\ton a.log_id = l.id
\twhere %s
\torder by %s %s
\tlimit %d,%d
SQL;
$sql = sprintf($sql, Record::tableName, Log::tableName, self::buildSQLSelectCondition(), $request->sort_by, $request->order, $request->page * $request->limit, $request->limit);
$data = DB::query($sql);
$result = array();
foreach ($data as $row) {
$result[] = array('record' => new Record($row), 'eventInfo' => array('event' => $row['event']));
}
return $result;
}
/**
* Возвращает все алиасы (пред версии урлов)
* @param int $id
*/
public static function selectById($id)
{
$id = IntegerHelper::toNatural($id);
$sql = 'select * from `%s` where `page_id`="%d" order by `id` DESC';
$sql = sprintf($sql, SITEMAP_HISTORY_TABLE, $id);
$dbResult = DB::query($sql);
return $dbResult;
}
public static function createCache()
{
self::clearCache();
self::disableCache();
// Получаем все ряды, выстроенные по предку
$sql = 'SELECT * FROM `%s` ORDER by `parent` ASC,`id` ASC';
$sql = sprintf($sql, SYSTEMREGISTER_TABLE);
$aData = DB::query($sql);
// Перебор всех рядов
$parent = -1;
$aChild = array();
foreach ($aData as $row) {
// Если предок изменился, то генерируем другой childCache
if ($row['parent'] != $parent) {
if (!empty($aChild)) {
self::storeChildCache($parent, $aChild);
$aChild = array();
}
$parent = $row['parent'];
$aChild[] = $row;
} else {
$aChild[] = $row;
}
// Добавляем в getCache
self::storeGetCache($row['name'], $parent, $row);
}
self::enableCache();
SimpleCache::set(SYSTEM_REGISTER_GET_CACHE, self::$aGetCache);
SimpleCache::set(SYSTEM_REGISTER_CHILD_CACHE, self::$aChildCache);
}
protected static function selectChilds($nSitemapId, $szAdditionalSQL, $orderCondition, $nStart, $nCount)
{
$nSitemapId = intval($nSitemapId);
$nCount = intval($nCount);
$nStart = intval($nStart);
$nStart = $nStart < 0 ? 0 : $nStart;
$sql = 'SELECT SQL_CALC_FOUND_ROWS * FROM `%s` WHERE `parent`="%d" %s ORDER by %s';
if (!empty($nCount)) {
$sql .= ' LIMIT %d,%d';
} else {
}
$orderCondition = empty($orderCondition) ? '`order` asc' : $orderCondition;
if (!defined('CMS')) {
$szAdditionalSQL .= 'and `visible`=1 ';
}
$sql = sprintf($sql, SITEMAP_TABLE, $nSitemapId, $szAdditionalSQL, $orderCondition, $nStart, $nCount);
$aItem = DB::query($sql);
//
if (!empty($nCount)) {
$aFound = DB::get('SELECT FOUND_ROWS() as `totalcount`');
self::$nTotalCount = $aFound['totalcount'];
}
return $aItem;
}
