package com.uas.platform.b2b.dao;

import com.uas.platform.b2b.model.PurchaseNotice;
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 org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

@Repository
@Transactional
public interface PurchaseNoticeDao extends JpaSpecificationExecutor<PurchaseNotice>, JpaRepository<PurchaseNotice, Long> {

	/**
	 * 按买家企业ID和送货提醒来源ID查找送货提醒单
	 * 
	 * @return
	 */
	List<PurchaseNotice> findByEnUUAndSourceId(long enUU, long sourceId);

	/**
	 * 按供应商企业ID和传输状态查找送货提醒单
	 * 
	 * @return
	 */
	List<PurchaseNotice> findByVendUUAndSendStatus(long vendUU, Short sendStatus);

	/**
	 * 按供应商企业ID、是否结案和结案状态查找送货提醒
	 * 
	 * @return
	 */
	List<PurchaseNotice> findByVendUUAndEndStatus(long vendUU, Short endStatus);

	/**
	 * 按ID集合查找送货提醒
	 * 
	 * @return
	 */
	@Query("select n from PurchaseNotice n where n.id IN (:ids) and (n.endQty is null or n.endQty < n.qty)")
	List<PurchaseNotice> findUnEndByIds(@Param("ids") Long[] ids);

	/**
	 * 按发货单更新通知单的发货数
	 * 
	 */
	@Modifying(clearAutomatically = true)
	@Query("update PurchaseNotice n set n.endQty=(select sum(s.qty) from SaleSendItem s where s.notice=n) where n.id= :id")
	public void updateBySend(@Param("id") long id);
	
	/**
	 * 根据截止日期获取（交货日期）
	 * @param formDate
	 * @param endDate
	 * @return
	 */
	@Query("select p.id from PurchaseNotice p where p.vendUU=:vendUU and p.delivery <= :endDate")
	public List<Long> findByEndDate(@Param("vendUU")Long vendUU, @Param("endDate")Date endDate);
	
	/**
	 * 根据起始日期获取（交货日期）
	 * @param formDate
	 * @param endDate
	 * @return
	 */
	@Query("select p.id from PurchaseNotice p where p.vendUU=:vendUU and p.delivery >= :fromDate")
	public List<Long> findByFromDate(@Param("vendUU")Long vendUU, @Param("fromDate")Date formDate);

	/**
	 * 根据供应商UU号获取客户发发货提醒数量
	 * @param vendUU
	 * @return
	 */
	@Query("select count(p) from PurchaseNotice p where p.vendUU = :vendUU")
	public long countByVendUU(@Param("vendUU")Long vendUU);

	/**
	 * 获得每种单据状态的未读数量
	 *
	 * @Param ids 未读单据id
	 * @return
	 */
	@Query("select count(p), p.status from PurchaseNotice p where p.id in :ids and (p.end is null or p.end = 0) and p.waiting=0 group by p.status")
	List<Object[]> getUnreadCountEveryStatus(@Param("ids") List<Long> ids);

	/**
	 * 更新发货提醒发货状态
	 *
	 * @param noticeId 发货提醒id
	 * @param status 状态
	 */
	@Transactional
    @Modifying(clearAutomatically = true)
	@Query("update PurchaseNotice set status = :status where id = :noticeId")
	void updateStatus(@Param("noticeId") Long noticeId, @Param("status") short status);
}
