<?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="MdeWorkExperienceMapper">
	<resultMap type="com.wechat.model.dbEntity.MdeWorkExperience" id="MdeWorkExperience">
    	<id column="work_id" property="workId"/>
		<result column="user_code" property="userCode"/>
		<result column="co_id" property="coId"/>
		<result column="co_name" property="coName"/>
		<result column="position_title" property="positionTitle"/>
		<result column="enter_date" property="enterDate"/>
		<result column="quit_date" property="quitDate"/>
		<result column="on_job" property="onJob"/>
		<result column="show_flag" property="showFlag"/>
		<result column="en_flag" property="enFlag"/>
		<result column="version_no" property="versionNo"/>
		<result column="remarks" property="remarks"/>
		<result column="pay" property="pay"/>
		<result column="del_flag" property="delFlag"/>
		<result column="create_by" property="createBy"/>
		<result column="create_date" property="createDate"/>
		<result column="update_by" property="updateBy"/>
		<result column="update_date" property="updateDate"/>
	</resultMap>
	<sql id="mdeWorkExperienceColumns">
		a.work_id AS "workId",
		a.user_code AS "userCode",
		a.co_id AS "coId",
		a.co_name AS "coName",
		a.position_title AS "positionTitle",
		a.enter_date AS "enterDate",
		a.quit_date AS "quitDate",
		a.on_job AS "onJob",
		a.show_flag AS "showFlag",
		a.en_flag AS "enFlag",
		a.version_no AS "versionNo",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		a.create_by AS "createBy",
		a.create_date AS "createDate",
		a.update_by AS "updateBy",
		a.update_date AS "updateDate"
	</sql>

    
	<select id="get" resultMap="MdeWorkExperience">
		SELECT 
			<include refid="mdeWorkExperienceColumns"/>
		FROM mde_work_experience a
		WHERE a.work_id = #{workId}
	</select>
	
	<select id="findList" resultMap="MdeWorkExperience">
		SELECT 
			<include refid="mdeWorkExperienceColumns"/>
		FROM mde_work_experience a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY #{page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<select id="findAllList" resultMap="MdeWorkExperience">
		SELECT 
			<include refid="mdeWorkExperienceColumns"/>
		FROM mde_work_experience a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
		</where>		
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY #{page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<insert id="insert">
		INSERT INTO mde_work_experience(
			user_code,
			co_id,
			co_name,
			position_title,
			enter_date,
			quit_date,
			on_job,
			show_flag,
			en_flag,
			pay,
			version_no,
			remarks,
			del_flag,
			create_by,
			create_date,
			update_by,
			update_date
		) VALUES (
			#{userCode},
			#{coId},
			#{coName},
			#{positionTitle},
			#{enterDate},
			#{quitDate},
			#{onJob},
			#{showFlag},
			#{enFlag},
			#{pay},
			#{versionNo},
			#{remarks},
			#{delFlag},
			#{createBy},
			#{createDate},
			#{updateBy},
			#{updateDate}
		)
	</insert>
	
	<update id="update">
		UPDATE mde_work_experience SET 	
			<if test="userCode !=null"> 	user_code = #{userCode},        </if>
			<if test="coId !=null"> 		co_id = #{coId},                </if>
			<if test="coName !=null"> 		co_name = #{coName},            </if>
			<if test="positionTitle !=null"> position_title = #{positionTitle},</if>
			<if test="enterDate !=null"> 	enter_date = #{enterDate},      </if>
				quit_date = #{quitDate},
				pay = #{pay},    
			<if test="onJob !=null"> 		on_job = #{onJob},              </if>
			<if test="showFlag !=null"> 	show_flag = #{showFlag},        </if>
			<if test="enFlag !=null"> 		en_flag = #{enFlag},            </if>
			<if test="remarks !=null"> 		remarks = #{remarks},           </if>
			<if test="updateBy !=null"> 	update_by = #{updateBy},        </if>
			<if test="updateDate !=null"> 	update_date = #{updateDate},    </if>
			version_no = version_no +1
		WHERE work_id = #{workId} and del_flag=0 <if test="versionNo !=null"> and version_no = #{versionNo} </if>
	</update>
	<!-- 修改职位头衔 -->
	<update id="updatePositionTitle">
		UPDATE mde_work_experience SET 	
			show_flag = case WORK_ID when #{workId} then 0 else 1 end ,
			<if test="updateBy !=null"> 	update_by = #{updateBy},        </if>
			<if test="updateDate !=null"> 	update_date = #{updateDate},    </if>
			version_no = version_no +1
		WHERE del_flag=0 and user_code=#{userCode}
		<if test="versionNo !=null"> and version_no = #{versionNo} </if>
	</update>
	
	<!-- 当前显示头衔 设置为不显示 -->
	<update id="updateCurrentPositionTitle" >
			UPDATE mde_work_experience SET 	
			show_flag = 1,
			version_no = version_no +1
		WHERE del_flag=0 and show_flag =0 and user_code= #{userCode}
	</update>
	<!-- 物理删除 -->
	<delete id="delete" parameterType="java.lang.Integer">
		DELETE
		FROM
			mde_work_experience
		WHERE
			WORK_ID = #{workId}
	</delete>
		
	<!-- 用户工作经历记录数 -->
	<select id="findByUserCodeCount" resultType="java.lang.Integer">
		select count(*) from  mde_work_experience where USER_CODE=#{userCode} and  DEL_FLAG=0
	</select>
	
	<!-- 职位头衔详情 -->
	<select id="findWorkPTByUserCode" parameterType="java.lang.String" resultMap="MdeWorkExperience">
		select <include refid="mdeWorkExperienceColumns"/> from  mde_work_experience a
		where a.USER_CODE=#{userCode} and a.DEL_FLAG=0 and a.show_flag = 0 limit 1 
	</select>
	
	<select id="getBaseWorkExperienceByUserCode" resultMap="MdeWorkExperience">
		SELECT 
			<include refid="mdeWorkExperienceColumns"/>
		FROM mde_work_experience a
		WHERE a.user_code = #{userCode} and a.DEL_FLAG=0
		ORDER BY a.ENTER_DATE DESC,IFNULL(a.QUIT_DATE,9999-12-01) DESC
	</select>
	
	<select id="getAllWorkExperienceByUserCode" resultMap="MdeWorkExperience">
		SELECT 
			<include refid="mdeWorkExperienceColumns"/>
			,a.PAY
		FROM mde_work_experience a
		WHERE a.user_code = #{userCode} and a.DEL_FLAG=0
		ORDER BY a.ENTER_DATE DESC,IFNULL(a.QUIT_DATE,9999-12-01) DESC
	</select>
	
		<!-- 批量查询工作经历 -->
	<select id="getWorkExperienceByUserCodeList"  resultMap="MdeWorkExperience">
		select 
		<include refid="mdeWorkExperienceColumns"/>
		from mde_work_experience a
		where 
			a.del_flag=0
			and a.user_code in 
			<foreach collection="userCodeList" item="userCode" separator="," open="(" close=")">
			#{userCode}
			</foreach>
			order by a.ENTER_DATE desc,IFNULL(a.QUIT_DATE,9999-12-01) DESC
	</select>
	
	<!-- 获取工作履历 降序 -->
	<select id="getWorkExperienceAndQuitDateDesc" resultMap="MdeWorkExperience">
			SELECT
				<include refid="mdeWorkExperienceColumns"/>
			FROM
				mde_work_experience a
			WHERE
				a.USER_CODE = #{userCode}
			ORDER BY
				IFNULL(a.QUIT_DATE,NOW()) DESC			
				,a.ENTER_DATE DESC
	</select>
	
	<!-- 查询离职日期为空 并按入职时间降序 -->
	<select id="getQuitDateIsNull" resultMap="MdeWorkExperience">
			SELECT
					<include refid="mdeWorkExperienceColumns"/>
				FROM
					mde_work_experience a
				WHERE
					a.USER_CODE = #{userCode}
				AND a.QUIT_DATE IS NULL
				ORDER BY a.ENTER_DATE DESC
	</select>
	<!-- 重置用户职位头衔显示 -->
	<update id="resetPositionTitle">
		update mde_work_experience set show_flag=1 WHERE user_code = #{userCode}
	</update>
	<!-- 设置最近的工作履历为显示的职位头衔 -->
	<update id="setLatestWEShow">
		UPDATE mde_work_experience SET show_flag = 0 
		WHERE del_flag = 0 AND user_code = #{userCode}
		ORDER BY enter_date DESC, IFNULL( quit_date, '9999-12-31' ) DESC 
		LIMIT 1 
	</update>
	
</mapper>