| 10126 |
amar.kumar |
1 |
package in.shop2020.support.utils;
|
|
|
2 |
|
|
|
3 |
import java.io.BufferedWriter;
|
|
|
4 |
import java.io.File;
|
|
|
5 |
import java.io.FileWriter;
|
|
|
6 |
import java.util.ArrayList;
|
|
|
7 |
import java.util.Date;
|
|
|
8 |
import java.util.Hashtable;
|
|
|
9 |
import java.util.List;
|
|
|
10 |
import java.util.Map;
|
|
|
11 |
|
|
|
12 |
import org.apache.commons.lang.StringUtils;
|
|
|
13 |
import org.apache.commons.logging.Log;
|
|
|
14 |
import org.slf4j.Logger;
|
|
|
15 |
import org.slf4j.LoggerFactory;
|
|
|
16 |
|
|
|
17 |
import com.mysql.jdbc.log.LogFactory;
|
|
|
18 |
|
|
|
19 |
import in.shop2020.model.v1.catalog.CatalogService;
|
|
|
20 |
import in.shop2020.model.v1.catalog.Item;
|
|
|
21 |
import in.shop2020.model.v1.inventory.InventoryService;
|
|
|
22 |
import in.shop2020.model.v1.inventory.InventoryType;
|
|
|
23 |
import in.shop2020.model.v1.inventory.ItemInventory;
|
|
|
24 |
import in.shop2020.model.v1.inventory.OOSStatus;
|
|
|
25 |
import in.shop2020.model.v1.inventory.VendorItemPricing;
|
|
|
26 |
import in.shop2020.model.v1.inventory.Warehouse;
|
|
|
27 |
import in.shop2020.model.v1.inventory.WarehouseType;
|
|
|
28 |
import in.shop2020.support.controllers.ProductNotificationsController;
|
|
|
29 |
import in.shop2020.thrift.clients.CatalogClient;
|
|
|
30 |
import in.shop2020.thrift.clients.InventoryClient;
|
|
|
31 |
import in.shop2020.utils.GmailUtils;
|
|
|
32 |
|
|
|
33 |
public class StockSalesReportFetcher {
|
|
|
34 |
private static final int NUMBER_OF_DAYS_SALE = 5;
|
|
|
35 |
private static final String[] mailTo = {"amar.kumar@shop2020.in", "rajveer.singh@shop2020.in"};
|
|
|
36 |
private static final String senderAccountMail = "cnc.center@shop2020.in";
|
|
|
37 |
private static final String senderAccountPswd = "5h0p2o2o";
|
|
|
38 |
|
|
|
39 |
private static Logger logger = LoggerFactory.getLogger(StockSalesReportFetcher.class);
|
|
|
40 |
|
|
|
41 |
private static void createAndMailReport() {
|
|
|
42 |
try {
|
|
|
43 |
CatalogService.Client catalogClient = new CatalogClient().getClient();
|
|
|
44 |
List<Item> itemList = catalogClient.getAllAliveItems();
|
|
|
45 |
|
|
|
46 |
InventoryService.Client inventoryClient = new InventoryClient().getClient();
|
|
|
47 |
Map<Long, ItemInventory> inventoryMap = inventoryClient.getInventorySnapshot(0);
|
|
|
48 |
|
|
|
49 |
inventoryClient = new InventoryClient().getClient();
|
|
|
50 |
List<OOSStatus> oosStatuses = inventoryClient.getOosStatusesForXDays(-1, NUMBER_OF_DAYS_SALE);
|
|
|
51 |
Map<Long, List<OOSStatus>> oosStatusMapByItem = new Hashtable<Long, List<OOSStatus>>(5000);
|
|
|
52 |
for(OOSStatus oosStatus : oosStatuses) {
|
|
|
53 |
List<OOSStatus> oosStatusList;
|
|
|
54 |
Long itemId = oosStatus.getItem_id();
|
|
|
55 |
if(oosStatusMapByItem.containsKey(itemId)) {
|
|
|
56 |
oosStatusList = oosStatusMapByItem.get(itemId);
|
|
|
57 |
oosStatusList.add(oosStatus);
|
|
|
58 |
} else {
|
|
|
59 |
oosStatusList = new ArrayList<OOSStatus>(6);
|
|
|
60 |
oosStatusList.add(oosStatus);
|
|
|
61 |
}
|
|
|
62 |
oosStatusMapByItem.put(itemId, oosStatusList);
|
|
|
63 |
}
|
|
|
64 |
|
|
|
65 |
inventoryClient = new InventoryClient().getClient();
|
|
|
66 |
List<Warehouse> ourGoodWarehouses = inventoryClient.getWarehouses(WarehouseType.OURS, InventoryType.GOOD, 0, 0, 0);
|
|
|
67 |
Map<Long, Warehouse> ourWarehouseMap = new Hashtable<Long, Warehouse>(200);
|
|
|
68 |
for(Warehouse warehouse : ourGoodWarehouses) {
|
|
|
69 |
ourWarehouseMap.put(warehouse.getId(), warehouse);
|
|
|
70 |
}
|
|
|
71 |
|
|
|
72 |
inventoryClient = new InventoryClient().getClient();
|
|
|
73 |
List<VendorItemPricing> vendorItemPricings = inventoryClient.getAllVendorItemPricing(0, 0);
|
|
|
74 |
Map<Long, List<VendorItemPricing>> itemPricingMap = new Hashtable<Long, List<VendorItemPricing>>(5000);
|
|
|
75 |
for(VendorItemPricing vendorItemPricing : vendorItemPricings) {
|
|
|
76 |
List<VendorItemPricing> pricingListForItem;
|
|
|
77 |
Long itemId = vendorItemPricing.getItemId();
|
|
|
78 |
if(itemPricingMap.containsKey(itemId)) {
|
|
|
79 |
pricingListForItem = itemPricingMap.get(itemId);
|
|
|
80 |
pricingListForItem.add(vendorItemPricing);
|
|
|
81 |
} else {
|
|
|
82 |
pricingListForItem = new ArrayList<VendorItemPricing>();
|
|
|
83 |
pricingListForItem.add(vendorItemPricing);
|
|
|
84 |
}
|
|
|
85 |
itemPricingMap.put(itemId, pricingListForItem);
|
|
|
86 |
}
|
|
|
87 |
|
|
|
88 |
String tmpDir = System.getProperty("java.io.tmpdir");
|
|
|
89 |
File file = new File(tmpDir + "/StockSaleReport-" + (new Date()) + ".xls");
|
|
|
90 |
BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(
|
|
|
91 |
file));
|
|
|
92 |
bufferedWriter.write(StringUtils.join(new String[] { "ItemId", "ProductGroup", "Brand",
|
|
|
93 |
"Model Name", "Model Number", "Color", "Stock Qty", "Stock Value", "NOD Stock",
|
|
|
94 |
"Sales History(All-Sources)", "Avg Sales(All-Sources)", "Sales History(Website)",
|
|
|
95 |
"Avg Sales(Website)", "Sales History(Ebay)", "Avg Sales(Ebay)", "Sales History(Snapdeal)",
|
|
|
96 |
"Avg Sales(Snapdeal)", "Sales History(Flipkart)", "Avg Sales(Flipkart)",
|
|
|
97 |
"Sales History(Amazon-MFN)", "Avg Sales(Amazon-MFN)"}, '\t'));
|
|
|
98 |
|
|
|
99 |
Long stockCount = 0L;
|
|
|
100 |
Double stockValue = 0.0;
|
|
|
101 |
for (Item item : itemList) {
|
|
|
102 |
stockCount = 0L;
|
|
|
103 |
stockValue = 0.0;
|
|
|
104 |
Long totalOrderCount = 0L;
|
|
|
105 |
Long websiteOrderCount = 0L;
|
|
|
106 |
Long ebayOrderCount = 0L;
|
|
|
107 |
Long snapdealOrderCount = 0L;
|
|
|
108 |
Long flipkartOrderCount = 0L;
|
|
|
109 |
Long amzn_mfnOrderCount = 0L;
|
|
|
110 |
Double totalAverageSale = 0.0;
|
|
|
111 |
Double websiteAverageSale = 0.0;
|
|
|
112 |
Double ebayAverageSale = 0.0;
|
|
|
113 |
Double snapdealAverageSale = 0.0;
|
|
|
114 |
Double flipkartAverageSale = 0.0;
|
|
|
115 |
Double amzn_mfnAverageSale = 0.0;
|
|
|
116 |
StringBuilder totalSaleHistory = new StringBuilder("");
|
|
|
117 |
StringBuilder websiteSaleHistory = new StringBuilder("");
|
|
|
118 |
StringBuilder ebaySaleHistory = new StringBuilder("");
|
|
|
119 |
StringBuilder snapdealSaleHistory = new StringBuilder("");
|
|
|
120 |
StringBuilder flipkartSaleHistory = new StringBuilder("");
|
|
|
121 |
StringBuilder amzn_mfnSaleHistory = new StringBuilder("");
|
|
|
122 |
|
|
|
123 |
ItemInventory itemInventory = inventoryMap.get(item.getId());
|
|
|
124 |
Map<Long, Long> availabilityMap = itemInventory.getAvailability();
|
|
|
125 |
for (Long warehouseId : availabilityMap.keySet()) {
|
|
|
126 |
if(ourGoodWarehouses.contains(warehouseId)) {
|
|
|
127 |
long currentAvailability = availabilityMap.get(warehouseId);
|
|
|
128 |
stockCount += currentAvailability;
|
|
|
129 |
if(currentAvailability>0) {
|
|
|
130 |
List<VendorItemPricing> pricingList = itemPricingMap.get(item.getId());
|
|
|
131 |
for(VendorItemPricing vendorItemPricing: pricingList) {
|
|
|
132 |
if(vendorItemPricing.getVendorId() == ourWarehouseMap.get(warehouseId).getVendor().getId()) {
|
|
|
133 |
stockValue = stockValue + (vendorItemPricing.getNlc()*currentAvailability);
|
|
|
134 |
}
|
|
|
135 |
}
|
|
|
136 |
}
|
|
|
137 |
}
|
|
|
138 |
}
|
|
|
139 |
for(OOSStatus oosStatus : oosStatusMapByItem.get(item.getId())) {
|
|
|
140 |
switch(oosStatus.getSourceId()) {
|
|
|
141 |
case 0:
|
|
|
142 |
if(oosStatus.isIs_oos()== false) {
|
|
|
143 |
totalOrderCount += oosStatus.getNum_orders();
|
|
|
144 |
totalSaleHistory.append(oosStatus.getNum_orders() + "-");
|
|
|
145 |
} else {
|
|
|
146 |
totalSaleHistory.append("X-");
|
|
|
147 |
}
|
|
|
148 |
break;
|
|
|
149 |
case 1:
|
|
|
150 |
if(oosStatus.isIs_oos()== false) {
|
|
|
151 |
websiteOrderCount += oosStatus.getNum_orders();
|
|
|
152 |
websiteSaleHistory.append(oosStatus.getNum_orders() + "-");
|
|
|
153 |
} else {
|
|
|
154 |
websiteSaleHistory.append("X-");
|
|
|
155 |
}
|
|
|
156 |
break;
|
|
|
157 |
case 3:
|
|
|
158 |
if(oosStatus.isIs_oos()== false) {
|
|
|
159 |
amzn_mfnOrderCount += oosStatus.getNum_orders();
|
|
|
160 |
amzn_mfnSaleHistory.append(oosStatus.getNum_orders() + "-");
|
|
|
161 |
} else {
|
|
|
162 |
amzn_mfnSaleHistory.append("X-");
|
|
|
163 |
}
|
|
|
164 |
break;
|
|
|
165 |
case 6:
|
|
|
166 |
if(oosStatus.isIs_oos()== false) {
|
|
|
167 |
ebayOrderCount += oosStatus.getNum_orders();
|
|
|
168 |
ebaySaleHistory.append(oosStatus.getNum_orders() + "-");
|
|
|
169 |
} else {
|
|
|
170 |
ebaySaleHistory.append("X-");
|
|
|
171 |
}
|
|
|
172 |
break;
|
|
|
173 |
case 7:
|
|
|
174 |
if(oosStatus.isIs_oos()== false) {
|
|
|
175 |
snapdealOrderCount += oosStatus.getNum_orders();
|
|
|
176 |
snapdealSaleHistory.append(oosStatus.getNum_orders() + "-");
|
|
|
177 |
} else {
|
|
|
178 |
snapdealSaleHistory.append("X-");
|
|
|
179 |
}
|
|
|
180 |
break;
|
|
|
181 |
case 8:
|
|
|
182 |
if(oosStatus.isIs_oos()== false) {
|
|
|
183 |
flipkartOrderCount += oosStatus.getNum_orders();
|
|
|
184 |
flipkartSaleHistory.append(oosStatus.getNum_orders() + "-");
|
|
|
185 |
} else {
|
|
|
186 |
flipkartSaleHistory.append("X-");
|
|
|
187 |
}
|
|
|
188 |
break;
|
|
|
189 |
default:
|
|
|
190 |
//do nothing
|
|
|
191 |
break;
|
|
|
192 |
}
|
|
|
193 |
}
|
|
|
194 |
totalAverageSale = (double)totalOrderCount/NUMBER_OF_DAYS_SALE;
|
|
|
195 |
websiteAverageSale = (double)websiteOrderCount/NUMBER_OF_DAYS_SALE;
|
|
|
196 |
ebayAverageSale = (double)ebayOrderCount/NUMBER_OF_DAYS_SALE;
|
|
|
197 |
snapdealAverageSale = (double)snapdealOrderCount/NUMBER_OF_DAYS_SALE;
|
|
|
198 |
flipkartAverageSale = (double)flipkartOrderCount/NUMBER_OF_DAYS_SALE;
|
|
|
199 |
amzn_mfnAverageSale = (double)amzn_mfnOrderCount/NUMBER_OF_DAYS_SALE;
|
|
|
200 |
|
|
|
201 |
|
|
|
202 |
bufferedWriter.write(StringUtils.join(
|
|
|
203 |
new String[] {
|
|
|
204 |
String.valueOf(item.getId()),
|
|
|
205 |
item.getProductGroup(),
|
|
|
206 |
item.getBrand(),
|
|
|
207 |
item.getModelName(),
|
|
|
208 |
item.getModelNumber(),
|
|
|
209 |
item.getColor(),
|
|
|
210 |
stockCount.toString(),
|
|
|
211 |
stockValue.toString(),
|
|
|
212 |
String.valueOf(stockCount/totalAverageSale),
|
|
|
213 |
totalSaleHistory.substring(0, totalSaleHistory.length()-1),
|
|
|
214 |
totalAverageSale.toString(),
|
|
|
215 |
websiteSaleHistory.substring(0, websiteSaleHistory.length()-1),
|
|
|
216 |
websiteAverageSale.toString(),
|
|
|
217 |
ebaySaleHistory.substring(0, ebaySaleHistory.length()-1),
|
|
|
218 |
ebayAverageSale.toString(),
|
|
|
219 |
snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1),
|
|
|
220 |
snapdealAverageSale.toString(),
|
|
|
221 |
flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1),
|
|
|
222 |
flipkartAverageSale.toString(),
|
|
|
223 |
amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1),
|
|
|
224 |
amzn_mfnAverageSale.toString()}, '\t'));
|
|
|
225 |
}
|
|
|
226 |
bufferedWriter.close();
|
|
|
227 |
|
|
|
228 |
List<File> files = new ArrayList<File>(1);
|
|
|
229 |
GmailUtils mailer = new GmailUtils();
|
|
|
230 |
mailer.sendSSLMessage(mailTo, "Stock Sales Report ", "", senderAccountMail, senderAccountPswd, files);
|
|
|
231 |
} catch (Exception e) {
|
|
|
232 |
logger.error("Error in generating/sending Stock Sales Report", e);
|
|
|
233 |
}
|
|
|
234 |
|
|
|
235 |
|
|
|
236 |
}
|
|
|
237 |
|
|
|
238 |
public static void main(String[] args) {
|
|
|
239 |
createAndMailReport();
|
|
|
240 |
}
|
|
|
241 |
|
|
|
242 |
}
|