<?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.yifu.cloud.plus.v1.yifu.archives.mapper.EmployeeRegistrationPreMapper">

  <resultMap id="employeeRegistrationPreMap" type="com.yifu.cloud.plus.v1.yifu.archives.entity.EmployeeRegistrationPre">
    <id property="id" column="id"/>
    <result property="employeeName" column="employee_name"/>
    <result property="empIdcard" column="emp_idcard"/>
    <result property="empPhone" column="emp_phone"/>
    <result property="position" column="position"/>
    <result property="joinLeaveDate" column="join_leave_date"/>
    <result property="processStatus" column="process_status"/>
    <result property="customerUsername" column="customer_username"/>
    <result property="customerUserLoginname" column="customer_user_loginname"/>
    <result property="deptName" column="dept_name"/>
    <result property="deptNo" column="dept_no"/>
    <result property="deptId" column="dept_id"/>
    <result property="empNature" column="emp_nature"/>
    <result property="contractType" column="contract_type"/>
    <result property="contractSubName" column="contract_sub_name"/>
    <result property="dataSource" column="data_source"/>
    <result property="expectedCollectionTime" column="expected_collection_time"/>
    <result property="serverItem" column="server_item"/>
    <result property="fileProvince" column="file_province"/>
    <result property="fileCity" column="file_city"/>
    <result property="fileTown" column="file_town"/>
	  <result property="provinceCode" column="province_code"/>
	  <result property="cityCode" column="city_code"/>
	  <result property="townCode" column="town_code"/>
    <result property="confirmUser" column="confirm_user"/>
    <result property="confirmTime" column="confirm_time"/>
    <result property="createBy" column="create_by"/>
    <result property="createName" column="create_name"/>
    <result property="createTime" column="create_time"/>
    <result property="updateBy" column="update_by"/>
    <result property="updateTime" column="update_time"/>
    <result property="customerUsernameNew" column="customer_username_new"/>
    <result property="insuranceIsBuy" column="insurance_is_buy"/>
    <result property="socialIsBuy" column="social_is_buy"/>
  </resultMap>

	<resultMap id="employeeRegistrationPreExportMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.EmployeeRegistrationPreExportVo">
		<result property="employeeName" column="employee_name"/>
		<result property="empIdcard" column="emp_idcard"/>
		<result property="empPhone" column="emp_phone"/>
		<result property="joinLeaveDate" column="join_leave_date"/>
		<result property="customerUsernameNew" column="customer_username_new"/>
		<result property="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="empNature" column="emp_nature"/>
		<result property="contractType" column="contract_type"/>
		<result property="dataSource" column="data_source"/>
		<result property="expectedCollectionTime" column="expected_collection_time"/>
		<result property="processStatus" column="process_status"/>
	</resultMap>

	<resultMap id="employeeRegistrationPreInfoMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.EmployeeRegistrationPreInfoExportVo">
		<result property="employeeName" column="employee_name"/>
		<result property="empIdcard" column="emp_idcard"/>
		<result property="empPhone" column="emp_phone"/>
		<result property="position" column="position"/>
		<result property="joinLeaveDate" column="join_leave_date"/>
		<result property="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="empNature" column="emp_nature"/>
		<result property="contractType" column="contract_type"/>
		<result property="serverItem" column="server_item"/>
		<result property="confirmUser" column="confirm_user"/>
		<result property="confirmTime" column="confirm_time"/>
		<result property="fileAdress" column="fileAdress"/>
	</resultMap>

	<resultMap id="messageTempleteVoMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.MessageTempleteVo">
		<result property="signName" column="sign_name"/>
		<result property="templeteCode" column="model_code"/>
	</resultMap>

	<resultMap id="getEmployeeInfoMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.EmployeeInfoMsgVo">
		<result property="deptId" column="deptId"/>
		<result property="name" column="name"/>
		<result property="card" column="card"/>
		<result property="phone" column="phone"/>
		<result property="detailId" column="detailId"/>
	</resultMap>

    <sql id="Base_Column_List">
                a.id,
                a.employee_name,
                a.emp_idcard,
                a.emp_phone,
                a.position,
                a.join_leave_date,
                a.process_status,
                a.customer_username,
                a.customer_user_loginname,
                a.dept_name,
                a.dept_no,
                a.dept_id,
                a.emp_nature,
                a.contract_type,
                a.data_source,
                a.expected_collection_time,
                a.server_item,
                a.file_province,
                a.file_city,
                a.file_town,
                a.confirm_user,
                a.confirm_time,
                a.create_by,
                a.create_name,
                a.create_time,
                a.update_by,
                a.update_time,
				a.contract_sub_name,
				a.customer_username_new,
				province_code,city_code,town_code,insurance_is_buy
    </sql>
    <sql id="employeeRegistrationPre_where">
        <if test="employeeRegistrationPre != null">
					<if test="employeeRegistrationPre.isRefuse != null and employeeRegistrationPre.isRefuse.trim() != ''">
						AND a.IS_REFUSE = #{employeeRegistrationPre.isRefuse}
					</if>
					<if test="employeeRegistrationPre.isLeave != null and employeeRegistrationPre.isLeave.trim() != ''">
						AND a.IS_LEAVE = #{employeeRegistrationPre.isLeave}
					</if>
                    <if test="employeeRegistrationPre.id != null and employeeRegistrationPre.id.trim() != ''">
                        AND a.id = #{employeeRegistrationPre.id}
                    </if>
                    <if test="employeeRegistrationPre.employeeName != null and employeeRegistrationPre.employeeName.trim() != ''">
						AND a.employee_name like CONCAT('%',#{employeeRegistrationPre.employeeName},'%')
                    </if>
                    <if test="employeeRegistrationPre.empIdcard != null and employeeRegistrationPre.empIdcard.trim() != ''">
						AND a.emp_idcard like CONCAT('%',#{employeeRegistrationPre.empIdcard},'%')
                    </if>
                    <if test="employeeRegistrationPre.empPhone != null and employeeRegistrationPre.empPhone.trim() != ''">
                        AND a.emp_phone = #{employeeRegistrationPre.empPhone}
                    </if>
                    <if test="employeeRegistrationPre.position != null and employeeRegistrationPre.position.trim() != ''">
                        AND a.position = #{employeeRegistrationPre.position}
                    </if>
                    <if test="employeeRegistrationPre.joinLeaveDate != null">
                        AND a.join_leave_date = #{employeeRegistrationPre.joinLeaveDate}
                    </if>
                    <if test="employeeRegistrationPre.statusList != null and employeeRegistrationPre.statusList.size() > 0">
						AND a.process_status in
						<foreach item="item" index="index" collection="employeeRegistrationPre.statusList" open="(" separator="," close=")">
							'${item}'
						</foreach>
                    </if>
                    <if test="employeeRegistrationPre.customerUsernameNew != null and employeeRegistrationPre.customerUsernameNew.trim() != ''">
						AND a.customer_username_new like CONCAT('%',#{employeeRegistrationPre.customerUsernameNew},'%')
                    </if>
                    <if test="employeeRegistrationPre.customerUserLoginname != null and employeeRegistrationPre.customerUserLoginname.trim() != ''">
                        AND a.customer_user_loginname = #{employeeRegistrationPre.customerUserLoginname}
                    </if>
                    <if test="employeeRegistrationPre.deptName != null and employeeRegistrationPre.deptName.trim() != ''">
                        AND a.dept_name like CONCAT('%',#{employeeRegistrationPre.deptName},'%')
                    </if>
                    <if test="employeeRegistrationPre.deptNo != null and employeeRegistrationPre.deptNo.trim() != ''">
                        AND a.dept_no = #{employeeRegistrationPre.deptNo}
                    </if>
                    <if test="employeeRegistrationPre.deptId != null and employeeRegistrationPre.deptId.trim() != ''">
                        AND a.dept_id = #{employeeRegistrationPre.deptId}
                    </if>
                    <if test="employeeRegistrationPre.empNature != null and employeeRegistrationPre.empNature.trim() != ''">
                        AND a.emp_nature = #{employeeRegistrationPre.empNature}
                    </if>
                    <if test="employeeRegistrationPre.contractType != null and employeeRegistrationPre.contractType.trim() != ''">
                        AND a.contract_type = #{employeeRegistrationPre.contractType}
                    </if>
                    <if test="employeeRegistrationPre.dataSource != null and employeeRegistrationPre.dataSource.trim() != ''">
                        AND a.data_source = #{employeeRegistrationPre.dataSource}
                    </if>
                    <if test="employeeRegistrationPre.expectedCollectionTime != null">
                        AND a.expected_collection_time = #{employeeRegistrationPre.expectedCollectionTime}
                    </if>
                    <if test="employeeRegistrationPre.serverItem != null and employeeRegistrationPre.serverItem.trim() != ''">
                        AND a.server_item = #{employeeRegistrationPre.serverItem}
                    </if>
                    <if test="employeeRegistrationPre.fileProvince != null">
                        AND a.file_province = #{employeeRegistrationPre.fileProvince}
                    </if>
                    <if test="employeeRegistrationPre.fileCity != null">
                        AND a.file_city = #{employeeRegistrationPre.fileCity}
                    </if>
                    <if test="employeeRegistrationPre.fileTown != null">
                        AND a.file_town = #{employeeRegistrationPre.fileTown}
                    </if>
                    <if test="employeeRegistrationPre.confirmUser != null and employeeRegistrationPre.confirmUser.trim() != ''">
                        AND a.confirm_user = #{employeeRegistrationPre.confirmUser}
                    </if>
					<if test="employeeRegistrationPre.authSql != null and employeeRegistrationPre.authSql.trim() != ''">
						${employeeRegistrationPre.authSql}
					</if>
					<if test="employeeRegistrationPre.deptNoList != null and employeeRegistrationPre.deptNoList.size > 0">
						AND a.dept_no in
						<foreach item="idStr" index="index" collection="employeeRegistrationPre.deptNoList" open="(" separator=","
								 close=")">
							#{idStr}
						</foreach>
					</if>
        </if>
    </sql>
    <!--employeeRegistrationPre简单分页查询-->
    <select id="getEmployeeRegistrationPrePage" resultMap="employeeRegistrationPreMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM employee_registration_pre a
        <where>
            1=1
            <include refid="employeeRegistrationPre_where"/>
        </where>
		order by a.create_time desc
    </select>

	<select id="selectExportCount" resultType="java.lang.Long">
		SELECT
		count(1)
		FROM employee_registration_pre a
		<where>
			1=1
			<include refid="employeeRegistrationPre_where"/>
		</where>
	</select>

	<!--employeeRegistrationPre导出查询-->
	<select id="selectExportList" resultMap="employeeRegistrationPreExportMap">
		SELECT
			a.employee_name,
			a.emp_idcard,
			a.emp_phone,
			a.join_leave_date,
			a.customer_username_new,
			a.dept_name,
			a.dept_no,
			a.emp_nature,
			a.contract_type,
			if(a.data_source = '1','客户端','客服端') data_source,
			a.expected_collection_time,
		case a.process_status when '0' then '短信待发送' when '1' then '信息待填写' when '2' then '信息待审核'
		when '3' then '拒绝入职' when '4' then '已完成' when '5' then '已确认离职' else '-' end process_status

		FROM employee_registration_pre a
		<where>
			1=1
			<include refid="employeeRegistrationPre_where"/>
		</where>
		order by a.create_time desc
		<if test="employeeRegistrationPre.limitStart != null">
			limit #{employeeRegistrationPre.limitStart},#{employeeRegistrationPre.limitEnd}
		</if>
	</select>

	<!--employeeRegistrationPre导出查询-->
	<select id="selectConfirmExportList" resultMap="employeeRegistrationPreInfoMap">
		SELECT
		a.dept_name,
		a.dept_no,
		a.employee_name,
		a.emp_idcard,
		a.emp_phone,
		a.position,
		a.join_leave_date,
		a.confirm_user,
		a.confirm_time,
		a.server_item,
		a.emp_nature,
		a.contract_type,
		concat(a.file_province,'/',a.file_city,if(a.file_town is null,'',concat('/',a.file_town))) as fileAdress
		FROM employee_registration_pre a
		<where>
			1=1
			<include refid="employeeRegistrationPre_where"/>
		</where>
		order by a.create_time desc
		<if test="employeeRegistrationPre.limitStart != null">
			limit #{employeeRegistrationPre.limitStart},#{employeeRegistrationPre.limitEnd}
		</if>
	</select>

	<select id="selectTempleteVo" resultMap="messageTempleteVoMap">
		SELECT
		a.model_code,
		a.sign_name,
		a.APP_ID
		from t_message_templete a
		where id = #{id}
	</select>

	<!-- 判断简档详档-优先从档案维护任务表 -->
	<select id="judegIsSimpleByPre" resultType="java.lang.String">
		select
			if(a.emp_nature is null or (a.emp_nature != '2' and (a.contract_type = '' or a.contract_type is null or a.contract_type in ('1','20','9'))),1,2) 类型1详档2简档
		from employee_registration_pre a where a.emp_idcard = #{empIdCard} and a.dept_id = #{deptId}
	</select>
	<!-- 判断简档详档-从项目档案表 -->
	<select id="judegIsSimpleByEmpProject" resultType="java.lang.String">
		select
			if(a.EMP_NATRUE is null or (a.EMP_NATRUE != '2' and (a.contract_type = '' or a.contract_type is null or a.contract_type in ('1','20','9'))),1,2) 类型1详档2简档
		from t_employee_project a where a.DELETE_FLAG = '0' and a.PROJECT_STATUS = 0 and a.emp_idcard = #{empIdCard} and a.dept_id = #{deptId}
	</select>

	<!-- 获取员工类型与档案地市-优先从档案维护任务表 -->
	<select id="getPreByPre" resultMap="employeeRegistrationPreMap">
		select
			<include refid="Base_Column_List"/>
		from employee_registration_pre a where a.emp_idcard = #{empIdCard} and a.dept_id = #{deptId}
	</select>
	<!-- 获取员工类型与档案地市-从项目档案表 -->
	<select id="getPreByEmpProject" resultMap="employeeRegistrationPreMap">
		select
			a.id,
			a.emp_natrue emp_nature,
			e.file_province province_code,
			e.file_city city_code,
			e.file_town town_code
		from t_employee_project a
		left join t_employee_info e on e.id = a.EMP_ID
		where a.DELETE_FLAG = '0' and a.PROJECT_STATUS = 0 and a.emp_idcard = #{empIdCard} and a.dept_id = #{deptId}
	</select>

	<select id="getEmployeeArchivesInfo" resultMap="getEmployeeInfoMap">
		SELECT
			a.emp_idcard as card,
			a.emp_phone as phone,
			a.dept_id as deptId,
			a.emp_name as name,
			a.warning_id as detailId
		from t_registe_warning_employee a
		where a.id = #{id}
		<if test="typeList != null and typeList.size() > 0">
			and a.type in
			<foreach item="item" index="index" collection="typeList" open="(" separator="," close=")">
				'${item}'
			</foreach>
		</if>
	</select>
</mapper>
