Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
21786 ashik.ali 1 
package com.spice.profitmandi.common.util;
2 
 
3 
import java.io.IOException;
4 
import java.io.InputStream;
22470 ashik.ali 5 
import java.io.OutputStream;
21786 ashik.ali 6 
import java.time.LocalDateTime;
7 
import java.time.ZoneId;
8 
import java.util.ArrayList;
9 
import java.util.Date;
23819 govind 10 
import java.util.LinkedHashMap;
21786 ashik.ali 11 
import java.util.List;
23819 govind 12 
import java.util.Map;
13 
import java.util.Set;
21786 ashik.ali 14 
 
24052 amit.gupta 15 
import org.apache.logging.log4j.LogManager;
16 
import org.apache.logging.log4j.Logger;
21786 ashik.ali 17 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
22470 ashik.ali 18 
import org.apache.poi.ss.usermodel.Cell;
19 
import org.apache.poi.ss.usermodel.CellStyle;
21786 ashik.ali 20 
import org.apache.poi.ss.usermodel.CellType;
22470 ashik.ali 21 
import org.apache.poi.ss.usermodel.Font;
22521 ashik.ali 22 
import org.apache.poi.ss.usermodel.HorizontalAlignment;
22470 ashik.ali 23 
import org.apache.poi.ss.usermodel.Row;
21786 ashik.ali 24 
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
22470 ashik.ali 25 
import org.apache.poi.ss.util.CellRangeAddress;
26 
import org.apache.poi.xssf.streaming.SXSSFSheet;
27 
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
22247 amit.gupta 28 
import org.apache.poi.xssf.usermodel.XSSFRow;
21786 ashik.ali 29 
import org.apache.poi.xssf.usermodel.XSSFSheet;
30 
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
31 
 
32 
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
22470 ashik.ali 33 
import com.spice.profitmandi.common.model.InventoryItemAgingModel;
34 
import com.spice.profitmandi.common.model.InventoryItemAgingValue;
22521 ashik.ali 35 
import com.spice.profitmandi.common.model.ItemCompleteLedgerModel;
24107 govind 36 
import com.spice.profitmandi.common.model.PartnerTargetModel;
21786 ashik.ali 37 
import com.spice.profitmandi.common.model.ProfitMandiConstants;
23017 ashik.ali 38 
import com.spice.profitmandi.common.model.SchemeModel;
21786 ashik.ali 39 
import com.spice.profitmandi.common.model.TagListingModel;
40 
 
41 
public class ExcelUtils {
42 
	private static final String TAG_ID = "Tag Id";
43 
	private static final String TAG_LABEL = "Tag Label";
44 
	private static final String ITEM_ID = "Item Id";
45 
	private static final String BRAND = "Brand";
46 
	private static final String MODEL_NAME = "Model Name";
47 
	private static final String MODEL_NUMBER = "Model Number";
48 
	private static final String COLOR = "Color";
49 
	private static final String SELLING_PRICE = "Selling Price";
22204 amit.gupta 50 
	private static final String MOP = "MOP";
21786 ashik.ali 51 
	private static final String SUPPORT_PRICE = "Support Price";
52 
	private static final String START_DATE = "Start Date";
53 
	private static final String TAG_LISTING = "Tag Listing";
24107 govind 54 
	private static final String FOFO_ID = "fofoId";
55 
	private static final String STORE_NAME = "storeName";
56 
	private static final String EMAIL = "email";
57 
	private static final String TARGET_VALUE="targetValue";
21786 ashik.ali 58 
 
23568 govind 59 
	private static final Logger LOGGER = LogManager.getLogger(ExcelUtils.class);
21786 ashik.ali 60 
 
61 
	public static void main(String[] args) throws Throwable{
22470 ashik.ali 62 
		//List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);
63 
		//writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);
21786 ashik.ali 64 
	}
22924 ashik.ali 65 
	public static List<TagListingModel> parse(InputStream inputStream) throws Exception {
21786 ashik.ali 66 
 
67 
		List<TagListingModel> tagListings = new ArrayList<>();
68 
		XSSFWorkbook myWorkBook = null;
69 
		try{
70 
			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
71 
			myWorkBook = new XSSFWorkbook (inputStream);
72 
 
73 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
74 
			// Return first sheet from the XLSX workbook
75 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
22247 amit.gupta 76 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
21786 ashik.ali 77 
 
22247 amit.gupta 78 
			for(int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++){
79 
				XSSFRow row = mySheet.getRow(rowNumber);
80 
				LOGGER.info("row {}", row);
81 
				TagListingModel tagListing = new TagListingModel();
82 
				if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){
83 
					tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
84 
				}else{
85 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
22226 amit.gupta 86 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 87 
					throw profitMandiBusinessException;
88 
				}
22247 amit.gupta 89 
 
90 
				if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC){
91 
					tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
92 
				}else{
93 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
94 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
95 
					throw profitMandiBusinessException;
96 
				}
22204 amit.gupta 97 
 
22247 amit.gupta 98 
				if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){
99 
					tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
100 
				}else{
101 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
22226 amit.gupta 102 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
22209 amit.gupta 103 
					throw profitMandiBusinessException;
21786 ashik.ali 104 
				}
22247 amit.gupta 105 
				if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){
106 
					tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
107 
				}else{
108 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");
109 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
110 
					throw profitMandiBusinessException;
111 
				}
112 
				if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){
113 
					tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
114 
				}else{
115 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
116 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
117 
					throw profitMandiBusinessException;
118 
				}
22563 amit.gupta 119 
				if(row.getCell(10) != null && row.getCell(10).getCellTypeEnum() == CellType.NUMERIC){
120 
					tagListing.setMaxDiscountPrice(Double.valueOf(row.getCell(10).toString()).floatValue());
121 
				}else{
122 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
123 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
124 
					throw profitMandiBusinessException;
125 
				}
126 
				if(row.getCell(11) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(11))){
22865 amit.gupta 127 
					Date date = row.getCell(11).getDateCellValue();
22247 amit.gupta 128 
					LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
129 
					tagListing.setStartDate(startDate);
130 
				}else{
22865 amit.gupta 131 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(11).toString(), "TGLSTNG_VE_1010");
22247 amit.gupta 132 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
133 
					throw  profitMandiBusinessException;
134 
				}
135 
				tagListings.add(tagListing);
21786 ashik.ali 136 
			}
137 
			myWorkBook.close();
22247 amit.gupta 138 
		} catch(IOException ioException){
22206 amit.gupta 139 
			ioException.printStackTrace();
21786 ashik.ali 140 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
22247 amit.gupta 141 
		} finally {
21786 ashik.ali 142 
			if(myWorkBook != null){
143 
				try {
144 
					myWorkBook.close();
145 
				} catch (IOException e) {
146 
					// TODO Auto-generated catch block
147 
					e.printStackTrace();
148 
				}
149 
			}
150 
		}
151 
		return tagListings;
152 
	}
22470 ashik.ali 153 
 
22521 ashik.ali 154 
	public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels, List<Integer> intervals, OutputStream outputStream){
22470 ashik.ali 155 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
156 
 
157 
		//CreationHelper createHelper = workbook.getCreationHelper();
158 
 
159 
		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
160 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
161 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
162 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
163 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
164 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
165 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
166 
		sheet.trackAllColumnsForAutoSizing();
167 
 
168 
 
169 
	    Row rowHeader = sheet.createRow(0);
170 
	    Cell cellItemIdHeader = rowHeader.createCell(0);
171 
		cellItemIdHeader.setCellValue("Item Id");
172 
		Cell cellBrandHeader = rowHeader.createCell(1);
173 
		cellBrandHeader.setCellValue("Brand");
174 
		Cell cellModelNameHeader = rowHeader.createCell(2);
175 
		cellModelNameHeader.setCellValue("Model Name");
176 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
177 
		cellModelNumberHeader.setCellValue("Model Number");
178 
		Cell cellColorHeader = rowHeader.createCell(4);
179 
		cellColorHeader.setCellValue("Color");
180 
		Cell cellTypeHeader = rowHeader.createCell(5);
181 
		cellTypeHeader.setCellValue("Item Type");
182 
		Row rowPriceQuantity = sheet.createRow(1);
183 
		for(int index = 0, colIndex = 6; index < intervals.size() + 1; index++, colIndex = colIndex + 2){
184 
			Cell cellHeader = rowHeader.createCell(colIndex);
185 
			if(index == 0){
186 
				cellHeader.setCellValue("Less Than "+intervals.get(index)+" Days");
187 
			}else if(index < intervals.size()){
188 
				cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");
189 
			}else{
190 
				cellHeader.setCellValue("More Than "+intervals.get(index - 1)+" Days");
191 
			}
192 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, colIndex, colIndex + 1));
193 
			rowHeader.createCell(colIndex + 1);
194 
			Cell cellPrice = rowPriceQuantity.createCell(colIndex);
195 
			cellPrice.setCellValue("Price");
196 
			Cell cellQuantity = rowPriceQuantity.createCell(colIndex + 1);
197 
			cellQuantity.setCellValue("Quantity");
198 
		}
199 
		Font font = workbook.createFont();
200 
		CellStyle cellStyle = workbook.createCellStyle();
201 
		font.setBold(true);
202 
		//font.setFontHeight((short)16);
203 
		cellStyle.setFont(font);
22486 ashik.ali 204 
		for(int i = 0; i < 8 + (intervals.size() * 2); i++){
22470 ashik.ali 205 
			rowHeader.getCell(i).setCellStyle(cellStyle);
206 
			if(rowPriceQuantity.getCell(i) != null){
207 
				rowPriceQuantity.getCell(i).setCellStyle(cellStyle);
208 
			}
209 
		}
210 
 
211 
		for(int index = 0; index < inventoryItemAgingModels.size(); index++){
212 
			InventoryItemAgingModel inventoryItemAgingModel = inventoryItemAgingModels.get(index);
213 
	    	Row rowValues = sheet.createRow(index + 2);
214 
	    	Cell cellItemId = rowValues.createCell(0);
215 
	    	cellItemId.setCellValue(inventoryItemAgingModel.getItemId());
216 
	    	Cell cellBrand = rowValues.createCell(1);
217 
	    	cellBrand.setCellValue(inventoryItemAgingModel.getBrand());
218 
	    	Cell cellModelName = rowValues.createCell(2);
219 
	    	cellModelName.setCellValue(inventoryItemAgingModel.getModelName());
220 
	    	Cell cellModelNumber = rowValues.createCell(3);
221 
	    	cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());
222 
	    	Cell cellColor = rowValues.createCell(4);
223 
	    	cellColor.setCellValue(inventoryItemAgingModel.getColor());
224 
	    	Cell cellType = rowValues.createCell(5);
225 
	    	cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());
226 
	    	List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
227 
	    	//LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
228 
	    	for(int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2){
229 
	    		Cell cellPrice = rowValues.createCell(colIndex);
230 
	    		InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i);
231 
	    		//LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);
232 
	    		Cell cellQuantity = rowValues.createCell(colIndex + 1);
233 
	    		if(inventoryItemAgingValue != null){
234 
	    			cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
235 
	    			cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
236 
	    		}else{
22521 ashik.ali 237 
	    			cellPrice.setCellValue("-");
238 
	    			cellQuantity.setCellValue("-");
22470 ashik.ali 239 
	    		}
240 
	    	}
241 
	    }
242 
 
22486 ashik.ali 243 
		for(int index = 0; index < 8 + (intervals.size() * 2); index++){
22470 ashik.ali 244 
			sheet.autoSizeColumn(index);
245 
		}
246 
 
247 
	    try{
22486 ashik.ali 248 
			workbook.write(outputStream);
22470 ashik.ali 249 
	    	workbook.close();
250 
	    }catch(IOException ioException){
22486 ashik.ali 251 
	    	LOGGER.error("Unable to generate excel file", ioException);
22470 ashik.ali 252 
	    }
253 
	}
254 
 
22521 ashik.ali 255 
	public static void writeItemCompleteLedgerModels(List<ItemCompleteLedgerModel> itemCompleteLedgerModels, OutputStream outputStream){
256 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
257 
 
258 
		//CreationHelper createHelper = workbook.getCreationHelper();
259 
 
260 
		SXSSFSheet sheet = workbook.createSheet("ItemCompleteLeger");
261 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
262 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
263 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
264 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
265 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
266 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
267 
		sheet.trackAllColumnsForAutoSizing();
268 
 
269 
 
270 
	    Row rowHeader = sheet.createRow(0);
271 
	    Cell cellItemIdHeader = rowHeader.createCell(0);
272 
		cellItemIdHeader.setCellValue("Item Id");
273 
		Cell cellBrandHeader = rowHeader.createCell(1);
274 
		cellBrandHeader.setCellValue("Brand");
275 
		Cell cellModelNameHeader = rowHeader.createCell(2);
276 
		cellModelNameHeader.setCellValue("Model Name");
277 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
278 
		cellModelNumberHeader.setCellValue("Model Number");
279 
		Cell cellColorHeader = rowHeader.createCell(4);
280 
		cellColorHeader.setCellValue("Color");
281 
		Cell cellTypeHeader = rowHeader.createCell(5);
282 
		cellTypeHeader.setCellValue("Item Type");
283 
		Cell cellOpeningBalanceHeader = rowHeader.createCell(6);
284 
		cellOpeningBalanceHeader.setCellValue("Opening Balance");
285 
		Cell cellInwardsHeader = rowHeader.createCell(9);
286 
		cellInwardsHeader.setCellValue("Inwards");
287 
		Cell cellOutwardsHeader = rowHeader.createCell(12);
288 
		cellOutwardsHeader.setCellValue("Outwards");
289 
		Cell cellClosingBalanceHeader = rowHeader.createCell(15);
290 
		cellClosingBalanceHeader.setCellValue("Closing Balance");
291 
		Row rowQuantityRateValue = sheet.createRow(1);
292 
 
293 
		for(int index = 6; index < 18; index = index + 3){
294 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
295 
			cellQuantityHeader.setCellValue("Quantity");
296 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
297 
			cellRateHeader.setCellValue("Rate");
298 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
299 
			cellValueHeader.setCellValue("Value");
300 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
301 
		}
302 
		Font font = workbook.createFont();
303 
		CellStyle cellStyle = workbook.createCellStyle();
304 
		font.setBold(true);
305 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
306 
		//font.setFontHeight((short)16);
307 
		cellStyle.setFont(font);
308 
		for(int i = 0; i < 18; i++){
309 
			if(rowHeader.getCell(i) != null){
310 
				rowHeader.getCell(i).setCellStyle(cellStyle);
311 
			}
312 
		}
313 
		int openingQuantityTotal = 0;
314 
		float openingValueTotal = 0;
315 
		int inwardsQuantityTotal = 0;
316 
		float inwardsValueTotal = 0;
317 
		int outwardsQuantityTotal = 0;
318 
		float outwardsValueTotal = 0;
319 
		int closingQuantityTotal = 0;
320 
		float closingValueTotal = 0;
321 
		for(int index = 0; index < itemCompleteLedgerModels.size(); index++){
322 
			ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
323 
	    	Row rowValues = sheet.createRow(index + 2);
324 
	    	Cell cellItemId = rowValues.createCell(0);
325 
	    	cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
326 
	    	Cell cellBrand = rowValues.createCell(1);
327 
	    	cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
328 
	    	Cell cellModelName = rowValues.createCell(2);
329 
	    	cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
330 
	    	Cell cellModelNumber = rowValues.createCell(3);
331 
	    	cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
332 
	    	Cell cellColor = rowValues.createCell(4);
333 
	    	cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
334 
	    	Cell cellType = rowValues.createCell(5);
335 
	    	cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
336 
	    	Cell cellOpeningQuantity = rowValues.createCell(6);
337 
	    	Cell cellOpeningRate = rowValues.createCell(7);
338 
	    	Cell cellOpeningValue = rowValues.createCell(8);
339 
	    	if(itemCompleteLedgerModel.getOpeningLedger() == null){
340 
	    		cellOpeningQuantity.setCellValue("-");
341 
	    		cellOpeningRate.setCellValue("-");
342 
	    		cellOpeningValue.setCellValue("-");
343 
	    	}else{
344 
	    		cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
345 
	    		cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
346 
	    		cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
347 
	    		openingQuantityTotal = openingQuantityTotal + itemCompleteLedgerModel.getOpeningLedger().getQuantity();
348 
	    		openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
349 
	    	}
350 
	    	Cell cellInwardsQuantity = rowValues.createCell(9);
351 
	    	Cell cellInwardsRate = rowValues.createCell(10);
352 
	    	Cell cellInwardsValue = rowValues.createCell(11);
353 
	    	if(itemCompleteLedgerModel.getInwardsLedger() == null){
354 
	    		cellInwardsQuantity.setCellValue("-");
355 
	    		cellInwardsRate.setCellValue("-");
356 
	    		cellInwardsValue.setCellValue("-");
357 
	    	}else{
358 
	    		cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
359 
	    		cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
360 
	    		cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
361 
	    		inwardsQuantityTotal = inwardsQuantityTotal + itemCompleteLedgerModel.getInwardsLedger().getQuantity();
362 
	    		inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
363 
	    	}
364 
	    	Cell cellOutwardsQuantity = rowValues.createCell(12);
365 
	    	Cell cellOutwardsRate = rowValues.createCell(13);
366 
	    	Cell cellOutwardsValue = rowValues.createCell(14);
367 
	    	if(itemCompleteLedgerModel.getOutwardsLedger() == null){
368 
	    		cellOutwardsQuantity.setCellValue("-");
369 
	    		cellOutwardsRate.setCellValue("-");
370 
	    		cellOutwardsValue.setCellValue("-");
371 
	    	}else{
372 
	    		cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
373 
	    		cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
374 
	    		cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
375 
	    		outwardsQuantityTotal = outwardsQuantityTotal + itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
376 
	    		outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
377 
	    	}
378 
	    	Cell cellClosingQuantity = rowValues.createCell(15);
379 
	    	Cell cellClosingRate = rowValues.createCell(16);
380 
	    	Cell cellClosingValue = rowValues.createCell(17);
381 
	    	if(itemCompleteLedgerModel.getClosingLedger() == null){
382 
	    		cellClosingQuantity.setCellValue("-");
383 
	    		cellClosingRate.setCellValue("-");
384 
	    		cellClosingValue.setCellValue("-");
385 
	    	}else{
386 
	    		cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
387 
	    		cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
388 
	    		cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
389 
	    		closingQuantityTotal = closingQuantityTotal + itemCompleteLedgerModel.getClosingLedger().getQuantity();
390 
	    		closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
391 
	    	}
392 
	    }
393 
 
394 
		Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);
395 
 
396 
		if(openingQuantityTotal > 0){
397 
			Cell cellOpeningQuantityTotal = rowTotal.createCell(6);
398 
			cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);
399 
			Cell cellOpeningValueTotal = rowTotal.createCell(8);
400 
			cellOpeningValueTotal.setCellValue(openingValueTotal);
401 
		}
402 
 
403 
		if(inwardsQuantityTotal > 0){
404 
			Cell cellInwardsQuantityTotal = rowTotal.createCell(9);
405 
			cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);
406 
			Cell cellInwardsValueTotal = rowTotal.createCell(11);
407 
			cellInwardsValueTotal.setCellValue(inwardsValueTotal);
408 
		}
409 
 
410 
		if(outwardsQuantityTotal > 0){
411 
			Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);
412 
			cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);
413 
			Cell cellOutwardsValueTotal = rowTotal.createCell(14);
414 
			cellOutwardsValueTotal.setCellValue(outwardsValueTotal);
415 
		}
416 
 
417 
		if(closingQuantityTotal > 0){
418 
			Cell cellClosingQuantityTotal = rowTotal.createCell(15);
419 
			cellClosingQuantityTotal.setCellValue(closingQuantityTotal);
420 
			Cell cellClosingValueTotal = rowTotal.createCell(17);
421 
			cellClosingValueTotal.setCellValue(closingValueTotal);
422 
		}
423 
 
424 
		for(int index = 0; index < 18; index++){
425 
			sheet.autoSizeColumn(index);
426 
		}
427 
 
428 
	    try{
429 
			workbook.write(outputStream);
430 
	    	workbook.close();
431 
	    }catch(IOException ioException){
432 
	    	LOGGER.error("Unable to generate excel file", ioException);
433 
	    }
434 
	}
23017 ashik.ali 435 
 
436 
	public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream){
437 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
438 
 
439 
		//CreationHelper createHelper = workbook.getCreationHelper();
440 
 
441 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
442 
		sheet.trackAllColumnsForAutoSizing();
443 
 
444 
 
445 
	    Row rowHeader = sheet.createRow(0);
446 
	    Cell cellSchemeIdHeader = rowHeader.createCell(0);
447 
		cellSchemeIdHeader.setCellValue("Scheme Id");
448 
		Cell cellNameHeader = rowHeader.createCell(1);
449 
		cellNameHeader.setCellValue("Name");
450 
		Cell cellDescriptionHeader = rowHeader.createCell(2);
451 
		cellDescriptionHeader.setCellValue("Description");
452 
		Cell cellSchemeTypeHeader = rowHeader.createCell(3);
453 
		cellSchemeTypeHeader.setCellValue("Scheme Type");
454 
		Cell cellAmountTypeHeader = rowHeader.createCell(4);
455 
		cellAmountTypeHeader.setCellValue("Amount Type");
456 
		Cell cellAmountHeader = rowHeader.createCell(5);
457 
		cellAmountHeader.setCellValue("Amount");
458 
		Cell cellStartDateTimeHeader = rowHeader.createCell(6);
459 
		cellStartDateTimeHeader.setCellValue("Start Date Time");
460 
		Cell cellEndDateTimeHeader = rowHeader.createCell(7);
461 
		cellEndDateTimeHeader.setCellValue("End Date Time");
462 
		Cell cellCreatedAtHeader = rowHeader.createCell(8);
463 
		cellCreatedAtHeader.setCellValue("Created At");
464 
		Cell cellActiveHeader = rowHeader.createCell(9);
465 
		cellActiveHeader.setCellValue("Active");
466 
		Cell cellExpireHeader = rowHeader.createCell(10);
467 
		cellExpireHeader.setCellValue("Expire");
468 
		Cell cellCreatedByHeader = rowHeader.createCell(11);
469 
		cellCreatedByHeader.setCellValue("Created By");
470 
		Cell cellItemIdsHeader = rowHeader.createCell(12);
471 
		cellItemIdsHeader.setCellValue("Item Ids");
472 
		Cell cellRetailerIdsHeader = rowHeader.createCell(13);
473 
		cellRetailerIdsHeader.setCellValue("Retailer Ids");
474 
		//Row rowQuantityRateValue = sheet.createRow(1);
475 
 
476 
		/*for(int index = 6; index < 18; index = index + 3){
477 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
478 
			cellQuantityHeader.setCellValue("Quantity");
479 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
480 
			cellRateHeader.setCellValue("Rate");
481 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
482 
			cellValueHeader.setCellValue("Value");
483 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
484 
		}*/
485 
		Font font = workbook.createFont();
486 
		CellStyle cellStyle = workbook.createCellStyle();
487 
		font.setBold(true);
488 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
489 
		//font.setFontHeight((short)16);
490 
		cellStyle.setFont(font);
491 
		for(int i = 0; i < 14; i++){
492 
			if(rowHeader.getCell(i) != null){
493 
				rowHeader.getCell(i).setCellStyle(cellStyle);
494 
			}
495 
		}
496 
 
497 
		for(int index = 0; index < schemeModels.size(); index++){
498 
			SchemeModel schemeModel = schemeModels.get(index);
499 
			//ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
500 
	    	Row rowValues = sheet.createRow(index + 1);
501 
	    	Cell cellSchemeId = rowValues.createCell(0);
502 
			cellSchemeId.setCellValue(schemeModel.getSchemeId());
503 
			Cell cellName = rowValues.createCell(1);
504 
			cellName.setCellValue(schemeModel.getName());
505 
			Cell cellDescription = rowValues.createCell(2);
506 
			cellDescription.setCellValue(schemeModel.getDescription());
507 
			Cell cellSchemeType = rowValues.createCell(3);
508 
			cellSchemeType.setCellValue(schemeModel.getSchemeType());
509 
			Cell cellAmountType = rowValues.createCell(4);
510 
			cellAmountType.setCellValue(schemeModel.getAmountType());
511 
			Cell cellAmount = rowValues.createCell(5);
512 
			cellAmount.setCellValue(schemeModel.getAmount());
513 
			Cell cellStartDateTime = rowValues.createCell(6);
514 
			cellStartDateTime.setCellValue(schemeModel.getStartDateTime());
515 
			Cell cellEndDateTime = rowValues.createCell(7);
516 
			cellEndDateTime.setCellValue(schemeModel.getEndDateTime());
517 
			Cell cellCreatedAt = rowValues.createCell(8);
518 
			cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());
519 
			Cell cellActive = rowValues.createCell(9);
520 
			if(schemeModel.getActiveTimestamp() !=null){
521 
				cellActive.setCellValue(schemeModel.getActiveTimestamp());
522 
			}else{
523 
				cellActive.setCellValue("False");
524 
			}
525 
			Cell cellExpire = rowValues.createCell(10);
526 
			if(schemeModel.getExpireTimestamp() != null){
527 
				cellExpire.setCellValue(schemeModel.getExpireTimestamp());
528 
			}else{
529 
				cellExpire.setCellValue("False");
530 
			}
531 
			Cell cellCreatedBy = rowValues.createCell(11);
532 
			cellCreatedBy.setCellValue(schemeModel.getCreatedBy());
533 
			Cell cellItemIds = rowValues.createCell(12);
23338 ashik.ali 534 
			cellItemIds.setCellValue(schemeModel.getItemStringMap().toString());
23017 ashik.ali 535 
			Cell cellRetailerIds = rowValues.createCell(13);
536 
			cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());
23338 ashik.ali 537 
			int maxHeight = Math.max(schemeModel.getItemStringMap().size(), schemeModel.getRetailerIds().size());
23017 ashik.ali 538 
			if(maxHeight > 1){
539 
				rowValues.setHeight((short)(maxHeight * 240));
540 
			}
541 
	    }
542 
 
543 
		for(int index = 0; index < 14; index++){
544 
			sheet.autoSizeColumn(index);
545 
		}
546 
 
547 
	    try{
548 
			workbook.write(outputStream);
549 
	    	workbook.close();
550 
	    }catch(IOException ioException){
551 
	    	LOGGER.error("Unable to generate excel file", ioException);
552 
	    }
553 
	}
23819 govind 554 
	public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId,int itemId,OutputStream outputStream){
555 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
556 
 
557 
		//CreationHelper createHelper = workbook.getCreationHelper();
558 
 
559 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
560 
		sheet.trackAllColumnsForAutoSizing();
561 
 
562 
 
563 
	    Row rowHeader = sheet.createRow(0);
564 
	    Cell cellItemHeader = rowHeader.createCell(0);
565 
	    cellItemHeader.setCellValue("ITEMID");
566 
		Cell cellIMEIHeader = rowHeader.createCell(1);
567 
		cellIMEIHeader.setCellValue("IMEI");
568 
		Cell cellFOFOIDHeader = rowHeader.createCell(2);
569 
		cellFOFOIDHeader.setCellValue("RETAILERNAME");
570 
		//Row rowQuantityRateValue = sheet.createRow(1);
571 
 
572 
		/*for(int index = 6; index < 18; index = index + 3){
573 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
574 
			cellQuantityHeader.setCellValue("Quantity");
575 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
576 
			cellRateHeader.setCellValue("Rate");
577 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
578 
			cellValueHeader.setCellValue("Value");
579 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
580 
		}*/
581 
		Font font = workbook.createFont();
582 
		CellStyle cellStyle = workbook.createCellStyle();
583 
		font.setBold(true);
584 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
585 
		//font.setFontHeight((short)16);
586 
		cellStyle.setFont(font);
587 
		for(int i = 0; i < 14; i++){
588 
			if(rowHeader.getCell(i) != null){
589 
				rowHeader.getCell(i).setCellStyle(cellStyle);
590 
			}
591 
		}
592 
		LinkedHashMap<String,String> priceAmount=new LinkedHashMap<>(priceDropIMEIfofoId);
593 
		Set<String> keyset=priceAmount.keySet();
594 
		int rownum = 1;
595 
		for (String key : keyset) {
596 
            Row row = sheet.createRow(rownum++);
597 
            String objArr = priceAmount.get(key);
598 
            int cellnum = 0;
599 
            Cell cell = row.createCell(cellnum++);
600 
            cell.setCellValue(itemId);
601 
            Cell cell1 = row.createCell(cellnum++);
602 
            cell1.setCellValue(key);
603 
            Cell cell2 = row.createCell(cellnum++);
604 
            cell2.setCellValue(priceAmount.get(key));
605 
            }
606 
 
607 
		for(int index = 0; index < 14; index++){
608 
			sheet.autoSizeColumn(index);
609 
		}
610 
 
611 
	    try{
612 
			workbook.write(outputStream);
613 
	    	workbook.close();
614 
	    }catch(IOException ioException){
615 
	    	LOGGER.error("Unable to generate excel file", ioException);
616 
	    }
617 
	}
618 
	public static void writePriceDropForAllIMEI(Map<String,String>priceDropAmount,OutputStream outputStream){
619 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
620 
 
621 
		//CreationHelper createHelper = workbook.getCreationHelper();
622 
 
623 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
624 
		sheet.trackAllColumnsForAutoSizing();
625 
 
626 
 
627 
	    Row rowHeader = sheet.createRow(0);
628 
	    Cell cellItemHeader = rowHeader.createCell(0);
629 
	    cellItemHeader.setCellValue("Itemdescription");
630 
		Cell cellIMEIHeader = rowHeader.createCell(1);
631 
		cellIMEIHeader.setCellValue("IMEI");
632 
		//Row rowQuantityRateValue = sheet.createRow(1);
633 
 
634 
		/*for(int index = 6; index < 18; index = index + 3){
635 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
636 
			cellQuantityHeader.setCellValue("Quantity");
637 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
638 
			cellRateHeader.setCellValue("Rate");
639 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
640 
			cellValueHeader.setCellValue("Value");
641 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
642 
		}*/
643 
		Font font = workbook.createFont();
644 
		CellStyle cellStyle = workbook.createCellStyle();
645 
		font.setBold(true);
646 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
647 
		//font.setFontHeight((short)16);
648 
		cellStyle.setFont(font);
649 
		for(int i = 0; i < 14; i++){
650 
			if(rowHeader.getCell(i) != null){
651 
				rowHeader.getCell(i).setCellStyle(cellStyle);
652 
			}
653 
		}
654 
		Set<String> keyset=priceDropAmount.keySet();
655 
		int rownum = 1;
656 
		for (String key : keyset) {
657 
            Row row = sheet.createRow(rownum++);
658 
            String objArr = priceDropAmount.get(key);
659 
            int cellnum = 0;
660 
            Cell cell = row.createCell(cellnum++);
661 
            cell.setCellValue(priceDropAmount.get(key));
662 
            Cell cell1 = row.createCell(cellnum++);
663 
            cell1.setCellValue(key);
664 
            }
665 
 
666 
		for(int index = 0; index < 14; index++){
667 
			sheet.autoSizeColumn(index);
668 
		}
669 
 
670 
	    try{
671 
			workbook.write(outputStream);
672 
	    	workbook.close();
673 
	    }catch(IOException ioException){
674 
	    	LOGGER.error("Unable to generate excel file", ioException);
675 
	    }
676 
	}
24107 govind 677 
public static List<PartnerTargetModel> parseFromExcel(InputStream inputStream) throws Exception {
678 
 
679 
		List<PartnerTargetModel> partnerTargetModels = new ArrayList<>();
680 
		XSSFWorkbook myWorkBook = null;
681 
		try{
682 
			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
683 
			myWorkBook = new XSSFWorkbook (inputStream);
684 
 
685 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
686 
			// Return first sheet from the XLSX workbook
687 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
688 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
689 
 
690 
			for(int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++){
691 
				XSSFRow row = mySheet.getRow(rowNumber);
692 
				LOGGER.info("row {}", row);
693 
				PartnerTargetModel partnerTargetModel = new PartnerTargetModel();
694 
				if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){
695 
					partnerTargetModel.setFofoId((int) row.getCell(0).getNumericCellValue());
696 
				}else{
697 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(FOFO_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
698 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
699 
					throw profitMandiBusinessException;
700 
				}
701 
 
702 
				if(row.getCell(1) != null && row.getCell(1).getCellTypeEnum() == CellType.STRING){
703 
					partnerTargetModel.setStoreName(row.getCell(1).getStringCellValue());
704 
				}else{
705 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(STORE_NAME, row.getCell(2).toString(), "TGLSTNG_VE_1010");
706 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
707 
					throw profitMandiBusinessException;
708 
				}
709 
 
710 
				if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.STRING){
711 
					partnerTargetModel.setEmail(row.getCell(2).getStringCellValue());
712 
				}else{
713 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(EMAIL, row.getCell(7), "TGLSTNG_VE_1010");
714 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
715 
					throw profitMandiBusinessException;
716 
				}
717 
				if(row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC){
718 
					partnerTargetModel.setTargetValue((int) row.getCell(3).getNumericCellValue());
719 
				}else{
720 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TARGET_VALUE, row.getCell(8), "TGLSTNG_VE_1010");
721 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
722 
					throw profitMandiBusinessException;
723 
				}
724 
 
725 
				partnerTargetModels.add(partnerTargetModel);
726 
			}
727 
			myWorkBook.close();
728 
		} catch(IOException ioException){
729 
			ioException.printStackTrace();
730 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
731 
		} finally {
732 
			if(myWorkBook != null){
733 
				try {
734 
					myWorkBook.close();
735 
				} catch (IOException e) {
736 
					// TODO Auto-generated catch block
737 
					e.printStackTrace();
738 
				}
739 
			}
740 
		}
741 
		return partnerTargetModels;
742 
	}
21786 ashik.ali 743 
}