<?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.patrol" name="app网格巡查业务">
    <select id="getPatrolRecordNum" parameterType="map" resultType="map">
	    select
		   count(1) record_num
		from jl_grid_patrol_record a
		where a.del_flag = '0'
		and a.patrol_status = '1'
		and a.cardno = #{card_no}
		<if test="garden_id != null and garden_id != ''">
			and a.garden_id = #{garden_id}
		</if>
	</select>
	
	<select id="getEventNum" parameterType="map" resultType="map">
	    select
		   count(b.id) report_num,
		   IFNULL(SUM(b.consumption_times),0) consumption_times
		from jl_grid_patrol_record a
		left join jl_event b on a.id = b.record_id
		where a.del_flag = '0'
		and a.patrol_status = '1'
		and b.del_flag = '0'
		and a.cardno = #{card_no}
		<if test="garden_id != null and garden_id != ''">
			and a.garden_id = #{garden_id}
		</if>
	</select>
	
	<select id="dsPatrolTimeList" parameterType="map" resultType="map">
		<!-- 
		select
		pt.id,
		pt.name,
		pt.duration,
		pt.patrol_project,
		pt.road_length,
		DATE_FORMAT(pt.statr_time,'%T') as start_time,
		DATE_FORMAT(pt.end_time,'%T') as end_time,
		IF((
		SELECT
		CASE pr.patrol_status
		WHEN '1' THEN '已巡查'
		WHEN '2' THEN '进行中'
		END
		FROM jl_grid_patrol_record pr
		WHERE pr.garden_id = dr.garden_id
		AND pr.patrol_time_id = pt.id
		AND pr.cardno = dr.cardno
		AND DATEDIFF(pr.create_time,NOW()) = 0
		) IS NULL,'未巡查',
		(SELECT
		CASE pr.patrol_status
		WHEN '1' THEN '已巡查'
		WHEN '2' THEN '进行中'
		END
		FROM jl_grid_patrol_record pr
		WHERE pr.garden_id = dr.garden_id
		AND pr.patrol_time_id = pt.id
		AND pr.cardno = dr.cardno
		AND DATEDIFF(pr.create_time,NOW()) = 0)) patrol_status_name,
		IF((
		SELECT
		pr.patrol_status
		FROM jl_grid_patrol_record pr
		WHERE pr.garden_id = dr.garden_id
		AND pr.patrol_time_id = pt.id
		AND pr.cardno = dr.cardno
		AND DATEDIFF(create_time,NOW()) = 0) IS NULL,'0',
		(SELECT
		pr.patrol_status
		FROM jl_grid_patrol_record pr
		WHERE pr.garden_id = dr.garden_id
		AND pr.patrol_time_id = pt.id
		AND pr.cardno = dr.cardno
		AND DATEDIFF(create_time,NOW()) = 0)) as status
		from ( SELECT
		DISTINCT cardno,
		garden_id
		FROM jl_grid_garden_user
		where cardno =  #{card_no}
		<if test="garden_id != null and garden_id != ''">
			and garden_id = #{garden_id}
		</if> ) dr
		join patrol_time pt on dr.garden_id = pt.ds_id
		order by pt.statr_time asc
		 -->
		 
		 SELECT
			pt.id,
			pt.name,
			pt.duration,
			pt.patrol_project,
			pt.road_length,
			DATE_FORMAT( pt.statr_time, '%T' ) AS start_time,
			DATE_FORMAT( pt.end_time, '%T' ) AS end_time,
		CASE WHEN pr.id IS NULL THEN
			'未巡查'  WHEN pr.patrol_status = '1' THEN
			'已巡查' ELSE '进行中' END patrol_status_name,
		CASE WHEN pr.id IS NULL THEN '0' ELSE pr.patrol_status END status 
		FROM
			jl_grid_garden_user dr
			INNER JOIN jl_patrol_time pt ON dr.garden_id = pt.garden_id
			LEFT JOIN (select * from jl_grid_patrol_record where DATEDIFF(create_time,NOW()) = 0 ) pr ON pr.garden_id = dr.garden_id 
			AND pr.patrol_time_id = pt.id 
			AND pr.cardno = dr.cardno 
		WHERE
			dr.cardno = #{card_no}
			AND dr.garden_id = #{garden_id}
		ORDER BY
			pt.statr_time ASC
	</select>
	
	<select id="getGridInfo" parameterType="map" resultType="map">
		select
		dr.name manager,
		ds.name grid_name,
		REPLACE(csc_mapjson_ht,' ','') csc_mapjson_ht,
		ds.id garden_id,
		dx.count patrol_project_num
		from jl_grid_garden ds
		inner join jl_grid_garden_user dr on ds.id = dr.garden_id
		left join 
		(SELECT count(garden_id) count, garden_id from jl_grid_substance WHERE del_flag = '0' and garden_id = #{garden_id}) dx 
		on ds.id = dx.garden_id
		where dr.del_flag = '0'
		and ds.del_flag = '0'
		and dr.cardno = #{card_no}
		and ds.id = #{garden_id}
		limit 1
    </select>
    
   <select id="selectGridListByRecord" parameterType="map" resultType="map"> 
		select
		dx.id,
		dx.name patrol_item_name,
		REPLACE(dx.csc_mapjson_ht,' ','') csc_mapjson
		from jl_grid_patrol_record_substance dx where record_id=#{record_id} and csc_mapjson_ht is not null
    </select>
    
    <select id="selectGridList" parameterType="map" resultType="map"> 
        select
             dx.id,
             dx.name patrol_item_name,
		REPLACE(dx.csc_mapjson_ht,' ','') csc_mapjson
		from jl_grid_garden_user dr
		left join jl_grid_substance dx
		on dr.garden_id = dx.garden_id
		where dr.del_flag = '0'
		and dx.del_flag = '0'
		and dr.cardno = #{card_no}
		and dx.csc_mapjson_ht is not null and dr.garden_id = #{garden_id}
    </select>
    
	
	<select id="selectLocationPositionList" parameterType="map" resultType="map">
	     select
		     id,
			 lng,
			 lat,
			 record_id,
			 point_id,
			 state,
			 case state
			 when '1' then '继续巡查'
			 when '2' then '暂停巡查'
			 end as state_name,
			 CAST(create_time AS char) create_time
		from jl_grid_patrol_pause_location
		<where>
		     record_id = #{record_id}
		     <if test="state != null and state != ''">
		         and state = #{state}
		     </if>
		     <if test="point_id != null and point_id != ''">
		         and point_id = #{point_id}
		     </if>
		</where>
	</select>
	
	<insert id="insertLocationPosition" parameterType="map">
	     insert into jl_grid_patrol_pause_location(
	         id,
	         lng,
	         lat,
	         record_id,
	         point_id,
	         state,
	         create_time
	     )values(
	        #{uuid},
	        #{lng},
	        #{lat},
	        #{record_id},
	        #{point_id},
	        #{state},
	        #{create_time}
	     )
	</insert>
	
	<select id="getPatroEventsList" parameterType="map" resultType="map">
	    SELECT
			a.id,
			b.label claim_title,
			address area,
			date_format( occurrence_time, '%Y-%m-%d %T' ) AS create_time,
			dispute_description adescribe 
		FROM
			jl_event a
			INNER JOIN sys_dict b ON a.event_category_id = b.VALUE
		WHERE
			record_id = #{record_id}
			and  b.parent_id='0'
			and b.type = 'event_category' 
			order by occurrence_time desc 
	</select>
	
	<insert id="insertPatrolItems" parameterType="map">
        INSERT INTO jl_grid_patrol_record_substance (
        id,
        name,
        company_code,
        city_id,
        area_id,
        street_id,
        community_id,
        garden_id,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type,
        wj_code,
        wj_fzr,
        wj_fzr_phone,
        wj_address,
        csc_mapjson,
        csc_mapjson_ht,
        wj_img,
        shipin_url,
        substanceindex,
        is_checked,
        record_id
        ) VALUES (
        #{id},
        #{name},
        #{company_code},
        #{city_id},
        #{area_id},
        #{street_id},
        #{community_id},
        #{garden_id},
        #{create_by},
        #{create_time},
        #{update_by},
        #{update_time},
        #{map_type},
        #{wj_code},
        #{wj_fzr},
        #{wj_fzr_phone},
        #{wj_address},
        #{csc_mapjson},
        #{csc_mapjson_ht},
        #{wj_img},
        #{shipin_url},
        #{substanceindex},
        #{is_checked},
        #{record_id});
    </insert>

    <update id="updateSubstanceCheckState" parameterType="map">
        update jl_grid_patrol_record_substance set
        is_checked = #{is_checked}
        where id = #{id} and record_id = #{record_id}
    </update>
    
    <select id="getGardenData" parameterType="map" resultType="map">
	  	SELECT
        *
        from
        jl_grid_garden
        <where>
	        <if test="id != null and id !='' ">
	            AND id=#{id,jdbcType=VARCHAR}
	        </if>
	        <if test="company_code != null and company_code !='' ">
	            AND company_code=#{company_code,jdbcType=VARCHAR}
	        </if>
        </where>
        limit 1
	</select>
	
	<select id="selectGardenUserByCardno" parameterType="map" resultType="map" >
		SELECT * FROM jl_grid_garden_user a WHERE a.cardno = #{card_no} and garden_id=#{garden_id}
	</select>
    
    <select id="selectSubstanceByGardenId" parameterType="map" resultType="map">
        select
        id, name, remark, company_code, city_id, area_id, street_id, community_id, garden_id, del_flag,
        create_by, create_time, update_by, update_time, map_type, wj_code, wj_fzr,
        wj_fzr_phone, wj_address, csc_mapjson, csc_mapjson_ht, wj_img, shipin_url,substanceindex
        from jl_grid_substance
        <where>
            <if test="garden_id != null">
                and garden_id = #{garden_id}
            </if>
            <if test="del_flag != null">
                and del_flag = #{del_flag}
            </if>
        </where>
    </select>
    
    <insert id="insertPatrolRecord" parameterType="map">
	    insert into jl_grid_patrol_record(
	        id,
	        del_flag,
	        patrol_status,
	        patrol_time_id,
	        create_time,
	        start_time,
	        create_time_str,
		    patrol_name,
		    city_id,
		    area_id,
		    street_id,
		    community_id,
			garden_id,
		    cardno,
		    item_count,
		    garden_user_id,
			patrol_region,
			patrol_type
	    )values(
	        #{uuid},
	        '0',
	        #{patrol_status},
	        #{patrol_time_id},
	        #{nowDate},
	        #{nowDate},
	        #{create_time_str},
		    #{patrol_name},
		    #{city_id},
		    #{area_id},
		    #{street_id},
		    #{community_id},
			#{garden_id},
		    #{card_no},
		    #{item_count},
		    #{garden_user_id},
			#{patrol_region},
			#{patrol_type}
	    )
	</insert>
	
	<update id="updatePatrolRecordInfo" parameterType="map" >
	    update jl_grid_patrol_record
	    <set>
	      <if test="patrol_time_id != null" >
	        patrol_time_id = #{patrol_time_id,jdbcType=VARCHAR},
	      </if>
	      <if test="city_id != null" >
	        city_id = #{city_id,jdbcType=VARCHAR},
	      </if>
	      <if test="area_id != null" >
	        area_id = #{area_id,jdbcType=VARCHAR},
	      </if>
	      <if test="street_id != null" >
	        street_id = #{street_id,jdbcType=VARCHAR},
	      </if>
	      <if test="community_id != null" >
	        community_id = #{community_id,jdbcType=VARCHAR},
	      </if>
	      <if test="garden_id != null" >
	        garden_id = #{garden_id,jdbcType=VARCHAR},
	      </if>
	      <if test="garden_user_id != null" >
	        garden_user_id = #{garden_user_id,jdbcType=VARCHAR},
	      </if>
	      <if test="patrol_name != null" >
	        patrol_name = #{patrol_name,jdbcType=VARCHAR},
	      </if>
	      <if test="cardno != null" >
	        cardno = #{cardno,jdbcType=VARCHAR},
	      </if>
	      <if test="patrol_project != null" >
	        patrol_project = #{patrol_project,jdbcType=VARCHAR},
	      </if>
	      <if test="start_time != null and start_time != ''" >
	        start_time = #{start_time,jdbcType=TIMESTAMP},
	      </if>
	      <if test="end_time != null and end_time != ''" >
	        end_time = #{end_time,jdbcType=TIMESTAMP},
	      </if>
	      <if test="patrol_distance != null" >
	        patrol_distance = #{patrol_distance,jdbcType=VARCHAR},
	      </if>
	      <if test="patrol_type != null" >
	        patrol_type = #{patrol_type,jdbcType=VARCHAR},
	      </if>
	      <if test="patrol_region != null" >
	        patrol_region = #{patrol_region,jdbcType=VARCHAR},
	      </if>
	      <if test="patrol_location != null" >
	        patrol_location = #{patrol_location,jdbcType=VARCHAR},
	      </if>
	      <if test="current_duration != null" >
	        current_duration = #{current_duration,jdbcType=VARCHAR},
	      </if>
	      <if test="time != null" >
	        time = #{time,jdbcType=VARCHAR},
	      </if>
	      <if test="current_distance != null" >
	        current_distance = #{current_distance,jdbcType=VARCHAR},
	      </if>
	      <if test="patrol_status != null" >
	        patrol_status = #{patrol_status,jdbcType=VARCHAR},
	      </if>
	      <if test="del_flag != null" >
	        del_flag = #{del_flag,jdbcType=VARCHAR},
	      </if>
	      <if test="cost_seconds != null" >
	        cost_seconds = #{cost_seconds,jdbcType=INTEGER},
	      </if>
	      <if test="item_count != null" >
	        item_count = #{item_count,jdbcType=INTEGER},
	      </if>
	      <if test="report_count != null" >
	        report_count = #{report_count,jdbcType=INTEGER},
	      </if>
	      <if test="update_time != null and update_time != ''" >
	        update_time = #{update_time,jdbcType=TIMESTAMP},
	      </if>
	      <if test="create_time_str != null" >
	        create_time_str = #{create_time_str,jdbcType=VARCHAR},
	      </if>
	      <if test="photo != null" >
	        photo = #{photo,jdbcType=LONGVARCHAR},
	      </if>
	      <if test="current_item != null" >
	        current_item = #{current_item,jdbcType=VARCHAR},
	      </if>
	      <if test="checked_items != null" >
	        checked_items = #{checked_items,jdbcType=VARCHAR},
	      </if>
	      <if test="hidden_danger_desc != null and hidden_danger_desc != ''" >
	        hidden_danger_desc = #{hidden_danger_desc,jdbcType=VARCHAR},
	        hidden_danger_state = '1',
	      </if>
	    </set>
	    where id = #{id,jdbcType=VARCHAR}
	  </update>
	  
	  <select id="getPatrolRecordInfo" parameterType="map" resultType="map">
			select
			pr.id,
			pr.patrol_name,
			pr.cardno,
			pr.patrol_project,
			cast(pr.start_time as char) start_time,
			cast(pr.end_time as char) end_time,
			pr.patrol_distance,
			pr.patrol_type,
			pr.patrol_region,
			pr.patrol_location,
			pr.photo,
			pr.current_duration,
			pr.time,
			pr.current_item,
			pr.current_distance,
			pr.checked_items
			from jl_grid_patrol_record pr left join jl_patrol_time pt
			on pr.garden_id = pt.garden_id
			AND pr.patrol_time_id = pt.id
			where pr.del_flag = '0'
			and pr.patrol_status = '2'
			and pr.garden_id = #{garden_id}
			and pr.cardno = #{card_no}
			and pt.id = #{task_id}
			ORDER BY pr.create_time DESC
			limit 1
		</select>
		
		<select id="selectPatrolEventReportCount" parameterType="string" resultType="int">
	        select count(id) count from jl_event
	        where record_id = #{record_id}
	    </select>
	    
	<select id="getPatrolHistoricalTrace" parameterType="map" resultType="map">
	    select
	       jsonparams
	    from jl_grid_patrol_historical_trace
	    where record_id = #{record_id}
	    limit 1
	</select>
	
	<select id="getPatrolRecordPage" parameterType="page" resultType="map">
        select pr.id,
        pr.patrol_name,
		cast(AES_DECRYPT(FROM_BASE64 (pr.cardno),'*#id#') AS CHAR) card_no,
        pr.patrol_project,
        date_format(pr.start_time, '%Y-%m-%d %T') as start_time,
        date_format(pr.end_time, '%Y-%m-%d %T') as end_time,
        pr.patrol_distance,
        date_format(pr.create_time, '%Y-%m-%d') as create_time,
        pr.patrol_type,
        pr.patrol_region,
        pr.patrol_location,
        pr.photo,
        pr.patrol_status,
        pr.patrol_time_id task_id,
        pr.del_flag
        from jl_grid_patrol_record pr
        <where>
            pr.del_flag = '0'
            and pr.patrol_status = '1'
            <if test="params.patrol_name != null and params.patrol_name != ''">
               and patrol_name = #{params.patrol_name}
            </if>
            <if test="params.card_no != null and params.card_no != ''">
               and  cardno = #{params.card_no}
            </if>
            <if test="params.patrol_project != null and params.patrol_project != ''">
              and  patrol_project = #{params.patrol_project}
            </if>
            <if test="params.create_time != null and params.create_time != ''">
              and date(create_time) = date(#{params.create_time})
            </if>
            <if test="params.garden_id != null and params.garden_id != ''">
                and pr.garden_id = #{params.garden_id}
            </if>
        </where>
        order by start_time desc
    </select>
	
	<select id="getPatrolRecordPageForPC" parameterType="page" resultType="map">
        select 
        pr.id,
        patrol_name,
        patrol_project,
        date_format(start_time, '%Y-%m-%d %T') as start_time,
        date_format(end_time, '%Y-%m-%d %T') as end_time,
        patrol_distance,
        patrol_type,
        patrol_location,
        photo,
        patrol_status,
        patrol_time_id task_id,
		city.NAME city_name,
		area.NAME area_name,
		street.NAME street_name,
		community.NAME community_name,
		patrol_region
		<if test="params.hidden_danger_query != null and params.hidden_danger_query != ''">
            ,
            hidden_danger_desc,
            hidden_danger_state,
    		disposal_user_name,
    		disposal_user_departinfo,
    		disposal_msg,
    		date_format(disposal_time, '%Y-%m-%d %T') as disposal_time 
        </if>
        from jl_grid_patrol_record pr
        LEFT JOIN sys_area city ON city.id = city_id
		LEFT JOIN sys_area area ON area.id = area_id
		LEFT JOIN sys_area street ON street.id = street_id
		LEFT JOIN sys_area community ON community.id = community_id
        <where>
            pr.del_flag = '0'
            and patrol_status = '1'
            <if test="params.disposal_time_begin != null ">
               and disposal_time >= #{params.disposal_time_begin}
            </if>
            <if test="params.disposal_time_end != null ">
               and  #{params.disposal_time_end} >= disposal_time
            </if>
            <if test="params.patrol_time_begin != null ">
               and start_time >= #{params.patrol_time_begin}
            </if>
            <if test="params.patrol_time_end != null ">
               and #{params.patrol_time_end} >= start_time
            </if>
            <if test="params.patrol_name != null and params.patrol_name != ''">
               and patrol_name = #{params.patrol_name}
            </if>
            <if test="params.city_id != null and params.city_id != ''">
                and city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                and area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                and street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                and community_id = #{params.community_id}
            </if>
            <if test="params.garden_id != null and params.garden_id != ''">
                and garden_id = #{params.garden_id}
            </if>
            <choose>
            	<when test="params._user.company_grade == '3'.toString() ">
					and city_id=#{params._user.company_id , jdbcType=VARCHAR}
				</when>
				<when test="params._user.company_grade == '4'.toString() ">
					and area_id=#{params._user.company_id , jdbcType=VARCHAR}
				</when>
				<when test="params._user.company_grade == '5'.toString() ">
					and street_id=#{params._user.company_id , jdbcType=VARCHAR}
				</when>
				<when test="params._user.company_grade == '6'.toString() ">
					and community_id=#{params._user.company_id , jdbcType=VARCHAR}
				</when>
			</choose>
			<if test="params.hidden_danger_query != null and params.hidden_danger_query != ''">
                and hidden_danger_state != '0'
            </if>
			<if test="params.hidden_danger_state != null and params.hidden_danger_state != ''">
                and hidden_danger_state = #{params.hidden_danger_state}
            </if>
        </where>
        order by start_time desc
    </select>
    
    <insert id="insertPatrolPause" parameterType="map">
	     insert into jl_grid_patrol_pause(
	         id,
	         record_id,
	         reason,
	         reason_desc,
	         lng,
	         lat,
	         create_time
	     )values(
	         #{uuid},
	         #{record_id},
	         #{reason},
	         #{reason_desc},
	         #{lng},
	         #{lat},
	         #{create_time}
	     )
	</insert>
	
	<select id="patrolRecordDetail" parameterType="map" resultType="map">
        select a.id,
        a.patrol_name,
		cast(AES_DECRYPT(FROM_BASE64 (a.cardno),'*#id#') AS CHAR) card_no,
        a.patrol_project,
        date_format(a.create_time, '%Y-%m-%d %T') as create_time,
        date_format(a.start_time, '%Y-%m-%d %T') as start_time,
        date_format(a.end_time, '%Y-%m-%d %T') as end_time,
        a.patrol_distance,
        a.patrol_type,
        a.patrol_region,
        a.patrol_location,
		a.photo,
        a.patrol_status,
        a.checked_items,
        a.hidden_danger_desc from jl_grid_patrol_record a
        <where>
            a.del_flag = '0'
            and a.id = #{id}
        </where>
    </select>
    
   <update id="updateHiddenDangerDisposa" parameterType="map" >
	    update jl_grid_patrol_record
	    set hidden_danger_state = '2',
	    	disposal_user_id=#{_user.id},
	    	disposal_user_name=#{_user.name},
	    	disposal_user_departinfo=#{_user.company_name},
	    	disposal_msg=#{disposal_msg},
	    	disposal_time = now()
	    where id = #{id,jdbcType=VARCHAR} and hidden_danger_state='1'
	</update>

	<select id="selectGardenUserByCardNo" parameterType="map" resultType="map" >
		SELECT
			id, appid, docking_id, `name`,
		cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) cardno,
		tel, responsibility, remark, city_id, area_id, street_id, community_id, garden_id, company_id, duty_id
		FROM
		jl_grid_garden_user
		<where>
			cardno = #{_user.cardno,jdbcType=VARCHAR}
		</where>
	</select>
</mapper>
