Demo1DAO.xml.oracle

<?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.gaowj.business.test.demo1.Demo1DAO">

	<select id="get" parameterType="String" resultType="upperCaseKeyMap">
	 <![CDATA[
	select t.*, s.EMPLOYEE_CODE, s.NAME, s.DEPT_ID, s.ID_CODE,s.MOBILE_PHONE MOBILE,s.PHONE OFFICE_PHONE,s.SEX  
		from mis_audit.t_audit_email_apply t
		left join URP_CORE.V_ALL_USER s
		on t.EMPLOYEE_ID = s.EMPLOYEE_ID
		where t.APPLY_ID = #{APPLY_ID}
	  ]]>
	</select>
 
	<select id="listPersonal" parameterType="map" resultType="upperCaseKeyMap">
	 <![CDATA[
	select * from mis_test.t_test_apply
	  ]]> 	
	</select>
 
	<select id="listPersonalCount" parameterType="map" resultType="int">
	 <![CDATA[
	select count(1) c
		from mis_audit.t_audit_email_apply t
		left join URP_CORE.V_ALL_USER s
		on t.EMPLOYEE_ID = s.EMPLOYEE_ID
		where 1=1
	  ]]>
	  	and t.EMPLOYEE_ID in (select EMPLOYEE_ID from URP_CORE.V_ALL_USER where USER_ID = #{SESSION_USER_ID})
	  	<if test="BEGIN_DATE != null and BEGIN_DATE != ''">
	    and  t.APPLY_DATE &gt;= #{BEGIN_DATE} 
		</if>
		<if test="END_DATE != null and END_DATE != ''">
	    and  t.APPLY_DATE &lt;= #{END_DATE} 
		</if>
		<if test="APPLY_ID != null and APPLY_ID != ''">
	    and  t.APPLY_ID = #{APPLY_ID}
		</if>
	</select>
	
	
	
 
	<select id="listAudit" parameterType="map" resultType="upperCaseKeyMap">
	 <![CDATA[
	select t.*, s.EMPLOYEE_CODE, s.NAME, w.WORKITEM_INS_ID, w.WORKITEM_STATE, w.AUDIT_PHASE, w.AUDIT_STATUS, s.DEPT_ID, s.ID_CODE
	,s.MOBILE_PHONE MOBILE,s.PHONE OFFICE_PHONE,s.SEX,w.WORKITEM_NAME   
		from mis_audit.t_audit_email_apply t
		left join URP_CORE.V_ALL_USER s
		on t.EMPLOYEE_ID = s.EMPLOYEE_ID
		left join mis.v_rt_workitem_instance w
		on w.MODULE_ID = 'audit.emailApply' and t.APPLY_ID = w.BUSINESS_ID 
		where 1=1
	  ]]>
	  	and w.USER_ID = #{SESSION_USER_ID}
		<if test="NAME != null and NAME != ''">
		and t.EMPLOYEE_ID in (select EMPLOYEE_ID from URP_CORE.V_ALL_USER where name like #{NAME} or EMPLOYEE_ID like #{NAME})
		</if>
		<if test="DEPT_ID != null and DEPT_ID != ''">
        and  s.DEPT_ID = #{DEPT_ID}
        </if>
        <if test="BEGIN_DATE != null and BEGIN_DATE != ''">
	    and  t.APPLY_DATE &gt;= #{BEGIN_DATE} 
		</if>
		<if test="END_DATE != null and END_DATE != ''">
	    and  t.APPLY_DATE &lt;= #{END_DATE} 
		</if>
		<if test="WORKITEM_STATUS != null and WORKITEM_STATUS != '' and WORKITEM_STATUS == 1">
	    and  w.workitem_state = #{WORKITEM_STATUS}
		</if>
		<if test="WORKITEM_STATUS != null and WORKITEM_STATUS != '' and WORKITEM_STATUS == 0">
	    and  w.workitem_state &gt; 1
		</if>
		<if test="WORKITEM_NAME != null and WORKITEM_NAME != ''">
	    and  w.WORKITEM_NAME = #{WORKITEM_NAME}
		</if>
	</select>
 
	<select id="listAuditCount" parameterType="map" resultType="int">
	 <![CDATA[
	select count(1) c
		from mis_audit.t_audit_email_apply t
		left join URP_CORE.V_ALL_USER s
		on t.EMPLOYEE_ID = s.EMPLOYEE_ID
		left join mis.v_rt_workitem_instance w
		on w.MODULE_ID = 'audit.emailApply' and t.APPLY_ID = w.BUSINESS_ID 
		where 1=1
	  ]]> 
	  	and w.USER_ID = #{SESSION_USER_ID}
		<if test="NAME != null and NAME != ''">
		and t.EMPLOYEE_ID in (select EMPLOYEE_ID from URP_CORE.V_ALL_USER where name like #{NAME} or EMPLOYEE_ID like #{NAME})
		</if>
		<if test="DEPT_ID != null and DEPT_ID != ''">
        and  s.DEPT_ID = #{DEPT_ID}
        </if>
        <if test="BEGIN_DATE != null and BEGIN_DATE != ''">
	    and  t.APPLY_DATE &gt;= #{BEGIN_DATE} 
		</if>
		<if test="END_DATE != null and END_DATE != ''">
	    and  t.APPLY_DATE &lt;= #{END_DATE} 
		</if>
		<if test="WORKITEM_STATUS != null and WORKITEM_STATUS != '' and WORKITEM_STATUS == 1">
	    and  w.workitem_state = #{WORKITEM_STATUS}
		</if>
		<if test="WORKITEM_STATUS != null and WORKITEM_STATUS != '' and WORKITEM_STATUS == 0">
	    and  w.workitem_state &gt; 1
		</if>
		<if test="WORKITEM_NAME != null and WORKITEM_NAME != ''">
	    and  w.WORKITEM_NAME = #{WORKITEM_NAME}
		</if>
	</select>
	
	
	
 
	<select id="listManage" parameterType="map" resultType="upperCaseKeyMap">
	 <![CDATA[
	select t.*, s.EMPLOYEE_CODE, s.NAME, s.DEPT_ID, s.ID_CODE,s.MOBILE_PHONE MOBILE,s.PHONE OFFICE_PHONE,s.SEX  
		from mis_audit.t_audit_email_apply t
		left join URP_CORE.V_ALL_USER s
		on t.EMPLOYEE_ID = s.EMPLOYEE_ID
		where 1=1
	  ]]>
	  	and (t.BUSI_STATUS = 'manage' or t.BUSI_STATUS = 'finish')
		<if test="NAME != null and NAME != ''">
		and t.EMPLOYEE_ID in (select EMPLOYEE_ID from URP_CORE.V_ALL_USER where name like #{NAME} or EMPLOYEE_ID like #{NAME})
		</if>
		<if test="DEPT_ID != null and DEPT_ID != ''">
        and  s.DEPT_ID = #{DEPT_ID}
        </if>
        <if test="BEGIN_DATE != null and BEGIN_DATE != ''">
	    and  t.APPLY_DATE &gt;= #{BEGIN_DATE} 
		</if>
		<if test="END_DATE != null and END_DATE != ''">
	    and  t.APPLY_DATE &lt;= #{END_DATE} 
		</if>
	</select>
 
	<select id="listManageCount" parameterType="map" resultType="int">
	 <![CDATA[
	select count(1) c
		from mis_audit.t_audit_email_apply t
		left join URP_CORE.V_ALL_USER s
		on t.EMPLOYEE_ID = s.EMPLOYEE_ID
		where 1=1
	  ]]> 
	  	and (t.BUSI_STATUS = 'manage' or t.BUSI_STATUS = 'finish')
		<if test="NAME != null and NAME != ''">
		and t.EMPLOYEE_ID in (select EMPLOYEE_ID from URP_CORE.V_ALL_USER where name like #{NAME} or EMPLOYEE_ID like #{NAME})
		</if>
		<if test="DEPT_ID != null and DEPT_ID != ''">
       and  s.DEPT_ID = #{DEPT_ID}
        </if>
	</select>
	
	
	
 
 	<insert id="insert" parameterType="map">
		<selectKey resultType="int" keyProperty="APPLY_ID" order="AFTER" >
			select SEQ_mis_audit.t_audit_email_apply.currval from dual
        </selectKey>
	      insert into mis_audit.t_audit_email_apply(
	      <trim prefix=""  suffixOverrides=",">
	         <if test="EMPLOYEE_ID != null ">EMPLOYEE_ID, </if>
	         <if test="APPLY_DATE != null ">APPLY_DATE, </if>
             <if test="BUSI_STATUS != null ">BUSI_STATUS, </if>
             <if test="APPLY_PHASE != null ">APPLY_PHASE, </if>
             <if test="REMARK != null ">REMARK , </if>
             <if test="EMAIL_NAME != null ">EMAIL_NAME, </if>
             <if test="EMAIL_REASON != null ">EMAIL_REASON, </if>
             <if test="MOBILE_PHONE != null ">MOBILE_PHONE, </if>
             <if test="EMAIL_TYPE != null ">EMAIL_TYPE, </if>
             <if test="CONTACT != null ">CONTACT, </if>
           </trim>
	    <![CDATA[ ) values( ]]> 
	       <trim prefix=""  suffixOverrides=",">
	         <if test="EMPLOYEE_ID != null ">#{EMPLOYEE_ID}, </if>
             <if test="APPLY_DATE != null ">#{APPLY_DATE}, </if>
             <if test="BUSI_STATUS != null ">#{BUSI_STATUS}, </if>
             <if test="APPLY_PHASE != null ">#{APPLY_PHASE}, </if>
             <if test="REMARK != null ">#{REMARK}, </if>
             <if test="EMAIL_NAME != null ">#{EMAIL_NAME}, </if>
             <if test="EMAIL_REASON != null ">#{EMAIL_REASON}, </if>
             <if test="MOBILE_PHONE != null ">#{MOBILE_PHONE}, </if>
             <if test="EMAIL_TYPE != null ">#{EMAIL_TYPE}, </if>
             <if test="CONTACT != null ">#{CONTACT}, </if>
            </trim>
	    <![CDATA[ ) ]]> 
 	</insert>
 
 	<update id="modify" parameterType="map">
	   update mis_audit.t_audit_email_apply  
	    <set>  
             <if test="EMPLOYEE_ID != null ">EMPLOYEE_ID = #{EMPLOYEE_ID}, </if>
             <if test="APPLY_DATE != null ">APPLY_DATE = #{APPLY_DATE}, </if>
             <if test="BUSI_STATUS != null ">BUSI_STATUS = #{BUSI_STATUS}, </if>
             <if test="REMARK != null ">REMARK = #{REMARK}, </if>
             <if test="EMAIL_NAME != null ">EMAIL_NAME = #{EMAIL_NAME}, </if>
             <if test="EMAIL_REASON != null ">EMAIL_REASON = #{EMAIL_REASON}, </if>
             <if test="MOBILE_PHONE != null ">MOBILE_PHONE = #{MOBILE_PHONE}, </if>
             <if test="EMAIL_TYPE != null ">EMAIL_TYPE = #{EMAIL_TYPE}, </if>
             <if test="CONTACT != null ">CONTACT = #{CONTACT}, </if>
	    </set>
         where APPLY_ID = #{APPLY_ID} 
 	</update>

 	<update id="delete" parameterType="String">
	 <![CDATA[
	delete from mis_audit.t_audit_email_apply
		where APPLY_ID = #{APPLY_ID}
	  ]]> 
 	</update>

 
 	<update id="modifyStatus" parameterType="map">
	 <![CDATA[
	update mis_audit.t_audit_email_apply set BUSI_STATUS = #{BUSI_STATUS}
		where APPLY_ID = #{APPLY_ID}
	  ]]> 
 	</update>
</mapper>