<?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.jlgridgarden" name="网格管理员">
    <resultMap id="BaseResultMap" type="com.scpyun.platform.jilinsscgsdp.bean.entity.JlGridGardenUser">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Mon Sep 02 14:32:15 CST 2024.
        -->
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="cardno" jdbcType="VARCHAR" property="cardno"/>
        <result column="tel" jdbcType="INTEGER" property="tel"/>
        <result column="responsibility" jdbcType="VARCHAR" property="responsibility"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="city_id" jdbcType="VARCHAR" property="cityId"/>
        <result column="area_id" jdbcType="VARCHAR" property="areaId"/>
        <result column="street_id" jdbcType="VARCHAR" property="streetId"/>
        <result column="community_id" jdbcType="VARCHAR" property="communityId"/>
        <result column="garden_id" jdbcType="VARCHAR" property="gardenId"/>
        <result column="company_id" jdbcType="VARCHAR" property="companyId"/>
        <result column="duty_id" jdbcType="VARCHAR" property="dutyId"/>
        <result column="del_flag" jdbcType="CHAR" property="delFlag"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>
    <sql id="Base_Column_List">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Mon Sep 02 14:32:15 CST 2024.
        -->
        id, name, cardno, tel, responsibility, remark, city_id, area_id, street_id, community_id,
        garden_id, company_id, duty_id, del_flag, create_by, create_time, update_by, update_time
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Mon Sep 02 14:32:15 CST 2024.
        -->
        select
        <include refid="Base_Column_List"/>
        from jl_grid_garden_user
        where id = #{id,jdbcType=VARCHAR}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.String">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Mon Sep 02 14:32:15 CST 2024.
        -->
        delete from jl_grid_garden_user
        where id = #{id,jdbcType=VARCHAR}
    </delete>

    <insert id="insertSelective" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlGridGardenUser">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Mon Sep 02 14:32:15 CST 2024.
        -->
        insert into jl_grid_garden_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="cardno != null">
                cardno,
            </if>
            <if test="tel != null">
                tel,
            </if>
            <if test="responsibility != null">
                responsibility,
            </if>
            <if test="remark != null">
                remark,
            </if>
            <if test="cityId != null">
                city_id,
            </if>
            <if test="areaId != null">
                area_id,
            </if>
            <if test="streetId != null">
                street_id,
            </if>
            <if test="communityId != null">
                community_id,
            </if>
            <if test="gardenId != null">
                garden_id,
            </if>
            <if test="companyId != null">
                company_id,
            </if>
            <if test="dutyId != null">
                duty_id,
            </if>
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=VARCHAR},
            </if>
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="cardno != null">
                TO_BASE64(AES_ENCRYPT(#{cardno,jdbcType=VARCHAR},'*#id#')),
            </if>
            <if test="tel != null">
                #{tel},
            </if>
            <if test="responsibility != null">
                #{responsibility,jdbcType=VARCHAR},
            </if>
            <if test="remark != null">
                #{remark,jdbcType=VARCHAR},
            </if>
            <if test="cityId != null">
                #{cityId,jdbcType=VARCHAR},
            </if>
            <if test="areaId != null">
                #{areaId,jdbcType=VARCHAR},
            </if>
            <if test="streetId != null">
                #{streetId,jdbcType=VARCHAR},
            </if>
            <if test="communityId != null">
                #{communityId,jdbcType=VARCHAR},
            </if>
            <if test="gardenId != null">
                #{gardenId,jdbcType=VARCHAR},
            </if>
            <if test="companyId != null">
                #{companyId,jdbcType=VARCHAR},
            </if>
            <if test="dutyId != null">
                #{dutyId,jdbcType=VARCHAR},
            </if>
            '0',
            #{_user.id},
            now(),
            #{_user.id},
            now()
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlGridGardenUser">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Mon Sep 02 14:32:15 CST 2024.
        -->
        update jl_grid_garden_user
        <set>
            <if test="name != null">
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="cardno != null">
                cardno = TO_BASE64(AES_ENCRYPT(#{cardno,jdbcType=VARCHAR},'*#id#')),
            </if>
            <if test="tel != null">
                tel = #{tel,jdbcType=INTEGER},
            </if>
            <if test="responsibility != null">
                responsibility = #{responsibility,jdbcType=VARCHAR},
            </if>
            <if test="remark != null">
                remark = #{remark,jdbcType=VARCHAR},
            </if>
            <if test="cityId != null">
                city_id = #{cityId,jdbcType=VARCHAR},
            </if>
            <if test="areaId != null">
                area_id = #{areaId,jdbcType=VARCHAR},
            </if>
            <if test="streetId != null">
                street_id = #{streetId,jdbcType=VARCHAR},
            </if>
            <if test="communityId != null">
                community_id = #{communityId,jdbcType=VARCHAR},
            </if>
            <if test="gardenId != null">
                garden_id = #{gardenId,jdbcType=VARCHAR},
            </if>
            <if test="companyId != null">
                company_id = #{companyId,jdbcType=VARCHAR},
            </if>
            <if test="dutyId != null">
                duty_id = #{dutyId,jdbcType=VARCHAR},
            </if>
            <if test="delFlag != null">
                del_flag = #{delFlag,jdbcType=CHAR},
            </if>
            <if test="createBy != null">
                create_by = #{createBy,jdbcType=VARCHAR},
            </if>
            <if test="createTime != null">
                create_time = #{createTime,jdbcType=TIMESTAMP},
            </if>
            update_by = #{_user.id},
            update_time = now()
        </set>
        where id = #{id,jdbcType=VARCHAR}
    </update>

    <select id="findJlGridGardenUserList" parameterType="page" resultType="map">
        select
        a.id, 
        a.name, 
        cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) cardno, 
        tel, 
        responsibility, 
        remark,
        a.city_id cityId,
        a.area_id areaId,
        a.street_id streetId,
        a.community_id communityId,
        a.garden_id gardenId,
        a.company_id companyId,
        a.duty_id dutyId,
        b.label companyName,
        c.gardenName,
        duty,
        a.remark
        from jl_grid_garden_user a 
        left join (select a.label,a.value from sys_dict a where a.type='sys_type_office' ) b on a.company_id=b.value
        left join (select id gardenId,name gardenName from jl_grid_garden ) c on a.garden_id=c.gardenId
        left join jl_grid_garden_user_duty duty on a.duty_id=duty.id
        <where>
            a.del_flag = '0'
            <if test="params.city_id != null and params.city_id !='' ">
            	and a.city_id=#{params.city_id,jdbcType=VARCHAR}
            </if>
            <if test="params.area_id != null and params.area_id !='' ">
            	and a.area_id=#{params.area_id,jdbcType=VARCHAR}
            </if>
            <if test="params.street_id != null and params.street_id !='' ">
            	and a.street_id=#{params.street_id,jdbcType=VARCHAR}
            </if>
            <if test="params.community_id != null and params.community_id !='' ">
            	and a.community_id=#{params.community_id,jdbcType=VARCHAR}
            </if>
            <if test="params.garden_id != null and params.garden_id !='' ">
            	and a.garden_id=#{params.garden_id,jdbcType=VARCHAR}
            </if>
            <choose>
            	<when test="params._user!=null and params._user.company_grade == '3'.toString()">
            		and a.city_id = #{params._user.company_id}
            	</when>
            	<when test="params._user!=null and params._user.company_grade == '4'.toString()">
            		and a.area_id = #{params._user.company_id}
            	</when>
            	<when test="params._user!=null and params._user.company_grade == '5'.toString()">
            		and a.street_id = #{params._user.company_id}
            	</when>
            	<when test="params._user!=null and params._user.company_grade == '6'.toString()">
            		and a.community_id = #{params._user.company_id}
            	</when>
            </choose>
        </where>
        order by a.name asc
    </select>
    
    <select id="queryCommunityGardenUserList" expose="true" parameterType="map" resultType="map" name="查询社区下的网格员">
    	select cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) cardno,name,phone from sys_user 
    	<where>
    		<choose>
    			<when test="community_id != null and community_id !='' ">
    				and company_id=#{community_id,jdbcType=VARCHAR}
    			</when>
    			<otherwise>
    				and company_id=#{_user.company_id}
    			</otherwise>
    		</choose>
    		and job_duty='1' and del_flag='0' 
    	</where>
    	order by name  
    </select>
    
    <select id="queryCityDutyList" expose="true" parameterType="map" resultType="map" name="查询定责信息">
    	select dict.label company_name,dict.value company_id,duty,duty.id duty_id from jl_grid_garden_user_duty duty inner join 
    	(select label,value,sort from sys_dict where type='sys_type_office') dict on duty.company_id=dict.value
    	<where>
    		<choose>
    			<when test="_user.company_grade!=null and (_user.company_grade == '4'.toString() or 
    			_user.company_grade == '5'.toString() or _user.company_grade == '6'.toString())">
    				and city_id = SUBSTRING_INDEX(SUBSTRING_INDEX(#{_user.parent_ids}, ',', 3), ',', -1)
    			</when>
    			<when test="_user.company_grade!=null and _user.company_grade == '3'.toString()">
    				and city_id = #{_user.company_id}
    			</when>
	            <otherwise>
	            	and city_id = concat(SUBSTR(#{community_id,jdbcType=VARCHAR},1,4),'00000000') 
	            </otherwise>
    		</choose>
    	</where>
    	order by dict.sort
    </select>
    
    <select id="findTimeList" parameterType="page" resultType="map">
        select
            a.id,
            a.city_id,
            a.area_id,
            a.street_id,
            a.community_id,
            city.name city_name,
            area.name area_name,
            street.name street_name,
            community.name community_name,
            a.name garden_name,
            a.count,
            a.time
        from
            jl_grid_garden a 
            inner join sys_office city on a.city_id=city.id
            inner join sys_office area on a.area_id=area.id
            inner join sys_office street on a.street_id=street.id
            inner join sys_office community on a.community_id=community.id
           <where>
           		a.del_flag = '0'
           		<if test="params.city_id != null and params.city_id !='' ">
            		and a.city_id=#{params.city_id,jdbcType=VARCHAR}
	            </if>
	            <if test="params.area_id != null and params.area_id !='' ">
	            	and a.area_id=#{params.area_id,jdbcType=VARCHAR}
	            </if>
	            <if test="params.street_id != null and params.street_id !='' ">
	            	and a.street_id=#{params.street_id,jdbcType=VARCHAR}
	            </if>
	            <if test="params.community_id != null and params.community_id !='' ">
	            	and a.community_id=#{params.community_id,jdbcType=VARCHAR}
	            </if>
	            <choose>
	            	<when test="params._user!=null and params._user.company_grade == '3'.toString()">
	            		and a.city_id = #{params._user.company_id}
	            	</when>
	            	<when test="params._user!=null and params._user.company_grade == '4'.toString()">
	            		and a.area_id = #{params._user.company_id}
	            	</when>
	            	<when test="params._user!=null and params._user.company_grade == '5'.toString()">
	            		and a.street_id = #{params._user.company_id}
	            	</when>
	            	<when test="params._user!=null and params._user.company_grade == '6'.toString()">
	            		and a.community_id = #{params._user.company_id}
	            	</when>
	            </choose>
           </where> 
        order by a.city_id,a.area_id,a.street_id,a.community_id
    </select>
    
    <update id="updateGardenTimeConfig" parameterType="map">
        update jl_grid_garden set  count = #{count},time = #{time} where id = #{id}
    </update>
    
   <select id="getPatrolTimeNum" parameterType="map" resultType="int" name="查询网格是否有未完成的巡查">
		select
		count(1) num
		from jl_patrol_time pt
		LEFT JOIN jl_grid_patrol_record pr
		ON pt.garden_id = pr.garden_id
		AND pt.id = pr.patrol_time_id
		where pt.garden_id = #{garden_id}
		and pr.start_time is not null
		and pr.end_time is null
	</select>
	
	<delete id="deletePatrolTime" parameterType="map">
	    delete from jl_patrol_time where garden_id = #{garden_id}
	</delete>
	
	<insert id="insertPatrolTime" parameterType="map">
	    insert into jl_patrol_time(
	        id,
	        statr_time,
	        end_time,
	        status,
	        garden_id,
	        name,
	        duration,
	        road_length,
	        patrol_project,
	        patrol_status
	    )values(
	        #{id},
	        #{statr_time},
	        #{end_time},
	        '0',
	        #{garden_id},
	        #{name},
	        #{duration},
	        #{road_length},
	        #{patrol_project},
	        #{patrol_status}
	    )
	</insert>
</mapper>