private void InsertDataEvent(ClassDATA a, string b, float c)
{
using (db = new CatLoraPostContext())
{
using (var tran = db.Database.BeginTransaction())
{
try
{
var query = new Collecteddata()
{
//Timestamp = a.DevEUI_uplink.Time,
Timestamp = DateTime.Now,
DeviceAddress = a.DevEUI_uplink.DevAddr,
DevEui = a.DevEUI_uplink.DevEUI,
SensorId = b,
ValueSensor = c,
Fport = float.Parse(a.DevEUI_uplink.FPort.Replace(" ", "")),
FcntUp = float.Parse(a.DevEUI_uplink.FCntUp.Replace(" ", "")),
FcntDw = float.Parse(a.DevEUI_uplink.FCntDn.Replace(" ", "")),
Rssi = float.Parse(a.DevEUI_uplink.LrrRSSI.Replace(" ", "")),
Snr = float.Parse(a.DevEUI_uplink.LrrSNR.Replace(" ", "")),
SubBand = a.DevEUI_uplink.SubBand,
Channel = a.DevEUI_uplink.Channel,
Lrr = a.DevEUI_uplink.Lrrid,
Timereceive = DateTime.Now
// Ack = a.DevEUI_uplink.Time,
//DeliveryStatus = a.DevEUI_uplink.Time,
//Rx1 = a.DevEUI_uplink.Time,
//Rx2 = a.DevEUI_uplink.Time,
// Beacon = a.DevEUI_uplink.ba
};
db.Collecteddata.Add(query);
db.SaveChanges();
tran.Commit();
this.Log("Insert Data Commit");
}
catch (Exception ex)
{
tran.Rollback();
this.Log(ex.Message);
}
}
}
}
public dynamic Get(string dt1, string dt2)
{
using (var db = new CatLoraPostContext())
{
DateTime startDt = (dt1 == "1970-01-01")?DateTime.Now.Date:Convert.ToDateTime(dt1);
DateTime endDt = (dt2 == "1970-01-01") ? DateTime.Now.Date : Convert.ToDateTime(dt2);
var s1 = "Sensor1";
var s2 = "Sensor2";
var query = (from a in db.Collecteddata
join b in db.Station on a.DevEui equals b.DevEui
where a.Timestamp.Date >= startDt.Date &&
a.Timestamp.Date <= endDt.Date
orderby a.Timestamp descending
select new
{
a.Timestamp,
b.DevEui,
b.DeviceAddress,
b.NameStation,
a.SensorId,
a.ValueSensor
}).ToList();
var re = from data in query
//where s1.Contains(data.SensorId.Trim()) && s2.Contains(data.SensorId.Trim())
group data by new { Timestamp = data.Timestamp.Date.AddHours(data.Timestamp.Hour) } into g
orderby g.First().Timestamp descending
select new
{
g.First().Timestamp,
g.First().DevEui,
g.First().DeviceAddress,
g.First().NameStation,
g.First().SensorId,
val = g.Sum(x => x.ValueSensor)
};
return(re);
}
}
public DoorOpenController(CatLoraPostContext _db)
{
db = _db;
}
