def get(self):
"""销售业绩"""
form = Form(self.request.arguments, list_schema)
op_id = form.operator_id.value
start = form.start_date.value
end = form.end_date.value
download = form.action.value if form.action.value else 'show'
if not form.validate():
return self.render('finance/profit.html',
form=form,
page=[],
name='输入正确参数查询',
total='',
amount='',
sale_amount='',
sum_commission='')
operator = self.db.get('select * from operator where id=%s', op_id)
if not operator:
return self.render('finance/profit.html',
form=form,
page=[],
name='销售人员不存在',
total='',
amount='',
sale_amount='',
sum_commission='')
sql = ''
sum_sql = ''
kind = ''
kind0 = ''
kind1 = ''
sale_amount = 0
params = [op_id]
# 统计消费(验证)明细(不包含刷单)
if form.kind.value == 1:
sql = 'select goods_name, used_at as time, sales_price, purchase_price, payment as amount, ' \
'(payment-purchase_price) as profit, commission, order_no, order_id, distr_shop_id from item where sales_id=%s ' \
'and used_at is not null and cheat_at is null and status=2 '
sum_sql = 'select sum(payment) amount, sum(commission) commission, sum(payment-purchase_price) as total ' \
'from item where sales_id=%s and used_at is not null and cheat_at is null and status=2 '
sale_sql = 'select sum(payment) amount from item where sales_id=%s and created_at is not null '
kind = '-消费利润汇总：'
kind0 = '消费总金额：'
kind1 = '渠道佣金：'
if start:
sql += 'and cast(used_at as Date)>=%s '
sum_sql += 'and cast(used_at as Date)>=%s '
sale_sql += 'and cast(created_at as Date)>=%s '
params.append(start)
if end:
sql += 'and cast(used_at as Date)<=%s '
sum_sql += 'and cast(used_at as Date)<=%s '
sale_sql += 'and cast(created_at as Date)<=%s '
params.append(end)
sql += 'order by used_at desc'
sale_amount = self.db.get(sale_sql, *params).amount
sale_amount = (',销售总金额：' + str(sale_amount)) if sale_amount else ''
# 统计"已消费"退款明细
if form.kind.value == 2:
sql = 'select goods_name, refund_at as time, sales_price, purchase_price, refund_value as amount, ' \
'(purchase_price-refund_value) as profit, commission, order_no, order_id, distr_shop_id from item where sales_id=%s ' \
'and refund_at is not null and used_at is not null '
sum_sql = 'select sum(refund_value) amount,sum(commission) commission,sum(purchase_price-refund_value) as total ' \
'from item where sales_id=%s and refund_at is not null and used_at is not null '
kind = '-已消费退款负利润汇总：'
kind0 = '退款总金额：'
kind1 = '渠道佣金：'
if start:
sql += 'and cast(refund_at as Date)>=%s '
sum_sql += 'and cast(refund_at as Date)>=%s '
params.append(start)
if end:
sql += 'and cast(refund_at as Date)<=%s '
sum_sql += 'and cast(refund_at as Date)<=%s '
params.append(end)
sql += 'order by refund_at desc'
# 统计刷单明细
if form.kind.value == 3:
sql = 'select goods_name, cheat_at as time, sales_price, purchase_price, sales_price as amount, ' \
'(cheat_value-purchase_price) as profit, commission, order_no, order_id, distr_shop_id from item where sales_id=%s ' \
'and cheat_at is not null '
sum_sql = 'select sum(sales_price) amount,sum(commission) commission,sum(cheat_value-purchase_price) total ' \
'from item where sales_id=%s and cheat_at is not null '
kind = '-刷单利润(手续费)汇总：'
kind0 = '刷单总金额：'
kind1 = '刷单佣金：'
if start:
sql += 'and cast(cheat_at as Date)>=%s '
sum_sql += 'and cast(cheat_at as Date)>=%s '
params.append(start)
if end:
sql += 'and cast(cheat_at as Date)<=%s '
sum_sql += 'and cast(cheat_at as Date)<=%s '
params.append(end)
sql += 'order by cheat_at desc'
if download == 'download':
distr_shops = self.db.query(
'select id, name from distributor_shop')
distr_shops = PropDict([(i.id, i.name) for i in distr_shops])
page = self.db.query(sql, *params)
title = [
u'交易时间', u'订单号', u'渠道', u'商品名称', u'售价', u'进价', u'实际金额', u'利润',
u'佣金'
]
self.set_header('Content-type', 'application/excel')
self.set_header(
'Content-Disposition', u'attachment; filename=' + u'销售业绩-' +
operator.name.decode('utf-8') + u'.xls')
order_excel = Workbook(encoding='utf-8')
write_order_excel = order_excel.add_sheet('0')
for index, content in enumerate(title):
write_order_excel.write(0, index, content)
range_list = [
'time', 'order_no', 'distr_shop_id', 'goods_name',
'sales_price', 'purchase_price', 'amount', 'profit',
'commission'
]
for i, item in enumerate(page):
for j, content in enumerate(range_list):
v = item.get(content, '')
v = v if v else 0
if content == 'time':
v = str(v)
if content == 'distr_shop_id':
v = distr_shops.get(item.get(content))
write_order_excel.write(i + 1, j, v)
stream = StringIO.StringIO()
order_excel.save(stream)
self.write(stream.getvalue())
else:
page = Paginator(self, sql, params)
summary = self.db.get(sum_sql, *params)
sum_total = summary.total if summary.total else '0'
sum_commission = summary.commission if summary.commission else '0'
self.render("finance/profit.html",
form=form,
page=page,
name=operator.name,
total=kind + str(sum_total),
amount=kind0 + str(summary.amount),
sale_amount=sale_amount,
sum_commission=kind1 + str(sum_commission))
def get(self):
"""销售业绩"""
form = Form(self.request.arguments, list_schema)
op_id = form.operator_id.value
start = form.start_date.value
end = form.end_date.value
download = form.action.value if form.action.value else 'show'
if not form.validate():
return self.render('finance/profit.html', form=form, page=[], name='输入正确参数查询', total='', amount='',
sale_amount='', sum_commission='')
operator = self.db.get('select * from operator where id=%s', op_id)
if not operator:
return self.render('finance/profit.html', form=form, page=[], name='销售人员不存在', total='', amount='',
sale_amount='', sum_commission='')
sql = ''
sum_sql = ''
kind = ''
kind0 = ''
kind1 = ''
sale_amount = 0
params = [op_id]
# 统计消费(验证)明细(不包含刷单)
if form.kind.value == 1:
sql = 'select goods_name, used_at as time, sales_price, purchase_price, payment as amount, ' \
'(payment-purchase_price) as profit, commission, order_no, order_id, distr_shop_id from item where sales_id=%s ' \
'and used_at is not null and cheat_at is null and status=2 '
sum_sql = 'select sum(payment) amount, sum(commission) commission, sum(payment-purchase_price) as total ' \
'from item where sales_id=%s and used_at is not null and cheat_at is null and status=2 '
sale_sql = 'select sum(payment) amount from item where sales_id=%s and created_at is not null '
kind = '-消费利润汇总：'
kind0 = '消费总金额：'
kind1 = '渠道佣金：'
if start:
sql += 'and cast(used_at as Date)>=%s '
sum_sql += 'and cast(used_at as Date)>=%s '
sale_sql += 'and cast(created_at as Date)>=%s '
params.append(start)
if end:
sql += 'and cast(used_at as Date)<=%s '
sum_sql += 'and cast(used_at as Date)<=%s '
sale_sql += 'and cast(created_at as Date)<=%s '
params.append(end)
sql += 'order by used_at desc'
sale_amount = self.db.get(sale_sql, *params).amount
sale_amount = (',销售总金额：' + str(sale_amount)) if sale_amount else ''
# 统计"已消费"退款明细
if form.kind.value == 2:
sql = 'select goods_name, refund_at as time, sales_price, purchase_price, refund_value as amount, ' \
'(purchase_price-refund_value) as profit, commission, order_no, order_id, distr_shop_id from item where sales_id=%s ' \
'and refund_at is not null and used_at is not null '
sum_sql = 'select sum(refund_value) amount,sum(commission) commission,sum(purchase_price-refund_value) as total ' \
'from item where sales_id=%s and refund_at is not null and used_at is not null '
kind = '-已消费退款负利润汇总：'
kind0 = '退款总金额：'
kind1 = '渠道佣金：'
if start:
sql += 'and cast(refund_at as Date)>=%s '
sum_sql += 'and cast(refund_at as Date)>=%s '
params.append(start)
if end:
sql += 'and cast(refund_at as Date)<=%s '
sum_sql += 'and cast(refund_at as Date)<=%s '
params.append(end)
sql += 'order by refund_at desc'
# 统计刷单明细
if form.kind.value == 3:
sql = 'select goods_name, cheat_at as time, sales_price, purchase_price, sales_price as amount, ' \
'(cheat_value-purchase_price) as profit, commission, order_no, order_id, distr_shop_id from item where sales_id=%s ' \
'and cheat_at is not null '
sum_sql = 'select sum(sales_price) amount,sum(commission) commission,sum(cheat_value-purchase_price) total ' \
'from item where sales_id=%s and cheat_at is not null '
kind = '-刷单利润(手续费)汇总：'
kind0 = '刷单总金额：'
kind1 = '刷单佣金：'
if start:
sql += 'and cast(cheat_at as Date)>=%s '
sum_sql += 'and cast(cheat_at as Date)>=%s '
params.append(start)
if end:
sql += 'and cast(cheat_at as Date)<=%s '
sum_sql += 'and cast(cheat_at as Date)<=%s '
params.append(end)
sql += 'order by cheat_at desc'
if download == 'download':
distr_shops = self.db.query('select id, name from distributor_shop')
distr_shops = PropDict([(i.id, i.name) for i in distr_shops])
page = self.db.query(sql, *params)
title = [u'交易时间', u'订单号', u'渠道', u'商品名称', u'售价', u'进价', u'实际金额', u'利润', u'佣金']
self.set_header('Content-type', 'application/excel')
self.set_header('Content-Disposition', u'attachment; filename=' +
u'销售业绩-'+operator.name.decode('utf-8')+u'.xls')
order_excel = Workbook(encoding='utf-8')
write_order_excel = order_excel.add_sheet('0')
for index, content in enumerate(title):
write_order_excel.write(0, index, content)
range_list = ['time', 'order_no', 'distr_shop_id', 'goods_name', 'sales_price', 'purchase_price', 'amount',
'profit', 'commission']
for i, item in enumerate(page):
for j, content in enumerate(range_list):
v = item.get(content, '')
v = v if v else 0
if content == 'time':
v = str(v)
if content == 'distr_shop_id':
v = distr_shops.get(item.get(content))
write_order_excel.write(i + 1, j, v)
stream = StringIO.StringIO()
order_excel.save(stream)
self.write(stream.getvalue())
else:
page = Paginator(self, sql, params)
summary = self.db.get(sum_sql, *params)
sum_total = summary.total if summary.total else '0'
sum_commission = summary.commission if summary.commission else '0'
self.render("finance/profit.html", form=form, page=page, name=operator.name, total=kind + str(sum_total),
amount=kind0 + str(summary.amount), sale_amount=sale_amount,
sum_commission=kind1 + str(sum_commission))
def post(self):
form = Form(self.request.arguments, list_schema)
start = form.start_date.value
end = form.end_date.value
if not start or not end:
self.render('finance/statement.html', form=form, error='请输入查询日期')
return
start_date = datetime.strptime(start, '%Y-%m-%d')
end_date = datetime.strptime(end, '%Y-%m-%d')
durations = (end_date - start_date).days
if durations > 60:
self.render('finance/statement.html',
form=form,
error='最大支持60天查询，建议缩小查询区间，提高数据生成速度')
return
result = []
goods_link_ids = []
# 包含首尾
for i in range(durations + 1):
attempts = 0
# 一个请求重试3次
while attempts < 3:
wb = Wuba('queryjiesuan')
d = (start_date + timedelta(days=i)).strftime('%Y-%m-%d')
request_params = {'jiesuantime': d}
response = yield wb(**request_params)
wb.parse_response(response.body)
if wb.is_ok():
if wb.message.data.jiesuanDetails:
for item in wb.message.data.jiesuanDetails:
item.update({'jiesuandate': d})
result.append(item)
goods_link_ids.append(str(item.get('groupid3')))
break
else:
attempts += 1
if attempts == 3:
result.append({
'jiesuandate': d,
'orderid58': '当日查询结果异常，建议单独重新查询',
'groupprice': '',
'commission': '',
'jiesuanmoney': '',
'usetime': ''
})
if result and goods_link_ids:
# 下载
title = [
u'结算日期', u'外部订单号', u'验证/发货日期', u'商品名称', u'业务发生金额', u'佣金',
u'实际结算金额'
]
self.set_header('Content-type', 'application/excel')
self.set_header(
'Content-Disposition', u'attachment; filename=' + u'58对账单' +
start + u'到' + end + u'.xls')
wuba_excel = Workbook(encoding='utf-8')
write_wuba_excel = wuba_excel.add_sheet('0')
for index, content in enumerate(title):
write_wuba_excel.write(0, index, content)
range_list = [
'jiesuandate', 'orderid58', 'usetime', 'goods_name',
'groupprice', 'commission', 'jiesuanmoney'
]
goods_names = self.db.query(
'select goods_link_id glid, short_name name '
'from goods g, goods_distributor_shop gds '
'where g.id=gds.goods_id and gds.goods_link_id in '
'(' + ','.join(set(goods_link_ids)) + ')')
name_dict = PropDict([(i.glid, i.name) for i in goods_names])
for i, item in enumerate(result):
for j, content in enumerate(range_list):
v = item.get(content, '')
v = v if v else ''
if content == 'usetime':
v = str(v)
elif content == 'commission':
if not v:
v = 0
elif content == 'orderid58':
v = str(v)
elif content == 'goods_name':
v = name_dict.get(item.get('groupid3'))
write_wuba_excel.write(i + 1, j, v)
stream = StringIO.StringIO()
wuba_excel.save(stream)
self.write(stream.getvalue())
else:
self.render('finance/statement.html', form=form, error='该期间没有结算数据')
def post(self):
self.set_header('Content-Type', 'application/json; charset=UTF-8')
coupon_list = [i.strip() for i in self.get_argument('coupons', '').split(',') if i.strip()]
if len(coupon_list) == 0:
self.write({'ok': False, 'msg': u'请输入券号'})
return
shop_id = self.get_argument('shop_id', 0)
#这一步是检查该操作员是否有权限验证券
all_shops = self.db.query('select ss.* from supplier_shop ss, supplier_user su where ss.supplier_id=%s and '
'ss.deleted=0 and su.id=%s and su.supplier_id=ss.supplier_id and '
' (su.shop_id=0 or ss.id=su.shop_id)',
self.current_user.supplier_id, self.current_user.id)
if int(shop_id) not in [i.id for i in all_shops]:
self.write({'ok': False, 'msg': u'对不起，您无权执行此操作'})
return
is_our_coupon = False
coupon_length = 0
messages = PropDict() # 用来存不同的手机号对应的券号
#检测长度是否相同、是否是我们的券
for coupon_sn in coupon_list:
coupon = self.db.get('select id, sn, mobile from item_coupon where sn=%s', coupon_sn)
if coupon:
is_our_coupon = True
if coupon_length == 0:
coupon_length = len(coupon_sn)
elif coupon_length != len(coupon_sn):
coupon_length = -1
if coupon_length == -1:
self.write({'ok': False, 'msg': u'券号长度必须一致'})
return
result_list = []
if is_our_coupon:
# 我们自己的券
for coupon_sn in coupon_list:
# 本地检测
check_result = local_check(self.db, coupon_sn, shop_id)
if not check_result.ok:
ok, msg = (False, check_result.msg)
else:
# 合作伙伴API验证
api_result = yield partner_api_verify(self.db, coupon_sn)
if not api_result.ok:
ok, msg = (False, api_result.msg)
else:
# 本地验证
verify_result = local_verify(self.db, coupon_sn, shop_id, self.current_user.name)
ok, msg = (verify_result.ok, verify_result.msg)
# 验证通过，记录需要发送确认短信的券
if ok:
if messages.get(str(check_result.coupon.mobile)):
# 手机号已存在，添加券号到对应手机号
messages.get(str(check_result.coupon.mobile)).append(str(coupon_sn))
else:
# 手机号不存在，新建K-V对
messages.update({str(check_result.coupon.mobile): [str(coupon_sn)]})
result_list.append({'coupon_sn': coupon_sn, 'ok': ok, 'msg': msg})
else:
mobile = self.get_argument('mobile', '')
# 检查手机号合法性
mobile_ptn = re.compile('^1[\d+]{10}$')
if not re.match(mobile_ptn, mobile):
# 不合法手机号，不记录
mobile = ''
# 尝试外部验证
results = yield partner_browser_verify(self.db, coupon_list, shop_id)
for result in results:
if result.ok:
goods_info = self.db.get('select g.* from goods g where id=%s', result.goods_id)
verify_infos = {'goods_id': result.goods_id, 'shop_id': shop_id}
#创建分销订单
distributor_order_id = self.db.execute(
'insert into distributor_order(order_no, message, distributor_shop_id, created_at) '
'values (%s, %s, %s, NOW())', result.coupon_sn, json_dumps(verify_infos), result.distributor_shop_id)
order_id, order_no = new_distributor_order(self.db, result.distributor_shop_id,
goods_info.sales_price, goods_info.sales_price, mobile)
#记录订单信息
new_order = new_distributor_item(
db=self.db,
order_id=order_id,
order_no=order_no,
sales_price=None,
count=1,
goods_info=goods_info,
mobile=mobile,
distributor_shop_id=result.distributor_shop_id,
distributor_goods_id='',
distributor_coupons=[{'coupon_sn': result.coupon_sn, 'coupon_pwd': result.coupon_pwd or ''}],
use_distributor_coupon=True
)
if new_order.ok:
#更新分销订单id
self.db.execute('update orders set distributor_order_id=%s where id = %s',
distributor_order_id, order_id)
#更新订单id
self.db.execute('update distributor_order set order_id=%s where id=%s',
order_id, distributor_order_id)
#更新该券验证的门店
self.db.execute('update item set sp_shop_id = %s where order_id=%s', shop_id, order_id)
self.redis.lpush(options.queue_coupon_local_verify, json_dumps({'coupon': result.coupon_sn,
'shop_id': shop_id, 'retry': 0, 'used_at': datetime.now()}))
result_list.append({'coupon_sn': result.coupon_sn, 'coupon_pwd': result.coupon_pwd or '',
'ok': result.ok, 'msg': result.msg})
# 如果有合法券号，发送确认短信
if messages:
shop_name = self.db.get('select name from supplier_shop where id=%s', shop_id).name
for mobile, coupon_sns in messages.items():
CouponConsumedMessage(self.redis, coupon_sns, shop_name, mobile).send()
self.write({'ok': True, 'result': result_list})
def post(self):
self.set_header('Content-Type', 'application/json; charset=UTF-8')
coupon_list = [
i.strip() for i in self.get_argument('coupons', '').split(',')
if i.strip()
]
if len(coupon_list) == 0:
self.write({'ok': False, 'msg': u'请输入券号'})
return
shop_id = self.get_argument('shop_id', 0)
#这一步是检查该操作员是否有权限验证券
all_shops = self.db.query(
'select ss.* from supplier_shop ss, supplier_user su where ss.supplier_id=%s and '
'ss.deleted=0 and su.id=%s and su.supplier_id=ss.supplier_id and '
' (su.shop_id=0 or ss.id=su.shop_id)',
self.current_user.supplier_id, self.current_user.id)
if int(shop_id) not in [i.id for i in all_shops]:
self.write({'ok': False, 'msg': u'对不起，您无权执行此操作'})
return
is_our_coupon = False
coupon_length = 0
messages = PropDict() # 用来存不同的手机号对应的券号
#检测长度是否相同、是否是我们的券
for coupon_sn in coupon_list:
coupon = self.db.get(
'select id, sn, mobile from item_coupon where sn=%s',
coupon_sn)
if coupon:
is_our_coupon = True
if coupon_length == 0:
coupon_length = len(coupon_sn)
elif coupon_length != len(coupon_sn):
coupon_length = -1
if coupon_length == -1:
self.write({'ok': False, 'msg': u'券号长度必须一致'})
return
result_list = []
if is_our_coupon:
# 我们自己的券
for coupon_sn in coupon_list:
# 本地检测
check_result = local_check(self.db, coupon_sn, shop_id)
if not check_result.ok:
ok, msg = (False, check_result.msg)
else:
# 合作伙伴API验证
api_result = yield partner_api_verify(self.db, coupon_sn)
if not api_result.ok:
ok, msg = (False, api_result.msg)
else:
# 本地验证
verify_result = local_verify(self.db, coupon_sn,
shop_id,
self.current_user.name)
ok, msg = (verify_result.ok, verify_result.msg)
# 验证通过，记录需要发送确认短信的券
if ok:
if messages.get(str(check_result.coupon.mobile)):
# 手机号已存在，添加券号到对应手机号
messages.get(str(
check_result.coupon.mobile)).append(
str(coupon_sn))
else:
# 手机号不存在，新建K-V对
messages.update({
str(check_result.coupon.mobile):
[str(coupon_sn)]
})
result_list.append({
'coupon_sn': coupon_sn,
'ok': ok,
'msg': msg
})
else:
mobile = self.get_argument('mobile', '')
# 检查手机号合法性
mobile_ptn = re.compile('^1[\d+]{10}$')
if not re.match(mobile_ptn, mobile):
# 不合法手机号，不记录
mobile = ''
# 尝试外部验证
results = yield partner_browser_verify(self.db, coupon_list,
shop_id)
for result in results:
if result.ok:
goods_info = self.db.get(
'select g.* from goods g where id=%s', result.goods_id)
verify_infos = {
'goods_id': result.goods_id,
'shop_id': shop_id
}
#创建分销订单
distributor_order_id = self.db.execute(
'insert into distributor_order(order_no, message, distributor_shop_id, created_at) '
'values (%s, %s, %s, NOW())', result.coupon_sn,
json_dumps(verify_infos), result.distributor_shop_id)
order_id, order_no = new_distributor_order(
self.db, result.distributor_shop_id,
goods_info.sales_price, goods_info.sales_price, mobile)
#记录订单信息
new_order = new_distributor_item(
db=self.db,
order_id=order_id,
order_no=order_no,
sales_price=None,
count=1,
goods_info=goods_info,
mobile=mobile,
distributor_shop_id=result.distributor_shop_id,
distributor_goods_id='',
distributor_coupons=[{
'coupon_sn':
result.coupon_sn,
'coupon_pwd':
result.coupon_pwd or ''
}],
use_distributor_coupon=True)
if new_order.ok:
#更新分销订单id
self.db.execute(
'update orders set distributor_order_id=%s where id = %s',
distributor_order_id, order_id)
#更新订单id
self.db.execute(
'update distributor_order set order_id=%s where id=%s',
order_id, distributor_order_id)
#更新该券验证的门店
self.db.execute(
'update item set sp_shop_id = %s where order_id=%s',
shop_id, order_id)
self.redis.lpush(
options.queue_coupon_local_verify,
json_dumps({
'coupon': result.coupon_sn,
'shop_id': shop_id,
'retry': 0,
'used_at': datetime.now()
}))
result_list.append({
'coupon_sn': result.coupon_sn,
'coupon_pwd': result.coupon_pwd or '',
'ok': result.ok,
'msg': result.msg
})
# 如果有合法券号，发送确认短信
if messages:
shop_name = self.db.get(
'select name from supplier_shop where id=%s', shop_id).name
for mobile, coupon_sns in messages.items():
CouponConsumedMessage(self.redis, coupon_sns, shop_name,
mobile).send()
self.write({'ok': True, 'result': result_list})
