<?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="org.jeecg.modules.warning.mapper.ZjycsMapper">

	<resultMap id="Zjycs" type="org.jeecg.modules.warning.entity.Zjycs" >
	</resultMap>

	<select id="selectList" parameterType="hashMap" resultMap="Zjycs">
		WITH t1 AS (
			SELECT
				sj,
				bjlx,
				TO_CHAR (sj, 'yyyyMMDD') || bjlx || '_' || SUBSTR (bjxx, 0, 12) bjpart,
				trim(bjxx) bjxx
			FROM
				${table}
		),
			 tt1 AS (
				 SELECT
					 ROW_NUMBER () OVER (PARTITION BY bjpart ORDER BY sj) pn,
					 t1.*
				 FROM
					 t1
			 ),
			 tt5 AS (
				 SELECT
					 bjpart,
					 MAX (pn) maxPn
				 FROM
					 tt1
				 GROUP BY
					 bjpart
			 ),
			 tt3 AS (
				 SELECT
					 tt2.sj nSj,
					 tt5.maxpn,
					 tt1.*
				 FROM
					 tt1
						 LEFT JOIN tt1 tt2 ON tt2.pn = tt1.pn - 1
						 AND tt1.bjpart = tt2.bjpart
						 LEFT JOIN tt5 ON tt5.bjpart = tt1.bjpart
			 ),
			 tt6 AS (
				 SELECT
					 tt3.sj,
					 tt3.nsj,
					 ROUND (
							 TO_NUMBER (tt3.sj - tt3.nsj) * 24 * 60
						 ) diffM,
					 tt3.pn,
					 tt3.maxpn,
					 tt3.bjlx,
					 bjPart,
					 tt3.bjxx
				 FROM
					 tt3
				 ORDER BY
					 bjpart,
					 pn
			 ),
			 tt9 AS (
				 SELECT
					 *
				 FROM
					 tt6
				 WHERE
					 diffM > 8
					OR maxpn = pn
			 ) SELECT
			*
		FROM
			tt9
		WHERE
			TO_CHAR (SJ, 'yyyy-MM-dd') = #{time}
		ORDER BY
			sj
	</select>

	<select id="selectLtd" parameterType="hashMap" resultType="org.jeecg.modules.warning.entity.ZjycsLtd">
		select * from ${table}
		<where>
			<if test="timeStart != null and timeStart != ''">
				and SJ &gt; to_date(#{timeStart},'yyyy-mm-dd hh24:mi:ss')
			</if>
			<if test="timeEnd != null and timeEnd !=''">
				and SJ &lt;= to_date(#{timeEnd},'yyyy-mm-dd hh24:mi:ss')
			</if>
		</where>
		ORDER BY SJ
	</select>
	<select id="selectAllDate" parameterType="hashMap" resultType="java.util.Map">
		select distinct to_char(SJ,'YYYY-MM-DD') as TIME from ${table} order by TIME desc
	</select>
</mapper>