<?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="cn.com.countrygarden.bi.main.dao.experience.TygGzhH5Mapper">
	<select id="selectListByPhone" 	resultType="cn.com.countrygarden.bi.main.dto.experience.WcxExperContentDto">
		SELECT
		distinct
		m.id id,
		m.publish_type publishType,
		m.publish_content publishContent,
		DATE_FORMAT(m.publish_time,'%Y-%m-%d %H:%i:%s') publishTime,
		m.point point,
		m.publish_no publishNo,
		m.publish_reply publishReply,
		m.tag_id tagId,
		(select head_imgurl from wcx_employees_manage
			where phone =#{phone}
			and deleted=0
			and effective=1
			and status=1
			and head_imgurl is not null limit 1) headImgurl,
		m.publish_time
		from
		 wcx_publish_message m
		where m.deleted = 0
		AND m.phone = #{phone}
		ORDER BY m.publish_time DESC
		<if test="page != null and size != null">
			limit #{page}, #{size}
		</if>
	</select>

	<select id="selectCountByPhone" resultType="int">
		SELECT
	count(0)
		from
		wcx_publish_message m
		where m.deleted = 0
		AND m.phone = #{phone}
		ORDER BY m.publish_time DESC
	</select>

	<insert id="save" parameterType="cn.com.countrygarden.bi.main.model.experience.WcxPublishMessage">

      INSERT INTO wcx_publish_message
        (
            publish_no,
            open_id,
            created,
            creater,
            deleted,
            updated,
            updater,
            version,
            name,
            phone,
            age,
            organ_code,
            organ_name,
            comm_id,
            comm_name,
            identity,
            publish_type,
            publish_content,
            publish_time,
            avatar_url
        )
        VALUES
        (
            #{publishNo},
            #{openId},
            #{created},
            #{creater},
            #{deleted},
            #{updated},
            #{updater},
            #{version},
            #{name},
            #{phone},
            #{age},
            #{organCode},
            #{organName},
            #{commId},
            #{commName},
            #{identity},
            #{publishType},
            #{publishContent},
            #{publishTime},
            #{avatarUrl}
        )

    </insert>

	<insert id="saveFile" parameterType="cn.com.countrygarden.bi.main.model.experience.WcxPublishFile">

		INSERT INTO wcx_publish_file
		(
		created,
		creater,
		deleted,
		updated,
		updater,
		version,
		publish_no,
		publish_type,
		publish_url
		)
		VALUES
		<foreach collection="list" item="item" separator="," >
			(
			#{item.created},
			#{item.creater},
			#{item.deleted},
			#{item.updated},
			#{item.updater},
			#{item.version},
			#{item.pushlishNo},
			#{item.publishType},
			#{item.publishUrl}
			)
		</foreach>
	</insert>

	<select id="myInfo" parameterType="cn.com.countrygarden.bi.main.dto.experience.WcxPublishContentDto"
			resultType="cn.com.countrygarden.bi.main.dto.experience.WcxMyPublishContentDto">

		SELECT
		m.id id,
		m.publish_no publishNo,
		m.open_id openId,
		m.created created,
		m.creater creater,
		m.deleted deleted,
		m.updated updated,
		m.updater updater,
		m.version version,
		(CASE
		WHEN
		(SELECT t.`name` FROM wcx_employees_manage t where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone LIMIT 1) IS NOT NULL THEN
		(SELECT t.`name` FROM wcx_employees_manage t where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone LIMIT 1)
		ELSE
		(SELECT t.`name` FROM wcx_employees_manage t where 1 = 1 AND t.deleted =0 AND t.identification = '0' AND t.phone = m.phone LIMIT 1)
		END
		) `name`,
		m.name chatName,
		m.phone phone,
		m.age age,
		m.organ_code organCode,
		m.organ_name organName,
		m.comm_id commId,
		m.comm_name commName,
		m.identity identity,
		m.publish_type publishType,
		m.publish_content publishContent,
		DATE_FORMAT(m.publish_time,'%Y-%m-%d %H:%i:%s') publishTime,
		m.point point,
		m.avatar_url avatarUrl
		FROM
		wcx_publish_message m
		WHERE 1 = 1
		<if test="deleted!=null">
			AND m.deleted = #{deleted}
		</if>
		<if test="phone!=null and phone !=''">
			AND m.phone = #{phone}
		</if>
		<if test="openid!=null and openid!=''">
			AND m.open_id = #{openid}
		</if>
		<if test="publishType!=null and publishType!=''">
			AND m.publish_type = #{publishType}
		</if>
		<if test="id!=null ">
			AND m.id = #{id}
		</if>


	</select>


	<select id="info" parameterType="cn.com.countrygarden.bi.main.dto.experience.WcxPublishContentDto"
			resultType="cn.com.countrygarden.bi.main.dto.experience.WcxMyPublishContentDto">

		SELECT
		m.id id,
		m.phone phone,
		head_imgurl headImgurl,
		tyg_certificate_number tygCertificateNumber,
		(CASE WHEN(SELECT t.`name` FROM wcx_employees_manage t where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone LIMIT 1) IS NOT NULL
		THEN(SELECT t.`name` FROM wcx_employees_manage t where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone LIMIT 1)
		ELSE(SELECT t.`name` FROM wcx_employees_manage t where 1 = 1 AND t.deleted =0 AND t.identification = '0' AND t.phone = m.phone LIMIT 1)END) `name`,
		tyg_grade_id tygGradeId
		FROM
		wcx_employees_manage m
		WHERE
		1 = 1
		AND m.deleted = 0
		<if test="phone!=null and phone!=''">
			AND m.phone = #{phone}
		</if>
		<if test="identity!=null and identity!=''">
			AND m.identification = #{identity}
		</if>
		<if test="openid!=null and openid!=''">
			AND m.opend_id = #{openid}
		</if>
		LIMIT 1

	</select>


	<update id="deletePublishMessage">

        UPDATE wcx_publish_message SET deleted = #{deleted} WHERE id = #{id}

    </update>

	<update id="deletePublishFile">

        UPDATE wcx_publish_file SET deleted =  #{deleted} WHERE publish_no = #{publishNo}

    </update>

	<select id="selectCount" resultType="int">
		select IFNUll(sum(point),0) point from wcx_publish_message e where e.deleted=0
		<if test="phone!=null and phone!=''">
			AND e.phone = #{phone}
		</if>
		<if test="openid!=null and openid!=''">
			AND e.open_id = #{openid}
		</if>
	</select>

	<update id="updateAccount" parameterType="cn.com.countrygarden.bi.main.dto.experience.WcxPublishContentDto">
		update
		wcx_employees_manage e
		set account=#{account}
		WHERE
		1=1
		<if test="phone!=null and phone!=''">
			AND e.phone = #{phone}
		</if>
		<if test="openid!=null and openid!=''">
			AND e.opend_id = #{openid}
		</if>
	</update>

	<update id="updateAccountByphone">
		update wcx_employees_manage e
		set account=#{account}
		WHERE
		1=1
		and
		phone =(select phone from wcx_publish_message WHERE id = #{id})
	</update>
</mapper>