cloudlog/application/models/Bands.php

<?php

class Bands extends CI_Model {

	public $bandslots = array(
		// LF/MF Bands
		"2190m"=>0,
		"630m"=>0,
		"560m"=>0,
		// HF Bands
		"160m"=>0,
		"80m"=>0,
		"60m"=>0,
		"40m"=>0,
		"30m"=>0,
		"20m"=>0,
		"17m"=>0,
		"15m"=>0,
		"12m"=>0,
		"10m"=>0,
		// VHF Bands
		"6m"=>0,
		"4m"=>0,
		"2m"=>0,
		"1.25m"=>0,
		// UHF Bands
		"70cm"=>0,
		"33cm"=>0,
		// SHF Bands (Microwave)
		"23cm"=>0,
		"13cm"=>0,
		"9cm"=>0,
		"6cm"=>0,
		"3cm"=>0,
		"1.2cm"=>0,
		// EHF Bands (Millimeter wave)
		"6mm"=>0,
		"4mm"=>0,
		"2.5mm"=>0,
		"2mm"=>0,
		"1mm"=>0,
		// Special
		"SAT"=>0,
	);

	function get_user_bands($award = 'None') {
		$this->db->from('bands');
		$this->db->join('bandxuser', 'bandxuser.bandid = bands.id');
		$this->db->where('bandxuser.userid', $this->session->userdata('user_id'));
		$this->db->where('bandxuser.active', 1);

		if ($award != 'None' && $this->db->field_exists($award, 'bandxuser')) {
			$this->db->where('bandxuser.'.$award, 1);
		}

		$result = $this->db->get()->result();

		$results = array();

		foreach($result as $band) {
			array_push($results, $band->band);
		}

		return $results;
	}

	function get_all_bands() {
		$this->db->from('bands');

		$result = $this->db->get()->result();

		$results = array();

		foreach($result as $band) {
			$results['b'.strtoupper($band->band)] = array('CW' => $band->cw, 'SSB' => $band->ssb, 'DIGI' => $band->data);
		}

		return $results;
	}

	function get_user_bands_for_qso_entry($includeall = false) {
		$this->db->from('bands');
		$this->db->join('bandxuser', 'bandxuser.bandid = bands.id');
		$this->db->where('bandxuser.userid', $this->session->userdata('user_id'));
		if (!$includeall) {
			$this->db->where('bandxuser.active', 1);
		}
		$this->db->where('bands.bandgroup != "sat"');

		$result = $this->db->get()->result();

		$results = array();

		foreach($result as $band) {
			$results[$band->bandgroup][] = $band->band;
		}

		return $results;
	}

	function get_all_bands_for_user() {
		$this->db->from('bands');
		$this->db->join('bandxuser', 'bandxuser.bandid = bands.id');
		$this->db->where('bandxuser.userid', $this->session->userdata('user_id'));

		return $this->db->get()->result();
	}

	function get_user_bands_for_bandmap($includeall = false) {
		$this->db->from('bands');
		$this->db->join('bandxuser', 'bandxuser.bandid = bands.id');
		$this->db->where('bandxuser.userid', $this->session->userdata('user_id'));
		if (!$includeall) {
			$this->db->where('bandxuser.active', 1);
		}
		$this->db->where('bands.bandgroup != "sat"');

		$result = $this->db->get()->result();

		// Define typical band ranges (in kHz for bandmap display) in frequency order
		$band_ranges = array(
			// LF/MF Bands
			'2190m' => array('start' => 135700, 'end' => 137800, 'order' => 1),
			'630m' => array('start' => 472000, 'end' => 479000, 'order' => 2),
			'560m' => array('start' => 501000, 'end' => 504000, 'order' => 3),
			// HF Bands
			'160m' => array('start' => 1800, 'end' => 2000, 'order' => 4),
			'80m' => array('start' => 3500, 'end' => 4000, 'order' => 5),
			'60m' => array('start' => 5351500, 'end' => 5366500, 'order' => 6),
			'40m' => array('start' => 7000, 'end' => 7300, 'order' => 7),
			'30m' => array('start' => 10100, 'end' => 10150, 'order' => 8),
			'20m' => array('start' => 14000, 'end' => 14350, 'order' => 9),
			'17m' => array('start' => 18068, 'end' => 18168, 'order' => 10),
			'15m' => array('start' => 21000, 'end' => 21450, 'order' => 11),
			'12m' => array('start' => 24890, 'end' => 24990, 'order' => 12),
			'10m' => array('start' => 28000, 'end' => 29700, 'order' => 13),
			// VHF Bands
			'6m' => array('start' => 50000, 'end' => 54000, 'order' => 14),
			'4m' => array('start' => 70000, 'end' => 70500, 'order' => 15),
			'2m' => array('start' => 144000, 'end' => 148000, 'order' => 16),
			'1.25m' => array('start' => 222000, 'end' => 225000, 'order' => 17),
			// UHF Bands
			'70cm' => array('start' => 420000, 'end' => 450000, 'order' => 18),
			'33cm' => array('start' => 902000, 'end' => 928000, 'order' => 19),
			// SHF Bands (Microwave)
			'23cm' => array('start' => 1240000, 'end' => 1300000, 'order' => 20),
			'13cm' => array('start' => 2300000, 'end' => 2450000, 'order' => 21),
			'9cm' => array('start' => 3300000, 'end' => 3500000, 'order' => 22),
			'6cm' => array('start' => 5650000, 'end' => 5925000, 'order' => 23),
			'3cm' => array('start' => 10000000, 'end' => 10500000, 'order' => 24),
			'1.2cm' => array('start' => 24000000, 'end' => 24250000, 'order' => 25),
			// EHF Bands (Millimeter wave)
			'6mm' => array('start' => 47000000, 'end' => 47200000, 'order' => 26),
			'4mm' => array('start' => 75500000, 'end' => 81000000, 'order' => 27),
			'2.5mm' => array('start' => 119980000, 'end' => 120020000, 'order' => 28),
			'2mm' => array('start' => 142000000, 'end' => 149000000, 'order' => 29),
			'1mm' => array('start' => 241000000, 'end' => 250000000, 'order' => 30),
		);

		$results = array();

		foreach($result as $band) {
			if (isset($band_ranges[$band->band])) {
				$results[] = array(
					'band' => $band->band,
					'bandgroup' => $band->bandgroup,
					'start' => $band_ranges[$band->band]['start'],
					'end' => $band_ranges[$band->band]['end'],
					'order' => $band_ranges[$band->band]['order']
				);
			}
		}

		// Sort by frequency order
		usort($results, function($a, $b) {
			return $a['order'] - $b['order'];
		});

		return $results;
	}

	function all() {
		return $this->bandslots;
	}

	function get_worked_bands($award = 'None') {

		$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();
		}

		$location_list = implode(',', array_map('intval', $logbooks_locations_array));

		// get all worked slots from database
		$data = $this->db->query(
			"SELECT distinct LOWER(`COL_BAND`) as `COL_BAND` FROM `".$this->config->item('table_name')."` WHERE station_id in (" . $location_list . ") AND COL_PROP_MODE != \"SAT\""
		);
		$worked_slots = array();
		foreach($data->result() as $row){
			array_push($worked_slots, $row->COL_BAND);
		}

		$SAT_data = $this->db->query(
			"SELECT distinct LOWER(`COL_PROP_MODE`) as `COL_PROP_MODE` FROM `".$this->config->item('table_name')."` WHERE station_id in (" . $location_list . ") AND COL_PROP_MODE = \"SAT\""
		);

		foreach($SAT_data->result() as $row){
			array_push($worked_slots, strtoupper($row->COL_PROP_MODE));
		}

		$EME_data = $this->db->query(
			"SELECT distinct LOWER(`COL_PROP_MODE`) as `COL_PROP_MODE` FROM `".$this->config->item('table_name')."` WHERE station_id in (" . $location_list . ") AND COL_PROP_MODE = \"EME\""
		);

		foreach($EME_data->result() as $row){
			array_push($worked_slots, strtoupper($row->COL_PROP_MODE));
		}

		// bring worked-slots in order of defined $bandslots
		$bandslots = $this->get_user_bands($award);

		$results = array();
		foreach($bandslots as $slot) {
			if(in_array($slot, $worked_slots)) {
				array_push($results, $slot);
			}
		}

		if (in_array('SAT', $worked_slots) && !in_array('SAT', $results)) {
			array_push($results, 'SAT');
		}

		if (in_array('EME', $worked_slots) && !in_array('EME', $results)) {
			array_push($results, 'EME');
		}

		return $results;
	}

	function get_worked_bands_distances() {
		$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();
		}
		$location_list = implode(',', array_map('intval', $logbooks_locations_array));

        // get all worked slots from database
        $sql = "SELECT distinct LOWER(COL_BAND) as COL_BAND FROM ".$this->config->item('table_name')." WHERE station_id in (" . $location_list . ")";

        $data = $this->db->query($sql);
        $worked_slots = array();
        foreach($data->result() as $row){
            array_push($worked_slots, $row->COL_BAND);
        }

        // bring worked-slots in order of defined $bandslots
		$bandslots = $this->get_user_bands();

		$results = array();
		foreach($bandslots as $slot) {
			if(in_array($slot, $worked_slots)) {
				array_push($results, $slot);
			}
		}
        return $results;
    }

	function get_worked_sats() {
		$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();
		}

		$location_list = implode(',', array_map('intval', $logbooks_locations_array));

        // get all worked sats from database
        $sql = "SELECT distinct col_sat_name FROM ".$this->config->item('table_name')." WHERE station_id in (" . $location_list . ") and coalesce(col_sat_name, '') <> '' ORDER BY col_sat_name";

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

        $worked_sats = array();
        foreach($data->result() as $row){
            array_push($worked_sats, $row->col_sat_name);
        }

        return $worked_sats;
    }

	function get_worked_bands_dok() {
		$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();
		}

		$location_list = implode(',', array_map('intval', $logbooks_locations_array));

		// get all worked slots from database
		$data = $this->db->query(
			"SELECT distinct LOWER(`COL_BAND`) as `COL_BAND` FROM `".$this->config->item('table_name')."` WHERE station_id in (" . $location_list . ") AND COL_DARC_DOK IS NOT NULL AND COL_DARC_DOK != ''  AND COL_DXCC = 230 "
		);
		$worked_slots = array();
		foreach($data->result() as $row){
			array_push($worked_slots, $row->COL_BAND);
		}

		// bring worked-slots in order of defined $bandslots
		$bandslots = $this->get_user_bands('dok');

		$results = array();
		foreach($bandslots as $slot) {
			if(in_array($slot, $worked_slots)) {
				array_push($results, $slot);
			}
		}
		return $results;
	}

	function get_worked_powers() {
		$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();
		}

		$location_list = implode(',', array_map('intval', $logbooks_locations_array));

        // get all worked powers from database
        $sql = "SELECT distinct col_tx_pwr FROM ".$this->config->item('table_name')." WHERE station_id in (" . $location_list . ") ORDER BY col_tx_pwr";

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

        $worked_powers = array();
        foreach($data->result() as $row) array_push($worked_powers, $row->col_tx_pwr);

        return $worked_powers;
    }

	function activateall() {
        $data = array(
            'active' => '1',
        );
		$this->db->where('bandxuser.userid', $this->session->userdata('user_id'));

        $this->db->update('bandxuser', $data);

        return true;
    }

    function deactivateall() {
        $data = array(
            'active' => '0',
        );
		$this->db->where('bandxuser.userid', $this->session->userdata('user_id'));

        $this->db->update('bandxuser', $data);

        return true;
    }

	function delete($id) {
		// Clean ID
		$clean_id = $this->security->xss_clean($id);

		// Delete Mode
		$this->db->delete('bandxuser', array('id' => $clean_id));
	}

	function saveBand($id, $band) {
        $data = array(
			'active' 	 => $band['status']		== "true" ? '1' : '0',
			'cq' 		 => $band['cq'] 		== "true" ? '1' : '0',
			'dok' 		 => $band['dok'] 		== "true" ? '1' : '0',
			'dxcc' 		 => $band['dxcc'] 		== "true" ? '1' : '0',
			'iota' 		 => $band['iota'] 		== "true" ? '1' : '0',
			'pota' 		 => $band['pota'] 		== "true" ? '1' : '0',
			'sig' 		 => $band['sig'] 		== "true" ? '1' : '0',
			'sota'		 => $band['sota'] 		== "true" ? '1' : '0',
			'uscounties' => $band['uscounties'] == "true" ? '1' : '0',
			'was' 		 => $band['was'] 		== "true" ? '1' : '0',
			'wwff' 		 => $band['wwff'] 		== "true" ? '1' : '0',
			'vucc'		 => $band['vucc'] 		== "true" ? '1' : '0'
        );

		$this->db->where('bandxuser.userid', $this->session->userdata('user_id'));
        $this->db->where('bandxuser.id', $id);

        $this->db->update('bandxuser', $data);

        return true;
    }

	function saveBandAward($award, $status) {
		$data = array(
			$award 	 => $status == "true" ? '1' : '0',
        );

		$this->db->where('bandxuser.userid', $this->session->userdata('user_id'));

        $this->db->update('bandxuser', $data);

        return true;
    }

	function add() {
		$data = array(
			'band' 		=> xss_clean($this->input->post('band', true)),
			'bandgroup' => xss_clean($this->input->post('bandgroup', true)),
			'ssb'	 	=> xss_clean($this->input->post('ssbqrg', true)),
			'data' 		=> xss_clean($this->input->post('dataqrg', true)),
			'cw' 		=> xss_clean($this->input->post('cwqrg', true)),
		);

		$this->db->where('band', xss_clean($this->input->post('band', true)));
		$result = $this->db->get('bands');

		if ($result->num_rows() == 0) {
		   $this->db->insert('bands', $data);
		}

		$user_id = (int) $this->session->userdata('user_id');
		$sql = "insert into bandxuser (bandid, userid, active, cq, dok, dxcc, iota, pota, sig, sota, uscounties, was, wwff, vucc)
		select bands.id, ?, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 from bands where band = ? and not exists (select 1 from bandxuser where userid = ? and bandid = bands.id)";
		$this->db->query($sql, array($user_id, $data['band'], $user_id));
	}

	function getband($id) {
		$this->db->where('id', $id);
		return $this->db->get('bands');
	}

	function saveupdatedband($id, $band) {
		$data = array(
			'band' 		=> $band['band'],
			'bandgroup' => $band['bandgroup'],
			'ssb'	 	=> $band['ssbqrg'],
			'data' 		=> $band['dataqrg'],
			'cw' 		=> $band['cwqrg'],
        );

        $this->db->where('bands.id', $id);

        $this->db->update('bands', $data);

        return true;
	}

	function get_worked_bands_oqrs($station_id) {
		$station_id = (int) $station_id;

		// get all worked slots from database
		$data = $this->db->query(
			"SELECT distinct LOWER(`COL_BAND`) as `COL_BAND` FROM `".$this->config->item('table_name')."` WHERE station_id = ? AND COL_PROP_MODE != \"SAT\"",
			array($station_id)
		);
		$worked_slots = array();
		foreach($data->result() as $row){
			array_push($worked_slots, $row->COL_BAND);
		}

		$SAT_data = $this->db->query(
			"SELECT distinct LOWER(`COL_PROP_MODE`) as `COL_PROP_MODE` FROM `".$this->config->item('table_name')."` WHERE station_id = ? AND COL_PROP_MODE = \"SAT\"",
			array($station_id)
		);

		foreach($SAT_data->result() as $row){
			array_push($worked_slots, strtoupper($row->COL_PROP_MODE));
		}

		// php5
		usort(
			$worked_slots,
			function($b, $a) {
				sscanf($a, '%f%s', $ac, $ar);
				sscanf($b, '%f%s', $bc, $br);
				if ($ar == $br) {
					return ($ac < $bc) ? -1 : 1;
				}
				return ($ar < $br) ? -1 : 1;
			}
		);

		// Only for php7+
		// usort(
		// 	$worked_slots,
		// 	function($b, $a) {
		// 		sscanf($a, '%f%s', $ac, $ar);
		// 		sscanf($b, '%f%s', $bc, $br);
		// 		return ($ar == $br) ? $ac <=> $bc : $ar <=> $br;
		// 	}
		// );

		return $worked_slots;
	}
}

?>