<?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.paic.dbaudit.dao.prd.MapperScheduler">

    <select id="getNextTriggerSEQ" resultType="int">
        select DA_QRTZ_SCHEDULER_SEQ.NEXTVAL from dual
    </select>

    <select id="getAllTriggers" resultType="com.paic.dbaudit.bean.table.DA_QRTZ_SCHEDULER">
        select s.job_name,
               s.trigger_id,
               s.trigger_type,
               s.trigger_expression,
               s.created_by,
               s.created_date,
               s.updated_by,
               s.updated_date,
               ll.start_time as last_execute_time,
               ll.job_result
          from da_qrtz_scheduler s,
               (select l.trigger_id,
                       l.start_time,
                       l.job_result,
                       rank() over(partition by l.trigger_id order by l.start_time desc) as rank
                  from da_qrtz_log l) ll
         where s.trigger_id = ll.trigger_id(+)
           and (ll.rank = 1 or ll.rank is null)
      order by s.job_name desc, s.trigger_id asc
    </select>

    <select id="getTriggerByTriggerId" resultType="com.paic.dbaudit.bean.table.DA_QRTZ_SCHEDULER">
        select job_name,
               trigger_id,
               trigger_type,
               trigger_expression,
               created_by,
               created_date,
               updated_by,
               updated_date
          from da_qrtz_scheduler
         where trigger_id = #{trigger_ide, jdbcType = VARCHAR}
    </select>

    <select id="getTriggersByJobName" resultType="com.paic.dbaudit.bean.table.DA_QRTZ_SCHEDULER">
            select s.job_name,
               s.trigger_id,
               s.trigger_type,
               s.trigger_expression,
               s.created_by,
               s.created_date,
               s.updated_by,
               s.updated_date,
               ll.start_time as last_execute_time,
               ll.job_result,
               ll.seconds_costed
          from da_qrtz_scheduler s,
               (select l.trigger_id,
                       l.start_time,
                       l.seconds_costed,
                       l.job_result,
                       rank() over(partition by l.trigger_id order by l.start_time desc) as rank
                  from da_qrtz_log l
                 where job_name = #{job_name, jdbcType = VARCHAR}) ll
         where s.trigger_id = ll.trigger_id(+)
           and s.job_name = #{job_name, jdbcType = VARCHAR}
           and (ll.rank = 1 or ll.rank is null)
         order by s.created_date desc, s.trigger_type desc
    </select>

    <select id="getLogs" resultType="com.paic.dbaudit.bean.table.DA_QRTZ_LOG">
            select *
              from (select t.*, rownum rn
                      from (
                        select l.log_id,
                               l.job_name,
                               l.trigger_id,
                               s.trigger_expression,
                               l.start_time,
                               l.end_time,
                               l.seconds_costed,
                               l.job_result,
                               l.created_by,
                               l.created_date
                          from DA_QRTZ_LOG l, da_qrtz_scheduler s
                         where l.job_name = #{job_name, jdbcType = VARCHAR}
                           and l.trigger_id = s.trigger_id(+)
                      <if test="trigger_id != null and trigger_id != ''">
                           and l.trigger_id = #{trigger_id,jdbcType=VARCHAR}
                      </if>
                      <if test="job_result != null and job_result != ''">
                           and l.job_result = #{job_result,jdbcType=VARCHAR}
                      </if>
                      <if test="beginTime != null and beginTime != ''">
                          <![CDATA[
                           and start_time >= to_date(#{beginTime, jdbcType = VARCHAR}, 'yyyymmddHH24:MI:SS')
                          ]]> 
                      </if>
                      <if test="endTime != null and endTime != ''">
                          <![CDATA[
                           and start_time < to_date(#{endTime, jdbcType = VARCHAR}, 'yyyymmddHH24:MI:SS')
                          ]]> 
                      </if>
                         order by l.created_date desc
                      ) t
            <![CDATA[
                     where rownum <= #{endRow, jdbcType = VARCHAR})
             where rn >= #{startRow, jdbcType = NUMERIC}
            ]]> 
    </select>

    <select id="getLogsCount" resultType="int">
         select count(l.log_id)
           from DA_QRTZ_LOG l
          where l.job_name = #{job_name, jdbcType = VARCHAR}
       <if test="trigger_id != null and trigger_id != ''">
            and l.trigger_id = #{trigger_id,jdbcType=VARCHAR}
       </if>
       <if test="job_result != null and job_result != ''">
            and l.job_result = #{job_result,jdbcType=VARCHAR}
       </if>
       <if test="beginTime != null and beginTime != ''">
           <![CDATA[
            and start_time >= to_date(#{beginTime, jdbcType = VARCHAR}, 'yyyymmddHH24:MI:SS')
           ]]> 
       </if>
       <if test="endTime != null and endTime != ''">
           <![CDATA[
            and start_time < to_date(#{endTime, jdbcType = VARCHAR}, 'yyyymmddHH24:MI:SS')
           ]]> 
       </if>
    </select>

    <insert id="addTrigger" parameterType="com.paic.dbaudit.bean.table.DA_QRTZ_SCHEDULER">
        insert into da_qrtz_scheduler 
            (job_name, 
            trigger_id,
            trigger_type,
            trigger_expression,
            created_by, 
            created_date, 
            updated_by, 
            updated_date)
        values(
            #{job_name, jdbcType = VARCHAR},    
            #{trigger_id, jdbcType = VARCHAR}, 
            #{trigger_type, jdbcType = NUMERIC}, 
            #{trigger_expression, jdbcType = VARCHAR}, 
            #{created_by, jdbcType = VARCHAR}, 
            sysdate, 
            #{created_by, jdbcType = VARCHAR}, 
            sysdate 
        )
    </insert>

    <insert id="addLog" parameterType="com.paic.dbaudit.bean.table.DA_QRTZ_LOG">
        insert into DA_QRTZ_LOG 
            (
            job_name,
            trigger_id,
            start_time,
            end_time,
            seconds_costed,
            created_by, 
            created_date, 
            updated_by, 
            updated_date)
        values(
            #{job_name, jdbcType = VARCHAR},    
            #{trigger_id, jdbcType = VARCHAR},  
            #{start_time, jdbcType = TIMESTAMP}, 
            #{end_time, jdbcType = TIMESTAMP}, 
            #{seconds_costed, jdbcType = VARCHAR}, 
            #{created_by, jdbcType = VARCHAR}, 
            sysdate, 
            #{created_by, jdbcType = VARCHAR}, 
            sysdate 
        )
    </insert>

    <delete id="deleteTrigger">
        delete from da_qrtz_scheduler where trigger_id = #{trigger_id, jdbcType = NUMERIC}
    </delete>

</mapper>