public Response Register(RegisterUser registerUser)
{
using (PP_testEntities context = new PP_testEntities())
{
bool isExist = context.Uzytkownicies.Any(u => u.email == registerUser.Email);
if (isExist)
{
return new Response()
{
Result = "Failed", Reason = "User is already exist"
}
}
;
var user = new Uzytkownicy()
{
email = registerUser.Email,
haslo = registerUser.Password,
nazwa_uzytk = registerUser.Name
};
context.Uzytkownicies.Add(user);
context.SaveChanges();
}
return(new Response()
{
Result = "OK"
});
}
public List <Passage> GetPassages(string authToken)
{
bool tokenExists = AuthRepository.IsTokenExist(authToken);
//#if DEBUG
// tokenExists = true;
//#endif
if (!tokenExists)
{
return(new List <Passage>());
}
string login = AuthRepository.GetLogin(authToken);
using (var context = new PP_testEntities())
{
context.Configuration.ProxyCreationEnabled = false;
context.Configuration.LazyLoadingEnabled = false;
var user = context.Uzytkownicies.First(u => u.email == login);
IQueryable <Passage> result;
if (user.administrator)
{
result = context.Przejazdy_fs
.Select(p => new Passage
{
Date = p.data_przejazdu,
UserID = p.id_uzytk,
PassageGuid = p.id_przejazdu,
Car = context.Pojazdies.Where(poj => poj.id_pojazdu == p.id_pojazdu).Select(poj => poj.marka).FirstOrDefault(),
Time = context.Tagis.Where(tag => tag.id_przejazdu == p.id_przejazdu && tag.skrot == "Time")
.Select(tag => tag.komentarz).FirstOrDefault(),
Length = context.Tagis.Where(tag => tag.id_przejazdu == p.id_przejazdu && tag.skrot == "Dist")
.Select(tag => tag.komentarz).FirstOrDefault()
});
}
else
{
result = context.Przejazdy_fs.Where(p => p.id_uzytk == user.id_uzytk)
.Select(p => new Passage
{
Date = p.data_przejazdu,
UserID = p.id_uzytk,
PassageGuid = p.id_przejazdu,
Car = context.Pojazdies.Where(poj => poj.id_pojazdu == p.id_pojazdu).Select(poj => poj.marka).FirstOrDefault(),
Time = context.Tagis.Where(tag => tag.id_przejazdu == p.id_przejazdu && tag.skrot == "Time")
.Select(tag => tag.komentarz).FirstOrDefault(),
Length = context.Tagis.Where(tag => tag.id_przejazdu == p.id_przejazdu && tag.skrot == "Dist")
.Select(tag => tag.komentarz).FirstOrDefault()
});
}
var resultList = result.ToList();
for (int i = 0; i < resultList.Count; i++)
{
resultList[i].Passage_ID = i + 1;
}
return(resultList);
}
}
public List <Interwaly> GetAllInterwals()
{
using (PP_testEntities context = new PP_testEntities())
{
List <Interwaly> list = new ListStack <Interwaly>();
foreach (var c in context.Interwalies)
{
list.Add(c);
}
return(list);
}
}
public LoginResponse LogIn(LogInUser logInUser)
{
string haslo;
if (logInUser.Password.Length < 64)
{
haslo = GetHash(logInUser.Password);
}
else
{
haslo = logInUser.Password;
}
Uzytkownicy user;
using (PP_testEntities context = new PP_testEntities())
{
user = (from u in context.Uzytkownicies
where u.email == logInUser.Email && u.haslo == haslo
select u).FirstOrDefault();
}
if (user == null)
{
return new LoginResponse()
{
Result = "Failed", Reason = "Bad email or password"
}
}
;
var time = DateTime.Now;
string str = time.ToString("yyyyMMddHHmmssfffffff") + logInUser.Email + logInUser.UserAgent;
MD5 md5 = MD5.Create();
byte[] data = md5.ComputeHash(Encoding.UTF8.GetBytes(str));
StringBuilder sBuilder = new StringBuilder();
for (int i = 0; i < data.Length; i++)
{
sBuilder.Append(data[i].ToString("x2"));
}
var token = sBuilder.ToString();
sessions.Add(token, logInUser.Email);
return(new LoginResponse()
{
Token = token, Result = "OK"
});
}
public Response UpdateInterwalies(List <Interwaly> interwaly)
{
using (PP_testEntities context = new PP_testEntities())
{
var rows = from o in context.Interwalies
select o;
foreach (var row in rows)
{
context.Interwalies.Remove(row);
}
foreach (var i in interwaly)
{
context.Interwalies.Add(i);
}
context.SaveChanges();
}
return(new Response()
{
Result = "OK"
});
}
public UserProfile GetUser(string token)
{
if (AuthRepository.IsTokenExist(token))
{
string login = AuthRepository.GetLogin(token);
using (PP_testEntities context = new PP_testEntities())
{
context.Configuration.ProxyCreationEnabled = false;
context.Configuration.LazyLoadingEnabled = false;
var user = (from u in context.Uzytkownicies.Include("Przejazdy_fs").Include("Pojazdies") where u.email == login select u).FirstOrDefault();
UserProfile userProfile = new UserProfile();
userProfile.administrator = user.administrator;
userProfile.email = user.email;
userProfile.haslo = user.haslo;
userProfile.id_uzytk = user.id_uzytk;
userProfile.imie = user.imie;
userProfile.nazwisko = user.nazwisko;
userProfile.nazwa_uzytk = user.nazwa_uzytk;
return(userProfile);
}
}
return(null);
}
public Response UpdateUser(UserProfile user)
{
using (PP_testEntities context = new PP_testEntities())
{
var userToUpdate = (from u in context.Uzytkownicies.Include("Przejazdy_fs").Include("Pojazdies")
where user.email == u.email
select u).FirstOrDefault();
if (userToUpdate != null)
{
userToUpdate.administrator = user.administrator;
userToUpdate.email = user.email;
userToUpdate.haslo = user.haslo;
userToUpdate.id_uzytk = user.id_uzytk;
userToUpdate.imie = user.imie;
userToUpdate.nazwisko = user.nazwisko;
userToUpdate.nazwa_uzytk = user.nazwa_uzytk;
}
context.SaveChanges();
}
return(new Response()
{
Result = "OK"
});
}
public void AddTagsToRide(SQLiteConnection con, PP_testEntities context, Przejazdy_fs przejazd)
{
long time = 0;
double vmax = 0;
string ride = "SELECT START_TIMESTAMP, STOP_TIMESTAMP FROM ride";
using (SQLiteCommand cmd = new SQLiteCommand(ride, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
long stop = rdr.GetInt32(1);
long start = rdr.GetInt32(0);
time = stop - start;
}
}
}
double lastLatitude;
double lastLongitude;
long lastTimestamp;
string ord = "SELECT * FROM location_data ORDER BY TIMESTAMP";
double sec;
double minutes = 0.0;
double speed = 0.0;
double seconds = 0.0;
double sum = 0.0;
using (SQLiteCommand cmd = new SQLiteCommand(ord, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
rdr.Read();
lastTimestamp = rdr.GetInt64(1);
lastLatitude = Degrees2Radius(rdr.GetDouble(2));
lastLongitude = Degrees2Radius(rdr.GetDouble(3));
while (rdr.Read())
{
long timestamp = rdr.GetInt32(1);
double latitude = Degrees2Radius(rdr.GetDouble(2));
double longitude = Degrees2Radius(rdr.GetDouble(3));
long diffTime = timestamp - lastTimestamp;
double diffLat = latitude - lastLatitude;
double diffLng = longitude - lastLongitude;
lastTimestamp = timestamp;
lastLatitude = latitude;
lastLongitude = longitude;
double a = Math.Pow(Math.Sin(diffLat / 2), 2) +
Math.Cos(lastLatitude) * Math.Cos(latitude) * Math.Pow(Math.Sin(diffLng / 2), 2);
double b = 2 * Math.Asin(Math.Sqrt(a));
double dist = Math.Round(EarthRadiusInMeters * b, 2);
double v = (dist / 1000) / ((double) diffTime / 1000 / 60 / 60);
if (v > vmax)
vmax = v;
//Console.WriteLine(string.Format("{0}m - {1:0.00} km/h - {2}ms", dist, v, diffTime));
sum += dist;
}
sec = (double) time / 1000;
minutes = sec / 60;
speed = (sum / 1000) / (minutes / 60);
seconds = (minutes - (int) minutes) * 60;
//Console.WriteLine(string.Format("Distance: {0:0.000}km, Time: {1:0}h {2}min {3:0}s, Avg speed: {4:0.00}km/h, Max speed: {5:0.00}km/h",
// (sum / 1000), (int)(minutes / 60), (int)minutes, seconds, speed, vmax));
}
}
}
var tagVMax = new Tagi();
tagVMax.id_przejazdu = przejazd.id_przejazdu;
tagVMax.skrot = Static.Tags.TagCode.MaxSpeed;
tagVMax.komentarz = string.Format("{0:0.00}km/h", vmax);
context.Tagis.Add(tagVMax);
var tagAvgSpeed = new Tagi();
tagAvgSpeed.id_przejazdu = przejazd.id_przejazdu;
tagAvgSpeed.skrot = Static.Tags.TagCode.AverageSpeed;
tagAvgSpeed.komentarz = string.Format("{0:0.00}km/h", speed);
context.Tagis.Add(tagAvgSpeed);
var tagDist = new Tagi();
tagDist.id_przejazdu = przejazd.id_przejazdu;
tagDist.skrot = Static.Tags.TagCode.Distance;
tagDist.komentarz = string.Format("{0:0.000}km", sum/1000);
context.Tagis.Add(tagDist);
var tagTime = new Tagi();
tagTime.id_przejazdu = przejazd.id_przejazdu;
tagTime.skrot = Static.Tags.TagCode.TotalTime;
tagTime.komentarz = string.Format("{0:0}h {1}min {2:0}s", (int)(minutes / 60), (int)minutes, seconds);
context.Tagis.Add(tagTime);
}
public Response Read(Reading reading)
{
bool tokenExist = AuthRepository.IsTokenExist(reading.AuthToken);
#if DEBUG
tokenExist = true;
#endif
if (tokenExist)
{
try
{
List<int> ridesIds = new List<int>();
var file = Convert.FromBase64String(reading.Content);//System.Text.Encoding.Unicode.GetBytes(reading.Content);
string fileName = DateTime.Now.ToString("yyyyMMddHHmmtt") + RandomString(5);
string tmpFilePath = Path.Combine(Path.GetTempPath(), fileName);
File.WriteAllBytes(tmpFilePath, file);
using (SQLiteConnection con = new SQLiteConnection("Data Source="+tmpFilePath))
{
con.Open();
string ride = "SELECT _id FROM ride";
using (SQLiteCommand cmd = new SQLiteCommand(ride, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
ridesIds.Add(rdr.GetInt32(0));
}
}
}
using (PP_testEntities context = new PP_testEntities())
{
if (ridesIds.Count == 1)
{
var przejazd = new Przejazdy_fs();
przejazd.dane_przejazdu = file;
var userLogins = AuthRepository.GetLogin(reading.AuthToken);
#if DEBUG
var user = (from u in context.Uzytkownicies select u).FirstOrDefault();
#else
var user = (from u in context.Uzytkownicies where u.email == userLogins select u).FirstOrDefault();
#endif
przejazd.Uzytkownicy = user;
przejazd.id_przejazdu = Guid.NewGuid();
context.Przejazdy_fs.Add(przejazd);
AddTagsToRide(con, context, przejazd);
}
else if (ridesIds.Count > 1)
{
foreach (var id in ridesIds)
{
string fname = DateTime.Now.ToString("yyyyMMddHHmmtt") + RandomString(5);
string path = Path.Combine(Path.GetTempPath(), fname);
File.WriteAllBytes(path, file);
var przejazd = new Przejazdy_fs();
using (SQLiteConnection con2 = new SQLiteConnection("Data Source=" + path))
{
con2.Open();
string query = "DELETE FROM location_data where RIDE_ID <> " + id.ToString();
SQLiteCommand cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "DELETE FROM accelerometer_data where RIDE_ID <> " + id.ToString();
cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "DELETE FROM gyroscope_data where RIDE_ID <> " + id.ToString();
cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "DELETE FROM light_data where RIDE_ID <> " + id.ToString();
cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "DELETE FROM ride where _id <> " + id.ToString();
cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
var userLogins = AuthRepository.GetLogin(reading.AuthToken);
#if DEBUG
var user = (from u in context.Uzytkownicies select u).FirstOrDefault();
#else
var user = (from u in context.Uzytkownicies where u.email == userLogins select u).FirstOrDefault();
#endif
przejazd.Uzytkownicy = user;
przejazd.id_przejazdu = Guid.NewGuid();
przejazd.dane_przejazdu = Encoding.ASCII.GetBytes(System.DateTime.Now.ToString());
context.Przejazdy_fs.Add(przejazd);
AddTagsToRide(con2, context, przejazd);
con2.Close();
GC.Collect();
GC.WaitForPendingFinalizers();
}
przejazd.dane_przejazdu = File.ReadAllBytes(path);
}
}
context.SaveChanges();
}
}
File.Delete(tmpFilePath);
return new Response() { Result = "OK" };
}
catch (Exception e)
{
return new Response() { Result = "Error", Reason = e.Message };
}
}
else
{
return new Response() {Result = "Error", Reason = "Zły token sesji"};
}
}
public PassageData GetStatistic(string authToken, string passageGuid)
{
bool tokenExists = AuthRepository.IsTokenExist(authToken);
//#if DEBUG
// tokenExists = true;
//#endif
if (!tokenExists || passageGuid == null)
{
return(new PassageData());
}
string login = AuthRepository.GetLogin(authToken);
var guid = new Guid(passageGuid);
if (_passages.ContainsKey(guid))
{
return(_passages[guid]);
}
_passages.Add(guid, new PassageData());
using (var context = new PP_testEntities())
{
var user = context.Uzytkownicies.First(u => u.email == login);
var przejazd = context.Przejazdy_fs.FirstOrDefault(p => p.id_przejazdu == guid);
if (przejazd == null || przejazd.id_uzytk != user.id_uzytk && !user.administrator)
{
return(new PassageData());
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmtt") + ReadingsRepository.RandomString(5);
string tmpFilePath = Path.Combine(Path.GetTempPath(), fileName);
File.WriteAllBytes(tmpFilePath, przejazd.dane_przejazdu);
var result = _passages[guid];
using (var con = new SQLiteConnection("Data Source=" + tmpFilePath))
{
con.Open();
string cmdstr = "SELECT * FROM location_data ORDER BY TIMESTAMP";
using (var cmd = new SQLiteCommand(cmdstr, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
long timestamp;
double lat, lng;
while (rdr.Read())
{
timestamp = rdr.GetInt64(1);
lat = rdr.GetDouble(2);
lng = rdr.GetDouble(3);
result.LocationTimestamp.Add(timestamp);
result.LocationLat.Add(lat);
result.LocationLng.Add(lng);
}
}
}
cmdstr = "SELECT * FROM accelerometer_data ORDER BY TIMESTAMP";
using (var cmd = new SQLiteCommand(cmdstr, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
long timestamp;
double x, y, z;
while (rdr.Read())
{
timestamp = rdr.GetInt64(1);
x = rdr.GetDouble(2);
y = rdr.GetDouble(3);
z = rdr.GetDouble(4);
result.AccTimestamp.Add(timestamp);
result.AccX.Add(x);
result.AccY.Add(y);
result.AccZ.Add(z);
}
}
}
cmdstr = "SELECT * FROM gyroscope_data ORDER BY TIMESTAMP";
using (var cmd = new SQLiteCommand(cmdstr, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
long timestamp;
double x, y, z;
while (rdr.Read())
{
timestamp = rdr.GetInt64(1);
x = rdr.GetDouble(2);
y = rdr.GetDouble(3);
z = rdr.GetDouble(4);
result.GyroTimestamp.Add(timestamp);
result.GyroX.Add(x);
result.GyroY.Add(y);
result.GyroZ.Add(z);
}
}
}
cmdstr = "SELECT * FROM light_data ORDER BY TIMESTAMP";
using (var cmd = new SQLiteCommand(cmdstr, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
long timestamp;
double intensity;
while (rdr.Read())
{
timestamp = rdr.GetInt64(1);
intensity = rdr.GetDouble(2);
result.LightTimestamp.Add(timestamp);
result.LightIntensity.Add(intensity);
}
}
}
}
File.Delete(tmpFilePath);
return(result);
}
}
public PassageData SplitPassageData(UpdateStatistic stat)
{
bool tokenExists = AuthRepository.IsTokenExist(stat.Token);
//#if DEBUG
// tokenExists = true;
//#endif
if (!tokenExists || stat.PassageGuid == null)
{
return(new PassageData());
}
string login = AuthRepository.GetLogin(stat.Token);
var guid = new Guid(stat.PassageGuid);
using (var context = new PP_testEntities())
{
var user = context.Uzytkownicies.First(u => u.email == login);
var przejazd = context.Przejazdy_fs.FirstOrDefault(p => p.id_przejazdu == guid);
if (przejazd == null || przejazd.id_uzytk != user.id_uzytk && !user.administrator)
{
return(new PassageData());
}
string fileNameInner = DateTime.Now.ToString("yyyyMMddHHmmtt") + ReadingsRepository.RandomString(5);
string fileNameOuter = DateTime.Now.ToString("yyyyMMddHHmmtt") + ReadingsRepository.RandomString(5);
string tmpFilePathInner = Path.Combine(Path.GetTempPath(), fileNameInner);
string tmpFilePathOuter = Path.Combine(Path.GetTempPath(), fileNameOuter);
File.WriteAllBytes(tmpFilePathInner, przejazd.dane_przejazdu);
File.WriteAllBytes(tmpFilePathOuter, przejazd.dane_przejazdu);
var nowy_przejazd = new Przejazdy_fs();
using (var con = new SQLiteConnection("Data Source=" + tmpFilePathInner))
using (var con2 = new SQLiteConnection("Data Source=" + tmpFilePathOuter))
{
con.Open();
con2.Open();
string query =
$"DELETE FROM location_data where TIMESTAMP >= {stat.StartTimestamp} and TIMESTAMP <= {stat.EndTimestamp}";
SQLiteCommand cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = $"DELETE FROM accelerometer_data where TIMESTAMP >= {stat.StartTimestamp} and TIMESTAMP <= {stat.EndTimestamp}";
cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = $"DELETE FROM gyroscope_data where TIMESTAMP >= {stat.StartTimestamp} and TIMESTAMP <= {stat.EndTimestamp}";
cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = $"DELETE FROM light_data where TIMESTAMP >= {stat.StartTimestamp} and TIMESTAMP <= {stat.EndTimestamp}";
cmd = new SQLiteCommand(query, con2);
cmd.ExecuteNonQuery();
cmd.Dispose();
query =
$"DELETE FROM location_data where TIMESTAMP < {stat.StartTimestamp} or TIMESTAMP > {stat.EndTimestamp}";
cmd = new SQLiteCommand(query, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = $"DELETE FROM accelerometer_data where TIMESTAMP < {stat.StartTimestamp} or TIMESTAMP > {stat.EndTimestamp}";
cmd = new SQLiteCommand(query, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = $"DELETE FROM gyroscope_data where TIMESTAMP < {stat.StartTimestamp} or TIMESTAMP > {stat.EndTimestamp}";
cmd = new SQLiteCommand(query, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = $"DELETE FROM light_data where TIMESTAMP < {stat.StartTimestamp} or TIMESTAMP > {stat.EndTimestamp}";
cmd = new SQLiteCommand(query, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
context.Tagis.RemoveRange(context.Tagis.Where(t => t.id_przejazdu == guid));
var repo = new ReadingsRepository();
repo.AddTagsToRide(con2, context, przejazd);
nowy_przejazd.id_przejazdu = Guid.NewGuid();
nowy_przejazd.id_pojazdu = przejazd.id_pojazdu;
nowy_przejazd.id_uzytk = przejazd.id_uzytk;
nowy_przejazd.data_przejazdu = przejazd.data_przejazdu;
repo.AddTagsToRide(con, context, nowy_przejazd);
}
przejazd.dane_przejazdu = File.ReadAllBytes(tmpFilePathOuter);
nowy_przejazd.dane_przejazdu = File.ReadAllBytes(tmpFilePathInner);
File.Delete(tmpFilePathOuter);
File.Delete(tmpFilePathInner);
context.Przejazdy_fs.Add(nowy_przejazd);
context.SaveChanges();
return(new PassageData());
}
}
