def record_charging(self, vin, charging_status, charge_date, level,
latitude, longitude):
conn = get_db()
if charging_status == "InProgress":
try:
in_progress = conn.execute(
"SELECT stop_at FROM battery WHERE VIN=? ORDER BY start_at DESC limit 1",
(vin, )).fetchone()[0] is None
except TypeError:
in_progress = False
if not in_progress:
conn.execute(
"INSERT INTO battery(start_at,start_level,VIN) VALUES(?,?,?)",
(charge_date, level, vin))
conn.commit()
else:
try:
start_at, stop_at, start_level = conn.execute(
"SELECT start_at, stop_at, start_level from battery WHERE VIN=? ORDER BY start_at "
"DESC limit 1", (vin, )).fetchone()
in_progress = stop_at is None
if in_progress:
co2_per_kw = Ecomix.get_co2_per_kw(start_at, charge_date,
latitude, longitude)
kw = (level - start_level
) / 100 * self.vehicles_list.get_car_by_vin(
vin).battery_power
conn.execute(
"UPDATE battery set stop_at=?, end_level=?, co2=?, kw=? WHERE start_at=? and VIN=?",
(charge_date, level, co2_per_kw, kw, start_at, vin))
conn.commit()
except TypeError:
logger.debug("battery table is empty")
conn.close()
def download_song_posters(app):
"""
Download poster image from `image_url`
"""
WEB_PATH = path.join(path.dirname(path.abspath('__main__')), 'web')
DOWNLOAD_PATH = WEB_PATH + url_for('static', filename='images')
db = get_db(app)
cursor = db.cursor()
if not path.exists(DOWNLOAD_PATH):
mkdir(DOWNLOAD_PATH)
urls = PosterImage(cursor).select_urls()
for url in urls:
url = url[0]
local_url = None
try:
print("Downloading image: ", url)
local_url = download(url, out=DOWNLOAD_PATH)
local_url = path.basename(local_url)
PosterImage(cursor).update(url, local_url)
db.commit()
except Exception as error:
print("Failed to download poster image: ", url)
print(error)
db.close()
def record_position(self, vin, mileage, latitude, longitude, date, level,
level_fuel, moving):
conn = get_db()
if mileage == 0: # fix a bug of the api
logger.error("The api return a wrong mileage for %s : %f", vin,
mileage)
else:
if conn.execute("SELECT Timestamp from position where Timestamp=?",
(date, )).fetchone() is None:
temp = get_temp(latitude, longitude, self.weather_api)
if level_fuel == 0: # fix fuel level not provided when car is off
try:
level_fuel = conn.execute(
"SELECT level_fuel FROM position WHERE level_fuel>0 AND VIN=? ORDER BY Timestamp DESC "
"LIMIT 1", (vin, )).fetchone()[0]
logger.info(
"level_fuel fixed with last real value %f for %s",
level_fuel, vin)
except TypeError:
level_fuel = None
logger.info("level_fuel unfixed for %s", vin)
conn.execute(
"INSERT INTO position(Timestamp,VIN,longitude,latitude,mileage,level,level_fuel,moving,"
"temperature) VALUES(?,?,?,?,?,?,?,?,?)",
(date, vin, longitude, latitude, mileage, level,
level_fuel, moving, temp))
conn.commit()
logger.info("new position recorded for %s", vin)
clean_position(conn)
else:
logger.debug("position already saved")
def fetch_page(page_number):
collection = db.get_db()['inventory']
query = db.build_query(request.json)
batch = collection.find(query).limit(RESULT_PER_PAGE).skip(
(int(page_number) - 1) * RESULT_PER_PAGE)
batch_cnt = collection.count_documents(query)
return render_template('results.html',
data=batch,
page_cnt=batch_cnt,
pages=range(math.ceil(batch_cnt / RESULT_PER_PAGE)),
cur_page=int(page_number))
def fetch_login():
user_requested = list(db.get_db()['user'].find(
{'email': request.form['email']}))[0]
hashed_pwd = blake2b(str.encode(request.form['password']), digest_size=10)
if user_requested is not None and user_requested[
'password'] == hashed_pwd.hexdigest():
session['logged_in_user'] = {
'_id': str(user_requested['_id']),
'name': user_requested['name'],
'email': user_requested['email']
}
return json.dumps({'success': True})
return json.dumps({'success': False})
def save_rankings_to_db(ranking, app):
"""
Save ranking in song_rankings table
"""
db = get_db(app)
try:
cursor = db.cursor()
for rank in ranking:
song_name = rank.name
artist_name = rank.artist
youtube_id = rank.youtube_id
source = rank.source
song_rank = rank.ranking
week = rank.week
genre = rank.genre
artist_ids = []
for artist in artist_name:
artist_id = Artist(artist, 'singer') \
.insert(cursor).id
artist_ids.append(artist_id)
song_id = Song(song_name,
None,
None,
None,
None,
youtube_id,
artist_ids=artist_ids).insert(cursor).id
genre_id = Genre(genre).insert(cursor).id
SongGenre(song_id, genre_id).insert(cursor)
for artist_id in artist_ids:
SongArtist(song_id, artist_id).insert(cursor)
SongRankings(
song_id,
artist_id,
source,
song_rank,
week,
).insert(cursor)
except IOError as error:
print('Error while inserting new ranking ', error)
finally:
db.commit()
db.close()
def get_chargings(min=None, max=None):
conn = get_db()
if min is not None:
if max is not None:
res = conn.execute(
"select * from battery WHERE start_at>=? and start_at<=?",
(min, max)).fetchall()
else:
res = conn.execute("select * from battery WHERE start_at>=?",
(min, )).fetchall()
elif max is not None:
res = conn.execute("select * from battery WHERE start_at<=?",
(max, )).fetchall()
else:
res = conn.execute("select * from battery").fetchall()
return tuple(map(dict, res))
def get_trips() -> List[Trip]:
conn = get_db()
res = conn.execute(
'SELECT * FROM position ORDER BY Timestamp').fetchall()
trips = []
if len(res) > 1:
start = res[0]
end = res[1]
tr = Trip()
#res = list(map(dict,res))
for x in range(0, len(res) - 2):
next_el = res[x + 2]
if end["mileage"] - start["mileage"] == 0 or \
(end["Timestamp"] - start["Timestamp"]).total_seconds() / 3600 > 3:
start = end
tr = Trip()
else:
distance = next_el["mileage"] - end["mileage"] # km
duration = (next_el["Timestamp"] -
end["Timestamp"]).total_seconds() / 3600
if (
distance == 0 and duration > 0.08
) or duration > 2: # check the speed to handle missing point
tr.distance = end["mileage"] - start["mileage"] # km
if tr.distance > 0:
tr.start_at = start["Timestamp"]
tr.end_at = end["Timestamp"]
tr.add_points(end["longitude"], end["latitude"])
tr.duration = (
end["Timestamp"] -
start["Timestamp"]).total_seconds() / 3600
tr.speed_average = tr.distance / tr.duration
diff_level = start["level"] - end["level"]
tr.consumption = diff_level / 100 * BATTERY_POWER # kw
tr.consumption_km = 100 * tr.consumption / tr.distance # kw/100 km
# logger.debug(
# f"Trip: {start['Timestamp']} {tr.distance:.1f}km {tr.duration:.2f}h {tr.speed_average:.2f} km/h {tr.consumption:.2f} kw {tr.consumption_km:.2f}kw/100km")
# filter bad value
if tr.consumption_km < 70:
trips.append(tr)
start = next_el
tr = Trip()
else:
tr.add_points(end["longitude"], end["latitude"])
end = next_el
return trips
def get_recorded_position():
from geojson import Feature, Point, FeatureCollection
from geojson import dumps as geo_dumps
conn = get_db()
res = conn.execute('SELECT * FROM position ORDER BY Timestamp')
features_list = []
for row in res:
feature = Feature(geometry=Point(
(row["longitude"], row["latitude"])),
properties={
"vin": row["vin"],
"date": row["Timestamp"],
"mileage": row["mileage"],
"level": row["level"]
})
features_list.append(feature)
feature_collection = FeatureCollection(features_list)
conn.close()
return geo_dumps(feature_collection, sort_keys=True)
def get_recorded_position():
conn = get_db()
res = conn.execute('SELECT * FROM position ORDER BY Timestamp')
features_list = []
for row in res:
if row["longitude"] is None or row["latitude"] is None:
continue
feature = Feature(geometry=Point(
(row["longitude"], row["latitude"])),
properties={
"vin": row["vin"],
"date": row["Timestamp"].strftime("%x %X"),
"mileage": row["mileage"],
"level": row["level"],
"level_fuel": row["level_fuel"]
})
features_list.append(feature)
feature_collection = FeatureCollection(features_list)
conn.close()
return geo_dumps(feature_collection, sort_keys=True)
def post_register():
"""process user registration form information. logs user in if successful
Returns:
json: success status and error message if failed
"""
# check if user with this email already exist
# TODO: change error message after adding password retrieval function
user_requested = list(db.get_db()['user'].find(
{'email': request.form['email']}))
if user_requested:
return json.dumps({
'success':
False,
'message':
"User already exist. Please contact site manager for password reset."
})
# check access code
if current_app.config['ACCESS_CODE'] != request.form['access-code']:
return json.dumps({
'success': False,
'message': "Wrong access code. Please try again."
})
hashed_pwd = blake2b(str.encode(request.form['password']), digest_size=10)
new_user_info = {
"name": request.form['full-name'],
"email": request.form['email'],
"password": hashed_pwd.hexdigest(),
"affiliation": request.form['affiliation'],
"title": request.form['title']
}
new_user_id = db.insert_new_user(new_user_info)
return json.dumps({'success': True})
def record_info(self, vin, status: psac.models.status.Status):
longitude = status.last_position.geometry.coordinates[0]
latitude = status.last_position.geometry.coordinates[1]
date = status.last_position.properties.updated_at
mileage = status.timed_odometer.mileage
level = status.energy[0].level
charging_status = status.energy[0].charging.status
moving = status.kinetic.moving
conn = get_db()
if mileage == 0: # fix a bug of the api
logger.error(
f"The api return a wrong mileage for {vin} : {mileage}")
else:
if conn.execute("SELECT Timestamp from position where Timestamp=?",
(date, )).fetchone() is None:
temp = None
if self.weather_api is not None:
try:
weather_rep = requests.get(
"https://api.openweathermap.org/data/2.5/onecall",
params={
"lat": latitude,
"lon": longitude,
"exclude": "minutely,hourly,daily,alerts",
"appid": "f8ee4124ea074950b696fd3e956a7069",
"units": "metric"
})
temp = weather_rep.json()["current"]["temp"]
logger.debug(f"Temperature :{temp}c")
except Exception as e:
logger.error(
f"Unable to get temperature from openweathermap :{e}"
)
conn.execute(
"INSERT INTO position(Timestamp,VIN,longitude,latitude,mileage,level, moving, temperature) VALUES(?,?,?,?,?,?,?,?)",
(date, vin, longitude, latitude, mileage, level, moving,
temp))
conn.commit()
logger.info(f"new position recorded for {vin}")
res = conn.execute(
"SELECT Timestamp,mileage,level from position ORDER BY Timestamp DESC LIMIT 3;"
).fetchall()
# Clean DB
if len(res) == 3:
if res[0]["mileage"] == res[1]["mileage"] == res[2][
"mileage"]:
if res[0]["level"] == res[1]["level"] == res[2][
"level"]:
logger.debug("Delete duplicate line")
conn.execute(
"DELETE FROM position where Timestamp=?;",
(res[1]["Timestamp"], ))
conn.commit()
else:
logger.debug("position already saved")
# todo handle battery status
charge_date = status.energy[0].updated_at
if charging_status == "InProgress":
try:
in_progress = conn.execute(
"SELECT stop_at FROM battery WHERE VIN=? ORDER BY start_at DESC limit 1",
(vin, )).fetchone()[0] is None
except TypeError:
in_progress = False
if not in_progress:
res = conn.execute(
"INSERT INTO battery(start_at,start_level,VIN) VALUES(?,?,?)",
(charge_date, level, vin))
conn.commit()
else:
try:
start_at, stop_at, start_level = conn.execute(
"SELECT start_at, stop_at, start_level from battery WHERE VIN=? ORDER BY start_at "
"DESC limit 1", (vin, )).fetchone()
in_progress = stop_at is None
if in_progress:
co2_per_kw = Ecomix.get_co2_per_kw(start_at, charge_date,
latitude, longitude)
kw = (level - start_level) / 100 * BATTERY_POWER
res = conn.execute(
"UPDATE battery set stop_at=?, end_level=?, co2=?, kw=? WHERE start_at=? and VIN=?",
(charge_date, level, co2_per_kw, kw, start_at, vin))
conn.commit()
except TypeError:
logger.debug("battery table is empty")
except:
logger.debug("Error when saving status " +
traceback.format_exc())
conn.close()
def run_scrapers(app):
"""
Returns list of songs after running scrapers.
"""
song_count = 0
db = get_db(app)
try:
dj = DjpunjabScraper()
for song in dj.parse():
if song:
print("Got a song {}".format(song))
print('--------------------')
song_count += 1
save_song_to_db(song, db)
except Exception as e:
print("DjpunjabScraper failed: ", e)
try:
jo = DjjohalScraper()
for song in jo.parse():
if song:
print("Got a song {}".format(song))
print('--------------------')
song_count += 1
save_song_to_db(song, db)
except Exception as e:
print("DjjohalScraper failed: ", e)
db.close()
try:
jt = JattjugadScraper()
for song in jt.parse():
if song:
print("Got a song {}".format(song))
print('---------------------')
song_count += 1
save_song_to_db(song, db)
except Exception as e:
print("JattjugadScraper failed: ", e)
try:
mr = MrjattScraper()
for song in mr.parse():
if song:
print("Got a song {}".format(song))
print('---------------------')
song_count += 1
save_song_to_db(song, db)
except Exception as e:
print("MrjattScraper failed: ", e)
print('******************************')
print('** SAVED {} SONGS TO DB ***'.format(song_count))
print('******************************')
def get_trips(vehicles_list: Cars) -> Dict[str, Trips]:
conn = get_db()
vehicles = conn.execute(
"SELECT DISTINCT vin FROM position;").fetchall()
trips_by_vin = {}
for vin in vehicles:
trips = Trips()
vin = vin[0]
res = conn.execute(
'SELECT * FROM position WHERE VIN=? ORDER BY Timestamp',
(vin, )).fetchall()
if len(res) > 1:
car = vehicles_list.get_car_by_vin(vin)
assert car is not None
trip_parser = TripParser(car)
start = res[0]
end = res[1]
tr = Trip()
# for debugging use this line res = list(map(dict,res))
for x in range(0, len(res) - 2):
logger.debugv("%s mileage:%.1f level:%s level_fuel:%s",
res[x]['Timestamp'], res[x]['mileage'],
res[x]['level'], res[x]['level_fuel'])
next_el = res[x + 2]
distance = end["mileage"] - start["mileage"]
duration = (end["Timestamp"] -
start["Timestamp"]).total_seconds() / 3600
try:
speed_average = distance / duration
except ZeroDivisionError:
speed_average = 0
restart_trip = False
if trip_parser.is_refuel(start, end, distance):
restart_trip = True
elif speed_average < 0.2 and duration > 0.05:
restart_trip = True
logger.debugv("low speed detected")
if restart_trip:
start = end
tr = Trip()
logger.debugv(
"restart trip at %s mileage:%.1f level:%s level_fuel:%s",
start['Timestamp'], start['mileage'],
start['level'], start['level_fuel'])
else:
distance = next_el["mileage"] - end["mileage"] # km
duration = (next_el["Timestamp"] -
end["Timestamp"]).total_seconds() / 3600
try:
speed_average = distance / duration
except ZeroDivisionError:
speed_average = 0
end_trip = False
if trip_parser.is_refuel(end, next_el, distance):
end_trip = True
elif speed_average < 0.2 and duration > 0.05:
# (distance == 0 and duration > 0.08) or duration > 2 or
# check the speed to handle missing point
end_trip = True
logger.debugv("low speed detected")
elif duration > 2:
end_trip = True
logger.debugv("too much time detected")
elif x == len(res) - 3: # last record detected
# think if add point is needed
end = next_el
end_trip = True
logger.debugv("last position found")
if end_trip:
logger.debugv(
"stop trip at %s mileage:%.1f level:%s level_fuel:%s",
end['Timestamp'], end['mileage'], end['level'],
end['level_fuel'])
tr.distance = end["mileage"] - start[
"mileage"] # km
if tr.distance > 0:
tr.start_at = start["Timestamp"]
tr.end_at = end["Timestamp"]
tr.add_points(end["longitude"],
end["latitude"])
if end["temperature"] is not None and start[
"temperature"] is not None:
tr.add_temperature(end["temperature"])
tr.duration = (
end["Timestamp"] -
start["Timestamp"]).total_seconds() / 3600
tr.speed_average = tr.distance / tr.duration
diff_level, diff_level_fuel = trip_parser.get_level_consumption(
start, end)
tr.car = car
if diff_level != 0:
tr.set_consumption(diff_level) # kw
if diff_level_fuel != 0:
tr.set_fuel_consumption(diff_level_fuel)
tr.mileage = end["mileage"]
logger.debugv(
"Trip: %s -> %s %.1fkm %.2fh %.0fkm/h %.2fkWh %.2fkWh/100km %.2fL "
"%.2fL/100km %.1fkm", tr.start_at,
tr.end_at, tr.distance, tr.duration,
tr.speed_average, tr.consumption,
tr.consumption_km, tr.consumption_fuel,
tr.consumption_fuel_km, tr.mileage)
# filter bad value
trips.check_and_append(tr)
start = next_el
tr = Trip()
else:
tr.add_points(end["longitude"], end["latitude"])
end = next_el
trips_by_vin[vin] = trips
return trips_by_vin
