<?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.thinkgem.jeesite.modules.sys.dao.UserDao">
	
	<!-- <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.pcode,
    	a.ckbh,
		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,
    	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">
		JOIN sys_office c ON c.id = a.company_id
		JOIN sys_area ca ON ca.id = c.area_id
		JOIN sys_office o ON o.id = a.office_id
		JOIN sys_area oa ON oa.id = o.area_id
		LEFT JOIN sys_user cu ON cu.id = c.primary_person
		LEFT JOIN sys_user cu2 ON cu2.id = c.deputy_person
		LEFT JOIN sys_user ou ON ou.id = o.primary_person
		LEFT JOIN sys_user ou2 ON ou2.id = o.deputy_person<!-- 
		LEFT JOIN sys_user_role ur ON ur.user_id = a.id
        LEFT JOIN sys_role r ON r.id = ur.role_id -->
    </sql>
	
	<!-- 根据编号获得用户 -->
	<select id="get" resultType="User">
		SELECT
			<include refid="userColumns"/><!-- ,
			ro.office_id AS "roleList.officeList.id" -->
		FROM sys_user a
		<include refid="userJoins"/><!-- 
		LEFT JOIN sys_role_office ro ON ro.role_id = r.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 sys_user a
		<include refid="userJoins"/><!-- 
		LEFT JOIN sys_role_office ro ON ro.role_id = r.id -->
		WHERE a.login_name = #{loginName} AND a.del_flag = #{DEL_FLAG_NORMAL}
	</select>

	<!-- 根据登录名查询用户 -->
	<select id="getByName" resultType="User" parameterType="User">
		SELECT 
			* 
		FROM sys_user a
		WHERE a.name = #{loginName} AND a.del_flag = #{DEL_FLAG_NORMAL}
	</select>
	
	<!-- 分页查询用户信息 -->
	<select id="findList" resultType="User">
		SELECT
			<include refid="userColumns"/>
		FROM sys_user a
		<include refid="userJoins"/>
		<if test="role != null and role.id != null and role.id != ''">
			JOIN sys_user_role ur ON ur.user_id = a.id AND ur.role_id = #{role.id}
		</if>
		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 == '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 == '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 == 'mysql'">CONCAT('%', #{loginName}, '%')</if>
		</if>
		<if test="name != null and name != ''">
			AND a.name like 
					<if test="dbName == 'oracle'">'%'||#{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 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 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
			<include refid="userColumns"/>
		FROM sys_user a
		<include refid="userJoins"/>
		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 sys_user a
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
	</select>
	
	<!-- 插入用户 -->
	<insert id="insert">
		INSERT INTO sys_user(
			id, 
			company_id, 
			office_id, 
			login_name, 
			password, 
			no, 
			pcode,
			ckbh,
			name, 
			email, 
			phone, 
			mobile, 
			user_type, 
			create_by, 
			create_date, 
			update_by, 
			update_date, 
			remarks, 
			login_flag, 
			photo, 
			del_flag,
			windowNumber，
			blyw
		) VALUES (
			#{id}, 
			#{company.id}, 
			#{office.id}, 
			#{loginName}, 
			#{password}, 
			#{no}, 
			#{pcode},
			#{ckbh}, 
			#{name}, 
			#{email}, 
			#{phone}, 
			#{mobile}, 
			#{userType}, 
			#{createBy.id}, 
			#{createDate}, 
			#{updateBy.id}, 
			#{updateDate}, 
			#{remarks}, 
			#{loginFlag}, 
			#{photo}, 
			#{delFlag},
			#{windowNumber}, 
			#{blyw}
		)
	</insert>
	
	<!-- 更新用户 -->
	<update id="update">
		UPDATE sys_user SET 
			company_id = #{company.id}, 
			office_id = #{office.id}, 
			login_name = #{loginName}, 
			password = #{password}, 
			no = #{no}, 
			pcode = #{pcode}, 
			ckbh = #{ckbh},
			name = #{name}, 
			email = #{email}, 
			phone = #{phone}, 
			mobile = #{mobile}, 
			user_type = #{userType}, 
			update_by = #{updateBy.id}, 
			update_date = #{updateDate}, 
			remarks = #{remarks},
			login_flag = #{loginFlag},
			photo = #{photo},
			windowNumber = #{windowNumber}, 
			blyw = #{blyw}
		WHERE id = #{id}
	</update>
	
	<!-- 删除用户和角色关联表数据 -->
	<delete id="deleteUserRole">
		DELETE FROM sys_user_role WHERE user_id = #{id}
	</delete>
	
	<!-- 插入用户和角色关联表数据 -->
	<insert id="insertUserRole">
		INSERT INTO sys_user_role(user_id, role_id)
		<foreach collection="roleList" item="role" separator=" union all ">
			SELECT #{id}, #{role.id} FROM dual
		</foreach>
	</insert>
	
	<!-- 更新用户信息  -->
	<update id="updateUserInfo">
		UPDATE sys_user SET 
			email = #{email}, 
			phone = #{phone}, 
			pcode = #{pcode}, 
			ckbh = #{ckbh},
			mobile = #{mobile}, 
			update_by = #{updateBy.id}, 
			update_date = #{updateDate}, 
			remarks = #{remarks},
			photo = #{photo},
			windowNumber = #{windowNumber}, 
			blyw = #{blyw}
		WHERE id = #{id}
	</update>
	
	<!-- 更新用户密码 -->
	<update id="updatePasswordById">
		UPDATE sys_user SET 
			password = #{password} 
		WHERE id = #{id}
	</update>
	
	<!-- 更新登录信息，如登录IP、登录时间 -->
	<update id="updateLoginInfo">
		UPDATE sys_user SET 
			login_ip = #{loginIp}, 
			login_Date = #{loginDate} 
		WHERE id = #{id}
	</update>
	
	<!-- 逻辑删除用户 -->
	<update id="delete">
		UPDATE sys_user SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	
</mapper>