package com.hfkj.dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.type.JdbcType;

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

/**
 * mapper扩展类
 */
public interface BsGasOrderMapperExt {
    
    @Select("<script>" +
            " SELECT" +
            " count( 1 ) totalCount," +
            " case when sum( gas_refuel_price ) then sum( gas_refuel_price ) else 0 end totalRefuelPrice, " +
            " case when sum( gas_oil_liters ) then sum( gas_oil_liters ) else 0 end totalOilLiters," +
            " case when sum( actual_pay_price ) then sum( actual_pay_price ) else 0 end totalActualPayPrice " +
            " FROM" +
            " bs_gas_order" +
            " where 1 = 1" +
            " <if test='param.deductionCouponNotNull != null'> <![CDATA[  and deduction_coupon_price > 0 ]]> </if>" +
            " <if test='param.merChainBrandId != null'> and mer_chain_brand_id = #{param.merChainBrandId} </if>" +
            " <if test='param.merId != null'> and mer_id = #{param.merId} </if>" +
            " <if test='param.merNo != null'> and mer_no = #{param.merNo} </if>" +
            " <if test='param.merName != null'> and mer_name like concat('%',#{param.merName},'%') </if>" +
            " <if test='param.orderNo != null'> and order_no like concat('%',#{param.orderNo},'%') </if>" +
            " <if test='param.orderChildNo != null'> and order_child_no like concat('%',#{param.orderChildNo},'%') </if>" +
            " <if test='param.channelType != null'> and channel_type = #{param.channelType} </if>" +
            " <if test='param.channelOrderNo != null'> and channel_order_no like concat('%',#{param.channelOrderNo},'%') </if>" +
            " <if test='param.payType != null'> and pay_type = #{param.payType} </if>" +
            " <if test='param.paySerialNo != null'> and pay_serial_no like concat('%',#{param.paySerialNo},'%') </if>" +
            " <if test='param.userPhone != null'> and user_phone = #{param.userPhone} </if>" +
            " <if test='param.gasOilType != null'> and gas_oil_type = #{param.gasOilType} </if>" +
            " <if test='param.gasStaffId != null'> and gas_staff_id = #{param.gasStaffId} </if>" +
            " <if test='param.gasStaffName != null'> and gas_staff_name like concat('%',#{param.gasStaffName},'%') </if>" +
            " <if test='param.agentId != null &amp;&amp; param.agentId == 0'> and agent_id is null </if>" +
            " <if test='param.agentId != null &amp;&amp; param.agentId != 0'> and agent_id = #{param.agentId} </if>" +
            " <if test='param.agentStaffId != null'> and agent_staff_id = #{param.agentStaffId} </if>" +
            " <if test='param.status != null'> and status = #{param.status} </if>" +
            " <if test='param.abnormal != null'> and abnormal = #{param.abnormal} </if>" +
            " <if test='param.gasClassGroupId != null'> and gas_class_group_id = #{param.gasClassGroupId} </if>" +
            " <if test='param.gasClassGroupTaskId != null'> and gas_class_group_task_id = #{param.gasClassGroupTaskId} </if>" +
            " <if test='param.gasOilNo != null'> and gas_oil_no = #{param.gasOilNo} </if>" +
            " <if test='param.createTimeS != null'><![CDATA[ and create_time >= #{param.createTimeS} ]]></if>" +
            " <if test='param.createTimeE != null'><![CDATA[ and create_time <= #{param.createTimeE} ]]></if>" +
            " <if test='param.payTimeS != null'><![CDATA[ and pay_time >= #{param.payTimeS} ]]></if>" +
            " <if test='param.payTimeE != null'><![CDATA[ and pay_time <= #{param.payTimeE} ]]></if>" +
            " <if test='param.refundTimeS != null'><![CDATA[ and refund_time >= #{param.refundTimeS} ]]></if>" +
            " <if test='param.refundTimeE != null'><![CDATA[ and refund_time <= #{param.refundTimeE} ]]></if>" +
            "</script>")
    Map<String,Object> selectGasOrderCount(@Param("param") Map<String,Object> param);

    @Select("<script>" +
            " select count(1) from bs_gas_order where user_phone = #{userPhone} and `status` in (2,5,6) and create_type = 1 and to_days(pay_time) = to_days(NOW())" +
            "</script>")
    int countTodayOrder(@Param("userPhone") String userPhone);
    @Select("<script>" +
            " select * from (select b.*," +
            " a.gains_rebate_user_id gainsRebateUserId, " +
            " a.gains_rebate_amount gainsRebateAmount, " +
            " a.gains_rebate_rate gainsRebateRate, " +
            " a.gains_rebate_one_user_id gainsRebateOneUserId, " +
            " a.gains_rebate_one_amount gainsRebateOneAmount, " +
            " a.gains_rebate_one_rate gainsRebateOneRate, " +
            " a.gains_rebate_two_user_id gainsRebateTwoUserId, " +
            " a.gains_rebate_two_amount gainsRebateTwoAmount, " +
            " a.gains_rebate_two_rate gainsRebateTwoRate" +
            " from bs_gas_order_spread a " +
            " LEFT JOIN bs_gas_order b on b.order_no = a.order_no " +
            " where 1 = 1" +
            " <if test='param.payTimeS != null'> <![CDATA[ and b.pay_time >= #{param.payTimeS} ]]> </if>" +
            " <if test='param.payTimeE != null'> <![CDATA[ and b.pay_time <= #{param.payTimeE} ]]> </if>" +
            " and b.`status` in (${param.status}) " +
            " ORDER BY b.pay_time desc) a " +
            " <if test='param.userId != null'> where a.gainsRebateUserId = #{param.userId} OR a.gainsRebateOneUserId = #{param.userId} OR a.gainsRebateTwoUserId = #{param.userId} </if>" +
            "</script>")
    @Results({
            @Result(column="id", property="id", jdbcType= JdbcType.BIGINT, id=true),
            @Result(column="order_no", property="orderNo", jdbcType=JdbcType.VARCHAR),
            @Result(column="order_child_no", property="orderChildNo", jdbcType=JdbcType.VARCHAR),
            @Result(column="channel_type", property="channelType", jdbcType=JdbcType.INTEGER),
            @Result(column="channel_order_no", property="channelOrderNo", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_id", property="agentId", jdbcType=JdbcType.BIGINT),
            @Result(column="agent_name", property="agentName", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_staff_id", property="agentStaffId", jdbcType=JdbcType.BIGINT),
            @Result(column="agent_staff_name", property="agentStaffName", jdbcType=JdbcType.VARCHAR),
            @Result(column="user_id", property="userId", jdbcType=JdbcType.BIGINT),
            @Result(column="user_phone", property="userPhone", jdbcType=JdbcType.VARCHAR),
            @Result(column="mer_chain_brand_id", property="merChainBrandId", jdbcType=JdbcType.BIGINT),
            @Result(column="mer_chain_brand_name", property="merChainBrandName", jdbcType=JdbcType.VARCHAR),
            @Result(column="mer_id", property="merId", jdbcType=JdbcType.BIGINT),
            @Result(column="mer_no", property="merNo", jdbcType=JdbcType.VARCHAR),
            @Result(column="mer_name", property="merName", jdbcType=JdbcType.VARCHAR),
            @Result(column="mer_address", property="merAddress", jdbcType=JdbcType.VARCHAR),
            @Result(column="total_deduction_price", property="totalDeductionPrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="deduction_coupon_price", property="deductionCouponPrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="deduction_oil_price", property="deductionOilPrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="pay_integral", property="payIntegral", jdbcType=JdbcType.BIGINT),
            @Result(column="payable_price", property="payablePrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="actual_pay_price", property="actualPayPrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_refuel_price", property="gasRefuelPrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_settle_price", property="gasSettlePrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_agent_settle_price", property="gasAgentSettlePrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_service_fee_rate", property="gasServiceFeeRate", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_service_fee_price", property="gasServiceFeePrice", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_oil_no", property="gasOilNo", jdbcType=JdbcType.VARCHAR),
            @Result(column="gas_gun_no", property="gasGunNo", jdbcType=JdbcType.VARCHAR),
            @Result(column="gas_oil_type", property="gasOilType", jdbcType=JdbcType.INTEGER),
            @Result(column="gas_price_official", property="gasPriceOfficial", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_price_gun", property="gasPriceGun", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_price_vip", property="gasPriceVip", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_price_platform", property="gasPricePlatform", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_price_cost", property="gasPriceCost", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_oil_liters", property="gasOilLiters", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_discount", property="gasDiscount", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_oil_subsidy", property="gasOilSubsidy", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_liters_preferences", property="gasLitersPreferences", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_price_preferences", property="gasPricePreferences", jdbcType=JdbcType.DECIMAL),
            @Result(column="gas_class_group_id", property="gasClassGroupId", jdbcType=JdbcType.BIGINT),
            @Result(column="gas_class_group_name", property="gasClassGroupName", jdbcType=JdbcType.VARCHAR),
            @Result(column="gas_class_group_task_id", property="gasClassGroupTaskId", jdbcType=JdbcType.BIGINT),
            @Result(column="gas_staff_id", property="gasStaffId", jdbcType=JdbcType.BIGINT),
            @Result(column="gas_staff_name", property="gasStaffName", jdbcType=JdbcType.VARCHAR),
            @Result(column="receipt_status", property="receiptStatus", jdbcType=JdbcType.INTEGER),
            @Result(column="receipt_fail_remark", property="receiptFailRemark", jdbcType=JdbcType.VARCHAR),
            @Result(column="pay_type", property="payType", jdbcType=JdbcType.INTEGER),
            @Result(column="pay_serial_no", property="paySerialNo", jdbcType=JdbcType.VARCHAR),
            @Result(column="pay_channel_order_no", property="payChannelOrderNo", jdbcType=JdbcType.VARCHAR),
            @Result(column="status", property="status", jdbcType=JdbcType.INTEGER),
            @Result(column="create_type", property="createType", jdbcType=JdbcType.INTEGER),
            @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="cancel_time", property="cancelTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="pay_time", property="payTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="refund_time", property="refundTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="refund_remarks", property="refundRemarks", jdbcType=JdbcType.VARCHAR),
            @Result(column="abnormal", property="abnormal", jdbcType=JdbcType.BIT),
            @Result(column="abnormal_content", property="abnormalContent", jdbcType=JdbcType.VARCHAR),
            @Result(column="ext_1", property="ext1", jdbcType=JdbcType.VARCHAR),
            @Result(column="ext_2", property="ext2", jdbcType=JdbcType.VARCHAR),
            @Result(column="ext_3", property="ext3", jdbcType=JdbcType.VARCHAR),
            @Result(column="ext_4", property="ext4", jdbcType=JdbcType.VARCHAR),
            @Result(column="ext_5", property="ext5", jdbcType=JdbcType.VARCHAR),
            @Result(column="ext_6", property="ext6", jdbcType=JdbcType.VARCHAR)
    })
    List<HashMap<String,Object>> queryOilOrderSpread(@Param("param") Map<String,Object> param);
    
    @Select("<script>" +
            " SET @totalCount := (select count(1) totalCount from bs_gas_order where status = 2 " +
            " <if test='channelType != null'> and channel_type = #{channelType} </if>" +
            " and TO_DAYS(#{startTime}) - TO_DAYS(create_time) = #{days});" +
            " SET @totalPrice := (select sum(gas_refuel_price) totalPrice from bs_gas_order where status = 2" +
            " <if test='channelType != null'> and channel_type = #{channelType} </if>" +
            " and TO_DAYS(#{startTime}) - TO_DAYS(create_time) = #{days});" +
            " "+
            " select " +
            " a.id merId," +
            " a.mer_no merNo," +
            " a.mer_name merName," +
            " a.status merStatus," +
            " case when b.totalCount is not null then b.totalCount else 0 end totalCount," +
            " case when b.totalCountRatio is not null then b.totalCountRatio else 0 end totalCountRatio," +
            " case when b.totalPrice is not null then b.totalPrice else 0 end totalPrice," +
            " case when b.totalPriceRatio is not null then b.totalPriceRatio else 0 end totalPriceRatio" +
            " from bs_merchant a" +
            " LEFT JOIN (" +
            "  SELECT " +
            "  mer_id," +
            "  mer_no," +
            "  mer_name," +
            "  order_no," +
            "  count(1) totalCount," +
            "  ROUND((count(1)  / @totalCount)  * 100,2) totalCountRatio," +
            "  sum(gas_refuel_price) totalPrice," +
            "  ROUND((sum(gas_refuel_price) / @totalPrice) * 100 ,2) totalPriceRatio" +
            "  from bs_gas_order" +
            "  where TO_DAYS(#{startTime}) - TO_DAYS(create_time) = #{days}  and status = 2" +
            " <if test='channelType != null'> and channel_type = #{channelType} </if>" +
            "  GROUP BY mer_no" +
            " ) b on b.mer_id = a.id" +
            " where 1 = 1 " +
            " <if test='channelType != null'> and a.source_type = #{channelType} </if>" +
            " ORDER BY totalPriceRatio desc" +
            "</script>")
    List<HashMap<String,Object>> countOrderRatio(Integer channelType, String startTime,Integer days);
}
