package com.sonymusic.dx.dxadvsearchservice.service; import java.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.time.LocalDate; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.sonymusic.dx.dxadvsearchservice.dto.DataSourceDTO; import com.sonymusic.dx.dxadvsearchservice.dto.GenreDTO; import com.sonymusic.dx.dxadvsearchservice.dto.LocalProductStrategyDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductClassDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductCompanyDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductConfigurationDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductLabelDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductParticipantDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductPublishStatusDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductPurposeDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductRepOwnerDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductTitleLanguageDTO; import com.sonymusic.dx.dxadvsearchservice.dto.ProductVersionDTO; import com.sonymusic.dx.dxadvsearchservice.model.ListModel; import com.sonymusic.dx.dxadvsearchservice.model.ProductModel; import com.sonymusic.dx.dxadvsearchservice.searchModel.ProductSearchModel; import com.sonymusic.dx.dxadvsearchservice.util.ConvertString; import com.sonymusic.dx.dxadvsearchservice.util.DxSearchUtil; import com.sonymusic.dx.persistence.gras.Company; import com.sonymusic.dx.persistence.gras.Configuration; import com.sonymusic.dx.persistence.gras.DataSource; import com.sonymusic.dx.persistence.gras.Genre; import com.sonymusic.dx.persistence.gras.Label; import com.sonymusic.dx.persistence.gras.Language; import com.sonymusic.dx.persistence.gras.LocalProduct; import com.sonymusic.dx.persistence.gras.LocalProductVersion; import com.sonymusic.dx.persistence.gras.Participant; import com.sonymusic.dx.persistence.gras.Product; import com.sonymusic.dx.persistence.gras.ProductClass; import com.sonymusic.dx.persistence.gras.ProductPurpose; import com.sonymusic.dx.persistence.gras.ProductVersion; import com.sonymusic.dx.persistence.gras.PublishStatus; import com.sonymusic.dx.persistence.gras.RepertoireOwner; import com.sonymusic.dx.persistence.gras.StrategicReportingGroup; import jakarta.persistence.EntityManager; import jakarta.persistence.PersistenceContext; import jakarta.persistence.criteria.CriteriaBuilder; import jakarta.persistence.criteria.CriteriaQuery; import jakarta.persistence.criteria.Join; import jakarta.persistence.criteria.JoinType; import jakarta.persistence.criteria.Predicate; import jakarta.persistence.criteria.Root; @Service public class ProductService { @PersistenceContext(name = "dx-gras-persistence") private EntityManager em; @Autowired private ConvertString converter; private final Logger log = LoggerFactory.getLogger(getClass()); public ListModel advSearchProduct(ProductSearchModel products, int page, int size) throws ParseException { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery cq1 = cb.createQuery(ProductDTO.class); Root root = cq1.from(Product.class); Join productVersionJoin = root.join("productVersion",JoinType.LEFT); Join productCompanyJoin =root.join("maintenanceOwner",JoinType.LEFT); Join productDataSourceJoin=root.join("dataSource",JoinType.LEFT); Join productConfigurationJoin =root.join("configuration",JoinType.LEFT); Join productPublishStatusJoin =root.join("publishKey",JoinType.LEFT); Join productVersionGenreJoin =productVersionJoin.join("genre",JoinType.LEFT); Join productLabelJoin = productVersionJoin.join("label",JoinType.LEFT); Join productVersionRepertoireOwnerJoin =productVersionJoin.join("repertoireOwner",JoinType.LEFT); Join repertoireOwnerCompanyJoin = productVersionRepertoireOwnerJoin.join("company",JoinType.LEFT); Join productVersionParticipantJoin = productVersionJoin.join("mainArtist",JoinType.LEFT); Join productVersionProductPurposeJoin = productVersionJoin.join("productPurpose",JoinType.LEFT); Join productVersionProductClassJoin =productVersionJoin.join("productClass",JoinType.LEFT); Join productVersionLanguageJoin =productVersionJoin.join("titleLanguage",JoinType.LEFT); Join productVersionLocalProductVersionJoin =productVersionJoin.join("localProductVersions",JoinType.LEFT); Join productVersionStrategicReportingGroupJoin = productVersionJoin.join("strategicReportingGroup",JoinType.LEFT); Join productLocalProductJoin =root.join("localProducts",JoinType.LEFT); Predicate productPredicates = buildPredicates(cb,root,products); cq1.where(productPredicates); cq1.select(cb.construct(ProductDTO.class, root.get("productNumber"), root.get("barcode"), root.get("originalProductNumber"), root.get("digitalTitleSuppl"), root.get("digitalBarcode"), root.get("isComplete"), root.get("setCompleteDate"), root.get("setCompleteUser"), root.get("grid"), root.get("cLine"), root.get("firstRelaseDate"), root.get("catlgPfixId"), root.get("catlgCoreId"), root.get("affiliateId"), cb.construct(ProductVersionDTO.class, productVersionJoin.get("productVersionNumber"), productVersionJoin.get("productTitle"), productVersionJoin.get("productTitleSupplemental"), productVersionJoin.get("sonyRightsExpired")), cb.construct(GenreDTO.class, productVersionGenreJoin.get("genreKey"), productVersionGenreJoin.get("genreName")), cb.construct(DataSourceDTO.class, productDataSourceJoin.get("dataSourceId"), productDataSourceJoin.get("dataSourceName")), cb.construct(ProductLabelDTO.class, productLabelJoin.get("labelName")), cb.construct(ProductCompanyDTO.class, productCompanyJoin.get("companyName")), cb.construct(ProductRepOwnerDTO.class, productVersionRepertoireOwnerJoin.get("repOwnerKey"), repertoireOwnerCompanyJoin.get("companyName")), cb.construct(ProductParticipantDTO.class, productVersionParticipantJoin.get("fullName")), cb.construct(ProductConfigurationDTO.class, productConfigurationJoin.get("configKey"), productConfigurationJoin.get("configName")), cb.construct(ProductPurposeDTO.class, productVersionProductPurposeJoin.get("productPurposeKey"), productVersionProductPurposeJoin.get("productPurposeName")), cb.construct(ProductClassDTO.class, productVersionProductClassJoin.get("productClassKey"), productVersionProductClassJoin.get("productClassName")), cb.construct(ProductPublishStatusDTO.class, productPublishStatusJoin.get("publishKey"), productPublishStatusJoin.get("publishName")), root.get("raasInitiatedDate"), cb.construct(ProductTitleLanguageDTO.class, productVersionLanguageJoin.get("languageKey"), productVersionLanguageJoin.get("languageName")), cb.construct(LocalProductStrategyDTO.class, productVersionStrategicReportingGroupJoin.get("strategicRepoprtingGroupName")) )); cq1.distinct(true); List productDTOS = em.createQuery(cq1) .setFirstResult((page - 1) * size) .setMaxResults(size) .getResultList(); CriteriaQuery countQuery =cb.createQuery(Long.class); Root countRoot =countQuery.from(Product.class); countQuery.select(cb.count(countRoot)); countQuery.where(buildPredicates(cb,countRoot,products)); Long totalRecords =em.createQuery(countQuery).getSingleResult(); List productListModel = new ArrayList<>(); ProductModel productResponseModel = null; for (ProductDTO result : productDTOS){ productResponseModel = new ProductModel(); // add track available on aoma // aoma cover productResponseModel.setComplete(result.isComplete()); productResponseModel.setPublishStatus(result.getPublishStatus().getPublishName()); productResponseModel.setProductNo(result.getProductNumber()); productResponseModel.setMaintenanceOwner(result.getCompanyName().getCompanyName()); productResponseModel.setRepertoireOwner(result.getRepOwnerName().getCompanyName()); // add business unit productResponseModel.setProductConfiguration(result.getConfiguration().getConfigName()); // add parent flor productResponseModel.setMainArtist(result.getParticipantFullName().getFullName()); productResponseModel.setProductTitle(result.getProductVersion().getProductMatchTitle()); productResponseModel.setProductTitleSupplemental(result.getProductVersion().getProductMatchTitleSupplemental()); productResponseModel.setDigitalTitleSupplemental(result.getDigitalTitleSuppl()); // product profit center // project profit center // no of components if (result.getFirstRelaseDate()!=null){ String inputDate = result.getFirstRelaseDate(); SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd"); Date parsedDate = format.parse(inputDate); SimpleDateFormat displayDate = new SimpleDateFormat(DxSearchUtil.DATE_FORMAT_dd_MMM_yyyy); String formattedDate = displayDate.format(parsedDate); productResponseModel.setFirstReleasedDate(formattedDate); } if(result.getCompleteDate()!=null){ Timestamp dbValue = result.getCompleteDate(); LocalDateTime parsedDate = dbValue.toLocalDateTime(); DateTimeFormatter formatter = DateTimeFormatter.ofPattern(DxSearchUtil.DATE_FORMAT_dd_MMM_yyyy); String formattedDate = parsedDate.format(formatter); productResponseModel.setCompleteDate(formattedDate); } productResponseModel.setCompleteUser(result.getCompleteUser()); productResponseModel.setProductPurpose(result.getProductPurpose().getProductPurposeName()); productResponseModel.setImprintLabel(result.getLabel().getLabelName()); productResponseModel.setInitiator(result.getCompanyName().getCompanyName()); productResponseModel.setGenre(result.getGenre().getGenreName()); productResponseModel.setProductClass(result.getProductClass().getProductClassName()); productResponseModel.setBarcode(result.getBarcode()); productResponseModel.setDigBarcode(result.getDigitalBarcode()); productResponseModel.setGridNumber(result.getGrid()); productResponseModel.setcLine(result.getcLine()); productResponseModel.setSonyRightsExpired(result.getProductVersion().getSonyRightsExpired()); productResponseModel.setDataSource(result.getDataSource().getDataSourceName()); // add no of tracks if (result.getRaasInitiatedDate()!=null){ String inputDate = result.getRaasInitiatedDate(); SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd"); Date parsedDate = format.parse(inputDate); SimpleDateFormat displayDate = new SimpleDateFormat(DxSearchUtil.DATE_FORMAT_dd_MMM_yyyy); String formattedDate = displayDate.format(parsedDate); productResponseModel.setRaasInitiatedDate(formattedDate); } // has split deal // split deal no // split deal name // gcls no // distributed rights // recording project // recording project title productResponseModel.setProductTitleLanguage(result.getProductVersion().getProductMatchTitle()); //pline //release company productResponseModel.setStrategicRepGroup(result.getLocalProductStrategy().getStrategicRepoprtingGroupName()); // productResponseModel.setConfigurationCategory(result.getConfiguration); productResponseModel.setCatalogPrefix(result.getCatalogPrefix()); productResponseModel.setCatalogCore(result.getCatalogCore()); productResponseModel.setAffiliate(result.getAffiliate()); productListModel.add(productResponseModel); } ListModel listModel = new ListModel(); listModel.setItems(productListModel); listModel.setTotalCount(totalRecords); listModel.setLimit(size); listModel.setTotalPageCount((int) Math.ceil((double) totalRecords / size)); listModel.setCurPage(page); return listModel; } private Predicate buildPredicates(CriteriaBuilder cb, Root root, ProductSearchModel products) { List predicates = new ArrayList<>(); if (products.getProductNo() != null) { predicates.add(cb.equal(root.get("productNumber"), products.getProductNo())); } if (products.getDigitalTitleSupplemental() != null && !products.getDigitalTitleSupplemental().isEmpty()) { predicates.add(cb.like((root.get("digitalTitleSupplMatch")), converter.convertString(products.getDigitalTitleSupplemental()) + DxSearchUtil.WILD_CARD)); } if (products.getProductTitle()!= null && !products.getProductTitle().isEmpty()) { predicates.add(cb.like((root.get("productVersion").get("productMatchTitle")), converter.convertString(products.getProductTitle()) + DxSearchUtil.WILD_CARD)); } if (products.getProductTitleSupplemental() != null && !products.getProductTitleSupplemental().isEmpty()) { predicates.add(cb.like((root.get("productVersion").get("productMatchTitleSupplemental")), converter.convertString(products.getProductTitleSupplemental()) + DxSearchUtil.WILD_CARD)); } if (products.getMainArtist() != null && !products.getMainArtist().isEmpty()) { predicates.add(cb.like((root.get("productVersion").get("mainArtist").get("fullMatchName")), converter.convertString(products.getMainArtist()) + DxSearchUtil.WILD_CARD)); } if (products.getGenre() != null) { predicates.add(cb.equal(root.get("productVersion").get("genre").get("genreKey"), products.getGenre().getKey())); } if (products.getProductPurpose() != null) { predicates.add(cb.equal(root.get("productVersion").get("productPurpose").get("productPurposeKey"), products.getProductPurpose().getKey())); } if (products.getProductClass() != null) { predicates.add(cb.equal(root.get("productVersion").get("productClass").get("productClassKey"), products.getProductClass().getKey())); } if (products.getMaintenanceOwner() != null) { predicates.add(cb.equal(root.get("maintenanceOwner").get("companyKey"), products.getMaintenanceOwner().getKey())); } if (products.getImprintLabel() != null) { predicates.add(cb.equal(root.get("productVersion").get("label").get("labelName"), products.getImprintLabel().getName())); } if (products.getRepertoireOwner() != null) { predicates.add(cb.equal(root.get("productVersion").get("repertoireOwner").get("company").get("companyKey"), products.getRepertoireOwner().getKey())); } if (products.getProductConfiguration() != null) { predicates.add(cb.equal(root.get("configuration").get("configKey"), products.getProductConfiguration().getKey())); } if (products.getConfigurationCategory() != null) { predicates.add(cb.equal(root.get("configuration").get("configurationCategory").get("configurationCategoryKey"), products.getConfigurationCategory().getKey())); } if (products.getConfigIsDigital()!=null) { if (products.getConfigIsDigital().getName().equals(DxSearchUtil.YES)){ predicates.add(cb.equal(root.get("configuration").get("isDmdConfig"), true)); } else if(products.getConfigIsDigital().getName().equals(DxSearchUtil.NO)) { predicates.add(cb.equal(root.get("configuration").get("isDmdConfig"), false)); } } if (products.isIgnoreAutoGenProd()) { predicates.add(cb.notEqual(root.get("isGeneratedByGrid"), products.isIgnoreAutoGenProd())); } if (products.getComplete()!=null) { if (products.getComplete().getName().equals(DxSearchUtil.YES)){ predicates.add(cb.equal(root.get("isComplete"), true)); } else if (products.getComplete().getName().equals(DxSearchUtil.NO)) { predicates.add(cb.equal(root.get("isComplete"), false)); } } if (products.getPublishStatus() != null) { predicates.add(cb.equal(root.get("publishKey").get("publishKey"), products.getPublishStatus().getKey())); } if (products.getInitiator() != null) { predicates.add(cb.equal(root.get("raasInitiatingCompany").get("companyKey"), products.getInitiator().getKey())); } if (products.isSonyRightsExpired()) { predicates.add(cb.equal(root.get("productVersion").get("sonyRightsExpired"), products.isSonyRightsExpired())); } if (products.getBarcode() != null && !products.getBarcode().isEmpty()) { predicates.add(cb.like(root.get("barcode"), products.getBarcode() + DxSearchUtil.WILD_CARD)); } if (products.getGridNumber() != null && !products.getGridNumber().isEmpty()) { predicates.add(cb.equal(root.get("grid"), products.getGridNumber())); } if (products.getDataSource() != null) { predicates.add(cb.equal(root.get("dataSource").get("dataSourceId"), products.getDataSource().getKey())); } if (products.getCatalogPrefix() != null && !products.getCatalogPrefix().isEmpty()) { predicates.add(cb.equal(root.get("catlgPfixId"), products.getCatalogPrefix())); } if (products.getCatalogCore() != null && !products.getCatalogCore().isEmpty()) { predicates.add(cb.equal(root.get("catlgCoreId"), products.getCatalogCore())); } if (products.getAffiliate() != null && !products.getAffiliate().isEmpty()) { predicates.add(cb.equal(root.get("affiliateId"), products.getAffiliate())); } if (products.getProductTitleLanguage() != null) { predicates.add(cb.equal(root.get("productVersion").get("titleLanguage").get("languageKey"), products.getProductTitleLanguage().getKey())); } DateTimeFormatter inputFormat = DateTimeFormatter.ofPattern("d MMM yyyy"); DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd"); if (products.getFirstReleaseFromDate() != null && products.getFirstReleaseToDate() != null) { LocalDate parsedFromDate = LocalDate.parse(products.getFirstReleaseFromDate(), inputFormat); LocalDate parsedToDate = LocalDate.parse(products.getFirstReleaseToDate(), inputFormat); String formattedFromDate = parsedFromDate.format(formatter); String formattedToDate = parsedToDate.format(formatter); predicates.add(cb.between(root.get("firstRelaseDate"), formattedFromDate, formattedToDate)); } else if (products.getFirstReleaseFromDate() != null) { LocalDate fromDate = LocalDate.parse(products.getFirstReleaseFromDate(), formatter); String formattedFromDate = fromDate.format(formatter); predicates.add(cb.greaterThanOrEqualTo(root.get("firstRelaseDate"), formattedFromDate)); } else if (products.getFirstReleaseToDate() != null) { LocalDate toDate = LocalDate.parse(products.getFirstReleaseToDate(), formatter); String formattedToDate = toDate.format(formatter); predicates.add(cb.lessThanOrEqualTo(root.get("firstRelaseDate"), formattedToDate)); } if (products.getRaasInitiatedFromDate() != null && products.getRaasInitiatedToDate() != null) { LocalDate parsedFromDate = LocalDate.parse(products.getRaasInitiatedFromDate(), inputFormat); LocalDate parsedToDate = LocalDate.parse(products.getRaasInitiatedToDate(), inputFormat); String formattedFromDate = parsedFromDate.format(formatter); String formattedToDate = parsedToDate.format(formatter); predicates.add(cb.between(root.get("raasInitiatedDate"), formattedFromDate, formattedToDate)); } else if (products.getRaasInitiatedFromDate() != null) { LocalDate fromDate = LocalDate.parse(products.getRaasInitiatedFromDate(), formatter); String formattedFromDate = fromDate.format(formatter); predicates.add(cb.greaterThanOrEqualTo(root.get("raasInitiatedDate"), formattedFromDate)); } else if (products.getRaasInitiatedToDate() != null) { LocalDate toDate = LocalDate.parse(products.getRaasInitiatedToDate(), formatter); String formattedToDate = toDate.format(formatter); predicates.add(cb.lessThanOrEqualTo(root.get("raasInitiatedDate"), formattedToDate)); } if (products.getCompleteBetweenFromDate() != null && products.getCompleteBetweenToDate() != null) { LocalDate fromDateTime = LocalDate.parse(products.getCompleteBetweenFromDate(), inputFormat); LocalDate toDateTime = LocalDate.parse(products.getCompleteBetweenToDate(), inputFormat); Timestamp fromTs = Timestamp.valueOf(fromDateTime.atStartOfDay()); Timestamp toTs = Timestamp.valueOf(toDateTime.atTime(23,59,59)); predicates.add(cb.between(root.get("setCompleteDate"), fromTs, toTs)); } else if (products.getCompleteBetweenFromDate() != null) { LocalDate fromDateTime = LocalDate.parse(products.getCompleteBetweenFromDate(), inputFormat); Timestamp fromTs =Timestamp.valueOf(fromDateTime.atStartOfDay()); predicates.add(cb.greaterThanOrEqualTo(root.get("setCompleteDate"), fromTs)); } else if (products.getCompleteBetweenToDate() != null) { LocalDate toDateTime = LocalDate.parse(products.getCompleteBetweenToDate(), inputFormat); Timestamp toTs =Timestamp.valueOf(toDateTime.atTime(23,59,59)); predicates.add(cb.lessThanOrEqualTo(root.get("setCompleteDate"),toTs)); } return cb.and(predicates.toArray(new Predicate[0])); } }