[프로젝트] 웹 siCloud - day15

오류 및 문제점

1. 테이블 목록의 최솟값을 where 조건으로 사용하기

• 문제점: 계약이 끝나는 가장 빠른 날짜를 불러 조건에 추가해야 한다.
• 해결 방안: 서브쿼리를 이용해 가장 빠른 날짜를 가져와 비교한다.

SELECT * 
FROM threepl 
	JOIN product_group 
	ON threepl.product_group_no = product_group.product_group_no 
	JOIN (SELECT threepl_no, IFNULL(MIN(end_date), SYSDATE()) AS end_date 
		FROM matching 
			RIGHT OUTER JOIN warehouse 
			ON matching.warehouse_no = warehouse.warehouse_no 
		GROUP BY warehouse.threepl_no) AS dateinfo 
	ON threepl.threepl_no = dateinfo.threepl_no 
WHERE product_group.group_name LIKE CONCAT('%', :productGroup, '%')	 
AND threepl.address LIKE CONCAT('%', :address, '%') 
AND threepl.fee <= :fee 
AND end_date <= DATE_ADD(SYSDATE(), INTERVAL :contractPeriod MONTH) 
ORDER BY end_date, company_name

진행 상황

1. 매칭 계약 실패 조건 추가

• 나의 창고가 모두 찬 경우
• 다른 3PL과 이미 계약된 경우
• 나와 이미 계약된 경우

// MatchingService.java
Long sellerNo = matchingDTO.getSellerNo();
Seller seller = sellerService.findById(sellerNo);	
Warehouse warehouse = warehouseService.findByLocation(matchingDTO.getLocation(), matchingDTO.getThreePLNo());	
Matching tmpMatching = matchingRepository.findBySeller_SellerNo(sellerNo);
		
// 창고가 없는 경우, 남은 자리가 없는 경우 실패
if(warehouse == null || (warehouse.getThreePL().getCntContracted() == warehouse.getThreePL().getCntTotal())) return false;
// 다른 사람과 계약된 경우 실패
if(tmpMatching != null) return false;
// 나와 이미 계약된 경우 실패
if(tmpMatching != null && (tmpMatching.getSeller() == seller)) return false;

2. 3PL 상세 조회

• exportCnt: 출고건수, 계약된 화주사의 모든 출고건수의 합
• endDate: 가장 빨리 만료되는 계약일

// ThreePLService.java
public ThreePLDTO threePLDetail(Long threePLNo) {
	ThreePL threepl = threePLRepository.findById(threePLNo).orElse(null);
	List<Matching> matchings = matchingRepository.findByWarehouse_ThreePL_ThreePLNo(threePLNo);
	List<MatchingDTO> matchingDTOs = new ArrayList<>();
	long exportCnt = 0;
	Timestamp endDate = null;
		
	for(Matching matching: matchings) {
		exportCnt += exportsService.getNumberOfSalesYearly(matching.getSeller().getSellerNo(), TimestampUtil.getLastYear() + 1);
		MatchingDTO matchingDTO = matching.getSeller().toMatchingSellerDTO(matching);
		matchingDTOs.add(matchingDTO);
			
		if(endDate == null) {
			endDate = matching.getEndDate();
		} else {
			if(endDate.compareTo(matching.getEndDate()) > 0) {
				endDate = matching.getEndDate();
			}
		}
	}
		
	return threepl.toThreePLDTO(endDate == null ? null : TimestampUtil.convertTimestampToDate(endDate), exportCnt, matchingDTOs);
}

3. 매칭 검색 조건에 해당하는 3PL 조회

• 상품군
• 주소
• 100박스당 사용료
• 계약 끝 여부 (0: 당장 계약 가능, 1: 한 달 후 계약 종료)

// ThreePLRepository.java
@Query(value = "SELECT * "
		+ "FROM threepl "
		+ "	JOIN product_group "
		+ "	ON threepl.product_group_no = product_group.product_group_no "
		+ "	JOIN (SELECT threepl_no, IFNULL(MIN(end_date), SYSDATE()) AS end_date "
		+ "		FROM matching "
		+ "		RIGHT OUTER JOIN warehouse "
		+ "		ON matching.warehouse_no = warehouse.warehouse_no "
		+ "		GROUP BY warehouse.threepl_no) AS dateinfo "
		+ "	ON threepl.threepl_no = dateinfo.threepl_no "
		+ "WHERE product_group.group_name LIKE CONCAT('%', :productGroup, '%')	 "
		+ "AND threepl.address LIKE CONCAT('%', :address, '%') "
		+ "AND threepl.fee <= :fee "
		+ "AND end_date <= DATE_ADD(SYSDATE(), INTERVAL :contractPeriod MONTH) "
		+ "ORDER BY end_date, company_name", nativeQuery = true)
public Page<ThreePL> findByMatchingCondition(@Param("productGroup") String productGroup, @Param("address") String address,
		@Param("fee") long fee, @Param("contractPeriod") int contractPeriod, Pageable pageable);
        
// ThreePLService.java
public MatchingListDTO searchingThreePLByCondition(MatchingConditionDTO matchingConditionDTO) {
	List<MatchingDTO> matchingDTOs = new ArrayList<>();
	Page<ThreePL> findedThreePLs = threePLService.findByMatchingCondition(matchingConditionDTO);
		
	for(ThreePL findedThreePL : findedThreePLs) {
		List<Matching> matchings = matchingRepository.findByWarehouse_ThreePL_ThreePLNo(findedThreePL.getThreePLNo());
		Timestamp endDate = null;
			
		for(Matching matching : matchings) {
			if(endDate == null) {
				endDate = matching.getEndDate();
			} else {
				if(endDate.compareTo(matching.getEndDate()) > 0) {
					endDate = matching.getEndDate();
				}
			}
		}
			
		MatchingDTO matchingDTO = findedThreePL.toMatchingThreePLDTO(endDate == null ? null :TimestampUtil.convertTimestampToDate(endDate));
		matchingDTOs.add(matchingDTO);
	}
	MatchingListDTO matchingListDTO = MatchingListDTO
			.builder()
			.totalPage(findedThreePLs.getTotalPages())
			.matchingCompanies(matchingDTOs)
			.build();
		
	return matchingListDTO;
}