def create_country_stats(session, date, logger=None): if logger is None: logger = app.logger (start, end) = date_to_timestamps(date) # First kill the stats for the selected date deleted_counter = session.query(CountryStats).filter( CountryStats.date == date).delete() country_stats = (session.query( literal(date), Country.gid, func.count(AircraftBeacon.timestamp).label("aircraft_beacon_count"), func.count(func.distinct( AircraftBeacon.receiver_id)).label("device_count")).filter( between(AircraftBeacon.timestamp, start, end)).filter( func.st_contains(Country.geom, AircraftBeacon.location)).group_by( Country.gid).subquery()) # And insert them ins = insert(CountryStats).from_select([ CountryStats.date, CountryStats.country_id, CountryStats.aircraft_beacon_count, CountryStats.device_count ], country_stats) res = session.execute(ins) insert_counter = res.rowcount session.commit()
def _generate_monthly_uniques(self, table, tables): idx = tables.index(table) # Join them all at = union_all(*[ select([tbl]).where(tbl.c.message == "Ack") for tbl in tables[idx-29:idx+1] ]) # Get uniques s = select([ func.count(func.distinct(at.c.uaid_hash)). label("unique_count"), at.c.browser_os, at.c.browser_version, ]).\ group_by(at.c.browser_os, at.c.browser_version) results = self._conn.execute(s).fetchall() if not results: return # Determine the date for this entry tname = table.name date_parts = [tname[-8:-4], tname[-4:-2], tname[-2:]] insert_date = "-".join(date_parts) self._conn.execute(monthly_rollup.insert(), [ dict(date=insert_date, count=x.unique_count, browser_os=x.browser_os, browser_version=x.browser_version) for x in results ])
def _filter_events(klass, events, query=None, categories=None, tags=None, flags=None): query_tags = None if query: tags_matching_query = Tag.query.filter( Tag.tag_name.ilike("{}%".format(query))) query_tags = {t.tag_name for t in tags_matching_query} if query_tags: if tags is None: tags = query_tags else: tags |= query_tags else: events = klass._filter_events_by_query(events=events, query=query) if tags or categories: # Filter out valid_tags valid_tags = db_session.query(func.distinct(Tag.tag_name)).filter( Tag.tag_name.in_(tags)) tags = set(t[0] for t in valid_tags) events = klass._filter_events_by_tags(events, tags=tags, categories=categories) if flags: events = klass._filter_events_by_flags(events, flags=flags) return events, tags
def __init__(self, doc_ids): # we use these to filter our queries, rather than trying to pull # complex filter logic into our view queries self.doc_ids = doc_ids self.formats = {} # map from a score name to its row in the score sheet self.score_row = {} self.n_columns = 0 # collect media headings medium_ids = self.filter(db.session.query(func.distinct(Document.medium_id))) self.media = Medium.query\ .filter(Medium.id.in_(medium_ids))\ .order_by(Medium.name)\ .all() self.n_columns = len(self.media) self.score_col_start = 3 # how nested are the ratings? def depth(ratings, deep=1): for rating in ratings: if len(rating) < 3: yield deep else: yield max(depth(rating[2], deep + 1)) # the column at which the ratings for each medium starts self.rating_col_start = (max(depth(self.ratings)) - 1) * 2
def get_user_count(self, ndays=7): """ Fetch statistic in the ndays @return { date: { user_count, question_count }, date: { user_count, question_count }... } """ results = {} start_t = datetime.datetime.now() start_t -= datetime.timedelta(days=ndays) rows = self._sess.query(func.date(Histories.CreateAt), \ func.count(func.distinct(Histories.DeviceId)), \ func.count(Histories.id)) \ .filter(Histories.CreateAt >= func.date(start_t)) \ .group_by(func.date(Histories.CreateAt)) \ .all() for row in rows: (date, user_cnt, q_cnt) = row results[date] = {"question_count": q_cnt, "user_count": user_cnt} return results
def exact_count(query): 'usable before sa0.5rc4' from sqlalchemy.sql import func m = mapper_of_query(query) #see Query.count() return query._col_aggregate(m.primary_key[0], lambda x: func.count(func.distinct(x)))
def exact_count(query): "usable before sa0.5rc4" from sqlalchemy.sql import func m = mapper_of_query(query) # see Query.count() return query._col_aggregate(m.primary_key[0], lambda x: func.count(func.distinct(x)))
def generate_summary_data(user): ret = [] user_query = Record.query.filter_by(user=user) years = user_query.with_entities(func.distinct(Record.year)).all() for year in years: months = user_query.filter_by(year=year[0]).with_entities( func.distinct(Record.month)).all() for month in months: node = { "year": year[0], "month": month[0], "worktime": user_query.filter_by( year=year[0], month=month[0]).with_entities(func.sum(Record.hours) ).first()[0] } ret.append(node) return ret
def getMinMaxYearByELP(session, COD_ELP, listeInds): retour = {"min": 0, "max": 0, "ecart": 0} CI = aliased(tables.ConnexionINDSQLITE) minMaxYear = session.query(func.distinct(CI.DATE_CONN)).filter(CI.SESAME_ETU.in_(listeInds)).order_by(CI.DATE_CONN) if minMaxYear: retour["min"] = int(minMaxYear[0][0].split("/")[0]) retour["max"] = int(minMaxYear[-1][0].split("/")[0]) retour["ecart"] = retour["max"] - retour["min"] return retour
def count_trackback_pings(paper_id: str) -> int: """Count trackback pings for a particular document (paper_id).""" row = db.session.query( func.count(func.distinct(TrackbackPing.url)).label('num_pings') ).filter(TrackbackPing.document_id == Document.document_id) \ .filter(Document.paper_id == paper_id) \ .filter(TrackbackPing.status == 'accepted').first() return int(row.num_pings)
def customers(session, date): """ Method returns the number of distinct customers on a given date :param session: database connection session :param date: date string in YYYY-MM-DD format :return: number of distinct customer ids on a given day """ return len( session.query(func.distinct(Orders.customer_id)) .filter(Orders.created_at.contains(date)) .all() )
def commissions_order_average(session, date, total): """ :param session: Database connection session :param date: Date string in YYYY-MM-DD format :param total: The total commissions on the specified date :return: Returns the average commission paid per order on a given date """ return ( total / session.query(func.count(func.distinct(Orders.id))) .filter(Orders.created_at.contains(date)) .first()[0] )
def count(cls, session, params, conditions=[], distinct=False): u"""计数. eg: BaseModel.count([BaseModel.id, BaseModel.XXX], [BaseModel.id==2]) BaseModel.count(BaseModel.id, [BaseModel.id==2], True) """ if distinct: if isinstance(params, Iterable) and len(params) >= 2: re = session.query(func.count( func.distinct(func.concat(*params))))\ .filter(*conditions).one()[0] elif isinstance(params, Iterable): qp = params[0] re = session.query(func.count( func.distinct(qp))).filter(*conditions).one()[0] else: re = session.query(func.count( func.distinct(params))).filter(*conditions).one()[0] else: if not isinstance(params, Iterable): params = [params] re = session.query(*params).filter(*conditions).count() return re
def find_device(current_user): device_ids = [ device_id for device_id, in db.session.query( func.distinct(Move.device_id)).join(User).join(Device).filter( Device.name != gpx_import.GPX_DEVICE_NAME).filter( Move.user == current_user).all() ] if not device_ids: return None assert len(device_ids) == 1 device_id = device_ids[0] device = db.session.query(Device).filter_by(id=device_id).one() return device
def make_facets(parent_alias, filter_func, parser_func): """ Return a set of facets based on the current query string. This will also consider filters set for the query, i.e. only show facets that match the current set of filters. """ facets = {} for facet in request.args.getlist('facet'): parent_obj = parent_alias() q = db.session.query() facet_count = func.count(func.distinct(parent_obj.id)) q = q.add_columns(facet_count) q = q.order_by(facet_count.desc()) q = filter_func(q, parent_obj) q = parser_func(parent_obj, facet, facet, q) facets[facet] = Pager(q, name='facet_%s' % facet, results_converter=results_process) return facets
def overview(): if request.method == "GET": userid = int(session.get("userid")) overview = {} # 投资中金额,最大年利率,待收益,投资中的平台数去重复 investinfo = db.session.query( func.sum(Invest.money).label("investment_money"), func.max(Invest.profit).label("investment_max_profit"), func.sum(Invest.lucre).label("investment_lucre"), func.count(func.distinct(Invest.id)).label("count_p2p")).filter_by( user_id=userid, status=0).first() # 7天内即将到期 count_expire = db.session.query( func.count(Invest.id).label("expiring_invest")).filter( Invest.user_id == userid, Invest.status == 0, Invest.end_time >= (datetime.now() - timedelta(days=7))).first() # 投资已到期,需要确认 expire_invest = db.session.query( func.count(Invest.id).label("expire_invest")).filter( Invest.user_id == userid, Invest.status == 1, Invest.end_time >= datetime.now()).first() # 提现,充值金额 in_money = db.session.query( func.sum(BillFlow.money).label("in_money")).filter_by( user_id=userid, status=1, type=0).first() out_money = db.session.query( func.sum(BillFlow.money).label("out_money")).filter_by( user_id=userid, status=1, type=1).first() # 最近登录时间,ip loginfo = db.session.query(Loginlog.ip, Loginlog.addtime).filter_by( user_id=userid).order_by(Loginlog.id.desc()).first() print(in_money.in_money) overview["last_login_ip"] = loginfo.ip overview["last_login_time"] = str(loginfo.addtime) overview["in_money"] = float(in_money.in_money) overview["out_money"] = float(out_money.out_money) overview["investment_money"] = float(investinfo.investment_money) overview["investment_max_profit"] = float( investinfo.investment_max_profit) overview["investment_lucre"] = float(investinfo.investment_lucre) overview["count_p2p"] = int(investinfo.count_p2p) overview["expiring_invest"] = int(count_expire.expiring_invest) overview["expire_invest"] = int(expire_invest.expire_invest) return jsonify(overview)
def _query_weekly_average(self, table, tables): # First see if we can find 6 days prior for a full week idx = tables.index(table) # For Python list math, 6 has 6 numbers before it as zero index # based, so 6 or larger is needed if idx < 6: return None # Get our weekly set together # Note that we add one to idx since list splicing needs one higher than # the index for right-side inclusive week_tables = union_all(*[ select([tbl]).where(tbl.c.message == "Ack") for tbl in tables[idx-6:idx+1] ]) # Calculate channels per user for the past week chans_per_user = select([ week_tables.c.uaid_hash, func.count(func.distinct(week_tables.c.channel_id)).label("count") ]).\ group_by(week_tables.c.uaid_hash) # Rank them into ntiles ranked = select([ chans_per_user.c.uaid_hash, chans_per_user.c.count, func.ntile(100).over(order_by=text("count ASC")).label("rank"), ]) # Remove the bottom/upper 5%, get sum/count for avg weekly_channels_stats = select([ func.sum(ranked.c.count), func.count(ranked.c.uaid_hash), ]).\ where(ranked.c.rank > 5).\ where(ranked.c.rank <= 95) sums, count = self._conn.execute(weekly_channels_stats).fetchone() weekly_avg = Decimal(sums) / Decimal(count) return weekly_avg
def getLeaderboard(): leaderboard = [] sortedPlayers = Players.query.filter(Players.Ranking != 0).order_by(Players.Ranking).all() for player in sortedPlayers: row = {} row["First Name"] = player.FirstName row["Last Name"] = player.LastName row["Ranking"] = player.Ranking row["Series Wins"] = player.SeriesWins row["Game Wins"] = player.GameWins row["Game Losses"] = player.TotalGamesPlayed - player.GameWins row["Points Scored"] = player.TotalPoints row["Shutouts"] = player.Shutouts row["Game Win %"] = round(100.0*player.GameWins / player.TotalGamesPlayed,2) numSeriesPlayed = db.session.query(func.distinct(Series.Id)).join(History).filter(History.PlayerId == player.Id).count() row["Series Win %"] = round(100.0*player.SeriesWins / numSeriesPlayed,2) row["Avg Points/Game"] = round(player.TotalPoints / player.TotalGamesPlayed,2) sumWinMargin = db.session.query(func.sum(Games.WinMargin)).join(History).filter(Games.Winner == History.Side).filter(History.PlayerId == player.Id).scalar() row["Avg Win Margin"] = round(sumWinMargin / player.TotalGamesPlayed,2) leaderboard.append(row) return json.dumps(leaderboard)
def get_events(self, query=None, categories=None, tags=None, cities=None, flags=None, page=1, future_only=False): current_user = UserController().current_user selected_categories = set(categories.split(',') if categories else []) selected_tags = set(tags.split(',') if tags else []) events_with_counts = db_session.query( Event, func.count(func.distinct( UserEvent.user_id)).label('ct')).outerjoin( UserEvent, UserEvent.event_id == Event.event_id).group_by( Event.event_id).order_by(desc('ct')) if current_user: current_user_events_table = alias(current_user.user_events(), 'current_user_events_table') events_with_counts = events_with_counts.filter(~Event.event_id.in_( db_session.query( current_user_events_table.c.user_events_event_id))) results, categories, tags, event_cities, results_table = self._process_events( events=events_with_counts, cities=cities, page=page, query=query, user=current_user, selected_categories=selected_categories, selected_tags=selected_tags, flags=flags, future_only=future_only) return results, categories, tags, event_cities
def _generate_daily_uniques(self, table): s = select([ func.count(func.distinct(table.c.uaid_hash)). label("unique_count"), cast(table.c.timestamp, Date).label("date"), table.c.browser_os, table.c.browser_version, ]).\ where(table.c.message == "Ack").\ group_by("date", table.c.browser_os, table.c.browser_version) results = self._conn.execute(s).fetchall() if not results: return # Now build the insert for the rollup table, and insert self._conn.execute(daily_rollup.insert(), [ dict(count=x.unique_count, date=x.date, browser_os=x.browser_os, browser_version=x.browser_version) for x in results ])
def update_entries(session, date, logger=None): """Create receiver coverage stats for Melissas ognrange.""" if logger is None: logger = app.logger logger.info("Compute receiver coverages.") (start, end) = date_to_timestamps(date) # Filter aircraft beacons sq = (session.query( AircraftBeacon.location_mgrs_short, AircraftBeacon.receiver_id, AircraftBeacon.signal_quality, AircraftBeacon.altitude, AircraftBeacon.device_id).filter( and_(between(AircraftBeacon.timestamp, start, end), AircraftBeacon.location_mgrs_short != null(), AircraftBeacon.receiver_id != null(), AircraftBeacon.device_id != null())).subquery()) # ... and group them by reduced MGRS, receiver and date query = (session.query( sq.c.location_mgrs_short, sq.c.receiver_id, func.cast(date, Date).label("date"), func.max(sq.c.signal_quality).label("max_signal_quality"), func.min(sq.c.altitude).label("min_altitude"), func.max(sq.c.altitude).label("max_altitude"), func.count(sq.c.altitude).label("aircraft_beacon_count"), func.count(func.distinct(sq.c.device_id)).label("device_count"), ).group_by(sq.c.location_mgrs_short, sq.c.receiver_id).subquery()) # if a receiver coverage entry exist --> update it upd = (update(ReceiverCoverage).where( and_( ReceiverCoverage.location_mgrs_short == query.c.location_mgrs_short, ReceiverCoverage.receiver_id == query.c.receiver_id, ReceiverCoverage.date == date)).values({ "max_signal_quality": query.c.max_signal_quality, "min_altitude": query.c.min_altitude, "max_altitude": query.c.max_altitude, "aircraft_beacon_count": query.c.aircraft_beacon_count, "device_count": query.c.device_count, })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.debug( "Updated receiver coverage entries: {}".format(update_counter)) # if a receiver coverage entry doesnt exist --> insert it new_coverage_entries = session.query(query).filter(~exists().where( and_( ReceiverCoverage.location_mgrs_short == query.c.location_mgrs_short, ReceiverCoverage.receiver_id == query.c.receiver_id, ReceiverCoverage.date == date))) ins = insert(ReceiverCoverage).from_select( ( ReceiverCoverage.location_mgrs_short, ReceiverCoverage.receiver_id, ReceiverCoverage.date, ReceiverCoverage.max_signal_quality, ReceiverCoverage.min_altitude, ReceiverCoverage.max_altitude, ReceiverCoverage.aircraft_beacon_count, ReceiverCoverage.device_count, ), new_coverage_entries, ) result = session.execute(ins) insert_counter = result.rowcount session.commit() finish_message = "ReceiverCoverage: {} inserted, {} updated".format( insert_counter, update_counter) logger.debug(finish_message) return finish_message
def topsku_week_table_js(): date = datetime.strptime(request.form["sku_date"], "%Y-%m-%d").date() hour = int(request.form["hour"]) weekday = date.weekday() start_date = date - relativedelta(weeks=4, weekday=weekday) end_date = date # if start_date == "" and end_date == "" and date.today().weekday() != 6: # date_today = date.today() # start_date = date_today - relativedelta(weeks=4, weekday=MO(-1)) # end_date = start_date + relativedelta(weeks=3, weekday=SU(1)) # elif start_date == "" and end_date == "" and date.today().weekday() == 6: # date_today = date.today() # start_date = date_today - relativedelta(weeks=4, weekday=MO(-1)) # end_date = date_today # else: # start_date = datetime.strptime(start_date,"%Y-%m-%d").date() + relativedelta(weekday=MO(-1)) # end_date = datetime.strptime(end_date,"%Y-%m-%d").date() + relativedelta(weekday=SU(1)) brands = db.session.query(func.distinct(top_sku_talendfc.brand))\ .filter(and_(top_sku_talendfc.txn_date >= start_date, top_sku_talendfc.txn_date <= end_date, func.weekday(top_sku_talendfc.txn_date) == weekday, top_sku_talendfc.processing_hr == hour)) lookup = db.session.query(top_sku_talendfc.txn_date,top_sku_talendfc.brand,func.sum(top_sku_talendfc.txn_amount))\ .filter(and_(top_sku_talendfc.txn_date >= start_date, top_sku_talendfc.txn_date <= end_date, func.weekday(top_sku_talendfc.txn_date) == weekday, top_sku_talendfc.processing_hr == hour))\ .group_by(top_sku_talendfc.txn_date,top_sku_talendfc.brand) brands = [b[0] for b in brands] + ["TOTAL"] sku_dict = {} for l in lookup.all(): key = l[0].strftime("%Y-%m-%d") if key not in sku_dict.keys(): sku_dict[key] = { "brands": dict.fromkeys(brands, None), } sku_dict[key]["brands"][l[1]] = str(l[2]) if sku_dict[key]["brands"]["TOTAL"] == None: sku_dict[key]["brands"]["TOTAL"] = 0 sku_dict[key]["brands"]["TOTAL"] += l[2] else: if sku_dict[key]["brands"]["TOTAL"] == None: sku_dict[key]["brands"]["TOTAL"] = 0 sku_dict[key]["brands"][l[1]] = str(l[2]) sku_dict[key]["brands"]["TOTAL"] += l[2] # for l in lookup.all(): # cur_week = "" # if l[1] not in sku_dict.keys(): # sku_dict[l[1]] = { # "start_date": None, # "end_date": None, # "brands": dict.fromkeys(brands,None), # } # insert_sku_table(l,sku_dict[l[1]]) # aggregate_sku_table(l,sku_dict[l[1]]) # else: # insert_sku_table(l,sku_dict[l[1]]) # aggregate_sku_table(l,sku_dict[l[1]]) formatted_data = {"columns": ["Dates"] + brands, "data": []} for k in sku_dict.keys(): sku = dict.fromkeys(formatted_data["columns"]) sku["Dates"] = k for b in sku_dict[k]["brands"].keys(): sku[b] = str(sku_dict[k]["brands"][b]) formatted_data["data"].append(sku) # for k in sku_dict.keys(): # sku = dict.fromkeys(formatted_data["columns"]) # sku["Dates"] = str(sku_dict[k]["start_date"]) + " to " + str(sku_dict[k]["end_date"]) # for b in sku_dict[k]["brands"].keys(): # sku[b] = str(sku_dict[k]["brands"][b]) # formatted_data["data"].append(sku) # print(formatted_data) return jsonify(formatted_data)
def __init__(self, group_strategy, timeseries_unit='day', date_filter_attributes=None): self.filterable_attributes = [ DATE, CUSTOM_ATTRIBUTE, TRANSFER_ACCOUNT, USER ] self.timeseries_unit = timeseries_unit self.date_filter_attributes = date_filter_attributes self.metrics = [] # Timeseries Metrics if group_strategy: users_created_timeseries_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(User.id).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[User]).label('date'), group_strategy.group_by_column)\ .group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[User])), User) aggregated_users_created_query = group_strategy\ .build_query_group_by_with_join(db.session.query(func.count(User.id).label('volume'), group_strategy.group_by_column), User) else: users_created_timeseries_query = db.session.query(func.count(User.id).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[User]).label('date'))\ .group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[User])) aggregated_users_created_query = None total_users_created_query = db.session.query( func.count(User.id).label('volume')) self.metrics.append( metric.Metric( metric_name='users_created', is_timeseries=True, query=users_created_timeseries_query, aggregated_query=aggregated_users_created_query, total_query=total_users_created_query, object_model=User, #stock_filters=[filters.beneficiary_filters], # NOTE: Do we still want this filter? stock_filters=[], query_caching_combinatory_strategy=metrics_cache.SUM_OBJECTS, aggregated_query_caching_combinatory_strategy=metrics_cache. SUM_OBJECTS, total_query_caching_combinatory_strategy=metrics_cache.TALLY, filterable_by=self.filterable_attributes, query_actions=[FORMAT_TIMESERIES], aggregated_query_actions=[FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST], )) if group_strategy: active_users_timeseries_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer]).label('date'), group_strategy.group_by_column)\ .group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer])), CreditTransfer) aggregated_active_users_query = group_strategy.build_query_group_by_with_join( db.session.query( func.count(func.distinct( CreditTransfer.sender_user_id)).label('volume'), group_strategy.group_by_column), CreditTransfer) else: active_users_timeseries_query = db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer]).label('date'))\ .group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer])) aggregated_active_users_query = None total_active_users_query = db.session.query( func.count(func.distinct( CreditTransfer.sender_user_id)).label('volume')) self.metrics.append( metric.Metric( metric_name='active_users', is_timeseries=True, query=active_users_timeseries_query, aggregated_query=aggregated_active_users_query, total_query=total_active_users_query, object_model=CreditTransfer, #stock_filters=[filters.beneficiary_filters], # NOTE: Do we still want this filter? stock_filters=[], query_caching_combinatory_strategy=metrics_cache.SUM_OBJECTS, aggregated_query_caching_combinatory_strategy=metrics_cache. QUERY_ALL, total_query_caching_combinatory_strategy=metrics_cache. QUERY_ALL, filterable_by=self.filterable_attributes, query_actions=[FORMAT_TIMESERIES], aggregated_query_actions=[FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST], )) if group_strategy: total_users_timeseries_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(User.id).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[User]).label('date'), group_strategy.group_by_column)\ .group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[User])), User) else: total_users_timeseries_query = db.session.query(func.count(User.id).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[User]).label('date'))\ .group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[User])) self.metrics.append( metric.Metric( metric_name='total_population_cumulative', is_timeseries=True, query=total_users_timeseries_query, total_query=total_users_created_query, aggregated_query=aggregated_active_users_query, object_model=User, stock_filters=[], query_caching_combinatory_strategy=metrics_cache.SUM_OBJECTS, aggregated_query_caching_combinatory_strategy=metrics_cache. SUM_OBJECTS, total_query_caching_combinatory_strategy=metrics_cache.TALLY, filterable_by=self.filterable_attributes, aggregated_query_actions=[FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST], query_actions=[ ADD_MISSING_DAYS_TO_TODAY, ACCUMULATE_TIMESERIES, FORMAT_TIMESERIES ]))
def calculate_transfer_stats(total_time_series=False, start_date=None, end_date=None, user_filter={}): date_filter = [] filter_active = False if start_date is not None and end_date is not None: date_filter.append(CreditTransfer.created >= start_date) date_filter.append(CreditTransfer.created <= end_date) filter_active = True disbursement_filters = [ CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE, CreditTransfer.transfer_type == TransferTypeEnum.PAYMENT, CreditTransfer.transfer_subtype == TransferSubTypeEnum.DISBURSEMENT ] standard_payment_filters = [ CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE, CreditTransfer.transfer_type == TransferTypeEnum.PAYMENT, CreditTransfer.transfer_subtype == TransferSubTypeEnum.STANDARD ] exchanged_filters = [ CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE, CreditTransfer.transfer_type == TransferTypeEnum.EXCHANGE, CreditTransfer.token == g.active_organisation.token ] beneficiary_filters = [User.has_beneficiary_role == True] vendor_filters = [User.has_vendor_role == True] exhaused_balance_filters = [ CreditTransfer.transfer_type == TransferTypeEnum.PAYMENT, TransferAccount._balance_wei == 0 ] transfer_use_filters = [ *standard_payment_filters, CreditTransfer.transfer_use.isnot(None), ] # Disable cache if any filters are being used disable_cache = False if user_filter or date_filter: disable_cache = True total_distributed = db.session.query( func.sum(CreditTransfer.transfer_amount).label('total')) total_distributed = apply_filters(total_distributed, user_filter, CreditTransfer) total_distributed = total_distributed.filter(*disbursement_filters).filter( *date_filter) total_distributed = metrics_cache.execute_with_partial_history_cache( 'total_distributed', total_distributed, CreditTransfer, metrics_cache.SUM, disable_cache=disable_cache) total_spent = db.session.query( func.sum(CreditTransfer.transfer_amount).label('total')) total_spent = apply_filters(total_spent, user_filter, CreditTransfer) total_spent = total_spent.filter(*standard_payment_filters).filter( *date_filter) total_spent = metrics_cache.execute_with_partial_history_cache( 'total_spent', total_spent, CreditTransfer, metrics_cache.SUM, disable_cache=disable_cache) total_exchanged = db.session.query( func.sum(CreditTransfer.transfer_amount).label('total')) total_exchanged = apply_filters(total_exchanged, user_filter, CreditTransfer) total_exchanged = total_exchanged.filter(*exchanged_filters).filter( *date_filter) total_exchanged = metrics_cache.execute_with_partial_history_cache( 'total_exchanged', total_exchanged, CreditTransfer, metrics_cache.SUM, disable_cache=disable_cache) total_beneficiaries = db.session.query(User).filter(*beneficiary_filters) total_beneficiaries = metrics_cache.execute_with_partial_history_cache( 'total_beneficiaries', total_beneficiaries, CreditTransfer, metrics_cache.COUNT, disable_cache=disable_cache) total_vendors = db.session.query(User).filter(*vendor_filters) total_vendors = metrics_cache.execute_with_partial_history_cache( 'total_vendors', total_vendors, CreditTransfer, metrics_cache.COUNT, disable_cache=disable_cache) total_users = total_beneficiaries + total_vendors has_transferred_count = db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)) .label('transfer_count'))\ .filter(*standard_payment_filters) \ .filter(*date_filter) \ .first().transfer_count exhausted_balance_count = db.session.query(func.count(func.distinct( CreditTransfer.sender_transfer_account_id)) .label('transfer_count')) \ .join(CreditTransfer.sender_transfer_account)\ .filter(*exhaused_balance_filters) \ .filter(*date_filter) \ .first().transfer_count daily_transaction_volume = db.session.query( func.sum(CreditTransfer.transfer_amount).label('volume'), func.date_trunc('day', CreditTransfer.created).label('date')) daily_transaction_volume = apply_filters(daily_transaction_volume, user_filter, CreditTransfer) daily_transaction_volume = daily_transaction_volume.group_by(func.date_trunc('day', CreditTransfer.created))\ .filter(*standard_payment_filters) \ .filter(*date_filter) daily_transaction_volume = metrics_cache.execute_with_partial_history_cache( 'daily_transaction_volume', daily_transaction_volume, CreditTransfer, metrics_cache.SUM_OBJECTS, disable_cache=disable_cache) daily_disbursement_volume = db.session.query( func.sum(CreditTransfer.transfer_amount).label('volume'), func.date_trunc('day', CreditTransfer.created).label('date')) daily_disbursement_volume = apply_filters(daily_disbursement_volume, user_filter, CreditTransfer) daily_disbursement_volume = daily_disbursement_volume.group_by(func.date_trunc('day', CreditTransfer.created)) \ .filter(*disbursement_filters) \ .filter(*date_filter) daily_disbursement_volume = metrics_cache.execute_with_partial_history_cache( 'daily_disbursement_volume', daily_disbursement_volume, CreditTransfer, metrics_cache.SUM_OBJECTS, disable_cache=disable_cache) transfer_use_breakdown = db.session.query( CreditTransfer.transfer_use.cast(JSONB), func.count(CreditTransfer.transfer_use)) transfer_use_breakdown = apply_filters(transfer_use_breakdown, user_filter, CreditTransfer) transfer_use_breakdown = transfer_use_breakdown.filter(*transfer_use_filters) \ .group_by(CreditTransfer.transfer_use.cast(JSONB)) \ .all() try: last_day = daily_transaction_volume[0][1] last_day_volume = daily_transaction_volume[0][0] transaction_vol_list = [{ 'date': item[1].isoformat(), 'volume': item[0] } for item in daily_transaction_volume] except IndexError: # No transactions last_day = datetime.datetime.utcnow() last_day_volume = 0 has_transferred_count = 0 transaction_vol_list = [{ 'date': datetime.datetime.utcnow().isoformat(), 'volume': 0 }] try: disbursement_vol_list = [{ 'date': item[1].isoformat(), 'volume': item[0] } for item in daily_disbursement_volume] except IndexError: disbursement_vol_list = [{ 'date': datetime.datetime.utcnow().isoformat(), 'volume': 0 }] try: master_wallet_balance = cached_funds_available() except: master_wallet_balance = 0 data = { 'total_distributed': total_distributed, 'total_spent': total_spent, 'total_exchanged': total_exchanged, 'has_transferred_count': has_transferred_count, 'zero_balance_count': exhausted_balance_count, 'total_beneficiaries': total_beneficiaries, 'total_users': total_users, 'master_wallet_balance': master_wallet_balance, 'daily_transaction_volume': transaction_vol_list, 'daily_disbursement_volume': disbursement_vol_list, 'transfer_use_breakdown': transfer_use_breakdown, 'last_day_volume': { 'date': last_day.isoformat(), 'volume': last_day_volume }, 'filter_active': filter_active } return data
""" Just encapsulates everything needed to make an excel report """ def __init__(self, title, query, headers=None, totals=None): #string queries if type(query) in (unicode, str): self.query = RawQuery(session, query) else: self.query = query self.title = title self.headers = headers self.totals = totals sampleperiod_subq = session.query( SamplePeriod.anopheline2_id, func.count(func.distinct(SamplePeriod.site_id)).label('site_count'), func.count('*').label('sampleperiod_count') ).group_by(SamplePeriod.anopheline2_id).subquery() point_subq = session.query( SamplePeriod.anopheline2_id, func.count('*').label('count') ).filter(Site.area_type=='point').filter(exists().where(SamplePeriod.site_id==Site.site_id)).filter(Anopheline.id==SamplePeriod.anopheline2_id) q = session.query(Anopheline.name, func.coalesce(sampleperiod_subq.c.site_count,0), func.coalesce(sampleperiod_subq.c.sampleperiod_count, 0) ).order_by(Anopheline.name.desc()) reports.append( ExcelReport(
def strava_import(current_user, activity_id): client = get_strava_client(current_user) activity = client.get_activity(activity_id=activity_id) stream_types = [ 'time', 'distance', 'latlng', 'temp', 'heartrate', 'velocity_smooth', 'altitude' ] streams = client.get_activity_streams(activity_id, types=stream_types) device_ids = [ device_id for device_id, in db.session.query( func.distinct(Move.device_id)).join(User).join(Device).filter( Device.name != gpx_import.GPX_DEVICE_NAME).filter( Move.user == current_user).all() ] assert len(device_ids) == 1 device_id = device_ids[0] device = db.session.query(Device).filter_by(id=device_id).one() activity_string = map_type(activity.type) result = db.session.query( Move.activity_type).filter(Move.activity == activity_string).first() if result: activity_type, = result else: activity_type = None move = Move() move.user = current_user move.duration = activity.elapsed_time move.ascent = float(activity.total_elevation_gain) move.speed_avg = float(activity.average_speed) move.hr_avg = heart_rate(activity.average_heartrate) move.temperature_avg = celcius_to_kelvin(activity.average_temp) move.device = device move.date_time = activity.start_date_local move.activity = activity_string move.activity_type = activity_type move.distance = float(activity.distance) move.import_date_time = datetime.now() move.import_module = __name__ move.strava_activity_id = activity_id move.public = False move.source = "Strava activity id=%d; external_id='%s'" % ( activity_id, activity.external_id) lengths = set([len(streams[stream].data) for stream in streams]) assert len(lengths) == 1 length, = lengths move.speed_max = move.speed_avg all_samples = [] for i in range(0, length): time = timedelta(seconds=streams['time'].data[i]) distance = float(streams['distance'].data[i]) if 'heartrate' in streams: hr = float(streams['heartrate'].data[i]) else: hr = None if 'latlng' in streams: lat, lng = streams['latlng'].data[i] else: lat = None lng = None if 'altitude' in streams: altitude = float(streams['altitude'].data[i]) else: altitude = None if 'velocity_smooth' in streams: speed = float(streams['velocity_smooth'].data[i]) else: speed = None if 'temp' in streams: temperature = celcius_to_kelvin(streams['temp'].data[i]) else: temperature = None sample = Sample() sample.sample_type = SAMPLE_TYPE sample.move = move sample.time = time sample.utc = (activity.start_date + time).replace(tzinfo=None) sample.distance = distance sample.latitude = degree_to_radian(lat) sample.longitude = degree_to_radian(lng) sample.hr = heart_rate(hr) sample.temperature = temperature sample.speed = speed sample.altitude = altitude move.speed_max = max(move.speed_max, speed) all_samples.append(sample) derive_move_infos_from_samples(move, all_samples) db.session.add(move) db.session.commit() return move
def strava_import(current_user, activity_id): client = get_strava_client(current_user) activity = client.get_activity(activity_id=activity_id) stream_types = ['time', 'distance', 'latlng', 'temp', 'heartrate', 'velocity_smooth', 'altitude'] streams = client.get_activity_streams(activity_id, types=stream_types) device_ids = [device_id for device_id, in db.session.query(func.distinct(Move.device_id)) .join(User) .join(Device) .filter(Device.name != gpx_import.GPX_DEVICE_NAME) .filter(Move.user == current_user).all()] assert len(device_ids) == 1 device_id = device_ids[0] device = db.session.query(Device).filter_by(id = device_id).one(); activity_string = map_type(activity.type) result = db.session.query(Move.activity_type).filter(Move.activity == activity_string).first() if result: activity_type, = result else: activity_type = None move = Move() move.user = current_user move.duration = activity.elapsed_time move.ascent = float(activity.total_elevation_gain) move.speed_avg = float(activity.average_speed) move.hr_avg = heart_rate(activity.average_heartrate) move.temperature_avg = celcius_to_kelvin(activity.average_temp) move.device = device move.date_time = activity.start_date_local move.activity = activity_string move.activity_type = activity_type move.distance = float(activity.distance) move.import_date_time = datetime.now() move.import_module = __name__ move.strava_activity_id = activity_id move.public = False move.source = "Strava activity id=%d; external_id='%s'" % (activity_id, activity.external_id) lengths = set([len(streams[stream].data) for stream in streams]) assert len(lengths) == 1 length, = lengths move.speed_max = move.speed_avg all_samples = [] for i in range(0, length): time = timedelta(seconds=streams['time'].data[i]) distance = float(streams['distance'].data[i]) if 'heartrate' in streams: hr = float(streams['heartrate'].data[i]) else: hr = None if 'latlng' in streams: lat, lng = streams['latlng'].data[i] else: lat = None lng = None if 'altitude' in streams: altitude = float(streams['altitude'].data[i]) else: altitude = None if 'velocity_smooth' in streams: speed = float(streams['velocity_smooth'].data[i]) else: speed = None if 'temp' in streams: temperature = celcius_to_kelvin(streams['temp'].data[i]) else: temperature = None sample = Sample() sample.sample_type = SAMPLE_TYPE sample.move = move sample.time = time sample.utc = (activity.start_date + time).replace(tzinfo=None) sample.distance = distance sample.latitude = degree_to_radian(lat) sample.longitude = degree_to_radian(lng) sample.hr = heart_rate(hr) sample.temperature = temperature sample.speed = speed sample.altitude = altitude move.speed_max = max(move.speed_max, speed) all_samples.append(sample) derive_move_infos_from_samples(move, all_samples) db.session.add(move) db.session.commit() return move
share = relation(Share, innerjoin=True, backref=backref('disks', cascade='all')) __extra_table_args__ = (Index('%s_share_idx' % _TN, share_id), ) __mapper_args__ = {'polymorphic_identity': 'virtual_disk'} def __init__(self, **kw): if 'address' not in kw or kw['address'] is None: raise ValueError("address is mandatory for shared disks") super(VirtualDisk, self).__init__(**kw) def __repr__(self): return "<%s %s (%s) of machine %s, %d GB, provided by %s>" % \ (self._get_class_label(), self.device_name, self.controller_type, self.machine.label, self.capacity, (self.share.name if self.share else "no_share")) # The formatter code is interested in the count of disks/machines, and it is # cheaper to query the DB than to load all entities into memory Share.disk_count = column_property(select( [func.count()], VirtualDisk.share_id == Share.id).label("disk_count"), deferred=True) Share.machine_count = column_property( select([func.count(func.distinct(VirtualDisk.machine_id))], VirtualDisk.share_id == Share.id).label("machine_count"), deferred=True)
def run(): options, args = commandline_args("Dump BIP into JSON files") d = DB() conn, meta = d.connect(options["database"]) park_table = d.model.park park_dim_table = d.model.park_dimension bip_table = d.model.bip game_table = d.model.game player_table = d.model.mlbam_player ab_table = d.model.atbat # Arg, postgres requires every column to be in the ORDER BY clause park_sql = select( [park_table.c.id, park_table.c.name, func.count(bip_table.c.id).label("num")], from_obj=park_table.join(bip_table), ).group_by(park_table.c.id, park_table.c.name) parks = {} for row in conn.execute(park_sql): p = {} for key in row.keys(): if key in set(["id", "num"]): p[key] = int(row[key]) else: p[key] = str(row[key]) p["bip"] = p["num"] p["years"] = {} del p["num"] parks[p["id"]] = p # Skipping the closing year for simplicity. If a team switches parks in the # middle of the year, it will be a park with a new id. I believe the only # issue will be if MLBAM updates the image in the middle of the season. # This might happen for Citi Field (no bases or foul lines!). dimension_sql = select( [ park_dim_table.c.park_id, park_dim_table.c.image_file, park_dim_table.c.opening, park_dim_table.c.hp_x, park_dim_table.c.hp_y, park_dim_table.c.image_hp_x, park_dim_table.c.image_hp_y, park_dim_table.c.scale, ], from_obj=park_dim_table, ) for row in conn.execute(dimension_sql): id = row["park_id"] if id not in parks: continue opening = row["opening"] images = { "file": row["image_file"], "scale": float(row["scale"]), "hp_x": float(row["hp_x"]), "hp_y": float(row["hp_y"]), "image_hp_x": float(row["image_hp_x"]), "image_hp_y": float(row["image_hp_y"]), "opening": opening.year, } if "images" not in parks[id]: parks[id]["images"] = {} parks[id]["images"][opening.year] = images years_sql = select([func.distinct(text(get_year(conn, "day")))], from_obj=game_table) years = [int(row[0]) for row in conn.execute(years_sql)] p = player_table.alias() b = player_table.alias() bip_sql = select( [ game_table.c.day.label("day"), bip_table.c.type.label("type"), bip_table.c.x.label("x"), bip_table.c.y.label("y"), ab_table.c.event.label("event"), (b.c.namelast + ", " + b.c.namefirst).label("batter"), ab_table.c.batter_stand.label("stand"), (p.c.namelast + ", " + p.c.namefirst).label("pitcher"), ab_table.c.pitcher_throw.label("throw"), ], and_(park_table.c.id == bindparam("park"), text(get_year(conn, "day")) == bindparam("year")), from_obj=bip_table.join(park_table) .join(ab_table) .join(game_table) .outerjoin(p, onclause=p.c.mlbamid == ab_table.c.pitcher) .outerjoin(b, onclause=b.c.mlbamid == ab_table.c.batter), ) for park_id in parks.keys(): for year in years: bip_list = [] str_y = str(year) for bip in conn.execute(bip_sql, {"park": park_id, "year": str_y}): bip_list.append( { "x": bip["x"], "y": bip["y"], "event": bip["event"], "type": bip["type"], "pitcher": bip["pitcher"], "throw": bip["throw"], "batter": bip["batter"], "stand": bip["stand"], } ) # No need to write empty files! if len(bip_list) > 0: parks[park_id]["years"][year] = True park_file = os.path.join(options["output_dir"], "park-" + str(park_id) + "-" + str(year) + ".json") dump_json(park_file, bip_list) parks_file = os.path.join(options["output_dir"], "parks.json") dump_json(parks_file, parks)
def __init__(self, group_strategy, timeseries_unit = 'day', token=None, date_filter_attributes=None): self.filterable_attributes = [DATE, CUSTOM_ATTRIBUTE, TRANSFER_ACCOUNT, CREDIT_TRANSFER, USER] self.timeseries_unit = timeseries_unit self.date_filter_attributes = date_filter_attributes self.metrics = [] total_amount_query = db.session.query(func.sum(CreditTransfer.transfer_amount).label('total')) self.metrics.append(metric.Metric( metric_name='total_distributed', query=total_amount_query, object_model=CreditTransfer, stock_filters=[filters.disbursement_filters], caching_combinatory_strategy=metrics_cache.SUM, filterable_by=self.filterable_attributes, bypass_user_filters=True, )) self.metrics.append(metric.Metric( metric_name='total_reclaimed', query=total_amount_query, object_model=CreditTransfer, stock_filters=[filters.reclamation_filters], caching_combinatory_strategy=metrics_cache.SUM, filterable_by=self.filterable_attributes, bypass_user_filters=True, )) self.metrics.append(metric.Metric( metric_name='total_withdrawn', query=total_amount_query, object_model=CreditTransfer, stock_filters=[filters.withdrawal_filters], caching_combinatory_strategy=metrics_cache.SUM, filterable_by=self.filterable_attributes, bypass_user_filters=True, )) # Timeseries Metrics if group_strategy: transaction_volume_timeseries_query = group_strategy.build_query_group_by_with_join(db.session.query(func.sum(CreditTransfer.transfer_amount).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer]).label('date'), group_strategy.group_by_column).group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer])), CreditTransfer) aggregated_transaction_volume_query = group_strategy.build_query_group_by_with_join(db.session.query(func.sum(CreditTransfer.transfer_amount).label('volume'), group_strategy.group_by_column), CreditTransfer) else: transaction_volume_timeseries_query = db.session.query(func.sum(CreditTransfer.transfer_amount).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer]).label('date')).group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer])) aggregated_transaction_volume_query = None total_transaction_volume_query = db.session.query(func.sum(CreditTransfer.transfer_amount).label('volume')) self.metrics.append(metric.Metric( metric_name='all_payments_volume', is_timeseries=True, query=transaction_volume_timeseries_query, aggregated_query=aggregated_transaction_volume_query, total_query=total_transaction_volume_query, object_model=CreditTransfer, timeseries_caching_combinatory_strategy=metrics_cache.SUM_OBJECTS, caching_combinatory_strategy=metrics_cache.QUERY_ALL, filterable_by=self.filterable_attributes, query_actions=[FORMAT_TIMESERIES], aggregated_query_actions=[FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST], value_type=CURRENCY, token=token )) self.metrics.append(metric.Metric( metric_name='transfer_amount_per_user', is_timeseries=True, query=transaction_volume_timeseries_query, aggregated_query=aggregated_transaction_volume_query, total_query=total_transaction_volume_query, object_model=CreditTransfer, stock_filters=[filters.standard_payment_filters], timeseries_caching_combinatory_strategy=metrics_cache.SUM_OBJECTS, caching_combinatory_strategy=metrics_cache.QUERY_ALL, filterable_by=self.filterable_attributes, query_actions=[CALCULATE_TIMESERIES_PER_USER, FORMAT_TIMESERIES], # Add per user aggregated_query_actions=[CALCULATE_AGGREGATE_PER_USER, FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST, CALCULATE_TOTAL_PER_USER], value_type=CURRENCY, token=token )) if group_strategy: transaction_count_timeseries_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(CreditTransfer.id).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer]).label('date'), group_strategy.group_by_column).group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer])), CreditTransfer) aggregated_transaction_count_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(CreditTransfer.id).label('volume'), group_strategy.group_by_column), CreditTransfer) else: transaction_count_timeseries_query = db.session.query(func.count(CreditTransfer.id).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer]).label('date')).group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer])) aggregated_transaction_count_query = None total_transaction_count_query = db.session.query(func.count(CreditTransfer.id).label('volume')) self.metrics.append(metric.Metric( metric_name='daily_transaction_count', is_timeseries=True, query=transaction_count_timeseries_query, aggregated_query=aggregated_transaction_count_query, total_query=total_transaction_count_query, object_model=CreditTransfer, stock_filters=[filters.standard_payment_filters], timeseries_caching_combinatory_strategy=metrics_cache.SUM_OBJECTS, caching_combinatory_strategy=metrics_cache.QUERY_ALL, filterable_by=self.filterable_attributes, query_actions=[FORMAT_TIMESERIES], aggregated_query_actions=[FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST], value_type=COUNT )) self.metrics.append(metric.Metric( metric_name='trades_per_user', is_timeseries=True, query=transaction_count_timeseries_query, aggregated_query=aggregated_transaction_count_query, total_query=total_transaction_count_query, object_model=CreditTransfer, stock_filters=[filters.standard_payment_filters], timeseries_caching_combinatory_strategy=metrics_cache.SUM_OBJECTS, caching_combinatory_strategy=metrics_cache.QUERY_ALL, filterable_by=self.filterable_attributes, query_actions=[CALCULATE_TIMESERIES_PER_USER, FORMAT_TIMESERIES], # Add per user aggregated_query_actions=[CALCULATE_AGGREGATE_PER_USER, FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST, CALCULATE_TOTAL_PER_USER], value_type=COUNT_AVERAGE, )) if group_strategy: active_users_timeseries_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer]).label('date'), group_strategy.group_by_column).group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer])), CreditTransfer) aggregated_active_users_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume'), group_strategy.group_by_column), CreditTransfer) else: active_users_timeseries_query = db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume'), func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer]).label('date')).group_by(func.date_trunc(self.timeseries_unit, self.date_filter_attributes[CreditTransfer])) aggregated_active_users_query = None total_transaction_volume_query = db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume')) self.metrics.append(metric.Metric( metric_name='users_who_made_purchase', is_timeseries=True, query=active_users_timeseries_query, aggregated_query=aggregated_active_users_query, total_query=total_transaction_volume_query, object_model=CreditTransfer, #stock_filters=[filters.beneficiary_filters], # NOTE: Do we want this filter? stock_filters=[], timeseries_caching_combinatory_strategy=metrics_cache.SUM_OBJECTS, caching_combinatory_strategy=metrics_cache.QUERY_ALL, filterable_by=self.filterable_attributes, query_actions=[FORMAT_TIMESERIES], aggregated_query_actions=[FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST], value_type=COUNT, ))
def topsku_week_table_excel(): date = datetime.strptime(request.form["sku_date"], "%Y-%m-%d").date() hour = int(request.form["hour"]) weekday = date.weekday() start_date = date - relativedelta(weeks=4, weekday=weekday) end_date = date brands = db.session.query(func.distinct(top_sku_talendfc.brand))\ .filter(and_(top_sku_talendfc.txn_date >= start_date, top_sku_talendfc.txn_date <= end_date, func.weekday(top_sku_talendfc.txn_date) == weekday, top_sku_talendfc.processing_hr == hour)) lookup = db.session.query(top_sku_talendfc.txn_date,top_sku_talendfc.brand,func.sum(top_sku_talendfc.txn_amount))\ .filter(and_(top_sku_talendfc.txn_date >= start_date, top_sku_talendfc.txn_date <= end_date, func.weekday(top_sku_talendfc.txn_date) == weekday, top_sku_talendfc.processing_hr == hour))\ .group_by(top_sku_talendfc.txn_date,top_sku_talendfc.brand) brands = [b[0] for b in brands] + ["TOTAL"] sku_dict = {} for l in lookup.all(): key = l[0].strftime("%Y-%m-%d") if key not in sku_dict.keys(): sku_dict[key] = { "brands": dict.fromkeys(brands, None), } sku_dict[key]["brands"][l[1]] = str(l[2]) if sku_dict[key]["brands"]["TOTAL"] == None: sku_dict[key]["brands"]["TOTAL"] = 0 sku_dict[key]["brands"]["TOTAL"] += l[2] else: if sku_dict[key]["brands"]["TOTAL"] == None: sku_dict[key]["brands"]["TOTAL"] = 0 sku_dict[key]["brands"][l[1]] = str(l[2]) sku_dict[key]["brands"]["TOTAL"] += l[2] formatted_data = {"columns": ["Dates"] + brands, "data": []} for k in sku_dict.keys(): sku = dict.fromkeys(formatted_data["columns"]) sku["Dates"] = k for b in sku_dict[k]["brands"].keys(): sku[b] = str(sku_dict[k]["brands"][b]) formatted_data["data"].append(sku) #output in bytes output = io.BytesIO() #create WorkBook object workbook = Workbook() workbook_name = "Manifest Hive Bashing {}".format( datetime.now().strftime("%Y-%m-%d %H-%M-%S")) #add a sheet ws = workbook.create_sheet('Week on Week Amounts per Brand', 0) greenFill = PatternFill(start_color='AEEA00', end_color='AEEA00', fill_type='solid') x_pos = 1 y_pos = 1 temp_y = 1 x_lim = 19 row = 0 for c in cdr_dict.keys(): print(c) workbook.save(output) output.seek(0) pprint.pprint(cdr_dict) filename = workbook_name return Response( output, mimetype= "application/openxmlformats-officedocument.spreadsheetml.sheet", headers={ "Content-Disposition": "attachment;filename={}.xlsx".format(filename) })
def child_source_scores(self, row): """ Counts of children sources, how many speak, etc. """ self.scores_ws.write(row, 0, 'Child Sources') # all child sources rows = self.filter( db.session.query( Medium.name, func.count(1).label('freq')) .join(Document) .join(DocumentSource) .filter(DocumentSource.source_type == 'child') .group_by(Medium.name) ).all() self.write_simple_score_row('Total child sources', rows, row) row += 1 self.write_percent_row('Child sources', self.score_row['Total sources'], row - 1, row) row += 1 # quoted child sources rows = self.filter( db.session.query( Medium.name, func.count(1).label('freq')) .join(Document) .join(DocumentSource) .filter(DocumentSource.source_type == 'child') .filter(DocumentSource.quoted == True) .group_by(Medium.name) ).all() # noqa self.write_simple_score_row('Quoted child sources', rows, row) row += 1 # percent of all sources self.write_percent_row('Quoted child sources', self.score_row['Total sources'], row - 1, row) row += 1 # source counts per document rows = self.source_counts(children=True, limit=4) rows = [[m, c + ' Child Sources', v] for m, c, v in rows] buckets = ['1 Child Sources', '2 Child Sources', '3 Child Sources', '4 Child Sources', '>4 Child Sources'] starting_row = row row = self.write_score_table(buckets, rows, row) + 1 row = self.write_percent_table(buckets, self.score_row['Total articles'], starting_row, row) + 1 # origin of documents with quoted children self.scores_ws.write(row, 0, 'Origins of Quoted Children') rows = self.filter( db.session.query( Medium.name, Location.name, func.count(func.distinct(Document.id)).label('freq')) .join(Document) .join(DocumentSource) .join(Location) .filter(DocumentSource.source_type == 'child') .filter(DocumentSource.quoted == True) .group_by(Medium.name) # noqa ).all() origins = list(set(r[1] for r in rows)) row = self.write_score_table(origins, rows, row) # entropy self.write_simple_score_row('Diversity of Quoted Origins', self.entropy(rows), row) row += 1 return row
def __repr__(self): return "<%s %s (%s) of machine %s, %d GB, provided by %s>" % \ (self._get_class_label(), self.device_name, self.controller_type, self.machine.label, self.capacity, (self.share.name if self.share else "no_share")) # The formatter code is interested in the count of disks/machines, and it is # cheaper to query the DB than to load all entities into memory Share.disk_count = column_property( select([func.count()], VirtualNasDisk.share_id == Share.id) .label("disk_count"), deferred=True) Share.machine_count = column_property( select([func.count(func.distinct(VirtualNasDisk.machine_id))], VirtualNasDisk.share_id == Share.id) .label("machine_count"), deferred=True) class VirtualLocalDisk(VirtualDisk): filesystem_id = Column(Integer, ForeignKey('filesystem.id', name='%s_filesystem_fk' % _TN, ondelete='CASCADE'), nullable=True) filesystem = relation(Filesystem, innerjoin=True, backref=backref('disks', cascade='all')) __extra_table_args__ = (Index('%s_filesystem_idx' % _TN, filesystem_id),) __mapper_args__ = {'polymorphic_identity': 'virtual_localdisk'}
def calculate_transfer_stats(total_time_series=False): total_distributed = db.session.query(func.sum(models.CreditTransfer.transfer_amount).label('total'))\ .filter(models.CreditTransfer.transfer_type == models.TransferTypeEnum.DISBURSEMENT).first().total total_spent = db.session.query(func.sum(models.CreditTransfer.transfer_amount).label('total'))\ .filter(models.CreditTransfer.transfer_type == models.TransferTypeEnum.PAYMENT).first().total total_beneficiaries = db.session.query( models.User).filter(models.User.is_beneficiary == True).count() total_vendors = db.session.query(models.User)\ .filter(models.User.is_vendor == True).count() total_users = total_beneficiaries + total_vendors has_transferred_count = db.session.query(func.count(func.distinct(models.CreditTransfer.sender_user_id)) .label('transfer_count'))\ .filter(models.CreditTransfer.transfer_type == models.TransferTypeEnum.PAYMENT).first().transfer_count # zero_balance_count = db.session.query(func.count(models.TransferAccount.id).label('zero_balance_count'))\ # .filter(models.TransferAccount.balance == 0).first().zero_balance_count exhausted_balance_count = db.session.query(func.count(func.distinct(models.CreditTransfer.sender_transfer_account_id)) .label('transfer_count')) \ .join(models.CreditTransfer.sender_transfer_account)\ .filter(models.CreditTransfer.transfer_type == models.TransferTypeEnum.PAYMENT) \ .filter(models.TransferAccount.balance == 0).first().transfer_count daily_transaction_volume = db.session.query(func.sum(models.CreditTransfer.transfer_amount).label('volume'), func.date_trunc('day', models.CreditTransfer.created).label('date'))\ .group_by(func.date_trunc('day', models.CreditTransfer.created))\ .filter(models.CreditTransfer.transfer_type == models.TransferTypeEnum.PAYMENT).all() daily_disbursement_volume = db.session.query(func.sum(models.CreditTransfer.transfer_amount).label('volume'), func.date_trunc('day', models.CreditTransfer.created).label('date')) \ .group_by(func.date_trunc('day', models.CreditTransfer.created)) \ .filter(models.CreditTransfer.transfer_type == models.TransferTypeEnum.DISBURSEMENT).all() try: master_wallet_balance = master_wallet_funds_available() except BlockchainError: master_wallet_balance = 0 try: last_day = daily_transaction_volume[0].date last_day_volume = daily_transaction_volume[0].volume transaction_vol_list = [{ 'date': item.date.isoformat(), 'volume': item.volume } for item in daily_transaction_volume] except IndexError: # No transactions last_day = datetime.datetime.utcnow() last_day_volume = 0 has_transferred_count = 0 transaction_vol_list = [{ 'date': datetime.datetime.utcnow().isoformat(), 'volume': 0 }] try: last_day_disbursement_volume = daily_disbursement_volume[0].volume disbursement_vol_list = [{ 'date': item.date.isoformat(), 'volume': item.volume } for item in daily_disbursement_volume] except IndexError: last_day_disbursement_volume = 0 disbursement_vol_list = [{ 'date': datetime.datetime.utcnow().isoformat(), 'volume': 0 }] data = { 'total_distributed': total_distributed, 'total_spent': total_spent, 'has_transferred_count': has_transferred_count, 'zero_balance_count': exhausted_balance_count, 'total_beneficiaries': total_beneficiaries, 'total_users': total_users, 'master_wallet_balance': master_wallet_balance, 'daily_transaction_volume': transaction_vol_list, 'daily_disbursement_volume': disbursement_vol_list, 'last_day_volume': { 'date': last_day.isoformat(), 'volume': last_day_volume } } return data
def create_receiver_stats(session, date, logger=None): """Add/update receiver stats.""" if logger is None: logger = app.logger (start, end) = date_to_timestamps(date) # First kill the stats for the selected date deleted_counter = session.query(ReceiverStats).filter( ReceiverStats.date == date).delete() # Select one day sq = session.query(ReceiverBeacon).filter( between(ReceiverBeacon.timestamp, start, end)).subquery() # Calculate stats, firstseen, lastseen and last values != NULL receiver_stats = session.query( distinct(sq.c.receiver_id).label("receiver_id"), literal(date).label("date"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).asc().nullslast()).label("firstseen"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("lastseen"), func.first_value(sq.c.location).over( partition_by=sq.c.receiver_id, order_by=case([(sq.c.location == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "location_wkt"), func.first_value(sq.c.altitude).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.altitude == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("altitude"), func.first_value(sq.c.version).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.version == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("version"), func.first_value(sq.c.platform).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.platform == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("platform"), ).subquery() # And insert them ins = insert(ReceiverStats).from_select( [ ReceiverStats.receiver_id, ReceiverStats.date, ReceiverStats.firstseen, ReceiverStats.lastseen, ReceiverStats.location_wkt, ReceiverStats.altitude, ReceiverStats.version, ReceiverStats.platform, ], receiver_stats, ) res = session.execute(ins) insert_counter = res.rowcount session.commit() logger.warn("ReceiverStats for {}: {} deleted, {} inserted".format( date, deleted_counter, insert_counter)) # Update aircraft_beacon_count, aircraft_count and max_distance aircraft_beacon_stats = (session.query( AircraftBeacon.receiver_id, func.count(AircraftBeacon.timestamp).label("aircraft_beacon_count"), func.count(func.distinct( AircraftBeacon.device_id)).label("aircraft_count"), func.max(AircraftBeacon.distance).label("max_distance"), ).filter( and_(between(AircraftBeacon.timestamp, start, end), AircraftBeacon.error_count == 0, AircraftBeacon.quality <= MAX_PLAUSIBLE_QUALITY, AircraftBeacon.relay == null())).group_by( AircraftBeacon.receiver_id).subquery()) upd = (update(ReceiverStats).where( and_(ReceiverStats.date == date, ReceiverStats.receiver_id == aircraft_beacon_stats.c.receiver_id)).values({ "aircraft_beacon_count": aircraft_beacon_stats.c.aircraft_beacon_count, "aircraft_count": aircraft_beacon_stats.c.aircraft_count, "max_distance": aircraft_beacon_stats.c.max_distance })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.warn("Updated {} ReceiverStats".format(update_counter)) return "ReceiverStats for {}: {} deleted, {} inserted, {} updated".format( date, deleted_counter, insert_counter, update_counter)
def __init__(self, group_strategy, timeseries_unit = 'day'): self.filterable_attributes = [DATE, CUSTOM_ATTRIBUTE, TRANSFER_ACCOUNT, USER] self.timeseries_unit = timeseries_unit self.metrics = [] total_beneficiaries_query = db.session.query(User) self.metrics.append(metric.Metric( metric_name='total_beneficiaries', query=total_beneficiaries_query, object_model=User, stock_filters=[filters.beneficiary_filters], caching_combinatory_strategy=metrics_cache.COUNT, filterable_by=self.filterable_attributes)) total_vendors_query = db.session.query(User) self.metrics.append(metric.Metric( metric_name='total_vendors', query=total_vendors_query, object_model=User, stock_filters=[filters.vendor_filters], caching_combinatory_strategy=metrics_cache.COUNT, filterable_by=self.filterable_attributes)) # Timeseries Metrics if group_strategy: users_created_timeseries_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(User.id).label('volume'), func.date_trunc(self.timeseries_unit, User.created).label('date'), group_strategy.group_by_column).group_by(func.date_trunc(self.timeseries_unit, User.created)), User) aggregated_users_created_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(User.id).label('volume'), group_strategy.group_by_column), User) else: users_created_timeseries_query = db.session.query(func.count(User.id).label('volume'), func.date_trunc(self.timeseries_unit, User.created).label('date')).group_by(func.date_trunc(self.timeseries_unit, User.created)) aggregated_users_created_query = None total_users_created_query = db.session.query(func.count(User.id).label('volume')) self.metrics.append(metric.Metric( metric_name='users_created', is_timeseries=True, query=users_created_timeseries_query, aggregated_query=aggregated_users_created_query, total_query=total_users_created_query, object_model=User, #stock_filters=[filters.beneficiary_filters], # NOTE: Do we still want this filter? stock_filters=[], caching_combinatory_strategy=metrics_cache.QUERY_ALL, filterable_by=self.filterable_attributes, query_actions=[FORMAT_TIMESERIES], aggregated_query_actions=[FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST], )) if group_strategy: active_users_timeseries_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume'), func.date_trunc(self.timeseries_unit, CreditTransfer.created).label('date'), group_strategy.group_by_column).group_by(func.date_trunc(self.timeseries_unit, CreditTransfer.created)), CreditTransfer) aggregated_active_users_query = group_strategy.build_query_group_by_with_join(db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume'), group_strategy.group_by_column), CreditTransfer) else: active_users_timeseries_query = db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume'), func.date_trunc(self.timeseries_unit, CreditTransfer.created).label('date')).group_by(func.date_trunc(self.timeseries_unit, CreditTransfer.created)) aggregated_active_users_query = None total_active_users_query = db.session.query(func.count(func.distinct(CreditTransfer.sender_user_id)).label('volume')) self.metrics.append(metric.Metric( metric_name='active_users', is_timeseries=True, query=active_users_timeseries_query, aggregated_query=aggregated_active_users_query, total_query=total_active_users_query, object_model=CreditTransfer, #stock_filters=[filters.beneficiary_filters], # NOTE: Do we still want this filter? stock_filters=[], caching_combinatory_strategy=metrics_cache.QUERY_ALL, filterable_by=self.filterable_attributes, query_actions=[FORMAT_TIMESERIES], aggregated_query_actions=[FORMAT_AGGREGATE_METRICS], total_query_actions=[GET_FIRST], ))
def _process_events(klass, events, page, query=None, cities=None, user=None, flags=None, selected_tags=None, selected_categories=None, future_only=None): if future_only: events_with_counts = events_with_counts.filter( or_(Event.start_time >= datetime.datetime.now(), Event.end_time >= datetime.datetime.now())) events = events.filter(Event.status != Event.STATUS_CLOSED_PERM) events, selected_tags = klass._filter_events( events, query=query, categories=selected_categories, tags=selected_tags, flags=flags) event_cities = klass._cities_for_events(events) if cities: events = events.filter(Event.city.in_(cities)) for city in event_cities: city['selected'] = city['chip_name'] in cities tags, categories = klass._tags_for_events( events=events, selected_categories=selected_categories, selected_tags=selected_tags) event_user_ids = None if user: event_ids = {e[0].event_id for e in events if e[1]} following_user_ids = alias( db_session.query(func.distinct(Follow.follow_id)).filter( and_(UserEvent.event_id.in_(event_ids), UserEvent.user_id == Follow.follow_id, Follow.user_id == user.user_id, Follow.follow_id != user.user_id, Follow.active == True)), "following_user_ids") event_users = { str(u.user_id): { 'user_id': u.user_id, 'username': u.username, 'image_url': u.image_url } for u in User.query.filter(User.user_id.in_( following_user_ids)) } events_with_following_counts = db_session.query( UserEvent.event_id, func.array_agg(func.distinct( User.user_id)).label('user_ids')).filter( and_(Follow.user_id == user.user_id, UserEvent.user_id == Follow.follow_id, UserEvent.event_id.in_(event_ids), Follow.follow_id != user.user_id)).group_by( UserEvent.event_id) event_user_ids = { row[0]: set(str(follower_id) for follower_id in row[1]) for row in events_with_following_counts } events = klass._order_events(events) events = events.limit(klass.PAGE_SIZE).offset( (page - 1) * klass.PAGE_SIZE) results = [] for event, user_count in events: event.card_user_count = user_count if event_user_ids and event.event_id in event_user_ids: event.card_event_users = [ event_users[x] for x in event_user_ids[event.event_id] if x in event_users ] results.append(event) return results, categories, tags, event_cities, events
__mapper_args__ = {"polymorphic_identity": "virtual_disk"} def __init__(self, **kw): if "address" not in kw or kw["address"] is None: raise ValueError("address is mandatory for shared disks") super(VirtualDisk, self).__init__(**kw) def __repr__(self): return "<%s %s (%s) of machine %s, %d GB, provided by %s>" % ( self._get_class_label(), self.device_name, self.controller_type, self.machine.label, self.capacity, (self.share.name if self.share else "no_share"), ) # The formatter code is interested in the count of disks/machines, and it is # cheaper to query the DB than to load all entities into memory Share.disk_count = column_property( select([func.count()], VirtualDisk.share_id == Share.id).label("disk_count"), deferred=True ) Share.machine_count = column_property( select([func.count(func.distinct(VirtualDisk.machine_id))], VirtualDisk.share_id == Share.id).label( "machine_count" ), deferred=True, )