def calculate_weekly_sales(item_collection_ids, user_tz, year):
"""
Calculates sales per week for items in the given set of item_collection_ids in a given year,
in the user's timezone.
"""
ordered_week_sales = OrderedDict()
for year_week in Week.weeks_of_year(year):
ordered_week_sales[year_week.week] = 0
start_at = isoweek_datetime(year, 1, user_tz)
end_at = isoweek_datetime(year + 1, 1, user_tz)
week_sales = db.session.query('sales_week', 'sum').from_statement(db.text('''
SELECT EXTRACT(WEEK FROM ordered_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone)
AS sales_week, SUM(final_amount) AS sum
FROM line_item INNER JOIN item on line_item.item_id = item.id
WHERE status IN :statuses AND item_collection_id IN :item_collection_ids
AND ordered_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone >= :start_at
AND ordered_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone < :end_at
GROUP BY sales_week ORDER BY sales_week;
''')).params(timezone=user_tz, statuses=tuple([LINE_ITEM_STATUS.CONFIRMED, LINE_ITEM_STATUS.CANCELLED]),
start_at=start_at, end_at=end_at, item_collection_ids=tuple(item_collection_ids)).all()
for week_sale in week_sales:
ordered_week_sales[int(week_sale.sales_week)] = week_sale.sum
return ordered_week_sales
def calculate_weekly_refunds(item_collection_ids, user_tz, year):
"""
Calculates refunds per week for a given set of item_collection_ids in a given year,
in the user's timezone.
"""
ordered_week_refunds = OrderedDict()
for year_week in Week.weeks_of_year(year):
ordered_week_refunds[year_week.week] = 0
start_at = isoweek_datetime(year, 1, user_tz)
end_at = isoweek_datetime(year + 1, 1, user_tz)
week_refunds = db.session.query('sales_week', 'sum').from_statement(
db.text('''
SELECT EXTRACT(WEEK FROM payment_transaction.created_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone)
AS sales_week, SUM(payment_transaction.amount) AS sum
FROM customer_order INNER JOIN payment_transaction on payment_transaction.customer_order_id = customer_order.id
WHERE customer_order.status IN :statuses AND customer_order.item_collection_id IN :item_collection_ids
AND payment_transaction.transaction_type = :transaction_type
AND payment_transaction.created_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone
>= :start_at
AND payment_transaction.created_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone
< :end_at
GROUP BY sales_week ORDER BY sales_week;
''')).params(timezone=user_tz,
statuses=tuple(ORDER_STATUS.TRANSACTION),
transaction_type=TRANSACTION_TYPE.REFUND,
start_at=start_at,
end_at=end_at,
item_collection_ids=tuple(item_collection_ids)).all()
for week_refund in week_refunds:
ordered_week_refunds[int(week_refund.sales_week)] = week_refund.sum
return ordered_week_refunds
def calculate_weekly_sales(item_collection_ids, user_tz, year):
"""
Calculates sales per week for items in the given set of item_collection_ids in a given year,
in the user's timezone.
"""
ordered_week_sales = OrderedDict()
for year_week in Week.weeks_of_year(year):
ordered_week_sales[year_week.week] = 0
start_at = isoweek_datetime(year, 1, user_tz)
end_at = isoweek_datetime(year + 1, 1, user_tz)
week_sales = (db.session.query('sales_week', 'sum').from_statement(
db.text('''
SELECT EXTRACT(WEEK FROM ordered_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone)
AS sales_week, SUM(final_amount) AS sum
FROM line_item INNER JOIN item on line_item.item_id = item.id
WHERE status IN :statuses AND item_collection_id IN :item_collection_ids
AND ordered_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone >= :start_at
AND ordered_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone < :end_at
GROUP BY sales_week ORDER BY sales_week;
''')).params(
timezone=user_tz,
statuses=tuple(
[LINE_ITEM_STATUS.CONFIRMED, LINE_ITEM_STATUS.CANCELLED]),
start_at=start_at,
end_at=end_at,
item_collection_ids=tuple(item_collection_ids),
).all())
for week_sale in week_sales:
ordered_week_sales[int(week_sale.sales_week)] = week_sale.sum
return ordered_week_sales
def sales_by_date(sales_datetime, item_ids, user_tz):
"""Return the sales amount accrued during the given day for given items."""
if not item_ids:
return None
start_at = midnight_to_utc(sales_datetime, timezone=user_tz)
end_at = midnight_to_utc(
sales_datetime + datetime.timedelta(days=1), timezone=user_tz
)
sales_on_date = (
db.session.query(db.column('sum'))
.from_statement(
db.text(
'''SELECT SUM(final_amount) FROM line_item
WHERE status=:status AND ordered_at >= :start_at AND ordered_at < :end_at
AND line_item.item_id IN :item_ids
'''
)
)
.params(
status=LINE_ITEM_STATUS.CONFIRMED,
start_at=start_at,
end_at=end_at,
item_ids=tuple(item_ids),
)
.scalar()
)
return sales_on_date if sales_on_date else Decimal(0)
def sales_by_date(sales_datetime, item_ids, user_tz):
"""
Returns the sales amount accrued during the day for a given date/datetime,
list of item_ids and timezone.
"""
if not item_ids:
return None
start_at = midnight_to_utc(sales_datetime, timezone=user_tz)
end_at = midnight_to_utc(sales_datetime + datetime.timedelta(days=1), timezone=user_tz)
sales_on_date = db.session.query('sum').from_statement(db.text('''SELECT SUM(final_amount) FROM line_item
WHERE status=:status AND ordered_at >= :start_at AND ordered_at < :end_at
AND line_item.item_id IN :item_ids
''')).params(status=LINE_ITEM_STATUS.CONFIRMED, start_at=start_at, end_at=end_at, item_ids=tuple(item_ids)).scalar()
return sales_on_date if sales_on_date else Decimal(0)
def item_collection_net_sales(self):
"""Returns the net revenue for an item collection"""
total_paid = db.session.query('sum').from_statement(
db.text('''SELECT SUM(amount) FROM payment_transaction
INNER JOIN customer_order ON payment_transaction.customer_order_id = customer_order.id
WHERE transaction_type=:transaction_type
AND customer_order.item_collection_id = :item_collection_id
''')).params(transaction_type=TRANSACTION_TYPE.PAYMENT,
item_collection_id=self.id).scalar()
total_refunded = db.session.query('sum').from_statement(
db.text('''SELECT SUM(amount) FROM payment_transaction
INNER JOIN customer_order ON payment_transaction.customer_order_id = customer_order.id
WHERE transaction_type=:transaction_type
AND customer_order.item_collection_id = :item_collection_id
''')).params(transaction_type=TRANSACTION_TYPE.REFUND,
item_collection_id=self.id).scalar()
if total_paid and total_refunded:
return total_paid - total_refunded
elif total_paid:
return total_paid
else:
return Decimal('0')
def counts_per_date_per_item(item_collection, user_tz):
"""
Returns number of line items sold per date per item.
Eg: {'2016-01-01': {'item-xxx': 20}}
"""
date_item_counts = {}
for item in item_collection.items:
item_id = unicode(item.id)
item_results = db.session.query('date', 'count').from_statement(
db.text('''SELECT DATE_TRUNC('day', line_item.ordered_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone)::date as date, count(line_item.id) AS count
FROM line_item WHERE item_id = :item_id AND status = :status
GROUP BY date ORDER BY date ASC''')).params(timezone=user_tz, status=LINE_ITEM_STATUS.CONFIRMED, item_id=item.id).all()
for date_count in item_results:
if not date_item_counts.get(date_count.date):
# if this date hasn't been been mapped in date_item_counts yet
date_item_counts[date_count.date] = {item_id: date_count.count}
else:
# if it has been mapped, assign the count
date_item_counts[date_count.date][item_id] = date_count.count
return date_item_counts
def counts_per_date_per_item(item_collection, user_tz):
"""
Returns number of line items sold per date per item.
Eg: {'2016-01-01': {'item-xxx': 20}}
"""
date_item_counts = {}
for item in item_collection.items:
item_id = unicode(item.id)
item_results = db.session.query('date', 'count').from_statement(
db.text(
'''SELECT DATE_TRUNC('day', line_item.ordered_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone)::date as date, count(line_item.id) AS count
FROM line_item WHERE item_id = :item_id AND status = :status
GROUP BY date ORDER BY date ASC''')).params(
timezone=user_tz,
status=LINE_ITEM_STATUS.CONFIRMED,
item_id=item.id).all()
for date_count in item_results:
if not date_item_counts.get(date_count.date):
# if this date hasn't been been mapped in date_item_counts yet
date_item_counts[date_count.date] = {item_id: date_count.count}
else:
# if it has been mapped, assign the count
date_item_counts[date_count.date][item_id] = date_count.count
return date_item_counts