<?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.punishment.mapper.PersonlockMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.chinaweal.aiccs.aiccs.punishment.entity.Personlock">
        <id column="PLID" property="plid" />
        <result column="IDCARDNO" property="idcardno" />
        <result column="PERNAME" property="pername" />
        <result column="ENTNAME" property="entname" />
        <result column="REGNO" property="regno" />
        <result column="DUTY" property="duty" />
        <result column="ILLEGALITEM" property="illegalitem" />
        <result column="INVOLVEDATE" property="involvedate" />
        <result column="INVOLVEDMONEY" property="involvedmoney" />
        <result column="LOCKREASON" property="lockreason" />
        <result column="LOCKSTARTDATE" property="lockstartdate" />
        <result column="LOCKENDDATE" property="lockenddate" />
        <result column="LOCKUSERNAME" property="lockusername" />
        <result column="LOCKUSERID" property="lockuserid" />
        <result column="LOCKUSERDEPTID" property="lockuserdeptid" />
        <result column="LOCKUSERUNITID" property="lockuserunitid" />
        <result column="UNLOCKUSERNAME" property="unlockusername" />
        <result column="UNLOCKUSERID" property="unlockuserid" />
        <result column="ULDEPTID" property="uldeptid" />
        <result column="ULUNITID" property="ulunitid" />
        <result column="UNLDATE" property="unldate" />
        <result column="LOCKLEVEL" property="locklevel" />
        <result column="LOCKSTATE" property="lockstate" />
        <result column="INPUTDATE" property="inputdate" />
        <result column="RLOCKUSERNAME" property="rlockusername" />
        <result column="RLOCKUSERID" property="rlockuserid" />
        <result column="RLOCKUSERUNITID" property="rlockuserunitid" />
        <result column="RLOCKUSERDEPID" property="rlockuserdepid" />
        <result column="RLOCKDATE" property="rlockdate" />
        <result column="ULOCKSEASON" property="ulockseason" />
        <result column="ENTTYPE" property="enttype" />
        <result column="REGNOID" property="regnoid" />
        <result column="LOCKDUTY" property="lockduty" />
        <result column="WHITELOCKTYPE" property="whitelocktype" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        PLID, IDCARDNO, PERNAME, ENTNAME, REGNO, DUTY, ILLEGALITEM, INVOLVEDATE, INVOLVEDMONEY, LOCKREASON, LOCKSTARTDATE, LOCKENDDATE, LOCKUSERNAME, LOCKUSERID, LOCKUSERDEPTID, LOCKUSERUNITID, UNLOCKUSERNAME, UNLOCKUSERID, ULDEPTID, ULUNITID, UNLDATE, LOCKLEVEL, LOCKSTATE, INPUTDATE, RLOCKUSERNAME, RLOCKUSERID, RLOCKUSERUNITID, RLOCKUSERDEPID, RLOCKDATE, ULOCKSEASON, ENTTYPE, REGNOID, LOCKDUTY, WHITELOCKTYPE
    </sql>
    <select id="selectLockPerson" resultMap="BaseResultMap">
        select <include refid="Base_Column_List" /> from personlock where entname = #{entname} order by lockstartdate desc
    </select>

    <select id="queryAll" resultMap="BaseResultMap">
        SELECT
            ex.zyfzr AS PERNAME,
            ex.dwmc AS ENTNAME,
            e.REGNO AS REGNO,
            '信用风险监管处' AS LOCKUSERNAME,
            e.cerno as IDCARDNO,
            '1' AS LOCKSTATE,
            '1、安全生产黑名单（安监总局）联合惩戒要求进行限制；2、根据《失信企业协同监管和联合惩戒合作备忘录》（发改财金〔2015〕2045号）《广州市人民政府关于印发广州市建立完善守信联合激励和失信联合惩戒机制实施方案的通知》（穗府〔2019〕1号）。 （可办理：一、企业因地址原因列入经营异常名录办理的地址变更，二、企业办理清算小组备案，办理注销）。' AS LOCKREASON
        FROM
            crgs.ex_xxzx_09_aqschmd_aj ex
            LEFT JOIN e_baseinfo e ON ex.dwmc = e.ENTNAME
        WHERE
            e.LEREP IS NOT NULL
          AND e.REGSTATE IN ( '1', '003001', '003' )
          AND ex.zyfzr NOT IN ( SELECT PERNAME FROM personlock) UNION ALL
        SELECT
            e.LEREP AS PERNAME,
            e.ENTNAME AS ENTNAME,
            e.REGNO AS REGNO,
            '信用风险监管处' AS LOCKUSERNAME,
            e.cerno as IDCARDNO,
            '1' AS LOCKSTATE,
            '1、统计上严重失信企业汇总表（国家统计局）联合惩戒要求进行限制；2、根据《失信企业协同监管和联合惩戒合作备忘录》（发改财金〔2015〕2045号）《广州市人民政府关于印发广州市建立完善守信联合激励和失信联合惩戒机制实施方案的通知》（穗府〔2019〕1号）。 （可办理：一、企业因地址原因列入经营异常名录办理的地址变更，二、企业办理清算小组备案，办理注销）。' AS LOCKREASON
        FROM
            crgs.ex_xxzx_13_tjsyzsxqyh ex
            LEFT JOIN e_baseinfo e ON ex.xzxdrmc = e.ENTNAME
        WHERE
            e.LEREP IS NOT NULL
          AND e.REGSTATE IN ( '1', '003001', '003' )
          AND e.LEREP NOT IN ( SELECT PERNAME FROM personlock ) UNION ALL
        SELECT
            e.LEREP AS PERNAME,
            e.ENTNAME AS ENTNAME,
            e.REGNO AS REGNO,
            '信用风险监管处' AS LOCKUSERNAME,
            e.cerno as IDCARDNO,
            '1' AS LOCKSTATE,
            '1、2016年度出入境检验检疫严重失信企业名单（质检总局）联合惩戒要求进行限制；2、根据《失信企业协同监管和联合惩戒合作备忘录》（发改财金〔2015〕2045号）《广州市人民政府关于印发广州市建立完善守信联合激励和失信联合惩戒机制实施方案的通知》（穗府〔2019〕1号）。 （可办理：一、企业因地址原因列入经营异常名录办理的地址变更，二、企业办理清算小组备案，办理注销）。' AS LOCKREASON
        FROM
            crgs.ex_xxzx_14_2016ndcrjj ex
            LEFT JOIN e_baseinfo e ON ex.jyjysysxqymd = e.ENTNAME
        WHERE
            e.LEREP IS NOT NULL
          AND e.REGSTATE IN ( '1', '003001', '003' )
          AND e.LEREP NOT IN ( SELECT PERNAME FROM personlock ) UNION ALL
        SELECT
            e.LEREP AS PERNAME,
            e.ENTNAME AS ENTNAME,
            e.REGNO AS REGNO,
            '信用风险监管处' AS LOCKUSERNAME,
            e.cerno as IDCARDNO,
            '1' AS LOCKSTATE,
            '1、严重质量失信企业名单（质检总局）联合惩戒要求进行限制；2、根据《失信企业协同监管和联合惩戒合作备忘录》（发改财金〔2015〕2045号）《广州市人民政府关于印发广州市建立完善守信联合激励和失信联合惩戒机制实施方案的通知》（穗府〔2019〕1号）。 （可办理：一、企业因地址原因列入经营异常名录办理的地址变更，二、企业办理清算小组备案，办理注销）。' AS LOCKREASON
        FROM
            crgs.ex_xxzx_15_yzzlsxqymd ex
                LEFT JOIN e_baseinfo e ON ex.yzzlsxqymc = e.ENTNAME
        WHERE
            e.LEREP IS NOT NULL
          AND e.REGSTATE IN ( '1', '003001', '003' )
          AND e.LEREP NOT IN ( SELECT PERNAME FROM personlock ) UNION ALL
        SELECT
            e.LEREP AS PERNAME,
            e.ENTNAME AS ENTNAME,
            e.REGNO AS REGNO,
            '信用风险监管处' AS LOCKUSERNAME,
            e.cerno as IDCARDNO,
            '1' AS LOCKSTATE,
            '根据国家38部委关于《失信企业协同监管和联合惩戒合作备忘录》（发改财金[2015]2045号）及《广州市人民政府关于印发广州市建立完善守信联合激励和失信联合惩戒机制实施方案的通知》(穗府[2019]1号)的要求，我局从公共信用信息平台获取到税务部门联合惩戒相关数据，你公司法定代表人被税务部门列为有重大税收案件失信认定名单，要求进行联合惩戒，我局在注册登记系统进行警示限制。（如企业没有因税收违法行为,触犯刑事法律,公司法定代表人、股东被判处刑罚的，可以申请解除联合惩戒警示）（如有疑问咨询85592417）' AS LOCKREASON
        FROM
            crgs.ex_xxzx_16_zdsswfajds ex
                LEFT JOIN e_baseinfo e ON ex.nsrmc = e.ENTNAME
        WHERE
            e.LEREP IS NOT NULL
          AND e.REGSTATE IN ( '1', '003001', '003' )
          AND e.LEREP NOT IN ( SELECT PERNAME FROM personlock ) UNION ALL
        select
            e.LEREP AS PERNAME,
            e.ENTNAME AS ENTNAME,
            e.REGNO AS REGNO,
            '信用风险监管处' AS LOCKUSERNAME,
            e.cerno as IDCARDNO,
            '1' AS LOCKSTATE,
            '1、电子商务失信问题专项治理黑名单汇总表（发改委）联合惩戒要求进行限制；2、根据《失信企业协同监管和联合惩戒合作备忘录》（发改财金〔2015〕2045号）《广州市人民政府关于印发广州市建立完善守信联合激励和失信联合惩戒机制实施方案的通知》（穗府〔2019〕1号）。 （可办理：一、企业因地址原因列入经营异常名录办理的地址变更，二、企业办理清算小组备案，办理注销）。' AS LOCKREASON
        FROM
            crgs.ex_xxzx_19_dzswsxwtzx ex
                LEFT JOIN e_baseinfo e ON ex.qymc = e.ENTNAME
        WHERE
            e.LEREP IS NOT NULL
          AND e.REGSTATE IN ( '1', '003001', '003' )
          AND e.LEREP NOT IN (
            SELECT
                PERNAME
            FROM
                personlock) UNION ALL
        SELECT
            e.LEREP AS PERNAME,
            e.ENTNAME AS ENTNAME,
            e.REGNO AS REGNO,
            '信用风险监管处' AS LOCKUSERNAME,
            e.cerno as IDCARDNO,
            '1' AS LOCKSTATE,
            '1、涉金融黑名单（发改委）联合惩戒要求进行限制；2、根据《失信企业协同监管和联合惩戒合作备忘录》（发改财金〔2015〕2045号）《广州市人民政府关于印发广州市建立完善守信联合激励和失信联合惩戒机制实施方案的通知》（穗府〔2019〕1号）。 （可办理：一、企业因地址原因列入经营异常名录办理的地址变更，二、企业办理清算小组备案，办理注销）。' AS LOCKREASON
        FROM
            crgs.ex_xxzx_20_sjrhmd ex
                LEFT JOIN e_baseinfo e ON ex.sxzt = e.ENTNAME
        WHERE
            e.LEREP IS NOT NULL
          AND e.REGSTATE IN ( '1', '003001', '003' )
          AND e.LEREP NOT IN (
            SELECT
                PERNAME
            FROM
                personlock) UNION ALL
        SELECT
            e.LEREP AS PERNAME,
            e.ENTNAME AS ENTNAME,
            e.REGNO AS REGNO,
            '信用风险监管处' AS LOCKUSERNAME,
            e.cerno as IDCARDNO,
            '1' AS LOCKSTATE,
            '根据国家38部委关于《失信企业协同监管和联合惩戒合作备忘录》（发改财金[2015]2045号）及《广州市人民政府关于印发广州市建立完善守信联合激励和失信联合惩戒机制实施方案的通知》(穗府[2019]1号)的要求，我局从公共信用信息平台获取到人社部门联合惩戒相关数据，你公司法定代表人被人社部门失信名单，要求进行联合惩戒，我局在注册登记系统进行警示限制。（如有疑问咨询85592417）' AS LOCKREASON
        FROM
            crgs.ex_xxzx_21_2018tqnmggz ex
                LEFT JOIN e_baseinfo e ON ex.dxmc = e.ENTNAME
        WHERE
            e.LEREP IS NOT NULL
          AND e.REGSTATE IN ( '1', '003001', '003' )
          AND e.LEREP NOT IN (
            SELECT
                PERNAME
            FROM
                personlock)
    </select>

    <select id="unlocked" resultMap="BaseResultMap">
        SELECT
            *
        FROM
            (
                SELECT
                    '1' AS punishFrom,
                    aa.F000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_09_aqschmd_aj aa
                        LEFT JOIN e_baseinfo eb ON aa.dwmc = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003', '003001' )
                  AND aa.zyfzr IN ( SELECT
                                        PERNAME
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs
                ) UNION ALL
                SELECT
                    '5' AS punishFrom,
                    aa.F000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_13_tjsyzsxqyh aa
                        LEFT JOIN e_baseinfo eb ON aa.xzxdrmc = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003', '003001' )
                  AND eb.LEREP IN ( SELECT
                                        pername
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs ) UNION ALL
                SELECT
                    '6' AS punishFrom,
                    aa.F000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_14_scjr_zjh aa
                        LEFT JOIN e_baseinfo eb ON aa.cfdxmc = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003', '003001' )
                  AND eb.LEREP IN ( SELECT
                                        PERNAME
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs
                ) UNION ALL
                SELECT
                    '7' AS punishFrom,
                    aa.F000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_14_2016ndcrjj aa
                        LEFT JOIN e_baseinfo eb ON aa.jyjysysxqymd = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003', '003001' )
                  AND eb.LEREP IN ( SELECT
                                        PERNAME
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs
                ) UNION ALL
                SELECT
                    '8' AS punishFrom,
                    aa.F000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_15_yzzlsxqymd aa
                        LEFT JOIN e_baseinfo eb ON aa.yzzlsxqymc = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003', '003001' )
                  AND eb.LEREP IN ( SELECT
                                        PERNAME
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs
                ) UNION ALL
                SELECT
                    '9' AS punishFrom,
                    aa.F000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_16_zdsswfajds aa
                        LEFT JOIN e_baseinfo eb ON aa.nsrmc = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003', '003001' )
                  AND eb.LEREP IN ( SELECT
                                        PERNAME
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs
                ) UNION ALL
                SELECT
                    '11' AS punishFrom,
                    aa.F000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_19_dzswsxwtzx aa
                        LEFT JOIN e_baseinfo eb ON aa.qymc = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003', '003001' )
                  AND eb.LEREP IN ( SELECT
                                        PERNAME
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs
                ) UNION ALL
                SELECT
                    '12' AS punishFrom,
                    aa.f000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_20_sjrhmd aa
                        LEFT JOIN e_baseinfo eb ON aa.sxzt = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003001', '003' )
                  AND eb.LEREP IN ( SELECT
                                        PERNAME
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs
                ) UNION ALL
                SELECT
                    '13' AS punishFrom,
                    aa.F000 AS punishInfoId,
                    eb.pripid,
                    eb.uniscid,
                    eb.entname AS entname,
                    eb.regno,
                    eb.LEREP AS PERNAME
                FROM
                    crgs.ex_xxzx_21_2018tqnmggz aa
                        LEFT JOIN e_baseinfo eb ON aa.dxmc = eb.ENTNAME
                WHERE
                    eb.LEREP IS NOT NULL
                  AND eb.REGSTATE IN ( '1', '003', '003001' )
                  AND eb.LEREP IN ( SELECT
                                        PERNAME
                                    FROM
                                        (
                                            SELECT
                                                *
                                            FROM
                                                ( SELECT DISTINCT ( PERNAME ) pname, personlock.* FROM personlock ORDER BY INPUTDATE DESC ) p
                                            GROUP BY
                                                p.ENTNAME
                                            HAVING
                                                p.LOCKSTATE = '0' and p.LOCKUSERNAME='信用风险监管处'
                                        ) rs
                )
            ) res
                LEFT JOIN personlock p ON res.PERNAME = p.PERNAME
    </select>


    <select id="check" resultMap="BaseResultMap">
        select * from personlock where PERNAME=#{lerep} order by INPUTDATE desc limit 1
    </select>


    <select id="lockList" resultMap="BaseResultMap">
        SELECT
            p.PLID,
            p.IDCARDNO,
            p.PERNAME,
            p.tflockid,
            p.WHITELOCKTYPE,
            p.LOCKDUTY,
            p.REGNOID,
            p.ENTTYPE,
            p.ULOCKSEASON,
            p.RLOCKDATE,
            p.RLOCKUSERDEPID,
            p.RLOCKUSERUNITID,
            p.RLOCKUSERID,
            p.RLOCKUSERNAME,
            p.INPUTDATE,
            p.LOCKLEVEL,
            p.LOCKSTATE,
            p.UNLDATE,
            p.ULUNITID,
            p.ULDEPTID,
            p.UNLOCKUSERID,
            p.UNLOCKUSERNAME,
            p.LOCKUSERUNITID,
            p.LOCKUSERID,
            p.LOCKUSERDEPTID,
            p.LOCKUSERNAME,
            p.LOCKENDDATE,
            p.LOCKSTARTDATE,
            p.LOCKREASON,
            p.INVOLVEDMONEY,
            p.ILLEGALITEM,
            p.INVOLVEDATE,
            p.DUTY,
            p.REGNO,
            p.ENTNAME
        FROM
            (
                SELECT DISTINCT
                    PERNAME AS pname,
                    personlock.*
                FROM
                    personlock
                ORDER BY
                    INPUTDATE DESC
            ) AS p
        GROUP BY
            p.ENTNAME
        HAVING
            p.LOCKSTATE = '1' and p.LOCKUSERNAME='信用风险监管处'
    </select>
</mapper>
