def global_statistics():
mysql = FreifunkMySQL()
stats = mysql.fetchall("SELECT * FROM stats_global")
return helper_statistics(mysql, stats, None, None)
def global_hoodstatistics(selecthood):
mysql = FreifunkMySQL()
stats = mysql.fetchall("SELECT * FROM stats_hood WHERE hood = %s",
(selecthood, ))
return helper_statistics(mysql, stats, selecthood, None)
def router_info(dbid):
try:
mysql = FreifunkMySQL()
router = mysql.findone(
"""SELECT * FROM router WHERE id = %s LIMIT 1""", (dbid, ))
mac = None
if router:
if request.args.get('fffconfig', None) != None:
mysql.close()
s = "\nconfig fff 'system'\n"
s += " option hostname '{}'\n".format(
router["hostname"])
s += " option description '{}'\n".format(
router["description"])
s += " option latitude '{}'\n".format(
router["lat"] if router["lat"] else "")
s += " option longitude '{}'\n".format(
router["lng"] if router["lng"] else "")
s += " option position_comment '{}'\n".format(
router["position_comment"])
s += " option contact '{}'\n".format(router["contact"])
return Response(s, mimetype='text/plain')
router = mysql.utcaware(router, ["created", "last_contact"])
router["user"] = mysql.findone(
"SELECT nickname FROM users WHERE email = %s",
(router["contact"], ), "nickname")
router["netifs"] = mysql.fetchall(
"""SELECT * FROM router_netif WHERE router = %s""", (dbid, ))
netifs = []
for n in router["netifs"]:
n["ipv6_addrs"] = mysql.fetchall(
"""SELECT ipv6 FROM router_ipv6 WHERE router = %s AND netif = %s""",
(
dbid,
n["netif"],
), "ipv6")
if n["netif"] == "br-mesh":
mac = n["mac"]
netifs.append(n["netif"])
router["neighbours"] = mysql.fetchall(
"""
SELECT nb.mac, nb.netif, nb.quality, r.hostname, r.id
FROM router_neighbor AS nb
LEFT JOIN (
SELECT router, mac FROM router_netif GROUP BY mac, router
) AS net ON nb.mac = net.mac
LEFT JOIN router as r ON net.router = r.id
WHERE nb.router = %s
ORDER BY nb.quality DESC
""", (dbid, ))
# FIX SQL: only one from router_netif
router["gws"] = mysql.fetchall(
"""
SELECT router_gw.mac AS mac, quality, router_gw.netif AS netif, gw_class, selected, gw.name AS gw, n1.netif AS gwif, n2.netif AS batif, n2.mac AS batmac
FROM router_gw
LEFT JOIN (
gw_netif AS n1
INNER JOIN gw ON n1.gw = gw.id
LEFT JOIN gw_netif AS n2 ON n1.mac = n2.vpnmac AND n1.gw = n2.gw
) ON router_gw.mac = n1.mac
WHERE router = %s
""", (dbid, ))
for gw in router["gws"]:
gw["label"] = gw_name(gw)
gw["batX"] = gw_bat(gw)
router["events"] = mysql.fetchall(
"""SELECT * FROM router_events WHERE router = %s""", (dbid, ))
router["events"] = mysql.utcawaretuple(router["events"], "time")
## Create json with all data except stats
if request.args.get('json', None) != None:
mysql.close()
return Response(bson2json(router, sort_keys=True, indent=4),
mimetype='application/json')
cwan = "blue"
cclient = "orange"
cbatman = "#29c329"
cvpn = "red"
chidden = "gray"
## Label netifs AFTER json if clause
for n in router["netifs"]:
netif = n["netif"]
desc = None
color = None
if netif == 'br-mesh':
desc = "Bridge"
elif netif.endswith('.1'):
desc = "Clients via Ethernet"
color = cclient
elif netif.endswith('.2'):
desc = "WAN"
color = cwan
elif netif.endswith('.3'):
desc = "Mesh via Ethernet"
color = cbatman
elif netif == "w2ap":
desc = "Clients @ 2.4 GHz"
color = cclient
elif netif == "w2mesh" or netif == "w2ibss":
desc = "Mesh @ 2.4 GHz"
color = cbatman
elif netif == "w2configap":
desc = "Config @ 2.4 GHz"
color = chidden
elif netif == "w5ap":
desc = "Clients @ 5 GHz"
color = cclient
elif netif == "w5mesh" or netif == "w5ibss":
desc = "Mesh @ 5 GHz"
color = cbatman
elif netif == "w5configap":
desc = "Config @ 5 GHz"
color = chidden
elif netif == "fffVPN":
desc = "Fastd VPN Tunnel"
color = cvpn
elif netif.startswith("l2tp"):
desc = "L2TP VPN Tunnel"
color = cvpn
elif netif.startswith("bat"):
desc = "Batman Interface"
elif netif.startswith("eth") and any(
item.startswith("{}.".format(netif))
for item in netifs):
desc = "Switch"
elif netif == "eth1":
# already known from above: no switch; no one-port, as there must be eth0
if not "eth0" in netifs or any(
item.startswith("eth0.") for item in netifs):
desc = "WAN"
color = cwan
else:
# Second port of Nanostation M2
desc = "Ethernet Multi-Port"
elif netif == "eth0":
if any(item.startswith("eth1.") for item in netifs):
# already known from above: no switch
desc = "WAN"
color = cwan
else:
# First port of Nanostation M2 or ONE-Port
desc = "Ethernet Multi-Port"
n["description"] = desc
n["color"] = color
## Set color for neighbors AFTER json if clause
for n in router["neighbours"]:
n["color"] = neighbor_color(n["quality"], n["netif"],
router["routing_protocol"])
router["stats"] = mysql.fetchall(
"""SELECT * FROM router_stats WHERE router = %s""", (dbid, ))
for s in router["stats"]:
s["time"] = mysql.utcawareint(s["time"])
threshold_neighstats = (utcnow() -
datetime.timedelta(hours=24)).timestamp()
neighfetch = mysql.fetchall(
"""
SELECT quality, mac, time FROM router_stats_neighbor WHERE router = %s AND time > %s
""", (
dbid,
threshold_neighstats,
))
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)
neighident = mysql.fetchall(
"""
SELECT snb.mac, r.hostname, n.netif
FROM router_stats_neighbor AS snb
INNER JOIN router_netif AS n ON snb.mac = n.mac
INNER JOIN router AS r ON n.router = r.id
WHERE snb.router = %s AND n.netif <> 'w2ap' AND n.netif <> 'w5ap'
GROUP BY snb.mac, r.hostname, n.netif
""", (dbid, ))
neighlabel = {}
for ni in neighident:
label = ni["hostname"]
# add network interface when there are multiple links to same node
for ni2 in neighident:
if label == ni2["hostname"] and ni["mac"] != ni2["mac"]:
# This shows the NEIGHBOR'S interface name
label += "@" + ni["netif"]
append = " (old)"
for nnn in router["neighbours"]:
if nnn["mac"] == ni["mac"]:
append = ""
neighlabel[ni["mac"]] = label + append
gwfetch = mysql.fetchall(
"""
SELECT quality, mac, time FROM router_stats_gw WHERE router = %s
""", (dbid, ))
for ns in gwfetch:
ns["time"] = mysql.utcawareint(ns["time"])
if request.method == 'POST':
if request.form.get("act") == "delete":
# a router may not have a owner, but admin users still can delete it
if is_authorized(router["user"], session):
delete_router(mysql, dbid)
flash(
"<b>Router <i>%s</i> deleted!</b>" %
router["hostname"], "success")
mysql.close()
return redirect(url_for("index"))
else:
flash(
"<b>You are not authorized to perform this action!</b>",
"danger")
elif request.form.get("act") == "ban":
if session.get('admin'):
if mac:
ban_router(mysql, dbid)
delete_router(mysql, dbid)
flash(
"<b>Router <i>%s</i> banned!</b>" %
router["hostname"], "success")
mysql.close()
return redirect(url_for("index"))
else:
flash(
"<b>Router has no br-mesh and thus cannot be banned!</b>",
"danger")
else:
flash(
"<b>You are not authorized to perform this action!</b>",
"danger")
elif request.form.get("act") == "changeblocked" and mac:
if session.get('admin'):
if request.form.get("blocked") == "true":
added = mysql.utcnow()
mysql.execute(
"INSERT INTO blocked (mac, added) VALUES (%s, %s)",
(
mac,
added,
))
mysql.execute(
"""
INSERT INTO router_events (router, time, type, comment)
VALUES (%s, %s, %s, %s)
""", (
dbid,
mysql.utcnow(),
"admin",
"Marked as blocked",
))
mysql.commit()
else:
mysql.execute("DELETE FROM blocked WHERE mac = %s",
(mac, ))
mysql.execute(
"""
INSERT INTO router_events (router, time, type, comment)
VALUES (%s, %s, %s, %s)
""", (
dbid,
mysql.utcnow(),
"admin",
"Removed blocked status",
))
mysql.commit()
router["events"] = mysql.fetchall(
"""SELECT * FROM router_events WHERE router = %s""",
(dbid, ))
router["events"] = mysql.utcawaretuple(
router["events"], "time")
else:
flash(
"<b>You are not authorized to perform this action!</b>",
"danger")
elif request.form.get("act") == "report":
abusemails = mysql.fetchall(
"SELECT email FROM users WHERE abuse = 1")
for a in abusemails:
send_email(
recipient=a["email"],
subject="Monitoring: Router %s reported" %
router["hostname"],
content="Hello Admin,\n\n" +
"The router with hostname %s has been reported as abusive by a user.\n"
% router["hostname"] + "Please take care:\n" +
"%s\n\n" %
url_for("router_info", dbid=dbid, _external=True) +
"Regards,\nFreifunk Franken Monitoring System")
flash("<b>Router reported to administrators!</b>",
"success")
else:
mysql.close()
return "Router not found"
router["blocked"] = mysql.findone(
"""
SELECT blocked.mac
FROM router_netif AS n
LEFT JOIN blocked ON n.mac = blocked.mac
WHERE n.router = %s AND n.netif = 'br-mesh'
""", (dbid, ), "mac")
mysql.close()
return render_template("router.html",
router=router,
mac=mac,
tileurls=tileurls,
neighstats=neighdata,
neighlabel=neighlabel,
gwstats=gwfetch,
authuser=is_authorized(router["user"], session),
authadmin=session.get('admin'))
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_router.txt", str(e))
import traceback
writefulllog(
"Warning: Failed to display router details page: %s\n__%s" %
(e, traceback.format_exc().replace("\n", "\n__")))
def user_info(nickname):
mysql = FreifunkMySQL()
user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",
(nickname, ))
user["created"] = mysql.utcaware(user["created"])
if not user:
mysql.close()
return "User not found"
if request.method == 'POST':
if request.form.get("action") == "changepw":
if is_authorized(user["nickname"], session):
if request.form["password"] != request.form["password_rep"]:
flash("<b>Passwords did not match!</b>", "danger")
elif request.form["password"] == "":
flash("<b>Password must not be empty!</b>", "danger")
else:
set_user_password(mysql, user["nickname"],
request.form["password"])
flash("<b>Password changed!</b>", "success")
else:
flash("<b>You are not authorized to perform this action!</b>",
"danger")
elif request.form.get("action") == "changemail":
if is_authorized(user["nickname"], session):
if request.form["email"] != request.form["email_rep"]:
flash("<b>E-Mail addresses do not match!</b>", "danger")
elif not "@" in request.form["email"]:
flash("<b>Invalid E-Mail addresse!</b>", "danger")
else:
try:
set_user_email(mysql, user["nickname"],
request.form["email"])
flash("<b>E-Mail changed!</b>", "success")
if not session.get('admin'):
password = base64.b32encode(
os.urandom(10)).decode()
set_user_password(mysql, user["nickname"],
password)
send_email(
recipient=request.form['email'],
subject="Password for %s" % user['nickname'],
content="Hello %s,\n\n" % user["nickname"] +
"You changed your email address on https://monitoring.freifunk-franken.de/\n"
+
"To verify your new email address your password was changed to %s\n"
% password +
"... and sent to your new address. Please log in and change it.\n\n"
+
"Regards,\nFreifunk Franken Monitoring System")
mysql.close()
return logout()
else:
# force db data reload
user = mysql.findone(
"SELECT * FROM users WHERE nickname = %s LIMIT 1",
(nickname, ))
user["created"] = mysql.utcaware(user["created"])
except AccountWithEmailExists:
flash(
"<b>There is already an account with this E-Mail Address!</b>",
"danger")
else:
flash("<b>You are not authorized to perform this action!</b>",
"danger")
elif request.form.get("action") == "changeadmin":
if session.get('admin'):
set_user_admin(mysql, nickname,
request.form.get("admin") == "true")
# force db data reload
user = mysql.findone(
"SELECT * FROM users WHERE nickname = %s LIMIT 1",
(nickname, ))
user["created"] = mysql.utcaware(user["created"])
else:
flash("<b>You are not authorized to perform this action!</b>",
"danger")
elif request.form.get("action") == "changeabuse":
if session.get('admin'):
set_user_abuse(mysql, nickname,
request.form.get("abuse") == "true")
# force db data reload
user = mysql.findone(
"SELECT * FROM users WHERE nickname = %s LIMIT 1",
(nickname, ))
user["created"] = mysql.utcaware(user["created"])
else:
flash("<b>You are not authorized to perform this action!</b>",
"danger")
elif request.form.get("action") == "deleteaccount":
if is_authorized(user["nickname"], session):
mysql.execute("DELETE FROM users WHERE nickname = %s LIMIT 1",
(nickname, ))
mysql.commit()
flash("<b>User <i>%s</i> deleted!</b>" % nickname, "success")
mysql.close()
if user["nickname"] == session.get("user"):
session.pop('user', None)
return redirect(url_for("user_list"))
else:
flash("<b>You are not authorized to perform this action!</b>",
"danger")
routers = mysql.fetchall(
"""
SELECT id, hostname, status, hood, firmware, hardware, created, sys_uptime, clients, reset, blocked, v2
FROM router
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
WHERE contact = %s
ORDER BY hostname ASC
""", (user["email"], ))
mysql.close()
routers = mysql.utcawaretuple(routers, "created")
return render_template("user.html",
user=user,
routers=routers,
routers_count=len(routers),
authuser=is_authorized(user["nickname"], session),
authadmin=session.get('admin'))
#!/usr/bin/python3
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..'))
from ffmap.mysqltools import FreifunkMySQL
mysql = FreifunkMySQL()
mysql.execute("""
CREATE TABLE `gw` (
`id` smallint(5) UNSIGNED NOT NULL,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`stats_page` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`version` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_contact` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
mysql.execute("""
ALTER TABLE `gw`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `name` (`name`)
""")
mysql.execute("""
ALTER TABLE `gw`
MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT
""")
#!/usr/bin/python3
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..'))
from ffmap.mysqltools import FreifunkMySQL
mysql = FreifunkMySQL()
mysql.execute("""
CREATE TABLE `banned` (
`mac` bigint(20) UNSIGNED NOT NULL,
`added` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
mysql.execute("""
ALTER TABLE `banned`
ADD PRIMARY KEY (`mac`)
""")
mysql.execute("""
CREATE TABLE `blocked` (
`mac` bigint(20) UNSIGNED NOT NULL,
`added` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
mysql.execute("""
ALTER TABLE `blocked`
#!/usr/bin/python3
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..'))
from ffmap.mysqltools import FreifunkMySQL
mysql = FreifunkMySQL()
mysql.execute("""
CREATE TABLE `stats_global` (
`time` int(11) NOT NULL,
`clients` mediumint(9) NOT NULL,
`online` smallint(6) NOT NULL,
`offline` smallint(6) NOT NULL,
`unknown` smallint(6) NOT NULL,
`orphaned` smallint(6) NOT NULL,
`rx` int(10) UNSIGNED DEFAULT NULL,
`tx` int(10) UNSIGNED DEFAULT 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_gw (
`time` int(11) NOT NULL,
#!/usr/bin/python3
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..'))
from ffmap.mysqltools import FreifunkMySQL
mysql = FreifunkMySQL()
mysql.execute("""
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`nickname` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`password` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`token` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`email` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`admin` tinyint(4) NOT NULL DEFAULT '0'
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
mysql.execute("""
ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD KEY `nickname` (`nickname`),
ADD KEY `email` (`email`)
""")
mysql.execute("""
#!/usr/bin/python
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..'))
from ffmap.mysqltools import FreifunkMySQL
import math
mysql = FreifunkMySQL()
hoods = [
{
"keyxchange_id": 1,
"name": "Default",
"net": "10.50.16.0/20"
},
{
"keyxchange_id": 2,
"name": "Fuerth",
"net": "10.50.32.0/21",
"position": {"lng": 10.966, "lat": 49.4814}
},
{
"keyxchange_id": 3,
"name": "Nuernberg",
"net": "10.50.40.0/21",
"position": {"lng": 11.05, "lat": 49.444}
},
{
"keyxchange_id": 4,
import base64
import datetime
client = MongoClient(tz_aware=True, connect=False)
db = client.freifunk
users = db.users.find({}, {
"nickname": 1,
"password": 1,
"email": 1,
"token": 1,
"created": 1,
"admin": 1
})
mysql = FreifunkMySQL()
cur = mysql.cursor()
for u in users:
#print(u)
cur.execute(
"""
INSERT INTO users (nickname, password, token, email, created, admin)
VALUES (%s, %s, %s, %s, %s, %s)
""", (
u.get("nickname"),
u.get("password"),
u.get("token"),
u.get("email", ""),
u.get("created"),
u.get("admin", 0),
))
#!/usr/bin/python3
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..'))
from ffmap.mysqltools import FreifunkMySQL
mysql = FreifunkMySQL()
mysql.execute("""
CREATE TABLE hoods (
`id` int(11) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`net` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`lat` double DEFAULT NULL,
`lng` double DEFAULT NULL,
`cos_lat` double DEFAULT NULL,
`sin_lat` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
mysql.execute("""
ALTER TABLE hoods
ADD PRIMARY KEY (`id`),
ADD KEY `name` (`name`),
ADD KEY `lat` (`lat`),
ADD KEY `lng` (`lng`),
ADD KEY `cos_lat` (`cos_lat`),
ADD KEY `sin_lat` (`sin_lat`)
#!/usr/bin/python3
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..'))
from ffmap.mysqltools import FreifunkMySQL
mysql = FreifunkMySQL()
mysql.execute("""
CREATE TABLE `hoods` (
`id` smallint(6) UNSIGNED NOT NULL,
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
mysql.execute("""
ALTER TABLE `hoods`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `name` (`name`)
""")
mysql.execute("""
ALTER TABLE `hoods`
MODIFY `id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT
""")
mysql.execute("""
ALTER TABLE hoods AUTO_INCREMENT = 30001
""")
#!/usr/bin/python3
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..'))
from ffmap.mysqltools import FreifunkMySQL
mysql = FreifunkMySQL()
mysql.execute("""
CREATE TABLE router (
`id` int(11) NOT NULL,
`status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`hostname` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`last_contact` datetime NOT NULL,
`sys_time` datetime NOT NULL,
`sys_uptime` int(11) NOT NULL,
`sys_memfree` int(11) NOT NULL,
`sys_membuff` int(11) NOT NULL,
`sys_memcache` int(11) NOT NULL,
`sys_loadavg` double NOT NULL,
`sys_procrun` smallint(6) NOT NULL,
`sys_proctot` smallint(6) NOT NULL,
`clients` smallint(6) NOT NULL,
`wan_uplink` tinyint(1) NOT NULL,
`cpu` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`chipset` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`hardware` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`os` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
def global_gwstatistics(selectgw):
mysql = FreifunkMySQL()
stats = mysql.fetchall("SELECT * FROM stats_gw WHERE mac = %s",
(selectgw, ))
return helper_statistics(mysql, stats, None, selectgw)
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '..'))
from ffmap.mysqltools import FreifunkMySQL
import pymongo
from bson.json_util import dumps as bson2json
from bson.objectid import ObjectId
import base64
import datetime
import csv
targetfile = "/data/fff/users.txt"
mysql = FreifunkMySQL()
data = []
with open(targetfile, newline='') as csvfile:
spamreader = csv.reader(csvfile, delimiter=';')
for row in spamreader:
if row[5] == "None":
row[5] = None
if row[1] == "None":
row[1] = None
if row[1] == "None":
row[1] = None
if row[2] == "None":
row[2] = None
if row[3] == "None":
row[3] = None
if row[4] == "True":
#!/usr/bin/python3
# Execute once daily, also 2 min after full 5 mins (so it does not coincide with alfred)
import os
import sys
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '..'))
from ffmap.routertools import delete_old_stats
from ffmap.mysqltools import FreifunkMySQL
import time
start_time = time.time()
mysql = FreifunkMySQL()
delete_old_stats(mysql)
mysql.close()
print("--- Total duration: %.3f seconds ---" % (time.time() - start_time))
def routers():
# Suppresses routers without br-mesh
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT router.id, hostname, status, hood, contact, nickname, hardware, firmware, clients, lat, lng, last_contact, mac
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
LEFT JOIN users ON router.contact = users.email
WHERE netif = 'br-mesh'
""")
router_data = mysql.utcawaretuple(router_data, "last_contact")
router_net = mysql.fetchall("""
SELECT id, netif, COUNT(router) AS count
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
GROUP BY id, netif
""")
mysql.close()
net_dict = {}
for rs in router_net:
if not rs["id"] in net_dict:
net_dict[rs["id"]] = []
net_dict[rs["id"]].append(rs["netif"])
nodelist_data = {'version': '1.0.0'}
nodelist_data['nodes'] = list()
for router in router_data:
fastd = 0
l2tp = 0
hood = router['hood']
user = router['nickname']
firmware = router['firmware']
mac = router['mac']
if router["id"] in net_dict:
for netif in net_dict[router["id"]]:
if netif == 'fffVPN':
fastd += 1
elif netif.startswith('l2tp'):
l2tp += 1
#elif netif['netif'] == 'br-mesh' and 'mac' in netif:
# mac = netif["mac"]
nodelist_data['nodes'].append({
'id':
str(router['id']),
'name':
router['hostname'],
'mac':
mac,
'hood':
hood,
'status':
router['status'],
'user':
user,
'hardware':
router['hardware'],
'firmware':
firmware,
'href':
'https://monitoring.freifunk-franken.de/routers/' +
str(router['id']),
'clients':
router['clients'],
'lastcontact':
router['last_contact'].isoformat(),
'uplink': {
'fastd': fastd,
'l2tp': l2tp
}
})
nodelist_data['nodes'][-1]['position'] = {
'lat': router['lat'],
'lng': router['lng']
}
return jsonify(nodelist_data)
