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()