Exemple #1
0
 private int getYardID(String FlightUniqueID) {
   String SQL = @"SELECT YardID From PayLoadFlight WHERE FlightUniqueID='" + FlightUniqueID + "'";
   return Util.getDBInt(SQL);
 }
Exemple #2
0
    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();


    }
Exemple #3
0
 public String getBox() {
   String SQL = "SELECT * FROM PayLoadYard WHERE YardID=" + _YardID;
   String JSon = Util.getDBRowsJson(SQL);
   if (String.IsNullOrWhiteSpace(JSon)) JSon = "[]";
   return JSon;
 }
Exemple #4
0
    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();
    }
Exemple #5
0
 public String getUASName() {
   String SQL = "SELECT DroneName From MSTR_Drone WHERE DroneID=" + _UAS;
   return Util.getDBVal(SQL);
 }
Exemple #6
0
 public String getPilotName() {
   String SQL = "SELECT FirstName + ' ' +  LastName From MSTR_User WHERE UserID=" + _Pilot;
   return Util.getDBVal(SQL);
 }
Exemple #7
0
    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()