<?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.chinaweal.aiccs.aiccs.expelled.mapper.ExpelledBizInfoMapper">

    <select id="listTodoNoticeInfo" resultType="com.chinaweal.aiccs.aiccs.expelled.vo.BizInfoVo">
        SELECT
            i.ID id,
            i.BIZ_NO bizNo,
            i.TRUTH truth,
            i.REASON reason,
            i.LAW law,
            i."RESULT" result,
            i.REMOVE_TIME removeTime,
            r.ID entId,
            r.NOTICE_NO noticeNo,
            e.ENT_NAME entName,
            e.USCC uscc,
            e.LEREP_NAME lerepName,
            e.ADDRESS address,
            r.SEND_STATUS sendStatus,
            r.SEND_TIME sendTime
        FROM
            EXPELLED_BIZ_INFO i
            LEFT JOIN EXPELLED_ENT_INFO r ON i.ID = r.BIZ_ID
            LEFT JOIN ENT_BASE_EXPELLED_INFO e ON r.EXPELLED_ID = e.ID
        <where>
            AND r.STATUS IN ('0')
            <if test="map.bizNo != null and map.bizNo != ''">
                AND i.BIZ_NO = #{map.bizNo}
            </if>
            <if test="map.entName != null and map.entName != ''">
                AND e.ENT_NAME LIKE '%' || #{map.entName} || '%'
            </if>
            <if test="map.uscc != null and map.uscc != ''">
                AND e.USCC = #{map.uscc}
            </if>
        </where>
    </select>

    <select id="listNoticeEntBaseInfo" resultType="com.chinaweal.aiccs.aiccs.expelled.vo.BizInfoVo">
        SELECT
            r.NOTICE_NO noticeNo,
            e.ENT_NAME entName,
            e.USCC uscc,
            e.LEREP_NAME lerepName,
            e.ADDRESS address
        FROM
            EXPELLED_BIZ_INFO i
            LEFT JOIN EXPELLED_ENT_INFO r ON i.ID = r.BIZ_ID
            LEFT JOIN ENT_BASE_EXPELLED_INFO e ON r.EXPELLED_ID = e.ID
        <where>
            <if test="map.id != null and map.id != ''">
                AND i.ID = #{map.id}
            </if>
            <if test="map.entName != null and map.entName != ''">
                AND e.ENT_NAME LIKE '%' || #{map.entName} || '%'
            </if>
            <if test="map.uscc != null and map.uscc != ''">
                AND e.USCC = #{map.uscc}
            </if>
        </where>
    </select>
</mapper>
