<?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.auditing.mapper.TSOpinionMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.chinaweal.aiccs.aiccs.auditing.entity.TSOpinion">
        <id column="OPINIONID" property="opinionid" />
        <result column="WORKFLOWID" property="workflowid" />
        <result column="OPINIONTYPE" property="opiniontype" />
        <result column="HANDLER" property="handler" />
        <result column="handle_dept" property="handleDept" />
        <result column="handle_org" property="handleOrg" />
        <result column="HANDLERID" property="handlerid" />
        <result column="OPINIONCONTENT" property="opinioncontent" />
        <result column="HANDLEDATE" property="handledate" />
        <result column="TRIALDATE" property="trialdate" />
        <result column="ISAGREE" property="isagree" />
        <result column="AREA_CODE" property="areaCode" />
        <result column="LAUPTIME" property="lauptime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        OPINIONID, WORKFLOWID, OPINIONTYPE, HANDLER, HANDLERID, OPINIONCONTENT, HANDLEDATE, TRIALDATE, ISAGREE, AREA_CODE, LAUPTIME
    </sql>

    <select id="selectOpinionWithOrg" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>, dp.orgUnitName as handle_dept, og.orgUnitName as handle_org
        from aiccs.tsopinion op left join cxaicorg.t_users u on op.handlerid = u.userid
            left join cxaicorg.t_orgunits dp on u.orgDeptID = dp.orgUnitID
            left join cxaicorg.t_orgunits og on u.orgUnitID = og.orgUnitID
        where op.WORKFLOWID = #{workflowId}
        order by op.LAUPTIME,  op.OPINIONTYPE asc
    </select>

    <select id="getOpinionByOne" resultMap="BaseResultMap">
        select OPINIONCONTENT
        from aiccs.tsopinion op
        where op.WORKFLOWID = #{workflowId}
        order by op.LAUPTIME desc limit 1
    </select>

    <select id="queryByWorkflowId" parameterType="String" resultMap="BaseResultMap">
        select * from tsopinion t where WORKFLOWID = #{workflowId} order by HANDLEDATE desc
    </select>

    <select id="getRecoverListInbiz" resultMap="BaseResultMap">
        SELECT t.OPINIONID, t.WORKFLOWID, t.OPINIONTYPE, t.HANDLER, t.HANDLERID, t.OPINIONCONTENT, t.HANDLEDATE, t.TRIALDATE, t.ISAGREE, t.AREA_CODE, t.LAUPTIME
        FROM tsopinion t
        LEFT JOIN tstwfprocess s on t.WORKFLOWID = s.PROCESSID
        WHERE t.HANDLEDATE > '2021-11-19 20:23:00' AND s.DEFNAME = 'exptlistWorkFlow'
        GROUP BY t.WORKFLOWID
    </select>

    <select id="getRecoverListReturn" resultMap="BaseResultMap">
        SELECT DISTINCT WORKFLOWID
        FROM tsopinion t
        WHERE t.HANDLEDATE > '2021-11-19 20:23:00' and t.OPINIONTYPE = '9'
    </select>

    <select id="getRecoverListFinish" resultMap="BaseResultMap">
        SELECT t.OPINIONID, t.WORKFLOWID, t.OPINIONTYPE, t.HANDLER, t.HANDLERID, t.OPINIONCONTENT, t.HANDLEDATE, t.TRIALDATE, t.ISAGREE, t.AREA_CODE, t.LAUPTIME
        FROM tsopinion t
        INNER JOIN tshwfprocess s on t.WORKFLOWID = s.PROCESSID
        WHERE t.HANDLEDATE &gt; '2021-11-19 20:23:00' and t.HANDLEDATE &lt; '2021-11-25 00:00:00' AND s.DEFNAME = 'exptlistWorkFlow'
        and t.OPINIONTYPE = '2' and t.ISAGREE = '1'
        GROUP BY t.WORKFLOWID
    </select>


    <select id="listAlreadyYb" resultType="java.lang.String">
        SELECT DISTINCT WORKFLOWID
        FROM tsopinion t
                 INNER JOIN tshwfprocess s on t.WORKFLOWID = s.PROCESSID
        WHERE t.HANDLEDATE > '2021-11-19 20:23:00' AND s.DEFNAME = 'exptlistWorkFlow'
        GROUP BY t.WORKFLOWID
    </select>

    <select id="queryOldOpninion" resultMap="BaseResultMap">
        select
        tsu.SUNITNAME as handleOrg,
        tsaf.SUSERNAME as handler,
        tsaf2.SMEMO as opinioncontent,
        tsaf2.DTFLOWTIME as handledate,
        tsaf2.NNUMBER as opiniontype
        from tfdb.TB_SHS_APPROVE_FLOW tsaf
        left join tfdb.TB_SHS_APPROVE_FLOW tsaf2 on tsaf.SAPPROVECODE = tsaf2.SAPPROVECODE and tsaf2.NNUMBER = tsaf.NNUMBER + 1
        left join tfdb.tb_sus_unit tsu on tsaf.SUNITID = tsu.SUNITID
        where tsaf.SAPPROVECODE = #{SAPPROVECODE}
        and tsaf.NNUMBER in ('0','1','2')
        order by tsaf.NNUMBER asc
    </select>

    <select id="queryOldSerillegaOpninion" resultMap="BaseResultMap">
        select
        tsu.SUNITNAME as handleOrg,
        tsaf.SUSERNAME as handler,
        tsaf2.SMEMO as opinioncontent,
        tsaf2.DTFLOWTIME as handledate,
        tsaf.NNUMBER as opiniontype
        from tfdb.TB_YZWF_APPROVE_FLOW tsaf
        left join tfdb.TB_YZWF_APPROVE_FLOW tsaf2 on tsaf.SAPPROVECODE = tsaf2.SAPPROVECODE and tsaf2.NNUMBER = tsaf.NNUMBER + 1
        left join tfdb.tb_sus_unit tsu on tsaf.SUNITID = tsu.SUNITID
        where tsaf.SAPPROVECODE = #{SAPPROVECODE}
        and tsaf.NNUMBER in ('0','1','2')
        order by tsaf.NNUMBER asc
    </select>
</mapper>
