278 lines
9.1 KiB
PHP
278 lines
9.1 KiB
PHP
<?php
|
|
|
|
namespace KupShop\ContentBundle\Util;
|
|
|
|
use KupShop\I18nBundle\Translations\ArticlesAuthorsTranslation;
|
|
use KupShop\I18nBundle\Translations\ArticlesSectionsTranslation;
|
|
use KupShop\I18nBundle\Translations\ArticlesTagsTranslation;
|
|
use KupShop\I18nBundle\Translations\ArticlesTranslation;
|
|
use KupShop\I18nBundle\Translations\PhotosTranslation;
|
|
use KupShop\KupShopBundle\Util\StringUtil;
|
|
use Query\Operator;
|
|
use Query\QueryBuilder;
|
|
use Query\Translation;
|
|
|
|
class ArticleList
|
|
{
|
|
protected $divide;
|
|
|
|
protected $total_count;
|
|
|
|
private $image;
|
|
|
|
protected $tags;
|
|
|
|
// returns section by defined spec
|
|
public function getSection($spec = null)
|
|
{
|
|
return $this->getSections($spec)[0] ?? false;
|
|
}
|
|
|
|
// return sections by specs
|
|
public function getSections($spec = null)
|
|
{
|
|
$qb = sqlQueryBuilder()->select('ab.*')
|
|
->from('articles_branches', 'ab')
|
|
->andWhere($spec)
|
|
->andWhere(Translation::coalesceTranslatedFields(ArticlesSectionsTranslation::class))
|
|
->orderBy('ab.position');
|
|
|
|
$qb->andWhere(Translation::joinTranslatedFields(ArticlesSectionsTranslation::class,
|
|
function (QueryBuilder $qb, $columnName, $translatedField) {
|
|
$qb->andWhere(Operator::coalesce($translatedField, 'ab.figure').' = "Y" ');
|
|
|
|
return false;
|
|
}, ['figure']));
|
|
|
|
return $qb->execute()->fetchAll();
|
|
}
|
|
|
|
public function getSectionById(int $id): ?array
|
|
{
|
|
if ($section = $this->getSection(Operator::equals(['ab.id' => $id]))) {
|
|
return $this->prepareArticleSection($section);
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public function getSectionsTree($topSection = null)
|
|
{
|
|
$tree = [];
|
|
|
|
$spec = function (QueryBuilder $qb) use ($topSection) {
|
|
$qb->andWhere(Operator::equalsNullable(['ab.top_branch' => $topSection]));
|
|
};
|
|
|
|
foreach ($this->getSections($spec) as $section) {
|
|
$tree[$section['id']] = $this->prepareArticleSection($section);
|
|
}
|
|
|
|
return $tree;
|
|
}
|
|
|
|
public function getArticlesCount($spec = null)
|
|
{
|
|
$qb = sqlQueryBuilder()->select('COUNT(DISTINCT a.id) as count')
|
|
->from('articles_relation', 'ar')
|
|
->leftJoin('ar', 'articles', 'a', 'ar.id_art=a.id')
|
|
->andWhere('a.date <= NOW()')
|
|
->andWhere($spec);
|
|
|
|
$qb->andWhere(Translation::joinTranslatedFields(ArticlesTranslation::class,
|
|
function (QueryBuilder $qb, $columnName, $translatedField) {
|
|
$qb->andWhere(Operator::coalesce($translatedField, 'a.figure').' = "Y" ');
|
|
}, ['figure']));
|
|
|
|
return $qb->execute()->fetch()['count'];
|
|
}
|
|
|
|
public function getArticles($spec = null)
|
|
{
|
|
$data = [];
|
|
$dbcfg = \Settings::getDefault();
|
|
|
|
$qb = sqlQueryBuilder()->select('SQL_CALC_FOUND_ROWS DISTINCT(a.id) as id, ar.id_branch, a.figure,
|
|
a.title, a.type, a.link, a.source, DATE_FORMAT(a.date, "'.$dbcfg->date_format.' '.$dbcfg->time_format.'") AS datef,
|
|
a.lead_in, a.date, a.data, a.id_block, a.url, a.seen, GROUP_CONCAT(t.id) AS tags')
|
|
->from('articles_relation', 'ar')
|
|
->leftJoin('ar', 'articles', 'a', 'ar.id_art=a.id')
|
|
->leftJoin('a', 'articles_tags_relation', 'atr', 'a.id = atr.id_article')
|
|
->leftJoin('atr', 'articles_tags', 't', 'atr.id_tag = t.id')
|
|
->leftJoin('ar', 'articles_branches', 'ab', 'ab.id = ar.id_branch')
|
|
->andWhere('a.date <= NOW()')
|
|
->having('figure = "Y"')
|
|
->groupBy('a.id')
|
|
->andWhere($spec)
|
|
->andWhere(Translation::coalesceTranslatedFields(ArticlesTranslation::class));
|
|
|
|
$qb->andWhere(Translation::joinTranslatedFields(ArticlesSectionsTranslation::class,
|
|
function (QueryBuilder $qb, $columnName, $translatedField) {
|
|
$qb->andWhere(Operator::coalesce($translatedField, 'ab.figure').' = "Y" ');
|
|
|
|
return false;
|
|
}, ['figure']));
|
|
|
|
$qb = $qb->execute();
|
|
|
|
$this->total_count = (int) sqlFetchAssoc(sqlQuery('SELECT FOUND_ROWS() as total_count'))['total_count'];
|
|
|
|
foreach ($qb as $row) {
|
|
$row['lead'] = $row['lead_in'];
|
|
$row['seo_url'] = !empty($row['url']) ? $row['url'] : StringUtil::slugify($row['title']);
|
|
|
|
// if isset section
|
|
if ($id_branch = $row['id_branch']) {
|
|
$spec = function (QueryBuilder $qb) use ($id_branch) {
|
|
$qb->andWhere(Operator::equals(['ab.id' => $id_branch]));
|
|
};
|
|
} else {
|
|
$spec = null;
|
|
}
|
|
$row['section'] = $this->getSection($spec);
|
|
|
|
$row['date'] = new \DateTime($row['date']);
|
|
|
|
$row['data'] = json_decode($row['data'], true);
|
|
|
|
if (!empty($row['tags'])) {
|
|
$tag_ids = array_flip(explode(',', $row['tags']));
|
|
$row['tags'] = array_intersect_key($this->getTags(), $tag_ids);
|
|
}
|
|
|
|
$data[$row['id']] = $row;
|
|
}
|
|
|
|
// multi fetches
|
|
$this->fetchArticlesPhotos($data);
|
|
$this->fetchArticlesAuthors($data);
|
|
|
|
return $data;
|
|
}
|
|
|
|
public function fetchArticlesPhotos(array &$articles): void
|
|
{
|
|
if (!$this->image) {
|
|
return;
|
|
}
|
|
|
|
if (empty($articles)) {
|
|
return;
|
|
}
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('pa.id_art as id_article, ph.id, ph.descr, ph.source, ph.image_2 AS image_big, ph.date_update')
|
|
->from('photos_articles_relation', 'pa')
|
|
->leftJoin('pa', 'photos', 'ph', 'pa.id_photo = ph.id')
|
|
->andWhere(Translation::coalesceTranslatedFields(PhotosTranslation::class))
|
|
->andWhere(Operator::inIntArray(array_keys($articles), 'pa.id_art'))
|
|
->andWhere(Operator::equals(['pa.show_in_lead' => 'Y', 'pa.active' => 'Y']));
|
|
|
|
foreach ($qb->execute() as $item) {
|
|
if (empty($articles[$item['id_article']])) {
|
|
continue;
|
|
}
|
|
|
|
if ($this->image == 'photo') {
|
|
$articles[$item['id_article']]['photoId'] = $item['id'];
|
|
$articles[$item['id_article']]['photoDescr'] = $item['descr'];
|
|
$articles[$item['id_article']]['photoDateUpdate'] = strtotime($item['date_update']);
|
|
} else {
|
|
$articles[$item['id_article']]['image'] = getImage($item['id'], $item['image_big'], $item['source'], $this->image, $item['descr'], strtotime($item['date_update']));
|
|
}
|
|
}
|
|
}
|
|
|
|
public function fetchArticlesAuthors(array &$articles): void
|
|
{
|
|
if (!findModule(\Modules::ARTICLES_AUTHORS)) {
|
|
return;
|
|
}
|
|
|
|
if (empty($articles)) {
|
|
return;
|
|
}
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('aar.id_art as id_article, au.*')
|
|
->from('articles_authors', 'au')
|
|
->join('au', 'articles_authors_relation', 'aar', 'au.id = aar.id_auth')
|
|
->andWhere(Translation::coalesceTranslatedFields(ArticlesAuthorsTranslation::class))
|
|
->andWhere(Operator::inIntArray(array_keys($articles), 'aar.id_art'));
|
|
|
|
foreach ($qb->execute() as $item) {
|
|
if (empty($articles[$item['id_article']])) {
|
|
continue;
|
|
}
|
|
|
|
$articles[$item['id_article']]['authors'][$item['id']] = $item;
|
|
}
|
|
}
|
|
|
|
public function setImage($image): self
|
|
{
|
|
$this->image = $image;
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function createPager(int $pageNumber)
|
|
{
|
|
$pager = new \Pager();
|
|
$pager->setOnPage($this->getDivide());
|
|
$pager->setPageNumber($pageNumber);
|
|
|
|
return $pager;
|
|
}
|
|
|
|
public function getDivide(): int
|
|
{
|
|
if (!$this->divide) {
|
|
$this->divide = findModule(\Modules::ARTICLES, 'limit') ?: 25;
|
|
}
|
|
|
|
return $this->divide;
|
|
}
|
|
|
|
public function setDivide(int $divide)
|
|
{
|
|
$this->divide = $divide;
|
|
}
|
|
|
|
/**
|
|
* @return int
|
|
*/
|
|
public function getTotalCount()
|
|
{
|
|
return $this->total_count;
|
|
}
|
|
|
|
public function getTags()
|
|
{
|
|
if (!isset($this->tags)) {
|
|
$this->tags = [];
|
|
$qb = sqlQueryBuilder()
|
|
->select('at.*')
|
|
->from('articles_tags', 'at')
|
|
->andWhere(Translation::coalesceTranslatedFields(ArticlesTagsTranslation::class))
|
|
->execute();
|
|
|
|
$tags = $qb->fetchAll();
|
|
if (!empty($tags)) {
|
|
$this->tags = array_combine(array_column($tags, 'id'), $tags);
|
|
}
|
|
}
|
|
|
|
return $this->tags;
|
|
}
|
|
|
|
protected function prepareArticleSection(array $section): array
|
|
{
|
|
$section['url'] = path('kupshop_content_articles_articles_2', ['IDb' => $section['id'], 'slug' => StringUtil::slugify($section['name'])]);
|
|
$section['children'] = $this->getSectionsTree($section['id']);
|
|
$section['articles_count'] = $this->getArticlesCount(Operator::equals(['ar.id_branch' => $section['id']]));
|
|
|
|
return $section;
|
|
}
|
|
}
|