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()
