<?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.business.mapper.TSTaskListMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.chinaweal.aiccs.aiccs.business.entity.TSTaskList">
        <id column="TASKLISTID" property="tasklistid" />
        <result column="WORKFLOWID" property="workflowid" />
        <result column="BIZSEQID" property="bizseqid" />
        <result column="SIGNUSERID" property="signuserid" />
        <result column="SENDERORGID" property="senderorgid" />
        <result column="SENDERUSERID" property="senderuserid" />
        <result column="SENDTOORGID" property="sendtoorgid" />
        <result column="ACCEPTNO" property="acceptno" />
        <result column="BUSNAME" property="busname" />
        <result column="SIGNTIME" property="signtime" />
        <result column="BUSTYPE" property="bustype" />
        <result column="BUSSTATUS" property="busstatus" />
        <result column="LINKNAME" property="linkname" />
        <result column="AUDITDEPTTYPE" property="auditdepttype" />
        <result column="ISSIGNON" property="issignon" />
        <result column="SIGNLOGINNAME" property="signloginname" />
        <result column="SENDERTIME" property="sendertime" />
        <result column="SENDERNAME" property="sendername" />
        <result column="ACCEPTGROUPID" property="acceptgroupid" />
        <result column="AREA_CODE" property="areaCode" />
        <result column="LAUPTIME" property="lauptime" />
        <result column="CREATEUSERID" property="createUserid" />
        <result column="CREATEUSERNAME" property="createUsername" />
        <result column="CREATETIME" property="createTime" />
        <result column="ISDEADLINE" property="isdeadline" />
    </resultMap>

    <resultMap id="TaskListUnionMap" type="com.chinaweal.aiccs.aiccs.business.entity.dto.TaskListUnionDto">
        <id column="TASKLISTID" property="tasklistid" />
        <result column="WORKFLOWID" property="workflowid" />
        <result column="BIZSEQID" property="bizseqid" />
        <result column="SIGNUSERID" property="signuserid" />
        <result column="SENDERORGID" property="senderorgid" />
        <result column="SENDERUSERID" property="senderuserid" />
        <result column="SENDTOORGID" property="sendtoorgid" />
        <result column="ACCEPTNO" property="acceptno" />
        <result column="BUSNAME" property="busname" />
        <result column="SIGNTIME" property="signtime" />
        <result column="BUSTYPE" property="bustype" />
        <result column="BUSSTATUS" property="busstatus" />
        <result column="LINKNAME" property="linkname" />
        <result column="AUDITDEPTTYPE" property="auditdepttype" />
        <result column="ISSIGNON" property="issignon" />
        <result column="SIGNLOGINNAME" property="signloginname" />
        <result column="SENDERTIME" property="sendertime" />
        <result column="SENDERNAME" property="sendername" />
        <result column="ACCEPTGROUPID" property="acceptgroupid" />
        <result column="AREA_CODE" property="areaCode" />
        <result column="LAUPTIME" property="lauptime" />
        <result column="currentNodeOrBizStatus" property="currentNodeOrBizStatus" />
        <result column="currentNode" property="currentNode" />
        <result column="lastNode" property="lastNode" />
        <result column="CREATEUSERID" property="createUserid" />
        <result column="CREATEUSERNAME" property="createUsername" />
        <result column="CREATETIME" property="createTime" />
        <result column="subBusType" property="subBusType" />
        <result column="tasklistType" property="tasklistType" />
        <result column="CURRUSERID" property="curruserid"/>
        <result column="CURRUSERNAME" property="currusername"/>
        <result column="ORGUNITID" property="orgunitid"/>
        <result column="ORGUNITNAME" property="orgunitname"/>
    </resultMap>

    <resultMap id="crgsTaskListMap" type="com.chinaweal.aiccs.crgs.system.entity.TaskList">
        <id column="TASKLISTID" property="tasklistid"/>
        <result column="WORKFLOWID" property="workflowid"/>
        <result column="BIZSEQID" property="bizseqid"/>
        <result column="SIGNUSERID" property="signuserid"/>
        <result column="SENDERORGID" property="senderorgid"/>
        <result column="SENDERUSERID" property="senderuserid"/>
        <result column="SENDTOORGID" property="sendtoorgid"/>
        <result column="ACCEPTNO" property="acceptno"/>
        <result column="BUSNAME" property="busname"/>
        <result column="SIGNTIME" property="signtime"/>
        <result column="BUSTYPE" property="bustype"/>
        <result column="BUSSTATUS" property="busstatus"/>
        <result column="LINKNAME" property="linkname"/>
        <result column="AUDITDEPTTYPE" property="auditdepttype"/>
        <result column="ISSIGNON" property="issignon"/>
        <result column="SIGNLOGINNAME" property="signloginname"/>
        <result column="SENDERTIME" property="sendertime"/>
        <result column="SENDERNAME" property="sendername"/>
        <result column="ACCEPTGROUPID" property="acceptgroupid"/>
        <result column="AREA_CODE" property="areaCode"/>
        <result column="LAUPTIME" property="lauptime"/>
        <result column="uscc" property="uscc"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        TASKLISTID, WORKFLOWID, BIZSEQID, SIGNUSERID, SENDERORGID, SENDERUSERID, SENDTOORGID, ACCEPTNO, BUSNAME, SIGNTIME, BUSTYPE, BUSSTATUS, LINKNAME, AUDITDEPTTYPE, ISSIGNON, SIGNLOGINNAME, SENDERTIME, SENDERNAME, ACCEPTGROUPID, AREA_CODE, LAUPTIME, CREATEUSERID, CREATEUSERNAME, CREATETIME, ISDEADLINE
    </sql>

    <select id="selectByWorkflowID" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from TSTaskList
        where WorkflowID = #{workflowid}
    </select>

    <select id="selectPageWithProcess" resultMap="BaseResultMap">
        select t.*, n.currentNode, p.defname
        from TSTaskList t
        LEFT JOIN TSTwfProcessNode n on t.WorkflowID = n.processid
        LEFT JOIN TSTwfProcess p on t.WorkflowID = p.processid
        where n.status = 0
        <if test="customParamMap != null">
            <if test="customParamMap.bustype != null and customParamMap.bustype != '' ">
                and t.BUSTYPE = #{customParamMap.bustype}
            </if>
            <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                and t.BUSNAME like concat('%',#{customParamMap.searchName},'%')
            </if>
            <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
                and t.ACCEPTNO = #{customParamMap.acceptno}
            </if>
        </if>
        order by t.LAUPTIME desc
    </select>

    <select id="selectByTasklistID" resultMap="BaseResultMap">
        select t.*, n.currentNode, p.defname from TSTaskList t, TSTwfProcessNode n, TSTwfProcess p
        where t.WorkflowID = n.processid
          and t.WorkflowID = p.processid
          and n.status = 0
          and t.TasklistID = #{tasklistid}
    </select>

    <select id="searchTstaskByPage" resultMap="BaseResultMap"
            parameterType="com.chinaweal.aiccs.aiccs.business.entity.TSTaskList">
        SELECT
        v.TasklistID,
        v.WorkflowID,
        v.BizSeqID,
        v.SignUserID,
        v.SenderOrgID,
        v.SenderUserID,
        v.SendToOrgID,
        v.AcceptNo,
        v.BusName,
        v.SignTime,
        v.BusType,
        v.BusStatus,
        v.LinkName,
        v.AuditDeptType,
        v.IsSignOn,
        v.SignLoginName,
        v.SenderTime,
        v.SenderName,
        v.AcceptGroupID,
        v.LaUpTime
        from TSTaskList v
        where v.busStatus = '2'
        <if test="tsTaskListModel.bustype != null and tsTaskListModel.bustype != ''">
            <!--异常名录 只查询本机构做出决定的记录-->
            <if test='tsTaskListModel.bustype == "5"'> <!--列入 包含修改-->
                and v.bustype in ('5','22','23','24')
                and exists(select 1 from TSBizSpeList where BizSeq=v.BizSeqID
                <if test="decorg!=null and decorg!=''">
                    and DecOrg = #{decorg}
                </if> )
            </if>
            <if test='tsTaskListModel.bustype == "6"'> <!--异议-->
                and v.bustype = #{tsTaskListModel.bustype}
                and exists(select 1 from TSBizDissent where BizSeq=v.BizSeqID)
            </if>
            <if test='tsTaskListModel.bustype == "7"'> <!--移出-->
                and v.bustype = #{tsTaskListModel.bustype}
                and exists(select 1 from TSBizRemList where BizSeq=v.BizSeqID
                <if test="decorg!=null and decorg!=''">
                    and DecOrg = #{decorg}
                </if> )
            </if>

            <!--严重违法失信 只查询本机构做出决定的记录-->
            <if test='tsTaskListModel.bustype == "15"'> <!--列入-->
                and v.bustype = #{tsTaskListModel.bustype}
                and exists(select 1 from TSBizSerIllegal where BizSeq=v.BizSeqID
                <if test="decorg!=null and decorg!=''">
                    and DecOrg = #{decorg}
                </if> )
            </if>
            <if test='tsTaskListModel.bustype == "16"'> <!--异议-->
                and v.bustype = #{tsTaskListModel.bustype}
                and exists(select 1 from TSBizSerIllegalDiss where BizSeq=v.BizSeqID)
            </if>
            <if test='tsTaskListModel.bustype == "17"'> <!--移出-->
                and v.bustype = #{tsTaskListModel.bustype}
                and exists(select 1 from TSBizSerIllegalRem where BizSeq=v.BizSeqID
                <if test="decorg!=null and decorg!=''">
                    and DecOrg = #{decorg}
                </if> )
            </if>

            <!--拍卖会备案-->
            <if test='tsTaskListModel.bustype == "26" '>  <!--拍卖前备案-->
                and v.bustype = #{tsTaskListModel.bustype}
                and exists(select 1 from TSAuction where bizseqid = v.BizSeqID)
            </if>
            <if test='tsTaskListModel.bustype == "27" '>  <!--拍卖后备案-->
                and v.bustype = #{tsTaskListModel.bustype}
                and exists(select 1 from TSAfterAuction where bizseqid = v.BizSeqID)
            </if>
            <if test='tsTaskListModel.bustype == "80" or tsTaskListModel.bustype == "81" '>  <!--外单位列严移严-->
                and v.bustype = #{tsTaskListModel.bustype}
            </if>
        </if>
        <if test="flag != null and flag != ''">
            <if test="flag=='abnormal'">
                and v.bustype in ('5','6','7','22','23','24')
            </if>
            <if test="flag=='serIllegal'">
                and v.bustype in ('15','16','17')
            </if>
            <if test="flag=='auction'">
                and v.bustype in ('26','27')
            </if>
        </if>
        <if test="tsTaskListModel.acceptno != null and tsTaskListModel.acceptno != ''">
            and v.AcceptNo = #{tsTaskListModel.acceptno}
        </if>
        <if test="tsTaskListModel.busname != null and tsTaskListModel.busname != ''">
            and v.busname like '%'||#{tsTaskListModel.busname}||'%'
        </if>
        order by v.sendertime desc
    </select>

    <select id="selectUnionTaskPage" resultMap="TaskListUnionMap">
        SELECT m.TASKLISTID, m.WORKFLOWID, m.BIZSEQID, m.SIGNUSERID, m.SENDERORGID, m.SENDERUSERID, m.SENDTOORGID, m.ACCEPTNO as acceptno, m.BUSNAME as busname, m.SIGNTIME as signtime, m.BUSTYPE as bustype, m.BUSSTATUS, m.LINKNAME, m.AUDITDEPTTYPE, m.ISSIGNON, m.SIGNLOGINNAME, m.SENDERTIME as sendertime, m.SENDERNAME as sendername, m.ACCEPTGROUPID, m.AREA_CODE, m.LAUPTIME , m.currentNodeOrBizStatus, m.lastNode, m.CREATETIME, m.ISDEADLINE, m.ISBACK, m.deadlineDate, m.subBusType, m.tasklistType
        FROM (
        (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , b.STATUS as currentNodeOrBizStatus, null as lastNode, t.CREATETIME, null as ISDEADLINE, null as ISBACK, null as deadlineDate, null as subBusType, 'ts' as tasklistType
        from tsrevtasklist t, tsbizrevlist b
        where t.bizseqid = b.bizseq and t.busstatus = 0 and b.ISREMOVE = '0')
        UNION all
        (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME , n.currentNode as currentNodeOrBizStatus, SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode, ta.CREATETIME, ta.ISDEADLINE, ta.ISBACK, ta.deadlineDate, null as subBusType, 'ts' as tasklistType
        from TSTaskList ta
        LEFT JOIN TSTwfProcessNode n on ta.WorkflowID = n.processid
        where n.status = 0 and ta.BUSSTATUS in ('0','-1'))
        UNION all
        (select xr.task_like_id as TASKLISTID, xr.work_flow_id as WORKFLOWID, xr.biz_seq_no as BIZSEQID, xr.sign_user_id as SIGNUSERID, xr.sender_org_id as SENDERORGID, xr.sender_user_id as SENDERUSERID, xr.send_to_org_id as SENDTOORGID, xr.accept_no as ACCEPTNO, xr.bus_name as BUSNAME, xr.sign_time as SIGNTIME, xr.bus_type as BUSTYPE, xr.bus_status as BUSSTATUS, xr.link_name as LINKNAME, null as AUDITDEPTTYPE, xr.is_signon as ISSIGNON, null as SIGNLOGINNAME, xr.sender_time as SENDERTIME, null as SENDERNAME, null as ACCEPTGROUPID, xr.area_code as AREA_CODE,
        activity.update_time as LAUPTIME, xr.bus_status as currentNodeOrBizStatus, activity.link_type as lastNode, xr.create_time as CREATETIME, xr.is_deadline as ISDEADLINE, null as ISBACK, null as deadlineDate,
        (CASE
            WHEN xr.bus_type = '50' THEN
                CASE
                    -- 营业执照作废公告
                    WHEN EXISTS (SELECT 1 FROM xr_liccan_rec_task WHERE biz_seq_no = xr.biz_seq_no AND ROWNUM = 1) THEN '5'
                    -- 终止拟强制注销公告
                    WHEN EXISTS (SELECT 1 FROM xr_force_deregister_rem WHERE biz_seq_no = xr.biz_seq_no AND force_rem_type = '1' AND ROWNUM = 1) THEN '2'
                    -- 恢复登记申请
                    WHEN EXISTS (SELECT 1 FROM xr_force_deregister_rem WHERE biz_seq_no = xr.biz_seq_no AND force_rem_type = '4' AND ROWNUM = 1) THEN '4'
                    -- 拟强制注销公告或强制注销
                    ELSE (SELECT force_type FROM xr_force_deregister_spe WHERE biz_seq_no = xr.biz_seq_no AND ROWNUM = 1)
                END
            ELSE null
        END) as subBusType, 'xr' as tasklistType
        from xr_task_list xr
        inner join xr_activity activity on xr.task_like_id = activity.task_id and activity.link_status = '1'
        and activity.user_id = #{customParamMap.userId}
        where xr.bus_status in ('0','-1'))
        ) m
        <where>
            <if test="customParamMap != null">
                <if test="customParamMap.UNISCID and customParamMap.UNISCID != '' ">
                    and m.BIZSEQID in (
                    select BIZSEQ from aiccs.tsbizremlist tsb left join e_baseinfo eb on tsb.PRIPID = eb.PRIPID
                    where 1=1
                    and eb.UNISCID = #{customParamMap.UNISCID}
                    union all
                    select BIZSEQ from aiccs.tsbizserillegalrem tsb left join e_baseinfo eb on tsb.PRIPID =
                    eb.PRIPID
                    where 1=1
                    and eb.UNISCID = #{customParamMap.UNISCID}
                    )
                </if>
                <if test="customParamMap.REGNO and customParamMap.REGNO != '' ">
                    and m.BIZSEQID in (
                    select BIZSEQ from aiccs.tsbizremlist tsb left join e_baseinfo eb on tsb.PRIPID = eb.PRIPID
                    where 1=1
                    and eb.REGNO = #{customParamMap.REGNO}
                    union all
                    select BIZSEQ from aiccs.tsbizserillegalrem tsb left join e_baseinfo eb on tsb.PRIPID =
                    eb.PRIPID
                    where 1=1
                    and eb.REGNO = #{customParamMap.REGNO}
                    )
                </if>
                <if test="customParamMap.bustype != null and customParamMap.bustype != '' ">
                    and m.BUSTYPE = #{customParamMap.bustype}
                </if>
                <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                    and m.BUSNAME like concat('%',#{customParamMap.searchName},'%')
                </if>
                <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
                    and m.ACCEPTNO = #{customParamMap.acceptno}
                </if>
                <if test="customParamMap.subBusType != null and customParamMap.subBusType != ''">
                    and m.subBusType = #{customParamMap.subBusType}
                </if>
                <if test="customParamMap.type != null and customParamMap.type != ''">
                    <if test="customParamMap.type == 'abnormal'.toString()">
                        and m.BUSTYPE in ('5','6','11','13','71','72','73')
                    </if>
                    <if test="customParamMap.type == 'illegal'.toString()">
                        and m.BUSTYPE in ('55','15','16','17')
                    </if>
                    <if test="customParamMap.type == 'revoke'.toString()">
                        and m.BUSTYPE in ('51','52','53','54')
                    </if>
                    <if test="customParamMap.type == 'revokeZX'.toString()">
                        and m.BUSTYPE in ('91')
                    </if>
                    <if test="customParamMap.type == 'punishment'.toString()">
                        and m.BUSTYPE in ('61','62')
                    </if>
                    <if test="customParamMap.type == 'cancel'.toString()">
                        and m.BUSTYPE in ('31','81','82','84','85','86','87','88','89')
                    </if>
                    <if test="customParamMap.type == 'repair'.toString()">
                        and m.BUSTYPE in ('33','7','12','14','27')
                        <if test="customParamMap.gzrStartDate != null and customParamMap.gzrStartDate != '' ">
                            and m.deadlineDate between #{customParamMap.gzrStartDate} and #{customParamMap.gzrEndDate}
                        </if>
                        <if test="customParamMap.gzrDate != null and customParamMap.gzrDate != '' ">
                            and m.deadlineDate &lt; #{customParamMap.gzrDate}
                        </if>
                    </if>
                    <if test="customParamMap.type == 'maketProblem'.toString()">
                        and m.BUSTYPE in ('66')
                    </if>
                    <if test="customParamMap.type == 'gzis'.toString()">
                        and m.BUSTYPE in ('30')
                    </if>
                    <if test="customParamMap.type == 'correct'.toString()">
                        and m.BUSTYPE in ('40','41')
                    </if>
                    <if test="customParamMap.type == 'forcederegister'.toString()">
                        and m.BUSTYPE in ('50')
                    </if>
                    <if test="customParamMap.busstatus != null and customParamMap.busstatus == '1'.toString() ">
                        and m.currentNodeOrBizStatus in ('exptlistEnter',
                        'acceptDissentEnter',
                        'serIllegalEnter',
                        'revCancelEnter',
                        'startAccept',
                        'caseEnter',
                        'feedbackEnter',
                        'dissentEnter',
                        'annualDissentEnter',
                        'approveDissent',
                        'punishEnter',
                        '1',
                        '4',
                        '7',
                        '10'
                        )
                    </if>
                    <if test="customParamMap.busstatus != null and customParamMap.busstatus == '2'.toString() ">
                        and m.currentNodeOrBizStatus in ('handleExptlist',
                        'handleAnnualDissent',
                        'handleDissentAccept',
                        'handleSerIllegal',
                        'handleRevCancel',
                        'handleCancel',
                        'handleCase',
                        'handleFeedback',
                        'handlePunish',
                        'handleDissent',
                        'handleResultAdd'
                        '2',
                        '5',
                        '8',
                        '11',
                        '15',
                        '29'
                        )
                    </if>
                    <if test="customParamMap.busstatus != null and customParamMap.busstatus == '3'.toString() ">
                        and m.currentNodeOrBizStatus in ('approvelist',
                        'approveSerIllegal',
                        'approveRevCancel',
                        'approvePunish',
                        'approvalCancel',
                        'approvalCase',
                        'approvalResultAdd',
                        'approveAnnualDissent',
                        'handleDissentApprove',
                        'handleDissentAccept',
                        'passDissent',
                        '3',
                        '6',
                        '9',
                        '13',
                        '16',
                        '24',
                        '25',
                        '26',
                        '27',
                        '28'
                        )
                    </if>
                    <if test="customParamMap.busstatus != null and customParamMap.busstatus == '4'.toString() ">
                        and m.currentNodeOrBizStatus in ('14')
                    </if>
                </if>
            </if>
            and ( m.SIGNUSERID = #{customParamMap.userId}
                or (m.currentNodeOrBizStatus in ('exptlistEnter','startAccept','serIllegalEnter') AND m.SIGNUSERID IS null
                    <choose>
                        <when test="customParamMap.checkChildUnit eq '0'.toString()">
                            AND m.area_code = #{customParamMap.areaCode}
                        </when>
                        <when test="customParamMap.checkChildUnit eq '1'.toString()">
                            AND m.area_code like #{customParamMap.areaCode}
                        </when>
                        <otherwise>
                            AND m.area_code = #{customParamMap.areaCode}
                        </otherwise>
                    </choose>
                )
                or exists (select 1 from tsrefgroupanduser tg
                            where tg.GROUPID = m.ACCEPTGROUPID and tg.USERID = #{customParamMap.userId}))
        </where>
        <choose>
            <when test="customParamMap.orderName != null and customParamMap.orderName != ''">
                <choose>
                    <when test="customParamMap.orderName == 'bustypename'.toString()">
                        ORDER BY bustype ${customParamMap.orderType}, m.LAUPTIME DESC
                    </when>
                    <when test="customParamMap.orderName == 'busStatusName'.toString()">
                        ORDER BY m.currentNodeOrBizStatus ${customParamMap.orderType}, m.LAUPTIME DESC
                    </when>
                    <otherwise>
                        ORDER BY ${customParamMap.orderName} ${customParamMap.orderType}, m.LAUPTIME DESC
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                ORDER BY m.ISDEADLINE desc, m.LAUPTIME DESC
            </otherwise>
        </choose>
    </select>

    <select id="unionTaskPageStatistic" resultType="java.util.HashMap">
        SELECT
        count(1) as "allnum",
        sum(case WHEN m.BUSTYPE in ('5','6','11','13','71','72','73') then 1 else 0 end) as "abnormalnum",
        sum(case WHEN m.BUSTYPE in ('55','15','16','17') then 1 else 0 end) as "illegalnum",
        sum(case WHEN m.BUSTYPE in ('51','52','53','54') then 1 else 0 end) as "revokenum",
        sum(case WHEN m.BUSTYPE in ('61','62') then 1 else 0 end) as "punishmentnum",
        sum(case WHEN m.BUSTYPE in ('31','81','82') then 1 else 0 end) as "cancelnum",
        sum(case WHEN m.BUSTYPE in ('33','7','12','14','27') then 1 else 0 end) as "repairnum",
        sum(case WHEN m.BUSTYPE in ('66') then 1 else 0 end) as "marketnum",
        sum(case WHEN m.BUSTYPE in ('30') then 1 else 0 end) as "gzisnum",
        sum(case WHEN m.BUSTYPE in ('40','41') then 1 else 0 end) as "correctnum",
        sum(case WHEN m.BUSTYPE in ('50') then 1 else 0 end) as "forcederegisternum"
        FROM (
            (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , b.STATUS as currentNodeOrBizStatus, null as lastNode, t.CREATETIME, null as ISDEADLINE
            from tsrevtasklist t, tsbizrevlist b
            where t.bizseqid = b.bizseq and t.busstatus = 0 and b.ISREMOVE = '0')
        UNION all
            (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME , n.currentNode as currentNodeOrBizStatus, SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode, ta.CREATETIME, ta.ISDEADLINE
            from TSTaskList ta
            LEFT JOIN TSTwfProcessNode n on ta.WorkflowID = n.processid
            where n.status = 0 and ta.BUSSTATUS in ('0','-1'))
        UNION all
            (select xr.task_like_id as TASKLISTID, xr.work_flow_id as WORKFLOWID, xr.biz_seq_no as BIZSEQID, xr.sign_user_id as SIGNUSERID, xr.sender_org_id as SENDERORGID, xr.sender_user_id as SENDERUSERID, xr.send_to_org_id as SENDTOORGID, xr.accept_no as ACCEPTNO, xr.bus_name as BUSNAME, xr.sign_time as SIGNTIME, xr.bus_type as BUSTYPE, xr.bus_status as BUSSTATUS, xr.link_name as LINKNAME, null as AUDITDEPTTYPE, xr.is_signon as ISSIGNON, null as SIGNLOGINNAME, xr.sender_time as SENDERTIME, null as SENDERNAME, null as ACCEPTGROUPID, xr.area_code as AREA_CODE, null as LAUPTIME, xr.bus_status as currentNodeOrBizStatus, null as lastNode, xr.create_time as CREATETIME, xr.is_deadline as ISDEADLINE
            from xr_task_list xr
            where xr.bus_status in ('0','-1'))
        ) m
        <where>
            <if test="customParamMap != null">
                <if test="customParamMap.UNISCID and customParamMap.UNISCID != '' ">
                    and m.BIZSEQID in (
                    select BIZSEQ from aiccs.tsbizremlist tsb left join e_baseinfo eb on tsb.PRIPID = eb.PRIPID
                    where 1=1
                    and eb.UNISCID = #{customParamMap.UNISCID}
                    union all
                    select BIZSEQ from aiccs.tsbizserillegalrem tsb left join e_baseinfo eb on tsb.PRIPID =
                    eb.PRIPID
                    where 1=1
                    and eb.UNISCID = #{customParamMap.UNISCID}
                    )
                </if>
                <if test="customParamMap.REGNO and customParamMap.REGNO != '' ">
                    and m.BIZSEQID in (
                    select BIZSEQ from aiccs.tsbizremlist tsb left join e_baseinfo eb on tsb.PRIPID = eb.PRIPID
                    where 1=1
                    and eb.REGNO = #{customParamMap.REGNO}
                    union all
                    select BIZSEQ from aiccs.tsbizserillegalrem tsb left join e_baseinfo eb on tsb.PRIPID =
                    eb.PRIPID
                    where 1=1
                    and eb.REGNO = #{customParamMap.REGNO}
                    )
                </if>
                <if test="customParamMap.currentNodeOrBizStatus.toString() != null and customParamMap.currentNodeOrBizStatus.toString() != '' ">
                    and m.currentNodeOrBizStatus = #{customParamMap.currentNodeOrBizStatus}
                </if>

            </if>
            and ( m.SIGNUSERID = #{customParamMap.userId}
            or (m.area_code = #{customParamMap.areaCode} AND m.currentNodeOrBizStatus in ('exptlistEnter','startAccept','serIllegalEnter') AND m.SIGNUSERID IS null)
            or exists (select 1 from tsrefgroupanduser tg where tg.GROUPID = m.ACCEPTGROUPID and tg.USERID = #{customParamMap.userId}))
        </where>
    </select>

    <select id="getRiskStatistic" resultType="int">
        SELECT count(1)
        FROM crgs.tstasklist t,
        crgs.tstwfprocessnode n,
        crgs.TSTwfProcess p,
        crgs.biz_credit bc
        WHERE t.workflowid = n.processid
        and t.workflowid = p.processid
        and t.bizseqid = bc.bizseq
        <if test="customParamMap!=null">
            <if test="customParamMap.userId!= null and customParamMap.userId !=''">
                AND n.curruserid = #{customParamMap.userId}
            </if>
            <if test="customParamMap.bustype != null and customParamMap.bustype != '' ">
                and t.BUSTYPE = #{customParamMap.bustype}
            </if>
            <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                and t.BUSNAME like concat('%',#{customParamMap.searchName},'%')
            </if>
            <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
                and t.ACCEPTNO = #{customParamMap.acceptno}
            </if>
            <if test="customParamMap.currentNodeOrBizStatus.toString() != null and customParamMap.currentNodeOrBizStatus.toString() != '' ">
                and n.currentNode = #{customParamMap.currentNodeOrBizStatus}
            </if>
        </if>
        AND n.status = '0'
    </select>

    <select id="getCheckPage" resultMap="BaseResultMap">
        SELECT t.*,n.currentNode as currentNodeOrBizStatus, p.defname, bc.uscc
        FROM crgs.tstasklist t,
        crgs.tstwfprocessnode n,
        crgs.TSTwfProcess p,
        crgs.biz_credit bc
        WHERE t.workflowid = n.processid
        and t.workflowid = p.processid
        and t.bizseqid = bc.bizseq
        <if test="customParamMap!=null">
            <if test="customParamMap.userId!= null and customParamMap.userId !=''">
                AND n.curruserid = #{customParamMap.userId}
            </if>
            <if test="customParamMap.bustype != null and customParamMap.bustype != '' ">
                and t.BUSTYPE = #{customParamMap.bustype}
            </if>
            <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                and t.BUSNAME like concat('%',#{customParamMap.searchName},'%')
            </if>
            <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
                and t.ACCEPTNO = #{customParamMap.acceptno}
            </if>
            <if test="customParamMap.currentNodeOrBizStatus.toString() != null and customParamMap.currentNodeOrBizStatus.toString() != '' ">
                and n.currentNode = #{customParamMap.currentNodeOrBizStatus}
            </if>
        </if>
        AND n.status = '0'
    </select>
    <select id="selectCurrentExecUserInfo" resultMap="TaskListUnionMap">
        SELECT tu.USERID CURRUSERID,
        tu.REALNAME CURRUSERNAME,
        torg.ORGUNITID ORGUNITID,
        torg.ORGUNITNAME ORGUNITNAME
        FROM TSOPINION n LEFT JOIN CXAICORG.T_USERS tu ON tu.USERID = n.HANDLERID
        LEFT JOIN CXAICORG.T_ORGUNITS torg ON tu.ORGUNITID = torg.ORGUNITID
        WHERE n.WORKFLOWID = #{workFlowId}
        ORDER BY n.OPINIONTYPE DESC LIMIT 1
    </select>
    <select id="selectFinishUnionTaskPage" resultMap="TaskListUnionMap">
        SELECT *
        from(
            <choose>
                <when test="customParamMap.finished != null and customParamMap.finished != '' ">
                    (select DISTINCT ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME,ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME , n.currentNode as currentNodeOrBizStatus,SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode, ta.CREATETIME
                    from TSTaskList ta
                    LEFT JOIN TShwfProcessNode n on ta.WorkflowID = n.processid
                    where ta.BUSSTATUS = 2 AND ta.WORKFLOWID IN (
                    SELECT
                    DISTINCT WORKFLOWID
                    FROM
                    tsopinion tso
                    <where>
                        <if test="customParamMap.childUnitSwitch != null and customParamMap.childUnitSwitch != ''">
                            <choose>
                                <when test='customParamMap.childUnitSwitch eq "1"'>
                                    and (
                                    tso.HANDLERID = #{customParamMap.userId}
                                    or exists(select 1 from CXAICORG.T_USERS tu left join CXAICORG.T_ORGUNITS torg on tu.ORGUNITID = torg.ORGUNITID
                                    where tso.HANDLERID = tu.USERID and torg.ORGNUMBER like #{customParamMap.myOrgNumberLike}
<!--                                    and torg.ORGNUMBER != #{customParamMap.myOrgNumber}-->
                                    ))
                                </when>
                                <otherwise>
                                    and tso.HANDLERID = #{customParamMap.userId}
                                </otherwise>
                            </choose>
                        </if>
                    </where>) and REGEXP_LIKE(n.currentNode, '^end|over', 'i') = 1)
                union all
                    (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , n.currentNode as currentNodeOrBizStatus ,SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode, t.CREATETIME
                    from tsrevtasklist t
                    LEFT JOIN TShwfProcessNode n on t.WorkflowID = n.processid
                    where t.BUSSTATUS = 2 AND t.WORKFLOWID IN (
                    SELECT
                    DISTINCT WORKFLOWID
                    FROM
                    tsopinion tso
                    <where>
                        <if test="customParamMap.childUnitSwitch != null and customParamMap.childUnitSwitch != ''">
                            <choose>
                                <when test='customParamMap.childUnitSwitch eq "1"'>
                                    and (
                                    tso.HANDLERID = #{customParamMap.userId}
                                    or exists(select 1 from CXAICORG.T_USERS tu left join CXAICORG.T_ORGUNITS torg on tu.ORGUNITID = torg.ORGUNITID
                                    where tso.HANDLERID = tu.USERID and torg.ORGNUMBER like #{customParamMap.myOrgNumberLike}
<!--                                    and torg.ORGNUMBER != #{customParamMap.myOrgNumber}-->
                                    ))
                                </when>
                                <otherwise>
                                    and tso.HANDLERID = #{customParamMap.userId}
                                </otherwise>
                            </choose>
                        </if>
                    </where>
                    ) and REGEXP_LIKE(n.currentNode, '^end|over', 'i') = 1)
                union all
                </when>
            </choose>
            (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , b.STATUS as currentNodeOrBizStatus, null as lastNode, t.CREATETIME
            from tsrevtasklist t, tsbizrevlist b,TShwfProcessNode n
            where t.bizseqid = b.bizseq and t.busstatus = '0' and b.ISREMOVE = '0' AND REGEXP_LIKE(n.currentNode, '^end|over', 'i') = 1
            and t.WorkflowID = n.processid and t.WORKFLOWID in (SELECT DISTINCT WORKFLOWID FROM tsopinion tso
            <where>
                <if test="customParamMap.childUnitSwitch != null and customParamMap.childUnitSwitch != ''">
                    <choose>
                        <when test='customParamMap.childUnitSwitch eq "1"'>
                            and (
                            tso.HANDLERID = #{customParamMap.userId}
                            or exists(select 1 from CXAICORG.T_USERS tu left join CXAICORG.T_ORGUNITS torg on tu.ORGUNITID = torg.ORGUNITID
                            where tso.HANDLERID = tu.USERID and torg.ORGNUMBER like #{customParamMap.myOrgNumberLike}
<!--                            and torg.ORGNUMBER != #{customParamMap.myOrgNumber}-->
                            ))
                        </when>
                        <otherwise>
                            and tso.HANDLERID = #{customParamMap.userId}
                        </otherwise>
                    </choose>
                </if>
            </where>))
        union all
            (select DISTINCT ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME, n.currentNode as currentNodeOrBizStatus,SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode, ta.CREATETIME
            from TSTaskList ta
            LEFT JOIN TSTwfProcessNode n on ta.WorkflowID = n.processid
            where ta.BUSSTATUS in ('-1', '0', '1', '3', '5') AND REGEXP_LIKE(n.currentNode, '^end|over', 'i') = 1
            <if test="customParamMap.childUnitSwitch != null and customParamMap.childUnitSwitch != ''">
                <choose>
                    <when test='customParamMap.childUnitSwitch eq "1"'>
                        and (
                        n.CURRUSERID = #{customParamMap.userId}
                        or exists(select 1 from CXAICORG.T_USERS tu left join CXAICORG.T_ORGUNITS torg on tu.ORGUNITID = torg.ORGUNITID
                        where n.CURRUSERID = tu.USERID and torg.ORGNUMBER like #{customParamMap.myOrgNumberLike}
<!--                        and torg.ORGNUMBER != #{customParamMap.myOrgNumber}-->
                        ))
                    </when>
                    <otherwise>
                        and n.CURRUSERID = #{customParamMap.userId}
                    </otherwise>
                </choose>
            </if>
        )
        ) m
        <where>
            <if test="customParamMap != null">
                <if test="customParamMap.startDate != null and customParamMap.startDate != '' ">
                    and date_format(m.CREATETIME,'%Y-%m-%d') &gt;= #{customParamMap.startDate}
                </if>
                <if test="customParamMap.endDate != null and customParamMap.endDate != '' ">
                    and date_format(m.CREATETIME,'%Y-%m-%d') &lt;= #{customParamMap.endDate}
                </if>
                <if test="customParamMap.UNISCID and customParamMap.UNISCID != '' ">
                    and m.BIZSEQID in (
                    select BIZSEQ from aiccs.tsbizremlist tsb left join e_baseinfo eb on tsb.PRIPID = eb.PRIPID
                    where 1=1
                    and eb.UNISCID = #{customParamMap.UNISCID}
                    union all
                    select BIZSEQ from aiccs.tsbizserillegalrem tsb left join e_baseinfo eb on tsb.PRIPID =
                    eb.PRIPID
                    where 1=1
                    and eb.UNISCID = #{customParamMap.UNISCID}
                    )
                </if>
                <if test="customParamMap.REGNO and customParamMap.REGNO != '' ">
                    and m.BIZSEQID in (
                    select BIZSEQ from aiccs.tsbizremlist tsb left join e_baseinfo eb on tsb.PRIPID = eb.PRIPID
                    where 1=1
                    and eb.REGNO = #{customParamMap.REGNO}
                    union all
                    select BIZSEQ from aiccs.tsbizserillegalrem tsb left join e_baseinfo eb on tsb.PRIPID =
                    eb.PRIPID
                    where 1=1
                    and eb.REGNO = #{customParamMap.REGNO}
                    )
                </if>
                <if test="customParamMap.bustype != null and customParamMap.bustype != '' ">
                    and m.BUSTYPE = #{customParamMap.bustype}
                </if>
                <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                    and m.BUSNAME = #{customParamMap.searchName}
                </if>
                <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
                    and m.ACCEPTNO = #{customParamMap.acceptno}
                </if>
                <if test="customParamMap.currentNode != null and customParamMap.currentNode != '' ">
                    and m.currentNodeOrBizStatus = #{customParamMap.currentNode}
                </if>
                <if test="customParamMap.lastNode != null and customParamMap.lastNode != '' ">
                    and m.lastNode = #{customParamMap.lastNode}
                </if>
                <if test="customParamMap.busStatus != null and customParamMap.busStatus == '1'.toString() ">
                    and m.currentNodeOrBizStatus in ('exptlistEnter',
                    'serIllegalEnter',
                    'revCancelEnter',
                    'startAccept',
                    'caseEnter',
                    'feedbackEnter',
                    '1',
                    '4',
                    '7',
                    '10'
                    )
                </if>
                <if test="customParamMap.busStatus != null and customParamMap.busStatus == '2'.toString() ">
                    and m.currentNodeOrBizStatus in ('handleExptlist',
                    'handleSerIllegal',
                    'handleRevCancel',
                    'handleCancel',
                    'handleCase',
                    'handleFeedback',
                    'endInspect',
                    'startInspect',
                    '2',
                    '5',
                    '8',
                    '24',
                    '25',
                    '26',
                    '29',
                    '15'
                    )
                </if>
                <if test="customParamMap.busStatus != null and customParamMap.busStatus == '3'.toString() ">
                    and m.currentNodeOrBizStatus in ( 'approvelist',
                    'approveSerIllegal',
                    'approveRevCancel',
                    'approvalCancel',
                    'approvalCase',
                    '3',
                    '6',
                    '24',
                    '25',
                    '26',
                    '9',
                    '27',
                    '28',
                    '29',
                    '13',
                    '16'
                    )
                </if>
                <if test="customParamMap.busStatus != null and customParamMap.busStatus == '4'.toString() ">
                    and m.currentNodeOrBizStatus in ('endExptlist',
                    'endDissent',
                    'endSerIllegal',
                    'endRevoke',
                    'endRevCancel',
                    'endPunish',
                    'endCase',
                    'endFeedback',
                    '14','noRegister','cancelShow','noRegister')
                </if>
                <if test="customParamMap.busStatus != null and customParamMap.busStatus == '5'.toString() ">
                    and m.currentNodeOrBizStatus in ('overExptlist',
                    'overDissent',
                    'overSerIllegal',
                    'overRevoke',
                    'overRevCancel',
                    'overPunish',
                    'overCase',
                    'overFeedback'
                    )
                </if>
                <if test="customParamMap.busStatus != null and customParamMap.busStatus == '6'.toString() ">
                    and m.currentNodeOrBizStatus = 'clueReturn'
                </if>
                <if test="customParamMap.busStatus != null and customParamMap.busStatus == '7'.toString() ">
                    and m.currentNodeOrBizStatus in ('overNullify','noAccept')
                </if>
            </if>
        </where>
        ORDER BY m.LAUPTIME DESC
    </select>

    <select id="searchUnionTaskList" resultMap="TaskListUnionMap">
        SELECT *
        FROM (
        (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , b.STATUS as currentNodeOrBizStatus
        from tsrevtasklist t, tsbizrevlist b
        where t.bizseqid = b.bizseq and t.busstatus = 0 and b.ISREMOVE = '0')
        UNION all
        (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME , n.currentNode as currentNodeOrBizStatus
        from TSTaskList ta
        LEFT JOIN TSTwfProcessNode n on ta.WorkflowID = n.processid
        LEFT JOIN TSTwfProcess p on ta.WorkflowID = p.processid
        where n.status = 0 AND ta.BUSSTATUS = '0')
        ) m
        where m.BUSTYPE = #{bustype}
        and m.SIGNUSERID = #{userId}
        ORDER BY m.LAUPTIME DESC
    </select>

    <select id="searchUnionTaskToday" resultMap="TaskListUnionMap">
        SELECT *
        FROM (
        (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , b.STATUS as currentNodeOrBizStatus
        from tsrevtasklist t, tsbizrevlist b
        where t.bizseqid = b.bizseq and t.busstatus = 0 and b.ISREMOVE = '0')
        UNION all
        (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME , n.currentNode as currentNodeOrBizStatus
        from TSTaskList ta
        LEFT JOIN TSTwfProcessNode n on ta.WorkflowID = n.processid
        LEFT JOIN TSTwfProcess p on ta.WorkflowID = p.processid
        where n.status = 0 AND ta.BUSSTATUS = '0')
        ) m
        where
        m.SIGNUSERID = #{userId}
        and LAUPTIME >= DATE_FORMAT(now(),'%Y-%m-%d')
        ORDER BY m.LAUPTIME DESC
    </select>

    <select id="countFinishToday" resultType="int">
        select (
		select count(*)
		from TSTaskList ts
		where exists ( SELECT 1 FROM tsopinion tso where HANDLERID = #{userId}
						and ts.WORKFLOWID = tso.WORKFLOWID
						and LAUPTIME >= DATE_FORMAT(now(),'%Y-%m-%d')
						)
		and ts.LAUPTIME >= DATE_FORMAT(now(),'%Y-%m-%d')
		) + (
		select count(*)
		from tsrevtasklist ts
		where exists ( SELECT 1 FROM tsopinion tso where HANDLERID = #{userId}
						and ts.WORKFLOWID = tso.WORKFLOWID
						and LAUPTIME >= DATE_FORMAT(now(),'%Y-%m-%d')
						)
		and ts.LAUPTIME >= DATE_FORMAT(now(),'%Y-%m-%d')
		) zs2 from dual
    </select>

    <select id="countFinishAll" resultType="int">
        select (
		select count(*)
		from TSTaskList ts
		where exists ( SELECT 1 FROM tsopinion tso where HANDLERID = #{userId}
						and ts.WORKFLOWID = tso.WORKFLOWID
						)
		) + (
		select count(*)
		from tsrevtasklist ts
		where exists ( SELECT 1 FROM tsopinion tso where HANDLERID = #{userId}
						and ts.WORKFLOWID = tso.WORKFLOWID
						)
		) zs2 from dual
    </select>

    <select id="countByBUSTYPE" resultType="int">
        select
		count(*)
        from TSTaskList ta
        where ta.BUSSTATUS = '0'
        and ta.SIGNUSERID = #{userId}
        and BUSTYPE = #{bustype}
    </select>

    <select id="countByBUSTYPEAndsourceType" resultType="int">
        select
		count(*)
        from TSTaskList ta, tsbizserillegal t2
        where ta.bizseqid = t2.bizseq
        and ta.BUSSTATUS = '0'
        and ta.SIGNUSERID = #{userId}
        and BUSTYPE = #{bustype}
        and t2.sourceType = #{sourceType}
    </select>

    <select id="countPD" resultType="int">
        select count(*) from tsrevtasklist where BUSSTATUS = '0' and SIGNUSERID = #{userId}
    </select>

    <select id="countByUserid" resultType="int">
        SELECT
		count(*)
        FROM (
        (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME
        from tsrevtasklist t, tsbizrevlist b
        where t.bizseqid = b.bizseq and t.busstatus = 0 and b.ISREMOVE = '0'
        and t.SIGNUSERID = #{userId}
        )
        UNION all
        (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME
        from TSTaskList ta
        where ta.BUSSTATUS = '0'
        and ta.SIGNUSERID = #{userId}
        )
        ) m
    </select>

    <select id="searchCheckList" resultMap="BaseResultMap">
        SELECT t.*,n.currentNode as currentNodeOrBizStatus, p.defname, bc.uscc
        FROM crgs.tstasklist t,
        crgs.tstwfprocessnode n,
        crgs.TSTwfProcess p,
        crgs.biz_credit bc
        WHERE t.workflowid = n.processid
        and t.workflowid = p.processid
        and t.bizseqid = bc.bizseq
		and  n.curruserid = #{aicUserId}
		AND n.status = '0'
    </select>

    <select id="selectTaskListByBizseqid" resultMap="BaseResultMap">
        SELECT * from TSTaskList where  BIZSEQID = #{bizseqid}
    </select>

    <select id="queryForComprehensive" resultMap="TaskListUnionMap">
        SELECT *
        from(
            (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME,ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME , n.currentNode as currentNodeOrBizStatus, SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode
            from TSTaskList ta
            LEFT JOIN TShwfProcessNode n on ta.WorkflowID = n.processid
            where ta.BUSSTATUS = 2 and REGEXP_LIKE(n.currentNode, '^end|over', 'i') = 1)
        union all
            (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , n.currentNode as currentNodeOrBizStatus, SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode
            from tsrevtasklist t
            LEFT JOIN TShwfProcessNode n on t.WorkflowID = n.processid
            where t.BUSSTATUS = 2 and REGEXP_LIKE(n.currentNode, '^end|over', 'i') = 1)
        union all
            (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , b.STATUS as currentNodeOrBizStatus, null as lastNode
            from tsrevtasklist t, tsbizrevlist b
            where t.bizseqid = b.bizseq and t.busstatus = 0 and b.ISREMOVE = '0')
        union all
            (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME, n.currentNode as currentNodeOrBizStatus,SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode
            from TSTaskList ta
            LEFT JOIN TSTwfProcessNode n on ta.WorkflowID = n.processid
            where n.status = 0)
        ) rs
		where 1=1
        <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
            and rs.BIZSEQID in (
            select BIZSEQ from tsbizabnrevoke where RevokeName like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizdissent where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizremlist where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizPunishLock where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizpunishunlock where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizserillegal where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizserillegaldiss where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizserillegalrem where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizrevlist where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from biz_corrections where entname like concat('%',#{customParamMap.searchName},'%')
            union all
            select BIZSEQ from tsbizserder where entname like concat('%',#{customParamMap.searchName},'%')
            )
        </if>
        <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
            and rs.ACCEPTNO = #{customParamMap.acceptno}
        </if>
        <if test="customParamMap.busType != null and customParamMap.busType != '' ">
            and rs.BUSTYPE = #{customParamMap.busType}
        </if>
        and rs.BUSSTATUS != '4'
        <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
        union all
        SELECT *
        from(
        (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME,ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME , n.currentNode as currentNodeOrBizStatus, SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode
        from TSTaskList ta
        LEFT JOIN TShwfProcessNode n on ta.WorkflowID = n.processid
        where ta.BUSSTATUS = 2 and REGEXP_LIKE(n.currentNode, '^end|over', 'i') = 1)
        union all
        (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , n.currentNode as currentNodeOrBizStatus, SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode
        from tsrevtasklist t
        LEFT JOIN TShwfProcessNode n on t.WorkflowID = n.processid
        where t.BUSSTATUS = 2 and REGEXP_LIKE(n.currentNode, '^end|over', 'i') = 1)
        union all
        (select DISTINCT t.TASKLISTID, t.WORKFLOWID, t.BIZSEQID, t.SIGNUSERID, t.SENDERORGID, t.SENDERUSERID, t.SENDTOORGID, t.ACCEPTNO, t.BUSNAME, t.SIGNTIME, t.BUSTYPE, t.BUSSTATUS, t.LINKNAME, t.AUDITDEPTTYPE, t.ISSIGNON, t.SIGNLOGINNAME, t.SENDERTIME, t.SENDERNAME, t.ACCEPTGROUPID, t.AREA_CODE, t.LAUPTIME , b.STATUS as currentNodeOrBizStatus, null as lastNode
        from tsrevtasklist t, tsbizrevlist b
        where t.bizseqid = b.bizseq and t.busstatus = 0 and b.ISREMOVE = '0')
        union all
        (select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID, ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS, ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME, ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME, n.currentNode as currentNodeOrBizStatus,SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) as lastNode
        from TSTaskList ta
        LEFT JOIN TSTwfProcessNode n on ta.WorkflowID = n.processid
        where n.status = 0)
        ) rs
        where 1=1
        and rs.BIZSEQID in (
        select BIZSEQ from tsbizspelist where entname like concat('%',#{customParamMap.searchName},'%')
        )
        <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
            and rs.ACCEPTNO = #{customParamMap.acceptno}
        </if>
        <if test="customParamMap.busType != null and customParamMap.busType != '' ">
            and rs.BUSTYPE = #{customParamMap.busType}
        </if>
        and rs.BUSSTATUS != '4'
        </if>
    </select>

    <select id="selectByBIZSEQIDANDSIGNUSERID" resultMap="BaseResultMap">
        select * from tstasklist where BIZSEQID = #{BIZSEQID} and SIGNUSERID = #{SIGNUSERID}
    </select>

    <select id="getBusQueryConditionBySearchName" resultType="java.lang.String">
        select * from (
            select distinct BIZSEQ from tsbizspelist t where entname = #{searchName}
        ) tmp
    </select>

    <select id="getBusQueryConditionByPripid" resultType="java.lang.String">
        select * from  (
           select BIZSEQ from tsbizspelist where PRIPID = #{pripid}
           union all
           select BIZSEQ from TSBizDissent where PRIPID = #{pripid}
           union all
           select BIZSEQ from tsbizremlist where PRIPID = #{pripid}
           union all
           select BIZSEQ from tsbizserillegal where PRIPID = #{pripid}
           union all
           select BIZSEQ from tsbizserillegaldiss where PRIPID = #{pripid}
           union all
           select BIZSEQ from tsbizserillegalrem where PRIPID = #{pripid}
           union all
           select BIZSEQ from TBizRemPunishment where PRIPID = #{pripid}
           union all
           select BIZSEQ from biz_corrections where PRIPID = #{pripid}
           union all
           select BIZSEQ from t_fraud_ent where PRIPID = #{pripid}
       ) tmp
    </select>

    <select id="listTaskAndToincludeabn"
            resultType="com.chinaweal.aiccs.aiccs.business.entity.dto.TaskListUnionDto">
        SELECT
        ta.TASKLISTID,
        ta.WORKFLOWID,
        ta.BIZSEQID,
        ta.SIGNUSERID,
        ta.SENDERORGID,
        ta.SENDERUSERID,
        ta.SENDTOORGID,
        ta.ACCEPTNO,
        ta.BUSNAME,
        ta.SIGNTIME,
        ta.BUSTYPE,
        ta.BUSSTATUS,
        ta.LINKNAME,
        ta.AUDITDEPTTYPE,
        ta.ISSIGNON,
        ta.SIGNLOGINNAME,
        ta.SENDERTIME,
        ta.SENDERNAME,
        ta.ACCEPTGROUPID,
        ta.AREA_CODE,
        ta.LAUPTIME,
        n.currentNode AS currentNodeOrBizStatus,
        SUBSTR(n.NODEFLOW, 1, INSTR(n.NODEFLOW, '_') - 1) AS lastNode,
        ta.CREATETIME,
        ta.ISDEADLINE,
        null as toIncludeId
        FROM TSTaskList ta
        LEFT JOIN TSTwfProcessNode n ON ta.WorkflowID = n.processid
        LEFT JOIN TSTwfProcess p ON ta.WorkflowID = p.processid
        <where>
            n.STATUS = 0
            AND ta.BUSSTATUS = '0'
            AND ta.BUSTYPE IN ('5', '6', '11', '13')
            AND ta.SIGNUSERID = #{customParamMap.userId}
            <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                and ta.BUSNAME like concat('%',#{customParamMap.searchName},'%')
            </if>
            <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
                and ta.ACCEPTNO = #{customParamMap.acceptno}
            </if>
            <if test="customParamMap.currentNodeOrBizStatus.toString() != null and customParamMap.currentNodeOrBizStatus.toString() != '' ">
                and ta.currentNodeOrBizStatus = #{customParamMap.currentNodeOrBizStatus}
            </if>
        </where>
        UNION ALL
        SELECT
        null as TASKLISTID,
        null as WORKFLOWID,
        null as BIZSEQID,
        null as SIGNUSERID,
        null as SENDERORGID,
        t.regEntryUserId as SENDERUSERID,
        null as SENDTOORGID,
        null as ACCEPTNO,
        e.ENTNAME as BUSNAME,
        t.insertTime as SIGNTIME,
        CASE WHEN e.enttype IN ( '9100', '9200' ) THEN '13' WHEN e.enttype = '9910' THEN '11' ELSE '5' END as BUSTYPE,
        null as BUSSTATUS,
        '经营异常名录' as LINKNAME,
        null as AUDITDEPTTYPE,
        null as ISSIGNON,
        null as SIGNLOGINNAME,
        t.insertTime as SENDERTIME,
        t.regEntryUserName as SENDERNAME,
        null as ACCEPTGROUPID,
        null as AREA_CODE,
        t.insertTime as LAUPTIME,
        'exptlistEnter' AS currentNodeOrBizStatus,
        null AS lastNode,
        t.insertTime as CREATETIME,
        null as ISDEADLINE,
        t.toIncludeId
        FROM toincludeabn t
        LEFT JOIN e_baseinfo e ON t.pripid = e.pripid
        LEFT JOIN gzaiccode g ON e.SUPORG = g.scountrycode
        <where>
            t.state = '0' and t.dataState = '0'
            <if test="customParamMap.acceptno != null and customParamMap.acceptno !=''">
                and e.UNISCID = #{customParamMap.acceptno}
            </if>
            <if test="customParamMap.uniscid != null and customParamMap.uniscid !=''">
                and e.UNISCID = #{customParamMap.uniscid}
            </if>
            <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                and e.entname = #{customParamMap.searchName}
            </if>
            <if test="customParamMap.regno != null and customParamMap.regno !=''">
                and e.regno = #{customParamMap.regno}
            </if>
            <if test="customParamMap.entname != null and customParamMap.entname !=''">
                and e.ENTNAME like concat('%', #{customParamMap.entName}, '%')
            </if>
            <choose>
                <when test="customParamMap.regOrg != null and customParamMap.regOrg !='' ">
                    and e.REGORG = #{customParamMap.regOrg}
                </when>
                <when test="customParamMap.acceptOrg != null and customParamMap.acceptOrg !='' ">
                    and e.ACCEPTREGORG = #{customParamMap.acceptOrg}
                </when>
                <when test="customParamMap.scountrycodes != null and customParamMap.scountrycodes.size() > 0">
                    and e.SUPORG in
                    <foreach collection="customParamMap.scountrycodes" item="AnnCheDept" open="(" separator="," close=")">
                        #{AnnCheDept}
                    </foreach>
                </when>
                <otherwise>
                    and e.SUPORG = '-1'
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="comprehensiveOptimizeQuery"
            resultType="com.chinaweal.aiccs.aiccs.business.entity.dto.TaskListUnionDto">
        select distinct ta.TASKLISTID,
        ta.WORKFLOWID,
        ta.BIZSEQID,
        ta.ACCEPTNO,
        ta.BUSNAME,
        ta.SIGNTIME,
        ta.signuserid,
        ta.BUSTYPE,
        ta.BUSSTATUS,
        ta.SENDERTIME,
        ta.SENDERNAME,
        ta.SIGNLOGINNAME
        from tstasklist ta
        <if test="customParamMap.orgNumber != null and customParamMap.orgNumber != ''">
            left join (
            select tu.userID, left(tog.orgNumber,6) as orgNumber
            from cxaicorg.t_users tu left join cxaicorg.t_orgunits tog on tu.orgUnitID = tog.orgUnitID
            ) org on ta.SIGNUSERID = org.userID
        </if>
        where ta.BUSSTATUS in ('-1', '0', '1', '2', '3', '5')
        <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
            and ta.ACCEPTNO = #{customParamMap.acceptno}
        </if>
        <if test="customParamMap.busType != null and customParamMap.busType != '' ">
            and ta.BUSTYPE = #{customParamMap.busType}
        </if>
        <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
            and (ta.BUSNAME = #{customParamMap.searchName}
            <if test="customParamMap.bizSeqList != null and customParamMap.bizSeqList.size() > 0">
                or ta.BIZSEQID in
                <foreach collection="customParamMap.bizSeqList" item="bizSeq" open="(" separator="," close=")">
                    #{bizSeq}
                </foreach>
            </if>
            )
        </if>
        <if test="customParamMap.pripid != null and customParamMap.pripid != '' ">
            <if test="customParamMap.bizSeqList != null and customParamMap.bizSeqList.size() > 0">
                and ta.BIZSEQID in
                <foreach collection="customParamMap.bizSeqList" item="bizSeq" open="(" separator="," close=")">
                    #{bizSeq}
                </foreach>
            </if>
        </if>
        <if test="customParamMap.sendertimestart != null and customParamMap.sendertimestart != ''">
            and date_format(ta.SENDERTIME, '%Y-%m-%d') &gt;= #{customParamMap.sendertimestart}
        </if>
        <if test="customParamMap.sendertimeend != null and customParamMap.sendertimeend != ''">
            and date_format(ta.SENDERTIME, '%Y-%m-%d') &lt;= #{customParamMap.sendertimeend}
        </if>
        <if test="customParamMap.orgNumber != null and customParamMap.orgNumber != ''">
            and org.orgNumber = #{customParamMap.orgNumber}
        </if>
    </select>

    <select id="queryForNoTswrit" resultMap="BaseResultMap">
        select * from aiccs.tstasklist t
        where 1=1
        and (BUSTYPE = '71' or bustype = '72' or bustype = '5')
        and date_format(LAUPTIME ,'%Y-%m-%d') = #{date}
        and not exists (select 1 from aiccs.tswrit tw where t.bizseqid = tw.bizseq)
        and not exists (select 1 from aiccs.tshwfprocessnode t2 where t.WORKFLOWID = t2.PROCESSID
            and currentnode = 'overExptlist')
        order by LAUPTIME asc
    </select>


    <select id="tolockDetail" resultMap="BaseResultMap">
        SELECT
            BIZSEQID,
            TASKLISTID,
            WORKFLOWID
        FROM
            tstasklist
                LEFT JOIN tsbizpunishlock ON tstasklist.BIZSEQID = tsbizpunishlock.BIZSEQ
                LEFT JOIN entlock ON tsbizpunishlock.lockId = entlock.ELID
        WHERE
            entlock.ENTNAME = #{entname} and entlock.locktime = #{locktime}
    </select>
    <select id="tounlockDetail" resultMap="BaseResultMap">
        SELECT
            BIZSEQID,
            TASKLISTID,
            WORKFLOWID
        FROM
            tstasklist
                LEFT JOIN tsbizpunishunlock ON tstasklist.BIZSEQID = tsbizpunishunlock.BIZSEQ
                LEFT JOIN entlock ON tsbizpunishunlock.ELID = entlock.ELID
        WHERE
            entlock.ENTNAME = #{entname} and entlock.UNLOCKTIME = #{unlocktime}
    </select>

    <select id="queryAbnList" resultType="com.chinaweal.aiccs.aiccs.abnormal.entity.TSAbnList">
        select * from tsabnlist t where 1=1
        <if test="customParamMap.sliceIds != null and customParamMap.sliceIds.size() > 0 ">
            and exists(select 1 from e_baseinfo e where e.pripid = t.pripid and e.sareacode_temp in
            <foreach collection="customParamMap.sliceIds" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="customParamMap.entName != null and customParamMap.entName != '' ">
            and entname like concat('%',#{customParamMap.entName},'%')
        </if>
        <if test="customParamMap.regno != null and customParamMap.regno != '' ">
            and regno like concat('%',#{customParamMap.regno},'%')
        </if>
        <if test="customParamMap.uniscid != null and customParamMap.uniscid != '' ">
            and uniscid = #{customParamMap.uniscid}
        </if>
        <if test="customParamMap.specause != null and customParamMap.specause != '' ">
            and specause = #{customParamMap.specause}
        </if>
        <if test="customParamMap.intoDecorg != null and customParamMap.intoDecorg != '' ">
            and intoDecorg like concat(#{customParamMap.intoDecorg}, '%')
        </if>
        <if test="customParamMap.redecorg != null and customParamMap.redecorg != '' ">
            and redecorg like concat(#{customParamMap.redecorg}, '%')
        </if>
        <if test="customParamMap.startIntoDate != null and customParamMap.startIntoDate != '' ">
            and abntime &gt;= #{customParamMap.startIntoDate}
        </if>
        <if test="customParamMap.endIntoDate != null and customParamMap.endIntoDate != '' ">
            and abntime &lt;= #{customParamMap.endIntoDate}
        </if>
        <if test="customParamMap.startRemDate != null and customParamMap.startRemDate != '' ">
            and remdate &gt;= #{customParamMap.startRemDate}
        </if>
        <if test="customParamMap.endRemDate != null and customParamMap.endRemDate != '' ">
            and remdate &lt;= #{customParamMap.endRemDate}
        </if>
        <!--主体类型筛选-->
        <!--企业-->
        <if test="customParamMap.entType != null and customParamMap.entType == '0'.toString()">
            and enttype not in
            ('8000','8100','8200','8300','9000','9100','9200','9910','99100','99101','99102','99103','9999')
        </if>
        <!--个体户-->
        <if test="customParamMap.entType != null and customParamMap.entType == '1'.toString()">
            and enttype in ('9910','99100','99101','99102','99103','9999')
        </if>
        <!--农合-->
        <if test="customParamMap.entType != null and customParamMap.entType == '2'.toString()">
            and enttype in ('9100','9200')
        </if>
        <!--异常状态筛选 0:移出 1:列入（包含列入和修改）查询全部包括移出、列入、修改-->
        <choose>
            <when test="customParamMap.operatestatus eq '0'.toString()">
                and operatestatus = '0'
            </when>
            <when test="customParamMap.operatestatus eq '1'.toString()">
                and operatestatus in ('1','3')
            </when>
            <otherwise>
                and operatestatus in ('0','1','3')
            </otherwise>
        </choose>
        <!--主体状态筛选（国标）-->
        <if test="customParamMap.regstate != null and customParamMap.regstate != '' ">
            and exists(select 1 from e_baseinfo e where e.pripid = t.pripid and e.regstate = #{customParamMap.regstate})
        </if>
    </select>

    <select id="exportAbnList" resultType="com.chinaweal.aiccs.aiccs.abnormal.entity.excel.AbnListExcel">
        select t.uniscid, t.entname, t.spereasons, t.abntime, t.intoDecorg, t.remdate, t.reDecorg,
        e.sareaname_temp as slice, e.dom as dom, e.lerep as lerep,
        CASE WHEN t.OPERATESTATUS = '0' THEN '移出'
        WHEN  t.OPERATESTATUS = '1' THEN '列入'
        WHEN  t.OPERATESTATUS = '2' THEN '撤销'
        WHEN  t.OPERATESTATUS = '3' THEN '修改'
        WHEN  t.OPERATESTATUS = '4' THEN '标记严重失信'
        ELSE '' END AS OPERATESTATUS
        from tsabnlist t left join e_baseinfo e on t.pripid = e.pripid where 1=1
        <if test="customParamMap.sliceIds != null and customParamMap.sliceIds.size() > 0 ">
            <!--and exists(select 1 from e_baseinfo e where e.pripid = t.pripid and e.sareacode_temp = #{customParamMap.sliceId})-->
            and e.sareacode_temp in
            <foreach collection="customParamMap.sliceIds" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="customParamMap.entName != null and customParamMap.entName != '' ">
            and t.entname like concat('%',#{customParamMap.entName},'%')
        </if>
        <if test="customParamMap.regno != null and customParamMap.regno != '' ">
            and t.regno like concat('%',#{customParamMap.regno},'%')
        </if>
        <if test="customParamMap.uniscid != null and customParamMap.uniscid != '' ">
            and t.uniscid = #{customParamMap.uniscid}
        </if>
        <if test="customParamMap.specause != null and customParamMap.specause != '' ">
            and t.specause = #{customParamMap.specause}
        </if>
        <if test="customParamMap.intoDecorg != null and customParamMap.intoDecorg != '' ">
            and t.intoDecorg like concat(#{customParamMap.intoDecorg}, '%')
        </if>
        <if test="customParamMap.redecorg != null and customParamMap.redecorg != '' ">
            and t.redecorg like concat(#{customParamMap.redecorg}, '%')
        </if>
        <if test="customParamMap.startIntoDate != null and customParamMap.startIntoDate != '' ">
            and t.abntime &gt;= #{customParamMap.startIntoDate}
        </if>
        <if test="customParamMap.endIntoDate != null and customParamMap.endIntoDate != '' ">
            and t.abntime &lt;= #{customParamMap.endIntoDate}
        </if>
        <if test="customParamMap.startRemDate != null and customParamMap.startRemDate != '' ">
            and t.remdate &gt;= #{customParamMap.startRemDate}
        </if>
        <if test="customParamMap.endRemDate != null and customParamMap.endRemDate != '' ">
            and t.remdate &lt;= #{customParamMap.endRemDate}
        </if>
        <!--主体类型筛选-->
        <!--企业-->
        <if test="customParamMap.entType != null and customParamMap.entType == '0'.toString()">
            and t.enttype not in
            ('8000','8100','8200','8300','9000','9100','9200','9910','99100','99101','99102','99103','9999')
        </if>
        <!--个体户-->
        <if test="customParamMap.entType != null and customParamMap.entType == '1'.toString()">
            and t.enttype in ('9910','99100','99101','99102','99103','9999')
        </if>
        <!--农合-->
        <if test="customParamMap.entType != null and customParamMap.entType == '2'.toString()">
            and t.enttype in ('9100','9200')
        </if>
        <!--异常状态筛选 0:移出 1:列入（包含列入和修改）查询全部包括移出、列入、修改-->
        <choose>
            <when test="customParamMap.operatestatus eq '0'.toString()">
                and t.operatestatus = '0'
            </when>
            <when test="customParamMap.operatestatus eq '1'.toString()">
                and t.operatestatus in ('1','3')
            </when>
            <otherwise>
                and t.operatestatus in ('0','1','3')
            </otherwise>
        </choose>
        <!--主体状态筛选（国标）-->
        <if test="customParamMap.regstate != null and customParamMap.regstate != '' ">
            and e.regstate = #{customParamMap.regstate}
        </if>
        LIMIT 50000
    </select>


    <select id="queryOldeBusness" resultType="com.chinaweal.aiccs.aiccs.business.entity.dto.TaskListUnionDto">
        select * from (
        -- 经营异常
        select
        SAPPROVECODE as bizseqid,
        SAPPROVEREALCODE as acceptno,
        SAPPROVENAME as busname,
        case
        when SSPTYPE = '001' and SENTTYPE = '001' then '5'
        when SSPTYPE = '002' and SENTTYPE = '001' then '7'
        when SSPTYPE = '003' and SENTTYPE = '001' then '6'
        when SSPTYPE = '001' and SENTTYPE = '002' then '11'
        when SSPTYPE = '002' and SENTTYPE = '002' then '12'
        when SSPTYPE = '003' and SENTTYPE = '002' then '18'
        when SSPTYPE = '001' and SENTTYPE = '003' then '13'
        when SSPTYPE = '002' and SENTTYPE = '003' then '14'
        when SSPTYPE = '003' and SENTTYPE = '003' then '19'
        else '' end as bustype,
        'endExptlist' as currentNodeOrBizStatus,
        tsu.SUNITNAME as xzqh,
        SAPPROVERNAME as signloginname,
        date_format(DTAPPROVEDATE, '%Y-%m-%d') as signtime,
        SAUDITORNAME as sendername,
        date_format(DTAUDITDATE, '%Y-%m-%d') as sendertime
        from tfdb.TB_SHS_APPROVE tsa
        left join tfdb.tb_sus_unit tsu on tsa.SAPPROVERUNITID = tsu.SUNITID
        where 1=1
        <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
            and SAPPROVEREALCODE = #{customParamMap.acceptno}
        </if>
        <if test="customParamMap.ssptype != null and customParamMap.ssptype != '' ">
            and ssptype = #{customParamMap.ssptype}
        </if>
        <if test="(customParamMap.pripids != null and customParamMap.pripids.size() > 0) or
            (customParamMap.searchName != null and customParamMap.searchName != '')">
            and exists (select 1 from tfdb.tb_shs_approve_entity tsae
            where tsae.SAPPROVECODE = tsa.SAPPROVECODE
            <if test="customParamMap.pripids != null and customParamMap.pripids.size() > 0 ">
                and SENTID in
                <foreach collection="customParamMap.pripids" item="pripid" open="(" separator="," close=")">
                    #{pripid}
                </foreach>
            </if>
            <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                and sentname like concat('%',#{customParamMap.searchName},'%')
            </if>
            )
        </if>
        <if test="customParamMap.xzqhCn != null and customParamMap.xzqhCn != '' ">
            and tsu.SUNITNAME = #{customParamMap.xzqhCn}
        </if>
        <if test="customParamMap.sendertimestart != null and customParamMap.sendertimestart != '' ">
            and date_format(DTAUDITDATE, '%Y-%m-%d') &gt; #{customParamMap.sendertimestart}
        </if>
        <if test="customParamMap.sendertimeend != null and customParamMap.sendertimeend != '' ">
            and date_format(DTAUDITDATE, '%Y-%m-%d') &lt; #{customParamMap.sendertimeend}
        </if>
        -- 严重违法
        union all
        select
        sapprovecode as bizseqid,
        null as acceptno,
        sapprovename as busname,
        case
        when ssptype = '001' then '15'
        when ssptype = '002' then '33'
        when ssptype = '003' then '16'
        else '' end as bustype,
        'endSerIllegal' as busstatus,
        tsu.SUNITNAME as xzqh,
        SAPPROVERNAME as signloginname,
        date_format(DTAPPROVEDATE, '%Y-%m-%d') as signtime,
        SAUDITORNAME as sendername,
        date_format(DTAUDITDATE, '%Y-%m-%d') as sendertime
        from tfdb.TB_YZWF_APPROVE tya
        left join tfdb.tb_sus_unit tsu on tya.regorg = tsu.SREGORGACODE
        where 1=1
        <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
            and false
        </if>
        <if test="customParamMap.uniscid != null and customParamMap.uniscid != '' ">
            and (suniscid = #{customParamMap.uniscid} or sregno = #{customParamMap.uniscid})
        </if>
        <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
            and sentname = #{customParamMap.searchName}
        </if>
        -- and ssptype = '001'
        <if test="customParamMap.sendertimestart != null and customParamMap.sendertimestart != '' ">
            and date_format(DTAUDITDATE, '%Y-%m-%d') &gt; #{customParamMap.sendertimestart}
        </if>
        <if test="customParamMap.sendertimeend != null and customParamMap.sendertimeend != '' ">
            and date_format(DTAUDITDATE, '%Y-%m-%d') &lt; #{customParamMap.sendertimeend}
        </if>
        union all
        select
        tyba.sapprovecode as bizseqid,
        null as acceptno,
        tyba.sapprovename as busname,
        --         case
        --         when tyba.ssptype = '01' then '55'
        --         when tyba.ssptype = '02' then '15'
        --         else '' end as bustype,
        '15' as bustype,
        'endSerIllegal' as busstatus,
        tsu.SUNITNAME as xzqh,
        tyba.SAPPROVERNAME as signloginname,
        date_format(tyba.DTAPPROVEDATE, '%Y-%m-%d') as signtime,
        tyba.SAUDITORNAME as sendername,
        date_format(tyba.DTAUDITDATE, '%Y-%m-%d') as sendertime
        from tfdb.tb_yzwf_batch_approve tyba
        left join tfdb.tb_sus_unit tsu on tyba.regorg = tsu.SREGORGACODE
        where 1=1
        <if test="customParamMap.acceptno != null and customParamMap.acceptno != '' ">
            and false
        </if>
        <if test="(customParamMap.pripids != null and customParamMap.pripids.size() > 0) or
            (customParamMap.searchName != null and customParamMap.searchName != '')">
            and exists (select 1 from tfdb.tb_yzwf_batch_list tybl
            where tyba.sapprovecode = tybl.sapprovecode
            <if test="customParamMap.pripids != null and customParamMap.pripids.size() > 0 ">
                and tybl.pripid in
                <foreach collection="customParamMap.pripids" item="pripid" open="(" separator="," close=")">
                    #{pripid}
                </foreach>
            </if>
            <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                and tybl.sentname like concat('%',#{customParamMap.searchName},'%')
            </if>
            )
        </if>
        <if test="customParamMap.sendertimestart != null and customParamMap.sendertimestart != '' ">
            and date_format(tyba.DTAUDITDATE, '%Y-%m-%d') &gt; #{customParamMap.sendertimestart}
        </if>
        <if test="customParamMap.sendertimeend != null and customParamMap.sendertimeend != '' ">
            and date_format(tyba.DTAUDITDATE, '%Y-%m-%d') &lt; #{customParamMap.sendertimeend}
        </if>
        ) rs where 1=1
        <if test="customParamMap.busType != null and customParamMap.busType != '' ">
            and rs.BUSTYPE = #{customParamMap.busType}
        </if>
    </select>

    <select id="repairTaskQuery" resultType="com.chinaweal.aiccs.aiccs.business.entity.dto.RepairTaskListDto">
        WITH MaxTaskList AS (
            SELECT
                tta.TASKLISTID,
                tta.BIZSEQID,
                ROW_NUMBER() OVER (PARTITION BY tta.BIZSEQID ORDER BY tta.TASKLISTID DESC) AS rn
            FROM tstasklist tta
            WHERE tta.BUSSTATUS = '0'
            <if test="customParamMap.busType == null or customParamMap.busType == '' ">
                and tta.BUSTYPE in ('33','7','12','14','27')
            </if>
            <if test="customParamMap.busType != null and customParamMap.busType != '' ">
                and tta.BUSTYPE = #{customParamMap.busType}
            </if>
            <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
                and tta.BUSNAME = #{customParamMap.searchName}
            </if>
            <if test="customParamMap.sendertimestart != null and customParamMap.sendertimestart != ''">
                and date_format(tta.SENDERTIME, '%Y-%m-%d') &gt;= #{customParamMap.sendertimestart}
            </if>
            <if test="customParamMap.sendertimeend != null and customParamMap.sendertimeend != ''">
                and date_format(tta.SENDERTIME, '%Y-%m-%d') &lt;= #{customParamMap.sendertimeend}
            </if>
            <if test="customParamMap.gzrStartDate != null and customParamMap.gzrStartDate != '' ">
                and tta.deadlineDate between #{customParamMap.gzrStartDate} and #{customParamMap.gzrEndDate}
            </if>
            <if test="customParamMap.gzrDate != null and customParamMap.gzrDate != '' ">
                and tta.deadlineDate &lt; #{customParamMap.gzrDate}
            </if>
        )
        SELECT
            ta.TASKLISTID,
            ta.WORKFLOWID,
            ta.BIZSEQID,
            ta.BUSNAME,
            ta.SIGNTIME,
            ta.BUSTYPE,
            case
                when ta.BUSTYPE = '33' then '严重违法失信名单信用修复'
                when ta.BUSTYPE = '7' then '企业经营异常名录信用修复'
                when ta.BUSTYPE = '12' then '个体户经营异常状态信用修复'
                when ta.BUSTYPE = '14' then '农专社经营异常名录信用修复'
                when ta.BUSTYPE = '27' then '行政处罚修复'
                else ''
            end as busTypeName,
            ta.BUSSTATUS,
            ta.SENDERTIME,
            ta.SENDERNAME,
            ta.SIGNLOGINNAME,
            ta.deadlineDate,
            ta.signuserid,
            ta.createTime,
            ta.area_code as areaCode
        FROM tstasklist ta
        JOIN MaxTaskList mt ON ta.TASKLISTID = mt.TASKLISTID AND mt.rn = 1
        <if test="customParamMap.orgNumber != null and customParamMap.orgNumber != ''">
            WHERE ta.area_code = #{customParamMap.orgNumber}
        </if>
    </select>

    <select id="repairTaskStatistic" resultType="com.chinaweal.aiccs.aiccs.business.entity.dto.RepairTaskStatisticDto">
        WITH MaxTaskList AS (
        SELECT
        tta.TASKLISTID,
        tta.BIZSEQID
        FROM tstasklist tta
        WHERE tta.BUSSTATUS = '0'
        <if test="customParamMap.busType == null or customParamMap.busType == '' ">
            and tta.BUSTYPE in ('33','7','12','14','27')
        </if>
        <if test="customParamMap.busType != null and customParamMap.busType != '' ">
            and tta.BUSTYPE = #{customParamMap.busType}
        </if>
        <if test="customParamMap.searchName != null and customParamMap.searchName != '' ">
            and tta.BUSNAME = #{customParamMap.searchName}
        </if>
        <if test="customParamMap.sendertimestart != null and customParamMap.sendertimestart != ''">
            and date_format(tta.SENDERTIME, '%Y-%m-%d') &gt;= #{customParamMap.sendertimestart}
        </if>
        <if test="customParamMap.sendertimeend != null and customParamMap.sendertimeend != ''">
            and date_format(tta.SENDERTIME, '%Y-%m-%d') &lt;= #{customParamMap.sendertimeend}
        </if>
        <if test="customParamMap.gzrStartDate != null and customParamMap.gzrStartDate != '' ">
            and tta.deadlineDate between #{customParamMap.gzrStartDate} and #{customParamMap.gzrEndDate}
        </if>
        <if test="customParamMap.gzrDate != null and customParamMap.gzrDate != '' ">
            and tta.deadlineDate &lt; #{customParamMap.gzrDate}
        </if>
        )
        SELECT
        ta.area_code as orgNumber,
        SUM(CASE WHEN ta.origin = '1' THEN 1 ELSE 0 END) as outerCount,  -- 外网数量
        SUM(CASE WHEN ta.origin != '1' OR ta.origin IS NULL THEN 1 ELSE 0 END) as innerCount,  -- 内网数量
        COUNT(*) as total  -- 总数
        FROM tstasklist ta
        JOIN MaxTaskList mt ON ta.TASKLISTID = mt.TASKLISTID
        <if test="customParamMap.areaCode != null and customParamMap.areaCode != ''">
            WHERE ta.area_code like #{customParamMap.areaCode}
        </if>
        <if test="customParamMap.orgNumber != null and customParamMap.orgNumber != ''">
            WHERE ta.area_code = #{customParamMap.orgNumber}
        </if>
        GROUP BY ta.area_code
        ORDER BY ta.area_code
    </select>

    <select id="indexPageRepairStatistic" resultType="java.util.Map">
        SELECT
        sum(case WHEN m.DEADLINEDATE is not null
        AND trunc(sysdate - m.DEADLINEDATE) &lt; -1
        THEN 1 ELSE 0 END) as "normalnum",      -- 正常：截止日期距离当前日期大于1天
        sum(case WHEN m.DEADLINEDATE is not null
        AND trunc(sysdate - m.DEADLINEDATE) BETWEEN -1 AND 0
        THEN 1 ELSE 0 END) as "aboutExpirenum", -- 即将超期：截止日期距离当前日期小于等于1天且未过期
        sum(case WHEN m.DEADLINEDATE is not null
        AND trunc(sysdate - m.DEADLINEDATE) > 0
        THEN 1 ELSE 0 END) as "expirednum"      -- 超期：截止日期已超过当前日期
        FROM (
        select ta.TASKLISTID, ta.WORKFLOWID, ta.BIZSEQID, ta.SIGNUSERID, ta.SENDERORGID, ta.SENDERUSERID,
        ta.SENDTOORGID, ta.ACCEPTNO, ta.BUSNAME, ta.SIGNTIME, ta.BUSTYPE, ta.BUSSTATUS,
        ta.LINKNAME, ta.AUDITDEPTTYPE, ta.ISSIGNON,ta.SIGNLOGINNAME, ta.SENDERTIME, ta.SENDERNAME,
        ta.ACCEPTGROUPID, ta.AREA_CODE, ta.LAUPTIME , n.currentNode as currentNodeOrBizStatus,
        ta.DEADLINEDATE, ta.CREATETIME, ta.ISDEADLINE
        from TSTaskList ta
        LEFT JOIN TSTwfProcessNode n on ta.WorkflowID = n.processid
        where n.status = 0 and ta.BUSSTATUS in ('0','-1')
        ) m
        where m.BUSTYPE in ('33','7','12','14','27')
        and ( m.SIGNUSERID = #{customParamMap.userId}
        or (m.area_code = #{customParamMap.areaCode} AND m.currentNodeOrBizStatus in ('exptlistEnter','startAccept','serIllegalEnter') AND m.SIGNUSERID IS null)
        or exists (select 1 from tsrefgroupanduser tg where tg.GROUPID = m.ACCEPTGROUPID and tg.USERID = #{customParamMap.userId}) )
    </select>

    <select id="tsAbnEntTypeStatistic" resultType="java.util.Map">
        SELECT
            SUM(CASE WHEN enttype = '9910' OR enttype = '9500' THEN 1 ELSE 0 END) AS "individualNum",  -- 个体户数量
            SUM(CASE WHEN enttype = '9100' OR enttype = '9200' THEN 1 ELSE 0 END) AS "coopNum",      -- 农专数量
            SUM(CASE WHEN enttype != '9910' AND enttype != '9500' AND enttype != '9100' AND enttype != '9200' THEN 1 ELSE 0 END) AS "enterpriseNum"  -- 企业数量
        FROM tsabnlist
    </select>

    <select id="tsAbnEntLocStatistic" resultType="com.chinaweal.aiccs.aiccs.business.entity.dto.AbnEntLocStatistic">
        SELECT DECORG as orgNumber,count(*) as abnNum FROM AICCS.tsabnlist
        WHERE 1=1
        <if test="customParamMap.orgNumber != null and customParamMap.orgNumber != ''">
            AND DECORG LIKE #{customParamMap.orgNumber}
        </if>
        <if test="customParamMap.orgList != null">
            AND DECORG IN
            <foreach collection="customParamMap.orgList" item="decorg" open="(" separator="," close=")">
                #{decorg}
            </foreach>
        </if>
        GROUP BY DECORG
    </select>

</mapper>
