package com.cirle.scientific.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.cirle.scientific.po.QkjRecommend;

public interface QkjRecommendDao
		extends PagingAndSortingRepository<QkjRecommend, Long>, JpaSpecificationExecutor<QkjRecommend> {

	@Query(value = "select a.* from qkj_recommend a where a.pos_id=?1 and a.status = 1 order by a.sort_num desc limit ?2,?3", nativeQuery = true)
	List<QkjRecommend> findByPosId(Integer posId, int start, int size);

	@Query(value = "select a.* from qkj_recommend a where a.pos_id=?1 and (a.status = 1 or a.status=?2) order by a.sort_num desc limit ?3,?4", nativeQuery = true)
	List<QkjRecommend> findByPosId(Integer posId, Integer status, int start, int size);

	@Query(value = "SELECT * FROM qkj_recommend WHERE pos_id IN (SELECT id FROM sys_positions WHERE p_posid = ?1) LIMIT ?2,?3" ,nativeQuery = true)
	List<QkjRecommend> findBypPosId(Integer pid, Integer start, Integer size);

	@Query(value = "SELECT * FROM qkj_recommend WHERE pos_id IN (SELECT id FROM sys_positions WHERE p_posid = ?1) AND rcmd_obj_id = ?4 LIMIT ?2,?3",nativeQuery = true)
	List<QkjRecommend> findByPosIdAndObjId(Integer posId, int i, int size, Long objId);

	@Query(value = "SELECT * FROM qkj_recommend WHERE status=1 AND id = ?1",nativeQuery = true)
	QkjRecommend findByIdAndStatus(Long id);

	@Query(value = "SELECT r.* FROM qkj_recommend_detail d"
					+"	LEFT JOIN qkj_recommend r on d.rcmd_id = r.id"
					+" WHERE post_type=?1 and obj_id=?2 and d.`status`=0 and r.`status`=1 ORDER BY d.sqn desc LIMIT ?3,?4" ,nativeQuery = true)
	List<QkjRecommend> findByPostTypeAndObjId(Integer postType, Integer objId, Integer start, Integer size);


	@Query(value = "select * from qkj_recommend where rcmd_obj_id = ?1  and  rcmd_type =?2",nativeQuery = true)
	List<QkjRecommend> findListByObjIdAndType(Long id, Integer type);

}
