<?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.revoke.mapper.TsbizrevlistMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.chinaweal.aiccs.aiccs.revoke.entity.Tsbizrevlist">
        <id column="REVLISTID" property="revlistid" />
        <result column="BIZSEQ" property="bizseq" />
        <result column="UNISCID" property="uniscid" />
        <result column="PRIPID" property="pripid" />
        <result column="REGNO" property="regno" />
        <result column="ENTNAME" property="entname" />
        <result column="NAME" property="name" />
        <result column="ENTTYPE" property="enttype" />
        <result column="ENTTYPE_CN" property="enttypeCn" />
        <result column="SETTIME" property="settime" />
        <result column="LAUPTIME" property="lauptime" />
        <result column="INSERTTIME" property="insertTime" />
        <result column="STATUS" property="status" />
        <result column="INTOSPONSOR" property="intosponsor" />
        <result column="REMHANDLER" property="remHandler" />
        <result column="ISREMOVE" property="isRemove" />
        <result column="REMNO" property="remNo" />
        <result column="REMOVEREASON" property="removeReason" />
        <result column="REMOVETIME" property="removeTime" />
        <result column="ISHEARING" property="isHearing" />
        <result column="ISHEARINGPUBLISH" property="isHearingPublish" />
        <result column="HEARINGPUBLISHDATE" property="hearingPublishDate" />
        <result column="ISDECISIONPUBLISH" property="isDecisionPublish" />
        <result column="DECISIONPUBLISHDATE" property="decisionPublishDate" />
        <result column="BIZENTTYPE" property="bizEntType" />
        <result column="NOOPENSTATE" property="noOpenState" />
        <result column="decWritStartNo" property="decWritStartNo" />
        <result column="decFirstWrit" property="decFirstWrit" />
        <result column="hearWritStartNo" property="hearWritStartNo" />
        <result column="hearFirstWrit" property="hearFirstWrit" />
        <result column="handleOrg" property="handleOrg" />
        <result column="handleOrgCn" property="handleOrgCn" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        REVLISTID, BIZSEQ, UNISCID, PRIPID, REGNO, ENTNAME, NAME, ENTTYPE, ENTTYPE_CN, SETTIME, LAUPTIME, INSERTTIME,
        STATUS, INTOSPONSOR, ISREMOVE, REMOVEREASON, REMOVETIME, ISHEARING, REMNO, ISHEARINGPUBLISH, HEARINGPUBLISHDATE,
         ISDECISIONPUBLISH, DECISIONPUBLISHDATE, BIZENTTYPE, REMHANDLER, NOOPENSTATE,decWritStartNo,decFirstWrit,
         hearWritStartNo,hearFirstWrit,handleOrg,handleOrgCn
    </sql>

    <sql id="Rev_Ent_Column_list">
        e.REVLISTID,b.BIZSEQ,e.UNISCID, e.PRIPID, e.REGNO, e.ENTNAME, e.NAME, e.ENTTYPE, e.ENTTYPE_CN,SETTIME,
        e.LAUPTIME, e.INSERTTIME,STATUS, INTOSPONSOR, e.ISREMOVE,e.REMOVEREASON, e.REMOVETIME, e.ISHEARING, e.REMNO,
        e.ISHEARINGPUBLISH, e.HEARINGPUBLISHDATE,e.ISDECISIONPUBLISH, e.DECISIONPUBLISHDATE, BIZENTTYPE, e.REMHANDLER,
        e.NOOPENSTATE,e.PRE_PUN_DEC_WRITSNO,e.PRE_HEAR_NOT_WRITSNO
    </sql>

    <sql id="RevZX_Ent_Column_list">
        e.FORCERESID REVLISTID,b.BIZSEQ,e.UNISCID, e.PRIPID, e.REGNO, e.ENTNAME, e.LEREP name, e.ENTTYPE, e.ENTTYPE_CN,
        e.LAUPTIME, e.CREATE_TIME INSERTTIME,b.STATUS, b.INTOSPONSOR, e.ISREMOVE,e.REMOVEREASON, e.REMOVETIME, e.REMNO,
        e.ISDECISIONPUBLISH, e.DECISIONPUBLISHDATE, BIZENTTYPE, e.REMHANDLER,e.NOOPENSTATE
    </sql>

    <select id="queryRevocationTzsList"  resultMap="BaseResultMap">
        SELECT t.REVLISTID, t.BIZSEQ, t.UNISCID, t.PRIPID, t.REGNO, t.ENTNAME, t.NAME, t.ENTTYPE,
         t.ENTTYPE_CN, t.SETTIME, t.LAUPTIME, t.STATUS, t.INTOSPONSOR, t.ISREMOVE, t.REMOVEREASON,
          t.REMOVETIME, t.ISHEARING ,w.writsno as writsno,w.CREATETIME as writsTime,w.AREA_CODE as areaCode,
          w.WRITSTYPE as writstype
        from tsbizrevlist t,tswrit w
        where t.BIZSEQ = w.BIZSEQ
        and t.PRIPID = w.PRIPID
        and w.WRITSTYPE = '32'
        and t.ISREMOVE = '0'
        and t.ISHEARING = '1'
    </select>

    <select id="queryRevocationJdsList"  resultMap="BaseResultMap">
        SELECT t.REVLISTID, t.BIZSEQ, t.UNISCID, t.PRIPID, t.REGNO, t.ENTNAME, t.NAME, t.ENTTYPE,
         t.ENTTYPE_CN, t.SETTIME, t.LAUPTIME, t.STATUS, t.INTOSPONSOR, t.ISREMOVE, t.REMOVEREASON,
          t.REMOVETIME, t.ISHEARING ,w.writsno as writsno,w.CREATETIME as writsTime,w.AREA_CODE as areaCode,
          w.WRITSTYPE as writstype
        from tsbizrevlist t,tswrit w
        where t.BIZSEQ = w.BIZSEQ
        and t.PRIPID = w.PRIPID
        and w.WRITSTYPE = '33'
        and t.ISREMOVE = '0'
        and t.ISHEARING = '1'
    </select>

    <select id="queryRevocationTzsByNameList"  resultMap="BaseResultMap">
        SELECT e.REVLISTID, t.BIZSEQ, e.UNISCID, e.PRIPID, e.REGNO, e.ENTNAME, e.NAME, e.ENTTYPE,
        e.ENTTYPE_CN, t.SETTIME, e.LAUPTIME, t.STATUS, t.INTOSPONSOR, e.ISREMOVE, e.REMOVEREASON,e.HEARINGPUBLISHDATE,
        e.REMOVETIME, e.ISHEARING ,w.writsno as writsno,w.CREATETIME as writsTime,w.AREA_CODE as areaCode,
        w.WRITSTYPE as writstype
        FROM tsbizrevlist t
        LEFT JOIN biz_rev_ent e on t.bizseq = e.bizseq
        left join tswrit w on t.BIZSEQ = w.BIZSEQ
        where e.PRIPID = w.PRIPID
        and w.WRITSTYPE = '32'
        and e.ISREMOVE = '0'
        and e.ISHEARINGPUBLISH = '1'
        <if test="params.entName != null and params.entName != ''">
            and e.ENTNAME like #{params.entName}
        </if>
        ORDER by writsTime DESC
    </select>

    <select id="queryRevocationJdsByNameList"  resultMap="BaseResultMap">
        SELECT e.REVLISTID, t.BIZSEQ, e.UNISCID, e.PRIPID, e.REGNO, e.ENTNAME, e.NAME, e.ENTTYPE,
        e.ENTTYPE_CN, t.SETTIME, e.LAUPTIME, t.STATUS, t.INTOSPONSOR, e.ISREMOVE,e.REMOVEREASON,
        e.REMOVETIME, e.ISHEARING ,w.writsno as writsno,w.CREATETIME as writsTime,w.AREA_CODE as areaCode,
        w.WRITSTYPE as writstype
        FROM tsbizrevlist t
        LEFT JOIN biz_rev_ent e on t.bizseq = e.bizseq
        left join tswrit w on t.BIZSEQ = w.BIZSEQ
        where e.PRIPID = w.PRIPID
        and w.WRITSTYPE = '33'
        and e.ISREMOVE = '0'
        and e.ISDECISIONPUBLISH = '1'
        <if test="params.entName != null and params.entName != ''">
            and e.ENTNAME like #{params.entName}
        </if>
        ORDER by writsTime DESC
    </select>

    <select id="selectNoOpenRevListPage" resultMap="BaseResultMap">
        select <include refid="Rev_Ent_Column_list"/>
        from tsbizrevlist b
        left join biz_rev_ent e on b.bizseq = e.bizseq
        where e.NOOPENSTATE = '1' and e.BIZSEQ = #{paramMap.bizseq}
    </select>

    <select id="selectBizRevEntList" resultMap="BaseResultMap">
        select e.REVLISTID,b.BIZSEQ,e.UNISCID, e.PRIPID, e.REGNO, e.ENTNAME, e.NAME, e.ENTTYPE, e.ENTTYPE_CN,SETTIME,
        e.LAUPTIME, e.INSERTTIME,STATUS, INTOSPONSOR, e.ISREMOVE,e.REMOVEREASON, e.REMOVETIME, e.ISHEARING, e.REMNO,
        e.ISHEARINGPUBLISH, e.HEARINGPUBLISHDATE,e.ISDECISIONPUBLISH, e.DECISIONPUBLISHDATE, BIZENTTYPE, e.REMHANDLER,
        e.NOOPENSTATE
        from tsbizrevlist b
        left join biz_rev_ent e on b.BIZSEQ = e.BIZSEQ
        <where>
            <if test="param.bizseq != null and param.bizseq != ''">
                and b.BIZSEQ = #{param.bizseq}
            </if>
            <if test="param.isRemove != null and param.isRemove != ''">
                and e.ISREMOVE = #{param.isRemove}
            </if>
            <if test="param.revlistid != null and param.revlistid != ''">
                and e.REVLISTID = #{param.revlistid}
            </if>
        </where>
        order by INSERTTIME asc
    </select>

    <select id="selectRevEntPage" resultMap="BaseResultMap">
        select <include refid="Rev_Ent_Column_list"/>
        from tsbizrevlist b
        left join biz_rev_ent e on b.BIZSEQ = e.BIZSEQ
        <where>
            <if test="paramMap.bizseq != null and paramMap.bizseq != ''">
                and b.BIZSEQ = #{paramMap.bizseq}
            </if>
            <if test="paramMap.entname != null and paramMap.entname != ''">
                and e.ENTNAME like #{paramMap.entname}
            </if>
            <if test="paramMap.uniscid != null and paramMap.uniscid != ''">
                and e.UNISCID = #{paramMap.uniscid}
            </if>
            <if test="paramMap.regno != null and paramMap.regno != ''">
                and e.REGNO = #{paramMap.regno}
            </if>
        </where>
        <choose>
            <when test="paramMap.type != null and paramMap.type eq 'all' ">
                order by e.ISREMOVE desc, insertTime asc
            </when>
            <otherwise>
                and e.ISREMOVE = '0' ORDER BY insertTime asc
            </otherwise>
        </choose>
    </select>

    <select id="selectInBiz" resultMap="BaseResultMap">
        select <include refid="Rev_Ent_Column_list"/>
        from tsbizrevlist b
        left join biz_rev_ent e on b.BIZSEQ = e.BIZSEQ
        where e.pripid = #{pripid} and e.ISREMOVE = '0' and b.STATUS not in ('20','21','22')
    </select>

    <select id="getForceLogoutNoticetPage" resultMap="BaseResultMap">
        select <include refid="RevZX_Ent_Column_list"/>
        from tsbizrevlist b
        left join FORCE_LOGOUT_NOTICE_DETAIL e on b.BIZSEQ = e.BIZSEQID
        <where>
            <if test="paramMap.bizseq != null and paramMap.bizseq != ''">
                and b.BIZSEQ = #{paramMap.bizseq}
            </if>
            <if test="paramMap.entname != null and paramMap.entname != ''">
                and e.ENTNAME like #{paramMap.entname}
            </if>
            <if test="paramMap.uniscid != null and paramMap.uniscid != ''">
                and e.UNISCID = #{paramMap.uniscid}
            </if>
            <if test="paramMap.regno != null and paramMap.regno != ''">
                and e.REGNO = #{paramMap.regno}
            </if>
        </where>
        <choose>
            <when test="paramMap.type != null and paramMap.type eq 'all' ">
                order by e.ISREMOVE desc, CREATE_TIME asc
            </when>
            <otherwise>
                and e.ISREMOVE = '0' ORDER BY CREATE_TIME asc
            </otherwise>
        </choose>
    </select>

    <select id="selectInZxBiz" resultMap="BaseResultMap">
        select <include refid="RevZX_Ent_Column_list"/>
        from tsbizrevlist b
        left join FORCE_LOGOUT_NOTICE_DETAIL e on b.BIZSEQ = e.BIZSEQID
        where e.pripid = #{pripid} and e.ISREMOVE = '0' and b.STATUS not in ('34','35')
    </select>
</mapper>
