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 save(self):
"""写入数据库"""
args = {k: v for k, v in self.__dict__.items() if v is not None}
sql = db_module.structure_sql("add", table_name, **args)
if "sn" in args.keys(): # 编辑的情况
query = "where sn={}".format(args.pop('sn'))
sql = db_module.structure_sql("edit", table_name, query, **args)
ses = db_module.sql_session()
ses.execute(sql)
ses.commit()
ses.close()
def delete(**kwargs):
"""删除"""
message = {"message": "success"}
flag = True
for x in kwargs.keys():
if x not in columns:
flag = False
message['message'] = '错误的参数：{}'.format(x)
break
if not flag:
return message
else:
try:
sn = kwargs.pop('sn')
company_sn = kwargs.pop('company_sn')
query = "where sn={} and company_sn={}".format(sn, company_sn)
sql = db_module.structure_sql('delete', table_name, query)
ses = db_module.sql_session()
proxy = ses.execute(sql)
ses.commit()
message[
'message'] = "success" if proxy.rowcount == 1 else "删除失败，没有此团队或没有删除的权限"
ses.close()
except KeyError as e:
print(e)
message['message'] = '{}不能为空'.format(e.args[0])
except Exception as e_all:
print(e_all)
message['message'] = '操作失败'
finally:
return message
def edit(cls, **kwargs):
"""
编辑用户
:param kwargs: 各种参数
:return: 结果的字典
"""
message = {"message": "success"}
try:
sn = kwargs.pop("sn")
query = "where sn={}".format(sn)
flag = True
for x in kwargs.keys():
if x not in columns:
flag = False
message['message'] = '错误的参数：{}'.format(x)
break
if not flag:
pass
else:
sql = db_module.structure_sql("edit", table_name, query,
**kwargs)
ses = db_module.sql_session()
proxy = ses.execute(sql)
message[
'message'] = "success" if proxy.rowcount == 1 else "编辑失败，没有此用户或没有删除的权限"
ses.commit()
ses.close()
except KeyError:
message['message'] = '缺少用户sn'
except Exception as all_e:
print(all_e)
message['message'] = '数据库执行错位'
finally:
return message
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
def edit_customer(**kwargs):
"""编辑用户信息"""
message = {"message": "success"}
the_type = kwargs.pop("the_type")
user_sn = kwargs.pop("user_sn")
sql = ""
company_sn = 0
try:
company_sn = kwargs.pop("company_sn")
except KeyError:
print("admin账户")
finally:
if company_sn == 0:
term = " where user_sn={}".format(user_sn)
else:
term = " where user_sn={} and company_sn={}".format(user_sn, company_sn)
if the_type == "delete":
"""删除"""
sql = "delete from {} {}".format(table_name, term)
elif the_type == "edit":
query = "where user_sn={} and company_sn={}".format(user_sn, company_sn)
sql = db_module.structure_sql("edit", table_name, query, **kwargs)
else:
pass
if sql == "":
message['message'] = "不明操作"
else:
ses = db_module.sql_session()
ses.execute(sql)
ses.commit()
ses.close()
return message
def bak_customer(**kwargs):
"""
异步保存用户
"""
sql = structure_sql("add", "customer_info", **kwargs)
ses = bak_sql_session()
ses.execute(sql)
ses.commit()
ses.close()
def delete_company(**kwargs):
"""删除公司信息"""
message = {"message": "success"}
sn = kwargs.pop("sn")
term = "where sn={}".format(sn)
sql = db_module.structure_sql("delete", table_name, term, **kwargs)
ses = db_module.sql_session()
ses.execute(sql)
ses.commit()
ses.close()
return message
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 edit_company(**kwargs):
"""编辑公司信息"""
message = {"message": "success"}
sn = kwargs.pop("sn")
user_password = kwargs['user_password']
user_password = hashlib.md5(user_password.strip().encode()).hexdigest()
kwargs['user_password'] = user_password
term = "where sn={}".format(sn)
sql = db_module.structure_sql("edit", table_name, term, **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 get_by_sn(cls, sn):
"""
根据用户sn获取用户对象
:return: Employee的实例
"""
message = {"message": "success"}
if sn is None:
message['message'] = 'sn不能为空'
else:
query = "where sn={} and user_status=1".format(sn)
sql = db_module.structure_sql("select", table_name, query)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchone()
ses.close()
if raw is None:
return None
else:
args = dict(zip(columns, raw))
obj = Employee(**args)
return obj
def add(**kwargs):
"""添加团队"""
message = {"message": "success"}
flag = True
for x in kwargs.keys():
if x not in columns:
flag = False
message['message'] = '错误的参数：{}'.format(x)
break
if not flag:
return message
else:
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 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