Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
13584 manish.sha 1 
package in.shop2020.serving.services;
2 
 
3 
import java.io.BufferedWriter;
4 
import java.io.FileNotFoundException;
5 
import java.io.FileOutputStream;
6 
import java.io.FileWriter;
7 
import java.io.IOException;
8 
import java.util.ArrayList;
9 
import java.util.Collections;
10 
import java.util.HashMap;
11 
import java.util.List;
12 
import java.util.Map;
13 
import java.util.Map.Entry;
14 
 
15 
import javax.mail.MessagingException;
16 
 
17 
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
18 
import org.apache.poi.hssf.usermodel.HSSFFont;
19 
import org.apache.poi.hssf.usermodel.HSSFRow;
20 
import org.apache.poi.hssf.usermodel.HSSFSheet;
21 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
22 
import org.apache.poi.hssf.util.HSSFColor;
23 
import org.apache.thrift.TException;
24 
 
25 
import in.shop2020.model.v1.catalog.CatalogServiceException;
26 
import in.shop2020.model.v1.catalog.Category;
27 
import in.shop2020.model.v1.catalog.Item;
28 
import in.shop2020.model.v1.order.FlipkartFaSalesSnapshot;
29 
import in.shop2020.thrift.clients.CatalogClient;
30 
import in.shop2020.utils.EmailUtils;
31 
import in.shop2020.utils.FASalesSnapshot;
32 
import in.shop2020.utils.FkExcelRow;
33 
import in.shop2020.utils.FkSaleComparator;
34 
import in.shop2020.utils.FkStockEstValueComparator;
35 
import in.shop2020.utils.FlipkartConsumer;
36 
import in.shop2020.utils.GmailUtils;
37 
 
38 
public class CreateSendFAStockEstReportEMT{
39 
 
40 
	private static final String FLIPKART_FBA_SHEET = "/tmp/Except-Mobiles-Tablets-FA-Stock-Estimation.xls";
41 
	private static final String FLIPKART_FBA_SHEET_BODY = "/tmp/Except-Mobiles-Tablets-FA-Stock-Estimation-body.html";
42 
 
43 
	public static void main(String[] args) throws IOException, MessagingException {
44 
		CatalogClient catalogServiceClient = null;
45 
        try {
46 
            catalogServiceClient = new CatalogClient("catalog_service_server_host_amazon","catalog_service_server_port");
47 
        	//catalogServiceClient = new CatalogClient();
48 
        } catch (Exception e) {
49 
            e.printStackTrace();
50 
        }
51 
 
52 
        in.shop2020.model.v1.catalog.CatalogService.Client catalogClient   = catalogServiceClient.getClient();
53 
 
54 
	    Map<Long, Long> currentFaInventoryMap = FlipkartConsumer.getCurrentFAInventory();
55 
	    Map<Long,Double> currentFAInventorySellingPriceMap = FlipkartConsumer.getCurrentFAInventorySellingPrice();
56 
	    List<FlipkartFaSalesSnapshot> salessnapshots = FlipkartConsumer.getFlipkartFbaSalesSnapshotForDays(4);
57 
	    Map<Long, Map<Long,FASalesSnapshot>> itemId5daysSale = new HashMap<Long, Map<Long,FASalesSnapshot>>();
58 
	    boolean skip = false;
59 
	    for(FlipkartFaSalesSnapshot salessnapshot : salessnapshots) {
60 
	    	try {
61 
                if(itemId5daysSale.containsKey(salessnapshot.getItem_id())) {
62 
                	for (Entry<Long, FASalesSnapshot> saleentry : itemId5daysSale.get(salessnapshot.getItem_id()).entrySet()) {
63 
                		if(saleentry.getValue().getDateOfSale().longValue() == salessnapshot.getDateOfSale()) {
64 
                			if(!salessnapshot.isIsOutOfStock()){
65 
                				if(saleentry.getValue().getTotalOrderCount()==-1){
66 
                                    saleentry.getValue().setTotalOrderCount(salessnapshot.getTotalOrderCount());
67 
                                    saleentry.getValue().setTotalSale((float) (salessnapshot.getTotalSale()));
68 
 
69 
                                }
70 
                                else{
71 
                                    saleentry.getValue().setTotalOrderCount(saleentry.getValue().getTotalOrderCount()+ salessnapshot.getTotalOrderCount());
72 
                                    saleentry.getValue().setTotalSale((float) (saleentry.getValue().getTotalSale() + salessnapshot.getTotalSale()));
73 
                                }
74 
                                itemId5daysSale.get(salessnapshot.getItem_id()).put(saleentry.getValue().getDateOfSale(),saleentry.getValue());
75 
                			}
76 
                			skip = true;
77 
                            break;
78 
                		}
79 
                	}
80 
                	if(!skip){
81 
                		FASalesSnapshot faSalesSnapshot = new FASalesSnapshot();
82 
                		if(!salessnapshot.isIsOutOfStock()) {
83 
	                        faSalesSnapshot.setTotalOrderCount(salessnapshot.getTotalOrderCount());
84 
	                        faSalesSnapshot.setTotalSale((float) salessnapshot.getTotalSale());
85 
	                        faSalesSnapshot.setDateOfSale(salessnapshot.getDateOfSale());
86 
	                    }else {
87 
	                    	faSalesSnapshot.setTotalOrderCount(-1);
88 
	                    	faSalesSnapshot.setTotalSale((float) 0);
89 
	                    	faSalesSnapshot.setDateOfSale(salessnapshot.getDateOfSale());
90 
                        }
91 
                		itemId5daysSale.get(salessnapshot.getItem_id()).put(faSalesSnapshot.getDateOfSale(),faSalesSnapshot);
92 
                	}
93 
                	skip=false;
94 
                }
95 
                else{
96 
                	Map<Long,FASalesSnapshot> last5daysSale = new HashMap<Long,FASalesSnapshot>();
97 
                	FASalesSnapshot faSalesSnapshot = new FASalesSnapshot();
98 
                    if (!salessnapshot.isIsOutOfStock()) {
99 
                    	faSalesSnapshot.setTotalOrderCount(salessnapshot.getTotalOrderCount());
100 
                    	faSalesSnapshot.setTotalSale((float) salessnapshot.getTotalSale());
101 
                    	faSalesSnapshot.setDateOfSale(salessnapshot.getDateOfSale());
102 
                    }
103 
                    else {
104 
                    	faSalesSnapshot.setTotalOrderCount(-1);
105 
                    	faSalesSnapshot.setTotalSale((float) 0);
106 
                    	faSalesSnapshot.setDateOfSale(salessnapshot.getDateOfSale());
107 
                    }
108 
                    last5daysSale.put(faSalesSnapshot.getDateOfSale(),faSalesSnapshot);
109 
                    itemId5daysSale.put(salessnapshot.getItem_id(),last5daysSale);
110 
                }
111 
	    	} catch (Exception e) {
112 
                e.printStackTrace();
113 
            }
114 
	    }
115 
 
116 
	    HSSFWorkbook hwb = new HSSFWorkbook();
117 
        HSSFSheet sheet1 = hwb.createSheet("MOVING");
118 
        HSSFSheet sheet2 = hwb.createSheet("SLOW-MOVING");
119 
        HSSFSheet sheet3 = hwb.createSheet("NOT-MOVING");
120 
 
121 
        int i=0;
122 
        HSSFRow rowhead1 = sheet1.createRow((short) 0);
123 
        HSSFRow rowhead2 = sheet2.createRow((short) 0);
124 
        HSSFRow rowhead3 = sheet3.createRow((short) 0);
125 
 
126 
        rowhead1.createCell(i).setCellValue("ITEM-ID");
127 
        rowhead1.createCell(++i).setCellValue("BRAND");
128 
        rowhead1.createCell(++i).setCellValue("CATEGORY");
129 
        rowhead1.createCell(++i).setCellValue("PRODUCT-GROUP");
130 
        rowhead1.createCell(++i).setCellValue("PRODUCT-NAME");
131 
        rowhead1.createCell(++i).setCellValue("SALES-HISTORY");
132 
        rowhead1.createCell(++i).setCellValue("AVG-SALE");
133 
        rowhead1.createCell(++i).setCellValue("STOCK-QUANTITY");
134 
        rowhead1.createCell(++i).setCellValue("SELLING-PRICE");
135 
        rowhead1.createCell(++i).setCellValue("STOCK-VALUE");
136 
        rowhead1.createCell(++i).setCellValue("NOD-STOCK");
137 
        rowhead1.createCell(++i).setCellValue("MOVEMENT-CATEGORY");
138 
 
13589 manish.sha 139 
        i=0;
140 
 
13584 manish.sha 141 
        rowhead2.createCell(i).setCellValue("ITEM-ID");
142 
        rowhead2.createCell(++i).setCellValue("BRAND");
143 
        rowhead2.createCell(++i).setCellValue("CATEGORY");
144 
        rowhead2.createCell(++i).setCellValue("PRODUCT-GROUP");
145 
        rowhead2.createCell(++i).setCellValue("PRODUCT-NAME");
146 
        rowhead2.createCell(++i).setCellValue("SALES-HISTORY");
147 
        rowhead2.createCell(++i).setCellValue("AVG-SALE");
148 
        rowhead2.createCell(++i).setCellValue("STOCK-QUANTITY");
149 
        rowhead2.createCell(++i).setCellValue("SELLING-PRICE");
150 
        rowhead2.createCell(++i).setCellValue("STOCK-VALUE");
151 
        rowhead2.createCell(++i).setCellValue("NOD-STOCK");
152 
        rowhead2.createCell(++i).setCellValue("MOVEMENT-CATEGORY");
153 
 
13589 manish.sha 154 
        i=0;
155 
 
13584 manish.sha 156 
        rowhead3.createCell(i).setCellValue("ITEM-ID");
157 
        rowhead3.createCell(++i).setCellValue("BRAND");
158 
        rowhead3.createCell(++i).setCellValue("CATEGORY");
159 
        rowhead3.createCell(++i).setCellValue("PRODUCT-GROUP");
160 
        rowhead3.createCell(++i).setCellValue("PRODUCT-NAME");
161 
        rowhead3.createCell(++i).setCellValue("SALES-HISTORY");
162 
        rowhead3.createCell(++i).setCellValue("AVG-SALE");
163 
        rowhead3.createCell(++i).setCellValue("STOCK-QUANTITY");
164 
        rowhead3.createCell(++i).setCellValue("SELLING-PRICE");
165 
        rowhead3.createCell(++i).setCellValue("STOCK-VALUE");
166 
        rowhead3.createCell(++i).setCellValue("NOD-STOCK");
167 
        rowhead3.createCell(++i).setCellValue("MOVEMENT-CATEGORY");
168 
 
169 
        List<FkExcelRow> Moving_List = new ArrayList<FkExcelRow>();
170 
        List<FkExcelRow> Replenishment_Items = new ArrayList<FkExcelRow>();
171 
        List<FkExcelRow> Slow_Moving_Items = new ArrayList<FkExcelRow>();
172 
        List<FkExcelRow> Not_Moving_Item_List = new ArrayList<FkExcelRow>();
173 
 
174 
 
175 
        for (Entry<Long, Map<Long, FASalesSnapshot>> entry : itemId5daysSale.entrySet()) {
176 
        	Item item = null;
177 
        	Category category = null;
178 
        	try {
179 
                //System.out.println("Item ID is " + entry.getKey());
180 
                item = catalogClient.getItem(entry.getKey());
181 
                if (item.getId() == 0) {
182 
                    continue;
183 
                }
184 
                if (!(item.getProductGroup().equalsIgnoreCase("Mobile Phone") || item.getProductGroup().equalsIgnoreCase("Handsets") || item.getProductGroup().equalsIgnoreCase("Tablets") ||
185 
                        item.getProductGroup().equalsIgnoreCase("Compact Cameras") ||
186 
                        item.getProductGroup().equalsIgnoreCase("DSLR Cameras")  ||
187 
                        item.getProductGroup().equalsIgnoreCase("Cameras") ||
188 
                        item.getProductGroup().equalsIgnoreCase("Digital Camera") ||
189 
                        item.getProductGroup().equalsIgnoreCase("Lenses"))) {
190 
                }
191 
                else {
192 
                    continue;
193 
                }
194 
                if (item.isSetCategory() && item.getCategory() != 0) {
195 
                    category = catalogClient.getCategory(item.getCategory());
196 
                }
197 
        	}
198 
        	catch (CatalogServiceException e) {
199 
                e.printStackTrace();
200 
            } catch (TException e) {
201 
                e.printStackTrace();
202 
            }
203 
 
204 
            FkExcelRow fkExcelRow = new FkExcelRow();
205 
            int count = 1;
206 
            Double total_sale = 0.0;
207 
            int total_sale_days = 0;
208 
            float avg_total_sale = 0;
209 
            StringBuilder total_days_sale = new StringBuilder();
210 
 
211 
            List<FASalesSnapshot> fiveDaySale = new ArrayList<FASalesSnapshot>(entry.getValue().values());
212 
            Collections.sort(fiveDaySale, new FkSaleComparator());
13602 manish.sha 213 
            long stockQuantity = 0;
214 
            if(currentFaInventoryMap.containsKey(entry.getKey())){
215 
            	stockQuantity = currentFaInventoryMap.get(entry.getKey());
216 
            }else{
217 
            	stockQuantity = 0;
218 
            }
13584 manish.sha 219 
 
220 
            for(FASalesSnapshot sale : fiveDaySale) {
221 
 
222 
            	if (sale.getTotalOrderCount() != -1) {
223 
                    total_days_sale.append(sale.getTotalOrderCount());
224 
                    avg_total_sale = avg_total_sale + sale.getTotalOrderCount();
225 
                    total_sale = total_sale + sale.getTotalSale();
226 
                    total_sale_days++;
227 
                } else {
228 
                    total_days_sale.append("X");
229 
                }
230 
                if (count != entry.getValue().size()) {
231 
                    total_days_sale.append("-");
232 
                }
233 
                count++;
234 
            }
235 
            if(entry.getValue().size()<5){
236 
	            while(count<=5){
237 
	            	total_days_sale.append("-");
13602 manish.sha 238 
	            	if(stockQuantity>0){
239 
	            		total_days_sale.append("0");
240 
	            		total_sale_days++;
241 
	            	} else {
242 
	            		total_days_sale.append("X");
243 
	            	}
13584 manish.sha 244 
	            	count++;
245 
	            }
246 
            }
247 
 
248 
 
249 
            float total_sale_avg = 0;
250 
            if (total_sale_days != 0) {
251 
                total_sale_avg = avg_total_sale / total_sale_days;
252 
            }
253 
            fkExcelRow.setItem_id(entry.getKey());
254 
 
255 
            float days_of_stock = 0;
13602 manish.sha 256 
 
13584 manish.sha 257 
            double sellingPrice = 0.0;//
258 
            if(currentFAInventorySellingPriceMap.containsKey(entry.getKey())){
259 
            	sellingPrice = currentFAInventorySellingPriceMap.get(entry.getKey());
260 
            }else{
261 
            	sellingPrice = total_sale/avg_total_sale;
262 
            }
263 
            double stockValue = stockQuantity*sellingPrice;
264 
            if(total_sale_avg>0){
265 
            	days_of_stock = (stockQuantity/total_sale_avg);
266 
            }
267 
 
268 
            fkExcelRow.setBrand(item.getBrand());
269 
            fkExcelRow.setProductGroup(item.getProductGroup());
270 
            fkExcelRow.setCategory(category.getLabel());
271 
            fkExcelRow.setProductName(item.getModelName()+" "+item.getModelNumber()+" "+item.getColor());
272 
            fkExcelRow.setSalesHistory(total_days_sale.toString());
273 
            fkExcelRow.setAvgSale(total_sale_avg);
274 
        	fkExcelRow.setStockQuantity(stockQuantity);
275 
        	fkExcelRow.setSellingPrice(sellingPrice);
276 
        	fkExcelRow.setStockValue(stockValue);
277 
        	fkExcelRow.setNodStock(days_of_stock);
278 
 
279 
        	if (total_sale_avg > 0) {
13607 manish.sha 280 
	        	if(days_of_stock<=20){
13584 manish.sha 281 
	        		fkExcelRow.setMovementCategory("MOVING");
282 
	        		Moving_List.add(fkExcelRow);
283 
	        		Replenishment_Items.add(fkExcelRow);
284 
	        	}
285 
	        	if(days_of_stock>20){
286 
	        		fkExcelRow.setMovementCategory("SLOW-MOVING");
287 
	        		Slow_Moving_Items.add(fkExcelRow);
288 
	        	}
289 
        	}else{
290 
        		if(total_sale_avg == 0 && stockQuantity>0){
291 
	        		fkExcelRow.setMovementCategory("NON-MOVING");
292 
	        		Not_Moving_Item_List.add(fkExcelRow);
293 
        		}
294 
        	}
295 
 
296 
        }
297 
 
298 
        Collections.sort(Moving_List, new FkStockEstValueComparator());
299 
        Collections.sort(Replenishment_Items, new FkStockEstValueComparator());
300 
        Collections.sort(Slow_Moving_Items, new FkStockEstValueComparator());
301 
        Collections.sort(Not_Moving_Item_List, new FkStockEstValueComparator());
302 
 
303 
        HSSFRow row = null;
304 
        int iterator = 1;
305 
        long movingStock = 0;
306 
        double movingStockValue = 0;
307 
 
308 
        for (FkExcelRow excelrow : Moving_List){
309 
        	movingStock = movingStock+excelrow.getStockQuantity();
310 
        	movingStockValue = movingStockValue+excelrow.getStockValue();
311 
        	row = sheet1.createRow((short) iterator);
312 
            HSSFCellStyle style = hwb.createCellStyle();
313 
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
314 
            HSSFFont font = hwb.createFont();
315 
            style.setFillForegroundColor(HSSFColor.WHITE.index);
316 
            font.setColor(HSSFColor.BLACK.index);
317 
            style.setFont(font);
318 
            i=0;
319 
            row.createCell(i).setCellValue(excelrow.getItem_id());
320 
            row.createCell(++i).setCellValue(excelrow.getBrand());
321 
            row.createCell(++i).setCellValue(excelrow.getCategory());
322 
            row.createCell(++i).setCellValue(excelrow.getProductGroup());
323 
            row.createCell(++i).setCellValue(excelrow.getProductName());
324 
            row.createCell(++i).setCellValue(excelrow.getSalesHistory());
325 
            row.createCell(++i).setCellValue(String.format("%.2f",excelrow.getAvgSale()));
326 
            row.createCell(++i).setCellValue(excelrow.getStockQuantity());
327 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getSellingPrice()));
328 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getStockValue()));
329 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getNodStock()));
330 
            row.createCell(++i).setCellValue(excelrow.getMovementCategory());
331 
            iterator++;
332 
        }
333 
        iterator = 1;
334 
        double not_moving_value = 0;
335 
        long not_moving_count = 0;
336 
 
337 
        for (FkExcelRow excelrow : Not_Moving_Item_List) {
338 
        	not_moving_value = not_moving_value + excelrow.getStockValue();
339 
            not_moving_count = not_moving_count + excelrow.getStockQuantity();
340 
            row = sheet3.createRow((short) iterator);
341 
            HSSFCellStyle style = hwb.createCellStyle();
342 
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
343 
            HSSFFont font = hwb.createFont();
344 
            style.setFillForegroundColor(HSSFColor.WHITE.index);
345 
            font.setColor(HSSFColor.BLACK.index);
346 
            style.setFont(font);
347 
            i=0;
348 
            row.createCell(i).setCellValue(excelrow.getItem_id());
349 
            row.createCell(++i).setCellValue(excelrow.getBrand());
350 
            row.createCell(++i).setCellValue(excelrow.getCategory());
351 
            row.createCell(++i).setCellValue(excelrow.getProductGroup());
352 
            row.createCell(++i).setCellValue(excelrow.getProductName());
353 
            row.createCell(++i).setCellValue(excelrow.getSalesHistory());
354 
            row.createCell(++i).setCellValue(String.format("%.2f",excelrow.getAvgSale()));
355 
            row.createCell(++i).setCellValue(excelrow.getStockQuantity());
356 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getSellingPrice()));
357 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getStockValue()));
358 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getNodStock()));
359 
            row.createCell(++i).setCellValue(excelrow.getMovementCategory());
360 
            iterator++;
361 
        }
362 
 
363 
        iterator = 1;
364 
        double slow_moving_value = 0;
365 
        long slow_moving_count = 0;
366 
 
367 
        for (FkExcelRow excelrow : Slow_Moving_Items) {
368 
        	slow_moving_value = slow_moving_value + excelrow.getStockValue();
369 
        	slow_moving_count = slow_moving_count + excelrow.getStockQuantity();
370 
            row = sheet2.createRow((short) iterator);
371 
            HSSFCellStyle style = hwb.createCellStyle();
372 
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
373 
            HSSFFont font = hwb.createFont();
374 
            style.setFillForegroundColor(HSSFColor.WHITE.index);
375 
            font.setColor(HSSFColor.BLACK.index);
376 
            style.setFont(font);
377 
            i=0;
378 
            row.createCell(i).setCellValue(excelrow.getItem_id());
379 
            row.createCell(++i).setCellValue(excelrow.getBrand());
380 
            row.createCell(++i).setCellValue(excelrow.getCategory());
381 
            row.createCell(++i).setCellValue(excelrow.getProductGroup());
382 
            row.createCell(++i).setCellValue(excelrow.getProductName());
383 
            row.createCell(++i).setCellValue(excelrow.getSalesHistory());
384 
            row.createCell(++i).setCellValue(String.format("%.2f",excelrow.getAvgSale()));
385 
            row.createCell(++i).setCellValue(excelrow.getStockQuantity());
386 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getSellingPrice()));
387 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getStockValue()));
388 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getNodStock()));
389 
            row.createCell(++i).setCellValue(excelrow.getMovementCategory());
390 
            iterator++;
391 
        }
392 
 
393 
        FileOutputStream fileOut = null;
394 
        try {
395 
            fileOut = new FileOutputStream(FLIPKART_FBA_SHEET);
396 
        } catch (FileNotFoundException e) {
397 
            e.printStackTrace();
398 
        }
399 
        try {
400 
            hwb.write(fileOut);
401 
        } catch (IOException e) {
402 
            e.printStackTrace();
403 
        }
404 
        try {
405 
            fileOut.close();
406 
        } catch (IOException e) {
407 
            e.printStackTrace();
408 
        }
409 
 
410 
        StringBuffer sb = new StringBuffer();
411 
        sb.append("<html><table border=\"1\" align=\"center\">"
412 
                + "<caption><b>" + "ITEMS TO REPLENISH" + "</b></caption>"
413 
                + "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>" + "<td><b>"
414 
                + "BRAND" + "</b></td>" + "<td><b>" + "CATEGORY" + "</b></td>"
415 
                + "<td><b>" + "PRODUCT-GROUP" + "</b></td>" + "<td><b>"
416 
                + "PRODUCT-NAME" + "</b></td>" + "<td><b>" + "SALES-HISTORY"
417 
                + "</b></td>" + "<td><b>" + "AVG-SALE" + "</b></td>"
418 
                + "</b></td>" + "<td><b>" + "CURRENT-STOCK" + "</b></td>"
419 
                + "<td><b>" + "NO-OF-DAYS-STOCK" + "</b></td>"
420 
                + "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "</tr>");
421 
 
422 
        int count = 0;
423 
        for(FkExcelRow excelRow: Replenishment_Items){
424 
        	if (count < 10) {
425 
                sb.append("<tr>" + "<td>"+ excelRow.getItem_id() +"</td>"
426 
                		+"<td>"+ excelRow.getBrand() +"</td>"
427 
                		+"<td>"+ excelRow.getCategory() +"</td>"
428 
                		+"<td>"+ excelRow.getProductGroup() +"</td>"
429 
                		+"<td>"+ excelRow.getProductName() +"</td>"
430 
                		+"<td>"+ excelRow.getSalesHistory() +"</td>"
431 
                		+"<td>"+ Math.round(excelRow.getAvgSale() * 100.0) / 100.0 +"</td>"
432 
                		+"<td>"+ excelRow.getStockQuantity() +"</td>"
433 
                		+"<td>"+ String.format("%.0f",excelRow.getNodStock()) +"</td>"
434 
                		+"<td>"+ String.format("%.0f",excelRow.getStockValue()) +"</td></tr>");
435 
                count++;
436 
        	}else{
437 
        		break;
438 
        	}
439 
        }
440 
        sb.append("</table>");
441 
        sb.append("<table border=\"1\" align=\"center\">"
442 
                + "<caption><b>" + "SLOW-MOVING-ITEMS" + "</b></caption>"
443 
                + "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>" + "<td><b>"
444 
                + "BRAND" + "</b></td>" + "<td><b>" + "CATEGORY" + "</b></td>"
445 
                + "<td><b>" + "PRODUCT-GROUP" + "</b></td>" + "<td><b>"
446 
                + "PRODUCT-NAME" + "</b></td>" + "<td><b>" + "SALES-HISTORY"
447 
                + "</b></td>" + "<td><b>" + "AVG-SALE" + "</b></td>"
448 
                + "</b></td>" + "<td><b>" + "CURRENT-STOCK" + "</b></td>"
449 
                + "<td><b>" + "NO-OF-DAYS-STOCK" + "</b></td>"
450 
                + "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "</tr>");
451 
 
452 
        count = 0;
453 
        for(FkExcelRow excelRow : Slow_Moving_Items){
454 
        	if (count < 10) {
455 
                sb.append("<tr>" + "<td>"+ excelRow.getItem_id() +"</td>"
456 
                		+"<td>"+ excelRow.getBrand() +"</td>"
457 
                		+"<td>"+ excelRow.getCategory() +"</td>"
458 
                		+"<td>"+ excelRow.getProductGroup() +"</td>"
459 
                		+"<td>"+ excelRow.getProductName() +"</td>"
460 
                		+"<td>"+ excelRow.getSalesHistory() +"</td>"
461 
                		+"<td>"+ Math.round(excelRow.getAvgSale() * 100.0) / 100.0 +"</td>"
462 
                		+"<td>"+ excelRow.getStockQuantity() +"</td>"
463 
                		+"<td>"+ String.format("%.0f",excelRow.getNodStock()) +"</td>"
464 
                		+"<td>"+ String.format("%.0f",excelRow.getStockValue()) +"</td></tr>");
465 
                count++;
466 
        	}else{
467 
        		break;
468 
        	}
469 
        }
470 
        sb.append("</table>");
471 
        sb.append("<table border=\"1\" align=\"center\">"
472 
                + "<caption><b>" + "NON-MOVING-ITEMS" + "</b></caption>"
473 
                + "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>" + "<td><b>"
474 
                + "BRAND" + "</b></td>" + "<td><b>" + "CATEGORY" + "</b></td>"
475 
                + "<td><b>" + "PRODUCT-GROUP" + "</b></td>" + "<td><b>"
476 
                + "PRODUCT-NAME" + "</b></td>" + "<td><b>" + "SALES-HISTORY"
477 
                + "</b></td>" + "<td><b>" + "AVG-SALE" + "</b></td>"
478 
                + "</b></td>" + "<td><b>" + "CURRENT-STOCK" + "</b></td>"
479 
                + "<td><b>" + "NO-OF-DAYS-STOCK" + "</b></td>"
480 
                + "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "</tr>");
481 
 
482 
        count = 0;
483 
        for(FkExcelRow excelRow : Not_Moving_Item_List){
484 
        	if (count < 10) {
485 
                sb.append("<tr>" + "<td>"+ excelRow.getItem_id() +"</td>"
486 
                		+"<td>"+ excelRow.getBrand() +"</td>"
487 
                		+"<td>"+ excelRow.getCategory() +"</td>"
488 
                		+"<td>"+ excelRow.getProductGroup() +"</td>"
489 
                		+"<td>"+ excelRow.getProductName() +"</td>"
490 
                		+"<td>"+ excelRow.getSalesHistory() +"</td>"
491 
                		+"<td>"+ Math.round(excelRow.getAvgSale() * 100.0) / 100.0 +"</td>"
492 
                		+"<td>"+ excelRow.getStockQuantity() +"</td>"
493 
                		+"<td>"+ String.format("%.0f",excelRow.getNodStock()) +"</td>"
494 
                		+"<td>"+ String.format("%.0f",excelRow.getStockValue()) +"</td></tr>");
495 
                count++;
496 
        	}else{
497 
        		break;
498 
        	}
499 
        }
500 
        sb.append("</table></html>");
501 
        StringBuffer stockSummary = new StringBuffer();
502 
        stockSummary.append("<html><table border=\"1\" align=\"center\">" +
503 
                "<caption><b>"+ "STOCK SUMMARY" + "</b></caption>" +
504 
                "<tr>" + "<td><b>"+ "STOCK-TYPE" + "</b></td>" +"<td><b>"+
505 
                "NO-OF-SKUS" + "</b></td>" +"<td><b>"
506 
                + "QTY" + "</b></td>" +"<td><b>" + "VALUE"+"</tr>");
507 
        stockSummary.append("<tr>" + "<td><b>"+ "MOVING" + "</b></td>" +
508 
                "<td><b>" +Moving_List.size()+ "</b></td>" +"<td><b>"
509 
                + "</b>"+movingStock+"</td>" +"<td><b>" + String.format("%.0f",movingStockValue)+"</tr>");
510 
        stockSummary.append("<tr>" + "<td><b>"+ "SLOW-MOVING" + "</b></td>"+
511 
                "<td><b>" +    Slow_Moving_Items.size() + "</b></td>" +"<td><b>"
512 
                + "</b>"+slow_moving_count+"</td>" +"<td><b>" +String.format("%.0f",slow_moving_value)+"</tr>");
513 
        stockSummary.append("<tr>" + "<td><b>"+ "NOT-MOVING" + "</b></td>" +
514 
                "<td><b>" +    Not_Moving_Item_List.size() + "</b></td>" +"<td><b>"
515 
                + "</b>"+not_moving_count+"</td>" +"<td><b>" +String.format("%.0f",not_moving_value)+"</tr>");
516 
        stockSummary.append("<tr>" + "<td><b>"+ "TOTAL" + "</b></td>"+
517 
                "<td><b>" +  (Moving_List.size() +Slow_Moving_Items.size()+ Not_Moving_Item_List.size()) + "</b></td>" +"<td><b>"
518 
                + "</b>"+(movingStock+not_moving_count+slow_moving_count)+"</td>" +"<td><b>" +String.format("%.0f",(movingStockValue+not_moving_value+slow_moving_value))+"</tr>");
519 
        stockSummary.append("</table></html>");
520 
 
521 
        BufferedWriter out = new BufferedWriter(new FileWriter(
522 
        		FLIPKART_FBA_SHEET_BODY));
523 
        out.write(stockSummary.toString()+sb.toString());
524 
        out.flush();
525 
        out.close();
526 
        String emailFromAddress = "build-staging@shop2020.in";
527 
        String password = "shop2020";
528 
 
529 
        String[] sendTo = new String[]{"rajneesh.arora@shop2020.in","manish.sharma@shop2020.in","chaitnaya.vats@shop2020.in","khushal.bhatia@shop2020.in","manoj.kumar@shop2020.in","amit.gupta@shop2020.in","manoj.pal@shop2020.in"};
530 
        //String[] sendTo = new String[] { "manish.sharma@shop2020.in" };
531 
        String emailSubjectTxt = "FA Stock Estimation Sheet Except (Mobiles and Tablets)";
532 
        try {
533 
            GmailUtils mailer = new GmailUtils();
534 
            mailer.sendSSLMessage(sendTo, emailSubjectTxt, "", emailFromAddress, password, FLIPKART_FBA_SHEET, FLIPKART_FBA_SHEET_BODY);
535 
        } catch (Exception ex) {
536 
            ex.printStackTrace();
537 
            EmailUtils emailUtils = new EmailUtils();
538 
            emailUtils.sendEmail(sendTo, emailSubjectTxt, "", emailFromAddress, FLIPKART_FBA_SHEET, FLIPKART_FBA_SHEET_BODY);
539 
        }
540 
	}
541 
}