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