<?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.cftech.mp.fans.dao.MpFanssMapper">
    <resultMap id="BaseResultMap" type="com.cftech.mp.fans.model.MpFanssEntity">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="openid" property="openid" jdbcType="VARCHAR"/>
        <result column="nickname" property="nickname" jdbcType="VARCHAR"/>
        <result column="sex" property="sex" />
        <result column="city" property="city" jdbcType="VARCHAR"/>
        <result column="province" property="province" jdbcType="VARCHAR"/>
        <result column="country" property="country" jdbcType="VARCHAR"/>
        <result column="headimgurl" property="headimgurl" jdbcType="VARCHAR"/>
        <result column="subscribetime" property="subscribetime"
                jdbcType="TIMESTAMP"/>
        <result column="createtime" property="createtime" jdbcType="TIMESTAMP"/>
        <result column="status" property="status"/>
        <result column="source" property="source"/>
        <result column="recommender" property="recommender"/>
        <result column="store" property="store"/>
        <result column="description" property="description"/>
        <result column="canceltime" property="canceltime"/>
        <result column="tag" property="tag"/>
        <result column="taglist" property="taglist"/>
    </resultMap>

    <resultMap id="BaseResultMapStore" type="com.cftech.mp.fans.model.MpFanssEntity">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="openid" property="openid" jdbcType="VARCHAR"/>
        <result column="nickname" property="nickname" jdbcType="VARCHAR"/>
        <result column="sex" property="sex" jdbcType="CHAR"/>
        <result column="city" property="city" jdbcType="VARCHAR"/>
        <result column="province" property="province" jdbcType="VARCHAR"/>
        <result column="country" property="country" jdbcType="VARCHAR"/>
        <result column="headimgurl" property="headimgurl" jdbcType="VARCHAR"/>
        <result column="subscribetime" property="subscribetime"
                jdbcType="TIMESTAMP"/>
        <result column="createtime" property="createtime" jdbcType="TIMESTAMP"/>
        <result column="status" property="status"/>
        <result column="source" property="source"/>
        <result column="recommender" property="recommender"/>
        <result column="store" property="store"/>
        <result column="storeName" property="storeName"/>
        <result column="description" property="description"/>
        <result column="canceltime" property="canceltime"/>
        <result column="tags" property="tags"/>
        <result column="taglist" property="taglist"/>
    </resultMap>


    <sql id="Base_Column_List_Store">
		t.id, t.openid,t.canceltime,
		CONVERT(AES_DECRYPT(t.nickname,'aideakey') USING UTF8) nickname,t.sex, CONVERT(AES_DECRYPT(t.city,'aideakey') USING UTF8) city,CONVERT(AES_DECRYPT(t.province,'aideakey') USING UTF8) province,t.country,t.headimgurl,t.createtime,t.subscribetime,
		t.delflag,t.status,t.source
	</sql>

    <sql id="Base_Column_List">
		id, openid, canceltime, CONVERT(AES_DECRYPT(nickname,'aideakey') USING UTF8) nickname, sex,CONVERT(AES_DECRYPT(city,'aideakey') USING UTF8) city,CONVERT(AES_DECRYPT(province,'aideakey') USING UTF8) province,country,country,headimgurl,createtime,subscribetime,delflag,status,source,recommender,store,taglist
	</sql>


    <sql id="sqlWhere">
        <if test="conds!=null">
            <trim prefix="WHERE" prefixOverrides="AND|OR">
                <foreach collection="conds.conds" index="index" item="cond">
                    ${cond.linkType}
                    <if test="cond.condType == 'EQUAL'">${cond.param} = #{cond.value}</if>
                    <if test="cond.condType == 'NOTEQUAL'">${cond.param} &lt;&gt; #{cond.value}</if>
                    <if test="cond.condType == 'GREATEQUAL'">${cond.param} &gt;= #{cond.value}</if>
                    <if test="cond.condType == 'GREATTHAN'">${cond.param} &gt; #{cond.value}</if>
                    <if test="cond.condType == 'LESSEQUAL'">${cond.param} &lt;= #{cond.value}</if>
                    <if test="cond.condType == 'LESSTHAN'">${cond.param} &lt; #{cond.value}</if>
                    <if test="cond.condType == 'BETWEEN'">${cond.param} BETWEEN #{cond.startValue} AND
                        #{cond.endValue}
                    </if>
                    <if test="cond.condType == 'ISNULL'">${cond.param} IS NULL</if>
                    <if test="cond.condType == 'NOTNULL'">${cond.param} IS NOT NULL</if>
                    <if test="cond.condType == 'LIKE'">${cond.param} LIKE #{cond.value}</if>
                    <if test="cond.condType == 'IN'">
                        ${cond.param} IN
                        <foreach item="item" index="index" collection="cond.value"
                                 open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </if>
                </foreach>
            </trim>
        </if>
    </sql>


    <!-- 插入用户服务号 -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.cftech.mp.fans.model.MpFanssEntity">
		insert into wx_mp_fanss ( openid, nickname,
		sex,city,province,country,delflag,headimgurl,
		subscribetime,createtime,timestamp,updatetime,mpaccountid,userid,taglist,status,recommender,store,source,description,unionid)
		values (#{openid,jdbcType=VARCHAR},
		AES_ENCRYPT( #{nickname,jdbcType=VARBINARY},'aideakey'),
		#{sex,jdbcType=VARCHAR},
		AES_ENCRYPT(#{city,jdbcType=VARBINARY},'aideakey'),
		AES_ENCRYPT( #{province,jdbcType=VARBINARY},'aideakey'),
		#{country,jdbcType=VARCHAR},
		#{delflag,jdbcType=INTEGER},
		#{headimgurl,jdbcType=VARCHAR},
		#{subscribetime,jdbcType=TIMESTAMP},
		#{createtime,jdbcType=TIMESTAMP},
		#{timestamp,jdbcType=BIGINT},
		#{updatetime,jdbcType=TIMESTAMP},
		#{mpaccountid,jdbcType=BIGINT},
		#{userid,jdbcType=BIGINT},
		#{taglist,jdbcType=VARCHAR},
		#{status,jdbcType=VARCHAR},
		#{recommender,jdbcType=VARCHAR},
		#{store,jdbcType=VARCHAR},
		#{source,jdbcType=VARCHAR},
		#{description,jdbcType=VARCHAR},
		#{unionid,jdbcType=VARCHAR}
		)
	</insert>


    <insert id="insertFannsGroupMapping">
        INSERT INTO wx_mp_fansgroup(fansid, tagid)
        <foreach collection="fansgroups" item="fansgroups" separator=" union all ">
            SELECT #{fansgroups.fansid}, #{fansgroups.tagid}
            FROM dual
        </foreach>
    </insert>


    <select id="getMpFans" resultMap="BaseResultMap"
            parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List"/>
        from wx_mp_fanss where mpaccountid = #{userid,jdbcType=BIGINT} and delflag=0 order by
        delflag asc,createtime desc
    </select>

    <select id="selectFannscounts" resultType="Long">
        select
           count(1)
         from wx_mp_fanss where mpaccountid = #{userid,jdbcType=BIGINT} and delflag=0
    </select>


    <!-- 分页条件查询 -->
    <select id="getMpFansAndPage" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from wx_mp_fanss where mpaccountid = #{userid,jdbcType=BIGINT} and delflag=0 order by
        delflag asc,createtime desc
    </select>


    <select id="getDetail" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List"/>
        from wx_mp_fanss where id = #{id,jdbcType=BIGINT} and delflag=0
    </select>


    <!-- 更新账户信息 -->
    <update id="updateByPrimaryKey" parameterType="com.cftech.mp.fans.model.MpFanssEntity">
        update wx_mp_fanss
        <set>

            <if test="encodingaeskey != null">
                encodingaeskey = #{encodingaeskey,jdbcType=VARCHAR},
            </if>
            <if test="token != null">
                token = #{token,jdbcType=CHAR},
            </if>
            <if test="description != null">
                description = #{description,jdbcType=VARCHAR},
            </if>

            <if test="appid != null">
                appid = #{appid,jdbcType=VARCHAR},
            </if>

            <if test="secret != null">
                secret = #{secret,jdbcType=VARCHAR},
            </if>
            <if test="accesstoken != null">
                accesstoken = #{accesstoken,jdbcType=VARCHAR},
            </if>
            <if test="taglist != null">
                taglist = #{taglist},
            </if>
            updatetime = #{updatetime,jdbcType=TIMESTAMP}

        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <!-- 逻辑删除账户 -->
    <update id="deleteByPrimaryKey" parameterType="com.cftech.mp.fans.model.MpFanssEntity">
        update wx_mp_fanss
        <set>
            delflag = 1,
            updatetime = now()
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <!-- 逻辑删除账户 -->
    <update id="deleteByOpenId" parameterType="java.lang.String">
        update wx_mp_fanss
        <set>
            delflag = 1,
            updatetime = now(),
            canceltime = now()
        </set>
        where openid = #{id} and delflag = 0
    </update>

    <!-- 根据openid获取用户详情 -->
    <select id="getWxUser" parameterType="java.lang.String" resultType="com.cftech.mp.fans.model.MpFanssEntity">
        select  <include refid="Base_Column_List"/> from wx_mp_fanss
        <where>
            openid=#{openid,jdbcType=VARCHAR} and delflag=0
        </where>
    </select>

    <!-- 根据公众号id获取公众号下面的所有关注者ID -->
    <select id="getAllWxUser" parameterType="java.lang.String" resultType="String">
        select openid from wx_mp_fanss
        <where>
            mpaccountid=#{mpaccountid,jdbcType=VARCHAR} and delflag=0
        </where>
    </select>

    <!--根据openid更新信息 -->
    <update id="updateByOpenId" parameterType="com.cftech.mp.fans.model.MpFanssEntity">
        update wx_mp_fanss
        <set>
            <if test="id != null">
                id = #{id},
            </if>
            <if test="nickname != null">
                nickname = AES_ENCRYPT(#{nickname},'aideakey'),
            </if>
            <if test="sex != null">
                sex = #{sex},
            </if>
            <if test="city != null">
                city = AES_ENCRYPT(#{city},'aideakey'),
            </if>
            <if test="province != null">
                province = AES_ENCRYPT(#{province},'aideakey'),
            </if>
            <if test="userid != null">
                userid = #{userid},
            </if>
            <if test="createtime != null">
                createtime = #{createtime},
            </if>
            <if test="updatetime != null">
                updatetime = #{updatetime},
            </if>
            <if test="timestamp != null">
                timestamp = #{timestamp},
            </if>
            <if test="description != null">
                description = #{description},
            </if>
            <if test="delflag != null">
                delflag = #{delflag},
            </if>
            <if test="country != null">
                country = #{country},
            </if>
            <if test="headimgurl != null">
                headimgurl = #{headimgurl},
            </if>
            <if test="subscribetime != null">
                subscribetime = #{subscribetime},
            </if>
            <if test="unionid != null">
                unionid = #{unionid},
            </if>
            <if test="mpaccountid != null">
                mpaccountid = #{mpaccountid},
            </if>
            <if test="taglist != null">
                taglist = #{taglist},
            </if>
            <if test="recommender != null">
                recommender = #{recommender},
            </if>
            <if test="store != null">
                store = #{store},
            </if>
            <if test="source != null">
                source = #{source},
            </if>
            <if test="status != null">
                status = #{status}
            </if>
        </set>
        where openid = #{openid} and delflag = 0
    </update>
    <update id="updateLabelByid" parameterType="java.util.Map">
        update wx_mp_fanss
        <set>
            <if test="taglist != null">
                taglist = #{taglist},
            </if>
        </set>
        where openid = #{openid} and delflag = 0
    </update>

    <select id="fetchSearchByPage" parameterType="java.util.Map"
            resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        ,(
        SELECT
        GROUP_CONCAT(NAME SEPARATOR ',')
        FROM
        wx_mp_fanssgroup
        WHERE
        FIND_IN_SET(groupid,SUBSTRING_INDEX(t.taglist,',',20))
        ) tags
        FROM wx_mp_fanss t
        <include refid="sqlWhere"/>
        <if test="areaname!=null">
            AND ( CONVERT ( AES_DECRYPT( t.city, 'aideakey' ) USING UTF8 ) like #{areaname}
             or CONVERT ( AES_DECRYPT( t.province, 'aideakey' ) USING UTF8 ) like #{areaname})
        </if>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_mp_fanss t
        <include refid="sqlWhere"/>
        <if test="areaname!=null">
            AND (t.city like #{areaname} or t.province like #{areaname})
        </if>
    </select>

    <select id="fannsByArea" parameterType="java.util.Map" resultType="String">
        select DISTINCT openid from wx_mp_fanss fanss
        <if test="storeIds!=null and storeIds!=''">
            LEFT JOIN wx_mp_member member ON member.open_id = fanss.openid
            LEFT JOIN wx_mp_member_offline offline ON offline.member_id = member.member_id
            LEFT JOIN t_dc_store store ON store.code = offline.storeid
        </if>
        where fanss.delflag=0 and fanss.mpaccountid=${accountsid}
        <if test="areaName!=null and areaName!=''">
            and ((find_in_set( CONVERT(AES_DECRYPT(city,'aideakey') USING UTF8) ,#{areaName})) or (find_in_set( CONVERT(AES_DECRYPT(province,'aideakey') USING UTF8),#{areaName})))
        </if>
        <if test="sex!=null and sex!=''">
            and fanss.sex=${sex}
        </if>
        <if test="tags!=null and tags!=''">
            and fanss.taglist in (${tags})
        </if>
        <if test="storeIds!=null and storeIds!=''">
            AND (
            store.id IN (${storeIds})
            OR (fanss.source = 1 and fanss.store IN (${storeIds})
            )
            )
        </if>
    </select>

    <!-- 导出粉丝Excel,需导出改粉丝关注时扫描的导购名字或门店名字 -->
    <select id="fetchSearchByPageStore" parameterType="java.util.Map"
            resultMap="BaseResultMapStore">
        SELECT
        <include refid="Base_Column_List_Store"/>
        ,(
        SELECT
        GROUP_CONCAT(NAME SEPARATOR ',')
        FROM
        wx_mp_fanssgroup
        WHERE
        FIND_IN_SET(groupid,SUBSTRING_INDEX(t.taglist,',',20))
        ) tags
        from wx_mp_fanss t

        <include refid="sqlWhere"/>
        <if test="areaname!=null">
            AND (t.city like #{areaname} or t.province like #{areaname})
        </if>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>
    <select id="findopenidById" parameterType="java.lang.Long"
            resultType="java.lang.String">
		SELECT openid from wx_mp_fanss t  where id = #{id} 
	</select>


    <update id="updateBatchLabelByTags" parameterType="java.util.Map">
        update wx_mp_fanss set taglist =
        case
        when FIND_IN_SET('${tagId}',taglist)=0
        then CONCAT(taglist,'${tagId},')
        ELSE
        taglist
        end
        where openid in
        <foreach item="item" index="index" collection="openIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>
</mapper>