def __getRealQuery(self,
columns,
outerjoins=[],
filters=None,
orders=None):
"""
私有方法，通用查询方法
:param columns:
:param outerjoins:
:param filters:
:param orders:
:return:
"""
if columns is not None:
if isinstance(columns, (tuple, list)):
query = db_session.query(*columns)
else:
query = db_session.query(columns)
if outerjoins is not None:
if isinstance(outerjoins, (tuple, list)):
query = query.outerjoin(*outerjoins)
else:
query = query.outerjoin(outerjoins)
if filters is not None:
if isinstance(filters, (tuple, list)):
query = query.filter(*filters)
else:
query = query.filter(filters)
if orders is not None:
if isinstance(orders, (tuple, list)):
query = query.order_by(*orders)
else:
query = query.order_by(orders)
return query
def updRolePermission():
myRes = MyResponse()
RESOURCE_KEYS = MyRequest.get_verify_list("RESOURCE_KEYS")
ROLE_KEY = MyRequest.get("ROLE_KEY", type=int)
RESOURCE_TYPE = MyRequest.get("RESOURCE_TYPE", type=int)
try:
db_session.begin_nested()
db_session.query(RolePermission).filter(
RolePermission.ROLE_KEY == ROLE_KEY).filter(
RolePermission.RESOURCE_TYPE == RESOURCE_TYPE).delete(
synchronize_session=False)
for key in RESOURCE_KEYS:
rolePermission = RolePermission()
rolePermission.ROLE_KEY = ROLE_KEY
rolePermission.RESOURCE_KEY = key
rolePermission.RESOURCE_TYPE = RESOURCE_TYPE
db_session.add(rolePermission)
db_session.commit()
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
db_session.rollback()
mylog.error(ex)
myRes.msg = str(ex)
finally:
db_session.close()
return myRes.to_json()
def add():
myRes = MyResponse()
OPER_NAME = MyRequest.get_verify_empty("OPER_NAME", errmsg="操作名称不能为空")
OPER_PKEY = MyRequest.get_verify_empty("OPER_PKEY", errmsg="OPER_PKEY不能为空")
OPER_URL = MyRequest.get("OPER_URL", type=str)
OPER_TYPE = MyRequest.get("OPER_TYPE", type=int)
OPER_SOFT = MyRequest.get("OPER_SOFT", type=int)
try:
mybrother = db_session.query(
OperInfo.OPER_KEY).filter(OperInfo.OPER_PKEY == OPER_PKEY).all()
if mybrother:
array_id = [int(x.OPER_KEY) for x in mybrother]
###取最大的ID
OPER_KEY = str(max(array_id) + 1)
elif OPER_PKEY == "0":
OPER_KEY = "101"
else:
OPER_KEY = OPER_PKEY + "001"
operInfo = OperInfo()
operInfo.OPER_KEY = OPER_KEY
operInfo.OPER_NAME = OPER_NAME
operInfo.OPER_URL = OPER_URL
operInfo.OPER_TYPE = OPER_TYPE
operInfo.OPER_PKEY = OPER_PKEY
operInfo.OPER_SOFT = OPER_SOFT
MySqlalchemy.comAdd(operInfo)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def identify(cls, request):
"""
用户鉴权
:return: list
"""
auth_header = request.headers.get('Authorization')
if (auth_header):
auth_tokenArr = auth_header.split(" ")
if (not auth_tokenArr or auth_tokenArr[0] != 'Bearer'
or len(auth_tokenArr) != 2):
abort(401, 'Token错误或已过期，请重新登录')
else:
auth_token = auth_tokenArr[1]
payload = cls.__decode_auth_token(cls, auth_token)
if not isinstance(payload, str):
userInfo = db_session.query(UserInfo).filter(
UserInfo.USER_KEY == payload['data']['USER_KEY'])
if (userInfo is None):
abort(401, '找不到该用户信息')
else:
# if (user.login_time == payload['data']['login_time']):
if True:
return payload['data']['USER_KEY']
else:
abort(401, 'Token已更改，请重新登录获取')
else:
abort(401, 'Token错误或已过期，请重新登录')
else:
abort(401, '没有提供认证token')
def getDptAndUserList():
myRes = MyResponse()
try:
query_data_dpt = db_session.query(DptInfo.DPT_KEY, DptInfo.DPT_PKEY,
DptInfo.DPT_NAME)
query_data_user = db_session.query(
UserInfo.USER_KEY, UserInfo.DPT_KEY,
UserInfo.USER_NAME).filter(UserInfo.USER_KEY != 1)
query_data = query_data_dpt.union_all(query_data_user)
myRes.data = MySqlalchemy.convertToList(query_data)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def exportLogOper():
start_time = time.time()
searchName = MyRequest.get("searchName", type=str).strip()
startDate = MyRequest.get_verify_date("startDate")
endDate = MyRequest.get_verify_date("endDate")
downLoadFile = DownLoadFile()
xlsxwriterWriter = XlsxwriterWriter()
try:
query_data = db_session.query(LogOper).all()
from app.apis.utils.thread_helper import convertToListThread
dataArray = convertToListThread(query_data)
end_time = time.time()
print(end_time - start_time)
print(len(dataArray))
# df = pandas.DataFrame(dataArray)
# xlsxwriterWriter.open()
# xlsxwriterWriter.writeSheetbyList(df, titles=None, sheetname="ttt")
# downLoadFile.filepath = xlsxwriterWriter.filepath
# downLoadFile.filename=xlsxwriterWriter.filename
# downLoadFile.newfilename = "操作日志"+getSysDateTimeStr()+".xlsx"
except ExcelError as ex:
mylog.error(ex)
abort(404, "写入excel错误")
except SQLAlchemyError as ex:
mylog.error(ex)
abort(404, sys.exc_info()[1])
except Exception as ex:
mylog.error(ex)
abort(404, sys.exc_info()[1])
finally:
xlsxwriterWriter.close()
myRes = MyResponse()
print("ok")
return myRes.to_json()
def add():
myRes = MyResponse()
MENU_NAME = MyRequest.get_verify_empty("MENU_NAME", errmsg="菜单名称不能为空")
MENU_PKEY = MyRequest.get_verify_empty("MENU_PKEY", errmsg="MENU_PKEY")
MENU_URL = MyRequest.get("MENU_URL", type=str)
MENU_ICON = MyRequest.get("MENU_ICON", type=str)
MENU_SOFT = MyRequest.get("MENU_SOFT", type=str)
try:
mybrother = db_session.query(
MenuInfo.MENU_KEY).filter(MenuInfo.MENU_PKEY == MENU_PKEY).all()
if mybrother:
array_id = [int(x.MENU_KEY) for x in mybrother]
###取最大的ID
MENU_KEY = str(max(array_id) + 1)
elif MENU_PKEY == "0":
MENU_KEY = "101"
else:
MENU_KEY = MENU_PKEY + "001"
menu = MenuInfo()
menu.MENU_KEY = MENU_KEY
menu.MENU_NAME = MENU_NAME
menu.MENU_PKEY = MENU_PKEY
menu.MENU_URL = MENU_URL
menu.MENU_ICON = MENU_ICON
menu.MENU_SOFT = MENU_SOFT
MySqlalchemy.comAdd(menu)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def add():
myRes = MyResponse()
DPT_PKEY = MyRequest.get_verify_empty("DPT_PKEY", errmsg="请选择一个父节点")
DPT_NAME = MyRequest.get_verify_empty("DPT_NAME", errmsg="部门名称不能为空")
DPT_SOFT = MyRequest.get("DPT_SOFT", type=int)
try:
mybrother = db_session.query(DptInfo.DPT_KEY).filter(DptInfo.DPT_PKEY == DPT_PKEY).all()
if mybrother:
array_id = [int(x.DPT_KEY) for x in mybrother]
###取最大的ID
DPT_KEY = str(max(array_id) + 1)
elif DPT_PKEY=="0":
DPT_KEY = "101"
else:
DPT_KEY=DPT_PKEY+"001"
dpt = DptInfo()
dpt.DPT_KEY=DPT_KEY
dpt.DPT_PKEY = DPT_PKEY
dpt.DPT_NAME = DPT_NAME
dpt.DPT_SOFT = DPT_SOFT
MySqlalchemy.comAdd(dpt)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg=ResState.ERROR_DB
return myRes.to_json()
def getList():
myRes = MyResponse()
try:
query_data = db_session.query(UserInfo).all()
myRes.data = MySqlalchemy.convertToList(query_data)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def getDetail():
myRes = MyResponse()
DPT_KEY = MyRequest.get_verify_empty("DPT_KEY", errmsg="部门ID不能为空")
try:
query_data=db_session.query(DptInfo).filter(DptInfo.DPT_KEY==DPT_KEY).one()
dict_menu=MySqlalchemy.convertToDict(query_data)
myRes.data = dict_menu
myRes.code = ResState.HTTP_SUCCESS
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg=str(ex)
return myRes.to_json()
def getList():
myRes = MyResponse()
try:
query_data = db_session.query(RoleInfo).order_by(
RoleInfo.TIME_MODIFY.desc())
myRes.data = MySqlalchemy.convertToList(query_data)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def getDetail():
myRes = MyResponse()
ROLE_KEY = MyRequest.get("ROLE_KEY", type=int)
try:
query_data = db_session.query(RoleInfo).filter(
RoleInfo.ROLE_KEY == ROLE_KEY).one()
dict_data = MySqlalchemy.convertToDict(query_data)
myRes.code = ResState.HTTP_SUCCESS
myRes.data = dict_data
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def getList():
myRes = MyResponse()
try:
query_menuButton = db_session.query(OperInfo).order_by(
OperInfo.OPER_SOFT.asc()).all()
myRes.data = MySqlalchemy.convertToList(query_menuButton)
print(myRes.data)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def getDetail():
myRes = MyResponse()
Fid = MyRequest.get_verify_empty("Fid", errmsg="Fid不能为空")
try:
query_menu = db_session.query(MenuInfo).filter(
MenuInfo.Fid == Fid).one()
dict_menu = MySqlalchemy.convertToDict(query_menu)
myRes.data = dict_menu
myRes.code = ResState.HTTP_SUCCESS
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def updUserRole():
myRes = MyResponse()
ROLE_KEYS = MyRequest.getList("ROLE_KEYS[]")
USER_KEY = MyRequest.get("USER_KEY", type=int)
try:
db_session.begin_nested()
db_session.query(UserRole).filter(
UserRole.USER_KEY == USER_KEY).delete()
for key in ROLE_KEYS:
userRole = UserRole()
userRole.USER_KEY = USER_KEY
userRole.ROLE_KEY = key
db_session.add(userRole)
db_session.commit()
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
db_session.rollback()
mylog.error(ex)
myRes.msg = str(ex)
finally:
db_session.close()
return myRes.to_json()
def getList():
myRes = MyResponse()
try:
query_menu = db_session.query(
MenuInfo.MENU_KEY, MenuInfo.MENU_PKEY, MenuInfo.MENU_NAME,
MenuInfo.MENU_URL, MenuInfo.MENU_ICON,
MenuInfo.MENU_SOFT).order_by(MenuInfo.MENU_SOFT.asc()).all()
myRes.data = MySqlalchemy.convertToList(query_menu)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def getRoleMenuAndOperList():
myRes = MyResponse()
ROLE_KEY = MyRequest.get("ROLE_KEY", type=int)
try:
query_data = db_session.query(RolePermission.ROLE_KEY,
RolePermission.RESOURCE_KEY,
RolePermission.RESOURCE_TYPE).filter(
RolePermission.ROLE_KEY == ROLE_KEY)
myRes.data = MySqlalchemy.convertToList(query_data)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def getRoleListByUserKey():
myRes = MyResponse()
USER_KEY = MyRequest.get("USER_KEY", type=int)
try:
clomns = [UserRole.USER_KEY, RoleInfo.ROLE_NAME, UserRole.ROLE_KEY]
query_data = db_session.query(*clomns).outerjoin(
RoleInfo, RoleInfo.ROLE_KEY == UserRole.ROLE_KEY).filter(
UserRole.USER_KEY == USER_KEY)
myRes.data = MySqlalchemy.convertToList(query_data)
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def delete():
myRes = MyResponse()
OPER_KEY = MyRequest.get_verify_empty("OPER_KEY", errmsg="OPER_KEY不能为空")
try:
db_session.begin_nested()
###删除按钮相关的角色权限信息
db_session.query(RolePermission).filter(
RolePermission.RESOURCE_TYPE == 1).filter(
RolePermission.RESOURCE_KEY.like(OPER_KEY + "%")).delete(
synchronize_session=False)
###删除按钮信息
db_session.query(OperInfo).filter(
OperInfo.OPER_KEY.like(OPER_KEY +
"%")).delete(synchronize_session=False)
db_session.commit()
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
db_session.rollback()
mylog.error(ex)
myRes.msg = str(ex)
finally:
db_session.close()
return myRes.to_json()
def getPageList():
myRes = MyResponse()
currentPage = MyRequest.get("currentPage", type=int)
pageSize = MyRequest.get("pageSize", type=int)
searchDptKey = MyRequest.get("searchDptKey", type=str)
searchName = MyRequest.get("searchName", type=str).strip()
columns = [
UserInfo.USER_KEY, UserInfo.USER_NAME, UserInfo.LOGIN_NAME,
UserInfo.USER_SEX, UserInfo.USER_POSITION, UserInfo.DPT_KEY,
DptInfo.DPT_NAME, UserInfo.TIME_MODIFY, UserInfo.PHONE
]
filters = []
outerjoins = [(DptInfo, DptInfo.DPT_KEY == UserInfo.DPT_KEY)]
try:
USER_KEY = g.USER_KEY
if USER_KEY != 1:
filters.append(UserInfo.USER_KEY != 1)
if searchDptKey != 0:
filters.append(UserInfo.DPT_KEY.like(searchDptKey + "%"))
if searchName is not None:
filters.append(UserInfo.USER_NAME.like("%" + searchName + "%"))
user_page_info = MySqlalchemy.get_page_list(
currentPage,
pageSize,
UserInfo.USER_KEY,
columns,
outerjoins=outerjoins,
filters=filters,
orders=[UserInfo.TIME_MODIFY.desc()])
###拼接用户角色
query_data_role = db_session.query(
UserRole.USER_KEY, RoleInfo.ROLE_NAME,
UserRole.ROLE_KEY).outerjoin(
RoleInfo, RoleInfo.ROLE_KEY == UserRole.ROLE_KEY)
list_role = MySqlalchemy.convertToList(query_data_role)
for xuser in user_page_info["data"]:
list_roleName = []
for xrole in list_role:
if xuser["USER_KEY"] == xrole["USER_KEY"]:
list_roleName.append(xrole["ROLE_NAME"])
xuser["ROLE_NAMES"] = ",".join(list_roleName)
myRes.data = user_page_info
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
return myRes.to_json()
def resetPwdByUserKey():
myRes = MyResponse()
USER_KEY = MyRequest.get("USER_KEY", type=int)
try:
q_model = db_session.query(UserInfo).filter(
UserInfo.USER_KEY == USER_KEY).one()
q_model.set_password("123456")
db_session.commit()
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
finally:
db_session.close()
return myRes.to_json()
def delete():
myRes = MyResponse()
DPT_KEY = MyRequest.get_verify_empty("DPT_KEY", errmsg="ID不能为空")
try:
rows = db_session.query(DptInfo).filter(DptInfo.DPT_KEY.like(DPT_KEY+"%")).delete(synchronize_session=False)
db_session.commit()
if rows < 1:
raise SQLAlchemyError("操作失败")
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = ResState.ERROR_DB
finally:
db_session.close()
return myRes.to_json()
def comDel(cls, model, filters):
"""
删除方法
:param model:
:param filters:
:return:
"""
try:
rows = db_session.query(model).filter(*filters).delete(
synchronize_session=False)
db_session.commit()
if rows < 1:
raise SQLAlchemyError("操作失败")
except SQLAlchemyError as ex:
raise ex
finally:
db_session.close()
def comUpdate(cls, model, filters, attrs):
"""
修改方法
:param model:
:param filters:
:param attrs:
:return:
"""
try:
rows = db_session.query(model).filter(*filters).update(attrs)
db_session.commit()
if rows < 1:
raise SQLAlchemyError("操作失败")
except SQLAlchemyError as ex:
raise ex
finally:
db_session.close()
def authenticate(cls, username, password):
"""
用户登录，登录成功返回token，写将登录时间写入数据库；登录失败返回失败原因
:param password:
:return: json
"""
userInfo = db_session.query(UserInfo).filter(
UserInfo.LOGIN_NAME == username).first()
if (userInfo is None):
return abort(401, "用户名或密码错误")
else:
if (userInfo.check_password(userInfo.USER_PWD, password)):
login_time = int(time.time())
###这里后期需要将登录时间写入缓存，提高性能
token = cls.__encode_auth_token(cls, userInfo.USER_KEY,
login_time)
dict_user = MySqlalchemy.convertToDict(userInfo)
return dict_user, token.decode()
else:
return abort(401, "用户名或密码错误")
def comUpdates(cls, list_model, id, attrs):
"""
批量修改
:param list_model:
:param id:
:param attrs:
:return:
"""
try:
db_session.begin_nested()
for model in list_model:
rows = db_session.query(model).filter(
model.Fid == id).update(attrs)
if rows < 1:
raise SQLAlchemyError("操作失败")
db_session.commit()
except SQLAlchemyError as ex:
db_session.rollback()
raise ex
finally:
db_session.close()
def exportLogLogin():
myRes = MyResponse()
searchName = MyRequest.get("searchName", type=str).strip()
startDate = MyRequest.get_verify_date("startDate")
endDate = MyRequest.get_verify_date("endDate")
downLoadFile = DownLoadFile()
xlsxwriterWriter = XlsxwriterWriter()
try:
db_query = db_session.query(UserInfo.Fid, UserInfo.FloginName,
UserInfo.Fname, LogLogin.FcreateTime,
LogLogin.Fremark, LogLogin.FipAddress)
db_query = db_query.outerjoin(UserInfo,
UserInfo.Fid == LogLogin.FuserId)
db_query = db_query.filter(
LogLogin.FcreateTime.between(startDate, endDate))
if searchName != '':
db_query = db_query.filter(
UserInfo.FloginName.like("%" + searchName + "%"))
query_data = db_query.order_by(LogLogin.FcreateTime.desc())
dataArray = MySqlalchemy.convertToDict(query_data)
df = pandas.DataFrame(dataArray)
xlsxwriterWriter.open()
xlsxwriterWriter.writeSheetbyList(df, titles=None, sheetname="ttt")
downLoadFile.filepath = xlsxwriterWriter.filepath
downLoadFile.filename = xlsxwriterWriter.filename
downLoadFile.newfilename = "操作日志" + getSysDateTimeStr() + ".xlsx"
myRes.data = []
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except ExcelError as ex:
mylog.error(ex)
abort(404, "写入excel错误")
except SQLAlchemyError as ex:
mylog.error(ex)
abort(404, sys.exc_info()[1])
finally:
xlsxwriterWriter.close()
return downLoadFile.download()
def resetMyPwd():
myRes = MyResponse()
FuserId = g.USER_KEY
FoldPwd = MyRequest.get_verify_empty("FoldPwd", errmsg="旧密码不能为空")
FnewPwd = MyRequest.get_verify_empty("FnewPwd", errmsg="新密码不能为空")
FnewPwdOk = MyRequest.get("FnewPwdOk", type=str)
try:
if FnewPwd != FnewPwdOk:
return myRes.to_json_msg("新旧密码不一致")
userInfo = db_session.query(UserInfo).filter(
UserInfo.USER_KEY == FuserId).one()
if not userInfo.check_password(userInfo.USER_PWD, FoldPwd):
return myRes.to_json_msg("旧密码错误，请重新输入")
userInfo.set_password(FnewPwd)
db_session.commit()
myRes.code = ResState.HTTP_SUCCESS
myRes.msg = "操作成功"
except SQLAlchemyError as ex:
mylog.error(ex)
myRes.msg = str(ex)
finally:
db_session.close()
return myRes.to_json()
def comDels(cls, models, ids):
"""
批量删除
:param models:
:param ids:
:return:
"""
try:
if isinstance(models,
(list, tuple)) and isinstance(ids, (list, tuple)):
db_session.begin_nested()
for model in models:
rows = db_session.query(models).filter(
model.Fid == id).delete(synchronize_session=False)
if rows < 1:
raise SQLAlchemyError("操作失败")
db_session.commit()
else:
raise MyError("Models and ids mast be list or tuple.")
except SQLAlchemyError as ex:
db_session.rollback()
raise ex
finally:
db_session.close()
