public List<ValidationMap> getValidationMessages(int ThisCheckListID) { String SQL = "SELECT\n" + " DroneFlight.DroneID\n" + "FROM\n" + " DroneCheckList,\n" + " DroneFlight\n" + "WHERE\n" + " DroneFlight.ID = DroneCheckList.FlightID and\n" + " DroneCheckList.ID =" + ThisCheckListID; List<ValidationMap> ValidationMessages = new List<ValidationMap>(); //Load the drone ID to check against String sDroneID = Util.getDBVal(SQL); DroneID = Int32.Parse(sDroneID); //check the validation agains the saved checklist for drone foreach (var item in CheckItems) { if (!item.isValidated(ThisCheckListID, DroneID)) { ValidationMessages.Add(new ValidationMap { ItemTitle = item.Title, FieldType = item.FieldType, SlNo = item.SlNo }); }; } return ValidationMessages; }
public Decimal getValidationFieldValue(String Suffix) { Decimal dFieldValue = 0; String SQL = "SELECT [" + Suffix + "Value] FROM [DroneCheckListValidation] WHERE \n" + " [DroneID]= " + DroneID + " AND\n" + " [DroneCheckListID] = " + CheckListID + " AND\n" + " [DroneCheckListItemID] = " + ID; String FieldValue = Util.getDBVal(SQL); Decimal.TryParse(FieldValue, out dFieldValue); return dFieldValue; }
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 String getAllowedLocation(int FlightID) { _DroneID = getDroneIDForFlight(FlightID); String dateSQL = @"select CONVERT(VARCHAR, FlightDate ,20) as FlightDate from DroneFlight where ID=" + FlightID; String CheckDate = Util.getDBVal(dateSQL); StringBuilder Cord = new StringBuilder(); String SQL = @"SELECT ApprovalID, Coordinates, InnerBoundaryCoord FROM [GCA_Approval] WHERE DroneID = " + _DroneID + @" AND '" + CheckDate + "' BETWEEN StartDate and EndDate"; var Rows = Util.getDBRows(SQL); foreach (var Row in Rows) { String Coordinates = Row["Coordinates"].ToString(); if (Cord.Length > 0) Cord.AppendLine(","); Cord.AppendLine("{"); Cord.AppendLine("Outer: ["); Cord.Append(toScriptArray(Coordinates)); Cord.AppendLine("]"); //Inner cordinates String InnerCoordinates = Row["InnerBoundaryCoord"].ToString(); Cord.AppendLine(","); Cord.AppendLine("Inner: ["); Cord.Append(toScriptArray(InnerCoordinates)); Cord.AppendLine("]"); Cord.AppendLine("}"); } return Cord.ToString(); }
public static String getProfileImage(int UserID = 0) { if (UserID == 0) { UserID = getLoginUserID(); } String SQL = "SELECT PhotoUrl From MSTR_User WHERE UserID=" + UserID; String ProfileImage = Util.getDBVal(SQL).ToString(); String ProfileImageURL = "/images/PilotImage.png"; if (!String.IsNullOrEmpty(ProfileImage)) { String dProfileImageURL = $"/Upload/User/{UserID}/{ProfileImage}"; var FPath = System.Web.Hosting.HostingEnvironment.MapPath(dProfileImageURL); if (System.IO.File.Exists(FPath)) { return(dProfileImageURL); } } return(ProfileImageURL); }
public String getVideoStartDate(int FlightID) { String SQL = "select TOP 1 VideoURL from DroneFlightVideo WHERE FlightID=" + FlightID + " ORDER BY VideoURL ASC"; String VideoURL = Util.getDBVal(SQL); return getVideoDate(VideoURL); }
public String getDroneNameForFlight(int FlightID = 0) { _DroneID = getDroneIDForFlight(FlightID); String SQL = "Select DroneName From MSTR_Drone WHERE DroneID=" + _DroneID; return Util.getDBVal(SQL); }
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); }