com.infovity.iep.loader.util.SupplierLoaderUtil.java Source code

Java tutorial

Introduction

Here is the source code for com.infovity.iep.loader.util.SupplierLoaderUtil.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.infovity.iep.loader.util;

import com.infovity.iep.util.IepDbUtil;
import com.infovity.iep.util.IepUtil;
//import com.infovity.iep.util.JDBCSingleton;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Random;
import java.util.Set;
import javax.servlet.http.HttpSession;
import org.apache.commons.collections4.ListUtils;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author SRAVAN KUMAR T
 */
public class SupplierLoaderUtil {
    static IepDbUtil iepDbUtil = new IepDbUtil();
    static private Connection connect = null;
    static private Statement statement = null;
    static HttpSession session = IepUtil.getHttpSession();
    static String empId = (String) session.getAttribute("loggedInEmpID");
    static String loginTime = (String) session.getAttribute("loggedInTime");
    static String userName = (String) session.getAttribute("loggedInUser");

    //To PROCESS SUPPLIER SHEET.
    public boolean processSupplierSheet(File file) throws Exception {
        ArrayList<String[]> data = getDataFromFile(file, "Consolidated Supplier Import");
        ArrayList<String> queries = getSupplierInsertScript(data);
        boolean success = executeBatchQueries(queries);
        return success;
    }

    public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) {
        ArrayList<String[]> data = new ArrayList<String[]>();
        File selectedFile = null;
        FileInputStream fis = null;
        ;
        XSSFWorkbook workbook = null;
        //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx");
        boolean sheetFound = false;
        XSSFSheet sheet = null;
        try {

            int sheetIndex = -1;
            fis = new FileInputStream(inputFile);
            workbook = new XSSFWorkbook(fis);

            int noOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < noOfSheets; i++) {
                sheet = workbook.getSheetAt(i);
                if (sheet.getSheetName().equals(sheetName)) {
                    sheetFound = true;
                    sheetIndex = i;
                    selectedFile = inputFile;
                    break;
                }
            }
            XSSFWorkbook myWorkBook;

            try {
                myWorkBook = new XSSFWorkbook(selectedFile);
                // Return first sheet from the XLSX workbook
                // XSSFSheet mySheet = myWorkBook.getSheetAt(0);
                // Get iterator to all the rows in current sheet
                Iterator<Row> rowIterator = sheet.iterator();
                String query;
                String[] values = null;
                // Traversing over each row of XLSX file
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    if (rowHasData(row) && (row.getRowNum() >= 9)) {
                        int endColumn = row.getLastCellNum();
                        int startColumn = row.getFirstCellNum();
                        // For each row, iterate through each columns
                        values = new String[endColumn + 2];
                        for (int i = startColumn; i < endColumn; i++) {
                            String cellValue;
                            Cell cell = row.getCell(i);
                            values[0] = Integer.toString(row.getRowNum() + 1);
                            if (cell != null) {
                                if (cell.getCellType() == cell.CELL_TYPE_NUMERIC
                                        && DateUtil.isCellDateFormatted(cell)) {
                                    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                                    Date cellDateValue = cell.getDateCellValue();
                                    cellValue = df.format(cellDateValue);
                                } else {
                                    cell.setCellType(cell.CELL_TYPE_STRING);
                                    cellValue = cell.getStringCellValue().replaceAll("'", "");
                                }
                                if (!"".equals(cellValue) && cellValue != null) {
                                    values[i + 1] = cellValue;
                                } else if (cellValue.isEmpty() || "".equals(cellValue)) {
                                    values[i + 1] = "";
                                }
                            } else {
                                values[i + 1] = "";
                            }
                        }
                        data.add(values);
                    }

                }
            } catch (InvalidFormatException ex) {
                Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
            }
        } catch (IOException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        } finally {
            try {
                fis.close();
                workbook.close();
            } catch (IOException ex) {
                Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
            }
        }

        return data;
    }

    public static boolean rowHasData(Row row) {
        short cellNumber;
        boolean nonBlankRowFound = false;
        for (cellNumber = row.getFirstCellNum(); cellNumber <= row.getLastCellNum(); cellNumber++) {
            Cell cell = row.getCell(cellNumber);
            if (cell != null && row.getCell(cellNumber).getCellType() != cell.CELL_TYPE_BLANK) {
                nonBlankRowFound = true;
            }
        }
        return nonBlankRowFound;
    }

    public static ArrayList<String> getSupplierInsertScript(ArrayList<String[]> data) {
        String batchNumber = loginTime + empId;
        String createdBy = userName;
        String updatedBy = userName;
        String query = null;
        Set<Integer> set = getUniqueNumbersSet(data.size() * 7);
        int uniqueNumber = getUniqueNumber(set);
        //String[] value = new String[2];
        ArrayList<String> list = new ArrayList<String>();

        String primaryKeyForSupplierImportInterFace = "";
        String primaryKeyForSuppAdd = "";
        String primaryKeyForSuppSite = "";
        String primaryKeyForSuppPayee = "";
        String primaryKeyForSuppBankAccnts = "";
        String primaryKeyForSuppSiteAssign = "";
        String primaryKeyForSuppBankAccntAssgn = "";
        String previousValue = "";
        String previousSuppAdd = "";
        Iterator<String[]> iterator = data.iterator();
        Date date = new Date(System.currentTimeMillis());
        SimpleDateFormat formatter = new SimpleDateFormat("ddMMYYHHmmss");
        String formatted = formatter.format(date);
        while (iterator.hasNext()) {
            String[] val = iterator.next();
            if ((val[3] != null) && !val[3].equals("") && !val[3].equals(previousValue)) {
                query = "INSERT INTO SUPPLIER_IMPORT_INTERFACE VALUES(";
                uniqueNumber = getUniqueNumber(set);
                primaryKeyForSupplierImportInterFace = formatted + uniqueNumber;
                query = query + primaryKeyForSupplierImportInterFace + ",'" + val[0] + "'," + "'" + batchNumber
                        + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'";
                for (int i = 2; i < 150; i++) {
                    query = query + val[i] + "','";
                }
                query = query.substring(0, query.length() - 2) + ")";
                //System.out.println(query);
                list.add(query);
            }
            previousValue = val[3];
            //            if(val[150] == null || val[150].equals("")){
            //                primaryKeyForSuppAdd = null;
            //            }else{
            //                
            //            }
            if ((val[150] != null) && !val[150].equals("")) {// && !val[150].equals(previousSuppAdd)
                query = "INSERT INTO SUPP_ADD VALUES(";
                uniqueNumber = getUniqueNumber(set);
                primaryKeyForSuppAdd = formatted + uniqueNumber;
                query = query + primaryKeyForSuppAdd + "," + primaryKeyForSupplierImportInterFace + ",'" + val[0]
                        + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'";
                //code to insert foriegn key and remaining.
                for (int i = 150; i < 240; i++) {
                    query = query + val[i] + "','";
                }
                query = query.substring(0, query.length() - 2) + ")";
                //                System.out.println(query);
                list.add(query);
            }
            previousSuppAdd = val[150];
            String previousSuppSite = val[240];
            if ((previousSuppSite != null) && !previousSuppSite.equals("")) {
                if (previousSuppAdd == null || previousSuppAdd.equals("")) {
                    primaryKeyForSuppAdd = null;
                }
                query = "INSERT INTO SUPPLIER_SITE VALUES(";
                uniqueNumber = getUniqueNumber(set);
                primaryKeyForSuppSite = formatted + uniqueNumber;
                query = query + primaryKeyForSuppSite + "," + primaryKeyForSuppAdd + ",'" + val[0] + "'," + "'"
                        + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'";
                //code to insert foriegn key and remaining.
                for (int i = 240; i < 433; i++) {
                    query = query + val[i] + "','";
                }
                query = query.substring(0, query.length() - 2) + ")";
                //                System.out.println(query);
                list.add(query);
            }
            previousSuppSite = val[240];
            query = "INSERT INTO SUPP_SITE_ASSGN VALUES(";
            uniqueNumber = getUniqueNumber(set);
            primaryKeyForSuppSiteAssign = formatted + uniqueNumber;
            query = query + primaryKeyForSuppSiteAssign + "," + primaryKeyForSuppSite + ",'" + val[0] + "'," + "'"
                    + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'";
            //code to insert foriegn key and remaining.
            for (int i = 433; i < 445; i++) {
                query = query + val[i] + "','";
            }
            query = query.substring(0, query.length() - 2) + ")";
            //            System.out.println(query);
            list.add(query);
            String previousPayeeId = val[446];
            if ((previousPayeeId != null) && !previousPayeeId.equals("")) {
                query = "INSERT INTO SUPP_PAYEE VALUES(";
                uniqueNumber = getUniqueNumber(set);
                primaryKeyForSuppPayee = formatted + uniqueNumber;
                query = query + primaryKeyForSuppPayee + "," + primaryKeyForSuppSite + ","
                        + primaryKeyForSupplierImportInterFace + ",'" + previousValue + "','" + val[0] + "'," + "'"
                        + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'";
                //code to insert foriegn key and remaining.
                for (int i = 445; i < 466; i++) {
                    query = query + val[i] + "','";
                }
                query = query.substring(0, query.length() - 2) + ")";
                //                System.out.println(query);
                list.add(query);
            }
            previousPayeeId = val[446];
            String previousPayeeBankAcId = val[468];
            if ((previousPayeeBankAcId != null) && !previousPayeeBankAcId.equals("")
                    && !primaryKeyForSuppPayee.equals("")) {
                query = "INSERT INTO SUPP_BANK_ACCNTS VALUES(";
                uniqueNumber = getUniqueNumber(set);
                primaryKeyForSuppBankAccnts = formatted + uniqueNumber;
                query = query + primaryKeyForSuppBankAccnts + "," + primaryKeyForSuppPayee + ",'" + val[0] + "',"
                        + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'";
                //code to insert foriegn key and remaining.
                for (int i = 466; i < 505; i++) {
                    query = query + val[i] + "','";
                }
                query = query.substring(0, query.length() - 2) + ")";
                //                    System.out.println(query);
                list.add(query);

                query = "INSERT INTO SUPP_BANK_ACCNT_ASSGN VALUES(";
                uniqueNumber = getUniqueNumber(set);
                primaryKeyForSuppBankAccntAssgn = formatted + uniqueNumber;
                // primaryKeyForSuppBankAccnts;
                query = query + primaryKeyForSuppBankAccntAssgn + "," + primaryKeyForSuppBankAccnts + ",'" + val[0]
                        + "'," + "'" + batchNumber + "','" + createdBy + "',NOW(),'" + updatedBy + "',NOW(),'";
                //code to insert foriegn key and remaining.
                for (int i = 505; i < 512; i++) {
                    query = query + val[i] + "','";
                }
                query = query.substring(0, query.length() - 2) + ")";
                //                    System.out.println(query);
                list.add(query);
            }
            previousPayeeBankAcId = val[468];
        }
        return list;
    }

    public static Set<Integer> getUniqueNumbersSet(int numberOfUniquekeys) {
        Set<Integer> set = new HashSet<Integer>();
        while (set.size() < numberOfUniquekeys) {
            set.add(gen());
        }
        return set;
    }

    public static int gen() {
        Random r = new Random(System.currentTimeMillis());
        return ((1 + r.nextInt(2)) * 10000 + r.nextInt(10000));
    }

    public static int getUniqueNumber(Set<Integer> set) {
        Integer n = null;
        Iterator itr = set.iterator();
        while (itr.hasNext()) {
            n = (Integer) itr.next();
        }
        set.remove(n);
        return n;
    }

    public static boolean executeBatchQueries(List<String> queries) {
        boolean success = true;
        try {
            connect = iepDbUtil.getConnection();

            // Statements allow to issue SQL queries to the database
            List<List<String>> bigList = ListUtils.partition(queries, 20);
            for (List<String> subList : bigList) {
                statement = connect.createStatement();
                for (String query : subList) {
                    statement.addBatch(query);
                }
                statement.executeBatch();
            }
        } catch (Exception e) {
            java.util.logging.Logger.getLogger(SupplierLoaderUtil.class.getName())
                    .log(java.util.logging.Level.SEVERE, null, e + "EXCEPTION DURING ");
            success = false;
            e.printStackTrace();
        } finally {
            try {
                connect.close();
                statement.close();
            } catch (SQLException ex) {
                java.util.logging.Logger.getLogger(SupplierLoaderUtil.class.getName())
                        .log(java.util.logging.Level.SEVERE, null, ex);
            }
        }
        return success;
    }
}