mirror of
https://github.com/wavelog/wavelog
synced 2026-08-13 18:41:16 -04:00
243 lines
8 KiB
PHP
243 lines
8 KiB
PHP
<?php
|
|
class Contesting_import_model extends CI_Model {
|
|
|
|
/**
|
|
* Returns historical contest QSO groups not yet linked to any contest session,
|
|
* scoped to the current user's stations. Groups by (COL_CONTEST_ID, station_id)
|
|
* and splits into separate sessions wherever consecutive QSOs are at least
|
|
* 72 hours apart and not within the same ISO calendar week (see
|
|
* _segment_qsos). QSOs without a valid COL_TIME_ON are excluded.
|
|
*
|
|
* @return array
|
|
*/
|
|
function get_legacy_contest_groups() {
|
|
$user_id = $this->session->userdata('user_id');
|
|
return $this->_fetch_legacy_groups_for_users([$user_id]);
|
|
}
|
|
|
|
/**
|
|
* Returns historical contest QSO groups for ALL users of this instance,
|
|
* not yet linked to any contest session.
|
|
*
|
|
* @return array
|
|
*/
|
|
function get_all_legacy_contest_groups() {
|
|
return $this->_fetch_legacy_groups_for_users([]);
|
|
}
|
|
|
|
/**
|
|
* Finds a contest entry by ADIF name. Returns 1 ("Other") if none found.
|
|
*
|
|
* @param string $adif_name
|
|
* @return int contest.id
|
|
*/
|
|
function ensure_contest_exists($adif_name) {
|
|
$query = $this->db->query(
|
|
"SELECT id FROM contest WHERE adifname = ? LIMIT 1",
|
|
[$adif_name]
|
|
);
|
|
if ($query->num_rows() > 0) {
|
|
return (int)$query->row()->id;
|
|
}
|
|
return 1; // "Other"
|
|
}
|
|
|
|
/**
|
|
* Imports a single legacy contest group for the current session user.
|
|
*
|
|
* @param string $adif_name
|
|
* @param int $station_id
|
|
* @param int $segment_start Unix timestamp of the segment's first QSO
|
|
* @return int Number of QSOs linked, 0 on ownership failure or unknown segment
|
|
*/
|
|
function import_legacy_contest_group($adif_name, $station_id, $segment_start) {
|
|
$user_id = $this->session->userdata('user_id');
|
|
return $this->_do_import_legacy_group($adif_name, $station_id, $segment_start, $user_id);
|
|
}
|
|
|
|
/**
|
|
* Imports a single legacy contest group for an explicitly specified user.
|
|
* Used by the all-users admin import.
|
|
*
|
|
* @param string $adif_name
|
|
* @param int $station_id
|
|
* @param int $segment_start Unix timestamp of the segment's first QSO
|
|
* @param int $user_id
|
|
* @return int Number of QSOs linked, 0 on ownership failure or unknown segment
|
|
*/
|
|
function import_legacy_contest_group_as_user($adif_name, $station_id, $segment_start, $user_id) {
|
|
return $this->_do_import_legacy_group($adif_name, $station_id, $segment_start, (int)$user_id);
|
|
}
|
|
|
|
// -------------------------------------------------------------------------
|
|
// Private helpers
|
|
// -------------------------------------------------------------------------
|
|
|
|
private function _fetch_legacy_groups_for_users(array $user_ids) {
|
|
$table = $this->config->item('table_name');
|
|
|
|
$user_filter = empty($user_ids)
|
|
? ""
|
|
: "AND t.station_id IN (SELECT station_id FROM station_profile WHERE user_id = ?)";
|
|
|
|
$sql = "SELECT
|
|
t.COL_CONTEST_ID AS adif_name,
|
|
t.station_id,
|
|
t.COL_TIME_ON,
|
|
c.id AS contest_table_id,
|
|
COALESCE(c.name, t.COL_CONTEST_ID) AS contest_name,
|
|
sp.station_callsign,
|
|
sp.user_id AS owner_user_id
|
|
FROM {$table} t
|
|
LEFT JOIN contest c ON c.adifname = t.COL_CONTEST_ID
|
|
LEFT JOIN station_profile sp ON sp.station_id = t.station_id
|
|
LEFT JOIN contest_qsos cq ON cq.qso_id = t.COL_PRIMARY_KEY
|
|
WHERE t.COL_CONTEST_ID IS NOT NULL
|
|
AND t.COL_CONTEST_ID != ''
|
|
AND t.COL_TIME_ON IS NOT NULL
|
|
AND cq.id IS NULL
|
|
{$user_filter}
|
|
ORDER BY t.COL_CONTEST_ID, t.station_id, t.COL_TIME_ON";
|
|
|
|
$bindings = empty($user_ids) ? [] : $user_ids;
|
|
$qsos = $this->db->query($sql, $bindings)->result_array();
|
|
|
|
// Group QSOs by (adif_name, station_id), then split each group into
|
|
// time segments so that preview rows match the sessions created on import
|
|
$grouped = [];
|
|
foreach ($qsos as $qso) {
|
|
$grouped[$qso['adif_name'] . '|' . $qso['station_id']][] = $qso;
|
|
}
|
|
|
|
$result = [];
|
|
foreach ($grouped as $group_qsos) {
|
|
foreach ($this->_segment_qsos($group_qsos) as $segment) {
|
|
$first = $segment[0];
|
|
$last = end($segment);
|
|
$result[] = [
|
|
'adif_name' => $first['adif_name'],
|
|
'station_id' => $first['station_id'],
|
|
'segment_start' => strtotime($first['COL_TIME_ON']),
|
|
'contest_year' => (int)date('Y', strtotime($first['COL_TIME_ON'])),
|
|
'time_start' => $first['COL_TIME_ON'],
|
|
'time_end' => $last['COL_TIME_ON'],
|
|
'qso_count' => count($segment),
|
|
'contest_table_id' => $first['contest_table_id'],
|
|
'contest_name' => $first['contest_name'],
|
|
'station_callsign' => $first['station_callsign'],
|
|
'owner_user_id' => $first['owner_user_id'],
|
|
];
|
|
}
|
|
}
|
|
|
|
usort($result, function ($a, $b) {
|
|
return $b['segment_start'] <=> $a['segment_start'];
|
|
});
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Splits a time-ordered list of QSOs into segments. A new segment starts
|
|
* whenever two consecutive QSOs are at least 72h apart — unless both QSOs
|
|
* fall into the same ISO calendar week (Mon-Sun). This keeps week-long
|
|
* activity events with sparse QSOs (e.g. one QSO on Monday, one on Friday)
|
|
* in a single session, while weekly series (same weekday every week) always
|
|
* cross a week boundary and are still split.
|
|
*
|
|
* @param array $qsos QSO rows ordered by COL_TIME_ON ascending
|
|
* @return array Array of segments, each an array of QSO rows
|
|
*/
|
|
private function _segment_qsos(array $qsos) {
|
|
$segments = [];
|
|
$current = [];
|
|
$prev_ts = null;
|
|
|
|
foreach ($qsos as $qso) {
|
|
$ts = strtotime($qso['COL_TIME_ON']);
|
|
if ($prev_ts !== null && ($ts - $prev_ts) >= 72 * 3600 && date('oW', $ts) !== date('oW', $prev_ts)) {
|
|
$segments[] = $current;
|
|
$current = [];
|
|
}
|
|
$current[] = $qso;
|
|
$prev_ts = $ts;
|
|
}
|
|
if (!empty($current)) {
|
|
$segments[] = $current;
|
|
}
|
|
return $segments;
|
|
}
|
|
|
|
private function _do_import_legacy_group($adif_name, $station_id, $segment_start, $user_id) {
|
|
$table = $this->config->item('table_name');
|
|
|
|
// Verify station belongs to the specified user
|
|
$check = $this->db->query(
|
|
"SELECT station_id FROM station_profile WHERE station_id = ? AND user_id = ? LIMIT 1",
|
|
[$station_id, $user_id]
|
|
);
|
|
if ($check->num_rows() === 0) {
|
|
return 0;
|
|
}
|
|
|
|
$contest_id = $this->ensure_contest_exists($adif_name);
|
|
$is_other = ($contest_id === 1 && $adif_name !== 'Other');
|
|
|
|
// Fetch all unlinked QSOs for this contest/station combination
|
|
$all_qsos = $this->db->query(
|
|
"SELECT t.COL_PRIMARY_KEY, t.COL_TIME_ON
|
|
FROM {$table} t
|
|
LEFT JOIN contest_qsos cq ON cq.qso_id = t.COL_PRIMARY_KEY
|
|
WHERE t.COL_CONTEST_ID = ?
|
|
AND t.station_id = ?
|
|
AND t.COL_TIME_ON IS NOT NULL
|
|
AND cq.id IS NULL
|
|
ORDER BY t.COL_TIME_ON ASC",
|
|
[$adif_name, $station_id]
|
|
)->result_array();
|
|
|
|
if (empty($all_qsos)) {
|
|
return 0;
|
|
}
|
|
|
|
// Re-derive the segments and pick the one starting at the requested time
|
|
$qsos = null;
|
|
foreach ($this->_segment_qsos($all_qsos) as $segment) {
|
|
if (strtotime($segment[0]['COL_TIME_ON']) === (int)$segment_start) {
|
|
$qsos = $segment;
|
|
break;
|
|
}
|
|
}
|
|
if ($qsos === null) {
|
|
return 0;
|
|
}
|
|
|
|
$year = (int)date('Y', strtotime($qsos[0]['COL_TIME_ON']));
|
|
$time_start = date('Y-m-d H:i:s', strtotime($qsos[0]['COL_TIME_ON']) - 3600);
|
|
$time_end = date('Y-m-d H:i:s', strtotime(end($qsos)['COL_TIME_ON']) + 3600);
|
|
|
|
if ($is_other) {
|
|
$comment = sprintf(__("Imported from logbook\n(ADIF: %s, Year: %d) [Original: %s]"), $adif_name, $year, $adif_name);
|
|
} else {
|
|
$comment = sprintf(__("Imported from logbook\n(ADIF: %s, Year: %d)"), $adif_name, $year);
|
|
}
|
|
|
|
$settings = json_encode(['exchangetype' => 'Exchange', 'copyexchangeto' => '', 'exchangefields' => ['exchange']]);
|
|
|
|
$this->db->query(
|
|
"INSERT INTO contest_session (user_id, contest_adif_id, time_start, time_end, station_id, comment, settings)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?)",
|
|
[$user_id, $contest_id, $time_start, $time_end, $station_id, $comment, $settings]
|
|
);
|
|
$session_id = (int)$this->db->insert_id();
|
|
|
|
$linked = 0;
|
|
foreach ($qsos as $qso) {
|
|
$this->db->query(
|
|
"INSERT INTO contest_qsos (contest_session_id, qso_id) VALUES (?, ?)",
|
|
[$session_id, $qso['COL_PRIMARY_KEY']]
|
|
);
|
|
$linked++;
|
|
}
|
|
return $linked;
|
|
}
|
|
}
|