def list(self, page, size, proName, userName, modName, limitTime,
currentUser):
"""
查询分配任务记录
"""
start = (int(page) - 1) * int(size)
try:
sql = """
SELECT task.*, member.PRO_NAME, member.USER_NAME
FROM man_pro_task AS task
LEFT JOIN man_pro_member AS member
ON task.PRO_ID = member.PRO_ID
AND task.USER_ID = member.USER_ID
WHERE 1=1
{}{}{}{}{}
ORDER BY MODULE_END ASC, CREATE_TIME DESC
LIMIT {start}, {size};
""".format(
' AND PRO_NAME like "%{}%"'.format(proName) if proName else '',
' AND USER_NAME like "%{}%"'.format(userName)
if userName else '',
' AND MODULE_NAME like "%{}%"'.format(modName)
if modName else '',
' AND MODULE_END <= "{}"'.format(limitTime)
if limitTime else '',
' AND USER_CREATOR = "{}"'.format(currentUser)
if currentUser else '',
start=start,
size=size)
Utils.log('查询数据库任务列表SQL', sql)
list = PySQL.get(sql)
sqlTotal = """
SELECT COUNT(1)
FROM man_pro_task AS task
LEFT JOIN man_pro_member AS member
ON task.PRO_ID = member.PRO_ID
AND task.USER_ID = member.USER_ID
WHERE 1=1
{}{}{}{}{}
""".format(
' AND PRO_NAME like "%{}%"'.format(proName) if proName else '',
' AND USER_NAME like "%{}%"'.format(userName)
if userName else '',
' AND MODULE_NAME = "{}"'.format(modName) if modName else '',
' AND MODULE_END <= "{}"'.format(limitTime)
if limitTime else '',
' AND USER_CREATOR = "{}"'.format(currentUser)
if currentUser else '',
)
total = PySQL.count(sqlTotal)
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
def getList(self, page, size, proId, userCreator, userId, userName):
"""
查询数据库 user 列表
"""
start = (int(page) - 1) * int(size)
try:
sql = """
SELECT user.*
FROM man_auth_user AS user
WHERE (user.USER_CREATOR='{userCreator}' OR user.USER_ID='{userCreator}')
{id}{name}
AND user.USER_ID NOT IN (
SELECT member.USER_ID
FROM man_pro_member AS member
WHERE member.USER_CREATOR='{userCreator}'
AND member.PRO_ID='{proId}'
)
ORDER BY USER_ID
LIMIT {start}, {size};
""".format(
userCreator=userCreator,
proId=proId,
id=' AND USER_ID like "%{}%"'.format(userId) if userId else '',
name=' AND USER_NAME like "%{}%"'.format(userName)
if userName else '',
start=start,
size=size)
sqlTotal = """
SELECT COUNT(1)
FROM man_auth_user AS user
WHERE (user.USER_CREATOR='{userCreator}' OR user.USER_ID='{userCreator}')
{id}{name}
AND user.USER_ID NOT IN (
SELECT member.USER_ID
FROM man_pro_member AS member
WHERE member.USER_CREATOR='{userCreator}'
AND member.PRO_ID='{proId}'
)
ORDER BY USER_ID;
""".format(
userCreator=userCreator,
proId=proId,
id=' AND USER_ID like "%{}%"'.format(userId) if userId else '',
name=' AND USER_NAME like "%{}%"'.format(userName)
if userName else '')
Utils.log('查询项目成员列表 {}'.format(sql))
list = PySQL.get(sql)
total = PySQL.count(sqlTotal)
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
def getList(self):
"""
查询 projec type 列表
"""
try:
sql = "SELECT CONVERT(TYPE_ID, char) AS TYPE_ID, TYPE_TEXT FROM man_pro_type;"
list = PySQL.get(sql)
return list
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return []
def getList(self, page, size, userId, userName, userAuth, userLogin,
userCreator):
"""
查询数据库用户列表
"""
start = (int(page) - 1) * int(size)
try:
sql = """
SELECT USER_ID,USER_NAME,USER_AUTH,USER_LOGIN,USER_CREATOR,USER_CRE_TIME
FROM man_auth_user
WHERE 1=1
{}{}{}{}{}{}
ORDER BY USER_AUTH, USER_ID
LIMIT {start}, {size};
""".format(
' AND USER_ID like "%{}%"'.format(userId) if userId else '',
' AND USER_Name like "%{}%"'.format(userName)
if userName else '',
' AND USER_AUTH = "{}"'.format(userAuth) if userAuth else '',
' AND USER_LOGIN = "******"'.format(userLogin)
if userLogin else '',
' AND USER_CREATOR = "{}"'.format(userCreator)
if userCreator and userCreator != 'admin' else '',
' OR USER_ID = "{}"'.format(userCreator)
if userCreator and userCreator != 'admin' else '',
start=start,
size=size)
Utils.log('查询数据库用户列表SQL', sql)
list = PySQL.get(sql)
sqlTotal = """
SELECT COUNT(1) FROM man_auth_user
WHERE 1=1
{}{}{}{}{}{};
""".format(
' AND USER_ID like "%{}%"'.format(userId) if userId else '',
' AND USER_Name like "%{}%"'.format(userName)
if userName else '',
' AND USER_AUTH = "{}"'.format(userAuth) if userAuth else '',
' AND USER_LOGIN = "******"'.format(userLogin)
if userLogin else '',
' AND USER_CREATOR = "{}"'.format(userCreator)
if userCreator and userCreator != 'admin' else '',
' OR USER_ID = "{}"'.format(userCreator)
if userCreator and userCreator != 'admin' else '',
)
total = PySQL.count(sqlTotal)
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
def getInfo(self, page, size, proId, userId, userName):
"""
查询数据库 project-user 列表
"""
start = (int(page) - 1) * int(size)
try:
sql = """
SELECT mem.*, task.MODULE_ID, task.MODULE_NAME
FROM man_pro_member AS mem
LEFT JOIN man_pro_task AS task
ON mem.PRO_ID = task.PRO_ID
AND mem.USER_ID = task.USER_ID
WHERE mem.PRO_ID='{proId}'
{id}{name}
ORDER BY mem.USER_ID
LIMIT {start}, {size};
""".format(
proId=proId,
id=' AND mem.USER_ID like "%{}%"'.format(userId)
if userId else '',
name=' AND mem.USER_NAME like "%{}%"'.format(userName)
if userName else '',
start=start,
size=size)
sqlTotal = """
SELECT COUNT(1)
FROM man_pro_member AS mem
LEFT JOIN man_pro_task AS task
ON mem.PRO_ID = task.PRO_ID
AND mem.USER_ID = task.USER_ID
WHERE mem.PRO_ID='{proId}'
{id}{name}
ORDER BY mem.USER_ID
LIMIT {start}, {size};
""".format(
proId=proId,
id=' AND mem.USER_ID like "%{}%"'.format(userId)
if userId else '',
name=' AND mem.USER_NAME like "%{}%"'.format(userName)
if userName else '',
start=start,
size=size)
list = PySQL.get(sql)
total = PySQL.count(sqlTotal)
Utils.log('查询项目成员负责模块 {}'.format(sql))
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
def checkLogin(self, id, pwd):
"""
检查登录信息，验证账户密码是否正确
"""
try:
sql = "SELECT * FROM man_auth_user WHERE USER_LOGIN='******' AND USER_ID='{0}' AND USER_PWD='{1}';".format(
id, pwd)
sqlTotal = "SELECT COUNT(1) FROM man_auth_user WHERE USER_LOGIN='******' AND USER_ID='{0}' AND USER_PWD='{1}';".format(
id, pwd)
list = PySQL.get(sql)
total = PySQL.count(sqlTotal)
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
def list(self, page, size, proName, fileName, uploadStart, uploadEnd,
userCreator):
"""
查询上传服务器的文件列表
"""
start = (int(page) - 1) * int(size)
try:
sql = """
SELECT file.*, CONCAT( '{downPath}', file.FILE_NAME ) AS FILE_URL
FROM man_pro_file AS file
WHERE 1=1
{}{}{}
ORDER BY UPLOAD_TIME DESC
LIMIT {start}, {size};
""".format(
' AND PRO_Name like "%{}%"'.format(proName) if proName else '',
' AND FILE_NAME like "%{}%"'.format(fileName)
if fileName else '',
' AND UPLOAD_TIME BETWEEN "{}" AND "{}"'.format(
uploadStart, uploadEnd)
if uploadStart and uploadEnd else '',
downPath=DOWNLOAD_HOST,
start=start,
size=size)
sqlTotal = """
SELECT COUNT(1)
FROM man_pro_file AS file
WHERE 1=1
{}{}{}
ORDER BY UPLOAD_TIME DESC;
""".format(
' AND PRO_Name like "%{}%"'.format(proName) if proName else '',
' AND FILE_NAME like "%{}%"'.format(fileName)
if fileName else '',
' AND UPLOAD_TIME BETWEEN "{}" AND "{}"'.format(
uploadStart, uploadEnd)
if uploadStart and uploadEnd else '',
)
Utils.log('查询数据库上传服务器的文件列表SQL', sql)
list = PySQL.get(sql)
total = PySQL.count(sqlTotal)
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
def query(self, proId, userId):
"""
查询数据库项目模块列表
"""
try:
sql = """
SELECT task.MODULE_ID, task.MODULE_NAME
FROM man_pro_task AS task
WHERE task.PRO_ID='{}'
ORDER BY CREATE_TIME;
""".format(proId)
Utils.log('查询数据库项目模块列表SQL', sql)
list = PySQL.get(sql)
return list
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return []
def getListAll(self, proId):
"""
查询数据库所属项目成员列表
"""
try:
sql = """
SELECT USER_ID, USER_NAME
FROM man_pro_member
WHERE PRO_ID='{proId}'
ORDER BY USER_NAME ASC;
""".format(proId=proId)
list = PySQL.get(sql)
return list
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return []
def getList(self, page, size, proId, proName, proType, proUse,
userCreator):
"""
查询数据库 project 列表
"""
start = (int(page) - 1) * int(size)
try:
sql = """
SELECT info.*,type.TYPE_TEXT
FROM man_pro_info AS info, man_pro_type AS type
WHERE info.PRO_TYPE=type.TYPE_ID
{}{}{}{}{}
ORDER BY PRO_ID
LIMIT {start}, {size};
""".format(
' AND PRO_ID like "%{}%"'.format(proId) if proId else '',
' AND PRO_Name like "%{}%"'.format(proName) if proName else '',
' AND PRO_TYPE = "{}"'.format(proType) if proType else '',
' AND PRO_USE = "{}"'.format(proUse) if proUse else '',
' AND PRO_LEADER = "{}"'.format(userCreator)
if userCreator else '',
start=start,
size=size)
Utils.log('查询数据库 project 列表SQL', sql)
list = PySQL.get(sql)
sqlTotal = """
SELECT COUNT(1)
FROM man_pro_info AS info, man_pro_type AS type
WHERE info.PRO_TYPE=type.TYPE_ID
{}{}{}{}{}
ORDER BY PRO_ID;
""".format(
' AND PRO_ID like "%{}%"'.format(proId) if proId else '',
' AND PRO_NAME like "%{}%"'.format(proName) if proName else '',
' AND PRO_TYPE = "{}"'.format(proType) if proType else '',
' AND PRO_USE = "{}"'.format(proUse) if proUse else '',
' AND PRO_LEADER = "{}"'.format(userCreator)
if userCreator else '')
total = PySQL.count(sqlTotal)
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
def record(self, page, size, currentUserr):
"""
查询调试记录列表
"""
start = (int(page) - 1) * int(size)
try:
sql = """
SELECT record.*,
bug.PRO_NAME, bug.MODULE_NAME, bug.BUG_TITLE, bug.BUG_LEVEL, bug.BUG_DES
FROM man_bug_record AS record
LEFT JOIN man_pro_bug AS bug
ON record.BUG_ID = bug.BUG_ID
WHERE bug.PRO_ID IN (
SELECT DISTINCT member.PRO_ID
FROM man_pro_member AS member
WHERE member.USER_ID = '{userId}'
)
ORDER BY HANDLE_TIME DESC
LIMIT {start}, {size};
""".format(userId=currentUserr, start=start, size=size)
sqlTotal = """
SELECT COUNT(1)
FROM man_bug_record AS record
LEFT JOIN man_pro_bug AS bug
ON record.BUG_ID = bug.BUG_ID
WHERE bug.PRO_ID IN (
SELECT DISTINCT member.PRO_ID
FROM man_pro_member AS member
WHERE member.USER_ID = '{userId}'
);
""".format(userId=currentUserr)
Utils.log('查询问题列表SQL', sql)
list = PySQL.get(sql)
total = PySQL.count(sqlTotal)
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
def query(self, modId):
"""
查询任务进度
"""
try:
sql = """
SELECT rate.*, task.MODULE_NAME, member.PRO_NAME, task.MODULE_START, task.MODULE_END
FROM man_pro_rate AS rate
LEFT JOIN man_pro_task AS task
ON rate.MODULE_ID = task.MODULE_ID
LEFT JOIN man_pro_member AS member
ON task.PRO_ID = member.PRO_ID
AND rate.USER_ID = member.USER_ID
WHERE rate.USER_ID='{}'
ORDER BY task.CREATE_TIME DESC;
""".format(modId)
data = PySQL.get(sql)
Utils.log(sql)
Utils.log(data)
return data
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return []
def list(self, page, size, proName, modName, bugTitle, bugLevel, bugStatus,
createBy, handleBy, createStart, createEnd, currentUserr):
"""
查询问题列表
"""
start = (int(page) - 1) * int(size)
try:
sql = """
SELECT bug.*
FROM man_pro_bug AS bug
WHERE 1=1
{}{}{}{}{}{}{}{}
AND bug.PRO_ID IN (
SELECT DISTINCT member.PRO_ID
FROM man_pro_member AS member
WHERE member.USER_ID = '{userId}'
)
ORDER BY CREATE_TIME DESC
LIMIT {start}, {size};
""".format(
' AND PRO_Name like "%{}%"'.format(proName) if proName else '',
' AND MODULE_NAME like "%{}%"'.format(modName)
if modName else '',
' AND BUG_TITLE like "%{}%"'.format(bugTitle)
if bugTitle else '',
' AND BUG_LEVEL = "{}"'.format(bugLevel) if bugLevel else '',
' AND BUG_STATUS like "%{}%"'.format(bugStatus)
if bugStatus else '',
' AND CREATE_BY like "%{}%"'.format(createBy)
if createBy else '',
' AND HANDLE_BY like "%{}%"'.format(handleBy)
if handleBy else '',
' AND CREATE_TIME BETWEEN "{}" AND "{}"'.format(
createStart, createEnd)
if createStart and createEnd else '',
userId=currentUserr,
start=start,
size=size)
sqlTotal = """
SELECT COUNT(1)
FROM man_pro_bug AS bug
WHERE 1=1
{}{}{}{}{}{}{}{}
AND bug.PRO_ID IN (
SELECT DISTINCT member.PRO_ID
FROM man_pro_member AS member
WHERE member.USER_ID = '{userId}'
);
""".format(
' AND PRO_Name like "%{}%"'.format(proName) if proName else '',
' AND MODULE_NAME like "%{}%"'.format(modName)
if modName else '',
' AND BUG_TITLE like "%{}%"'.format(bugTitle)
if bugTitle else '',
' AND BUG_LEVEL = "{}"'.format(bugLevel) if bugLevel else '',
' AND BUG_STATUS like "%{}%"'.format(bugStatus)
if bugStatus else '',
' AND CREATE_BY like "%{}%"'.format(createBy)
if createBy else '',
' AND HANDLE_BY like "%{}%"'.format(handleBy)
if handleBy else '',
' AND CREATE_TIME BETWEEN "{}" AND "{}"'.format(
createStart, createEnd)
if createStart and createEnd else '',
userId=currentUserr,
)
Utils.log('查询问题列表SQL', sql)
list = PySQL.get(sql)
total = PySQL.count(sqlTotal)
return list, total
except Exception as e:
print('ERROR {}'.format(e))
Utils.log('ERROR {}'.format(e))
return [], 0
