package com.hfkj.dao;

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

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * mapper扩展类
 */
public interface BsGasOrderSpreadMapperExt {
    
    @Select(" select " +
            " DATE_SUB(CURDATE(), INTERVAL 1 DAY) settleDate," +
            " case when sum(totalAmount) is not null then sum(totalAmount) else 0 end settleAmount" +
            " from (" +
            " select " +
            " sum(b.gains_rebate_amount) totalAmount" +
            " from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            " WHERE DATE_FORMAT(a.pay_time, '%Y-%m-%d') = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and a.`status` = 2 and b.`status` = 1 and b.gains_rebate_user_id = #{param.userId}" +
            " union all" +
            " select " +
            " sum(b.gains_rebate_one_amount) totalAmount" +
            " from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            " WHERE DATE_FORMAT(a.pay_time, '%Y-%m-%d') = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and a.`status` = 2 and b.`status` = 1 and b.gains_rebate_one_user_id = #{param.userId}" +
            " union all" +
            " select " +
            " sum(b.gains_rebate_two_amount) totalAmount" +
            " from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            " WHERE DATE_FORMAT(a.pay_time, '%Y-%m-%d') = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and a.`status` = 2 and b.`status` = 1 and b.gains_rebate_two_user_id = #{param.userId}" +
            " ) a")
    HashMap<String,Object> querySettleOrderList(@Param("param") Map<String,Object> param);


    @Select("<script>" +
            " select " +
            "  case when sum(totalAmount) is not null then sum(totalAmount) else 0 end" +
            "  from (" +
            "  select sum(b.gains_rebate_amount) totalAmount" +
            "  from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            "  WHERE a.`status` in (${param.status})" +
            " <if test='param.userId != null'> and b.gains_rebate_user_id = #{param.userId} </if>" +
            " <if test='param.payTimeS != null'> <![CDATA[ and a.pay_time >= #{param.payTimeS} ]]> </if>" +
            " <if test='param.payTimeE != null'> <![CDATA[ and a.pay_time <= #{param.payTimeE} ]]> </if>" +
            "  union all" +
            "  select sum(b.gains_rebate_one_amount) totalAmount" +
            "  from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            "  WHERE a.`status` in (${param.status})" +
            " <if test='param.userId != null'> and b.gains_rebate_one_user_id = #{param.userId} </if>" +
            " <if test='param.payTimeS != null'> <![CDATA[ and a.pay_time >= #{param.payTimeS} ]]> </if>" +
            " <if test='param.payTimeE != null'> <![CDATA[ and a.pay_time <= #{param.payTimeE} ]]> </if>" +
            "  union all" +
            "  select sum(b.gains_rebate_two_amount) totalAmount" +
            "  from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            "  WHERE a.`status` in (${param.status})" +
            " <if test='param.userId != null'> and b.gains_rebate_two_user_id = #{param.userId} </if>" +
            " <if test='param.payTimeS != null'> <![CDATA[ and a.pay_time >= #{param.payTimeS} ]]> </if>" +
            " <if test='param.payTimeE != null'> <![CDATA[ and a.pay_time <= #{param.payTimeE} ]]> </if>" +
            "  ) a" +
            "</script>")
    BigDecimal countSettleOrderPrice(@Param("param") Map<String,Object> param);

    @Select("<script>" +
            " select " +
            " case when sum(totalAmount) is not null then sum(totalAmount) else 0 end" +
            " from (" +
            " select " +
            " sum(b.gains_rebate_amount) totalAmount" +
            " from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            " WHERE DATE(a.pay_time) = CURDATE() and a.`status` = 2" +
            " <if test='userId != null'> and b.gains_rebate_user_id = #{userId} </if>" +
            " union all" +
            " select " +
            " sum(b.gains_rebate_one_amount) totalAmount" +
            " from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            " WHERE DATE(a.pay_time) = CURDATE() and a.`status` = 2" +
            " <if test='userId != null'> and b.gains_rebate_one_user_id = #{userId} </if>" +
            " union all" +
            " select " +
            " sum(b.gains_rebate_two_amount) totalAmount" +
            " from bs_gas_order a LEFT JOIN bs_gas_order_spread b on a.order_no = b.order_no" +
            " WHERE DATE(a.pay_time) = CURDATE() and a.`status` = 2" +
            " <if test='userId != null'> and b.gains_rebate_two_user_id = #{userId} </if>" +
            " ) a" +
            "</script>")
    BigDecimal queryNotCashOutAmount(@Param("userId") Long userId);

    @Select(" select " +
            " ROUND(a.amount + a.cashOutAmount,2) totalAmount," +
            " a.amount notWithdrawnAmount," +
            " a.cashOutAmount," +
            " a.sysSperadAmount," +
            " ROUND(a.sysSperadAmount - a.cashOutAmount,2) surplusAmount" +
            " from " +
            " (SELECT" +
            " (select code_name from sec_dictionary where code_type = 'SYS_USER_SPREAD_ACCOUNT' and `status` = 1 LIMIT 1) sysSperadAmount," +
            " sum( amount ) amount," +
            " sum( cash_out_amount ) cashOutAmount" +
            " FROM" +
            " bs_user_spread_account " +
            " WHERE `status` = 1) a ")
    HashMap<String,Object> countCommission();
}


