Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
12166 manish.sha 1 
package in.shop2020.support.utils;
2 
 
3 
import java.io.BufferedWriter;
4 
import java.io.File;
5 
import java.io.FileOutputStream;
6 
import java.io.FileWriter;
7 
import java.util.ArrayList;
8 
import java.util.Collections;
9 
import java.util.Date;
10 
import java.util.HashMap;
11 
import java.util.Hashtable;
12 
import java.util.List;
13 
import java.util.Map;
14 
 
15 
import org.apache.commons.lang.StringUtils;
16 
import org.apache.commons.logging.Log;
17 
import org.apache.poi.hssf.usermodel.HSSFSheet;
18 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
19 
import org.apache.poi.ss.usermodel.Cell;
20 
import org.apache.poi.ss.usermodel.Row;
21 
 
22 
import java.util.Set;
23 
import org.slf4j.Logger;
24 
import org.slf4j.LoggerFactory;
25 
 
26 
import com.mysql.jdbc.log.LogFactory;
27 
import in.shop2020.model.v1.catalog.CatalogService;
28 
import in.shop2020.model.v1.catalog.Category;
29 
import in.shop2020.model.v1.catalog.Item;
30 
import in.shop2020.model.v1.inventory.InventoryService;
31 
import in.shop2020.model.v1.inventory.InventoryType;
32 
import in.shop2020.model.v1.inventory.ItemInventory;
33 
import in.shop2020.model.v1.inventory.OOSStatus;
34 
import in.shop2020.model.v1.inventory.VendorItemPricing;
35 
import in.shop2020.model.v1.inventory.Warehouse;
36 
import in.shop2020.model.v1.inventory.WarehouseType;
37 
import in.shop2020.support.models.MovingStockSaleDetailComparator;
38 
import in.shop2020.support.models.SnmMovingStockSaleDetailComparator;
39 
import in.shop2020.support.models.StockSaleDetail;
40 
import in.shop2020.thrift.clients.CatalogClient;
41 
import in.shop2020.thrift.clients.InventoryClient;
42 
import in.shop2020.utils.GmailUtils;
43 
 
44 
public class NewStockSalesReportFetcher {
45 
	private static final int NUMBER_OF_DAYS_SALE = 5;
12234 manish.sha 46 
	private static final String[] mailToMT = {"chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com", "khushal.bhatia@saholic.com", "rajneesh.arora@saholic.com"};
47 
	private static final String[] mailToEMT = {"khushal.bhatia@saholic.com", "rajneesh.arora@saholic.com", "chandan.kumar@saholic.com", "manoj.pal@shop2020.in"};
12166 manish.sha 48 
	private static final String[] mailTo = {"manish.sharma@shop2020.in"};
49 
	private static final String senderAccountMail = "build-staging@shop2020.in";
50 
	private static final String senderAccountPswd = "shop2020";
51 
 
52 
	static String tmpDir = System.getProperty("java.io.tmpdir");
53 
	static File stockSalesReportMT = new File(tmpDir + "/StockSaleReport-MT" + (new Date()) + ".xls");
54 
	static File stockSalesReportEMT = new File(tmpDir + "/StockSaleReport-EMT" + (new Date()) + ".xls");
55 
 
56 
	static List<StockSaleDetail> movingStockSaleListMT = new ArrayList<StockSaleDetail>();
57 
	static List<StockSaleDetail> nonMovingStockSaleListMT = new ArrayList<StockSaleDetail>();
58 
	static List<StockSaleDetail> slowMovingStockSaleListMT = new ArrayList<StockSaleDetail>();
59 
	static List<StockSaleDetail> movingStockSaleListEMT = new ArrayList<StockSaleDetail>();
60 
	static List<StockSaleDetail> nonMovingStockSaleListEMT = new ArrayList<StockSaleDetail>();
61 
	static List<StockSaleDetail> slowMovingStockSaleListEMT = new ArrayList<StockSaleDetail>();
62 
 
63 
	private static Logger logger = LoggerFactory.getLogger(StockSalesReportFetcher.class);
64 
 
65 
	private static void createAndMailReport() {
66 
		try {
67 
			CatalogClient catalogServiceClient = new CatalogClient();
68 
			CatalogService.Client catalogClient = catalogServiceClient.getClient();
69 
			List<Item> itemList = catalogClient.getAllAliveItems();
70 
 
71 
			if(!catalogClient.isAlive()){
72 
				catalogClient = catalogServiceClient.getClient();
73 
			}
74 
			List<Category> allCategories = catalogClient.getAllCategories();
75 
			Map<Long, String> categoryIdLabelMap = new Hashtable<Long, String>();
76 
			for(Category category : allCategories) {
77 
				categoryIdLabelMap.put(category.getId(), category.getLabel());
78 
			}
79 
 
80 
			InventoryClient inventoryServiceClient = new InventoryClient();
81 
			InventoryService.Client inventoryClient = inventoryServiceClient.getClient();
82 
			Map<Long, ItemInventory> inventoryMap = inventoryClient.getInventorySnapshot(0);
83 
 
84 
			if(!inventoryClient.isAlive()){
85 
				inventoryClient = inventoryServiceClient.getClient();
86 
			}
87 
			List<OOSStatus> oosStatuses = inventoryClient.getOosStatusesForXDays(-1, NUMBER_OF_DAYS_SALE);
88 
			Map<Long, List<OOSStatus>> oosStatusMapByItem = new Hashtable<Long, List<OOSStatus>>(5000);
89 
			for(OOSStatus oosStatus : oosStatuses) {
90 
				List<OOSStatus> oosStatusList;
91 
				Long itemId = oosStatus.getItem_id();
92 
				if(oosStatusMapByItem.containsKey(itemId)) {
93 
					oosStatusList = oosStatusMapByItem.get(itemId);
94 
					oosStatusList.add(oosStatus);
95 
				} else {
96 
					oosStatusList = new ArrayList<OOSStatus>(6);
97 
					oosStatusList.add(oosStatus);
98 
				}
99 
				oosStatusMapByItem.put(itemId, oosStatusList);
100 
			}
101 
 
102 
			if(!inventoryClient.isAlive()){
103 
				inventoryClient = inventoryServiceClient.getClient();
104 
			}
105 
			List<Warehouse>  ourGoodWarehouses = inventoryClient.getWarehouses(WarehouseType.OURS, InventoryType.GOOD, 0, 0, 0);
106 
			Map<Long, Warehouse> ourWarehouseMap = new Hashtable<Long, Warehouse>(200);
107 
			for(Warehouse warehouse : ourGoodWarehouses) {
108 
				//System.out.println("Warehouse = " + warehouse.getId());
109 
				ourWarehouseMap.put(warehouse.getId(), warehouse);
110 
			}
111 
 
112 
			if(!inventoryClient.isAlive()){
113 
				inventoryClient = inventoryServiceClient.getClient();
114 
			}
115 
			List<VendorItemPricing> vendorItemPricings = inventoryClient.getAllVendorItemPricing(0, 0);
116 
			Map<Long, List<VendorItemPricing>> itemPricingMap = new Hashtable<Long, List<VendorItemPricing>>(5000);
117 
			for(VendorItemPricing vendorItemPricing : vendorItemPricings) {
118 
				List<VendorItemPricing> pricingListForItem;
119 
				Long itemId = vendorItemPricing.getItemId();
120 
				if(itemPricingMap.containsKey(itemId)) {
121 
					pricingListForItem = itemPricingMap.get(itemId);
122 
					pricingListForItem.add(vendorItemPricing);
123 
				} else {
124 
					pricingListForItem = new ArrayList<VendorItemPricing>();
125 
					pricingListForItem.add(vendorItemPricing);
126 
				}
127 
				itemPricingMap.put(itemId, pricingListForItem);
128 
			}
129 
 
130 
			Long stockCount;
131 
			Double stockValue;
132 
			for (Item item : itemList) {
133 
				stockCount = 0L;
134 
				stockValue = 0.0;
135 
				Long totalOrderCount    = 0L;
136 
				Long websiteOrderCount  = 0L;
137 
				Long ebayOrderCount     = 0L;
138 
				Long snapdealOrderCount = 0L;
139 
				Long flipkartOrderCount = 0L;
140 
				Long amzn_mfnOrderCount = 0L;
141 
				Long totalInStockDays    = 0L;
142 
				Long websiteInStockDays  = 0L;
143 
				Long ebayInStockDays     = 0L;
144 
				Long snapdealInStockDays = 0L;
145 
				Long flipkartInStockDays = 0L;
146 
				Long amzn_mfnInStockDays = 0L;
147 
				Double websiteAverageSale  = 0.0;
148 
				Double totalAverageSale = 0.0;
149 
				Double ebayAverageSale     = 0.0;
150 
				Double snapdealAverageSale = 0.0;
151 
				Double flipkartAverageSale = 0.0;
152 
				Double amzn_mfnAverageSale  = 0.0;
153 
				StringBuilder totalSaleHistory    = new StringBuilder("");
154 
				StringBuilder websiteSaleHistory  = new StringBuilder("");
155 
				StringBuilder ebaySaleHistory     = new StringBuilder("");
156 
				StringBuilder snapdealSaleHistory = new StringBuilder("");
157 
				StringBuilder flipkartSaleHistory = new StringBuilder("");
158 
				StringBuilder amzn_mfnSaleHistory = new StringBuilder("");
159 
 
160 
				ItemInventory itemInventory = inventoryMap.get(item.getId());
161 
				Map<Long, Long> availabilityMap;
162 
				if(itemInventory!= null) {
163 
					//System.out.println("itemId =" + item.getId());
164 
					availabilityMap = itemInventory.getAvailability();
165 
					for (Long warehouseId : availabilityMap.keySet()) {
166 
						if(ourWarehouseMap.containsKey(warehouseId)) {
167 
							long currentAvailability = availabilityMap.get(warehouseId);
168 
							//System.out.println("itemId =" + item.getId() + " availability = "+ currentAvailability);
169 
							stockCount += currentAvailability;
170 
							if(currentAvailability>0) {
171 
								List<VendorItemPricing> pricingList = itemPricingMap.get(item.getId());
172 
								for(VendorItemPricing vendorItemPricing: pricingList) {
173 
									if(vendorItemPricing.getVendorId() == ourWarehouseMap.get(warehouseId).getVendor().getId()) {
174 
										stockValue = stockValue + (vendorItemPricing.getNlc()*currentAvailability);
175 
									}
176 
								}
177 
							}
178 
						}
179 
					}
180 
				}
181 
				if(stockCount<=0) {
182 
					continue;
183 
				}
12221 manish.sha 184 
				if(oosStatusMapByItem.containsKey(item.getId())){
185 
					for(OOSStatus oosStatus : oosStatusMapByItem.get(item.getId())) {
186 
						switch(oosStatus.getSourceId()) {
187 
						case 0:
188 
							if(oosStatus.isIs_oos()== false) {
189 
								totalInStockDays++;
190 
								totalOrderCount += oosStatus.getNum_orders();
191 
								totalSaleHistory.append(oosStatus.getNum_orders() + "-");
192 
							} else {
193 
								totalSaleHistory.append("X-");
194 
							}
195 
							break;
196 
						case 1:
197 
							if(oosStatus.isIs_oos()== false) {
198 
								websiteInStockDays++;
199 
								websiteOrderCount += oosStatus.getNum_orders();
200 
								websiteSaleHistory.append(oosStatus.getNum_orders() + "-");
201 
							} else {
202 
								websiteSaleHistory.append("X-");
203 
							}
204 
							break;
205 
						case 3:
206 
							if(oosStatus.isIs_oos()== false) {
207 
								amzn_mfnInStockDays++;
208 
								amzn_mfnOrderCount += oosStatus.getNum_orders();
209 
								amzn_mfnSaleHistory.append(oosStatus.getNum_orders() + "-");
210 
							} else {
211 
								amzn_mfnSaleHistory.append("X-");
212 
							}
213 
							break;
214 
						case 6:
215 
							if(oosStatus.isIs_oos()== false) {
216 
								ebayInStockDays++;
217 
								ebayOrderCount += oosStatus.getNum_orders();
218 
								ebaySaleHistory.append(oosStatus.getNum_orders() + "-");
219 
							} else {
220 
								ebaySaleHistory.append("X-");
221 
							}
222 
							break;
223 
						case 7:
224 
							if(oosStatus.isIs_oos()== false) {
225 
								snapdealInStockDays++;
226 
								snapdealOrderCount += oosStatus.getNum_orders();
227 
								snapdealSaleHistory.append(oosStatus.getNum_orders() + "-");
228 
							} else {
229 
								snapdealSaleHistory.append("X-");
230 
							}
231 
							break;
232 
						case 8:
233 
							if(oosStatus.isIs_oos()== false) {
234 
								flipkartInStockDays++;
235 
								flipkartOrderCount += oosStatus.getNum_orders();
236 
								flipkartSaleHistory.append(oosStatus.getNum_orders() + "-");
237 
							} else {
238 
								flipkartSaleHistory.append("X-");
239 
							}
240 
							break;
241 
						default:
242 
							//do nothing
243 
							break;
12166 manish.sha 244 
						}
245 
					}
246 
				}
12221 manish.sha 247 
				else{
248 
					System.out.println("Oos Status is not available for item : - "+ item.getId());
12222 manish.sha 249 
					continue;
12221 manish.sha 250 
				}
12166 manish.sha 251 
				totalAverageSale    = (double)totalOrderCount/totalInStockDays;
12178 manish.sha 252 
				totalAverageSale    = Math.round(totalAverageSale * 10.0)/10.0;
12166 manish.sha 253 
				websiteAverageSale  = (double)websiteOrderCount/websiteInStockDays;
12178 manish.sha 254 
				websiteAverageSale  = Math.round(websiteAverageSale * 10.0)/10.0;
12166 manish.sha 255 
				ebayAverageSale     = (double)ebayOrderCount/ebayInStockDays;
12178 manish.sha 256 
				ebayAverageSale     = Math.round(ebayAverageSale * 10.0)/10.0;
12166 manish.sha 257 
				snapdealAverageSale = (double)snapdealOrderCount/snapdealInStockDays;
12178 manish.sha 258 
				snapdealAverageSale = Math.round(snapdealAverageSale * 10.0)/10.0;
12166 manish.sha 259 
				flipkartAverageSale = (double)flipkartOrderCount/flipkartInStockDays;
12178 manish.sha 260 
				flipkartAverageSale = Math.round(flipkartAverageSale * 10.0)/10.0;
12166 manish.sha 261 
				amzn_mfnAverageSale = (double)amzn_mfnOrderCount/amzn_mfnInStockDays;
12178 manish.sha 262 
				amzn_mfnAverageSale = Math.round(amzn_mfnAverageSale * 10.0)/10.0;
12166 manish.sha 263 
 
264 
				if(totalAverageSale<=0.0) {
265 
					StockSaleDetail ssDetail = new StockSaleDetail();
266 
					ssDetail.setItemId(item.getId());
267 
					ssDetail.setCategory(categoryIdLabelMap.get(item.getCategory()));
268 
					ssDetail.setProductGroup(item.getProductGroup());
269 
					ssDetail.setBrand(item.getBrand());
270 
					ssDetail.setModelName(item.getModelName());
271 
					ssDetail.setModelNumber(item.getModelNumber());
272 
					ssDetail.setColor(item.getColor());
273 
					ssDetail.setStockQuantity(stockCount);
274 
					ssDetail.setStockValue(stockValue);
275 
					ssDetail.setNodStock(totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()) : 0);
276 
					ssDetail.setSalesHistoryAll(totalSaleHistory.substring(0, totalSaleHistory.length()-1));
277 
					ssDetail.setAvgSalesAll(totalAverageSale);
278 
					ssDetail.setSalesHistoryWS(websiteSaleHistory.substring(0, websiteSaleHistory.length()-1));
279 
					ssDetail.setAvgSalesWS(websiteAverageSale);
280 
					ssDetail.setSalesHistoryEbay(ebaySaleHistory.substring(0, ebaySaleHistory.length()-1));
281 
					ssDetail.setAvgSalesEbay(ebayAverageSale);
282 
					ssDetail.setSalesHistorySD(snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1));
283 
					ssDetail.setAvgSalesSD(snapdealAverageSale);
284 
					ssDetail.setSalesHistoryFK(flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1));
285 
					ssDetail.setAvgSalesFK(flipkartAverageSale);
286 
					ssDetail.setSalesHistoryMFN(amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1));
287 
					ssDetail.setAvgSalesMFN(amzn_mfnAverageSale);
288 
					if(item.getCategory() == 10006 || item.getCategory() == 10010){
289 
						nonMovingStockSaleListMT.add(ssDetail);
290 
					} else {
291 
						nonMovingStockSaleListEMT.add(ssDetail);
292 
					}
293 
				}
294 
				else {
295 
					double nodStock = totalAverageSale > 0 ? new Double(stockCount/totalAverageSale): 0;
296 
					if(nodStock>30) {
297 
						StockSaleDetail ssDetail = new StockSaleDetail();
298 
						ssDetail.setItemId(item.getId());
299 
						ssDetail.setCategory(categoryIdLabelMap.get(item.getCategory()));
300 
						ssDetail.setProductGroup(item.getProductGroup());
301 
						ssDetail.setBrand(item.getBrand());
302 
						ssDetail.setModelName(item.getModelName());
303 
						ssDetail.setModelNumber(item.getModelNumber());
304 
						ssDetail.setColor(item.getColor());
305 
						ssDetail.setStockQuantity(stockCount);
306 
						ssDetail.setStockValue(stockValue);
307 
						ssDetail.setNodStock(totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()) : 0);
308 
						ssDetail.setSalesHistoryAll(totalSaleHistory.substring(0, totalSaleHistory.length()-1));
309 
						ssDetail.setAvgSalesAll(totalAverageSale);
310 
						ssDetail.setSalesHistoryWS(websiteSaleHistory.substring(0, websiteSaleHistory.length()-1));
311 
						ssDetail.setAvgSalesWS(websiteAverageSale);
312 
						ssDetail.setSalesHistoryEbay(ebaySaleHistory.substring(0, ebaySaleHistory.length()-1));
313 
						ssDetail.setAvgSalesEbay(ebayAverageSale);
314 
						ssDetail.setSalesHistorySD(snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1));
315 
						ssDetail.setAvgSalesSD(snapdealAverageSale);
316 
						ssDetail.setSalesHistoryFK(flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1));
317 
						ssDetail.setAvgSalesFK(flipkartAverageSale);
318 
						ssDetail.setSalesHistoryMFN(amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1));
319 
						ssDetail.setAvgSalesMFN(amzn_mfnAverageSale);
320 
						if(item.getCategory() == 10006 || item.getCategory() == 10010){
321 
							slowMovingStockSaleListMT.add(ssDetail);
322 
						} else {
323 
							slowMovingStockSaleListEMT.add(ssDetail);
324 
						}
325 
					} else {
326 
						StockSaleDetail ssDetail = new StockSaleDetail();
327 
						ssDetail.setItemId(item.getId());
328 
						ssDetail.setCategory(categoryIdLabelMap.get(item.getCategory()));
329 
						ssDetail.setProductGroup(item.getProductGroup());
330 
						ssDetail.setBrand(item.getBrand());
331 
						ssDetail.setModelName(item.getModelName());
332 
						ssDetail.setModelNumber(item.getModelNumber());
333 
						ssDetail.setColor(item.getColor());
334 
						ssDetail.setStockQuantity(stockCount);
335 
						ssDetail.setStockValue(stockValue);
336 
						ssDetail.setNodStock(totalAverageSale>0 ? new Long(new Double(stockCount/totalAverageSale).longValue()) : 0);
337 
						ssDetail.setSalesHistoryAll(totalSaleHistory.substring(0, totalSaleHistory.length()-1));
338 
						ssDetail.setAvgSalesAll(totalAverageSale);
339 
						ssDetail.setSalesHistoryWS(websiteSaleHistory.substring(0, websiteSaleHistory.length()-1));
340 
						ssDetail.setAvgSalesWS(websiteAverageSale);
341 
						ssDetail.setSalesHistoryEbay(ebaySaleHistory.substring(0, ebaySaleHistory.length()-1));
342 
						ssDetail.setAvgSalesEbay(ebayAverageSale);
343 
						ssDetail.setSalesHistorySD(snapdealSaleHistory.substring(0, snapdealSaleHistory.length()-1));
344 
						ssDetail.setAvgSalesSD(snapdealAverageSale);
345 
						ssDetail.setSalesHistoryFK(flipkartSaleHistory.substring(0, flipkartSaleHistory.length()-1));
346 
						ssDetail.setAvgSalesFK(flipkartAverageSale);
347 
						ssDetail.setSalesHistoryMFN(amzn_mfnSaleHistory.substring(0, amzn_mfnSaleHistory.length()-1));
348 
						ssDetail.setAvgSalesMFN(amzn_mfnAverageSale);
349 
						if(item.getCategory() == 10006 || item.getCategory() == 10010){
350 
							movingStockSaleListMT.add(ssDetail);
351 
						} else {
352 
							movingStockSaleListEMT.add(ssDetail);
353 
						}
354 
					}
355 
				}
356 
			}
357 
 
358 
			if(movingStockSaleListMT!=null && movingStockSaleListMT.size()>2){
359 
				Collections.sort(movingStockSaleListMT, new MovingStockSaleDetailComparator());
360 
			}
361 
			if(movingStockSaleListEMT!=null && movingStockSaleListEMT.size()>2){
362 
				Collections.sort(movingStockSaleListEMT, new MovingStockSaleDetailComparator());
363 
			}
364 
			if(slowMovingStockSaleListMT!=null && slowMovingStockSaleListMT.size()>2){
365 
				Collections.sort(slowMovingStockSaleListMT, new SnmMovingStockSaleDetailComparator());
366 
			}
367 
			if(slowMovingStockSaleListEMT!=null && slowMovingStockSaleListEMT.size()>2){
368 
				Collections.sort(slowMovingStockSaleListEMT, new SnmMovingStockSaleDetailComparator());
369 
			}
370 
			if(nonMovingStockSaleListMT!=null && nonMovingStockSaleListMT.size()>2){
371 
				Collections.sort(nonMovingStockSaleListMT, new SnmMovingStockSaleDetailComparator());
372 
			}
373 
			if(nonMovingStockSaleListEMT!=null && nonMovingStockSaleListEMT.size()>2){
374 
				Collections.sort(nonMovingStockSaleListEMT, new SnmMovingStockSaleDetailComparator());
375 
			}
12221 manish.sha 376 
 
377 
 
12220 manish.sha 378 
			List<StockSaleDetail> itemToReplenishMT = new ArrayList<StockSaleDetail>();
379 
			List<StockSaleDetail> itemToReplenishEMT = new ArrayList<StockSaleDetail>();
380 
			for(StockSaleDetail ssDetail  : movingStockSaleListMT){
381 
				if(ssDetail.getNodStock() <= 5){
382 
					itemToReplenishMT.add(ssDetail);
383 
				}
384 
			}
385 
			for(StockSaleDetail ssDetail  : movingStockSaleListEMT){
386 
				if(ssDetail.getNodStock() <= 5){
387 
					itemToReplenishEMT.add(ssDetail);
388 
				}
389 
			}
12221 manish.sha 390 
 
12220 manish.sha 391 
			if(itemToReplenishMT!=null && itemToReplenishMT.size()>2){
392 
				Collections.sort(itemToReplenishMT, new MovingStockSaleDetailComparator());
393 
			}
394 
			if(itemToReplenishEMT!=null && itemToReplenishEMT.size()>2){
395 
				Collections.sort(itemToReplenishEMT, new MovingStockSaleDetailComparator());
396 
			}
12221 manish.sha 397 
 
12166 manish.sha 398 
			StringBuffer sb = new StringBuffer();
399 
			sb.append("<html>" );
400 
			sb.append("<table border=\"1\" align=\"center\">"
401 
					+ "<caption><b>" + "ITEMS TO REPLENISH" + "</b></caption>"
402 
					+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"
403 
					+ "<td><b>" + "BRAND" + "</b></td>"
404 
					+ "<td><b>" + "CATEGORY" + "</b></td>"
405 
					+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"
406 
					+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"
407 
					+ "<td><b>" + "STOCK-QTY" + "</b></td>"
408 
					+ "<td><b>" + "STOCK-VALUE" + "</b></td>"
409 
					+ "<td><b>" + "NOD-STOCK" + "</b></td>"
410 
					+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"
411 
					+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"
412 
					+ "</tr>");
413 
			int i=1;
12220 manish.sha 414 
			for(StockSaleDetail ssDetail  : itemToReplenishMT){
12166 manish.sha 415 
				if(i==10){
416 
					break;
417 
				}
418 
				sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"
12221 manish.sha 419 
						+ "<td>" + ssDetail.getBrand() + "</td>"
420 
						+ "<td>" + ssDetail.getCategory() + "</td>"
421 
						+ "<td>" + ssDetail.getProductGroup() + "</td>"
422 
						+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"
423 
						+ "<td>" + ssDetail.getStockQuantity() + "</td>"
424 
						+ "<td>" + ssDetail.getStockValue() + "</td>"
425 
						+ "<td>" + ssDetail.getNodStock() + "</td>"
426 
						+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"
427 
						+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"
428 
						+ "</tr>");
12166 manish.sha 429 
				i++;
430 
			}
12221 manish.sha 431 
 
12166 manish.sha 432 
			sb.append("</table>" );
12221 manish.sha 433 
 
12166 manish.sha 434 
			sb.append("<table border=\"1\" align=\"center\">"
435 
					+ "<caption><b>" + "SLOW MOVING ITEMS" + "</b></caption>"
436 
					+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"
437 
					+ "<td><b>" + "BRAND" + "</b></td>"
438 
					+ "<td><b>" + "CATEGORY" + "</b></td>"
439 
					+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"
440 
					+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"
441 
					+ "<td><b>" + "STOCK-QTY" + "</b></td>"
442 
					+ "<td><b>" + "STOCK-VALUE" + "</b></td>"
443 
					+ "<td><b>" + "NOD-STOCK" + "</b></td>"
444 
					+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"
445 
					+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"
446 
					+ "</tr>");
447 
			i=1;
448 
			for(StockSaleDetail ssDetail  : slowMovingStockSaleListMT){
449 
				if(i==10){
450 
					break;
451 
				}
452 
				sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"
12221 manish.sha 453 
						+ "<td>" + ssDetail.getBrand() + "</td>"
454 
						+ "<td>" + ssDetail.getCategory() + "</td>"
455 
						+ "<td>" + ssDetail.getProductGroup() + "</td>"
456 
						+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"
457 
						+ "<td>" + ssDetail.getStockQuantity() + "</td>"
458 
						+ "<td>" + ssDetail.getStockValue() + "</td>"
459 
						+ "<td>" + ssDetail.getNodStock() + "</td>"
460 
						+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"
461 
						+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"
462 
						+ "</tr>");
12166 manish.sha 463 
				i++;
464 
			}
12221 manish.sha 465 
 
12166 manish.sha 466 
			sb.append("</table>" );
12221 manish.sha 467 
 
12166 manish.sha 468 
			sb.append("<table border=\"1\" align=\"center\">"
469 
					+ "<caption><b>" + "NON MOVING ITEMS" + "</b></caption>"
470 
					+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"
471 
					+ "<td><b>" + "BRAND" + "</b></td>"
472 
					+ "<td><b>" + "CATEGORY" + "</b></td>"
473 
					+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"
474 
					+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"
475 
					+ "<td><b>" + "STOCK-QTY" + "</b></td>"
476 
					+ "<td><b>" + "STOCK-VALUE" + "</b></td>"
477 
					+ "<td><b>" + "NOD-STOCK" + "</b></td>"
478 
					+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"
479 
					+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"
480 
					+ "</tr>");
481 
			i=1;
482 
			for(StockSaleDetail ssDetail  : nonMovingStockSaleListMT){
483 
				if(i==10){
484 
					break;
485 
				}
486 
				sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"
12221 manish.sha 487 
						+ "<td>" + ssDetail.getBrand() + "</td>"
488 
						+ "<td>" + ssDetail.getCategory() + "</td>"
489 
						+ "<td>" + ssDetail.getProductGroup() + "</td>"
490 
						+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"
491 
						+ "<td>" + ssDetail.getStockQuantity() + "</td>"
492 
						+ "<td>" + ssDetail.getStockValue() + "</td>"
493 
						+ "<td>" + ssDetail.getNodStock() + "</td>"
494 
						+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"
495 
						+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"
496 
						+ "</tr>");
12166 manish.sha 497 
				i++;
498 
			}
12221 manish.sha 499 
 
12166 manish.sha 500 
			sb.append("</table>");
501 
			sb.append("</html>");
12221 manish.sha 502 
 
12166 manish.sha 503 
			BufferedWriter out = new BufferedWriter(new FileWriter("/tmp/MobileTablets-StockSales-file.htm"));
504 
			out.write(sb.toString());
505 
			out.flush();
506 
			out.close();
12221 manish.sha 507 
 
508 
 
12166 manish.sha 509 
			sb = new StringBuffer();
510 
			sb.append("<html>" );
511 
			sb.append("<table border=\"1\" align=\"center\">"
512 
					+ "<caption><b>" + "ITEMS TO REPLENISH" + "</b></caption>"
513 
					+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"
514 
					+ "<td><b>" + "BRAND" + "</b></td>"
515 
					+ "<td><b>" + "CATEGORY" + "</b></td>"
516 
					+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"
517 
					+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"
518 
					+ "<td><b>" + "STOCK-QTY" + "</b></td>"
519 
					+ "<td><b>" + "STOCK-VALUE" + "</b></td>"
520 
					+ "<td><b>" + "NOD-STOCK" + "</b></td>"
521 
					+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"
522 
					+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"
523 
					+ "</tr>");
524 
			i=1;
12220 manish.sha 525 
			for(StockSaleDetail ssDetail  : itemToReplenishEMT){
12166 manish.sha 526 
				if(i==10){
527 
					break;
528 
				}
529 
				sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"
12221 manish.sha 530 
						+ "<td>" + ssDetail.getBrand() + "</td>"
531 
						+ "<td>" + ssDetail.getCategory() + "</td>"
532 
						+ "<td>" + ssDetail.getProductGroup() + "</td>"
533 
						+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"
534 
						+ "<td>" + ssDetail.getStockQuantity() + "</td>"
535 
						+ "<td>" + ssDetail.getStockValue() + "</td>"
536 
						+ "<td>" + ssDetail.getNodStock() + "</td>"
537 
						+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"
538 
						+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"
539 
						+ "</tr>");
12166 manish.sha 540 
				i++;
541 
			}
12221 manish.sha 542 
 
12166 manish.sha 543 
			sb.append("</table>" );
12221 manish.sha 544 
 
12166 manish.sha 545 
			sb.append("<table border=\"1\" align=\"center\">"
546 
					+ "<caption><b>" + "SLOW MOVING ITEMS" + "</b></caption>"
547 
					+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"
548 
					+ "<td><b>" + "BRAND" + "</b></td>"
549 
					+ "<td><b>" + "CATEGORY" + "</b></td>"
550 
					+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"
551 
					+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"
552 
					+ "<td><b>" + "STOCK-QTY" + "</b></td>"
553 
					+ "<td><b>" + "STOCK-VALUE" + "</b></td>"
554 
					+ "<td><b>" + "NOD-STOCK" + "</b></td>"
555 
					+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"
556 
					+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"
557 
					+ "</tr>");
558 
			i=1;
559 
			for(StockSaleDetail ssDetail  : slowMovingStockSaleListEMT){
560 
				if(i==10){
561 
					break;
562 
				}
563 
				sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"
12221 manish.sha 564 
						+ "<td>" + ssDetail.getBrand() + "</td>"
565 
						+ "<td>" + ssDetail.getCategory() + "</td>"
566 
						+ "<td>" + ssDetail.getProductGroup() + "</td>"
567 
						+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"
568 
						+ "<td>" + ssDetail.getStockQuantity() + "</td>"
569 
						+ "<td>" + ssDetail.getStockValue() + "</td>"
570 
						+ "<td>" + ssDetail.getNodStock() + "</td>"
571 
						+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"
572 
						+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"
573 
						+ "</tr>");
12166 manish.sha 574 
				i++;
575 
			}
12221 manish.sha 576 
 
12166 manish.sha 577 
			sb.append("</table>" );
12221 manish.sha 578 
 
12166 manish.sha 579 
			sb.append("<table border=\"1\" align=\"center\">"
580 
					+ "<caption><b>" + "NON MOVING ITEMS" + "</b></caption>"
581 
					+ "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>"
582 
					+ "<td><b>" + "BRAND" + "</b></td>"
583 
					+ "<td><b>" + "CATEGORY" + "</b></td>"
584 
					+ "<td><b>" + "PRODUCT-GROUP" + "</b></td>"
585 
					+ "<td><b>" + "PRODUCT-NAME" + "</b></td>"
586 
					+ "<td><b>" + "STOCK-QTY" + "</b></td>"
587 
					+ "<td><b>" + "STOCK-VALUE" + "</b></td>"
588 
					+ "<td><b>" + "NOD-STOCK" + "</b></td>"
589 
					+ "<td><b>" + "SALES-HISTORY-ALL" + "</b></td>"
590 
					+ "<td><b>" + "AVG-SALE-ALL" + "</b></td>"
591 
					+ "</tr>");
592 
			i=1;
593 
			for(StockSaleDetail ssDetail  : nonMovingStockSaleListEMT){
594 
				if(i==10){
595 
					break;
596 
				}
597 
				sb.append("<tr>" + "<td><b>" + ssDetail.getItemId() + "</b></td>"
12221 manish.sha 598 
						+ "<td>" + ssDetail.getBrand() + "</td>"
599 
						+ "<td>" + ssDetail.getCategory() + "</td>"
600 
						+ "<td>" + ssDetail.getProductGroup() + "</td>"
601 
						+ "<td>" + ssDetail.getBrand() +" "+ ssDetail.getModelName()+ " "+ssDetail.getModelNumber()+ " "+ssDetail.getColor() + "</td>"
602 
						+ "<td>" + ssDetail.getStockQuantity() + "</td>"
603 
						+ "<td>" + ssDetail.getStockValue() + "</td>"
604 
						+ "<td>" + ssDetail.getNodStock() + "</td>"
605 
						+ "<td>" + ssDetail.getSalesHistoryAll() + "</td>"
606 
						+ "<td>" + ssDetail.getAvgSalesAll() + "</td>"
607 
						+ "</tr>");
12166 manish.sha 608 
				i++;
609 
			}
12221 manish.sha 610 
 
12166 manish.sha 611 
			sb.append("</table>");
612 
			sb.append("</html>");
12221 manish.sha 613 
 
12166 manish.sha 614 
			out = new BufferedWriter(new FileWriter("/tmp/ExceptMobileTablets-StockSales-file.htm"));
615 
			out.write(sb.toString());
616 
			out.flush();
617 
			out.close();
12221 manish.sha 618 
 
12177 manish.sha 619 
			createXlsFile(movingStockSaleListMT, slowMovingStockSaleListMT, nonMovingStockSaleListMT, true);
620 
			createXlsFile(movingStockSaleListEMT, slowMovingStockSaleListEMT, nonMovingStockSaleListEMT, false);
12166 manish.sha 621 
 
12221 manish.sha 622 
 
12166 manish.sha 623 
			GmailUtils mailer = new GmailUtils();
12234 manish.sha 624 
			mailer.sendSSLMessage(mailToMT, "Stock Sales Report - Mobile And Tablets ", "", senderAccountMail, senderAccountPswd, tmpDir +"/"+ stockSalesReportMT.getName(), "/tmp/MobileTablets-StockSales-file.htm");
625 
			mailer.sendSSLMessage(mailToEMT, "Stock Sales Report - Except Mobile And Tablets ", "", senderAccountMail, senderAccountPswd, tmpDir +"/"+stockSalesReportEMT.getName(), "/tmp/ExceptMobileTablets-StockSales-file.htm");
12166 manish.sha 626 
		} catch (Exception e) {
627 
			logger.error("Error in generating/sending Stock Sales Report", e);
628 
		}
629 
 
630 
 
631 
	}
632 
 
12177 manish.sha 633 
	private static void createXlsFile(List<StockSaleDetail> movingStockSaleList,
12166 manish.sha 634 
			List<StockSaleDetail> slowMovingStockSaleList,
635 
			List<StockSaleDetail> nonMovingStockSaleList,
636 
			boolean mobTab) {
637 
		try{
12177 manish.sha 638 
			HSSFWorkbook workbook = new HSSFWorkbook();
639 
			HSSFSheet movingSheet = workbook.createSheet("Moving-Stock");
640 
			HSSFSheet nonMovingSheet = workbook.createSheet("Non-Moving-Stock");
641 
			HSSFSheet slowMovingSheet = workbook.createSheet("Slow-Moving-Stock");
12166 manish.sha 642 
			Row row = movingSheet.createRow(0);
643 
 
644 
			row.createCell(0).setCellValue("ItemId");
645 
			row.createCell(1).setCellValue("Category");
646 
			row.createCell(2).setCellValue("ProductGroup");
647 
			row.createCell(3).setCellValue("Brand");
648 
			row.createCell(4).setCellValue("Model Name");
649 
			row.createCell(5).setCellValue("Model Number");
650 
			row.createCell(6).setCellValue("Color");
651 
			row.createCell(7).setCellValue("Product");
652 
			row.createCell(8).setCellValue("Stock Qty");
653 
			row.createCell(9).setCellValue("Stock Value");
654 
			row.createCell(10).setCellValue("NOD Stock");
655 
			row.createCell(11).setCellValue("Sales History(All-Sources)");
656 
			row.createCell(12).setCellValue("Avg Sales(All-Sources)");
657 
			row.createCell(13).setCellValue("Sales History(Website)");
658 
			row.createCell(14).setCellValue("Avg Sales(Website)");
659 
			row.createCell(15).setCellValue("Sales History(Ebay)");
660 
			row.createCell(16).setCellValue("Avg Sales(Ebay)");
661 
			row.createCell(17).setCellValue("Sales History(Snapdeal)");
662 
			row.createCell(18).setCellValue("Avg Sales(Snapdeal)");
663 
			row.createCell(19).setCellValue("Sales History(Flipkart)");
664 
			row.createCell(20).setCellValue("Avg Sales(Flipkart)");
665 
			row.createCell(21).setCellValue("Sales History(Amazon-MFN)");
666 
			row.createCell(22).setCellValue("Avg Sales(Amazon-MFN)");
667 
 
668 
			int i=1;
669 
			for(StockSaleDetail ssDetail : movingStockSaleList){
670 
				row = movingSheet.createRow(i);
671 
				row.createCell(0).setCellValue(ssDetail.getItemId()+"");
672 
				row.createCell(1).setCellValue(ssDetail.getCategory());
673 
				row.createCell(2).setCellValue(ssDetail.getProductGroup());
674 
				row.createCell(3).setCellValue(ssDetail.getBrand());
675 
				row.createCell(4).setCellValue(ssDetail.getModelName());
676 
				row.createCell(5).setCellValue(ssDetail.getModelNumber());
677 
				row.createCell(6).setCellValue(ssDetail.getColor());
678 
				row.createCell(7).setCellValue(ssDetail.getBrand()+" "+ssDetail.getModelName()+" "+ssDetail.getModelNumber()+" "+ssDetail.getColor());
679 
				row.createCell(8).setCellValue(ssDetail.getStockQuantity()+"");
680 
				row.createCell(9).setCellValue(ssDetail.getStockValue()+"");
681 
				row.createCell(10).setCellValue(ssDetail.getNodStock()+"");
682 
				row.createCell(11).setCellValue(ssDetail.getSalesHistoryAll());
683 
				row.createCell(12).setCellValue(ssDetail.getAvgSalesAll()+"");
684 
				row.createCell(13).setCellValue(ssDetail.getSalesHistoryWS());
685 
				row.createCell(14).setCellValue(ssDetail.getAvgSalesWS()+"");
686 
				row.createCell(15).setCellValue(ssDetail.getSalesHistoryEbay());
687 
				row.createCell(16).setCellValue(ssDetail.getAvgSalesEbay()+"");
688 
				row.createCell(17).setCellValue(ssDetail.getSalesHistorySD());
689 
				row.createCell(18).setCellValue(ssDetail.getAvgSalesSD()+"");
690 
				row.createCell(19).setCellValue(ssDetail.getSalesHistoryFK());
691 
				row.createCell(20).setCellValue(ssDetail.getAvgSalesFK()+"");
692 
				row.createCell(21).setCellValue(ssDetail.getSalesHistoryMFN());
693 
				row.createCell(22).setCellValue(ssDetail.getAvgSalesMFN());
694 
				i++;
695 
			}
696 
 
697 
			row = nonMovingSheet.createRow(0);
698 
 
699 
			row.createCell(0).setCellValue("ItemId");
700 
			row.createCell(1).setCellValue("Category");
701 
			row.createCell(2).setCellValue("ProductGroup");
702 
			row.createCell(3).setCellValue("Brand");
703 
			row.createCell(4).setCellValue("Model Name");
704 
			row.createCell(5).setCellValue("Model Number");
705 
			row.createCell(6).setCellValue("Color");
706 
			row.createCell(7).setCellValue("Product");
707 
			row.createCell(8).setCellValue("Stock Qty");
708 
			row.createCell(9).setCellValue("Stock Value");
709 
			row.createCell(10).setCellValue("NOD Stock");
710 
			row.createCell(11).setCellValue("Sales History(All-Sources)");
711 
			row.createCell(12).setCellValue("Avg Sales(All-Sources)");
712 
			row.createCell(13).setCellValue("Sales History(Website)");
713 
			row.createCell(14).setCellValue("Avg Sales(Website)");
714 
			row.createCell(15).setCellValue("Sales History(Ebay)");
715 
			row.createCell(16).setCellValue("Avg Sales(Ebay)");
716 
			row.createCell(17).setCellValue("Sales History(Snapdeal)");
717 
			row.createCell(18).setCellValue("Avg Sales(Snapdeal)");
718 
			row.createCell(19).setCellValue("Sales History(Flipkart)");
719 
			row.createCell(20).setCellValue("Avg Sales(Flipkart)");
720 
			row.createCell(21).setCellValue("Sales History(Amazon-MFN)");
721 
			row.createCell(22).setCellValue("Avg Sales(Amazon-MFN)");
722 
 
723 
			i=1;
724 
			for(StockSaleDetail ssDetail : nonMovingStockSaleList){
12179 manish.sha 725 
				row = nonMovingSheet.createRow(i);
12166 manish.sha 726 
				row.createCell(0).setCellValue(ssDetail.getItemId()+"");
727 
				row.createCell(1).setCellValue(ssDetail.getCategory());
728 
				row.createCell(2).setCellValue(ssDetail.getProductGroup());
729 
				row.createCell(3).setCellValue(ssDetail.getBrand());
730 
				row.createCell(4).setCellValue(ssDetail.getModelName());
731 
				row.createCell(5).setCellValue(ssDetail.getModelNumber());
732 
				row.createCell(6).setCellValue(ssDetail.getColor());
733 
				row.createCell(7).setCellValue(ssDetail.getBrand()+" "+ssDetail.getModelName()+" "+ssDetail.getModelNumber()+" "+ssDetail.getColor());
734 
				row.createCell(8).setCellValue(ssDetail.getStockQuantity()+"");
735 
				row.createCell(9).setCellValue(ssDetail.getStockValue()+"");
736 
				row.createCell(10).setCellValue(ssDetail.getNodStock()+"");
737 
				row.createCell(11).setCellValue(ssDetail.getSalesHistoryAll());
738 
				row.createCell(12).setCellValue(ssDetail.getAvgSalesAll()+"");
739 
				row.createCell(13).setCellValue(ssDetail.getSalesHistoryWS());
740 
				row.createCell(14).setCellValue(ssDetail.getAvgSalesWS()+"");
741 
				row.createCell(15).setCellValue(ssDetail.getSalesHistoryEbay());
742 
				row.createCell(16).setCellValue(ssDetail.getAvgSalesEbay()+"");
743 
				row.createCell(17).setCellValue(ssDetail.getSalesHistorySD());
744 
				row.createCell(18).setCellValue(ssDetail.getAvgSalesSD()+"");
745 
				row.createCell(19).setCellValue(ssDetail.getSalesHistoryFK());
746 
				row.createCell(20).setCellValue(ssDetail.getAvgSalesFK()+"");
747 
				row.createCell(21).setCellValue(ssDetail.getSalesHistoryMFN());
748 
				row.createCell(22).setCellValue(ssDetail.getAvgSalesMFN());
749 
				i++;
750 
			}
751 
 
752 
			row = slowMovingSheet.createRow(0);
753 
 
754 
			row.createCell(0).setCellValue("ItemId");
755 
			row.createCell(1).setCellValue("Category");
756 
			row.createCell(2).setCellValue("ProductGroup");
757 
			row.createCell(3).setCellValue("Brand");
758 
			row.createCell(4).setCellValue("Model Name");
759 
			row.createCell(5).setCellValue("Model Number");
760 
			row.createCell(6).setCellValue("Color");
761 
			row.createCell(7).setCellValue("Product");
762 
			row.createCell(8).setCellValue("Stock Qty");
763 
			row.createCell(9).setCellValue("Stock Value");
764 
			row.createCell(10).setCellValue("NOD Stock");
765 
			row.createCell(11).setCellValue("Sales History(All-Sources)");
766 
			row.createCell(12).setCellValue("Avg Sales(All-Sources)");
767 
			row.createCell(13).setCellValue("Sales History(Website)");
768 
			row.createCell(14).setCellValue("Avg Sales(Website)");
769 
			row.createCell(15).setCellValue("Sales History(Ebay)");
770 
			row.createCell(16).setCellValue("Avg Sales(Ebay)");
771 
			row.createCell(17).setCellValue("Sales History(Snapdeal)");
772 
			row.createCell(18).setCellValue("Avg Sales(Snapdeal)");
773 
			row.createCell(19).setCellValue("Sales History(Flipkart)");
774 
			row.createCell(20).setCellValue("Avg Sales(Flipkart)");
775 
			row.createCell(21).setCellValue("Sales History(Amazon-MFN)");
776 
			row.createCell(22).setCellValue("Avg Sales(Amazon-MFN)");
777 
 
778 
			i=1;
779 
			for(StockSaleDetail ssDetail : slowMovingStockSaleList){
12179 manish.sha 780 
				row = slowMovingSheet.createRow(i);
12166 manish.sha 781 
				row.createCell(0).setCellValue(ssDetail.getItemId()+"");
782 
				row.createCell(1).setCellValue(ssDetail.getCategory());
783 
				row.createCell(2).setCellValue(ssDetail.getProductGroup());
784 
				row.createCell(3).setCellValue(ssDetail.getBrand());
785 
				row.createCell(4).setCellValue(ssDetail.getModelName());
786 
				row.createCell(5).setCellValue(ssDetail.getModelNumber());
787 
				row.createCell(6).setCellValue(ssDetail.getColor());
788 
				row.createCell(7).setCellValue(ssDetail.getBrand()+" "+ssDetail.getModelName()+" "+ssDetail.getModelNumber()+" "+ssDetail.getColor());
789 
				row.createCell(8).setCellValue(ssDetail.getStockQuantity()+"");
790 
				row.createCell(9).setCellValue(ssDetail.getStockValue()+"");
791 
				row.createCell(10).setCellValue(ssDetail.getNodStock()+"");
792 
				row.createCell(11).setCellValue(ssDetail.getSalesHistoryAll());
793 
				row.createCell(12).setCellValue(ssDetail.getAvgSalesAll()+"");
794 
				row.createCell(13).setCellValue(ssDetail.getSalesHistoryWS());
795 
				row.createCell(14).setCellValue(ssDetail.getAvgSalesWS()+"");
796 
				row.createCell(15).setCellValue(ssDetail.getSalesHistoryEbay());
797 
				row.createCell(16).setCellValue(ssDetail.getAvgSalesEbay()+"");
798 
				row.createCell(17).setCellValue(ssDetail.getSalesHistorySD());
799 
				row.createCell(18).setCellValue(ssDetail.getAvgSalesSD()+"");
800 
				row.createCell(19).setCellValue(ssDetail.getSalesHistoryFK());
801 
				row.createCell(20).setCellValue(ssDetail.getAvgSalesFK()+"");
802 
				row.createCell(21).setCellValue(ssDetail.getSalesHistoryMFN());
803 
				row.createCell(22).setCellValue(ssDetail.getAvgSalesMFN());
804 
				i++;
805 
			}
806 
 
12177 manish.sha 807 
			FileOutputStream out = null;
12166 manish.sha 808 
			if(mobTab){
809 
				out = new FileOutputStream(stockSalesReportMT);
810 
			} else {
811 
				out = new FileOutputStream(stockSalesReportEMT);
812 
			}
12177 manish.sha 813 
			workbook.write(out);
12166 manish.sha 814 
			out.close();
815 
			System.out.println("Excel written successfully..");
816 
		}catch(Exception e){
817 
			e.printStackTrace();
818 
		}
819 
 
820 
	}
821 
 
822 
	public static void main(String[] args) {
823 
		createAndMailReport();
824 
	}
825 
 
826 
}