public String getAlertSQL(FlightReportFilter Filter) {
StringBuilder SQL = new StringBuilder();
SQL.Append(
@"Select
PortalAlert.AlertID,
FlightID,
PortalAlert.CreatedOn,
MSTR_Drone.DroneName as RPAS,
MSTR_User.FirstName + ' ' + MSTR_User.LastName as Pilot,
CASE WHEN SMSSend = 1 THEN 'Yes' Else 'No' END as SMS,
AlertCategory,
AlertType,
PortalAlert.Latitude,
PortalAlert.Longitude,
PortalAlert.Altitude,
Count(*) OVER() AS _TotalRecords,
PortalAlert.AlertID AS _PKey
From
PortalAlert
LEFT JOIN MSTR_Drone On
MSTR_Drone.DroneID = PortalAlert.DroneID
LEFT JOIN MSTR_User On
MSTR_User.UserID = PortalAlert.PilotID
");
SQL.AppendLine("WHERE");
SQL.AppendLine(" PortalAlert.CreatedOn BETWEEN '" + Filter.FromSQL() + "' AND '" + Filter.ToSQL() + "'");
if (Filter.Pilot > 0)
SQL.AppendLine("AND PortalAlert.PilotID=" + Filter.Pilot);
if (Filter.UAS > 0)
SQL.AppendLine("AND PortalAlert.DroneID=" + Filter.UAS);
if (Filter.Proximity > 0)
SQL.AppendLine("AND PortalAlert.AlertCategory='Proximity'");
if (Filter.Height > 0)
SQL.AppendLine("AND PortalAlert.AlertCategory = 'Height'");
if (Filter.Boundary > 0)
SQL.AppendLine("AND PortalAlert.AlertCategory = 'Boundary'");
if (!exLogic.User.hasAccess("DRONE.VIEWALL"))
{
SQL.AppendLine(" AND PortalAlert.AccountID=" + Util.getAccountID());
}
return SQL.ToString();
}
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()
