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

 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.common.usermodel.HyperlinkType;
 import org.apache.poi.ss.usermodel.*;
 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;
     RbmTargetService rbmTargetService;
     @Autowired
     PartnerStatsService partnerStatsService;
+    @Autowired
+    UserWalletHistoryRepository userWalletHistoryRepository;
     public void test() throws Exception {
         System.out.println("test start");
         //partnerStatsService.getAllPartnerStats();
         this.generateBiReportExcel();
         System.out.println("test end");
             long currentMonthReturn = currentMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L) + currentMonthRtoRefundOrderMap.getOrDefault(fofoId, 0L);
-            Map<Integer, Double> secondaryMtd = orderRepository.selectOrderValueBetweenDatesGroupByFofoId(Arrays.asList(fofoId),
+            Map<Integer, Double> secondaryMtd = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
-                    Arrays.asList(OrderStatus.BILLED, OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.DELIVERY_SUCCESS),
                     startOfToday.withDayOfMonth(1), startOfToday.with(LocalTime.MAX).minusDays(1)).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
             double secondaryAchievedMtd = secondaryMtd.getOrDefault(fofoId, 0.0);
             double currentMonthNetSecondary = secondaryAchievedMtd - currentMonthReturn;
             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),
+            Map<Integer, Double> lastMonthSecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
-                    Arrays.asList(OrderStatus.BILLED, OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.DELIVERY_SUCCESS),
                     lastMontStartDate, lastMonthEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
             double lastMonthSecondaryAchieved = lastMonthSecondary.getOrDefault(fofoId, 0.0);
             double lastMonthNetSecondary = lastMonthSecondaryAchieved - lastMonthReturn;
             double twoMonthAgoSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(twoMonthsAgo, fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(twoMonthsAgo, fofoId).getPurchaseTarget() : 0;
             long twoMonthAgoReturn = (twoMonthAgoPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + twoMonthAgoRtoRefundOrderMap.getOrDefault(fofoId,0L));
-            Map<Integer, Double> twoMonthAgoSecondary = orderRepository.selectOrderValueBetweenDatesGroupByFofoId(Arrays.asList(fofoId),
+            Map<Integer, Double> twoMonthAgoSecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
-                    Arrays.asList(OrderStatus.BILLED, OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.DELIVERY_SUCCESS),
                     twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
             double twoMonthAgoSecondaryAchieved = twoMonthAgoSecondary.getOrDefault(fofoId, 0.0);
             double twoMonthAgoNetSecondary = twoMonthAgoSecondaryAchieved - twoMonthAgoReturn;
             String alertLevel = "-";
             int lastPurchaseDays = 0;
             if (orderId != 0) {
                 Order order = orderRepository.selectById(orderId);
+                LOGGER.info("last billing order - {}",order);
                 // Calculate the number of days since the last purchase (billing)
                 lastPurchaseDays = (int) Duration.between(order.getCreateTimestamp().plusDays(1), LocalDateTime.now()).toDays();
                 if (lastPurchaseDays >= 11) {
                     alertLevel = "Alert for Management";
         //generate excel and sent to mail
         List<List<String>> headerGroup = new ArrayList<>();
         List<String> headers1 = Arrays.asList(
                 "","","","",
-                "Retailer Detail", "","", "", "", "", "", "", "", "","",
+                "Retailer Detail", "","", "", "", "", "", "", "", "","","","",
                 twoMonthAgoStringValue, "", "", "", "", "", "",
                 lastMonthStringValue, "", "", "", "", "", "",
                 currentMonthStringValue, "", "", "", "", "", "",
         );
         List<String> headers2 = Arrays.asList(
                 "Assessment","Zero billing","Billing needed","Counta",
-                "BM","Partner Id","Link","Code","Area",  "City", "Store Name", "Status","Category","Sales Manager", "RBM",
+                "BM","Partner Id","Link","Wallet Date","Creation Date","Code","Area",  "City", "Store Name", "Status","Category","Sales Manager", "RBM",
                 "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 %",
         List<List<?>> rows = new ArrayList<>();
         for (Map.Entry<Integer, BIRetailerModel> entry : biRetailerModelMap.entrySet()) {
             Integer fofoId = entry.getKey();
+            User user = userRepository.selectById(fofoId);
+            LocalDateTime walletCreationDate = userWalletHistoryRepository.selectFirstCreatedDate(fofoId);
             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 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.getBmName(),fofoId ,"https://partners.smartdukaan.com/partnerPerformance?fofoId="+fofoId, retailer.getCode(), retailer.getArea(), retailer.getCity(),  retailer.getStoreName(), retailer.getStatus(),
+                    retailer.getBmName(),fofoId ,"https://partners.smartdukaan.com/partnerPerformance?fofoId="+fofoId,walletCreationDate,user.getCreateTimestamp(), retailer.getCode(), retailer.getArea(), retailer.getCity(),  retailer.getStoreName(), retailer.getStatus(),
                     retailer.getCategory(),  retailer.getSalesManager(), retailer.getRbm()
             ));
                 row.addAll(Arrays.asList(
                         fofoInvestmentModelValue.getCounterPotential(),
                         fofoInvestmentModelValue.getShortInvestment(),
                         fofoInvestmentModelValue.getDefaultLoan(),
                         fofoInvestmentModelValue.getInvestmentLevel(),
-                        fofoInvestmentModelValue.getActiveLoan(),
+                       "-"+fofoInvestmentModelValue.getActiveLoan(),
                         fofoInvestmentModelValue.getPoValue(),
                         fofoInvestmentModelValue.getAgreedInvestment(),
                         fofoInvestmentModelValue.getWallet(),
                         fofoInvestmentModelValue.getPoAndBilled(),
                         fofoInvestmentModelValue.getMonthBeginingDrr(),
+    }
     public static ByteArrayOutputStream getExcelStreamWithMultiHeaders(List<List<String>> headerGroup, List<List<?>> rows) {
         Workbook workbook = new XSSFWorkbook();
         Sheet sheet = workbook.createSheet("BI Report");
+        CreationHelper creationHelper = workbook.getCreationHelper();
         int rowIndex = 0;
         CellStyle centeredStyle = workbook.createCellStyle();
         centeredStyle.setAlignment(HorizontalAlignment.CENTER); // Center horizontally
         centeredStyle.setVerticalAlignment(VerticalAlignment.CENTER); // Center vertically
         headerStyles.put("BM", blueStyle);
         headerStyles.put("RBM", blueStyle);
         headerStyles.put("Sales Manager", blueStyle);
         headerStyles.put("Status", blueStyle);
         headerStyles.put("Category", blueStyle);
+        headerStyles.put("Wallet Date", blueStyle);
+        headerStyles.put("Creation Date", blueStyle);
+        headerStyles.put("Partner Id", blueStyle);
+        //for link
+        // Create hyperlink style
+        CellStyle hyperlinkStyle = workbook.createCellStyle();
+        Font hlinkFont = workbook.createFont();
+        hlinkFont.setUnderline(Font.U_SINGLE);
+        hlinkFont.setColor(IndexedColors.BLUE.getIndex());
+        hyperlinkStyle.setFont(hlinkFont);
         // Light Yellow
         CellStyle yellowStyle = workbook.createCellStyle();
         yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
         yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                 if (value instanceof Number) {
                     cell.setCellValue(((Number) value).doubleValue());
                 } else {
                     cell.setCellValue(value != null ? value.toString() : "");
+                }
+                if (i == 6 && value != null) { // Assuming column 6 is "Link"
+                    Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
+                    hyperlink.setAddress(value.toString());
+                    cell.setCellValue("View Link"); // Text to display in Excel
+                    cell.setHyperlink(hyperlink);
+                    cell.setCellStyle(hyperlinkStyle);
+                } else {
+                    cell.setCellValue(value != null ? value.toString() : "");
+                }
+            }
+        }
         // Auto-size columns
         if (!rows.isEmpty()) {

Subversion Repositories SmartDukaan

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