/**
* @param array $params
* @param array $select
* @param null|int $limit
* @param null|string $order
* @return array
*/
public function getNetsByParams($params, $select = array('*'), $limit = null, $order = null)
{
$SQLselect = $this->buildSelect($select);
$SQLwhere = array();
$pdoParams = array();
foreach ($params as $p => $pv) {
switch ($p) {
case 'coords':
$pdoParams[':latStart'] = $pv->getLatStart();
$SQLwhere[] = 'latitude > :latStart';
$pdoParams[':latEnd'] = $pv->getLatEnd();
$SQLwhere[] = 'latitude < :latEnd';
$pdoParams[':lonStart'] = $pv->getLonStart();
$SQLwhere[] = 'longitude > :lonStart';
$pdoParams[':lonEnd'] = $pv->getLonEnd();
$SQLwhere[] = 'longitude < :lonEnd';
break;
case 'ssid':
$pdoParams[':ssid'] = '%' . $pv . '%';
$SQLwhere[] = 'ssid LIKE :ssid';
break;
case 'mac':
$pdoParams[':mac'] = '%' . MyUtils::macSeparator2Colon($pv) . '%';
$SQLwhere[] = 'mac LIKE :mac';
break;
default:
$pdoParams[":" . $p] = $pv;
$SQLwhere[] = $p . " = :{$p}";
}
}
$where = implode(' AND ', $SQLwhere);
$sql = "SELECT " . $SQLselect . " FROM " . self::TABLE . " WHERE " . $where;
if ($order) {
$sql .= " ORDER BY " . $order;
}
if ($limit) {
$sql .= " LIMIT " . intval($limit);
}
$sth = $this->pdo->prepare($sql);
foreach ($pdoParams as $p => $v) {
$sth->bindValue($p, $v);
}
$sth->execute();
$data = $sth->fetchAll(\PDO::FETCH_ASSOC);
return $data;
}
/**
* @param array $line
* @return Wifi
*/
public function parseLine($line)
{
$wifi = new Wifi();
$wifi->setMac(MyUtils::macSeparator2Colon($line['netid']));
$wifi->setSsid($line['ssid'] ? $line['ssid'] : "");
$wifi->setComment(trim($line['comment']));
$wifi->setName(trim($line['name']));
$wifi->setType($line['type']);
$wifi->setFreenet($line['freenet']);
$wifi->setPaynet($line['paynet']);
$wifi->setFirsttime(new DateTime($line['firsttime']));
$wifi->setLasttime(new DateTime($line['lasttime']));
$wifi->setFlags($line['flags']);
$wifi->setWep($line['wep']);
$wifi->setLatitude((double) $line['trilat']);
$wifi->setLongitude((double) $line['trilong']);
$wifi->setLastupdt($line['lastupdt']);
$wifi->setChannel((int) $line['channel']);
$wifi->setBcninterval($line['bcninterval']);
$wifi->setQos((int) $line['qos']);
$wifi->setSource(self::ID_SOURCE);
return $wifi;
}
/**
* change Wi-Fi object to associative array
*
* @param Wifi $wifi
* @return array
*/
private function prepareArrayForDB(Wifi $wifi)
{
$wifi->synchronizeSecurity();
$array = array("id_source" => $wifi->getSource(), "date_added" => date("Y-m-d"), "mac" => MyUtils::macSeparator2Colon($wifi->getMac()), "ssid" => $wifi->getSsid(), "sec" => $wifi->getSec(), "latitude" => $wifi->getLatitude(), "longitude" => $wifi->getLongitude(), "altitude" => $wifi->getAltitude(), "comment" => $wifi->getComment(), "name" => $wifi->getName(), "type" => $wifi->getType(), "freenet" => $wifi->getFreenet(), "paynet" => $wifi->getPaynet(), "firsttime" => $wifi->getFirsttime(), "lasttime" => $wifi->getLasttime(), "flags" => $wifi->getFlags(), "wep" => $wifi->getWep(), "lastupdt" => $wifi->getLastupdt(), "channel" => $wifi->getChannel(), "bcninterval" => $wifi->getBcninterval(), "qos" => $wifi->getQos(), "accuracy" => $wifi->getAccuracy(), "calculated" => $wifi->getCalculated());
return $array;
}
/**
* accuracy page
*/
public function renderAccuracy()
{
if ($this->getHttpRequest()->getQuery("mac") != "" && $this->getHttpRequest()->getQuery("r_latitude") != "" && $this->getHttpRequest()->getQuery("r_longitude") != "" && MyUtils::isMacAddress($this->getHttpRequest()->getQuery("mac"))) {
$mac = MyUtils::macSeparator2Colon($this->getHttpRequest()->getQuery("mac"));
$tableData = $this->wifiManager->getDistanceFromOriginalGPS($mac, doubleval($this->getHttpRequest()->getQuery("r_latitude")), doubleval($this->getHttpRequest()->getQuery("r_longitude")));
$data = array();
foreach ($tableData as $td) {
$coords = new Coords($td["latitude"], $this->getHttpRequest()->getQuery("r_latitude"), $td["longitude"], $this->getHttpRequest()->getQuery("r_longitude"));
$inM = $coords->getDistanceInMetres();
$arr = $td;
$arr["inM"] = $inM;
$data[] = $arr;
}
usort($data, "self::Sort");
$chWigleMin = PHP_INT_MAX;
$chWigleMax = 0;
$chWigleTotal = 0;
$chGoogleMin = PHP_INT_MAX;
$chGoogleMax = 0;
$chGoogleTotal = 0;
$chWigleAvg = 0;
$wigleCount = 0;
$wifileaksCount = 0;
$wifileaksTotal = 0;
$googleCount = 0;
foreach ($data as $d) {
if ($d["id_source"] == WifileaksDownload::ID_SOURCE) {
$wifileaksCount++;
$wifileaksTotal += $d["inM"];
}
if ($d["id_source"] == WigleDownload::ID_SOURCE) {
$wigleCount++;
if ($chWigleMin > $d["inM"]) {
$chWigleMin = $d["inM"];
}
if ($chWigleMax < $d["inM"]) {
$chWigleMax = $d["inM"];
}
$chWigleTotal += $d["inM"];
if ($d["calculated"] == 1) {
$chWigleAvg = $d["inM"];
}
}
if ($d["id_source"] == GoogleDownload::ID_SOURCE) {
$googleCount++;
if ($chGoogleMin > $d["inM"]) {
$chGoogleMin = $d["inM"];
}
if ($chGoogleMax < $d["inM"]) {
$chGoogleMax = $d["inM"];
}
$chGoogleTotal += $d["inM"];
}
}
if ($chWigleAvg == 0) {
$chWigleAvg = $chWigleTotal / $wigleCount;
}
$chGoogleAvg = $chGoogleTotal / $googleCount;
$chWifileaks = $wifileaksTotal / $wifileaksCount;
$this->template->chWifileaks = $chWifileaks;
$this->template->chWigleMin = $chWigleMin;
$this->template->chWigleMax = $chWigleMax;
$this->template->chWigleAvg = $chWigleAvg;
$this->template->chGoogleMin = $chGoogleMin;
$this->template->chGoogleMax = $chGoogleMax;
$this->template->chGoogleAvg = $chGoogleAvg;
$this->template->table = $data;
}
}
