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'