<?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.UcardCardMapper">

    <select id="countList" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardPageEntity">
        SELECT COUNT(*) FROM ucard_card c
        LEFT JOIN ucard_type b ON c.card_type_id = b.card_type_id
        LEFT JOIN ucard_type_config t ON t.card_type_id = b.card_type_id
        LEFT JOIN ucard_merchant_user uc ON c.unique_id = uc.unique_id
        <where>
            <if test="cardNumber!=null and cardNumber!=''">
                and c.`card_number` like concat(#{cardNumber},'%')
            </if>
            <if test="status != null and status != ''">
                and c.`status` = #{status}
            </if>
            <if test="freezeStatus != null and freezeStatus > 0">
                <choose>
                    <when test="freezeStatus == 1">
                        and c.`freeze_type` = 1 and c.`freeze_status` = 'success'
                    </when>
                    <when test="freezeStatus == 2">
                        and c.`freeze_type` = 2 and (c.`freeze_status` = 'wait_process' or c.`freeze_status` = 'processing')
                    </when>
                    <when test="freezeStatus == 3">
                        and c.`freeze_type` = 2 and c.`freeze_status` = 'success'
                    </when>
                    <when test="freezeStatus == 4">
                        and c.`freeze_type` = 2 and c.`freeze_status` = 'fail'
                    </when>
                    <when test="freezeStatus == 5">
                        and c.`freeze_type` = 1 and (c.`freeze_status` = 'wait_process' or c.`freeze_status` = 'processing')
                    </when>
                    <when test="freezeStatus == 6">
                        and c.`freeze_type` = 1 and c.`freeze_status` = 'fail'
                    </when>
                </choose>
            </if>
            <if test="type != null and type != ''">
                and b.`type` = #{type}
            </if>
            <if test="email!=null and email!=''">
                and uc.`email` like concat(#{email},'%')
            </if>
            <if test="mobile!=null and mobile!=''">
                and uc.`mobile` like concat(#{mobile},'%')
            </if>
            <if test="cId != null and cId > 0">
                and uc.`c_id` = #{cId}
            </if>
            <if test="isDisplayCancel != null and isDisplayCancel != ''">
                and c.`cancel_status` is null
            </if>
            <if test="cancelStatus != null and cancelStatus != ''">
                and c.`cancel_status` = #{cancelStatus}
            </if>
            <if test="blockedStatus != null and blockedStatus > 0">
                <choose>
                    <when test="blockedStatus == 1">
                        and c.`blocked` = 0
                    </when>
                    <when test="blockedStatus == 2">
                        and c.`blocked` = 1
                    </when>
                </choose>
            </if>
        </where>
    </select>

    <select id="pageList" resultType="com.crm.rely.backend.core.dto.ucard.UcardCardDto"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardPageEntity">
        SELECT
        c.`id`,
        c.card_no,
        c.card_type_id,
        c.expire_date,
        c.unique_id,
        c.`status`,
        c.`cancel_status`,
        c.`logistics_status`,
        c.blocked,
        c.freeze_type,
        c.freeze_status,
        c.merchant_order_no,
        c.card_number,
        c.activate_url,
        b.organization as organization,
        b.`type`,
        COALESCE(t.card_name, b.card_name) as card_name,
        b.recharge_currency,
        COALESCE(t.recharge_min_quota, b.recharge_min_quota) as recharge_min_quota,
        COALESCE(t.recharge_max_quota, b.recharge_max_quota) as recharge_max_quota,
        COALESCE(t.recharge_digital, b.recharge_digital) as recharge_digital,
        COALESCE(t.recharge_fee_rate, b.recharge_fee_rate) as recharge_fee_rate,
        COALESCE(t.recharge_fixed_fee, b.recharge_fixed_fee) as recharge_fixed_fee,
        uc.c_id,
        uc.area_code,
        uc.mobile,
        uc.email,
        uc.last_name,
        uc.first_name,
        uc.country
        FROM ucard_card c
        LEFT JOIN ucard_type b ON c.card_type_id = b.card_type_id
        LEFT JOIN ucard_type_config t ON t.card_type_id = b.card_type_id
        LEFT JOIN ucard_merchant_user uc ON c.unique_id = uc.unique_id
        <where>
            <if test="cardNumber!=null and cardNumber!=''">
                and c.`card_number` like concat(#{cardNumber},'%')
            </if>
            <if test="status != null and status != ''">
                and c.`status` = #{status}
            </if>
            <if test="freezeStatus != null and freezeStatus > 0">
                <choose>
                    <when test="freezeStatus == 1">
                        and c.`freeze_type` = 1 and c.`freeze_status` = 'success'
                    </when>
                    <when test="freezeStatus == 2">
                        and c.`freeze_type` = 2 and (c.`freeze_status` = 'wait_process' or c.`freeze_status` = 'processing')
                    </when>
                    <when test="freezeStatus == 3">
                        and c.`freeze_type` = 2 and c.`freeze_status` = 'success'
                    </when>
                    <when test="freezeStatus == 4">
                        and c.`freeze_type` = 2 and c.`freeze_status` = 'fail'
                    </when>
                    <when test="freezeStatus == 5">
                        and c.`freeze_type` = 1 and (c.`freeze_status` = 'wait_process' or c.`freeze_status` = 'processing')
                    </when>
                    <when test="freezeStatus == 6">
                        and c.`freeze_type` = 1 and c.`freeze_status` = 'fail'
                    </when>
                </choose>
            </if>
            <if test="type != null and type != ''">
                and b.`type` = #{type}
            </if>
            <if test="email!=null and email!=''">
                and uc.`email` like concat(#{email},'%')
            </if>
            <if test="mobile!=null and mobile!=''">
                and uc.`mobile` like concat(#{mobile},'%')
            </if>
            <if test="cId != null and cId > 0">
                and uc.`c_id` = #{cId}
            </if>
            <if test="isDisplayCancel != null and isDisplayCancel != ''">
                and c.`cancel_status` is null
            </if>
            <if test="cancelStatus != null and cancelStatus != ''">
                and c.`cancel_status` = #{cancelStatus}
            </if>
            <if test="blockedStatus != null and blockedStatus > 0">
                <choose>
                    <when test="blockedStatus == 1">
                        and c.`blocked` = 0
                    </when>
                    <when test="blockedStatus == 2">
                        and c.`blocked` = 1
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY c.id DESC
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>
    <select id="cardInfoQuery" resultType="com.crm.rely.backend.core.dto.ucard.CardInfoQueryDto"
    parameterType="com.crm.rely.backend.core.entity.ucard.CardInfoQueryEntity">
        select
            uc.`id`,
            uc.`card_no`,
            uc.`unique_id`,
            uc.`card_number`,
            uc. `expire_date`,
            uc. `status`,
            uc. `cancel_status`,
            uc.`logistics_status`,
            uc. `blocked`,
            uc. `freeze_status`,
            uc. `freeze_type`,
            um.`area_code`,
            um.`mobile`,
            um.`email`,
            um.`last_name`,
            um.`first_name`,
            um.`gender`,
            co.`en_name` as country_en_name,
            um.`address`,
            um.`address_cn`,
            ut.`type`,
            COALESCE(sc.name, co.en_name) as country_cn_name,
            cou.`en_name` as town_en_name
        from ucard_card uc
        left join ucard_merchant_user um on uc.unique_id = um.unique_id
        left join ucard_type ut on uc.card_type_id = ut.card_type_id
        left join (
            select code, en_name, card_category
            from ucard_country
            group by code, en_name, card_category
        ) co on um.country = co.code and co.card_category = 2
        left join (
            select code, en_name, card_category
            from ucard_country
            group by code, en_name, card_category
        ) cou on um.town = cou.code and cou.card_category = 2
        left join sys_country sc on co.code = sc.code
        where uc.card_number = #{cardNumber}
    </select>
    <select id="findAllByUniqueId" resultType="com.crm.rely.backend.core.pojo.table.ucard.UcardCardTable">
        select * from ucard_card where unique_id = #{uniqueId} and `status` = 'success' and (cancel_status is null or cancel_status = 'fail')
        and blocked = 0 and freeze_status = 'success' and freeze_type = 1
    </select>

</mapper> 