package cn.ysk.cashier.repository;


import cn.ysk.cashier.pojo.TbShopUserDuty;
import cn.ysk.cashier.repository.mapping.SumDateMapping;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.Date;
import java.util.List;


/**
 * @author lyf
 */
public interface ShopUserDutyRepository extends JpaRepository<TbShopUserDuty, Integer>{
    @Query("SELECT SUM(detail.orderNum), SUM(detail.amount) " +
            "FROM TbShopUserDuty detail " +
            "WHERE detail.shopId = :shopId")
    Tuple sumByShopId(@Param("shopId") Integer shopId);
    @Query(value = "SELECT\n" +
            "\tifnull( sum( amount ), 0 ) AS amount,\n" +
            "\ttrade_day as tradeDay\n" +
            "FROM\n" +
            "\t`tb_shop_user_duty` \n" +
            "WHERE\n" +
            "\tshop_id = :shopId \n" +
            "\tAND login_time BETWEEN :startTime AND :endTime \n" +
            "GROUP BY\n" +
            "\tshop_id,\n" +
            "\ttrade_day",nativeQuery = true)
    List<Object[]> sumByDate(@Param("shopId")Integer shopId, @Param("startTime") Date startTime,@Param("endTime") Date endTime);

    @Query("SELECT SUM(detail.orderNum), SUM(detail.amount) " +
            "FROM TbShopUserDuty detail " +
            "WHERE detail.shopId = :shopId " +
            "AND detail.loginTime BETWEEN :startTime AND :endTime")
    Tuple sumByShopIdToday(@Param("shopId") Integer shopId,@Param("startTime") Date startTime,@Param("endTime") Date endTime);

    @Query(value = "SELECT\n" +
            "\tifnull( sum( order_num ), 0 ) AS amount,\n" +
            "\ttrade_day as tradeDay\n" +
            "FROM\n" +
            "\t`tb_shop_user_duty` \n" +
            "WHERE\n" +
            "\tshop_id = :shopId \n" +
            "\tAND login_time BETWEEN :startTime AND :endTime \n" +
            "GROUP BY\n" +
            "\ttrade_day", nativeQuery = true)
    List<Object[]> sumByDateOrderNum(@Param("shopId")Integer shopId, @Param("startTime") Date startTime,@Param("endTime") Date endTime);
}
