Python SQL.limit方法代碼示例

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_policies(sql_filter, sql_order, sql_limit):
    dbtp = get_db().table_prefix
    s = SQL(get_db())
    s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'], alt_name='p')

    str_where = ''
    _where = list()

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'search':
                _where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
            if k == 'state':
                _where.append('p.state={}'.format(sql_filter[k]))
            else:
                log.e('unknown filter field: {}\n'.format(k))
                return TPE_PARAM, s.total_count, 0, s.recorder

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    s.order_by('p.rank', True)

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    return err, s.total_count, s.page_index, s.recorder

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_logs(sql_filter, sql_order, sql_limit):
    s = SQL(get_db())
    s.select_from('syslog', ['id', 'user_name', 'user_surname', 'client_ip', 'code', 'log_time', 'message'], alt_name='l')

    str_where = ''
    _where = list()

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'log_user_name':
                _where.append('l.user_name="{}"'.format(sql_filter[k]))
            # elif k == 'search_record':
            #     _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'log_time' == sql_order['name']:
            s.order_by('l.log_time', _sort)
        # elif 'name' == sql_order['name']:
        #     s.order_by('h.name', _sort)
        # elif 'os_type' == sql_order['name']:
        #     s.order_by('h.os_type', _sort)
        # elif 'cid' == sql_order['name']:
        #     s.order_by('h.cid', _sort)
        # elif 'state' == sql_order['name']:
        #     s.order_by('h.state', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, s.total_count, s.recorder

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    return err, s.total_count, s.recorder

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_asset(sql_filter, sql_order, sql_limit):
    ss = SQL(get_db())
    ss.select_from('ops_auz', ['id', 'policy_id', 'rtype', 'rid', 'name'], alt_name='p')

    _where = list()
    _where.append('p.type=1')
    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'policy_id':
                # _where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
                _where.append('p.policy_id={}'.format(sql_filter[k]))
            elif k == 'search':
                _where.append('(p.name LIKE "%{filter}%")'.format(filter=sql_filter[k]))
            else:
                log.e('unknown filter field: {}\n'.format(k))
                return TPE_PARAM, 0, 0, {}
    if len(_where) > 0:
        ss.where('( {} )'.format(' AND '.join(_where)))

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'name' == sql_order['name']:
            ss.order_by('p.name', _sort)
        elif 'rtype' == sql_order['name']:
            ss.order_by('p.rtype', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, ss.total_count, 0, ss.recorder

    if len(sql_limit) > 0:
        ss.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = ss.query()
    if err != TPE_OK:
        return err, 0, 0, {}

    # print(ss.recorder)
    return TPE_OK, ss.total_count, ss.page_index, ss.recorder

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_records(handler, sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
    """
    獲取會話列表
    會話審計列表的顯示策略（下列的`審計`操作指為會話做標記、置為保留狀態、寫備注等）：
     1. 運維權限：可以查看自己的會話，但不能審計；
     2. 運維授權權限：可以查看所有會話，但不能審計；
     3. 審計權限：可以查看被授權的主機相關的會話，且可以審計；
     4. 審計授權權限：可以查看所有會話，且可以審計。
    """

    allow_uid = 0
    allow_hids = list()
    allow_all = False
    user = handler.get_current_user()
    if (user['privilege'] & TP_PRIVILEGE_OPS_AUZ) != 0 or (user['privilege'] & TP_PRIVILEGE_AUDIT_AUZ) != 0:
        allow_all = True
    if not allow_all:
        if (user['privilege'] & TP_PRIVILEGE_OPS) != 0:
            allow_uid = user.id
        if (user['privilege'] & TP_PRIVILEGE_AUDIT) != 0:
            s = SQL(get_db())
            s.select_from('audit_map', ['u_id', 'h_id', 'p_state', 'policy_auth_type', 'u_state', 'gu_state'], alt_name='a')
            s.where(
                'a.u_id={user_id} AND '
                'a.p_state={enable_state} AND'
                '('
                '((a.policy_auth_type={U2H} OR a.policy_auth_type={U2HG}) AND a.u_state={enable_state}) OR '
                '((a.policy_auth_type={UG2H} OR a.policy_auth_type={UG2HG}) AND a.u_state={enable_state} AND a.gu_state={enable_state})'
                ')'.format(enable_state=TP_STATE_NORMAL, user_id=user.id, U2H=TP_POLICY_AUTH_USER_HOST, U2HG=TP_POLICY_AUTH_USER_gHOST, UG2H=TP_POLICY_AUTH_gUSER_HOST, UG2HG=TP_POLICY_AUTH_gUSER_gHOST))
            err = s.query()
            if err != TPE_OK:
                return err, 0, []
            for h in s.recorder:
                if h.h_id not in allow_hids:
                    allow_hids.append(h.h_id)
            if len(allow_hids) == 0:
                return TPE_OK, 0, []

        if allow_uid == 0 and len(allow_hids) == 0:
            return TPE_FAILED, 0, []

    s = SQL(get_db())
    s.select_from('record', ['id', 'sid', 'user_id', 'host_id', 'acc_id', 'state', 'user_username', 'user_surname', 'host_ip', 'conn_ip', 'conn_port', 'client_ip', 'acc_username', 'protocol_type', 'protocol_sub_type', 'time_begin', 'time_end'], alt_name='r')

    str_where = ''
    _where = list()

    if len(sql_restrict) > 0:
        for k in sql_restrict:
            if k == 'state':
                _where.append('r.state IN ({})'.format(','.join([str(state) for state in sql_restrict[k]])))
            else:
                log.w('unknown restrict field: {}\n'.format(k))

    if len(sql_exclude) > 0:
        for k in sql_exclude:
            if k == 'state':
                _where.append('r.state NOT IN ({})'.format(','.join([str(state) for state in sql_exclude[k]])))
            else:
                log.w('unknown exclude field: {}\n'.format(k))

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'state':
                _where.append('r.state={}'.format(sql_filter[k]))
            # elif k == 'search_record':
            #     _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))

    if not allow_all:
        if allow_uid != 0:
            _where.append('r.user_id={uid}'.format(uid=allow_uid))
        if len(allow_hids) > 0:
            hids = [str(h) for h in allow_hids]
            _where.append('r.host_id IN ({hids})'.format(hids=','.join(hids)))

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'id' == sql_order['name']:
            s.order_by('r.id', _sort)
        elif 'time_begin' == sql_order['name']:
            s.order_by('r.time_begin', _sort)
        elif 'sid' == sql_order['name']:
            s.order_by('r.sid', _sort)
        # elif 'cid' == sql_order['name']:
        #     s.order_by('h.cid', _sort)
        # elif 'state' == sql_order['name']:
        #     s.order_by('h.state', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, s.total_count, s.recorder

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
#.........這裏部分代碼省略.........

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_groups(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
    dbtp = get_db().table_prefix
    s = SQL(get_db())
    s.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')

    str_where = ''
    _where = list()

    # if len(sql_restrict) > 0:
    #     for k in sql_restrict:
    #         if k == 'ops_policy_id':
    #             _where.append('g.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype=2)'.format(dbtp=dbtp, pid=sql_exclude[k]))
    #         else:
    #             log.w('unknown restrict field: {}\n'.format(k))

    if len(sql_exclude) > 0:
        for k in sql_exclude:
            # if k == 'group_id':
            #     _where.append('u.id NOT IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_exclude[k]))
            if k == 'ops_policy_id':
                pid = sql_exclude[k]['pid']
                gtype = sql_exclude[k]['gtype']
                _where.append('g.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'.format(dbtp=dbtp, pid=pid, rtype=gtype))
            elif k == 'auditor_policy_id':
                pid = sql_exclude[k]['pid']
                gtype = sql_exclude[k]['gtype']
                _where.append('g.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'.format(dbtp=dbtp, pid=pid, ptype=TP_POLICY_OPERATOR, rtype=gtype))
            elif k == 'auditee_policy_id':
                pid = sql_exclude[k]['pid']
                gtype = sql_exclude[k]['gtype']
                _where.append('g.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'.format(dbtp=dbtp, pid=pid, ptype=TP_POLICY_ASSET, rtype=gtype))
            else:
                log.w('unknown exclude field: {}\n'.format(k))

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'type':
                _where.append('g.type={filter}'.format(filter=sql_filter[k]))
            elif k == 'state':
                _where.append('g.state={filter}'.format(filter=sql_filter[k]))
            elif k == 'search':
                _where.append('(g.name LIKE "%{filter}%" OR g.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
            else:
                log.e('unknown filter field: {}\n'.format(k))
                return TPE_PARAM, 0, 0, {}

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'name' == sql_order['name']:
            s.order_by('g.name', _sort)
        elif 'state' == sql_order['name']:
            s.order_by('g.state', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, 0, 0, {}

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    return err, s.total_count, s.page_index, s.recorder

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_group_with_member(sql_filter, sql_order, sql_limit):
    """
    獲取主機組列表，以及每個組的總成員數以及不超過5個的成員
    """
    # 首先獲取要查詢的組的信息
    sg = SQL(get_db())
    sg.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')

    _where = list()
    _where.append('g.type={}'.format(TP_GROUP_HOST))

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'search':
                _where.append('(g.name LIKE "%{}%" OR g.desc LIKE "%{}%")'.format(sql_filter[k], sql_filter[k]))
            elif k == 'state':
                _where.append('(g.state={filter})'.format(filter=sql_filter[k]))

    if len(_where) > 0:
        sg.where('( {} )'.format(' AND '.join(_where)))

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'name' == sql_order['name']:
            sg.order_by('g.name', _sort)
        elif 'state' == sql_order['name']:
            sg.order_by('g.state', _sort)
        else:
            log.e('unknown order field.\n')
            return TPE_PARAM, sg.total_count, sg.recorder

    if len(sql_limit) > 0:
        sg.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = sg.query()
    if err != TPE_OK or len(sg.recorder) == 0:
        return err, sg.total_count, sg.recorder

    for g in sg.recorder:
        g['member_count'] = 0
        g['members'] = []
        g['_mid'] = []  # 臨時使用，構建此組的前5個成員的id

    # 對於本次要返回的用戶組，取其中每一個組內成員的基本信息（id/用戶名/真實名稱等）
    groups = [g['id'] for g in sg.recorder]
    sgm = SQL(get_db())
    sgm.select_from('group_map', ['gid', 'mid'], alt_name='gm')
    # sgm.limit(0, 5)

    _where = list()
    # _where.append('({})'.format(' OR '.join(['gm.gid={}'.format(gid) for gid in groups])))
    _where.append('gm.type={}'.format(TP_GROUP_HOST))
    _where.append('gm.gid IN ({})'.format(','.join([str(gid) for gid in groups])))
    str_where = '( {} )'.format(' AND '.join(_where))
    sgm.where(str_where)
    err = sgm.query()
    if err != TPE_OK or len(sgm.recorder) == 0:
        return err, sg.total_count, sg.recorder

    for g in sg.recorder:
        for gm in sgm.recorder:
            if gm['gid'] == g['id']:
                g['member_count'] += 1
                if len(g['_mid']) < 5:
                    g['_mid'].append(gm['mid'])

    # 將得到的賬號id合並到列表中並去重，然後獲取這些賬號的信息
    users = []
    for g in sg.recorder:
        users.extend(g['_mid'])
    users = list(set(users))

    su = SQL(get_db())
    su.select_from('host', ['id', 'os_type', 'name', 'ip', 'router_ip', 'router_port', 'cid'], alt_name='h')

    su.where('h.id IN ({})'.format(','.join([str(uid) for uid in users])))
    su.order_by('h.ip')
    err = su.query()
    if err != TPE_OK or len(su.recorder) == 0:
        return err, sg.total_count, sg.recorder

    # 現在可以將具體的用戶信息追加到組信息中了
    for g in sg.recorder:
        for u in su.recorder:
            for m in g['_mid']:
                if u['id'] == m:
                    g['members'].append(u)

    return err, sg.total_count, sg.recorder

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_hosts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
    s = SQL(get_db())
    s.select_from('host', ['id', 'type', 'os_type', 'os_ver', 'name', 'ip', 'router_ip', 'router_port', 'state', 'acc_count', 'cid', 'desc'], alt_name='h')

    str_where = ''
    _where = list()

    if len(sql_restrict) > 0:
        for k in sql_restrict:
            if k == 'group_id':
                _where.append('h.id IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(get_db().table_prefix, TP_GROUP_HOST, sql_restrict[k]))
            else:
                log.w('unknown restrict field: {}\n'.format(k))

    if len(sql_exclude) > 0:
        for k in sql_exclude:
            if k == 'group_id':
                _where.append('h.id NOT IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(get_db().table_prefix, TP_GROUP_HOST, sql_exclude[k]))
            elif k == 'ops_policy_id':
                _where.append('h.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'.format(dbtp=get_db().table_prefix, pid=sql_exclude[k], rtype=TP_HOST))
            elif k == 'auditee_policy_id':
                _where.append('h.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'.format(dbtp=get_db().table_prefix, pid=sql_exclude[k], ptype=TP_POLICY_ASSET, rtype=TP_HOST))
            else:
                log.w('unknown exclude field: {}\n'.format(k))

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'state':
                _where.append('h.state={}'.format(sql_filter[k]))
            elif k == 'search':
                _where.append('(h.name LIKE "%{filter}%" OR h.ip LIKE "%{filter}%" OR h.router_ip LIKE "%{filter}%" OR h.desc LIKE "%{filter}%" OR h.cid LIKE "%{filter}%")'.format(filter=sql_filter[k]))
            elif k == 'host_group':
                shg = SQL(get_db())
                shg.select_from('group_map', ['mid'], alt_name='g')
                shg.where('g.type={} AND g.gid={}'.format(TP_GROUP_HOST, sql_filter[k]))
                err = shg.query()
                if err != TPE_OK:
                    return err, 0, 1, []
                if len(shg.recorder) == 0:
                    return TPE_OK, 0, 1, []
                h_list = ','.join([str(i['mid']) for i in shg.recorder])
                _where.append('h.id IN ({})'.format(h_list))

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'ip' == sql_order['name']:
            s.order_by('h.ip', _sort)
        elif 'name' == sql_order['name']:
            s.order_by('h.name', _sort)
        elif 'os_type' == sql_order['name']:
            s.order_by('h.os_type', _sort)
        elif 'cid' == sql_order['name']:
            s.order_by('h.cid', _sort)
        elif 'state' == sql_order['name']:
            s.order_by('h.state', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, s.total_count, s.page_index, s.recorder

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    return err, s.total_count, s.page_index, s.recorder

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_accounts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
    db = get_db()
    dbtp = db.table_prefix

    s = SQL(db)
    # s.select_from('acc', ['id', 'host_id', 'host_ip', 'router_ip', 'router_port', 'username', 'protocol_type', 'auth_type', 'state'], alt_name='a')
    s.select_from('acc', ['id', 'host_id', 'username', 'protocol_type', 'auth_type', 'state', 'username_prompt', 'password_prompt'], alt_name='a')

    str_where = ''
    _where = list()

    if len(sql_restrict) > 0:
        for k in sql_restrict:
            if k == 'group_id':
                _where.append('a.id IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(dbtp, TP_GROUP_ACCOUNT, sql_restrict[k]))
            else:
                log.w('unknown restrict field: {}\n'.format(k))

    if len(sql_exclude) > 0:
        for k in sql_exclude:
            if k == 'group_id':
                _where.append('a.id NOT IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(dbtp, TP_GROUP_ACCOUNT, sql_exclude[k]))
            elif k == 'ops_policy_id':
                _where.append('a.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_ACCOUNT))
            else:
                log.w('unknown exclude field: {}\n'.format(k))

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'search':
                _where.append('(a.username LIKE "%{filter}%" OR a.host_ip LIKE "%{filter}%" OR a.router_ip LIKE "%{filter}%")'.format(filter=sql_filter[k]))
                # _where.append('(a.username LIKE "%{filter}%")'.format(filter=sql_filter[k]))

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'username' == sql_order['name']:
            s.order_by('a.username', _sort)
        elif 'protocol_type' == sql_order['name']:
            s.order_by('a.protocol_type', _sort)
        elif 'state' == sql_order['name']:
            s.order_by('a.state', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, s.total_count, 1, s.recorder

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    if err != TPE_OK:
        return err, 0, 1, None

    # 得到主機id列表，然後查詢相關主機的詳細信息
    host_ids = []
    for _acc in s.recorder:
        if _acc.host_id not in host_ids:
            host_ids.append(_acc.host_id)
    if len(host_ids) == 0:
        return TPE_OK, 0, 1, None
    s_host = SQL(db)
    s_host.select_from('host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h')
    str_host_ids = ','.join([str(i) for i in host_ids])
    s_host.where('h.id IN ({ids})'.format(ids=str_host_ids))
    err = s_host.query()
    if err != TPE_OK:
        return err, 0, None
    hosts = {}
    for _host in s_host.recorder:
        if _host.id not in hosts:
            hosts[_host.id] = _host

    for _acc in s.recorder:
        _acc['_host'] = hosts[_acc.host_id]

    return err, s.total_count, s.page_index, s.recorder

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_users(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
    dbtp = get_db().table_prefix
    s = SQL(get_db())
    s.select_from('user', ['id', 'type', 'auth_type', 'username', 'surname', 'role_id', 'state', 'email', 'last_login'],
                  alt_name='u')
    s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'})

    _where = list()

    if len(sql_restrict) > 0:
        for k in sql_restrict:
            if k == 'group_id':
                _sql = 'u.id IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
                _where.append(_sql.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_restrict[k]))
            else:
                log.w('unknown restrict field: {}\n'.format(k))

    if len(sql_exclude) > 0:
        for k in sql_exclude:
            if k == 'group_id':
                _where.append(
                    'u.id NOT IN ('
                    'SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
                    ''.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_exclude[k]))
            elif k == 'ops_policy_id':
                _where.append(
                    'u.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
                    ''.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_USER))
            elif k == 'auditor_policy_id':
                _where.append(
                    'u.id NOT IN ('
                    'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
                    'AND `type`={ptype} AND rtype={rtype}'
                    ')'.format(dbtp=dbtp, pid=sql_exclude[k], ptype=TP_POLICY_OPERATOR, rtype=TP_USER))
            elif k == 'auditee_policy_id':
                _where.append(
                    'u.id NOT IN ('
                    'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
                    'AND `type`={ptype} AND rtype={rtype}'
                    ')'.format(dbtp=dbtp, pid=sql_exclude[k], ptype=TP_POLICY_ASSET, rtype=TP_USER))
            else:
                log.w('unknown exclude field: {}\n'.format(k))

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'role':
                _where.append('u.role_id={filter}'.format(filter=sql_filter[k]))
            elif k == 'type':
                _where.append('u.type={filter}'.format(filter=sql_filter[k]))
            elif k == 'state':
                _where.append('u.state={filter}'.format(filter=sql_filter[k]))
            elif k == 'search':
                _where.append('('
                              'u.username LIKE "%{filter}%" '
                              'OR u.surname LIKE "%{filter}%" '
                              'OR u.email LIKE "%{filter}%" '
                              'OR u.desc LIKE "%{filter}%"'
                              ')'.format(filter=sql_filter[k]))

    if len(_where) > 0:
        s.where('( {} )'.format(' AND '.join(_where)))

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'username' == sql_order['name']:
            s.order_by('u.username', _sort)
        elif 'surname' == sql_order['name']:
            s.order_by('u.surname', _sort)
        elif 'role_id' == sql_order['name']:
            s.order_by('u.role_id', _sort)
        elif 'state' == sql_order['name']:
            s.order_by('u.state', _sort)
        elif 'type' == sql_order['name']:
            s.order_by('u.type', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, 0, 0, {}

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    return err, s.total_count, s.page_index, s.recorder

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import limit [as 別名]
def get_all_remotes(handler, sql_filter, sql_order, sql_limit):
    s = SQL(get_db())
    s.select_from('host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h')

    str_where = ''
    _where = list()

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'state':
                _where.append('h.state={}'.format(sql_filter[k]))
            elif k == 'search':
                _where.append('(h.name LIKE "%{k}%" OR h.ip LIKE "%{k}%" OR h.router_ip LIKE "%{k}%")'.format(k=sql_filter[k]))
            elif k == 'host_group':
                shg = SQL(get_db())
                shg.select_from('group_map', ['mid'], alt_name='g')
                shg.where('g.type={} AND g.gid={}'.format(TP_GROUP_HOST, sql_filter[k]))
                err = shg.query()
                if err != TPE_OK:
                    return err, 0, 1, []
                if len(shg.recorder) == 0:
                    return TPE_OK, 0, 1, []
                h_list = ','.join([str(i['mid']) for i in shg.recorder])
                _where.append('h.id IN ({})'.format(h_list))

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'id' == sql_order['name']:
            s.order_by('h.id', _sort)
        elif 'ip' == sql_order['name']:
            s.order_by('h.ip', _sort)
        elif 'name' == sql_order['name']:
            s.order_by('h.name', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, s.total_count, s.page_index, s.recorder

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    if err != TPE_OK:
        return err, 0, 1, []

    ret = s.recorder
    for h in ret:
        h['h_id'] = h.id
        h['h_state'] = TP_STATE_NORMAL
        h['gh_state'] = TP_STATE_NORMAL
        h['h_name'] = h.name
        del h['id']
        del h['name']
        h['accounts_'] = []

        sa = SQL(get_db())
        sa.select_from('acc', ['id', 'protocol_type', 'protocol_port', 'username'], alt_name='a')
        sa.where('a.host_id={}'.format(h.h_id))
        sa.order_by('a.username', True)
        err = sa.query()
        if err != TPE_OK:
            continue
        for a in sa.recorder:
            h['accounts_'].append({
                'a_name': a.username,
                'id': a.id,
                'a_id': a.id,
                'policy_auth_type': TP_POLICY_AUTH_USER_ACC,
                'uni_id': 'none',
                'a_state': TP_STATE_NORMAL,
                'ga_state': TP_STATE_NORMAL,
                'protocol_type': a.protocol_type,
                'h_id': h.h_id,
                'policy_': {
                    'flag_ssh': TP_FLAG_ALL,
                    'flag_rdp': TP_FLAG_ALL
                }
            })

    # print(json.dumps(s.recorder, indent='  '))
    return err, s.total_count, s.page_index, s.recorder