<?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.TCompleteMonitorMapper">

  <resultMap id="tCompleteMonitorMap" type="com.yifu.cloud.plus.v1.yifu.archives.entity.TCompleteMonitor">
    <id property="id" column="ID"/>
    <result property="unitName" column="UNIT_NAME"/>
    <result property="unitNo" column="UNIT_NO"/>
    <result property="deptName" column="DEPT_NAME"/>
    <result property="deptNo" column="DEPT_NO"/>
    <result property="deptId" column="DEPT_ID"/>
    <result property="businessPrimaryType" column="BUSINESS_PRIMARY_TYPE"/>
    <result property="businessSecondType" column="BUSINESS_SECOND_TYPE"/>
    <result property="businessThirdType" column="BUSINESS_THIRD_TYPE"/>
    <result property="inusePersonNum" column="INUSE_PERSON_NUM"/>
    <result property="isComplete" column="IS_COMPLETE"/>
    <result property="incompleteNum" column="INCOMPLETE_NUM"/>
    <result property="completePer" column="COMPLETE_PER"/>
    <result property="afterEmployment" column="AFTER_EMPLOYMENT"/>
    <result property="afterIsComplete" column="AFTER_IS_COMPLETE"/>
    <result property="afterIncompleteNum" column="AFTER_INCOMPLETE_NUM"/>
    <result property="alertUser" column="ALERT_USER"/>
  </resultMap>

	<resultMap id="employeeRegistrationProjectMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.EmployeeRegistrationProjectListVo">
		<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="processStatus" column="process_status"/>
		<result property="type" column="type"/>
		<result property="customerUsernameNew" column="customer_username"/>
		<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="empProjectId" column="empProjectId"/>
		<result property="empInfoId" column="empInfoId"/>
		<result property="deptId" column="dept_id"/>
	</resultMap>

	<resultMap id="tCompleteMonitorExport" type="com.yifu.cloud.plus.v1.yifu.archives.vo.TCompleteMonitorVo">
		<result property="unitName" column="UNIT_NAME"/>
		<result property="unitNo" column="UNIT_NO"/>
		<result property="deptName" column="DEPT_NAME"/>
		<result property="deptNo" column="DEPT_NO"/>
		<result property="businessPrimaryType" column="BUSINESS_PRIMARY_TYPE"/>
		<result property="inusePersonNum" column="INUSE_PERSON_NUM"/>
		<result property="isComplete" column="IS_COMPLETE"/>
		<result property="incompleteNum" column="INCOMPLETE_NUM"/>
		<result property="completePer" column="COMPLETE_PER"/>
		<result property="afterEmployment" column="AFTER_EMPLOYMENT"/>
		<result property="afterIsComplete" column="AFTER_IS_COMPLETE"/>
		<result property="afterIncompleteNum" column="AFTER_INCOMPLETE_NUM"/>
		<result property="alertUser" column="ALERT_USER"/>
	</resultMap>

    <sql id="Base_Column_List">
                a.ID,
                a.UNIT_NAME,
                a.UNIT_NO,
                a.DEPT_NAME,
                a.DEPT_NO,
                a.DEPT_ID,
                a.BUSINESS_PRIMARY_TYPE,
                a.BUSINESS_SECOND_TYPE,
                a.BUSINESS_THIRD_TYPE,
                a.INUSE_PERSON_NUM,
                a.IS_COMPLETE,
                a.INCOMPLETE_NUM,
                a.COMPLETE_PER,
                a.AFTER_EMPLOYMENT,
                a.AFTER_IS_COMPLETE,
                a.AFTER_INCOMPLETE_NUM,
                a.ALERT_USER
    </sql>
    <sql id="tCompleteMonitor_where">
        <if test="tCompleteMonitor != null">
                    <if test="tCompleteMonitor.id != null and tCompleteMonitor.id.trim() != ''">
                        AND a.ID = #{tCompleteMonitor.id}
                    </if>
                    <if test="tCompleteMonitor.unitName != null and tCompleteMonitor.unitName.trim() != ''">
                        AND a.UNIT_NAME like concat('%',#{tCompleteMonitor.unitName},'%')
                    </if>
                    <if test="tCompleteMonitor.unitNo != null and tCompleteMonitor.unitNo.trim() != ''">
                        AND a.UNIT_NO = #{tCompleteMonitor.unitNo}
                    </if>
                    <if test="tCompleteMonitor.deptName != null and tCompleteMonitor.deptName.trim() != ''">
                        AND a.DEPT_NAME like concat('%',#{tCompleteMonitor.deptName},'%')
                    </if>
                    <if test="tCompleteMonitor.deptNo != null and tCompleteMonitor.deptNo.trim() != ''">
                        AND a.DEPT_NO = #{tCompleteMonitor.deptNo}
                    </if>
                    <if test="tCompleteMonitor.businessThirdType != null and tCompleteMonitor.businessThirdType.trim() != ''">
                        AND a.BUSINESS_THIRD_TYPE like concat('%',#{tCompleteMonitor.businessThirdType},'%')
                    </if>
                    <if test="tCompleteMonitor.isComplete != null and tCompleteMonitor.isComplete.trim() != ''">
                        AND a.IS_COMPLETE = #{tCompleteMonitor.isComplete}
                    </if>
                    <if test="tCompleteMonitor.afterIsComplete != null and tCompleteMonitor.afterIsComplete.trim() != ''">
                        AND a.AFTER_IS_COMPLETE = #{tCompleteMonitor.afterIsComplete}
                    </if>
					<if test="tCompleteMonitor.inusePersonNumStart != null">
						AND a.INUSE_PERSON_NUM <![CDATA[ >= ]]> #{tCompleteMonitor.inusePersonNumStart}
					</if>
					<if test="tCompleteMonitor.inusePersonNumEnd != null">
						AND a.INUSE_PERSON_NUM <![CDATA[ <= ]]> #{tCompleteMonitor.inusePersonNumEnd}
					</if>
					<if test="tCompleteMonitor.authSql != null and tCompleteMonitor.authSql.trim() != ''">
						${tCompleteMonitor.authSql}
					</if>
        </if>
    </sql>

	<sql id="employeeRegistrationPre_where">
		<if test="employeeRegistrationPre != null">
			<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.EMP_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.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.dataSource != null and employeeRegistrationPre.dataSource.trim() != ''">
				AND a.PROJECT_SOURCE = #{employeeRegistrationPre.dataSource}
			</if>
			<if test="employeeRegistrationPre.customerUsernameNew != null and employeeRegistrationPre.customerUsernameNew.trim() != ''">
				AND c.CS_NAME like CONCAT('%',#{employeeRegistrationPre.customerUsernameNew},'%')
			</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>


    <!--tCompleteMonitor简单分页查询-->
    <select id="getTCompleteMonitorPage" resultMap="tCompleteMonitorMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_complete_monitor a
        <where>
            1=1
            <include refid="tCompleteMonitor_where"/>
        </where>
    </select>

	<!--tCompleteMonitor简单分页查询-->
	<select id="getAllLackArchivesPage" resultMap="employeeRegistrationProjectMap">
		SELECT
		a.ID as empProjectId,
		a.EMP_NATRUE as emp_nature,
		a.EMP_NAME as employee_name,
		a.EMP_IDCARD as emp_idcard,
		a.DEPT_NAME as dept_name,
		a.DEPT_NO as dept_no,
		a.CONTRACT_TYPE as contract_type,
		if(ifnull(CONTRACT_TYPE,-1) != -1 and CONTRACT_TYPE !='' and ifnull(WORKING_HOURS,-1) != -1 and WORKING_HOURS !=''
		and ifnull(POST,-1) != -1 and POST !='' and ifnull(ENJOIN_DATE,-1) != -1 and ifnull(TRY_PERIOD,-1) != -1 and TRY_PERIOD !='' ,'基本信息','合同信息') as type,
		'1' as process_status,
		a.ENJOIN_DATE as join_leave_date,
		a.PROJECT_SOURCE as data_source,
		c.CS_NAME as customer_username,
		b.EMP_PHONE as emp_phone,
		b.id as empInfoId,
		a.DEPT_ID
		FROM t_employee_project a
		inner join t_employee_info b on a.EMP_ID = b.id
		inner join t_settle_domain c on a.DEPT_NO = c.DEPART_NO
		<where>
			a.delete_flag = '0' and a.IS_COMPLETE = '1' and a.project_status = 0
			<include refid="employeeRegistrationPre_where"/>
		</where>
		order by a.CREATE_TIME desc
	</select>


	<!--tCompleteMonitor简单分页查询-->
	<select id="getAllLackArchivesExportCount" resultType="java.lang.Long">
		SELECT
		count(1)
		FROM t_employee_project a
		inner join t_employee_info b on a.EMP_ID = b.id
		inner join t_settle_domain c on a.DEPT_NO = c.DEPART_NO
		<where>
			a.delete_flag = '0' and a.IS_COMPLETE = '1'  and a.project_status = 0
			<include refid="employeeRegistrationPre_where"/>
		</where>
	</select>


	<!--tCompleteMonitor简单分页查询-->
	<select id="getAllLackArchivesExportList" resultMap="employeeRegistrationProjectMap">
		SELECT
		a.EMP_NATRUE as emp_nature,
		a.EMP_NAME as employee_name,
		a.EMP_IDCARD as emp_idcard,
		a.DEPT_NAME as dept_name,
		a.DEPT_NO as dept_no,
		a.CONTRACT_TYPE as contract_type,
		if(ifnull(CONTRACT_TYPE,-1) != -1 and CONTRACT_TYPE !='' and ifnull(WORKING_HOURS,-1) != -1 and WORKING_HOURS !=''
		and ifnull(POST,-1) != -1 and POST !='' and ifnull(ENJOIN_DATE,-1) != -1 and ifnull(TRY_PERIOD,-1) != -1 and TRY_PERIOD !='' ,'基本信息','合同信息') as type,
		'待完善' as process_status,
		DATE_FORMAT(a.ENJOIN_DATE, '%Y-%m-%d') as join_leave_date,
		CASE WHEN a.PROJECT_SOURCE='1' THEN "社保/公积金"
		WHEN a.PROJECT_SOURCE ='2' THEN "薪酬"
		WHEN a.PROJECT_SOURCE ='3' THEN "商险"
		WHEN a.PROJECT_SOURCE ='4' THEN "人员档案新建"
		WHEN a.PROJECT_SOURCE ='5' THEN "项目档案新建"
		WHEN a.PROJECT_SOURCE ='6' THEN "批量导入"
		WHEN a.PROJECT_SOURCE ='7' THEN "电子签"
		WHEN a.PROJECT_SOURCE ='8' THEN "预入职"
		ELSE a.PROJECT_SOURCE END as data_source,
		c.CS_NAME as customer_username,
		b.EMP_PHONE as emp_phone
		FROM t_employee_project a
		inner join t_employee_info b on a.EMP_ID = b.id
		inner join t_settle_domain c on a.DEPT_NO = c.DEPART_NO
		<where>
			a.delete_flag = '0' and a.IS_COMPLETE = '1' and a.project_status = 0
			<include refid="employeeRegistrationPre_where"/>
		</where>
		order by a.CREATE_TIME desc
	</select>

	<!--tCompleteMonitor简单分页查询-->
	<select id="getExportCount" resultType="java.lang.Long">
		SELECT
		count(1)
		FROM t_complete_monitor a
		<where>
			1=1
			<include refid="tCompleteMonitor_where"/>
		</where>
	</select>

	<!--tCompleteMonitor简单分页查询-->
	<select id="getTCompleteMonitorExportList" resultMap="tCompleteMonitorExport">
		SELECT
		a.UNIT_NAME,
		a.UNIT_NO,
		a.DEPT_NAME,
		a.DEPT_NO,
		concat(BUSINESS_PRIMARY_TYPE,'-',BUSINESS_SECOND_TYPE,'-',BUSINESS_THIRD_TYPE) as BUSINESS_PRIMARY_TYPE,
		a.INUSE_PERSON_NUM,
		a.IS_COMPLETE,
		a.INCOMPLETE_NUM,
		a.COMPLETE_PER,
		a.AFTER_EMPLOYMENT,
		a.AFTER_IS_COMPLETE,
		a.AFTER_INCOMPLETE_NUM,
		a.ALERT_USER
		FROM t_complete_monitor a
		<where>
			1=1
			<include refid="tCompleteMonitor_where"/>
		</where>
		<if test="tCompleteMonitor.limitStart != null">
			limit #{tCompleteMonitor.limitStart},#{tCompleteMonitor.limitEnd}
		</if>
	</select>

	<!--获取详情人员列表-->
	<select id="getMonitorEmpInfoList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TCompleteMonitorInfoVo">
		select
			a.EMP_NAME empName,
			a.EMP_IDCARD empIdCard,
			b.EMP_PHONE phone,
			a.CREATE_TIME inTime,
			if(a.IS_COMPLETE = '0','是','否') isComplete,
			a.EMP_ID empInfoId,
			a.ID empProjectId
		from
			t_employee_project a
			left join t_employee_info b on a.EMP_ID = b.id
		where a.DELETE_FLAG = '0'
		  and a.project_status = 0
		<if test="completeMonitorInfoVo.deptNo != null and completeMonitorInfoVo.deptNo.trim() != ''">
			and a.dept_no = #{completeMonitorInfoVo.deptNo}
		</if>
		<if test="completeMonitorInfoVo.empName != null and completeMonitorInfoVo.empName.trim() != ''">
			and a.EMP_NAME = #{completeMonitorInfoVo.empName}
		</if>
		<if test="completeMonitorInfoVo.phone != null and completeMonitorInfoVo.phone.trim() != ''">
			and b.EMP_PHONE = #{completeMonitorInfoVo.phone}
		</if>
		<if test="completeMonitorInfoVo.empIdCard != null and completeMonitorInfoVo.empIdCard.trim() != ''">
			and a.EMP_IDCARD = #{completeMonitorInfoVo.empIdCard}
		</if>
		<if test="completeMonitorInfoVo.isComplete != null and completeMonitorInfoVo.isComplete.trim() != ''">
			and a.IS_COMPLETE = #{completeMonitorInfoVo.isComplete}
		</if>
		<if test="completeMonitorInfoVo.inTimeStart != null">
			AND a.CREATE_TIME <![CDATA[ >= ]]> #{completeMonitorInfoVo.inTimeStart}
		</if>
		<if test="completeMonitorInfoVo.inTimeEnd != null">
			AND a.CREATE_TIME <![CDATA[ <= ]]> #{completeMonitorInfoVo.inTimeEnd}
		</if>
		order by a.CREATE_TIME desc
	</select>

	<!--生成数据封装-->
	<select id="getAllMonitorEmpList" resultMap="tCompleteMonitorMap">
		select
		b.CUSTOMER_NAME UNIT_NAME,
		b.CUSTOMER_NO UNIT_NO,
		b.DEPART_NAME DEPT_NAME,
		b.DEPART_NO DEPT_NO,
		b.id DEPT_ID,
		b.BUSINESS_PRIMARY_TYPE,
		b.BUSINESS_SECOND_TYPE,
		b.BUSINESS_THIRD_TYPE,
		ifnull(a.INUSE_PERSON_NUM,0) INUSE_PERSON_NUM,
		ifnull(a.IS_COMPLETE,'0') IS_COMPLETE,
		ifnull(a.INCOMPLETE_NUM,0) INCOMPLETE_NUM,
		ifnull(a.COMPLETE_PER,'0.00%') COMPLETE_PER,
		ifnull(a.AFTER_EMPLOYMENT,0) AFTER_EMPLOYMENT,
		ifnull(a.AFTER_IS_COMPLETE,'0') AFTER_IS_COMPLETE,
		ifnull(a.AFTER_INCOMPLETE_NUM,0) AFTER_INCOMPLETE_NUM,
		b.CREATE_USER_NAME as ALERT_USER
		from
		t_settle_domain b
		left join (select DEPT_NO,
		sum(if(PROJECT_STATUS = 0,1,0)) as INUSE_PERSON_NUM,
		if(sum(if((PROJECT_STATUS = 0 and IS_COMPLETE = '0') or PROJECT_STATUS = 1,0,1)) > 0,1,0) as IS_COMPLETE,
		sum(if((PROJECT_STATUS = 0 and IS_COMPLETE = '0') or PROJECT_STATUS = 1,0,1)) as INCOMPLETE_NUM,
		concat(ROUND((sum(if(PROJECT_STATUS = 0,1,0)) - sum(if((PROJECT_STATUS = 0 and IS_COMPLETE = '0') or PROJECT_STATUS = 1,0,1)))*100/sum(if(PROJECT_STATUS = 0,1,0)),2),'%') as COMPLETE_PER,
		sum(if(PROJECT_STATUS = 0 and DATE_FORMAT(CREATE_TIME,'%Y-%m-%d') >= '2024-10-01',1,0)) as AFTER_EMPLOYMENT,
		if(sum(if((PROJECT_STATUS = 0 and DATE_FORMAT(CREATE_TIME,'%Y-%m-%d') >= '2024-10-01' and IS_COMPLETE = '0') or '2024-10-01' > DATE_FORMAT(CREATE_TIME,'%Y-%m-%d') or PROJECT_STATUS = 1,0,1)) > 0,1,0) AFTER_IS_COMPLETE,
		sum(if(PROJECT_STATUS = 0 and DATE_FORMAT(CREATE_TIME,'%Y-%m-%d') >= '2024-10-01' and IS_COMPLETE = '1',1,0)) as AFTER_INCOMPLETE_NUM
		from t_employee_project where DELETE_FLAG = '0' and PROJECT_STATUS = 0 group by DEPT_NO) a on a.DEPT_NO = b.DEPART_NO
		where b.DELETE_FLAG = '0'
		<if test="deptNo != null and deptNo.trim() != ''">
			and b.DEPART_NO = #{deptNo}
		</if>
		group by b.DEPART_NO
	</select>


	<!--判断项目档案的完整状态-->
	<select id="checkEmpProjectCompleteFlag" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TCompleteMonitorFlagVo">
		select
			e.EMP_SEX empSex,
			e.VALIDITY_START validityStart,
			e.VALIDITY_END validityEnd,
			e.EMP_BIRTHDAY empBirthday,
			e.EMP_AGE empAge,
			e.EMP_MARRI_STATUS empMarriStatus,
			e.EMP_NATIONAL empNational,
			e.POLITICAL_STATUS politicalStatus,
			e.EMP_PHONE empPhone,
			e.EMP_REGIS_TYPE empRegisType,
			e.FILE_PROVINCE fileProvince,
			e.IS_COLLEGE isCollege,
			e.HIGN_EDUCATION hignEducation,
			if(e.HIGN_EDUCATION != '5' and e.HIGN_EDUCATION != '6' and e.HIGN_EDUCATION != '7' and e.HIGN_EDUCATION != '8','1',e.SCHOOL) SCHOOL,
		    if(e.HIGN_EDUCATION != '5' and e.HIGN_EDUCATION != '6' and e.HIGN_EDUCATION != '7' and e.HIGN_EDUCATION != '8','1',e.MAJOR) MAJOR,
			if(e.HIGN_EDUCATION != '5' and e.HIGN_EDUCATION != '6' and e.HIGN_EDUCATION != '7' and e.HIGN_EDUCATION != '8','2022-05-13',e.ADMISSION_DATE) admissionDate,
			if(e.HIGN_EDUCATION != '5' and e.HIGN_EDUCATION != '6' and e.HIGN_EDUCATION != '7' and e.HIGN_EDUCATION != '8','2022-05-13',e.GRADUTION_DATE) gradutionDate,
			e.CONTACT_ADDRESS contactAddress,
			e.FIRST_WORK_FLAG firstWorkFlag,
			e.HAVE_QUALIFICATION haveQualification,

			a.DEPT_NAME deptName,
			a.UNIT_NAME unitName,
			a.BUSINESS_PRIMARY_TYPE businessPrimaryType,
			a.BUSINESS_SECOND_TYPE businessSecondType,
			a.BUSINESS_THIRD_TYPE businessThirdType,
			a.WORKING_HOURS workingHours,
			a.TRY_PERIOD tryPeriod,
			a.ENJOIN_DATE enjoinDate,
			a.POST,
			a.id as empProjectId,

			d.id as contactInfoId,

			if(ifnull(f.OCCUPATIONAL_DISEASE_FLAG,'6') != '6' and ifnull(f.INFECTIOUS_DISEASE_FLAG,'6') != '6' and ifnull(f.DISABILITY_FLAG,'6') != '6' and ifnull(f.OTHER_FLAG,'6') != '6','1',null) as disabilityFlag,

			if(ifnull(b.REMARK,1) != 1,'1',null) as badRecordFlag,

			if(e.HIGN_EDUCATION != '5' and e.HIGN_EDUCATION != '6' and e.HIGN_EDUCATION != '7' and e.HIGN_EDUCATION != '8','1',if(c.count >0,'1',null)) as educationFlag,

			if(g.count >0,'1',null) as familyFlag

			,if(e.BANK_NAME='',null,e.BANK_NAME) bankName
			,if(e.BANK_NO='',null,e.BANK_NO) bankNo
			,if(e.BANK_PROVINCE='',null,e.BANK_PROVINCE) bankProvince
			,if(e.BANK_CITY='',null,e.BANK_CITY) bankCity
		from
			t_employee_project a
				left join (
				select
					ee.ID,
					ee.EMP_SEX,
					ee.VALIDITY_START,
					ee.VALIDITY_END,
					ee.EMP_BIRTHDAY,
					ee.EMP_AGE,
					ee.EMP_MARRI_STATUS,
					ee.EMP_NATIONAL,
					ee.POLITICAL_STATUS,
					ee.EMP_PHONE,
					ee.EMP_REGIS_TYPE,
					ee.FILE_PROVINCE,
					ee.IS_COLLEGE,
					ee.HIGN_EDUCATION,
					ee.SCHOOL,
					ee.MAJOR,
					ee.ADMISSION_DATE,
					ee.GRADUTION_DATE,
					ee.CONTACT_ADDRESS,
					ee.FIRST_WORK_FLAG,
					ee.HAVE_QUALIFICATION
					,ee.BANK_NAME
					,ee.BANK_NO
					,ee.BANK_PROVINCE
					,ee.BANK_CITY
				from t_employee_info ee
						 left join t_atta_info tt on ee.id = tt.DOMAIN_ID and tt.RELATION_TYPE = '9'
				where ee.DELETE_FLAG = '0' and ee.FILE_STATUS = 0 and tt.id is not null
				group by ee.ID
			) e on a.EMP_ID = e.ID

				left join t_emp_bad_record b on a.EMP_IDCARD = b.EMP_IDCARD
				left join (select EMP_ID,sum(if(ifnull(HIGH_IDENTIFICATION,-1) != -1 and ifnull(SCHOOL,-1) != -1 and ifnull(MAJOR,-1) != -1 and ifnull(ENTRY_DATE,-1) != -1 and ifnull(GRADUTION_DATE,-1) != -1,1,0)) as count from t_emp_education a left join t_atta_info b on a.id = b.domain_id where a.DELETE_FLAG = '0' and b.RELATION_TYPE = '0' group by a.EMP_ID) c on a.EMP_ID = c.EMP_ID
				left join t_emp_contact_info d on a.EMP_IDCARD  = d.EMP_IDCARD
				left join t_emp_disability_info f on a.EMP_ID = f.EMP_ID and f.DELETE_FLAG = '0'
				left join (select EMP_ID,sum(if(ifnull(RELATIONSHIP_SELF,-1) != -1 and ifnull(FAMILY_NAME,-1) != -1 and ifnull(CONTRACT_TEL,-1) != -1,1,0)) as count from t_emp_family where DELETE_FLAG = '0' group by EMP_ID) g on a.EMP_ID = g.EMP_ID
		where a.DELETE_FLAG = '0' and a.PROJECT_STATUS = 0
		<if test="deptNo != null and deptNo.trim() != ''">
			and a.DEPT_NO = #{deptNo}
		</if>
		group by a.ID
	</select>

	<!--删除所有监控数据-->
	<update id="deleteAllMonitorInfo">
		TRUNCATE t_complete_monitor
	</update>

	<!--根据项目编码删除监控数据-->
	<delete id="deleteMonitorInfoByDeptNo">
		delete from t_complete_monitor WHERE DEPT_NO = #{deptNo}
	</delete>


	<!-- 导出详情 -->
	<select id="getTEmpMainAllCount" resultType="java.lang.Integer">
		SELECT
		count(1)
		FROM t_complete_monitor a
		left join t_employee_project p on a.DEPT_NO=p.DEPT_NO
		left join t_employee_info e on p.EMP_ID=e.id
		<where>
			p.DELETE_FLAG = '0' and p.PROJECT_STATUS = 0
			<include refid="tCompleteMonitor_where"/>
		</where>
	</select>

	<!--导出统计list-->
	<select id="getTEmpMainAllList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
		p.EMP_ID mainId,if(p.IS_COMPLETE=0,'是','否') field1
		,DATE_FORMAT(p.create_time,'%Y-%m-%d') field2,e.EMP_CODE field3,e.EMP_NATRUE field4,e.emp_name field5,e.emp_idcard field6
		,if(e.EMP_SEX='2','女',if(e.EMP_SEX='1','男','-')) field7
		,if(e.VALIDITY_END is null,'-',if(e.VALIDITY_END > '2999-12-30','长期','固定')) field8	,e.VALIDITY_START field9
		,if(e.VALIDITY_END > '2999-12-30','长期',e.VALIDITY_END) field10,e.EMP_BIRTHDAY field11,e.EMP_AGE field12,e.EMP_MARRI_STATUS field13,e.EMP_NATIONAL field14
		,e.POLITICAL_STATUS field15,e.EMP_PHONE field16,e.EMP_EMAIL field17
		,e.ID_PROVINCE field18,e.ID_CITY field69,e.ID_TOWN field70,e.ID_PROVINCE field18,e.EMP_REGIS_TYPE field19
		,e.FILE_PROVINCE field20,e.FILE_CITY field71,e.FILE_TOWN field72,if(e.status=0,'草稿','已审核') field21
		,e.CONTACT_ADDRESS field22,p.UNIT_NAME field23,p.DEPT_NAME field24,p.DEPT_NO field64
		,concat(p.BUSINESS_PRIMARY_TYPE,'-',ifnull(p.BUSINESS_SECOND_TYPE,''),'-',ifnull(p.BUSINESS_THIRD_TYPE,'')) field25
		,p.CONTRACT_TYPE field26,p.WORKING_HOURS field27,p.POST field28,p.ENJOIN_DATE field29,p.TRY_PERIOD field30
		,p.EMP_LABEL field31
		,ec.EMP_NAME field32,ec.RELATION_TYPE field33,ec.ADDRESS field34,ec.TEL field35
		,if(e.IS_COLLEGE=0,'否','是') field39
		,e.HIGN_EDUCATION field40
		,if(e.FIRST_WORK_FLAG is null,'',if(e.FIRST_WORK_FLAG=0,'是','否')) field43
		,if(e.HAVE_QUALIFICATION is null,'',if(e.HAVE_QUALIFICATION=0,'是','否')) field51
		,e.BANK_NO field73,e.BANK_NAME field74,e.BANK_PROVINCE field75,e.BANK_CITY field76,e.BANK_SUB_NAME field77,e.TAX_MONTH field78
		FROM t_complete_monitor a
		left join t_employee_project p on a.DEPT_NO=p.DEPT_NO
		left join t_employee_info e on p.EMP_ID=e.id
		left join (select contact.EMP_NAME ,contact.RELATION_TYPE,contact.ADDRESS,contact.TEL,contact.EMP_ID from
		t_emp_contact_info contact group by contact.EMP_ID) ec on e.id=ec.EMP_ID
		<where>
			p.DELETE_FLAG = '0' and p.PROJECT_STATUS = 0
			<include refid="tCompleteMonitor_where"/>
		</where>
		<if test="tCompleteMonitor != null">
			<if test="tCompleteMonitor.limitStart != null">
				limit #{tCompleteMonitor.limitStart},#{tCompleteMonitor.limitEnd}
			</if>
		</if>
	</select>

	<select id="getTEmpMainAllListByContract" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
			ee.EMP_ID mainId,ee.contract_name field26,ee.WORKING_HOURS field27,ee.POST field28,ee.CONTRACT_START field29,ee.TRY_PERIOD field30
		from t_employee_contract_info ee
		where ee.IN_USE='0' and ee.delete_flag = '0' and ee.audit_status = '2' and ee.EMP_ID in
		<foreach collection="empIdList" item="ids" open="(" separator="," close=")">
			#{ids}
		</foreach>
		ORDER BY ee.create_time desc
	</select>

	<select id="getTEmpMainAllListByEducation" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
			ee.EMP_ID mainId,ee.SCHOOL field36,ee.MAJOR field37,ee.EDUCATION_NAME field38,ee.ENTRY_DATE field41,ee.GRADUTION_DATE field42
		from t_emp_education ee
		where ee.DELETE_FLAG = '0' and ee.EMP_ID in
		<foreach collection="empIdList" item="ids" open="(" separator="," close=")">
			#{ids}
		</foreach>
		ORDER BY ee.create_time desc
	</select>

	<select id="getTEmpMainAllListByBad" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
			ee.EMP_ID mainId,ee.REMARK field63
		from t_emp_bad_record ee
		where ee.REMARK is not null  and ee.EMP_ID in
		<foreach collection="empIdList" item="ids" open="(" separator="," close=")">
			#{ids}
		</foreach>
		ORDER BY ee.create_time desc
	</select>

	<select id="getTEmpMainAllListByDisability" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
			ee.EMP_ID mainId,if(ee.INJURY_IDENTIFICATION=0,'是','否') field54,if(ee.OCCUPATIONAL_DISEASE_FLAG=0,'是','否') field55	,ee.OCCUPATIONAL_DISEASE field56
		,if(ee.INFECTIOUS_DISEASE_FLAG=0,'是','否') field57	,ee.INFECTIOUS_NAME field58	,if(ee.DISABILITY_FLAG=0,'是','否') field59
		,ee.DISABILITY_NAME field60,ee.DISABILITY_LEVEL field61,if(ee.OTHER_FLAG=0,'是','否') field62
		from t_emp_disability_info ee
		where ee.DELETE_FLAG = '0'  and ee.EMP_ID in
		<foreach collection="empIdList" item="ids" open="(" separator="," close=")">
			#{ids}
		</foreach>
		ORDER BY ee.create_time desc
	</select>

	<select id="getTEmpMainAllListByWork" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
			ee.EMP_ID mainId,ee.WORK_UNIT field44,ee.WORK_JOB field45,ee.START_DATE field46,ee.END_DATE field47
		from t_emp_work_recording ee
		where ee.EMP_ID in
		<foreach collection="empIdList" item="ids" open="(" separator="," close=")">
			#{ids}
		</foreach>
		ORDER BY ee.create_time desc
	</select>

	<select id="getTEmpMainAllListByFamily" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
			ee.EMP_ID mainId,ee.FAMILY_NAME field48,ee.RELATIONSHIP_SELF field49,ee.CONTRACT_TEL field50
		FROM t_emp_family ee
		where ee.EMP_ID in
		<foreach collection="empIdList" item="ids" open="(" separator="," close=")">
			#{ids}
		</foreach>
		ORDER BY ee.create_time desc
	</select>

	<select id="getTEmpMainAllListByProfessional" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
			ee.EMP_ID mainId,ee.DECLARE_YEAR field52,ee.QUALIFICATION_TYPE field53
		FROM t_emp_professional_qualification ee
		where ee.EMP_ID in
		<foreach collection="empIdList" item="ids" open="(" separator="," close=")">
			#{ids}
		</foreach>
		ORDER BY ee.create_time desc
	</select>

	<!-- 数量 -->
	<select id="getTEmpMainAllCountOneWork" resultType="java.lang.Integer">
		SELECT
		count(DISTINCT ew.id)
		FROM t_complete_monitor a
		left join t_employee_project p on a.DEPT_NO=p.DEPT_NO
		left join t_employee_info e on p.EMP_ID=e.id
		left join t_emp_work_recording ew on e.id=ew.EMP_ID
		<where>
			p.DELETE_FLAG = '0' and p.PROJECT_STATUS = 0 and ew.id is not null and ew.DELETE_FLAG = '0' and EXISTS (
			select 1 from (select a.emp_id from t_emp_work_recording a GROUP BY a.EMP_ID HAVING count(1)>1) ea where
			ea.emp_id=ew.emp_id
			)
			<include refid="tCompleteMonitor_where"/>
		</where>
	</select>

	<!--导出统计list1主要工作经历-->
	<select id="getTEmpMainAllListOneWork" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
		e.emp_idcard field6
		,if(e.FIRST_WORK_FLAG is null,'',if(e.FIRST_WORK_FLAG=0,'是','否')) field43,ew.WORK_UNIT field44,ew.WORK_JOB field45,ew.START_DATE field46,ew.END_DATE field47
		FROM t_complete_monitor a
		left join t_employee_project p on a.DEPT_NO=p.DEPT_NO
		left join t_employee_info e on p.EMP_ID=e.id
		left join t_emp_work_recording ew on e.id=ew.EMP_ID
		<where>
			p.DELETE_FLAG = '0' and p.PROJECT_STATUS = 0 and ew.id is not null and ew.DELETE_FLAG = '0' and EXISTS (
			select 1 from (select a.emp_id from t_emp_work_recording a GROUP BY a.EMP_ID HAVING count(1)>1) ea where ea.emp_id=ew.emp_id
			)
			<include refid="tCompleteMonitor_where"/>
		</where>
		group  by ew.id ORDER BY e.emp_idcard DESC
		<if test="tCompleteMonitor != null">
			<if test="tCompleteMonitor.limitStart != null">
				limit #{tCompleteMonitor.limitStart},#{tCompleteMonitor.limitEnd}
			</if>
		</if>
	</select>

	<!-- 数量 -->
	<select id="getTEmpMainAllCountTwoFamily" resultType="java.lang.Integer">
		SELECT
		count(DISTINCT ef.id)
		FROM t_complete_monitor a
		left join t_employee_project p on a.DEPT_NO=p.DEPT_NO
		left join t_employee_info e on p.EMP_ID=e.id
		left join t_emp_family ef on e.id=ef.EMP_ID
		<where>
			p.DELETE_FLAG = '0' and p.PROJECT_STATUS = 0 and ef.id is not null and ef.DELETE_FLAG = '0' and EXISTS (
			select 1 from (select a.emp_id from t_emp_family a GROUP BY a.EMP_ID HAVING count(1)>1) ea where ea.emp_id=ef.emp_id
			)
			<include refid="tCompleteMonitor_where"/>
		</where>
	</select>
	<!--导出统计list2主要家庭成员信息-->
	<select id="getTEmpMainAllListTwoFamily" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
		e.emp_idcard field6
		,ef.FAMILY_NAME field48,ef.RELATIONSHIP_SELF field49,ef.CONTRACT_TEL field50
		FROM t_complete_monitor a
		left join t_employee_project p on a.DEPT_NO=p.DEPT_NO
		left join t_employee_info e on p.EMP_ID=e.id
		left join t_emp_family ef on e.id=ef.EMP_ID
		<where>
			p.DELETE_FLAG = '0' and p.PROJECT_STATUS = 0 and ef.id is not null and ef.DELETE_FLAG = '0' and EXISTS (
			select 1 from (select a.emp_id from t_emp_family a GROUP BY a.EMP_ID HAVING count(1)>1) ea where ea.emp_id=ef.emp_id
			)
			<include refid="tCompleteMonitor_where"/>
		</where>
		group  by ef.id ORDER BY e.emp_idcard DESC
		<if test="tCompleteMonitor != null">
			<if test="tCompleteMonitor.limitStart != null">
				limit #{tCompleteMonitor.limitStart},#{tCompleteMonitor.limitEnd}
			</if>
		</if>
	</select>

	<!-- 数量 -->
	<select id="getTEmpMainAllCountThreeProfessional" resultType="java.lang.Integer">
		SELECT
		count(DISTINCT ep.id)
		FROM t_complete_monitor a
		left join t_employee_project p on a.DEPT_NO=p.DEPT_NO
		left join t_employee_info e on p.EMP_ID=e.id
		left join t_emp_professional_qualification ep on e.id=ep.EMP_ID
		<where>
			p.DELETE_FLAG = '0' and p.PROJECT_STATUS = 0 and ep.id is not null and ep.DELETE_FLAG = '0' and EXISTS (
			select 1 from (select a.emp_id from t_emp_professional_qualification a GROUP BY a.EMP_ID HAVING count(1)>1) ea where ea.emp_id=ep.emp_id
			)
			<include refid="tCompleteMonitor_where"/>
		</where>
	</select>
	<!--导出统计list3职业资格信息-->
	<select id="getTEmpMainAllListThreeProfessional" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TMainExportAllVO">
		SELECT
		e.emp_idcard field6
		,if(e.HAVE_QUALIFICATION is null,'',if(e.HAVE_QUALIFICATION=0,'是','否')) field51,ep.DECLARE_YEAR field52,ep.QUALIFICATION_TYPE field53
		FROM t_complete_monitor a
		left join t_employee_project p on a.DEPT_NO=p.DEPT_NO
		left join t_employee_info e on p.EMP_ID=e.id
		left join t_emp_professional_qualification ep on e.id=ep.EMP_ID
		<where>
			p.DELETE_FLAG = '0' and p.PROJECT_STATUS = 0 and ep.id is not null and ep.DELETE_FLAG = '0' and EXISTS (
			select 1 from (select a.emp_id from t_emp_professional_qualification a GROUP BY a.EMP_ID HAVING count(1)>1) ea where ea.emp_id=ep.emp_id
			)
			<include refid="tCompleteMonitor_where"/>
		</where>
		group  by ep.id ORDER BY e.emp_idcard DESC
		<if test="tCompleteMonitor != null">
			<if test="tCompleteMonitor.limitStart != null">
				limit #{tCompleteMonitor.limitStart},#{tCompleteMonitor.limitEnd}
			</if>
		</if>
	</select>

</mapper>
