def get_standings(admin=False, count=None):
score = db.func.sum(Challenges.value).label('score')
date = db.func.max(Solves.date).label('date')
scores = db.session.query(Solves.teamid.label('teamid'), score,
date).join(Challenges).group_by(Solves.teamid)
awards = db.session.query(Awards.teamid.label('teamid'), db.func.sum(Awards.value).label('score'), db.func.max(Awards.date).label('date')) \
.group_by(Awards.teamid)
results = union_all(scores, awards).alias('results')
sumscores = db.session.query(results.columns.teamid, db.func.sum(results.columns.score).label('score'), db.func.max(results.columns.date).label('date')) \
.group_by(results.columns.teamid).subquery()
if admin:
standings_query = db.session.query(Teams.id.label('teamid'), Teams.name.label('name'), Teams.banned, sumscores.columns.score) \
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
else:
standings_query = db.session.query(Teams.id.label('teamid'), Teams.name.label('name'), sumscores.columns.score) \
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
db.session.close()
return standings
def scores():
if get_config('view_scoreboard_if_authed') and not authed():
return redirect(url_for('auth.login', next=request.path))
score = db.func.sum(Challenges.value).label('score')
scores = db.session.query(Solves.teamid.label('teamid'), Teams.name.label('name'), score, Solves.date.label('date')) \
.join(Teams) \
.join(Challenges) \
.filter(Teams.banned == None) \
.group_by(Solves.teamid)
awards = db.session.query(Teams.id.label('teamid'), Teams.name.label('name'), db.func.sum(Awards.value).label('score'), Awards.date.label('date'))\
.filter(Teams.id==Awards.teamid)\
.group_by(Teams.id)
results = union_all(scores, awards)
standings = db.session.query(results.columns.teamid, results.columns.name, db.func.sum(results.columns.score).label('score'))\
.group_by(results.columns.teamid)\
.order_by(db.func.sum(results.columns.score).desc(), db.func.max(results.columns.date))\
.all()
db.session.close()
json = {'standings':[]}
for i, x in enumerate(standings):
json['standings'].append({'pos':i+1, 'id':x.teamid, 'team':x.name,'score':int(x.score)})
return jsonify(json)
def scoreboard(cls):
# credit: https://github.com/CTFd/CTFd/blob/master/CTFd/scoreboard.py
uniq = db.session\
.query(Solve.tid.label("tid"), Solve.pid.label("pid"))\
.distinct()\
.subquery()
# flash("uniq: " + str(uniq).replace("\n", ""), "info")
scores = db.session\
.query(
# uniq.columns.tid.label("tid"),
Solve.tid.label("tid"),
db.func.max(Solve.pid).label("pid"),
db.func.sum(Problem.value).label("score"),
db.func.max(Solve.date).label("date"))\
.join(Problem)\
.group_by(Solve.tid)
# flash("scores: " + str(scores).replace("\n", ""), "info")
results = union_all(scores).alias("results")
sumscores = db.session\
.query(results.columns.tid, db.func.sum(results.columns.score).label("score"), db.func.max(results.columns.pid), db.func.max(results.columns.date).label("date"))\
.group_by(results.columns.tid)\
.subquery()
query = db.session\
.query(Team, Team.tid.label("tid"), sumscores.columns.score, sumscores.columns.date)\
.filter(Team.banned == False)\
.join(sumscores, Team.tid == sumscores.columns.tid)\
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
# flash("full query: " + str(query).replace("\n", ""), "info")
return query.all()
def get_team_standings(count=None, admin=False, fields=None):
if fields is None:
fields = []
scores = (db.session.query(
Solves.team_id.label("team_id"),
db.func.sum(Challenges.value).label("score"),
db.func.max(Solves.id).label("id"),
db.func.max(Solves.date).label("date"),
).join(Challenges).filter(Challenges.value != 0).group_by(Solves.team_id))
awards = (db.session.query(
Awards.team_id.label("team_id"),
db.func.sum(Awards.value).label("score"),
db.func.max(Awards.id).label("id"),
db.func.max(Awards.date).label("date"),
).filter(Awards.value != 0).group_by(Awards.team_id))
freeze = get_config("freeze")
if not admin and freeze:
scores = scores.filter(Solves.date < unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < unix_time_to_utc(freeze))
results = union_all(scores, awards).alias("results")
sumscores = (db.session.query(
results.columns.team_id,
db.func.sum(results.columns.score).label("score"),
db.func.max(results.columns.id).label("id"),
db.func.max(results.columns.date).label("date"),
).group_by(results.columns.team_id).subquery())
if admin:
standings_query = (db.session.query(
Teams.id.label("team_id"),
Teams.oauth_id.label("oauth_id"),
Teams.name.label("name"),
Teams.hidden,
Teams.banned,
sumscores.columns.score,
*fields,
).join(sumscores, Teams.id == sumscores.columns.team_id).order_by(
sumscores.columns.score.desc(), sumscores.columns.id))
else:
standings_query = (db.session.query(
Teams.id.label("team_id"),
Teams.oauth_id.label("oauth_id"),
Teams.name.label("name"),
sumscores.columns.score,
*fields,
).join(sumscores, Teams.id == sumscores.columns.team_id).filter(
Teams.banned == False).filter(Teams.hidden == False).order_by(
sumscores.columns.score.desc(), sumscores.columns.id))
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
return standings
def upgrade_severity_levels(session_maker, severity_map):
"""
Updates the potentially changed severities at the reports.
"""
LOG.debug("Upgrading severity levels started...")
with DBSession(session_maker) as session:
try:
# Create a sql query from the severity map.
severity_map_q = union_all(*[
select([cast(bindparam('checker_id' + str(i), str(checker_id))
.label('checker_id'), sqlalchemy.String),
cast(bindparam('severity' + str(i),
Severity._NAMES_TO_VALUES[
severity_map[checker_id]])
.label('severity'), sqlalchemy.Integer)])
for i, checker_id in enumerate(severity_map)]) \
.alias('new_severities')
checker_ids = list(severity_map.keys())
# Get checkers which has been changed.
changed_checker_q = select([Report.checker_id, Report.severity]) \
.group_by(Report.checker_id, Report.severity) \
.where(Report.checker_id.in_(checker_ids)) \
.except_(
session.query(severity_map_q)).alias('changed_severites')
changed_checkers = session.query(changed_checker_q.c.checker_id,
changed_checker_q.c.severity)
# Update severity levels of checkers.
if changed_checkers:
updated_checker_ids = set()
for checker_id, severity_old in changed_checkers:
severity_new = severity_map.get(checker_id, 'UNSPECIFIED')
severity_id = Severity._NAMES_TO_VALUES[severity_new]
LOG.info("Upgrading severity level of '%s' checker from "
"%s to %s",
checker_id,
Severity._VALUES_TO_NAMES[severity_old],
severity_new)
if checker_id in updated_checker_ids:
continue
session.query(Report) \
.filter(Report.checker_id == checker_id) \
.update({Report.severity: severity_id})
updated_checker_ids.add(checker_id)
session.commit()
LOG.debug("Upgrading of severity levels finished...")
except (sqlalchemy.exc.OperationalError,
sqlalchemy.exc.ProgrammingError) as ex:
LOG.error("Failed to upgrade severity levels: %s", str(ex))
def get_standings(admin=False, count=None, contestid=None):
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.date).label('date')).join(Challenges).group_by(
Solves.teamid)
awards = db.session.query(Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.date).label('date')).group_by(
Awards.teamid)
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.date).label('date')).join(Challenges).group_by(
Solves.teamid)
if contestid is not None:
scores = scores.filter(Challenges.contestid == contestid)
freeze = utils.get_config('freeze')
if not admin and freeze:
scores = scores.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.unix_time_to_utc(freeze))
results = union_all(scores, awards).alias('results')
sumscores = db.session.query(
results.columns.teamid,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.date).label('date')).group_by(
results.columns.teamid).subquery()
if admin:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
db.session.close()
return standings
def topteams(count):
if get_config('view_scoreboard_if_authed') and not authed():
return redirect(url_for('auth.login', next=request.path))
try:
count = int(count)
except:
count = 10
if count > 20 or count < 0:
count = 10
json = {'scores':{}}
score = db.func.sum(Challenges.value).label('score')
scores = db.session.query(Solves.teamid.label('teamid'), Teams.name.label('name'), score, Solves.date.label('date')) \
.join(Teams) \
.join(Challenges) \
.filter(Teams.banned == None) \
.group_by(Solves.teamid)
awards = db.session.query(Teams.id.label('teamid'), Teams.name.label('name'),
db.func.sum(Awards.value).label('score'), Awards.date.label('date')) \
.filter(Teams.id == Awards.teamid) \
.group_by(Teams.id)
results = union_all(scores, awards)
standings = db.session.query(results.columns.teamid, results.columns.name,
db.func.sum(results.columns.score).label('score')) \
.group_by(results.columns.teamid) \
.order_by(db.func.sum(results.columns.score).desc(), db.func.max(results.columns.date)) \
.limit(count).all()
for team in standings:
solves = Solves.query.filter_by(teamid=team.teamid).all()
awards = Awards.query.filter_by(teamid=team.teamid).all()
json['scores'][team.name] = []
scores = []
for x in solves:
json['scores'][team.name].append({
'chal': x.chalid,
'team': x.teamid,
'value': x.chal.value,
'time': unix_time(x.date)
})
for award in awards:
json['scores'][team.name].append({
'chal': None,
'team': award.teamid,
'value': award.value,
'time': unix_time(award.date)
})
json['scores'][team.name] = sorted(json['scores'][team.name], key=lambda k: k['time'])
return jsonify(json)
def execute(self, limit, begin=None, end=None, incl=None):
# Compute all the column names
all_tables = set([])
for s in self.selects:
all_tables.add(s.klass.history_table)
all_columns = set([])
for colset in [cols for cols in [table.columns for table in all_tables]]:
all_columns.update(set([col.name for col in colset]))
# Append columns to selects
for s in self.selects:
table_column_names = [col.name for col in s.klass.history_table.columns]
columns = [literal(s.objclass).label('objclass')]
for column in all_columns:
if column in table_column_names:
columns.append(getattr(s.klass.history_table.c, column))
else:
# In mysql, Null UNION Numeric = float
column_type = None
for table in all_tables:
if column in table.c:
column_type = table.c[column].type
break
if isinstance(column_type, Numeric):
columns.append(literal(0).label(column))
else:
columns.append(literal(None).label(column))
# There are 2 types of columns:
# columns from the table we select (these have the 'name' attribute)
# literal columns needed for union with the other selects (these have the 'key' attribute)
columns = sorted(columns, key=lambda x: x.name if hasattr(x, 'name') else x.key)
for column in columns:
s.select.append_column(column)
max_limit = 5000 # TODO: configuration option?
if limit is None or limit > max_limit:
limit = max_limit
timestamp = sql_expression.column('timestamp')
def limit_query_timestamp(query):
if begin is not None:
query = query.where(timestamp >= begin)
if end is not None:
query = query.where(timestamp <= end)
return query
def limit_query(query):
return query.limit(limit).order_by(desc(timestamp))
for s in self.selects:
limit_query(s)
query = union_all(*[db.session.query('*').select_from(limit_query_timestamp(s.select)) for s in self.selects])
result = db.session.execute(limit_query(query)).fetchall()
return [HistorySelect._row_info(row, incl) for row in result]
def union_select(selectComponents, distinct=False, select_type=TRIPLE_SELECT):
"""
Helper function for building union all select statement.
Terms: u - uri refs v - variables b - bnodes l - literal f - formula
Takes a list of:
- table name
- table alias
- table type (literal, type, asserted, quoted)
- where clause string
"""
selects = []
for table, whereClause, tableType in selectComponents:
if select_type == COUNT_SELECT:
selectClause = table.count(whereClause)
elif select_type == CONTEXT_SELECT:
selectClause = expression.select([table.c.context], whereClause)
elif tableType in FULL_TRIPLE_PARTITIONS:
selectClause = table.select(whereClause)
elif tableType == ASSERTED_TYPE_PARTITION:
selectClause = expression.select([
table.c.id.label("id"),
table.c.member.label("subject"),
expression.literal(text_type(RDF.type)).label("predicate"),
table.c.klass.label("object"),
table.c.context.label("context"),
table.c.termComb.label("termcomb"),
expression.literal_column("NULL").label("objlanguage"),
expression.literal_column("NULL").label("objdatatype")
], whereClause)
elif tableType == ASSERTED_NON_TYPE_PARTITION:
selectClause = expression.select([c for c in table.columns] + [
expression.literal_column("NULL").label("objlanguage"),
expression.literal_column("NULL").label("objdatatype")
],
whereClause,
from_obj=[table])
selects.append(selectClause)
order_statement = []
if select_type == TRIPLE_SELECT:
order_statement = [
expression.literal_column("subject"),
expression.literal_column("predicate"),
expression.literal_column("object"),
]
if distinct:
return expression.union(*selects, **{"order_by": order_statement})
else:
return expression.union_all(*selects, **{"order_by": order_statement})
def upgrade_severity_levels(session, severity_map):
"""
Updates the potentially changed severities at the reports.
"""
LOG.debug("Upgrading severity levels started...")
# Create a sql query from the severity map.
severity_map_q = union_all(*[
select([cast(bindparam('checker_id' + str(i), str(checker_id))
.label('checker_id'), sqlalchemy.String),
cast(bindparam('severity' + str(i), Severity._NAMES_TO_VALUES[
severity_map[checker_id]])
.label('severity'), sqlalchemy.Integer)])
for i, checker_id in enumerate(severity_map)]) \
.alias('new_severities')
checker_ids = severity_map.keys()
# Get checkers which has been changed.
changed_checker_q = select([Report.checker_id, Report.severity]) \
.group_by(Report.checker_id, Report.severity) \
.where(Report.checker_id.in_(checker_ids)) \
.except_(session.query(severity_map_q)).alias('changed_severites')
changed_checkers = session.query(changed_checker_q.c.checker_id,
changed_checker_q.c.severity)
# Update severity levels of checkers.
if changed_checkers:
updated_checker_ids = set()
for checker_id, severity_old in changed_checkers:
severity_new = severity_map.get(checker_id, 'UNSPECIFIED')
severity_id = Severity._NAMES_TO_VALUES[severity_new]
LOG.info("Upgrading severity level of '%s' checker from %s to %s",
checker_id,
Severity._VALUES_TO_NAMES[severity_old],
severity_new)
if checker_id in updated_checker_ids:
continue
session.query(Report) \
.filter(Report.checker_id == checker_id) \
.update({Report.severity: severity_id})
updated_checker_ids.add(checker_id)
session.commit()
LOG.debug("Upgrading of severity levels finished...")
def _get_contributors(self, indirect=True, id_only=True):
from .post import Post
from .auth import AgentProfile
from .idea_content_link import Extract
# Get extracts related to the idea
extracts = self.db.query(Extract).join(
Extract.extract_source.of_type(Post)).filter(
Extract.idea_id == self.id).options(
joinedload(Extract.extract_source)).all()
extracts_by_author = defaultdict(list)
for e in extracts:
extracts_by_author[e.extract_source.creator_id].append(e)
author_ids = extracts_by_author.keys()
def priority(author_id):
extracts = extracts_by_author[author_id]
return (-len([e for e in extracts if e.important]), -len(extracts))
# Sort authors by number of important extracts, then extracts
author_ids.sort(key=priority)
if indirect and extracts:
# Get ids of all messages replying one of those extracts's messages
root_posts = list({e.content_id for e in extracts})
pattern = """SELECT id FROM (
SELECT transitive t_in (1) t_out (2) T_DISTINCT T_NO_CYCLES
id, parent_id FROM post ) pa%d
WHERE parent_id = :post_id%d"""
if len(root_posts) > 1:
union = union_all(
*[text(pattern % (n, n)).columns(
column('id')).bindparams(**{'post_id'+str(n): id})
for n, id in enumerate(root_posts)])
else:
union = text(pattern % (0, 0)).columns(
column('id')).bindparams(post_id0=root_posts[0])
# get those messages' authors. Sort by most recent
indirect_authors = self.db.query(AgentProfile.id).join(
Post).filter(Post.id.in_(union)).order_by(
Post.creation_date.desc()).all()
indirect_authors = [x for (x,) in indirect_authors
if x not in author_ids]
author_ids.extend(indirect_authors)
if not author_ids:
return []
if id_only:
return [AgentProfile.uri_generic(id) for id in author_ids]
else:
return self.db.query(AgentProfile).filter(
AgentProfile.id.in_(author_ids)).all()
def _get_contributors(self, indirect=True, id_only=True):
from .post import Post
from .auth import AgentProfile
from .idea_content_link import Extract
# Get extracts related to the idea
extracts = self.db.query(Extract).join(
Extract.extract_source.of_type(Post)).filter(
Extract.idea_id == self.id).options(
joinedload(Extract.extract_source)).all()
extracts_by_author = defaultdict(list)
for e in extracts:
extracts_by_author[e.extract_source.creator_id].append(e)
author_ids = extracts_by_author.keys()
def priority(author_id):
extracts = extracts_by_author[author_id]
return (-len([e for e in extracts if e.important]), -len(extracts))
# Sort authors by number of important extracts, then extracts
author_ids.sort(key=priority)
if indirect and extracts:
# Get ids of all messages replying one of those extracts's messages
root_posts = list({e.content_id for e in extracts})
pattern = """SELECT id FROM (
SELECT transitive t_in (1) t_out (2) T_DISTINCT T_NO_CYCLES
id, parent_id FROM post ) pa%d
WHERE parent_id = :post_id%d"""
if len(root_posts) > 1:
union = union_all(
*[text(pattern % (n, n)).columns(
column('id')).bindparams(**{'post_id'+str(n): id})
for n, id in enumerate(root_posts)])
else:
union = text(pattern % (0, 0)).columns(
column('id')).bindparams(post_id0=root_posts[0])
# get those messages' authors. Sort by most recent
indirect_authors = self.db.query(AgentProfile.id).join(
Post).filter(Post.id.in_(union)).order_by(
Post.creation_date.desc()).all()
indirect_authors = [x for (x,) in indirect_authors
if x not in author_ids]
author_ids.extend(indirect_authors)
if not author_ids:
return []
if id_only:
return [AgentProfile.uri_generic(id) for id in author_ids]
else:
return AgentProfile.db.query(AgentProfile).filter(
AgentProfile.id.in_(author_ids)).all()
def place(self, admin=False):
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.date).label('date')
).join(Challenges).group_by(Solves.teamid)
awards = db.session.query(
Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.date).label('date')
).group_by(Awards.teamid)
if not admin:
freeze = Config.query.filter_by(key='freeze').first()
if freeze and freeze.value:
freeze = int(freeze.value)
freeze = datetime.datetime.utcfromtimestamp(freeze)
scores = scores.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
results = union_all(scores, awards).alias('results')
sumscore = db.func.sum(results.columns.score).label('sumscore')
quickest = db.func.max(results.columns.date).label('quickest')
standings_query = db.session.query(results.columns.teamid)\
.join(Teams)\
.group_by(results.columns.teamid)\
.order_by(sumscore.desc(), quickest)
if not admin:
standings_query = standings_query.filter(Teams.banned == False)
standings = standings_query.all()
# http://codegolf.stackexchange.com/a/4712
try:
i = standings.index((self.id,)) + 1
k = i % 10
return "%d%s" % (i, "tsnrhtdd"[(i / 10 % 10 != 1) * (k < 4) * k::4])
except ValueError:
return 0
def get_compare_avg_cpu_load_data_for_test_ids(self, test_id_1, test_id_2):
test_1_name = self.execute_statement(
select([tests.c.display_name]).where(tests.c.id == test_id_1),
False)[0][0]
test_2_name = self.execute_statement(
select([tests.c.display_name]).where(tests.c.id == test_id_2),
False)[0][0]
if test_1_name == test_2_name:
test_1_name += '_1'
test_2_name += '_2'
st1 = select([tests_monitoring_data.c.server_name,
func.avg(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD_1'),
literal(0).label('CPU_LOAD_2'),
])\
.where(tests_monitoring_data.c.test_id == tests.c.id)\
.where(tests.c.id == test_id_1).group_by(tests_monitoring_data.c.server_name)
st2 = select([
tests_monitoring_data.c.server_name,
literal(0).label('CPU_LOAD_1'),
func.avg(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD_2')]) \
.where(tests_monitoring_data.c.test_id == tests.c.id) \
.where(tests.c.id == test_id_2).group_by(tests_monitoring_data.c.server_name)
#s1 = st1.alias('s1')
#s2 = st2.alias('s2')
qt = union_all(st1, st2).alias("united")
qr = select([
qt.c.server_name,
func.sum(qt.c.CPU_LOAD_1).label(test_1_name),
func.sum(qt.c.CPU_LOAD_2).label(test_2_name)
]).group_by(qt.c.server_name)
#statement = select([s1.c.server_name,s1.c.CPU_LOAD.label(test_1_name),s2.c.CPU_LOAD.label(test_2_name)])\
# .where(s1.c.server_name==s2.c.server_name)
return self.execute_statement(qr, True)
def admin_scoreboard():
score = db.func.sum(Challenges.value).label('score')
scores = db.session.query(Solves.teamid.label('teamid'), Teams.name.label('name'), Teams.banned.label('banned'), score, Solves.date.label('date')) \
.join(Teams) \
.join(Challenges) \
.group_by(Solves.teamid)
awards = db.session.query(Teams.id.label('teamid'), Teams.name.label('name'), Teams.banned.label('banned'),
db.func.sum(Awards.value).label('score'), Awards.date.label('date')) \
.filter(Teams.id == Awards.teamid) \
.group_by(Teams.id)
results = union_all(scores, awards)
standings = db.session.query(results.columns.teamid, results.columns.name, results.columns.banned,
db.func.sum(results.columns.score).label('score')) \
.group_by(results.columns.teamid) \
.order_by(db.func.sum(results.columns.score).desc(), db.func.max(results.columns.date)) \
.all()
db.session.close()
return render_template('admin/scoreboard.html', teams=standings)
def merge_tables(aliases, merge_type, as_alias, *args, **kwargs):
sql_tables = []
for alias in aliases:
sql_tables.append(select([alias.sql_table]).where(alias.where))
rename_to = as_alias.split('/')[-1]
if merge_type == 'union all':
sql = sqlalchemy.alias(union_all(*sql_tables), rename_to)
state_date_columns = aliases[0].state_date_columns
is_deleted_clause = aliases[0].is_deleted_column
elif merge_type == 'union':
sql = sqlalchemy.alias(union(*sql_tables), rename_to)
state_date_columns = aliases[0].state_date_columns
is_deleted_clause = aliases[0].is_deleted_column
elif merge_type == 'modifications':
sql, is_deleted_clause = merge_changes(aliases,
by=kwargs.get('by'),
rename_to=rename_to,
start=kwargs.get('start'), end=kwargs.get('end'))
state_date_columns = [STATE_START_COLUMN, STATE_END_COLUMN]
return sql, state_date_columns, is_deleted_clause
def get_scores(admin=False):
scores = (db.session.query(
Solves.account_id.label("account_id"),
db.func.sum(Challenges.value).label("score"),
db.func.max(Solves.id).label("id"),
db.func.max(Solves.date).label("date"),
).join(Challenges).filter(Challenges.value != 0).group_by(
Solves.account_id))
awards = (db.session.query(
Awards.account_id.label("account_id"),
db.func.sum(Awards.value).label("score"),
db.func.max(Awards.id).label("id"),
db.func.max(Awards.date).label("date"),
).filter(Awards.value != 0).group_by(Awards.account_id))
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = get_config("freeze")
if not admin and freeze:
scores = scores.filter(Solves.date < unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
results = union_all(scores, awards).alias("results")
"""
Sum each of the results by the team id to get their score.
"""
sumscores = (db.session.query(
results.columns.account_id,
db.func.sum(results.columns.score).label("score"),
db.func.max(results.columns.id).label("id"),
db.func.max(results.columns.date).label("date"),
).group_by(results.columns.account_id).subquery())
return sumscores
def species_filter(self, query, criteria):
obsValTable = Base.metadata.tables['ObservationDynPropValuesNow']
o2 = aliased(Observation)
s2 = aliased(Station)
joinStaObs = join(s2, o2, s2.ID == o2.FK_Station)
operator = criteria['Operator']
if 'not' in criteria['Operator']:
operator = operator.replace('not ', '').replace(' not', '')
existInd = select([Individual.ID]).where(
and_(
o2.FK_Individual == Individual.ID,
eval_.eval_binary_expr(Individual.Species, operator,
criteria['Value'])))
existObs = select([obsValTable.c['ID']]).where(
and_(
obsValTable.c['FK_Observation'] == o2.ID,
and_(
or_(obsValTable.c['Name'].like('%taxon'),
obsValTable.c['Name'].like('%species%')),
eval_.eval_binary_expr(obsValTable.c['ValueString'], operator,
criteria['Value']))))
selectCommon = select([s2.ID]).select_from(joinStaObs)
selectInd = selectCommon.where(exists(existInd))
selectObs = selectCommon.where(exists(existObs))
unionQuery = union_all(selectInd, selectObs)
if 'not' in criteria['Operator']:
query = query.where(~Station.ID.in_(unionQuery))
else:
query = query.where(Station.ID.in_(unionQuery))
return query
def scoreboard_view():
if get_config('view_scoreboard_if_authed') and not authed():
return redirect(url_for('auth.login', next=request.path))
score = db.func.sum(Challenges.value).label('score')
scores = db.session.query(Solves.teamid.label('teamid'), Teams.name.label('name'), score, Solves.date.label('date')) \
.join(Teams) \
.join(Challenges) \
.filter(Teams.banned == None) \
.group_by(Solves.teamid)
awards = db.session.query(Teams.id.label('teamid'), Teams.name.label('name'),
db.func.sum(Awards.value).label('score'), Awards.date.label('date')) \
.filter(Teams.id == Awards.teamid) \
.group_by(Teams.id)
results = union_all(scores, awards)
standings = db.session.query(results.columns.teamid, results.columns.name,
db.func.sum(results.columns.score).label('score')) \
.group_by(results.columns.teamid) \
.order_by(db.func.sum(results.columns.score).desc(), db.func.max(results.columns.date)) \
.all()
db.session.close()
return render_template('scoreboard.html', teams=standings)
def get_standings(admin=False, count=None):
score = db.func.sum(Challenges.value).label('score')
date = db.func.max(Solves.date).label('date')
scores = db.session.query(Solves.userid.label('userid'), score, date).join(Challenges).group_by(Solves.userid)
awards = db.session.query(Awards.userid.label('userid'), db.func.sum(Awards.value).label('score'), db.func.max(Awards.date).label('date')) \
.group_by(Awards.userid)
results = union_all(scores, awards).alias('results')
sumscores = db.session.query(results.columns.userid, db.func.sum(results.columns.score).label('score'), db.func.max(results.columns.date).label('date')) \
.group_by(results.columns.userid).subquery()
if admin:
standings_query = db.session.query(Users.id.label('userid'), Users.name.label('name'), Users.banned, sumscores.columns.score) \
.join(sumscores, Users.id == sumscores.columns.userid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
else:
standings_query = db.session.query(Users.id.label('userid'), Users.name.label('name'), sumscores.columns.score) \
.join(sumscores, Users.id == sumscores.columns.userid) \
.filter(Users.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
if count is not None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
db.session.close()
return standings
def __init__(self, user, mask_closed_events=True, search=None, sort=None, order=None):
"""
Initialisation de l'objet qui effectue les requêtes de VigiBoard
sur la base de données.
Cet objet est responsable de la vérification des droits de
l'utilisateur sur les données manipulées.
@param user: Nom de l'utilisateur cherchant à afficher les événements.
@type user: C{str}
@param mask_closed_events: Booléen indiquant si l'on souhaite masquer les
événements fermés ou non.
@type mask_closed_events: C{boolean}
@param search: Dictionnaire contenant les critères de recherche.
@type search: C{dict}
@param sort: Colonne de tri; vide en l'absence de tri.
@type sort: C{unicode}
@param order: Ordre du tri ("asc" ou "desc"); vide en l'absence de tri.
@type order: C{unicode}
"""
# Permet s'appliquer des filtres de recherche aux sous-requêtes.
self.subqueries = []
self.generaterq = False
# Éléments à retourner (SELECT ...)
self.table = []
# Tables sur lesquelles porte la récupération (JOIN)
self.join = []
# Options à ajouter la requête
self.option = []
# Tables sur lesquelles porte la récupération (OUTER JOIN)
self.outerjoin = []
# Critères de filtrage (WHERE)
self.filter = []
# Regroupements (GROUP BY)
# PostgreSQL est pointilleux sur les colonnes qui apparaissent
# dans la clause GROUP BY. Si une colonne apparaît dans ORDER BY,
# elle doit systématiquement apparaître AUSSI dans GROUP BY.
self.groupby = [
StateName.order,
Event.timestamp,
CorrEvent.ack,
CorrEvent.priority,
StateName.statename,
]
self.req = DBSession
self.plugin = []
self.events = []
# Si l'utilisateur est privilégié, il a accès
# à tous les hôtes/services sans restriction.
if config.is_manager.is_met(request.environ):
# Sélection de tous les services de la BDD.
lls_query = DBSession.query(
LowLevelService.idservice.label("idsupitem"),
LowLevelService.servicename.label("servicename"),
Host.name.label("hostname"),
).join(
(Host, Host.idhost == LowLevelService.idhost),
).distinct()
# Sélection de tous les hôtes de la BDD.
host_query = DBSession.query(
Host.idhost.label("idsupitem"),
expr_null().label("servicename"),
Host.name.label("hostname"),
).distinct()
# Application des filtres des plugins si nécessaire.
if search is not None:
# On tire ici partie du fait que les listes sont passées
# par référence dans les fonctions.
subqueries = [lls_query, host_query]
for _plugin, instance in config.get('columns_plugins', []):
instance.handle_search_fields(
self, search, INNER, subqueries)
lls_query = subqueries[0]
host_query = subqueries[1]
# Union des deux sélections précédentes
self.items = union_all(
lls_query,
host_query,
correlate=False
).alias()
# Sinon, on ne récupère que les hôtes/services auquel il a accès.
else:
items = DBSession.query(
UserSupItem.idsupitem,
UserSupItem.servicename,
UserSupItem.hostname,
).filter(
UserSupItem.username == user.user_name
).distinct()
# Application des filtres des plugins si nécessaire.
if search is not None:
# On tire ici partie du fait que les listes sont passées
# par référence dans les fonctions.
subqueries = [items]
for _plugin, instance in config.get('columns_plugins', []):
instance.handle_search_fields(
self, search, INNER, subqueries)
items = subqueries[0]
# Permet d'avoir le même format que pour l'autre requête.
self.items = items.subquery()
# Tris (ORDER BY)
# Permet de répondre aux exigences suivantes :
# - VIGILO_EXIG_VIGILO_BAC_0050
# - VIGILO_EXIG_VIGILO_BAC_0060
self.orderby = []
if sort:
for _plugin, instance in config.get('columns_plugins', []):
criterion = instance.get_sort_criterion(self, sort)
if criterion is not None:
if order == 'asc':
self.orderby.append(asc(criterion))
else:
self.orderby.append(desc(criterion))
# Permet de définir le sens de tri pour la priorité.
if config['vigiboard_priority_order'] == 'asc':
priority_order = asc(CorrEvent.priority)
else:
priority_order = desc(CorrEvent.priority)
self.orderby.extend([
asc(CorrEvent.ack), # État acquittement
asc(StateName.statename.in_([u'OK', u'UP'])), # Vert / Pas vert
priority_order, # Priorité ITIL
])
if asbool(config.get('state_first', True)):
self.orderby.extend([
desc(StateName.order), # Etat courant
desc(Event.timestamp), # Timestamp
])
else:
self.orderby.extend([
desc(Event.timestamp), # Timestamp
desc(StateName.order), # Etat courant
])
if search is not None:
# 2nde passe pour les filtres : self.items est désormais défini.
for _plugin, instance in config.get('columns_plugins', []):
instance.handle_search_fields(self, search, ITEMS, subqueries)
if mask_closed_events:
self.filter.append(
# On masque les événements avec l'état OK
# et traités (ack == CorrEvent.ACK_CLOSED).
not_(and_(
StateName.statename.in_([u'OK', u'UP']),
CorrEvent.ack == CorrEvent.ACK_CLOSED
))
)
def get_place(self, admin=False):
"""
This method is generally a clone of CTFd.scoreboard.get_standings.
The point being that models.py must be self-reliant and have little
to no imports within the CTFd application as importing from the
application itself will result in a circular import.
"""
scores = db.session.query(
Solves.team_id.label('team_id'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.id).label('id'),
db.func.max(Solves.date).label('date')).join(Challenges).filter(
Challenges.value != 0).group_by(Solves.team_id)
awards = db.session.query(
Awards.team_id.label('team_id'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date')).filter(
Awards.value != 0).group_by(Awards.team_id)
if not admin:
freeze = Configs.query.filter_by(key='freeze').first()
if freeze and freeze.value:
freeze = int(freeze.value)
freeze = datetime.datetime.utcfromtimestamp(freeze)
scores = scores.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
results = union_all(scores, awards).alias('results')
sumscores = db.session.query(
results.columns.team_id,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.id).label('id'),
db.func.max(results.columns.date).label('date')).group_by(
results.columns.team_id).subquery()
if admin:
standings_query = db.session.query(
Teams.id.label('team_id'),
) \
.join(sumscores, Teams.id == sumscores.columns.team_id) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Teams.id.label('team_id'),
) \
.join(sumscores, Teams.id == sumscores.columns.team_id) \
.filter(Teams.banned == False,) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
standings = standings_query.all()
# http://codegolf.stackexchange.com/a/4712
try:
i = standings.index((self.id, )) + 1
k = i % 10
return "%d%s" % (i, "tsnrhtdd"[(i / 10 % 10 != 1) *
(k < 4) * k::4])
except ValueError:
return 0
def get_metric_compare_data_for_test_ids(self, test_id_1, test_id_2,
server_1, server_2, metric):
test_name_1 = str(self.get_test_name_for_test_id(test_id_1)[0])
test_name_2 = str(self.get_test_name_for_test_id(test_id_2)[0])
if metric == "CPU_all":
min_time_stamp_1 = select([
func.min(func.date_trunc('minute',tests_monitoring_data.c.timestamp)).label('min_ts')
]).where(tests_monitoring_data.c.test_id == test_id_1). \
where(tests_monitoring_data.c.server_name == server_1)
mts1 = min_time_stamp_1.alias('mts1')
stmt1 = select([
(func.date_trunc('minute', tests_monitoring_data.c.timestamp) -
mts1.c.min_ts).label('timestamp'),
(tests_monitoring_data.c.CPU_user +
tests_monitoring_data.c.CPU_system +
tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD_' +
test_name_1),
literal(0).label('CPU_LOAD_' + test_name_2),
]).where(tests_monitoring_data.c.test_id == test_id_1).where(
tests_monitoring_data.c.server_name == server_1)
min_time_stamp_2 = select([
func.min(func.date_trunc('minute',tests_monitoring_data.c.timestamp)).label('min_ts')
]).where(tests_monitoring_data.c.test_id == test_id_2). \
where(tests_monitoring_data.c.server_name == server_2)
mts2 = min_time_stamp_2.alias('mts2')
stmt2 = select([
(func.date_trunc('minute', tests_monitoring_data.c.timestamp) -
mts2.c.min_ts).label('timestamp'),
literal(0).label('CPU_LOAD_' + test_name_1),
(tests_monitoring_data.c.CPU_user +
tests_monitoring_data.c.CPU_system +
tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD_' +
test_name_2)
]).where(tests_monitoring_data.c.test_id == test_id_2).where(
tests_monitoring_data.c.server_name == server_2)
qt = union_all(stmt1, stmt2).alias("united")
statement = select([qt.c.timestamp, func.sum(qt.c['CPU_LOAD_'+test_name_1]).label('CPU_LOAD_'+test_name_1),
func.sum(qt.c['CPU_LOAD_'+test_name_2]).label('CPU_LOAD_'+test_name_2)]). \
group_by(qt.c.timestamp).order_by(asc(qt.c.timestamp))
else:
min_time_stamp_1 = select([
func.min(func.date_trunc('minute',tests_monitoring_data.c.timestamp)).label('min_ts')
]).where(tests_monitoring_data.c.test_id == test_id_1). \
where(tests_monitoring_data.c.server_name == server_1)
mts1 = min_time_stamp_1.alias('mts1')
stmt1 = select([
(func.date_trunc('minute',tests_monitoring_data.c.timestamp)-mts1.c.min_ts).label('timestamp'),
tests_monitoring_data.c[metric].label(metric+'_'+test_name_1),
literal(0).label(metric + '_' + test_name_2),
]).where(tests_monitoring_data.c.test_id == test_id_1). \
where(tests_monitoring_data.c.server_name == server_1).order_by(asc(tests_monitoring_data.c.timestamp))
min_time_stamp_2 = select([
func.min(func.date_trunc('minute',tests_monitoring_data.c.timestamp)).label('min_ts')
]).where(tests_monitoring_data.c.test_id == test_id_2). \
where(tests_monitoring_data.c.server_name == server_2)
mts2 = min_time_stamp_2.alias('mts2')
stmt2 = select([
(func.date_trunc('minute',tests_monitoring_data.c.timestamp)-mts2.c.min_ts).label('timestamp'),
literal(0).label(metric+'_'+test_name_1),
tests_monitoring_data.c[metric].label(metric+'_'+test_name_2)
]).where(tests_monitoring_data.c.test_id == test_id_2). \
where(tests_monitoring_data.c.server_name == server_2).order_by(asc(tests_monitoring_data.c.timestamp))
qt = union_all(stmt1, stmt2).alias("united")
statement = select([qt.c.timestamp, func.sum(qt.c[metric + '_' + test_name_1]).label(metric + '_' + test_name_1),
func.sum(qt.c[metric + '_' + test_name_2]).label(metric + '_' + test_name_2)]).\
group_by(qt.c.timestamp).order_by(asc(qt.c.timestamp))
return self.execute_statement(statement, True)
def get_standings(count=None, admin=False):
"""
Get standings as a list of tuples containing account_id, name, and score e.g. [(account_id, team_name, score)].
Ties are broken by who reached a given score first based on the solve ID. Two users can have the same score but one
user will have a solve ID that is before the others. That user will be considered the tie-winner.
Challenges & Awards with a value of zero are filtered out of the calculations to avoid incorrect tie breaks.
"""
Model = get_model()
scores = db.session.query(
Solves.account_id.label('account_id'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.id).label('id'),
db.func.max(Solves.date).label('date'),
db.func.concat("0", "").cast(db.Integer).label('unlock_count'),
db.func.count(Solves.id).label('solve'),
).join(Challenges) \
.filter(Challenges.value != 0) \
.group_by(Solves.account_id)
awards = db.session.query(
Awards.account_id.label('account_id'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date'),
db.func.concat("0", "").cast(db.Integer).label('unlock_count'),
db.func.concat("0", "").cast(db.Integer).label('solve')
) \
.filter(Awards.value != 0) \
.group_by(Awards.account_id)
hints_name_list = db.session.query(
db.func.concat("Hint ", Hints.id).label("hints_name")).count()
if hints_name_list > 0:
hints_name = db.func.concat("Hint ", Hints.id).label("hints_name")
awards = db.session.query(
Awards.account_id.label('account_id'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date'),
db.func.count(Awards.value < 0).label('unlock_count'),
db.func.concat("0", "").cast(db.Integer).label('solve')
) \
.join(Hints, Awards.name == hints_name) \
.join(Solves, (Awards.account_id == Solves.account_id) & (Hints.challenge_id == Solves.challenge_id)) \
.filter(Awards.value != 0) \
.group_by(Awards.account_id)
awards_by_admin = db.session.query(
Awards.account_id.label('account_id'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date'),
db.func.concat("0", "").cast(db.Integer).label('unlock_count'),
db.func.concat("0", "").cast(db.Integer).label('solve')
) \
.filter(Awards.value > 0) \
.group_by(Awards.account_id)
awards = awards.union(awards_by_admin)
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = get_config('freeze')
if not admin and freeze:
scores = scores.filter(Solves.date < unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
results = union_all(scores, awards).alias('results')
"""
Sum each of the results by the team id to get their score.
"""
sumscores = db.session.query(
results.columns.account_id,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.id).label('id'),
db.func.max(results.columns.date).label('date'),
db.func.max(results.columns.unlock_count).label('unlock_count'),
db.func.sum(results.columns.solve).label('solve'),
).group_by(results.columns.account_id) \
.subquery()
"""
Admins can see scores for all users but the public cannot see banned users.
Filters out banned users.
Properly resolves value ties by ID.
Different databases treat time precision differently so resolve by the row ID instead.
"""
if admin:
standings_query = db.session.query(
Model.id.label('account_id'),
Model.oauth_id.label('oauth_id'),
Model.name.label('name'),
Model.hidden,
Model.banned,
sumscores.columns.score
) \
.join(sumscores, Model.id == sumscores.columns.account_id) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.unlock_count.asc(), sumscores.columns.date.asc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Model.id.label('account_id'),
Model.oauth_id.label('oauth_id'),
Model.name.label('name'),
sumscores.columns.score,
sumscores.columns.solve,
) \
.join(sumscores, Model.id == sumscores.columns.account_id) \
.filter(Model.banned == False, Model.hidden == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.unlock_count.asc(), sumscores.columns.date.asc(), sumscores.columns.id)
"""
Only select a certain amount of users if asked.
"""
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
db.session.close()
return standings
def union_select(select_components, distinct=False, select_type=TRIPLE_SELECT):
"""
Helper function for building union all select statement.
Args:
select_components (iterable of tuples): Indicates the table and table type
(table_name, where_clause_string, table_type)
distinct (bool): Whether to eliminate duplicate results
select_type (int): From `rdflib_sqlalchemy.constants`. Either `COUNT_SELECT`,
`CONTEXT_SELECT`, `TRIPLE_SELECT`
"""
selects = []
for table, whereClause, tableType in select_components:
if select_type == COUNT_SELECT:
c = table.c
if tableType == ASSERTED_TYPE_PARTITION:
cols = [c.member, c.klass]
elif tableType in (ASSERTED_LITERAL_PARTITION,
ASSERTED_NON_TYPE_PARTITION, QUOTED_PARTITION):
cols = [c.subject, c.predicate, c.object]
else:
raise ValueError(
'Unrecognized table type {}'.format(tableType))
select_clause = expression.select([
functions.count().label('aCount')
]).select_from(
expression.select(cols,
whereClause).distinct().select_from(table))
elif select_type == CONTEXT_SELECT:
select_clause = expression.select([table.c.context], whereClause)
elif tableType in FULL_TRIPLE_PARTITIONS:
select_clause = table.select(whereClause)
elif tableType == ASSERTED_TYPE_PARTITION:
select_clause = expression.select([
table.c.id.label("id"),
table.c.member.label("subject"),
expression.literal(text_type(RDF.type)).label("predicate"),
table.c.klass.label("object"),
table.c.context.label("context"),
table.c.termComb.label("termcomb"),
expression.literal_column("NULL").label("objlanguage"),
expression.literal_column("NULL").label("objdatatype")
], whereClause)
elif tableType == ASSERTED_NON_TYPE_PARTITION:
select_clause = expression.select([c for c in table.columns] + [
expression.literal_column("NULL").label("objlanguage"),
expression.literal_column("NULL").label("objdatatype")
],
whereClause,
from_obj=[table])
selects.append(select_clause)
order_statement = []
if select_type == TRIPLE_SELECT:
order_statement = [
expression.literal_column("subject"),
expression.literal_column("predicate"),
expression.literal_column("object"),
]
if distinct and select_type != COUNT_SELECT:
return expression.union(*selects, **{"order_by": order_statement})
else:
return expression.union_all(*selects, **{"order_by": order_statement})
def get_monthly_stats(cls, year):
"""
Gets monthly statistics for the specified year
Args:
year (int): The year to which the invoice number belongs, e.g.
2019.
Returns:
Sums of the normale and reversal invoices per calendar month based
on the invoice date.
"""
year_class = cls._get_year_class(year)
if year_class is None:
return None
db_session = DBSession()
result = []
# SQLite specific: substring for SQLite as it does not support
# date_trunc.
# invoice_date_month = func.date_trunc(
# 'month',
# invoice_date)
paid_date = cls._PAYMENT_FIELDS[year]['paid_date']
amount_paid = cls._PAYMENT_FIELDS[year]['amount_paid']
invoice_date_month = func.substr(year_class.invoice_date, 1, 7)
payment_date_month = func.substr(paid_date, 1, 7)
# collect the invoice amounts per month
invoice_amounts_query = db_session.query(
invoice_date_month.label('month'),
func.sum(expression.case(
[(
expression.not_(year_class.is_reversal),
year_class.invoice_amount)],
else_=Decimal('0.0'))).label('amount_invoiced_normal'),
func.sum(expression.case(
[(
year_class.is_reversal,
year_class.invoice_amount)],
else_=Decimal('0.0'))).label('amount_invoiced_reversal'),
expression.literal_column(
'\'0.0\'', DatabaseDecimal).label('amount_paid')
).group_by(invoice_date_month)
# collect the payments per month
member_payments_query = db_session.query(
payment_date_month.label('month'),
expression.literal_column(
'\'0.0\'', DatabaseDecimal).label('amount_invoiced_normal'),
expression.literal_column(
'\'0.0\'', DatabaseDecimal
).label('amount_invoiced_reversal'),
func.sum(amount_paid).label('amount_paid')
).filter(paid_date.isnot(None)) \
.group_by(payment_date_month)
# union invoice amounts and payments
union_all_query = expression.union_all(
member_payments_query, invoice_amounts_query)
# aggregate invoice amounts and payments by month
result_query = db_session.query(
union_all_query.c.month.label('month'),
func.sum(union_all_query.c.amount_invoiced_normal).label(
'amount_invoiced_normal'),
func.sum(union_all_query.c.amount_invoiced_reversal).label(
'amount_invoiced_reversal'),
func.sum(union_all_query.c.amount_paid).label('amount_paid')
) \
.group_by(union_all_query.c.month) \
.order_by(union_all_query.c.month)
for month_stat in result_query.all():
result.append(
{
'month': datetime(
int(month_stat[0][0:4]),
int(month_stat[0][5:7]),
1),
'amount_invoiced_normal': month_stat[1],
'amount_invoiced_reversal': month_stat[2],
'amount_paid': month_stat[3]
})
return result
def get_range(comp, admin=False, count=None, teamid=None):
Comp = Competitions.query.filter(Competitions.id == comp).first()
if not Comp:
return []
scores = db.session.query(Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.id).label('id'),
db.func.max(Solves.date).label('date')).join(
Challenges).join(Chalcomp).group_by(
Solves.teamid)
awards = db.session.query(Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date')).group_by(
Awards.teamid)
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = utils.get_config('freeze')
chals = [x.chalid for x in Comp.chals]
awardTitles = [u"Hint for {}".format(x.chal.name) for x in Comp.chals]
print awardTitles
if not admin and freeze:
scores = scores.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
scores = scores.filter(Chalcomp.compid == Comp.id)
scores = scores.filter(Solves.date < Comp.endTime)
scores = scores.filter(Solves.date > Comp.startTime)
awards = awards.filter(Awards.date < Comp.endTime)
awards = awards.filter(Awards.date > Comp.startTime)
awards = awards.filter(Awards.name.in_(awardTitles))
#awards=scores.filter(Solves.chalid in chals)
results = union_all(scores, awards).alias('results')
if (teamid is not None):
scores = scores.filter(Solves.teamid == teamid)
awards = awards.filter(Solves.teamid == teamid)
"""
Sum each of the results by the team id to get their score.
"""
sumscores = db.session.query(
results.columns.teamid,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.id).label('id'),
db.func.max(results.columns.date).label('date')).group_by(
results.columns.teamid).subquery()
"""
Admins can see scores for all users but the public cannot see banned users.
Filters out banned users.
Properly resolves value ties by ID.
Different databases treat time precision differently so resolve by the row ID instead.
"""
if admin:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
#print standings_query
"""
Only select a certain amount of users if asked.
"""
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
db.session.close()
return standings
def WhereInJoinTable(self, query, criteriaObj):
''' Override parent function to include management of Observation/Protocols and fieldWorkers '''
query = super().WhereInJoinTable(query, criteriaObj)
curProp = criteriaObj['Column']
if curProp == 'FK_ProtocoleType':
o = aliased(Observation)
subSelect = select([o.ID]
).where(
and_(Station.ID == o.FK_Station,
eval_.eval_binary_expr(o.FK_ProtocoleType, criteriaObj['Operator'],
criteriaObj['Value'])))
query = query.where(exists(subSelect))
if curProp == 'Species':
obsValTable = Base.metadata.tables['ObservationDynPropValuesNow']
o2 = aliased(Observation)
s2 = aliased(Station)
joinStaObs = join(s2, o2, s2.ID == o2.FK_Station)
operator = criteriaObj['Operator']
if 'not' in criteriaObj['Operator']:
operator = operator.replace('not ', '').replace(' not', '')
existInd = select([Individual.ID]
).where(and_(o2.FK_Individual == Individual.ID,
eval_.eval_binary_expr(Individual.Species, operator, criteriaObj['Value']))
)
existObs = select([obsValTable.c['ID']]
).where(and_(obsValTable.c['FK_Observation'] == o2.ID,
and_(or_(obsValTable.c['Name'].like('%taxon'), obsValTable.c['Name'].like('%species%')),
eval_.eval_binary_expr(obsValTable.c['ValueString'], operator, criteriaObj['Value']))
)
)
selectCommon = select([s2.ID]).select_from(joinStaObs)
selectInd = selectCommon.where(exists(existInd))
selectObs = selectCommon.where(exists(existObs))
unionQuery = union_all(selectInd, selectObs)
if 'not' in criteriaObj['Operator']:
query = query.where(~Station.ID.in_(unionQuery))
else:
query = query.where(Station.ID.in_(unionQuery))
if curProp == 'FK_Individual':
if criteriaObj['Operator'].lower() in ['is null', 'is not null']:
subSelect = select([Observation]).where(
and_(Station.ID == Observation.FK_Station,
Observation.__table__.c[curProp] != None)
)
if criteriaObj['Operator'].lower() == 'is':
query = query.where(~exists(subSelect))
else:
query = query.where(exists(subSelect))
else:
subSelect = select([Observation]
).where(
and_(Station.ID == Observation.FK_Station,
eval_.eval_binary_expr(Observation.__table__.c[curProp],
criteriaObj['Operator'],
criteriaObj['Value'])))
query = query.where(exists(subSelect))
if curProp == 'FK_FieldWorker':
joinTable = join(Station_FieldWorker, User, Station_FieldWorker.FK_FieldWorker == User.id)
subSelect = select([Station_FieldWorker]
).select_from(joinTable).where(
and_(Station.ID == Station_FieldWorker.FK_Station,
eval_.eval_binary_expr(User.__table__.c['Login'],
criteriaObj['Operator'],
criteriaObj['Value'])))
query = query.where(exists(subSelect))
if curProp == 'LastImported':
st = aliased(Station)
subSelect2 = select([st]).where(
cast(st.creationDate, DATE) > cast(Station.creationDate, DATE))
query = query.where(~exists(subSelect2))
return query
def get_standings(admin=False, count=None):
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.id).label('id'),
db.func.max(Solves.date).label('date')).join(Challenges).group_by(
Solves.teamid)
awards = db.session.query(Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date')).group_by(
Awards.teamid)
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = utils.get_config('freeze')
if not admin and freeze:
scores = scores.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
results = union_all(scores, awards).alias('results')
"""
Sum each of the results by the team id to get their score.
"""
sumscores = db.session.query(
results.columns.teamid,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.id).label('id'),
db.func.max(results.columns.date).label('date')).group_by(
results.columns.teamid).subquery()
"""
Admins can see scores for all users but the public cannot see banned users.
Filters out banned users.
Properly resolves value ties by ID.
Different databases treat time precision differently so resolve by the row ID instead.
"""
if admin:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
"""
Only select a certain amount of users if asked.
"""
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
db.session.close()
return standings
def get_json_data(self, idcorrevent, *args, **kwargs):
"""
Renvoie les éléments pour l'affichage de la fenêtre de dialogue
contenant des détails sur un événement corrélé.
@param idcorrevent: identifiant de l'événement corrélé.
@type idcorrevent: C{int}
"""
# Obtention de données sur l'événement et sur son historique
host_query = DBSession.query(
Host.idhost.label("idsupitem"),
Host.idhost.label("idhost"),
Host.name.label("host"),
expr_null().label("service"),
)
lls_query = DBSession.query(
LowLevelService.idservice.label("idsupitem"),
Host.idhost.label("idhost"),
Host.name.label("host"),
LowLevelService.servicename.label("service"),
).join(
(Host, Host.idhost == LowLevelService.idhost),
)
supitems = union_all(lls_query, host_query, correlate=False).alias()
event = DBSession.query(
CorrEvent.idcorrevent,
CorrEvent.idcause,
supitems.c.idhost,
supitems.c.host,
supitems.c.service,
Event.message,
Event.initial_state,
Event.current_state,
Event.peak_state
).join(
(Event, Event.idevent == CorrEvent.idcause),
(supitems, supitems.c.idsupitem == Event.idsupitem),
).filter(CorrEvent.idcorrevent == idcorrevent
).first()
# On détermine les cartes auxquelles cet utilisateur a accès.
user_maps = []
max_maps = int(config['max_maps'])
is_manager = config.is_manager.is_met(request.environ)
if max_maps != 0 and (is_manager or
has_permission('vigimap-access').is_met(request.environ)):
items = DBSession.query(
Map.idmap,
Map.title,
func.lower(Map.title),
).distinct(
).join(
(MAP_GROUP_TABLE, MAP_GROUP_TABLE.c.idmap == Map.idmap),
(MapGroup, MapGroup.idgroup == MAP_GROUP_TABLE.c.idgroup),
(MapNodeHost, MapNodeHost.idmap == Map.idmap),
).order_by(func.lower(Map.title).asc()
).filter(MapNodeHost.idhost == event.idhost)
if not is_manager:
mapgroups = get_current_user().mapgroups(only_direct=True)
# pylint: disable-msg=E1103
items = items.filter(MapGroup.idgroup.in_(mapgroups))
# La valeur -1 supprime la limite.
if max_maps > 0:
# On limite au nombre maximum de cartes demandés + 1.
# Un message sera affiché s'il y a effectivement plus
# de cartes que la limite configurée.
items = items.limit(max_maps + 1)
user_maps = [(m.idmap, m.title) for m in items.all()]
context = {
'idcorrevent': idcorrevent,
'host': event.host,
'service': event.service,
'message': event.message,
'maps': user_maps,
'current_state': StateName.value_to_statename(event.current_state),
'initial_state': StateName.value_to_statename(event.initial_state),
'peak_state': StateName.value_to_statename(event.peak_state),
}
eventdetails = {}
for edname, edlink in enumerate(config['vigiboard_links.eventdetails']):
# Évite que les gardes ne se polluent entre elles.
local_ctx = context.copy()
# Les liens peuvent être conditionnés à l'aide
# d'une expression ou d'un callable qui agira
# comme un prédicat de test.
if 'only_if' in edlink:
if callable(edlink['only_if']):
display_link = edlink['only_if'](local_ctx)
else:
display_link = edlink['only_if']
if not display_link:
continue
if callable(edlink['uri']):
uri = edlink['uri'](local_ctx)
else:
uri = edlink['uri'] % local_ctx
eventdetails[unicode(edname)] = {
'url': url(uri),
'target': edlink.get('target', '_blank')
}
return dict(
current_state = StateName.value_to_statename(
event.current_state),
initial_state = StateName.value_to_statename(
event.initial_state),
peak_state = StateName.value_to_statename(
event.peak_state),
idcorrevent = idcorrevent,
host = event.host,
service = event.service,
eventdetails = eventdetails,
maps = user_maps,
idcause = event.idcause,
)
def get_beer_scores(tasting):
s1 = db.session.query(
Beers.id.label('beer_id'),
Beers.tasting_id.label('tasting_id'),
Beers.number.label('number'),
ScoreLook.score.label('score'),
ScoreLook.participant_id.label('participant_id')
).join(ScoreLook)
s2 = db.session.query(
Beers.id.label('beer_id'),
Beers.tasting_id.label('tasting_id'),
Beers.number.label('number'),
ScoreSmell.score.label('score'),
ScoreSmell.participant_id.label('participant_id')
).join(ScoreSmell)
s3 = db.session.query(
Beers.id.label('beer_id'),
Beers.tasting_id.label('tasting_id'),
Beers.number.label('number'),
ScoreTaste.score.label('score'),
ScoreTaste.participant_id.label('participant_id')
).join(ScoreTaste)
s4 = db.session.query(
Beers.id.label('beer_id'),
Beers.tasting_id.label('tasting_id'),
Beers.number.label('number'),
ScoreAftertaste.score.label('score'),
ScoreAftertaste.participant_id.label('participant_id')
).join(ScoreAftertaste)
s5 = db.session.query(
Beers.id.label('beer_id'),
Beers.tasting_id.label('tasting_id'),
Beers.number.label('number'),
ScoreXmas.score.label('score'),
ScoreXmas.participant_id.label('participant_id')
).join(ScoreXmas)
s = union_all(s1, s2, s3, s4, s5).alias('s')
scores = db.session.query(
db.func.sum(s.c.score).label('sum'),
s.c.beer_id.label('beer_id'),
Participants.id.label('participant_id')
).join(
Participants,
s.c.participant_id == Participants.id
).group_by(Participants.id, s.c.beer_id, s.c.tasting_id).subquery()
details = {}
for beer in Beers.query.filter(Beers.tasting_id == tasting.id).all():
details[beer.number] = db.session.query(
scores.c.sum, Participants.name
).join(
Participants, Participants.id == scores.c.participant_id
).join(
Beers, Beers.id == scores.c.beer_id
).filter(Beers.id == beer.id).all()
return {
"totals": db.session.query(
db.func.sum(scores.c.sum).label('sum'),
db.func.avg(scores.c.sum).label('avg'),
db.func.std(scores.c.sum).label('std'),
Beers.name.label('name'),
Beers.number.label('number'),
Heats.id.label('heat_id'),
Heats.name.label('heat_name')
).join(
Beers, Beers.id == scores.c.beer_id
).join(
Heats,
db.func.coalesce(Beers.heat_id, '') == db.func.coalesce(Heats.id, ''),
isouter=True
).filter(
Beers.tasting_id == tasting.id
).group_by(scores.c.beer_id).all(),
"details": details
}
def get_standings(admin=False, count=None, classification=None):
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.date).label('date')).join(Challenges).group_by(
Solves.teamid)
awards = db.session.query(
Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.date).label('date')).group_by(Awards.teamid)
freeze = utils.get_config('freeze')
if not admin and freeze:
scores = scores.filter(
Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(
Awards.date < utils.unix_time_to_utc(freeze))
results = union_all(scores, awards).alias('results')
sumscores = db.session.query(
results.columns.teamid,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.date).label('date')).group_by(
results.columns.teamid).subquery()
if admin:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score,
Classification.classification
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.join(Classification, Teams.id == Classification.id) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score,
Classification.classification
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.join(Classification, Teams.id == Classification.id) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
if classification and count:
# -=- For TAMUctf, but can be left in without any problems -=-
try:
tamu_test()
c = Classification
if (classification == "tamu"):
standings = standings_query.filter(
or_(c.classification == "U0", c.classification == "U1",
c.classification == "U2", c.classification == "U3",
c.classification == "U4", c.classification == "U5",
c.classification == "G5", c.classification == "G6",
c.classification == "G7", c.classification == "G8",
c.classification == "G9")).limit(count).all()
elif (classification == "tamug"):
standings = standings_query.filter(
or_(c.classification == "G5", c.classification == "G6",
c.classification == "G7", c.classification == "G8",
c.classification == "G9")).limit(count).all()
elif (classification == "tamuu"):
standings = standings_query.filter(
or_(c.classification == "U0", c.classification == "U1",
c.classification == "U2", c.classification == "U3",
c.classification == "U4",
c.classification == "U5")).limit(count).all()
elif (classification == "U4"):
standings = standings_query.filter(
or_(c.classification == "U4",
c.classification == "U5")).limit(count).all()
elif (classification == "tamum"):
standings = standings_query.filter(
or_(c.other == 3, c.other == 5, c.other == 7,
c.other == 8, c.other == 12, c.other == 10,
c.other == 15)).limit(count).all()
elif (classification == "tamumc"):
standings = standings_query.filter(
or_(c.other == 3, c.other == 8, c.other == 10,
c.other == 15)).limit(count).all()
elif (classification == "tamumr"):
standings = standings_query.filter(
or_(c.other == 5, c.other == 8, c.other == 12,
c.other == 15)).limit(count).all()
elif (classification == "tamumd"):
standings = standings_query.filter(
or_(c.other == 7, c.other == 12, c.other == 10,
c.other == 15)).limit(count).all()
else:
standings = standings_query.filter(
Classification.classification == classification).limit(
count).all()
except:
standings = standings_query.filter(
Classification.classification == classification).limit(
count).all()
#-=-
elif classification:
# -=- For TAMUctf, but can be left in without any problems -=-
try:
tamu_test()
c = Classification
if (classification == "tamu"):
standings = standings_query.filter(
or_(c.classification == "U0", c.classification == "U1",
c.classification == "U2", c.classification == "U3",
c.classification == "U4", c.classification == "U5",
c.classification == "G5", c.classification == "G6",
c.classification == "G7", c.classification == "G8",
c.classification == "G9")).all()
elif (classification == "tamug"):
standings = standings_query.filter(
or_(c.classification == "G5", c.classification == "G6",
c.classification == "G7", c.classification == "G8",
c.classification == "G9")).all()
elif (classification == "tamuu"):
standings = standings_query.filter(
or_(c.classification == "U01",
c.classification == "U1", c.classification == "U2",
c.classification == "U3", c.classification == "U4",
c.classification == "U5")).all()
elif (classification == "tamuu"):
standings = standings_query.filter(
or_(c.classification == "U4",
c.classification == "U5")).all()
elif (classification == "tamum"):
standings = standings_query.filter(
or_(c.other == 3, c.other == 5, c.other == 7,
c.other == 8, c.other == 12, c.other == 10,
c.other == 15)).all()
elif (classification == "tamumc"):
standings = standings_query.filter(
or_(c.other == 3, c.other == 8, c.other == 10,
c.other == 15)).all()
elif (classification == "tamumr"):
standings = standings_query.filter(
or_(c.other == 5, c.other == 8, c.other == 12,
c.other == 15)).all()
elif (classification == "tamumd"):
standings = standings_query.filter(
or_(c.other == 7, c.other == 12, c.other == 10,
c.other == 15)).all()
else:
standings = standings_query.filter(
Classification.classification == classification).all()
except:
standings = standings_query.filter(
Classification.classification == classification).all()
#-=-
elif count:
standings = standings_query.limit(count).all()
else:
standings = standings_query.all()
return standings
def get_monitoring_data(self, test_id, server_name, metric):
if metric == "CPU_all":
min_time_stamp_1 = select([
func.min(func.date_trunc('minute',tests_monitoring_data.c.timestamp)).label('min_ts')
]).where(tests_monitoring_data.c.test_id == test_id). \
where(tests_monitoring_data.c.server_name == server_name)
mts1 = min_time_stamp_1.alias('mts1')
stmt1 = select([
(func.date_trunc('minute',tests_monitoring_data.c.timestamp)-mts1.c.min_ts).label('timestamp'),
(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label(metric),
literal(0).label('rps')
]).where(tests_monitoring_data.c.test_id == test_id).where(tests_monitoring_data.c.server_name == server_name).\
order_by(asc(tests_monitoring_data.c.timestamp))
min_time_stamp_2 = select([
func.min(
func.date_trunc(
'minute',
tests_overall_data.c.timestamp)).label('min_ts')
]).where(tests_overall_data.c.test_id == test_id)
mts2 = min_time_stamp_2.alias('mts2')
stmt2 = select([
(func.date_trunc('minute', tests_overall_data.c.timestamp) -
mts2.c.min_ts).label('timestamp'),
literal(0).label(metric),
(tests_overall_data.c.count / 60).label('rps')
]).where(tests_overall_data.c.test_id == test_id).order_by(
asc(tests_overall_data.c.timestamp))
qt = union_all(stmt1, stmt2).alias("united")
statement = select([
qt.c.timestamp,
func.sum(qt.c[metric]).label(metric),
func.sum(qt.c['rps']).label('rps')
]).group_by(qt.c.timestamp).order_by(asc(qt.c.timestamp))
else:
min_time_stamp_1 = select([
func.min(func.date_trunc('minute',tests_monitoring_data.c.timestamp)).label('min_ts')
]).where(tests_monitoring_data.c.test_id == test_id). \
where(tests_monitoring_data.c.server_name == server_name)
mts1 = min_time_stamp_1.alias('mts1')
stmt1 = select([
(func.date_trunc('minute',tests_monitoring_data.c.timestamp)-mts1.c.min_ts).label('timestamp'),
tests_monitoring_data.c[metric].label(metric),
literal(0).label('rps')
]).where(tests_monitoring_data.c.test_id == test_id).where(tests_monitoring_data.c.server_name == server_name).\
order_by(asc(tests_monitoring_data.c.timestamp))
min_time_stamp_2 = select([
func.min(
func.date_trunc(
'minute',
tests_overall_data.c.timestamp)).label('min_ts')
]).where(tests_overall_data.c.test_id == test_id)
mts2 = min_time_stamp_2.alias('mts2')
stmt2 = select([
(func.date_trunc('minute', tests_overall_data.c.timestamp) -
mts2.c.min_ts).label('timestamp'),
literal(0).label(metric),
(tests_overall_data.c.count / 60).label('rps')
]).where(tests_overall_data.c.test_id == test_id).order_by(
asc(tests_overall_data.c.timestamp))
qt = union_all(stmt1, stmt2).alias("united")
statement = select([
qt.c.timestamp,
func.sum(qt.c[metric]).label(metric),
func.sum(qt.c['rps']).label('rps')
]).group_by(qt.c.timestamp).order_by(asc(qt.c.timestamp))
return self.execute_statement(statement, True)
def get_place(self, admin=False, numeric=False):
"""
This method is generally a clone of CTFd.scoreboard.get_standings.
The point being that models.py must be self-reliant and have little
to no imports within the CTFd application as importing from the
application itself will result in a circular import.
"""
scores = db.session.query(
Solves.user_id.label('user_id'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.id).label('id'),
db.func.max(Solves.date).label('date'),
db.func.concat("0", "").cast(
db.Integer).label('unlock_count')).join(Challenges).filter(
Challenges.value != 0).group_by(Solves.user_id)
awards = db.session.query(
Awards.user_id.label('user_id'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date'),
db.func.concat("0",
"").cast(db.Integer).label('unlock_count')).filter(
Awards.value != 0).group_by(Awards.user_id)
if self.get_hints():
hints_name = db.func.concat("Hint ", Hints.id).label("hints_name")
awards = db.session.query(
Awards.user_id.label('user_id'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date'),
db.func.count(Awards.value < 0).label('unlock_count')
) \
.join(Hints, Awards.name == hints_name) \
.join(Solves, (Awards.user_id == Solves.user_id) & (Hints.challenge_id == Solves.challenge_id)) \
.filter(Awards.value != 0).group_by(Awards.user_id)
awards_by_admin = db.session.query(
Awards.user_id.label('user_id'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date'),
db.func.concat("0", "").cast(db.Integer).label('unlock_count')
) \
.filter(Awards.value > 0).group_by(Awards.user_id)
awards = awards.union(awards_by_admin)
if not admin:
freeze = Configs.query.filter_by(key='freeze').first()
if freeze and freeze.value:
freeze = int(freeze.value)
freeze = datetime.datetime.utcfromtimestamp(freeze)
scores = scores.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
results = union_all(scores, awards).alias('results')
sumscores = db.session.query(
results.columns.user_id,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.id).label('id'),
db.func.max(results.columns.date).label('date'),
db.func.sum(results.columns.unlock_count).label('unlock_count'),
).group_by(results.columns.user_id).subquery()
if admin:
standings_query = db.session.query(
Users.id.label('user_id'),
) \
.join(sumscores, Users.id == sumscores.columns.user_id) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.unlock_count.asc(), sumscores.columns.date.asc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Users.id.label('user_id'),
) \
.join(sumscores, Users.id == sumscores.columns.user_id) \
.filter(Users.banned == False, Users.hidden == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.unlock_count.asc(), sumscores.columns.date.asc(), sumscores.columns.id)
standings = standings_query.all()
# http://codegolf.stackexchange.com/a/4712
try:
i = standings.index((self.id, )) + 1
if numeric:
return i
else:
k = i % 10
return "%d%s" % (i, "tsnrhtdd"[(i / 10 % 10 != 1) *
(k < 4) * k::4])
except ValueError:
return 0
def get_standings(admin=False, count=None):
"""
Get team standings as a list of tuples containing team_id, team_name, and score e.g. [(team_id, team_name, score)].
Ties are broken by who reached a given score first based on the solve ID. Two users can have the same score but one
user will have a solve ID that is before the others. That user will be considered the tie-winner.
Challenges & Awards with a value of zero are filtered out of the calculations to avoid incorrect tie breaks.
"""
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.id).label('id'),
db.func.max(Solves.date).label('date')
).join(Challenges)\
.filter(Challenges.value != 0)\
.group_by(Solves.teamid)
awards = db.session.query(
Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date')
)\
.filter(Awards.value != 0)\
.group_by(Awards.teamid)
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = utils.get_config('freeze')
if not admin and freeze:
scores = scores.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
results = union_all(scores, awards).alias('results')
"""
Sum each of the results by the team id to get their score.
"""
sumscores = db.session.query(
results.columns.teamid,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.id).label('id'),
db.func.max(results.columns.date).label('date')
).group_by(results.columns.teamid)\
.subquery()
"""
Admins can see scores for all users but the publics.
cannot see banned user
Filters out banned users.
Properly resolves value ties by ID.
Different databases treat time precision differently so resolve by the row ID instead.
"""
if admin:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score,
Teams.solves_count
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score,
Teams.solves_count
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
"""
Only select a certain amount of users if asked.
"""
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
db.session.close()
return standings
def WhereInJoinTable(self, query, criteriaObj):
''' Override parent function to include management of Observation/Protocols and fieldWorkers '''
query = super().WhereInJoinTable(query, criteriaObj)
curProp = criteriaObj['Column']
if curProp == 'FK_ProtocoleType':
o = aliased(Observation)
subSelect = select([o.ID]
).where(
and_(Station.ID == o.FK_Station, eval_.eval_binary_expr(o.FK_ProtocoleType, criteriaObj['Operator'], criteriaObj['Value'])))
query = query.where(exists(subSelect))
if curProp == 'Species':
obsValTable = Base.metadata.tables['ObservationDynPropValuesNow']
o2 = aliased(Observation)
s2 = aliased(Station)
s1 = aliased(Station)
joinStaObs = join(s2, o2, s2.ID == o2.FK_Station)
operator = criteriaObj['Operator']
if 'not' in criteriaObj['Operator']:
operator = operator.replace('not ', '').replace(' not', '')
existInd = select([Individual.ID]
).where(and_(o2.FK_Individual == Individual.ID, eval_.eval_binary_expr(Individual.Species, operator, criteriaObj['Value']))
)
existObs = select([obsValTable.c['ID']]
).where(and_(obsValTable.c['FK_Observation'] == o2.ID, and_(or_(obsValTable.c['Name'].like('%taxon'), obsValTable.c['Name'].like('%species%')), eval_.eval_binary_expr(obsValTable.c['ValueString'], operator, criteriaObj['Value'])
)
)
)
selectCommon = select([s2.ID]).select_from(joinStaObs)
# if 'not' in criteriaObj['Operator'] :
# selectInd = selectCommon.where(~exists(existInd))
# selectObs = selectCommon.where(~exists(existObs))
# else :
selectInd = selectCommon.where(exists(existInd))
selectObs = selectCommon.where(exists(existObs))
unionQuery = union_all(selectInd, selectObs)
if 'not' in criteriaObj['Operator']:
query = query.where(~Station.ID.in_(unionQuery))
else:
query = query.where(Station.ID.in_(unionQuery))
if curProp == 'FK_Individual':
if criteriaObj['Operator'].lower() in ['is null', 'is not null']:
subSelect = select([Observation]).where(
and_(Station.ID == Observation.FK_Station,
Observation.__table__.c[curProp] != None)
)
if criteriaObj['Operator'].lower() == 'is':
query = query.where(~exists(subSelect))
else:
query = query.where(exists(subSelect))
else:
subSelect = select([Observation]
).where(
and_(Station.ID == Observation.FK_Station, eval_.eval_binary_expr(Observation.__table__.c[curProp], criteriaObj['Operator'], criteriaObj['Value'])))
query = query.where(exists(subSelect))
if curProp == 'FK_FieldWorker':
subSelect = select([Station_FieldWorker]
).where(
and_(Station.ID == Station_FieldWorker.FK_Station, eval_.eval_binary_expr(Station_FieldWorker.__table__.c[curProp], criteriaObj['Operator'], criteriaObj['Value'])))
query = query.where(exists(subSelect))
if curProp == 'LastImported':
st = aliased(Station)
subSelect = select([Observation]).where(
Observation.FK_Station == Station.ID)
subSelect2 = select([st]).where(
cast(st.creationDate, DATE) > cast(Station.creationDate, DATE))
query = query.where(and_(~exists(subSelect), ~exists(subSelect2)))
return query
def get_standings(count=None, admin=False):
"""
Get standings as a list of tuples containing account_id, name, and score e.g. [(account_id, team_name, score)].
Ties are broken by who reached a given score first based on the solve ID. Two users can have the same score but one
user will have a solve ID that is before the others. That user will be considered the tie-winner.
Challenges & Awards with a value of zero are filtered out of the calculations to avoid incorrect tie breaks.
"""
Model = get_model()
scores = (db.session.query(
Solves.account_id.label("account_id"),
db.func.sum(Challenges.value).label("score"),
db.func.max(Solves.id).label("id"),
db.func.max(Solves.date).label("date"),
).join(Challenges).filter(Challenges.value != 0).group_by(
Solves.account_id))
awards = (db.session.query(
Awards.account_id.label("account_id"),
db.func.sum(Awards.value).label("score"),
db.func.max(Awards.id).label("id"),
db.func.max(Awards.date).label("date"),
).filter(Awards.value != 0).group_by(Awards.account_id))
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = get_config("freeze")
if not admin and freeze:
scores = scores.filter(Solves.date < unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
results = union_all(scores, awards).alias("results")
"""
Sum each of the results by the team id to get their score.
"""
sumscores = (db.session.query(
results.columns.account_id,
db.func.sum(results.columns.score).label("score"),
db.func.max(results.columns.id).label("id"),
db.func.max(results.columns.date).label("date"),
).group_by(results.columns.account_id).subquery())
"""
Admins can see scores for all users but the public cannot see banned users.
Filters out banned users.
Properly resolves value ties by ID.
Different databases treat time precision differently so resolve by the row ID instead.
"""
if admin:
standings_query = (db.session.query(
Model.id.label("account_id"),
Model.name.label("name"),
Model.hidden,
Model.banned,
sumscores.columns.score,
).join(sumscores, Model.id == sumscores.columns.account_id).order_by(
sumscores.columns.score.desc(), sumscores.columns.id))
else:
standings_query = (db.session.query(
Model.id.label("account_id"),
Model.name.label("name"),
sumscores.columns.score,
).join(sumscores, Model.id == sumscores.columns.account_id).filter(
Model.banned == False,
Model.hidden == False).order_by(sumscores.columns.score.desc(),
sumscores.columns.id))
"""
Only select a certain amount of users if asked.
"""
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
return standings
def get_place(self, admin=False, numeric=False):
"""
This method is generally a clone of CTFd.scoreboard.get_standings.
The point being that models.py must be self-reliant and have little
to no imports within the CTFd application as importing from the
application itself will result in a circular import.
"""
scores = (
db.session.query(
Solves.user_id.label("user_id"),
db.func.sum(Challenges.value).label("score"),
db.func.max(Solves.id).label("id"),
db.func.max(Solves.date).label("date"),
)
.join(Challenges)
.filter(Challenges.value != 0)
.group_by(Solves.user_id)
)
awards = (
db.session.query(
Awards.user_id.label("user_id"),
db.func.sum(Awards.value).label("score"),
db.func.max(Awards.id).label("id"),
db.func.max(Awards.date).label("date"),
)
.filter(Awards.value != 0)
.group_by(Awards.user_id)
)
rfp_scores = (
db.session.query(
RFP.account_id.label("account_id"),
db.func.sum(RFP.score).label("score"),
db.func.max(RFP.id).label("id"),
db.func.max(RFP.date).label("date"),
)
.filter(RFP.score != 0)
.group_by(RFP.account_id)
)
if not admin:
freeze = Configs.query.filter_by(key="freeze").first()
if freeze and freeze.value:
freeze = int(freeze.value)
freeze = datetime.datetime.utcfromtimestamp(freeze)
scores = scores.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
results = union_all(scores, awards, rfp_scores).alias("results")
sumscores = (
db.session.query(
results.columns.user_id,
db.func.sum(results.columns.score).label("score"),
db.func.max(results.columns.id).label("id"),
db.func.max(results.columns.date).label("date"),
)
.group_by(results.columns.user_id)
.subquery()
)
if admin:
standings_query = (
db.session.query(Users.id.label("user_id"))
.join(sumscores, Users.id == sumscores.columns.user_id)
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
)
else:
standings_query = (
db.session.query(Users.id.label("user_id"))
.join(sumscores, Users.id == sumscores.columns.user_id)
.filter(Users.banned == False, Users.hidden == False)
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
)
standings = standings_query.all()
# http://codegolf.stackexchange.com/a/4712
try:
i = standings.index((self.id,)) + 1
if numeric:
return i
else:
k = i % 10
return "%d%s" % (i, "tsnrhtdd"[(i / 10 % 10 != 1) * (k < 4) * k :: 4])
except ValueError:
return 0
