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