mirror of
https://github.com/wavelog/wavelog.git
synced 2026-03-23 10:47:16 +00:00
420 lines
10 KiB
PHP
420 lines
10 KiB
PHP
<?php
|
|
|
|
class Waip extends CI_Model {
|
|
|
|
// Award constants
|
|
private $DXCC_ITALY = '248';
|
|
private $DXCC_SARDINIA = '225';
|
|
private $VALID_BANDS = array('160M','80M','40M','30M','20M','17M','15M','12M','10M');
|
|
private $MODE_CATEGORIES = array('MIXED', 'PHONE', 'CW', 'DIGI');
|
|
private $AWARD_START_DATE = '1948-06-02';
|
|
|
|
// Italian provinces mapping (2-letter codes to names)
|
|
private $province_names = array(
|
|
'AG' => 'Agrigento',
|
|
'AL' => 'Alexandria',
|
|
'AN' => 'Ancona',
|
|
'AO' => 'Aosta',
|
|
'AP' => 'Ascoli Piceno',
|
|
'AQ' => 'L\'Aquila',
|
|
'AR' => 'Arezzo',
|
|
'AT' => 'Asti',
|
|
'AV' => 'Avellino',
|
|
'BA' => 'Bari',
|
|
'BG' => 'Bergamo',
|
|
'BI' => 'Biella',
|
|
'BL' => 'Belluno',
|
|
'BN' => 'Benevento',
|
|
'BO' => 'Bologna',
|
|
'BR' => 'Brindisi',
|
|
'BS' => 'Brescia',
|
|
'BT' => 'Barletta-Andria-Trani',
|
|
'BZ' => 'Bolzano/Bozen',
|
|
'CA' => 'Cagliari',
|
|
'CB' => 'Campobasso',
|
|
'CE' => 'Caserta',
|
|
'CH' => 'Chieti',
|
|
'CI' => 'Carbonia-Iglesias',
|
|
'CL' => 'Caltanissetta',
|
|
'CN' => 'Cuneo',
|
|
'CO' => 'Como',
|
|
'CR' => 'Cremona',
|
|
'CS' => 'Cosenza',
|
|
'CT' => 'Catania',
|
|
'CZ' => 'Catanzaro',
|
|
'EN' => 'Enna',
|
|
'FC' => 'Forlì-Cesena',
|
|
'FE' => 'Ferrara',
|
|
'FG' => 'Foggia',
|
|
'FI' => 'Firenze',
|
|
'FM' => 'Fermo',
|
|
'FR' => 'Frosinone',
|
|
'GE' => 'Genova',
|
|
'GO' => 'Gorizia',
|
|
'GR' => 'Grosseto',
|
|
'IM' => 'Imperia',
|
|
'IS' => 'Isernia',
|
|
'KR' => 'Crotone',
|
|
'LC' => 'Lecco',
|
|
'LE' => 'Lecce',
|
|
'LI' => 'Livorno',
|
|
'LO' => 'Lodi',
|
|
'LT' => 'Latina',
|
|
'LU' => 'Lucca',
|
|
'MB' => 'Monza and Brianza',
|
|
'MC' => 'Macerata',
|
|
'ME' => 'Messina',
|
|
'MI' => 'Milano',
|
|
'MN' => 'Mantova',
|
|
'MO' => 'Modena',
|
|
'MS' => 'Massa-Carrara',
|
|
'MT' => 'Matera',
|
|
'NA' => 'Napoli',
|
|
'NO' => 'Novara',
|
|
'NU' => 'Nuoro',
|
|
'OG' => 'Ogliastra',
|
|
'OR' => 'Oristano',
|
|
'OT' => 'Olbia-Tempio',
|
|
'PA' => 'Palermo',
|
|
'PC' => 'Piacenza',
|
|
'PD' => 'Padova',
|
|
'PE' => 'Pescara',
|
|
'PG' => 'Perugia',
|
|
'PI' => 'Pisa',
|
|
'PN' => 'Pordenone',
|
|
'PO' => 'Prato',
|
|
'PR' => 'Parma',
|
|
'PT' => 'Pistoia',
|
|
'PU' => 'Pesaro-Urbino',
|
|
'PV' => 'Pavia',
|
|
'PZ' => 'Potenza',
|
|
'RA' => 'Ravenna',
|
|
'RC' => 'Reggio Calabria',
|
|
'RE' => 'Reggio Emilia',
|
|
'RG' => 'Ragusa',
|
|
'RI' => 'Rieti',
|
|
'RM' => 'Roma',
|
|
'RN' => 'Rimini',
|
|
'RO' => 'Rovigo',
|
|
'SA' => 'Salerno',
|
|
'SI' => 'Siena',
|
|
'SO' => 'Sondrio',
|
|
'SP' => 'La Spezia',
|
|
'SR' => 'Siracusa',
|
|
'SS' => 'Sassari',
|
|
'SV' => 'Savona',
|
|
'TA' => 'Taranto',
|
|
'TE' => 'Teramo',
|
|
'TN' => 'Trento',
|
|
'TO' => 'Torino',
|
|
'TP' => 'Trapani',
|
|
'TR' => 'Terni',
|
|
'TS' => 'Trieste',
|
|
'TV' => 'Treviso',
|
|
'UD' => 'Udine',
|
|
'VA' => 'Varese',
|
|
'VB' => 'Verbano-Cusio-Ossola',
|
|
'VC' => 'Vercelli',
|
|
'VE' => 'Venezia',
|
|
'VI' => 'Vicenza',
|
|
'VR' => 'Verona',
|
|
'VS' => 'Medio Campidano',
|
|
'VT' => 'Viterbo',
|
|
'VV' => 'Vibo Valentia'
|
|
);
|
|
|
|
function __construct() {
|
|
$this->load->library('Genfunctions');
|
|
}
|
|
|
|
/**
|
|
* Get province codes
|
|
*/
|
|
function getProvinceCodes() {
|
|
return array_keys($this->province_names);
|
|
}
|
|
|
|
/**
|
|
* Get province name from code
|
|
*/
|
|
function getProvinceName($code) {
|
|
return isset($this->province_names[$code]) ? $this->province_names[$code] : $code;
|
|
}
|
|
|
|
/**
|
|
* Build base SQL query
|
|
*/
|
|
private function buildBaseQuery($location_list, $withCount = false) {
|
|
$select = $withCount
|
|
? "SELECT UPPER(COL_STATE) as COL_STATE, COUNT(*) as qso_count"
|
|
: "SELECT DISTINCT UPPER(COL_STATE) as COL_STATE";
|
|
|
|
$sql = $select . "
|
|
FROM " . $this->config->item('table_name') . " thcv
|
|
WHERE station_id IN (" . $location_list . ")
|
|
AND COL_TIME_ON >= '" . $this->AWARD_START_DATE . "'
|
|
AND COL_DXCC IN ('" . $this->DXCC_ITALY . "', '" . $this->DXCC_SARDINIA . "')
|
|
AND (COL_PROP_MODE != 'SAT' OR COL_PROP_MODE IS NULL)
|
|
AND COL_BAND IN ('" . implode("','", $this->VALID_BANDS) . "')
|
|
AND COL_STATE IS NOT NULL AND COL_STATE != ''
|
|
AND UPPER(COL_STATE) IN ('" . implode("','", $this->getProvinceCodes()) . "')";
|
|
|
|
return $sql;
|
|
}
|
|
|
|
/**
|
|
* Add band filter to query
|
|
*/
|
|
private function addBandFilter($sql, $band) {
|
|
if ($band != 'All') {
|
|
$sql .= " AND COL_BAND = '" . $band . "'";
|
|
}
|
|
return $sql;
|
|
}
|
|
|
|
/**
|
|
* Add mode category filter to query
|
|
*/
|
|
private function addModeCategoryFilter($sql, $mode_category) {
|
|
if ($mode_category == 'PHONE') {
|
|
$sql .= " AND (UPPER(COL_MODE) IN ('SSB','USB','LSB','AM','FM','SSTV') OR UPPER(COL_SUBMODE) IN ('SSB','USB','LSB','AM','FM','SSTV'))";
|
|
} elseif ($mode_category == 'CW') {
|
|
$sql .= " AND (UPPER(COL_MODE) = 'CW' OR UPPER(COL_SUBMODE) = 'CW')";
|
|
} elseif ($mode_category == 'DIGI') {
|
|
$digi_modes = "'RTTY','PSK','PSK31','PSK63','PSK125','PSKR','FSK','FSK441','FT4','FT8','JS8','JT4','JT6M','JT9','JT65','MFSK','OLIVIA','OPERA','PAX','PAX2','PKT','Q15','QRA64','ROS','T10','THOR','THRB','TOR','VARA','WSPR'";
|
|
$sql .= " AND (UPPER(COL_MODE) IN (" . $digi_modes . ") OR UPPER(COL_SUBMODE) IN (" . $digi_modes . "))";
|
|
}
|
|
return $sql;
|
|
}
|
|
|
|
/**
|
|
* Finalize query with GROUP BY
|
|
*/
|
|
private function finalizeQuery($sql, $withCount = false) {
|
|
if ($withCount) {
|
|
$sql .= " GROUP BY UPPER(COL_STATE)";
|
|
}
|
|
return $sql;
|
|
}
|
|
|
|
/**
|
|
* Execute province query
|
|
*/
|
|
private function queryProvinces($location_list, $options = array()) {
|
|
$band = isset($options['band']) ? $options['band'] : 'All';
|
|
$mode_category = isset($options['mode_category']) ? $options['mode_category'] : null;
|
|
$confirmed = isset($options['confirmed']) ? $options['confirmed'] : false;
|
|
$withCount = isset($options['withCount']) ? $options['withCount'] : false;
|
|
$postdata = isset($options['postdata']) ? $options['postdata'] : array();
|
|
|
|
$sql = $this->buildBaseQuery($location_list, $withCount);
|
|
$sql = $this->addBandFilter($sql, $band);
|
|
|
|
if ($mode_category) {
|
|
$sql = $this->addModeCategoryFilter($sql, $mode_category);
|
|
}
|
|
|
|
if ($confirmed && !empty($postdata)) {
|
|
$sql .= $this->genfunctions->addQslToQuery($postdata);
|
|
}
|
|
|
|
$sql = $this->finalizeQuery($sql, $withCount);
|
|
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
}
|
|
|
|
/**
|
|
* Get worked (not confirmed) QSO counts by mode categories
|
|
*/
|
|
function get_waip_worked_by_modes($location_list) {
|
|
$result = array();
|
|
|
|
foreach ($this->province_names as $code => $name) {
|
|
$result[$code] = array_fill_keys($this->MODE_CATEGORIES, 0);
|
|
}
|
|
|
|
foreach ($this->MODE_CATEGORIES as $category) {
|
|
$provData = $this->queryProvinces($location_list, array(
|
|
'mode_category' => $category,
|
|
'confirmed' => false,
|
|
'withCount' => true
|
|
));
|
|
|
|
foreach ($provData as $line) {
|
|
if (isset($result[$line->COL_STATE])) {
|
|
$result[$line->COL_STATE][$category] = (int)$line->qso_count;
|
|
}
|
|
}
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Get confirmed QSO counts by mode categories
|
|
*/
|
|
function get_waip_simple_by_modes($postdata, $location_list) {
|
|
$result = array();
|
|
|
|
foreach ($this->province_names as $code => $name) {
|
|
$result[$code] = array_fill_keys($this->MODE_CATEGORIES, 0);
|
|
}
|
|
|
|
foreach ($this->MODE_CATEGORIES as $category) {
|
|
$provData = $this->queryProvinces($location_list, array(
|
|
'mode_category' => $category,
|
|
'confirmed' => true,
|
|
'withCount' => true,
|
|
'postdata' => $postdata
|
|
));
|
|
|
|
foreach ($provData as $line) {
|
|
if (isset($result[$line->COL_STATE])) {
|
|
$result[$line->COL_STATE][$category] = (int)$line->qso_count;
|
|
}
|
|
}
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Get total confirmed province counts by mode categories
|
|
*/
|
|
function get_waip_totals_by_modes($postdata, $location_list) {
|
|
$totals = array();
|
|
|
|
foreach ($this->MODE_CATEGORIES as $category) {
|
|
$provData = $this->queryProvinces($location_list, array(
|
|
'mode_category' => $category,
|
|
'confirmed' => true,
|
|
'postdata' => $postdata
|
|
));
|
|
$totals[$category] = count($provData);
|
|
}
|
|
|
|
return $totals;
|
|
}
|
|
|
|
/**
|
|
* Get worked (not confirmed) QSO counts by bands
|
|
*/
|
|
function get_waip_worked_by_bands($location_list) {
|
|
$result = array();
|
|
|
|
foreach ($this->province_names as $code => $name) {
|
|
$result[$code] = array_fill_keys($this->VALID_BANDS, 0);
|
|
}
|
|
|
|
foreach ($this->VALID_BANDS as $band) {
|
|
$provData = $this->queryProvinces($location_list, array(
|
|
'band' => $band,
|
|
'confirmed' => false,
|
|
'withCount' => true
|
|
));
|
|
|
|
foreach ($provData as $line) {
|
|
if (isset($result[$line->COL_STATE])) {
|
|
$result[$line->COL_STATE][$band] = (int)$line->qso_count;
|
|
}
|
|
}
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Get confirmed QSO counts by bands
|
|
*/
|
|
function get_waip_simple_by_bands($postdata, $location_list) {
|
|
$result = array();
|
|
|
|
foreach ($this->province_names as $code => $name) {
|
|
$result[$code] = array_fill_keys($this->VALID_BANDS, 0);
|
|
}
|
|
|
|
foreach ($this->VALID_BANDS as $band) {
|
|
$provData = $this->queryProvinces($location_list, array(
|
|
'band' => $band,
|
|
'confirmed' => true,
|
|
'withCount' => true,
|
|
'postdata' => $postdata
|
|
));
|
|
|
|
foreach ($provData as $line) {
|
|
if (isset($result[$line->COL_STATE])) {
|
|
$result[$line->COL_STATE][$band] = (int)$line->qso_count;
|
|
}
|
|
}
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Get total confirmed province counts by bands
|
|
*/
|
|
function get_waip_totals_by_bands($postdata, $location_list) {
|
|
$totals = array();
|
|
|
|
foreach ($this->VALID_BANDS as $band) {
|
|
$provData = $this->queryProvinces($location_list, array(
|
|
'band' => $band,
|
|
'confirmed' => true,
|
|
'postdata' => $postdata
|
|
));
|
|
$totals[$band] = count($provData);
|
|
}
|
|
|
|
return $totals;
|
|
}
|
|
|
|
/**
|
|
* Get map status (W=worked, C=confirmed, -=not worked)
|
|
*/
|
|
function get_waip_map_status($category, $postdata, $location_list) {
|
|
$result = array();
|
|
|
|
foreach ($this->province_names as $code => $name) {
|
|
$result[$code] = '-';
|
|
}
|
|
|
|
$options = array('withCount' => true);
|
|
|
|
if (in_array($category, $this->MODE_CATEGORIES)) {
|
|
$options['mode_category'] = $category;
|
|
} elseif (in_array($category, $this->VALID_BANDS)) {
|
|
$options['band'] = $category;
|
|
}
|
|
|
|
// Get worked provinces
|
|
$workedData = $this->queryProvinces($location_list, $options);
|
|
$workedProvs = array();
|
|
foreach ($workedData as $line) {
|
|
$workedProvs[$line->COL_STATE] = true;
|
|
}
|
|
|
|
// Get confirmed provinces
|
|
$options['confirmed'] = true;
|
|
$options['postdata'] = $postdata;
|
|
$confirmedData = $this->queryProvinces($location_list, $options);
|
|
$confirmedProvs = array();
|
|
foreach ($confirmedData as $line) {
|
|
$confirmedProvs[$line->COL_STATE] = true;
|
|
}
|
|
|
|
// Build result
|
|
foreach ($this->province_names as $code => $name) {
|
|
if (isset($confirmedProvs[$code])) {
|
|
$result[$code] = 'C';
|
|
} elseif (isset($workedProvs[$code])) {
|
|
$result[$code] = 'W';
|
|
}
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
}
|
|
?>
|