def read_distribution():
supply = Address.select(fn.SUM(Address.balance)).execute()[0].sum
res = {}
sq = Address.select(Address.balance).order_by(Address.balance.desc()).limit(25)
q = Address.select(fn.SUM(sq.c.balance)).from_(sq)
res['0_24'] = {
'percent': (q[0].sum / supply) * 100,
'total': q[0].sum
}
sq = Address.select(Address.balance).order_by(Address.balance.desc()).offset(25).limit(25)
q = Address.select(fn.SUM(sq.c.balance)).from_(sq)
res['25_49'] = {
'percent': (q[0].sum / supply) * 100,
'total': q[0].sum
}
sq = Address.select(Address.balance).order_by(Address.balance.desc()).offset(50).limit(25)
q = Address.select(fn.SUM(sq.c.balance)).from_(sq)
res['50_99'] = {
'percent': (q[0].sum / supply) * 100,
'total': q[0].sum
}
sq = Address.select(Address.balance).order_by(Address.balance.desc()).offset(100)
q = Address.select(fn.SUM(sq.c.balance)).from_(sq)
res['remain'] = {
'percent': (q[0].sum / supply) * 100,
'total': q[0].sum
}
return res
def get_results(since=timedelta(days=1)):
yesterday = datetime.datetime.now() - since
d = time.mktime(yesterday.timetuple())
result = LongPosition.select(
fn.SUM(LongPosition.profit).alias('total_profit'),
fn.SUM(LongPosition.buy_quantity *
LongPosition.purchase_price).alias('total_spent'),
fn.SUM(LongPosition.fees).alias('total_fees'),
fn.COUNT(LongPosition.id).alias('num_positions')).where(
# position is closed...
(LongPosition.status << [
LongPosition.STATUS__CLOSED_RIDE_PROFIT,
LongPosition.STATUS__CLOSED_LOSS
]) &
# ...within the last...
(LongPosition.date_closed >= d))
return {
"profit":
result[0].total_profit,
"spent":
result[0].total_spent,
"profit_percentage":
result[0].total_profit / result[0].total_spent
if result[0].total_profit else Decimal('0.0'),
"num_trades":
result[0].num_positions,
"fees":
result[0].total_fees,
}
def api_stat_site_top_cate_at_date(date):
try:
date = datetime.strptime(date, '%Y-%m-%d').date()
limit = int(request.args.get('limit', 20))
limit = limit if limit > 0 else 20
except ValueError:
return jsonify({'code': 1, 'msg': 'invalid request'})
query = RoomDailyStat.select(
RoomDailyStat.cate,
fn.SUM(RoomDailyStat.dcount).alias('dsum'),
fn.SUM(RoomDailyStat.gcount).alias('gsum'),
fn.SUM(RoomDailyStat.income).alias('isum'),
(fn.SUM(RoomDailyStat.dcount * 100) + fn.SUM(RoomDailyStat.income)).alias('f')
).join(Date, on=(RoomDailyStat.date == Date.date_key)) \
.join(RoomCate, on=(RoomDailyStat.cate == RoomCate.cate_key)) \
.where(Date.date == date) \
.group_by(RoomDailyStat.cate) \
.order_by(SQL('f').desc()) \
.limit(limit)
payload = []
order = 0
for row in query:
payload.append({
'cateId': row.cate.cate_id,
'cateName': row.cate.name,
'dcount': row.dsum,
'gcount': row.gsum,
'income': int(row.isum / 100),
'factor': int(row.f / 100),
'order': order
})
order += 1
return jsonify({'code': 0, 'msg': 'success', 'data': payload})
def get(self, user):
args = parser.parse_args()
initial_date = date.fromisoformat(args.initial_date)
end_date = date.fromisoformat(args.end_date)
data = Move.select().where(
Move.submit_date.between(initial_date, end_date),
Move.user == user)
out = data.select(fn.SUM(
Move.value).alias("total")).where(Move.type == 1)
entry = data.select(fn.SUM(
Move.value).alias("total")).where(Move.type == 0)
out = out[0].total
if out is None:
out = 0
entry = entry[0].total
if entry is None:
entry = 0
return json_response(
{
"entry_sum": entry,
"out_sum": out,
"profit": (entry - out)
}, 200)
def api_stat_site_cate_daily(date):
try:
date = datetime.strptime(date, '%Y-%m-%d').date()
except ValueError or KeyError:
return jsonify({'code': 1, 'msg': 'invalid request'})
query = RoomHourlyStat.select(
RoomHourlyStat.cate,
fn.SUM(RoomHourlyStat.dcount).alias('dsum'),
fn.SUM(RoomHourlyStat.gcount).alias('gsum'),
fn.SUM(RoomHourlyStat.ucount).alias('usum'),
fn.SUM(RoomHourlyStat.income).alias('isum'),
fn.COUNT(fn.DISTINCT(RoomHourlyStat.room)).alias('rsum')
).join(Date, on=(RoomHourlyStat.date == Date.date_key)) \
.where(Date.date == date) \
.group_by(RoomHourlyStat.cate)
payload = []
for row in query:
payload.append({
'cate': row.cate.name,
'income': row.isum,
'ucount': row.usum,
'gcount': row.gsum,
'dcount': row.dsum,
'rcount': row.rsum
})
return jsonify({'code': 0, 'msg': 'success', 'data': payload})
def sumAllDebts(self, client: Client = None):
if client is None:
return Credit.select(fn.SUM(Credit.debt).alias('investment')).get()
else:
return Credit.select(fn.SUM(
Credit.debt).alias('investment')).where(
Credit.client == client).get()
def stars_stats(self, event, user=None):
if user:
try:
given_stars = list(StarboardEntry.select(
fn.COUNT('*'),
).join(Message).where(
(~ (StarboardEntry.star_message_id >> None)) &
(StarboardEntry.stars.contains(user.id)) &
(Message.guild_id == event.guild.id)
).tuples())[0][0]
recieved_stars_posts, recieved_stars_total = list(StarboardEntry.select(
fn.COUNT('*'),
fn.SUM(fn.array_length(StarboardEntry.stars, 1)),
).join(Message).where(
(~ (StarboardEntry.star_message_id >> None)) &
(Message.author_id == user.id) &
(Message.guild_id == event.guild.id)
).tuples())[0]
except:
return event.msg.reply(':warning: failed to crunch the numbers on that user')
embed = MessageEmbed()
embed.color = 0xffd700
embed.title = user.username
embed.set_thumbnail(url=user.avatar_url)
embed.add_field(name='Total Stars Given', value=str(given_stars), inline=True)
embed.add_field(name='Total Posts w/ Stars', value=str(recieved_stars_posts), inline=True)
embed.add_field(name='Total Stars Recieved', value=str(recieved_stars_total), inline=True)
# embed.add_field(name='Star Rank', value='#{}'.format(recieved_stars_rank), inline=True)
return event.msg.reply('', embed=embed)
total_starred_posts, total_stars = list(StarboardEntry.select(
fn.COUNT('*'),
fn.SUM(fn.array_length(StarboardEntry.stars, 1)),
).join(Message).where(
(~ (StarboardEntry.star_message_id >> None)) &
(Message.guild_id == event.guild.id)
).tuples())[0]
top_users = list(StarboardEntry.select(fn.SUM(fn.array_length(StarboardEntry.stars, 1)), User.user_id).join(
Message,
).join(
User,
on=(Message.author_id == User.user_id),
).where(
(~ (StarboardEntry.star_message_id >> None)) &
(fn.array_length(StarboardEntry.stars, 1) > 0) &
(Message.guild_id == event.guild.id)
).group_by(User).order_by(fn.SUM(fn.array_length(StarboardEntry.stars, 1)).desc()).limit(5).tuples())
embed = MessageEmbed()
embed.color = 0xffd700
embed.title = 'Star Stats'
embed.add_field(name='Total Stars Given', value=total_stars, inline=True)
embed.add_field(name='Total Starred Posts', value=total_starred_posts, inline=True)
embed.add_field(name='Top Star Recievers', value='\n'.join(
'{}. <@{}> ({})'.format(idx + 1, row[1], row[0]) for idx, row in enumerate(top_users)
))
event.msg.reply('', embed=embed)
def opportunity(opportunityid=None):
opportunities = models.Opportunity.select()
opp_stage_data = models.Opportunity.select(
models.Opportunity.stage,
fn.SUM(models.Opportunity.mrr).alias('mrr_in_stage')).group_by(
models.Opportunity.stage)
title = 'Opportunities'
if opportunityid != None:
opportunity = models.Opportunity.select().where(
models.Opportunity.id == opportunityid).get()
subscriptions = models.Subscription.select().where(
models.Subscription.opportunity_id == opportunityid)
prod_mrr = models.Subscription.select(
models.Subscription.product,
fn.SUM(models.Subscription.mrr).alias('mrr')).where(
models.Subscription.opportunity_id == opportunityid).group_by(
models.Subscription.product)
print(prod_mrr)
title = 'Opportunity Details'
return render_template('opportunity-detail.html',
opportunity=opportunity,
user=g.user._get_current_object(),
subscriptions=subscriptions,
prod_mrr=prod_mrr,
title=title)
return render_template('opportunity.html',
opportunities=opportunities,
title=title,
opp_stage_data=opp_stage_data)
def _recurse_availability_up_tree(node):
available = node.available
if not node.parent:
return node
else:
parent = node.parent
Parent = Item.alias()
children = Item.select().join(Parent, on=(Item.parent == Parent.pk)).where(Item.parent == parent.pk)
if not available:
children_available = children.where(Item.available == True).count() > 0
available = children_available
total_files = children.aggregate(fn.SUM(Item.total_files))
child_remote = children.where(((Item.available == False) & (Item.kind != "Topic")) | (Item.kind == "Topic")).aggregate(fn.SUM(Item.remote_size))
child_on_disk = children.aggregate(fn.SUM(Item.size_on_disk))
if parent.available != available:
parent.available = available
if parent.total_files != total_files:
parent.total_files = total_files
# Ensure that the aggregate sizes are not None
if parent.remote_size != child_remote and child_remote:
parent.remote_size = child_remote
# Ensure that the aggregate sizes are not None
if parent.size_on_disk != child_on_disk and child_on_disk:
parent.size_on_disk = child_on_disk
if parent.is_dirty():
parent.save()
_recurse_availability_up_tree(parent)
return node
def handler(body) -> dict:
"""
Serie temporal de casos e mortes
type -> 'day' or 'week'
period -> None, 'ytd', 'mtd' ...
init_date -> data inicial or None
end_date -> data final or None
state -> id do estado or 0 para total
"""
type = body.get('type', 'day')
period = body.get('period', None)
init_date = body.get('init_date', None)
end_date = body.get('end_date', None)
state = body.get('state', 0)
where = []
if period:
init_date, end_date = get_period(period)
if init_date and end_date:
where = [
CaseReport.date_report >= init_date,
CaseReport.date_report <= end_date
]
if type == 'day':
response = CaseReport.select(
CaseReport.date_report,
CaseReport.total_cases, CaseReport.total_deaths).where(
CaseReport.state_id == state, *where).order_by(
CaseReport.date_report.asc()).dicts().execute()
else:
response = CaseReport.select(
fn.MAX(CaseReport.date_report).alias('date_report'),
fn.SUM(CaseReport.total_cases).alias('total_cases'),
fn.SUM(CaseReport.total_deaths).alias('total_deaths')).where(
CaseReport.state_id == state, *where).order_by(
fn.MAX(CaseReport.date_report).asc()).group_by(
CaseReport.epi_week).dicts().execute()
cases = [[mktime(i.get('date_report').timetuple()),
i.get('total_cases')] for i in response]
deaths = [[
mktime(i.get('date_report').timetuple()),
i.get('total_deaths')
] for i in response]
return {
'dataSeries': [{
'id': 0,
'name': 'Casos',
'data': cases,
'type': 'spline'
}, {
'id': 1,
'name': 'Mortes',
'data': deaths,
'type': 'spline'
}]
}
async def count(self) -> typing.Tuple[int, int]:
"""
Return the total number of A-Ranks and S-Ranks relayed by Filo
"""
a_count = list(SubscriptionsMeta.select(fn.SUM(SubscriptionsMeta.value).alias('total')).where(SubscriptionsMeta.name == 'a_count'))[0].total or 0
s_count = list(SubscriptionsMeta.select(fn.SUM(SubscriptionsMeta.value).alias('total')).where(SubscriptionsMeta.name == 's_count'))[0].total or 0
return (a_count, s_count)
def calc_user_discover(body):
now = utils.now()
start = utils.timedelta(now, days=-90)
year_start = utils.timedelta(now, days=-365)
id_start = 0
while 1:
users = User.select(User.id).where(User.id>id_start).limit(200)
if not users:
break
for u in users:
user_id = u.id
id_start = u.id
us = UserStatistics.select().where(UserStatistics.user == u.id).first()
if not us:
us = UserStatistics()
us.user = u.id
count = UserDiscover.select(fn.SUM(UserDiscover.view_num)).where(UserDiscover.user==user_id, UserDiscover.update_at.between(start, now)).scalar()
us.season_view = count if count else 0
# 90天内投标
count = Proposal.select().where(Proposal.user==user_id,Proposal.ptype=='D',Proposal.create_at.between(start, now)).count()
us.season_proposal = count
# 90内沟通中
count = Proposal.select().where(Proposal.user==user_id,
Proposal.status=="interview",
Proposal.update_at.between(start, now)).count()
us.season_interview = count
# 90天内被邀请
count = Proposal.select().where(Proposal.user==user_id,Proposal.ptype=='I',Proposal.create_at.between(start, now)).count()
us.season_invite = count
# 90天内被邀请回复
count = Proposal.select().where(Proposal.user==user_id,
Proposal.ptype=='I',Proposal.status=="interview",
Proposal.update_at.between(start, now)).count()
us.season_reply = count
# 90天内被邀请当天回复
count1 = Proposal.select().where(Proposal.user==user_id,
Proposal.ptype=='I',Proposal.day_reply==True,
Proposal.update_at.between(start, now)).count()
us.season_day_reply = count1
# 90天内雇佣
count = Proposal.select().where(Proposal.user==user_id,
Proposal.status=="hire",
Proposal.update_at.between(start, now)).count()
us.season_hire = count
# 一年内收总金额
year_amount = MarginRecord.select(fn.SUM(MarginRecord.amount)).where(MarginRecord.user==user_id,
MarginRecord.record_type=="income", MarginRecord.create_at.between(year_start, now)).scalar()
us.year_amount = year_amount if year_amount else 0
us.update_at = now
us.save()
def create_subscription(accountid=None, opportunityid=None):
form = forms.SubscriptionForm()
form.account.choices = [(str(account.id), account.name)
for account in models.Account.select()]
form.opportunity.choices = [(str(opportunity.id), opportunity.name)
for opportunity in models.Opportunity.select()]
form.product.choices = [(str(product.id), product.name)
for product in models.Product.select()]
products = models.Product.select()
title = 'Create Subscription'
if form.validate_on_submit():
flash("Subscription created", 'success')
sub_create = models.Subscription.create(
account=form.account.data,
opportunity=form.opportunity.data,
product=form.product.data,
product_price=form.product_price.data,
quantity=form.quantity.data,
sub_start_date=form.sub_start_date.data,
sub_end_date=form.sub_end_date.data,
mrr=form.product_price.data * form.quantity.data,
arr=form.product_price.data * form.quantity.data * 12,
created_by=g.user._get_current_object())
sub_create.save()
subscriptionid = sub_create.id
sub_account_id = models.Subscription.select(
models.Subscription.account_id).where(
subscriptionid == models.Subscription.id)
new_account_mrr = models.Subscription.select(
fn.SUM(models.Subscription.mrr)).where(
models.Subscription.account_id == sub_account_id)
new_account_arr = models.Subscription.select(
fn.SUM(models.Subscription.arr)).where(
models.Subscription.account_id == sub_account_id)
models.Account.update(mrr=new_account_mrr, arr=new_account_arr).where(
models.Account.id == sub_account_id).execute()
sub_opp_id = models.Subscription.select(
models.Subscription.opportunity_id).where(
subscriptionid == models.Subscription.id)
new_opp_mrr = models.Subscription.select(
fn.SUM(models.Subscription.mrr)).where(
models.Subscription.opportunity_id == sub_opp_id)
new_opp_arr = models.Subscription.select(
fn.SUM(models.Subscription.arr)).where(
models.Subscription.opportunity_id == sub_opp_id)
models.Opportunity.update(mrr=new_opp_mrr, arr=new_opp_arr).where(
models.Opportunity.id == sub_opp_id).execute()
if opportunityid != None:
return redirect(url_for('opportunity',
opportunityid=opportunityid))
return redirect('subscription')
return render_template('create.html',
form=form,
products=products,
title=title,
accountid=accountid,
opportunityid=opportunityid)
def edit_subscription(subscriptionid, accountid=None, opportunityid=None):
form = forms.SubscriptionForm()
form.account.choices = [(str(account.id), account.name)
for account in models.Account.select()]
form.opportunity.choices = [(str(opportunity.id), opportunity.name)
for opportunity in models.Opportunity.select()]
form.product.choices = [(str(product.id), product.name)
for product in models.Product.select()]
record = models.Subscription.select().where(
subscriptionid == models.Subscription.id).dicts().get()
title = 'Edit Subscription'
products = models.Product.select()
if form.validate_on_submit():
flash("Subscription update", 'success')
edit_subscription = models.Subscription.update(
account=form.account.data,
opportunity=form.opportunity.data,
product=form.product.data,
product_price=form.product_price.data,
quantity=form.quantity.data,
sub_start_date=form.sub_start_date.data,
sub_end_date=form.sub_end_date.data,
mrr=form.product_price.data * form.quantity.data,
arr=form.product_price.data * form.quantity.data * 12,
).where(subscriptionid == models.Subscription.id)
edit_subscription.execute()
sub_account_id = models.Subscription.select(
models.Subscription.account_id).where(
subscriptionid == models.Subscription.id)
new_account_mrr = models.Subscription.select(
fn.SUM(models.Subscription.mrr)).where(
models.Subscription.account_id == sub_account_id)
new_account_arr = models.Subscription.select(
fn.SUM(models.Subscription.arr)).where(
models.Subscription.account_id == sub_account_id)
models.Account.update(mrr=new_account_mrr, arr=new_account_arr).where(
models.Account.id == sub_account_id).execute()
sub_opp_id = models.Subscription.select(
models.Subscription.opportunity_id).where(
subscriptionid == models.Subscription.id)
new_opp_mrr = models.Subscription.select(
fn.SUM(models.Subscription.mrr)).where(
models.Subscription.opportunity_id == sub_opp_id)
new_opp_arr = models.Subscription.select(
fn.SUM(models.Subscription.arr)).where(
models.Subscription.opportunity_id == sub_opp_id)
models.Opportunity.update(mrr=new_opp_mrr, arr=new_opp_arr).where(
models.Opportunity.id == sub_opp_id).execute()
return redirect('subscription')
return render_template('edit.html',
form=form,
record=record,
title=title,
products=products)
async def get_clan_info(identify):
test = Profile.select(
fn.SUM(Profile.rg).alias('total'),
Profile.user_id).group_by(Profile.rg, Profile.user_id).where(
Profile.clan == identify).order_by(fn.SUM(Profile.rg).desc())
query_result = await manager.execute(test)
raitings = 0
members = 0
for u in query_result:
raitings += round(int(u.total))
members += 1
return raitings, members
def get_overall_sentiment(analysis_id):
try:
return (
Tweet
.select(fn.SUM(Tweet.sentiment == 1).alias("positive"),
fn.SUM(Tweet.sentiment == -1).alias("negative"))
.where(Tweet.analysis == analysis_id)
.get()
)
except Tweet.DoesNotExist:
return None
except Exception as ex:
raise ex
def summary_report(cls):
query = Donor.select(Donor,
fn.COUNT(Donation.amount).alias('count'),
fn.SUM(Donation.amount).alias('sum')) \
.join(Donation) \
.group_by(Donor) \
.order_by(-fn.SUM(Donation.amount))
report = "DONOR NAME TOTAL DONATED NUM DONATIONS AVG DONATION\n"
for item in query:
report += f"{item.name:20s} ${item.sum:12,.2f} {item.count:3d}" \
+ f" ${item.sum/item.count:11,.2f}\n"
return report
def scalped_positions_report():
markets = [
lp.market for lp in LongPosition.select(LongPosition.market).where(
LongPosition.scalped_quantity.is_null(False)).distinct()
]
results = []
result_str = "Scalped Positions:\n"
total_net = Decimal('0.0')
total_spent = Decimal('0.0')
for market in markets:
current_price = Candle.select().where(
Candle.market == market).order_by(
Candle.timestamp.desc()).limit(1)[0].close
(num_positions, spent, quantity_scalped) = LongPosition.select(
fn.COUNT(LongPosition.id),
fn.SUM(LongPosition.buy_quantity * LongPosition.purchase_price),
fn.SUM(LongPosition.scalped_quantity)).where(
LongPosition.market == market,
LongPosition.sell_timestamp.is_null(False)).scalar(
as_tuple=True)
quantity = Decimal(quantity_scalped).quantize(Decimal('0.00000001'))
spent = Decimal(spent).quantize(Decimal('0.00000001'))
current_value = (quantity * current_price).quantize(
Decimal('0.00000001'))
total_net += current_value
total_spent += spent
results.append({
"market": market,
"num_positions": num_positions,
"spent": spent,
"current_value": current_value,
"quantity": quantity.normalize()
})
total_net = total_net.quantize(Decimal('0.00000001'))
total_spent = total_spent.quantize(Decimal('0.00000001'))
for result in sorted(results,
key=lambda i: i['current_value'],
reverse=True):
result_str += f"{'{:>8}'.format(result['market'])}: current_value {'{:>10}'.format(str(result['current_value']))} | {'{:>6f}'.format(result['quantity'])} | {result['num_positions']:3d}\n"
result_str += f"{'-' * 49}\n"
result_str += f" total: {'{:>10}'.format(str(total_net))}\n"
return result_str
def statistics():
"""Returns statistics about Blocks table
"""
stats = Transaction.select(
fn.COUNT(Transaction.id),
fn.SUM(Transaction.fee),
fn.SUM(Transaction.amount),
).scalar(as_tuple=True)
return {
"transactions_count": stats[0],
"total_fee": stats[1],
"total_amount": stats[2],
}
def get(self, call_id: int):
try:
call = Call.get_by_id(call_id)
except Call.DoesNotExist:
raise BadRequest("invalid call_id")
call_emotions_total = list(
Emotions.select(
peewee_fn.SUM(Emotions.anger).alias("anger_total"),
peewee_fn.SUM(Emotions.contempt).alias("contempt_total"),
peewee_fn.SUM(Emotions.disgust).alias("disgust_total"),
peewee_fn.SUM(Emotions.fear).alias("fear_total"),
peewee_fn.SUM(Emotions.happiness).alias("happiness_total"),
peewee_fn.SUM(Emotions.neutral).alias("neutral_total"),
peewee_fn.SUM(Emotions.sadness).alias("sadness_total"),
peewee_fn.SUM(Emotions.surprise).alias("surprise_total"),
).where(Emotions.call == call).execute())[0]
emotions = {
"raiva": call_emotions_total.anger_total or 0.0,
"desprezo": call_emotions_total.contempt_total or 0.0,
"desgosto": call_emotions_total.disgust_total or 0.0,
"medo": call_emotions_total.fear_total or 0.0,
"felicidade": call_emotions_total.happiness_total or 0.0,
"neutro": call_emotions_total.neutral_total or 0.0,
"tristeza": call_emotions_total.sadness_total or 0.0,
"surpresa": call_emotions_total.surprise_total or 0.0,
}
emotions_keys = list(emotions.keys())
emotions_values = np.asarray(list(emotions.values()), dtype=np.float32)
emotions_values /= emotions_values.max()
response = make_response(bar_chart(emotions_keys, emotions_values))
response.headers.set("Content-Type", "image/png")
return response
def minerstat24h(query_name):
t = int(time.time()) - 86400
s = get_format_datetime(t)
infos = []
for i in range(3):
if i == 0:
if '.' not in query_name:
continue
l = query_name.split('.', 1)
infos = TblStatInfoDetail30m.select(fn.SUM(TblStatInfoDetail30m.totaldiff).alias('totaldiffsum'),
fn.SUM(TblStatInfoDetail30m.validcount).alias('validcountsum'),
fn.SUM(TblStatInfoDetail30m.invalidcount).alias('invalidcountsum'),
TblStatInfoDetail30m.periodtime)\
.where(TblStatInfoDetail30m.periodtime > s, TblStatInfoDetail30m.uname == l[0], TblStatInfoDetail30m.worker == l[1])\
.group_by(TblStatInfoDetail30m.periodtime).order_by(TblStatInfoDetail30m.periodtime).execute()
if len(infos) > 0:
break
elif i == 1:
infos = TblStatInfoDetail30m.select(fn.SUM(TblStatInfoDetail30m.totaldiff).alias('totaldiffsum'),
fn.SUM(TblStatInfoDetail30m.validcount).alias('validcountsum'),
fn.SUM(TblStatInfoDetail30m.invalidcount).alias('invalidcountsum'),
TblStatInfoDetail30m.periodtime) \
.where(TblStatInfoDetail30m.periodtime > s, TblStatInfoDetail30m.uname == query_name)\
.group_by(TblStatInfoDetail30m.periodtime).order_by(TblStatInfoDetail30m.periodtime).execute()
if len(infos) > 0:
break
else:
infos = TblStatInfoDetail30m.select(fn.SUM(TblStatInfoDetail30m.totaldiff).alias('totaldiffsum'),
fn.SUM(TblStatInfoDetail30m.validcount).alias('validcountsum'),
fn.SUM(TblStatInfoDetail30m.invalidcount).alias('invalidcountsum'),
TblStatInfoDetail30m.periodtime) \
.where(TblStatInfoDetail30m.periodtime > s, TblStatInfoDetail30m.worker == query_name) \
.group_by(TblStatInfoDetail30m.periodtime).order_by(TblStatInfoDetail30m.periodtime).execute()
if len(infos) > 0:
break
stat_24h_list = []
for info in infos:
sharesdiff = float(info.totaldiffsum)
hashrate = float(UfoDiff.get_hash_rate_by_diff(sharesdiff, 1800, None)) / 1000 / 1000
validcount = int(info.validcountsum)
invalidcount = int(info.invalidcountsum)
periodtime = str(info.periodtime)
stat_24h_list.append(
{
"sharesdiff": "%.06f" % sharesdiff,
"hashrate": "%.06f" % hashrate,
"validcount": validcount,
"invalidcount": invalidcount,
"periodtime": periodtime
}
)
return json.dumps(stat_24h_list)
def extend_rows(self):
self.parent.btt_export.setEnabled(True)
nb_rows = self.rowCount()
date = self.parent.date_.text()
self.setRowCount(nb_rows + 4)
self.amount_ht = 0
for row_num in xrange(0, self.data.__len__()):
mtt = is_int(self.item(row_num, 3).text())
self.amount_ht += mtt
row_num += 1
self.setItem(row_num, 2, TotalsWidget(u"Total vente : "))
self.amount_apricot = self.amount_ht
self.setItem(
row_num, 3,
TotalsWidget(formatted_number(formatted_number(self.amount_ht))))
row_num += 1
self.setItem(row_num, 2, TotalsWidget(u"Dette du jour : "))
self.total_debt = Refund.select(fn.SUM(Refund.amount)).where(
Refund.type_ == Refund.DT,
Refund.date < date_on_or_end(date, on=False),
Refund.date > date_on_or_end(date)).scalar() or 0
if self.total_debt:
self.amount_apricot -= self.total_debt
self.setItem(
row_num, 3,
TotalsWidget(formatted_number(formatted_number(self.total_debt))))
row_num += 1
self.setItem(row_num, 2, TotalsWidget(u"Dette reglée : "))
self.total_refund = Refund.select(fn.SUM(Refund.amount)).where(
Refund.type_ == Refund.RB,
Refund.date < date_on_or_end(date, on=False),
Refund.date > date_on_or_end(date)).scalar() or 0
if self.total_refund:
self.amount_apricot += self.total_refund
self.setItem(
row_num, 3,
TotalsWidget(formatted_number(formatted_number(
self.total_refund))))
row_num += 1
self.setItem(row_num, 2, TotalsWidget(u"Caise : "))
self.setItem(
row_num, 3,
TotalsWidget(
formatted_number(formatted_number(self.amount_apricot))))
self.setSpan(nb_rows, 0, 4, 2)
def get_wh_self_per():
total_count = Commodity.select(fn.SUM(
Commodity.total).alias('tc')).dicts()[0]['tc']
self_count = Commodity.select(fn.SUM(Commodity.total).alias('tc')).where(
Commodity.is_self == True).dicts()[0]['tc']
non_self_count = Commodity.select(fn.SUM(
Commodity.total).alias('tc')).where(
Commodity.is_self == False).dicts()[0]['tc']
WHSelfPer.create(
total_count=total_count,
self_count=self_count,
self_percentage=calculate_percentage(total_count, self_count),
non_self_count=non_self_count,
non_self_percentage=calculate_percentage(total_count, non_self_count))
def get(self, match_id):
keyword = self.get_argument("kw", "")
match = Match.get_or_404(id=match_id)
query = MatchMember.query_all_members(match_id)
group_query = MatchGroup.select() \
.where(MatchGroup.match_id == match_id)
self.logger.debug(group_query.sql())
groups = []
for group in group_query:
groups.append({"name": group.name})
group_name = self.get_argument("group_name", "")
if group_name:
query = query.where(MatchGroup.name == group_name)
if keyword:
if is_mobile(keyword):
query = query.where(MatchMember.mobile == keyword)
else:
query = query.where(MatchMember.name.contains(keyword))
members = self.paginate_query(query)
sum_fee = MatchMember.select(
fn.SUM(MatchMember.total_fee).alias("sum")).where(
MatchMember.match_id == match_id).scalar()
self.render("match/match_members_list.html",
sum_fee=sum_fee,
match=match,
groups=groups,
members=members,
pagination=members.pagination)
def get_links(keyword):
""" Retrieves all the links for the tokens of keyword
Args:
keyword (str): A string of tokens
Returns:
list of all link details
"""
tokens = keyword.lower().split()
related_links = defaultdict(dict)
i = 0
for token in tokens:
for record in Links.select(Links.link, fn.SUM(KeywordsLink.occurrence).alias("key_count"))\
.join(KeywordsLink, on=(Links.id == KeywordsLink.link_id))\
.dicts()\
.where(KeywordsLink.keyword.contains(token))\
.group_by(Links.link):
related_links[str(i)]['link'] = record['link']
related_links[str(i)]['count'] = record['key_count']
i += 1
if related_links:
links = ranking_links(related_links)
return links
else:
return related_links
def api_stat_site_top_room_in_date_range(start, end):
try:
start = datetime.strptime(start, '%Y-%m-%d').date()
end = datetime.strptime(end, '%Y-%m-%d').date()
limit = int(request.args.get('limit', 20))
limit = limit if limit > 0 else 20
except ValueError:
return jsonify({'code': 1, 'msg': 'invalid request'})
query = RoomDailyStat.select(
RoomDailyStat.room,
RoomDailyStat.dcount, RoomDailyStat.gcount, RoomDailyStat.income,
fn.SUM((RoomDailyStat.dcount * 100 + RoomDailyStat.income)).alias('f')
).join(Date, on=(RoomDailyStat.date == Date.date_key)) \
.where((Date.date >= start) & (Date.date <= end)) \
.group_by(RoomDailyStat.room) \
.order_by(SQL('f').desc()) \
.limit(limit)
payload = []
order = 0
for row in query:
payload.append({
'roomId': row.room.room_id,
'roomName': row.room.name,
'dcount': row.dcount,
'gcount': row.gcount,
'income': int(row.income / 100),
'factor': int(row.f / 100),
'order': order
})
order += 1
return jsonify({'code': 0, 'msg': 'success', 'data': payload})
def _find_next_steak(self):
# Seems like queries cannot be reused...
## Prepare query to search for the next available steak
# Create subquery that counts the number of grills (max 1) to hold a spice
subquery = SteakSpice.select(
SteakSpice.spice.alias('spice'),
fn.COUNT(GrillSpice.id.distinct()).alias('in_use'))
subquery = subquery.join(GrillSpice,
JOIN.LEFT_OUTER,
on=(SteakSpice.spice == GrillSpice.spice))
subquery = subquery.group_by(SteakSpice.spice)
# Select all raw steaks without a grill and attach a flag whether any spices are in use
query = Steak.select(
Steak.id.alias('steak_id'),
fn.COALESCE(fn.SUM(subquery.c.in_use), 0).alias('spice_in_use'))
query = query.join(SteakGrill, JOIN.LEFT_OUTER)
query = query.switch(Steak).join(SteakSpice, JOIN.LEFT_OUTER)
query = query.join(subquery,
JOIN.LEFT_OUTER,
on=subquery.c.spice == SteakSpice.spice).group_by(
Steak.id)
query = query.where(Steak.status == STATUS_RAW).where(
SteakGrill.grill == None)
query = query.where(Steak.recipe.in_(self.recipe_names))
# Select the steaks with no spices in use
steak_query = Steak.select().join(
query, JOIN.INNER,
on=(Steak.id == query.c.steak_id)).where(query.c.spice_in_use == 0)
# Return oldest steak
return steak_query.order_by(Steak.created).first()
def get(self, match_id):
match = Match.get_or_404(id=match_id)
query = MatchMember.select(MatchMember, MatchGroup).join(
MatchGroup,
join_type=JOIN_LEFT_OUTER,
on=(MatchMember.group_id == MatchGroup.id).alias("group")).where(
MatchMember.match_id == match.id).order_by(
MatchMember.id.desc())
group_query = MatchGroup.select() \
.where(MatchGroup.match_id == match_id)
self.logger.debug(group_query.sql())
groups = []
for group in group_query:
groups.append({"name": group.name})
group_name = self.get_argument("group_name", "")
if group_name:
query = query.where(MatchGroup.name == group_name)
members = self.paginate_query(query)
sum_fee = MatchMember.select(
fn.SUM(MatchMember.total_fee).alias("sum")).where(
MatchMember.match_id == match_id).scalar()
self.render("match/match_members_list.html",
sum_fee=sum_fee,
groups=groups,
match=match,
members=members,
pagination=members.pagination)
def get_brand_percentage():
# redmi = BrandPercentage.get(
# BrandPercentage.main_brand == '小米',
# BrandPercentage.sub_brand == '红米'
# )
# xiaomi = BrandPercentage.get(
# BrandPercentage.main_brand == '小米',
# BrandPercentage.sub_brand == '小米'
# )
# for phone in Phone.select().where(Phone.brand == '小米'):
# if '小米' in phone.model:
# xiaomi.total += phone.total
# xiaomi.save()
# if '红米' in phone.model:
# redmi.total += phone.total
# redmi.save()
#
# vivo = BrandPercentage.get(
# BrandPercentage.main_brand == 'vivo',
# BrandPercentage.sub_brand == 'vivo'
# )
# iqoo = BrandPercentage.get(
# BrandPercentage.main_brand == 'vivo',
# BrandPercentage.sub_brand == 'iQOO'
# )
# for phone in Phone.select().where(Phone.brand == 'vivo'):
# if 'iQOO' in phone.model:
# iqoo.total += phone.total
# iqoo.save()
# else:
# vivo.total += phone.total
# vivo.save()
#
# oppo = BrandPercentage.get(
# BrandPercentage.main_brand == 'OPPO',
# BrandPercentage.sub_brand == 'OPPO'
# )
# realme = BrandPercentage.get(
# BrandPercentage.main_brand == 'OPPO',
# BrandPercentage.sub_brand == 'realme'
# )
# oneplus = BrandPercentage.get(
# BrandPercentage.main_brand == 'OPPO',
# BrandPercentage.sub_brand == '一加'
# )
# oppo.total = Phone.select(fn.SUM(Phone.total).alias('tc')).where(Phone.brand == 'OPPO').dicts()[0]['tc']
# oppo.save()
# realme.total = Phone.select(fn.SUM(Phone.total).alias('tc')).where(Phone.brand == 'realme').dicts()[0]['tc']
# realme.save()
# oneplus.total = Phone.select(fn.SUM(Phone.total).alias('tc')).where(Phone.brand == '一加').dicts()[0]['tc']
# oneplus.save()
brand_list = ['小米', 'OPPO', 'vivo']
for brand in brand_list:
total_count = BrandPercentage.select(fn.SUM(BrandPercentage.total).alias('tc')) \
.where(BrandPercentage.main_brand == brand).dicts()[0]['tc']
print(total_count)
for bp in BrandPercentage.select().where(BrandPercentage.main_brand == brand):
bp.percentage = calculate_percentage(total_count, bp.total)
bp.save()
def get_wh_price_and_sales():
for commodity in Commodity.select():
if 0 < commodity.price < 100:
whpas = WHPriceAndSales.get_by_id('0-100元')
whpas.total += commodity.total
whpas.save()
if 100 <= commodity.price < 200:
whpas = WHPriceAndSales.get_by_id('100-200元')
whpas.total += commodity.total
whpas.save()
if 200 <= commodity.price < 500:
whpas = WHPriceAndSales.get_by_id('200-500元')
whpas.total += commodity.total
whpas.save()
if 500 <= commodity.price < 900:
whpas = WHPriceAndSales.get_by_id('500-900元')
whpas.total += commodity.total
whpas.save()
if 900 <= commodity.price < 2000:
whpas = WHPriceAndSales.get_by_id('900-2000元')
whpas.total += commodity.total
whpas.save()
if commodity.price >= 2000:
whpas = WHPriceAndSales.get_by_id('2000元以上')
whpas.total += commodity.total
whpas.save()
total_count = WHPriceAndSales.select(
fn.SUM(WHPriceAndSales.total).alias('tc')).dicts()[0]['tc']
print(total_count)
for whpas in WHPriceAndSales.select():
whpas.percentage = calculate_percentage(total_count, whpas.total)
whpas.save()
