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

    <select id="countList" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardApplyPageEntity">
        SELECT COUNT(*)
        FROM
        ucard_apply a
        LEFT JOIN ucard_type b ON a.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 a.unique_id = uc.unique_id
        left join sys_reasons_refusal rr on rr.id = a.approve_desc
        left join (
        select code, en_name, card_category
        from ucard_country
        group by code, en_name, card_category
        ) u
        on a.mailing_country = u.code and u.card_category = b.card_category
        left join (
        select code, en_name, card_category
        from ucard_country
        group by code, en_name, card_category
        ) uu
        on a.mailing_town = uu.code and uu.card_category = b.card_category
        <where>
            <if test="cardNo!=null and cardNo!=''">
                and a.`card_no` like concat(#{cardNo},'%')
            </if>
            <choose>
                <when test="status == 'wait_process'">
                    and (a.status = 'wait_process' or a.status is null)
                </when>
                <when test="status != null and status != ''">
                    and a.status = #{status}
                </when>
            </choose>
            <if test="type != null and type != ''">
                and b.`type` = #{type}
            </if>
            <if test="email!=null and email!=''">
                and a.`email` like concat(#{email},'%')
            </if>
            <if test="mobile!=null and mobile!=''">
                and a.`mobile` like concat(#{mobile},'%')
            </if>
            <if test="cId!=null and cId > 0">
                and uc.`c_id` = #{cId}
            </if>
            <if test="approveStatus != null and approveStatus > 0">
                and a.`approve_status` = #{approveStatus}
            </if>
            <if test="tradeStatus != null and tradeStatus > 0">
                <choose>
                    <when test="tradeStatus == 1">
                        and a.`trade_status` = 1 and a.`trade_type` = 1
                    </when>
                    <when test="tradeStatus == 2">
                        and a.`trade_status` = 2 and a.`trade_type` = 1
                    </when>
                    <when test="tradeStatus == 3">
                        and a.`trade_status` = 3 and a.`trade_type` = 1
                    </when>
                    <when test="tradeStatus == 4">
                        and a.`trade_status` = 1 and a.`trade_type` = 2
                    </when>
                    <when test="tradeStatus == 5">
                        and a.`trade_status` = 2 and a.`trade_type` = 2
                    </when>
                    <when test="tradeStatus == 6">
                        and a.`trade_status` = 3 and a.`trade_type` = 2
                    </when>
                </choose>
            </if>
            <if test="startDate != null">
                and a.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and a.add_time <![CDATA[ < ]]> #{endDate}
            </if>
        </where>
    </select>

    <select id="pageList" resultType="com.crm.rely.backend.core.dto.ucard.CardApplyDto"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardApplyPageEntity">
        SELECT
        a.`id`,
        a.`card_no`,
        a.`serial`,
        a.`discount_type`,
        a.`login`,
        a.trade_status,
        a.trade_type,
        a.`unique_id`,
        a.area_code,
        a.`mobile`,
        a.`email`,
        a.last_name,
        a.first_name,
        a.`mailing_area_code`,
        a.`mailing_mobile`,
        a.`mailing_post_code`,
        a.`mailing_address`,
        a.`mailing_address_cn`,
        a.`merchant_order_no`,
        a.`holder_merchant_order_no`,
        a.`status`,
        a.`order_no`,
        a.`card_number`,
        a.`card_type_id`,
        a.`holder_id`,
        a.`amount`,
        a.`currency`,
        a.`fee`,
        a.`received_amount`,
        a.`received_currency`,
        a.`approve_status`,
        a.`approve_desc`,
        a.`status_flow_location`,
        a.`holder_status`,
        a.`resp_msg`,
        a.`holder_description`,
        a.`remark`,
        a.`transaction_time`,
        a.add_time,
        a.add_user,
        a.add_ip,
        a.modify_time,
        a.modify_user,
        a.modify_ip,
        a.note,
        b.`card_category`,
        b.`type`,
        COALESCE(t.card_name, b.card_name) as card_name,
        b.organization AS organization,
        uc.c_id,
        uc.country,
        u.en_name as mailing_country,
        uu.en_name as mailing_town,
        rr.content as approve_desc_cn
        FROM
        ucard_apply a
        LEFT JOIN ucard_type b ON a.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 a.unique_id = uc.unique_id
        left join sys_reasons_refusal rr on rr.id = a.approve_desc
        left join (
        select code, en_name, card_category
        from ucard_country
        group by code, en_name, card_category
        ) u
        on a.mailing_country = u.code and u.card_category = b.card_category
        left join (
        select code, en_name, card_category
        from ucard_country
        group by code, en_name, card_category
        ) uu
        on a.mailing_town = uu.code and uu.card_category = b.card_category
        <where>
            <if test="cardNo!=null and cardNo!=''">
                and a.`card_no` like concat(#{cardNo},'%')
            </if>
            <choose>
                <when test="status == 'wait_process'">
                    and (a.status = 'wait_process' or a.status is null)
                </when>
                <when test="status != null and status != ''">
                    and a.status = #{status}
                </when>
            </choose>
            <if test="type != null and type != ''">
                and b.`type` = #{type}
            </if>
            <if test="email!=null and email!=''">
                and a.`email` like concat(#{email},'%')
            </if>
            <if test="mobile!=null and mobile!=''">
                and a.`mobile` like concat(#{mobile},'%')
            </if>
            <if test="cId!=null and cId > 0">
                and uc.`c_id` = #{cId}
            </if>
            <if test="approveStatus != null and approveStatus > 0">
                and a.`approve_status` = #{approveStatus}
            </if>
            <if test="tradeStatus != null and tradeStatus > 0">
                <choose>
                    <when test="tradeStatus == 1">
                        and a.`trade_status` = 1 and a.`trade_type` = 1
                    </when>
                    <when test="tradeStatus == 2">
                        and a.`trade_status` = 2 and a.`trade_type` = 1
                    </when>
                    <when test="tradeStatus == 3">
                        and a.`trade_status` = 3 and a.`trade_type` = 1
                    </when>
                    <when test="tradeStatus == 4">
                        and a.`trade_status` = 1 and a.`trade_type` = 2
                    </when>
                    <when test="tradeStatus == 5">
                        and a.`trade_status` = 2 and a.`trade_type` = 2
                    </when>
                    <when test="tradeStatus == 6">
                        and a.`trade_status` = 3 and a.`trade_type` = 2
                    </when>
                </choose>
            </if>
            <if test="startDate != null">
                and a.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and a.add_time <![CDATA[ < ]]> #{endDate}
            </if>
        </where>
        ORDER BY a.id DESC
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="findByUniqueIdAndCardTypeId" resultType="com.crm.rely.backend.core.pojo.table.ucard.CardApplyTable">
        SELECT * FROM ucard_apply
        WHERE card_type_id = #{cardTypeId}
        AND unique_id = #{uniqueId}
        AND `status` != 'cancel'
        AND NOT (trade_type = 2 AND trade_status = 2)
        ORDER BY
            CASE
            WHEN trade_status IN (1, 3) THEN 1
            WHEN trade_type = 1 AND trade_status = 2 THEN 2
            ELSE 3
            END,
        add_time DESC
        LIMIT 1;
    </select>

    <select id="findById" resultType="com.crm.rely.backend.core.dto.ucard.CardApplyDto"
            parameterType="com.crm.rely.backend.core.entity.base.BaseEntity">
        SELECT
            a.`id`,
            a.`card_no`,
            a.`serial`,
            a.`discount_type`,
            a.`login`,
            a.trade_status,
            a.trade_type,
            a.`unique_id`,
            a.last_name,
            a.first_name,
            a.`area_code`,
            a.`mobile`,
            a.`email`,
            a.mailing_area_code,
            a.mailing_mobile,
            a.mailing_post_code,
            a.mailing_address,
            a.mailing_address_cn,
            a.`merchant_order_no`,
            a.`holder_merchant_order_no`,
            a.`status`,
            a.`order_no`,
            a.`card_number`,
            a.`card_type_id`,
            a.`holder_id`,
            a.`amount`,
            a.`currency`,
            a.`fee`,
            a.`received_amount`,
            a.`received_currency`,
            a.`approve_status`,
            a.`approve_desc`,
            a.`status_flow_location`,
            a.`holder_status`,
            a.`resp_msg`,
            a.`holder_description`,
            a.`remark`,
            a.`transaction_time`,
            a.add_time,
            a.add_user,
            a.add_ip,
            a.modify_time,
            a.modify_user,
            a.modify_ip,
            a.note,
            b.`card_category`,
            b.`type`,
            COALESCE(t.card_name, b.card_name) as card_name,
            uc.c_id,
            u.en_name AS mailing_country,
            d.en_name AS mailing_town
        FROM
            ucard_apply a
        LEFT JOIN ucard_type b ON a.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 a.unique_id = uc.unique_id
        LEFT JOIN (
            SELECT code, en_name, card_category
            FROM ucard_country
            GROUP BY code, en_name, card_category
        ) u
        ON a.mailing_country = u.code AND u.card_category = 2
        LEFT JOIN (
            SELECT code, en_name, card_category
            FROM ucard_country
            GROUP BY code, en_name, card_category
        ) d
        ON a.mailing_town = d.code AND d.card_category = 2
        WHERE a.id = #{id}
    </select>
    <select id="cardNumberVerify" resultType="com.crm.rely.backend.core.pojo.table.ucard.CardApplyTable">
        SELECT * FROM ucard_apply WHERE card_number = #{cardNumber}
         AND (`status` != 'fail' OR (`status` IS NULL AND (holder_status != 'reject' or holder_id IS NOT NULL)))
    </select>
    <select id="findByUniqueIdAndHolderIdNotNullAndHolderStatusIsReject" resultType="com.crm.rely.backend.core.pojo.table.ucard.CardApplyTable">
        SELECT
            *
        FROM
            ucard_apply
        WHERE
            unique_id = #{uniqueId}
          AND holder_status = 'reject'
          AND holder_id IS NOT NULL
        ORDER BY add_time DESC LIMIT 1
    </select>
    <select id="queryAllUsedCardNumber" resultType="java.lang.String">
        SELECT card_number
        FROM ucard_apply
        WHERE card_number IS NOT NULL
          AND card_number != ''
          AND `status` IN ('success', 'processing', 'wait_process')
        GROUP BY card_number;
    </select>

</mapper>