<?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.tzld.piaoquan.growth.common.dao.mapper.ext.WeComUserMapperExt">

    <select id="countGroupSendUserByDate" resultType="java.lang.Integer">
        select count(1)
        from we_com_user wcu
        join we_com_send_msg_result wcsmr on wcsmr.user_id = wcu.id
        join we_com_special_send_message wcssm on wcssm.user_id = wcsmr.user_id and wcssm.staff_id = wcsmr.staff_id
        join we_com_staff wcs on wcs.id = wcssm.staff_id
        where wcu.group_msg_disabled > 0
        and wcssm.is_send = 1
        and wcsmr.create_time between #{start} and #{end}
        and wcssm.pre_send_date = #{preSendDate}
        <if test="carrierIds != null and carrierIds.size() > 0">
            and wcs.carrier_id in
            <foreach collection="carrierIds" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getGroupSendUserByDate" resultType="com.tzld.piaoquan.growth.common.model.bo.WeComUserBo">
        select wcu.corp_id as corpId, wcu.external_user_id as externalUserId, wcu.union_id as unionId,
        wcu.external_user_id_3rd_party as externalUserId3rdParty, wcu.type as type, wcu.name as name,
        wcu.avatar as avatar, wcu.gender as gender, wcs.carrier_id as carrierId, wcsmr.status as status,
        UNIX_TIMESTAMP(wcsmr.send_time) * 1000 as sendTime, wcsmr.id as sendMsgResultId, wcspsm.status as strategyStatus,
        wcspsm.content as content, wcspsm.gender as sendGender, wct.tag_name
        from we_com_user wcu
        join we_com_send_msg_result wcsmr on wcsmr.user_id = wcu.id
        join we_com_special_send_message wcssm on wcssm.user_id = wcsmr.user_id and wcssm.staff_id = wcsmr.staff_id
        join we_com_staff wcs on wcs.id = wcssm.staff_id
        join we_com_special_pre_send_message wcspsm on wcssm.group = wcspsm.id
        join we_com_tag wct on wcspsm.tag_id = wct.id
        where wcssm.is_send = 1
        and wcsmr.create_time between #{start} and #{end}
        and wcssm.pre_send_date = #{preSendDate}
        <if test="carrierIds != null and carrierIds.size() > 0">
            and wcs.carrier_id in
            <foreach collection="carrierIds" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="pageSize != null and pageSize > 0">
            limit #{offset}, #{pageSize}
        </if>
    </select>
</mapper>