private int getYardID(String FlightUniqueID) {
String SQL = @"SELECT YardID From PayLoadFlight WHERE FlightUniqueID='" + FlightUniqueID + "'";
return Util.getDBInt(SQL);
}
public String getGrid(String FlightUniqueID, bool IsReturnJSon = false) {
_YardID = getYardID(FlightUniqueID);
String SQL = "SELECT MAX(RowNumber) as Rows, MAX(ColumnNumber) as Cols FROM PayLoadYardGrid WHERE YardID=" + _YardID;
var GridSpec = Util.getDBRow(SQL);
SQL = @"SELECT
PayLoadYardGrid.ColumnNumber ColNum,
PayLoadYardGrid.RowNumber as RowNum,
Concat(
'[', PayLoadYardGrid.[TopLeftLat], ',',PayLoadYardGrid.[TopLeftLon], '],',
'[', PayLoadYardGrid.[TopRightLat], ',', PayLoadYardGrid.[TopRightLon], '],',
'[', PayLoadYardGrid.[BottomLeftLat],',', PayLoadYardGrid.[BottomLeftLon], '],',
'[', PayLoadYardGrid.[BottomRightLat],',', PayLoadYardGrid.[BottomRightLon], ']'
) as Grid,
(SELECT Count(*) FROM
PayLoadMapData
WHERE
PayLoadMapData.RowNumber = PayLoadYardGrid.RowNumber and
PayLoadMapData.ColumnNumber = PayLoadYardGrid.ColumnNumber and
PayLoadMapData.FlightUniqueID = '" + FlightUniqueID + @"'
) as Products
FROM
PayLoadYardGrid
WHERE
PayLoadYardGrid.YardID=" + _YardID + @"
ORDER BY
RowNum,
ColNum";
StringBuilder Grid = new StringBuilder();
StringBuilder GridRow = new StringBuilder();
int lastRow = -1, Row = 0, ProductCount = 0;
using (var ctx = new ExponentPortalEntities()) {
using (var cmd = ctx.Database.Connection.CreateCommand()) {
ctx.Database.Connection.Open();
cmd.CommandText = SQL;
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
Row = reader.GetInt32(reader.GetOrdinal("RowNum"));
ProductCount = reader.GetInt32(reader.GetOrdinal("Products"));
if (Row != lastRow && GridRow.Length > 0) {
if (Grid.Length > 0) Grid.AppendLine(",");
Grid.Append("[");
Grid.Append(GridRow);
Grid.Append("]");
GridRow.Clear();
}
if (GridRow.Length > 0) GridRow.Append(",");
GridRow.Append("{\"grid\":[");
GridRow.Append(reader.GetValue(reader.GetOrdinal("Grid")).ToString());
GridRow.Append("], \"items\":");
GridRow.Append(ProductCount);
GridRow.Append("}");
lastRow = Row;
}//while
}//using reader
}//using ctx.Database.Connection.CreateCommand
}//using ExponentPortalEntities
//adding the last row
if (Grid.Length > 0) Grid.AppendLine(",");
Grid.Append("[");
Grid.Append(GridRow);
Grid.Append("]");
GridRow.Clear();
if(IsReturnJSon) {
StringBuilder JsonGrid = new StringBuilder();
JsonGrid.Append("[");
JsonGrid.Append(Grid);
JsonGrid.Append("]");
return JsonGrid.ToString();
}
return Grid.ToString();
}
public String getBox() {
String SQL = "SELECT * FROM PayLoadYard WHERE YardID=" + _YardID;
String JSon = Util.getDBRowsJson(SQL);
if (String.IsNullOrWhiteSpace(JSon)) JSon = "[]";
return JSon;
}
public String getTable(String FlightUniqueID) {
int MaxRow = 0;
int MaxCol = 0;
Dictionary<String, String> Rows = new Dictionary<String, String>();
String SQL = @"select
Max(RowNumber) as MaxRow,
Max(ColumnNumber) as MaxCol
from
PayLoadMapData
where
FlightUniqueID = '" + FlightUniqueID + "'";
var Max = Util.getDBRow(SQL);
int.TryParse(Max["MaxRow"].ToString(), out MaxRow);
int.TryParse(Max["MaxCol"].ToString(), out MaxCol);
SQL = @"select
RowNumber,
ColumnNumber,
Count(PayLoadDataMapID) as Items
from
PayLoadMapData
where
FlightUniqueID='" + FlightUniqueID + @"'
GROUP BY
RowNumber,
ColumnNumber
Order By
RowNumber,
ColumnNumber";
//Add all reference to rows
for(var Row = 1; Row <= MaxRow; Row++) {
for(var Col = 1; Col <= MaxCol; Col++) {
String ThisRef = Row + "." + Col;
Rows[ThisRef] = "";
}
}
using (var ctx = new ExponentPortalEntities()) {
using (var cmd = ctx.Database.Connection.CreateCommand()) {
ctx.Database.Connection.Open();
cmd.CommandText = SQL;
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
String ThisRef = reader["RowNumber"].ToString() + "." +
reader["ColumnNumber"].ToString();
String RFID = getRFID(
ctx,
FlightUniqueID,
reader["RowNumber"].ToString(),
reader["ColumnNumber"].ToString());
//Rows[ThisRef] = int.Parse(reader["Items"].ToString());
Rows[ThisRef] = RFID;
}//while
}//using reader
}//using ctx.Database.Connection.CreateCommand
}//using ExponentPortalEntities
StringBuilder TableRows = new StringBuilder();
for (var Row = 1; Row <= MaxRow; Row++) {
for (var Col = 1; Col <= MaxCol; Col++) {
String ThisRef = Row + "." + Col;
if (TableRows.Length > 0) TableRows.Append(",");
TableRows.Append("\"");
TableRows.Append(ThisRef);
TableRows.Append("\": \"");
TableRows.Append(Rows[ThisRef]);
TableRows.Append("\"");
}
if (TableRows.Length > 0) TableRows.AppendLine("");
}
if (TableRows.Length > 0) TableRows.Append(",");
TableRows.Append("\"Rows\":");
TableRows.Append(MaxRow);
TableRows.Append(",\"Cols\":");
TableRows.Append(MaxCol);
return TableRows.ToString();
}
public String getUASName() {
String SQL = "SELECT DroneName From MSTR_Drone WHERE DroneID=" + _UAS;
return Util.getDBVal(SQL);
}
public String getPilotName() {
String SQL = "SELECT FirstName + ' ' + LastName From MSTR_User WHERE UserID=" + _Pilot;
return Util.getDBVal(SQL);
}
public String getFlightReportSQL(FlightReportFilter Filter, bool IsReturnExtraInfo = false) {
StringBuilder SQLFilter = new StringBuilder();
StringBuilder SQL = new StringBuilder();
SQL.AppendLine(@"SELECT
DroneFlight.ID AS Ref,
DroneFlight.FlightDate,
( MSTR_User.FirstName + ' ' + MSTR_User.LastName ) AS Pilot,
MSTR_Drone.DroneName as UAS,
convert(varchar, DATEADD(ms, DroneFlight.FlightHours * 1000, 0),108) as FlightTime,
DroneFlight.MaxAltitude,
Convert(Varchar(10), DroneFlight.BoundaryCritical) + ' of ' +
Convert(Varchar(10), BoundaryHigh + BoundaryWarning + BoundaryCritical ) as BoundaryAlerts,
Convert(Varchar(10), DroneFlight.ProximityCritical) + ' of ' +
Convert(Varchar(10), ProximityHigh + ProximityCritical + ProximityWarning) as ProximityAlerts,
Convert(Varchar(10), DroneFlight.HeightCritical) + ' of ' +
Convert(Varchar(10), HeightHigh + HeightCritical + HeightWarning) as AltitudeAlerts,");
if (IsReturnExtraInfo) SQL.AppendLine(@"
BoundaryCritical,
BoundaryHigh + BoundaryWarning + BoundaryCritical as Boundary,
ProximityCritical,
ProximityHigh + ProximityCritical + ProximityWarning as Proximity,
HeightCritical,
HeightHigh + HeightCritical + HeightWarning as Height,");
SQL.AppendLine(@"
Count(*)
OVER() AS _TotalRecords,
DroneFlight.ID AS _PKey
FROM
DroneFlight
INNER JOIN MSTR_Drone ON
MSTR_Drone.DroneID = DroneFlight.DroneID
LEFT JOIN MSTR_User ON
MSTR_User.UserID = DroneFlight.PilotID
");
/*
LEFT JOIN (SELECT
FlightID,
Sum(CASE
WHEN PortalAlert.AlertCategory = 'Boundary' THEN
1
ELSE
0
END) AS Boundary,
Sum(CASE
WHEN PortalAlert.AlertCategory = 'Boundary' AND
AlertType = 'Critical' THEN
1
ELSE
0
END) AS BoundaryCritical,
Sum(CASE
WHEN PortalAlert.AlertCategory = 'Height' THEN
1
ELSE
0
END) AS Height,
Sum(CASE
WHEN PortalAlert.AlertCategory = 'Height' AND
AlertType = 'Critical' THEN
1
ELSE
0
END) AS HeightCritical,
Sum(CASE
WHEN PortalAlert.AlertCategory = 'Proximity' THEN
1
ELSE
0
END) AS Proximity,
Sum(CASE
WHEN PortalAlert.AlertCategory = 'Proximity' AND
AlertType = 'Critical' THEN
1
ELSE
0
END) AS ProximityCritical
FROM
PortalAlert
GROUP BY
FlightID) AS PortalAlertCounter ON
PortalAlertCounter.FlightID = DroneFlight.ID
*/
SQLFilter.AppendLine("WHERE");
SQLFilter.AppendLine(" DroneFlight.FlightDate BETWEEN '" + Filter.FromSQL() + "' AND '" + Filter.ToSQL() + "'");
if (Filter.Pilot > 0)
SQLFilter.AppendLine("AND DroneFlight.PilotID=" + Filter.Pilot);
if (Filter.UAS > 0)
SQLFilter.AppendLine("AND DroneFlight.DroneID=" + Filter.UAS);
if (Filter.Proximity > 0)
SQLFilter.AppendLine("AND (ProximityHigh + ProximityCritical + ProximityWarning) > 0");
if (Filter.ProximityCritical > 0)
SQLFilter.AppendLine("AND DroneFlight.ProximityCritical > 0");
if (Filter.Height > 0)
SQLFilter.AppendLine("AND (HeightHigh + HeightCritical + HeightWarning) > 0");
if (Filter.HeightCritical > 0)
SQLFilter.AppendLine("AND DroneFlight.HeightCritical > 0");
if (Filter.Boundary > 0)
SQLFilter.AppendLine("AND (BoundaryHigh + BoundaryWarning + BoundaryCritical) > 0");
if (Filter.BoundaryCritical > 0)
SQLFilter.AppendLine("AND DroneFlight.BoundaryCritical > 0");
if (!exLogic.User.hasAccess("DRONE.VIEWALL")) {
SQLFilter.AppendLine(" AND MSTR_Drone.AccountID=" + Util.getAccountID() + "");
}
SQL.Append(SQLFilter);
return SQL.ToString();
}//public String getFlightReportSQL()
