<?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.base.mapper.StationDefectRelationMapper">

    <resultMap type="StationDefectRelation" id="StationDefectRelationResult">
            <result property="objId"    column="obj_id"    />
            <result property="stationCode"    column="station_code"    />
            <result property="stationName"    column="station_name"    />
            <result property="defectCode"    column="defect_code"    />
            <result property="defectName"    column="defect_name"    />
            <result property="productionLine"    column="production_line"    />
            <result property="isFlag"    column="is_flag"    />
            <result property="createdBy"    column="created_by"    />
            <result property="createdTime"    column="created_time"    />
            <result property="updatedBy"    column="updated_by"    />
            <result property="updatedTime"    column="updated_time"    />
            <result property="materialCategories"    column="material_categories"    />
            <result property="materialSubclass"    column="material_subclass"    />
    </resultMap>

    <sql id="selectStationDefectRelationVo">
        select sdr.obj_id,
               sdr.station_code,
               pl.PRODUCT_LINE_NAME station_name,
               sdr.defect_code,
               qii.QUALITY_DEFECT_NAME defect_name,
               pl.PARENT_ID production_line,
               qii.MATERIAL_CATEGORIES material_categories,
               qii.MATERIAL_SUBCLASS material_subclass,
               sdr.is_flag,
               sdr.created_by,
               sdr.created_time,
               sdr.updated_by,
               sdr.updated_time
        from station_defect_relation sdr
                 left join BASE_PRODUCTLINE pl on pl.PRODUCT_LINE_CODE = sdr.station_code
                 left join BASE_QUALITY_INSPECTION_ITEM qii on qii.QUALITY_DEFECT_CODE = sdr.defect_code
    </sql>

    <select id="selectStationDefectRelationList" parameterType="StationDefectRelation" resultMap="StationDefectRelationResult">
        <include refid="selectStationDefectRelationVo"/>
        <where>
                        <if test="stationCode != null  and stationCode != ''"> and  sdr.station_code = #{stationCode}</if>
                        <if test="stationName != null  and stationName != ''"> and  sdr.station_name like concat(concat('%', #{stationName}), '%')</if>
                        <if test="defectCode != null  and defectCode != ''"> and  sdr.defect_code = #{defectCode}</if>
                        <if test="defectName != null  and defectName != ''"> and  sdr.defect_name like concat(concat('%', #{defectName}), '%')</if>
                        <if test="productionLine != null  and productionLine != ''"> and  sdr.production_line = #{productionLine}</if>
                        <if test="isFlag != null "> and  sdr.is_flag = #{isFlag}</if>
                        <if test="createdBy != null  and createdBy != ''"> and  sdr.created_by = #{createdBy}</if>
                        <if test="createdTime != null "> and  sdr.created_time = #{createdTime}</if>
                        <if test="updatedBy != null  and updatedBy != ''"> and  sdr.updated_by = #{updatedBy}</if>
                        <if test="updatedTime != null "> and  sdr.updated_time = #{updatedTime}</if>
        </where>
        order by sdr.station_code, sdr.defect_code
    </select>

    <select id="selectStationDefectRelationByObjId" parameterType="Long" resultMap="StationDefectRelationResult">
            <include refid="selectStationDefectRelationVo"/>
            where sdr.obj_id = #{objId}
    </select>

    <insert id="insertStationDefectRelation" parameterType="StationDefectRelation">
            <selectKey keyProperty="objId" resultType="long" order="BEFORE">
                SELECT seq_station_defect_relation.NEXTVAL as objId FROM DUAL
            </selectKey>
        insert into station_defect_relation
        <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="objId != null">obj_id,</if>
                <if test="stationCode != null">station_code,</if>
                <if test="stationName != null">station_name,</if>
                <if test="defectCode != null">defect_code,</if>
                <if test="defectName != null">defect_name,</if>
                <if test="productionLine != null">production_line,</if>
                <if test="isFlag != null">is_flag,</if>
                <if test="createdBy != null">created_by,</if>
                <if test="createdTime != null">created_time,</if>
                <if test="updatedBy != null">updated_by,</if>
                <if test="updatedTime != null">updated_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="objId != null">#{objId},</if>
                <if test="stationCode != null">#{stationCode},</if>
                <if test="stationName != null">#{stationName},</if>
                <if test="defectCode != null">#{defectCode},</if>
                <if test="defectName != null">#{defectName},</if>
                <if test="productionLine != null">#{productionLine},</if>
                <if test="isFlag != null">#{isFlag},</if>
                <if test="createdBy != null">#{createdBy},</if>
                <if test="createdTime != null">#{createdTime},</if>
                <if test="updatedBy != null">#{updatedBy},</if>
                <if test="updatedTime != null">#{updatedTime},</if>
        </trim>
    </insert>

    <update id="updateStationDefectRelation" parameterType="StationDefectRelation">
        update station_defect_relation
        <trim prefix="SET" suffixOverrides=",">
                    <if test="stationCode != null">station_code = #{stationCode},</if>
                    <if test="stationName != null">station_name = #{stationName},</if>
                    <if test="defectCode != null">defect_code = #{defectCode},</if>
                    <if test="defectName != null">defect_name = #{defectName},</if>
                    <if test="productionLine != null">production_line = #{productionLine},</if>
                    <if test="isFlag != null">is_flag = #{isFlag},</if>
                    <if test="createdBy != null">created_by = #{createdBy},</if>
                    <if test="createdTime != null">created_time = #{createdTime},</if>
                    <if test="updatedBy != null">updated_by = #{updatedBy},</if>
                    <if test="updatedTime != null">updated_time = #{updatedTime},</if>
        </trim>
        where obj_id = #{objId}
    </update>

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

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