Rev 13696 | Blame | Compare with Previous | Last modification | View Log | RSS feed
package com.amazonaws.mws.samples;import in.shop2020.model.v1.order.AmazonFbaOrderReturns;import in.shop2020.thrift.clients.CatalogClient;import in.shop2020.thrift.clients.TransactionClient;import in.shop2020.utils.GmailUtils;import java.io.BufferedReader;import java.io.BufferedWriter;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.FileReader;import java.io.FileWriter;import java.io.IOException;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Arrays;import java.util.Calendar;import java.util.Collections;import java.util.Date;import java.util.GregorianCalendar;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Set;import javax.xml.bind.JAXBContext;import javax.xml.bind.JAXBException;import javax.xml.bind.Unmarshaller;import javax.xml.datatype.DatatypeConfigurationException;import javax.xml.datatype.DatatypeFactory;import javax.xml.datatype.XMLGregorianCalendar;import org.apache.commons.io.IOUtils;import org.apache.commons.lang.StringUtils;import com.amazonaws.mws.MarketplaceWebService;import com.amazonaws.mws.MarketplaceWebServiceClient;import com.amazonaws.mws.MarketplaceWebServiceConfig;import com.amazonaws.mws.MarketplaceWebServiceException;import com.amazonaws.mws.model.AmazonEnvelope;import com.amazonaws.mws.model.FulfillmentData;import com.amazonaws.mws.model.GetReportListRequest;import com.amazonaws.mws.model.GetReportRequest;import com.amazonaws.mws.model.IdList;import com.amazonaws.mws.model.Message;import com.amazonaws.mws.model.Order;import com.amazonaws.mws.model.OrderItem;import com.amazonaws.mws.model.RequestReportRequest;public class FetchAmazonOrderReturnsData{static TransactionClient transactionServiceClient = null;static in.shop2020.model.v1.order.TransactionService.Client tclient;static CatalogClient catalogServiceClient = null;static in.shop2020.model.v1.catalog.CatalogService.Client catalogClient;static List<AmazonFbaOrderReturns> amazonFbaOrderReturnsList = new ArrayList<AmazonFbaOrderReturns>();static String emailFromAddress;static String password;static GmailUtils mailer;static String sendTo[];static String sendMobTab[];static String sendExceptMobTab[];static Date startDateForOrder;static Date endDateForOrder;static {emailFromAddress = "build-staging@shop2020.in";password = "shop2020";mailer = new GmailUtils();sendTo = new String[] { "manish.sharma@shop2020.in" };sendMobTab = new String[]{ "rajneesh.arora@shop2020.in","khushal.bhatia@shop2020.in", "manoj.kumar@saholic.com", "chaitnaya.vats@saholic.com","manish.sharma@shop2020.in"};sendExceptMobTab = new String[]{ "rajneesh.arora@shop2020.in","khushal.bhatia@shop2020.in", "chandan.kumar@saholic.com", "manoj.pal@saholic.com","manish.sharma@shop2020.in"};}public static void getReportsFromAmazon(){final String accessKeyId = "AKIAII3SGRXBJDPCHSGQ";final String secretAccessKey = "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";final String appName = "Test";final String appVersion = "1.0";final String merchantId = "AF6E3O0VE0X4D";MarketplaceWebServiceConfig config = new MarketplaceWebServiceConfig();config.setServiceURL("https://mws.amazonservices.in");MarketplaceWebService service = new MarketplaceWebServiceClient(accessKeyId, secretAccessKey, appName, appVersion, config);final IdList marketplaces = new IdList(Arrays.asList("A21TJRUUN4KGV"));RequestReportRequest orderreportrequest = new RequestReportRequest().withMerchant(merchantId).withMarketplaceIdList(marketplaces).withReportType("_GET_XML_ALL_ORDERS_DATA_BY_ORDER_DATE_").withReportOptions("ShowSalesChannel=true");RequestReportRequest returnsreportrequest = new RequestReportRequest().withMerchant(merchantId).withMarketplaceIdList(marketplaces).withReportType("_GET_FBA_FULFILLMENT_CUSTOMER_RETURNS_DATA_").withReportOptions("ShowSalesChannel=true");DatatypeFactory df = null;try {df = DatatypeFactory.newInstance();} catch (DatatypeConfigurationException e) {e.printStackTrace();throw new RuntimeException(e);}Calendar cal = Calendar.getInstance();cal.add(Calendar.DAY_OF_MONTH, -45);long ordertimediff = cal.getTimeInMillis();GregorianCalendar ost = new GregorianCalendar();ost.setTimeInMillis(ordertimediff);//For Order Report StartDateXMLGregorianCalendar orderStartDate = df.newXMLGregorianCalendar(ost);//For Order Report EndDatecal.add(Calendar.DAY_OF_MONTH, 15);ordertimediff = cal.getTimeInMillis();ost = new GregorianCalendar();ost.setTimeInMillis(ordertimediff);XMLGregorianCalendar orderEndDate = df.newXMLGregorianCalendar(ost);System.out.println("Order Start Date " + orderStartDate + "Order End Date " + orderEndDate);orderreportrequest.setStartDate(orderStartDate);orderreportrequest.setEndDate(orderEndDate);Calendar cal1 = Calendar.getInstance();cal1.add(Calendar.DAY_OF_MONTH, -45);long returntimediff = cal1.getTimeInMillis();GregorianCalendar ist = new GregorianCalendar();ist.setTimeInMillis(returntimediff);//For Return Report StartDateXMLGregorianCalendar returnStartDate = df.newXMLGregorianCalendar(ist);//For Return Report StartDateXMLGregorianCalendar returnEndDate = df.newXMLGregorianCalendar(new GregorianCalendar());System.out.println("Return Start Date " + returnStartDate + "Return End Date " + returnEndDate);returnsreportrequest.setStartDate(returnStartDate);returnsreportrequest.setEndDate(returnEndDate);Map<String,String> requestIdreportIdmap;String orderreportrequestId = null;String returnreportrequestId = null;boolean retry=true;int retryCount =0;while(retry && retryCount!=5){if(retryCount==4){String emailFromAddress = "build-staging@shop2020.in";String password = "shop2020";String[] sendTo = new String[]{ "manish.sharma@shop2020.in"};String emailSubjectTxt = "Fetch FBA Order Returns Details failure";try {GmailUtils mailer = new GmailUtils();mailer.sendSSLMessage(sendTo, emailSubjectTxt, "", emailFromAddress, password,"");}catch (Exception ex) {ex.printStackTrace();}System.exit(1);}try {orderreportrequestId = RequestReportSample.invokeRequestReport(service, orderreportrequest);returnreportrequestId = RequestReportSample.invokeRequestReport(service, returnsreportrequest);retry = false;} catch (MarketplaceWebServiceException e) {// TODO Auto-generated catch blocke.printStackTrace();try {Thread.sleep(10*60*1000);retryCount++;} catch (InterruptedException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}}}while(true){GetReportListRequest requestreportlist = new GetReportListRequest();requestreportlist.setMerchant( merchantId );final IdList requestIdList = new IdList(Arrays.asList(orderreportrequestId,returnreportrequestId));requestreportlist.setReportRequestIdList(requestIdList);requestIdreportIdmap = GetReportListSample.invokeGetReportList(service, requestreportlist);if(requestIdreportIdmap.get(orderreportrequestId)!=null && requestIdreportIdmap.get(returnreportrequestId)!=null){GetReportRequest requestorderreport = new GetReportRequest();GetReportRequest requestreturnsreport = new GetReportRequest();requestorderreport.setMerchant( merchantId );requestreturnsreport.setMerchant(merchantId);requestorderreport.setReportId( requestIdreportIdmap.get(orderreportrequestId));requestreturnsreport.setReportId(requestIdreportIdmap.get(returnreportrequestId));OutputStream orderreport=null;OutputStream returnsreport=null;try {orderreport = new FileOutputStream( "/tmp/amazonorderreport.xml" );returnsreport = new FileOutputStream( "/tmp/fbareturnsreport.csv" );} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}requestorderreport.setReportOutputStream(orderreport);requestreturnsreport.setReportOutputStream(returnsreport);GetReportSample.invokeGetReport(service, requestorderreport);GetReportSample.invokeGetReport(service, requestreturnsreport);System.out.println("Order and Return Reports are ready please check");break;} else {System.out.println("Report not ready");try {Thread.sleep(5*60*1000);} catch (InterruptedException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}public static void readOrders(){String toFind = "<AmazonEnvelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:noNamespaceSchemaLocation=\"amzn-envelope.xsd\">";String toReplace = "<AmazonEnvelope xmlns=\"http://mws.amazonaws.com/doc/2009-01-01/\">";File orderReportFile = new File("/tmp/amazonorderreport.xml");String content = "";try {content = IOUtils.toString(new FileInputStream(orderReportFile));} catch (FileNotFoundException e1) {// TODO Auto-generated catch blocke1.printStackTrace();} catch (IOException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}content = content.replaceAll(toFind, toReplace);try {IOUtils.write(content, new FileOutputStream(orderReportFile));} catch (FileNotFoundException e1) {e1.printStackTrace();} catch (IOException e1) {e1.printStackTrace();}JAXBContext jc = null;Unmarshaller unmarshaller = null;AmazonEnvelope amazonOrderData = null;try {jc = JAXBContext.newInstance(AmazonEnvelope.class);unmarshaller = jc.createUnmarshaller();amazonOrderData = (AmazonEnvelope)unmarshaller.unmarshal(orderReportFile);} catch (JAXBException e1) {e1.printStackTrace();}SimpleDateFormat istFormatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");try {Date insertDate = dateFormat.parse(dateFormat.format(new Date()));int orderCount =1;if(amazonOrderData!=null){List<Message> orderMessageList = amazonOrderData.getMessage();System.out.println("Amazon Order List ... "+orderMessageList.size());transactionServiceClient = new TransactionClient();tclient = transactionServiceClient.getClient();for(Message orderMessage : orderMessageList){if(orderCount%500==0){if(!tclient.isAlive()){tclient = transactionServiceClient.getClient();}if(amazonFbaOrderReturnsList!=null && amazonFbaOrderReturnsList.size()>0){tclient.updateAmazonFbaOrdersReturns(amazonFbaOrderReturnsList);Thread.sleep(5*1000);}amazonFbaOrderReturnsList = new ArrayList<AmazonFbaOrderReturns>();orderCount = 1;}Order amazonOrder = orderMessage.getOrder();FulfillmentData orderFullfillmentData = amazonOrder.getFulfillmentData();for(OrderItem orderItem : amazonOrder.getOrderItem()){if("Amazon.in".equalsIgnoreCase(amazonOrder.getSalesChannel()) && "Amazon".equalsIgnoreCase(orderFullfillmentData.getFulfillmentChannel()) && orderItem!=null && "Shipped".equalsIgnoreCase(orderItem.getItemStatus())){Date date = istFormatter.parse(amazonOrder.getPurchaseDate().toString());Date purchaseDate = dateFormat.parse(dateFormat.format(date));long sku = 0l;if(orderItem.getSKU().startsWith("FBB")){if(orderItem.getSKU().contains("_")){sku = Long.parseLong(orderItem.getSKU().replaceAll("FBB","").split("_")[0]);}else{sku = Long.parseLong(orderItem.getSKU().replaceAll("FBB",""));}} else if(orderItem.getSKU().startsWith("FBA")){if(orderItem.getSKU().contains("_")){sku = Long.parseLong(orderItem.getSKU().replaceAll("FBA","").split("_")[0]);}else{sku = Long.parseLong(orderItem.getSKU().replaceAll("FBA",""));}} else if(orderItem.getSKU().startsWith("FBG")){if(orderItem.getSKU().contains("_")){sku = Long.parseLong(orderItem.getSKU().replaceAll("FBG","").split("_")[0]);}else{sku = Long.parseLong(orderItem.getSKU().replaceAll("FBG",""));}} else {continue;}AmazonFbaOrderReturns amazonfbaOrderReturn = new AmazonFbaOrderReturns();amazonfbaOrderReturn.setAmazonOrderId(amazonOrder.getAmazonOrderID());amazonfbaOrderReturn.setInsertionTimestamp(insertDate.getTime());amazonfbaOrderReturn.setSku(sku);amazonfbaOrderReturn.setCreationTimestamp(purchaseDate.getTime());amazonfbaOrderReturn.setShippedQuantity(orderItem.getQuantity());amazonfbaOrderReturn.setSellableReturnQuantity(0);amazonfbaOrderReturn.setNonSellableReturnQuantity(0);amazonFbaOrderReturnsList.add(amazonfbaOrderReturn);}}orderCount++;}}//System.out.println(orderCount);try{if(!tclient.isAlive()){tclient = transactionServiceClient.getClient();}if(amazonFbaOrderReturnsList!=null && amazonFbaOrderReturnsList.size()>0){//amazonFbaOrderReturnsList.tclient.updateAmazonFbaOrdersReturns(amazonFbaOrderReturnsList);}} catch (Exception e){/*try {Thread.sleep(60*60*1000);} catch (InterruptedException e1) {// TODO Auto-generated catch blocke.printStackTrace();}*/}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}private static void updateReturnsInOrderDetails() {try{BufferedReader returnsreportreader = null;try {/*orderreportreader = new CSVReader(new FileReader("/home/amazonorderreport.csv"),'\t');*/returnsreportreader = new BufferedReader(new FileReader("/tmp/fbareturnsreport.csv"));} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}String [] nextLine;String line;SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");Date insertDate = dateFormat.parse(dateFormat.format(new Date()));tclient = transactionServiceClient.getClient();List<AmazonFbaOrderReturns> addedAmazonFbaOrderReturnsList = tclient.getAllAmazonFbaOrderReturnsByCurrentTime(insertDate.getTime());Map<String, AmazonFbaOrderReturns> addedAmazonFbaOrderReturnsMap = new HashMap<String, AmazonFbaOrderReturns>();Map<String, AmazonFbaOrderReturns> returnAmazonFbaOrderReturnsMap = new HashMap<String, AmazonFbaOrderReturns>();for(AmazonFbaOrderReturns orderReturn : addedAmazonFbaOrderReturnsList){addedAmazonFbaOrderReturnsMap.put(orderReturn.getAmazonOrderId(), orderReturn);}File file = new File("/tmp/FBA_Returns_Report.xls");BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(file));int count =1;amazonFbaOrderReturnsList = new ArrayList<AmazonFbaOrderReturns>();while((line = returnsreportreader.readLine())!= null){nextLine = line.split("\t");bufferedWriter.write(StringUtils.join(nextLine, '\t'));bufferedWriter.newLine();if(count!=1){if(addedAmazonFbaOrderReturnsMap.containsKey(nextLine[1])){AmazonFbaOrderReturns orderReturn = null;if(returnAmazonFbaOrderReturnsMap.containsKey(nextLine[1])){orderReturn = returnAmazonFbaOrderReturnsMap.get(nextLine[1]);if("SELLABLE".equalsIgnoreCase(nextLine[8])){orderReturn.setSellableReturnQuantity(orderReturn.getSellableReturnQuantity()+Long.parseLong(nextLine[6]));}else{orderReturn.setNonSellableReturnQuantity(orderReturn.getNonSellableReturnQuantity()+Long.parseLong(nextLine[6]));}returnAmazonFbaOrderReturnsMap.put(orderReturn.getAmazonOrderId(), orderReturn);} else {orderReturn = addedAmazonFbaOrderReturnsMap.get(nextLine[1]);if("SELLABLE".equalsIgnoreCase(nextLine[8])){orderReturn.setSellableReturnQuantity(orderReturn.getSellableReturnQuantity()+Long.parseLong(nextLine[6]));}else{orderReturn.setNonSellableReturnQuantity(orderReturn.getNonSellableReturnQuantity()+Long.parseLong(nextLine[6]));}returnAmazonFbaOrderReturnsMap.put(orderReturn.getAmazonOrderId(), orderReturn);}}}count++;}bufferedWriter.close();for(String key : returnAmazonFbaOrderReturnsMap.keySet()){amazonFbaOrderReturnsList.add(returnAmazonFbaOrderReturnsMap.get(key));}int returnCount =1;List<AmazonFbaOrderReturns> amazonFbaOrderReturnsSubList = new ArrayList<AmazonFbaOrderReturns>();for(int i=0; i<amazonFbaOrderReturnsList.size(); i++){if(returnCount%500==0){if(amazonFbaOrderReturnsSubList!=null && amazonFbaOrderReturnsSubList.size()>0){if(!tclient.isAlive()){tclient = transactionServiceClient.getClient();}if(amazonFbaOrderReturnsSubList!=null && amazonFbaOrderReturnsSubList.size()>0){tclient.updateAmazonFbaOrdersReturns(amazonFbaOrderReturnsSubList);Thread.sleep(5*1000);}amazonFbaOrderReturnsSubList = new ArrayList<AmazonFbaOrderReturns>();returnCount = 1;}}amazonFbaOrderReturnsSubList.add(amazonFbaOrderReturnsList.get(i));returnCount++;}if(!tclient.isAlive()){tclient = transactionServiceClient.getClient();}try{if(amazonFbaOrderReturnsSubList!=null && amazonFbaOrderReturnsSubList.size()>0){tclient.updateAmazonFbaOrdersReturns(amazonFbaOrderReturnsSubList);}} catch(Exception e) {}} catch(Exception e){e.printStackTrace();}}private static void getReturnsReportDataAndSendMail(){try{SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");Date insertDate = dateFormat.parse(dateFormat.format(new Date()));Calendar cal = Calendar.getInstance();cal.add(Calendar.DAY_OF_MONTH, -45);startDateForOrder = new Date(cal.getTimeInMillis());cal.add(Calendar.DAY_OF_MONTH, 15);endDateForOrder = new Date(cal.getTimeInMillis());transactionServiceClient = new TransactionClient();catalogServiceClient= new CatalogClient();catalogClient = catalogServiceClient.getClient();List<in.shop2020.model.v1.catalog.Item> allItems = catalogClient.getAllItems(false);Map<Long, in.shop2020.model.v1.catalog.Item> itemDetailsMap = new HashMap<Long, in.shop2020.model.v1.catalog.Item>();for(in.shop2020.model.v1.catalog.Item item : allItems){itemDetailsMap.put(item.getId(), item);}tclient = transactionServiceClient.getClient();Map<Long, Map<String, Long>> amazonFbaReturnDataSkuMap = tclient.getTotalSaleReturnsFbaSkusCurentTime(insertDate.getTime());List<FbaSkuOrderSaleReturnDetail> fbaSkuMobileTabletReturnDetailList = new ArrayList<FbaSkuOrderSaleReturnDetail>();List<FbaSkuOrderSaleReturnDetail> fbaSkuExceptMobileTabletReturnDetailList = new ArrayList<FbaSkuOrderSaleReturnDetail>();Set<Long> itemIdSet = amazonFbaReturnDataSkuMap.keySet();long totalSaleMobileTablet = 0l;long totalNonSellableReturnsMobileTablet = 0l;long totalSaleExceptMobileTablet = 0l;long totalNonSellableReturnsExceptMobileTablet = 0l;for(Long itemId : itemIdSet){in.shop2020.model.v1.catalog.Item item = itemDetailsMap.get(itemId);long soldQuantity = amazonFbaReturnDataSkuMap.get(itemId).get("Sale");long saleableReturn = amazonFbaReturnDataSkuMap.get(itemId).get("SaleableReturn");long nonsaleableReturn = amazonFbaReturnDataSkuMap.get(itemId).get("NonSaleableReturn");FbaSkuOrderSaleReturnDetail skuDetail = new FbaSkuOrderSaleReturnDetail();skuDetail.setItemId(itemId.longValue());skuDetail.setBrand(item.getBrand());skuDetail.setProductName(item.getModelName()+" "+item.getModelNumber());skuDetail.setProductCategory(item.getProductGroup());skuDetail.setColor(item.getColor());skuDetail.setSoldQuantity(soldQuantity);skuDetail.setReturnQuantity(saleableReturn+nonsaleableReturn);skuDetail.setSellableReturns(saleableReturn);skuDetail.setNonsellableReturns(nonsaleableReturn);double returnPtage = (double)(saleableReturn+nonsaleableReturn)/(double)soldQuantity*100.0;skuDetail.setReturnPercentage(Math.round(returnPtage * 10.0)/10.0);returnPtage = (double)saleableReturn/(double)soldQuantity*100.0;skuDetail.setSellableReturnPercentage(Math.round(returnPtage * 10.0)/10.0);returnPtage = (double)nonsaleableReturn/(double)soldQuantity*100.0;skuDetail.setNonSellableReturnPercentage(Math.round(returnPtage * 10.0)/10.0);if("Handsets".equalsIgnoreCase(item.getProductGroup()) || "Tablets".equalsIgnoreCase(item.getProductGroup()) || "Handset".equalsIgnoreCase(item.getProductGroup())){totalSaleMobileTablet = totalSaleMobileTablet + soldQuantity;totalNonSellableReturnsMobileTablet = totalNonSellableReturnsMobileTablet + nonsaleableReturn;fbaSkuMobileTabletReturnDetailList.add(skuDetail);} else {totalSaleExceptMobileTablet = totalSaleExceptMobileTablet + soldQuantity;totalNonSellableReturnsExceptMobileTablet = totalNonSellableReturnsExceptMobileTablet + nonsaleableReturn;fbaSkuExceptMobileTabletReturnDetailList.add(skuDetail);}}double avgNonsellableReturnsPercentageMobileTablet = (double) totalNonSellableReturnsMobileTablet/(double)totalSaleMobileTablet * 100;avgNonsellableReturnsPercentageMobileTablet = Math.round(avgNonsellableReturnsPercentageMobileTablet * 10.0)/10.0;double avgNonsellableReturnsPercentageExceptMobileTablet = (double) totalNonSellableReturnsExceptMobileTablet/(double)totalSaleExceptMobileTablet * 100;avgNonsellableReturnsPercentageExceptMobileTablet = Math.round(avgNonsellableReturnsPercentageExceptMobileTablet * 10.0)/10.0;Map<String, FbaBrandCategorySaleReturnInfo> fbaBrandCategorySaleReturnsMobTabMap = new HashMap<String, FbaBrandCategorySaleReturnInfo>();Map<String, FbaBrandCategorySaleReturnInfo> fbaBrandCategorySaleReturnsExceptMobTabMap = new HashMap<String, FbaBrandCategorySaleReturnInfo>();List<FbaSkuOrderSaleReturnDetail> mailBodySkuMobileTabletReturnDetailList = new ArrayList<FbaSkuOrderSaleReturnDetail>();List<FbaSkuOrderSaleReturnDetail> mailBodySkuExceptMobileTabletReturnDetailList = new ArrayList<FbaSkuOrderSaleReturnDetail>();for(FbaSkuOrderSaleReturnDetail fba_detail: fbaSkuMobileTabletReturnDetailList){if(fba_detail.getSoldQuantity() >= 25){mailBodySkuMobileTabletReturnDetailList.add(fba_detail);}if(fbaBrandCategorySaleReturnsMobTabMap.containsKey(fba_detail.getBrand()+"-"+fba_detail.getProductCategory())){FbaBrandCategorySaleReturnInfo saleReturnInfo = fbaBrandCategorySaleReturnsMobTabMap.get(fba_detail.getBrand()+"-"+fba_detail.getProductCategory());saleReturnInfo.setSale(saleReturnInfo.getSale()+fba_detail.getSoldQuantity());saleReturnInfo.setNonSellReturn(saleReturnInfo.getNonSellReturn()+fba_detail.getNonsellableReturns());fbaBrandCategorySaleReturnsMobTabMap.put(fba_detail.getBrand()+"-"+fba_detail.getProductCategory(), saleReturnInfo);} else {FbaBrandCategorySaleReturnInfo saleReturnInfo = new FbaBrandCategorySaleReturnInfo();saleReturnInfo.setBrand(fba_detail.getBrand());saleReturnInfo.setCategory(fba_detail.getProductCategory());saleReturnInfo.setSale(fba_detail.getSoldQuantity());saleReturnInfo.setNonSellReturn(fba_detail.getNonsellableReturns());fbaBrandCategorySaleReturnsMobTabMap.put(fba_detail.getBrand()+"-"+fba_detail.getProductCategory(), saleReturnInfo);}}for(FbaSkuOrderSaleReturnDetail fba_detail: fbaSkuExceptMobileTabletReturnDetailList){if(fba_detail.getSoldQuantity() >= 25){mailBodySkuExceptMobileTabletReturnDetailList.add(fba_detail);}if(fbaBrandCategorySaleReturnsExceptMobTabMap.containsKey(fba_detail.getBrand()+"-"+fba_detail.getProductCategory())){FbaBrandCategorySaleReturnInfo saleReturnInfo = fbaBrandCategorySaleReturnsExceptMobTabMap.get(fba_detail.getBrand()+"-"+fba_detail.getProductCategory());saleReturnInfo.setSale(saleReturnInfo.getSale()+fba_detail.getSoldQuantity());saleReturnInfo.setNonSellReturn(saleReturnInfo.getNonSellReturn()+fba_detail.getNonsellableReturns());fbaBrandCategorySaleReturnsExceptMobTabMap.put(fba_detail.getBrand()+"-"+fba_detail.getProductCategory(), saleReturnInfo);} else {FbaBrandCategorySaleReturnInfo saleReturnInfo = new FbaBrandCategorySaleReturnInfo();saleReturnInfo.setBrand(fba_detail.getBrand());saleReturnInfo.setCategory(fba_detail.getProductCategory());saleReturnInfo.setSale(fba_detail.getSoldQuantity());saleReturnInfo.setNonSellReturn(fba_detail.getNonsellableReturns());fbaBrandCategorySaleReturnsExceptMobTabMap.put(fba_detail.getBrand()+"-"+fba_detail.getProductCategory(), saleReturnInfo);}}List<FbaBrandCategorySaleReturnInfo> mailBodyBCSaleReturnMobTabList = new ArrayList<FbaBrandCategorySaleReturnInfo>();List<FbaBrandCategorySaleReturnInfo> mailBodyBCSaleReturnExceptMobTabList = new ArrayList<FbaBrandCategorySaleReturnInfo>();for(String br_category : fbaBrandCategorySaleReturnsMobTabMap.keySet()){FbaBrandCategorySaleReturnInfo saleReturnInfo = fbaBrandCategorySaleReturnsMobTabMap.get(br_category);if(saleReturnInfo.getSale()>=100){double saleNonSellReturnPtage = (double)saleReturnInfo.getNonSellReturn()/(double)saleReturnInfo.getSale()*100;saleReturnInfo.setNonSellReturnPtage(Math.round(saleNonSellReturnPtage*10.0)/10.0);mailBodyBCSaleReturnMobTabList.add(saleReturnInfo);}}for(String br_category : fbaBrandCategorySaleReturnsExceptMobTabMap.keySet()){FbaBrandCategorySaleReturnInfo saleReturnInfo = fbaBrandCategorySaleReturnsExceptMobTabMap.get(br_category);if(saleReturnInfo.getSale()>=100){double saleNonSellReturnPtage = (double)saleReturnInfo.getNonSellReturn()/(double)saleReturnInfo.getSale()*100;saleReturnInfo.setNonSellReturnPtage(Math.round(saleNonSellReturnPtage*10.0)/10.0);mailBodyBCSaleReturnExceptMobTabList.add(saleReturnInfo);}}if (mailBodyBCSaleReturnMobTabList != null&& mailBodyBCSaleReturnMobTabList.size() >= 2) {Collections.sort(mailBodyBCSaleReturnMobTabList,new FbaBrandCategorySaleReturnInfoComparator());}if (mailBodyBCSaleReturnExceptMobTabList != null&& mailBodyBCSaleReturnExceptMobTabList.size() >= 2) {Collections.sort(mailBodyBCSaleReturnExceptMobTabList,new FbaBrandCategorySaleReturnInfoComparator());}if(mailBodySkuMobileTabletReturnDetailList!=null&& mailBodySkuMobileTabletReturnDetailList.size() >= 2){Collections.sort(mailBodySkuMobileTabletReturnDetailList,new FbaSkuOrderSaleReturnDetailComparator());}if(mailBodySkuExceptMobileTabletReturnDetailList!=null&& mailBodySkuExceptMobileTabletReturnDetailList.size() >= 2){Collections.sort(mailBodySkuExceptMobileTabletReturnDetailList,new FbaSkuOrderSaleReturnDetailComparator());}File file = new File("/tmp/MobileTablets-FBAOrdersReturns.xls");BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(file));bufferedWriter.write(StringUtils.join(new String[] { "Sku","Brand", "ProductName", "Category", "Color","Sales", "Return", "Return%", "Sellable Return", "Sellable Return%","Non Sellable Return", "Non Sellable Return%" }, '\t'));for(FbaSkuOrderSaleReturnDetail fbaReturndetail:fbaSkuMobileTabletReturnDetailList){bufferedWriter.newLine();bufferedWriter.write(StringUtils.join(new String[] { String.valueOf(fbaReturndetail.getItemId()),fbaReturndetail.getBrand(), fbaReturndetail.getProductName(), fbaReturndetail.getProductCategory(), fbaReturndetail.getColor(),String.valueOf(fbaReturndetail.getSoldQuantity()), String.valueOf(fbaReturndetail.getSellableReturns()+fbaReturndetail.getNonsellableReturns()),String.valueOf(fbaReturndetail.getReturnPercentage()), String.valueOf(fbaReturndetail.getSellableReturns()), String.valueOf(fbaReturndetail.getSellableReturnPercentage()),String.valueOf(fbaReturndetail.getNonsellableReturns()), String.valueOf(fbaReturndetail.getNonSellableReturnPercentage())}, '\t'));}bufferedWriter.close();file = new File("/tmp/ExceptMobileTablets-FBAOrdersReturns.xls");bufferedWriter = new BufferedWriter(new FileWriter(file));bufferedWriter.write(StringUtils.join(new String[] { "Sku","Brand", "ProductName", "Category", "Color","Sales", "Return", "Return%", "Sellable Return", "Sellable Return%","Non Sellable Return", "Non Sellable Return%" }, '\t'));for(FbaSkuOrderSaleReturnDetail fbaReturndetail:fbaSkuExceptMobileTabletReturnDetailList){bufferedWriter.newLine();bufferedWriter.write(StringUtils.join(new String[] { String.valueOf(fbaReturndetail.getItemId()),fbaReturndetail.getBrand(), fbaReturndetail.getProductName(), fbaReturndetail.getProductCategory(), fbaReturndetail.getColor(),String.valueOf(fbaReturndetail.getSoldQuantity()), String.valueOf(fbaReturndetail.getSellableReturns()+fbaReturndetail.getNonsellableReturns()),String.valueOf(fbaReturndetail.getReturnPercentage()), String.valueOf(fbaReturndetail.getSellableReturns()), String.valueOf(fbaReturndetail.getSellableReturnPercentage()),String.valueOf(fbaReturndetail.getNonsellableReturns()), String.valueOf(fbaReturndetail.getNonSellableReturnPercentage())}, '\t'));}bufferedWriter.close();StringBuffer sb = new StringBuffer();sb.append("<html>" + "<p><b>" +"Date Range for Orders : "+ dateFormat.format(startDateForOrder) + " - "+ dateFormat.format(endDateForOrder) + "</b></p><br>");sb.append("<p><b>" +"Date Range for Returns : "+ dateFormat.format(startDateForOrder) + " - "+ dateFormat.format(insertDate) + "</b></p><br>");sb.append("<table border=\"1\" align=\"center\">"+ "<caption><b>" + "AVERGAGE NON SELLABLE RETURNS" + "</b></caption>"+ "<tr>" + "<td><b>" + "Avg Non Sellable Returns %" + "</b></td>"+ "<td>" + avgNonsellableReturnsPercentageMobileTablet + "</td>"+ "</tr></table><br>");sb.append("<table border=\"1\" align=\"center\">"+"<caption><b>" + "TOP BRAND- CATEGORY HIGH NON SELLABLE RETURN %" + "</b></caption>"+ "<tr>" + "<td><b>" + "Brand" + "</b></td>"+ "<td><b>" + "Category" + "</b></td>"+ "<td><b>" + "Sale" + "</b></td>"+ "<td><b>" + "NonSellable Return" + "</b></td>"+ "<td><b>" + "NonSellable Return %" + "</b></td></tr>");for(FbaBrandCategorySaleReturnInfo fbaBCSaleReturnInfo : mailBodyBCSaleReturnMobTabList){sb.append("<tr>" + "<td>" + fbaBCSaleReturnInfo.getBrand() + "</td>"+ "<td>" + fbaBCSaleReturnInfo.getCategory()+ "</td>"+ "<td>" + fbaBCSaleReturnInfo.getSale() + "</td>"+ "<td>" + fbaBCSaleReturnInfo.getNonSellReturn() + "</td>"+ "<td>" + fbaBCSaleReturnInfo.getNonSellReturnPtage()+ "</td>"+ "</tr>");}sb.append("</table>");sb.append("<table border=\"1\" align=\"center\">"+"<caption><b>" + "TOP TEN SKU WITH HIGH NON SELLABLE RETURNS" + "</b></caption>" +"<tr>" + "<td><b>" + "Item Id" + "</b></td>"+ "<td><b>" + "Product Name" + "</b></td>"+ "<td><b>" + "Sale" + "</b></td>"+ "<td><b>" + "Return" + "</b></td>"+ "<td><b>" + "Return %" + "</b></td>"+ "<td><b>" + "Sellable Return" + "</b></td>"+ "<td><b>" + "Sellable Return %" + "</b></td>"+ "<td><b>" + "Non Sellable Return" + "</b></td>"+ "<td><b>" + "Non Sellable Return %" + "</b></td>");int count =1;for(FbaSkuOrderSaleReturnDetail fbaReturndetail : mailBodySkuMobileTabletReturnDetailList){if(count ==11)break;sb.append("<tr>" + "<td>" + fbaReturndetail.getItemId() + "</td>"+ "<td>" + fbaReturndetail.getBrand()+ " "+ fbaReturndetail.getProductName() + " " + fbaReturndetail.getColor() + "</td>"+ "<td>" + fbaReturndetail.getSoldQuantity() + "</td>"+ "<td>" + fbaReturndetail.getReturnQuantity() + "</td>"+ "<td>" + fbaReturndetail.getReturnPercentage() + "</td>"+ "<td>" + fbaReturndetail.getSellableReturns() + "</td>"+ "<td>" + fbaReturndetail.getSellableReturnPercentage() + "</td>"+ "<td>" + fbaReturndetail.getNonsellableReturns() + "</td>"+ "<td>" + fbaReturndetail.getNonSellableReturnPercentage() + "</td>"+ "</tr>");count++;}sb.append("</table></html>");BufferedWriter out = new BufferedWriter(new FileWriter("/tmp/MobileTablets-FBAOrdersReturns-file.htm"));out.write(sb.toString());out.flush();out.close();sb = new StringBuffer();sb.append("<html>" + "<p><b>" +"Date Range for Orders : "+ dateFormat.format(startDateForOrder) + " - "+ dateFormat.format(endDateForOrder) + "</b></p><br>");sb.append("<p><b>" +"Date Range for Returns : "+ dateFormat.format(startDateForOrder) + " - "+ dateFormat.format(insertDate) + "</b></p><br>");sb.append("<table border=\"1\" align=\"center\">"+ "<caption><b>" + "AVERGAGE NON SELLABLE RETURNS" + "</b></caption>"+ "<tr>" + "<td><b>" + "Avg Non Sellable Returns %" + "</b></td>"+ "<td>" + avgNonsellableReturnsPercentageExceptMobileTablet + "</td>"+ "</tr></table><br>");sb.append("<table border=\"1\" align=\"center\">"+"<caption><b>" + "TOP BRAND- CATEGORY HIGH NON SELLABLE RETURN %" + "</b></caption>"+ "<tr>" + "<td><b>" + "Brand" + "</b></td>"+ "<td><b>" + "Category" + "</b></td>"+ "<td><b>" + "Sale" + "</b></td>"+ "<td><b>" + "NonSellable Return" + "</b></td>"+ "<td><b>" + "NonSellable Return %" + "</b></td></tr>");for(FbaBrandCategorySaleReturnInfo fbaBCSaleReturnInfo : mailBodyBCSaleReturnExceptMobTabList){sb.append("<tr>" + "<td>" + fbaBCSaleReturnInfo.getBrand() + "</td>"+ "<td>" + fbaBCSaleReturnInfo.getCategory()+ "</td>"+ "<td>" + fbaBCSaleReturnInfo.getSale() + "</td>"+ "<td>" + fbaBCSaleReturnInfo.getNonSellReturn() + "</td>"+ "<td>" + fbaBCSaleReturnInfo.getNonSellReturnPtage()+ "</td>"+ "</tr>");}sb.append("</table>");sb.append("<table border=\"1\" align=\"center\">"+"<caption><b>" + "TOP TEN SKU WITH HIGH NON SELLABLE RETURNS" + "</b></caption>" +"<tr>" + "<td><b>" + "Item Id" + "</b></td>"+ "<td><b>" + "Product Name" + "</b></td>"+ "<td><b>" + "Sale" + "</b></td>"+ "<td><b>" + "Return" + "</b></td>"+ "<td><b>" + "Return %" + "</b></td>"+ "<td><b>" + "Sellable Return" + "</b></td>"+ "<td><b>" + "Sellable Return %" + "</b></td>"+ "<td><b>" + "Non Sellable Return" + "</b></td>"+ "<td><b>" + "Non Sellable Return %" + "</b></td>");count =1;for(FbaSkuOrderSaleReturnDetail fbaReturndetail : mailBodySkuExceptMobileTabletReturnDetailList){if(count ==11)break;sb.append("<tr>" + "<td>" + fbaReturndetail.getItemId() + "</td>"+ "<td>" + fbaReturndetail.getBrand()+ " "+ fbaReturndetail.getProductName() + " " + fbaReturndetail.getColor() + "</td>"+ "<td>" + fbaReturndetail.getSoldQuantity() + "</td>"+ "<td>" + fbaReturndetail.getReturnQuantity() + "</td>"+ "<td>" + fbaReturndetail.getReturnPercentage() + "</td>"+ "<td>" + fbaReturndetail.getSellableReturns() + "</td>"+ "<td>" + fbaReturndetail.getSellableReturnPercentage() + "</td>"+ "<td>" + fbaReturndetail.getNonsellableReturns() + "</td>"+ "<td>" + fbaReturndetail.getNonSellableReturnPercentage() + "</td>"+ "</tr>");count++;}sb.append("</table></html>");out = new BufferedWriter(new FileWriter("/tmp/ExceptMobileTablets-FBAOrdersReturns-file.htm"));out.write(sb.toString());out.flush();out.close();List<File> mobTabfiles = new ArrayList<File>();mobTabfiles.add(new File("/tmp/MobileTablets-FBAOrdersReturns.xls"));mobTabfiles.add(new File("/tmp/FBA_Returns_Report.xls"));List<File> exceptMobTabfiles = new ArrayList<File>();exceptMobTabfiles.add(new File("/tmp/ExceptMobileTablets-FBAOrdersReturns.xls"));exceptMobTabfiles.add(new File("/tmp/FBA_Returns_Report.xls"));mailer.sendSSLMessage(sendMobTab, "FBA Sale Return Data - Mobile & Tablets - "+dateFormat.format(insertDate), " ", emailFromAddress, password, mobTabfiles, "/tmp/MobileTablets-FBAOrdersReturns-file.htm");mailer.sendSSLMessage(sendExceptMobTab, "FBA Sale Return Data - Except Mobile & Tablets - "+dateFormat.format(insertDate), " ", emailFromAddress, password, exceptMobTabfiles, "/tmp/ExceptMobileTablets-FBAOrdersReturns-file.htm");} catch(Exception e){e.printStackTrace();System.out.println(e.getMessage());}}public static void main(String args[]){getReportsFromAmazon();readOrders();updateReturnsInOrderDetails();getReturnsReportDataAndSendMail();}}