def get_samples_query():
query = Sample.query \
.with_entities(Sample,
Analysis,
func.group_concat(func.CONCAT_WS
(';', Variation.id, Variation.tier_label)
.distinct()).label("variations"),
ObservedMap.id.label('observed_map_id'),
func.count(Error.id.distinct()).label('total_errors'),
func.group_concat(func.CONCAT_WS
(';', Map.id, Map.construct_id)
.distinct()).label("map"),
func.group_concat(func.CONCAT_WS
(';', ObservedMap.id, ObservedMap.status)
.distinct()).label("total_maps")
) \
.outerjoin((Analysis, Sample.id == Analysis.sample_id)) \
.outerjoin(MapAnalysis) \
.outerjoin(Map) \
.outerjoin(ObservedMap) \
.outerjoin(Error) \
.outerjoin(Variation) \
.group_by(Analysis.id)
return query
def user_query():
args = utility.get_args(request)
draw = args.get('draw', "") # 这个值作者会直接返回给前台
start = args.get('start', "") # 从多少开始
length = args.get('length', "") # 数据长度
search = args.get('search[value]', "") # 获取前台传过来的过滤条件
order_column = args.get('order[0][column]', "") # 哪一列排序，从0开始
order_dir = args.get('order[0][dir]', "") # asc desc 升序或者降序
order_name = args.get('columns[' + order_column + '][name]',
"") # 需要html里定义name
if order_name == "users.online_client_count": #特殊处理
order = asc if order_dir == "asc" else desc
users = db.session.query(User).join(User.software).join(
Software.admins).filter(Admin.id == current_user.id).filter(
func.CONCAT_WS(
',', User.user_name, User.qq, User.email, User.serial_no,
User.remark,
Software.name).like('%' + search + '%')).order_by(
order(User.online_client_count)).offset(start).limit(
length).all()
else:
users = db.session.query(User).join(User.software).join(
Software.admins).filter(Admin.id == current_user.id).filter(
func.CONCAT_WS(
',', User.user_name, User.qq, User.email, User.serial_no,
User.remark,
Software.name).like('%' + search + '%')).order_by(
text(order_name + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(User.id)).join(
User.software).join(
Software.admins).filter(Admin.id == current_user.id).scalar()
recordsFiltered = recordsTotal if search == "" else len(users)
data = []
for user in users:
item = [
user.id, user.software.name, user.user_name, user.password,
user.password_question, user.password_answer, user.qq, user.email,
user.phone, user.version, user.serial_no, user.token,
"{}/{}".format(user.online_client_count, user.client_count),
user.is_bind, user.remark, user.unbind_date, user.terminate_date,
user.is_enable, user.update_time, user.create_time
]
data.append(item)
result = {
"draw": draw,
"recordsTotal": recordsTotal,
"recordsFiltered": recordsFiltered,
"data": data
}
return utility.get_json(result)
def operatelog_query():
args = utility.get_args(request)
draw = args.get('draw', "") # 这个值作者会直接返回给前台
start = args.get('start', "") # 从多少开始
length = args.get('length', "") # 数据长度
search = args.get('search[value]', "") # 获取前台传过来的过滤条件
order_column = args.get('order[0][column]', "") # 哪一列排序，从0开始
order_dir = args.get('order[0][dir]', "") # asc desc 升序或者降序
operatelogs = []
recordsTotal = 0
if admin_permission.can():
operatelogs = db.session.query(OperateLog).join(
OperateLog.admin).filter(
func.CONCAT_WS(
',', Admin.user_name, OperateLog.operate,
OperateLog.remark).like('%' + search + '%')).order_by(
text(str(int(order_column) + 1) + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(OperateLog.id)).scalar()
else:
operatelogs = db.session.query(OperateLog).join(
OperateLog.admin).filter(
or_(Admin.id == current_user.id,
Admin.superior_id == current_user.id)).filter(
func.CONCAT_WS(',', Admin.user_name,
OperateLog.operate, OperateLog.remark).
like('%' + search + '%')).order_by(
text(str(int(order_column) + 1) + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(OperateLog.id)).join(
OperateLog.admin).filter(
or_(Admin.id == current_user.id,
Admin.superior_id == current_user.id)).scalar()
recordsFiltered = recordsTotal if search == "" else len(operatelogs)
data = []
for operatelog in operatelogs:
item = [
operatelog.id, operatelog.admin.user_name, operatelog.operate,
operatelog.remark, operatelog.create_time
]
data.append(item)
result = {
"draw": draw,
"recordsTotal": recordsTotal,
"recordsFiltered": recordsFiltered,
"data": data
}
return utility.get_json(result)
def software_query():
args = utility.get_args(request)
draw = args.get('draw', "") # 这个值作者会直接返回给前台
start = args.get('start', "") # 从多少开始
length = args.get('length', "") # 数据长度
search = args.get('search[value]', "") # 获取前台传过来的过滤条件
order_column = args.get('order[0][column]', "") # 哪一列排序，从0开始
order_dir = args.get('order[0][dir]', "") # asc desc 升序或者降序
order_name = args.get('columns[' + order_column + '][name]',
"") # 需要html里定义name
softwares = db.session.query(Software).join(
Software.admins).filter(Admin.id == current_user.id).filter(
func.CONCAT_WS(
',', Software.name, Software.message, Software.version,
Admin.user_name).like('%' + search + '%')).order_by(
text(order_name + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(Software.id)).scalar()
recordsFiltered = recordsTotal if search == "" else len(softwares)
data = []
for software in softwares:
item = [
software.id, software.name, software.register_hour,
software.unbind_hour, software.wait_hour, software.client_count,
software.is_bind, software.message,
utility.get_short(software.script, 16), software.version,
software.new_version, software.new_url,
software.get_admins_names(), software.is_enable,
software.update_time, software.create_time
]
data.append(item)
result = {
"draw": draw,
"recordsTotal": recordsTotal,
"recordsFiltered": recordsFiltered,
"data": data
}
return utility.get_json(result)
def log_query():
args = utility.get_args(request)
draw = args.get('draw', "") # 这个值作者会直接返回给前台
start = args.get('start', "") # 从多少开始
length = args.get('length', "") # 数据长度
search = args.get('search[value]', "") # 获取前台传过来的过滤条件
order_column = args.get('order[0][column]', "") # 哪一列排序，从0开始
order_dir = args.get('order[0][dir]', "") # asc desc 升序或者降序
log_month = args.get('log_month', "")
# if db.engine.dialect.has_table(db.engine.connect(), log_month):
months = Log.get_months()
month = time.strftime("%Y%m") if log_month == "" else log_month
if month not in months:
return "{}"
MyLog = Log.model(log_month)
logs = db.session.query(MyLog).filter(
func.CONCAT_WS(',', MyLog.ip, MyLog.uri,
MyLog.args).like('%' + search + '%')).order_by(
text(str(int(order_column) + 1) + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(MyLog.id)).scalar()
recordsFiltered = recordsTotal if search == "" else len(logs)
data = []
for log in logs:
item = [
log.id, log.ip, log.country, log.region, log.city, log.uri,
log.args, log.result, log.create_time
]
data.append(item)
result = {
"draw": draw,
"recordsTotal": recordsTotal,
"recordsFiltered": recordsFiltered,
"data": data
}
return utility.get_json(result)
def cardtype_query():
args = utility.get_args(request)
draw = args.get('draw', "") # 这个值作者会直接返回给前台
start = args.get('start', "") # 从多少开始
length = args.get('length', "") # 数据长度
search = args.get('search[value]', "") # 获取前台传过来的过滤条件
order_column = args.get('order[0][column]', "") # 哪一列排序，从0开始
order_dir = args.get('order[0][dir]', "") # asc desc 升序或者降序
cardtypes = db.session.query(CardType).join(CardType.software).join(
Software.admins).filter(Admin.id == current_user.id).filter(
func.CONCAT_WS(
',', Software.name, CardType.day, CardType.expired_day,
CardType.price).like('%' + search + '%')).order_by(
text(str(int(order_column) + 1) + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(CardType.id)).join(
CardType.software).join(
Software.admins).filter(Admin.id == current_user.id).scalar()
recordsFiltered = recordsTotal if search == "" else len(cardtypes)
data = []
for cardtype in cardtypes:
item = [
cardtype.id, cardtype.software.name, cardtype.day,
cardtype.expired_day, cardtype.price, cardtype.is_enable,
cardtype.update_time, cardtype.create_time
]
data.append(item)
result = {
"draw": draw,
"recordsTotal": recordsTotal,
"recordsFiltered": recordsFiltered,
"data": data
}
return utility.get_json(result)
def card_query():
args = utility.get_args(request)
draw = args.get('draw', "") # 这个值作者会直接返回给前台
start = args.get('start', "") # 从多少开始
length = args.get('length', "") # 数据长度
search = args.get('search[value]', "") # 获取前台传过来的过滤条件
order_column = args.get('order[0][column]', "") # 哪一列排序，从0开始
order_dir = args.get('order[0][dir]', "") # asc desc 升序或者降序
order_name = args.get('columns[' + order_column + '][name]',
"") # 需要html里定义name
'''
SELECT * FROM
(SELECT cards.*, admins.user_name AS admin_name, my_card_types.card_type_name FROM `cards` LEFT JOIN `admins` ON cards.admin_id = admins.id
LEFT JOIN (SELECT card_types.*,CONCAT(softwares.name, '(', CAST(card_types.day AS CHAR), '天卡)') AS card_type_name FROM card_types
LEFT JOIN softwares ON card_types.software_id = softwares.id) AS my_card_types ON cards.type_id = my_card_types.id) AS t1
WHERE CONCAT_WS(',',`card_no`,`remark`,`admin_name`,`card_type_name`)
LIKE '%"..search.."%' "..order.." LIMIT "..start..", "..length
'''
# 点卡需要用户间彼此隔离
cards = []
recordsTotal = 0
if admin_permission.can():
cards = db.session.query(Card).join(Card.admin).join(
Card.card_type).join(CardType.software).filter(
func.CONCAT_WS(
',', Card.card_no, Card.remark, Admin.user_name,
func.CONCAT(
Software.name, "(", CardType.day,
'天卡)')).like('%' + search + '%')).order_by(
text(order_name + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(Card.id)).scalar()
else:
cards = db.session.query(Card).join(Card.admin).join(
Card.card_type).join(CardType.software).filter(
or_(Admin.id == current_user.id,
Admin.superior_id == current_user.id)).filter(
func.CONCAT_WS(
',', Card.card_no, Card.remark, Admin.user_name,
func.CONCAT(
Software.name, "(", CardType.day,
'天卡)')).like('%' + search + '%')).order_by(
text(order_name + " " + order_dir)).offset(
start).limit(length).all()
recordsTotal = db.session.query(func.count(Card.id)).join(
Card.admin).join(Card.card_type).join(CardType.software).filter(
or_(Admin.id == current_user.id,
Admin.superior_id == current_user.id)).scalar()
recordsFiltered = recordsTotal if search == "" else len(cards)
data = []
for card in cards:
item = [
card.id, card.admin.user_name,
card.card_type.get_name(), card.card_no, card.password,
card.remark, card.is_used, card.is_enable,
card.create_time + timedelta(days=card.card_type.expired_day),
card.update_time, card.create_time
]
data.append(item)
result = {
"draw": draw,
"recordsTotal": recordsTotal,
"recordsFiltered": recordsFiltered,
"data": data
}
return utility.get_json(result)
def admin_query():
args = utility.get_args(request)
draw = args.get('draw', "") # 这个值作者会直接返回给前台
start = args.get('start', "") # 从多少开始
length = args.get('length', "") # 数据长度
search = args.get('search[value]', "") # 获取前台传过来的过滤条件
order_column = args.get('order[0][column]', "") # 哪一列排序，从0开始
order_dir = args.get('order[0][dir]', "") # asc desc 升序或者降序
Superior = db.aliased(Admin)
admins = []
recordsTotal = 0
if admin_permission.can():
# Left join
admins = db.session.query(Admin, Superior).join(
Superior, Admin.superior,
isouter=True).join(Admin.softwares).filter(
func.CONCAT_WS(
',', Admin.user_name, Superior.user_name,
Software.name).like('%' + search + '%')).order_by(
text(str(int(order_column) + 1) + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(Admin.id)).scalar()
else:
# Left join
admins = db.session.query(Admin, Superior).join(
Superior, Admin.superior,
isouter=True).join(Admin.softwares).filter(
Admin.superior_id == current_user.id).filter(
func.CONCAT_WS(
',', Admin.user_name, Superior.user_name,
Software.name).like('%' + search + '%')).order_by(
text(str(int(order_column) + 1) + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(
Admin.id)).filter(Admin.superior_id == current_user.id).scalar()
recordsFiltered = recordsTotal if search == "" else len(admins)
data = []
for admin, superior in admins:
item = [
admin.id, admin.user_name, admin.password,
admin.get_roles_names(),
admin.superior.user_name if admin.superior else "",
admin.get_subordinates_names(),
admin.get_softwares_names(), admin.is_enable, admin.update_time,
admin.create_time
]
data.append(item)
result = {
"draw": draw,
"recordsTotal": recordsTotal,
"recordsFiltered": recordsFiltered,
"data": data
}
return utility.get_json(result)
def obj_name(cls):
return func.CONCAT_WS(' ', cls.FORMALNAME, cls.SHORTNAME)
def fetch_requests(request_id=None, role='client'):
""""
Provides SbS request records
"""
results = None
try:
query = Request.query \
.with_entities(Request,
Analysis.id.label("analysis_id"),
func.group_concat(func.CONCAT_WS
(';', Sample.curr_alpha_analysis,
Analysis.id, Analysis.type)
.distinct()).label("analysis_type"),
func.group_concat(func.CONCAT_WS
(';', Analysis.id, Analysis.load_date)
.distinct()).label("load_date"),
Crop.organism.label("organism"),
TxMethod.method_name.label("tx_method"),
func.count(RequestComment.id.distinct()).label('total_comments'),
func.count(Error.id.distinct()).label('total_errors'),
func.count(Sample.id.distinct()).label('total_samples')
) \
.outerjoin(Sample) \
.outerjoin((Analysis, Sample.id == Analysis.sample_id)) \
.outerjoin(Crop) \
.outerjoin(TxMethod) \
.outerjoin(RequestComment) \
.outerjoin(Error) \
.group_by(Request.id)
if request_id:
query = query.filter(request_id == Request.id)
results = query.one_or_none()
else:
results = query.all()
except Exception as e:
logger.error('An error occurred in get request query : {}'.format(
str(e)))
raise Exception('Failed to get request as error in query: ', e)
requests_list = []
total_requests = 0
if results:
if request_id:
total_requests = 1
if role and role == "client":
requests_list.append(
get_request(results).browse_request_client_as_dict())
else:
requests_list.append(
get_request(results).browse_request_as_dict())
else:
total_requests = len(results)
for result in results:
if role and role == "client":
requests_list.append(
get_request(result).browse_request_client_as_dict())
else:
requests_list.append(
get_request(result).browse_request_as_dict())
requests_list = sorted(requests_list,
key=lambda x: x['load_date'],
reverse=True)
for obj in requests_list:
load_date = obj['load_date'].split(" ")[0]
obj['load_date'] = load_date
requests = {'requests': requests_list, 'total_requests': total_requests}
logger.info('total request is :[{}]'.format(total_requests))
return requests