<?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.CardTypeMapper">
    <insert id="batchSave" parameterType="java.util.List">
        INSERT IGNORE INTO ucard_type (
        card_type_id,
        `type`,
        card_name,
        card_desc,
        `status`,
        enable_status,
        card_category,
        organization,
        country,
        bank_card_bin,
        card_price,
        card_price_currency,
        risk,
        support,
        support_holder_regin,
        support_holder_area_code,
        need_card_holder,
        need_deposit_for_active_card,
        deposit_amount_min_quota_for_active_card,
        deposit_amount_max_quota_for_active_card,
        fiat_currency,
        max_count,
        recharge_currency,
        recharge_min_quota,
        recharge_max_quota,
        recharge_digital,
        recharge_fee_rate,
        recharge_fixed_fee,
        enable_active_card,
        enable_deposit,
        enable_freeze,
        enable_un_freeze,
        card_holder_max_card_limit,
        card_holder_model,
        support_setting_no_pin_payment_amount,
        default_no_pin_payment_amount,
        no_pin_payment_amount_min_quota,
        no_pin_payment_amount_max_quota,
        add_time,
        add_user,
        add_ip,
        modify_time,
        modify_user,
        modify_ip,
        note
        )
        VALUES
        <foreach collection="list" item="table" separator=",">
            (
            #{table.cardTypeId},
            #{table.type},
            #{table.cardName},
            #{table.cardDesc},
            #{table.status},
            #{table.enableStatus},
            #{table.cardCategory},
            #{table.organization},
            #{table.country},
            #{table.bankCardBin},
            #{table.cardPrice},
            #{table.cardPriceCurrency},
            #{table.risk},
            #{table.support},
            #{table.supportHolderRegin},
            #{table.supportHolderAreaCode},
            #{table.needCardHolder},
            #{table.needDepositForActiveCard},
            #{table.depositAmountMinQuotaForActiveCard},
            #{table.depositAmountMaxQuotaForActiveCard},
            #{table.fiatCurrency},
            #{table.maxCount},
            #{table.rechargeCurrency},
            #{table.rechargeMinQuota},
            #{table.rechargeMaxQuota},
            #{table.rechargeDigital},
            #{table.rechargeFeeRate},
            #{table.rechargeFixedFee},
            #{table.enableActiveCard},
            #{table.enableDeposit},
            #{table.enableFreeze},
            #{table.enableUnFreeze},
            #{table.cardHolderMaxCardLimit},
            #{table.cardHolderModel},
            #{table.supportSettingNoPinPaymentAmount},
            #{table.defaultNoPinPaymentAmount},
            #{table.noPinPaymentAmountMinQuota},
            #{table.noPinPaymentAmountMaxQuota},
            #{table.addTime},
            #{table.addUser},
            #{table.addIp},
            #{table.modifyTime},
            #{table.modifyUser},
            #{table.modifyIp},
            #{table.note}
            )
        </foreach>
    </insert>
    <delete id="deleteByCardCategory">
        DELETE FROM ucard_type WHERE card_category = #{cardCategory}
    </delete>

    <select id="countList" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardTypePageEntity">
        SELECT COUNT(*) FROM ucard_type t
        LEFT JOIN ucard_type_config c ON t.card_type_id = c.card_type_id
        <where>
            <if test="cardTypeId!=null">
                and t.`card_type_id` = #{cardTypeId}
            </if>
            <if test="type != null and type != ''">
                and t.`type` = #{type}
            </if>
            <if test="cardName != null and cardName != ''">
                and (c.card_name like concat('%', #{cardName},'%')
                or (c.card_name is null and t.card_name like concat('%', #{cardName},'%')))
            </if>
            <if test="status != null and status != ''">
                and t.`status` = #{status}
            </if>
            <if test="enableStatus!=null">
                and t.`enable_status` = #{enableStatus}
            </if>
            <if test="cardCategory != null and cardCategory > 0">
                and t.`card_category` = #{cardCategory}
            </if>
            and t.`enable_status` = 1
        </where>
    </select>

    <select id="pageList" resultType="com.crm.rely.backend.core.dto.ucard.CardTypeDto"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardTypePageEntity">
        SELECT
        t.`id`,
        t.card_type_id,
        t.`type`,
        COALESCE(c.card_name, t.card_name) as card_name,
        COALESCE(c.card_desc, t.card_desc) as card_desc,
        t.`status`,
        t.`enable_status`,
        t.card_category,
        t.organization,
        t.country,
        COALESCE(c.bank_card_bin, t.bank_card_bin) as bank_card_bin,
        t.card_price,
        t.card_price_currency,
        t.risk,
        t.support,
        t.support_holder_regin,
        t.support_holder_area_code,
        t.need_card_holder,
        t.need_deposit_for_active_card,
        t.deposit_amount_min_quota_for_active_card,
        t.deposit_amount_max_quota_for_active_card,
        t.fiat_currency,
        t.max_count,
        t.recharge_currency,
        COALESCE(c.recharge_min_quota, t.recharge_min_quota) as recharge_min_quota,
        COALESCE(c.recharge_max_quota, t.recharge_max_quota) as recharge_max_quota,
        COALESCE(c.recharge_digital, t.recharge_digital) as recharge_digital,
        COALESCE(c.recharge_fee_rate, t.recharge_fee_rate) as recharge_fee_rate,
        COALESCE(c.recharge_fixed_fee, t.recharge_fixed_fee) as recharge_fixed_fee,
        t.recharge_min_quota as min_quota,
        t.recharge_max_quota as max_quota,
        t.enable_active_card,
        t.enable_deposit,
        t.enable_freeze,
        t.enable_un_freeze,
        t.card_holder_max_card_limit,
        t.card_holder_model,
        t.support_setting_no_pin_payment_amount,
        t.default_no_pin_payment_amount,
        t.no_pin_payment_amount_min_quota,
        t.no_pin_payment_amount_max_quota,
        c.is_contact,
        c.card_fee,
        c.original_card_fee
        FROM ucard_type t
        LEFT JOIN ucard_type_config c ON t.card_type_id = c.card_type_id
        <where>
            <if test="cardTypeId!=null">
                and t.`card_type_id` = #{cardTypeId}
            </if>
            <if test="type != null and type != ''">
                and t.`type` = #{type}
            </if>
            <if test="cardName != null and cardName != ''">
                and (c.card_name like concat('%', #{cardName},'%')
                or (c.card_name is null and t.card_name like concat('%', #{cardName},'%')))
            </if>
            <if test="status != null and status != ''">
                and t.`status` = #{status}
            </if>
            <if test="enableStatus!=null">
                and t.`enable_status` = #{enableStatus}
            </if>
            <if test="cardCategory != null and cardCategory > 0">
                and t.`card_category` = #{cardCategory}
            </if>
            and t.`enable_status` = 1
        </where>
        order by t.id desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>
    <select id="getByEnableStatus" resultType="com.crm.rely.backend.core.dto.ucard.CardTypeDto">
        SELECT
            t.`id`,
            t.card_type_id,
            t.`type`,
            COALESCE(c.card_name, t.card_name) as card_name,
            COALESCE(c.card_desc, t.card_desc) as card_desc,
            t.`status`,
            t.`enable_status`,
            t.card_category,
            t.organization,
            t.country,
            COALESCE(c.bank_card_bin, t.bank_card_bin) as bank_card_bin,
            t.card_price,
            t.card_price_currency,
            t.risk,
            t.support,
            t.support_holder_regin,
            t.support_holder_area_code,
            t.need_card_holder,
            t.need_deposit_for_active_card,
            t.deposit_amount_min_quota_for_active_card,
            t.deposit_amount_max_quota_for_active_card,
            t.fiat_currency,
            t.max_count,
            t.recharge_currency,
            COALESCE(c.recharge_min_quota, t.recharge_min_quota) as recharge_min_quota,
            COALESCE(c.recharge_max_quota, t.recharge_max_quota) as recharge_max_quota,
            COALESCE(c.recharge_digital, t.recharge_digital) as recharge_digital,
            COALESCE(c.recharge_fee_rate, t.recharge_fee_rate) as recharge_fee_rate,
            COALESCE(c.recharge_fixed_fee, t.recharge_fixed_fee) as recharge_fixed_fee,
            t.enable_active_card,
            t.enable_deposit,
            t.enable_freeze,
            t.enable_un_freeze,
            t.card_holder_max_card_limit,
            t.card_holder_model,
            t.support_setting_no_pin_payment_amount,
            t.default_no_pin_payment_amount,
            t.no_pin_payment_amount_min_quota,
            t.no_pin_payment_amount_max_quota,
            c.is_contact,
            c.card_fee,
            c.original_card_fee
        FROM ucard_type t
        LEFT JOIN ucard_type_config c ON t.card_type_id = c.card_type_id
        where t.`enable_status` = 1
        order by t.`id` desc
    </select>
    <select id="getByCardTypeId" resultType="com.crm.rely.backend.core.dto.ucard.CardTypeDto">
        SELECT
            t.`id`,
            t.card_type_id,
            t.`type`,
            COALESCE(c.card_name, t.card_name) as card_name,
            COALESCE(c.card_desc, t.card_desc) as card_desc,
            t.`status`,
            t.`enable_status`,
            t.card_category,
            t.organization,
            t.country,
            COALESCE(c.bank_card_bin, t.bank_card_bin) as bank_card_bin,
            t.card_price,
            t.card_price_currency,
            t.risk,
            t.support,
            t.support_holder_regin,
            t.support_holder_area_code,
            t.need_card_holder,
            t.need_deposit_for_active_card,
            t.deposit_amount_min_quota_for_active_card,
            t.deposit_amount_max_quota_for_active_card,
            t.fiat_currency,
            t.max_count,
            t.recharge_currency,
            COALESCE(c.recharge_min_quota, t.recharge_min_quota) as recharge_min_quota,
            COALESCE(c.recharge_max_quota, t.recharge_max_quota) as recharge_max_quota,
            COALESCE(c.recharge_digital, t.recharge_digital) as recharge_digital,
            COALESCE(c.recharge_fee_rate, t.recharge_fee_rate) as recharge_fee_rate,
            COALESCE(c.recharge_fixed_fee, t.recharge_fixed_fee) as recharge_fixed_fee,
            t.enable_active_card,
            t.enable_deposit,
            t.enable_freeze,
            t.enable_un_freeze,
            t.card_holder_max_card_limit,
            t.card_holder_model,
            t.support_setting_no_pin_payment_amount,
            t.default_no_pin_payment_amount,
            t.no_pin_payment_amount_min_quota,
            t.no_pin_payment_amount_max_quota,
            c.is_contact,
            c.card_fee,
            c.original_card_fee
        FROM ucard_type t
        LEFT JOIN ucard_type_config c ON t.card_type_id = c.card_type_id
        where t.`enable_status` = 1 and t.card_type_id = #{cardTypeId}
        order by t.`id` desc
    </select>

</mapper> 