<?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.qmth.sop.business.mapper.TSJobRemindMapper">

    <select id="getFlowTaskRemindSmsInfo" resultType="com.qmth.sop.business.bean.result.FlowTaskSmsResult">
        select
            tbs.id as serviceId,
            tbs.name as serviceName,
            sc.name as customName,
            su.real_name as realName,
            su.mobile_number as mobileNumber,
            tfcfe.crm_no as crmNo,
            tfcf.`type`,
            tsjr.receive_user_id as userId
        from
            t_s_job_remind tsjr
                left join t_f_custom_flow_entity tfcfe on tfcfe.code = tsjr.code
                left join t_f_custom_flow tfcf on tfcf.id = tfcfe.t_f_custom_flow_id
                left join t_b_crm tbc on tbc.crm_no = tfcfe.crm_no
                left join t_b_service tbs on tbs.id = tbc.service_id
                left join sys_custom sc on sc.id = tbc.custom_id
                left join sys_user su on su.id = tsjr.receive_user_id
        where
            tsjr.id = #{id}
    </select>

    <select id="getJobRemindProcessHour" resultType="com.qmth.sop.business.bean.result.WorkTaskResult">
        select
            tsjr.code,
            24 - TIMESTAMPDIFF(hour,
                    DATE_FORMAT(FROM_UNIXTIME(max(tsjr.exec_time) / 1000), '%Y-%m-%d %H:%i:%s'),
                    NOW()) as ex
        from
            t_s_job_remind tsjr
                left join t_f_custom_flow_entity tfcfe on
                tsjr.code = tfcfe.code
        <where> 1 = 1
            and tsjr.remind_type = 'AFTER'
            <if test="userId != null and userId != ''">
                and tsjr.receive_user_id = #{userId}
            </if>
            <if test="sopNoSet != null and sopNoSet != '' and sopNoSet.size() > 0">
                and tsjr.code IN
                <foreach collection="sopNoSet" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        group by
            tsjr.code,
            tsjr.exec_time
    </select>
</mapper>
