<?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.cyksj.mapper.corp.CorpUserTagMapper">

    <select id="selectCorpUserDetailByCondition" resultType="com.cyksj.model.views.CorpUserView">
        select * from (select cu.id,
        cu.unionid,
        any_value(remark) as nickname,
        group_concat(ct.tag_name) as tagNames,
        group_concat(cf.name) as customers,
        any_value(ct.created_time) as createdTime
        from (select * from corp_user_tag where
        <choose>
            <when test="groupId!=null and groupId != ''">
                tag_id in (select tag_id from corp_tag where group_id = #{groupId})
            </when>
            <otherwise>
                tag_id = #{tagId}
            </otherwise>
        </choose>
        ) ct
        inner join corp_user_follow_relation cfr on cfr.id = ct.relation_id and cfr.wx_corp_id = #{wxCorpId}
        inner join corp_follow cf on cf.userid = cfr.follow_id and cf.wx_corp_id = #{wxCorpId}
        inner join corp_user_auth cua on cua.external_userid = cfr.corp_user_id and cua.wx_corp_id = #{wxCorpId}
        inner join corp_user cu on cu.id = cua.corp_user_id and cu.unionid != ''
        group by cu.id, cu.unionid) s order by s.createdTime desc
    </select>

    <select id="exportUserTagDetail" resultType="com.cyksj.model.excel.CorpUserTagExcelData">
        select * from (select cu.id,
        cu.unionid,
        group_concat(ct.tag_name) as tagNames,
        group_concat(cf.name) as customers,
        any_value(ct.created_time) as tagTime,
        u.id as userId,
        u.nickname,
        u.headimgurl
        from (select * from corp_user_tag where
        <choose>
            <when test="groupIds != null and groupIds.length > 0">
                tag_id in (select tag_id from corp_tag where group_id in
                <foreach collection="groupIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
                )
            </when>
            <when test="tagIds != null and tagIds.length > 0">
                tag_id in
                <foreach collection="tagIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </when>
        </choose>
        ) ct
        inner join corp_user_follow_relation cfr on cfr.id = ct.relation_id and cfr.wx_corp_id = #{wxCorpId}
        inner join corp_follow cf on cf.userid = cfr.follow_id and cf.wx_corp_id = #{wxCorpId}
        inner join corp_user_auth cua on cua.external_userid = cfr.corp_user_id and cua.wx_corp_id = #{wxCorpId}
        inner join corp_user cu on cu.id = cua.corp_user_id and cu.unionid != ''
        inner join user u on u.id = cu.user_id
        group by cu.id, cu.unionid)s order by s.tagTime desc, s.userId asc
    </select>

    <select id="getDelCorpUserNum" resultType="java.lang.Integer">
        select count(distinct if(cufr.status = 0,ca.corp_user_id,NULL)) as delNumOfUser
        from (select relation_id
        from `corp_user_tag` ct
        where tag_id = #{tagIdStr}
        <if test="startTime != null">
            and ct.created_time >= #{startTime}
        </if>
        <if test="endTime != null">
            and ct.created_time &lt; #{endTime}
        </if>) s
        left join corp_user_follow_relation cufr on cufr.id = s.relation_id
        left join corp_user_auth ca on ca.external_userid = cufr.corp_user_id
    </select>

    <select id="getTagIdByCorpUserId" resultType="java.lang.Long">
        select distinct ct.id
        from (select external_userid from corp_user_auth where corp_user_id = #{corpUserId} limit 1) ca
                 inner join corp_user_follow_relation cufr on cufr.corp_user_id = ca.external_userid
                 inner join corp_user_tag cut on cut.relation_id = cufr.id
                 inner join corp_tag ct on ct.tag_id = cut.tag_id
    </select>

    <select id="getUserIntentionPie" resultType="com.cyksj.model.views.UserIntentionPieView">
        select count(distinct ca.corp_user_id) as `count`, ccc.intention
        as intention
        from (select relation_id
        from `corp_user_tag` ct
        where tag_id = #{tagIdStr}) s
        left join corp_user_follow_relation cufr on cufr.id = s.relation_id
        left join corp_user_auth ca on ca.external_userid = cufr.corp_user_id
        left join corp_user cu on cu.id = ca.corp_user_id
        inner join corp_user_pay_intention ccc on ccc.union_id = cu.unionid
        <if test="thisDate != null">
            and date_format(cu.created_time,'%Y-%m-%d') = #{thisDate}
        </if>
        group by ccc.intention
    </select>

    <select id="getCorpUserIntentionView" resultType="com.cyksj.model.views.CorpUserIntentionView">
        select cu.user_id,cu.name as nickname,cu.created_time,ccc2.id as intentionId,ccc.intention
        from (select distinct cu.*
        from (select DISTINCT relation_id from corp_user_tag where tag_id = #{tagIdStr}) ct
        inner join corp_user_follow_relation cufr on cufr.id = ct.relation_id
        inner join corp_user_auth ca on ca.external_userid = cufr.corp_user_id
        inner join corp_user cu on cu.id = ca.corp_user_id
        <if test="thisDate != null">
            where date_format(cu.created_time,'%Y-%m-%d') = #{thisDate}
        </if>
        ) cu
        left join corp_user_pay_intention ccc on ccc.union_id = cu.unionid
        left join corp_customer_acquistion_callback ccc2 on ccc2.union_id = cu.unionid
        <where>
            <if test="userId != null">
                cu.user_id = #{userId}
            </if>
            <if test="userId != null">
                and cu.name like concat('%',#{userName},'%')
            </if>
            <if test="intention != null">
                and ccc.intention = #{intention}
            </if>
        </where>
    </select>

    <select id="selectIntentionUserTag" resultType="com.cyksj.model.entity.CorpUserTag">
        select ct.*, cufr.follow_id, cufr.corp_user_id as externalUserId
        from corp_user_tag ct
                 inner join corp_user_follow_relation cufr on cufr.id = ct.relation_id
        where ct.group_name = #{groupName}
        and cufr.corp_user_id = #{externalUserId}
    </select>
</mapper>