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()