Subversion Repositories SmartDukaan

package com.smartdukaan.cron.scheduled;

import java.io.Serializable;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.HashSet;

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.stream.Collectors;

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

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

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

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

import org.springframework.core.io.ByteArrayResource;

import org.springframework.mail.javamail.JavaMailSender;

import org.springframework.stereotype.Component;

import org.springframework.transaction.annotation.Transactional;

import com.google.common.collect.Sets;

import com.smartdukaan.cron.Application;

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

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

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

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

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

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

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

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

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

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

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

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

import com.spice.profitmandi.dao.enumuration.fofo.ScanType;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

@Component

@Transactional(rollbackFor = Throwable.class)

public class Reconciliation {

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

    @Autowired

    private FofoStoreRepository fofoStoreRepository;

    private static final List<WalletReferenceType> reconciliationReferenceTypes = Arrays

            .asList(WalletReferenceType.PURCHASE, WalletReferenceType.SCHEME_IN, WalletReferenceType.SCHEME_OUT);

    @Autowired

    private PurchaseRepository purchaseRepository;

    @Autowired

    private OrderService orderService;

    @Autowired

    private FofoOrderItemRepository fofoOrderItemRepository;

    @Autowired

    private FofoLineItemRepository fofoLineItemRepository;

    @Autowired

    private UserWalletRepository userWalletRepository;

    @Autowired

    private InventoryItemRepository inventoryItemRepository;

    @Autowired

    private ScanRecordRepository scanRecordRepository;

    @Autowired

    private UserWalletHistoryRepository userWalletHistoryRepository;

    @Autowired

    private SchemeInOutRepository schemeInOutRepository;

    @Autowired

    private SchemeRepository schemeRepository;

    @Autowired

    private RetailerService retailerService;

    @Autowired

    private ReturnOrderRepository returnOrderRepository;

    @Autowired

    private OrderRepository orderRepository;

    @Autowired

    private FofoOrderRepository fofoOrderRepository;

    @Autowired

    private JavaMailSender mailSender;

    public void dailyReconciliation() throws Exception {

        LocalDate date = LocalDate.now().minusDays(1);

        dailyReconciliation(date);

    }

    public void dailyReconciliation(LocalDate localDate) throws Exception {

        Map<SchemeType, Set<Integer>> schemeTypeMap = schemeRepository.selectAll().stream()

                .collect(Collectors.groupingBy(Scheme::getType, Collectors.mapping(Scheme::getId, Collectors.toSet())));

        boolean reconciled = true;

        Map<Integer, String> stores = fofoStoreRepository.getStoresMap();

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

        LocalDate yesterday = LocalDate.now().minusDays(1);

        Map<Integer, String> retailerNameMap = retailerService

                .getAllFofoRetailerIdNameMap(new ArrayList<>(stores.keySet()));

        for (int partnerId : stores.keySet()) {

            UserWallet uw = userWalletRepository.selectByRetailerId(partnerId);

            List<UserWalletHistory> walletHistory = userWalletHistoryRepository.selectByWalletIdAndDate(uw.getId(),

                    yesterday);

            Map<WalletReferenceType, List<UserWalletHistory>> referenceWiseWalletHistory = walletHistory.stream()

                    .collect(Collectors.groupingBy(x -> x.getReferenceType(), Collectors.toList()));

            reconciliationReferenceTypes.forEach(x -> {

                if (!referenceWiseWalletHistory.containsKey(x)) {

                    referenceWiseWalletHistory.put(x, new ArrayList<>());

                }

            });

            List<Serializable> reconciliation = new ArrayList<>();

            LocalDate dateToReconcile = yesterday;

            // "PartnerId", "Partner Name", "Reconciliation Date"

            reconciliation.addAll(Arrays.asList(partnerId, retailerNameMap.get(partnerId), dateToReconcile));

            for (WalletReferenceType walletReferenceType : reconciliationReferenceTypes) {

                List<UserWalletHistory> history = referenceWiseWalletHistory.get(walletReferenceType);

                Map<Integer, Integer> referenceWalletMap = history.stream().collect(

                        Collectors.groupingBy(x -> x.getReference(), Collectors.summingInt(x -> x.getAmount())));

                switch (walletReferenceType) {

                    case PURCHASE:

                        reconciliation.addAll(reconcileOrdersAndWallet(dateToReconcile, referenceWalletMap, history));

                        break;

                    case SCHEME_IN:

                        reconciliation.addAll(reconcileSchemeInAndWallet(dateToReconcile, referenceWalletMap, history,

                                schemeTypeMap.get(SchemeType.IN)));

                        break;

                    case SCHEME_OUT:

                        reconciliation.addAll(reconcileSchemeOutAndWallet(dateToReconcile, referenceWalletMap, history,

                                schemeTypeMap.get(SchemeType.OUT)));

                        break;

                    default:

                        break;

                }

            }

            reconciled = reconciled || Boolean.TRUE.equals(reconciliation.get(0));

            rows.add(reconciliation);

        }

        ByteArrayOutputStream baos = FileUtil.getCSVByteStream(Arrays.asList("PartnerId", "Partner Name",

                "Reconciliation Date", "Purchase Reconciled", "Wallet amount consumed", "Ordered Total",

                "Cancelled Total", "Refunded Total", "Scheme In Reconciled", "Scheme In to Wallet",

                "Scheme In disbursed", "Scheme In rolledback", "SchemeOut Reconciled", "Scheme Out to Wallet",

                "Scheme Out Disbursed", "Scheme Out Rolledback"), rows);

        Utils.sendMailWithAttachment(mailSender,

                new String[]{"amit.gupta@shop2020.in", "neeraj.gupta@smartdukaan.com"}, new String[]{},

                reconciled ? "Reconciled Successfully" : "Reconciliation failed", "Report attached",

                String.format("reconciliation-%s.csv", FormattingUtils.formatDate(yesterday.atStartOfDay())),

                new ByteArrayResource(baos.toByteArray()));

    }

    private List<? extends Serializable> reconcileOrdersAndWallet(LocalDate localDate,

                                                                  Map<Integer, Integer> transactionsOnThatDate, List<UserWalletHistory> history) throws Exception {

        int totalWalletConsumed = 0;

        float cancelledAmount = 0;

        float returnedAmount = 0;

        float totalDeductedAmount = 0;

        for (int transactionId : transactionsOnThatDate.keySet()) {

            List<Order> orders = orderRepository.selectAllByTransactionId(transactionId);

            for (Order o : orders) {

                if (o.getCreateTimestamp().toLocalDate().equals(localDate)) {

                    if (Arrays.asList(OrderStatus.PAYMENT_PENDING, OrderStatus.PAYMENT_FAILED)

                            .contains(o.getStatus())) {

                        cancelledAmount += o.getWalletAmount();

                    } else if (o.getRefundTimestamp() != null

                            && o.getRefundTimestamp().toLocalDate().equals(localDate)) {

                        ReturnOrder returnedOrder = returnOrderRepository.selectByOrderId(o.getId());

                        if (returnedOrder == null) {

                            cancelledAmount += o.getWalletAmount();

                        } else {

                            returnedAmount += returnedOrder.getTotalPrice();

                        }

                    }

                    totalDeductedAmount += o.getWalletAmount();

                } else if (o.getRefundTimestamp() != null && o.getRefundTimestamp().toLocalDate().equals(localDate)) {

                    ReturnOrder returnedOrder = returnOrderRepository.selectByOrderId(o.getId());

                    if (returnedOrder == null) {

                        cancelledAmount += o.getWalletAmount();

                    } else {

                        returnedAmount += returnedOrder.getTotalPrice();

                    }

                }

            }

            totalWalletConsumed -= transactionsOnThatDate.get(transactionId);

        }

        boolean reconciled = Math

                .abs(totalWalletConsumed - (totalDeductedAmount - cancelledAmount - returnedAmount)) < 2;

        return Arrays.asList(reconciled, totalWalletConsumed, totalDeductedAmount, cancelledAmount, returnedAmount, "");

    }

    private List<? extends Serializable> reconcileSchemeInAndWallet(LocalDate localDate,

                                                                    Map<Integer, Integer> transactionsOnThatDate, List<UserWalletHistory> history, Set<Integer> schemeIds) throws ProfitMandiBusinessException {

        int totalSchemeInWalletCredited = 0;

        float schemeInAmountAdded = 0;

        float schemeInAmountRolledBack = 0;

        for (int transactionId : transactionsOnThatDate.keySet()) {

            List<InventoryItem> inventoryItems = inventoryItemRepository.selectByPurchaseId(transactionId).stream()

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

            Set<Integer> inventoryItemIds = inventoryItems.stream().map(x -> x.getId()).collect(Collectors.toSet());

            if (inventoryItemIds.size() == 0) {

                continue;

            }

            List<SchemeInOut> sios = schemeInOutRepository.selectByInventoryItemIds(new HashSet<>(inventoryItemIds))

                    .stream().filter(x -> schemeIds.contains(x.getSchemeId())).collect(Collectors.toList());

            totalSchemeInWalletCredited += transactionsOnThatDate.get(transactionId);

            for (SchemeInOut sio : sios) {

                if (sio.getCreateTimestamp().toLocalDate().equals(localDate)) {

                    schemeInAmountAdded += sio.getAmount();

                }

                if (sio.getRolledBackTimestamp() != null

                        && sio.getRolledBackTimestamp().toLocalDate().equals(localDate)) {

                    schemeInAmountRolledBack += sio.getAmount();

                }

            }

        }

        boolean reconciled = Math

                .abs(totalSchemeInWalletCredited - (schemeInAmountAdded - schemeInAmountRolledBack)) < 5;

        return Arrays.asList(reconciled, totalSchemeInWalletCredited, schemeInAmountAdded, schemeInAmountRolledBack,

                "");

    }

    private List<? extends Serializable> reconcileSchemeOutAndWallet(LocalDate localDate,

                                                                     Map<Integer, Integer> transactionsOnThatDate, List<UserWalletHistory> history, Set<Integer> schemeIds) throws ProfitMandiBusinessException {

        int totalSchemeOutWalletCredited = 0;

        float schemeOutAmountAdded = 0;

        float schemeOutAmountRolledBack = 0;

        for (int fofoOrderId : transactionsOnThatDate.keySet()) {

            Set<Integer> fofoOrderItemIds = fofoOrderItemRepository.selectByOrderId(fofoOrderId).stream()

                    .map(x -> x.getId()).collect(Collectors.toSet());

            List<FofoLineItem> fofoLineItems = fofoLineItemRepository.selectByFofoOrderItemIds(fofoOrderItemIds);

            Set<Integer> inventoryItemIds = fofoLineItems.stream().map(x -> x.getInventoryItemId())

                    .collect(Collectors.toSet());

            List<SchemeInOut> sios = schemeInOutRepository.selectByInventoryItemIds(new HashSet<>(inventoryItemIds))

                    .stream().filter(x -> schemeIds.contains(x.getSchemeId())).collect(Collectors.toList());

            totalSchemeOutWalletCredited += transactionsOnThatDate.get(fofoOrderId);

            for (SchemeInOut sio : sios) {

                if (sio.getCreateTimestamp().toLocalDate().equals(localDate)) {

                    schemeOutAmountAdded += sio.getAmount();

                }

                if (sio.getRolledBackTimestamp() != null

                        && sio.getRolledBackTimestamp().toLocalDate().equals(localDate)) {

                    schemeOutAmountRolledBack += sio.getAmount();

                }

            }

        }

        boolean reconciled = Math

                .abs(totalSchemeOutWalletCredited - (schemeOutAmountAdded - schemeOutAmountRolledBack)) < 5;

        return Arrays.asList(reconciled, totalSchemeOutWalletCredited, schemeOutAmountAdded, schemeOutAmountRolledBack,

                "");

    }

    public void reconcileExpiredFixedSchemes() throws Exception {

        List<Scheme> allSchemes = schemeRepository.selectAll();

        // .stream().filter(x ->

        // x.getAmountType().equals(AmountType.FIXED)).collect(Collectors.toList());

        allSchemes = allSchemes.stream()

                .filter(x -> x.getEndDateTime().isAfter(LocalDate.of(2019, 3, 31).atStartOfDay()))

                .collect(Collectors.toList());

        System.out.println(

                "InventoryId\tSerialNumber\tItem Id\tScheme Id\tScheme Name\tScheme Type\tAmount Type\tScheme Amount\tAmount Paid\tReference\tTransaction Time\tScheme Start\tScheme End\tScheme Expiry");

        for (Scheme scheme : allSchemes) {

            if (scheme.getExpireTimestamp() != null) {

                if (scheme.getExpireTimestamp().isBefore(scheme.getEndDateTime())) {

                    List<SchemeInOut> inOuts = schemeInOutRepository.selectBySchemeIds(Sets.newHashSet(scheme.getId()));

                    if (scheme.getType().equals(SchemeType.IN)) {

                        for (SchemeInOut schemeInOut : inOuts) {

                            ScanRecord inRecord = scanRecordRepository

                                    .selectByInventoryItemId(schemeInOut.getInventoryItemId()).stream()

                                    .filter(x -> x.getType().equals(ScanType.PURCHASE)).collect(Collectors.toList())

                                    .get(0);

                            if (inRecord.getCreateTimestamp().isAfter(scheme.getExpireTimestamp())) {

                                InventoryItem ii = inventoryItemRepository.selectById(inRecord.getInventoryItemId());

                                Purchase purchase = purchaseRepository.selectByIdAndFofoId(ii.getPurchaseId(),

                                        ii.getFofoId());

                                System.out.println(String.format("%d\t%s\t%d\t%d\t%s\t%s\t%f\t%f\t%d\t%s\t%s\t%s\t%s",

                                        ii.getId(), ii.getSerialNumber(), ii.getItemId(), schemeInOut.getSchemeId(),

                                        scheme.getName(), scheme.getType(), scheme.getAmount(), schemeInOut.getAmount(),

                                        purchase.getId(), inRecord.getCreateTimestamp(), scheme.getStartDateTime(),

                                        scheme.getEndDateTime(), scheme.getExpireTimestamp()));

                            }

                        }

                    }

                    if (scheme.getType().equals(SchemeType.OUT)) {

                        for (SchemeInOut schemeInOut : inOuts) {

                            ScanRecord outRecord = scanRecordRepository

                                    .selectByInventoryItemId(schemeInOut.getInventoryItemId()).stream()

                                    .filter(x -> x.getType().equals(ScanType.SALE))

                                    .sorted((x1, x2) -> x1.getId() - x2.getId()).collect(Collectors.toList()).get(0);

                            if (outRecord.getCreateTimestamp().isAfter(scheme.getExpireTimestamp())) {

                                InventoryItem ii = inventoryItemRepository.selectById(outRecord.getInventoryItemId());

                                FofoOrder fofoOrder;

                                if (outRecord.getOrderId() == 0) {

                                    fofoOrder = orderService.getOrderByInventoryItemId(ii.getId());

                                } else {

                                    fofoOrder = fofoOrderRepository.selectByOrderId(outRecord.getOrderId());

                                    if (fofoOrder == null) {

                                        LOGGER.info("Order id does not exits - {}", outRecord.getOrderId());

                                        continue;

                                    }

                                }

                                System.out.println(String.format("%d\t%s\t%d\t%d\t%s\t%s\t%f\t%f\t%d\t%s\t%s\t%s\t%s",

                                        ii.getId(), ii.getSerialNumber(), ii.getItemId(), schemeInOut.getSchemeId(),

                                        scheme.getName(), scheme.getType(), scheme.getAmount(), schemeInOut.getAmount(),

                                        fofoOrder.getId(), outRecord.getCreateTimestamp(), scheme.getStartDateTime(),

                                        scheme.getEndDateTime(), scheme.getExpireTimestamp()));

                            }

                        }

                    }

                }

            }

        }

    }

    private void checkForDblClickIssues() {

        // For all schmes from April onwards

    }

    private void dbClickPurchase() {

        List<Purchase> purchases = purchaseRepository

                .selectAllBetweenPurchaseDate(LocalDate.of(2019, 4, 1).atStartOfDay(), LocalDateTime.now());

    }

    private void dbClickSale() {

        List<FofoOrder> sales = fofoOrderRepository.selectBetweenSaleDate(LocalDate.of(2019, 4, 1).atStartOfDay(),

                LocalDateTime.now());

    }

}