package com.hfkj.dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * mapper扩展类
 */
public interface BsTradeSettleMapperExt {

    @Select("<script>" +
            "  select   " +
            "  a.settleDay,  " +
            " #{param.merNo} merNo,   " +
            " (a.tradeNum + b.tradeNum) tradeNum,  " +
            " (a.tradeAmount - b.tradeAmount) tradeAmount,   " +
            " (a.settleAmount - b.settleAmount) settleAmount,   " +
            " (a.shdFeeAmount - b.shdFeeAmount) shdFeeAmount   " +
            " from   " +
            " (select    " +
            " a.days settleDay,  " +
            " case when b.tradeNum is null then 0 else b.tradeNum end tradeNum,    " +
            " case when b.tradeAmount is null then 0 else b.tradeAmount end tradeAmount,    " +
            " case when b.settleAmount is null then 0 else b.settleAmount end settleAmount,    " +
            " case when b.shdFeeAmount is null then 0 else b.shdFeeAmount end shdFeeAmount   " +
            " from (   " +
            " SELECT date_add(date_sub(#{param.settleTimeS},interval 1 day), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) day) days    " +
            " FROM mysql.help_topic    " +
            " WHERE help_topic_id <![CDATA[ < ]]>  DATEDIFF(#{param.settleTimeE},date_sub(#{param.settleTimeS},interval 1 day)) ORDER BY help_topic_id) a   " +
            " LEFT JOIN (select   " +
            " settle_date,   " +
            " count(1) tradeNum,   " +
            " sum(trade_amount) tradeAmount,   " +
            " sum(settle_amount) settleAmount,   " +
            " sum(shd_fee_amt) shdFeeAmount   " +
            " from bs_trade_settle   " +
            " where settle_date between #{param.settleTimeS} and #{param.settleTimeE}  " +
            " and mer_no = #{param.merNo}  " +
            " and trade_type = 1  " +
            " and `status` = 1  " +
            " GROUP BY settle_date  " +
            " ) b on b.settle_date = a.days) a,    " +
            " (select    " +
            " a.days settleDay,  " +
            " case when b.tradeNum is null then 0 else b.tradeNum end tradeNum,    " +
            " case when b.tradeAmount is null then 0 else b.tradeAmount end tradeAmount,    " +
            " case when b.settleAmount is null then 0 else b.settleAmount end settleAmount,    " +
            " case when b.shdFeeAmount is null then 0 else b.shdFeeAmount end shdFeeAmount   " +
            " from (   " +
            " SELECT date_add(date_sub(#{param.settleTimeS},interval 1 day), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) day) days    " +
            " FROM mysql.help_topic    " +
            " WHERE help_topic_id <![CDATA[ < ]]>  DATEDIFF(#{param.settleTimeE},date_sub(#{param.settleTimeS},interval 1 day)) ORDER BY help_topic_id) a   " +
            " LEFT JOIN (select   " +
            " settle_date,   " +
            " count(1) tradeNum,   " +
            " sum(trade_amount) tradeAmount,   " +
            " sum(settle_amount) settleAmount,   " +
            " sum(shd_fee_amt) shdFeeAmount   " +
            " from bs_trade_settle   " +
            " where settle_date between #{param.settleTimeS} and #{param.settleTimeE} " +
            " and mer_no = #{param.merNo}  " +
            " and trade_type = 2  " +
            " and `status` = 1  " +
            " GROUP BY settle_date  " +
            " ) b on b.settle_date = a.days)  b where b.settleDay = a.settleDay  " +
            " ORDER BY a.settleDay desc" +
            "</script>")
    List<Map<String,Object>> queryTradeSettleDayCount(@Param("param") Map<String, Object> param);

    @Select({"<script>" +
            " select " +
            " sum(a.tradeNum) tradeNum," +
            " sum(CAST(a.tradeAmount as decimal(10,2))) tradeAmount, " +
            " sum(CAST(a.settleAmount as decimal(10,2))) settleAmount, " +
            " sum(CAST(a.shdFeeAmount as decimal(10,2))) shdFeeAmount  " +
            "  from (select " +
            " count(1) tradeNum," +
            " case when sum(trade_amount) is null then 0 else sum(trade_amount) end tradeAmount," +
            " case when sum(settle_amount) is null then 0 else sum(settle_amount) end settleAmount," +
            " case when sum(shd_fee_amt) is null then 0 else sum(shd_fee_amt) end shdFeeAmount" +
            " from bs_trade_settle " +
            " where mer_no = #{param.merNo} and settle_date between #{param.settleTimeS} and #{param.settleTimeE} and trade_type = 1 and status = 1" +
            " <if test='param.outTradeNo != null'> and out_trade_no = #{param.outTradeNo} </if>" +
            " <if test='param.accTradeNo != null'> and acc_trade_no = #{param.accTradeNo} </if>" +
            " UNION" +
            " select " +
            " count(1) tradeNum," +
            "  CONCAT('-', case when sum(trade_amount) is null then 0 else sum(trade_amount) end) tradeAmount," +
            "  CONCAT('-', case when sum(settle_amount) is null then 0 else sum(settle_amount) end) settleAmount," +
            "  CONCAT('-', case when sum(shd_fee_amt) is null then 0 else sum(shd_fee_amt) end) shdFeeAmount" +
            " from bs_trade_settle " +
            " where mer_no = #{param.merNo} and settle_date between #{param.settleTimeS} and #{param.settleTimeE} and trade_type = 2 and status = 1" +
            " <if test='param.outTradeNo != null'> and out_trade_no = #{param.outTradeNo} </if>" +
            " <if test='param.accTradeNo != null'> and acc_trade_no = #{param.accTradeNo} </if>" +
            ") a" +
            " </script>"})
    Map<String,Object> queryTradeSettleCount(@Param("param") Map<String, Object> param);
}
