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

    <select id="countList" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardRechargePageEntity">
        SELECT COUNT(*)
        FROM ucard_recharge_order r
        LEFT JOIN ucard_card ua on r.card_no = ua.card_no
        LEFT JOIN ucard_type b on ua.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 r.unique_id = uc.unique_id
        left join sys_reasons_refusal rr on rr.id = r.approve_desc
        <where>
            <if test="uniqueId!=null and uniqueId!=''">
                and r.`unique_id` = #{uniqueId}
            </if>
            <if test="cardNumber!=null and cardNumber!=''">
                and ua.`card_number` like concat(#{cardNumber},'%')
            </if>
            <if test="currency!=null and currency!=''">
                and r.`currency` = #{currency}
            </if>
            <if test="status != null and status != ''">
                and r.`status` = #{status}
            </if>
            <if test="startDate != null">
                and r.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and r.add_time <![CDATA[ < ]]> #{endDate}
            </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="approveStatus != null and approveStatus > 0">
                and r.`approve_status` = #{approveStatus}
            </if>
        </where>
    </select>

    <select id="pageList" resultType="com.crm.rely.backend.core.dto.ucard.CardRechargeOrderDto"
            parameterType="com.crm.rely.backend.core.entity.ucard.CardRechargePageEntity">
        SELECT r.*,b.`type`,COALESCE(t.card_name, b.card_name) as card_name,uc.c_id,uc.area_code,uc.mobile,uc.email ,uc.country,uc.last_name,uc.first_name,
               ua.card_number,rr.content as approve_desc_cn
        FROM ucard_recharge_order r
        LEFT JOIN ucard_card ua on r.card_no = ua.card_no
        LEFT JOIN ucard_type b on ua.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 r.unique_id = uc.unique_id
        left join sys_reasons_refusal rr on rr.id = r.approve_desc
        <where>
            <if test="uniqueId!=null and uniqueId!=''">
                and r.`unique_id` = #{uniqueId}
            </if>
            <if test="cardNumber!=null and cardNumber!=''">
                and ua.`card_number` like concat(#{cardNumber},'%')
            </if>
            <if test="currency!=null and currency!=''">
                and r.`currency` = #{currency}
            </if>
            <if test="status != null and status != ''">
                and r.`status` = #{status}
            </if>
            <if test="startDate != null">
                and r.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and r.add_time <![CDATA[ < ]]> #{endDate}
            </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="approveStatus != null and approveStatus > 0">
                and r.`approve_status` = #{approveStatus}
            </if>
        </where>
        order by r.id desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>
    <select id="sumPage" resultType="com.crm.rely.backend.core.dto.ucard.CardRechargeOrderSumDto">
        SELECT IFNULL(sum(r.amount),0) amount,IFNULL(sum(r.fee),0) fee
        FROM ucard_recharge_order r
        LEFT JOIN ucard_card ua on r.card_no = ua.card_no
        LEFT JOIN ucard_type b on ua.card_type_id = b.card_type_id
        LEFT JOIN ucard_merchant_user uc ON r.unique_id = uc.unique_id
        <where>
            <if test="uniqueId!=null and uniqueId!=''">
                and r.`unique_id` = #{uniqueId}
            </if>
            <if test="cardNumber!=null and cardNumber!=''">
                and ua.`card_number` like concat(#{cardNumber},'%')
            </if>
            <if test="currency!=null and currency!=''">
                and r.`currency` = #{currency}
            </if>
            <if test="status != null and status != ''">
                and r.`status` = #{status}
            </if>
            <if test="startDate != null">
                and r.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and r.add_time <![CDATA[ < ]]> #{endDate}
            </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="approveStatus != null and approveStatus > 0">
                and r.`approve_status` = #{approveStatus}
            </if>
        </where>
    </select>

</mapper> 