WebSVN – SmartDukaan – Diff – /trunk/profitmandi-cron/src/main/java/com/smartdukaan/cron/scheduled/ScheduledTasksTest.java

 package com.smartdukaan.cron.scheduled;
 import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
+import com.spice.profitmandi.common.model.BrandStockPrice;
 import com.spice.profitmandi.common.model.CustomRetailer;
 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.cart.CartService;
 import com.spice.profitmandi.dao.cart.SmartCartService;
 import com.spice.profitmandi.dao.entity.auth.AuthUser;
 import com.spice.profitmandi.dao.entity.fofo.*;
 import com.spice.profitmandi.dao.entity.logistics.AST;
 import com.spice.profitmandi.dao.entity.logistics.ASTRepository;
 import com.spice.profitmandi.dao.entity.logistics.AreaRepository;
-import com.spice.profitmandi.dao.entity.transaction.Loan;
+import com.spice.profitmandi.dao.entity.transaction.*;
-import com.spice.profitmandi.dao.entity.transaction.Order;
-import com.spice.profitmandi.dao.entity.transaction.Transaction;
 import com.spice.profitmandi.dao.entity.user.User;
 import com.spice.profitmandi.dao.enumuration.cs.EscalationType;
+import com.spice.profitmandi.dao.enumuration.transaction.LoanReferenceType;
 import com.spice.profitmandi.dao.model.BIRetailerModel;
 import com.spice.profitmandi.dao.model.BiSecondaryModel;
+import com.spice.profitmandi.dao.model.BrandWiseModel;
 import com.spice.profitmandi.dao.model.ReturnOrderInfoModel;
 import com.spice.profitmandi.dao.repository.auth.AuthRepository;
 import com.spice.profitmandi.dao.repository.cs.CsService;
 import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;
 import com.spice.profitmandi.dao.repository.fofo.*;
 import com.spice.profitmandi.dao.repository.inventory.StateRepository;
-import com.spice.profitmandi.dao.repository.transaction.LoanRepository;
+import com.spice.profitmandi.dao.repository.transaction.*;
-import com.spice.profitmandi.dao.repository.transaction.OrderRepository;
-import com.spice.profitmandi.dao.repository.transaction.ReturnOrderInfoRepository;
-import com.spice.profitmandi.dao.repository.transaction.TransactionRepository;
 import com.spice.profitmandi.dao.repository.user.UserRepository;
+import com.spice.profitmandi.service.inventory.InventoryService;
 import com.spice.profitmandi.service.transaction.SDCreditService;
 import com.spice.profitmandi.service.user.RetailerService;
+import com.spice.profitmandi.service.wallet.WalletService;
 import in.shop2020.model.v1.order.OrderStatus;
+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.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.beans.factory.annotation.Qualifier;
 import org.springframework.beans.factory.annotation.Value;
+import org.springframework.core.io.ByteArrayResource;
 import org.springframework.mail.javamail.JavaMailSender;
 import org.springframework.mail.javamail.MimeMessageHelper;
 import org.springframework.stereotype.Component;
 import org.springframework.transaction.annotation.Transactional;
 import javax.mail.MessagingException;
 import javax.mail.internet.InternetAddress;
 import javax.mail.internet.MimeMessage;
-import java.io.IOException;
+import java.io.*;
+import java.math.BigDecimal;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.time.LocalTime;
 import java.time.YearMonth;
 import java.time.temporal.ChronoUnit;
 import java.util.*;
 import java.util.stream.Collectors;
+import java.util.stream.Stream;
 @Component
 @Transactional(rollbackFor = {Throwable.class, ProfitMandiBusinessException.class})
 public class ScheduledTasksTest {
     @Autowired
     TransactionRepository transactionRepository;
     @Autowired
+    @Qualifier(value = "googleMailSender")
+    private JavaMailSender googleMailSender;
+    @Autowired
     LoanRepository loanRepository;
     @Autowired
     SDCreditService sdCreditService;
     ReturnOrderInfoRepository returnOrderInfoRepository;
     @Autowired
     OrderRepository orderRepository;
+    @Autowired
+    FofoOrderItemRepository fofoOrderItemRepository;
+    @Autowired
+    InventoryService inventoryService;
+    @Autowired
+    UserWalletRepository userWalletRepository;
+    @Autowired
+    LoanStatementRepository loanStatementRepository;
+    @Autowired
+    WalletService walletService;
     public void test() throws Exception {
         System.out.println("test start");
-//        smartCartService.activatedButNotBilledCatalogQtyMap();
+        //this.generateBiReportExcel();
+        this.loanSettle();
         System.out.println("test end");
+    }
     public void createLoanForBillingByTransactionIdAndInvoiceNumber(int transactionId, double invoiceAmount, String invoiceNumber) throws Exception {
         sdCreditService.createLoanForBilling(transactionId, invoiceAmount, invoiceNumber);
+    }
+    public void loanSettle() throws Exception {
+        List<Integer> refrences = Arrays.asList(25807,36003,38938,39506,42219,45084);
+        for(Integer ref : refrences){
+            List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(ref);
+            double amountSum = loanStatements.stream().map(LoanStatement::getAmount).mapToDouble(BigDecimal::doubleValue).sum();
+            if(amountSum > 0){
+                walletService.addAmountToWallet(loanStatements.get(0).getFofoId(),ref, WalletReferenceType.CREDIT_LIMIT,"Amount reversal against credit limit deduction",(float) amountSum,LocalDateTime.now());
+//                Loan statement entry
+                    BigDecimal adjustAmount = BigDecimal.valueOf(amountSum).negate(); // or multiply by -1
+                    LoanStatement loanStatement = new LoanStatement();
+                    loanStatement.setAmount(adjustAmount);
+                    loanStatement.setFofoId(loanStatements.get(0).getFofoId());
+                    loanStatement.setLoanReferenceType(LoanReferenceType.PRINCIPAL);
+                    loanStatement.setCreatedAt(LocalDateTime.now());
+                    loanStatement.setDescription("Amount reversal due to access debit against limit");
+                    loanStatement.setLoanId(ref);
+                    loanStatement.setBusinessDate(LocalDateTime.now());
+                    loanStatementRepository.persist(loanStatement);
+                    Loan loan = loanRepository.selectByLoanId(ref);
+                    loan.setPendingAmount(BigDecimal.valueOf(0));
+                    loan.setSettledOn(LocalDateTime.now());
+                }
+        }
+    }
     private void sendMailHtmlFormat(String email[], String body, String cc[], String bcc[], String subject)
             throws MessagingException, ProfitMandiBusinessException, IOException {
         MimeMessage message = mailSender.createMimeMessage();
         MimeMessageHelper helper = new MimeMessageHelper(message);
         LOGGER.info("Consolidated Sales L1 feedback summary email sent.");
+    }
     public void generateBiReportExcel() throws Exception {
         LocalDateTime startOfToday = LocalDate.now().atStartOfDay();
-        Map<Integer, CustomRetailer> customRetailers = retailerService.getAllFofoRetailers();
+        Map<Integer, CustomRetailer> customRetailers = retailerService.getAllFofoRetailersInternalFalse();
         List<Integer> retailerIds = customRetailers.values().stream().map(CustomRetailer::getPartnerId).collect(Collectors.toList());
-//        this month return data
+//  this month return data
+        YearMonth currentMonth = YearMonth.now();
+        String currentMonthStringValue = String.valueOf(currentMonth);
         LocalDateTime currentMonthStartDate = YearMonth.now().atDay(1).atStartOfDay();
         LocalDateTime currentMonthEndDate = LocalDateTime.now();
         List<ReturnOrderInfoModel> currentMonthReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(currentMonthStartDate, currentMonthEndDate);
         Map<Integer, Long> currentMonthPartnerReturnOrderInfoModelMap = currentMonthReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));
         List<Order> currentMonthRtoRefundOrders = orderRepository.selectAllOrderDatesBetweenByStatus(currentMonthStartDate, currentMonthEndDate, OrderStatus.RTO_REFUNDED);
         Map<Integer, Long> currentMonthRtoRefundOrderMap = currentMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));
-//        last month return data
+//  last month return data
         YearMonth lastMonth = YearMonth.now().minusMonths(1);
+        String lastMonthStringValue = String.valueOf(lastMonth);
         LocalDateTime lastMontStartDate = lastMonth.atDay(1).atStartOfDay();
         LocalDateTime lastMonthEndDate = lastMonth.atEndOfMonth().atTime(23, 59, 59);
         List<ReturnOrderInfoModel> lastMonthReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(lastMontStartDate, lastMonthEndDate);
         Map<Integer, Long> lastMonthPartnerReturnOrderInfoModelMap = lastMonthReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));
         List<Order> lastMonthRtoRefundOrders = orderRepository.selectAllOrderDatesBetweenByStatus(lastMontStartDate, lastMonthEndDate, OrderStatus.RTO_REFUNDED);
         Map<Integer, Long> lastMonthRtoRefundOrderMap = lastMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));
-//        twoMonthsAgo return data
+//  twoMonthsAgo return data
         YearMonth twoMonthsAgo = YearMonth.now().minusMonths(2);
+        String twoMonthAgoStringValue = String.valueOf(twoMonthsAgo);
         LocalDateTime twoMonthsAgoStartDate = twoMonthsAgo.atDay(1).atStartOfDay();
         LocalDateTime twoMonthsAgoEndDate = twoMonthsAgo.atEndOfMonth().atTime(23, 59, 59);
         List<ReturnOrderInfoModel> twoMonthAgoReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(twoMonthsAgoStartDate, twoMonthsAgoEndDate);
         Map<Integer, Long> twoMonthAgoPartnerReturnOrderInfoModelMap = twoMonthAgoReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));
         List<Order> twoMonthRtoRefundOrders = orderRepository.selectAllOrderDatesBetweenByStatus(twoMonthsAgoStartDate, twoMonthsAgoEndDate, OrderStatus.RTO_REFUNDED);
-        Map<Integer, Long> twoMonthRtoRefundOrderMap = twoMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));
+        Map<Integer, Long> twoMonthAgoRtoRefundOrderMap = twoMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));
-        List<BIRetailerModel> biRetailerModels = new ArrayList<>();
+        Map<Integer , BIRetailerModel> biRetailerModelMap = new HashMap<>();
-        List<BiSecondaryModel> biSecondaryModels = new ArrayList<>();
+        Map<Integer, Map<YearMonth, BiSecondaryModel>> allRetailerMonthlyData = new HashMap<>();
-        for(Integer fofoId: retailerIds){
+        Map<Integer,Double> fofoTotalStockPriceMap = new HashMap<>();
-            int rbmL1 = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_RBM,EscalationType.L1,fofoId);
+        Map<Integer,Map<String, BrandStockPrice>> fofoBrandStockPriceMap = new HashMap<>();
+        Map<Integer,Double> fofoTotalTertiaryMap = new HashMap<>();
-            String rbmName = authRepository.selectById(rbmL1).getFullName();
+        Map<Integer,Map<String, Double>> fofoBrandTertiaryMap = new HashMap<>();
+        for(Integer fofoId: retailerIds){
+            String rbmName = "";
+            int rbmL1 = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_RBM,EscalationType.L1,fofoId);
+            if(rbmL1 != 0){
+                 rbmName = authRepository.selectById(rbmL1).getFullName();
+            }
+            String bmName ="";
             int bmId = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_SALES,EscalationType.L2,fofoId);
+            if(bmId !=0){
-            String bmName = authRepository.selectById(bmId).getFullName();
+                bmName = authRepository.selectById(bmId).getFullName();
+            }
             int managerId = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_SALES,EscalationType.L1,fofoId);
             String managerName = " ";
             if(managerId != 0){
                  managerName = authRepository.selectById(managerId).getFullName();
 //            generate retaile detail
             BIRetailerModel biRetailerModel = new BIRetailerModel();
             biRetailerModel.setBmName(bmName);
             biRetailerModel.setCode(customRetailers.get(fofoId).getCode());
+            if(ast != null){
-            biRetailerModel.setArea(ast.getArea());
+                biRetailerModel.setArea(ast.getArea());
+            }else {
+                biRetailerModel.setArea("-");
+            }
             biRetailerModel.setCity(customRetailers.get(fofoId).getAddress().getCity());
             biRetailerModel.setStoreName(customRetailers.get(fofoId).getBusinessName());
-            biRetailerModel.setStoreName(String.valueOf(customRetailers.get(fofoId).getActivationType()));
+            biRetailerModel.setStatus(String.valueOf(customRetailers.get(fofoId).getActivationType()));
             biRetailerModel.setCategory(String.valueOf(partnerTypeThisMonth));
             biRetailerModel.setSalesManager(managerName);
             biRetailerModel.setRbm(rbmName);
-            biRetailerModels.add(biRetailerModel);
+            biRetailerModelMap.put(fofoId,biRetailerModel);
 //            generate secondary data
 //            this month secondary target
-            MonthlyTarget secondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId);
+            double currentSecondaryTarget =  monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId).getPurchaseTarget() : 0;
+            long currentMonthReturn = currentMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L) + currentMonthRtoRefundOrderMap.getOrDefault(fofoId, 0L);
             Map<Integer, Double> secondaryMtd = orderRepository.selectOrderValueBetweenDatesGroupByFofoId(Arrays.asList(fofoId),
                     Arrays.asList(OrderStatus.BILLED, OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.DELIVERY_SUCCESS, OrderStatus.ACCEPTED, OrderStatus.SUBMITTED_FOR_PROCESSING),
                     startOfToday.withDayOfMonth(1), startOfToday.with(LocalTime.MAX)).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
             double secondaryAchievedMtd = secondaryMtd.getOrDefault(fofoId, 0.0);
+            double currentMonthNetSecondary = secondaryAchievedMtd - currentMonthReturn;
+            double currentMonthSecondaryPercent = currentSecondaryTarget == 0 ? 0.0 : (secondaryAchievedMtd / currentSecondaryTarget) * 100;
+            double currentMonthUnbilled = 0;
+//          this month tertiary----------
+            LocalDateTime now = LocalDateTime.now();
+            double todaySale = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday, now, fofoId, false).get(fofoId);
+            double mtdSaleTillYesterDay = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday.withDayOfMonth(1), startOfToday, fofoId, false).get(fofoId);
+            double mtdSale = mtdSaleTillYesterDay + todaySale;
 //            last month secondary target
-            MonthlyTarget lastMonthSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId);
+            double lastMonthSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId) != null ?  monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId).getPurchaseTarget() : 0;
+            long lastMonthReturn = (lastMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + lastMonthRtoRefundOrderMap.getOrDefault(fofoId,0L));
             Map<Integer, Double> lastMonthSecondary = orderRepository.selectOrderValueBetweenDatesGroupByFofoId(Arrays.asList(fofoId),
                     Arrays.asList(OrderStatus.BILLED, OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.DELIVERY_SUCCESS, OrderStatus.ACCEPTED, OrderStatus.SUBMITTED_FOR_PROCESSING),
                     lastMontStartDate, lastMonthEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
             double lastMonthSecondaryAchieved = lastMonthSecondary.getOrDefault(fofoId, 0.0);
+            double lastMonthNetSecondary = lastMonthSecondaryAchieved - lastMonthReturn;
+            double lastMonthSecondaryPercent = lastMonthSecondaryTarget == 0 ? 0.0 : (lastMonthSecondaryAchieved / lastMonthSecondaryTarget) * 100;
+            double lastMonthUnbilled = 0;
-//            last month secondary target
+//           last month tertiary
+            Double lastMonthSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
+                    lastMontStartDate, lastMonthEndDate, fofoId, false).get(fofoId);
+//            two month ago secondary target
-            MonthlyTarget twoMonthAgoSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId);
+            double twoMonthAgoSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId).getPurchaseTarget() : 0;
+            long twoMonthAgoReturn = (twoMonthAgoPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + twoMonthAgoRtoRefundOrderMap.getOrDefault(fofoId,0L));
             Map<Integer, Double> twoMonthAgoSecondary = orderRepository.selectOrderValueBetweenDatesGroupByFofoId(Arrays.asList(fofoId),
                     Arrays.asList(OrderStatus.BILLED, OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.DELIVERY_SUCCESS, OrderStatus.ACCEPTED, OrderStatus.SUBMITTED_FOR_PROCESSING),
                     twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
             double twoMonthAgoSecondaryAchieved = twoMonthAgoSecondary.getOrDefault(fofoId, 0.0);
+            double twoMonthAgoNetSecondary = twoMonthAgoSecondaryAchieved - twoMonthAgoReturn;
+            double twoMonthAgoSecondaryPercent = twoMonthAgoSecondaryTarget == 0 ? 0.0 : (twoMonthAgoSecondaryAchieved / twoMonthAgoSecondaryTarget) * 100;
-//
+            double twoMonthAgoUnbilled = 0;
-////            this month tertiary
+//          second Month Tertiary
-//            Double todaySale = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday, now, fofoId, false).get(fofoId);
+            double twoMonthAgoSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
-//            Double mtdSaleTillYesterDay = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday.withDayOfMonth(1), startOfToday, fofoId, false).get(fofoId);
+                    twoMonthsAgoStartDate, twoMonthsAgoEndDate, fofoId, false).get(fofoId);
-//            Double mtdSale = mtdSaleTillYesterDay + todaySale;
+            Map<YearMonth, BiSecondaryModel> monthlySecondaryModels = new HashMap<>();
-//            Double lmtdSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
+            BiSecondaryModel currentMonthSecondaryModel = new BiSecondaryModel(
+                    currentSecondaryTarget,
+                    secondaryAchievedMtd,
+                    currentMonthReturn,
+                    currentMonthNetSecondary,
+                    currentMonthSecondaryPercent,
+                    mtdSale,
-//                    startOfToday.withDayOfMonth(1).minusMonths(1), now.minusMonths(1), fofoId, false).get(fofoId);
+                    currentMonthUnbilled // for now, unbilled tertiary value
+            );
-//
+            BiSecondaryModel lastMonthSecondaryModel = new BiSecondaryModel(
+                    lastMonthSecondaryTarget,
+                    lastMonthSecondaryAchieved,
+                    lastMonthReturn,
+                    lastMonthNetSecondary,
+                    lastMonthSecondaryPercent,
+                    lastMonthSale,
+                    lastMonthUnbilled // for now, unbilled tertiary value
+            );
-//
-//            BiSecondaryModel biSecondaryModel = new BiSecondaryModel();
+            BiSecondaryModel twoMonthAgoSecondaryModel = new BiSecondaryModel(
+                    twoMonthAgoSecondaryTarget,
+                    twoMonthAgoSecondaryAchieved,
+                    twoMonthAgoReturn,
+                    twoMonthAgoNetSecondary,
+                    twoMonthAgoSecondaryPercent,
+                    twoMonthAgoSale,
+                    twoMonthAgoUnbilled // for now, unbilled tertiary value
+            );
+            monthlySecondaryModels.put(currentMonth, currentMonthSecondaryModel);
+            monthlySecondaryModels.put(lastMonth, lastMonthSecondaryModel);
+            monthlySecondaryModels.put(twoMonthsAgo, twoMonthAgoSecondaryModel);
+            allRetailerMonthlyData.put(fofoId, monthlySecondaryModels);
+//            brandwiseStock value price
+            Map<String, BrandStockPrice> brandStockPriceMap = inventoryService.getBrandWiseStockValue(fofoId);
+            fofoBrandStockPriceMap.put(fofoId,brandStockPriceMap);
+            double totalStockPrice = brandStockPriceMap.values().stream().mapToDouble(x->x.getTotalValue()).sum();
+            fofoTotalStockPriceMap.put(fofoId,totalStockPrice);
+            Map<String, Double> brandMtdTertiaryAmount = fofoOrderItemRepository.selectSumAmountGroupByBrand(
+                    currentMonthStartDate, currentMonthEndDate, fofoId);
+            fofoBrandTertiaryMap.put(fofoId,brandMtdTertiaryAmount);
+            double totalMtdTertiaryAmount = brandMtdTertiaryAmount.values().stream().mapToDouble(Double::doubleValue).sum();
+            fofoTotalTertiaryMap.put(fofoId,totalMtdTertiaryAmount);
+//            List<BrandWiseModel> brandMtdSecondary = orderRepository.selectAllBilledOrderGroupByBrandFofoId(fofoId, curDate.withDayOfMonth(1).minusMonths(6));
+        }
-        LOGGER.info("biRetailerModels {}",biRetailerModels);
+        LOGGER.info("Total BI Retailers processed: {}", biRetailerModelMap.size());
+        //generate excel and sent to mail
+        List<List<String>> headerGroup = new ArrayList<>();
+        List<String> headers1 = Arrays.asList(
+                "Retailer Detail", "", "", "", "", "", "", "", "", "",
+                currentMonthStringValue, "", "", "", "", "", "",
+                lastMonthStringValue, "", "", "", "", "", "",
+                twoMonthAgoStringValue, "", "", "", "", "", ""
+        );
+        List<String> headers2 = Arrays.asList(
+                "Code", "Store Name", "City", "Area", "BM", "Sales Manager", "RBM", "Status", "Category",
+                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",
+                "Tertiary Sale", "Unbilled",
+                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",
+                "Tertiary Sale", "Unbilled",
+                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",
+                "Tertiary Sale", "Unbilled",
+              "Total Stock",  "Apple","Xiaomi","Vivo","Tecno","Samsung","Realme","Oppo","OnePlus","Poco","Lava","Itel","Almost New"
+        );
+        headerGroup.add(headers1);
+        headerGroup.add(headers2);
+        List<List<?>> rows = new ArrayList<>();
+        for (Map.Entry<Integer, BIRetailerModel> entry : biRetailerModelMap.entrySet()) {
+            Integer fofoId = entry.getKey();
+            BIRetailerModel retailer = entry.getValue();
+            Map<YearMonth, BiSecondaryModel> monthlyData = allRetailerMonthlyData.get(fofoId);
+            BiSecondaryModel current = monthlyData.getOrDefault(YearMonth.now(), new BiSecondaryModel(0,0,0,0,0,0,0));
+            BiSecondaryModel last = monthlyData.getOrDefault(YearMonth.now().minusMonths(1), new BiSecondaryModel(0,0,0,0,0,0,0));
+            BiSecondaryModel twoAgo = monthlyData.getOrDefault(YearMonth.now().minusMonths(2), new BiSecondaryModel(0,0,0,0,0,0,0));
+            List<Object> row = new ArrayList<>();
+            row.addAll(Arrays.asList(
+                    retailer.getCode(), retailer.getStoreName(), retailer.getCity(), retailer.getArea(),
+                    retailer.getBmName(), retailer.getSalesManager(), retailer.getRbm(),
+                    retailer.getStatus(), retailer.getCategory()
+            ));
+            // Current Month
+            row.addAll(Arrays.asList(
+                    current.getSecondaryTarget(),
+                    current.getSecondaryAchieved(),
+                    current.getSecondaryReturn(),
+                    current.getNetSecondary(),
+                    current.getSecondaryAchievedPercent(),
+                    current.getTertiary(),
+                    current.getTertiaryUnBilled()
+            ));
+            // Last Month
+            row.addAll(Arrays.asList(
+                    last.getSecondaryTarget(),
+                    last.getSecondaryAchieved(),
+                    last.getSecondaryReturn(),
+                    last.getNetSecondary(),
+                    last.getSecondaryAchievedPercent(),
+                    last.getTertiary(),
+                    last.getTertiaryUnBilled()
+            ));
+            // Two Months Ago
+            row.addAll(Arrays.asList(
+                    twoAgo.getSecondaryTarget(),
+                    twoAgo.getSecondaryAchieved(),
+                    twoAgo.getSecondaryReturn(),
+                    twoAgo.getNetSecondary(),
+                    twoAgo.getSecondaryAchievedPercent(),
+                    twoAgo.getTertiary(),
+                    twoAgo.getTertiaryUnBilled()
+            ));
+            Map<String, BrandStockPrice> brandMap = fofoBrandStockPriceMap.get(fofoId);
+            row.addAll(Arrays.asList(
+                    fofoTotalStockPriceMap.getOrDefault(fofoId, 0.0),
+                    brandMap.get("Apple") != null ? brandMap.get("Apple").getTotalValue() : 0.0,
+                    brandMap.get("Xiaomi") != null ? brandMap.get("Xiaomi").getTotalValue() : 0.0,
+                    brandMap.get("Vivo") != null ? brandMap.get("Vivo").getTotalValue() : 0.0,
+                    brandMap.get("Tecno") != null ? brandMap.get("Tecno").getTotalValue() : 0.0,
+                    brandMap.get("Samsung") != null ? brandMap.get("Samsung").getTotalValue() : 0.0,
+                    brandMap.get("Realme") != null ? brandMap.get("Realme").getTotalValue() : 0.0,
+                    brandMap.get("Oppo") != null ? brandMap.get("Oppo").getTotalValue() : 0.0,
+                    brandMap.get("OnePlus") != null ? brandMap.get("OnePlus").getTotalValue() : 0.0,
+                    brandMap.get("Poco") != null ? brandMap.get("Poco").getTotalValue() : 0.0,
+                    brandMap.get("Lava") != null ? brandMap.get("Lava").getTotalValue() : 0.0,
+                    brandMap.get("Itel") != null ? brandMap.get("Itel").getTotalValue() : 0.0,
+                    brandMap.get("Almost New") != null ? brandMap.get("Almost New").getTotalValue() : 0.0
+            ));
+            rows.add(row);
+        }
+// Send to email
+        ByteArrayOutputStream csvStream = FileUtil.getCSVByteStreamWithMultiHeaders(headerGroup, rows);
+        String fileName = "BI-Retailer-Monthly-Report-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".csv";
+        String[] sendToArray = new String[]{"ranu.rajput@smartdukaan.com"};
+        Utils.sendMailWithAttachment(googleMailSender, sendToArray, new String[]{}, "BI Retailer Monthly Report", "Please find attached the BI retailer secondary/tertiary monthly report.", fileName, new ByteArrayResource(csvStream.toByteArray()));
+    }
+}

Subversion Repositories SmartDukaan

(root)/trunk/profitmandi-cron/src/main/java/com/smartdukaan/cron/scheduled/ScheduledTasksTest.java – Rev 34606 → 34619