<?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.djhk.uav.uav.mapper.UavOperatorMapper">

    <resultMap type="com.djhk.uav.airspace.domain.UavOperator" id="UavOperatorResult">
        <result property="id"    column="id"    />
        <result property="userId"    column="user_id"    />
        <result property="userName"    column="user_name"    />
        <result property="policeNo"    column="police_no"    />
        <result property="policeType"    column="police_type"    />
        <result property="tel"    column="tel"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="planTime"    column="plan_time"    />
        <result property="status" column="status"/>
        <result property="email" column="email"/>
        <result property="sex"    column="sex"    />
        <result property="operatorType"    column="operator_type"    />
        <result property="certificateType"    column="certificate_type"    />
        <result property="certificateName"    column="certificate_name"    />
        <result property="certificateNo"    column="certificate_no"    />
        <result property="certificateFileId"    column="certificate_file_id"    />
        <result property="sceneType"    column="scene_type"    />
        <result property="sceneName"    column="scene_name"    />
        <result property="appId"    column="app_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />
        <result property="picUrl"    column="pic_url"    />
        <result property="celebrateDate"    column="celebrate_date"    />
        <result property="jobType"    column="job_type"    />
        <collection property="operatorAbilityList" javaType="list" ofType="com.djhk.uav.uav.model.UavOperatorAbility" select="selectOperatorAbilityList" column="id"></collection>
    </resultMap>

    <select id="selectOperatorAbilityList" resultType="com.djhk.uav.uav.model.UavOperatorAbility">
        select * from uav_operator_ability where del_flag = "0" and uav_operator_id=#{id};
    </select>

    <sql id="selectUavOperatorVo">
        select id, pic_url,  celebrate_date, user_id, user_name, police_no, police_type, tel,status, email, del_flag,sex, operator_type, certificate_type,
        certificate_name, certificate_no, certificate_file_id, scene_type, scene_name , create_by,
        create_time, update_by, update_time, remark, plan_time, app_id, dept_id
        from uav_operator
    </sql>

<!--    <select id="selectUavOperatorList" parameterType="com.djhk.uav.airspace.domain.UavOperator" resultMap="UavOperatorResult">-->
<!--        select o.id, user_id, user_name, police_no, police_type,tel,o.status, email,sex, operator_type, certificate_type,-->
<!--        certificate_name, certificate_no, certificate_file_id, scene_type, scene_name , o.create_by,-->
<!--        o.create_time, o.update_by, o.update_time, o.remark, plan_time, o.app_id, o.dept_id-->
<!--        from uav_operator o-->
<!--        <where>-->
<!--            and o.del_flag = "0"-->
<!--            <if test="userId != null "> and user_id = #{userId}</if>-->
<!--            <if test="status != null "> and o.status = #{status}</if>-->
<!--            <if test="delFlag != null "> and del_Flag = #{delFlag}</if>-->
<!--            <if test="userName != null  and userName != ''"> and user_Name like concat('%', #{userName}, '%')</if>-->
<!--            <if test="remark != null  and remark != ''"> and remark = #{remark}</if>-->
<!--            <if test="planTime != null "> and plan_time = #{planTime}</if>-->
<!--            <if test="sex != null  and sex != ''"> and sex = #{sex}</if>-->
<!--            <if test="operatorType != null  and operatorType != ''"> and operator_type = #{operatorType}</if>-->
<!--            <if test="certificateType != null  and certificateType != ''"> and certificate_type = #{certificateType}</if>-->
<!--            <if test="certificateName != null  and certificateName != ''"> and certificate_name like concat('%', #{certificateName}, '%')</if>-->
<!--            <if test="certificateNo != null  and certificateNo != ''"> and certificate_no = #{certificateNo}</if>-->
<!--            <if test="certificateFileId != null "> and certificate_file_id = #{certificateFileId}</if>-->
<!--            <if test="sceneType != null  and sceneType != ''"> and scene_type = #{sceneType}</if>-->
<!--            <if test="appId != null  and appId != ''"> and o.app_id = #{appId}</if>-->
<!--            <if test="deptId != null and deptId != '' ">-->
<!--                AND (o.dept_id = #{deptId} OR o.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE-->
<!--                find_in_set(#{deptId}, ancestors) ))-->
<!--            </if>-->
<!--        </where>-->
<!--        order by o.create_Time desc-->
<!--    </select>-->
    <select id="selectUavOperatorList" parameterType="com.djhk.uav.airspace.domain.UavOperator" resultMap="UavOperatorResult">
        select o.id, o.pic_url, o.job_type, o.celebrate_date, o.user_id, o.user_name, o.police_no, o.police_type,o.tel,o.status, o.email,o.sex, o.operator_type, o.certificate_type,
        o.certificate_name, o.certificate_no, o.certificate_file_id, o.scene_type, o.scene_name , o.create_by,
        o.create_time, o.update_by, o.update_time, o.remark, plan_time, o.app_id, o.dept_id,t.dept_name
        from uav_operator o LEFT JOIN sys_dept t ON o.dept_id=t.dept_id
        <where>
            and o.del_flag = "0"
            <if test="userId != null "> and user_id = #{userId}</if>
            <if test="status != null "> and o.status = #{status}</if>
            <if test="delFlag != null "> and del_Flag = #{delFlag}</if>
            <if test="userName != null  and userName != ''"> and user_Name like concat('%', #{userName}, '%')</if>
            <if test="remark != null  and remark != ''"> and remark = #{remark}</if>
            <if test="planTime != null "> and plan_time = #{planTime}</if>
            <if test="sex != null  and sex != ''"> and sex = #{sex}</if>
            <if test="operatorType != null  and operatorType != ''"> and operator_type = #{operatorType}</if>
            <if test="certificateType != null  and certificateType != ''"> and certificate_type = #{certificateType}</if>
            <if test="certificateName != null  and certificateName != ''"> and certificate_name like concat('%', #{certificateName}, '%')</if>
            <if test="certificateNo != null  and certificateNo != ''"> and certificate_no = #{certificateNo}</if>
            <if test="certificateFileId != null "> and certificate_file_id = #{certificateFileId}</if>
            <if test="sceneType != null  and sceneType != ''"> and scene_type = #{sceneType}</if>
            <if test="appId != null  and appId != ''"> and o.app_id = #{appId}</if>
            <if test="celebrateDateRage != null  and celebrateDateRage != ''">
                AND date_format(o.celebrate_date,'%y%m%d') &gt;= date_format(#{celebrateDateRage[0]},'%y%m%d')
            </if>
            <if test="celebrateDateRage != null  and celebrateDateRage != ''"><!-- 结束时间检索 -->
                AND date_format(o.celebrate_date,'%y%m%d') &lt;= date_format(#{ celebrateDateRage[1]},'%y%m%d')
            </if>
            <if test="appId != null  and appId != ''"> and o.app_id = #{appId}</if>
            <if test="deptId != null and deptId != '' ">
                AND (o.dept_id = #{deptId} OR o.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE
                find_in_set(#{deptId}, ancestors) ))
            </if>
        </where>
        order by o.create_Time desc
    </select>

    <select id="selectLists" parameterType="com.djhk.uav.airspace.domain.UavOperator" resultMap="UavOperatorResult">
        select o.id, o.pic_url, o.job_type, o.celebrate_date, o.user_id, o.user_name, o.police_no, o.police_type,o.tel,o.status, o.email,o.sex, o.operator_type, o.certificate_type,
        o.certificate_name, o.certificate_no, o.certificate_file_id, o.scene_type, o.scene_name , o.create_by,
        o.create_time, o.update_by, o.update_time, o.remark, plan_time, o.app_id, o.dept_id
        from uav_operator o
        <where>
            and o.del_flag = "0"
            <if test="userId != null "> and user_id = #{userId}</if>
            <if test="policeNo != null "> and o.police_no = #{policeNo}</if>
        </where>
        order by o.create_Time desc
    </select>

    <select id="selectUavOperatorById" parameterType="Long" resultMap="UavOperatorResult">
        select o.id, o.pic_url, o.job_type,o.celebrate_date,o.user_id, o.user_name, o.police_no, o.police_type,o.tel,o.status, o.email,o.sex, o.operator_type, o.certificate_type,
        o.certificate_name, o.certificate_no, o.certificate_file_id, o.scene_type, o.scene_name , o.create_by,
        o.create_time, o.update_by, o.update_time, o.remark, plan_time, o.app_id, o.dept_id,t.dept_name
        from uav_operator o LEFT JOIN sys_dept t ON o.dept_id=t.dept_id
        where user_id = #{id}
    </select>

    <select id="selectOperatorCount"  resultType="int">
        select count(1) from uav_operator where del_flag = "0"
    </select>
    <select id="selectUavOperatorByUserId" resultType="com.djhk.uav.airspace.domain.UavOperator">
        <include refid="selectUavOperatorVo"/>
        where user_id = #{userId}
        and del_flag = '0'
    </select>
    <select id="selectUavOperatorExportList" resultType="com.djhk.uav.airspace.domain.UavOperator">
        select o.id,  o.job_type, o.celebrate_date, user_id, user_name,tel,o.status, email,sex, operator_type, certificate_type,
        certificate_name, certificate_no, certificate_file_id, scene_type, scene_name , o.create_by,
        o.create_time, o.update_by, o.update_time, o.remark, plan_time, o.app_id, o.dept_id
        from uav_operator o
        <where>
            and o.del_flag = "0"
            <if test="ids != null and ids.size() > 0">
            and id in
            <foreach item="id" collection="ids" open="(" separator="," close=")">
                #{id}
            </foreach>
            </if>
            <if test="planTime != null and planTime != '' ">
                AND o.plan_time = #{planTime}
            </if>
            <if test="userName != null and userName != '' ">
                AND o.user_name like concat('%',#{userName},'%')
            </if>
            <if test="deptIds != null and deptIds.size() > 0">
                and dept_id in
                <foreach collection="deptIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        order by o.create_Time desc
    </select>

    <!--    useGeneratedKeys="true" keyProperty="id"-->
    <insert id="insertUavOperator" parameterType="com.djhk.uav.airspace.domain.UavOperator" >
        <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="id">
            select LAST_INSERT_ID()
        </selectKey>
        insert into uav_operator
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="userName != null">user_Name,</if>
            <if test="policeNo != null">police_no,</if>
            <if test="policeType != null">police_type,</if>
            <if test="tel != null">tel,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
            <if test="planTime != null">plan_time,</if>
            <if test="status != null">status,</if>
            <if test="email != null">email,</if>
            <if test="sex != null">sex,</if>
            <if test="operatorType != null">operator_type,</if>
            <if test="certificateType != null">certificate_type,</if>
            <if test="certificateName != null">certificate_name,</if>
            <if test="certificateNo != null">certificate_no,</if>
            <if test="certificateFileId != null">certificate_file_id,</if>
            <if test="sceneType != null">scene_type,</if>
            <if test="sceneName != null">scene_name,</if>
            <if test="appId != null and appId != ''">app_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="picUrl != null">pic_url,</if>
            <if test="celebrateDate != null">celebrate_date,</if>
            <if test="jobType != null">job_type,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="userName != null">#{userName},</if>
            <if test="policeNo != null">#{policeNo},</if>
            <if test="policeType != null">#{policeType},</if>
            <if test="tel != null">#{tel},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="planTime != null">#{planTime},</if>
            <if test="status != null">#{status},</if>
            <if test="email != null">#{email},</if>
            <if test="sex != null">#{sex},</if>
            <if test="operatorType != null">#{operatorType},</if>
            <if test="certificateType != null">#{certificateType},</if>
            <if test="certificateName != null">#{certificateName},</if>
            <if test="certificateNo != null">#{certificateNo},</if>
            <if test="certificateFileId != null">#{certificateFileId},</if>
            <if test="sceneType != null">#{sceneType},</if>
            <if test="sceneName != null">#{sceneName},</if>
            <if test="appId != null and appId != ''">#{appId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="picUrl != null">#{picUrl},</if>
            <if test="celebrateDate != null">#{celebrateDate},</if>
            <if test="jobType != null">#{jobType},</if>
         </trim>
    </insert>

    <update id="updateUavOperator" parameterType="com.djhk.uav.airspace.domain.UavOperator">
        update uav_operator
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="userName != null">user_Name = #{userName},</if>
            <if test="policeNo != null">police_no = #{policeNo},</if>
            <if test="policeType != null">police_type = #{policeType},</if>
            <if test="tel != null">tel = #{tel},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="planTime != null">plan_time = #{planTime},</if>
            <if test="status != null">status = #{status},</if>
            <if test="email != null">email = #{email},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="operatorType != null">operator_type = #{operatorType},</if>
            <if test="certificateType != null">certificate_type = #{certificateType},</if>
            <if test="certificateName != null">certificate_name = #{certificateName},</if>
            <if test="certificateNo != null">certificate_no = #{certificateNo},</if>
            <if test="certificateFileId != null">certificate_file_id = #{certificateFileId},</if>
            <if test="sceneType != null">scene_type = #{sceneType},</if>
            <if test="sceneName != null">scene_name = #{sceneName},</if>
            <if test="appId != null and appId != ''">app_id = #{appId},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="picUrl != null">pic_url = #{picUrl},</if>
            <if test="celebrateDate != null">celebrate_date = #{celebrateDate},</if>
            <if test="jobType != null">job_type = #{jobType},</if>
        </trim>
        where user_id = #{userId}
    </update>

    <delete id="deleteUavOperatorById" parameterType="Long">
        delete from uav_operator where user_id = #{id}
    </delete>

    <delete id="deleteUavOperatorByIds" parameterType="String">
        delete from uav_operator where user_id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>
