<?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.ym.mec.auth.dal.dao.SysUserDao">

    <resultMap type="com.ym.mec.auth.api.entity.SysUser" id="SysUser">
        <result column="id_" property="id"/>
        <result column="tenant_id_" property="tenantId"/>
        <result column="username_" property="username"/>
        <result column="real_name_" property="realName"/>
        <result column="password_" property="password"/>
        <result column="salt_" property="salt"/>
        <result column="phone_" property="phone"/>
        <result column="avatar_" property="avatar"/>
        <result column="organ_id_" property="organId"/>
        <result column="create_time_" property="createTime"/>
        <result column="update_time_" property="updateTime"/>
        <result column="lock_flag_" property="lockFlag"/>
        <result column="del_flag_" property="delFlag" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result column="wx_openid_" property="wxOpenid"/>
        <result column="qq_openid_" property="qqOpenid"/>
        <result column="user_type_" property="userType"/>
        <result column="gender_" property="gender"/>
        <result column="nation_" property="nation"/>
        <result column="birthdate_" property="birthdate"/>
        <result column="email_" property="email"/>
        <result column="im_token_" property="imToken"/>
        <result column="id_card_no_" property="idCardNo"/>
        <result column="wechat_id_" property="wechatId"/>
        <result column="is_super_admin_" property="isSuperAdmin"/>
        <result column="current_grade_" property="currentGrade"/>
        <result column="current_grade_num_" property="currentGradeNum"/>
        <result column="current_class_" property="currentClass"/>
        <result column="certificate_type_" property="certificateType"/>
    </resultMap>

    <!-- 查询条件 -->
    <sql id="queryCondition">
        <if test="userStartDate != null">
            and create_time_ &gt;= #{createStartDate}
        </if>
        <if test="userEndDate != null">
            and create_time_ &lt;= #{createEndDate}
        </if>
        <if test="userType != null">
            and user_type_ = #{userType}
        </if>
        <if test="organId != null">
            and organ_id_ = #{organId}
        </if>
        <if test="tenantId != null">
            and tenant_id_ = #{tenantId}
        </if>
    </sql>

    <!-- 根据主键查询一条记录 -->
    <select id="get" resultMap="SysUser">
		SELECT su.*,s.current_class_,s.current_grade_num_
        FROM sys_user su LEFT JOIN student s ON su.id_ = s.user_id_
        WHERE su.id_ = #{id}
	</select>

    <!-- 全查询 -->
    <select id="findAll" resultMap="SysUser">
		SELECT * FROM sys_user where tenant_id_ = #{tenantId} ORDER BY id_
	</select>

    <!-- 向数据库增加一条记录 -->
    <insert id="insert" parameterType="com.ym.mec.auth.api.entity.SysUser" useGeneratedKeys="true" keyColumn="id"
            keyProperty="id">
        INSERT INTO sys_user
        (tenant_id_,is_super_admin_,im_token_,id_,username_,salt_,phone_,avatar_,organ_id_,create_time_,update_time_,wx_openid_,qq_openid_,user_type_,
        gender_,nation_,birthdate_,email_,id_card_no_,wechat_id_,real_name_,certificate_type_)
        VALUES(#{tenantId},#{isSuperAdmin},#{imToken},#{id},#{username},#{salt},#{phone},#{avatar},#{organId},now(),now(),#{wxOpenid},#{qqOpenid},#{userType},
        #{gender},#{nation},#{birthdate},#{email},#{idCardNo},#{wechatId},#{realName},#{certificateType})
    </insert>
    <insert id="insertTeacher">
        INSERT INTO teacher
        (id_,organ_id_,create_time_,update_time_,tenant_id_)VALUES(#{userId},#{lesseeOrganId},NOW(),NOW(),#{tenantId})
    </insert>
    <insert id="insertSysTenantAccount">
        INSERT INTO sys_tenant_account
        (user_id_,available_minutes_,frozen_minutes_,status_,create_time_,update_time_)
		VALUES(#{userId},0,0,1,NOW(),NOW())
    </insert>
    <insert id="saveStudent">
        INSERT INTO student (user_id_,create_time_,update_time_,service_tag_,operating_tag_,tenant_id_) VALUES(#{userId},NOW(),NOW(),0,0,#{tenantId})
    </insert>

    <!-- 根据主键查询一条记录 -->
    <update id="update" parameterType="com.ym.mec.auth.api.entity.SysUser">
        UPDATE sys_user
        <set>
            <if test="delFlag != null">
                del_flag_ = #{delFlag, typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler},
            </if>
            <if test="wxOpenid != null and wxOpenid != ''">
                wx_openid_ = #{wxOpenid},
            </if>
            <if test="avatar != null and avatar != ''">
                avatar_ = #{avatar},
            </if>
            <if test="email != null and email != ''">
                email_ = #{email},
            </if>
            <if test="gender != null">
                gender_ = #{gender},
            </if>
            <if test="salt != null and salt != ''">
                salt_ = #{salt},
            </if>
            <if test="username != null and username != ''">
                username_ = #{username},
            </if>
            <if test="userType != null and userType != ''">
                user_type_ = #{userType},
            </if>
            <if test="updateTime != null">
                update_time_ = NOW(),
            </if>
            <if test="lockFlag != null">
                lock_flag_ = #{lockFlag},
            </if>
            <if test="birthdate != null">
                birthdate_ = #{birthdate},
            </if>
            <if test="phone != null and phone != ''">
                phone_ = #{phone},
            </if>
            <if test="qqOpenid != null and qqOpenid != ''">
                qq_openid_ = #{qqOpenid},
            </if>
            <if test="nation != null and nation != ''">
                nation_ = #{nation},
            </if>
            <if test="organId != null">
                organ_id_ = #{organId},
            </if>
            <if test="imToken != null and imToken != ''">
                im_token_ = #{imToken},
            </if>
            <if test="idCardNo != null and idCardNo != ''">
                id_card_no_ = #{idCardNo},
            </if>
            <if test="password != null and password != ''">
                password_ = #{password},
            </if>
            <if test="wechatId != null and wechatId != ''">
                wechat_id_ = #{wechatId},
            </if>
            <if test="realName != null and realName != ''">
                real_name_ = #{realName},
            </if>
            <if test="idCardNo != null and idCardNo != ''">
                id_card_no_ = #{idCardNo},
            </if>
            <if test="isSuperAdmin != null">
                is_super_admin_ = #{isSuperAdmin},
            </if>
            <if test="certificateType != null">
                certificate_type_ = #{certificateType},
            </if>
        </set>
        WHERE id_ = #{id} and tenant_id_ = #{tenantId}
    </update>
    <update id="updatePassword">
        UPDATE sys_user SET password_ = #{password} WHERE phone_ = #{mobile}
    </update>
    <update id="refreshImToken">
        UPDATE sys_user SET im_token_ = #{imToken},update_time_ = NOW() WHERE id_ = #{userId}
    </update>

    <!-- 根据主键删除一条记录 -->
    <delete id="delete">
		UPDATE sys_user SET del_flag_ = 1 WHERE id_ = #{id}
	</delete>

    <!-- 分页查询 -->
    <select id="queryPage" resultMap="SysUser" parameterType="map">
        SELECT * FROM sys_user
        <include refid="queryCondition"/>
        <include refid="global.orderby"/>
        <include refid="global.limit"/>
    </select>

    <!-- 查询当前表的总记录数 -->
    <select id="queryCount" resultType="int">
        SELECT COUNT(*) FROM sys_user
        <include refid="queryCondition"/>
    </select>

    <select id="queryByUsername" resultMap="SysUser">
		select * from sys_user where username_ = #{username} OR phone_ = #{username} LIMIT 1 FOR UPDATE
	</select>

    <select id="queryByPhone" resultMap="SysUser">
		select * from sys_user where phone_ = #{phone} OR username_ = #{phone} LIMIT 1
	</select>

    <select id="queryLockByPhone" resultMap="SysUser">
		select * from sys_user where phone_ = #{phone} OR username_ = #{phone} LIMIT 1 FOR UPDATE
	</select>
    <resultMap id="ImUserModel" type="com.ym.mec.common.entity.ImUserModel">
        <result column="id_" property="id"/>
        <result property="name" column="username_"/>
        <result property="portrait" column="avatar_"/>
    </resultMap>
    <select id="getBasic" resultMap="ImUserModel">
        select CASE WHEN user_type_ LIKE '%TEACHER%' OR user_type_ LIKE '%SYSTEM%' THEN real_name_ ELSE username_ END username_,id_,avatar_
        from sys_user where id_ = #{userId} LIMIT 1
    </select>
    
    <select id="queryByStatus" resultMap="SysUser">
        select * from sys_user where lock_flag_ = #{status} and tenant_id_ = #{tenantId}
    </select>
    <select id="findUserBySalt" resultMap="SysUser">
        SELECT * FROM sys_user WHERE salt_ IS NOT NULL AND salt_ = 'replace' AND user_type_ LIKE CONCAT('%',#{userType},'%') LIMIT 1
    </select>
    <select id="getTeacherOrganId" resultType="java.lang.Integer">
        SELECT organ_id_ FROM teacher WHERE id_ = #{userId}
    </select>
    <select id="queryByPhoneAndClient" resultMap="SysUser">
        select * from sys_user where (phone_ = #{phone} OR username_ = #{phone}) AND user_type_ LIKE CONCAT('%',#{client},'%') LIMIT 1 FOR UPDATE
    </select>

    <select id="queryLikeByPhoneOrName" resultMap="SysUser">
        select *
        from sys_user
        where tenant_id_ = #{tenantId}
          and lock_flag_ = 0
          and del_flag_ = 0
          and (
                    phone_ like CONCAT('%', #{search}, '%')
                or real_name_ like CONCAT('%', #{search}, '%')
            )
    </select>

    <select id="queryEmployeeList" resultMap="SysUser">
        select  su.*
        from employee e
        left join sys_user su on su.id_ = e.user_id_
        <where>
            su.del_flag_ = 0
            <if test="createStartDate != null and createStartDate != ''">
                and #{createStartDate} &lt;= e.create_time_
            </if>
            <if test="createEndDate != null and createEndDate != ''">
                and #{createEndDate} &gt; e.create_time_
            </if>
        </where>
    </select>

</mapper>
