package cn.ysk.cashier.repository;

import cn.ysk.cashier.pojo.TbShopUserDutyDetail;
import cn.ysk.cashier.vo.ProductVO;
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 ShopUserDutyDetailRepository extends JpaRepository<TbShopUserDutyDetail, Integer> {

    @Query(value = "SELECT" +
            " row_number() over ( ORDER BY SUM(product.num) DESC) AS productId, product.product_name as productName, SUM(product.num)AS num," +
            " sum(product.amount) as productNum" +
            " FROM" +
            " tb_shop_user_duty_detail AS product" +
            " LEFT JOIN tb_shop_user_duty AS user ON product.duty_id = USER.id " +
            "WHERE user.shop_id = :shopId" +
            " AND user.login_time BETWEEN :startTime AND :endTime" +
            " GROUP BY product.product_id" +
            " Limit :currentPage, :currentSize ", nativeQuery = true)
    List<Object[]> searchByDutyId(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime
                                  ,@Param("currentPage") Integer currentPage, @Param("currentSize")Integer currentSize);


    @Query(value = "SELECT\n" +
            "\tcount( 0 ) \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tproduct.product_name \n" +
            "\tFROM\n" +
            "\t\ttb_shop_user_duty_detail AS product\n" +
            "\t\tLEFT JOIN tb_shop_user_duty AS USER ON product.duty_id = USER.id \n" +
            "\tWHERE\n" +
            "\t\tUSER.shop_id = :shopId \n" +
            "\t\tAND USER.login_time BETWEEN :startTime \n" +
            "\t\tAND :endTime \n" +
            "\tGROUP BY\n" +
            "\tproduct.product_id \n" +
            "\t) AS total", nativeQuery = true)
    Tuple searchCount(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);


    @Query(value = "SELECT\n" +
            "\tsum( sudd.num ),\n" +
            "\tb.amount \n" +
            "FROM\n" +
            "\ttb_shop_user_duty_detail sudd\n" +
            "\tINNER JOIN tb_shop_user_duty sud ON sudd.duty_id = sud.id\n" +
            "\tLEFT JOIN ( SELECT shop_id, sum( amount ) AS amount FROM tb_shop_user_duty WHERE shop_id = :shopId AND login_time BETWEEN :startTime AND :endTime ) b ON sud.shop_id = b.shop_id \n" +
            "WHERE\n" +
            "\tsud.shop_id = :shopId \n" +
            "\tAND sud.login_time BETWEEN :startTime \n" +
            "\tAND :endTime \n" +
            "GROUP BY\n" +
            "\tb.shop_id", nativeQuery = true)
    Tuple searchByDutyIdSum(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);



    @Query(value = "SELECT" +
            " SUM(product.num),SUM(product.amount)" +
            " FROM" +
            " tb_shop_user_duty_detail AS product" +
            " LEFT JOIN tb_shop_user_duty AS user ON product.duty_id = USER.id " +
            "WHERE user.shop_id = :shopId" +
            " AND user.login_time BETWEEN :startTime AND :endTime" +
            " group by user.trade_day", nativeQuery = true)
    List<Object[]> searchByDayToAmount(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);


    @Query(value = "SELECT" +
            " SUM(product.num),user.trade_day" +
            " FROM" +
            " tb_shop_user_duty_detail AS product" +
            " LEFT JOIN tb_shop_user_duty AS user ON product.duty_id = USER.id " +
            "WHERE user.shop_id = :shopId" +
            " AND user.login_time BETWEEN :startTime AND :endTime" +
            " group by user.trade_day", nativeQuery = true)
    List<Object[]> searchByDutyGroup(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query(value = "SELECT" +
            " SUM(user.amount),user.trade_day" +
            " FROM" +
            " tb_shop_user_duty AS user " +
            " WHERE user.shop_id = :shopId" +
            " AND user.login_time BETWEEN :startTime AND :endTime" +
            " group by user.trade_day", nativeQuery = true)
    List<Object[]> searchByGroup(@Param("shopId") Integer shopId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);
}
