package cn.palmte.work.model;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author Yuanping Zhang
 * @date 2021/11/11
 */
public interface ProjectSettleIncomeRepository extends JpaRepository<ProjectSettleIncome, Integer> {

    List<ProjectSettleIncome> findAllByProjectIdAndTime(int id, String time);

    @Query(value = "select * from project_settle_income where project_id = ?1 and time < ?2", nativeQuery = true)
    List<ProjectSettleIncome> findAllByProjectIdBefore(int id, String time);

    @Query(value = "select * from project_settle_income where project_id = ?1 and time <= ?2", nativeQuery = true)
    List<ProjectSettleIncome> findAllByProjectIdAndTimeBeforeAndEquals(int id, String time);

    @Query(value = "select sum(income_tax_exclude) from project_settle_income where project_id = ? and type = ?", nativeQuery = true)
    BigDecimal incomeTaxExcludeSum(int projectId, int type);

    List<ProjectSettleIncome> findAllByProjectId(int id);

    List<ProjectSettleIncome> findAllByTime(String time);

    @Query(value = "select * from project_settle_income where project_id = ?1 order by id desc limit 0, 1", nativeQuery = true)
    ProjectSettleIncome findNewByProjectId(int id);

    @Query(value = "select * from project_settle_income where project_id in ?1 and time = ?2", nativeQuery = true)
    List<ProjectSettleIncome> findAllByProjectsAndTime(List<Integer> ids, String time);

    @Query(value = "SELECT time FROM project_settle_income GROUP BY time ORDER BY time", nativeQuery = true)
    List<String> getProjectTime();

    @Query(value = "select sum(income_tax_exclude) from project_settle_income where time = ? and type = ?", nativeQuery = true)
    BigDecimal incomeTaxExcludeSumByTime(String time, int type);

    @Query(value = "select sum(income_tax_exclude) from project_settle_income where type = ?", nativeQuery = true)
    BigDecimal incomeTaxExcludeSumByType(int type);

    @Query(value = "select * from project_settle_income where project_id in ?1", nativeQuery = true)
    List<ProjectSettleIncome> findAllByProjectIds(List<Integer> projectInt);

    @Query(value = "select time from project_settle_income where project_id = ?1 group by time order by time asc", nativeQuery = true)
    List<String> findAllByProjectIdAndGroupByTime(int id);
}
