private static GatewayLocation GetGPSData(int trackerId) { 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(); GatewayLocation objLocation = new GatewayLocation(); 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) { log.Info("C# HTTP trigger function processed a request."); 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"); //var collectionAlert = UriFactory.CreateDocumentCollectionUri("TitanCosmosDB", "CCAlertCollection"); List <ActiveShipments> shipMasterList = new List <ActiveShipments>(); 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] "; using (SqlConnection conn = new SqlConnection(SQLconnectionstring)) { using (SqlCommand cmd = new SqlCommand(SqlSelect, conn)) { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ActiveShipments shipMaster = new ActiveShipments(); shipMaster.ShipmasterID = (int)reader["ShipmasterID"]; shipMaster.ShipmentID = reader["ShipmentID"].ToString(); shipMaster.ShipmentStatus = reader["ShipmentStatus"].ToString(); shipMaster.CreatedBy = reader["CreatedBy"].ToString(); shipMaster.CreatedDateTime = (DateTime)reader["CreatedTime"]; shipMaster.SourceLoc = reader["SourceLoc"].ToString(); shipMaster.DestinationLoc = reader["DestinationLoc"].ToString(); shipMaster.LogisticPartner = reader["LogisticPartner"].ToString(); shipMaster.DateofShipment = (DateTime)reader["DateofShipment"]; shipMaster.DeliveryDate = (DateTime)reader["DeliveryDate"]; shipMaster.InvoiceDocRef = reader["InvoiceDocRef"].ToString(); shipMaster.PONumber = reader["PONumber"].ToString(); shipMaster.AlertStatus = reader["AlertStatus"].ToString(); shipMaster.IsActive = (bool)reader["IsActive"]; shipMaster.GatewayCount = (int)reader["GatewayCount"]; shipMaster.PalletCount = (int)reader["PalletCount"]; shipMaster.CartonCount = (int)reader["CartonCount"]; shipMaster.BoxCount = (int)reader["BoxCount"]; shipMaster.ProductCount = (int)reader["ProductCount"]; shipMaster.BeaconCount = (int)reader["BeaconCount"]; shipMaster.BlockchainStatus = reader["BlockchainStatus"].ToString(); shipMaster.TemperatureBreachCount = (int)reader["TemperatureBreach"]; shipMaster.HumidityBreachCount = (int)reader["HumidityBreach"]; shipMaster.ShockVibrationCount = (int)reader["VibrationBreach"]; shipMaster.TamperBreachCount = (int)reader["TamperBreach"]; shipMaster.UnreachableDeviceCount = (int)reader["UnreachableDevice"]; string gatewayID = GetMacId(shipMaster.ShipmasterID); GatewayLocation objLocation = new GatewayLocation(); 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"; //var test = "SELECT * FROM c where c.current_gps_time='235952.207'"; IQueryable <GatewayLocation> cosTelemetry = client.CreateDocumentQuery <GatewayLocation>(collectionlinK, cosmosTelemeterySelect); objLocation = cosTelemetry.ToList().SingleOrDefault(); if (objLocation != null) { if (objLocation.current_latitude == 0) { shipMaster.CurrentLatitude = objLocation.last_recorded_latitude; } else { shipMaster.CurrentLatitude = objLocation.current_latitude; } if (objLocation.current_longitude == 0) { shipMaster.CurrentLongitude = objLocation.last_recorded_longitude; } else { shipMaster.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()) ) //{ // shipMaster.CurrentGPSTime = objLocation.last_recorded_gps_time; //} if (string.IsNullOrEmpty(objLocation.current_gps_time)) { shipMaster.CurrentGPSTime = objLocation.last_recorded_gps_time; } else { shipMaster.CurrentGPSTime = objLocation.current_gps_time; } } ///GPS Data Test GatewayLocation objGPS = new GatewayLocation(); int trackerId = GetTrackerId(gatewayID); objGPS = GetGPSData(trackerId); shipMaster.CurrentLatitude = objGPS.current_latitude; shipMaster.CurrentLongitude = objGPS.current_longitude; shipMaster.CurrentGPSTime = objGPS.current_gps_time; /////GPS///////////////// shipMasterList.Add(shipMaster); } reader.Close(); conn.Close(); } } return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(JsonConvert.SerializeObject(shipMasterList, Formatting.Indented), Encoding.UTF8, "application/json") }); }