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.Utils;

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.model.StateGstRateModel;

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

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

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

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 com.spice.profitmandi.service.inventory.VendorCatalogPricingService;

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.DateUtil;

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.io.FileInputStream;

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 {

    private static List<Character> ALPHABETS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".chars().mapToObj(c -> (char) c).collect(Collectors.toList());

    @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 {

                this.addItem(itemLoaderModel);

            } catch (ProfitMandiBusinessException e) {

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

            }

        }

    }

    private String toAlphabet(int number) {

        StringBuffer sb = new StringBuffer();

        boolean loop = true;

        while (loop) {

            sb.append(ALPHABETS.get(number % 26));

            number = number / 26;

            loop = number > 0;

        }

        return sb.reverse().toString();

    }

    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());

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

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

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

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

            itemLoaderModel.setModelNumber(row.getCell(i++).getStringCellValue());

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

            if (modelCell == null) {

                itemLoaderModel.setModelName("");

            } else {

                itemLoaderModel.setModelName(modelCell.getStringCellValue());

            }

            itemLoaderModel.setColor(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);

        } catch (Throwable e) {

            LOGGER.info(e.getCause());

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

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

        }

        return itemLoaderModel;

    }

    @Autowired

    SupplierRepository supplierRepository;

    private void validateItemLoaderModel(ItemLoaderModel itemLoaderModel) throws ProfitMandiBusinessException {

        if (itemLoaderModel.getTaxRate() > 28)

            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", "", "");

    }

    private String getCellValue(Cell cell) {

        if (cell == null) {

            return "Null cell";

        }

        switch (cell.getCellTypeEnum()) {

            case STRING:

                return cell.getStringCellValue();

            case NUMERIC:

                if (DateUtil.isCellDateFormatted(cell)) {

                    return cell.getDateCellValue().toString();

                } else {

                    return Double.toString(cell.getNumericCellValue());

                }

            case BOOLEAN:

                return Boolean.toString(cell.getBooleanCellValue());

            case FORMULA:

                return cell.getCellFormula();

            default:

                return "";

        }

    }

    //@Autowired

    private void addItem(ItemLoaderModel itemLoaderModel) throws ProfitMandiBusinessException {

        this.validateItemLoaderModel(itemLoaderModel);

        List<Item> items = itemRepository.selectAll(itemLoaderModel.getBrand(), itemLoaderModel.getModelName(), itemLoaderModel.getModelNumber());

        int catalogId = 0;

        if (items.size() > 0) {

            //Check if color already exist

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

            if(similarColorCount > 0) {

                throw new ProfitMandiBusinessException("Color alerady 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());

                }

            }

            catalogId = items.get(0).getCatalogItemId();

        } else {

            catalogId = itemRepository.getNextEntity();

        }

        //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(catalogId);

        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, itemLoaderModel);

    }

    private void addVendorItemPricing(Item item, ItemLoaderModel itemLoaderModel) {

        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);

        }

    }

    /*

    item = Item()

    item.productGroup = product_group

    item.brand = brand

    item.modelNumber = model_number

    item.modelName = model_name

    item.color = color

            try:

    item.mrp = round(mrp)

    except:

    pass

            #if item is risky

            if categoryMap.has_key(category_name.strip()):

    item.category = categoryMap[category_name.strip()]

    item.itemStatus = 8

            else:

    print "can't find {0} at row {1}".format(category_name, rownum)

                continue

    item.sellingPrice = round(sp)

    item.startDate = long(to_java_date(datetime.datetime(1899, 12, 30) + timedelta(days=int(start_date))))

    item.preferredVendor = preferred_vendor

    item.risky = int(risky)

    item.weight = weight

    item.updatedOn = updatedOn

    item.type = int(item_type)

    cclient = CatalogClient().get_client()

                    for c1 in (5,5,5,5,5):

            try:

            prodClient.addItem(item)

            if len(states) > 0:

            for c3 in (1,2,3,4,5):

            try:

            prodClient.updateItemStateVat(item_id, states)

    print "break 1"

            break

    except Exception as e:

    prodClient = CatalogClient("catalog_service_server_host_prod").get_client()

    print "break 2"

            break

    except:

    prodClient = CatalogClient("catalog_service_server_host_prod").get_client()

    print "break 3"

            break

    except Exception as e:

    cclient = CatalogClient().get_client()

    vip = VendorItemPricing()

    vip.itemId = item_id

    vip.dealerPrice = round(dp)

    vip.mop = round(mop)

    vip.nlc = round(nlc)

    vip.transferPrice = round(tp)

    vip.vendorId = preferred_vendor

            for c in (1,2,3,4,5):

            try:

            iclient.addVendorItemPricing(vip)

            break

    except Exception as e:

    iclient = InventoryClient().get_client()

    except:

            traceback.print_exc()

    message = message + "\t" + str(brand) + "\t" + str(model_name) + "\t" + str(model_number) + "\t" + "\n"

    print message

    mail("build@shop2020.in", "cafe@nes", ["amit.gupta@shop2020.in", "chandan.kumar@shop2020.in"], "Problem while adding items", message, [], [], [])

    print "Successfully updated the item list information."

    def main():

    parser = optparse.OptionParser()

            parser.add_option("-f", "--file", dest="filename",

    default="ItemList.xls", type="string",

    help="Read the item list from FILE",

    metavar="FILE")

            (options, args) = parser.parse_args()

            if len(args) != 0:

            parser.error("You've supplied extra arguments. Are you sure you want to run this program?")

    filename = options.filename

    load_item_data(filename)

if __name__ == '__main__':

    main()

*/

}