/** * Gets article data for tag functionality * @param $tag string search query * @return array */ public function searchTags($tag) { $idw = FrontEndHelper::getWebIdFromTextId(\Yii::$app->request->get('web', \Yii::$app->params['defaultWeb'])); $idl = FrontEndHelper::getLanguageIdFromAcronym(); $sql = "( SELECT DISTINCT content.id AS article_id, content.title AS article_title, perex, content.updated_at AS article_updated_at, menu_item.id AS menu_item_id, menu_item.title AS menu_item_title\n\t\t\tFROM `content`\n\t\t\tINNER JOIN `article_category` ON article_category.article_id=content.id\n\t\t\tINNER JOIN `category` ON category.id=article_category.category_id\n\t\t\tINNER JOIN `menu_item_content` ON menu_item_content.category_id=category.id\n\t\t\tINNER JOIN `menu_item` ON menu_item.id=menu_item_content.menu_item_id\n\t\t\tINNER JOIN `menu` ON menu.id=menu_item.menu_id\n\t\t\tINNER JOIN `content_tag` ON content_tag.content_id=content.id\n\t\t\tINNER JOIN `tag` ON tag.id=content_tag.tag_id\n\t\t\tWHERE ((((((`content`.`content_type`=:content_type))\n\t\t\t\tAND (`content`.`active`=TRUE)) " . (Yii::$app->user->isGuest ? " AND (`content`.`public`=TRUE))" : '') . "\n\t\t\t\tAND (`content`.`language_id`=:language_id))\n\t\t\t\tAND (`menu`.`web_id`=:web_id))\n\t\t\t\tAND (`tag`.`name`=:tag))\n\t\t\tORDER BY `content`.`updated_at`"; $query = ContentRecord::findBySql($sql, [':content_type' => ContentRecord::TYPE_ARTICLE, ':language_id' => $idl, ':web_id' => $idw, ':tag' => $tag]); return $query->asArray()->all(); }
public function init() { Widget::init(); if ($this->categoryId) { $language_id = FrontEndHelper::getLanguageIdFromAcronym(); $web_id = FrontEndHelper::getWebIdFromTextId(\Yii::$app->request->get('web', \Yii::$app->params['defaultWeb'])); $this->orderBy = 'content.content_date DESC'; $this->withDate = true; $sql = "( SELECT DISTINCT `content`.*\n\t\t\t\tFROM `content`\n\t\t\t\tINNER JOIN `article_category` ON article_category.article_id=content.id\n\t\t\t\tINNER JOIN `category` ON (category.id=article_category.category_id AND category.id=:category_id)\n\t\t\t\tINNER JOIN `menu_item_content` ON menu_item_content.category_id=category.id\n\t\t\t\tINNER JOIN `menu_item` ON menu_item.id=menu_item_content.menu_item_id\n\t\t\t\tINNER JOIN `menu` ON menu.id=menu_item.menu_id\n\t\t\t\tWHERE (((((((`content`.`content_type`=:content_type))\n\t\t\t\t\tAND ((content.content_date<:actual_date) AND (IF(content.content_end_date IS NULL, content.content_end_date IS NULL, content.content_end_date<:actual_date))))\n\t\t\t\t\tAND (NOT (`content`.`id`" . ($this->articleId ? "=" . $this->articleId : " IS NULL") . ")))\n\t\t\t\t\tAND (`content`.`active`=TRUE)) " . (Yii::$app->user->isGuest ? " AND (`content`.`public`=TRUE))" : '') . "\n\t\t\t\t\tAND (`content`.`language_id`=:language_id))\n\t\t\t\t\tAND (`menu`.`web_id`=:web_id))\n\t\t\t\tORDER BY " . $this->orderBy . ($this->itemsCount ? " LIMIT " . $this->itemsCount : ""); $query = ContentRecord::findBySql($sql, [':category_id' => $this->categoryId ?: $this->categoryId, ':content_type' => ContentRecord::TYPE_ARTICLE, ':language_id' => $language_id, ':web_id' => $web_id, ':actual_date' => Yii::$app->formatter->asDate('now', 'y-MM-dd')]); $this->_items = $query->all(); $this->setMenuUrlParts(); } else { throw new InvalidParamException(\Yii::t('front', 'No required parameter given') . ' - categoryId'); } }
public function init() { parent::init(); if (!$this->invitationsCategoryId) { $this->invitationsCategoryId = Yii::$app->params[Yii::$app->language]['defaultInvitationsCategoryId']; } if (!$this->actualitiesCategoryId) { $this->actualitiesCategoryId = Yii::$app->params[Yii::$app->language]['defaultActualitiesCategoryId']; } $language_id = FrontEndHelper::getLanguageIdFromAcronym(); $web_id = FrontEndHelper::getWebIdFromTextId(\Yii::$app->request->get('web', \Yii::$app->params['defaultWeb'])); // Invitations $sql = "( SELECT DISTINCT `content`.*\n\t\t\t\tFROM `content`\n\t\t\t\tINNER JOIN `article_category` ON article_category.article_id=content.id\n\t\t\t\tINNER JOIN `category` ON (category.id=article_category.category_id AND category.id=:category_id)\n\t\t\t\tINNER JOIN `menu_item_content` ON menu_item_content.category_id=category.id\n\t\t\t\tINNER JOIN `menu_item` ON menu_item.id=menu_item_content.menu_item_id\n\t\t\t\tINNER JOIN `menu` ON menu.id=menu_item.menu_id\n\t\t\t\tWHERE ((((((`content`.`content_type`=:content_type))\n\t\t\t\t\tAND ((content.content_date>=:actual_date) OR (content.content_end_date>=:actual_date)))\n\t\t\t\t\tAND (`content`.`active`=TRUE)) " . (Yii::$app->user->isGuest ? " AND (`content`.`public`=TRUE))" : '') . "\n\t\t\t\t\tAND (`content`.`language_id`=:language_id))\n\t\t\t\t\tAND (`menu`.`web_id`=:web_id))\n\t\t\t\tORDER BY content.content_date, content.content_time ASC" . ($this->itemsCount ? " LIMIT " . $this->itemsCount : ""); $this->_invitationsItems = ContentRecord::findBySql($sql, [':category_id' => $this->invitationsCategoryId, ':content_type' => ContentRecord::TYPE_ARTICLE, ':language_id' => $language_id, ':web_id' => $web_id, ':actual_date' => Yii::$app->formatter->asDate('now', 'y-MM-dd')])->all(); $this->setMenuUrlParts('invitations'); $invitationsCount = count($this->_invitationsItems); if ($invitationsCount < $this->itemsCount) { $limit = $this->itemsCount - $invitationsCount; $sql = "( SELECT DISTINCT `content`.*\n\t\t\t\t\tFROM `content`\n\t\t\t\t\tINNER JOIN `article_category` ON article_category.article_id=content.id\n\t\t\t\t\tINNER JOIN `category` ON (category.id=article_category.category_id AND category.id=:category_id)\n\t\t\t\t\tINNER JOIN `menu_item_content` ON menu_item_content.category_id=category.id\n\t\t\t\t\tINNER JOIN `menu_item` ON menu_item.id=menu_item_content.menu_item_id\n\t\t\t\t\tINNER JOIN `menu` ON menu.id=menu_item.menu_id\n\t\t\t\t\tWHERE (((((((`content`.`content_type`=:content_type))\n\t\t\t\t\t\tAND (IF(`content`.`content_date` IS NULL, `content`.`content_date` IS NULL, `content`.`content_date`<=:actual_date)))\n AND (IF(`content`.`content_end_date` IS NULL, `content`.`content_end_date` IS NULL, `content`.`content_end_date`>=:actual_date)))\n AND (`content`.`active`=TRUE)) " . (Yii::$app->user->isGuest ? " AND (`content`.`public`=TRUE))" : '') . "\n\t\t\t\t\t\tAND (`content`.`language_id`=:language_id))\n\t\t\t\t\t\tAND (`menu`.`web_id`=:web_id))\n\t\t\t\t\tORDER BY content.updated_at DESC" . ($this->itemsCount ? " LIMIT " . $limit : ""); $this->_actualitiesItems = ContentRecord::findBySql($sql, [':category_id' => $this->actualitiesCategoryId, ':content_type' => ContentRecord::TYPE_ARTICLE, ':language_id' => $language_id, ':web_id' => $web_id, ':actual_date' => Yii::$app->formatter->asDate('now', 'y-MM-dd')])->all(); $this->setMenuUrlParts('actualities'); } }
public static function getFirstArticleIdFromCategoryId($categoryId) { //TODO opravit - není zcela košér - nebere v potaz více webů $sql = "( SELECT DISTINCT `content`.`id`\n\t\t\t\tFROM `content`\n\t\t\t\tINNER JOIN `article_category` ON article_category.article_id=content.id\n\t\t\t\tINNER JOIN `category` ON category.id=article_category.category_id\n\t\t\t\tWHERE (((((((`article_category`.`category_id`=:category_id))\n\t\t\t\t\tAND (`content`.`content_type`=:content_type)))\n\t\t\t\t\tAND (`content`.`active`=TRUE)) " . (Yii::$app->user->isGuest ? " AND (`content`.`public`=TRUE))" : '') . "\n\t\t\t\t\tAND (`content`.`language_id`=:language_id)))\n\t\t\t\tORDER BY content.updated_at DESC LIMIT 1"; return ContentRecord::findBySql($sql, [':category_id' => $categoryId, ':content_type' => ContentRecord::TYPE_ARTICLE, ':language_id' => FrontEndHelper::getLanguageIdFromAcronym()])->scalar(); }