#!/bin/bash
dbserver="localhost"
dbuser="flighttrax"
dbpw="flighttrax"
dbtable="flighttrax"
clientid="1000000"
core="/root/file.txt"
dump="/tmp/dump.txt"
selectdate=`date -d "1 min ago" '+%Y/%m/%d,%H:%M'`
listener="localhost"
listenerport="30003"
ncpid=`ps waux | grep "nc $listener $listenerport"| grep -v grep | awk '{print $2}'`
dumppid=`ps waux | grep "dump1090"| grep -v grep | awk '{print $2}'`
log="/var/log/flighttrax.log"
echo "`date`" >> $log
echo "`date` - Starting script" >> $log
echo "`date` - Netcat pid is $ncpid" >> $log
echo "`date` - Dump1090 pid is $dumppid" >> $log
if [ -z $dumppid ]
then
echo "`date` - Dump1090 not started yet" >> $log
/root/dump1090/dump1090 --net --quiet &
echo "`date` - Started Dump1090" >> $log
exit
fi
if [ -z $ncpid ]
then
echo "`date` - Netcat not started yet" >> $log
nc $listener $listenerport >> $core
echo "`date` - Started NC" >> $log
exit
fi
echo "`date` - Copying last minute" >> $log
cat $core | grep $selectdate > $dump
echo "`date` - Calculating Entries" >> $log
entries=`wc -l $dump`
echo "`date` - Total of $entries to import" >> $log
echo "`date` - Starting FOR statement" >> $log
for i in `cat $dump | awk -F ',' '{print $5}' | sort | uniq | sed '/^$/d'`
do
reg=$i
flightnum=`cat $dump | grep $i | awk -F ',' '{print $11}'| sort| uniq | sed '/^$/d'|awk '{print $1}'|sed 's/\r//'`
for i in `cat $dump | grep $reg | awk -F ',' '{print $7","$8","$15","$16","$12}'| sort| uniq | sed '/,,/d'`
do
date=`echo $i| awk -F ',' '{print $1}' | sed 's#/#-#g'`
time=`echo $i| awk -F ',' '{print $2}'`
lat=`echo $i| awk -F ',' '{print $3}'`
long=`echo $i| awk -F ',' '{print $4}'`
alt=`echo $i| awk -F ',' '{print $5}'`
echo "$reg,$flightnum,$date,$time,$lat,$long,$alt"
mysql -uflighttrax -pflighttrax flighttrax -e "INSERT INTO flighttrax VALUES ('','$clientid','$reg','$flightnum','$date $time','$lat','$long','$alt');"
done
done
echo "`date` - For completed" >> $log
echo "`date` - Deleting temp files" >> $log
rm $dump
echo "`date` - Done." >> $log
