<?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.seriousillegal.mapper.TSBizSerIllegalRemMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.chinaweal.aiccs.aiccs.seriousillegal.entity.TSBizSerIllegalRem">
        <id column="bizSerRemID" property="bizSerRemID" />
        <result column="bizSeq" property="bizSeq" />
        <result column="illegalListID" property="illegalListID" />
        <result column="priPID" property="priPID" />
        <result column="regNO" property="regNO" />
        <result column="entName" property="entName" />
        <result column="serIllRea" property="serIllRea" />
        <result column="abnTime" property="abnTime" />
        <result column="remExcpRes" property="remExcpRes" />
        <result column="applyRemRea" property="applyRemRea" />
        <result column="remType" property="remType" />
        <result column="approvalDate" property="approvalDate" />
        <result column="decOrg" property="decOrg" />
        <result column="area_code" property="areaCode" />
        <result column="lauptime" property="lauptime" />
        <result column="lostCreditExplainrem" property="lostCreditExplainrem" />
        <result column="ReconsiderationOrg" property="reconsiderationOrg" />
        <result column="GovernmentOrg" property="governmentOrg" />
        <result column="LitiOrg" property="litiOrg" />
        <result column="remSponsorIllegal" property="remSponsorIllegal" />
        <result column="status" property="status" />
        <result column="isAccept" property="isAccept" />
        <result column="isAgreeRepair" property="isAgreeRepair" />
        <result column="applyTime" property="applyTime" />
        <result column="applicant" property="applicant" />
        <result column="applicantNo" property="applicantNo" />
        <result column="handleStatus" property="handleStatus" />
        <result column="backProposal" property="backProposal" />
        <result column="notRepaired" property="notRepaired" />
        <result column="remark" property="remark" />
        <result column="appOrPc" property="appOrPc" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        bizSerRemID, bizSeq, illegalListID, priPID, regNO, entName, serIllRea, abnTime, remExcpRes, applyRemRea, remType, approvalDate, decOrg, area_code, lauptime,
        lostCreditExplainrem, ReconsiderationOrg, GovernmentOrg, LitiOrg, remSponsorIllegal, status, isAccept, isAgreeRepair, applyTime,applicant,applicantNo,
        handleStatus,backProposal,notRepaired,remark,appOrPc
    </sql>

    <select id="selectBRMserIllegalList"  parameterType="com.chinaweal.aiccs.aiccs.seriousillegal.entity.TSBizSerIllegalRem"
            resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from TSBizSerIllegalRem where 1=1
        <if test=" tsBizSerIllegalRemModel.priPID != null and tsBizSerIllegalRemModel.priPID != ''">
            and pripid = #{tsBizSerIllegalRemModel.priPID}
        </if>
        <if test=" tsBizSerIllegalRemModel.illegalListID != null and tsBizSerIllegalRemModel.illegalListID != ''">
            and illegalListID = #{tsBizSerIllegalRemModel.illegalListID}
        </if>
        <if test=" tsBizSerIllegalRemModel.bizSeq != null and tsBizSerIllegalRemModel.bizSeq != ''">
            and bizseq = #{tsBizSerIllegalRemModel.bizSeq}
        </if>
        and handleStatus not in (4,5)
    </select>

    <select id="queryBRMserIllegalList"  parameterType="com.chinaweal.aiccs.aiccs.seriousillegal.entity.TSBizSerIllegalRem"
            resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from TSBizSerIllegalRem where 1=1
        <if test=" tsBizSerIllegalRemModel.priPID != null and tsBizSerIllegalRemModel.priPID != ''">
            and pripid = #{tsBizSerIllegalRemModel.priPID}
        </if>
        <if test=" tsBizSerIllegalRemModel.illegalListID != null and tsBizSerIllegalRemModel.illegalListID != ''">
            and illegalListID = #{tsBizSerIllegalRemModel.illegalListID}
        </if>
        <if test=" tsBizSerIllegalRemModel.bizSeq != null and tsBizSerIllegalRemModel.bizSeq != ''">
            and bizseq = #{tsBizSerIllegalRemModel.bizSeq}
        </if>
        and (bizseq is null or bizseq !='')
        and handleStatus not in (4,5)
    </select>

    <select id="selectbizRembizseq" parameterType="java.lang.String" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from TSBizSerIllegalRem where bizseq = #{bizseq,jdbcType=CHAR}
    </select>


    <select id="selectbizRemByPagelist" parameterType="com.chinaweal.aiccs.aiccs.seriousillegal.entity.TSBizSerIllegalRem"
            resultType="com.chinaweal.aiccs.aiccs.seriousillegal.entity.TSBizSerIllegalRem">
        select
            t1.bizSerRemID, t1.bizSeq, t1.illegalListID, t1.priPID, t1.regNO, t1.entName, t1.serIllRea,
            t1.abnTime, t1.remExcpRes, t1.applyRemRea, t1.remType, t1.approvalDate, t1.decOrg, t1.lostcreditexplainrem,
            t2.lerepname, t3.address, t4.tel as enttel, t4.postalcode
        from TSBizSerIllegalRem t1
                 left join TREntBase t2 on t1.pripid=t2.pripid
                 left join TREntBaseAddr t3 on t2.pripid = t3.pripid and t3.addrType = '2'
                 left join TREntBaseAppend t4 on t2.pripid = t4.pripid
        where t1.bizseq = #{tsBizSerIllegalRemModel.bizseq,jdbcType=CHAR}
    </select>

    <select id="selectbizRemByPriPid"  parameterType="java.util.Map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from TSBizSerIllegalRem
        <where>
            <if test="params.priPid != null and params.priPid != ''">
                and pripid = #{params.priPid}
            </if>
            <if test="params.bizseq != null and params.bizseq != ''">
                and bizseq = #{params.bizseq}
            </if>
            <if test="params.illegalListID != null and params.illegalListID != ''">
                and illegalListID = #{params.illegalListID}
            </if>
            and handlestatus != '5'
        </where>
        order by applyTime desc
    </select>

    <select id="selectbizRemByPriPidAndBizseq"  parameterType="java.util.Map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from TSBizSerIllegalRem
        <where>
            <if test="params.priPid != null and params.priPid != ''">
                and pripid = #{params.priPid}
            </if>
            <if test="params.bizseq != null and params.bizseq != ''">
                and bizseq = #{params.bizseq}
            </if>
            <if test="params.illegalListID != null and params.illegalListID != ''">
                and illegalListID = #{params.illegalListID}
            </if>
            and handlestatus != '5'
            and bizseq is not null
            and bizseq != ''
        </where>
        order by applyTime desc
    </select>

    <select id="queryHistorgBizRemInfo" resultMap="BaseResultMap">
        select
        tya.SAPPROVECODE as bizseq,
        tya.SAPPROVEREALCODE as illegalListID,
        tya.SREGNO as regno,
        tya.SUNISCID as uniscid,
        tya.sentname as entname,
        tya.SREASONCONTENT as lostCreditExplainrem,
        tswa.SJBRMOBILE as applicantNo,
        tswa.SAPPLYTIME as applyTime
        from tfdb.TB_YZWF_APPROVE tya
        left join tfdb.TB_SHS_WX_APPLY tswa on tya.SAPPROVECODE = tswa.SAPPROVECODE
        where tya.SAPPROVECODE = #{SAPPROVECODE}
    </select>

    <select id="querynoticecontent" resultType="java.lang.String">
        select tbl.NOTICECONTENT from tfdb.tb_yzwf_approve tya left join tfdb.tb_break_law_list_gs tbl
        on tya.sbookcode = tbl.sbookcode
        where 1=1 and tya.sapprovecode = #{SAPPROVECODE}
    </select>
</mapper>
