<?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.huiju.estateagents.redpack.mapper.TaRedPacketPersonMapper">

    <select id="getRedPacketRankingList" resultType="com.huiju.estateagents.redpack.entity.TaRedPacketPerson">
        SELECT
            t.*,
            t2.phone,
            t2.NAME as name,
            t2.nickname nickname,
            t2.avatarurl,
        (select count(*) from ta_red_packet_history th where th.target_miniapp_person = t.miniapp_person_id and th.activity_id = #{activityId} and th.org_id = #{orgId}) helpNum
        FROM
          ta_red_packet_person t
          LEFT JOIN ta_person t2 ON t.miniapp_person_id = t2.person_id
        WHERE
            t.activity_id = #{activityId}
            AND t.org_id = #{orgId}
            AND t.is_signed_up = TRUE
            <if test = "phone != null and phone != ''">
                AND t2.phone like concat('%',#{phone},'%')
            </if>
        ORDER BY
            helpNum DESC,
            t.create_date DESC
    </select>

    <select id="exportRedPacketRankingList" resultType="com.huiju.estateagents.excel.redPacket.RedPacketRankingListExport">
        SELECT
            t.votes,
            t.create_date,
            t2.phone,
            t2.NAME as name,
            t2.nickname nickname,
            t2.avatarurl,
            (select count(*) from ta_red_packet_history th where th.target_miniapp_person = t.miniapp_person_id and th.activity_id = #{activityId} and th.org_id = #{orgId}) votes
        FROM
            ta_red_packet_person t
        LEFT JOIN ta_person t2 ON t.miniapp_person_id = t2.person_id
        WHERE
        t.activity_id = #{activityId}
        AND t.org_id = #{orgId}
        AND t.is_signed_up = TRUE
        <if test = "phone != null and phone != ''">
            AND t2.phone like concat('%',#{phone},'%')
        </if>
        ORDER BY
        votes DESC,
        t.create_date DESC
    </select>

</mapper>
