<?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.base.mapper.MaLabelBindMapper">
    <resultMap type="com.bonus.sgzb.base.vo.MaLabelBindVO" id="MaLabelBindVOResult">
        <result property="labelId"    column="label_id"    />
        <result property="maId"    column="ma_id"    />
        <result property="maCode"    column="ma_code"    />
        <result property="labelType"    column="label_type"    />
        <result property="labelCode"    column="label_code"    />
        <result property="typeName"    column="type_name"    />
        <result property="typeId"    column="type_id"    />
        <result property="modelName"    column="modelName"    />
        <result property="modelId"    column="modelId"    />
        <result property="wpName"    column="wpName"    />
        <result property="kindName"    column="kindName"    />
        <result property="isBind"    column="is_bind"    />
        <result property="name"    column="name"    />
        <result property="bindTime"    column="bind_time"    />
        <result property="userName"    column="nick_name"    />
        <result property="id"    column="id"    />
    </resultMap>

    <sql id="selectMaLabelBindVo">
        select a.label_id,a.label_type,a.label_code,c.type_name,d.type_name,f.type_name,b.ma_code,a.is_bind
        from ma_machine_label a
        left JOIN ma_machine b on a.type_id=b.type_id
        left Join ma_type c on a.type_id=c.type_id
        left Join ma_type d on a.type_id=d.type_id
        left Join ma_type f on a.type_id=f.type_id
        left join ma_label_bind e on a.type_id=e.type_id
    </sql>

    <select id="selectMaLabelBindList" parameterType="com.bonus.sgzb.base.vo.MaLabelBindVO" resultMap="MaLabelBindVOResult">
        SELECT b.label_id,a.id,b.is_bind,b.label_code,a.bind_time,b.label_type,mt.type_id,mt.type_name,
        mt2.type_id as modelId , mt2.type_name as modelName,mt3.type_name as wpName,
        mt4.type_name as kindName ,a.status,a.binder,dic.name,ma.ma_code,user.nick_name
        FROM ma_machine_label b
        left join ma_label_bind a on a.ma_id = b.ma_id
        LEFT JOIN ma_type mt ON a.type_id = mt.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id AND mt2.`level` = '3'
        LEFT JOIN ma_type mt3 ON mt2.parent_id = mt3.type_id AND mt3.`level` = '2'
        LEFT JOIN ma_type mt4 ON mt3.parent_id = mt4.type_id AND mt4.`level` = '1'
        LEFT JOIN ma_type_repair mtk ON mtk.type_id = mt.type_id
        LEFT JOIN sys_dic dic ON dic.id = b.label_type
        LEFT JOIN ma_machine ma ON ma.ma_id = a.ma_id
        LEFT JOIN sys_user user on user.user_id =a.binder
        where 1=1
        <if test="keyWord != null and keyWord != ''">
            and (b.label_code like concat('%',#{keyWord},'%') or
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            ma.ma_code like concat('%',#{keyWord},'%')
            )
        </if>
        <if test="typeId != null ">
            and mt2.type_id = #{typeId}
        </if>
        <if test="modelId != null  and modelId != ''">
            and mt.type_id= #{modelId}
        </if>

        <if test="labelCode != null  and labelCode != ''">and a.label_code = #{labelCode}</if>
        <if test="bindTime != null  and bindTime != ''">and a.bind_time = #{bindTime}</if>
        <if test="binder != null  and binder != ''">and a.binder = #{binder}</if>
        <if test="companyId != null  and companyId != ''">and a.company_id = #{companyId}</if>

        <if test="wpName != null  and wpName != ''">and mt3.type_name = #{wpName}</if>
        <if test="isBind != null  and isBind != ''">and b.is_bind = #{isBind}</if>
        ORDER BY b.is_bind ASC,b.label_id DESC
    </select>
    <select id="selecthistoryList" parameterType="com.bonus.sgzb.base.vo.MaLabelBindVO" resultMap="MaLabelBindVOResult">
        SELECT b.label_id,a.id,b.is_bind,b.label_code,a.bind_time,b.label_type,mt.type_id,mt.type_name,
        mt2.type_id as modelId , mt2.type_name as modelName,mt3.type_name as wpName,
        mt4.type_name as kindName ,a.status,a.binder,dic.name,ma.ma_code,user.nick_name
        FROM ma_machine_label b
        left join ma_label_bind a on a.ma_id = b.ma_id
        LEFT JOIN ma_type mt  ON a.type_id = mt.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id AND mt2.`level` = '3'
        LEFT JOIN ma_type mt3 ON mt2.parent_id = mt3.type_id AND mt3.`level` = '2'
        LEFT JOIN ma_type mt4 ON mt3.parent_id = mt4.type_id AND mt4.`level` = '1'
        LEFT JOIN sys_dic dic ON dic.id = b.label_type
        LEFT JOIN ma_machine  ma ON ma.ma_id = a.ma_id
        LEFT JOIN sys_user user on user.user_id =a.binder
        WHERE mt.`level` = '4' AND mt.`del_flag` = '0'
            <if test="keyWord != null  and keyWord != ''">
                and (b.label_code like concat('%', #{keyWord}, '%') or
                ma.ma_code like concat('%', #{keyWord}, '%'))
            </if>
            <if test="labelCode != null  and labelCode != ''"> and a.label_code = #{labelCode}</if>
            <if test="typeId != null "> and mt2.type_id = #{typeId}</if>
            <if test="bindTime != null  and bindTime != ''"> and a.bind_time = #{bindTime}</if>
            <if test="binder != null  and binder != ''"> and a.binder = #{binder}</if>
            <if test="labelType != null "> and a.label_type = #{labelType}</if>
            <if test="companyId != null  and companyId != ''"> and a.company_id = #{companyId}</if>
            <if test="modelId != null  and modelId != ''"> and mt.type_id= #{modelId}</if>
            <if test="wpName != null  and wpName != ''"> and mt3.type_name = #{wpName}</if>
        order by a.bind_time desc
    </select>

    <select id="selectMaLabelBindByMaId" parameterType="Long" resultMap="MaLabelBindVOResult">
        <include refid="selectMaLabelBindVo"/>
        where ma_id = #{maId}
    </select>

    <insert id="insertMaLabelBindS" parameterType="com.bonus.sgzb.base.vo.MaLabelBindVO">
        insert into ma_label_bind
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="maId != null">ma_id,</if>
            <if test="labelCode != null">label_code,</if>
            <if test="typeId != null">type_id,</if>
            <if test="binder != null">binder,</if>
            <if test="labelType != null">label_type,</if>
            <if test="companyId != null">company_id,</if>
            <if test="bindTime != null">binder_time,</if>
            <if test="status != null">status,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="maId != null">#{maId},</if>
            <if test="labelCode != null">#{labelCode},</if>
            <if test="typeId != null">#{typeId},</if>
            <if test="binder != null">#{binder},</if>
            <if test="labelType != null">#{labelType},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="bindTime != null">#{bindTime},</if>
            <if test="status != null">#{status},</if>
        </trim>
    </insert>
    <insert id="insertLabelBind">
        insert into ma_label_bind
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="maId != null">ma_id,</if>
            <if test="labelCode != null">label_code,</if>
            <if test="typeId != null">type_id,</if>
            <if test="binder != null">binder,</if>
            <if test="labelType != null">label_type,</if>
            <if test="companyId != null">company_id,</if>
            <if test="bindTime != null">bind_time,</if>
            <if test="status != null">status,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="maId != null">#{maId},</if>
            <if test="labelCode != null">#{labelCode},</if>
            <if test="typeId != null">#{typeId},</if>
            <if test="binder != null">#{binder},</if>
            <if test="labelType != null">#{labelType},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="bindTime != null">#{bindTime},</if>
            <if test="status != null">#{status},</if>
        </trim>
    </insert>
    <update id="insertMaLabelBind" parameterType="com.bonus.sgzb.base.vo.MaLabelBindVO">
        update ma_label_bind
        <trim prefix="SET" suffixOverrides=",">
            <if test="labelCode != null">ma_id = #{maId},</if>
            <if test="labelCode != null">label_code = #{labelCode},</if>
            <if test="typeId != null">type_id = #{typeId},</if>
            <if test="binder != null">binder = #{binder},</if>
            <if test="labelType != null">label_type = #{labelType},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
        </trim>
    </update>
    <update id="updateMaLabelBind" parameterType="com.bonus.sgzb.base.vo.MaLabelBindVO">
        update ma_label_bind
        <trim prefix="SET" suffixOverrides=",">
            <if test="labelCode != null">label_code = #{labelCode},</if>
            <if test="typeId != null">type_id = #{typeId},</if>
            <if test="binder != null">binder = #{binder},</if>
            <if test="labelType != null">label_type = #{labelType},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            update_time = NOW()
        </trim>
        where ma_id = #{maId}
    </update>

    <delete id="deleteMaLabelBindByMaId" parameterType="Long">
        delete from ma_label_bind where ma_id = #{maId}
    </delete>

    <update id="updateMaLabelBindByMaIds" parameterType="Long">
        update ma_label_bind set status = '2', update_time = NOW() where id = #{id}
    </update>

    <select id="selectMaMachineMaId" parameterType="com.bonus.sgzb.base.vo.MaLabelBindVO">
        select ma_id as maId,type_id as typeId
        from ma_machine
        where ma_code = #{maCode}
    </select>
    <select id="selectMaLabelBindByMaCode" resultType="com.bonus.sgzb.base.api.domain.MaLabelBind">
        select * from ma_label_bind where label_code = #{qrCode}
    </select>
    <update id="updateMaLabelBindMaIds" parameterType="com.bonus.sgzb.base.vo.MaLabelBindVO">
        update ma_machine_label set
          ma_id = #{maId},is_bind =#{isBind},update_time = NOW()
          where label_id =#{labelId}
    </update>
    <update id="updateMalableCode" parameterType="com.bonus.sgzb.base.api.domain.MaLabelBind">
        update ma_machine_label set
          ma_id = #{maId},is_bind =1,update_time = NOW()
          where label_code =#{labelCode}
    </update>
    <update id="updateMatypeNum">
        UPDATE ma_type
        SET num = IFNULL( num, 0 ) + 1,
            update_time = now()
        WHERE
            type_id = #{typeId}
    </update>
    <insert id="insertMaLabels">
        insert into ma_maLabel
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="typeId != null">type_id,</if>
            <if test="maCode != null">ma_code,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="typeId != null">#{typeId},</if>
            <if test="labelCode != null">#{maCode},</if>
        </trim>
    </insert>
    <insert id="insertMaLabel" useGeneratedKeys="true" keyProperty="maId">
        insert into ma_machine (type_id,ma_code,ma_status,qr_code,create_time)
        values (#{typeId},#{maCode},'15',#{qrCode},now());
    </insert>
</mapper>