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