<?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.UserServiceDao">
    <resultMap id="userAccountResultMap" type="com.mmc.iuav.user.entity.UserAccountDO">
        <id property="id" column="id"/>
        <result property="phoneNum" column="phone_num"/>
        <result property="userName" column="user_name"/>
        <result property="nickName" column="nick_name"/>
        <result property="userImg" column="user_img"/>
        <result property="openId" column="open_id"/>
        <result property="unionId" column="union_id"/>
        <result property="userSex" column="user_sex"/>
        <result property="email" column="email"/>
        <result property="source" column="source"/>
        <result property="accountStatus" column="account_status"/>
        <result property="remark" column="remark"/>
        <result property="portType" column="port_type"/>
        <result property="disable" column="disable"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>

        <result property="companyAuthStatus" column="companyAuthStatus"/>
        <result property="companyName" column="company_name"/>
        <result property="realNameAuthStatus" column="realNameAuthStatus"/>
        <association property="userRcdDO" javaType="com.mmc.iuav.user.entity.UserRcdDO">
            <id property="id" column="user_rcd_id"/>
            <result property="rcdUserId" column="rcd_user_id"/>
            <result property="rcdNickname" column="rcdNickname"/>
            <result property="rcdUserName" column="rcdUsername"/>
        </association>

    </resultMap>

    <insert id="insertUserAccount" parameterType="com.mmc.iuav.user.entity.UserAccountDO"
            useGeneratedKeys="true" keyProperty="id">
        insert into user_account(phone_num, user_name, nick_name, user_img, open_id,
                                 union_id, user_sex, email, source, province_code, city_code, district_code, remark,
                                 port_type, create_time)
        values (#{phoneNum}, #{userName}, #{nickName}, #{userImg}, #{openId},
                #{unionId}, #{userSex}, #{email}, #{source}, #{provinceCode}, #{cityCode}, #{districtCode}, #{remark},
                #{portType}, NOW())
    </insert>

    <insert id="insertUserRcd" parameterType="com.mmc.iuav.user.entity.UserRcdDO">
        insert into user_rcd (user_account_id, rcd_user_id, create_time)
        values (#{userAccountId}, #{rcdUserId}, NOW());
    </insert>
    <insert id="insertActiveUserCount">
        insert into active_user_count
            (active_user_count, active_date, create_time)
        values (#{activeUserCount}, #{activeDate}, NOW())
    </insert>

    <update id="update" parameterType="com.mmc.iuav.user.entity.UserAccountDO">
        update user_account
        <set>
            <if test="phoneNum != null">
                phone_num = #{phoneNum},
            </if>
            <if test="userName != null">
                user_name = #{userName},
            </if>
            <if test="nickName != null">
                nick_name = #{nickName},
            </if>
            <if test="userImg != null">
                user_img = #{userImg},
            </if>
            <if test="userSex != null">
                user_sex = #{userSex},
            </if>
            <if test="email != null">
                email = #{email},
            </if>
            <if test="accountStatus != null">
                account_status = #{accountStatus},
            </if>
            <if test="provinceCode != null">
                province_code = #{provinceCode},
            </if>
            <if test="cityCode != null">
                city_code = #{cityCode},
            </if>
            <if test="districtCode != null">
                district_code = #{districtCode},
            </if>
            <if test="remark != null">
                remark = #{remark},
            </if>
            <if test="portType != null">
                port_type = #{portType},
            </if>
            <if test="coverPicture!='' and coverPicture!=null">
                cover_picture=#{coverPicture},
            </if>
            <if test="districtChildId!=null">
                district_child_id=#{districtChildId},
            </if>
            <if test="briefIntroduction!='' and briefIntroduction!=null">
                brief_introduction=#{briefIntroduction}
            </if>
        </set>
        <where>
            id = #{id}
        </where>
    </update>

    <update id="disableUserAccount">
        update user_account
        set disable  = 1,
            union_id = #{removeNO}
        where id = #{userAccountId}
    </update>

    <select id="getUserAccountInfoByUnionId" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        select id,
               phone_num,
               user_name,
               nick_name,
               user_img,
               open_id,
               union_id,
               user_sex,
               email,
               source,
               account_status,
               remark,
               port_type,
               disable,
               create_time,
               update_time
        from user_account
        where union_id = #{unionId}
          and disable = 0
    </select>

    <select id="getUserAccountById" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        SELECT ua.id,
               ua.phone_num,
               ua.user_name,
               ua.nick_name,
               ua.user_img,
               ua.open_id,
               ua.union_id,
               ua.user_sex,
               ua.email,
               ua.`source`,
               ua.account_status,
               ua.remark,
               ua.port_type,
               ua.`DISABLE`,
               ua.create_time,
               ua.update_time,
               pc.audit_status,
               up.total_points,
               ua.brief_introduction,
               ua.cover_picture,
               ua.district_child_id
        FROM user_account ua
                 LEFT JOIN pilot_certification pc ON pc.user_account_id = ua.id
                 LEFT JOIN user_points up ON up.user_account_id = ua.id
        WHERE ua.id = #{id}
    </select>

    <select id="listUserAccountIdsByDistrictCode" resultType="java.lang.Integer">
        select id from user_account
        where disable = 0 and port_type = 0
        <if test="provinceCode != null">
            and province_code = #{provinceCode}
        </if>
        <if test="cityCode != null">
            and city_code = #{cityCode}
        </if>
        <if test="districtCode != null">
            and district_code = #{districtCode}
        </if>
    </select>

    <select id="feignListUserAccount" resultType="com.mmc.iuav.user.entity.UserAccountDO"
            parameterType="com.mmc.iuav.user.model.qo.UserAccountQO">
        select ua.id, ua.phone_num, rna.user_name, ua.nick_name, ua.user_img, ua.open_id,
        ua.union_id, ua.user_sex, ua.email, ua.source, ua.account_status, ua.remark,
        ua.port_type, ua.disable, ua.create_time, ua.update_time, ca.auth_status as companyAuthStatus,
        ua.latitude,
        ua.longitude,
        ua.region,
        ua.brief_introduction,
        ua.cover_picture
        from user_account ua left join company_auth ca on ua.id = ca.user_account_id left join real_name_auth rna on
        ua.id = rna.user_account_id
        where ua.disable = 0
        <if test=" userIds != null ">
            <foreach collection="userIds" item="id" open="and ua.id in (" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        <if test="keyword != null">
            and (
            user_name like CONCAT("%",#{keyword},"%") or
            nick_name like CONCAT("%",#{keyword},"%") or
            phone_num like CONCAT("%",#{keyword},"%") or
            id like CONCAT("%",#{keyword},"%"))
        </if>
        <if test="provinceCode != null">
            and ua.province_code = #{provinceCode}
        </if>
        <if test="cityCode != null">
            and ua.city_code = #{cityCode}
        </if>
        <if test="districtCode != null">
            and ua.district_code = #{districtCode}
        </if>
    </select>

    <select id="listUserAccountByIds" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        select ua.id, ua.phone_num, ua.user_name, ua.nick_name, ua.user_img, ua.open_id,
        ua.union_id, ua.user_sex, ua.email, ua.source, ua.account_status, ua.remark,
        ua.port_type, ua.disable, ua.create_time, ua.update_time, ca.auth_status as companyAuthStatus,
        ca.company_name
        from user_account ua left join company_auth ca on ua.id = ca.user_account_id
        where ua.disable = 0
        <if test=" userIds != null ">
            <foreach collection="userIds" item="id" open="and ua.id in (" close=")" separator=",">
                #{id}
            </foreach>
        </if>
    </select>

    <select id="feignListRcdUserInfo" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        select ua.id, ua.phone_num, ua.user_name, ua.nick_name, ua.user_img, ua.open_id,
        ua.union_id, ua.user_sex, ua.email, ua.source, ua.account_status, ua.remark,
        ua.port_type, ua.disable, ua.create_time, ua.update_time, ca.auth_status as companyAuthStatus,
        ca.company_name
        from user_rcd ur inner join user_account ua on ua.id = ur.rcd_user_id left join company_auth ca on ua.id =
        ca.user_account_id
        where ua.disable = 0
        <if test=" userIds != null ">
            <foreach collection="userIds" item="id" open="and ur.user_account_id in (" close=")" separator=",">
                #{id}
            </foreach>
        </if>
    </select>

    <select id="getUserRcdByAccountId" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        select ua.id,
               ua.phone_num,
               ua.user_name,
               ua.nick_name,
               ua.user_img,
               ua.open_id,
               ua.union_id,
               ua.user_sex,
               ua.email,
               ua.source,
               ua.account_status,
               ua.remark,
               ua.port_type,
               ua.disable,
               ua.create_time,
               ua.update_time,
               ca.auth_status as companyAuthStatus,
               ca.company_name
        from user_rcd ur
                 inner join user_account ua on ua.id = ur.rcd_user_id
                 left join company_auth ca on ua.id = ca.user_account_id
        where ua.disable = 0
          and ur.user_account_id = #{userAccountId}
    </select>

    <select id="getUserRcdDO" resultType="com.mmc.iuav.user.entity.UserRcdDO">
        select id, user_account_id, rcd_user_id
        from user_rcd
        where user_account_id = #{userAccountId}
    </select>

    <select id="countListAppUser" resultType="int" parameterType="com.mmc.iuav.user.model.qo.UserAccountQO">
        SELECT
        count(*)
        FROM
        user_account ua
        LEFT JOIN company_member cm ON cm.user_account_id = ua.id
        LEFT JOIN company_info ci ON ci.id = cm.company_info_id
        LEFT JOIN real_name_auth rna ON rna.user_account_id = ua.id
        WHERE
        ua.disable = 0 and ua.port_type = 100
        <if test="companyInfoId != null">
            and ci.id = #{companyInfoId}
        </if>
        <if test="phoneNum != null and phoneNum != '' ">
            and ( ua.phone_num like CONCAT("%",#{phoneNum},"%") )
        </if>
        <if test="companyAuthStatus == 0">
            AND ci.company_name is null
        </if>
        <if test="companyAuthStatus == 1">
            AND ci.company_name is not null
        </if>
        <if test="realAuthStatus == 1">
            AND rna.check_status = #{realAuthStatus}
        </if>
        <if test="realAuthStatus == 0">
            AND ( rna.check_status is null or rna.check_status = 0 )
        </if>
        <if test="startTime != null ">
            AND ua.create_time >= #{startTime}
        </if>
        <if test="endTime != null ">
            and #{endTime} >= ua.create_time
        </if>
        <if test="source != null ">
            and ua.source = #{source}
        </if>
        <if test="keyword != null and keyword != '' ">
            and (
            ua.user_name like CONCAT("%",#{keyword},"%")
            or ua.nick_name like CONCAT("%",#{keyword},"%")
            or ua.id like CONCAT("%",#{keyword},"%")
            )
        </if>
    </select>

    <select id="listAppUser" resultMap="userAccountResultMap"
            parameterType="com.mmc.iuav.user.model.qo.UserAccountQO">
        SELECT
        ua.id,
        ua.phone_num,
        ua.nick_name,
        ua.user_img,
        ua.open_id,
        ua.union_id,
        ua.user_sex,
        ua.email,
        ua.source,
        ua.account_status,
        ua.remark,
        ua.port_type,
        ua.`disable`,
        ua.create_time,
        ua.update_time,
        ci.company_name,
        rcd.id AS user_rcd_id,
        rcd.rcd_user_id,
        r_user.nick_name AS rcdNickname,
        r_user.user_name AS rcdUserName,
        rna.user_name,
        rna.check_status as realNameAuthStatus
        FROM
        user_account ua
        LEFT JOIN company_member cm ON cm.user_account_id = ua.id
        LEFT JOIN company_info ci ON ci.id = cm.company_info_id
        LEFT JOIN user_rcd rcd ON rcd.user_account_id = ua.id
        LEFT JOIN user_account r_user ON r_user.id = rcd.rcd_user_id
        LEFT JOIN real_name_auth rna ON rna.user_account_id = ua.id
        WHERE
        ua.`disable` = 0
        AND ua.port_type = 100
        <if test="companyInfoId != null">
            and ci.id = #{companyInfoId}
        </if>
        <if test="phoneNum != null and phoneNum != '' ">
            and ( ua.phone_num like CONCAT("%",#{phoneNum},"%") )
        </if>
        <if test="companyAuthStatus == 0">
            AND ci.company_name is null
        </if>
        <if test="companyAuthStatus == 1">
            AND ci.company_name is not null
        </if>
        <if test="realAuthStatus == 1">
            AND rna.check_status = #{realAuthStatus}
        </if>
        <if test="realAuthStatus == 0">
            AND ( rna.check_status is null or rna.check_status = 0 )
        </if>
        <if test="startTime != null ">
            AND ua.create_time >= #{startTime}
        </if>
        <if test="endTime != null ">
            and #{endTime} >= ua.create_time
        </if>
        <if test="source != null ">
            and ua.source = #{source}
        </if>
        <if test="keyword != null and keyword != '' ">
            and (
            ua.user_name like CONCAT("%",#{keyword},"%")
            or ua.nick_name like CONCAT("%",#{keyword},"%")
            or ua.id like CONCAT("%",#{keyword},"%")
            )
        </if>
        order by ua.create_time desc
        limit #{pageNo}, #{pageSize}
    </select>

    <select id="countRcd" resultType="int">
        select count(*)
        from user_rcd
        where rcd_user_id = #{userAccountId}
    </select>

    <select id="listUserRcd" resultType="com.mmc.iuav.user.entity.UserAccountDO"
            parameterType="com.mmc.iuav.user.model.qo.UserRcdQO">
        select ua.id, ua.phone_num, ua.user_name, ua.nick_name, ua.user_img, ur.create_time
        from user_account ua
                 inner join user_rcd ur on ua.id = ur.user_account_id
        where ur.rcd_user_id = #{userAccountId}
        order by ur.create_time desc
            limit #{pageNo}, #{pageSize}
    </select>

    <select id="topRcd" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        select ua.id,
               ua.nick_name,
               ua.user_img,
               ua.user_name,
               ua.phone_num,
               count(*) as invite_count
        from user_rcd urd
                 INNER JOIN user_account ua ON urd.rcd_user_id = ua.id
        where ua.disable = 0
        GROUP BY urd.rcd_user_id
        ORDER BY invite_count DESC limit #{topNum}
    </select>

    <select id="getInviteCount" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        select ua.id,
        ua.nick_name,
        ua.user_img,
        ua.user_name,
        ua.phone_num,
        count(*) as invite_count
        from user_rcd urd
        INNER JOIN user_account ua ON urd.rcd_user_id = ua.id
        where ua.disable = 0
        <if test="uIds != null">
            <foreach collection="uIds" separator="," open="and urd.rcd_user_id in (" close=")" item="id">
                #{id}
            </foreach>
        </if>
        GROUP BY urd.rcd_user_id
    </select>

    <select id="getUserAccountByPhoneNum" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        select id,
               phone_num,
               user_name,
               nick_name,
               user_img,
               open_id,
               union_id,
               user_sex,
               email,
               source,
               account_status,
               remark,
               port_type,
               disable,
               create_time,
               update_time
        from user_account
        where phone_num = #{phoneNum}
          and disable = 0
    </select>
    <select id="getUserAccountInfoByOpenId" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        select id,
               phone_num,
               user_name,
               nick_name,
               user_img,
               open_id,
               union_id,
               user_sex,
               email,
               `source`,
               account_status,
               remark,
               port_type,
               disable,
               create_time,
               update_time
        from user_account
        where open_id = #{openid}
          and disable = 0
    </select>
    <select id="listUserFddInfo" resultType="com.mmc.iuav.user.entity.fdd.UserFddDO">
        select id,
        customer_id,
        union_id,
        person_verify_status,
        ent_verify_status,
        account_type,
        create_time,
        update_time
        from user_fdd
        <where>
            union_id = #{unionId}
            <if test="openid != null">
                or union_id = #{openid}
            </if>
        </where>
    </select>
    <select id="getUsableUserCount" resultType="java.lang.Integer">
        SELECT count(*)
        FROM user_account ua
                 LEFT JOIN company_member cm ON cm.user_account_id = ua.id
                 LEFT JOIN company_info ci ON ci.id = cm.company_info_id
                 LEFT JOIN real_name_auth rna ON rna.user_account_id = ua.id
        WHERE ua.DISABLE = 0
          AND ua.port_type = 100
    </select>
    <select id="getYesterdayAddUserCount" resultType="java.lang.Integer">
        SELECT count(*)
        FROM user_account
        WHERE DATE (create_time) = CURDATE() - INTERVAL 1 DAY;
    </select>
    <select id="getActiveUserCountByDayNum" resultType="com.mmc.iuav.user.entity.data.ActiveUserCountDO">
        SELECT 1                                       AS day_num,
               COALESCE(SUM(auc.active_user_count), 0) AS active_user_count
        FROM active_user_count auc
        WHERE (TO_DAYS(NOW()) - TO_DAYS(auc.active_date)) = 1
    </select>

    <select id="countPhoneNum" resultType="java.lang.Integer">
        select count(*)
        from user_account
        where phone_num = #{phoneNum}
          and `disable` = 0
          and account_status = 1
    </select>

    <select id="countPhoneNums" resultType="java.lang.Integer">
        select count(*)
        from user_account
        where phone_num = #{phoneNum}
          and `disable` = 0
          and account_status = 1
          and id = #{userAccountId}
    </select>

    <select id="updateAccountPhone">
        update user_account
        <set>
            phone_num = #{phoneNum}
        </set>
        where id = #{userAccountId}
    </select>

    <update id="appUpdateUser" parameterType="com.mmc.iuav.user.model.qo.UserMessageQO">
        update user_account
        <set>
            <if test="userImg!='' and userImg!=null ">
                user_img=#{userImg},
            </if>
            <if test="coverPicture!='' and coverPicture!=null">
                cover_picture=#{coverPicture},
            </if>
            <if test="nickName!='' and nickName!=null ">
                nick_name=#{nickName},
            </if>
            <if test="districtChildId!=null">
                district_child_id=#{districtChildId},
            </if>
            <if test="briefIntroduction!='' and briefIntroduction!=null">
                brief_introduction=#{briefIntroduction},
            </if>
        </set>
        where id=#{id}
    </update>

    <select id="selectUserAccount" resultType="java.lang.Integer"
            parameterType="com.mmc.iuav.user.model.qo.UserMessageQO">
        select count(*)
        from user_account
        where disable = 0
          and nick_name = #{nickName}
          and id!=#{id}
    </select>

    <select id="selectUserAccountNickName" resultType="java.lang.Integer"
            parameterType="com.mmc.iuav.user.entity.UserAccountDO">
        select count(*)
        from user_account
        where disable = 0
          and nick_name = #{nickName}
          and id!=#{id}
    </select>

    <select id="getDistrictChild" resultType="java.lang.String">
        SELECT concat(p1.`name`, p2.`name`) AS region
        FROM sys_district p1,
             sys_district p2
        WHERE p2.LEVEL != 3
	AND p1.id = p2.pid
	AND p2.id = #{districtChildId}
    </select>

    <select id="userMessages" resultType="com.mmc.iuav.user.model.vo.UserAccountMessageVo">
        SELECT
            rna.user_account_id as userAccountId,
            rna.sex,
            (
                YEAR (
                CURDATE())- YEAR ( birthday )) AS birthday,
            pc.id as pilotId,
            pc.years_of_working
        FROM
            real_name_auth rna
                INNER JOIN pilot_certification pc ON rna.user_account_id = pc.user_account_id
        WHERE
            rna.user_account_id = #{userAccountId}
    </select>

    <select id="userMessagesPilot" resultType="java.lang.String">
        SELECT
            ability_name
        FROM
            pilot_ability_certification pac
        WHERE
            pilot_certification_id = #{pilotId}
    </select>

</mapper>
