TYPE_STR, # Заголовок 'textshort' => TYPE_STR, # Краткое описание 'mtitle' => TYPE_NOTAGS, # Meta Title 'mkeywords' => TYPE_NOTAGS, # Meta Keywords 'mdescription' => TYPE_NOTAGS, # Meta Description 'content_search' => TYPE_STR, # Publicator-поиск ]; /** @var NestedSetsTree для категорий */ protected $treeCategories; /** @var array */ public $langCategories = [ 'title' => TYPE_STR, # Название 'mtitle' => TYPE_NOTAGS, # Meta Title 'mkeywords' => TYPE_NOTAGS, # Meta Keywords 'mdescription' => TYPE_NOTAGS, # Meta Description ]; /** * Компонент для работы с деревом категорий * @return NestedSetsTree */ public function treeCategories() { if ($this->treeCategories === null) { $this->treeCategories = new NestedSetsTree(static::TABLE_CATEGORIES); } return $this->treeCategories; } /** * ID "Корневой категории" (изменять не рекомендуется) * @return int */ public function categoryRootID() { return 1; } # -------------------------------------------------------------------- # Вопросы /** * Список вопросов (admin) * @param array $filter фильтр списка вопросов * @param bool $countOnly только подсчет кол-ва вопросов * @param string $limit * @param string $orderBy * @param string|null $lang * @return mixed */ public function questionsListing(array $filter, bool $countOnly = false, string $limit = '', string $orderBy = '', ?string $lang = null) { $lang = $lang ?? $this->locale->current(); if ($countOnly) { $filter = $this->prepareFilter($filter, 'Q'); $this->db->tag('help-questions-listing-count', ['filter' => &$filter]); return $this->db->one_data( 'SELECT COUNT(Q.id) FROM ' . static::TABLE_QUESTIONS . ' Q ' . $filter['where'], $filter['bind'] ); } $join = ' LEFT JOIN ' . static::TABLE_CATEGORIES_LANG . ' C ON C.id = Q.cat_id1 AND C.lang = :lang'; $filter[':lang'] = $this->db->langAnd(false, 'Q', 'QL', $lang); $filter = $this->prepareFilter($filter, 'Q', [':lang' => $this->locale->getCurrentLanguage()]); $this->db->tag('help-questions-listing-data', ['filter' => &$filter, 'join' => &$join]); return $this->db->select( 'SELECT Q.id, Q.link, Q.created, QL.title, Q.enabled, Q.content_no, Q.fav, Q.cat_id, C.title as cat_title FROM ' . static::TABLE_QUESTIONS . ' Q ' . $join . ', ' . static::TABLE_QUESTIONS_LANG . ' QL ' . $filter['where'] . ' GROUP BY Q.id ' . (!empty($orderBy) ? ' ORDER BY ' . $orderBy : '') . $limit, $filter['bind'] ); } /** * Поиск вопросов (frontend) * @param string $query фильтр списка вопросов * @param bool $countOnly только подсчет кол-ва вопросов * @param string $limit * @param string|null $lang * @return array|int */ public function questionsSearch(string $query, bool $countOnly = false, string $limit = '', ?string $lang = null) { $lang = $lang ?? $this->locale->current(); $filter = ['enabled' => 1]; $filter[':query'] = $this->db->prepareFulltextQuery( $query, 'QL.title, QL.textshort, QL.content_search' ); $filter[':lang'] = $this->db->langAnd(false, 'Q', 'QL', $lang); $filter = $this->prepareFilter($filter, 'Q'); if ($countOnly) { $this->db->tag('help-questions-search-count', ['filter' => &$filter]); return $this->db->one_data( 'SELECT COUNT(Q.id) FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL ' . $filter['where'], $filter['bind'] ); } $this->db->tag('help-questions-search-data', ['filter' => &$filter]); $data = $this->db->select( 'SELECT QL.title, Q.link, QL.textshort, Q.content_no FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL ' . $filter['where'] . ' ORDER BY QL.title ' . $limit, $filter['bind'] ); if (empty($data)) { $data = []; } return $data; } /** * Список частых вопросов (frontend) * @param array $opts * @return mixed */ public function questionsFav(array $opts = []) { $opts = $this->defaults($opts, [ 'limit' => 0, 'lang' => $this->locale->current(), ]); $filter = ['enabled' => 1, 'fav>0']; $filter[':lang'] = $this->db->langAnd(false, 'Q', 'QL', $opts['lang']); $filter = $this->prepareFilter($filter, 'Q'); $limit = ($opts['limit'] > 0 ? $this->db->prepareLimit(0, $opts['limit']) : ' '); $this->db->tag('help-questions-fav-data', ['filter' => &$filter]); return $this->db->select( 'SELECT Q.link, QL.title, Q.content_no FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL ' . $filter['where'] . ' ORDER BY fav' . $limit, $filter['bind'] ); } /** * Получение данных вопроса * @param int $questionID ID вопроса * @return array */ public function questionData(int $questionID) { $data = $this->db->select_row(static::TABLE_QUESTIONS, ['*'], ['id' => $questionID]); if (!empty($data)) { $this->db->langSelect($questionID, $data, $this->langQuestions, static::TABLE_QUESTIONS_LANG); } else { $data = []; } return $data; } /** * Просмотр вопроса (frontend) * @param int $questionID ID вопроса * @param string|null $lang * @return array */ public function questionView(int $questionID, ?string $lang = null) { $lang = $lang ?? $this->locale->current(); $this->db->tag('help-question-view-data'); $data = $this->db->one_array( 'SELECT Q.id, Q.cat_id, Q.link, QL.title, Q.content, QL.textshort, QL.mtitle, QL.mkeywords, QL.mdescription, Q.mtemplate, Q.content_no FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL WHERE Q.id = :id AND Q.enabled = 1 ' . $this->db->langAnd(true, 'Q', 'QL', $lang), [':id' => $questionID] ); if (empty($data)) { return []; } # другие вопросы $this->db->tag('help-question-view-other'); $data['questions_other'] = $this->db->select( 'SELECT Q.link, QL.title, Q.content_no FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL WHERE Q.cat_id = :cat AND Q.id != :id AND Q.enabled = 1 ' . $this->db->langAnd(true, 'Q', 'QL', $lang), [':cat' => $data['cat_id'], ':id' => $questionID] ); # хлебные крошки $this->db->tag('help-question-view-crumbs'); $data['crumbs'] = $this->db->select( 'SELECT C.keyword, CL.title FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL WHERE ' . $this->db->prepareIN('C.id', $this->categoryParentsID($data['cat_id'])) . $this->db->langAnd(true, 'C', 'CL', $lang) ); return $data; } /** * Сохранение вопроса * @param int $questionID ID вопроса * @param array $data данные вопроса * @return bool|int */ public function questionSave(int $questionID, array $data) { if (empty($data)) { return false; } $data['modified'] = $this->db->now(); if ($questionID > 0) { $res = $this->db->update(static::TABLE_QUESTIONS, array_diff_key($data, $this->langQuestions), ['id' => $questionID]); $this->db->langUpdate($questionID, $data, $this->langQuestions, static::TABLE_QUESTIONS_LANG); return !empty($res); } else { $data['created'] = $data['modified']; $data['user_id'] = $data['user_id'] ?? User::id(); $data['num'] = (int)$this->db->one_data( 'SELECT MAX(num) FROM ' . static::TABLE_QUESTIONS . ' WHERE cat_id = :cat', [':cat' => $data['cat_id']] ); $data['num'] += 1; $questionID = $this->db->insert(static::TABLE_QUESTIONS, array_diff_key($data, $this->langQuestions)); if ($questionID > 0) { $this->db->langInsert($questionID, $data, $this->langQuestions, static::TABLE_QUESTIONS_LANG); } return $questionID; } } /** * Переключатели параметров вопроса * @param int $questionID ID вопроса * @param string $field переключаемое поле * @return mixed|void */ public function questionToggle(int $questionID, string $field) { switch ($field) { case 'enabled': return $this->toggleInt(static::TABLE_QUESTIONS, $questionID, $field); case 'fav': return $this->toggleInt(static::TABLE_QUESTIONS, $questionID, $field, 'id', true); } } /** * Перемещение вопроса * @param string $orderField поле, по которому производится сортировка * @param string $where дополнительные условия * @return bool */ public function questionsRotate(string $orderField, string $where = '') { if (! empty($where)) { $where = ' AND ' . (is_array($where) ? join(' AND ', $where) : $where); } return $this->db->rotateTablednd(static::TABLE_QUESTIONS, $where, 'id', $orderField); } /** * Перемещение вопросов из одной категории ($catOldID) в другую ($catNewID) * @param int $catNewID ID категории, в которую перемещаем вопросы * @param int $catOldID ID категории, из которой перемещаем вопросы * @return bool */ public function questionsMoveToCategory(int $catNewID, int $catOldID) { if (empty($catNewID) || empty($catOldID)) { return false; } $newData = $this->categoryData($catNewID); if (empty($newData)) { return false; } $update = ['cat_id' => $catNewID]; $parentsID = $this->categoryParentsID($catNewID); if (! empty($parentsID)) { foreach ($parentsID as $lvl => $id) { $update['cat_id' . $lvl] = $id; } } return $this->db->update(static::TABLE_QUESTIONS, $update, ['cat_id' => $catOldID]); } /** * Перестраиваем URL на страницы всех вопросов * @param string $lang ключ языка * @return void */ public function questionsUrlRefresh(string $lang) { $this->db->select_iterator( 'SELECT Q.id, QL.title FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL WHERE Q.id = QL.id AND QL.lang = :lang ORDER BY Q.id', [':lang' => $lang], function ($question) { $url = $this->controller->url('view', $question); $this->db->update(static::TABLE_QUESTIONS, ['link' => $url], ['id' => $question['id']]); } ); } /** * Удаление вопроса * @param int $questionID ID вопроса * @return bool */ public function questionDelete(int $questionID) { if (empty($questionID)) { return false; } $res = $this->db->delete(static::TABLE_QUESTIONS, ['id' => $questionID]); if (! empty($res)) { $this->db->delete(static::TABLE_QUESTIONS_LANG, ['id' => $questionID]); return true; } return false; } # -------------------------------------------------------------------- # Категории /** * Список категорий (admin) * @param array $filter фильтр списка категорий * @param bool $countOnly только подсчет кол-ва категорий * @param string $limit * @param string $orderBy * @param string|null $lang * @return mixed */ public function categoriesListing(array $filter, bool $countOnly = false, string $limit = '', string $orderBy = '', ?string $lang = null) { $lang = $lang ?? $this->locale->current(); $filter[':lang'] = $this->db->langAnd(false, 'C', 'CL', $lang); $filter[] = 'pid != 0'; $filter = $this->prepareFilter($filter, 'C'); if ($countOnly) { $this->db->tag('help-categories-listing-count', ['filter' => &$filter]); return $this->db->one_data( 'SELECT COUNT(C.id) FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL ' . $filter['where'], $filter['bind'] ); } $this->db->tag('help-categories-listing-data', ['filter' => &$filter]); return $this->db->select( 'SELECT C.id, C.created, CL.title, C.enabled, C.pid, C.numlevel, ((C.numright-C.numleft)-1) as subs FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL ' . $filter['where'] . (!empty($orderBy) ? ' ORDER BY ' . $orderBy : '') . $limit, $filter['bind'] ); } /** * Список категорий (frontend) * @param array $opts * @return mixed */ public function categoriesListIndex(array $opts = []) { $opts = $this->defaults($opts, [ 'lang' => $this->locale->current(), ]); $filter = [ 'pid != 0', 'enabled' => 1, ':lang' => $this->db->langAnd(false, 'C', 'CL', $opts['lang']), ]; $filter = $this->prepareFilter($filter, 'C'); $this->db->tag('help-categories-list-index-data', ['filter' => &$filter]); $data = $this->db->select_key( 'SELECT C.id, CL.title, C.keyword, C.pid, COUNT(Q.id) as questions FROM ' . static::TABLE_CATEGORIES . ' C LEFT JOIN ' . static::TABLE_QUESTIONS . ' Q ON Q.cat_id = C.id AND Q.enabled = 1 , ' . static::TABLE_CATEGORIES_LANG . ' CL ' . $filter['where'] . ' GROUP BY C.id ORDER BY C.numleft', 'id', $filter['bind'] ); if (! empty($data)) { $categoryID = []; foreach ($data as $k => &$v) { if ($v['questions'] > 0) { $v['questions_list'] = []; $categoryID[] = $k; } } unset($v); $this->db->tag('help-categories-list-index-questions'); $questionsList = $this->db->select( 'SELECT Q.cat_id, Q.link, QL.title, Q.content_no FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL WHERE Q.enabled = 1 AND ' . $this->db->prepareIN('Q.cat_id', $categoryID) . ' AND ' . $this->db->langAnd(false, 'Q', 'QL', $opts['lang']) . ' ORDER BY Q.num' ); if (! empty($questionsList)) { foreach ($questionsList as $v) { $data[$v['cat_id']]['questions_list'][] = $v; } } unset($questionsList); $data = $this->db->transformRowsToTree($data, 'id', 'pid', 'subcats'); } return $data; } /** * Получение данных категории * @param int $categoryID ID категории * @param bool $edit при редактировании * @return array */ public function categoryData(int $categoryID, bool $edit = false) { if ($edit) { $data = $this->db->one_array( 'SELECT C.*, ((C.numright-C.numleft)-1) as subs, COUNT(Q.id) as questions FROM ' . static::TABLE_CATEGORIES . ' C LEFT JOIN ' . static::TABLE_QUESTIONS . ' Q ON Q.cat_id = C.id WHERE C.id = :id GROUP BY C.id', [':id' => $categoryID] ); if (! empty($data)) { $this->db->langSelect($categoryID, $data, $this->langCategories, static::TABLE_CATEGORIES_LANG); } } else { $data = $this->db->one_array( 'SELECT C.*, ((C.numright-C.numleft)-1) as subs FROM ' . static::TABLE_CATEGORIES . ' C WHERE C.id = :id', [':id' => $categoryID] ); } if (empty($data)) { $data = []; } return $data; } /** * Просмотр категории + вопросов (или подкатегорий) * @param string $keyword url keyword категории * @param array $opts * @return array|bool */ public function categoryView(string $keyword, array $opts = []) { $opts = $this->defaults($opts, [ 'lang' => $this->locale->current(), ]); $this->db->tag('help-category-view-data'); $data = $this->db->one_array( 'SELECT C.id, C.pid, C.keyword, CL.title, CL.mtitle, CL.mkeywords, CL.mdescription, C.mtemplate, ((C.numright-C.numleft)-1) as subcats FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL WHERE C.keyword = :key AND C.enabled = 1 AND ' . $this->db->langAnd(false, 'C', 'CL', $opts['lang']) . ' ', [':key' => $keyword] ); if (! empty($data)) { $categoryID = $data['id']; # хлебные крошки $this->db->tag('help-category-view-crumbs'); $data['crumbs'] = $this->db->select( 'SELECT C.keyword, CL.title FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL WHERE ' . $this->db->prepareIN('C.id', $this->categoryParentsID($categoryID)) . $this->db->langAnd(true, 'C', 'CL', $opts['lang']) ); if (!$data['subcats']) { # вопросы $this->db->tag('help-category-view-questions'); $data['questions_list'] = $this->db->select( 'SELECT Q.link, QL.title, QL.textshort, Q.content_no FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL WHERE Q.enabled = 1 AND Q.cat_id = :cat AND ' . $this->db->langAnd(false, 'Q', 'QL', $opts['lang']) . ' ORDER BY Q.num', [':cat' => $categoryID] ); } else { # подкатегории $this->db->tag('help-category-view-subcats'); $data['subcats_list'] = $this->db->select( 'SELECT C.keyword, CL.title FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL WHERE C.enabled = 1 AND C.pid = :cat AND ' . $this->db->langAnd(false, 'C', 'CL', $opts['lang']) . ' ORDER BY C.numleft', [':cat' => $categoryID] ); } } return $data; } /** * Получение данных категории * @param string $keyword URL keyword * @param int $categoryID ID категории * @param int $categoryParentID ID parent-категории * @return bool */ public function categoryKeywordExists(string $keyword, int $categoryID, int $categoryParentID) { $filter = $this->prepareFilter([ 'pid' => $categoryParentID, 'keyword_edit' => $keyword, ['id != :id', ':id' => $categoryID] ]); $data = $this->db->one_array( 'SELECT id FROM ' . static::TABLE_CATEGORIES . ' ' . $filter['where'] . ' LIMIT 1', $filter['bind'] ); return !empty($data); } /** * Сохранение категории * @param int $categoryID ID категории * @param array $data данные категории * @return bool|int */ public function categorySave(int $categoryID, array $data) { if (empty($data)) { return false; } $data['modified'] = $this->db->now(); if ($categoryID > 0) { if (isset($data['pid'])) { unset($data['pid']); # запрет изменения pid } $res = $this->db->update(static::TABLE_CATEGORIES, array_diff_key($data, $this->langCategories), [ 'id' => $categoryID, ]); $this->db->langUpdate($categoryID, $data, $this->langCategories, static::TABLE_CATEGORIES_LANG); return !empty($res); } else { $data['created'] = $data['modified']; $categoryID = $this->treeCategories()->insertNode($data['pid']); if ($categoryID > 0) { unset($data['pid']); $this->db->update(static::TABLE_CATEGORIES, array_diff_key($data, $this->langCategories), [ 'id' => $categoryID, ]); $this->db->langInsert($categoryID, $data, $this->langCategories, static::TABLE_CATEGORIES_LANG); } return $categoryID; } } /** * Переключатели параметров категории * @param int $categoryID ID категории * @param string $field переключаемое поле * @return mixed|void */ public function categoryToggle(int $categoryID, string $field) { switch ($field) { case 'enabled': return $this->toggleInt(static::TABLE_CATEGORIES, $categoryID, $field); } } /** * Перемещение категории * @return array|bool */ public function categoriesRotate() { return $this->treeCategories()->rotateTablednd(); } /** * Удаление категории * @param int $categoryID ID категории * @return bool */ public function categoryDelete(int $categoryID): bool { if (empty($categoryID)) { return false; } $subCnt = $this->categorySubCount($categoryID); if (! empty($subCnt)) { $this->errors->set(Help::langAdmin('Unable to delete category if there are subcategories')); return false; } $itemsCnt = $this->db->one_data('SELECT COUNT(I.id) FROM ' . static::TABLE_QUESTIONS . ' I WHERE I.cat_id = :id', [ ':id' => $categoryID, ]); if (! empty($itemsCnt)) { $this->errors->set(Help::langAdmin('Unable to delete category when there are nested items')); return false; } $deletedID = $this->treeCategories()->deleteNode($categoryID); if (! empty($deletedID)) { $this->db->delete(static::TABLE_CATEGORIES_LANG, ['id' => $categoryID]); return true; } return false; } /** * Удаление всех категорий * @return bool */ public function categoriesDeleteAll(): bool { # чистим таблицу категорий (+ зависимости по внешним ключам) $this->db->exec('DELETE FROM ' . static::TABLE_CATEGORIES . ' WHERE id > 0'); $this->db->exec('TRUNCATE TABLE ' . static::TABLE_CATEGORIES_LANG); $this->db->exec('ALTER TABLE ' . static::TABLE_CATEGORIES . ' AUTO_INCREMENT = 2'); $this->db->update(static::TABLE_QUESTIONS, ['cat_id' => 0, 'cat_id1' => 0, 'cat_id2' => 0]); # создаем корневую директорию $rootID = $this->categoryRootID(); $rootTitle = Help::langAdmin('Root Section'); $data = [ 'id' => $rootID, 'pid' => 0, 'numleft' => 1, 'numright' => 2, 'numlevel' => 0, 'keyword' => 'root', 'enabled' => 1, 'created' => $this->db->now(), 'modified' => $this->db->now(), ]; $res = $this->db->insert(static::TABLE_CATEGORIES, $data); if (! empty($res)) { $dataLang = ['title' => []]; foreach ($this->locale->getLanguages() as $lng) { $dataLang['title'][$lng] = $rootTitle; } $this->db->langInsert($rootID, $dataLang, $this->langCategories, static::TABLE_CATEGORIES_LANG); } return !empty($res); } /** * Получаем кол-во вложенных категорий * @param int $categoryID * @return int */ public function categorySubCount(int $categoryID): int { return $this->treeCategories()->getChildrenCount($categoryID); } /** * Формирование списка подкатегорий * @param int $categoryID ID категории * @param mixed $options формировать select-options или FALSE * @param string|null $lang * @return array|string */ public function categorySubOptions(int $categoryID, $options = false, ?string $lang = null) { $lang = $lang ?? $this->locale->current(); $data = $this->db->select( 'SELECT C.id, CL.title FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL WHERE C.pid = :pid ' . $this->db->langAnd(true, 'C', 'CL', $lang) . ' ORDER BY C.numleft', [':pid' => $categoryID] ); if (empty($options)) { return $data; } return HTML::selectOptions($data, $options['sel'], $options['empty'], 'id', 'title'); } /** * Обработка редактирования keyword'a в категории с подменой его в путях подкатегорий * @param int $categoryID ID категории * @param string $keywordBefore предыдущий keyword * @return bool */ public function categoryRebuildSubsKeyword(int $categoryID, string $keywordBefore) { $catData = $this->categoryData($categoryID); if (empty($catData)) { return false; } if ($catData['pid'] == $this->categoryRootID()) { $from = $keywordBefore . '/'; } else { $parentCatData = $this->categoryData($catData['pid']); if (empty($parentCatData)) { return false; } $from = $parentCatData['keyword'] . '/' . $keywordBefore . '/'; } # перестраиваем полный путь подкатегорий $res = $this->db->update( static::TABLE_CATEGORIES, ['keyword = REPLACE(keyword, :from, :to)'], 'numleft > :left AND numright < :right', [ ':from' => $from, ':to' => $catData['keyword'] . '/', ':left' => $catData['numleft'], ':right' => $catData['numright'], ] ); return !empty($res); } /** * Формирование списка основных категорий * @param int $selectedID ID выбранной категории * @param mixed $emptyOption невыбранное значение * @param int $type тип списка: 0 - все(кроме корневого), 1 - список при добавлении категории, 2 - список при добавлении записи * @param array $opts доп. параметры * @return string ... */ public function categoriesOptions(int $selectedID = 0, $emptyOption = false, int $type = 0, array $opts = []) { $opts = $this->defaults($opts, [ 'onlyID' => [], # только список определенных категорий 'lang' => $this->locale->current(), ]); $filter = []; if ($type == 1) { $filter[] = 'numlevel < 2'; } else { $filter[] = 'numlevel > 0'; } if (!empty($opts['onlyID'])) { $filter[':only'] = '(C.id IN (' . join(',', $opts['onlyID']) . ') OR C.pid IN(' . join(',', $opts['onlyID']) . '))'; } # Chrome не понимает style="padding" в option $usePadding = (mb_stripos(Request::userAgent(), 'chrome') === false); $joinItems = ($type > 0); $filter[':lang'] = $this->db->langAnd(false, 'C', 'CL', $opts['lang']); $filter = $this->prepareFilter($filter, 'C'); $catsList = $this->db->select( 'SELECT C.id, CL.title, C.numlevel, ((C.numright-C.numleft)-1) as subs ' . ($joinItems ? ', COUNT(I.id) as items ' : '') . ' FROM ' . static::TABLE_CATEGORIES . ' C ' . ($joinItems ? ' LEFT JOIN ' . static::TABLE_QUESTIONS . ' I ON C.id = I.cat_id ' : '') . ' , ' . static::TABLE_CATEGORIES_LANG . ' CL ' . $filter['where'] . ' GROUP BY C.id ORDER BY C.numleft', $filter['bind'] ); $options = ''; foreach ($catsList as &$v) { if ($v['id'] == $this->categoryRootID()) { $v['title'] = Help::langAdmin('Root Section'); } $numLevel = & $v['numlevel']; $disable = ($type > 0 && ($type == 2 ? $v['subs'] > 0 : ($numLevel > 0 && $v['items'] > 0))); $options .= ''; } unset($v); if ($emptyOption !== false) { $value = 0; if (is_array($emptyOption)) { $value = key($emptyOption); $emptyOption = current($emptyOption); } $emptyOption = ''; $options = $emptyOption . $options; } return $options; } /** * Формирование списков категорий (при добавлении/редактировании записи) * @param array $categoriesID ID категорий [lvl=>selectedID, ...] * @param mixed $options формировать select-options или нет (false) * @param string|null $lang * @return array [lvl=>[a=>selectedID, categories=>список категорий(массив или options)],...] */ public function categoriesOptionsByLevel(array $categoriesID, $options = false, ?string $lang = null) { if (empty($categoriesID)) { return []; } $lang = $lang ?? $this->locale->current(); # формируем список требуемых уровней категорий $levels = []; $fill = true; $parentID = $this->categoryRootID(); foreach ($categoriesID as $lvl => $categoryID) { if ($categoryID || $fill) { $levels[$lvl] = $parentID; if (!$categoryID) { break; } $parentID = $categoryID; } else { break; } } if (empty($levels)) { return []; } $data = $this->db->select( 'SELECT C.id, CL.title, C.numlevel as lvl FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL WHERE C.numlevel IN (' . join(',', array_keys($levels)) . ') AND C.pid IN(' . join(',', $levels) . ') ' . $this->db->langAnd(true, 'C', 'CL', $lang) . ' ORDER BY C.numleft' ); if (empty($data)) { return []; } $levels = []; foreach ($data as $v) { $levels[$v['lvl']][$v['id']] = $v; } unset($data); foreach ($categoriesID as $lvl => $selectedID) { if (isset($levels[$lvl])) { $categoriesID[$lvl] = [ 'a' => $selectedID, 'categories' => (!empty($options) ? HTML::selectOptions($levels[$lvl], $selectedID, $options['empty'], 'id', 'title') : $levels[$lvl]), ]; } else { $categoriesID[$lvl] = [ 'a' => $selectedID, 'categories' => false, ]; } } return $categoriesID; } /** * Получаем данные parent-категорий * @param int $categoryID ID категории * @param bool $includingSelf включать текущую в итоговых список * @param bool $exludeRoot исключить корневой раздел * @return array [lvl=>id, ...] */ public function categoryParentsID(int $categoryID, bool $includingSelf = true, bool $exludeRoot = true) { if (empty($categoryID)) { return [1 => 0]; } $parentsData = $this->treeCategories()->getNodeParentsID( $categoryID, ($exludeRoot ? ' AND numlevel > 0' : ''), $includingSelf, ['id','numlevel'] ); $parentsID = []; if (! empty($parentsData)) { foreach ($parentsData as $v) { $parentsID[$v['numlevel']] = $v['id']; } } return $parentsID; } /** * Получаем названия parent-категорий * @param int $categoryID ID категории * @param bool $includingSelf включать текущую в итоговых список * @param bool $exludeRoot исключить корневой раздел * @param mixed $separator объединить в одну строку или FALSE * @param string|null $lang * @return array|string [lvl=>id, ...] */ public function categoryParentsTitle(int $categoryID, bool $includingSelf = false, bool $exludeRoot = false, $separator = true, ?string $lang = null) { $lang = $lang ?? $this->locale->current(); $parentsID = $this->treeCategories() ->getNodeParentsID($categoryID, ($exludeRoot ? ' AND numlevel > 0' : ''), $includingSelf); if (empty($parentsID)) { return ($separator !== false ? '' : []); } $data = $this->db->select_one_column( 'SELECT CL.title FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL WHERE ' . $this->db->prepareIN('C.id', $parentsID) . $this->db->langAnd(true, 'C', 'CL', $lang) . ' ORDER BY C.numleft' ); if (empty($data)) { $data = []; } if ($separator !== false) { return join('  ' . ($separator === true ? '>' : $separator) . '  ', $data); } else { return $data; } } /** * Список таблиц модели с мультиязычными данными * @return array */ public function getLocaleTables(): array { return [ static::TABLE_QUESTIONS => ['type' => 'table', 'fields' => $this->langQuestions, 'title' => Help::lang('Questions')], static::TABLE_CATEGORIES => ['type' => 'table', 'fields' => $this->langCategories, 'title' => Help::lang('Categories')], ]; } }