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.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.uas.platform.b2b.model.PurchaseOrderChange;

@Repository
public interface PurchaseOrderChangeDao extends JpaSpecificationExecutor<PurchaseOrderChange>, JpaRepository<PurchaseOrderChange, Long> {

	/**
	 * 按所属企业及编号查找变更单
	 * 
	 * @param enUU
	 * @param code
	 * @return
	 */
	@Query("from PurchaseOrderChange r where r.enUU = :enUU and r.code = :code")
	public List<PurchaseOrderChange> findByEnUUAndCode(@Param("enUU") long enUU, @Param("code") String code);

	/**
	 * (买家)按回复的状态查找变更单回复信息
	 * 
	 * @param enUU
	 * @param status
	 * @param backStatus
	 * @return
	 */
	@Query("from PurchaseOrderChange r where r.enUU = :enUU and r.status = :status and r.backStatus = :backStatus")
	public List<PurchaseOrderChange> findByEnUUAndStatusAndBackStatus(@Param("enUU") long enUU, @Param("status") short status,
			@Param("backStatus") short backStatus);

	/**
	 * 卖家按上传状态查找变更单
	 * 
	 * @param vendUU
	 *            卖方企业ID
	 * @param sendStatus
	 *            上传到卖家ERP的状态
	 * @return
	 */
	@Query("from PurchaseOrderChange r where r.vendUU = :vendUU and r.sendStatus = :sendStatus and r.erpId is not null")
	public List<PurchaseOrderChange> findByVendUUAndSendStatus(@Param("vendUU") long vendUU, @Param("sendStatus") short sendStatus);

	/**
	 * 卖家按上传状态查找变更单(买家b2b)
	 *
	 * @param vendUU
	 *            卖方企业ID
	 * @param sendStatus
	 *            上传到卖家ERP的状态
	 * @return
	 */
	@Query("from PurchaseOrderChange r where r.vendUU = :vendUU and r.sendStatus = :sendStatus and r.erpId is null")
	public List<PurchaseOrderChange> findByVendUUAndSendStatusAndErpId(@Param("vendUU") long vendUU, @Param("sendStatus") short sendStatus);

	/**
	 * 卖家按回复信息上传状态查找变更单
	 * 
	 * @param vendUU
	 *            卖方企业ID
	 * @param status
	 *            是否已回复的状态
	 * @param replySendStatus
	 *            回复信息上传到卖家ERP的状态
	 * @return
	 */
	@Query("from PurchaseOrderChange r where r.vendUU = :vendUU and r.status = :status and r.replySendStatus = :replySendStatus")
	public List<PurchaseOrderChange> findByVendUUAndStatusAndReplySendStatus(@Param("vendUU") long vendUU, @Param("status") short status,
			@Param("replySendStatus") short replySendStatus);
	
	/**
	 * 按变更单的企业UU号来统计条数
	 * 
	 * @param enUU
	 * @return
	 */
	@Query("select count(o) from PurchaseOrderChange o where o.enUU = :enUU")
	public Long getChangeCountByEnUU(@Param("enUU") Long enUU);

	/**
	 * 根据供应商UU号获取客户采购变更单数量
	 * @param vendUU
	 * @return
	 */
	@Query("select count(p) from PurchaseOrderChange p where p.vendUU = :vendUU")
	public long countByVendUU(@Param("vendUU")Long vendUU);

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

}
