def get_callers(self, params): params.setdefault('offset', (params.page - 1) * params.per_page) sql = "SELECT caller,count(*) FROM sessions" + where(params) sql += ' group by caller ORDER BY ' + params.get( 'sort') + ' ' + params.get( 'order') + ' LIMIT %(per_page)s OFFSET %(offset)s ' sql2 = "SELECT count(*) FROM ( SELECT DISTINCT caller FROM sessions" + \ where(params) + " ) as t" self.total_callers = select(sql2, params)[0]['count'] return select(sql, params)
def get_nodes(self, params): params.setdefault('offset', (params.page - 1) * params.per_page) sql = "SELECT node,count(*) from sessions_history h inner join sessions s on h.session = s.id" + \ where(params, 's') sql += " AND node <> '' group by node ORDER BY " + params.get( 'sort') + ' ' + params.get( 'order') + ' LIMIT %(per_page)s OFFSET %(offset)s ' sql2 = "SELECT count(*) FROM (SELECT DISTINCT node FROM sessions_history h inner join sessions s on h.session = s.id" + \ where(params, 's') sql2 += " AND node <> '' ) as t " self.total_nodes = select(sql2, params)[0]['count'] return select(sql, params)
def delete_node_label(self, params): print(params) sql = "update nodes set label='' where service = %(service)s and name = %(name)s" params.update({"service": self.service}) db.execute(sql, params) sql = "SELECT name,label FROM nodes where service = %(service)s and name = %(name)s" return select(sql, params)
def __init__(self, params): params.setdefault('offset', (params.page - 1) * params.per_page) sql = "SELECT * FROM sessions" + where(params) sql += ' ORDER BY ' + params.get('sort') + ' ' + params.get( 'order') + ' LIMIT %(per_page)s OFFSET %(offset)s ' sql2 = "SELECT count(*) FROM sessions" + where(params) self.total = select(sql2, params)[0]['count'] self.data = pd.read_sql(sql, db, params=params)
def login(self, user): if user.username and user.password: p = {'login': user.username} res = select( 'select login,password from users where login=%(login)s', p) if(len(res)): if(pwd_hash.verify(user.password, res[0].get("password"))): return {'token': self.encode({"username": user.username})} return None
def loadPermissions(): global permissions permissions = select( ' SELECT login,s.id as server,role,admin FROM (SELECT * FROM permissions p RIGHT JOIN users u ON u.login=p.user) AS u LEFT JOIN servers s ON u.service=s.service')
def get(self): sql = "SELECT name,label FROM nodes where service = %(service)s ORDER BY name" return select(sql, {"service": self.service})
def get_calls_stat(self, params): sql = "select * from calls_stat(%(fromDate)s,%(toDate)s,%(server)s);" return select(sql, params)
def get(self): print(self.current_user) return select( "SELECT id,name,role FROM services s RIGHT JOIN permissions p ON s.id = p.service WHERE p.user=%(user)s", {"user": self.current_user})
def get(self, p): return select("SELECT id,name FROM servers WHERE service=%(service)s", p)