def get_errors(self, test_id):
statement = select([
(func.sum(aggregate.c.count*aggregate.c.errors/100)*100/func.sum(aggregate.c.count)).label('fail_%'),
(100 - func.sum(aggregate.c.count*aggregate.c.errors/100)*100/func.sum(aggregate.c.count)).label('success_%')
])\
.where(aggregate.c.test_id == test_id)
return self.execute_statement(statement, False)
def accounts():
month_year = request.args.get('month_year')
month_years = (db.session.query(func.strftime("%m-%Y", Transaction.date))
.select_from(Transaction)
.distinct()
.order_by(func.strftime("%Y-%m", Transaction.date))
.all()
)
month_years = [each[0] for each in month_years]
accounts_query = (db.session.query(
func.sum(Transaction.withdrawal),
func.sum(Transaction.deposit),
Account.full_title)
.select_from(Account)
.outerjoin(Transaction)
.group_by(Account.id)
.order_by(Account.full_title)
)
if month_year:
accounts_query = accounts_query.filter(func.strftime("%m-%Y", Transaction.date) == month_year)
accounts = accounts_query.all()
def relabel(account):
return {"withdrawal": account[0],
"deposit": account[1],
"full_title": account[2]}
accounts = [relabel(account) for account in accounts]
return render_template('accounts.html', accounts=accounts, title="Accounts", month_year=month_year, month_years=month_years)
def stockcheck(request, info, session):
buylist = []
depts = session\
.query(Department)\
.order_by(Department.id)\
.all()
if request.method == 'POST':
form = StockCheckForm(depts, request.POST)
if form.is_valid():
cd = form.cleaned_data
ahead = datetime.timedelta(days=cd['weeks_ahead'] * 7)
behind = datetime.timedelta(days=cd['months_behind'] * 30.4)
min_sale = cd['minimum_sold']
dept = int(cd['department'])
r = session\
.query(StockType, func.sum(StockOut.qty) / behind.days)\
.join(StockItem)\
.join(StockOut)\
.options(lazyload(StockType.department),
lazyload(StockType.unit),
undefer(StockType.instock))\
.filter(StockOut.removecode_id == 'sold')\
.filter((func.now() - StockOut.time) < behind)\
.filter(StockType.dept_id == dept)\
.having(func.sum(StockOut.qty) / behind.days > min_sale)\
.group_by(StockType)\
.all()
buylist = [(st, '{:0.1f}'.format(sold),
'{:0.1f}'.format(sold * ahead.days - st.instock))
for st, sold in r]
buylist.sort(key=lambda l: float(l[2]), reverse=True)
else:
form = StockCheckForm(depts)
return ('stockcheck.html', {'form': form, 'buylist': buylist})
def overview():
uquery = lambda: session.session.query(User)
entries = [{"title": "Nutzer in Datenbank",
"href": None,
"number": uquery().count()},
{"title": "Mitglieder",
"href": None,
"number": uquery().join(Membership).filter(
Membership.group == config.member_group,
Membership.active())
.count()},
{"title": "Nicht bezahlt",
"href": None,
"number": uquery().join(User.account)
.join(Split)
.group_by(User.id)
.having(func.sum(Split.amount) > 0)
.count()},
{"title": "Nicht bezahlt (Mitglieder)",
"href": "#",
"number": uquery().join(Membership).filter(
Membership.group == config.member_group,
Membership.active())
.join(User.account)
.join(Split)
.group_by(User.id)
.having(func.sum(Split.amount) > 0)
.count()}]
return render_template("user/user_overview.html", entries=entries)
def get_query_orders_summary(self,
store_id,
start_time,
end_time,
room_id=0):
OrderModel = get_model('t_order', store_id)
q = self.slave.query(
func.count('1').label('count'),
func.sum(OrderModel.money).label('money'),
func.sum(OrderModel.real_money).label('real_money'))
if room_id:
q = q.filter_by(room_id=room_id)
q = q.filter(OrderModel.state == ORDER_STATE['finish'])
q = q.filter(OrderModel.finish_time >= start_time,
OrderModel.finish_time <= end_time)
result = q.all()
summary = {'count': 0, 'money': 0, 'real_money': 0}
if not result:
return summary
if result[0].count:
summary['count'] = result[0].count
if result[0].money:
summary['money'] = int(result[0].money)
if result[0].real_money:
summary['real_money'] = int(result[0].real_money)
return summary
def stockcheck(request, info, session):
buylist = []
depts = session.query(Department).order_by(Department.id).all()
if request.method == "POST":
form = StockCheckForm(depts, request.POST)
if form.is_valid():
cd = form.cleaned_data
ahead = datetime.timedelta(days=cd["weeks_ahead"] * 7)
behind = datetime.timedelta(days=cd["months_behind"] * 30.4)
min_sale = cd["minimum_sold"]
dept = int(cd["department"])
q = (
session.query(StockType, func.sum(StockOut.qty) / behind.days)
.join(StockItem)
.join(StockOut)
.options(lazyload(StockType.department))
.options(lazyload(StockType.unit))
.options(undefer(StockType.instock))
.filter(StockOut.removecode_id == "sold")
.filter((func.now() - StockOut.time) < behind)
.filter(StockType.dept_id == dept)
.having(func.sum(StockOut.qty) / behind.days > min_sale)
.group_by(StockType)
)
r = q.all()
buylist = [(st, "{:0.1f}".format(sold), "{:0.1f}".format(sold * ahead.days - st.instock)) for st, sold in r]
buylist.sort(key=lambda l: float(l[2]), reverse=True)
else:
form = StockCheckForm(depts)
return ("stockcheck.html", {"form": form, "buylist": buylist})
def get_pack_sales(self,
store_id,
bill_ids,
order_by='count',
page=1,
page_size=10):
if not bill_ids:
return []
offset = (page - 1) * page_size
BillModel = get_model('t_bill_product', store_id)
q = self.slave.query(BillModel.pack_id,
func.sum(BillModel.count).label('count'),
func.sum(BillModel.money).label('money'))
q = q.filter(BillModel.bill_id.in_(tuple(bill_ids)),
BillModel.pack_id > 0)
q = q.group_by('pack_id').order_by(
desc(order_by)).offset(offset).limit(page_size)
result = q.all()
sales = []
for ret in result:
sales.append({
'pack_id': ret.pack_id,
'count': int(ret.count) if ret.count else 0,
'money': int(ret.money) if ret.money else 0
})
return sales
def get_average_score(word_type):
"""Average score of last 7 days"""
other_time = datetime.now() - timedelta(hours=168)
x = config.db.session.query(
label('total', func.sum(Words.attempts)),
label('correct',
func.sum(Words.correct))).filter_by(word_type=word_type).first()
return {"attempts": x[0], "correct": x[1]}
def generate_metrics(full=False):
def write_single_stat(name, help, type, value):
fmt = "# HELP {name} {help}\n# TYPE {name} {type}\n{name} {value}\n\n"
return fmt.format(name=name, help=help, type=type, value=value)
def gen_labels(labels):
pieces = [key + "=" + str(val) for key, val in labels.items()]
return ",".join(pieces)
def write_array_stat(name, help, type, data):
ret = "# HELP {name} {help}\n# TYPE {name} {type}\n" \
.format(name=name, help=help, type=type)
for entry in data:
assert (len(entry) == 2)
ret += "{name}{{{labels}}} {value}\n" \
.format(name=name, labels=gen_labels(entry[0]), value=entry[1])
return ret + "\n"
downloads_result = db.session.query(func.sum(
Package.downloads)).one_or_none()
downloads = 0 if not downloads_result or not downloads_result[
0] else downloads_result[0]
packages = Package.query.filter_by(state=PackageState.APPROVED).count()
users = User.query.filter(User.rank != UserRank.NOT_JOINED).count()
ret = ""
ret += write_single_stat("contentdb_packages", "Total packages", "counter",
packages)
ret += write_single_stat("contentdb_users", "Number of registered users",
"counter", users)
ret += write_single_stat("contentdb_downloads", "Total downloads",
"counter", downloads)
if full:
scores = Package.query.join(User).with_entities(User.username, Package.name, Package.score) \
.filter(Package.state==PackageState.APPROVED).all()
ret += write_array_stat("contentdb_package_score", "Package score",
"gauge", [({
"author": score[0],
"name": score[1]
}, score[2]) for score in scores])
else:
score_result = db.session.query(func.sum(Package.score)).one_or_none()
score = 0 if not score_result or not score_result[0] else score_result[
0]
ret += write_single_stat("contentdb_score", "Total package score",
"gauge", score)
return ret
def get_accepted_ids_query():
limit = Settings.get_settings().accept_limit
Application.standardize_scores()
answer_values = Application.question_answer_matrix_subquery()
return db.session.query(Application.id, func.sum(answer_values.c.sum_values + Application.standardized_score)) \
.join(answer_values, answer_values.c.id == Application.id) \
.filter(Application.score != 0) \
.group_by(Application.id) \
.order_by(func.sum(answer_values.c.sum_values + Application.standardized_score).desc().nullslast()) \
.limit(limit) \
.subquery()
def get_done(self):
total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(Task.project_id == self.id) \
.filter(Task.state != Task.state_removed) \
.scalar()
done = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(and_(Task.project_id == self.id,
Task.state == Task.state_done)) \
.scalar()
if not done:
done = 0
return round(done * 100 / total) if total != 0 else 0
def get_validated(self):
total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(Task.project_id == self.id) \
.filter(Task.state != Task.state_removed) \
.scalar()
validated = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(and_(Task.project_id == self.id,
Task.state == Task.state_validated)) \
.scalar()
if not validated:
validated = 0
return round(validated * 100 / total) if total != 0 else 0
def get_order_items():
orderItems = db.session.query(func.date_trunc(
'year', Order.order_date), func.sum(OrderItem.quantity)).group_by(
func.date_trunc('year', Order.order_date)).join(Order).all()
schema = OrderItemSchema(many=True)
# Renvoi un tab [] vide ??
return jsonify(orderItems)
def question_answer_matrix_subquery():
return db.session.query(Application.id, func.sum(Answer.answer_weight * Question.weight).label("sum_values")) \
.join(Answer) \
.join(Question) \
.filter((Question.question_type == QuestionType.demographic) | (Question.question_type == QuestionType.university)) \
.group_by(Application.id) \
.subquery()
def rank_participants():
Application.standardize_scores()
answer_values = Application.question_answer_matrix_subquery()
scores = db.session.query(Application.id, func.sum(answer_values.c.sum_values + Application.standardized_score).label('calculated_score')) \
.join(answer_values, answer_values.c.id == Application.id) \
.group_by(Application.id) \
.subquery()
team_scores = db.session.query(Answer.answer, func.avg(scores.c.calculated_score).label('team_score')) \
.join(scores, scores.c.id == Answer.application_id) \
.join(Question, Question.id == Answer.question_id) \
.filter(Question.question_type == QuestionType.teamEmail) \
.group_by(Answer.answer) \
.subquery()
teamQuestion = aliased(Question)
teamAnswer = aliased(Answer)
results_q = db.session.query(Application.id, Application.date_added, Application.feedback, Application.score, Application.standardized_score, team_scores.c.team_score, func.json_object_agg(Question.question, Answer.answer)) \
.join(Answer) \
.join(Question) \
.join(teamAnswer, teamAnswer.application_id == Application.id) \
.join(teamQuestion, teamQuestion.id == teamAnswer.question_id) \
.join(team_scores, team_scores.c.answer == teamAnswer.answer) \
.filter(teamQuestion.question_type == QuestionType.teamEmail) \
.group_by(Application.id, team_scores.c.team_score) \
.order_by(team_scores.c.team_score.desc().nullslast()) \
results = results_q.all()
return results
def has_exceeded_traffic(user):
"""
The function calculates the balance of the users traffic.
:param user: The user object which has to be checked.
:return: True if the user has more traffic than allowed and false if he
did not exceed the limit.
"""
result = session.session.query(
User.id,
(func.max(TrafficGroup.traffic_limit) * 1.10) < func.sum(TrafficVolume.size).label("has_exceeded_traffic")
).join(
User.active_traffic_groups
).join(
User.user_hosts
).join(
Host.ips
).join(
Ip.traffic_volumes
).filter(
User.id == user.id
).group_by(
User.id
).first()
if result is not None:
return result.has_exceeded_traffic
else:
return False
def amount(self, account):
query = (
self.session
.query(func.sum(Ledger.amount))
.filter(Ledger.account == account)
)
return query.scalar() or 0
def disliked_post_count(self):
from szurubooru.db import session
return (session
.query(func.sum(1))
.filter(PostScore.user_id == self.user_id)
.filter(PostScore.score == -1)
.one()[0] or 0)
def __check_user_jobs(self, job):
if job.user:
# Check the hard limit first
if self.app.config.registered_user_job_limit:
# Cache the job count if necessary
if not self.user_job_count:
query = self.sa_session.execute(select([model.Job.table.c.user_id, func.count(model.Job.table.c.user_id)]) \
.where(and_(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING)), (model.Job.table.c.user_id is not None))) \
.group_by(model.Job.table.c.user_id))
for row in query:
self.user_job_count[row[0]] = row[1]
if self.user_job_count.get(
job.user_id,
0) >= self.app.config.registered_user_job_limit:
return JOB_WAIT
# If we pass the hard limit, also check the per-runner count
if job.job_runner_name in self.app.config.job_limits:
# Cache the job count if necessary
if job.job_runner_name not in self.user_job_count_per_runner:
self.user_job_count_per_runner[job.job_runner_name] = {}
query_url, limit = self.app.config.job_limits[
job.job_runner_name]
base_query = select([model.Job.table.c.user_id, model.Job.table.c.job_runner_name, func.count(model.Job.table.c.user_id).label('job_count')]) \
.where(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING))) \
.group_by(model.Job.table.c.user_id, model.Job.table.c.job_runner_name)
if '%' in query_url or '_' in query_url:
subq = base_query.having(
model.Job.table.c.job_runner_name.like(
query_url)).alias('subq')
query = self.sa_session.execute(
select([
subq.c.user_id,
func.sum(subq.c.job_count).label('job_count')
]).group_by(subq.c.user_id))
else:
query = self.sa_session.execute(
base_query.having(model.Job.table.c.job_runner_name
== query_url))
for row in query:
self.user_job_count_per_runner[job.job_runner_name][
row['user_id']] = row['job_count']
if self.user_job_count_per_runner[job.job_runner_name].get(
job.user_id, 0) >= self.app.config.job_limits[
job.job_runner_name][1]:
return JOB_WAIT
elif job.galaxy_session:
# Anonymous users only get the hard limit
if self.app.config.anonymous_user_job_limit:
count = self.sa_session.query( model.Job ).enable_eagerloads( False ) \
.filter( and_( model.Job.session_id == job.galaxy_session.id,
or_( model.Job.state == model.Job.states.RUNNING,
model.Job.state == model.Job.states.QUEUED ) ) ).count()
if count >= self.app.config.anonymous_user_job_limit:
return JOB_WAIT
else:
log.warning(
'Job %s is not associated with a user or session so job concurrency limit cannot be checked.'
% job.id)
return JOB_READY
def hottest_users():
users_and_ids = db.session.query(func.sum(Hot.number), User.id).filter(User.id == Post.user_id, Post.id == Hot.post_id).group_by(User.id).order_by(func.sum(Hot.number)).limit(3).all()
users = []
for user_and_id in users_and_ids:
user = user_from_id(user_and_id[1])
user.__setattr__('hot', user_and_id[0])
users.append(user)
return users
def get_validated(self):
total = (
DBSession.query(func.sum(ST_Area(Task.geometry)))
.filter(Task.project_id == self.id, Task.cur_state.has(TaskState.state != TaskState.state_removed))
.scalar()
)
validated = (
DBSession.query(func.sum(ST_Area(Task.geometry)))
.filter(Task.project_id == self.id, Task.cur_state.has(TaskState.state == TaskState.state_validated))
.scalar()
)
if not validated:
validated = 0
return round(validated * 100 / total) if total != 0 else 0
def get_done(self):
total = (
DBSession.query(func.sum(ST_Area(Task.geometry)))
.filter(Task.project_id == self.id, Task.cur_state.has(TaskState.state != TaskState.state_removed))
.scalar()
)
done = (
DBSession.query(func.sum(ST_Area(Task.geometry)))
.filter(Task.project_id == self.id, Task.cur_state.has(TaskState.state == TaskState.state_done))
.scalar()
)
if not done:
done = 0
return round(done * 100 / total) if total != 0 else 0
def group_hot_value(group):
hot = db.session.query(func.sum(Hot.number))\
.filter(Group.active == True,
Post.group_id == group.id,
Post.id == Hot.post_id) \
.group_by(Group.id)\
.first()[0]
return hot
def overview():
uquery = lambda: session.session.query(User)
entries = [{
"title": "Nutzer in Datenbank",
"href": None,
"number": uquery().count()
}, {
"title":
"Mitglieder",
"href":
None,
"number":
uquery().join(Membership).filter(
Membership.group == config.member_group,
Membership.active()).count()
}, {
"title":
"Nicht bezahlt",
"href":
None,
"number":
uquery().join(User.account).join(Split).group_by(
User.id).having(func.sum(Split.amount) > 0).count()
}, {
"title":
"Nicht bezahlt (Mitglieder)",
"href":
"#",
"number":
uquery().join(Membership).filter(
Membership.group == config.member_group,
Membership.active()).join(User.account).join(Split).group_by(
User.id).having(func.sum(Split.amount) > 0).count()
}]
return render_template(
"user/user_overview.html",
entries=entries,
traffic_top_table=TrafficTopTable(
data_url=url_for("user.json_users_highest_traffic"),
table_args={
'data-page-size': 10,
'data-search': 'false'
},
))
def get_stats(self, name, limit=100, total=False):
q = (db.session.query(Stats.date,
func.sum(Stats.value).label("value")).filter(
place_parents.c.parent_id == self.id,
place_parents.c.child_id == Stats.place_id,
Stats.name == name).group_by(
Stats.date).order_by(
Stats.date).limit(limit))
return q.all()
def refund(self, invoice, amount):
"""Refund the invoice
"""
Transaction = tables.Transaction
tx_model = self.factory.create_transaction_model()
transactions = []
self.get(invoice.guid, with_lockmode='update')
if invoice.status != self.statuses.SETTLED:
raise InvalidOperationError('You can only refund a settled invoice')
refunded_amount = (
self.session.query(
func.coalesce(func.sum(Transaction.amount), 0)
)
.filter(
Transaction.invoice == invoice,
Transaction.transaction_type == TransactionModel.types.REFUND,
Transaction.submit_status.in_([
TransactionModel.submit_statuses.STAGED,
TransactionModel.submit_statuses.RETRYING,
TransactionModel.submit_statuses.DONE,
])
)
).scalar()
# Make sure do not allow refund more than effective amount
if refunded_amount + amount > invoice.effective_amount:
raise InvalidOperationError(
'Refund total amount {} + {} will exceed invoice effective amount {}'
.format(
refunded_amount,
amount,
invoice.effective_amount,
)
)
# the settled transaction
settled_transaction = (
self.session.query(Transaction)
.filter(
Transaction.invoice == invoice,
Transaction.transaction_type == TransactionModel.types.DEBIT,
Transaction.submit_status == TransactionModel.submit_statuses.DONE,
)
).one()
# create the refund transaction
transaction = tx_model.create(
invoice=invoice,
transaction_type=TransactionModel.types.REFUND,
amount=amount,
reference_to=settled_transaction,
)
transactions.append(transaction)
return transactions
def amount(self, account):
cache = self.session.query(AccountAmount).get(account.guid)
query = (self.session.query(func.sum(
Ledger.amount)).filter(Ledger.account == account))
if cache is None:
last_amount = 0
else:
last_amount = cache.amount
query = query.filter(Ledger.created_at > cache.updated_at)
return (query.scalar() or 0) + last_amount
def get_stats(self, name, limit=100, total=False):
q = (db.session.query(Stats.date, func.sum(Stats.value).label("value"))
.filter(
place_parents.c.parent_id==self.id,
place_parents.c.child_id==Stats.place_id,
Stats.name==name)
.group_by(Stats.date)
.order_by(Stats.date)
.limit(limit))
return q.all()
def home_page():
query = Package.query.filter_by(approved=True, soft_deleted=False)
count = query.count()
new = query.order_by(db.desc(Package.created_at)).limit(8).all()
pop_mod = query.filter_by(type=PackageType.MOD).order_by(db.desc(Package.score)).limit(8).all()
pop_gam = query.filter_by(type=PackageType.GAME).order_by(db.desc(Package.score)).limit(4).all()
pop_txp = query.filter_by(type=PackageType.TXP).order_by(db.desc(Package.score)).limit(4).all()
downloads = db.session.query(func.sum(PackageRelease.downloads)).first()[0]
return render_template("index.html", count=count, downloads=downloads, \
new=new, pop_mod=pop_mod, pop_txp=pop_txp, pop_gam=pop_gam)
def hottest_groups(user):
groups = db.session.query(Group)\
.outerjoin((Post, Post.group_id == Group.id),
(Hot, Post.id == Hot.post_id)) \
.filter(Group.active == True)\
.group_by(Group.id)\
.order_by(func.sum(Hot.number))\
.limit(3)\
.all()
return groups
def test_life_cycle_daily_total(db: me_db.Database):
"""Test that the sum of all measurements for a day is <= 24 hours."""
with db.Session() as s:
q = s.query(func.DATE(me_db.Measurement.date).label('date'),
(func.sum(me_db.Measurement.value)).label('time')) \
.filter(me_db.Measurement.source == 'LifeCycle') \
.group_by(me_db.Measurement.date)
df = pd.read_sql(q.statement, q.session.bind)
assert df[df.time > MILLISECONDS_IN_A_DAY].empty
def get_done(self):
total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(
Task.project_id == self.id,
Task.cur_state.has(TaskState.state != TaskState.state_removed)
) \
.scalar()
done = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(
Task.project_id == self.id,
Task.cur_state.has(TaskState.state == TaskState.state_done)
) \
.scalar()
if not done:
done = 0
return math.floor(done * 100 / total) if total != 0 else 0
def set_event_on_days(self, day_event: DayEvent, days_duration: list):
""" Set an event on several days each time with a specific duration.
:param day_event:
:param days_duration: An array of pairs. Each pair is (date, duration). Each date
must be unique.
:return:
"""
day_max = date(1980, 1, 1)
day_min = date(2050, 12, 31)
mainlog.debug("set_event_on_days")
mainlog.debug(days_duration)
for day, duration in days_duration:
day_min = min(day_min, day)
day_max = max(day_max, day)
db_events = session().query(DayEvent).filter(
and_(DayEvent.employee_id == day_event.employee_id,
DayEvent.event_type == day_event.event_type,
DayEvent.date.between(day_min, day_max))).all()
db_events_dates = dict(zip([e.date for e in db_events], db_events))
other_db_events = session().query(DayEvent.date,
func.sum(DayEvent.duration).label("duration_sum")).\
filter( and_( DayEvent.employee_id == day_event.employee_id,
DayEvent.event_type != day_event.event_type,
DayEvent.date.between(day_min, day_max))).\
group_by(DayEvent.date).all()
other_db_events_dates = dict([(e.date, e.duration_sum)
for e in other_db_events])
for day, duration in days_duration:
if day in other_db_events_dates and other_db_events_dates[
day] + duration > 1:
raise ServerException(ServerErrors.too_much_off_time_on_a_day,
date_to_dmy(day))
if day in db_events_dates:
# Replace the old duration
db_event = db_events_dates[day]
db_event.duration = duration
else:
nu_event = DayEvent()
nu_event.date = day
nu_event.duration = duration
nu_event.event_type = day_event.event_type
nu_event.employee_id = day_event.employee_id
session().add(nu_event)
session().commit()
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 build_query(self, name_field, id_field, subquery_where_guid=None):
fao = self.build_sub_query(where_guid=subquery_where_guid).alias()
query = select([name_field.label(Constants.NAME),
id_field.label(Constants.ID),
fao.columns[Constants.ASMT_SUBJECT].label(Constants.ASMT_SUBJECT),
fao.columns[Constants.LEVEL].label(Constants.LEVEL),
func.sum(fao.columns[Constants.TOTAL]).label(Constants.TOTAL)],
from_obj=[fao.join(self._dim_inst_hier, self._dim_inst_hier.c.inst_hier_rec_id == fao.c.inst_hier_rec_id)])\
.group_by(Constants.NAME, Constants.ID, Constants.ASMT_SUBJECT, Constants.LEVEL)\
.order_by(Constants.NAME)
return query
def download_size(self):
from ututi.model import File, FileDownload
download_size = meta.Session.query(func.sum(File.filesize))\
.filter(FileDownload.file_id==File.id)\
.filter(FileDownload.user==self)\
.filter(FileDownload.range_start==None)\
.filter(FileDownload.range_end==None)\
.scalar()
if not download_size:
return 0
return int(download_size)
def total_adjustment_amount(self):
"""Sum of total adjustment amount
"""
from sqlalchemy import func
session = object_session(self)
return (
session.query(func.coalesce(func.sum(Adjustment.amount), 0))
.filter(Adjustment.invoice_guid == self.guid)
.scalar()
)
def compute_statistics():
n_all = Question.query.count()
# Partial questions
n_partial_query = Question.query.filter(
db.and_(Question.n_answers > 0, Question.n_answers < MAX_ANSWERS))
n_partial = n_partial_query.count()
partial_questions_array = [0, 0, 0]
all_partial = n_partial_query.all()
for question in all_partial:
partial_questions_array[question.n_answers - 1] += 1
n_full_questions = Question.query.filter(
Question.n_answers == MAX_ANSWERS).count()
# Partial examples
n_full_examples, partial_examples = count_full_examples()
partial_examples_array = [0, 0, 0, 0, 0]
for example in partial_examples:
partial_examples_array[5 - example[1]] += 1
# Non-empty and non-full examples:
questions_not_empty = Question.query.filter(Question.n_answers > 0).all()
examples_not_empty = set()
for q in questions_not_empty:
examples_not_empty.add(q.example)
examples_not_empty = list(examples_not_empty)
examples_n_answers = []
for example in examples_not_empty:
sum_answers = Question.query.filter(
Question.example == example).with_entities(
func.sum(Question.n_answers)).scalar()
# Do not add examples that have been fully answered
if not sum_answers == MAX_ANSWERS * (N_MODELS * (N_MODELS - 1) / 2):
examples_n_answers += [[example, sum_answers]]
n_answers = Answer.query.count()
n_participants = User.query.count()
non_zero_participants = User.query.join(Answer).filter(
func.length(User.answers) > 0).all()
print "Total", n_all
print "Full examples:", n_full_examples
print "Partial examples:", len(partial_examples), ', distribution:', zip([
'1 question:', '2 questions:', '3 questions:', '4 questions:',
'5 questions:'
], partial_examples_array)
print "Non-empty examples:", len(examples_n_answers)
print "Full questions: ", n_full_questions
print "Partial questions:", n_partial, ', distribution:', zip(
['1 answer:', '2 answers:', '3 answers:'], partial_questions_array)
print 'Total answers:', n_answers
print 'Total participants:', n_participants, "(non-zero:", len(
non_zero_participants), ")"
def home():
def join(query):
return query.options(joinedload(Package.license),
joinedload(Package.media_license))
query = Package.query.filter_by(state=PackageState.APPROVED)
count = query.count()
featured = query.filter(Package.tags.any(name="featured")).order_by(
func.random()).limit(6).all()
new = join(query.order_by(db.desc(Package.approved_at))).limit(4).all()
pop_mod = join(
query.filter_by(type=PackageType.MOD).order_by(db.desc(
Package.score))).limit(8).all()
pop_gam = join(
query.filter_by(type=PackageType.GAME).order_by(db.desc(
Package.score))).limit(8).all()
pop_txp = join(
query.filter_by(type=PackageType.TXP).order_by(db.desc(
Package.score))).limit(8).all()
high_reviewed = join(query.order_by(db.desc(Package.score - Package.score_downloads))) \
.filter(Package.reviews.any()).limit(4).all()
updated = db.session.query(Package).select_from(PackageRelease).join(Package) \
.filter_by(state=PackageState.APPROVED) \
.order_by(db.desc(PackageRelease.releaseDate)) \
.limit(20).all()
updated = updated[:4]
reviews = PackageReview.query.filter_by(recommends=True).order_by(
db.desc(PackageReview.created_at)).limit(5).all()
downloads_result = db.session.query(func.sum(
Package.downloads)).one_or_none()
downloads = 0 if not downloads_result or not downloads_result[
0] else downloads_result[0]
tags = db.session.query(func.count(Tags.c.tag_id), Tag) \
.select_from(Tag).outerjoin(Tags).group_by(Tag.id).order_by(db.asc(Tag.title)).all()
return render_template("index.html",
count=count,
downloads=downloads,
tags=tags,
featured=featured,
new=new,
updated=updated,
pop_mod=pop_mod,
pop_txp=pop_txp,
pop_gam=pop_gam,
high_reviewed=high_reviewed,
reviews=reviews)
def get_total(self, filter_request: FilterRequest) -> float:
"""Gets the total of all transaction matching the provided filters.
:param filter_request: the filter request
:return: the total
"""
query = self.__entity_manager.query(
label('total', func.sum(TransactionDbo.amount)))
query = self.__query_builder.build(query, filters=filter_request)
logging.debug(query)
total = query.scalar()
return 0 if total is None else total
def get_stats(project):
"""
the changes to create a chart with
"""
total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(
Task.cur_state.has(TaskState.state != TaskState.state_removed),
Task.project_id == project.id
) \
.scalar()
subquery = DBSession.query(
TaskState.state,
TaskState.date,
ST_Area(Task.geometry).label('area'),
func.lag(TaskState.state).over(
partition_by=(
TaskState.task_id,
TaskState.project_id
),
order_by=TaskState.date
).label('prev_state')
).join(Task).filter(
TaskState.project_id == project.id,
TaskState.state != TaskState.state_ready) \
.order_by(TaskState.date)
tasks = subquery.all()
log.debug('Number of tiles: %s', len(tasks))
stats = [[project.created.isoformat(), 0, 0]]
done = 0
validated = 0
# for every day count number of changes and aggregate changed tiles
for task in tasks:
if task.state == TaskState.state_done:
done += task.area
if task.state == TaskState.state_invalidated:
if task.prev_state == TaskState.state_done:
done -= task.area
elif task.prev_state == TaskState.state_validated:
validated -= task.area
if task.state == TaskState.state_validated:
validated += task.area
done -= task.area
# append a day to the stats and add total number of 'done' tiles and a
# copy of a current tile_changes list
stats.append([task.date.isoformat(), done, validated])
return {"total": total, "stats": stats}
def stockcheck(request, info, session):
buylist = []
depts = session\
.query(Department)\
.order_by(Department.id)\
.all()
if request.method == 'POST':
form = StockCheckForm(depts, request.POST)
if form.is_valid():
cd = form.cleaned_data
ahead = datetime.timedelta(days=cd['weeks_ahead'] * 7)
behind = datetime.timedelta(days=cd['months_behind'] * 30.4)
min_sale = cd['minimum_sold']
dept = int(cd['department'])
r = session\
.query(StockType, func.sum(StockOut.qty) / behind.days)\
.join(StockItem)\
.join(StockOut)\
.options(lazyload(StockType.department),
lazyload(StockType.unit),
undefer(StockType.instock))\
.filter(StockOut.removecode_id == 'sold')\
.filter((func.now() - StockOut.time) < behind)\
.filter(StockType.dept_id == dept)\
.having(func.sum(StockOut.qty) / behind.days > min_sale)\
.group_by(StockType)\
.all()
buylist = [(st, '{:0.1f}'.format(sold),
'{:0.1f}'.format(sold * ahead.days - st.instock))
for st, sold in r]
buylist.sort(key=lambda l: float(l[2]), reverse=True)
else:
form = StockCheckForm(depts)
return ('stockcheck.html', {
'nav': [("Buying list", info.reverse("tillweb-stockcheck"))],
'form': form,
'buylist': buylist,
})
def amount(self, account):
cache = self.session.query(AccountAmount).get(account.guid)
query = (
self.session
.query(func.sum(Ledger.amount))
.filter(Ledger.account == account)
)
if cache is None:
last_amount = 0
else:
last_amount = cache.amount
query = query.filter(Ledger.created_at > cache.updated_at)
return (query.scalar() or 0) + last_amount
def get_space_usage(self, user_id):
"""Get space usage of user
"""
from sqlalchemy.sql.expression import func
# 100 MB basic space
basic = 100 * 1024 * 1024
# TODO: sum space of user here
used = self.session \
.query(func.ifnull(func.sum(tables.Audio.size), 0)) \
.filter_by(user_id=user_id) \
.scalar()
return dict(total=basic, used=used)
def recalculate_account_totals(accounts):
"""Recalculate the account totals based on the transaction log.
"""
amounts = [] # Set of tuples (Account, old total, new total)
for account in accounts:
deposit_sum = session.query(func.sum(Transaction.amount).\
label('deposit_sum')).\
filter(Transaction.account == account).\
filter(Transaction.status == True).\
filter(Transaction.action == Transaction.DEPOSIT).\
one().deposit_sum
withdraw_sum = session.query(func.sum(Transaction.amount).\
label('withdrawal_sum')).\
filter(Transaction.account == account).\
filter(Transaction.status == True).\
filter(Transaction.action == Transaction.WITHDRAWAL).\
one().withdrawal_sum
if debug:
print('Deposits: %s; Withdrawals: %s' % (deposit_sum, withdraw_sum))
new_total = ((deposit_sum if deposit_sum is not None else 0) -
(withdraw_sum if withdraw_sum is not None else 0))
amounts.append((account, account.total, _format_out_amount(new_total)))
return amounts
def update_score(connection, update_all=True):
"""
Update the score of all teams. If ``update_all`` is set, the points
for all challenges are updated beforehand as well.
This is your one-shot function to create up-to-date points for everything.
"""
from fluxscoreboard.models import dynamic_challenges
if update_all:
update_challenge_points(connection, update_team_count=True)
bonus_col = func.sum(Challenge._points - Challenge.base_points)
bonus_score = (select([func.coalesce(bonus_col, 0)]).
where(Challenge.id == Submission.challenge_id).
where(Team.id == Submission.team_id).
where(~Challenge.dynamic).
where(Challenge.published).
correlate(Team))
# base score
challenge_sum = func.coalesce(func.sum(Challenge.base_points), 0)
# first blood
fb_sum = func.coalesce(func.sum(Submission.additional_pts), 0)
points_col = challenge_sum + fb_sum
for module in dynamic_challenges.registry.values():
points_col += module.get_points_query(Team)
base_score = (select([points_col]).
where(Challenge.id == Submission.challenge_id).
where(Team.id == Submission.team_id).
where(~Challenge.dynamic).
where(Challenge.published).
correlate(Team))
query = (Team.__table__.update().
where(Team.active).
values(base_score=base_score,
bonus_score=bonus_score))
connection.execute(query)
def _query(self, id, site_id, date, date1, date2):
query = db.session.query(func.sum(RankModel.rank))
query = query.join(PageModel, RankModel.page == PageModel.id)
query = query.join(SiteModel, PageModel.site_id == SiteModel.id)
query = query.join(PersonModel, RankModel.person == PersonModel.id)
if date1 and date2:
return query.filter(RankModel.person == id,
SiteModel.id == site_id,
func.DATE(PageModel.scan) >= date1,
func.DATE(PageModel.scan) <= date2)
if date:
return query.filter(RankModel.person == id,
SiteModel.id == site_id,
func.DATE(PageModel.scan) == date)
return query.filter(RankModel.person == id, SiteModel.id == site_id)
def get_balance(self):
s = select([
AccountTransaction.patientAccount_id,
func.sum( AccountTransaction.amount * AccountTransactionType.multiplier ).label('balance')
]).where(
and_(
AccountTransaction.transactionType_id == AccountTransactionType.id,
AccountTransaction.patientAccount_id == self.id
)
).group_by(AccountTransaction.patientAccount_id)
result = db.session.execute(s).fetchone()
balance = result[1] if result else 0
return balance + self.initialBalance
def _resource_consumption_stats_view():
time_difference_expr = func.sum(KojiTask.finished - KojiTask.started)
time_difference = extract('EPOCH', time_difference_expr)
time_difference_all = select([time_difference]).select_from(KojiTask)
return (select([
Package.name,
KojiTask.arch,
time_difference_expr.label('time'),
cast(time_difference / time_difference_all,
Float).label('time_percentage'),
]).select_from(
join(
join(Package, Build, Package.id == Build.package_id),
KojiTask,
)).group_by(Package.name, KojiTask.arch))
def order_update():
this_week = Week.query.filter(Week.current is True).first()
the_orders = Order.query.filter(Order.user_id == current_user.id and Order.week == this_week.id).all()
the_order_ids = [i.id for i in the_orders]
if request.method == 'GET':
obj = {'items': [], 'old_orders': []}
the_items = Item.query.filter(Item.active is True, Item.week_id == this_week.id).order_by(asc(Item.user_id)).all()
for i in range(len(the_items)):
gone = 0
for order in the_items[i].orders:
gone += order.amount
obj['items'].append({'name': the_items[i].name, 'description': the_items[i].description, 'price': the_items[i].price, 'units': the_items[i].unit, 'available': the_items[i].max_available - gone, 'farmer': the_items[i].user.username, 'id': the_items[i].id})
for i in range(len(the_orders)):
obj['old_orders'].append({'name': the_orders[i].item.name, 'quantity': the_orders[i].amount, 'units': the_orders[i].item.unit, 'price': the_orders[i].item.price, 'id': the_orders[i].id, 'item_id': the_orders[i].item_id})
return json.dumps(obj)
elif request.method == 'POST':
orders = request.get_json(force=True)
order_ids = []
for i in orders:
try:
order_ids.append(i['id'])
except:
pass
for order in orders:
gone = db.session.query(func.sum(Order.amount)).filter(Order.week_id == this_week.id, Order.item_id == order['item_id']).first()[0]
if 'id' in order:
this_quan = Order.query.get(order['id']).amount
else:
this_quan = 0
if not gone:
gone = 0
if not this_quan:
this_quan = 0
left = float(Item.query.get(order['item_id']).max_available) - gone + float(this_quan)
order['quantity'] = min(left, float(order['quantity']))
new = Order(this_week.id, order['item_id'], order['quantity'], current_user.id)
try:
new.id = order['id']
except:
pass
db.session.merge(new)
db.session.commit()
for order in the_orders:
if order.id not in order_ids:
db.session.delete(order)
db.session.commit()
response = {'message': 'Your Order has been placed', 'priority': 'success', 'items': []}
return json.dumps(response)
def get_idea_published_tag(idea_id):
tag_ids = [elt[0] for elt in
session.query(TagData.id).outerjoin(TagData.ideas).filter(
IdeaData.id == idea_id)]
result = (session.query(
TagData.label,
func.sum(StateData.label.in_(get_workflow().get_published_states())).label(
'ideas_count')
).outerjoin(TagData.ideas))
result = (result.outerjoin(IdeaData.wf_context)
.outerjoin(IdeaWFContextData.state)
.filter(TagData.id.in_(tag_ids))
.group_by(TagData.label))
return result
def business_totals(session, firstday, lastday):
# This query is wrong in that it ignores the 'business' field in
# VatRate objects. Fixes that don't involve a database round-trip
# per session are welcome!
return (
session.query(Business, func.sum(Transline.items * Transline.amount))
.join(VatBand)
.join(Department)
.join(Transline)
.join(Transaction)
.join(Session)
.filter(Session.date <= lastday)
.filter(Session.date >= firstday)
.order_by(Business.id)
.group_by(Business)
.all()
)
def testName(self):
date1 = datetime.date(2009, 5, 23)
date2 = datetime.date(2010, 5, 23)
date3 = datetime.date(2011, 5, 23)
date4 = datetime.date(2013, 5, 23)
date5 = datetime.date(2014, 5, 23)
with self.control._session_context as session:
deal = model.Deal()
session.add(deal)
deal.cashflows.append(model.Cashflow(amount=Decimal(15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date2))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date4))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(25), settlement_date = date4))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(75), settlement_date = date5))
deal.holdings.append(model.Holding(current_value=Decimal(10)))
deal.holdings.append(model.Holding(current_value=Decimal(-5)))
session.commit()
s = session.query(func.sum(model.Cashflow.amount), func.avg(model.Cashflow.amount))
s = s.filter(model.Cashflow.amount == Decimal(15))
print s
#s.filter(model.Cashflow.amount == Decimal(15))
#print s
r = session.execute(s)
id = deal.id
cash_flows = []
#for row in r:
#print row
back = session.query(model.Cashflow.settlement_date.label("settlement_date"),model.Cashflow.amount.label("amount")).\
filter(model.Cashflow.deal_id==deal.id)
forward = session.query(model.ForwardCashflow.settlement_date.label("settlement_date"), model.ForwardCashflow.amount.label("amount")).\
filter(model.ForwardCashflow.deal_id==deal.id)
cash = back.union_all(forward).order_by(literal_column("settlement_date").asc(), literal_column("amount").asc())
for c in cash:
print c
cash_flows.append(c)
irr = IRRCalculator(Decimal('0.10'), cash_flows)
irr2 = IRRCalculator(Decimal('0.10'), [])
#print irr.theIRR
#print irr2.theIRR
mm = MMCalculator(cash_flows)
print mm.theMM
print mm.errors
def get_turnover(cls, company, year):
"""
Compute the annual turnover for a given company
"""
from autonomie.models.task import (
Task,
)
query = DBSESSION.query(func.sum(Task.ht))
query = query.filter(Task.company_id == company.id)
query = query.filter(func.year(Task.date) == year)
query = query.filter(Task.status == 'valid')
invoice_sum = query.filter(
Task.type_.in_(('invoice', 'cancelinvoice'))
).first()[0]
if invoice_sum is None:
invoice_sum = 0
return invoice_sum
def scoreboard(self, format='html'):
"""
A list of users ordered by their score. The score is computed by
by assigning every dataset a score (10 divided by no. of maintainers)
and then adding that score up for all maintainers.
This does give users who maintain a single dataset a higher score than
those who are a part of a maintenance team, which is not really what
we want (since that rewards single points of failure in the system).
But this is an adequate initial score and this will only be accessible
to administrators (who may be interested in findin these single points
of failures).
"""
# If user is not an administrator we abort
if not (c.account and c.account.admin):
abort(403, _("You are not authorized to view this page"))
# Assign scores to each dataset based on number of maintainers
score = db.session.query(Dataset.id,
(10 / func.count(Account.id)).label('sum'))
score = score.join('managers').group_by(Dataset.id).subquery()
# Order users based on their score which is the sum of the dataset
# scores they maintain
user_score = db.session.query(
Account.name, Account.email,
func.coalesce(func.sum(score.c.sum), 0).label('score'))
user_score = user_score.outerjoin(Account.datasets).outerjoin(score)
user_score = user_score.group_by(Account.name, Account.email)
# We exclude the system user
user_score = user_score.filter(Account.name != 'system')
user_score = user_score.order_by(desc('score'))
# Fetch all and assign to a context variable score and paginate them
# We paginate 42 users per page, just because that's an awesome number
scores = user_score.all()
c.page = templating.Page(scores, items_per_page=42,
item_count=len(scores),
**request.params)
return templating.render('account/scoreboard.html')
def __check_user_jobs( self, job ):
if job.user:
# Check the hard limit first
if self.app.config.registered_user_job_limit:
# Cache the job count if necessary
if not self.user_job_count:
query = self.sa_session.execute(select([model.Job.table.c.user_id, func.count(model.Job.table.c.user_id)]) \
.where(and_(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING)), (model.Job.table.c.user_id is not None))) \
.group_by(model.Job.table.c.user_id))
for row in query:
self.user_job_count[row[0]] = row[1]
if self.user_job_count.get(job.user_id, 0) >= self.app.config.registered_user_job_limit:
return JOB_WAIT
# If we pass the hard limit, also check the per-runner count
if job.job_runner_name in self.app.config.job_limits:
# Cache the job count if necessary
if job.job_runner_name not in self.user_job_count_per_runner:
self.user_job_count_per_runner[job.job_runner_name] = {}
query_url, limit = self.app.config.job_limits[job.job_runner_name]
base_query = select([model.Job.table.c.user_id, model.Job.table.c.job_runner_name, func.count(model.Job.table.c.user_id).label('job_count')]) \
.where(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING))) \
.group_by(model.Job.table.c.user_id, model.Job.table.c.job_runner_name)
if '%' in query_url or '_' in query_url:
subq = base_query.having(model.Job.table.c.job_runner_name.like(query_url)).alias('subq')
query = self.sa_session.execute(select([subq.c.user_id, func.sum(subq.c.job_count).label('job_count')]).group_by(subq.c.user_id))
else:
query = self.sa_session.execute(base_query.having(model.Job.table.c.job_runner_name == query_url))
for row in query:
self.user_job_count_per_runner[job.job_runner_name][row['user_id']] = row['job_count']
if self.user_job_count_per_runner[job.job_runner_name].get(job.user_id, 0) >= self.app.config.job_limits[job.job_runner_name][1]:
return JOB_WAIT
elif job.galaxy_session:
# Anonymous users only get the hard limit
if self.app.config.anonymous_user_job_limit:
count = self.sa_session.query( model.Job ).enable_eagerloads( False ) \
.filter( and_( model.Job.session_id == job.galaxy_session.id,
or_( model.Job.state == model.Job.states.RUNNING,
model.Job.state == model.Job.states.QUEUED ) ) ).count()
if count >= self.app.config.anonymous_user_job_limit:
return JOB_WAIT
else:
log.warning( 'Job %s is not associated with a user or session so job concurrency limit cannot be checked.' % job.id )
return JOB_READY
def _resource_consumption_stats_view():
time_difference_expr = func.sum(KojiTask.finished - KojiTask.started)
time_difference = extract('EPOCH', time_difference_expr)
time_difference_all = select([time_difference]).select_from(KojiTask)
return (
select([
Package.name,
KojiTask.arch,
time_difference_expr.label('time'),
cast(time_difference / time_difference_all, Float).label('time_percentage'),
])
.select_from(
join(
join(Package, Build, Package.id == Build.package_id),
KojiTask,
)
)
.group_by(Package.name, KojiTask.arch)
)
