cloudlog/application/models/Dxcc.php

<?php

class DXCC extends CI_Model {

	/**
	*	Function: mostactive
	*	Information: Returns the most active band
	**/
	function info($callsign)
	{
		$exceptions = $this->db->query('
				SELECT *
				FROM `dxcc_exceptions`
				WHERE `prefix` = \''.$callsign.'\'
				LIMIT 1
			');

		if ($exceptions->num_rows() > 0)
		{
			return $exceptions;
		} else {

			$query = $this->db->query('
					SELECT *
					FROM dxcc_entities
					WHERE prefix = SUBSTRING( \''.$callsign.'\', 1, LENGTH( prefix ) )
					ORDER BY LENGTH( prefix ) DESC
					LIMIT 1
				');

			return $query;
		}
	}

    function search(){
        print_r($this->input->get());
        return;
    }

	function empty_table($table) {
		$this->db->empty_table($table);
	}

	/*
	 * Fetches a list of all dxcc's, both current and deleted
	 */
	function list() {
		$this->db->order_by('name', 'ASC');
		return $this->db->get('dxcc_entities');
	}

	/*
	 * Fetches a list of all current dxcc's (non-deleted)
	 */
	function list_current() {
		$this->db->where('end', null);
		$this->db->order_by('name', 'ASC');
		return $this->db->get('dxcc_entities');
	}

	/* Optimized method to get count of current DXCC entities without loading all records */
	function get_total_dxcc_count() {
		$this->db->where('end', null);
		return $this->db->count_all_results('dxcc_entities');
	}

	function get_dxcc_array($dxccArray, $bands, $postdata) {
		$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));

		$qsl = "";
		if ($postdata['confirmed'] != NULL) {
			if ($postdata['qsl'] != NULL ) {
				$qsl .= "Q";
			}
			if ($postdata['lotw'] != NULL ) {
				$qsl .= "L";
			}
			if ($postdata['eqsl'] != NULL ) {
				$qsl .= "E";
			}
		}

		foreach ($bands as $band) {             	// Looping through bands and entities to generate the array needed for display
			foreach ($dxccArray as $dxcc) {
				$dxccMatrix[$dxcc->adif]['name'] = ucwords(strtolower($dxcc->name), "- (/");
				$dxccMatrix[$dxcc->adif]['Dxccprefix'] = $dxcc->prefix;
				if ($postdata['includedeleted'])
					$dxccMatrix[$dxcc->adif]['Deleted'] = isset($dxcc->Enddate) ? 1 : 0;
				$dxccMatrix[$dxcc->adif][$band] = '-';
			}

			// If worked is checked, we add worked entities to the array
			if ($postdata['worked'] != NULL) {
				$workedDXCC = $this->getDxccBandWorked($location_list, $band, $postdata);
				foreach ($workedDXCC as $wdxcc) {
					$dxccMatrix[$wdxcc->dxcc][$band] = '<div class="bg-danger awardsBgDanger" ><a href=\'javascript:viewDxccQsosByStatus(' . $wdxcc->dxcc . ', "W")\'>W</a></div>';
				}
			}

			// If confirmed is checked, we add confirmed entities to the array
			if ($postdata['confirmed'] != NULL) {
				$confirmedDXCC = $this->getDxccBandConfirmed($location_list, $band, $postdata);
				foreach ($confirmedDXCC as $cdxcc) {
					$dxccMatrix[$cdxcc->dxcc][$band] = '<div class="bg-success awardsBgSuccess"><a href=\'javascript:viewDxccQsosByStatus(' . $cdxcc->dxcc . ', "C")\'>C</a></div>';
				}
			}
		}

		// We want to remove the worked dxcc's in the list, since we do not want to display them
		if ($postdata['worked'] == NULL) {
			$workedDxcc = $this->getDxccWorked($location_list, $postdata);
			foreach ($workedDxcc as $wdxcc) {
				if (array_key_exists($wdxcc->dxcc, $dxccMatrix)) {
					unset($dxccMatrix[$wdxcc->dxcc]);
				}
			}
		}

		// We want to remove the confirmed dxcc's in the list, since we do not want to display them
		if ($postdata['confirmed'] == NULL) {
			$confirmedDxcc = $this->getDxccConfirmed($location_list, $postdata);
			foreach ($confirmedDxcc as $cdxcc) {
				if (array_key_exists($cdxcc->dxcc, $dxccMatrix)) {
					unset($dxccMatrix[$cdxcc->dxcc]);
				}
			}
		}

		if (isset($dxccMatrix)) {
			return $dxccMatrix;
		}
		else {
			return 0;
		}
	}

	function getDxccBandConfirmed($location_list, $band, $postdata) {
		$sql = "select adif as dxcc, name from dxcc_entities
				join (
					select col_dxcc from ".$this->config->item('table_name')." thcv
					where station_id in (" . $location_list .
				  ") and col_dxcc > 0";

		$sql .= $this->addBandToQuery($band);

		$sql .= $this->addModeToQuery($postdata['mode']);

		$sql .= $this->addYearToQuery($postdata);
		$sql .= $this->addQslToQuery($postdata);

		$sql .= " group by col_dxcc
				) x on dxcc_entities.adif = x.col_dxcc";

		if ($postdata['includedeleted'] == NULL) {
			$sql .= " and dxcc_entities.end is null";
		}

		$sql .= $this->addContinentsToQuery($postdata);

		$query = $this->db->query($sql);

		return $query->result();
	}

	function getDxccBandWorked($location_list, $band, $postdata) {
		$sql = "select adif as dxcc, name from dxcc_entities
				join (
					select col_dxcc from ".$this->config->item('table_name')." thcv
					where station_id in (" . $location_list .
					") and col_dxcc > 0";

		$sql .= $this->addBandToQuery($band);

		$sql .= $this->addModeToQuery($postdata['mode']);

		$sql .= $this->addYearToQuery($postdata);

		$sql .= " group by col_dxcc
				) x on dxcc_entities.adif = x.col_dxcc";;

		if ($postdata['includedeleted'] == NULL) {
			$sql .= " and dxcc_entities.end is null";
		}

		$sql .= $this->addContinentsToQuery($postdata);

		$query = $this->db->query($sql);

		return $query->result();
	}

	function addBandToQuery($band) {
		$sql = '';
		$propModeBands = array('SAT', 'EME');

		if (is_array($band)) {
			$selectedBands = array_values(array_unique(array_filter($band, function ($value) {
				return $value !== NULL && $value !== '';
			})));

			if (count($selectedBands) === 0) {
				return ' and 1 = 0';
			}

			$selectedPropModes = array_values(array_intersect($selectedBands, $propModeBands));
			$terrestrialBands = array_values(array_diff($selectedBands, $propModeBands));
			$hasPropModes = count($selectedPropModes) > 0;
			$propModesList = "'" . implode("','", array_map(array($this->db, 'escape_str'), $selectedPropModes)) . "'";

			if ($hasPropModes && count($terrestrialBands) > 0) {
				$bandslots_list = "'" . implode("','", array_map(array($this->db, 'escape_str'), $terrestrialBands)) . "'";
				$sql .= " and ((col_prop_mode in (" . $propModesList . ")) or (col_prop_mode not in ('SAT', 'EME') and col_band in (" . $bandslots_list . ")))";
			} else if ($hasPropModes) {
				$sql .= " and col_prop_mode in (" . $propModesList . ")";
			} else if (count($terrestrialBands) > 0) {
				$bandslots_list = "'" . implode("','", array_map(array($this->db, 'escape_str'), $terrestrialBands)) . "'";
				$sql .= " and col_prop_mode not in ('SAT', 'EME')";
				$sql .= " and col_band in (" . $bandslots_list . ")";
			} else {
				$sql .= ' and 1 = 0';
			}

			return $sql;
		}

		if ($band != 'All') {
			$safeBand = $this->db->escape_str($band);
			if (in_array($band, $propModeBands, true)) {
				$sql .= " and col_prop_mode ='" . $safeBand . "'";
			} else {
				$sql .= " and col_prop_mode not in ('SAT', 'EME')";
				$sql .= " and col_band ='" . $safeBand . "'";
			}
		}
		return $sql;
	}

	function addModeToQuery($mode) {
		if ($mode == 'All') {
			return '';
		}
		$safeMode = $this->db->escape_str($mode);
		return " and (col_mode = '" . $safeMode . "' or col_submode = '" . $safeMode . "')";
	}

	function addYearToQuery($postdata) {
		$sql = '';
		if (!empty($postdata['year']) && $postdata['year'] !== 'All') {
			$year = (int) $postdata['year'];
			$sql .= " and YEAR(col_time_on) = " . $year;
		}
		return $sql;
	}

	function get_worked_years() {
		$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 [];
		}
		$location_list = implode(',', array_map('intval', $logbooks_locations_array));
		$sql = "SELECT DISTINCT YEAR(col_time_on) as year FROM " . $this->config->item('table_name') .
			" WHERE station_id IN (" . $location_list . ") AND col_dxcc > 0 AND col_time_on IS NOT NULL ORDER BY year DESC";
		$query = $this->db->query($sql);
		return array_column($query->result_array(), 'year');
	}

	function fetchDxcc($postdata) {
		$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));

		$sql = "select adif, prefix, name, cont, date(end) Enddate, date(start) Startdate, lat, `long`
            from dxcc_entities";

		if ($postdata['notworked'] == NULL) {
			$sql .= " join (select col_dxcc from " . $this->config->item('table_name') . " where station_id in (" . $location_list . ") and col_dxcc > 0";

			$sql .= $this->addBandToQuery($postdata['band']);

			$sql .= $this->addModeToQuery($postdata['mode']);

			$sql .= ' group by col_dxcc) x on dxcc_entities.adif = x.col_dxcc';
		}

		$sql .= " where 1 = 1";

		if ($postdata['includedeleted'] == NULL) {
			$sql .= " and end is null";
		}

		$sql .= $this->addContinentsToQuery($postdata);

		$sql .= ' order by prefix';
		$query = $this->db->query($sql);

		return $query->result();
	}

	function getDxccWorked($location_list, $postdata) {
		$sql = "SELECT adif as dxcc FROM dxcc_entities
        join (
            select col_dxcc
            from ".$this->config->item('table_name')." thcv
            where station_id in (" . $location_list .
              ") and col_dxcc > 0";

		$sql .= $this->addBandToQuery($postdata['band']);

		$sql .= $this->addModeToQuery($postdata['mode']);

		$sql .= $this->addYearToQuery($postdata);

		$sql .= " and not exists (select 1 from ".$this->config->item('table_name')." where station_id in (". $location_list .") and col_dxcc = thcv.col_dxcc and col_dxcc > 0";

		$sql .= $this->addBandToQuery($postdata['band']);

		$sql .= $this->addModeToQuery($postdata['mode']);

		$sql .= $this->addYearToQuery($postdata);

		$sql .= $this->addQslToQuery($postdata);

		$sql .= ')';

		$sql .= " group by col_dxcc
            ) ll on dxcc_entities.adif = ll.col_dxcc
            where 1=1";

		if ($postdata['includedeleted'] == NULL) {
			$sql .= " and dxcc_entities.end is null";
		}

		$sql .= $this->addContinentsToQuery($postdata);

		$query = $this->db->query($sql);

		return $query->result();
	}

	function getDxccConfirmed($location_list, $postdata) {
		$sql = "SELECT adif as dxcc FROM dxcc_entities
            join (
                select col_dxcc
                from ".$this->config->item('table_name')." thcv
                where station_id in (". $location_list .
                    ") and col_dxcc > 0";

		$sql .= $this->addBandToQuery($postdata['band']);

		$sql .= $this->addModeToQuery($postdata['mode']);

		$sql .= $this->addYearToQuery($postdata);
		$sql .= $this->addQslToQuery($postdata);

		$sql .= " group by col_dxcc
            ) ll on dxcc_entities.adif = ll.col_dxcc
            where 1=1";

		if ($postdata['includedeleted'] == NULL) {
			$sql .= " and dxcc_entities.end is null";
		}

		$sql .= $this->addContinentsToQuery($postdata);

		$query = $this->db->query($sql);

		return $query->result();
	}

	// Made function instead of repeating this several times
	function addQslToQuery($postdata) {
		$sql = '';
		$qsl = array();
		if ($postdata['lotw'] != NULL || $postdata['qsl'] != NULL || $postdata['eqsl'] != NULL) {
			$sql .= ' and (';
			if ($postdata['qsl'] != NULL) {
				array_push($qsl, "col_qsl_rcvd = 'Y'");
			}
			if ($postdata['lotw'] != NULL) {
				array_push($qsl, "col_lotw_qsl_rcvd = 'Y'");
			}
			if ($postdata['eqsl'] != NULL) {
				array_push($qsl, "col_eqsl_qsl_rcvd = 'Y'");
			}
			$sql .= implode(' or ', $qsl);
			$sql .= ')';
		}
		return $sql;
	}

	// Made function instead of repeating this several times
	function addContinentsToQuery($postdata) {
		$sql = '';
		if ($postdata['Africa'] == NULL) {
			$sql .= " and cont <> 'AF'";
		}

		if ($postdata['Europe'] == NULL) {
			$sql .= " and cont <> 'EU'";
		}

		if ($postdata['Asia'] == NULL) {
			$sql .= " and cont <> 'AS'";
		}

		if ($postdata['SouthAmerica'] == NULL) {
			$sql .= " and cont <> 'SA'";
		}

		if ($postdata['NorthAmerica'] == NULL) {
			$sql .= " and cont <> 'NA'";
		}

		if ($postdata['Oceania'] == NULL) {
			$sql .= " and cont <> 'OC'";
		}

		if ($postdata['Antarctica'] == NULL) {
			$sql .= " and cont <> 'AN'";
		}
		return $sql;
	}

	/*
     * Function gets worked and confirmed summary on each band on the active stationprofile
     */
	function get_dxcc_summary($bands, $postdata)
	{
		$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));

		foreach ($bands as $band) {
			$worked = $this->getSummaryByBand($band, $postdata, $location_list);
			$confirmed = $this->getSummaryByBandConfirmed($band, $postdata, $location_list);
			$dxccSummary['worked'][$band] = $worked[0]->count;
			$dxccSummary['confirmed'][$band] = $confirmed[0]->count;
		}

		$workedTotal = $this->getSummaryByBand($postdata['band'], $postdata, $location_list);
		$confirmedTotal = $this->getSummaryByBandConfirmed($postdata['band'], $postdata, $location_list);

		$dxccSummary['worked']['Total'] = $workedTotal[0]->count;
		$dxccSummary['confirmed']['Total'] = $confirmedTotal[0]->count;

		return $dxccSummary;
	}

	function getSummaryByBand($band, $postdata, $location_list)
	{
		$sql = "SELECT count(distinct thcv.col_dxcc) as count FROM " . $this->config->item('table_name') . " thcv";
		$sql .= " join dxcc_entities d on thcv.col_dxcc = d.adif";

		$sql .= " where station_id in (" . $location_list . ") and col_dxcc > 0";

		if (is_array($band)) {
			$sql .= $this->addBandToQuery($band);
		} else if ($band == 'SAT') {
			$sql .= " and thcv.col_prop_mode ='" . $this->db->escape_str($band) . "'";
		} else if ($band == 'All') {
			$this->load->model('bands');

			$bandslots = $this->bands->get_worked_bands('dxcc');
	
			$bandslots_list = "'".implode("','", array_map(array($this->db, 'escape_str'), $bandslots))."'";
			
			$sql .= " and thcv.col_band in (" . $bandslots_list . ")" .
					" and thcv.col_prop_mode !='SAT'";
		} else {
			$sql .= " and thcv.col_prop_mode !='SAT'";
			$sql .= " and thcv.col_band ='" . $this->db->escape_str($band) . "'";
		}

		$sql .= $this->addModeToQuery($postdata['mode']);

		if ($postdata['includedeleted'] == NULL) {
			$sql .= " and d.end is null";
		}

		$sql .= $this->addContinentsToQuery($postdata);

		$query = $this->db->query($sql);

		return $query->result();
	}

	function getSummaryByBandConfirmed($band, $postdata, $location_list)
	{
		$sql = "SELECT count(distinct thcv.col_dxcc) as count FROM " . $this->config->item('table_name') . " thcv";
		$sql .= " join dxcc_entities d on thcv.col_dxcc = d.adif";

		$sql .= " where station_id in (" . $location_list . ")";

		if (is_array($band)) {
			$sql .= $this->addBandToQuery($band);
		} else if ($band == 'SAT') {
			$sql .= " and thcv.col_prop_mode ='" . $this->db->escape_str($band) . "'";
		} else if ($band == 'All') {
			$this->load->model('bands');

			$bandslots = $this->bands->get_worked_bands('dxcc');
	
			$bandslots_list = "'".implode("','", array_map(array($this->db, 'escape_str'), $bandslots))."'";
			
			$sql .= " and thcv.col_band in (" . $bandslots_list . ")" .
					" and thcv.col_prop_mode !='SAT'";
		} else {
			$sql .= " and thcv.col_prop_mode !='SAT'";
			$sql .= " and thcv.col_band ='" . $this->db->escape_str($band) . "'";
		}

		$sql .= $this->addModeToQuery($postdata['mode']);

		$sql .= $this->addQslToQuery($postdata);

		
		if ($postdata['includedeleted'] == NULL) {
			$sql .= " and d.end is null";
		}

		$sql .= $this->addContinentsToQuery($postdata);

		$query = $this->db->query($sql);

		return $query->result();
	}

  function lookup_country($country)
	{
		$query = $this->db->query('
					SELECT *
					FROM dxcc_entities
					WHERE name = "'.$country.'"
					ORDER BY LENGTH( prefix ) DESC
					LIMIT 1
				');

		return $query->row();
	}

	/*
	 * Get continent breakdown for DXCC statistics
	 */
	function get_continent_breakdown($dxccArray) {
		$continents = array(
			'Africa' => 0,
			'Antarctica' => 0,
			'Asia' => 0,
			'Europe' => 0,
			'NorthAmerica' => 0,
			'SouthAmerica' => 0,
			'Oceania' => 0
		);

		if (!$dxccArray) {
			return $continents;
		}

		// Map continent codes to names
		$continentMap = array(
			'AF' => 'Africa',
			'AN' => 'Antarctica',
			'AS' => 'Asia',
			'EU' => 'Europe',
			'NA' => 'NorthAmerica',
			'SA' => 'SouthAmerica',
			'OC' => 'Oceania'
		);

		foreach ($dxccArray as $dxcc) {
			if (isset($dxcc->cont) && isset($continentMap[$dxcc->cont])) {
				$continentName = $continentMap[$dxcc->cont];
				$continents[$continentName]++;
			}
		}

		return $continents;
	}

	/*
	 * Get overall DXCC statistics
	 */
	function get_dxcc_statistics($dxcc_array, $postdata) {
		$stats = array(
			'total_worked' => 0,
			'total_confirmed' => 0,
			'worked_by_continent' => array(
				'Africa' => 0,
				'Antarctica' => 0,
				'Asia' => 0,
				'Europe' => 0,
				'NorthAmerica' => 0,
				'SouthAmerica' => 0,
				'Oceania' => 0
			),
			'confirmed_by_continent' => array(
				'Africa' => 0,
				'Antarctica' => 0,
				'Asia' => 0,
				'Europe' => 0,
				'NorthAmerica' => 0,
				'SouthAmerica' => 0,
				'Oceania' => 0
			),
			'milestones' => array(
				100 => false,
				200 => false,
				300 => false
			)
		);

		if (!$dxcc_array) {
			return $stats;
		}

		// Get DXCC entities to map continents
		$dxcc_result = $this->db->get('dxcc_entities')->result_array();
		$all_dxcc = array();
		foreach ($dxcc_result as $row) {
			$all_dxcc[$row['adif']] = $row;
		}

		// Map continent codes to names
		$continentMap = array(
			'AF' => 'Africa',
			'AN' => 'Antarctica',
			'AS' => 'Asia',
			'EU' => 'Europe',
			'NA' => 'NorthAmerica',
			'SA' => 'SouthAmerica',
			'OC' => 'Oceania'
		);

		// Count worked DXCC from array
		$worked_count = 0;
		$confirmed_count = 0;
		
		foreach ($dxcc_array as $adif => $entity) {
			// Check if this DXCC has any confirmed contacts (any cell with 'bg-success')
			$is_worked = false;
			$is_confirmed = false;
			
			foreach ($entity as $key => $value) {
				if (is_string($value) && strpos($value, 'bg-success') !== false) {
					$is_confirmed = true;
					$is_worked = true;
					break;
				}
				if (is_string($value) && strpos($value, 'bg-danger') !== false) {
					$is_worked = true;
				}
			}
			
			if ($is_worked) {
				$worked_count++;
			}
			if ($is_confirmed) {
				$confirmed_count++;
			}
			
			// Map continent stats
			if (isset($all_dxcc[$adif]) && isset($all_dxcc[$adif]['cont'])) {
				$cont_code = $all_dxcc[$adif]['cont'];
				$continent = isset($continentMap[$cont_code]) ? $continentMap[$cont_code] : null;
				
				if ($continent) {
					if ($is_confirmed) {
						$stats['confirmed_by_continent'][$continent]++;
					}
					if ($is_worked) {
						$stats['worked_by_continent'][$continent]++;
					}
				}
			}
		}

		$stats['total_worked'] = $worked_count;
		$stats['total_confirmed'] = $confirmed_count;

		// Check milestone achievements
		$stats['milestones'][100] = $stats['total_worked'] >= 100;
		$stats['milestones'][200] = $stats['total_worked'] >= 200;
		$stats['milestones'][300] = $stats['total_worked'] >= 300;

		return $stats;
	}
}
?>