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

    <select id="countList" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.ucard.GlobalOrderPageEntity">
        SELECT COUNT(*) FROM ucard_global_order g
        LEFT JOIN ucard_merchant_user uc ON g.unique_id = uc.unique_id
        <where>
            <if test="merchantOrderNo != null and merchantOrderNo != ''">
                and g.`merchant_order_no` = #{merchantOrderNo}
            </if>
            <if test="deductionAccountType != null and deductionAccountType != ''">
                and g.`deduction_account_type` = #{deductionAccountType}
            </if>
            <if test="deductionAccountStatus != null and deductionAccountStatus != ''">
                and g.`deduction_account_status` = #{deductionAccountStatus}
            </if>
            <choose>
                <when test="status == 'wait_process'">
                    and (g.status = 'wait_process' or g.status is null)
                </when>
                <when test="status != null and status != ''">
                    and g.status = #{status}
                </when>
            </choose>
            <if test="complianceStatus != null and complianceStatus != ''">
                and g.`compliance_status` = #{complianceStatus}
            </if>
            <if test="payoutCurrency != null and payoutCurrency != ''">
                and g.`payout_currency` = #{payoutCurrency}
            </if>
            <if test="transferType != null and transferType != ''">
                and g.`transfer_type` = #{transferType}
            </if>
            <if test="payoutMethod != null and payoutMethod != ''">
                and g.`payout_method` = #{payoutMethod}
            </if>
            <if test="approveStatus != null and approveStatus != ''">
                and g.`approve_status` = #{approveStatus}
            </if>
            <if test="startDate != null">
                and g.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and g.add_time <![CDATA[ < ]]> #{endDate}
            </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 uc.`c_id` = #{cId}
            </if>
        </where>
    </select>

    <select id="pageList" resultType="com.crm.rely.backend.core.dto.ucard.GlobalOrderDto"
            parameterType="com.crm.rely.backend.core.entity.ucard.GlobalOrderPageEntity">
        select g.*,
               uc.c_id,
               uc.area_code,
               uc.mobile,
               uc.email,
               uc.last_name,
               uc.first_name,
               uc.country,
               rr.content as approve_desc_cn
        FROM ucard_global_order g
        LEFT JOIN ucard_merchant_user uc ON g.unique_id = uc.unique_id
        LEFT JOIN sys_reasons_refusal rr ON rr.id = g.approve_desc
        <where>
            <if test="merchantOrderNo != null and merchantOrderNo != ''">
                and g.`merchant_order_no` = #{merchantOrderNo}
            </if>
            <if test="deductionAccountType != null and deductionAccountType != ''">
                and g.`deduction_account_type` = #{deductionAccountType}
            </if>
            <if test="deductionAccountStatus != null and deductionAccountStatus != ''">
                and g.`deduction_account_status` = #{deductionAccountStatus}
            </if>
            <choose>
                <when test="status == 'wait_process'">
                    and (g.status = 'wait_process' or g.status is null)
                </when>
                <when test="status != null and status != ''">
                    and g.status = #{status}
                </when>
            </choose>
            <if test="complianceStatus != null and complianceStatus != ''">
                and g.`compliance_status` = #{complianceStatus}
            </if>
            <if test="payoutCurrency != null and payoutCurrency != ''">
                and g.`payout_currency` = #{payoutCurrency}
            </if>
            <if test="transferType != null and transferType != ''">
                and g.`transfer_type` = #{transferType}
            </if>
            <if test="payoutMethod != null and payoutMethod != ''">
                and g.`payout_method` = #{payoutMethod}
            </if>
            <if test="approveStatus != null and approveStatus != ''">
                and g.`approve_status` = #{approveStatus}
            </if>
            <if test="startDate != null">
                and g.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and g.add_time <![CDATA[ < ]]> #{endDate}
            </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 uc.`c_id` = #{cId}
            </if>
        </where>
        order by g.id desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>
    <select id="findAllByCId" resultType="java.math.BigDecimal">
        select coalesce(sum(deduction_amount), 0)
        from ucard_global_order
        where c_id = #{cId} and (status not in ('fail', 'cancel') or status is null)
        and approve_status != 3
        and payout_currency = #{payoutCurrency} and transfer_type = #{transferType}
        and payout_method = #{payoutMethod}
    </select>

</mapper> 