def commissions_per_promotion(self, given_date):
"""
The total amount of commissions earned per promotion that day.
"""
# ensure given_date is a date
assert isinstance(given_date,
date), "given_date must be datetime.date()"
total_commission_per_promotion = [
i for i in cur.execute(
"""
SELECT promotion_id, Sum(product_price * quantity * rate)
FROM orders
INNER JOIN order_lines ON orders.id=order_lines.order_id
INNER JOIN vendor_commissions
ON orders.vendor_id=vendor_commissions.vendor_id
AND vendor_commissions.date LIKE ?
INNER JOIN product_promotions
ON order_lines.product_id=product_promotions.product_id
AND product_promotions.date LIKE ?
WHERE created_at LIKE ?
GROUP BY promotion_id
""", (given_date.isoformat() + '%', given_date.isoformat() + '%',
given_date.isoformat() + '%'))
]
return {i[0]: i[1] for i in total_commission_per_promotion}
def average_amount_commissions(self, given_date):
"""
The average amount of commissions per order for that day.
"""
# ensure given_date is a date
assert isinstance(given_date,
date), "given_date must be datetime.date()"
average_commissions = [
i[0] for i in cur.execute(
"""
SELECT Sum(product_price * quantity * rate)
FROM orders
INNER JOIN order_lines ON orders.id=order_lines.order_id
INNER JOIN vendor_commissions
ON orders.vendor_id=vendor_commissions.vendor_id
AND date LIKE ?
WHERE created_at LIKE ?
GROUP BY order_id
""", (given_date.isoformat() + '%', given_date.isoformat() + '%'))
]
if len(average_commissions) == 0:
return 0
else:
return sum(average_commissions) / len(average_commissions)
def total_discount(self, given_date):
"""
The total amount of discount given that day.
"""
# ensure given_date is a date
assert isinstance(given_date,
date), "given_date must be datetime.date()"
discounts = [
i[0] for i in cur.execute(
"""
SELECT discounted_amount
FROM orders
INNER JOIN order_lines ON orders.id=order_lines.order_id
WHERE created_at LIKE ?
""", (given_date.isoformat() + '%', ))
]
return sum(discounts)
def total_customers(self, given_date):
"""
The total number of customers that made an order that day.
"""
# ensure given_date is a date
assert isinstance(given_date,
date), "given_date must be datetime.date()"
customer_ids = [
i[0] for i in cur.execute(
"""
SELECT customer_id
FROM orders
WHERE created_at LIKE ?
""", (given_date.isoformat() + '%', ))
]
# return total number of customers without duplicates
return len(set(customer_ids))
def total_items_sold(self, given_date):
"""
The total number of items sold on a given day.
"""
# ensure given_date is a date
assert isinstance(given_date,
date), "given_date must be datetime.date()"
# get quantity from order_lines for given_date
quantities = [
i[0] for i in cur.execute(
"""
SELECT quantity
FROM orders
INNER JOIN order_lines ON orders.id=order_lines.order_id
WHERE created_at LIKE ?
""", (given_date.isoformat() + '%', ))
]
# sum quantity
return sum(quantities)
def average_discount_rate(self, given_date):
"""
The average discount rate applied to the items sold that day.
"""
# ensure given_date is a date
assert isinstance(given_date,
date), "given_date must be datetime.date()"
discount_rates = [
i[0] for i in cur.execute(
"""
SELECT discount_rate
FROM orders
INNER JOIN order_lines ON orders.id=order_lines.order_id
WHERE created_at LIKE ?
""", (given_date.isoformat() + '%', ))
]
if len(discount_rates) == 0:
return 0
else:
return sum(discount_rates) / len(discount_rates)
def average_order_total(self, given_date):
"""
The average order total for that day.
"""
# ensure given_date is a date
assert isinstance(given_date,
date), "given_date must be datetime.date()"
order_totals = [
i[0] for i in cur.execute(
"""
SELECT Sum(total_amount)
FROM orders
INNER JOIN order_lines ON orders.id=order_lines.order_id
WHERE created_at LIKE ?
GROUP BY order_id
""", (given_date.isoformat() + '%', ))
]
if len(order_totals) == 0:
return 0
else:
return sum(order_totals) / len(order_totals)
def total_amount_commissions(self, given_date):
"""
The total amount of commissions generated that day.
Assumption made -> commission rate apply to product_price * quantity
"""
# ensure given_date is a date
assert isinstance(given_date,
date), "given_date must be datetime.date()"
amount_commissions = [
i for i in cur.execute(
"""
SELECT product_price, quantity, rate
FROM orders
INNER JOIN order_lines ON orders.id=order_lines.order_id
INNER JOIN vendor_commissions
ON orders.vendor_id=vendor_commissions.vendor_id
AND date LIKE ?
WHERE created_at LIKE ?
""", (given_date.isoformat() + '%', given_date.isoformat() + '%'))
]
return sum([i[0] * i[1] * i[2] for i in amount_commissions])
