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