Package es.kukenan.smartfi.microservice.smartficore.repository

Interface ContractRepository

All Superinterfaces:

org.springframework.data.repository.CrudRepository<Contract,String>, org.springframework.data.jpa.repository.JpaRepository<Contract,String>, org.springframework.data.jpa.repository.JpaSpecificationExecutor<Contract>, org.springframework.data.repository.PagingAndSortingRepository<Contract,String>, org.springframework.data.repository.query.QueryByExampleExecutor<Contract>, org.springframework.data.repository.Repository<Contract,String>

@Repository
public interface ContractRepository
extends org.springframework.data.jpa.repository.JpaRepository<Contract,String>, org.springframework.data.jpa.repository.JpaSpecificationExecutor<Contract>

Repository for queries for Contract Skyline.

Method Summary

Modifier and Type	Method	Description
Long	countContractByStateNameAndProductAndCompanyId(String contractStateName, String product, Integer companyId)	Count contracts by state, product and company.
List<Contract>	findAllByProductAndContractState(String product, String state)	Find all contracts by product and state.
List<Contract>	findAllByUserId(String userId)	Select registers by user id.
List<Contract>	findAllByUserIdAndContractStateList(String userId, List<ContractState> states)	Select registers by user id.
List<Contract>	findAllByUserIdAndProductAndContractStateList(String userId, String product, List<ContractState> states)	Select active contract by user id.
List<Contract>	findAllByUserIdAndProductAndContractStateListByFortnight(String userId, String product, List<ContractState> states)	Select active contract by user id in actual Fortnight.
List<Contract>	findAllPendingConciliationByCompany(Integer companyId, String product)	Get all Contracts pending for conciliation.
Contract	findByIdAndCoreUserId(String id, String userId)	Select contract detail by id and user id.
es.kukenan.smartfi.microservice.backoffice.dtos.core.conciliation.ConciliationFileDto	findPendingConciliationByCompany(Integer companyId, String product)	Calculate the company pending conciliation data.
Float	sumContractAmountByStateNameAndProductAndCompanyId(String contractStateName, String product, Integer companyId)	Sum contracts amount by state, product and company.

Methods inherited from interface org.springframework.data.repository.CrudRepository

count, delete, deleteAll, deleteAll, deleteAllById, deleteById, existsById, findById, save

Methods inherited from interface org.springframework.data.jpa.repository.JpaRepository

deleteAllByIdInBatch, deleteAllInBatch, deleteAllInBatch, deleteInBatch, findAll, findAll, findAll, findAll, findAllById, flush, getById, getOne, getReferenceById, saveAll, saveAllAndFlush, saveAndFlush

Methods inherited from interface org.springframework.data.jpa.repository.JpaSpecificationExecutor

count, exists, findAll, findAll, findAll, findOne

Methods inherited from interface org.springframework.data.repository.PagingAndSortingRepository

findAll

Methods inherited from interface org.springframework.data.repository.query.QueryByExampleExecutor

count, exists, findAll, findBy, findOne

Method Details

findAllByUserId

@Query("SELECT c FROM Contract c WHERE c.coreUserId.id = :userId")
List<Contract> findAllByUserId(@Param("userId")
 String userId)

Select registers by user id.

Parameters:

userId - name for backoffice.

Returns:

new contract object from database

findByIdAndCoreUserId

@Query("SELECT c FROM Contract c WHERE c.id = :id AND c.coreUserId.id = :userId")
Contract findByIdAndCoreUserId(@Param("id")
 String id,
 @Param("userId")
 String userId)

Select contract detail by id and user id.

Parameters:

id - contract identifier.

userId - user identifier.

Returns:

Contract object from database.

findAllByUserIdAndContractStateList

@Query("SELECT c FROM Contract c WHERE c.coreUserId.id = :userId AND c.contractState in :states")
List<Contract> findAllByUserIdAndContractStateList(@Param("userId")
 String userId,
 @Param("states")
 List<ContractState> states)

Select registers by user id.

Parameters:

userId - name for backoffice.

states - list of ContractState to filter.

Returns:

new contract object from database

findAllByUserIdAndProductAndContractStateList

@Query("SELECT c FROM Contract c WHERE c.coreUserId.id = :userId AND c.product = :product AND c.contractState in :state")
List<Contract> findAllByUserIdAndProductAndContractStateList(@Param("userId")
 String userId,
 @Param("product")
 String product,
 @Param("state")
 List<ContractState> states)

Select active contract by user id.

Parameters:

userId - name for backoffice.

product - product type.

states - list of ContractState to filter.

Returns:

new contract object from database

findAllByUserIdAndProductAndContractStateListByFortnight

@Query(value="SELECT c.* FROM contract c WHERE CASE WHEN EXTRACT(\'Day\' FROM now()) < 14 THEN     EXTRACT(\'Day\' FROM c.create_date) <= 13 ELSE     EXTRACT(\'Day\' FROM c.create_date) > 13 END AND EXTRACT(\'Month\' FROM c.create_date) = EXTRACT(\'Month\' FROM now()) AND c.core_user_id = :userId AND c.product = :product AND c.contract_state_id in :state",
       nativeQuery=true)
List<Contract> findAllByUserIdAndProductAndContractStateListByFortnight(@Param("userId")
 String userId,
 @Param("product")
 String product,
 @Param("state")
 List<ContractState> states)

Select active contract by user id in actual Fortnight.

Parameters:

userId - name for backoffice.

product - product type.

states - list of ContractState to filter.

Returns:

list of contracts

findAllByProductAndContractState

@Query("SELECT c FROM Contract c WHERE c.product = :product AND c.contractState.name = :state")
List<Contract> findAllByProductAndContractState(@Param("product")
 String product,
 @Param("state")
 String state)

Find all contracts by product and state.

Parameters:

product - type of product

state - state name

Returns:

list of Contracts

countContractByStateNameAndProductAndCompanyId

@Query("SELECT count(c.id) FROM Contract c WHERE c.contractState.name = :contractStateName AND c.product = :product AND c.userCompany.company.id = :companyId")
Long countContractByStateNameAndProductAndCompanyId(@Param("contractStateName")
 String contractStateName,
 @Param("product")
 String product,
 @Param("companyId")
 Integer companyId)

Count contracts by state, product and company.

Parameters:

contractStateName - contract state name.

product - product name.

companyId - company identifier.

Returns:

contracts counted

sumContractAmountByStateNameAndProductAndCompanyId

@Query("SELECT sum(c.amount) FROM Contract c WHERE c.contractState.name = :contractStateName AND c.product = :product AND c.userCompany.company.id = :companyId")
Float sumContractAmountByStateNameAndProductAndCompanyId(@Param("contractStateName")
 String contractStateName,
 @Param("product")
 String product,
 @Param("companyId")
 Integer companyId)

Sum contracts amount by state, product and company.

Parameters:

contractStateName - contract state name.

product - product name.

companyId - company identifier.

Returns:

total contracts amount.

findPendingConciliationByCompany

@Query("SELECT NEW es.kukenan.smartfi.microservice.backoffice.dtos.core.conciliation.ConciliationFileDto(COUNT(c.id), SUM(c.amount), MIN(c.createDate)) FROM Contract c LEFT JOIN UserCompany uc ON c.userCompany.id = uc.id LEFT JOIN Company cmp ON cmp.id = uc.company.id WHERE c.conciliation IS NULL AND c.product = :product   AND c.contractState.id not in (SELECT cs.id FROM ContractState cs WHERE cs.product = :product AND (cs.name = \'REJECTED\' OR cs.name = \'FINISHED\'))   AND cmp.id = :companyId")
es.kukenan.smartfi.microservice.backoffice.dtos.core.conciliation.ConciliationFileDto findPendingConciliationByCompany(@Param("companyId")
 Integer companyId,
 @Param("product")
 String product)

Calculate the company pending conciliation data.

Parameters:

companyId - Company identifier.

product - product type.

Returns:

Conciliation data.

findAllPendingConciliationByCompany

@Query("SELECT c FROM Contract c LEFT JOIN UserCompany uc ON c.userCompany.id = uc.id LEFT JOIN Company cmp ON cmp.id = uc.company.id WHERE c.conciliation IS NULL AND c.product = :product   AND c.contractState.id not in (SELECT cs.id FROM ContractState cs WHERE cs.product = :product AND (cs.name = \'REJECTED\' OR cs.name = \'FINISHED\'))   AND cmp.id = :companyId")
List<Contract> findAllPendingConciliationByCompany(@Param("companyId")
 Integer companyId,
 @Param("product")
 String product)

Get all Contracts pending for conciliation.

Parameters:

companyId - Company identifier.

product - product type.

Returns:

Contract list.