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);
}
