mirror of
https://github.com/magicbug/Cloudlog
synced 2026-08-13 17:49:35 -04:00
53019ade11
Extends Gridmap and Activated Gridmap to accept a new `sat_orbit` filter and applies it across worked/confirmed grid and VUCC queries, including SAT-specific SQL filtering for LEO, MEO (IO-117), and GEO (QO-100). Updates the gridmap UI to show/hide orbit controls with SAT selection, sends the new filter in AJAX requests, and adds activated-grid summary cards with worked/confirmed counts and satellite class breakdown data from new model methods.
341 lines
12 KiB
PHP
341 lines
12 KiB
PHP
<?php
|
|
|
|
class Activated_gridmap_model extends CI_Model {
|
|
|
|
function get_band_confirmed($band, $mode, $qsl, $lotw, $eqsl, $qrz, $sat, $sat_orbit = 'All') {
|
|
$CI =& get_instance();
|
|
$CI->load->model('logbooks_model');
|
|
$logbooks_locations_array = $CI->logbooks_model->list_logbook_relationships($this->session->userdata('active_station_logbook'));
|
|
|
|
if (!$logbooks_locations_array) {
|
|
return null;
|
|
}
|
|
|
|
$location_list = implode(',', array_map('intval', $logbooks_locations_array));
|
|
$band = $this->db->escape_str($band);
|
|
$mode = $this->db->escape_str($mode);
|
|
$sat = $this->db->escape_str($sat);
|
|
$sat_orbit = $this->db->escape_str($sat_orbit);
|
|
|
|
$sql = 'SELECT DISTINCT station_gridsquare AS GRID_SQUARES, COL_BAND FROM '
|
|
. 'station_profile JOIN '.$this->config->item('table_name').' on '.$this->config->item('table_name').'.station_id = station_profile.station_id '
|
|
. 'WHERE station_profile.station_gridsquare != "" '
|
|
. 'AND station_profile.station_id in ('.$location_list.')';
|
|
|
|
if ($band != 'All') {
|
|
if ($band == 'SAT') {
|
|
$sql .= " and col_prop_mode ='" . $band . "'";
|
|
$sql .= $this->getSatelliteOrbitFilterSql($sat_orbit);
|
|
if ($sat != 'All') {
|
|
$sql .= " and col_sat_name ='" . $sat . "'";
|
|
}
|
|
}
|
|
else {
|
|
$sql .= " and col_prop_mode !='SAT'";
|
|
$sql .= " and col_band ='" . $band . "'";
|
|
}
|
|
}
|
|
|
|
if ($mode != 'All') {
|
|
$sql .= " and (col_mode ='" . $mode . "' or col_submode ='" . $mode . "')";
|
|
}
|
|
|
|
$sql .= $this->addQslToQuery($qsl, $lotw, $eqsl, $qrz);
|
|
return $this->db->query($sql);
|
|
}
|
|
|
|
function get_band($band, $mode, $qsl, $lotw, $eqsl, $qrz, $sat, $sat_orbit = 'All') {
|
|
$CI =& get_instance();
|
|
$CI->load->model('logbooks_model');
|
|
$logbooks_locations_array = $CI->logbooks_model->list_logbook_relationships($this->session->userdata('active_station_logbook'));
|
|
|
|
if (!$logbooks_locations_array) {
|
|
return null;
|
|
}
|
|
|
|
$location_list = implode(',', array_map('intval', $logbooks_locations_array));
|
|
$band = $this->db->escape_str($band);
|
|
$mode = $this->db->escape_str($mode);
|
|
$sat = $this->db->escape_str($sat);
|
|
$sat_orbit = $this->db->escape_str($sat_orbit);
|
|
|
|
$sql = 'SELECT DISTINCT station_gridsquare AS GRID_SQUARES, COL_BAND FROM '
|
|
. 'station_profile JOIN '.$this->config->item('table_name').' on '.$this->config->item('table_name').'.station_id = station_profile.station_id '
|
|
. 'WHERE station_profile.station_gridsquare != "" '
|
|
. 'AND station_profile.station_id in ('.$location_list.')';
|
|
|
|
if ($band != 'All') {
|
|
if ($band == 'SAT') {
|
|
$sql .= " and col_prop_mode ='" . $band . "'";
|
|
$sql .= $this->getSatelliteOrbitFilterSql($sat_orbit);
|
|
if ($sat != 'All') {
|
|
$sql .= " and col_sat_name ='" . $sat . "'";
|
|
}
|
|
}
|
|
else {
|
|
$sql .= " and col_prop_mode !='SAT'";
|
|
$sql .= " and col_band ='" . $band . "'";
|
|
}
|
|
}
|
|
|
|
if ($mode != 'All') {
|
|
$sql .= " and (col_mode ='" . $mode . "' or col_submode ='" . $mode . "')";
|
|
}
|
|
|
|
return $this->db->query($sql);
|
|
}
|
|
|
|
function get_band_worked_vucc_squares($band, $mode, $qsl, $lotw, $eqsl, $qrz, $sat, $sat_orbit = 'All') {
|
|
$CI =& get_instance();
|
|
$CI->load->model('logbooks_model');
|
|
$logbooks_locations_array = $CI->logbooks_model->list_logbook_relationships($this->session->userdata('active_station_logbook'));
|
|
|
|
if (!$logbooks_locations_array) {
|
|
return null;
|
|
}
|
|
|
|
$location_list = implode(',', array_map('intval', $logbooks_locations_array));
|
|
$band = $this->db->escape_str($band);
|
|
$mode = $this->db->escape_str($mode);
|
|
$sat = $this->db->escape_str($sat);
|
|
$sat_orbit = $this->db->escape_str($sat_orbit);
|
|
|
|
$sql = 'SELECT distinct COL_VUCC_GRIDS, COL_BAND FROM '
|
|
.$this->config->item('table_name')
|
|
.' WHERE station_id in ('
|
|
.$location_list.') AND COL_VUCC_GRIDS != ""';
|
|
|
|
if ($band != 'All') {
|
|
if ($band == 'SAT') {
|
|
$sql .= " and col_prop_mode ='" . $band . "'";
|
|
$sql .= $this->getSatelliteOrbitFilterSql($sat_orbit);
|
|
if ($sat != 'All') {
|
|
$sql .= " and col_sat_name ='" . $sat . "'";
|
|
}
|
|
}
|
|
else {
|
|
$sql .= " and col_prop_mode !='SAT'";
|
|
$sql .= " and col_band ='" . $band . "'";
|
|
}
|
|
}
|
|
|
|
if ($mode != 'All') {
|
|
$sql .= " and (col_mode ='" . $mode . "' or col_submode ='" . $mode . "')";
|
|
}
|
|
|
|
return null;
|
|
return $this->db->query($sql);
|
|
}
|
|
|
|
function get_band_confirmed_vucc_squares($band, $mode, $qsl, $lotw, $eqsl, $qrz, $sat, $sat_orbit = 'All') {
|
|
$CI =& get_instance();
|
|
$CI->load->model('logbooks_model');
|
|
$logbooks_locations_array = $CI->logbooks_model->list_logbook_relationships($this->session->userdata('active_station_logbook'));
|
|
|
|
if (!$logbooks_locations_array) {
|
|
return null;
|
|
}
|
|
|
|
$location_list = implode(',', array_map('intval', $logbooks_locations_array));
|
|
$band = $this->db->escape_str($band);
|
|
$mode = $this->db->escape_str($mode);
|
|
$sat = $this->db->escape_str($sat);
|
|
$sat_orbit = $this->db->escape_str($sat_orbit);
|
|
|
|
$sql = 'SELECT distinct COL_VUCC_GRIDS, COL_BAND FROM '
|
|
.$this->config->item('table_name')
|
|
.' WHERE station_id in ('
|
|
.$location_list.') AND COL_VUCC_GRIDS != ""';
|
|
|
|
if ($band != 'All') {
|
|
if ($band == 'SAT') {
|
|
$sql .= " and col_prop_mode ='" . $band . "'";
|
|
$sql .= $this->getSatelliteOrbitFilterSql($sat_orbit);
|
|
if ($sat != 'All') {
|
|
$sql .= " and col_sat_name ='" . $sat . "'";
|
|
}
|
|
}
|
|
else {
|
|
$sql .= " and col_prop_mode !='SAT'";
|
|
$sql .= " and col_band ='" . $band . "'";
|
|
}
|
|
}
|
|
|
|
if ($mode != 'All') {
|
|
$sql .= " and (col_mode ='" . $mode . "' or col_submode ='" . $mode . "')";
|
|
}
|
|
|
|
$sql .= $this->addQslToQuery($qsl, $lotw, $eqsl, $qrz);
|
|
|
|
return null;
|
|
return $this->db->query($sql);
|
|
}
|
|
|
|
function get_satellite_breakdown($band, $mode, $qsl, $lotw, $eqsl, $qrz, $sat, $sat_orbit = 'All', $confirmed = false) {
|
|
$CI =& get_instance();
|
|
$CI->load->model('logbooks_model');
|
|
$logbooks_locations_array = $CI->logbooks_model->list_logbook_relationships($this->session->userdata('active_station_logbook'));
|
|
|
|
if (!$logbooks_locations_array) {
|
|
return array(
|
|
'total' => 0,
|
|
'LEO' => 0,
|
|
'MEO' => 0,
|
|
'GEO' => 0,
|
|
);
|
|
}
|
|
|
|
$location_list = implode(',', array_map('intval', $logbooks_locations_array));
|
|
$band = $this->db->escape_str($band);
|
|
$mode = $this->db->escape_str($mode);
|
|
$sat = $this->db->escape_str($sat);
|
|
$sat_orbit = $this->db->escape_str($sat_orbit);
|
|
|
|
$sql = 'SELECT DISTINCT station_gridsquare AS GRID_SQUARES, COL_SAT_NAME FROM ' . $this->config->item('table_name')
|
|
. ' JOIN station_profile ON ' . $this->config->item('table_name') . '.station_id = station_profile.station_id'
|
|
. ' WHERE station_profile.station_gridsquare != ""'
|
|
. ' AND station_profile.station_id in (' . $location_list . ')'
|
|
. ' AND col_prop_mode =\'SAT\'';
|
|
|
|
if ($band != 'All') {
|
|
if ($band == 'SAT') {
|
|
$sql .= $this->getSatelliteOrbitFilterSql($sat_orbit);
|
|
if ($sat != 'All') {
|
|
$sql .= " and col_sat_name ='" . $sat . "'";
|
|
}
|
|
} else {
|
|
$sql .= " and col_prop_mode !='SAT'";
|
|
$sql .= " and col_band ='" . $band . "'";
|
|
}
|
|
}
|
|
|
|
if ($mode != 'All') {
|
|
$sql .= " and (col_mode ='" . $mode . "' or col_submode ='" . $mode . "')";
|
|
}
|
|
|
|
if ($confirmed) {
|
|
$sql .= $this->addQslToQuery($qsl, $lotw, $eqsl, $qrz);
|
|
}
|
|
|
|
$query = $this->db->query($sql);
|
|
$categories = array(
|
|
'LEO' => array(),
|
|
'MEO' => array(),
|
|
'GEO' => array(),
|
|
);
|
|
$unique_grids = array();
|
|
|
|
if ($query && $query->num_rows() > 0) {
|
|
foreach ($query->result() as $row) {
|
|
$grid = strtoupper(substr((string) $row->GRID_SQUARES, 0, 4));
|
|
$satellite = strtoupper(trim((string) $row->COL_SAT_NAME));
|
|
$category = $this->get_satellite_category($satellite);
|
|
|
|
if ($grid !== '' && !in_array($grid, $unique_grids, true)) {
|
|
$unique_grids[] = $grid;
|
|
}
|
|
|
|
if ($grid !== '' && !in_array($grid, $categories[$category], true)) {
|
|
$categories[$category][] = $grid;
|
|
}
|
|
}
|
|
}
|
|
|
|
return array(
|
|
'total' => count($unique_grids),
|
|
'LEO' => count($categories['LEO']),
|
|
'MEO' => count($categories['MEO']),
|
|
'GEO' => count($categories['GEO']),
|
|
);
|
|
}
|
|
|
|
function get_satellite_category($satellite_name) {
|
|
if ($satellite_name === 'IO-117') {
|
|
return 'MEO';
|
|
}
|
|
|
|
if ($satellite_name === 'QO-100') {
|
|
return 'GEO';
|
|
}
|
|
|
|
return 'LEO';
|
|
}
|
|
|
|
function getSatelliteOrbitFilterSql($sat_orbit) {
|
|
if ($sat_orbit === 'MEO') {
|
|
return " and col_sat_name = 'IO-117'";
|
|
}
|
|
|
|
if ($sat_orbit === 'GEO') {
|
|
return " and col_sat_name = 'QO-100'";
|
|
}
|
|
|
|
if ($sat_orbit === 'LEO') {
|
|
return " and col_sat_name not in ('IO-117', 'QO-100')";
|
|
}
|
|
|
|
return '';
|
|
}
|
|
|
|
// Adds confirmation to query
|
|
function addQslToQuery($qsl, $lotw, $eqsl, $qrz) {
|
|
$sql = '';
|
|
if ($lotw == "true") {
|
|
$sql .= " or col_lotw_qsl_sent = 'Y'";
|
|
}
|
|
|
|
if ($qsl == "true") {
|
|
$sql .= " or col_qsl_sent = 'Y'";
|
|
}
|
|
|
|
if ($eqsl == "true") {
|
|
$sql .= " or col_eqsl_qsl_sent = 'Y'";
|
|
}
|
|
|
|
if ($qrz == "true") {
|
|
$sql .= " or col_qrzcom_qso_upload_status = 'Y'";
|
|
}
|
|
if ($sql != '') {
|
|
$sql='and (1=0 '.$sql.')';
|
|
}
|
|
return $sql;
|
|
}
|
|
|
|
|
|
/*
|
|
* Get's the worked modes from the log
|
|
*/
|
|
function get_worked_modes() {
|
|
$CI =& get_instance();
|
|
$CI->load->model('logbooks_model');
|
|
$logbooks_locations_array = $CI->logbooks_model->list_logbook_relationships($this->session->userdata('active_station_logbook'));
|
|
|
|
if (!$logbooks_locations_array) {
|
|
return null;
|
|
}
|
|
|
|
$location_list = implode(',', array_map('intval', $logbooks_locations_array));
|
|
|
|
// get all worked modes from database
|
|
$data = $this->db->query(
|
|
"SELECT distinct LOWER(`COL_MODE`) as `COL_MODE` FROM `" . $this->config->item('table_name') . "` WHERE station_id in (" . $location_list . ") order by COL_MODE ASC"
|
|
);
|
|
$results = array();
|
|
foreach ($data->result() as $row) {
|
|
array_push($results, $row->COL_MODE);
|
|
}
|
|
|
|
$data = $this->db->query(
|
|
"SELECT distinct LOWER(`COL_SUBMODE`) as `COL_SUBMODE` FROM `" . $this->config->item('table_name') . "` WHERE station_id in (" . $location_list . ") and coalesce(COL_SUBMODE, '') <> '' order by COL_SUBMODE ASC"
|
|
);
|
|
foreach ($data->result() as $row) {
|
|
if (!in_array($row, $results)) {
|
|
array_push($results, $row->COL_SUBMODE);
|
|
}
|
|
}
|
|
|
|
asort($results);
|
|
|
|
return $results;
|
|
}
|
|
}
|