Untitled

 avatar
wyc1230
plain_text
4 months ago
2.4 kB
6
Indexable
	@Override
	public List<MultiShipmentItemOfBatchDTO> findShipmentItemListOfBatchByProcessPathGroupId(Long processPathGroupId) {
		StringBuilder nativeQuery = new StringBuilder(1500);
		nativeQuery
			.append("SELECT shipment.id AS shipmentId, ")
			.append("shipmentCutline.shippedCutline, ")
			.append("batch.id AS batchId, batch.createdAt AS batchCreatedAt, batch.streamQueueFinished AS batchFinished, batch.rebinWallId, ")
			.append("shipmentItem.id AS shipmentItemId, shipmentItem.skuId, ")
			.append("pickingAssignInfo.id AS pickingAssignInfoId, pickingAssignInfo.locationId, pickingAssignInfo.quantity, ")
			.append("pickingAssignInfo.directedMoveId, batchItem.batchItemStatus, batchJob.id AS batchJobId, ")
			.append("batchJob.batchStatus AS batchJobStatus, batchJob.userLoginId, batchJob.streamSubQueueFinished AS batchJobFinished, ")
			.append("pickingAssignInfo.locationType AS locationType ")
			.append("batchItem.createdAt AS batchItemCreatedAt");


		nativeQuery.append("FROM batchs batch ")
			.append("INNER JOIN process_paths processPath USE INDEX (process_paths_idx04) ON batch.processPathId=processPath.id ")
			.append("INNER JOIN shipment_info shipmentInfo ON batch.id=shipmentInfo.batchId ")
			.append("INNER JOIN shipments shipment ON shipment.id=shipmentInfo.shipmentId ")
			.append("INNER JOIN shipment_cutlines shipmentCutline ON shipment.id=shipmentCutline.shipmentId ")
			.append("INNER JOIN shipment_items shipmentItem ON shipment.id=shipmentItem.shipmentId ")
			.append("INNER JOIN picking_assign_info pickingAssignInfo ON shipmentItem.id=pickingAssignInfo.shipmentItemId ")
			.append("LEFT JOIN batch_items batchItem ON batchItem.directedMoveId=pickingAssignInfo.directedMoveId ")
			.append("LEFT JOIN batch_jobs batchJob ON batchJob.id=batchItem.batchJobId ");

		nativeQuery.append("WHERE processPath.groupId = :processPathGroupId ")
			.append("AND batch.batchStatus IN (:batchStatusList) ")
			//			.append("AND pickingAssignInfo.alive = TRUE ")
			.append("ORDER BY shipmentCutline.shippedCutline ASC, shipment.id ASC ");

		Query query = this.getEntityManager().createNativeQuery(nativeQuery.toString());
		query.setParameter("processPathGroupId", processPathGroupId);
		query.setParameter("batchStatusList", Lists.newArrayList(BatchJob.BatchStatus.PENDING.name(),
			BatchJob.BatchStatus.PICKING.name()));

		return new JpaResultMapper().list(query, MultiShipmentItemOfBatchDTO.class);
	}
Editor is loading...
Leave a Comment