public static function getArchivePhoto($type, $filter, $sort, $limit, $offset, $count = false)
{
///////////////////////////////////SELECT//////////////////////////////////////
if ($count) {
$query = (new Query())->select('COUNT(1)');
} else {
$query = (new Query())->select(['p.id AS photo_id', 'p.src AS photo_src', 'p.name AS photo_name', 'u.id AS user_id', 'u.name AS user_name', 'l1.name AS photo_category', 'l2.name AS photo_subcategory', 'p.date AS photo_date']);
if ($type == "photo_day") {
$query->addSelect('bp.date AS glory');
} elseif ($type == "photo_week") {
$query->addSelect(['bp.week_num AS week_num', 'bp.year AS year']);
} elseif ($type == "photo_month") {
$query->addSelect(['bp.month_num AS month_num', 'bp.year AS year']);
} elseif ($type == "photo_year") {
$query->addSelect('bp.year_num AS year');
}
}
///////////////////////////////////FROM//////////////////////////////////////
$query = self::getFromToQuery($type, $query);
///////////////////////////////////JOIN//////////////////////////////////////
$query->innerJoin('photos p', 'bp.id_photo=p.id')->innerJoin('user u', 'p.id_author=u.id')->innerJoin('level_1 l1', 'p.id_level_1=l1.id')->leftJoin('level_2 l2', 'p.id_level_2=l2.id');
///////////////////////////////////WHERE//////////////////////////////////////
if ($filter) {
$query->filterWhere(['=', 'p.id', $filter['photo_id']])->AndFilterWhere(['like', 'p.name', $filter['photo_name']])->AndFilterWhere(['like', 'u.name', $filter['user_name']])->AndFilterWhere(['like', 'l1.name', $filter['photo_category']])->AndFilterWhere(['like', 'l2.name', $filter['photo_subcategory']])->AndFilterWhere(['>=', 'p.date', $filter['date_min']])->AndFilterWhere(['<=', 'p.date', $filter['date_max']]);
$query = self::getFilterGlory($filter['glory_min'], $filter['glory_max'], $type, $query);
}
///////////////////////////////////LIMIT, OFFSET//////////////////////////////////////
if (!$count) {
if ($limit) {
$query->limit($limit)->offset($offset);
}
///////////////////////////////////ORDER BY//////////////////////////////////////
if ($sort) {
if ($sort == 'glory' || $sort == '-glory') {
$sort = self::getSortGlory($sort, $type);
$query->orderBy($sort);
} else {
$query = AdminCommon::getSortQuery($query, $sort);
}
}
$photos = $query->all();
foreach ($photos as &$photo) {
$photo['photo_src'] = '/img/photos/' . str_replace('-', '/', explode(' ', $photo['photo_date'])[0]) . '/s_' . $photo['photo_src'];
$photo['photo_date'] = explode(" ", $photo['photo_date'])[0];
if ($photo['photo_subcategory'] == "") {
$photo['photo_subcategory'] = "Немає підкатегорії";
}
$photo['glory'] = self::generatePhotoGlory($photo, $type);
}
return $photos;
} else {
return $query->scalar();
}
}
public static function getArticles($filter, $sort, $offset, $limit)
{
$query = (new Query())->select('
a.id AS article_id,
a.name AS article_name,
a.date AS article_date,
a.thumbnail AS article_cover,
a.rating_all AS article_rating,
a.id AS article_id,
u.id AS user_id,
u.name AS user_name,
l1.name AS category_name,
')->from('articles a')->innerJoin('user u', 'a.id_author=u.id')->innerJoin('level_1 l1', 'a.id_level_1=l1.id');
if ($filter) {
self::getFilterArticles($filter, $query);
}
if ($sort) {
AdminCommon::getSortQuery($query, $sort);
}
$articles = $query->limit($limit)->offset($offset)->all();
return $articles;
}
