<?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.base.org.dao.QyuserMapper">

	<resultMap id="resultMap" type="com.cftech.base.org.model.Qyuser">
		<id column="id" property="id" />
		<result column="name" property="name" />
		<result column="qrcode" property="qrcode" />
		<result column="avatar" property="avatar" />
		<result column="mobile" property="mobile" />
		<result column="email" property="email" />
		<result column="role" property="role" />
		<result column="store_id" property="storeId" />
		<result column="store_name" property="storeName" />
		<result column="wxno" property="wxno" />
		<result column="gender" property="gender" />
		<result column="accounts_id" property="accountsId" />
		<result column="position_id" property="positionId" />
		<result column="wxuser_id" property="wxuserId" />
		<result column="org_id" property="orgId" />
		<result column="tag_ids" property="tagIds" />
		<result column="status" property="status" />
		<result column="create_time" property="createTime" />
		<result column="update_time" property="updateTime" />
		<result column="description" property="description" />
		<result column="del_flag" property="delFlag" />
		<result column="create_by" property="createBy" />
		<result column="update_by" property="updateBy" />
		<result column="is_ver" property="isVer" />
	</resultMap>

	<resultMap id="resultMapVO" type="com.cftech.base.org.model.vo.QyuserVO">
		<id column="id" property="id" />
		<result column="avatar" property="avatar" />
		<result column="qrcode" property="qrcode" />
		<result column="role" property="role" />
		<result column="position_name" property="positionName" />
		<result column="store_name" property="storeName" />
		<result column="org_name" property="orgName" />
		<result column="name" property="name" />
		<result column="mobile" property="mobile" />
		<result column="wxno" property="wxno" />
		<result column="email" property="email" />
		<result column="wxuser_id" property="wxuserId" />
		<result column="is_ver" property="isVer" />
	</resultMap>

	<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>

	<sql id="sqlColumns">
		id, name, qrcode, avatar, mobile, email, role, wxno, gender, accounts_id,
		position_id, store_id, store_name, wxuser_id, org_id, tag_ids, status, create_time,
		update_time, description, del_flag, create_by, update_by,is_ver
	</sql>

    <sql id="sqlColumnsT">
		t.id, t.name, t.qrcode, t.avatar, t.mobile, t.email,t.role,  t.wxno, t.gender, t.accounts_id,
		t.position_id, t.store_id,  t.store_name, t.wxuser_id, t.org_id, t.tag_ids, t.status, t.create_time,
		t.update_time, t.description, t.del_flag, t.create_by, t.update_by,is_ver
	</sql>

	<insert id="save" parameterType="com.cftech.base.org.model.Qyuser"
		keyProperty="id" useGeneratedKeys="true">
		insert into t_qyuser
		(
		id, name, qrcode,
		avatar, mobile, email, role,
		wxno, gender, accounts_id, position_id,store_id,store_name,
		wxuser_id, org_id, tag_ids,
		status, create_time, update_time,
		description, del_flag, create_by,
		update_by,is_ver
		)
		values
		(
		#{id,
		jdbcType=BIGINT}, #{name, jdbcType=VARCHAR},#{qrcode, jdbcType=VARCHAR},
		#{avatar,
		jdbcType=VARCHAR}, #{mobile, jdbcType=VARCHAR},
		#{email,
		jdbcType=VARCHAR}, #{role,
		jdbcType=VARCHAR}, #{wxno, jdbcType=VARCHAR}, #{gender,
		jdbcType=VARCHAR}, #{accountsId, jdbcType=BIGINT},
		#{positionId,
		jdbcType=BIGINT}, #{storeId,
		jdbcType=BIGINT}, #{storeName, jdbcType=VARCHAR},#{wxuserId, jdbcType=VARCHAR}, #{orgId,
		jdbcType=BIGINT}, #{tagIds, jdbcType=VARCHAR},
		#{status,
		jdbcType=VARCHAR}, now(), now(), #{description, jdbcType=VARCHAR},
		#{delFlag, jdbcType=TINYINT}, #{createBy, jdbcType=BIGINT},
		#{updateBy, jdbcType=BIGINT},#{isVer, jdbcType=VARCHAR}
		)
	</insert>

	<select id="fetchById" parameterType="java.lang.Long" resultMap="resultMap">
		SELECT
		t.id, t.name, t.qrcode, t.avatar, t.mobile, t.email, t.role, t.wxno, t.gender, t.accounts_id,
		t.position_id, t.store_id, t.store_name, t.wxuser_id, t.org_id, t.tag_ids, t.status, t.create_time,
		t.update_time, t.description, t.del_flag, t.create_by, t.update_by,u.username loginName,is_ver
		FROM t_qyuser t
		left join user u on u.userid=t.id
		WHERE t.id=#{id}
	</select>

	<select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
		SELECT COUNT(1) FROM t_qyuser t
		left join t_orgunit o on o.id = t.org_id
		left join user u on u.userid=t.id
		<include refid="sqlWhere" />
        <if test="orgId > 0">
            and instr(o.parent_ids,',${orgId},')>0
		</if>
	</select>

	<select id="fetchSearchByPage" parameterType="java.util.Map"
		resultMap="resultMap">
		SELECT
		t.id, t.name, t.qrcode, t.avatar, t.mobile, t.email, t.role, t.wxno, t.gender, t.accounts_id,
		t.position_id, t.store_id, t.store_name, t.wxuser_id, t.org_id, t.tag_ids, t.status, t.create_time,
		t.update_time, t.description, t.del_flag, t.create_by, t.update_by,u.username loginName,is_ver
		FROM t_qyuser t
		left join t_orgunit o on o.id = t.org_id
		left join user u on u.userid=t.id
		<include refid="sqlWhere" />
        <if test="orgId > 0">
            and instr(o.parent_ids,',${orgId},')>0
		</if>
		<if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
		<if test="limit>0">limit #{offset},#{limit}</if>
	</select>

	<update id="update" parameterType="com.cftech.base.org.model.Qyuser">
		update t_qyuser
		<set>
			<if test="id != null">
				id = #{id, jdbcType=BIGINT},
			</if>
			<if test="name != null">
				name = #{name, jdbcType=VARCHAR},
			</if>
			<if test="qrcode != null">
				qrcode = #{qrcode, jdbcType=VARCHAR},
			</if>
			<if test="avatar != null">
				avatar = #{avatar, jdbcType=VARCHAR},
			</if>
			<if test="mobile != null">
				mobile = #{mobile, jdbcType=VARCHAR},
			</if>
			<if test="email != null">
				email = #{email, jdbcType=VARCHAR},
			</if>
			<if test="role != null">
				role = #{role, jdbcType=VARCHAR},
			</if>
			<if test="wxno != null">
				wxno = #{wxno, jdbcType=VARCHAR},
			</if>
			<if test="gender != null">
				gender = #{gender, jdbcType=VARCHAR},
			</if>
			<if test="accountsId != null">
				accounts_id = #{accountsId, jdbcType=BIGINT},
			</if>
			<if test="positionId != null">
				position_id = #{positionId, jdbcType=BIGINT},
			</if>
			<if test="storeId != null">
				store_id = #{storeId, jdbcType=BIGINT},
			</if>
			<if test="storeName != null">
				store_name = #{storeName, jdbcType=VARCHAR},
			</if>
			<if test="wxuserId != null">
				wxuser_id = #{wxuserId, jdbcType=VARCHAR},
			</if>
			<if test="orgId != null">
				org_id = #{orgId, jdbcType=BIGINT},
			</if>
			<if test="tagIds != null">
				tag_ids = #{tagIds, jdbcType=VARCHAR},
			</if>
			<if test="status != null">
				status = #{status, jdbcType=VARCHAR},
			</if>
			<if test="createTime != null">
				create_time = #{createTime, jdbcType=TIMESTAMP},
			</if>
			<if test="updateTime != null">
				update_time=now(),
			</if>
			<if test="description != null">
				description = #{description, jdbcType=VARCHAR},
			</if>
			<if test="delFlag != null">
				del_flag = #{delFlag, jdbcType=TINYINT},
			</if>
			<if test="createBy != null">
				create_by = #{createBy, jdbcType=BIGINT},
			</if>
			<if test="updateBy != null">
				update_by = #{updateBy, jdbcType=BIGINT},
			</if>
			<if test="isVer != null">
				is_ver = #{isVer, jdbcType=VARCHAR},
			</if>
		</set>
		where id=#{id,jdbcType=BIGINT}
	</update>

	<update id="delete" parameterType="java.lang.Long">
		update t_qyuser set del_flag=1
		where id=#{id,jdbcType=BIGINT}
	</update>

	<select id="fetchSearchVOByPage" parameterType="java.util.Map"
		resultMap="resultMapVO">
		SELECT t.id id, t.qrcode qrcode, t.avatar avatar,t.role role,  p.name position_name, s.name store_name,
		o.org_name org_name,
		t.name name, t.mobile mobile, t.wxno wxno, t.email email,t.description,
		t.wxuser_id
		wxuser_id,
		u.username loginName,(select count(1) from wx_mp_fanss fanss where fanss.recommender=t.id and fanss.mpaccountid=t.accounts_id and fanss.delflag=0) fanssCount,is_ver
		FROM t_qyuser t
		left join t_position p on p.id =t.position_id
        left join t_dc_store s on s.id = t.store_id
		left join t_orgunit o on o.id = t.org_id
		left join user u on u.userid=t.id
		<include refid="sqlWhere" />
        <if test="orgId > 0">
		and instr(o.parent_ids,',${orgId},')>0
		</if>
		
		<if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
		<if test="limit>0">limit #{offset},#{limit}</if>
	</select>

	<update id="changeStatus" parameterType="java.util.Map">
		update t_qyuser set status=#{status, jdbcType=VARCHAR}
		<include refid="sqlWhere" />
	</update>

	<update id="updateStoreInfo" parameterType="java.util.Map">
		update t_qyuser
		set role=#{role, jdbcType=VARCHAR}, store_id = #{storeId, jdbcType=BIGINT}, store_name = #{storeName, jdbcType=VARCHAR}
		where id=#{id,jdbcType=BIGINT}

	</update>
	<update id="batchUpdate" parameterType="java.util.Map">
		update t_qyuser
		<set>
			<if test="id != null">
				id = #{id, jdbcType=BIGINT},
			</if>
			<if test="name != null">
				name = #{name, jdbcType=VARCHAR},
			</if>
			<if test="avatar != null">
				avatar = #{avatar, jdbcType=VARCHAR},
			</if>
			<if test="mobile != null">
				mobile = #{mobile, jdbcType=VARCHAR},
			</if>
			<if test="email != null">
				email = #{email, jdbcType=VARCHAR},
			</if>
			<if test="role != null">
				role = #{role, jdbcType=VARCHAR},
			</if>
			<if test="wxno != null">
				wxno = #{wxno, jdbcType=VARCHAR},
			</if>
			<if test="gender != null">
				gender = #{gender, jdbcType=VARCHAR},
			</if>
			<if test="accountsId != null">
				accounts_id = #{accountsId, jdbcType=BIGINT},
			</if>
			<if test="positionId != null">
				position_id = #{positionId, jdbcType=BIGINT},
			</if>
			<if test="storeId != null">
				store_id = #{storeId, jdbcType=BIGINT},
			</if>
			<if test="storeName != null">
				store_name = #{storeName, jdbcType=VARCHAR},
			</if>
			<if test="wxuserId != null">
				wxuser_id = #{wxuserId, jdbcType=VARCHAR},
			</if>
			<if test="orgId != null">
				org_id = #{orgId, jdbcType=BIGINT},
			</if>
			<if test="tagIds != null">
				tag_ids = #{tagIds, jdbcType=VARCHAR},
			</if>
			<if test="status != null">
				status = #{status, jdbcType=VARCHAR},
			</if>
			<if test="createTime != null">
				create_time = #{createTime, jdbcType=TIMESTAMP},
			</if>
			<if test="updateTime != null">
				update_time=now(),
			</if>
			<if test="description != null">
				description = #{description, jdbcType=VARCHAR},
			</if>
			<if test="delFlag != null">
				del_flag = #{delFlag, jdbcType=TINYINT},
			</if>
			<if test="createBy != null">
				create_by = #{createBy, jdbcType=BIGINT},
			</if>
			<if test="updateBy != null">
				update_by = #{updateBy, jdbcType=BIGINT},
			</if>
			<if test="isVer != null">
				is_ver = #{isVer, jdbcType=VARCHAR},
			</if>
		</set>
		<include refid="sqlWhere" />
	</update>

	<!-- 根据人员ID，查询此人所有下级部门及人员 -->
	<select id="getOrgSubordinateUser" parameterType="java.util.Map"
		resultType="com.cftech.base.org.model.vo.QyOrgUserVO">
		select a.id,a.name,a.type from(
		(select id,org_name name,'0' type from
		t_orgunit org
		where parent_id = #{orgId}
		and org.del_flag = 0
		and org.accounts_id =
		#{accountsId}
		<if test="name!=null and name!=''">
			and org.org_name like '%${name}%'
		</if>
		)
		UNION ALL
		(
		select qyuser.id,qyuser.name,'1' type
		from t_qyuser qyuser
		left join t_position posi on posi.id = qyuser.position_id
		where
		org_id=#{orgId} and qyuser.id!=#{userId} and
		instr(posi.parent_ids,',${positionId},')>0
		and posi.id !=#{positionId}
		and qyuser.accounts_id = #{accountsId}
		and qyuser.del_flag=0
		<if test="name!=null and name!=''">
			and qyuser.name like '%${name}%'
		</if>
		) )a
		order by a.type asc,a.id desc
		<if test="limit>0">limit #{offset},#{limit}</if>
	</select>

	<!-- 根据人员ID，查询此人所有下级人员 -->
	<select id="getSubordinateUser" parameterType="java.util.Map"
		resultMap="resultMapVO">
		select qyuser.* from t_qyuser qyuser
		left join t_position p
		on p.id =
		qyuser.position_id
		left join t_orgunit o on o.id =
		qyuser.org_id
		where
		instr(o.parent_ids,',${orgId},') > 0
		and
		instr(p.parent_ids,',${positionId},') > 0
		and
		p.id!=#{positionId}
		and
		qyuser.id != #{userId}
		and
		qyuser.del_flag = 0 and p.del_flag=0 and
		o.del_flag=0
		and
		qyuser.accounts_id = #{accountsId}
		<if test="name!=null and name !='' ">and qyuser.name like '%${name}%'</if>
		<if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
		<if test="limit>0">limit #{offset},#{limit}</if>
	</select>

	<!-- 根据选择的部门，查询出所有的人员 -->
	<select id="getOrgUserList" parameterType="java.util.Map"
		resultMap="resultMap">
		SELECT
		qyuser.id,
		qyuser. NAME
		FROM
		t_qyuser qyuser
		LEFT JOIN t_position posi ON posi.id = qyuser.position_id
		left join t_orgunit org on org.id = qyuser.org_id
		WHERE
		(
		1=1 and
		<foreach item="item" index="index" open="(" close=")" collection="orgIds"  separator="or" >
		instr(org.parent_ids,'${item}') > 0
		</foreach>
		AND instr(posi.parent_ids, ',${positionId},') > 0
		AND posi.id != ${positionId}
		AND qyuser.accounts_id = #{accountsId}
		and qyuser.del_flag = 0
		)
	</select>  

	<update id="appendTagids" parameterType="java.util.Map">
		update t_qyuser set tag_ids = concat(IFNULL(tag_ids,''), #{tagIds,
		jdbcType=VARCHAR})
		<include refid="sqlWhere" />
	</update>

	<!-- 根据标签id，查找所有的标签用户 -->
	<select id="findUsersByTagIds" parameterType="java.util.Map"
			resultMap="resultMap">
		SELECT
		<include refid="sqlColumns"/>
		FROM
		t_qyuser
		WHERE
		1=1
		AND
		del_flag = #{delFlag}
		AND
		accounts_id = #{accountsId}
		AND
		tag_ids REGEXP '#{tagIds}';
	</select>
	
	
	<update id="updateUserTerminal" parameterType="java.util.Map">
	update wx_qy_terminal set sale_account = #{wxUserId} where id in
	<foreach item="item" index="index" open="(" close=")" collection="terminals"  separator="," >
		${item}
	</foreach> 
	</update>





	<select id="getLogInfo" parameterType="java.util.Map" resultMap="resultMap">
		select  b.id,b.name,b.store_id, b.role,c.name store_name,b.qrcode,b.is_ver from user a INNER JOIN t_qyuser b on b.id = a.userid
		INNER JOIN t_dc_store c on c.id = b.store_id
		where a.id = #{id} and a.delflag = '0'
	</select>


</mapper>