Subversion Repositories SmartDukaan

Rev

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