<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="User">
<typeAlias alias="page" type="byd.core.Page"/>
<typeAlias alias="user" type="byd.entity.User"/>
<parameterMap id="userMap" class="user">
<parameter property="userName" javaType="string" jdbcType="VARCHAR"/>
<parameter property="passWord" javaType="string" jdbcType="VARCHAR"/>
<parameter property="emplNo" javaType="string" jdbcType="VARCHAR"/>
<parameter property="emplName" javaType="string" jdbcType="VARCHAR"/>
<parameter property="phoneNum" javaType="string" jdbcType="VARCHAR"/>
<parameter property="email" javaType="string" jdbcType="VARCHAR"/>
<parameter property="deptName" javaType="string" jdbcType="VARCHAR"/>
<parameter property="dutyName" javaType="string" jdbcType="VARCHAR"/>
<parameter property="remark" javaType="string" jdbcType="VARCHAR"/>
<parameter property="companyName" javaType="string" jdbcType="VARCHAR"/>
</parameterMap>
<sql id="byUserIdCondition">
<![CDATA[
id = #id:INTEGER#
]]>
</sql>
<!-- 新增用户 -->
<insert id="save" parameterMap="userMap">
<![CDATA[
INSERT INTO PLAN_USER (id,userName,passWord,emplNo,emplName,phoneNum,email,deptName,dutyName,remark,companyName)
VALUES (PLAN_USER_LOG_S.nextval,?,?,?,?,?,?,?,?,?,?)
]]>
<selectKey resultClass="java.lang.Integer">
<![CDATA[
SELECT PLAN_USER_LOG_S.currval
AS id FROM dual
]]>
</selectKey>
</insert>
<!-- 删除用户 -->
<delete id="deleteById" parameterClass="user">
<![CDATA[
delete PLAN_USER
]]>
<dynamic prepend="WHERE">
<include refid="byUserIdCondition"/>
</dynamic>
</delete>
<!-- 修改用户 -->
<update id="update" parameterClass="user">
<![CDATA[
update PLAN_USER
]]>
<dynamic prepend="SET">
<isNotNull property="userName" prepend=",">
<![CDATA[
userName = #userName#
]]>
</isNotNull>
<isNotNull property="passWord" prepend=",">
<![CDATA[
passWord = #passWord#
]]>
</isNotNull>
<isNotNull property="emplNo" prepend=",">
<![CDATA[
emplNo = #emplNo#
]]>
</isNotNull>
<isNotNull property="emplName" prepend=",">
<![CDATA[
emplName = #emplName#
]]>
</isNotNull>
<isNotNull property="phoneNum" prepend=",">
<![CDATA[
phoneNum = #phoneNum#
]]>
</isNotNull>
<isNotNull property="deptName" prepend=",">
<![CDATA[
deptName = #deptName#
]]>
</isNotNull>
<isNotNull property="dutyName" prepend=",">
<![CDATA[
dutyName = #dutyName#
]]>
</isNotNull>
<isNotNull property="remark" prepend=",">
<![CDATA[
remark = #remark#
]]>
</isNotNull>
<isNotNull property="email" prepend=",">
<![CDATA[
email= #email#
]]>
</isNotNull>
<isNotNull property="companyName" prepend=",">
<![CDATA[
companyName = #companyName#
]]>
</isNotNull>
</dynamic>
<dynamic prepend="WHERE">
<include refid="byUserIdCondition" />
</dynamic>
</update>
<!-- 分页查询 -->
<select id="findByPage" parameterClass="page" resultClass="user">
<![CDATA[
SELECT id,userName,passWord,emplNo,emplName,phoneNum,email,deptName,dutyName,remark,companyName,state FROM (SELECT a.*,ROWNUM nm
FROM (SELECT u.id, u.username, u.passWord, u.emplname, u.emplno, u.companyname,u.phoneNum,
u.deptname, u.dutyname,u.remark, u.email,l.state
FROM PLAN_user u LEFT JOIN TIME_logout l ON u.id =l.userid
WHERE (
('$objCondition$' is null or upper(u.userName) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(u.phoneNum) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(u.email) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(u.companyName) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(u.deptName) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(u.dutyName) LIKE upper('%$objCondition$%')))
ORDER BY id DESC) a
WHERE ($start$=0 OR ROWNUM <= DECODE ($start$, 1, $limit$, $start$ + $limit$ - 1)))
WHERE ($start$=0 OR nm >= $start$)
]]>
</select>
<select id="findByCount" parameterClass="page" resultClass="int">
<![CDATA[
SELECT COUNT(*) FROM PLAN_USER
WHERE (
('$objCondition$' is null or upper(userName) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(phoneNum) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(email) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(companyName) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(deptName) LIKE upper('%$objCondition$%'))
OR('$objCondition$' is null or upper(dutyName) LIKE upper('%$objCondition$%')))
]]>
</select>
<!-- 用户登录 -->
<select id="login" parameterClass="user" resultClass="user">
<![CDATA[
SELECT * FROM PLAN_USER u
WHERE userName = #userName# and passWord = #passWord# and u.id not in(SELECT userId FROM TIME_LOGOUT)
]]>
</select>
<!-- 查询所有用户姓名 -->
<select id="findUser" resultClass="user">
<![CDATA[
SELECT emplName FROM PLAN_USER
]]>
</select>
</sqlMap>
