<?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.aucma.report.mapper.RecordExceptionMapper">

    <resultMap type="RecordException" id="RecordExceptionResult">
        <result property="objId" column="obj_id"/>
        <result property="productSncode" column="product_sncode"/>
        <result property="exceptionMsg" column="exception_msg"/>
        <result property="productScantime" column="product_scantime"/>
        <result property="productLineCode" column="product_line_code"/>
        <result property="exceptionType" column="exception_type"/>
        <result property="boxCode" column="box_code"/>
    </resultMap>

    <sql id="selectRecordExceptionVo">
        select re.obj_id,
               re.product_sncode,
               re.exception_msg,
               re.product_scantime,
               re.product_line_code,
               re.exception_type,
               cb.box_code
        from record_exception re
                 left join CODE_BINDING cb on cb.PRODUCT_CODE = re.product_sncode
    </sql>

    <select id="selectRecordExceptionList" parameterType="RecordException" resultMap="RecordExceptionResult">
        <include refid="selectRecordExceptionVo"/>
        <where>
            <if test="productSncode != null  and productSncode != ''">and re.product_sncode = #{productSncode}</if>
            <if test="exceptionMsg != null  and exceptionMsg != ''">and re.exception_msg = #{exceptionMsg}</if>
            <if test="params.beginProductScantime != null and params.beginProductScantime != '' and params.endProductScantime != null and params.endProductScantime != ''">
                and re.product_scantime between to_date(#{params.beginProductScantime}, 'yyyy-mm-dd hh24:mi:ss') and
                to_date(#{params.endProductScantime}, 'yyyy-mm-dd hh24:mi:ss')
            </if>
            <if test="productLineCode != null  and productLineCode != ''">and re.product_line_code = #{productLineCode}
            </if>
            <if test="boxCode != null  and boxCode != ''">and cb.box_code = #{boxCode}</if>
            <if test="exceptionType != null ">and re.exception_type = #{exceptionType}</if>
        </where>
        order by re.product_scantime desc
    </select>

    <select id="selectRecordExceptionByObjId" parameterType="Long" resultMap="RecordExceptionResult">
        <include refid="selectRecordExceptionVo"/>
        where  re.obj_id = #{objId}
    </select>

    <insert id="insertRecordException" parameterType="RecordException">
        <selectKey keyProperty="objId" resultType="long" order="BEFORE">
            SELECT seq_record_exception.NEXTVAL as objId FROM DUAL
        </selectKey>
        insert into record_exception
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="objId != null">obj_id,</if>
            <if test="productSncode != null">product_sncode,</if>
            <if test="exceptionMsg != null">exception_msg,</if>
            <if test="productScantime != null">product_scantime,</if>
            <if test="productLineCode != null">product_line_code,</if>
            <if test="exceptionType != null">exception_type,</if>
            <if test="boxCode != null">box_code,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="objId != null">#{objId},</if>
            <if test="productSncode != null">#{productSncode},</if>
            <if test="exceptionMsg != null">#{exceptionMsg},</if>
            <if test="productScantime != null">#{productScantime},</if>
            <if test="productLineCode != null">#{productLineCode},</if>
            <if test="exceptionType != null">#{exceptionType},</if>
            <if test="boxCode != null">#{boxCode},</if>
        </trim>
    </insert>

    <update id="updateRecordException" parameterType="RecordException">
        update record_exception
        <trim prefix="SET" suffixOverrides=",">
            <if test="productSncode != null">product_sncode = #{productSncode},</if>
            <if test="exceptionMsg != null">exception_msg = #{exceptionMsg},</if>
            <if test="productScantime != null">product_scantime = #{productScantime},</if>
            <if test="productLineCode != null">product_line_code = #{productLineCode},</if>
            <if test="exceptionType != null">exception_type = #{exceptionType},</if>
            <if test="boxCode != null">box_code = #{boxCode},</if>
        </trim>
        where obj_id = #{objId}
    </update>

    <delete id="deleteRecordExceptionByObjId" parameterType="Long">
        delete
        from record_exception
        where obj_id = #{objId}
    </delete>

    <delete id="deleteRecordExceptionByObjIds" parameterType="String">
        delete from record_exception where obj_id in
        <foreach item="objId" collection="array" open="(" separator="," close=")">
            #{objId}
        </foreach>
    </delete>
</mapper>