def _belong_time_by_back_money(money, start, end, back_money_obj):
# 获取合同执行额
report_money = _order_executive_reports(money, start, end)
now_Q_back_moneys = back_money_obj['now_Q_back_moneys']
t_report_money = 0
report_times = []
# 根据回款向前归并，比如合同执行时间跨月，回款要一次评分在三个月
for k in range(len(report_money)):
t_report_money += report_money[k]['money']
if now_Q_back_moneys <= 0:
break
if report_money[k]['money'] < now_Q_back_moneys:
report_times.append(
(report_money[k]['month_day'], report_money[k]['money']))
else:
report_times.append(
(report_money[k]['month_day'], now_Q_back_moneys))
now_Q_back_moneys -= report_money[k]['money']
# 确定回款属于哪个执行月
if report_times:
if len(report_times) > 1:
start = report_times[0][0].strftime(
'%Y') + '.' + check_month_get_Q(report_times[0][0].strftime('%m'))
end = report_times[-1][0].strftime('%Y') + '.' + check_month_get_Q(
report_times[-1][0].strftime('%m'))
if start == end:
return {'back_moneys': report_times, 'belong_time': end}
return {'back_moneys': report_times, 'belong_time': start + u' 至 ' + end}
start = report_times[0][0].strftime(
'%Y') + '.' + check_month_get_Q(report_times[0][0].strftime('%m'))
return {'back_moneys': report_times, 'belong_time': start}
return {'back_moneys': [], 'belong_time': u'无'}
def _belong_time_by_back_money(money, start, end, back_money_obj):
# 获取合同执行额
report_money = _order_executive_reports(money, start, end)
now_Q_back_moneys = back_money_obj['now_Q_back_moneys']
t_report_money = 0
report_times = []
for k in range(len(report_money)):
t_report_money += report_money[k]['money']
if now_Q_back_moneys <= 0:
break
if report_money[k]['money'] < now_Q_back_moneys:
report_times.append(
(report_money[k]['month_day'], report_money[k]['money']))
else:
report_times.append(
(report_money[k]['month_day'], now_Q_back_moneys))
now_Q_back_moneys -= report_money[k]['money']
if report_times:
if len(report_times) > 1:
start = report_times[0][0].strftime(
'%Y') + '.' + check_month_get_Q(
report_times[0][0].strftime('%m'))
end = report_times[-1][0].strftime('%Y') + '.' + check_month_get_Q(
report_times[-1][0].strftime('%m'))
if start == end:
return {'back_moneys': report_times, 'belong_time': end}
return {
'back_moneys': report_times,
'belong_time': start + u' 至 ' + end
}
start = report_times[0][0].strftime('%Y') + '.' + check_month_get_Q(
report_times[0][0].strftime('%m'))
return {'back_moneys': report_times, 'belong_time': start}
return {'back_moneys': [], 'belong_time': u'无'}
def get_completion(self, belong_time):
completion_obj = {}
back_moneys = belong_time['back_moneys']
for k in back_moneys:
Q = check_month_get_Q(k[0].strftime('%m'))
completion_obj[str(k[0].year) + Q] = self.completion(k[0])
return completion_obj
def completion(self, date):
year = str(date.year)
Q = check_month_get_Q(date.strftime('%m'))
completion = self.completion_user.filter_by(time=year + Q).first()
if completion:
return completion.rate / 100
return 0
def get_performance(self, belong_time):
performance_obj = {}
back_moneys = belong_time['back_moneys']
for k in back_moneys:
Q = check_month_get_Q(k[0].strftime('%m'))
performance_obj[str(k[0].year) + Q] = self.performance(
k[0].year, Q)
return performance_obj
def index():
now_year = request.values.get('year', '')
now_Q = request.values.get('Q', '')
if not now_year and not now_Q:
now_date = datetime.date.today()
now_year = now_date.strftime('%Y')
now_month = now_date.strftime('%m')
now_Q = check_month_get_Q(now_month)
Q_monthes = check_Q_get_monthes(now_Q)
start_month_day = datetime.datetime.strptime(
now_year + '-' + str(Q_monthes[0]), '%Y-%m')
end_month_day = datetime.datetime.strptime(
now_year + '-' + str(Q_monthes[-1]), '%Y-%m')
outsources = OutSourceExecutiveReport.all()
if now_Q == '00':
outsources = [k for k in outsources if int(
k.month_day.year) == int(now_year)]
else:
outsources = [k for k in outsources if k.month_day >=
start_month_day and k.month_day <= end_month_day]
# 踢掉删除的合同
outsources = [outsource_to_dict(k) for k in outsources]
outsources = [k for k in outsources if k['order_status'] == 1]
# 所有外包分类
types = [1, 2, 3, 4, 5, 6, 7, 8, 9]
monthes_data = {}
for k in types:
monthes_data[str(k)] = []
monthes_data['t_locataion'] = []
monthes_data['t_month'] = []
for k in Q_monthes:
month_day = datetime.datetime.strptime(
now_year + '-' + str(k), '%Y-%m')
t_huabei_num = 0
t_huadong_num = 0
t_huanan_num = 0
for i in types:
num_data = {}
num_data['huabei'] = sum([j['l_pre_pay_num'] for j in outsources
if j['month_day'] == month_day and j['type'] == i and 1 in j['locations']])
num_data['huadong'] = sum([j['l_pre_pay_num'] for j in outsources
if j['month_day'] == month_day and j['type'] == i and 2 in j['locations']])
num_data['huanan'] = sum([j['l_pre_pay_num'] for j in outsources
if j['month_day'] == month_day and j['type'] == i and 3 in j['locations']])
t_huabei_num += num_data['huabei']
t_huadong_num += num_data['huadong']
t_huanan_num += num_data['huanan']
monthes_data[str(i)].append(num_data)
monthes_data['t_locataion'].append(
{'huabei': t_huabei_num, 'huadong': t_huadong_num, 'huanan': t_huanan_num})
monthes_data['t_month'].append(
t_huabei_num + t_huadong_num + t_huanan_num)
if request.values.get('action', '') == 'download':
return write_outsource_excel(Q_monthes, monthes_data)
return tpl('/data_query/outsource/index.html', Q=now_Q, now_year=now_year,
Q_monthes=Q_monthes, monthes_data=monthes_data)
def saler():
if not (g.user.is_super_leader() or g.user.is_finance()):
abort(403)
now_year = request.values.get('year', '')
now_Q = request.values.get('Q', '')
location_id = int(request.values.get('location_id', 0))
if not now_year and not now_Q:
now_date = datetime.date.today()
now_year = now_date.strftime('%Y')
now_month = now_date.strftime('%m')
now_Q = check_month_get_Q(now_month)
Q_monthes = check_Q_get_monthes(now_Q)
start_Q_month = datetime.datetime(int(now_year), int(Q_monthes[0]), 1)
# 获取下季度的第一天为本季度的结束时间
d = cal.monthrange(int(now_year), int(Q_monthes[-1]))
end_Q_month = datetime.datetime(
int(now_year), int(Q_monthes[-1]), d[1]) + datetime.timedelta(days=1)
# 获取该季度及之前所有回款及返点发票
client_back_moneys = [_dict_back_money(
k, 'money') for k in BackMoney.query.filter(BackMoney.back_time < end_Q_month)]
client_back_moneys += [_dict_back_money(k, 'invoice') for k in BackInvoiceRebate.query.filter(
BackInvoiceRebate.back_time < end_Q_month)]
douban_back_moneys = [_dict_back_money(k, 'money') for k in DoubanBackMoney.query.filter(
DoubanBackMoney.back_time < end_Q_month)]
douban_back_moneys += [_dict_back_money(k, 'invoice') for k in DoubanBackInvoiceRebate.query.filter(
DoubanBackInvoiceRebate.back_time < end_Q_month)]
# 获取当前季度所有回款
now_Q_client_back_moneys = [
k for k in client_back_moneys if k['back_time'] >= start_Q_month]
now_Q_douban_back_moneys = [
k for k in douban_back_moneys if k['back_time'] >= start_Q_month]
# 获取当季度回款的所有合同
client_orders = list(set([k['order'] for k in now_Q_client_back_moneys]))
douban_orders = list(set([k['order'] for k in now_Q_douban_back_moneys]))
orders = [_order_to_dict(k, start_Q_month, end_Q_month, client_back_moneys, now_Q_client_back_moneys)
for k in client_orders if k.contract_status not in [7, 8, 9] and k.status == 1 and k.contract]
orders += [_order_to_dict(k, start_Q_month, end_Q_month, douban_back_moneys, now_Q_douban_back_moneys)
for k in douban_orders if k.contract_status not in [7, 8, 9] and k.status == 1 and k.contract]
if location_id:
orders = [k for k in orders if location_id in k['locations']]
orders = sorted(orders, key=operator.itemgetter('client_start'), reverse=False)
if request.values.get('action') == 'download':
return write_report_excel(Q=now_Q, now_year=now_year, orders=orders)
return tpl('/account/commission/saler.html', Q=now_Q, now_year=now_year,
Q_monthes=Q_monthes, location_id=location_id, orders=orders)
def index():
now_year = request.values.get('year', '')
now_Q = request.values.get('Q', '')
if not now_year and not now_Q:
now_date = datetime.date.today()
now_year = now_date.strftime('%Y')
now_month = now_date.strftime('%m')
now_Q = check_month_get_Q(now_month)
Q_monthes = check_Q_get_monthes(now_Q)
start_month_day = datetime.datetime.strptime(
now_year + '-' + str(Q_monthes[0]), '%Y-%m')
end_month_day = datetime.datetime.strptime(
now_year + '-' + str(Q_monthes[-1]), '%Y-%m')
outsources = OutSourceExecutiveReport.all()
if now_Q == '00':
outsources = [
k for k in outsources if int(k.month_day.year) == int(now_year)
]
else:
outsources = [
k for k in outsources
if k.month_day >= start_month_day and k.month_day <= end_month_day
]
# 踢掉删除的合同
outsources = [outsource_to_dict(k) for k in outsources]
outsources = [k for k in outsources if k['order_status'] == 1]
# 所有外包分类
types = [1, 2, 3, 4, 5, 6, 7, 8, 9]
monthes_data = {}
for k in types:
monthes_data[str(k)] = []
monthes_data['t_locataion'] = []
monthes_data['t_month'] = []
for k in Q_monthes:
month_day = datetime.datetime.strptime(now_year + '-' + str(k),
'%Y-%m')
t_huabei_num = 0
t_huadong_num = 0
t_huanan_num = 0
for i in types:
num_data = {}
num_data['huabei'] = sum([
j['l_pre_pay_num'] for j in outsources
if j['month_day'] == month_day and j['type'] == i
and 1 in j['locations']
])
num_data['huadong'] = sum([
j['l_pre_pay_num'] for j in outsources
if j['month_day'] == month_day and j['type'] == i
and 2 in j['locations']
])
num_data['huanan'] = sum([
j['l_pre_pay_num'] for j in outsources
if j['month_day'] == month_day and j['type'] == i
and 3 in j['locations']
])
t_huabei_num += num_data['huabei']
t_huadong_num += num_data['huadong']
t_huanan_num += num_data['huanan']
monthes_data[str(i)].append(num_data)
monthes_data['t_locataion'].append({
'huabei': t_huabei_num,
'huadong': t_huadong_num,
'huanan': t_huanan_num
})
monthes_data['t_month'].append(t_huabei_num + t_huadong_num +
t_huanan_num)
if request.values.get('action', '') == 'download':
return write_outsource_excel(Q_monthes, monthes_data)
return tpl('/data_query/outsource/index.html',
Q=now_Q,
now_year=now_year,
Q_monthes=Q_monthes,
monthes_data=monthes_data)
def saler():
if not (g.user.is_super_leader() or g.user.is_finance()):
abort(403)
now_year = request.values.get('year', '')
now_Q = request.values.get('Q', '')
location_id = int(request.values.get('location_id', 0))
if not now_year and not now_Q:
now_date = datetime.date.today()
now_year = now_date.strftime('%Y')
now_month = now_date.strftime('%m')
now_Q = check_month_get_Q(now_month)
Q_monthes = check_Q_get_monthes(now_Q)
start_Q_month = datetime.datetime(int(now_year), int(Q_monthes[0]), 1)
# 获取下季度的第一天为本季度的结束时间
d = cal.monthrange(int(now_year), int(Q_monthes[-1]))
end_Q_month = datetime.datetime(int(now_year), int(Q_monthes[-1]),
d[1]) + datetime.timedelta(days=1)
# 获取该季度及之前所有回款及返点发票
client_back_moneys = [
_dict_back_money(k, 'money')
for k in BackMoney.query.filter(BackMoney.back_time < end_Q_month)
]
client_back_moneys += [
_dict_back_money(k, 'invoice') for k in BackInvoiceRebate.query.filter(
BackInvoiceRebate.back_time < end_Q_month)
]
douban_back_moneys = [
_dict_back_money(k, 'money') for k in DoubanBackMoney.query.filter(
DoubanBackMoney.back_time < end_Q_month)
]
douban_back_moneys += [
_dict_back_money(k, 'invoice')
for k in DoubanBackInvoiceRebate.query.filter(
DoubanBackInvoiceRebate.back_time < end_Q_month)
]
# 获取当前季度所有回款
now_Q_client_back_moneys = [
k for k in client_back_moneys if k['back_time'] >= start_Q_month
]
now_Q_douban_back_moneys = [
k for k in douban_back_moneys if k['back_time'] >= start_Q_month
]
# 获取当季度回款的所有合同
client_orders = list(set([k['order'] for k in now_Q_client_back_moneys]))
douban_orders = list(set([k['order'] for k in now_Q_douban_back_moneys]))
orders = [
_order_to_dict(k, start_Q_month, end_Q_month, client_back_moneys,
now_Q_client_back_moneys) for k in client_orders
if k.contract_status not in [7, 8, 9] and k.status == 1 and k.contract
]
orders += [
_order_to_dict(k, start_Q_month, end_Q_month, douban_back_moneys,
now_Q_douban_back_moneys) for k in douban_orders
if k.contract_status not in [7, 8, 9] and k.status == 1 and k.contract
]
if location_id:
orders = [k for k in orders if location_id in k['locations']]
orders = sorted(orders,
key=operator.itemgetter('client_start'),
reverse=False)
if request.values.get('action') == 'download':
return write_report_excel(Q=now_Q, now_year=now_year, orders=orders)
return tpl('/account/commission/saler.html',
Q=now_Q,
now_year=now_year,
Q_monthes=Q_monthes,
location_id=location_id,
orders=orders)
def index():
now_year = request.values.get('year', '')
now_Q = request.values.get('Q', '')
location_id = int(request.values.get('location_id', 0))
if not now_year and not now_Q:
now_date = datetime.date.today()
now_year = now_date.strftime('%Y')
now_month = now_date.strftime('%m')
now_Q = check_month_get_Q(now_month)
Q_monthes = check_Q_get_monthes(now_Q)
start_Q_month = datetime.datetime(int(now_year), int(Q_monthes[0]), 1)
# 获取下季度的第一天为本季度的结束时间
d = cal.monthrange(int(now_year), int(Q_monthes[-1]))
end_Q_month = datetime.datetime(int(now_year), int(Q_monthes[-1]),
d[1]) + datetime.timedelta(days=1)
# 获取该季度及之前所有回款
client_back_moneys = [
_dict_back_money(k)
for k in BackMoney.query.filter(BackMoney.back_time < end_Q_month)
]
douban_back_moneys = [
_dict_back_money(k) for k in DoubanBackMoney.query.filter(
BackMoney.back_time < end_Q_month)
]
# ####################获取相关订单执行额，用于计算销售季度完成率##################
client_continue_time = []
for k in client_back_moneys:
client_continue_time += [k['order_start'], k['order_end']]
douban_countinue_time = []
for k in douban_back_moneys:
douban_countinue_time += [k['order_start'], k['order_end']]
client_continue_time.sort()
douban_countinue_time.sort()
if client_continue_time:
client_continue_start = client_continue_time[0].replace(day=1)
client_continue_end = client_continue_time[-1].replace(day=1)
client_report = list(
ClientOrderExecutiveReport.query.filter(
ClientOrderExecutiveReport.month_day >= client_continue_start,
ClientOrderExecutiveReport.month_day <= client_continue_end))
else:
client_report = []
if douban_countinue_time:
douban_countinue_start = douban_countinue_time[0].replace(day=1)
douban_countinue_end = douban_countinue_time[-1].replace(day=1)
douban_report = list(
DoubanOrderExecutiveReport.query.filter(
DoubanOrderExecutiveReport.month_day >= douban_countinue_start,
DoubanOrderExecutiveReport.month_day <= douban_countinue_end))
else:
douban_report = []
# ####################获取相关订单执行额，用于计算销售季度完成率##################
# 获取当前季度所有回款
now_Q_client_back_moneys = [
k for k in client_back_moneys if k['back_time'] >= start_Q_month
]
now_Q_douban_back_moneys = [
k for k in douban_back_moneys if k['back_time'] >= start_Q_month
]
# 回去当季度回款的所有合同
client_orders = list(set([k['order'] for k in now_Q_client_back_moneys]))
douban_orders = list(set([k['order'] for k in now_Q_douban_back_moneys]))
orders = [
_order_to_dict(k, start_Q_month, client_back_moneys,
now_Q_client_back_moneys, client_report)
for k in client_orders if k.contract_status not in [7, 8, 81, 9]
and k.status == 1 and k.contract
]
orders += [
_order_to_dict(k, start_Q_month, douban_back_moneys,
now_Q_douban_back_moneys, douban_report)
for k in douban_orders if k.contract_status not in [7, 8, 81, 9]
and k.status == 1 and k.contract
]
if g.user.is_super_leader() or g.user.is_finance():
orders = orders
elif g.user.is_leader():
orders = [k for k in orders if g.user.location in k['locations']]
else:
orders = [k for k in orders if g.user.id in k['salers_ids']]
if location_id:
orders = [k for k in orders if location_id in k['locations']]
return tpl('/data_query/commission/index.html',
orders=orders,
Q=now_Q,
now_year=now_year,
Q_monthes=Q_monthes,
location_id=location_id)
def _order_to_dict(order, start_Q_month, back_moneys, now_Q_back_moneys,
t_report):
dict_order = {}
dict_order['client_name'] = order.client.name
dict_order['money'] = order.money
dict_order['agent_name'] = order.agent.name
dict_order['contract'] = order.contract
dict_order['campaign'] = order.campaign
dict_order['locations'] = order.locations
dict_order['contract_status'] = order.contract_status
dict_order['status'] = order.status
# 获取合同回款信息
dict_order['back_money_obj'] = _order_back_money_by_Q(
order.id, start_Q_month, back_moneys, now_Q_back_moneys)
# 根据回款信息确定合同所属时间
dict_order['belong_time'] = _belong_time_by_back_money(
order.money, order.client_start, order.client_end,
dict_order['back_money_obj'])
dict_order['last_rebate_agent_time'] = order.last_rebate_agent_time()
dict_order['last_rebate_agent_money'] = order.last_rebate_agent_money()
dict_order['direct_sales'] = []
for saler in order.direct_sales:
d_saler = {}
d_saler['id'] = saler.id
d_saler['name'] = saler.name
d_saler['location_cn'] = saler.location_cn
d_saler['performance'] = saler.get_performance(
dict_order['belong_time'])
d_saler['commission'] = saler.get_commission(dict_order['belong_time'])
d_saler['performance_rate'] = _get_performance_rate_by_user(
order.__tablename__, d_saler['performance'], saler, 'direct',
t_report)
# 判断销售是否有平分金额
if len(set(order.locations)) > 1:
l_count = len(set(order.locations))
else:
l_count = 1
count = len(order.direct_sales)
if saler.team.location == 3 and len(order.locations) > 1:
count = len(order.direct_sales)
elif saler.team.location == 3 and len(order.locations) == 1:
count = len(order.agent_sales + order.direct_sales)
d_saler['str_formula'] = ""
commission_money = 0
for b_money_obj in dict_order['belong_time']['back_moneys']:
b_money = float('%.2f' % (b_money_obj[1] / count / l_count))
day = b_money_obj[0]
Q = check_month_get_Q(day.strftime('%m'))
if str(day.year) + Q + 'rate' in d_saler['performance_rate']:
# 完成率
performance_rate = d_saler['performance_rate'][str(day.year) +
Q + 'rate'][0]
# 执行金额
zhixing_money = d_saler['performance_rate'][str(day.year) + Q +
'rate'][1]
# 销售业绩
performance_money = d_saler['performance_rate'][str(day.year) +
Q + 'rate'][2]
else:
performance_rate = 0
zhixing_money = 0
performance_money = 0
if day.strftime('%Y') in d_saler['commission']:
commission = d_saler['commission'][day.strftime('%Y')]
else:
commission = 0
c_money = float('%.2f' % (performance_rate * commission * b_money))
commission_money += c_money
# 计算公式
d_saler[
'str_formula'] += u"(%s / %s) * %s * %s = %s (%s月 提成信息)<br/>" % (
str('%.2f' % zhixing_money), str(performance_money),
str(commission), str(b_money), str(c_money),
day.strftime('%Y-%m'))
d_saler['commission_money'] = commission_money
dict_order['direct_sales'].append(d_saler)
dict_order['agent_sales'] = []
for saler in order.agent_sales:
d_saler = {}
d_saler['id'] = saler.id
d_saler['name'] = saler.name
d_saler['location_cn'] = saler.location_cn
d_saler['performance'] = saler.get_performance(
dict_order['belong_time'])
d_saler['commission'] = saler.get_commission(dict_order['belong_time'])
d_saler['performance_rate'] = _get_performance_rate_by_user(
order.__tablename__, d_saler['performance'], saler, 'agent',
t_report)
# 判断销售是否有平分金额
if len(set(order.locations)) > 1:
l_count = len(set(order.locations))
else:
l_count = 1
count = len(order.agent_sales)
if saler.team.location == 3 and len(order.locations) > 1:
count = len(order.agent_sales)
elif saler.team.location == 3 and len(order.locations) == 1:
count = len(order.agent_sales + order.direct_sales)
d_saler['str_formula'] = ""
commission_money = 0
for b_money_obj in dict_order['belong_time']['back_moneys']:
b_money = float('%.2f' % (b_money_obj[1] / count / l_count))
day = b_money_obj[0]
Q = check_month_get_Q(day.strftime('%m'))
if str(day.year) + Q + 'rate' in d_saler['performance_rate']:
# 完成率
performance_rate = d_saler['performance_rate'][str(day.year) +
Q + 'rate'][0]
# 执行金额
zhixing_money = d_saler['performance_rate'][str(day.year) + Q +
'rate'][1]
# 销售业绩
performance_money = d_saler['performance_rate'][str(day.year) +
Q + 'rate'][2]
else:
performance_rate = 0
zhixing_money = 0
performance_money = 0
if day.strftime('%Y') in d_saler['commission']:
commission = d_saler['commission'][day.strftime('%Y')]
else:
commission = 0
c_money = float('%.2f' % (performance_rate * commission * b_money))
commission_money += c_money
# 计算公式
d_saler[
'str_formula'] += u"(%s / %s) * %s * %s = %s (%s月 提成信息)<br/>" % (
str('%.2f' % zhixing_money), str(performance_money),
str(commission), str(b_money), str(c_money),
day.strftime('%Y-%m'))
d_saler['commission_money'] = commission_money
dict_order['agent_sales'].append(d_saler)
dict_order['salers_ids'] = [
k['id']
for k in (dict_order['direct_sales'] + dict_order['agent_sales'])
]
dict_order['client_start'] = order.client_start
dict_order['client_end'] = order.client_end
return dict_order