| 8285 |
kshitij.so |
1 |
package com.amazonaws.mws.samples;
|
|
|
2 |
|
|
|
3 |
import in.shop2020.model.v1.catalog.Amazonlisted;
|
|
|
4 |
import in.shop2020.model.v1.catalog.CatalogServiceException;
|
|
|
5 |
import in.shop2020.model.v1.catalog.Item;
|
|
|
6 |
import in.shop2020.model.v1.inventory.AmazonFbaInventorySnapshot;
|
|
|
7 |
import in.shop2020.model.v1.inventory.InventoryServiceException;
|
|
|
8 |
import in.shop2020.model.v1.inventory.Vendor;
|
|
|
9 |
import in.shop2020.model.v1.inventory.VendorItemPricing;
|
|
|
10 |
import in.shop2020.model.v1.order.AmazonFbaSalesSnapshot;
|
|
|
11 |
import in.shop2020.model.v1.order.TransactionService.Client;
|
|
|
12 |
import in.shop2020.thrift.clients.CatalogClient;
|
|
|
13 |
import in.shop2020.thrift.clients.InventoryClient;
|
|
|
14 |
import in.shop2020.thrift.clients.TransactionClient;
|
|
|
15 |
import in.shop2020.utils.GmailUtils;
|
|
|
16 |
import java.io.FileNotFoundException;
|
|
|
17 |
import java.io.FileOutputStream;
|
|
|
18 |
import java.io.FileReader;
|
|
|
19 |
import java.io.IOException;
|
|
|
20 |
import java.text.ParseException;
|
|
|
21 |
import java.text.SimpleDateFormat;
|
|
|
22 |
import java.util.ArrayList;
|
|
|
23 |
import java.util.Date;
|
|
|
24 |
import java.util.HashMap;
|
|
|
25 |
import java.util.List;
|
|
|
26 |
import java.util.Map;
|
|
|
27 |
import java.util.Map.Entry;
|
|
|
28 |
import java.util.TimeZone;
|
|
|
29 |
|
|
|
30 |
import org.apache.poi.hssf.usermodel.HSSFRow;
|
|
|
31 |
import org.apache.poi.hssf.usermodel.HSSFSheet;
|
|
|
32 |
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
|
|
33 |
import org.apache.thrift.TException;
|
|
|
34 |
|
|
|
35 |
import au.com.bytecode.opencsv.CSVReader;
|
|
|
36 |
|
|
|
37 |
|
|
|
38 |
public class CreateSendFBAStockEstimation {
|
|
|
39 |
final public static String AMAZON_FBA_SHEET = "/home/vikram/Desktop/FBA-Stock-File.xls";
|
|
|
40 |
|
|
|
41 |
public static void main(){
|
|
|
42 |
TransactionClient transactionServiceClient = null;
|
|
|
43 |
try {
|
|
|
44 |
transactionServiceClient = new TransactionClient();
|
|
|
45 |
} catch (Exception e) {
|
|
|
46 |
// TODO Auto-generated catch block
|
|
|
47 |
e.printStackTrace();
|
|
|
48 |
}
|
|
|
49 |
|
|
|
50 |
in.shop2020.model.v1.order.TransactionService.Client transactionClient = transactionServiceClient.getClient();
|
|
|
51 |
List<AmazonFbaSalesSnapshot> salessnapshots = null;
|
|
|
52 |
try {
|
|
|
53 |
salessnapshots = transactionClient.getAmazonFbaSalesSnapshotForDays(5);
|
|
|
54 |
} catch (TException e) {
|
|
|
55 |
// TODO Auto-generated catch block
|
|
|
56 |
e.printStackTrace();
|
|
|
57 |
}
|
|
|
58 |
Map<Long,Integer> itemIdDaysOfStockMap = new HashMap<Long,Integer>();
|
|
|
59 |
Map<Long,List<Integer>> itemId5daysSale = new HashMap<Long,List<Integer>>();
|
|
|
60 |
AmazonFbaSalesSnapshot snapshot;
|
|
|
61 |
for(AmazonFbaSalesSnapshot salessnapshot:salessnapshots){
|
|
|
62 |
try {
|
|
|
63 |
snapshot = transactionClient.getAmazonFbaSalesLatestSnapshotForItem(salessnapshot.getItem_id());
|
|
|
64 |
if (snapshot.getMinFbaPrice()!=0 && snapshot.getMinMfnPrice()!=0){
|
|
|
65 |
if(!itemIdDaysOfStockMap.containsKey(snapshot.getItem_id())){
|
|
|
66 |
if(getPercentageDifferenceFromMinimumPrice(snapshot) >= -3){
|
|
|
67 |
itemIdDaysOfStockMap.put(snapshot.getItem_id(),15);
|
|
|
68 |
}
|
|
|
69 |
else if(getPercentageDifferenceFromMinimumPrice(snapshot) > -3){
|
|
|
70 |
itemIdDaysOfStockMap.put(snapshot.getItem_id(),10);
|
|
|
71 |
}
|
|
|
72 |
else if(getPercentageDifferenceFromMinimumPrice(snapshot) < 1){
|
|
|
73 |
itemIdDaysOfStockMap.put(snapshot.getItem_id(),4);
|
|
|
74 |
}
|
|
|
75 |
else if(snapshot.getSalePrice() > snapshot.getMinMfnPrice() && snapshot.getSalePrice() < snapshot.getMinFbaPrice()){
|
|
|
76 |
if(getPercentageDifferenceFromMinimumPrice(snapshot) <= 2)
|
|
|
77 |
itemIdDaysOfStockMap.put(snapshot.getItem_id(),2);
|
|
|
78 |
}
|
|
|
79 |
else{
|
|
|
80 |
itemIdDaysOfStockMap.put(snapshot.getItem_id(),0);
|
|
|
81 |
}
|
|
|
82 |
}
|
|
|
83 |
}
|
|
|
84 |
else{
|
|
|
85 |
itemIdDaysOfStockMap.put(snapshot.getItem_id(),0);
|
|
|
86 |
}
|
|
|
87 |
if(itemId5daysSale.containsKey(salessnapshot.getItem_id())){
|
|
|
88 |
if(salessnapshot.getAmazonFbaInventory()!=0)
|
|
|
89 |
itemId5daysSale.get(salessnapshot.getItem_id()).add(salessnapshot.getOrderCount());
|
|
|
90 |
else
|
|
|
91 |
itemId5daysSale.get(salessnapshot.getItem_id()).add(-1);
|
|
|
92 |
}
|
|
|
93 |
else{
|
|
|
94 |
List<Integer> last5daysSale = new ArrayList<Integer>();
|
|
|
95 |
if(salessnapshot.getAmazonFbaInventory()!=0){
|
|
|
96 |
last5daysSale.add(salessnapshot.getOrderCount());
|
|
|
97 |
}
|
|
|
98 |
else{
|
|
|
99 |
last5daysSale.add(-1);
|
|
|
100 |
}
|
|
|
101 |
itemId5daysSale.put(salessnapshot.getItem_id(),last5daysSale);
|
|
|
102 |
}
|
|
|
103 |
} catch (TException e) {
|
|
|
104 |
// TODO Auto-generated catch block
|
|
|
105 |
e.printStackTrace();
|
|
|
106 |
}
|
|
|
107 |
System.out.println("ItemID , Last Five Days Sale , Product Name , TP , NLC , STICKY , Preferred Vendor , Qty , Min AFN , Min MFN , SP ,Total Amount");
|
|
|
108 |
CatalogClient catalogServiceClient = null;
|
|
|
109 |
InventoryClient inventoryServiceClient = null;
|
|
|
110 |
try {
|
|
|
111 |
catalogServiceClient = new CatalogClient();
|
|
|
112 |
inventoryServiceClient = new InventoryClient();
|
|
|
113 |
//catalogServiceClient = new CatalogClient("catalog_service_server_host_amazon","catalog_service_server_port");
|
|
|
114 |
} catch (Exception e) {
|
|
|
115 |
// TODO Auto-generated catch block
|
|
|
116 |
e.printStackTrace();
|
|
|
117 |
}
|
|
|
118 |
in.shop2020.model.v1.catalog.CatalogService.Client catalogClient = catalogServiceClient.getClient();
|
|
|
119 |
in.shop2020.model.v1.inventory.InventoryService.Client inventoryClient = inventoryServiceClient.getClient();
|
|
|
120 |
HSSFWorkbook hwb=new HSSFWorkbook();
|
|
|
121 |
HSSFSheet sheet = hwb.createSheet("FBA-STOCK-SHEET");
|
|
|
122 |
HSSFRow rowhead= sheet.createRow((short)0);
|
|
|
123 |
rowhead.createCell((short) 0).setCellValue("ITEM-ID");
|
|
|
124 |
rowhead.createCell((short) 1).setCellValue("LAST 5 DAYS SALE");
|
|
|
125 |
rowhead.createCell((short) 2).setCellValue("PRODUCT-NAME");
|
|
|
126 |
rowhead.createCell((short) 3).setCellValue("TRANSFER-PRICE");
|
|
|
127 |
rowhead.createCell((short) 4).setCellValue("NLC");
|
|
|
128 |
rowhead.createCell((short) 5).setCellValue("IS-STICKY");
|
|
|
129 |
rowhead.createCell((short) 6).setCellValue("PREFERRED-VENDOR");
|
|
|
130 |
rowhead.createCell((short) 7).setCellValue("QUANTITY");
|
|
|
131 |
rowhead.createCell((short) 8).setCellValue("MIN-AFN-PRICE");
|
|
|
132 |
rowhead.createCell((short) 9).setCellValue("MIN-MFN-PRICE");
|
|
|
133 |
rowhead.createCell((short) 10).setCellValue("OUR-PRICE");
|
|
|
134 |
rowhead.createCell((short) 11).setCellValue("TOTAL-AMOUNT");
|
|
|
135 |
int iterator=1;
|
|
|
136 |
for(Map.Entry<Long,List<Integer>> entry : itemId5daysSale.entrySet()){
|
|
|
137 |
int count =1;
|
|
|
138 |
int avg_sale=0;
|
|
|
139 |
int sale_days=0;
|
|
|
140 |
String days_sale = new String();
|
|
|
141 |
for(Integer sale:entry.getValue()){
|
|
|
142 |
if(sale!=-1){
|
|
|
143 |
days_sale.concat(sale.toString());
|
|
|
144 |
avg_sale=avg_sale+sale;
|
|
|
145 |
sale_days++;
|
|
|
146 |
}
|
|
|
147 |
else{
|
|
|
148 |
days_sale.concat("X");
|
|
|
149 |
}
|
|
|
150 |
if(count != entry.getValue().size()){
|
|
|
151 |
days_sale.concat("-");
|
|
|
152 |
}
|
|
|
153 |
}
|
|
|
154 |
avg_sale=avg_sale*sale_days;
|
|
|
155 |
Item item = null;
|
|
|
156 |
VendorItemPricing vip=null;
|
|
|
157 |
Vendor vendor = null;
|
|
|
158 |
try {
|
|
|
159 |
System.out.println("Item ID is " + entry.getKey());
|
|
|
160 |
item = catalogClient.getItem(entry.getKey());
|
|
|
161 |
vip = inventoryClient.getItemPricing(item.getId(),item.getPreferredVendor());
|
|
|
162 |
vendor = inventoryClient.getVendor(item.getPreferredVendor());
|
|
|
163 |
} catch (CatalogServiceException e) {
|
|
|
164 |
// TODO Auto-generated catch block
|
|
|
165 |
e.printStackTrace();
|
|
|
166 |
} catch (TException e) {
|
|
|
167 |
// TODO Auto-generated catch block
|
|
|
168 |
e.printStackTrace();
|
|
|
169 |
} catch (InventoryServiceException e) {
|
|
|
170 |
// TODO Auto-generated catch block
|
|
|
171 |
e.printStackTrace();
|
|
|
172 |
}
|
|
|
173 |
System.out.println(entry.getKey() + "\t "+ days_sale.toString() + "\t" + item.getBrand() + " " + item.getModelName() + item.getModelNumber() +
|
|
|
174 |
item.getColor() + "\t" + vip.getTransferPrice() + "\t" + vip.getNlc() + "\t" + item.isIsWarehousePreferenceSticky() + "\t"
|
|
|
175 |
+ vendor.getName() + avg_sale*itemIdDaysOfStockMap.get(entry.getKey()));
|
|
|
176 |
HSSFRow row= sheet.createRow((short)iterator);
|
|
|
177 |
row.createCell((short) 0).setCellValue(entry.getKey());
|
|
|
178 |
row.createCell((short) 1).setCellValue(days_sale);
|
|
|
179 |
row.createCell((short) 2).setCellValue(item.getBrand() + " " + item.getModelName()+" " + item.getModelNumber() + " " +
|
|
|
180 |
item.getColor());
|
|
|
181 |
row.createCell((short) 3).setCellValue(vip.getTransferPrice());
|
|
|
182 |
row.createCell((short) 4).setCellValue(vip.getNlc());
|
|
|
183 |
if(item.isIsWarehousePreferenceSticky())
|
|
|
184 |
row.createCell((short) 5).setCellValue("YES");
|
|
|
185 |
else
|
|
|
186 |
row.createCell((short) 5).setCellValue("NO");
|
|
|
187 |
row.createCell((short) 6).setCellValue(vendor.getName());
|
|
|
188 |
row.createCell((short) 7).setCellValue(avg_sale*itemIdDaysOfStockMap.get(entry.getKey()));
|
|
|
189 |
AmazonFbaSalesSnapshot latest_snapshot = null;
|
|
|
190 |
try {
|
|
|
191 |
latest_snapshot = transactionClient.getAmazonFbaSalesLatestSnapshotForItem(entry.getKey());
|
|
|
192 |
} catch (TException e) {
|
|
|
193 |
// TODO Auto-generated catch block
|
|
|
194 |
e.printStackTrace();
|
|
|
195 |
}
|
|
|
196 |
row.createCell((short) 8).setCellValue(latest_snapshot.getMinFbaPrice());
|
|
|
197 |
row.createCell((short) 9).setCellValue(latest_snapshot.getMinMfnPrice());
|
|
|
198 |
row.createCell((short) 10).setCellValue(latest_snapshot.getSalePrice());
|
|
|
199 |
row.createCell((short) 11).setCellValue(avg_sale*itemIdDaysOfStockMap.get(entry.getKey())*latest_snapshot.getSalePrice());
|
|
|
200 |
iterator++;
|
|
|
201 |
|
|
|
202 |
}
|
|
|
203 |
FileOutputStream fileOut = null;
|
|
|
204 |
try {
|
|
|
205 |
fileOut = new FileOutputStream(AMAZON_FBA_SHEET);
|
|
|
206 |
} catch (FileNotFoundException e) {
|
|
|
207 |
// TODO Auto-generated catch block
|
|
|
208 |
e.printStackTrace();
|
|
|
209 |
}
|
|
|
210 |
try {
|
|
|
211 |
hwb.write(fileOut);
|
|
|
212 |
} catch (IOException e) {
|
|
|
213 |
// TODO Auto-generated catch block
|
|
|
214 |
e.printStackTrace();
|
|
|
215 |
}
|
|
|
216 |
try {
|
|
|
217 |
fileOut.close();
|
|
|
218 |
} catch (IOException e) {
|
|
|
219 |
// TODO Auto-generated catch block
|
|
|
220 |
e.printStackTrace();
|
|
|
221 |
}
|
|
|
222 |
|
|
|
223 |
}
|
|
|
224 |
String emailFromAddress = "build@shop2020.in";
|
|
|
225 |
String password = "cafe@nes";
|
|
|
226 |
String[] sendTo = new String[]{ "vikram.raghav@shop2020.in", "kshitij.sood@shop2020.in" };
|
|
|
227 |
String emailSubjectTxt = "FBA Stock Estimation Sheet ";
|
|
|
228 |
GmailUtils mailer = new GmailUtils();
|
|
|
229 |
try {
|
|
|
230 |
mailer.sendSSLMessage(sendTo, emailSubjectTxt, "", emailFromAddress, password, "/home/amazon/FBA-Stock-File.xls");
|
|
|
231 |
}
|
|
|
232 |
catch (Exception ex) {
|
|
|
233 |
ex.printStackTrace();
|
|
|
234 |
}
|
|
|
235 |
|
|
|
236 |
}
|
|
|
237 |
|
|
|
238 |
public static Double getPercentageDifferenceFromMinimumPrice(AmazonFbaSalesSnapshot snapshot){
|
|
|
239 |
Double minPrice = getMinimumSalePriceOnAmazonFBA(snapshot);
|
|
|
240 |
return (snapshot.getSalePrice() - minPrice)/snapshot.getSalePrice();
|
|
|
241 |
|
|
|
242 |
}
|
|
|
243 |
|
|
|
244 |
public static Double getMinimumSalePriceOnAmazonFBA(AmazonFbaSalesSnapshot snapshot){
|
|
|
245 |
if(snapshot.getMinFbaPrice() > snapshot.getMinMfnPrice()){
|
|
|
246 |
return snapshot.getMinMfnPrice();
|
|
|
247 |
}
|
|
|
248 |
else{
|
|
|
249 |
return snapshot.getMinFbaPrice();
|
|
|
250 |
}
|
|
|
251 |
}
|
|
|
252 |
|
|
|
253 |
}
|