<?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.tzs.api.mapper.AlertFormValueMapper">
	 <select id="selectListByCalledId" resultType="com.yeejoin.amos.boot.module.tzs.api.dto.AlertFormValueDto">
		SELECT
			v.sequence_nbr sequenceNbr,
			v.alert_form_id alertFormId,
			v.alert_called_id alertCalledId,
			v.alert_type_code alertTypeCode,
			v.field_name fieldName,
			v.field_code fieldCode,
			v.field_value fieldValue,
			v.field_value_code fieldValueCode,
			v.rec_user_name recUserName,
			v.rec_user_id recUserId,
			v.rec_date recDate,
			v.is_delete isDelete,
			v.block block,
			f.field_type fieldType
		FROM
			tz_alert_form_value v
		LEFT JOIN tz_alert_form f ON f.sequence_nbr = v.alert_form_id
		WHERE v.alert_called_id=#{id}
	</select>
	<select id="listAll" resultType="java.util.Map">
		select
		d.*
		from
		(
		select
		i.alert_called_id instanceId,
		i.alert_type_code groupCode,
		i.rec_date recDate,
		<foreach collection="fieldCodes" item="item" index="key" separator=",">
			MAX(CASE WHEN i.FIELD_CODE = #{item} THEN i.FIELD_VALUE ELSE '' END) as ${item}
		</foreach>
		from
		tz_alert_form_value i
		where i.alert_type_code = #{groupCode}
		GROUP by
		i.alert_called_id)d
		<if test="params != null and params.size > 0 ">
			where 1=1
			  <foreach collection="params" index="key" item="value">
				  <if test="key != null and key == 'beginDate' and value != null and value != ''">
					  and d.recDate >= #{value}
				  </if>
				  <if test="key != null and key == 'endDate'">
					  and d.recDate <![CDATA[<=]]> #{value}
				  </if>
			  </foreach>
		</if>
		order by instanceId
	</select>
</mapper>
