Untitled

 avatar
unknown
plain_text
5 months ago
1.3 kB
3
Indexable
package com.hdfc.epi.lib.repository;

import com.hdfc.epi.lib.entity.TxnBlackoutMaster;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

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

public interface TxnBlackoutMasterRepository extends JpaRepository<TxnBlackoutMaster, Long> {

    @Query(value = """
           SELECT * FROM txn_blackout_master t
           WHERE 
               t.channel_id = 'NB' AND
               (
                   (t.status = 'FULL_PERIOD' AND 
                       CAST(:currentDateTime AS timestamp) BETWEEN t.start_time AND t.end_time)
                   OR 
                   (t.status = 'DAILY' AND 
                       CAST(:currentDateTime AS date) BETWEEN CAST(t.start_time AS date) AND CAST(t.end_time AS date) AND
                       EXTRACT(HOUR FROM CAST(:currentDateTime AS timestamp)) = EXTRACT(HOUR FROM t.start_time) AND
                       EXTRACT(MINUTE FROM CAST(:currentDateTime AS timestamp)) BETWEEN EXTRACT(MINUTE FROM t.start_time) AND EXTRACT(MINUTE FROM t.end_time))
               )
           """, nativeQuery = true)
    List<TxnBlackoutMaster> findCurrentBlackoutTransactions(@Param("currentDateTime") LocalDateTime currentDateTime);
}
Editor is loading...
Leave a Comment