<?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="MdeTrendsMapper">
    <resultMap type="com.wechat.model.dbEntity.MdeTrends" id="MdeTrends">
    	<id column="trend_id" property="trendId"/>
		<result column="user_code" property="userCode"/>
		<result column="trend_desc" property="trendDesc"/>
		<result column="trend_picurl" property="trendPicurl"/>
		<result column="show_flag" property="showFlag"/>
		<result column="like_who" property="likeWho"/>
		<result column="like_count" property="likeCount"/>
		<result column="comment_who" property="commentWho"/>
		<result column="comment_count" property="commentCount"/>
		<result column="version_no" property="versionNo"/>
		<result column="remarks" property="remarks"/>
		<result column="del_flag" property="delFlag"/>
		<result column="create_by" property="createBy"/>
		<result column="create_date" property="createDate"/>
		<result column="update_by" property="updateBy"/>
		<result column="update_date" property="updateDate"/>
		
		<result column="name" property="userName"/>
		<result column="head_portrait" property="userHeadPortrait"/>
		<result column="share_count" property="shareCount"/>
		<result column="HEAD_PORTRAIT" property="headImage"/>
		<result column="foreign_name" property="foreignName"/>
		<result column="identity_flag" property="identityFlag"/>
		
		<result column="origin_trend_id" property="originTrendId"/>
		<result column="origin_user_code" property="originUserCode"/>
		<result column="origin_trend" property="originTrend"/>
		<result column="link_info" property="linkInfo"/>
    </resultMap>
	<sql id="mdeTrendsColumns">
		a.trend_id AS "trendId",
		a.user_code AS "userCode",
		a.trend_desc AS "trendDesc",
		a.trend_picurl AS "trendPicurl",
		a.show_flag AS "showFlag",
		IFNULL(a.like_who,'') AS "likeWho",
		IFNULL(a.like_count,0) AS "likeCount",
		IFNULL(a.comment_who,'') AS "commentWho",
		IFNULL(a.comment_count,0) AS "commentCount",
		a.version_no AS "versionNo",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		a.create_by AS "createBy",
		a.create_date AS "createDate",
		a.update_by AS "updateBy",
		a.update_date AS "updateDate",
		a.link_info AS "linkInfo",
		a.origin_trend_id AS "originTrendId",
		a.origin_user_code AS "originUserCode",
		a.origin_trend AS "originTrend"
		
		,b.name as userName
		,b.head_portrait as userHeadPortrait
		,b.foreign_name as foreignName
		,b.identity_flag as identityFlag
		
		,c.head_portrait as originUserHeadPortrait 
	</sql>
	
	<sql id="mdeTrendsJoins">
		inner join mde_user b on b.user_code = a.user_code and b.del_flag=0 and b.ALLOW_FLAG = '0'
		left join mde_user c on c.user_code = a.origin_user_code and c.del_flag=0 and c.ALLOW_FLAG = '0'
	</sql>
    
	<select id="get" resultMap="MdeTrends">
		SELECT 
			<include refid="mdeTrendsColumns"/>
		FROM mde_trends a
		<include refid="mdeTrendsJoins"/>
		WHERE a.trend_id = #{trendsId}
	</select>
    
	<select id="getTrendsByIds" resultMap="MdeTrends">
		SELECT 
			<include refid="mdeTrendsColumns"/>
		FROM mde_trends a
		<include refid="mdeTrendsJoins"/>
		<where>
			a.DEL_FLAG = 0
			<if test="trendIds != null">
			and  a.trend_id in 
			<foreach collection="trendIds" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
			</if>
		</where>
	</select>
	
	<select id="myThumbUp" resultMap="MdeTrends">
		SELECT 
			<include refid="mdeTrendsColumns"/>
		FROM mde_trends a
		<include refid="mdeTrendsJoins"/>
		WHERE a.trend_id in 
		<foreach collection="likeList" index="index" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>
	
	
	<insert id="insert">
		INSERT INTO mde_trends(
			user_code,
			trend_desc,
			trend_picurl,
			show_flag,
			link_info,
			like_who,
			like_count,
			comment_who,
			comment_count,
			version_no,
			remarks,
			del_flag,
			create_by,
			create_date,
			update_by,
			update_date
		) VALUES (
			#{userCode},
			#{trendDesc},
			#{trendPicurl},
			#{showFlag},
			#{linkInfo},
			#{likeWho},
			#{likeCount},
			#{commentWho},
			#{commentCount},
			#{versionNo},
			#{remarks},
			#{delFlag},
			#{createBy},
			#{createDate},
			#{updateBy},
			#{updateDate}
		)
	</insert>
	<!-- 动态分享 -->
	<insert id="insertSharedTrends">
		INSERT INTO mde_trends(
			user_code, trend_desc, show_flag, 
			like_who, like_count, comment_who, comment_count, version_no, remarks, 
			del_flag, create_by, create_date, update_by, update_date,
			origin_trend_id, origin_user_code, origin_trend
		) 
		values( 
			#{userCode}, #{trendDesc}, #{showFlag}, null, 0, null, 0, 1, '', 
			#{delFlag}, #{userCode}, now(), #{userCode}, now(),
			#{originTrendId}, #{originUserCode}, #{originTrend}
		)
		<!-- from mde_trends where trend_id = #{originTrendId} -->
	</insert>
	
	<update id="update">
		UPDATE mde_trends SET 
			<if test="trendDesc !=null">trend_desc = #{trendDesc},</if>
			<if test="trendPicurl !=null">trend_picurl = #{trendPicurl},</if>
			<if test="showFlag !=null">show_flag = #{showFlag},</if>
			<if test="likeWho !=null">like_who = #{likeWho},</if>
			<if test="likeCount !=null">like_count = like_count + #{likeCount},</if>
			<if test="commentWho !=null">comment_who = CONCAT(#{commentWho},';',IFNULL(comment_who,'')) ,</if>
			<if test="commentCount !=null">comment_count = comment_count + #{commentCount},</if>
			<if test="remarks !=null">remarks = #{remarks},</if>
			<if test="updateBy !=null">update_by = #{updateBy},</if>
			<if test="updateDate !=null">update_date = #{updateDate},</if>
			version_no = version_no +1
		WHERE trend_id = #{trendId} and del_flag=0
			<if test="versionNo !=null">and version_no = #{versionNo}</if>
	</update>
	
	<update id="delete">
		UPDATE mde_trends SET 
			version_no = version_no +1,
			update_date = now(),
			<if test="updateBy !=null">update_by = #{updateBy},</if>
			del_flag = #{delFlag}
		WHERE trend_id = #{trendId} <if test="versionNo !=null"> and version_no = #{versionNo}</if>
	</update>
	
	<update id="deleteTrendOrigin">
		UPDATE mde_trends SET 
			update_date = now(), 
			origin_trend_id = -1 , 
			version_no = version_no +1
		WHERE origin_trend_id = #{trendId} <if test="versionNo !=null"> and version_no = #{versionNo}</if>
	</update>
	
	<sql id="getFriendUnionTable">
		SELECT
			case #{userCode} 
				when user_id_1 then user_id_2
				when user_id_2 then user_id_1 
			end as user_code
		FROM
			mde_friend_relation 
		WHERE
			( USER_ID_1 = #{userCode} OR USER_ID_2 = #{userCode} ) 	AND del_flag = 0 AND CONFIRM_TIME IS NOT NULL
	</sql>
	<!-- 获取好友的活动信息, trend_desc: xgzl->修改资料, fbdt->发布动态;  -->
	<select id="getFriendTrendsWithLimit" resultMap="MdeTrends">
		select 
			max(a.date) as updateDate,
			a.user_code as userCode, 
			a.activity as trendDesc,
			a.trend_id as trendId,
			b.name as userName,
			b.foreign_name as "foreignName",
			b.PREPARE_FLAG as prepareFlag,
			b.identity_flag as "identityFlag",
			b.HEAD_PORTRAIT as userHeadPortrait
		from (
			SELECT user_code ,
			case #{lang} when 'en' then 'publish trends' else '发布动态' end as  activity ,
			create_date as date ,
			trend_id
			FROM `mde_trends`
			where del_flag=0 and unix_timestamp(create_date) &gt; unix_timestamp(DATE_SUB(now(),INTERVAL IFNULL(#{limitDay},7) DAY))
		) a 
		left join mde_user b on b.user_code = a.user_code and b.del_flag=0
		where a.user_code in (
			<include refid="getFriendUnionTable"/> 
		)
		group by a.user_code
		order by a.date desc 
		limit #{limitSize}
	</select>
	
	<!-- 获取好友动态 -->
	<select id="getFriendTrends" resultType="com.wechat.model.dto.TrendsDto">
		SELECT 
			a.trend_id AS "trendId",
			a.user_code AS "userCode",
			a.trend_desc AS "trendDesc",
			a.trend_picurl AS "trendPicurl",
			a.show_flag AS "showFlag",
			IFNULL(a.like_who,'') AS "likeWho",
			IFNULL(a.like_count,0) AS "likeCount",
			IFNULL(a.comment_who,'') AS "commentWho",
			IFNULL(a.comment_count,0) AS "commentCount",
			a.version_no AS "versionNo",
			a.link_info AS "linkInfo",
			a.create_date AS "createDate",
			a.origin_trend_id AS "originTrendId",
			a.origin_user_code AS "originUserCode",
			a.origin_trend AS "originTrend"
			,b.name as userName
			,b.head_portrait as userHeadPortrait
			,b.foreign_name as foreignName
			,b.PREPARE_FLAG as prepareFlag
			,b.identity_flag as identityFlag
			,exp1.CO_NAME as coName
			,exp1.POSITION_TITLE as positionTitle
			,c.head_portrait as originUserHeadPortrait 
			,exp2.CO_NAME as originCoName
			,exp2.POSITION_TITLE as originPositionTitle
		FROM mde_trends a
		inner join mde_user b 
			on b.user_code = a.user_code and b.del_flag=0 and b.ALLOW_FLAG = '0'
		left join MDE_WORK_EXPERIENCE	exp1										
			on b.user_code = exp1.user_code and exp1.SHOW_FLAG=0						
		left join mde_user c 
			on c.user_code = a.origin_user_code and c.del_flag=0 and c.ALLOW_FLAG = '0'
		left join MDE_WORK_EXPERIENCE	exp2										
			on c.user_code = exp2.user_code and exp2.SHOW_FLAG=0
		<where>
			a.del_flag = 0 
			and (EXISTS (
					SELECT
						1
					FROM
						mde_friend_relation rel
					WHERE
						(
							rel.USER_ID_1 = #{userCode} and b.USER_CODE = rel.user_id_2
						) 
						AND rel.del_flag = 0 
						AND rel.CONFIRM_TIME IS NOT NULL 
				union all	
					SELECT
						1
					FROM
						mde_friend_relation rel
					WHERE
						(
							rel.USER_ID_2 = #{userCode} and b.USER_CODE = rel.user_id_1 
						) 
						AND rel.del_flag = 0 
						AND rel.CONFIRM_TIME IS NOT NULL 
					)
					oR b.IDENTITY_FLAG = '1' 
					oR b.USER_CODE = #{userCode})
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				order by a.create_date desc
			</otherwise>
		</choose>
	</select>
	
	<select id="getFriendTrendsCounts" resultType="java.lang.Integer">
		SELECT 
			count(1)
		FROM mde_trends a
		inner join mde_user b 
			on b.user_code = a.user_code and b.del_flag=0 and b.ALLOW_FLAG = '0'
		<where>
			a.del_flag = 0 			
			and (EXISTS (
					SELECT
						1
					FROM
						mde_friend_relation rel
					WHERE
						(
							rel.USER_ID_1 = #{userCode} and b.USER_CODE = rel.user_id_2
						) 
						AND rel.del_flag = 0 
						AND rel.CONFIRM_TIME IS NOT NULL 
				union all	
					SELECT
						1
					FROM
						mde_friend_relation rel
					WHERE
						(
							rel.USER_ID_2 = #{userCode} and b.USER_CODE = rel.user_id_1 
						) 
						AND rel.del_flag = 0 
						AND rel.CONFIRM_TIME IS NOT NULL 
					)
					oR b.IDENTITY_FLAG = '1' 
					oR b.USER_CODE = #{userCode})
		</where>
	</select>
	<!-- 获取我的动态  (固定条数)-->
	<select id="getMyTrendsWithLimit" resultMap="MdeTrends">
		SELECT 
			<include refid="mdeTrendsColumns"/>
		FROM mde_trends a
		<include refid="mdeTrendsJoins"/>
		<where>
			a.del_flag = 0 and a.user_code = #{userCode} and a.show_flag=0
		</where>
		<if test="beViewed != null and beViewed != ''">
			AND if (
				(SELECT
					1 
				FROM
					mde_friend_relation rel 
				WHERE
					rel.del_flag = 0 
					AND rel.CONFIRM_TIME IS NOT NULL 
					AND (( rel.USER_ID_1 = #{userCode} AND rel.USER_ID_2 = #{beViewed} ) 
						OR ( rel.USER_ID_1 = #{beViewed} AND rel.USER_ID_2 = #{userCode} ) ) )
				, 1=1, a.SHOW_FLAG = '0')
		</if>
		order by a.create_date desc
		limit #{limitSize}
	</select>
	<!-- 获取我的动态 -->
	<select id="getMyTrends" resultType="com.wechat.model.dto.TrendsDto">
		SELECT 
			a.trend_id AS "trendId",
			a.user_code AS "userCode",
			a.trend_desc AS "trendDesc",
			a.trend_picurl AS "trendPicurl",
			a.show_flag AS "showFlag",
			IFNULL(a.like_who,'') AS "likeWho",
			IFNULL(a.like_count,0) AS "likeCount",
			IFNULL(a.comment_who,'') AS "commentWho",
			IFNULL(a.comment_count,0) AS "commentCount",
			a.version_no AS "versionNo",
			a.link_info AS "linkInfo",
			a.create_date AS "createDate",
			a.origin_trend_id AS "originTrendId",
			a.origin_user_code AS "originUserCode",
			a.origin_trend AS "originTrend"
			,b.name as userName
			,b.PREPARE_FLAG as prepareFlag
			,b.head_portrait as userHeadPortrait
			,b.foreign_name as foreignName
			,b.identity_flag as identityFlag
			,exp1.CO_NAME as coName
			,exp1.POSITION_TITLE as positionTitle
			,c.head_portrait as originUserHeadPortrait 
			,exp2.CO_NAME as originCoName
			,exp2.POSITION_TITLE as originPositionTitle
		FROM mde_trends a
		inner join mde_user b 
			on b.user_code = a.user_code and b.del_flag=0 and b.ALLOW_FLAG = '0'
		left join MDE_WORK_EXPERIENCE	exp1										
			on b.user_code = exp1.user_code and exp1.SHOW_FLAG=0						
		left join mde_user c 
			on c.user_code = a.origin_user_code and c.del_flag=0 and c.ALLOW_FLAG = '0'
		left join MDE_WORK_EXPERIENCE	exp2										
			on c.user_code = exp2.user_code and exp2.SHOW_FLAG=0
		<where>
			a.del_flag = 0 and a.user_code = #{beViewed}
		</where>
		<if test="theSearch != null and theSearch != ''">
			AND if (
				(SELECT
					1 
				FROM
					mde_friend_relation rel 
				WHERE
					rel.del_flag = 0 
					AND rel.CONFIRM_TIME IS NOT NULL 
					AND (( rel.USER_ID_1 = #{theSearch} AND rel.USER_ID_2 = #{beViewed} ) 
						OR ( rel.USER_ID_1 = #{beViewed} AND rel.USER_ID_2 = #{theSearch} ) ) )
				, 1=1, a.SHOW_FLAG = '0')
		</if>
		order by a.create_date desc
	</select>
	
	<select id="getMyTrendsCounts" resultType="java.lang.Integer">
		SELECT 
			count(1)
		FROM mde_trends a
		inner join mde_user b 
			on b.user_code = a.user_code and b.del_flag=0 and b.ALLOW_FLAG = '0'
		left join MDE_WORK_EXPERIENCE	exp1										
			on b.user_code = exp1.user_code and exp1.SHOW_FLAG=0						
		left join mde_user c 
			on c.user_code = a.origin_user_code and c.del_flag=0 and c.ALLOW_FLAG = '0'
		left join MDE_WORK_EXPERIENCE	exp2										
			on c.user_code = exp2.user_code and exp2.SHOW_FLAG=0
		<where>
			a.del_flag = 0 and a.user_code = #{beViewed}
		</where>
		<if test="theSearch != null and theSearch != ''">
			AND if (
				(SELECT
					1 
				FROM
					mde_friend_relation rel 
				WHERE
					rel.del_flag = 0 
					AND rel.CONFIRM_TIME IS NOT NULL 
					AND (( rel.USER_ID_1 = #{theSearch} AND rel.USER_ID_2 = #{beViewed} ) 
						OR ( rel.USER_ID_1 = #{beViewed} AND rel.USER_ID_2 = #{theSearch} ) ) )
				, 1=1, a.SHOW_FLAG = '0')
		</if>
	</select>
	
	
</mapper>