public GPSInfo getGPS(int FlightID, DateTime FileCreatedOn) {
StringBuilder GPSInfo = new StringBuilder();
String FromTime = Util.toSQLDate(FileCreatedOn.AddMinutes(-5).ToUniversalTime());
String ToTime = Util.toSQLDate(FileCreatedOn.AddMinutes(5).ToUniversalTime());
String ThisTime = Util.toSQLDate(FileCreatedOn.ToUniversalTime());
String SQL = @"Select TOP 1
Latitude,
Longitude,
Altitude,
ABS(DATEDIFF(SECOND, ReadTime,'" + ThisTime + @"')) as SortTime
from
FlightMapData
where
flightid=" + FlightID + @" AND
ReadTime >= '" + FromTime + @"' AND
ReadTime <= '" + ToTime + @"'
ORDER BY
SortTime ASC,
ReadTime DESC";
var Row = Util.getDBRow(SQL);
var theGPS = new GPSInfo();
if (Row["hasRows"].ToString() == "True") {
theGPS.Latitude = Util.toDouble(Row["Latitude"]);
theGPS.Longitude = Util.toDouble(Row["Longitude"]);
theGPS.Altitude = Util.toDouble(Row["Altitude"]);
}
return theGPS;
}
public static UserInfo getInfo(String UserName) {
UserInfo thisUser = new UserInfo();
String SQL = "select\n" +
" MSTR_User.UserID," +
" FirstName + ISNull(' ' + MiddleName, '') + ISNull(' ' +LastName, '') as FullName,\n" +
" MSTR_Account.AccountID,\n" +
" MSTR_Account.BrandColor,\n" +
" MSTR_Account.BrandLogo\n" +
"from\n" +
" MSTR_User\n" +
"left Join MSTR_Account On\n" +
" MSTR_Account.AccountId = MSTR_User.AccountId\n" +
"where\n" +
" MSTR_User.UserName = '******'\n";
var Result = Util.getDBRow(SQL);
thisUser.UserID = int.Parse(Result["UserID"].ToString());
thisUser.AccountID =string.IsNullOrEmpty(Convert.ToString(Result["AccountID"]))? 0 : int.Parse(Convert.ToString(Result["AccountID"]));
thisUser.FullName = Result["FullName"].ToString();
thisUser.BrandColor = Result["BrandColor"].ToString();
thisUser.BrandLogo = Result["BrandLogo"].ToString();
thisUser.UserName = UserName;
if (thisUser.BrandColor == "") thisUser.BrandColor = "#ea050e";
if (thisUser.BrandLogo == "") thisUser.BrandLogo = "exponent-logo.png";
return thisUser;
}
public String getDataTable(
bool isIncludeData = false,
bool isIncludeFooter = true,
String qDataTableID = "qViewTable") {
StringBuilder Table = new StringBuilder();
StringBuilder THead = new StringBuilder();
THead.AppendLine("<tr>");
foreach (var Column in ColumDef) {
THead.Append("<th>");
THead.Append(Util.toCaption(Column));
THead.AppendLine("</th>");
}//foreach
if (IsPrimaryKey) THead.Append("<th class=\"menu\"> </th>");
THead.AppendLine("</tr>");
Table.AppendLine($"<table id=\"{qDataTableID}\" class=\"{ClassName}\">");
Table.AppendLine("<thead>");
Table.Append(THead);
Table.AppendLine("</thead>");
if (isIncludeData) {
Table.AppendLine("<tbody>");
Table.Append(getTableRows());
Table.AppendLine("</tbody>");
}
//add total row to the sql
if (!String.IsNullOrEmpty(TotalSQL)) {
Dictionary<String, Object> Row = Util.getDBRow(TotalSQL);
StringBuilder TotalRow = new StringBuilder();
TotalRow.AppendLine("<tfoot>");
TotalRow.AppendLine("<tr>");
foreach (var Column in Row) {
switch (Column.Key.ToLower()) {
case "hasrows":
break;
default:
TotalRow.AppendLine("<th>" + Column.Value.ToString() + "</th>");
break;
}
}
if (IsPrimaryKey) TotalRow.AppendLine("<th></th>");
TotalRow.AppendLine("</tr>");
TotalRow.AppendLine("</tfoot>");
Table.Append(TotalRow);
}
if (isIncludeFooter) {
Table.AppendLine("<tfoot>");
Table.Append(THead);
Table.AppendLine("</tfoot>");
}
Table.AppendLine("</table>");
return Table.ToString();
}//getDataTable()
public Yard getYard(int thisYardID = 0) {
if (thisYardID == 0) thisYardID = _YardID;
String SQL = @"SELECT
[AccountID],
[YardName],
[TopLeftLat],
[TopLeftLon],
[TopRightLat],
[TopRightLon],
[BottomLeftLat],
[BottomLeftLon],
[BottomRightLat],
[BottomRightLon],
[VechileOrientation],
[VechileLength],
[VechileWidth],
[CreatedBy],
[CreatedOn]
FROM
[PayLoadYard]
WHERE
YardID=" + thisYardID;
var Row = Util.getDBRow(SQL);
var theYard = new Yard();
if(Row["hasRows"].ToString() == "True") theYard = new Yard {
YardID = thisYardID,
AccountID = Util.toInt(Row["AccountID"]),
YardName = Row["YardName"].ToString(),
VechileOrientation = Row["VechileOrientation"].ToString(),
VechileLength = Util.toInt(Row["VechileLength"]),
VechileWidth = Util.toInt(Row["VechileWidth"]),
Bounds = new GeoBox {
TopLeft = new MapPoint {
Latitude = Util.toDouble(Row["TopLeftLat"]),
Longitude = Util.toDouble(Row["TopLeftLon"])
},
TopRight = new MapPoint {
Latitude = Util.toDouble(Row["TopRightLat"]),
Longitude = Util.toDouble(Row["TopRightLon"])
},
BottomRight = new MapPoint {
Latitude = Util.toDouble(Row["BottomRightLat"]),
Longitude = Util.toDouble(Row["BottomRightLon"])
},
BottomLeft = new MapPoint {
Latitude = Util.toDouble(Row["BottomLeftLat"]),
Longitude = Util.toDouble(Row["BottomLeftLon"])
}
}
};
return theYard;
}
public bool isValidated(int ThisCheckListID, int DroneID) {
String SQL;
String sFieldValue;
Decimal FieldValue = 0;
bool Validated = true;
SQL = "SELECT FieldValue, FieldNote FROM [DroneCheckListItem] WHERE\n" +
" [DroneCheckListID] = " + ThisCheckListID + " AND\n" +
" [DroneCheckListItemID] = " + ID;
var Result = Util.getDBRow(SQL);
sFieldValue = Result["FieldValue"].ToString();
this.FieldNote = Result["FieldNote"].ToString();
this.FieldValue = sFieldValue;
try {
FieldValue = Convert.ToDecimal(sFieldValue);
} catch(Exception ex) {
Util.ErrorHandler(ex);
//error in parsing data. set to zero
FieldValue = 0;
}
SQL = "SELECT MinValue, MaxValue FROM [DroneCheckListValidation] WHERE \n" +
" [DroneID]= " + DroneID + " AND\n" +
" [DroneCheckListID] = " + CheckListID + " AND\n" +
" [DroneCheckListItemID] = " + ID;
Result = Util.getDBRow(SQL);
if ((bool)Result["hasRows"]) {
Decimal MinValue = (Decimal)Result["MinValue"];
Decimal MaxValue = (Decimal)Result["MaxValue"];
if (MinValue > 0 && MaxValue > 0 && FieldValue >= MinValue && FieldValue <= MaxValue) {
Validated = true;
} else if (MinValue == 0 && MaxValue == 0) {
Validated = true;
} else {
Validated = false;
}
}
this.isValid = Validated;
return Validated;
}//function isValidated
public String getGrid(String FlightUniqueID, bool IsReturnJSon = false) {
_YardID = getYardID(FlightUniqueID);
String SQL = "SELECT MAX(RowNumber) as Rows, MAX(ColumnNumber) as Cols FROM PayLoadYardGrid WHERE YardID=" + _YardID;
var GridSpec = Util.getDBRow(SQL);
SQL = @"SELECT
PayLoadYardGrid.ColumnNumber ColNum,
PayLoadYardGrid.RowNumber as RowNum,
Concat(
'[', PayLoadYardGrid.[TopLeftLat], ',',PayLoadYardGrid.[TopLeftLon], '],',
'[', PayLoadYardGrid.[TopRightLat], ',', PayLoadYardGrid.[TopRightLon], '],',
'[', PayLoadYardGrid.[BottomLeftLat],',', PayLoadYardGrid.[BottomLeftLon], '],',
'[', PayLoadYardGrid.[BottomRightLat],',', PayLoadYardGrid.[BottomRightLon], ']'
) as Grid,
(SELECT Count(*) FROM
PayLoadMapData
WHERE
PayLoadMapData.RowNumber = PayLoadYardGrid.RowNumber and
PayLoadMapData.ColumnNumber = PayLoadYardGrid.ColumnNumber and
PayLoadMapData.FlightUniqueID = '" + FlightUniqueID + @"'
) as Products
FROM
PayLoadYardGrid
WHERE
PayLoadYardGrid.YardID=" + _YardID + @"
ORDER BY
RowNum,
ColNum";
StringBuilder Grid = new StringBuilder();
StringBuilder GridRow = new StringBuilder();
int lastRow = -1, Row = 0, ProductCount = 0;
using (var ctx = new ExponentPortalEntities()) {
using (var cmd = ctx.Database.Connection.CreateCommand()) {
ctx.Database.Connection.Open();
cmd.CommandText = SQL;
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
Row = reader.GetInt32(reader.GetOrdinal("RowNum"));
ProductCount = reader.GetInt32(reader.GetOrdinal("Products"));
if (Row != lastRow && GridRow.Length > 0) {
if (Grid.Length > 0) Grid.AppendLine(",");
Grid.Append("[");
Grid.Append(GridRow);
Grid.Append("]");
GridRow.Clear();
}
if (GridRow.Length > 0) GridRow.Append(",");
GridRow.Append("{\"grid\":[");
GridRow.Append(reader.GetValue(reader.GetOrdinal("Grid")).ToString());
GridRow.Append("], \"items\":");
GridRow.Append(ProductCount);
GridRow.Append("}");
lastRow = Row;
}//while
}//using reader
}//using ctx.Database.Connection.CreateCommand
}//using ExponentPortalEntities
//adding the last row
if (Grid.Length > 0) Grid.AppendLine(",");
Grid.Append("[");
Grid.Append(GridRow);
Grid.Append("]");
GridRow.Clear();
if(IsReturnJSon) {
StringBuilder JsonGrid = new StringBuilder();
JsonGrid.Append("[");
JsonGrid.Append(Grid);
JsonGrid.Append("]");
return JsonGrid.ToString();
}
return Grid.ToString();
}
public String getTable(String FlightUniqueID) {
int MaxRow = 0;
int MaxCol = 0;
Dictionary<String, String> Rows = new Dictionary<String, String>();
String SQL = @"select
Max(RowNumber) as MaxRow,
Max(ColumnNumber) as MaxCol
from
PayLoadMapData
where
FlightUniqueID = '" + FlightUniqueID + "'";
var Max = Util.getDBRow(SQL);
int.TryParse(Max["MaxRow"].ToString(), out MaxRow);
int.TryParse(Max["MaxCol"].ToString(), out MaxCol);
SQL = @"select
RowNumber,
ColumnNumber,
Count(PayLoadDataMapID) as Items
from
PayLoadMapData
where
FlightUniqueID='" + FlightUniqueID + @"'
GROUP BY
RowNumber,
ColumnNumber
Order By
RowNumber,
ColumnNumber";
//Add all reference to rows
for(var Row = 1; Row <= MaxRow; Row++) {
for(var Col = 1; Col <= MaxCol; Col++) {
String ThisRef = Row + "." + Col;
Rows[ThisRef] = "";
}
}
using (var ctx = new ExponentPortalEntities()) {
using (var cmd = ctx.Database.Connection.CreateCommand()) {
ctx.Database.Connection.Open();
cmd.CommandText = SQL;
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
String ThisRef = reader["RowNumber"].ToString() + "." +
reader["ColumnNumber"].ToString();
String RFID = getRFID(
ctx,
FlightUniqueID,
reader["RowNumber"].ToString(),
reader["ColumnNumber"].ToString());
//Rows[ThisRef] = int.Parse(reader["Items"].ToString());
Rows[ThisRef] = RFID;
}//while
}//using reader
}//using ctx.Database.Connection.CreateCommand
}//using ExponentPortalEntities
StringBuilder TableRows = new StringBuilder();
for (var Row = 1; Row <= MaxRow; Row++) {
for (var Col = 1; Col <= MaxCol; Col++) {
String ThisRef = Row + "." + Col;
if (TableRows.Length > 0) TableRows.Append(",");
TableRows.Append("\"");
TableRows.Append(ThisRef);
TableRows.Append("\": \"");
TableRows.Append(Rows[ThisRef]);
TableRows.Append("\"");
}
if (TableRows.Length > 0) TableRows.AppendLine("");
}
if (TableRows.Length > 0) TableRows.Append(",");
TableRows.Append("\"Rows\":");
TableRows.Append(MaxRow);
TableRows.Append(",\"Cols\":");
TableRows.Append(MaxCol);
return TableRows.ToString();
}
