<?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.thinkgem.jeesite.modules.user.dao.UserApiDao">

    <select id="getUserInfo" resultType="com.thinkgem.jeesite.modules.user.entity.UserEntity">
        SELECT
            yu.id,
            yu.code,
            yu.nickname,
            yu.sub_merchant_id AS subMerchantId,
            yu.level,
            yu.mobile,
            yu.password,
            yu.avatar,
            yu.recommended_mobile AS recommendedMobile,
            yu.recommended_path AS recommendedPath,
            yu.belong_to AS belongTo,
            yu.status,
            yu.create_date AS created,
            yu.create_by AS createdUser,
            yu.update_date AS modified,
            yu.update_by AS modifiedUser
        FROM
            youka_users yu
        WHERE
            yu.code = #{subMerchantCode}
    </select>

    <insert id="saveIdentifyCode">
        INSERT INTO youka_identity_code (
            code,
            func,
            mobile,
            identity_code,
            create_by,
            update_by
        )
        VALUES
            (#{code}, #{function}, #{mobile}, #{identityCode}, 'sys', 'sys')
    </insert>

    <select id="getIdentifyCode" resultType="com.thinkgem.jeesite.modules.user.entity.IdentityCodeEntity">
        SELECT
            yic.id,
            yic.code,
            yic.func AS function,
            yic.mobile,
            yic.identity_code AS identityCode,
            yic.create_date AS created,
            yic.create_by AS createdUser,
            yic.update_date AS modified,
            yic.update_by AS modifiedUser
        FROM
            youka_identity_code yic
        WHERE
            yic.func = #{function}
            AND yic.mobile = #{mobile}
            AND yic.identity_code = #{identityCode}
    </select>

    <select id="checkRecommend" resultType="com.thinkgem.jeesite.modules.user.entity.UserEntity">
        SELECT
            yu.id,
            yu.code,
            yu.nickname,
            yu.sub_merchant_id AS subMerchantId,
            yu.level,
            yu.mobile,
            yu.password,
            yu.avatar,
            yu.recommended_mobile AS recommendedMobile,
            yu.recommended_path AS recommendedPath,
            yu.belong_to AS belongTo,
            yu.status,
            yu.create_date AS created,
            yu.create_by AS createdUser,
            yu.update_date AS modified,
            yu.update_by AS modifiedUser
        FROM
            youka_users yu
        WHERE
            yu.recommended_mobile = #{recommendedMobile}
        LIMIT 1
    </select>

    <select id="checkMobile" resultType="com.thinkgem.jeesite.modules.user.entity.UserEntity">
        SELECT
            yu.id,
            yu.code,
            yu.nickname,
            yu.sub_merchant_id AS subMerchantId,
            yu.level,
            yu.mobile,
            yu.password,
            yu.avatar,
            yu.recommended_mobile AS recommendedMobile,
            yu.recommended_path AS recommendedPath,
            yu.belong_to AS belongTo,
            yu.status,
            yu.create_date AS created,
            yu.create_by AS createdUser,
            yu.update_date AS modified,
            yu.update_by AS modifiedUser
        FROM
            youka_users yu
        WHERE
            yu.mobile = #{mobile}
    </select>

    <insert id="signUp">
        INSERT INTO youka_users (
            code,
            nickname,
            sub_merchant_id,
            level,
            mobile,
            password,
            avatar,
            recommended_mobile,
            recommended_path,
            belong_to,
            status,
            create_by,
            update_by
        )
        VALUES
            (#{code}, #{nickname}, #{subMerchantId}, #{level}, #{mobile}, #{password}, #{avatar}, #{recommendedMobile}, #{recommendedPath}, #{belongTo}, #{status}, #{createdUser}, #{modifiedUser})
    </insert>

    <update id="resetPassword">
        UPDATE youka_users
            SET password = #{password}
        WHERE
            code = #{code}
    </update>

    <select id="getUserInfoByPassword" resultType="com.thinkgem.jeesite.modules.user.entity.UserEntity">
        SELECT
            yu.id,
            yu.code,
            yu.nickname,
            yu.sub_merchant_id AS subMerchantId,
            yu.level,
            yu.mobile,
            yu.password,
            yu.avatar,
            yu.recommended_mobile AS recommendedMobile,
            yu.recommended_path AS recommendedPath,
            yu.belong_to AS belongTo,
            yu.status,
            yu.create_date AS created,
            yu.create_by AS createdUser,
            yu.update_date AS modified,
            yu.update_by AS modifiedUser
        FROM
            youka_users yu
        WHERE
            yu.mobile = #{mobile}
            AND yu.password = #{password}
    </select>

    <select id="getUserDetailByCode" resultType="com.thinkgem.jeesite.modules.user.entity.UserDetailEntity">
        SELECT
            yud.id,
            yud.code,
            yud.user_code AS userCode,
            yud.real_name AS realName,
            yud.id_card AS idCard,
            yud.id_card_place AS idCardPlace,
            yud.id_card_front AS idCardFront,
            yud.id_card_back AS idCardBack,
            yud.id_card_hold AS idCardHold,
            yud.debit_card AS debitCard,
            yud.create_date AS created,
            yud.create_by AS createdUser,
            yud.update_date AS modified,
            yud.update_by AS modifiedUser
        FROM
            youka_user_details yud
        WHERE
            yud.user_code = #{code}
        LIMIT 1
    </select>

    <select id="getCardInfoByCode" resultType="com.thinkgem.jeesite.modules.user.entity.CardEntity">
        SELECT
            yc.id,
            yc.code,
            yc.user_code AS userCode,
            yc.card_type AS cardType,
            yc.card_no AS cardNo,
            yc.card_mobile AS cardMobile,
            yc.belong_bank AS belongBank,
            yc.card_bank AS cardBank,
            yc.card_place AS cardPlace,
            yc.is_debit AS isDebit,
            yc.sub_merchant_id AS subMerchantId,
            yc.card_src AS cardSrc,
            yc.type,
            yc.status,
            yc.province,
            yc.city,
            yc.create_date AS created,
            yc.create_by AS createdUser,
            yc.update_date AS modified,
            yc.update_by AS modifiedUser,
            yc.del_flag AS delFlag
        FROM
            youka_cards yc
        WHERE
            yc.user_code = #{code}
            AND yc.is_debit = 'Y'
            AND yc.del_flag = '0'
    </select>

    <select id="getCardsInfo" resultType="com.thinkgem.jeesite.modules.user.entity.CardEntity">
        SELECT
            yc.id,
            yc.code,
            yc.user_code AS userCode,
            yc.card_type AS cardType,
            yc.card_no AS cardNo,
            yc.card_mobile AS cardMobile,
            yc.belong_bank AS belongBank,
            yb.bank_name AS bankName,
            yb.bank_short AS bankShort,
            yc.card_bank AS cardBank,
            yc.card_place AS cardPlace,
            yc.is_debit AS isDebit,
            yc.sub_merchant_id AS subMerchantId,
            yc.card_src AS cardSrc,
            yc.type,
            yc.status,
            yc.province,
            yc.city,
            yc.create_date AS created,
            yc.create_by AS createdUser,
            yc.update_date AS modified,
            yc.update_by AS modifiedUser,
            yc.del_flag AS delFlag
        FROM
            youka_cards yc
        LEFT JOIN youka_bank yb ON yc.belong_bank = yb.bank_code
        WHERE
            yc.user_code = #{subMerchantCode}
            AND yc.card_type = #{cardType}
            AND yc.del_flag = '0'
            <if test="payChannel!=null and payChannel != ''">
            AND yb.support_pay_channel LIKE concat('%',#{payChannel},',%')
            </if>
    </select>

    <select id="getCardInfo" resultType="com.thinkgem.jeesite.modules.user.entity.CardEntity">
        SELECT
            yc.id,
            yc.code,
            yc.user_code AS userCode,
            yc.card_type AS cardType,
            yc.card_no AS cardNo,
            yc.card_mobile AS cardMobile,
            yc.belong_bank AS belongBank,
            yb.bank_name AS bankName,
            yb.bank_short AS bankShort,
            yb.support_pay_channel AS supportPayChannel,
            yc.card_bank AS cardBank,
            yc.card_place AS cardPlace,
            yc.is_debit AS isDebit,
            yc.sub_merchant_id AS subMerchantId,
            yc.card_src AS cardSrc,
            yc.type,
            yc.status,
            yc.province,
            yc.city,
            yc.create_date AS created,
            yc.create_by AS createdUser,
            yc.update_date AS modified,
            yc.update_by AS modifiedUser,
            yc.del_flag AS delFlag
        FROM
            youka_cards yc
        LEFT JOIN youka_bank yb ON yc.belong_bank = yb.bank_code
        WHERE
            yc.user_code = #{subMerchantCode}
            AND yc.code = #{cardCode}
            AND yc.del_flag = '0'
        LIMIT 1
    </select>

    <update id="removeCards">
        UPDATE
        youka_cards
        SET
        del_flag = '1'
        WHERE code IN
        <foreach collection="cardCodes" separator="," open="(" close=")" index="index" item="cardCode">
            #{cardCode}
        </foreach>
    </update>

    <update id="updateCardStatus">
        UPDATE
            youka_cards
        SET
            is_debit = #{isDebit}
        WHERE
            user_code = #{userCode}
    </update>

    <insert id="addNewCard">
        INSERT INTO youka_cards (
            code,
            user_code,
            card_type,
            card_no,
            card_mobile,
            belong_bank,
            card_bank,
            card_place,
            is_debit,
            sub_merchant_id,
            card_src,
            type,
            status,
            province,
            city,
            create_by,
            update_by,
            del_flag
        )
        VALUES
            (#{code}, #{userCode}, #{cardType}, #{cardNo}, #{cardMobile}, #{belongBank}, #{cardBank}, #{cardPlace}, #{isDebit}, #{subMerchantId}, #{cardSrc}, #{type}, #{status}, #{province}, #{city}, #{createdUser}, #{modifiedUser}, #{delFlag})
    </insert>

    <insert id="addFeedback">
        INSERT INTO youka_feedback (
            code,
            user_code,
            feedback,
            create_by,
            update_by
        )
        VALUES
            (#{code}, #{userCode}, #{feedback}, #{createdUser}, #{modifiedUser})
    </insert>

    <select id="getAboutUsInfo" resultType="com.thinkgem.jeesite.modules.user.entity.AboutUsEntity">
        SELECT
            yau.id,
            yau.code,
            yau.about_info AS aboutInfo,
            yau.create_date AS created,
            yau.create_by AS createdUser,
            yau.update_date AS modified,
            yau.update_by AS modifiedUser
        FROM
            youka_about_us yau
        ORDER BYd
            yau.update_date DESC
        LIMIT 1
    </select>

    <insert id="saveUserDetail">
        INSERT INTO youka_user_details (
            code,
            user_code,
            real_name,
            id_card,
            id_card_place,
            id_card_front,
            id_card_back,
            id_card_hold,
            debit_card,
            create_by,
            update_by,
            update_date
        )
        VALUES
            (#{code}, #{userCode}, #{realName}, #{idCard}, #{idCardPlace}, #{idCardFront}, #{idCardBack}, #{idCardHold}, #{debitCard}, #{createdUser}, #{modifiedUser},now())
    </insert>

    <update id="updateUserDetail">
        UPDATE
            youka_user_details
        SET
            real_name = #{realName},
            id_card = #{idCard},
            id_card_place = #{idCardPlace},
            id_card_front = #{idCardFront},
            id_card_back = #{idCardBack},
            id_card_hold = #{idCardHold},
            debit_card = #{debitCard},
            create_by = #{createdUser},
            update_by = #{modifiedUser}
        WHERE
            user_code = #{userCode}
    </update>

    <update id="updateUserInfo">
        UPDATE youka_users
        SET code = #{code}
        <if test="status != null and status !=''">
            , status = #{status}
        </if>
        WHERE code = #{code}
    </update>
    <update id="updateUser">
        UPDATE youka_users
        SET code = #{subMerchantCode}
        <if test="avatar != null and avatar !=''">
            , avatar = #{avatar}
        </if>
        <if test="mobile != null and mobile !=''">
            , mobile = #{mobile}
        </if>
        <if test="nickname != null and nickname !=''">
            , nickname = #{nickname}
        </if>
        WHERE code = #{subMerchantCode}
    </update>
    <select id="getRateByLevel" resultType="com.thinkgem.jeesite.modules.pay.entity.RateEntity">
        SELECT
            yr.id,
            yr.code,
            yr.user_level AS userLevel,
            yr.pay_method AS payMethod,
            yr.pay_channel AS payChannel,
            yr.rate,
            yr.balance,
            yr.single_limit AS singleLimit,
            yr.day_limit AS dayLimit,
            yr.create_date AS created,
            yr.create_by AS createdUser,
            yr.update_date AS modified,
            yr.update_by AS modifiedUser
        FROM
            youka_rate yr
        WHERE
            yr.user_level = #{userLevel}
            AND yr.pay_method = #{payMethod}
            AND yr.pay_channel = #{payChannel}
    </select>

    <select id="getUserInfoBySameRecommend" resultType="com.thinkgem.jeesite.modules.user.entity.UserEntity">
        SELECT
            yu.id,
            yu.code,
            yu.nickname,
            yu.sub_merchant_id AS subMerchantId,
            yu.level,
            yu.mobile,
            yu.password,
            yu.avatar,
            yu.recommended_mobile AS recommendedMobile,
            yu.recommended_path AS recommendedPath,
            yu.belong_to AS belongTo,
            yu.status,
            yu.create_date AS created,
            yu.create_by AS createdUser,
            yu.update_date AS modified,
            yu.update_by AS modifiedUser
        FROM
            youka_users yu
        WHERE
            yu.recommended_path = (SELECT yu1.recommended_path FROM youka_users yu1 WHERE yu1.code = #{code})
            AND yu.status != 0
    </select>

    <select id="getUserInfoById" resultType="com.thinkgem.jeesite.modules.user.entity.UserEntity">
        SELECT
            yu.id,
            yu.code,
            yu.nickname,
            yu.sub_merchant_id AS subMerchantId,
            yu.level,
            yu.mobile,
            yu.password,
            yu.avatar,
            yu.recommended_mobile AS recommendedMobile,
            yu.recommended_path AS recommendedPath,
            yu.belong_to AS belongTo,
            yu.status,
            yu.create_date AS created,
            yu.create_by AS createdUser,
            yu.update_date AS modified,
            yu.update_by AS modifiedUser
        FROM
            youka_users yu
        WHERE
            id = #{id}
    </select>

    <update id="updateUserLevel">
        UPDATE
            youka_users
        SET
            level = #{level}
        WHERE
            code = #{code}
            AND level = 'E'
    </update>

    <select id="getRecommendCardsInfo" resultType="com.thinkgem.jeesite.modules.user.entity.CardEntity">
        SELECT
            yc.id,
            yc.code,
            yc.user_code AS userCode,
            yc.card_type AS cardType,
            yc.card_no AS cardNo,
            yc.card_mobile AS cardMobile,
            yc.belong_bank AS belongBank,
            yc.card_bank AS cardBank,
            yc.card_place AS cardPlace,
            yc.is_debit AS isDebit,
            yc.sub_merchant_id AS subMerchantId,
            yc.card_src AS cardSrc,
            yc.type,
            yc.status,
            yc.province,
            yc.city,
            yc.create_date AS created,
            yc.create_by AS createdUser,
            yc.update_date AS modified,
            yc.update_by AS modifiedUser,
            yc.del_flag AS delFlag
        FROM
            youka_cards yc
        WHERE
            yc.user_code = #{code}
            AND yc.del_flag = '0'
    </select>

    <update id="updateCardNoDebit">
        UPDATE
            youka_cards
        SET
            is_debit = 'N'
        WHERE
            user_code = #{subMerchantCode}
    </update>

    <update id="updateCardDebit">
        UPDATE
            youka_cards
        SET
            is_debit = 'Y'
        WHERE
            code = #{debitCardCode}
    </update>


    <select id="getDebitCard" resultType="com.thinkgem.jeesite.modules.user.entity.CardEntity">
        SELECT
            yc.id,
            yc.code,
            yc.user_code AS userCode,
            yc.card_type AS cardType,
            yc.card_no AS cardNo,
            yc.card_mobile AS cardMobile,
            yc.belong_bank AS belongBank,
            yc.card_bank AS cardBank,
            yc.card_place AS cardPlace,
            yc.is_debit AS isDebit,
            yc.sub_merchant_id AS subMerchantId,
            yc.card_src AS cardSrc,
            yc.type,
            yc.status,
            yc.province,
            yc.city,
            yc.create_date AS created,
            yc.create_by AS createdUser,
            yc.update_date AS modified,
            yc.update_by AS modifiedUser,
            yc.del_flag AS delFlag
        FROM
            youka_cards yc
        WHERE
            yc.code = #{code}
    </select>

    <select id="getDebitCardByUserCode" resultType="com.thinkgem.jeesite.modules.user.entity.CardEntity">
        SELECT
        yc.id,
        yc.code,
        yc.user_code AS userCode,
        yc.card_type AS cardType,
        yc.card_no AS cardNo,
        yc.card_mobile AS cardMobile,
        yc.belong_bank AS belongBank,
        yc.card_bank AS cardBank,
        yc.card_place AS cardPlace,
        yc.is_debit AS isDebit,
        yc.sub_merchant_id AS subMerchantId,
        yc.card_src AS cardSrc,
        yc.type,
        yc.status,
        yc.province,
        yc.city,
        yc.create_date AS created,
        yc.create_by AS createdUser,
        yc.update_date AS modified,
        yc.update_by AS modifiedUser,
        yc.del_flag AS delFlag
        FROM
        youka_cards yc
        WHERE
        yc.user_code = #{subMerchantCode}
        AND yc.is_debit = 'Y'
    </select>

    <select id="getProvince" resultType="com.thinkgem.jeesite.modules.user.entity.ProvinceCityEntity">
        SELECT
            id,
            province_code AS code,
            province AS name,
            country_id AS pid
        FROM
            region_provinces
        WHERE
            country_id = #{pid}
    </select>

    <select id="getCity" resultType="com.thinkgem.jeesite.modules.user.entity.ProvinceCityEntity">
        SELECT
            id,
            city_code AS code,
            city AS name,
            province_id AS pid
        FROM
            region_cities
        WHERE
            province_id = #{pid}
    </select>

    <select id="getBelongBank" resultType="com.thinkgem.jeesite.modules.user.entity.BelongBankEntity">
        SELECT
            yb.id,
            yb.code,
            yb.bank_code AS bankCode,
            yb.bank_name AS bankName,
            yb.bank_short AS bankShort,
            yb.create_date AS created,
            yb.create_by AS createdUser,
            yb.update_date AS modified,
            yb.update_by AS modifiedUser,
            yb.del_flag AS delFlag
        FROM
            youka_bank yb
        WHERE
            del_flag = '0'
    </select>


    <select id="getBelongBankEntity" resultType="com.thinkgem.jeesite.modules.user.entity.BelongBankEntity">
        SELECT
        yb.id,
        yb.code,
        yb.bank_code AS bankCode,
        yb.bank_name AS bankName,
        yb.bank_short AS bankShort,
        yb.create_date AS created,
        yb.create_by AS createdUser,
        yb.update_date AS modified,
        yb.update_by AS modifiedUser,
        yb.del_flag AS delFlag
        FROM
        youka_bank yb
        WHERE
        del_flag = '0'
        AND yb.bank_code = #{belongBank}

    </select>

    <select id="getRateListByLevel" resultType="com.thinkgem.jeesite.modules.pay.entity.RateEntity">
         SELECT
            yr.user_level AS userLevel,
            yr.pay_method AS payMethod,
            yr.rate,
            yr.balance,
            yr.single_limit AS singleLimit,
            yr.day_limit AS dayLimit,
            sd.img AS "img",
            sd.label AS "payMethodLabel"
        FROM
            youka_rate yr
        LEFT JOIN sys_dict sd ON sd.`value` = yr.pay_method
        WHERE
            yr.user_level = #{userLevel}
        ORDER BY sd.sort DESC
    </select>
    <select id="getLevelByUserLevel" resultType="com.thinkgem.jeesite.modules.user.entity.Level">
         SELECT
            yl.right_interest AS "rightInterest"
        FROM
            youka_levels yl
        WHERE
            yl.`level` = #{level}
    </select>

</mapper>