def GetInvoiceTotals(self, tenantId, param=None):
"""
Calculates invoice totals, amounts, due, etc.,
"""
if tenantId:
query = DBSession.query(
func.count(Order.Id).label("Count"),
func.ifnull(func.sum(Order.OrderAmount), 0).label("TotalAmount"),
func.ifnull(
func.sum(func.IF(Order.PaidAmount >= Order.OrderAmount, Order.OrderAmount, Order.PaidAmount)), 0
).label("PaidAmount"),
)
query = query.filter(Order.TenantId == tenantId, Order.Status == True)
if param:
query = self.applySearchParam(query, param)
totals = query.first()
if totals:
oq = query.filter((Order.OrderAmount - Order.PaidAmount) > 0.5, Order.DueDate < func.now()).subquery()
totals.Overdues = DBSession.query(
oq.c.Count, (oq.c.TotalAmount - oq.c.PaidAmount).label("OverdueAmount")
).first()
return totals
return None
def overview():
q = Query(CsdcContestant)
sc = onetimescorecard().subquery()
q = q.outerjoin(sc, CsdcContestant.player_id == sc.c.player_id)
totalcols = []
wktotal = []
wkbonuses = []
for col in ("fifteenrune", "sub40k", "zig", "lowxlzot", "nolairwin", "asceticrune"):
totalcols.append(func.ifnull(getattr(sc.c, col), 0))
q = q.add_column(getattr(sc.c, col).label(col))
for wk in weeks:
a = wk.sortedscorecard().subquery()
totalcols.append(func.ifnull(a.c.total, 0))
wktotal.append(a.c.total)
wkbonuses.append(func.ifnull(a.c.bonusone, 0) + func.ifnull(a.c.bonustwo, 0))
q = q.outerjoin(a, CsdcContestant.player_id == a.c.player_id
).add_column( a.c.total.label("wk" + wk.number))
return q.add_columns(
sc.c.account_id.label("account_id"),
sum(totalcols).label("grandtotal"),
sum(wkbonuses).label("tiebreak"),
sc.c.hiscore.label("hiscore"),
(func.coalesce(*wktotal) != None).label("played")
).order_by(desc("grandtotal"),desc("tiebreak"),desc("hiscore"),desc("played"))
def get_updates(self, session, params):
"""
Get all updates by the specified filters
Status filter is specially treaten
in this order of priority: Update -> Update Type
"""
try:
# Defining subqueries
installed_targets = (
session.query(Target.update_id, func.sum(Target.is_installed).label("total_installed"))
.group_by(Target.update_id)
.subquery()
)
all_targets = (
session.query(Target.update_id, func.count("*").label("total_targets"))
.group_by(Target.update_id)
.subquery()
)
# Main query
query = (
session.query(
Update,
func.ifnull(installed_targets.c.total_installed, 0).label("total_installed"),
func.ifnull(all_targets.c.total_targets, 0).label("total_targets"),
)
.join(Update.update_type)
.outerjoin(installed_targets, Update.id == installed_targets.c.update_id)
.outerjoin(all_targets, Update.id == all_targets.c.update_id)
)
# ==== STATUS FILTERING ======================
if "filters" in params and "status" in params["filters"]:
# Special filter treatment for status
Status = int(params["filters"]["status"])
del params["filters"]["status"]
# other non-confusing filters
# are automatically treaten by @DatabaseHelper._listinfo
if "hide_installed_update" in params:
if params["hide_installed_update"]:
query = query.filter(installed_targets.c.total_installed != all_targets.c.total_targets)
if Status == STATUS_NEUTRAL:
# Neutral status
query = query.filter((Update.status == Status) & (UpdateType.status == Status))
else:
# Dominant status
query = query.filter(
(Update.status == Status) | ((Update.status == STATUS_NEUTRAL) & (UpdateType.status == Status))
)
#
# ==== END STATUS FILTERING ==================
return query
except Exception as e:
logger.error("DB Error: %s" % str(e))
return False
def subquery_builder(lsams, db_class, db):
l = lsams
lis = [getattr(db_class, x) for x in l]
lis_af = [func.ifnull(x, 0) for x in lis]
lis_an = [func.ifnull((x + 1) / (x + 1), 0) for x in lis]
all_the_sum_together = functools.reduce(lambda a, b: a + b, lis_af)
all_the_an_together = functools.reduce(lambda a, b: a + b, lis_an) * 2
all_the_af_together = all_the_sum_together / (all_the_an_together)
dquery = db.session.query(db_class.p_id,
all_the_sum_together.label('CURRENT_AC'),
all_the_af_together.label('CURRENT_AF'),
all_the_an_together.label("CURRENT_AN"))
return (dquery.subquery())
def ResultQuery(run_id, max_time=None, time=None, include_absent=False, results_from_id=None):
if max_time == None and time == None:
time, max_time = ServerCache().Get(('run_times', run_id), lambda: GetRunTimes(run_id))
run = Run.get_by(id=run_id)
if run:
r = session.query()
team = alias(select([Team.table], Team.table.c.present.op('&')(1 << (run.day - 1))), alias="team")
sort = alias(select([Sort.table], Sort.table.c.run_id==run_id), alias="sort")
breed = alias(select([Breed.table]), alias="breed")
res = alias(select([Result.table], Result.table.c.run_id==run_id), alias="result")
r = r.add_entity(Team, alias=team)
if results_from_id:
res_from = alias(select([Result.table], Result.table.c.run_id==results_from_id), alias="result_from")
r = r.add_entity(Result, alias=res_from)
r = r.add_entity(Result, alias=res)
r = r.outerjoin(res).outerjoin(res_from)
else:
r = r.add_entity(Result, alias=res)
r = r.outerjoin(res)
r = r.add_entity(Breed, alias=breed)
r = r.add_entity(Sort, alias=sort)
r = r.outerjoin(sort).outerjoin(breed)
r = r.add_columns((team.c.handler_name + ' ' + team.c.handler_surname).label("team_handler"))
r = r.add_columns((team.c.dog_name + ' ' + team.c.dog_kennel).label("team_dog"))
r = r.add_columns((res.c.mistakes*5 + res.c.refusals*5).label("penalty"))
r = r.add_columns(((res.c.time - time)*(res.c.time > time)).label("time_penalty"))
r = r.add_columns(((res.c.time - time)*(res.c.time > time) + res.c.mistakes*5 + res.c.refusals*5).label("total_penalty"))
r = r.add_columns(func.ifnull(run.length/res.c.time, 0).label('speed'))
disq = (res.c.time > max_time) | (res.c.disqualified) | (res.c.refusals >= 3)
if include_absent:
disq = disq | (res.c.time == 0)
r = r.add_columns(disq.label("disq"))
if run.variant == 0:
r = r.filter(team.c.category == run.category)
else:
r = r.filter(team.c.category != 3)
s = ((func.ifnull(sort.c.value, 0) == 0) & (team.c.def_sort == 1)) | ((func.ifnull(sort.c.value, 0) == 3) & (team.c.def_sort == 0))
r = r.filter((s != 1) & ((res.c.time > 0) | 'disq'))
r = r.order_by("disq, total_penalty, penalty, result_time")
return r, {'team': team, 'result': res, 'sort': sort}
else:
return None, None
def username(username):
workstations = db.session.query(models.Workstation, models.History). \
filter(models.User.id == models.History.user_id). \
filter(models.Workstation.id == models.History.workstation_id). \
group_by(models.Workstation.name).distinct(models.Workstation.name). \
filter(models.User.name == username).all()
servers = db.session.query(models.Server, models.History). \
filter(models.User.id == models.History.user_id). \
filter(models.Updates.id == models.History.update_id). \
filter(models.Server.id == models.Updates.server_id). \
filter(models.User.name == username). \
group_by(models.Server.name).distinct(models.Server.name).all()
products = db.session.query(models.Product.common_name, models.Product.type, models.History.time_in,
func.sum(func.julianday(func.ifnull(models.History.calculated_timein,
datetime.datetime.now())) - func.julianday(
models.History.time_out)).label('time_sum')). \
filter(models.User.id == models.History.user_id). \
filter(models.User.name == username). \
filter(models.History.product_id == models.Product.id). \
group_by(models.Product.common_name).distinct(models.Product.common_name).all()
return render_template('pages/username.html',
workstations=workstations,
servers=servers,
products=products)
def productname(servername, productname, days=3):
users = db.session.query(models.User.name, models.History.time_in,
func.sum(func.julianday(func.ifnull(models.History.calculated_timein,
datetime.datetime.now())) - func.julianday(
models.History.time_out)).label('time_sum')). \
filter(models.User.id == models.History.user_id). \
filter(models.History.product_id == models.Product.id). \
filter(models.Product.common_name == productname). \
distinct(models.User.name).group_by(models.User.name).all()
days = datetime.datetime.utcnow() - datetime.timedelta(days=days)
chart_data = db.session.query(func.count(models.History.user_id).label('users'), models.Product.license_total,
extract('month', models.History.time_out).label('m'),
extract('day', models.History.time_out).label('d'),
extract('year', models.History.time_out).label('y')). \
filter(models.Product.id == models.History.product_id). \
filter(models.Server.id == models.Updates.server_id). \
filter(models.Updates.id == models.History.update_id). \
filter(models.Server.name == servername). \
filter(models.History.time_out > days). \
filter(models.Product.common_name == productname). \
distinct(models.History.user_id). \
group_by(models.Product.common_name, models.Server.name, 'm', 'd', 'y'). \
order_by(desc('y')).order_by(desc('m')).order_by(desc('d')).all()
info = db.session.query(models.Product). \
filter(models.Server.id == models.Product.server_id). \
filter(models.Server.name == servername). \
filter(models.Product.common_name == productname).first()
return render_template('pages/productname.html',
users=users,
chart_data=chart_data,
info=info)
def shows(self, user_id):
with new_session() as session:
now_ = datetime.utcnow()
episodes = session.query(
models.Episode.show_id.label('show_id'),
func.min(models.Episode.number).label('episode_number'),
).filter(
models.Show_fan.user_id == user_id,
models.Episode.show_id == models.Show_fan.show_id,
models.Episode.air_date > (now_-timedelta(days=7)).date(),
func.addtime(
models.Episode.air_date,
func.ifnull(models.Episode.air_time, '00:00:00'),
) < datetime.utcnow(),
).group_by(models.Episode.show_id).subquery()
p = session.query(models.Show, models.Episode).filter(
models.Show.id == episodes.c.show_id,
models.Episode.show_id == models.Show.id,
models.Episode.number == episodes.c.episode_number,
).order_by(
desc(models.Episode.air_date),
models.Episode.show_id,
).paginate(page=self.page, per_page=self.per_page)
p.records = [{
'show': r.Show.serialize(),
'episode': r.Episode.serialize()
} for r in p.records]
return p
def workstationname(workstationname):
users = db.session.query(User.name, History.time_in). \
filter(User.id == History.user_id). \
filter(Workstation.id == History.workstation_id). \
group_by(User.name).distinct(User.name). \
filter(Workstation.name == workstationname).all()
servers = db.session.query(Server, History.time_in). \
filter(Workstation.id == History.workstation_id). \
filter(Updates.id == History.update_id). \
filter(Server.id == Updates.server_id). \
filter(Workstation.name == workstationname). \
group_by(Server.name).distinct(Server.name).all()
products = db.session.query(Product.common_name, Product.type, History.time_in,
func.sum(func.julianday(func.ifnull(History.calculated_timein,
datetime.datetime.now())) - func.julianday(
History.time_out)).label('time_sum')). \
filter(Workstation.id == History.workstation_id). \
filter(Workstation.name == workstationname). \
filter(History.product_id == Product.id). \
group_by(Product.common_name).distinct(Product.common_name).all()
return render_template('pages/workstationname.html',
users=users,
servers=servers,
products=products)
def SearchOrders(self, searchParam):
"""
Searchs the order from the given parameters
Searchable Params:
TenantId (mandatory)
UserId
IpAddress
OrderNo
CustomerId
CustomerName
FromOrderDate
ToOrderDate
MinAmount
MaxAmount
PageNo (default=0)
PageSize (default=50)
"""
if not searchParam or not searchParam.TenantId:
return None
query = DBSession.query(Order)
if searchParam.NotEmpty:
query = query.filter(Order.LineItemsCount > 0)
query = query.filter(Order.TenantId == searchParam.TenantId)
query = query.filter(Order.Status == True)
query = self.formQueryFromParam(query, searchParam)
if not searchParam.PageNo:
searchParam.PageNo = 0
if not searchParam.PageSize and searchParam.PageSize <= 0:
searchParam.PageSize = 50
query = query.order_by(desc(Order.OrderDate))
orders = query.limit(searchParam.PageSize).offset(searchParam.PageNo).all()
if not searchParam.LoadStats:
return orders
smt = query.subquery()
tquery = DBSession.query(func.count(smt.c.Id).label('ItemsCount'), \
func.ifnull(func.sum(smt.c.OrderAmount),0).label('TotalAmount'),\
func.ifnull(func.sum(func.IF(smt.c.PaidAmount>=smt.c.OrderAmount,smt.c.OrderAmount,smt.c.PaidAmount)),0).label('TotalPaidAmount'))
return orders, tquery.first()
def getPubAndAuthorsAndDocsOffset(offset, limit):
"""
Get a chunk of publications with corresponding authors and documents starting at 'offset' and using 'limit' publications
@param offset: database table offset
@type offset: int
@param limit: number of publications to fetch from offset
@type limit: int
@return: dict enumerating the publications
@rtype: dict
"""
# default is 1024 and too small for this query
db.session.execute("SET SESSION group_concat_max_len = 100000")
result = {i: {**r[0].to_dict(),
'authors': uniquifySorted([{'id': a,
'forename': r[2].split(',')[j] if r[2] is not None else '',
'surname': r[3].split(',')[j] if r[3] is not None else '',
'cleanname': r[4].split(',')[j] if r[4] is not None else ''
} for j, a in enumerate(r[1].split(',') if r[1] is not None else [])]),
'documents': uniquify(
[
{
'id': d,
'publication_id': r[6].split(';')[j] if r[6] is not None else '',
'visible': r[7].split(';')[j] if r[7] is not None else '',
'remote': r[8].split(';')[j] if r[8] is not None else '',
'filename': r[9].split(';')[j] if r[9] is not None else ''
} for j, d in enumerate(r[5].split(';') if r[5] is not None else [])
])
}
for i, r in enumerate(db.session.query(
Publications,
func.group_concat(func.ifnull(Authors.id, '').op("ORDER BY")(Authors_publications.position)),
func.group_concat(func.ifnull(Authors.forename, '').op("ORDER BY")(Authors_publications.position)),
func.group_concat(func.ifnull(Authors.surname, '').op("ORDER BY")(Authors_publications.position)),
func.group_concat(func.ifnull(Authors.cleanname, '').op("ORDER BY")(Authors_publications.position)).label('authors'),
func.group_concat(func.ifnull(Documents.id, '').op('SEPARATOR')(literal_column('\';\''))),
func.group_concat(func.ifnull(Documents.publication_id, '').op('SEPARATOR')(literal_column('\';\''))),
func.group_concat(func.ifnull(Documents.visible, '').op('SEPARATOR')(literal_column('\';\''))),
func.group_concat(func.ifnull(Documents.remote, '').op('SEPARATOR')(literal_column('\';\''))),
func.group_concat(func.ifnull(Documents.filename, '').op('SEPARATOR')(literal_column('\';\'')))
)\
.outerjoin(Documents, Documents.publication_id == Publications.id)\
.filter(Publications.id == Authors_publications.publication_id) \
.filter(Authors.id == Authors_publications.author_id) \
.group_by(Publications.id)\
.order_by(Publications.year.desc(), Publications.id.desc())
.offset(offset)
.limit(limit))}
db.session.close()
return result
def GetPurchaseTotals(self, tenantId, param=None):
"""
Calculates invoice totals, amounts, due, etc.,
"""
if tenantId:
a = DBSession.query(
PurchaseLineItem.PurchaseId,
func.sum(PurchaseLineItem.BuyPrice * PurchaseLineItem.Quantity).label("PurchaseAmount"),
)
a = (
a.join(Purchase, Purchase.Id == PurchaseLineItem.PurchaseId)
.group_by(PurchaseLineItem.PurchaseId)
.subquery()
)
b = DBSession.query(PurchasePayment.PurchaseId, func.sum(PurchasePayment.PaidAmount).label("PaidAmount"))
b = (
b.join(Purchase, Purchase.Id == PurchasePayment.PurchaseId)
.group_by(PurchasePayment.PurchaseId)
.subquery()
)
query = DBSession.query(
func.count(Purchase.Id).label("Count"),
func.ifnull(func.sum(a.c.PurchaseAmount), 0).label("TotalAmount"),
func.ifnull(
func.sum(func.IF(b.c.PaidAmount >= a.c.PurchaseAmount, a.c.PurchaseAmount, b.c.PaidAmount)), 0
).label("PaidAmount"),
)
query = query.outerjoin(a, a.c.PurchaseId == Purchase.Id).outerjoin(b, b.c.PurchaseId == Purchase.Id)
query = query.filter(Purchase.TenantId == tenantId, Purchase.Status == True)
if param:
query = self.applyPurchaseSearchParam(query, a, b, param)
totals = query.first()
if totals:
oq = query.filter(a.c.PurchaseAmount > b.c.PaidAmount, Purchase.DueDate < func.now()).subquery()
totals.Overdues = DBSession.query(
oq.c.Count, (oq.c.TotalAmount - oq.c.PaidAmount).label("OverdueAmount")
).first()
return totals
return None
def getPubAndAuthorsAndDocs():
"""
Get a dict containing all publications + their authors and documents enumerated from 0 to n-1 as dict,
e.g. {1: pub_dict_1, 2: pub_dict_2}.
@return: A dict enumerating the publications
@rtype: dict
"""
# default is 1024 and too small for this query
db.session.execute("SET SESSION group_concat_max_len = 100000")
result = {i: {**r[0].to_dict(),
'authors': uniquifySorted([{'id': a,
'forename': r[2].split(',')[j] if r[2] is not None else '',
'surname': r[3].split(',')[j] if r[3] is not None else '',
'cleanname': r[4].split(',')[j] if r[4] is not None else ''
} for j, a in enumerate(r[1].split(',') if r[1] is not None else [])]),
'documents': uniquify(
[
{
'id': d,
'publication_id': r[6].split(';')[j] if r[6] is not None else '',
'visible': r[7].split(';')[j] if r[7] is not None else '',
'remote': r[8].split(';')[j] if r[8] is not None else '',
'filename': r[9].split(';')[j] if r[9] is not None else ''
} for j, d in enumerate(r[5].split(';') if r[5] != None else [])
])
}
for i, r in enumerate(db.session.query(
Publications,
func.group_concat(func.ifnull(Authors.id, '').op("ORDER BY")(Authors_publications.position)),
func.group_concat(func.ifnull(Authors.forename, '').op("ORDER BY")(Authors_publications.position)),
func.group_concat(func.ifnull(Authors.surname, '').op("ORDER BY")(Authors_publications.position)),
func.group_concat(func.ifnull(Authors.cleanname, '').op("ORDER BY")(Authors_publications.position)).label('authors'),
func.group_concat(func.ifnull(Documents.id, '').op('SEPARATOR')(literal_column('\';\''))),
func.group_concat(func.ifnull(Documents.publication_id, '').op('SEPARATOR')(literal_column('\';\''))),
func.group_concat(func.ifnull(Documents.visible, '').op('SEPARATOR')(literal_column('\';\''))),
func.group_concat(func.ifnull(Documents.remote, '').op('SEPARATOR')(literal_column('\';\''))),
func.group_concat(func.ifnull(Documents.filename, '').op('SEPARATOR')(literal_column('\';\'')))
)\
.outerjoin(Documents, Documents.publication_id == Publications.id)\
#.filter(Documents.publication_id == Publications.id )\
.filter(Publications.id == Authors_publications.publication_id) \
.filter(Authors.id == Authors_publications.author_id) \
#.filter(Documents.visible == 1 or Documents.visible == None) \
.group_by(Publications.id)\
.order_by(Publications.year.desc(), Publications.id.desc())
.all())}
db.session.close()
return result
def reserveProductsWithProdOrderNr(ProductionOrderNr, session):
"""Reservierung von Produkten mit ProductionOrderNr."""
stock = session.query(func.ifnull(func.sum(StockEntry.quantity), 0), StockEntry.fkmaterials,
StockEntry.fkplaces). \
filter(StockEntry.productionOrderNr == ProductionOrderNr). \
group_by(StockEntry.fkmaterials, StockEntry.fkplaces). \
having(func.ifnull(func.sum(StockEntry.quantity), 0) > 0).all()
if stock is None:
# Der Produktionsauftrag existiert nicht (mehr)
return None, 'Der Produktionsauftrag ' + ProductionOrderNr + ' existiert nicht.'
# Anlegen des Reservierungskopfes
new_goodsOrder = GoodsOrder(fkmaterials=stock[0][1])
session.add(new_goodsOrder)
session.commit()
fkgoodsOrders = new_goodsOrder.idgoodsOrders
for pos in stock:
reserved_stock = session.query(func.ifnull(func.sum(GoodsOrderPosition.quantity), 0)). \
filter(
(GoodsOrderPosition.productionOrderNr == ProductionOrderNr) & (GoodsOrderPosition.fkplaces == pos[2]) &
((GoodsOrderPosition.done != 1) | (GoodsOrderPosition.done.is_(None)))). \
group_by(GoodsOrderPosition.productionOrderNr).first()
if reserved_stock is not None:
# Berechnung des nicht reservierten Bestands
not_reserved_stock = pos[0] - reserved_stock[0]
else:
not_reserved_stock = pos[0]
if not_reserved_stock <= 0:
# Der Produktionauftrag wurde vollständig reserviert
return None, 'Der Produktionsauftrag ' + ProductionOrderNr + ' wurde bereits vollständig reserviert.'
# Buchen der Reservierung
new_goodsOrderPos = GoodsOrderPosition(
fkgoodsOrders=fkgoodsOrders,
productionOrderNr=ProductionOrderNr,
quantity=not_reserved_stock,
fkplaces=pos[2])
session.add(new_goodsOrderPos)
return new_goodsOrder, ''
def get_updates(self, session, params):
"""
Get all updates by the specified filters
Status filter is specially treaten
in this order of priority: Update -> Update Type
"""
#.add_column(func.count(Target))\
try:
# Defining subqueries
installed_targets = session.query(
Target.update_id,
func.sum(
Target.is_installed).label('total_installed')).group_by(
Target.update_id).subquery()
all_targets = session.query(
Target.update_id,
func.count('*').label('total_targets')).group_by(
Target.update_id).subquery()
# Main query
query = session.query(Update, func.ifnull(installed_targets.c.total_installed, 0).label('total_installed'), func.ifnull(all_targets.c.total_targets, 0).label('total_targets'))\
.join(Update.update_type)\
.outerjoin(installed_targets, Update.id == installed_targets.c.update_id)\
.outerjoin(all_targets, Update.id == all_targets.c.update_id)
# ==== STATUS FILTERING ======================
if 'filters' in params and 'status' in params['filters']:
# Special filter treatment for status
Status = params['filters']['status']
del params['filters']['status']
# other non-confusing filters
# are automatically treaten by @DatabaseHelper._listinfo
if Status == STATUS_NEUTRAL:
# Neutral status
query = query.filter(\
(Update.status == Status) & \
(UpdateType.status == Status)\
)
else:
# Dominant status
query = query.filter(\
(Update.status == Status)| \
(\
(Update.status == STATUS_NEUTRAL) &\
(UpdateType.status == Status)\
)\
)
#
# ==== END STATUS FILTERING ==================
return query
except Exception, e:
logger.error("DB Error: %s" % str(e))
return False
def _sub40k(self):
with get_session() as s:
ktyp_id = get_ktyp(s, "winning").id
return type_coerce(func.ifnull(and_(
Game.ktyp_id == ktyp_id,
Game.end <= self.end,
~self._valid_milestone().filter(
Milestone.turn >= 40000).exists()
), 0), Integer)
def workstations():
all_ws = db.session.query(Workstation.name, History.time_in,
func.sum(func.julianday(func.ifnull(History.calculated_timein,
datetime.datetime.now())) - func.julianday(
History.time_out)).label('time_sum')). \
filter(Workstation.id == History.workstation_id). \
filter(History.product_id == Product.id). \
filter(Product.type == 'core'). \
distinct(Workstation.name).group_by(Workstation.name).all()
return render_template('pages/workstations.html', ws=all_ws)
def __rank_1(self):
if not self.datasources.files.exists('ranking', 'rank_1', 'rank_1',
'csv'):
active_users = self.datasources.files\
.read('ranking', 'get_active_users', 'active_users', 'csv').index.tolist()
with self.datasources.database.session_scope() as session:
rank = pd.read_sql(session.query(User.id, User.user_name, (
func.ifnull(
func.sum(1 / UserCommunity.indegree_centrality), 1) +
func.ifnull(func.sum(UserContext.topical_focus), 0)
).label('rank')).join(UserCommunity).join(UserContext).filter(
User.id.in_(active_users)).group_by(
UserCommunity.user_id).order_by(
desc('rank'), User.user_name.asc()).statement,
con=session.bind).round(decimals=3)
self.datasources.files.write(rank.set_index('id', drop=True),
'ranking', 'rank_1', 'rank_1', 'csv')
def users():
all_users = db.session.query(models.User.name, models.History.time_in,
func.sum(func.julianday(func.ifnull(models.History.calculated_timein,
datetime.datetime.now())) - func.julianday(
models.History.time_out)).label('time_sum')). \
filter(models.User.id == models.History.user_id). \
filter(models.History.product_id == models.Product.id). \
filter(models.Product.type == 'core'). \
distinct(models.User.name).group_by(models.User.name).all()
return render_template('pages/users.html', users=all_users)
def get_attempted_problems(self, username, types):
conds = [Problem_DB.types.contains(_type) for _type in types]
sub_q = session.query(Submission_DB, func.max(Submission_DB.points))\
.filter(Submission_DB._user == username)\
.group_by(Submission_DB._code).subquery()
q = session.query(Problem_DB)\
.join(sub_q, Problem_DB.code == sub_q.c._code, isouter=True)\
.filter(func.ifnull(sub_q.c.points, 0) != 0)\
.filter(or_(*conds))
return q.all()
def getAnswerSummary(lectureId, student):
"""Fetch answerSummary row for student"""
try:
dbAnsSummary = (Session.query(
db.AnswerSummary).with_lockmode('update').filter(
db.AnswerSummary.lectureId == lectureId).filter(
db.AnswerSummary.studentId == student.studentId).one())
except NoResultFound:
dbAnsSummary = db.AnswerSummary(
lectureId=lectureId,
studentId=student.studentId,
grade=0,
)
Session.add(dbAnsSummary)
# Update based on answer table
(
dbAnsSummary.lecAnswered,
dbAnsSummary.lecCorrect,
dbAnsSummary.practiceAnswered,
dbAnsSummary.practiceCorrect,
maxTimeEnd,
) = Session.query(
func.count(),
func.ifnull(func.sum(db.Answer.correct), 0),
func.ifnull(func.sum(db.Answer.practice), 0),
func.ifnull(
func.sum(
expression.case([(db.Answer.practice & db.Answer.correct, 1)],
else_=0)), 0),
func.max(db.Answer.timeEnd),
).filter(db.Answer.lectureId == lectureId).filter(
db.Answer.studentId == student.studentId).one()
dbAnsSummary.lecAnswered = int(dbAnsSummary.lecAnswered)
dbAnsSummary.lecCorrect = int(dbAnsSummary.lecCorrect)
dbAnsSummary.practiceAnswered = int(dbAnsSummary.practiceAnswered)
dbAnsSummary.practiceCorrect = int(dbAnsSummary.practiceCorrect)
if not maxTimeEnd:
maxTimeEnd = datetime.datetime.utcfromtimestamp(0)
return (dbAnsSummary, maxTimeEnd)
def overview():
q = Query(CsdcContestant)
totalcols = []
for wk in weeks:
a = wk.scorecard().subquery()
totalcols.append(func.ifnull(a.c.total, 0))
q = q.outerjoin(a,
CsdcContestant.player_id == a.c.player_id).add_column(
a.c.total.label("wk" + wk.number))
return q.add_column(sum(totalcols).label("grandtotal")).order_by(
desc("grandtotal"))
def get_unsolved_problems(self, username, types, low=1, high=50):
conds = [Problem_DB.types.contains(_type) for _type in types]
sub_q = session.query(Submission_DB, func.max(Submission_DB.points))\
.filter(Submission_DB._user == username)\
.group_by(Submission_DB._code).subquery()
q = session.query(Problem_DB)\
.join(sub_q, Problem_DB.code == sub_q.c._code, isouter=True)\
.filter(func.ifnull(sub_q.c.points, 0) < Problem_DB.points)\
.filter(or_(*conds))\
.filter(Problem_DB.points.between(low, high))\
.filter(Problem_DB.is_organization_private == 0)
return q.all()
def GetProductStock(self, tenantId, minStock=1000, productIds=None):
if not tenantId:
return None
lsb = (
DBSession.query(LineItem.ProductId, func.sum(LineItem.Quantity).label("Sold"))
.group_by(LineItem.ProductId)
.subquery()
)
plsb = (
DBSession.query(PurchaseLineItem.ProductId, func.sum(PurchaseLineItem.Quantity).label("Bought"))
.group_by(PurchaseLineItem.ProductId)
.subquery()
)
smt = DBSession.query(
Product.SupplierId,
Supplier.Name.label("SupplierName"),
Product.Id,
Product.Name,
Product.Barcode,
Product.MRP,
(func.ifnull(plsb.c.Bought, 0) - func.ifnull(lsb.c.Sold, 0)).label("Stock"),
)
smt = smt.join(Supplier)
smt = smt.outerjoin(lsb, lsb.c.ProductId == Product.Id)
smt = smt.outerjoin(plsb, plsb.c.ProductId == Product.Id)
smt = smt.group_by(Product.Id)
if productIds and len(productIds) > 0:
smt = smt.filter(Product.Id.in_(productIds))
smt = smt.filter(Product.TenantId == tenantId).subquery()
query = DBSession.query(smt)
query = query.filter(smt.c.Stock <= minStock).order_by(smt.c.Stock)
lstItems = query.offset(0).limit(20).all()
return lstItems, query.count()
def getAnswerSummary(lectureId, student):
"""Fetch answerSummary row for student"""
try:
dbAnsSummary = (Session.query(db.AnswerSummary)
.with_lockmode('update')
.filter(db.AnswerSummary.lectureId == lectureId)
.filter(db.AnswerSummary.studentId == student.studentId)
.one())
except NoResultFound:
dbAnsSummary = db.AnswerSummary(
lectureId=lectureId,
studentId=student.studentId,
grade=0,
)
Session.add(dbAnsSummary)
# Update based on answer table
(
dbAnsSummary.lecAnswered,
dbAnsSummary.lecCorrect,
dbAnsSummary.practiceAnswered,
dbAnsSummary.practiceCorrect,
maxTimeEnd,
) = Session.query(
func.count(),
func.ifnull(func.sum(db.Answer.correct), 0),
func.ifnull(func.sum(db.Answer.practice), 0),
func.ifnull(func.sum(expression.case([(db.Answer.practice & db.Answer.correct, 1)], else_=0)), 0),
func.max(db.Answer.timeEnd),
).filter(db.Answer.lectureId == lectureId).filter(db.Answer.studentId == student.studentId).one()
dbAnsSummary.lecAnswered = int(dbAnsSummary.lecAnswered)
dbAnsSummary.lecCorrect = int(dbAnsSummary.lecCorrect)
dbAnsSummary.practiceAnswered = int(dbAnsSummary.practiceAnswered)
dbAnsSummary.practiceCorrect = int(dbAnsSummary.practiceCorrect)
if not maxTimeEnd:
maxTimeEnd = datetime.datetime.utcfromtimestamp(0)
return (dbAnsSummary, maxTimeEnd)
async def vc(self, ctx, *usernames):
"""Suggest a contest"""
usernames = list(usernames)
query = Query()
if usernames == []:
username = query.get_handle(ctx.author.id, ctx.guild.id)
if username:
usernames = [username]
users = await asyncio.gather(*[query.get_user(username)
for username in usernames])
usernames = [user.username for user in users]
for i in range(len(users)):
if users[i] is None:
return await ctx.send(f'{usernames[i]} does not exist on DMOJ')
q = session.query(Contest_DB)
for user in users:
# if the user has attempted any problems from the problem set
sub_q = session.query(Submission_DB,
func.max(Submission_DB.points))\
.filter(Submission_DB._user == user.username)\
.group_by(Submission_DB._code).subquery()
sub_q = session.query(Problem_DB.code)\
.join(sub_q, Problem_DB.code == sub_q.c._code, isouter=True)\
.filter(func.ifnull(sub_q.c.points, 0) != 0)
sub_q = list(map(itemgetter(0), sub_q.all()))
q = q.filter(not_(Contest_DB.rankings.contains(user.username)))\
.filter(~Contest_DB.problems.any(Problem_DB.code.in_(sub_q)))
if q.count() == 0:
await ctx.send("Cannot find any contests which "
"all users have not done")
return
contest = random.choice(q.all())
# When problems are private, it says there are no problems
window = 'No'
is_rated = 'Not Rated'
if contest.time_limit:
window = f"{contest.time_limit/60/60} Hr"
if contest.is_rated:
is_rated = "Rated"
embed = discord.Embed(
title=contest.name, url=f"https://dmoj.ca/contest/{contest.key}",
description=f"{window} window | {len(contest.problems)} Problems | {is_rated}",
color=0xfcdb05
)
await ctx.send(embed=embed)
def get_updates(self, session, params):
"""
Get all updates by the specified filters
Status filter is specially treaten
in this order of priority: Update -> Update Type
"""
#.add_column(func.count(Target))\
try:
# Defining subqueries
installed_targets = session.query(Target.update_id, func.sum(Target.is_installed).label('total_installed')).group_by(Target.update_id).subquery()
all_targets = session.query(Target.update_id, func.count('*').label('total_targets')).group_by(Target.update_id).subquery()
# Main query
query = session.query(Update, func.ifnull(installed_targets.c.total_installed, 0).label('total_installed'), func.ifnull(all_targets.c.total_targets, 0).label('total_targets'))\
.join(Update.update_type)\
.outerjoin(installed_targets, Update.id == installed_targets.c.update_id)\
.outerjoin(all_targets, Update.id == all_targets.c.update_id)
# ==== STATUS FILTERING ======================
if 'filters' in params and 'status' in params['filters']:
# Special filter treatment for status
Status = params['filters']['status']
del params['filters']['status']
# other non-confusing filters
# are automatically treaten by @DatabaseHelper._listinfo
if Status == STATUS_NEUTRAL:
# Neutral status
query = query.filter(\
(Update.status == Status) & \
(UpdateType.status == Status)\
)
else:
# Dominant status
query = query.filter(\
(Update.status == Status)| \
(\
(Update.status == STATUS_NEUTRAL) &\
(UpdateType.status == Status)\
)\
)
#
# ==== END STATUS FILTERING ==================
return query
except Exception, e:
logger.error("DB Error: %s" % str(e))
return False
def get_tweet_counts_for_date(self, for_date=None):
if not for_date:
for_date = datetime.now().date()
session = self.sessionmaker()
tally_subq = session.query(
Tweet.user_id,
func.count(Tweet.user_id).label('tally')). \
filter(func.date(Tweet.date_created) == for_date). \
group_by(Tweet.user_id).subquery()
return session.query(User.user_name,
func.ifnull(tally_subq.c.tally, 0))\
.select_from(User)\
.outerjoin(tally_subq, tally_subq.c.user_id == User.user_id)\
.order_by(User.user_name).all()
def get_extra_charge(self, booking_id):
extra_charge = 0
try:
session = Session()
row = session.query(func.ifnull(func.sum(UserPaymentRecord.price), 0)) \
.filter(UserPaymentRecord.booking_id == booking_id) \
.filter(UserPaymentRecord.status == 'CHARGED') \
.one()
extra_charge = int(row[0]) * 0.8
extra_charge = int(extra_charge)
except Exception, e:
print e
extra_charge = 0
def allInvs(session,
enames=None,
lvls=None,
lvlsets=None,
workers=None,
assignments=None,
enameSet=None,
lvlSet=None,
lvlsetSet=None,
workerSet=None,
assignmentSet=None,
colSwaps=None):
q = session.query(
Event.experiment,
func.json_extract(Event.payload, "$.lvlid"),
func.json_extract(Event.payload, "$.lvlset"),
func.json_extract(Event.payload, "$.workerId"),
func.json_extract(Event.payload, "$.assignmentId"),
func.json_extract(Event.payload, "$.raw"),
func.json_extract(Event.payload, "$.canonical"),
func.ifnull(func.json_extract(Event.payload, "$.colSwap"), 0)
) \
.filter(Event.type == "FoundInvariant")
q = filterEvents(q, enames, lvls, lvlsets, workers, assignments)
def gen():
for row in q.all():
if enameSet is not None:
enameSet.add(row[0])
if lvlSet is not None:
lvlSet.add(row[1])
if lvlsetSet is not None:
lvlsetSet.add(row[2])
if workerSet is not None:
workerSet.add(row[3])
if assignmentSet is not None:
assignmentSet.add(row[4])
if colSwaps is not None:
try:
colSwaps[row[7]] += 1
except KeyError:
colSwaps[row[7]] = 1
yield (row[5], row[6])
return set(dict(gen()).iteritems())
def mispackaged():
# Parameters
requested = request.args.get('requested', None)
if requested not in ('1', None):
abort(400) # Bad request
db = current_app.config['DB']()
query = db.query(tables.Package)
query = query.filter(tables.Package.status == 'mispackaged')
query = query.join(tables.CollectionPackage)
query = query.filter(tables.CollectionPackage.collection_ident == 'fedora')
# Do an outer join with Links, but ONLY with rows of type 'bug' so that if
# a package has only e.g. a 'repo' link, it won't affect the results.
query = query.outerjoin(
tables.Link,
and_(tables.Link.type == 'bug',
tables.Link.collection_package_id == tables.CollectionPackage.id))
# If appropriate: Filter only to packages where maintainer requested a patch
if requested:
query = query.join(tables.TrackingBug)
query = query.filter(
tables.TrackingBug.url ==
"https://bugzilla.redhat.com/show_bug.cgi?id=1333765")
# Order by the last_update field, and if it's null, substitute it with the
# year 9999 so it's very last. (Note: sqlite does not support NULLS LAST)
query = query.order_by(func.ifnull(tables.Link.last_update, '9999'))
# Speedup: Prevent starting subqueries for each package.
query = query.options(subqueryload('collection_packages'))
query = query.options(subqueryload('collection_packages.links'))
query = query.options(subqueryload('collection_packages.tracking_bugs'))
mispackaged = list(query)
# Render the page, pass the data
return render_template(
'mispackaged.html',
breadcrumbs=(
(url_for('hello'), 'Python 3 Porting Database'),
(url_for('mispackaged', requested=1), 'Mispackaged'),
),
requested=bool(requested),
mispackaged=mispackaged,
)
def mispackaged():
# Parameters
requested = request.args.get('requested', None)
if requested not in ('1', None):
abort(400) # Bad request
db = current_app.config['DB']()
query = db.query(tables.Package)
query = query.filter(tables.Package.status == 'mispackaged')
query = query.join(tables.CollectionPackage)
query = query.filter(
tables.CollectionPackage.collection_ident == 'fedora')
# Do an outer join with Links, but ONLY with rows of type 'bug' so that if
# a package has only e.g. a 'repo' link, it won't affect the results.
query = query.outerjoin(tables.Link, and_(tables.Link.type == 'bug',
tables.Link.collection_package_id == tables.CollectionPackage.id))
# If appropriate: Filter only to packages where maintainer requested a patch
if requested:
query = query.join(tables.TrackingBug)
query = query.filter(tables.TrackingBug.url ==
"https://bugzilla.redhat.com/show_bug.cgi?id=1333765")
# Order by the last_update field, and if it's null, substitute it with the
# year 9999 so it's very last. (Note: sqlite does not support NULLS LAST)
query = query.order_by(func.ifnull(tables.Link.last_update, '9999'))
# Speedup: Prevent starting subqueries for each package.
query = query.options(subqueryload('collection_packages'))
query = query.options(subqueryload('collection_packages.links'))
query = query.options(subqueryload('collection_packages.tracking_bugs'))
mispackaged = list(query)
# Render the page, pass the data
return render_template(
'mispackaged.html',
breadcrumbs=(
(url_for('hello'), 'Python 3 Porting Database'),
(url_for('mispackaged'), 'Mispackaged'),
),
requested=bool(requested),
mispackaged=mispackaged,
)
def get_unsolved_problems(self, username, types, low=1, high=50):
# Does not find problems if you first
# +update_problems
# +gimme
# This is cause calling the /problems api does not return is_organization_private
# The original goal of is_organization_private filter is to prevent leaking problems
conds = [Problem_DB.types.contains(_type) for _type in types]
sub_q = session.query(Submission_DB, func.max(Submission_DB.points))\
.filter(Submission_DB._user == username)\
.group_by(Submission_DB._code).subquery()
q = session.query(Problem_DB)\
.join(sub_q, Problem_DB.code == sub_q.c._code, isouter=True)\
.filter(func.ifnull(sub_q.c.points, 0) < Problem_DB.points)\
.filter(or_(*conds))\
.filter(Problem_DB.points.between(low, high))\
.filter(Problem_DB.is_organization_private == 0)\
.filter(Problem_DB.is_public == 1)
return q.all()
def get(self,argv):
s=None
planname=None
try:
planname = self.get_argument("planname",None)
displayrange = self.get_argument("range",'all')
so.userlog.info('received getplans request, planname:'+str(planname)+' range:'+str(displayrange))
so.userlog.debug('open a session for getplans planname:'+str(planname)+' range:'+str(displayrange))
s=so.Session()
planlist=[]
rs=[]
if planname==None:
allres=s.query(Plan,Report).outerjoin(Plan.Report).order_by(desc(func.ifnull(Plan.LastModifyTime,0)))
if displayrange=='all':
rs=allres
else:
displayrange=eval(displayrange)
rs=allres[displayrange[0]:displayrange[1]]
planlist.append(str(allres.count()))
for r in rs:
planprogress=self.getplanstatus(r[0].Name)
print "planprogress:"+str(planprogress)
planlist.append(['$$##'+r[0].Name,'$$##'+r[0].PlanStatus.Name,None if r[0].CreateTime==None else u'$$##'+str(r[0].CreateTime),None if r[0].StartTime==None else u'$$##'+str(r[0].StartTime),None if r[0].EndTime==None else u'$$##'+str(r[0].EndTime),None if r[0].DESC==None else '$$##'+str(r[0].DESC).decode('utf8'),None if r[1]==None else ['$$##'+r[1].Name,'$$##'+r[1].ReportStatus.Name],planprogress])
else:
allres=s.query(Plan,Report).outerjoin(Plan.Report).filter(Plan.Name.like(planname+"%")).order_by(desc(func.ifnull(Plan.LastModifyTime,0)))
if displayrange=='all':
rs=allres
else:
displayrange=eval(displayrange)
rs=allres[displayrange[0]:displayrange[1]]
planlist.append(str(allres.count()))
for r in rs:
planprogress=self.getplanstatus(r[0].Name)
print "planprogress:"+str(planprogress)
planlist.append(['$$##'+r[0].Name,'$$##'+r[0].PlanStatus.Name,None if r[0].CreateTime==None else u'$$##'+str(r[0].CreateTime),None if r[0].StartTime==None else u'$$##'+str(r[0].StartTime),None if r[0].EndTime==None else u'$$##'+str(r[0].EndTime),None if r[0].DESC==None else '$$##'+str(r[0].DESC).decode('utf8'),None if r[1]==None else ['$$##'+r[1].Name,'$$##'+r[1].ReportStatus.Name],planprogress])
s.close()
so.userlog.debug('close session for getplans planname:'+str(planname)+' range:'+str(displayrange))
self.write(str(planlist).replace('None','null').replace("u'$$##","'"))
so.userlog.info('getplans request process success, planname:'+str(planname)+' range:'+str(displayrange))
except Exception,e:
if s!=None:
s.rollback()
s.close()
so.userlog.error('getplans failed,error occured,'+str(traceback.format_exc()))
def GetSquadResults(run_id):
squads = []
run = Run.get_by(id=run_id)
if run:
query, aliases = ResultQuery(run_id, include_absent=True)
squad_list = ServerCache().Get(('squads', run_id), lambda: GetSquads(run_id))
time, max_time = ServerCache().Get(('run_times', run_id), lambda: GetRunTimes(run_id))
if None in squad_list:
squad_list.remove(None)
if "" in squad_list:
squad_list.remove("")
for s in squad_list:
squery = query.filter(func.ifnull(aliases['team'].c.squad, "") == s)
rows = session.execute(squery).fetchall()
results = []
for r in rows:
r = dict(zip(r.keys(), r.values()))
results.append(r)
squad = {"name": s,
"penalty": reduce(lambda x,y: x + y['penalty'], results[0:3], 0),
"time_pen": reduce(lambda x,y: x + y['time_penalty'], results[0:3], 0),
"total_penalty": reduce(lambda x,y: x + y['time_penalty'] + y['penalty'], results[0:3], 0),
"result_time": reduce(lambda x,y: x + y['result_time'], results[0:3], 0),
"disq_count": reduce(lambda x,y: x + y['disq'], results, 0),
"disq": len(results) - reduce(lambda x,y: x + y['disq'], results, 0) < 3,
'members': results
}
squads.append(squad)
squads.sort(key=lambda s: (s['disq']* s['disq_count'], s['total_penalty'], s['penalty'], s['result_time']))
rank = 0
for s in squads:
rank += 1
s['rank'] = rank
return squads
def query_case_when():
# 使用case when的情况很少. 因为 case when就算在sql语句里, 写的也很笨重. 所以, 很少使用.
# 能够替换case when的 语句可能有 mysql里的 ifnull( a,b ) oracle里的 nvl, nvl2等等.
# ifnull(a, b)意思是, 如果a是空的, 那么使用b的值, if(a, b, c) 意思是 如果a是true, 那么使用b, 否则使用c, 是三目运算符.
# 如何使用 ifnull, if 等函数,分别使用 func.ifnull, func.IF(大写), 使用我上面说的 func.函数名 用法. 这里就不多说了.
# 假设一个场景:
# 我们根据员工的收入分等级, 奖金+工资 1500 美元以下的,都是低收入, 1500-3500之前的是 中等收入, 3500美元以上的是高收入
# 包含下限, 不包含上限.
from sqlalchemy import text
income_level = case([
(text('(emp.sal + ifnull(emp.comm,0))<1500'), 'LOW_INCOME'),
(text('1500<=(emp.sal + ifnull(emp.comm,0))<3500'), 'MIDDLE_INCOME'),
(text('(emp.sal + ifnull(emp.comm,0))>=3500'), 'HIGH_INCOME'),
],
else_='UNKNOWN').label('income_level')
emps = sess.query(Emp.ename,
label('income', Emp.sal + func.ifnull(Emp.comm, 0)),
income_level).all()
for item in emps:
print(item.ename, item.income, item.income_level)
'''
def productname(product_name):
users = db.session.query(User.name, History.time_in,
Server.name.label('servername'),
func.sum(func.julianday(func.ifnull(History.calculated_timein,
datetime.datetime.now())) - func.julianday(
History.time_out)).label('time_sum')). \
filter(User.id == History.user_id). \
filter(History.product_id == Product.id). \
filter(Product.server_id == Server.id). \
filter(Product.common_name == product_name). \
distinct(User.name).group_by(User.name).all()
# days = datetime.datetime.utcnow() - datetime.timedelta(days=days)
# chart_data = db.session.query(func.count(History.user_id).label('users'), Product.license_total,
# extract('month', History.time_out).label('m'),
# extract('day', History.time_out).label('d'),
# extract('year', History.time_out).label('y')). \
# filter(Product.id == History.product_id). \
# filter(Server.id == Updates.server_id). \
# filter(Updates.id == History.update_id). \
# filter(Server.name == server_name). \
# filter(Product.common_name == product_name). \
# distinct(History.user_id). \
# group_by(Product.common_name, Server.name, 'm', 'd', 'y'). \
# order_by(desc('y')).order_by(desc('m')).order_by(desc('d')).all()
# filter(History.time_out > days).
# info = db.session.query(Product). \
# filter(Server.id == Product.server_id). \
# filter(Server.name == server_name). \
# filter(Product.common_name == product_name).first()
return render_template(
'pages/productname.html',
users=users,
# chart_data=chart_data,
# info=info
)
def get_article(article_id=None, title_path=None, render=True, released=None):
"""Return an article by it's ID."""
if article_id is None and title_path is None:
raise ValueError("You must specify either an ID or path.")
# Generate the proper where condition
if article_id is not None:
where_cond = (articles.c.id == article_id)
else:
where_cond = (articles.c.title_path == title_path)
# Generate the SQL syntax with SQLAlchemy
stmt = select(
[articles,
func.ifnull(func.group_concat(tags.c.tag, ", "), "").label('tag_list')]
).select_from(
articles.outerjoin(
tag_map,
articles.c.id == tag_map.c.article_id
).outerjoin(
tags,
tag_map.c.tag_id == tags.c.id
)
).where(
where_cond
).where(
articles.c.released == released if released is not None else ""
).group_by(
tag_map.c.article_id
)
# Get our results
conn = engine.connect()
result = conn.execute(stmt)
row = result.fetchone()
article = article_from_row(row, render=render) if row is not None else None
conn.close()
return article
def progress(cls):
return func.round(
(cls.indexes_verified / func.ifnull(cls.hc_keyspace, 0)) * 100)
async def vc(ctx):
usernames = ctx.options.usernames
print(usernames)
query = Query()
if usernames == []:
username = query.get_handle(ctx.author.id, ctx.get_guild().id)
if username:
usernames = [username]
users = await asyncio.gather(
*[query.get_user(username) for username in usernames])
usernames = [user.username for user in users]
for i in range(len(users)):
if users[i] is None:
return await ctx.respond(f"{usernames[i]} does not exist on DMOJ")
q = session.query(Contest_DB)
for user in users:
# if the user has attempted any problems from the problem set
sub_q = (session.query(Submission_DB, func.max(
Submission_DB.points)).filter(
Submission_DB._user == user.username).group_by(
Submission_DB._code).subquery())
sub_q = (session.query(Problem_DB.code).join(
sub_q, Problem_DB.code == sub_q.c._code,
isouter=True).filter(func.ifnull(sub_q.c.points, 0) != 0))
sub_q = list(map(itemgetter(0), sub_q.all()))
q = (q.filter(not_(Contest_DB.rankings.contains(
user.username))).filter(
~Contest_DB.problems.any(Problem_DB.code.in_(sub_q))).filter(
Contest_DB.is_private == 0).filter(
Contest_DB.is_organization_private == 0))
if q.count() == 0:
await ctx.respond("Cannot find any contests which "
"all users have not done")
return
contests = q.all()
while True:
contest = random.choice(contests)
try:
contest = await query.get_contest(contest.key, cached=False)
break
except ObjectNotFound:
pass
# When problems are private, it says there are no problems
window = "No"
is_rated = "Not Rated"
if contest.time_limit:
window = f"{round(contest.time_limit/60/60, 2)} Hr"
if contest.is_rated:
is_rated = "Rated"
embed = hikari.Embed(
title=contest.name,
url=f"https://dmoj.ca/contest/{contest.key}",
description=
f"{window} window | {len(contest.problems)} Problems | {is_rated}",
color=0xFCDB05,
)
await ctx.respond(embed=embed)
def reserveProductsWithArticelNr(fkmaterials, quantity, session):
"""Reservierung von Produkten mit Materialnummer und Menge."""
stock = session.query(func.ifnull(func.sum(StockEntry.quantity), 0), func.min(StockEntry.booking_date),
StockEntry.productionOrderNr, StockEntry.fkmaterials, StockEntry.fkplaces). \
filter((StockEntry.productionOrderNr != '') and (StockEntry.fkmaterials == fkmaterials)). \
group_by(StockEntry.productionOrderNr, StockEntry.fkmaterials, StockEntry.fkplaces). \
having(func.ifnull(func.sum(StockEntry.quantity), 0) > 0). \
order_by(StockEntry.booking_date).all()
if stock is None:
# Problem: Für die Materialnummer gibt es keinen Bestand, daher kann dieser auch nicht ausgeliefert werden.
return None, 'Für den Artikel ' + str(
fkmaterials) + ' gibt es keinen Bestand.'
# Anlegen des Reservierungskopfes
new_goodsOrder = GoodsOrder(fkmaterials=fkmaterials)
session.add(new_goodsOrder)
session.commit()
fkgoodsOrders = new_goodsOrder.idgoodsOrders
# Reservieren der Menge
remaining_quantity = quantity
for pos in stock:
if remaining_quantity <= 0:
break
# Ermittlung des bereits reservierten Bestands
reserved_stock = session.query(func.ifnull(func.sum(GoodsOrderPosition.quantity), 0)). \
filter((GoodsOrderPosition.productionOrderNr == pos[2]) & (GoodsOrderPosition.fkplaces == pos[4]) &
((GoodsOrderPosition.done != 1) | (GoodsOrderPosition.done.is_(None)))). \
group_by(GoodsOrderPosition.productionOrderNr).first()
if reserved_stock is not None:
# Berechnung des nicht reservierten Bestands
not_reserved_stock = pos[0] - reserved_stock[0]
else:
not_reserved_stock = pos[0]
# Der Bestand der ProductionOrderNr ist komplett reserviert. Zur nächsten ProductionOrderNr springen.
if not_reserved_stock <= 0:
continue
# Menge der Reservierung ermitteln:
if not_reserved_stock >= remaining_quantity:
# Der nicht reservierte Bestand ist größer als die Restmenge. -> Die komplette Restmenge bei dieser
# ProductionOrderNr reservieren.
booked_stock = remaining_quantity
elif remaining_quantity > not_reserved_stock:
# Die Restmenge ist größer als der Bestand -> Den kompletten Bestand reservieren
booked_stock = not_reserved_stock
# Buchen der Reservierung
new_goodsOrderPos = GoodsOrderPosition(fkgoodsOrders=fkgoodsOrders,
productionOrderNr=pos[2],
quantity=booked_stock,
fkplaces=pos[4])
session.add(new_goodsOrderPos)
remaining_quantity = remaining_quantity - booked_stock
session.refresh(new_goodsOrder)
if remaining_quantity > 0:
# Fehler: Der Bestand für die Reservierung ist nicht ausreichend!
return new_goodsOrder, 'Der Bestand für den Artikel ' + str(fkmaterials) + \
' ist nicht ausreichend. Insgesamt konnten ' + str(remaining_quantity) + ' Stück nicht gebucht werden.'
return new_goodsOrder, ''
def get_updates_for_group(self, session, params):
"""
Get all updates for a group of hosts by the specified filters,
like get_updates function.
Status filter is specially treaten.
in this order of priority:
Groups -> Update -> Update Type
"""
if 'gid' in params:
gid = params['gid']
if 'uuids' in params:
uuids = params['uuids']
else:
uuids = []
if 'is_install' in params:
is_installed = params['is_installed']
else:
is_installed = None
if 'filters' in params and 'status' in params['filters']:
dStatus = int(params['filters']['status'])
del params['filters']['status']
else:
dStatus = STATUS_NEUTRAL
try:
# Defining subqueries
installed_targets = session.query(
Target.update_id,
func.sum(Target.is_installed).label('total_installed'))
# count only group machines
installed_targets = installed_targets.filter(
Target.uuid.in_(uuids))
installed_targets = installed_targets.group_by(Target.update_id)
installed_targets = installed_targets.subquery()
all_targets = session.query(Target.update_id,
func.count('*').label('total_targets'))
# count only group machines
all_targets = all_targets.filter(Target.uuid.in_(uuids))
all_targets = all_targets.group_by(Target.update_id).subquery()
group = session.query(Groups).filter(Groups.gid == gid).subquery()
query = session.query(
Update,
func.ifnull(installed_targets.c.total_installed,
0).label('total_installed'),
func.ifnull(all_targets.c.total_targets,
0).label('total_targets'),
func.ifnull(group.c.gid, gid).label('gid'),
func.ifnull(group.c.status, 0).label('group_status'),
)
query = query.join(Target)
query = query.join(UpdateType)
# filter on the group of hosts
query = query.filter(Target.uuid.in_(uuids))
# add subqueries
query = query.outerjoin(installed_targets,
Update.id == installed_targets.c.update_id)
query = query.outerjoin(all_targets,
Update.id == all_targets.c.update_id)
query = query.outerjoin(group, Update.id == group.c.update_id)
if is_installed is not None:
query = query.filter(Target.is_installed == is_installed)
# ============================================
# ==== STATUS FILTERING ======================
# ============================================
if 'hide_installed_update' in params:
if params['hide_installed_update']:
query = query.filter(installed_targets.c.total_installed !=
all_targets.c.total_targets)
if dStatus == STATUS_NEUTRAL:
query = query.filter((group.c.status == None)
| (group.c.status == STATUS_NEUTRAL))
query = query.filter(Update.status == STATUS_NEUTRAL)
query = query.filter(UpdateType.status == STATUS_NEUTRAL)
else:
query = query.filter(
# 1st level filtering : Group status
(group.c.status == dStatus)
| (((group.c.status == None) |
(group.c.status == STATUS_NEUTRAL)) & (
# 2nd level filtering : Update status
(Update.status == dStatus) | (
(Update.status == STATUS_NEUTRAL) &
(UpdateType.status == dStatus)))))
# ============================================
# ==== END STATUS FILTERING ==================
# ============================================
return query
except Exception as e:
logger.error("DB Error: %s" % str(e))
return False
class Item(Base):
"""Store Item info"""
__tablename__ = "item"
id = Column(Integer, primary_key=True)
cantidad = Column(Integer)
costo = Column(Float)
#parent
parent_id = Column(Integer, ForeignKey('item_padre.id'))
#procedencia
procedencia_id = Column(Integer, ForeignKey('procedencia.id'))
#precio
precio = relationship('PrecioVenta', backref='item')
#movimientos
movimientos = relationship('ItemMovido', backref="item")
salidas = column_property(
select([func.ifnull(func.sum(ItemMovido.cantidad), 0)]).where(
and_(Movimiento.tipo == TipoMovimiento.SALIDA,
ItemMovido.item_id == id,
ItemMovido.movimiento_id == Movimiento.id)))
devoluciones = column_property(
select([func.ifnull(func.sum(ItemMovido.cantidad), 0)]).where(
and_(Movimiento.tipo == TipoMovimiento.DEVOLUCION,
ItemMovido.item_id == id,
ItemMovido.movimiento_id == Movimiento.id)))
vendidos = column_property(
select([func.ifnull(func.sum(Venta.cantidad), 0)
]).where(and_(Venta.item_id == id, Venta.invalidada is False)))
restantes = column_property(cantidad - salidas + devoluciones - vendidos)
@hybrid_method
def getTracker(self):
engine = db.Engine.instance
with engine.connect() as conn:
sql = """
select im.item_id, cantidad, m.tipo, m.fecha, "MOVIMIENTO" as evento , m.cliente_id, rv.precio from items_movido as im
left join movimiento as m on m.id == im.movimiento_id
left join (select pv.item_id, pv.precio, m.id from precio_venta as pv
left join items_movido im on im.item_id = pv.item_id
left join movimiento as m on m.id == im.movimiento_id and m.fecha between pv.fecha_inicio and ifnull(pv.fecha_final,'2999-12-31'))
as rv on rv.item_id == im.item_id and rv.id == m.id where im.item_id==%d
union
select item_id, cantidad, "NONE", fecha, "VENTA", cliente_id, precio from venta as v where v.item_id==%d
order by fecha
""" % (self.id, self.id)
results = conn.execute(text(sql))
obj_result = []
session = db.session()
for r in results:
o = type('', (), {})()
o.cliente = session.query(Cliente).filter(
Cliente.id == r['cliente_id']).one()
o.cantidad = r['cantidad']
o.tipo = r['evento']
o.valor = r['precio']
o.fecha = datetime.strptime(r['fecha'], '%Y-%m-%d %H:%M:%S.%f')
obj_result.append(o)
return obj_result
@staticmethod
def getItemsByClient(procedencia):
# TODO: Make this from another way. This is for going fast and is a shit
engine = db.Engine.instance
with engine.connect() as conn:
sql = """ select r1.item_id, (ifnull(r1.total_sal,0) - ifnull(r2.total_dev,0) - ifnull(rv.vendido,0)) as tiene, r1.cliente_id
from (select im.item_id, m.tipo, c.nombre, sum(im.cantidad) as total_sal, m.cliente_id from movimiento as m
left join items_movido as im on im.movimiento_id == m.id
left join item as i on i.id == im.item_id
left join cliente as c on c.id == m.cliente_id
where m.tipo = "SALIDA" and i.procedencia_id == %d group by im.item_id, m.tipo,m.cliente_id) as r1
left join (select im.item_id, m.tipo, c.nombre, sum(im.cantidad)as total_dev, m.cliente_id from movimiento as m
left join items_movido as im on im.movimiento_id == m.id left join cliente as c on c.id == m.cliente_id
where m.tipo = "DEVOLUCION" group by im.item_id, m.tipo) as r2 on r1.item_id == r2.item_id and r1.cliente_id == r2.cliente_id
left join item on item.id == r1.item_id
left join (select v.item_id, v.cliente_id, sum(v.cantidad) as vendido from venta as v group by v.item_id, v.cliente_id) as rv
on r1.item_id == rv.item_id and r1.cliente_id == rv.cliente_id
where tiene > 0;
""" % procedencia
results = conn.execute(text(sql))
res = []
items_id = []
clients_id = []
for row in results:
items_id.append(row['item_id'])
clients_id.append(row['cliente_id'])
res.append((row['item_id'], row['cliente_id'], row['tiene']))
session = db.session()
items = session.query(Item).filter(Item.id.in_(items_id)).all()
clientes = session.query(Cliente).filter(
Cliente.id.in_(clients_id)).all()
obj_res = []
for r in res:
o = type('', (), {})()
for i in items:
if i.id == r[0]:
o.item = i
for c in clientes:
if c.id == r[1]:
o.cliente = c
o.tiene = r[2]
obj_res.append(o)
return obj_res
def addPrecio(self, precio):
"""
Agrega un precio a los items, actualiza las fecha de inicio y fin de los precios anteriores
:param precio:
:return:
"""
if isinstance(precio, float) or isinstance(precio, int):
precio = PrecioVenta.newPrecio(precio)
if isinstance(precio, PrecioVenta):
if len(self.precio) > 0:
last = self.precio[-1]
last.fecha_fin = datetime.now()
self.precio.append(precio)
def getPrecioinDate(self, fecha=datetime.date(datetime.now())):
for p in self.precio:
if p.fecha_inicio is None or p.fecha_final is None:
continue
if p.fecha_inicio < fecha < p.fecha_final:
return p
return self.precio[-1]
def __repr__(self):
return "Item<-%s-,'%s'>" % (self.id, self.cantidad)
def get_articles(start=None, page_size=config.PAGE_SIZE, with_body=True,
with_links=False, released=False, render=True,
tag=None, tag_list=False):
"""Return a list of articles."""
by_tag = True if isinstance(tag, str) else False
# Generate the correct list of columns
cols = [articles.c.id,
articles.c.released,
articles.c.title_path,
articles.c.title,
articles.c.date]
if with_body:
cols.append(articles.c.body)
if with_links:
cols.append(articles.c.title_link)
cols.append(articles.c.title_alt)
if tag_list:
cols.append(
func.ifnull(
func.group_concat(tags.c.tag, ", "),
""
).label('tag_list')
)
# Build the statement
stmt = select(cols, offset=start, limit=page_size).where(
articles.c.released == released if released is not None else ""
).order_by(
articles.c.date.desc()
)
# Join the tag map and tag table if either:
# - we want to return tags
# - we are returning all articles with a certain tag
if by_tag or tag_list:
stmt = stmt.select_from(
articles.outerjoin(
tag_map,
articles.c.id == tag_map.c.article_id
).outerjoin(
tags,
tag_map.c.tag_id == tags.c.id
)
).group_by(articles.c.id)
# Limit articles by tag
if by_tag:
stmt = stmt.where(
articles.c.id.in_(
select([tag_map.c.article_id]).select_from(
tag_map.outerjoin(
tags,
tag_map.c.tag_id == tags.c.id
)
).where(
tags.c.tag == tag
)
)
)
# Execute the statement
article_list = []
conn = engine.connect()
for row in conn.execute(stmt):
article = article_from_row(row, render=render)
article_list.append(article)
conn.close()
return article_list
def SearchPurchases(self, param):
if not (param and param.TenantId):
return None
a = DBSession.query(
PurchaseLineItem.PurchaseId,
func.count(PurchaseLineItem.PurchaseId).label("ItemCount"),
func.ROUND(func.sum(PurchaseLineItem.BuyPrice * PurchaseLineItem.Quantity), 2).label("PurchaseAmount"),
)
a = (
a.join(Purchase, Purchase.Id == PurchaseLineItem.PurchaseId)
.group_by(PurchaseLineItem.PurchaseId)
.subquery()
)
b = DBSession.query(
PurchasePayment.PurchaseId, func.ROUND(func.sum(PurchasePayment.PaidAmount), 2).label("PaidAmount")
)
b = b.join(Purchase, Purchase.Id == PurchasePayment.PurchaseId).group_by(PurchasePayment.PurchaseId).subquery()
query = DBSession.query(
Purchase.Id,
Purchase.PurchaseNo,
Purchase.PurchaseDate,
Purchase.DueDate,
Supplier.Name.label("SupplierName"),
a.c.ItemCount,
func.ifnull(a.c.PurchaseAmount, 0).label("PurchaseAmount"),
func.ifnull(b.c.PaidAmount, 0).label("PaidAmount"),
)
query = (
query.join(Supplier)
.outerjoin(a, a.c.PurchaseId == Purchase.Id)
.outerjoin(b, b.c.PurchaseId == Purchase.Id)
.group_by(Purchase.Id)
)
query = self.formQueryFromParam(query, a, b, param)
query = query.order_by(desc(Purchase.PurchaseDate), a.c.PurchaseAmount)
lstItems = query.offset(param.PageNo).limit(param.PageSize).all()
if not param.LoadStats:
return lstItems
tquery = DBSession.query(
func.count(Purchase.Id).label("ItemsCount"),
func.ifnull(func.sum(a.c.PurchaseAmount), 0).label("TotalAmount"),
func.ifnull(
func.sum(func.IF(b.c.PaidAmount >= a.c.PurchaseAmount, a.c.PurchaseAmount, b.c.PaidAmount)), 0
).label("TotalPaidAmount"),
)
tquery = (
tquery.join(Supplier)
.outerjoin(a, a.c.PurchaseId == Purchase.Id)
.outerjoin(b, b.c.PurchaseId == Purchase.Id)
)
tquery = self.formQueryFromParam(tquery, a, b, param)
return lstItems, tquery.first()
def hello():
db = current_app.config['DB']()
# Main package query
query = queries.packages(db)
total_pkg_count = query.count()
py3_only = query.filter(tables.Package.status == 'py3-only')
py3_only = queries.order_by_name(db, py3_only)
legacy_leaf = query.filter(tables.Package.status == 'legacy-leaf')
legacy_leaf = queries.order_by_name(db, legacy_leaf)
released = query.filter(tables.Package.status == 'released')
released = queries.order_by_name(db, released)
dropped = query.filter(tables.Package.status == 'dropped')
dropped = queries.order_by_name(db, dropped)
mispackaged = query.filter(tables.Package.status == 'mispackaged')
mispackaged = mispackaged.options(subqueryload('collection_packages'))
mispackaged = mispackaged.options(
subqueryload('collection_packages.tracking_bugs'))
mispackaged = mispackaged.join(tables.CollectionPackage)
mispackaged = mispackaged.outerjoin(
tables.Link,
and_(tables.Link.type == 'bug',
tables.Link.collection_package_id == tables.CollectionPackage.id))
mispackaged = mispackaged.order_by(
func.ifnull(tables.Link.last_update, '9999'))
mispackaged = queries.order_by_name(db, mispackaged)
blocked = query.filter(tables.Package.status == 'blocked')
blocked = blocked.options(subqueryload('run_time_requirements'))
blocked = queries.order_by_name(db, blocked)
ready = query.filter(tables.Package.status == 'idle')
ready = ready.options(subqueryload('run_time_requirers'))
ready = queries.order_by_name(db, ready)
# Naming policy tracking.
naming_progress, _ = get_naming_policy_progress(db)
py3_only = list(py3_only)
legacy_leaf = list(legacy_leaf)
released = list(released)
ready = list(ready)
blocked = list(blocked)
mispackaged = list(mispackaged)
dropped = list(dropped)
random_mispackaged = random.choice(mispackaged)
# Check we account for all the packages
done_packages = (py3_only, legacy_leaf, released, dropped)
sum_by_status = sum(
len(x) for x in (ready, blocked, mispackaged) + done_packages)
assert sum_by_status == total_pkg_count
the_score = sum(len(x) for x in done_packages) / total_pkg_count
# Nonbolocking set query
query = db.query(tables.Package)
query = query.outerjoin(tables.Package.collection_packages)
query = query.filter(tables.CollectionPackage.nonblocking)
nonblocking = set(query)
# Group query
query = db.query(tables.Group)
query = query.join(tables.Group.packages)
query = query.join(tables.Package.status_obj)
query = query.group_by(tables.Group.ident)
query = query.group_by(tables.Package.status)
query = query.order_by(tables.Status.order)
query = query.order_by(tables.Group.name)
query = query.add_columns(tables.Package.status,
func.count(tables.Package.name))
groups = get_groups(db, query.filter(~tables.Group.hidden))
hidden_groups = get_groups(db, query.filter(tables.Group.hidden))
# Statuses with no. of packages
statuses = OrderedDict(
db.query(tables.Status, func.count(tables.Package.name)).outerjoin(
tables.Status.packages).group_by(tables.Status.ident).order_by(
tables.Status.order))
return render_template(
'index.html',
breadcrumbs=((url_for('hello'), 'Python 3 Porting Database'), ),
statuses=statuses,
priorities=list(
db.query(tables.Priority).order_by(tables.Priority.order)),
total_pkg_count=total_pkg_count,
status_summary=get_status_summary(db),
ready_packages=ready,
blocked_packages=blocked,
py3_only_packages=py3_only,
legacy_leaf_packages=legacy_leaf,
released_packages=released,
dropped_packages=dropped,
mispackaged_packages=mispackaged,
random_mispackaged=random_mispackaged,
groups=groups,
hidden_groups=hidden_groups,
nonblocking=nonblocking,
the_score=the_score,
naming_progress=naming_progress,
)
def get_updates_for_group(self, session, params):
"""
Get all updates for a group of hosts by the specified filters,
like get_updates function.
Status filter is specially treaten.
in this order of priority:
Groups -> Update -> Update Type
"""
if 'gid' in params:
gid = params['gid']
if 'uuids' in params:
uuids = params['uuids']
else:
uuids = []
if 'is_install' in params:
is_installed = params['is_installed']
else:
is_installed = None
if 'filters' in params and 'status' in params['filters']:
dStatus = int(params['filters']['status'])
del params['filters']['status']
else:
dStatus = STATUS_NEUTRAL
try:
# Defining subqueries
installed_targets = session.query(
Target.update_id,
func.sum(Target.is_installed).label('total_installed')
)
# count only group machines
installed_targets = installed_targets.filter(
Target.uuid.in_(uuids))
installed_targets = installed_targets.group_by(Target.update_id)
installed_targets = installed_targets.subquery()
all_targets = session.query(
Target.update_id,
func.count('*').label('total_targets')
)
# count only group machines
all_targets = all_targets.filter(Target.uuid.in_(uuids))
all_targets = all_targets.group_by(Target.update_id).subquery()
group = session.query(Groups).filter(Groups.gid == gid).subquery()
query = session.query(
Update,
func.ifnull(
installed_targets.c.total_installed,
0).label('total_installed'),
func.ifnull(
all_targets.c.total_targets,
0).label('total_targets'),
func.ifnull(
group.c.gid,
gid).label('gid'),
func.ifnull(
group.c.status,
0).label('group_status'),
)
query = query.join(Target)
query = query.join(UpdateType)
# filter on the group of hosts
query = query.filter(Target.uuid.in_(uuids))
# add subqueries
query = query.outerjoin(
installed_targets,
Update.id == installed_targets.c.update_id)
query = query.outerjoin(
all_targets,
Update.id == all_targets.c.update_id)
query = query.outerjoin(
group,
Update.id == group.c.update_id)
if is_installed is not None:
query = query.filter(Target.is_installed == is_installed)
# ============================================
# ==== STATUS FILTERING ======================
# ============================================
if dStatus == STATUS_NEUTRAL:
query = query.filter((group.c.status == None) |
(group.c.status == STATUS_NEUTRAL))
query = query.filter(Update.status == STATUS_NEUTRAL)
query = query.filter(UpdateType.status == STATUS_NEUTRAL)
else:
query = query.filter(
# 1st level filtering : Group status
(group.c.status == dStatus) |
(
(
(group.c.status == None) |
(group.c.status == STATUS_NEUTRAL)
) &
(
# 2nd level filtering : Update status
(Update.status == dStatus) |
(
(Update.status == STATUS_NEUTRAL) &
(UpdateType.status == dStatus)
)
)
)
)
# ============================================
# ==== END STATUS FILTERING ==================
# ============================================
return query
except Exception as e:
logger.error("DB Error: %s" % str(e))
return False
def GetSums(runs):
if runs:
run_objs = []
presence_mask = 0
for run_id in runs:
run = Run.get_by(id=run_id)
run_objs.append(run)
presence_mask = presence_mask | (1 << (run.day - 1))
team = alias(select([Team.table], Team.table.c.present.op('&')(presence_mask)), alias="team")
breed = alias(select([Breed.table]), alias="breed")
r = session.query()
r = r.add_entity(Team, alias=team)
r = r.outerjoin(breed)
r = r.add_entity(Breed, alias=breed)
pen = []
time_pen = []
time = []
sorts = []
disq = []
ran = []
lengths = 0
for run in run_objs:
run_time, run_max_time = ServerCache().Get(('run_times', run.id), lambda: GetRunTimes(run.id))
res = alias(select([Result.table], Result.table.c.run_id==run.id))
sort = alias(select([Sort.table], Sort.table.c.run_id==run.id))
pen.append(res.c.mistakes*5 + res.c.refusals*5)
time_pen.append((res.c.time - run_time)*(res.c.time > run_time))
time.append(res.c.time)
s = ((func.ifnull(sort.c.value, 0) == 0) & (team.c.def_sort == 1)) | ((func.ifnull(sort.c.value, 0) == 3) & (team.c.def_sort == 0))
sorts.append(s)
lengths = lengths + run.length
dis = ((res.c.time > run_max_time) | (res.c.disqualified) | (res.c.refusals >= 3))
disq.append(dis)
ran.append((res.c.time > 0) | dis)
r = r.outerjoin(res).outerjoin(sort)
r = r.add_columns(reduce(lambda x,y: x+y, pen).label("penalty"))
r = r.add_columns(reduce(lambda x,y: x+y, time_pen).label("time_penalty"))
r = r.add_columns(reduce(lambda x,y: x+y, pen+time_pen).label("total_penalty"))
r = r.add_columns(reduce(lambda x,y: x*y, time).label("time_fac"))
r = r.add_columns(reduce(lambda x,y: x+y, ran).label("ran_all"))
r = r.add_columns(reduce(lambda x,y: x+y, disq).label("disq"))
r = r.add_columns(reduce(lambda x,y: max(x, y), sorts).label("sort"))
r = r.add_columns("(team.handler_name || ' ' || team.handler_surname) team_handler")
r = r.add_columns("(team.dog_name || ' ' || team.dog_kennel) team_dog")
result_time = reduce(lambda x,y: x+y, time).label("result_time")
r = r.add_columns(result_time)
r = r.add_columns(func.ifnull(lengths/result_time, 0).label("speed"))
r = r.filter("sort == 0 AND ran_all == %d" % len(runs))
r = r.order_by("disq, total_penalty, penalty, result_time")
rows = session.execute(r).fetchall()
num = 0
sums = []
for r in rows:
r = dict(zip(r.keys(), r.values()))
num += 1
r['rank'] = num
sums.append(r)
else:
sums = []
return sums
def hello():
db = current_app.config['DB']()
query = queries.collections(db)
query = query.options(subqueryload('collection_statuses'))
collections = list(query)
coll_info = {}
for i, collection in enumerate(collections):
query = db.query(tables.CollectionPackage.status,
func.count(tables.CollectionPackage.id))
query = query.filter(tables.CollectionPackage.collection == collection)
query = query.join(tables.CollectionPackage.status_obj)
query = query.group_by(tables.CollectionPackage.status)
query = query.order_by(tables.Status.order)
data = OrderedDict(query)
total = sum(v for k, v in data.items())
coll_info[collection] = {
'total': total,
'data': data,
}
# Main package query
query = queries.packages(db)
total_pkg_count = query.count()
active = query.filter(tables.Package.status == 'in-progress')
active = queries.order_by_weight(db, active)
active = queries.order_by_name(db, active)
active = active.options(subqueryload('collection_packages'))
active = active.options(subqueryload('collection_packages.links'))
done = query.filter(tables.Package.status == 'released')
done = queries.order_by_name(db, done)
dropped = query.filter(tables.Package.status == 'dropped')
dropped = queries.order_by_name(db, dropped)
mispackaged = query.filter(tables.Package.status == 'mispackaged')
mispackaged = mispackaged.options(subqueryload('collection_packages'))
mispackaged = mispackaged.options(subqueryload('collection_packages.tracking_bugs'))
mispackaged = mispackaged.join(tables.CollectionPackage)
mispackaged = mispackaged.outerjoin(
tables.Link,
and_(tables.Link.type == 'bug',
tables.Link.collection_package_id == tables.CollectionPackage.id))
mispackaged = mispackaged.order_by(func.ifnull(tables.Link.last_update, '9999'))
mispackaged = queries.order_by_name(db, mispackaged)
blocked = query.filter(tables.Package.status == 'blocked')
blocked = blocked.options(subqueryload('requirements'))
blocked = queries.order_by_name(db, blocked)
ready = query.filter(tables.Package.status == 'idle')
ready = ready.options(subqueryload('requirers'))
ready = queries.order_by_name(db, ready)
active = list(active)
done = list(done)
ready = list(ready)
blocked = list(blocked)
mispackaged = list(mispackaged)
dropped = list(dropped)
random_mispackaged = random.choice(mispackaged)
# Check we account for all the packages
sum_by_status = sum(len(x) for x in (active, done, ready, blocked,
mispackaged, dropped))
assert sum_by_status == total_pkg_count
the_score = (len(done) + len(dropped)) / total_pkg_count
# Nonbolocking set query
query = db.query(tables.Package)
query = query.outerjoin(tables.Package.collection_packages)
query = query.filter(tables.CollectionPackage.nonblocking)
nonblocking = set(query)
# Group query
query = db.query(tables.Group)
query = query.join(tables.Group.packages)
query = query.join(tables.Package.status_obj)
query = query.group_by(tables.Group.ident)
query = query.group_by(tables.Package.status)
query = query.order_by(tables.Status.order)
query = query.order_by(tables.Group.name)
query = query.add_columns(tables.Package.status,
func.count(tables.Package.name))
groups = get_groups(db, query.filter(~tables.Group.hidden))
hidden_groups = get_groups(db, query.filter(tables.Group.hidden))
# Statuses with no. of packages
statuses = OrderedDict(
db.query(tables.Status, func.count(tables.Package.name))
.outerjoin(tables.Status.packages)
.group_by(tables.Status.ident)
.order_by(tables.Status.order))
return render_template(
'index.html',
breadcrumbs=(
(url_for('hello'), 'Python 3 Porting Database'),
),
collections=collections,
coll_info=coll_info,
statuses=statuses,
priorities=list(db.query(tables.Priority).order_by(tables.Priority.order)),
total_pkg_count=total_pkg_count,
status_summary=get_status_summary(db),
active_packages=active,
ready_packages=ready,
blocked_packages=blocked,
done_packages=done,
dropped_packages=dropped,
mispackaged_packages=mispackaged,
random_mispackaged=random_mispackaged,
groups=groups,
hidden_groups=hidden_groups,
nonblocking=nonblocking,
the_score=the_score,
)
def GetStartList(run_id, includeRemoved=False, includeNotPresent=False):
run = Run.get_by(id=run_id)
if run:
breeds = BreedFilter.query.filter_by(run=run).all()
sq = aliased(Result, Result.query.filter_by(run=run).subquery())
sort = aliased(Sort, Sort.query.filter_by(run=run).subquery())
query = Team.query.filter_by(size=run.size)
if includeNotPresent:
query = query.filter(Team.table.c.registered.op('&')(1 << (run.day - 1)))
query = query.filter(Team.table.c.confirmed)
else:
query = query.filter(Team.table.c.present.op('&')(1 << (run.day - 1)))
if len(breeds):
query = query.filter(Team.table.c.dog_breed_id.in_([b.breed_id for b in breeds]))
if run.variant == 0:
query = query.filter_by(category=run.category)
else:
query = query.filter(Team.table.c.category!=3)
if run.squads:
query = query.filter(func.ifnull(Team.table.c.squad, "") != "")
#magic numbers ahoy! these are actually random
order = [func.length(Team.table.c.squad) * 133 % 204, Team.table.c.squad, Team.table.c.start_num]
else:
#beginning, end, start number
order = [func.ifnull(sort.value, 0) != 1, func.ifnull(sort.value, 0) == 2, Team.table.c.start_num]
s = ((func.ifnull(sort.value, 0) == 0) & (Team.table.c.def_sort == 1)) | ((func.ifnull(sort.value, 0) == 3) & (Team.table.c.def_sort == 0))
if includeRemoved:
#put removed teams at the end
order.insert(0, s)
else:
#or don't get them at all
query = query.filter(s != 1)
query = query.add_entity(sq).add_entity(sort).outerjoin(sq).outerjoin(sort).add_entity(Breed).outerjoin(Breed.table).order_by(*order)
query = query.all()
result = []
new_entries = False
for t in query:
team = t[0].to_dict()
if t[1]:
res = t[1]
else:
res = Result(team_id=team['id'], run_id=run.id)
new_entries = True
if t[2]:
sort = t[2]
else:
sort = Sort(team_id=team['id'], run_id=run.id)
new_entries = True
team['result'] = res.to_dict()
team['sort'] = sort.to_dict()
team['breed'] = t[3].to_dict()
if team['def_sort']:
#def_sorts of one get special sorting messages, see enum definitons
team['sort']['value'] += 4
result.append(team)
if new_entries:
session.commit()
return GetStartList(run_id, includeRemoved)
if not run.squads and not run.sort_run_id:
spacing = 8
while spacing > 0:
result, r = DoSpacing(result)
result.reverse()
result, r = DoSpacing(result)
result.reverse()
if not r:
break
spacing -= 1
elif run.sort_run_id:
sort = []
if run.squads:
squad_results = ServerCache().Get(('squad_results', run.sort_run_id), lambda: GetSquadResults(run.sort_run_id))
for r in squad_results:
for m in r['members']:
sort.append(m['team_id'])
else:
indiv_results = ServerCache().Get(('results', run.sort_run_id), lambda: GetResults(run.sort_run_id))
for r in indiv_results:
sort.append(r['team_id'])
for r in result[:]:
if not r['id'] in sort:
result.remove(r)
result.sort(key=lambda t: sort.index(t['id']), reverse=True)
order = 0
for t in result:
order += 1
t['order'] = order
return result
else:
return []