<?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.WcxExperienceMapper">

	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap"
			   type="cn.com.countrygarden.bi.main.model.experience.WcxEmployeesManage">
		<id column="id" property="id" />
		<result column="created" property="created" />
		<result column="creater" property="creater" />
		<result column="deleted" property="deleted" />
		<result column="updated" property="updated" />
		<result column="updater" property="updater" />
		<result column="version" property="version" />
		<result column="name" property="name" />
		<result column="phone" property="phone" />
		<result column="age" property="age" />
		<result column="sex" property="sex" />
		<result column="id_card" property="idCard" />
		<result column="organ_code" property="organCode" />
		<result column="organ_name" property="organName" />
		<result column="comm_id" property="commId" />
		<result column="comm_name" property="commName" />
		<result column="dep_code" property="depCode" />
		<result column="dep_name" property="depName" />
		<result column="closing_time" property="closingTime" />
		<result column="customer_tag" property="customerTag" />
		<result column="key_note" property="keyNote" />
		<result column="hiredate" property="hiredate" />
		<result column="mpbs" property="mpbs" />
		<result column="positions" property="positions" />
		<result column="education" property="education" />
		<result column="marriage" property="marriage" />
		<result column="industry" property="industry" />
		<result column="occupation" property="occupation" />
		<result column="hobby" property="hobby" />
		<result column="identification" property="identification" />
		<result column="effective" property="effective" />
		<result column="status" property="status" />
		<result column="is_login" property="isLogin" />
		<result column="last_updated" property="lastUpdated" />
		<result column="account" property="account" />
		<result column="is_focus_wx" property="isFocusWx" />
		<result column="opend_id" property="opendId" />
		<result column="wx_name" property="wxName" />
		<result column="tyg_grade_id" property="tygGradeId" />
		<result column="tyg_certificate_number" property="tygCertificateNumber" />
		<result column="access_token" property="accessToken" />
		<result column="head_imgurl" property="headImgurl" />
	</resultMap>

	<resultMap id="BaseWcxPublishMessageMap" type="cn.com.countrygarden.bi.main.model.experience.WcxPublishMessage">
		<id column="id" property="id"/>
		<result column="publish_no" property="publishNo"/>
		<result column="open_id" property="openId"/>
		<result column="created" property="created"/>
		<result column="creater" property="creater"/>
		<result column="deleted" property="deleted"/>
		<result column="updated" property="updated"/>
		<result column="updater" property="updater"/>
		<result column="version" property="version"/>
		<result column="name" property="name"/>
		<result column="phone" property="phone"/>
		<result column="age" property="age"/>
		<result column="organ_code" property="organCode"/>
		<result column="organ_name" property="organName"/>
		<result column="comm_id" property="commId"/>
		<result column="comm_name" property="commName"/>
		<result column="identity" property="identity"/>
		<result column="publish_type" property="publishType"/>
		<result column="publish_content" property="publishContent"/>
		<result column="publish_time" property="publishTime"/>
		<result column="point" property="point"/>
		<result column="avatar_url" property="avatarUrl"/>
	</resultMap>


	<select id="list" parameterType="java.util.Map"
			resultType="cn.com.countrygarden.bi.main.model.experience.WcxEmployeesManage">

		SELECT
			m.id id,
			m.created created,
			m.creater creater,
			m.deleted deleted,
			m.updated updated,
			m.updater updater,
			m.version version,
			m.name `name`,
			m.phone phone,
			m.age age,
			m.sex sex,
			m.id_card idCard,
			m.organ_code organCode,
			m.organ_name organName,
			m.comm_id commId,
			m.comm_name commName,
			m.dep_code depCode,
			m.dep_name depName,
			m.closing_time closingTime,
			m.customer_tag customerTag,
			m.key_note keyNote,
			m.hiredate hiredate,
			m.mpbs mpbs,
			m.positions positions,
			m.education education,
			m.marriage marriage,
			m.industry industry,
			m.occupation occupation,
			m.hobby hobby,
			m.identification identification,
			m.effective effective,
			m.account,
			m.is_focus_wx isFocusWx,
			m.opend_id opendId,
			m.wx_name wxName,
			m.tyg_grade_id tygGradeId,
			m.tyg_certificate_number tygCertificateNumber,
			(
			CASE
				WHEN m.last_updated is NOT NULL  THEN '1'
				ELSE '0'
			END
			) status,
			m.is_login isLogin,
			m.last_updated lastUpdated
		FROM
			wcx_employees_manage m
		<where>
			1 = 1
			AND m.deleted = 0
			<if test="id!=null">
				AND m.id = #{id}
			</if>
			<if test="name!=null and name!=''">
				AND m.name LIKE CONCAT('%',#{name},'%')
			</if>
			<if test="phone!=null and phone!=''">
				AND m.phone LIKE CONCAT('%',#{phone},'%')
			</if>
			<if test="organCode!=null and organCode!=''">
				AND m.organ_code = #{organCode}
			</if>
			<if test="commId!=null ">
				AND m.comm_id = #{commId}
			</if>
			<if test="identification!=null and identification!=''">
				AND m.identification = #{identification}
			</if>
		</where>
		ORDER BY
			m.created DESC
		<if test="offset != null and limit != null">
			limit #{offset}, #{limit}
		</if>
	</select>
	<select id="list2" parameterType="java.util.Map"
			resultType="cn.com.countrygarden.bi.main.model.experience.WcxEmployeesManageDto">

		SELECT
		m.id id,
		m.created created,
		m.creater creater,
		m.deleted deleted,
		m.updated updated,
		m.updater updater,
		m.version version,
		m.name `name`,
		m.phone phone,
		m.age age,
		m.sex sex,
		m.id_card idCard,
		m.organ_code organCode,
		m.organ_name organName,
		m.comm_id commId,
		m.comm_name commName,
		m.dep_code depCode,
		m.dep_name depName,
		m.closing_time closingTime,
		m.customer_tag customerTag,
		m.key_note keyNote,
		m.hiredate hiredate,
		m.mpbs mpbs,
		m.positions positions,
		m.education education,
		m.marriage marriage,
		m.industry industry,
		m.occupation occupation,
		m.hobby hobby,
		m.identification identification,
		m.effective effective,
		m.account,
		m.is_focus_wx isFocusWx,
		m.opend_id opendId,
		m.wx_name wxName,
		m.tyg_grade_id tygGradeId,
		m.tyg_certificate_number tygCertificateNumber,
		(
		CASE
		WHEN m.last_updated is NOT NULL  THEN '1'
		ELSE '0'
		END
		) status,
		m.is_login isLogin,
		m.last_updated lastUpdated
		FROM
		wcx_employees_manage m left join tyg_grade t on m.tyg_grade_id=t.id
		<where>
			1 = 1
			AND m.deleted = 0
			<if test="id!=null">
				AND m.id = #{id}
			</if>
			<if test="name!=null and name!=''">
				AND m.name LIKE CONCAT('%',#{name},'%')
			</if>
			<if test="phone!=null and phone!=''">
				AND m.phone LIKE CONCAT('%',#{phone},'%')
			</if>
			<if test="organCode!=null and organCode!=''">
				AND m.organ_code = #{organCode}
			</if>
			<if test="commId!=null ">
				AND m.comm_id = #{commId}
			</if>
			<if test="identification!=null and identification!=''">
				AND m.identification = #{identification}
			</if>
		</where>
		ORDER BY
		m.created DESC
		<if test="offset != null and limit != null">
			limit #{offset}, #{limit}
		</if>
	</select>
	<select id="count" resultType="java.lang.Integer" parameterType="java.util.Map">
		SELECT
			count(*)
		FROM
		wcx_employees_manage m
		<where>
			1 = 1
			AND m.deleted = 0
			<if test="id!=null">
				AND m.id = #{id}
			</if>
			<if test="name!=null and name!=''">
				AND m.name LIKE CONCAT('%',#{name},'%')
			</if>
			<if test="phone!=null and phone!=''">
				AND m.phone LIKE CONCAT('%',#{phone},'%')
			</if>
			<if test="organCode!=null and organCode!=''">
				AND m.organ_code = #{organCode}
			</if>
			<if test="commId!=null ">
				AND m.comm_id = #{commId}
			</if>
			<if test="identification!=null and identification!=''">
				AND m.identification = #{identification}
			</if>
		</where>

	</select>

	<select id="queryById" parameterType="java.lang.Long" resultMap="BaseResultMap">

		SELECT * FROM wcx_employees_manage WHERE id = #{id}

	</select>
	<select id="selectByByOpenId" parameterType="java.lang.String" resultMap="BaseResultMap">
		SELECT * FROM wcx_employees_manage
		WHERE opend_id = #{openId} and effective=1 and deleted = 0
	</select>

    <select id="queryByPhone" resultMap="BaseResultMap">
		SELECT * FROM wcx_employees_manage
		WHERE
			deleted = 0
			AND phone = #{phone}
			<if test="identification!=null and identification!=''">
				AND identification = #{identification}
			</if>
	</select>


    <update id="delete" parameterType="java.lang.Long">

        UPDATE wcx_employees_manage SET deleted = 1 WHERE id = #{id}

    </update>


    <update id="setEnable">

        UPDATE wcx_employees_manage SET effective= #{enable},updated = SYSDATE() WHERE id = #{id}

    </update>


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

		INSERT INTO wcx_employees_manage
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="created!=null">
				created,
			</if>
			<if test="creater!=null and creater!=''">
				creater,
			</if>
			<if test="deleted!=null">
				deleted,
			</if>
			<if test="updated!=null">
				updated,
			</if>
			<if test="updater!=null and updater!=''">
				updater,
			</if>
			<if test="version!=null">
				version,
			</if>
			<if test="name!=null and name!=''">
				name,
			</if>
			<if test="phone!=null and phone!=''">
				phone,
			</if>
			<if test="age!=null and age!=''">
				age,
			</if>
			<if test="sex!=null and sex!=''">
				sex,
			</if>
			<if test="idCard!=null and idCard!=''">
				id_card,
			</if>
			<if test="organCode!=null and organCode!=''">
				organ_code,
			</if>
			<if test="commId!=null and commId!=''">
				comm_id,
			</if>
			<if test="depCode!=null and depCode!=''">
				dep_code,
			</if>
			<if test="depName!=null and depName!=''">
				dep_name,
			</if>
			<if test="closingTime!=null and closingTime	!=''">
				closing_time,
			</if>
			<if test="customerTag!=null and customerTag	!=''">
				customer_tag,
			</if>
			<if test="keyNote!=null and keyNote!=''">
				key_note,
			</if>
			<if test="hiredate!=null">
				hiredate,
			</if>
			<if test="mpbs!=null and mpbs!=''">
				mpbs,
			</if>
			<if test="positions!=null and positions!=''">
				positions,
			</if>
			<if test="education!=null and education!=''">
				education,
			</if>
			<if test="marriage!=null and marriage!=''">
				marriage,
			</if>
			<if test="industry!=null and industry!=''">
				industry,
			</if>
			<if test="occupation!=null and occupation!=''">
				occupation,
			</if>
			<if test="hobby!=null and hobby!=''">
				hobby,
			</if>
			<if test="effective!=null and effective!=''">
				effective,
			</if>
			<if test="status!=null and status!=''">
				status,
			</if>
			<if test="identification!=null and identification!=''">
				identification,
			</if>
			<if test="opendId!=null and opendId!=''">
				opend_id,
			</if>
			<if test="account!=null">
				account,
			</if>
			<if test="isFocusWx!=null">
				is_focus_wx,
			</if>
			<if test="wxName!=null and wxName!=''">
				wx_name,
			</if>
			<if test="tygCertificateNumber!=null and tygCertificateNumber!=''">
				tyg_certificate_number,
			</if>
			<if test="headImgurl!=null and headImgurl!=''">
				head_imgurl
			</if>
		</trim>
		<trim prefix="VALUES (" suffix=")" suffixOverrides=",">
			<if test="created!=null">
				#{created},
			</if>
			<if test="creater!=null and creater!=''">
				#{creater},
			</if>
			<if test="deleted!=null">
				#{deleted},
			</if>
			<if test="updated!=null">
				#{updated},
			</if>
			<if test="updater!=null and updater!=''">
				#{updater},
			</if>
			<if test="version!=null">
				#{version},
			</if>
			<if test="name!=null and name!=''">
				#{name},
			</if>
			<if test="phone!=null and phone!=''">
				#{phone},
			</if>
			<if test="age!=null and age!=''">
				#{age},
			</if>
			<if test="sex!=null and sex!=''">
				#{sex},
			</if>
			<if test="idCard!=null and idCard!=''">
				#{idCard},
			</if>
			<if test="organCode!=null and organCode!=''">
				#{organCode},
			</if>
			<if test="commId!=null and commId!=''">
				#{commId},
			</if>
			<if test="depCode!=null and depCode!=''">
				#{depCode},
			</if>
			<if test="depName!=null and depName!=''">
				#{depName},
			</if>
			<if test="closingTime!=null and closingTime	!=''">
				#{closingTime},
			</if>
			<if test="customerTag!=null and customerTag	!=''">
				#{customerTag},
			</if>
			<if test="keyNote!=null and keyNote!=''">
				#{keyNote},
			</if>
			<if test="hiredate!=null">
				#{hiredate},
			</if>
			<if test="mpbs!=null and mpbs!=''">
				#{mpbs},
			</if>
			<if test="positions!=null and positions!=''">
				#{positions},
			</if>
			<if test="education!=null and education!=''">
				#{education},
			</if>
			<if test="marriage!=null and marriage!=''">
				#{marriage},
			</if>
			<if test="industry!=null and industry!=''">
				#{industry},
			</if>
			<if test="occupation!=null and occupation!=''">
				#{occupation},
			</if>
			<if test="hobby!=null and hobby!=''">
				#{hobby},
			</if>
			<if test="effective!=null and effective!=''">
				#{effective},
			</if>
			<if test="status!=null and status!=''">
				#{status},
			</if>
			<if test="identification!=null and identification!=''">
				#{identification},
			</if>
			<if test="opendId!=null and opendId!=''">
				#{opendId},
			</if>
			<if test="account!=null">
				#{account},
			</if>
			<if test="isFocusWx!=null">
				#{isFocusWx},
			</if>
			<if test="wxName!=null and wxName!=''">
				#{wxName},
			</if>
			<if test="tygCertificateNumber!=null and tygCertificateNumber!=''">
				#{tygCertificateNumber},
			</if>
			<if test="headImgurl!=null and headImgurl!=''">
				#{headImgurl}
			</if>
		</trim>
	</insert>

	<update id="update" parameterType="cn.com.countrygarden.bi.main.model.experience.WcxEmployeesManage">

		UPDATE
			wcx_employees_manage
		<set>
			<trim prefix="" suffix="" suffixOverrides=",">
				<if test="updated!=null">
					updated = SYSDATE(),
				</if>
				<if test="version!=null">
					version = #{version},
				</if>
				<if test="updater!=''">
					updater = #{updater},
				</if>
				<if test="name!=null and name!=''">
					name = #{name},
				</if>
				<if test="phone!=null and phone!=''">
					phone = #{phone},
				</if>
				<if test="age!=null and age!=''">
					age = #{age},
				</if>
				<if test="sex!=null and sex!=''">
					sex = #{sex},
				</if>
				<if test="idCard!=null and idCard!=''">
					id_card = #{idCard},
				</if>
				<if test="organCode!=null and organCode!=''">
					organ_code = #{organCode},
				</if>
				<if test="organName!=null and organName!=''">
					organ_name = #{organName},
				</if>
				<if test="commId!=null and commId!=''">
					comm_id = #{commId},
				</if>
				<if test="commName!=null and commName!=''">
					comm_name = #{commName},
				</if>
				<if test="depCode!=null and depCode!=''">
					dep_code = #{depCode},
				</if>
				<if test="depName!=null and depName!=''">
					dep_name = #{depName},
				</if>
				<if test="closingTime!=null and closingTime	!=''">
					closing_time = #{closingTime},
				</if>
				<if test="customerTag!=null and customerTag	!=''">
					customer_tag = #{customerTag},
				</if>
				<if test="keyNote!=null and keyNote!=''">
					key_note = #{keyNote},
				</if>
				<if test="hiredate!=null">
					hiredate = #{hiredate},
				</if>
				<if test="mpbs!=null and mpbs!=''">
					mpbs = #{mpbs},
				</if>
				<if test="positions!=null and positions!=''">
					positions = #{positions},
				</if>
				<if test="education!=null and education!=''">
					education = #{education},
				</if>
				<if test="marriage!=null and marriage!=''">
					marriage = #{marriage},
				</if>
				<if test="industry!=null and industry!=''">
					industry = #{industry},
				</if>
				<if test="occupation!=null and occupation!=''">
					occupation = #{occupation},
				</if>
				<if test="hobby!=null and hobby!=''">
					hobby = #{hobby},
				</if>
				<if test="effective!=null and effective!=''">
					effective = #{effective},
				</if>
				<if test="status!=null and status!=''">
					status = #{status},
				</if>
				<if test="identification !=null and identification!=''">
					identification = #{identification},
				</if>
				<if test="tygGradeId !=null and tygGradeId!=''">
					tyg_grade_id = #{tygGradeId},
				</if>
				<if test="opendId !=null and opendId!=''">
					opend_id = #{opendId},
				</if>
				<if test="wxName !=null and wxName!=''">
					wx_name = #{wxName},
				</if>
				<if test="tygCertificateNumber !=null and tygCertificateNumber!=''">
					tyg_certificate_number = #{tygCertificateNumber},
				</if>
				<if test="accessToken !=null and accessToken!=''">
					access_token = #{accessToken},
				</if>
				<if test="headImgurl !=null and headImgurl!=''">
					head_imgurl = #{headImgurl},
				</if>
				<if test="lastUpdated !=null">
					last_updated= SYSDATE(),
				</if>
				<if test="isFocusWx !=null">
					is_focus_wx= #{isFocusWx},
				</if>
				<if test="account !=null">
					account= #{account}
				</if>
			</trim>
		</set>
		WHERE id = #{id}

	</update>
	
	<insert id="batchSave" parameterType="cn.com.countrygarden.bi.main.model.experience.WcxEmployeesManage">
		INSERT INTO
			wcx_employees_manage
		(
			created,
			creater,
			deleted,
			updated,
			updater,
			version,
			name,
			phone,
			age,
			sex,
			id_card,
			organ_code,
			organ_name,
			comm_id,
			comm_name,
			dep_code,
			dep_name,
			hiredate,
			mpbs,
			positions,
			education,
			effective,
			status,
			identification
		) VALUES
		<foreach collection="list" item="item" separator="," >
			(
			#{item.created},
			#{item.creater},
			#{item.deleted},
			#{item.updated},
			#{item.updater},
			#{item.version},
			#{item.name},
			#{item.phone},
			#{item.age},
			#{item.sex},
			#{item.idCard},
			#{item.organCode},
			#{item.organName},
			#{item.commId},
			#{item.commName},
			#{item.depCode},
			#{item.depName},
			#{item.hiredate},
			#{item.mpbs},
			#{item.positions},
			#{item.education},
			#{item.effective},
			#{item.status},
			#{item.identification}
			)
		</foreach>

	</insert>


	<insert id="batchSaveCustomer" parameterType="cn.com.countrygarden.bi.main.model.experience.WcxEmployeesManage">
		INSERT INTO
		wcx_employees_manage
		(
		created,
		creater,
		deleted,
		updated,
		updater,
		version,
		name,
		phone,
		age,
		sex,
		organ_code,
		organ_name,
		comm_id,
		comm_name,
		closing_time,
		customer_tag,
		key_note,
		effective,
		status,
		identification
		) VALUES
		<foreach collection="list" item="item" separator="," >
			(
			#{item.created},
			#{item.creater},
			#{item.deleted},
			#{item.updated},
			#{item.updater},
			#{item.version},
			#{item.name},
			#{item.phone},
			#{item.age},
			#{item.sex},
			#{item.organCode},
			#{item.organName},
			#{item.commId},
			#{item.commName},
			#{item.closingTime},
			#{item.customerTag},
			#{item.keyNote},
			#{item.effective},
			#{item.status},
			#{item.identification}
			)
		</foreach>

	</insert>



	<select id="contentList" parameterType="java.util.Map"
			resultType="cn.com.countrygarden.bi.main.dto.experience.WcxExperContentDto">
		<if test="name !=null or commId or organCode !=null">
			select * from (
		</if>
		SELECT
		m.id id,
		(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
		<if test="name!=null and name!=''">AND t.name like concat('%',#{name},'%') and t.name is not null
		</if>
		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
		<if test="name!=null and name!=''">
					AND t.name like concat('%',#{name},'%') and t.name is not null
				</if>
            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
				<if test="name!=null and name!=''">
					AND t.name like concat('%',#{name},'%') and t.name is not null
				</if>
		LIMIT 1)
		END
		) `name`,
		m.name chatName,
		m.phone phone,
		(
		CASE
		WHEN
		(SELECT t.age 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.age 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.age FROM wcx_employees_manage t where 1 = 1 AND t.deleted =0 AND t.identification = '0' AND t.
		phone = m.phone LIMIT 1)
		END
		) age,
		(
		CASE
		WHEN
		(SELECT t.organ_code FROM wcx_employees_manage t
		where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone
		<if test="organCode!=null and organCode!=''">
			AND t.organ_code = #{organCode} and t.organ_code is not null
		</if>
		LIMIT 1
        ) IS NOT NULL
        THEN
        (SELECT t.organ_code FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone
        <if test="organCode!=null and organCod !=''">
		AND t.organ_code = #{organCode} and t.organ_code is not null
		</if>
		LIMIT 1)
        ELSE
        (SELECT t.organ_code FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '0' AND t.phone = m.phone
        <if test="organCode!=null and organCode!=''">
			AND t.organ_code = #{organCode} AND t.organ_code is not null
		</if>
		LIMIT 1)
        END
        ) organCode,
        (
        CASE
        WHEN
        (SELECT t.organ_name FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone
		<if test="organCode !=null and organCode !=''">
			AND t.organ_code = #{organCode}
		</if>
        LIMIT 1) IS NOT NULL
        THEN
        (SELECT t.organ_name FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone
		<if test="organCode!=null and organCode !=''">
			AND t.organ_code = #{organCode} and t.organ_code is not null
		</if>
        LIMIT 1)
        ELSE
        (SELECT t.organ_name FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '0' AND t.phone = m.phone
		<if test="organCode !=null and organCode !=''">
			AND t.organ_code = #{organCode} and t.organ_code is not null
		</if>
        LIMIT 1)
        END
        ) organName,
        (
        CASE
        WHEN
        (SELECT t.comm_id FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone
		<if test="commId!=null and commId!=''">
			AND t.comm_id = #{commId}
		</if>
        LIMIT 1) IS NOT NULL
        THEN
        (SELECT t.comm_id FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone
		<if test="commId!=null and commId!=''">
			AND t.comm_id = #{commId}
		</if>
        LIMIT 1)
        ELSE
        (SELECT t.comm_id FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '0' AND t.phone = m.phone
		<if test="commId!=null and commId!=''">
			AND t.comm_id = #{commId}
		</if>
        LIMIT 1)
        END
        ) commId,
        (
        CASE
        WHEN
        (SELECT t.comm_name FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone
		<if test="commId!=null and commId!=''">
			AND t.comm_id = #{commId}
		</if>
        LIMIT 1) IS NOT NULL
        THEN
        (SELECT t.comm_name FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '1' AND t.phone = m.phone
		<if test="commId!=null and commId!=''">
			AND t.comm_id = #{commId}
		</if>
        LIMIT 1)
        ELSE
        (SELECT t.comm_name FROM wcx_employees_manage t
        where 1 = 1 AND t.deleted =0 AND t.identification = '0' AND t.phone = m.phone
		<if test="commId!=null and commId!=''">
			AND t.comm_id = #{commId}
		</if>
		LIMIT 1)
		END
		)
		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.publish_no publishNo,
		m.publish_reply publishReply,
		m.tag_id tagId
		FROM
		wcx_publish_message m left join tyg_tag tt on m.tag_id=tt.id
		<where>
			1 = 1
			AND m.deleted = 0
			<if test="publishTime!=null and publishTime!=''">
				AND DATE_FORMAT(m.publish_time,'%Y-%m-%d') = #{publishTime}
			</if>
			<if test="identity!=null">
				AND m.identity IN
				<foreach item="item" collection="identity" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="publishType!=null and publishType!=''">
				AND m.publish_type = #{publishType}
			</if>
		</where>
		ORDER BY m.created DESC
		<if test="name !=null or commId or organCode !=null">
			) mm where 1=1
		</if>
		<if test="name!=null and name!=''">
			AND mm.name like concat('%',#{name},'%') and mm.name is not null
		</if>
		<if test="commId!=null and commId!=''">
			AND mm.commId = #{commId} and mm.commId is not null
		</if>
		<if test="organCode!=null and organCode !=''">
			AND mm.organCode = #{organCode} and  mm.organCode is not null
		</if>
		<if test="offset != null and limit != null">
			limit #{offset}, #{limit}
		</if>
	</select>

	<select id="exportList" parameterType="java.util.Map"
			resultType="cn.com.countrygarden.bi.main.dto.experience.WcxExperContentDto">

		SELECT
		m.id id,
		e.`name` `name`,
		m.name chatName,
		m.phone phone,
		e.age age,
		e.organ_code organCode,
		e.organ_name organName,
		e.comm_id commId,
		e.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
		FROM
		wcx_publish_message m
		LEFT JOIN wcx_employees_manage e ON
		m.phone = e.phone
		<where>
			1 = 1
			AND e.identification = '1'
			AND m.deleted = 0
			AND e.deleted = 0
			<if test="publishTime!=null and publishTime !=''">
				AND DATE_FORMAT(m.publish_time,'%Y-%m-%d') = #{publishTime}
			</if>
			<if test="identity!=null">
				AND m.identity IN
				<foreach item="item" collection="identity" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="publishType!=null and publishType!=''">
				AND m.publish_type = #{publishType}
			</if>
		</where>
		ORDER BY m.created DESC

	</select>

	<select id="countContent" resultType="java.lang.Integer" parameterType="java.util.Map">
		SELECT
			count(*)
		FROM
		wcx_publish_message m
		LEFT JOIN wcx_employees_manage e ON
			m.phone = e.phone
		<if test="name!=null and name!=''">
			AND e.name like concat('%',#{name},'%') and e.name is not null
		</if>
		<if test="commId!=null and commId!=''">
			AND e.comm_id = #{commId} and e.comm_id is not null
		</if>
		<if test="organCode!=null and organCode !=''">
			AND e.organ_code = #{organCode} and  e.organ_code is not null
		</if>
		<where>
			1 = 1
            AND e.identification = '1'
			AND m.deleted = 0
			AND e.deleted = 0
			<if test="publishTime!=null and publishTime!=''">
				AND DATE_FORMAT(m.publish_time,'%Y-%m-%d') = #{publishTime}
			</if>
			<if test="identity!=null ">
				AND m.identity IN
				<foreach item="item" collection="identity" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="publishType!=null and publishType!=''">
				AND m.publish_type = #{publishType}
			</if>
		</where>

	</select>

	<update id="score" parameterType="cn.com.countrygarden.bi.main.dto.experience.WcxExperContentDto">
		UPDATE wcx_publish_message SET updated = SYSDATE(),point = #{point}
		<if test="tagId !=null and tagId !=''">
			,tag_id=#{tagId}
		</if>
		<if test="publishReply !=null and publishReply !=''">
			,publish_reply =#{publishReply}
		</if>
		 WHERE id = #{id}
	</update>

	<update id="deleteContent" parameterType="cn.com.countrygarden.bi.main.dto.experience.WcxExperContentDto">

		UPDATE wcx_publish_message SET updated = SYSDATE(),deleted = 1 WHERE id = #{id}

	</update>

	<select id="queryPublishFiles"
			parameterType="cn.com.countrygarden.bi.main.dto.experience.WcxExperContentDto"
			resultType="cn.com.countrygarden.bi.main.dto.experience.WcxPublishFileDto">

		SELECT
			f.id id,
			f.publish_no publishNo,
			f.publish_type publishType,
			f.publish_url publishUrl,
			m.`name` chatName,
      		(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.phone phone,
			DATE_FORMAT(m.publish_time,'%Y-%m-%d %H:%i:%s') publishTime,
			m.publish_content publishContent,
			m.avatar_url avatarUrl,
			m.point,
			m.publish_reply publishReply,
  			m.tag_id tagId
		FROM
			wcx_publish_file f
		LEFT JOIN wcx_publish_message m on
			f.publish_no = m.publish_no
		WHERE 1 = 1
			AND f.deleted = 0
			AND m.deleted = 0
			AND f.publish_no = #{publishNo}


	</select>


	<select id="queryPublishList" parameterType="java.lang.String" resultMap="BaseWcxPublishMessageMap">

		SELECT * FROM wcx_publish_message m WHERE  m.deleted = 0 AND m.phone = #{phone}

	</select>

	<update id="updateAccessToken" parameterType="cn.com.countrygarden.bi.main.model.experience.WcxEmployeesManage">
		update wcx_employees_manage set access_token=#{accessToken},wx_name=#{wxName},head_imgurl=#{headImgurl}
		where id =#{id}
	</update>

	<select id="selectByPhone" resultMap="BaseResultMap">
		select * from wcx_employees_manage where phone=#{phone} and deleted = 0
	</select>

	<select id="selectByIds" resultMap="BaseResultMap">
		select * from wcx_employees_manage
		where deleted = 0 and
		id in
		<foreach collection="ids" separator="," close=")" open="(" item="id">
			#{id}
		</foreach>
		and deleted = 0
		<if test="page != null and size != null">
			limit #{page}, #{size}
		</if>
	</select>

	<select id="selectCountByIds" resultType="int">
		select count(0) from wcx_employees_manage
		where id in
		<foreach collection="ids" separator="," close=")" open="(" item="id">
			#{id}
		</foreach>
		and deleted = 0
	</select>

	<select id="selectByTygGzhUserInfoDto" resultMap="BaseResultMap" parameterType="cn.com.countrygarden.bi.main.dto.experience.TygGzhUserInfoDto">
		select * from wcx_employees_manage
		where deleted = 0
		<if test="identification !=null and identification!='' and identificationArray ==null">
			and identification = #{identification}
		</if>
		<if test="identificationArray !=null">
			and identification in
			<foreach collection="identificationArray" separator="," open="(" close=")" item="identification">
				#{identification}
			</foreach>
		</if>
		<if test="sex!=null and sex!='' and sexArray ==null">
			and sex = #{sex}
		</if>
		<if test="sexArray !=null">
			and sex in
			<foreach collection="sexArray" separator="," open="(" close=")" item="sex">
				#{sex}
			</foreach>
		</if>
		<if test="orgName!=null and orgName!='' and orgNameArray ==null">
			and organ_name like concat('%',#{orgName},'%')
		</if>
		<if test="orgNameArray !=null">
			and
			(
			<foreach collection="orgNameArray" separator="or" item="orgName">
				organ_name like concat("%",#{orgName},"%")
			</foreach>
			)
		</if>
		<if test="commName!=null and commName!='' and commNameArray ==null">
			and comm_name like  concat('%', #{commName},'%')
		</if>
		<if test="commNameArray !=null">
			and (comm_name in
			<foreach collection="commNameArray" separator="or" item="commName">
				comm_name like concat("%",#{commName},"%")
			</foreach>
			)
		</if>
		<if test="phone !=null and phone !=''">
			and phone like concat ('%',#{phone},'%')
		</if>
		<if test="name !=null and name !=''">
			and name like concat ('%',#{name},'%')
		</if>
		<if test="size !=null and page !=null">
			limit #{pageStart},#{size}
		</if>
	</select>
	<select id="selectByTygGzhUserInfoDtoCount" resultType="int">
		select count(*) from wcx_employees_manage
		where deleted = 0
		<if test="identification !=null and identification!='' and identificationArray ==null">
			and identification = #{identification}
		</if>
		<if test="identificationArray !=null">
			and identification in
			<foreach collection="identificationArray" separator="," open="(" close=")" item="identification">
				#{identification}
			</foreach>
		</if>
		<if test="sex!=null and sex!='' and sexArray ==null">
			and sex = #{sex}
		</if>
		<if test="sexArray !=null">
			and sex in
			<foreach collection="sexArray" separator="," open="(" close=")" item="sex">
				#{sex}
			</foreach>
		</if>
		<if test="orgName!=null and orgName!='' and orgNameArray ==null">
			and organ_name like concat('%',#{orgName},'%')
		</if>
		<if test="orgNameArray !=null">
			and
			(
			<foreach collection="orgNameArray" separator="or" item="orgName">
				organ_name like concat("%",#{orgName},"%")
			</foreach>
			)
		</if>
		<if test="commName!=null and commName!='' and commNameArray ==null">
			and comm_name like  concat('%', #{commName},'%')
		</if>
		<if test="commNameArray !=null">
			and (comm_name in
			<foreach collection="commNameArray" separator="or" item="commName">
				comm_name like concat("%",#{commName},"%")
			</foreach>
			)
		</if>
		<if test="phone !=null and phone !=''">
			and phone like concat ('%',#{phone},'%')
		</if>
		<if test="name !=null and name !=''">
			and name like concat ('%',#{name},'%')
		</if>
	</select>

	<select id="selectAllByIds" resultMap="BaseResultMap">
		select * from wcx_employees_manage
		where id in
		<foreach collection="ids" separator="," close=")" open="(" item="id">
			#{id}
		</foreach>
		and deleted = 0
		and status =1
	</select>
	<select id="queryPublishFile" resultType="cn.com.countrygarden.bi.main.dto.experience.WcxPublishFileDto">
 SELECT
            f.id id,
            f.publish_url publishUrl,
            f.publish_type publishType,
            f.publish_no publishNo
        FROM
            wcx_publish_file f
        WHERE 1 = 1
            AND f.deleted = 0
            AND f.publish_no = #{publishNo}
	</select>
	<select id="selectByAccessToken" resultMap="BaseResultMap">
		select * from wcx_employees_manage where access_token=#{accessToken} and status=1 and effective=1 and deleted = 0
	</select>
	<select id="selectAllOrByPhoneBy0" resultMap="BaseResultMap">
			select * from wcx_employees_manage where  status=1 and effective=1 and deleted = 0 and (tyg_certificate_number is null or tyg_certificate_number = "")
		<if test="identification!=null and identification!=''">
			AND identification = #{identification}
		</if>
	</select>
	<select id="selectMax" resultType="string">
		SELECT max(tyg_certificate_number) num FROM  wcx_employees_manage where  status=1 and effective=1 and deleted = 0
		<if test="identification!=null and identification!=''">
			AND identification = #{identification}
		</if>
	</select>
	<update id="updateTygCertificateNumberByPhoen" parameterType="cn.com.countrygarden.bi.main.model.experience.WcxEmployeesManage">
		update wcx_employees_manage set tyg_certificate_number=#{tygCertificateNumber}
		where phone=#{phone}
	</update>
	<select id="selectByPhoneAndIdentification" resultMap="BaseResultMap">
		select * from wcx_employees_manage where phone=#{phone} and deleted = 0 and identification=#{identification}
		and (tyg_certificate_number is null or tyg_certificate_number = "")
	</select>

	<select id="selectCountSourceById" resultType="int">
		select IFNUll(sum(point),0) point from wcx_publish_message e where e.deleted=0
	    and e.phone =(select phone from wcx_publish_message where id=#{id})
	</select>
	<select id="selectSourceById" resultType="int">
		select IFNUll(point,0) point from wcx_publish_message e where e.deleted=0
	    and id=#{id}
	</select>
	<select id="queryByPhoneOrderByLastUpdate" resultMap="BaseResultMap">
		SELECT * FROM wcx_employees_manage
		WHERE
		deleted = 0
		AND phone = #{phone}
		<if test="identification!=null and identification!=''">
			AND identification = #{identification}
		</if>
		order by last_updated
	</select>
</mapper>