def aggregate_transactions_by_category_and_day(self, category, begin=None, end=None, dryrun=True):
self.log.info(' aggregate category: {}'.format(category))
params = {
'category': category,
}
if begin and end:
begin = datetime.strptime(begin, '%Y-%m-%d') - timedelta(days=1)
begin = datetime.strftime(begin, '%Y-%m-%d')
params['period'] = "AND _time > '{}' and _time <= '{}'".format(begin, end)
else:
params['period'] = ''
with connection.cursor() as cursor:
cmd = """
SELECT
to_char(_time, 'YYYY-MM-DD') AS day,
c.name AS category,
SUM(t.turnover) AS turnover
FROM
sale_transaction AS t
JOIN sale_product AS p
ON p.sku = t.product_id
JOIN sale_category AS c
ON c.id = p.category_id
WHERE c.name = '{category}'
{period}
GROUP BY day, category
ORDER BY day desc
""".format(**params)
cursor.execute(cmd)
rows = dictfetchall(cursor)
self.log.info(' found {} entries - dryrun: {}'.format(len(rows), dryrun))
all_categories = Category.objects.all()
if rows and not dryrun:
objects_to_create = []
for row in rows:
self.log.info(row)
obj = TransactionsByDay(_time=row['day'],
category=all_categories.get(name=row['category']),
turnover=row['turnover'])
objects_to_create.append(obj)
TransactionsByDay.objects.bulk_create(objects_to_create)
return rows
def aggregate_salesrank_history_by_day(self, product, begin=None, end=None, dryrun=True):
self.log.info(' aggregate sku: {}'.format(product.sku))
params = {
'sku': product.sku,
}
if begin and end:
begin = datetime.strptime(begin, '%Y-%m-%d') - timedelta(days=1)
end = datetime.strftime(begin, '%Y-%m-%d')
params['period'] = "AND _time > '{}' and _time <= '{}'".format(begin, end)
else:
params['period'] = ''
SalesRankHistoryByDay.objects.filter(product=product).delete()
with connection.cursor() as cursor:
cmd = """
SELECT
to_char(_time, 'YYYY-MM-DD') AS day,
AVG(salesrank)::integer AS avg_salesrank,
round(AVG(price)::numeric, 2) AS avg_price
FROM
sale_salesrankhistory
WHERE product_id = '{sku}'
{period}
GROUP BY day
ORDER BY day desc
""".format(**params)
cursor.execute(cmd)
rows = dictfetchall(cursor)
self.log.info(' found {} entries - dryrun: {}'.format(len(rows), dryrun))
if rows and not dryrun:
objects_to_create = []
for row in rows:
obj = SalesRankHistoryByDay(product=product,
price=row['avg_price'],
salesrank=row['avg_salesrank'],
_time=row['day'])
objects_to_create.append(obj)
SalesRankHistoryByDay.objects.bulk_create(objects_to_create)
return rows
