<?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.BaseProcessStationMapper">

    <resultMap type="BaseProcessStation" id="BaseProcessStationResult">
        <result property="objId" column="obj_id"/>
        <result property="processCode" column="process_code"/>
        <result property="processName" column="process_name"/>
        <result property="processType" column="process_type"/>
        <result property="productLineCode" column="product_line_code"/>
        <result property="productionTime" column="production_time"/>
        <result property="capacityDay" column="capacity_day"/>
        <result property="capacityMonth" column="capacity_month"/>
        <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="parentId" column="parent_id"/>
        <result property="stationName" column="stationName"/>
    </resultMap>

    <sql id="selectBaseProcessStationVo">
        select bps.obj_id,
               bps.process_code,
               bps.process_name,
               bps.process_type,
               bps.product_line_code,
               bps.production_time,
               bps.capacity_day,
               bps.capacity_month,
               bps.is_flag,
               bps.created_by,
               bps.created_time,
               bps.updated_by,
               bps.updated_time,
               bps.parent_id,
               st.station_name stationName
        from base_process_station bps
                 LEFT JOIN (
            SELECT ST.PROCESS_CODE,
                   LISTAGG(ST.STATION_CODE, ',') WITHIN GROUP (ORDER BY ST.PROCESS_CODE)      AS station_code,
                   LISTAGG(PL.PRODUCT_LINE_NAME, ',') WITHIN GROUP (ORDER BY ST.PROCESS_CODE) AS station_name
            FROM (SELECT PROCESS_CODE,
                         TRIM(REGEXP_SUBSTR(PARENT_ID, '[^,]+', 1, LEVEL)) AS station_code
                  FROM BASE_PROCESS_STATION
                  CONNECT BY PRIOR PROCESS_CODE = PROCESS_CODE
                         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
                         AND LENGTH(REGEXP_REPLACE(PARENT_ID, '[^,]+')) + 1 >= LEVEL
                  ORDER BY PROCESS_CODE, LEVEL) ST
                     LEFT JOIN BASE_PRODUCTLINE PL ON pl.PRODUCT_LINE_CODE = st.station_code
            GROUP BY ST.PROCESS_CODE
        ) st ON st.PROCESS_CODE = bps.PROCESS_CODE
    </sql>

    <select id="selectBaseProcessStationList" parameterType="BaseProcessStation" resultMap="BaseProcessStationResult">
        <include refid="selectBaseProcessStationVo"/>
        <where>
            <if test="processCode != null  and processCode != ''">and bps.process_code = #{processCode}</if>
            <if test="processName != null  and processName != ''">and bps.process_name like concat(concat('%',
                #{processName}), '%')
            </if>
            <if test="processType != null ">and bps.process_type = #{processType}</if>
            <if test="productLineCode != null  and productLineCode != ''">and bps.product_line_code = #{productLineCode}
            </if>
            <if test="productionTime != null ">and bps.production_time = #{productionTime}</if>
            <if test="capacityDay != null ">and capacity_day = #{capacityDay}</if>
            <if test="capacityMonth != null ">and capacity_month = #{capacityMonth}</if>
            <if test="isFlag != null ">and is_flag = #{isFlag}</if>
            <if test="createdBy != null  and createdBy != ''">and created_by = #{createdBy}</if>
            <if test="createdTime != null ">and created_time = #{createdTime}</if>
            <if test="updatedBy != null  and updatedBy != ''">and updated_by = #{updatedBy}</if>
            <if test="updatedTime != null ">and updated_time = #{updatedTime}</if>
            <if test="parentId != null  and parentId != ''">and bps.parent_id = #{parentId}</if>
        </where>
    </select>

    <select id="selectBaseProcessStationByObjId" parameterType="Long" resultMap="BaseProcessStationResult">
        <include refid="selectBaseProcessStationVo"/>
        where bps.obj_id = #{objId}
    </select>

    <insert id="insertBaseProcessStation" parameterType="BaseProcessStation">
        <selectKey keyProperty="objId" resultType="long" order="BEFORE">
            SELECT seq_base_process_station.NEXTVAL as objId FROM DUAL
        </selectKey>
        insert into base_process_station
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="objId != null">obj_id,</if>
            <if test="processCode != null">process_code,</if>
            <if test="processName != null">process_name,</if>
            <if test="processType != null">process_type,</if>
            <if test="productLineCode != null">product_line_code,</if>
            <if test="productionTime != null">production_time,</if>
            <if test="capacityDay != null">capacity_day,</if>
            <if test="capacityMonth != null">capacity_month,</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>
            <if test="parentId != null">parent_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="objId != null">#{objId},</if>
            <if test="processCode != null">#{processCode},</if>
            <if test="processName != null">#{processName},</if>
            <if test="processType != null">#{processType},</if>
            <if test="productLineCode != null">#{productLineCode},</if>
            <if test="productionTime != null">#{productionTime},</if>
            <if test="capacityDay != null">#{capacityDay},</if>
            <if test="capacityMonth != null">#{capacityMonth},</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>
            <if test="parentId != null">#{parentId},</if>
        </trim>
    </insert>

    <update id="updateBaseProcessStation" parameterType="BaseProcessStation">
        update base_process_station
        <trim prefix="SET" suffixOverrides=",">
            <if test="processCode != null">process_code = #{processCode},</if>
            <if test="processName != null">process_name = #{processName},</if>
            <if test="processType != null">process_type = #{processType},</if>
            <if test="productLineCode != null">product_line_code = #{productLineCode},</if>
            <if test="productionTime != null">production_time = #{productionTime},</if>
            <if test="capacityDay != null">capacity_day = #{capacityDay},</if>
            <if test="capacityMonth != null">capacity_month = #{capacityMonth},</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>
            <if test="parentId != null">parent_id = #{parentId},</if>
        </trim>
        where obj_id = #{objId}
    </update>

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

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