<?php
namespace Web\Modules\Advertisement\Model;
use Application\Modules\Profiles\Model\Profiles;
use Common\Mailer\Message;
use Web\Core\Language\Language as FL;
use Web\Core\Engine\Model as WebModel;
use Web\Core\Engine\Navigation as WebNavigation;
use Web\Core\Engine\Url as WebUrl;
use Web\Modules\Profiles\Model\Profile;
class Advertisement
{
const QUERY_DATAGRID_BROWSE_PROFILE_GROUPS =
'SELECT gr.id, g.name AS group_name, UNIX_TIMESTAMP(gr.expires_on) AS expires_on,
IF(gr.expires_on IS NOT NULL AND gr.expires_on <= NOW(), 1, 0) AS hidden
FROM profiles_groups AS g
INNER JOIN profiles_groups_rights AS gr ON gr.group_id = g.id
WHERE gr.profile_id = ?';
public static function get(string $url): array
{
$advertisementPost = (array)WebModel::getContainer()->get('database')->getRecord(
'SELECT i.id, i.revision_id, i.language, i.title, i.introduction, i.text, i.category_id,
c.title AS category_title, m2.url AS category_url, i.image,
UNIX_TIMESTAMP(i.publish_on) AS publish_on, i.user_id,
i.allow_comments, m.id AS meta_id, i.open_hours, i.special_open_hours, i.payments,i.facebook, i.twitter, i.instagram, p.email, p.id as profile_id, i.type,
m.keywords AS meta_keywords, m.keywords_overwrite AS meta_keywords_overwrite,
m.description AS meta_description, m.description_overwrite AS meta_description_overwrite,
m.title AS meta_title, m.title_overwrite AS meta_title_overwrite, m.custom AS meta_custom,
m.url, i.tags, i.youtube, l.lat, l.lng,
m.data AS meta_data, m.seo_follow AS meta_seo_follow, m.seo_index AS meta_seo_index, l.street as loc_street, l.number as loc_number, l.zip as loc_zip, l.city as loc_city, l.country as loc_country, l.phone_number as loc_phone_number, l.email as loc_email, l.website as loc_website
FROM advertisement_posts AS i
LEFT JOIN advertisement_categories AS c ON i.category_id = c.id
INNER JOIN meta AS m ON i.meta_id = m.id
LEFT JOIN meta AS m2 ON c.meta_id = m2.id
LEFT JOIN profile_advertisement AS pa ON pa.advertisement_id = i.id
LEFT JOIN profiles AS p on p.id = pa.profile_id
LEFT JOIN location AS l on i.id = l.advertisement_id
WHERE i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on <= ? AND m.url = ?
LIMIT 1',
['active', LANGUAGE, false, WebModel::getUTCDate('Y-m-d H:i'), $url]
);
if ($advertisementPost['profile_id'])
$advertisementPost['profile'] = Profiles::getSettings($advertisementPost['profile_id']);
else {
$advertisementPost['profile'] = [];
}
$advertisementPost['open_hours'] = json_decode($advertisementPost['open_hours']);
$special_open_hours = json_decode($advertisementPost['special_open_hours']);
$advertisementPost['special_open_hours'] = [];
if (!empty($special_open_hours)) {
foreach ($special_open_hours as $key => $special_open_hour) {
if ($special_open_hour == 1)
$advertisementPost['special_open_hours'][] = $key;
}
}
$payments = json_decode($advertisementPost['payments']);
if (!empty($payments)) {
$advertisementPost['payments'] = [];
foreach ($payments as $key => $payment) {
$advertisementPost['payments'][] = $key;
}
}
return self::completeAdvertisementPost($advertisementPost);
}
public static function getAllSearchPost($query)
{
return (array)WebModel::getContainer()->get('database')->getRecords(
"SELECT i.title
FROM advertisement_posts AS i
WHERE (i.title LIKE '%$query%' OR i.tags LIKE '%$query%') AND i.status LIKE 'active'
GROUP BY i.title
ORDER BY i.title ASC"
);
}
public static function getAllSearchCategories($query)
{
return (array)WebModel::getContainer()->get('database')->getRecords(
"SELECT i.title
FROM advertisement_categories AS i
WHERE (i.title LIKE '%$query%')
GROUP BY i.title
ORDER BY i.title ASC"
);
}
public static function getAllSearchTags($query)
{
return (array)WebModel::getContainer()->get('database')->getRecords(
"SELECT i.title
FROM advertisement_tags AS i
WHERE i.title LIKE '%$query%' AND i.header NOT LIKE 'zoekfilter'
GROUP BY i.title
ORDER BY i.title ASC"
);
}
public static function getAll($company = false, $city = false, $rubriek = false, $service = false, $state = false, $order = false, $page = false): array
{
$queryCompany = '';
$queryCity = '';
$queryRubriek = '';
$active = '';
$queryService = '';
$queryState = '';
$queryOrder = '';
$rand = '';
if ($company) {
$company = str_replace("'", "\\'", strtolower($company));
$queryCompany = " AND (i.title LIKE '%$company%' OR c.title LIKE '$company' OR i.tags LIKE '$company' OR at.title LIKE '$company')";
}
if ($city)
$queryCity = " AND (l.city LIKE '$city' OR l.state LIKE '$city' OR l.street LIKE '$city' OR pr.name LIKE '$city')";
if ($rubriek) {
$r = join("','", $rubriek);
$queryRubriek = " AND c.title IN ('$r')";
}
if ($service) {
$s = join("'|'", $service);
$queryService = " AND i.tags REGEXP '$s'";
}
if ($state) {
$t = join("','", $state);
$queryState = " AND l.state IN ('$t')";
}
if($rubriek || $service || $state)
$active = ' AND i.type = "adverteerder"';
if($order)
$queryOrder = $order == 1 ? ', i.title ASC' : ', i.title DESC';
if($page)
$rand = ', RAND()';
$items = (array)WebModel::getContainer()->get('database')->getRecords(
"SELECT i.id, i.revision_id, i.language, i.title, i.introduction, i.text, i.num_comments AS comments_count, i.category_id, i.profile_id,
c.title AS category_title, i.image, i.tags, i.user_id, i.allow_comments,
m.url, i.open_hours, l.street as loc_street, l.number as loc_number, l.zip as loc_zip, l.city as loc_city, l.country as loc_country, l.phone_number as loc_phone_number, l.email as loc_email, l.website as loc_website, l.lat, l.lng, i.type, l.state
FROM advertisement_posts AS i
LEFT JOIN advertisement_categories AS c ON i.category_id = c.id
LEFT JOIN meta AS m ON i.meta_id = m.id
LEFT JOIN location AS l on i.id = l.advertisement_id
LEFT JOIN provincies AS pr on l.state = pr.id
LEFT JOIN advertisement_tags AS at on at.category_id = c.id
WHERE i.status = ?
$queryCompany
$queryCity
$queryRubriek
$queryService
$queryState
$active
GROUP BY i.id
ORDER BY FIELD(i.type, 'adverteerder') DESC $queryOrder $rand",
['active']
);
if (empty($items)) {
return [];
}
return $items;
}
public static function getAllCategories(): array
{
$return = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT c.id, c.title AS label, m.url, COUNT(c.id) AS total, m.data AS meta_data,
m.seo_follow AS meta_seo_follow, m.seo_index AS meta_seo_index
FROM advertisement_categories AS c
INNER JOIN advertisement_posts AS i ON c.id = i.category_id AND c.language = i.language
INNER JOIN meta AS m ON c.meta_id = m.id
WHERE c.language = ? AND i.status = ? AND i.hidden = ? AND i.publish_on <= ?
GROUP BY c.id',
[LANGUAGE, 'active', false, WebModel::getUTCDate('Y-m-d H:i')],
'id'
);
foreach ($return as &$row) {
if (isset($row['meta_data'])) {
$row['meta_data'] = @unserialize($row['meta_data']);
}
}
return $return;
}
public static function getAllRubrieken(): array
{
$return = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT c.id, c.title AS label, m.url, COUNT(c.id) AS total, m.data AS meta_data,
m.seo_follow AS meta_seo_follow, m.seo_index AS meta_seo_index
FROM advertisement_categories AS c
INNER JOIN meta AS m ON c.meta_id = m.id
WHERE c.language = ?
GROUP BY c.id ORDER BY c.title',
['nl'],
'id'
);
foreach ($return as &$row) {
if (isset($row['meta_data'])) {
$row['meta_data'] = @unserialize($row['meta_data']);
}
}
return $return;
}
public static function getSearchTerms(): array
{
$return = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT *
FROM advertisement_search_result AS c
WHERE MONTH(created_at) = MONTH(CURRENT_DATE())
AND YEAR(created_at) = YEAR(CURRENT_DATE())
AND term NOT LIKE ""
GROUP BY c.term, c.ip_address ORDER BY c.created_at DESC LIMIT 10'
);
return $return;
}
public static function getSearchTermsAmount(): array
{
$return = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT *
FROM advertisement_search_result AS c
WHERE MONTH(created_at) = MONTH(CURRENT_DATE())
AND YEAR(created_at) = YEAR(CURRENT_DATE())
AND term NOT LIKE ""
ORDER BY c.created_at DESC'
);
return $return;
}
public static function getBannerStats(): array
{
$return = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT *
FROM mv_stats_banner AS c
INNER JOIN advertisement_media as m ON m.title = banner_title WHERE m.type IN ("banner", "banner_big")'
);
return $return;
}
public static function getFormStats(): array
{
$return = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT *
FROM mv_stats_contactform AS c
ORDER BY c.date DESC'
);
return $return;
}
public static function getFormStatsClient($postId, $type): array
{
$return = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT *
FROM advertisement_offerte AS c
INNER JOIN forms_data_fields as f on c.data_id = f.data_id
WHERE c.post_id = ? AND c.type = ?
ORDER BY c.created_at DESC',
[$postId, $type]
);
return $return;
}
public static function getFormStatsAll($type): array
{
$return = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT *
FROM advertisement_offerte AS c
INNER JOIN forms_data_fields as f on c.data_id = f.data_id
LEFT JOIN advertisement_posts as a on c.post_id = a.id
WHERE c.type = ? and a.status = ?
ORDER BY c.created_at DESC',
[$type, 'active']
);
return $return;
}
public static function getCategory(string $slug): array
{
$category = (array)WebModel::getContainer()->get('database')->getRecord(
'SELECT c.id, c.title AS label, m.url, m.id AS meta_id, COUNT(c.id) AS total
FROM advertisement_categories AS c
INNER JOIN advertisement_posts AS i ON c.id = i.category_id AND c.language = i.language
INNER JOIN meta AS m ON c.meta_id = m.id AND m.url = ?
WHERE c.language = ? AND i.status = ? AND i.hidden = ? AND i.publish_on <= ?
GROUP BY c.id',
[$slug, LANGUAGE, 'active', false, WebModel::getUTCDate('Y-m-d H:i')],
'id'
);
if (empty($category)) {
return [];
}
$category['meta'] = WebModel::get('icebird.repository.meta')->find($category['meta_id']);
return $category;
}
public static function getAllComments(int $limit = 10, int $offset = 0): array
{
$comments = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT i.id, UNIX_TIMESTAMP(i.created_on) AS created_on, i.author, i.text,
p.id AS post_id, p.title AS post_title, m.url AS post_url, i.email
FROM advertisement_comments AS i
INNER JOIN advertisement_posts AS p ON i.post_id = p.id AND i.language = p.language
INNER JOIN meta AS m ON p.meta_id = m.id
WHERE i.status = ? AND i.language = ?
GROUP BY i.id
ORDER BY i.created_on DESC
LIMIT ?, ?',
['published', LANGUAGE, $offset, $limit]
);
foreach ($comments as &$row) {
$row['author'] = htmlspecialchars($row['author']);
$row['text'] = htmlspecialchars($row['text']);
$row['gravatar_id'] = md5($row['email']);
}
return $comments;
}
public static function getAllCount($company = false, $city = false): int
{
$queryCompany = '';
$queryCity = '';
if ($company) {
$company = str_replace("'", "\\'", $company);
$queryCompany = " AND (i.title LIKE '%$company%' OR c.title LIKE '$company' OR at.title LIKE '$company' OR i.tags LIKE '%$company%')";
}
if ($city)
$queryCity = " AND (l.city LIKE '$city' OR l.state LIKE '$city' OR l.street LIKE '$city')";
return (int)WebModel::getContainer()->get('database')->getNumRows(
"SELECT COUNT(i.id)
FROM advertisement_posts AS i
LEFT JOIN advertisement_categories AS c ON i.category_id = c.id
LEFT JOIN location AS l on i.id = l.advertisement_id
LEFT JOIN advertisement_tags as at on c.id = at.category_id
WHERE i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on <= ?
$queryCompany
$queryCity
GROUP BY i.id",
['active',
'nl',
false,
WebModel::getUTCDate('Y-m-d H:i'),
]
);
}
public static function getAllForCategory(string $categoryUrl, int $limit = 10, int $offset = 0): array
{
$rand = '';
if ($offset == 0)
$rand = ', RAND() ';
$items = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT i.id, i.revision_id, i.language, i.title, i.introduction, i.text, i.num_comments AS comments_count, i.category_id, i.profile_id,
c.title AS category_title, i.image, m2.url AS category_url, i.tags,
UNIX_TIMESTAMP(i.publish_on) AS publish_on, i.user_id, i.allow_comments,
m.url, i.open_hours, l.street as loc_street, l.number as loc_number, l.zip as loc_zip, l.city as loc_city, l.country as loc_country, l.phone_number as loc_phone_number, l.email as loc_email, l.website as loc_website, l.lat, l.lng, i.type, l.state
FROM advertisement_posts AS i
INNER JOIN advertisement_categories AS c ON i.category_id = c.id
INNER JOIN meta AS m ON i.meta_id = m.id
INNER JOIN meta AS m2 ON c.meta_id = m2.id
LEFT JOIN location AS l on i.id = l.advertisement_id
LEFT JOIN advertisement_tags as at on c.id = at.category_id
WHERE i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on <= ? AND m2.url = ?
GROUP BY i.id
ORDER BY FIELD(i.type, "adverteerder") DESC ' . $rand . ' LIMIT ?, ?',
[
'active',
LANGUAGE,
false,
WebModel::getUTCDate('Y-m-d H:i'),
$categoryUrl,
$offset,
$limit,
],
'id'
);
if (empty($items)) {
return [];
}
$link = WebNavigation::getUrlForBlock('Advertisement', 'Detail');
$categoryLink = WebNavigation::getUrlForBlock('Advertisement', 'Category');
$folders = WebModel::getThumbnailFolders(WEB_FILES_PATH . '/Advertisement/images', true);
foreach ($items as $key => $row) {
$items[$key]['full_url'] = $link . '/' . $row['url'];
$items[$key]['category_full_url'] = $categoryLink . '/' . $row['category_url'];
if ($row['comments_count'] > 0) {
$items[$key]['comments'] = true;
}
if ($row['comments_count'] > 1) {
$items[$key]['comments_multiple'] = true;
}
$items[$key]['allow_comments'] = (bool)$row['allow_comments'];
if (!WebModel::get('icebird.settings')->get('Advertisement', 'allow_comments')) {
$items[$key]['allow_comments'] = false;
}
if (isset($row['image'])) {
foreach ($folders as $folder) {
$items[$key]['image_' . $folder['dirname']] = $folder['url'] . '/' . $folder['dirname'] .
'/' . $row['image'];
}
}
}
return $items;
}
public static function getAllForCategoryCount(string $url): int
{
return (int)WebModel::getContainer()->get('database')->getVar(
'SELECT COUNT(i.id) AS count
FROM advertisement_posts AS i
INNER JOIN advertisement_categories AS c ON i.category_id = c.id
INNER JOIN meta AS m ON c.meta_id = m.id
WHERE i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on <= ? AND m.url = ?',
['active', LANGUAGE, false, WebModel::getUTCDate('Y-m-d H:i'), $url]
);
}
public static function getAllForDateRange(int $start, int $end, int $limit = 10, int $offset = 0): array
{
$items = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT i.id, i.revision_id, i.language, i.title, i.introduction, i.text, i.num_comments AS comments_count,
c.title AS category_title, m2.url AS category_url, i.image,
UNIX_TIMESTAMP(i.publish_on) AS publish_on, i.user_id, i.allow_comments,
m.url
FROM advertisement_posts AS i
INNER JOIN advertisement_categories AS c ON i.category_id = c.id
INNER JOIN meta AS m ON i.meta_id = m.id
INNER JOIN meta AS m2 ON c.meta_id = m2.id
WHERE i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on BETWEEN ? AND ?
ORDER BY i.publish_on DESC
LIMIT ?, ?',
[
'active',
LANGUAGE,
false,
WebModel::getUTCDate('Y-m-d H:i', $start),
WebModel::getUTCDate('Y-m-d H:i', $end),
$offset,
$limit,
],
'id'
);
if (empty($items)) {
return [];
}
$link = WebNavigation::getUrlForBlock('Advertisement', 'Detail');
$folders = WebModel::getThumbnailFolders(WEB_FILES_PATH . '/Advertisement/images', true);
foreach ($items as $key => $row) {
$items[$key]['full_url'] = $link . '/' . $row['url'];
if ($row['comments_count'] > 0) {
$items[$key]['comments'] = true;
}
if ($row['comments_count'] > 1) {
$items[$key]['comments_multiple'] = true;
}
$items[$key]['allow_comments'] = (bool)$row['allow_comments'];
if (!WebModel::get('icebird.settings')->get('Advertisement', 'allow_comments')) {
$items[$key]['allow_comments'] = false;
}
if (isset($row['image'])) {
foreach ($folders as $folder) {
$items[$key]['image_' . $folder['dirname']] = $folder['url'] . '/' . $folder['dirname'] .
'/' . $row['image'];
}
}
}
return $items;
}
public static function getAllForDateRangeCount(int $start, int $end): int
{
return (int)WebModel::getContainer()->get('database')->getVar(
'SELECT COUNT(i.id)
FROM advertisement_posts AS i
WHERE i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on BETWEEN ? AND ?',
[
'active',
LANGUAGE,
false,
WebModel::getUTCDate('Y-m-d H:i:s', $start),
WebModel::getUTCDate('Y-m-d H:i:s', $end),
]
);
}
public static function getArchiveNumbers(): array
{
$numbers = WebModel::getContainer()->get('database')->getPairs(
'SELECT DATE_FORMAT(i.publish_on, "%Y%m") AS month, COUNT(i.id)
FROM advertisement_posts AS i
INNER JOIN meta AS m ON i.meta_id = m.id
WHERE i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on <= ?
GROUP BY month',
['active', LANGUAGE, false, WebModel::getUTCDate('Y-m-d H:i')]
);
$stats = [];
$link = WebNavigation::getUrlForBlock('Advertisement', 'Archive');
$firstYear = (int)date('Y');
$lastYear = 0;
foreach ($numbers as $key => $count) {
$year = mb_substr($key, 0, 4);
$month = mb_substr($key, 4, 2);
if ($year < $firstYear) {
$firstYear = $year;
}
if ($year > $lastYear) {
$lastYear = $year;
}
$timestamp = gmmktime(00, 00, 00, $month, 01, $year);
if (!isset($stats[$year])) {
$stats[$year] = [
'url' => $link . '/' . $year,
'label' => $year,
'total' => 0,
'months' => null,
];
}
$stats[$year]['total'] += (int)$count;
$stats[$year]['months'][$key] = [
'url' => $link . '/' . $year . '/' . $month,
'label' => $timestamp,
'total' => $count,
];
}
for ($i = $firstYear; $i <= $lastYear; ++$i) {
if (!isset($stats[$i])) {
$stats[$i] = ['url' => null, 'label' => $i, 'total' => 0, 'months' => null];
}
}
krsort($stats);
foreach ($stats as &$row) {
if ($row['total'] == 0) {
$row['url'] = null;
}
if (!empty($row['months'])) {
// sort months
ksort($row['months']);
}
}
return $stats;
}
public static function getComments(int $advertisementPostId): array
{
$comments = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT c.id, UNIX_TIMESTAMP(c.created_on) AS created_on, c.text, c.data,
c.author, c.email, c.website, c.rating
FROM advertisement_comments AS c
WHERE c.post_id = ? AND c.status = ? AND c.language = ?
ORDER BY c.id ASC',
[$advertisementPostId, 'published', LANGUAGE]
);
foreach ($comments as &$row) {
$row['author'] = htmlspecialchars($row['author']);
$row['text'] = htmlspecialchars($row['text']);
$row['rating'] = htmlspecialchars($row['rating']);
$row['gravatar_id'] = md5($row['email']);
}
return $comments;
}
public static function getForTags(array $advertisementPostIds): array
{
$items = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT i.title, i.image, m.url
FROM advertisement_posts AS i
INNER JOIN meta AS m ON m.id = i.meta_id
WHERE i.status = ? AND i.hidden = ? AND i.id IN (' . implode(',', $advertisementPostIds) . ') AND i.publish_on <= ?
ORDER BY i.publish_on DESC',
['active', false, WebModel::getUTCDate('Y-m-d H:i')]
);
if (!empty($items)) {
$link = WebNavigation::getUrlForBlock('Advertisement', 'Detail');
$folders = WebModel::getThumbnailFolders(WEB_FILES_PATH . '/Advertisement/images', true);
foreach ($items as &$row) {
$row['full_url'] = $link . '/' . $row['url'];
if (isset($row['image'])) {
foreach ($folders as $folder) {
$row['image_' . $folder['dirname']] = $folder['url'] . '/' . $folder['dirname'] .
'/' . $row['image'];
}
}
}
}
return $items;
}
public static function getTagsByCategory ($categoryId) {
return (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT i.title, i.header
FROM advertisement_tags AS i
WHERE i.category_id = ? AND i.header IN ("Faciliteiten", "Diensten", "Specialismen")
ORDER BY i.title ASC',
[$categoryId]
);
}
public static function getIdForTags(WebUrl $url): int
{
$itemUrl = (string)$url->getParameter(1);
return self::get($itemUrl)['id'] ?? 0;
}
public static function getNavigation(int $advertisementPostId): array
{
$database = WebModel::getContainer()->get('database');
$date = (string)$database->getVar(
'SELECT i.publish_on
FROM advertisement_posts AS i
WHERE i.id = ? AND i.status = ?',
[$advertisementPostId, 'active']
);
if ($date === '') {
return [];
}
$navigation = [];
$detailLink = WebNavigation::getUrlForBlock('Advertisement', 'Detail') . '/';
$navigation['previous'] = $database->getRecord(
'SELECT i.id, i.title, CONCAT(?, m.url) AS url
FROM advertisement_posts AS i
INNER JOIN meta AS m ON i.meta_id = m.id
WHERE i.id != ? AND i.status = ? AND i.hidden = ? AND i.language = ? AND
((i.publish_on = ? AND i.id < ?) OR i.publish_on < ?)
ORDER BY i.publish_on DESC, i.id DESC
LIMIT 1',
[$detailLink, $advertisementPostId, 'active', false, LANGUAGE, $date, $advertisementPostId, $date]
);
$navigation['next'] = $database->getRecord(
'SELECT i.id, i.title, CONCAT(?, m.url) AS url
FROM advertisement_posts AS i
INNER JOIN meta AS m ON i.meta_id = m.id
WHERE i.id != ? AND i.status = ? AND i.hidden = ? AND i.language = ? AND
((i.publish_on = ? AND i.id > ?) OR (i.publish_on > ? AND i.publish_on <= ?))
ORDER BY i.publish_on ASC, i.id ASC
LIMIT 1',
[
$detailLink,
$advertisementPostId,
'active',
false,
LANGUAGE,
$date,
$advertisementPostId,
$date,
WebModel::getUTCDate('Y-m-d H:i'),
]
);
if (empty($navigation['previous'])) {
unset($navigation['previous']);
}
if (empty($navigation['next'])) {
unset($navigation['next']);
}
return $navigation;
}
public static function getRecentComments(int $limit = 5): array
{
$return = [];
$comments = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT c.id, c.author, c.website, c.email, UNIX_TIMESTAMP(c.created_on) AS created_on, c.text,
i.id AS post_id, i.title AS post_title,
m.url AS post_url
FROM advertisement_comments AS c
INNER JOIN advertisement_posts AS i ON c.post_id = i.id AND c.language = i.language
INNER JOIN meta AS m ON i.meta_id = m.id
WHERE c.status = ? AND i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on <= ?
ORDER BY c.id DESC
LIMIT ?',
['published', 'active', LANGUAGE, false, WebModel::getUTCDate('Y-m-d H:i'), $limit]
);
if (empty($comments)) {
return $return;
}
$link = WebNavigation::getUrlForBlock('Advertisement', 'Detail');
foreach ($comments as &$row) {
$row['post_full_url'] = $link . '/' . $row['post_url'];
$row['full_url'] = $link . '/' . $row['post_url'] . '#comment-' . $row['id'];
$row['gravatar_id'] = md5($row['email']);
}
return $comments;
}
public static function getRelated(int $advertisementPostId, int $limit = 5): array
{
$link = WebNavigation::getUrlForBlock('Advertisement', 'Detail');
$items = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT i.id, i.title, m.url
FROM advertisement_posts AS i
INNER JOIN meta AS m ON i.meta_id = m.id
WHERE i.status = ? AND i.language = ? AND i.hidden = ? AND i.publish_on <= ?
ORDER BY i.publish_on DESC, i.id DESC
LIMIT ?',
['active', LANGUAGE, false, WebModel::getUTCDate('Y-m-d H:i'), $limit],
'id'
);
foreach ($items as &$row) {
$row['full_url'] = $link . '/' . $row['url'];
}
return $items;
}
public static function getRevision(string $url, int $revisionId): array
{
$advertisementPost = (array)WebModel::getContainer()->get('database')->getRecord(
'SELECT i.id, i.revision_id, i.language, i.title, i.introduction, i.text, i.image,
c.title AS category_title, m2.url AS category_url, m.id AS meta_id,
UNIX_TIMESTAMP(i.publish_on) AS publish_on, i.user_id,
i.allow_comments,
m.keywords AS meta_keywords, m.keywords_overwrite AS meta_keywords_overwrite,
m.description AS meta_description, m.description_overwrite AS meta_description_overwrite,
m.title AS meta_title, m.title_overwrite AS meta_title_overwrite, m.custom AS meta_custom,
m.url,
m.data AS meta_data, m.seo_follow AS meta_seo_follow, m.seo_index AS meta_seo_index
FROM advertisement_posts AS i
INNER JOIN advertisement_categories AS c ON i.category_id = c.id
INNER JOIN meta AS m ON i.meta_id = m.id
INNER JOIN meta AS m2 ON c.meta_id = m2.id
WHERE i.language = ? AND i.revision_id = ? AND m.url = ?
LIMIT 1',
[LANGUAGE, $revisionId, $url]
);
return self::completeAdvertisementPost($advertisementPost);
}
private static function completeAdvertisementPost(array $advertisementPost)
{
if (isset($advertisementPost['meta_id'])) {
$advertisementPost['meta'] = WebModel::get('icebird.repository.meta')->find($advertisementPost['meta_id']);
}
if (isset($advertisementPost['meta_data'])) {
$advertisementPost['meta_data'] = @unserialize($advertisementPost['meta_data'], ['allowed_classes' => false]);
}
if (isset($advertisementPost['image'])) {
$folders = WebModel::getThumbnailFolders(WEB_FILES_PATH . '/Advertisement/images', true);
foreach ($folders as $folder) {
$advertisementPost['image_' . $folder['dirname']] = $folder['url'] . '/' . $folder['dirname'] . '/' . $advertisementPost['image'];
}
}
return $advertisementPost;
}
public static function insertComment(array $comment): int
{
$database = WebModel::getContainer()->get('database');
$comment['id'] = (int)$database->insert('advertisement_comments', $comment);
if ($comment['status'] == 'published') {
$numComments = (int)WebModel::getContainer()->get('database')->getVar(
'SELECT COUNT(i.id) AS comment_count
FROM advertisement_comments AS i
INNER JOIN advertisement_posts AS p ON i.post_id = p.id AND i.language = p.language
WHERE i.status = ? AND i.post_id = ? AND i.language = ? AND p.status = ?
GROUP BY i.post_id',
['published', $comment['post_id'], 'nl', 'active']
);
$database->update('advertisement_posts', ['num_comments' => $numComments], 'id = ?', $comment['post_id']);
}
return $comment['id'];
}
public static function isModerated(string $author, string $email): bool
{
return (bool)WebModel::getContainer()->get('database')->getVar(
'SELECT 1
FROM advertisement_comments AS c
WHERE c.status = ? AND c.author = ? AND c.email = ?
LIMIT 1',
['published', $author, $email]
);
}
public static function notifyAdmin(array $comment, $emailClient = false): void
{
if ($comment['status'] == 'spam') {
return;
}
$notifyByMailOnComment = WebModel::get('icebird.settings')->get(
'Advertisement',
'notify_by_email_on_new_comment',
false
);
$notifyByMailOnCommentToModerate = WebModel::get('icebird.settings')->get(
'Advertisement',
'notify_by_email_on_new_comment_to_moderate',
false
);
$url = SITE_URL . WebNavigation::getUrlForBlock('Advertisement', 'Detail') . '/' . $comment['post_url'] . '#comment-' . $comment['id'];
$ApplicationUrl = SITE_URL . WebNavigation::getApplicationUrlForBlock('comments', 'Advertisement') . '#tabModeration';
if($emailClient) {
$from = WebModel::get('icebird.settings')->get('Core', 'mailer_from');
$replyTo = WebModel::get('icebird.settings')->get('Core', 'mailer_reply_to');
$message = Message::newInstance('Uw inloggegevens voor TurkseGids.nl')
->setFrom([$from['email'] => $from['name']])
->setTo([$comment['email'] => ''])
->setReplyTo([$replyTo['email'] => $replyTo['name']])
->parseHtml(
'/Advertisement/Layout/Templates/Mails/Notification.html.twig',
[
'loginUrl' => 'https://www.turksegids.nl/aanmelden',
'firstName' => $comment['first_name'],
'lastName' => $comment['last_name'],
'email' => $comment['email'],
'password' => $comment['unencrypted_password'],
],
true
);
WebModel::get('mailer')->send($message);
}
if ($notifyByMailOnComment) {
$variables = [];
if ($comment['status'] == 'moderation') {
$variables['message'] = vsprintf(
FL::msg('AdvertisementEmailNotificationsNewCommentToModerate'),
[$comment['author'], $url, $comment['post_title'], $ApplicationUrl]
);
} elseif ($comment['status'] == 'published') {
$variables['message'] = vsprintf(
FL::msg('AdvertisementEmailNotificationsNewComment'),
[$comment['author'], $url, $comment['post_title']]
);
}
$to = WebModel::get('icebird.settings')->get('Core', 'mailer_to');
$from = WebModel::get('icebird.settings')->get('Core', 'mailer_from');
$replyTo = WebModel::get('icebird.settings')->get('Core', 'mailer_reply_to');
$message = Message::newInstance(FL::msg('NotificationSubject'))
->setFrom([$from['email'] => $from['name']])
->setTo([$to['email'] => $to['name']])
->setReplyTo([$replyTo['email'] => $replyTo['name']])
->parseHtml(
'/Core/Layout/Templates/Mails/Notification.html.twig',
$variables,
true
);
WebModel::get('mailer')->send($message);
} elseif ($notifyByMailOnCommentToModerate && $comment['status'] == 'moderation') {
$variables = [];
$variables['message'] = vsprintf(
FL::msg('AdvertisementEmailNotificationsNewCommentToModerate'),
[$comment['author'], $url, $comment['post_title'], $ApplicationUrl]
);
$to = WebModel::get('icebird.settings')->get('Core', 'mailer_to');
$from = WebModel::get('icebird.settings')->get('Core', 'mailer_from');
$replyTo = WebModel::get('icebird.settings')->get('Core', 'mailer_reply_to');
$message = Message::newInstance(FL::msg('NotificationSubject'))
->setFrom([$from['email'] => $from['name']])
->setTo([$to['email'] => $to['name']])
->setReplyTo([$replyTo['email'] => $replyTo['name']])
->parseHtml(
'/Core/Layout/Templates/Mails/Notification.html.twig',
$variables,
true
);
WebModel::get('mailer')->send($message);
}
}
public static function search(array $ids): array
{
$items = (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT i.id, i.title, i.introduction, i.text, m.url
FROM advertisement_posts AS i
INNER JOIN meta AS m ON i.meta_id = m.id
WHERE i.status = ? AND i.hidden = ? AND i.language = ? AND i.publish_on <= ? AND i.id IN (' .
implode(',', $ids) . ')',
['active', false, LANGUAGE, date('Y-m-d H:i')],
'id'
);
$detailUrl = WebNavigation::getUrlForBlock('Advertisement', 'Detail');
foreach ($items as &$item) {
$item['full_url'] = $detailUrl . '/' . $item['url'];
}
return $items;
}
public static function getAdvertisementByProfile(int $profileId)
{
return (array)WebModel::getContainer()->get('database')->getRecord(
'SELECT *
FROM advertisement_posts AS a
WHERE a.profile_id = ?
AND a.language = ?
AND a.status = ?',
[$profileId, LANGUAGE, 'active']
);
}
public static function getAdvertisementMedia(int $postId, $type = 'image')
{
return (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT m.id,m.filename,m.title
FROM advertisement_media AS m
WHERE m.post_id = ?
AND m.type = ?
ORDER BY m.sequence ASC',
[$postId, $type]
);
}
public static function getInvoice()
{
return
'SELECT m.id,m.filename, m.created_at, m.profile_id
FROM advertisement_invoice AS m
WHERE m.profile_id = ?
AND m.type = ?';
}
public static function getInvoices($id, $type = 'invoice')
{
return (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT m.id,m.filename, m.created_at
FROM advertisement_invoice AS m
WHERE m.profile_id = ?
AND m.type = ?',
[$id, $type]
);
}
public static function updateAdvertisement(array $advertisementPost, $advertisementId)
{
WebModel::getContainer()->get('database')->update('advertisement_posts', $advertisementPost, 'id = ?', $advertisementId);
}
public static function getZeroHits($subject = null, $location = null)
{
return (array)WebModel::getContainer()->get('database')->getRecord(
'SELECT *
FROM search_results
WHERE subject = ?
AND location = ?',
[$subject, $location]
);
}
public static function getAllZeroHits()
{
return (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT *
FROM search_results'
);
}
public static function addZeroHists($item)
{
$database = WebModel::getContainer()->get('database');
$database->insert('search_results', $item);
}
public static function addHist($item)
{
$database = WebModel::getContainer()->get('database');
$database->insert('advertisement_search_result', $item);
}
public static function updateZeroHists($id, $item)
{
$database = WebModel::getContainer()->get('database');
$database->update('search_results', $item, 'id = ?', $id);
}
public static function getOldRatings()
{
return (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT *
FROM waardering WHERE rating > 0'
);
}
public static function getOldTags($id)
{
return (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT t.Trefwoord
FROM vermeldingtrefwoorden as vt
INNER JOIN trefwoorden as t ON t.TrefwoordID = vt.TrefwoordID
WHERE vt.VermeldingID = ?
GROUP BY t.trefwoord',
[$id]
);
}
public static function getTagByName($category, $name)
{
return (array)WebModel::getContainer()->get('database')->getRecord(
'SELECT t.header, t.title
FROM advertisement_tags as t
WHERE t.title LIKE ? AND category_id = ?',
["%$name%", $category]
);
}
public static function getTagByNameCat($category, $name)
{
return (array)WebModel::getContainer()->get('database')->getRecord(
'SELECT t.header, t.title
FROM advertisement_tags as t
WHERE t.title LIKE ? AND category_id = ?',
["$name", $category]
);
}
public static function getStats($id)
{
return (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT * FROM advertisement_stats WHERE advertisement_id = ?',
[$id]
);
}
public static function insertOfferte($postId, $dataId, $type)
{
$database = WebModel::getContainer()->get('database');
$database->insert('advertisement_offerte', ['post_id' => $postId, 'data_id' => $dataId, 'type' => $type]);
}
public static function addMedia($data)
{
$database = WebModel::getContainer()->get('database');
$database->insert('advertisement_media', $data);
}
public function checkComment($ip, $postId)
{
if (strpos($ip, '2001:982:68dc:1') !== false) {
return false;
}else {
return (array)WebModel::getContainer()->get('database')->getRecords(
'SELECT * FROM advertisement_comments WHERE post_id = ? AND ip_address LIKE ?',
[$postId, $ip]
);
}
}
}