4.15

package com.example.baoli.repository;

import com.example.baoli.entity.ApprovalRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface ApprovalRecordRepository extends JpaRepository<ApprovalRecord, Long> {

List<ApprovalRecord> findByStatus(String status);

List<ApprovalRecord> findByApplicant(String applicant);

List<ApprovalRecord> findByApplicationType(String applicationType);

List<ApprovalRecord> findByApprover(String approver);

List<ApprovalRecord> findByDepartment(String department);

List<ApprovalRecord> findByStation(String station);

List<ApprovalRecord> findByWorkArea(String workArea);

@Query("SELECT a FROM ApprovalRecord a WHERE a.applicationDate BETWEEN :startDate AND :endDate")
List<ApprovalRecord> findByApplicationDateBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

@Query("SELECT a FROM ApprovalRecord a WHERE a.approvalDate BETWEEN :startDate AND :endDate")
List<ApprovalRecord> findByApprovalDateBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

@Query("SELECT a FROM ApprovalRecord a WHERE a.sparePartName LIKE %:keyword% OR a.sparePartModel LIKE %:keyword% OR a.applicant LIKE %:keyword% OR a.applicationId LIKE %:keyword%")
List<ApprovalRecord> searchByKeyword(@Param("keyword") String keyword);

@Query("SELECT a FROM ApprovalRecord a WHERE a.applicationType = '维修借用' AND a.status = '已通过' AND a.expectedReturnDate < :currentTime AND a.timeoutReminderSent = false")
List<ApprovalRecord> findOverdueRecords(@Param("currentTime") LocalDateTime currentTime);

@Query("SELECT a FROM ApprovalRecord a WHERE a.urgencyLevel = :urgencyLevel")
List<ApprovalRecord> findByUrgencyLevel(@Param("urgencyLevel") String urgencyLevel);

@Query("SELECT COUNT(a) FROM ApprovalRecord a WHERE a.status = :status")
Long countByStatus(@Param("status") String status);

@Query("SELECT a FROM ApprovalRecord a WHERE a.sparePartName = :partName AND a.sparePartModel = :partModel")
List<ApprovalRecord> findBySparePartNameAndSparePartModel(@Param("partName") String partName, @Param("partModel") String partModel);

@Query("SELECT a FROM ApprovalRecord a WHERE a.faultEquipment LIKE %:equipment%")
List<ApprovalRecord> findByFaultEquipmentContaining(@Param("equipment") String equipment);

/**
* 获取状态统计信息
*/
@Query("SELECT a.status, COUNT(a) FROM ApprovalRecord a GROUP BY a.status")
List<Object[]> getStatusStatistics();

/**
* 多条件搜索审批记录
*/
@Query("SELECT a FROM ApprovalRecord a WHERE " +
"(:applicant IS NULL OR a.applicant LIKE %:applicant%) AND " +
"(:sparePartName IS NULL OR a.sparePartName LIKE %:sparePartName%) AND " +
"(:status IS NULL OR a.status = :status) AND " +
"(:applicationType IS NULL OR a.applicationType = :applicationType) AND " +
"(:urgencyLevel IS NULL OR a.urgencyLevel = :urgencyLevel)")
List<ApprovalRecord> searchApprovalRecords(@Param("applicant") String applicant,
@Param("sparePartName") String sparePartName,
@Param("status") String status,
@Param("applicationType") String applicationType,
@Param("urgencyLevel") String urgencyLevel);

/**
* 获取不重复的申请人列表
*/
@Query("SELECT DISTINCT a.applicant FROM ApprovalRecord a WHERE a.applicant IS NOT NULL ORDER BY a.applicant")
List<String> findDistinctApplicants();

/**
* 获取不重复的审核人列表
*/
@Query("SELECT DISTINCT a.approver FROM ApprovalRecord a WHERE a.approver IS NOT NULL ORDER BY a.approver")
List<String> findDistinctApprovers();

/**
* 获取不重复的部门列表
*/
@Query("SELECT DISTINCT a.department FROM ApprovalRecord a WHERE a.department IS NOT NULL ORDER BY a.department")
List<String> findDistinctDepartments();
}