def _supply_optimization(request): def __get_max_date(): st = StatPublisher.objects.filter(date__lte=date.today().strftime('%Y-%m-%d')).aggregate(date=Max('date')) if st: return st['date'].strftime('%Y-%m-%d') return date.today().strftime('%Y-%m-%d') params = { 'user': int(request.GET.get('user', request.user.id if request.user.type == 'regular' else '') or 0), 'publisher': request.GET.get('publisher', ''), 'app': request.GET.get('app', ''), 'region': request.GET.get('region', ''), 'os': request.GET.get('os', ''), 'publisher_genre': request.GET.get('publisher_genre', ''), 'advertiser_genre': request.GET.get('advertiser_genre', ''), #'type': request.GET.get('type', ''), 'impression_avail': int(request.GET.get('impression_avail', '') or 0), 'impression': int(request.GET.get('impression', '') or 0), 'revenue': int(request.GET.get('revenue', '') or 0), 'date': request.GET.get('date', __get_max_date()), 'sort': request.GET.get('sort', '-revenue'), 'export': request.GET.get('export', ''), 'expand': request.GET.get('expand'), } join_claueses = {} join_claueses['user'] = (" AND a.user_id = %s " % params['user']) if params['user'] else '' join_claueses['advertiser_genre'] = (" AND ag.id=%s" % params['advertiser_genre']) if params['advertiser_genre'] else '' join_claueses['publisher_genre'] = (" AND pg.id=%s" % params['publisher_genre']) if params['publisher_genre'] else '' join_claueses['account'] = (" AND a.internal_id=%s " % params['publisher']) if params['publisher'] else '' where = ["b.active=true"] #if params['type']: # where.append("b.type = '%s'" % params['type']) if params['region']: where.append("b.country in ( %s )" % (','.join("'%s'" % cty for cty in REGIONAL_DATA[params['region']]))) if params['app']: where.append("b.app_id = %s" % params['app']) if params['os']: where.append("app.os = '%s'" % params['os']) having = [] if params['impression_avail']: having.append("SUM(COALESCE(b.impression_avail,0)) >= %s" % params['impression_avail']) if params['impression']: having.append("SUM(COALESCE(s.impression,0)) >= %s" % params['impression']) if params['revenue']: having.append("SUM(COALESCE(s.revenue,0)) >= %s" % params['revenue']) having_clause = '' if len(having): having_clause = "HAVING %s" % " AND ".join(having) limit_clause = '' cursor = connection.cursor() if not params['export']: sql = """ select count(*) from (select b.app_id, b.publisher_genre_id, b.advertiser_genre_id from stats_publisher_base b left join stats_publisher s on b.id=s.base_id and date='%s' join am.app app on app.app_id = b.app_id join genre ag on ag.id=b.advertiser_genre_id %s join genre pg on pg.id=b.publisher_genre_id %s join accounts a on a.internal_id = b.publisher_id and a.type='publisher' %s join sales_authuser u on u.id = a.user_id %s WHERE %s group by b.app_id, b.publisher_genre_id, b.advertiser_genre_id %s) sub """ \ % (params['date'], join_claueses['advertiser_genre'], join_claueses['publisher_genre'], join_claueses['account'], join_claueses['user'], ' AND '.join(where), having_clause) cursor.execute(sql) total_count = cursor.fetchone()[0] if total_count: paginator = Paginator(range(0, total_count), PAGE_SIZE) page = int(request.GET.get('page') or 1) try: _range = paginator.page(page) except PageNotAnInteger: _range = paginator.page(1) page = 1 except EmptyPage: _range = paginator.page(paginator.num_pages) page = paginator.num_pages params['pagination'] = { 'count': paginator.num_pages, 'current': page } limit_clause = "limit %d, %d" % (_range[0], PAGE_SIZE) order_by_clause = '' additional_fields = '' if params['sort']: desc = '-' in params['sort'] field = params['sort'].replace('-', '') if not 'status' in params['sort']: order_by_clause = 'ORDER BY %s %s' % (field, 'DESC' if desc else '') else: additional_fields = ',MIN(COALESCE(s.status,0)) all_on, SUM(COALESCE(s.status,0)) all_count' if not '-' in params['sort']: order_by_clause = 'ORDER BY all_on desc, all_count desc' else: order_by_clause = 'ORDER BY all_on,all_count' sql = """select a.name as publisher, b.publisher_id, b.app_id, b.publisher_genre_id, b.advertiser_genre_id, app.app as app_name, u.first_name, u.last_name, app.os, pg.genre_name as publisher_genre, ag.genre_name as advertiser_genre, SUM(COALESCE(b.impression_avail, 0)) as impression_avail, SUM(COALESCE(s.impression, 0)) as impressions, SUM(COALESCE(s.revenue, 0)) as revenue, SUM(COALESCE(s.margin, 0)) as margin %s from stats_publisher_base b left join stats_publisher s on b.id=s.base_id and date='%s' join am.app app on app.app_id = b.app_id join genre ag on ag.id=b.advertiser_genre_id %s join genre pg on pg.id=b.publisher_genre_id %s join accounts a on a.internal_id = b.publisher_id and a.type='publisher' %s join sales_authuser u on u.id = a.user_id %s WHERE %s group by b.app_id, b.publisher_genre_id, b.advertiser_genre_id %s %s %s""" % (additional_fields, params['date'], join_claueses['advertiser_genre'], join_claueses['publisher_genre'], join_claueses['account'], join_claueses['user'], " AND ".join(where), having_clause, order_by_clause, limit_clause) cursor.execute(sql) stats = dictfetchall(cursor) stats_res = [] def append_stat(_stat, region = ''): new_row = {} new_row.update(_stat) if region: new_row['region'] = region new_row['origin_os'] = _stat['os'] new_row['origin_app_name'] = _stat['app_name'] new_row['origin_publisher'] = _stat['publisher'] new_row['origin_publisher_genre'] = _stat['publisher_genre'] new_row['origin_advertiser_genre'] = _stat['advertiser_genre'] new_row['percent'] = (_stat['revenue'] / _stat['margin'] * 100) if _stat['margin'] != 0 else 0 new_row['row_id'] = _stat.get('_id', '') stats_res.append(new_row) def get_status(_stats): all_on = True least_on = False for stat in _stats: all_on = all_on and stat['status'] least_on = least_on or stat['status'] if all_on: return 'on' elif least_on: return 'limited' else: return 'off' index = 1 for stat in stats: current_row = {} current_row.update(stat) _where = [] _where.extend(where) _where.append(" b.app_id=%s" % current_row['app_id']) #_where.append(" b.app_name='%s'" % current_row['app_name']) #_where.append(" b.os = '%s'" % current_row['os']) _where.append(" b.publisher_genre_id=%s" % current_row['publisher_genre_id']) _where.append(" b.advertiser_genre_id=%s" % current_row['advertiser_genre_id']) #_where.append(" b.type='%s'" % current_row['type']) sql = """select b.id, b.country, s.status, b.impression_avail, COALESCE(s.impression,0) as impressions, COALESCE(s.revenue,0) as revenue, COALESCE(s.margin,0) as margin from stats_publisher_base b left join stats_publisher s on b.id=s.base_id and date='%s' join am.app app on app.app_id = b.app_id join genre ag on ag.id=b.advertiser_genre_id %s join genre pg on pg.id=b.publisher_genre_id %s join accounts a on a.internal_id = b.publisher_id and a.type='publisher' %s join sales_authuser u on u.id = a.user_id %s WHERE %s """ % (params['date'], join_claueses['advertiser_genre'], join_claueses['publisher_genre'], join_claueses['account'], join_claueses['user'], " AND ".join(_where)) cursor.execute(sql) sub_stats = dictfetchall(cursor) current_row['country'] = params['region'] if params['region'] else 'All' current_row['status'] = get_status(sub_stats) current_row['id'] = index current_row['parent'] = False current_row['_id'] = '' current_row['child_ids'] = ','.join([str(s['id']) for s in sub_stats]) append_stat(current_row, params['region']) current_row['child_ids'] = '' all_parent = {} all_parent.update(current_row) if not params['region']: for r in regions: regions_rows = [] revenue = 0 margin = 0 impression_avail = 0 impressions = 0 #get and append regional rows for s in sub_stats: if s['country'] in REGIONAL_DATA[r]: regions_rows.append(s) revenue += s['revenue'] margin += s['margin'] impression_avail += s['impression_avail'] impressions += s['impressions'] if regions_rows: index += 1 current_row['country'] = r current_row['revenue'] = revenue current_row['margin'] = margin current_row['impression_avail'] = impression_avail current_row['impressions'] = impressions current_row['status'] = get_status(regions_rows) current_row['id'] = index current_row['parent'] = all_parent['id'] current_row['_id'] = '' current_row['child_ids'] = ','.join([str(s['id']) for s in regions_rows]) append_stat(current_row, r) current_row['child_ids'] = '' #append country level rows sub_parent = {} sub_parent.update(current_row) for s in regions_rows: index += 1 current_row['country'] = s['country'] current_row['revenue'] = s['revenue'] current_row['margin'] = s['margin'] current_row['impression_avail'] = s['impression_avail'] current_row['impressions'] = s['impressions'] current_row['status'] = 'on' if s['status'] else 'off' current_row['id'] = index current_row['parent'] = sub_parent['id'] current_row['_id'] = s['id'] append_stat(current_row) else: for s in sub_stats: index += 1 current_row['country'] = s['country'] current_row['revenue'] = s['revenue'] current_row['margin'] = s['margin'] current_row['impression_avail'] = s['impression_avail'] current_row['impressions'] = s['impressions'] current_row['status'] = 'on' if s['status'] else 'off' current_row['_id'] = s['id'] current_row['id'] = index current_row['parent'] = all_parent['id'] append_stat(current_row) index += 1 prev_row = {} stat_common_fields = [ 'publisher', 'app_name', 'first_name', 'last_name', 'os', 'publisher_genre', 'advertiser_genre'] for _stat in stats_res: flag = True origin = {} origin.update(_stat) for f in stat_common_fields: if flag and prev_row.get(f) == _stat.get(f): _stat[f] = '' else: flag = False prev_row.update(origin) res = { 'params': params, 'rows': stats_res } return res
def _demand_optimization(request): def __get_max_date(): st = StatAdvertiser.objects.filter(date__lte=date.today().strftime('%Y-%m-%d')).aggregate(date=Max('date')) if st: return st['date'].strftime('%Y-%m-%d') return date.today().strftime('%Y-%m-%d') params = { 'campaign': MySQLdb.escape_string(request.GET.get('campaign', '')), 'advertiser': MySQLdb.escape_string(request.GET.get('advertiser', '')), 'user': int(request.GET.get('user', request.user.id if request.user.type == 'regular' else '') or 0), 'region': MySQLdb.escape_string(request.GET.get('region', '')), 'os': MySQLdb.escape_string(request.GET.get('os', '')), 'genre': MySQLdb.escape_string(request.GET.get('genre', '')), 'traffic': MySQLdb.escape_string(request.GET.get('traffic', '')), 'type': MySQLdb.escape_string(request.GET.get('type', '')), 'date': MySQLdb.escape_string(request.GET.get('date', __get_max_date())), 'sort': MySQLdb.escape_string(request.GET.get('sort', '-revenue')), 'budget__gte': int(request.GET.get('budget__gte') or 0), 'revenue__gte': int(request.GET.get('revenue__gte') or 0), 'export': MySQLdb.escape_string(request.GET.get('export', '')), 'expand': request.GET.get('expand') } join_claueses = {} join_claueses['campaign'] = (" AND c.campaign_id = %s " % params['campaign']) if params['campaign'] else '' join_claueses['advertiser'] = (" AND c.advertiser_id = %s " % params['advertiser']) if params['advertiser'] \ else '' join_claueses['user'] = (" AND a.user_id = %s " % params['user']) if params['user'] else '' join_claueses['campaign'] += (" AND c.os = '%s' " % params['os']) if params['os'] else '' join_claueses['genre'] = (" AND g.id=%s" % params['genre']) if params['genre'] else '' where = ["b.active=true"] if params['type']: where.append("b.type = '%s'" % params['type']) if params['traffic']: where.append("b.traffic='%s'" % params['traffic']) if params['budget__gte']: where.append("budget>=%s" % params['budget__gte']) if params['region']: where.append("b.country in ( %s )" % (','.join("'%s'" % cty for cty in REGIONAL_DATA[params['region']]))) having = [] if params['revenue__gte']: having.append("SUM(revenue)>=%s" % params['revenue__gte']) if params['budget__gte']: having.append("SUM(budget)>=%s" % params['budget__gte']) limit_clause = '' cursor = connection.cursor() if not params['export']: sql = """select count(*) from (select b.campaign_id, b.traffic, b.type from stats_advertiser_base b left join stats_advertiser s on b.id=s.base_id and date='%s' join campaigns c on c.campaign_id = b.campaign_id %s join genre g on g.id = c.genre_id %s join accounts a on a.internal_id = c.advertiser_id and a.type = 'advertiser' %s join sales_authuser u on u.id = a.user_id %s %s group by b.campaign_id, traffic, b.type %s) sub""" % (params['date'], join_claueses['campaign'], join_claueses['genre'], join_claueses['advertiser'], join_claueses['user'], (("WHERE " + " AND ".join(where)) if len(where) else ''), (("HAVING " + " AND ".join(having)) if len(having) else '')) cursor.execute(sql) total_count = cursor.fetchone()[0] if total_count: paginator = Paginator(range(0, total_count), PAGE_SIZE) page = int(request.GET.get('page') or 1) try: _range = paginator.page(page) except PageNotAnInteger: _range = paginator.page(1) page = 1 except EmptyPage: _range = paginator.page(paginator.num_pages) page = paginator.num_pages params['pagination'] = { 'count': paginator.num_pages, 'current': page } limit_clause = "limit %d, %d" % (_range[0], PAGE_SIZE) additional_fields = '' order_by_clause = '' if params['sort']: desc = '-' in params['sort'] field = params['sort'].replace('-', '') if not 'status' in params['sort']: order_by_clause = 'ORDER BY %s %s' % (field, 'DESC' if desc else '') else: additional_fields = ',MIN(COALESCE(campaign_status,0)) all_on, SUM(COALESCE(campaign_status,0)) all_count' if not '-' in params['sort']: order_by_clause = 'ORDER BY all_on desc, all_count desc' else: order_by_clause = 'ORDER BY all_on,all_count' sql = """select b.campaign_id, c.advertiser_id, campaign, traffic, b.type, c.os, g.genre_name, c.genre_id, SUM(COALESCE(budget, 0)) as budget, SUM(COALESCE(revenue, 0)) as revenue, u.first_name, u.last_name, a.name as advertiser %s from stats_advertiser_base b left join stats_advertiser s on b.id=s.base_id and date='%s' join campaigns c on c.campaign_id = b.campaign_id %s join genre g on g.id = c.genre_id %s join accounts a on a.internal_id = c.advertiser_id and a.type = 'advertiser' %s join sales_authuser u on u.id = a.user_id %s %s group by b.campaign_id, traffic, b.type %s %s %s """ \ % (additional_fields, params['date'], join_claueses['campaign'], join_claueses['genre'], join_claueses['advertiser'], join_claueses['user'], (("WHERE " + " AND ".join(where)) if len(where) else ''), (("HAVING " + " AND ".join(having)) if len(having) else ''), order_by_clause, limit_clause) cursor.execute(sql) stats = dictfetchall(cursor) stats_res = [] def append_stat(_stat, region=''): new_row = {} new_row.update(_stat) new_row['traffic_copy'] = _stat['traffic'] new_row['campaign_copy'] = _stat['campaign'] new_row['os_copy'] = _stat['os'] new_row['region'] = region _stat['revenue'] = round(_stat['revenue'], 0) _stat['budget'] = round(_stat['budget'], 0) new_row['percent'] = (_stat['revenue'] / _stat['budget'] * 100) if _stat['budget'] != 0 else 0 new_row['row_id'] = _stat.get('_id', '') stats_res.append(new_row) def get_status(_stats): all_on = True least_on = False for stat in _stats: all_on = all_on and stat['campaign_status'] least_on = least_on or stat['campaign_status'] if all_on: return 'on' elif least_on: return 'limited' else: return 'off' index = 1 for stat in stats: current_row = {} current_row.update(stat) _where = [] _where.extend(where) _where.append(" b.traffic='%s'" % current_row['traffic']) _where.append(" b.type='%s'" % current_row['type']) _where.append(" b.campaign_id=%s" % current_row['campaign_id']) sql = """select b.country, s.campaign_status, COALESCE(s.revenue, 0) as revenue, b.budget, b.id, a.name as advertiser, campaign_status from stats_advertiser_base b left join stats_advertiser s on b.id=s.base_id and date='%s' join campaigns c on c.campaign_id = b.campaign_id %s join genre g on g.id = c.genre_id %s join accounts a on a.internal_id = c.advertiser_id and a.type = 'advertiser' %s join sales_authuser u on u.id = a.user_id %s WHERE %s""" % (params['date'], join_claueses['campaign'], join_claueses['genre'], join_claueses['advertiser'], join_claueses['user'], " AND ".join(_where)) cursor.execute(sql) sub_stats=dictfetchall(cursor) current_row['country'] = params['region'] if params['region'] else 'All' current_row['campaign_status'] = get_status(sub_stats) current_row['id'] = index current_row['parent'] = False current_row['_id'] = '' current_row['child_ids'] = ','.join([str(s['id']) for s in sub_stats]) append_stat(current_row, params['region']) current_row['child_ids'] = '' all_parent = {} all_parent.update(current_row) if not params['region']: for r in regions: regions_rows = [] budget = 0 revenue = 0 #get and append regional rows for s in sub_stats: if s['country'] in REGIONAL_DATA[r]: regions_rows.append(s) budget += s['budget'] revenue += s['revenue'] if regions_rows: index += 1 current_row['country'] = r current_row['revenue'] = revenue current_row['budget'] = budget current_row['campaign_status'] = get_status(regions_rows) current_row['id'] = index current_row['parent'] = all_parent['id'] current_row['_id'] = '' current_row['child_ids'] = ','.join([str(s['id']) for s in regions_rows]) append_stat(current_row, r) current_row['child_ids'] = '' #append country level rows sub_parent = {} sub_parent.update(current_row) for s in regions_rows: index += 1 current_row['country'] = s['country'] current_row['revenue'] = s['revenue'] current_row['budget'] = s['budget'] current_row['campaign_status'] = 'on' if s['campaign_status'] else 'off' current_row['id'] = index current_row['parent'] = sub_parent['id'] current_row['_id'] = s['id'] append_stat(current_row) else: for s in sub_stats: index += 1 current_row['country'] = s['country'] current_row['revenue'] = s['revenue'] current_row['budget'] = s['budget'] current_row['campaign_status'] = 'on' if s['campaign_status'] else 'off' current_row['_id'] = s['id'] current_row['id'] = index current_row['parent'] = all_parent['id'] append_stat(current_row) index += 1 prev_row = {} stat_common_fields = [ 'advertiser', 'campaign', 'first_name', 'last_name', 'os', 'genre_name', 'traffic', 'type'] for _stat in stats_res: flag = True origin = {} origin.update(_stat) for f in stat_common_fields: if flag and prev_row.get(f) == _stat.get(f): _stat[f] = '' else: flag = False prev_row.update(origin) res = { 'params': params, 'rows': stats_res } return res