Subversion Repositories SmartDukaan

Rev

Rev 13584 | Rev 13602 | 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());
213 
 
214 
            for(FASalesSnapshot sale : fiveDaySale) {
215 
 
216 
            	if (sale.getTotalOrderCount() != -1) {
217 
                    total_days_sale.append(sale.getTotalOrderCount());
218 
                    avg_total_sale = avg_total_sale + sale.getTotalOrderCount();
219 
                    total_sale = total_sale + sale.getTotalSale();
220 
                    total_sale_days++;
221 
                } else {
222 
                    total_days_sale.append("X");
223 
                }
224 
                if (count != entry.getValue().size()) {
225 
                    total_days_sale.append("-");
226 
                }
227 
                count++;
228 
            }
229 
            if(entry.getValue().size()<5){
230 
	            while(count<=5){
231 
	            	total_days_sale.append("-");
232 
	            	total_days_sale.append("X");
233 
	            	count++;
234 
	            }
235 
            }
236 
 
237 
 
238 
            float total_sale_avg = 0;
239 
            if (total_sale_days != 0) {
240 
                total_sale_avg = avg_total_sale / total_sale_days;
241 
            }
242 
            fkExcelRow.setItem_id(entry.getKey());
243 
 
244 
            float days_of_stock = 0;
245 
            long stockQuantity = 0;
246 
            if(currentFaInventoryMap.containsKey(entry.getKey())){
247 
            	stockQuantity = currentFaInventoryMap.get(entry.getKey());
248 
            }else{
249 
            	stockQuantity = 0;
250 
            }
251 
            double sellingPrice = 0.0;//
252 
            if(currentFAInventorySellingPriceMap.containsKey(entry.getKey())){
253 
            	sellingPrice = currentFAInventorySellingPriceMap.get(entry.getKey());
254 
            }else{
255 
            	sellingPrice = total_sale/avg_total_sale;
256 
            }
257 
            double stockValue = stockQuantity*sellingPrice;
258 
            if(total_sale_avg>0){
259 
            	days_of_stock = (stockQuantity/total_sale_avg);
260 
            }
261 
 
262 
            fkExcelRow.setBrand(item.getBrand());
263 
            fkExcelRow.setProductGroup(item.getProductGroup());
264 
            fkExcelRow.setCategory(category.getLabel());
265 
            fkExcelRow.setProductName(item.getModelName()+" "+item.getModelNumber()+" "+item.getColor());
266 
            fkExcelRow.setSalesHistory(total_days_sale.toString());
267 
            fkExcelRow.setAvgSale(total_sale_avg);
268 
        	fkExcelRow.setStockQuantity(stockQuantity);
269 
        	fkExcelRow.setSellingPrice(sellingPrice);
270 
        	fkExcelRow.setStockValue(stockValue);
271 
        	fkExcelRow.setNodStock(days_of_stock);
272 
 
273 
        	if (total_sale_avg > 0) {
274 
	        	if(days_of_stock<=5){
275 
	        		fkExcelRow.setMovementCategory("MOVING");
276 
	        		Moving_List.add(fkExcelRow);
277 
	        		Replenishment_Items.add(fkExcelRow);
278 
	        	}
279 
	        	if(days_of_stock>20){
280 
	        		fkExcelRow.setMovementCategory("SLOW-MOVING");
281 
	        		Slow_Moving_Items.add(fkExcelRow);
282 
	        	}
283 
        	}else{
284 
        		if(total_sale_avg == 0 && stockQuantity>0){
285 
	        		fkExcelRow.setMovementCategory("NON-MOVING");
286 
	        		Not_Moving_Item_List.add(fkExcelRow);
287 
        		}
288 
        	}
289 
 
290 
        }
291 
 
292 
        Collections.sort(Moving_List, new FkStockEstValueComparator());
293 
        Collections.sort(Replenishment_Items, new FkStockEstValueComparator());
294 
        Collections.sort(Slow_Moving_Items, new FkStockEstValueComparator());
295 
        Collections.sort(Not_Moving_Item_List, new FkStockEstValueComparator());
296 
 
297 
        HSSFRow row = null;
298 
        int iterator = 1;
299 
        long movingStock = 0;
300 
        double movingStockValue = 0;
301 
 
302 
        for (FkExcelRow excelrow : Moving_List){
303 
        	movingStock = movingStock+excelrow.getStockQuantity();
304 
        	movingStockValue = movingStockValue+excelrow.getStockValue();
305 
        	row = sheet1.createRow((short) iterator);
306 
            HSSFCellStyle style = hwb.createCellStyle();
307 
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
308 
            HSSFFont font = hwb.createFont();
309 
            style.setFillForegroundColor(HSSFColor.WHITE.index);
310 
            font.setColor(HSSFColor.BLACK.index);
311 
            style.setFont(font);
312 
            i=0;
313 
            row.createCell(i).setCellValue(excelrow.getItem_id());
314 
            row.createCell(++i).setCellValue(excelrow.getBrand());
315 
            row.createCell(++i).setCellValue(excelrow.getCategory());
316 
            row.createCell(++i).setCellValue(excelrow.getProductGroup());
317 
            row.createCell(++i).setCellValue(excelrow.getProductName());
318 
            row.createCell(++i).setCellValue(excelrow.getSalesHistory());
319 
            row.createCell(++i).setCellValue(String.format("%.2f",excelrow.getAvgSale()));
320 
            row.createCell(++i).setCellValue(excelrow.getStockQuantity());
321 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getSellingPrice()));
322 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getStockValue()));
323 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getNodStock()));
324 
            row.createCell(++i).setCellValue(excelrow.getMovementCategory());
325 
            iterator++;
326 
        }
327 
        iterator = 1;
328 
        double not_moving_value = 0;
329 
        long not_moving_count = 0;
330 
 
331 
        for (FkExcelRow excelrow : Not_Moving_Item_List) {
332 
        	not_moving_value = not_moving_value + excelrow.getStockValue();
333 
            not_moving_count = not_moving_count + excelrow.getStockQuantity();
334 
            row = sheet3.createRow((short) iterator);
335 
            HSSFCellStyle style = hwb.createCellStyle();
336 
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
337 
            HSSFFont font = hwb.createFont();
338 
            style.setFillForegroundColor(HSSFColor.WHITE.index);
339 
            font.setColor(HSSFColor.BLACK.index);
340 
            style.setFont(font);
341 
            i=0;
342 
            row.createCell(i).setCellValue(excelrow.getItem_id());
343 
            row.createCell(++i).setCellValue(excelrow.getBrand());
344 
            row.createCell(++i).setCellValue(excelrow.getCategory());
345 
            row.createCell(++i).setCellValue(excelrow.getProductGroup());
346 
            row.createCell(++i).setCellValue(excelrow.getProductName());
347 
            row.createCell(++i).setCellValue(excelrow.getSalesHistory());
348 
            row.createCell(++i).setCellValue(String.format("%.2f",excelrow.getAvgSale()));
349 
            row.createCell(++i).setCellValue(excelrow.getStockQuantity());
350 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getSellingPrice()));
351 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getStockValue()));
352 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getNodStock()));
353 
            row.createCell(++i).setCellValue(excelrow.getMovementCategory());
354 
            iterator++;
355 
        }
356 
 
357 
        iterator = 1;
358 
        double slow_moving_value = 0;
359 
        long slow_moving_count = 0;
360 
 
361 
        for (FkExcelRow excelrow : Slow_Moving_Items) {
362 
        	slow_moving_value = slow_moving_value + excelrow.getStockValue();
363 
        	slow_moving_count = slow_moving_count + excelrow.getStockQuantity();
364 
            row = sheet2.createRow((short) iterator);
365 
            HSSFCellStyle style = hwb.createCellStyle();
366 
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
367 
            HSSFFont font = hwb.createFont();
368 
            style.setFillForegroundColor(HSSFColor.WHITE.index);
369 
            font.setColor(HSSFColor.BLACK.index);
370 
            style.setFont(font);
371 
            i=0;
372 
            row.createCell(i).setCellValue(excelrow.getItem_id());
373 
            row.createCell(++i).setCellValue(excelrow.getBrand());
374 
            row.createCell(++i).setCellValue(excelrow.getCategory());
375 
            row.createCell(++i).setCellValue(excelrow.getProductGroup());
376 
            row.createCell(++i).setCellValue(excelrow.getProductName());
377 
            row.createCell(++i).setCellValue(excelrow.getSalesHistory());
378 
            row.createCell(++i).setCellValue(String.format("%.2f",excelrow.getAvgSale()));
379 
            row.createCell(++i).setCellValue(excelrow.getStockQuantity());
380 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getSellingPrice()));
381 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getStockValue()));
382 
            row.createCell(++i).setCellValue(String.format("%.0f",excelrow.getNodStock()));
383 
            row.createCell(++i).setCellValue(excelrow.getMovementCategory());
384 
            iterator++;
385 
        }
386 
 
387 
        FileOutputStream fileOut = null;
388 
        try {
389 
            fileOut = new FileOutputStream(FLIPKART_FBA_SHEET);
390 
        } catch (FileNotFoundException e) {
391 
            e.printStackTrace();
392 
        }
393 
        try {
394 
            hwb.write(fileOut);
395 
        } catch (IOException e) {
396 
            e.printStackTrace();
397 
        }
398 
        try {
399 
            fileOut.close();
400 
        } catch (IOException e) {
401 
            e.printStackTrace();
402 
        }
403 
 
404 
        StringBuffer sb = new StringBuffer();
405 
        sb.append("<html><table border=\"1\" align=\"center\">"
406 
                + "<caption><b>" + "ITEMS TO REPLENISH" + "</b></caption>"
407 
                + "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>" + "<td><b>"
408 
                + "BRAND" + "</b></td>" + "<td><b>" + "CATEGORY" + "</b></td>"
409 
                + "<td><b>" + "PRODUCT-GROUP" + "</b></td>" + "<td><b>"
410 
                + "PRODUCT-NAME" + "</b></td>" + "<td><b>" + "SALES-HISTORY"
411 
                + "</b></td>" + "<td><b>" + "AVG-SALE" + "</b></td>"
412 
                + "</b></td>" + "<td><b>" + "CURRENT-STOCK" + "</b></td>"
413 
                + "<td><b>" + "NO-OF-DAYS-STOCK" + "</b></td>"
414 
                + "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "</tr>");
415 
 
416 
        int count = 0;
417 
        for(FkExcelRow excelRow: Replenishment_Items){
418 
        	if (count < 10) {
419 
                sb.append("<tr>" + "<td>"+ excelRow.getItem_id() +"</td>"
420 
                		+"<td>"+ excelRow.getBrand() +"</td>"
421 
                		+"<td>"+ excelRow.getCategory() +"</td>"
422 
                		+"<td>"+ excelRow.getProductGroup() +"</td>"
423 
                		+"<td>"+ excelRow.getProductName() +"</td>"
424 
                		+"<td>"+ excelRow.getSalesHistory() +"</td>"
425 
                		+"<td>"+ Math.round(excelRow.getAvgSale() * 100.0) / 100.0 +"</td>"
426 
                		+"<td>"+ excelRow.getStockQuantity() +"</td>"
427 
                		+"<td>"+ String.format("%.0f",excelRow.getNodStock()) +"</td>"
428 
                		+"<td>"+ String.format("%.0f",excelRow.getStockValue()) +"</td></tr>");
429 
                count++;
430 
        	}else{
431 
        		break;
432 
        	}
433 
        }
434 
        sb.append("</table>");
435 
        sb.append("<table border=\"1\" align=\"center\">"
436 
                + "<caption><b>" + "SLOW-MOVING-ITEMS" + "</b></caption>"
437 
                + "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>" + "<td><b>"
438 
                + "BRAND" + "</b></td>" + "<td><b>" + "CATEGORY" + "</b></td>"
439 
                + "<td><b>" + "PRODUCT-GROUP" + "</b></td>" + "<td><b>"
440 
                + "PRODUCT-NAME" + "</b></td>" + "<td><b>" + "SALES-HISTORY"
441 
                + "</b></td>" + "<td><b>" + "AVG-SALE" + "</b></td>"
442 
                + "</b></td>" + "<td><b>" + "CURRENT-STOCK" + "</b></td>"
443 
                + "<td><b>" + "NO-OF-DAYS-STOCK" + "</b></td>"
444 
                + "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "</tr>");
445 
 
446 
        count = 0;
447 
        for(FkExcelRow excelRow : Slow_Moving_Items){
448 
        	if (count < 10) {
449 
                sb.append("<tr>" + "<td>"+ excelRow.getItem_id() +"</td>"
450 
                		+"<td>"+ excelRow.getBrand() +"</td>"
451 
                		+"<td>"+ excelRow.getCategory() +"</td>"
452 
                		+"<td>"+ excelRow.getProductGroup() +"</td>"
453 
                		+"<td>"+ excelRow.getProductName() +"</td>"
454 
                		+"<td>"+ excelRow.getSalesHistory() +"</td>"
455 
                		+"<td>"+ Math.round(excelRow.getAvgSale() * 100.0) / 100.0 +"</td>"
456 
                		+"<td>"+ excelRow.getStockQuantity() +"</td>"
457 
                		+"<td>"+ String.format("%.0f",excelRow.getNodStock()) +"</td>"
458 
                		+"<td>"+ String.format("%.0f",excelRow.getStockValue()) +"</td></tr>");
459 
                count++;
460 
        	}else{
461 
        		break;
462 
        	}
463 
        }
464 
        sb.append("</table>");
465 
        sb.append("<table border=\"1\" align=\"center\">"
466 
                + "<caption><b>" + "NON-MOVING-ITEMS" + "</b></caption>"
467 
                + "<tr>" + "<td><b>" + "ITEM-ID" + "</b></td>" + "<td><b>"
468 
                + "BRAND" + "</b></td>" + "<td><b>" + "CATEGORY" + "</b></td>"
469 
                + "<td><b>" + "PRODUCT-GROUP" + "</b></td>" + "<td><b>"
470 
                + "PRODUCT-NAME" + "</b></td>" + "<td><b>" + "SALES-HISTORY"
471 
                + "</b></td>" + "<td><b>" + "AVG-SALE" + "</b></td>"
472 
                + "</b></td>" + "<td><b>" + "CURRENT-STOCK" + "</b></td>"
473 
                + "<td><b>" + "NO-OF-DAYS-STOCK" + "</b></td>"
474 
                + "<td><b>" + "STOCK-VALUE" + "</b></td>"+ "</tr>");
475 
 
476 
        count = 0;
477 
        for(FkExcelRow excelRow : Not_Moving_Item_List){
478 
        	if (count < 10) {
479 
                sb.append("<tr>" + "<td>"+ excelRow.getItem_id() +"</td>"
480 
                		+"<td>"+ excelRow.getBrand() +"</td>"
481 
                		+"<td>"+ excelRow.getCategory() +"</td>"
482 
                		+"<td>"+ excelRow.getProductGroup() +"</td>"
483 
                		+"<td>"+ excelRow.getProductName() +"</td>"
484 
                		+"<td>"+ excelRow.getSalesHistory() +"</td>"
485 
                		+"<td>"+ Math.round(excelRow.getAvgSale() * 100.0) / 100.0 +"</td>"
486 
                		+"<td>"+ excelRow.getStockQuantity() +"</td>"
487 
                		+"<td>"+ String.format("%.0f",excelRow.getNodStock()) +"</td>"
488 
                		+"<td>"+ String.format("%.0f",excelRow.getStockValue()) +"</td></tr>");
489 
                count++;
490 
        	}else{
491 
        		break;
492 
        	}
493 
        }
494 
        sb.append("</table></html>");
495 
        StringBuffer stockSummary = new StringBuffer();
496 
        stockSummary.append("<html><table border=\"1\" align=\"center\">" +
497 
                "<caption><b>"+ "STOCK SUMMARY" + "</b></caption>" +
498 
                "<tr>" + "<td><b>"+ "STOCK-TYPE" + "</b></td>" +"<td><b>"+
499 
                "NO-OF-SKUS" + "</b></td>" +"<td><b>"
500 
                + "QTY" + "</b></td>" +"<td><b>" + "VALUE"+"</tr>");
501 
        stockSummary.append("<tr>" + "<td><b>"+ "MOVING" + "</b></td>" +
502 
                "<td><b>" +Moving_List.size()+ "</b></td>" +"<td><b>"
503 
                + "</b>"+movingStock+"</td>" +"<td><b>" + String.format("%.0f",movingStockValue)+"</tr>");
504 
        stockSummary.append("<tr>" + "<td><b>"+ "SLOW-MOVING" + "</b></td>"+
505 
                "<td><b>" +    Slow_Moving_Items.size() + "</b></td>" +"<td><b>"
506 
                + "</b>"+slow_moving_count+"</td>" +"<td><b>" +String.format("%.0f",slow_moving_value)+"</tr>");
507 
        stockSummary.append("<tr>" + "<td><b>"+ "NOT-MOVING" + "</b></td>" +
508 
                "<td><b>" +    Not_Moving_Item_List.size() + "</b></td>" +"<td><b>"
509 
                + "</b>"+not_moving_count+"</td>" +"<td><b>" +String.format("%.0f",not_moving_value)+"</tr>");
510 
        stockSummary.append("<tr>" + "<td><b>"+ "TOTAL" + "</b></td>"+
511 
                "<td><b>" +  (Moving_List.size() +Slow_Moving_Items.size()+ Not_Moving_Item_List.size()) + "</b></td>" +"<td><b>"
512 
                + "</b>"+(movingStock+not_moving_count+slow_moving_count)+"</td>" +"<td><b>" +String.format("%.0f",(movingStockValue+not_moving_value+slow_moving_value))+"</tr>");
513 
        stockSummary.append("</table></html>");
514 
 
515 
        BufferedWriter out = new BufferedWriter(new FileWriter(
516 
        		FLIPKART_FBA_SHEET_BODY));
517 
        out.write(stockSummary.toString()+sb.toString());
518 
        out.flush();
519 
        out.close();
520 
        String emailFromAddress = "build-staging@shop2020.in";
521 
        String password = "shop2020";
522 
 
523 
        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"};
524 
        //String[] sendTo = new String[] { "manish.sharma@shop2020.in" };
525 
        String emailSubjectTxt = "FA Stock Estimation Sheet Except (Mobiles and Tablets)";
526 
        try {
527 
            GmailUtils mailer = new GmailUtils();
528 
            mailer.sendSSLMessage(sendTo, emailSubjectTxt, "", emailFromAddress, password, FLIPKART_FBA_SHEET, FLIPKART_FBA_SHEET_BODY);
529 
        } catch (Exception ex) {
530 
            ex.printStackTrace();
531 
            EmailUtils emailUtils = new EmailUtils();
532 
            emailUtils.sendEmail(sendTo, emailSubjectTxt, "", emailFromAddress, FLIPKART_FBA_SHEET, FLIPKART_FBA_SHEET_BODY);
533 
        }
534 
	}
535 
}