ps-inquiry/src/main/java/com/uas/ps/inquiry/dao/PublicInquiryItemDao.java

package com.uas.ps.inquiry.dao;

import com.uas.ps.inquiry.model.PublicInquiryItem;
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 org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Set;

/**
 * 公共询价报价信息表数据库操作
 *
 * Created by hejq on 2018-01-14.
 */
@Repository
public interface PublicInquiryItemDao extends JpaRepository<PublicInquiryItem, Long>, JpaSpecificationExecutor<PublicInquiryItem> {

    /**
     * 通过企业UU和下载状态查询已报价未传回ERP的单据
     *
     * @param enUU 企业UU
     * @param backStatus 未下载状态202
     * @author hejq
     * @date 2018-01-14 11:49
     * @return
     */
    @Query("from PublicInquiryItem d where d.inquiry.enUU = :enUU and d.backStatus = :backStatus and ifnull(d.source, ' ') = 'ERP'")
    List<PublicInquiryItem> findByEnUUAndBackStatus(@Param("enUU") Long enUU, @Param("backStatus") short backStatus);

    /**
     * 根据传回的id更新下载状态
     *
     * @param ids 询价明细idList
     * @author hejq
     * @date 2018-01-14 11:49
     */
    @Modifying
    @Transactional
    @Query("update PublicInquiryItem i set i.backStatus = 351 where i.id in (:ids)")
    void updateBackStatus(@Param("ids") Set<Long> ids);

    /**
     * 按询价单明细ID更新采纳结果
     *              新增将decideDownStatus设置为202 ，供报价方UAS获取
     *
     * @author hejq
     * @date 2018-01-14 15:03
     * @param agreed 采纳结果
     * @param decideStatus 采纳状态
     * @param id 询价id
     */
    @Modifying(clearAutomatically = true)
    @Query("update PublicInquiryItem p set p.agreed = :agreed, p.decideStatus = :decideStatus, p.decideDownStatus = 202 where p.id = :id")
    @Transactional
    void updateDecideStatusByID(@Param("agreed")Short agreed, @Param("decideStatus") Short decideStatus, @Param("id") Long id);

    /**
     * 通过供应商UU号和来源id查询单据明细
     *
     * @author hejq
     * @date 2018-01-14 14:53
     * @param enuu 供应商UU号
     * @param id 来源id
     * @return
     */
    PublicInquiryItem findByVendUUAndSourceId(Long enuu, Long id);

    /**
     * 通过主表id查询序号
     *
     * @param id 询价主表id
     * @return
     */
    @Query("select count(1) from PublicInquiryItem i where i.inquiry.id = :id")
    Integer countByInquiryId(@Param("id") Long id);

    /**
     * 通过来源id查询报价信息
     *
     * @param sourceId
     * @return
     */
    List<PublicInquiryItem> findBySourceId(Long sourceId);

    /**
     * 通过来源id查询报价信息 报价时间倒序
     *
     * @param sourceId
     * @return
     */
    List<PublicInquiryItem> findBySourceIdOrderByOfferTimeDesc(Long sourceId);

    /**
     * 根据enUU和上传状态获取报价明细
     * @param vendUU 企业UU
     * @param status  状态
     * @return 报价单list
     */
    List<PublicInquiryItem> findByVendUUAndDecideDownStatus(Long vendUU, short status);

    /**
     * 通过企业UU和回复下载状态查询已报价 未传到卖家ERP的单据
     *
     * @param vendUU 企业UU
     * @param replySendStatus 未下载状态202
     * @author dongbw
     * @return
     */
    @Query(value = "select * from public$inquiryitems d where d.id_venduu = :vendUU and d.id_replysendstatus = :replySendStatus limit 200", nativeQuery = true)
    List<PublicInquiryItem> findByVendUUAndReplySendStatus(@Param("vendUU")Long vendUU, @Param("replySendStatus") short replySendStatus);
}