Rev 12619 | Blame | Compare with Previous | Last modification | View Log | RSS feed
package com.amazonaws.mws.samples;import in.shop2020.model.v1.catalog.Amazonlisted;import in.shop2020.model.v1.catalog.CatalogServiceException;import in.shop2020.model.v1.catalog.Category;import in.shop2020.model.v1.catalog.Item;import in.shop2020.model.v1.inventory.AmazonFbaInventorySnapshot;import in.shop2020.model.v1.inventory.AmazonInventorySnapshot;import in.shop2020.model.v1.inventory.InventoryServiceException;import in.shop2020.model.v1.inventory.ItemInventory;import in.shop2020.model.v1.inventory.Vendor;import in.shop2020.model.v1.inventory.VendorItemPricing;import in.shop2020.model.v1.order.AmazonFbaSalesSnapshot;import in.shop2020.model.v1.order.TransactionService.Client;import in.shop2020.thrift.clients.CatalogClient;import in.shop2020.thrift.clients.InventoryClient;import in.shop2020.thrift.clients.TransactionClient;import in.shop2020.utils.GmailUtils;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.FileReader;import java.io.IOException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.TimeZone;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.thrift.TException;import au.com.bytecode.opencsv.CSVReader;public class CreateSendFBAStockEstimation {final public static String AMAZON_FBA_SHEET = "/home/FBA-Stock-File.xls";public static void main(String... args){TransactionClient transactionServiceClient = null;try {transactionServiceClient = new TransactionClient();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}in.shop2020.model.v1.order.TransactionService.Client transactionClient = transactionServiceClient.getClient();List<AmazonFbaSalesSnapshot> salessnapshots = null;try {salessnapshots = transactionClient.getAmazonFbaSalesSnapshotForDays(5);} catch (TException e) {// TODO Auto-generated catch blocke.printStackTrace();}Map<Long,List<FbaSalesSnapshot>> itemId5daysSale = new HashMap<Long,List<FbaSalesSnapshot>>();AmazonFbaSalesSnapshot snapshot;for(AmazonFbaSalesSnapshot salessnapshot:salessnapshots){try {FbaSalesSnapshot fbaSalesSnapshot = new FbaSalesSnapshot();if(itemId5daysSale.containsKey(salessnapshot.getItem_id())){if(!salessnapshot.isIsOutOfStock()){fbaSalesSnapshot.setPromotionOrderCount(salessnapshot.getPromotionOrderCount());fbaSalesSnapshot.setTotalOrderCount(salessnapshot.getTotalOrderCount());fbaSalesSnapshot.setTotalPromotionSale((float) salessnapshot.getPromotionSale());fbaSalesSnapshot.setTotalSale((float) salessnapshot.getTotalSale());}else{fbaSalesSnapshot.setPromotionOrderCount(-1);fbaSalesSnapshot.setTotalOrderCount(-1);fbaSalesSnapshot.setTotalPromotionSale((float) 0);fbaSalesSnapshot.setTotalSale((float) 0);}itemId5daysSale.get(salessnapshot.getItem_id()).add(fbaSalesSnapshot);}else{List<FbaSalesSnapshot> last5daysSale = new ArrayList<FbaSalesSnapshot>();if(!salessnapshot.isIsOutOfStock()){fbaSalesSnapshot.setPromotionOrderCount(salessnapshot.getPromotionOrderCount());fbaSalesSnapshot.setTotalOrderCount(salessnapshot.getTotalOrderCount());fbaSalesSnapshot.setTotalPromotionSale((float) salessnapshot.getPromotionSale());fbaSalesSnapshot.setTotalSale((float) salessnapshot.getTotalSale());}else{fbaSalesSnapshot.setPromotionOrderCount(-1);fbaSalesSnapshot.setTotalOrderCount(-1);fbaSalesSnapshot.setTotalPromotionSale((float) 0);fbaSalesSnapshot.setTotalSale((float) 0);}last5daysSale.add(fbaSalesSnapshot);itemId5daysSale.put(salessnapshot.getItem_id(),last5daysSale);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}//System.out.println("ItemID , Last Five Days Sale , Product Name , TP , NLC , STICKY , Preferred Vendor , Qty , Min AFN , Min MFN , SP ,Total Amount");CatalogClient catalogServiceClient = null;InventoryClient inventoryServiceClient = null;try {inventoryServiceClient = new InventoryClient();//catalogServiceClient = new CatalogClient();catalogServiceClient = new CatalogClient("catalog_service_server_host_amazon","catalog_service_server_port");} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}in.shop2020.model.v1.catalog.CatalogService.Client catalogClient = catalogServiceClient.getClient();in.shop2020.model.v1.inventory.InventoryService.Client inventoryClient = inventoryServiceClient.getClient();HSSFWorkbook hwb=new HSSFWorkbook();HSSFSheet sheet = hwb.createSheet("FBA-STOCK-SHEET");HSSFRow rowhead= sheet.createRow((short)0);rowhead.createCell((short) 0).setCellValue("ITEM-ID");rowhead.createCell((short) 1).setCellValue("BRAND");rowhead.createCell((short) 2).setCellValue("CATEGORY");rowhead.createCell((short) 3).setCellValue("PRODUCT-GROUP");rowhead.createCell((short) 4).setCellValue("PRODUCT-NAME");rowhead.createCell((short) 5).setCellValue("IS-STICKY");rowhead.createCell((short) 6).setCellValue("PREFERRED-VENDOR");rowhead.createCell((short) 7).setCellValue("TRANSFER-PRICE");rowhead.createCell((short) 8).setCellValue("NLC");rowhead.createCell((short) 9).setCellValue("LAST 5 DAYS TOTAL SALE");rowhead.createCell((short) 10).setCellValue("TOTAL ASP");rowhead.createCell((short) 11).setCellValue("LAST 5 DAYS PROMOTION SALE");rowhead.createCell((short) 12).setCellValue("PROMOTION ASP");rowhead.createCell((short) 13).setCellValue("MIN-AFN-PRICE");rowhead.createCell((short) 14).setCellValue("MIN-MFN-PRICE");rowhead.createCell((short) 15).setCellValue("OUR-PRICE");rowhead.createCell((short) 16).setCellValue("STOCK-IN-MUMBAI");rowhead.createCell((short) 17).setCellValue("AVERAGE-SALE");rowhead.createCell((short) 18).setCellValue("DAYS-OF-STOCK");rowhead.createCell((short) 19).setCellValue("REQUIRED STOCK");rowhead.createCell((short) 20).setCellValue("CURRENT-STOCK (FBA)");rowhead.createCell((short) 21).setCellValue("P.O");rowhead.createCell((short) 22).setCellValue("P.O AMOUNT");rowhead.createCell((short) 23).setCellValue("VARIANCE-FROM-CHEAPEST");rowhead.createCell((short) 24).setCellValue("DAYS-OF-STOCK-RULE");int iterator=1;Map<Long,ItemInventory> allItemsAvailability = null;List<Long> WarehouseIdsForMumbaiLocation = null;try {WarehouseIdsForMumbaiLocation = inventoryClient.getOursGoodWarehouseIdsForLocation(1);allItemsAvailability = inventoryClient.getInventorySnapshot(0);} catch (TException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}for(Entry<Long, List<FbaSalesSnapshot>> entry : itemId5daysSale.entrySet()){Item item = null;VendorItemPricing vip=null;Vendor vendor = null;Category category = null;try {System.out.println("Item ID is " + entry.getKey());item = catalogClient.getItem(entry.getKey());if(item.getId()==0){continue;}category = catalogClient.getCategory(item.getCategory());//System.out.println("Preferred Vendor is " + item.getPreferredVendor());if(item.getPreferredVendor()!=0){vip = inventoryClient.getItemPricing(item.getId(),item.getPreferredVendor());vendor = inventoryClient.getVendor(item.getPreferredVendor());}} catch (CatalogServiceException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (TException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (InventoryServiceException e) {// TODO Auto-generated catch blocke.printStackTrace();}HSSFRow row= sheet.createRow((short)iterator);HSSFCell cell = row.createCell((short) 0);cell.setCellValue(entry.getKey());HSSFCellStyle style = hwb.createCellStyle();style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);HSSFFont font = hwb.createFont();style.setFillForegroundColor(HSSFColor.WHITE.index);font.setColor(HSSFColor.BLACK.index);style.setFont(font);cell.setCellStyle(style);cell.setCellValue(entry.getKey());int count =1;Double promotion_sale=0.0;Double total_sale=0.0;float avg_promotion_sale=0;float avg_total_sale=0;int total_sale_days=0;int promotion_sale_days=0;StringBuilder total_days_sale = new StringBuilder();StringBuilder promotion_days_sale = new StringBuilder();for(FbaSalesSnapshot sale:entry.getValue()){if(sale.getPromotionOrderCount()!=-1){promotion_days_sale.append(sale.getPromotionOrderCount());avg_promotion_sale=avg_promotion_sale+sale.getPromotionOrderCount();promotion_sale = promotion_sale+sale.getTotalPromotionSale();promotion_sale_days++;}else{promotion_days_sale.append("X");}if(count != entry.getValue().size()){promotion_days_sale.append("-");}if(sale.getTotalOrderCount()!=-1){total_days_sale.append(sale.getTotalOrderCount());avg_total_sale=avg_total_sale+sale.getTotalOrderCount();total_sale = total_sale+sale.getTotalSale();total_sale_days++;}else{total_days_sale.append("X");}if(count != entry.getValue().size()){total_days_sale.append("-");}count++;}float promotion_sale_avg=0;float total_sale_avg = 0;if(promotion_sale_days!=0){promotion_sale_avg = avg_promotion_sale/promotion_sale_days;}if(total_sale_days!=0){total_sale_avg=avg_total_sale/total_sale_days;}AmazonFbaSalesSnapshot latest_snapshot = null;long availability=0;double percent_diff=0;try {latest_snapshot = transactionClient.getAmazonFbaSalesLatestSnapshotForItem(entry.getKey());//availability = inventoryClient.getAmazonFbaItemInventory(entry.getKey());} catch (TException e) {// TODO Auto-generated catch blocke.printStackTrace();}double asp_total=0;if(avg_total_sale!=0){asp_total = total_sale/avg_total_sale;}int days_of_stock=0;String days_of_stock_rule= new String();if(latest_snapshot.getMinFbaPrice()==0 || latest_snapshot.getMinMfnPrice()==0){days_of_stock=0;days_of_stock_rule = "Min Prices not Available";}else{percent_diff = getPercentageDifferenceFromMinimumPrice(latest_snapshot);if(latest_snapshot.getSalePrice() <= latest_snapshot.getMinFbaPrice() && latest_snapshot.getSalePrice() <= latest_snapshot.getMinMfnPrice()){if(percent_diff >= 0 && latest_snapshot.getSalePrice()<=asp_total ){days_of_stock = 15;days_of_stock_rule = "Cheapest 15 days";style.setFillForegroundColor(HSSFColor.GREEN.index);///GREEN}else if(percent_diff >= 0 && latest_snapshot.getSalePrice()>asp_total && asp_total!=0){days_of_stock = 7;days_of_stock_rule = "Cheapest Price Increased 7 days";style.setFillForegroundColor(HSSFColor.GREEN.index);///GREEN}else if(percent_diff >= 0 && latest_snapshot.getSalePrice()>asp_total){days_of_stock = 0;days_of_stock_rule = "Cheapest but no Sale";style.setFillForegroundColor(HSSFColor.RED.index);///RED}}else if(latest_snapshot.getSalePrice() > latest_snapshot.getMinFbaPrice() && latest_snapshot.getSalePrice() > latest_snapshot.getMinMfnPrice()){if(percent_diff > -1 && percent_diff < 0){days_of_stock = 4;days_of_stock_rule = "Slightly Expensive (diff 0% to 1 %) 4 days";style.setFillForegroundColor(HSSFColor.YELLOW.index);///YELLOW}else{days_of_stock = 0;days_of_stock_rule = "Non Competitive 0 days";style.setFillForegroundColor(HSSFColor.RED.index);////RED}}else if(latest_snapshot.getSalePrice() < latest_snapshot.getMinFbaPrice() && latest_snapshot.getSalePrice() > latest_snapshot.getMinMfnPrice()){if(((latest_snapshot.getMinFbaPrice() - latest_snapshot.getSalePrice())/latest_snapshot.getSalePrice())*100 >=0 && ((latest_snapshot.getMinMfnPrice() - latest_snapshot.getSalePrice())/latest_snapshot.getSalePrice())*100 <=-2){days_of_stock = 4;days_of_stock_rule = "Cheapest AFN but not MFN 4 days";style.setFillForegroundColor(HSSFColor.YELLOW.index);////YELLOW}else{days_of_stock = 0;days_of_stock_rule = "Non Competitive 0 days";style.setFillForegroundColor(HSSFColor.RED.index);///RED}}}rowhead.createCell((short) 23).setCellValue("VARIANCE-FROM-CHEAPEST");rowhead.createCell((short) 24).setCellValue("DAYS-OF-STOCK-RULE");row.createCell((short) 1).setCellValue(item.getBrand());row.createCell((short) 2).setCellValue(category.getLabel());row.createCell((short) 3).setCellValue(item.getProductGroup());row.createCell((short) 4).setCellValue(item.getModelName()+" " + item.getModelNumber() + " " +item.getColor());if(item.isIsWarehousePreferenceSticky()){row.createCell((short) 5).setCellValue("YES");}else{row.createCell((short) 5).setCellValue("NO");}if(vendor!=null){row.createCell((short) 6).setCellValue(vendor.getName());}else{row.createCell((short) 6).setCellValue("Preferred Vendor Not Set");}if(vip!=null){row.createCell((short) 7).setCellValue(vip.getTransferPrice());row.createCell((short) 8).setCellValue(vip.getNlc());}else{row.createCell((short) 7).setCellValue("Preferred Vendor Not Set");row.createCell((short) 8).setCellValue("Preferred Vendor Not Set");}row.createCell((short) 9).setCellValue(total_days_sale.toString());if(avg_total_sale!=0){row.createCell((short) 10).setCellValue(String.format("%.2f", asp_total));}else{row.createCell((short) 10).setCellValue(0);}row.createCell((short) 11).setCellValue(promotion_days_sale.toString());if(avg_promotion_sale!=0){double asp_promotion = promotion_sale/avg_promotion_sale;row.createCell((short) 12).setCellValue(String.format("%.2f",asp_promotion));}else{row.createCell((short) 12).setCellValue(0);}if(latest_snapshot.getMinFbaPrice()!=0){row.createCell((short) 13).setCellValue(latest_snapshot.getMinFbaPrice());}else{row.createCell((short) 13).setCellValue("Not Available");}if(latest_snapshot.getMinFbaPrice()!=0){row.createCell((short) 14).setCellValue(latest_snapshot.getMinMfnPrice());}else{row.createCell((short) 14).setCellValue("Not Available");}row.createCell((short) 15).setCellValue(latest_snapshot.getSalePrice());long inv_mum=0;if(allItemsAvailability.containsKey(entry.getKey())){ItemInventory itemInventory = allItemsAvailability.get(entry.getKey());Map<Long, Long> itemAvailability = itemInventory.getAvailability();Map<Long, Long> itemReserved = itemInventory.getReserved();Map<Long, Long> itemHeld = itemInventory.getHeld();for (Map.Entry<Long,Long> itementry : itemAvailability.entrySet()) {if(WarehouseIdsForMumbaiLocation.contains(itementry.getKey())){System.out.println(itemAvailability.get(itementry.getKey()) - itemReserved.get(itementry.getKey()) - itemHeld.get(itementry.getKey()));inv_mum = inv_mum + itemAvailability.get(itementry.getKey()) - itemReserved.get(itementry.getKey()) - itemHeld.get(itementry.getKey());}}if(inv_mum!=0){row.createCell((short) 16).setCellValue(inv_mum);}else{row.createCell((short) 16).setCellValue(0);}}else{row.createCell((short) 16).setCellValue(0);}row.createCell((short) 17).setCellValue(String.format("%.2f",total_sale_avg));row.createCell((short) 18).setCellValue(days_of_stock);int total_stock = (int) (total_sale_avg*days_of_stock);row.createCell((short) 19).setCellValue(total_stock);row.createCell((short) 20).setCellValue(availability);long po = total_stock - availability;row.createCell((short) 21).setCellValue(po);if(item.getPreferredVendor()!=0){double amount = total_sale_avg*days_of_stock*vip.getNlc();row.createCell((short) 22).setCellValue(amount);}else{row.createCell((short) 22).setCellValue(0.0);}row.createCell((short) 23).setCellValue(String.format("%.2f", percent_diff)+"%");row.createCell((short) 24).setCellValue(days_of_stock_rule);iterator++;}FileOutputStream fileOut = null;try {fileOut = new FileOutputStream(AMAZON_FBA_SHEET);} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}try {hwb.write(fileOut);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}try {fileOut.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}String emailFromAddress = "build@shop2020.in";String password = "cafe@nes";String[] sendTo = new String[]{ "rajneesh.arora@shop2020.in" };//String[] sendTo = new String[]{ "vikram.raghav@shop2020.in"};String emailSubjectTxt = "FBA Stock Estimation Sheet";try {GmailUtils mailer = new GmailUtils();mailer.sendSSLMessage(sendTo, emailSubjectTxt, "", emailFromAddress, password, AMAZON_FBA_SHEET);}catch (Exception ex) {ex.printStackTrace();}}public static Double getPercentageDifferenceFromMinimumPrice(AmazonFbaSalesSnapshot snapshot){Double minPrice = getMinimumSalePriceOnAmazonFBA(snapshot);return (((minPrice - snapshot.getSalePrice())/snapshot.getSalePrice())*100);}public static Double getMinimumSalePriceOnAmazonFBA(AmazonFbaSalesSnapshot snapshot){if(snapshot.getMinFbaPrice() > snapshot.getMinMfnPrice()){return snapshot.getMinMfnPrice();}else{return snapshot.getMinFbaPrice();}}}