<?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">
<!-- 不使用namespace的话sql搜索定位会比较方便 -->
<mapper namespace="com.blueland.dao.PubUserDao">
	<resultMap id="RM_PubUserEntity" type="com.blueland.bo.PubUserEntity">
        <result property="userId" column="USER_ID"/>
        <result property="userCode" column="USER_CODE"/>
        <result property="userName" column="USER_NAME"/>
        <result property="userSex" column="USER_SEX"/>
        <result property="userAge" column="USER_AGE"/>
        <result property="companyId" column="COMPANY_ID"/>
        <result property="orgId" column="ORG_ID"/>
        <result property="userMobile" column="USER_MOBILE"/>
        <result property="userMail" column="USER_MAIL"/>
        <result property="userWorkAddress" column="USER_WORK_ADDRESS"/>
        <result property="userWorkPhone" column="USER_WORK_PHONE"/>
        <result property="userHomeAddree" column="USER_HOME_ADDREE"/>
        <result property="userHomePhone" column="USER_HOME_PHONE"/>
        <result property="positionId" column="POSITION_ID"/>
        <result property="pluralityPositionId" column="PLURALITY_POSITION_ID"/>
        <result property="titleId" column="TITLE_ID"/>
        <result property="pluralityTitleId" column="PLURALITY_TITLE_ID"/>
        <result property="userType" column="USER_TYPE"/>
        <result property="userStatus" column="USER_STATUS"/>
        <result property="userSort" column="USER_SORT"/>
        <result property="password" column="PASSWORD"/>
        <result property="salt" column="SALT"/>
        <result property="userCreateTime" column="USER_CREATE_TIME"/>
        <result property="userUpdateTime" column="USER_UPDATE_TIME"/>
        <result property="userCreator" column="USER_CREATOR"/>
        <result property="remark" column="REMARK"/>
        <result property="deptId" column="DEPT_ID"/>
        <result property="companyName" column="COMPANY_NAME"/>
        <result property="deptName" column="DEPT_NAME"/>
        <result property="orgName" column="ORG_NAME"/>
	</resultMap>

	<sql id="PubUserEntityColumns">
        	USER_ID,
        	USER_CODE,
        	USER_NAME,
        	USER_SEX,
        	USER_AGE,
        	COMPANY_ID,
        	ORG_ID,
        	USER_MOBILE,
        	USER_MAIL,
        	USER_WORK_ADDRESS,
        	USER_WORK_PHONE,
        	USER_HOME_ADDREE,
        	USER_HOME_PHONE,
        	POSITION_ID,
        	PLURALITY_POSITION_ID,
        	TITLE_ID,
        	PLURALITY_TITLE_ID,
        	USER_TYPE,
        	USER_STATUS,
        	USER_SORT,
        	PASSWORD,
        	SALT,
        	USER_CREATE_TIME,
        	USER_UPDATE_TIME,
        	USER_CREATOR,
        	REMARK,
        	DEPT_ID,
        	COMPANY_NAME,
        	DEPT_NAME,
        	ORG_NAME
	</sql>
	
	<sql id="PubUserEntityQueryColumns">
        	USER_ID,
        	USER_CODE,
        	USER_NAME,
        	USER_SEX,
        	USER_AGE,
        	COMPANY_ID,
        	ORG_ID,
        	USER_MOBILE,
        	USER_MAIL,
        	USER_WORK_ADDRESS,
        	USER_WORK_PHONE,
        	USER_HOME_ADDREE,
        	USER_HOME_PHONE,
        	POSITION_ID,
        	PLURALITY_POSITION_ID,
        	TITLE_ID,
        	PLURALITY_TITLE_ID,
        	USER_TYPE,
        	USER_STATUS,
        	USER_SORT,
        	PASSWORD,
        	SALT,
        	USER_CREATE_TIME,
        	USER_UPDATE_TIME,
        	USER_CREATOR,
        	REMARK,
        	DEPT_ID,
        	COMPANY_NAME,
        	DEPT_NAME,
        	ORG_NAME
	</sql>
	<!-- useGeneratedKeys="true" keyProperty="xxx" for sqlserver and mysql -->
	<insert id="insert" useGeneratedKeys="false" keyProperty="userId">
        INSERT INTO t_pub_user (<include refid="PubUserEntityColumns"/>) VALUES (
        	#{userId,jdbcType=VARCHAR},
        	#{userCode,jdbcType=VARCHAR},
        	#{userName,jdbcType=VARCHAR},
        	#{userSex,jdbcType=VARCHAR},
        	#{userAge,jdbcType=INTEGER},
        	#{companyId,jdbcType=VARCHAR},
        	#{orgId,jdbcType=VARCHAR},
        	#{userMobile,jdbcType=VARCHAR},
        	#{userMail,jdbcType=VARCHAR},
        	#{userWorkAddress,jdbcType=VARCHAR},
        	#{userWorkPhone,jdbcType=VARCHAR},
        	#{userHomeAddree,jdbcType=VARCHAR},
        	#{userHomePhone,jdbcType=VARCHAR},
        	#{positionId,jdbcType=VARCHAR},
        	#{pluralityPositionId,jdbcType=VARCHAR},
        	#{titleId,jdbcType=VARCHAR},
        	#{pluralityTitleId,jdbcType=VARCHAR},
        	#{userType,jdbcType=VARCHAR},
        	#{userStatus,jdbcType=VARCHAR},
        	#{userSort,jdbcType=INTEGER},
        	#{password,jdbcType=VARCHAR},
        	#{salt,jdbcType=VARCHAR},
        	#{userCreateTime,jdbcType=TIMESTAMP},
        	#{userUpdateTime,jdbcType=TIMESTAMP},
        	#{userCreator,jdbcType=VARCHAR},
        	#{remark,jdbcType=VARCHAR},
        	#{deptId,jdbcType=VARCHAR},
        	#{companyName,jdbcType=VARCHAR},
        	#{deptName,jdbcType=VARCHAR},
        	#{orgName,jdbcType=VARCHAR}
        )
	</insert>
    
    <select id="get" resultMap="RM_PubUserEntity">
		SELECT <include refid="PubUserEntityQueryColumns"/>
	    FROM t_pub_user 
        WHERE 
	        USER_ID = #{userId,jdbcType=VARCHAR} 
	</select>
	
	<select id="getAppRoleUser" resultType="java.util.Map">
			select t1.user_id ,
		t1.user_name ,
       t1.org_id ,
       t1.org_name ,
       t3.app_id ,
       t3.role_id ,
        t1.company_id ,
                       t1.company_name ,
                       t1.dept_id ,
                       t1.dept_name 
	  from t_pub_user t1, t_pf_role2user t2, t_pf_role t3
	 where t1.user_id = t2.user_id
	   and t2.status = 'y'
	   and t3.role_id = t2.role_id
	   and t3.status = 'y'
	   and t3.app_id = #{bo.appId,jdbcType=VARCHAR}
	   and t2.role_id = #{bo.roleId,jdbcType=VARCHAR}
	   <if test="@Ognl@isNotBlank(bo.companyId)"> and t1.company_id=#{bo.companyId,jdbcType=VARCHAR}</if>
       <if test="@Ognl@isNotBlank(bo.deptId)"> and t1.dept_id=#{bo.deptId,jdbcType=VARCHAR}</if>
	   <if test="@Ognl@isNotBlank(bo.orgId)"> and t1.org_id=#{bo.orgId,jdbcType=VARCHAR}</if>
	</select>

	<select id="getAppRoleUserPage" resultType="java.util.Map">
		select t1.user_id ,
		t1.user_name ,
		t1.org_id ,
		t1.org_name ,
		t3.app_id ,
		t3.role_id ,
		t1.company_id ,
		t1.company_name ,
		t1.dept_id ,
		t1.dept_name
		from t_pub_user t1, t_pf_role2user t2, t_pf_role t3
		where t1.user_id = t2.user_id
		and t2.status = 'y'
		and t3.role_id = t2.role_id
		and t3.status = 'y'
		and t3.app_id = #{bo.appId,jdbcType=VARCHAR}
		and t2.role_id = #{bo.roleId,jdbcType=VARCHAR}
		<if test="@Ognl@isNotBlank(bo.companyId)"> and t1.company_id=#{bo.companyId,jdbcType=VARCHAR}</if>
		<if test="@Ognl@isNotBlank(bo.deptId)"> and t1.dept_id=#{bo.deptId,jdbcType=VARCHAR}</if>
		<if test="@Ognl@isNotBlank(bo.orgId)"> and t1.org_id=#{bo.orgId,jdbcType=VARCHAR}</if>
	</select>
    <!--
    	分页查询已经使用Dialect进行分页,也可以不使用Dialect直接编写分页
    	因为分页查询将传 offset,pageSize,lastRows 三个参数,不同的数据库可以根于此三个参数属性应用不同的分页实现
    -->
    <select id="findList" resultMap="RM_PubUserEntity">
    	SELECT <include refid="PubUserEntityQueryColumns"/>
	    FROM t_pub_user 
		<include refid="findList_where"/>
		
		<if test="@Ognl@isNotEmpty(bo.sortColumns)">
			ORDER BY ${bo.sortColumns}
		</if>
    </select>

	<select id="findPage" resultMap="RM_PubUserEntity">
		SELECT <include refid="PubUserEntityQueryColumns"/>
		FROM t_pub_user
		<include refid="findList_where"/>

		<if test="@Ognl@isNotEmpty(bo.sortColumns)">
			ORDER BY ${bo.sortColumns}
		</if>
	</select>
	
	<sql id="findList_where">
		<!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用rapid中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用，具体请查看Ognl类 -->
		<where>	      				
	       <if test="@Ognl@isNotEmpty(bo.userId)">
				AND USER_ID = #{bo.userId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userCode)">
				AND USER_CODE = #{bo.userCode,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userName)">
				AND USER_NAME = #{bo.userName,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userSex)">
				AND USER_SEX = #{bo.userSex,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userAge)">
				AND USER_AGE = #{bo.userAge,jdbcType=INTEGER}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.companyId)">
				AND COMPANY_ID = #{bo.companyId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.orgId)">
				AND ORG_ID = #{bo.orgId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userMobile)">
				AND USER_MOBILE = #{bo.userMobile,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userMail)">
				AND USER_MAIL = #{bo.userMail,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userWorkAddress)">
				AND USER_WORK_ADDRESS = #{bo.userWorkAddress,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userWorkPhone)">
				AND USER_WORK_PHONE = #{bo.userWorkPhone,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userHomeAddree)">
				AND USER_HOME_ADDREE = #{bo.userHomeAddree,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userHomePhone)">
				AND USER_HOME_PHONE = #{bo.userHomePhone,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.positionId)">
				AND POSITION_ID = #{bo.positionId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.pluralityPositionId)">
				AND PLURALITY_POSITION_ID = #{bo.pluralityPositionId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.titleId)">
				AND TITLE_ID = #{bo.titleId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.pluralityTitleId)">
				AND PLURALITY_TITLE_ID = #{bo.pluralityTitleId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userType)">
				AND USER_TYPE = #{bo.userType,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userStatus)">
				AND USER_STATUS = #{bo.userStatus,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.userSort)">
				AND USER_SORT = #{bo.userSort,jdbcType=INTEGER}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.password)">
				AND PASSWORD = #{bo.password,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.salt)">
				AND SALT = #{bo.salt,jdbcType=VARCHAR}
			</if>
	       
	       
	       <if test="@Ognl@isNotEmpty(bo.userCreator)">
				AND USER_CREATOR = #{bo.userCreator,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.remark)">
				AND REMARK = #{bo.remark,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.deptId)">
				AND DEPT_ID = #{bo.deptId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.companyName)">
				AND COMPANY_NAME = #{bo.companyName,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.deptName)">
				AND DEPT_NAME = #{bo.deptName,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.orgName)">
				AND ORG_NAME = #{bo.orgName,jdbcType=VARCHAR}
			</if>
		</where>
	</sql>
    
	
</mapper>

