<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.safeSoundDao" name="平">

	<select id="selectAreaList" parameterType="map" resultType="map">
		SELECT
			id,
			parent_id,
			name,
			sort
		FROM
			sys_area
		WHERE
			parent_ids LIKE '%220100000000%'
		order by sort asc
	</select>

	<select id="selectSafeSoundDictList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqDictVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ResDictVo">
		SELECT
			id,
			value,
			label,
			parent_id,
			sort
		FROM
			sys_dict
		WHERE
			del_flag = '0'
		AND type = 'event_category'
		<if test="parent_id == null or parent_id == ''">
			AND parent_id = '0'
		</if>
		<if test="parent_id != null and parent_id != ''">
			AND parent_id = #{parent_id}
		</if>
		ORDER BY sort
	</select>

	<select id="getNewSafeSoundEvent" parameterType="map" resultType="map">
		SELECT
		COUNT(DISTINCT id) num,
		IFNULL(SUM(IF(DATE_FORMAT(create_time,'%Y-%m-%d')= CURDATE(),1,0)),0) newNum,
		IFNULL(SUM(IF(status= 2,1,0)),0) processingNum,
		IFNULL(SUM(IF(is_early_warning= 4,1,0)),0) warningNum,
		IFNULL(SUM(IF(status= 1,1,0)),0) completionNum
		FROM
		jl_safe_sound_event
		<where>
			del_flag = '0'
			<if test="createTimeStart != null and createTimeStart != '' and createTimeEnd != null and createTimeEnd != ''">
				AND create_time BETWEEN #{createTimeStart} AND #{createTimeEnd}
			</if>
			<choose>
				<when test="area_id != null and area_id != ''">
					AND area_id = #{area_id}
					AND area_id = (SELECT area_id FROM sys_office WHERE id = #{area_id})
				</when>
				<when test="street_id != null and street_id != ''">
					AND street_id = #{street_id}
					AND street_id = (SELECT area_id FROM sys_office WHERE id = #{street_id})
				</when>
				<when test="community_id != null and community_id != ''">
					AND community_id = #{community_id}
					AND community_id = (SELECT area_id FROM sys_office WHERE id = #{community_id})
				</when>
				<otherwise>
					AND city_id = (SELECT area_id FROM sys_office WHERE id = '220100000000')
				</otherwise>
			</choose>
		</where>
	</select>

	<select id="selectDayEventList" parameterType="map" resultType="map">
		select
			COUNT(je.id) number,
			DATE(je.create_time) date
		FROM
			jl_event je
		<where>
			<if test="createTimeStart != null and createTimeStart != '' and createTimeEnd != null and createTimeEnd != ''">
				AND je.create_time BETWEEN #{createTimeStart} AND #{createTimeEnd}
			</if>
			<choose>
				<when test="area_id != null and area_id != ''">
					AND area_id = #{area_id}
					AND area_id = (SELECT area_id FROM sys_office WHERE id = #{area_id})
				</when>
				<when test="street_id != null and street_id != ''">
					AND street_id = #{street_id}
					AND street_id = (SELECT area_id FROM sys_office WHERE id = #{street_id})
				</when>
				<when test="community_id != null and community_id != ''">
					AND community_id = #{community_id}
					AND community_id = (SELECT area_id FROM sys_office WHERE id = #{community_id})
				</when>
				<otherwise>
					AND city_id = (SELECT area_id FROM sys_office WHERE id = '220100000000')
				</otherwise>
			</choose>
		</where>
			GROUP BY DATE(je.create_time)
			ORDER BY je.create_time ASC
	</select>

	<select id="getSafeSoundEventById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
			resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
		SELECT
			je.event_type eventType,
			(SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
			`value`)eventCategoryId,
			(SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` )
			eventTypeId,
			je.is_hidden_danger isHiddenDanger,
			je.is_special_tasks isSpecialTasks,
			je.is_difficult isDifficult,
			je.is_public_opinion isPublicOpinion,
			je.is_early_warning isEarlyWarning,
			je.is_point_event isPointEvent,
			je.involved_number involvedNumber,
			DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrenceTime,
			je.city_id cityId,
			(SELECT name FROM sys_area WHERE code = je.city_id) city,
			je.area_id areaId,
			(SELECT name FROM sys_area WHERE code = je.area_id) area,
			je.street_id streetId,
			(SELECT name FROM sys_area WHERE code = je.street_id) street,
			je.community_id communityId,
			(SELECT name FROM sys_area WHERE code = je.community_id) community,
			je.garden_id gardenId,
			je.building_id buildingId,
			je.unit_id unitId,
			je.floor_id floorId,
			je.doorplate_id doorplateId,
			je.event_code eventCode,
			je.event_source eventSource,
			je.address,
			je.dispute_description disputeDescription,
			je.reporting_name reportingName,
			je.reporting_unit reportingUnit,
			je.reporting_garden reportingGarden,
			IF(je.risk_name != '' AND je.risk_name IS NOT NULL,je.risk_name,'') as riskName,
			je.event_category_other_name,
			je.event_category_mini_other_name,
			je.is_involve_everyone,
			je.is_involve_everyone_count,
			je.status as eventStatus,
			je.event_name,
			je.event_tel,
			jefl.id,
			jefl.event_id eventId,
			jefl.flow_version flowVersion,
			jefl.flow_id flowId,
			jefl.office_id officeId,
			jefl.status,
			jefl.log_type logType,
			jefl.assignment_type assignmentType,
			jefl.log_result logResult,
			jefl.resolve_name resolveName,
			jefl.resolve_unit resolveUnit,
			jefl.resolve_hierarchy resolveHierarchy,
			DATE_FORMAT(jefl.resolve_time,'%Y-%m-%d %T') resolveTime,
			jefl.is_satisfaction isSatisfaction,
			jefl.resolve_process resolveProcess,
			jefl.is_resolve isResolve,
			jefl.warning_level warningLevel,
			jefl.contacts,
			jefl.attachment,
			jefl.deal_warn_time dealWarnTime,
			jefl.deal_end_time dealEndTime,
			jefl.event_check eventCheck,
			jefl.resolve_way resolveWay,
			DATE_FORMAT(jefl.term_time,'%Y-%m-%d %T') termTime,
			(SELECT risk_level FROM jl_event_flow_log WHERE event_id = je.id AND event_id = je.id
			ORDER BY create_time DESC LIMIT 1) riskLevel,
			(SELECT hierarchy FROM jl_event_flow_setting WHERE flow_id = jefl.id) hierarchySetting,
			'0' typeNum
		FROM
			jl_event je
		LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
		WHERE
			je.del_flag = '0'
		AND jefl.del_flag = '0'
		AND je.id = #{id}
		ORDER BY jefl.create_time DESC
		LIMIT 1
	</select>

	<select id="getSafeSoundEventMById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
			resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
		SELECT
			je.id,
			je.id eventId,
			je.event_type eventType,
			(SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
			`value`)eventCategoryId,
			(SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` )
			eventTypeId,
			je.is_hidden_danger isHiddenDanger,
			je.is_special_tasks isSpecialTasks,
			je.is_difficult isDifficult,
			je.is_public_opinion isPublicOpinion,
			je.is_early_warning isEarlyWarning,
			je.is_point_event isPointEvent,
			je.involved_number involvedNumber,
			DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrenceTime,
			je.city_id cityId,
			(SELECT name FROM sys_area WHERE code = je.city_id) city,
			je.area_id areaId,
			(SELECT name FROM sys_area WHERE code = je.area_id) area,
			je.street_id streetId,
			(SELECT name FROM sys_area WHERE code = je.street_id) street,
			je.community_id communityId,
			(SELECT name FROM sys_area WHERE code = je.community_id) community,
			je.garden_id gardenId,
			je.building_id buildingId,
			je.unit_id unitId,
			je.floor_id floorId,
			je.doorplate_id doorplateId,
			je.event_code eventCode,
			je.event_source eventSource,
			je.address,
			je.dispute_description disputeDescription,
			je.reporting_name reportingName,
			je.reporting_unit reportingUnit,
			je.reporting_garden reportingGarden,
			IF(je.risk_name != '' AND je.risk_name IS NOT NULL,je.risk_name,'') as riskName,
			je.event_category_other_name,
			je.event_category_mini_other_name,
			je.is_involve_everyone,
			je.is_involve_everyone_count,
			je.status as eventStatus,
			je.event_name,
			je.event_tel,
			je.type_num typeNum
		FROM
			jl_safe_sound_event je
		WHERE
			je.del_flag = '0'
		AND je.id = #{id}
		ORDER BY je.create_time DESC
		LIMIT 1
	</select>

	<select id="getValueNameDict" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo" resultType="String">
		SELECT
			value name
		FROM
			sys_dict
		WHERE
			label = #{eventCategoryId}
		AND type = 'event_category'
		AND parent_id = '0'
	</select>

	<update id="updateSafeSoundEventByType" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
		UPDATE
		jl_safe_sound_event
		SET
		type_num = '0'
		WHERE
		id = #{eventId}
	</update>

	<insert id="insertSafeSoundEvent" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
		INSERT INTO jl_event(
			id,
			risk_name,
			risk_level,
			event_type,
			event_category_id,
			event_type_id,
			is_hidden_danger,
			is_special_tasks,
			is_difficult,
			is_public_opinion,
			is_point_event,
			involved_number,
			occurrence_time,
			city_id,
			area_id,
			street_id,
			community_id,
			garden_id,
			building_id,
			unit_id,
			floor_id,
			doorplate_id,
			event_code,
			event_source,
			address,
			dispute_description,
			reporting_name,
			reporting_unit,
			reporting_garden,
			`status`,
			del_flag,
			create_by,
			create_time,
			scanning_time,
			is_early_warning,
			early_warning_time,
			alarm_time,
			is_acceptance,
			record_id,
			event_category_other_name,
			reporting_unit_code,
			event_category_mini_other_name
			<if test="is_involve_everyone!=null and is_involve_everyone!=''">
				,is_involve_everyone
			</if>
			<if test="is_involve_everyone_count!=null and is_involve_everyone_count!=''">
				,is_involve_everyone_count
			</if>
			<if test="event_name!=null and event_name!=''">
				,event_name
			</if>
			<if test="event_tel!=null and event_tel!=''">
				,event_tel
			</if>
		)VALUES(
			#{id},
			#{riskName},
			#{riskLevel},
			#{eventType},
			#{eventCategoryId},
			#{eventTypeId},
			#{isHiddenDanger},
			#{isSpecialTasks},
			#{isDifficult},
			#{isPublicOpinion},
			#{isPointEvent},
			#{involvedNumber},
			#{occurrenceTime},
			#{cityId},
			#{areaId},
			#{streetId},
			#{communityId},
			#{gardenId},
			#{buildingId},
			#{unitId},
			#{floorId},
			#{doorplateId},
			#{eventCode},
			'4',
			#{address},
			#{disputeDescription},
			#{reportingName},
			#{reportingUnit},
			#{reportingGarden},
			#{status},
			0,
			#{_user.id},
			#{createTime},
			NOW(),
			'1',
			#{earlyWarningTime},
			#{alarmTime},
			'1',
			#{recordId},
			#{event_category_other_name},
			#{reportingUnitCode},
			#{event_category_mini_other_name}
			<if test="is_involve_everyone!=null and is_involve_everyone!=''">
				,#{is_involve_everyone}
			</if>
			<if test="is_involve_everyone_count!=null and is_involve_everyone_count!=''">
				,#{is_involve_everyone_count}
			</if>
			<if test="event_name!=null and event_name!=''">
				,#{event_name}
			</if>
			<if test="event_tel!=null and event_tel!=''">
				,#{event_tel}
			</if>
		)
	</insert>

	<insert id="insertSafeSoundEventFront" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSafeSoundEventInsertVo">
		INSERT INTO  jl_safe_sound_event (
			id,
			event_type,
			event_category_id,
			event_type_id,
			occurrence_time,
			city_id,
			area_id,
			street_id,
			community_id,
			event_code,
			event_source,
			address,
			dispute_description,
			`status`,
			del_flag,
			create_by,
			create_time,
			scanning_time,
			event_category_other_name,
			reporting_unit_code,
			event_category_mini_other_name
		)VALUES(
			#{id},
			2,
			#{eventCategoryId},
			#{eventTypeId},
			#{occurrenceTime},
			#{cityId},
			#{areaId},
			#{streetId},
			#{communityId},
			#{eventCode},
			4,
			#{address},
			#{disputeDescription},
			2,
			0,
			#{userId},
			NOW(),
			NOW(),
			#{event_category_other_name},
			#{communityId},
			#{event_category_mini_other_name}
		)
	</insert>

	<select id="getSafeSoundFax" parameterType="String" resultType="String">
		select fax from sys_office where id=#{id}
	</select>

	<select id="getSafeSoundOfficeId" parameterType="String" resultType="String">
		select office_id from sys_user
		where id=(select create_by from sys_office where id=#{id})
	</select>
<!--
	<update id="updateSafeSoundEvent" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
		update
			jl_event
		set
		<if test="eventType != null and eventType != ''">
			event_type = #{eventType},
		</if>
		<if test="eventCategoryId != null and eventCategoryId != ''">
			event_category_id = #{eventCategoryId},
		</if>
		<if test="eventTypeId != null and eventTypeId != ''">
			event_type_id = #{eventTypeId},
		</if>
		<if test="event_category_other_name != null and event_category_other_name != ''">
			event_category_other_name = #{event_category_other_name},
		</if>
		<if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
			event_category_mini_other_name = #{event_category_mini_other_name},
		</if>
		<if test="isHiddenDanger != null and isHiddenDanger != ''">
			is_hidden_danger = #{isHiddenDanger},
		</if>
		<if test="isSpecialTasks != null and isSpecialTasks != ''">
			is_special_tasks = #{isSpecialTasks},
		</if>
		<if test="isDifficult != null and isDifficult != ''">
			is_difficult = #{isDifficult},
		</if>
		<if test="isPublicOpinion != null and isPublicOpinion != ''">
			is_public_opinion = #{isPublicOpinion},
		</if>
		<if test="isEarlyWarning != null and isEarlyWarning != ''">
			is_early_warning = #{isEarlyWarning},
		</if>
		<if test="isPointEvent != null and isPointEvent != ''">
			is_point_event = #{isPointEvent},
		</if>
		<if test="isSuperviseHandle != null and isSuperviseHandle != ''">
			is_supervise_handle = #{isSuperviseHandle},
		</if>
		<if test="involvedNumber != null and involvedNumber != ''">
			involved_number = #{involvedNumber},
		</if>
		<if test="deadNumber != null and deadNumber != ''">
			dead_number = #{deadNumber},
		</if>
		<if test="occurrenceTime != null and occurrenceTime != ''">
			occurrence_time = #{occurrenceTime},
		</if>
		<if test="cityId != null and cityId != ''">
			city_id = #{cityId},
		</if>
		<if test="areaId != null and areaId != ''">
			area_id = #{areaId},
		</if>
		<if test="streetId != null and streetId != ''">
			street_id = #{streetId},
		</if>
		<if test="communityId != null and communityId != ''">
			community_id = #{communityId},
		</if>
		<if test="eventCode != null and eventCode != ''">
			event_code = #{eventCode},
		</if>
		<if test="eventSource != null and eventSource != ''">
			event_source = #{eventSource},
		</if>
		<if test="address != null and address != ''">
			address = #{address},
		</if>
		<if test="disputeDescription != null and disputeDescription != ''">
			dispute_description = #{disputeDescription},
		</if>
		<if test="reportingName != null and reportingName != ''">
			reporting_name = #{reportingName},
		</if>
		<if test="reportingUnit != null and reportingUnit != ''">
			reporting_unit = #{reportingUnit},
		</if>
		<if test="reportingGarden != null and reportingGarden != ''">
			reporting_garden = #{reportingGarden},
		</if>
		<if test="status != null and status != ''">
			`status` = #{status},
		</if>
		<if test="completeTime != null and completeTime != ''">
			complete_time = #{completeTime},
		</if>
		<if test="supervisionTime != null and supervisionTime != ''">
			supervision_time = #{supervisionTime},
		</if>
		<if test="earlyWarningTime != null and earlyWarningTime != ''">
			early_warning_time = #{earlyWarningTime},
		</if>
		<if test="alarmTime != null and alarmTime != ''">
			alarm_time = #{alarmTime},
		</if>
		<if test="isAcceptance != null and isAcceptance != ''">
			is_acceptance = #{isAcceptance},
		</if>
		<if test="recordId != null and recordId != ''">
			record_id = #{recordId},
		</if>
		<if test="riskName != null and eventType != ''">
			risk_name = #{riskName},
		</if>
		<if test="riskLevel != null and riskLevel != ''">
			risk_level = #{riskLevel},
		</if>
		<if test="is_involve_everyone != null and is_involve_everyone != ''">
			is_involve_everyone = #{is_involve_everyone},
		</if>
		<if test="isInvolveEveryoneCount != null and isInvolveEveryoneCount != ''">
			is_involve_everyone_count = #{isInvolveEveryoneCount},
		</if>
		<if test="eventName != null and eventName != ''">
			event_name = #{eventName},
		</if>
		<if test="eventTel != null and eventTel != ''">
			event_tel = #{eventTel},
		</if>
		<if test="reportingUnitCode != null and reportingUnitCode != ''">
			reporting_unit_code = #{reportingUnitCode},
		</if>
			update_by = #{_user.id},
			update_time = NOW()
		WHERE
			id = #{id}
	</update>-->

	<insert id="insertSafeSoundEventCategoryOther" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
		<selectKey keyProperty="count" resultType="int" order="BEFORE">
			SELECT count(*) FROM jl_event_category_other WHERE event_id = #{id}
		</selectKey>
		<if test="count > 0">
			UPDATE jl_event_category_other
			<set>
				update_time = now(),
				update_by = #{_user.id},
				<if test="event_category_other_name != null and event_category_other_name != ''">
					event_category_other_name = #{event_category_other_name},
				</if>
				<if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
					event_category_mini_other_name = #{event_category_mini_other_name},
				</if>
			</set>
			WHERE event_id = #{id}
		</if>
		<if test="count == 0">
			INSERT INTO jl_event_category_other (
			id,
			event_id,
			<if test="event_category_other_name != null and event_category_other_name != ''">
				event_category_other_name,
			</if>
			<if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
				event_category_mini_other_name,
			</if>
			create_by, create_time
			) VALUES (
			uuid(),
			#{id},
			<if test="event_category_other_name != null and event_category_other_name != ''">
				#{event_category_other_name},
			</if>
			<if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
				#{event_category_mini_other_name},
			</if>
			#{_user.id}, now()
			)
		</if>
	</insert>

	<insert id="insertSafeSoundEventCategoryOtherFront" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSafeSoundEventInsertVo">
		<selectKey keyProperty="count" resultType="int" order="BEFORE">
			SELECT count(*) FROM jl_safe_sound_event_category_other WHERE event_id = #{id}
		</selectKey>
		<if test="count > 0">
			UPDATE jl_safe_sound_event_category_other
			<set>
				update_time = now(),
				update_by = #{_user.id},
				<if test="event_category_other_name != null and event_category_other_name != ''">
					event_category_other_name = #{event_category_other_name},
				</if>
				<if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
					event_category_mini_other_name = #{event_category_mini_other_name},
				</if>
			</set>
			WHERE event_id = #{id}
		</if>
		<if test="count == 0">
			INSERT INTO jl_safe_sound_event_category_other (
			id,
			event_id,
			<if test="event_category_other_name != null and event_category_other_name != ''">
				event_category_other_name,
			</if>
			<if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
				event_category_mini_other_name,
			</if>
			create_by, create_time
			) VALUES (
			uuid(),
			#{id},
			<if test="event_category_other_name != null and event_category_other_name != ''">
				#{event_category_other_name},
			</if>
			<if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
				#{event_category_mini_other_name},
			</if>
			#{communityId}, now()
			)
		</if>
	</insert>

	<select id="selectSafeSoundEventUserList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlEventUser">
		SELECT
			id,
			event_user_type eventUserType,
			event_user_name eventUserName,
			is_keynote isKeynote,
			keynote_type keynoteType,
			CAST(AES_DECRYPT(FROM_BASE64(event_cardno),'*#id#') AS CHAR) AS eventCardno,
			event_tel eventTel,
			tyshxydm,
			event_represent eventRepresent,
			remark
		FROM
			jl_event_user
		WHERE
			event_id = #{eventId}
	</select>

	<select id="selectSafeSoundMEventUserList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlEventUser">
		SELECT
			id,
			event_user_type eventUserType,
			event_user_name eventUserName,
			is_keynote isKeynote,
			keynote_type keynoteType,
			CAST(AES_DECRYPT(FROM_BASE64(event_cardno),'*#id#') AS CHAR) AS eventCardno,
			event_tel eventTel,
			tyshxydm,
			event_represent eventRepresent,
			remark
		FROM
			jl_safe_sound_event_user
		WHERE
			event_id = #{eventId}
	</select>

	<select id="findSafeSoundFlowLogProcessList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
		SELECT
			jefl.id,
			jefl.event_id eventId,
			jefl.flow_version flowVersion,
			jefl.flow_id flowId,
			jefl.office_id officeId,
			jefl.status,
			jefl.log_type logType,
			jefl.assign_result assignResult,
			jefl.assignment_type assignmentType,
			jefl.log_result logResult,
			jefl.grade_user_id gradeUserId,
			jefl.event_check eventCheck,
			DATE_FORMAT(jefl.complete_time,'%Y-%m-%d %T') completeTime,
			DATE_FORMAT(jefl.back_time,'%Y-%m-%d %T') backTime,
			DATE_FORMAT(jefl.assign_time,'%Y-%m-%d %T') assignTime,
			DATE_FORMAT(jefl.receive_time,'%Y-%m-%d %T') receiveTime,
			DATE_FORMAT(jefl.create_time,'%Y-%m-%d %T') createTime,
			hierarchy,
			IF(hierarchy = '1', CONCAT('网格员：', su.`name`),CONCAT(so.`name`,IF(suc.id IS NULL, '', CONCAT('：', suc.`name`)))) hierarchyName,
			(select event_code from jl_event where id=jefl.event_id and del_flag='0') as eventCode,
			(select status from jl_event where id=jefl.event_id and del_flag='0') as eventStatus,
			jefl.is_dispatch_office,
			jefl.is_resolve isResolve,
			jefl.resolve_name as resolveName,
			(select status from jl_event where id=#{eventId}) as eventStatus,
			jefl.deal_log_type
		FROM
			jl_event_flow_log jefl
		LEFT JOIN jl_event_flow_setting jefs ON jefl.flow_id = jefs.id
		LEFT JOIN sys_office so ON jefl.office_id = so.id
		LEFT JOIN sys_user su ON jefl.grade_user_id = su.id
		LEFT JOIN sys_user suc ON jefl.update_by = suc.id
		WHERE
			jefl.event_id = #{eventId}
		AND jefl.del_flag = '0'
		ORDER BY jefl.create_time DESC
	</select>

	<select id="selectEventCategoryDictList" parameterType="map" resultType="map">
		SELECT
			sd.label,
			COUNT(je.id) number
		FROM
			jl_event je
		LEFT JOIN sys_dict sd ON je.event_category_id = sd.value AND sd.del_flag = '0' AND type ='event_category'
		WHERE
			je.del_flag = '0'
		AND sd.del_flag = '0'
		AND type ='event_category'
		<if test="createTimeStart != null and createTimeStart != '' and createTimeEnd != null and createTimeEnd != ''">
			AND je.create_time BETWEEN #{createTimeStart} AND #{createTimeEnd}
		</if>
		<choose>
			<when test="area_id != null and area_id != ''">
				AND area_id = #{area_id}
				AND area_id = (SELECT area_id FROM sys_office WHERE id = #{area_id})
			</when>
			<when test="street_id != null and street_id != ''">
				AND street_id = #{street_id}
				AND street_id = (SELECT area_id FROM sys_office WHERE id = #{street_id})
			</when>
			<when test="community_id != null and community_id != ''">
				AND community_id = #{community_id}
				AND community_id = (SELECT area_id FROM sys_office WHERE id = #{community_id})
			</when>
			<otherwise>
				AND city_id = (SELECT area_id FROM sys_office WHERE id = '220100000000')
			</otherwise>
		</choose>
		GROUP BY je.event_category_id
		ORDER BY COUNT(je.id) DESC
		LIMIT 5
	</select>

	<select id="findSafeSoundEarlyWarningList" parameterType="map" resultType="map">
		SELECT
			a.id,
			a.disputeDescription,
			TIMESTAMPDIFF(HOUR,DATE_SUB(a.createTime,INTERVAL -(b.red_warn_days+if(c.approve_days IS NULL ,0,c.approve_days)) day), NOW()) redWarnTime
		FROM (
		SELECT
		DISTINCT
			je.id,
			je.complete_time,
			je.dispute_description disputeDescription,
			DATE_FORMAT(je.complete_time,'%Y-%m-%d %T') completeTime,
			je.create_time createTime,
			je.risk_level riskLevel
		FROM
			jl_event je
		LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
		LEFT JOIN sys_user su ON je.create_by = su.id
		LEFT JOIN sys_office so ON so.id = su.company_id
		LEFT JOIN sys_dict sd ON sd.`value` = je.event_category_id AND sd.type = 'event_category' AND sd.parent_id = '0'
		LEFT JOIN sys_dict sd1 ON sd1.`value` = je.event_type_id AND sd1.type = 'event_category' AND sd1.parent_id != '0'
		LEFT JOIN sys_area sa ON sa.code = je.city_id
		LEFT JOIN sys_area sa1 ON sa1.code = je.area_id
		LEFT JOIN sys_area sa2 ON sa2.code = je.street_id
		LEFT JOIN sys_area sa3 ON sa3.code = je.community_id
		WHERE
			je.del_flag = '0'
		AND jefl.del_flag = '0'
		AND je.risk_level IS NOT NULL
		<if test="createTimeStart != null and createTimeStart != '' and createTimeEnd != null and createTimeEnd != ''">
			AND je.create_time BETWEEN #{createTimeStart} AND #{createTimeEnd}
		</if>
		<choose>
			<when test="area_id != null and area_id != ''">
				AND area_id = #{area_id}
				AND area_id = (SELECT area_id FROM sys_office WHERE id = #{area_id})
			</when>
			<when test="street_id != null and street_id != ''">
				AND street_id = #{street_id}
				AND street_id = (SELECT area_id FROM sys_office WHERE id = #{street_id})
			</when>
			<when test="community_id != null and community_id != ''">
				AND community_id = #{community_id}
				AND community_id = (SELECT area_id FROM sys_office WHERE id = #{community_id})
			</when>
			<otherwise>
				AND city_id = (SELECT area_id FROM sys_office WHERE id = '220100000000')
			</otherwise>
		</choose>
		) a
		INNER JOIN jl_event_overdue_warning_config b ON b.risk_type=a.riskLevel
		LEFT JOIN (SELECT sum(approve_days) AS approve_days ,event_id from jl_event_approve where del_flag='0' and approve_type='0' GROUP BY event_id) c ON c.event_id=a.id
		WHERE
			DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'), DATE_FORMAT(a.createTime,'%Y-%m-%d'))>(b.red_warn_days+if(c.approve_days IS NULL ,0,c.approve_days))
		ORDER BY createTime DESC
	</select>

	<insert id="insertSafeSoundEventAttach" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventAttachInsertVo">
		INSERT INTO jl_event_attach(
			id,
			event_id,
			url,
			attach_type,
			attach_uuid,
			attach_suffix,
			create_time
		)VALUES(
			UUID(),
			#{id},
			#{url},
			1,
			#{uuid},
			#{suffix},
			NOW()
		)
	</insert>

	<insert id="insertSafeSoundEventUserKey" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventUserInsertVo">
		INSERT INTO jl_event_user(
			id,
			event_id,
			event_user_type,
			event_user_name,
			event_cardno,
			event_tel,
			tyshxydm,
			event_represent,
			remark,
			is_keynote,
			keynote_type,
			create_by,
			create_time
		) VALUES (
			UUID(),
			#{eventId},
			#{eventUserType},
			#{eventUserName},
			TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#')),
			#{eventTel},
			#{tyshxydm},
			#{eventRepresent},
			#{remark},
			#{isKeynote},
			#{keynoteType},
			#{_user.id},
			NOW()
		)
	</insert>

	<insert id="insertSafeSoundEventUserKeyFront" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSafeSoundEventUserInsertVo">
		INSERT INTO jl_safe_sound_event_user (
			id,
			event_id,
			event_user_type,
			event_user_name,
			event_cardno,
			event_tel,
			tyshxydm,
			event_represent,
			remark,
			is_keynote,
			keynote_type,
			create_by,
			create_time
		) VALUES (
			UUID(),
			#{eventId},
			#{eventUserType},
			#{eventUserName},
			TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#')),
			#{eventTel},
			#{tyshxydm},
			#{eventRepresent},
			#{remark},
			#{isKeynote},
			#{keynoteType},
			#{_user.id},
			NOW()
		)
	</insert>

	<insert id="insertSafeSoundTriggerEvent" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventUserInsertVo">
		<selectKey keyProperty="count" resultType="int" order="BEFORE">
			SELECT COUNT(1) FROM jl_trigger_event WHERE cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
		</selectKey>
		<if test="count > 0">
			UPDATE
				jl_trigger_event a,
				(SELECT
				GROUP_CONCAT( DISTINCT a.event_user_name) name,
				GROUP_CONCAT( DISTINCT a.event_tel) tel,
				GROUP_CONCAT( DISTINCT a.keynote_type) user_type,
				GROUP_CONCAT( DISTINCT b.city_id) city_id,
				GROUP_CONCAT( DISTINCT b.area_id) area_id,
				GROUP_CONCAT( DISTINCT b.street_id) street_id,
				GROUP_CONCAT( DISTINCT b.community_id) community_id,
				COUNT(DISTINCT b.id) num,
				GROUP_CONCAT(DISTINCT b.id) eventNum
			FROM
				jl_event_user a
			INNER JOIN jl_event b ON a.event_id = b.id
			WHERE
				b.del_flag = '0'
			AND b.`status` = '2'
			AND a.event_card_type = '1'
			AND a.event_cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))) b
			SET
				a.`name` = b.`name`,
				a.city_id = b.city_id,
				a.area_id = b.area_id,
				a.street_id = b.street_id,
				a.community_id = b.community_id,
				a.tel = b.tel,
				a.user_type = b.user_type,
				a.trigger_num = num,
				a.trigger_event_id = eventNum,
				a.trigger_time = NOW(),
				a.update_time = NOW()
			WHERE
				a.cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
		</if>
		<if test="count == 0">
			INSERT INTO jl_trigger_event
				SELECT
					UUID(),
					GROUP_CONCAT( DISTINCT a.event_user_name),
					a.event_cardno,
					GROUP_CONCAT( DISTINCT a.event_tel),
					GROUP_CONCAT( DISTINCT a.keynote_type),
					GROUP_CONCAT( DISTINCT b.city_id),
					GROUP_CONCAT( DISTINCT b.area_id),
					GROUP_CONCAT( DISTINCT b.street_id),
					GROUP_CONCAT( DISTINCT b.community_id),
					NOW(),
					COUNT(DISTINCT b.id),
					GROUP_CONCAT(DISTINCT b.id),
					'1',
					NOW(),
					NOW()
				FROM
					jl_event_user a
				INNER JOIN jl_event b ON a.event_id = b.id
				WHERE
					b.del_flag = '0'
				AND b.`status` = '2'
				AND a.event_card_type = '1'
				AND a.event_cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
		</if>
	</insert>

	<select id="getSafeSoundUserEventNumber" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventUserInsertVo" resultType="Integer">
		SELECT
			IFNULL(COUNT(b.id),0)
		FROM
			jl_event_user a
		INNER JOIN jl_event b ON a.event_id = b.id
		WHERE
			b.del_flag = '0'
		AND b.`status` = '2'
		AND a.event_card_type = '1'
		AND a.event_cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
	</select>

	<select id="getSafeSoundJobDuty" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo"
			resultType="String" expose="true">
		SELECT
			IFNULL(job_duty,'') job_duty
		FROM
			sys_user
		WHERE
			id = #{_user.id}
	</select>

	<insert id="insertSafeSoundEventSupplementInfo" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.SupplementEvent">
		INSERT INTO jl_warring_supplement_event (
			event_id,
			create_time,
			is_areas_ocus,
			area_id,
			city_id,
			occurrence_time,
			event_category_id
		) VALUES (
			#{eventId},
			#{createTime},
			#{isAreasOcus},
			#{areaId},
			#{cityId},
			#{occurrenceTime},
			#{eventCategoryId}
		)
	</insert>

	<select id="findSafeSound30DayEvent"
			parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo"
			resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
		SELECT
			jl_event.id AS id,
			community_id,
			event_type_id,
			event_category_id
		FROM
			jl_event
		WHERE
			jl_event.del_flag = '0'
		AND status='2'
		AND occurrence_time >= ( #{occurrenceTime} - INTERVAL 30 DAY )
		AND occurrence_time &lt; ( #{occurrenceTime} + INTERVAL 1 DAY )
		and community_id=#{communityId}
		and event_category_id=#{eventCategoryId}
		and event_type_id=#{eventTypeId}
	</select>

	<select id="selectSafeSoundEventCategory"  parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventCategory" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventCategory">
		SELECT
			a.id,
			a.value,
			a.label,
			a.parent_id,
			ifnull(a.is_easy,0) as is_easy,
			a.sort
		FROM
			sys_dict a
		WHERE
			a.del_flag = '0'
		AND a.type = 'event_category'
		AND a.value=#{value}
	</select>

	<select id="getSafeSoundEventTriggerInfo" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventTrigger">
		select
			id,
			event_category_id as eventCategoryId,
			event_type_id as eventTypeId,
			trigger_type as triggerType,
			trigger_time as triggerTime,
			city_id as cityId,
			area_id as areaId,
			street_id as streetId,
			community_id as communityId
		from
			jl_event_trigger
		where
			event_category_id=#{eventCategoryId}
		and event_type_id=#{eventTypeId}
		and trigger_type=#{triggerType}
		and city_id=#{cityId}
		and area_id=#{areaId}
		and street_id=#{streetId}
		and community_id=#{communityId}
	</select>

	<insert id="insertSafeSoundEventTriggerForRisk" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventTrigger">
		INSERT IGNORE INTO jl_event_trigger(
			id,
			event_category_id,
			event_type_id,
			trigger_type,
			trigger_time,
			city_id,
			area_id,
			street_id,
			community_id,
			alarm_level,
			event_id
		) VALUES (
			#{id},
			#{eventCategoryId},
			#{eventTypeId},
			#{triggerType},
			now(),
			#{cityId},
			#{areaId},
			#{streetId},
			#{communityId},
			#{alarmLevel},
			#{eventId}
		)
	</insert>

	<insert id="insertSafeSoundFirstEventFlowLog" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
		INSERT INTO jl_event_flow_log(
			id,
			event_id,
			flow_version,
			flow_id,
			office_id,
			grade_user_id,
			status,
			log_type,
			deal_log_type,
			assignment_type,
			risk_level,
			term_time,
			log_result,
			resolve_name,
			resolve_unit,
			resolve_hierarchy,
			resolve_time,
			is_satisfaction,
			resolve_process,
			is_resolve,
			warning_level,
			contacts,
			complete_time,
			<if test="logType == '3'.toString()">
				assign_time,
			</if>
			receive_time,
			attachment,
			deal_warn_time,
			deal_end_time,
			create_by,
			create_name,
			create_time,
			<if test="assignResult!=null and assignResult!=''">
				assign_result,
			</if>
			scanning_time
		) VALUES (
			UUID(),
			#{eventId},
			#{flowVersion},
			#{flowId},
			#{_user.company_id},
			#{gradeUserId},
			#{status},
			#{logType},
			#{logType},
			#{assignmentType},
			#{riskLevel},
			#{termTime},
			#{logResult},
			#{resolveName},
			#{resolveUnit},
			#{resolveHierarchy},
			#{resolveTime},
			#{isSatisfaction},
			#{resolveProcess},
			'2',
			#{warningLevel},
			#{contacts},
			NOW(),
			<if test="logType == '3'.toString()">
				NOW(),
			</if>
			NOW(),
			#{attachment},
			#{dealWarnTime},
			#{dealEndTime},
			#{_user.id},
			#{_user.name},
			NOW(),
			<if test="assignResult!=null and assignResult!=''">
				#{assignResult},
			</if>
			NOW()
		)
	</insert>

	<select id="getSafeSoundEventFlowSetting" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
		SELECT
			id,
			version
		FROM
			jl_event_flow_setting
		WHERE
			hierarchy = #{hierarchy}
		AND type = #{eventFlowSettingType}
		<if test="version == null or version == ''">
			AND is_history = 0
		</if>
		<if test="version != null and version != ''">
			AND version = #{version}
		</if>
		ORDER BY order_no DESC
		LIMIT 1
	</select>

	<insert id="insertSafeSoundEventFlowLog" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
		INSERT INTO jl_event_flow_log(
			id,
			event_id,
			flow_version,
			flow_id,
			office_id,
			grade_user_id,
			status,
			log_type,
			assignment_type,
			risk_level,
			term_time,
			log_result,
			event_check,
			resolve_name,
			resolve_unit,
			resolve_hierarchy,
			resolve_time,
			is_satisfaction,
			resolve_process,
			is_resolve,
			warning_level,
			contacts,
			<if test="resolveWay != null and resolveWay != ''">
				resolve_way,
			</if>
			<if test="assignResult != null and assignResult != ''">
				assign_result,
			</if>
			<if test="is_dispatch_office != null and is_dispatch_office != ''">
				is_dispatch_office,
			</if>
			attachment,
			deal_warn_time,
			deal_end_time,
			create_by,
			create_name,
			create_time,
			scanning_time
		) VALUES (
			UUID(),
			#{eventId},
			#{flowVersion},
			#{flowId},
			#{officeId},
			#{gradeUserId},
			#{status},
			#{logType},
			#{assignmentType},
			#{riskLevel},
			#{termTime},
			#{logResult},
			#{eventCheck},
			#{resolveName},
			#{resolveUnit},
			#{resolveHierarchy},
			#{resolveTime},
			#{isSatisfaction},
			#{resolveProcess},
			#{isResolve},
			#{warningLevel},
			#{contacts},
			<if test="resolveWay != null and resolveWay != ''">
				#{resolveWay},
			</if>
			<if test="assignResult != null and assignResult != ''">
				#{assignResult},
			</if>
			<if test="is_dispatch_office != null and is_dispatch_office != ''">
				#{is_dispatch_office},
			</if>
			#{attachment},
			#{dealWarnTime},
			#{dealEndTime},
			#{_user.id},
			#{_user.name},
			NOW(),
			NOW()
		)
	</insert>

	<update id="updateSafeSoundEventTriggerForRisk" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventTrigger">
		UPDATE
			jl_event_trigger
		SET
			alarm_level = #{alarmLevel},
			status = 1
		WHERE
			id=#{id}
	</update>

	<insert id="insertSafeSoundEventRiskFactor" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventRiskFactor">
		INSERT IGNORE INTO jl_event_risk_factor(
			id,
			event_category_id,
			event_type_id,
			alarm_level,
			city_id,
			area_id,
			street_id,
			community_id,
			event_id
		) VALUES (
			#{id},
			#{eventCategoryId},
			#{eventTypeId},
			#{alarmLevel},
			#{cityId},
			#{areaId},
			#{streetId},
			#{communityId},
			#{eventId}
		)
	</insert>

	<select id="findSafeSoundEventLogByOfficeStatusList"
			parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
			resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
		SELECT
			a.*
		FROM (
		SELECT
			DISTINCT je.id,
			je.id eventId,
			je.event_type eventType,
			(SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
			`value`)eventCategoryId,
			(SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` )
			eventTypeId,
			je.is_hidden_danger isHiddenDanger,
			je.is_special_tasks isSpecialTasks,
			je.risk_name as riskName,
			je.is_difficult isDifficult,
			je.is_public_opinion isPublicOpinion,
			je.is_early_warning isEarlyWarning,
			je.is_point_event isPointEvent,
			je.involved_number involvedNumber,
			je.risk_level as 'riskLevel',
			DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrenceTime,
			je.city_id cityId,
			date_format(je.complete_time, '%Y-%m-%d %T' ) as completeTime,
			(SELECT name FROM sys_area WHERE code = je.city_id) city,
			je.area_id areaId,
			(SELECT name FROM sys_area WHERE code = je.area_id) area,
			je.street_id streetId,
			(SELECT name FROM sys_area WHERE code = je.street_id) street,
			je.community_id communityId,
			(SELECT name FROM sys_area WHERE code = je.community_id) community,
			je.garden_id gardenId,
			je.building_id buildingId,
			je.unit_id unitId,
			je.floor_id floorId,
			je.doorplate_id doorplateId,
			je.event_code eventCode,
			je.event_source eventSource,
			je.address,
			je.dispute_description disputeDescription,
			je.reporting_name reportingName,
			je.create_by as createBy,
			je.reporting_unit reportingUnit,
			je.reporting_garden reportingGarden,
			je.create_time createTime,
			je.is_acceptance as isAcceptance,
			je.status as eventStatus,
			(select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='0' order by
			approve_time desc limit 1) as deal_result_0,
			(select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='1' order by
			approve_time desc limit 1) as deal_result_1,
			(select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='2' order by
			approve_time desc limit 1) as deal_result_2,
			(select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='3' order by
			approve_time desc limit 1) as deal_result_3,
			(SELECT status FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND office_id =
			#{params._user.company_id} ORDER BY create_time DESC LIMIT 1) status,
			(SELECT is_resolve FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND event_id = je.id
			AND status = '3' ORDER BY create_time DESC LIMIT 1) isResolve,
			(SELECT is_resolve FROM jl_event_flow_log WHERE event_id = je.id AND office_id = #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) isResolveMine,
			(SELECT DATE_FORMAT(term_time,'%Y-%m-%d %T') FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS
			NULL AND event_id = je.id
			ORDER BY create_time DESC LIMIT 1) termTime,
			(SELECT resolve_way FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND event_id = je.id
			ORDER BY create_time DESC LIMIT 1) resolveWay,
			(SELECT IF(log_type = '4' AND status = '3','2',log_type) FROM jl_event_flow_log WHERE event_id = je.id AND
			grade_user_id IS NULL AND office_id =
			#{params._user.company_id} ORDER BY create_time DESC LIMIT 1) logType,
			(SELECT log_type FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' ORDER BY create_time DESC LIMIT 1) logTypeNew,
			(SELECT log_type FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' AND office_id =#{params._user.company_id} ORDER BY create_time DESC LIMIT 1) mineLatestLogType,
			(SELECT deal_log_type FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' AND office_id =#{params._user.company_id} and grade_user_id is null and complete_time is not null ORDER BY create_time DESC LIMIT 1) mineLatestLogTypeNew,
			(SELECT IFNULL(fax,'') FROM sys_office WHERE id = jefl.office_id) fax,
			(SELECT IFNULL(assignment_type,'9999') FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL
			AND office_id = #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) assignmentType,
			(SELECT hierarchy FROM jl_event_flow_setting WHERE flow_id = jefl.id) hierarchySetting,
			(SELECT IFNULL(IF(status = '3', 2, 1), 2) FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL
			AND office_id = #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) logStatus,
			(select count(office_id) from jl_event_flow_log where event_id=je.id and del_flag='0' and is_dispatch_office='1') as is_dispatch_office_counts,
			(select count(office_id) from jl_event_flow_log where event_id=je.id and del_flag='0' and is_dispatch_office='1' and status='3' and is_resolve='2') as is_dispatch_office_back_counts,<!--这种情况是横向部门退回-->
			je.is_involve_everyone,
			je.is_involve_everyone_count,
			je.type_num typeNum
		FROM
		jl_safe_sound_event je
		LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
		LEFT JOIN jl_safe_sound_event_user jeu ON je.id = jeu.event_id
		WHERE
		je.area_id = #{params._user.company_id}
		AND je.del_flag = '0'
		<if test="params.id != null and params.id != ''">
			AND je.id = #{params.id}
		</if>
		<if test="params.eventCardno != null and params.eventCardno != ''">
			AND jeu.event_cardno = TO_BASE64(AES_ENCRYPT(#{params.eventCardno},'*#id#'))
		</if>
		<if test="params.eventUserName != null and params.eventUserName != ''">
			AND jeu.event_user_name LIKE CONCAT('%',#{params.eventUserName},'%')
		</if>
		<if test="params.eventTel != null and params.eventTel != ''">
			AND jeu.event_tel = #{params.eventTel}
		</if>
		<if test="params.eventType != null and params.eventType != ''">
			AND je.event_type = #{params.eventType}
		</if>
		<if test="params.eventCode != null and params.eventCode != ''">
			AND je.event_code = #{params.eventCode}
		</if>
		<if test="params.cityId != null and params.cityId != ''">
			AND je.city_id = #{params.cityId}
		</if>
		<if test="params.areaId != null and params.areaId != ''">
			AND je.area_id = #{params.areaId}
		</if>
		<if test="params.streetId != null and params.streetId != ''">
			AND je.street_id = #{params.streetId}
		</if>
		<if test="params.communityId != null and params.communityId != ''">
			AND je.community_id = #{params.communityId}
		</if>
		<if test="params.status != null and params.status != ''">
			AND je.status = #{params.status}
		</if>
		<if test="params.gardenId != null and params.gardenId != ''">
			AND je.garden_id = #{params.gardenId}
		</if>
		<if test="params.buildingId != null and params.buildingId != ''">
			AND je.building_id = #{params.buildingId}
		</if>
		<if test="params.floorId != null and params.floorId != ''">
			AND je.floor_id = #{params.floorId}
		</if>
		<if test="params.doorplateId != null and params.doorplateId != ''">
			AND je.doorplate_id = #{params.doorplateId}
		</if>
		<if test="params.eventCategoryId != null and params.eventCategoryId != ''">
			AND je.event_category_id = #{params.eventCategoryId}
		</if>
		<if test="params.eventTypeId != null and params.eventTypeId != ''">
			AND je.event_type_id = #{params.eventTypeId}
		</if>
		<if test="params.isHiddenDanger != null and params.isHiddenDanger != ''">
			AND je.is_hidden_danger = #{params.isHiddenDanger}
		</if>
		<if test="params.isDifficult != null and params.isDifficult != ''">
			AND je.is_difficult = #{params.isDifficult}
		</if>
		<if test="params.isPublicOpinion != null and params.isPublicOpinion != ''">
			AND je.is_public_opinion = #{params.isPublicOpinion}
		</if>
		<if test="params.isEarlyWarning != null and params.isEarlyWarning != ''">
			AND je.is_early_warning = #{params.isEarlyWarning}
		</if>
		<if test="params.isPointEvent != null and params.isPointEvent != ''">
			AND je.is_point_event = #{params.isPointEvent}
		</if>
		<if test="params.occurrenceTime != null and params.occurrenceTime != ''">
			AND DATE_FORMAT(je.occurrence_time,'%Y-%m-%d') = #{params.occurrenceTime}
		</if>
		<if test="params.address != null and params.address != ''">
			AND je.address LIKE CONCAT('%',#{params.address},'%')
		</if>
		<if test="params.resolveWay != null and params.resolveWay != ''">
			AND jefl.resolve_way = #{params.resolveWay}
		</if>
		<if test="params.isResolve != null and params.isResolve != ''">
			AND jefl.is_resolve = #{params.isResolve}
		</if>
		<if test="params.isSatisfaction != null and params.isSatisfaction != ''">
			AND jefl.is_satisfaction = #{params.isSatisfaction}
		</if>
		<if test="params.occurrenceTimeStart != null and params.occurrenceTimeStart != '' and params.occurrenceTimeStart != null and params.occurrenceTimeStart != ''">
			AND #{params.occurrenceTimeStart} >= je.occurrence_time
			AND je.occurrence_time >= #{params.occurrenceTimeStart}
		</if>
		<if test="params.resolveTimeStart != null and params.resolveTimeStart != '' and params.occurrenceTimeEnd != null and params.occurrenceTimeEnd != ''">
			AND #{params.occurrenceTimeEnd} >= je.complete_time
			AND je.complete_time >= #{params.resolveTimeStart}
		</if>
		<if test="params.is_involve_everyone != null and params.is_involve_everyone != ''">
			AND je.is_involve_everyone = #{params.is_involve_everyone}
		</if>
		<if test="params.is_involve_everyone_count != null and params.is_involve_everyone_count != ''">
			AND je.is_involve_everyone_count = #{params.is_involve_everyone_count}
		</if>
		<if test="params.logType!=null and params.logType!=''">
			<if test="params.logType=='0'.toString()">
				and je.status='1'
			</if>
			<if test="params.logType!='0'.toString()">
				HAVING mineLatestLogTypeNew = #{params.logType} and je.status!='1'
			</if>
		</if>
		) a
		ORDER BY
		a.createTime DESC
	</select>

	<select id="getSafeSoundEvent" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
			resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
		SELECT
			je.id,
			je.id eventId,
			je.event_type eventType,
			(SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
			`value`)eventCategoryId,
			(SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` )
			eventTypeId,
			je.is_hidden_danger isHiddenDanger,
			je.is_special_tasks isSpecialTasks,
			je.is_difficult isDifficult,
			je.is_public_opinion isPublicOpinion,
			je.is_early_warning isEarlyWarning,
			je.is_point_event isPointEvent,
			je.involved_number involvedNumber,
			DATE_FORMAT(je.create_time,'%Y-%m-%d %T') createTime,
			DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrenceTime,
			je.city_id cityId,
			(SELECT name FROM sys_area WHERE code = je.city_id) city,
			je.area_id areaId,
			(SELECT name FROM sys_area WHERE code = je.area_id) area,
			je.street_id streetId,
			(SELECT name FROM sys_area WHERE code = je.street_id) street,
			je.community_id communityId,
			(SELECT name FROM sys_area WHERE code = je.community_id) community,
			je.garden_id gardenId,
			je.building_id buildingId,
			je.unit_id unitId,
			je.floor_id floorId,
			je.doorplate_id doorplateId,
			je.event_code eventCode,
			je.event_source eventSource,
			je.address,
			je.dispute_description disputeDescription,
			je.reporting_name reportingName,
			je.reporting_unit reportingUnit,
			je.reporting_garden reportingGarden,
			IF(je.risk_name != '' AND je.risk_name IS NOT NULL,je.risk_name,'') as riskName,
			je.event_category_other_name,
			je.event_category_mini_other_name,
			je.is_involve_everyone,
			je.is_involve_everyone_count,
			je.status as eventStatus,
			je.event_name,
			je.event_tel
		FROM
			jl_safe_sound_event je
		WHERE
			je.del_flag = '0'
		AND je.id = #{id}
	</select>

	<select id="selectSafeSoundUserByEventIdList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlEventUser">
		SELECT
			id,
			event_user_type eventUserType,
			event_user_name eventUserName,
			is_keynote isKeynote,
			keynote_type keynoteType,
			CAST(AES_DECRYPT(FROM_BASE64(event_cardno),'*#id#') AS CHAR) AS eventCardno,
			event_tel eventTel,
			tyshxydm,
			event_represent eventRepresent,
			remark
		FROM
			jl_safe_sound_event_user
		WHERE
			event_id = #{eventId}
	</select>

	<select id="selectEventAllByCardList" parameterType="map" resultType="map">
		SELECT * FROM (
		SELECT
			jl.id,
			jl.event_code,
			DATE_FORMAT(jl.create_time,'%Y-%m-%d %T') create_time,
			jl.city_id,
			jl.area_id,
			jl.street_id,
			jl.community_id,
			jl.status,
			jeu.event_user_name,
			jeu.event_cardno,
			0 type_num,
			'' reason_rejection
		FROM
			jl_event jl
		INNER JOIN jl_event_user jeu ON jl.id = jeu.event_id
		WHERE
		jl.city_id = '220100000000'
		<if test="event_cardno != null and event_cardno != ''">
			AND jeu.event_cardno = TO_BASE64(AES_ENCRYPT(#{event_cardno},'*#id#'))
		</if>
		<if test="event_tel != null and event_tel != ''">
			AND jeu.event_tel = #{event_tel}
		</if>
		UNION ALL
		SELECT
			jl.id,
			jl.event_code,
			DATE_FORMAT(jl.create_time,'%Y-%m-%d %T') create_time,
			jl.city_id,
			jl.area_id,
			jl.street_id,
			jl.community_id,
			jl.status,
			jeu.event_user_name,
			jeu.event_cardno,
			jl.type_num,
			jl.reason_rejection
		FROM
			jl_safe_sound_event jl
		INNER JOIN jl_safe_sound_event_user jeu ON jl.id = jeu.event_id
		WHERE
			jl.city_id = '220100000000'
		<if test="event_cardno != null and event_cardno != ''">
			AND jeu.event_cardno = TO_BASE64(AES_ENCRYPT(#{event_cardno},'*#id#'))
		</if>
		<if test="event_tel != null and event_tel != ''">
			AND jeu.event_tel = #{event_tel}
		</if>
		) a
		GROUP BY id,event_cardno
		ORDER BY create_time DESC
	</select>

	<update id="updateSafeSoundEventTypeNum" parameterType="map">
		UPDATE
			jl_safe_sound_event
		set
			type_num = '2',
			reason_rejection = #{reason_rejection}
		WHERE
			id = #{id}
	</update>

	<select id="findSafeSoundFlowLogProcessInfo" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
		SELECT
		jefl.id,
		jefl.is_resolve isResolve,
		jefl.resolve_name resolveName,
		jefl.resolve_unit resolveUnit,
		jefl.resolve_hierarchy resolveHierarchy,
		jefl.resolve_way resolveWay,
		DATE_FORMAT(jefl.resolve_time,'%Y-%m-%d %T') resolveTime,
		jefl.is_satisfaction isSatisfaction,
		jefl.resolve_process resolveProcess
		FROM
		jl_event_flow_log jefl
		WHERE
		jefl.event_id=#{eventId} AND jefl.del_flag = '0'
		AND jefl.resolve_name IS NOT NULL
		AND jefl.resolve_name != ''
	</select>
</mapper>