private static List <Models.MSTR_User> GetOrganizationPilots() { var DB = new Models.ExponentPortalEntities(); int AccountID = Util.getAccountID(); var Query = from u in DB.MSTR_User where u.AccountId == AccountID && u.IsPilot == true orderby u.FirstName select u; var TheList = Query.ToList(); //If no photo image, skip it foreach (var Usr in TheList) { if (String.IsNullOrEmpty(Usr.PhotoUrl)) { Usr.PhotoUrl = "/images/PilotImage.png"; } else { Usr.PhotoUrl = $"/Upload/User/{Usr.UserId}/{Usr.PhotoUrl}"; if (!System.IO.File.Exists(System.Web.HttpContext.Current.Server.MapPath(Usr.PhotoUrl))) { Usr.PhotoUrl = "/images/PilotImage.png"; } } }//foreach(var Usr in TheList) return(TheList); }
}//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 List <ChartViewModel> getRecentFlights() { List <ChartViewModel> AllChartData = new List <ChartViewModel>(); String SQL = @"SELECT w.NAME, w.ChartColor, v.DroneId, v.AccountID, v.DroneName, IsNull(( SELECT TOP 1 CAST(ISNULL(a.flighthours,0.0) as Numeric(12,2)) FROM droneflight a WHERE a.DroneId = v.DroneID ORDER BY a.FlightDate DESC ),0) AS LastFlightHours, IsNull(( SELECT Sum(CAST(ISNULL(b.flighthours ,0) as Numeric(12,2))) FROM droneflight b WHERE b.DroneId = v.DroneID AND b.FlightDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) ),0) AS LastMonthHours, IsNull(( SELECT Sum(CAST(ISNULL(b.flighthours ,0) as Numeric(12,2))) FROM droneflight b WHERE b.DroneId = v.DroneID ),0) AS TotalFlightHours FROM MSTR_Drone v LEFT JOIN mstr_account w ON v.AccountID = w.AccountID"; if (!exLogic.User.hasAccess("DRONE.VIEWALL")) { SQL = SQL + $" WHERE v.AccountID = {Util.getAccountID()}"; } using (var cmd = ctx.Database.Connection.CreateCommand()) { cmd.CommandText = SQL; cmd.CommandType = CommandType.Text; using (var RS = cmd.ExecuteReader()) { while (RS.Read()) { AllChartData.Add(new ChartViewModel() { DroneID = RS.GetInt32(RS.GetOrdinal("DroneID")), DroneName = RS["DroneName"].ToString(), ShortName = RS["DroneName"].ToString().Split('-').Last(), AccountID = RS.GetInt32(RS.GetOrdinal("AccountID")), AccountName = RS["NAME"].ToString(), ChartColor = RS["ChartColor"].ToString(), TotalFightTime = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("TotalFlightHours"))) / 60, 2), CurrentFlightTime = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("LastMonthHours"))) / 60, 2), LastFlightTime = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("LastFlightHours"))) / 60, 2) }); } //while(RS.Read()) } //using(var RS) } //using(cmd) return(AllChartData); }
public List <ChartViewModel> GetFlightHoursByAccount() { List <ChartViewModel> AllChartData = new List <ChartViewModel>(); DateTime StartDateTime = DateTime.Now.AddMonths(-10); DateTime StartAt = new DateTime(StartDateTime.Year, StartDateTime.Month, 1); StringBuilder SQLString = new StringBuilder("SELECT\n"); for (int m = 0; m < 12; m++) { String sDate = StartAt.AddMonths(m).ToString("yyyy-MM-dd"); SQLString.AppendLine($"Sum(CASE WHEN DroneFlight.FlightDate < '{sDate}' THEN CAST(DroneFlight.FlightHours as Numeric(12,2)) ELSE 0 END) As M{m},"); } SQLString.AppendLine(@" MSTR_Drone.AccountID from DroneFlight, MSTR_Drone WHERE DroneFlight.DroneID = MSTR_Drone.DroneId "); if (!exLogic.User.hasAccess("DRONE.VIEWALL")) { SQLString.AppendLine($" AND MSTR_Drone.AccountID = {Util.getAccountID()}"); } SQLString.AppendLine(@"GROUP BY MSTR_Drone.AccountID"); using (var cmd = ctx.Database.Connection.CreateCommand()) { cmd.CommandText = SQLString.ToString(); cmd.CommandType = CommandType.Text; using (var RS = cmd.ExecuteReader()) { while (RS.Read()) { String AccountName = Util.getDBVal($"SELECT Name From MSTR_Account WHERE AccountID={RS["AccountID"].ToString()}"); AllChartData.Add(new ChartViewModel() { AccountID = RS.GetInt32(RS.GetOrdinal("AccountID")), AccountName = AccountName, M1 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M0"))) / 60, 2), M2 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M1"))) / 60, 2), M3 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M2"))) / 60, 2), M4 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M3"))) / 60, 2), M5 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M4"))) / 60, 2), M6 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M5"))) / 60, 2), M7 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M6"))) / 60, 2), M8 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M7"))) / 60, 2), M9 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M8"))) / 60, 2), M10 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M9"))) / 60, 2), M11 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M10"))) / 60, 2), M12 = Math.Round((Double)(RS.GetDecimal(RS.GetOrdinal("M11"))) / 60, 2) }); } //while(RS.Read()) } //using(var RS) } //using(cmd) return(AllChartData); }
public static bool hasDrone(int DroneID) { if (exLogic.User.hasAccess("DRONE.MANAGE")) return true; String SQL = "SELECT Count(*) FROM\n" + "MSTR_Drone\n" + "WHERE\n" + " DroneID=" + DroneID + " AND\n" + " AccountID=" + Util.getAccountID(); int Count = Util.getDBInt(SQL); if (Count > 0) return true; return false; }
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 static IEnumerable <SelectListItem> GetDropDowntList(String TypeOfList, bool IsStrictFilter = false, int PilotID = 0) { List <SelectListItem> SelectList = new List <SelectListItem>(); SelectList.Add(new SelectListItem { Text = "Please Select...", Value = "0" }); String SQL = "SELECT 0 as Value, 'Not Available' as Name"; using (var ctx = new ExponentPortalEntities()) { using (var cmd = ctx.Database.Connection.CreateCommand()) { ctx.Database.Connection.Open(); switch (TypeOfList.ToLower()) { case "drone": SQL = "SELECT [DroneId] as Value, Convert(nvarchar(20),DroneId)+'-'+[DroneName] as Name FROM [MSTR_Drone] where IsActive=1"; if (IsStrictFilter || !exLogic.User.hasAccess("DRONE.VIEWALL")) { SQL += "\n" + " AND\n " + " MSTR_Drone.AccountID=" + Util.getAccountID(); } SQL += "\n ORDER BY [DroneName]"; break; case "pilot": SQL = "SELECT UserID as Value, FirstName as Name FROM MSTR_User WHERE \n"; if (IsStrictFilter || !exLogic.User.hasAccess("DRONE.VIEWALL")) { SQL += "\n" + " MSTR_User.AccountID=" + Util.getAccountID() + " and "; } SQL += "\n MSTR_User.IsPilot=1 ORDER BY FirstName"; break; case "allpilot": SQL = $"SELECT UserID as Value, FirstName+" + " ' - ' " + "+Name as Name FROM MSTR_User " + "Left join MSTR_Account on MSTR_Account.AccountID=MSTR_User.AccountID WHERE \n"; if (IsStrictFilter || !exLogic.User.hasAccess("DRONE.VIEWALL")) { SQL += "\n" + " MSTR_User.AccountID=" + Util.getAccountID() + " and "; } SQL += "\n MSTR_User.IsPilot=1 ORDER BY FirstName"; break; case "gsc": SQL = "SELECT UserID as Value, FirstName as Name FROM MSTR_User"; if (IsStrictFilter || !exLogic.User.hasAccess("DRONE.VIEWALL")) { SQL += "\n" + "WHERE\n" + " MSTR_User.AccountID=" + Util.getAccountID(); } SQL += "\nORDER BY FirstName"; break; case "pilotdrones": SQL = $@"SELECT [DroneId] as Value, Convert(nvarchar(20), DroneId) + '-' +[DroneName] as Name FROM[MSTR_Drone] where IsActive = 1 and AccountID = (Select AccountID from MSTR_User where userID = {PilotID}) "; break; } cmd.CommandText = SQL; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { SelectList.Add(new SelectListItem { Text = reader["Name"].ToString(), Value = reader["Value"].ToString() }); } //while } //using ctx.Database.Connection.Close(); } //using Database.Connection } //using ExponentPortalEntities; return(SelectList); //return the list objects } //function GetDropDowntList
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()