Subversion Repositories SmartDukaan

Rev

Rev 24188 | Rev 24215 | 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();
170 
 
22470 ashik.ali 171 
		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
172 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
173 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
174 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
175 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
176 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
177 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
178 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 179 
 
180 
		Row rowHeader = sheet.createRow(0);
181 
		Cell cellItemIdHeader = rowHeader.createCell(0);
22470 ashik.ali 182 
		cellItemIdHeader.setCellValue("Item Id");
183 
		Cell cellBrandHeader = rowHeader.createCell(1);
184 
		cellBrandHeader.setCellValue("Brand");
185 
		Cell cellModelNameHeader = rowHeader.createCell(2);
186 
		cellModelNameHeader.setCellValue("Model Name");
187 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
188 
		cellModelNumberHeader.setCellValue("Model Number");
189 
		Cell cellColorHeader = rowHeader.createCell(4);
190 
		cellColorHeader.setCellValue("Color");
191 
		Cell cellTypeHeader = rowHeader.createCell(5);
192 
		cellTypeHeader.setCellValue("Item Type");
193 
		Row rowPriceQuantity = sheet.createRow(1);
24119 govind 194 
		for (int index = 0, colIndex = 6; index < intervals.size() + 1; index++, colIndex = colIndex + 2) {
22470 ashik.ali 195 
			Cell cellHeader = rowHeader.createCell(colIndex);
24119 govind 196 
			if (index == 0) {
197 
				cellHeader.setCellValue("Less Than " + intervals.get(index) + " Days");
198 
			} else if (index < intervals.size()) {
22470 ashik.ali 199 
				cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");
24119 govind 200 
			} else {
201 
				cellHeader.setCellValue("More Than " + intervals.get(index - 1) + " Days");
22470 ashik.ali 202 
			}
203 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, colIndex, colIndex + 1));
204 
			rowHeader.createCell(colIndex + 1);
205 
			Cell cellPrice = rowPriceQuantity.createCell(colIndex);
206 
			cellPrice.setCellValue("Price");
207 
			Cell cellQuantity = rowPriceQuantity.createCell(colIndex + 1);
208 
			cellQuantity.setCellValue("Quantity");
209 
		}
210 
		Font font = workbook.createFont();
211 
		CellStyle cellStyle = workbook.createCellStyle();
212 
		font.setBold(true);
24119 govind 213 
		// font.setFontHeight((short)16);
22470 ashik.ali 214 
		cellStyle.setFont(font);
24119 govind 215 
		for (int i = 0; i < 8 + (intervals.size() * 2); i++) {
22470 ashik.ali 216 
			rowHeader.getCell(i).setCellStyle(cellStyle);
24119 govind 217 
			if (rowPriceQuantity.getCell(i) != null) {
22470 ashik.ali 218 
				rowPriceQuantity.getCell(i).setCellStyle(cellStyle);
219 
			}
220 
		}
24119 govind 221 
 
222 
		for (int index = 0; index < inventoryItemAgingModels.size(); index++) {
22470 ashik.ali 223 
			InventoryItemAgingModel inventoryItemAgingModel = inventoryItemAgingModels.get(index);
24119 govind 224 
			Row rowValues = sheet.createRow(index + 2);
225 
			Cell cellItemId = rowValues.createCell(0);
226 
			cellItemId.setCellValue(inventoryItemAgingModel.getItemId());
227 
			Cell cellBrand = rowValues.createCell(1);
228 
			cellBrand.setCellValue(inventoryItemAgingModel.getBrand());
229 
			Cell cellModelName = rowValues.createCell(2);
230 
			cellModelName.setCellValue(inventoryItemAgingModel.getModelName());
231 
			Cell cellModelNumber = rowValues.createCell(3);
232 
			cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());
233 
			Cell cellColor = rowValues.createCell(4);
234 
			cellColor.setCellValue(inventoryItemAgingModel.getColor());
235 
			Cell cellType = rowValues.createCell(5);
236 
			cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());
237 
			List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
238 
			// LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
239 
			for (int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2) {
240 
				Cell cellPrice = rowValues.createCell(colIndex);
241 
				InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i);
242 
				// LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);
243 
				Cell cellQuantity = rowValues.createCell(colIndex + 1);
244 
				if (inventoryItemAgingValue != null) {
245 
					cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
246 
					cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
247 
				} else {
248 
					cellPrice.setCellValue("-");
249 
					cellQuantity.setCellValue("-");
250 
				}
251 
			}
252 
		}
253 
 
254 
		for (int index = 0; index < 8 + (intervals.size() * 2); index++) {
22470 ashik.ali 255 
			sheet.autoSizeColumn(index);
256 
		}
24119 govind 257 
 
258 
		try {
22486 ashik.ali 259 
			workbook.write(outputStream);
24119 govind 260 
			workbook.close();
261 
		} catch (IOException ioException) {
262 
			LOGGER.error("Unable to generate excel file", ioException);
263 
		}
22470 ashik.ali 264 
	}
24119 govind 265 
 
266 
	public static void writeItemCompleteLedgerModels(List<ItemCompleteLedgerModel> itemCompleteLedgerModels,
267 
			OutputStream outputStream) {
22521 ashik.ali 268 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 269 
 
270 
		// CreationHelper createHelper = workbook.getCreationHelper();
271 
 
22521 ashik.ali 272 
		SXSSFSheet sheet = workbook.createSheet("ItemCompleteLeger");
273 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
274 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
275 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
276 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
277 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
278 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
279 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 280 
 
281 
		Row rowHeader = sheet.createRow(0);
282 
		Cell cellItemIdHeader = rowHeader.createCell(0);
22521 ashik.ali 283 
		cellItemIdHeader.setCellValue("Item Id");
284 
		Cell cellBrandHeader = rowHeader.createCell(1);
285 
		cellBrandHeader.setCellValue("Brand");
286 
		Cell cellModelNameHeader = rowHeader.createCell(2);
287 
		cellModelNameHeader.setCellValue("Model Name");
288 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
289 
		cellModelNumberHeader.setCellValue("Model Number");
290 
		Cell cellColorHeader = rowHeader.createCell(4);
291 
		cellColorHeader.setCellValue("Color");
292 
		Cell cellTypeHeader = rowHeader.createCell(5);
293 
		cellTypeHeader.setCellValue("Item Type");
294 
		Cell cellOpeningBalanceHeader = rowHeader.createCell(6);
295 
		cellOpeningBalanceHeader.setCellValue("Opening Balance");
296 
		Cell cellInwardsHeader = rowHeader.createCell(9);
297 
		cellInwardsHeader.setCellValue("Inwards");
298 
		Cell cellOutwardsHeader = rowHeader.createCell(12);
299 
		cellOutwardsHeader.setCellValue("Outwards");
300 
		Cell cellClosingBalanceHeader = rowHeader.createCell(15);
301 
		cellClosingBalanceHeader.setCellValue("Closing Balance");
302 
		Row rowQuantityRateValue = sheet.createRow(1);
24119 govind 303 
 
304 
		for (int index = 6; index < 18; index = index + 3) {
22521 ashik.ali 305 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
306 
			cellQuantityHeader.setCellValue("Quantity");
307 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
308 
			cellRateHeader.setCellValue("Rate");
309 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
310 
			cellValueHeader.setCellValue("Value");
311 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
312 
		}
313 
		Font font = workbook.createFont();
314 
		CellStyle cellStyle = workbook.createCellStyle();
315 
		font.setBold(true);
316 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 317 
		// font.setFontHeight((short)16);
22521 ashik.ali 318 
		cellStyle.setFont(font);
24119 govind 319 
		for (int i = 0; i < 18; i++) {
320 
			if (rowHeader.getCell(i) != null) {
22521 ashik.ali 321 
				rowHeader.getCell(i).setCellStyle(cellStyle);
322 
			}
323 
		}
324 
		int openingQuantityTotal = 0;
325 
		float openingValueTotal = 0;
326 
		int inwardsQuantityTotal = 0;
327 
		float inwardsValueTotal = 0;
328 
		int outwardsQuantityTotal = 0;
329 
		float outwardsValueTotal = 0;
330 
		int closingQuantityTotal = 0;
331 
		float closingValueTotal = 0;
24119 govind 332 
		for (int index = 0; index < itemCompleteLedgerModels.size(); index++) {
22521 ashik.ali 333 
			ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
24119 govind 334 
			Row rowValues = sheet.createRow(index + 2);
335 
			Cell cellItemId = rowValues.createCell(0);
336 
			cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
337 
			Cell cellBrand = rowValues.createCell(1);
338 
			cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
339 
			Cell cellModelName = rowValues.createCell(2);
340 
			cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
341 
			Cell cellModelNumber = rowValues.createCell(3);
342 
			cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
343 
			Cell cellColor = rowValues.createCell(4);
344 
			cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
345 
			Cell cellType = rowValues.createCell(5);
346 
			cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
347 
			Cell cellOpeningQuantity = rowValues.createCell(6);
348 
			Cell cellOpeningRate = rowValues.createCell(7);
349 
			Cell cellOpeningValue = rowValues.createCell(8);
350 
			if (itemCompleteLedgerModel.getOpeningLedger() == null) {
351 
				cellOpeningQuantity.setCellValue("-");
352 
				cellOpeningRate.setCellValue("-");
353 
				cellOpeningValue.setCellValue("-");
354 
			} else {
355 
				cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
356 
				cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
357 
				cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
358 
				openingQuantityTotal = openingQuantityTotal + itemCompleteLedgerModel.getOpeningLedger().getQuantity();
359 
				openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
360 
			}
361 
			Cell cellInwardsQuantity = rowValues.createCell(9);
362 
			Cell cellInwardsRate = rowValues.createCell(10);
363 
			Cell cellInwardsValue = rowValues.createCell(11);
364 
			if (itemCompleteLedgerModel.getInwardsLedger() == null) {
365 
				cellInwardsQuantity.setCellValue("-");
366 
				cellInwardsRate.setCellValue("-");
367 
				cellInwardsValue.setCellValue("-");
368 
			} else {
369 
				cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
370 
				cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
371 
				cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
372 
				inwardsQuantityTotal = inwardsQuantityTotal + itemCompleteLedgerModel.getInwardsLedger().getQuantity();
373 
				inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
374 
			}
375 
			Cell cellOutwardsQuantity = rowValues.createCell(12);
376 
			Cell cellOutwardsRate = rowValues.createCell(13);
377 
			Cell cellOutwardsValue = rowValues.createCell(14);
378 
			if (itemCompleteLedgerModel.getOutwardsLedger() == null) {
379 
				cellOutwardsQuantity.setCellValue("-");
380 
				cellOutwardsRate.setCellValue("-");
381 
				cellOutwardsValue.setCellValue("-");
382 
			} else {
383 
				cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
384 
				cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
385 
				cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
386 
				outwardsQuantityTotal = outwardsQuantityTotal
387 
						+ itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
388 
				outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
389 
			}
390 
			Cell cellClosingQuantity = rowValues.createCell(15);
391 
			Cell cellClosingRate = rowValues.createCell(16);
392 
			Cell cellClosingValue = rowValues.createCell(17);
393 
			if (itemCompleteLedgerModel.getClosingLedger() == null) {
394 
				cellClosingQuantity.setCellValue("-");
395 
				cellClosingRate.setCellValue("-");
396 
				cellClosingValue.setCellValue("-");
397 
			} else {
398 
				cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
399 
				cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
400 
				cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
401 
				closingQuantityTotal = closingQuantityTotal + itemCompleteLedgerModel.getClosingLedger().getQuantity();
402 
				closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
403 
			}
404 
		}
405 
 
22521 ashik.ali 406 
		Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);
24119 govind 407 
 
408 
		if (openingQuantityTotal > 0) {
22521 ashik.ali 409 
			Cell cellOpeningQuantityTotal = rowTotal.createCell(6);
410 
			cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);
411 
			Cell cellOpeningValueTotal = rowTotal.createCell(8);
412 
			cellOpeningValueTotal.setCellValue(openingValueTotal);
413 
		}
24119 govind 414 
 
415 
		if (inwardsQuantityTotal > 0) {
22521 ashik.ali 416 
			Cell cellInwardsQuantityTotal = rowTotal.createCell(9);
417 
			cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);
418 
			Cell cellInwardsValueTotal = rowTotal.createCell(11);
419 
			cellInwardsValueTotal.setCellValue(inwardsValueTotal);
420 
		}
24119 govind 421 
 
422 
		if (outwardsQuantityTotal > 0) {
22521 ashik.ali 423 
			Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);
424 
			cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);
425 
			Cell cellOutwardsValueTotal = rowTotal.createCell(14);
426 
			cellOutwardsValueTotal.setCellValue(outwardsValueTotal);
427 
		}
24119 govind 428 
 
429 
		if (closingQuantityTotal > 0) {
22521 ashik.ali 430 
			Cell cellClosingQuantityTotal = rowTotal.createCell(15);
431 
			cellClosingQuantityTotal.setCellValue(closingQuantityTotal);
432 
			Cell cellClosingValueTotal = rowTotal.createCell(17);
433 
			cellClosingValueTotal.setCellValue(closingValueTotal);
434 
		}
24119 govind 435 
 
436 
		for (int index = 0; index < 18; index++) {
22521 ashik.ali 437 
			sheet.autoSizeColumn(index);
438 
		}
24119 govind 439 
 
440 
		try {
22521 ashik.ali 441 
			workbook.write(outputStream);
24119 govind 442 
			workbook.close();
443 
		} catch (IOException ioException) {
444 
			LOGGER.error("Unable to generate excel file", ioException);
445 
		}
22521 ashik.ali 446 
	}
24119 govind 447 
 
448 
	public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream) {
23017 ashik.ali 449 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 450 
 
451 
		// CreationHelper createHelper = workbook.getCreationHelper();
452 
 
23017 ashik.ali 453 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
454 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 455 
 
456 
		Row rowHeader = sheet.createRow(0);
457 
		Cell cellSchemeIdHeader = rowHeader.createCell(0);
23017 ashik.ali 458 
		cellSchemeIdHeader.setCellValue("Scheme Id");
459 
		Cell cellNameHeader = rowHeader.createCell(1);
460 
		cellNameHeader.setCellValue("Name");
461 
		Cell cellDescriptionHeader = rowHeader.createCell(2);
462 
		cellDescriptionHeader.setCellValue("Description");
463 
		Cell cellSchemeTypeHeader = rowHeader.createCell(3);
464 
		cellSchemeTypeHeader.setCellValue("Scheme Type");
465 
		Cell cellAmountTypeHeader = rowHeader.createCell(4);
466 
		cellAmountTypeHeader.setCellValue("Amount Type");
467 
		Cell cellAmountHeader = rowHeader.createCell(5);
468 
		cellAmountHeader.setCellValue("Amount");
469 
		Cell cellStartDateTimeHeader = rowHeader.createCell(6);
470 
		cellStartDateTimeHeader.setCellValue("Start Date Time");
471 
		Cell cellEndDateTimeHeader = rowHeader.createCell(7);
472 
		cellEndDateTimeHeader.setCellValue("End Date Time");
473 
		Cell cellCreatedAtHeader = rowHeader.createCell(8);
474 
		cellCreatedAtHeader.setCellValue("Created At");
475 
		Cell cellActiveHeader = rowHeader.createCell(9);
476 
		cellActiveHeader.setCellValue("Active");
477 
		Cell cellExpireHeader = rowHeader.createCell(10);
478 
		cellExpireHeader.setCellValue("Expire");
479 
		Cell cellCreatedByHeader = rowHeader.createCell(11);
480 
		cellCreatedByHeader.setCellValue("Created By");
481 
		Cell cellItemIdsHeader = rowHeader.createCell(12);
482 
		cellItemIdsHeader.setCellValue("Item Ids");
483 
		Cell cellRetailerIdsHeader = rowHeader.createCell(13);
484 
		cellRetailerIdsHeader.setCellValue("Retailer Ids");
24119 govind 485 
		// Row rowQuantityRateValue = sheet.createRow(1);
486 
 
487 
		/*
488 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
489 
		 * rowQuantityRateValue.createCell(index);
490 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
491 
		 * rowQuantityRateValue.createCell(index + 1);
492 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
493 
		 * rowQuantityRateValue.createCell(index + 2);
494 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
495 
		 * CellRangeAddress(0, 0, index, index + 2)); }
496 
		 */
23017 ashik.ali 497 
		Font font = workbook.createFont();
498 
		CellStyle cellStyle = workbook.createCellStyle();
499 
		font.setBold(true);
500 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 501 
		// font.setFontHeight((short)16);
23017 ashik.ali 502 
		cellStyle.setFont(font);
24119 govind 503 
		for (int i = 0; i < 14; i++) {
504 
			if (rowHeader.getCell(i) != null) {
23017 ashik.ali 505 
				rowHeader.getCell(i).setCellStyle(cellStyle);
506 
			}
507 
		}
508 
 
24119 govind 509 
		for (int index = 0; index < schemeModels.size(); index++) {
23017 ashik.ali 510 
			SchemeModel schemeModel = schemeModels.get(index);
24119 govind 511 
			// ItemCompleteLedgerModel itemCompleteLedgerModel =
512 
			// itemCompleteLedgerModels.get(index);
513 
			Row rowValues = sheet.createRow(index + 1);
514 
			Cell cellSchemeId = rowValues.createCell(0);
23017 ashik.ali 515 
			cellSchemeId.setCellValue(schemeModel.getSchemeId());
516 
			Cell cellName = rowValues.createCell(1);
517 
			cellName.setCellValue(schemeModel.getName());
518 
			Cell cellDescription = rowValues.createCell(2);
519 
			cellDescription.setCellValue(schemeModel.getDescription());
520 
			Cell cellSchemeType = rowValues.createCell(3);
521 
			cellSchemeType.setCellValue(schemeModel.getSchemeType());
522 
			Cell cellAmountType = rowValues.createCell(4);
523 
			cellAmountType.setCellValue(schemeModel.getAmountType());
524 
			Cell cellAmount = rowValues.createCell(5);
525 
			cellAmount.setCellValue(schemeModel.getAmount());
526 
			Cell cellStartDateTime = rowValues.createCell(6);
527 
			cellStartDateTime.setCellValue(schemeModel.getStartDateTime());
528 
			Cell cellEndDateTime = rowValues.createCell(7);
529 
			cellEndDateTime.setCellValue(schemeModel.getEndDateTime());
530 
			Cell cellCreatedAt = rowValues.createCell(8);
531 
			cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());
532 
			Cell cellActive = rowValues.createCell(9);
24119 govind 533 
			if (schemeModel.getActiveTimestamp() != null) {
23017 ashik.ali 534 
				cellActive.setCellValue(schemeModel.getActiveTimestamp());
24119 govind 535 
			} else {
23017 ashik.ali 536 
				cellActive.setCellValue("False");
537 
			}
538 
			Cell cellExpire = rowValues.createCell(10);
24119 govind 539 
			if (schemeModel.getExpireTimestamp() != null) {
23017 ashik.ali 540 
				cellExpire.setCellValue(schemeModel.getExpireTimestamp());
24119 govind 541 
			} else {
23017 ashik.ali 542 
				cellExpire.setCellValue("False");
543 
			}
544 
			Cell cellCreatedBy = rowValues.createCell(11);
545 
			cellCreatedBy.setCellValue(schemeModel.getCreatedBy());
546 
			Cell cellItemIds = rowValues.createCell(12);
23338 ashik.ali 547 
			cellItemIds.setCellValue(schemeModel.getItemStringMap().toString());
23017 ashik.ali 548 
			Cell cellRetailerIds = rowValues.createCell(13);
549 
			cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());
23338 ashik.ali 550 
			int maxHeight = Math.max(schemeModel.getItemStringMap().size(), schemeModel.getRetailerIds().size());
24119 govind 551 
			if (maxHeight > 1) {
552 
				rowValues.setHeight((short) (maxHeight * 240));
23017 ashik.ali 553 
			}
24119 govind 554 
		}
555 
 
556 
		for (int index = 0; index < 14; index++) {
23017 ashik.ali 557 
			sheet.autoSizeColumn(index);
558 
		}
24119 govind 559 
 
560 
		try {
23017 ashik.ali 561 
			workbook.write(outputStream);
24119 govind 562 
			workbook.close();
563 
		} catch (IOException ioException) {
564 
			LOGGER.error("Unable to generate excel file", ioException);
565 
		}
23017 ashik.ali 566 
	}
24119 govind 567 
 
568 
	public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId, int itemId, OutputStream outputStream) {
23819 govind 569 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 570 
 
571 
		// CreationHelper createHelper = workbook.getCreationHelper();
572 
 
23819 govind 573 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
574 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 575 
 
576 
		Row rowHeader = sheet.createRow(0);
577 
		Cell cellItemHeader = rowHeader.createCell(0);
578 
		cellItemHeader.setCellValue("ITEMID");
23819 govind 579 
		Cell cellIMEIHeader = rowHeader.createCell(1);
580 
		cellIMEIHeader.setCellValue("IMEI");
581 
		Cell cellFOFOIDHeader = rowHeader.createCell(2);
582 
		cellFOFOIDHeader.setCellValue("RETAILERNAME");
24119 govind 583 
		// Row rowQuantityRateValue = sheet.createRow(1);
584 
 
585 
		/*
586 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
587 
		 * rowQuantityRateValue.createCell(index);
588 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
589 
		 * rowQuantityRateValue.createCell(index + 1);
590 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
591 
		 * rowQuantityRateValue.createCell(index + 2);
592 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
593 
		 * CellRangeAddress(0, 0, index, index + 2)); }
594 
		 */
23819 govind 595 
		Font font = workbook.createFont();
596 
		CellStyle cellStyle = workbook.createCellStyle();
597 
		font.setBold(true);
598 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 599 
		// font.setFontHeight((short)16);
23819 govind 600 
		cellStyle.setFont(font);
24119 govind 601 
		for (int i = 0; i < 14; i++) {
602 
			if (rowHeader.getCell(i) != null) {
23819 govind 603 
				rowHeader.getCell(i).setCellStyle(cellStyle);
604 
			}
605 
		}
24119 govind 606 
		LinkedHashMap<String, String> priceAmount = new LinkedHashMap<>(priceDropIMEIfofoId);
607 
		Set<String> keyset = priceAmount.keySet();
23819 govind 608 
		int rownum = 1;
609 
		for (String key : keyset) {
24119 govind 610 
			Row row = sheet.createRow(rownum++);
611 
			String objArr = priceAmount.get(key);
612 
			int cellnum = 0;
613 
			Cell cell = row.createCell(cellnum++);
614 
			cell.setCellValue(itemId);
615 
			Cell cell1 = row.createCell(cellnum++);
616 
			cell1.setCellValue(key);
617 
			Cell cell2 = row.createCell(cellnum++);
618 
			cell2.setCellValue(priceAmount.get(key));
619 
		}
620 
 
621 
		for (int index = 0; index < 14; index++) {
23819 govind 622 
			sheet.autoSizeColumn(index);
623 
		}
24119 govind 624 
 
625 
		try {
23819 govind 626 
			workbook.write(outputStream);
24119 govind 627 
			workbook.close();
628 
		} catch (IOException ioException) {
629 
			LOGGER.error("Unable to generate excel file", ioException);
630 
		}
23819 govind 631 
	}
24119 govind 632 
 
633 
	public static void writePriceDropForAllIMEI(Map<String, String> priceDropAmount, OutputStream outputStream) {
23819 govind 634 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 635 
 
636 
		// CreationHelper createHelper = workbook.getCreationHelper();
637 
 
23819 govind 638 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
639 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 640 
 
641 
		Row rowHeader = sheet.createRow(0);
642 
		Cell cellItemHeader = rowHeader.createCell(0);
643 
		cellItemHeader.setCellValue("Itemdescription");
23819 govind 644 
		Cell cellIMEIHeader = rowHeader.createCell(1);
645 
		cellIMEIHeader.setCellValue("IMEI");
24119 govind 646 
		// Row rowQuantityRateValue = sheet.createRow(1);
647 
 
648 
		/*
649 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
650 
		 * rowQuantityRateValue.createCell(index);
651 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
652 
		 * rowQuantityRateValue.createCell(index + 1);
653 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
654 
		 * rowQuantityRateValue.createCell(index + 2);
655 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
656 
		 * CellRangeAddress(0, 0, index, index + 2)); }
657 
		 */
23819 govind 658 
		Font font = workbook.createFont();
659 
		CellStyle cellStyle = workbook.createCellStyle();
660 
		font.setBold(true);
661 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 662 
		// font.setFontHeight((short)16);
23819 govind 663 
		cellStyle.setFont(font);
24119 govind 664 
		for (int i = 0; i < 14; i++) {
665 
			if (rowHeader.getCell(i) != null) {
23819 govind 666 
				rowHeader.getCell(i).setCellStyle(cellStyle);
667 
			}
668 
		}
24119 govind 669 
		Set<String> keyset = priceDropAmount.keySet();
23819 govind 670 
		int rownum = 1;
671 
		for (String key : keyset) {
24119 govind 672 
			Row row = sheet.createRow(rownum++);
673 
			String objArr = priceDropAmount.get(key);
674 
			int cellnum = 0;
675 
			Cell cell = row.createCell(cellnum++);
676 
			cell.setCellValue(priceDropAmount.get(key));
677 
			Cell cell1 = row.createCell(cellnum++);
678 
			cell1.setCellValue(key);
679 
		}
680 
 
681 
		for (int index = 0; index < 14; index++) {
23819 govind 682 
			sheet.autoSizeColumn(index);
683 
		}
24119 govind 684 
 
685 
		try {
23819 govind 686 
			workbook.write(outputStream);
24119 govind 687 
			workbook.close();
688 
		} catch (IOException ioException) {
689 
			LOGGER.error("Unable to generate excel file", ioException);
690 
		}
23819 govind 691 
	}
24119 govind 692 
 
693 
	public static List<PartnerTargetModel> parseFromExcel(InputStream inputStream) throws Exception {
694 
 
24107 govind 695 
		List<PartnerTargetModel> partnerTargetModels = new ArrayList<>();
696 
		XSSFWorkbook myWorkBook = null;
24119 govind 697 
		try {
698 
			// FileInputStream fileInputStream = new
699 
			// FileInputStream("/home/ashikali/tag_listing1.xlsx");
700 
			myWorkBook = new XSSFWorkbook(inputStream);
701 
 
24107 govind 702 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
24119 govind 703 
			// Return first sheet from the XLSX workbook
24107 govind 704 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
705 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
24119 govind 706 
 
707 
			for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
24107 govind 708 
				XSSFRow row = mySheet.getRow(rowNumber);
709 
				LOGGER.info("row {}", row);
710 
				PartnerTargetModel partnerTargetModel = new PartnerTargetModel();
24119 govind 711 
 
712 
				if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
24107 govind 713 
					partnerTargetModel.setFofoId((int) row.getCell(0).getNumericCellValue());
24119 govind 714 
				} else {
715 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
716 
							FOFO_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
24107 govind 717 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
718 
					throw profitMandiBusinessException;
719 
				}
24119 govind 720 
				if (row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
721 
					partnerTargetModel.setTargetValue((int) row.getCell(3).getNumericCellValue());
722 
				} else {
723 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
724 
							TARGET_VALUE, row.getCell(8), "TGLSTNG_VE_1010");
24107 govind 725 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
726 
					throw profitMandiBusinessException;
727 
				}
728 
 
729 
				partnerTargetModels.add(partnerTargetModel);
730 
			}
731 
			myWorkBook.close();
24119 govind 732 
		} catch (IOException ioException) {
24107 govind 733 
			ioException.printStackTrace();
24119 govind 734 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
735 
					"EXL_VE_1000");
24107 govind 736 
		} finally {
24119 govind 737 
			if (myWorkBook != null) {
24107 govind 738 
				try {
739 
					myWorkBook.close();
740 
				} catch (IOException e) {
741 
					// TODO Auto-generated catch block
742 
					e.printStackTrace();
743 
				}
744 
			}
745 
		}
746 
		return partnerTargetModels;
747 
	}
24119 govind 748 
 
24177 govind 749 
	public static void writeDailySaleReportVsTarget(String targetName,Map<Integer, Float> targetValues, Map<Integer, Double> saleValues,
24119 govind 750 
			Map<Integer, CustomRetailer> fofoIdsAndCustomRetailer, Map<Integer, String> fofoIdsAndSlabNames,
751 
			Map<Integer, Double> monthlyTargetAchievementPercentage, Map<Integer, Float> dailyAverageSale,
752 
			Map<Integer, Double> remainingMonthlyTargets, Map<Integer, Double> todayAchievements,
24171 govind 753 
			 List<Integer> fofoIds,
24131 govind 754 
			Map<Integer, String> salesHeadNameAndFofoIdMap, OutputStream outputStream) {
24119 govind 755 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
756 
 
757 
		// CreationHelper createHelper = workbook.getCreationHelper();
758 
 
759 
		SXSSFSheet sheet = workbook.createSheet("DailySaleReports");
760 
		sheet.trackAllColumnsForAutoSizing();
761 
 
762 
		Row rowHeader = sheet.createRow(0);
24188 govind 763 
		Cell cellStoreName = rowHeader.createCell(0);
764 
		cellStoreName.setCellValue("Store Name");
24177 govind 765 
		Cell cellBusiness = rowHeader.createCell(1);
24133 govind 766 
		cellBusiness.setCellValue("Business Manager");
24177 govind 767 
		Cell cellAssistant = rowHeader.createCell(2);
24133 govind 768 
		cellAssistant.setCellValue("Assistant Manager");
24188 govind 769 
		Cell cellSchemeName = rowHeader.createCell(3);
770 
		cellSchemeName.setCellValue("Scheme Name");
771 
		Cell cellTargetValue = rowHeader.createCell(4);
772 
		cellTargetValue.setCellValue("Scheme Target");
773 
		Cell cellTotalAchievement = rowHeader.createCell(5);
774 
		cellTotalAchievement.setCellValue("Total Achievement");
775 
		Cell cellTotalAchievementPercentage = rowHeader.createCell(6);
776 
		cellTotalAchievementPercentage.setCellValue("Total Achievement Percentage");
777 
		Cell cellDailyTarget = rowHeader.createCell(7);
24119 govind 778 
		cellDailyTarget.setCellValue("Daily Target");
24188 govind 779 
		Cell cellTodayAchievement = rowHeader.createCell(8);
780 
		cellTodayAchievement.setCellValue("Today Achievement");
781 
		Cell cellRemainingTarget = rowHeader.createCell(9);
782 
		cellRemainingTarget.setCellValue("Remaining Target");
783 
		Cell cellEligibility = rowHeader.createCell(10);
24119 govind 784 
		cellEligibility.setCellValue("Eligibility");
785 
		Font font = workbook.createFont();
786 
		CellStyle cellStyle = workbook.createCellStyle();
787 
		font.setBold(true);
788 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
789 
		// font.setFontHeight((short)16);
790 
		cellStyle.setFont(font);
791 
		for (int i = 0; i < 14; i++) {
792 
			if (rowHeader.getCell(i) != null) {
793 
				rowHeader.getCell(i).setCellStyle(cellStyle);
794 
			}
795 
		}
796 
		int rownum = 1;
24131 govind 797 
		for (Integer fofoId : fofoIds) {
24119 govind 798 
			Row row = sheet.createRow(rownum++);
799 
			int cellnum = 0;
24188 govind 800 
			Cell cellPartner = row.createCell(cellnum++);
801 
			if (fofoIdsAndCustomRetailer.get(fofoId) != null) {
802 
				cellPartner.setCellValue(fofoIdsAndCustomRetailer.get(fofoId).getBusinessName());
24178 govind 803 
			} else {
24188 govind 804 
				cellPartner.setCellValue("-");
24177 govind 805 
			}
24133 govind 806 
			Cell cellBusinessManager = row.createCell(cellnum++);
24131 govind 807 
			if (salesHeadNameAndFofoIdMap.get(fofoId) != null) {
24133 govind 808 
				if (salesHeadNameAndFofoIdMap.get(fofoId).equals("Kamal")) {
809 
					cellBusinessManager.setCellValue("Mohinder");
810 
				} else {
811 
					cellBusinessManager.setCellValue(salesHeadNameAndFofoIdMap.get(fofoId));
812 
				}
24131 govind 813 
			} else {
24133 govind 814 
				cellBusinessManager.setCellValue("-");
24131 govind 815 
			}
24133 govind 816 
			Cell cellAssistantManager = row.createCell(cellnum++);
817 
			if (salesHeadNameAndFofoIdMap.get(fofoId) != null) {
818 
				if (salesHeadNameAndFofoIdMap.get(fofoId).equals("Kamal")) {
819 
					cellAssistantManager.setCellValue(salesHeadNameAndFofoIdMap.get(fofoId));
820 
				} else {
821 
					cellAssistantManager.setCellValue("");
822 
				}
823 
			} else {
824 
				cellAssistantManager.setCellValue("");
825 
			}
24188 govind 826 
			Cell cellTargetName = row.createCell(cellnum++);
827 
			if (targetName==null||targetName=="") {
828 
				cellTargetName.setCellValue("");
829 
 
24119 govind 830 
			} else {
24188 govind 831 
				cellTargetName.setCellValue(targetName);
24119 govind 832 
			}
833 
 
834 
			Cell cellMTDTargetValue = row.createCell(cellnum++);
835 
			if (targetValues.get(fofoId) != null) {
836 
				cellMTDTargetValue.setCellValue(targetValues.get(fofoId));
837 
			} else {
24133 govind 838 
				cellMTDTargetValue.setCellValue("-");
24119 govind 839 
			}
24188 govind 840 
			Cell cellMTDAchievement = row.createCell(cellnum++);
841 
			if (saleValues.get(fofoId) != null) {
842 
				cellMTDAchievement.setCellValue(saleValues.get(fofoId));
843 
			} else {
844 
				cellMTDAchievement.setCellValue(0);
845 
			}
846 
			Cell cellMTDAchievementPercentage = row.createCell(cellnum++);
847 
			if (monthlyTargetAchievementPercentage.get(fofoId) != null) {
848 
				cellMTDAchievementPercentage.setCellValue(monthlyTargetAchievementPercentage.get(fofoId) + "%");
849 
			} else {
850 
				cellMTDAchievementPercentage.setCellValue(0 + "%");
851 
			}
24119 govind 852 
 
853 
			Cell cellDailyTargetValue = row.createCell(cellnum++);
854 
			if (dailyAverageSale.get(fofoId) != null) {
24131 govind 855 
				String formatting = FormattingUtils.formatDecimalTwoDigits(dailyAverageSale.get(fofoId));
24119 govind 856 
				cellDailyTargetValue.setCellValue(formatting);
857 
			} else {
858 
				cellDailyTargetValue.setCellValue(0);
859 
			}
860 
			Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);
861 
			if (todayAchievements.get(fofoId) != null) {
862 
				cellTodayAchieveMentSaleValue.setCellValue(todayAchievements.get(fofoId));
863 
			} else {
864 
				cellTodayAchieveMentSaleValue.setCellValue(0);
865 
			}
24188 govind 866 
 
24119 govind 867 
			Cell cellRemaining = row.createCell(cellnum++);
868 
			if (remainingMonthlyTargets.get(fofoId) != null) {
869 
				cellRemaining.setCellValue(remainingMonthlyTargets.get(fofoId));
870 
			} else {
24133 govind 871 
				cellRemaining.setCellValue(0);
24119 govind 872 
			}
873 
 
874 
			Cell cellEligible = row.createCell(cellnum++);
875 
			if (fofoIdsAndSlabNames.get(fofoId) != null) {
876 
				cellEligible.setCellValue(fofoIdsAndSlabNames.get(fofoId));
877 
			} else {
24133 govind 878 
				cellEligible.setCellValue("-");
24119 govind 879 
			}
880 
		}
881 
 
882 
		for (int index = 0; index < saleValues.size(); index++) {
883 
			sheet.autoSizeColumn(index);
884 
		}
885 
 
886 
		try {
887 
			workbook.write(outputStream);
888 
			workbook.close();
889 
		} catch (IOException ioException) {
890 
			LOGGER.error("Unable to generate excel file", ioException);
891 
		}
892 
	}
24184 govind 893 
	public static void writeDailySaleReportVsTargetForPartner(Map<Integer,String> targetIdAndTargetNameMap,Map<Integer, Float> targetIdAndtargetValuesMap, Map<Integer, Double> targetIdAndsaleValuesMap,
894 
			Map<Integer, CustomRetailer> targetIdAndCustomRetailerMap, Map<Integer, String> targetIdAndSlabNamesMap,
895 
			Map<Integer, Double> targetIdAndsaleValuesPercentageMap, Map<Integer, Float> targetIdAndtargetdailyAverageSaleMap,
896 
			Map<Integer, Double> targetIdAndRemainingTargetMap, Map<Integer, Double> targetIdAndtodayAchievementsMap,
897 
			 Map<Integer, String> targetIdAndSalesHeadMap , OutputStream outputStream) {
898 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 899 
 
24184 govind 900 
		// CreationHelper createHelper = workbook.getCreationHelper();
901 
 
902 
		SXSSFSheet sheet = workbook.createSheet("DailySaleReports");
903 
		sheet.trackAllColumnsForAutoSizing();
904 
 
905 
		Row rowHeader = sheet.createRow(0);
24188 govind 906 
		Cell cellStoreName = rowHeader.createCell(0);
907 
		cellStoreName.setCellValue("Store Name");
24184 govind 908 
		Cell cellBusiness = rowHeader.createCell(1);
909 
		cellBusiness.setCellValue("Business Manager");
910 
		Cell cellAssistant = rowHeader.createCell(2);
911 
		cellAssistant.setCellValue("Assistant Manager");
24188 govind 912 
		Cell cellSchemeName = rowHeader.createCell(3);
913 
		cellSchemeName.setCellValue("Scheme Name");
24184 govind 914 
		Cell cellTargetValue = rowHeader.createCell(4);
24188 govind 915 
		cellTargetValue.setCellValue("Scheme Target");
916 
		Cell cellMonthlySaleValue = rowHeader.createCell(5);
917 
		cellMonthlySaleValue.setCellValue("Total Achievement");
918 
		Cell cellMonthlySaleValuePercentage = rowHeader.createCell(6);
919 
		cellMonthlySaleValuePercentage.setCellValue("Today Achievement Percentage");
920 
		Cell cellDailyTargetValue = rowHeader.createCell(7);
921 
		cellDailyTargetValue.setCellValue("Daily Target");
922 
		Cell cellTodayAchievement = rowHeader.createCell(8);
923 
		cellTodayAchievement.setCellValue("Today Achievement");
24184 govind 924 
		Cell cellRemainingTarget = rowHeader.createCell(9);
24188 govind 925 
		cellRemainingTarget.setCellValue("Remaining Target");
24184 govind 926 
		Cell cellEligibility = rowHeader.createCell(10);
927 
		cellEligibility.setCellValue("Eligibility");
928 
		Font font = workbook.createFont();
929 
		CellStyle cellStyle = workbook.createCellStyle();
930 
		font.setBold(true);
931 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
932 
		// font.setFontHeight((short)16);
933 
		cellStyle.setFont(font);
934 
		for (int i = 0; i < 14; i++) {
935 
			if (rowHeader.getCell(i) != null) {
936 
				rowHeader.getCell(i).setCellStyle(cellStyle);
937 
			}
938 
		}
939 
		int rownum = 1;
940 
		for (Integer targetId : targetIdAndTargetNameMap.keySet()) {
941 
			Row row = sheet.createRow(rownum++);
942 
			int cellnum = 0;
24188 govind 943 
			Cell cellPartner = row.createCell(cellnum++);
944 
			if (targetIdAndCustomRetailerMap.get(targetId) != null) {
945 
				cellPartner.setCellValue(targetIdAndCustomRetailerMap.get(targetId).getBusinessName());
24184 govind 946 
			} else {
24188 govind 947 
				cellPartner.setCellValue("-");
24184 govind 948 
			}
24188 govind 949 
 
24184 govind 950 
			Cell cellBusinessManager = row.createCell(cellnum++);
24186 govind 951 
			if (targetIdAndSalesHeadMap.get(targetId) != null) {
952 
				if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {
24184 govind 953 
					cellBusinessManager.setCellValue("Mohinder");
954 
				} else {
24186 govind 955 
					cellBusinessManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));
24184 govind 956 
				}
957 
			} else {
958 
				cellBusinessManager.setCellValue("-");
959 
			}
960 
			Cell cellAssistantManager = row.createCell(cellnum++);
961 
			if (targetIdAndSalesHeadMap.get(targetId) != null) {
962 
				if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {
963 
					cellAssistantManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));
964 
				} else {
965 
					cellAssistantManager.setCellValue("");
966 
				}
967 
			} else {
968 
				cellAssistantManager.setCellValue("");
969 
			}
24188 govind 970 
			Cell cellTargetName = row.createCell(cellnum++);
971 
			if (targetIdAndTargetNameMap.get(targetId)==null||targetIdAndTargetNameMap.get(targetId)=="") {
972 
				cellTargetName.setCellValue("");
973 
 
24186 govind 974 
			} else {
24188 govind 975 
				cellTargetName.setCellValue(targetIdAndTargetNameMap.get(targetId));
24186 govind 976 
			}
24184 govind 977 
			Cell cellMTDTargetValue = row.createCell(cellnum++);
978 
			if (targetIdAndtargetValuesMap.get(targetId) != null) {
979 
				cellMTDTargetValue.setCellValue(targetIdAndtargetValuesMap.get(targetId));
980 
			} else {
981 
				cellMTDTargetValue.setCellValue("-");
982 
			}
983 
			Cell cellMTDAchievement = row.createCell(cellnum++);
984 
			if (targetIdAndsaleValuesMap.get(targetId) != null) {
985 
				cellMTDAchievement.setCellValue(targetIdAndsaleValuesMap.get(targetId));
986 
			} else {
987 
				cellMTDAchievement.setCellValue(0);
988 
			}
989 
			Cell cellMTDAchievementPercentage = row.createCell(cellnum++);
990 
			if (targetIdAndsaleValuesPercentageMap.get(targetId) != null) {
991 
				cellMTDAchievementPercentage.setCellValue(targetIdAndsaleValuesPercentageMap.get(targetId) + "%");
992 
			} else {
993 
				cellMTDAchievementPercentage.setCellValue(0 + "%");
994 
			}
995 
 
24188 govind 996 
			Cell cellDailyTargetValue1 = row.createCell(cellnum++);
997 
			if (targetIdAndtargetdailyAverageSaleMap.get(targetId) != null) {
998 
				String formatting = FormattingUtils.formatDecimalTwoDigits(targetIdAndtargetdailyAverageSaleMap.get(targetId));
999 
				cellDailyTargetValue1.setCellValue(formatting);
1000 
			} else {
1001 
				cellDailyTargetValue1.setCellValue(0);
1002 
			}
1003 
			Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);
1004 
			if (targetIdAndtodayAchievementsMap.get(targetId) != null) {
1005 
				cellTodayAchieveMentSaleValue.setCellValue(targetIdAndtodayAchievementsMap.get(targetId));
1006 
			} else {
1007 
				cellTodayAchieveMentSaleValue.setCellValue(0);
1008 
			}
1009 
 
24184 govind 1010 
			Cell cellRemaining = row.createCell(cellnum++);
1011 
			if (targetIdAndRemainingTargetMap.get(targetId) != null) {
1012 
				cellRemaining.setCellValue(targetIdAndRemainingTargetMap.get(targetId));
1013 
			} else {
1014 
				cellRemaining.setCellValue(0);
1015 
			}
1016 
 
1017 
			Cell cellEligible = row.createCell(cellnum++);
1018 
			if (targetIdAndSlabNamesMap.get(targetId) != null) {
1019 
				cellEligible.setCellValue(targetIdAndSlabNamesMap.get(targetId));
1020 
			} else {
1021 
				cellEligible.setCellValue("-");
1022 
			}
1023 
		}
1024 
 
1025 
		for (int index = 0; index < targetIdAndsaleValuesMap.size(); index++) {
1026 
			sheet.autoSizeColumn(index);
1027 
		}
1028 
 
1029 
		try {
1030 
			workbook.write(outputStream);
1031 
			workbook.close();
1032 
		} catch (IOException ioException) {
1033 
			LOGGER.error("Unable to generate excel file", ioException);
1034 
		}
1035 
	}
1036 
 
21786 ashik.ali 1037 
}