Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
21786 ashik.ali 1 
package com.spice.profitmandi.common.util;
2 
 
3 
import java.io.IOException;
4 
import java.io.InputStream;
22470 ashik.ali 5 
import java.io.OutputStream;
21786 ashik.ali 6 
import java.time.LocalDateTime;
7 
import java.time.ZoneId;
8 
import java.util.ArrayList;
9 
import java.util.Date;
23819 govind 10 
import java.util.LinkedHashMap;
21786 ashik.ali 11 
import java.util.List;
23819 govind 12 
import java.util.Map;
13 
import java.util.Set;
21786 ashik.ali 14 
 
24052 amit.gupta 15 
import org.apache.logging.log4j.LogManager;
16 
import org.apache.logging.log4j.Logger;
21786 ashik.ali 17 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
22470 ashik.ali 18 
import org.apache.poi.ss.usermodel.Cell;
19 
import org.apache.poi.ss.usermodel.CellStyle;
21786 ashik.ali 20 
import org.apache.poi.ss.usermodel.CellType;
22470 ashik.ali 21 
import org.apache.poi.ss.usermodel.Font;
22521 ashik.ali 22 
import org.apache.poi.ss.usermodel.HorizontalAlignment;
22470 ashik.ali 23 
import org.apache.poi.ss.usermodel.Row;
21786 ashik.ali 24 
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
22470 ashik.ali 25 
import org.apache.poi.ss.util.CellRangeAddress;
26 
import org.apache.poi.xssf.streaming.SXSSFSheet;
27 
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
22247 amit.gupta 28 
import org.apache.poi.xssf.usermodel.XSSFRow;
21786 ashik.ali 29 
import org.apache.poi.xssf.usermodel.XSSFSheet;
30 
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
31 
 
32 
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
24119 govind 33 
import com.spice.profitmandi.common.model.CustomRetailer;
22470 ashik.ali 34 
import com.spice.profitmandi.common.model.InventoryItemAgingModel;
35 
import com.spice.profitmandi.common.model.InventoryItemAgingValue;
22521 ashik.ali 36 
import com.spice.profitmandi.common.model.ItemCompleteLedgerModel;
24107 govind 37 
import com.spice.profitmandi.common.model.PartnerTargetModel;
21786 ashik.ali 38 
import com.spice.profitmandi.common.model.ProfitMandiConstants;
23017 ashik.ali 39 
import com.spice.profitmandi.common.model.SchemeModel;
21786 ashik.ali 40 
import com.spice.profitmandi.common.model.TagListingModel;
41 
 
24119 govind 42 
import in.shop2020.model.v1.catalog.SalesPotential;
43 
 
21786 ashik.ali 44 
public class ExcelUtils {
45 
	private static final String TAG_ID = "Tag Id";
46 
	private static final String TAG_LABEL = "Tag Label";
47 
	private static final String ITEM_ID = "Item Id";
48 
	private static final String BRAND = "Brand";
49 
	private static final String MODEL_NAME = "Model Name";
50 
	private static final String MODEL_NUMBER = "Model Number";
51 
	private static final String COLOR = "Color";
52 
	private static final String SELLING_PRICE = "Selling Price";
22204 amit.gupta 53 
	private static final String MOP = "MOP";
21786 ashik.ali 54 
	private static final String SUPPORT_PRICE = "Support Price";
55 
	private static final String START_DATE = "Start Date";
56 
	private static final String TAG_LISTING = "Tag Listing";
24107 govind 57 
	private static final String FOFO_ID = "fofoId";
58 
	private static final String STORE_NAME = "storeName";
59 
	private static final String EMAIL = "email";
24119 govind 60 
	private static final String TARGET_VALUE = "targetValue";
61 
 
23568 govind 62 
	private static final Logger LOGGER = LogManager.getLogger(ExcelUtils.class);
24119 govind 63 
 
64 
	public static void main(String[] args) throws Throwable {
65 
		// List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);
66 
		// writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);
21786 ashik.ali 67 
	}
24119 govind 68 
 
22924 ashik.ali 69 
	public static List<TagListingModel> parse(InputStream inputStream) throws Exception {
24119 govind 70 
 
21786 ashik.ali 71 
		List<TagListingModel> tagListings = new ArrayList<>();
72 
		XSSFWorkbook myWorkBook = null;
24119 govind 73 
		try {
74 
			// FileInputStream fileInputStream = new
75 
			// FileInputStream("/home/ashikali/tag_listing1.xlsx");
76 
			myWorkBook = new XSSFWorkbook(inputStream);
77 
 
21786 ashik.ali 78 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
24119 govind 79 
			// Return first sheet from the XLSX workbook
21786 ashik.ali 80 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
22247 amit.gupta 81 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
24119 govind 82 
 
83 
			for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
22247 amit.gupta 84 
				XSSFRow row = mySheet.getRow(rowNumber);
85 
				LOGGER.info("row {}", row);
86 
				TagListingModel tagListing = new TagListingModel();
24119 govind 87 
				if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 88 
					tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
24119 govind 89 
				} else {
90 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID,
91 
							row.getCell(0).toString(), "TGLSTNG_VE_1010");
22226 amit.gupta 92 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 93 
					throw profitMandiBusinessException;
94 
				}
24119 govind 95 
 
96 
				if (row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 97 
					tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
24119 govind 98 
				} else {
99 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
100 
							ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
22247 amit.gupta 101 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
102 
					throw profitMandiBusinessException;
103 
				}
22204 amit.gupta 104 
 
24119 govind 105 
				if (row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 106 
					tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
24119 govind 107 
				} else {
108 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
109 
							SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
22226 amit.gupta 110 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
22209 amit.gupta 111 
					throw profitMandiBusinessException;
21786 ashik.ali 112 
				}
24119 govind 113 
				if (row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 114 
					tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
24119 govind 115 
				} else {
116 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP,
117 
							row.getCell(8), "TGLSTNG_VE_1010");
22247 amit.gupta 118 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
119 
					throw profitMandiBusinessException;
120 
				}
24119 govind 121 
				if (row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 122 
					tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
24119 govind 123 
				} else {
124 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
125 
							SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
22247 amit.gupta 126 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
127 
					throw profitMandiBusinessException;
128 
				}
24119 govind 129 
				if (row.getCell(10) != null && row.getCell(10).getCellTypeEnum() == CellType.NUMERIC) {
22563 amit.gupta 130 
					tagListing.setMaxDiscountPrice(Double.valueOf(row.getCell(10).toString()).floatValue());
24119 govind 131 
				} else {
132 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
133 
							SUPPORT_PRICE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
22563 amit.gupta 134 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
135 
					throw profitMandiBusinessException;
136 
				}
24119 govind 137 
				if (row.getCell(11) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(11))) {
22865 amit.gupta 138 
					Date date = row.getCell(11).getDateCellValue();
22247 amit.gupta 139 
					LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
140 
					tagListing.setStartDate(startDate);
24119 govind 141 
				} else {
142 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
143 
							START_DATE, row.getCell(11).toString(), "TGLSTNG_VE_1010");
22247 amit.gupta 144 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
24119 govind 145 
					throw profitMandiBusinessException;
22247 amit.gupta 146 
				}
147 
				tagListings.add(tagListing);
21786 ashik.ali 148 
			}
149 
			myWorkBook.close();
24119 govind 150 
		} catch (IOException ioException) {
22206 amit.gupta 151 
			ioException.printStackTrace();
24119 govind 152 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
153 
					"EXL_VE_1000");
22247 amit.gupta 154 
		} finally {
24119 govind 155 
			if (myWorkBook != null) {
21786 ashik.ali 156 
				try {
157 
					myWorkBook.close();
158 
				} catch (IOException e) {
159 
					// TODO Auto-generated catch block
160 
					e.printStackTrace();
161 
				}
162 
			}
163 
		}
164 
		return tagListings;
165 
	}
24119 govind 166 
 
167 
	public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels,
168 
			List<Integer> intervals, OutputStream outputStream) {
22470 ashik.ali 169 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 170 
 
171 
		// CreationHelper createHelper = workbook.getCreationHelper();
172 
 
22470 ashik.ali 173 
		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
174 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
175 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
176 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
177 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
178 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
179 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
180 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 181 
 
182 
		Row rowHeader = sheet.createRow(0);
183 
		Cell cellItemIdHeader = rowHeader.createCell(0);
22470 ashik.ali 184 
		cellItemIdHeader.setCellValue("Item Id");
185 
		Cell cellBrandHeader = rowHeader.createCell(1);
186 
		cellBrandHeader.setCellValue("Brand");
187 
		Cell cellModelNameHeader = rowHeader.createCell(2);
188 
		cellModelNameHeader.setCellValue("Model Name");
189 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
190 
		cellModelNumberHeader.setCellValue("Model Number");
191 
		Cell cellColorHeader = rowHeader.createCell(4);
192 
		cellColorHeader.setCellValue("Color");
193 
		Cell cellTypeHeader = rowHeader.createCell(5);
194 
		cellTypeHeader.setCellValue("Item Type");
195 
		Row rowPriceQuantity = sheet.createRow(1);
24119 govind 196 
		for (int index = 0, colIndex = 6; index < intervals.size() + 1; index++, colIndex = colIndex + 2) {
22470 ashik.ali 197 
			Cell cellHeader = rowHeader.createCell(colIndex);
24119 govind 198 
			if (index == 0) {
199 
				cellHeader.setCellValue("Less Than " + intervals.get(index) + " Days");
200 
			} else if (index < intervals.size()) {
22470 ashik.ali 201 
				cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");
24119 govind 202 
			} else {
203 
				cellHeader.setCellValue("More Than " + intervals.get(index - 1) + " Days");
22470 ashik.ali 204 
			}
205 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, colIndex, colIndex + 1));
206 
			rowHeader.createCell(colIndex + 1);
207 
			Cell cellPrice = rowPriceQuantity.createCell(colIndex);
208 
			cellPrice.setCellValue("Price");
209 
			Cell cellQuantity = rowPriceQuantity.createCell(colIndex + 1);
210 
			cellQuantity.setCellValue("Quantity");
211 
		}
212 
		Font font = workbook.createFont();
213 
		CellStyle cellStyle = workbook.createCellStyle();
214 
		font.setBold(true);
24119 govind 215 
		// font.setFontHeight((short)16);
22470 ashik.ali 216 
		cellStyle.setFont(font);
24119 govind 217 
		for (int i = 0; i < 8 + (intervals.size() * 2); i++) {
22470 ashik.ali 218 
			rowHeader.getCell(i).setCellStyle(cellStyle);
24119 govind 219 
			if (rowPriceQuantity.getCell(i) != null) {
22470 ashik.ali 220 
				rowPriceQuantity.getCell(i).setCellStyle(cellStyle);
221 
			}
222 
		}
24119 govind 223 
 
224 
		for (int index = 0; index < inventoryItemAgingModels.size(); index++) {
22470 ashik.ali 225 
			InventoryItemAgingModel inventoryItemAgingModel = inventoryItemAgingModels.get(index);
24119 govind 226 
			Row rowValues = sheet.createRow(index + 2);
227 
			Cell cellItemId = rowValues.createCell(0);
228 
			cellItemId.setCellValue(inventoryItemAgingModel.getItemId());
229 
			Cell cellBrand = rowValues.createCell(1);
230 
			cellBrand.setCellValue(inventoryItemAgingModel.getBrand());
231 
			Cell cellModelName = rowValues.createCell(2);
232 
			cellModelName.setCellValue(inventoryItemAgingModel.getModelName());
233 
			Cell cellModelNumber = rowValues.createCell(3);
234 
			cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());
235 
			Cell cellColor = rowValues.createCell(4);
236 
			cellColor.setCellValue(inventoryItemAgingModel.getColor());
237 
			Cell cellType = rowValues.createCell(5);
238 
			cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());
239 
			List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
240 
			// LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
241 
			for (int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2) {
242 
				Cell cellPrice = rowValues.createCell(colIndex);
243 
				InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i);
244 
				// LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);
245 
				Cell cellQuantity = rowValues.createCell(colIndex + 1);
246 
				if (inventoryItemAgingValue != null) {
247 
					cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
248 
					cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
249 
				} else {
250 
					cellPrice.setCellValue("-");
251 
					cellQuantity.setCellValue("-");
252 
				}
253 
			}
254 
		}
255 
 
256 
		for (int index = 0; index < 8 + (intervals.size() * 2); index++) {
22470 ashik.ali 257 
			sheet.autoSizeColumn(index);
258 
		}
24119 govind 259 
 
260 
		try {
22486 ashik.ali 261 
			workbook.write(outputStream);
24119 govind 262 
			workbook.close();
263 
		} catch (IOException ioException) {
264 
			LOGGER.error("Unable to generate excel file", ioException);
265 
		}
22470 ashik.ali 266 
	}
24119 govind 267 
 
268 
	public static void writeItemCompleteLedgerModels(List<ItemCompleteLedgerModel> itemCompleteLedgerModels,
269 
			OutputStream outputStream) {
22521 ashik.ali 270 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 271 
 
272 
		// CreationHelper createHelper = workbook.getCreationHelper();
273 
 
22521 ashik.ali 274 
		SXSSFSheet sheet = workbook.createSheet("ItemCompleteLeger");
275 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
276 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
277 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
278 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
279 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
280 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
281 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 282 
 
283 
		Row rowHeader = sheet.createRow(0);
284 
		Cell cellItemIdHeader = rowHeader.createCell(0);
22521 ashik.ali 285 
		cellItemIdHeader.setCellValue("Item Id");
286 
		Cell cellBrandHeader = rowHeader.createCell(1);
287 
		cellBrandHeader.setCellValue("Brand");
288 
		Cell cellModelNameHeader = rowHeader.createCell(2);
289 
		cellModelNameHeader.setCellValue("Model Name");
290 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
291 
		cellModelNumberHeader.setCellValue("Model Number");
292 
		Cell cellColorHeader = rowHeader.createCell(4);
293 
		cellColorHeader.setCellValue("Color");
294 
		Cell cellTypeHeader = rowHeader.createCell(5);
295 
		cellTypeHeader.setCellValue("Item Type");
296 
		Cell cellOpeningBalanceHeader = rowHeader.createCell(6);
297 
		cellOpeningBalanceHeader.setCellValue("Opening Balance");
298 
		Cell cellInwardsHeader = rowHeader.createCell(9);
299 
		cellInwardsHeader.setCellValue("Inwards");
300 
		Cell cellOutwardsHeader = rowHeader.createCell(12);
301 
		cellOutwardsHeader.setCellValue("Outwards");
302 
		Cell cellClosingBalanceHeader = rowHeader.createCell(15);
303 
		cellClosingBalanceHeader.setCellValue("Closing Balance");
304 
		Row rowQuantityRateValue = sheet.createRow(1);
24119 govind 305 
 
306 
		for (int index = 6; index < 18; index = index + 3) {
22521 ashik.ali 307 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
308 
			cellQuantityHeader.setCellValue("Quantity");
309 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
310 
			cellRateHeader.setCellValue("Rate");
311 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
312 
			cellValueHeader.setCellValue("Value");
313 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
314 
		}
315 
		Font font = workbook.createFont();
316 
		CellStyle cellStyle = workbook.createCellStyle();
317 
		font.setBold(true);
318 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 319 
		// font.setFontHeight((short)16);
22521 ashik.ali 320 
		cellStyle.setFont(font);
24119 govind 321 
		for (int i = 0; i < 18; i++) {
322 
			if (rowHeader.getCell(i) != null) {
22521 ashik.ali 323 
				rowHeader.getCell(i).setCellStyle(cellStyle);
324 
			}
325 
		}
326 
		int openingQuantityTotal = 0;
327 
		float openingValueTotal = 0;
328 
		int inwardsQuantityTotal = 0;
329 
		float inwardsValueTotal = 0;
330 
		int outwardsQuantityTotal = 0;
331 
		float outwardsValueTotal = 0;
332 
		int closingQuantityTotal = 0;
333 
		float closingValueTotal = 0;
24119 govind 334 
		for (int index = 0; index < itemCompleteLedgerModels.size(); index++) {
22521 ashik.ali 335 
			ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
24119 govind 336 
			Row rowValues = sheet.createRow(index + 2);
337 
			Cell cellItemId = rowValues.createCell(0);
338 
			cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
339 
			Cell cellBrand = rowValues.createCell(1);
340 
			cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
341 
			Cell cellModelName = rowValues.createCell(2);
342 
			cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
343 
			Cell cellModelNumber = rowValues.createCell(3);
344 
			cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
345 
			Cell cellColor = rowValues.createCell(4);
346 
			cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
347 
			Cell cellType = rowValues.createCell(5);
348 
			cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
349 
			Cell cellOpeningQuantity = rowValues.createCell(6);
350 
			Cell cellOpeningRate = rowValues.createCell(7);
351 
			Cell cellOpeningValue = rowValues.createCell(8);
352 
			if (itemCompleteLedgerModel.getOpeningLedger() == null) {
353 
				cellOpeningQuantity.setCellValue("-");
354 
				cellOpeningRate.setCellValue("-");
355 
				cellOpeningValue.setCellValue("-");
356 
			} else {
357 
				cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
358 
				cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
359 
				cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
360 
				openingQuantityTotal = openingQuantityTotal + itemCompleteLedgerModel.getOpeningLedger().getQuantity();
361 
				openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
362 
			}
363 
			Cell cellInwardsQuantity = rowValues.createCell(9);
364 
			Cell cellInwardsRate = rowValues.createCell(10);
365 
			Cell cellInwardsValue = rowValues.createCell(11);
366 
			if (itemCompleteLedgerModel.getInwardsLedger() == null) {
367 
				cellInwardsQuantity.setCellValue("-");
368 
				cellInwardsRate.setCellValue("-");
369 
				cellInwardsValue.setCellValue("-");
370 
			} else {
371 
				cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
372 
				cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
373 
				cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
374 
				inwardsQuantityTotal = inwardsQuantityTotal + itemCompleteLedgerModel.getInwardsLedger().getQuantity();
375 
				inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
376 
			}
377 
			Cell cellOutwardsQuantity = rowValues.createCell(12);
378 
			Cell cellOutwardsRate = rowValues.createCell(13);
379 
			Cell cellOutwardsValue = rowValues.createCell(14);
380 
			if (itemCompleteLedgerModel.getOutwardsLedger() == null) {
381 
				cellOutwardsQuantity.setCellValue("-");
382 
				cellOutwardsRate.setCellValue("-");
383 
				cellOutwardsValue.setCellValue("-");
384 
			} else {
385 
				cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
386 
				cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
387 
				cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
388 
				outwardsQuantityTotal = outwardsQuantityTotal
389 
						+ itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
390 
				outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
391 
			}
392 
			Cell cellClosingQuantity = rowValues.createCell(15);
393 
			Cell cellClosingRate = rowValues.createCell(16);
394 
			Cell cellClosingValue = rowValues.createCell(17);
395 
			if (itemCompleteLedgerModel.getClosingLedger() == null) {
396 
				cellClosingQuantity.setCellValue("-");
397 
				cellClosingRate.setCellValue("-");
398 
				cellClosingValue.setCellValue("-");
399 
			} else {
400 
				cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
401 
				cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
402 
				cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
403 
				closingQuantityTotal = closingQuantityTotal + itemCompleteLedgerModel.getClosingLedger().getQuantity();
404 
				closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
405 
			}
406 
		}
407 
 
22521 ashik.ali 408 
		Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);
24119 govind 409 
 
410 
		if (openingQuantityTotal > 0) {
22521 ashik.ali 411 
			Cell cellOpeningQuantityTotal = rowTotal.createCell(6);
412 
			cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);
413 
			Cell cellOpeningValueTotal = rowTotal.createCell(8);
414 
			cellOpeningValueTotal.setCellValue(openingValueTotal);
415 
		}
24119 govind 416 
 
417 
		if (inwardsQuantityTotal > 0) {
22521 ashik.ali 418 
			Cell cellInwardsQuantityTotal = rowTotal.createCell(9);
419 
			cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);
420 
			Cell cellInwardsValueTotal = rowTotal.createCell(11);
421 
			cellInwardsValueTotal.setCellValue(inwardsValueTotal);
422 
		}
24119 govind 423 
 
424 
		if (outwardsQuantityTotal > 0) {
22521 ashik.ali 425 
			Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);
426 
			cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);
427 
			Cell cellOutwardsValueTotal = rowTotal.createCell(14);
428 
			cellOutwardsValueTotal.setCellValue(outwardsValueTotal);
429 
		}
24119 govind 430 
 
431 
		if (closingQuantityTotal > 0) {
22521 ashik.ali 432 
			Cell cellClosingQuantityTotal = rowTotal.createCell(15);
433 
			cellClosingQuantityTotal.setCellValue(closingQuantityTotal);
434 
			Cell cellClosingValueTotal = rowTotal.createCell(17);
435 
			cellClosingValueTotal.setCellValue(closingValueTotal);
436 
		}
24119 govind 437 
 
438 
		for (int index = 0; index < 18; index++) {
22521 ashik.ali 439 
			sheet.autoSizeColumn(index);
440 
		}
24119 govind 441 
 
442 
		try {
22521 ashik.ali 443 
			workbook.write(outputStream);
24119 govind 444 
			workbook.close();
445 
		} catch (IOException ioException) {
446 
			LOGGER.error("Unable to generate excel file", ioException);
447 
		}
22521 ashik.ali 448 
	}
24119 govind 449 
 
450 
	public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream) {
23017 ashik.ali 451 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 452 
 
453 
		// CreationHelper createHelper = workbook.getCreationHelper();
454 
 
23017 ashik.ali 455 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
456 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 457 
 
458 
		Row rowHeader = sheet.createRow(0);
459 
		Cell cellSchemeIdHeader = rowHeader.createCell(0);
23017 ashik.ali 460 
		cellSchemeIdHeader.setCellValue("Scheme Id");
461 
		Cell cellNameHeader = rowHeader.createCell(1);
462 
		cellNameHeader.setCellValue("Name");
463 
		Cell cellDescriptionHeader = rowHeader.createCell(2);
464 
		cellDescriptionHeader.setCellValue("Description");
465 
		Cell cellSchemeTypeHeader = rowHeader.createCell(3);
466 
		cellSchemeTypeHeader.setCellValue("Scheme Type");
467 
		Cell cellAmountTypeHeader = rowHeader.createCell(4);
468 
		cellAmountTypeHeader.setCellValue("Amount Type");
469 
		Cell cellAmountHeader = rowHeader.createCell(5);
470 
		cellAmountHeader.setCellValue("Amount");
471 
		Cell cellStartDateTimeHeader = rowHeader.createCell(6);
472 
		cellStartDateTimeHeader.setCellValue("Start Date Time");
473 
		Cell cellEndDateTimeHeader = rowHeader.createCell(7);
474 
		cellEndDateTimeHeader.setCellValue("End Date Time");
475 
		Cell cellCreatedAtHeader = rowHeader.createCell(8);
476 
		cellCreatedAtHeader.setCellValue("Created At");
477 
		Cell cellActiveHeader = rowHeader.createCell(9);
478 
		cellActiveHeader.setCellValue("Active");
479 
		Cell cellExpireHeader = rowHeader.createCell(10);
480 
		cellExpireHeader.setCellValue("Expire");
481 
		Cell cellCreatedByHeader = rowHeader.createCell(11);
482 
		cellCreatedByHeader.setCellValue("Created By");
483 
		Cell cellItemIdsHeader = rowHeader.createCell(12);
484 
		cellItemIdsHeader.setCellValue("Item Ids");
485 
		Cell cellRetailerIdsHeader = rowHeader.createCell(13);
486 
		cellRetailerIdsHeader.setCellValue("Retailer Ids");
24119 govind 487 
		// Row rowQuantityRateValue = sheet.createRow(1);
488 
 
489 
		/*
490 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
491 
		 * rowQuantityRateValue.createCell(index);
492 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
493 
		 * rowQuantityRateValue.createCell(index + 1);
494 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
495 
		 * rowQuantityRateValue.createCell(index + 2);
496 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
497 
		 * CellRangeAddress(0, 0, index, index + 2)); }
498 
		 */
23017 ashik.ali 499 
		Font font = workbook.createFont();
500 
		CellStyle cellStyle = workbook.createCellStyle();
501 
		font.setBold(true);
502 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 503 
		// font.setFontHeight((short)16);
23017 ashik.ali 504 
		cellStyle.setFont(font);
24119 govind 505 
		for (int i = 0; i < 14; i++) {
506 
			if (rowHeader.getCell(i) != null) {
23017 ashik.ali 507 
				rowHeader.getCell(i).setCellStyle(cellStyle);
508 
			}
509 
		}
510 
 
24119 govind 511 
		for (int index = 0; index < schemeModels.size(); index++) {
23017 ashik.ali 512 
			SchemeModel schemeModel = schemeModels.get(index);
24119 govind 513 
			// ItemCompleteLedgerModel itemCompleteLedgerModel =
514 
			// itemCompleteLedgerModels.get(index);
515 
			Row rowValues = sheet.createRow(index + 1);
516 
			Cell cellSchemeId = rowValues.createCell(0);
23017 ashik.ali 517 
			cellSchemeId.setCellValue(schemeModel.getSchemeId());
518 
			Cell cellName = rowValues.createCell(1);
519 
			cellName.setCellValue(schemeModel.getName());
520 
			Cell cellDescription = rowValues.createCell(2);
521 
			cellDescription.setCellValue(schemeModel.getDescription());
522 
			Cell cellSchemeType = rowValues.createCell(3);
523 
			cellSchemeType.setCellValue(schemeModel.getSchemeType());
524 
			Cell cellAmountType = rowValues.createCell(4);
525 
			cellAmountType.setCellValue(schemeModel.getAmountType());
526 
			Cell cellAmount = rowValues.createCell(5);
527 
			cellAmount.setCellValue(schemeModel.getAmount());
528 
			Cell cellStartDateTime = rowValues.createCell(6);
529 
			cellStartDateTime.setCellValue(schemeModel.getStartDateTime());
530 
			Cell cellEndDateTime = rowValues.createCell(7);
531 
			cellEndDateTime.setCellValue(schemeModel.getEndDateTime());
532 
			Cell cellCreatedAt = rowValues.createCell(8);
533 
			cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());
534 
			Cell cellActive = rowValues.createCell(9);
24119 govind 535 
			if (schemeModel.getActiveTimestamp() != null) {
23017 ashik.ali 536 
				cellActive.setCellValue(schemeModel.getActiveTimestamp());
24119 govind 537 
			} else {
23017 ashik.ali 538 
				cellActive.setCellValue("False");
539 
			}
540 
			Cell cellExpire = rowValues.createCell(10);
24119 govind 541 
			if (schemeModel.getExpireTimestamp() != null) {
23017 ashik.ali 542 
				cellExpire.setCellValue(schemeModel.getExpireTimestamp());
24119 govind 543 
			} else {
23017 ashik.ali 544 
				cellExpire.setCellValue("False");
545 
			}
546 
			Cell cellCreatedBy = rowValues.createCell(11);
547 
			cellCreatedBy.setCellValue(schemeModel.getCreatedBy());
548 
			Cell cellItemIds = rowValues.createCell(12);
23338 ashik.ali 549 
			cellItemIds.setCellValue(schemeModel.getItemStringMap().toString());
23017 ashik.ali 550 
			Cell cellRetailerIds = rowValues.createCell(13);
551 
			cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());
23338 ashik.ali 552 
			int maxHeight = Math.max(schemeModel.getItemStringMap().size(), schemeModel.getRetailerIds().size());
24119 govind 553 
			if (maxHeight > 1) {
554 
				rowValues.setHeight((short) (maxHeight * 240));
23017 ashik.ali 555 
			}
24119 govind 556 
		}
557 
 
558 
		for (int index = 0; index < 14; index++) {
23017 ashik.ali 559 
			sheet.autoSizeColumn(index);
560 
		}
24119 govind 561 
 
562 
		try {
23017 ashik.ali 563 
			workbook.write(outputStream);
24119 govind 564 
			workbook.close();
565 
		} catch (IOException ioException) {
566 
			LOGGER.error("Unable to generate excel file", ioException);
567 
		}
23017 ashik.ali 568 
	}
24119 govind 569 
 
570 
	public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId, int itemId, OutputStream outputStream) {
23819 govind 571 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 572 
 
573 
		// CreationHelper createHelper = workbook.getCreationHelper();
574 
 
23819 govind 575 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
576 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 577 
 
578 
		Row rowHeader = sheet.createRow(0);
579 
		Cell cellItemHeader = rowHeader.createCell(0);
580 
		cellItemHeader.setCellValue("ITEMID");
23819 govind 581 
		Cell cellIMEIHeader = rowHeader.createCell(1);
582 
		cellIMEIHeader.setCellValue("IMEI");
583 
		Cell cellFOFOIDHeader = rowHeader.createCell(2);
584 
		cellFOFOIDHeader.setCellValue("RETAILERNAME");
24119 govind 585 
		// Row rowQuantityRateValue = sheet.createRow(1);
586 
 
587 
		/*
588 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
589 
		 * rowQuantityRateValue.createCell(index);
590 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
591 
		 * rowQuantityRateValue.createCell(index + 1);
592 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
593 
		 * rowQuantityRateValue.createCell(index + 2);
594 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
595 
		 * CellRangeAddress(0, 0, index, index + 2)); }
596 
		 */
23819 govind 597 
		Font font = workbook.createFont();
598 
		CellStyle cellStyle = workbook.createCellStyle();
599 
		font.setBold(true);
600 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 601 
		// font.setFontHeight((short)16);
23819 govind 602 
		cellStyle.setFont(font);
24119 govind 603 
		for (int i = 0; i < 14; i++) {
604 
			if (rowHeader.getCell(i) != null) {
23819 govind 605 
				rowHeader.getCell(i).setCellStyle(cellStyle);
606 
			}
607 
		}
24119 govind 608 
		LinkedHashMap<String, String> priceAmount = new LinkedHashMap<>(priceDropIMEIfofoId);
609 
		Set<String> keyset = priceAmount.keySet();
23819 govind 610 
		int rownum = 1;
611 
		for (String key : keyset) {
24119 govind 612 
			Row row = sheet.createRow(rownum++);
613 
			String objArr = priceAmount.get(key);
614 
			int cellnum = 0;
615 
			Cell cell = row.createCell(cellnum++);
616 
			cell.setCellValue(itemId);
617 
			Cell cell1 = row.createCell(cellnum++);
618 
			cell1.setCellValue(key);
619 
			Cell cell2 = row.createCell(cellnum++);
620 
			cell2.setCellValue(priceAmount.get(key));
621 
		}
622 
 
623 
		for (int index = 0; index < 14; index++) {
23819 govind 624 
			sheet.autoSizeColumn(index);
625 
		}
24119 govind 626 
 
627 
		try {
23819 govind 628 
			workbook.write(outputStream);
24119 govind 629 
			workbook.close();
630 
		} catch (IOException ioException) {
631 
			LOGGER.error("Unable to generate excel file", ioException);
632 
		}
23819 govind 633 
	}
24119 govind 634 
 
635 
	public static void writePriceDropForAllIMEI(Map<String, String> priceDropAmount, OutputStream outputStream) {
23819 govind 636 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 637 
 
638 
		// CreationHelper createHelper = workbook.getCreationHelper();
639 
 
23819 govind 640 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
641 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 642 
 
643 
		Row rowHeader = sheet.createRow(0);
644 
		Cell cellItemHeader = rowHeader.createCell(0);
645 
		cellItemHeader.setCellValue("Itemdescription");
23819 govind 646 
		Cell cellIMEIHeader = rowHeader.createCell(1);
647 
		cellIMEIHeader.setCellValue("IMEI");
24119 govind 648 
		// Row rowQuantityRateValue = sheet.createRow(1);
649 
 
650 
		/*
651 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
652 
		 * rowQuantityRateValue.createCell(index);
653 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
654 
		 * rowQuantityRateValue.createCell(index + 1);
655 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
656 
		 * rowQuantityRateValue.createCell(index + 2);
657 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
658 
		 * CellRangeAddress(0, 0, index, index + 2)); }
659 
		 */
23819 govind 660 
		Font font = workbook.createFont();
661 
		CellStyle cellStyle = workbook.createCellStyle();
662 
		font.setBold(true);
663 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 664 
		// font.setFontHeight((short)16);
23819 govind 665 
		cellStyle.setFont(font);
24119 govind 666 
		for (int i = 0; i < 14; i++) {
667 
			if (rowHeader.getCell(i) != null) {
23819 govind 668 
				rowHeader.getCell(i).setCellStyle(cellStyle);
669 
			}
670 
		}
24119 govind 671 
		Set<String> keyset = priceDropAmount.keySet();
23819 govind 672 
		int rownum = 1;
673 
		for (String key : keyset) {
24119 govind 674 
			Row row = sheet.createRow(rownum++);
675 
			String objArr = priceDropAmount.get(key);
676 
			int cellnum = 0;
677 
			Cell cell = row.createCell(cellnum++);
678 
			cell.setCellValue(priceDropAmount.get(key));
679 
			Cell cell1 = row.createCell(cellnum++);
680 
			cell1.setCellValue(key);
681 
		}
682 
 
683 
		for (int index = 0; index < 14; index++) {
23819 govind 684 
			sheet.autoSizeColumn(index);
685 
		}
24119 govind 686 
 
687 
		try {
23819 govind 688 
			workbook.write(outputStream);
24119 govind 689 
			workbook.close();
690 
		} catch (IOException ioException) {
691 
			LOGGER.error("Unable to generate excel file", ioException);
692 
		}
23819 govind 693 
	}
24119 govind 694 
 
695 
	public static List<PartnerTargetModel> parseFromExcel(InputStream inputStream) throws Exception {
696 
 
24107 govind 697 
		List<PartnerTargetModel> partnerTargetModels = new ArrayList<>();
698 
		XSSFWorkbook myWorkBook = null;
24119 govind 699 
		try {
700 
			// FileInputStream fileInputStream = new
701 
			// FileInputStream("/home/ashikali/tag_listing1.xlsx");
702 
			myWorkBook = new XSSFWorkbook(inputStream);
703 
 
24107 govind 704 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
24119 govind 705 
			// Return first sheet from the XLSX workbook
24107 govind 706 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
707 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
24119 govind 708 
 
709 
			for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
24107 govind 710 
				XSSFRow row = mySheet.getRow(rowNumber);
711 
				LOGGER.info("row {}", row);
712 
				PartnerTargetModel partnerTargetModel = new PartnerTargetModel();
24119 govind 713 
 
714 
				if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
24107 govind 715 
					partnerTargetModel.setFofoId((int) row.getCell(0).getNumericCellValue());
24119 govind 716 
				} else {
717 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
718 
							FOFO_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
24107 govind 719 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
720 
					throw profitMandiBusinessException;
721 
				}
24119 govind 722 
				if (row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
723 
					partnerTargetModel.setTargetValue((int) row.getCell(3).getNumericCellValue());
724 
				} else {
725 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
726 
							TARGET_VALUE, row.getCell(8), "TGLSTNG_VE_1010");
24107 govind 727 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
728 
					throw profitMandiBusinessException;
729 
				}
730 
 
731 
				partnerTargetModels.add(partnerTargetModel);
732 
			}
733 
			myWorkBook.close();
24119 govind 734 
		} catch (IOException ioException) {
24107 govind 735 
			ioException.printStackTrace();
24119 govind 736 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
737 
					"EXL_VE_1000");
24107 govind 738 
		} finally {
24119 govind 739 
			if (myWorkBook != null) {
24107 govind 740 
				try {
741 
					myWorkBook.close();
742 
				} catch (IOException e) {
743 
					// TODO Auto-generated catch block
744 
					e.printStackTrace();
745 
				}
746 
			}
747 
		}
748 
		return partnerTargetModels;
749 
	}
24119 govind 750 
 
751 
	public static void writeDailySaleReportVsTarget(Map<Integer, Float> targetValues, Map<Integer, Double> saleValues,
752 
			Map<Integer, CustomRetailer> fofoIdsAndCustomRetailer, Map<Integer, String> fofoIdsAndSlabNames,
753 
			Map<Integer, Double> monthlyTargetAchievementPercentage, Map<Integer, Float> dailyAverageSale,
754 
			Map<Integer, Double> remainingMonthlyTargets, Map<Integer, Double> todayAchievements,
755 
			Map<Integer, Float> todayAchievementsPercentage, OutputStream outputStream) {
756 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
757 
 
758 
		// CreationHelper createHelper = workbook.getCreationHelper();
759 
 
760 
		SXSSFSheet sheet = workbook.createSheet("DailySaleReports");
761 
		sheet.trackAllColumnsForAutoSizing();
762 
 
763 
		Row rowHeader = sheet.createRow(0);
764 
		Cell cellFOfoId = rowHeader.createCell(0);
765 
		cellFOfoId.setCellValue("FoFoId");
766 
		Cell cellStoreName = rowHeader.createCell(1);
767 
		cellStoreName.setCellValue("Store Name");
768 
		Cell cellTargetValue = rowHeader.createCell(2);
769 
		cellTargetValue.setCellValue("MTD Target Value");
770 
		Cell cellDailyTarget = rowHeader.createCell(3);
771 
		cellDailyTarget.setCellValue("Daily Target");
772 
		Cell cellTodayAchievement = rowHeader.createCell(4);
773 
		cellTodayAchievement.setCellValue("Today Achievement");
774 
		Cell cellTodayAchievementPercentage = rowHeader.createCell(5);
775 
		cellTodayAchievementPercentage.setCellValue("Today Achievement %");
776 
		Cell cellMonthlySaleValue = rowHeader.createCell(6);
777 
		cellMonthlySaleValue.setCellValue("MTD Achievement");
778 
		Cell cellMonthlySaleValuePercentage = rowHeader.createCell(7);
779 
		cellMonthlySaleValuePercentage.setCellValue("MTD Achievement%");
780 
		Cell cellRemainingTarget = rowHeader.createCell(8);
781 
		cellRemainingTarget.setCellValue("Remaining");
782 
		Cell cellEligibility = rowHeader.createCell(9);
783 
		cellEligibility.setCellValue("Eligibility");
784 
		Font font = workbook.createFont();
785 
		CellStyle cellStyle = workbook.createCellStyle();
786 
		font.setBold(true);
787 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
788 
		// font.setFontHeight((short)16);
789 
		cellStyle.setFont(font);
790 
		for (int i = 0; i < 14; i++) {
791 
			if (rowHeader.getCell(i) != null) {
792 
				rowHeader.getCell(i).setCellStyle(cellStyle);
793 
			}
794 
		}
795 
		int rownum = 1;
796 
		for (Integer fofoId : saleValues.keySet()) {
797 
			Row row = sheet.createRow(rownum++);
798 
			int cellnum = 0;
799 
			Cell cellFofoId = row.createCell(cellnum++);
800 
			cellFofoId.setCellValue(fofoId);
801 
			Cell cellPartner = row.createCell(cellnum++);
802 
			if (fofoIdsAndCustomRetailer.get(fofoId)!= null) {
803 
				cellPartner.setCellValue(fofoIdsAndCustomRetailer.get(fofoId).getBusinessName());
804 
			} else {
805 
				cellPartner.setCellValue("Name Not Associated");
806 
			}
807 
 
808 
			Cell cellMTDTargetValue = row.createCell(cellnum++);
809 
			if (targetValues.get(fofoId) != null) {
810 
				cellMTDTargetValue.setCellValue(targetValues.get(fofoId));
811 
			} else {
812 
				cellMTDTargetValue.setCellValue("Target not defined");
813 
			}
814 
 
815 
			Cell cellDailyTargetValue = row.createCell(cellnum++);
816 
			if (dailyAverageSale.get(fofoId) != null) {
817 
				String formatting=FormattingUtils.formatDecimalTwoDigits(dailyAverageSale.get(fofoId));
818 
				cellDailyTargetValue.setCellValue(formatting);
819 
			} else {
820 
				cellDailyTargetValue.setCellValue(0);
821 
			}
822 
			Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);
823 
			if (todayAchievements.get(fofoId) != null) {
824 
				cellTodayAchieveMentSaleValue.setCellValue(todayAchievements.get(fofoId));
825 
			} else {
826 
				cellTodayAchieveMentSaleValue.setCellValue(0);
827 
			}
828 
			Cell cellTodayAchieveMentSaleValuePercentage = row.createCell(cellnum++);
829 
			if (todayAchievementsPercentage.get(fofoId) != null) {
830 
				cellTodayAchieveMentSaleValuePercentage.setCellValue(todayAchievementsPercentage.get(fofoId) + "%");
831 
			} else {
832 
				cellTodayAchieveMentSaleValuePercentage.setCellValue(0 + "%");
833 
 
834 
			}
835 
			Cell cellMTDAchievement = row.createCell(cellnum++);
836 
			if (saleValues.get(fofoId) != null) {
837 
				cellMTDAchievement.setCellValue(saleValues.get(fofoId));
838 
			} else {
839 
				cellMTDAchievement.setCellValue("No sale");
840 
			}
841 
			Cell cellMTDAchievementPercentage = row.createCell(cellnum++);
842 
			if (monthlyTargetAchievementPercentage.get(fofoId) != null) {
843 
				cellMTDAchievementPercentage.setCellValue(monthlyTargetAchievementPercentage.get(fofoId) + "%");
844 
			} else {
845 
				cellMTDAchievementPercentage.setCellValue(0 + "%");
846 
			}
847 
 
848 
			Cell cellRemaining = row.createCell(cellnum++);
849 
			if (remainingMonthlyTargets.get(fofoId) != null) {
850 
				cellRemaining.setCellValue(remainingMonthlyTargets.get(fofoId));
851 
			} else {
852 
				cellRemaining.setCellValue("no target define");
853 
			}
854 
 
855 
			Cell cellEligible = row.createCell(cellnum++);
856 
			if (fofoIdsAndSlabNames.get(fofoId) != null) {
857 
				cellEligible.setCellValue(fofoIdsAndSlabNames.get(fofoId));
858 
			} else {
859 
				cellEligible.setCellValue("No slab");
860 
			}
861 
		}
862 
 
863 
		for (int index = 0; index < saleValues.size(); index++) {
864 
			sheet.autoSizeColumn(index);
865 
		}
866 
 
867 
		try {
868 
			workbook.write(outputStream);
869 
			workbook.close();
870 
		} catch (IOException ioException) {
871 
			LOGGER.error("Unable to generate excel file", ioException);
872 
		}
873 
	}
874 
 
21786 ashik.ali 875 
}