Subversion Repositories SmartDukaan

package com.spice.profitmandi.common.util;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.time.LocalDateTime;

import java.time.ZoneId;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.streaming.SXSSFSheet;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;

import com.spice.profitmandi.common.model.InventoryItemAgingModel;

import com.spice.profitmandi.common.model.InventoryItemAgingValue;

import com.spice.profitmandi.common.model.ProfitMandiConstants;

import com.spice.profitmandi.common.model.TagListingModel;

public class ExcelUtils {

	private static final String TAG_ID = "Tag Id";

	private static final String TAG_LABEL = "Tag Label";

	private static final String ITEM_ID = "Item Id";

	private static final String BRAND = "Brand";

	private static final String MODEL_NAME = "Model Name";

	private static final String MODEL_NUMBER = "Model Number";

	private static final String COLOR = "Color";

	private static final String SELLING_PRICE = "Selling Price";

	private static final String MOP = "MOP";

	private static final String SUPPORT_PRICE = "Support Price";

	private static final String START_DATE = "Start Date";

	private static final String TAG_LISTING = "Tag Listing";

	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

	public static void main(String[] args) throws Throwable{

		//List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);

		//writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);

	}

	public static List<TagListingModel> parse(InputStream inputStream) throws Throwable {

		List<TagListingModel> tagListings = new ArrayList<>();

		XSSFWorkbook myWorkBook = null;

		try{

			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");

			myWorkBook = new XSSFWorkbook (inputStream);

			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);

			// Return first sheet from the XLSX workbook 

			XSSFSheet mySheet = myWorkBook.getSheetAt(0);

			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());

			for(int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++){

				XSSFRow row = mySheet.getRow(rowNumber);

				LOGGER.info("row {}", row);

				TagListingModel tagListing = new TagListingModel();

				if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){

					tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());

				}else{

					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");

					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);

					throw profitMandiBusinessException;

				}

				if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC){

					tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());

				}else{

					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");

					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);

					throw profitMandiBusinessException;

				}

				if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){

					tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());

				}else{

					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");

					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);

					throw profitMandiBusinessException;

				}

				if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){

					tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());

				}else{

					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");

					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);

					throw profitMandiBusinessException;

				}

				if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){

					tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());

				}else{

					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");

					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);

					throw profitMandiBusinessException;

				}

				if(row.getCell(10) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(10))){

					Date date = row.getCell(10).getDateCellValue();

					LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());

					tagListing.setStartDate(startDate);

				}else{

					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(10).toString(), "TGLSTNG_VE_1010");

					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);

					throw  profitMandiBusinessException;

				}

				tagListings.add(tagListing);

			}

			myWorkBook.close();

		} catch(IOException ioException){

			ioException.printStackTrace();

			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");

		} finally {

			if(myWorkBook != null){

				try {

					myWorkBook.close();

				} catch (IOException e) {

					// TODO Auto-generated catch block

					e.printStackTrace();

				}

			}

		}

		return tagListings;

	}

	public static OutputStream writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels, List<Integer> intervals, OutputStream outputStream){

		SXSSFWorkbook workbook = new SXSSFWorkbook();

		//CreationHelper createHelper = workbook.getCreationHelper();

		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");

		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));

		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));

		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));

		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));

		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));

		sheet.trackAllColumnsForAutoSizing();

	    Row rowHeader = sheet.createRow(0);

	    Cell cellItemIdHeader = rowHeader.createCell(0);

		cellItemIdHeader.setCellValue("Item Id");

		Cell cellBrandHeader = rowHeader.createCell(1);

		cellBrandHeader.setCellValue("Brand");

		Cell cellModelNameHeader = rowHeader.createCell(2);

		cellModelNameHeader.setCellValue("Model Name");

		Cell cellModelNumberHeader = rowHeader.createCell(3);

		cellModelNumberHeader.setCellValue("Model Number");

		Cell cellColorHeader = rowHeader.createCell(4);

		cellColorHeader.setCellValue("Color");

		Cell cellTypeHeader = rowHeader.createCell(5);

		cellTypeHeader.setCellValue("Item Type");

		Row rowPriceQuantity = sheet.createRow(1);

		for(int index = 0, colIndex = 6; index < intervals.size() + 1; index++, colIndex = colIndex + 2){

			Cell cellHeader = rowHeader.createCell(colIndex);

			if(index == 0){

				cellHeader.setCellValue("Less Than "+intervals.get(index)+" Days");

			}else if(index < intervals.size()){

				cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");

			}else{

				cellHeader.setCellValue("More Than "+intervals.get(index - 1)+" Days");

			}

			sheet.addMergedRegion(new CellRangeAddress(0, 0, colIndex, colIndex + 1));

			rowHeader.createCell(colIndex + 1);

			Cell cellPrice = rowPriceQuantity.createCell(colIndex);

			cellPrice.setCellValue("Price");

			Cell cellQuantity = rowPriceQuantity.createCell(colIndex + 1);

			cellQuantity.setCellValue("Quantity");

		}

		Font font = workbook.createFont();

		CellStyle cellStyle = workbook.createCellStyle();

		font.setBold(true);

		//font.setFontHeight((short)16);

		cellStyle.setFont(font);

		for(int i = 0; i < 8 + (intervals.size() * 2); i++){

			rowHeader.getCell(i).setCellStyle(cellStyle);

			if(rowPriceQuantity.getCell(i) != null){

				rowPriceQuantity.getCell(i).setCellStyle(cellStyle);

			}

		}

		for(int index = 0; index < inventoryItemAgingModels.size(); index++){

			InventoryItemAgingModel inventoryItemAgingModel = inventoryItemAgingModels.get(index);

	    	Row rowValues = sheet.createRow(index + 2);

	    	Cell cellItemId = rowValues.createCell(0);

	    	cellItemId.setCellValue(inventoryItemAgingModel.getItemId());

	    	Cell cellBrand = rowValues.createCell(1);

	    	cellBrand.setCellValue(inventoryItemAgingModel.getBrand());

	    	Cell cellModelName = rowValues.createCell(2);

	    	cellModelName.setCellValue(inventoryItemAgingModel.getModelName());

	    	Cell cellModelNumber = rowValues.createCell(3);

	    	cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());

	    	Cell cellColor = rowValues.createCell(4);

	    	cellColor.setCellValue(inventoryItemAgingModel.getColor());

	    	Cell cellType = rowValues.createCell(5);

	    	cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());

	    	List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();

	    	//LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);

	    	for(int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2){

	    		Cell cellPrice = rowValues.createCell(colIndex);

	    		InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i); 

	    		//LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);

	    		Cell cellQuantity = rowValues.createCell(colIndex + 1);

	    		if(inventoryItemAgingValue != null){

	    			cellPrice.setCellValue(inventoryItemAgingValue.getPrice());

	    			cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());

	    		}else{

	    			cellPrice.setCellValue("NA");

	    			cellQuantity.setCellValue("NA");

	    		}

	    	}

	    }

		for(int index = 0; index < 8 + (intervals.size() * 2); index++){

			sheet.autoSizeColumn(index);

		}

	    try{

			workbook.write(outputStream);

	    	workbook.close();

	    }catch(IOException ioException){

	    	LOGGER.error("Unable to generate excel file", ioException);

	    }

	    return null;

	}

}