Subversion Repositories SmartDukaan

Rev

Rev 34588 | 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.*;
33172 tejus.loha 24 
import java.util.stream.Collectors;
21786 ashik.ali 25 
 
26 
public class ExcelUtils {
27 
	private static final String TAG_ID = "Tag Id";
28 
	private static final String TAG_LABEL = "Tag Label";
29 
	private static final String ITEM_ID = "Item Id";
30 
	private static final String BRAND = "Brand";
31 
	private static final String MODEL_NAME = "Model Name";
32 
	private static final String MODEL_NUMBER = "Model Number";
33 
	private static final String COLOR = "Color";
34 
	private static final String SELLING_PRICE = "Selling Price";
22204 amit.gupta 35 
	private static final String MOP = "MOP";
21786 ashik.ali 36 
	private static final String SUPPORT_PRICE = "Support Price";
37 
	private static final String START_DATE = "Start Date";
38 
	private static final String TAG_LISTING = "Tag Listing";
24107 govind 39 
	private static final String FOFO_ID = "fofoId";
40 
	private static final String STORE_NAME = "storeName";
41 
	private static final String EMAIL = "email";
24119 govind 42 
	private static final String TARGET_VALUE = "targetValue";
43 
 
33172 tejus.loha 44 
	private static List<Character> ALPHABETS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".chars().mapToObj(c -> (char) c).collect(Collectors.toList());
45 
 
46 
 
27064 amit.gupta 47 
	private static final List<WalletReferenceType> BULK_WALLET_REFERENCES = Arrays.asList(
34673 amit.gupta 48 
			WalletReferenceType.SCHEME_OUT, WalletReferenceType.ADDITIONAL_SCHEME,
27064 amit.gupta 49 
			WalletReferenceType.BRAND_FEE, WalletReferenceType.FESTIVE_OFFER, WalletReferenceType.GOODWILL_GESTURE,
50 
			WalletReferenceType.EOL, WalletReferenceType.ACTIVATION_SCHEME, WalletReferenceType.BRAND_PAYOUT,
51 
			WalletReferenceType.SALES_MARKETING_SUPPORT, WalletReferenceType.INVESTMENT_PAYOUT,
31314 amit.gupta 52 
			WalletReferenceType.CATEGORY_SPL_PAYOUT, WalletReferenceType.REFERRAL_INCENTIVES, WalletReferenceType.OTHERS,
33862 tejus.loha 53 
			WalletReferenceType.SHOP_BOY_SUPPORT, WalletReferenceType.PREBOOKING_ORDER
27064 amit.gupta 54 
 
55 
	);
56 
 
23568 govind 57 
	private static final Logger LOGGER = LogManager.getLogger(ExcelUtils.class);
24119 govind 58 
 
59 
	public static void main(String[] args) throws Throwable {
60 
		// List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);
61 
		// writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);
21786 ashik.ali 62 
	}
24119 govind 63 
 
22924 ashik.ali 64 
	public static List<TagListingModel> parse(InputStream inputStream) throws Exception {
24119 govind 65 
 
21786 ashik.ali 66 
		List<TagListingModel> tagListings = new ArrayList<>();
67 
		XSSFWorkbook myWorkBook = null;
24119 govind 68 
		try {
69 
			// FileInputStream fileInputStream = new
70 
			// FileInputStream("/home/ashikali/tag_listing1.xlsx");
71 
			myWorkBook = new XSSFWorkbook(inputStream);
72 
 
21786 ashik.ali 73 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
24119 govind 74 
			// Return first sheet from the XLSX workbook
21786 ashik.ali 75 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
22247 amit.gupta 76 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
24119 govind 77 
 
78 
			for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
22247 amit.gupta 79 
				XSSFRow row = mySheet.getRow(rowNumber);
80 
				LOGGER.info("row {}", row);
81 
				TagListingModel tagListing = new TagListingModel();
24119 govind 82 
				if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 83 
					tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
24119 govind 84 
				} else {
85 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID,
86 
							row.getCell(0).toString(), "TGLSTNG_VE_1010");
22226 amit.gupta 87 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 88 
					throw profitMandiBusinessException;
89 
				}
24119 govind 90 
 
91 
				if (row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 92 
					tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
24119 govind 93 
				} else {
94 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
95 
							ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
22247 amit.gupta 96 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
97 
					throw profitMandiBusinessException;
98 
				}
22204 amit.gupta 99 
 
24119 govind 100 
				if (row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 101 
					tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
24119 govind 102 
				} else {
103 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
104 
							SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
22226 amit.gupta 105 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
22209 amit.gupta 106 
					throw profitMandiBusinessException;
21786 ashik.ali 107 
				}
24119 govind 108 
				if (row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 109 
					tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
24119 govind 110 
				} else {
111 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP,
112 
							row.getCell(8), "TGLSTNG_VE_1010");
22247 amit.gupta 113 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
114 
					throw profitMandiBusinessException;
115 
				}
24119 govind 116 
				if (row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC) {
22247 amit.gupta 117 
					tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
24119 govind 118 
				} else {
119 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
120 
							SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
22247 amit.gupta 121 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
122 
					throw profitMandiBusinessException;
123 
				}
24119 govind 124 
				if (row.getCell(10) != null && row.getCell(10).getCellTypeEnum() == CellType.NUMERIC) {
22563 amit.gupta 125 
					tagListing.setMaxDiscountPrice(Double.valueOf(row.getCell(10).toString()).floatValue());
24119 govind 126 
				} else {
127 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
128 
							SUPPORT_PRICE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
22563 amit.gupta 129 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
130 
					throw profitMandiBusinessException;
131 
				}
24119 govind 132 
				if (row.getCell(11) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(11))) {
22865 amit.gupta 133 
					Date date = row.getCell(11).getDateCellValue();
22247 amit.gupta 134 
					LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
135 
					tagListing.setStartDate(startDate);
24119 govind 136 
				} else {
137 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
138 
							START_DATE, row.getCell(11).toString(), "TGLSTNG_VE_1010");
22247 amit.gupta 139 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
24119 govind 140 
					throw profitMandiBusinessException;
22247 amit.gupta 141 
				}
142 
				tagListings.add(tagListing);
21786 ashik.ali 143 
			}
144 
			myWorkBook.close();
24119 govind 145 
		} catch (IOException ioException) {
22206 amit.gupta 146 
			ioException.printStackTrace();
24119 govind 147 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
148 
					"EXL_VE_1000");
22247 amit.gupta 149 
		} finally {
24119 govind 150 
			if (myWorkBook != null) {
21786 ashik.ali 151 
				try {
152 
					myWorkBook.close();
153 
				} catch (IOException e) {
154 
					// TODO Auto-generated catch block
155 
					e.printStackTrace();
156 
				}
157 
			}
158 
		}
159 
		return tagListings;
160 
	}
24119 govind 161 
 
162 
	public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels,
163 
			List<Integer> intervals, OutputStream outputStream) {
22470 ashik.ali 164 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 165 
 
166 
		// CreationHelper createHelper = workbook.getCreationHelper();
26976 amit.gupta 167 
		boolean showPartner = inventoryItemAgingModels.get(0).getFofoId() > 0;
26974 amit.gupta 168 
		int nonValueColumns = showPartner ? 9 : 6;
22470 ashik.ali 169 
		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
170 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
171 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
172 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
173 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
174 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
175 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
27064 amit.gupta 176 
 
26961 amit.gupta 177 
		int rowIndex = 0;
178 
		Row rowHeader = sheet.createRow(rowIndex++);
179 
		Row rowPriceQuantity = sheet.createRow(rowIndex++);
27064 amit.gupta 180 
		int i = 0;
181 
		if (showPartner) {
26961 amit.gupta 182 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));
183 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7));
26974 amit.gupta 184 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 8));
26983 amit.gupta 185 
		}
186 
		sheet.trackAllColumnsForAutoSizing();
27064 amit.gupta 187 
		if (showPartner) {
26961 amit.gupta 188 
			Cell cellFofoIdHeader = rowHeader.createCell(i++);
189 
			cellFofoIdHeader.setCellValue("Partner Id");
190 
			Cell cellStoreCodeHeader = rowHeader.createCell(i++);
191 
			cellStoreCodeHeader.setCellValue("Store Code");
192 
			Cell cellStoreNameHeader = rowHeader.createCell(i++);
193 
			cellStoreNameHeader.setCellValue("Store Name");
194 
		}
195 
		Cell cellItemIdHeader = rowHeader.createCell(i++);
22470 ashik.ali 196 
		cellItemIdHeader.setCellValue("Item Id");
26961 amit.gupta 197 
		Cell cellBrandHeader = rowHeader.createCell(i++);
22470 ashik.ali 198 
		cellBrandHeader.setCellValue("Brand");
26961 amit.gupta 199 
		Cell cellModelNameHeader = rowHeader.createCell(i++);
22470 ashik.ali 200 
		cellModelNameHeader.setCellValue("Model Name");
26961 amit.gupta 201 
		Cell cellModelNumberHeader = rowHeader.createCell(i++);
22470 ashik.ali 202 
		cellModelNumberHeader.setCellValue("Model Number");
26961 amit.gupta 203 
		Cell cellColorHeader = rowHeader.createCell(i++);
22470 ashik.ali 204 
		cellColorHeader.setCellValue("Color");
26961 amit.gupta 205 
		Cell cellTypeHeader = rowHeader.createCell(i++);
22470 ashik.ali 206 
		cellTypeHeader.setCellValue("Item Type");
26961 amit.gupta 207 
		for (int index = 0; index <= intervals.size(); index++) {
208 
			Cell cellHeader = rowHeader.createCell(i++);
24119 govind 209 
			if (index == 0) {
210 
				cellHeader.setCellValue("Less Than " + intervals.get(index) + " Days");
211 
			} else if (index < intervals.size()) {
22470 ashik.ali 212 
				cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");
24119 govind 213 
			} else {
214 
				cellHeader.setCellValue("More Than " + intervals.get(index - 1) + " Days");
22470 ashik.ali 215 
			}
27064 amit.gupta 216 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, i - 1, i));
26961 amit.gupta 217 
			rowHeader.createCell(i++);
27064 amit.gupta 218 
			Cell cellPrice = rowPriceQuantity.createCell(i - 2);
22470 ashik.ali 219 
			cellPrice.setCellValue("Price");
27064 amit.gupta 220 
			Cell cellQuantity = rowPriceQuantity.createCell(i - 1);
22470 ashik.ali 221 
			cellQuantity.setCellValue("Quantity");
222 
		}
26961 amit.gupta 223 
 
22470 ashik.ali 224 
		Font font = workbook.createFont();
26961 amit.gupta 225 
		font.setBold(true);
22470 ashik.ali 226 
		CellStyle cellStyle = workbook.createCellStyle();
227 
		cellStyle.setFont(font);
27064 amit.gupta 228 
		for (int j = 0; j < nonValueColumns + ((intervals.size() + 1)) * 2; j++) {
26961 amit.gupta 229 
			rowHeader.getCell(j).setCellStyle(cellStyle);
26975 amit.gupta 230 
			if (rowPriceQuantity.getCell(j) != null) {
231 
				rowPriceQuantity.getCell(j).setCellStyle(cellStyle);
22470 ashik.ali 232 
			}
233 
		}
26961 amit.gupta 234 
		for (InventoryItemAgingModel inventoryItemAgingModel : inventoryItemAgingModels) {
27064 amit.gupta 235 
			i = 0;
26961 amit.gupta 236 
			Row rowValues = sheet.createRow(rowIndex++);
27064 amit.gupta 237 
			if (showPartner) {
26983 amit.gupta 238 
				rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getFofoId());
239 
				rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getStoreCode());
240 
				rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getStoreName());
26961 amit.gupta 241 
			}
242 
			rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getItemId());
243 
			rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getBrand());
244 
			rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getModelName());
245 
			rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getModelNumber());
246 
			rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getColor());
247 
			rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getItemType().toString());
24119 govind 248 
			List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
249 
			// LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
26961 amit.gupta 250 
			for (InventoryItemAgingValue inventoryItemAgingValue : inventoryItemAgingValues) {
24119 govind 251 
				if (inventoryItemAgingValue != null) {
26961 amit.gupta 252 
					rowValues.createCell(i++).setCellValue(inventoryItemAgingValue.getPrice());
253 
					rowValues.createCell(i++).setCellValue(inventoryItemAgingValue.getQuantity());
24119 govind 254 
				} else {
26961 amit.gupta 255 
					rowValues.createCell(i++).setCellValue("-");
256 
					rowValues.createCell(i++).setCellValue("-");
24119 govind 257 
				}
258 
			}
259 
		}
260 
 
27064 amit.gupta 261 
		for (int index = 0; index < nonValueColumns + ((intervals.size() + 1) * 2); index++) {
22470 ashik.ali 262 
			sheet.autoSizeColumn(index);
263 
		}
24119 govind 264 
 
265 
		try {
22486 ashik.ali 266 
			workbook.write(outputStream);
24119 govind 267 
			workbook.close();
268 
		} catch (IOException ioException) {
269 
			LOGGER.error("Unable to generate excel file", ioException);
270 
		}
22470 ashik.ali 271 
	}
24119 govind 272 
 
25380 amit.gupta 273 
	public static void writeItemCompleteLedgerModels(
274 
			Map<String, List<ItemCompleteLedgerModel>> itemCompleteLedgerModelsMap, OutputStream outputStream) {
22521 ashik.ali 275 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 276 
 
277 
		// CreationHelper createHelper = workbook.getCreationHelper();
278 
 
25380 amit.gupta 279 
		for (Map.Entry<String, List<ItemCompleteLedgerModel>> itemCompleteLedgerModelsEntry : itemCompleteLedgerModelsMap
280 
				.entrySet()) {
24215 amit.gupta 281 
			SXSSFSheet sheet = workbook.createSheet(itemCompleteLedgerModelsEntry.getKey());
282 
			List<ItemCompleteLedgerModel> itemCompleteLedgerModels = itemCompleteLedgerModelsEntry.getValue();
283 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
284 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
285 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
286 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
287 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
288 
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
289 
			sheet.trackAllColumnsForAutoSizing();
25380 amit.gupta 290 
 
24215 amit.gupta 291 
			Row rowHeader = sheet.createRow(0);
292 
			Cell cellItemIdHeader = rowHeader.createCell(0);
293 
			cellItemIdHeader.setCellValue("Item Id");
294 
			Cell cellBrandHeader = rowHeader.createCell(1);
295 
			cellBrandHeader.setCellValue("Brand");
296 
			Cell cellModelNameHeader = rowHeader.createCell(2);
297 
			cellModelNameHeader.setCellValue("Model Name");
298 
			Cell cellModelNumberHeader = rowHeader.createCell(3);
299 
			cellModelNumberHeader.setCellValue("Model Number");
300 
			Cell cellColorHeader = rowHeader.createCell(4);
301 
			cellColorHeader.setCellValue("Color");
302 
			Cell cellTypeHeader = rowHeader.createCell(5);
303 
			cellTypeHeader.setCellValue("Item Type");
304 
			Cell cellOpeningBalanceHeader = rowHeader.createCell(6);
305 
			cellOpeningBalanceHeader.setCellValue("Opening Balance");
306 
			Cell cellInwardsHeader = rowHeader.createCell(9);
307 
			cellInwardsHeader.setCellValue("Inwards");
308 
			Cell cellOutwardsHeader = rowHeader.createCell(12);
309 
			cellOutwardsHeader.setCellValue("Outwards");
310 
			Cell cellClosingBalanceHeader = rowHeader.createCell(15);
311 
			cellClosingBalanceHeader.setCellValue("Closing Balance");
312 
			Row rowQuantityRateValue = sheet.createRow(1);
25380 amit.gupta 313 
 
24215 amit.gupta 314 
			for (int index = 6; index < 18; index = index + 3) {
315 
				Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
316 
				cellQuantityHeader.setCellValue("Quantity");
317 
				Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
318 
				cellRateHeader.setCellValue("Rate");
319 
				Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
320 
				cellValueHeader.setCellValue("Value");
321 
				sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
22521 ashik.ali 322 
			}
24215 amit.gupta 323 
			Font font = workbook.createFont();
324 
			CellStyle cellStyle = workbook.createCellStyle();
325 
			font.setBold(true);
326 
			cellStyle.setAlignment(HorizontalAlignment.CENTER);
327 
			// font.setFontHeight((short)16);
328 
			cellStyle.setFont(font);
329 
			for (int i = 0; i < 18; i++) {
330 
				if (rowHeader.getCell(i) != null) {
331 
					rowHeader.getCell(i).setCellStyle(cellStyle);
332 
				}
24119 govind 333 
			}
24215 amit.gupta 334 
			int openingQuantityTotal = 0;
335 
			float openingValueTotal = 0;
336 
			int inwardsQuantityTotal = 0;
337 
			float inwardsValueTotal = 0;
338 
			int outwardsQuantityTotal = 0;
339 
			float outwardsValueTotal = 0;
340 
			int closingQuantityTotal = 0;
341 
			float closingValueTotal = 0;
342 
			for (int index = 0; index < itemCompleteLedgerModels.size(); index++) {
343 
				ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
344 
				Row rowValues = sheet.createRow(index + 2);
345 
				Cell cellItemId = rowValues.createCell(0);
346 
				cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
347 
				Cell cellBrand = rowValues.createCell(1);
348 
				cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
349 
				Cell cellModelName = rowValues.createCell(2);
350 
				cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
351 
				Cell cellModelNumber = rowValues.createCell(3);
352 
				cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
353 
				Cell cellColor = rowValues.createCell(4);
354 
				cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
355 
				Cell cellType = rowValues.createCell(5);
356 
				cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
357 
				Cell cellOpeningQuantity = rowValues.createCell(6);
358 
				Cell cellOpeningRate = rowValues.createCell(7);
359 
				Cell cellOpeningValue = rowValues.createCell(8);
360 
				if (itemCompleteLedgerModel.getOpeningLedger() == null) {
361 
					cellOpeningQuantity.setCellValue("-");
362 
					cellOpeningRate.setCellValue("-");
363 
					cellOpeningValue.setCellValue("-");
364 
				} else {
365 
					cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
366 
					cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
367 
					cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
25380 amit.gupta 368 
					openingQuantityTotal = openingQuantityTotal
369 
							+ itemCompleteLedgerModel.getOpeningLedger().getQuantity();
24215 amit.gupta 370 
					openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
371 
				}
372 
				Cell cellInwardsQuantity = rowValues.createCell(9);
373 
				Cell cellInwardsRate = rowValues.createCell(10);
374 
				Cell cellInwardsValue = rowValues.createCell(11);
375 
				if (itemCompleteLedgerModel.getInwardsLedger() == null) {
376 
					cellInwardsQuantity.setCellValue("-");
377 
					cellInwardsRate.setCellValue("-");
378 
					cellInwardsValue.setCellValue("-");
379 
				} else {
380 
					cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
381 
					cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
382 
					cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
25380 amit.gupta 383 
					inwardsQuantityTotal = inwardsQuantityTotal
384 
							+ itemCompleteLedgerModel.getInwardsLedger().getQuantity();
24215 amit.gupta 385 
					inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
386 
				}
387 
				Cell cellOutwardsQuantity = rowValues.createCell(12);
388 
				Cell cellOutwardsRate = rowValues.createCell(13);
389 
				Cell cellOutwardsValue = rowValues.createCell(14);
390 
				if (itemCompleteLedgerModel.getOutwardsLedger() == null) {
391 
					cellOutwardsQuantity.setCellValue("-");
392 
					cellOutwardsRate.setCellValue("-");
393 
					cellOutwardsValue.setCellValue("-");
394 
				} else {
395 
					cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
396 
					cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
397 
					cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
398 
					outwardsQuantityTotal = outwardsQuantityTotal
399 
							+ itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
400 
					outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
401 
				}
402 
				Cell cellClosingQuantity = rowValues.createCell(15);
403 
				Cell cellClosingRate = rowValues.createCell(16);
404 
				Cell cellClosingValue = rowValues.createCell(17);
405 
				if (itemCompleteLedgerModel.getClosingLedger() == null) {
406 
					cellClosingQuantity.setCellValue("-");
407 
					cellClosingRate.setCellValue("-");
408 
					cellClosingValue.setCellValue("-");
409 
				} else {
410 
					cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
411 
					cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
412 
					cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
25380 amit.gupta 413 
					closingQuantityTotal = closingQuantityTotal
414 
							+ itemCompleteLedgerModel.getClosingLedger().getQuantity();
24215 amit.gupta 415 
					closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
416 
				}
24119 govind 417 
			}
25380 amit.gupta 418 
 
24215 amit.gupta 419 
			Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);
25380 amit.gupta 420 
 
24215 amit.gupta 421 
			if (openingQuantityTotal > 0) {
422 
				Cell cellOpeningQuantityTotal = rowTotal.createCell(6);
423 
				cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);
424 
				Cell cellOpeningValueTotal = rowTotal.createCell(8);
425 
				cellOpeningValueTotal.setCellValue(openingValueTotal);
24119 govind 426 
			}
25380 amit.gupta 427 
 
24215 amit.gupta 428 
			if (inwardsQuantityTotal > 0) {
429 
				Cell cellInwardsQuantityTotal = rowTotal.createCell(9);
430 
				cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);
431 
				Cell cellInwardsValueTotal = rowTotal.createCell(11);
432 
				cellInwardsValueTotal.setCellValue(inwardsValueTotal);
24119 govind 433 
			}
25380 amit.gupta 434 
 
24215 amit.gupta 435 
			if (outwardsQuantityTotal > 0) {
436 
				Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);
437 
				cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);
438 
				Cell cellOutwardsValueTotal = rowTotal.createCell(14);
439 
				cellOutwardsValueTotal.setCellValue(outwardsValueTotal);
440 
			}
25380 amit.gupta 441 
 
24215 amit.gupta 442 
			if (closingQuantityTotal > 0) {
443 
				Cell cellClosingQuantityTotal = rowTotal.createCell(15);
444 
				cellClosingQuantityTotal.setCellValue(closingQuantityTotal);
445 
				Cell cellClosingValueTotal = rowTotal.createCell(17);
446 
				cellClosingValueTotal.setCellValue(closingValueTotal);
447 
			}
25380 amit.gupta 448 
 
24215 amit.gupta 449 
			for (int index = 0; index < 18; index++) {
450 
				sheet.autoSizeColumn(index);
451 
			}
24119 govind 452 
		}
24222 amit.gupta 453 
		try {
454 
			workbook.write(outputStream);
455 
			workbook.close();
456 
		} catch (IOException ioException) {
457 
			LOGGER.error("Unable to generate excel file", ioException);
458 
		}
22521 ashik.ali 459 
	}
24119 govind 460 
 
461 
	public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream) {
23017 ashik.ali 462 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 463 
 
464 
		// CreationHelper createHelper = workbook.getCreationHelper();
465 
 
23017 ashik.ali 466 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
467 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 468 
 
469 
		Row rowHeader = sheet.createRow(0);
470 
		Cell cellSchemeIdHeader = rowHeader.createCell(0);
23017 ashik.ali 471 
		cellSchemeIdHeader.setCellValue("Scheme Id");
472 
		Cell cellNameHeader = rowHeader.createCell(1);
473 
		cellNameHeader.setCellValue("Name");
474 
		Cell cellDescriptionHeader = rowHeader.createCell(2);
475 
		cellDescriptionHeader.setCellValue("Description");
476 
		Cell cellSchemeTypeHeader = rowHeader.createCell(3);
477 
		cellSchemeTypeHeader.setCellValue("Scheme Type");
478 
		Cell cellAmountTypeHeader = rowHeader.createCell(4);
479 
		cellAmountTypeHeader.setCellValue("Amount Type");
480 
		Cell cellAmountHeader = rowHeader.createCell(5);
481 
		cellAmountHeader.setCellValue("Amount");
482 
		Cell cellStartDateTimeHeader = rowHeader.createCell(6);
483 
		cellStartDateTimeHeader.setCellValue("Start Date Time");
484 
		Cell cellEndDateTimeHeader = rowHeader.createCell(7);
485 
		cellEndDateTimeHeader.setCellValue("End Date Time");
486 
		Cell cellCreatedAtHeader = rowHeader.createCell(8);
487 
		cellCreatedAtHeader.setCellValue("Created At");
488 
		Cell cellActiveHeader = rowHeader.createCell(9);
489 
		cellActiveHeader.setCellValue("Active");
490 
		Cell cellExpireHeader = rowHeader.createCell(10);
491 
		cellExpireHeader.setCellValue("Expire");
492 
		Cell cellCreatedByHeader = rowHeader.createCell(11);
493 
		cellCreatedByHeader.setCellValue("Created By");
494 
		Cell cellItemIdsHeader = rowHeader.createCell(12);
495 
		cellItemIdsHeader.setCellValue("Item Ids");
496 
		Cell cellRetailerIdsHeader = rowHeader.createCell(13);
497 
		cellRetailerIdsHeader.setCellValue("Retailer Ids");
24119 govind 498 
		// Row rowQuantityRateValue = sheet.createRow(1);
499 
 
500 
		/*
501 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
502 
		 * rowQuantityRateValue.createCell(index);
503 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
504 
		 * rowQuantityRateValue.createCell(index + 1);
505 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
506 
		 * rowQuantityRateValue.createCell(index + 2);
507 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
508 
		 * CellRangeAddress(0, 0, index, index + 2)); }
509 
		 */
23017 ashik.ali 510 
		Font font = workbook.createFont();
511 
		CellStyle cellStyle = workbook.createCellStyle();
512 
		font.setBold(true);
513 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 514 
		// font.setFontHeight((short)16);
23017 ashik.ali 515 
		cellStyle.setFont(font);
24119 govind 516 
		for (int i = 0; i < 14; i++) {
517 
			if (rowHeader.getCell(i) != null) {
23017 ashik.ali 518 
				rowHeader.getCell(i).setCellStyle(cellStyle);
519 
			}
520 
		}
521 
 
24119 govind 522 
		for (int index = 0; index < schemeModels.size(); index++) {
23017 ashik.ali 523 
			SchemeModel schemeModel = schemeModels.get(index);
24119 govind 524 
			// ItemCompleteLedgerModel itemCompleteLedgerModel =
525 
			// itemCompleteLedgerModels.get(index);
526 
			Row rowValues = sheet.createRow(index + 1);
527 
			Cell cellSchemeId = rowValues.createCell(0);
23017 ashik.ali 528 
			cellSchemeId.setCellValue(schemeModel.getSchemeId());
529 
			Cell cellName = rowValues.createCell(1);
530 
			cellName.setCellValue(schemeModel.getName());
531 
			Cell cellDescription = rowValues.createCell(2);
532 
			cellDescription.setCellValue(schemeModel.getDescription());
533 
			Cell cellSchemeType = rowValues.createCell(3);
534 
			cellSchemeType.setCellValue(schemeModel.getSchemeType());
535 
			Cell cellAmountType = rowValues.createCell(4);
536 
			cellAmountType.setCellValue(schemeModel.getAmountType());
537 
			Cell cellAmount = rowValues.createCell(5);
538 
			cellAmount.setCellValue(schemeModel.getAmount());
539 
			Cell cellStartDateTime = rowValues.createCell(6);
540 
			cellStartDateTime.setCellValue(schemeModel.getStartDateTime());
541 
			Cell cellEndDateTime = rowValues.createCell(7);
542 
			cellEndDateTime.setCellValue(schemeModel.getEndDateTime());
543 
			Cell cellCreatedAt = rowValues.createCell(8);
544 
			cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());
545 
			Cell cellActive = rowValues.createCell(9);
24119 govind 546 
			if (schemeModel.getActiveTimestamp() != null) {
23017 ashik.ali 547 
				cellActive.setCellValue(schemeModel.getActiveTimestamp());
24119 govind 548 
			} else {
23017 ashik.ali 549 
				cellActive.setCellValue("False");
550 
			}
551 
			Cell cellExpire = rowValues.createCell(10);
24119 govind 552 
			if (schemeModel.getExpireTimestamp() != null) {
23017 ashik.ali 553 
				cellExpire.setCellValue(schemeModel.getExpireTimestamp());
24119 govind 554 
			} else {
23017 ashik.ali 555 
				cellExpire.setCellValue("False");
556 
			}
557 
			Cell cellCreatedBy = rowValues.createCell(11);
558 
			cellCreatedBy.setCellValue(schemeModel.getCreatedBy());
559 
			Cell cellItemIds = rowValues.createCell(12);
30124 amit.gupta 560 
			cellItemIds.setCellValue(schemeModel.getCatalogStringMap().toString());
23017 ashik.ali 561 
			Cell cellRetailerIds = rowValues.createCell(13);
562 
			cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());
30124 amit.gupta 563 
			int maxHeight = Math.max(schemeModel.getCatalogStringMap().size(), schemeModel.getRetailerIds().size());
24119 govind 564 
			if (maxHeight > 1) {
565 
				rowValues.setHeight((short) (maxHeight * 240));
23017 ashik.ali 566 
			}
24119 govind 567 
		}
568 
 
569 
		for (int index = 0; index < 14; index++) {
23017 ashik.ali 570 
			sheet.autoSizeColumn(index);
571 
		}
24119 govind 572 
 
573 
		try {
23017 ashik.ali 574 
			workbook.write(outputStream);
24119 govind 575 
			workbook.close();
576 
		} catch (IOException ioException) {
577 
			LOGGER.error("Unable to generate excel file", ioException);
578 
		}
23017 ashik.ali 579 
	}
24119 govind 580 
 
581 
	public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId, int itemId, OutputStream outputStream) {
23819 govind 582 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 583 
 
584 
		// CreationHelper createHelper = workbook.getCreationHelper();
585 
 
23819 govind 586 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
587 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 588 
 
589 
		Row rowHeader = sheet.createRow(0);
590 
		Cell cellItemHeader = rowHeader.createCell(0);
591 
		cellItemHeader.setCellValue("ITEMID");
23819 govind 592 
		Cell cellIMEIHeader = rowHeader.createCell(1);
593 
		cellIMEIHeader.setCellValue("IMEI");
594 
		Cell cellFOFOIDHeader = rowHeader.createCell(2);
595 
		cellFOFOIDHeader.setCellValue("RETAILERNAME");
24119 govind 596 
		// Row rowQuantityRateValue = sheet.createRow(1);
597 
 
598 
		/*
599 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
600 
		 * rowQuantityRateValue.createCell(index);
601 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
602 
		 * rowQuantityRateValue.createCell(index + 1);
603 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
604 
		 * rowQuantityRateValue.createCell(index + 2);
605 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
606 
		 * CellRangeAddress(0, 0, index, index + 2)); }
607 
		 */
23819 govind 608 
		Font font = workbook.createFont();
609 
		CellStyle cellStyle = workbook.createCellStyle();
610 
		font.setBold(true);
611 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 612 
		// font.setFontHeight((short)16);
23819 govind 613 
		cellStyle.setFont(font);
24119 govind 614 
		for (int i = 0; i < 14; i++) {
615 
			if (rowHeader.getCell(i) != null) {
23819 govind 616 
				rowHeader.getCell(i).setCellStyle(cellStyle);
617 
			}
618 
		}
24119 govind 619 
		LinkedHashMap<String, String> priceAmount = new LinkedHashMap<>(priceDropIMEIfofoId);
620 
		Set<String> keyset = priceAmount.keySet();
23819 govind 621 
		int rownum = 1;
622 
		for (String key : keyset) {
24119 govind 623 
			Row row = sheet.createRow(rownum++);
624 
			String objArr = priceAmount.get(key);
625 
			int cellnum = 0;
626 
			Cell cell = row.createCell(cellnum++);
627 
			cell.setCellValue(itemId);
628 
			Cell cell1 = row.createCell(cellnum++);
629 
			cell1.setCellValue(key);
630 
			Cell cell2 = row.createCell(cellnum++);
631 
			cell2.setCellValue(priceAmount.get(key));
632 
		}
633 
 
634 
		for (int index = 0; index < 14; index++) {
23819 govind 635 
			sheet.autoSizeColumn(index);
636 
		}
24119 govind 637 
 
638 
		try {
23819 govind 639 
			workbook.write(outputStream);
24119 govind 640 
			workbook.close();
641 
		} catch (IOException ioException) {
642 
			LOGGER.error("Unable to generate excel file", ioException);
643 
		}
23819 govind 644 
	}
24119 govind 645 
 
646 
	public static void writePriceDropForAllIMEI(Map<String, String> priceDropAmount, OutputStream outputStream) {
23819 govind 647 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 648 
 
649 
		// CreationHelper createHelper = workbook.getCreationHelper();
650 
 
23819 govind 651 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
652 
		sheet.trackAllColumnsForAutoSizing();
24119 govind 653 
 
654 
		Row rowHeader = sheet.createRow(0);
655 
		Cell cellItemHeader = rowHeader.createCell(0);
656 
		cellItemHeader.setCellValue("Itemdescription");
23819 govind 657 
		Cell cellIMEIHeader = rowHeader.createCell(1);
658 
		cellIMEIHeader.setCellValue("IMEI");
24119 govind 659 
		// Row rowQuantityRateValue = sheet.createRow(1);
660 
 
661 
		/*
662 
		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
663 
		 * rowQuantityRateValue.createCell(index);
664 
		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
665 
		 * rowQuantityRateValue.createCell(index + 1);
666 
		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
667 
		 * rowQuantityRateValue.createCell(index + 2);
668 
		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
669 
		 * CellRangeAddress(0, 0, index, index + 2)); }
670 
		 */
23819 govind 671 
		Font font = workbook.createFont();
672 
		CellStyle cellStyle = workbook.createCellStyle();
673 
		font.setBold(true);
674 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
24119 govind 675 
		// font.setFontHeight((short)16);
23819 govind 676 
		cellStyle.setFont(font);
24119 govind 677 
		for (int i = 0; i < 14; i++) {
678 
			if (rowHeader.getCell(i) != null) {
23819 govind 679 
				rowHeader.getCell(i).setCellStyle(cellStyle);
680 
			}
681 
		}
24119 govind 682 
		Set<String> keyset = priceDropAmount.keySet();
23819 govind 683 
		int rownum = 1;
684 
		for (String key : keyset) {
24119 govind 685 
			Row row = sheet.createRow(rownum++);
686 
			String objArr = priceDropAmount.get(key);
687 
			int cellnum = 0;
688 
			Cell cell = row.createCell(cellnum++);
689 
			cell.setCellValue(priceDropAmount.get(key));
690 
			Cell cell1 = row.createCell(cellnum++);
691 
			cell1.setCellValue(key);
692 
		}
693 
 
694 
		for (int index = 0; index < 14; index++) {
23819 govind 695 
			sheet.autoSizeColumn(index);
696 
		}
24119 govind 697 
 
698 
		try {
23819 govind 699 
			workbook.write(outputStream);
24119 govind 700 
			workbook.close();
701 
		} catch (IOException ioException) {
702 
			LOGGER.error("Unable to generate excel file", ioException);
703 
		}
23819 govind 704 
	}
24119 govind 705 
 
25380 amit.gupta 706 
	public static void writeDailySaleReportVsTargetForPartner(Map<Integer, String> targetIdAndTargetNameMap,
707 
			Map<Integer, Float> targetIdAndtargetValuesMap, Map<Integer, Double> targetIdAndsaleValuesMap,
24184 govind 708 
			Map<Integer, CustomRetailer> targetIdAndCustomRetailerMap, Map<Integer, String> targetIdAndSlabNamesMap,
25380 amit.gupta 709 
			Map<Integer, Double> targetIdAndsaleValuesPercentageMap,
710 
			Map<Integer, Float> targetIdAndtargetdailyAverageSaleMap,
24184 govind 711 
			Map<Integer, Double> targetIdAndRemainingTargetMap, Map<Integer, Double> targetIdAndtodayAchievementsMap,
25380 amit.gupta 712 
			Map<Integer, String> targetIdAndSalesHeadMap, OutputStream outputStream) {
24184 govind 713 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
24119 govind 714 
 
24184 govind 715 
		// CreationHelper createHelper = workbook.getCreationHelper();
716 
 
717 
		SXSSFSheet sheet = workbook.createSheet("DailySaleReports");
718 
		sheet.trackAllColumnsForAutoSizing();
719 
 
720 
		Row rowHeader = sheet.createRow(0);
24188 govind 721 
		Cell cellStoreName = rowHeader.createCell(0);
722 
		cellStoreName.setCellValue("Store Name");
24184 govind 723 
		Cell cellBusiness = rowHeader.createCell(1);
724 
		cellBusiness.setCellValue("Business Manager");
725 
		Cell cellAssistant = rowHeader.createCell(2);
726 
		cellAssistant.setCellValue("Assistant Manager");
24188 govind 727 
		Cell cellSchemeName = rowHeader.createCell(3);
728 
		cellSchemeName.setCellValue("Scheme Name");
24184 govind 729 
		Cell cellTargetValue = rowHeader.createCell(4);
24188 govind 730 
		cellTargetValue.setCellValue("Scheme Target");
731 
		Cell cellMonthlySaleValue = rowHeader.createCell(5);
732 
		cellMonthlySaleValue.setCellValue("Total Achievement");
733 
		Cell cellMonthlySaleValuePercentage = rowHeader.createCell(6);
734 
		cellMonthlySaleValuePercentage.setCellValue("Today Achievement Percentage");
735 
		Cell cellDailyTargetValue = rowHeader.createCell(7);
736 
		cellDailyTargetValue.setCellValue("Daily Target");
737 
		Cell cellTodayAchievement = rowHeader.createCell(8);
738 
		cellTodayAchievement.setCellValue("Today Achievement");
24184 govind 739 
		Cell cellRemainingTarget = rowHeader.createCell(9);
24188 govind 740 
		cellRemainingTarget.setCellValue("Remaining Target");
24184 govind 741 
		Cell cellEligibility = rowHeader.createCell(10);
742 
		cellEligibility.setCellValue("Eligibility");
743 
		Font font = workbook.createFont();
744 
		CellStyle cellStyle = workbook.createCellStyle();
745 
		font.setBold(true);
746 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
747 
		// font.setFontHeight((short)16);
748 
		cellStyle.setFont(font);
749 
		for (int i = 0; i < 14; i++) {
750 
			if (rowHeader.getCell(i) != null) {
751 
				rowHeader.getCell(i).setCellStyle(cellStyle);
752 
			}
753 
		}
754 
		int rownum = 1;
755 
		for (Integer targetId : targetIdAndTargetNameMap.keySet()) {
756 
			Row row = sheet.createRow(rownum++);
757 
			int cellnum = 0;
24188 govind 758 
			Cell cellPartner = row.createCell(cellnum++);
759 
			if (targetIdAndCustomRetailerMap.get(targetId) != null) {
760 
				cellPartner.setCellValue(targetIdAndCustomRetailerMap.get(targetId).getBusinessName());
24184 govind 761 
			} else {
24188 govind 762 
				cellPartner.setCellValue("-");
24184 govind 763 
			}
25380 amit.gupta 764 
 
24184 govind 765 
			Cell cellBusinessManager = row.createCell(cellnum++);
24186 govind 766 
			if (targetIdAndSalesHeadMap.get(targetId) != null) {
767 
				if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {
24184 govind 768 
					cellBusinessManager.setCellValue("Mohinder");
769 
				} else {
24186 govind 770 
					cellBusinessManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));
24184 govind 771 
				}
772 
			} else {
773 
				cellBusinessManager.setCellValue("-");
774 
			}
775 
			Cell cellAssistantManager = row.createCell(cellnum++);
776 
			if (targetIdAndSalesHeadMap.get(targetId) != null) {
777 
				if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {
778 
					cellAssistantManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));
779 
				} else {
780 
					cellAssistantManager.setCellValue("");
781 
				}
782 
			} else {
783 
				cellAssistantManager.setCellValue("");
784 
			}
24188 govind 785 
			Cell cellTargetName = row.createCell(cellnum++);
25380 amit.gupta 786 
			if (targetIdAndTargetNameMap.get(targetId) == null || targetIdAndTargetNameMap.get(targetId) == "") {
24188 govind 787 
				cellTargetName.setCellValue("");
25380 amit.gupta 788 
 
24186 govind 789 
			} else {
24188 govind 790 
				cellTargetName.setCellValue(targetIdAndTargetNameMap.get(targetId));
24186 govind 791 
			}
24184 govind 792 
			Cell cellMTDTargetValue = row.createCell(cellnum++);
793 
			if (targetIdAndtargetValuesMap.get(targetId) != null) {
794 
				cellMTDTargetValue.setCellValue(targetIdAndtargetValuesMap.get(targetId));
795 
			} else {
796 
				cellMTDTargetValue.setCellValue("-");
797 
			}
798 
			Cell cellMTDAchievement = row.createCell(cellnum++);
799 
			if (targetIdAndsaleValuesMap.get(targetId) != null) {
800 
				cellMTDAchievement.setCellValue(targetIdAndsaleValuesMap.get(targetId));
801 
			} else {
802 
				cellMTDAchievement.setCellValue(0);
803 
			}
804 
			Cell cellMTDAchievementPercentage = row.createCell(cellnum++);
805 
			if (targetIdAndsaleValuesPercentageMap.get(targetId) != null) {
806 
				cellMTDAchievementPercentage.setCellValue(targetIdAndsaleValuesPercentageMap.get(targetId) + "%");
807 
			} else {
808 
				cellMTDAchievementPercentage.setCellValue(0 + "%");
809 
			}
810 
 
24188 govind 811 
			Cell cellDailyTargetValue1 = row.createCell(cellnum++);
812 
			if (targetIdAndtargetdailyAverageSaleMap.get(targetId) != null) {
25380 amit.gupta 813 
				String formatting = FormattingUtils
29930 amit.gupta 814 
						.formatDecimal(targetIdAndtargetdailyAverageSaleMap.get(targetId));
24188 govind 815 
				cellDailyTargetValue1.setCellValue(formatting);
816 
			} else {
817 
				cellDailyTargetValue1.setCellValue(0);
818 
			}
819 
			Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);
820 
			if (targetIdAndtodayAchievementsMap.get(targetId) != null) {
821 
				cellTodayAchieveMentSaleValue.setCellValue(targetIdAndtodayAchievementsMap.get(targetId));
822 
			} else {
823 
				cellTodayAchieveMentSaleValue.setCellValue(0);
824 
			}
25380 amit.gupta 825 
 
24184 govind 826 
			Cell cellRemaining = row.createCell(cellnum++);
827 
			if (targetIdAndRemainingTargetMap.get(targetId) != null) {
828 
				cellRemaining.setCellValue(targetIdAndRemainingTargetMap.get(targetId));
829 
			} else {
830 
				cellRemaining.setCellValue(0);
831 
			}
832 
 
833 
			Cell cellEligible = row.createCell(cellnum++);
834 
			if (targetIdAndSlabNamesMap.get(targetId) != null) {
835 
				cellEligible.setCellValue(targetIdAndSlabNamesMap.get(targetId));
836 
			} else {
837 
				cellEligible.setCellValue("-");
838 
			}
839 
		}
840 
 
841 
		for (int index = 0; index < targetIdAndsaleValuesMap.size(); index++) {
842 
			sheet.autoSizeColumn(index);
843 
		}
844 
 
845 
		try {
846 
			workbook.write(outputStream);
847 
			workbook.close();
848 
		} catch (IOException ioException) {
849 
			LOGGER.error("Unable to generate excel file", ioException);
850 
		}
851 
	}
852 
 
27064 amit.gupta 853 
	public static List<WalletHistoryModel> parseWalletBulkCredit(InputStream inputStream) throws Exception {
854 
		List<WalletHistoryModel> walletHistoryModels = new ArrayList<>();
855 
		try (XSSFWorkbook myWorkBook = new XSSFWorkbook(inputStream)) {
856 
 
857 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
858 
			// Return first sheet from the XLSX workbook
859 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
860 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
861 
 
862 
			for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
863 
				XSSFRow row = mySheet.getRow(rowNumber);
864 
				LOGGER.info("row {}", row);
865 
				WalletHistoryModel walletHistoryModel = new WalletHistoryModel();
866 
				if (row.getCell(1) != null && row.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {
867 
					walletHistoryModel.setFofoId((int) row.getCell(1).getNumericCellValue());
868 
				} else {
869 
					throw new ProfitMandiBusinessException("Invalid Fofo Id", "row number " + rowNumber,
870 
							"row number " + rowNumber);
871 
				}
872 
				if (row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.STRING) {
873 
					WalletReferenceType referenceType = WalletReferenceType
874 
							.valueOf(row.getCell(2).getStringCellValue());
875 
					if (!BULK_WALLET_REFERENCES.contains(referenceType)) {
876 
						throw new ProfitMandiBusinessException("Invalid Reference type", "row number " + rowNumber,
877 
								"row number " + rowNumber);
878 
					}
879 
					walletHistoryModel
880 
							.setWalletReferenceType(WalletReferenceType.valueOf(row.getCell(2).getStringCellValue()));
881 
				} else {
882 
					throw new ProfitMandiBusinessException("Invalid ReferenceType", "row number " + rowNumber,
883 
							"row number " + rowNumber);
884 
				}
885 
				if (row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
886 
					Date businessDate = row.getCell(3).getDateCellValue();
27065 amit.gupta 887 
					LocalDateTime businessDateTime = LocalDateTime.ofInstant(businessDate.toInstant(), ZoneId.systemDefault());
27064 amit.gupta 888 
					walletHistoryModel.setBusinessDate(businessDateTime);
889 
					;
890 
				} else {
891 
					throw new ProfitMandiBusinessException("Invalid Business Date", "row number " + rowNumber,
892 
							"row number " + rowNumber);
893 
				}
894 
				if (row.getCell(4) != null && row.getCell(4).getCellTypeEnum() == CellType.NUMERIC) {
27065 amit.gupta 895 
					double amount = row.getCell(4).getNumericCellValue();
27064 amit.gupta 896 
					if (amount < 0) {
897 
						throw new ProfitMandiBusinessException("Invalid Amount, only positive values",
898 
								"row number " + rowNumber, "row number " + rowNumber);
899 
					}
900 
					walletHistoryModel.setAmount(amount);
901 
				} else {
902 
					throw new ProfitMandiBusinessException("Invalid Amount", "row number " + rowNumber,
903 
							"row number " + rowNumber);
904 
				}
27065 amit.gupta 905 
				if (row.getCell(5) != null && row.getCell(5).getCellTypeEnum() == CellType.STRING) {
906 
					String transactionType = row.getCell(5).getStringCellValue().toLowerCase();
27064 amit.gupta 907 
					if (transactionType.equals("dr")) {
908 
						walletHistoryModel.setAmount(-walletHistoryModel.getAmount());
909 
					} else if (!transactionType.equals("cr")) {
910 
						throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,
911 
								"row number " + rowNumber);
912 
					}
913 
				} else {
914 
					throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,
915 
							"row number " + rowNumber);
916 
				}
27065 amit.gupta 917 
				if (row.getCell(6) != null && row.getCell(6).getCellTypeEnum() == CellType.STRING) {
28140 amit.gupta 918 
					String description = row.getCell(6).getStringCellValue();
27064 amit.gupta 919 
					walletHistoryModel.setDescription(description);
920 
				} else {
921 
					throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,
922 
							"row number " + rowNumber);
923 
				}
27343 amit.gupta 924 
				if (row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) {
925 
					int reference = (int)row.getCell(7).getNumericCellValue();
926 
					walletHistoryModel.setReference(reference);
927 
				} else {
928 
					throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,
929 
							"row number " + rowNumber);
930 
				}
27064 amit.gupta 931 
				walletHistoryModels.add(walletHistoryModel);
932 
			}
933 
			return walletHistoryModels;
934 
		}
935 
	}
936 
 
33172 tejus.loha 937 
 
938 
	public static String toAlphabet(int number) {
939 
		StringBuffer sb = new StringBuffer();
940 
		boolean loop = true;
941 
		while (loop) {
942 
			sb.append(ALPHABETS.get(number % 26));
943 
			number = number / 26;
944 
			loop = number > 0;
945 
 
946 
		}
947 
		return sb.reverse().toString();
948 
	}
949 
 
950 
 
951 
	public static String getCellValue(Cell cell) {
952 
		if (cell == null) {
953 
			return "Null cell";
954 
		}
955 
		switch (cell.getCellTypeEnum()) {
956 
			case STRING:
957 
				return cell.getStringCellValue().trim();
958 
			case NUMERIC:
959 
				if (DateUtil.isCellDateFormatted(cell)) {
960 
					return cell.getDateCellValue().toString();
961 
				} else {
962 
					return Double.toString(cell.getNumericCellValue());
963 
				}
964 
			case BOOLEAN:
965 
				return Boolean.toString(cell.getBooleanCellValue());
966 
			case FORMULA:
967 
				return cell.getCellFormula();
968 
			default:
969 
				return "";
970 
		}
971 
	}
972 
 
33737 ranu 973 
 
974 
	public static void writePurchaseInvoicesReport(List<PurchaseInvoiceModel> purchaseInvoiceModels, OutputStream outputStream) {
975 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
976 
 
977 
		// CreationHelper createHelper = workbook.getCreationHelper();
978 
 
979 
		SXSSFSheet sheet = workbook.createSheet("PurchaseInvoicesReport");
980 
		sheet.trackAllColumnsForAutoSizing();
981 
 
982 
		Row rowHeader = sheet.createRow(0);
983 
 
984 
		Cell cellPoDateHeader = rowHeader.createCell(1);
34588 ranu 985 
		cellPoDateHeader.setCellValue("GRN_Date");
33737 ranu 986 
 
34574 ranu 987 
		Cell cellSupplierNameHeader = rowHeader.createCell(2);
33737 ranu 988 
		cellSupplierNameHeader.setCellValue("Supplier_Name");
989 
 
34574 ranu 990 
		Cell cellSupplierStateHeader = rowHeader.createCell(3);
34533 ranu 991 
		cellSupplierStateHeader.setCellValue("Supplier_State");
33737 ranu 992 
 
34574 ranu 993 
		Cell cellSupplierGSTINHeader = rowHeader.createCell(4);
34533 ranu 994 
		cellSupplierGSTINHeader.setCellValue("Supplier_GSTIN");
33737 ranu 995 
 
34533 ranu 996 
 
34574 ranu 997 
		Cell cellInvoiceNumberHeader = rowHeader.createCell(5);
33737 ranu 998 
		cellInvoiceNumberHeader.setCellValue("Invoice_Number");
999 
 
34574 ranu 1000 
		Cell cellInvoiceDateHeader = rowHeader.createCell(6);
33737 ranu 1001 
		cellInvoiceDateHeader.setCellValue("Invoice_Date");
1002 
 
34574 ranu 1003 
		Cell cellSupplierWarehouseNameHeader = rowHeader.createCell(7);
34534 ranu 1004 
        cellSupplierWarehouseNameHeader.setCellValue("Warehouse_Name");
33737 ranu 1005 
 
34574 ranu 1006 
		Cell cellSupplierWarehouseIdHeader = rowHeader.createCell(8);
34534 ranu 1007 
        cellSupplierWarehouseIdHeader.setCellValue("Warehouse_Id");
34533 ranu 1008 
 
34574 ranu 1009 
		Cell cellReceivedFromHeader = rowHeader.createCell(9);
34533 ranu 1010 
		cellReceivedFromHeader.setCellValue("Received_By");
1011 
 
1012 
 
34574 ranu 1013 
		Cell cellItemIdHeader = rowHeader.createCell(10);
33737 ranu 1014 
		cellItemIdHeader.setCellValue("Item_Id");
1015 
 
34574 ranu 1016 
		Cell cellBrandHeader = rowHeader.createCell(11);
33737 ranu 1017 
		cellBrandHeader.setCellValue("Brand");
1018 
 
34574 ranu 1019 
		Cell cellModelNameHeader = rowHeader.createCell(12);
33737 ranu 1020 
		cellModelNameHeader.setCellValue("Model_Name");
1021 
 
34574 ranu 1022 
		Cell cellModelNumberHeader = rowHeader.createCell(13);
33737 ranu 1023 
		cellModelNumberHeader.setCellValue("Model_Number");
1024 
 
34574 ranu 1025 
		Cell cellColorHeader = rowHeader.createCell(14);
33737 ranu 1026 
		cellColorHeader.setCellValue("Color");
1027 
 
34574 ranu 1028 
		Cell cellSystemPriceHeader = rowHeader.createCell(15);
34533 ranu 1029 
		cellSystemPriceHeader.setCellValue("System_Price");
1030 
 
34574 ranu 1031 
		Cell cellUnitPriceHeader = rowHeader.createCell(16);
33737 ranu 1032 
		cellUnitPriceHeader.setCellValue("Unit_Price");
1033 
 
34574 ranu 1034 
		Cell cellBasicRateHeader = rowHeader.createCell(17);
34533 ranu 1035 
		cellBasicRateHeader.setCellValue("Basic_Rate");
1036 
 
34574 ranu 1037 
		Cell cellQuantityHeader = rowHeader.createCell(18);
34533 ranu 1038 
		cellQuantityHeader.setCellValue("Quantity");
1039 
 
34574 ranu 1040 
		Cell cellTotalBasicValueHeader = rowHeader.createCell(19);
34533 ranu 1041 
		cellTotalBasicValueHeader.setCellValue("Total_Basic_Value");
1042 
 
34574 ranu 1043 
		Cell cellSGSTHeader = rowHeader.createCell(20);
33737 ranu 1044 
		cellSGSTHeader.setCellValue("SGST%");
1045 
 
34574 ranu 1046 
		Cell cellSGSTAmountHeader = rowHeader.createCell(21);
33737 ranu 1047 
		cellSGSTAmountHeader.setCellValue("SGST_Amount");
1048 
 
34574 ranu 1049 
		Cell cellCGSTHeader = rowHeader.createCell(22);
33737 ranu 1050 
		cellCGSTHeader.setCellValue("CGST%");
1051 
 
34574 ranu 1052 
		Cell cellCGSTAmountHeader = rowHeader.createCell(23);
33737 ranu 1053 
		cellCGSTAmountHeader.setCellValue("CGST_Amount");
1054 
 
34574 ranu 1055 
		Cell cellIGSTHeader = rowHeader.createCell(24);
33737 ranu 1056 
		cellIGSTHeader.setCellValue("IGST%");
1057 
 
34574 ranu 1058 
		Cell cellIGSTAmountHeader = rowHeader.createCell(25);
33737 ranu 1059 
		cellIGSTAmountHeader.setCellValue("IGST_Amount");
1060 
 
34574 ranu 1061 
		Cell cellAmountWithGstHeader = rowHeader.createCell(26);
34533 ranu 1062 
		cellAmountWithGstHeader.setCellValue("Total_Invoice_Value_Amount");
33737 ranu 1063 
 
33740 ranu 1064 
 
33737 ranu 1065 
		Font font = workbook.createFont();
1066 
		CellStyle cellStyle = workbook.createCellStyle();
1067 
		font.setBold(true);
1068 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
1069 
		// font.setFontHeight((short)16);
1070 
		cellStyle.setFont(font);
1071 
		for (int i = 0; i < 14; i++) {
1072 
			if (rowHeader.getCell(i) != null) {
1073 
				rowHeader.getCell(i).setCellStyle(cellStyle);
1074 
			}
1075 
		}
1076 
 
1077 
		for (int index = 0; index < purchaseInvoiceModels.size(); index++) {
1078 
			PurchaseInvoiceModel purchaseInvoiceModel = purchaseInvoiceModels.get(index);
1079 
 
1080 
			Row rowValues = sheet.createRow(index + 1);
1081 
 
1082 
			Cell cellPoDate = rowValues.createCell(1);
34588 ranu 1083 
			cellPoDate.setCellValue(String.valueOf(purchaseInvoiceModel.getFormattedGrnDate()));
33737 ranu 1084 
 
34574 ranu 1085 
			Cell cellSupplierName = rowValues.createCell(2);
33737 ranu 1086 
			cellSupplierName.setCellValue(purchaseInvoiceModel.getSupplierName());
1087 
 
34574 ranu 1088 
			Cell cellSupplierState = rowValues.createCell(3);
34533 ranu 1089 
			cellSupplierState.setCellValue(purchaseInvoiceModel.getSupplierState());
33737 ranu 1090 
 
34574 ranu 1091 
			Cell cellSupplierGSTIN = rowValues.createCell(4);
34533 ranu 1092 
			cellSupplierGSTIN.setCellValue(purchaseInvoiceModel.getSupplierGstIn());
33737 ranu 1093 
 
34574 ranu 1094 
			Cell cellInvoiceNumber = rowValues.createCell(5);
33737 ranu 1095 
			cellInvoiceNumber.setCellValue(purchaseInvoiceModel.getInvoiceNumber());
1096 
 
34574 ranu 1097 
			Cell cellInvoiceDate = rowValues.createCell(6);
34533 ranu 1098 
			cellInvoiceDate.setCellValue(String.valueOf(purchaseInvoiceModel.getFormattedInvoiceDate()));
33737 ranu 1099 
 
34533 ranu 1100 
 
34574 ranu 1101 
			Cell cellSupplierWarehouseName = rowValues.createCell(7);
34534 ranu 1102 
            cellSupplierWarehouseName.setCellValue(purchaseInvoiceModel.getWarehouseName());
34533 ranu 1103 
 
34574 ranu 1104 
			Cell cellSupplierWarehouseId = rowValues.createCell(8);
34534 ranu 1105 
            cellSupplierWarehouseId.setCellValue(purchaseInvoiceModel.getWarehouseId());
34533 ranu 1106 
 
34574 ranu 1107 
			Cell cellReceivedFrom = rowValues.createCell(9);
33737 ranu 1108 
			cellReceivedFrom.setCellValue(purchaseInvoiceModel.getReceivedFrom());
1109 
 
34574 ranu 1110 
			Cell cellItemId = rowValues.createCell(10);
33737 ranu 1111 
			cellItemId.setCellValue(purchaseInvoiceModel.getItemId());
1112 
 
34574 ranu 1113 
			Cell cellBrand = rowValues.createCell(11);
33737 ranu 1114 
			cellBrand.setCellValue(purchaseInvoiceModel.getBrand());
1115 
 
34574 ranu 1116 
			Cell cellModelName = rowValues.createCell(12);
33737 ranu 1117 
			cellModelName.setCellValue(purchaseInvoiceModel.getModelName());
1118 
 
34574 ranu 1119 
			Cell cellModelNumber = rowValues.createCell(13);
33737 ranu 1120 
			cellModelNumber.setCellValue(purchaseInvoiceModel.getModelNumber());
1121 
 
34574 ranu 1122 
			Cell cellColor = rowValues.createCell(14);
33737 ranu 1123 
			cellColor.setCellValue(purchaseInvoiceModel.getColor());
1124 
 
34574 ranu 1125 
			Cell cellSystemPrice = rowValues.createCell(15);
34533 ranu 1126 
			cellSystemPrice.setCellValue(purchaseInvoiceModel.getSystemPrice());
1127 
 
34574 ranu 1128 
			Cell cellUnitPrice = rowValues.createCell(16);
33737 ranu 1129 
			cellUnitPrice.setCellValue(purchaseInvoiceModel.getUnitPrice());
1130 
 
34574 ranu 1131 
			Cell cellBasicRate = rowValues.createCell(17);
34533 ranu 1132 
			cellBasicRate.setCellValue(purchaseInvoiceModel.getBasicRate());
1133 
 
34574 ranu 1134 
			Cell cellQuantity = rowValues.createCell(18);
34533 ranu 1135 
			cellQuantity.setCellValue(purchaseInvoiceModel.getQuantity());
1136 
 
34574 ranu 1137 
			Cell cellTotalBasicValue = rowValues.createCell(19);
34533 ranu 1138 
			cellTotalBasicValue.setCellValue(purchaseInvoiceModel.getTotalBasic());
1139 
 
34574 ranu 1140 
			Cell cellSGSTPercent = rowValues.createCell(20);
33737 ranu 1141 
			cellSGSTPercent.setCellValue(purchaseInvoiceModel.getSgstRate());
1142 
 
34574 ranu 1143 
			Cell cellSGSTAmount = rowValues.createCell(21);
33737 ranu 1144 
			cellSGSTAmount.setCellValue(purchaseInvoiceModel.getSgstAmount());
1145 
 
34574 ranu 1146 
			Cell cellCGSTPercent = rowValues.createCell(22);
33737 ranu 1147 
			cellCGSTPercent.setCellValue(purchaseInvoiceModel.getCgstRate());
1148 
 
34574 ranu 1149 
			Cell cellCGSTAmount = rowValues.createCell(23);
33737 ranu 1150 
			cellCGSTAmount.setCellValue(purchaseInvoiceModel.getCgstAmount());
1151 
 
34574 ranu 1152 
			Cell cellIGSTPercent = rowValues.createCell(24);
33737 ranu 1153 
			cellIGSTPercent.setCellValue(purchaseInvoiceModel.getIgstRate());
1154 
 
34574 ranu 1155 
			Cell cellIGSTAmount = rowValues.createCell(25);
33737 ranu 1156 
			cellIGSTAmount.setCellValue(purchaseInvoiceModel.getIgstAmount());
1157 
 
34574 ranu 1158 
			Cell cellAmountWithGst = rowValues.createCell(26);
34533 ranu 1159 
			cellAmountWithGst.setCellValue(purchaseInvoiceModel.getTotalInvoiceValueAmount());
33737 ranu 1160 
		}
1161 
 
1162 
		for (int index = 0; index < 14; index++) {
1163 
			sheet.autoSizeColumn(index);
1164 
		}
1165 
 
1166 
		try {
1167 
			workbook.write(outputStream);
1168 
			workbook.close();
1169 
		} catch (IOException ioException) {
1170 
			LOGGER.error("Unable to generate excel file", ioException);
1171 
		}
1172 
	}
1173 
 
21786 ashik.ali 1174 
}