def dashboard():
error = None
if request.method == 'POST':
# Set timer session value cming from the web
session['timer'] = request.form['timer']
print 'timer: ' + session['timer']
# Get data according to the admin priv
if session['admin'] == '1':
sql = "SELECT 'admin', s.sid, s.name, 'list', DATE_FORMAT(s.birthday, '%Y-%m-%d'), "
sql += "DATE_FORMAT(s.timestamp, '%Y-%m-%d %H:%i'), s.severity, s.status "
sql += "FROM spaces s, users u WHERE u.uid = s.uid AND u.uid != '1' AND s.status NOT LIKE '%close%' GROUP BY s.name ORDER BY s.name;"
else:
sql = "SELECT '" + session['grp'] + "', s.sid, s.name, 'Me', "
sql += "DATE_FORMAT(s.birthday, '%Y-%m-%d'), DATE_FORMAT(s.timestamp, '%Y-%m-%d %H:%i'), s.severity, s.status "
sql += "FROM spaces s WHERE s.uid = '" + session[
'uid'] + "' AND u.uid != '1' AND s.status NOT LIKE '%close%' ORDER BY s.name;"
try:
spaces = exeReq(sql)
except Exception as e:
wEvent('dashboard', session['uid'],
'Database error (get dashboard data)', 'KO')
return render_template('index.html')
return jsonify(data=spaces)
else:
return 'Dashboard refreshed'
def dashboard():
try:
wEvent('/html/v1.0/dashboard', 'exeReq', 'Get', 'OK')
return render_template('dashboard.html', maps=getMaps())
except Exception as e:
wEvent('/html/v1.0/dashboard', 'exeReq', 'Get', 'KO')
return 'Dashboard error'
def saveTracker():
try:
wEvent('/html/v1.0/tracker/save', 'exeReq', 'Get', 'OK')
return 'Tracking update ongoing'
except Exception as e:
wEvent('/html/v1.0/tracker/save', 'exeReq', 'Get', 'KO')
return 'Tracker error'
def create_item(item):
#try:
if not request.json or not item in request.json:
abort(400)
if 'iot' in item:
sql = "INSERT INTO tracking SET "
sql += "uid = '{}', ".format(request.json[item]['uid'])
sql += "did = '{}', ".format(request.json[item]['did'])
sql += "humidity = '{}', ".format(request.json[item]['hum'])
sql += "luminosity = '{}', ".format(request.json[item]['lum'])
sql += "temp_amb = '{}', ".format(request.json[item]['tam'])
sql += "temp_sensor = '{}', ".format(request.json[item]['tse'])
sql += 'data = "{}";'.format(request.json)
item = 'tracking'
elif 'lebonsailounge' in item:
# Create device if not exist
did = exeReq("SELECT count(*) FROM device WHERE name = '{}';".format(
request.json[item]['device']))
did = re.sub("[^0-9]", "", "{}".format(did))
if did == 0:
sql = "INSERT INTO device SET "
sql += "name = '{}'".format(request.json[item]['device'])
sql += "status = 'to register'"
exeReq(sql)
# Insert tracking event
vals = request.json[item]['data'].split('f')
sql = "INSERT INTO tracking SET "
sql += "uid = (SELECT uid FROM device WHERE name = '{}'), ".format(
request.json[item]['device'])
sql += "did = (SELECT did FROM device WHERE name = '{}'), ".format(
request.json[item]['device'])
sql += "gps = '{},{}', ".format(request.json[item]['lat'],
request.json[item]['lng'])
sql += "humidity = '{}', ".format(vals[1])
sql += "luminosity = '{}', ".format(vals[2])
sql += "temp_amb = '{}', ".format(vals[0])
#sql += "temp_sensor = '{}', ".format(request.json[item]['tse'])
sql += 'data = "{}";'.format(request.json)
item = 'tracking'
else:
fields = get_fields(item)
sql = 'INSERT INTO {} SET '.format(item)
for field in request.json[item]:
sql += '{} = "{}", '.format(field, request.json[item][field])
sql = sql[:-2] + ';'
exeReq(sql)
res = exeReq("SELECT COUNT(*) FROM {};".format(item))
item_id = re.sub("[^0-9]", "", "{}".format(res))
wEvent('/api/v1.0/{}/{}'.format(item, item_id), 'api', 'POST', 'OK')
return jsonify({item: item_id}), 201
#except Exception as e:
wEvent('/api/v1.0/{}'.format(item), 'api', 'POST', 'KO')
abort(400)
def newCustomer():
try:
wEvent('/html/v1.0/customer/new','request','Get new user','OK')
return render_template('customer.html', maps = '')
except Exception as e:
wEvent('/html/v1.0/customer/new','request','Get new user','KO')
return 'New error'
def sparkClose(roomid):
try:
sparkRemovePeople(roomid, app.config['APP_BOT'])
room = del_room(app.config['SPARK_ACCESS_TOKEN'], roomid)
wEvent('sparkClose', roomid, "Room deleted", 'app', '1', room)
except Exception as e:
wEvent('sparkClose', roomid, "Issue during room delete", 'app', '0', e)
return 'OK'
def users():
try:
users = exeReq(
"SELECT login,email,admin,grp FROM users WHERE uid != '1';")
return render_template('users.html', users=users)
except Exception as e:
wEvent('users', 'webhook', 'Get user list error', 'KO')
return 'Get user list error'
def sparkPostMsg(roomid, msg):
try:
msg = post_markdown(app.config['SPARK_ACCESS_TOKEN'], roomid, msg)
wEvent('sparkPostMsg', roomid, "Message posted", 'app', '1', msg)
except Exception as e:
wEvent('sparkPostMsg', roomid, "Issue during post message", 'app', '0',
e)
return 'KO'
return 'OK'
def user():
try:
user = exeReq(
"SELECT login,email,mobile,admin,grp FROM users WHERE login = '******'login'] + "';")
return render_template('user.html', user=user[0])
except Exception as e:
wEvent('user', 'webhook', 'Get user error', 'KO')
return 'Get user error'
def viewCustomer():
try:
sql = "SELECT login, firstname, lastname, email, address, enterprise, mobile, password "
sql += "FROM user WHERE login = '******'login'] + "' AND grp = 'customer';"
view = exeReq(sql)
wEvent('/html/v1.0/customer/view','exeReq','Get','OK')
return render_template('customer.html', view = view[0], maps = getMaps())
except Exception as e:
wEvent('/html/v1.0/customer/view','exeReq','Get','KO')
return 'View error'
def newTracking():
try:
wEvent('/html/v1.0/tracking/new', 'request', 'Get new tracking', 'OK')
return render_template('tracking.html',
maps='',
loginList=loginList(),
nameList=nameList())
except Exception as e:
wEvent('/html/v1.0/tracking/new', 'request', 'Get new tracking', 'KO')
return 'New error'
def viewUser():
try:
sql = "SELECT uid, login, firstname, lastname, email, address, enterprise, grp, mobile, '', admin "
sql += "FROM user WHERE login = '******'login'] + "';"
view = exeReq(sql)
wEvent('/html/v1.0/user/view','exeReq','Get','OK')
return render_template('user.html', view = view[0], maps = getMaps())
except Exception as e:
wEvent('/html/v1.0/user/view','exeReq','Get','KO')
return 'View error'
def tracker():
try:
wEvent('/html/v1.0/tracker', 'exeReq', 'Get', 'OK')
return render_template('tracker.html',
maps=getMaps(),
loginList=loginList(),
nameList=nameList())
except Exception as e:
wEvent('/html/v1.0/tracker', 'exeReq', 'Get', 'KO')
return 'Tracker error'
def deleteCustomer():
try:
sql = "UPDATE user SET grp = 'deleted' WHERE login = '******'login'] + "';"
print sql
exeReq(sql)
wEvent('/html/v1.0/customer/delete','exeReq','Get','OK')
return listCustomer()
except Exception as e:
wEvent('/html/v1.0/customer/delete','exeReq','Get','KO')
return 'Delete error'
def recGPS():
gps = request.form['latitude'] + ',' + request.form['longitude']
# Check if webbrowser device is recorded, if not add it
try:
sql = "INSERT INTO device SET uid = (SELECT uid FROM user WHERE login = '******'login'] + "'), "
sql += " name = '" + request.user_agent.browser + "', status = 'ok', description = '" + request.user_agent.string + "' "
sql += "ON DUPLICATE KEY UPDATE status = 'ok';"
exeReq(sql)
wEvent('/html/v1.0/tracker/recGPS', 'exeReq',
'Add or update web device', 'OK')
except Exception as e:
wEvent('/html/v1.0/tracker/recGPS', 'exeReq',
'Add or update web device', 'KO')
return 'Add or update web device error'
# Add new localisation
try:
sql = "INSERT INTO tracking SET "
sql += " uid = (SELECT uid FROM user WHERE login = '******'login'] + "'), "
sql += " did = (SELECT did FROM device WHERE name = '" + request.user_agent.browser + "'), "
sql += " gps = '" + str(gps) + "';"
exeReq(sql)
wEvent('/html/v1.0/tracker/recGPS', 'exeReq', 'GPS record', 'OK')
return 'GPS record OK'
except Exception as e:
wEvent('/html/v1.0/tracker/recGPS', 'exeReq', 'GPS record', 'KO')
return 'GPS record error'
def sparkSearch(roomid, text):
i = 0
newmsg = app.config['SPARK_MSG_SEARCH'] + '\n'
# Remove search word
p = re.compile('^search ')
msgtofind = p.sub('', text)
# Search in the event table (history) the text msg
try:
msgs = exeReq("SELECT id, msg FROM events WHERE msg LIKE '%" +
msgtofind + "%' AND owner != 'app' AND owner != '" +
app.config['APP_MAIL'] + "'")
wEvent('sparkSearch', roomid, msgtofind, 'app', '1', msgs)
except Exception as e:
wEvent('sparkSearch', roomid, msgtofind, 'app', '0', e)
# Format and send the result
for msg in msgs:
roomlink = re.split('ciscospark://us/ROOM/',
str(base64.b64decode(msg[0])))
roomurl = 'https://web.ciscospark.com/#/rooms/' + str(roomlink[1])
newmsg += '* [' + str(i) + '](' + roomurl + ') ' + msg[1] + '\n'
i = i + 1
try:
room = post_markdown(app.config['SPARK_ACCESS_TOKEN'], roomid, newmsg)
wEvent('sparkSearch', roomid, newmsg, 'app', '1', room)
except Exception as e:
wEvent('sparkSearch', roomid,
"Issue during the post of the result message", 'app', '0', e)
return 'OK'
def sms(to, msg):
if not to:
wEvent('sendSms', 'app', str("No phone number provided"))
return ''
if not msg:
wEvent('sendSms', 'app', str("No message provided"))
return ''
client = TwilioRestClient(api.config['TWILIO_ACCOUNT_SID'],
api.config['TWILIO_AUTH_TOKEN'])
client.messages.create(
to=to,
from_=api.config['TWILIO_FROM'],
body=msg,
)
return client
def viewTracking():
try:
sql = "SELECT t.tid, u.login, d.name, t.ip, t.gps, t.url, t.website, t.webhook, t.address, t.timestamp, t.humidity, t.luminosity, t.temp_amb, t.temp_sensor, t.data "
sql += "FROM tracking t, user u, device d "
sql += "WHERE u.uid = t.uid AND t.did = d.did AND t.tid = '" + request.args[
'tracking'] + "';"
view = exeReq(sql)
wEvent('/html/v1.0/tracking/view', 'exeReq', 'Get', 'OK')
return render_template('tracking.html',
view=view[0],
maps=getMaps(),
loginList=loginList(),
nameList=nameList())
except Exception as e:
wEvent('/html/v1.0/tracking/view', 'exeReq', 'Get', 'KO')
return 'View error'
def saveTracking():
try:
sql = "INSERT INTO tracking SET tid = '" + request.form[
'tracking'] + "', "
sql += " uid = (SELECT uid FROM user WHERE login = '******'login'] + "'), "
sql += " did = (SELECT did FROM device WHERE name = '" + request.form[
'name'] + "'), "
sql += " gps = '" + request.form['gps'] + "', url = '" + request.form[
'url'] + "', "
sql += " website = '" + request.form[
'website'] + "', webhook = '" + request.form['webhook'] + "', "
sql += " address = '" + request.form[
'address'] + "', ip = '" + request.form['ip'] + "', "
sql += " humidity = '" + request.form[
'humidity'] + "', luminosity = '" + request.form[
'luminosity'] + "', "
sql += " temp_amb = '" + request.form[
'temp_amb'] + "', temp_sensor = '" + request.form[
'temp_sensor'] + "', "
sql += " data = '{}' ".format(request.json)
sql += "ON DUPLICATE KEY UPDATE "
sql += " uid = (SELECT uid FROM user WHERE login = '******'login'] + "'), "
sql += " did = (SELECT did FROM device WHERE name = '" + request.form[
'name'] + "'), "
sql += " gps = '" + request.form['gps'] + "', url = '" + request.form[
'url'] + "', "
sql += " website = '" + request.form[
'website'] + "', webhook = '" + request.form['webhook'] + "', "
sql += " address = '" + request.form[
'address'] + "', ip = '" + request.form['ip'] + "', "
sql += " humidity = '" + request.form[
'humidity'] + "', luminosity = '" + request.form[
'luminosity'] + "', "
sql += " temp_amb = '" + request.form[
'temp_amb'] + "', temp_sensor = '" + request.form[
'temp_sensor'] + "', "
sql += " data = '{}'; ".format(request.json)
exeReq(sql)
wEvent('/html/v1.0/tracking/save', 'exeReq', 'Save', 'OK')
return 'Save OK'
except Exception as e:
wEvent('/html/v1.0/tracking/save', 'exeReq', 'Save', 'KO')
return 'Save error'
def calltts(to, msg):
if not to:
wEvent('sendCall', 'app', str("No phone number provided"))
return ''
if not msg:
wEvent('sendCall', 'app', str("No message provided"))
return ''
client = TwilioRestClient(api.config['TWILIO_ACCOUNT_SID'],
api.config['TWILIO_AUTH_TOKEN'])
client.calls.create(
to=to,
from_=api.config['TWILIO_FROM'],
url=msg,
#media_url="https://climacons.herokuapp.com/clear.png",
)
return client
def sparkMsg(roomid, msgid):
# Get message
try:
msg = get_message(app.config['SPARK_ACCESS_TOKEN'], msgid)
msgtxt = msg.get('text')
except Exception as e:
wEvent('sparkMsg', roomid, 'Issue during get message data', 'app', '0',
e)
return 'KO'
# Remove the eventual bot name as first word
bot_name = app.config['APP_BOT'].split("@")[0]
msgtxt = msgtxt.split(bot_name, 1)[1]
# Search request
if (re.search('^[s|S]earch', msgtxt)):
return sparkSearch(roomid, msgtxt)
# Close request
elif (re.search('^[c|C]lose',
msgtxt)) and (msg.get('personEmail')
== app.config['SPARK_USER_STRESS']):
return sparkClose(roomid)
# Escalation request
elif (re.search('^[e|E]scalation', msgtxt)) or (re.search(
'^[e|E]scalade', msgtxt)):
return sparkEscalation(roomid, app.config['SPARK_USER_ESCALATION'])
# Validated by the Stress engineer
elif (re.search('^[v|V]alid',
msgtxt)) and (msg.get('personEmail')
== app.config['SPARK_USER_STRESS']):
return sparkAddPeople(roomid, app.config['SPARK_USER_DESIGN'])
# Tips message
#elif ( msg.get('personEmail') != app.config['APP_BOT']) and (msg.get('personEmail') != app.config['APP_MAIL']):
# return sparkPostMsg(roomid, '_Tips_ : ' + random.choice( app.config['SPARK_MSG_TIPS'] ))
# Record the message
else:
wEvent('sparkMsg', roomid, msgtxt, msg.get('personEmail'), '1', msg)
return 'OK'
def delete_item(item, item_id=None):
if item not in ('customer', 'user'):
return jsonify({
'result': False,
'Description': 'Not available in this item'
})
try:
fields = get_fields(item)
sql = "UPDATE {} SET grp = 'deleted' WHERE {} = '{}';".format(
item, fields[0], item_id)
exeJson(sql, fields)
wEvent('/api/v1.0/{}/{}'.format(item, item_id), 'api', 'DELETE', 'OK')
return jsonify({'result': True})
except Exception as e:
wEvent('/api/v1.0/{}/{}'.format(item, item_id), 'api', 'DELETE', 'KO')
abort(400)
def update_item(item, item_id):
try:
fields = get_fields(item)
if not request.json and item not in request.json:
abort(400)
sql = 'UPDATE {} SET '.format(item)
for field in request.json[item]:
sql += "{} = '{}', ".format(field, request.json[item][field])
sql = sql[:-2] + " WHERE {} = '{}';".format(fields[0], item_id)
exeReq(sql)
wEvent('/api/v1.0/{}/{}'.format(item, item_id), 'api', 'PUT', 'OK')
return jsonify({item: task[0]})
except Exception as e:
wEvent('/api/v1.0/{}/{}'.format(item, item_id), 'api', 'PUT', 'KO')
abort(400)
def newCustomerSub():
try:
sql = "INSERT INTO user SET login = '******'login'] + "', "
sql += " firstname = '" + request.form['firstname'] + "', lastname = '" + request.form['lastname'] + "', "
sql += " email = '" + request.form['email'] + "', address = '" +request.form['address'] + "', "
sql += " admin = '0', grp = 'customer', "
sql += " password = '******'password'] + "', enterprise = '" + request.form['enterprise'] + "', "
sql += " mobile = '" + request.form['mobile'] + "' "
sql += "ON DUPLICATE KEY UPDATE "
sql += " firstname = '" + request.form['firstname'] + "', lastname = '" + request.form['lastname'] + "', "
sql += " email = '" + request.form['email'] + "', address = '" +request.form['address'] + "', "
sql += " admin = '0', grp = 'customer', "
sql += " password = '******'password'] + "', enterprise = '" + request.form['enterprise'] + "', "
sql += " mobile = '" + request.form['mobile'] + "';"
exeReq(sql)
wEvent('/html/v1.0/customer/save','exeReq','Save','OK')
return 'Save OK'
except Exception as e:
wEvent('/html/v1.0/customer/save','exeReq','Save','KO')
return 'Save error'
def close():
if not request.form['sid']:
wEvent('close', session['uid'], "No space ID provided", 'KO')
return 'No space ID provided'
session['sname'] = request.form['sname']
session['sid'] = request.form['sid']
try:
# Delete webhook
webhook = exeReq(
"SELECT name FROM spaces WHERE uid = '1' AND sid = '" +
session['sid'] + "';")
web = pyCiscoSpark.del_webhook(api.config['ACCESS_TOKEN'],
webhook[0][0])
# Delete room
pyCiscoSpark.del_room(api.config['ACCESS_TOKEN'], session['sid'])
exeReq("UPDATE spaces SET status = 'close' WHERE sid = '" +
session['sid'] + "';")
# Log
sEvent('closure')
wEvent('close', session['uid'], "Space closed, id: " + session['sid'],
'OK')
return render_template('new.html')
except Exception as e:
wEvent('close', session['uid'],
str("Issue to close the space, id " + session['sid']), 'KO')
return 'Issue during space closure'
def get_items(item, item_id=None):
try:
fields = get_fields(item)
where = 1
logpath = ''
# /api/v1.0/<item>s ==> with where close coming from JSON
if request.json and item in request.json:
logpath = '/api/v1.0/{}s'.format(item)
where = ''
for field in request.json[item]:
where += "{} = '{}' AND ".format(field,
request.json[item][field])
where = sql[:-5]
# '/api/v1.0/<item>/<int:item_id>'
if item_id not in (None, ''):
logpath = '/api/v1.0/{}/{}'.format(item, item_id)
where = "{} = '{}'".format(fields[0], item_id)
order = 'ASC'
if 'order' in request.args:
order = request.args['order']
limit = 10
if 'limit' in request.args:
limit = request.args['limit']
sql = "SELECT {} FROM {} WHERE {} ORDER BY '{}' LIMIT {};".format(
','.join(fields), item, where, order, limit)
res = exeJson(sql, fields)
wEvent(logpath, 'api', 'GET', 'OK')
return jsonify({item: res})
except Exception as e:
wEvent(logpath, 'api', 'GET', 'KO')
abort(400)
def listTracking():
try:
sql_cont = "FROM tracking t, user u, device d "
sql_cont += "WHERE t.uid = u.uid AND t.did = d.did AND u.grp != 'deleted' AND d.status != 'deleted' "
# Pagination
search = False
q = request.args.get('q')
if q:
search = True
page = request.args.get(get_page_parameter(), type=int, default=1)
per_page = 20
startat = page * per_page
if startat <= per_page:
startat = 0
count = exeReq("SELECT count(*) {}".format(sql_cont))
count = re.sub("[^0-9]", "", "{}".format(count))
pagination = Pagination(page=page,
total=int(count),
search=search,
record_name='list',
css_framework='foundation',
per_page=per_page)
# Get data
sql = "SELECT t.tid, u.login, d.name, t.timestamp, data "
sql += "{} ORDER BY t.tid DESC LIMIT {}, {};".format(
sql_cont, startat, per_page)
list = exeReq(sql)
wEvent('/html/v1.0/tracking/list', 'exeReq', 'Get list', 'OK')
return render_template('listTracking.html',
list=list,
maps=getMaps(),
pagination=pagination)
except Exception as e:
wEvent('/html/v1.0/tracking/list', 'exeReq', 'Get list', 'KO')
return 'List error'
def get_fields(ref):
if 'device' in ref:
return ['did', 'uid', 'name', 'description', 'status', 'lastupdate']
elif 'event' in ref:
return ['eid', 'module', 'user', 'timestamp', 'msg', 'status']
elif 'tracking' in ref:
return [
'tid', 'uid', 'did', 'ip', 'gps', 'url', 'website', 'webhook',
'address', 'timestamp', 'humidity', 'luminosity', 'temp_amb',
'temp_sensor', 'data'
]
elif 'user' in ref:
return [
'uid', 'login', 'firstname', 'lastname', 'email', 'address',
'enterprise', 'grp', 'mobile', 'password', 'admin'
]
elif 'iot' in ref:
return [
'tid', 'uid', 'did', 'humidity', 'luminosity', 'temp_amb',
'temp_sensor', 'data'
]
else:
wEvent('400', 'api', ref, 'KO')
abort(400)
def listCustomer():
try:
# Pagination
search = False
q = request.args.get('q')
if q:
search = True
page = request.args.get(get_page_parameter(), type=int, default=1)
per_page = 20
startat = page * per_page
if startat <= per_page:
startat = 0
count = exeReq("SELECT count(*) FROM user WHERE grp = 'customer';")
count = re.sub("[^0-9]", "","{}".format(count))
pagination = Pagination(page=page, total=int(count), search=search, record_name='list', css_framework='foundation', per_page=per_page)
# Get data
list = exeReq("SELECT login, email, grp FROM user WHERE grp = 'customer';")
wEvent('/html/v1.0/customer/list','exeReq','Get list','OK')
return render_template('listCustomer.html', list = list, maps = getMaps(), pagination=pagination)
except Exception as e:
wEvent('/html/v1.0/customer/list','exeReq','Get list','KO')
return 'List error'
def sparkRemovePeople(roomid, mail):
try:
membership = del_people(app.config['SPARK_ACCESS_TOKEN'], roomid)
wEvent('sparkRemovePeople', roomid, "Membership deleted", 'app', '1',
membership)
except Exception as e:
wEvent('sparkRemovePeople', roomid, "Issue during membership delete",
'app', '0', e)
try:
msg = post_markdown(app.config['SPARK_ACCESS_TOKEN'], roomid,
app.config['SPARK_MSG_DESIGN_ADD'])
wEvent('sparkremovePeople', roomid, "Add membership message posted",
'app', '1', msg)
except Exception as e:
wEvent('sparkRemovePeople', roomid,
"Issue during post of delete membership message", 'app', '0', e)
return 'OK'
