Subversion Repositories SmartDukaan

Rev

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