<?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.ejweb.modules.sys.dao.SysUserDao">

    <!-- <resultMap id="userResult" type="User">
        <id property="id" column="id" />
        <result property="company.id" column="company.id" />
        <result property="office.id" column="office.id" />
        <result property="loginName" column="login_name" />
        <result property="password" column="password" />
        <result property="no" column="no" />
        <result property="name" column="name" />
        <result property="email" column="email" />
        <result property="phone" column="phone" />
        <result property="mobile" column="mobile" />
        <result property="userType" column="user_type" />
        <result property="loginIp" column="login_ip" />
        <result property="loginDate" column="login_date" />
        <result property="remarks" column="remarks" />
        <result property="loginFlag" column="loginFlag" />
        <result property="photo" column="photo" />
        <result property="createBy.id" column="createBy.id" />
        <result property="createDate" column="create_date" />
        <result property="updateBy.id" column="updateBy.id" />
        <result property="updateDate" column="update_date" />
        <result property="company.name" column="company.name" />
        <result property="company.parentId" column="company.parentId" />
        <result property="company.parentIds" column="company.parentIds" />
        <result property="company.area.id" column="company.area.id" />
        <result property="company.area.name" column="company.area.name" />
        <result property="company.area.parentId" column="company.area.parentId" />
        <result property="company.area.parentIds" column="company.area.parentIds" />
        <result property="company.primaryPerson.id" column="company.primaryPerson.id" />
        <result property="company.primaryPerson.name" column="company.primaryPerson.name" />
        <result property="company.deputyPerson.id" column="company.deputyPerson.id" />
        <result property="company.deputyPerson.name" column="company.deputyPerson.name" />
        <result property="office.name" column="office.name" />
        <result property="office.parentId" column="office.parentId" />
        <result property="office.parentIds" column="office.parentIds" />
        <result property="office.area.id" column="office.area.id" />
        <result property="office.area.name" column="office.area.name" />
        <result property="office.area.parentId" column="office.area.parentId" />
        <result property="office.area.parentIds" column="office.area.parentIds" />
        <result property="office.primaryPerson.id" column="office.primaryPerson.id" />
        <result property="office.primaryPerson.name" column="office.primaryPerson.name" />
        <result property="office.deputyPerson.id" column="office.deputyPerson.id" />
        <result property="office.deputyPerson.name" column="office.deputyPerson.name" />
        <collection property="roleList" ofType="Role">
            <id property="id" column="roleList.id" />
            <result property="office.id" column="roleList.office.id" />
            <result property="name" column="roleList.name" />
            <result property="enname" column="roleList.enname" />
            <result property="roleType" column="roleList.roleType" />
            <result property="dataScope" column="roleList.dataScope" />
            <collection property="officeList" ofType="Office">
                <id property="id" column="roleList.officeList.id" />
            </collection>
        </collection>
    </resultMap> -->

    <sql id="userColumns">
        a.id,
        a.company_id AS "company.id",
        a.office_id AS "office.id",
        a.login_name,
        a.password,
        a.no,
        a.name,
        a.email,
        a.phone,
        a.mobile,
        a.user_type,
        a.login_ip,
        a.login_date,
        a.remarks,
        a.login_flag,
        a.photo,
        a.create_by AS "createBy.id",
        a.create_date,
        a.update_by AS "updateBy.id",
        a.update_date,
        a.del_flag,
        b.label AS supplementGroup,
        c.name AS "company.name",
        c.parent_id AS "company.parent.id",
        c.parent_ids AS "company.parentIds",
        ca.id AS "company.area.id",
        ca.name AS "company.area.name",
        ca.parent_id AS "company.area.parent.id",
        ca.parent_ids AS "company.area.parentIds",
        o.name AS "office.name",
        o.parent_id AS "office.parent.id",
        o.parent_ids AS "office.parentIds",
        oa.id AS "office.area.id",
        oa.name AS "office.area.name",
        oa.parent_id AS "office.area.parent.id",
        oa.parent_ids AS "office.area.parentIds",
        cu.id AS "company.primaryPerson.id",
        cu.name AS "company.primaryPerson.name",
        cu2.id AS "company.deputyPerson.id",
        cu2.name AS "company.deputyPerson.name",
        ou.id AS "office.primaryPerson.id",
        ou.name AS "office.primaryPerson.name",
        ou2.id AS "office.deputyPerson.id",
        ou2.name AS "office.deputyPerson.name"


        <!-- ,
        r.id AS "roleList.id",
        r.office_id AS "roleList.office.id",
        r.name AS "roleList.name",
        r.enname AS "roleList.enname",
        r.role_type AS "roleList.roleType",
        r.data_scope AS "roleList.dataScope" -->
    </sql>

    <sql id="userJoins">
        LEFT JOIN ct_bbtc_sys_office c ON c.id = a.company_id
        LEFT JOIN ct_bbtc_sys_area ca ON ca.id = c.area_id
        LEFT JOIN ct_bbtc_sys_office o ON o.id = a.office_id
        LEFT JOIN ct_bbtc_sys_area oa ON oa.id = o.area_id
        LEFT JOIN ct_bbtc_sys_user cu ON cu.id = c.primary_person
        LEFT JOIN ct_bbtc_sys_user cu2 ON cu2.id = c.deputy_person
        LEFT JOIN ct_bbtc_sys_user ou ON ou.id = o.primary_person
        LEFT JOIN ct_bbtc_sys_user ou2 ON ou2.id = o.deputy_person
        LEFT JOIN ct_bbtc_sys_dict b on b.id = a.supplement_group

        <!--
        LEFT JOIN ct_bbtc_sys_user2role ur ON ur.user_id = a.id
        LEFT JOIN ct_bbtc_sys_role r ON r.id = ur.role_id
         -->
    </sql>

    <!-- 根据编号获得用户 -->
    <select id="get" resultType="User">
        SELECT
        <include refid="userColumns"/>
        ,cr.name AS area
        FROM ct_bbtc_sys_user a
        <include refid="userJoins"/>
        LEFT JOIN ct_bbtc_region CR ON a.area = CR.id
        WHERE a.id = #{id}
    </select>

    <!-- 根据登录名查询用户 -->
    <select id="getByLoginName" resultType="User" parameterType="User">
        SELECT
        <include refid="userColumns"/><!-- ,
            ro.office_id AS "roleList.officeList.id" -->
        FROM ct_bbtc_sys_user a
        <include refid="userJoins"/><!--
        LEFT JOIN ct_bbtc_sys_office2role ro ON ro.role_id = r.id -->
        WHERE a.login_name = #{loginName} AND a.del_flag = #{DEL_FLAG_NORMAL}
    </select>

    <!-- 分页查询用户信息 -->
    <select id="findList" resultType="User">
        SELECT
        role.id AS roleId,
        cr.name AS areaName,
        <include refid="userColumns"/>
        FROM ct_bbtc_sys_user a
        <include refid="userJoins"/>
        LEFT JOIN ct_bbtc_region CR ON a.area = CR.id

        <if test="role != null and role.id != null and role.id != ''">
            JOIN ct_bbtc_sys_user2role ur ON ur.user_id = a.id AND ur.role_id = #{role.id}
        </if>
        LEFT JOIN ct_bbtc_sys_user2role ur ON ur.user_id = a.id
        LEFT JOIN ct_bbtc_sys_role role ON ur.role_id = role.id
        WHERE a.del_flag = #{DEL_FLAG_NORMAL}
        <if test="company != null and company.id != null and company.id != ''">
            AND (c.id = #{company.id} OR c.parent_ids LIKE
            <if test="dbName == 'oracle'">'%,'||#{company.id}||',%')</if>
            <if test="dbName == 'mssql'">'%,'+#{company.id}+',%')</if>
            <if test="dbName == 'mysql'">CONCAT('%,', #{company.id}, ',%'))</if>
        </if>
        <if test="office != null and office.id != null and office.id != ''">
            AND (o.id = #{office.id} OR o.parent_ids LIKE
            <if test="dbName == 'oracle'">'%,'||#{office.id}||',%')</if>
            <if test="dbName == 'mssql'">'%,'+#{office.id}+',%')</if>
            <if test="dbName == 'mysql'">CONCAT('%,', #{office.id}, ',%'))</if>
        </if>
        <!-- 如果不是超级管理员，则不显示超级管理员用户 -->
        <if test="!currentUser.admin">
            AND a.id != '1'
        </if>
        <if test="loginName != null and loginName != ''">
            AND a.login_name like
            <if test="dbName == 'oracle'">'%'||#{loginName}||'%'</if>
            <if test="dbName == 'mssql'">'%'+#{loginName}+'%'</if>
            <if test="dbName == 'mysql'">CONCAT('%', #{loginName}, '%')</if>
        </if>
        <if test="name != null and name != ''">
            AND a.name like
            <if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
            <if test="dbName == 'mssql'">'%'+#{name}+'%'</if>
            <if test="dbName == 'mysql'">CONCAT('%', #{name}, '%')</if>
        </if>
        <!-- 数据范围过滤 -->
        ${sqlMap.dsf}
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.create_date DESC,c.code, o.code, a.name
            </otherwise>
        </choose>
    </select>

    <!-- 根据OfficeId获取用户（树查询用户时用） -->
    <select id="findUserByOfficeId" resultType="User" useCache="true">
        SELECT
            a.id, a.name, a.login_name
        FROM ct_bbtc_sys_user a
        WHERE a.del_flag = #{DEL_FLAG_NORMAL}
            AND a.office_id = #{office.id}
        ORDER BY a.name
    </select>

    <!-- 查询全部用户 -->
    <select id="findAllList" resultType="User">
        SELECT
        cr.name AS areaName,
        <include refid="userColumns"/>
        FROM ct_bbtc_sys_user a
        <include refid="userJoins"/>
        LEFT JOIN ct_bbtc_region CR ON a.area = CR.id

        WHERE a.del_flag = #{DEL_FLAG_NORMAL}
        ORDER BY c.code, o.code, a.name
    </select>

    <!-- 查询全部用户数目 -->
    <select id="findAllCount" resultType="long">
        SELECT
            COUNT(1)
        FROM ct_bbtc_sys_user a
        WHERE a.del_flag = #{DEL_FLAG_NORMAL}
    </select>

    <!-- 插入用户 -->
    <insert id="insert">
        INSERT INTO ct_bbtc_sys_user(
            id,
            company_id,
            office_id,
            login_name,
            password,
            no,
            name,
            area,
            email,
            phone,
            mobile,
            user_type,
            create_by,
            create_date,
            update_by,
            update_date,
            remarks,
            login_flag,
            photo,
            del_flag,
            supplement_group
        ) VALUES (
            #{id},
            #{company.id},
            #{office.id},
            #{loginName},
            #{password},
            #{no},
            #{name},
            #{area},
            #{email},
            #{phone},
            #{mobile},
            #{userType},
            #{createBy.id},
            #{createDate},
            #{updateBy.id},
            #{updateDate},
            #{remarks},
            #{loginFlag},
            #{photo},
            #{delFlag},
            #{supplementGroup}

        )
    </insert>
    <insert id="insertOfficeName">
    INSERT INTO ct_bbtc_sys_user_profiles(
    user_id,
    office_name,
    device_update
     ) VALUES (
        #{id},
        #{officeName},
        CURRENT_TIMESTAMP
        )
    </insert>
    <!-- 更新用户 -->
    <update id="update">
        UPDATE ct_bbtc_sys_user SET
            company_id = #{company.id},
            office_id = #{office.id},
            login_name = #{loginName},
            password = #{password},
            no = #{no},
            name = #{name},
            area=#{area},
            email = #{email},
            phone = #{phone},
            mobile = #{mobile},
            user_type = #{userType},
            update_by = #{updateBy.id},
            update_date = #{updateDate},
            remarks = #{remarks},
            login_flag = #{loginFlag},
            photo = #{photo},
            supplement_group=#{supplementGroup}
        WHERE id = #{id}
    </update>

    <!-- 删除用户和角色关联表数据 -->
    <delete id="deleteUserRole">
        DELETE FROM ct_bbtc_sys_user2role WHERE user_id = #{id}
    </delete>

    <!-- 插入用户和角色关联表数据 -->
    <insert id="insertUserRole">
        INSERT INTO ct_bbtc_sys_user2role(user_id, role_id)
        <foreach collection="roleList" item="role" separator=" union all ">
            SELECT #{id}, #{role.id}
            <if test="dbName != 'mssql'">
                FROM dual
            </if>
        </foreach>
    </insert>

    <!-- 更新用户信息  -->
    <update id="updateUserInfo">
        UPDATE ct_bbtc_sys_user SET
            email = #{email},
            phone = #{phone},
            mobile = #{mobile},
            update_by = #{updateBy.id},
            update_date = #{updateDate},
            remarks = #{remarks},
            photo = #{photo}
        WHERE id = #{id}
    </update>

    <!-- 更新用户密码 -->
    <update id="updatePasswordById">
        UPDATE ct_bbtc_sys_user SET
            password = #{password}
        WHERE id = #{id}
    </update>

    <!-- 更新登录信息，如登录IP、登录时间 -->
    <update id="updateLoginInfo">
        UPDATE ct_bbtc_sys_user SET
            login_ip = #{loginIp},
            login_Date = #{loginDate}
        WHERE id = #{id}
    </update>

    <!-- 逻辑删除用户 -->
    <update id="delete">
        UPDATE ct_bbtc_sys_user SET
            del_flag = #{DEL_FLAG_DELETE}
        WHERE id = #{id}
    </update>

</mapper>