Example #1
0
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
Example #2
0
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)
Example #3
0
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)
Example #4
0
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)
Example #5
0
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)
Example #6
0
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)
Example #7
0
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)
Example #8
0
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)
Example #9
0
 def obj_name(cls):
     return func.CONCAT_WS(' ', cls.FORMALNAME, cls.SHORTNAME)
Example #10
0
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