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}
