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

	@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 InventoryItemRepository inventoryRepository;

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

	}

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

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

					.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 (Map.Entry<WalletReferenceType, List<UserWalletHistory>> entry : referenceWiseWalletHistory

					.entrySet()) {

				List<UserWalletHistory> history = entry.getValue();

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

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

				switch (entry.getKey()) {

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

		int totalSchemeInWalletCredited = 0;

		float schemeInAmountAdded = 0;

		float schemeInAmountRolledBack = 0;

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

			List<InventoryItem> inventoryItems = inventoryRepository.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) {

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

	}

}