com.vsquaresystem.safedeals.rawmarketprice.RawMarketPriceService.java Source code

Java tutorial

Introduction

Here is the source code for com.vsquaresystem.safedeals.rawmarketprice.RawMarketPriceService.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.vsquaresystem.safedeals.rawmarketprice;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.vsquaresystem.safedeals.util.AttachmentUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import javax.swing.JFrame;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
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 org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

@Service
@Transactional
public class RawMarketPriceService {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private RawMarketPriceDAL rawMarketPriceDAL;

    @Autowired
    private AttachmentUtils attachmentUtils;

    @Transactional(readOnly = false)
    public Boolean insertAttachments(MultipartFile attachmentMultipartFile)
            throws JsonProcessingException, IOException {
        logger.info("attachmentMultipartFile in service Line31{}", attachmentMultipartFile);
        System.out.println("attachmentMultipartFile" + attachmentMultipartFile);
        File outputFile = attachmentUtils.storeAttachmentByAttachmentType(
                attachmentMultipartFile.getOriginalFilename(), attachmentMultipartFile.getInputStream(),
                AttachmentUtils.AttachmentType.RAW_MARKET_PRICE);
        return outputFile.exists();
    }

    ;

    public class Result {

        String response;
    }

    public Vector read() throws IOException {

        File excelFile = attachmentUtils
                .getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.RAW_MARKET_PRICE);
        File[] listofFiles = excelFile.listFiles();
        String fileName = excelFile + "/" + listofFiles[0].getName();

        Vector cellVectorHolder = new Vector();
        int type;
        try {
            FileInputStream myInput = new FileInputStream(fileName);

            XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);
            XSSFSheet mySheet = myWorkBook.getSheetAt(0);
            Iterator rowIter = mySheet.rowIterator();
            while (rowIter.hasNext()) {
                XSSFRow myRow = (XSSFRow) rowIter.next();
                Iterator cellIter = myRow.cellIterator();

                List list = new ArrayList();
                while (cellIter.hasNext()) {
                    XSSFCell myCell = (XSSFCell) cellIter.next();
                    if (myCell != null) {
                        switch (myCell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.println(new DataFormatter().formatCellValue(myCell));
                            list.add(new DataFormatter().formatCellValue(myCell));
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.println(new DataFormatter().formatCellValue(myCell));
                            list.add(new DataFormatter().formatCellValue(myCell));
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.println(new DataFormatter().formatCellValue(myCell));
                            list.add(new DataFormatter().formatCellValue(myCell));
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            System.out.println(new DataFormatter().formatCellValue(myCell));
                            list.add(new DataFormatter().formatCellValue(myCell));
                            break;

                        case Cell.CELL_TYPE_FORMULA:
                            break;
                        }
                    }

                }

                cellVectorHolder.addElement(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cellVectorHolder;

    }

    public Boolean checkExistingData() throws IOException {

        Boolean a = true;
        Result result = new Result();
        result.response = "true";
        Vector checkCellVectorHolder = read();

        int excelSize = checkCellVectorHolder.size() - 1;

        List<RawMarketPrice> rs = rawMarketPriceDAL.getAll();
        JFrame parent = new JFrame();

        int listSize = rs.size();

        if (excelSize == listSize || excelSize > listSize) {

        } else {
            System.out.println("No selected");

        }
        return true;
    }

    public boolean saveExcelToDatabase() throws IOException {

        Vector dataHolder = read();
        rawMarketPriceDAL.truncateAll();
        dataHolder.remove(0);

        RawMarketPrice rawMarketPrice = new RawMarketPrice();
        String id = "";
        String cityId = "";
        String locationName = "";
        String year = "";
        String month = "";
        String mpAgriLandLowest = "";
        String mpAgriLandHighest = "";
        String mpPlotLowest = "";
        String mpPlotHighest = "";
        String mpResidentialLowest = "";
        String mpResidentialHighest = "";
        String mpCommercialLowest = "";
        String mpCommercialHighest = "";
        String sdZoneId = "";
        String locationTypeId = "";
        String locationCategories = "";
        String description = "";
        String majorApproachRoad = "";
        String sourceOfWater = "";
        String publicTransport = "";
        String advantage = "";
        String disadvantage = "";
        String population = "";
        String migrationRate = "";
        String isCommercialCenter = "";
        System.out.println(dataHolder);
        DataFormatter formatter = new DataFormatter();
        for (Iterator iterator = dataHolder.iterator(); iterator.hasNext();) {
            List list = (List) iterator.next();
            logger.info("list", list);
            cityId = list.get(1).toString();
            locationName = list.get(2).toString();
            year = list.get(3).toString();
            month = list.get(4).toString();
            mpAgriLandLowest = list.get(5).toString();
            mpAgriLandHighest = list.get(6).toString();
            mpPlotLowest = list.get(7).toString();
            mpPlotHighest = list.get(8).toString();
            mpResidentialLowest = list.get(9).toString();
            mpResidentialHighest = list.get(10).toString();
            mpCommercialLowest = list.get(11).toString();
            mpCommercialHighest = list.get(12).toString();
            sdZoneId = list.get(13).toString();
            locationTypeId = list.get(14).toString();
            locationCategories = list.get(15).toString();
            description = list.get(16).toString();
            majorApproachRoad = list.get(17).toString();
            sourceOfWater = list.get(18).toString();
            publicTransport = list.get(19).toString();
            advantage = list.get(20).toString();
            disadvantage = list.get(21).toString();
            population = list.get(22).toString();
            migrationRate = list.get(23).toString();
            isCommercialCenter = list.get(24).toString();
            List<String> strList = new ArrayList<String>(Arrays.asList(locationCategories.split(",")));
            List<Integer> numberList = new ArrayList<Integer>();
            for (String number : strList) {
                numberList.add(Integer.parseInt(number));
            }

            try {
                rawMarketPrice.setCityId(Integer.parseInt(cityId));
                rawMarketPrice.setLocationName(locationName);
                rawMarketPrice.setYear(Integer.parseInt(year));
                rawMarketPrice.setMonth(Integer.parseInt(month));
                rawMarketPrice.setMpAgriLandLowest(Double.parseDouble(mpAgriLandLowest));
                rawMarketPrice.setMpAgriLandHighest(Double.parseDouble(mpAgriLandHighest));
                rawMarketPrice.setMpPlotLowest(Double.parseDouble(mpPlotLowest));
                rawMarketPrice.setMpPlotHighest(Double.parseDouble(mpPlotHighest));
                rawMarketPrice.setMpResidentialLowest(Double.parseDouble(mpResidentialLowest));
                rawMarketPrice.setMpResidentialHighest(Double.parseDouble(mpResidentialHighest));
                rawMarketPrice.setMpCommercialLowest(Double.parseDouble(mpCommercialLowest));
                rawMarketPrice.setMpCommercialHighest(Double.parseDouble(mpCommercialHighest));
                rawMarketPrice.setSafedealZoneId(Integer.parseInt(sdZoneId));
                rawMarketPrice.setLocationTypeId(Integer.parseInt(locationTypeId));
                rawMarketPrice.setLocationCategories(numberList);
                rawMarketPrice.setDescription(description);
                rawMarketPrice.setMajorApproachRoad(majorApproachRoad);
                rawMarketPrice.setSourceOfWater(sourceOfWater);
                rawMarketPrice.setPublicTransport(publicTransport);
                rawMarketPrice.setAdvantage(advantage);
                rawMarketPrice.setDisadvantage(disadvantage);
                rawMarketPrice.setPopulation(Integer.parseInt(population));
                rawMarketPrice.setMigrationRate(Integer.parseInt(migrationRate));
                rawMarketPrice.setIsCommercialCenter((Boolean.parseBoolean(isCommercialCenter)));
                rawMarketPriceDAL.insert(rawMarketPrice);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        File excelFile = attachmentUtils
                .getDirectoryByAttachmentType(AttachmentUtils.AttachmentType.RAW_MARKET_PRICE);
        FileUtils.cleanDirectory(excelFile);
        return true;

    }

    private void writeBook(RawMarketPrice rawMarketPrice, Row row) {
        Cell cell = row.createCell(1);
        cell.setCellValue(rawMarketPrice.getId());

        cell = row.createCell(2);
        cell.setCellValue(rawMarketPrice.getCityId());

        cell = row.createCell(3);
        cell.setCellValue(rawMarketPrice.getLocationName());

        cell = row.createCell(4);
        cell.setCellValue(rawMarketPrice.getYear());

        cell = row.createCell(5);
        cell.setCellValue(rawMarketPrice.getMonth());

        cell = row.createCell(6);
        cell.setCellValue(rawMarketPrice.getMpAgriLandLowest());

        cell = row.createCell(7);
        cell.setCellValue(rawMarketPrice.getMpAgriLandHighest());

        cell = row.createCell(8);
        cell.setCellValue(rawMarketPrice.getMpPlotLowest());

        cell = row.createCell(9);
        cell.setCellValue(rawMarketPrice.getMpPlotHighest());

        cell = row.createCell(10);
        cell.setCellValue(rawMarketPrice.getMpResidentialLowest());

        cell = row.createCell(11);
        cell.setCellValue(rawMarketPrice.getMpResidentialHighest());

        cell = row.createCell(12);
        cell.setCellValue(rawMarketPrice.getMpCommercialLowest());

        cell = row.createCell(13);
        cell.setCellValue(rawMarketPrice.getMpCommercialHighest());

        cell = row.createCell(14);
        cell.setCellValue(rawMarketPrice.getSafedealZoneId());

        cell = row.createCell(15);
        cell.setCellValue(rawMarketPrice.getLocationTypeId());

        cell = row.createCell(16);
        cell.setCellValue("1,2,3");

        cell = row.createCell(17);
        cell.setCellValue(rawMarketPrice.getDescription());

        cell = row.createCell(18);
        cell.setCellValue(rawMarketPrice.getMajorApproachRoad());

        cell = row.createCell(19);
        cell.setCellValue(rawMarketPrice.getSourceOfWater());

        cell = row.createCell(20);
        cell.setCellValue(rawMarketPrice.getPublicTransport());

        cell = row.createCell(21);
        cell.setCellValue(rawMarketPrice.getAdvantage());

        cell = row.createCell(22);
        cell.setCellValue(rawMarketPrice.getDisadvantage());

        cell = row.createCell(23);
        cell.setCellValue(rawMarketPrice.getPopulation());

        cell = row.createCell(24);
        cell.setCellValue(rawMarketPrice.getMigrationRate());

        cell = row.createCell(25);
        cell.setCellValue(rawMarketPrice.getIsCommercialCenter());

    }

    public Boolean exportExcel() throws IOException {
        logger.info("getExportExcel method is working for raw market price");
        List<RawMarketPrice> rmp = rawMarketPriceDAL.findAllRawMarketPrice();
        //        logger.info("result set excel sop" + rmp);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Raw Market Price Master");
        XSSFRow xssfrow = sheet.createRow((short) 0);
        xssfrow.createCell(1);
        xssfrow.createCell(2);
        xssfrow.createCell(3);
        xssfrow.createCell(4);
        xssfrow.createCell(5);
        xssfrow.createCell(6);
        xssfrow.createCell(7);
        xssfrow.createCell(8);
        xssfrow.createCell(9);
        xssfrow.createCell(10);
        xssfrow.createCell(11);
        xssfrow.createCell(12);
        xssfrow.createCell(13);
        xssfrow.createCell(14);
        xssfrow.createCell(15);
        xssfrow.createCell(16);
        xssfrow.createCell(17);
        xssfrow.createCell(18);
        xssfrow.createCell(19);
        xssfrow.createCell(20);
        xssfrow.createCell(21);
        xssfrow.createCell(22);
        xssfrow.createCell(23);
        xssfrow.createCell(24);
        xssfrow.createCell(25);
        xssfrow.getCell(1).setCellValue("Id");
        xssfrow.getCell(2).setCellValue("City Name");
        xssfrow.getCell(3).setCellValue("Location Name");
        xssfrow.getCell(4).setCellValue("Year");
        xssfrow.getCell(5).setCellValue("Month");
        xssfrow.getCell(6).setCellValue("MP Agri Land Lowest");
        xssfrow.getCell(7).setCellValue("MP Agri Land Highest");
        xssfrow.getCell(8).setCellValue("MP Plot Lowest");
        xssfrow.getCell(9).setCellValue("MP Plot Highest");
        xssfrow.getCell(10).setCellValue("MP Residential Lowest");
        xssfrow.getCell(11).setCellValue("MP Residential Highest");
        xssfrow.getCell(12).setCellValue("MP Commercial Lowest");
        xssfrow.getCell(13).setCellValue("MP Commercial Highest");
        xssfrow.getCell(14).setCellValue("Safedeal Zone Id");
        xssfrow.getCell(15).setCellValue("Location Type Id");
        xssfrow.getCell(16).setCellValue("Location Categories");
        xssfrow.getCell(17).setCellValue("Description");
        xssfrow.getCell(18).setCellValue("Major Approach Road");
        xssfrow.getCell(19).setCellValue("Source of Water");
        xssfrow.getCell(20).setCellValue("Public Transport");
        xssfrow.getCell(21).setCellValue("Advantage");
        xssfrow.getCell(22).setCellValue("Disadvantage");
        xssfrow.getCell(23).setCellValue("Population");
        xssfrow.getCell(24).setCellValue("Migration Rate");
        xssfrow.getCell(25).setCellValue("Commercial Center");

        String fileName = "/RawMarketPriceMasterData.xls";
        String exportPath = attachmentUtils.getRawMarketPriceExportAttachmentRootDirectory() + fileName;

        logger.info("exportPath" + exportPath);

        int rowCount = 0;

        for (RawMarketPrice aRawMarketPrice : rmp) {
            Row row = sheet.createRow(++rowCount);
            writeBook(aRawMarketPrice, row);
        }

        try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
            workbook.write(outputStream);
        }

        return true;
    }
}