<?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.yeejoin.amos.boot.module.common.api.mapper.DutyPersonShiftMapper">
	<select id="calMaxPersonAndShiftNum" resultType="java.util.Map">
		select
		(select
		IFNULL(max(num),0)
		from
		(SELECT
		ifnull(count(1),0) num
		FROM
		cb_duty_person_shift s,
		cb_duty_shift ds,
		cb_dynamic_form_instance i
		WHERE
		ds.sequence_nbr = s.shift_id
		and s.instance_id = i.instance_id
		AND i.field_code = 'userId'
		and s.duty_date >= #{beginDate}
		and s.duty_date <![CDATA[<=]]>
		#{endDate}
		AND s.shift_id is not null <!--// BUG 2807 更新人员车辆排版值班的保存逻辑 如果没有填写数据则保存空数据 。 
			同步修改 查询 导出相关逻辑 by kongfm 2021-09-14 -->
		and s.app_Key = #{appKey}
		GROUP BY s.duty_date,s.shift_id <!--增添分组条件 根据班次分组技术 -->
		) a) as maxDutyPersonNumDay,
		(select
		IFNULL(max(num),0)
		from
		(SELECT
		ifnull(count(DISTINCT(s.shift_id)),0) num
		FROM
		cb_duty_person_shift s,
		cb_duty_shift ds,
		cb_dynamic_form_instance i
		WHERE
		ds.sequence_nbr = s.shift_id
		and s.instance_id = i.instance_id
		AND i.field_code = 'userId'
		and s.duty_date >= #{beginDate}
		and s.duty_date <![CDATA[<=]]>
		#{endDate}
		and s.app_Key = #{appKey}
		GROUP BY s.duty_date) b) as maxShiftNumDay
	</select>
	<select id="bankViewData" resultType="java.util.Map">
		SELECT
		S1.*,
		S2.name as shiftName
		from
		(SELECT
		s.shift_id as shiftId,
		GROUP_CONCAT(i.field_value) AS userName
		FROM
		cb_duty_person_shift s,
		cb_duty_shift ds,
		cb_dynamic_form_instance i
		WHERE
		s.instance_id = i.instance_id
		AND ds.sequence_nbr = s.shift_id
		AND i.field_code = 'userName'
		AND s.duty_date = #{dutyDate}
		AND s.shift_id is not null
		AND s.app_key = #{appKey}
		and i.group_code =#{groupCode}
		GROUP BY
		s.shift_id
		) S1,
		cb_duty_shift S2
		where
		s1.shiftId = s2.sequence_nbr
	</select>
	<select id="stationViewData" resultType="java.util.Map">
		select
		i.field_value as postTypeName,
		count(1) as total
		from
		cb_duty_person_shift s,
		cb_dynamic_form_instance i
		where
		s.instance_id = i.instance_id
		and i.field_code = 'postTypeName'
		AND s.duty_date = #{dutyDate}
		AND s.shift_id is not null
		AND s.app_key = #{appKey}
		and i.group_code =#{groupCode}
		GROUP BY i.field_value
	</select>
	<select id="genRangeDate" resultType="map">
		SELECT
		DATE_FORMAT(DATE( DATE_ADD( #{beginDate}, INTERVAL @s DAY )),'%Y-%m-%d') AS date,
		@s := @s + 1 AS `index`
		FROM
		mysql.help_topic,
		( SELECT @s := 0 ) temp
		WHERE
		@s <![CDATA[<=]]>
		DATEDIFF(#{endDate},#{beginDate})
	</select>


	<select id='getSpecifyDateList' resultType='map'>
		SELECT
		GROUP_CONCAT(cd.field_value) AS
		value
		,
		cds.name
		FROM
		cb_dynamic_form_instance cd
		LEFT JOIN (
		SELECT
		dp.instance_id,
		ds.`name`
		FROM
		cb_duty_person_shift dp
		LEFT JOIN cb_duty_shift ds ON dp.shift_id = ds.sequence_nbr
		WHERE
		dp.duty_date = #{dutyDate}
		AND dp.is_delete = 0
		) cds ON cd.instance_id = cds.instance_id
		WHERE
		cd.group_code = #{groupCode}
		AND cds.instance_id IS NOT NULL
		AND cd.is_delete = 0
		AND cd.field_code = 'userName'
		<if test="params != null and  params!='' ">
			and cd.field_value !='消防车驾驶员'
		</if>
		AND cd.instance_id IN 
		<foreach collection ='instanceIds' item='instanceId' index='index' open="(" close= ")" separator=",">
		#{instanceId}
		</foreach>
		GROUP BY
		cds.`name`
	</select>

	<select id='getEquipmentForSpecifyDate' resultType='map'>
	select * from (
		SELECT
			MAX(
		CASE
		WHEN cd.FIELD_CODE = #{equipmentId} THEN
			cd.FIELD_VALUE
		END
	) AS #{equipmentId},
	MAX(
				CASE
				WHEN cd.FIELD_CODE = #{equipmentName} THEN
					cd.FIELD_VALUE
				END
			) AS #{equipmentName},
	
	MAX(
				CASE
				WHEN cd.FIELD_CODE = #{teamName} THEN
					cd.FIELD_VALUE
				END
			) AS #{teamName}

		FROM
		cb_dynamic_form_instance cd
		LEFT JOIN (
		SELECT
		dp.instance_id,
		ds.`name`
		FROM
		cb_duty_person_shift dp
		LEFT JOIN cb_duty_shift ds ON dp.shift_id = ds.sequence_nbr
		WHERE
		dp.duty_date = #{dutyDate} and dp.is_delete=0
		) cds ON cd.instance_id = cds.instance_id
		where cd.group_code =#{groupCode} and cds.instance_id is not null and
		cd.is_delete=0
		group by cd.instance_id
) result group by ${groupByName}
		
	</select>
	<select id='getInstanceIdForSpecifyDateAndEquipment'
		resultType='map'>
		SELECT
		GROUP_CONCAT(cd.instance_id) as instanceIds
		FROM
		cb_dynamic_form_instance cd
		LEFT JOIN (
		SELECT
		dp.instance_id,
		ds.`name`
		FROM
		cb_duty_person_shift dp
		LEFT JOIN cb_duty_shift ds ON dp.shift_id = ds.sequence_nbr
		WHERE
		dp.duty_date = #{dutyDate}
		AND dp.is_delete = 0
		) cds ON cd.instance_id = cds.instance_id
		WHERE
		cd.group_code = #{groupCode}
		AND cds.instance_id IS NOT NULL
		AND cd.is_delete = 0
		AND cd.field_value = #{targetId}
	</select>
	<select id='getEquipmentOperator' resultType='map'>
	SELECT
	result.userName	
FROM
	(
		SELECT
			MAX(
				CASE
				WHEN cd.FIELD_CODE = 'userName' THEN
					cd.FIELD_VALUE
				END
			) AS 'userName',
			MAX(
				CASE
				WHEN cd.FIELD_CODE = 'postTypeName' THEN
					cd.FIELD_VALUE
				END
			) AS 'postTypeName',
			cds. NAME
		FROM
			cb_dynamic_form_instance cd
		LEFT JOIN (
			SELECT
				dp.instance_id,
				ds.`name`
			FROM
				cb_duty_person_shift dp
			LEFT JOIN cb_duty_shift ds ON dp.shift_id = ds.sequence_nbr
			WHERE
				dp.duty_date = #{dutyDate}
			AND dp.is_delete = 0
		) cds ON cd.instance_id = cds.instance_id
		WHERE
			cd.group_code =  #{groupCode}
		AND cds.instance_id IS NOT NULL
		AND cd.is_delete = 0
		AND cd.instance_id IN 
		<foreach collection ='instanceIds' item='instanceId' index='index' open="(" close= ")" separator=",">
		#{instanceId}
		</foreach>
	) result
WHERE
	result.postTypeName = #{operator} and result.name =#{duty}
	</select>
	
	
	<select id ='getPositionStaffDutyForSpecifyDate' resultType="map">
	SELECT
	GROUP_CONCAT(ss.userName) AS userName,
	ss.postTypeName
FROM
	(
		SELECT
			MAX(
				CASE
				WHEN cd.FIELD_CODE = 'userName' THEN
					cd.FIELD_VALUE
				END
			) AS 'userName',
			MAX(
				CASE
				WHEN cd.FIELD_CODE = 'postTypeName' THEN
					cd.FIELD_VALUE
				END
			) AS 'postTypeName',
			cd.instance_id
		FROM
			cb_dynamic_form_instance cd
		WHERE
			cd.instance_id in
		<foreach collection ='instanceIds' item='instanceId' index='index' open="(" close= ")" separator=",">
			#{instanceId}
		</foreach>
		AND cd.group_code = #{groupCode}
		AND cd.is_delete = 0
		GROUP BY
			cd.instance_id
	) ss
LEFT JOIN (
	SELECT
		dp.instance_id,
		ds.`name`
	FROM
		cb_duty_person_shift dp
	LEFT JOIN cb_duty_shift ds ON dp.shift_id = ds.sequence_nbr
	WHERE
		dp.duty_date = #{dutyDate}
	AND dp.is_delete = 0
	AND NAME IS NOT NULL
) cds ON ss.instance_id = cds.instance_id
WHERE
	cds.NAME =#{duty}
GROUP BY
	ss.postTypeName
	</select>
	
<select id='getDutyForSpecifyDate' resultType="map">
SELECT
	ds.`name`
FROM
	cb_duty_person_shift dp
LEFT JOIN cb_duty_shift ds ON dp.shift_id = ds.sequence_nbr
WHERE
	dp.duty_date = #{dutyDate}
AND dp.is_delete = 0
AND NAME IS NOT NULL
GROUP BY
	NAME
</select>	
</mapper>
