fj-dc/.svn/pristine/ee/ee238908a2b520aa3bb70947cf417d711ae4b8db.svn-base

<?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="cn.com.goldenwater.dcproj.dao.BisInspGroupArrangementDao">


    <select id="getAllInfoByPnmAndTypeAndOrgId" resultType="cn.com.goldenwater.dcproj.dto.PlanAllInfoDto">
        select * from (
        SELECT
        BIA.PNM AS PNM,
        MAX(BIA.PLAN_DP_ID) AS PLAN_DP_ID,
        SUM(BIA.QUANTITY) AS QUANTITY,
        TO_CHAR(MAX(BIA.STTM), 'YYYY-MM-DD') AS ST_TM,
        TO_CHAR(MAX(BIA.ENTM), 'YYYY-MM-DD') AS EN_TM,
        LISTAGG(BIA.ID, ',') within group (order by BIA.ID) AS IDS,
        BIA.YEAR_TASK_ID AS DTL_ID,
        MAX(BIA.INTM) AS INTM
        FROM BIS_INSP_ALL BIA
        WHERE BIA.IS_PLAN = '1'
        AND LENGTH(BIA.ID) = 12
        <if test="years != null">
            and (to_char(BIA.STTM,'YYYY-MM') = to_char(#{years},'YYYY-MM')  OR to_char(BIA.ENTM,'YYYY-MM') = to_char(#{years},'YYYY-MM'))
        </if>
        <if test="inspectionTeam != null and inspectionTeam != ''">
            AND BIA.PNM LIKE CONCAT('%',CONCAT(#{inspectionTeam},'%'))
        </if>
        <if test="inspectionUnit != null and inspectionUnit != ''">
            AND BIA.PLAN_DP_ID IN (SELECT ID FROM BIS_INSP_PLAN_DP WHERE DP_NAME LIKE CONCAT('%',CONCAT(#{inspectionUnit},'%') ) )
        </if>
        <if test="adName != null and adName != ''">
            AND BIA.ID IN (SELECT DISTINCT BISA.ID FROM BIS_INSP_SEL_AREA BISA WHERE BISA.AD_NAME LIKE CONCAT('%',CONCAT(#{adName},'%') ) )
        </if>
        <if test="planningMatters != null and planningMatters != ''">
            AND BIA.PID IN (
            SELECT B.ID
            FROM BIS_INSP_ALL B
            JOIN (SELECT TO_CHAR(BIPDP.PTYPE || '${orgId}') AS AID
            FROM BIS_INSP_PLAN_DTL_PTYP BIPDP
            LEFT JOIN BIS_INSP_PLAN_DTL BIPD ON BIPD.ID = BIPDP.PLAN_DTL_ID
            LEFT JOIN BIS_INSP_PLAN_YEAR BIPY ON BIPY.ID = BIPD.PLAN_ID
            WHERE BIPY.CHK_NAME LIKE CONCAT('%',CONCAT(#{planningMatters}, '%'))) A ON A.AID = B.PID
            )
        </if>
        <if test="ddAdCode != null and ddAdCode != ''">
            AND BIA.PID IN (
            SELECT B.ID
            FROM BIS_INSP_ALL B
            JOIN (SELECT TO_CHAR(BIPDP.PTYPE || '${orgId}') AS AID
            FROM BIS_INSP_PLAN_DTL_PTYP BIPDP
            LEFT JOIN BIS_INSP_PLAN_DTL BIPD ON BIPD.ID = BIPDP.PLAN_DTL_ID
            LEFT JOIN BIS_INSP_PLAN_YEAR BIPY ON BIPY.ID = BIPD.PLAN_ID
            WHERE BIPY.AD_CODE LIKE CONCAT(#{ddAdCode}, '%')) A ON A.AID = B.PID
            )
        </if>
        <if test="yearId != null and yearId != ''">
            AND BIA.PID IN (
            SELECT B.ID
            FROM BIS_INSP_ALL B
            JOIN (SELECT TO_CHAR(BIPDP.PTYPE || '${orgId}') AS AID
            FROM BIS_INSP_PLAN_DTL_PTYP BIPDP
            LEFT JOIN BIS_INSP_PLAN_DTL BIPD ON BIPD.ID = BIPDP.PLAN_DTL_ID
            LEFT JOIN BIS_INSP_PLAN_YEAR BIPY ON BIPY.ID = BIPD.PLAN_ID
            WHERE BIPY.ID = #{yearId}) A ON A.AID = B.PID
            )
        </if>
        <if test="dtlId != null and dtlId !=''">
            AND (
                BIA.PID IN (
                SELECT B.ID
                FROM BIS_INSP_ALL B
                JOIN (SELECT TO_CHAR(BIPDP.PTYPE || '${orgId}') AS AID
                FROM BIS_INSP_PLAN_DTL_PTYP BIPDP
                LEFT JOIN BIS_INSP_PLAN_DTL BIPD
                ON BIPD.ID = BIPDP.PLAN_DTL_ID
                WHERE BIPD.ID = #{dtlId}) A ON A.AID = B.PID
                ) AND BIA.YEAR_TASK_ID = #{dtlId}
            )
        </if>
        <if test="planDpId != null and planDpId !=''">
            AND BIA.PID IN (
            SELECT B.ID
            FROM BIS_INSP_ALL B
            JOIN (SELECT TO_CHAR(BIPDP.PTYPE || '${orgId}') AS AID
            FROM BIS_INSP_PLAN_DTL_PTYP BIPDP
            LEFT JOIN BIS_INSP_PLAN_DTL BIPD ON BIPD.ID = BIPDP.PLAN_DTL_ID
            LEFT JOIN BIS_INSP_PLAN_YEAR BIPY ON BIPY.ID = BIPD.PLAN_ID
            WHERE BIPY.ID IN (SELECT DISTINCT PLAN_ID FROM BIS_INSP_PLANDP_RL WHERE PLAN_DP_ID = #{planDpId})
            OR BIPY.LEAD_DEP_ID = #{planDpId}
            ) A ON A.AID = B.PID
            )
        </if>
        GROUP BY BIA.PNM,BIA.YEAR_TASK_ID
        <!-- 实施计划查询 按照 插入时间 从前往后 排序 asc -->
        ) order by INTM desc
    </select>
</mapper>