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

  <resultMap id="tEmployeeContractPreMap" type="com.yifu.cloud.plus.v1.yifu.archives.entity.TEmployeeContractPre">
    <id property="id" column="id"/>
    <result property="deptName" column="dept_name"/>
    <result property="deptNo" column="dept_no"/>
    <result property="deptId" column="dept_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="contractType" column="contract_type"/>
    <result property="processStatus" column="process_status"/>
    <result property="signatory" column="signatory"/>
    <result property="customerUsername" column="customer_username"/>
    <result property="customerUserLoginname" column="customer_user_loginname"/>
    <result property="signType" column="sign_type"/>
    <result property="signFlag" column="sign_flag"/>
    <result property="errorInfo" column="error_info"/>
    <result property="errorTime" column="error_time"/>
    <result property="fadadaTemplate" column="fadada_template"/>
    <result property="fadadaTemplateId" column="fadada_template_id"/>
    <result property="dataSource" column="data_source"/>
    <result property="expectedCollectionTime" column="expected_collection_time"/>
    <result property="contractStart" column="contract_start"/>
    <result property="contractEnd" column="contract_end"/>
    <result property="situation" column="situation"/>
    <result property="contractDurationYear" column="contract_duration_year"/>
    <result property="contractDurationMonth" column="contract_duration_month"/>
    <result property="contractTerm" column="CONTRACT_TERM"/>
    <result property="tryPeriodStart" column="try_period_start"/>
    <result property="tryPeriodEnd" column="try_period_end"/>
	<result property="task" column="TASK"/>
	<result property="taskType" column="TASK_TYPE"/>
	<result property="taskEndStandard" column="TASK_END_STANDARD"/>
    <result property="expectedConfirmTime" column="expected_confirm_time"/>
    <result property="configName" column="config_name"/>
    <result property="updateFlag" column="update_flag"/>
    <result property="tryPeriodType" column="TRY_PERIOD_TYPE"/>
    <result property="tryPeriod" column="TRY_PERIOD"/>
    <result property="salaryType" column="SALARY_TYPE"/>
    <result property="salaryStandardPerHour" column="SALARY_STANDARD_PER_HOUR"/>
    <result property="salaryStandardPerPiece" column="SALARY_STANDARD_PER_PIECE"/>
    <result property="configId" column="config_id"/>
    <result property="workingDept" column="WORKING_DEPT"/>
    <result property="workingCompany" column="WORKING_COMPANY"/>
    <result property="workingHours" column="working_hours"/>
    <result property="workingReward" column="working_reward"/>
    <result property="riskBuyDesc" column="RISK_BUY_DESC"/>
    <result property="otherWage" column="OTHER_WAGE"/>
    <result property="laborCost" column="LABOR_COST"/>
    <result property="defaultAmount" column="DEFAULT_AMOUNT"/>
    <result property="settlementCycle" column="SETTLEMENT_CYCLE"/>
    <result property="paymentTime" column="PAYMENT_TIME"/>
    <result property="paymentType" column="PAYMENT_TYPE"/>
    <result property="dispatchPeriodYear" column="DISPATCH_PERIOD_YEAR"/>
    <result property="dispatchPeriodMonth" column="DISPATCH_PERIOD_MONTH"/>
    <result property="dispatchPeriodStart" column="DISPATCH_PERIOD_START"/>
	<result property="registerId" column="register_id"/>
	<result property="contractFlag" column="contract_flag"/>
	<result property="revokeReason" column="revoke_reason"/>
    <result property="dispatchPeriodEnd" column="DISPATCH_PERIOD_END"/>
    <result property="workType" column="WORK_TYPE"/>
    <result property="workDayCount" column="WORK_DAY_COUNT"/>
    <result property="workDay" column="WORK_DAY"/>
    <result property="workHours" column="WORK_HOURS"/>
    <result property="otherWorkDay" column="OTHER_WORK_DAY"/>
    <result property="internshipPeriod" column="INTERNSHIP_PERIOD"/>
    <result property="internshipPeriodStart" column="INTERNSHIP_PERIOD_START"/>
    <result property="internshipPeriodEnd" column="INTERNSHIP_PERIOD_END"/>
	<result property="confirmEmpSelect" column="CONFIRM_EMP_SELECT"/>
	<result property="timeoutElecSign" column="TIMEOUT_ELEC_SIGN"/>
	<result property="contractEndType" column="CONTRACT_END_TYPE"/>
    <result property="postType" column="POST_TYPE"/>
    <result property="post" column="POST"/>
    <result property="contractId" column="contract_id"/>
    <result property="workAddress" column="WORK_ADDRESS"/>
    <result property="updateTime" column="update_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="contractSubName" column="CONTRACT_SUB_NAME"/>
    <result property="requestId" column="request_id"/>
    <result property="requestMsg" column="request_msg"/>
    <result property="overFlag" column="over_flag"/>
    <result property="changeTypeReason" column="change_type_reason"/>
    <result property="changeTypeUser" column="change_type_user"/>
    <result property="changeTypeTime" column="change_type_time"/>
    <result property="archivesIsCreate" column="archives_is_create"/>
  </resultMap>
    <sql id="Base_Column_List">
			a.id
			,a.dept_name
			,a.dept_no
			,a.dept_id
			,a.employee_name
			,a.emp_idcard
			,a.emp_phone
			,a.position
			,a.join_leave_date
			,a.contract_type
			,a.process_status
			,a.signatory
			,a.customer_username
			,a.customer_user_loginname
			,a.sign_type
			,a.fadada_template
			,a.fadada_template_id
			,a.data_source
			,a.expected_confirm_time
			,a.expected_collection_time
			,a.contract_start
			,a.contract_end
			,a.situation
			,a.try_period_start
			,a.try_period_end
			,a.config_name
			,a.update_flag
			,a.try_period_type
			,a.try_period
			,a.salary_type
			,a.salary_standard_per_hour
			,a.salary_standard_per_piece
			,a.config_id
			,a.working_dept
			,a.working_company
			,a.risk_buy_desc
			,a.other_wage
			,a.labor_cost
			,a.default_amount
			,a.settlement_cycle
			,a.payment_time
			,a.payment_type
			,a.dispatch_period_start
			,a.dispatch_period_end
			,a.work_type
			,a.work_day_count
			,a.work_day
			,a.work_hours
			,a.other_work_day
			,a.internship_period
			,a.internship_period_start
			,a.internship_period_end
			,a.post_type
			,a.post
			,a.work_address
			,a.contract_term
			,a.contract_duration_year
			,a.contract_duration_month
			,a.register_id
			,a.sign_flag
			,a.error_info
			,a.contract_id
			,a.revoke_reason
			,a.working_reward
			,a.working_hours
			,a.dispatch_period_year
			,a.dispatch_period_month
			,a.error_time
			,a.contract_end_type
			,a.confirm_emp_select
			,a.timeout_elec_sign
			,a.update_time
			,a.create_by
			,a.create_name
			,a.create_time
			,a.update_by
		    ,a.TASK
		    ,a.TASK_TYPE
		    ,a.TASK_END_STANDARD,a.contract_flag,a.CONTRACT_SUB_NAME
			,a.request_id
			,a.request_msg,a.over_flag,a.change_type_reason,a.change_type_user,a.change_type_time
    </sql>
    <sql id="tEmployeeContractPre_where">
        <if test="tEmployeeContractPre != null">
			<if test="tEmployeeContractPre.isRefuse != null and tEmployeeContractPre.isRefuse.trim() != ''">
				AND a.IS_REFUSE = #{tEmployeeContractPre.isRefuse}
			</if>
			<if test="tEmployeeContractPre.isLeave != null and tEmployeeContractPre.isLeave.trim() != ''">
				AND a.IS_LEAVE = #{tEmployeeContractPre.isLeave}
			</if>
			<if test="tEmployeeContractPre.id != null and tEmployeeContractPre.id.trim() != ''">
				AND a.id = #{tEmployeeContractPre.id}
			</if>
			<if test="tEmployeeContractPre.idList != null">
				AND a.id in
				<foreach item="idStr" index="index" collection="tEmployeeContractPre.idList" open="(" separator=","
						 close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeContractPre.deptName != null and tEmployeeContractPre.deptName.trim() != ''">
				AND a.dept_name like CONCAT('%',#{tEmployeeContractPre.deptName},'%')
			</if>
			<if test="tEmployeeContractPre.deptNo != null and tEmployeeContractPre.deptNo.trim() != ''">
				AND a.dept_no = #{tEmployeeContractPre.deptNo}
			</if>
			<if test="tEmployeeContractPre.deptId != null and tEmployeeContractPre.deptId.trim() != ''">
				AND a.dept_id = #{tEmployeeContractPre.deptId}
			</if>
			<if test="tEmployeeContractPre.employeeName != null and tEmployeeContractPre.employeeName.trim() != ''">
				AND a.employee_name = #{tEmployeeContractPre.employeeName}
			</if>
			<if test="tEmployeeContractPre.empIdcard != null and tEmployeeContractPre.empIdcard.trim() != ''">
				AND a.emp_idcard like CONCAT('%',#{tEmployeeContractPre.empIdcard},'%')
			</if>
			<if test="tEmployeeContractPre.empPhone != null and tEmployeeContractPre.empPhone.trim() != ''">
				AND a.emp_phone = #{tEmployeeContractPre.empPhone}
			</if>
			<if test="tEmployeeContractPre.position != null and tEmployeeContractPre.position.trim() != ''">
				AND a.position like CONCAT('%',#{tEmployeeContractPre.position},'%')
			</if>
			<if test="tEmployeeContractPre.joinTimeStart != null">
				AND a.join_leave_date <![CDATA[ >= ]]> #{tEmployeeContractPre.joinTimeStart}
			</if>
			<if test="tEmployeeContractPre.joinTimeEnd != null">
				AND a.join_leave_date <![CDATA[ <= ]]> #{tEmployeeContractPre.joinTimeEnd}
			</if>
			<if test="tEmployeeContractPre.contractType != null and tEmployeeContractPre.contractType.trim() != ''">
				AND a.contract_type = #{tEmployeeContractPre.contractType}
			</if>
			<if test="tEmployeeContractPre.processStatus != null and tEmployeeContractPre.processStatus.trim() != ''">
				AND a.process_status = #{tEmployeeContractPre.processStatus}
			</if>
			<if test="tEmployeeContractPre.autoSendFlag != null and tEmployeeContractPre.autoSendFlag.trim() != ''">
				AND a.expected_confirm_time is not null and DATE_FORMAT(a.expected_confirm_time,'%Y-%m-%d') <![CDATA[ <= ]]> CURDATE()
			</if>
			<if test="tEmployeeContractPre.statusList != null and tEmployeeContractPre.statusList.size() > 0">
				AND a.process_status in
				<foreach item="item" index="index" collection="tEmployeeContractPre.statusList" open="(" separator=","
						 close=")">
					'${item}'
				</foreach>
			</if>
			<if test="tEmployeeContractPre.signatory != null and tEmployeeContractPre.signatory.trim() != ''">
				AND a.signatory like CONCAT('%',#{tEmployeeContractPre.signatory},'%')
			</if>
			<if test="tEmployeeContractPre.customerUsername != null and tEmployeeContractPre.customerUsername.trim() != ''">
				AND a.customer_username like CONCAT('%',#{tEmployeeContractPre.customerUsername},'%')
			</if>
			<if test="tEmployeeContractPre.customerUserLoginname != null and tEmployeeContractPre.customerUserLoginname.trim() != ''">
				AND a.customer_user_loginname = #{tEmployeeContractPre.customerUserLoginname}
			</if>
			<if test="tEmployeeContractPre.signType != null and tEmployeeContractPre.signType.trim() != ''">
				AND a.sign_type = #{tEmployeeContractPre.signType}
			</if>
			<if test="tEmployeeContractPre.fadadaTemplateId != null and tEmployeeContractPre.fadadaTemplateId.trim() != ''">
				AND a.fadada_template_id = #{tEmployeeContractPre.fadadaTemplateId}
			</if>
			<if test="tEmployeeContractPre.fadadaTemplate != null and tEmployeeContractPre.fadadaTemplate.trim() != ''">
				AND a.fadada_template like CONCAT('%',#{tEmployeeContractPre.fadadaTemplate},'%')
			</if>
			<if test="tEmployeeContractPre.expectedCollectionTime != null">
				AND a.expected_collection_time = #{tEmployeeContractPre.expectedCollectionTime}
			</if>
			<if test="tEmployeeContractPre.revokeReason != null and tEmployeeContractPre.revokeReason.trim() != ''">
				AND a.revoke_reason = #{tEmployeeContractPre.revokeReason}
			</if>
			<if test="tEmployeeContractPre.expectedConfirmTime != null">
				AND DATE_FORMAT(a.expected_confirm_time,"%Y-%m-%d") =
				DATE_FORMAT(#{tEmployeeContractPre.expectedConfirmTime},"%Y-%m-%d")
			</if>
			<if test="tEmployeeContractPre.authSql != null and tEmployeeContractPre.authSql.trim() != ''">
				${tEmployeeContractPre.authSql}
			</if>
			<if test="tEmployeeContractPre.deptNoList != null and tEmployeeContractPre.deptNoList.size > 0">
				AND a.dept_no in
				<foreach item="idStr" index="index" collection="tEmployeeContractPre.deptNoList" open="(" separator=","
						 close=")">
					#{idStr}
				</foreach>
			</if>
        </if>
    </sql>
    <!--tEmployeeContractPre简单分页查询-->
    <select id="getTEmployeeContractPrePage" resultMap="tEmployeeContractPreMap">
        SELECT
        <include refid="Base_Column_List"/>
		,if(b.id is null,'1','0') archives_is_create
        FROM t_employee_contract_pre a
		left join t_employee_project b on a.emp_idcard = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
		and b.PROJECT_STATUS = 0 and b.DELETE_FLAG = '0'
        <where>
            1=1
            <include refid="tEmployeeContractPre_where"/>

			<if test="tEmployeeContractPre != null">
				<if test="tEmployeeContractPre.archivesIsCreate != null and tEmployeeContractPre.archivesIsCreate.trim() != ''">
					<if test='tEmployeeContractPre.archivesIsCreate == "0" '>
						AND b.id is not null
					</if>
					<if test='tEmployeeContractPre.archivesIsCreate == "1" '>
						AND b.id is null
					</if>
				</if>
			</if>

        </where>
		order by a.create_time desc
    </select>

	<!--tEmployeeContractPre c端不分页查询-->
	<select id="getTEmployeeContractPreNoPage" resultMap="tEmployeeContractPreMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM t_employee_contract_pre a
		<where>
			1=1 and a.IS_REFUSE ='1' and a.IS_LEAVE ='1'
			<include refid="tEmployeeContractPre_where"/>
		</where>
		order by a.create_time desc
		limit 0,2000
	</select>

	<!--tEmployeeContractPre导出查询-->
	<select id="getTEmployeeContractPreExportCount" resultType="java.lang.Long">
		SELECT
		 count(1)
		FROM t_employee_contract_pre a
		    left join t_employee_project b on a.emp_idcard = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
		and b.PROJECT_STATUS = 0 and b.DELETE_FLAG = '0'
		<where>
			1=1
			<include refid="tEmployeeContractPre_where"/>
			<if test="tEmployeeContractPre != null">
				<if test="tEmployeeContractPre.archivesIsCreate != null and tEmployeeContractPre.archivesIsCreate.trim() != ''">
					<if test='tEmployeeContractPre.archivesIsCreate == "0" '>
						AND b.id is not null
					</if>
					<if test='tEmployeeContractPre.archivesIsCreate == "1" '>
						AND b.id is null
					</if>
				</if>
			</if>
		</where>
	</select>

	<!--tEmployeeContractPre导出查询-->
	<select id="getTEmployeeContractPreExport" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TEmployeeContractPreExportVo">
		SELECT
			a.dept_name,
			a.dept_no,
			a.employee_name,
			a.emp_idcard,
			a.emp_phone,
			a.position,
			DATE_FORMAT(a.join_leave_date,'%Y-%m-%d') join_leave_date,
			a.contract_type,
			a.signatory,
			a.customer_username,
			if(a.sign_type = '0','电子签','线下签') sign_type,
			a.fadada_template,
			DATE_FORMAT(a.expected_confirm_time,'%Y-%m-%d %H:%i') expected_confirm_time,
			DATE_FORMAT(a.expected_collection_time,'%Y-%m-%d %H:%i') expected_collection_time,
			CASE WHEN a.process_status=0 THEN "待确认"
			WHEN a.process_status =1 THEN "待发起"
			WHEN a.process_status =2 THEN "线下签待审核"
			WHEN a.process_status =3 THEN "线下签审核不通过"
			WHEN a.process_status =4 THEN "线下签待归档"
			WHEN a.process_status =5 THEN "发起失败"
			WHEN a.process_status =6 THEN "签署中"
			WHEN a.process_status =7 THEN "签署失败"
			WHEN a.process_status =8 THEN "电子待归档"
			WHEN a.process_status =10 THEN "撤销签署-客服撤销"
			WHEN a.process_status =11 THEN "撤销签署-系统自动撤销"
			ELSE a.process_status END as process_status,
			if(b.id is null,'否','是') archives_is_create,
		CASE WHEN a.process_status=5 THEN a.error_info
		ELSE "" end as error_info
		FROM t_employee_contract_pre a
		left join t_employee_project b on a.emp_idcard = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
		and b.PROJECT_STATUS = 0 and b.DELETE_FLAG = '0'
		<where>
			1=1
			<include refid="tEmployeeContractPre_where"/>
			<if test="tEmployeeContractPre != null">
				<if test="tEmployeeContractPre.archivesIsCreate != null and tEmployeeContractPre.archivesIsCreate.trim() != ''">
					<if test='tEmployeeContractPre.archivesIsCreate == "0" '>
						AND b.id is not null
					</if>
					<if test='tEmployeeContractPre.archivesIsCreate == "1" '>
						AND b.id is null
					</if>
				</if>
			</if>
		</where>
		order by a.create_time desc
		<if test="tEmployeeContractPre.limitStart != null">
			limit #{tEmployeeContractPre.limitStart},#{tEmployeeContractPre.limitEnd}
		</if>
	</select>

	<!--tEmployeeContractPre合同派单查询-->
	<select id="getDisPatcherContractList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.EmployeeContractVO">
		SELECT
		(@i :=@i+1) as rowIndex,
		a.employee_name empName,
		a.emp_idcard empIdcard,
		a.dept_no deptNo,
		a.situation,
		a.contract_type contractName,
		a.CONTRACT_SUB_NAME contractSubName,
		null reason,
		a.signatory contractParty,
		if(a.CONTRACT_TERM = '' or a.CONTRACT_TERM is null,'2',a.CONTRACT_TERM) contractType,
		a.CONTRACT_START as contractStart,
		if(a.contract_type = '实习协议',a.CONTRACT_END,if(a.CONTRACT_TERM ='1' ,a.CONTRACT_END,null)) as contractEnd,
		a.post,
		if(a.working_hours = '' or a.working_hours is null,'4',a.working_hours) as workingHours,
		null contractNo,
		null fileCabinetNo,
		null leaveDate,
		null reduceReason,
		null remark,
		a.sign_type signType,
		null changeContractAndEmployee,
		if(a.TRY_PERIOD = '' or a.TRY_PERIOD is null,'无',a.TRY_PERIOD) as tryPeriod,
		a.id preId,
		if(#{type} = '1',concat('自动化手动-',a.customer_username),concat('自动化自动-',a.customer_username)) preName
		FROM t_employee_contract_pre a,(select @i:=0) as itable
		<where>
			1=1  and a.IS_REFUSE ='1' and a.IS_LEAVE ='1'
			AND a.id in
			<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
			AND a.process_status in
			<foreach item="status" index="index" collection="statusList" open="(" separator="," close=")">
				#{status}
			</foreach>
		</where>
	</select>

	<select id="getAllUnconfimData" resultMap="tEmployeeContractPreMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM t_employee_contract_pre a
		where a.process_status = '0'  and a.expected_confirm_time is not null and a.IS_REFUSE ='1' and a.IS_LEAVE ='1'
		and DATE_FORMAT(a.expected_confirm_time,"%Y-%m-%d") <![CDATA[ <= ]]> CURDATE()
		group by a.customer_user_loginname
	</select>

	<select id="getAllUnDisData" resultType="java.lang.String">
		SELECT
			id
		FROM t_employee_contract_pre a
			where
			  a.expected_collection_time is not null
		  	  and DATE_FORMAT(a.expected_collection_time,"%Y-%m-%d") <![CDATA[ <= ]]> CURDATE()
		  	  and ((a.timeout_elec_sign = '0' and a.process_status = '0' and a.sign_type = '0') or
		  	  <!-- 1,3,5,7,11 改为 1 ，5 （待发起、发起失败 -->
				a.process_status in ('1','5')) and a.IS_REFUSE ='1' and a.IS_LEAVE ='1'
	</select>
</mapper>
