private static ShipmentLocation GetGPSData(int trackerId) { //string qrySelecGPS = " SELECT TOP 1 id,[TrackerId],[Latitude],[Longitude],[Timestamp] FROM[dbo].[GPS_Data] ORDER BY[Timestamp] desc"; string qrySelecGPS = "SELECT TOP 1 a.id,a.[TrackerId],a.[Latitude],a.[Longitude],a.[Timestamp] FROM[dbo].[GPS_Data] a INNER JOIN[DeviceInfo] d on a.[TrackerId] = d.macid " + " WHERE d.deviceid = @TrackerId ORDER BY a.[Timestamp] desc"; var Connectionstring = Environment.GetEnvironmentVariable("SQLConnectionString"); SqlConnection connGPS = new SqlConnection(Connectionstring); SqlCommand commandGPS = new SqlCommand(qrySelecGPS, connGPS); commandGPS.Parameters.Clear(); commandGPS.Parameters.Add("@TrackerId", SqlDbType.Int).Value = trackerId; connGPS.Open(); SqlDataReader reader = commandGPS.ExecuteReader(); ShipmentLocation objLocation = new ShipmentLocation(); while (reader.Read()) { objLocation.current_latitude = (double)reader["Latitude"]; objLocation.current_longitude = (double)reader["Longitude"]; objLocation.current_system_time = (DateTime)reader["Timestamp"]; objLocation.current_gps_time = reader["Timestamp"].ToString(); break; } reader.Close(); connGPS.Close(); return(objLocation); }
public static async Task <HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequestMessage req, TraceWriter log) { // parse query parameter string shipmentID = req.GetQueryNameValuePairs() .FirstOrDefault(q => string.Compare(q.Key, "ShipmentID", true) == 0) .Value; log.Info("Inside beacon data"); if (string.IsNullOrEmpty(shipmentID)) { return(req.CreateErrorResponse(HttpStatusCode.BadRequest, "Input is Null or Empty")); } log.Info("Connecting to DataBase"); var Connectionstring = Environment.GetEnvironmentVariable("SQLConnectionString"); var NOsqlconnectionstring = Environment.GetEnvironmentVariable("NoSQLConnectionString"); var SQLconnectionstring = Environment.GetEnvironmentVariable("SQLConnectionString"); var PrimaryKey = Environment.GetEnvironmentVariable("NoSqlPrimaryKey"); DocumentClient client = new DocumentClient(new Uri(NOsqlconnectionstring), PrimaryKey); FeedOptions feedOptions = new FeedOptions(); Database databaseInfo = client.CreateDatabaseQuery().Where(x => x.Id == "TitanCosmosDB").AsEnumerable().FirstOrDefault(); string DB = databaseInfo.SelfLink; DocumentCollection documentCollection = new DocumentCollection { Id = "TitanTelemetryCollection" }; //string param = "eadd3a4f3531"; //string cosmosQuery = "SELECT TOP 1 c.temperature,c.humidity,f.current_system_time,c.humidity_alert,c.temperature_alert,c.tamper_alert FROM f JOIN c IN f.sensor_Values WHERE c.sensorID = '"+ param + "' order by f.current_system_time desc"; var collectionlinK = UriFactory.CreateDocumentCollectionUri("TitanCosmosDB", "TitanTelemetryCollection"); ActiveShipment shipMent = new ActiveShipment(); string SqlSelect = "SELECT[ShipmasterID],[ShipmentID],[ShipmentStatus],[CreatedBy],[CreatedTime],[SourceLoc],[DestinationLoc],[LogisticPartner],[DateofShipment],[DeliveryDate]," + "[InvoiceDocRef],[PONumber],[BlockchainStatus],[GatewayCount],[PalletCount],[CartonCount],[BoxCount],[ProductCount],[BeaconCount]," + "[CurrrentLat],[CurrrentLong],[TemperatureBreach],[HumidityBreach],[TamperBreach],[VibrationBreach],[UnreachableDevice],[IsActive],[AlertStatus] " + " FROM [VW_AssociatedShipments] WHERE [ShipmentID] =@ShipmentID"; using (SqlConnection conn = new SqlConnection(Connectionstring)) { using (SqlCommand cmd = new SqlCommand(SqlSelect, conn)) { cmd.Parameters.Add("@ShipmentID", SqlDbType.Char); cmd.Parameters["@ShipmentID"].Value = shipmentID; conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { shipMent.ShipmasterID = (int)reader["ShipmasterID"]; shipMent.ShipmentID = reader["ShipmentID"].ToString(); shipMent.ShipmentStatus = reader["ShipmentStatus"].ToString(); shipMent.CreatedBy = reader["CreatedBy"].ToString(); shipMent.CreatedDateTime = (DateTime)reader["CreatedTime"]; shipMent.SourceLoc = reader["SourceLoc"].ToString(); shipMent.DestinationLoc = reader["DestinationLoc"].ToString(); shipMent.LogisticPartner = reader["LogisticPartner"].ToString(); shipMent.DateofShipment = (DateTime)reader["DateofShipment"]; shipMent.DeliveryDate = (DateTime)reader["DeliveryDate"]; shipMent.InvoiceDocRef = reader["InvoiceDocRef"].ToString(); shipMent.PONumber = reader["PONumber"].ToString(); shipMent.IsActive = (bool)reader["IsActive"]; shipMent.GatewayCount = (int)reader["GatewayCount"]; shipMent.PalletCount = (int)reader["PalletCount"]; shipMent.CartonCount = (int)reader["CartonCount"]; shipMent.BoxCount = (int)reader["BoxCount"]; shipMent.ProductCount = (int)reader["ProductCount"]; shipMent.BeaconCount = (int)reader["BeaconCount"]; shipMent.BlockchainStatus = reader["BlockchainStatus"].ToString(); shipMent.TemperatureBreachCount = (int)reader["TemperatureBreach"]; shipMent.HumidityBreachCount = (int)reader["HumidityBreach"]; shipMent.ShockVibrationCount = (int)reader["VibrationBreach"]; shipMent.TamperBreachCount = (int)reader["TamperBreach"]; shipMent.UnreachableDeviceCount = (int)reader["UnreachableDevice"]; shipMent.AlertStatus = reader["AlertStatus"].ToString(); string gatewayID = GetMacId(shipMent.ShipmasterID); ShipmentLocation objLocation = new ShipmentLocation(); string cosmosTelemeterySelect = "SELECT TOP 1 c.gatewayId, c.current_longitude,c.current_latitude,c.current_system_time,c.current_gps_time, " + "c.last_recorded_latitude,c.last_recorded_longitude ,c.last_recorded_gps_time FROM c where c.gatewayId = '" + gatewayID + "' order by c.current_system_time desc"; IQueryable <ShipmentLocation> cosTelemetry = client.CreateDocumentQuery <ShipmentLocation>(collectionlinK, cosmosTelemeterySelect); objLocation = cosTelemetry.ToList().SingleOrDefault(); if (objLocation != null) { if (objLocation.current_latitude == 0) { shipMent.CurrentLatitude = objLocation.last_recorded_latitude; } else { shipMent.CurrentLatitude = objLocation.current_latitude; } if (objLocation.current_longitude == 0) { shipMent.CurrentLongitude = objLocation.last_recorded_longitude; } else { shipMent.CurrentLongitude = objLocation.current_longitude; } //if (objLocation.current_gps_time.ToString() == "0" || objLocation.current_gps_time.ToString() == "0.0" || string.IsNullOrEmpty(objLocation.current_gps_time.ToString())) //{ // shipMent.CurrentGPSTime = objLocation.last_recorded_gps_time; //} //else //{ // shipMent.CurrentGPSTime = objLocation.current_gps_time; //} if (string.IsNullOrEmpty(objLocation.current_gps_time)) { shipMent.CurrentGPSTime = objLocation.last_recorded_gps_time; } else { shipMent.CurrentGPSTime = objLocation.current_gps_time; } } //GPS Data Test ShipmentLocation objGPS = new ShipmentLocation(); int trackerId = GetTrackerId(gatewayID); objGPS = GetGPSData(trackerId); shipMent.CurrentLatitude = objGPS.current_latitude; shipMent.CurrentLongitude = objGPS.current_longitude; shipMent.CurrentGPSTime = objGPS.current_gps_time; /////GPS///////////////// break; } reader.Close(); conn.Close(); } } return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(JsonConvert.SerializeObject(shipMent, Formatting.Indented), Encoding.UTF8, "application/json") }); }