Subversion Repositories SmartDukaan

package com.spice.profitmandi.service.offers;

import com.google.common.collect.Lists;

import com.google.gson.Gson;

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

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

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

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

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

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

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

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

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

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

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

import com.spice.profitmandi.dao.entity.fofo.FofoLineItem;

import com.spice.profitmandi.dao.entity.fofo.FofoOrderItem;

import com.spice.profitmandi.dao.entity.fofo.InventoryItem;

import com.spice.profitmandi.dao.entity.fofo.OfferPayout;

import com.spice.profitmandi.dao.entity.transaction.LineItem;

import com.spice.profitmandi.dao.entity.transaction.Order;

import com.spice.profitmandi.dao.entity.transaction.UserWalletHistory;

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

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

import com.spice.profitmandi.dao.enumuration.catalog.AchievementType;

import com.spice.profitmandi.dao.enumuration.catalog.AmountType;

import com.spice.profitmandi.dao.enumuration.catalog.OfferSchemeType;

import com.spice.profitmandi.dao.enumuration.transaction.SchemePayoutStatus;

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

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

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

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

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

import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;

import com.spice.profitmandi.dao.repository.fofo.ActivatedImeiRepository;

import com.spice.profitmandi.dao.repository.fofo.InventoryItemRepository;

import com.spice.profitmandi.dao.repository.fofo.OfferPayoutRepository;

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

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

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

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

import com.spice.profitmandi.service.NotificationService;

import com.spice.profitmandi.service.pricecircular.PriceCircularService;

import com.spice.profitmandi.service.scheme.InventoryPayoutModel;

import com.spice.profitmandi.service.user.RetailerService;

import com.spice.profitmandi.service.wallet.WalletService;

import com.spice.profitmandi.service.whatsapp.WhatsappMessageType;

import in.shop2020.model.v1.order.WalletReferenceType;

import org.apache.commons.io.output.ByteArrayOutputStream;

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

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

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

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.cache.CacheManager;

import org.springframework.cache.annotation.Cacheable;

import org.springframework.stereotype.Component;

import java.io.IOException;

import java.io.InputStream;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.YearMonth;

import java.util.*;

import java.util.stream.Collectors;

@Component

public class OfferServiceImpl implements OfferService {

    private static final List<String> targets = Arrays.asList("Target 1", "Target 2", "Target 3", "Target 4",

            "Target 5");

    @Autowired

    private Gson gson;

    @Autowired

    NotificationService notificationService;

    @Autowired

    private OfferRepository offerRepository;

    @Autowired

    private RetailerService retailerService;

    @Autowired

    PriceCircularService priceCircularService;

    @Autowired

    OfferPayoutRepository offerPayoutRepository;

    @Autowired

    private ItemCriteriaRepository itemCriteriaRepository;

    @Autowired

    private ItemRepository itemRepository;

    @Autowired

    private TagListingRepository tagListingRepository;

    @Autowired

    private OfferTargetSlabRepository offerTargetSlabRepository;

    @Autowired

    private FofoStoreRepository fofoStoreRepository;

    @Autowired

    CacheManager oneDayCacheManager;

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

    @Autowired

    private ActivatedImeiRepository activatedImeiRepository;

    @Autowired

    private WarehouseScanRepository warehouseScanRepository;

    @Autowired

    private WarehouseInventoryItemRepository warehouseInventoryItemRepository;

    @Autowired

    private LineItemImeisRepository lineItemImeisRepository;

    @Autowired

    private InventoryItemRepository inventoryItemRepository;

    @Override

    public void addOfferService(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {

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

        this.createOffer(createOfferRequest);

        this.createTargetSlabs(createOfferRequest);

    }

    private void createOffer(CreateOfferRequest createOfferRequest) {

        Offer offer = new Offer();

        offer.setName(createOfferRequest.getName());

        offer.setTargetType(createOfferRequest.getTargetType());

        offer.setBooster(createOfferRequest.isBooster());

        offer.setPayoutType(createOfferRequest.getPayoutType());

        offer.setSchemeType(createOfferRequest.getSchemeType());

        offer.setBaseCriteia(createOfferRequest.isBaseCriteria());

        offer.setEndDate(createOfferRequest.getEndDate());

        offer.setStartDate(createOfferRequest.getStartDate());

        offer.setBrandSharePercentage(createOfferRequest.getBrandShareTerms());

        offer.setSellinPercentage(createOfferRequest.getSellinPercentage());

        offer.setOfferNotes(createOfferRequest.getOfferNotes());

        offer.setActivationBrands(createOfferRequest.getActivationBrands());

        offer.setTerms(createOfferRequest.getTerms());

        offer.setItemCriteria(gson.toJson(createOfferRequest.getItemCriteria()));

        offer.setPartnerCriteria(gson.toJson(createOfferRequest.getPartnerCriteria()));

        offer.setDiscount(createOfferRequest.isDiscount());

        offerRepository.persist(offer);

        createOfferRequest.setId(offer.getId());

    }

    @Override

    public List<CreateOfferRequest> getPublishedOffers(int fofoId, YearMonth yearMonth) throws ProfitMandiBusinessException {

        List<Offer> publishedOffers = offerRepository.selectAllPublishedMapByPartner(yearMonth).get(fofoId);

        List<CreateOfferRequest> createOfferRequests = new ArrayList<>();

        if (publishedOffers != null) {

            for (Offer offer : publishedOffers) {

                CreateOfferRequest createOfferRequest = this.getCreateOfferRequest(offer);

                createOfferRequests.add(createOfferRequest);

                setCriteriaPayoutAchieved(fofoId, createOfferRequest);

            }

        }

        return createOfferRequests;

    }

    private void setCriteriaPayoutAchieved(int fofoId, CreateOfferRequest createOfferRequest) {

        Map<Integer, Long> criteriaTransactionMap = null;

        if (createOfferRequest.getSchemeType().equals(OfferSchemeType.SELLIN)) {

            criteriaTransactionMap = offerRepository.getPartnerWisePurchaseSum(createOfferRequest).get(fofoId);

        } else {

            criteriaTransactionMap = offerRepository.getPartnerWiseSalesSum(createOfferRequest).get(fofoId);

        }

        LOGGER.info("I am here ------- {}", createOfferRequest.getId());

        List<com.spice.profitmandi.dao.model.ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest

                .getTargetSlabs().get(0).getItemCriteriaPayouts();

        for (com.spice.profitmandi.dao.model.ItemCriteriaPayout itemCriteriaPayout : itemCriteriaPayouts) {

            itemCriteriaPayout.setNextSlab(null);

            LOGGER.info("I am here --- {}", itemCriteriaPayout.getPayoutSlabs());

            long saleAmount = 0;

            if (criteriaTransactionMap != null

                    && criteriaTransactionMap.containsKey(itemCriteriaPayout.getItemCriteria().getId())) {

                saleAmount = criteriaTransactionMap.containsKey(0) ? criteriaTransactionMap.get(0) : criteriaTransactionMap.get(itemCriteriaPayout.getItemCriteria().getId());

            }

            itemCriteriaPayout.setAchievedValue(saleAmount);

            List<com.spice.profitmandi.service.offers.PayoutSlab> payoutSlabs = itemCriteriaPayout.getPayoutSlabs();

            for (int i = 0; i < payoutSlabs.size(); i++) {

                com.spice.profitmandi.service.offers.PayoutSlab beginSlab = payoutSlabs.get(i);

                com.spice.profitmandi.service.offers.PayoutSlab endSlab = null;

                if (payoutSlabs.size() - 1 > i) {

                    endSlab = payoutSlabs.get(i + 1);

                }

                if (beginSlab.getOnwardsAmount() > saleAmount) {

                    itemCriteriaPayout.setShortValue(beginSlab.getOnwardsAmount() - saleAmount);

                    itemCriteriaPayout.setNextSlab(beginSlab);

                    LOGGER.info("I am here");

                    break;

                }

                if (endSlab != null) {

                    if (beginSlab.getOnwardsAmount() <= saleAmount && endSlab.getOnwardsAmount() > saleAmount) {

                        itemCriteriaPayout.setCurrentSlab(beginSlab);

                        beginSlab.setSelected(true);

                        itemCriteriaPayout.setNextSlab(endSlab);

                        itemCriteriaPayout.setShortValue(endSlab.getOnwardsAmount() - saleAmount);

                        LOGGER.info("I am here");

                        break;

                    }

                    // Last loop

                } else {

                    beginSlab.setSelected(true);

                    itemCriteriaPayout.setCurrentSlab(beginSlab);

                    LOGGER.info("I am here");

                }

            }

            LOGGER.info("I am here --- {}", itemCriteriaPayout);

        }

    }

    @Override

    @Cacheable(value = "allOffers", cacheManager = "oneDayCacheManager")

    public Map<Integer, CreateOfferRequest> getAllOffers(YearMonth yearMonth) throws ProfitMandiBusinessException {

        List<Offer> allOffers = offerRepository.selectAll(yearMonth, false);

        List<CreateOfferRequest> createOfferRequests = new ArrayList<>();

        for (Offer offer : allOffers) {

            CreateOfferRequest createOfferRequest = this.getCreateOfferRequest(offer);

            createOfferRequests.add(createOfferRequest);

        }

        return createOfferRequests.stream().collect(Collectors.toMap(CreateOfferRequest::getId, x -> x));

    }

    @Override

    @Cacheable(value = "partnerOffers", cacheManager = "thirtyMinsTimeOutCacheManager")

    public CreateOfferRequest getOffer(int fofoId, int offerId) throws ProfitMandiBusinessException {

        Offer offer = offerRepository.selectById(offerId);

        CreateOfferRequest createOfferRequest = this.getCreateOfferRequest(offer);

        if (fofoId > 0) {

            this.setCriteriaPayoutAchieved(fofoId, createOfferRequest);

        }

        return createOfferRequest;

    }

    @Override

    public CreateOfferRequest getCreateOfferRequest(Offer fromOffer) throws ProfitMandiBusinessException {

        CreateOfferRequest createOfferRequest = new CreateOfferRequest();

        createOfferRequest.setName(fromOffer.getName());

        createOfferRequest.setDiscount(fromOffer.isDiscount());

        createOfferRequest.setTargetType(fromOffer.getTargetType());

        createOfferRequest.setPayoutType(fromOffer.getPayoutType());

        createOfferRequest.setSchemeType(fromOffer.getSchemeType());

        createOfferRequest.setActivationBrands(fromOffer.getActivationBrands());

        createOfferRequest.setEndDate(fromOffer.getEndDate());

        createOfferRequest.setProcessTimestamp(fromOffer.getProcessedTimestamp());

        createOfferRequest.setStartDate(fromOffer.getStartDate());

        createOfferRequest.setBooster(fromOffer.isBooster());

        createOfferRequest.setActive(fromOffer.isActive());

        createOfferRequest.setSellinPercentage(fromOffer.getSellinPercentage());

        createOfferRequest.setBrandShareTerms(fromOffer.getBrandSharePercentage());

        createOfferRequest.setOfferNotes(fromOffer.getOfferNotes());

        createOfferRequest.setTerms(fromOffer.getTerms());

        createOfferRequest.setBaseCriteria(fromOffer.isBaseCriteia());

        createOfferRequest.setItemCriteria(

                gson.fromJson(fromOffer.getItemCriteria(), com.spice.profitmandi.service.offers.ItemCriteria.class));

        createOfferRequest.setPartnerCriteria(gson.fromJson(fromOffer.getPartnerCriteria(), PartnerCriteria.class));

        createOfferRequest.setId(fromOffer.getId());

        createOfferRequest.setCreatedOn(fromOffer.getCreatedTimestamp());

        createOfferRequest.setItemCriteriaString(

                itemCriteriaRepository.getItemCriteriaDescription(createOfferRequest.getItemCriteria()));

        List<com.spice.profitmandi.dao.model.TargetSlab> targetSlabs = offerTargetSlabRepository

                .getByOfferId(fromOffer.getId());

        createOfferRequest.setPartnerCriteriaString(

                this.getPartnerCriteriaString(createOfferRequest.getPartnerCriteria()));

        createOfferRequest.setTargetSlabs(targetSlabs);

        for (com.spice.profitmandi.dao.model.ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs()

                .get(0).getItemCriteriaPayouts()) {

            itemCriteriaPayout.setNextSlab(itemCriteriaPayout.getPayoutSlabs().get(0));

        }

        return createOfferRequest;

    }

    @Autowired

    private WalletService walletService;

    @Override

    public void reverseAdditionalSelloutSchemes(List<InventoryItem> inventoryItems, String reversalReason) throws ProfitMandiBusinessException {

        Map<Integer, List<InventoryItem>> fofoInventoryItemsMap = inventoryItems.stream().collect(Collectors.groupingBy(x -> x.getFofoId()));

        for (Map.Entry<Integer, List<InventoryItem>> fofoInventoryItemsEntry : fofoInventoryItemsMap.entrySet()) {

            int fofoId = fofoInventoryItemsEntry.getKey();

            List<InventoryItem> partnerInventoryItems = fofoInventoryItemsEntry.getValue();

            List<OfferPayout> offerPayouts = new ArrayList<>();

            LOGGER.info("Reversing schemes for fofoId {}, InventoryItems {}", fofoId, inventoryItems);

            //As of now we are not handling non-serialised items

            inventoryItems = inventoryItems.stream().filter(x -> x.getSerialNumber() != null).collect(Collectors.toList());

            if (inventoryItems.size() > 0) {

                for (InventoryItem inventoryItem : inventoryItems) {

                    offerPayouts.addAll(offerPayoutRepository.selectAllBySerialNumber(inventoryItem.getFofoId(), inventoryItem.getSerialNumber())

                            .stream()

                            .filter(x -> x.getRejectTimestamp() == null)

                            .collect(Collectors.toList()));

                }

                for (OfferPayout offerPayout : offerPayouts) {

                    offerPayout.setStatus(SchemePayoutStatus.REJECTED);

                    offerPayout.setRejectTimestamp(LocalDateTime.now());

                }

                Map<Long, Double> offerPayoutAmountMap = offerPayouts.stream().collect(Collectors.groupingBy(x -> x.getOfferId(), Collectors.summingDouble(x -> x.getAmount())));

                for (Map.Entry<Long, Double> offerPayoutEntry : offerPayoutAmountMap.entrySet()) {

                    List<UserWalletHistory> histories = walletService.getAllByReference(fofoId, offerPayoutEntry.getKey().intValue(), WalletReferenceType.ADDITIONAL_SCHEME);

                    if (histories.size() > 0) {

                        walletService.rollbackAmountFromWallet(fofoId, offerPayoutEntry.getValue().floatValue(), offerPayoutEntry.getKey().intValue(),

                                WalletReferenceType.ADDITIONAL_SCHEME, reversalReason, LocalDateTime.now());

                    }

                }

            }

        }

    }

    private void createTargetSlabs(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {

        List<com.spice.profitmandi.dao.model.TargetSlab> targetSlabs = createOfferRequest.getTargetSlabs().stream()

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

        if (targetSlabs.size() >= 0) {

            Collections.sort(targetSlabs);

            for (com.spice.profitmandi.dao.model.TargetSlab targetSlab : targetSlabs) {

                if (targetSlab.validate()) {

                    List<ItemCriteriaPayout> itemCriteriaPayouts = targetSlab.getItemCriteriaPayouts();

                    for (ItemCriteriaPayout itemCriteriaPayout : itemCriteriaPayouts) {

                        ItemCriteria itemCriteria = itemCriteriaRepository.selectById(itemCriteriaPayout.getItemCriteria().getId());

                        if (itemCriteriaPayout.getItemCriteria().getId() > 0) {

                            itemCriteriaPayout.getItemCriteria().setId(0);

                        } else {

                            String itemCriteriaJson = gson.toJson(itemCriteriaPayout.getItemCriteria());

                            itemCriteria = itemCriteriaRepository.selectByCriteria(itemCriteriaJson);

                            // ItemCriteria Only once

                            if (itemCriteria == null) {

                                itemCriteria = new ItemCriteria();

                                itemCriteria.setCriteria(gson.toJson(itemCriteriaPayout.getItemCriteria()));

                                itemCriteriaRepository.persist(itemCriteria);

                            }

                        }

                        for (PayoutSlab payoutSlab : itemCriteriaPayout.getPayoutSlabs()) {

                            TargetSlabEntity targetSlabEntity = new TargetSlabEntity();

                            targetSlabEntity.setOfferId(createOfferRequest.getId());

                            targetSlabEntity.setOnwardsTarget(targetSlab.getOnwardsAmount());

                            targetSlabEntity.setTargetDescription(targetSlab.getTargetDescription());

                            targetSlabEntity.setPayoutTarget(payoutSlab.getOnwardsAmount());

                            targetSlabEntity.setPayoutValue(payoutSlab.getPayoutAmount());

                            targetSlabEntity.setItemCriteriaId(itemCriteria.getId());

                            targetSlabEntity.setAmountType(itemCriteriaPayout.getAmountType());

                            targetSlabEntity.setStartDate(itemCriteriaPayout.getStartDate());

                            targetSlabEntity.setEndDate(itemCriteriaPayout.getEndDate());

                            offerTargetSlabRepository.persist(targetSlabEntity);

                        }

                    }

                }

            }

        } else {

            throw new ProfitMandiBusinessException("Invalid Target Slabs", "Invalid Target", "");

        }

    }

    @Override

    public List<CreateOfferRequest> getPublishedOffers(LocalDate date, int fofoId, int catalogId) throws ProfitMandiBusinessException {

        List<Offer> publishedOffers = offerRepository.selectAllPublishedMapByPartner(YearMonth.from(date)).get(fofoId);

        List<CreateOfferRequest> createOfferRequests = new ArrayList<>();

        if (publishedOffers != null) {

            for (Offer offer : publishedOffers) {

                boolean found = false;

                List<com.spice.profitmandi.dao.model.TargetSlab> targetSlabs = offerTargetSlabRepository

                        .getByOfferId(offer.getId());

                CreateOfferRequest createOfferRequest = this.getCreateOfferRequest(offer);

                createOfferRequest.setTargetSlabs(targetSlabs);

                this.setCriteriaPayoutAchieved(fofoId, createOfferRequest);

                Set<com.spice.profitmandi.service.offers.ItemCriteria> itemCriteriaSet = targetSlabs.stream()

                        .flatMap(x -> x.getItemCriteriaPayouts().stream()).map(x -> x.getItemCriteria()).distinct()

                        .filter(x -> itemRepository.containsModel(x, catalogId)).collect(Collectors.toSet());

                for (com.spice.profitmandi.dao.model.TargetSlab targetSlab : targetSlabs) {

                    targetSlab.setItemCriteriaPayouts(targetSlab.getItemCriteriaPayouts().stream()

                            .filter(x -> itemCriteriaSet.contains(x.getItemCriteria())).collect(Collectors.toList()));

                    if (targetSlab.getItemCriteriaPayouts().size() > 0) {

                        found = true;

                    }

                }

                if (found) {

                    createOfferRequests.add(createOfferRequest);

                }

            }

        }

        return createOfferRequests;

    }

    @Override

    public void createOffers(InputStream fileInputStream) throws Exception {

        Map<CreateOfferRequest, List<PartnerTargetModel>> offerPartnerTargetMap = this.parseFromExcel(fileInputStream);

        Set<YearMonth> ymListToEvict = new HashSet<>();

        for (Map.Entry<CreateOfferRequest, List<PartnerTargetModel>> partnerTargetEntry : offerPartnerTargetMap

                .entrySet()) {

            CreateOfferRequest createOfferRequest = partnerTargetEntry.getKey();

            Map<List<Integer>, List<Integer>> targetPartnerMap = partnerTargetEntry.getValue().stream()

                    .collect(Collectors.groupingBy(PartnerTargetModel::getTargets,

                            Collectors.mapping(PartnerTargetModel::getFofoId, Collectors.toList())));

            for (Map.Entry<List<Integer>, List<Integer>> targetPartnersEntry : targetPartnerMap.entrySet()) {

                List<Integer> fofoIds = createOfferRequest.getPartnerCriteria().getFofoIds();

                List<Integer> filterFofoIds = targetPartnersEntry.getValue().stream().filter(x -> !fofoIds.contains(x))

                        .collect(Collectors.toList());

                LOGGER.info("FofoIds {}", filterFofoIds);

                List<Integer> targets = targetPartnersEntry.getKey();

                LOGGER.info("Targets  {}", targets);

                createOfferRequest.getPartnerCriteria().setFofoIds(filterFofoIds);

                int counter = 0;

                List<ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest.getTargetSlabs().get(0)

                        .getItemCriteriaPayouts();

                List<PayoutSlab> payoutSlabs = itemCriteriaPayouts.stream().flatMap(x -> x.getPayoutSlabs().stream()).collect(Collectors.toList());

                for (PayoutSlab payoutSlab : payoutSlabs) {

                    payoutSlab.setOnwardsAmount(targets.get(counter));

                    counter++;

                }

                this.addOfferService(createOfferRequest);

            }

            ymListToEvict.add(YearMonth.from(createOfferRequest.getStartDate()));

        }

        for (YearMonth ymToEvict : ymListToEvict) {

            oneDayCacheManager.getCache("allOffers").evict(ymToEvict);

        }

    }

    private Map<CreateOfferRequest, List<PartnerTargetModel>> parseFromExcel(InputStream inputStream)

            throws ProfitMandiBusinessException {

        Map<CreateOfferRequest, List<PartnerTargetModel>> offerPartnerTargetMap = new HashMap<>();

        XSSFWorkbook myWorkBook = null;

        try {

            myWorkBook = new XSSFWorkbook(inputStream);

            myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);

            for (int i = 0; i < myWorkBook.getNumberOfSheets(); i++) {

                XSSFSheet mySheet = myWorkBook.getSheetAt(i);

                int offerId = Integer.parseInt(mySheet.getSheetName());

                CreateOfferRequest existingOffer = this.getOffer(0, offerId);

                List<PartnerTargetModel> partnerTargetModels = new ArrayList<>();

                offerPartnerTargetMap.put(existingOffer, partnerTargetModels);

                this.printIterator(mySheet);

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

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

                    XSSFRow row = mySheet.getRow(rowNumber);

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

                    PartnerTargetModel partnerTargetModel = new PartnerTargetModel();

                    if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {

                        partnerTargetModel.setFofoId((int) row.getCell(0).getNumericCellValue());

                    } else {

                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(

                                "FOFO ID", row.getCell(0).toString(), "TGLSTNG_VE_1010");

                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);

                        throw profitMandiBusinessException;

                    }

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

                    long targetsSize = existingOffer.getTargetSlabs().get(0).getItemCriteriaPayouts().stream().flatMap(x -> x.getPayoutSlabs().stream()).collect(Collectors.counting());

                    for (int cellNumber = 1; cellNumber <= targetsSize; cellNumber++) {

                        if (row.getCell(cellNumber) != null

                                && row.getCell(cellNumber).getCellTypeEnum() == CellType.NUMERIC) {

                            targets.add((int) row.getCell(cellNumber).getNumericCellValue());

                        } else {

                            ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(

                                    "Target Column issue at " + rowNumber + ", " + cellNumber, row.getCell(cellNumber),

                                    "Invalid target");

                            LOGGER.error("Excel file parse error : ", profitMandiBusinessException);

                            throw profitMandiBusinessException;

                        }

                    }

                    partnerTargetModel.setTargets(targets);

                    partnerTargetModels.add(partnerTargetModel);

                }

            }

            myWorkBook.close();

        } catch (ProfitMandiBusinessException pbse) {

            throw pbse;

        } catch (IOException ioException) {

            ioException.printStackTrace();

            throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),

                    "EXL_VE_1000");

        } finally {

            if (myWorkBook != null) {

                try {

                    myWorkBook.close();

                } catch (IOException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            }

        }

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

        return offerPartnerTargetMap;

    }

    private void printIterator(XSSFSheet sheet) {

        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();

            // For each row, iterate through all the columns

            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                // Check the cell type and format accordingly

                switch (cell.getCellType()) {

                    case Cell.CELL_TYPE_NUMERIC:

                        System.out.print(cell.getNumericCellValue() + "t");

                        break;

                    case Cell.CELL_TYPE_STRING:

                        System.out.print(cell.getStringCellValue() + "t");

                        break;

                }

            }

            System.out.println("");

        }

    }

    @Override

    public Map<Integer, CreateOfferRequest> getPublishedOffers(LocalDate date, int fofoId, String brand) {

        // TODO Auto-generated method stub

        return null;

    }

    @Override

    @Cacheable(value = "slabPayoutMap1", cacheManager = "oneDayCacheManager")

    public Map<Integer, Map<Integer, Long>> getSlabPayoutMap(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {

        Map<Integer, Map<Integer, Long>> catalogSlabPayoutMap = new HashMap<>();

        com.spice.profitmandi.dao.model.TargetSlab targetSlab = createOfferRequest.getTargetSlabs().get(0);

        List<ItemCriteriaPayout> payouts = targetSlab.getItemCriteriaPayouts();

        for (ItemCriteriaPayout itemCriteriaPayout : payouts) {

            com.spice.profitmandi.service.offers.ItemCriteria itemCriteria = itemCriteriaPayout.getItemCriteria();

            List<Integer> catalogIds = itemRepository.getCatalogIds(itemCriteria);

            for (PayoutSlab payoutSlab : Lists.reverse(itemCriteriaPayout.getPayoutSlabs())) {

                if (itemCriteriaPayout.getAmountType().equals(AmountType.FIXED)) {

                    for (Integer catalogId : catalogIds) {

                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {

                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());

                        }

                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),

                                (long) payoutSlab.getPayoutAmount());

                    }

                } else if (itemCriteriaPayout.getAmountType().equals(AmountType.SLAB_FIXED)) {

                    for (Integer catalogId : catalogIds) {

                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {

                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());

                        }

                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),

                                (long) (payoutSlab.getPayoutAmount() / payoutSlab.getOnwardsAmount()));

                    }

                } else if (itemCriteriaPayout.getAmountType().equals(AmountType.PERCENTAGE)) {

                    Map<Integer, TagListing> tagListingsMap = tagListingRepository.selectAllByCatalogIds(catalogIds);

                    for (Map.Entry<Integer, TagListing> tagListingEntry : tagListingsMap.entrySet()) {

                        TagListing tagListing = tagListingEntry.getValue();

                        if (!catalogSlabPayoutMap.containsKey(tagListingEntry.getKey())) {

                            catalogSlabPayoutMap.put(tagListingEntry.getKey(), new LinkedHashMap<>());

                        }

                        catalogSlabPayoutMap.get(tagListingEntry.getKey()).put(payoutSlab.getOnwardsAmount(),

                                (long) (tagListing.getSellingPrice() * payoutSlab.getPayoutAmount() / 100));

                    }

                }

            }

        }

        return catalogSlabPayoutMap;

    }

    @Override

    @Cacheable(value = "slabPayoutMap", cacheManager = "oneDayCacheManager")

    public Map<Integer, Map<Integer, AmountModel>> getSlabPayoutMapNew(CreateOfferRequest createOfferRequest) {

        Map<Integer, Map<Integer, AmountModel>> catalogSlabPayoutMap = new HashMap<>();

        com.spice.profitmandi.dao.model.TargetSlab targetSlab = createOfferRequest.getTargetSlabs().get(0);

        List<ItemCriteriaPayout> payouts = targetSlab.getItemCriteriaPayouts();

        for (ItemCriteriaPayout itemCriteriaPayout : payouts) {

            com.spice.profitmandi.service.offers.ItemCriteria itemCriteria = itemCriteriaPayout.getItemCriteria();

            List<Integer> catalogIds = itemRepository.getCatalogIds(itemCriteria);

            for (PayoutSlab payoutSlab : Lists.reverse(itemCriteriaPayout.getPayoutSlabs())) {

                if (itemCriteriaPayout.getAmountType().equals(AmountType.FIXED)) {

                    for (Integer catalogId : catalogIds) {

                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {

                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());

                        }

                        AmountModel amountModel = new AmountModel();

                        amountModel.setAmountType(AmountType.FIXED);

                        amountModel.setAmount(payoutSlab.getPayoutAmount());

                        amountModel.setDiscount(createOfferRequest.isDiscount());

                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),

                                amountModel);

                    }

                } else if (itemCriteriaPayout.getAmountType().equals(AmountType.SLAB_FIXED)) {

                    for (Integer catalogId : catalogIds) {

                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {

                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());

                        }

                        AmountModel amountModel = new AmountModel();

                        amountModel.setAmountType(AmountType.FIXED);

                        amountModel.setDiscount(createOfferRequest.isDiscount());

                        amountModel.setAmount(payoutSlab.getPayoutAmount() / payoutSlab.getOnwardsAmount());

                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),

                                amountModel);

                    }

                } else if (itemCriteriaPayout.getAmountType().equals(AmountType.PERCENTAGE)) {

                    for (Integer catalogId : catalogIds) {

                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {

                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());

                        }

                        AmountModel amountModel = new AmountModel();

                        amountModel.setAmountType(AmountType.PERCENTAGE);

                        amountModel.setAmount(payoutSlab.getPayoutAmount());

                        amountModel.setDiscount(createOfferRequest.isDiscount());

                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),

                                amountModel);

                    }

                }

            }

        }

        return catalogSlabPayoutMap;

    }

    private double getAmount(InventoryPayoutModel inventoryPayoutModel, AmountModel amountModel) {

        double rollout = 0;

        if (amountModel.getAmountType().equals(AmountType.PERCENTAGE)) {

            double effectiveDp = inventoryPayoutModel.getDp() - inventoryPayoutModel.getFixedAmount();

            double totalPercentage = inventoryPayoutModel.getPercentageAmount() + amountModel.getAmount();

            rollout = effectiveDp * (totalPercentage / (100 + totalPercentage) - (inventoryPayoutModel.getPercentageAmount() / (100 + inventoryPayoutModel.getPercentageAmount())));

        } else {

            rollout = amountModel.getAmount() * (100 / (100 + inventoryPayoutModel.getPercentageAmount()));

        }

        return rollout;

    }

    @Autowired

    OfferEligibleImeiRepository offerEligibleImeiRepository;

    @Override

    public void processActivationtOffer(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {

        List<OfferPayout> offerPayouts = offerPayoutRepository.selectAllByOfferId(createOfferRequest.getId());

        List<ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts();

        Map<Integer, ItemCriteriaPayout> itemCriteriaPayoutMap = itemCriteriaPayouts.stream().collect(Collectors.toMap(x -> x.getItemCriteria().getId(), x -> x));

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

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

        Map<Integer, Map<Integer, Double>> fixedSlabCriteriaPartnerMap = offerPayouts.stream().filter(x -> itemCriteriaPayoutMap.get((int) x.getCriteriaId()).getAmountType().equals(AmountType.SLAB_FIXED))

                .collect(Collectors.groupingBy(x -> (int) x.getCriteriaId(), Collectors.groupingBy(x -> (int) x.getFofoId(), Collectors.summingDouble(x -> x.getSlabAmount()))));

        Map<String, Double> serialNumberPaid = offerPayouts.stream().filter(x -> x.getRejectTimestamp() == null).collect(Collectors.groupingBy(x -> x.getSerialNumber(), Collectors.summingDouble(x -> x.getSlabAmount())));

        Map<Integer, Map<Integer, List<FofoOrderItem>>> criteriaPartnerwiseTertiary = offerRepository.getCriteriaWisePartnerTertiary(createOfferRequest);

        Map<String, AgeingSummaryModel> ageingSummaryModelsMap = null;

        final Map<String, LocalDateTime> activatedImeisActivationDateMap;

        //Find Only eligible imeis if any

        List<String> imeisAllowedManually = offerEligibleImeiRepository.selectEligibleImeisByOfferId(createOfferRequest.getId());

        if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {

            //All Serialnumbers for all eligible retailers

            List<String> serialNumbers = criteriaPartnerwiseTertiary.entrySet().stream().flatMap(x -> x.getValue().entrySet().stream().flatMap(foiList -> foiList.getValue().stream()))

                    .flatMap(y -> y.getFofoLineItems().stream()).filter(fli -> fli.getSerialNumber() != null)

                    .map(x -> x.getSerialNumber()).collect(Collectors.toList());

            if (serialNumbers.size() > 0) {

                if (imeisAllowedManually != null) {

                    serialNumbers = serialNumbers.stream().filter(x -> imeisAllowedManually.contains(x)).collect(Collectors.toList());

                }

                activatedImeisActivationDateMap = activatedImeiRepository.selectBySerialNumbers(serialNumbers).stream()

                        .filter(x -> createOfferRequest.isWithinRange(x.getActivationTimestamp()))

                        .collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x.getActivationTimestamp()));

                ageingSummaryModelsMap = warehouseInventoryItemRepository.findStockAgeingByFofoIdSerialNumbers(0, serialNumbers).stream().collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x, (u, v) -> v));

            } else {

                activatedImeisActivationDateMap = null;

            }

        } else {

            activatedImeisActivationDateMap = null;

        }

        Map<Integer, Integer> userBaseQtyMap = new HashMap<>();

        Map<Integer, Integer> userBaseValueMap = new HashMap<>();

        Map<Integer, Integer> eligibleBaseQtyMap = new HashMap<>();

        Map<Integer, Integer> eligibleBaseValueMap = new HashMap<>();

        if (createOfferRequest.isBaseCriteria()) {

            Map<Integer, List<FofoOrderItem>> foiMap = criteriaPartnerwiseTertiary.get(0);

            for (Map.Entry<Integer, List<FofoOrderItem>> orderEntry : foiMap.entrySet()) {

                int fofoId = orderEntry.getKey();

                List<FofoOrderItem> fofoOrderItems = orderEntry.getValue();

                int totalBaseQty = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (x.getQuantity())));

                int totalBaseValue = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (Math.round(x.getDp())) * x.getQuantity()));

                userBaseQtyMap.put(fofoId, totalBaseQty);

                userBaseValueMap.put(fofoId, totalBaseValue);

                if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {

                    Map<Integer, Integer> inventoryItemValueMap = new HashMap<>();

                    for (FofoOrderItem fofoOrderItem : fofoOrderItems) {

                        fofoOrderItem.getFofoLineItems().stream().filter(x -> x.getSerialNumber() != null).forEach(

                                lineItem -> {

                                    if (activatedImeisActivationDateMap == null || activatedImeisActivationDateMap.containsKey(lineItem.getSerialNumber())) {

                                        inventoryItemValueMap.put(lineItem.getInventoryItemId(), (int) fofoOrderItem.getDp());

                                    }

                                }

                        );

                    }

                    eligibleBaseQtyMap.put(fofoId, inventoryItemValueMap.size());

                    eligibleBaseValueMap.put(fofoId, inventoryItemValueMap.entrySet().stream().collect(Collectors.summingInt(x -> x.getValue())));

                } else {

                    eligibleBaseQtyMap.put(fofoId, totalBaseQty);

                    eligibleBaseValueMap.put(fofoId, totalBaseValue);

                }

            }

        }

        //As of now eligible sale is considered to be without special criteria like brand/

        //CriteriaPayoutWise

        for (ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()) {

            int criteriaId = itemCriteriaPayout.getItemCriteria().getId();

            Map<Integer, List<FofoOrderItem>> ordersMap = criteriaPartnerwiseTertiary.get(criteriaId);

            //Partywise iteration

            for (Map.Entry<Integer, List<FofoOrderItem>> orderEntry : ordersMap.entrySet()) {

                double totalRolloutAmount = 0;

                int fofoId = orderEntry.getKey();

                List<FofoOrderItem> fofoOrderItems = orderEntry.getValue();

                OfferRowModel offerRowModel = new OfferRowModel();

                int totalQty = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (x.getQuantity())));

                int totalValue = fofoOrderItems.stream().collect(Collectors.summingInt(x -> Math.round(x.getDp()) * (x.getQuantity())));

                int eligibleQty = totalQty;

                int eligibleValue = totalValue;

                Map<FofoLineItem, Integer> lineItemValueMap = new HashMap<>();

                if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {

                    for (FofoOrderItem fofoOrderItem : fofoOrderItems) {

                        fofoOrderItem.getFofoLineItems().stream().filter(x -> x.getSerialNumber() != null).forEach(

                                lineItem -> {

                                    if (activatedImeisActivationDateMap == null) {

                                        lineItemValueMap.put(lineItem, (int) fofoOrderItem.getDp());

                                    } else if (activatedImeisActivationDateMap.containsKey(lineItem.getSerialNumber())) {

                                        if (itemCriteriaPayout.isWithinRange(activatedImeisActivationDateMap.get(lineItem.getSerialNumber()))) {

                                            lineItemValueMap.put(lineItem, (int) fofoOrderItem.getDp());

                                        }

                                    }

                                }

                        );

                    }

                    eligibleQty = lineItemValueMap.size();

                    eligibleValue = lineItemValueMap.entrySet().stream().collect(Collectors.summingInt(x -> x.getValue()));

                    offerRowModel.setEligibleImeis(lineItemValueMap.keySet().stream().map(x -> x.getSerialNumber()).collect(Collectors.toList()));

                }

                int totalBaseQty = totalQty;

                int totalBaseValue = totalValue;

                int eligibleBaseQty = eligibleQty;

                int eligibleBaseValue = eligibleValue;

                if (createOfferRequest.isBaseCriteria()) {

                    totalBaseQty = userBaseQtyMap.get(fofoId) == null ? 0 : userBaseQtyMap.get(fofoId);

                    totalBaseValue = userBaseValueMap.get(fofoId) == null ? 0 : userBaseValueMap.get(fofoId);

                    eligibleBaseQty = eligibleBaseQtyMap.get(fofoId) == null ? 0 : eligibleBaseQtyMap.get(fofoId);

                    eligibleBaseValue = eligibleBaseValueMap.get(fofoId) == null ? 0 : eligibleBaseValueMap.get(fofoId);

                }

                int totalSale = totalBaseValue;

                int eligibleSale = eligibleBaseValue;

                int eligibleCriteriaSale = eligibleValue;

                int eligibleCriteriaSaleDp = eligibleValue;

                if (createOfferRequest.getTargetType().equals(AchievementType.QUANTITY)) {

                    totalSale = totalBaseQty;

                    eligibleSale = eligibleBaseQty;

                    eligibleCriteriaSale = eligibleQty;

                }

                offerRowModel.setTotalSale(totalSale);

                offerRowModel.setEligibleSale(eligibleSale);

                float eligiblePayoutValue = 0;

                float previousPayoutValue = 0;

                double finalPayout;

                for (PayoutSlab payoutSlab : itemCriteriaPayout.getPayoutSlabs()) {

                    if (payoutSlab.getOnwardsAmount() <= eligibleSale) {

                        LOGGER.info("Eligible Sale {}", eligibleSale);

                        offerRowModel.setPayoutTargetAchieved(payoutSlab.getOnwardsAmount());

                        eligiblePayoutValue = payoutSlab.getPayoutAmount();

                        previousPayoutValue = Math.min(eligiblePayoutValue, previousPayoutValue);

                        offerRowModel.setPayoutValue(eligiblePayoutValue);

                    } else {

                        break;

                    }

                }

                if (AmountType.PERCENTAGE.equals(itemCriteriaPayout.getAmountType())) {

                    finalPayout = (eligiblePayoutValue * eligibleCriteriaSaleDp) / 100;

                } else if (AmountType.FIXED.equals(itemCriteriaPayout.getAmountType())) {

                    finalPayout = eligiblePayoutValue * eligibleCriteriaSale;

                } else {

                    Map<Integer, Double> fofoCriteriaMap = fixedSlabCriteriaPartnerMap.get(criteriaId);

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

                    finalPayout = eligiblePayoutValue;

                    if (fofoCriteriaMap != null) {

                        if (fofoCriteriaMap.containsKey(fofoId)) {

                            //double totalPayout = fofoCriteriaMap.get(fofoId);

                            finalPayout = eligiblePayoutValue - previousPayoutValue;

                            if (Math.abs(finalPayout) < Utils.DOUBLE_EPSILON) {

                                finalPayout = 0;

                            }

                        }

                    }

                }

                LOGGER.info("Final Payout - {}", finalPayout);

                if (finalPayout > 0) {

                    List<Integer> inventoryItemIds = lineItemValueMap.keySet().stream().map(x -> x.getInventoryItemId()).collect(Collectors.toList());

                    int totalDpValue = lineItemValueMap.values().stream().collect(Collectors.summingInt(x -> x));

                    LOGGER.info("Total DP Value {}", totalDpValue);

                    Map<Integer, InventoryItem> inventoryItemsMap = inventoryItemRepository.selectAllByIds(inventoryItemIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

                    for (Map.Entry<FofoLineItem, Integer> lineItemValueEntry : lineItemValueMap.entrySet()) {

                        FofoLineItem fofoLineItem = lineItemValueEntry.getKey();

                        double amount = 0;

                        if (AmountType.SLAB_FIXED.equals(itemCriteriaPayout.getAmountType())) {

                            amount = (eligiblePayoutValue * lineItemValueEntry.getValue()) / totalDpValue;

                        } else {

                            if (ageingSummaryModelsMap.containsKey(fofoLineItem.getSerialNumber())) {

                                if (ageingSummaryModelsMap.get(fofoLineItem.getSerialNumber()).isAgedAbove(300)) {

                                    continue;

                                }

                            }

                            amount = eligiblePayoutValue;

                        }

                        if (serialNumberPaid.containsKey(fofoLineItem.getSerialNumber())) {

                            amount = amount - serialNumberPaid.get(fofoLineItem.getSerialNumber());

                        }

                        LOGGER.info("Amount - {}", amount);

                        //ignore reasonably small

                        if (amount < Utils.DOUBLE_EPSILON) continue;

                        AmountModel amountModel = new AmountModel();

                        amountModel.setAmountType(itemCriteriaPayout.getAmountType());

                        amountModel.setAmount(amount);

                        amountModel.setDiscount(createOfferRequest.isDiscount());

                        InventoryPayoutModel inventoryPayoutModel = priceCircularService.getPayouts(inventoryItemsMap.get(fofoLineItem.getInventoryItemId()));

                        double rolloutAmount = inventoryPayoutModel.getRolloutAmount(amountModel);

                        totalRolloutAmount += rolloutAmount;

                        OfferPayout offerPayout = new OfferPayout(fofoId, createOfferRequest.getId(), criteriaId, amount, fofoLineItem.getSerialNumber(), rolloutAmount, SchemePayoutStatus.CREDITED, createOfferRequest.getDescription(), LocalDateTime.now());

                        offerPayout.setInventoryItemId(fofoLineItem.getInventoryItemId());

                        offerPayoutRepository.persist(offerPayout);

                    }

                    walletService.addAmountToWallet((int) fofoId, createOfferRequest.getId(), WalletReferenceType.ADDITIONAL_SCHEME, createOfferRequest.getDescription(), (float) totalRolloutAmount, LocalDateTime.now());

                }

            }

        }

        Offer offer = offerRepository.selectById(createOfferRequest.getId());

        offer.setProcessedTimestamp(LocalDateTime.now());

    }

    @Override

    public void processSellin(CreateOfferRequest createOfferRequest) throws Exception {

        //List<OfferRowModel> offerRowModels = new ArrayList<>();

        List<OfferPayout> offerPayouts = offerPayoutRepository.selectAllByOfferId(createOfferRequest.getId());

        Map<String, Double> serialNumberPaid = offerPayouts.stream().filter(x -> x.getRejectTimestamp() == null).collect(Collectors.groupingBy(x -> x.getSerialNumber(), Collectors.summingDouble(x -> x.getSlabAmount())));

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

        List<ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts();

        Map<Integer, ItemCriteriaPayout> itemCriteriaPayoutMap = itemCriteriaPayouts.stream().collect(Collectors.toMap(x -> x.getItemCriteria().getId(), x -> x));

        Map<Integer, Map<Integer, Double>> fixedSlabCriteriaPartnerMap = offerPayouts.stream().filter(x -> itemCriteriaPayoutMap.get((int) x.getCriteriaId()).getAmountType().equals(AmountType.SLAB_FIXED))

                .collect(Collectors.groupingBy(x -> (int) x.getCriteriaId(), Collectors.groupingBy(x -> (int) x.getFofoId(), Collectors.summingDouble(x -> x.getSlabAmount()))));

        Set<Long> fofoIds = offerPayouts.stream().map(x -> x.getFofoId()).collect(Collectors.toSet());

        Map<Integer, Map<Integer, List<Order>>> criteriaOrdersMap = offerRepository.getPartnerWiseSellin(createOfferRequest, true);

        Map<Integer, List<Order>> userBaseCriteriaOrdersMap = null;

        if (criteriaOrdersMap.get(0) != null && criteriaOrdersMap.get(0).size() > 0) {

            userBaseCriteriaOrdersMap = criteriaOrdersMap.get(0).entrySet().stream()

                    .filter(x -> !fofoIds.contains(x.getKey())).collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));

        }

        Map<Integer, Integer> userBaseQtyMap = null;

        Map<Integer, Integer> userBaseValueMap = null;

        if (userBaseCriteriaOrdersMap != null) {

            userBaseQtyMap = new HashMap<>();

            userBaseValueMap = new HashMap<>();

            for (Map.Entry<Integer, List<Order>> orderEntry : userBaseCriteriaOrdersMap.entrySet()) {

                int fofoId = orderEntry.getKey();

                List<Order> orders = orderEntry.getValue();

                int totalBaseQty = orders.stream().collect(Collectors.summingInt(x -> (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty())));

                int totalBaseValue = orders.stream().collect(Collectors.summingInt(x ->

                        Math.round(x.getLineItem().getUnitPrice() * (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty()))

                ));

                userBaseQtyMap.put(fofoId, totalBaseQty);

                userBaseValueMap.put(fofoId, totalBaseValue);

            }

        }

        LOGGER.info("Base Qty Map - {}", userBaseQtyMap);

        LOGGER.info("Base Value Map - {}", userBaseValueMap);

        LOGGER.info("Processing sellin");

        List<OfferPayout> offerPayoutsNew = new ArrayList<>();

        for (ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()) {

            int criteriaId = itemCriteriaPayout.getItemCriteria().getId();

            Map<Integer, List<Order>> ordersMap = criteriaOrdersMap.get(criteriaId).entrySet().stream()

                    .filter(x -> !fofoIds.contains(x.getKey())).collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));

            for (Map.Entry<Integer, List<Order>> orderEntry : ordersMap.entrySet()) {

                int fofoId = orderEntry.getKey();

                //CustomRetailer retailer = retailerService.getAllFofoRetailers().get(fofoId);

                List<Order> orders = orderEntry.getValue();

                //Set<String> returnedSerialNumbers = orders.stream().filter(x -> x.getSerialNumber() != null).flatMap(x -> x.getReturnedImeis().stream()).collect(Collectors.toSet());

                int totalQty = orders.stream().collect(Collectors.summingInt(x -> (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty())));

                int totalValue = orders.stream().collect(Collectors.summingInt(x -> Math.round(x.getLineItem().

                        getUnitPrice() * (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty()))));

                int totalBaseQty = totalQty;

                int totalBaseValue = totalValue;

                if (userBaseCriteriaOrdersMap != null) {

                    if (userBaseQtyMap.containsKey(fofoId)) {

                        totalBaseQty = userBaseQtyMap.get(fofoId);

                        totalBaseValue = userBaseValueMap.get(fofoId);

                    } else {

                        continue;

                    }

                }

                int purchasedValue;

                if (createOfferRequest.getTargetType().equals(AchievementType.VALUE)) {

                    purchasedValue = totalBaseValue;

                } else {

                    purchasedValue = totalBaseQty;

                }

                PayoutSlab eligibleSlab = null;

                for (PayoutSlab payoutSlab : itemCriteriaPayout.getPayoutSlabs()) {

                    if (payoutSlab.getOnwardsAmount() <= purchasedValue) {

                        eligibleSlab = payoutSlab;

                    } else {

                        break;

                    }

                }

                if (eligibleSlab == null) continue;

                List<Integer> orderIds = orders.stream().map(x -> x.getId()).collect(Collectors.toList());

                Map<Integer, LineItem> lineItemMap = orders.stream().collect(Collectors.toMap(x -> x.getLineItem().getId(), x -> x.getLineItem()));

                List<WarehouseScan> warehouseScans = warehouseScanRepository.selectAllByOrderIds(orderIds);

                LOGGER.info("Warehosue scans --> {}", warehouseScans);

                List<Integer> warehouseInventoryItemIds = warehouseScans.stream().collect(Collectors.groupingBy(x -> x.getInventoryItemId())).entrySet()

                        .stream().filter(x -> x.getValue().size() == 1).map(x -> x.getKey()).collect(Collectors.toList());

                List<OfferPayout> partnerPayouts = offerPayouts.stream().filter(x -> x.getFofoId() == fofoId).collect(Collectors.toList());

                Set<String> serialNumbersRejected = partnerPayouts.stream().filter(x -> x.getStatus().equals(SchemePayoutStatus.REJECTED)).map(x -> x.getSerialNumber()).collect(Collectors.toSet());

                Set<String> billedNotCancelledSerialNumbers;

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