Example #1
0
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/")
Example #2
0
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/")
Example #3
0
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)
Example #4
0
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/")
Example #5
0
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)
Example #6
0
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)
Example #7
0
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)
Example #8
0
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/")
Example #9
0
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)
Example #10
0
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)
Example #11
0
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)
Example #12
0
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",))
Example #13
0
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 
Example #14
0
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]
Example #15
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
Example #16
0
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)
Example #17
0
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
Example #18
0
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
Example #20
0
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
Example #21
0
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)
Example #22
0
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/")
Example #23
0
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)
Example #24
0
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
Example #25
0
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
Example #26
0
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)
Example #27
0
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,))
Example #29
0
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
Example #31
0
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
Example #32
0
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/")
Example #33
0
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()
Example #34
0
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)
Example #35
0
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)
Example #36
0
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 []
Example #38
0
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
Example #39
0
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)