373 lines
13 KiB
PHP
373 lines
13 KiB
PHP
<?php
|
|
/**
|
|
* Post Query Class — fetches posts per category for a given month
|
|
*/
|
|
|
|
if (!defined('ABSPATH')) {
|
|
exit;
|
|
}
|
|
|
|
class Thalim_NL_Post_Query {
|
|
|
|
/**
|
|
* Window type per category ID (special cases).
|
|
* - datetime_to_fin : window = [datetime ∥ post_date, date_de_fin ∥ datetime ∥ post_date]
|
|
* - debut_minus35_to_fin: window = [date_de_debut - 35d ∥ post_date - 35d, date_de_fin ∥ date_de_debut ∥ post_date]
|
|
* - datetime_plus3m : window = [datetime ∥ post_date, datetime ∥ post_date + 3 months]
|
|
*
|
|
* All other categories use 'datetime_plus35d':
|
|
* window = [datetime ∥ post_date, datetime ∥ post_date + 35 days]
|
|
*/
|
|
private const SPECIAL_WINDOW_TYPES = [
|
|
THALIM_NL_CAT_APPELS => 'datetime_to_fin',
|
|
THALIM_NL_CAT_COLLOQUES => 'debut_minus35_to_fin',
|
|
THALIM_NL_CAT_SEMINAIRES => 'debut_minus35_to_fin',
|
|
THALIM_NL_CAT_COMMS => 'debut_minus35_to_fin',
|
|
THALIM_NL_CAT_SOUTENANCES => 'datetime_to_fin',
|
|
THALIM_NL_CAT_OUVRAGES => 'datetime_plus3m',
|
|
THALIM_NL_CAT_ARTICLES => 'datetime_plus3m',
|
|
];
|
|
|
|
private const DEFAULT_WINDOW_TYPE = 'datetime_plus35d';
|
|
|
|
/** Categories to exclude from the newsletter UI */
|
|
private const EXCLUDED_CATS = [
|
|
12, // Séance de séminaire
|
|
20, // Newsletter
|
|
31, // Non classé
|
|
];
|
|
|
|
/**
|
|
* Get all newsletter-eligible categories, grouped by parent.
|
|
* Returns [ parent_id => ['name' => string, 'children' => [cat_id => name, ...]], ... ]
|
|
*/
|
|
public static function get_eligible_categories(): array {
|
|
$all_cats = get_categories([
|
|
'taxonomy' => 'category',
|
|
'hide_empty' => false,
|
|
'orderby' => 'term_id',
|
|
'order' => 'ASC',
|
|
]);
|
|
|
|
$by_parent = [];
|
|
$parents = [];
|
|
|
|
foreach ($all_cats as $cat) {
|
|
if (in_array($cat->term_id, self::EXCLUDED_CATS, true)) {
|
|
continue;
|
|
}
|
|
if ($cat->parent == 0) {
|
|
$parents[$cat->term_id] = $cat->name;
|
|
}
|
|
}
|
|
|
|
foreach ($parents as $pid => $pname) {
|
|
$by_parent[$pid] = ['name' => $pname, 'children' => []];
|
|
}
|
|
|
|
foreach ($all_cats as $cat) {
|
|
if (in_array($cat->term_id, self::EXCLUDED_CATS, true)) {
|
|
continue;
|
|
}
|
|
if ($cat->parent == 0) {
|
|
continue; // parents handled above
|
|
}
|
|
$p = $cat->parent;
|
|
if (!isset($by_parent[$p])) {
|
|
continue;
|
|
}
|
|
$by_parent[$p]['children'][$cat->term_id] = $cat->name;
|
|
}
|
|
|
|
return $by_parent;
|
|
}
|
|
|
|
/**
|
|
* Flat list of all eligible category IDs (excluding EXCLUDED_CATS).
|
|
*/
|
|
public static function get_all_eligible_cat_ids(): array {
|
|
$groups = self::get_eligible_categories();
|
|
$ids = [];
|
|
foreach ($groups as $pid => $group) {
|
|
$ids[] = $pid;
|
|
foreach ($group['children'] as $cid => $name) {
|
|
$ids[] = $cid;
|
|
}
|
|
}
|
|
return $ids;
|
|
}
|
|
|
|
/**
|
|
* Get the window type for a given category.
|
|
*/
|
|
public static function get_window_type(int $cat_id): string {
|
|
return self::SPECIAL_WINDOW_TYPES[$cat_id] ?? self::DEFAULT_WINDOW_TYPE;
|
|
}
|
|
|
|
/**
|
|
* Get posts grouped by category for the given year-month (e.g. "2026-03").
|
|
*
|
|
* @param string $year_month Format: YYYY-MM
|
|
* @return array ['cat_id' => [post_data, ...], ...]
|
|
*/
|
|
public function get_posts_for_month(string $year_month): array {
|
|
$month_start = strtotime($year_month . '-01 00:00:00');
|
|
if (!$month_start) {
|
|
return [];
|
|
}
|
|
$month_end = strtotime('last day of ' . $year_month . ' 23:59:59');
|
|
|
|
$result = [];
|
|
foreach (self::get_all_eligible_cat_ids() as $cat_id) {
|
|
$window_type = self::get_window_type($cat_id);
|
|
$posts = $this->query_category($cat_id, $window_type, $month_start, $month_end);
|
|
if (!empty($posts)) {
|
|
$result[$cat_id] = $posts;
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Query a single category with the appropriate window expression.
|
|
*/
|
|
private function query_category(int $cat_id, string $window_type, int $month_start, int $month_end): array {
|
|
global $wpdb;
|
|
|
|
// Build window SQL expressions (UNIX_TIMESTAMP values for comparison)
|
|
switch ($window_type) {
|
|
case 'datetime_to_fin':
|
|
$start_expr = $this->sql_datetime_start();
|
|
$end_expr = $this->sql_datetime_to_fin_end();
|
|
$order_expr = "COALESCE(NULLIF(pm_dt.meta_value, ''), p.post_date)";
|
|
break;
|
|
|
|
case 'debut_minus35_to_fin':
|
|
$start_expr = $this->sql_debut_minus35_start();
|
|
$end_expr = $this->sql_debut_to_fin_end();
|
|
$order_expr = "COALESCE(NULLIF(pm_deb.meta_value, ''), p.post_date)";
|
|
break;
|
|
|
|
case 'datetime_plus3m':
|
|
$start_expr = $this->sql_datetime_start();
|
|
$end_expr = $this->sql_datetime_plus3m_end();
|
|
$order_expr = "COALESCE(NULLIF(pm_dt.meta_value, ''), p.post_date)";
|
|
break;
|
|
|
|
case 'datetime_plus35d':
|
|
$start_expr = $this->sql_datetime_start();
|
|
$end_expr = $this->sql_datetime_plus35d_end();
|
|
$order_expr = "COALESCE(NULLIF(pm_dt.meta_value, ''), p.post_date)";
|
|
break;
|
|
|
|
default:
|
|
return [];
|
|
}
|
|
|
|
$sql = $wpdb->prepare(
|
|
"SELECT DISTINCT p.ID, p.post_title, p.post_date
|
|
FROM {$wpdb->posts} p
|
|
INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.ID
|
|
INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
|
|
AND tt.taxonomy = 'category' AND tt.term_id = %d
|
|
LEFT JOIN {$wpdb->postmeta} pm_dt ON pm_dt.post_id = p.ID AND pm_dt.meta_key = 'datetime'
|
|
LEFT JOIN {$wpdb->postmeta} pm_deb ON pm_deb.post_id = p.ID AND pm_deb.meta_key = 'date_de_debut'
|
|
LEFT JOIN {$wpdb->postmeta} pm_fin ON pm_fin.post_id = p.ID AND pm_fin.meta_key = 'date_de_fin'
|
|
WHERE p.post_type = 'post'
|
|
AND p.post_status = 'publish'
|
|
AND {$start_expr} <= %d
|
|
AND {$end_expr} >= %d
|
|
ORDER BY {$order_expr} ASC",
|
|
$cat_id,
|
|
$month_end,
|
|
$month_start
|
|
);
|
|
|
|
$rows = $wpdb->get_results($sql);
|
|
if (!$rows) {
|
|
return [];
|
|
}
|
|
|
|
$posts = [];
|
|
foreach ($rows as $row) {
|
|
$posts[] = $this->build_post_data((int) $row->ID, $row->post_title, $row->post_date, $month_start, $month_end);
|
|
}
|
|
return $posts;
|
|
}
|
|
|
|
// -------------------------------------------------------------------------
|
|
// SQL window expression helpers (return raw SQL strings, not prepared)
|
|
// -------------------------------------------------------------------------
|
|
|
|
/**
|
|
* UNIX_TIMESTAMP of: datetime meta if valid, else post_date
|
|
*/
|
|
private function sql_datetime_start(): string {
|
|
return "UNIX_TIMESTAMP(CASE
|
|
WHEN pm_dt.meta_value IS NOT NULL
|
|
AND pm_dt.meta_value != ''
|
|
AND LEFT(pm_dt.meta_value, 4) != '0000'
|
|
THEN pm_dt.meta_value
|
|
ELSE p.post_date
|
|
END)";
|
|
}
|
|
|
|
/**
|
|
* UNIX_TIMESTAMP of: date_de_fin if valid, else datetime if valid, else post_date
|
|
*/
|
|
private function sql_datetime_to_fin_end(): string {
|
|
return "UNIX_TIMESTAMP(CASE
|
|
WHEN pm_fin.meta_value IS NOT NULL
|
|
AND pm_fin.meta_value != ''
|
|
AND LEFT(pm_fin.meta_value, 4) != '0000'
|
|
THEN pm_fin.meta_value
|
|
WHEN pm_dt.meta_value IS NOT NULL
|
|
AND pm_dt.meta_value != ''
|
|
AND LEFT(pm_dt.meta_value, 4) != '0000'
|
|
THEN pm_dt.meta_value
|
|
ELSE p.post_date
|
|
END)";
|
|
}
|
|
|
|
/**
|
|
* UNIX_TIMESTAMP of (date_de_debut if valid, else post_date) minus 35 days (3024000 seconds)
|
|
*/
|
|
private function sql_debut_minus35_start(): string {
|
|
return "(UNIX_TIMESTAMP(CASE
|
|
WHEN pm_deb.meta_value IS NOT NULL
|
|
AND pm_deb.meta_value != ''
|
|
AND LEFT(pm_deb.meta_value, 4) != '0000'
|
|
THEN pm_deb.meta_value
|
|
ELSE p.post_date
|
|
END) - 3024000)";
|
|
}
|
|
|
|
/**
|
|
* UNIX_TIMESTAMP of: date_de_fin if valid, else date_de_debut if valid, else post_date
|
|
*/
|
|
private function sql_debut_to_fin_end(): string {
|
|
return "UNIX_TIMESTAMP(CASE
|
|
WHEN pm_fin.meta_value IS NOT NULL
|
|
AND pm_fin.meta_value != ''
|
|
AND LEFT(pm_fin.meta_value, 4) != '0000'
|
|
THEN pm_fin.meta_value
|
|
WHEN pm_deb.meta_value IS NOT NULL
|
|
AND pm_deb.meta_value != ''
|
|
AND LEFT(pm_deb.meta_value, 4) != '0000'
|
|
THEN pm_deb.meta_value
|
|
ELSE p.post_date
|
|
END)";
|
|
}
|
|
|
|
/**
|
|
* UNIX_TIMESTAMP of (datetime if valid, else post_date) + 3 months
|
|
*/
|
|
private function sql_datetime_plus3m_end(): string {
|
|
return "UNIX_TIMESTAMP(DATE_ADD(CASE
|
|
WHEN pm_dt.meta_value IS NOT NULL
|
|
AND pm_dt.meta_value != ''
|
|
AND LEFT(pm_dt.meta_value, 4) != '0000'
|
|
THEN pm_dt.meta_value
|
|
ELSE p.post_date
|
|
END, INTERVAL 3 MONTH))";
|
|
}
|
|
|
|
/**
|
|
* UNIX_TIMESTAMP of (datetime if valid, else post_date) + 35 days
|
|
*/
|
|
private function sql_datetime_plus35d_end(): string {
|
|
return "(UNIX_TIMESTAMP(CASE
|
|
WHEN pm_dt.meta_value IS NOT NULL
|
|
AND pm_dt.meta_value != ''
|
|
AND LEFT(pm_dt.meta_value, 4) != '0000'
|
|
THEN pm_dt.meta_value
|
|
ELSE p.post_date
|
|
END) + 3024000)";
|
|
}
|
|
|
|
// -------------------------------------------------------------------------
|
|
// Post data builder
|
|
// -------------------------------------------------------------------------
|
|
|
|
private function build_post_data(int $post_id, string $post_title, string $post_date, int $month_start = 0, int $month_end = 0): array {
|
|
$data = [
|
|
'id' => $post_id,
|
|
'title' => $post_title,
|
|
'permalink' => get_permalink($post_id),
|
|
'datetime' => get_post_meta($post_id, 'datetime', true) ?: '',
|
|
'date_debut' => get_post_meta($post_id, 'date_de_debut', true) ?: '',
|
|
'date_fin' => get_post_meta($post_id, 'date_de_fin', true) ?: '',
|
|
'post_date' => $post_date,
|
|
'membres' => $this->get_post_membres($post_id),
|
|
'autrepersonnes' => get_post_meta($post_id, 'autrepersonnes', true) ?: '',
|
|
'seances' => [],
|
|
];
|
|
|
|
// For seminars, fetch séances within the month window
|
|
if ($month_start && $month_end && has_category(THALIM_NL_CAT_SEMINAIRES, $post_id)) {
|
|
$data['seances'] = $this->get_seances_in_window($post_id, $month_start, $month_end);
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
/**
|
|
* Fetch séances linked to a seminar that fall within the given time window.
|
|
*/
|
|
private function get_seances_in_window(int $seminar_id, int $month_start, int $month_end): array {
|
|
$seance_ids = get_post_meta($seminar_id, 'seances', false);
|
|
if (empty($seance_ids)) {
|
|
return [];
|
|
}
|
|
|
|
$seances = [];
|
|
foreach ($seance_ids as $sid) {
|
|
$sid = (int) $sid;
|
|
$post = get_post($sid);
|
|
if (!$post || $post->post_status !== 'publish') {
|
|
continue;
|
|
}
|
|
|
|
$raw_debut = get_post_meta($sid, 'date_de_debut', true) ?: '';
|
|
$ts = $raw_debut ? strtotime($raw_debut) : false;
|
|
if (!$ts || $ts < $month_start || $ts > $month_end) {
|
|
continue;
|
|
}
|
|
|
|
$seances[] = [
|
|
'id' => $sid,
|
|
'title' => get_the_title($sid),
|
|
'date_debut' => $raw_debut,
|
|
'date_fin' => get_post_meta($sid, 'date_de_fin', true) ?: '',
|
|
'heure_de_debut' => substr(get_post_meta($sid, 'heure_de_debut', true) ?: '', 0, 5),
|
|
'heure_de_fin' => substr(get_post_meta($sid, 'heure_de_fin', true) ?: '', 0, 5),
|
|
'lieu' => get_post_meta($sid, 'lieu', true) ?: '',
|
|
];
|
|
}
|
|
|
|
// Sort by date_de_debut ascending
|
|
usort($seances, function ($a, $b) {
|
|
return strcmp($a['date_debut'], $b['date_debut']);
|
|
});
|
|
|
|
return $seances;
|
|
}
|
|
|
|
/**
|
|
* Resolve 'membres' postmeta rows to display names.
|
|
*
|
|
* @return string[]
|
|
*/
|
|
public function get_post_membres(int $post_id): array {
|
|
$uids = get_post_meta($post_id, 'membres', false);
|
|
$names = [];
|
|
foreach ($uids as $uid) {
|
|
$user = get_userdata((int) $uid);
|
|
if ($user) {
|
|
$names[] = $user->display_name;
|
|
}
|
|
}
|
|
return $names;
|
|
}
|
|
}
|