<?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.zcloud.mapper.dsno1.corp.CorpTimeMapper">

	<!--表名 -->
	<sql id="tableName">
		BUS_CORP_TIME
	</sql>

	<!--数据字典表名 -->
	<sql id="dicTableName">
		SYS_DICTIONARIES
	</sql>

	<!-- 字段 -->
	<sql id="Field">
		f.CORPINFO_ID,
		f.USE_DATE_START,
		f.USE_DATE_END,
		f.RETENTION_DATA_DATE,
		f.ISIN,
		f.ISDELETE,
		f.CREATOR,
		f.CREATTIME,
		f.OPERATOR,
		f.OPERATTIME,
		f.CORP_TIME_ID,
		f.CONTRACT_FILE,
		f.TERM
	</sql>

	<!-- 字段用于新增 -->
	<sql id="Field2">
		CORPINFO_ID,
		USE_DATE_START,
		USE_DATE_END,
		RETENTION_DATA_DATE,
		ISIN,
		ISDELETE,
		CREATOR,
		CREATTIME,
		OPERATOR,
		OPERATTIME,
		CORP_TIME_ID,
		CONTRACT_FILE,
		TERM
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{CORPINFO_ID},
		#{USE_DATE_START},
		#{USE_DATE_END},
		#{RETENTION_DATA_DATE},
		#{ISIN},
		#{ISDELETE},
		#{CREATOR},
		#{CREATTIME},
		#{OPERATOR},
		#{OPERATTIME},
		#{CORP_TIME_ID},
		#{CONTRACT_FILE},
		#{TERM}
	</sql>

	<!-- 新增-->
	<insert id="save" parameterType="pd">
		insert into
	<include refid="tableName"></include>
		(
	<include refid="Field2"></include>,NOTES
		) values (
	<include refid="FieldValue"></include>,#{NOTES}
		)
	</insert>

	<!--保存停用企业-->
	<insert id="saveStopSchedule" parameterType="pd">
		insert into	bus_corp_stop_schedule
		(
		 ID,
		 CORPINFO_ID,
		 CREATOR,
		 CREATTIME,
		 OPERATOR,
		 OPERATTIME,
		 ISDELETE,
		 STOP_REASON,
		 STOP_TIME_START,
		 STOP_TIME_END
		) values
		(
		 #{ID},
		 #{CORPINFO_ID},
		 #{CREATOR},
		 #{CREATTIME},
		 #{OPERATOR},
		 #{OPERATTIME},
		 #{ISDELETE},
		 #{STOP_REASON},
		 #{STOP_TIME_START},
		 #{STOP_TIME_END}
		)
	</insert>

	<!-- 删除-->
	<delete id="delete" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			ISDELETE = '1'
		where
			CORPINFO_ID = #{CORPINFO_ID}
	</delete>

	<!-- 修改 -->
	<update id="edit" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			CORPINFO_ID = #{CORPINFO_ID},
			USE_DATE_START = #{USE_DATE_START},
			USE_DATE_END = #{USE_DATE_END},
			RETENTION_DATA_DATE = #{RETENTION_DATA_DATE},
			ISIN = #{ISIN},
			ISDELETE = #{ISDELETE},
			OPERATTIME = #{OPERATTIME},
			OPERATOR = #{OPERATOR},
			CREATOR = #{CREATOR},
			CREATTIME = #{CREATTIME},
			CONTRACT_FILE = #{CONTRACT_FILE},
			TERM = #{TERM}
		where
			CORPINFO_ID = #{CORPINFO_ID}
	</update>

	<!-- 通过ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		where
			f.CORPINFO_ID = #{CORPINFO_ID} and f.ISDELETE = '0'
	</select>

	<!-- 列表 -->
	<select id="datalistPage" parameterType="page" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		where f.ISDELETE = '0'
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
				<!--	根据需求自己加检索条件
					字段1 LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					字段2 LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
				-->
				)
		</if>
	</select>

	<!-- 列表(全部) -->
	<select id="listAll" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
	</select>

	<!-- 获取所有企业在日期内。但是isin字段为0的数据 -->
	<select id="getInTimeList" parameterType="pd" resultType="pd">
		SELECT
			bct.*
		FROM
			bus_corp_time bct
			LEFT JOIN bus_corp_info bci ON bci.CORPINFO_ID = bct.CORPINFO_ID
		WHERE
			bci.ISDELETE = '0'
			AND bct.ISDELETE = '0'
			AND bci.ISUSE = '0'
			and bci.ISDELIVER = '1'
			and CURDATE() BETWEEN bct.USE_DATE_START and bct.USE_DATE_END
	</select>

	<!-- 批量删除 -->
	<delete id="deleteAll" parameterType="String">
		update
		<include refid="tableName"></include>
		set
			ISDELETE = '1'
		where
			CORP_TIME_ID in
		<foreach item="item" index="index" collection="ArrayDATA_IDS" open="(" separator="," close=")">
                 #{item}
		</foreach>
	</delete>

	<!-- 修改到周期内 -->
	<update id="editInPeriod" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		ISIN = '1'
		where
		CORP_TIME_ID = #{CORP_TIME_ID}
	</update>

	<!-- 修改到周期外 -->
	<update id="editOutPeriod" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		ISIN = '0'

	</update>

    <update id="updateCorpTime" parameterType="pd">
		update
		<include refid="tableName"></include>
		set CONTRACT_FILE = #{CONTRACT_FILE}
		where CORPINFO_ID = #{CORPINFO_ID} and USE_DATE_START <![CDATA[ <= ]]> NOW() and USE_DATE_END <![CDATA[ > ]]> NOW()
 	</update>

	<!-- 查询企业最终到期时间 -->
	<select id="findMaxEndDate" parameterType="pd" resultType="pd">
		SELECT
			MAX(f.USE_DATE_END) USE_DATE_END,
			MAX(f.RETENTION_DATA_DATE) RETENTION_DATA_DATE
		FROM <include refid="tableName"></include> f
		WHERE f.ISDELETE = '0'
			AND f.CORPINFO_ID = #{CORPINFO_ID}
	</select>

	<!--获取要停用的企业列表-->
	<select id="getStopCorpScheduledList" resultType="pd" parameterType="pd">
		select
			CORPINFO_ID,
			STOP_REASON,
			STOP_TIME_START,
			STOP_TIME_END,
			(case
			    when bcsc.STOP_TIME_START = DATE_FORMAT(NOW(),'%Y-%m-%d') then 'stop'
			    when bcsc.STOP_TIME_END = date_add(DATE_FORMAT(NOW(),'%Y-%m-%d'), interval -1 day) then 'enable'
			    else 'none'
			    end ) as STATUS
		from bus_corp_stop_schedule bcsc
		where bcsc.ISDELETE = '0'
	</select>
</mapper>
