}//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;
}
