<?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.market.mapper.TSUsualInspectMapper">

    <select id="statistics" resultType="java.util.Map">
        select 其它.*, 现场核查.*, 投诉处理.* from
                                                  (select
                                                       0 as "业户认领-认领户数",
                                                       0 as "业户认领-未认领",
                                                       0 as "业户认领-认领率",
                                                       0 as "国家（省）局执照-录入",
                                                       0 as "国家（省）局执照-移交") 其它,

                                                  (select
                                                       count(case when status = '1' then 1 else null end) as "现场核查-检查总数",
                                                       0 as "现场核查-过期检查数",
                                                       0 as "现场核查-过期未检查数",
                                                       0 as "现场核查-检查率",
                                                       count(case when status = '1' and inspectResult = '1' then 1 else null end) as "现场核查-未发现问题",
                                                       count(case when status = '1' and inspectResult = '2' then 1 else null end) as "现场核查-未按规定公示应当公示的信息",
                                                       count(case when status = '1' and inspectResult = '3' then 1 else null end) as "现场核查-公示信息隐瞒真实情况弄虚作假",
                                                       count(case when status = '1' and inspectResult = '4' then 1 else null end) as "现场核查-通过登记住所（经营场所）无法联系",
                                                       count(case when status = '1' and inspectResult = '5' then 1 else null end) as "现场核查-发现问题已责令改正",
                                                       count(case when status = '1' and inspectResult = '6' then 1 else null end) as "现场核查-不配合检查情节严重",
                                                       count(case when status = '1' and inspectResult = '7' then 1 else null end) as "现场核查-未发现开展本次检查涉及的经营活动",
                                                       count(case when status = '1' and inspectResult = '8' then 1 else null end) as "现场核查-发现问题待后续处理"
                                                   from tsusualinspect where inspectOrg = #{orgCode}) 现场核查,

                                                  (select
                                                       count(case when status = '1' then 1 else null end) as "投诉处理-检查总数",
                                                       0 as "投诉处理-过期检查数",
                                                       0 as "投诉处理-检查率",
                                                       count(case when status = '1' and inspectResult = '1' then 1 else null end) as "投诉处理-未发现问题",
                                                       count(case when status = '1' and inspectResult = '2' then 1 else null end) as "投诉处理-未按规定公示应当公示的信息",
                                                       count(case when status = '1' and inspectResult = '3' then 1 else null end) as "投诉处理-公示信息隐瞒真实情况弄虚作假",
                                                       count(case when status = '1' and inspectResult = '4' then 1 else null end) as "投诉处理-通过登记住所（经营场所）无法联系",
                                                       count(case when status = '1' and inspectResult = '5' then 1 else null end) as "投诉处理-发现问题已责令改正",
                                                       count(case when status = '1' and inspectResult = '6' then 1 else null end) as "投诉处理-不配合检查情节严重",
                                                       count(case when status = '1' and inspectResult = '7' then 1 else null end) as "投诉处理-未发现开展本次检查涉及的经营活动",
                                                       count(case when status = '1' and inspectResult = '8' then 1 else null end) as "投诉处理-发现问题待后续处理"
                                                   from tssueinspect where inspectOrg = #{orgCode}) 投诉处理
    </select>

    <select id="getListByPripid" resultType="com.chinaweal.aiccs.aiccs.market.entity.TSUsualInspect">
        select * from v_usual_inspect_info where pripid = #{pripid} order by inspectDate desc
    </select>

    <select id="queryHistroyListByPripid" resultType="com.chinaweal.aiccs.aiccs.market.entity.TSUsualInspect">
        select
            r.scheckid as bizseq,
            e.sentid as pripid,
            e.sentregno as regno,
            e.sentname as entname,
            e.sdom as dom,
            e.sprincipal as legalname,
            case
                when SRESULTTYPECODE = '5' then '6'
                when SRESULTTYPECODE = '6' then ''
                else SRESULTTYPECODE end as inspectResult,
            r.sresulttypename as inspectResultStr,
            r.sresulttypename as inspectContent,
            e.sunitname as inspectOrgName,
            e.dtcheckdate as inspectDate,
            e.sunitid as inspectorg,
            e.suserid as inspectorid,
            e.susername as inspector
        from tfdb.tb_dsm_spot_check_result r
                 left join tfdb.tb_dsm_spot_check_entity e on r.scheckid = e.scheckid
        where e.sentid = #{pripid}
    </select>

    <select id="getCheckAndRevResult" resultType="com.chinaweal.aiccs.aiccs.revoke.Vo.EnableListAndCheckVo">
        select e.uscc, e.entname, e.regOrgCN, e.supOrgCn, t.inspectResult as checkResult,
               CASE t.status
                   WHEN '0' THEN '1'
                   WHEN '1' THEN '2'
                   ELSE '0' END as checkStatus,
               CASE t.status
                   WHEN '0' THEN '待检查'
                   WHEN '1' THEN '已检查'
                   ELSE '无现场检查任务' END as checkStatusCN from tsusualinspect t
                                                                       left join tsusualinspectentlist e
                                                                                 on t.bizSeq = e.bizSeq
        where e.pripid = #{pripid} and t.source = '批量吊销' and t.status in ('0', '1')
        order by t.create_time desc limit 1
    </select>

    <select id="getPageByTask" resultType="com.chinaweal.aiccs.aiccs.market.entity.TSUsualInspect">
        select t.* from tsusualinspect t
        left join tstasklist ta
        on t.bizSeq = ta.bizSeqId
        where ta.bustype = '66' and ta.busstatus in ('0', '-1')
        and (signuserid = #{signuserid} or exists(select 1 from tsrefgroupanduser tg where tg.GROUPID = ta.ACCEPTGROUPID and tg.USERID = #{signuserid}))
        <if test="uscc != null and uscc != ''">
            and t.uscc = #{uscc}
        </if>
        <if test="regno != null and regno != ''">
            and t.regNo = #{regno}
        </if>
        <if test="entName != null and entName != ''">
            and t.entName like concat('%', #{entName}, '%')
        </if>
        <if test="orderField != null and orderField != ''">
            order by ${orderField}
        </if>
        <if test="!orderingRuleBoolean">
            desc
        </if>
        limit #{pageIndex}, #{pageSize}
    </select>

    <select id="countByTask" resultType="java.lang.Integer">
        select count(*) from tsusualinspect t
        left join tstasklist ta
        on t.bizSeq = ta.bizSeqId
        where ta.bustype = '66' and ta.busstatus in ('0', '-1')
        and (signuserid = #{signuserid} or exists(select 1 from tsrefgroupanduser tg where tg.GROUPID = ta.ACCEPTGROUPID and tg.USERID = #{signuserid}))
        <if test="uscc != null and uscc != ''">
            and t.uscc = #{uscc}
        </if>
        <if test="regno != null and regno != ''">
            and t.regNo = #{regno}
        </if>
        <if test="entName != null and entName != ''">
            and t.entName like concat('%', #{entName}, '%')
        </if>
    </select>

    <select id="getbyXyfxbizSeq" resultType="com.chinaweal.aiccs.aiccs.market.entity.TSUsualInspect">
        select * from tsusualinspect where xyxfbizSeq = #{bizseq}
    </select>
</mapper>
