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 test_isoweek_datetime_all_timezones(self):
"""Test that isoweek_datetime works for all timezones"""
for timezone in common_timezones:
for week in range(53):
isoweek_datetime(2017, week + 1, timezone)
def test_isoweek_datetime_all_timezones(self):
"""Test that isoweek_datetime works for all timezones"""
for timezone in common_timezones:
for week in range(53):
isoweek_datetime(2017, week + 1, timezone)
