def sum(column):
"""Wrapper around sqlalchemy.sql.functions.sum to handle BigInteger.
sqlalchemy returns a Decimal for sum over BigInteger values. Detect the
column type and coerce to long if it's a BigInteger.
"""
if isinstance(column.property.columns[0].type, BigInteger):
return sa_sum(column, type_=CoerceToLong)
else:
return sa_sum(column)
def sum(column):
"""Wrapper around sqlalchemy.sql.functions.sum to handle BigInteger.
sqlalchemy returns a Decimal for sum over BigInteger values. Detect the
column type and coerce to long if it's a BigInteger.
"""
if isinstance(column.property.columns[0].type, BigInteger):
return sa_sum(column, type_=CoerceToLong)
else:
return sa_sum(column)
def gold_revenue_on(date):
NON_REVENUE_STATUSES = ("declined", "chargeback", "fudge")
query = (select([sa_sum(gold_table.c.pennies)])
.where(~ gold_table.c.status.in_(NON_REVENUE_STATUSES))
.where(sa.func.date_trunc('day', gold_table.c.date) == date))
rows = ENGINE.execute(query)
return rows.fetchone()[0] or 0
def gold_revenue_on(date):
NON_REVENUE_STATUSES = ("declined", "chargeback", "fudge")
query = (select([
sa_sum(gold_table.c.pennies)
]).where(~gold_table.c.status.in_(NON_REVENUE_STATUSES)).where(
func.date_trunc('day', gold_table.c.date) == date))
rows = ENGINE.execute(query)
return rows.fetchone()[0] or 0
def gold_revenue_multi(dates):
date_expr = sa.func.date_trunc("day", sa.func.timezone(TIMEZONE.zone, gold_table.c.date))
query = (
select([date_expr, sa_sum(gold_table.c.pennies)])
.where(~gold_table.c.status.in_(NON_REVENUE_STATUSES))
.where(date_expr.in_(dates))
.group_by(date_expr)
)
return {truncated_time.date(): pennies for truncated_time, pennies in ENGINE.execute(query)}
def gold_revenue_multi(dates):
date_expr = sa.func.date_trunc('day',
sa.func.timezone(TIMEZONE.zone, gold_table.c.date))
query = (select([date_expr, sa_sum(gold_table.c.pennies)])
.where(~ gold_table.c.status.in_(NON_REVENUE_STATUSES))
.where(date_expr.in_(dates))
.group_by(date_expr)
)
return {truncated_time.date(): pennies
for truncated_time, pennies in ENGINE.execute(query)}
def get_campaign_pageviews(date, sr_name=''):
# ads go live at hour=5
start = datetime.datetime(date.year, date.month, date.day, 5, 0)
hours = [start + datetime.timedelta(hours=i) for i in xrange(24)]
traffic_cls = TargetedImpressionsByCodename
codename_string = PC_PREFIX + '_%'
q = (Session.query(traffic_cls.codename,
sa_sum(traffic_cls.pageview_count).label('daily'))
.filter(traffic_cls.subreddit == sr_name)
.filter(traffic_cls.codename.like(codename_string))
.filter(traffic_cls.interval == 'hour')
.filter(traffic_cls.date.in_(hours))
.group_by(traffic_cls.codename))
pageviews = dict(q)
return pageviews
def get_campaign_pageviews(date, sr_name=''):
# ads go live at hour=5
start = datetime.datetime(date.year, date.month, date.day, 5, 0)
hours = [start + datetime.timedelta(hours=i) for i in xrange(24)]
traffic_cls = TargetedImpressionsByCodename
codename_string = PC_PREFIX + '_%'
q = (Session.query(traffic_cls.codename,
sa_sum(traffic_cls.pageview_count).label('daily'))
.filter(traffic_cls.subreddit == sr_name)
.filter(traffic_cls.codename.like(codename_string))
.filter(traffic_cls.interval == 'hour')
.filter(traffic_cls.date.in_(hours))
.group_by(traffic_cls.codename))
pageviews = dict(q)
return pageviews
