<?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.mmc.iuav.user.dao.dronepilot.PilotCertificationDao">

    <resultMap id="pilotList" type="com.mmc.iuav.user.entity.dronepilot.PilotCertificationDO">
        <result property="id" column="id"/>
        <result property="licenseType" column="license_type"/>
        <result property="licenseNumber" column="license_number"/>
        <result property="licenseUrl" column="license_url"/>
        <result property="areaNumber" column="area_number"/>
        <result property="yearsOfWorking" column="years_of_working"/>
        <result property="abilityUrl" column="ability_url"/>
        <result property="individualResume" column="individual_resume"/>
        <result property="auditStatus" column="audit_status"/>
        <result property="userAccountId" column="user_account_id"/>
        <result property="timeOfApplication" column="time_of_application"/>
        <result property="remark" column="remark"/>
        <result property="residentCity" column="resident_city"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="userName" column="user_name"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="phoneNum" column="phone_num"/>
        <result property="reasonId" column="reason_id"/>
        <result property="reason" column="reason"/>
        <result property="userImg" column="user_img"/>
        <collection property="pilotAbility" ofType="com.mmc.iuav.user.model.vo.dronepilot.PilotAbilityVO"
                    select="selectAbilityList"
                    column="{pilotCertificationId=id}">
        </collection>

    </resultMap>

    <select id="selectAbilityList" resultType="com.mmc.iuav.user.model.vo.dronepilot.PilotAbilityVO">
        select id,
               ability_name           AS abilityName,
               ability_id             AS abilityId,
               pilot_certification_id AS pilotCertificationId
        from pilot_ability_certification
        where pilot_certification_id = #{pilotCertificationId}
    </select>

    <select id="abilityList" resultType="com.mmc.iuav.user.entity.dronepilot.PilotAbilityDO">
        select id AS abilityId, `name` AS abilityName
        from pilot_ability
    </select>


    <select id="selectRealNameAuth" resultType="java.lang.Integer">
        select count(*)
        from real_name_auth
        where user_account_id = #{userAccountId}
    </select>

    <insert id="insertPilot" useGeneratedKeys="true" keyProperty="id"
            parameterType="com.mmc.iuav.user.entity.dronepilot.PilotCertificationDO">
        insert into pilot_certification(license_type, license_number, license_url, area_number, years_of_working,
                                        ability_url, individual_resume, audit_status, user_account_id,
                                        time_of_application, resident_city, create_time, update_time)
        values (#{licenseType}, #{licenseNumber}, #{licenseUrl}, #{areaNumber}, #{yearsOfWorking},
                #{abilityUrl}, #{individualResume}, #{auditStatus}, #{userAccountId},
                NOW(), #{residentCity}, NOW(), NOW())
    </insert>


    <insert id="insertAbility" parameterType="java.util.List">
        insert into
        pilot_ability_certification(ability_name,ability_id,pilot_certification_id)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.abilityName},#{item.abilityId},#{item.pilotCertificationId})
        </foreach>
    </insert>

    <insert id="insertPilotLog" parameterType="com.mmc.iuav.user.entity.dronepilot.PilotCertificationLogDO">
        insert into pilot_certification_log(certification_date, user_account_id, operator_user_id,
                                            certification_message, create_time,
                                            update_time)
        values (NOW(), #{userAccountId}, #{operatorUserId}, #{certificationMessage}, NOW(), NOW())
    </insert>

    <select id="selectPilotCertification" resultType="java.lang.Integer">
        select count(*)
        from pilot_certification
        where user_account_id = #{userAccountId}
    </select>
    <delete id="deleteAbility">
        delete
        from pilot_ability_certification
        where pilot_certification_id = #{id}
    </delete>

    <update id="updatePilot" parameterType="com.mmc.iuav.user.entity.dronepilot.PilotCertificationDO">
        UPDATE pilot_certification
        <set>
            <if test=" licenseType != null and licenseType != '' ">
                license_type = #{licenseType},
            </if>
            <if test=" licenseNumber != null and licenseNumber != '' ">
                license_number = #{licenseNumber},
            </if>
            <if test=" licenseUrl != null and licenseUrl != '' ">
                license_url = #{licenseUrl},
            </if>
            <if test=" areaNumber != null and areaNumber != '' ">
                area_number = #{areaNumber},
            </if>
            <if test=" yearsOfWorking != null  ">
                years_of_working = #{yearsOfWorking},
            </if>
            <if test=" abilityUrl != null and abilityUrl != '' ">
                ability_url = #{abilityUrl},
            </if>
            <if test=" individualResume != null and individualResume != '' ">
                individual_resume = #{individualResume},
            </if>
            <if test=" remark != null and remark != '' ">
                remark = #{remark},
            </if>
            <if test=" residentCity != null and residentCity != '' ">
                resident_city = #{residentCity},
            </if>
            update_time=NOW(),
            audit_status = 0
        </set>
        where id = #{id}
    </update>

    <select id="selectPilotCount" resultType="java.lang.Integer">
        select count(*)
        from pilot_certification
        where id = #{id}
          and user_account_id = #{userAccountId}
    </select>

    <select id="pilotList" resultMap="pilotList">
        SELECT
        pc.id,
        pc.license_type,
        pc.license_number,
        pc.license_url,
        pc.area_number,
        pc.years_of_working,
        pc.ability_url,
        pc.individual_resume,
        pc.audit_status,
        pc.user_account_id,
        pc.time_of_application,
        pc.remark,
        pc.resident_city,
        pc.create_time,
        pc.update_time,
        rna.user_name,
        rna.birthday,
        rna.sex,
        ua.phone_num,
        pc.reason_id,
        pr.reason,
        ua.user_img
        FROM
        pilot_certification pc
        LEFT JOIN real_name_auth rna ON pc.user_account_id = rna.user_account_id
        LEFT JOIN user_account ua ON pc.user_account_id = ua.id
        LEFT JOIN pilot_reason pr ON pc.reason_id = pr.id
        WHERE
        1 = 1
        AND ua.`disable` = 0
        AND rna.is_deleted =0
        <if test=" areaNumber != null and areaNumber != '' ">
            and pc.area_number =#{areaNumber}
        </if>
        <if test=" id != null  ">
            and pc.id =#{id}
        </if>
        <if test=" licenseType != null and licenseType != '' ">
            and pc.license_type =#{licenseType}
        </if>
        <if test=" auditStatus != null  ">
            and pc.audit_status =#{auditStatus}
        </if>
        <if test="accountNumber != null and accountNumber != '' ">
            and ( pc.user_account_id like concat('%',#{accountNumber},'%')
            or rna.user_name like concat('%',#{accountNumber},'%')
            or ua.phone_num like concat('%',#{accountNumber},'%'))
        </if>
        order by pc.update_time desc, pc.create_time desc
    </select>


    <select id="detailPilot" resultMap="pilotList">
        SELECT pc.id,
               pc.license_type,
               pc.license_number,
               pc.license_url,
               pc.area_number,
               pc.years_of_working,
               pc.ability_url,
               pc.individual_resume,
               pc.audit_status,
               pc.user_account_id,
               pc.time_of_application,
               pc.remark,
               pc.resident_city,
               pc.create_time,
               pc.update_time,
               rna.user_name,
               rna.birthday,
               rna.sex,
               ua.phone_num,
               pc.reason_id,
               pr.reason,
               ua.user_img
        FROM pilot_certification pc
                 LEFT JOIN real_name_auth rna ON pc.user_account_id = rna.user_account_id
                 LEFT JOIN user_account ua ON pc.user_account_id = ua.id
                 LEFT JOIN pilot_reason pr ON pr.id = pc.reason_id
        WHERE pc.id = #{id}
    </select>

    <select id="userDetailPilot" resultMap="pilotList">
        SELECT pc.id,
               pc.license_type,
               pc.license_number,
               pc.license_url,
               pc.area_number,
               pc.years_of_working,
               pc.ability_url,
               pc.individual_resume,
               pc.audit_status,
               pc.user_account_id,
               pc.time_of_application,
               pc.remark,
               pc.resident_city,
               pc.create_time,
               pc.update_time,
               rna.user_name,
               rna.birthday,
               rna.sex,
               ua.phone_num,
               pc.reason_id,
               pr.reason,
               ua.user_img
        FROM pilot_certification pc
                 LEFT JOIN real_name_auth rna ON pc.user_account_id = rna.user_account_id
                 LEFT JOIN user_account ua ON pc.user_account_id = ua.id
                 LEFT JOIN pilot_reason pr ON pc.reason_id = pr.id
        WHERE pc.user_account_id = #{userAccountId}
    </select>


    <select id="interiorDetailPilot" resultMap="pilotList">
        SELECT pc.id,
               pc.license_type,
               pc.license_number,
               pc.license_url,
               pc.area_number,
               pc.years_of_working,
               pc.ability_url,
               pc.individual_resume,
               pc.audit_status,
               pc.user_account_id,
               pc.time_of_application,
               pc.remark,
               pc.resident_city,
               pc.create_time,
               pc.update_time,
               rna.user_name,
               rna.birthday,
               rna.sex,
               ua.phone_num,
               pc.reason_id,
               pr.reason,
               ua.user_img
        FROM pilot_certification pc
                 LEFT JOIN real_name_auth rna ON pc.user_account_id = rna.user_account_id
                 LEFT JOIN user_account ua ON pc.user_account_id = ua.id
                 LEFT JOIN pilot_reason pr ON pr.id = pc.reason_id
        WHERE pc.user_account_id = #{userAccountId}
          and ua.`disable` = 0
          and pc.audit_status = 1
    </select>


    <update id="updateRemark" parameterType="com.mmc.iuav.user.model.qo.dronepilot.PilotRemarkQO">
        UPDATE pilot_certification
        <set>
            remark = #{remark},
            update_time=NOW()
        </set>
        where id = #{id}
    </update>

    <update id="updateAuditStatus" parameterType="com.mmc.iuav.user.model.qo.dronepilot.PilotAuditStatusQO">
        UPDATE pilot_certification
        <set>
            audit_status = #{auditStatus},
            reason_id=#{reasonId},
            operator_user_id=#{operatorUserId},
            update_time=NOW(),
            is_first_approve=#{isFirstApprove}
        </set>
        where id = #{id}
    </update>

    <select id="selectUserAccountId" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        SELECT pc.user_account_id as id,
               ua.union_id,
               ua.open_id,
               bua.user_name      AS operatorUserName,
               rna.user_name
        FROM pilot_certification pc
                 LEFT JOIN real_name_auth rna ON pc.user_account_id = rna.user_account_id
                 LEFT JOIN user_account ua ON ua.id = pc.user_account_id
                 LEFT JOIN back_user_account bua ON bua.id = pc.operator_user_id
        WHERE pc.id = #{id}
    </select>

    <select id="backPilotLogList" resultType="com.mmc.iuav.user.entity.dronepilot.PilotCertificationLogDO">
        SELECT
        pcl.id,
        pcl.certification_date AS certificationDate,
        pcl.user_account_id AS userAccountId,
        pcl.certification_message AS certificationMessage,
        pcl.operator_user_id AS operatorUserId,
        pcl.create_time AS createTime,
        pcl.update_time AS updateTime,
        rna.user_name AS userName,
        ua.phone_num AS phoneNum,
        nua.account_no AS operatorUserAccount,
        nua.user_name AS operatorUserName
        FROM
        pilot_certification_log pcl
        INNER JOIN user_account ua ON pcl.user_account_id = ua.id
        INNER JOIN real_name_auth rna ON rna.user_account_id = ua.id
        LEFT JOIN back_user_account nua ON nua.id = pcl.operator_user_id
        where 1=1
        <if test="accountNumber != null and accountNumber != '' ">
            and ( pcl.user_account_id like concat('%',#{accountNumber},'%')
            or rna.user_name like concat('%',#{accountNumber},'%')
            or ua.phone_num like concat('%',#{accountNumber},'%'))
        </if>
        order by pcl.create_time desc
    </select>

    <select id="backListReason" resultType="com.mmc.iuav.user.entity.dronepilot.PilotReasonDO">
        select id, reason
        from pilot_reason
    </select>

    <select id="selectPilotReason" resultType="com.mmc.iuav.user.entity.dronepilot.PilotReasonDO">
        select id, reason
        from pilot_reason
        where id = #{reasonId}
    </select>
    <select id="selectFirstApprove" resultType="java.lang.Integer">
        select is_first_approve
        from pilot_certification
        where id = #{id}
    </select>
    <select id="getDronePilotCount" resultType="java.lang.Integer">
        SELECT count(*)
        FROM pilot_certification pc
                 LEFT JOIN user_account ua ON pc.user_account_id = ua.id
        WHERE pc.audit_status = 1
          AND ua.`disable` = 0
    </select>
</mapper>
