Subversion Repositories SmartDukaan

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

    @RequestMapping(value = "/downloadVendorPricingChangesReport", method = RequestMethod.GET)

    public ResponseEntity<?> downloadVendorPricingChangesReport(

            @RequestParam(name = "startDate") LocalDate startDate,

            @RequestParam(name = "endDate") LocalDate endDate) throws Exception {

        Map<Integer, Supplier> supplierMap = supplierRepository.selectAll().stream()

                .filter(s -> s.isStatus() && !s.isInternal())

                .collect(Collectors.toMap(Supplier::getId, s -> s));

        List<VendorPriceCircularModel> allBaseline = vendorCatalogPricingLogRepository.getVendorPricesOnDate(0, startDate);

        Map<Integer, List<VendorPriceCircularModel>> baselineByVendor = allBaseline.stream()

                .filter(p -> supplierMap.containsKey(p.getVendorId()))

                .collect(Collectors.groupingBy(VendorPriceCircularModel::getVendorId));

        List<VendorPriceCircularModel> allChanges = vendorCatalogPricingLogRepository.selectApprovedInDateRange(startDate, endDate);

        Map<Integer, List<VendorPriceCircularModel>> changesByVendor = allChanges.stream()

                .collect(Collectors.groupingBy(VendorPriceCircularModel::getVendorId));

        Set<Integer> allCatalogIds = new HashSet<>();

        allBaseline.stream().filter(p -> supplierMap.containsKey(p.getVendorId())).forEach(p -> allCatalogIds.add(p.getCatalogId()));

        allChanges.forEach(p -> allCatalogIds.add(p.getCatalogId()));

        Map<Integer, List<Item>> itemMap = new HashMap<>();

        if (!allCatalogIds.isEmpty()) {

            itemMap = itemRepository.selectAllByCatalogIds(allCatalogIds).stream()

                    .collect(Collectors.groupingBy(Item::getCatalogItemId));

        }

        ByteArrayOutputStream bos = buildVendorPricingChangesExcel(supplierMap, baselineByVendor, changesByVendor, itemMap);

        HttpHeaders headers = new HttpHeaders();

        headers.set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        headers.set("Content-disposition", "attachment; filename=vendor-pricing-changes-" + startDate + "-to-" + endDate + ".xlsx");

        headers.setContentLength(bos.toByteArray().length);

        InputStreamResource resource = new InputStreamResource(new ByteArrayInputStream(bos.toByteArray()));

        return new ResponseEntity<>(resource, headers, org.springframework.http.HttpStatus.OK);

    }

    private ByteArrayOutputStream buildVendorPricingChangesExcel(

            Map<Integer, Supplier> supplierMap,

            Map<Integer, List<VendorPriceCircularModel>> baselineByVendor,

            Map<Integer, List<VendorPriceCircularModel>> changesByVendor,

            Map<Integer, List<Item>> itemMap) throws Exception {

        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFSheet sheet = workbook.createSheet("Pricing Changes");

        CellStyle headerStyle = workbook.createCellStyle();

        Font boldFont = workbook.createFont();

        boldFont.setBold(true);

        headerStyle.setFont(boldFont);

        int rowNum = 0;

        XSSFRow colRow = sheet.createRow(rowNum++);

        String[] columns = {"Vendor", "Effective Date", "Model", "Catalog Id", "TP", "DP", "MOP"};

        for (int i = 0; i < columns.length; i++) {

            colRow.createCell(i).setCellValue(columns[i]);

            colRow.getCell(i).setCellStyle(headerStyle);

        }

        List<Map.Entry<Integer, Supplier>> sortedVendors = supplierMap.entrySet().stream()

                .sorted(Comparator.comparing(e -> e.getValue().getName()))

                .collect(Collectors.toList());

        for (Map.Entry<Integer, Supplier> vendorEntry : sortedVendors) {

            int vendorId = vendorEntry.getKey();

            String vendorName = vendorEntry.getValue().getName();

            List<VendorPriceCircularModel> baseline = baselineByVendor.getOrDefault(vendorId, Collections.emptyList());

            List<VendorPriceCircularModel> changes = changesByVendor.getOrDefault(vendorId, Collections.emptyList());

            if (baseline.isEmpty() && changes.isEmpty()) {

                continue;

            }

            Map<Integer, Float> tpTracker = new HashMap<>();

            for (VendorPriceCircularModel bp : baseline) {

                XSSFRow dataRow = sheet.createRow(rowNum++);

                dataRow.createCell(0).setCellValue(vendorName);

                dataRow.createCell(1).setCellValue(FormattingUtils.formatDate(bp.getEffectedOn()));

                dataRow.createCell(2).setCellValue(getModelName(itemMap, bp.getCatalogId()));

                dataRow.createCell(3).setCellValue(bp.getCatalogId());

                dataRow.createCell(4).setCellValue(bp.getTransferPrice());

                dataRow.createCell(5).setCellValue(bp.getDealerPrice());

                dataRow.createCell(6).setCellValue(bp.getMop());

                tpTracker.put(bp.getCatalogId(), bp.getTransferPrice());

            }

            for (VendorPriceCircularModel cp : changes) {

                Float lastTp = tpTracker.get(cp.getCatalogId());

                if (lastTp == null || Float.compare(lastTp, cp.getTransferPrice()) != 0) {

                    XSSFRow dataRow = sheet.createRow(rowNum++);

                    dataRow.createCell(0).setCellValue(vendorName);

                    dataRow.createCell(1).setCellValue(FormattingUtils.formatDate(cp.getEffectedOn()));

                    dataRow.createCell(2).setCellValue(getModelName(itemMap, cp.getCatalogId()));

                    dataRow.createCell(3).setCellValue(cp.getCatalogId());

                    dataRow.createCell(4).setCellValue(cp.getTransferPrice());

                    dataRow.createCell(5).setCellValue(cp.getDealerPrice());

                    dataRow.createCell(6).setCellValue(cp.getMop());

                    tpTracker.put(cp.getCatalogId(), cp.getTransferPrice());

                }

            }

        }

        for (int i = 0; i < 7; i++) {

            sheet.autoSizeColumn(i);

        }

        ByteArrayOutputStream bos = new ByteArrayOutputStream();

        workbook.write(bos);

        workbook.close();

        return bos;

    }

    private String getModelName(Map<Integer, List<Item>> itemMap, int catalogId) {

        List<Item> items = itemMap.get(catalogId);

        if (items != null && !items.isEmpty()) {

            return items.get(0).getItemDescriptionNoColor();

        }

        return "Unknown (" + catalogId + ")";

    }