}//public String getUAS public String getPilots(String Term) { StringBuilder SQL = new StringBuilder(); SQL.AppendLine("SELECT "); SQL.AppendLine(" MSTR_User.UserID as value, "); SQL.AppendLine(" MSTR_User.FirstName + ' ' + MSTR_User.LastName as label "); SQL.AppendLine("FROM "); SQL.AppendLine(" MSTR_User"); SQL.AppendLine("WHERE \n"); SQL.AppendLine(" MSTR_User.IsPilot = 1"); if (User.hasAccess("DRONE.VIEWALL") || User.hasAccess("PILOT")) { } else { SQL.AppendLine(" AND MSTR_User.AccountID = " + Util.getAccountID()); } if (!String.IsNullOrEmpty(Term)) { SQL.AppendLine(" AND ("); SQL.AppendLine(" MSTR_User.FirstName LIKE '%" + Term + "%' OR"); SQL.AppendLine(" MSTR_User.LastName LIKE '%" + Term + "%' )"); } SQL.AppendLine("ORDER BY"); SQL.AppendLine(" label"); StringBuilder Result = new StringBuilder(); Result.AppendLine("["); Result.AppendLine(Util.getDBRowsJson(SQL.ToString())); Result.AppendLine("]"); return Result.ToString(); }//public String getPilots
}//public String getFlightReportSQL() public String getUAS(String Term) { StringBuilder SQL = new StringBuilder(); StringBuilder SQLFilter = new StringBuilder(); SQL.AppendLine(@"SELECT Mstr_Drone.DroneID as value, MSTR_Drone.DroneName as label FROM MSTR_Drone"); if (!User.hasAccess("DRONE.VIEWALL")) { SQLFilter.AppendLine("MSTR_Drone.AccountID = " + Util.getAccountID()); } if (!String.IsNullOrEmpty(Term)) { if (SQLFilter.Length > 0) SQLFilter.AppendLine(" AND "); SQLFilter.AppendLine(" MSTR_Drone.DroneName LIKE '%" + Term + "%'"); } if (SQLFilter.Length > 0) { SQL.AppendLine("WHERE"); SQL.Append(SQLFilter); } SQL.AppendLine("ORDER BY label"); StringBuilder Result = new StringBuilder(); Result.AppendLine("["); Result.AppendLine(Util.getDBRowsJson(SQL.ToString())); Result.AppendLine("]"); return Result.ToString(); }//public String getUAS
public String getCords() { String SQL = "SELECT\n" + " [TopLeftLat],\n" + " [TopLeftLon],\n" + " [TopRightLat],\n" + " [TopRightLon],\n" + " [BottomLeftLat],\n" + " [BottomLeftLon],\n" + " [BottomRightLat],\n" + " [BottomRightLon],\n" + " CellNumber, RowNumber, ColumnNumber\n" + " FROM\n" + " [PayLoadYardGrid]"; return Util.getDBRowsJson(SQL); }
public String getLiveUAS() { String SQL; SQL = @"IF OBJECT_ID('tempdb..#TempLiveDrons') IS NOT NULL DROP TABLE #TempLiveDrons;"; Util.doSQL(SQL); SQL = @"select DroneID, max(FlightMapDataID) as FlightMapDataID INTO #TempLiveDrons from FlightMapData WHERE CreatedTime > DATEADD(month, -1, GETDATE()) Group BY DroneID"; Util.doSQL(SQL); SQL = @"SELECT MSTR_Drone.DroneID, FlightMapData.FlightID, MSTR_Drone.AccountID, MSTR_Drone.DroneName, UAVType.Name as UAVType, FlightMapData.Latitude, FlightMapData.Longitude, FlightMapData.ReadTime, FlightMapData.Speed, FlightMapData.Altitude, FlightMapData.TotalFlightTime FROM FlightMapData, #TempLiveDrons, MSTR_Drone LEFT JOIN LUP_Drone AS UAVType ON UAVType.Type='UAVType' AND MSTR_Drone.UavTypeId = UAVType.TypeID WHERE FlightMapData.FlightMapDataID = #TempLiveDrons.FlightMapDataID AND MSTR_Drone.DroneID = FlightMapData.DroneID "; return Util.getDBRowsJson(SQL); }
public String getDistance(String UniqueFlightID) { String SQL = @"Select RowDistance, ColDistance, ColPointLat, ColPointLon, RowPointLat, RowPointLon, Latitude, Longitude, RowNumber, ColumnNumber from PayLoadMapData WHERE FlightUniqueID='" + UniqueFlightID + "'"; return Util.getDBRowsJson(SQL); }
public String getColumnLines() { String SQL = @"SELECT LeftPoint.RowNumber as RowTop, RightPoint.RowNumber as RowBottom, LeftPoint.ColumnNumber, LeftPoint.TopLeftLat as sLat, LeftPoint.TopLeftLon as sLon, RightPoint.BottomLeftLat as eLat, RightPoint.BottomLeftLon as eLon FROM PayLoadYardGrid as LeftPoint LEFT JOIN PayLoadYardGrid as RightPoint ON RightPoint.RowNumber = (SELECT MAX(RowNumber) FROM PayLoadYardGrid WHERE YardID=" + _YardID + @") AND RightPoint.ColumnNumber = LeftPoint.ColumnNumber AND RightPoint.YardID=" + _YardID + @" WHERE LeftPoint.RowNumber = 0 AND LeftPoint.ColumnNumber > 0 AND LeftPoint.YardID=" + _YardID + @" ORDER BY LeftPoint.ColumnNumber;"; return Util.getDBRowsJson(SQL); }
public String getBox() { String SQL = "SELECT * FROM PayLoadYard WHERE YardID=" + _YardID; String JSon = Util.getDBRowsJson(SQL); if (String.IsNullOrWhiteSpace(JSon)) JSon = "[]"; return JSon; }