src/main/java/org/eclipse/openk/contactbasedata/repository/DetailedContactRepository.java

/*
 *******************************************************************************
 * Copyright (c) 2019 Contributors to the Eclipse Foundation
 *
 * See the NOTICE file(s) distributed with this work for additional
 * information regarding copyright ownership.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0.
 *
 * SPDX-License-Identifier: EPL-2.0
 *******************************************************************************
 */
package org.eclipse.openk.contactbasedata.repository;

import org.eclipse.openk.contactbasedata.model.VwDetailedContact;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.UUID;

public interface DetailedContactRepository extends PagingAndSortingRepository<VwDetailedContact, Long> {
    Page<VwDetailedContact> findAll(Pageable pageable);

    @Query( "select dc from VwDetailedContact dc "
            + " where (COALESCE(:contactType) is null or COALESCE(:contactType) is not null AND dc.contactType=:contactType)"
            + " and (COALESCE(:personType) is null or COALESCE(:personType) is not null AND dc.personTypeUuid=:personType)"
            + " and (COALESCE(:searchText) is null or COALESCE(:searchText) is not null AND dc.searchfield like %:searchText%)"
            + " and (COALESCE(:moduleName) is null or COALESCE(:moduleName) is not null AND dc.fkContactId in (select distinct amc.tblContact.id from TblAssignmentModulContact amc where amc.modulName=:moduleName))"
            + " and (COALESCE(:withoutModule) = false or COALESCE(:withoutModule) = true AND dc.fkContactId not in "
            + "        (select distinct amc.tblContact.id from TblAssignmentModulContact amc)) "
            + " and (COALESCE(:dateTimeNow) is null or COALESCE(:dateTimeNow) is not null AND dc.fkContactId in "
            + "        (select distinct amc.tblContact.id from TblAssignmentModulContact amc where "
            + "           amc.expiringDate is not null and amc.expiringDate < :dateTimeNow and "
            + "          (amc.deletionLockUntil is null or amc.deletionLockUntil is not null and amc.deletionLockUntil < :dateTimeNow )))"
    )
    Page<VwDetailedContact> findByFilter(@Param("contactType")String contactType,
                                         @Param("personType") UUID personType,
                                         @Param("searchText") String searchText,
                                         @Param("moduleName") String moduleName,
                                         @Param("withoutModule") boolean withoutModule,
                                         @Param("dateTimeNow") Date dateTimeNow,
                                         Pageable pageable);
}