/* * File: src/main/java/reports/service/SalesService.java * Description: This service class provides functionality for handling sales-related operations in the ERP reporting system. * It includes methods for retrieving KPI data and generating sales reports. */ package com.nebula.erp.reports.service; import com.nebula.erp.reports.model.sales.SalesItem; import com.nebula.erp.reports.model.sales.SalesReturnItem; import com.nebula.erp.reports.repository.product.BrandRepository; import com.nebula.erp.reports.repository.product.CategoryRepository; import com.nebula.erp.reports.repository.product.FeesRepository; import com.nebula.erp.reports.repository.purchase.*; import com.nebula.erp.reports.repository.sales.SalesRepository; import com.nebula.erp.reports.repository.sales.SalesReturnItemRepository; import com.nebula.erp.reports.repository.sales.SalesItemRepository; import com.nebula.erp.reports.repository.product.ProductRepository; import com.nebula.erp.reports.requestmodel.SalesRequest; import com.nebula.erp.reports.utility.CreateLogger; import com.nebula.erp.reports.utility.JwtRequestUtils; import com.nebula.erp.reports.utility.MoneyUtils; import jakarta.servlet.http.HttpServletRequest; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; import org.springframework.data.jpa.domain.Specification; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.stereotype.Service; import com.nebula.erp.reports.utility.ApiResponseStructure; import java.math.BigDecimal; import java.time.LocalDate; import java.time.LocalDateTime; import java.util.*; @Service public class SalesService { @Autowired private FeesRepository feesRepository; @Autowired private PurchaseRepository purchaseRepository; @Autowired private SalesRepository salesRepository; @Autowired private SalesItemRepository salesItemRepository; @Autowired private SalesReturnItemRepository salesReturnItemRepository; @Autowired private SupplierRepository supplierRepository; @Autowired private GRNRepository grnRepository; @Autowired private PurchaseReturnRepository purchaseReturnRepository; @Autowired private PurchaseReturnItemRepository purchaseReturnItemRepository; @Autowired private ProductRepository productRepository; @Autowired private GRNItemRepository grnItemRepository; @Autowired private PurchaseItemRepository purchaseItemRepository; @Autowired private CategoryRepository categoryRepository; @Autowired private BrandRepository brandRepository; @Autowired private JwtRequestUtils jwtRequestUtils; @Autowired private HttpServletRequest httpServletRequest; @Autowired private CreateLogger createLogger; private static final String path = "/reports/sales"; public ApiResponseStructure> getKPIData(LocalDate fromDate, LocalDate toDate) { // Extract Authorization headers from the request HttpHeaders headers = new HttpHeaders(); headers.add(HttpHeaders.AUTHORIZATION, httpServletRequest.getHeader(HttpHeaders.AUTHORIZATION)); String tenantName = jwtRequestUtils.getTenantNameFromHeaders(headers); if (fromDate != null && toDate != null) { // Convert LocalDate to LocalDateTime for comparison with created_at LocalDateTime fromDateTime = fromDate.atStartOfDay(); LocalDateTime toDateTime = toDate.atTime(23, 59, 59); BigDecimal totalSalesAmount = salesRepository.findTotalSalesAmount(fromDateTime, toDateTime, tenantName); BigDecimal pharmacySales = salesItemRepository.getTotalPharmacySales(fromDateTime, toDateTime, tenantName); BigDecimal hospitalSales = salesItemRepository.getTotalHospitalSales(fromDateTime, toDateTime, tenantName); Long totalQuantity = salesItemRepository.findTotalQuantity(fromDateTime, toDateTime, tenantName); List product_id = salesItemRepository.findTopSellingProduct(fromDateTime, toDateTime, tenantName, "product"); String product_name = null; if (!product_id.isEmpty()) { product_name = productRepository.findProductNameById(product_id.get(0), tenantName); } BigDecimal averageOrderValue = salesRepository.findAverageOrderValue(fromDateTime, toDateTime, tenantName); Long totalOrder = salesRepository.countTotalSales(fromDateTime, toDateTime, tenantName); Map totals = new LinkedHashMap<>(); totals.put( "total_hospital_sales_amount", MoneyUtils.truncateToTwoDecimals(hospitalSales.doubleValue())); totals.put( "total_pharmacy_sales_amount", MoneyUtils.truncateToTwoDecimals(pharmacySales.doubleValue())); totals.put( "total_sales_amount", MoneyUtils.truncateToTwoDecimals( hospitalSales.add(pharmacySales).doubleValue())); // Create the response map Map salesData = new LinkedHashMap<>(); salesData.put("total_sales_amount", MoneyUtils.truncateToTwoDecimals(totalSalesAmount.doubleValue())); salesData.put("total_quantity_sold", totalQuantity); salesData.put("top_product", product_name); salesData.put("average_order_value", MoneyUtils.truncateToTwoDecimals(averageOrderValue.doubleValue())); salesData.put("total_orders", totalOrder); salesData.put("totals", totals); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", salesData); } createLogger.createLogger("error", path, "GET", "Please provide start-date and end-date with valid dates.", ""); return new ApiResponseStructure<>("Failure", 500, "Please provide start-date and end-date with valid dates.", Collections.emptyMap()); } public ApiResponseStructure> getSalesReport(SalesRequest salesRequest, String reportType) { // Extract Authorization headers from the request HttpHeaders headers = new HttpHeaders(); headers.add(HttpHeaders.AUTHORIZATION, httpServletRequest.getHeader(HttpHeaders.AUTHORIZATION)); String tenantName = jwtRequestUtils.getTenantNameFromHeaders(headers); // Validate presence of fromDate and toDate if (salesRequest.getStartDate() == null || salesRequest.getEndDate() == null) { createLogger.createLogger("error", path, "POST", "Both 'startDate' and 'endDate' must be provided.", ""); return new ApiResponseStructure<>("Error", HttpStatus.BAD_REQUEST.value(), "Both 'startDate' and 'endDate' must be provided.", new HashMap<>()); } // Create a specification for the query Specification spec = Specification.where(null); // Add tenant name filtering spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("tenant"), tenantName)); // Check the data types for getStartDate() and getEndDate() LocalDate fromDate = salesRequest.getStartDate(); // Ensure this is LocalDate LocalDate toDate = salesRequest.getEndDate(); // Ensure this is LocalDate // Convert LocalDate to LocalDateTime LocalDateTime fromDateTime = fromDate.atStartOfDay(); LocalDateTime toDateTime = toDate.atTime(23, 59, 59); // Add date range filtering if (salesRequest.getStartDate() != null && salesRequest.getEndDate() != null) { spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.between(root.get("created_at"), fromDateTime, toDateTime)); } // Switch cases to handle various report type switch (reportType) { case "byProduct": // Add dynamic conditions if (salesRequest.getConditions() != null) { for (SalesRequest.Condition condition : salesRequest.getConditions()) { switch (condition.getOperator()) { case "equals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .equal(root.get(condition.getField()), condition.getValue())); break; case "greaterThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThan( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .lessThan(root.get(condition.getField()), (Comparable) condition.getValue())); break; case "greaterThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.lessThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; default: createLogger.createLogger("error", path, "POST", "Operator " + condition.getOperator() + " is not supported.", ""); throw new IllegalArgumentException( "Operator " + condition.getOperator() + " is not supported."); } } } // Add product type filtering spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("type"), "product")); // Fetch the data with pagination for sales items Pageable pageable = PageRequest.of(salesRequest.getPage() != null ? salesRequest.getPage() : 0, // Page // number // is // zero-based salesRequest.getSize() != null ? salesRequest.getSize() : 20); Page salesPage = salesItemRepository.findAll(spec, pageable); // Group sales items by product_id Map> groupedSalesData = new HashMap<>(); salesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { Long productId = Long.valueOf(item.getProduct_id()); String productName = productRepository.findProductNameById(productId, tenantName); Long categoryId = productRepository.findCategoryIdByProductId(productId, tenantName); Long brandId = productRepository.findBrandIdByProductId(productId, tenantName); String categoryName = categoryRepository.findCategoryNameById(categoryId, tenantName); String brandName = brandRepository.findBrandNameById(brandId, tenantName); groupedSalesData.computeIfAbsent(productId, id -> { Map data = new LinkedHashMap<>(); data.put("product_id", productId); data.put("product_name", productName); data.put("category_id", categoryId); data.put("category_name", categoryName); data.put("brand_id", brandId); data.put("brand_name", brandName); data.put("quantity", 0); data.put("total_price", 0.0); return data; }); Map data = groupedSalesData.get(productId); data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); data.put("total_price", MoneyUtils .truncateToTwoDecimals((Double) data.get("total_price") + item.getTotal_price())); data.put("unit_price", item.getUnit_price()); // Assuming unit price is consistent for each // product } }); // Prepare reportData list from the grouped data List> salesDataList = new ArrayList<>(groupedSalesData.values()); // Prepare column details Map column = new LinkedHashMap<>(); column.put("product_id", "PRODUCT ID"); column.put("product_name", "PRODUCT NAME"); column.put("category_id", "CATEGORY ID"); column.put("category_name", "CATEGORY NAME"); column.put("brand_id", "BRAND ID"); column.put("brand_name", "BRAND NAME"); column.put("quantity", "QUANTITY"); column.put("total_price", "TOTAL PRICE"); column.put("unit_price", "UNIT PRICE"); // Prepare pagination details Map pagination = new LinkedHashMap<>(); pagination.put("currentPage", salesPage.getNumber() + 1); // Page number is 0-based pagination.put("totalPages", salesPage.getTotalPages()); pagination.put("pageSize", pageable.getPageSize()); pagination.put("totalRecords", groupedSalesData.size()); // Calculate summary details double totalRevenue = salesPage.getContent().stream().mapToDouble(SalesItem::getTotal_price).sum(); int totalItemsSold = salesPage.getContent().stream().mapToInt(SalesItem::getQuantity).sum(); // Find the top-selling product Map productSalesMap = new HashMap<>(); salesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { productSalesMap.put(Long.valueOf(item.getProduct_id()), productSalesMap.getOrDefault(item.getProduct_id(), 0) + item.getQuantity()); } }); Long topSellingProductId = productSalesMap.entrySet().stream().max(Map.Entry.comparingByValue()) .map(Map.Entry::getKey).orElse(null); // Fetch the product name using the topSellingProductId String topSellingProductName = null; if (topSellingProductId != null) { topSellingProductName = productRepository.findProductNameById(topSellingProductId, tenantName); } // Prepare summary details Map summary = new LinkedHashMap<>(); summary.put("totalRevenue", MoneyUtils.truncateToTwoDecimals(totalRevenue)); summary.put("totalItemsSold", totalItemsSold); summary.put("topSellingProduct", topSellingProductName); // Prepare response data Map salesData = new LinkedHashMap<>(); salesData.put("module", "sales"); salesData.put("reportType", reportType); salesData.put("startDate", String.valueOf(fromDate)); salesData.put("endDate", String.valueOf(toDate)); salesData.put("reportData", salesDataList); salesData.put("column", column); salesData.put("pagination", pagination); salesData.put("summary", summary); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", salesData); // case "byService": // // Add dynamic conditions // if (salesRequest.getConditions() != null) { // for (SalesRequest.Condition condition : salesRequest.getConditions()) { // switch (condition.getOperator()) { // case "equals": // spec = spec.and((root, query, criteriaBuilder) -> // criteriaBuilder.equal(root.get(condition.getField()), condition.getValue())); // break; // case "greaterThan": // spec = spec.and((root, query, criteriaBuilder) -> // criteriaBuilder.greaterThan(root.get(condition.getField()), (Comparable) // condition.getValue())); // break; // case "lessThan": // spec = spec.and((root, query, criteriaBuilder) -> // criteriaBuilder.lessThan(root.get(condition.getField()), (Comparable) // condition.getValue())); // break; // case "greaterThanOrEquals": // spec = spec.and((root, query, criteriaBuilder) -> // criteriaBuilder.greaterThanOrEqualTo(root.get(condition.getField()), // (Comparable) condition.getValue())); // break; // case "lessThanOrEquals": // spec = spec.and((root, query, criteriaBuilder) -> // criteriaBuilder.lessThanOrEqualTo(root.get(condition.getField()), // (Comparable) condition.getValue())); // break; // default: // createLogger.createLogger("error", path, "POST", "Operator " + // condition.getOperator() + " is not supported.", ""); // throw new IllegalArgumentException("Operator " + condition.getOperator() + " // is not supported."); // } // } // } // // Add product type filtering // spec = spec.and((root, query, criteriaBuilder) -> // criteriaBuilder.equal(root.get("type"), "service")); // // // Fetch the data with pagination for sales items // Pageable pageableService = PageRequest.of(salesRequest.getPage() != null ? // salesRequest.getPage() : 0, // Page number is zero-based // salesRequest.getSize() != null ? salesRequest.getSize() : 20); // // Page salesServicePage = salesItemRepository.findAll(spec, // pageableService); // // // Group sales items by product_id // Map> groupedSalesServiceData = new HashMap<>(); // // salesServicePage.getContent().forEach(item -> { // Long productId = item.getProduct_id(); // Fees serviceItem = feesRepository.findServiceById(productId, tenantName); // if (serviceItem != null) { // groupedSalesServiceData.computeIfAbsent(productId, id -> { // Map data = new LinkedHashMap<>(); // data.put("product_id", productId); // data.put("speciality", serviceItem.getSpeciality()); // data.put("fees_type_name", serviceItem.getFees_type_name()); // data.put("service_category", serviceItem.getService_category()); // data.put("measurement", serviceItem.getMeasurement()); // data.put("quantity", 0); // data.put("total_price", 0.0); // return data; // }); // // Map data = groupedSalesServiceData.get(productId); // data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); // data.put("total_price", (Double) data.get("total_price") + // item.getTotal_price()); // data.put("unit_price", item.getUnit_price()); // Assuming unit price is // consistent for each product // } // }); // // // Prepare reportData list from the grouped data // List> salesServiceDataList = new // ArrayList<>(groupedSalesServiceData.values()); // // // Prepare column details // Map columnService = new LinkedHashMap<>(); // columnService.put("product_id", "PRODUCT ID"); // columnService.put("speciality", "SPECIALITY"); // columnService.put("fees_type_name", "FEES TYPE NAME"); // columnService.put("service_category", "SERVICE CATEGORY"); // columnService.put("measurement", "MEASUREMENT"); // columnService.put("quantity", "QUANTITY"); // columnService.put("total_price", "TOTAL PRICE"); // columnService.put("unit_price", "UNIT PRICE"); // // // Prepare pagination details // Map paginationService = new LinkedHashMap<>(); // paginationService.put("currentPage", salesServicePage.getNumber() + 1); // // Page number is 0-based // paginationService.put("totalPages", salesServicePage.getTotalPages()); // paginationService.put("pageSize", pageableService.getPageSize()); // paginationService.put("totalRecords", groupedSalesServiceData.size()); // // // Calculate summary details // double totalServiceRevenue = salesServicePage.getContent().stream() // .mapToDouble(SalesItem::getTotal_price) // .sum(); // // int totalServiceItemsSold = salesServicePage.getContent().stream() // .mapToInt(SalesItem::getQuantity) // .sum(); // // // Find the top-selling product // Map serviceSalesMap = new HashMap<>(); // salesServicePage.getContent().forEach(item -> { // serviceSalesMap.put(item.getProduct_id(), // serviceSalesMap.getOrDefault(item.getProduct_id(), 0) + item.getQuantity()); // }); // // Long topSellingServiceId = serviceSalesMap.entrySet().stream() // .max(Map.Entry.comparingByValue()) // .map(Map.Entry::getKey) // .orElse(null); // // // Fetch the product name using the topSellingProductId // String topSellingServiceName = null; // if (topSellingServiceId != null) { // topSellingServiceName = // productRepository.findProductNameById(topSellingServiceId, tenantName); // } // // // Prepare summary details // Map summaryService = new LinkedHashMap<>(); // summaryService.put("totalServiceRevenue", totalServiceRevenue); // summaryService.put("totalServicesSold", totalServiceItemsSold); // summaryService.put("topSellingService", topSellingServiceName); // // // Prepare response data // Map salesServiceData = new LinkedHashMap<>(); // salesServiceData.put("module", "sales"); // salesServiceData.put("reportType", reportType); // salesServiceData.put("startDate", fromDate); // salesServiceData.put("endDate", toDate); // salesServiceData.put("reportData", salesServiceDataList); // salesServiceData.put("column", columnService); // salesServiceData.put("pagination", paginationService); // salesServiceData.put("summary", summaryService); // // // Create and return the structured ApiResponseStructure // return new ApiResponseStructure<>("Success", 200, "Data retrieved.", // salesServiceData); case "byCategory": // Add dynamic conditions if (salesRequest.getConditions() != null) { for (SalesRequest.Condition condition : salesRequest.getConditions()) { switch (condition.getOperator()) { case "equals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .equal(root.get(condition.getField()), condition.getValue())); break; case "greaterThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThan( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .lessThan(root.get(condition.getField()), (Comparable) condition.getValue())); break; case "greaterThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.lessThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; default: createLogger.createLogger("error", path, "POST", "Operator " + condition.getOperator() + " is not supported.", ""); throw new IllegalArgumentException( "Operator " + condition.getOperator() + " is not supported."); } } } // Fetch the data with pagination for sales items pageable = PageRequest.of(salesRequest.getPage() != null ? salesRequest.getPage() : 0, // Page number is // zero-based salesRequest.getSize() != null ? salesRequest.getSize() : 20); salesPage = salesItemRepository.findAll(spec, pageable); // Group sales items by category_id Map> groupedSalesDataByCategory = new HashMap<>(); salesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { Long productId = Long.valueOf(item.getProduct_id()); Long categoryId = productRepository.findCategoryIdByProductId(productId, tenantName); Long brandId = productRepository.findBrandIdByProductId(productId, tenantName); String categoryName = categoryRepository.findCategoryNameById(categoryId, tenantName); String brandName = brandRepository.findBrandNameById(brandId, tenantName); // Create a new entry for the category if it doesn't exist groupedSalesDataByCategory.computeIfAbsent(categoryId, id -> { Map data = new LinkedHashMap<>(); data.put("category_id", categoryId); data.put("category_name", categoryName); data.put("brand_id", brandId); data.put("brand_name", brandName); data.put("quantity", 0); data.put("total_price", 0.0); data.put("products", new ArrayList>()); // Initialize products array return data; }); // Get the current data for the category Map data = groupedSalesDataByCategory.get(categoryId); data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); data.put("total_price", MoneyUtils .truncateToTwoDecimals((Double) data.get("total_price") + item.getTotal_price())); // Prepare product details to add to the products array Map productData = new LinkedHashMap<>(); productData.put("product_id", productId); String productName = productRepository.findProductNameById(productId, tenantName); productData.put("product_name", productName); productData.put("category_id", categoryId); productData.put("category_name", categoryName); productData.put("brand_id", brandId); productData.put("brand_name", brandName); productData.put("quantity", item.getQuantity()); productData.put("total_price", MoneyUtils.truncateToTwoDecimals(item.getTotal_price())); productData.put("unit_price", item.getUnit_price()); List> productsList = (List>) data.get("products"); Optional> existingProductOpt = productsList.stream() .filter(product -> product.get("product_id").equals(productId)).findFirst(); if (existingProductOpt.isPresent()) { // Update existing product data Map existingProduct = existingProductOpt.get(); existingProduct.put("quantity", (Integer) existingProduct.get("quantity") + item.getQuantity()); existingProduct.put("total_price", MoneyUtils.truncateToTwoDecimals( (Double) existingProduct.get("total_price") + item.getTotal_price())); } else { // Add new product to the products list productsList.add(productData); } } }); // Prepare reportData list from the grouped data List> salesDataListByCategory = new ArrayList<>( groupedSalesDataByCategory.values()); // Prepare column details column = new LinkedHashMap<>(); column.put("product_id", "PRODUCT ID"); column.put("product_name", "PRODUCT NAME"); column.put("category_id", "CATEGORY ID"); column.put("category_name", "CATEGORY NAME"); column.put("brand_id", "BRAND ID"); column.put("brand_name", "BRAND NAME"); column.put("quantity", "QUANTITY"); column.put("total_price", "TOTAL PRICE"); column.put("unit_price", "UNIT PRICE"); // Prepare pagination details pagination = new LinkedHashMap<>(); pagination.put("currentPage", salesPage.getNumber() + 1); // Page number is 0-based pagination.put("totalPages", salesPage.getTotalPages()); pagination.put("pageSize", pageable.getPageSize()); pagination.put("totalRecords", groupedSalesDataByCategory.size()); // Calculate summary details totalRevenue = salesDataListByCategory.stream().mapToDouble(data -> (Double) data.get("total_price")) .sum(); totalItemsSold = salesDataListByCategory.stream().mapToInt(data -> (Integer) data.get("quantity")) .sum(); // Find the top-selling category Map categorySalesMap = new HashMap<>(); salesDataListByCategory.forEach(data -> { Long categoryId = (Long) data.get("category_id"); Integer quantity = (Integer) data.get("quantity"); categorySalesMap.put(categoryId, categorySalesMap.getOrDefault(categoryId, 0) + quantity); }); Long topSellingCategoryId = categorySalesMap.entrySet().stream().max(Map.Entry.comparingByValue()) .map(Map.Entry::getKey).orElse(null); // Fetch the category name using the topSellingCategoryId (assuming you have a // method to fetch category name) String topSellingCategoryName = null; if (topSellingCategoryId != null) { topSellingCategoryName = categoryRepository.findCategoryNameById(topSellingCategoryId, tenantName); // Adjust // this // line // as // needed } // Prepare summary details summary = new LinkedHashMap<>(); summary.put("totalRevenue", MoneyUtils.truncateToTwoDecimals(totalRevenue)); summary.put("totalItemsSold", totalItemsSold); summary.put("topSellingCategory", topSellingCategoryName); // Prepare response data salesData = new LinkedHashMap<>(); salesData.put("module", "sales"); salesData.put("reportType", reportType); salesData.put("startDate", String.valueOf(fromDate)); salesData.put("endDate", String.valueOf(toDate)); salesData.put("reportData", salesDataListByCategory); salesData.put("column", column); salesData.put("pagination", pagination); salesData.put("summary", summary); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", salesData); case "byBrand": // Add dynamic conditions if (salesRequest.getConditions() != null) { for (SalesRequest.Condition condition : salesRequest.getConditions()) { switch (condition.getOperator()) { case "equals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .equal(root.get(condition.getField()), condition.getValue())); break; case "greaterThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThan( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .lessThan(root.get(condition.getField()), (Comparable) condition.getValue())); break; case "greaterThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.lessThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; default: createLogger.createLogger("error", path, "POST", "Operator " + condition.getOperator() + " is not supported.", ""); throw new IllegalArgumentException( "Operator " + condition.getOperator() + " is not supported."); } } } // Fetch the data with pagination for sales items pageable = PageRequest.of(salesRequest.getPage() != null ? salesRequest.getPage() : 0, // Page number is // zero-based salesRequest.getSize() != null ? salesRequest.getSize() : 20); salesPage = salesItemRepository.findAll(spec, pageable); // Group sales items by brand_id Map> groupedSalesDataByBrand = new HashMap<>(); salesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { Long productId = Long.valueOf(item.getProduct_id()); Long categoryId = productRepository.findCategoryIdByProductId(productId, tenantName); Long brandId = productRepository.findBrandIdByProductId(productId, tenantName); String categoryName = categoryRepository.findCategoryNameById(categoryId, tenantName); String brandName = brandRepository.findBrandNameById(brandId, tenantName); // Create a new entry for the brand if it doesn't exist groupedSalesDataByBrand.computeIfAbsent(brandId, id -> { Map data = new LinkedHashMap<>(); data.put("brand_id", brandId); data.put("brand_name", brandName); data.put("quantity", 0); data.put("total_price", 0.0); data.put("categories", new ArrayList>()); // Initialize categories array return data; }); // Get the current data for the brand Map data = groupedSalesDataByBrand.get(brandId); data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); data.put("total_price", MoneyUtils .truncateToTwoDecimals((Double) data.get("total_price") + item.getTotal_price())); // Prepare category details to add to the categories array List> categoriesList = (List>) data.get("categories"); Map categoryData = Map.of(); if (categoryId != null) { // Check if category already exists in the categories list Optional> existingCategoryOpt = categoriesList.stream() .filter(category -> category.get("category_id").equals(categoryId)).findFirst(); if (existingCategoryOpt.isPresent()) { categoryData = existingCategoryOpt.get(); categoryData.put("quantity", (Integer) categoryData.get("quantity") + item.getQuantity()); categoryData.put("total_price", MoneyUtils.truncateToTwoDecimals( (Double) categoryData.get("total_price") + item.getTotal_price())); } else { // Create a new category entry categoryData = new LinkedHashMap<>(); categoryData.put("category_id", categoryId); categoryData.put("category_name", categoryName); categoryData.put("quantity", item.getQuantity()); categoryData.put("total_price", MoneyUtils.truncateToTwoDecimals(item.getTotal_price())); categoryData.put("products", new ArrayList>()); // Initialize // products array categoriesList.add(categoryData); // Add new category to the list } } // Prepare product details to add to the products array Map productData = new LinkedHashMap<>(); productData.put("product_id", productId); String productName = productRepository.findProductNameById(productId, tenantName); productData.put("product_name", productName); productData.put("category_id", categoryId); productData.put("category_name", categoryName); productData.put("brand_id", brandId); productData.put("brand_name", brandName); productData.put("quantity", item.getQuantity()); productData.put("total_price", MoneyUtils.truncateToTwoDecimals(item.getTotal_price())); productData.put("unit_price", item.getUnit_price()); // Check if product already exists in the products list of the category List> productsList = (List>) categoryData .get("products"); if (productsList != null) { Optional> existingProductOpt = productsList.stream() .filter(product -> product.get("product_id").equals(productId)).findFirst(); if (existingProductOpt.isPresent()) { // Update existing product data Map existingProduct = existingProductOpt.get(); existingProduct.put("quantity", (Integer) existingProduct.get("quantity") + item.getQuantity()); existingProduct.put("total_price", MoneyUtils.truncateToTwoDecimals( (Double) existingProduct.get("total_price") + item.getTotal_price())); } else { // Add new product to the products list productsList.add(productData); } } } }); // Prepare reportData list from the grouped data List> salesDataListByBrand = new ArrayList<>(groupedSalesDataByBrand.values()); // Prepare column details column = new LinkedHashMap<>(); column.put("product_id", "PRODUCT ID"); column.put("product_name", "PRODUCT NAME"); column.put("category_id", "CATEGORY ID"); column.put("category_name", "CATEGORY NAME"); column.put("brand_id", "BRAND ID"); column.put("brand_name", "BRAND NAME"); column.put("quantity", "QUANTITY"); column.put("total_price", "TOTAL PRICE"); column.put("unit_price", "UNIT PRICE"); // Prepare pagination details pagination = new LinkedHashMap<>(); pagination.put("currentPage", salesPage.getNumber() + 1); // Page number is 0-based pagination.put("totalPages", salesPage.getTotalPages()); pagination.put("pageSize", pageable.getPageSize()); pagination.put("totalRecords", groupedSalesDataByBrand.size()); // Calculate summary details totalRevenue = salesDataListByBrand.stream().mapToDouble(data -> (Double) data.get("total_price")) .sum(); totalItemsSold = salesDataListByBrand.stream().mapToInt(data -> (Integer) data.get("quantity")).sum(); // Find the top-selling brand Map brandSalesMap = new HashMap<>(); salesDataListByBrand.forEach(data -> { Long brandIdVal = (Long) data.get("brand_id"); Integer quantity = (Integer) data.get("quantity"); brandSalesMap.put(brandIdVal, brandSalesMap.getOrDefault(brandIdVal, 0) + quantity); }); Long topSellingBrandId = brandSalesMap.entrySet().stream().max(Map.Entry.comparingByValue()) .map(Map.Entry::getKey).orElse(null); // Fetch the brand name using the topSellingBrandId String topSellingBrandName = null; if (topSellingBrandId != null) { topSellingBrandName = brandRepository.findBrandNameById(topSellingBrandId, tenantName); // Adjust // this line // as needed } // Prepare summary details summary = new LinkedHashMap<>(); summary.put("totalRevenue", MoneyUtils.truncateToTwoDecimals(totalRevenue)); summary.put("totalItemsSold", totalItemsSold); summary.put("topSellingBrand", topSellingBrandName); // Prepare response data salesData = new LinkedHashMap<>(); salesData.put("module", "sales"); salesData.put("reportType", reportType); salesData.put("startDate", String.valueOf(fromDate)); salesData.put("endDate", String.valueOf(toDate)); salesData.put("reportData", salesDataListByBrand); salesData.put("column", column); salesData.put("pagination", pagination); salesData.put("summary", summary); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", salesData); case "daily": // Add dynamic conditions for the daily report if (salesRequest.getConditions() != null) { for (SalesRequest.Condition condition : salesRequest.getConditions()) { switch (condition.getOperator()) { case "equals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .equal(root.get(condition.getField()), condition.getValue())); break; case "greaterThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThan( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .lessThan(root.get(condition.getField()), (Comparable) condition.getValue())); break; case "greaterThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.lessThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; default: createLogger.createLogger("error", path, "POST", "Operator " + condition.getOperator() + " is not supported.", ""); throw new IllegalArgumentException( "Operator " + condition.getOperator() + " is not supported."); } } } // Fetch all sales items based on date range and tenant name pageable = PageRequest.of(salesRequest.getPage() != null ? salesRequest.getPage() : 0, // Page number is // zero-based salesRequest.getSize() != null ? salesRequest.getSize() : 20); Page dailySalesPage = salesItemRepository.findAll(spec, pageable); // Group sales items by product_id Map> groupedDailySalesData = new HashMap<>(); dailySalesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { Long productId = Long.valueOf(item.getProduct_id()); String productName = productRepository.findProductNameById(productId, tenantName); Long categoryId = productRepository.findCategoryIdByProductId(productId, tenantName); Long brandId = productRepository.findBrandIdByProductId(productId, tenantName); String categoryName = categoryRepository.findCategoryNameById(categoryId, tenantName); String brandName = brandRepository.findBrandNameById(brandId, tenantName); // Create a new entry for the product if it doesn't exist groupedDailySalesData.computeIfAbsent(productId, id -> { Map data = new LinkedHashMap<>(); data.put("product_id", productId); data.put("product_name", productName); data.put("category_id", categoryId); data.put("category_name", categoryName); data.put("brand_id", brandId); data.put("brand_name", brandName); data.put("quantity", 0); data.put("total_price", 0.0); data.put("unit_price", item.getUnit_price()); // Assuming unit price is consistent for each // product return data; }); // Update the existing product data Map data = groupedDailySalesData.get(productId); data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); data.put("total_price", MoneyUtils .truncateToTwoDecimals((Double) data.get("total_price") + item.getTotal_price())); } }); // Prepare reportData list from the grouped data List> dailySalesDataList = new ArrayList<>(groupedDailySalesData.values()); // Prepare pagination details Map dailyPagination = new LinkedHashMap<>(); dailyPagination.put("currentPage", dailySalesPage.getNumber() + 1); // Page number is 0-based dailyPagination.put("totalPages", dailySalesPage.getTotalPages()); dailyPagination.put("pageSize", pageable.getPageSize()); dailyPagination.put("totalRecords", dailySalesDataList.size()); // Calculate summary details double dailyTotalRevenue = dailySalesDataList.stream() .mapToDouble(data -> (Double) data.get("total_price")).sum(); int dailyTotalItemsSold = dailySalesDataList.stream().mapToInt(data -> (Integer) data.get("quantity")) .sum(); Long topSellingDailyProductId = dailySalesDataList.stream() .max(Comparator.comparingInt(data -> (Integer) data.get("quantity"))) .map(data -> (Long) data.get("product_id")).orElse(null); // Fetch the product name using the topSellingProductId String topSellingDailyProductName = null; if (topSellingDailyProductId != null) { topSellingDailyProductName = productRepository.findProductNameById(topSellingDailyProductId, tenantName); } // Prepare summary details Map dailySummary = new LinkedHashMap<>(); dailySummary.put("totalRevenue", MoneyUtils.truncateToTwoDecimals(dailyTotalRevenue)); dailySummary.put("totalItemsSold", dailyTotalItemsSold); dailySummary.put("topSellingBrand", topSellingDailyProductName); // Prepare column details column = new LinkedHashMap<>(); column.put("product_id", "PRODUCT ID"); column.put("product_name", "PRODUCT NAME"); column.put("category_id", "CATEGORY ID"); column.put("category_name", "CATEGORY NAME"); column.put("brand_id", "BRAND ID"); column.put("brand_name", "BRAND NAME"); column.put("quantity", "QUANTITY"); column.put("total_price", "TOTAL PRICE"); column.put("unit_price", "UNIT PRICE"); // Prepare response data Map dailySalesData = new LinkedHashMap<>(); dailySalesData.put("module", "sales"); dailySalesData.put("reportType", reportType); dailySalesData.put("startDate", String.valueOf(fromDate)); dailySalesData.put("endDate", String.valueOf(toDate)); dailySalesData.put("reportData", dailySalesDataList); dailySalesData.put("column", column); dailySalesData.put("pagination", dailyPagination); dailySalesData.put("summary", dailySummary); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", dailySalesData); case "monthly": // Add dynamic conditions for the daily report if (salesRequest.getConditions() != null) { for (SalesRequest.Condition condition : salesRequest.getConditions()) { switch (condition.getOperator()) { case "equals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .equal(root.get(condition.getField()), condition.getValue())); break; case "greaterThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThan( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .lessThan(root.get(condition.getField()), (Comparable) condition.getValue())); break; case "greaterThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.lessThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; default: createLogger.createLogger("error", path, "POST", "Operator " + condition.getOperator() + " is not supported.", ""); throw new IllegalArgumentException( "Operator " + condition.getOperator() + " is not supported."); } } } // Fetch all sales items based on date range and tenant name pageable = PageRequest.of(salesRequest.getPage() != null ? salesRequest.getPage() : 0, // Page number is // zero-based salesRequest.getSize() != null ? salesRequest.getSize() : 20); Page monthlySalesPage = salesItemRepository.findAll(spec, pageable); // Group sales items by product_id Map> groupedMonthlySalesData = new HashMap<>(); monthlySalesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { Long productId = Long.valueOf(item.getProduct_id()); String productName = productRepository.findProductNameById(productId, tenantName); Long categoryId = productRepository.findCategoryIdByProductId(productId, tenantName); Long brandId = productRepository.findBrandIdByProductId(productId, tenantName); String categoryName = categoryRepository.findCategoryNameById(categoryId, tenantName); String brandName = brandRepository.findBrandNameById(brandId, tenantName); // Create a new entry for the product if it doesn't exist groupedMonthlySalesData.computeIfAbsent(productId, id -> { Map data = new LinkedHashMap<>(); data.put("product_id", productId); data.put("product_name", productName); data.put("category_id", categoryId); data.put("category_name", categoryName); data.put("brand_id", brandId); data.put("brand_name", brandName); data.put("quantity", 0); data.put("total_price", 0.0); data.put("unit_price", item.getUnit_price()); // Assuming unit price is consistent for each // product return data; }); // Update the existing product data Map data = groupedMonthlySalesData.get(productId); data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); data.put("total_price", MoneyUtils .truncateToTwoDecimals((Double) data.get("total_price") + item.getTotal_price())); } }); // Prepare reportData list from the grouped data List> monthlySalesDataList = new ArrayList<>(groupedMonthlySalesData.values()); // Prepare pagination details Map monthlyPagination = new LinkedHashMap<>(); monthlyPagination.put("currentPage", monthlySalesPage.getNumber() + 1); // Page number is 0-based monthlyPagination.put("totalPages", monthlySalesPage.getTotalPages()); monthlyPagination.put("pageSize", pageable.getPageSize()); monthlyPagination.put("totalRecords", monthlySalesDataList.size()); // Calculate summary details double monthlyTotalRevenue = monthlySalesDataList.stream() .mapToDouble(data -> (Double) data.get("total_price")).sum(); int monthlyTotalItemsSold = monthlySalesDataList.stream() .mapToInt(data -> (Integer) data.get("quantity")).sum(); Long topSellingMonthlyProductId = monthlySalesDataList.stream() .max(Comparator.comparingInt(data -> (Integer) data.get("quantity"))) .map(data -> (Long) data.get("product_id")).orElse(null); // Fetch the product name using the topSellingProductId String topSellingMonthlyProductName = null; if (topSellingMonthlyProductId != null) { topSellingMonthlyProductName = productRepository.findProductNameById(topSellingMonthlyProductId, tenantName); } // Prepare summary details summary = new LinkedHashMap<>(); summary.put("totalRevenue", MoneyUtils.truncateToTwoDecimals(monthlyTotalRevenue)); summary.put("totalItemsSold", monthlyTotalItemsSold); summary.put("topSellingBrand", topSellingMonthlyProductName); // Prepare column details column = new LinkedHashMap<>(); column.put("product_id", "PRODUCT ID"); column.put("product_name", "PRODUCT NAME"); column.put("category_id", "CATEGORY ID"); column.put("category_name", "CATEGORY NAME"); column.put("brand_id", "BRAND ID"); column.put("brand_name", "BRAND NAME"); column.put("quantity", "QUANTITY"); column.put("total_price", "TOTAL PRICE"); column.put("unit_price", "UNIT PRICE"); // Prepare response data Map monthlySalesData = new LinkedHashMap<>(); monthlySalesData.put("module", "sales"); monthlySalesData.put("reportType", reportType); monthlySalesData.put("startDate", String.valueOf(fromDate)); monthlySalesData.put("endDate", String.valueOf(toDate)); monthlySalesData.put("reportData", monthlySalesDataList); monthlySalesData.put("column", column); monthlySalesData.put("pagination", monthlyPagination); monthlySalesData.put("summary", summary); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", monthlySalesData); case "annual": // Add dynamic conditions for the daily report if (salesRequest.getConditions() != null) { for (SalesRequest.Condition condition : salesRequest.getConditions()) { switch (condition.getOperator()) { case "equals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .equal(root.get(condition.getField()), condition.getValue())); break; case "greaterThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThan( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .lessThan(root.get(condition.getField()), (Comparable) condition.getValue())); break; case "greaterThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.lessThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; default: createLogger.createLogger("error", path, "POST", "Operator " + condition.getOperator() + " is not supported.", ""); throw new IllegalArgumentException( "Operator " + condition.getOperator() + " is not supported."); } } } // Fetch all sales items based on date range and tenant name pageable = PageRequest.of(salesRequest.getPage() != null ? salesRequest.getPage() : 0, // Page number is // zero-based salesRequest.getSize() != null ? salesRequest.getSize() : 20); Page annualSalesPage = salesItemRepository.findAll(spec, pageable); // Group sales items by product_id Map> groupedAnnualSalesData = new HashMap<>(); annualSalesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { Long productId = Long.valueOf(item.getProduct_id()); String productName = productRepository.findProductNameById(productId, tenantName); Long categoryId = productRepository.findCategoryIdByProductId(productId, tenantName); Long brandId = productRepository.findBrandIdByProductId(productId, tenantName); String categoryName = categoryRepository.findCategoryNameById(categoryId, tenantName); String brandName = brandRepository.findBrandNameById(brandId, tenantName); // Create a new entry for the product if it doesn't exist groupedAnnualSalesData.computeIfAbsent(productId, id -> { Map data = new LinkedHashMap<>(); data.put("product_id", productId); data.put("product_name", productName); data.put("category_id", categoryId); data.put("category_name", categoryName); data.put("brand_id", brandId); data.put("brand_name", brandName); data.put("quantity", 0); data.put("total_price", 0.0); data.put("unit_price", item.getUnit_price()); // Assuming unit price is consistent for each // product return data; }); // Update the existing product data Map data = groupedAnnualSalesData.get(productId); data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); data.put("total_price", MoneyUtils .truncateToTwoDecimals((Double) data.get("total_price") + item.getTotal_price())); } }); // Prepare reportData list from the grouped data List> annualSalesDataList = new ArrayList<>(groupedAnnualSalesData.values()); // Prepare pagination details Map annualPagination = new LinkedHashMap<>(); annualPagination.put("currentPage", annualSalesPage.getNumber() + 1); // Page number is 0-based annualPagination.put("totalPages", annualSalesPage.getTotalPages()); annualPagination.put("pageSize", pageable.getPageSize()); annualPagination.put("totalRecords", annualSalesDataList.size()); // Calculate summary details double annualTotalRevenue = annualSalesDataList.stream() .mapToDouble(data -> (Double) data.get("total_price")).sum(); int annualTotalItemsSold = annualSalesDataList.stream().mapToInt(data -> (Integer) data.get("quantity")) .sum(); Long topSellingAnnualProductId = annualSalesDataList.stream() .max(Comparator.comparingInt(data -> (Integer) data.get("quantity"))) .map(data -> (Long) data.get("product_id")).orElse(null); // Fetch the product name using the topSellingProductId String topSellingAnnualProductName = null; if (topSellingAnnualProductId != null) { topSellingAnnualProductName = productRepository.findProductNameById(topSellingAnnualProductId, tenantName); } // Prepare summary details summary = new LinkedHashMap<>(); summary.put("totalRevenue", MoneyUtils.truncateToTwoDecimals(annualTotalRevenue)); summary.put("totalItemsSold", annualTotalItemsSold); summary.put("topSellingBrand", topSellingAnnualProductName); // Prepare column details column = new LinkedHashMap<>(); column.put("product_id", "PRODUCT ID"); column.put("product_name", "PRODUCT NAME"); column.put("category_id", "CATEGORY ID"); column.put("category_name", "CATEGORY NAME"); column.put("brand_id", "BRAND ID"); column.put("brand_name", "BRAND NAME"); column.put("quantity", "QUANTITY"); column.put("total_price", "TOTAL PRICE"); column.put("unit_price", "UNIT PRICE"); // Prepare response data Map annualSalesData = new LinkedHashMap<>(); annualSalesData.put("module", "sales"); annualSalesData.put("reportType", reportType); annualSalesData.put("startDate", String.valueOf(fromDate)); annualSalesData.put("endDate", String.valueOf(toDate)); annualSalesData.put("reportData", annualSalesDataList); annualSalesData.put("column", column); annualSalesData.put("pagination", annualPagination); annualSalesData.put("summary", summary); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", annualSalesData); case "topSellingProducts": // Add dynamic conditions if (salesRequest.getConditions() != null) { for (SalesRequest.Condition condition : salesRequest.getConditions()) { switch (condition.getOperator()) { case "equals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .equal(root.get(condition.getField()), condition.getValue())); break; case "greaterThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThan( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThan": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder .lessThan(root.get(condition.getField()), (Comparable) condition.getValue())); break; case "greaterThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThanOrEquals": spec = spec.and((root, query, criteriaBuilder) -> criteriaBuilder.lessThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; default: createLogger.createLogger("error", path, "POST", "Operator " + condition.getOperator() + " is not supported.", ""); throw new IllegalArgumentException( "Operator " + condition.getOperator() + " is not supported."); } } } // Fetch the data with pagination for sales items pageable = PageRequest.of(salesRequest.getPage() != null ? salesRequest.getPage() : 0, // Page number is // zero-based salesRequest.getSize() != null ? salesRequest.getSize() : 20); salesPage = salesItemRepository.findAll(spec, pageable); // Group sales items by product_id groupedSalesData = new HashMap<>(); salesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { Long productId = Long.valueOf(item.getProduct_id()); String productName = productRepository.findProductNameById(productId, tenantName); Long categoryId = productRepository.findCategoryIdByProductId(productId, tenantName); Long brandId = productRepository.findBrandIdByProductId(productId, tenantName); String categoryName = categoryRepository.findCategoryNameById(categoryId, tenantName); String brandName = brandRepository.findBrandNameById(brandId, tenantName); groupedSalesData.computeIfAbsent(productId, id -> { Map data = new LinkedHashMap<>(); data.put("product_id", productId); data.put("product_name", productName); data.put("category_id", categoryId); data.put("category_name", categoryName); data.put("brand_id", brandId); data.put("brand_name", brandName); data.put("quantity", 0); data.put("total_price", 0.0); return data; }); Map data = groupedSalesData.get(productId); data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); data.put("total_price", MoneyUtils .truncateToTwoDecimals((Double) data.get("total_price") + item.getTotal_price())); data.put("unit_price", item.getUnit_price()); // Assuming unit price is consistent for each // product } }); // Prepare reportData list from the grouped data salesDataList = new ArrayList<>(groupedSalesData.values()); // Sort salesDataList in descending order based on quantity salesDataList.sort((a, b) -> Integer.compare((Integer) b.get("quantity"), (Integer) a.get("quantity"))); // Prepare column details column = new LinkedHashMap<>(); column.put("product_id", "PRODUCT ID"); column.put("product_name", "PRODUCT NAME"); column.put("category_id", "CATEGORY ID"); column.put("category_name", "CATEGORY NAME"); column.put("brand_id", "BRAND ID"); column.put("brand_name", "BRAND NAME"); column.put("quantity", "QUANTITY"); column.put("total_price", "TOTAL PRICE"); column.put("unit_price", "UNIT PRICE"); // Prepare pagination details pagination = new LinkedHashMap<>(); pagination.put("currentPage", salesPage.getNumber() + 1); // Page number is 0-based pagination.put("totalPages", salesPage.getTotalPages()); pagination.put("pageSize", pageable.getPageSize()); pagination.put("totalRecords", groupedSalesData.size()); // Calculate summary details totalRevenue = salesPage.getContent().stream().mapToDouble(SalesItem::getTotal_price).sum(); totalItemsSold = salesPage.getContent().stream().mapToInt(SalesItem::getQuantity).sum(); // Find the top-selling product productSalesMap = new HashMap<>(); salesPage.getContent().forEach(item -> { if ((item.getProduct_id() != null) && "product".equals(item.getType())) { productSalesMap.put(Long.valueOf(item.getProduct_id()), productSalesMap.getOrDefault(item.getProduct_id(), 0) + item.getQuantity()); } }); topSellingProductId = productSalesMap.entrySet().stream().max(Map.Entry.comparingByValue()) .map(Map.Entry::getKey).orElse(null); // Fetch the product name using the topSellingProductId topSellingProductName = null; if (topSellingProductId != null) { topSellingProductName = productRepository.findProductNameById(topSellingProductId, tenantName); } // Prepare summary details summary = new LinkedHashMap<>(); summary.put("totalRevenue", MoneyUtils.truncateToTwoDecimals(totalRevenue)); summary.put("totalItemsSold", totalItemsSold); summary.put("topSellingProduct", topSellingProductName); // Prepare response data salesData = new LinkedHashMap<>(); salesData.put("module", "sales"); salesData.put("reportType", reportType); salesData.put("startDate", String.valueOf(fromDate)); salesData.put("endDate", String.valueOf(toDate)); salesData.put("reportData", salesDataList); salesData.put("column", column); salesData.put("pagination", pagination); salesData.put("summary", summary); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", salesData); case "salesReturn": // Create a specification for the query Specification specs = Specification.where(null); // Add tenant name filtering specs = specs .and((root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("tenant"), tenantName)); // Add date range filtering if (salesRequest.getStartDate() != null && salesRequest.getEndDate() != null) { specs = specs.and((root, query, criteriaBuilder) -> criteriaBuilder.between(root.get("created_at"), fromDateTime, toDateTime)); } // Add dynamic conditions if (salesRequest.getConditions() != null) { for (SalesRequest.Condition condition : salesRequest.getConditions()) { switch (condition.getOperator()) { case "equals": specs = specs.and((root, query, criteriaBuilder) -> criteriaBuilder .equal(root.get(condition.getField()), condition.getValue())); break; case "greaterThan": specs = specs.and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThan( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThan": specs = specs.and((root, query, criteriaBuilder) -> criteriaBuilder .lessThan(root.get(condition.getField()), (Comparable) condition.getValue())); break; case "greaterThanOrEquals": specs = specs .and((root, query, criteriaBuilder) -> criteriaBuilder.greaterThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; case "lessThanOrEquals": specs = specs.and((root, query, criteriaBuilder) -> criteriaBuilder.lessThanOrEqualTo( root.get(condition.getField()), (Comparable) condition.getValue())); break; default: createLogger.createLogger("error", path, "POST", "Operator " + condition.getOperator() + " is not supported.", ""); throw new IllegalArgumentException( "Operator " + condition.getOperator() + " is not supported."); } } } // Fetch the data with pagination for sales return items pageable = PageRequest.of(salesRequest.getPage() != null ? salesRequest.getPage() : 0, // Page number is // zero-based salesRequest.getSize() != null ? salesRequest.getSize() : 20); Page salesReturnPage = salesReturnItemRepository.findAll(specs, pageable); // Group sales return items by product_id Map> groupedSalesReturnData = new HashMap<>(); salesReturnPage.getContent().forEach(item -> { Long productId = item.getProduct_id(); String productName = productRepository.findProductNameById(productId, tenantName); Long categoryId = productRepository.findCategoryIdByProductId(productId, tenantName); Long brandId = productRepository.findBrandIdByProductId(productId, tenantName); String categoryName = categoryRepository.findCategoryNameById(categoryId, tenantName); String brandName = brandRepository.findBrandNameById(brandId, tenantName); groupedSalesReturnData.computeIfAbsent(productId, id -> { Map data = new LinkedHashMap<>(); data.put("product_id", productId); data.put("product_name", productName); data.put("category_id", categoryId); data.put("category_name", categoryName); data.put("brand_id", brandId); data.put("brand_name", brandName); data.put("quantity", 0); return data; }); Map data = groupedSalesReturnData.get(productId); data.put("quantity", (Integer) data.get("quantity") + item.getQuantity()); }); // Prepare reportData list from the grouped data List> salesReturnDataList = new ArrayList<>(groupedSalesReturnData.values()); // Prepare column details column = new LinkedHashMap<>(); column.put("product_id", "PRODUCT ID"); column.put("product_name", "PRODUCT NAME"); column.put("category_id", "CATEGORY ID"); column.put("category_name", "CATEGORY NAME"); column.put("brand_id", "BRAND ID"); column.put("brand_name", "BRAND NAME"); column.put("quantity", "QUANTITY"); // Prepare pagination details pagination = new LinkedHashMap<>(); pagination.put("currentPage", salesReturnPage.getNumber() + 1); // Page number is 0-based pagination.put("totalPages", salesReturnPage.getTotalPages()); pagination.put("pageSize", pageable.getPageSize()); pagination.put("totalRecords", groupedSalesReturnData.size()); int totalItemsReturned = salesReturnPage.getContent().stream().mapToInt(SalesReturnItem::getQuantity) .sum(); // Find the top-selling product Map productReturnMap = new HashMap<>(); salesReturnPage.getContent().forEach(item -> { productReturnMap.put(item.getProduct_id(), productReturnMap.getOrDefault(item.getProduct_id(), 0) + item.getQuantity()); }); Long topReturnedProductId = productReturnMap.entrySet().stream().max(Map.Entry.comparingByValue()) .map(Map.Entry::getKey).orElse(null); // Fetch the product name using the topSellingProductId String topReturnedProductName = null; if (topReturnedProductId != null) { topReturnedProductName = productRepository.findProductNameById(topReturnedProductId, tenantName); } // Prepare summary details summary = new LinkedHashMap<>(); summary.put("totalItemsReturned", totalItemsReturned); summary.put("topReturnedProduct", topReturnedProductName); // Prepare response data Map salesReturnData = new LinkedHashMap<>(); salesReturnData.put("module", "sales"); salesReturnData.put("reportType", reportType); salesReturnData.put("startDate", String.valueOf(fromDate)); salesReturnData.put("endDate", String.valueOf(toDate)); salesReturnData.put("reportData", salesReturnDataList); salesReturnData.put("column", column); salesReturnData.put("pagination", pagination); salesReturnData.put("summary", summary); // Create and return the structured ApiResponseStructure return new ApiResponseStructure<>("Success", 200, "Data retrieved.", salesReturnData); default: createLogger.createLogger("error", path, "POST", "Invalid report type provided.", ""); return new ApiResponseStructure<>("Error", 400, "Invalid report type provided.", new HashMap<>()); } } }