}//getBulkInsertHeader()
private String getBulkInsertRow(String Line, int DroneID, int FlightID, int BBFlightID) {
String[] Data = Line.Split(',');
return "(\n" +
" " + DroneID + ",\n" + //[DroneId],\n" +
" " + FlightID + ",\n" + //[FlightID],\n" +
" " + BBFlightID + ",\n" + //[BBFlightID],\n" +
" " + Util.toInt(Data[0]) + ",\n" + //[RecordNumber],\n" +
" " + Util.toInt(Data[1]) + ",\n" + //[DroneSerialID],\n"
" " + Util.toDecimal(Data[2]) + ",\n" + //[Latitude],\n" +
" " + Util.toDecimal(Data[3]) + ",\n" + //[Longitude],\n" +
" " + Util.toDecimal(Data[4]) + ",\n" + //[Altitude],\n" +
" " + Util.toDecimal(Data[5]) + ",\n" + //[Speed],\n" +
" " + Util.toDecimal(Data[6]) + ",\n" + //[FixQuality],\n" +
" " + Util.toInt(Data[7]) + ",\n" + //[Satellites],\n" +
" '" + Util.toDate(Data[8]) + "',\n" + //[ReadTime],\n" +
" " + Util.toDecimal(Data[9]) + ",\n" + //[Pitch],\n" +
" " + Util.toDecimal(Data[10]) + ",\n" + //[Roll],\n" +
" " + Util.toDecimal(Data[11]) + ",\n" + //[Heading],\n" +
" " + Util.toDecimal(Data[12]) + "\n" + //[TotalFlightTime]\n"
")";
}//getBulkInsertRow()
}//getFlights()
public void saveTechnicalLog(HttpRequestBase Request) {
String SQL;
String[] Index = Request["theFlight.Index"].Split(',');
foreach (String RowID in Index) {
if (RowID == "SLNO") continue;
String FlightID = Request["theFlight[" + RowID + "].ID"];
String LogTakeOffTime = Request["theFlight[" + RowID + "].FlightDate"] + " " +
Request["theFlight[" + RowID + "].LogTakeOffTime"];
String LogLandingTime = Request["theFlight[" + RowID + "].FlightDate"] + " " +
Request["theFlight[" + RowID + "].LogLandingTime"];
if (FlightID == "0") {
//create new flight for drone
SQL =
"INSERT INTO DroneFlight (\n" +
" DroneID,\n" +
" PilotID,\n" +
" FlightDate,\n" +
" CreatedOn,\n" +
" CreatedBy,\n" +
" [LogFrom],\n" +
" [LogTo],\n" +
" [LogTakeOffTime],\n" +
" [LogLandingTime],\n" +
" [LogBattery1ID],\n" +
" [LogBattery1StartV],\n" +
" [LogBattery1EndV],\n" +
" [LogBattery2ID],\n" +
" [LogBattery2StartV],\n" +
" [LogBattery2EndV],\n" +
" [isLogged],\n" +
" [LogDateTime],\n" +
" [LogCreatedBy],\n" +
" [Descrepency],\n" +
" [ActionTaken]\n" +
") VALUES (" +
" " + Util.toInt(Request["DroneID"]) + ",\n" +
" " + Util.getLoginUserID() + ",\n" +
" '" + Util.toSQLDate(LogTakeOffTime) + "',\n" +
" GETDATE(),\n" +
" " + Util.getLoginUserID() + ",\n" +
" '" + Request["theFlight[" + RowID + "].LogFrom"] + "',\n" +
" '" + Request["theFlight[" + RowID + "].LogTo"] + "',\n" +
" '" + Util.toSQLDate(LogTakeOffTime) + "',\n" +
" '" + Util.toSQLDate(LogLandingTime) + "',\n" +
" '" + Request["theFlight[" + RowID + "].LogBattery1ID"] + "',\n" +
" " + Util.toDecimal(Request["theFlight[" + RowID + "].LogBattery1StartV"]) + ",\n" +
" " + Util.toDecimal(Request["theFlight[" + RowID + "].LogBattery1EndV"]) + ",\n" +
" '" + Request["theFlight[" + RowID + "].LogBattery2ID"] + "',\n" +
" " + Util.toDecimal(Request["theFlight[" + RowID + "].LogBattery2StartV"]) + ",\n" +
" " + Util.toDecimal(Request["theFlight[" + RowID + "].LogBattery2EndV"]) + ",\n" +
" 1,\n" +
" GETDATE(),\n" +
" " + Util.getLoginUserID() + ",\n" +
" '" + Request["theFlight[" + RowID + "].Descrepency"] + "',\n" +
" '" + Request["theFlight[" + RowID + "].ActionTaken"] + "'\n" +
")";
Util.doSQL(SQL);
} else { //if(FlightID == "0")
//Update flight information for technical Log
SQL =
"UPDATE\n" +
" DroneFlight\n" +
"SET\n" +
" [LogFrom] = '" + Request["theFlight[" + RowID + "].LogFrom"] + "',\n" +
" [LogTo] = '" + Request["theFlight[" + RowID + "].LogTo"] + "',\n" +
" [LogTakeOffTime] = '" + Util.toSQLDate(LogTakeOffTime) + "',\n" +
" [LogLandingTime] = '" + Util.toSQLDate(LogLandingTime) + "',\n" +
" [LogBattery1ID] = '" + Request["theFlight[" + RowID + "].LogBattery1ID"] + "',\n" +
" [LogBattery1StartV] = " + Util.toDecimal(Request["theFlight[" + RowID + "].LogBattery1StartV"]) + ",\n" +
" [LogBattery1EndV] = " + Util.toDecimal(Request["theFlight[" + RowID + "].LogBattery1EndV"]) + ",\n" +
" [LogBattery2ID] = '" + Request["theFlight[" + RowID + "].LogBattery2ID"] + "',\n" +
" [LogBattery2StartV] = " + Util.toDecimal(Request["theFlight[" + RowID + "].LogBattery2StartV"]) + ",\n" +
" [LogBattery2EndV] = " + Util.toDecimal(Request["theFlight[" + RowID + "].LogBattery2EndV"]) + ",\n" +
" [isLogged] = 1,\n" +
" [LogDateTime] = GETDATE(),\n" +
" LogCreatedBy=" + Util.getLoginUserID() + ",\n" +
" Descrepency = '" + Request["theFlight[" + RowID + "].Descrepency"] + "',\n" +
" ActionTaken = '" + Request["theFlight[" + RowID + "].ActionTaken"] + "'\n" +
"WHERE\n" +
" ID=" + FlightID;
Util.doSQL(SQL);
}//if(FlightID == "0")
}//foreach(Index)
}//saveTechnicalLog()
public List<DroneFlight> getLogFlights(List<DroneFlight> Flights, int FlightID = 0) {
String SQL = FlightID == 0 ?
"SELECT\n" +
" DroneFlight.ID,\n" +
" LogFrom,\n" +
" LogTo,\n" +
" Convert(Varchar, Min(FlightMapData.ReadTime), 111) as 'FlightDate(UTC)',\n" +
" Convert(Varchar, Min(FlightMapData.ReadTime), 108) as LogTakeOffTime,\n" +
" Convert(Varchar, Max(FlightMapData.ReadTime), 108) as LogLandingTime,\n" +
" Convert(Varchar, DATEADD(\n" +
" Minute,\n" +
" DATEDIFF(MINUTE, Min(FlightMapData.ReadTime), Max(FlightMapData.ReadTime)),\n" +
" '2000-01-01 00:00:00'), 108) as Duration,\n" +
" FlightMapData.BBFlightID,\n" +
" '' as LogBattery1ID,\n" +
" '' as LogBattery1StartV,\n" +
" '' as LogBattery1EndV,\n" +
" '' as LogBattery2ID,\n" +
" '' as LogBattery2StartV,\n" +
" '' as LogBattery2EndV,\n" +
" '' as Descrepency,\n" +
" '' as ActionTaken\n" +
"From\n" +
" DroneFlight\n" +
"LEFT JOIN FlightMapData On\n" +
" FlightMapData.FlightID = DroneFlight.ID\n" +
"WHERE\n" +
" DroneFlight.DroneID = " + DroneID.ToString() + " AND\n" +
" (isLogged IS NULL OR isLogged = 0)\n" +
"GROUP BY\n" +
" DroneFlight.ID,\n" +
" DroneFlight.DroneID,\n" +
" FlightMapData.BBFlightID,\n" +
" LogFrom,\n" +
" LogTo\n"
:
"SELECT\n" +
" DroneFlight.ID,\n" +
" LogFrom,\n" +
" LogTo,\n" +
" Convert(Varchar(11), FlightDate, 111) as 'FlightDate(UTC)',\n" +
" Convert(Varchar, LogTakeOffTime, 108) as LogTakeOffTime,\n" +
" Convert(Varchar, LogLandingTime, 108) as LogLandingTime,\n" +
" Convert(Varchar, DATEADD(\n" +
" Minute,\n" +
" DATEDIFF(MINUTE, LogTakeOffTime, LogLandingTime),\n" +
" '2000-01-01 00:00:00'), 104) as Duration,\n" +
" LogBattery1ID,\n" +
" LogBattery1StartV,\n" +
" LogBattery1EndV,\n" +
" LogBattery2ID,\n" +
" LogBattery2StartV,\n" +
" LogBattery2EndV,\n" +
" Descrepency,\n" +
" ActionTaken,\n" +
" '0' as BBFlightID\n" +
"From\n" +
" DroneFlight\n" +
"WHERE\n" +
" ID=" + FlightID;
var Rows = Util.getDBRows(SQL);
foreach (var Row in Rows) {
DroneFlight Flight = new DroneFlight() {
ID = Util.toInt(Row["ID"].ToString()),
LogFrom = Row["LogFrom"].ToString(),
LogTo = Row["LogFrom"].ToString(),
LogTakeOffTime = Util.toDate(Row["FlightDate"].ToString() + " " + Row["LogTakeOffTime"].ToString()),
LogLandingTime = Util.toDate(Row["FlightDate"].ToString() + " " + Row["LogLandingTime"].ToString()),
FlightDate = Util.toDate(Row["FlightDate"].ToString()),
LogBattery1ID = Row["LogBattery1ID"].ToString(),
LogBattery1StartV = Util.toDecimal(Row["LogBattery1StartV"].ToString()),
LogBattery1EndV = Util.toDecimal(Row["LogBattery1EndV"].ToString()),
LogBattery2ID = Row["LogBattery2ID"].ToString(),
LogBattery2StartV = Util.toDecimal(Row["LogBattery2StartV"].ToString()),
LogBattery2EndV = Util.toDecimal(Row["LogBattery2EndV"].ToString()),
Descrepency = Row["Descrepency"].ToString(),
ActionTaken = Row["ActionTaken"].ToString()
};
Flights.Add(Flight);
}
return Flights;
}//getFlights()
public static WeatherViewModel getLocalWeather(String UserIP)
{
String JSonContent = String.Empty;
int DelayHours = 0;
//If local server set it to dubai address
MaxMind.GeoIP2.Responses.CityResponse city = new MaxMind.GeoIP2.Responses.CityResponse();
var WeatherView = new WeatherViewModel();
WeatherView.Forecast = new List <Forcast>();
String DatabaseLocation = HttpContext.Current.Server.MapPath("/GeoLiteCity/GeoLite2-City.mmdb");
using (var reader = new DatabaseReader(DatabaseLocation)) {
// Replace "City" with the appropriate method for your database, e.g.,
// "Country".
try {
city = reader.City(UserIP);
WeatherView.Country = city.Country.Name; // 'United States'
WeatherView.City = city.City.Name;
} catch {
//do any error processing
}
}//using(var reader)
if (String.IsNullOrEmpty(WeatherView.Country))
{
WeatherView.Country = "United Arab Emirates";
}
if (String.IsNullOrEmpty(WeatherView.City))
{
WeatherView.City = "Dubai";
}
do
{
String CacheFile = getWeatherFile(WeatherView.Country, WeatherView.City, DelayHours);
if (!File.Exists(CacheFile))
{
JSonContent = DownloadWeatherInfo(WeatherView.Country, WeatherView.City, CacheFile);
}
else
{
JSonContent = File.ReadAllText(CacheFile);
}
DelayHours = DelayHours - 4;
} while (JSonContent.Length <= 10);
dynamic WeatherInfo = System.Web.Helpers.Json.Decode(JSonContent);
if (WeatherInfo != null)
{
var WeatherNow = WeatherInfo.data.current_condition[0];
WeatherView.ConditionText = WeatherNow.weatherDesc[0].value;
WeatherView.ConditionCode = WeatherNow.weatherCode;
WeatherView.Speed = WeatherNow.windspeedKmph;
WeatherView.ConditionTemperature = Util.toDouble(WeatherNow.temp_C).ToString("0.0");
if (WeatherView.ConditionTemperature == "0.0")
{
Double Temp = (Util.toDouble(WeatherNow.temp_F) - 32) / 1.8;
WeatherView.ConditionTemperature = Temp.ToString("0.0");
}
WeatherView.TemperatureUnit = "°C";
WeatherView.Pressure = Util.toDouble(WeatherNow.pressure);
WeatherView.Wind = WeatherNow.windspeedKmph;
WeatherView.Direction = WeatherNow.winddirDegree;
WeatherView.Visibility = Util.toDouble(WeatherNow.visibility);
WeatherView.Humidity = WeatherNow.humidity;
foreach (var ForcastDay in WeatherInfo.data.weather)
{
var thisForcast = new Forcast {
Code = Util.toInt(ForcastDay.hourly[3].weatherCode),
Date = (DateTime.Parse(ForcastDay.date)).ToString("dd MMM"),
status = ForcastDay.hourly[3].weatherDesc[0].value,
TempHigh = ForcastDay.maxtempC,
TempLow = ForcastDay.mintempC
};
WeatherView.Forecast.Add(thisForcast);
}
}
//var intAddress = BitConverter.ToInt64(IPAddress.Parse(UserIP).GetAddressBytes(), 0);
return(WeatherView);
}
private String setOrderFilterPaging(String SQL)
{
//http://www.codeproject.com/Articles/32524/SQL-Parser
setColumnMapping();
String SearchFilter = getQueryFilter();
String SearchOrderBy = getQueryOrder();
Parser.SqlParser myParser = new Parser.SqlParser();
myParser.Parse(SQL);
//Add Required Filter
if (SearchFilter != "")
{
if (string.IsNullOrEmpty(myParser.WhereClause))
{
myParser.WhereClause = SearchFilter;
}
else
{
myParser.WhereClause += " AND " + SearchFilter;
} //if string.IsNullOrEmpty(myParser.WhereClause)
} //if (SearchFilter != "")
//Set Paging for SQL Query
String OrderColumn = Request["order[0][column]"];
String OrderDir = Request["order[0][dir]"];
if (OrderDir != "asc")
{
OrderDir = "desc";
}
String OrderField = ColumDef.ElementAt(Util.toInt(OrderColumn));
int StartAt = Util.toInt(Request["start"]);
int RowLength = Util.toInt(Request["length"]);
myParser.OrderByClause = "";
SQL = myParser.ToText();
//Replace only first instance of SQL with RowNumber()
var regex = new Regex(Regex.Escape("SELECT"));
String RowNumber = "SELECT ROW_NUMBER() OVER (ORDER BY " + ColumnMapping[OrderField] + " " + OrderDir + ") AS _RowNumber,\n";
SQL = regex.Replace(SQL, RowNumber, 1);
String newSQL = "SELECT * FROM (\n" +
SQL +
") as QueryTable\n" +
"WHERE\n" +
" _RowNumber > " + StartAt + " AND _RowNumber <= " + (RowLength + StartAt) + "\n" +
"ORDER BY\n" +
SearchOrderBy;
return(newSQL);
/*
* //Add Order BY
* if (SearchOrderBy != "") {
* myParser.OrderByClause = SearchOrderBy;
* }
*
* return myParser.ToText();
*/
}
