// GET: SQL public JsonResult Upit(DateTime start, DateTime end) { RuckusLogEntities6 obj = new RuckusLogEntities6(); DateTime startDate = start; DateTime endDate = end; var deviceCount = (from r in obj.Ruckus_Log where r.EventTypeId == 2 && (r.TimeStamp >= startDate && r.TimeStamp <= endDate) select r).Count(); return(Json(new { data1 = deviceCount }, JsonRequestBehavior.AllowGet)); }
public ActionResult Upit(string start, string end, string date1, string date2) { RuckusLogEntities6 obj = new RuckusLogEntities6(); DateTime startDate; DateTime endDate; DateTime startTime; DateTime EndTime; long DDF = obj.Device.LongCount(); List <HetmapIndex> lista = new List <HetmapIndex>(); List <DeviceList> lista1 = new List <DeviceList>(); if (date1 == null) { startDate = Convert.ToDateTime("01/09/2017" + " " + "00:00:00"); endDate = DateTime.Now; if (start == null) { startTime = Convert.ToDateTime(date1 + " " + "00:00:00"); EndTime = Convert.ToDateTime(date2 + " " + "23:59:59"); } else { startTime = Convert.ToDateTime(date1 + " " + start); EndTime = Convert.ToDateTime(date2 + " " + end); } } else { startDate = Convert.ToDateTime(date1 + " " + "00:00:00"); endDate = Convert.ToDateTime(date2 + " " + "23:59:59"); if (start == null) { startTime = Convert.ToDateTime(date1 + " " + "00:00:00"); EndTime = Convert.ToDateTime(date2 + " " + "23:59:59"); } else { startTime = Convert.ToDateTime(date1 + " " + start); EndTime = Convert.ToDateTime(date2 + " " + end); } } var results2 = from e in obj.Ruckus_Log where (e.TimeStamp >= startDate && e.TimeStamp <= endDate) group e by e.Device.DeviceId into g select new { DeviceId = g.Key, DeviceNumLogs = g.Count(), }; var results3 = from e in obj.Ruckus_Log where (e.TimeStamp >= startDate && e.TimeStamp <= endDate) && (e.TimeStamp.Value.Hour >= startTime.Hour && e.TimeStamp.Value.Hour <= EndTime.Hour) group e by e.Device.DeviceId into g select new { DeviceId = g.Key, DeviceNumLogs = g.Count(), }; var results4 = from e in obj.Ruckus_Log where (e.TimeStamp >= startDate && e.TimeStamp <= endDate && e.User.UserMac == "94:F6:65:3E:A3:D0") group e by e.Device.DeviceName into g select new { DeviceName = g.Key, UsersNumLogs = g.Count(), }; foreach (var a in results3) { HetmapIndex jerko = new HetmapIndex(); jerko.DeviceId = a.DeviceId; jerko.DeviceNumLogs = a.DeviceNumLogs; lista.Add(jerko); } var results1 = from dev in obj.Device select new { DeviceID = dev.DeviceId, DeviceMac = dev.DeviceMac, DeviceName = dev.DeviceName, GeoLat = dev.GeoLat, GeoLong = dev.GeoLong, }; foreach (var d in results1) { DeviceList jerko1 = new DeviceList(); jerko1.DeviceID = d.DeviceID; jerko1.DeviceMac = d.DeviceMac; jerko1.DeviceName = d.DeviceName; jerko1.GeoLat = d.GeoLat; jerko1.GeoLong = d.GeoLong; lista1.Add(jerko1); } foreach (DeviceList dev1 in lista1) { foreach (HetmapIndex dev2 in lista) { if (dev1.DeviceID == dev2.DeviceId) { dev1.NumLogs = dev2.DeviceNumLogs; } } } var test = from p in lista1 orderby p.NumLogs select new { DeviceID = p.DeviceID, DeviceMac = p.DeviceMac, DeviceName = p.DeviceName, GeoLat = p.GeoLat, GeoLong = p.GeoLong, NumLogs = p.NumLogs, }; var firstFiveArrivals = lista1.OrderBy(i => i.NumLogs).Take(5); var lastFiveArrivals = lista1.OrderByDescending(i => i.NumLogs).Take(5); var ZaPoslati = from rez in lista1 orderby rez.NumLogs descending select new { DeviceID = rez.DeviceID, DeviceMac = rez.DeviceMac, DeviceName = rez.DeviceName, GeoLat = rez.GeoLat, GeoLong = rez.GeoLong, NumLogs = rez.NumLogs, }; var ZaPoslati1 = from rez1 in lista1 orderby rez1.DeviceName select new { DeviceID = rez1.DeviceID, DeviceMac = rez1.DeviceMac, DeviceName = rez1.DeviceName, GeoLat = rez1.GeoLat, GeoLong = rez1.GeoLong, NumLogs = rez1.NumLogs, }; var sortiranjeNumlogs = ZaPoslati.OrderBy(i => i.NumLogs); var sortiranjeIme = ZaPoslati.OrderBy(i => i.DeviceName); return(Json(new { data = ZaPoslati, data1 = results2 }, JsonRequestBehavior.AllowGet)); }