Subversion Repositories SmartDukaan

Rev

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