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()
*/
}