Subversion Repositories SmartDukaan

package com.spice.profitmandi.web.controller;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;

import com.spice.profitmandi.common.model.ItemDescriptionModel;

import com.spice.profitmandi.common.util.FileUtil;

import com.spice.profitmandi.common.util.FormattingUtils;

import com.spice.profitmandi.dao.entity.auth.AuthUser;

import com.spice.profitmandi.dao.entity.catalog.CategorisedCatalog;

import com.spice.profitmandi.dao.entity.catalog.Item;

import com.spice.profitmandi.dao.entity.inventory.Vendor;

import com.spice.profitmandi.dao.entity.inventory.VendorCatalogPricing;

import com.spice.profitmandi.dao.entity.inventory.VendorCatalogPricingLog;

import com.spice.profitmandi.dao.entity.warehouse.Supplier;

import com.spice.profitmandi.dao.enumuration.inventory.VendorCatalogPricingStatus;

import com.spice.profitmandi.dao.model.VendorCatalogPricingModel;

import com.spice.profitmandi.dao.model.VendorPriceCircularModel;

import com.spice.profitmandi.dao.repository.auth.AuthRepository;

import com.spice.profitmandi.dao.repository.catalog.CategorisedCatalogRepository;

import com.spice.profitmandi.dao.repository.catalog.ItemRepository;

import com.spice.profitmandi.dao.repository.inventory.VendorCatalogPricingLogRepository;

import com.spice.profitmandi.dao.repository.inventory.VendorCatalogPricingRepository;

import com.spice.profitmandi.dao.repository.inventory.VendorRepository;

import com.spice.profitmandi.dao.repository.transaction.OrderRepository;

import com.spice.profitmandi.dao.repository.warehouse.SupplierRepository;

import com.spice.profitmandi.service.authentication.RoleManager;

import com.spice.profitmandi.service.inventory.VendorCatalogPricingService;

import com.spice.profitmandi.service.order.OrderService;

import com.spice.profitmandi.service.warehouse.WarehouseService;

import com.spice.profitmandi.web.model.LoginDetails;

import com.spice.profitmandi.web.util.CookiesProcessor;

import com.spice.profitmandi.web.util.MVCResponseSender;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.core.io.ClassPathResource;

import org.springframework.core.io.InputStreamResource;

import org.springframework.http.HttpHeaders;

import org.springframework.http.MediaType;

import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Controller;

import org.springframework.transaction.annotation.Transactional;

import org.springframework.ui.Model;

import org.springframework.web.bind.annotation.*;

import org.springframework.web.multipart.MultipartFile;

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 javax.mail.MessagingException;

import javax.servlet.http.HttpServletRequest;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.util.*;

import java.util.stream.Collectors;

@Controller

@Transactional(rollbackFor = Throwable.class)

public class VendorController {

    @Autowired

    VendorRepository vendorRepository;

    @Autowired

    private CookiesProcessor cookiesProcessor;

    @Autowired

    private RoleManager roleManager;

    @Autowired

    private AuthRepository authRepository;

    @Autowired

    private OrderRepository orderRepository;

    @Autowired

    private OrderService orderService;

    @Autowired

    private VendorCatalogPricingService vendorCatalogPricingService;

    @Autowired

    private VendorCatalogPricingLogRepository vendorCatalogPricingLogRepository;

    @Autowired

    private VendorCatalogPricingRepository vendorCatalogPricingRepository;

    @Autowired

    private MVCResponseSender mvcResponseSender;

    @Autowired

    private ItemRepository itemRepository;

    @Autowired

    private SupplierRepository supplierRepository;

    @Autowired

    private WarehouseService warehouseService;

    @Autowired

    CategorisedCatalogRepository categorisedCatalogRepository;

    private static final Logger LOGGER = LogManager.getLogger(VendorController.class);

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

    public String vendorCatalogPricing() throws Exception {

        return "vendor-catalog-pricing";

    }

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

    public String getVendorCatalogPricingByModel(@RequestParam int catalogId, Model model) throws Exception {

        List<Integer> vendorIds = new ArrayList<>();

        Map<Integer, VendorCatalogPricing> vendorCatalogPricingMap = new HashMap<>();

        Map<Integer, VendorCatalogPricingLog> vendorCatalogPricingLogMap = new HashMap<>();

        List<VendorCatalogPricing> vendorCatalogPricing = vendorCatalogPricingRepository.selectByCatalogId(catalogId);

        if (!vendorCatalogPricing.isEmpty()) {

            vendorIds.addAll(vendorCatalogPricing.stream().distinct().map(x -> x.getVendorId()).collect(Collectors.toList()));

            vendorCatalogPricingMap = vendorCatalogPricing.stream().collect(Collectors.toMap(x -> x.getVendorId(), x -> x));

        }

        List<VendorCatalogPricingLog> vendorCatalogPricingLog = vendorCatalogPricingLogRepository.selectByCatalogId(catalogId, VendorCatalogPricingStatus.PENDING);

        if (!vendorCatalogPricingLog.isEmpty()) {

            vendorIds.addAll(vendorCatalogPricingLog.stream().distinct().map(x -> x.getVendorId()).collect(Collectors.toList()));

            vendorCatalogPricingLogMap = vendorCatalogPricingLog.stream().filter(x -> x.getStatus().equals(VendorCatalogPricingStatus.PENDING)).collect(Collectors.toMap(x -> x.getVendorId(), x -> x));

        }

        LOGGER.info("VendorIds {}", vendorIds);

        List<Supplier> suppliers = new ArrayList<Supplier>();

        if (!vendorIds.isEmpty()) {

            suppliers.addAll(supplierRepository.selectBySupplierIds(vendorIds));

        }

        LOGGER.info("suppliers {}", suppliers);

        model.addAttribute("suppliers", suppliers);

        model.addAttribute("vendorCatalogPricingMap", vendorCatalogPricingMap);

        model.addAttribute("vendorCatalogPricingLogMap", vendorCatalogPricingLogMap);

        model.addAttribute("catalogId", catalogId);

        return "vendor-catalog-pricing-view";

    }

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

    public String getVendor(Model model, @RequestParam String query) throws Throwable {

        List<Supplier> vendors = supplierRepository.selectAll().stream().filter(x -> x.getName().toLowerCase().matches(".*?" + query.toLowerCase() + ".*?"))

                .filter(x -> x.isStatus()).collect(Collectors.toList());

        model.addAttribute("response1", mvcResponseSender.createResponseString(vendors));

        return "response";

    }

    @RequestMapping(value = "/createVendorCatalogPricing", method = RequestMethod.POST)

    public String createVendorCatalogPricing(HttpServletRequest request, @RequestBody VendorCatalogPricingModel vendorCatalogPricingModel, Model model) throws Exception {

        LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

        AuthUser authUser = authRepository.selectByEmailOrMobile(loginDetails.getEmailId());

        vendorCatalogPricingModel.setAuthId(authUser.getId());

        LOGGER.info("vendorCatalogPricingModel {}", vendorCatalogPricingModel);

        vendorCatalogPricingService.createVendorCatalogPricingLog(vendorCatalogPricingModel);

        model.addAttribute("response1", mvcResponseSender.createResponseString(true));

        return "response";

    }

    @RequestMapping(value = "/createBulkPricing")

    public String createBulkPricing() throws ProfitMandiBusinessException, MessagingException, IOException {

        return "bulk-vendor-pricing";

    }

    @PostMapping(value = "/bulkVendorCatalogPricing/upload")

    public String uploadCatalog(HttpServletRequest request, @RequestPart("file") MultipartFile file, Model model)

            throws Exception {

        LoginDetails fofoDetails = cookiesProcessor.getCookiesObject(request);

        int authId = authRepository.selectByEmailOrMobile(fofoDetails.getEmailId()).getId();

        LOGGER.info("authId - {}", authId);

        vendorCatalogPricingService.parseBulkVendorCatalogPricing(file, authId);

        model.addAttribute("response1", mvcResponseSender.createResponseString(true));

        return "response";

    }

    @GetMapping("/bulkVendorCatalogPricing/downloadReferenceFile")

    public ResponseEntity<?> downloadReferenceFile() throws IOException {

        ClassPathResource dummyFile = new ClassPathResource("vendor-catalog-pricing-file.xlsx"); // Make sure the file is in the resources folder

        return ResponseEntity.ok()

                .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=dummy-file.xlsx")

                .contentType(MediaType.APPLICATION_OCTET_STREAM)

                .body(new InputStreamResource(dummyFile.getInputStream()));

    }

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

    public String vendorCatalogPricingRequests(Model model) throws Exception {

        List<VendorCatalogPricingLog> vendorCatalogPricingRequests = vendorCatalogPricingLogRepository.selectByStatus(VendorCatalogPricingStatus.PENDING);

        Map<Integer, AuthUser> authUserMap = authRepository.selectAllActiveUser().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        model.addAttribute("vendorCatalogPricingRequests", vendorCatalogPricingRequests);

        if (!vendorCatalogPricingRequests.isEmpty()) {

            Set<Integer> catalogIds = vendorCatalogPricingRequests.stream().map(x -> x.getCatalogId()).collect(Collectors.toSet());

            List<Integer> vendorIds = vendorCatalogPricingRequests.stream().map(x -> x.getVendorId()).collect(Collectors.toList());

            Map<Integer, List<Item>> itemMap = itemRepository.selectAllByCatalogIds(catalogIds).stream().collect(Collectors.groupingBy(x -> x.getCatalogItemId()));

            model.addAttribute("itemMap", itemMap);

        }

        Map<Integer, Vendor> vendorMap = vendorRepository.selectAll().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        model.addAttribute("authMap", authUserMap);

        model.addAttribute("vendorMap", vendorMap);

        model.addAttribute("vendorPricingStatus", VendorCatalogPricingStatus.values());

        return "vendor-catalog-pricing-request";

    }

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

    public String vendorCatalogPricingPendingRequests() throws Exception {

        return "vendor-catalog-pricing-pending-request";

    }

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

    public String datewiseVendorCatalogPricingPendingRequests(@RequestParam LocalDateTime startDate, @RequestParam LocalDateTime endDate, Model model) throws Exception {

        List<VendorCatalogPricingLog> vendorCatalogPricingRequests = vendorCatalogPricingLogRepository.selectByDate(startDate, endDate);

        Map<Integer, AuthUser> authUserMap = authRepository.selectAllActiveUser().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        LOGGER.info("vendorCatalogPricingRequests {}", vendorCatalogPricingRequests);

        model.addAttribute("vendorCatalogPricingRequests", vendorCatalogPricingRequests);

        if (!vendorCatalogPricingRequests.isEmpty()) {

            Set<Integer> catalogIds = vendorCatalogPricingRequests.stream().map(x -> x.getCatalogId()).collect(Collectors.toSet());

            Map<Integer, List<Item>> itemMap = itemRepository.selectAllByCatalogIds(catalogIds).stream().collect(Collectors.groupingBy(x -> x.getCatalogItemId()));

            model.addAttribute("itemMap", itemMap);

        }

        Map<Integer, Vendor> vendorMap = vendorRepository.selectAll().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        model.addAttribute("authMap", authUserMap);

        model.addAttribute("vendorMap", vendorMap);

        model.addAttribute("vendorPricingStatus", VendorCatalogPricingStatus.values());

        return "vendor-catalog-pricing-pending-request-table";

    }

    @RequestMapping(value = "/verifyVendorCatalogPricingRequest", method = RequestMethod.POST)

    public String verifyVendorCatalogPricingRequest(HttpServletRequest request, @RequestParam int id, @RequestParam VendorCatalogPricingStatus status, Model model) throws Exception {

        LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

        AuthUser authUser = authRepository.selectByEmailOrMobile(loginDetails.getEmailId());

        VendorCatalogPricingLog vendorCatalogPricingLog = vendorCatalogPricingLogRepository.selectById(id);

        if (status.equals(VendorCatalogPricingStatus.APPROVED)) {

            List<VendorCatalogPricingLog> vendorCatalogPricingLogsOnEffectedDate = vendorCatalogPricingLogRepository.selectByEffectedDateAndCatalogId(vendorCatalogPricingLog.getVendorId(), vendorCatalogPricingLog.getCatalogId(), vendorCatalogPricingLog.getEffectedOn());

            Optional<VendorCatalogPricingLog> optionalVendorCatalogPricingLog = vendorCatalogPricingLogsOnEffectedDate.stream().filter(x -> x.getStatus().equals(VendorCatalogPricingStatus.APPROVED)).findAny();

            if (optionalVendorCatalogPricingLog.isPresent()) {

                VendorCatalogPricingLog approvedPricingLog = optionalVendorCatalogPricingLog.get();

                approvedPricingLog.setStatus(VendorCatalogPricingStatus.REJECTED);

                approvedPricingLog.setUpdatedTimestamp(LocalDateTime.now());

            }

        }

        vendorCatalogPricingLog.setStatus(status);

        vendorCatalogPricingLog.setUpdatedTimestamp(LocalDateTime.now());

        if (vendorCatalogPricingLog.getStatus().equals(VendorCatalogPricingStatus.APPROVED)) {

            vendorCatalogPricingLog.setApprovedBy(authUser.getId());

            vendorCatalogPricingLog.setApprovedTimestamp(LocalDateTime.now());

            vendorCatalogPricingService.createVendorCatalogPricing(vendorCatalogPricingLog);

        }

        model.addAttribute("response1", mvcResponseSender.createResponseString(true));

        return "response";

    }

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

    public String vendorPriceCircular() throws Exception {

        return "vendor-price-circular";

    }

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

    public String getVendorPriceCircular(@RequestParam(defaultValue = "0") int vendorId, @RequestParam LocalDate effectedDate, Model model) throws Exception {

        List<VendorPriceCircularModel> vendorCatalogPricings = vendorCatalogPricingLogRepository.getVendorPricesOnDate(vendorId, effectedDate);

        Map<Integer, Supplier> supplierMap = supplierRepository.selectAll().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        if (!vendorCatalogPricings.isEmpty()) {

            Set<Integer> catalogIds = vendorCatalogPricings.stream().map(x -> x.getCatalogId()).collect(Collectors.toSet());

            Map<Integer, List<Item>> itemMap = itemRepository.selectAllByCatalogIds(catalogIds).stream().collect(Collectors.groupingBy(x -> x.getCatalogItemId()));

            model.addAttribute("itemMap", itemMap);

        }

        model.addAttribute("vendorCatalogPricings", vendorCatalogPricings);

        model.addAttribute("suppliers", supplierMap);

        return "vendor-price-circular-view";

    }

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

    public ResponseEntity<?> getSelectDownloadPriceCircularReport(@RequestParam(name = "startDate") LocalDate startDate) throws Exception {

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

        Map<Integer, Supplier> supplierMap = supplierRepository.selectAll().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

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

        List<List<?>> rows = new ArrayList<>();

        if (!vendorCatalogPricings.isEmpty()) {

            Set<Integer> catalogIds = vendorCatalogPricings.stream().map(x -> x.getCatalogId()).collect(Collectors.toSet());

            itemMap = itemRepository.selectAllByCatalogIds(catalogIds).stream().collect(Collectors.groupingBy(x -> x.getCatalogItemId()));

        }

        for (VendorPriceCircularModel vcp : vendorCatalogPricings) {

            Supplier supplier = supplierMap.get(vcp.getVendorId());

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

            if (supplier == null || supplier.isInternal() || items == null || items.isEmpty()) {

                continue;

            }

            rows.add(Arrays.asList(vcp.getCatalogId(), supplier.getName(), items.get(0).getItemDescriptionNoColor(), vcp.getTransferPrice(), vcp.getDealerPrice(), vcp.getMop(),

                    FormattingUtils.formatDate(vcp.getEffectedOn())));

        }

        org.apache.commons.io.output.ByteArrayOutputStream baos = FileUtil

                .getCSVByteStream(Arrays.asList("Catalog Id", "Vendor Name", "Model name", "TP", "DP", "MOP",

                        "Effected On"), rows);

        ResponseEntity<?> responseEntity = orderService.downloadReportInCsv(baos, rows, "price circular report");

        return responseEntity;

    }

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

    public String getPricing(HttpServletRequest request, @RequestParam int vendorId, @RequestParam int itemId, @RequestParam LocalDate onDate, Model model) throws Exception {

        List<Integer> checkRequiredCategoryId = Arrays.asList(10006);

        List<String> checkRequiredCategoryGroup = Arrays.asList("Handset");

        Item item = itemRepository.selectById(itemId);

        List<String> brandsIgnoreToCheckCategory = Arrays.asList("Dummy", "Live Demo");

        if (!brandsIgnoreToCheckCategory.contains(item.getBrand())) {

            if (checkRequiredCategoryId.contains(item.getCategoryId()) || checkRequiredCategoryGroup.contains(item.getProductGroup())) {

                CategorisedCatalog categorisedCatalog = categorisedCatalogRepository.getCurrentCatalogMovement(item.getCatalogItemId());

                if (categorisedCatalog == null || categorisedCatalog.getStatus().getValue().isEmpty()) {

                    model.addAttribute("response1", mvcResponseSender.createResponseString(false));

                    return "response";

                }

            }

        }

        VendorPriceCircularModel vendorPriceCircularModel = vendorCatalogPricingLogRepository.getVendorPriceOnDate(vendorId, item.getCatalogItemId(), onDate);

        LOGGER.info("vendorCatalogPricing {}", vendorPriceCircularModel);

        model.addAttribute("response1", mvcResponseSender.createResponseString(vendorPriceCircularModel));

        return "response";

    }

    private void addVendorPricingIfMissing(int itemId, int vendorId) throws ProfitMandiBusinessException {

        Supplier supplier = supplierRepository.selectById(vendorId);

        Item item = itemRepository.selectById(itemId);

        int vendorWithPricing = 0;

        if (supplier.isInternal() && (item.getBrand().equals("Samsung") || item.getCategoryId() != 10006)) {

            VendorPriceCircularModel vendorPriceCircularModel = vendorCatalogPricingLogRepository.getVendorPriceOnDate(vendorId, item.getCatalogItemId(), LocalDate.now());

            LOGGER.info("VendorPriceCircularModel - {}", vendorPriceCircularModel);

            if (vendorPriceCircularModel == null) {

                if (item.getBrand().equals("Samsung")) {

                    //BSB

                    vendorWithPricing = 334;

                } else if (item.getCategoryId() != 10006) {

                    vendorWithPricing = vendorCatalogPricingLogRepository.selectByCatalogId(item.getCatalogItemId(), VendorCatalogPricingStatus.APPROVED).stream().map(x -> x.getVendorId())

                            .findFirst().orElse(null);

                }

                VendorPriceCircularModel existingPriceCircular = vendorCatalogPricingLogRepository.getVendorPriceOnDate(vendorWithPricing, item.getCatalogItemId(), LocalDate.now());

                LOGGER.info("Existing price circular - {}", existingPriceCircular);

                if (existingPriceCircular == null) {

                    throw new ProfitMandiBusinessException("Pricing missing for vendor - " + vendorId, "Pricing missing for vendor - " + vendorId, "Pricing missing for vendor - " + vendorId);

                }

                VendorCatalogPricingModel vendorCatalogPricingModel = new VendorCatalogPricingModel();

                vendorCatalogPricingModel.setAuthId(52);

                vendorCatalogPricingModel.setVendorId(vendorId);

                vendorCatalogPricingModel.setCatalogId(existingPriceCircular.getCatalogId());

                vendorCatalogPricingModel.setDp(existingPriceCircular.getDealerPrice());

                vendorCatalogPricingModel.setEffectedOn(existingPriceCircular.getEffectedOn());

                vendorCatalogPricingModel.setMop(existingPriceCircular.getMop());

                vendorCatalogPricingModel.setTp(existingPriceCircular.getTransferPrice());

                vendorCatalogPricingModel.setCatalogId(item.getCatalogItemId());

                VendorCatalogPricingLog vendorCatalogPricingLog = vendorCatalogPricingService.createVendorCatalogPricingLog(vendorCatalogPricingModel);

                vendorCatalogPricingLog.setStatus(VendorCatalogPricingStatus.APPROVED);

                vendorCatalogPricingLog.setUpdatedTimestamp(LocalDateTime.now());

                //Deenanath Auth Id

                vendorCatalogPricingLog.setApprovedBy(138);

                vendorCatalogPricingLog.setApprovedTimestamp(LocalDateTime.now());

                vendorCatalogPricingService.createVendorCatalogPricing(vendorCatalogPricingLog);

            }

        }

    }

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

    public String getItemPricing(Model model, @RequestParam int vendorId, @RequestParam String query) throws Exception {

        String query1 = query.toLowerCase();

        int itemId = 0;

        try {

            itemId = Integer.parseInt(query1);

            addVendorPricingIfMissing(itemId, vendorId);

        } catch (NumberFormatException e) {

            LOGGER.info("Ignore number format exception");

            // e.printStackTrace();

        }

        LOGGER.info("Vendor Id - {}", vendorId);

        List<ItemDescriptionModel> partnersItemDescription = warehouseService.getAllPartnerItemStringDescription(vendorId).parallelStream().filter(x -> x.getItemDescription().toLowerCase().matches(".*?" + query1 + ".*?")).collect(Collectors.toList());

        LOGGER.info("partnersItemDescription" + partnersItemDescription);

        model.addAttribute("response1", mvcResponseSender.createResponseString(partnersItemDescription));

        return "response";

    }

    @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 + ")";

    }

}