<?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.ruoyi.bst.hardwareVersion.mapper.HardwareVersionMapper">

    <resultMap type="HardwareVersionVO" id="HardwareVersionResult" autoMapping="true">
        <result property="id"    column="id"    />
        <result property="parentId"    column="parent_id"    />
        <result property="ancestors"    column="ancestors"    />
        <result property="version"    column="version"    />
        <result property="productionTime"    column="production_time"    />
        <result property="quantity"    column="quantity"    />
        <result property="notEnteredNum"    column="not_entered_num"    />
        <result property="instructions"    column="instructions"    />
        <result property="createTime"    column="create_time"    />
        <result property="remark"    column="remark"    />
    </resultMap>

    <sql id="selectHardwareVersionVo">
        select
            bhv.id,
            bhv.parent_id,
            bhv.ancestors,
            bhv.version,
            bhv.production_time,
            bhv.quantity,
            bhv.not_entered_num,
            bhv.instructions,
            bhv.create_time,
            bhv.remark
        from bst_hardware_version bhv
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and bhv.id = #{query.id}</if>
        <if test="query.parentId != null "> and bhv.parent_id = #{query.parentId}</if>
        <if test="query.ancestors != null  and query.ancestors != ''"> and bhv.ancestors = #{query.ancestors}</if>
        <if test="query.version != null  and query.version != ''"> and bhv.version like concat('%', #{query.version}, '%')</if>
        <if test="query.productionTime != null "> and bhv.production_time = #{query.productionTime}</if>
        <if test="query.quantity != null "> and bhv.quantity = #{query.quantity}</if>
        <if test="query.notEnteredNum != null "> and bhv.not_entered_num = #{query.notEnteredNum}</if>
        <if test="query.instructions != null  and query.instructions != ''"> and bhv.instructions like concat('%', #{query.instructions}, '%')</if>
        ${query.params.dataScope}
    </sql>

    <select id="selectHardwareVersionList" parameterType="HardwareVersionQuery" resultMap="HardwareVersionResult">
        <include refid="selectHardwareVersionVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectHardwareVersionById" parameterType="Long" resultMap="HardwareVersionResult">
        <include refid="selectHardwareVersionVo"/>
        where id = #{id}
    </select>

    <select id="selectNum" resultType="java.lang.Integer">
        select count(bd.id) as count
        from bst_hardware_version bhv
        left join bst_device bd on bhv.id = bd.hardware_version_id
            <choose>
                <when test="condition == 'NotNULL'">
                    and bd.mch_id IS NOT NULL
                </when>
                <when test="condition == 'NULL'">
                    and bd.mch_id IS NULL
                </when>
            </choose>
        <where>
            <include refid="searchCondition"></include>
        </where>
    </select>

    <select id="countBoundNum" resultType="java.lang.Integer">
        select count(1) from bst_device where hardware_version_id = #{hardwareVersionId} and mch_id is not null
    </select>

    <select id="countUnBoundNum" resultType="java.lang.Integer">
        select count(1) from bst_device where hardware_version_id = #{hardwareVersionId} and mch_id is null
    </select>

    <select id="selectBoundCount" resultType="com.ruoyi.bst.hardwareVersion.domain.HardwareVersionVO">
        select bhv.id,count(1) as Bound
            from bst_hardware_version bhv
        left join bst_device bd
        on bhv.id = bd.hardware_version_id
        where bd.mch_id is not null
         and bd.hardware_version_id is not null
        group by bhv.id
    </select>

    <select id="selectUnBoundCount" resultType="com.ruoyi.bst.hardwareVersion.domain.HardwareVersionVO">
        select bhv.id,count(1) as unBound
        from bst_hardware_version bhv
                 left join bst_device bd
                           on bhv.id = bd.hardware_version_id
        where bd.mch_id is null
          and bd.hardware_version_id is not null
        group by bhv.id
    </select>

    <insert id="insertHardwareVersion" parameterType="HardwareVersion" useGeneratedKeys="true" keyProperty="id">
        insert into bst_hardware_version
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="parentId != null">parent_id,</if>
            <if test="ancestors != null">ancestors,</if>
            <if test="version != null">version,</if>
            <if test="productionTime != null">production_time,</if>
            <if test="quantity != null">quantity,</if>
            <if test="notEnteredNum != null">not_entered_num,</if>
            <if test="instructions != null">instructions,</if>
            <if test="createTime != null">create_time,</if>
            <if test="remark != null">remark,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="parentId != null">#{parentId},</if>
            <if test="ancestors != null">#{ancestors},</if>
            <if test="version != null">#{version},</if>
            <if test="productionTime != null">#{productionTime},</if>
            <if test="quantity != null">#{quantity},</if>
            <if test="notEnteredNum != null">#{notEnteredNum},</if>
            <if test="instructions != null">#{instructions},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="remark != null">#{remark},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="HardwareVersion" useGeneratedKeys="true" keyProperty="id">
        insert into bst_hardware_version
        <trim prefix="(" suffix=")" suffixOverrides=",">
            parent_id,
            ancestors,
            version,
            production_time,
            quantity,
            not_entered_num,
            instructions,
            create_time,
            remark,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.parentId != null ">#{i.parentId},</if>
                <if test="i.parentId == null ">default,</if>
                <if test="i.ancestors != null ">#{i.ancestors},</if>
                <if test="i.ancestors == null ">default,</if>
                <if test="i.version != null ">#{i.version},</if>
                <if test="i.version == null ">default,</if>
                <if test="i.productionTime != null ">#{i.productionTime},</if>
                <if test="i.productionTime == null ">default,</if>
                <if test="i.quantity != null ">#{i.quantity},</if>
                <if test="i.quantity == null ">default,</if>
                <if test="i.notEnteredNum != null ">#{i.notEnteredNum},</if>
                <if test="i.notEnteredNum == null ">default,</if>
                <if test="i.instructions != null ">#{i.instructions},</if>
                <if test="i.instructions == null ">default,</if>
                <if test="i.createTime != null ">#{i.createTime},</if>
                <if test="i.createTime == null ">default,</if>
                <if test="i.remark != null ">#{i.remark},</if>
                <if test="i.remark == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="batchUpdate">
        update bst_hardware_version
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="parent_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.parentId != null ">
                        WHEN #{item.id} THEN #{item.parentId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `parent_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="ancestors = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.ancestors != null ">
                        WHEN #{item.id} THEN #{item.ancestors}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `ancestors`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="version = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.version != null ">
                        WHEN #{item.id} THEN #{item.version}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `version`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="production_time = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.productionTime != null ">
                        WHEN #{item.id} THEN #{item.productionTime}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `production_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="quantity = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.quantity != null ">
                        WHEN #{item.id} THEN #{item.quantity}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `quantity`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="not_entered_num = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.notEnteredNum != null ">
                        WHEN #{item.id} THEN #{item.notEnteredNum}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `not_entered_num`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="instructions = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.instructions != null ">
                        WHEN #{item.id} THEN #{item.instructions}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `instructions`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="create_time = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.createTime != null ">
                        WHEN #{item.id} THEN #{item.createTime}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `create_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="remark = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.remark != null ">
                        WHEN #{item.id} THEN #{item.remark}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `remark`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>

    <update id="updateHardwareVersion" parameterType="HardwareVersion">
        update bst_hardware_version
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.parentId != null">parent_id = #{data.parentId},</if>
        <if test="data.ancestors != null">ancestors = #{data.ancestors},</if>
        <if test="data.version != null">version = #{data.version},</if>
        <if test="data.productionTime != null">production_time = #{data.productionTime},</if>
        <if test="data.quantity != null">quantity = #{data.quantity},</if>
        <if test="data.notEnteredNum != null">not_entered_num = #{data.notEnteredNum},</if>
        <if test="data.instructions != null">instructions = #{data.instructions},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.remark != null">remark = #{data.remark},</if>
    </sql>

    <delete id="deleteHardwareVersionById" parameterType="Long">
        delete from bst_hardware_version where id = #{id}
    </delete>

    <delete id="deleteHardwareVersionByIds" parameterType="String">
        delete from bst_hardware_version where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- selectNameList --> 

    <select id="selectNameList" resultType="HardwareVersionNameVO">
        select 
            bhv.id,
            bhv.parent_id,
            bhv.version,
            bhv.instructions
        from bst_hardware_version bhv
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>
    
</mapper>
