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}