<?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.elephant.framework.galaxy.aroad.module.roadcondition.mapper.RoadConditionEventMapper">

	<resultMap id="roadConditionEventMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.entity.RoadConditionEvent">
		<id property="id" column="id"/>
		<result property="eventId" column="event_id"/>
		<result property="roadCode" column="road_code"/>
		<result property="roadName" column="road_name"/>
		<result property="maintainUnit" column="maintain_unit"/>
		<result property="stoem" column="sToeM"/>
		<result property="startm" column="startM"/>
		<result property="endm" column="endM"/>
		<result property="eventType" column="event_type"/>
		<result property="eventTitle" column="event_title"/>
		<result property="eventContent" column="event_content"/>
		<result property="influence" column="influence"/>
		<result property="carsLength" column="cars_length"/>
		<result property="solution" column="solution"/>
		<result property="startPerson" column="start_person"/>
		<result property="startTime" column="start_time"/>
		<result property="startUnit" column="start_unit"/>
		<result property="startUnitNo" column="start_unit_no"/>
		<result property="startBz" column="start_bz"/>
		<result property="preEndTime" column="pre_end_time"/>
		<result property="endPerson" column="end_person"/>
		<result property="endTime" column="end_time"/>
		<result property="endUnit" column="end_unit"/>
		<result property="endUnitNo" column="end_unit_no"/>
		<result property="endReason" column="end_reason"/>
		<result property="endBz" column="end_bz"/>
		<result property="checkPerson" column="check_person"/>
		<result property="checkTime" column="check_time"/>
		<result property="longitude" column="longitude"/>
		<result property="latitude" column="latitude"/>
		<result property="current" column="current"/>
		<result property="size" column="size"/>
	</resultMap>

	<resultMap id="staticByEventTypeMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.staticByEventTypeDto">
		<result property="count" column="count"/>
		<result property="eventType" column="event_type"/>
	</resultMap>

	<resultMap id="staticByInfluenceMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.staticByInfluenceDto">
		<result property="count" column="count"/>
		<result property="influence" column="influence"/>
	</resultMap>

	<resultMap id="staticByMonthMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.staticByMonthDto">
		<result property="count" column="count"/>
		<result property="year" column="year"/>
		<result property="month" column="month"/>
	</resultMap>

	<resultMap id="staticByRoadCodeMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.staticByRoadCodeDto">
		<result property="count" column="count"/>
		<result property="roadcode" column="roadcode"/>
	</resultMap>

	<resultMap id="staticByCityMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.staticByCityDto">
		<result property="count" column="count"/>
		<result property="city" column="city"/>
	</resultMap>

	<resultMap id="staticByYHCityMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.staticByYHCityDto">
		<result property="count" column="count"/>
		<result property="startunit" column="startunit"/>
	</resultMap>

	<resultMap id="citysWithOrganizesMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.CityNameDto">
		<result property="cityName" column="city"/>
		<result property="organizeName" column="organize"/>
	</resultMap>

	<resultMap id="CoordinateDtoMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.CoordinateDto">
		<id column="id" property="id"/>
		<result property="eventType" column="event_type"/>
		<result property="eventTitle" column="event_title"/>
		<result column="longitude" property="longitude"/>
		<result column="latitude" property="latitude"/>
	</resultMap>

	<resultMap id="SaveUnitMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.SaveUnitDto">
		<result property="unitId" column="unit_id"/>
		<result property="unitName" column="unit_name"/>
	</resultMap>

	<resultMap id="DeptUnitMap"
	           type="com.elephant.framework.galaxy.aroad.module.roadcondition.dto.DeptUnitDto">
		<result property="unitId" column="unit_id"/>
		<result property="deptId" column="dept_id"/>
	</resultMap>

	<select id="getUnitTree" resultMap="SaveUnitMap">
		select  * from roadcondition_unit
	</select>

	<delete id="deleteUnit">
		delete from roadcondition_unit;
	</delete>
	<insert id="saveUnit">
		INSERT INTO roadcondition_unit ( unit_id, unit_name )
		SELECT start_unit_no, maintain_unit FROM road_condition_event
		GROUP BY start_unit_no, maintain_unit
	</insert>

	<delete id="deleteUnitByDept">
		delete from roadcondition_emergencyevent where dept_id=#{deptId}
	</delete>

	<insert id="saveUnitWithDept">
		insert into roadcondition_emergencyevent (dept_id, unit_id) values (#{deptId},#{unit})
	</insert>

	<select id="getRoadConditionEventPage" resultMap="roadConditionEventMap"
	        parameterType="com.elephant.framework.galaxy.aroad.module.roadcondition.entity.RoadConditionEvent">
		SELECT
		*
		FROM road_condition_event
		<where>
			<if test="roadConditionEvent.startUnit != null">maintain_unit like CONCAT('%',#{roadConditionEvent.startUnit},'%') and</if>
			<if test="roadConditionEvent.eventType != null">event_type like concat('%',#{roadConditionEvent.eventType},'%') and</if>
			<if test="roadConditionEvent.influence != null">influence = #{roadConditionEvent.influence} and</if>
			<if test="roadConditionEvent.startTime != null">start_time &gt; #{roadConditionEvent.startTime} and</if>
			<if test="roadConditionEvent.endTime != null">end_time &lt; #{roadConditionEvent.endTime} and</if>
			start_unit_no in (#{units})
		</where>
	</select>

	<select id="getRoadConditionEventMobilePage" resultMap="roadConditionEventMap">
		SELECT *
		FROM road_condition_event
	</select>

	<select id="getById" resultMap="roadConditionEventMap">
		SELECT *
		FROM road_condition_event
		where id = #{id}
	</select>

	<select id="staticByEventType" resultMap="staticByEventTypeMap">
		SELECT count(*) as count,
		       event_type
		FROM road_condition_event
		GROUP BY event_type
	</select>

	<select id="staticByInfluence" resultMap="staticByInfluenceMap">
		SELECT count(*) AS count,
		       influence
		FROM road_condition_event
		GROUP BY influence
	</select>

	<select id="staticByMonth" resultMap="staticByMonthMap">
		SELECT count(*)          AS count,
		       YEAR(start_time)  AS year,
		       MONTH(start_time) AS month
		FROM road_condition_event
		WHERE event_type = '养护施工信息'
		GROUP BY YEAR(start_time),
		         MONTH(start_time)
	</select>

	<select id="staticByRoadCode" resultMap="staticByRoadCodeMap">
		SELECT count(*)  AS count,
		       road_code as roadcode
		FROM road_condition_event
		WHERE event_type = '养护施工信息'
		GROUP BY road_code
	</select>

	<select id="staticByCity" resultMap="staticByCityMap">
		SELECT
			count( * ) AS count,
# 			maintain_unit,
			SUBSTRING( maintain_unit FROM 1 FOR 2 ) as city
		FROM
			road_condition_event
		where SUBSTRING( maintain_unit FROM 1 FOR 2 )!='山东'
		GROUP BY
			maintain_unit
	</select>

	<select id="staticByYHCity" resultMap="staticByYHCityMap">
		SELECT count(*)   as count,
		       maintain_unit as startunit
		FROM road_condition_event
		WHERE event_type = '养护施工信息'
		GROUP BY maintain_unit
	</select>

	<select id="getTree" resultMap="citysWithOrganizesMap">
		SELECT city,
		       fullname as organize
		FROM base_organize
		WHERE city IS NOT NULL
		  AND city != '山东'
		GROUP BY city,
		         fullname
	</select>

	<select id="getUnitByDept" resultMap="DeptUnitMap">
		select unit_id from roadcondition_emergencyevent
		where dept_id = #{deptId}
	</select>

	<select id="getCoordinates" resultMap="CoordinateDtoMap">
		select id, longitude, latitude, event_type,event_title
		from `road_condition_event`
		where start_unit_no in (#{units});
	</select>

	<select id="getTQCoordinates" resultMap="CoordinateDtoMap">
		select id, longitude, latitude, event_type,event_title
		from `road_condition_event`
		where event_title = '异常天气';
	</select>

	<select id="getYHCoordinates" resultMap="roadConditionEventMap">
		select id, longitude, latitude, event_type,event_title
		from `road_condition_event`
		where event_title = '养护施工';
	</select>

	<select id="getTFCoordinates" resultMap="CoordinateDtoMap">
		select id, longitude, latitude, event_type,event_title
		from `road_condition_event`
		where event_title = '突发事件';
	</select>

	<select id="getSFZCoordinates" resultMap="CoordinateDtoMap">
		select id, longitude, latitude, event_type,event_title
		from `road_condition_event`
		where event_title = '收费站异常';
	</select>

	<select id="selectRoadMaintainEvent"
	        parameterType="com.elephant.framework.galaxy.aroad.module.roadcondition.entity.RoadConditionEvent" resultMap="roadConditionEventMap">
		SELECT * FROM road_condition_event
		WHERE
		<if test="id != null">
			id = #{id} AND
		</if>
		<if test="id == null">
			ISNULL(id) AND
		</if>

		<if test="eventId != null">
			event_id = #{eventId} AND
		</if>
		<if test="eventId == null">
			ISNULL(event_id) AND
		</if>

		<if test="roadCode != null">
			road_code = #{roadCode} AND
		</if>
		<if test="roadCode == null">
			ISNULL(road_code) AND
		</if>

		<if test="roadName != null">
			road_name = #{roadName} AND
		</if>
		<if test="roadName == null">
			ISNULL(road_name) AND
		</if>

		<if test="maintainUnit != null">
			maintain_unit = #{maintainUnit} AND
		</if>
		<if test="maintainUnit == null">
			ISNULL(maintain_unit) AND
		</if>

		<if test="stoem != null">
			sToeM = #{stoem} AND
		</if>
		<if test="stoem == null">
			ISNULL(sToeM) AND
		</if>

		<if test="startm != null">
			startM = #{startm} AND
		</if>
		<if test="startm == null">
			ISNULL(startM) AND
		</if>

		<if test="endm != null">
			endM = #{endm} AND
		</if>
		<if test="endm == null">
			ISNULL(endM) AND
		</if>

		<if test="eventType != null">
			event_type = #{eventType} AND
		</if>
		<if test="eventType == null">
			ISNULL(event_type) AND
		</if>

		<if test="eventTitle != null">
			event_title = #{eventTitle} AND
		</if>
		<if test="eventTitle == null">
			ISNULL(event_title) AND
		</if>

		<if test="eventContent != null">
			event_content = #{eventContent} AND
		</if>
		<if test="eventContent == null">
			ISNULL(event_content) AND
		</if>

		<if test="influence != null">
			influence = #{influence} AND
		</if>
		<if test="influence == null">
			ISNULL(influence) AND
		</if>

		<if test="carsLength != null">
			cars_length = #{carsLength} AND
		</if>
		<if test="carsLength == null">
			ISNULL(cars_length) AND
		</if>

		<if test="solution != null">
			solution = #{solution} AND
		</if>
		<if test="solution == null">
			ISNULL(solution) AND
		</if>

		<if test="startPerson != null">
			start_person = #{startPerson} AND
		</if>
		<if test="startPerson == null">
			ISNULL(start_person) AND
		</if>

		<if test="startTime != null">
			start_time = #{startTime} AND
		</if>
		<if test="startTime == null">
			ISNULL(start_time) AND
		</if>

		<if test="startUnit != null">
			start_unit = #{startUnit} AND
		</if>
		<if test="startUnit == null">
			ISNULL(start_unit) AND
		</if>

		<if test="startUnitNo != null">
			start_unit_no = #{startUnitNo} AND
		</if>
		<if test="startUnitNo == null">
			ISNULL(start_unit_no) AND
		</if>

		<if test="startBz != null">
			start_bz = #{startBz} AND
		</if>
		<if test="startBz == null">
			ISNULL(start_bz) AND
		</if>

		<if test="preEndTime != null">
			pre_end_time = #{preEndTime} AND
		</if>
		<if test="preEndTime == null">
			ISNULL(pre_end_time) AND
		</if>

		<if test="endPerson != null">
			end_person = #{endPerson} AND
		</if>
		<if test="endPerson == null">
			ISNULL(end_person) AND
		</if>

		<if test="endTime != null">
			end_time = #{endTime} AND
		</if>
		<if test="endTime == null">
			ISNULL(end_time) AND
		</if>

		<if test="endUnit != null">
			end_unit = #{endUnit} AND
		</if>
		<if test="endUnit == null">
			ISNULL(end_unit) AND
		</if>

		<if test="endUnitNo != null">
			end_unit_no = #{endUnitNo} AND
		</if>
		<if test="endUnitNo == null">
			ISNULL(end_unit_no) AND
		</if>

		<if test="endReason != null">
			end_reason = #{endReason} AND
		</if>
		<if test="endReason == null">
			ISNULL(end_reason) AND
		</if>

		<if test="endBz != null">
			end_bz = #{endBz} AND
		</if>
		<if test="endBz == null">
			ISNULL(end_bz) AND
		</if>

		<if test="checkPerson != null">
			check_person = #{checkPerson} AND
		</if>
		<if test="checkPerson == null">
			ISNULL(check_person) AND
		</if>

		<if test="checkTime != null">
			check_time = #{checkTime} AND
		</if>
		<if test="checkTime == null">
			ISNULL(check_time) AND
		</if>

		<if test="longitude != null">
			longitude = #{longitude} AND
		</if>
		<if test="longitude == null">
			ISNULL(longitude) AND
		</if>

		<if test="latitude != null">
			latitude = #{latitude} AND
		</if>
		<if test="latitude == null">
			ISNULL(latitude) AND
		</if>
	</select>

	<update id="updateBatchByEventId"  parameterType="java.util.List">
		UPDATE  road_condition_event
		WHERE  event_id IN
		<foreach collection="eventIdList" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
	</update>

	<update id="deleteBatchByEvent"
	        parameterType="com.elephant.framework.galaxy.aroad.module.roadcondition.entity.RoadConditionEvent">
		UPDATE  road_condition_event
		WHERE
		<if test="id != null">
			id = #{id} AND
		</if>
		<if test="id == null">
			ISNULL(id) AND
		</if>

		<if test="eventId != null">
			event_id = #{eventId} AND
		</if>
		<if test="eventId == null">
			ISNULL(event_id) AND
		</if>

		<if test="roadCode != null">
			road_code = #{roadCode} AND
		</if>
		<if test="roadCode == null">
			ISNULL(road_code) AND
		</if>

		<if test="roadName != null">
			road_name = #{roadName} AND
		</if>
		<if test="roadName == null">
			ISNULL(road_name) AND
		</if>

		<if test="maintainUnit != null">
			maintain_unit = #{maintainUnit} AND
		</if>
		<if test="maintainUnit == null">
			ISNULL(maintain_unit) AND
		</if>

		<if test="stoem != null">
			sToeM = #{stoem} AND
		</if>
		<if test="stoem == null">
			ISNULL(sToeM) AND
		</if>

		<if test="startm != null">
			startM = #{startm} AND
		</if>
		<if test="startm == null">
			ISNULL(startM) AND
		</if>

		<if test="endm != null">
			endM = #{endm} AND
		</if>
		<if test="endm == null">
			ISNULL(endM) AND
		</if>

		<if test="eventType != null">
			event_type = #{eventType} AND
		</if>
		<if test="eventType == null">
			ISNULL(event_type) AND
		</if>

		<if test="eventTitle != null">
			event_title = #{eventTitle} AND
		</if>
		<if test="eventTitle == null">
			ISNULL(event_title) AND
		</if>

		<if test="eventContent != null">
			event_content = #{eventContent} AND
		</if>
		<if test="eventContent == null">
			ISNULL(event_content) AND
		</if>

		<if test="influence != null">
			influence = #{influence} AND
		</if>
		<if test="influence == null">
			ISNULL(influence) AND
		</if>

		<if test="carsLength != null">
			cars_length = #{carsLength} AND
		</if>
		<if test="carsLength == null">
			ISNULL(cars_length) AND
		</if>

		<if test="solution != null">
			solution = #{solution} AND
		</if>
		<if test="solution == null">
			ISNULL(solution) AND
		</if>

		<if test="startPerson != null">
			start_person = #{startPerson} AND
		</if>
		<if test="startPerson == null">
			ISNULL(start_person) AND
		</if>

		<if test="startTime != null">
			start_time = #{startTime} AND
		</if>
		<if test="startTime == null">
			ISNULL(start_time) AND
		</if>

		<if test="startUnit != null">
			start_unit = #{startUnit} AND
		</if>
		<if test="startUnit == null">
			ISNULL(start_unit) AND
		</if>

		<if test="startUnitNo != null">
			start_unit_no = #{startUnitNo} AND
		</if>
		<if test="startUnitNo == null">
			ISNULL(start_unit_no) AND
		</if>

		<if test="startBz != null">
			start_bz = #{startBz} AND
		</if>
		<if test="startBz == null">
			ISNULL(start_bz) AND
		</if>

		<if test="preEndTime != null">
			pre_end_time = #{preEndTime} AND
		</if>
		<if test="preEndTime == null">
			ISNULL(pre_end_time) AND
		</if>

		<if test="endPerson != null">
			end_person = #{endPerson} AND
		</if>
		<if test="endPerson == null">
			ISNULL(end_person) AND
		</if>

		<if test="endTime != null">
			end_time = #{endTime} AND
		</if>
		<if test="endTime == null">
			ISNULL(end_time) AND
		</if>

		<if test="endUnit != null">
			end_unit = #{endUnit} AND
		</if>
		<if test="endUnit == null">
			ISNULL(end_unit) AND
		</if>

		<if test="endUnitNo != null">
			end_unit_no = #{endUnitNo} AND
		</if>
		<if test="endUnitNo == null">
			ISNULL(end_unit_no) AND
		</if>

		<if test="endReason != null">
			end_reason = #{endReason} AND
		</if>
		<if test="endReason == null">
			ISNULL(end_reason) AND
		</if>

		<if test="endBz != null">
			end_bz = #{endBz} AND
		</if>
		<if test="endBz == null">
			ISNULL(end_bz) AND
		</if>

		<if test="checkPerson != null">
			check_person = #{checkPerson} AND
		</if>
		<if test="checkPerson == null">
			ISNULL(check_person) AND
		</if>

		<if test="checkTime != null">
			check_time = #{checkTime} AND
		</if>
		<if test="checkTime == null">
			ISNULL(check_time) AND
		</if>

		<if test="longitude != null">
			longitude = #{longitude} AND
		</if>
		<if test="longitude == null">
			ISNULL(longitude) AND
		</if>

		<if test="latitude != null">
			latitude = #{latitude} AND
		</if>
		<if test="latitude == null">
			ISNULL(latitude) AND
		</if>
	</update>
</mapper>
