public static void ClassInit(TestContext testContext) { // file path of the database to create var filePath = @"C:\database\TempDb.sdf"; // delete it if it already exists if (File.Exists(filePath)) { File.Delete(filePath); } // create the SQL CE connection string - this just points to the file path string connectionString = "Datasource = " + filePath; // NEED TO SET THIS TO MAKE DATABASE CREATION WORK WITH SQL CE!!! Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0"); using (var context = new AirUDBCOE(connectionString)) { // this will create the database with the schema from the Entity Model context.Database.Create(); //context.Database.CreateIfNotExists(); } // initialize our DbContext class with the SQL CE connection string, // ready for our tests to use it. _context = new AirUDBCOE(connectionString); }
public IHttpActionResult AddAMSDataSet([FromBody]DataPoint[] dataSet) { var db = new AirUDBCOE(); db.DataPoints.AddRange(dataSet); db.SaveChanges(); return Ok(dataSet); }
public IHttpActionResult UpdateAMSDeviceState([FromBody]DeviceState[] states) { var db = new AirUDBCOE(); string deviceID = states[0].DeviceID; Device device = db.Devices.SingleOrDefault(x => x.DeviceID == deviceID); if (device == null) { // Failed to add DeviceState. return Ok("Failed to add device state with Device with ID = " + states[0].DeviceID + " not found."); } db.DeviceStates.AddRange(states); db.SaveChanges(); // Success. return Ok(states); }
public IHttpActionResult Delete(string id) { AirUDBCOE db = new AirUDBCOE(); // Validate Device from given DeviceId exists. Device registeredDevice = db.Devices.SingleOrDefault(x => x.DeviceID == id); if (registeredDevice != null) { Device toDelete = (from dev in db.Devices where dev.DeviceID == id select dev).Single(); db.Devices.Remove(toDelete); db.SaveChanges(); return Ok("Delete Successful"); } else { // Device with DeviceID: <deviceID> does not exist. return NotFound(); } }
public IHttpActionResult GetAllDevicesInMapRange([FromBody]SwaggerMapParameters para) { // SHOULD BE VARIABLE decimal latMin = para.southWest.lat; decimal latMax = para.northEast.lat; decimal longMin = para.southWest.lng; decimal longMax = para.northEast.lng; var db = new AirUDBCOE(); var results = from state in db.DeviceStates where state.Lat > latMin && state.Lat < latMax && state.Long > longMin && state.Long < longMax && state.StatePrivacy == false // Can create add in Spring && state.InOrOut == false // Can create add in Spring group state by state.DeviceID into deviceIDGroup select new { MaxStateTime = deviceIDGroup.Max(device => device.StateTime) } into MaxStates join coordinates in db.DeviceStates on MaxStates.MaxStateTime equals coordinates.StateTime into latestStateGroup select latestStateGroup.FirstOrDefault(); SwaggerAMSList amses = new SwaggerAMSList(); foreach (DeviceState d in results) { amses.AddSwaggerDevice(d.DeviceID, d.Lat, d.Long); } return Ok(amses); }
public IHttpActionResult GetLatestDataFromSingleAMSDevice([FromBody]string deviceID) { var db = new AirUDBCOE(); // Validate DeviceID represents an actual AMS device. Device registeredDevice = db.Devices.SingleOrDefault(x => x.DeviceID == deviceID); if (registeredDevice != null) { // Performs database query to obtain the latest Datapoints for specific DeviceID. SqlConnection conn = new SqlConnection(@"Data Source=mssql.eng.utah.edu;Initial Catalog=lobato;Persist Security Info=True;User ID=lobato;Password=eVHDpynh;MultipleActiveResultSets=True;Application Name=EntityFramework"); SwaggerLatestPollutantsList latestPollutants = new SwaggerLatestPollutantsList(); SwaggerLatestDataPoints latest = new SwaggerLatestDataPoints(); using (SqlConnection myConnection = conn) { string oString = @"select Devices_States_and_DataPoints.DeviceID, Devices_States_and_DataPoints.StateTime, Devices_States_and_DataPoints.MeasurementTime, Devices_States_and_DataPoints.Lat, Devices_States_and_DataPoints.Long, Devices_States_and_DataPoints.InOrOut, Devices_States_and_DataPoints.StatePrivacy, Devices_States_and_DataPoints.Value, Devices_States_and_DataPoints.PollutantName from(select DeviceID, Max(MeasurementTime) as MaxMeasurementTime, PollutantName from (select MaxStates.DeviceID, MaxStates.MaxStateTime, MeasurementTime, PollutantName from (select DeviceID, Max(StateTime) as MaxStateTime from DeviceStates where DeviceID=@deviceID group by DeviceID) as MaxStates left join Devices_States_and_DataPoints on MaxStates.DeviceID = Devices_States_and_DataPoints.DeviceID and MaxStates.MaxStateTime = Devices_States_and_DataPoints.StateTime) as MaxStatesAndMeasurementTime group by DeviceID, PollutantName) as MaxMeasurementTimeForPollutants left join Devices_States_and_DataPoints on MaxMeasurementTimeForPollutants.DeviceID = Devices_States_and_DataPoints.DeviceID and MaxMeasurementTimeForPollutants.PollutantName = Devices_States_and_DataPoints.PollutantName and MaxMeasurementTimeForPollutants.MaxMeasurementTime = Devices_States_and_DataPoints.MeasurementTime"; SqlCommand oCmd = new SqlCommand(oString, myConnection); oCmd.Parameters.AddWithValue("@deviceID", deviceID); myConnection.Open(); using (SqlDataReader oReader = oCmd.ExecuteReader()) { while (oReader.Read()) { latestPollutants.AddPollutantAndValue(oReader["PollutantName"].ToString(), (double)oReader["Value"]); } foreach (var item in latestPollutants.latest) { switch (item.pollutantName) { case "Altitude": latest.altitude = item.value.ToString(); break; case "CO": latest.co = item.value.ToString(); break; case "CO2": latest.co2 = item.value.ToString(); break; case "Humidity": latest.humidity = item.value.ToString(); break; case "NO2": latest.no2 = item.value.ToString(); break; case "PM": latest.pm = item.value.ToString(); break; case "Pressure": latest.pressure = item.value.ToString(); break; case "Temperature": latest.temp = item.value.ToString(); break; case "O3": latest.o3 = item.value.ToString(); break; } } myConnection.Close(); } } return Ok(latest); } else { // Device with DeviceID: <deviceID> does not exist. return NotFound(); } }
public IHttpActionResult GetUserDeviceStates() { var db = new AirUDBCOE(); string email = "*****@*****.**"; // Validate given email has associated User. User registeredUser = db.Users.SingleOrDefault(x => x.Email == email); if (registeredUser != null) { SqlConnection conn = new SqlConnection(@"Data Source=mssql.eng.utah.edu;Initial Catalog=lobato;Persist Security Info=True;User ID=lobato;Password=eVHDpynh;MultipleActiveResultSets=True;Application Name=EntityFramework"); List<SwaggerDeviceState> swaggerDeviceStates = new List<SwaggerDeviceState>(); using (SqlConnection myConnection = conn) { string oString = @"select MaxCompleteStates.DeviceID, Devices.Name, Devices.Purpose, MaxCompleteStates.StateTime, MaxCompleteStates.Lat, MaxCompleteStates.Long, MaxCompleteStates.InOrOut, MaxCompleteStates.StatePrivacy from (select MaxStates.DeviceID, MaxStates.StateTime, DeviceStates.Lat, DeviceStates.Long, DeviceStates.InOrOut, DeviceStates.StatePrivacy from (select DeviceID, Max(StateTime) as StateTime from DeviceStates group by DeviceID) as MaxStates left join DeviceStates on MaxStates.DeviceID=DeviceStates.DeviceID and MaxStates.StateTime = DeviceStates.StateTime) as MaxCompleteStates left join Devices on Devices.DeviceID=MaxCompleteStates.DeviceID where Devices.Email = @owner;"; SqlCommand oCmd = new SqlCommand(oString, myConnection); oCmd.Parameters.AddWithValue("@owner", email); myConnection.Open(); using (SqlDataReader oReader = oCmd.ExecuteReader()) { while (oReader.Read()) { swaggerDeviceStates.Add(new SwaggerDeviceState( (string)oReader["Name"], (string)oReader["DeviceID"], (bool)oReader["StatePrivacy"], (string)oReader["Purpose"], (bool) oReader["InOrOut"], (decimal)oReader["Lat"], (decimal)oReader["Long"], email)); } myConnection.Close(); } } return Ok(swaggerDeviceStates); } else { // User with email address: <email> does not exist. return NotFound(); } }
public IHttpActionResult UpdateUserDeviceState([FromBody]SwaggerDeviceState state) { var db = new AirUDBCOE(); // Validate Device from given DeviceId exists. Device registeredDevice = db.Devices.SingleOrDefault(x => x.DeviceID == state.Id); if (registeredDevice != null) { // Request previous state from database based on state.DeviceID DeviceState previousState = ( from device in db.DeviceStates where device.DeviceID == state.Id && device.StateTime <= DateTime.Now // **May be a future source of contention - REVIEW** group device by device.DeviceID into deviceIDGroup select new { DeviceID = deviceIDGroup.Key, MaxMeasurementTime = deviceIDGroup.Max(device => device.StateTime) } into MaxStates join coordinates in db.DeviceStates on MaxStates.MaxMeasurementTime equals coordinates.StateTime into latestStateGroup select latestStateGroup.FirstOrDefault()).Single(); // Inherit lat and long from previous state DeviceState newDeviceState = new DeviceState(); newDeviceState.Device = previousState.Device; newDeviceState.DeviceID = state.Id; newDeviceState.InOrOut = state.Indoor; newDeviceState.StatePrivacy = state.Privacy; newDeviceState.Lat = previousState.Lat; newDeviceState.Long = previousState.Long; newDeviceState.StateTime = DateTime.Now; db.DeviceStates.Add(newDeviceState); db.SaveChanges(); registeredDevice.Name = state.Name; registeredDevice.Purpose = state.Purpose; //db.Devices.Add(registeredDevice); db.SaveChanges(); // Send user newly updated state back to user return Ok(state); } else { // Device with DeviceID: <deviceID> does not exist. return NotFound(); } }
public IHttpActionResult RegisterUserDevice([FromBody]SwaggerDeviceState newDeviceState) { var db = new AirUDBCOE(); Device existingDevice = db.Devices.SingleOrDefault(x => x.DeviceID == newDeviceState.Id); if (existingDevice == null) { // Add device success. Device device = new Device(); device.Name = newDeviceState.Name; device.DeviceID = newDeviceState.Id; device.Email = "*****@*****.**"; // newDeviceAndState.Email; device.DevicePrivacy = newDeviceState.Privacy; device.Purpose = newDeviceState.Purpose; db.Devices.Add(device); db.SaveChanges(); DeviceState state = new DeviceState(); state.Device = device; state.DeviceID = newDeviceState.Id; state.InOrOut = newDeviceState.Indoor; state.StatePrivacy = newDeviceState.Privacy; state.StateTime = new DateTime(1900, 1, 1); state.Long = 0.0m; state.Lat = 90.0m; db.DeviceStates.Add(state); db.SaveChanges(); return Ok(newDeviceState); } else { // Add device fail. return BadRequest("Existing Device"); } }
public IHttpActionResult UserLogin([FromBody]SwaggerUser user) { var db = new AirUDBCOE(); User validUserAndPass = db.Users.SingleOrDefault(x => x.Email == user.email && x.Pass == user.pass); if (validUserAndPass != null) { // Login success. return Ok("Login Successful! Welcome, " + user.email); } else { // Login fail. return BadRequest("Login failed! Please check email and password."); } }
public IHttpActionResult UserRegistration([FromBody]SwaggerUser user) { var db = new AirUDBCOE(); User existingUser = db.Users.SingleOrDefault(x => x.Email == user.email); if (existingUser == null) { // Perform queries to insert new user into database. User newUser = new User(); newUser.Email = user.email; newUser.Pass = user.pass; db.Users.Add(newUser); db.SaveChanges(); // Account register success. return Ok("Account registration successful! Welcome, " + user.email); } else { // Account register failed. Account with email address: '<user.Email>' already exists. Please try a different email address. return BadRequest("Account registration failed! Account with email address: " + user.email + " already exists. Please try a different email address."); } }
public IHttpActionResult GetAllDataPointsForDevice([FromBody]string deviceID) { var db = new AirUDBCOE(); Device existingDevice = db.Devices.SingleOrDefault(x => x.DeviceID == deviceID); if (existingDevice != null) { List<Pollutant> pollutants = db.Pollutants.Select(x => x).ToList<Pollutant>(); List<SwaggerPollutantList> data = new List<SwaggerPollutantList>(); StringBuilder msg = new StringBuilder(); foreach (Pollutant p in pollutants) { var amsDataForPollutant = from a in db.Devices_States_and_Datapoints where a.DeviceID == deviceID && a.PollutantName == p.PollutantName orderby a.MeasurementTime select a; /* MOVE ALTITUDE TO STATE */ if (amsDataForPollutant.Count() != 0 && !p.PollutantName.Equals("Altitude")) { SwaggerPollutantList pl = new SwaggerPollutantList(p.PollutantName); foreach (var item in amsDataForPollutant) { pl.values.Add(new object[2]); pl.values.Last()[0] = ConvertDateTimeToMilliseconds(item.MeasurementTime); pl.values.Last()[1] = (decimal)item.Value; } data.Add(pl); } } return Ok(data); } else { // Account register failed. Account with email address: '<user.Email>' already exists. Please try a different email address. return BadRequest("Device with ID: " + deviceID + " does not exist. Please try a different Device ID."); } }
public IHttpActionResult ServerAndDatabaseTest() { var db = new AirUDBCOE(); List<User> allUsers = db.Users.Select(x => x).ToList<User>(); if(allUsers != null) { List<SwaggerUsers> swaggerUsers = new List<SwaggerUsers>(); foreach (var item in allUsers) { swaggerUsers.Add(new SwaggerUsers(item.Email)); } return Ok(swaggerUsers); } else { return NotFound(); } }