def begin_track(customer_sn, employee_sn):
"""
开始跟踪
:param customer_sn:
:param employee_sn:
:return: None
"""
"""先检查是否有基本跟进信息"""
ses = db_module.sql_session()
sql = "select count(1) from {} where sn={}".format(table_name, customer_sn)
proxy = ses.execute(sql)
has_it = proxy.fetchone()[0]
if has_it == 0:
args = {"begin_date": db_module.current_datetime(), "sn": customer_sn}
sql = db_module.structure_sql("add", table_name, **args)
ses.execute(sql)
ses.commit()
"""插入一条跟进信息"""
args = {"customer_sn": customer_sn, "employee_sn": employee_sn, "track_type_sn": 14, "create_date":
db_module.current_datetime()}
sql = db_module.structure_sql("add", track_table, **args)
ses.execute(sql)
args = {"track_status": 14}
sql = db_module.structure_sql("edit", table_name, "where sn={}".format(customer_sn), **args)
ses.execute(sql)
ses.commit()
ses.close()
def add_user(**kwargs):
"""添加用户"""
kwargs['create_date'] = db_module.current_datetime()
kwargs['employee_sn'] = 0
message = {"message": "success"}
company_sn = check_special_url(kwargs['page_url'])
if company_sn != 0: # 是否是专用链接？
kwargs['company_sn'] = company_sn
kwargs['in_count'] = 0
else:
company_sn = next_company_sn(0) # 从分配计划获取下一个company_sn
kwargs['company_sn'] = company_sn
"""获取team_sn"""
team_sn = Team.allot_customer(company_sn)
kwargs['team_sn'] = team_sn
ses = db_module.sql_session()
"""只分配到团队，不分配到个人"""
sql = db_module.structure_sql("add", table_name, **kwargs)
file_path = os.path.join(os.path.split(__file__)[0], "sql.log")
file = open(file_path, mode="a", encoding="utf8")
print(sql, file=file)
print(sql)
file.flush()
file.close()
try:
ses.execute(sql)
ses.commit()
bak_customer.delay(**kwargs) # 备份用户
except Exception as e:
print(e)
message['message'] = '注册失败'
finally:
ses.close()
return message
class User(Base):
__tablename__ = "user_info"
user_sn = Column(Integer, primary_key=True, autoincrement=True)
user_name = Column(String(45), unique=True, nullable=False)
user_password = Column(String(200), nullable=False)
create_date = Column(DateTime, nullable=False, default=current_datetime())
def __repr__(self):
return "<User(user_name={}, user_password={}, create_date={})>" \
.format(self.user_sn, self.user_password, self.create_date)
def add_company(**kwargs):
"""创建公司"""
message = {"message": "success"}
kwargs['create_date'] = db_module.current_datetime()
user_password = kwargs['user_password']
user_password = hashlib.md5(user_password.strip().encode()).hexdigest()
kwargs['user_password'] = user_password
sql = db_module.structure_sql("add", table_name, **kwargs)
ses = db_module.sql_session()
ses.execute(sql)
ses.commit()
ses.close()
return message
def add_user(**kwargs):
"""添加用户"""
kwargs['create_date'] = db_module.current_datetime()
message = {"message": "success"}
sql = db_module.structure_sql("add", table_name, **kwargs)
ses = db_module.sql_session()
try:
ses.execute(sql)
ses.commit()
except Exception as e:
print(e)
message['message'] = '注册失败'
finally:
ses.close()
return message
def count_by_team_sn(team_sn):
result = 0
team_sn_list = get_my_team_sn(team_sn)
sn_list = "({})".format(",".join([str(x) for x in team_sn_list]))
today = db_module.current_datetime(-1)
sql = "select count(1) from {} where team_sn in {} and create_date>'{}'".format(table_name, sn_list, today)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchone()
ses.close()
if raw is None:
pass
else:
result = raw[0]
return result
def teachers(the_type, t_name='', t_nickname='', t_description='', t_password="******", t_title="宏赟分析师", t_id=0,
t_can_use=1):
ses = get_conn()
message = {"message": "success"}
if the_type == "add":
sql = " insert into teachinfo(t_name,t_nickname,t_title,t_description,t_password,t_CreateTime,t_can_use,t_Level,t_in_class) values('{0}','{1}','{2}','{3}','{4}',now(),1,1,1)".format(
t_name, t_nickname, t_title, t_description, t_password, db_module.current_datetime())
print(sql)
ses.execute(sql)
ses.commit()
elif the_type == "edit":
if t_id == 0:
message = {"message": "老师id不存在"}
else:
sql = " update teachinfo set t_name='{0}',t_nickname='{1}',t_description='{2}',t_can_use={3},t_title='{4}',t_password='******' where t_id={6}".format(
t_name, t_nickname, t_description, t_can_use, t_title, t_password, t_id)
print(sql)
ses.execute(sql)
ses.commit()
elif the_type == "delete":
if t_id == 0:
message = {"message": "老师id不存在"}
else:
sql = "delete from teachinfo where t_id={0}".format(t_id)
print(sql)
ses.execute(sql)
ses.commit()
elif the_type == "all": # 查看所有老师的信息，用于营销直播室的老师风采
sql = "select t_id,t_account,t_name,t_nickname,t_title,t_description from teachinfo where t_can_use=1 order by t_CreateTime desc" # id 名字 昵称 介绍
proxy = ses.execute(sql)
raw = proxy.fetchall()
raw = [{"t_id": x[0], "t_account": x[1], "t_name": x[2], "t_nickname": x[3], "t_title": x[4],
"t_description": x[5]} for x in raw]
message["data"] = raw
elif the_type == "reset_password":
sql = "update teachinfo set t_password='******' where t_id={1}".format(t_password, t_id)
ses.execute(sql)
ses.commit()
else:
message = {"message": "未知的teachers操作"}
ses.close()
return message
def next_employee(employee_sn):
""""""
ses = db_module.sql_session()
sql = "select employee_info.sn,employee_info.team_sn,has_team from employee_info,team_info,position_info where position_info.sn=employee_info.position_sn and employee_info.team_sn=team_info.sn" \
" and team_info.leader_sn={}".format(employee_sn)
proxy = ses.execute(sql)
raw = proxy.fetchall()
print(raw)
if len(raw) == 0:
ses.close()
print("this")
return employee_sn
else:
if raw[0][2] == 1:
"""有团队"""
sn_list = [x[0] for x in raw]
r = list()
for x in sn_list:
sql = "select sn from team_info where leader_sn={}".format(x)
proxy = ses.execute(sql)
r.append((x, proxy.fetchone()[0]))
ses.close()
r2 = list()
for x in r:
r2.append((x[0], count_by_team_sn(x[1]))) # 按团队统计人数
"""按团队统计分配数"""
r2.sort(key=lambda obj: obj[1])
return next_employee(r2[0][0])
else:
sn_list = [x[0] for x in raw]
r = list()
today = db_module.current_datetime(-1)
for x in sn_list:
sql = "select count(1) from customer_info where employee_sn={} and in_count_company=1 and create_date>'{}'".format(
x, today)
proxy = ses.execute(sql)
r.append((x, proxy.fetchone()[0]))
ses.close()
"""按员工排序人数统计"""
r.sort(key=lambda obj: obj[1])
return r[0][0]
def __init__(self,
real_name,
user_phone,
user_password="******",
is_leader=0,
position_sn=1):
"""
员工构造器
:param real_name: 真实姓名，必须。
:param user_phone: 员工手机，唯一。
:param user_password: 员工密码，默认 123456。
:param is_leader: 是否有分配权/操作团队的权利。默认0，没有。
:param position_sn: 职位，默认是0，投资顾问。
"""
self.real_name = real_name
self.user_phone = user_phone
self.user_password = user_password
self.is_leader = is_leader
self.position_sn = position_sn
self.create_date = db_module.current_datetime()
self.staff_status = 1
def process(**kwargs):
"""对计划及其参与者的添加，修改，删除"""
message = {"message": "success"}
the_type = kwargs.pop("the_type")
now = db_module.current_datetime()
kwargs['create_date'] = now
kwargs['update_date'] = "1970-01-01"
try:
member_list = kwargs.pop('member_list')
except KeyError:
member_list = list()
ses = db_module.sql_session()
if the_type == "add":
"""添加策略"""
sql = db_module.structure_sql("add", table_name, **kwargs)
proxy = ses.execute(sql)
plan_sn = proxy.lastrowid
for member in member_list:
member['plan_sn'] = plan_sn
member['create_date'] = now
sub = db_module.structure_sql("add", "plan_item_info", **member)
ses.execute(sub)
ses.commit()
elif the_type == "edit":
plan_sn = kwargs.pop("sn")
query = "where sn={}".format(plan_sn)
sql = db_module.structure_sql("edit", table_name, query, **kwargs)
ses.execute(sql)
sql = "select sn,member_sn,per_num from plan_item_info where plan_sn={}".format(
plan_sn)
proxy = ses.execute(sql)
raw = proxy.fetchall()
# 添加容器，更新容器和删除容器。
add_list, edit_list, delete_list = list(), list(), list()
if len(raw) == 0:
pass
else:
raw = [dict(zip(('sn', 'member_sn', "per_num"), x)) for x in raw]
delete_list = [
x for x in raw if x['sn'] not in
[int(y['sn']) for y in member_list if y['sn'] != ""]
]
add_list = [x for x in member_list if x['sn'] == ""]
edit_list = [
x for x in member_list
if x['sn'] in [str(y['sn']) for y in raw]
]
for x in delete_list:
sql = "delete from plan_item_info where sn={}".format(x['sn'])
ses.execute(sql)
for x in edit_list:
sql = "update plan_item_info set member_sn={},per_num={} where sn={}".format(
x['member_sn'], x['per_num'], x['sn'])
ses.execute(sql)
for x in add_list:
x.pop("sn")
x['create_date'] = now
x['plan_sn'] = plan_sn
sql = db_module.structure_sql("add", "plan_item_info", **x)
ses.execute(sql)
ses.commit()
elif the_type == "delete":
plan_sn = kwargs.pop("sn")
sql = "delete from {} where sn={}".format(table_name, plan_sn)
ses.execute(sql)
ses.commit()
elif the_type in ("up", "down"):
plan_status = 1 if the_type == "up" else 0
plan_sn = kwargs.pop("sn")
owner_sn = kwargs.pop("owner_sn")
if plan_status:
sql = "update {} set plan_status=0 where owner_sn={}".format(
table_name, owner_sn)
ses.execute(sql)
sql = "update {} set plan_status=1,update_date='{}' where sn={}".format(
table_name, now, plan_sn)
ses.execute(sql)
else:
sql = "update {} set plan_status=0 where sn={}".format(
table_name, plan_sn)
ses.execute(sql)
ses.commit()
else:
message['message'] = "不理解的操作"
ses.close()
return message
def __init__(self, user_name, user_phone, page_url, source_sn):
self.user_name = user_name
self.user_phone = user_phone
self.page_url = page_url
self.source_sn = source_sn
self.create_date = db_module.current_datetime()
def export_customer(company_sn: int, begin: str = None, end: str = None) -> None:
"""
导出/生产一个用户数据的ｅｘｃｅｌ文件
:param company_sn: 公司sn int
:param begin: 开始时间，字符串格式 %Y-%m-%d %H:%M:%S
:param end: 结束时间，字符串格式 %Y-%m-%d %H:%M:%S
:return: None
"""
end = db_module.current_datetime() if end is None or end == "" else end
begin = "1970-01-01 :00:00:00" if begin is None or begin == "" else begin
keys = ["user_sn", "user_name", "user_phone", "page_url", "create_date"]
vals = ["客户id", "客户名称", "客户手机", "注册网址", "注册时间"]
if company_sn == 0:
par = ''
else:
par = "and company_sn={}".format(company_sn)
sql = "select {0} from {1} where create_date>'{2}' and create_date<'{3}' {4} order by create_date desc".format(
",".join(keys), table_name, begin, end, par)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
wb = xlwt.Workbook()
sheet = wb.add_sheet("客户名单")
# 创建格式style
style = xlwt.XFStyle()
# 创建font，设置字体
font = xlwt.Font()
# 字体格式
font.name = 'Times New Roman'
# 将字体font，应用到格式style
style.font = font
# 创建alignment，居中
alignment = xlwt.Alignment()
# 居中
alignment.horz = xlwt.Alignment.HORZ_CENTER
# 应用到格式style
style.alignment = alignment
style1 = xlwt.XFStyle()
font1 = xlwt.Font()
font1.name = 'Times New Roman'
# 字体颜色（绿色）
# font1.colour_index = 3
# 字体加粗
font1.bold = True
style1.font = font1
style1.alignment = alignment
for index, item in enumerate(raw):
if index == 0:
for i, key in enumerate(keys):
sheet.write(0, i, vals[i], style1)
else:
pass
for i, key in enumerate(keys):
val = item[i]
val = val.strftime("%Y-%m-%d %H:%M:%S") if isinstance(val, datetime.datetime) else val
sheet.write(index + 1, i, val)
excel_path = os.path.join(EXCEL_PATH, str(company_sn))
if not os.path.exists(excel_path):
os.makedirs(excel_path)
wb.save(os.path.join(excel_path, "{}至{}.xls".format(begin, end)))