package com.uas.platform.b2b.dao;

import java.util.List;

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 com.uas.platform.b2b.model.SaleSendItem;

@Repository
@Transactional
public interface SaleSendItemDao extends JpaSpecificationExecutor<SaleSendItem>, JpaRepository<SaleSendItem, Long> {

	/**
	 * 按送货单主记录ID和明细序号查找送货明细
	 * 
	 * @param sendId
	 * @param number
	 * @return
	 */
	@Query("select s from SaleSendItem s where s.send.id = :sendId and s.number = :number")
	List<SaleSendItem> findBySendAndNumber(@Param("sendId") long sendId, @Param("number") short number);

	/**
	 * 按客户回复更新送货单明细数量
	 */
	@Modifying(clearAutomatically = true)
	@Query("update SaleSendItem s set s.qty = :qty,s.replyRemark = :remark where s.id= :id")
	public void updateByReply(@Param("id") long id, @Param("qty") double qty, @Param("remark") String remark);
	
	/**
	 * 更新客户收料数量、合格数、不合格数
	 * @param id
	 * @param verifyQty
	 */
	@Modifying(clearAutomatically = true)
	@Query(nativeQuery = true,
		value = "update sale$senditem si set si_verifyqty = (select sum(siv_qty) from SALE$SENDITEMVERIFY siv where siv.SIV_SENDITEMID = si.SI_ID ), si_okqty = (select sum(siv_okqty) from SALE$SENDITEMVERIFY siv where siv.SIV_SENDITEMID = si.SI_ID ), si_notokqty = (select sum(siv_notokqty) from SALE$SENDITEMVERIFY siv where siv.SIV_SENDITEMID = si.SI_ID ) where si.si_id= :id")
	public void updateByVerify(@Param("id") long id);
	
	/**
	 * 获取发货单的发货明细数量
	 * @param sendId
	 * @return
	 */
	@Query("select count(1) from SaleSendItem s where s.send.id = :sendId")
	public int getTotalCountBySendId(@Param("sendId") long sendId);

	/**
	 * 获取发货单的发货明细数量
	 * @param sendId
	 * @return
	 */
	@Query("select count(1) from SaleSendItem s where s.send.id = :sendId and s.verifyQty >= s.qty")
	public int getFullVerifyCountBySendId(@Param("sendId") long sendId);
	
}
