mapper.xml

<?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.engine.modules.bpapprove.mapper.ApprovalMapper">

    <resultMap type="com.engine.modules.bpapprove.model.ApprovalMix" id="mix">
        <result column="ORJGBM" property="orgno"/>
        <result column="POSITION_PERMS" property="positionPerms"/>
        <result column="POSITION_LEVEL" property="positionLevel"/>
        <result column="NUM" property="num"/>
    </resultMap>


    <!--    &lt;!&ndash; 改 &ndash;&gt;
        <update id="updataByProductCode" parameterType="java.lang.String">
            UPDATE CRD_STA_APPROVAL
            SET
            APPLICATION_TYPE=#{applicationType}
            where PRODUCT_CODE=#{productCode}

        </update>-->


    <!-- 查 -->
    <select id="findPositionInfo" resultMap="mix"
            parameterType="java.lang.String" resultType="java.util.List">
            SELECT
                lf.ORJGBM,
                lf.POSITION_PERMS,
                lf.POSITION_LEVEL,
            CASE
            WHEN rt.num IS NULL THEN
                0
            WHEN rt.POSITION_LEVEL = #{positionLevel} THEN
                rt.num - 1 ELSE rt.num
            END AS NUM
            FROM
                POSITION_ORGANIZATION lf
            LEFT JOIN (
            SELECT
                lf.ORGNO,
                rt.POSITION_PERMS,
                rt.POSITION_LEVEL,
                count( lf.ID ) NUM
            FROM
                USER_INFO lf
            JOIN USER_POSITION rt ON lf.ID = rt.USER_PERMS
            WHERE
                lf.ORGNO = #{orgno}
            AND ( rt.POSITION_LEVEL >= #{positionLevel})
            GROUP BY
                lf.ORGNO,
                rt.POSITION_PERMS,
                rt.POSITION_LEVEL
            ORDER BY
                rt.POSITION_LEVEL
                ) rt ON ( lf.ORJGBM = rt.ORGNO AND lf.POSITION_PERMS = rt.POSITION_PERMS AND lf.POSITION_LEVEL =
                rt.POSITION_LEVEL )
            ORDER BY
                lf.POSITION_LEVEL
    </select>


    <select id="findPositionLevel" resultType="java.lang.Integer" parameterType="java.lang.String">
        SELECT
            POSITION_LEVEL
        FROM
            USER_POSITION
        WHERE
            USER_PERMS = #{id}
    </select>

    <select id="findApprovalInfo" resultType="java.lang.String"
            parameterType="java.lang.String">
        SELECT
        res.YID
        FROM
        ( SELECT y.ID YID,
        CASE WHEN n.ID IS NULL THEN '0' ELSE n.ID
        END AS NID
        FROM
        ( SELECT a.ID ID
        FROM
        ( SELECT MAX( ID ) ID,
        APPLICATION_TIME,
        COUNT( * ) NUM
        FROM
        CRD_STA_APPROVAL
        WHERE
        ORGANIZATION_CODE = #{orgno}
        AND POSITION_LEVEL <![CDATA[ <= ]]> #{positionLevel}
        AND APPROVE_RESULT = 3
        GROUP BY
        APPLICATION_TIME,
        PRODUCT_CODE,
        APPLICATION_TYPES
        ) a
        WHERE
        a.NUM = 1
        ) y
        LEFT JOIN ( SELECT ID FROM CRD_STA_APPROVAL b INNER JOIN ( SELECT APPLICATION_TIME FROM CRD_STA_APPROVAL WHERE APPROVE_RESULT = 0 ) a ON a.APPLICATION_TIME = b.APPLICATION_TIME ) n ON y.ID = n.ID
        ) res
        WHERE
        res.YID != res.NID
    </select>

    <insert id="saveBothOracle" parameterType="java.util.List">
        INSERT INTO
        CRD_STA_APPROVAL(ID,PRODUCT_CODE,APPLICATION_TIME,APPLICATION_INSTRUCTION,APPLICATION_TYPE,APPROVE_TIME,APPROVE_INSTRUCTION,APPROVE_RESULT,SYSTEM,APPLICANT,APPROVER,APPLICATION_TYPES,ISDELETE,RELATED_ID,POSITION_CODE,ORGANIZATION_CODE,POSITION_LEVEL)
        (select BOTH_ORACLE.NEXTVAL,temp.* FROM(
        <foreach collection="list" item="approval" separator="union all">
            select
            #{approval.productCode,jdbcType=VARCHAR},
            sysdate,
            #{approval.applicationInstruction,jdbcType=VARCHAR},
            #{approval.applicationType,jdbcType=VARCHAR},
            #{approval.approveTime,jdbcType=DATE},
            #{approval.approveInstruction,jdbcType=VARCHAR},
            #{approval.approveResult,jdbcType=VARCHAR},
            #{approval.system,jdbcType=VARCHAR},
            #{approval.applicant,jdbcType=NUMERIC},
            #{approval.approver,jdbcType=NUMERIC},
            #{approval.applicationTypes,jdbcType=VARCHAR},
            #{approval.isdelete,jdbcType=VARCHAR},
            #{approval.relatedId,jdbcType=VARCHAR},
            #{approval.positionCode,jdbcType=VARCHAR},
            #{approval.organizationCode,jdbcType=VARCHAR},
            #{approval.positionLevel,jdbcType=VARCHAR}
            from DUAL
        </foreach>
        )temp
        )
    </insert>
</mapper>