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 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 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`;