<?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.czg.service.order.mapper.CreditBuyerMapper">

    <sql id="view_credit_buyer_order_count">
        (SELECT
        tb.credit_buyer_id AS credit_buyer_id,
        tb.status AS status,
        count( 0 ) AS count
        FROM
        <include refid="view_credit_buyer_order"/>
        tb
        GROUP BY tb.credit_buyer_id,tb.status)
    </sql>

    <sql id="view_credit_buyer_order">
        (SELECT t1.id                                               AS id,
                t1.credit_buyer_id                                  AS credit_buyer_id,
                t1.order_id                                         AS order_id,
                t2.pay_amount - t2.refund_amount                    AS pay_amount,
                t1.paid_amount                                      AS paid_amount,
                (t2.pay_amount - t2.refund_amount) - t1.paid_amount AS unpaid_amount,
                t1.status                                           AS status,
                t2.create_time                                      AS create_time,
                t1.last_payment_time                                AS last_payment_time,
                t1.last_payment_method                              AS last_payment_method,
                t1.remark                                           AS remark
         FROM tb_credit_buyer_order t1
                  LEFT JOIN tb_order_info t2 ON t1.order_id = t2.id)
    </sql>

    <select id="findCreditBuyerList" resultType="com.czg.order.dto.CreditBuyerDTO">
        select x1.*,
        x2.shop_name,
        ifnull(x3.owed_amount, 0 ) as owed_amount,
        ifnull(x3.accumulate_amount,0) as accumulate_amount
        from tb_credit_buyer x1
        left join tb_shop_info x2 on x1.shop_id = x2.id
        left join (select t.credit_buyer_id,ifnull(sum(t.unpaid_amount),0) as owed_amount,ifnull(sum(t.pay_amount),0) as
        accumulate_amount from
        <include refid="view_credit_buyer_order"/>
        t
        group by t.credit_buyer_id) x3 on x1.id = x3.credit_buyer_id
        <where>
            and x1.is_del = 0
            and x1.shop_id = #{shopId}
            <if test="id !=null and id != ''">
                and x1.id = #{id}
            </if>
            <if test="responsiblePerson !=null and responsiblePerson != ''">
                and x1.responsible_person like concat('%',#{responsiblePerson},'%')
            </if>
            <if test="status !=null">
                and x1.status = #{status}
            </if>
            <if test="keywords !=null and keywords != ''">
                and (x1.debtor like concat('%',#{keywords},'%') or x1.mobile like concat('%',#{keywords},'%'))
            </if>
            <if test="repaymentStatus !=null and repaymentStatus != ''">
                <if test="repaymentStatus == 'unpaid'">
                    and 0 &lt; ifnull((select x.count from <include refid="view_credit_buyer_order_count"/> x where x.credit_buyer_id = x1.id and x.status = 'unpaid'),0)
                    and 0 = ifnull((select x.count from <include refid="view_credit_buyer_order_count"/> x where x.credit_buyer_id = x1.id and x.status = 'partial'),0)
                </if>
                <if test="repaymentStatus == 'partial'">
                    and 0 &lt; ifnull((select x.count from <include refid="view_credit_buyer_order_count"/> x where x.credit_buyer_id = x1.id and x.status = 'partial'),0)
                </if>
                <if test="repaymentStatus == 'paid'">
                    and 0 = ifnull((select sum(x.count) from <include refid="view_credit_buyer_order_count"/> x where x.credit_buyer_id = x1.id and x.status in ('unpaid','partial')),0)
                </if>
            </if>
        </where>
        order by x1.status desc,x1.id desc
    </select>

</mapper>