<?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="cn.qg.qaplatform.zdata.mapper.XyqbLoanDataMapper">
    <!-- 注册未申请 -->
    <select id="getUnApplyLoanUserUuidList" resultType="java.lang.String">
        SELECT `uuid` FROM `apply_list`
        <where>
            `apply_status` = 1
            <if test="channel != null">
                AND `channel_id` = #{channel}
            </if>
        </where>
    </select>

    <!-- 根据uuidList获取用户信息列表   -->
    <select id="getUserDataListByUuidList" parameterType="java.util.List" resultType="cn.qg.qaplatform.zdata.entity.XjfqUserEntity">
        SELECT `id` as userId, `phone_no` as phoneNo, `uuid`, `registered_from` as registeredFrom, `enable`, `created_at` as createdAt  FROM `user`
        where `uuid` in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        order by created_at desc
    </select>

    <!-- 判断手机号是否在库里  -->
    <select id="queryIfPhoneExist" resultType="object">
        SELECT * FROM `user` where phone_no = #{phone}
    </select>

    <!-- 根据userIdList获取用户信息List   -->
    <select id="getUserDataListByUserIdList" resultType="cn.qg.qaplatform.zdata.entity.XjfqUserEntity">
        SELECT  `id` as userId, `uuid`, `phone_no` as phoneNo, `registered_from` as registeredFrom, `enable`, `created_at` as createdAt FROM `user`
        where `id` in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        order by created_at desc
    </select>

    <!-- 根据不同审核状态返回用户Id列表  -->
    <select id="getAuditStatusUserIdList" resultType="java.lang.String">
        SELECT `user_id` FROM apply_quota_record
        <where>
            `apply_status` = #{status}
            <if test="channel != null">
                AND `apply_from` = #{channel}
            </if>
        </where>
    </select>

    <!--  审核通过未提现  xyqb库 -->
    <select id="queryAuthAndUnWithdraw" resultType="java.lang.String">
        SELECT afrr.user_id
        FROM apply_funding_risk_record as afrr
        LEFT JOIN assets_order as ao ON afrr.assets_order_id = ao.id
        <where>
            afrr.apply_status = 2
            AND ao.status &lt;&gt; 1
            <if test="channel != null">
                AND ao.created_from = #{channel}
            </if>
            <if test="fundId != null">
                AND afrr.funding_corp_id=#{fundId}
            </if>
        </where>
    </select>

    <!-- 获取不同放款状态的用户Id列表 xyqb库 -->
    <select id="getLoanStatusUserIdList" resultType="java.lang.String">
        SELECT lah.`user_id`
        FROM `loan_application_history` as lah
        LEFT JOIN `apply_funding_risk_record` as afrr
        on lah.`user_id` = afrr.`user_id`
        <where>
            `progress` = #{progress}
            <if test="channel != null">
                AND lah.`created_from` = #{channel}
            </if>
            <if test="fundId != null">
                AND afrr.`funding_corp_id` = #{fundId}
            </if>
        </where>
    </select>

    <!-- 获取多订单用户列表 -->
    <select id="getMultiOrderuuidList" resultType="java.lang.String">
       SELECT uuid FROM `white_list_config_new`
    </select>

    <!-- 获取资方id   -->
    <select id="getAssignFundIdByUserId" resultType="java.lang.Integer">
        SELECT `funding_corp_id` as `fundId` FROM `assign_funding_record`
        WHERE `user_id` = #{userId} order by id desc limit 0,1
    </select>

    <!--  根据手机号获取用户信息-->
    <select id="getUserInfoByPhoneNo" resultType="cn.qg.qaplatform.zdata.entity.XjfqGenProcessVo">
        SELECT `id` as userId, `phone_no` as phoneNo, `uuid`, `registered_from` as registeredFrom, `created_at` as createdAt  FROM `user`
        WHERE `phone_no` = #{phoneNo}
    </select>

    <!-- 根据用户id获取放款进度 -->
    <select id="getLoanProgressByUserId" resultType="java.lang.Integer">
        SELECT `progress` FROM `loan_application_history`
        WHERE `user_id`=#{userId}
    </select>

</mapper>