raex_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.CollectionInfoDTO;
import com.izouma.nineth.dto.CollectionStockAndSale;
import com.izouma.nineth.dto.RecommendCollection;
import com.izouma.nineth.enums.CollectionSource;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.CachePut;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
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.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
import java.util.Set;

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.total_quota = ?24, c.minimum_charge = ?25 " +
            "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,
                Integer totalQuota, BigDecimal minimumCharge);

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

    @Query("select c from Collection c where c.del = false and c.id = ?1")
    Optional<Collection> findDetailById(@Nonnull Long id);

    @Cacheable("collectionInfo")
    @Query("select new com.izouma.nineth.dto.CollectionInfoDTO(c,cp) from Collection c left join CollectionPrivilege cp " +
            "on c.id = cp.collectionId where c.id = ?1 ")
    Optional<CollectionInfoDTO> findInfoById(@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")
    @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);

    @Query("select c.assetId from Collection  c where c.price >= ?1 and c.source = ?2 and c.createdAt <= ?3 and c.salable = ?4")
    Set<Long> findResaleCollectionPriceOver20K(BigDecimal price, CollectionSource source, LocalDateTime startTime, boolean salable);

    List<Collection> findAllByNameLike(String name);

    @Query(value = "select avg(t.price) from (select c.price from collection_info c where c.name like ?1 " +
            "and c.source = 'TRANSFER' " +
            "and c.salable = true " +
            "and c.stock > 0 order by c.price limit 5) t", nativeQuery = true)
    String lowestPrice(String search);

    @Query("select c from Collection c join c.tags t on t.id = ?1 ")
    Page<Collection> byTag(Long tagId, Pageable pageable);

    @Query("select count(id) from Collection where source = 'TRANSFER' and name like ?1")
    int countAllByNameLike(String name);

    Collection findFirstByOnShelfAndAssetId(boolean onShelf, Long assetId);

    List<Collection> findAllByOasisIdInAndSourceInAndStockGreaterThan(List<Long> oasisIds, List<CollectionSource> sources, int sale);
}