Subversion Repositories SmartDukaan

Rev

Rev 10353 | Rev 12098 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
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;
10352 amar.kumar 20 
import in.shop2020.model.v1.catalog.Category;
10126 amar.kumar 21 
import in.shop2020.model.v1.catalog.Item;
22 
import in.shop2020.model.v1.inventory.InventoryService;
23 
import in.shop2020.model.v1.inventory.InventoryType;
24 
import in.shop2020.model.v1.inventory.ItemInventory;
25 
import in.shop2020.model.v1.inventory.OOSStatus;
26 
import in.shop2020.model.v1.inventory.VendorItemPricing;
27 
import in.shop2020.model.v1.inventory.Warehouse;
28 
import in.shop2020.model.v1.inventory.WarehouseType;
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;
10253 manish.sha 35 
	private static final String[] mailTo = {"manish.sharma@shop2020.in", "rajveer.singh@shop2020.in", "chaitnaya.vats@saholic.com",
10134 amar.kumar 36 
			"manoj.kumar@saholic.com", "chandan.kumar@saholic.com", "khushal.bhatia@saholic.com", "rajneesh.arora@saholic.com"};
10126 amar.kumar 37 
	private static final String senderAccountMail = "cnc.center@shop2020.in";
38 
	private static final String senderAccountPswd = "5h0p2o2o";
39 
 
40 
	private static Logger logger = LoggerFactory.getLogger(StockSalesReportFetcher.class);
41 
 
42 
	private static void createAndMailReport() {
43 
		try {
44 
			CatalogService.Client catalogClient = new CatalogClient().getClient();
45 
			List<Item> itemList = catalogClient.getAllAliveItems();
46 
 
11975 manish.sha 47 
			if(!catalogClient.isAlive()){
48 
				catalogClient = new CatalogClient().getClient();
49 
			}
10352 amar.kumar 50 
			List<Category> allCategories = catalogClient.getAllCategories();
51 
			Map<Long, String> categoryIdLabelMap = new Hashtable<Long, String>();
52 
			for(Category category : allCategories) {
53 
				categoryIdLabelMap.put(category.getId(), category.getLabel());
54 
			}
55 
 
10126 amar.kumar 56 
			InventoryService.Client inventoryClient = new InventoryClient().getClient();
57 
			Map<Long, ItemInventory> inventoryMap = inventoryClient.getInventorySnapshot(0);
58 
 
11975 manish.sha 59 
			if(!inventoryClient.isAlive()){
60 
				inventoryClient = new InventoryClient().getClient();
61 
			}
10126 amar.kumar 62 
			List<OOSStatus> oosStatuses = inventoryClient.getOosStatusesForXDays(-1, NUMBER_OF_DAYS_SALE);
63 
			Map<Long, List<OOSStatus>> oosStatusMapByItem = new Hashtable<Long, List<OOSStatus>>(5000);
64 
			for(OOSStatus oosStatus : oosStatuses) {
65 
				List<OOSStatus> oosStatusList;
66 
				Long itemId = oosStatus.getItem_id();
67 
				if(oosStatusMapByItem.containsKey(itemId)) {
68 
					oosStatusList = oosStatusMapByItem.get(itemId);
69 
					oosStatusList.add(oosStatus);
70 
				} else {
71 
					oosStatusList = new ArrayList<OOSStatus>(6);
72 
					oosStatusList.add(oosStatus);
73 
				}
74 
				oosStatusMapByItem.put(itemId, oosStatusList);
75 
			}
76 
 
11975 manish.sha 77 
			if(!inventoryClient.isAlive()){
78 
				inventoryClient = new InventoryClient().getClient();
79 
			}
10126 amar.kumar 80 
			List<Warehouse>  ourGoodWarehouses = inventoryClient.getWarehouses(WarehouseType.OURS, InventoryType.GOOD, 0, 0, 0);
81 
			Map<Long, Warehouse> ourWarehouseMap = new Hashtable<Long, Warehouse>(200);
82 
			for(Warehouse warehouse : ourGoodWarehouses) {
10133 amar.kumar 83 
				//System.out.println("Warehouse = " + warehouse.getId());
10126 amar.kumar 84 
				ourWarehouseMap.put(warehouse.getId(), warehouse);
85 
			}
86 
 
11975 manish.sha 87 
			if(!inventoryClient.isAlive()){
88 
				inventoryClient = new InventoryClient().getClient();
89 
			}
10126 amar.kumar 90 
			List<VendorItemPricing> vendorItemPricings = inventoryClient.getAllVendorItemPricing(0, 0);
91 
			Map<Long, List<VendorItemPricing>> itemPricingMap = new Hashtable<Long, List<VendorItemPricing>>(5000);
92 
			for(VendorItemPricing vendorItemPricing : vendorItemPricings) {
93 
				List<VendorItemPricing> pricingListForItem;
94 
				Long itemId = vendorItemPricing.getItemId();
95 
				if(itemPricingMap.containsKey(itemId)) {
96 
					pricingListForItem = itemPricingMap.get(itemId);
97 
					pricingListForItem.add(vendorItemPricing);
98 
				} else {
99 
					pricingListForItem = new ArrayList<VendorItemPricing>();
100 
					pricingListForItem.add(vendorItemPricing);
101 
				}
102 
				itemPricingMap.put(itemId, pricingListForItem);
103 
			}
104 
 
105 
			String tmpDir = System.getProperty("java.io.tmpdir");
10352 amar.kumar 106 
            File nonMovingFile = new File(tmpDir + "/StockSaleReport-NonMoving" + (new Date()) + ".xls");
107 
            File slowMovingFile = new File(tmpDir + "/StockSaleReport-SlowMoving" + (new Date()) + ".xls");
108 
            File movingFile = new File(tmpDir + "/StockSaleReport-Moving" + (new Date()) + ".xls");
109 
            BufferedWriter nonMovingBufferedWriter = new BufferedWriter(new FileWriter(
110 
            		nonMovingFile));
111 
            BufferedWriter slowMovingBufferedWriter = new BufferedWriter(new FileWriter(
112 
            		slowMovingFile));
113 
            BufferedWriter movingBufferedWriter = new BufferedWriter(new FileWriter(
114 
            		movingFile));
115 
            nonMovingBufferedWriter.write(StringUtils.join(new String[] { "ItemId", "Category", "ProductGroup",
10353 amar.kumar 116 
            		"Brand", "Model Name", "Model Number", "Color", "Product", "Stock Qty", "Stock Value", "NOD Stock",
10126 amar.kumar 117 
                    "Sales History(All-Sources)", "Avg Sales(All-Sources)", "Sales History(Website)",
118 
                    "Avg Sales(Website)", "Sales History(Ebay)", "Avg Sales(Ebay)", "Sales History(Snapdeal)",
119 
                    "Avg Sales(Snapdeal)", "Sales History(Flipkart)", "Avg Sales(Flipkart)",
120 
                    "Sales History(Amazon-MFN)", "Avg Sales(Amazon-MFN)"}, '\t'));
10352 amar.kumar 121 
            slowMovingBufferedWriter.write(StringUtils.join(new String[] { "ItemId", "Category", "ProductGroup",
10353 amar.kumar 122 
            		"Brand", "Model Name", "Model Number", "Color", "Product", "Stock Qty", "Stock Value", "NOD Stock",
10352 amar.kumar 123 
                    "Sales History(All-Sources)", "Avg Sales(All-Sources)", "Sales History(Website)",
124 
                    "Avg Sales(Website)", "Sales History(Ebay)", "Avg Sales(Ebay)", "Sales History(Snapdeal)",
125 
                    "Avg Sales(Snapdeal)", "Sales History(Flipkart)", "Avg Sales(Flipkart)",
126 
                    "Sales History(Amazon-MFN)", "Avg Sales(Amazon-MFN)"}, '\t'));
127 
            movingBufferedWriter.write(StringUtils.join(new String[] { "ItemId", "Category", "ProductGroup",
10353 amar.kumar 128 
            		"Brand", "Model Name", "Model Number", "Color", "Product", "Stock Qty", "Stock Value", "NOD Stock",
10352 amar.kumar 129 
                    "Sales History(All-Sources)", "Avg Sales(All-Sources)", "Sales History(Website)",
130 
                    "Avg Sales(Website)", "Sales History(Ebay)", "Avg Sales(Ebay)", "Sales History(Snapdeal)",
131 
                    "Avg Sales(Snapdeal)", "Sales History(Flipkart)", "Avg Sales(Flipkart)",
132 
                    "Sales History(Amazon-MFN)", "Avg Sales(Amazon-MFN)"}, '\t'));
10126 amar.kumar 133 
 
10129 amar.kumar 134 
            Long stockCount;
135 
            Double stockValue;
10126 amar.kumar 136 
            for (Item item : itemList) {
137 
            	stockCount = 0L;
138 
            	stockValue = 0.0;
139 
            	Long totalOrderCount    = 0L;
140 
            	Long websiteOrderCount  = 0L;
141 
            	Long ebayOrderCount     = 0L;
142 
            	Long snapdealOrderCount = 0L;
143 
            	Long flipkartOrderCount = 0L;
144 
            	Long amzn_mfnOrderCount = 0L;
10352 amar.kumar 145 
            	Long totalInStockDays    = 0L;
146 
            	Long websiteInStockDays  = 0L;
147 
            	Long ebayInStockDays     = 0L;
148 
            	Long snapdealInStockDays = 0L;
149 
            	Long flipkartInStockDays = 0L;
150 
            	Long amzn_mfnInStockDays = 0L;
10128 amar.kumar 151 
            	Double websiteAverageSale  = 0.0;
10126 amar.kumar 152 
            	Double totalAverageSale = 0.0;
153 
            	Double ebayAverageSale     = 0.0;
154 
            	Double snapdealAverageSale = 0.0;
155 
            	Double flipkartAverageSale = 0.0;
156 
            	Double amzn_mfnAverageSale  = 0.0;
157 
            	StringBuilder totalSaleHistory    = new StringBuilder("");
158 
            	StringBuilder websiteSaleHistory  = new StringBuilder("");
159 
            	StringBuilder ebaySaleHistory     = new StringBuilder("");
160 
            	StringBuilder snapdealSaleHistory = new StringBuilder("");
161 
            	StringBuilder flipkartSaleHistory = new StringBuilder("");
162 
            	StringBuilder amzn_mfnSaleHistory = new StringBuilder("");
163 
 
164 
            	ItemInventory itemInventory = inventoryMap.get(item.getId());
10127 amar.kumar 165 
            	Map<Long, Long> availabilityMap;
166 
            	if(itemInventory!= null) {
10133 amar.kumar 167 
            		//System.out.println("itemId =" + item.getId());
10127 amar.kumar 168 
	            	availabilityMap = itemInventory.getAvailability();
169 
	            	for (Long warehouseId : availabilityMap.keySet()) {
10131 amar.kumar 170 
	            		if(ourWarehouseMap.containsKey(warehouseId)) {
10127 amar.kumar 171 
	            			long currentAvailability = availabilityMap.get(warehouseId);
10133 amar.kumar 172 
	            			//System.out.println("itemId =" + item.getId() + " availability = "+ currentAvailability);
10127 amar.kumar 173 
	            			stockCount += currentAvailability;
174 
	                    	if(currentAvailability>0) {
175 
	                    		List<VendorItemPricing> pricingList = itemPricingMap.get(item.getId());
176 
	                    		for(VendorItemPricing vendorItemPricing: pricingList) {
177 
	                    			if(vendorItemPricing.getVendorId() == ourWarehouseMap.get(warehouseId).getVendor().getId()) {
178 
	                    				stockValue = stockValue + (vendorItemPricing.getNlc()*currentAvailability);
179 
	                    			}
180 
	                    		}
181 
	                    	}
182 
	            		}
183 
	            	}
10126 amar.kumar 184 
            	}
10352 amar.kumar 185 
            	if(stockCount<=0) {
186 
            		continue;
187 
            	}
10126 amar.kumar 188 
            	for(OOSStatus oosStatus : oosStatusMapByItem.get(item.getId())) {
189 
            		switch(oosStatus.getSourceId()) {
190 
            			case 0:
191 
            				if(oosStatus.isIs_oos()== false) {
10352 amar.kumar 192 
            					totalInStockDays++;
10126 amar.kumar 193 
            					totalOrderCount += oosStatus.getNum_orders();
194 
            					totalSaleHistory.append(oosStatus.getNum_orders() + "-");
195 
            				} else {
196 
            					totalSaleHistory.append("X-");
197 
            				}
198 
            				break;
199 
            			case 1:
200 
            				if(oosStatus.isIs_oos()== false) {
10352 amar.kumar 201 
            					websiteInStockDays++;
10126 amar.kumar 202 
            					websiteOrderCount += oosStatus.getNum_orders();
203 
            					websiteSaleHistory.append(oosStatus.getNum_orders() + "-");
204 
            				} else {
205 
            					websiteSaleHistory.append("X-");
206 
            				}
207 
            				break;
208 
            			case 3:
209 
            				if(oosStatus.isIs_oos()== false) {
10352 amar.kumar 210 
            					amzn_mfnInStockDays++;
10126 amar.kumar 211 
            					amzn_mfnOrderCount += oosStatus.getNum_orders();
212 
            					amzn_mfnSaleHistory.append(oosStatus.getNum_orders() + "-");
213 
            				} else {
214 
            					amzn_mfnSaleHistory.append("X-");
215 
            				}
216 
            				break;
217 
            			case 6:
218 
            				if(oosStatus.isIs_oos()== false) {
10352 amar.kumar 219 
            					ebayInStockDays++;
10126 amar.kumar 220 
            					ebayOrderCount += oosStatus.getNum_orders();
221 
            					ebaySaleHistory.append(oosStatus.getNum_orders() + "-");
222 
            				} else {
223 
            					ebaySaleHistory.append("X-");
224 
            				}
225 
            				break;
226 
            			case 7:
227 
            				if(oosStatus.isIs_oos()== false) {
10352 amar.kumar 228 
            					snapdealInStockDays++;
10126 amar.kumar 229 
            					snapdealOrderCount += oosStatus.getNum_orders();
230 
            					snapdealSaleHistory.append(oosStatus.getNum_orders() + "-");
231 
            				} else {
232 
            					snapdealSaleHistory.append("X-");
233 
            				}
234 
            				break;
235 
            			case 8:
236 
            				if(oosStatus.isIs_oos()== false) {
10352 amar.kumar 237 
            					flipkartInStockDays++;
10126 amar.kumar 238 
            					flipkartOrderCount += oosStatus.getNum_orders();
239 
            					flipkartSaleHistory.append(oosStatus.getNum_orders() + "-");
240 
            				} else {
241 
            					flipkartSaleHistory.append("X-");
242 
            				}
243 
            				break;
244 
            			default:
245 
            				//do nothing
246 
            				break;
247 
            		}
248 
            	}
10352 amar.kumar 249 
            	totalAverageSale    = (double)totalOrderCount/totalInStockDays;
250 
            	websiteAverageSale  = (double)websiteOrderCount/websiteInStockDays;
251 
            	ebayAverageSale     = (double)ebayOrderCount/ebayInStockDays;
252 
            	snapdealAverageSale = (double)snapdealOrderCount/snapdealInStockDays;
253 
            	flipkartAverageSale = (double)flipkartOrderCount/flipkartInStockDays;
254 
            	amzn_mfnAverageSale = (double)amzn_mfnOrderCount/amzn_mfnInStockDays;
10126 amar.kumar 255 
 
10352 amar.kumar 256 
            	if(totalAverageSale<=0.0) {
257 
            		nonMovingBufferedWriter.write('\n');
258 
            		nonMovingBufferedWriter.write(StringUtils.join(
259 
	        			new String[] {
260 
	                        String.valueOf(item.getId()),
10353 amar.kumar 261 
	                        categoryIdLabelMap.get(item.getCategory()),
10352 amar.kumar 262 
	                        item.getProductGroup(),
263 
	                        item.getBrand(),
264 
	                        item.getModelName(),
265 
	                        item.getModelNumber(),
266 
	                        item.getColor(),
10353 amar.kumar 267 
	                        item.getBrand() + " " + item.getModelName() + " " + item.getModelNumber() + " " + item.getColor(),
10352 amar.kumar 268 
	                        stockCount.toString(),
269 
	                        stockValue.toString(),
270 
	                        totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()).toString(): "0",
271 
	                        totalSaleHistory.substring(0, totalSaleHistory.length()-1),
272 
	                        totalAverageSale.toString(),
273 
	                        websiteSaleHistory.substring(0, websiteSaleHistory.length()-1),
274 
	                        websiteAverageSale.toString(),
275 
	                        ebaySaleHistory.substring(0, ebaySaleHistory.length()-1),
276 
	                        ebayAverageSale.toString(),
277 
	                        snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1),
278 
	                        snapdealAverageSale.toString(),
279 
	                        flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1),
280 
	                        flipkartAverageSale.toString(),
281 
	                        amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1),
282 
	                        amzn_mfnAverageSale.toString()}, '\t'));
10126 amar.kumar 283 
            	}
10352 amar.kumar 284 
            	else {
285 
            		double nodStock = totalAverageSale > 0 ? new Double(stockCount/totalAverageSale): 0;
286 
            		if(nodStock>30) {
287 
            			slowMovingBufferedWriter.write('\n');
288 
                		slowMovingBufferedWriter.write(StringUtils.join(
289 
    	        			new String[] {
290 
    	                        String.valueOf(item.getId()),
10353 amar.kumar 291 
    	                        categoryIdLabelMap.get(item.getCategory()),
10352 amar.kumar 292 
    	                        item.getProductGroup(),
293 
    	                        item.getBrand(),
294 
    	                        item.getModelName(),
295 
    	                        item.getModelNumber(),
296 
    	                        item.getColor(),
10353 amar.kumar 297 
    	                        item.getBrand() + " " + item.getModelName() + " " + item.getModelNumber() + " " + item.getColor(),
10352 amar.kumar 298 
    	                        stockCount.toString(),
299 
    	                        stockValue.toString(),
300 
    	                        totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()).toString(): "0",
301 
    	                        totalSaleHistory.substring(0, totalSaleHistory.length()-1),
302 
    	                        totalAverageSale.toString(),
303 
    	                        websiteSaleHistory.substring(0, websiteSaleHistory.length()-1),
304 
    	                        websiteAverageSale.toString(),
305 
    	                        ebaySaleHistory.substring(0, ebaySaleHistory.length()-1),
306 
    	                        ebayAverageSale.toString(),
307 
    	                        snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1),
308 
    	                        snapdealAverageSale.toString(),
309 
    	                        flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1),
310 
    	                        flipkartAverageSale.toString(),
311 
    	                        amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1),
312 
    	                        amzn_mfnAverageSale.toString()}, '\t'));
313 
            		} else {
314 
            			movingBufferedWriter.write('\n');
315 
                		movingBufferedWriter.write(StringUtils.join(
316 
    	        			new String[] {
317 
    	                        String.valueOf(item.getId()),
10353 amar.kumar 318 
    	                        categoryIdLabelMap.get(item.getCategory()),
10352 amar.kumar 319 
    	                        item.getProductGroup(),
320 
    	                        item.getBrand(),
321 
    	                        item.getModelName(),
322 
    	                        item.getModelNumber(),
323 
    	                        item.getColor(),
10353 amar.kumar 324 
    	                        item.getBrand() + " " + item.getModelName() + " " + item.getModelNumber() + " " + item.getColor(),
10352 amar.kumar 325 
    	                        stockCount.toString(),
326 
    	                        stockValue.toString(),
327 
    	                        totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()).toString(): "0",
328 
    	                        totalSaleHistory.substring(0, totalSaleHistory.length()-1),
329 
    	                        totalAverageSale.toString(),
330 
    	                        websiteSaleHistory.substring(0, websiteSaleHistory.length()-1),
331 
    	                        websiteAverageSale.toString(),
332 
    	                        ebaySaleHistory.substring(0, ebaySaleHistory.length()-1),
333 
    	                        ebayAverageSale.toString(),
334 
    	                        snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1),
335 
    	                        snapdealAverageSale.toString(),
336 
    	                        flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1),
337 
    	                        flipkartAverageSale.toString(),
338 
    	                        amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1),
339 
    	                        amzn_mfnAverageSale.toString()}, '\t'));
340 
            		}
341 
            	}
342 
            }
343 
            nonMovingBufferedWriter.close();
344 
            slowMovingBufferedWriter.close();
345 
            movingBufferedWriter.close();
10126 amar.kumar 346 
 
347 
            List<File> files = new ArrayList<File>(1);
10352 amar.kumar 348 
            files.add(nonMovingFile);
349 
            files.add(slowMovingFile);
350 
            files.add(movingFile);
10126 amar.kumar 351 
        	GmailUtils mailer = new GmailUtils();
352 
        	mailer.sendSSLMessage(mailTo, "Stock Sales Report ", "", senderAccountMail, senderAccountPswd, files);
353 
		} catch (Exception e) {
354 
			logger.error("Error in generating/sending Stock Sales Report", e);
355 
		}
356 
 
357 
 
358 
	}
359 
 
360 
	public static void main(String[] args) {
361 
		createAndMailReport();
362 
	}
363 
 
364 
}