package com.cirle.scientific.dao;


import com.cirle.scientific.po.QkjArticle;
import com.cirle.scientific.po.QkjConference;
import com.cirle.scientific.po.QkjLive;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public interface QkjConferenceDao extends PagingAndSortingRepository<QkjConference, Long>, JpaSpecificationExecutor<QkjConference> {

    @Query(value = "(SELECT a.* FROM (SELECT * FROM qkj_conference WHERE status=2 and is_visiable=1 and m_or_pc in (?3) ORDER BY bigen_time ASC) a) "
                    +"	UNION all "
                    +"	(SELECT b.* FROM (SELECT * FROM qkj_conference WHERE status=1 and is_visiable=1 and m_or_pc in (?3) ORDER BY bigen_time ASC) b) "
                    +"	UNION all "
                    +"	(SELECT d.* FROM (SELECT * FROM qkj_conference WHERE status=3 and is_visiable=1 and m_or_pc in (?3) ORDER BY bigen_time desc) d) "
                    +"	limit ?1,?2",nativeQuery = true)
    List<QkjConference> findList(int start, int size,Integer[] mOrPc);

    @Query(value = "select sum(tmpcount) from ((SELECT count(*) tmpcount FROM qkj_conference WHERE status=2 and is_visiable=1 and m_or_pc in (?1) ORDER BY bigen_time ASC) " +
            "          UNION all " +
            "          (SELECT count(*) tmpcount FROM qkj_conference WHERE status=1 and is_visiable=1 and m_or_pc in (?1) ORDER BY bigen_time ASC) " +
            "          UNION all " +
            "          (SELECT count(*) tmpcount FROM qkj_conference WHERE status=3 and is_visiable=1 and m_or_pc in (?1) ORDER BY bigen_time desc) ) a ",
            nativeQuery = true)
    Integer findTotalList(Integer[] mOrPc);

    @Query(value = "(SELECT a.* FROM (SELECT * FROM qkj_conference WHERE id in (SELECT confer_id from qkj_conference_series where series_id=?1) and status=2 ORDER BY bigen_time ASC) a) "
            +"	UNION all "
            +"	(SELECT b.* FROM (SELECT * FROM qkj_conference WHERE id in (SELECT confer_id from qkj_conference_series where series_id=?1) and status=1 ORDER BY bigen_time ASC) b) "
            +"	UNION all "
            +"	(SELECT d.* FROM (SELECT * FROM qkj_conference WHERE id in (SELECT confer_id from qkj_conference_series where series_id=?1) and status=3 ORDER BY bigen_time desc) d) ",nativeQuery = true)
    List<QkjConference> findSonList(Long conferId);

    @Query(value = "select * from qkj_conference q WHERE q.id = ?1",nativeQuery = true)
    QkjConference findConferAllById(long Id);
}
