Subversion Repositories SmartDukaan

Rev

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