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

 import com.spice.profitmandi.dao.entity.logistics.AreaRepository;
 import com.spice.profitmandi.dao.entity.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.*;
-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.*;
 import com.spice.profitmandi.dao.repository.user.UserRepository;
+import com.spice.profitmandi.service.RbmTargetService;
 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.*;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.usermodel.XSSFColor;
 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 javax.mail.MessagingException;
 import javax.mail.internet.InternetAddress;
 import javax.mail.internet.MimeMessage;
 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.*;
 import java.time.temporal.ChronoUnit;
 import java.util.*;
 import java.util.stream.Collectors;
 import java.util.stream.Stream;
     @Autowired
     LoanStatementRepository loanStatementRepository;
     @Autowired
+    ActivatedImeiRepository activatedImeiRepository;
+    @Autowired
+    PartnerDailyInvestmentRepository partnerDailyInvestmentRepository;
+    @Autowired
     WalletService walletService;
+    @Autowired
+    RbmTargetService rbmTargetService;
     public void test() throws Exception {
         System.out.println("test start");
-        //this.generateBiReportExcel();
+        this.generateBiReportExcel();
-        this.loanSettle();
         System.out.println("test end");
+    }
     public void createLoanForBillingByTransactionIdAndInvoiceNumber(int transactionId, double invoiceAmount, String invoiceNumber) throws Exception {
         Map<Integer, CustomRetailer> customRetailers = retailerService.getAllFofoRetailersInternalFalse();
         List<Integer> retailerIds = customRetailers.values().stream().map(CustomRetailer::getPartnerId).collect(Collectors.toList());
+        //partner daily investment
+        List<Loan> defaultLoans = sdCreditService.getDefaultLoan();
+        Map<Integer,List<Loan>> defaultLoanMap = defaultLoans.stream().collect(Collectors.groupingBy(Loan::getFofoId));
+        Map<Integer, PartnerDailyInvestment> partnerDailyInvestmentMap = new HashMap<>();
+        List<PartnerDailyInvestment> partnerDailyInvestments = partnerDailyInvestmentRepository
+                .selectAll(new ArrayList<>(retailerIds), LocalDate.now().minusDays(1));
+        if (!partnerDailyInvestments.isEmpty()) {
+            partnerDailyInvestmentMap = partnerDailyInvestments.stream()
+                    .collect(Collectors.toMap(x -> x.getFofoId(), x -> x));
+        }
 //  this month return data
         YearMonth currentMonth = YearMonth.now();
         String currentMonthStringValue = String.valueOf(currentMonth);
         LocalDateTime currentMonthStartDate = YearMonth.now().atDay(1).atStartOfDay();
         Map<Integer, Long> twoMonthAgoRtoRefundOrderMap = twoMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));
         Map<Integer , BIRetailerModel> biRetailerModelMap = new HashMap<>();
+        Map<Integer , FofoInvestmentModel> biInvestmentModelMap = new HashMap<>();
         Map<Integer, Map<YearMonth, BiSecondaryModel>> allRetailerMonthlyData = new HashMap<>();
         Map<Integer,Double> fofoTotalStockPriceMap = new HashMap<>();
         Map<Integer,Map<String, BrandStockPrice>> fofoBrandStockPriceMap = new HashMap<>();
+        Map<Integer,Long> fofoTotalMtdSecondaryMap = new HashMap<>();
+        Map<Integer , String> assessmentMap = new HashMap<>();
+        Map<Integer , String> zeroBillingMap = new HashMap<>();
+        Map<Integer , Float> billingNeededMap = new HashMap<>();
+        Map<Integer , Integer> countAMap = new HashMap<>();
+        Map<Integer,Map<String, Long>> fofoBrandWiseMtdSecondaryMap = new HashMap<>();
-        Map<Integer,Double> fofoTotalTertiaryMap = new HashMap<>();
+        Map<Integer,Double> fofoTotalMtdTertiaryMap = new HashMap<>();
-        Map<Integer,Map<String, Double>> fofoBrandTertiaryMap = new HashMap<>();
+        Map<Integer,Map<String, Double>> fofoBrandMtdTertiaryMap = new HashMap<>();
         for(Integer fofoId: retailerIds){
             String rbmName = "";
             int rbmL1 = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_RBM,EscalationType.L1,fofoId);
             if(rbmL1 != 0){
             biRetailerModel.setSalesManager(managerName);
             biRetailerModel.setRbm(rbmName);
             biRetailerModelMap.put(fofoId,biRetailerModel);
 //            generate secondary data
+            List<PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledMonthlyTotals = activatedImeiRepository.getTotalMonthlyActivatedNotBilled(fofoId,twoMonthsAgoStartDate);
+            Map<YearMonth , PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledTotalMap = partnerWiseActivatedNotBilledMonthlyTotals.stream().collect(Collectors.toMap(x-> YearMonth.parse(x.getYearMonth()),x->x));
 //            this month secondary target
             double currentSecondaryTarget =  monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId).getPurchaseTarget() : 0;
             double currentMonthNetSecondary = secondaryAchievedMtd - currentMonthReturn;
             double currentMonthSecondaryPercent = currentSecondaryTarget == 0 ? 0.0 : (secondaryAchievedMtd / currentSecondaryTarget) * 100;
-            double currentMonthUnbilled = 0;
+            double currentMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(currentMonth) != null ? partnerWiseActivatedNotBilledTotalMap.get(currentMonth).getTotalUnbilledAmount() : 0d;
 //          this month tertiary----------
             LocalDateTime now = LocalDateTime.now();
             double todaySale = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday, now, fofoId, false).get(fofoId);
             double lastMonthNetSecondary = lastMonthSecondaryAchieved - lastMonthReturn;
             double lastMonthSecondaryPercent = lastMonthSecondaryTarget == 0 ? 0.0 : (lastMonthSecondaryAchieved / lastMonthSecondaryTarget) * 100;
-            double lastMonthUnbilled = 0;
+            double lastMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(lastMonth) != null ? partnerWiseActivatedNotBilledTotalMap.get(lastMonth).getTotalUnbilledAmount() : 0d;
 //           last month tertiary
             Double lastMonthSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
                     lastMontStartDate, lastMonthEndDate, fofoId, false).get(fofoId);
             double twoMonthAgoNetSecondary = twoMonthAgoSecondaryAchieved - twoMonthAgoReturn;
             double twoMonthAgoSecondaryPercent = twoMonthAgoSecondaryTarget == 0 ? 0.0 : (twoMonthAgoSecondaryAchieved / twoMonthAgoSecondaryTarget) * 100;
-            double twoMonthAgoUnbilled = 0;
+            double twoMonthAgoUnbilled = partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo) != null ? partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo).getTotalUnbilledAmount() : 0d;
 //          second Month Tertiary
             double twoMonthAgoSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
                     twoMonthsAgoStartDate, twoMonthsAgoEndDate, fofoId, false).get(fofoId);
             fofoTotalStockPriceMap.put(fofoId,totalStockPrice);
             Map<String, Double> brandMtdTertiaryAmount = fofoOrderItemRepository.selectSumAmountGroupByBrand(
                     currentMonthStartDate, currentMonthEndDate, fofoId);
-            fofoBrandTertiaryMap.put(fofoId,brandMtdTertiaryAmount);
+            fofoBrandMtdTertiaryMap.put(fofoId,brandMtdTertiaryAmount);
             double totalMtdTertiaryAmount = brandMtdTertiaryAmount.values().stream().mapToDouble(Double::doubleValue).sum();
-            fofoTotalTertiaryMap.put(fofoId,totalMtdTertiaryAmount);
+            fofoTotalMtdTertiaryMap.put(fofoId,totalMtdTertiaryAmount);
+            List<BrandWiseModel> brandWiseMtdSecondary = orderRepository.selectAllBilledOrderGroupByBrandFofoId(fofoId, currentMonthStartDate);
+            Map<String,Long> brandWiseMtdSecondaryMap = brandWiseMtdSecondary.stream().collect(Collectors.toMap(BrandWiseModel::getBrand,BrandWiseModel::getAmount));
+            fofoBrandWiseMtdSecondaryMap.put(fofoId,brandWiseMtdSecondaryMap);
+            long mtdTotalSecondary = brandWiseMtdSecondary.stream().mapToLong(x -> x.getAmount() != 0 ? x.getAmount() : 0L).sum();
+            fofoTotalMtdSecondaryMap.put(fofoId,mtdTotalSecondary);
+            //            generate investment info
+            FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);
+            float shortInvestment = partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getShortInvestment() : 0f;
+            float agreedInvestment = partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getMinInvestment() : 0f;
+            float investmentLevel = partnerDailyInvestmentMap.get(fofoId) != null ? ((shortInvestment-agreedInvestment)/agreedInvestment)*100 : 0f;
+            List<Loan> fofoDefaultLoans = new ArrayList<>();
+            if(defaultLoanMap != null){
+                 fofoDefaultLoans  =  defaultLoanMap.get(fofoId);
+                LOGGER.info("fofoDefaultLoans {}",fofoDefaultLoans);
+            }
+            float defaultLoanAmount = 0f;
+            if(fofoDefaultLoans != null ){
+                if (!fofoDefaultLoans.isEmpty()) {
+                    defaultLoanAmount =  fofoDefaultLoans.stream().map(Loan::getPendingAmount).reduce(BigDecimal.ZERO, BigDecimal::add).floatValue(); // or .floatValue() directly
+                }
+            }
+            float activeLoan = loanRepository.selectActiveLoan(fofoId).stream().map(Loan::getPendingAmount).reduce(BigDecimal.ZERO,BigDecimal::add).floatValue();
+            float poValue = partnerDailyInvestmentMap.get(fofoId) != null ?  partnerDailyInvestmentMap.get(fofoId).getUnbilledAmount() : 0f;
+            List<Order> billedAndPoOrder = orderRepository.selectOrders(Arrays.asList(fofoId),Arrays.asList(OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.ACCEPTED, OrderStatus.BILLED, OrderStatus.SHIPPED_FROM_WH, OrderStatus.DELIVERY_SUCCESS),startOfToday,startOfToday.plusDays(1));
+            float poAndBilledValue = (float) billedAndPoOrder.stream().map(Order::getTotalAmount).reduce(0f,Float::sum);
+            double purchaseMtd = secondaryMtd.getOrDefault(fofoId, 0.0);
+            double todayRequiredDrr = rbmTargetService.calculateFofoIdTodayTarget(fofoId, purchaseMtd,LocalDate.now());
+            double monthDay1Drr = rbmTargetService.calculateFofoIdTodayTarget(fofoId,0d,YearMonth.now().atDay(1));
+            double gotDrrPercent = (todayRequiredDrr / monthDay1Drr) * 100;
+            int orderId = orderRepository.getLastOrderByFofoId(fofoId);
+            if (orderId != 0) {
-//            List<BrandWiseModel> brandMtdSecondary = orderRepository.selectAllBilledOrderGroupByBrandFofoId(fofoId, curDate.withDayOfMonth(1).minusMonths(6));
+                Order order = orderRepository.selectById(orderId);
+                // Calculate the number of days since the last purchase (billing)
+                int lastPurchaseDays = (int) Duration.between(order.getCreateTimestamp().plusDays(1), LocalDateTime.now()).toDays();
+                // Determine alert level
+                String alertLevel = "";
+                if (lastPurchaseDays >= 11) {
+                    alertLevel = "Alert for Management";
+                } else if (lastPurchaseDays >= 8) {
+                    alertLevel = " Alert for RSM/SH";
+                } else if (lastPurchaseDays >= 6) {
+                    alertLevel = "Must be Billed";
+                } else if (lastPurchaseDays >= 3) {
+                    alertLevel = "OK";
+                } else {
+                    alertLevel = "No Action Needed";
+                }
+                //          investment modal set all related value
+                FofoInvestmentModel fofoInvestmentModel = new FofoInvestmentModel();
+                fofoInvestmentModel.setCounterPotential(fofoStore.getCounterPotential());
+                fofoInvestmentModel.setShortInvestment(shortInvestment);
+                fofoInvestmentModel.setDefaultLoan(defaultLoanAmount);
+                fofoInvestmentModel.setInvestmentLevel(investmentLevel);
+                fofoInvestmentModel.setActiveLoan(activeLoan);
+                fofoInvestmentModel.setPoValue(poValue);
+                fofoInvestmentModel.setPoAndBilled(poAndBilledValue);
+                fofoInvestmentModel.setAgreedInvestment(agreedInvestment);
+                fofoInvestmentModel.setWallet(partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getWalletAmount() : 0);
+                fofoInvestmentModel.setMonthBeginingDrr(monthDay1Drr);
+                fofoInvestmentModel.setRequiredDrr(todayRequiredDrr);
+                fofoInvestmentModel.setDrrPercent(gotDrrPercent);
+                fofoInvestmentModel.setLastBillingDone(lastPurchaseDays);
+                fofoInvestmentModel.setSlab(alertLevel);
+                biInvestmentModelMap.put(fofoId, fofoInvestmentModel);
+            }
+                String assessment = "";
+                if(defaultLoanAmount > 0 ){
+                    assessment = "Loan Default";
+                }else if(investmentLevel <= 75 && defaultLoanAmount < 1){
+                    assessment = "Low Invest";
+                }else {
+                    assessment = "-";
+                }
+                assessmentMap.put(fofoId,assessment);
+                String zeroBilling = "";
+                if(currentMonthNetSecondary <= 100000 ){
+                    zeroBilling = "Zero Billing";
+                }else {
+                    zeroBilling = "-";
+                }
+                zeroBillingMap.put(fofoId,zeroBilling);
+                float billingNeeded = 0f;
+                if(gotDrrPercent >= 110 ){
+                    billingNeeded = (float) todayRequiredDrr;
+                }else {
+                    billingNeeded = 0f;
+                }
+                billingNeededMap.put(fofoId,billingNeeded);
+                int counta = 0;
+                if(defaultLoanAmount > 0 || investmentLevel <= 75 || currentMonthNetSecondary <= 100000 || gotDrrPercent >= 110 ){
+                    counta = 1;
+                }else {
+                    counta = 0;
+                }
+                countAMap.put(fofoId,counta);
+        }
         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", "", "", "", "", "", "", "", "", "",
+                "Retailer Detail", "", "", "", "", "", "", "", "",
                 currentMonthStringValue, "", "", "", "", "", "",
                 lastMonthStringValue, "", "", "", "", "", "",
-                twoMonthAgoStringValue, "", "", "", "", "", ""
+                twoMonthAgoStringValue, "", "", "", "", "", "",
+                "","", "", "", "", "", "", "", "", "", "", "", "", "",
+                "", "", "", "", "", "", "", "", "", "", "", "", "",
+                "", "", "", "", "", "", "", "", "", "", "", "", ""
         );
         List<String> headers2 = Arrays.asList(
+                "Assessment","Zero billing","Billing needed","Counta",
                 "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",
+                "Counter Potential", "Short investment", "Default", "INVESTMENT LEVEL", "Loan", "PO value", "Agreed investment",
+                "Wallet", "po+bill", "MONTH BEGINNING DRR", "REQ DRR", "Drr %", "Last billing Done", "Slab",
-              "Total Stock",  "Apple","Xiaomi","Vivo","Tecno","Samsung","Realme","Oppo","OnePlus","Poco","Lava","Itel","Almost New"
+              "Total Stock",  "Apple","Xiaomi", "Vivo", "Tecno", "Samsung", "Realme", "Oppo", "OnePlus", "Poco", "Lava", "Itel", "Almost New",
+              "Total Secondary", "Apple", "Xiaomi", "Vivo", "Tecno", "Samsung", "Realme", "Oppo", "OnePlus", "Poco", "Lava", "Itel", "Almost New",
+              "Total Tertiary",  "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(
+                    assessmentMap.get(fofoId),zeroBillingMap.get(fofoId),billingNeededMap.get(fofoId),countAMap.get(fofoId),
                     retailer.getCode(), retailer.getStoreName(), retailer.getCity(), retailer.getArea(),
                     retailer.getBmName(), retailer.getSalesManager(), retailer.getRbm(),
                     retailer.getStatus(), retailer.getCategory()
             ));
                     twoAgo.getNetSecondary(),
                     twoAgo.getSecondaryAchievedPercent(),
                     twoAgo.getTertiary(),
                     twoAgo.getTertiaryUnBilled()
             ));
+            FofoInvestmentModel fofoInvestmentModelValue = biInvestmentModelMap.get(fofoId);
+            if(fofoInvestmentModelValue != null){
+                row.addAll(Arrays.asList(
+                        fofoInvestmentModelValue.getCounterPotential(),
+                        fofoInvestmentModelValue.getShortInvestment(),
+                        fofoInvestmentModelValue.getDefaultLoan(),
+                        fofoInvestmentModelValue.getInvestmentLevel(),
+                        fofoInvestmentModelValue.getActiveLoan(),
+                        fofoInvestmentModelValue.getPoValue(),
+                        fofoInvestmentModelValue.getAgreedInvestment(),
+                        fofoInvestmentModelValue.getWallet(),
+                        fofoInvestmentModelValue.getPoAndBilled(),
+                        fofoInvestmentModelValue.getMonthBeginingDrr(),
+                        fofoInvestmentModelValue.getRequiredDrr(),
+                        fofoInvestmentModelValue.getDrrPercent(),
+                        fofoInvestmentModelValue.getLastBillingDone(),
+                        fofoInvestmentModelValue.getSlab()
+                ));
+            }else {
+                row.addAll(Arrays.asList(
+                        "-","-","-","-","-","-","-","-","-","-","-",""
+                ));
+            }
-            Map<String, BrandStockPrice> brandMap = fofoBrandStockPriceMap.get(fofoId);
+            Map<String, BrandStockPrice> brandStockMap = fofoBrandStockPriceMap.get(fofoId);
             row.addAll(Arrays.asList(
                     fofoTotalStockPriceMap.getOrDefault(fofoId, 0.0),
-                    brandMap.get("Apple") != null ? brandMap.get("Apple").getTotalValue() : 0.0,
+                    brandStockMap.get("Apple") != null ? brandStockMap.get("Apple").getTotalValue() : 0.0,
-                    brandMap.get("Xiaomi") != null ? brandMap.get("Xiaomi").getTotalValue() : 0.0,
+                    brandStockMap.get("Xiaomi") != null ? brandStockMap.get("Xiaomi").getTotalValue() : 0.0,
-                    brandMap.get("Vivo") != null ? brandMap.get("Vivo").getTotalValue() : 0.0,
+                    brandStockMap.get("Vivo") != null ? brandStockMap.get("Vivo").getTotalValue() : 0.0,
-                    brandMap.get("Tecno") != null ? brandMap.get("Tecno").getTotalValue() : 0.0,
+                    brandStockMap.get("Tecno") != null ? brandStockMap.get("Tecno").getTotalValue() : 0.0,
-                    brandMap.get("Samsung") != null ? brandMap.get("Samsung").getTotalValue() : 0.0,
+                    brandStockMap.get("Samsung") != null ? brandStockMap.get("Samsung").getTotalValue() : 0.0,
-                    brandMap.get("Realme") != null ? brandMap.get("Realme").getTotalValue() : 0.0,
+                    brandStockMap.get("Realme") != null ? brandStockMap.get("Realme").getTotalValue() : 0.0,
-                    brandMap.get("Oppo") != null ? brandMap.get("Oppo").getTotalValue() : 0.0,
+                    brandStockMap.get("Oppo") != null ? brandStockMap.get("Oppo").getTotalValue() : 0.0,
-                    brandMap.get("OnePlus") != null ? brandMap.get("OnePlus").getTotalValue() : 0.0,
+                    brandStockMap.get("OnePlus") != null ? brandStockMap.get("OnePlus").getTotalValue() : 0.0,
-                    brandMap.get("Poco") != null ? brandMap.get("Poco").getTotalValue() : 0.0,
+                    brandStockMap.get("Poco") != null ? brandStockMap.get("Poco").getTotalValue() : 0.0,
-                    brandMap.get("Lava") != null ? brandMap.get("Lava").getTotalValue() : 0.0,
+                    brandStockMap.get("Lava") != null ? brandStockMap.get("Lava").getTotalValue() : 0.0,
-                    brandMap.get("Itel") != null ? brandMap.get("Itel").getTotalValue() : 0.0,
+                    brandStockMap.get("Itel") != null ? brandStockMap.get("Itel").getTotalValue() : 0.0,
-                    brandMap.get("Almost New") != null ? brandMap.get("Almost New").getTotalValue() : 0.0
+                    brandStockMap.get("Almost New") != null ? brandStockMap.get("Almost New").getTotalValue() : 0.0
             ));
+            Map<String, Long> brandSecondaryMap = fofoBrandWiseMtdSecondaryMap.get(fofoId);
+            row.addAll(Arrays.asList(
+                    current.getSecondaryAchieved(),
+                    brandSecondaryMap.getOrDefault("Apple", 0L),
+                    brandSecondaryMap.getOrDefault("Xiaomi", 0L),
+                    brandSecondaryMap.getOrDefault("Vivo", 0L),
+                    brandSecondaryMap.getOrDefault("Tecno", 0L),
+                    brandSecondaryMap.getOrDefault("Samsung", 0L),
+                    brandSecondaryMap.getOrDefault("Realme", 0L),
+                    brandSecondaryMap.getOrDefault("Oppo", 0L),
+                    brandSecondaryMap.getOrDefault("OnePlus", 0L),
+                    brandSecondaryMap.getOrDefault("Poco", 0L),
+                    brandSecondaryMap.getOrDefault("Lava", 0L),
+                    brandSecondaryMap.getOrDefault("Itel", 0L),
+                    brandSecondaryMap.getOrDefault("Almost New", 0L)
+            ));
+            rows.add(row);
+            Map<String, Double> brandTertiaryMap = fofoBrandMtdTertiaryMap.get(fofoId);
+            row.addAll(Arrays.asList(
+                    current.getTertiary(),
+                    brandTertiaryMap.getOrDefault("Apple", 0d),
+                    brandTertiaryMap.getOrDefault("Xiaomi", 0d),
+                    brandTertiaryMap.getOrDefault("Vivo", 0d),
+                    brandTertiaryMap.getOrDefault("Tecno", 0d),
+                    brandTertiaryMap.getOrDefault("Samsung", 0d),
+                    brandTertiaryMap.getOrDefault("Realme", 0d),
+                    brandTertiaryMap.getOrDefault("Oppo", 0d),
+                    brandTertiaryMap.getOrDefault("OnePlus", 0d),
+                    brandTertiaryMap.getOrDefault("Poco", 0d),
+                    brandTertiaryMap.getOrDefault("Lava", 0d),
+                    brandTertiaryMap.getOrDefault("Itel", 0d),
+                    brandTertiaryMap.getOrDefault("Almost New", 0d)
+            ));
             rows.add(row);
+        }
-// Send to email
+        // Send to email
-        ByteArrayOutputStream csvStream = FileUtil.getCSVByteStreamWithMultiHeaders(headerGroup, rows);
+//        ByteArrayOutputStream csvStream = FileUtil.getCSVByteStreamWithMultiHeaders(headerGroup, rows);
+        ByteArrayOutputStream csvStream = getExcelStreamWithMultiHeaders(headerGroup, rows);
-        String fileName = "BI-Retailer-Monthly-Report-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".csv";
+        String fileName = "BI-Retailer-Monthly-Report-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".xlsx";
         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()));
+    }
+    public static ByteArrayOutputStream getExcelStreamWithMultiHeaders(List<List<String>> headerGroup, List<List<?>> rows) {
+        Workbook workbook = new XSSFWorkbook();
+        Sheet sheet = workbook.createSheet("BI Report");
+        int rowIndex = 0;
+        CellStyle centeredStyle = workbook.createCellStyle();
+        centeredStyle.setAlignment(HorizontalAlignment.CENTER); // Center horizontally
+        centeredStyle.setVerticalAlignment(VerticalAlignment.CENTER); // Center vertically
+    // Optional: bold font
+        Font font1 = workbook.createFont();
+        font1.setBold(true);
+        centeredStyle.setFont(font1);
+        // Create styles
+        Map<String, CellStyle> headerStyles = new HashMap<>();
+        // fontPurpleStyle
+        CellStyle purpleStyle = workbook.createCellStyle();
+        purpleStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());
+        purpleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+        purpleStyle.setFont(font1);
+        headerStyles.put("Assessment", purpleStyle);
+        headerStyles.put("Zero billing", purpleStyle);
+        headerStyles.put("Billing needed", purpleStyle);
+        headerStyles.put("Counta", purpleStyle);
+        headerStyles.put("MONTH BEGINNING DRR", purpleStyle);
+        headerStyles.put("REQ DRR", purpleStyle);
+        headerStyles.put("Drr %", purpleStyle);
+        // Light Blue
+        CellStyle blueStyle = workbook.createCellStyle();
+        blueStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
+        blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+        blueStyle.setFont(font1);
+        headerStyles.put("Code", blueStyle);
+        headerStyles.put("Store Name", blueStyle);
+        headerStyles.put("City", blueStyle);
+        headerStyles.put("Area", blueStyle);
+        headerStyles.put("BM", blueStyle);
+        headerStyles.put("RBM", blueStyle);
+        headerStyles.put("Sales Manager", blueStyle);
+        headerStyles.put("Status", blueStyle);
+        headerStyles.put("Category", blueStyle);
+        // Light Yellow
+        CellStyle yellowStyle = workbook.createCellStyle();
+        yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
+        yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+        yellowStyle.setFont(font1);
+        headerStyles.put("Last billing Done", yellowStyle);
+        headerStyles.put("Total Stock", yellowStyle);
+        // Light Orange
+        CellStyle orangeStyle = workbook.createCellStyle();
+        orangeStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
+        orangeStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+        orangeStyle.setFont(font1);
+        headerStyles.put("Total Tertiary", orangeStyle);
+        headerStyles.put("Total Secondary", orangeStyle);
+        headerStyles.put("Default", orangeStyle);
+        // Light green
+        CellStyle lightGreenStyle = workbook.createCellStyle();
+        lightGreenStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
+        lightGreenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+        lightGreenStyle.setFont(font1);
+        headerStyles.put("Short investment", lightGreenStyle);
+        headerStyles.put("INVESTMENT LEVEL", lightGreenStyle);
+        headerStyles.put("Loan", lightGreenStyle);
+        headerStyles.put("PO value", lightGreenStyle);
+        headerStyles.put("Agreed investment", lightGreenStyle);
+        headerStyles.put("Wallet", lightGreenStyle);
+        headerStyles.put("po+bill", lightGreenStyle);
+        // Light Green
+        CellStyle secondary1 = createStyle(workbook, IndexedColors.LIGHT_GREEN);
+        CellStyle secondary2 = createStyle(workbook, IndexedColors.LIGHT_YELLOW);
+        CellStyle secondary3 = createStyle(workbook, IndexedColors.LIGHT_ORANGE);
+        Map<String, CellStyle> brandStyles = new HashMap<>();
+        brandStyles.put("Apple", createStyle(workbook, IndexedColors.GREY_25_PERCENT));
+        brandStyles.put("Xiaomi", createStyle(workbook, IndexedColors.ORANGE));
+        brandStyles.put("Vivo", createStyle(workbook, IndexedColors.SKY_BLUE));
+        brandStyles.put("Tecno", createStyle(workbook, IndexedColors.LIGHT_BLUE));
+        brandStyles.put("Samsung", createStyle(workbook, IndexedColors.ROYAL_BLUE));
+        brandStyles.put("Realme", createStyle(workbook, IndexedColors.YELLOW));
+        brandStyles.put("Oppo", createStyle(workbook, IndexedColors.LIGHT_GREEN));
+        brandStyles.put("OnePlus", createStyle(workbook, IndexedColors.RED));
+        brandStyles.put("Poco", createStyle(workbook, IndexedColors.ORANGE));
+        brandStyles.put("Lava", createStyle(workbook, IndexedColors.LIGHT_YELLOW));
+        brandStyles.put("Itel", createStyle(workbook, IndexedColors.LIGHT_YELLOW));
+        brandStyles.put("Almost New", createStyle(workbook, IndexedColors.WHITE));
+        CellStyle defaultHeaderStyle = workbook.createCellStyle();
+        defaultHeaderStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
+        defaultHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+        defaultHeaderStyle.setFont(font1);
+        Map<String, Integer> headerCount = new HashMap<>();
+        for (int headerRowIndex = 0; headerRowIndex < headerGroup.size(); headerRowIndex++) {
+            List<String> headerRow = headerGroup.get(headerRowIndex);
+            Row row = sheet.createRow(rowIndex++);
+            for (int i = 0; i < headerRow.size(); i++) {
+                String headerText = headerRow.get(i);
+                sheet.setColumnWidth(i, 25 * 256);
+                row.setHeightInPoints(20); // 25-point height
+                Cell cell = row.createCell(i);
+                cell.setCellValue(headerText);
+                cell.setCellStyle(centeredStyle);
+                // Count how many times this header has appeared
+                int count = headerCount.getOrDefault(headerText, 0) + 1;
+                headerCount.put(headerText, count);
+                // Apply special style for repeated headers
+                if (headerText.equals("Secondary Target") || headerText.equals("Secondary Achieved") || headerText.equals("Returns") || headerText.equals("Net Secondary") || headerText.equals("Secondary %") || headerText.equals("Tertiary Sale") || headerText.equals("Unbilled")) {
+                    if (count == 1) {
+                        cell.setCellStyle(secondary1);
+                    } else if (count == 2) {
+                        cell.setCellStyle(secondary2);
+                    } else if (count == 3) {
+                        cell.setCellStyle(secondary3);
+                    }
+                }
+                // Brand header styling (apply only for the 2nd row of headers)
+                else if (headerRowIndex == 1 && brandStyles.containsKey(headerText)) {
+                    cell.setCellStyle(brandStyles.get(headerText));
+                }else if (headerStyles.containsKey(headerText)) {
+                    cell.setCellStyle(headerStyles.get(headerText));
+                } else {
+                    cell.setCellStyle(defaultHeaderStyle); // default style for others
+                }
+            }
+        }
+        // Write data rows
+        for (List<?> dataRow : rows) {
+            Row row = sheet.createRow(rowIndex++);
+            for (int i = 0; i < dataRow.size(); i++) {
+                Cell cell = row.createCell(i);
+                Object value = dataRow.get(i);
+                if (value instanceof Number) {
+                    cell.setCellValue(((Number) value).doubleValue());
+                } else {
+                    cell.setCellValue(value != null ? value.toString() : "");
+                }
+            }
+        }
+        // Auto-size columns
+        if (!rows.isEmpty()) {
+            for (int i = 0; i < rows.get(0).size(); i++) {
+                sheet.autoSizeColumn(i);
+            }
+        }
+        // Output as ByteArray
+        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
+            workbook.write(outputStream);
+            workbook.close();
+            return outputStream;
+        } catch (IOException e) {
+            throw new RuntimeException("Failed to generate Excel file", e);
+        }
+    }
+    private static CellStyle createStyle(Workbook workbook, IndexedColors color) {
+        CellStyle style = workbook.createCellStyle();
+        style.setFillForegroundColor(color.getIndex());
+        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+        Font font = workbook.createFont();
+        font.setBold(true);
+        style.setFont(font);
+        return style;
+    }
+}

Subversion Repositories SmartDukaan

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