';
// Header Section
$output .= '
On This Day in Rangers History
';
// Inline CSS: Three columns layout with alternating row backgrounds and game type badge styling
$output .= '';
// Filter Form Layout
$output .= '
';
// Build Date Conditions for SQL Queries (Birthdays)
$date_conditions = "";
$date_params = [];
if (!empty($filter_month)) {
$date_conditions .= " AND MONTH(Birthday) = %d";
$date_params[] = intval($filter_month);
}
if (!empty($filter_day)) {
$date_conditions .= " AND DAY(Birthday) = %d";
$date_params[] = intval($filter_day);
}
if (!empty($filter_year)) {
$date_conditions .= " AND YEAR(Birthday) = %d";
$date_params[] = intval($filter_year);
}
// =========================================================================
// 2. DATASETS FETCHING
// =========================================================================
// --- A. BIRTHDAYS (Players, Coaches, GMs) ---
$players_sql = "SELECT ID as entity_id, Name, Birthday, 'player' as type FROM {$players_table} WHERE played = 'y' AND Birthday IS NOT NULL AND Birthday != '0000-00-00'" . $date_conditions . " ORDER BY Birthday ASC";
$players_bday = $wpdb->get_results(!empty($date_params) ? $wpdb->prepare($players_sql, $date_params) : $players_sql);
$coaches_sql = "SELECT CoachID as entity_id, Name, Birthday, 'coach' as type FROM {$coaches_table} WHERE Birthday IS NOT NULL AND Birthday != '0000-00-00'" . $date_conditions . " ORDER BY Birthday ASC";
$coaches_bday = $wpdb->get_results(!empty($date_params) ? $wpdb->prepare($coaches_sql, $date_params) : $coaches_sql);
$gms_sql = "SELECT g.id as entity_id, g.name as Name, p.Birthday, 'gm' as type FROM {$gms_table} g JOIN {$players_table} p ON g.player_id = p.ID WHERE p.Birthday IS NOT NULL AND p.Birthday != '0000-00-00'" . str_replace("Birthday", "p.Birthday", $date_conditions) . " ORDER BY p.Birthday ASC";
$gms_bday = $wpdb->get_results(!empty($date_params) ? $wpdb->prepare($gms_sql, $date_params) : $gms_sql);
$all_birthdays = array_merge($players_bday, $coaches_bday, $gms_bday);
// --- B. DEATHS ---
$death_conditions = "";
$death_params = [];
if (!empty($filter_month)) {
$death_conditions .= " AND MONTH(Death) = %d";
$death_params[] = intval($filter_month);
}
if (!empty($filter_day)) {
$death_conditions .= " AND DAY(Death) = %d";
$death_params[] = intval($filter_day);
}
if (!empty($filter_year)) {
$death_conditions .= " AND YEAR(Death) = %d";
$death_params[] = intval($filter_year);
}
$deaths_sql = "SELECT ID as entity_id, Name, Death FROM {$players_table} WHERE Death IS NOT NULL AND Death != '0000-00-00'" . $death_conditions . " ORDER BY Death ASC";
$deaths = $wpdb->get_results(!empty($death_params) ? $wpdb->prepare($deaths_sql, $death_params) : $deaths_sql);
// --- C. GAMES ---
$games_conditions = " WHERE game_type != 1 AND game_outcome != 'SCH' AND game_outcome IS NOT NULL";
$games_params = [];
if (!empty($filter_month)) {
$games_conditions .= " AND MONTH(game_date) = %d";
$games_params[] = intval($filter_month);
}
if (!empty($filter_day)) {
$games_conditions .= " AND DAY(game_date) = %d";
$games_params[] = intval($filter_day);
}
if (!empty($filter_year)) {
$games_conditions .= " AND YEAR(game_date) = %d";
$games_params[] = intval($filter_year);
}
$games_sql = "SELECT g.*, st.team AS opponent_name FROM {$games_table} g LEFT JOIN otg_standings_nhl st ON (st.abbreviation = g.opponent_abbr && st.season = g.season && st.abbreviation != 'NYR')" . $games_conditions . " ORDER BY g.game_date DESC";
$games = $wpdb->get_results(!empty($games_params) ? $wpdb->prepare($games_sql, $games_params) : $games_sql);
// --- D. TRANSACTIONS ---
$allowed_trans_types = ['Released', 'Retired', 'Signed', 'Acquired', 'Traded', 'Contract Terminated', 'Buyout'];
$placeholders_trans = implode(',', array_fill(0, count($allowed_trans_types), '%s'));
$trans_conditions = " WHERE Transaction IN ($placeholders_trans)";
$trans_params = $allowed_trans_types;
if (!empty($filter_month)) {
$trans_conditions .= " AND MONTH(Date) = %d";
$trans_params[] = intval($filter_month);
}
if (!empty($filter_day)) {
$trans_conditions .= " AND DAY(Date) = %d";
$trans_params[] = intval($filter_day);
}
if (!empty($filter_year)) {
$trans_conditions .= " AND YEAR(Date) = %d";
$trans_params[] = intval($filter_year);
}
$transactions_sql = "SELECT * FROM {$transactions_table}" . $trans_conditions . " ORDER BY Date DESC";
$transactions = $wpdb->get_results($wpdb->prepare($transactions_sql, $trans_params));
// --- E. MOMENTS IN HISTORY (otg_rangers_events schema) ---
$events_conditions = " WHERE event_date IS NOT NULL AND event_date != '0000-00-00'";
$events_params = [];
if (!empty($filter_month)) {
$events_conditions .= " AND MONTH(event_date) = %d";
$events_params[] = intval($filter_month);
}
if (!empty($filter_day)) {
$events_conditions .= " AND DAY(event_date) = %d";
$events_params[] = intval($filter_day);
}
if (!empty($filter_year)) {
$events_conditions .= " AND YEAR(event_date) = %d";
$events_params[] = intval($filter_year);
}
$events_sql = "SELECT e.*, p.Name as player_name FROM {$events_table} e LEFT JOIN {$players_table} p ON e.related_player_id = p.ID" . $events_conditions . " ORDER BY e.event_date DESC";
$events = $wpdb->get_results(!empty($events_params) ? $wpdb->prepare($events_sql, $events_params) : $events_sql);
// =========================================================================
// 3. HTML RENDER SECTIONS
// =========================================================================
$render_otg_block = function($title, $items_html) {
$html = '
';
$html .= '
' . esc_html($title) . '
';
$html .= $items_html;
$html .= '
';
return $html;
};
// --- COLUMN 1: Birthdays & Passings ---
$col1_content = '';
// 1. Birthdays Section
$bday_html = '';
if (empty($all_birthdays)) {
$bday_html = '
No birthdays found for this date.
';
} else {
$bday_html = '
';
foreach ($all_birthdays as $person) {
$profile_url = ($person->type === 'coach') ? esc_url('/coach-profile/?coach_id=' . intval($person->entity_id)) : (($person->type === 'gm') ? esc_url('/general-managers/') : esc_url('/profile/?player_id=' . intval($person->entity_id)));
$birth_full = !empty($person->Birthday) ? date('F j, Y', strtotime($person->Birthday)) : '';
$bday_html .= '- ';
$bday_html .= '' . esc_html(trim($person->Name)) . ' (' . ucfirst($person->type) . ')';
$bday_html .= '' . esc_html($birth_full) . '';
$bday_html .= '
';
}
$bday_html .= '
';
}
$col1_content .= $render_otg_block('Birthdays', $bday_html);
// 2. Deaths Section
$death_html = '';
if (empty($deaths)) {
$death_html = '
No recorded deaths for this date.
';
} else {
$death_html = '
';
foreach ($deaths as $person) {
$profile_url = esc_url('/profile/?player_id=' . intval($person->entity_id));
$death_full = !empty($person->Death) ? date('F j, Y', strtotime($person->Death)) : '';
$death_html .= '- ';
$death_html .= '' . esc_html(trim($person->Name)) . '';
$death_html .= '' . esc_html($death_full) . '';
$death_html .= '
';
}
$death_html .= '
';
}
$col1_content .= $render_otg_block('Passings', $death_html);
// --- COLUMN 2: Games History & Transactions ---
$col2_content = '';
// Calculate total W-L-OT for the loaded games dataset
$total_w = 0;
$total_l = 0;
$total_ot = 0;
foreach ($games as $g_item) {
$out = strtoupper(trim($g_item->game_outcome));
$ot_stat = strtoupper(trim($g_item->overtime_status ?? ''));
if ($out === 'W') {
$total_w++;
} elseif ($out === 'L' || $out === 'OTL') {
if ($ot_stat === 'OT' || $ot_stat === 'SO' || $out === 'OTL') {
$total_ot++;
} else {
$total_l++;
}
} elseif ($out === 'T') {
$total_l++; // Treating ties or adjusting as standard if needed, but W-L-OT typically counts OT/SO losses here. Let's keep standard tally.
}
}
// Alternatively, let's map standard logic precisely: W = wins, L = regulation losses, OT = OTL/OT/SO losses
$calc_w = 0;
$calc_l = 0;
$calc_ot = 0;
foreach ($games as $g_item) {
$out = strtoupper(trim($g_item->game_outcome));
$ot_stat = strtoupper(trim($g_item->overtime_status ?? ''));
if ($out === 'W') {
$calc_w++;
} elseif ($out === 'OTL' || $ot_stat === 'OT' || $ot_stat === 'SO') {
$calc_ot++;
} elseif ($out === 'L') {
$calc_l++;
} elseif ($out === 'T') {
// T can be categorized or counted depending on historical eras, let's count as L or separate if needed, or follow standard record format.
$calc_l++;
}
}
$record_summary_string = $calc_w . '-' . $calc_l . '-' . $calc_ot;
// 3. Games Section
$games_html = '';
if (empty($games)) {
$games_html = '
No games found for this date.
';
} else {
$games_html = '
';
}
// Header title with total W-L-OT right-justified on the same row
$games_header_html = '
';
$games_header_html .= 'Games History';
$games_header_html .= 'Record: ' . esc_html($record_summary_string) . '';
$games_header_html .= '
';
$col2_content .= '
' . $games_header_html . $games_html . '
';
// 4. Transactions Section
$trans_html = '';
if (empty($transactions)) {
$trans_html = '
No relevant transactions recorded for this date.
';
} else {
$trans_html = '
';
}
$col2_content .= $render_otg_block('Transactions', $trans_html);
// --- COLUMN 3: Moments in History ---
$events_html = '';
if (empty($events)) {
$events_html = '
No historical moments recorded for this date.
';
} else {
$events_html = '
';
foreach ($events as $ev) {
$ev_date = !empty($ev->event_date) ? date('F j, Y', strtotime($ev->event_date)) : '';
$ev_title = !empty($ev->title) ? $ev->title : '';
$ev_excerpt = !empty($ev->excerpt) ? $ev->excerpt : '';
$ev_cat = !empty($ev->category) ? $ev->category : '';
$youtube_link = !empty($ev->you_tube_link) ? trim($ev->you_tube_link) : '';
$relations_output = [];
if (!empty($ev->related_player_id)) {
$p_url = esc_url(home_url('/profile/?player_id=' . intval($ev->related_player_id)));
$p_label = !empty($ev->player_name) ? $ev->player_name : 'Related Player';
$relations_output[] = '
' . esc_html($p_label) . '';
}
if (!empty($ev->related_game_id)) {
$g_url = esc_url(home_url('/box/?game_id=' . intval($ev->related_game_id)));
$relations_output[] = '
Related Box Score';
}
$events_html .= '
';
$events_html .= '
';
$events_html .= '
' . esc_html($ev_title) . ' ' . esc_html($ev_cat) . '
';
$events_html .= '
' . esc_html($ev_date) . '';
$events_html .= '
';
if (!empty($ev_excerpt)) {
$events_html .= '
' . nl2br(esc_html($ev_excerpt)) . '
';
}
if (!empty($relations_output)) {
$events_html .= '
' . implode(' | ', $relations_output) . '
';
}
if (!empty($youtube_link)) {
$events_html .= '
';
if (shortcode_exists('embedpress')) {
$events_html .= do_shortcode('[embedpress]' . esc_url($youtube_link) . '[/embedpress]');
} else {
$events_html .= '
';
$events_html .= '';
$events_html .= '
';
}
$events_html .= '
';
}
$events_html .= '
';
}
$events_html .= '
';
}
$col3_content = $render_otg_block('Moments in History', $events_html);
// Assemble Three-Column Grid structure
$output .= '
';
$output .= '
' . $col1_content . '
';
$output .= '
' . $col2_content . '
';
$output .= '
' . $col3_content . '
';
$output .= '
';
$output .= '
