package com.cirle.scientific.dao;

import com.cirle.scientific.po.QkjIntegralGoods;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

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

public interface QkjIntegralGoodsDao  extends PagingAndSortingRepository<QkjIntegralGoods, Long>, JpaSpecificationExecutor<QkjIntegralGoods> {


    @Query(value = "select  * from qkj_integral_goods where goods_status = 0 order by sqn desc limit ?1,?2",nativeQuery = true)
    List<QkjIntegralGoods> findGoodMallList(Integer page,Integer size);

    @Query(value = "select  count(*) from qkj_integral_goods where goods_status = 0 order by sqn desc ",nativeQuery = true)
    Integer findGoodMallListSize();

    @Query(value = "SELECT\n" +
            "\tcount(*) \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tid,\n" +
            "\t\tgoods_img AS 'img',\n" +
            "\t\tgoods_name AS 'name',\n" +
            "\t\t\"商品\" AS 'type',\n" +
            "\t\t`goods_status` AS 'goodsStatus',\n" +
            "\t\tNULL AS 'status',\n" +
            "\t\tgoods_need_points AS \"points\",\n" +
            "\t\tNULL AS \"price\",\n" +
            "\t\tNULL AS \"zq\",\n" +
            "\t\tNULL AS \"purchasedMembers\",\n" +
            "\t\tNULL AS \"totalClassHours\" \n" +
            "\tFROM\n" +
            "\t\tqkj_integral_goods \n" +
            "\tWHERE\n" +
            "\t\tgoods_status = 0 UNION ALL\n" +
            "\tSELECT\n" +
            "\t\tid,\n" +
            "\t\tcover_img AS 'img',\n" +
            "\t\ttitle AS 'name',\n" +
            "\t\t\"课程\" AS 'type',\n" +
            "\t\tNULL AS \"goodsStatus\",\n" +
            "\t\t`status` AS 'status',\n" +
            "\t\tpoint AS \"points\",\n" +
            "\t\tprice AS \"price\",\n" +
            "\t\tzq AS \"zq\",\n" +
            "\t\tpurchased_members AS \"purchasedMembers\",\n" +
            "\t\ttotal_class_hours AS \"totalClassHours\" \n" +
            "\tFROM\n" +
            "\t\tqkj_tqcourse \n" +
            "\tWHERE\n" +
            "\t\tSTATUS = 1 \n" +
            "\tAND is_visiable = 1 \n" +
            "\t) AS test",nativeQuery = true)
    Integer findAllListSize();

    @Query(value = "SELECT\n" +
            " *\n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tid AS 'id',\n" +
            "\t\tgoods_img AS 'img',\n" +
            "\t\tgoods_name AS 'name',\n" +
            "\t\t\"商品\" AS 'type',\n" +
            "\t\t`goods_status` AS 'goodsStatus',\n" +
            "\t\tNULL AS 'status',\n" +
            "\t\tgoods_need_points AS \"points\",\n" +
            "\t\tNULL AS \"price\",\n" +
            "\t\tNULL AS \"zq\",\n" +
            "\t\tNULL AS \"purchasedMembers\",\n" +
            "\t\tNULL AS \"totalClassHours\" \n" +
            "\tFROM\n" +
            "\t\tqkj_integral_goods \n" +
            "\tWHERE\n" +
            "\t\tgoods_status = 0 UNION ALL\n" +
            "\tSELECT\n" +
            "\t\tid AS 'id',\n" +
            "\t\tcover_img AS 'img',\n" +
            "\t\ttitle AS 'name',\n" +
            "\t\t\"课程\" AS 'type',\n" +
            "\t\tNULL AS \"goodsStatus\",\n" +
            "\t\t`status` AS 'status',\n" +
            "\t\tpoint AS \"points\",\n" +
            "\t\tprice AS \"price\",\n" +
            "\t\tzq AS \"zq\",\n" +
            "\t\tpurchased_members AS \"purchasedMembers\",\n" +
            "\t\ttotal_class_hours AS \"totalClassHours\" \n" +
            "\tFROM\n" +
            "\t\tqkj_tqcourse \n" +
            "\tWHERE\n" +
            "\t\tSTATUS = 1 \n" +
            "\tAND is_visiable = 1 \n" +
            "\t) AS test limit ?1, ?2\n" +
            "\t",nativeQuery = true)
    List<Map<String,Object>> findLimitAllList(Integer page,Integer size);

}
