def v2_routers():
try:
mysql = FreifunkMySQL()
statsv2 = mysql.fetchall("""
SELECT time, CAST(SUM(clients) AS SIGNED) clients, CAST(SUM(online) AS SIGNED) online, CAST(SUM(offline) AS SIGNED) offline, CAST(SUM(unknown) AS SIGNED) unknown, CAST(SUM(orphaned) AS SIGNED) orphaned, CAST(SUM(rx) AS SIGNED) rx, CAST(SUM(tx) AS SIGNED) tx
FROM stats_hood
INNER JOIN hoods ON hoods.id = stats_hood.hood
LEFT JOIN hoodsv2 ON hoodsv2.name = hoods.name
WHERE time > 1531612800 AND ( hoodsv2.id IS NOT NULL OR hoods.name REGEXP '[vV]2$' )
GROUP BY time
""")
statsv1 = mysql.fetchall("""
SELECT time, CAST(SUM(clients) AS SIGNED) clients, CAST(SUM(online) AS SIGNED) online, CAST(SUM(offline) AS SIGNED) offline, CAST(SUM(unknown) AS SIGNED) unknown, CAST(SUM(orphaned) AS SIGNED) orphaned, CAST(SUM(rx) AS SIGNED) rx, CAST(SUM(tx) AS SIGNED) tx
FROM stats_hood
INNER JOIN hoods ON hoods.id = stats_hood.hood
WHERE time > 1531612800 AND ( hoods.id > 9999 AND hoods.id < 11000 )
GROUP BY time
""")
mysql.close()
statsv2 = mysql.utcawaretupleint(statsv2,"time")
statsv1 = mysql.utcawaretupleint(statsv1,"time")
return render_template("v2routers.html",statsv2 = statsv2,statsv1 = statsv1)
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_v2.txt", str(e))
import traceback
writefulllog("Warning: Failed to display v2 page: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
def get_routers_by_nickname(nickname):
mysql = FreifunkMySQL()
users = mysql.fetchall(
"""
SELECT id
FROM users
WHERE nickname = %s
LIMIT 1
""", (nickname, ))
if len(users) == 0:
mysql.close()
return "User not found"
nodelist_data = dict()
nodelist_data['nodes'] = list()
routers = mysql.fetchall(
"""
SELECT router.id, hostname, contact, nickname, firmware, mac, fe80_addr
FROM router
INNER JOIN users ON router.contact = users.email
INNER JOIN router_netif ON router.id = router_netif.router
WHERE nickname = %s AND netif = 'br-mesh'
ORDER BY hostname ASC
""", (nickname, ))
mysql.close()
for router in routers:
nodelist_data['nodes'].append({
'name': router['hostname'],
'oid': str(router['id']),
'mac': router['mac'],
'ipv6_fe80_addr': router['fe80_addr']
})
return jsonify(nodelist_data)
def no_position():
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT router.id, hostname, contact, nickname, firmware
FROM router
LEFT JOIN users ON router.contact = users.email
WHERE lat IS NULL OR lng IS NULL
""")
mysql.close()
#nodelist_data = dict()
nodelist_data = list()
for router in router_data:
nick = router['nickname']
if not nick:
nick = ""
nodelist_data.append(
{
'name': router['hostname'],
'href': 'https://monitoring.freifunk-franken.de/routers/' + str(router['id']),
'firmware': router['firmware'],
'contact': router['contact'],
'owner': nick
}
)
nodelist_data2 = sorted(nodelist_data, key=itemgetter('owner'), reverse=False)
nodes = dict()
nodes['nodes'] = list(nodelist_data2)
return jsonify(nodes)
def router_list():
where, tuple, query_str = parse_router_list_search_query(request.args)
mysql = FreifunkMySQL()
routers = mysql.fetchall(
"""
SELECT router.id, hostname, status, hood, contact, nickname, hardware, router.created, sys_uptime, last_contact, clients, reset, blocked, v2
FROM router
LEFT JOIN users ON router.contact = users.email
LEFT JOIN (
SELECT router, blocked.mac AS blocked FROM router_netif
INNER JOIN blocked ON router_netif.mac = blocked.mac
WHERE netif = 'br-mesh'
) AS b
ON router.id = b.router
{}
ORDER BY hostname ASC
""".format(where), tuple)
mysql.close()
routers = mysql.utcawaretuple(routers, "created")
routers = mysql.utcawaretuple(routers, "last_contact")
return render_template("router_list.html",
query_str=query_str,
routers=routers,
numrouters=len(routers))
def nodelist():
mysql = FreifunkMySQL()
router_data = mysql.fetchall(
"""
SELECT id, hostname, status, clients, last_contact, lat, lng
FROM router
""", ())
router_data = mysql.utcawaretuple(router_data, "last_contact")
mysql.close()
nodelist_data = {'version': '1.0.0'}
nodelist_data['nodes'] = list()
for router in router_data:
nodelist_data['nodes'].append({
'id':
str(router['id']),
'name':
router['hostname'],
'node_type':
'AccessPoint',
'href':
'https://monitoring.freifunk-franken.de/routers/' +
str(router['id']),
'status': {
'online': router['status'] == 'online',
'clients': router['clients'],
'lastcontact': router['last_contact'].isoformat()
}
})
if router['lat'] and router['lng']:
nodelist_data['nodes'][-1]['position'] = {
'lat': router['lat'],
'long': router['lng']
}
return jsonify(nodelist_data)
def alfred2():
try:
start_time = time.time()
mysql = FreifunkMySQL()
banned = mysql.fetchall("""
SELECT mac FROM banned
""", (), "mac")
hoodsv2 = mysql.fetchall("""
SELECT name FROM hoodsv2
""", (), "name")
statstime = utcnow()
netifdict = mysql.fetchdict("SELECT id, name FROM netifs", (), "name",
"id")
hoodsdict = mysql.fetchdict("SELECT id, name FROM hoods", (), "name",
"id")
r = make_response(json.dumps({}))
r.mimetype = 'application/json'
if request.method == 'POST':
try:
alfred_data = request.get_json()
except Exception as e:
writelog(
CONFIG["debug_dir"] + "/fail_alfred2.txt",
"{} - {}".format(request.environ['REMOTE_ADDR'],
'JSON parsing failed'))
writefulllog(
"Warning: Error converting ALFRED2 data to JSON:\n__%s" %
(request.get_data(True, True).replace("\n", "\n__")))
r.headers['X-API-STATUS'] = "JSON parsing failed"
return r
if alfred_data:
# load router status xml data
i = 1
for mac, xml in alfred_data.items():
import_nodewatcher_xml(mysql, mac, xml, banned, hoodsv2,
netifdict, hoodsdict, statstime)
if (i % 500 == 0):
mysql.commit()
i += 1
mysql.commit()
r.headers['X-API-STATUS'] = "ALFRED2 data imported"
mysql.close()
writelog(
CONFIG["debug_dir"] + "/apitime.txt",
"%s - %.3f seconds (alfred2)" %
(request.environ['REMOTE_ADDR'], time.time() - start_time))
return r
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_alfred.txt",
"{} - {}".format(request.environ['REMOTE_ADDR'], str(e)))
writefulllog("Warning: Error while processing ALFRED2 data: %s\n__%s" %
(e, traceback.format_exc().replace("\n", "\n__")))
r.headers['X-API-STATUS'] = "ERROR processing ALFRED2 data"
return r
def check_login_details(nickname, password):
mysql = FreifunkMySQL()
user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",
(nickname, ))
mysql.close()
if user and check_password_hash(user.get('password', ''), password):
return user
else:
return False
def wifianalall():
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT hostname, mac, netif
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
GROUP BY id, netif
""",())
mysql.close()
return wifianalhelper(router_data,"ALL hoods")
def user_list():
mysql = FreifunkMySQL()
users = mysql.fetchall("SELECT id, nickname, email, created, admin FROM users ORDER BY nickname COLLATE utf8_unicode_ci ASC")
user_routers = stattools.router_user_sum(mysql)
mysql.close()
users = mysql.utcawaretuple(users,"created")
return render_template("user_list.html",
user_routers = user_routers,
users = users,
users_count = len(users)
)
def wifianal(selecthood):
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT hostname, mac, netif
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
WHERE hood = %s
GROUP BY id, netif
""",(selecthood,))
mysql.close()
return wifianalhelper(router_data,"Hood: " + selecthood)
def get_router_by_mac(mac):
mysql = FreifunkMySQL()
res_routers = mysql.fetchall("""
SELECT id
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
WHERE mac = %s
GROUP BY mac, id
""",(mac.lower(),))
mysql.close()
if len(res_routers) != 1:
return redirect(url_for("router_list", q="mac:%s" % mac))
else:
return redirect(url_for("router_info", dbid=res_routers[0]["id"]))
def register_user(nickname, email, password):
if not nickname or not email:
raise AccountWithEmptyField()
mysql = FreifunkMySQL()
user_with_nick = mysql.findone("SELECT id, email FROM users WHERE nickname = %s LIMIT 1",(nickname,))
user_with_email = mysql.findone("SELECT id FROM users WHERE email = %s LIMIT 1",(email,),"id")
pw = generate_password_hash(password)
if user_with_email:
mysql.close()
raise AccountWithEmailExists()
elif user_with_nick and user_with_nick["email"]:
mysql.close()
raise AccountWithNicknameExists()
else:
time = mysql.utcnow()
if user_with_nick:
mysql.execute("""
UPDATE users
SET password = %s, email = %s, created = %s, token = NULL
WHERE id = %s
LIMIT 1
""",(pw,email,time,user_with_nick["id"],))
mysql.commit()
mysql.close()
return user_with_nick["id"]
else:
mysql.execute("""
INSERT INTO users (nickname, password, email, created, token)
VALUES (%s, %s, %s, %s, NULL)
""",(nickname,pw,email,time,))
userid = mysql.cursor().lastrowid
mysql.commit()
mysql.close()
return userid
def get_routers_by_keyxchange_id(keyxchange_id):
mysql = FreifunkMySQL()
hood = mysql.findone(
"""
SELECT name
FROM hoods
WHERE id = %s
LIMIT 1
""", (int(keyxchange_id), ))
if not hood:
mysql.close()
return "Hood not found"
nodelist_data = dict()
nodelist_data['nodes'] = list()
routers = mysql.fetchall(
"""
SELECT router.id, hostname, hardware, mac, fe80_addr, firmware, lat, lng, contact, position_comment, description
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
WHERE hood = %s AND netif = 'br-mesh'
ORDER BY hostname ASC
""", (hood["name"], ))
mysql.close()
for router in routers:
nodelist_data['nodes'].append({
'name':
router['hostname'],
'ipv6_fe80_addr':
router['fe80_addr'],
'href':
'https://monitoring.freifunk-franken.de/routers/' +
str(router['id']),
'firmware':
router['firmware'],
'hardware':
router['hardware'],
'contact':
router['contact'],
'description':
router['description']
})
nodelist_data['nodes'][-1]['position'] = {
'lat': router['lat'],
'long': router['lng']
}
if router['position_comment']:
nodelist_data['nodes'][-1]['position']['comment'] = router[
'position_comment']
return jsonify(nodelist_data)
def get_routers_by_keyxchange_id(keyxchange_id):
mysql = FreifunkMySQL()
hood = mysql.findone(
"""
SELECT name
FROM hoodsv2
WHERE id = %s
LIMIT 1
""", (int(keyxchange_id), ), "name")
mysql.close()
if not hood:
return "Hood not found"
return jsonify(nodelist_helper('AND hoods.name = %s', (hood, )))
def get_routers_by_nickname(nickname):
mysql = FreifunkMySQL()
users = mysql.fetchall(
"""
SELECT id
FROM users
WHERE nickname = %s
LIMIT 1
""", (nickname, ))
mysql.close()
if len(users) == 0:
return "User not found"
return jsonify(nodelist_helper("AND nickname = %s", (nickname, )))
def router_list():
where, tuple, query_str = parse_router_list_search_query(request.args)
mysql = FreifunkMySQL()
routers = mysql.fetchall("""
SELECT router.id, hostname, status, hood, contact, nickname, hardware, router.created, sys_uptime, clients, reset
FROM router
LEFT JOIN users ON router.contact = users.email
{}
ORDER BY hostname ASC
""".format(where),tuple)
mysql.close()
routers = mysql.utcawaretuple(routers,"created")
return render_template("router_list.html", query_str=query_str, routers=routers, numrouters=len(routers))
def get_nearest_router():
if request.args.get("layer") == "none":
r = make_response(bson2json(None))
r.mimetype = 'application/json'
return r
lng = float(request.args.get("lng"))
lat = float(request.args.get("lat"))
where = ""
if request.args.get("layer") == "v1":
where = " AND h.id IS NOT NULL "
elif request.args.get("layer") == "v2":
where = " AND h.id IS NULL "
mysql = FreifunkMySQL()
router = mysql.findone("""
SELECT r.id, r.hostname, r.lat, r.lng, r.description, r.routing_protocol,
( acos( cos( radians(%s) )
* cos( radians( r.lat ) )
* cos( radians( r.lng ) - radians(%s) )
+ sin( radians(%s) ) * sin( radians( r.lat ) )
)
) AS distance
FROM
router AS r
LEFT JOIN hoods AS h ON r.hood = h.name
WHERE r.lat IS NOT NULL AND r.lng IS NOT NULL """ + where + """
ORDER BY
distance ASC
LIMIT 1
""",(lat,lng,lat,))
router["neighbours"] = mysql.fetchall("""
SELECT nb.mac, nb.netif, nb.quality, r.hostname, r.id
FROM router_neighbor AS nb
INNER JOIN (
SELECT router, mac FROM router_netif GROUP BY mac, router
) AS net ON nb.mac = net.mac
INNER JOIN router as r ON net.router = r.id
WHERE nb.router = %s""",(router["id"],))
mysql.close()
for n in router["neighbours"]:
n["color"] = neighbor_color(n["quality"],n["netif"],router["routing_protocol"])
r = make_response(bson2json(router))
r.mimetype = 'application/json'
return r
def load_netif_stats(dbid):
netif = request.args.get("netif","")
mysql = FreifunkMySQL()
netiffetch = mysql.fetchall("""
SELECT netifs.name AS netif, rx, tx, time
FROM router_stats_netif
INNER JOIN netifs ON router_stats_netif.netif = netifs.id
WHERE router = %s AND netifs.name = %s
""",(dbid,netif,))
mysql.close()
for ns in netiffetch:
ns["time"] = {"$date": int(mysql.utcawareint(ns["time"]).timestamp()*1000)}
r = make_response(json.dumps(netiffetch))
r.mimetype = 'application/json'
return r
def alfred():
try:
start_time = time.time()
mysql = FreifunkMySQL()
#cur = mysql.cursor()
#set_alfred_data = {65: "hallo", 66: "welt"}
set_alfred_data = {}
r = make_response(json.dumps(set_alfred_data))
#import cProfile, pstats, io
#pr = cProfile.Profile()
#pr.enable()
if request.method == 'POST':
alfred_data = request.get_json()
if alfred_data:
# load router status xml data
i = 1
for mac, xml in alfred_data.get("64", {}).items():
import_nodewatcher_xml(mysql, mac, xml)
if (i % 500 == 0):
mysql.commit()
i += 1
mysql.commit()
r.headers['X-API-STATUS'] = "ALFRED data imported"
#detect_offline_routers(mysql)
#detect_orphaned_routers(mysql)
#delete_orphaned_routers(mysql)
#delete_old_stats(mysql)
#record_global_stats(mysql)
#record_hood_stats(mysql)
#update_mapnik_csv(mysql)
mysql.close()
#pr.disable()
#s = io.StringIO()
#sortby = 'cumulative'
#ps = pstats.Stats(pr, stream=s).sort_stats(sortby)
#ps.print_stats()
#print(s.getvalue())
writelog(CONFIG["debug_dir"] + "/apitime.txt",
"%.3f seconds" % (time.time() - start_time))
r.mimetype = 'application/json'
return r
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", str(e))
def dnsentries():
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT hostname, mac, MIN(ipv6) AS fd43
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
INNER JOIN router_ipv6 ON router.id = router_ipv6.router AND router_netif.netif = router_ipv6.netif
WHERE LEFT(ipv6,4) = 'fd43'
GROUP BY hostname, mac
""",())
mysql.close()
s = ""
for router in router_data:
s += router["mac"].replace(":","") + ".fff.community. 300 IN AAAA " + router["fd43"] + " ; " + router["hostname"] + "\n"
return Response(s,mimetype='text/plain')
def load_neighbor_stats(dbid):
mysql = FreifunkMySQL()
neighfetch = mysql.fetchall("""
SELECT quality, mac, time FROM router_stats_neighbor WHERE router = %s
""",(dbid,))
mysql.close()
neighdata = {}
for ns in neighfetch:
ns["time"] = {"$date": int(mysql.utcawareint(ns["time"]).timestamp()*1000)}
if not ns["mac"] in neighdata:
neighdata[ns["mac"]] = []
neighdata[ns["mac"]].append(ns)
r = make_response(json.dumps(neighdata))
r.mimetype = 'application/json'
return r
def router_mac(mac):
mysql = FreifunkMySQL()
res_routers = mysql.fetchall("""
SELECT id
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
WHERE mac = %s
GROUP BY mac, id
""",(mac2int(mac),))
mysql.close()
if len(res_routers) != 1:
return redirect(url_for("router_list", q="mac:%s" % mac))
elif request.args.get('fffconfig', None) != None:
return redirect(url_for("router_info", dbid=res_routers[0]["id"], fffconfig=1))
elif request.args.get('json', None) != None:
return redirect(url_for("router_info", dbid=res_routers[0]["id"], json=1))
else:
return redirect(url_for("router_info", dbid=res_routers[0]["id"]))
def resetpw():
try:
if request.method == 'POST':
token = base64.b32encode(os.urandom(10)).decode()
mysql = FreifunkMySQL()
user = reset_user_password(mysql, request.form['email'], token)
mysql.close()
send_email(
recipient=request.form['email'],
subject="Password reset link",
content="Hello %s,\n\n" % user["nickname"] +
"You attemped to reset your password on https://monitoring.freifunk-franken.de/\n"
+ "To verify you a reset link was sent to you:\n" +
"%s\n" % url_for('resetpw',
email=request.form['email'],
token=token,
_external=True) +
"Clicking this link will reset your password and send the new password to your email address.\n\n"
+ "Regards,\nFreifunk Franken Monitoring System")
flash(
"<b>A password reset link was sent to %s</b>" %
request.form['email'], "success")
elif "token" in request.args:
password = base64.b32encode(os.urandom(10)).decode()
mysql = FreifunkMySQL()
user = reset_user_password(mysql, request.args['email'],
request.args['token'], password)
mysql.close()
send_email(
recipient=request.args['email'],
subject="Your new Password",
content="Hello %s,\n\n" % user["nickname"] +
"You attemped to reset your password on https://monitoring.freifunk-franken.de/\n"
+ "Your new Password: %s\n" % password +
"Please log in and change it\n\n" +
"Regards,\nFreifunk Franken Monitoring System")
flash(
"<b>Password reset successful!</b> - Your password was sent to %s"
% request.args['email'], "success")
except AccountNotExisting:
flash("<b>No Account found with this E-Mail address!</b>", "danger")
except InvalidToken:
flash("<b>Invalid password token!</b>", "danger")
return render_template("resetpw.html")
def alfred():
try:
start_time = time.time()
mysql = FreifunkMySQL()
#set_alfred_data = {65: "hallo", 66: "welt"}
set_alfred_data = {}
r = make_response(json.dumps(set_alfred_data))
#import cProfile, pstats, io
#pr = cProfile.Profile()
#pr.enable()
banned = mysql.fetchall("""
SELECT mac FROM banned
""",(),"mac")
statstime = utcnow()
netifdict = mysql.fetchdict("SELECT id, name FROM netifs",(),"name","id")
if request.method == 'POST':
alfred_data = request.get_json()
if alfred_data:
# load router status xml data
i = 1
for mac, xml in alfred_data.get("64", {}).items():
import_nodewatcher_xml(mysql, mac, xml, banned, netifdict, statstime)
if (i%500 == 0):
mysql.commit()
i += 1
mysql.commit()
r.headers['X-API-STATUS'] = "ALFRED data imported"
mysql.close()
#pr.disable()
#s = io.StringIO()
#sortby = 'cumulative'
#ps = pstats.Stats(pr, stream=s).sort_stats(sortby)
#ps.print_stats()
#print(s.getvalue())
writelog(CONFIG["debug_dir"] + "/apitime.txt", "%s - %.3f seconds" % (request.environ['REMOTE_ADDR'],time.time() - start_time))
r.mimetype = 'application/json'
return r
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],str(e)))
import traceback
writefulllog("Warning: Error while processing ALFRED data: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
def dnslist():
mysql = FreifunkMySQL()
router_data = mysql.fetchall(
"""
SELECT hostname, mac, MIN(ipv6) AS fd43
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
INNER JOIN router_ipv6 ON router.id = router_ipv6.router AND router_netif.netif = router_ipv6.netif
WHERE LEFT(HEX(ipv6),4) = 'fd43'
GROUP BY hostname, mac
""", ())
mysql.close()
s = ""
for router in router_data:
s += int2shortmac(router["mac"]) + "\t" + bintoipv6(
router["fd43"]) + "\n"
return Response(s, mimetype='text/plain')
def gateways():
try:
mysql = FreifunkMySQL()
gws = stattools.gateways(mysql)
ipv4 = stattools.gws_ipv4(mysql)
ipv6 = stattools.gws_ipv6(mysql)
dhcp = stattools.gws_dhcp(mysql)
mysql.close()
return render_template("gws.html",
gws = gws,
ipv4 = ipv4,
ipv6 = ipv6,
dhcp = dhcp
)
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_gateways.txt", str(e))
import traceback
writefulllog("Warning: Failed to display gateways page: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
def get_nearest_router():
lng = float(request.args.get("lng"))
lat = float(request.args.get("lat"))
mysql = FreifunkMySQL()
res_router = mysql.findone(
"""
SELECT id, hostname, lat, lng, description,
( acos( cos( radians(%s) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(%s) )
+ sin( radians(%s) ) * sin( radians( lat ) )
)
) AS distance
FROM
router
WHERE lat IS NOT NULL AND lng IS NOT NULL
ORDER BY
distance ASC
LIMIT 1
""", (
lat,
lng,
lat,
))
res_router["neighbours"] = mysql.fetchall(
"""
SELECT nb.mac, nb.quality, nb.net_if, r.hostname, r.id
FROM router_neighbor AS nb
INNER JOIN (
SELECT router, mac FROM router_netif GROUP BY mac, router
) AS net ON nb.mac = net.mac
INNER JOIN router as r ON net.router = r.id
WHERE nb.router = %s""", (res_router["id"], ))
mysql.close()
r = make_response(bson2json(res_router))
r.mimetype = 'application/json'
return r
def global_hoodstatistics(selecthood):
mysql = FreifunkMySQL()
hoods = stattools.hoods(mysql)
stats = mysql.fetchall("SELECT * FROM stats_hood WHERE hood = %s",(selecthood,))
stats = mysql.utcawaretuple(stats,"time")
numnew = len(hoods)-18
if numnew < 1:
numnew = 1
newest_routers = mysql.fetchall("""
SELECT id, hostname, hood, created
FROM router
WHERE hardware <> 'Legacy' AND hood = %s
ORDER BY created DESC
LIMIT %s
""",(selecthood,numnew,))
newest_routers = mysql.utcawaretuple(newest_routers,"created")
clients = stattools.total_clients(mysql)
router_status = stattools.router_status(mysql)
router_models = stattools.router_models(mysql,selecthood)
router_firmwares = stattools.router_firmwares(mysql,selecthood)
hoods_sum = stattools.hoods_sum(mysql)
mysql.close()
return render_template("statistics.html",
selecthood = selecthood,
stats = stats,
clients = clients,
router_status = router_status,
router_models = router_models,
router_firmwares = router_firmwares,
hoods = hoods,
hoods_sum = hoods_sum,
newest_routers = newest_routers
)
def gwinfo():
try:
start_time = time.time()
mysql = FreifunkMySQL()
#set_data = {65: "hallo", 66: "welt"}
set_data = {}
r = make_response(json.dumps(set_data))
if request.method == 'POST':
try:
gw_data = request.get_json()
except Exception as e:
writelog(
CONFIG["debug_dir"] + "/fail_gwinfo.txt",
"{} - {}".format(request.environ['REMOTE_ADDR'],
'JSON parsing failed'))
writefulllog(
"Warning: Error converting GWINFO data to JSON:\n__%s" %
(request.get_data(True, True).replace("\n", "\n__")))
return
if gw_data:
import_gw_data(mysql, gw_data)
mysql.commit()
r.headers['X-API-STATUS'] = "GW data imported"
mysql.close()
writelog(
CONFIG["debug_dir"] + "/gwtime.txt", "%s - %.3f seconds" %
(request.environ['REMOTE_ADDR'], time.time() - start_time))
r.mimetype = 'application/json'
return r
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_gwinfo.txt",
"{} - {}".format(request.environ['REMOTE_ADDR'], str(e)))
writefulllog("Warning: Error while processing GWINFO data: %s\n__%s" %
(e, traceback.format_exc().replace("\n", "\n__")))
`time` datetime NOT NULL,
`clients` int(11) NOT NULL,
`online` int(11) NOT NULL,
`offline` int(11) NOT NULL,
`unknown` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
mysql.execute("""
ALTER TABLE `stats_global`
ADD PRIMARY KEY (`time`)
""")
mysql.execute("""
CREATE TABLE stats_hood (
`hood` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`time` datetime NOT NULL,
`clients` int(11) NOT NULL,
`online` int(11) NOT NULL,
`offline` int(11) NOT NULL,
`unknown` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
mysql.execute("""
ALTER TABLE stats_hood
ADD PRIMARY KEY (`hood`,`time`)
""")
mysql.close()
