java xml实例

<select id="diseaseMap" resultType="com.cmnit.micro.service.cqjc.common.entity.vo.DiseaseMapVo">
        SELECT
            <if test="query.conditionType == 1">
                case t.direction
                when 1 then CONCAT(t.stake_no,'+',t.end_stake_no,'上行')
                when 2 then CONCAT(t.stake_no,'+',t.end_stake_no,'下行')
                when 3 then CONCAT(t.stake_no,'+',t.end_stake_no,'上下行')
                else CONCAT(t.stake_no,'+',t.end_stake_no,'上行') end diseasePosition,
                t.param param,
                t.record_time pushTime,
                case t.status
                when -3 then '草稿'
                when -2 then '未上报'
                when -1 then '非病害'
                when 0 then '已上报(待决策)'
                when 1 then '已决策'
                when 2 then '申请维修'
                when 3 then '待维修'
                when 4 then '维修中'
                when 5 then '待验收'
                when 6 then '验收合格'
                when 7 then '验收不合格'
                when 8 then '未关联合同'
                when 9 then '已关联合同'
                when 10 then '验收中'
                else '其他' end  diseaseStatus,
            </if>
            <if test="query.conditionType == 2">
                t2.form_name formName,

                group_concat(DISTINCT case t1.direction
                when 1 then CONCAT(t1.stake_no,'+',t1.end_stake_no,'上行')
                when 2 then CONCAT(t1.stake_no,'+',t1.end_stake_no,'下行')
                when 3 then CONCAT(t1.stake_no,'+',t1.end_stake_no,'上下行')
                else CONCAT(t.stake_no,'+',t.end_stake_no,'上行') end) formPosition,

                t2.form_task_time formTaskTime,
                CONCAT(DATE_FORMAT(t2.maintain_start,'%Y-%m-%d'),'-',DATE_FORMAT(t2.maintain_end,'%Y-%m-%d'))  maintTime,
                case
                when t2.form_status = 0 or t2.form_status = 3 then '编辑中'
                when t2.form_status = 1 then '审批中'
                when t2.form_status = 2 then '已驳回'
                when t2.form_status = 4 or t2.form_status = 9 then '待维修'
                when t2.form_status = 5 or t2.form_status = 6 then '维修中'
                when t2.form_status = 7 or t2.form_status = 12  then '待验收'
                when t2.form_status = 8 or t2.form_status = 10 or t2.form_status = 11 or t2.form_status = 13 then '验收中'
                when t2.form_status = 14 then '已完成'
                when t2.form_status = 15 then '待计量'
                when t2.form_status = 16 then '已计量'
                else '其他' end formStatus,
            </if>
            t.*,
            t2.id contructionId
        FROM
            ((
                SELECT
                    t1.*,
                    t2.lat,
                    t2.lng
                FROM
                    t_sys_disease t1
                    LEFT JOIN t_patrol_result_item t2 ON t1.record_id = t2.id
                WHERE
                    t2.lng IS NOT NULL
                    AND t2.lat IS NOT NULL
                    AND t1.disease_from IN ( 1, 2 )
                    ) UNION ALL
                (
                SELECT
                    t1.*,
                    t2.lat,
                    t2.lng
                FROM
                    t_sys_disease t1
                    LEFT JOIN t_fc_result_item t2 ON t1.record_id = t2.id
                WHERE
                    t2.lng IS NOT NULL
                    AND t2.lat IS NOT NULL
                AND t1.disease_from IN ( 3 )
            ))t
        left join t_form_contruction_task t1 on t.id = t1.disease_id
        left join t_form_contruction t2 on t1.form_construction_id = t2.id
        left join t_form_contruction_follow_log t3 on t2.id = t3.form_contruction_id
        left join t_form_contruction_state_change_log t4 on t2.id = t4.form_construction_id
        where
        t.is_deleted = 0
        and
        <foreach collection="query.refOrgIds" separator="or" item="d" open="(" close=")">
            t.create_unit_by like CONCAT(#{d},'%')
        </foreach>
        <if test="query.startDate != null and query.endDate != null and query.taskTimes == null and query.diseaseTimes == null">
            AND t.gmt_create <![CDATA[>=]]>  CONCAT(#{query.startDate},' 00:00:00')
            AND t.gmt_create <![CDATA[<=]]> CONCAT(#{query.endDate},' 23:59:59')
        </if>
        <if test="query.diseaseTypes != null and query.diseaseTypes.size > 0">
            and t.type in (
            <foreach collection="query.diseaseTypes" item="d" separator=",">
                #{d}
            </foreach>
            )
        </if>
        <if test="query.diseaseStatues != null and query.diseaseStatues.size > 0">
            and t.status in (
            <foreach collection="query.diseaseStatues" item="d" separator=",">
                #{d}
            </foreach>
            )
        </if>
        <if test="query.conditionType == null or query.conditionType == 1">
            <if test="query.condition != null and query.condition != ''">
                and t.name like CONCAT('%',#{query.condition},'%')
            </if>
            group by t.id
        </if>
        <if test="query.conditionType == 2">
            <if test="query.condition != null and query.condition != ''">
                and t2.form_name like CONCAT('%',#{query.condition},'%')
            </if>
            and t2.id is not null
            group by t2.id
        </if>
    </select>

<select id="page" resultType="com.cmnit.micro.service.cqjc.common.entity.vo.FormContructionVo">
        select
            t.*,t2.name as constructionDeptName,
            t1.name as 'projectName',
            t4.name as ownerName,
            t5.name as constructionUnitName,
            t8.form_no acceptFormNo,
            t8.approval_id  acceptApprovalId,
            (select count(r.id)
              from t_form_acceptance_package_ref r where r.package_id = t8.id) as packageNum,
            t6.id  acceptanceId
        from t_form_contruction t
        left join t_sys_project_child t1 on t1.id = t.project_id
        left join t_sys_dept t2 on t2.id = t.construction_dept_id
        left join t_sys_user t4 on t.owner_id = t4.id
        left join t_sys_org t5 on t.construction_unit_id = t5.id
        left join t_form_contruction_state_change_log t3 on t3.form_construction_id = t.id
        <if test="toDayStart != null and toDayStart !=''">
            and  t3.gmt_update >= #{toDayStart}
        </if>
        <if test="toDayEnd != null and toDayEnd != ''">
            <![CDATA[ and t3.gmt_update <= #{toDayEnd}]]>
        </if>
        left join t_form_acceptance t6 on t6.form_construction_id = t.id
        left join t_form_acceptance_package_ref t7 on  t7.form_acceptance_id = t6.id
        left join t_form_acceptance_package t8 on t7.package_id = t8.id
        left join t_form_contruction_task task on task.form_construction_id = t.id
        where t.is_deleted = 0
        <if test="query.orgIds != null  and query.orgIds.size>0">
            and
            <foreach collection="query.orgIds" item="d" separator="or" close=")" open="(">
                t.create_unit_by like CONCAT(#{d},'%')
            </foreach>
        </if>
        <if test="query.projectId != null and query.projectId != ''">
            and t.project_id  = #{query.projectId}
        </if>
        <if test="query.constructUnitId != null and query.constructUnitId != ''">
            and t.construction_unit_id = #{query.constructUnitId}
        </if>
        <if test="query.startDate != null and query.startDate != ''">
            and date_format(t.publish_time,'%Y-%m-%d') >= #{query.startDate}
        </if>
        <if test="query.endDate != null and query.endDate != ''">
            and date_format(t.publish_time,'%Y-%m-%d') <![CDATA[<=]]> #{query.endDate}
        </if>
        <if test="query.formStatus != null">
            <choose>
                <when test="query.formStatus == 0">
                    and t.form_status in  (0,3)
                </when>
                <when test="query.formStatus == 1">
                    and t.form_status = 1
                </when>
                <when test="query.formStatus == 2">
                    and t.form_status = 2
                </when>
                <when test="query.formStatus == 3">
                    and t.form_status in (4,9)
                </when>
                <when test="query.formStatus == 4">
                    and t.form_status in (5,6)
                </when>
                <when test="query.formStatus == 5">
                    and t.form_status in (7,12)
                </when>
                <when test="query.formStatus == 6">
                    and t.form_status in (8,11,10,13)
                </when>
                <when test="query.formStatus == 7">
                    and t.form_status = 14
                </when>
                <when test="query.formStatus == 8">
                    and t.form_status = 15
                </when>
                <when test="query.formStatus == 9">
                    and t.form_status = 16
                </when>
            </choose>
        </if>
        <if test="query.isSelf != null and query.isSelf">
            and t.owner_id  = #{query.applyUserId}
        </if>
        <if test="query.keyWords != null and query.keyWords != ''">
            and (
                t.form_name like concat('%', #{query.keyWords},'%')
                or t.form_no like concat('%', #{query.keyWords},'%')
                or t4.name like concat('%', #{query.keyWords},'%')
            )
        </if>
        <if test="!query.isAccepted and query.isAccepted != null">
            and t.id not in (
                select
                    t1.form_construction_id
                from
                t_form_acceptance t1
                where t1.is_deleted = 0
                and t1.form_construction_id is not null
                <if test="query.formAcceptId != null and query.formAcceptId != ''">
                    and t1.id != #{query.formAcceptId}
                </if>
            )
        </if>
        <if test="query.mainType == 0">
            <choose>
                <when test="null != query.isWaiXie and query.isWaiXie">
                    and (t.form_status not in (0,3)
                    or (t.form_status in(0,3) and t.owner_id = #{query.applyUserId}))  /**编辑中包含草稿和任务撤销  管理单位都能看 外协单位只能看自己发起的**/
                </when>
            </choose>
            <if test="query.childTypes != null and query.childTypes.size > 0 ">
                <foreach collection="query.childTypes" item="d">
                    <if test="d == 1">
                        and t3.action = 10
                    </if>
                    <if test="d == 2">
                        and t3.action = 30
                    </if>
                    <if test="d == 3">
                        and t3.action = 50
                    </if>
                    <if test="d == 4">
                        <![CDATA[and t.maintain_end <=  now()]]>
                        and t.form_status not in (14,15,16)
                    </if>
                </foreach>
            </if>
        </if>
        <if test="query.mainType != 0">
            <if test="query.childTypes != null and query.childTypes.size > 0">
                and t.form_status in (
                <foreach collection="query.childTypes" separator="," item="d">
                    <choose>
                        <when test="d == 0">
                            0,3
                        </when>
                        <when test="d == 4">
                            4,9
                        </when>
                        <when test="d == 5">
                            5,6
                        </when>
                        <when test="d == 7">
                            7,12
                        </when>
                        <when test="d == 8">
                            8,10,11,13
                        </when>
                        <otherwise>
                            #{d}
                        </otherwise>
                    </choose>
                </foreach>
                )
            </if>
            <choose>
                <when test="query.mainType == 1">
                    <choose>
                        <when test="null != query.isWaiXie and query.isWaiXie">
                            and (t.form_status in (1,2)
                            or (t.form_status in (0,3) and t.owner_id = #{query.applyUserId})) /**编辑中包含草稿和任务撤销 管理单位都能看 外协单位只能看自己发起的**/
                        </when>
                        <otherwise>
                            and t.form_status in (0,1,2,3)
                        </otherwise>
                    </choose>
                </when>
                <when test="query.mainType == 2">
                    and t.form_status in (4,5,6,9)
                </when>
                <when test="query.mainType == 3">
                    and t.form_status in (7,8,10,11,12,13)
                </when>
                <when test="query.mainType == 4">
                    and t.form_status in (14,15,16)
                </when>
            </choose>
        </if>
        <if test="query.type != null">
            <choose>
                <when test="query.type ==5">
                    and t.type in (4,5)
                </when>
                <when test="query.type ==6">
                    and t.type = 6
                </when>
            </choose>
        </if>

        /**关联病害**/
        <if  test="query.isRefDisease != null and !query.isRefDisease">
            and t.is_ref_disease = 0
        </if>
<!--        <if  test="query.isRefDisease != null and query.isRefDisease">-->
<!--            and t.is_ref_disease = 1-->
<!--        </if>-->

        <if test="query.stakeNo != null and query.stakeNo != ''">
            <choose>
                <when test="query.endStakeNo != null and query.endStakeNo !='' ">
                    and task.end_stake_num >= #{query.stakeNum}
                    and task.stake_num <![CDATA[<=]]> #{query.endStakeNum}
                </when>
                <otherwise>
                    and task.stake_num <![CDATA[<=]]> #{query.stakeNum}
                    and task.end_stake_num >= #{query.stakeNum}
                </otherwise>
            </choose>
        </if>
        <if test="query.sectionId != null and query.sectionId != ''">
            and task.section_id = #{query.sectionId}
        </if>
        group by t.id
        order by t.gmt_create desc
    </select>