def executeSelectOne(sql):
curs = conn.cursor()
curs.execute(sql)
data = curs.fetchone()
return data
def executeSelectAll(sql):
curs = conn.cursor()
curs.execute(sql)
data = curs.fetchall()
return data
def executeSQL(sql):
try:
print('executeSQL = {}'.format(sql))
curs = conn.cursor()
curs.execute(sql)
conn.commit()
return True
except IntegrityError:
return False
def GET(self, did=None):
'''@params:
did: (str)device id
sampling: (datetime'''
conn = pg.connect(dbname="bsolo3", user="******", password="******")
cursor = conn.cursor()
web.header('Content-Type', 'text/json')
web.header('Access-Control-Allow-Origin', '*')
if did:
sql = "SELECT content FROM raw WHERE content->>'device' LIKE %s ORDER BY id DESC LIMIT 35"
cursor.execute(sql, ('%/' + did + '/%', ))
'''
regx = re.compile('.*'+did+'/', re.IGNORECASE)
rst = [r for r in db.sensors.find({"device": regx},
{"_id": 0}).sort(
"_id", -1).limit(25)]
'''
rst = [r[0] for r in cursor.fetchall()]
if not rst:
return web.notfound()
if web.input().get('sampling', None):
#try:
sampling = to_date(web.input().get('sampling'))
_dari = time.mktime(sampling.timetuple())
_hingga = _dari + 86400
# satu hari = 86400 ms
'''
rst = [r for r in db.sensors.find(
{"$and": [{"device": regx},
{"sampling": {"$gte": _dari}},
{"sampling": {"$lt": _hingga}}]}, {_id: 0})]
'''
sql = "SELECT content FROM raw WHERE content->>'device' LIKE %s AND (content->>'sampling')::int >= %s AND (content->>'sampling')::int <= %s"
cursor.execute(sql, ('%/' + did + '/%', _dari, _hingga))
rst = [r[0] for r in cursor.fetchall()]
#except Exception as e:
# print e
out = {}
if web.input().get('raw'):
out['periodic'] = rst
else:
out['periodic'] = [map_periodic(r) for r in rst]
out["bsolo_logger"] = BSOLO_LOGGER.get(did)
else:
out = []
sql = "SELECT DISTINCT(content->>'device') FROM raw"
cursor.execute(sql)
out = [r[0] for r in cursor.fetchall()]
cursor.close()
conn.close()
return json.dumps(out)
def GET(self, did=None):
'''@params:
did: (str)device id
sampling: (datetime'''
conn = pg.connect(dbname="bsolo3", user="******", password="******")
cursor = conn.cursor()
if did:
sql = "SELECT content FROM raw WHERE content->>'device' LIKE %s ORDER BY id DESC LIMIT 35"
cursor.execute(sql, ('%/' + did + '/%', ))
'''
regx = re.compile('.*'+did+'/', re.IGNORECASE)
rst = [r for r in db.sensors.find({"device": regx},
{"_id": 0}).sort(
"_id", -1).limit(25)]
'''
rst = [r[0] for r in cursor.fetchall()]
if not rst:
return web.notfound()
if web.input().get('sampling', None):
#try:
sampling = to_date(web.input().get('sampling'))
_dari = time.mktime(sampling.timetuple())
_hingga = _dari + 86400
# satu hari = 86400 ms
'''
rst = [r for r in db.sensors.find(
{"$and": [{"device": regx},
{"sampling": {"$gte": _dari}},
{"sampling": {"$lt": _hingga}}]}, {_id: 0})]
'''
sql = "SELECT content FROM raw WHERE content->>'device' LIKE %s AND (content->>'sampling')::int >= %s AND (content->>'sampling')::int <= %s"
cursor.execute(sql, ('%/' + did + '/%', _dari, _hingga))
rst = [r[0] for r in cursor.fetchall()]
if not rst:
return "Tidak Ada Data Pada Tanggal " + web.input().get(
'sampling')
rst.reverse()
#except Exception as e:
# print e
out = {}
if web.input().get('raw'):
out['periodic'] = rst
else:
out['periodic'] = [map_periodic(r) for r in rst]
out["bsolo_logger"] = BSOLO_LOGGER.get(did)
else:
out = []
sql = "SELECT DISTINCT(content->>'device') FROM raw"
cursor.execute(sql)
out = [r[0] for r in cursor.fetchall()]
cursor.close()
conn.close()
#testing from asnan (data untuk kategori grafik) tinggal return untuk melihat
data = []
kategori = []
battery = []
signal_quality = []
tick = []
are_tick = False
distance = []
are_distance = False
wl_scale = []
are_wl_scale = False
sun_radiation_scale = []
wind_dir_scale = []
temperature_ambien_scale = []
humidity_ambien_scale = []
wind_speed_scale = []
are_klimatologi = False
r = out["periodic"]
r.reverse()
for j in r:
if "distance" in j:
are_distance = True
distance.append(j.get("distance"))
if "wl_scale" in j:
are_wl_scale = True
wl_scale.append(j.get("wl_scale"))
if "tick" in j:
if "sun_radiation_scale" and "wind_dir_scale" and "temperature_ambien_scale" and "humidity_ambien_scale" and "wind_speed_scale" in j:
are_klimatologi = True
sun_radiation_scale.append(j.get("sun_radiation_scale"))
wind_dir_scale.append(j.get("wind_dir_scale"))
temperature_ambien_scale.append(
j.get("temperature_ambien_scale"))
humidity_ambien_scale.append(
j.get("humidity_ambien_scale"))
wind_speed_scale.append(j.get("wind_speed_scale"))
tick.append(j.get("tick"))
else:
are_tick = True
tick.append(j.get("tick"))
kategori.append(j.get("sampling"))
battery.append(j.get("battery"))
signal_quality.append(j.get("signal_quality"))
#end
data.append({'name': 'signal_quality', 'data': signal_quality})
data.append({'name': 'battery', 'data': battery})
if are_distance == True:
data.append({'name': 'distance', 'data': distance})
jenis_prima = "SONAR"
if are_wl_scale == True:
data.append({'name': 'wl_scale', 'data': wl_scale})
jenis_prima = "PRESSURE"
if are_tick == True:
data.append({'name': 'tick', 'data': tick})
jenis_prima = "ARR"
if are_klimatologi == True:
data.append({'name': 'tick', 'data': tick})
data.append({
'name': 'sun_radiation_scale',
'data': sun_radiation_scale
})
data.append({'name': 'wind_dir_scale', 'data': wind_dir_scale})
data.append({
'name': 'temperature_ambien_scale',
'data': temperature_ambien_scale
})
data.append({
'name': 'humidity_ambien_scale',
'data': humidity_ambien_scale
})
data.append({'name': 'wind_speed_scale', 'data': wind_speed_scale})
jenis_prima = "KLIMATOLOGI"
conn = Agent._connection
sql = "SELECT cname from agent where prima_id = %s" % ('"' + did + '"')
result = conn.queryAll(sql)
if result:
pname = result[0][0]
else:
pname = "--"
#print result
return render.sensor.sensor_graph({
'data': str(data),
'kategori': str(kategori),
'did': did,
'jenis_prima': jenis_prima,
'pname': pname
})
def profile(page_num):
profile = db.session.query(
USERGAMESPLAYED.ID_USER, USERGAMESPLAYED.NM_GAME,
V_GAMES.DT_YEAROFRELEASE, V_GAMES.NM_GENRE,
V_GAMES.NR_CRITICSCORE).select_from(USERGAMESPLAYED).join(
V_GAMES, V_GAMES.ID_GAME == USERGAMESPLAYED.ID_GAME).filter(
USERGAMESPLAYED.ID_USER == current_user.id).paginate(
per_page=len(qtd_rows), page=page_num, error_out=True)
df_profile = db.session.query(
USERGAMESPLAYED.ID_USER, USERGAMESPLAYED.NM_GAME,
V_GAMES.DT_YEAROFRELEASE, V_GAMES.NM_GENRE,
V_GAMES.NR_CRITICSCORE).select_from(USERGAMESPLAYED).join(
V_GAMES, V_GAMES.ID_GAME == USERGAMESPLAYED.ID_GAME).filter(
USERGAMESPLAYED.ID_USER == current_user.id).count()
if " " not in current_user.name:
first_name = current_user.name.rsplit(' ', 1)[0]
last_name = " "
else:
first_name = current_user.name.rsplit(' ', 1)[0]
last_name = current_user.name.rsplit(' ', 1)[1]
df_checkgamesplayed = check_gamesplayed()
if df_checkgamesplayed is None or df_profile == 0:
recommendations_df = pd.DataFrame()
recommendations_df[[
"ID_USER", "ID_GAME", "NM_GAME", "NM_PUBLISHER", "NM_GENRE",
"QT_GAMES", "NR_CRITICSCORE", "DT_YEAROFRELEASE", "IC_PLAYED",
"Score"
]] = ""
disable = True
else:
disable = False
recommendations_df = get_recommendation()
if request.method == 'POST':
if request.form.getlist('delete_checkbox'):
for id in request.form.getlist('delete_checkbox'):
# Delete the games that were checked and commit in the database
#USERGAMESPLAYED.query.filter_by(NM_GAME=id, ID_USER=current_user.id).delete(synchronize_session='fetch')
cursor = conn.cursor()
cursor.execute(
'DELETE FROM "USERGAMESPLAYED" WHERE "NM_GAME" = %s AND "ID_USER" = %s',
(id, current_user.id))
conn.commit()
#db.session.commit()
recommendations_df = get_recommendation()
profile = db.session.query(
USERGAMESPLAYED.ID_USER, USERGAMESPLAYED.NM_GAME,
V_GAMES.DT_YEAROFRELEASE, V_GAMES.NM_GENRE,
V_GAMES.NR_CRITICSCORE).select_from(USERGAMESPLAYED).join(
V_GAMES,
V_GAMES.ID_GAME == USERGAMESPLAYED.ID_GAME).filter(
USERGAMESPLAYED.ID_USER == current_user.id).paginate(
per_page=len(qtd_rows),
page=page_num,
error_out=True)
flash('Games have been successfully deleted from your profile.')
return render_template('profile.html',
name=current_user.name,
profile=profile,
first_name=first_name,
last_name=last_name,
len=len(recommendations_df),
recommendations_profile=recommendations_df,
disable=disable)
if not request.form.getlist('delete_checkbox'):
flash(
'You have to check at least one game to delete from your profile!'
)
return render_template('profile.html',
name=current_user.name,
profile=profile,
first_name=first_name,
last_name=last_name,
len=len(recommendations_df),
recommendations_profile=recommendations_df,
disable=disable)
return render_template('profile.html',
name=current_user.name,
profile=profile,
first_name=first_name,
last_name=last_name,
len=len(recommendations_df),
recommendations_profile=recommendations_df,
disable=disable)
