<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~ Copyright 2019-2029 geekidea(https://github.com/geekidea)
  ~
  ~ Licensed under the Apache License, Version 2.0 (the "License");
  ~ you may not use this file except in compliance with the License.
  ~ You may obtain a copy of the License at
  ~
  ~     http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific language governing permissions and
  ~ limitations under the License.
  -->

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="io.geekidea.springbootplus.system.mapper.SysUserMapper">

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, username, nickname, phone, gender, head, remark, state, department_id, role_id, deleted, version, create_time, update_time
    </sql>

    <sql id="BaseQuerySelect">
        select
            u.id, u.username, u.nickname, u.phone, u.gender, u.head, u.remark,
            u.state, u.department_id, u.role_id, u.deleted, u.create_time, u.update_time,
            d.name as departmentName,
            r.name as roleName
        from sys_user u
        inner join sys_department d on u.department_id = d.id
        inner join sys_role r on u.role_id = r.id
        where u.deleted = 0
    </sql>

    <select id="getSysUserById" resultType="io.geekidea.springbootplus.system.vo.SysUserQueryVo">
        <include refid="BaseQuerySelect"/>
        and u.id = #{id}
    </select>

    <select id="getSysUserPageList" resultType="io.geekidea.springbootplus.system.vo.SysUserQueryVo">
        <include refid="BaseQuerySelect"/>
        <if test="param.departmentId != null">
            and u.department_id = #{param.departmentId}
        </if>
        <if test="param.roleId != null">
            and u.role_id = #{param.roleId}
        </if>
        <if test="param.state != null">
            and u.state = #{param.state}
        </if>
        <if test="param.createTimeStart != null">
            and date_format(u.create_time,'%Y-%m-%d') >= date_format(#{param.createTimeStart},'%Y-%m-%d')
        </if>
        <if test="param.createTimeEnd != null">
            and date_format(u.create_time,'%Y-%m-%d') &lt;= date_format(#{param.createTimeEnd},'%Y-%m-%d')
        </if>
        <if test="param.keyword != null and param.keyword != ''">
            and (
            u.username like concat('%', #{param.keyword} ,'%') or
            u.nickname like concat('%', #{param.keyword} ,'%')
            )
        </if>
        <if test="param.username != null and param.username != ''">
            and u.username like concat('%', #{param.username} ,'%')
        </if>
        <if test="param.nickname != null and param.nickname != ''">
            and u.nickname like concat('%', #{param.nickname} ,'%')
        </if>
    </select>

</mapper>
