lasuo_back/src/main/java/com/izouma/nineth/repo/CollectionRepo.java

package com.izouma.nineth.repo;

import com.izouma.nineth.domain.Collection;
import com.izouma.nineth.dto.CollectionStockAndSale;
import com.izouma.nineth.dto.RecommendCollection;
import com.izouma.nineth.enums.SubscribeStatus;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.CachePut;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.annotation.Nonnull;
import javax.transaction.Transactional;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

public interface CollectionRepo extends JpaRepository<Collection, Long>, JpaSpecificationExecutor<Collection> {
    @Query("update Collection t set t.del = true where t.id = ?1")
    @Modifying
    @Transactional
    @CacheEvict(value = {"collection", "recommend"}, allEntries = true)
    void softDelete(Long id);

    @Transactional
    @Modifying
    @Query(value = "update collection_info c set c.on_shelf = ?2, c.salable = ?3, c.start_time = ?4, " +
            "c.schedule_sale = ?5, c.sort = ?6, c.detail = ?7, c.privileges = ?8, " +
            "c.properties = ?9, c.model3d = ?10, c.max_count = ?11, c.count_id = ?12, c.scan_code = ?13, " +
            "c.no_sold_out = ?14, c.assignment = ?15, c.coupon_payment = ?16, c.share_bg = ?17," +
            "c.register_bg = ?18, c.vip_quota = ?19, c.time_delay = ?20, c.sale_time = ?21, c.hold_days = ?22, " +
            "c.open_quota = ?23, c.showroom_bg = ?24, c.max_collection = ?25, c.total_quota = ?26," +
            "c.collection_category = ?27 , c.collection_works = ?28 , c.issuer = ?29 , c.purchase_instructions = ?30," +
            "c.end_time = ?31, c.publish_time = ?32, c.purchase_time = ?33 " +
            "where c.id = ?1", nativeQuery = true)
    @CacheEvict(value = {"collection", "recommend"}, allEntries = true)
    void update(@Nonnull Long id, boolean onShelf, boolean salable, LocalDateTime startTime,
                boolean schedule, int sort, String detail, String privileges,
                String properties, String model3d, int maxCount, String countId, boolean scanCode,
                boolean noSoldOut, int assignment, boolean couponPayment, String shareBg, String registerBg,
                Integer vipQuota, Boolean timeDelay, LocalDateTime saleTime, Integer holdDays, Boolean openQuota,
                String showroomBg, Integer maxCollection, Integer totalQuota, String collectionCategory,
                String collectionWorks, String issuer, String purchaseInstructions, LocalDateTime endTime,
                LocalDateTime publishTime, LocalDateTime purchaseTime);

    @Cacheable("collection")
    Optional<Collection> findById(@Nonnull Long id);

    Optional<Collection> findByIdAndDelFalse(Long id);

    @Query("update Collection t set t.likes = t.likes + ?2 where t.id = ?1")
    @Modifying
    @Transactional
    @CacheEvict(value = "collection", key = "#id")
    void addLike(Long id, int num);

    @Query(value = "select distinct c from Collection c join Like l on l.collectionId = c.id " +
            "where l.userId = ?1 and l.del = false and c.del = false")
    List<Collection> userLikes(Long userId);

    List<Collection> findByScheduleSaleTrueAndOnShelfFalseAndStartTimeBeforeAndDelFalse(LocalDateTime time);

    @Nonnull
    @CachePut(value = "collection", key = "#collection.id")
    Collection save(@Nonnull Collection collection);

    @Query("select new com.izouma.nineth.dto.RecommendCollection(c,r) from Collection c join Recommend r on c.id = r.collectionId " +
            "where c.del = false and c.onShelf = true and r.type = ?1 and r.category = 'COLLECTION' order by r.sort desc")
    List<RecommendCollection> recommend(String type);

    @Transactional
    @Modifying
    @Query("update Collection c set c.currentNumber = COALESCE(c.currentNumber, 0) + ?2 where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void increaseNumber(Long id, int d);

    @Transactional
    @Modifying
    @Query("update Collection c set c.total = COALESCE(c.total, 0) + ?2 where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void increaseTotal(Long id, int d);

    @Transactional
    @Modifying
    @Query("update Collection c set c.onShelf = ?2 where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void setOnShelf(Long id, boolean onShelf);

    @Transactional
    @Modifying
//    @Query("update Collection c set c.scheduleSale = false, c.startTime = null, c.onShelf = ?2, c.salable = true where c.id = ?1")
    @Query("update Collection c set c.scheduleSale = false, c.onShelf = ?2, c.salable = true where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void scheduleOnShelf(Long id, boolean onShelf);

    @Query("select c.currentNumber from Collection c where c.id = ?1")
    Optional<Integer> getCurrentNumber(Long id);

    List<Collection> findByScheduleSaleTrue();

    List<Collection> findByNameLike(String name);

    List<Collection> findByStockGreaterThan(int stock);

    @Query("update Collection c set c.stock = ?2 where c.id = ?1")
    @Transactional
    @Modifying
    int updateStock(Long id, int stock);

    @Query("update Collection c set c.sale = ?2 where c.id = ?1")
    @Transactional
    @Modifying
    int updateSale(Long id, int sale);

    @Query("select c.stock from Collection c where c.id = ?1")
    Integer getStock(Long id);

    @Query("select c.sale from Collection c where c.id = ?1")
    Integer getSale(Long id);

    @Query("select new com.izouma.nineth.dto.CollectionStockAndSale(c.id, c.stock, c.sale) from Collection c where c.stock > 0")
    List<CollectionStockAndSale> getStockAndSale();

    List<Collection> findAllByIdIn(java.util.Collection<Long> ids);

    @Query(value = "select c.id, c.pic, c.model3d, c.minter_avatar, c.owner_avatar, c.detail from collection_info c", nativeQuery = true)
    List<List<String>> selectResource();

    @Query(value = "select c.id, c.pic, c.model3d, c.minter_avatar, c.owner_avatar, c.detail from collection_info c where c.id = ?1", nativeQuery = true)
    List<List<String>> selectResource(Long id);

    @Modifying
    @Transactional
    @Query(value = "update collection_info c set c.pic = ?2, c.model3d = ?3, c.minter_avatar = ?4, " +
            "c.owner_avatar = ?5, c.detail = ?6 where c.id = ?1", nativeQuery = true)
    int updateCDN(Long id, String pic, String model3d, String minterAvatar,
                  String ownerAvatar, String detail);

    @Query("update Collection c set c.vipQuota = ?2 where c.id = ?1")
    @Transactional
    @Modifying
    int updateVipQuota(Long id, int vipQuota);

    @Query("select c.vipQuota from Collection c where c.id = ?1")
    Integer getVipQuota(Long id);


    List<Collection> findAllByStartTimeGreaterThanAndStartTimeEquals(LocalDateTime startTime,LocalDateTime endTime);

    List<Collection> findAllByEndTimeGreaterThanAndEndTimeEquals(LocalDateTime startTime, LocalDateTime endTime);

    List<Collection> findAllByPublishTimeGreaterThanAndPublishTimeEquals(LocalDateTime endTime, LocalDateTime publishTime);

    List<Collection> findAllByPurchaseTimeGreaterThanAndPurchaseTimeEquals(LocalDateTime publishTime, LocalDateTime purchaseTime);


    //筛选
//    @Query(value = "select c.id, c.asset_id, c.stock, c.start_time, c.end_time, c.publish_time, c.purchase_time from collection_info c where c.end_time <= ?1 and c.publish_time > ?2", nativeQuery = true)
    List<Collection> findAllByEndTimeLessThanEqualAndPublishTimeGreaterThan(LocalDateTime startTime, LocalDateTime endTime);

    @Transactional
    @Modifying
    @Query("update Collection c set c.subscribeStatus = ?2 where c.id = ?1 ")
    @CacheEvict(value = "collection", key = "#id")
    int updateSubscribeStatus(Long id, SubscribeStatus subscribeStatus);

}