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