Subversion Repositories SmartDukaan

Rev

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