public StringBuilder getPayloadItems() { String SQL = @"SELECT PayloadMapData.RFID, (TopLeftLat + BottomRightLat)/2 as Lat, (TopLeftLon + BottomRightLon)/2 as Lon, MSTR_Product.VIN, MSTR_Product.CarMake, MSTR_Product.Model, MSTR_Product.[Year], MSTR_Product.Color FROM PayloadMapData LEFT JOIN PayLoadYardGrid ON PayLoadYardGrid.YardID = PayloadMapData.YardID AND PayLoadYardGrid.RowNumber = (PayloadMapData.RowNumber - 1) AND PayLoadYardGrid.ColumnNumber = (PayloadMapData.ColumnNumber - 1) LEFT JOIN MSTR_Product ON MSTR_Product.RFID = PayloadMapData.RFID WHERE PayloadMapData.FlightUniqueID = '" + _FlightUniqueID + @"' ORDER BY PayloadMapData.RowNumber, PayloadMapData.ColumnNumber "; StringBuilder SB = new StringBuilder(); var Data = Util.getDBRows(SQL); foreach(var Row in Data) { SB.Append(@"<Placemark>"); SB.AppendFormat("<name>" + Row["RFID"] + "</name>"); SB.Append(@"<description>"); SB.Append("<![CDATA["); SB.AppendLine("VIN: <b>" + Row["VIN"] + "</b><br>"); SB.AppendLine("Make: <b>" + Row["CarMake"] + "</b><br>"); SB.AppendLine("Year: <b>" + Row["Year"] + "</b><br>"); SB.AppendLine("Color: <b>" + Row["Color"] + "</b>"); SB.Append("]]>"); SB.Append(@"</description>"); SB.Append(@"<Point> <coordinates>"); SB.AppendFormat("{0},{1}\n", Row["Lon"], Row["Lat"]); SB.Append(@"</coordinates> </Point> </Placemark>"); } return SB; }
public StringBuilder BuildCordinates(String SQL) { StringBuilder SB = new StringBuilder(); var Data = Util.getDBRows(SQL); foreach(var Row in Data) { SB.AppendLine(@" <Placemark> <name>Blue Line</name> <styleUrl>#GridInLine</styleUrl> <LineString> <altitudeMode>relative</altitudeMode> <coordinates>"); SB.AppendFormat("{0},{1},0 {2},{3},0\n", Row["sLon"], Row["sLat"], Row["eLon"], Row["eLat"]); SB.AppendLine(@"</coordinates> </LineString> </Placemark>"); } return SB; }
public static List <Dictionary <String, Object> > getParts(int DroneID = 0) { string SQL = "select\n" + " PartsName,\n" + " Model,\n" + " ISNULL(MSTR_Account.Name, '') as Supplier," + " M2M_DroneParts.Quantity,\n" + " M2M_DroneParts.PartsId as id " + "from\n" + " M2M_DroneParts\n" + "LEFT JOIN MSTR_Parts on\n" + " M2M_DroneParts.PartsId = MSTR_Parts.PartsId\n" + "LEFT JOIN MSTR_Account On\n" + " MSTR_Account.AccountId = MSTR_Parts.SupplierId\n" + "where\n" + " M2M_DroneParts.DroneId =" + DroneID; return(Util.getDBRows(SQL)); }
public String getAllowedLocation(int FlightID) { _DroneID = getDroneIDForFlight(FlightID); String dateSQL = @"select CONVERT(VARCHAR, FlightDate ,20) as FlightDate from DroneFlight where ID=" + FlightID; String CheckDate = Util.getDBVal(dateSQL); StringBuilder Cord = new StringBuilder(); String SQL = @"SELECT ApprovalID, Coordinates, InnerBoundaryCoord FROM [GCA_Approval] WHERE DroneID = " + _DroneID + @" AND '" + CheckDate + "' BETWEEN StartDate and EndDate"; var Rows = Util.getDBRows(SQL); foreach (var Row in Rows) { String Coordinates = Row["Coordinates"].ToString(); if (Cord.Length > 0) Cord.AppendLine(","); Cord.AppendLine("{"); Cord.AppendLine("Outer: ["); Cord.Append(toScriptArray(Coordinates)); Cord.AppendLine("]"); //Inner cordinates String InnerCoordinates = Row["InnerBoundaryCoord"].ToString(); Cord.AppendLine(","); Cord.AppendLine("Inner: ["); Cord.Append(toScriptArray(InnerCoordinates)); Cord.AppendLine("]"); Cord.AppendLine("}"); } return Cord.ToString(); }
public static List <Dictionary <String, Object> > getFileNames(int DroneID, String DocumentType) { string SQL = "SELECT\n" + " a.UploadedDate,\n" + " a.DocumentName,\n" + " SUBSTRING(\n" + " a.DocumentName, \n" + " CHARINDEX('~', a.DocumentName) + 1,\n" + " LEN(a.DocumentName)\n" + " ) as Name\n" + "from \n" + " DroneDocuments a\n" + "where\n" + " a.DroneID =" + DroneID + " AND\n" + " a.DocumentType='" + DocumentType + "'\n" + "Order by\n" + " a.DocumentName"; return(Util.getDBRows(SQL)); }
public StringBuilder BuildBoundary(String SQL) { StringBuilder SB = new StringBuilder(); var Data = Util.getDBRows(SQL); foreach(var Row in Data) { SB.AppendLine(@" <Placemark> <name>Blue Line</name> <styleUrl>#GridBorder</styleUrl> <LineString> <altitudeMode>relative</altitudeMode> <coordinates>"); SB.AppendFormat("{0},{1},0\n", Row["TopLeftLon"], Row["TopLeftLat"]); SB.AppendFormat("{0},{1},0\n", Row["TopRightLon"], Row["TopRightLat"]); SB.AppendFormat("{0},{1},0\n", Row["BottomRightLon"], Row["BottomRightLat"]); SB.AppendFormat("{0},{1},0\n", Row["BottomLeftLon"], Row["BottomLeftLat"]); SB.AppendFormat("{0},{1},0\n", Row["TopLeftLon"], Row["TopLeftLat"]); SB.AppendLine(@"</coordinates> </LineString> </Placemark>"); } return SB; }
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()