def two_days_using_card_stylists(day_time_start):
'''
连续2天都创建售后服务卡的发型师
:param day_time_start:时间戳
:return:发型师名单
'''
stylist_one = mdb.customer_card_after.distinct(
"myid", {
"ctime": {
"$gt": ts2utcdatetime(day_time_start + 0 * 86400),
"$lt": ts2utcdatetime(day_time_start + 86400)
},
"status": 101
})
stylist_two = mdb.customer_card_after.distinct(
"myid", {
"ctime": {
"$gt": ts2utcdatetime(day_time_start + 86400),
"$lt": ts2utcdatetime(day_time_start + 2 * 86400)
},
"status": 101
})
two_stylist = (set(stylist_one) & set(stylist_two))
return list(two_stylist)
def get_stylists(star_timeStamp):
stylists = mdb.customer_card_after.distinct("myid",
{"ctime": {"$gt": ts2utcdatetime(star_timeStamp + 0 * 86400),
"$lt": ts2utcdatetime(star_timeStamp + 86400)},
"status": 101})
return stylists
def shangchuan_stylists(start_time, end_time):
return mdb.xm_exclusive_products.distinct(
"myid", {
"status": 1,
"utime": {
"$gte": ts2utcdatetime(start_time),
"$lt": ts2utcdatetime(end_time)
}
})
def get_card_nums(star_timeStamp, end_timStamp):
'''
售后卡生成情况，生成数量、被领取量
:param star_timeStamp:开始时间，时间戳
:param end_timStamp:结束时间，时间戳
:return:
all_cards_nums：完成的售后卡数量
coustomer_get_cards_nums：被顾客领取的数量
'''
all_cards_nums = mdb.customer_card_after.find({"ctime": {"$gt": ts2utcdatetime(star_timeStamp),
"$lt": ts2utcdatetime(end_timStamp)},
"status": 101}).count()
coustomer_get_cards_nums = mdb.customer_card_after.find({"ctime": {"$gt": ts2utcdatetime(star_timeStamp),
"$lt": ts2utcdatetime(end_timStamp)},
"status": 101, "is_get": 1}).count()
return all_cards_nums, coustomer_get_cards_nums
def write2excel_wangluo():
wangluo = mdb.xm_hair_need.find({
"status": 101,
"source": 2,
"ctime": {
"$gt": ts2utcdatetime(1538323200)
}
}).sort("_id", pymongo.DESCENDING)
w = xlwt.Workbook()
sh = w.add_sheet("wangluo_design0", cell_overwrite_ok=True)
writeHead_wangluo(sh)
for i, l in enumerate(wangluo):
if i > 65530:
break
else:
try:
id = str(l["_id"])
age = l["hair_info"]["age"]
sh.write(i + 1, 0, id)
sh.write(i + 1, 1, age)
sh.write(i + 1, 2, l["hair_info"]["three_court"])
sh.write(i + 1, 3, l["hair_info"]["face_length"])
sh.write(i + 1, 4, l["hair_info"]["face_sense"])
sh.write(i + 1, 5, l["hair_info"]["face_shape"])
sh.write(i + 1, 6, l["hair_info"]["natural_style"])
sh.write(i + 1, 7, l["hair_info"]["height"])
sh.write(i + 1, 8, l["hair_info"]["weight"])
sh.write(i + 1, 20, l["hair_info"]["skin_color"])
sh.write(i + 1, 21, l["hair_info"]["skin_hue"])
sh.write(i + 1, 22, l["hair_info"]["skin_shade"])
sh.write(i + 1, 23, l["hair_info"]["hair_length"])
sh.write(i + 1, 24, l["hair_info"]["career"])
sh.write(i + 1, 10, l["hair_info"]["like_change"])
sh.write(i + 1, 11, l["hair_info"]["like_length"])
sh.write(i + 1, 12, l["hair_info"]["accept_fringe"])
sh.write(i + 1, 13, l["hair_info"]["like_rantang"])
sh.write(i + 1, 14, l["hair_info"]["hair_care"])
sh.write(i + 1, 15, l["hair_info"]["career"])
sh.write(i + 1, 16, l["hair_info"]["like_dress"])
sh.write(i + 1, 17, l["hair_info"]["like_leisure"])
sh.write(i + 1, 18, l["hair_info"]["beauty_degree"])
sh.write(i + 1, 19, l["hair_info"]["season"])
sh.write(i + 1, 9, l["hair_info"]["hair_quantity"])
except:
pass
w.save("/Users/sunyihuan/Desktop/wangluo.xls")
def get_wangluosheji_stylists(day_time):
'''
获取某段时间内，完成网络设计订单的发型师名单
:param day_time:
:return:
'''
star_timeStamp = day2timestamp(day_time)
needs_finish = mdb.xm_hair_scheme.find({
"is_finish": 1,
"ctime": {
"$gte": ts2utcdatetime(star_timeStamp),
"$lt": ts2utcdatetime(star_timeStamp + 30 * 86400)
}
})
wangluo_stylist = []
for n_f in needs_finish:
need_id = id2ObjectId(n_f["need_id"])
needs_p = mdb.xm_hair_need.find({"_id": need_id})
for n in needs_p:
if n["source"] == 2:
if n["myid"] not in wangluo_stylist:
wangluo_stylist.append(n["myid"])
return wangluo_stylist
def three_days_using_card(day_time_start):
'''
连续3天都创建售后服务卡的发型师
:param day_time_start:时间戳
:return:
'''
stylist_one = mdb.customer_card_after.distinct(
"myid", {
"ctime": {
"$gt": ts2utcdatetime(day_time_start + 0 * 86400),
"$lt": ts2utcdatetime(day_time_start + 86400)
},
"status": 101
})
stylist_two = mdb.customer_card_after.distinct(
"myid", {
"ctime": {
"$gt": ts2utcdatetime(day_time_start + 86400),
"$lt": ts2utcdatetime(day_time_start + 2 * 86400)
},
"status": 101
})
stylist_three = mdb.customer_card_after.distinct(
"myid", {
"ctime": {
"$gt": ts2utcdatetime(day_time_start + 2 * 86400),
"$lt": ts2utcdatetime(day_time_start + 3 * 86400)
},
"status": 101
})
three_days_using_uv = set(stylist_one) & set(stylist_two) & set(
stylist_three)
two_stylist = (set(stylist_one) & set(stylist_two)) | (
set(stylist_two) & set(stylist_three))
return stylist_one, stylist_two, stylist_three, three_days_using_uv, two_stylist
created on 2019/1/17
@author:sunyihuan
'''
from utils import connect_mongodb_sheji, ts2utcdatetime, id2ObjectId
import xlwt
import pymongo
mdb = connect_mongodb_sheji()
# 2018年10月1日开始
wangluo_counts = mdb.xm_hair_need.find({
"status": 101,
"source": 2,
"ctime": {
"$gt": ts2utcdatetime(1538323200)
}
}).count()
xianchang_counts = mdb.xm_hair_need.find({"status": 101, "source": 1}).count()
def writeHead_wangluo(sh):
sh.write(0, 2, "three_court")
sh.write(0, 3, "face_length")
sh.write(0, 4, "face_sense")
sh.write(0, 5, "face_shape")
sh.write(0, 6, "natural_style")
sh.write(0, 7, "height")
sh.write(0, 8, "weight")
sh.write(0, 9, "hair_quantity")
def write2excel(name):
import xlwt
w = xlwt.Workbook()
sh = w.add_sheet("name")
sh.write(0, 0, "name")
sh.write(0, 1, "count")
for i in range(len(name)):
sh.write(i + 1, 0, name[i][0])
sh.write(i + 1, 1, name[i][1])
w.save("/Users/sunyihuan/Desktop/scheme_name.xls")
if __name__ == "__main__":
start_time = day2timestamp("2018-10-01")
sc_f, sc_name = scheme_static(ts2utcdatetime(start_time))
sc = sorted(sc_f.items(), key=lambda x: x[1], reverse=True)
name = sorted(sc_name.items(), key=lambda x: x[1], reverse=True)
print(sc)
print(name)
# for sn in sc_name.keys():
# print(sn.split("心")[1])
fa_count = {}
for ss in sc_name.keys():
fachang = ss.split("心")[1]
if fachang not in fa_count.keys():
fa_count[fachang] = sc_name[ss]
else:
fa_count[fachang] += sc_name[ss]
fa_count = sorted(fa_count.items(), key=lambda x: x[1], reverse=True)
print(fa_count)
def changJing_data(day_time_start):
'''
返回某一日数据看板中的数据
:param day_time_start: 日期，格式为："2018-12-21"
:return:
sheji_st：设计沟通场景日活,
sheji_st_vip：设计沟通场景VIP日活,
poster_st：营销拓客场景日活,
poster_st_vip：营销拓客场景VIP日活,
stylists：美助app日活,
stylists_yes：美助app昨日留存,
stylists_vip：美助app中VIP日活
'''
mdb = connect_mongodb_sheji() # 链接sheji
star_timeStamp = day2timestamp(day_time_start)
vip_st = mdb.wxuser.distinct("_id", {"expireat": {
"$gt": star_timeStamp
}}) # vip列表
vip_st = list_change_type(int, vip_st)
poster_st = mdb.log_poster.distinct(
"uid",
{"ctime": {
"$gt": star_timeStamp,
"$lt": star_timeStamp + 86400
}}) # 使用海报发型师uv
fxcl_st = mdb.log_material.distinct(
"uid",
{"ctime": {
"$gt": star_timeStamp,
"$lt": star_timeStamp + 86400
}}) # 使用海报发型师uv
poster_st = set(poster_st) | set(fxcl_st)
poster_st = list_change_type(int, poster_st)
stylist_one = mdb.xm_hair_scheme.distinct(
"myid", {
"utime": {
"$gte": ts2utcdatetime(star_timeStamp + 0 * 86400),
"$lt": ts2utcdatetime(star_timeStamp + 86400)
},
"is_finish": 1
}) # 完成订单发型师
stylist_one = list_change_type(int, stylist_one)
stylist_t = mdb.xm_good_hair.distinct(
"myid", {
"utime": {
"$gte": ts2utcdatetime(star_timeStamp + 0 * 86400),
"$lt": ts2utcdatetime(star_timeStamp + 86400)
},
"status": 1
}) # 收藏发型的发型师
stylist_t = list_change_type(int, stylist_t)
sheji_st = set(stylist_one) | set(stylist_t) # 设计沟通场景发型师日活
sheji_st = list_change_type(int, sheji_st)
sheji_st_vip = (set(stylist_one) | set(stylist_t)) & set(
vip_st) # 设计沟通场景发型师VIP日活
poster_st_vip = set(vip_st) & set(poster_st) # 使用海报发型师中VIP的人数
stylists = seven_day_stylist(star_timeStamp,
star_timeStamp + 86400) # 美业助手app当日uv
stylists_yestday = seven_day_stylist(star_timeStamp - 86400,
star_timeStamp) # 美业助手app昨日uv
vip_st = list_change_type(str, vip_st)
stylists_yes = set(stylists) & set(stylists_yestday) # 次日留存人数
stylists_vip = set(stylists) & set(vip_st) # 美业助手app当日VIP日活
return sheji_st, sheji_st_vip, poster_st, poster_st_vip, stylists, stylists_yes, stylists_vip
def get_finished_stylist_list(day_time):
'''
自2018-11-11开始
近30天完成订单用户中，完成订单次数前100排名（名字/电话/剪发价位/地址）
近30天完成订单用户中，完成订单次数前100排名（名字/电话/剪发价位/地址）
:return:day_time：开始日期，格式 "2018-11-11"
'''
w = xlwt.Workbook()
sh_wangluo = w.add_sheet("网络设计")
sh_xianchang = w.add_sheet("现场设计")
star_timeStamp = day2timestamp(day_time)
needs_finish = mdb.xm_hair_scheme.find({"is_finish": 1, "ctime": {"$gte": ts2utcdatetime(star_timeStamp),
"$lt": ts2utcdatetime(
star_timeStamp + 30 * 86400)}})
wangluo_stylist = {}
xianchang_stylist = {}
for n_f in needs_finish:
need_id = id2ObjectId(n_f["need_id"])
needs_p = mdb.xm_hair_need.find({"_id": need_id})
for n in needs_p:
my_id = n["myid"]
if n["source"] == 2:
if my_id not in wangluo_stylist.keys():
wangluo_stylist[my_id] = 1
else:
wangluo_stylist[my_id] += 1
elif n["source"] == 1:
if my_id not in xianchang_stylist.keys():
xianchang_stylist[my_id] = 1
else:
xianchang_stylist[my_id] += 1
wangluo_stylist = dict_sorted(wangluo_stylist)
xianchang_stylist = dict_sorted(xianchang_stylist)
wangluo_n = get_n_range(wangluo_stylist)
xianchang_n = get_n_range(xianchang_stylist)
sh_wangluo.write(0, 0, "姓名")
sh_wangluo.write(0, 1, "手机号码")
sh_wangluo.write(0, 2, "剪发价格")
sh_wangluo.write(0, 3, "发廊地址")
sh_wangluo.write(0, 4, "单数")
for i in range(wangluo_n):
my_id = wangluo_stylist[i][0]
my_nums = wangluo_stylist[i][1]
sh_wangluo.write(i + 1, 4, my_nums)
try:
s_p = mdb.person.find({"uid": my_id})
for s__ in s_p:
if s__["user_name"] != "":
sh_wangluo.write(i + 1, 1, s__["mobile"])
sh_wangluo.write(i + 1, 0, s__["user_name"])
sh_wangluo.write(i + 1, 2, s__["cut_price"])
sh_wangluo.write(i + 1, 3, s__["salon_position"])
except:
print(my_id)
sh_xianchang.write(0, 0, "姓名")
sh_xianchang.write(0, 1, "手机号码")
sh_xianchang.write(0, 2, "剪发价格")
sh_xianchang.write(0, 3, "发廊地址")
sh_xianchang.write(0, 4, "单数")
for i_ in range(xianchang_n):
my_id__ = xianchang_stylist[i_][0]
my_nums_ = xianchang_stylist[i_][1]
sh_xianchang.write(i_ + 1, 4, my_nums_)
try:
s_p = mdb.person.find({"uid": my_id__})
for s__ in s_p:
if s__["user_name"] != "":
sh_xianchang.write(i_ + 1, 0, s__["user_name"])
sh_xianchang.write(i_ + 1, 1, s__["mobile"])
sh_xianchang.write(i_ + 1, 2, s__["cut_price"])
sh_xianchang.write(i_ + 1, 3, s__["salon_position"])
except:
print(my_id__)
w.save("/Users/sunyihuan/Desktop/{}近30天完成订单发型师名单（前100）.xls".format(day_time))