<?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.yunzhi.marketing.mapper.HelpInitiateRecordMapper">

    <update id="updateHelpActivityProcessToEnd">
        update ta_help_initiate_record a
        left join ta_help_activity b on a.help_activity_id = b.help_activity_id
        set a.status = '2'
        where  (NOW() > b.end_date or b.activity_status = '2') and a.`status` = '1' and a.status != '0'
    </update>

    <select id="selectPageList" resultType="com.yunzhi.marketing.entity.HelpInitiateRecord">
        SELECT
        hi.*
        FROM
        ta_help_initiate_record as hi
        LEFT JOIN ta_help_activity ha ON hi.help_activity_id = ha.help_activity_id
       <where>
           hi.help_activity_id = #{helpActivityId}
        <if test="status == 'helpSucceed'">
            AND hi.verification !='null'
            AND hi.verification !=''
        </if>
       <if test="status == 'helpUnderway'">
           and (hi.verification is null OR hi.verification = '')
           AND hi.`status` = 1
       </if>

       <if test="status == 'helpUnfinished'">
           and (hi.verification is null
           OR hi.verification = '')
           AND hi.`status` =  2
       </if>
           <if test="tel != null and tel != ''">
               and  hi.phone like concat('%',#{tel},'%')
           </if>
       </where>
    </select>

    <select id="getHelpInitiateRecordSucceedExportList" resultType="com.yunzhi.marketing.excel.HelpInitiateRecordSucceed">
    SELECT
    b.name as name,
    b.create_date as createDate,
    b.phone as phone,
    b.succeed_date as succeedDate,
    if(b.verification_status = 0, '未核销', if(b.verification_status = 1, '已核销', '未知')) as verificationStatus
    from ta_help_initiate_record b
    where b.help_activity_id =  #{helpActivityId}
    AND b.verification !='null'
    AND b.verification !=''
    <if test="tel != null and tel != ''">
        and  b.phone like concat('%',#{tel},'%')
    </if>
     order by b.create_date desc
     limit #{pageCode}, #{pageSize}
    </select>

    <select id="getHelpProceedAndFinishExportList" resultType="com.yunzhi.marketing.excel.HelpProceedAndFinish">
    SELECT
    b.name as name,
    b.create_date as createDate,
    b.phone as phone
    from ta_help_initiate_record b
    where b.help_activity_id =  #{helpActivityId}
        <if test="condition == 'helpSucceed'">
            AND b.verification !='null'
            AND b.verification !=''
        </if>
        <if test="condition == 'helpUnderway'">
            and (b.verification is null OR b.verification = '')
            AND b.`status` = 1
        </if>

        <if test="condition == 'helpUnfinished'">
            and (b.verification is null
            OR b.verification = '')
            AND b.`status` =  2
        </if>
        <if test="tel != null and tel != ''">
            and  b.phone like concat('%',#{tel},'%')
        </if>
     order by b.create_date desc
     limit #{pageCode}, #{pageSize}
    </select>

    <select id="getInitiateRecordList" resultType="com.yunzhi.marketing.entity.HelpInitiateRecord">
      SELECT
        t.*,
        a.title,
        a.list_img as img,
        a.activity_status,
        a.end_date
    FROM
        ta_help_initiate_record t
        LEFT JOIN ta_help_activity a ON t.help_activity_id = a.help_activity_id
    WHERE
        t.org_id = #{orgId}
        AND t.person_id = #{personId}
    ORDER BY t.status, t.create_date desc
    </select>
</mapper>
