mirror of
https://github.com/wavelog/wavelog
synced 2026-08-13 18:41:16 -04:00
265 lines
8.8 KiB
PHP
265 lines
8.8 KiB
PHP
<?php
|
|
|
|
class Sota extends CI_Model {
|
|
|
|
function search_refs($term) {
|
|
$json = [];
|
|
$ref = strtoupper(trim((string) $term));
|
|
if ($ref === '') {
|
|
return $json;
|
|
}
|
|
|
|
$this->db->select('reference');
|
|
$this->db->like('reference', $ref, 'after');
|
|
$this->db->order_by('reference', 'asc');
|
|
$this->db->limit(100);
|
|
$q = $this->db->get('sota_directory');
|
|
|
|
foreach ($q->result() as $row) {
|
|
$json[] = ['name' => $row->reference];
|
|
}
|
|
|
|
return $json;
|
|
}
|
|
|
|
function get_all() {
|
|
$this->load->model('logbooks_model');
|
|
$logbooks_locations_array = $this->logbooks_model->list_logbook_relationships($this->session->userdata('active_station_logbook'));
|
|
|
|
if (empty($logbooks_locations_array) || $logbooks_locations_array[0] === -1) {
|
|
return [];
|
|
}
|
|
|
|
$location_list = "'" . implode("','", $logbooks_locations_array) . "'";
|
|
|
|
// Default filter mirrors the SOTA page's checkbox defaults, so the
|
|
// initial server-rendered table matches what the AJAX endpoints return
|
|
// (awards/sota_table + awards/sota_map) before "Apply Filters" is used.
|
|
$postdata = [
|
|
'qsl' => 1,
|
|
'lotw' => 1,
|
|
'eqsl' => null,
|
|
'qrz' => null,
|
|
'clublog' => null,
|
|
'worked' => 1,
|
|
'confirmed' => 1,
|
|
'band' => 'All',
|
|
'mode' => 'All',
|
|
'dateFrom' => null,
|
|
'dateTo' => null,
|
|
];
|
|
|
|
return $this->get_qso_list($postdata, $location_list);
|
|
}
|
|
|
|
/*
|
|
* Cheap content signature for the plotted directory rows. Changes only
|
|
* when summits are added/removed/renamed/re-activated — i.e. only after
|
|
* the update_sota cron runs. Powers the weak ETag so 304s short-circuit
|
|
* before any row is touched.
|
|
*/
|
|
function directory_signature() {
|
|
$sql = "SELECT COUNT(*) AS c,
|
|
COALESCE(MAX(reference), '') AS mr,
|
|
COALESCE(MAX(valid_till), '') AS vt,
|
|
COALESCE(MAX(last_activated), '') AS la
|
|
FROM sota_directory
|
|
WHERE lat IS NOT NULL AND lon IS NOT NULL";
|
|
$r = $this->db->query($sql)->row();
|
|
return 'sota-dir-' . $r->c . '-' . substr(md5($r->mr . '|' . $r->vt . '|' . $r->la), 0, 16);
|
|
}
|
|
|
|
/*
|
|
* Stream the plotted directory as a JSON array straight to stdout. Rows
|
|
* are fetched one at a time via unbuffered_row() so peak memory stays
|
|
* flat regardless of directory size (~140k SOTA summits). Body shape is
|
|
* identical to the previous array-returning get_directory() — frontend
|
|
* parsing is unchanged.
|
|
*/
|
|
function stream_directory_json() {
|
|
$sql = "SELECT reference, name, lat, lon, altitude, valid_from, valid_till
|
|
FROM sota_directory
|
|
WHERE lat IS NOT NULL AND lon IS NOT NULL
|
|
ORDER BY reference";
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
echo '[';
|
|
$first = true;
|
|
while ($row = $query->unbuffered_row('object')) {
|
|
echo ($first ? '' : ','), json_encode([
|
|
'reference' => $row->reference,
|
|
'name' => $row->name,
|
|
'lat' => (float) $row->lat,
|
|
'lon' => (float) $row->lon,
|
|
'altitude' => $row->altitude,
|
|
'inactive' => $this->_inactive($row->valid_from, $row->valid_till),
|
|
'valid_from' => $row->valid_from,
|
|
'valid_till' => $row->valid_till,
|
|
]);
|
|
$first = false;
|
|
}
|
|
echo ']';
|
|
}
|
|
|
|
function get_map_data($postdata, $location_list) {
|
|
$bindings = [];
|
|
|
|
$sql = "SELECT thcv.COL_SOTA_REF AS reference,
|
|
MAX(sd.lat) AS lat, MAX(sd.lon) AS lon, MAX(sd.name) AS name, MAX(sd.altitude) AS altitude,
|
|
MAX(sd.valid_from) AS valid_from, MAX(sd.valid_till) AS valid_till,
|
|
MAX(CASE WHEN thcv.COL_QSL_RCVD = 'Y' THEN 1 ELSE 0 END) AS qsl,
|
|
MAX(CASE WHEN thcv.COL_LOTW_QSL_RCVD = 'Y' THEN 1 ELSE 0 END) AS lotw,
|
|
MAX(CASE WHEN thcv.COL_EQSL_QSL_RCVD = 'Y' THEN 1 ELSE 0 END) AS eqsl,
|
|
MAX(CASE WHEN thcv.COL_QRZCOM_QSO_DOWNLOAD_STATUS = 'Y' THEN 1 ELSE 0 END) AS qrz,
|
|
MAX(CASE WHEN thcv.COL_CLUBLOG_QSO_DOWNLOAD_STATUS = 'Y' THEN 1 ELSE 0 END) AS clublog
|
|
FROM " . $this->config->item('table_name') . " thcv
|
|
LEFT JOIN sota_directory sd ON sd.reference = thcv.COL_SOTA_REF
|
|
WHERE thcv.station_id IN (" . $location_list . ")
|
|
AND thcv.COL_SOTA_REF IS NOT NULL
|
|
AND thcv.COL_SOTA_REF <> ''";
|
|
|
|
$band = $postdata['band'] ?? 'All';
|
|
if ($band == 'All') {
|
|
$sql .= " and (thcv.col_prop_mode !='SAT' or thcv.col_prop_mode is NULL)";
|
|
}
|
|
else if ($band == 'SAT') {
|
|
$sql .= " and thcv.col_prop_mode = ?";
|
|
$bindings[] = $band;
|
|
} else {
|
|
$sql .= " AND thcv.COL_BAND = ?";
|
|
$sql .= " and (thcv.col_prop_mode !='SAT' or thcv.col_prop_mode is NULL)";
|
|
$bindings[] = $band;
|
|
}
|
|
|
|
$mode = $postdata['mode'] ?? 'All';
|
|
if ($mode !== 'All' && $mode !== '') {
|
|
$sql .= " AND (thcv.COL_MODE = ? OR thcv.COL_SUBMODE = ?)";
|
|
$bindings[] = $mode;
|
|
$bindings[] = $mode;
|
|
}
|
|
|
|
if (!empty($postdata['dateFrom'])) {
|
|
$sql .= " AND thcv.COL_TIME_ON >= ?";
|
|
$bindings[] = $postdata['dateFrom'] . ' 00:00:00';
|
|
}
|
|
if (!empty($postdata['dateTo'])) {
|
|
$sql .= " AND thcv.COL_TIME_ON <= ?";
|
|
$bindings[] = $postdata['dateTo'] . ' 23:59:59';
|
|
}
|
|
|
|
$sql .= " GROUP BY thcv.COL_SOTA_REF";
|
|
$sql .= " ORDER BY thcv.COL_SOTA_REF ASC";
|
|
|
|
$query = $this->db->query($sql, $bindings);
|
|
|
|
$result = [];
|
|
foreach ($query->result() as $row) {
|
|
$isConfirmed = false;
|
|
if (($postdata['qsl'] ?? 0) == 1 && $row->qsl) $isConfirmed = true;
|
|
elseif (($postdata['lotw'] ?? 0) == 1 && $row->lotw) $isConfirmed = true;
|
|
elseif (($postdata['eqsl'] ?? 0) == 1 && $row->eqsl) $isConfirmed = true;
|
|
elseif (($postdata['qrz'] ?? 0) == 1 && $row->qrz) $isConfirmed = true;
|
|
elseif (($postdata['clublog'] ?? 0) == 1 && $row->clublog) $isConfirmed = true;
|
|
|
|
$status = $isConfirmed ? 'C' : 'W';
|
|
|
|
if ($status == 'C' && ($postdata['confirmed'] ?? null) != 1) continue;
|
|
if ($status == 'W' && ($postdata['worked'] ?? null) != 1) continue;
|
|
|
|
$result[] = [
|
|
'reference' => $row->reference,
|
|
'name' => $row->name,
|
|
'lat' => $row->lat !== null ? (float) $row->lat : null,
|
|
'lon' => $row->lon !== null ? (float) $row->lon : null,
|
|
'altitude' => $row->altitude !== null ? (int) $row->altitude : null,
|
|
'inactive' => $this->_inactive($row->valid_from, $row->valid_till),
|
|
'status' => $status,
|
|
];
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
function get_qso_list($postdata, $location_list) {
|
|
$bindings = [];
|
|
|
|
$sql = "SELECT thcv.COL_SOTA_REF, thcv.COL_TIME_ON, thcv.COL_CALL, thcv.COL_BAND,
|
|
thcv.COL_SAT_NAME, thcv.COL_RST_SENT, thcv.COL_RST_RCVD, thcv.COL_PRIMARY_KEY,
|
|
CASE WHEN thcv.COL_QSL_RCVD = 'Y' THEN 1 ELSE 0 END AS qsl,
|
|
CASE WHEN thcv.COL_LOTW_QSL_RCVD = 'Y' THEN 1 ELSE 0 END AS lotw,
|
|
CASE WHEN thcv.COL_EQSL_QSL_RCVD = 'Y' THEN 1 ELSE 0 END AS eqsl,
|
|
CASE WHEN thcv.COL_QRZCOM_QSO_DOWNLOAD_STATUS = 'Y' THEN 1 ELSE 0 END AS qrz,
|
|
CASE WHEN thcv.COL_CLUBLOG_QSO_DOWNLOAD_STATUS = 'Y' THEN 1 ELSE 0 END AS clublog
|
|
FROM " . $this->config->item('table_name') . " thcv
|
|
WHERE thcv.station_id IN (" . $location_list . ")
|
|
AND thcv.COL_SOTA_REF IS NOT NULL
|
|
AND thcv.COL_SOTA_REF <> ''";
|
|
|
|
$band = $postdata['band'] ?? 'All';
|
|
if ($band == 'All') {
|
|
$sql .= " and (thcv.col_prop_mode !='SAT' or thcv.col_prop_mode is NULL)";
|
|
}
|
|
else if ($band == 'SAT') {
|
|
$sql .= " and thcv.col_prop_mode = ?";
|
|
$bindings[] = $band;
|
|
} else {
|
|
$sql .= " AND thcv.COL_BAND = ?";
|
|
$sql .= " and (thcv.col_prop_mode !='SAT' or thcv.col_prop_mode is NULL)";
|
|
$bindings[] = $band;
|
|
}
|
|
|
|
$mode = $postdata['mode'] ?? 'All';
|
|
if ($mode !== 'All' && $mode !== '') {
|
|
$sql .= " AND (thcv.COL_MODE = ? OR thcv.COL_SUBMODE = ?)";
|
|
$bindings[] = $mode;
|
|
$bindings[] = $mode;
|
|
}
|
|
|
|
if (!empty($postdata['dateFrom'])) {
|
|
$sql .= " AND thcv.COL_TIME_ON >= ?";
|
|
$bindings[] = $postdata['dateFrom'] . ' 00:00:00';
|
|
}
|
|
if (!empty($postdata['dateTo'])) {
|
|
$sql .= " AND thcv.COL_TIME_ON <= ?";
|
|
$bindings[] = $postdata['dateTo'] . ' 23:59:59';
|
|
}
|
|
|
|
$sql .= " ORDER BY thcv.COL_SOTA_REF ASC, thcv.COL_TIME_ON ASC";
|
|
|
|
$query = $this->db->query($sql, $bindings);
|
|
|
|
$result = [];
|
|
foreach ($query->result() as $row) {
|
|
$isConfirmed = false;
|
|
if (($postdata['qsl'] ?? 0) == 1 && $row->qsl) $isConfirmed = true;
|
|
elseif (($postdata['lotw'] ?? 0) == 1 && $row->lotw) $isConfirmed = true;
|
|
elseif (($postdata['eqsl'] ?? 0) == 1 && $row->eqsl) $isConfirmed = true;
|
|
elseif (($postdata['qrz'] ?? 0) == 1 && $row->qrz) $isConfirmed = true;
|
|
elseif (($postdata['clublog'] ?? 0) == 1 && $row->clublog) $isConfirmed = true;
|
|
|
|
if ($isConfirmed && ($postdata['confirmed'] ?? null) != 1) continue;
|
|
if (!$isConfirmed && ($postdata['worked'] ?? null) != 1) continue;
|
|
|
|
$result[] = $row;
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
// A summit is inactive when today falls outside [valid_from, valid_till].
|
|
// A NULL bound means "no constraint" on that side, and the closing day
|
|
// (today == valid_till) still counts as active.
|
|
private function _inactive($valid_from, $valid_till) {
|
|
$today = date('Y-m-d');
|
|
if ($valid_from !== null && $today < $valid_from) {
|
|
return true;
|
|
}
|
|
if ($valid_till !== null && $today > $valid_till) {
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
|
|
?>
|