| 8363 |
vikram.rag |
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 |
|
|
|
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 |
public class FileParser {
|
|
|
38 |
final public static String AMAZON_FBA_SHEET = "/home/vikram/Desktop/FBA-Stock-File.xls";
|
|
|
39 |
public static void main(String... args){
|
|
|
40 |
CSVReader orderreportreader = null;
|
|
|
41 |
CSVReader inventoryhealthreportreader = null;
|
|
|
42 |
try {
|
| 8438 |
vikram.rag |
43 |
orderreportreader = new CSVReader(new FileReader("/home/amazonorderreport.csv"),'\t');
|
|
|
44 |
inventoryhealthreportreader = new CSVReader(new FileReader("/home/inventoryhealthreport.csv"),'\t');
|
| 8363 |
vikram.rag |
45 |
} catch (FileNotFoundException e) {
|
|
|
46 |
// TODO Auto-generated catch block
|
|
|
47 |
e.printStackTrace();
|
|
|
48 |
}
|
|
|
49 |
String [] nextLine;
|
|
|
50 |
try {
|
|
|
51 |
int count =1;
|
|
|
52 |
Map<Date,Map<Long,FbaSalesSnapshot>> orderDateItemIdFbaSaleSnapshotMap = new HashMap<Date,Map<Long,FbaSalesSnapshot>>();
|
|
|
53 |
while ((nextLine = orderreportreader.readNext()) != null) {
|
|
|
54 |
if(count!=1 && nextLine[5].equalsIgnoreCase("Amazon") && nextLine[6].equalsIgnoreCase("Amazon.in") && (nextLine[13].equalsIgnoreCase("Unshipped") || nextLine[13].equalsIgnoreCase("Shipped"))){
|
|
|
55 |
SimpleDateFormat istFormatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
|
|
|
56 |
istFormatter .setLenient(false);
|
|
|
57 |
TimeZone zone= TimeZone.getTimeZone("GMT");
|
|
|
58 |
istFormatter.setTimeZone(zone);
|
|
|
59 |
Date date = istFormatter.parse(nextLine[2]);
|
|
|
60 |
SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
|
|
|
61 |
Date date_key = dateFormat.parse(dateFormat.format(date));
|
|
|
62 |
System.out.println(nextLine[0]+" "+date_key+" "+ nextLine[11] +" " + nextLine[13] + " " + nextLine[14]);
|
|
|
63 |
Long itemid = Long.parseLong(nextLine[11].replaceAll("FBA",""));
|
| 8438 |
vikram.rag |
64 |
|
| 8363 |
vikram.rag |
65 |
Integer qty = new Integer(nextLine[14]);
|
| 8438 |
vikram.rag |
66 |
Float itemSale = null;
|
| 8363 |
vikram.rag |
67 |
if(nextLine[16].length()!=0){
|
|
|
68 |
itemSale = new Float(nextLine[16]);
|
| 8423 |
vikram.rag |
69 |
}
|
|
|
70 |
else{
|
| 8363 |
vikram.rag |
71 |
continue;
|
|
|
72 |
}
|
|
|
73 |
Float itemDiscount;
|
|
|
74 |
if(nextLine[22].length()!=0){
|
|
|
75 |
itemDiscount = new Float(nextLine[22]);
|
|
|
76 |
}
|
|
|
77 |
else{
|
|
|
78 |
itemDiscount = new Float(0);
|
|
|
79 |
}
|
|
|
80 |
if(orderDateItemIdFbaSaleSnapshotMap.containsKey(date_key)){
|
|
|
81 |
if(orderDateItemIdFbaSaleSnapshotMap.get(date_key).containsKey(itemid)){
|
|
|
82 |
FbaSalesSnapshot fbaSalesSnapshot = orderDateItemIdFbaSaleSnapshotMap.get(date_key).get(itemid);
|
|
|
83 |
if(itemDiscount!=0){
|
| 8438 |
vikram.rag |
84 |
fbaSalesSnapshot.setPromotionOrderCount(fbaSalesSnapshot.getPromotionOrderCount()+qty);
|
| 8423 |
vikram.rag |
85 |
fbaSalesSnapshot.setTotalPromotionSale(fbaSalesSnapshot.getTotalPromotionSale() + (itemSale - itemDiscount));
|
| 8363 |
vikram.rag |
86 |
}
|
|
|
87 |
fbaSalesSnapshot.setTotalOrderCount(fbaSalesSnapshot.getTotalOrderCount() + qty);
|
|
|
88 |
fbaSalesSnapshot.setTotalSale(fbaSalesSnapshot.getTotalSale() + itemSale);
|
|
|
89 |
orderDateItemIdFbaSaleSnapshotMap.get(date_key).put(itemid,fbaSalesSnapshot);
|
|
|
90 |
}
|
|
|
91 |
else{
|
|
|
92 |
FbaSalesSnapshot fbaSalesSnapshot = new FbaSalesSnapshot();
|
|
|
93 |
fbaSalesSnapshot.setTotalOrderCount(qty);
|
|
|
94 |
fbaSalesSnapshot.setTotalSale(itemSale);
|
|
|
95 |
if(itemDiscount!=0){
|
|
|
96 |
fbaSalesSnapshot.setPromotionOrderCount(qty);
|
|
|
97 |
fbaSalesSnapshot.setTotalPromotionSale(itemSale - itemDiscount);
|
|
|
98 |
}
|
|
|
99 |
else{
|
|
|
100 |
fbaSalesSnapshot.setPromotionOrderCount(0);
|
|
|
101 |
fbaSalesSnapshot.setTotalPromotionSale((float) 0);
|
|
|
102 |
}
|
|
|
103 |
orderDateItemIdFbaSaleSnapshotMap.get(date_key).put(itemid,fbaSalesSnapshot);
|
|
|
104 |
}
|
|
|
105 |
}
|
|
|
106 |
else{
|
|
|
107 |
Map<Long,FbaSalesSnapshot> ItemIdFbaSaleSnapshotMap = new HashMap<Long,FbaSalesSnapshot>();
|
|
|
108 |
FbaSalesSnapshot fbaSalesSnapshot = new FbaSalesSnapshot();
|
|
|
109 |
fbaSalesSnapshot.setTotalOrderCount(qty);
|
|
|
110 |
fbaSalesSnapshot.setTotalSale(itemSale);
|
|
|
111 |
if(itemDiscount!=0){
|
|
|
112 |
fbaSalesSnapshot.setTotalPromotionSale(itemSale - itemDiscount);
|
|
|
113 |
fbaSalesSnapshot.setPromotionOrderCount(qty);
|
|
|
114 |
}
|
|
|
115 |
else{
|
|
|
116 |
fbaSalesSnapshot.setTotalPromotionSale((float) 0);
|
|
|
117 |
fbaSalesSnapshot.setPromotionOrderCount(0);
|
|
|
118 |
}
|
|
|
119 |
ItemIdFbaSaleSnapshotMap.put(itemid,fbaSalesSnapshot);
|
|
|
120 |
orderDateItemIdFbaSaleSnapshotMap.put(date_key,ItemIdFbaSaleSnapshotMap);
|
|
|
121 |
}
|
|
|
122 |
}
|
|
|
123 |
count++;
|
|
|
124 |
}
|
|
|
125 |
InventoryClient inventoryServiceClient = null;
|
|
|
126 |
TransactionClient transactionServiceClient = null;
|
|
|
127 |
CatalogClient catalogServiceClient = null;
|
|
|
128 |
try {
|
|
|
129 |
inventoryServiceClient = new InventoryClient();
|
|
|
130 |
transactionServiceClient = new TransactionClient();
|
| 8438 |
vikram.rag |
131 |
//catalogServiceClient = new CatalogClient();
|
|
|
132 |
catalogServiceClient = new CatalogClient("catalog_service_server_host_amazon","catalog_service_server_port");
|
| 8363 |
vikram.rag |
133 |
} catch (Exception e) {
|
|
|
134 |
// TODO Auto-generated catch block
|
|
|
135 |
e.printStackTrace();
|
|
|
136 |
}
|
|
|
137 |
in.shop2020.model.v1.inventory.InventoryService.Client inventoryClient = inventoryServiceClient.getClient();
|
|
|
138 |
in.shop2020.model.v1.order.TransactionService.Client transactionClient = transactionServiceClient.getClient();
|
|
|
139 |
in.shop2020.model.v1.catalog.CatalogService.Client catalogClient = catalogServiceClient.getClient();
|
|
|
140 |
SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
|
|
|
141 |
Date date_today = dateFormat.parse(dateFormat.format(new Date()));
|
|
|
142 |
List<AmazonFbaInventorySnapshot> nonzeroFbaInventorySnapshotlist = inventoryClient.getAllAmazonFbaItemInventory();
|
|
|
143 |
if(nonzeroFbaInventorySnapshotlist!=null){
|
|
|
144 |
for(AmazonFbaInventorySnapshot amazonFbaInventory:nonzeroFbaInventorySnapshotlist){
|
|
|
145 |
if(orderDateItemIdFbaSaleSnapshotMap.containsKey(date_today) ){
|
|
|
146 |
if(!orderDateItemIdFbaSaleSnapshotMap.get(date_today).containsKey(amazonFbaInventory.getItem_id())){
|
|
|
147 |
Map<Long,FbaSalesSnapshot> ItemIdFbaSaleSnapshotMap = new HashMap<Long,FbaSalesSnapshot>();
|
|
|
148 |
FbaSalesSnapshot fbaSalesSnapshot = new FbaSalesSnapshot();
|
|
|
149 |
fbaSalesSnapshot.setTotalOrderCount(0);
|
|
|
150 |
fbaSalesSnapshot.setPromotionOrderCount(0);
|
|
|
151 |
fbaSalesSnapshot.setTotalPromotionSale((float) 0);
|
|
|
152 |
fbaSalesSnapshot.setTotalSale((float) 0);
|
|
|
153 |
ItemIdFbaSaleSnapshotMap.put(amazonFbaInventory.getItem_id(),fbaSalesSnapshot);
|
|
|
154 |
orderDateItemIdFbaSaleSnapshotMap.get(date_today).put(amazonFbaInventory.getItem_id(),fbaSalesSnapshot);
|
|
|
155 |
}
|
|
|
156 |
|
|
|
157 |
}
|
|
|
158 |
else{
|
|
|
159 |
Map<Long,FbaSalesSnapshot> ItemIdFbaSaleSnapshotMap = new HashMap<Long,FbaSalesSnapshot>();
|
|
|
160 |
FbaSalesSnapshot fbaSalesSnapshot = new FbaSalesSnapshot();
|
|
|
161 |
fbaSalesSnapshot.setTotalOrderCount(0);
|
|
|
162 |
fbaSalesSnapshot.setPromotionOrderCount(0);
|
|
|
163 |
fbaSalesSnapshot.setTotalPromotionSale((float) 0);
|
|
|
164 |
fbaSalesSnapshot.setTotalSale((float) 0);
|
|
|
165 |
ItemIdFbaSaleSnapshotMap.put(amazonFbaInventory.getItem_id(),fbaSalesSnapshot);
|
|
|
166 |
orderDateItemIdFbaSaleSnapshotMap.put(date_today,ItemIdFbaSaleSnapshotMap);
|
|
|
167 |
}
|
|
|
168 |
}
|
|
|
169 |
}
|
|
|
170 |
else{
|
|
|
171 |
System.out.println("No inventory in FBA");
|
|
|
172 |
}
|
| 8447 |
vikram.rag |
173 |
Map<Long,Double> itemIdOurPriceMap = new HashMap<Long,Double>();
|
| 8363 |
vikram.rag |
174 |
Map<Long,Double> itemIdSalePriceMap = new HashMap<Long,Double>();
|
|
|
175 |
Map<Long,Double> itemIdminFBAPriceMap = new HashMap<Long,Double>();
|
|
|
176 |
Map<Long,Double> itemIdminMFNPriceMap = new HashMap<Long,Double>();
|
|
|
177 |
count=1;
|
|
|
178 |
while ((nextLine = inventoryhealthreportreader.readNext()) != null ) {
|
|
|
179 |
if(count!=1){
|
|
|
180 |
//System.out.println(nextLine[1] +" "+ nextLine[31] +" " + nextLine[32] + " " + nextLine[34]);
|
|
|
181 |
Long item_id = Long.parseLong(nextLine[1].replaceAll("FBA",""));
|
| 8451 |
vikram.rag |
182 |
Double ourPrice = null;
|
|
|
183 |
Double minFBAPrice= null;
|
|
|
184 |
Double minMFNPrice= null;
|
|
|
185 |
Double salePrice= null;
|
|
|
186 |
|
|
|
187 |
if(nextLine[30].length() >0){
|
|
|
188 |
ourPrice = Double.parseDouble(nextLine[30]);
|
|
|
189 |
itemIdOurPriceMap.put(item_id,ourPrice);
|
|
|
190 |
}
|
|
|
191 |
if(nextLine[31].length() >0){
|
|
|
192 |
salePrice = Double.parseDouble(nextLine[31]);
|
|
|
193 |
itemIdSalePriceMap.put(item_id,salePrice);
|
|
|
194 |
}
|
|
|
195 |
if(nextLine[32].length() >0){
|
|
|
196 |
minFBAPrice = Double.parseDouble(nextLine[32]);
|
|
|
197 |
itemIdminFBAPriceMap.put(item_id,minFBAPrice);
|
|
|
198 |
}
|
|
|
199 |
if(nextLine[32].length() >0){
|
|
|
200 |
minMFNPrice = Double.parseDouble(nextLine[34]);
|
|
|
201 |
itemIdminMFNPriceMap.put(item_id,minMFNPrice);
|
|
|
202 |
}
|
| 8363 |
vikram.rag |
203 |
}
|
|
|
204 |
count++;
|
|
|
205 |
}
|
|
|
206 |
boolean oos;
|
|
|
207 |
for (Entry<Date, Map<Long, FbaSalesSnapshot>> entry : orderDateItemIdFbaSaleSnapshotMap.entrySet()){
|
|
|
208 |
Date orderDate = entry.getKey();
|
|
|
209 |
for(Entry<Long, FbaSalesSnapshot> entry1 :entry.getValue().entrySet()){
|
|
|
210 |
System.out.println("Item ID is " + entry1.getKey());
|
|
|
211 |
Long inventory = inventoryClient.getAmazonFbaItemInventory(entry1.getKey());
|
|
|
212 |
|
|
|
213 |
if(inventory==0 && entry1.getValue().getTotalOrderCount()==0){
|
|
|
214 |
oos=true;
|
|
|
215 |
}
|
|
|
216 |
else{
|
|
|
217 |
oos=false;
|
|
|
218 |
}
|
|
|
219 |
Long item_id = entry1.getKey();
|
| 8438 |
vikram.rag |
220 |
System.out.println(orderDate +","+entry1.getKey()+","+entry1.getValue()+","+ inventory +","+ oos+","+itemIdSalePriceMap.get(item_id)+","+itemIdminFBAPriceMap.get(item_id)+","+itemIdminMFNPriceMap.get(item_id));
|
| 8363 |
vikram.rag |
221 |
AmazonFbaSalesSnapshot amazonfbasalessnapshot = new AmazonFbaSalesSnapshot();
|
|
|
222 |
amazonfbasalessnapshot.setDateOfSale(orderDate.getTime());
|
|
|
223 |
amazonfbasalessnapshot.setItem_id(entry1.getKey());
|
|
|
224 |
amazonfbasalessnapshot.setTotalOrderCount(entry1.getValue().getPromotionOrderCount());
|
|
|
225 |
amazonfbasalessnapshot.setTotalOrderCount(entry1.getValue().getTotalOrderCount());
|
|
|
226 |
amazonfbasalessnapshot.setTotalSale(entry1.getValue().getTotalSale());
|
|
|
227 |
amazonfbasalessnapshot.setPromotionSale(entry1.getValue().getTotalPromotionSale());
|
|
|
228 |
amazonfbasalessnapshot.setIsOutOfStock(oos);
|
|
|
229 |
Amazonlisted amazon_item=null;
|
|
|
230 |
if(catalogClient.getAmazonItemDetails(item_id)!=null)
|
|
|
231 |
amazon_item = catalogClient.getAmazonItemDetails(item_id);
|
|
|
232 |
else
|
|
|
233 |
continue;
|
|
|
234 |
if(itemIdSalePriceMap.containsKey(item_id) && itemIdSalePriceMap.get(item_id)!=0){
|
|
|
235 |
amazonfbasalessnapshot.setSalePrice(itemIdSalePriceMap.get(item_id));
|
|
|
236 |
}
|
|
|
237 |
else{
|
|
|
238 |
amazonfbasalessnapshot.setSalePrice(amazon_item.getFbaPrice());
|
|
|
239 |
}
|
|
|
240 |
if(itemIdminMFNPriceMap.containsKey(item_id) && itemIdminMFNPriceMap.get(item_id)!=0){
|
|
|
241 |
amazonfbasalessnapshot.setMinFbaPrice(itemIdminMFNPriceMap.get(item_id));
|
|
|
242 |
}
|
|
|
243 |
else{
|
|
|
244 |
amazonfbasalessnapshot.setMinFbaPrice(0.0);
|
|
|
245 |
}
|
|
|
246 |
if(itemIdminMFNPriceMap.containsKey(item_id) && itemIdminMFNPriceMap.get(item_id)!=0){
|
|
|
247 |
amazonfbasalessnapshot.setMinMfnPrice(itemIdminMFNPriceMap.get(item_id));
|
|
|
248 |
}
|
|
|
249 |
else{
|
|
|
250 |
amazonfbasalessnapshot.setMinMfnPrice(0.0);
|
|
|
251 |
}
|
| 8452 |
vikram.rag |
252 |
if(itemIdOurPriceMap.containsKey(item_id)){
|
| 8447 |
vikram.rag |
253 |
amazonfbasalessnapshot.setOurPrice(itemIdOurPriceMap.get(item_id));
|
|
|
254 |
}
|
|
|
255 |
else{
|
| 8453 |
vikram.rag |
256 |
amazonfbasalessnapshot.setOurPrice(0);
|
| 8447 |
vikram.rag |
257 |
}
|
| 8363 |
vikram.rag |
258 |
amazonfbasalessnapshot.setAmazonFbaInventory(inventory);
|
|
|
259 |
transactionClient.addOrUpdateAmazonFbaSalesSnapshot(amazonfbasalessnapshot);
|
|
|
260 |
}
|
|
|
261 |
}
|
|
|
262 |
} catch (IOException e) {
|
|
|
263 |
// TODO Auto-generated catch block
|
|
|
264 |
e.printStackTrace();
|
|
|
265 |
} catch (ParseException e) {
|
|
|
266 |
// TODO Auto-generated catch block
|
|
|
267 |
e.printStackTrace();
|
|
|
268 |
} catch (TException e) {
|
|
|
269 |
// TODO Auto-generated catch block
|
|
|
270 |
e.printStackTrace();
|
|
|
271 |
}
|
|
|
272 |
}
|
|
|
273 |
|
|
|
274 |
|
|
|
275 |
public static Double getPercentageDifferenceFromMinimumPrice(AmazonFbaSalesSnapshot snapshot){
|
|
|
276 |
Double minPrice = getMinimumSalePriceOnAmazonFBA(snapshot);
|
|
|
277 |
return (snapshot.getSalePrice() - minPrice)/snapshot.getSalePrice();
|
|
|
278 |
|
|
|
279 |
}
|
|
|
280 |
|
|
|
281 |
public static Double getMinimumSalePriceOnAmazonFBA(AmazonFbaSalesSnapshot snapshot){
|
|
|
282 |
if(snapshot.getMinFbaPrice() > snapshot.getMinMfnPrice()){
|
|
|
283 |
return snapshot.getMinMfnPrice();
|
|
|
284 |
}
|
|
|
285 |
else{
|
|
|
286 |
return snapshot.getMinFbaPrice();
|
|
|
287 |
}
|
|
|
288 |
}
|
|
|
289 |
|
|
|
290 |
}
|