From a9a938c824f1be17cd2c3cdb02e048f7e9d7eec7 Mon Sep 17 00:00:00 2001 From: =?utf8?q?V=C3=A1s=C3=A1ry=20D=C3=A1niel?= Date: Tue, 27 Feb 2024 12:29:13 +0100 Subject: [PATCH] XLS support added --- .../user/mcvodsync/AssetImportExportBase.java | 8 +- mc-vod-sync/mc-vod-sync-service/pom.xml | 15 ++ ...rocessor.java => VodFastXlsProcessor.java} | 5 +- .../service/in/VodXlsPOIProcessor.java | 148 ++++++++++++++++++ .../mcvodsync/service/in/XlsProcessor.java | 9 ++ .../service/schedule/ScheduledImport.java | 8 +- 6 files changed, 186 insertions(+), 7 deletions(-) rename mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/{VodXlsProcessor.java => VodFastXlsProcessor.java} (96%) create mode 100644 mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodXlsPOIProcessor.java create mode 100644 mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/XlsProcessor.java diff --git a/mc-vod-sync/mc-vod-sync-app/src/test/java/hu/user/mcvodsync/AssetImportExportBase.java b/mc-vod-sync/mc-vod-sync-app/src/test/java/hu/user/mcvodsync/AssetImportExportBase.java index 43e93735..b34b0f19 100644 --- a/mc-vod-sync/mc-vod-sync-app/src/test/java/hu/user/mcvodsync/AssetImportExportBase.java +++ b/mc-vod-sync/mc-vod-sync-app/src/test/java/hu/user/mcvodsync/AssetImportExportBase.java @@ -18,12 +18,13 @@ import hu.user.mcvodsync.db.repository.PlaylistSyncRepository; import hu.user.mcvodsync.service.data.CompositeSummary; import hu.user.mcvodsync.service.data.Summary; import hu.user.mcvodsync.service.in.AssetImportService; -import hu.user.mcvodsync.service.in.VodXlsProcessor; +import hu.user.mcvodsync.service.in.XlsProcessor; import hu.user.mcvodsync.service.out.SyncProcessor; import hu.user.mcvodsync.service.out.VideoMapper; import lombok.extern.log4j.Log4j2; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.beans.factory.annotation.Qualifier; import java.io.IOException; import java.nio.file.Files; @@ -41,7 +42,8 @@ import static org.junit.Assert.assertNotNull; @Log4j2 public class AssetImportExportBase { @Autowired - private VodXlsProcessor vodXlsProcessor; + @Qualifier("fastXLSProcessor") + private XlsProcessor xlsProcessor; @Autowired private AssetRepository assetRepository; @@ -173,7 +175,7 @@ public class AssetImportExportBase { CompositeSummary summary; try { Path input = Paths.get(xlsFile); - summary = vodXlsProcessor.process(input.getFileName().toString(), Files.readAllBytes(input)); + summary = xlsProcessor.process(input.getFileName().toString(), Files.readAllBytes(input)); } catch (IOException e) { log.error(e); throw new RuntimeException(e); diff --git a/mc-vod-sync/mc-vod-sync-service/pom.xml b/mc-vod-sync/mc-vod-sync-service/pom.xml index b00502b7..8138e724 100644 --- a/mc-vod-sync/mc-vod-sync-service/pom.xml +++ b/mc-vod-sync/mc-vod-sync-service/pom.xml @@ -53,6 +53,21 @@ 0.0.1-SNAPSHOT compile + + commons-io + commons-io + 2.15.1 + + + org.apache.poi + poi + 5.2.5 + + + org.apache.poi + poi-ooxml + 5.2.5 + diff --git a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodXlsProcessor.java b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodFastXlsProcessor.java similarity index 96% rename from mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodXlsProcessor.java rename to mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodFastXlsProcessor.java index 47309637..c5aa8aee 100644 --- a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodXlsProcessor.java +++ b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodFastXlsProcessor.java @@ -9,6 +9,7 @@ import org.dhatim.fastexcel.reader.ReadableWorkbook; import org.dhatim.fastexcel.reader.Row; import org.dhatim.fastexcel.reader.Sheet; import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.stereotype.Component; import org.springframework.transaction.annotation.Transactional; @@ -26,7 +27,8 @@ import java.util.stream.Stream; @Log4j2 @Component -public class VodXlsProcessor { +@Qualifier("fastXLSProcessor") +public class VodFastXlsProcessor implements XlsProcessor { private List headers; @Autowired @@ -37,6 +39,7 @@ public class VodXlsProcessor { @Transactional + @Override public CompositeSummary process(String fileName, byte[] xlsData) { CompositeSummary summary = CompositeSummary.builder().started(Instant.now()).build(); try (InputStream is = new ByteArrayInputStream(xlsData); diff --git a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodXlsPOIProcessor.java b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodXlsPOIProcessor.java new file mode 100644 index 00000000..af9b1aef --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/VodXlsPOIProcessor.java @@ -0,0 +1,148 @@ +package hu.user.mcvodsync.service.in; + +import hu.user.mcvodsync.db.Asset; +import hu.user.mcvodsync.service.data.CompositeSummary; +import lombok.extern.log4j.Log4j2; +import org.apache.commons.lang3.StringUtils; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.beans.factory.annotation.Qualifier; +import org.springframework.stereotype.Component; +import org.springframework.transaction.annotation.Transactional; + +import java.io.ByteArrayInputStream; +import java.io.IOException; +import java.io.InputStream; +import java.time.Instant; +import java.time.LocalDate; +import java.util.*; +import java.util.stream.Collectors; +import java.util.stream.StreamSupport; + +@Log4j2 +@Component +@Qualifier("poiXLSProcessor") +public class VodXlsPOIProcessor implements XlsProcessor { + private List headers; + + @Autowired + private AssetMapper assetMapper; + + @Autowired + private AssetImportService assetImportService; + + + @Transactional + public CompositeSummary process(String fileName, byte[] xlsData) { + CompositeSummary summary = CompositeSummary.builder().started(Instant.now()).build(); + try (InputStream is = new ByteArrayInputStream(xlsData); + Workbook wb = getWorkbook(fileName, is)) { + Sheet sheet = wb.getSheetAt(0); + + Iterator rowIterator = sheet.iterator(); + while (rowIterator.hasNext()) { + Row row = rowIterator.next(); + + if (row.getRowNum() == 0) { + assetImportService.prepare(LocalDate.now()); + headers = getHeaders(row); + } else { + log.info("Processing {}", row.getRowNum()); + summary.getVideo().incAll(); + processRow(row, summary); + } + + } + assetImportService.processPlaylists(summary); + + } catch (IOException e) { + log.error("Excel file reading error from {}. System message: {}", fileName, e.getMessage()); + } + summary.setFinished(Instant.now()); + log.info(summary); + return summary; + } + + private Workbook getWorkbook(String fileName, InputStream is) throws IOException { + if (fileName.toUpperCase().endsWith("XLS")) { + return new HSSFWorkbook(is); + } else { + return new XSSFWorkbook(is); + } + } + + private List getHeaders(Row row) { + Iterator cellIterator = row.cellIterator(); + Iterable iterable = () -> cellIterator; + return StreamSupport.stream(iterable.spliterator(), false).collect(Collectors.toList()); + } + + private void processRow(Row row, CompositeSummary summary) { + Map rowData = new HashMap<>(); + Iterator cellIterator = row.cellIterator(); + Iterable iterable = () -> cellIterator; + + StreamSupport.stream(iterable.spliterator(), false).filter(Objects::nonNull).forEach(c -> { + try { + String header = headers.get(c.getColumnIndex()).getStringCellValue(); + + String data = null; + switch (c.getCellType()) { + case NUMERIC: + data = String.valueOf(c.getNumericCellValue()); + break; + default: + data = c.getStringCellValue(); + break; + } + rowData.put(header, data); + } catch (Exception e) { + log.error("Cell error!", e); + } + }); + + Asset asset = null; + try { + asset = assetMapper.toEntity(rowData); + if (isAssetValid(asset)) { + assetImportService.processAsset(asset, summary); + } else { + summary.getVideo().incSkip(); + } + summary.getVideo().incSuccess(); + } catch (Exception e) { + summary.getVideo().incError(); + log.error("Processing error for {}", asset, e); + } + } + + private boolean isAssetValid(Asset asset) { + if (StringUtils.isBlank(asset.getCatalogId())) { + log.warn("Catalog ID not defined for {}", asset); + return false; + } + if (StringUtils.isBlank(asset.getPlaylist())) { + log.warn("Playlist not defined for {}", asset); + return false; + } + if (StringUtils.isBlank(asset.getProgLocalTitle())) { + log.warn("Prog local title not defined for {}", asset); + return false; + } + if (Objects.isNull(asset.getSunset())) { + log.warn("Sunset not defined for {}", asset); + return false; + } + if (Objects.isNull(asset.getSunrise())) { + log.warn("Sunrise not defined for {}", asset); + return false; + } + return true; + } + +} diff --git a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/XlsProcessor.java b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/XlsProcessor.java new file mode 100644 index 00000000..151572f2 --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/in/XlsProcessor.java @@ -0,0 +1,9 @@ +package hu.user.mcvodsync.service.in; + +import hu.user.mcvodsync.service.data.CompositeSummary; +import org.springframework.transaction.annotation.Transactional; + +public interface XlsProcessor { + @Transactional + CompositeSummary process(String fileName, byte[] xlsData); +} diff --git a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/schedule/ScheduledImport.java b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/schedule/ScheduledImport.java index bba8f9e6..53eca892 100644 --- a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/schedule/ScheduledImport.java +++ b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/schedule/ScheduledImport.java @@ -5,9 +5,10 @@ import hu.user.mcvodsync.db.repository.UploadFileRepository; import hu.user.mcvodsync.service.data.CompositeSummary; import hu.user.mcvodsync.service.event.ImportCompletedEvent; import hu.user.mcvodsync.service.event.ImportStartedEvent; -import hu.user.mcvodsync.service.in.VodXlsProcessor; +import hu.user.mcvodsync.service.in.XlsProcessor; import lombok.extern.log4j.Log4j2; import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.ApplicationEventPublisher; import org.springframework.stereotype.Component; @@ -23,7 +24,8 @@ public class ScheduledImport implements Runnable { private UploadFileRepository uploadFileRepository; @Autowired - private VodXlsProcessor vodXlsProcessor; + @Qualifier("poiXLSProcessor") + private XlsProcessor xlsProcessor; @Override public void run() { @@ -33,7 +35,7 @@ public class ScheduledImport implements Runnable { if (opUploadFile.isPresent()) { applicationEventPublisher.publishEvent(new ImportStartedEvent(this, opUploadFile.get().getName())); log.info("ScheduledImport started from {}", opUploadFile.get().getName()); - summary = vodXlsProcessor.process(opUploadFile.get().getName(), opUploadFile.get().getFile()); + summary = xlsProcessor.process(opUploadFile.get().getName(), opUploadFile.get().getFile()); } } catch (Exception e) { log.error("ScheduledImport error!", e); -- 2.54.0