Rev 12234 | Rev 14657 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
package in.shop2020.support.utils;import java.io.BufferedWriter;import java.io.File;import java.io.FileOutputStream;import java.io.FileWriter;import java.util.ArrayList;import java.util.Collections;import java.util.Date;import java.util.HashMap;import java.util.Hashtable;import java.util.List;import java.util.Map;import org.apache.commons.lang.StringUtils;import org.apache.commons.logging.Log;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import java.util.Set;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.mysql.jdbc.log.LogFactory;import in.shop2020.model.v1.catalog.CatalogService;import in.shop2020.model.v1.catalog.Category;import in.shop2020.model.v1.catalog.Item;import in.shop2020.model.v1.inventory.InventoryService;import in.shop2020.model.v1.inventory.InventoryType;import in.shop2020.model.v1.inventory.ItemInventory;import in.shop2020.model.v1.inventory.OOSStatus;import in.shop2020.model.v1.inventory.VendorItemPricing;import in.shop2020.model.v1.inventory.Warehouse;import in.shop2020.model.v1.inventory.WarehouseType;import in.shop2020.support.models.MovingStockSaleDetailComparator;import in.shop2020.support.models.SnmMovingStockSaleDetailComparator;import in.shop2020.support.models.StockSaleDetail;import in.shop2020.thrift.clients.CatalogClient;import in.shop2020.thrift.clients.InventoryClient;import in.shop2020.utils.GmailUtils;public class NewStockSalesReportFetcher {private static final int NUMBER_OF_DAYS_SALE = 5;private static final String[] mailToMT = {"chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com", "khushal.bhatia@saholic.com", "rajneesh.arora@saholic.com", "manish.sharma@shop2020.in"};private static final String[] mailToEMT = {"khushal.bhatia@saholic.com", "rajneesh.arora@saholic.com", "chandan.kumar@saholic.com", "manoj.pal@shop2020.in", "manish.sharma@shop2020.in"};private static final String[] mailTo = {"manish.sharma@shop2020.in"};private static final String senderAccountMail = "build-staging@shop2020.in";private static final String senderAccountPswd = "shop2020";static String tmpDir = System.getProperty("java.io.tmpdir");static File stockSalesReportMT = new File(tmpDir + "/StockSaleReport-MT" + (new Date()) + ".xls");static File stockSalesReportEMT = new File(tmpDir + "/StockSaleReport-EMT" + (new Date()) + ".xls");static List<StockSaleDetail> movingStockSaleListMT = new ArrayList<StockSaleDetail>();static List<StockSaleDetail> nonMovingStockSaleListMT = new ArrayList<StockSaleDetail>();static List<StockSaleDetail> slowMovingStockSaleListMT = new ArrayList<StockSaleDetail>();static List<StockSaleDetail> movingStockSaleListEMT = new ArrayList<StockSaleDetail>();static List<StockSaleDetail> nonMovingStockSaleListEMT = new ArrayList<StockSaleDetail>();static List<StockSaleDetail> slowMovingStockSaleListEMT = new ArrayList<StockSaleDetail>();private static Logger logger = LoggerFactory.getLogger(StockSalesReportFetcher.class);private static void createAndMailReport() {try {CatalogClient catalogServiceClient = new CatalogClient();CatalogService.Client catalogClient = catalogServiceClient.getClient();List<Item> itemList = catalogClient.getAllAliveItems();if(!catalogClient.isAlive()){catalogClient = catalogServiceClient.getClient();}List<Category> allCategories = catalogClient.getAllCategories();Map<Long, String> categoryIdLabelMap = new Hashtable<Long, String>();for(Category category : allCategories) {categoryIdLabelMap.put(category.getId(), category.getLabel());}InventoryClient inventoryServiceClient = new InventoryClient();InventoryService.Client inventoryClient = inventoryServiceClient.getClient();Map<Long, ItemInventory> inventoryMap = inventoryClient.getInventorySnapshot(0);if(!inventoryClient.isAlive()){inventoryClient = inventoryServiceClient.getClient();}List<OOSStatus> oosStatuses = inventoryClient.getOosStatusesForXDays(-1, NUMBER_OF_DAYS_SALE);Map<Long, List<OOSStatus>> oosStatusMapByItem = new Hashtable<Long, List<OOSStatus>>(5000);for(OOSStatus oosStatus : oosStatuses) {List<OOSStatus> oosStatusList;Long itemId = oosStatus.getItem_id();if(oosStatusMapByItem.containsKey(itemId)) {oosStatusList = oosStatusMapByItem.get(itemId);oosStatusList.add(oosStatus);} else {oosStatusList = new ArrayList<OOSStatus>(6);oosStatusList.add(oosStatus);}oosStatusMapByItem.put(itemId, oosStatusList);}if(!inventoryClient.isAlive()){inventoryClient = inventoryServiceClient.getClient();}List<Warehouse> ourGoodWarehouses = inventoryClient.getWarehouses(WarehouseType.OURS, InventoryType.GOOD, 0, 0, 0);Map<Long, Warehouse> ourWarehouseMap = new Hashtable<Long, Warehouse>(200);for(Warehouse warehouse : ourGoodWarehouses) {//System.out.println("Warehouse = " + warehouse.getId());ourWarehouseMap.put(warehouse.getId(), warehouse);}if(!inventoryClient.isAlive()){inventoryClient = inventoryServiceClient.getClient();}List<VendorItemPricing> vendorItemPricings = inventoryClient.getAllVendorItemPricing(0, 0);Map<Long, List<VendorItemPricing>> itemPricingMap = new Hashtable<Long, List<VendorItemPricing>>(5000);for(VendorItemPricing vendorItemPricing : vendorItemPricings) {List<VendorItemPricing> pricingListForItem;Long itemId = vendorItemPricing.getItemId();if(itemPricingMap.containsKey(itemId)) {pricingListForItem = itemPricingMap.get(itemId);pricingListForItem.add(vendorItemPricing);} else {pricingListForItem = new ArrayList<VendorItemPricing>();pricingListForItem.add(vendorItemPricing);}itemPricingMap.put(itemId, pricingListForItem);}Long stockCount;Double stockValue;for (Item item : itemList) {stockCount = 0L;stockValue = 0.0;Long totalOrderCount = 0L;Long websiteOrderCount = 0L;Long ebayOrderCount = 0L;Long snapdealOrderCount = 0L;Long flipkartOrderCount = 0L;Long amzn_mfnOrderCount = 0L;Long totalInStockDays = 0L;Long websiteInStockDays = 0L;Long ebayInStockDays = 0L;Long snapdealInStockDays = 0L;Long flipkartInStockDays = 0L;Long amzn_mfnInStockDays = 0L;Double websiteAverageSale = 0.0;Double totalAverageSale = 0.0;Double ebayAverageSale = 0.0;Double snapdealAverageSale = 0.0;Double flipkartAverageSale = 0.0;Double amzn_mfnAverageSale = 0.0;StringBuilder totalSaleHistory = new StringBuilder("");StringBuilder websiteSaleHistory = new StringBuilder("");StringBuilder ebaySaleHistory = new StringBuilder("");StringBuilder snapdealSaleHistory = new StringBuilder("");StringBuilder flipkartSaleHistory = new StringBuilder("");StringBuilder amzn_mfnSaleHistory = new StringBuilder("");ItemInventory itemInventory = inventoryMap.get(item.getId());Map<Long, Long> availabilityMap;if(itemInventory!= null) {//System.out.println("itemId =" + item.getId());availabilityMap = itemInventory.getAvailability();for (Long warehouseId : availabilityMap.keySet()) {if(ourWarehouseMap.containsKey(warehouseId)) {long currentAvailability = availabilityMap.get(warehouseId);//System.out.println("itemId =" + item.getId() + " availability = "+ currentAvailability);stockCount += currentAvailability;if(currentAvailability>0) {List<VendorItemPricing> pricingList = itemPricingMap.get(item.getId());for(VendorItemPricing vendorItemPricing: pricingList) {if(vendorItemPricing.getVendorId() == ourWarehouseMap.get(warehouseId).getVendor().getId()) {stockValue = stockValue + (vendorItemPricing.getNlc()*currentAvailability);}}}}}}if(stockCount<=0) {continue;}if(oosStatusMapByItem.containsKey(item.getId())){for(OOSStatus oosStatus : oosStatusMapByItem.get(item.getId())) {switch(oosStatus.getSourceId()) {case 0:if(oosStatus.isIs_oos()== false) {totalInStockDays++;totalOrderCount += oosStatus.getNum_orders();totalSaleHistory.append(oosStatus.getNum_orders() + "-");} else {totalSaleHistory.append("X-");}break;case 1:if(oosStatus.isIs_oos()== false) {websiteInStockDays++;websiteOrderCount += oosStatus.getNum_orders();websiteSaleHistory.append(oosStatus.getNum_orders() + "-");} else {websiteSaleHistory.append("X-");}break;case 3:if(oosStatus.isIs_oos()== false) {amzn_mfnInStockDays++;amzn_mfnOrderCount += oosStatus.getNum_orders();amzn_mfnSaleHistory.append(oosStatus.getNum_orders() + "-");} else {amzn_mfnSaleHistory.append("X-");}break;case 6:if(oosStatus.isIs_oos()== false) {ebayInStockDays++;ebayOrderCount += oosStatus.getNum_orders();ebaySaleHistory.append(oosStatus.getNum_orders() + "-");} else {ebaySaleHistory.append("X-");}break;case 7:if(oosStatus.isIs_oos()== false) {snapdealInStockDays++;snapdealOrderCount += oosStatus.getNum_orders();snapdealSaleHistory.append(oosStatus.getNum_orders() + "-");} else {snapdealSaleHistory.append("X-");}break;case 8:if(oosStatus.isIs_oos()== false) {flipkartInStockDays++;flipkartOrderCount += oosStatus.getNum_orders();flipkartSaleHistory.append(oosStatus.getNum_orders() + "-");} else {flipkartSaleHistory.append("X-");}break;default://do nothingbreak;}}}else{System.out.println("Oos Status is not available for item : - "+ item.getId());continue;}totalAverageSale = (double)totalOrderCount/totalInStockDays;totalAverageSale = Math.round(totalAverageSale * 10.0)/10.0;websiteAverageSale = (double)websiteOrderCount/websiteInStockDays;websiteAverageSale = Math.round(websiteAverageSale * 10.0)/10.0;ebayAverageSale = (double)ebayOrderCount/ebayInStockDays;ebayAverageSale = Math.round(ebayAverageSale * 10.0)/10.0;snapdealAverageSale = (double)snapdealOrderCount/snapdealInStockDays;snapdealAverageSale = Math.round(snapdealAverageSale * 10.0)/10.0;flipkartAverageSale = (double)flipkartOrderCount/flipkartInStockDays;flipkartAverageSale = Math.round(flipkartAverageSale * 10.0)/10.0;amzn_mfnAverageSale = (double)amzn_mfnOrderCount/amzn_mfnInStockDays;amzn_mfnAverageSale = Math.round(amzn_mfnAverageSale * 10.0)/10.0;if(totalAverageSale<=0.0) {StockSaleDetail ssDetail = new StockSaleDetail();ssDetail.setItemId(item.getId());ssDetail.setCategory(categoryIdLabelMap.get(item.getCategory()));ssDetail.setProductGroup(item.getProductGroup());ssDetail.setBrand(item.getBrand());ssDetail.setModelName(item.getModelName());ssDetail.setModelNumber(item.getModelNumber());ssDetail.setColor(item.getColor());ssDetail.setStockQuantity(stockCount);ssDetail.setStockValue(stockValue);ssDetail.setNodStock(totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()) : 0);ssDetail.setSalesHistoryAll(totalSaleHistory.substring(0, totalSaleHistory.length()-1));ssDetail.setAvgSalesAll(totalAverageSale);ssDetail.setSalesHistoryWS(websiteSaleHistory.substring(0, websiteSaleHistory.length()-1));ssDetail.setAvgSalesWS(websiteAverageSale);ssDetail.setSalesHistoryEbay(ebaySaleHistory.substring(0, ebaySaleHistory.length()-1));ssDetail.setAvgSalesEbay(ebayAverageSale);ssDetail.setSalesHistorySD(snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1));ssDetail.setAvgSalesSD(snapdealAverageSale);ssDetail.setSalesHistoryFK(flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1));ssDetail.setAvgSalesFK(flipkartAverageSale);ssDetail.setSalesHistoryMFN(amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1));ssDetail.setAvgSalesMFN(amzn_mfnAverageSale);if(item.getCategory() == 10006 || item.getCategory() == 10010){nonMovingStockSaleListMT.add(ssDetail);} else {nonMovingStockSaleListEMT.add(ssDetail);}}else {double nodStock = totalAverageSale > 0 ? new Double(stockCount/totalAverageSale): 0;if(nodStock>30) {StockSaleDetail ssDetail = new StockSaleDetail();ssDetail.setItemId(item.getId());ssDetail.setCategory(categoryIdLabelMap.get(item.getCategory()));ssDetail.setProductGroup(item.getProductGroup());ssDetail.setBrand(item.getBrand());ssDetail.setModelName(item.getModelName());ssDetail.setModelNumber(item.getModelNumber());ssDetail.setColor(item.getColor());ssDetail.setStockQuantity(stockCount);ssDetail.setStockValue(stockValue);ssDetail.setNodStock(totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()) : 0);ssDetail.setSalesHistoryAll(totalSaleHistory.substring(0, totalSaleHistory.length()-1));ssDetail.setAvgSalesAll(totalAverageSale);ssDetail.setSalesHistoryWS(websiteSaleHistory.substring(0, websiteSaleHistory.length()-1));ssDetail.setAvgSalesWS(websiteAverageSale);ssDetail.setSalesHistoryEbay(ebaySaleHistory.substring(0, ebaySaleHistory.length()-1));ssDetail.setAvgSalesEbay(ebayAverageSale);ssDetail.setSalesHistorySD(snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1));ssDetail.setAvgSalesSD(snapdealAverageSale);ssDetail.setSalesHistoryFK(flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1));ssDetail.setAvgSalesFK(flipkartAverageSale);ssDetail.setSalesHistoryMFN(amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1));ssDetail.setAvgSalesMFN(amzn_mfnAverageSale);if(item.getCategory() == 10006 || item.getCategory() == 10010){slowMovingStockSaleListMT.add(ssDetail);} else {slowMovingStockSaleListEMT.add(ssDetail);}} else {StockSaleDetail ssDetail = new StockSaleDetail();ssDetail.setItemId(item.getId());ssDetail.setCategory(categoryIdLabelMap.get(item.getCategory()));ssDetail.setProductGroup(item.getProductGroup());ssDetail.setBrand(item.getBrand());ssDetail.setModelName(item.getModelName());ssDetail.setModelNumber(item.getModelNumber());ssDetail.setColor(item.getColor());ssDetail.setStockQuantity(stockCount);ssDetail.setStockValue(stockValue);ssDetail.setNodStock(totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()) : 0);ssDetail.setSalesHistoryAll(totalSaleHistory.substring(0, totalSaleHistory.length()-1));ssDetail.setAvgSalesAll(totalAverageSale);ssDetail.setSalesHistoryWS(websiteSaleHistory.substring(0, websiteSaleHistory.length()-1));ssDetail.setAvgSalesWS(websiteAverageSale);ssDetail.setSalesHistoryEbay(ebaySaleHistory.substring(0, ebaySaleHistory.length()-1));ssDetail.setAvgSalesEbay(ebayAverageSale);ssDetail.setSalesHistorySD(snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1));ssDetail.setAvgSalesSD(snapdealAverageSale);ssDetail.setSalesHistoryFK(flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1));ssDetail.setAvgSalesFK(flipkartAverageSale);ssDetail.setSalesHistoryMFN(amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1));ssDetail.setAvgSalesMFN(amzn_mfnAverageSale);if(item.getCategory() == 10006 || item.getCategory() == 10010){movingStockSaleListMT.add(ssDetail);} else {movingStockSaleListEMT.add(ssDetail);}}}}if(movingStockSaleListMT!=null && movingStockSaleListMT.size()>2){Collections.sort(movingStockSaleListMT, new MovingStockSaleDetailComparator());}if(movingStockSaleListEMT!=null && movingStockSaleListEMT.size()>2){Collections.sort(movingStockSaleListEMT, new MovingStockSaleDetailComparator());}if(slowMovingStockSaleListMT!=null && slowMovingStockSaleListMT.size()>2){Collections.sort(slowMovingStockSaleListMT, new SnmMovingStockSaleDetailComparator());}if(slowMovingStockSaleListEMT!=null && slowMovingStockSaleListEMT.size()>2){Collections.sort(slowMovingStockSaleListEMT, new SnmMovingStockSaleDetailComparator());}if(nonMovingStockSaleListMT!=null && nonMovingStockSaleListMT.size()>2){Collections.sort(nonMovingStockSaleListMT, new SnmMovingStockSaleDetailComparator());}if(nonMovingStockSaleListEMT!=null && nonMovingStockSaleListEMT.size()>2){Collections.sort(nonMovingStockSaleListEMT, new SnmMovingStockSaleDetailComparator());}List<StockSaleDetail> itemToReplenishMT = new ArrayList<StockSaleDetail>();List<StockSaleDetail> itemToReplenishEMT = new ArrayList<StockSaleDetail>();for(StockSaleDetail ssDetail : movingStockSaleListMT){if(ssDetail.getNodStock() <= 5){itemToReplenishMT.add(ssDetail);}}for(StockSaleDetail ssDetail : movingStockSaleListEMT){if(ssDetail.getNodStock() <= 5){itemToReplenishEMT.add(ssDetail);}}if(itemToReplenishMT!=null && itemToReplenishMT.size()>2){Collections.sort(itemToReplenishMT, new MovingStockSaleDetailComparator());}if(itemToReplenishEMT!=null && itemToReplenishEMT.size()>2){Collections.sort(itemToReplenishEMT, new MovingStockSaleDetailComparator());}StringBuffer sb = new StringBuffer();sb.append("<html>" );sb.append("<table border=\"1\" align=\"center\">"+ "<caption><b>" + "ITEMS TO REPLENISH" + "</b></caption>"+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"+ "<td><b>" + "BRAND" + "</b></td>"+ "<td><b>" + "CATEGORY" + "</b></td>"+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"+ "<td><b>" + "STOCK-QTY" + "</b></td>"+ "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "<td><b>" + "NOD-STOCK" + "</b></td>"+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"+ "</tr>");int i=1;for(StockSaleDetail ssDetail : itemToReplenishMT){if(i==10){break;}sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"+ "<td>" + ssDetail.getBrand() + "</td>"+ "<td>" + ssDetail.getCategory() + "</td>"+ "<td>" + ssDetail.getProductGroup() + "</td>"+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"+ "<td>" + ssDetail.getStockQuantity() + "</td>"+ "<td>" + ssDetail.getStockValue() + "</td>"+ "<td>" + ssDetail.getNodStock() + "</td>"+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"+ "</tr>");i++;}sb.append("</table>" );sb.append("<table border=\"1\" align=\"center\">"+ "<caption><b>" + "SLOW MOVING ITEMS" + "</b></caption>"+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"+ "<td><b>" + "BRAND" + "</b></td>"+ "<td><b>" + "CATEGORY" + "</b></td>"+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"+ "<td><b>" + "STOCK-QTY" + "</b></td>"+ "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "<td><b>" + "NOD-STOCK" + "</b></td>"+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"+ "</tr>");i=1;for(StockSaleDetail ssDetail : slowMovingStockSaleListMT){if(i==10){break;}sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"+ "<td>" + ssDetail.getBrand() + "</td>"+ "<td>" + ssDetail.getCategory() + "</td>"+ "<td>" + ssDetail.getProductGroup() + "</td>"+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"+ "<td>" + ssDetail.getStockQuantity() + "</td>"+ "<td>" + ssDetail.getStockValue() + "</td>"+ "<td>" + ssDetail.getNodStock() + "</td>"+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"+ "</tr>");i++;}sb.append("</table>" );sb.append("<table border=\"1\" align=\"center\">"+ "<caption><b>" + "NON MOVING ITEMS" + "</b></caption>"+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"+ "<td><b>" + "BRAND" + "</b></td>"+ "<td><b>" + "CATEGORY" + "</b></td>"+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"+ "<td><b>" + "STOCK-QTY" + "</b></td>"+ "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "<td><b>" + "NOD-STOCK" + "</b></td>"+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"+ "</tr>");i=1;for(StockSaleDetail ssDetail : nonMovingStockSaleListMT){if(i==10){break;}sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"+ "<td>" + ssDetail.getBrand() + "</td>"+ "<td>" + ssDetail.getCategory() + "</td>"+ "<td>" + ssDetail.getProductGroup() + "</td>"+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"+ "<td>" + ssDetail.getStockQuantity() + "</td>"+ "<td>" + ssDetail.getStockValue() + "</td>"+ "<td>" + ssDetail.getNodStock() + "</td>"+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"+ "</tr>");i++;}sb.append("</table>");sb.append("</html>");BufferedWriter out = new BufferedWriter(new FileWriter("/tmp/MobileTablets-StockSales-file.htm"));out.write(sb.toString());out.flush();out.close();sb = new StringBuffer();sb.append("<html>" );sb.append("<table border=\"1\" align=\"center\">"+ "<caption><b>" + "ITEMS TO REPLENISH" + "</b></caption>"+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"+ "<td><b>" + "BRAND" + "</b></td>"+ "<td><b>" + "CATEGORY" + "</b></td>"+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"+ "<td><b>" + "STOCK-QTY" + "</b></td>"+ "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "<td><b>" + "NOD-STOCK" + "</b></td>"+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"+ "</tr>");i=1;for(StockSaleDetail ssDetail : itemToReplenishEMT){if(i==10){break;}sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"+ "<td>" + ssDetail.getBrand() + "</td>"+ "<td>" + ssDetail.getCategory() + "</td>"+ "<td>" + ssDetail.getProductGroup() + "</td>"+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"+ "<td>" + ssDetail.getStockQuantity() + "</td>"+ "<td>" + ssDetail.getStockValue() + "</td>"+ "<td>" + ssDetail.getNodStock() + "</td>"+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"+ "</tr>");i++;}sb.append("</table>" );sb.append("<table border=\"1\" align=\"center\">"+ "<caption><b>" + "SLOW MOVING ITEMS" + "</b></caption>"+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"+ "<td><b>" + "BRAND" + "</b></td>"+ "<td><b>" + "CATEGORY" + "</b></td>"+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"+ "<td><b>" + "STOCK-QTY" + "</b></td>"+ "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "<td><b>" + "NOD-STOCK" + "</b></td>"+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"+ "</tr>");i=1;for(StockSaleDetail ssDetail : slowMovingStockSaleListEMT){if(i==10){break;}sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"+ "<td>" + ssDetail.getBrand() + "</td>"+ "<td>" + ssDetail.getCategory() + "</td>"+ "<td>" + ssDetail.getProductGroup() + "</td>"+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"+ "<td>" + ssDetail.getStockQuantity() + "</td>"+ "<td>" + ssDetail.getStockValue() + "</td>"+ "<td>" + ssDetail.getNodStock() + "</td>"+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"+ "</tr>");i++;}sb.append("</table>" );sb.append("<table border=\"1\" align=\"center\">"+ "<caption><b>" + "NON MOVING ITEMS" + "</b></caption>"+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"+ "<td><b>" + "BRAND" + "</b></td>"+ "<td><b>" + "CATEGORY" + "</b></td>"+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"+ "<td><b>" + "STOCK-QTY" + "</b></td>"+ "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "<td><b>" + "NOD-STOCK" + "</b></td>"+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"+ "</tr>");i=1;for(StockSaleDetail ssDetail : nonMovingStockSaleListEMT){if(i==10){break;}sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"+ "<td>" + ssDetail.getBrand() + "</td>"+ "<td>" + ssDetail.getCategory() + "</td>"+ "<td>" + ssDetail.getProductGroup() + "</td>"+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"+ "<td>" + ssDetail.getStockQuantity() + "</td>"+ "<td>" + ssDetail.getStockValue() + "</td>"+ "<td>" + ssDetail.getNodStock() + "</td>"+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"+ "</tr>");i++;}sb.append("</table>");sb.append("</html>");out = new BufferedWriter(new FileWriter("/tmp/ExceptMobileTablets-StockSales-file.htm"));out.write(sb.toString());out.flush();out.close();createXlsFile(movingStockSaleListMT, slowMovingStockSaleListMT, nonMovingStockSaleListMT, true);createXlsFile(movingStockSaleListEMT, slowMovingStockSaleListEMT, nonMovingStockSaleListEMT, false);GmailUtils mailer = new GmailUtils();mailer.sendSSLMessage(mailToMT, "Stock Sales Report - Mobile And Tablets ", "", senderAccountMail, senderAccountPswd, tmpDir +"/"+ stockSalesReportMT.getName(), "/tmp/MobileTablets-StockSales-file.htm");mailer.sendSSLMessage(mailToEMT, "Stock Sales Report - Except Mobile And Tablets ", "", senderAccountMail, senderAccountPswd, tmpDir +"/"+stockSalesReportEMT.getName(), "/tmp/ExceptMobileTablets-StockSales-file.htm");} catch (Exception e) {logger.error("Error in generating/sending Stock Sales Report", e);}}private static void createXlsFile(List<StockSaleDetail> movingStockSaleList,List<StockSaleDetail> slowMovingStockSaleList,List<StockSaleDetail> nonMovingStockSaleList,boolean mobTab) {try{HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet movingSheet = workbook.createSheet("Moving-Stock");HSSFSheet nonMovingSheet = workbook.createSheet("Non-Moving-Stock");HSSFSheet slowMovingSheet = workbook.createSheet("Slow-Moving-Stock");Row row = movingSheet.createRow(0);row.createCell(0).setCellValue("ItemId");row.createCell(1).setCellValue("Category");row.createCell(2).setCellValue("ProductGroup");row.createCell(3).setCellValue("Brand");row.createCell(4).setCellValue("Model Name");row.createCell(5).setCellValue("Model Number");row.createCell(6).setCellValue("Color");row.createCell(7).setCellValue("Product");row.createCell(8).setCellValue("Stock Qty");row.createCell(9).setCellValue("Stock Value");row.createCell(10).setCellValue("NOD Stock");row.createCell(11).setCellValue("Sales History(All-Sources)");row.createCell(12).setCellValue("Avg Sales(All-Sources)");row.createCell(13).setCellValue("Sales History(Website)");row.createCell(14).setCellValue("Avg Sales(Website)");row.createCell(15).setCellValue("Sales History(Ebay)");row.createCell(16).setCellValue("Avg Sales(Ebay)");row.createCell(17).setCellValue("Sales History(Snapdeal)");row.createCell(18).setCellValue("Avg Sales(Snapdeal)");row.createCell(19).setCellValue("Sales History(Flipkart)");row.createCell(20).setCellValue("Avg Sales(Flipkart)");row.createCell(21).setCellValue("Sales History(Amazon-MFN)");row.createCell(22).setCellValue("Avg Sales(Amazon-MFN)");int i=1;for(StockSaleDetail ssDetail : movingStockSaleList){row = movingSheet.createRow(i);row.createCell(0).setCellValue(ssDetail.getItemId()+"");row.createCell(1).setCellValue(ssDetail.getCategory());row.createCell(2).setCellValue(ssDetail.getProductGroup());row.createCell(3).setCellValue(ssDetail.getBrand());row.createCell(4).setCellValue(ssDetail.getModelName());row.createCell(5).setCellValue(ssDetail.getModelNumber());row.createCell(6).setCellValue(ssDetail.getColor());row.createCell(7).setCellValue(ssDetail.getBrand()+" "+ssDetail.getModelName()+" "+ssDetail.getModelNumber()+" "+ssDetail.getColor());row.createCell(8).setCellValue(ssDetail.getStockQuantity()+"");row.createCell(9).setCellValue(ssDetail.getStockValue()+"");row.createCell(10).setCellValue(ssDetail.getNodStock()+"");row.createCell(11).setCellValue(ssDetail.getSalesHistoryAll());row.createCell(12).setCellValue(ssDetail.getAvgSalesAll()+"");row.createCell(13).setCellValue(ssDetail.getSalesHistoryWS());row.createCell(14).setCellValue(ssDetail.getAvgSalesWS()+"");row.createCell(15).setCellValue(ssDetail.getSalesHistoryEbay());row.createCell(16).setCellValue(ssDetail.getAvgSalesEbay()+"");row.createCell(17).setCellValue(ssDetail.getSalesHistorySD());row.createCell(18).setCellValue(ssDetail.getAvgSalesSD()+"");row.createCell(19).setCellValue(ssDetail.getSalesHistoryFK());row.createCell(20).setCellValue(ssDetail.getAvgSalesFK()+"");row.createCell(21).setCellValue(ssDetail.getSalesHistoryMFN());row.createCell(22).setCellValue(ssDetail.getAvgSalesMFN());i++;}row = nonMovingSheet.createRow(0);row.createCell(0).setCellValue("ItemId");row.createCell(1).setCellValue("Category");row.createCell(2).setCellValue("ProductGroup");row.createCell(3).setCellValue("Brand");row.createCell(4).setCellValue("Model Name");row.createCell(5).setCellValue("Model Number");row.createCell(6).setCellValue("Color");row.createCell(7).setCellValue("Product");row.createCell(8).setCellValue("Stock Qty");row.createCell(9).setCellValue("Stock Value");row.createCell(10).setCellValue("NOD Stock");row.createCell(11).setCellValue("Sales History(All-Sources)");row.createCell(12).setCellValue("Avg Sales(All-Sources)");row.createCell(13).setCellValue("Sales History(Website)");row.createCell(14).setCellValue("Avg Sales(Website)");row.createCell(15).setCellValue("Sales History(Ebay)");row.createCell(16).setCellValue("Avg Sales(Ebay)");row.createCell(17).setCellValue("Sales History(Snapdeal)");row.createCell(18).setCellValue("Avg Sales(Snapdeal)");row.createCell(19).setCellValue("Sales History(Flipkart)");row.createCell(20).setCellValue("Avg Sales(Flipkart)");row.createCell(21).setCellValue("Sales History(Amazon-MFN)");row.createCell(22).setCellValue("Avg Sales(Amazon-MFN)");i=1;for(StockSaleDetail ssDetail : nonMovingStockSaleList){row = nonMovingSheet.createRow(i);row.createCell(0).setCellValue(ssDetail.getItemId()+"");row.createCell(1).setCellValue(ssDetail.getCategory());row.createCell(2).setCellValue(ssDetail.getProductGroup());row.createCell(3).setCellValue(ssDetail.getBrand());row.createCell(4).setCellValue(ssDetail.getModelName());row.createCell(5).setCellValue(ssDetail.getModelNumber());row.createCell(6).setCellValue(ssDetail.getColor());row.createCell(7).setCellValue(ssDetail.getBrand()+" "+ssDetail.getModelName()+" "+ssDetail.getModelNumber()+" "+ssDetail.getColor());row.createCell(8).setCellValue(ssDetail.getStockQuantity()+"");row.createCell(9).setCellValue(ssDetail.getStockValue()+"");row.createCell(10).setCellValue(ssDetail.getNodStock()+"");row.createCell(11).setCellValue(ssDetail.getSalesHistoryAll());row.createCell(12).setCellValue(ssDetail.getAvgSalesAll()+"");row.createCell(13).setCellValue(ssDetail.getSalesHistoryWS());row.createCell(14).setCellValue(ssDetail.getAvgSalesWS()+"");row.createCell(15).setCellValue(ssDetail.getSalesHistoryEbay());row.createCell(16).setCellValue(ssDetail.getAvgSalesEbay()+"");row.createCell(17).setCellValue(ssDetail.getSalesHistorySD());row.createCell(18).setCellValue(ssDetail.getAvgSalesSD()+"");row.createCell(19).setCellValue(ssDetail.getSalesHistoryFK());row.createCell(20).setCellValue(ssDetail.getAvgSalesFK()+"");row.createCell(21).setCellValue(ssDetail.getSalesHistoryMFN());row.createCell(22).setCellValue(ssDetail.getAvgSalesMFN());i++;}row = slowMovingSheet.createRow(0);row.createCell(0).setCellValue("ItemId");row.createCell(1).setCellValue("Category");row.createCell(2).setCellValue("ProductGroup");row.createCell(3).setCellValue("Brand");row.createCell(4).setCellValue("Model Name");row.createCell(5).setCellValue("Model Number");row.createCell(6).setCellValue("Color");row.createCell(7).setCellValue("Product");row.createCell(8).setCellValue("Stock Qty");row.createCell(9).setCellValue("Stock Value");row.createCell(10).setCellValue("NOD Stock");row.createCell(11).setCellValue("Sales History(All-Sources)");row.createCell(12).setCellValue("Avg Sales(All-Sources)");row.createCell(13).setCellValue("Sales History(Website)");row.createCell(14).setCellValue("Avg Sales(Website)");row.createCell(15).setCellValue("Sales History(Ebay)");row.createCell(16).setCellValue("Avg Sales(Ebay)");row.createCell(17).setCellValue("Sales History(Snapdeal)");row.createCell(18).setCellValue("Avg Sales(Snapdeal)");row.createCell(19).setCellValue("Sales History(Flipkart)");row.createCell(20).setCellValue("Avg Sales(Flipkart)");row.createCell(21).setCellValue("Sales History(Amazon-MFN)");row.createCell(22).setCellValue("Avg Sales(Amazon-MFN)");i=1;for(StockSaleDetail ssDetail : slowMovingStockSaleList){row = slowMovingSheet.createRow(i);row.createCell(0).setCellValue(ssDetail.getItemId()+"");row.createCell(1).setCellValue(ssDetail.getCategory());row.createCell(2).setCellValue(ssDetail.getProductGroup());row.createCell(3).setCellValue(ssDetail.getBrand());row.createCell(4).setCellValue(ssDetail.getModelName());row.createCell(5).setCellValue(ssDetail.getModelNumber());row.createCell(6).setCellValue(ssDetail.getColor());row.createCell(7).setCellValue(ssDetail.getBrand()+" "+ssDetail.getModelName()+" "+ssDetail.getModelNumber()+" "+ssDetail.getColor());row.createCell(8).setCellValue(ssDetail.getStockQuantity()+"");row.createCell(9).setCellValue(ssDetail.getStockValue()+"");row.createCell(10).setCellValue(ssDetail.getNodStock()+"");row.createCell(11).setCellValue(ssDetail.getSalesHistoryAll());row.createCell(12).setCellValue(ssDetail.getAvgSalesAll()+"");row.createCell(13).setCellValue(ssDetail.getSalesHistoryWS());row.createCell(14).setCellValue(ssDetail.getAvgSalesWS()+"");row.createCell(15).setCellValue(ssDetail.getSalesHistoryEbay());row.createCell(16).setCellValue(ssDetail.getAvgSalesEbay()+"");row.createCell(17).setCellValue(ssDetail.getSalesHistorySD());row.createCell(18).setCellValue(ssDetail.getAvgSalesSD()+"");row.createCell(19).setCellValue(ssDetail.getSalesHistoryFK());row.createCell(20).setCellValue(ssDetail.getAvgSalesFK()+"");row.createCell(21).setCellValue(ssDetail.getSalesHistoryMFN());row.createCell(22).setCellValue(ssDetail.getAvgSalesMFN());i++;}FileOutputStream out = null;if(mobTab){out = new FileOutputStream(stockSalesReportMT);} else {out = new FileOutputStream(stockSalesReportEMT);}workbook.write(out);out.close();System.out.println("Excel written successfully..");}catch(Exception e){e.printStackTrace();}}public static void main(String[] args) {createAndMailReport();}}