WebSVN – SmartDukaan – Diff – /trunk/profitmandi-common/src/main/java/com/spice/profitmandi/common/util/ExcelUtils.java

 import org.apache.poi.xssf.usermodel.XSSFRow;
 import org.apache.poi.xssf.usermodel.XSSFSheet;
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
+import com.spice.profitmandi.common.model.CustomRetailer;
 import com.spice.profitmandi.common.model.InventoryItemAgingModel;
 import com.spice.profitmandi.common.model.InventoryItemAgingValue;
 import com.spice.profitmandi.common.model.ItemCompleteLedgerModel;
 import com.spice.profitmandi.common.model.PartnerTargetModel;
 import com.spice.profitmandi.common.model.ProfitMandiConstants;
 import com.spice.profitmandi.common.model.SchemeModel;
 import com.spice.profitmandi.common.model.TagListingModel;
+import in.shop2020.model.v1.catalog.SalesPotential;
 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 START_DATE = "Start Date";
 	private static final String TAG_LISTING = "Tag Listing";
 	private static final String FOFO_ID = "fofoId";
 	private static final String STORE_NAME = "storeName";
 	private static final String EMAIL = "email";
-	private static final String TARGET_VALUE="targetValue";
+	private static final String TARGET_VALUE = "targetValue";
 	private static final Logger LOGGER = LogManager.getLogger(ExcelUtils.class);
-	public static void main(String[] args) throws Throwable{
+	public static void main(String[] args) throws Throwable {
-		//List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);
+		// List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);
-		//writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);
+		// writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);
+	}
 	public static List<TagListingModel> parse(InputStream inputStream) throws Exception {
 		List<TagListingModel> tagListings = new ArrayList<>();
 		XSSFWorkbook myWorkBook = null;
-		try{
+		try {
+			// FileInputStream fileInputStream = new
-			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
+			// FileInputStream("/home/ashikali/tag_listing1.xlsx");
-			myWorkBook = new XSSFWorkbook (inputStream);
+			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++){
+			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){
+				if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
 					tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
+					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){
+				if (row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC) {
 					tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
+					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){
+				if (row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) {
 					tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
+					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){
+				if (row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC) {
 					tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");
+					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){
+				if (row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC) {
 					tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
+					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 && row.getCell(10).getCellTypeEnum() == CellType.NUMERIC){
+				if (row.getCell(10) != null && row.getCell(10).getCellTypeEnum() == CellType.NUMERIC) {
 					tagListing.setMaxDiscountPrice(Double.valueOf(row.getCell(10).toString()).floatValue());
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
+					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
+							SUPPORT_PRICE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
 					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 					throw profitMandiBusinessException;
+				}
-				if(row.getCell(11) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(11))){
+				if (row.getCell(11) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(11))) {
 					Date date = row.getCell(11).getDateCellValue();
 					LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
 					tagListing.setStartDate(startDate);
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(11).toString(), "TGLSTNG_VE_1010");
+					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
+							START_DATE, row.getCell(11).toString(), "TGLSTNG_VE_1010");
 					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
-					throw  profitMandiBusinessException;
+					throw profitMandiBusinessException;
+				}
 				tagListings.add(tagListing);
+			}
 			myWorkBook.close();
-		} catch(IOException ioException){
+		} catch (IOException ioException) {
 			ioException.printStackTrace();
-			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
+			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
+					"EXL_VE_1000");
 		} finally {
-			if(myWorkBook != null){
+			if (myWorkBook != null) {
 				try {
 					myWorkBook.close();
 				} catch (IOException e) {
 					// TODO Auto-generated catch block
 					e.printStackTrace();
+				}
+			}
+		}
 		return tagListings;
+	}
-	public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels, List<Integer> intervals, OutputStream outputStream){
+	public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels,
+			List<Integer> intervals, OutputStream outputStream) {
 		SXSSFWorkbook workbook = new SXSSFWorkbook();
-		//CreationHelper createHelper = workbook.getCreationHelper();
+		// 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);
+		Row rowHeader = sheet.createRow(0);
-	    Cell cellItemIdHeader = rowHeader.createCell(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 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){
+		for (int index = 0, colIndex = 6; index < intervals.size() + 1; index++, colIndex = colIndex + 2) {
 			Cell cellHeader = rowHeader.createCell(colIndex);
-			if(index == 0){
+			if (index == 0) {
-				cellHeader.setCellValue("Less Than "+intervals.get(index)+" Days");
+				cellHeader.setCellValue("Less Than " + intervals.get(index) + " Days");
-			}else if(index < intervals.size()){
+			} else if (index < intervals.size()) {
 				cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");
-			}else{
+			} else {
-				cellHeader.setCellValue("More Than "+intervals.get(index - 1)+" Days");
+				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");
 			cellQuantity.setCellValue("Quantity");
+		}
 		Font font = workbook.createFont();
 		CellStyle cellStyle = workbook.createCellStyle();
 		font.setBold(true);
-		//font.setFontHeight((short)16);
+		// font.setFontHeight((short)16);
 		cellStyle.setFont(font);
-		for(int i = 0; i < 8 + (intervals.size() * 2); i++){
+		for (int i = 0; i < 8 + (intervals.size() * 2); i++) {
 			rowHeader.getCell(i).setCellStyle(cellStyle);
-			if(rowPriceQuantity.getCell(i) != null){
+			if (rowPriceQuantity.getCell(i) != null) {
 				rowPriceQuantity.getCell(i).setCellStyle(cellStyle);
+			}
+		}
-		for(int index = 0; index < inventoryItemAgingModels.size(); index++){
+		for (int index = 0; index < inventoryItemAgingModels.size(); index++) {
 			InventoryItemAgingModel inventoryItemAgingModel = inventoryItemAgingModels.get(index);
-	    	Row rowValues = sheet.createRow(index + 2);
+			Row rowValues = sheet.createRow(index + 2);
-	    	Cell cellItemId = rowValues.createCell(0);
+			Cell cellItemId = rowValues.createCell(0);
-	    	cellItemId.setCellValue(inventoryItemAgingModel.getItemId());
+			cellItemId.setCellValue(inventoryItemAgingModel.getItemId());
-	    	Cell cellBrand = rowValues.createCell(1);
+			Cell cellBrand = rowValues.createCell(1);
-	    	cellBrand.setCellValue(inventoryItemAgingModel.getBrand());
+			cellBrand.setCellValue(inventoryItemAgingModel.getBrand());
-	    	Cell cellModelName = rowValues.createCell(2);
+			Cell cellModelName = rowValues.createCell(2);
-	    	cellModelName.setCellValue(inventoryItemAgingModel.getModelName());
+			cellModelName.setCellValue(inventoryItemAgingModel.getModelName());
-	    	Cell cellModelNumber = rowValues.createCell(3);
+			Cell cellModelNumber = rowValues.createCell(3);
-	    	cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());
+			cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());
-	    	Cell cellColor = rowValues.createCell(4);
+			Cell cellColor = rowValues.createCell(4);
-	    	cellColor.setCellValue(inventoryItemAgingModel.getColor());
+			cellColor.setCellValue(inventoryItemAgingModel.getColor());
-	    	Cell cellType = rowValues.createCell(5);
+			Cell cellType = rowValues.createCell(5);
-	    	cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());
+			cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());
-	    	List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
+			List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
-	    	//LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
+			// LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
-	    	for(int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2){
+			for (int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2) {
-	    		Cell cellPrice = rowValues.createCell(colIndex);
+				Cell cellPrice = rowValues.createCell(colIndex);
-	    		InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i);
+				InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i);
-	    		//LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);
+				// LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);
-	    		Cell cellQuantity = rowValues.createCell(colIndex + 1);
+				Cell cellQuantity = rowValues.createCell(colIndex + 1);
-	    		if(inventoryItemAgingValue != null){
+				if (inventoryItemAgingValue != null) {
-	    			cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
+					cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
-	    			cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
+					cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
-	    		}else{
+				} else {
-	    			cellPrice.setCellValue("-");
+					cellPrice.setCellValue("-");
-	    			cellQuantity.setCellValue("-");
+					cellQuantity.setCellValue("-");
-	    		}
+				}
-	    	}
+			}
-	    }
+		}
-		for(int index = 0; index < 8 + (intervals.size() * 2); index++){
+		for (int index = 0; index < 8 + (intervals.size() * 2); index++) {
 			sheet.autoSizeColumn(index);
+		}
-	    try{
+		try {
 			workbook.write(outputStream);
-	    	workbook.close();
+			workbook.close();
-	    }catch(IOException ioException){
+		} catch (IOException ioException) {
-	    	LOGGER.error("Unable to generate excel file", ioException);
+			LOGGER.error("Unable to generate excel file", ioException);
-	    }
+		}
+	}
-	public static void writeItemCompleteLedgerModels(List<ItemCompleteLedgerModel> itemCompleteLedgerModels, OutputStream outputStream){
+	public static void writeItemCompleteLedgerModels(List<ItemCompleteLedgerModel> itemCompleteLedgerModels,
+			OutputStream outputStream) {
 		SXSSFWorkbook workbook = new SXSSFWorkbook();
-		//CreationHelper createHelper = workbook.getCreationHelper();
+		// CreationHelper createHelper = workbook.getCreationHelper();
 		SXSSFSheet sheet = workbook.createSheet("ItemCompleteLeger");
 		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);
+		Row rowHeader = sheet.createRow(0);
-	    Cell cellItemIdHeader = rowHeader.createCell(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 cellOutwardsHeader = rowHeader.createCell(12);
 		cellOutwardsHeader.setCellValue("Outwards");
 		Cell cellClosingBalanceHeader = rowHeader.createCell(15);
 		cellClosingBalanceHeader.setCellValue("Closing Balance");
 		Row rowQuantityRateValue = sheet.createRow(1);
-		for(int index = 6; index < 18; index = index + 3){
+		for (int index = 6; index < 18; index = index + 3) {
 			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
 			cellQuantityHeader.setCellValue("Quantity");
 			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
 			cellRateHeader.setCellValue("Rate");
 			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
+		}
 		Font font = workbook.createFont();
 		CellStyle cellStyle = workbook.createCellStyle();
 		font.setBold(true);
 		cellStyle.setAlignment(HorizontalAlignment.CENTER);
-		//font.setFontHeight((short)16);
+		// font.setFontHeight((short)16);
 		cellStyle.setFont(font);
-		for(int i = 0; i < 18; i++){
+		for (int i = 0; i < 18; i++) {
-			if(rowHeader.getCell(i) != null){
+			if (rowHeader.getCell(i) != null) {
 				rowHeader.getCell(i).setCellStyle(cellStyle);
+			}
+		}
 		int openingQuantityTotal = 0;
 		float openingValueTotal = 0;
 		float inwardsValueTotal = 0;
 		int outwardsQuantityTotal = 0;
 		float outwardsValueTotal = 0;
 		int closingQuantityTotal = 0;
 		float closingValueTotal = 0;
-		for(int index = 0; index < itemCompleteLedgerModels.size(); index++){
+		for (int index = 0; index < itemCompleteLedgerModels.size(); index++) {
 			ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
-	    	Row rowValues = sheet.createRow(index + 2);
+			Row rowValues = sheet.createRow(index + 2);
-	    	Cell cellItemId = rowValues.createCell(0);
+			Cell cellItemId = rowValues.createCell(0);
-	    	cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
+			cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
-	    	Cell cellBrand = rowValues.createCell(1);
+			Cell cellBrand = rowValues.createCell(1);
-	    	cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
+			cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
-	    	Cell cellModelName = rowValues.createCell(2);
+			Cell cellModelName = rowValues.createCell(2);
-	    	cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
+			cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
-	    	Cell cellModelNumber = rowValues.createCell(3);
+			Cell cellModelNumber = rowValues.createCell(3);
-	    	cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
+			cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
-	    	Cell cellColor = rowValues.createCell(4);
+			Cell cellColor = rowValues.createCell(4);
-	    	cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
+			cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
-	    	Cell cellType = rowValues.createCell(5);
+			Cell cellType = rowValues.createCell(5);
-	    	cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
+			cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
-	    	Cell cellOpeningQuantity = rowValues.createCell(6);
+			Cell cellOpeningQuantity = rowValues.createCell(6);
-	    	Cell cellOpeningRate = rowValues.createCell(7);
+			Cell cellOpeningRate = rowValues.createCell(7);
-	    	Cell cellOpeningValue = rowValues.createCell(8);
+			Cell cellOpeningValue = rowValues.createCell(8);
-	    	if(itemCompleteLedgerModel.getOpeningLedger() == null){
+			if (itemCompleteLedgerModel.getOpeningLedger() == null) {
-	    		cellOpeningQuantity.setCellValue("-");
+				cellOpeningQuantity.setCellValue("-");
-	    		cellOpeningRate.setCellValue("-");
+				cellOpeningRate.setCellValue("-");
-	    		cellOpeningValue.setCellValue("-");
+				cellOpeningValue.setCellValue("-");
-	    	}else{
+			} else {
-	    		cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
+				cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
-	    		cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
+				cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
-	    		cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
+				cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
-	    		openingQuantityTotal = openingQuantityTotal + itemCompleteLedgerModel.getOpeningLedger().getQuantity();
+				openingQuantityTotal = openingQuantityTotal + itemCompleteLedgerModel.getOpeningLedger().getQuantity();
-	    		openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
+				openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
-	    	}
+			}
-	    	Cell cellInwardsQuantity = rowValues.createCell(9);
+			Cell cellInwardsQuantity = rowValues.createCell(9);
-	    	Cell cellInwardsRate = rowValues.createCell(10);
+			Cell cellInwardsRate = rowValues.createCell(10);
-	    	Cell cellInwardsValue = rowValues.createCell(11);
+			Cell cellInwardsValue = rowValues.createCell(11);
-	    	if(itemCompleteLedgerModel.getInwardsLedger() == null){
+			if (itemCompleteLedgerModel.getInwardsLedger() == null) {
-	    		cellInwardsQuantity.setCellValue("-");
+				cellInwardsQuantity.setCellValue("-");
-	    		cellInwardsRate.setCellValue("-");
+				cellInwardsRate.setCellValue("-");
-	    		cellInwardsValue.setCellValue("-");
+				cellInwardsValue.setCellValue("-");
-	    	}else{
+			} else {
-	    		cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
+				cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
-	    		cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
+				cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
-	    		cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
+				cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
-	    		inwardsQuantityTotal = inwardsQuantityTotal + itemCompleteLedgerModel.getInwardsLedger().getQuantity();
+				inwardsQuantityTotal = inwardsQuantityTotal + itemCompleteLedgerModel.getInwardsLedger().getQuantity();
-	    		inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
+				inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
-	    	}
+			}
-	    	Cell cellOutwardsQuantity = rowValues.createCell(12);
+			Cell cellOutwardsQuantity = rowValues.createCell(12);
-	    	Cell cellOutwardsRate = rowValues.createCell(13);
+			Cell cellOutwardsRate = rowValues.createCell(13);
-	    	Cell cellOutwardsValue = rowValues.createCell(14);
+			Cell cellOutwardsValue = rowValues.createCell(14);
-	    	if(itemCompleteLedgerModel.getOutwardsLedger() == null){
+			if (itemCompleteLedgerModel.getOutwardsLedger() == null) {
-	    		cellOutwardsQuantity.setCellValue("-");
+				cellOutwardsQuantity.setCellValue("-");
-	    		cellOutwardsRate.setCellValue("-");
+				cellOutwardsRate.setCellValue("-");
-	    		cellOutwardsValue.setCellValue("-");
+				cellOutwardsValue.setCellValue("-");
-	    	}else{
+			} else {
-	    		cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
+				cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
-	    		cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
+				cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
-	    		cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
+				cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
+				outwardsQuantityTotal = outwardsQuantityTotal
-	    		outwardsQuantityTotal = outwardsQuantityTotal + itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
+						+ itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
-	    		outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
+				outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
-	    	}
+			}
-	    	Cell cellClosingQuantity = rowValues.createCell(15);
+			Cell cellClosingQuantity = rowValues.createCell(15);
-	    	Cell cellClosingRate = rowValues.createCell(16);
+			Cell cellClosingRate = rowValues.createCell(16);
-	    	Cell cellClosingValue = rowValues.createCell(17);
+			Cell cellClosingValue = rowValues.createCell(17);
-	    	if(itemCompleteLedgerModel.getClosingLedger() == null){
+			if (itemCompleteLedgerModel.getClosingLedger() == null) {
-	    		cellClosingQuantity.setCellValue("-");
+				cellClosingQuantity.setCellValue("-");
-	    		cellClosingRate.setCellValue("-");
+				cellClosingRate.setCellValue("-");
-	    		cellClosingValue.setCellValue("-");
+				cellClosingValue.setCellValue("-");
-	    	}else{
+			} else {
-	    		cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
+				cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
-	    		cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
+				cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
-	    		cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
+				cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
-	    		closingQuantityTotal = closingQuantityTotal + itemCompleteLedgerModel.getClosingLedger().getQuantity();
+				closingQuantityTotal = closingQuantityTotal + itemCompleteLedgerModel.getClosingLedger().getQuantity();
-	    		closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
+				closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
-	    	}
+			}
-	    }
+		}
 		Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);
-		if(openingQuantityTotal > 0){
+		if (openingQuantityTotal > 0) {
 			Cell cellOpeningQuantityTotal = rowTotal.createCell(6);
 			cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);
 			Cell cellOpeningValueTotal = rowTotal.createCell(8);
 			cellOpeningValueTotal.setCellValue(openingValueTotal);
+		}
-		if(inwardsQuantityTotal > 0){
+		if (inwardsQuantityTotal > 0) {
 			Cell cellInwardsQuantityTotal = rowTotal.createCell(9);
 			cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);
 			Cell cellInwardsValueTotal = rowTotal.createCell(11);
 			cellInwardsValueTotal.setCellValue(inwardsValueTotal);
+		}
-		if(outwardsQuantityTotal > 0){
+		if (outwardsQuantityTotal > 0) {
 			Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);
 			cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);
 			Cell cellOutwardsValueTotal = rowTotal.createCell(14);
 			cellOutwardsValueTotal.setCellValue(outwardsValueTotal);
+		}
-		if(closingQuantityTotal > 0){
+		if (closingQuantityTotal > 0) {
 			Cell cellClosingQuantityTotal = rowTotal.createCell(15);
 			cellClosingQuantityTotal.setCellValue(closingQuantityTotal);
 			Cell cellClosingValueTotal = rowTotal.createCell(17);
 			cellClosingValueTotal.setCellValue(closingValueTotal);
+		}
-		for(int index = 0; index < 18; index++){
+		for (int index = 0; index < 18; index++) {
 			sheet.autoSizeColumn(index);
+		}
-	    try{
+		try {
 			workbook.write(outputStream);
-	    	workbook.close();
+			workbook.close();
-	    }catch(IOException ioException){
+		} catch (IOException ioException) {
-	    	LOGGER.error("Unable to generate excel file", ioException);
+			LOGGER.error("Unable to generate excel file", ioException);
-	    }
+		}
+	}
-	public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream){
+	public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream) {
 		SXSSFWorkbook workbook = new SXSSFWorkbook();
-		//CreationHelper createHelper = workbook.getCreationHelper();
+		// CreationHelper createHelper = workbook.getCreationHelper();
 		SXSSFSheet sheet = workbook.createSheet("Schemes");
 		sheet.trackAllColumnsForAutoSizing();
-	    Row rowHeader = sheet.createRow(0);
+		Row rowHeader = sheet.createRow(0);
-	    Cell cellSchemeIdHeader = rowHeader.createCell(0);
+		Cell cellSchemeIdHeader = rowHeader.createCell(0);
 		cellSchemeIdHeader.setCellValue("Scheme Id");
 		Cell cellNameHeader = rowHeader.createCell(1);
 		cellNameHeader.setCellValue("Name");
 		Cell cellDescriptionHeader = rowHeader.createCell(2);
 		cellDescriptionHeader.setCellValue("Description");
 		cellCreatedByHeader.setCellValue("Created By");
 		Cell cellItemIdsHeader = rowHeader.createCell(12);
 		cellItemIdsHeader.setCellValue("Item Ids");
 		Cell cellRetailerIdsHeader = rowHeader.createCell(13);
 		cellRetailerIdsHeader.setCellValue("Retailer Ids");
-		//Row rowQuantityRateValue = sheet.createRow(1);
+		// Row rowQuantityRateValue = sheet.createRow(1);
+		/*
-		/*for(int index = 6; index < 18; index = index + 3){
+		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
-			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
+		 * rowQuantityRateValue.createCell(index);
-			cellQuantityHeader.setCellValue("Quantity");
+		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
-			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
+		 * rowQuantityRateValue.createCell(index + 1);
-			cellRateHeader.setCellValue("Rate");
+		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
-			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
+		 * rowQuantityRateValue.createCell(index + 2);
-			cellValueHeader.setCellValue("Value");
+		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
-			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
+		 * CellRangeAddress(0, 0, index, index + 2)); }
-		}*/
+		 */
 		Font font = workbook.createFont();
 		CellStyle cellStyle = workbook.createCellStyle();
 		font.setBold(true);
 		cellStyle.setAlignment(HorizontalAlignment.CENTER);
-		//font.setFontHeight((short)16);
+		// font.setFontHeight((short)16);
 		cellStyle.setFont(font);
-		for(int i = 0; i < 14; i++){
+		for (int i = 0; i < 14; i++) {
-			if(rowHeader.getCell(i) != null){
+			if (rowHeader.getCell(i) != null) {
 				rowHeader.getCell(i).setCellStyle(cellStyle);
+			}
+		}
-		for(int index = 0; index < schemeModels.size(); index++){
+		for (int index = 0; index < schemeModels.size(); index++) {
 			SchemeModel schemeModel = schemeModels.get(index);
-			//ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
+			// ItemCompleteLedgerModel itemCompleteLedgerModel =
+			// itemCompleteLedgerModels.get(index);
-	    	Row rowValues = sheet.createRow(index + 1);
+			Row rowValues = sheet.createRow(index + 1);
-	    	Cell cellSchemeId = rowValues.createCell(0);
+			Cell cellSchemeId = rowValues.createCell(0);
 			cellSchemeId.setCellValue(schemeModel.getSchemeId());
 			Cell cellName = rowValues.createCell(1);
 			cellName.setCellValue(schemeModel.getName());
 			Cell cellDescription = rowValues.createCell(2);
 			cellDescription.setCellValue(schemeModel.getDescription());
 			Cell cellEndDateTime = rowValues.createCell(7);
 			cellEndDateTime.setCellValue(schemeModel.getEndDateTime());
 			Cell cellCreatedAt = rowValues.createCell(8);
 			cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());
 			Cell cellActive = rowValues.createCell(9);
-			if(schemeModel.getActiveTimestamp() !=null){
+			if (schemeModel.getActiveTimestamp() != null) {
 				cellActive.setCellValue(schemeModel.getActiveTimestamp());
-			}else{
+			} else {
 				cellActive.setCellValue("False");
+			}
 			Cell cellExpire = rowValues.createCell(10);
-			if(schemeModel.getExpireTimestamp() != null){
+			if (schemeModel.getExpireTimestamp() != null) {
 				cellExpire.setCellValue(schemeModel.getExpireTimestamp());
-			}else{
+			} else {
 				cellExpire.setCellValue("False");
+			}
 			Cell cellCreatedBy = rowValues.createCell(11);
 			cellCreatedBy.setCellValue(schemeModel.getCreatedBy());
 			Cell cellItemIds = rowValues.createCell(12);
 			cellItemIds.setCellValue(schemeModel.getItemStringMap().toString());
 			Cell cellRetailerIds = rowValues.createCell(13);
 			cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());
 			int maxHeight = Math.max(schemeModel.getItemStringMap().size(), schemeModel.getRetailerIds().size());
-			if(maxHeight > 1){
+			if (maxHeight > 1) {
-				rowValues.setHeight((short)(maxHeight * 240));
+				rowValues.setHeight((short) (maxHeight * 240));
+			}
-	    }
+		}
-		for(int index = 0; index < 14; index++){
+		for (int index = 0; index < 14; index++) {
 			sheet.autoSizeColumn(index);
+		}
-	    try{
+		try {
 			workbook.write(outputStream);
-	    	workbook.close();
+			workbook.close();
-	    }catch(IOException ioException){
+		} catch (IOException ioException) {
-	    	LOGGER.error("Unable to generate excel file", ioException);
+			LOGGER.error("Unable to generate excel file", ioException);
-	    }
+		}
+	}
-	public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId,int itemId,OutputStream outputStream){
+	public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId, int itemId, OutputStream outputStream) {
 		SXSSFWorkbook workbook = new SXSSFWorkbook();
-		//CreationHelper createHelper = workbook.getCreationHelper();
+		// CreationHelper createHelper = workbook.getCreationHelper();
 		SXSSFSheet sheet = workbook.createSheet("Schemes");
 		sheet.trackAllColumnsForAutoSizing();
-	    Row rowHeader = sheet.createRow(0);
+		Row rowHeader = sheet.createRow(0);
-	    Cell cellItemHeader = rowHeader.createCell(0);
+		Cell cellItemHeader = rowHeader.createCell(0);
-	    cellItemHeader.setCellValue("ITEMID");
+		cellItemHeader.setCellValue("ITEMID");
 		Cell cellIMEIHeader = rowHeader.createCell(1);
 		cellIMEIHeader.setCellValue("IMEI");
 		Cell cellFOFOIDHeader = rowHeader.createCell(2);
 		cellFOFOIDHeader.setCellValue("RETAILERNAME");
-		//Row rowQuantityRateValue = sheet.createRow(1);
+		// Row rowQuantityRateValue = sheet.createRow(1);
+		/*
-		/*for(int index = 6; index < 18; index = index + 3){
+		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
-			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
+		 * rowQuantityRateValue.createCell(index);
-			cellQuantityHeader.setCellValue("Quantity");
+		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
-			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
+		 * rowQuantityRateValue.createCell(index + 1);
-			cellRateHeader.setCellValue("Rate");
+		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
-			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
+		 * rowQuantityRateValue.createCell(index + 2);
-			cellValueHeader.setCellValue("Value");
+		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
-			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
+		 * CellRangeAddress(0, 0, index, index + 2)); }
-		}*/
+		 */
 		Font font = workbook.createFont();
 		CellStyle cellStyle = workbook.createCellStyle();
 		font.setBold(true);
 		cellStyle.setAlignment(HorizontalAlignment.CENTER);
-		//font.setFontHeight((short)16);
+		// font.setFontHeight((short)16);
 		cellStyle.setFont(font);
-		for(int i = 0; i < 14; i++){
+		for (int i = 0; i < 14; i++) {
-			if(rowHeader.getCell(i) != null){
+			if (rowHeader.getCell(i) != null) {
 				rowHeader.getCell(i).setCellStyle(cellStyle);
+			}
+		}
-		LinkedHashMap<String,String> priceAmount=new LinkedHashMap<>(priceDropIMEIfofoId);
+		LinkedHashMap<String, String> priceAmount = new LinkedHashMap<>(priceDropIMEIfofoId);
-		Set<String> keyset=priceAmount.keySet();
+		Set<String> keyset = priceAmount.keySet();
 		int rownum = 1;
 		for (String key : keyset) {
-            Row row = sheet.createRow(rownum++);
+			Row row = sheet.createRow(rownum++);
-            String objArr = priceAmount.get(key);
+			String objArr = priceAmount.get(key);
-            int cellnum = 0;
+			int cellnum = 0;
-            Cell cell = row.createCell(cellnum++);
+			Cell cell = row.createCell(cellnum++);
-            cell.setCellValue(itemId);
+			cell.setCellValue(itemId);
-            Cell cell1 = row.createCell(cellnum++);
+			Cell cell1 = row.createCell(cellnum++);
-            cell1.setCellValue(key);
+			cell1.setCellValue(key);
-            Cell cell2 = row.createCell(cellnum++);
+			Cell cell2 = row.createCell(cellnum++);
-            cell2.setCellValue(priceAmount.get(key));
+			cell2.setCellValue(priceAmount.get(key));
-            }
+		}
-		for(int index = 0; index < 14; index++){
+		for (int index = 0; index < 14; index++) {
 			sheet.autoSizeColumn(index);
+		}
-	    try{
+		try {
 			workbook.write(outputStream);
-	    	workbook.close();
+			workbook.close();
-	    }catch(IOException ioException){
+		} catch (IOException ioException) {
-	    	LOGGER.error("Unable to generate excel file", ioException);
+			LOGGER.error("Unable to generate excel file", ioException);
-	    }
+		}
+	}
-	public static void writePriceDropForAllIMEI(Map<String,String>priceDropAmount,OutputStream outputStream){
+	public static void writePriceDropForAllIMEI(Map<String, String> priceDropAmount, OutputStream outputStream) {
 		SXSSFWorkbook workbook = new SXSSFWorkbook();
-		//CreationHelper createHelper = workbook.getCreationHelper();
+		// CreationHelper createHelper = workbook.getCreationHelper();
 		SXSSFSheet sheet = workbook.createSheet("Schemes");
 		sheet.trackAllColumnsForAutoSizing();
-	    Row rowHeader = sheet.createRow(0);
+		Row rowHeader = sheet.createRow(0);
-	    Cell cellItemHeader = rowHeader.createCell(0);
+		Cell cellItemHeader = rowHeader.createCell(0);
-	    cellItemHeader.setCellValue("Itemdescription");
+		cellItemHeader.setCellValue("Itemdescription");
 		Cell cellIMEIHeader = rowHeader.createCell(1);
 		cellIMEIHeader.setCellValue("IMEI");
-		//Row rowQuantityRateValue = sheet.createRow(1);
+		// Row rowQuantityRateValue = sheet.createRow(1);
+		/*
-		/*for(int index = 6; index < 18; index = index + 3){
+		 * for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
-			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
+		 * rowQuantityRateValue.createCell(index);
-			cellQuantityHeader.setCellValue("Quantity");
+		 * cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
-			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
+		 * rowQuantityRateValue.createCell(index + 1);
-			cellRateHeader.setCellValue("Rate");
+		 * cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
-			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
+		 * rowQuantityRateValue.createCell(index + 2);
-			cellValueHeader.setCellValue("Value");
+		 * cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
-			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
+		 * CellRangeAddress(0, 0, index, index + 2)); }
-		}*/
+		 */
 		Font font = workbook.createFont();
 		CellStyle cellStyle = workbook.createCellStyle();
 		font.setBold(true);
 		cellStyle.setAlignment(HorizontalAlignment.CENTER);
-		//font.setFontHeight((short)16);
+		// font.setFontHeight((short)16);
 		cellStyle.setFont(font);
-		for(int i = 0; i < 14; i++){
+		for (int i = 0; i < 14; i++) {
-			if(rowHeader.getCell(i) != null){
+			if (rowHeader.getCell(i) != null) {
 				rowHeader.getCell(i).setCellStyle(cellStyle);
+			}
+		}
-		Set<String> keyset=priceDropAmount.keySet();
+		Set<String> keyset = priceDropAmount.keySet();
 		int rownum = 1;
 		for (String key : keyset) {
-            Row row = sheet.createRow(rownum++);
+			Row row = sheet.createRow(rownum++);
-            String objArr = priceDropAmount.get(key);
+			String objArr = priceDropAmount.get(key);
-            int cellnum = 0;
+			int cellnum = 0;
-            Cell cell = row.createCell(cellnum++);
+			Cell cell = row.createCell(cellnum++);
-            cell.setCellValue(priceDropAmount.get(key));
+			cell.setCellValue(priceDropAmount.get(key));
-            Cell cell1 = row.createCell(cellnum++);
+			Cell cell1 = row.createCell(cellnum++);
-            cell1.setCellValue(key);
+			cell1.setCellValue(key);
-            }
+		}
-		for(int index = 0; index < 14; index++){
+		for (int index = 0; index < 14; index++) {
 			sheet.autoSizeColumn(index);
+		}
-	    try{
+		try {
 			workbook.write(outputStream);
-	    	workbook.close();
+			workbook.close();
-	    }catch(IOException ioException){
+		} catch (IOException ioException) {
-	    	LOGGER.error("Unable to generate excel file", ioException);
+			LOGGER.error("Unable to generate excel file", ioException);
-	    }
+		}
+	}
-public static List<PartnerTargetModel> parseFromExcel(InputStream inputStream) throws Exception {
+	public static List<PartnerTargetModel> parseFromExcel(InputStream inputStream) throws Exception {
 		List<PartnerTargetModel> partnerTargetModels = new ArrayList<>();
 		XSSFWorkbook myWorkBook = null;
-		try{
+		try {
+			// FileInputStream fileInputStream = new
-			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
+			// FileInputStream("/home/ashikali/tag_listing1.xlsx");
-			myWorkBook = new XSSFWorkbook (inputStream);
+			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++){
+			for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
 				XSSFRow row = mySheet.getRow(rowNumber);
 				LOGGER.info("row {}", row);
 				PartnerTargetModel partnerTargetModel = new PartnerTargetModel();
-				if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){
-					partnerTargetModel.setFofoId((int) row.getCell(0).getNumericCellValue());
-				}else{
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(FOFO_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
-					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
-					throw profitMandiBusinessException;
-				}
-				if(row.getCell(1) != null && row.getCell(1).getCellTypeEnum() == CellType.STRING){
-					partnerTargetModel.setStoreName(row.getCell(1).getStringCellValue());
-				}else{
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(STORE_NAME, row.getCell(2).toString(), "TGLSTNG_VE_1010");
-					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
-					throw profitMandiBusinessException;
-				}
-				if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.STRING){
+				if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
-					partnerTargetModel.setEmail(row.getCell(2).getStringCellValue());
+					partnerTargetModel.setFofoId((int) row.getCell(0).getNumericCellValue());
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(EMAIL, row.getCell(7), "TGLSTNG_VE_1010");
+					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
+							FOFO_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
 					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 					throw profitMandiBusinessException;
+				}
-				if(row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC){
+				if (row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
 					partnerTargetModel.setTargetValue((int) row.getCell(3).getNumericCellValue());
-				}else{
+				} else {
-					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TARGET_VALUE, row.getCell(8), "TGLSTNG_VE_1010");
+					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
+							TARGET_VALUE, row.getCell(8), "TGLSTNG_VE_1010");
 					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 					throw profitMandiBusinessException;
+				}
 				partnerTargetModels.add(partnerTargetModel);
+			}
 			myWorkBook.close();
-		} catch(IOException ioException){
+		} catch (IOException ioException) {
 			ioException.printStackTrace();
-			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
+			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
+					"EXL_VE_1000");
 		} finally {
-			if(myWorkBook != null){
+			if (myWorkBook != null) {
 				try {
 					myWorkBook.close();
 				} catch (IOException e) {
 					// TODO Auto-generated catch block
 					e.printStackTrace();
+				}
+			}
+		}
 		return partnerTargetModels;
+	}
+	public static void writeDailySaleReportVsTarget(Map<Integer, Float> targetValues, Map<Integer, Double> saleValues,
+			Map<Integer, CustomRetailer> fofoIdsAndCustomRetailer, Map<Integer, String> fofoIdsAndSlabNames,
+			Map<Integer, Double> monthlyTargetAchievementPercentage, Map<Integer, Float> dailyAverageSale,
+			Map<Integer, Double> remainingMonthlyTargets, Map<Integer, Double> todayAchievements,
+			Map<Integer, Float> todayAchievementsPercentage, OutputStream outputStream) {
+		SXSSFWorkbook workbook = new SXSSFWorkbook();
+		// CreationHelper createHelper = workbook.getCreationHelper();
+		SXSSFSheet sheet = workbook.createSheet("DailySaleReports");
+		sheet.trackAllColumnsForAutoSizing();
+		Row rowHeader = sheet.createRow(0);
+		Cell cellFOfoId = rowHeader.createCell(0);
+		cellFOfoId.setCellValue("FoFoId");
+		Cell cellStoreName = rowHeader.createCell(1);
+		cellStoreName.setCellValue("Store Name");
+		Cell cellTargetValue = rowHeader.createCell(2);
+		cellTargetValue.setCellValue("MTD Target Value");
+		Cell cellDailyTarget = rowHeader.createCell(3);
+		cellDailyTarget.setCellValue("Daily Target");
+		Cell cellTodayAchievement = rowHeader.createCell(4);
+		cellTodayAchievement.setCellValue("Today Achievement");
+		Cell cellTodayAchievementPercentage = rowHeader.createCell(5);
+		cellTodayAchievementPercentage.setCellValue("Today Achievement %");
+		Cell cellMonthlySaleValue = rowHeader.createCell(6);
+		cellMonthlySaleValue.setCellValue("MTD Achievement");
+		Cell cellMonthlySaleValuePercentage = rowHeader.createCell(7);
+		cellMonthlySaleValuePercentage.setCellValue("MTD Achievement%");
+		Cell cellRemainingTarget = rowHeader.createCell(8);
+		cellRemainingTarget.setCellValue("Remaining");
+		Cell cellEligibility = rowHeader.createCell(9);
+		cellEligibility.setCellValue("Eligibility");
+		Font font = workbook.createFont();
+		CellStyle cellStyle = workbook.createCellStyle();
+		font.setBold(true);
+		cellStyle.setAlignment(HorizontalAlignment.CENTER);
+		// font.setFontHeight((short)16);
+		cellStyle.setFont(font);
+		for (int i = 0; i < 14; i++) {
+			if (rowHeader.getCell(i) != null) {
+				rowHeader.getCell(i).setCellStyle(cellStyle);
+			}
+		}
+		int rownum = 1;
+		for (Integer fofoId : saleValues.keySet()) {
+			Row row = sheet.createRow(rownum++);
+			int cellnum = 0;
+			Cell cellFofoId = row.createCell(cellnum++);
+			cellFofoId.setCellValue(fofoId);
+			Cell cellPartner = row.createCell(cellnum++);
+			if (fofoIdsAndCustomRetailer.get(fofoId)!= null) {
+				cellPartner.setCellValue(fofoIdsAndCustomRetailer.get(fofoId).getBusinessName());
+			} else {
+				cellPartner.setCellValue("Name Not Associated");
+			}
+			Cell cellMTDTargetValue = row.createCell(cellnum++);
+			if (targetValues.get(fofoId) != null) {
+				cellMTDTargetValue.setCellValue(targetValues.get(fofoId));
+			} else {
+				cellMTDTargetValue.setCellValue("Target not defined");
+			}
+			Cell cellDailyTargetValue = row.createCell(cellnum++);
+			if (dailyAverageSale.get(fofoId) != null) {
+				String formatting=FormattingUtils.formatDecimalTwoDigits(dailyAverageSale.get(fofoId));
+				cellDailyTargetValue.setCellValue(formatting);
+			} else {
+				cellDailyTargetValue.setCellValue(0);
+			}
+			Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);
+			if (todayAchievements.get(fofoId) != null) {
+				cellTodayAchieveMentSaleValue.setCellValue(todayAchievements.get(fofoId));
+			} else {
+				cellTodayAchieveMentSaleValue.setCellValue(0);
+			}
+			Cell cellTodayAchieveMentSaleValuePercentage = row.createCell(cellnum++);
+			if (todayAchievementsPercentage.get(fofoId) != null) {
+				cellTodayAchieveMentSaleValuePercentage.setCellValue(todayAchievementsPercentage.get(fofoId) + "%");
+			} else {
+				cellTodayAchieveMentSaleValuePercentage.setCellValue(0 + "%");
+			}
+			Cell cellMTDAchievement = row.createCell(cellnum++);
+			if (saleValues.get(fofoId) != null) {
+				cellMTDAchievement.setCellValue(saleValues.get(fofoId));
+			} else {
+				cellMTDAchievement.setCellValue("No sale");
+			}
+			Cell cellMTDAchievementPercentage = row.createCell(cellnum++);
+			if (monthlyTargetAchievementPercentage.get(fofoId) != null) {
+				cellMTDAchievementPercentage.setCellValue(monthlyTargetAchievementPercentage.get(fofoId) + "%");
+			} else {
+				cellMTDAchievementPercentage.setCellValue(0 + "%");
+			}
+			Cell cellRemaining = row.createCell(cellnum++);
+			if (remainingMonthlyTargets.get(fofoId) != null) {
+				cellRemaining.setCellValue(remainingMonthlyTargets.get(fofoId));
+			} else {
+				cellRemaining.setCellValue("no target define");
+			}
+			Cell cellEligible = row.createCell(cellnum++);
+			if (fofoIdsAndSlabNames.get(fofoId) != null) {
+				cellEligible.setCellValue(fofoIdsAndSlabNames.get(fofoId));
+			} else {
+				cellEligible.setCellValue("No slab");
+			}
+		}
+		for (int index = 0; index < saleValues.size(); index++) {
+			sheet.autoSizeColumn(index);
+		}
+		try {
+			workbook.write(outputStream);
+			workbook.close();
+		} catch (IOException ioException) {
+			LOGGER.error("Unable to generate excel file", ioException);
+		}
+	}
+}

Subversion Repositories SmartDukaan

(root)/trunk/profitmandi-common/src/main/java/com/spice/profitmandi/common/util/ExcelUtils.java – Rev 24107 → 24119