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