def get_plan_by_owner_sn(cls, owner_sn=0, curr=True):
"""
根据拥有者id返回计划字典
:param owner_sn:
curr 是不是只返回当前使用的计划？
:return: 字典的数组/字典
"""
_table_name = "plan_info"
plan_cols = db_module.get_columns(_table_name)
if not curr:
sql = "select {} from {} where owner_sn={}".format(
",".join(plan_cols), _table_name, owner_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
res = [dict(zip(plan_cols, x)) for x in raw]
else:
sql = "select {} from {} where owner_sn={} and plan_status=1".format(
",".join(plan_cols), _table_name, owner_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchone()
ses.close()
if raw is None:
res = None
else:
res = dict(zip(plan_cols, raw))
return res
def insert_dialog_to_database(dialog_list):
alist = [x["message"].strip() for x in dialog_list if
(x["message"].strip('') != '' and len(x["message"].strip('')) < 255)]
ses = db_module.sql_session()
count = 0
# 先读取数据库里所有已存在的对话记录
sql = "select s_string from Robot_Dialog_Collector"
proxy = ses.execute(sql)
result = proxy.fetchall()
if len(result) == 0:
for x in alist:
sql_insert = "insert into Robot_Dialog_Collector(s_string) values('{0}') ".format(x)
ses.execute(sql_insert)
count += 1
CONN.commit()
else:
query_list = [x[0] for x in result]
for x in alist:
if x in query_list:
pass
else:
sql_insert = "insert into Robot_Dialog_Collector(s_string) values('{0}') ".format(x)
ses.execute(sql_insert)
count += 1
ses.commit()
ses.close()
now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") # 本次操作结束的的日期
def can_process_excel(company_sn: int = 0) -> (int, None):
"""检查(公司)账户是否有操作excel模块的权利？
:param company_sn: 公司的sn
：return： 如果有操作权限，就返回公司的sn，否则，返回None
"""
result = None
if company_sn == 0:
result = company_sn
elif isinstance(company_sn, int):
ses = db_module.sql_session()
sql = "select export_excel from company_info where sn={}".format(company_sn)
proxy = ses.execute(sql)
raw = proxy.fetchone()
ses.close()
if raw is None:
pass
else:
flag = raw[0]
if flag == 1:
result = company_sn
else:
pass
else:
pass
return result
def page_by_employee_sn(employee_sn, index=1, length=30, term='', sn=0):
"""分页查询注册用户，后台管理用，
employee_sn 员工sn
index是页码，
length是每页多少条记录
term 是查询条件表达式 类是 user_sn>10 暂时不用
sn 公司的sn，0表示未分配
return 数组
"""
result = []
try:
index = int(index)
length = int(length)
except ValueError:
index = ""
if db_module.validate_arg(term, "_") and index != "":
"""参数检查通过"""
skip = (index - 1) * length
limit = length
sql = "select {} from {} where employee_sn={} order by create_date desc limit {},{}". \
format(",".join(columns), table_name, employee_sn, skip, limit)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
if len(raw) == 0:
pass
else:
result = [dict(zip(columns, x)) for x in raw]
else:
pass
return result
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 sn_name(cls, company_sn, manager=False):
"""返回sn和team_name的字典,manager表示是否要求是团队管理"""
ses = db_module.sql_session()
result = dict()
sn_list = list()
if manager:
sql = "select sn from position_info where company_sn={} and has_team=1".format(
company_sn)
proxy = ses.execute(sql)
raw = proxy.fetchall()
if len(raw) == 0:
pass
else:
sn_list = [x[0] for x in raw]
sql = "select sn,real_name,position_sn from {} where company_sn={}".format(
table_name, company_sn)
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
if len(raw) == 0:
pass
else:
result = {x[0]: x[1] for x in raw}
if len(sn_list) == 0:
pass
else:
result = {x[0]: x[1] for x in raw if x[2] in sn_list}
return result
def __get_next(plan_dict, begin_plan):
"""根据分配计划字典和计划开始时间，计算下一个获取资源的客户的sn，
plan_dict 分配计划，是分公司sn和分配比率的字典
begin_plan 是计算分配比率的起始时间。
以上两个参数可以直接由get_plan函数获取
return 被分配的分公司的sn
"""
ses = db_module.sql_session()
d = dict()
# begin_plan = begin_plan.strftime("%Y-%m-%d %H:%M:%S")
for sn in plan_dict.keys():
sql = "select count(1) from customer_info where company_sn={} and in_count=1 and create_date>'{}'".format(
sn, begin_plan)
proxy = ses.execute(sql)
r = proxy.fetchone()[0]
d[sn] = r
ses.close()
all_count = sum(list(d.values()))
if all_count == 0:
return list(plan_dict.keys())[0]
else:
result = list(plan_dict.keys())[0]
for k, v in d.items():
if (v / all_count) < (plan_dict[k] / 100):
result = k
break
return result
def count_customer_by_team_sn(cls, team_sn, begin_date, all=False):
"""
递归的统计一个团队的客户数目。
:param team_sn: 团队sn
:param begin_date: 统计开始时间，这个一般是分配计划的开始时间
:param all: 只统计按计划分配的还是统计所有的，默认只统计按计划分配的
:return: int
"""
sql = "select employee_info.sn,position_info.has_team from " \
"employee_info,position_info where employee_info.position_sn=position_info.sn and " \
"employee_info.user_status=1 and employee_info.team_sn={}".format(team_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
result = 0
if len(raw) == 0:
return result
else:
team_sn_dict = {x[0]: x[1] for x in raw}
for k, v in team_sn_dict.items():
if v == 0:
result += cls.count_customer_by_team_sn_simple(
team_sn=team_sn)
break
else:
result += cls.count_customer_by_team_sn(
cls.get_team_sn_by_leader_sn(k), begin_date, all)
return result
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 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 page(company_sn=0, index=1, length=30):
"""分页查询团队，后台管理用，
company_sn
index是页码，
length是每页多少条记录
return 数组
"""
result = []
try:
index = int(index)
length = int(length)
except ValueError:
index = 1
length = 30
sql = ""
skip = (index - 1) * length
limit = length
if company_sn == 0:
"""所有分公司"""
sql = "select {} from {} order by sn desc limit {},{}". \
format(",".join(columns), table_name, skip, limit)
else:
sql = "select {} from {} where company_sn={} order by sn desc limit {},{}". \
format(",".join(columns), table_name, company_sn, skip, limit)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
if len(raw) == 0:
pass
else:
result = [dict(zip(columns, x)) for x in raw]
return result
def get_team_members(parent_sn):
"""根据parent_sn获取可用的团队成员"""
result = dict()
sql = "select sn,real_name from employee_info where user_status=1 and team_sn={}".format(
parent_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
if len(raw) == 0:
ses.close()
return result
else:
result = {x[0]: x[1] for x in raw}
sql = "select leader_sn,sn,team_name from team_info where company_sn=(select company_sn from team_info" \
" where sn={})".format(parent_sn)
proxy = ses.execute(sql)
raw = proxy.fetchall()
raw = {x[0]: [x[1], x[2]] for x in raw}
ses.close()
new_res = dict()
for k, v in result.items():
if k in raw:
temp = raw[k]
new_res[temp[0]] = temp[1]
else:
new_res[k] = v
return new_res
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 children_sn_name(cls, employee_sn):
""""
求employee_sn的团队成员的sn和名字的对应关系的字典。
1.如果此人没有自己的团队，返回本身的sn,name
2.如果此人有自己的团队，返回他的团队成员的sn，name的dict
3.如果此人的团队成员是其他团队的管理，那么返回团队的sn和name的dict
4.如果此人的团队成员包含以上两种情况，则混合的返回以上两种情况组成的字典
"""
sql = "select sn, real_name from {} where user_status=1 and " \
"team_sn=(select sn from team_info where leader_sn={})".format(table_name, employee_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
if len(raw) == 0:
sql = "select sn, real_name from employee_info where sn={}".format(
employee_sn)
proxy = ses.execute(sql)
raw = proxy.fetchone()
ses.close()
return {raw[0]: raw[1]}
else:
result = dict()
sn_name_dict = {x[0]: x[1] for x in raw}
for k, v in sn_name_dict.items():
sql = "select sn,team_name from team_info where leader_sn={}".format(
k)
proxy = ses.execute(sql)
raw = proxy.fetchone()
if raw is None:
result[k] = v
else:
result[raw[0]] = raw[1]
ses.close()
return result
def delete_by_sn(cls, team_sn=None):
"""删除一个"""
sql = "delete from {} where sn={}".format(cls._table_name, team_sn)
ses = db_module.sql_session()
ses.execute(sql)
ses.commit()
ses.close()
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 get_my_team_sn(team_sn, result=list()):
if len(result) == 0:
result = [team_sn]
else:
result.append(team_sn)
ses = db_module.sql_session()
sql = "select leader_sn from team_info where sn={}".format(team_sn)
proxy = ses.execute(sql)
raw = proxy.fetchone()
sql = "select sn from employee_info where team_sn={}".format(team_sn)
proxy = ses.execute(sql)
raw = proxy.fetchall()
if len(raw) == 0:
ses.close()
else:
sn_list = "({})".format(",".join([str(x[0]) for x in raw]))
sql = "select sn from team_info where leader_sn in {}".format(sn_list)
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
if len(raw) == 0:
pass
else:
sn_list = [x[0] for x in raw if x[0] not in result]
for sn in sn_list:
get_my_team_sn(sn, result)
return result
def check_login(user_name, user_password_md5):
"""检验登录"""
user_name = user_name.strip()
user_password_md5 = user_password_md5.strip()
message = {"message": "success"}
if not db_module.validate_arg(user_name, "_"):
message['message'] = "用户名非法"
else:
ses = db_module.sql_session()
sql = "select sn,user_password from {} where user_status=1 and user_name='{}'".format(
table_name, user_name)
proxy = ses.execute(sql)
raw = proxy.fetchone()
ses.close()
if raw is None:
message['message'] = "用户名不存在"
else:
md5_str = raw[1]
if md5_str != user_password_md5:
message['message'] = "密码错误"
else:
message['data'] = {
"sn": raw[0],
"user_name": user_name,
"user_password": user_password_md5
}
return message
def guest_message(id, event_type, referer, page_url, ip):
ses = db_module.sql_session()
result_select = ""
print("page_url is " + page_url + " referer is " + referer)
# 取完参数，运行程序
print("id is ", str(id), " referer is ", referer, " page_url is ",
page_url, " event_type is ", event_type)
the_id = id
if int(the_id) == 0:
now = datetime.datetime.now().strftime("%Y%m%d%H%M%S%f") # g格式化输出到毫秒
random_number = random.randint(0, 999) # 生成随机数，跟日期一起作为新id的唯一判断。
the_id = int(now[2:-3] + str(random_number))
print("the id is " + str(the_id))
if event_type == "open_page":
# 插入一条open_page类型的记录
sql_action = "insert into guest_action_recode(Guest_id,Referer,Page_url,Event_type,Event_Date,Ip) values({0},'{1}','{2}','{3}',now(),'{4}')".format(
the_id, referer, page_url, 'open_page', ip) # 插入一条open_page的事件记录。
print("sql_action:" + sql_action)
ses.execute(sql_action)
ses.commit()
print("result_select is " + str(result_select))
print("josn result_select is " + str(json.dumps(result_select)))
ses.close()
return json.dumps(the_id) # 返回id
else:
sql_action = "insert into guest_action_recode(Guest_id,Referer,Page_url,Event_type,Event_Date) values({0},'{1}','{2}','{3}',now())".format(
the_id, referer, page_url, event_type) # 插入一条open_page的事件记录。
print("event_type不等于open_page时的 sql_action:" + sql_action)
ses.execute(sql_action)
ses.commit()
ses.close()
return json.dumps('recode insert success')
def get_member_dict(cls, team_sn):
"""
根据团队sn获取下面成员的
:param team_sn:
:return:dict
"""
sql = "select employee_info.sn,position_info.has_team from " \
"employee_info,position_info where employee_info.position_sn=position_info.sn and " \
"employee_info.user_status=1 and employee_info.team_sn={}".format(team_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
if len(raw) == 0:
"""说明没有子成员"""
pass
else:
res = dict()
if raw[0][1] == 1:
for x in raw:
"""说明这是团队"""
temp_dict = cls.get_team_dict_by_leader_sn(x[0])
res.update(temp_dict)
else:
res = Employee.sn_name_in_team(team_sn)
return res
def logger(the_type, data={}):
ses = db_module.sql_session()
sql = "insert into Logger(event_type,event_context,event_href,event_ip,guest_id,user_agent,event_date) values('{0}','{1}','{2}','{3}',{4},'{5}',now())".format(
data["event_type"], data["event_context"], data["event_href"],
data["event_ip"], data["guest_id"], data["user_agent"])
ses.execute(sql)
ses.commit()
ses.close()
def talks(the_type, messages=[]):
ses = db_module.sql_session()
data = []
if the_type == "save":
if len(messages) == 0:
print("没有消息写入数据库")
else:
for x in messages:
print(x)
user_level = x["user_level"]
message = x["message"].replace("'", "￡")
message_id = x["message_id"]
come_from = x['come_from']
atime = x["time"]
name = x["name"]
ip = x["ip"]
page_url = x["page_url"]
sql = "insert into chartroom_talks(user_level,message_id,message,come_from,time,name,page_url,ip,save_date) values({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}',now())".format(
user_level, message_id, message, come_from, atime, name,
page_url, ip)
printSqlStr(34, sql)
ses.execute(sql)
ses.commit()
elif the_type == "delete":
if len(messages) == 0:
print("没有消息需要删除")
else:
temp = ''
for x in messages:
temp += "'" + x + "',"
temp = temp.rstrip(",")
sql = "delete from chartroom_talks where message_id in ({0})".format(
temp)
print(sql)
printSqlStr(47, sql)
ses.execute(sql)
ses.commit()
else:
sql = "select user_level,message_id,message,come_from,time,name,page_url,ip from chartroom_talks order by save_date desc limit 0,40"
printSqlStr(52, sql)
proxy = ses.execute(sql)
raw = proxy.fetchall()
if len(raw) == 0:
pass
else:
data = [{
"user_level": x[0],
"message_id": x[1],
"message": x[2].replace("￡", "'"),
"come_from": x[3],
"time": x[4],
"name": x[5],
"page_url": x[6],
"ip": x[7]
} for x in raw]
ses.close()
data.reverse()
return {"data": data}
def count(employee_sn):
"""统计"""
sql = "select count(1) from {} where employee_sn={}".format(customer_table, employee_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchone()
ses.close()
result = raw[0]
return result
def team_count(company_sn):
"""统计"""
sql = "select count(1) from {} where company_sn={}".format(
table_name, company_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchone()[0]
ses.close()
return raw
def page(index=1, length=30, term='', key_word=''):
"""分页查询，后台管理用，index是页码，length是每页多少条记录
term是查询的条件 相当于 where case=value中的case
key_word查询条件的值 相当于 where case=value中的value
"""
message = {"message": "success"}
if isinstance(index, (int, str)) and isinstance(length, (int, str)):
try:
index = index if isinstance(index, int) else int(index)
length = length if isinstance(length, int) else int(length)
ses = db_module.sql_session()
columns = db_module.get_columns(table_name)
if term != "" and key_word != "" and term in columns:
sql = "select " + ",".join(columns) + (
" from {} where {}='{}' order by create_date desc "
"limit {},{}".format(table_name, term, key_word,
(index - 1) * length, length))
else:
sql = "select " + ",".join(columns) + (
" from {} order by create_date desc "
"limit {},{}".format(table_name,
(index - 1) * length, length))
try:
proxy_result = ses.execute(sql)
result = proxy_result.fetchall()
if len(result) != 0:
result = [db_module.str_format(x) for x in result]
data = [dict(zip(columns, x)) for x in result]
data = db_module.str_format(data)
"""根据策略sn查询成员数目"""
for x in data:
if x['sn'] == 1: # 默认策略的情况
x["member_count"] = company_count(parent_sn=0)
else:
sql = "select count(1) from plan_item_info where plan_sn={}".format(
x['sn'])
proxy = ses.execute(sql)
member_count = proxy.fetchone()[0]
x["member_count"] = member_count
else:
data = []
message['data'] = data
except Exception as e:
print(e)
message['message'] = "查询错误"
finally:
ses.close()
except TypeError:
message['message'] = "参数错误"
finally:
pass
else:
raise TypeError("参数只能是str或者int")
message['message'] = "参数类型错误"
return message
def find_root_team(cls, company_sn):
"""获取根团队sn"""
sql = "select sn from team_info where company_sn={} and root_team=1".format(
company_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchone()
ses.close()
if raw is not None:
return raw[0]
def get_sub_team_sn(cls, team_sn):
"""获取子团队sn"""
sql = "select sn from team_info where leader_sn in (select sn from employee_info where team_sn={})".format(
team_sn)
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
if len(raw) != 0:
return [x[0] for x in raw]
def all_user():
ses = db_module.sql_session()
col_str = ",".join(columns)
sql = "select {} from {} order by u_CreateTime desc".format(
col_str, table_name)
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
result = [dict(zip(columns, db_module.str_format(x))) for x in raw]
return result
def get_track_type():
"""获取跟踪类型"""
temp = ",".join(track_columns)
sql = "select sn,type_name from track_type_info"
ses = db_module.sql_session()
proxy = ses.execute(sql)
raw = proxy.fetchall()
ses.close()
result = {str(x[0]): x[1] for x in raw}
return result
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()
