Subversion Repositories SmartDukaan

Rev

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