def get_usage_count(user):
hours = collections.defaultdict(lambda: [0 for _ in range(7)])
cnx = db.get_connection()
cursor = cnx.cursor()
time = datetime(2015, 8, 1, 9) # 9am aug 1, 2015
now = datetime.now()
step = timedelta(minutes=60)
def in_lab(time):
start, end = time, time + step
query = """
SELECT COUNT(*) FROM `staff_session_duration_public`
WHERE `user` = %s AND (
`start` BETWEEN %s AND %s OR
`end` BETWEEN %s AND %s OR
%s BETWEEN `start` AND `end` OR
%s BETWEEN `start` AND `end`)"""
cursor.execute(query, (user, start, end, start, end, start, end))
return cursor.fetchone()[0] > 0
while time < now:
if in_lab(time):
hours[time.hour][time.weekday()] += 1
time += step
return hours
def get_usage_count(user):
hours = collections.defaultdict(lambda: [0 for _ in range(7)])
cnx = db.get_connection()
cursor = cnx.cursor()
time = datetime(2016, 8, 21, 0)
now = datetime(2017, 1, 1, 0)
step = timedelta(minutes=60)
def in_lab(time):
start, end = time, time + step
query = """
SELECT COUNT(*) FROM `staff_session_duration_public`
WHERE `user` = %s AND (
`start` BETWEEN %s AND %s OR
`end` BETWEEN %s AND %s OR
%s BETWEEN `start` AND `end` OR
%s BETWEEN `start` AND `end`)"""
cursor.execute(query, (user, start, end, start, end, start, end))
return cursor.fetchone()[0] > 0
while time < now:
if in_lab(time):
hours[time.hour][time.weekday()] += 1
time += step
return hours
def update_staff():
staff = list_staff()
cnx = db.get_connection()
cursor = cnx.cursor()
cursor.execute('DELETE FROM `staff`')
for user in staff:
cursor.execute('INSERT INTO `staff` (`user`) VALUES (%s)', (user,))
cnx.commit()
def close_session(host):
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
UPDATE `session` SET `end` = NOW(), `last_update` = NOW()
WHERE `host` = %s AND `end` IS NULL"""
cursor.execute(query, (host,))
cnx.commit()
def update_staff():
staff = list(list_staff()) + ['pubstaff']
cnx = db.get_connection()
cursor = cnx.cursor()
cursor.execute('DELETE FROM `staff`')
for user in staff:
cursor.execute('INSERT INTO `staff` (`user`) VALUES (%s)', (user, ))
cnx.commit()
def close_session(host):
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
UPDATE `session` SET `end` = NOW(), `last_update` = NOW()
WHERE `host` = %s AND `end` IS NULL"""
cursor.execute(query, (host, ))
cnx.commit()
def get_current_users(): cnx = db.get_connection() cursor = cnx.cursor() query = """ SELECT `user`, `host`, `start` FROM `session` WHERE `end` IS NULL ORDER BY `host`""" cursor.execute(query) return [user for user in cursor]
def close_old_sessions():
"""Closes sessions which we haven't received an update for in a while.
Usually this means the host is off, so we want to end the session."""
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
UPDATE `session` SET `end` = NOW(), `last_update` = NOW()
WHERE `end` IS NULL AND
`last_update` < ADDDATE(NOW(), INTERVAL -{} MINUTE)""".format(int(settings.HOST_TIMEOUT))
cursor.execute(query)
cnx.commit()
def session_exists(host, user):
"""Returns whether an open session already exists for a given host and user
pair."""
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
SELECT count(*) FROM `session`
WHERE `host` = %s AND `user` = %s AND `end` IS NULL"""
cursor.execute(query, (host, user))
return cursor.fetchone()[0] > 0
def session_exists(host, user):
"""Returns whether an open session already exists for a given host and user
pair."""
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
SELECT count(*) FROM `session`
WHERE `host` = %s AND `user` = %s AND `end` IS NULL"""
cursor.execute(query, (host, user))
return cursor.fetchone()[0] > 0
def new_session(host, user):
if session_exists(host, user):
update_session(host, user)
return
close_session(host) # close old sessions
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
INSERT INTO `session` (`host`, `user`, `start`, `last_update`)
VALUES (%s, %s, NOW(), NOW())"""
cursor.execute(query, (host, user))
cnx.commit()
def close_old_sessions():
"""Closes sessions which we haven't received an update for in a while.
Usually this means the host is off, so we want to end the session."""
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
UPDATE `session` SET `end` = NOW(), `last_update` = NOW()
WHERE `end` IS NULL AND
`last_update` < ADDDATE(NOW(), INTERVAL -{} MINUTE)""".format(
int(settings.HOST_TIMEOUT))
cursor.execute(query)
cnx.commit()
def new_session(host, user):
if session_exists(host, user):
update_session(host, user)
return
close_session(host) # close old sessions
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
INSERT INTO `session` (`host`, `user`, `start`, `last_update`)
VALUES (%s, %s, NOW(), NOW())"""
cursor.execute(query, (host, user))
cnx.commit()
def get_top_users(num, start, end):
cnx = db.get_connection()
cursor = cnx.cursor()
query = """
SELECT `user`, SUM(TIME_TO_SEC(TIMEDIFF(`end`, `start`))) AS `duration` FROM `session`
WHERE (
`end` IS NOT NULL AND
(`start` BETWEEN %s AND %s OR `end` BETWEEN %s AND %s))
GROUP BY `user`
ORDER BY `duration` DESC
LIMIT {}""".format(int(num))
cursor.execute(query, (start, end, start, end))
return [user for user in cursor]
def get_utilization(host, start, end): """Return a UtilizationProfile for the given host between datetime objects start and end.""" cnx = db.get_connection() cursor = cnx.cursor() query = """ SELECT `start`, `end` FROM `session` WHERE `host` = %s AND ( `start` BETWEEN %s AND %s OR `end` BETWEEN %s AND %s OR %s BETWEEN `start` AND `end` OR %s BETWEEN `start` AND `end` OR `start` <= %s AND `end` IS NULL ) ORDER BY `start` ASC""" cursor.execute(query, (host, start, end, start, end, start, end, start)) return UtilizationProfile(start, end, [session for session in cursor])
#!/usr/bin/env python3
# initializes the database
import labstats.db as db
cnx = db.get_connection()
cursor = cnx.cursor()
print('Creating session table...')
query = """
CREATE TABLE `session` (
`id` int NOT NULL AUTO_INCREMENT,
`host` varchar(255) NOT NULL,
`user` varchar(16) NOT NULL,
`start` datetime NOT NULL,
`end` datetime DEFAULT NULL,
`last_update` datetime,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `staff` (
`user` varchar(16) NOT NULL,
PRIMARY KEY (`user`)
) ENGINE=InnoDB;
CREATE TABLE `printer_pages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`printer` varchar(255) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`)
#!/usr/bin/env python3
# initializes the database
import labstats.db as db
cnx = db.get_connection()
cursor = cnx.cursor()
print('Creating session table...')
query = """
CREATE TABLE `session` (
`id` int NOT NULL AUTO_INCREMENT,
`host` varchar(255) NOT NULL,
`user` varchar(8) NOT NULL,
`start` datetime NOT NULL,
`end` datetime DEFAULT NULL,
`last_update` datetime,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `staff` (
`user` varchar(8) NOT NULL,
PRIMARY KEY (`user`)
) ENGINE=InnoDB;
CREATE VIEW `session_duration` AS
SELECT *, timediff(`end`, `start`) AS `duration` FROM `session`;
CREATE VIEW `session_duration_public` AS
SELECT `id`, `host`, `start`, `end`, `duration` FROM `session_duration`;
