<?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.cool.store.mapper.EnterpriseUserMapper">
    <resultMap id="BaseResultMap" type="com.cool.store.entity.EnterpriseUserDO">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="user_id" jdbcType="VARCHAR" property="userId"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="mobile" jdbcType="VARCHAR" property="mobile"/>
        <result column="email" jdbcType="VARCHAR" property="email"/>
        <result column="org_email" jdbcType="VARCHAR" property="orgEmail"/>
        <result column="main_admin" jdbcType="BIT" property="mainAdmin"/>
        <result column="is_admin" jdbcType="BIT" property="isAdmin"/>
        <result column="unionid" jdbcType="VARCHAR" property="unionid"/>
        <result column="avatar" jdbcType="VARCHAR" property="avatar"/>
        <result column="jobnumber" jdbcType="VARCHAR" property="jobnumber"/>
        <result column="is_leader" jdbcType="BIT" property="isLeader"/>
        <result column="leader_dept_ids" jdbcType="VARCHAR" property="leaderDeptIds"/>
        <result column="face_url" jdbcType="VARCHAR" property="faceUrl"/>
        <result column="user_status" jdbcType="TINYINT" property="userStatus"/>
        <result column="deleted" jdbcType="BIT" property="deleted"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>
    <resultMap extends="BaseResultMap" id="ResultMapWithBLOBs" type="com.cool.store.entity.EnterpriseUserDO">
        <result column="user_region_ids" jdbcType="LONGVARCHAR" property="userRegionIds"/>
    </resultMap>
    <sql id="Base_Column_List">
        id, user_id, name, remark, mobile, email, org_email, main_admin, is_admin, unionid,
        avatar, jobnumber, is_leader, leader_dept_ids, face_url, user_status, deleted, create_time,
        update_time
    </sql>
    <sql id="Blob_Column_List">
        user_region_ids
    </sql>
    <insert id="batchInsertOrUpdate">
        <foreach collection="recordList" item="record" separator=";">
            insert into enterprise_user
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="record.userId != null">
                    user_id,
                </if>
                <if test="record.name != null">
                    name,
                </if>
                <if test="record.remark != null">
                    remark,
                </if>
                <if test="record.mobile != null">
                    mobile,
                </if>
                <if test="record.email != null">
                    email,
                </if>
                <if test="record.orgEmail != null">
                    org_email,
                </if>
                <if test="record.mainAdmin != null">
                    main_admin,
                </if>
                <if test="record.isAdmin != null">
                    is_admin,
                </if>
                <if test="record.unionid != null">
                    unionid,
                </if>
                <if test="record.avatar != null">
                    avatar,
                </if>
                <if test="record.jobnumber != null">
                    jobnumber,
                </if>
                <if test="record.isLeader != null">
                    is_leader,
                </if>
                <if test="record.leaderDeptIds != null">
                    leader_dept_ids,
                </if>
                <if test="record.faceUrl != null">
                    face_url,
                </if>
                <if test="record.userStatus != null">
                    user_status,
                </if>
                <if test="record.deleted != null">
                    deleted,
                </if>
                <if test="record.createTime != null">
                    create_time,
                </if>
                <if test="record.updateTime != null">
                    update_time,
                </if>
                <if test="record.userRegionIds != null">
                    user_region_ids,
                </if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="record.userId != null">
                    #{record.userId},
                </if>
                <if test="record.name != null">
                    #{record.name},
                </if>
                <if test="record.remark != null">
                    #{record.remark},
                </if>
                <if test="record.mobile != null">
                    #{record.mobile},
                </if>
                <if test="record.email != null">
                    #{record.email},
                </if>
                <if test="record.orgEmail != null">
                    #{record.orgEmail},
                </if>
                <if test="record.mainAdmin != null">
                    #{record.mainAdmin},
                </if>
                <if test="record.isAdmin != null">
                    #{record.isAdmin},
                </if>
                <if test="record.unionid != null">
                    #{record.unionid},
                </if>
                <if test="record.avatar != null">
                    #{record.avatar},
                </if>
                <if test="record.jobnumber != null">
                    #{record.jobnumber},
                </if>
                <if test="record.isLeader != null">
                    #{record.isLeader},
                </if>
                <if test="record.leaderDeptIds != null">
                    #{record.leaderDeptIds},
                </if>
                <if test="record.faceUrl != null">
                    #{record.faceUrl},
                </if>
                <if test="record.userStatus != null">
                    #{record.userStatus},
                </if>
                <if test="record.deleted != null">
                    #{record.deleted},
                </if>
                <if test="record.createTime != null">
                    #{record.createTime},
                </if>
                <if test="record.updateTime != null">
                    #{record.updateTime},
                </if>
                <if test="record.userRegionIds != null">
                    #{record.userRegionIds},
                </if>
            </trim>
            ON DUPLICATE KEY UPDATE user_id = values(user_id), name = values(name), remark = values(remark), mobile = values(mobile), email = values(email), org_email = values(org_email)
            , main_admin = values(main_admin), is_admin = values(is_admin), unionid = values(unionid), avatar = values(avatar), jobnumber = values(jobnumber)
            , face_url = values(face_url), user_status = values(user_status), user_region_ids = values(user_region_ids), deleted = values(deleted)
            <if test="record.isLeader != null">
                , is_leader = values(is_leader)
            </if>
            <if test="record.leaderDeptIds != null">
                , leader_dept_ids = values(leader_dept_ids)
            </if>
        </foreach>
    </insert>
    <update id="updateByPrimaryKeySelective">
        update enterprise_user
        <set>
            <if test="userId != null">
                user_id = #{userId},
            </if>
            <if test="name != null">
                name = #{name},
            </if>
            <if test="remark != null">
                remark = #{remark},
            </if>
            <if test="mobile != null">
                mobile = #{mobile},
            </if>
            <if test="email != null">
                email = #{email},
            </if>
            <if test="orgEmail != null">
                org_email = #{orgEmail},
            </if>
            <if test="mainAdmin != null">
                main_admin = #{mainAdmin},
            </if>
            <if test="isAdmin != null">
                is_admin = #{isAdmin},
            </if>
            <if test="unionid != null">
                unionid = #{unionid},
            </if>
            <if test="avatar != null">
                avatar = #{avatar},
            </if>
            <if test="jobnumber != null">
                jobnumber = #{jobnumber},
            </if>
            <if test="isLeader != null">
                is_leader = #{isLeader},
            </if>
            <if test="record.leaderDeptIds != null">
                leader_dept_ids = #{record.leaderDeptIds},
            </if>
            <if test="faceUrl != null">
                face_url = #{faceUrl},
            </if>
            <if test="userStatus != null">
                user_status = #{userStatus},
            </if>
            <if test="deleted != null">
                deleted = #{deleted},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
            <if test="userRegionIds != null">
                user_region_ids = #{userRegionIds},
            </if>
        </set>
        where id = #{id}
    </update>

    <select id="getUserInfoById" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>,
        <include refid="Blob_Column_List"/>
        from
        enterprise_user
        where
        user_id = #{userId}
    </select>

    <select id="getUserInfoByUserIds" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>,
        <include refid="Blob_Column_List"/>
        from
        enterprise_user
        <where>
            <if test="userIdList !=null and userIdList.size>0">
                <foreach collection="userIdList" item="userId" open="and user_id in (" close=")" separator=",">
                    #{userId}
                </foreach>
            </if>
        </where>
    </select>

    <update id="deleteUser">
        update enterprise_user set deleted = 1 where user_id not in <foreach collection="excludeUserIds" open="(" close=")" separator="," item="userId">#{userId}</foreach>
    </update>

    <update id="deleteUserByUserId">
        update enterprise_user set deleted = 1 where user_id = #{userId}
    </update>
    
    <select id="searchUserByRegionIdsAndKeyword" resultMap="BaseResultMap">
        select
            user_id, name, mobile
        from
            enterprise_user
        <where>
            deleted = 0
            and <foreach collection="regionIds" item="regionId" separator=" or " open="(" close=")">user_region_ids like concat('%', #{regionId}, '%')</foreach>
            <if test="keyword != null">
                and (name like concat("%", #{keyword}, "%") or mobile like concat("%", #{keyword}, "%"))
            </if>
            <if test="leaderRegionIds != null and leaderRegionIds.size()>0">
                and <foreach collection="leaderRegionIds" item="regionId" separator=" or " open="(" close=")">user_region_ids like concat('%', #{regionId}, '%')</foreach>
            </if>
        </where>
    </select>

    <select id="getUserCountByRegionId" resultType="integer">
        select count(1) from enterprise_user where deleted = 0 and user_region_ids like concat("%", #{regionId}, "%")
    </select>

    <select id="getUserListByDeptLeader" resultMap="BaseResultMap">
        select user_id, leader_dept_ids from enterprise_user where leader_dept_ids like concat("%", #{regionId}, "%") and deleted = 0
    </select>

    <select id="getUserListByDeptLeaders" resultMap="BaseResultMap">
        select
            user_id, name, mobile
        from
            enterprise_user
        where
            deleted = 0  and <foreach collection="regionIds" separator="or" open="(" close=")" item="regionId"> leader_dept_ids like concat("%", #{regionId}, "%") </foreach>
    </select>

    <select id="getUserListByRegionIds" resultMap="BaseResultMap">
        select
            user_id, name, mobile
        from
            enterprise_user
        where
            deleted = 0 and is_leader = 0 and <foreach collection="regionIds" separator="or" open="(" close=")" item="regionId"> user_region_ids like concat("%", #{regionId}, "%") </foreach>
        ORDER BY
        CASE WHEN jobnumber='' OR jobnumber IS NULL THEN 1 ELSE 0 END,
        SUBSTR(jobnumber,1,1),
        CAST(SUBSTR(jobnumber,2) AS UNSIGNED) ASC
    </select>
    <select id="selectByMobile" resultType="java.lang.String">
        SELECT  user_id FROM  enterprise_user WHERE  mobile =#{mobile}  LIMIT 1
    </select>
</mapper>