Subversion Repositories SmartDukaan

Rev

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