<?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" 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="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, t.nickname, t.sex, t.city,t.province,t.country,t.headimgurl,t.createtime,t.subscribetime,t.delflag,t.status,t.source
	</sql>
	 
	<sql id="Base_Column_List">
		id, openid,canceltime, nickname, sex, city,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}, #{nickname,jdbcType=VARCHAR},
		#{sex,jdbcType=VARCHAR},#{city,jdbcType=VARCHAR},
		#{province,jdbcType=VARCHAR},#{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 * 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 = #{nickname},
			</if>
			<if test="sex != null">
				sex = #{sex},
			</if>
			<if test="city != null">
				city = #{city},
			</if>
			<if test="province != null">
				province = #{province},
			</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 (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="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(city,#{areaName})) or (find_in_set(province,#{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>