def get_ap_history(self, apID): db = Database(self.filePath) history = db.get_data("select clients, timestamp from wlc_ap_clients where ap_key=? order by timestamp asc",(apID,)) data = [] for x in xrange(0,len(history)): node = {} node['val'] = history[x][0] time_struct = time.gmtime(history[x][1]) node['timestamp'] = time.strftime('%Y-%m-%d %H:%M:%S', time_struct) data.append(node) return data
def get_group_history(self, groupID): db = Database(self.filePath) history = db.get_data("select SUM(clients), timestamp from wlc_ap_clients where ap_key in (select ap_key from wlc_ap_group_binding where group_id=?) group by timestamp order by timestamp asc",(groupID,)) data = [] for x in xrange(0,len(history)): node = {} node['val'] = history[x][0] time_struct = time.gmtime(history[x][1]) node['timestamp'] = time.strftime('%Y-%m-%d %H:%M:%S', time_struct) data.append(node) return data
def get_current_load(self): db = Database(self.filePath) APs = db.get_data_raw("SELECT * FROM wlc_aps") data = [] for ap in APs: node = {} node['key'] = ap[0] node['name'] = ap[1] node['location'] = ap[2] node['cnt'] = int(db.get_data_single("SELECT clients from wlc_ap_clients WHERE ap_key=? ORDER BY timestamp DESC",(str(ap[0]),))) data.append(node) data = sorted(data, key=lambda k: k['cnt'], reverse = True) return data
def get_ap_history_span(self, apID, start, stop): db = Database(self.filePath) start = calendar.timegm(time.strptime(start,"%Y-%m-%d %H:%M:%S")) stop = calendar.timegm(time.strptime(stop,"%Y-%m-%d %H:%M:%S")) history = db.get_data("select clients, timestamp from wlc_ap_clients where ap_key =? AND (timestamp >= ? AND timestamp <= ?) order by timestamp asc",(apID,start,stop,)) data = [] for x in xrange(0,len(history)): node = {} node['val'] = history[x][0] time_struct = time.gmtime(history[x][1]) node['timestamp'] = time.strftime('%Y-%m-%d %H:%M:%S', time_struct) data.append(node) return data
def set(self, key, value): sql = Database(self.filePath) if sql.is_present("SELECT * FROM wlc_settings WHERE key=?",(key,)): sql.write_data("UPDATE wlc_settings SET value=? WHERE key=?",(value,key,)) else: sql.write_data("INSERT INTO wlc_settings (key,value) VALUES (?,?)",(key,value,)) sql.commit()
def save_data(self, ap_data): sql = Database(self.filePath) paramList = [] timestamp = time.time() for ap in ap_data: params = (str(ap['key']),) query = "SELECT * FROM wlc_aps WHERE ap_key=?" apPresent = sql.is_present(query,params) if apPresent: params = (str(ap['name']),str(ap['location']),str(ap['key'])) query = "UPDATE wlc_aps SET ap_name=?, ap_location=? WHERE ap_key=?" sql.write_data(query, params) else: params = (str(ap['key']),str(ap['name']),str(ap['location'])) query = "Insert INTO wlc_aps (ap_key,ap_name,ap_location) VALUES (?,?,?)" sql.write_data(query,params) params = (str(ap['key']),str(timestamp),str(ap['clients']),) paramList.append(params) query = "INSERT INTO wlc_ap_clients (ap_key, timestamp , clients) VALUES (?,?,?)" sql.write_data_dump(query,paramList) sql.commit()
def get_groups(self): db = Database(self.filePath) data = [] groups = db.get_data_raw("SELECT * FROM wlc_ap_groups") for group in groups: node = {} node['id'] = group[0] node['name'] = group[1] node['lat'] = group[2] node['lng'] = group[3] node['aps'] = [] node['cnt'] = 0 bindings = db.get_data("SELECT * FROM wlc_ap_group_binding WHERE group_id=?",(node['id'],)) for binding in bindings: apNode = {} apNode['key'] = binding[2] adDetails = db.get_data("SELECT * FROM wlc_aps WHERE ap_key=?",(apNode['key'],)) apNode["name"] = adDetails[0][1] node['aps'].append(apNode) node['cnt'] += int(db.get_data_single("SELECT clients from wlc_ap_clients WHERE ap_key=? ORDER BY timestamp DESC",(str(apNode['key']),))) data.append(node) data = sorted(data, key=lambda k: k['cnt'], reverse = True) return data
def save_groups(self,groups): db = Database(self.filePath) for group in groups: groupID = group['id'] if group['id'] is not None: #update db.write_data("UPDATE wlc_ap_groups SET group_name=?,ap_lat=?,ap_lng=? WHERE id=?",(group['name'],group['lat'],group['lng'],group['id'],)) else: #insert db.write_data("INSERT INTO wlc_ap_groups (group_name, ap_lat, ap_lng) VALUES (?,?,?)",(group['name'],group['lat'],group['lng'],)) groupID = db.get_data_single_raw("SELECT max(id) from wlc_ap_groups") db.write_data("DELETE FROM wlc_ap_group_binding WHERE group_id=?",(groupID,)) APs = group['aps'].split(":") for ap in APs: if ap != "": db.write_data("INSERT INTO wlc_ap_group_binding (group_id,ap_key) VALUES (?,?)",(groupID,ap,)) db.commit()
def get_updateStamp(self): sql = Database(self.filePath) query = "SELECT value from wlc_settings WHERE key='lastUpdate'" return sql.get_data_single(query)
def check_db(self): #init DB sql = Database(self.filePath) query = "CREATE TABLE if not exists wlc_aps (ap_key TEXT, ap_name TEXT, ap_location TEXT)" sql.write_data_raw(query) query = "CREATE TABLE if not exists wlc_settings (key TEXT PRIMARY KEY, value TEXT)" sql.write_data_raw(query) query = "CREATE TABLE if not exists wlc_ap_groups (id INTEGER PRIMARY KEY, group_name TEXT, ap_lat REAL, ap_lng REAL)" sql.write_data_raw(query) query = "CREATE TABLE if not exists wlc_ap_group_binding (id INTEGER PRIMARY KEY, group_id, ap_key TEXT)" sql.write_data_raw(query) query = "CREATE TABLE if not exists wlc_ap_clients (id INTEGER PRIMARY KEY, ap_key text,timestamp INTEGER, clients INTEGER)" sql.write_data_raw(query) query = "CREATE INDEX if not exists timestampSort on wlc_ap_clients (timestamp DESC)" sql.write_data_raw(query) sql.commit()
def set_updateStamp(self,timestamp): sql = Database(self.filePath) params = (timestamp,) query = "UPDATE wlc_settings SET value=? WHERE key='lastUpdate'" return sql.write_data(query,params)
def get_group_details(self, groupID): db = Database(self.filePath) data = db.get_data("SELECT * FROM wlc_ap_groups WHERE id=?",(groupID,)) return data
def get_ap_details(self, apID): db = Database(self.filePath) data = db.get_data("SELECT * FROM wlc_aps WHERE ap_key=?",(apID,)) return data
def delete_group(self,groupID): db = Database(self.filePath) db.write_data("DELETE FROM wlc_ap_group_binding WHERE group_id=?",(groupID,)) db.write_data("DELETE FROM wlc_ap_groups WHERE id=?",(groupID,)) db.commit()
def get(self, key): sql = Database(self.filePath) return sql.get_data_single("SELECT value FROM wlc_settings WHERE key=?",(key,))