Subversion Repositories SmartDukaan

package com.spice.profitmandi.service.catalog;

import com.spice.profitmandi.common.enumuration.ItemType;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

import in.shop2020.model.v1.catalog.status;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

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

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

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

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

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

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

import org.springframework.stereotype.Component;

import org.springframework.web.multipart.MultipartFile;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.util.Arrays;

import java.util.List;

import java.util.Map;

import java.util.stream.Collectors;

@Component

public class ItemLoaderService {

    @Autowired

    BrandsRepository brandsRepository;

    @Autowired

    ItemRepository itemRepository;

    @Autowired

    VendorCatalogPricingRepository vendorCatalogPricingRepository;

    @Autowired

    StateGstRateRepository stateGstRateRepository;

    @Autowired

    VendorItemPricingRepository vendorItemPricingRepository;

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

    public void parse(MultipartFile file) throws Exception {

        XSSFWorkbook myWorkBook = new XSSFWorkbook(file.getInputStream());

        myWorkBook.setMissingCellPolicy(Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);

        // Return first sheet from the XLSX workbook

        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());

        for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {

            XSSFRow row = mySheet.getRow(rowNumber);

            ItemLoaderModel itemLoaderModel = this.createLoaderModel(row);

            try {

                LOGGER.info("rownum - {}", rowNumber);

                this.addItem(itemLoaderModel);

            } catch (ProfitMandiBusinessException e) {

                throw new ProfitMandiBusinessException(e.getRejectedType(), e.getRejectedValue(), e.getCode() + "at " + itemLoaderModel.getRowIndex());

            }

        }

    }

    private ItemLoaderModel createLoaderModel(XSSFRow row) throws ProfitMandiBusinessException {

        ItemLoaderModel itemLoaderModel = new ItemLoaderModel();

        int i = 0;

        itemLoaderModel.setRowIndex(row.getRowNum());

        try {

            itemLoaderModel.setProductGroup(row.getCell(i++).getStringCellValue().trim());

            itemLoaderModel.setCategoryId((int) row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setCategory(row.getCell(i++).getStringCellValue().trim());

            itemLoaderModel.setHsnCode(String.valueOf((int) row.getCell(i++).getNumericCellValue()));

            itemLoaderModel.setBrand(StringUtils.normalizeWhitespace(row.getCell(i++).getStringCellValue()));

            Cell modelNumberCell = row.getCell(i++);

            if(modelNumberCell== null || StringUtils.normalizeWhitespace(modelNumberCell.getStringCellValue()).isEmpty()) {

                throw new Exception("Model Number cant be null or empty");

            } else {

                itemLoaderModel.setModelNumber(StringUtils.normalizeWhitespace(modelNumberCell.getStringCellValue()));

            }

            Cell modelNameCell = row.getCell(i++);

            if (modelNameCell == null) {

                itemLoaderModel.setModelName("");

            } else {

                itemLoaderModel.setModelName(StringUtils.normalizeWhitespace(modelNameCell.getStringCellValue()));

            }

            itemLoaderModel.setColor(StringUtils.normalizeWhitespace(row.getCell(i++).getStringCellValue()));

            //MRP	SP	MOP	DP	TP	NLC	Startdate	Preferred Vendor	Risky

            // Weight	Item type(1 for Serialized and 2 for non-serialized)	TaxRate

            itemLoaderModel.setMrp(row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setSp(row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setMop(row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setDp(row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setTp(row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setNlc(row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setStartDate(Utils.convertToLocalDateTime(row.getCell(i++).getDateCellValue()));

            itemLoaderModel.setPreferredVendor((int) (row.getCell(i++).getNumericCellValue()));

            itemLoaderModel.setRisky(row.getCell(i++).getNumericCellValue() == 0 ? false : true);

            itemLoaderModel.setWeight(row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setItemType((int) row.getCell(i++).getNumericCellValue());

            itemLoaderModel.setTaxRate(row.getCell(i++).getNumericCellValue() * 100);

            // optional trailing column: brand's own SKU/article code

            Cell brandIdentifierCell = row.getCell(i++);

            if (brandIdentifierCell != null) {

                itemLoaderModel.setBrandIdentifier(

                        StringUtils.normalizeWhitespace(brandIdentifierCell.getStringCellValue()));

            }

        } catch (Throwable e) {

            LOGGER.info(e.getCause());

            throw new ProfitMandiBusinessException("Field", "Field at row - " + row.getRowNum() + ", column - " + (i - 1),

                    "Invalid field value at - " + ExcelUtils.toAlphabet(i - 1) + (row.getRowNum() + 1) + ", " + ExcelUtils.getCellValue(row.getCell(i - 1)));

        }

        return itemLoaderModel;

    }

    @Autowired

    SupplierRepository supplierRepository;

    private void validateItemLoaderModel(ItemLoaderModel itemLoaderModel) throws ProfitMandiBusinessException {

        if (Utils.compareDouble(itemLoaderModel.getTaxRate(), 28d) > 0)

            throw new ProfitMandiBusinessException("Tax Rate should not be above 28%", "", "");

        if (itemLoaderModel.getMrp() < itemLoaderModel.getSp())

            throw new ProfitMandiBusinessException("Selling Price should not be greater than MRP", "", "");

        Supplier supplier = supplierRepository.selectById(itemLoaderModel.getPreferredVendor());

        if (supplier == null) throw new ProfitMandiBusinessException("Invalid Supplier", "", "");

    }

    @Autowired

    CatalogRepository catalogRepository;

    @Autowired

    CategorisedCatalogRepository categorisedCatalogRepository;

    private void addItem(ItemLoaderModel itemLoaderModel) throws ProfitMandiBusinessException {

        this.validateItemLoaderModel(itemLoaderModel);

        Catalog catalog = catalogRepository.selectCatalog(itemLoaderModel.getBrand(), itemLoaderModel.getModelNumber(), itemLoaderModel.getModelName());

        if (catalog != null) {

            List<Item> items = itemRepository.selectAllByCatalogItemId(catalog.getId());

            long similarColorCount = items.stream().filter(x -> itemLoaderModel.getColor().equals(x.getColorNatural())).count();

            if (similarColorCount > 0) {

                throw new ProfitMandiBusinessException("Color already exist", "Pls check", itemLoaderModel.getColor());

            }

            List<Integer> categoryIds = items.stream().map(x -> x.getCategoryId()).distinct().collect(Collectors.toList());

            if (categoryIds.size() > 1) {

                throw new ProfitMandiBusinessException("Model Wrongly Mapped to different categories", "Pls check", categoryIds.toString());

            } else if (categoryIds.get(0) != itemLoaderModel.getCategoryId()) {

                throw new ProfitMandiBusinessException("Model already Mapped to different category", "Pls check", categoryIds.toString());

            }

            List<Integer> catalogIds = items.stream().map(x -> x.getCategoryId()).distinct().collect(Collectors.toList());

            if (catalogIds.size() > 1) {

                throw new ProfitMandiBusinessException("Model Wrongly Mapped to different CatalogIds", "Pls check", catalogIds.toString());

            }

            Map<String, Long> colorsCount = items.stream().collect(Collectors.groupingBy(x -> x.getColorNatural(), Collectors.counting()));

            for (Map.Entry<String, Long> colorCountEntrySet : colorsCount.entrySet()) {

                if (colorCountEntrySet.getValue() > 1) {

                    throw new ProfitMandiBusinessException("Model repeats the same color", "Pls check", colorCountEntrySet.getKey());

                }

            }

            CategorisedCatalog categorisedCatalog = categorisedCatalogRepository.getCurrentCatalogMovement(catalog.getId());

            if (categorisedCatalog == null) {

                CategorisedCatalog categorisedCatalogNew = new CategorisedCatalog();

                categorisedCatalogNew.setCatalogId(catalog.getId());

                categorisedCatalogNew.setStatus(CatalogMovingEnum.RUNNING);

                categorisedCatalogNew.setStartDate(LocalDate.now());

                categorisedCatalogRepository.persist(categorisedCatalogNew);

            } else if (categorisedCatalog.getStatus().equals(CatalogMovingEnum.OTHER)) {

                categorisedCatalog.setStatus(CatalogMovingEnum.SLOWMOVING);

            }

        } else {

            catalog = new Catalog();

            BrandCatalog brand = brandsRepository.selectByBrand(itemLoaderModel.getBrand());

            if(brand == null) {

                    throw new ProfitMandiBusinessException("Brand Does not exist", "Pls check", itemLoaderModel.getBrand());

            }

            catalog.setBrandId(brand.getId());

            catalog.setBrand(itemLoaderModel.getBrand());

            catalog.setModelName(itemLoaderModel.getModelName());

            catalog.setModelNumber(itemLoaderModel.getModelNumber());

            catalog.setCategoryId(itemLoaderModel.getCategoryId());

            catalog.setCreated(LocalDateTime.now());

            catalog.setCreatedBy("System");

            catalogRepository.persist(catalog);

            CategorisedCatalog categorisedCatalog = new CategorisedCatalog();

            categorisedCatalog.setCatalogId(catalog.getId());

            categorisedCatalog.setStatus(CatalogMovingEnum.RUNNING);

            categorisedCatalog.setStartDate(LocalDate.now());

            categorisedCatalogRepository.persist(categorisedCatalog);

        }

        //Finally either got valid existing model or new model altogether

        Item item = new Item();

        item.setBrand(itemLoaderModel.getBrand());

        item.setModelName(itemLoaderModel.getModelName());

        item.setModelNumber(itemLoaderModel.getModelNumber());

        item.setColor(itemLoaderModel.getColor());

        item.setSellingPrice(new Float(Math.round(itemLoaderModel.getSp())));

        item.setMrp(new Float(Math.round(itemLoaderModel.getMrp())));

        item.setStartDate(itemLoaderModel.getStartDate());

        item.setCategoryId(itemLoaderModel.getCategoryId());

        item.setPreferredVendor(itemLoaderModel.getPreferredVendor());

        item.setRisky(itemLoaderModel.isRisky());

        item.setCatalogItemId(catalog.getId());

        if (itemLoaderModel.getBrandIdentifier() != null && !itemLoaderModel.getBrandIdentifier().isEmpty()) {

            item.setBrandIdentifier(itemLoaderModel.getBrandIdentifier());

        }

        if (itemLoaderModel.getWeight() <= 0) {

            throw new ProfitMandiBusinessException("Weight", "0", "Weight should be greater than 0");

        }

        item.setWeight(itemLoaderModel.getWeight());

        item.setStatus(status.PARTIALLY_ACTIVE);

        item.setUpdatedOn(LocalDateTime.now());

        item.setAddedOn(LocalDateTime.now());

        //if(item.getHsnCode())

        item.setHsnCode(itemLoaderModel.getHsnCode());

        ItemType itemType = ItemType.findByValue(itemLoaderModel.getItemType());

        if (itemType == null) {

            throw new ProfitMandiBusinessException("Item Type", "Invalid", "Should be 1 for SERIALIZED or 2 for Non Serialized");

        }

        item.setType(itemType);

        itemRepository.persist(item);

        //Logic for model here

        //Set Tax Rate

        StateGstRateModel stateGstRateModel = new StateGstRateModel();

        stateGstRateModel.setItemId(item.getId());

        stateGstRateModel.setIgstRate(itemLoaderModel.getTaxRate());

        stateGstRateRepository.addStateGstRates(Arrays.asList(stateGstRateModel));

        //Add Vendor

        this.addVendorItemPricing(item);

    }

    private void addVendorItemPricing(Item item) {

        List<VendorCatalogPricing> vendorCatalogPricingList = vendorCatalogPricingRepository.selectByCatalogId(item.getCatalogItemId());

        for (VendorCatalogPricing vendorCatalogPricing : vendorCatalogPricingList) {

            VendorItemPricing vendorItemPricing = new VendorItemPricing();

            vendorItemPricing.setItemId(item.getId());

            vendorItemPricing.setVendorId(vendorCatalogPricing.getVendorId());

            vendorItemPricing.setDp(vendorCatalogPricing.getDealerPrice());

            vendorItemPricing.setMop(vendorCatalogPricing.getMop());

            vendorItemPricing.setNlc(vendorCatalogPricing.getTransferPrice());

            vendorItemPricing.setTp(vendorCatalogPricing.getTransferPrice());

            vendorItemPricingRepository.persist(vendorItemPricing);

        }

    }

}