Subversion Repositories SmartDukaan

Rev

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