public int QueryZonePassage(int zoneId)
{
using var context = new SmartCityZoneContext();
return(context.ZoneAreas
.Where(x => x.Sensor.Zone.ZoneId == zoneId)
.Count());
}
public AtmData QueryAtmosphere(long atmId)
{
using var context = new SmartCityZoneContext();
return(context.AtmosphereData.ToList()
.Where(x => x.AtmreadId == atmId)
.FirstOrDefault());
}
public void FlushAllData()
{
try
{
using var context = new SmartCityZoneContext();
context.Database.EnsureCreated();
Console.WriteLine("Removing all data from AtmosphereData");
context.AtmosphereData.RemoveRange(context.AtmosphereData.ToList());
Console.WriteLine("Removing all data from Sensors");
context.Sensors.RemoveRange(context.Sensors.ToList());
Console.WriteLine("Removing all data from SensorTypes");
context.SensorTypes.RemoveRange(context.SensorTypes.ToList());
Console.WriteLine("Removing all data from Vehicles");
context.Vehicles.RemoveRange(context.Vehicles.ToList());
Console.WriteLine("Removing all data from VehicleRoberies");
context.VehicleRoberies.RemoveRange(context.VehicleRoberies.ToList());
Console.WriteLine("Removing all data from Zones");
context.Zones.RemoveRange(context.Zones.ToList());
Console.WriteLine("Removing all data from ZoneAreas");
context.ZoneAreas.RemoveRange(context.ZoneAreas.ToList());
Console.WriteLine("Removing all data from ZoneParkingRegister");
context.ZoneParkingRegister.RemoveRange(context.ZoneParkingRegister.ToList());
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when purging");
throw e;
}
}
public VehicleRoberyData QueryRobery(long roberyId)
{
using var context = new SmartCityZoneContext();
return(context.VehicleRoberies.ToList()
.Where(x => x.VehicleRoberyId == roberyId)
.FirstOrDefault());
}
public int QueryZoneRobery(int zoneId)
{
using var context = new SmartCityZoneContext();
return(context.VehicleRoberies
.Where(x => x.Zone.ZoneId == zoneId)
.Count());
}
public ZoneAreaData QueryZoneArea(long zoneAreaId)
{
using var context = new SmartCityZoneContext();
return(context.ZoneAreas.ToList()
.Where(x => x.ZoneAreaId == zoneAreaId)
.FirstOrDefault());
}
public double QuerySOxDay(DateTime date)
{
using var context = new SmartCityZoneContext();
var aux = context.AtmosphereData.Where(s => s.Date.Date == date.Date).Select(a => a.SOx);
return(aux.Sum() / aux.Count());
}
public Vehicle QueryVehicle(long vehicleId)
{
using var context = new SmartCityZoneContext();
return(context.Vehicles.ToList()
.Where(x => x.VehicleId == vehicleId)
.FirstOrDefault());
}
public Sensor QuerySensor(long sensorId)
{
using var context = new SmartCityZoneContext();
return(context.Sensors.ToList()
.Where(x => x.SensorId == sensorId)
.FirstOrDefault());
}
public double QueryCoDay(DateTime date)
{
using var context = new SmartCityZoneContext();
var co = context.AtmosphereData.Where(s => s.Date.Date == date.Date).Select(a => a.CO);
return(co.Sum() / co.Count());
}
public ZoneParkingData QueryZoneParking(long zoneParkingId)
{
using var context = new SmartCityZoneContext();
return(context.ZoneParkingRegister.ToList()
.Where(x => x.ZoneParkingId == zoneParkingId)
.FirstOrDefault());
}
public SensorType QuerySensorType(long typeId)
{
using var context = new SmartCityZoneContext();
Random a = new Random();
return(context.SensorTypes.ToList()
.Where(x => x.TypeId == typeId)
.FirstOrDefault());
}
public void InsertRegisterRoberys(List <VehicleRoberyData> roberyList)
{
try
{
using var context = new SmartCityZoneContext();
context.AddRange(roberyList);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering Roberys");
throw e;
}
}
public void InsertRegisterSensor(Sensor sensor)
{
try
{
using var context = new SmartCityZoneContext();
context.Add(sensor);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering a Sensor");
throw e;
}
}
public void InsertRegisterZoneParkings(List <ZoneParkingData> zoneParkingList)
{
try
{
using var context = new SmartCityZoneContext();
context.AddRange(zoneParkingList);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering ZoneParkings");
throw e;
}
}
public void InsertRegisterAtmospheres(List <AtmData> atmList)
{
try
{
using var context = new SmartCityZoneContext();
context.AddRange(atmList);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering AtmDatas");
throw e;
}
}
public void InsertRegisterZoneArea(ZoneAreaData zoneArea)
{
try
{
using var context = new SmartCityZoneContext();
context.Add(zoneArea);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering a ZoneAreaData");
throw e;
}
}
public void InsertRegisterSensorTypes(List <SensorType> sensorTypeList)
{
try
{
using var context = new SmartCityZoneContext();
context.AddRange(sensorTypeList);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering SensorTypes");
throw e;
}
}
public void InsertRegisterVehicle(Vehicle vehicle)
{
try
{
using var context = new SmartCityZoneContext();
context.Add(vehicle);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering a Vehicle");
throw e;
}
}
public void registerZoneAtm(AtmData zoneAtmDataInfo)
{
try
{
using var context = new SmartCityZoneContext();
zoneAtmDataInfo.Sensor = context.Sensors.ToList().Where(x => x.SensorId == zoneAtmDataInfo.Sensor.SensorId).First();
context.Add(zoneAtmDataInfo);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering a Atmosphere reading");
throw e;
}
}
public void registerZonePass(ZoneAreaData zone)
{
try
{
using var context = new SmartCityZoneContext();
zone.Vehicle = context.Vehicles.Where(x => x.VehicleId == zone.Vehicle.VehicleId).First();
zone.Sensor = context.Sensors.Where(x => x.SensorId == zone.Sensor.SensorId).First();
context.Add(zone);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering a area entry");
throw e;
}
}
public void registerParkExit(long carRfid)
{
try
{
using var context = new SmartCityZoneContext();
var parkingData = context.ZoneParkingRegister
.OrderByDescending(x => x.Date)
.Where(x => x.Vehicle.VehicleId == carRfid)
.FirstOrDefault();
parkingData.Duration = DateTime.Now - parkingData.Date;
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering a Atmosphere reading");
throw e;
}
}
public void InsertRegisterSensors(List <Sensor> sensorList)
{
try
{
using var context = new SmartCityZoneContext();
sensorList = sensorList.Select(x => new Sensor
{
Zone = context.Zones.Where(y => y.ZoneId == x.Zone.ZoneId).First(),
SensorId = x.SensorId,
Type = context.SensorTypes.Where(y => y.TypeId == x.Type.TypeId).First()
}).ToList();
context.AddRange(sensorList);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering Sensors");
throw e;
}
}
public void registerParkEntery(ZoneParkingData parkingData)
{
try
{
using var context = new SmartCityZoneContext();
parkingData.Vehicle = context.Vehicles
.Where(x => x.VehicleId == parkingData.Vehicle.VehicleId)
.First();
parkingData.Sensor = context.Sensors
.Where(x => x.SensorId == parkingData.Sensor.SensorId)
.First();
context.Add(parkingData);
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Problem connecting to DB when registering a Atmosphere reading");
throw e;
}
}
public IEnumerable <Vehicle> QueryVehicleDistinctZoneData()
{
using var context = new SmartCityZoneContext();
return(context.ZoneAreas.Select(s => s.Vehicle).Distinct().ToList());
}
public List <AtmData> QueryAtmosphereZoneList(int id)
{
using var context = new SmartCityZoneContext();
return(context.AtmosphereData.Where(s => s.Sensor.Zone.ZoneId == id).ToList());
}
public List <AtmData> QueryAtmosphereList()
{
using var context = new SmartCityZoneContext();
return(context.AtmosphereData.ToList());
}
public int QueryNumberSensores()
{
using var context = new SmartCityZoneContext();
return(context.Sensors.Distinct().Count());
}
public List <VehicleRoberyData> QueryGetRoberyList()
{
using var context = new SmartCityZoneContext();
return(context.VehicleRoberies.ToList());
}
public int QueryNumberRoberys()
{
using var context = new SmartCityZoneContext();
return(context.VehicleRoberies.Count());
}
