<?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.crm.ucard.dao.mapper.CardGlobalCurrenciesMapper">

    <insert id="batchSave" parameterType="com.crm.rely.backend.core.pojo.table.ucard.CardGlobalCurrenciesTable">
        insert into ucard_global_currencies(
        `payout_currency`,
        `transfer_type_id`,
        `transfer_type_value`,
        `payout_method_id`,
        `payout_method_value`,
        `country`,
        `fee_rate`,
        `fixed_fee`,
        `min_quota`,
        `max_quota`,
        `year_transfer_amount_quota`,
        `status`,
        `exchange_rate`,
        `add_time`
        )
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
             #{item.payoutCurrency},
             #{item.transferTypeId},
             #{item.transferTypeValue},
             #{item.payoutMethodId},
             #{item.payoutMethodValue},
             #{item.country},
             #{item.feeRate},
             #{item.fixedFee},
             #{item.minQuota},
             #{item.maxQuota},
             #{item.yearTransferAmountQuota},
             #{item.status},
             #{item.exchangeRate},
             now()
             )
        </foreach>
    </insert>
    <delete id="deleteAll">
        delete from ucard_global_currencies
    </delete>
    <select id="countList" resultType="java.lang.Integer">
        select count(*)
        from ucard_global_currencies gc
        left join ucard_fee_rate_config rc on rc.payout_currency = gc.payout_currency and rc.transfer_type_id = gc.transfer_type_id
        and rc.payout_method_id = gc.payout_method_id and rc.`type` = '2'
        <where>
            <if test="payoutCurrency != null and payoutCurrency != ''">
                and gc.`payout_currency` = #{payoutCurrency}
            </if>
            <if test="transferTypeId != null and transferTypeId != ''">
                and gc.`transfer_type_id` = #{transferTypeId}
            </if>
            <if test="payoutMethodId != null and payoutMethodId != ''">
                and gc.`payout_method_id` = #{payoutMethodId}
            </if>
            <if test="status != null and status != ''">
                and gc.`status` = #{status}
            </if>
        </where>
    </select>
    <select id="pageList" resultType="com.crm.rely.backend.core.dto.wasabi.WasabiAvailableCurrenciesListDto">
        select
        gc.`payout_currency`,
        gc.`transfer_type_id`,
        gc.`transfer_type_value`,
        gc.`payout_method_id`,
        gc.`payout_method_value`,
        gc.`country`,
        gc.`fee_rate`,
        gc.`fixed_fee`,
        gc. `min_quota`,
        gc.`max_quota`,
        gc.`status`,
        gc.`year_transfer_amount_quota`,
        rc.`fee_rate_config` as fee_rate_config,
        rc.`fixed_fee_config` as fixed_fee_config,
        rc. `min_quota_config` as min_quota_config,
        rc.`max_quota_config` as max_quota_config,
        rc.`year_transfer_amount_quota_config` as year_transfer_amount_quota_config,
        rc.`type`
        from ucard_global_currencies gc
        left join ucard_fee_rate_config rc on rc.payout_currency = gc.payout_currency and rc.transfer_type_id = gc.transfer_type_id
        and rc.payout_method_id = gc.payout_method_id and rc.`type` = '2'
        <where>
            <if test="payoutCurrency != null and payoutCurrency != ''">
                and gc.`payout_currency` = #{payoutCurrency}
            </if>
            <if test="transferTypeId != null and transferTypeId != ''">
                and gc.`transfer_type_id` = #{transferTypeId}
            </if>
            <if test="payoutMethodId != null and payoutMethodId != ''">
                and gc.`payout_method_id` = #{payoutMethodId}
            </if>
            <if test="status != null and status != ''">
                and gc.`status` = #{status}
            </if>
        </where>
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="dropdownList" resultType="com.crm.rely.backend.core.dto.ucard.GlobalCurrenciesDropdownListDto">
        select
            gc.`id`,
            gc.`payout_currency`,
            gc.`transfer_type_id`,
            gc.`transfer_type_value`,
            gc.`payout_method_id`,
            gc.`payout_method_value`,
            gc.`country`,
            gc.`status`,
            COALESCE(COALESCE(rc.`min_quota_config`, rc2.`min_quota_config`), gc.`min_quota`) as min_quota_config,
            COALESCE(COALESCE(rc.`max_quota_config`, rc2.`max_quota_config`), gc.`max_quota`) as max_quota_config,
            COALESCE(COALESCE(rc.`year_transfer_amount_quota_config`, rc2.`year_transfer_amount_quota_config`), gc.`year_transfer_amount_quota`) as year_transfer_amount_quota_config,
            COALESCE(rc.`type`, rc2.`type`)
        from ucard_global_currencies gc
        left join ucard_fee_rate_config rc on rc.payout_currency = gc.payout_currency and rc.transfer_type_id = gc.transfer_type_id
        and rc.payout_method_id = gc.payout_method_id and rc.`type` = '2'
        left join ucard_fee_rate_config rc2 on rc2.`type` = '1'
        where gc.`status` = 'online'
    </select>
    <select id="findAllByStatus"
            resultType="com.crm.rely.backend.core.dto.ucard.GlobalCurrenciesDropdownListDto">
        select
            gc.`id`,
            gc.`payout_currency`,
            gc.`transfer_type_id`,
            gc.`transfer_type_value`,
            gc.`payout_method_id`,
            gc.`payout_method_value`,
            gc.`country`,
            gc.`status`
        from ucard_global_currencies gc
        <where>
            <if test="status != null and status != ''">
                and gc.`status` = #{status}
            </if>
        </where>
    </select>

</mapper> 