<?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="cn.axzo.msg.center.dal.mapper.PendingMessageRecordMapper">

    <select id="queryByTemplateCodes" resultType="cn.axzo.msg.center.domain.entity.PendingMessageRecord">
        select * from pending_message_record where id in(SELECT
        id
        FROM
        (
        <if test="isNowDay">
            SELECT (@i:=@i+1) mi,aa.* FROM
            (SELECT
            old_undo.id,1 AS flag
            FROM
            (SELECT
            *
            FROM
            pending_message_record
            WHERE DATE(create_at) <![CDATA[<]]> DATE (#{selectDate})
            AND (state = ('HAS_BEEN_SENT'))
            AND is_delete = 0
            AND executor_person_id = #{personId}
            AND template_code IN <foreach collection="pendingCalendarCodeDTO.constructionCodes" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>
            ORDER BY create_at ASC) old_undo) aa,(SELECT (@i:=0))tt
            UNION
            ALL
        </if>
        SELECT (@i:=@i+1) mi,aa.* FROM
        (SELECT
        new_todo.id,2 AS flag
        FROM
        (SELECT
        *
        FROM
        pending_message_record
        WHERE DATE (create_at) = DATE (#{selectDate})
        AND state IN ('HAS_BEEN_SENT')
        AND is_delete = 0
        AND executor_person_id = #{personId}
        AND template_code IN <foreach collection="pendingCalendarCodeDTO.constructionCodes" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>
        ORDER BY create_at DESC) new_todo) aa,(SELECT (@i:=0))tt
        UNION
        ALL
        SELECT (@i:=@i+1) mi,aa.* FROM
        (SELECT
        new_todo_3.id,3 AS flag
        FROM
        (SELECT
        *
        FROM
        pending_message_record
        WHERE DATE (create_at) = DATE (#{selectDate})
        AND state IN ('HAS_BEEN_SENT')
        AND is_delete = 0
        AND executor_person_id = #{personId}
        AND template_code IN <foreach collection="pendingCalendarCodeDTO.punchInCodes" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>
        ORDER BY create_at DESC) new_todo_3) aa,(SELECT (@i:=0))tt
        UNION
        ALL
        SELECT (@i:=@i+1) mi,aa.* FROM
        (SELECT
        new_todo_4.id,4 AS flag
        FROM
        (SELECT
        *
        FROM
        pending_message_record
        WHERE DATE (create_at) = DATE (#{selectDate})
        AND state IN ('HAS_BEEN_SENT')
        AND is_delete = 0
        AND executor_person_id = #{personId}
        AND template_code IN <foreach collection="pendingCalendarCodeDTO.clockInCodes" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>
        ORDER BY create_at DESC) new_todo_4) aa,(SELECT (@i:=0))tt
        UNION
        ALL
        SELECT (@i:=@i+1) mi,aa.* FROM
        (SELECT
        all_new_do.id,5 AS flag
        FROM
        (SELECT
        *
        FROM
        pending_message_record
        WHERE (DATE(create_at) = DATE(#{selectDate}) <if test="isNowDay">
        OR (DATE(create_at) <![CDATA[<]]> DATE(#{selectDate}) AND state = ('COMPLETED') AND DATE(update_at) = DATE(#{selectDate})
        AND template_code IN <foreach collection="pendingCalendarCodeDTO.constructionCodes" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>)
    </if>)
        AND state = 'COMPLETED'
        AND is_delete = 0
        AND executor_person_id = #{personId}
        AND template_code IN <foreach collection="pendingCalendarCodeDTO.allCodes" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>
        ORDER BY create_at DESC) all_new_do) aa,(SELECT (@i:=0))tt
        ) ccc ORDER BY flag ASC,mi asc)
    </select>
</mapper>
