<?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="user_type" property="userType"/>
        <result column="peak_val" property="peakVal"/>
        <result column="assigned" property="assigned"/>
        <result column="un_assigned" property="unAssigned"/>
    </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="user_type" property="userType"/>
        <result column="gender" property="gender"/>
    </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, user_type, peak_val, assigned, un_assigned
	</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, user_type, peak_val, assigned, un_assigned
	</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,store_id,store_name,
		wxuser_id, org_id, tag_ids,
		status, create_time, update_time,
		description, del_flag, create_by,
		update_by, user_type, peak_val, assigned, un_assigned
		)
		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},
		#{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}, #{userType, jdbcType=VARCHAR}, #{peakVal, jdbcType=BIGINT}, #{assigned, jdbcType=BIGINT}, #{unAssigned, jdbcType=BIGINT}
		)
	</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.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, t.user_type, t.peak_val, t.assigned, t.un_assigned
		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.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, t.user_type,
        t.peak_val, t.assigned, t.un_assigned
        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="userType != null">
                user_type = #{userType, jdbcType=VARCHAR},
            </if>
            <if test="peakVal != null">
                peak_val = #{peakVal, jdbcType=BIGINT},
            </if>
            <if test="assigned != null">
                assigned = #{assigned, jdbcType=BIGINT},
            </if>
            <if test="unAssigned != null">
                un_assigned = #{unAssigned, jdbcType=BIGINT},
            </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,
        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, t.user_type, t.gender, qrcode.ticket qrcode, t.user_type, t.peak_val, t.assigned,
        t.un_assigned
        FROM t_qyuser t
        left join t_orgunit o on o.id = t.org_id
        left join user u on u.userid = t.id
        left join t_shop_wxqrcode qrcode on qrcode.bind_id = t.id AND qrcode.del_flag = '0' AND (qrcode.type = '0' or qrcode.type = '1' or qrcode.type = '2')
        <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="userType != null">
                user_type = #{userType, jdbcType=VARCHAR},
            </if>
            <if test="peakVal != null">
                peak_val = #{peakVal, jdbcType=BIGINT},
            </if>
            <if test="assigned != null">
                assigned = #{assigned, jdbcType=BIGINT},
            </if>
            <if test="unAssigned != null">
                un_assigned = #{unAssigned, jdbcType=BIGINT},
            </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 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>

    <!-- 获取轮询的人员（药师、医生）且通过峰值排序 -->
    <select id="fetchPollingData" parameterType="java.lang.String" resultMap="resultMap">
        SELECT t.* FROM t_qyuser t
        <if test="type=0">t.user_type = '0'</if>
        <if test="type=1">t.user_type = '1'</if>
        ORDER BY t.peak_val DESC
    </select>

    <update id="updateAssigned" parameterType="com.cftech.base.org.model.Qyuser">
        update t_qyuser
        <set>
            <if test="peakVal != null">
                peak_val = #{peakVal, jdbcType=BIGINT},
            </if>
            <if test="assigned != null">
                assigned = #{assigned, jdbcType=BIGINT},
            </if>
            <if test="unAssigned != null">
                un_assigned = #{unAssigned, jdbcType=BIGINT},
            </if>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>
</mapper>