Rev 25503 | Rev 27745 | Go to most recent revision | View as "text/plain" | Blame | Compare with Previous | Last modification | View Log | RSS feed
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);@Autowiredprivate FofoStoreRepository fofoStoreRepository;@Autowiredprivate PurchaseRepository purchaseRepository;@Autowiredprivate OrderService orderService;@Autowiredprivate FofoOrderItemRepository fofoOrderItemRepository;@Autowiredprivate FofoLineItemRepository fofoLineItemRepository;@Autowiredprivate UserWalletRepository userWalletRepository;@Autowiredprivate InventoryItemRepository inventoryItemRepository;@Autowiredprivate ScanRecordRepository scanRecordRepository;@Autowiredprivate UserWalletHistoryRepository userWalletHistoryRepository;@Autowiredprivate SchemeInOutRepository schemeInOutRepository;@Autowiredprivate SchemeRepository schemeRepository;@Autowiredprivate InventoryItemRepository inventoryRepository;@Autowiredprivate RetailerService retailerService;@Autowiredprivate ReturnOrderRepository returnOrderRepository;@Autowiredprivate OrderRepository orderRepository;@Autowiredprivate FofoOrderRepository fofoOrderRepository;@Autowiredprivate 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());}}