<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.common.mapper.DeviceDeviceMapper">

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="com.common.entity.DeviceDevice">
        <id column="id" property="id" />
        <result column="created_at" property="createdAt" />
        <result column="updated_at" property="updatedAt" />
        <result column="deleted_at" property="deletedAt" />
        <result column="name" property="name" />
        <result column="ip" property="ip" />
        <result column="device_group" property="deviceGroup" />
        <result column="device_type" property="deviceType" />
        <result column="vendor" property="vendor" />
        <result column="product_name" property="productName" />
        <result column="organization_id" property="organizationId" />
        <result column="last_receive_time" property="lastReceiveTime" />
        <result column="agent_id" property="agentId" />
        <result column="detail_id" property="detailId" />
        <result column="control_agent_id" property="controlAgentId" />
        <result column="license_start_time" property="licenseStartTime" />
        <result column="license_end_time" property="licenseEndTime" />
        <result column="is_monitoring" property="isMonitoring" />
        <result column="security_scope_id" property="securityScopeId" />
        <result column="owner_id" property="ownerId" />
        <result column="ssh_config_id" property="sshConfigId" />
        <result column="status" property="status" />
        <result column="created_by_id" property="createdById" />
        <result column="decode_type" property="decodeType" />
        <result column="miss_policy" property="missPolicy" />
        <result column="tenant_id" property="tenantId" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <result column="create_by" property="createBy" />
        <result column="update_by" property="updateBy" />
        <result column="del_flag" property="delFlag" />
        <result column="manager_name" property="managerName" />
        <result column="today_parse_count" property="todayParseCount" />
        <result column="today_non_log_count" property="todayNonLogCount" />
        <result column="create_dept" property="createDept" />
        <result column="device_collect_id" property="deviceCollectId" />
    </resultMap>

    <!-- 基础查询列 -->
    <sql id="Base_Column_List">
        id, created_at , updated_at, deleted_at, name, ip, device_group, device_type,
        vendor, product_name, organization_id, last_receive_time, agent_id, detail_id,
        control_agent_id, license_start_time, license_end_time, is_monitoring,
        security_scope_id, owner_id, ssh_config_id, status, created_by_id, decode_type,
        miss_policy, tenant_id, create_time, update_time, create_by, update_by, del_flag,
        manager_name, today_parse_count, today_non_log_count, create_dept, device_collect_id
    </sql>

    <!-- 根据ID查询 -->
    <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE id = #{id}
    </select>

    <!-- 查询所有设备 -->
    <select id="selectAll" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
    </select>

    <!-- 根据IP查询 -->
    <select id="selectByIp" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE ip = #{ip} and   del_flag='0'
    </select>

    <!-- 根据名称模糊查询 -->
    <select id="selectByNameLike" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE name LIKE CONCAT('%', #{name}, '%')
    </select>

    <!-- 根据设备组查询 -->
    <select id="selectByDeviceGroup" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE device_group = #{deviceGroup}
    </select>

    <!-- 根据设备类型查询 -->
    <select id="selectByDeviceType" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE device_type = #{deviceType}
    </select>

    <!-- 根据组织ID查询 -->
    <select id="selectByOrganizationId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE organization_id = #{organizationId}
    </select>

    <!-- 根据状态查询 -->
    <select id="selectByStatus" parameterType="java.lang.Short" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE status = #{status}
    </select>

    <!-- 多条件组合查询 -->
    <select id="selectByCondition" parameterType="com.common.entity.DeviceDevice" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        <where>
            <if test="name != null and name != ''">
                AND name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="ip != null and ip != ''">
                AND ip = #{ip}
            </if>
            <if test="deviceGroup != null">
                AND device_group = #{deviceGroup}
            </if>
            <if test="deviceType != null">
                AND device_type = #{deviceType}
            </if>
            <if test="vendor != null and vendor != ''">
                AND vendor = #{vendor}
            </if>
            <if test="organizationId != null">
                AND organization_id = #{organizationId}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
            <if test="isMonitoring != null">
                AND is_monitoring = #{isMonitoring}
            </if>
        </where>
        ORDER BY created_at DESC
    </select>

    <!-- 动态条件查询 -->
    <select id="selectByMap" parameterType="java.util.Map" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        <where>
            <if test="name != null">
                AND name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="ip != null">
                AND ip = #{ip}
            </if>
            <if test="deviceGroup != null">
                AND device_group = #{deviceGroup}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
            <if test="vendor != null">
                AND vendor = #{vendor}
            </if>
            <if test="startTime != null">
                AND created_at >= #{startTime}
            </if>
            <if test="endTime != null">
                AND created_at &lt;= #{endTime}
            </if>
        </where>
    </select>

    <!-- 分页查询 -->
    <select id="selectByPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        ORDER BY id
        LIMIT #{limit} OFFSET #{offset}
    </select>

    <!-- 统计总数 -->
    <select id="count" resultType="java.lang.Long">
        SELECT COUNT(*) FROM device_device
    </select>

    <!-- 根据条件统计 -->
    <select id="countByCondition" parameterType="com.common.entity.DeviceDevice" resultType="java.lang.Long">
        SELECT COUNT(*) FROM device_device
        <where>
            <if test="name != null and name != ''">
                AND name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
            <if test="isMonitoring != null">
                AND is_monitoring = #{isMonitoring}
            </if>
        </where>
    </select>

    <!-- 查询监控中的设备 -->
    <select id="selectMonitoringDevices" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE is_monitoring = true
    </select>

    <!-- 查询未删除的设备 -->
    <select id="selectActiveDevices" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE del_flag = '0'
    </select>

    <!-- 根据厂商查询 -->
    <select id="selectByVendor" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM device_device
        WHERE vendor = #{vendor}
    </select>

</mapper>