<?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.bonus.sgzb.material.mapper.IotMachineMapper">

    <insert id="add">
        insert into iot_machine
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="iotType != null">iot_type,</if>
            <if test="iotCode != null and iotCode != ''">iot_code,</if>
            iot_status,
            <if test="qrCode != null">qr_code,</if>
            bind_status,
            del_flag,
            <if test="createBy != null">create_by,</if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="iotType != null">#{iotType},</if>
            <if test="iotCode != null and iotCode != ''">#{iotCode},</if>
            0,
            <if test="qrCode != null">#{qrCode},</if>
            1,
            0,
            <if test="createBy != null">#{createBy},</if>
            NOW()
        </trim>

    </insert>
    <insert id="bind">
        insert into iot_machine_bind
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="iotId != null">iot_id,</if>
            <if test="typeId != null and typeId != ''">type_id,</if>
            <if test="maCode != null">ma_code,</if>
            <if test="binder != null">binder,</if>
            bind_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="iotId != null">#{iotId},</if>
            <if test="typeId != null and typeId != ''">#{typeId},</if>
            <if test="maCode != null">#{maCode},</if>
            <if test="binder != null">#{binder},</if>
            NOW()
        </trim>
    </insert>

    <update id="update">
        UPDATE iot_machine
        <trim prefix="SET" suffixOverrides=",">
            <if test="iotType != null ">iot_type = #{iotType},</if>
            <if test="iotCode != null">iot_code = #{iotCode},</if>
            <if test="iotStatus != null">iot_status = #{iotStatus},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            update_time = NOW()
        </trim>
        WHERE id = #{iotId}
    </update>

    <update id="deleteById">
        UPDATE iot_machine SET del_flag = '2' WHERE id = #{iotId}
    </update>
    <update id="updateBindStatus">
        UPDATE iot_machine SET bind_status = '0', update_time = NOW() WHERE id = #{iotId}
    </update>
    <update id="unbind">
        UPDATE iot_machine_bind SET unbinder = #{unBinder}, unbind_time = NOW() WHERE id = #{id}
    </update>
    <update id="updateUnBindStatus">
        UPDATE iot_machine SET bind_status = '1', update_time = NOW() WHERE id = #{iotId}
    </update>

    <select id="selectQrCode" resultType="java.lang.Integer">
        SELECT
            COUNT(*)
        FROM
            iot_machine
        WHERE
            del_flag = '0'
            AND qr_code = #{qrCode}
    </select>
        <select id="selectIotCode" resultType="com.bonus.sgzb.material.domain.IotVo">
        SELECT
            id AS iotId,
            iot_code AS iotCode,
            iot_type AS iotType,
            iot_status AS iotStatus,
            qr_code AS qrCode
        FROM
            iot_machine
        WHERE
            del_flag = '0'
            <if test="iotType != null and iotType != ''" >
                AND iot_type = #{iotType}
            </if>
            <if test="iotCode != null and iotCode != ''" >
                AND iot_code = #{iotCode}
            </if>
    </select>
    <select id="getIotList" resultType="com.bonus.sgzb.material.domain.IotVo">
        SELECT
            im.id AS iotId,
            im.iot_code AS iotCode,
            im.iot_type AS iotType,
            im.iot_status AS iotStatus,
            sd.name AS iotTypeName,
            im.qr_code AS qrCode,
            im.bind_status AS bindStatus
        FROM
            iot_machine im
        LEFT JOIN sys_dic sd on im.iot_type = sd.id
        WHERE
        del_flag = '0'
        <if test="keyWord != null  and keyWord != ''">
            AND (iot_type like concat('%', #{keyWord}, '%') or
            iot_code like concat('%', #{keyWord}, '%')
            )
        </if>
    </select>
    <select id="selectById" resultType="com.bonus.sgzb.material.domain.IotVo">
        SELECT
            id AS iotId,
            iot_code AS iotCode,
            iot_type AS iotType,
            iot_status AS iotStatus,
            qr_code AS qrCode,
            bind_status AS bindStatus
        FROM
            iot_machine
        WHERE
            del_flag = '0'
            AND id = #{iotId}
    </select>
    <select id="getTypeList" resultType="com.bonus.sgzb.material.domain.IotVo">
        SELECT
            imb.id AS id,
            im.iot_type AS iotType,
            sd.name AS iotTypeName,
            im.iot_code AS iotCode,
            im.iot_status AS iotStatus,
            imb.iot_id AS iotId,
            imb.bind_time AS bindTime,
            im.bind_status AS bindStatus
        FROM
            iot_machine im
            LEFT JOIN sys_dic sd on im.iot_type = sd.id
            LEFT JOIN iot_machine_bind imb ON im.id = imb.iot_id
        WHERE
            im.del_flag = '0'
        AND im.bind_status = '0'
        AND imb.unbind_time IS NULL
        AND imb.ma_code = #{maCode}
        <if test="keyWord != null  and keyWord != ''">
            AND (
                im.iot_type like concat('%', #{keyWord}, '%') or
                im.iot_code like concat('%', #{keyWord}, '%')
            )
        </if>
    </select>

    <select id="selectList" resultType="com.bonus.sgzb.material.domain.IotTypeVo">
        SELECT
            id AS iotTypeId,
            p_id AS pId,
            name AS iotTypeName
        FROM
            sys_dic
        WHERE
            status = '0'
            AND p_id = '124'
    </select>
    <select id="selectCodeList" resultType="com.bonus.sgzb.material.domain.IotCodeVo">
        SELECT
            id AS iotId,
            iot_code AS iotCode
        FROM
            iot_machine
        WHERE
            del_flag = '0'
          AND iot_status = '0'
          AND bind_status = '1'
          AND iot_type = #{iotType}
    </select>
    <select id="getRecordList" resultType="com.bonus.sgzb.material.domain.IotRecordVo">
        SELECT
            imb.id AS id,
            mt1.type_name AS typeName,
            imb.ma_code AS maCode,
            imb.type_id AS typeId,
            imb.bind_time AS bindTime,
            imb.unbind_time AS unBindTime
        FROM
            iot_machine_bind imb
                LEFT JOIN ma_type mt ON imb.type_id = mt.type_id
                LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
                LEFT JOIN ma_type mt2 ON mt1.parent_id = mt2.type_id
                LEFT JOIN ma_type mt3 ON mt2.parent_id = mt3.type_id
                LEFT JOIN iot_machine im ON imb.iot_id = im.id
        WHERE
            im.del_flag = '0'
            AND im.id = #{iotId}
        <if test="keyWord != null  and keyWord != ''">
            AND (
                mt1.type_name like concat('%', #{keyWord}, '%') or
                imb.ma_code like concat('%', #{keyWord}, '%')
            )
        </if>
    </select>

</mapper>