def show_room(roomid=None):
user_uuid = uuid_generate_and_set()
if not roomid:
roomid = request.forms.get('roomid')
user_uuid = uuid_generate_and_set()
result = db_exec_sql('select * from rooms where accesskey = ?', (roomid,))
if result:
ttl=result[0][3]
tdate=time.mktime(time.strptime(ttl,'%Y-%m-%d %H:%M:%S'))
cdate=time.time()
#CREATE TABLE votes (id integer primary key autoincrement not null, voter_uuid text not null, room_id integer not null, vote_id integer not null, value integer not null, date datetime not null default current_timestamp);
votes = db_exec_sql('select id,voter_uuid,room_id,vote_id,value,date from votes where room_id = ? and vote_id = ? group by voter_uuid', (roomid,result[0][5],))
votes1 = []
for i in votes:
#find names
name = db_exec_sql('select name from names where uuid = ?', (i[1],))
if name:
i=[i[0],name[0][0],i[2],i[3],i[4],i[5]]
votes1.append(i)
pass
bump = (str(user_uuid) == str(result[0][1]))
return dict(ttl=str(tdate-cdate+86400),roomname=result[0][4],roomid=roomid,vote_id=result[0][5],votes=votes1,bump=bump, vote_decode=vote_value)
else:
redirect("/poker/")
def createroom():
accesskey=gpw.GPW(10).password
user_uuid= uuid_generate_and_set()
name= request.forms.getunicode('roomname')
t=(str(user_uuid),accesskey,name,)
db_exec_sql('insert into rooms (creator_uuid, accesskey,name) values ( ?, ?, ?)', t)
redirect("/poker/rooms/")
def accept_vote(roomid,vote):
user_uuid = uuid_generate_and_set()
if int(vote) in range(0,len(vote_value)):
result = db_exec_sql('select * from rooms where accesskey = ?', (roomid,))
if result:
db_exec_sql('insert or replace into votes (voter_uuid, room_id, vote_id, value) values (?, ?, ?, ?)', (str(user_uuid), roomid, result[0][5],vote))
redirect("/poker/room/"+roomid)
def set_name():
user_uuid = uuid_generate_and_set()
name = request.forms.getunicode('name')
db_exec_sql('insert or replace into names (uuid, name) values (?, ?)', (
str(user_uuid),
name,
))
redirect("/poker/")
def putansible(hostname,ok,change,unreachable,failed):
#CREATE TABLE ansible (id integer primary key autoincrement not null, hostname text, time datetime not null, ok integer not null, change integer not null, unreachable integer not null, failed integer not null);
res = db_exec_sql("select id from ansible where hostname = ?", (hostname,))
if len(res) == 0:
t = (hostname, ok, change, unreachable, failed, )
result = db_exec_sql("insert into ansible ( hostname, ok, change, unreachable, failed, time ) values ( ?, ?, ?, ?, ?, (DATETIME('now')))", t)
else:
t = (ok, change, unreachable, failed, hostname)
result = db_exec_sql("update ansible set ok = ?, change= ?, unreachable = ?, failed = ?, time = (DATETIME('now')) where hostname = ?", t)
def bump_room(roomid):
result = db_exec_sql('select vote_id from rooms where accesskey = ?',
(roomid, ))
vote_id = result[0][0] + 1
db_exec_sql('update rooms set vote_id=? where accesskey = ?', (
vote_id,
roomid,
))
redirect("/poker/room/" + roomid)
def accept_vote(roomid, vote):
user_uuid = uuid_generate_and_set()
if int(vote) in range(0, len(vote_value)):
result = db_exec_sql('select * from rooms where accesskey = ?',
(roomid, ))
if result:
db_exec_sql(
'insert or replace into votes (voter_uuid, room_id, vote_id, value) values (?, ?, ?, ?)',
(str(user_uuid), roomid, result[0][5], vote))
redirect("/poker/room/" + roomid)
def createroom():
accesskey = gpw.GPW(10).password
user_uuid = uuid_generate_and_set()
name = request.forms.getunicode('roomname')
t = (
str(user_uuid),
accesskey,
name,
)
db_exec_sql(
'insert into rooms (creator_uuid, accesskey,name) values ( ?, ?, ?)',
t)
redirect("/poker/rooms/")
def resetstats():
result = db_exec_sql("select count() from queue")
count = result[0][0]
result = db_exec_sql("select count() from queue where done= ?", ( 'false',))
requests = result[0][0]
result = db_exec_sql("select date from queue where done = ? order by date desc limit 1", ( 'true', ))
try:
date=result[0][0]
except:
date=None
frequency = db_exec_sql("select username,count(username) from queue group by username order by count(username) desc limit 10")
topresets = db_exec_sql("select resetedby,count(resetedby) from queue group by resetedby order by count(resetedby) desc limit 10")
return dict(count = count, requests = requests, date = date, frequency = frequency, topresets = topresets)
def update_user():
user = os.environ["REMOTE_USER"].split('@')[0]
fio = request.forms.get('fio',None)
studnum = request.forms.get('studnum',None)
photo = request.forms.get('photo',None)
if fio:
result = db_exec_sql("update users set fio= ? where username=?",(fio.decode('utf-8'), user,))
if studnum:
result = db_exec_sql("update users set studnum= ? where username=?",(studnum.decode('utf-8'), user,))
if photo:
dphoto = photo.replace("data:image/png;base64,","")
result = db_exec_sql("update users set photo = ? where username=?",(photo.decode('utf-8'), user,))
return dict(username = user, fio = fio, studnum = studnum, photo = dphoto)
def mainpage():
user_uuid = uuid_generate_and_set()
result = db_exec_sql('select name from names where uuid = ?',
(str(user_uuid), ))
if result:
user_uuid = result[0][0]
return dict(user_uuid=user_uuid)
def showgroup_ui(form):
if "group" in form:
group=form["group"].value
try:
grp.getgrnam(group)
except:
print_ui(errorpage % ("No such group 1"))
return
grpvalue = grp.getgrnam(group)
if ((grpvalue[2] < 1000) or (grpvalue[2] > 64000)):
print_ui(errorpage % ("No such group 2"))
return
table= u"<tr><td>Группа</td><td>Пользователи</td><td>Комментарий к группе</td></tr>"
table += u"<tr><td>%s</td>" % (group, )
table += u"<td>"
grptable = u"<table width=\"100%\"><tr>"
k=0
for p in grpvalue[3]:
grptable += "<td width=12.5%><a href=\""+userbase + p +"\">" + unicode(p) + "</a></td>"
if k%8 == 7:
grptable += "</tr><tr>"
k= k+1
grptable += "</tr></table>"
table += grptable
comment = db_exec_sql('select comment from comments where groupname = ?', (group,))
if comment == []:
comment = ""
else:
comment = comment[0][0]
table += u"</td><td>%s</td></tr>" % (comment, )
print_ui(showgrouppage % (group,table,))
else:
print_ui(errorpage % ("No group specified",))
def findusers(key):
userlist = []
key = key.decode('utf-8')
key = '%' + key + '%'
t = ( key, key, key )
result=db_exec_sql("select username,fio,studnum from users where (username like ?) or (fio like ?) or (studnum like ?)", t)
return result
def getansible(hostname):
#CREATE TABLE ansible (id integer primary key autoincrement not null, hostname text, time datetime not null, ok integer not null, change integer not null, unreachable integer not null, failed integer not null);
result = db_exec_sql("select (julianday('now') - julianday(time)), ok, change, unreachable, failed from ansible where (hostname = ?) ", (hostname,))
if len(result)==0:
return None
else:
return result[0]
def getpopularity(period, ip):
""" Популярность компьютера и проведённое на нём время
period - в днях
возвращает массив из пар время в минутах, пользователь
"""
result = db_exec_sql("select users,count()*5 from users where (ip = ? ) and (julianday('now') - julianday(time)) <= ? group by users", (ip, period))
return result
def overquota():
result = db_exec_sql("select username from quota where usedspace > softlimit and softlimit > 0")
quotas = []
for i in result:
userinfo = getuser(i[0])
dictionary = dict(username = i[0], quota = userinfo["quota"], useddisk = userinfo["useddiskspace"])
quotas.append(dictionary)
return dict(quotas = quotas)
def resetpassword(username):
user={}
password=""
students_gid=grp.getgrnam("students")[2]
try:
passwd = pwd.getpwnam(username)
except:
return ""
#check that user is a student and generate password and qrcode from it
if passwd[3]==students_gid:
password=gpw.GPW(10).password
currentuser = os.environ["REMOTE_USER"]
t = ( username, password, currentuser )
db_exec_sql('insert into queue (username, password, resetedby) values (?, ?, ?)', t)
else:
password = ""
return password
def main():
#result = db_exec_sql("select id, ip, hostname, lastupdate, (julianday('now')-julianday(lastupdate))*24*60 from machines order by hostname")
online = db_exec_sql(
"select count() from machines where (julianday('now')-julianday(lastupdate))*24*60 < 10"
)
usersloggedin = db_exec_sql(
"select ip, users from users where (julianday('now')-julianday(time))*24*60 < 10 group by ip,users"
)
userslog = dict()
for i in usersloggedin:
if i[0] in userslog:
temp = userslog[i[0]]
userslog[i[0]] = temp + " " + i[1]
else:
userslog[i[0]] = i[1]
onlinecount = online[0][0]
displaydata = {}
for i in ['a425', 'a437', 'a439', 'a441', 'a443', 'a445', 'p4n', 'misc']:
displaydata[i] = {}
displaydata[i]['name'] = u"Аудитория " + i
displaydata[i]['link'] = i
displaydata[i]['online'] = 0
room = (i, )
if i == 'misc':
displaydata[i]['name'] = u"Прочее"
result = db_exec_sql(
"select id, ip, hostname, lastupdate, (julianday('now')-julianday(lastupdate))*24*60 from machines where room is NULL order by hostname"
)
else:
result = db_exec_sql(
"select id, ip, hostname, lastupdate, (julianday('now')-julianday(lastupdate))*24*60 from machines where room = ? order by hostname",
room)
temp = []
for record in result:
ansible = rrd_ansible.latest(str(record[2]))
scratch = rrd_scratch.latest(str(record[2]))
temptuple = record + (usage.getpowered(
30, record[1]), usage.getusage(
30, record[1]), ansible, scratch, 'NaN')
temp.append(temptuple)
displaydata[i]['values'] = temp
displaydata[i]['total'] = len(result)
return dict(data=displaydata,
date=datetime.datetime.now(),
online=onlinecount,
userslog=userslog)
def update_session(hostname, uptime):
result = db_exec_sql("select host_sessions where hostname = ? and endtime = NULL", (hostname,))
if len(result) == 0:
# No records - open one
#db_exec_sql("insert into host_sessions
pass
else:
pass
pass
def getuser(username):
user = {}
try:
passwd = pwd.getpwnam(username)
except:
return user
t = ( username, )
result=db_exec_sql('select fio,studnum from users where username = ?', t)[0]
quotaresult=db_exec_sql('select usedspace,softlimit from quota where username = ?',t)[0]
user["fio"] = result[0]
user["studnumber"] = result[1]
user["quota"] = int(quotaresult[1])
user["useddiskspace"] = int(quotaresult[0])
user["username"] = passwd[0]
user["groups"] = []
user["groups"].append(grp.getgrgid(passwd[3])[0])
for i in grp.getgrall():
if user["username"] in i[3]:
user["groups"].append(i[0])
return user
def machinestats(grp):
if grp in ['a425', 'a437', 'a439', 'a441', 'a443', 'a445', 'p4n', 'misc']:
if grp == 'misc':
result = db_exec_sql(
"select hostname,ip from machines where room is NULL order by hostname"
)
else:
result = db_exec_sql(
"select hostname,ip from machines where room = ? order by hostname",
(grp, ))
tabs = []
recipes = {}
popularity = {}
for i in result:
popularity[i[0]] = {}
for j in [7, 14, 30, 60, 90, 180]:
popularity[i[0]][j] = usage.getpopularity(j, i[1])
for i in result:
if os.path.isdir("/var/www/rrds/" + i[0]):
temp = []
temp.append(i[0])
temp2 = []
for j in ["cpu", "memory", "load", "users", "uptime"]:
recipe_name = i[0] + "_" + j
temp2.append(recipe_name)
recipes[recipe_name] = {}
recipes[recipe_name]["title"] = j + " on " + i[0]
temp.append(temp2)
tabs.append(temp)
tabs2 = json.dumps(tabs)
recipes2 = json.dumps(recipes)
return dict(date=datetime.datetime.now(),
hosts=result,
popularity=popularity,
tabs=tabs2,
recipes=recipes2,
attr=grp,
group=True)
def show_room(roomid=None):
user_uuid = uuid_generate_and_set()
if not roomid:
roomid = request.forms.get('roomid')
user_uuid = uuid_generate_and_set()
result = db_exec_sql('select * from rooms where accesskey = ?', (roomid, ))
if result:
ttl = result[0][3]
tdate = time.mktime(time.strptime(ttl, '%Y-%m-%d %H:%M:%S'))
cdate = time.time()
#CREATE TABLE votes (id integer primary key autoincrement not null, voter_uuid text not null, room_id integer not null, vote_id integer not null, value integer not null, date datetime not null default current_timestamp);
votes = db_exec_sql(
'select id,voter_uuid,room_id,vote_id,value,date from votes where room_id = ? and vote_id = ? group by voter_uuid',
(
roomid,
result[0][5],
))
votes1 = []
for i in votes:
#find names
name = db_exec_sql('select name from names where uuid = ?',
(i[1], ))
if name:
i = [i[0], name[0][0], i[2], i[3], i[4], i[5]]
votes1.append(i)
pass
bump = (str(user_uuid) == str(result[0][1]))
return dict(ttl=str(tdate - cdate + 86400),
roomname=result[0][4],
roomid=roomid,
vote_id=result[0][5],
votes=votes1,
bump=bump,
vote_decode=vote_value)
else:
redirect("/poker/")
def update_user():
user = os.environ["REMOTE_USER"].split('@')[0]
result = db_exec_sql("select fio, studnum, photo from users where username=?",(user,))
fio = ""
studnum = ""
photo = ""
if result:
(fio,studnum, photo ) = result[0]
if fio == None:
fio = u""
if studnum == None:
studnum = u""
if photo == None:
photo = u""
return dict(username = user, fio = fio, studnum = studnum, photo = photo)
def mainpage_ui(form):
table = u"<table border=1><tr><td>Группа</td><td>Пользователи</td><td>Комментарий к группе</td></tr>"
for i in grp.getgrall():
if (i[2] > 1000) and (i[2] <=64000):
table += "<tr><td><a href=./?page=showgroup&group=" + unicode(i[0]) + ">" + unicode(i[0]) + "</a></td>"
k=len(i[3])
table += u"<td>%d</td>" % k
comment = db_exec_sql('select comment from comments where groupname = ?', (i[0],))
if comment == []:
comment = ""
else:
comment = comment[0][0]
table += "<td>%s</td></tr>" % (comment, )
table+="</table>"
print_ui(mainpage % (table,))
pass
def show_userphoto(username):
response.set_header('Content-type', 'image/png')
empty="""
iVBORw0KGgoAAAANSUhEUgAAAGQAAABkAQAAAABYmaj5AAAAAmJLR0QAAd2KE6QAAAAZSURBVDjLY/
iPBD4wjPJGeaO8Ud4oj8Y8AL7rCVzcsTKLAAAAAElFTkSuQmCC
"""
t = ( username, )
photo=db_exec_sql('select photo from users where username = ?', t)[0]
if photo == None:
photo=empty
else:
photo=photo[0]
if photo==None:
photo=empty
image = photo.decode('base64')
return image
def machinestats2(machine, period='w'):
result = db_exec_sql(
"select hostname, ip from machines where hostname like ?", (machine, ))
if len(result) > 0:
ip_addr = result[0][1]
result = result[0][0]
else:
result = None
ip_addr = ""
popularity = {}
for j in [7, 14, 30, 60, 90, 180]:
popularity[j] = usage.getpopularity(j, ip_addr)
return dict(date=datetime.datetime.now(),
machine=result,
popularity=popularity,
attr=machine,
group=False,
period=period)
def show_rooms():
user_uuid = uuid_generate_and_set()
t = (str(user_uuid), )
result = db_exec_sql("select * from rooms where creator_uuid = ? ", t)
return dict(data=result)
def close_session(hostname):
db_exec_sql("update host_sessions set endtime = ? where hostname = ? and endtime = NULL", (time.time(), hostname,))
def show_rooms():
user_uuid = uuid_generate_and_set()
t=(str(user_uuid),)
result = db_exec_sql("select * from rooms where creator_uuid = ? ", t)
return dict(data=result)
import os
import sqlite3
import subprocess
import sys
if "REMOTE_ADDR" in os.environ:
print "Content/type: text/html"
print ""
print "Wrong page"
exit(0)
sys.path.insert(1, os.path.join(sys.path[0], '..'))
from my_db import db_exec_sql
#This is not a CGI
queue = db_exec_sql('select * from queue where done=False order by date asc')
if queue != None:
for i in queue:
commandline=u"""kadmin -p automator/admin -k -t /etc/krb5.keytab -q "change_password -pw %s %s" """ % (i[2], i[1], )
try:
retcode = subprocess.call(commandline, shell=True)
if retcode < 0:
print >>sys.stderr, "Child was terminated by signal", -retcode
else:
if retcode == 0:
db_exec_sql('update queue set done=True where username = %s and password = %s', (i[1],i[2]))
else:
print >>sys.stderr, "Child returned", retcode
except OSError as e:
print >>sys.stderr, "Execution failed:", e
def getpowered(period,ip):
""" Время во включенном состоянии компьютера за определённый период period - в днях"""
result = db_exec_sql("select count() from uptime where (ip = ?) and (julianday('now') - julianday(time)) <= ?",(ip, period))
return result[0][0]*5
def set_name():
user_uuid = uuid_generate_and_set()
name = request.forms.getunicode('name')
db_exec_sql('insert or replace into names (uuid, name) values (?, ?)',(str(user_uuid), name,))
redirect("/poker/")
def acceptdata():
ip_addr = request.environ.get("REMOTE_ADDR")
hostname = request.environ.get("REMOTE_HOST")
if hostname is None:
hostname = socket.gethostbyaddr(ip_addr)[0]
temp = (ip_addr, )
res = db_exec_sql("select id from machines where ip = ?", temp)
if len(res) == 0:
temp = (
ip_addr,
hostname,
)
db_exec_sql(
"insert into machines ( ip, hostname, lastupdate ) values ( ?, ?, (DATETIME('now')))",
temp)
else:
temp = (
hostname,
ip_addr,
)
db_exec_sql(
"update machines set hostname = ?, lastupdate = (DATETIME('now')) where ip = ?",
temp)
# here goes the report
reportedhostname = request.json['hostname']
uptime = request.json['uptime']
users = request.json['users']
cpu = request.json['cpu']
db_exec_sql(
"insert into hostnames (ip, hostname, time) values ( ?, ?, DATETIME('now'))",
(ip_addr, reportedhostname))
db_exec_sql(
"insert into uptime (ip, time, uptime) values ( ?, DATETIME('now'), ?)",
(ip_addr, uptime))
rrd_uptime.insert(hostname, [
uptime,
])
rrd_cpu.insert(hostname, [cpu['load'], cpu['loadavg'], cpu['cores']])
rrd_users.insert(hostname, [
len(set(users)),
])
for i in users:
db_exec_sql(
"insert into users (ip, time, users) values ( ?, DATETIME('now'), ?)",
(ip_addr, i))
return dict()
def bump_room(roomid):
result=db_exec_sql('select vote_id from rooms where accesskey = ?',(roomid,))
vote_id=result[0][0]+1
db_exec_sql('update rooms set vote_id=? where accesskey = ?',(vote_id,roomid,))
redirect("/poker/room/"+roomid)
def show_groups():
grouplist = []
counts = {}
counts['passwd'] = 0
counts['users'] = 0
counts['quota'] = 0
userlist = []
for i in pwd.getpwall():
if (i.pw_uid >=1000) and (i.pw_uid <=64000):
userlist.append(i.pw_name)
counts['passwd'] = len(userlist)
result = db_exec_sql('select count(username) from quota')[0][0]
counts['quota'] = result
result = db_exec_sql('select count(username) from users')[0][0]
counts['users'] = result
#cleanup begin
#remove absent from passwd
for i in db_exec_sql('select username from quota'):
if i[0] not in userlist:
#extra, remove it
#print i[0]
db_exec_sql('delete from quota where username = %s', (i[0],))
for i in db_exec_sql('select username from users'):
if i[0] not in userlist:
#extra, remove it
#print i[0]
db_exec_sql('delete from users where username = %s', (i[0],))
#insert missing
for i in userlist:
result = db_exec_sql('select username from quota where username = %s', (i,))
if len(result) == 0:
db_exec_sql('insert into quota (username, usedspace, softlimit) values ( %s, %s, %s)', (i, 0,0))
for i in userlist:
result = db_exec_sql('select username from users where username = %s', (i,))
if len(result) == 0:
db_exec_sql('insert into users (username, fio, studnum) values ( %s, %s, %s)', (i, '',''))
#cleanup end
for i in grp.getgrall():
if (i[2] > 1000) and (i[2] <=64000):
grouplist.append((i[0],i[3],"",))
return dict(data = grouplist, counts = counts)
def listreset():
data = db_exec_sql('select * from queue where done="false" order by date desc')
return dict(data = data)
def get_sessions(hostname, start, end):
#CREATE TABLE host_sessions (id integer primary key autoincrement not null, hostname text not null, starttime datetime not null, endtime datetime);
result = db_exec_sql("select hostname, starttime, endtime from host_sessions where hostname = ? and starttime > ?", (hostname, start))
return []
def getusage(period,ip):
""" Использование компьютера за определённый период """
#result = db_exec_sql("select count() from users where (ip = ?) and (julianday('now') - julianday(time)) <= ? group by time",(ip, period))
result = db_exec_sql("select count() from users where (ip = ?) and (julianday('now') - julianday(time)) <= ?",(ip, period))
return result[0][0]*5
def mainpage():
user_uuid = uuid_generate_and_set()
result = db_exec_sql('select name from names where uuid = ?', (str(user_uuid),))
if result:
user_uuid = result[0][0]
return dict(user_uuid=user_uuid)