$cleanid = explode(" ", $cleandesc[$idkey]); $cleanspeed = explode(" ", $cleandesc[$speedkey]); $kvhdata[$index]['name'] = trim($cleanname[1]); $kvhdata[$index]['latitude'] = $cleancoords[1]; $kvhdata[$index]['longitude'] = $cleancoords[0]; $kvhdata[$index]['speed'] = $cleanspeed[3] * 1.852; $kvhdata[$index]['ipaddr'] = substr($cleanname[0], 0, -7); $kvhdata[$index]['id'] = substr($cleanid[2], 0, -3); $kvhdata[$index]['status'] = $cleanstatus; if ($statuscode[4] == 'In') { $kvhdata[$index]['statuscode'] = "40000"; } else { $kvhdata[$index]['statuscode'] = "40002"; } $kvhdata[$index]['notes'] = strip_tags($cleandesc[$hubkey]) . "<br />" . strip_tags($cleandesc[$speedkey]) . "<br />" . strip_tags($cleandesc[$notekey1]) . "<br />" . strip_tags($cleandesc[$notekey2]) . "<br />" . strip_tags($cleandesc[$notekey3]) . "<br />" . strip_tags($cleandesc[$notekey4]) . "<br />" . strip_tags($cleandesc[$notekey5]); $kvhdata[$index]['address'] = handleGeozone($link, substr($cleanid[2], 0, -3), $cleancoords[1], $cleancoords[0]); $kvhdata[$index]['heading'] = handleHeading($link, substr($cleanid[2], 0, -3), $cleancoords[1], $cleancoords[0]); $index++; } //Build Device and Location Insert Query $index = 0; $devices_in_feed = ""; foreach ($kvhdata as $data) { $insertquery[$index] = "INSERT INTO Device (accountID,deviceID,groupID,equipmentType,vehicleID,uniqueID,displayName,description,ipAddressCurrent,isActive,lastUpdateTime,lastInputState,notes) VALUES ('gtg','" . $data['id'] . "','kvh','netmodem','" . $data['name'] . "','" . $data['id'] . "','" . $data['name'] . "','" . $data['name'] . "','" . $data['ipaddr'] . "',1," . time() . "," . $data['statuscode'] . ",'" . $data['status'] . "<br />" . $data['notes'] . "') ON DUPLICATE KEY UPDATE groupID=VALUES(groupID),lastUpdateTime=VALUES(lastUpdateTime),ipAddressCurrent=VALUES(ipAddressCurrent),lastInputState=VALUES(lastInputState),notes=VALUES(notes);"; //$insertquery[$index] = "REPLACE INTO Device SET accountID='gtg',deviceID='".$data['id']."',groupID='kvh',equipmentType='netmodem',vehicleID='".$data['name']."',uniqueID='".$data['id']."',displayName='".$data['name']."',description='".$data['name']."',ipAddressCurrent='".$data['ipaddr']."',isActive=1,lastUpdateTime=".time().",lastInputState=".$data['statuscode'].",notes='".$data['status']."<br />".$data['notes']."';"; $index++; $insertquery[$index] = "REPLACE INTO EventData SET accountID='gtg',deviceID='" . $data['id'] . "',timestamp=" . time() . ",statusCode=61472,latitude=" . $data['latitude'] . ",longitude=" . $data['longitude'] . ",speedKPH=" . $data['speed'] . ",address='" . $data['address'] . "',heading=" . $data['heading'] . ";"; $index++; $insertquery[$index] = "REPLACE INTO EventData SET accountID='gtg',deviceID='" . $data['id'] . "',timestamp=" . time() . ",statusCode=" . $data['statuscode'] . ",rawData='" . $data['status'] . "';"; $index++; $devices_in_feed .= "'" . $data['id'] . "',";
if ($nm['downsnr'] == "") { $nm['downsnr'] = 0; } if ($nm['upsnr'] == "") { $nm['upsnr'] = 0; } if ($nm['txpower'] == "") { $nm['txpower'] = 0; } if ($nm['nmstate'] >= 2) { $reason = $nm['nmalarms']; } else { $reason = $nm['nmwarnings']; } if (stripos($gid[0], "Bordelon-") !== false || $gid[0] == "L&M") { $addr = handleGeozone($link, $nm['nmid'], $lat, $long); $speedhead = handleSpeed($link, $nm['nmid'], $lat, $long); } $deviceinsertquery .= "('gtg'," . $nm['nmid'] . ",'" . $gid[0] . "','netmodem','" . $nm['netdid'] . "','" . $nm['nmid'] . "','" . $nm['nmname'] . "','" . $nm['nmname'] . "',1," . time() . "," . $stati[$nm['nmstate']] . ",'" . $nm['ethipadr'] . "','" . $reason . "'," . $nm['downsnr'] . "," . $nm['upsnr'] . "," . $nm['txpower'] . "," . $latency . "),"; if ($n > 0) { $geolocinsertquery .= "('gtg'," . $nm['nmid'] . "," . time() . ",61472," . $lat . "," . $long . ",'" . $addr . "'," . $speedhead[1] . "," . $speedhead[0] . "),"; } $statusinsertquery .= "('gtg'," . $nm['nmid'] . "," . time() . "," . $stati[$nm['nmstate']] . ",'" . $reason . "'),"; } } $deviceinsertquery = substr($deviceinsertquery, 0, -1) . " ON DUPLICATE KEY UPDATE groupID=VALUES(groupID),lastUpdateTime=VALUES(lastUpdateTime),displayName=VALUES(displayName),description=VALUES(description),lastInputState=VALUES(lastInputState),ipAddressCurrent=VALUES(ipAddressCurrent),lastReason=VALUES(lastReason),lastSnrCalDown=VALUES(lastSnrCalDown),lastSnrCalUp=VALUES(lastSnrCalUp),lastPowerDbm=VALUES(lastPowerDbm),lastRtt=VALUES(lastRtt)"; $geolocinsertquery = substr($geolocinsertquery, 0, -1); $statusinsertquery = substr($statusinsertquery, 0, -1); // Run Data Inserts $res = mysql_query($deviceinsertquery); $res = mysql_query($geolocinsertquery);
$cleanid = explode(" ", $cleandesc[$idkey]); $cleanspeed = explode(" ", $cleandesc[$speedkey]); $kvhdata[$index]['name'] = trim($cleanname[1]); $kvhdata[$index]['latitude'] = $cleancoords[1]; $kvhdata[$index]['longitude'] = $cleancoords[0]; $kvhdata[$index]['speed'] = $cleanspeed[3] * 1.852; $kvhdata[$index]['ipaddr'] = substr($cleanname[0], 0, -7); $kvhdata[$index]['id'] = substr($cleanid[2], 0, -3); $kvhdata[$index]['status'] = $cleanstatus; if ($statuscode[4] == 'In') { $kvhdata[$index]['statuscode'] = "40000"; } else { $kvhdata[$index]['statuscode'] = "40002"; } $kvhdata[$index]['notes'] = strip_tags($cleandesc[$hubkey]) . "<br />" . strip_tags($cleandesc[$speedkey]) . "<br />" . strip_tags($cleandesc[$notekey1]) . "<br />" . strip_tags($cleandesc[$notekey2]) . "<br />" . strip_tags($cleandesc[$notekey3]) . "<br />" . strip_tags($cleandesc[$notekey4]) . "<br />" . strip_tags($cleandesc[$notekey5]); $kvhdata[$index]['address'] = handleGeozone($link, strip_tags($cleanid[2]), $cleancoords[1], $cleancoords[0]); $kvhdata[$index]['heading'] = handleHeading($link, strip_tags($cleanid[2]), $cleancoords[1], $cleancoords[0]); $index++; } //Build Device and Location Insert Query $index = 0; print_r($kvhdata); foreach ($kvhdata as $data) { $insertquery[$index] = "INSERT INTO Device (accountID,deviceID,groupID,equipmentType,vehicleID,uniqueID,displayName,description,isActive,lastUpdateTime,lastInputState,notes) VALUES ('gtg','" . $data['id'] . "','kvh','netmodem','" . $data['name'] . "','" . $data['id'] . "','" . $data['name'] . "','" . $data['name'] . "',1," . time() . "," . $data['statuscode'] . ",'" . $data['status'] . "<br />" . $data['notes'] . "') ON DUPLICATE KEY UPDATE groupID=VALUES(groupID),lastUpdateTime=VALUES(lastUpdateTime),lastInputState=VALUES(lastInputState),notes=VALUES(notes);"; $index++; $insertquery[$index] = "REPLACE INTO EventData SET accountID='gtg',deviceID='" . $data['id'] . "',timestamp=" . time() . ",statusCode=61472,latitude=" . $data['latitude'] . ",longitude=" . $data['longitude'] . ",speedKPH=" . $data['speed'] . ",address='" . $data['address'] . "',heading=" . $data['heading'] . ";"; $index++; $insertquery[$index] = "REPLACE INTO EventData SET accountID='gtg',deviceID='" . $data['id'] . "',timestamp=" . time() . ",statusCode=" . $data['statuscode'] . ",rawData='" . $data['status'] . "';"; $index++; } //Perform Device and location Inserts