<?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.picc.grab.mapper.UserMapper">

	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap" type="com.picc.grab.domain.User">
		<id column="id" property="id" />
		<result column="fgs_id" property="fgsId" />
		<result column="ygbh" property="ygbh" />
		<result column="userName" property="userName" />
		<result column="userPass" property="userPass" />
		<result column="mail" property="mail" />
		<result column="trueName" property="trueName" />
		<result column="mobile" property="mobile" />
		<result column="createTime" property="createTime" />
		<result column="lastModify" property="lastModify" />
		<result column="lastLoginTime" property="lastLoginTime" />
		<result column="loginTimes" property="loginTimes" />
		<result column="loginErrTimes" property="loginErrTimes" />
		<result column="lastErrLoginTime" property="lastErrLoginTime" />
		<result column="lastLoginIp" property="lastLoginIp" />
		<result column="status" property="status" />
	</resultMap>

	<select id="findByName" resultType="com.picc.grab.domain.User" parameterType="java.lang.String" resultMap="BaseResultMap">
		SELECT * from md_user where userName=#{name} AND status = 1
	</select>

	<select id="findAllByStatus" resultMap="BaseResultMap">
		SELECT id,trueName FROM md_user WHERE status = #{status}
	</select>

	<select id="pageFind" resultType="com.picc.grab.domain.User">
		<!-- GROUP_CONCAT(fgs.fgs_mc) fgsMc 和 GROUP_CONCAT(ug.groupName) 会存在重复数据，前端展示需要排重  -->
		SELECT u.*,GROUP_CONCAT(fgs.fgs_mc) fgsMc,GROUP_CONCAT(ug.groupName) roleName
		FROM md_user u
		LEFT JOIN md_fgs_info fgs ON u.fgs_id LIKE CONCAT(CONCAT('%',fgs.id), '%')
		LEFT JOIN md_user_and_user_group uug ON u.id = uug.userId
		LEFT JOIN md_user_group ug ON uug.userGroupId = ug.id
		<where>
			1=1
			<if test="null != dto.searchWord and '' != dto.searchWord">
				<if test="null != dto.qtype and 0 == dto.qtype">
					and u.userName LIKE CONCAT('%',CONCAT(#{dto.searchWord},'%'))
				</if>
				<if test="null != dto.qtype and 1 == dto.qtype">
					and u.trueName LIKE CONCAT('%',CONCAT(#{dto.searchWord},'%'))
				</if>
			</if>
			<if test="null != dto.qstatus">
				and u.status = #{dto.qstatus}
			</if>
		</where>
		GROUP BY u.id
		ORDER BY u.createTime DESC
	</select>
</mapper>
