Subversion Repositories SmartDukaan

Rev

Rev 27390 | Rev 28869 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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