class UserDB: def __init__(self): self.db = DB() self.db.connect() self.user = "******" self.user_desc = [ ["user_no", "int(11)", "NO", "PRI", None, "auto_increment"], ["user_name", "varchar(15)", "NO", "", None, ""], ["openid", "char(28)", "NO", "", None, ""] ] def create_express_user(self, force=False): return self.db.create_table(self.user, self.user_desc, force) def check_express_user(self): return self.db.check_table(self.user, self.user_desc) def new_express_user(self, user_name, openid): if len(openid) != 28: return False insert_sql = "INSERT INTO %s (user_name,openid) VALUES ('%s','%s');" % (self.user, user_name, openid) self.db.execute(insert_sql) return True def update_express_user(self, user_name, openid): if len(openid) != 28: return False update_sql = "UPDATE %s SET user_name='%s' WHERE openid='%s';" % (self.user, user_name, openid) self.db.execute(update_sql) return True def select_user_name(self, openid): select_sql = "SELECT user_name FROM %s WHERE openid='%s';" % (self.user, openid) result = self.db.execute(select_sql) if result <= 0: return None return self.db.fetchone()[0] def select_user_no(self, openid): select_sql = "SELECT user_no FROM %s WHERE openid='%s';" % (self.user, openid) result = self.db.execute(select_sql) if result <= 0: return None return self.db.fetchone()[0] def select_user(self, user_no=None, openid=None, user_name=None): if user_no is not None: select_sql = "SELECT user_no,user_name,openid FROM %s WHERE user_no=%s;" % (self.user, user_no) elif openid is not None: select_sql = "SELECT user_no,user_name,openid FROM %s WHERE openid='%s';" % (self.user, openid) elif user_name is not None: select_sql = "SELECT user_no,user_name,openid FROM %s WHERE user_name='%s';" % (self.user, user_name) else: return None result = self.db.execute(select_sql) if result <= 0: return None db_r = self.db.fetchone() return {"user_no": db_r[0], "user_name": db_r[1], "openid": db_r[2]}
class CalcManager: def __init__(self): self.db = DB() self.calc = "calc_info" self.attribute = ("completed", "import", "account", "project_no", "person") self.attribute_ch = (u"计算完成时间", u"导入平台时间", u"导入账户", u"导入项目号", u"计算负责人") def new(self, data_no, calc_info, inputuser): if check_chinese(calc_info["person"], max_len=5) is False: return False, u"计算负责人只能是中文且长度不大于5" now_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S") insert_sql = "INSERT INTO %s (data_no," % self.calc values_sql = " VALUES ('%s'," % data_no for att in self.attribute: insert_sql += "%s," % att if att not in calc_info: return False, u"calc_info缺少必要参数%s" % att values_sql += "'%s'," % calc_info[att] values_sql += "'%s','%s')" % (now_time, inputuser) insert_sql += "inputtime,inputuser)%s" % values_sql self.db.execute(insert_sql) return True, "" def select(self, data_no): select_sql = "SELECT %s FROM %s WHERE data_no=%s;" % (",".join(self.attribute), self.calc, data_no) result = self.db.execute(select_sql) if result == 0: return False, u"数据编号不存在或没有相应记录" db_r = self.db.fetchone() calc_info = {} len_att = len(self.attribute) for index in range(len_att): calc_info[self.attribute[index]] = db_r[index] calc_info["completed"] = calc_info["completed"].strftime(DATE_FORMAT) return True, calc_info
class DataManager: def __init__(self): self.db = DB() self.data = "data_status" def new(self, inputuser): try: inputtime = datetime.now().strftime("%Y-%m-%d %H:%M:%S") insert_sql = "INSERT INTO %s (inputtime,inputuser) VALUES ('%s','%s');" % (self.data, inputtime, inputuser) self.db.execute("LOCK TABLES %s WRITE;" % self.data) insert_result = self.db.execute(insert_sql) if insert_result != 1: self.db.execute("UNLOCK TABLES;") return False, u"新建记录失败,请重试" self.db.execute("SELECT MAX(data_no) FROM %s;" % self.data) data_no = self.db.fetchone()[0] self.db.execute("UNLOCK TABLES;") return True, data_no except Exception as e: self.db.execute("UNLOCK TABLES;") error_message = "%s" % str(e.args) return False, error_message def update(self, data_no, status): try: update_sql = "UPDATE %s SET status=%s WHERE data_no=%s;" % (self.data, status, data_no) result = self.db.execute(update_sql) if result <= 0: return False, u"数据编号不存在" return True, "" except Exception as e: error_message = "%s" % str(e.args) print(error_message) return False, error_message def get(self, data_no=-1): select_sql = "SELECT data_no, status, inputtime FROM %s " % self.data if data_no != -1: select_sql += "WHERE data_no=%s " % data_no select_sql += "ORDER BY inputtime DESC;" result = self.db.execute(select_sql) data_info =[] for item in self.db.fetchall(): data_info.append({"data_no": item[0], "status": item[1], "inputtime": item[2]}) return data_info
class MarketManager: def __init__(self): self.db = DB() self.market = "market_info" self.attribute = ("person", "source", "diagnosis", "representation", "seq", "num", "panel", "arrivetime", "deadline") self.attribute_ch = (u"市场负责人", u"数据来源单位", u"临床诊断", u"临床表征", u"测序类型", u"样本数", u"Panel", u"数据达到时间", u"分析deadline") read = open("../Class/target.txt") target_content = read.read() read.close() self.target = target_content.split("\n") def new(self, data_no, market_info, inputuser): if check_chinese(market_info["source"], max_len=40) is False: return False, u"数据来源单位只能是中文且长度不大于40" if check_chinese(market_info["diagnosis"], max_len=40) is False: return False, u"临床诊断只能是中文且长度不大于40" now_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT INTO %s (data_no," % self.market values_sql = " VALUES ('%s'," % data_no for att in self.attribute: insert_sql += "%s," % att if att not in market_info: return False, u"upload_info缺少必要参数%s" % att values_sql += "'%s'," % market_info[att] values_sql += "'%s','%s')" % (now_time, inputuser) insert_sql += "inputtime,inputuser)%s" % values_sql self.db.execute(insert_sql) return True, "" def select(self, data_no): select_sql = "SELECT %s FROM %s WHERE data_no=%s;" % (",".join(self.attribute), self.market, data_no) result = self.db.execute(select_sql) if result == 0: return False, u"数据编号不存在或没有相应记录" db_r = self.db.fetchone() market_info = {} len_att = len(self.attribute) for index in range(len_att): market_info[self.attribute[index]] = db_r[index] market_info["arrivetime"] = market_info["arrivetime"].strftime(DATE_FORMAT) market_info["deadline"] = market_info["deadline"].strftime(DATE_FORMAT) return True, market_info
class IPManager: def __init__(self): self.db = DB() self.ip_info = "ip_info_s" def select_info_info(self, ip_value): if type(ip_value) != int: return False, "Bad ip value" select_sql = "SELECT ip_value_s,ip_value_e,info1,info2 FROM %s WHERE ip_value_s<=%s ORDER BY ip_value_s DESC LIMIT 1;" \ % (self.ip_info, ip_value) result = self.db.execute(select_sql) if result <= 0: return True, {"ip": ip_value, "info1": "", "info2": ""} s_ip, e_ip, info1, info2 = self.db.fetchone() if ip_value > e_ip: info1 = "" info2 = "" return True, {"ip": ip_value, "info1": info1, "info2": info2}
class UploadManager: def __init__(self): self.db = DB() self.upload = "upload_info" self.attribute = ("ossdir", "person", "completed") self.attribute_ch = (u"OSS上传目录", u"上传负责人", u"上传完成时间") def new(self, data_no, upload_info, inputuser): if check_chinese(upload_info["person"], max_len=5) is False: return False, u"上传负责人只能是中文且长度不大于5" now_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S") insert_sql = "INSERT INTO %s (data_no," % self.upload values_sql = " VALUES ('%s'," % data_no for att in self.attribute: insert_sql += "%s," % att if att not in upload_info: return False, u"upload_info缺少必要参数%s" % att values_sql += "'%s'," % upload_info[att] values_sql += "'%s','%s')" % (now_time, inputuser) insert_sql += "inputtime,inputuser)%s" % values_sql self.db.execute(insert_sql) return True, "" def select(self, data_no): select_sql = "SELECT %s FROM %s WHERE data_no=%s;" % (",".join(self.attribute), self.upload, data_no) result = self.db.execute(select_sql) if result == 0: return False, u"数据编号不存在或没有相应记录" db_r = self.db.fetchone() upload_info = {} len_att = len(self.attribute) for index in range(len_att): upload_info[self.attribute[index]] = db_r[index] upload_info["completed"] = upload_info["completed"].strftime(DATE_FORMAT) return True, upload_info
class UserDB: def __init__(self): self.db = DB() self.db.connect() self.user = "******" def new_express_user(self, user_name, openid): if len(openid) != 28: return False insert_sql = "INSERT INTO %s (user_name,openid) VALUES ('%s','%s');" % (self.user, user_name, openid) self.db.execute(insert_sql) return True def update_express_user(self, user_name, openid): if len(openid) != 28: return False update_sql = "UPDATE %s SET user_name='%s' WHERE openid='%s';" % (self.user, user_name, openid) self.db.execute(update_sql) return True def select_user(self, user_no=None, openid=None, user_name=None): select_sql = "SELECT user_no,user_name,openid FROM %s WHERE " % self.user if user_no is not None: select_sql += "user_no=%s;" % user_no elif openid is not None: select_sql += "openid='%s';" % openid elif user_name is not None: select_sql += "user_name='%s';" % user_name else: return None result = self.db.execute(select_sql) if result <= 0: return None db_r = self.db.fetchone() return {"user_no": db_r[0], "user_name": db_r[1], "openid": db_r[2]}
class TaskManager(object): def __init__(self, task_type): self.db = DB() self.register_task = "register_task" self.scheduler_status = "task_scheduler_status" self.task_type = task_type def register_new_task(self, task_no, user_name, reason, reason_desc): status_info = "%s1" % int(time()) args = dict(task_no=task_no, task_type=self.task_type, user_name=user_name, reason=reason, reason_desc=reason_desc, status_info=status_info) result = self.db.execute_insert(self.register_task, args=args, ignore=True) if result <= 0: return False, result return True, args def update_register_task(self, task_no, run_result=True): status_info = "|%s%s" % (int(time()), 1 if run_result is True else 0) update_sql = "UPDATE %s SET status_info=CONCAT(status_info, '%s') WHERE task_no=%s AND task_type=%s;" \ % (self.register_task, status_info, task_no, self.task_type) self.db.execute(update_sql) return True, "success" def select_register_task(self, task_no, user_name=None): cols = ["task_no", "task_type", "user_name", "reason", "reason_desc", "status_info"] if user_name is None: zero = 1 else: zero = 0 select_sql = "SELECT %s FROM %s WHERE task_no>=%s AND task_type=%s AND (user_name='%s' OR %s) " \ "ORDER BY task_no DESC;" \ % (",".join(cols), self.register_task, task_no, self.task_type, user_name, zero) self.db.execute(select_sql) db_r = self.db.fetchall() task_list = [] for item in db_r: task_info = {} for i in range(len(cols)): task_info[cols[i]] = item[i] task_list.append(task_info) return True, task_list def _new_task_status(self, task_status, user_name, reason_desc): update_time = int(time()) args = dict(task_type=self.task_type, task_status=task_status, user_name=user_name, reason_desc=reason_desc, update_time=update_time) result = self.db.execute_insert(self.scheduler_status, args=args) return True, result def update_scheduler_status(self, task_status, user_name, reason_desc): update_time = int(time()) update_value = dict(task_status="%s" % task_status, user_name=user_name, reason_desc=reason_desc, update_time=update_time) result = self.db.execute_update(self.scheduler_status, update_value, {"task_type": self.task_type}) if result <=0: return self._new_task_status(task_status, user_name, reason_desc) return True, result def select_scheduler_status(self): cols = ["task_status", "user_name", "reason_desc", "update_time"] result = self.db.execute_select(self.scheduler_status, {"task_type": self.task_type}, cols) info = {} if result <= 0: for col in cols: info[col] = None return True, info db_r = self.db.fetchone() for i in range(len(cols)): info[cols[i]] = db_r[i] return True, info
class HelpManager: def __init__(self): self.db = DB() self.api_part_info = "api_part_info" self.api_module = "api_module" self.api_info = "api_info" self.api_input = "api_input" self.api_output = "api_output" self.api_header = "api_header" self.predefine_header = "predefine_header" self.predefine_body = "predefine_body" self.api_body = "api_body" self.predefine_param = "predefine_param" self.api_care = "api_care" self.module_care = "module_care" self.send_message = "send_message" self.test_env = "test_env" self.api_stage_desc = [u"新建", u"修改中", u"已完成", u"待废弃", u"已废弃", u"已删除"] self.user = "******" def new_api_module(self, module_name, module_prefix, module_desc, module_part, module_env): module_name = module_name.strip(" ") if check_chinese_en(module_name, 1, 35) is False: return False, "Bad module_name." if check_path(module_prefix, 1, 35) is False: return False, "Bad module_prefix" if check_int(module_part, max_v=9999) is False: return False, "Bad module_part" if type(module_env) != list: return False, "Bad module_env" if len(module_env) not in range(1, 6): print(module_env) return False, "Bad module_env." module_desc = check_sql_character(module_desc)[:240] module_env_s = "" for env_no in module_env: if type(env_no) != int: return False, "Bad env_no" module_env_s += "%s|" % env_no insert_sql = "INSERT INTO %s (module_name,module_prefix,module_desc,module_part,module_env) " \ "VALUES ('%s','%s','%s',%s,'%s');" \ % (self.api_module, module_name, module_prefix, module_desc, module_part, module_env_s[:-1]) result = self.db.execute(insert_sql) if result != 1: return False, "sql execute result is %s " % result return True, "success" def update_api_module(self, module_no, module_name, module_prefix, module_desc, module_part, module_env): if check_chinese_en(module_name, 0, 35) is False: return False, "Bad module_name." if check_path(module_prefix, 0, 35) is False: return False, "Bad module_prefix" if check_int(module_part, max_v=9999) is False: return False, "Bad module_part" if type(module_env) != list: return False, "Bad module_env" if len(module_env) not in range(1, 6): print(module_env) return False, "Bad module_env." module_desc = check_sql_character(module_desc)[:240] module_env_s = "" for env_no in module_env: if type(env_no) != int: return False, "Bad env_no" module_env_s += "%s|" % env_no update_sql = "UPDATE %s SET module_name='%s',module_prefix='%s',module_desc='%s',module_part=%s,module_env='%s' " \ "WHERE module_no=%s;" \ % (self.api_module, module_name, module_prefix, module_desc, module_part, module_env_s[:-1], module_no) result = self.db.execute(update_sql) return True, "success" def del_api_module(self, module_no): delete_sql = "DELETE FROM %s WHERE module_no=%s;" % (self.api_module, module_no) self.db.execute(delete_sql) return True, "success" def new_api_info(self, module_no, api_title, api_path, api_method, api_desc): if type(module_no) != int: return False , "Bad module_no" if check_path(api_path) is False: return False, "Bad api_path" if api_path.endswith("/") is False: return False, u"api path should end with /" if check_http_method(api_method) is False: return False, "Bad api_method" api_title = check_sql_character(api_title) api_desc = check_sql_character(api_desc) if len(api_desc) < 1: return False, "Bad api_desc" api_no = uuid.uuid1().hex # 新建 api_info add_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT INTO %s (api_no,module_no,api_title,api_path,api_method,api_desc,add_time,update_time) " \ "VALUES('%s',%s,'%s','%s','%s','%s','%s','%s')" \ % (self.api_info, api_no, module_no, api_title, api_path, api_method, api_desc, add_time, add_time) result = self.db.execute(insert_sql) if result != 1: return False, "sql execute result is %s " % result return True, {"api_no": api_no} def update_api_info(self, api_no, module_no, api_title, api_path, api_method, api_desc): if len(api_no) != 32: return False, "Bad api_no" if type(module_no) != int: return False , "Bad module_no" if check_path(api_path) is False: return False, "Bad api_path" if api_path.endswith("/") is False: return False, u"api path should end with /" if check_http_method(api_method) is False: return False, "Bad api_method" api_title = check_sql_character(api_title) api_desc = check_sql_character(api_desc) if len(api_desc) < 1: return False, "Bad api_desc" # 更新 api_info update_time = datetime.now().strftime(TIME_FORMAT) update_sql = "UPDATE %s SET module_no=%s,api_title='%s',api_path='%s',api_method='%s',api_desc='%s',update_time='%s' " \ "WHERE api_no='%s'; " \ % (self.api_info, module_no, api_title, api_path, api_method, api_desc, update_time, api_no) result = self.db.execute(update_sql) return True, "success" def set_api_update(self, api_no): update_time = datetime.now().strftime(TIME_FORMAT) update_sql = "UPDATE %s SET update_time='%s' WHERE api_no='%s';" % (self.api_info, update_time, api_no) self.db.execute(update_sql) return True def set_api_stage(self, api_no, stage): if len(api_no) != 32: return False, "Bad api_no" if stage <= 0 or stage > 5: return False, "Bad stage" update_time = datetime.now().strftime(TIME_FORMAT) update_sql = "UPDATE %s SET update_time='%s',stage=%s WHERE api_no='%s';" \ % (self.api_info, update_time, stage, api_no) self.db.execute(update_sql) return True, "success" def new_test_env(self, env_name, env_address): insert_sql = "INSERT INTO %s VALUES ('%s','%s');" % (self.test_env, env_name, env_address) self.db.execute(insert_sql) return True, "success" def new_api_header(self, api_no, header_params): if len(api_no) != 32: return False, "Bad api_no" value_sql = "VALUES " new_result = [] for key, value in header_params.items(): if check_char_num_underline(key) is False: return False, "Bad header param %s" % key if "necessary" not in value or "desc" not in value: return False, "Bad header param %s, need necessary and desc" % key if value["necessary"] != 0 and value["necessary"] != 1: return False, "Bad header param %s, necessary must be 0 or 1" % key value["desc"] = value["desc"][:1000] param_desc = check_sql_character(value["desc"]) add_time = datetime.now().strftime(TIME_FORMAT) value_sql += "('%s','%s',%s,'%s','%s')" \ % (api_no, key, value["necessary"], value["desc"], add_time) necessary = True if value["necessary"] == 1 else False new_result.append({"api_no": api_no, "necessary": necessary, "param": key, "desc": param_desc, "add_time": add_time}) if len(value_sql) < 8: return True insert_sql = "INSERT INTO %s (api_no,param,necessary,param_desc, add_time) %s " \ "ON DUPLICATE KEY UPDATE necessary=VALUES(necessary),param_desc=VALUES(param_desc),add_time=VALUES(add_time)" \ % (self.api_header, value_sql) result = self.db.execute(insert_sql) self.set_api_update(api_no) return True, new_result def new_api_body(self, api_no, body_params): if len(api_no) != 32: return False, "Bad api_no" new_result = [] value_sql = "VALUES " for key, value in body_params.items(): if check_char_num_underline(key) is False: return False, "Bad body param %s" % key if "necessary" not in value or "desc" not in value or "type" not in value: return False, "Bad body param %s, need necessary type desc" % key if value["necessary"] != 0 and value["necessary"] != 1: return False, "Bad body param %s, necessary must be 0 or 1" % key if check_char(value["type"], 1, 20) is False: return False, "Bad body param %s, type must a-z" % key param_desc = check_sql_character(value["desc"])[:1000] add_time = datetime.now().strftime(TIME_FORMAT) value_sql += "('%s','%s',%s,'%s','%s','%s')" \ % (api_no, key, value["necessary"], value["type"], param_desc, add_time) necessary = True if value["necessary"] == 1 else False new_result.append({"api_no": api_no, "necessary": necessary, "param": key, "desc": param_desc, "type": value["type"], "add_time": add_time}) if len(value_sql) < 8: return True insert_sql = "INSERT INTO %s (api_no,param,necessary,type,param_desc,add_time) %s " \ "ON DUPLICATE KEY UPDATE necessary=VALUES(necessary),param_desc=VALUES(param_desc),type=VALUES(type)" \ % (self.api_body, value_sql) result = self.db.execute(insert_sql) self.set_api_update(api_no) return True, new_result def new_predefine_param(self, api_no, param, param_type, add_time=None): if len(api_no) != 32: return False, "Bad api_no" if param_type not in ("header", "body"): return False, "Bad param_type" if add_time is None: add_time = datetime.now().strftime(TIME_FORMAT) r_data = {"api_no": api_no, "param": param, "param_type": param_type, "add_time": add_time} result = self.db.execute_insert(self.predefine_param, r_data, ignore=True) r_data["result"] = result return True, r_data def new_api_input(self, api_no, input_examples): if len(api_no) != 32: return False, "Bad api_no" new_result = [] value_sql = "VALUES " for item in input_examples: if "desc" not in item or "example" not in item: return False, "input example need desc and example" input_desc = check_sql_character(item["desc"])[:550] input_example = check_sql_character(item["example"]) if len(input_desc) < 1: return False, "Bad input_desc" if len(input_example) < 1: return False, "Bad input_example" input_no = uuid.uuid1().hex add_time = datetime.now().strftime(TIME_FORMAT) value_sql += "('%s','%s','%s','%s','%s')" % (input_no,api_no, input_desc, input_example, add_time) new_result.append({"api_no": api_no, "input_no": input_no, "desc": input_desc, "example": input_example, "add_time": add_time}) if len(value_sql) < 8: return True insert_sql = "INSERT INTO %s (input_no,api_no,input_desc,input_example,add_time) %s" % (self.api_input, value_sql) result = self.db.execute(insert_sql) if result != 1: return False, "sql execute result is %s " % result self.set_api_update(api_no) return True, new_result def new_api_output(self, api_no, output_examples): if len(api_no) != 32: return False, "Bad api_no" new_result = [] value_sql = "VALUES " for item in output_examples: if "desc" not in item or "example" not in item: return False, "output example need desc and example" output_desc = check_sql_character(item["desc"])[:550] output_example = check_sql_character(item["example"]) if len(output_desc) < 1: return False, "Bad output_desc" if len(output_example) < 1: return False, "Bad output_example" output_no = uuid.uuid1().hex add_time = datetime.now().strftime(TIME_FORMAT) value_sql += "('%s','%s','%s','%s','%s')" % (output_no,api_no, output_desc, output_example, add_time) new_result.append({"api_no": api_no, "output_no": output_no, "desc": output_desc, "example": output_example, "add_time": add_time}) if len(value_sql) < 8: return True insert_sql = "INSERT INTO %s (output_no,api_no,output_desc,output_example,add_time) %s" \ % (self.api_output, value_sql) result = self.db.execute(insert_sql) if result != 1: return False, "sql execute result is %s " % result self.set_api_update(api_no) return True, new_result def new_api_care(self, api_no, user_name, care_level=2): if len(api_no) != 32: return False, "Bad api_no" care_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT INTO %s (api_no,user_name,care_time,level) VALUES('%s','%s','%s',%s)" \ " ON DUPLICATE KEY UPDATE care_time=VALUES(care_time);" \ % (self.api_care, api_no, user_name, care_time, care_level) result = self.db.execute(insert_sql) if result < 1: return False, "sql execute result is %s " % result return True, {"user_name": user_name, "api_no": api_no, "care_time": care_time} def new_module_care(self, module_no, user_name, care_level=2): if type(module_no) != int: return False, "Bad module_no" care_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT INTO %s (module_no,user_name,care_time,level) VALUES('%s','%s','%s',%s)" \ " ON DUPLICATE KEY UPDATE care_time=VALUES(care_time);" \ % (self.module_care, module_no, user_name, care_time, care_level) result = self.db.execute(insert_sql) if result < 1: return False, "sql execute result is %s " % result return True, {"user_name": user_name, "module_no": module_no, "care_time": care_time} def new_send_message(self, send_user, rec_user, content): content = check_sql_character(content) rec_user_s = ",".join(rec_user)[:500] send_time = int(time()) insert_sql = "INSERT INTO %s (send_user,rec_user,send_time,content) VALUES ('%s','%s',%s,'%s');" \ % (self.send_message, send_user, rec_user_s, send_time, content) self.db.execute(insert_sql) return True, "success" def get_test_env(self, env_no_list=None): if env_no_list is None: select_sql = "SELECT env_no,env_name,env_address FROM %s;" % self.test_env elif type(env_no_list) == list and len(env_no_list) >= 1 and len(env_no_list) <= 5: union_sql_list = [] for env_no in env_no_list: if type(env_no) != int: return False, "Bad env_no_list" part_select_sql = "SELECT env_no,env_name,env_address FROM %s WHERE env_no=%s" % (self.test_env, env_no) union_sql_list.append(part_select_sql) select_sql = " UNION ".join(union_sql_list) else: return False, "Bad env_no_list" self.db.execute(select_sql) db_result = self.db.fetchall() env_info = [] for item in db_result: env_info.append({"env_no": item[0], "env_name": item[1], "env_address": item[2]}) return True, env_info def get_part_list(self, user_name): select_sql = "SELECT part_no,part_name,part_desc,part_detail FROM %s;" % self.api_part_info self.db.execute(select_sql) part_list = [] for item in self.db.fetchall(): part_list.append({"part_no": item[0], "part_name": item[1], "part_desc": item[2], "part_detail": item[3]}) return part_list def get_part_api(self, user_name): part_list = self.get_part_list(user_name) for api_part in part_list: result, module_list= self.get_module_list(part_no=api_part["part_no"]) api_part["module_list"] = module_list return True, part_list def get_module_list(self, part_no=None): select_sql = "SELECT module_no,module_name,module_prefix,module_desc,module_part,module_env " \ "FROM %s WHERE module_part=%s;" % (self.api_module, part_no) self.db.execute(select_sql) module_list = [] for item in self.db.fetchall(): info = {"module_no": item[0], "module_name": item[1], "module_prefix": item[2], "module_desc": item[3], "module_part": item[4], "module_env": item[5]} module_list.append(info) return True, module_list def get_module_care_list(self, module_no): # 获得关注列表 select_sql = "SELECT module_no,c.user_name,care_time,nick_name,level,email FROM sys_user as su,%s as c " \ "WHERE su.user_name=c.user_name AND module_no='%s';" % (self.module_care, module_no) self.db.execute(select_sql) care_info = [] for item in self.db.fetchall(): care_info.append({"module_no": item[0], "user_name": item[1], "care_time": item[2].strftime(TIME_FORMAT), "nick_name": item[3], "level": item[4], "email": item[5]}) return care_info def get_api_basic_info(self, api_no): # get basic info basic_info_col = ("module_no", "api_no", "api_title", "api_path", "api_method", "api_desc", "add_time", "update_time", "module_name", "module_prefix", "module_desc", "module_env", "stage") select_sql = "SELECT m.%s FROM %s AS i, api_module AS m WHERE i.module_no=m.module_no AND api_no='%s';" \ % (",".join(basic_info_col), self.api_info, api_no) result = self.db.execute(select_sql) if result <= 0: return False, "Not Exist api_no" db_info = self.db.fetchone() basic_info = {} for i in range(len(db_info)): basic_info[basic_info_col[i]] = db_info[i] if basic_info["stage"] >= len(self.api_stage_desc) or basic_info["stage"] < 0: return False, "Not Exist api_no" basic_info["stage"] = self.api_stage_desc[basic_info["stage"]] basic_info["api_url"] = basic_info["module_prefix"].rstrip("/") + "/" + basic_info["api_path"].lstrip("/") basic_info["add_time"] = basic_info["add_time"].strftime(TIME_FORMAT) if basic_info["add_time"] is not None else "" basic_info["update_time"] = basic_info["update_time"].strftime(TIME_FORMAT) if basic_info["update_time"] is not None else "" return True, basic_info def get_api_output(self, api_no): # 获得返回示例 select_sql = "SELECT output_no,api_no,output_desc,output_example FROM %s WHERE api_no='%s' ORDER BY add_time;" \ % (self.api_output, api_no) self.db.execute(select_sql) output_info = [] for item in self.db.fetchall(): output_info.append({"output_no": item[0], "api_no": item[1], "output_desc": item[2], "output_example": item[3]}) return output_info def get_api_care_info(self, api_no): # 获得关注列表 select_sql = "SELECT api_no,c.user_name,care_time,nick_name,level,email FROM sys_user as su,%s as c " \ "WHERE su.user_name=c.user_name AND api_no='%s';" % (self.api_care, api_no) self.db.execute(select_sql) care_info = [] for item in self.db.fetchall(): care_info.append({"api_no": item[0], "user_name": item[1], "care_time": item[2].strftime(TIME_FORMAT), "nick_name": item[3], "level": item[4], "email": item[5]}) return care_info def get_api_info(self, api_no): if len(api_no) != 32: return False, "Bad api_no" # get basic info result, basic_info = self.get_api_basic_info(api_no) if result is False: return False, basic_info # 获得请求头部参数列表 select_sql = "SELECT api_no,param,necessary,param_desc FROM %s WHERE api_no='%s' ORDER BY add_time;" \ % (self.api_header, api_no) self.db.execute(select_sql) header_info = [] for item in self.db.fetchall(): necessary = True if item[2] == "\x01" else False header_info.append({"api_no": item[0], "param": item[1], "necessary": necessary, "param_desc": item[3]}) # 获得请求主体参数列表 select_sql = "SELECT api_no,param,necessary,type,param_desc FROM %s WHERE api_no='%s' ORDER BY add_time;" \ % (self.api_body, api_no) self.db.execute(select_sql) body_info = [] for item in self.db.fetchall(): necessary = True if item[2] == "\x01" else False body_info.append({"api_no": item[0], "param": item[1], "necessary": necessary, "type": item[3], "param_desc": item[4]}) # 获得预定义参数列表 select_sql = "SELECT param,param_type FROM %s WHERE api_no='%s' ORDER BY add_time;" % (self.predefine_param, api_no) self.db.execute(select_sql) predefine_param = {"header": [], "body": []} for item in self.db.fetchall(): if item[1] in predefine_param: predefine_param[item[1]].append(item[0]) # 获得预定义头部参数信息 select_sql = "SELECT param,necessary,param_desc FROM %s;" % self.predefine_header self.db.execute(select_sql) predefine_header = {} for item in self.db.fetchall(): necessary = True if item[1] == "\x01" else False predefine_header[item[0]] = {"param": item[0], "necessary": necessary, "param_desc": item[2]} # 获得预定义头部主体信息 select_sql = "SELECT param,necessary,type,param_desc FROM %s;" % self.predefine_body self.db.execute(select_sql) predefine_body = {} for item in self.db.fetchall(): necessary = True if item[1] == "\x01" else False predefine_body[item[0]] = {"param": item[0], "type": item[2], "necessary": necessary, "param_desc": item[3]} # 获得请求示例 select_sql = "SELECT input_no,api_no,input_desc,input_example FROM %s WHERE api_no='%s' ORDER BY add_time;" \ % (self.api_input, api_no) self.db.execute(select_sql) input_info = [] for item in self.db.fetchall(): input_info.append({"input_no": item[0], "api_no": item[1], "input_desc": item[2], "input_example": item[3]}) # 获得返回示例 output_info = self.get_api_output(api_no) # 获得关注列表 care_info = self.get_api_care_info(api_no) return True, {"basic_info": basic_info, "header_info": header_info, "body_info": body_info, "input_info": input_info, "output_info": output_info, "care_info": care_info, "predefine_param": predefine_param, "predefine_header": predefine_header, "predefine_body": predefine_body} def get_api_list(self, module_no): if type(module_no) != int: return False, "Bad module_no" select_sql = "SELECT api_no,module_no,api_title,api_path,api_method,api_desc,stage FROM %s " \ "WHERE module_no=%s AND stage<4 ORDER BY stage, api_path, api_method;" % (self.api_info, module_no) self.db.execute(select_sql) api_list = [] for item in self.db.fetchall(): if item[6] >= len(self.api_stage_desc) or item[6] < 0: continue api_stage = self.api_stage_desc[item[6]] api_list.append({"api_no": item[0], "module_no": item[1], "api_title": item[2], "api_path": item[3], "api_method": item[4], "api_desc": item[5], "stage": api_stage}) care_info = self.get_module_care_list(module_no) return True, {"api_list": api_list, "care_info": care_info, "module_info": {"module_no": module_no}} def del_api_header(self, api_no, param): if len(api_no) != 32: return False, "Bad api_no" delete_sql = "DELETE FROM %s WHERE api_no='%s' AND param='%s';" % (self.api_header, api_no, param) result = self.db.execute(delete_sql) self.set_api_update(api_no) return True, result def del_api_body(self, api_no, param): if len(api_no) != 32: return False, "Bad api_no" delete_sql = "DELETE FROM %s WHERE api_no='%s' AND param='%s';" % (self.api_body, api_no, param) result = self.db.execute(delete_sql) return True, result def del_predefine_param(self, api_no, param): if len(api_no) != 32: return False, "Bad api_no" where_value = {"api_no": api_no, "param": param} result = self.db.execute_delete(self.predefine_param, where_value) where_value["result"] = result return True, where_value def del_api_input(self, input_no): if len(input_no) != 32: return False, "Bad input_no" delete_sql = "DELETE FROM %s WHERE input_no='%s';" % (self.api_input, input_no) result = self.db.execute(delete_sql) return True, result def del_api_output(self, output_no): if len(output_no) != 32: return False, "Bad output_no" delete_sql = "DELETE FROM %s WHERE output_no='%s';" % (self.api_output, output_no) result = self.db.execute(delete_sql) return True, result def del_api_care(self, api_no, user_name): if len(api_no) != 32: return False, "Bad api_no" delete_sql = "DELETE FROM %s WHERE api_no='%s' AND user_name='%s' AND level <> 0;" % (self.api_care, api_no, user_name) result = self.db.execute(delete_sql) return True, result def del_module_care(self, module_no, user_name, level=2): if type(module_no) != int: return False, "Bad module_no" delete_sql = "DELETE FROM %s WHERE module_no='%s' AND user_name='%s' AND level=%s;" \ % (self.module_care, module_no, user_name, level) result = self.db.execute(delete_sql) return True, result def del_api_info(self, api_no, user_name): if len(api_no) != 32: return False, "Bad api_no" select_sql = "SELECT level FROM %s WHERE api_no='%s' AND user_name='%s' AND level=0;" \ % (self.api_care, api_no, user_name) result = self.db.execute(select_sql) if result <= 0: return False, "user can not delete this api" self.set_api_stage(api_no, 5) update_sql = "UPDATE %s SET level=3 WHERE api_no='%s' AND user_name='%s';" \ % (self.api_care, api_no, user_name) self.db.execute(update_sql) return True, result def del_api_other_info(self, api_no): delete_sql_format = "DELETE FROM %s WHERE api_no='" + api_no + "';" for t in (self.api_header, self.api_input, self.api_body, self.api_output): delete_sql = delete_sql_format % t self.db.execute(delete_sql) return True, "success"
class ControlManager: def __init__(self): self.db = DB() self.sys_user = "******" self.data = DataManager() self.market = MarketManager() self.market_attribute = self.market.attribute self.market_attribute_ch = self.market.attribute_ch self.market_target = self.market.target self.upload = UploadManager() self.upload_attribute = self.upload.attribute self.upload_attribute_ch = self.upload.attribute_ch self.calc = CalcManager() self.calc_attribute = self.calc.attribute self.calc_attribute_ch = self.calc.attribute_ch self.user = UserManager() self.user_role_desc = self.user.role_desc self.role_value = self.user.role_value self.dev = DevManager() self.api_help = HelpManager() self.api_status = StatusManager() self.bug = BugManager() self.ip = IPManager() self.release_man = ReleaseManager(release_dir) self.param_man = ParamFormatManager() self.pull_request_man = PullRequestManager() self.manger_email = ["*****@*****.**", "*****@*****.**"] self.jy_log = LogManager() def check_user_name_exist(self, user_name, role, check_user_name): if role & self.role_value["user_new"] <= 0: return False, u"用户无权限新建用户" return self.user.check_account_exist(user_name, check_user_name) def new_user(self, user_name, role, nick_name, creator, creator_role): if creator_role & self.role_value["user_new"] <= 0: return False, u"用户无权限新建用户" if creator_role | role > creator_role: return False, u"给新建用户赋予权限过高" return self.user.new(user_name, role, nick_name, creator) def change_password(self, user_name, old_password, new_password): return self.user.change_password(user_name, old_password, new_password) def send_code(self, user_name, password, tel): return self.user.send_code(user_name, password, tel) def bind_tel(self, user_name, password, tel, code): return self.user.bind_tel(user_name, password, tel, code) def get_my_user(self, user_name, role): if role & self.role_value["user_new"] <= 0: return False, u"用户无权限操作用户" return self.user.my_user(user_name) def update_my_user_role(self, role, user_name, my_user, my_user_role): if role & self.role_value["user_new"] <= 0: return False, u"用户无权限操作用户" if role & my_user_role != my_user_role: return False, u"赋予权限过高" return self.user.update_my_user_role(my_user_role, my_user, user_name) def add_my_user_role(self, role, user_name, add_role, add_user_list): if role & self.role_value["user_new"] <= 0: return False, u"用户无权限操作用户" if role & add_role != add_role: return False, u"增加权限过高" return self.user.add_role_my_users(add_role, add_user_list, user_name) def remove_my_user_role(self, role, user_name, remove_role, remove_user_list): if role & self.role_value["user_new"] <= 0: return False, u"用户无权限操作用户" if role & remove_role != remove_role: return False, u"移除权限过高" return self.user.remove_role_my_users(remove_role, remove_user_list, user_name) def get_role_user(self, role): return self.user.get_role_user(role) def get_data(self): return self.data.get() def new_data(self, role, inputuser): if (role & 1) <= 0: return False, u"您的权限不足" result, message = self.data.new(inputuser) return result, message def new_market(self, data_no, market_info, inputuser, role): if (role & 1) <= 0: return False, u"您的权限不足" data_info = self.data.get(data_no) if len(data_info) <= 0: return False, u"数据编号不存在" if data_info[0]["status"] != 0: return False, u"无修改权限" result, message = self.market.new(data_no, market_info, inputuser) if result is False: return False, message result, message = self.data.update(data_no, 1) if result is True: if inputuser != "upload": self.send_email(u"%s添加了市场记录" % inputuser, data_no, market_info, self.market_attribute, self.market_attribute_ch) return True, data_no def new_upload(self, data_no, upload_info, inputuser, role): if (role & 2) <= 0: return False, u"您的权限不足" data_info = self.data.get(data_no) if len(data_info) <= 0: return False, u"数据编号不存在" if data_info[0]["status"] != 1: return False, u"无修改权限" result, message = self.upload.new(data_no, upload_info, inputuser) if result is False: return False, message result, message = self.data.update(data_no, 2) if result is True: self.send_email(u"%s添加了上传记录" % inputuser, data_no, upload_info, self.upload_attribute, self.upload_attribute_ch) return True, "" def new_calc(self, data_no, calc_info, inputuser, role): if (role & 4) <= 0: return False, u"您的权限不足" data_info = self.data.get(data_no) if len(data_info) <= 0: return False, u"数据编号不存在" if data_info[0]["status"] != 2: return False, u"无修改权限" result, message = self.calc.new(data_no, calc_info, inputuser) if result is False: return False, message result, message = self.data.update(data_no, 3) if result is True: if inputuser != "calc": self.send_email(u"%s添加了计算记录" % inputuser, data_no, calc_info, self.calc_attribute, self.calc_attribute_ch) return True, "" def get_market(self, data_no, role): if (role & 1) <= 0: return False, u"您的权限不足" return self.market.select(data_no) def get_upload(self, data_no, role): if (role & 2) <= 0: return False, u"您的权限不足" return self.upload.select(data_no) def get_calc(self, data_no, role): if (role & 4) <= 0: return False, u"您的权限不足" return self.calc.select(data_no) # 针对开发者的应用 def show_operate_auth(self, role): if role & self.role_value["right_new"] < self.role_value["right_new"]: return False, u"您没有权限" return self.dev.get_operate_auth() def list_data_table(self, role): if role & self.role_value["table_look"] <= 0: return False, u"您没有权限" return self.dev.list_table() def get_table_info(self, table_name, role): if role & self.role_value["table_look"] <= 0: return False, u"您没有权限" return self.dev.get_table_info(table_name) def get_right_module(self, role): if role & self.role_value["right_look"] < self.role_value["right_look"]: return False, u"您没有权限" result, info = self.dev.get_right_module() return result, info def get_right_module_role(self, role, module_no): if role & self.role_value["right_look"] < self.role_value["right_look"]: return False, u"您没有权限" result, info = self.dev.get_right_module_role(module_no) return result, info def get_right_action_role(self, role, module_no): if role & self.role_value["right_look"] < self.role_value["right_look"]: return False, u"您没有权限" result, info = self.dev.get_right_action_role(module_no) return result, info def new_right_action(self, user_name, role, module_no, action_desc, min_role): if role & self.role_value["right_new"] < self.role_value["right_new"]: return False, u"您没有权限" result, info = self.dev.new_right_action(module_no, action_desc, min_role, user_name) return result, info def delete_right_action(self, user_name, role, action_no): if role & self.role_value["right_new"] < self.role_value["right_new"]: return False, u"您没有权限" result, info = self.dev.del_right_action(user_name, action_no) return result, info def backup_table(self, user_name, role, t_name, sql_path): return self.dev.backup_table(t_name, sql_path) def register_backup_task(self): return self.dev.register_backup_task() def send_email(self, sub, data_no, info, attribute, attribute_ch): print("strart send email to %s" % self.manger_email) content = sub + "<br>" content += u"数据编号 : %s<br>" % data_no att_len = len(attribute) for index in range(att_len): content += "%s : %s<br>" % (attribute_ch[index], info[attribute[index]]) for email in self.manger_email: my_email.send_mail_thread(email, sub, content) # 针对API HELP的应用 def get_part_api(self, user_name, role): if role & self.role_value["api_look"] <= 0: return False, u"您没有权限" return self.api_help.get_part_api(user_name=user_name) def get_module_list(self, role): if role & self.role_value["api_look"] <= 0: return False, u"您没有权限" return self.api_help.get_module_list() def get_test_env(self, role, env_no_list=None): if role & self.role_value["api_look"] <= 0: return False, u"您没有权限" return self.api_help.get_test_env(env_no_list) def new_api_module(self, role, module_name, module_prefix, module_desc, module_part, module_env): if role & self.role_value["api_module_new"] <= 0: return False, u"您没有权限" return self.api_help.new_api_module(module_name, module_prefix, module_desc, module_part, module_env) def update_api_module(self, role, module_no, module_name, module_prefix, module_desc, module_part, module_env): if role & self.role_value["api_module_new"] <= 0: return False, u"您没有权限" return self.api_help.update_api_module(module_no, module_name, module_prefix, module_desc, module_part, module_env) def delete_api_module(self, role, module_no): if role & self.role_value["api_module_new"] <= 0: return False, u"您没有权限" return self.api_help.del_api_module(module_no) def new_api_info(self, module_no, title, path, method, desc, user_name, role): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" result, data = self.api_help.new_api_info(module_no, title, path, method, desc) if result is True: self.api_help.new_api_care(data["api_no"], user_name, 0) api_no = data["api_no"] t = Thread(target=self._send_module_message, args=(user_name, module_no, api_no, title, method, desc)) t.start() return result, data def update_api_info(self, role, api_no, module_no, title, path, method, desc): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" result, data = self.api_help.update_api_info(api_no, module_no, title, path, method, desc) return result, data def get_api_info(self, api_no, role): if role & self.role_value["api_look"] <= 0: return False, u"您没有权限" result, api_info = self.api_help.get_api_info(api_no) if result is True: if role & self.role_value["api_new"] <= 0 and api_info["basic_info"]["stage"] == u'新建': return False, u"您没有权限" return result, api_info def add_header_param(self, user_name, api_no, param, necessary, desc, role): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" result, info = self.api_help.new_api_header(api_no, {param: {"necessary": necessary, "desc": desc}}) if result is True: self._send_api_update_message_thread(user_name, api_no, param) return result, info def add_predefine_header(self, user_name, api_no, param, param_type, role): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" result, info = self.api_help.new_predefine_param(api_no, param, param_type) if result is True: self._send_api_update_message_thread(user_name, api_no, param) return result, info def add_body_param(self, user_name, api_no, param, necessary, type, desc, role): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" result, info = self.api_help.new_api_body(api_no, {param: {"necessary": necessary, "type": type, "desc": desc}}) if result is True: self._send_api_update_message_thread(user_name, api_no, param) return result, info def add_input_example(self, user_name, api_no, example, desc, role): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" result, info = self.api_help.new_api_input(api_no, [{"desc": desc, "example": example}]) if result is True: self._send_api_update_message_thread(user_name, api_no, u"请求示例") return result, info def add_output_example(self, user_name, api_no, example, desc, role): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" result, info = self.api_help.new_api_output(api_no, [{"desc": desc, "example": example}]) if result is True: self._send_api_update_message_thread(user_name, api_no, u"返回示例") return result, info def add_care(self, api_no, user_name, role): if role & 8 <= 0: return False, u"您没有权限" return self.api_help.new_api_care(api_no, user_name) def add_module_care(self, user_name, role, module_no): if role & 8 <= 0: return False, u"您没有权限" return self.api_help.new_module_care(module_no, user_name) def get_api_list(self, module_no, role): if role & self.role_value["api_look"] <= 0: return False, u"您没有权限" result, api_list = self.api_help.get_api_list(module_no) if result is True and role & self.role_value["api_new"] <= 0: len_api = len(api_list["api_list"]) for i in range(len_api - 1, -1, -1): api_item = api_list["api_list"][i] if api_item["stage"] == u'新建': api_list["api_list"].remove(api_item) return result, api_list def delete_header(self, role, api_no, param): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" return self.api_help.del_api_header(api_no, param) def delete_predefine_param(self, role, api_no, param): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" return self.api_help.del_predefine_param(api_no, param) def delete_body(self, role, api_no, param): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" return self.api_help.del_api_body(api_no=api_no, param=param) def delete_input(self, input_no, role): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" return self.api_help.del_api_input(input_no) def delete_ouput(self, output_no, role): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" return self.api_help.del_api_output(output_no) def delete_care(self, api_no, user_name): return self.api_help.del_api_care(api_no, user_name) def delete_module_care(self, user_name, module_no): return self.api_help.del_module_care(module_no, user_name) def delete_api(self, api_no, user_name): return self.api_help.del_api_info(api_no, user_name) def set_api_status(self, user_name, role, api_no, stage): if role & self.role_value["api_new"] <= 0: return False, u"您没有权限" if stage == 2: # 必须至少一个返回示例 output_info = self.api_help.get_api_output(api_no) if len(output_info) <= 0: return False, u"请至少提交一个返回示例" result, info = self.api_help.set_api_stage(api_no, stage) return result, info # 针对API状态码的应用 def get_fun_info(self, role): if role & self.role_value["status_code_look"] <= 0: return False, u"您没有权限" return self.api_status.get_function_info() def get_status(self, role, *args, **kwargs): if role & self.role_value["status_code_look"] <= 0: return False, u"您没有权限" return self.api_status.get_status_code(*args, **kwargs) def get_error_type(self, role): if role & self.role_value["status_code_look"] <= 0: return False, u"您没有权限" return self.api_status.get_error_type() def new_service_module(self, user_name, role, service_title, service_desc): if role & self.role_value["status_code_module"] <= 0: return False, u"您没有权限" result, info = self.api_status.insert_service_module(service_title, service_desc) if result is False: return result, info service_id = info["service_id"] r, data = self.api_status.insert_function_module(service_id, u"公共功能模块", u"本服务模块所有功能模块可公共使用的错误状态码") if r is True: function_id = data["function_id"] error_info = [{"type_id": 0, "error_desc": u"访问系统正常,待用"}, {"type_id": 0, "error_desc": u"访问系统正常,将返回数据"}, {"type_id": 0, "error_desc": u"访问系统正常,仅返回成功信息"}, {"type_id": 0, "error_desc": u"访问系统正常,返回警告信息"}, {"type_id": 99, "error_desc": u"未经处理的异常,模块统一处理返回内部错误"}] r, data = self.api_status.new_mul_status_code(int(service_id), int(function_id), error_info, user_name) return result, info def new_function_module(self, user_name, role, service_id, function_title, function_desc): if role & self.role_value["status_code_module"] <= 0: return False, u"您没有权限" return self.api_status.insert_function_module(service_id, function_title, function_desc) def new_api_status(self, user_name, role, service_id, fun_id, type_id, error_id, error_desc): if role & self.role_value["status_code_new"] <= 0: return False, u"您没有权限" return self.api_status.new_status_code(service_id, fun_id, type_id, error_id, error_desc, user_name) def new_mul_api_status(self, user_name, role, service_id, fun_id, error_info): if role & self.role_value["status_code_new"] <= 0: return False, u"您没有权限" return self.api_status.new_mul_status_code(service_id, fun_id, error_info, user_name) def delete_api_status(self, user_name, role, status_code): if role & self.role_value["status_code_del"] < self.role_value["status_code_del"]: return False, u"您没有权限" return self.api_status.del_status_code(status_code) # 针对BUG的应用 def get_bug_list(self, role): if role & self.role_value["bug_look"] <= 0: return False, u"您没有权限" return self.bug.get_bug_list() def get_bug_statistic(self, role): if role & self.role_value["bug_look"] <= 0: return False, u"您没有权限" return self.bug.get_statistic() def get_bug_info(self, role, bug_no): if role & self.role_value["bug_look"] <= 0: return False, u"您没有权限" return self.bug.get_bug_info(bug_no) def new_bug(self, user_name, role, bug_title): if role & self.role_value["bug_new"] <= 0: return False, u"您没有权限" return self.bug.new_bug_info(bug_title, user_name) def add_bug_str_example(self, user_name, role, bug_no, content): if role & self.role_value["bug_new"] <= 0: return False, u"您没有权限" # 判断该bug是否是user_name提交的 select_sql = "SELECT submitter,bug_status FROM %s WHERE bug_no='%s';" % (self.bug.bug, bug_no) result = self.db.execute(select_sql) if result == 0: return False, u"BUG 不存在" submitter, bug_status = self.db.fetchone() if submitter != user_name: return False, u"您不能修改别人的BUG" if bug_status > 2: return False, u"BUG 已不能修改" return self.bug.new_bug_example(bug_no, 1, content) def add_bug_img_example(self, user_name, role, bug_no, path): if role & self.role_value["bug_new"] <= 0: return False, u"您没有权限" # 判断该bug是否是user_name提交的 select_sql = "SELECT submitter,bug_status FROM %s WHERE bug_no='%s';" % (self.bug.bug, bug_no) result = self.db.execute(select_sql) if result == 0: return False, u"BUG 不存在" submitter, bug_status = self.db.fetchone() if submitter != user_name: return False, u"您不能修改别人的BUG" if bug_status > 2: return False, u"BUG 已不能修改" return self.bug.new_bug_example(bug_no, 2, path) def _wx_send_bug(self, bug_no, user_name, type, link_desc): select_sql = "SELECT nick_name,wx_id,bug_title FROM %s as u,%s as b, %s as o " \ "WHERE u.user_name=o.user_name AND o.bug_no=b.bug_no " \ "AND type=%s AND u.user_name='%s' AND o.bug_no='%s';" \ % (self.user.user, self.bug.bug, self.bug.bug_owner, type, user_name, bug_no) result= self.db.execute(select_sql) if result != 0: nick_name, wx_id, bug_title = self.db.fetchone() if wx_id is not None: bug_url = "http://gene.ac/dev/bug/info?bug_no=%s" % bug_no title = u"%s, 您被标记为BUG %s" % (nick_name, link_desc) remark = u"请查看%s,如果是你的BUG,请尽快修复。" % bug_url pass def _wx_send_bug_thread(self, bug_no, user_name, type, link_desc): t = Thread(target=self._wx_send_bug, args=(bug_no, user_name, type, link_desc)) t.start() def add_bug_link(self, bug_no, user_name, role, link_user, link_type): if role & self.role_value["bug_new"] <= 0: return False, u"您没有权限" # 判断当前bug是否允许添加关联者 select_sql = "SELECT submitter,bug_status FROM %s WHERE bug_no='%s';" % (self.bug.bug, bug_no) result = self.db.execute(select_sql) if result == 0: return False, u"BUG 不存在" submitter, bug_status = self.db.fetchone() if bug_status > 2: return False, u"BUG 已不能修改" # 判断被链接者是否可以被链接 select_sql = "SELECT role FROM %s WHERE user_name='%s';" % (self.sys_user, link_user) result = self.db.execute(select_sql) if result == 0: return False, u"添加关联账户不存在" link_role = self.db.fetchone()[0] if link_role & self.role_value["bug_link"] <= 0: return False, u"添加关联账户无效" if link_type == "ys": if bug_status > 1: return False, u"BUG 状态已不允许添加疑似拥有者" return self._add_ys_link(bug_no, user_name, link_user) elif link_type == "owner": return self._add_owner_link(bug_no, user_name, link_user, submitter) elif link_type == "fix": return self._add_fix_link(bug_no, user_name, link_user, submitter) elif link_type == "cancel": return self._add_channel_link(bug_no, user_name, role, link_user, submitter) elif link_type == "design": return self._add_design_link(bug_no, user_name, role, link_user) else: return False, u"错误的请求" def delete_bug_link(self, bug_no, user_name, role, link_user, link_type): if role & self.role_value["bug_new"] <= 0: return False, u"您没有权限" # 判断当前bug是否允许删除关联者 select_sql = "SELECT submitter,bug_status FROM %s WHERE bug_no='%s';" % (self.bug.bug, bug_no) result = self.db.execute(select_sql) if result == 0: return False, u"BUG 不存在" submitter, bug_status = self.db.fetchone() if bug_status > 2: return False, u"BUG 已不能修改" if link_type == "ys": if bug_status > 1: return False, u"BUG 状态已不允许修改疑似拥有者" link_type_num = 1 elif link_type == "owner": link_type_num = 2 else: return False, u"错误的请求" # 仅当当前关联者超过一个时才可以删除 其他 select_sql = "SELECT user_name,adder FROM %s WHERE bug_no='%s' AND type=%s;" \ % (self.bug.bug_owner, bug_no, link_type_num) result = self.db.execute(select_sql) if result <= 1: return False, u"仅当关联者超过1个以后,才能删除" for item in self.db.fetchall(): if item[0] == link_user and item[1] == user_name: return self.bug.del_bug_link(bug_no, link_user, link_type_num, user_name) return False, u"您无权限删除" def _add_ys_link(self, bug_no, user_name, link_user): # 有new bug的权限均可添加疑似bug拥有者 result, info = self.bug.new_bug_link(bug_no, link_user, 1, user_name) if result is True: # 发送微信消息 pass return result, info def _add_owner_link(self, bug_no, user_name, link_user, submitter): # 判断操作者是否有权限操作 操作者可以是关联自己 或者 是bug的提交者 if user_name != link_user: # 判断提交者是否是bug提交者 if submitter != user_name: return False, u"您不能修改别人的BUG" result, info = self.bug.new_bug_link(bug_no, link_user, 2, user_name) if result is True: # 发送微信消息 pass return result, info def _add_fix_link(self, bug_no, user_name, link_user, submitter): # 只有BUG提交者可以标记为修复 if submitter != user_name: return False, u"您不能修改别人的BUG的状态" return self.bug.new_bug_link(bug_no, link_user, 3, user_name) def _add_channel_link(self, bug_no, user_name, role, link_user, submitter): # 只有bug提交者才 或者拥有bug_channel 权限的人可以操作 if submitter != user_name and role & self.role_value["bug_cancel"]: return False, u"您无权限修改该BUG的状态" return self.bug.new_bug_link(bug_no, link_user, 4, user_name) def _add_design_link(self, bug_no, user_name, role, link_user): # 拥有bug_channel 权限的人可以操作 if role & self.role_value["bug_cancel"]: return False, u"您无权限修改该BUG的状态" return self.bug.new_bug_link(bug_no, link_user, 5, user_name) # 发送API更新提醒 def _send_module_message(self, user_name, module_no, api_no, title, method, desc): care_info = self.api_help.get_module_care_list(module_no=module_no) rec_user = [] rec_email = [] for care_user in care_info: if care_user["email"] is None: continue rec_user.append("%s|%s" % (care_user["user_name"], care_user["email"])) rec_email.append(care_user["email"]) email_content_lines = [] email_content_lines.append(u"模块增加新的API") email_content_lines.append(u"API标题:%s" % title) email_content_lines.append(u"API访问方法:%s" % method) email_content_lines.append(u"API描述:%s" % desc) access_url = "http://dms.gene.ac/dev/api/info/?api_no=%s" % api_no email_content_lines.append(u"<a href='%s'>查看详情</a>" % access_url) email_content = "<br/>".join(email_content_lines) # 写入更新信息 self.api_help.new_send_message(user_name, rec_user, email_content) for email in rec_email: my_email.send_mail(email, u"模块增加新的API:%s" % title, email_content) def _send_api_update_message(self, user_name, api_no, param): result, api_info = self.api_help.get_api_basic_info(api_no) if result is False: return False # 判断添加api是否已完成 if api_info["stage"] != 2: return False care_info = self.api_help.get_api_care_info(api_no) rec_user = [] rec_email = [] for care_user in care_info: if care_user["email"] is None: continue rec_user.append("%s|%s" % (care_user["user_name"], care_user["email"])) rec_email.append(care_user["email"]) email_content_lines = [] email_content_lines.append(u"API更新了参数") email_content_lines.append(u"API标题:%s" % api_info["api_title"]) email_content_lines.append(u"API描述:%s" % api_info["api_desc"]) email_content_lines.append(u"更新的参数名称:%s" % param) access_url = "http://dms.gene.ac/dev/api/info/?api_no=%s" % api_no email_content_lines.append(u"<a href='%s'>查看详情</a>" % access_url) email_content = "<br/>".join(email_content_lines) # 写入更新信息 self.api_help.new_send_message(user_name, rec_user, email_content) for email in rec_email: my_email.send_mail(email, u"API:%s,更新了参数" % api_info["api_title"], email_content) return True def _send_api_update_message_thread(self, user_name, api_no, param): t = Thread(target=self._send_api_update_message, args=(user_name, api_no, param)) t.start() def _send_api_completed_message(self, user_name, api_no): result, api_info = self.api_help.get_api_basic_info(api_no) if result is False: return False care_info = self.api_help.get_api_care_info(api_no) rec_user = [] rec_email = [] for care_user in care_info: if care_user["email"] is None: continue rec_user.append("%s|%s" % (care_user["user_name"], care_user["email"])) rec_email.append(care_user["email"]) email_content_lines = [] email_content_lines.append(u"API文档完成") email_content_lines.append(u"API标题:%s" % api_info["api_title"]) email_content_lines.append(u"API描述:%s" % api_info["api_desc"]) access_url = "http://dms.gene.ac/dev/api/info/?api_no=%s" % api_no email_content_lines.append(u"<a href='%s'>查看详情</a>" % access_url) email_content = "<br/>".join(email_content_lines) # 写入更新信息 self.api_help.new_send_message(user_name, rec_user, email_content) for email in rec_email: my_email.send_mail(email, u"API:%s,文档已完成" % api_info["api_title"], email_content) return True def _send_api_completed_message_thread(self, user_name, api_no): t = Thread(target=self._send_api_completed_message, args=(user_name, api_no)) t.start() def test_send(self, user_name, content): result, user_info = self.user.get_user_info(user_name) if result is False: return False, "user exception" if user_info["email"] is None: return False, "user not config email" my_email.send_mail_thread(user_info["email"], u"晶云文档系统测试发送与接收邮件", content) return True, user_info["email"] # 针对查看晶云平台运行日志 def look_jy_log(self, user_name, role, start_time=None, end_time=None, level=None, search_url=None, search_account=None): if role & self.role_value["log_look"] <= 0: return False, u"您没有权限" result, info = self.jy_log.show_log(start_time=start_time, end_time=end_time, level=level, search_url=search_url, search_account=search_account) return result, info def new_login_server(self, server_ip, server_name, user_ip, user_name, login_time): return self.jy_log.insert_login_server(server_ip, server_name, user_ip, user_name, login_time) def get_login_info(self): return self.jy_log.select_login_log() def register_log_task(self): return self.jy_log.register_daily_task() def get_daily_log(self): return self.jy_log.select_daily_log() def get_one_log(self, user_name, role, log_no): if role & self.role_value["log_query"] <= 0: return False, "您没有权限" result, info = self.jy_log.select_log(log_no) return result, info def send_daily_log(self, template_html): result, user_list = self.user.get_role_user(self.role_value["log_receive"]) subject = u"%s运行日志" % datetime.now().strftime(DATE_FORMAT_STR) if result is True: for u in user_list: my_email.send_mail(u["email"], subject, template_html) def register_login_task(self): return self.jy_log.register_login_task() # 针对工具 def get_ip_info(self, ip_value): return self.ip.select_info_info(ip_value) # 针对发布 def get_task(self, user_name, user_role): if user_role & self.role_value["release_ih_N"] <= 0: return False, u"您没有权限" return self.release_man.select_release_task() def new_task(self, user_name, user_role, reason, restart_service, reason_desc): if user_role & self.role_value["release_ih_N"] <= 0 and user_name != "system": return False, u"您没有权限" if user_role & self.role_value["release_ih_V"] <= 0 and user_name != "system" and restart_service == 0: return False, u"您没有权限" if restart_service == 0: result, web_pull_requests = self.release_man.select_web_pull_request() if len(web_pull_requests) <= 0: return False, u"WEB无更新不可提交" result, api_pull_requests = self.release_man.select_api_pull_request() if len(api_pull_requests) <= 0: return False, u"API无更新不可提交" elif restart_service == 1: result, api_pull_requests = self.release_man.select_api_pull_request() if len(api_pull_requests) <= 0: return False, u"API无更新不可提交" elif restart_service == 2: result, web_pull_requests = self.release_man.select_web_pull_request() if len(web_pull_requests) <= 0: return False, u"WEB无更新不可提交" result, info = self.release_man.select_release_task(user_name=user_name) if result is False: return False, info elif len(info) >= 2: return False, u"今日已不再允许预约" return self.release_man.new_release_task(user_name, reason, restart_service, reason_desc) def update_task(self, release_no, run_result): return self.release_man.update_release_task(release_no, run_result) def release_ih(self): return self.release_man.release_ih() # 针对公共参数格式 def add_param_format(self, user_name, user_role, param, param_type, **kwargs): return self.param_man.new_param_format(user_name, param, param_type, **kwargs) def update_param_format(self, user_name, user_role, param, **kwargs): return self.param_man.update_param_format(user_name, param, **kwargs) def get_params_info(self, user_name, user_role): return self.param_man.select_param_format() # 针对pull request def add_pull_request(self, **kwargs): return self.pull_request_man.add_pull_request(**kwargs)
class UserManager: def __init__(self): self.db = DB() self.user = "******" self.default_password = "******" self._data_role_desc = {"module_desc": u"数据传输", "role_list": { "market": {"role_desc": u"市场", "role_value": 1}, "upload": {"role_desc": u"上传", "role_value": 2}, "calc": {"role_desc": u"计算", "role_value": 4} } } self._api_role_desc = {"module_desc": u"API文档", "role_list": { "api_look": {"role_desc": u"查看", "role_value": 8}, "api_new": {"role_desc": u"新建", "role_value": 16}, "api_module_new": {"role_desc": u"新建模块", "role_value": 8192 } } } self._table_role_desc = {"module_desc": u"数据表描述", "role_list": {"table_look": {"role_desc": u"查看", "role_value": 32} } } self._right_role_desc = {"module_desc": u"权限列表", "role_list": { "right_look": {"role_desc": u"查看", "role_value": 64}, "right_new": {"role_desc": u"新建", "role_value": 16384} } } self._user_role_desc = {"module_desc": u"操作用户", "role_list": {"user_new": {"role_desc": u"新建", "role_value": 128} } } self._bug_role_desc = {"module_desc": u"BUG操作", "role_list": { "bug_look": {"role_desc": u"查看", "role_value": 256}, "bug_new": {"role_desc": u"新建", "role_value": 512}, "bug_link": {"role_desc": u"被关联", "role_value": 1024}, "bug_cancel": {"role_desc": u"取消", "role_value": 2048 }, "bug_del": {"role_desc": u"删除", "role_value": 4096} } } self._status_code_role_desc = {"module_desc": u"API状态码", "role_list": { "status_code_look": {"role_desc": u"查看", "role_value": 32768}, "status_code_new": {"role_desc": u"新建", "role_value": 65536}, "status_code_del": {"role_desc": u"删除", "role_value": 131072}, "status_code_module": {"role_desc": u"新建模块", "role_value": 524288} } } self._log_role_desc = {"module_desc": u"晶云平台日志", "role_list": {"log_query": {"role_desc": u"查找", "role_value": 268435456}, "log_look": {"role_desc": u"查看", "role_value": 262144}, "log_receive": {"role_desc": u"接收", "role_value": 1048576} } } self._release_role_desc = {"module_desc": u"重新发布环境", "role_list": {"release_ih_N": {"role_desc": u"ih普通", "role_value": 2097152}, "release_ih_V": {"role_desc": u"ihVIP", "role_value": 4194304}, "release_ytj_N": {"role_desc": u"ytj普通", "role_value": 8388608}}} self._param_role_desc = {"module_desc": u"晶云参数", "role_list": {"param_look": {"role_desc": u"查看", "role_value": 16777216}, "param_new": {"role_desc": u"新建", "role_value": 33554432}, "param_update": {"role_desc": u"更新", "role_value": 67108864}, "param_del": {"role_desc": u"删除", "role_value": 134217728}}} self.role_desc = {"data": self._data_role_desc, "api": self._api_role_desc, "table": self._table_role_desc, "right": self._right_role_desc, "user": self._user_role_desc, "bug": self._bug_role_desc, "status_code": self._status_code_role_desc, "log": self._log_role_desc, "release": self._release_role_desc, "param": self._param_role_desc} self.__init_role__() def __init_role__(self): self.role_value = {} for key, role_module in self.role_desc.items(): for role, value in role_module["role_list"].items(): self.role_value[role] = value["role_value"] def new(self, user_name, role, nick_name, creator): if check_user(user_name, 1, 15) is False: return False, u"用户名只能由字母数字和下划线组成且长度不大于20" select_sql = "SELECT role FROM %s WHERE user_name='%s';" % (self.user, user_name) result = self.db.execute(select_sql) if result > 0: return False, u"用户名已存在" add_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT INTO %s (user_name,role,nick_name,creator,add_time) " \ "VALUES ('%s',%s,'%s','%s','%s');" \ % (self.user, user_name, role, nick_name, creator, add_time) self.db.execute(insert_sql) return True, user_name def check(self, user_name, password): check_url = "%s/confirm/" % jy_auth_host try: res = requests.post(check_url, json={"account": user_name, "password": password}) except requests.ConnectionError as ce: return False, u"暂时无法登录,请稍后重试" r = res.json() if r["status"] != 1: return False, r["message"] select_sql = "SELECT user_name,role FROM %s WHERE user_name='%s';" % (self.user, r["data"]["account"]) result = self.db.execute(select_sql) if result <= 0: r["data"]["role"] = 0 return True, r["data"] db_r = self.db.fetchone() role = db_r[1] r["data"]["role"] = role return True, r["data"] def check_vip(self, user_name): if user_name != "system": return False, 0 role = self.role_value["param_look"] return True, {"account": user_name, "role": role} def check_account_exist(self, user_name, check_name): if check_account_format(check_name) is False: return False, u"账户名仅允许数字和字母,下划线(_),而且必须以字母开头,用户名长度不得低于3位,不得高于20位" select_sql = "SELECT creator FROM %s WHERE user_name='%s';" % (self.user, check_name) result = self.db.execute(select_sql) if result > 0: if self.db.fetchone()[0] == user_name: return False, u"您已注册过该用户" return False, u"该用户已被他人注册" check_url = "%s/account/" % jy_auth_host try: res = requests.post(check_url, json={"list_account": [check_name]}) except requests.ConnectionError as ce: return False, u"无法检测账户 ,请稍后重试" r = res.json() if r["status"] != 1: return False, r["message"] if len(r["data"]) == 1 and r["data"][0].lower() == check_name.lower(): return True, r["data"][0] return False, u"账户名不存在" def change_password(self, user_name, old_password, new_password): change_url = "%s/password/" % jy_auth_host try: res = requests.put(change_url, json={"account": user_name, "password": old_password, "new_password": new_password}) except requests.ConnectionError as ce: return False, u"暂时无法更改密码,请稍后重试" r = res.json() if r["status"] != 2: return False, r["message"] return True, u"更新成功" def send_code(self, user_name, password, tel): change_url = "%s/code/bind/" % jy_auth_host try: res = requests.post(change_url, json={"account": user_name, "password": password, "tel": tel}) except requests.ConnectionError as ce: return False, u"暂时无法发送,请稍后重试" r = res.json() if r["status"] == 2: return True, u"发送成功" if r["status"] == 10701: return False, u"请求频率过高,请稍后重试" if r["status"] == 10402: return False, u"手机号已被他人绑定,请更改还手机号" if r["status"] == 10801: return False, u"请求超过限制,请稍后重试" return False, r["message"] def bind_tel(self, user_name, password, tel, code): change_url = "%s/tel/" % jy_auth_host try: res = requests.post(change_url, json={"account": user_name, "password": password, "tel": tel, "code": code}) except requests.ConnectionError as ce: return False, u"暂时绑定,请稍后重试" r = res.json() if r["status"] == 2: return True, u"绑定成功" if r["status"] == 10404: return False, u"验证码不正确,请重新输入" if r["status"] == 10405: return False, u"验证码已过期,请重新获得" if r["status"] == 10402: return False, u"手机号已被他人绑定,请更改还手机号" return False, r["message"] def clear_password(self, user_name, creator): update_sql = "UPDATE %s SET password=null WHERE user_name='%s' AND creator='%s';" % (self.user, user_name, creator) self.db.execute(update_sql) return True, u"重置成功" def get_role_user(self, role): cols = ["user_name", "role", "nick_name", "wx_id", "creator", "add_time", "email"] select_sql = "SELECT %s FROM %s WHERE role & %s > 0;" \ % (",".join(cols), self.user, role) self.db.execute(select_sql) user_list = [] for item in self.db.fetchall(): info = {} for i in range(len(cols)): info[cols[i]] = item[i] info["add_time"] = info["add_time"].strftime(TIME_FORMAT) user_list.append(info) return True, user_list def my_user(self, user_name): select_sql = "SELECT user_name,role,nick_name,wx_id,creator,add_time FROM %s WHERE creator='%s';" \ % (self.user, user_name) self.db.execute(select_sql) user_list = [] for item in self.db.fetchall(): user_list.append({"user_name": item[0], "role": item[1], "nick_name": item[2], "wx_id": item[3], "creator": item[4], "add_time": item[5].strftime(TIME_FORMAT)}) return True, user_list def get_user_info(self, user_name): select_sql = "SELECT user_name,role,nick_name,wx_id,creator,add_time,email FROM %s WHERE user_name='%s';" \ % (self.user, user_name) result = self.db.execute(select_sql) if result <= 0: return False, "user not exist" item = self.db.fetchone() user_info = {"user_name": item[0], "role": item[1], "nick_name": item[2], "wx_id": item[3], "creator": item[4], "add_time": item[5], "email": item[6]} return True, user_info def update_my_user_role(self, role, user_name, my_name): if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=%s WHERE user_name='%s' AND creator='%s';" \ % (self.user, role, user_name, my_name) self.db.execute(update_sql) return True, "success" def _add_role_my_user(self, role, user_name, my_name): if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=role | %s WHERE user_name='%s' AND creator='%s';" \ % (self.user, role, user_name, my_name) self.db.execute(update_sql) return True, "success" def add_role_my_users(self, role, user_names, my_name): if type(user_names) != list: return "Bad user_names" if len(user_names) == 0: return True, "no update" if len(user_names) == 1: return self.add_role_my_user(role, user_names, my_name) if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=role | %s WHERE creator='%s' AND user_name in ('%s');" \ % (self.user, role, "','".join(user_names), my_name) self.db.execute(update_sql) return True, "success" def _remove_role_my_user(self, role, user_name, my_name): if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=role & ~%s WHERE user_name='%s' AND creator='%s';" \ % (self.user, role, user_name, my_name) self.db.execute(update_sql) return True, "success" def remove_role_my_users(self, role, user_names, my_name): if type(user_names) != list: return "Bad user_names" if len(user_names) == 0: return True, "no update" if len(user_names) == 1: return self.add_role_my_user(role, user_names, my_name) if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=role & ~%s WHERE creator='%s' AND user_name in ('%s');" \ % (self.user, role, "','".join(user_names), my_name) self.db.execute(update_sql) return True, "success"
class ItemManager(object): def __init__(self): self.db = DB() self.t_item = "billing_item" def insert_item(self, project_no, item_no, item_name, unit_price, member_price): kwargs = dict(project_no=project_no, item_no=item_no, item_name=item_name, billing_method=1, billing_unit=1, unit_price=unit_price, member_price=member_price) l = self.db.execute_insert(self.t_item, kwargs=kwargs, ignore=True) if l <= 0: return False, l return True, kwargs def new_item(self, project_no, item_name, unit_price=0, member_price=None, basic_item=None): unit_price *= 100 if member_price is None: member_price = unit_price else: member_price *= 100 where_value = dict(project_no=project_no) if basic_item is None: self.db.execute_select(self.t_item, where_value=where_value, cols=["MAX(item_no)"]) max_no = self.db.fetchone()[0] if max_no is None: item_no = 100 else: item_no = (max_no / 100 + 1) * 100 else: basic_item = basic_item / 100 * 100 next_basic_item = basic_item + 100 where_cond = [ "item_no >= %s" % basic_item, "item_no < %s" % next_basic_item ] self.db.execute_select(self.t_item, where_value=where_value, cols=["MAX(item_no)"], where_cond=where_cond) max_no = self.db.fetchone()[0] if max_no is None: return False, "无效的主分类" item_no = max_no + 1 if item_no >= next_basic_item: return False, "主分类下最多添加99个子分类" return self.insert_item(project_no, item_no, item_name, unit_price, member_price) def select_item(self, project_no): cols = [ "item_no", "item_name", "billing_method", "billing_unit", "unit_price", "member_price" ] db_items = self.db.execute_select( self.t_item, where_value={"project_no": project_no}, cols=cols, package=True) for item in db_items: item["unit_price"] /= 100.00 item["member_price"] /= 100.00 return True, db_items
class ExpressDB: def __init__(self): self.db = DB() self.db.connect() self.wx = WxManager() self.uDB = UserDB() self.completed_express = "completed_express" self.transport_express = "transport_express" self.history_express = "history_express" self.listen_express = "listen_express" self.pre_listen = "pre_listen" self.express_com = "express_com" def new_express_record(self, com_code, waybill_num, recodes, user_no, completed=False): if len(recodes) <= 0: return True insert_sql = "INSERT INTO %s (com_code,waybill_num,sign_time,sign_info,user_no,add_time) VALUES " \ % (self.completed_express if completed is True else self.transport_express) now_time = datetime.now().strftime(TIME_FORMAT) for recode in recodes: insert_sql += "('%s','%s','%s','%s',%s,'%s')," % (com_code, waybill_num, recode["time"], recode["info"], user_no, now_time) insert_sql = insert_sql[:-1] + ";" self.db.execute(insert_sql) return True def new_history_record(self, listen_no, com_code, waybill_num, remark, user_no): now_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT INTO %s (listen_no,com_code, waybill_num,completed_time,remark,user_no) " \ "VALUES (%s,'%s','%s','%s','%s', %s);" \ % (self.history_express, listen_no, com_code, waybill_num, now_time, remark, user_no) self.db.execute(insert_sql) return True def del_express_record(self, com_code, waybill_num, user_no): del_sql = "DELETE FROM %s WHERE com_code='%s' AND waybill_num='%s' AND user_no=%s;" % (self.transport_express, com_code, waybill_num, user_no) self.db.execute(del_sql) return True def new_listen_record(self, com_code, waybill_num, remark, user_no): now_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT INTO %s (com_code, waybill_num,update_time,query_time,remark,user_no) " \ "VALUES ('%s','%s','%s','%s','%s', %s);" \ % (self.listen_express, com_code, waybill_num, now_time, now_time, remark, user_no) self.db.execute(insert_sql) return True def update_listen_record(self, com_code, waybill_num, user_no, update=False, query=False): if update is False and query is False: return True update_sql = "UPDATE %s SET " % self.listen_express now_time = datetime.now().strftime(TIME_FORMAT) if update is True: update_sql += "update_time='%s'," % now_time if query is True: update_sql += "query_time='%s'," % now_time update_sql = update_sql[:-1] + " WHERE com_code='%s' AND waybill_num='%s' AND user_no=%s;" % (com_code, waybill_num, user_no) self.db.execute(update_sql) return True def del_listen_record(self, com_code, waybill_num, user_no): del_sql = "DELETE FROM %s WHERE com_code='%s' AND waybill_num='%s' AND user_no=%s;" % (self.listen_express, com_code, waybill_num, user_no) self.db.execute(del_sql) return True def select_listen_record(self, user_no, listen_no=None): select_sql = "SELECT listen_no,l.com_code,waybill_num,remark,com_name FROM %s AS l,%s AS c WHERE user_no=%s " \ "AND l.com_code=c.com_code" % (self.listen_express, self.express_com, user_no) if listen_no is not None: select_sql += " AND listen_no=%s" % listen_no select_sql += ";" result = self.db.execute(select_sql) listen_info = [] for item in self.db.fetchall(): listen_info.append({"listen_no": item[0], "com_code": item[1], "waybill_num": item[2], "remark": item[3], "com_name": item[4]}) return listen_info def check_listen_record(self, com_code, waybill_num, user_no): select_sql = "SELECT listen_no FROM %s WHERE com_code='%s' AND waybill_num='%s' AND user_no=%s;" % (self.listen_express, com_code, waybill_num, user_no) result = self.db.execute(select_sql) if result > 0: return True return False def new_pre_listen(self, listen_key, com_code, waybill_num, remark, user_no, query_result): insert_value = {"listen_key": listen_key, "com_code": com_code, "waybill_num": waybill_num, "remark": remark, "user_no": user_no, "insert_time": datetime.now().strftime(TIME_FORMAT), "query_result": json.dumps(query_result)} self.db.execute_insert(self.pre_listen, args=insert_value) return True def select_pre_listen(self, listen_key, user_no): select_sql = "SELECT p.com_code,waybill_num,remark,query_result,com_name FROM %s AS p,%s AS c " \ "WHERE p.com_code=c.com_code AND listen_key='%s' AND user_no=%s;"\ % (self.pre_listen, self.express_com, listen_key, user_no) result = self.db.execute(select_sql) if result <= 0: return None db_r = self.db.fetchone() return {"com_code": db_r[0], "waybill_num": db_r[1], "remark": db_r[2], "query_result": db_r[3], "com_name": db_r[4]} def select_record_info(self, user_no, com_code, waybill_num): select_sql = "SELECT sign_time,sign_info,add_time FROM %s WHERE com_code='%s' AND waybill_num='%s' AND user_no=%s;" \ % (self.transport_express, com_code, waybill_num, user_no) self.db.execute(select_sql) records = [] for item in self.db.fetchall(): records.append({"time": item[0].strftime(TIME_FORMAT), "info": item[1], "add_time": item[2].strftime(TIME_FORMAT)}) return records def del_pre_listen(self, listen_key, user_no): select_sql = "DELETE FROM %s WHERE listen_key='%s' AND user_no='%s';" % (self.pre_listen, listen_key, user_no) result = self.db.execute(select_sql) if result <= 0: return False return True def select_com(self, com="", like=True): try: if com == "": select_sql = "SELECT com_name,com_code FROM express_com;" elif like is True: select_sql = "SELECT com_name,com_code FROM express_com WHERE com_name LIKE '%%%s%%' " \ "OR com_code LIKE '%%%s%%';" % (com, com) else: select_sql = "SELECT com_name,com_code FROM express_com WHERE com_code='%s';" % com self.db.execute(select_sql) com_info = [] for item in self.db.fetchall(): com_info.append({"com_name": item[0], "com_code": item[1]}) return com_info except Exception as e: print(e.args) return [] def send_wx(self, user_name, openid, status, com_code, waybill, remark, records): # 获得快递公司名称 com_name = self.select_com(com_code, False)[0]["com_name"] part_records = [] len_info = len(records) for index in range(0, 3): if 3 - index > len_info: part_records.append({"time": "", "info": ""}) continue part_records.append(records[len_info + index - 3]) self.wx.send_express_template(user_name, openid, status, com_name, com_code, waybill, remark, part_records) def loop_query(self): eq = ExpressQuery() # 最后最晚查询过的一条记录 select_sql = "SELECT com_code,waybill_num,query_time,update_time,user_no,remark,listen_no FROM listen_express WHERE query_time = (SELECT MIN(query_time) FROM listen_express);" result = self.db.execute(select_sql) if result <= 0: print("%s No Listen Record." % datetime.now().strftime(TIME_FORMAT)) return record = self.db.fetchone() if record[0] is None: print("%s No Listen Record" % datetime.now().strftime(TIME_FORMAT)) return com_code = record[0] waybill_num = record[1] update_time = record[3] user_no = record[4] remark = record[5] listen_no = record[6] user_info = self.uDB.select_user(user_no) openid = user_info["openid"] user_name = user_info["user_name"] print("%s Start Handle %s %s" % (datetime.now().strftime(TIME_FORMAT), com_code, waybill_num)) # 查询现在快递状态 query_result = eq.query(com_code, waybill_num) if query_result["completed"] is True: print("%s %s %s completed" % (datetime.now().strftime(TIME_FORMAT), com_code, waybill_num)) # 通知用户完成 self.send_wx(user_name, openid, "completed", com_code, waybill_num, remark, query_result["express_info"]) # 删除transport_express中对应的记录 self.del_express_record(com_code, waybill_num, user_no) # 删除listen_express中对应的记录 self.del_listen_record(com_code, waybill_num, user_no) # 将全部记录记入completed_express self.new_express_record(com_code, waybill_num, query_result["express_info"], user_no, True) # 将监听记录插入history_express self.new_history_record(listen_no, com_code, waybill_num, remark, user_no) return express_info = query_result["express_info"] if len(express_info) <= 0: return # 查询数据库中已有记录进行比对 select_sql = "SELECT MAX(sign_time) FROM transport_express WHERE com_code='%s' AND waybill_num='%s' AND user_no=%s;" % (com_code, waybill_num, user_no) result = self.db.execute(select_sql) max_sign_time = None if result > 0: max_sign_time = self.db.fetchone()[0] if max_sign_time is not None and max_sign_time >= datetime.strptime(express_info[-1]["time"], TIME_FORMAT): # 没有更新的快递信息 # 判断是否超过5天没有更新记录 if (datetime.now() - update_time).days >= 5: print("%s Long time no info" % datetime.now().strftime(TIME_FORMAT)) # 通知用户 self.send_wx(user_name, openid, "exception", com_code, waybill_num, remark, express_info) # 删除transport_express中对应的记录 self.del_express_record(com_code, waybill_num, user_no) # 删除listen_express中对应的记录 self.del_listen_record(com_code, waybill_num, user_no) else: # 更新 query_time self.update_listen_record(com_code, waybill_num, user_no, False, True) else: print("%s %s %s has new info." % (datetime.now().strftime(TIME_FORMAT), com_code, waybill_num)) # 通知用户 self.send_wx(user_name, openid, "transport", com_code, waybill_num, remark, express_info) # 添加运输记录 add_record = [] for record in express_info: if max_sign_time is None or max_sign_time < datetime.strptime(record["time"], TIME_FORMAT): add_record.append(record) self.new_express_record(com_code, waybill_num, add_record, user_no, False) # 更新update_time query_time self.update_listen_record(com_code, waybill_num, user_no, True, True)
class UserManager: def __init__(self): self.db = DB() self.user = "******" self._salt_password = "******" self.default_password = "******" self._data_role_desc = { "module_desc": u"数据传输", "role_list": { "market": { "role_desc": u"市场", "role_value": 1 }, "upload": { "role_desc": u"上传", "role_value": 2 }, "calc": { "role_desc": u"计算", "role_value": 4 } } } self._api_role_desc = { "module_desc": u"API文档", "role_list": { "api_look": { "role_desc": u"查看", "role_value": 8 }, "api_new": { "role_desc": u"新建", "role_value": 16 }, "api_module_new": { "role_desc": u"新建模块", "role_value": 8192 } } } self._table_role_desc = { "module_desc": u"数据表描述", "role_list": { "table_look": { "role_desc": u"查看", "role_value": 32 } } } self._right_role_desc = { "module_desc": u"权限列表", "role_list": { "right_look": { "role_desc": u"查看", "role_value": 64 }, "right_new": { "role_desc": u"新建", "role_value": 16384 } } } self._user_role_desc = { "module_desc": u"操作用户", "role_list": { "user_new": { "role_desc": u"新建", "role_value": 128 } } } self._bug_role_desc = { "module_desc": u"BUG操作", "role_list": { "bug_look": { "role_desc": u"查看", "role_value": 256 }, "bug_new": { "role_desc": u"新建", "role_value": 512 }, "bug_link": { "role_desc": u"被关联", "role_value": 1024 }, "bug_cancel": { "role_desc": u"取消", "role_value": 2048 }, "bug_del": { "role_desc": u"删除", "role_value": 4096 } } } self._status_code_role_desc = { "module_desc": u"API状态码", "role_list": { "status_code_look": { "role_desc": u"查看", "role_value": 32768 }, "status_code_new": { "role_desc": u"新建", "role_value": 65536 }, "status_code_del": { "role_desc": u"删除", "role_value": 131072 }, "status_code_module": { "role_desc": u"新建模块", "role_value": 524288 } } } self._log_role_desc = { "module_desc": u"晶云平台日志", "role_list": { "log_look": { "role_desc": u"查看", "role_value": 262144 }, "log_receive": { "role_desc": u"接收", "role_value": 1048576 } } } self._release_role_desc = { "module_desc": u"重新发布环境", "role_list": { "release_ih_N": { "role_desc": u"ih普通", "role_value": 2097152 }, "release_ih_V": { "role_desc": u"ihVIP", "role_value": 4194304 }, "release_ytj_N": { "role_desc": u"ytj普通", "role_value": 8388608 } } } self._param_role_desc = { "module_desc": u"晶云参数", "role_list": { "param_look": { "role_desc": u"查看", "role_value": 16777216 }, "param_new": { "role_desc": u"新建", "role_value": 33554432 }, "param_update": { "role_desc": u"更新", "role_value": 67108864 }, "param_del": { "role_desc": u"删除", "role_value": 134217728 } } } self.role_desc = { "data": self._data_role_desc, "api": self._api_role_desc, "table": self._table_role_desc, "right": self._right_role_desc, "user": self._user_role_desc, "bug": self._bug_role_desc, "status_code": self._status_code_role_desc, "log": self._log_role_desc, "release": self._release_role_desc, "param": self._param_role_desc } self.__init_role__() def __init_role__(self): self.role_value = {} for key, role_module in self.role_desc.items(): for role, value in role_module["role_list"].items(): self.role_value[role] = value["role_value"] def _md5_hash_password(self, account, password): md5_password = _md5_hash(account + password + account) return (md5_password + self._salt_password).upper() def _password_check(self, password, db_password, account): if len(password) <= 20: if len(db_password) == 66: if check_password_hash( db_password, self._md5_hash_password( account, password)) is True: return True return False def insert_user(self, user_name, password, tel=None): en_password = generate_password_hash( self._md5_hash_password(user_name, password)) result = self.db.execute_insert(self.user, kwargs={ "user_name": user_name, "password": en_password, "tel": tel }) return result def check(self, user_name, password): cols = ["user_name", "nick_name", "password", "tel", "role"] result = self.db.execute_select(self.user, where_value={"user_name": user_name}, cols=cols) if result <= 0: return False, "账户不存在" user_name, nick_name, db_password, tel, role = self.db.fetchone() if self._password_check(password, db_password, user_name) is False: return False, "密码不正确" u_info = { "user_name": user_name, "nick_name": nick_name, "tel": tel, "role": role } return True, u_info def check_vip(self, user_name): if user_name != "system": return False, 0 role = self.role_value["param_look"] return True, {"account": user_name, "role": role} def check_account_exist(self, user_name, check_name): if check_account_format(check_name) is False: return False, u"账户名仅允许数字和字母,下划线(_),而且必须以字母开头,用户名长度不得低于3位,不得高于20位" select_sql = "SELECT creator FROM %s WHERE user_name='%s';" % ( self.user, check_name) result = self.db.execute(select_sql) if result > 0: if self.db.fetchone()[0] == user_name: return False, u"您已注册过该用户" return False, u"该用户已被他人注册" return True, u"可以注册" def change_password(self, user_name, old_password, new_password): result, u_info = self.check(user_name, old_password) if result is False: return False, u_info en_password = generate_password_hash( self._md5_hash_password(user_name, new_password)) update_value = {"password": en_password} self.db.execute_update(self.user, update_value=update_value, where_value={"user_name": user_name}) return True, u"更新成功" def send_code(self, user_name, password, tel): change_url = "%s/code/bind/" try: res = requests.post(change_url, json={ "account": user_name, "password": password, "tel": tel }) except requests.ConnectionError as ce: return False, u"暂时无法发送,请稍后重试" r = res.json() if r["status"] == 2: return True, u"发送成功" if r["status"] == 10701: return False, u"请求频率过高,请稍后重试" if r["status"] == 10402: return False, u"手机号已被他人绑定,请更改还手机号" if r["status"] == 10801: return False, u"请求超过限制,请稍后重试" return False, r["message"] def bind_tel(self, user_name, password, tel, code): change_url = "%s/tel/" try: res = requests.post(change_url, json={ "account": user_name, "password": password, "tel": tel, "code": code }) except requests.ConnectionError as ce: return False, u"暂时绑定,请稍后重试" r = res.json() if r["status"] == 2: return True, u"绑定成功" if r["status"] == 10404: return False, u"验证码不正确,请重新输入" if r["status"] == 10405: return False, u"验证码已过期,请重新获得" if r["status"] == 10402: return False, u"手机号已被他人绑定,请更改还手机号" return False, r["message"] def clear_password(self, user_name, creator): update_sql = "UPDATE %s SET password=null WHERE user_name='%s' AND creator='%s';" % ( self.user, user_name, creator) self.db.execute(update_sql) return True, u"重置成功" def get_role_user(self, role): cols = [ "user_name", "role", "nick_name", "wx_id", "creator", "add_time", "email" ] select_sql = "SELECT %s FROM %s WHERE role & %s > 0;" \ % (",".join(cols), self.user, role) self.db.execute(select_sql) user_list = [] for item in self.db.fetchall(): info = {} for i in range(len(cols)): info[cols[i]] = item[i] info["add_time"] = info["add_time"].strftime(TIME_FORMAT) user_list.append(info) return True, user_list def my_user(self, user_name): select_sql = "SELECT user_name,role,nick_name,wx_id,creator,add_time FROM %s WHERE creator='%s';" \ % (self.user, user_name) self.db.execute(select_sql) user_list = [] for item in self.db.fetchall(): user_list.append({ "user_name": item[0], "role": item[1], "nick_name": item[2], "wx_id": item[3], "creator": item[4], "add_time": item[5].strftime(TIME_FORMAT) }) return True, user_list def get_user_info(self, user_name): select_sql = "SELECT user_name,role,nick_name,wx_id,creator,add_time,email FROM %s WHERE user_name='%s';" \ % (self.user, user_name) result = self.db.execute(select_sql) if result <= 0: return False, "user not exist" item = self.db.fetchone() user_info = { "user_name": item[0], "role": item[1], "nick_name": item[2], "wx_id": item[3], "creator": item[4], "add_time": item[5], "email": item[6] } return True, user_info def update_my_user_role(self, role, user_name, my_name): if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=%s WHERE user_name='%s' AND creator='%s';" \ % (self.user, role, user_name, my_name) self.db.execute(update_sql) return True, "success" def _add_role_my_user(self, role, user_name, my_name): if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=role | %s WHERE user_name='%s' AND creator='%s';" \ % (self.user, role, user_name, my_name) self.db.execute(update_sql) return True, "success" def add_role_my_users(self, role, user_names, my_name): if type(user_names) != list: return "Bad user_names" if len(user_names) == 0: return True, "no update" if len(user_names) == 1: return self.add_role_my_user(role, user_names, my_name) if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=role | %s WHERE creator='%s' AND user_name in ('%s');" \ % (self.user, role, "','".join(user_names), my_name) self.db.execute(update_sql) return True, "success" def _remove_role_my_user(self, role, user_name, my_name): if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=role & ~%s WHERE user_name='%s' AND creator='%s';" \ % (self.user, role, user_name, my_name) self.db.execute(update_sql) return True, "success" def remove_role_my_users(self, role, user_names, my_name): if type(user_names) != list: return "Bad user_names" if len(user_names) == 0: return True, "no update" if len(user_names) == 1: return self.add_role_my_user(role, user_names, my_name) if type(role) != int: return False, "Bad role" update_sql = "UPDATE %s SET role=role & ~%s WHERE creator='%s' AND user_name in ('%s');" \ % (self.user, role, "','".join(user_names), my_name) self.db.execute(update_sql) return True, "success"
class StatusManager: def __init__(self): self.db = DB() self.service_module = "service_module" self.function_module = "function_module" self.error_type = "error_type" self.status_code = "status_code" self.user = "******" def insert_service_module(self, service_title, service_desc): if check_chinese_en(service_title) is False: return False, "Bad service_title" if check_special_character(service_desc) is False: return False, "Bad service_desc" select_sql = "SELECT MAX(service_id) FROM %s;" % self.service_module result = self.db.execute(select_sql) if result == 0: service_id = 0 else: db_result = self.db.fetchone()[0] if db_result is None: service_id = 0 else: service_id = db_result + 1 insert_sql = "INSERT INTO %s (service_id,service_title,service_desc) VALUES (%s,'%s','%s');" \ % (self.service_module, service_id, service_title, service_desc) self.db.execute(insert_sql) return True, {"service_id": fill_zero(service_id, 2), "service_title": service_title, "service_desc": service_desc} def insert_function_module(self, service_id, function_title, function_desc): if check_chinese_en(function_title) is False: return False, "Bad function_title" if check_special_character(function_desc) is False: return False, "Bad function_desc" select_sql = "SELECT MAX(function_id) FROM %s WHERE service_id=%s;" % (self.function_module, service_id) result = self.db.execute(select_sql) if result == 0: function_id = 0 else: db_result = self.db.fetchone()[0] if db_result is None: function_id = 0 else: function_id = db_result + 1 insert_sql = "INSERT INTO %s (service_id,function_id,function_title,function_desc) VALUES (%s,%s,'%s','%s');" \ % (self.function_module, service_id, function_id, function_title, function_desc) self.db.execute(insert_sql) return True, {"function_id": fill_zero(function_id, 2), "function_title": function_title, "function_desc": function_desc} def _insert_status_code(self, status_code, code_desc, adder): code_desc = check_sql_character(code_desc) add_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT IGNORE INTO %s (status_code,code_desc,add_time,adder) " \ "VALUES (%s,'%s','%s','%s');" % (self.status_code, status_code, code_desc, add_time, adder) result = self.db.execute(insert_sql) return result def new_status_code(self, service_id, fun_id, type_id, error_id, error_desc, adder): if check_int(service_id) is False: return False, "Bad service_id" if check_int(fun_id) is False: return False, "Bad fun_id" if check_int(type_id) is False: return False, "Bad type_id" if check_int(error_id) is False: return False, "Bad error_id" status_code = fill_zero(service_id, 2) + fill_zero(fun_id, 2) + fill_zero(type_id, 2) + fill_zero(error_id, 2) self._insert_status_code(status_code, error_desc, adder) return True, status_code def new_mul_status_code(self, service_id, fun_id, error_info, adder): if check_int(service_id) is False: return False, "Bad service_id" if check_int(fun_id) is False: return False, "Bad fun_id" basic_code = service_id * 1000000 + fun_id * 10000 success_new = [] for item in error_info: if "type_id" not in item: continue if "error_desc" not in item: continue type_id = item["type_id"] error_desc = item["error_desc"] if check_int(type_id) is False: continue error_code = basic_code + type_id * 100 # 查询该模块下此类错误最大的状态码 select_sql = "SELECT status_code FROM %s WHERE status_code>=%s AND status_code<%s " \ "ORDER BY status_code DESC LIMIT 1;" % (self.status_code, error_code, error_code + 100) result = self.db.execute(select_sql) if result > 0: error_code = self.db.fetchone()[0] + 1 result = self._insert_status_code(error_code, error_desc, adder) if result == 1: success_new.append({"status_code": fill_zero(error_code, 8), "error_desc": error_desc}) return True, success_new def get_status_code(self, status_code=None): select_sql = "SELECT status_code,code_desc,add_time,adder FROM %s" % self.status_code args = [] if status_code is not None: select_sql += " WHERE status_code=%s" args.append(status_code) select_sql += ";" self.db.execute(select_sql, args=args) status_info = [] for item in self.db.fetchall(): status_info.append({"status_code": fill_zero(item[0], 8), "code_desc": item[1], "add_time": item[2].strftime(TIME_FORMAT), "adder": item[3]}) return True, status_info def get_function_info(self): select_sql = "SELECT service_id,service_title,service_desc FROM %s;" % self.service_module self.db.execute(select_sql) module_info = {} for item in self.db.fetchall(): service_id_s = fill_zero(item[0], 2) module_info[service_id_s] = {"title": item[1], "desc": item[2], "fun_info": {}} select_sql = "SELECT service_id,function_id,function_title,function_desc FROM %s;" % self.function_module self.db.execute(select_sql) for item in self.db.fetchall(): service_id_s = fill_zero(item[0], 2) if service_id_s in module_info: fun_id_s = fill_zero(item[1], 2) module_info[service_id_s]["fun_info"][fun_id_s] = {"title": item[2], "desc": item[3]} return True, module_info def get_error_type(self): select_sql = "SELECT type_id,type_title,type_desc FROM %s;" % self.error_type self.db.execute(select_sql) type_info = {} for item in self.db.fetchall(): type_id_s = fill_zero(item[0], 2) type_info[type_id_s] = {"title": item[1], "desc": item[2]} return True, type_info def del_status_code(self, status_code): if type(status_code) != int: return False, "Bad status_code" delete_sql = "DELETE FROM %s WHERE status_code=%s;" % (self.status_code, status_code) self.db.execute(delete_sql) return True, "success"
class BugManager: def __init__(self): self.db = DB() self.bug = "bug_info" self.bug_owner = "bug_owner" self.bug_example = "bug_example" self.user = "******" def new_bug_info(self, bug_title, submitter): submit_time = datetime.now().strftime(TIME_FORMAT) bug_no = uuid.uuid1().hex if len(bug_title) < 5: return False, "Bad bug_title" bug_title = check_sql_character(bug_title)[:50] insert_sql = "INSERT INTO %s (bug_no,bug_title,submitter,submit_time) VALUES ('%s','%s','%s','%s');" \ % (self.bug, bug_no, bug_title, submitter, submit_time) result = self.db.execute(insert_sql) if result != 1: return False, "sql execute result is %s " % result return True, {"bug_no": bug_no, "bug_title": bug_title, "submitter": submitter, "submit_time": submit_time} def new_bug_link(self, bug_no, user_name, link_type, adder): if len(bug_no) != 32: return False, "Bad bug_no" link_time = datetime.now().strftime(TIME_FORMAT) insert_sql = "INSERT INTO %s (bug_no,user_name,type,link_time,adder) VALUES ('%s','%s','%s','%s','%s') " \ "ON DUPLICATE KEY UPDATE adder=adder;" \ % (self.bug_owner, bug_no, user_name, link_type, link_time, adder) result = self.db.execute(insert_sql) # if result != 1: # return False, "sql execute result is %s " % result self.update_bug_status(bug_no, link_type) return True, {"bug_no": bug_no, "user_name": user_name, "link_type": link_type, "link_time": link_time} def del_bug_link(self, bug_no, user_name, link_type, adder): if len(bug_no) != 32: return False, "Bad bug_no" delete_sql = "DELETE FROM %s WHERE bug_no='%s' AND user_name='%s' AND adder='%s' AND type=%s;" \ % (self.bug_owner, bug_no, user_name, adder, link_type) result = self.db.execute(delete_sql) return True, result def del_bug(self, bug_no): if len(bug_no) != 32: return False, "Bad bug_no" def new_bug_example(self, bug_no, example_type, content): if len(bug_no) != 32: return False, "Bad bug_no" add_time = datetime.now().strftime(TIME_FORMAT) if len(content) < 5: return False, "Bad content" content = check_sql_character(content) insert_sql = "INSERT INTO %s (bug_no,type,content,add_time) VALUES ('%s','%s','%s','%s');" \ % (self.bug_example, bug_no, example_type, content, add_time) result = self.db.execute(insert_sql) if result != 1: return False, "sql execute result is %s " % result return True, {"bug_no": bug_no, "example_type": example_type, "content": content, "add_time": add_time} def update_bug_status(self, bug_no, status): if len(bug_no) != 32: return False, "Bad bug_no" update_sql = "UPDATE %s SET bug_status=%s WHERE bug_no='%s';" % (self.bug, status, bug_no) result = self.db.execute(update_sql) return True, result def get_bug_list(self, offset=0, num=20): if type(offset) != int or type(num) != int: return False, "Bad offset or num" select_sql = "SELECT bug_no,bug_title,submitter,submit_time,bug_status FROM %s " \ "ORDER BY bug_status,submit_time DESC LIMIT %s, %s;" % (self.bug, offset, num) self.db.execute(select_sql) bug_list = [] for item in self.db.fetchall(): bug_list.append({"bug_no": item[0], "bug_title": item[1], "submitter": item[2], "submit_time": item[3].strftime(TIME_FORMAT), "bug_status": item[4]}) return True, bug_list def get_bug_info(self, bug_no): if len(bug_no) != 32: return False, "Bad bug_no" # 获取基本信息 select_sql = "SELECT bug_no,bug_title,submitter,submit_time,bug_status,nick_name FROM %s AS i,%s AS u " \ "WHERE bug_no='%s' AND i.submitter=u.user_name;" \ % (self.bug, self.user, bug_no) result = self.db.execute(select_sql) if result != 1: return False, "Bad bug_no." info = self.db.fetchone() basic_info = {"bug_no": info[0], "bug_title": info[1], "submitter": info[2], "submit_time": info[3].strftime(TIME_FORMAT), "bug_status": info[4], "submit_name": info[5]} # 获取示例信息 select_sql = "SELECT type,content,add_time FROM %s WHERE bug_no='%s' ORDER BY add_time;" % (self.bug_example, bug_no) self.db.execute(select_sql) example_info = [] for item in self.db.fetchall(): example_info.append({"example_type": item[0], "content": item[1], "add_time": item[2].strftime(TIME_FORMAT)}) # 获取关联的人 select_sql = "SELECT o.user_name,type,link_time,adder,nick_name FROM %s AS o, %s AS u " \ "WHERE bug_no='%s' AND o.user_name=u.user_name;" % (self.bug_owner, self.user, bug_no) self.db.execute(select_sql) link_user = {"ys": {}, "owner": {}, "fix": {}, "channel": {}, "design": {}} for item in self.db.fetchall(): link_info = {"user_name": item[0], "link_type": item[1], "link_time": item[2].strftime(TIME_FORMAT), "adder": item[3], "nick_name": item[4]} if item[1] == 1: link_user["ys"][item[0]] = link_info elif item[1] == 2: link_user["owner"][item[0]] = link_info elif item[1] == 3: link_user["fix"][item[0]] = link_info elif item[1] == 4: link_user["channel"][item[0]] = link_info elif item[1] == 5: link_user["design"][item[0]] = link_info else: pass return True, {"basic_info": basic_info, "example_info": example_info, "link_user": link_user} def get_statistic(self): # 获得所有的统计信息 bug_role = 1024 select_sql = "SELECT u.user_name,nick_name,count(bug_no) AS bug_num FROM %s as u LEFT JOIN %s as b " \ "on u.user_name=b.user_name AND type=2 WHERE role & %s = %s " \ "GROUP BY u.user_name ORDER BY bug_num DESC;" \ % (self.user, self.bug_owner, bug_role, bug_role) self.db.execute(select_sql) all_data = [] for item in self.db.fetchall(): all_data.append({"user_name": item[0], "nick_name": item[1], "bug_num": item[2]}) # 获得最近一个月的统计信息 after_time = (datetime.now() - timedelta(days=30)).strftime(TIME_FORMAT) select_sql = "SELECT u.user_name,nick_name,count(bug_no) AS bug_num FROM %s as u LEFT JOIN %s as b " \ "on u.user_name=b.user_name AND type=2 AND link_time>'%s' " \ "WHERE role & %s = %s GROUP BY u.user_name ORDER BY bug_num DESC;" \ % (self.user, self.bug_owner, after_time, bug_role, bug_role) self.db.execute(select_sql) month_data = [] for item in self.db.fetchall(): month_data.append({"user_name": item[0], "nick_name": item[1], "bug_num": item[2]}) return True, {"month": month_data, "all": all_data}