def pre_month_medium_orders_money(self): if self.medium_money2: pre_medium_money2 = float(self.medium_money2) / \ ((self.medium_end - self.medium_start).days + 1) else: pre_medium_money2 = 0 if self.medium_money: pre_medium_money = float(self.medium_money) / \ ((self.medium_end - self.medium_start).days + 1) else: pre_medium_money = 0 if self.sale_money: pre_sale_money = float(self.sale_money) / \ ((self.medium_end - self.medium_start).days + 1) else: pre_sale_money = 0 pre_month_days = get_monthes_pre_days(datetime.datetime.strptime(self.start_date_cn, '%Y-%m-%d'), datetime.datetime.strptime(self.end_date_cn, '%Y-%m-%d')) pre_month_money_data = [] for k in pre_month_days: pre_month_money_data.append( {'medium_money': '%.2f' % (pre_medium_money * k['days']), 'medium_money2': '%.2f' % (pre_medium_money2 * k['days']), 'sale_money': '%.2f' % (pre_sale_money * k['days']), 'month': k['month'], 'days': k['days']}) return pre_month_money_data
def associated_douban_orders_pro_month_money(self, year, month, sale_type): try: if len(set(self.locations)) > 1: l_count = len(set(self.locations)) else: l_count = 1 if sale_type == 'agent': count = len(self.agent_sales) user = self.agent_sales[0] else: count = len(self.direct_sales) user = self.direct_sales[0] if user.team.location == 3 and len(self.locations) > 1: if sale_type == 'agent': count = len(self.agent_sales) else: count = len(self.direct_sales) elif user.team.location == 3 and len(self.locations) == 1: count = len(self.agent_sales + self.direct_sales) except: return 0 if self.associated_douban_orders.count(): pre_money = float(self.associated_douban_orders[0].money) / \ ((self.medium_end - self.medium_start).days + 1) pre_month_days = get_monthes_pre_days(datetime.datetime.strptime(self.start_date_cn, '%Y-%m-%d'), datetime.datetime.strptime(self.end_date_cn, '%Y-%m-%d')) pre_month_money_data = 0 search_pro_month = datetime.datetime.strptime( year + '-' + month, "%Y-%m") for k in pre_month_days: if k['month'] == search_pro_month: pre_month_money_data = round(pre_money * k['days'], 2) break return pre_month_money_data / count / l_count return 0
def _order_executive_reports(order, month_day): money, start, end = order.money, order.client_start, order.client_end back_moneys = order.backmoneys back_invoice = order.backinvoicerebates back_total = 0 # 获取到指定月之前的所有回款 for k in list(back_moneys) + list(back_invoice): back_time = k.back_time.replace(day=1).date() if back_time <= month_day: back_total += k.money if money: pre_money = float(money) / ((start - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(datetime.datetime.strptime(start.strftime('%Y-%m-%d'), '%Y-%m-%d'), datetime.datetime.strptime(end.strftime('%Y-%m-%d'), '%Y-%m-%d')) # pre_month_money_data = [] for k in pre_month_days: money = pre_money * k['days'] month = k['month'].date() # 回款逐月递减,减到指定月后,看总回款的剩余情况,计算指定月未回款金额 back_total -= money if month_day == month: if back_total <= 0: return money else: if back_total > money: return money else: return money - back_total else: continue return 0
def pre_month_money(money, start, end): if money: pre_money = float(money) / ((end - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(start, end) pre_month_money_data = [] for k in pre_month_days: pre_month_money_data.append({'month': k['month'], 'money': pre_money * k['days']}) return pre_month_money_data
def pre_month_money(money, start, end): if money: pre_money = float(money) / ((end - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(start, end) pre_month_money_data = {} for k in pre_month_days: pre_month_money_data[k['month']] = pre_money * k['days'] return pre_month_money_data
def pre_month_money(money, start, end, locations): if money: pre_money = float(money) / ((end - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(start, end) pre_month_money_data = [] for k in pre_month_days: money = pre_money * k['days'] / len(set(locations)) pre_month_money_data.append({'month': k['month'], 'money': money}) return pre_month_money_data
def _order_executive_reports(money, start, end): if money: pre_money = float(money) / ((start - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(datetime.datetime.strptime(start.strftime('%Y-%m-%d'), '%Y-%m-%d'), datetime.datetime.strptime(end.strftime('%Y-%m-%d'), '%Y-%m-%d')) pre_month_money_data = [] for k in pre_month_days: pre_month_money_data.append( {'money': pre_money * k['days'], 'month_day': k['month'], 'days': k['days']}) return pre_month_money_data
def pre_month_money(money, start, end): start = datetime.datetime.strptime(start.strftime('%Y-%m-%d'), '%Y-%m-%d') end = datetime.datetime.strptime(end.strftime('%Y-%m-%d'), '%Y-%m-%d') if money: pre_money = float(money) / ((end - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(start, end) pre_month_money_data = [] for k in pre_month_days: pre_month_money_data.append({'month_day': k['month'], 'money': pre_money * k['days']}) return pre_month_money_data
def pre_month_money(self): if self.money: pre_money = float(self.money) / \ ((self.client_end - self.client_start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(datetime.datetime.strptime(self.start_date_cn, '%Y-%m-%d'), datetime.datetime.strptime(self.end_date_cn, '%Y-%m-%d')) pre_month_money_data = [] for k in pre_month_days: pre_month_money_data.append( {'money': '%.2f' % (pre_money * k['days']), 'month': k['month'], 'days': k['days']}) return pre_month_money_data
def pre_month_money(self): if self.money: pre_money = float(self.money) / \ ((self.client_end - self.client_start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(datetime.datetime.strptime(self.start_date_cn, '%Y-%m-%d'), datetime.datetime.strptime(self.end_date_cn, '%Y-%m-%d')) pre_month_money_data = [] for k in pre_month_days: pre_month_money_data.append( {'money': '%.2f' % (pre_money * k['days']), 'month': k['month'], 'days': k['days']}) return pre_month_money_data
def douban_order_json(): if not (g.user.is_super_leader() or g.user.is_aduit() or g.user.is_finance()): abort(403) now_date = datetime.datetime.now() location = int(request.values.get('location', 0)) year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') client_params = {} now_monthes = get_monthes_pre_days(now_year_start, now_year_end) for k in now_monthes: client_params[k['month'].date()] = { 'orders': [], 'order_count': 0, 'order_pre_money': 0 } # 获取所有合同 orders = DoubanOrder.all() for k in orders: if k.contract_status in [2, 4, 5, 10, 19, 20]: create_month = k.client_start.replace(day=1) if create_month in client_params: if location == 0: client_params[create_month]['orders'].append(k) elif location in k.locations: client_params[create_month]['orders'].append(k) client_params = sorted(client_params.iteritems(), key=lambda x: x[0]) # 初始化highcharts数据 data = [] data.append({'name': u'客户成交数量', 'data': []}) data.append({'name': u'客户平均成交额', 'data': []}) # 根据时间组装合同 for k, v in client_params: order_count = len(v['orders']) sum_order_money = sum( [_get_money_by_location(i, location) for i in v['orders']]) if order_count: order_pre_money = sum_order_money / order_count else: order_pre_money = 0 # 主装highcharts数据 day_time_stamp = int( datetime.datetime.strptime(str(k), '%Y-%m-%d').strftime('%s')) * 1000 data[0]['data'].append([day_time_stamp, order_count]) data[1]['data'].append([day_time_stamp, order_pre_money]) return jsonify({'data': data, 'title': u'直签豆瓣订单客户数量分析'})
def client_order_json(): if not (g.user.is_super_leader() or g.user.is_aduit() or g.user.is_finance()): abort(403) now_date = datetime.datetime.now() location = int(request.values.get('location', 0)) year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') orders = ClientOrder.query.filter(ClientOrder.status == 1, ClientOrder.client_end <= now_year_end) client_params = {} now_monthes = get_monthes_pre_days(now_year_start, now_year_end) for k in now_monthes: client_params[k['month'].date()] = {'back_moneys': 0, 'un_back_moneys': 0} # 回款 back_moneys = list(ClientBackMoney.all()) + \ list(ClientBackInvoiceRebate.all()) back_moneys = _format_back_money(back_moneys, 'client', location) for k in back_moneys: if k['back_time'] in client_params: client_params[k['back_time']]['back_moneys'] += k['money'] # 计算未回款金额累计 orders = [_format_client_order(k, location) for k in orders if k.contract_status in [ 2, 4, 5, 10, 19, 20]] for k in client_params: need_back_money = _need_back_money(orders, k) client_params[k][ 'un_back_moneys'] = need_back_money - _back_money(k, back_moneys) client_params = sorted( client_params.iteritems(), key=lambda x: x[0]) # 初始化highcharts数据 data = [] data.append({'name': u'已回款金额', 'data': []}) data.append({'name': u'未回款金额', 'data': []}) for k, v in client_params: day_time_stamp = int(datetime.datetime.strptime( str(k), '%Y-%m-%d').strftime('%s')) * 1000 data[0]['data'].append([day_time_stamp, v['back_moneys']]) data[1]['data'].append([day_time_stamp, v['un_back_moneys']]) return jsonify({'data': data, 'title': u'新媒体订单回款分析(包含返点发票)'})
def search_excle_data(): now_date = datetime.datetime.now() location = 0 year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') client_params = {} now_monthes = get_monthes_pre_days(now_year_start, now_year_end) for k in now_monthes: client_params[k['month'].date()] = { 'orders': [], 'order_count': 0, 'order_pre_money': 0 } # 获取所有合同 orders = searchAdClientOrder.all() for k in orders: if k.contract_status in [2, 4, 5, 10, 19, 20]: create_month = k.client_start.replace(day=1) if create_month in client_params: if location == 0: client_params[create_month]['orders'].append(k) elif location in k.locations: client_params[create_month]['orders'].append(k) client_params = sorted(client_params.iteritems(), key=lambda x: x[0]) headings = [u'月份', u'成单客户数', u'平均客户金额'] data = [] data.append([str(k + 1) + u'月' for k in range(len(client_params))]) # 成单客户数 count_client = [] # 平均客户金额 pre_money_client = [] for k, v in client_params: order_count = len(v['orders']) sum_order_money = sum( [_get_money_by_location(i, location) for i in v['orders']]) if order_count: order_pre_money = sum_order_money / order_count else: order_pre_money = 0 count_client.append(order_count) pre_money_client.append(order_pre_money) data.append(count_client) data.append(pre_money_client) return {'data': data, 'title': u'搜索业务客户数量分析', 'headings': headings}
def client_order_excle_data(): now_date = datetime.datetime.now() location = int(request.values.get('location', 0)) year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') orders = ClientOrder.query.filter(ClientOrder.status == 1, ClientOrder.client_end <= now_year_end) client_params = {} now_monthes = get_monthes_pre_days(now_year_start, now_year_end) for k in now_monthes: client_params[k['month'].date()] = {'back_moneys': 0, 'un_back_moneys': 0} # 回款 back_moneys = list(ClientBackMoney.all()) + \ list(ClientBackInvoiceRebate.all()) back_moneys = _format_back_money(back_moneys, 'client', location) for k in back_moneys: if k['back_time'] in client_params: client_params[k['back_time']]['back_moneys'] += k['money'] # 计算未回款金额累计 orders = [_format_client_order(k, location) for k in orders if k.contract_status in [ 2, 4, 5, 10, 19, 20]] for k in client_params: need_back_money = _need_back_money(orders, k) client_params[k][ 'un_back_moneys'] = need_back_money - _back_money(k, back_moneys) client_params = sorted( client_params.iteritems(), key=lambda x: x[0]) headings = [u'月份', u'已汇款金额', u'未回款金额'] data = [] data.append([str(k + 1) + u'月' for k in range(len(client_params))]) # 成单客户数 back_moneys = [] # 平均客户金额 un_back_moneys = [] for k, v in client_params: back_moneys.append(v['back_moneys']) un_back_moneys.append(v['un_back_moneys']) data.append(back_moneys) data.append(un_back_moneys) return {'data': data, 'title': u'新媒体订单回款分析(包含返点发票)', 'headings': headings}
def _order_executive_reports(money, start, end): if money: pre_money = float(money) / ((start - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days( datetime.datetime.strptime(start.strftime('%Y-%m-%d'), '%Y-%m-%d'), datetime.datetime.strptime(end.strftime('%Y-%m-%d'), '%Y-%m-%d')) pre_month_money_data = [] for k in pre_month_days: pre_month_money_data.append({ 'money': pre_money * k['days'], 'month_day': k['month'], 'days': k['days'] }) return pre_month_money_data
def douban_order_json(): if not (g.user.is_super_leader() or g.user.is_aduit() or g.user.is_finance()): abort(403) now_date = datetime.datetime.now() location = int(request.values.get('location', 0)) year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') client_params = {} now_monthes = get_monthes_pre_days(now_year_start, now_year_end) for k in now_monthes: client_params[k['month'].date()] = {'orders': [], 'order_count': 0, 'order_pre_money': 0} # 获取所有合同 orders = DoubanOrder.all() for k in orders: if k.contract_status in [2, 4, 5, 10, 19, 20]: create_month = k.client_start.replace(day=1) if create_month in client_params: if location == 0: client_params[create_month]['orders'].append(k) elif location in k.locations: client_params[create_month]['orders'].append(k) client_params = sorted( client_params.iteritems(), key=lambda x: x[0]) # 初始化highcharts数据 data = [] data.append({'name': u'客户成交数量', 'data': []}) data.append({'name': u'客户平均成交额', 'data': []}) # 根据时间组装合同 for k, v in client_params: order_count = len(v['orders']) sum_order_money = sum([_get_money_by_location(i, location) for i in v['orders']]) if order_count: order_pre_money = sum_order_money / order_count else: order_pre_money = 0 # 主装highcharts数据 day_time_stamp = int(datetime.datetime.strptime( str(k), '%Y-%m-%d').strftime('%s')) * 1000 data[0]['data'].append([day_time_stamp, order_count]) data[1]['data'].append([day_time_stamp, order_pre_money]) return jsonify({'data': data, 'title': u'直签豆瓣订单客户数量分析'})
def search_excle_data(): now_date = datetime.datetime.now() location = 0 year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') client_params = {} now_monthes = get_monthes_pre_days(now_year_start, now_year_end) for k in now_monthes: client_params[k['month'].date()] = {'orders': [], 'order_count': 0, 'order_pre_money': 0} # 获取所有合同 orders = searchAdClientOrder.all() for k in orders: if k.contract_status in [2, 4, 5, 10, 19, 20]: create_month = k.client_start.replace(day=1) if create_month in client_params: if location == 0: client_params[create_month]['orders'].append(k) elif location in k.locations: client_params[create_month]['orders'].append(k) client_params = sorted( client_params.iteritems(), key=lambda x: x[0]) headings = [u'月份', u'成单客户数', u'平均客户金额'] data = [] data.append([str(k + 1) + u'月' for k in range(len(client_params))]) # 成单客户数 count_client = [] # 平均客户金额 pre_money_client = [] for k, v in client_params: order_count = len(v['orders']) sum_order_money = sum([_get_money_by_location(i, location) for i in v['orders']]) if order_count: order_pre_money = sum_order_money / order_count else: order_pre_money = 0 count_client.append(order_count) pre_money_client.append(order_pre_money) data.append(count_client) data.append(pre_money_client) return {'data': data, 'title': u'搜索业务客户数量分析', 'headings': headings}
def pre_month_money(self): if self.num: num = float(self.num) / \ ((self.order_end_date - self.order_start_date).days + 1) else: num = 0 if self.pay_num: pay_num = float(self.pay_num) / \ ((self.order_end_date - self.order_start_date).days + 1) else: pay_num = 0 pre_month_days = get_monthes_pre_days(datetime.datetime.strptime(self.order_start_date_cn, '%Y-%m-%d'), datetime.datetime.strptime(self.order_end_date_cn, '%Y-%m-%d')) pre_month_money_data = [] for k in pre_month_days: pre_month_money_data.append( {'num': '%.2f' % (num * k['days']), 'pay_num': '%.2f' % (pay_num * k['days']), 'month': k['month'], 'days': k['days']}) return pre_month_money_data
def search_client_order(): if not (g.user.is_super_leader() or g.user.is_aduit() or g.user.is_finance() or g.user.is_contract() or g.user.is_searchad_member()): abort(403) now_date = datetime.datetime.now() year = int(request.values.get('year', now_date.year)) # 获取整年月份 pre_year_month = get_monthes_pre_days(datetime.datetime.strptime(str(year) + '-01', '%Y-%m'), datetime.datetime.strptime(str(year) + '-12', '%Y-%m')) # 获取所有回款包含返点发票 back_money_data = _all_search_client_order_back_moneys() # 获取所有媒体返点 confirm_money_data = _all_search_confirm_money() # 获取当年合同 orders = searchAdClientOrder.query.filter(searchAdClientOrder.status == 1) # 去重合同 orders = [k for k in orders if k.client_start.year == year or k.client_end.year == year] # 格式化合同 orders = [_search_order_to_dict(k, back_money_data, confirm_money_data, pre_year_month ) for k in orders] # 去掉撤单、申请中的合同 orders = [k for k in orders if k['contract_status'] in [2, 4, 5, 10, 19, 20]] orders = sorted( orders, key=operator.itemgetter('start_date_cn'), reverse=False) total_money_data = [0 for k in range(12)] medium_medium_money2_data = [0 for k in range(12)] for k in orders: total_money_data = numpy.array( total_money_data) + numpy.array(k['money_data']) medium_medium_money2_data = numpy.array( medium_medium_money2_data) + numpy.array(k['medium_medium_money2_data']) if request.values.get('action') == 'excel': return write_search_order_excel(orders=orders, year=year, total_money_data=total_money_data, medium_medium_money2_data=medium_medium_money2_data) return tpl('/searchAd/data_query/accrued/search_client_order.html', orders=orders, year=year, total_money_data=total_money_data, medium_medium_money2_data=medium_medium_money2_data)
def search_json(): if not (g.user.is_super_leader() or g.user.is_aduit() or g.user.is_finance()): abort(403) now_date = datetime.datetime.now() location = 0 year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') last_year_start = datetime.datetime.strptime( str(year - 1) + '-01-01', '%Y-%m-%d') last_year_end = datetime.datetime.strptime( str(year - 1) + '-12-01', '%Y-%m-%d') before_last_year_start = datetime.datetime.strptime( str(year - 2) + '-01-01', '%Y-%m-%d') before_last_year_end = datetime.datetime.strptime( str(year - 2) + '-12-01', '%Y-%m-%d') medium_orders = searchAdMediumOrderExecutiveReport.query.filter( searchAdMediumOrderExecutiveReport.month_day >= before_last_year_start, searchAdMediumOrderExecutiveReport.month_day <= now_year_end) medium_orders = [_format_order(k) for k in medium_orders if k.status == 1] now_monthes = get_monthes_pre_days(now_year_start, now_year_end) last_monthes = get_monthes_pre_days(last_year_start, last_year_end) before_monthes = get_monthes_pre_days(before_last_year_start, before_last_year_end) # 格式化成字典 now_monthes_data = {} for k in now_monthes: now_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 last_monthes_data = {} for k in last_monthes: last_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 before_monthes_data = {} for k in before_monthes: before_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 for order in medium_orders: if order['contract_status'] in [2, 4, 5, 10, 19, 20 ] and order['status'] == 1: if int(order['month_day'].strftime( '%s')) * 1000 in now_monthes_data: now_monthes_data[int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location( order, location) if int(order['month_day'].strftime( '%s')) * 1000 in last_monthes_data: last_monthes_data[int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location( order, location) if int(order['month_day'].strftime( '%s')) * 1000 in before_monthes_data: before_monthes_data[int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location( order, location) # 格式化数据,把近三年数据放在一年用于画图 now_monthes_data = sorted(now_monthes_data.iteritems(), key=lambda x: x[0]) # now_monthes_data.reverse() last_monthes_data = sorted(last_monthes_data.iteritems(), key=lambda x: x[0]) # last_monthes_data.reverse() before_monthes_data = sorted(before_monthes_data.iteritems(), key=lambda x: x[0]) # before_monthes_data.reverse() before_monthes_params = [] last_monthes_params = [] now_monthes_params = [] # 整理近三年数据 for k, v in now_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) now_monthes_params.append({ 'time': int(month_day.strftime('%s')) * 1000, 'money': v }) for k, v in last_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) last_monthes_params.append({ 'time': int(month_day.strftime('%s')) * 1000, 'money': v }) for k, v in before_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) before_monthes_params.append({ 'time': int(month_day.strftime('%s')) * 1000, 'money': v }) data = [] data.append({ 'name': str(before_last_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in before_monthes_params] }) data.append({ 'name': str(last_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in last_monthes_params] }) data.append({ 'name': str(now_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in now_monthes_params] }) return jsonify({'data': data, 'title': u'搜索业务执行额分析'})
def _order_back_money_data(order, start_Q_month, end_Q_month, back_moneys): order_back_moneys = [k for k in back_moneys if k['order_id'] == order.id] order_back_moneys = sorted(order_back_moneys, key=operator.itemgetter('back_time'), reverse=True) # 获取合同每月的执行金额,用于计算回款属于哪个执行月 money = order.money start = order.client_start end = order.client_end if money: pre_money = float(money) / ((end - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days( datetime.datetime.strptime(start.strftime('%Y-%m-%d'), '%Y-%m-%d'), datetime.datetime.strptime(end.strftime('%Y-%m-%d'), '%Y-%m-%d')) total_back_money = money for k in range(len(pre_month_days)): # 累计执行额 month_money = pre_money * pre_month_days[k]['days'] for b in range(len(order_back_moneys)): back_money = order_back_moneys[b]['money'] if 'belong_time' not in order_back_moneys[b]: if month_money - back_money > 0.0: order_back_moneys[b]['belong_time'] = pre_month_days[k][ 'month'] month_money -= back_money total_back_money -= back_money elif month_money - back_money < 0.0: order_back_moneys[b]['money'] = month_money order_back_moneys[b]['belong_time'] = pre_month_days[k][ 'month'] total_back_money -= month_money # 当回款金额大于当月执行额是,要把回款进行拆分,归属到下个执行月 if b != len(order_back_moneys) - 1: db = {} db['type'] = order_back_moneys[b]['type'] db['money'] = back_money - month_money db['back_time'] = order_back_moneys[b]['back_time'] db['order'] = order_back_moneys[b]['order'] db['order_id'] = order_back_moneys[b]['order_id'] order_back_moneys.insert(b + 1, db) month_money = 0.0 continue else: db = {} db['type'] = order_back_moneys[b]['type'] db['money'] = back_money - month_money db['back_time'] = order_back_moneys[b]['back_time'] db['order'] = order_back_moneys[b]['order'] db['order_id'] = order_back_moneys[b]['order_id'] if k == len(pre_month_days) - 1: db['belong_time'] = pre_month_days[k]['month'] else: db['belong_time'] = pre_month_days[k + 1]['month'] order_back_moneys.insert(b + 1, db) month_money = 0.0 break else: order_back_moneys[b]['belong_time'] = pre_month_days[k][ 'month'] month_money = 0.0 total_back_money -= back_money order_back_moneys = [ k for k in order_back_moneys if k['back_time'] >= start_Q_month and k['back_time'] < end_Q_month ] return order_back_moneys
def search_excle_data(): now_date = datetime.datetime.now() location = 0 year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') last_year_start = datetime.datetime.strptime( str(year - 1) + '-01-01', '%Y-%m-%d') last_year_end = datetime.datetime.strptime( str(year - 1) + '-12-01', '%Y-%m-%d') before_last_year_start = datetime.datetime.strptime( str(year - 2) + '-01-01', '%Y-%m-%d') before_last_year_end = datetime.datetime.strptime( str(year - 2) + '-12-01', '%Y-%m-%d') medium_orders = searchAdMediumOrderExecutiveReport.query.filter( searchAdMediumOrderExecutiveReport.month_day >= before_last_year_start, searchAdMediumOrderExecutiveReport.month_day <= now_year_end) medium_orders = [_format_order(k) for k in medium_orders if k.status == 1] now_monthes = get_monthes_pre_days(now_year_start, now_year_end) last_monthes = get_monthes_pre_days(last_year_start, last_year_end) before_monthes = get_monthes_pre_days(before_last_year_start, before_last_year_end) # 格式化成字典 now_monthes_data = {} for k in now_monthes: now_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 last_monthes_data = {} for k in last_monthes: last_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 before_monthes_data = {} for k in before_monthes: before_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 for order in medium_orders: if order['contract_status'] in [2, 4, 5, 10, 19, 20 ] and order['status'] == 1: if int(order['month_day'].strftime( '%s')) * 1000 in now_monthes_data: now_monthes_data[int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location( order, location) if int(order['month_day'].strftime( '%s')) * 1000 in last_monthes_data: last_monthes_data[int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location( order, location) if int(order['month_day'].strftime( '%s')) * 1000 in before_monthes_data: before_monthes_data[int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location( order, location) # 格式化数据,把近三年数据放在一年用于画图 now_monthes_data = sorted(now_monthes_data.iteritems(), key=lambda x: x[0]) last_monthes_data = sorted(last_monthes_data.iteritems(), key=lambda x: x[0]) before_monthes_data = sorted(before_monthes_data.iteritems(), key=lambda x: x[0]) headings = [ '月份', str(before_last_year_start.year) + u'年度', str(last_year_start.year) + u'年度', str(now_year_start.year) + u'年度' ] data = [] data.append([str(k + 1) + u'月' for k in range(len(now_monthes_data))]) data.append([v for k, v in before_monthes_data]) data.append([v for k, v in last_monthes_data]) data.append([v for k, v in now_monthes_data]) return {'data': data, 'title': u'搜索业务执行额分析', 'headings': headings}
def money(): now_date = datetime.datetime.now() year = int(request.values.get('year', now_date.year)) start_date_month = datetime.datetime.strptime( str(year) + '-01' + '-01', '%Y-%m-%d') end_date_month = datetime.datetime.strptime( str(year) + '-12' + '-31', '%Y-%m-%d') pre_monthes = get_monthes_pre_days(start_date_month, end_date_month) # 获取代理返点系数 all_agent_rebate = _all_agent_rebate() # 获取媒体返点系数 all_medium_rebate = _all_medium_rebate() # 直签豆瓣订单开始 douban_orders = DoubanOrderExecutiveReport.query.filter( DoubanOrderExecutiveReport.month_day >= start_date_month, DoubanOrderExecutiveReport.month_day <= end_date_month) douban_orders = [ _douban_order_to_dict(k, all_agent_rebate) for k in douban_orders if k.status == 1 ] douban_orders = [ k for k in douban_orders if k['contract_status'] in [2, 4, 5, 10, 19, 20] and k['status'] == 1 ] douban_money = _get_medium_moneys(douban_orders, pre_monthes, 0, 'zhiqian_order', year) # 直签豆瓣订单结束 # 媒体订单开始 medium_orders = MediumOrderExecutiveReport.query.filter( MediumOrderExecutiveReport.month_day >= start_date_month, MediumOrderExecutiveReport.month_day <= end_date_month) # 关联豆瓣订单开始 ass_douban_order = [ _ass_medium_order_to_dict(k, all_agent_rebate, all_medium_rebate) for k in medium_orders if k.order.associated_douban_order ] ass_douban_order = [ k for k in ass_douban_order if k['contract_status'] in [2, 4, 5, 10, 19, 20] and k['status'] == 1 ] ass_douban_money = _get_medium_moneys(ass_douban_order, pre_monthes, 0, 'medium_order', year) # 关联豆瓣订单结束 # 普通媒体订单 medium_orders = [ _client_order_to_dict(k, all_agent_rebate, all_medium_rebate) for k in medium_orders if k.status == 1 ] medium_orders = [ k for k in medium_orders if k['contract_status'] in [2, 4, 5, 10, 19, 20] and k['status'] == 1 ] youli_money = _get_medium_moneys(medium_orders, pre_monthes, [3], 'medium_order', year) wuxian_money = _get_medium_moneys(medium_orders, pre_monthes, [8], 'medium_order', year) momo_money = _get_medium_moneys(medium_orders, pre_monthes, [7], 'medium_order', year) zhihu_money = _get_medium_moneys(medium_orders, pre_monthes, [5], 'medium_order', year) xiachufang_money = _get_medium_moneys(medium_orders, pre_monthes, [6], 'medium_order', year) xueqiu_money = _get_medium_moneys(medium_orders, pre_monthes, [9], 'medium_order', year) huxiu_money = _get_medium_moneys(medium_orders, pre_monthes, [14, 57], 'medium_order', year) kecheng_money = _get_medium_moneys(medium_orders, pre_monthes, [4], 'medium_order', year) midi_money = _get_medium_moneys(medium_orders, pre_monthes, [21], 'medium_order', year) weipiao_money = _get_medium_moneys(medium_orders, pre_monthes, [52], 'medium_order', year) one_money = _get_medium_moneys(medium_orders, pre_monthes, [51], 'medium_order', year) # ---计算大于100W的媒体 up_money = {} other_money = { 'sale_money': [0 for k in range(48)], 'money2': [0 for k in range(48)], 'm_ex_money': [0 for k in range(48)], 'a_rebate': [0 for k in range(48)], 'profit': [0 for k in range(48)] } # 用于计算合计的其他媒体售卖金额 total_except_money = [0 for k in range(48)] total_a_rebate = [0 for k in range(48)] for k in Medium.all(): if int(k.id) not in except_medium_ids: u_medium = _get_medium_moneys(medium_orders, pre_monthes, [int(k.id)], 'medium_order', year) if sum(u_medium['sale_money']) >= 1000000: up_money[k.name] = u_medium else: other_money['sale_money'] = numpy.array( other_money['sale_money']) + numpy.array( u_medium['sale_money']) other_money['money2'] = numpy.array( other_money['money2']) + numpy.array(u_medium['money2']) other_money['m_ex_money'] = numpy.array( other_money['m_ex_money']) + numpy.array( u_medium['m_ex_money']) other_money['a_rebate'] = numpy.array( other_money['a_rebate']) + numpy.array( u_medium['a_rebate']) other_money['profit'] = numpy.array( other_money['profit']) + numpy.array(u_medium['profit']) total_except_money = numpy.array(total_except_money) + numpy.array( u_medium['sale_money']) total_a_rebate = numpy.array(total_a_rebate) + numpy.array( u_medium['a_rebate']) # 计算大于100W的媒体结束--- # 媒体订单结束 # 搜索直签订单开始 searchAd_medium_orders = searchAdMediumOrderExecutiveReport.query.filter( searchAdMediumOrderExecutiveReport.month_day >= start_date_month, searchAdMediumOrderExecutiveReport.month_day <= end_date_month) searchAd_medium_orders = [ _search_order_to_dict(k) for k in searchAd_medium_orders ] searchAd_medium_orders = [ k for k in searchAd_medium_orders if k['contract_status'] in [2, 4, 5, 10, 19, 20] and k['status'] == 1 ] searchAD_money = _get_medium_moneys(searchAd_medium_orders, pre_monthes, 0, 's_medium_order', year) # 搜索直签订单结束 # 搜索返点订单开始 searchAd_rebate_orders = searchAdRebateOrderExecutiveReport.query.filter( searchAdMediumOrderExecutiveReport.month_day >= start_date_month, searchAdMediumOrderExecutiveReport.month_day <= end_date_month) searchAd_rebate_orders = [ _search_rebate_order_to_dict(k) for k in searchAd_rebate_orders if k.status == 1 ] searchAd_rebate_orders = [ k for k in searchAd_rebate_orders if k['contract_status'] in [2, 4, 5, 10, 19, 20] ] rebate_order_money = _get_medium_moneys(searchAd_rebate_orders, pre_monthes, 0, '', year) # 搜索返点订单结束 # 搜索业务毛利+返点收入 searchAD_money['profit'] = numpy.array( searchAD_money['profit']) + numpy.array( rebate_order_money['sale_money']) # 豆瓣收入、服务费、返点、毛利为直签豆瓣+优力和无线总和 douban_money['sale_money'] = numpy.array( douban_money['sale_money']) + numpy.array(ass_douban_money['money2']) if year == 2016: douban_money['money2'] = numpy.array( douban_money['money2']) + numpy.array( [k * 0.18 for k in ass_douban_money['money2']]) douban_money['a_rebate'] = [0 for k in range(48)] douban_money['profit'] = numpy.array( douban_money['money2']) - numpy.array(douban_money['a_rebate']) elif year == 2014: douban_money['money2'] = numpy.array( douban_money['money2']) + numpy.array( [k * 0.426 for k in ass_douban_money['money2']]) douban_money['a_rebate'] = numpy.array( douban_money['a_rebate']) + numpy.array( ass_douban_money['a_rebate']) douban_money['profit'] = numpy.array( douban_money['money2']) - numpy.array(douban_money['a_rebate']) else: douban_money['money2'] = numpy.array( douban_money['money2']) + numpy.array( [k * 0.4 for k in ass_douban_money['money2']]) douban_money['a_rebate'] = numpy.array( douban_money['a_rebate']) + numpy.array( ass_douban_money['a_rebate']) douban_money['profit'] = numpy.array( douban_money['money2']) - numpy.array(douban_money['a_rebate']) # 计算豆瓣收入、服务费、返点、毛利为直签豆瓣+优力和无线总和 if g.user.is_aduit() and str(year) == '2014': total = numpy.array(momo_money['sale_money']) +\ numpy.array(zhihu_money['sale_money']) +\ numpy.array(xiachufang_money['sale_money']) +\ numpy.array(xueqiu_money['sale_money']) +\ numpy.array(huxiu_money['sale_money']) +\ numpy.array(kecheng_money['sale_money']) +\ numpy.array(midi_money['sale_money']) +\ numpy.array(weipiao_money['sale_money']) +\ numpy.array(one_money['sale_money']) +\ numpy.array(total_except_money) +\ numpy.array(searchAD_money['sale_money']) +\ numpy.array(rebate_order_money['sale_money']) else: total = numpy.array(douban_money['profit']) +\ numpy.array(momo_money['sale_money']) +\ numpy.array(zhihu_money['sale_money']) +\ numpy.array(xiachufang_money['sale_money']) +\ numpy.array(xueqiu_money['sale_money']) +\ numpy.array(huxiu_money['sale_money']) +\ numpy.array(kecheng_money['sale_money']) +\ numpy.array(midi_money['sale_money']) +\ numpy.array(weipiao_money['sale_money']) +\ numpy.array(one_money['sale_money']) +\ numpy.array(total_except_money) +\ numpy.array(searchAD_money['sale_money']) +\ numpy.array(rebate_order_money['sale_money']) if request.values.get('action', '') == 'download': response = write_medium_money_excel( pre_monthes=pre_monthes, douban_money=douban_money, youli_money=youli_money, wuxian_money=wuxian_money, momo_money=momo_money, zhihu_money=zhihu_money, xiachufang_money=xiachufang_money, xueqiu_money=xueqiu_money, huxiu_money=huxiu_money, kecheng_money=kecheng_money, weipiao_money=weipiao_money, one_money=one_money, midi_money=midi_money, other_money=other_money, searchAD_money=searchAD_money, rebate_order_money=rebate_order_money, total=total, up_money=up_money, year=str(year)) return response return tpl('/data_query/super_leader/medium_money.html', pre_monthes=pre_monthes, douban_money=douban_money, youli_money=youli_money, wuxian_money=wuxian_money, momo_money=momo_money, zhihu_money=zhihu_money, xiachufang_money=xiachufang_money, xueqiu_money=xueqiu_money, huxiu_money=huxiu_money, kecheng_money=kecheng_money, weipiao_money=weipiao_money, one_money=one_money, midi_money=midi_money, other_money=other_money, total=total, searchAD_money=searchAD_money, rebate_order_money=rebate_order_money, year=str(year), up_money=up_money)
def search_excle_data(): now_date = datetime.datetime.now() location = 0 year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') last_year_start = datetime.datetime.strptime( str(year - 1) + '-01-01', '%Y-%m-%d') last_year_end = datetime.datetime.strptime( str(year - 1) + '-12-01', '%Y-%m-%d') before_last_year_start = datetime.datetime.strptime( str(year - 2) + '-01-01', '%Y-%m-%d') before_last_year_end = datetime.datetime.strptime( str(year - 2) + '-12-01', '%Y-%m-%d') medium_orders = searchAdMediumOrderExecutiveReport.query.filter( searchAdMediumOrderExecutiveReport.month_day >= before_last_year_start, searchAdMediumOrderExecutiveReport.month_day <= now_year_end) medium_orders = [_format_order(k) for k in medium_orders if k.status == 1] now_monthes = get_monthes_pre_days(now_year_start, now_year_end) last_monthes = get_monthes_pre_days(last_year_start, last_year_end) before_monthes = get_monthes_pre_days( before_last_year_start, before_last_year_end) # 格式化成字典 now_monthes_data = {} for k in now_monthes: now_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 last_monthes_data = {} for k in last_monthes: last_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 before_monthes_data = {} for k in before_monthes: before_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 for order in medium_orders: if order['contract_status'] in [2, 4, 5, 10, 19, 20] and order['status'] == 1: if int(order['month_day'].strftime('%s')) * 1000 in now_monthes_data: now_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) if int(order['month_day'].strftime('%s')) * 1000 in last_monthes_data: last_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) if int(order['month_day'].strftime('%s')) * 1000 in before_monthes_data: before_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) # 格式化数据,把近三年数据放在一年用于画图 now_monthes_data = sorted(now_monthes_data.iteritems(), key=lambda x: x[0]) last_monthes_data = sorted( last_monthes_data.iteritems(), key=lambda x: x[0]) before_monthes_data = sorted( before_monthes_data.iteritems(), key=lambda x: x[0]) headings = ['月份', str(before_last_year_start.year) + u'年度', str(last_year_start.year) + u'年度', str(now_year_start.year) + u'年度'] data = [] data.append([str(k + 1) + u'月' for k in range(len(now_monthes_data))]) data.append([v for k, v in before_monthes_data]) data.append([v for k, v in last_monthes_data]) data.append([v for k, v in now_monthes_data]) return {'data': data, 'title': u'搜索业务执行额分析', 'headings': headings}
def index(): if not (g.user.is_super_leader() or g.user.is_media_leader()): return abort(403) now_date = datetime.datetime.now() year = int(request.values.get('year', now_date.year)) start_date_month = datetime.datetime.strptime( str(year) + '-01' + '-01', '%Y-%m-%d') end_date_month = datetime.datetime.strptime( str(year) + '-12' + '-31', '%Y-%m-%d') pre_monthes = get_monthes_pre_days(start_date_month, end_date_month) medium_id = int(request.values.get('medium_id', 0)) ex_medium_orders = [o for o in Order.all( ) if o.medium_start.year == year or o.medium_end.year == year] obj_data = [] for order in ex_medium_orders: pre_month_sale_money = pre_month_money(order.sale_money, order.medium_start, order.medium_end) pre_month_medium_money2 = pre_month_money(order.medium_money2, order.medium_start, order.medium_end) # 单笔返点 agent_rebate = order.client_order.agent_rebate try: self_agent_rebate = order.client_order.self_agent_rebate self_agent_rebate = float(self_agent_rebate.split('-')[0]) self_agent_rebate_value = float(self_agent_rebate.split('-')[1]) except: self_agent_rebate = 0 self_agent_rebate_value = 0 for p in range(len(pre_month_sale_money)): d_order = {} d_order['medium_id'] = order.medium.id d_order['money'] = order.client_order.money d_order['contract_status'] = order.client_order.contract_status d_order['status'] = order.client_order.status d_order['sale_money'] = pre_month_sale_money[p]['money'] d_order['medium_money2'] = pre_month_medium_money2[p]['money'] d_order['month_day'] = pre_month_sale_money[p]['month_day'] medium_rebate = order.medium_rebate_by_year(d_order['month_day']) # 媒体单笔返点 try: self_medium_rebate_data = order.self_medium_rebate self_medium_rebate = self_medium_rebate_data.split('-')[0] self_medium_rebate_value = float(self_medium_rebate_data.split('-')[1]) except: self_medium_rebate = 0 self_medium_rebate_value = 0 if int(self_medium_rebate): if d_order['sale_money']: d_order['medium_rebate_value'] = d_order['sale_money'] / d_order['money'] * self_medium_rebate_value else: d_order['medium_rebate_value'] == 0 else: d_order['medium_rebate_value'] = d_order['medium_money2'] * medium_rebate / 100 if self_agent_rebate: if d_order['money']: d_order['agent_rebate_value'] = self_agent_rebate_value * d_order['sale_money'] / d_order['money'] else: d_order['agent_rebate_value'] = 0 else: d_order['agent_rebate_value'] = d_order['sale_money'] * agent_rebate / 100 obj_data.append(d_order) ex_medium_orders = [k for k in obj_data if k[ 'contract_status'] not in [0, 1, 3, 6, 7, 8, 81, 9] and k['status'] == 1] medium_data = [] if medium_id: mediums = Medium.query.filter_by(id=medium_id) else: mediums = Medium.all() sum_sale_money = 0 for k in mediums: sale_money_data = [] medium_money2_data = [] medium_rebate_data = [] agent_rebate_data = [] for i in pre_monthes: sale_money_data.append(sum([ex['sale_money'] for ex in ex_medium_orders if ex[ 'medium_id'] == k.id and ex['month_day'].date() == i['month'].date()])) medium_money2_data.append(sum([ex['medium_money2'] for ex in ex_medium_orders if ex[ 'medium_id'] == k.id and ex['month_day'].date() == i['month'].date()])) medium_rebate_data.append(sum([ex['medium_rebate_value'] for ex in ex_medium_orders if ex[ 'medium_id'] == k.id and ex['month_day'].date() == i['month'].date()])) agent_rebate_data.append(sum([ex['agent_rebate_value'] for ex in ex_medium_orders if ex[ 'medium_id'] == k.id and ex['month_day'].date() == i['month'].date()])) sum_sale_money += sum(sale_money_data) medium_data.append({'id': k.id, 'name': k.name, 'level': k.level or 100, 'sale_money_data': sale_money_data, 'medium_money2_data': medium_money2_data, 'medium_rebate_data': medium_rebate_data, 'agent_rebate_data': agent_rebate_data}) print sum_sale_money medium_data = sorted( medium_data, key=operator.itemgetter('level'), reverse=False) if request.values.get('action', '') == 'download': return write_client_excel(medium_data, year) return tpl('/data_query/medium/index.html', medium_data=medium_data, medium_id=medium_id, year=year, params="?medium_id=%s&year=%s" % ( medium_id, str(year)), s_mediums=Medium.all())
def search_json(): if not (g.user.is_super_leader() or g.user.is_aduit() or g.user.is_finance()): abort(403) now_date = datetime.datetime.now() location = 0 year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') last_year_start = datetime.datetime.strptime( str(year - 1) + '-01-01', '%Y-%m-%d') last_year_end = datetime.datetime.strptime( str(year - 1) + '-12-01', '%Y-%m-%d') before_last_year_start = datetime.datetime.strptime( str(year - 2) + '-01-01', '%Y-%m-%d') before_last_year_end = datetime.datetime.strptime( str(year - 2) + '-12-01', '%Y-%m-%d') medium_orders = searchAdMediumOrderExecutiveReport.query.filter( searchAdMediumOrderExecutiveReport.month_day >= before_last_year_start, searchAdMediumOrderExecutiveReport.month_day <= now_year_end) medium_orders = [_format_order(k) for k in medium_orders if k.status == 1] now_monthes = get_monthes_pre_days(now_year_start, now_year_end) last_monthes = get_monthes_pre_days(last_year_start, last_year_end) before_monthes = get_monthes_pre_days( before_last_year_start, before_last_year_end) # 格式化成字典 now_monthes_data = {} for k in now_monthes: now_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 last_monthes_data = {} for k in last_monthes: last_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 before_monthes_data = {} for k in before_monthes: before_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 for order in medium_orders: if order['contract_status'] in [2, 4, 5, 10, 19, 20] and order['status'] == 1: if int(order['month_day'].strftime('%s')) * 1000 in now_monthes_data: now_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) if int(order['month_day'].strftime('%s')) * 1000 in last_monthes_data: last_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) if int(order['month_day'].strftime('%s')) * 1000 in before_monthes_data: before_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) # 格式化数据,把近三年数据放在一年用于画图 now_monthes_data = sorted(now_monthes_data.iteritems(), key=lambda x: x[0]) # now_monthes_data.reverse() last_monthes_data = sorted( last_monthes_data.iteritems(), key=lambda x: x[0]) # last_monthes_data.reverse() before_monthes_data = sorted( before_monthes_data.iteritems(), key=lambda x: x[0]) # before_monthes_data.reverse() before_monthes_params = [] last_monthes_params = [] now_monthes_params = [] # 整理近三年数据 for k, v in now_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) now_monthes_params.append({'time': int(month_day.strftime( '%s')) * 1000, 'money': v}) for k, v in last_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) last_monthes_params.append({'time': int(month_day.strftime( '%s')) * 1000, 'money': v}) for k, v in before_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) before_monthes_params.append({'time': int(month_day.strftime( '%s')) * 1000, 'money': v}) data = [] data.append({'name': str(before_last_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in before_monthes_params]}) data.append({'name': str(last_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in last_monthes_params]}) data.append({'name': str(now_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in now_monthes_params]}) return jsonify({'data': data, 'title': u'搜索业务执行额分析'})
else: d_order['is_c_douban'] = False except: d_order['is_c_douban'] = False d_order['status'] = order.status return d_order if __name__ == '__main__': year = 2015 start_date_month = datetime.datetime.strptime( str(year) + '-01' + '-01', '%Y-%m-%d') end_date_month = datetime.datetime.strptime( str(year) + '-12' + '-31', '%Y-%m-%d') pre_monthes = get_monthes_pre_days(start_date_month, end_date_month) # 获取关联豆瓣订单代理返点,用于快速计算关联豆瓣订单代理 try: agent_youli_rebate = AgentRebate.query.filter_by( year=start_date_month, agent_id=105).first().douban_rebate except: agent_youli_rebate = 0 try: agent_wuxian_rebate = AgentRebate.query.filter_by( year=start_date_month, agent_id=94).first().douban_rebate except: agent_wuxian_rebate = 0 try: agent_haohai_rebate = AgentRebate.query.filter_by( year=start_date_month, agent_id=228).first().douban_rebate
def douban_order_json(): if not (g.user.is_super_leader() or g.user.is_aduit() or g.user.is_finance()): abort(403) now_date = datetime.datetime.now() location = int(request.values.get('location', 0)) year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') last_year_start = datetime.datetime.strptime( str(year - 1) + '-01-01', '%Y-%m-%d') last_year_end = datetime.datetime.strptime( str(year - 1) + '-12-01', '%Y-%m-%d') before_last_year_start = datetime.datetime.strptime( str(year - 2) + '-01-01', '%Y-%m-%d') before_last_year_end = datetime.datetime.strptime( str(year - 2) + '-12-01', '%Y-%m-%d') douban_orders = DoubanOrderExecutiveReport.query.filter( DoubanOrderExecutiveReport.month_day >= before_last_year_start, DoubanOrderExecutiveReport.month_day <= now_year_end) medium_orders = MediumOrderExecutiveReport.query.filter( MediumOrderExecutiveReport.month_day >= before_last_year_start, MediumOrderExecutiveReport.month_day <= now_year_end) douban_date = [_format_order(k, 'douban') for k in douban_orders if k.status == 1] medium_orders = [_format_order(k) for k in medium_orders if k.status == 1] douban_date += [{'month_day': k['month_day'], 'money':k['money'], 'locations':k['locations']} for k in medium_orders if k['medium_id'] in [3, 8]] now_monthes = get_monthes_pre_days(now_year_start, now_year_end) last_monthes = get_monthes_pre_days(last_year_start, last_year_end) before_monthes = get_monthes_pre_days( before_last_year_start, before_last_year_end) now_monthes_data = {} for k in now_monthes: now_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 last_monthes_data = {} for k in last_monthes: last_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 before_monthes_data = {} for k in before_monthes: before_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 for order in douban_date: if int(order['month_day'].strftime('%s')) * 1000 in now_monthes_data: now_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) if int(order['month_day'].strftime('%s')) * 1000 in last_monthes_data: last_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) if int(order['month_day'].strftime('%s')) * 1000 in before_monthes_data: before_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) # 格式化数据,把近三年数据放在一年用于画图 now_monthes_data = sorted(now_monthes_data.iteritems(), key=lambda x: x[0]) # now_monthes_data.reverse() last_monthes_data = sorted( last_monthes_data.iteritems(), key=lambda x: x[0]) # last_monthes_data.reverse() before_monthes_data = sorted( before_monthes_data.iteritems(), key=lambda x: x[0]) # before_monthes_data.reverse() before_monthes_params = [] last_monthes_params = [] now_monthes_params = [] for k, v in now_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) now_monthes_params.append({'time': int(month_day.strftime( '%s')) * 1000, 'money': v}) for k, v in last_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) last_monthes_params.append({'time': int(month_day.strftime( '%s')) * 1000, 'money': v}) for k, v in before_monthes_data: month_day = datetime.datetime.fromtimestamp(k / 1000) month_day = month_day.replace(year=year) before_monthes_params.append({'time': int(month_day.strftime( '%s')) * 1000, 'money': v}) data = [] data.append({'name': str(before_last_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in before_monthes_params]}) data.append({'name': str(last_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in last_monthes_params]}) data.append({'name': str(now_year_start.year) + u'年度', 'data': [[k['time'], k['money']] for k in now_monthes_params]}) return jsonify({'data': data, 'title': u'直签豆瓣订单(含:优力、无线)执行额分析'})
def index(): if not (g.user.is_super_leader() or g.user.is_aduit() or g.user.is_finance() or g.user.is_contract()): abort(403) shenji = int(request.values.get('shenji', 0)) now_date = datetime.datetime.now() year = int(request.values.get('year', now_date.year)) # 获取整年月份 pre_year_month = get_monthes_pre_days(datetime.datetime.strptime(str(year) + '-01', '%Y-%m'), datetime.datetime.strptime(str(year) + '-12', '%Y-%m')) # 获取所有回款包含返点发票 back_money_data = _all_client_order_back_moneys() # 获取代理返点系数 all_agent_rebate = _all_agent_rebate() # 获取媒体返点系数 all_medium_rebate = _all_medium_rebate() # 获取所有外包信息 all_outsource = _all_client_order_outsource() # 获取当年合同 orders = ClientOrder.query.filter(ClientOrder.status == 1, ClientOrder.contract != '') # 去重合同 orders = [k for k in orders if k.client_start.year == year or k.client_end.year == year] # 格式化合同 orders = [_client_order_to_dict(k, back_money_data, all_agent_rebate, all_medium_rebate, pre_year_month, all_outsource, shenji) for k in orders] # 去掉撤单、申请中的合同 orders = [k for k in orders if k['contract_status'] in [2, 4, 5, 10, 19, 20]] orders = sorted( orders, key=operator.itemgetter('start_date_cn'), reverse=False) total_outsource_data = [0 for k in range(12)] total_money_data = [0 for k in range(12)] total_money_rebate_data = [0 for k in range(12)] total_profit_data = [0 for k in range(12)] total_medium_money2_data = [0 for k in range(12)] total_medium_money2_rebate_data = [0 for k in range(12)] for k in orders: total_outsource_data = numpy.array( total_outsource_data) + numpy.array(k['outsource_data']) total_money_data = numpy.array( total_money_data) + numpy.array(k['money_data']) total_money_rebate_data = numpy.array( total_money_rebate_data) + numpy.array(k['money_rebate_data']) total_profit_data = numpy.array( total_profit_data) + numpy.array(k['profit_data']) total_medium_money2_data = numpy.array( total_medium_money2_data) + numpy.array(k['total_medium_money2_data']) total_medium_money2_rebate_data = numpy.array( total_medium_money2_rebate_data) + numpy.array(k['total_medium_money2_rebate_data']) if request.values.get('action') == 'excel': return write_client_order_excel(orders=orders, year=year, total_money_data=total_money_data, total_money_rebate_data=total_money_rebate_data, total_profit_data=total_profit_data, total_medium_money2_data=total_medium_money2_data, total_medium_money2_rebate_data=total_medium_money2_rebate_data, total_outsource_data=total_outsource_data, shenji=shenji) if shenji: tpl_html = '/shenji/cost_income_client_order_s.html' else: tpl_html = '/shenji/cost_income_client_order.html' return tpl(tpl_html, orders=orders, year=year, total_money_data=total_money_data, total_money_rebate_data=total_money_rebate_data, total_profit_data=total_profit_data, total_medium_money2_data=total_medium_money2_data, total_medium_money2_rebate_data=total_medium_money2_rebate_data, total_outsource_data=total_outsource_data)
def money(): now_date = datetime.datetime.now() year = int(request.values.get('year', now_date.year)) start_date_month = datetime.datetime.strptime( str(year) + '-01' + '-01', '%Y-%m-%d') end_date_month = datetime.datetime.strptime( str(year) + '-12' + '-31', '%Y-%m-%d') pre_monthes = get_monthes_pre_days(start_date_month, end_date_month) # 获取代理返点系数 all_agent_rebate = _all_agent_rebate() # 获取媒体返点系数 all_medium_rebate = _all_medium_rebate() # 直签豆瓣订单开始 douban_orders = DoubanOrderExecutiveReport.query.filter( DoubanOrderExecutiveReport.month_day >= start_date_month, DoubanOrderExecutiveReport.month_day <= end_date_month) douban_orders = [_douban_order_to_dict(k, all_agent_rebate) for k in douban_orders if k.status == 1] douban_orders = [k for k in douban_orders if k[ 'contract_status'] in [2, 4, 5, 10, 19, 20] and k['status'] == 1] douban_money = _get_medium_moneys( douban_orders, pre_monthes, 0, 'zhiqian_order', year) # 直签豆瓣订单结束 # 媒体订单开始 medium_orders = MediumOrderExecutiveReport.query.filter( MediumOrderExecutiveReport.month_day >= start_date_month, MediumOrderExecutiveReport.month_day <= end_date_month) # 关联豆瓣订单开始 ass_douban_order = [_ass_medium_order_to_dict( k, all_agent_rebate, all_medium_rebate) for k in medium_orders if k.order.associated_douban_order] ass_douban_order = [k for k in ass_douban_order if k[ 'contract_status'] in [2, 4, 5, 10, 19, 20] and k['status'] == 1] ass_douban_money = _get_medium_moneys( ass_douban_order, pre_monthes, 0, 'medium_order', year) # 关联豆瓣订单结束 # 普通媒体订单 medium_orders = [_client_order_to_dict(k, all_agent_rebate, all_medium_rebate) for k in medium_orders if k.status == 1] medium_orders = [k for k in medium_orders if k[ 'contract_status'] in [2, 4, 5, 10, 19, 20] and k['status'] == 1] youli_money = _get_medium_moneys( medium_orders, pre_monthes, [3], 'medium_order', year) wuxian_money = _get_medium_moneys( medium_orders, pre_monthes, [8], 'medium_order', year) momo_money = _get_medium_moneys( medium_orders, pre_monthes, [7], 'medium_order', year) zhihu_money = _get_medium_moneys( medium_orders, pre_monthes, [5], 'medium_order', year) xiachufang_money = _get_medium_moneys( medium_orders, pre_monthes, [6], 'medium_order', year) xueqiu_money = _get_medium_moneys( medium_orders, pre_monthes, [9], 'medium_order', year) huxiu_money = _get_medium_moneys( medium_orders, pre_monthes, [14, 57], 'medium_order', year) kecheng_money = _get_medium_moneys( medium_orders, pre_monthes, [4], 'medium_order', year) midi_money = _get_medium_moneys( medium_orders, pre_monthes, [21], 'medium_order', year) weipiao_money = _get_medium_moneys( medium_orders, pre_monthes, [52], 'medium_order', year) one_money = _get_medium_moneys( medium_orders, pre_monthes, [51], 'medium_order', year) # ---计算大于100W的媒体 up_money = {} other_money = {'sale_money': [0 for k in range(48)], 'money2': [0 for k in range(48)], 'm_ex_money': [0 for k in range(48)], 'a_rebate': [0 for k in range(48)], 'profit': [0 for k in range(48)]} # 用于计算合计的其他媒体售卖金额 total_except_money = [0 for k in range(48)] total_a_rebate = [0 for k in range(48)] for k in Medium.all(): if int(k.id) not in except_medium_ids: u_medium = _get_medium_moneys( medium_orders, pre_monthes, [int(k.id)], 'medium_order', year) if sum(u_medium['sale_money']) >= 1000000: up_money[k.name] = u_medium else: other_money['sale_money'] = numpy.array( other_money['sale_money']) + numpy.array(u_medium['sale_money']) other_money['money2'] = numpy.array( other_money['money2']) + numpy.array(u_medium['money2']) other_money['m_ex_money'] = numpy.array( other_money['m_ex_money']) + numpy.array(u_medium['m_ex_money']) other_money['a_rebate'] = numpy.array( other_money['a_rebate']) + numpy.array(u_medium['a_rebate']) other_money['profit'] = numpy.array( other_money['profit']) + numpy.array(u_medium['profit']) total_except_money = numpy.array( total_except_money) + numpy.array(u_medium['sale_money']) total_a_rebate = numpy.array( total_a_rebate) + numpy.array(u_medium['a_rebate']) # 计算大于100W的媒体结束--- # 媒体订单结束 # 搜索直签订单开始 searchAd_medium_orders = searchAdMediumOrderExecutiveReport.query.filter( searchAdMediumOrderExecutiveReport.month_day >= start_date_month, searchAdMediumOrderExecutiveReport.month_day <= end_date_month) searchAd_medium_orders = [_search_order_to_dict( k) for k in searchAd_medium_orders] searchAd_medium_orders = [k for k in searchAd_medium_orders if k[ 'contract_status'] in [2, 4, 5, 10, 19, 20] and k['status'] == 1] searchAD_money = _get_medium_moneys( searchAd_medium_orders, pre_monthes, 0, 's_medium_order', year) # 搜索直签订单结束 # 搜索返点订单开始 searchAd_rebate_orders = searchAdRebateOrderExecutiveReport.query.filter( searchAdMediumOrderExecutiveReport.month_day >= start_date_month, searchAdMediumOrderExecutiveReport.month_day <= end_date_month) searchAd_rebate_orders = [_search_rebate_order_to_dict(k) for k in searchAd_rebate_orders if k.status == 1] searchAd_rebate_orders = [k for k in searchAd_rebate_orders if k[ 'contract_status'] in [2, 4, 5, 10, 19, 20]] rebate_order_money = _get_medium_moneys( searchAd_rebate_orders, pre_monthes, 0, '', year) # 搜索返点订单结束 # 搜索业务毛利+返点收入 searchAD_money['profit'] = numpy.array( searchAD_money['profit']) + numpy.array(rebate_order_money['sale_money']) # 豆瓣收入、服务费、返点、毛利为直签豆瓣+优力和无线总和 douban_money['sale_money'] = numpy.array( douban_money['sale_money']) + numpy.array(ass_douban_money['money2']) if year == 2016: douban_money['money2'] = numpy.array( douban_money['money2']) + numpy.array([k * 0.18 for k in ass_douban_money['money2']]) douban_money['a_rebate'] = [0 for k in range(48)] douban_money['profit'] = numpy.array( douban_money['money2']) - numpy.array(douban_money['a_rebate']) elif year == 2014: douban_money['money2'] = numpy.array( douban_money['money2']) + numpy.array([k * 0.426 for k in ass_douban_money['money2']]) douban_money['a_rebate'] = numpy.array( douban_money['a_rebate']) + numpy.array(ass_douban_money['a_rebate']) douban_money['profit'] = numpy.array( douban_money['money2']) - numpy.array(douban_money['a_rebate']) else: douban_money['money2'] = numpy.array( douban_money['money2']) + numpy.array([k * 0.4 for k in ass_douban_money['money2']]) douban_money['a_rebate'] = numpy.array( douban_money['a_rebate']) + numpy.array(ass_douban_money['a_rebate']) douban_money['profit'] = numpy.array( douban_money['money2']) - numpy.array(douban_money['a_rebate']) # 计算豆瓣收入、服务费、返点、毛利为直签豆瓣+优力和无线总和 if g.user.is_aduit() and str(year) == '2014': total = numpy.array(momo_money['sale_money']) +\ numpy.array(zhihu_money['sale_money']) +\ numpy.array(xiachufang_money['sale_money']) +\ numpy.array(xueqiu_money['sale_money']) +\ numpy.array(huxiu_money['sale_money']) +\ numpy.array(kecheng_money['sale_money']) +\ numpy.array(midi_money['sale_money']) +\ numpy.array(weipiao_money['sale_money']) +\ numpy.array(one_money['sale_money']) +\ numpy.array(total_except_money) +\ numpy.array(searchAD_money['sale_money']) +\ numpy.array(rebate_order_money['sale_money']) else: total = numpy.array(douban_money['profit']) +\ numpy.array(momo_money['sale_money']) +\ numpy.array(zhihu_money['sale_money']) +\ numpy.array(xiachufang_money['sale_money']) +\ numpy.array(xueqiu_money['sale_money']) +\ numpy.array(huxiu_money['sale_money']) +\ numpy.array(kecheng_money['sale_money']) +\ numpy.array(midi_money['sale_money']) +\ numpy.array(weipiao_money['sale_money']) +\ numpy.array(one_money['sale_money']) +\ numpy.array(total_except_money) +\ numpy.array(searchAD_money['sale_money']) +\ numpy.array(rebate_order_money['sale_money']) if request.values.get('action', '') == 'download': response = write_medium_money_excel(pre_monthes=pre_monthes, douban_money=douban_money, youli_money=youli_money, wuxian_money=wuxian_money, momo_money=momo_money, zhihu_money=zhihu_money, xiachufang_money=xiachufang_money, xueqiu_money=xueqiu_money, huxiu_money=huxiu_money, kecheng_money=kecheng_money, weipiao_money=weipiao_money, one_money=one_money, midi_money=midi_money, other_money=other_money, searchAD_money=searchAD_money, rebate_order_money=rebate_order_money, total=total, up_money=up_money, year=str( year) ) return response return tpl('/data_query/super_leader/medium_money.html', pre_monthes=pre_monthes, douban_money=douban_money, youli_money=youli_money, wuxian_money=wuxian_money, momo_money=momo_money, zhihu_money=zhihu_money, xiachufang_money=xiachufang_money, xueqiu_money=xueqiu_money, huxiu_money=huxiu_money, kecheng_money=kecheng_money, weipiao_money=weipiao_money, one_money=one_money, midi_money=midi_money, other_money=other_money, total=total, searchAD_money=searchAD_money, rebate_order_money=rebate_order_money, year=str(year), up_money=up_money)
def _order_back_money_data(order, start_Q_month, end_Q_month, back_moneys): order_back_moneys = [k for k in back_moneys if k['order_id'] == order.id] order_back_moneys = sorted( order_back_moneys, key=operator.itemgetter('back_time'), reverse=True) # 获取合同每月的执行金额,用于计算回款属于哪个执行月 money = order.money start = order.client_start end = order.client_end if money: pre_money = float(money) / ((end - start).days + 1) else: pre_money = 0 pre_month_days = get_monthes_pre_days(datetime.datetime.strptime(start.strftime('%Y-%m-%d'), '%Y-%m-%d'), datetime.datetime.strptime(end.strftime('%Y-%m-%d'), '%Y-%m-%d')) total_back_money = money for k in range(len(pre_month_days)): # 累计执行额 month_money = pre_money * pre_month_days[k]['days'] for b in range(len(order_back_moneys)): back_money = order_back_moneys[b]['money'] if 'belong_time' not in order_back_moneys[b]: if month_money - back_money > 0.0: order_back_moneys[b][ 'belong_time'] = pre_month_days[k]['month'] month_money -= back_money total_back_money -= back_money elif month_money - back_money < 0.0: order_back_moneys[b]['money'] = month_money order_back_moneys[b][ 'belong_time'] = pre_month_days[k]['month'] total_back_money -= month_money # 当回款金额大于当月执行额是,要把回款进行拆分,归属到下个执行月 if b != len(order_back_moneys) - 1: db = {} db['type'] = order_back_moneys[b]['type'] db['money'] = back_money - month_money db['back_time'] = order_back_moneys[b]['back_time'] db['order'] = order_back_moneys[b]['order'] db['order_id'] = order_back_moneys[b]['order_id'] order_back_moneys.insert(b + 1, db) month_money = 0.0 continue else: db = {} db['type'] = order_back_moneys[b]['type'] db['money'] = back_money - month_money db['back_time'] = order_back_moneys[b]['back_time'] db['order'] = order_back_moneys[b]['order'] db['order_id'] = order_back_moneys[b]['order_id'] if k == len(pre_month_days) - 1: db['belong_time'] = pre_month_days[k]['month'] else: db['belong_time'] = pre_month_days[k + 1]['month'] order_back_moneys.insert(b + 1, db) month_money = 0.0 break else: order_back_moneys[b][ 'belong_time'] = pre_month_days[k]['month'] month_money = 0.0 total_back_money -= back_money order_back_moneys = [k for k in order_back_moneys if k[ 'back_time'] >= start_Q_month and k['back_time'] < end_Q_month] return order_back_moneys
else: d_order['is_c_douban'] = False except: d_order['is_c_douban'] = False d_order['status'] = order.status return d_order if __name__ == '__main__': year = 2015 start_date_month = datetime.datetime.strptime( str(year) + '-01' + '-01', '%Y-%m-%d') end_date_month = datetime.datetime.strptime( str(year) + '-12' + '-31', '%Y-%m-%d') pre_monthes = get_monthes_pre_days(start_date_month, end_date_month) # 获取关联豆瓣订单代理返点,用于快速计算关联豆瓣订单代理 try: agent_youli_rebate = AgentRebate.query.filter_by( year=start_date_month, agent_id=105).first().douban_rebate except: agent_youli_rebate = 0 try: agent_wuxian_rebate = AgentRebate.query.filter_by( year=start_date_month, agent_id=94).first().douban_rebate except: agent_wuxian_rebate = 0 try: agent_haohai_rebate = AgentRebate.query.filter_by( year=start_date_month, agent_id=228).first().douban_rebate
def douban_order_excle_data(): now_date = datetime.datetime.now() location = int(request.values.get('location', 0)) year = int(request.values.get('year', now_date.year)) now_year_start = datetime.datetime.strptime( str(year) + '-01-01', '%Y-%m-%d') now_year_end = datetime.datetime.strptime(str(year) + '-12-01', '%Y-%m-%d') last_year_start = datetime.datetime.strptime( str(year - 1) + '-01-01', '%Y-%m-%d') last_year_end = datetime.datetime.strptime( str(year - 1) + '-12-01', '%Y-%m-%d') before_last_year_start = datetime.datetime.strptime( str(year - 2) + '-01-01', '%Y-%m-%d') before_last_year_end = datetime.datetime.strptime( str(year - 2) + '-12-01', '%Y-%m-%d') douban_orders = DoubanOrderExecutiveReport.query.filter( DoubanOrderExecutiveReport.month_day >= before_last_year_start, DoubanOrderExecutiveReport.month_day <= now_year_end) medium_orders = MediumOrderExecutiveReport.query.filter( MediumOrderExecutiveReport.month_day >= before_last_year_start, MediumOrderExecutiveReport.month_day <= now_year_end) douban_date = [_format_order(k, 'douban') for k in douban_orders if k.status == 1] medium_orders = [_format_order(k) for k in medium_orders if k.status == 1] douban_date += [{'month_day': k['month_day'], 'money':k['money'], 'locations':k['locations']} for k in medium_orders if k['medium_id'] in [3, 8]] now_monthes = get_monthes_pre_days(now_year_start, now_year_end) last_monthes = get_monthes_pre_days(last_year_start, last_year_end) before_monthes = get_monthes_pre_days( before_last_year_start, before_last_year_end) now_monthes_data = {} for k in now_monthes: now_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 last_monthes_data = {} for k in last_monthes: last_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 before_monthes_data = {} for k in before_monthes: before_monthes_data[int(k['month'].strftime('%s')) * 1000] = 0.0 for order in douban_date: if int(order['month_day'].strftime('%s')) * 1000 in now_monthes_data: now_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) if int(order['month_day'].strftime('%s')) * 1000 in last_monthes_data: last_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) if int(order['month_day'].strftime('%s')) * 1000 in before_monthes_data: before_monthes_data[ int(order['month_day'].strftime('%s')) * 1000] += _get_money_by_location(order, location) # 格式化数据,把近三年数据放在一年用于画图 now_monthes_data = sorted(now_monthes_data.iteritems(), key=lambda x: x[0]) last_monthes_data = sorted( last_monthes_data.iteritems(), key=lambda x: x[0]) before_monthes_data = sorted( before_monthes_data.iteritems(), key=lambda x: x[0]) headings = ['月份', str(before_last_year_start.year) + u'年度', str(last_year_start.year) + u'年度', str(now_year_start.year) + u'年度'] data = [] data.append([str(k + 1) + u'月' for k in range(len(now_monthes_data))]) data.append([v for k, v in before_monthes_data]) data.append([v for k, v in last_monthes_data]) data.append([v for k, v in now_monthes_data]) return {'data': data, 'title': u'直签豆瓣订单(含:优力、无线)执行额分析', 'headings': headings}