Subversion Repositories SmartDukaan

Rev

Rev 24189 | Rev 24222 | 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 
			}
442 
 
443 
			try {
444 
				workbook.write(outputStream);
445 
				workbook.close();
446 
			} catch (IOException ioException) {
447 
				LOGGER.error("Unable to generate excel file", ioException);
448 
			}
24119 govind 449 
		}
22521 ashik.ali 450 
	}
24119 govind 451 
 
452 
	public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream) {
23017 ashik.ali 453 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 454 
 
455 
		// CreationHelper createHelper = workbook.getCreationHelper();
456 
 
23017 ashik.ali 457 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
458 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 459 
 
460 
		Row rowHeader = sheet.createRow(0);
461 
		Cell cellSchemeIdHeader = rowHeader.createCell(0);
23017 ashik.ali 462 
		cellSchemeIdHeader.setCellValue("Scheme Id");
463 
		Cell cellNameHeader = rowHeader.createCell(1);
464 
		cellNameHeader.setCellValue("Name");
465 
		Cell cellDescriptionHeader = rowHeader.createCell(2);
466 
		cellDescriptionHeader.setCellValue("Description");
467 
		Cell cellSchemeTypeHeader = rowHeader.createCell(3);
468 
		cellSchemeTypeHeader.setCellValue("Scheme Type");
469 
		Cell cellAmountTypeHeader = rowHeader.createCell(4);
470 
		cellAmountTypeHeader.setCellValue("Amount Type");
471 
		Cell cellAmountHeader = rowHeader.createCell(5);
472 
		cellAmountHeader.setCellValue("Amount");
473 
		Cell cellStartDateTimeHeader = rowHeader.createCell(6);
474 
		cellStartDateTimeHeader.setCellValue("Start Date Time");
475 
		Cell cellEndDateTimeHeader = rowHeader.createCell(7);
476 
		cellEndDateTimeHeader.setCellValue("End Date Time");
477 
		Cell cellCreatedAtHeader = rowHeader.createCell(8);
478 
		cellCreatedAtHeader.setCellValue("Created At");
479 
		Cell cellActiveHeader = rowHeader.createCell(9);
480 
		cellActiveHeader.setCellValue("Active");
481 
		Cell cellExpireHeader = rowHeader.createCell(10);
482 
		cellExpireHeader.setCellValue("Expire");
483 
		Cell cellCreatedByHeader = rowHeader.createCell(11);
484 
		cellCreatedByHeader.setCellValue("Created By");
485 
		Cell cellItemIdsHeader = rowHeader.createCell(12);
486 
		cellItemIdsHeader.setCellValue("Item Ids");
487 
		Cell cellRetailerIdsHeader = rowHeader.createCell(13);
488 
		cellRetailerIdsHeader.setCellValue("Retailer Ids");
24119 govind 489 
		// Row rowQuantityRateValue = sheet.createRow(1);
490 
 
491 
		/*
492 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
493 
		 * rowQuantityRateValue.createCell(index);
494 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
495 
		 * rowQuantityRateValue.createCell(index + 1);
496 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
497 
		 * rowQuantityRateValue.createCell(index + 2);
498 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
499 
		 * CellRangeAddress(0, 0, index, index + 2)); }
500 
		 */
23017 ashik.ali 501 
		Font font = workbook.createFont();
502 
		CellStyle cellStyle = workbook.createCellStyle();
503 
		font.setBold(true);
504 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 505 
		// font.setFontHeight((short)16);
23017 ashik.ali 506 
		cellStyle.setFont(font);
24119 govind 507 
		for (int i = 0; i < 14; i++) {
508 
			if (rowHeader.getCell(i) != null) {
23017 ashik.ali 509 
				rowHeader.getCell(i).setCellStyle(cellStyle);
510 
			}
511 
		}
512 
 
24119 govind 513 
		for (int index = 0; index < schemeModels.size(); index++) {
23017 ashik.ali 514 
			SchemeModel schemeModel = schemeModels.get(index);
24119 govind 515 
			// ItemCompleteLedgerModel itemCompleteLedgerModel =
516 
			// itemCompleteLedgerModels.get(index);
517 
			Row rowValues = sheet.createRow(index + 1);
518 
			Cell cellSchemeId = rowValues.createCell(0);
23017 ashik.ali 519 
			cellSchemeId.setCellValue(schemeModel.getSchemeId());
520 
			Cell cellName = rowValues.createCell(1);
521 
			cellName.setCellValue(schemeModel.getName());
522 
			Cell cellDescription = rowValues.createCell(2);
523 
			cellDescription.setCellValue(schemeModel.getDescription());
524 
			Cell cellSchemeType = rowValues.createCell(3);
525 
			cellSchemeType.setCellValue(schemeModel.getSchemeType());
526 
			Cell cellAmountType = rowValues.createCell(4);
527 
			cellAmountType.setCellValue(schemeModel.getAmountType());
528 
			Cell cellAmount = rowValues.createCell(5);
529 
			cellAmount.setCellValue(schemeModel.getAmount());
530 
			Cell cellStartDateTime = rowValues.createCell(6);
531 
			cellStartDateTime.setCellValue(schemeModel.getStartDateTime());
532 
			Cell cellEndDateTime = rowValues.createCell(7);
533 
			cellEndDateTime.setCellValue(schemeModel.getEndDateTime());
534 
			Cell cellCreatedAt = rowValues.createCell(8);
535 
			cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());
536 
			Cell cellActive = rowValues.createCell(9);
24119 govind 537 
			if (schemeModel.getActiveTimestamp() != null) {
23017 ashik.ali 538 
				cellActive.setCellValue(schemeModel.getActiveTimestamp());
24119 govind 539 
			} else {
23017 ashik.ali 540 
				cellActive.setCellValue("False");
541 
			}
542 
			Cell cellExpire = rowValues.createCell(10);
24119 govind 543 
			if (schemeModel.getExpireTimestamp() != null) {
23017 ashik.ali 544 
				cellExpire.setCellValue(schemeModel.getExpireTimestamp());
24119 govind 545 
			} else {
23017 ashik.ali 546 
				cellExpire.setCellValue("False");
547 
			}
548 
			Cell cellCreatedBy = rowValues.createCell(11);
549 
			cellCreatedBy.setCellValue(schemeModel.getCreatedBy());
550 
			Cell cellItemIds = rowValues.createCell(12);
23338 ashik.ali 551 
			cellItemIds.setCellValue(schemeModel.getItemStringMap().toString());
23017 ashik.ali 552 
			Cell cellRetailerIds = rowValues.createCell(13);
553 
			cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());
23338 ashik.ali 554 
			int maxHeight = Math.max(schemeModel.getItemStringMap().size(), schemeModel.getRetailerIds().size());
24119 govind 555 
			if (maxHeight > 1) {
556 
				rowValues.setHeight((short) (maxHeight * 240));
23017 ashik.ali 557 
			}
24119 govind 558 
		}
559 
 
560 
		for (int index = 0; index < 14; index++) {
23017 ashik.ali 561 
			sheet.autoSizeColumn(index);
562 
		}
24119 govind 563 
 
564 
		try {
23017 ashik.ali 565 
			workbook.write(outputStream);
24119 govind 566 
			workbook.close();
567 
		} catch (IOException ioException) {
568 
			LOGGER.error("Unable to generate excel file", ioException);
569 
		}
23017 ashik.ali 570 
	}
24119 govind 571 
 
572 
	public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId, int itemId, OutputStream outputStream) {
23819 govind 573 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 574 
 
575 
		// CreationHelper createHelper = workbook.getCreationHelper();
576 
 
23819 govind 577 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
578 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 579 
 
580 
		Row rowHeader = sheet.createRow(0);
581 
		Cell cellItemHeader = rowHeader.createCell(0);
582 
		cellItemHeader.setCellValue("ITEMID");
23819 govind 583 
		Cell cellIMEIHeader = rowHeader.createCell(1);
584 
		cellIMEIHeader.setCellValue("IMEI");
585 
		Cell cellFOFOIDHeader = rowHeader.createCell(2);
586 
		cellFOFOIDHeader.setCellValue("RETAILERNAME");
24119 govind 587 
		// Row rowQuantityRateValue = sheet.createRow(1);
588 
 
589 
		/*
590 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
591 
		 * rowQuantityRateValue.createCell(index);
592 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
593 
		 * rowQuantityRateValue.createCell(index + 1);
594 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
595 
		 * rowQuantityRateValue.createCell(index + 2);
596 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
597 
		 * CellRangeAddress(0, 0, index, index + 2)); }
598 
		 */
23819 govind 599 
		Font font = workbook.createFont();
600 
		CellStyle cellStyle = workbook.createCellStyle();
601 
		font.setBold(true);
602 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 603 
		// font.setFontHeight((short)16);
23819 govind 604 
		cellStyle.setFont(font);
24119 govind 605 
		for (int i = 0; i < 14; i++) {
606 
			if (rowHeader.getCell(i) != null) {
23819 govind 607 
				rowHeader.getCell(i).setCellStyle(cellStyle);
608 
			}
609 
		}
24119 govind 610 
		LinkedHashMap<String, String> priceAmount = new LinkedHashMap<>(priceDropIMEIfofoId);
611 
		Set<String> keyset = priceAmount.keySet();
23819 govind 612 
		int rownum = 1;
613 
		for (String key : keyset) {
24119 govind 614 
			Row row = sheet.createRow(rownum++);
615 
			String objArr = priceAmount.get(key);
616 
			int cellnum = 0;
617 
			Cell cell = row.createCell(cellnum++);
618 
			cell.setCellValue(itemId);
619 
			Cell cell1 = row.createCell(cellnum++);
620 
			cell1.setCellValue(key);
621 
			Cell cell2 = row.createCell(cellnum++);
622 
			cell2.setCellValue(priceAmount.get(key));
623 
		}
624 
 
625 
		for (int index = 0; index < 14; index++) {
23819 govind 626 
			sheet.autoSizeColumn(index);
627 
		}
24119 govind 628 
 
629 
		try {
23819 govind 630 
			workbook.write(outputStream);
24119 govind 631 
			workbook.close();
632 
		} catch (IOException ioException) {
633 
			LOGGER.error("Unable to generate excel file", ioException);
634 
		}
23819 govind 635 
	}
24119 govind 636 
 
637 
	public static void writePriceDropForAllIMEI(Map<String, String> priceDropAmount, OutputStream outputStream) {
23819 govind 638 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 639 
 
640 
		// CreationHelper createHelper = workbook.getCreationHelper();
641 
 
23819 govind 642 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
643 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 644 
 
645 
		Row rowHeader = sheet.createRow(0);
646 
		Cell cellItemHeader = rowHeader.createCell(0);
647 
		cellItemHeader.setCellValue("Itemdescription");
23819 govind 648 
		Cell cellIMEIHeader = rowHeader.createCell(1);
649 
		cellIMEIHeader.setCellValue("IMEI");
24119 govind 650 
		// Row rowQuantityRateValue = sheet.createRow(1);
651 
 
652 
		/*
653 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
654 
		 * rowQuantityRateValue.createCell(index);
655 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
656 
		 * rowQuantityRateValue.createCell(index + 1);
657 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
658 
		 * rowQuantityRateValue.createCell(index + 2);
659 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
660 
		 * CellRangeAddress(0, 0, index, index + 2)); }
661 
		 */
23819 govind 662 
		Font font = workbook.createFont();
663 
		CellStyle cellStyle = workbook.createCellStyle();
664 
		font.setBold(true);
665 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 666 
		// font.setFontHeight((short)16);
23819 govind 667 
		cellStyle.setFont(font);
24119 govind 668 
		for (int i = 0; i < 14; i++) {
669 
			if (rowHeader.getCell(i) != null) {
23819 govind 670 
				rowHeader.getCell(i).setCellStyle(cellStyle);
671 
			}
672 
		}
24119 govind 673 
		Set<String> keyset = priceDropAmount.keySet();
23819 govind 674 
		int rownum = 1;
675 
		for (String key : keyset) {
24119 govind 676 
			Row row = sheet.createRow(rownum++);
677 
			String objArr = priceDropAmount.get(key);
678 
			int cellnum = 0;
679 
			Cell cell = row.createCell(cellnum++);
680 
			cell.setCellValue(priceDropAmount.get(key));
681 
			Cell cell1 = row.createCell(cellnum++);
682 
			cell1.setCellValue(key);
683 
		}
684 
 
685 
		for (int index = 0; index < 14; index++) {
23819 govind 686 
			sheet.autoSizeColumn(index);
687 
		}
24119 govind 688 
 
689 
		try {
23819 govind 690 
			workbook.write(outputStream);
24119 govind 691 
			workbook.close();
692 
		} catch (IOException ioException) {
693 
			LOGGER.error("Unable to generate excel file", ioException);
694 
		}
23819 govind 695 
	}
24119 govind 696 
 
697 
	public static List<PartnerTargetModel> parseFromExcel(InputStream inputStream) throws Exception {
698 
 
24107 govind 699 
		List<PartnerTargetModel> partnerTargetModels = new ArrayList<>();
700 
		XSSFWorkbook myWorkBook = null;
24119 govind 701 
		try {
702 
			// FileInputStream fileInputStream = new
703 
			// FileInputStream("/home/ashikali/tag_listing1.xlsx");
704 
			myWorkBook = new XSSFWorkbook(inputStream);
705 
 
24107 govind 706 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
24119 govind 707 
			// Return first sheet from the XLSX workbook
24107 govind 708 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
709 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
24119 govind 710 
 
711 
			for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
24107 govind 712 
				XSSFRow row = mySheet.getRow(rowNumber);
713 
				LOGGER.info("row {}", row);
714 
				PartnerTargetModel partnerTargetModel = new PartnerTargetModel();
24119 govind 715 
 
716 
				if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
24107 govind 717 
					partnerTargetModel.setFofoId((int) row.getCell(0).getNumericCellValue());
24119 govind 718 
				} else {
719 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
720 
							FOFO_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
24107 govind 721 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
722 
					throw profitMandiBusinessException;
723 
				}
24119 govind 724 
				if (row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
725 
					partnerTargetModel.setTargetValue((int) row.getCell(3).getNumericCellValue());
726 
				} else {
727 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
728 
							TARGET_VALUE, row.getCell(8), "TGLSTNG_VE_1010");
24107 govind 729 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
730 
					throw profitMandiBusinessException;
731 
				}
732 
 
733 
				partnerTargetModels.add(partnerTargetModel);
734 
			}
735 
			myWorkBook.close();
24119 govind 736 
		} catch (IOException ioException) {
24107 govind 737 
			ioException.printStackTrace();
24119 govind 738 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
739 
					"EXL_VE_1000");
24107 govind 740 
		} finally {
24119 govind 741 
			if (myWorkBook != null) {
24107 govind 742 
				try {
743 
					myWorkBook.close();
744 
				} catch (IOException e) {
745 
					// TODO Auto-generated catch block
746 
					e.printStackTrace();
747 
				}
748 
			}
749 
		}
750 
		return partnerTargetModels;
751 
	}
24119 govind 752 
 
24177 govind 753 
	public static void writeDailySaleReportVsTarget(String targetName,Map<Integer, Float> targetValues, Map<Integer, Double> saleValues,
24119 govind 754 
			Map<Integer, CustomRetailer> fofoIdsAndCustomRetailer, Map<Integer, String> fofoIdsAndSlabNames,
755 
			Map<Integer, Double> monthlyTargetAchievementPercentage, Map<Integer, Float> dailyAverageSale,
756 
			Map<Integer, Double> remainingMonthlyTargets, Map<Integer, Double> todayAchievements,
24171 govind 757 
			 List<Integer> fofoIds,
24131 govind 758 
			Map<Integer, String> salesHeadNameAndFofoIdMap, OutputStream outputStream) {
24119 govind 759 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
760 
 
761 
		// CreationHelper createHelper = workbook.getCreationHelper();
762 
 
763 
		SXSSFSheet sheet = workbook.createSheet("DailySaleReports");
764 
		sheet.trackAllColumnsForAutoSizing();
765 
 
766 
		Row rowHeader = sheet.createRow(0);
24188 govind 767 
		Cell cellStoreName = rowHeader.createCell(0);
768 
		cellStoreName.setCellValue("Store Name");
24177 govind 769 
		Cell cellBusiness = rowHeader.createCell(1);
24133 govind 770 
		cellBusiness.setCellValue("Business Manager");
24177 govind 771 
		Cell cellAssistant = rowHeader.createCell(2);
24133 govind 772 
		cellAssistant.setCellValue("Assistant Manager");
24188 govind 773 
		Cell cellSchemeName = rowHeader.createCell(3);
774 
		cellSchemeName.setCellValue("Scheme Name");
775 
		Cell cellTargetValue = rowHeader.createCell(4);
776 
		cellTargetValue.setCellValue("Scheme Target");
777 
		Cell cellTotalAchievement = rowHeader.createCell(5);
778 
		cellTotalAchievement.setCellValue("Total Achievement");
779 
		Cell cellTotalAchievementPercentage = rowHeader.createCell(6);
780 
		cellTotalAchievementPercentage.setCellValue("Total Achievement Percentage");
781 
		Cell cellDailyTarget = rowHeader.createCell(7);
24119 govind 782 
		cellDailyTarget.setCellValue("Daily Target");
24188 govind 783 
		Cell cellTodayAchievement = rowHeader.createCell(8);
784 
		cellTodayAchievement.setCellValue("Today Achievement");
785 
		Cell cellRemainingTarget = rowHeader.createCell(9);
786 
		cellRemainingTarget.setCellValue("Remaining Target");
787 
		Cell cellEligibility = rowHeader.createCell(10);
24119 govind 788 
		cellEligibility.setCellValue("Eligibility");
789 
		Font font = workbook.createFont();
790 
		CellStyle cellStyle = workbook.createCellStyle();
791 
		font.setBold(true);
792 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
793 
		// font.setFontHeight((short)16);
794 
		cellStyle.setFont(font);
795 
		for (int i = 0; i < 14; i++) {
796 
			if (rowHeader.getCell(i) != null) {
797 
				rowHeader.getCell(i).setCellStyle(cellStyle);
798 
			}
799 
		}
800 
		int rownum = 1;
24131 govind 801 
		for (Integer fofoId : fofoIds) {
24119 govind 802 
			Row row = sheet.createRow(rownum++);
803 
			int cellnum = 0;
24188 govind 804 
			Cell cellPartner = row.createCell(cellnum++);
805 
			if (fofoIdsAndCustomRetailer.get(fofoId) != null) {
806 
				cellPartner.setCellValue(fofoIdsAndCustomRetailer.get(fofoId).getBusinessName());
24178 govind 807 
			} else {
24188 govind 808 
				cellPartner.setCellValue("-");
24177 govind 809 
			}
24133 govind 810 
			Cell cellBusinessManager = row.createCell(cellnum++);
24131 govind 811 
			if (salesHeadNameAndFofoIdMap.get(fofoId) != null) {
24133 govind 812 
				if (salesHeadNameAndFofoIdMap.get(fofoId).equals("Kamal")) {
813 
					cellBusinessManager.setCellValue("Mohinder");
814 
				} else {
815 
					cellBusinessManager.setCellValue(salesHeadNameAndFofoIdMap.get(fofoId));
816 
				}
24131 govind 817 
			} else {
24133 govind 818 
				cellBusinessManager.setCellValue("-");
24131 govind 819 
			}
24133 govind 820 
			Cell cellAssistantManager = row.createCell(cellnum++);
821 
			if (salesHeadNameAndFofoIdMap.get(fofoId) != null) {
822 
				if (salesHeadNameAndFofoIdMap.get(fofoId).equals("Kamal")) {
823 
					cellAssistantManager.setCellValue(salesHeadNameAndFofoIdMap.get(fofoId));
824 
				} else {
825 
					cellAssistantManager.setCellValue("");
826 
				}
827 
			} else {
828 
				cellAssistantManager.setCellValue("");
829 
			}
24188 govind 830 
			Cell cellTargetName = row.createCell(cellnum++);
831 
			if (targetName==null||targetName=="") {
832 
				cellTargetName.setCellValue("");
833 
 
24119 govind 834 
			} else {
24188 govind 835 
				cellTargetName.setCellValue(targetName);
24119 govind 836 
			}
837 
 
838 
			Cell cellMTDTargetValue = row.createCell(cellnum++);
839 
			if (targetValues.get(fofoId) != null) {
840 
				cellMTDTargetValue.setCellValue(targetValues.get(fofoId));
841 
			} else {
24133 govind 842 
				cellMTDTargetValue.setCellValue("-");
24119 govind 843 
			}
24188 govind 844 
			Cell cellMTDAchievement = row.createCell(cellnum++);
845 
			if (saleValues.get(fofoId) != null) {
846 
				cellMTDAchievement.setCellValue(saleValues.get(fofoId));
847 
			} else {
848 
				cellMTDAchievement.setCellValue(0);
849 
			}
850 
			Cell cellMTDAchievementPercentage = row.createCell(cellnum++);
851 
			if (monthlyTargetAchievementPercentage.get(fofoId) != null) {
852 
				cellMTDAchievementPercentage.setCellValue(monthlyTargetAchievementPercentage.get(fofoId) + "%");
853 
			} else {
854 
				cellMTDAchievementPercentage.setCellValue(0 + "%");
855 
			}
24119 govind 856 
 
857 
			Cell cellDailyTargetValue = row.createCell(cellnum++);
858 
			if (dailyAverageSale.get(fofoId) != null) {
24131 govind 859 
				String formatting = FormattingUtils.formatDecimalTwoDigits(dailyAverageSale.get(fofoId));
24119 govind 860 
				cellDailyTargetValue.setCellValue(formatting);
861 
			} else {
862 
				cellDailyTargetValue.setCellValue(0);
863 
			}
864 
			Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);
865 
			if (todayAchievements.get(fofoId) != null) {
866 
				cellTodayAchieveMentSaleValue.setCellValue(todayAchievements.get(fofoId));
867 
			} else {
868 
				cellTodayAchieveMentSaleValue.setCellValue(0);
869 
			}
24188 govind 870 
 
24119 govind 871 
			Cell cellRemaining = row.createCell(cellnum++);
872 
			if (remainingMonthlyTargets.get(fofoId) != null) {
873 
				cellRemaining.setCellValue(remainingMonthlyTargets.get(fofoId));
874 
			} else {
24133 govind 875 
				cellRemaining.setCellValue(0);
24119 govind 876 
			}
877 
 
878 
			Cell cellEligible = row.createCell(cellnum++);
879 
			if (fofoIdsAndSlabNames.get(fofoId) != null) {
880 
				cellEligible.setCellValue(fofoIdsAndSlabNames.get(fofoId));
881 
			} else {
24133 govind 882 
				cellEligible.setCellValue("-");
24119 govind 883 
			}
884 
		}
885 
 
886 
		for (int index = 0; index < saleValues.size(); index++) {
887 
			sheet.autoSizeColumn(index);
888 
		}
889 
 
890 
		try {
891 
			workbook.write(outputStream);
892 
			workbook.close();
893 
		} catch (IOException ioException) {
894 
			LOGGER.error("Unable to generate excel file", ioException);
895 
		}
896 
	}
24184 govind 897 
	public static void writeDailySaleReportVsTargetForPartner(Map<Integer,String> targetIdAndTargetNameMap,Map<Integer, Float> targetIdAndtargetValuesMap, Map<Integer, Double> targetIdAndsaleValuesMap,
898 
			Map<Integer, CustomRetailer> targetIdAndCustomRetailerMap, Map<Integer, String> targetIdAndSlabNamesMap,
899 
			Map<Integer, Double> targetIdAndsaleValuesPercentageMap, Map<Integer, Float> targetIdAndtargetdailyAverageSaleMap,
900 
			Map<Integer, Double> targetIdAndRemainingTargetMap, Map<Integer, Double> targetIdAndtodayAchievementsMap,
901 
			 Map<Integer, String> targetIdAndSalesHeadMap , OutputStream outputStream) {
902 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 903 
 
24184 govind 904 
		// CreationHelper createHelper = workbook.getCreationHelper();
905 
 
906 
		SXSSFSheet sheet = workbook.createSheet("DailySaleReports");
907 
		sheet.trackAllColumnsForAutoSizing();
908 
 
909 
		Row rowHeader = sheet.createRow(0);
24188 govind 910 
		Cell cellStoreName = rowHeader.createCell(0);
911 
		cellStoreName.setCellValue("Store Name");
24184 govind 912 
		Cell cellBusiness = rowHeader.createCell(1);
913 
		cellBusiness.setCellValue("Business Manager");
914 
		Cell cellAssistant = rowHeader.createCell(2);
915 
		cellAssistant.setCellValue("Assistant Manager");
24188 govind 916 
		Cell cellSchemeName = rowHeader.createCell(3);
917 
		cellSchemeName.setCellValue("Scheme Name");
24184 govind 918 
		Cell cellTargetValue = rowHeader.createCell(4);
24188 govind 919 
		cellTargetValue.setCellValue("Scheme Target");
920 
		Cell cellMonthlySaleValue = rowHeader.createCell(5);
921 
		cellMonthlySaleValue.setCellValue("Total Achievement");
922 
		Cell cellMonthlySaleValuePercentage = rowHeader.createCell(6);
923 
		cellMonthlySaleValuePercentage.setCellValue("Today Achievement Percentage");
924 
		Cell cellDailyTargetValue = rowHeader.createCell(7);
925 
		cellDailyTargetValue.setCellValue("Daily Target");
926 
		Cell cellTodayAchievement = rowHeader.createCell(8);
927 
		cellTodayAchievement.setCellValue("Today Achievement");
24184 govind 928 
		Cell cellRemainingTarget = rowHeader.createCell(9);
24188 govind 929 
		cellRemainingTarget.setCellValue("Remaining Target");
24184 govind 930 
		Cell cellEligibility = rowHeader.createCell(10);
931 
		cellEligibility.setCellValue("Eligibility");
932 
		Font font = workbook.createFont();
933 
		CellStyle cellStyle = workbook.createCellStyle();
934 
		font.setBold(true);
935 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
936 
		// font.setFontHeight((short)16);
937 
		cellStyle.setFont(font);
938 
		for (int i = 0; i < 14; i++) {
939 
			if (rowHeader.getCell(i) != null) {
940 
				rowHeader.getCell(i).setCellStyle(cellStyle);
941 
			}
942 
		}
943 
		int rownum = 1;
944 
		for (Integer targetId : targetIdAndTargetNameMap.keySet()) {
945 
			Row row = sheet.createRow(rownum++);
946 
			int cellnum = 0;
24188 govind 947 
			Cell cellPartner = row.createCell(cellnum++);
948 
			if (targetIdAndCustomRetailerMap.get(targetId) != null) {
949 
				cellPartner.setCellValue(targetIdAndCustomRetailerMap.get(targetId).getBusinessName());
24184 govind 950 
			} else {
24188 govind 951 
				cellPartner.setCellValue("-");
24184 govind 952 
			}
24188 govind 953 
 
24184 govind 954 
			Cell cellBusinessManager = row.createCell(cellnum++);
24186 govind 955 
			if (targetIdAndSalesHeadMap.get(targetId) != null) {
956 
				if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {
24184 govind 957 
					cellBusinessManager.setCellValue("Mohinder");
958 
				} else {
24186 govind 959 
					cellBusinessManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));
24184 govind 960 
				}
961 
			} else {
962 
				cellBusinessManager.setCellValue("-");
963 
			}
964 
			Cell cellAssistantManager = row.createCell(cellnum++);
965 
			if (targetIdAndSalesHeadMap.get(targetId) != null) {
966 
				if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {
967 
					cellAssistantManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));
968 
				} else {
969 
					cellAssistantManager.setCellValue("");
970 
				}
971 
			} else {
972 
				cellAssistantManager.setCellValue("");
973 
			}
24188 govind 974 
			Cell cellTargetName = row.createCell(cellnum++);
975 
			if (targetIdAndTargetNameMap.get(targetId)==null||targetIdAndTargetNameMap.get(targetId)=="") {
976 
				cellTargetName.setCellValue("");
977 
 
24186 govind 978 
			} else {
24188 govind 979 
				cellTargetName.setCellValue(targetIdAndTargetNameMap.get(targetId));
24186 govind 980 
			}
24184 govind 981 
			Cell cellMTDTargetValue = row.createCell(cellnum++);
982 
			if (targetIdAndtargetValuesMap.get(targetId) != null) {
983 
				cellMTDTargetValue.setCellValue(targetIdAndtargetValuesMap.get(targetId));
984 
			} else {
985 
				cellMTDTargetValue.setCellValue("-");
986 
			}
987 
			Cell cellMTDAchievement = row.createCell(cellnum++);
988 
			if (targetIdAndsaleValuesMap.get(targetId) != null) {
989 
				cellMTDAchievement.setCellValue(targetIdAndsaleValuesMap.get(targetId));
990 
			} else {
991 
				cellMTDAchievement.setCellValue(0);
992 
			}
993 
			Cell cellMTDAchievementPercentage = row.createCell(cellnum++);
994 
			if (targetIdAndsaleValuesPercentageMap.get(targetId) != null) {
995 
				cellMTDAchievementPercentage.setCellValue(targetIdAndsaleValuesPercentageMap.get(targetId) + "%");
996 
			} else {
997 
				cellMTDAchievementPercentage.setCellValue(0 + "%");
998 
			}
999 
 
24188 govind 1000 
			Cell cellDailyTargetValue1 = row.createCell(cellnum++);
1001 
			if (targetIdAndtargetdailyAverageSaleMap.get(targetId) != null) {
1002 
				String formatting = FormattingUtils.formatDecimalTwoDigits(targetIdAndtargetdailyAverageSaleMap.get(targetId));
1003 
				cellDailyTargetValue1.setCellValue(formatting);
1004 
			} else {
1005 
				cellDailyTargetValue1.setCellValue(0);
1006 
			}
1007 
			Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);
1008 
			if (targetIdAndtodayAchievementsMap.get(targetId) != null) {
1009 
				cellTodayAchieveMentSaleValue.setCellValue(targetIdAndtodayAchievementsMap.get(targetId));
1010 
			} else {
1011 
				cellTodayAchieveMentSaleValue.setCellValue(0);
1012 
			}
1013 
 
24184 govind 1014 
			Cell cellRemaining = row.createCell(cellnum++);
1015 
			if (targetIdAndRemainingTargetMap.get(targetId) != null) {
1016 
				cellRemaining.setCellValue(targetIdAndRemainingTargetMap.get(targetId));
1017 
			} else {
1018 
				cellRemaining.setCellValue(0);
1019 
			}
1020 
 
1021 
			Cell cellEligible = row.createCell(cellnum++);
1022 
			if (targetIdAndSlabNamesMap.get(targetId) != null) {
1023 
				cellEligible.setCellValue(targetIdAndSlabNamesMap.get(targetId));
1024 
			} else {
1025 
				cellEligible.setCellValue("-");
1026 
			}
1027 
		}
1028 
 
1029 
		for (int index = 0; index < targetIdAndsaleValuesMap.size(); index++) {
1030 
			sheet.autoSizeColumn(index);
1031 
		}
1032 
 
1033 
		try {
1034 
			workbook.write(outputStream);
1035 
			workbook.close();
1036 
		} catch (IOException ioException) {
1037 
			LOGGER.error("Unable to generate excel file", ioException);
1038 
		}
1039 
	}
1040 
 
21786 ashik.ali 1041 
}