// =========================================================================
// SHORTCODE: Frontpage On This Day Widget (100% width, single column, white headings)
// Usage: [frontpage_on_this_day]
// =========================================================================
add_shortcode('frontpage_on_this_day', 'otg_render_frontpage_on_this_day');
function otg_render_frontpage_on_this_day() {
global $wpdb;
$events_table = 'otg_rangers_events';
$transactions_table = 'otg_transactions';
$players_table = 'otg_players';
$current_md = date('m-d'); // e.g., '08-06'
$current_year = date('Y');
// 1. Query general/historical events (excluding current year, capped at 5)[cite: 5]
$events_sql = $wpdb->prepare(
"SELECT * FROM {$events_table}
WHERE DATE_FORMAT(event_date, '%%m-%%d') = %s
AND YEAR(event_date) != %d
ORDER BY event_date ASC LIMIT 5",
$current_md,
$current_year
);
$events = $wpdb->get_results($events_sql);
// 2. Query game events played on this month/day across all years[cite: 5]
$games_sql = $wpdb->prepare(
"SELECT * FROM {$events_table}
WHERE DATE_FORMAT(event_date, '%%m-%%d') = %s
AND (LOWER(category) = 'game' OR LOWER(category) = 'playoffs' OR related_game_id IS NOT NULL)
ORDER BY event_date DESC",
$current_md
);
$game_events = $wpdb->get_results($games_sql);
// 3. Query specifically for Birthdays matching today's month/day from both otg_rangers_events and otg_players
$birthday_events = [];
// 3a. Get birthdays from events table
$event_birthdays_sql = $wpdb->prepare(
"SELECT title, event_date as bday_date FROM {$events_table}
WHERE DATE_FORMAT(event_date, '%%m-%%d') = %s
AND LOWER(category) = 'birthday'
ORDER BY event_date ASC",
$current_md
);
$event_bdays = $wpdb->get_results($event_birthdays_sql);
if (!empty($event_bdays)) {
foreach ($event_bdays as $eb) {
$birthday_events[] = [
'name' => $eb->title,
'date' => $eb->bday_date
];
}
}
// 3b. Get player birthdays from otg_players table where played = 'y'
$player_birthdays_sql = $wpdb->prepare(
"SELECT Name, Birthday as bday_date FROM {$players_table}
WHERE played = 'y'
AND DATE_FORMAT(Birthday, '%%m-%%d') = %s
ORDER BY Birthday ASC",
$current_md
);
$player_bdays = $wpdb->get_results($player_birthdays_sql);
if (!empty($player_bdays)) {
foreach ($player_bdays as $pb) {
$birthday_events[] = [
'name' => $pb->Name,
'date' => $pb->bday_date
];
}
}
// Sort combined birthdays by birth year ascending
usort($birthday_events, function($a, $b) {
return strcmp($a['date'], $b['date']);
});
// 4. Query up to the top 5 most recent transactions excluding the current date[cite: 5]
$transactions_sql = $wpdb->prepare(
"SELECT * FROM {$transactions_table}
WHERE DATE_FORMAT(Date, '%%m-%%d') = %s
AND YEAR(Date) != %d
ORDER BY Date DESC LIMIT 5",
$current_md,
$current_year
);
$transactions = $wpdb->get_results($transactions_sql);
// Build container matching Active Injuries styling/padding (20px) and width set to 100%[cite: 5]
$html = '
';
// Widget Header matching "ACTIVE INDUSTRIES" layout/size: "ON THIS DAY: AUGUST 6"[cite: 5]
$html .= '
ON THIS DAY: ' . strtoupper(date('F j')) . '
';
// --- SECTION A: Overall Game Record & Last 5 Games on this Date ---
if (!empty($game_events)) {
$html .= '
';
$html .= '
Games
';
$html .= '
Record & Last Games on ' . date('F j') . ':
';
$last_five_games = array_slice($game_events, 0, 5);
$html .= '
';
foreach ($last_five_games as $g) {
$g_year = date('Y', strtotime($g->event_date));
$g_title = esc_html($g->title);
$html .= '
';
$html .= '' . $g_year . ': ' . $g_title;
$html .= '
';
}
$html .= '
';
$html .= '
';
} else {
$html .= '
No games played on this day.
';
}
// --- SECTION B: Birthdays ---
$html .= '
';
$html .= '
Birthdays
';
if (!empty($birthday_events)) {
$html .= '
';
foreach ($birthday_events as $bday) {
$b_year = !empty($bday['date']) ? date('Y', strtotime($bday['date'])) : '';
$b_title = esc_html($bday['name']);
$html .= '
';
$html .= '' . $b_title . '' . ($b_year ? ' (' . $b_year . ')' : '');
$html .= '
';
}
$html .= '
';
} else {
$html .= '
No birthdays today.
';
}
$html .= '
';
// --- SECTION C: Transactions ---
if (!empty($transactions)) {
$html .= '
';
$html .= '
Transactions
';
$html .= '
';
foreach ($transactions as $tx) {
$t_year = date('Y', strtotime($tx->Date));
$t_name = esc_html($tx->Name ?? 'Player');
$t_type = esc_html($tx->Transaction ?? 'Transaction');
$html .= '
';
$html .= '' . $t_year . ' - ' . $t_name . ': ' . $t_type;
$html .= '
';
}
$html .= '
';
$html .= '
';
}
// --- SECTION D: Passings & Historical Events (Capped at 5) ---
if (!empty($events)) {
$html .= '
';
foreach ($events as $event) {
$year = date('Y', strtotime($event->event_date));
$title = esc_html($event->title);
$excerpt = esc_html($event->excerpt);
// Handle YouTube Embed Responsively
$youtube_html = '';
if (!empty($event->you_tube_link)) {
$yt_url = trim($event->you_tube_link);
$embed_url = '';
if (strpos($yt_url, 'embed/') !== false) {
$embed_url = $yt_url;
} elseif (strpos($yt_url, 'youtu.be/') !== false) {
$parts = explode('youtu.be/', $yt_url);
$embed_url = 'https://www.youtube.com/embed/' . strtok($parts[1], '?');
} elseif (strpos($yt_url, 'watch?v=') !== false) {
parse_str(parse_url($yt_url, PHP_URL_QUERY), $queryParams);
if (!empty($queryParams['v'])) {
$embed_url = 'https://www.youtube.com/embed/' . $queryParams['v'];
}
}
if (!empty($embed_url)) {
$youtube_html = '
';
}
}
// Single column card box (with white/neutral accents)[cite: 5]
$html .= '
';
$html .= '
' . $year . '
';
$html .= '
' . $title . '
';
if (!empty($excerpt)) {
$html .= '
' . $excerpt . '
';
}
$html .= $youtube_html;
$html .= '
';
}
$html .= '
';
}
// Link at the bottom to go to the /on-this-day/ page[cite: 5]
$on_this_day_url = esc_url(home_url('/on-this-day/'));
$html .= '
';
$html .= '
';
return $html;
}