com.krawler.spring.importFunctionality.ImportUtil.java Source code

Java tutorial

Introduction

Here is the source code for com.krawler.spring.importFunctionality.ImportUtil.java

Source

/*
 * Copyright (C) 2012  Krawler Information Systems Pvt Ltd
 * All rights reserved.
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/
package com.krawler.spring.importFunctionality;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.krawler.common.admin.ColumnHeader;
import com.krawler.common.admin.DefaultHeader;
import com.krawler.common.admin.FieldParams;
import com.krawler.common.admin.ImportLog;
import com.krawler.common.admin.KWLDateFormat;
import com.krawler.common.admin.Modules;
import com.krawler.common.service.ServiceException;
import com.krawler.common.session.SessionExpiredException;
import com.krawler.common.util.Constants;
import com.krawler.common.util.CsvReader;
import com.krawler.common.util.DataInvalidateException;
import com.krawler.common.util.KrawlerLog;
import com.krawler.common.util.StringUtil;
import com.krawler.customFieldMaster.fieldManagerDAO;
import com.krawler.common.comet.ServerEventManager;
import com.krawler.customFieldMaster.fieldDataManager;
import com.krawler.spring.common.KwlReturnObject;
import com.krawler.spring.common.kwlCommonTablesDAO;
import com.krawler.spring.sessionHandler.sessionHandlerImpl;
import com.krawler.spring.storageHandler.storageHandlerImpl;
import com.krawler.utils.json.base.JSONArray;
import com.krawler.utils.json.base.JSONException;
import com.krawler.utils.json.base.JSONObject;

import org.apache.poi.ss.usermodel.DateUtil;
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;

public class ImportUtil {
    private static final DecimalFormat dfmt = new DecimalFormat("#.#####");

    private static Log LOG = LogFactory.getLog(ImportUtil.class);

    private static final int IMPORT_LIMIT = 1500;

    private static final String df = "yyyy-MM-dd";

    private static final String df_full = "yyyy-MM-dd hh:mm:ss";

    private static final String df_customfield = "MMM dd, yyyy hh:mm:ss aaa";

    private static final String EmailRegEx = "^[\\w-]+([\\w!#$%&'*+/=?^`{|}~-]+)*(\\.[\\w!#$%&'*+/=?^`{|}~-]+)*@[\\w-]+(\\.[\\w-]+)*(\\.[\\w-]+)$";

    private static Object[] tempFileData = null;

    /**
     * @param moduleId
     * @param companyid
     * @param importDao
     * @return
     * @throws ServiceException
     */
    public static JSONArray getModuleColumnConfig(String moduleId, String companyid,
            fieldManagerDAO fieldManagerDAOobj) throws ServiceException {
        JSONArray jArr = new JSONArray();
        HashMap<String, Object> requestParams = new HashMap<String, Object>();
        requestParams.put("filter_names", Arrays.asList("dh.Module.id", "dh.allowImport"));
        requestParams.put("filter_values", Arrays.asList(moduleId, true));
        KwlReturnObject kmsg = fieldManagerDAOobj.getDefaultHeader(requestParams);

        try {
            Iterator itr = kmsg.getEntityList().iterator();
            while (itr.hasNext()) {
                DefaultHeader obj = (DefaultHeader) itr.next();
                requestParams.clear();
                requestParams.put("filter_names", Arrays.asList("c.defaultheader.id", "c.company.companyID"));
                requestParams.put("filter_param", Arrays.asList(obj.getId(), companyid));
                kmsg = fieldManagerDAOobj.getColumnHeader(requestParams);
                Iterator ite1 = kmsg.getEntityList().iterator();
                if (ite1.hasNext()) {
                    ColumnHeader obj1 = (ColumnHeader) ite1.next();
                    JSONObject jtemp = getObject(obj);
                    jtemp.put("columnName",
                            StringUtil.isNullOrEmpty(obj1.getNewHeader())
                                    ? obj1.getDefaultheader().getDefaultHeader()
                                    : obj1.getNewHeader());
                    jtemp.put("isMandatory", obj1.isMandotory());
                    jArr.put(jtemp);
                } else if (!obj.isCustomflag()) {
                    JSONObject jtemp = getObject(obj);
                    jArr.put(jtemp);
                }
            }

        } catch (JSONException ex) {
            Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return jArr;
    }

    public static JSONArray getModuleColumnConfig1(String moduleId, String companyid,
            fieldManagerDAO fieldManagerDAOobj, String modulName, boolean allowAutoNoField)
            throws ServiceException {
        // THIS is creating issue
        JSONArray jArr = new JSONArray();
        HashMap<String, Object> requestParams = new HashMap<String, Object>();
        ArrayList filter_params = new ArrayList();
        ArrayList filter_names = new ArrayList();
        filter_names.add("dh.moduleName");
        filter_params.add(modulName);
        filter_names.add("!dh.configid");
        filter_params.add("1");
        filter_names.add("dh.allowImport");
        filter_params.add(true);
        filter_names.add("dh.customflag");
        filter_params.add(false);
        requestParams.put("filter_names", filter_names);
        requestParams.put("filter_values", filter_params);
        requestParams.put("companyid", companyid);
        requestParams.put("moduleName", modulName);
        requestParams.put("fetchautonofield", allowAutoNoField);

        KwlReturnObject kmsg = fieldManagerDAOobj.getDefaultHeader(requestParams);

        try {
            List<DefaultHeader> defaultHeaders = kmsg.getEntityList();
            List<String> headerIds = new ArrayList<String>();
            for (DefaultHeader obj : defaultHeaders) {
                headerIds.add(obj.getId());
            }

            Map<String, Object[]> results = getColumnHeaderMap(fieldManagerDAOobj, headerIds, companyid);

            for (DefaultHeader obj : defaultHeaders) {
                if (results.containsKey(obj.getDefaultHeader())) {
                    Object[] mapEntry = results.get(obj.getDefaultHeader());
                    ColumnHeader obj1 = (ColumnHeader) mapEntry[1];
                    DefaultHeader obj2 = (DefaultHeader) mapEntry[0];
                    JSONObject jtemp = getObject(obj2);
                    jtemp.put("columnName", StringUtil.isNullOrEmpty(obj1.getNewHeader()) ? obj2.getDefaultHeader()
                            : obj1.getNewHeader());
                    jtemp.put("isMandatory", obj1.isMandotory());
                    jArr.put(jtemp);
                } else if (!obj.isCustomflag()) {
                    JSONObject jtemp = getObject(obj);
                    jArr.put(jtemp);
                }
            }

        } catch (JSONException ex) {
            Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return jArr;
    }

    private static Map<String, Object[]> getColumnHeaderMap(fieldManagerDAO fieldManagerDAOobj,
            List<String> headerIds, String companyId) {
        Map<String, Object[]> result = new HashMap<String, Object[]>();
        List<Object[]> colList = fieldManagerDAOobj.getColumnHeader(companyId, headerIds);

        if (colList != null) {
            for (Object[] col : colList) {
                DefaultHeader dh = (DefaultHeader) col[0];
                result.put(dh.getDefaultHeader(), col);
            }
        }
        return result;
    }

    /**
     * @param dh
     * @return
     * @throws JSONException
     */
    private static JSONObject getObject(DefaultHeader dh) throws JSONException {
        JSONObject jtemp = new JSONObject();
        jtemp.put("id", dh.getId());
        jtemp.put("columnName", dh.getDefaultHeader());
        jtemp.put("pojoName", dh.getPojoMethodName());
        jtemp.put("isMandatory", dh.isMandatory());
        jtemp.put("isNotNull", dh.isHbmNotNull());
        jtemp.put("maxLength", dh.getMaxLength());
        jtemp.put("defaultValue", dh.getDefaultValue());
        jtemp.put("validatetype", dh.getValidateType());
        jtemp.put("refModule", dh.getRefModule_PojoClassName());
        jtemp.put("refFetchColumn", dh.getRefFetchColumn_HbmName());
        jtemp.put("refDataColumn", dh.getRefDataColumn_HbmName());
        jtemp.put("customflag", dh.isCustomflag());
        jtemp.put("pojoHeader", dh.getPojoheadername());
        jtemp.put("recordname", dh.getRecordname());
        jtemp.put("xtype", dh.getXtype());
        jtemp.put("configid", dh.getConfigid());
        jtemp.put("refcolumn_number", dh.getDbcolumnrefname());
        jtemp.put("dbcolumnname", dh.getDbcolumnname());
        jtemp.put("localekey", dh.getLocalekey());
        return jtemp;
    }

    /**
     * @param requestParams
     * @param txnManager
     * @param kwlCommonTablesDAOObj
     * @param importDao
     * @return
     */
    public static JSONObject validateFileData(HashMap<String, Object> requestParams,
            HibernateTransactionManager txnManager, kwlCommonTablesDAO kwlCommonTablesDAOObj, ImportDAO importDao,
            fieldManagerDAO fieldManagerDAOobj) {
        JSONObject jobj = new JSONObject();
        String msg = "";
        boolean issuccess = true;

        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setName("import_Tx");
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus status = txnManager.getTransaction(def);
        boolean commitedEx = false;

        int total = 0, failed = 0, fileSize = 0;
        String fileName = "", extn = "";
        Modules module = null;
        String exceededLimit = "no", channelName = "";
        try {
            String companyid = (String) requestParams.get("companyid");
            String mode = (String) requestParams.get("modName");
            fileName = (String) requestParams.get("filename");
            extn = fileName.substring(fileName.lastIndexOf(".") + 1);
            channelName = "/ValidateFile/" + fileName;

            Object extraObj = requestParams.get("extraObj");
            JSONObject extraParams = (JSONObject) requestParams.get("extraParams");

            String jsondata = (String) requestParams.get("resjson");
            JSONObject rootcsvjobj = new JSONObject(jsondata);
            JSONArray mapping = rootcsvjobj.getJSONArray("root");

            String dateFormat = null, dateFormatId = (String) requestParams.get("dateFormat");
            if (extn.equalsIgnoreCase("csv") && !StringUtil.isNullOrEmpty(dateFormatId)) {
                KWLDateFormat kdf = (KWLDateFormat) kwlCommonTablesDAOObj
                        .getClassObject(KWLDateFormat.class.getName(), dateFormatId);
                dateFormat = kdf != null ? kdf.getJavaForm() : null;
            }

            String classPath = "", primaryKey = "", uniqueKeyMethodName = "", uniqueKeyHbmName = "";
            try {
                List list = importDao.getModuleObject(mode);
                module = (Modules) list.get(0); //Will throw null pointer if no module entry found
            } catch (Exception ex) {
                throw new DataInvalidateException("Column config not available for module " + mode);
            }

            try {
                classPath = module.getPojoClassPathFull().toString();
                primaryKey = module.getPrimaryKey_MethodName().toString();
            } catch (Exception ex) {
                throw new DataInvalidateException("Please set proper properties for module " + mode);
            }
            uniqueKeyMethodName = module.getUniqueKey_MethodName();
            uniqueKeyHbmName = module.getUniqueKey_HbmName();

            JSONArray columnConfig = getModuleColumnConfig1(module.getId(), companyid, fieldManagerDAOobj,
                    module.getModuleName(), false);
            String tableName = importDao.getTableName(fileName);
            KwlReturnObject kresult = importDao.getFileData(tableName, new HashMap<String, Object>());
            List fileDataList = kresult.getEntityList();
            Iterator itr = fileDataList.iterator();

            importDao.markRecordValidation(tableName, -1, 1, "", ""); //reset all invalidation
            JSONArray recordJArr = new JSONArray(), columnsJArr = new JSONArray(), DataJArr = new JSONArray();
            if (itr.hasNext()) { //
                Object[] fileData = (Object[]) itr.next();
                JSONObject jtemp = new JSONObject();
                jtemp.put("header", "Row No.");
                jtemp.put("dataIndex", "col0");
                jtemp.put("width", 50);
                columnsJArr.put(jtemp);

                for (int i = 1; i < fileData.length - 3; i++) { //Discard columns, id at index 0 and isvalid,validationlog at last 2.
                    jtemp = new JSONObject();
                    jtemp.put("header", fileData[i] == null ? "" : fileData[i].toString());
                    jtemp.put("dataIndex", "col" + i);
                    columnsJArr.put(jtemp);
                }

                jtemp = new JSONObject();
                jtemp.put("header", "Validation Log");
                //                jtemp.put("hidden", true);
                jtemp.put("dataIndex", "validateLog");
                columnsJArr.put(jtemp);

                //Create record Obj for grid's store
                for (int i = 0; i < fileData.length - 1; i++) {
                    jtemp = new JSONObject();
                    jtemp.put("name", "col" + i);
                    recordJArr.put(jtemp);
                }
                jtemp = new JSONObject();
                jtemp.put("name", "validateLog");
                recordJArr.put(jtemp);
            }

            try {
                jobj.put("record", recordJArr);
                jobj.put("columns", columnsJArr);
                jobj.put("data", DataJArr);
                jobj.put("count", failed);
                jobj.put("valid", 0);
                jobj.put("totalrecords", total);
                jobj.put("isHeader", true);
                jobj.put("finishedValidation", false);
                ServerEventManager.publish(channelName, jobj.toString(),
                        (ServletContext) requestParams.get("servletContext"));
            } catch (Exception ex) {
                throw ex;
            }

            fileSize = fileDataList.size() - 1;
            fileSize = fileSize >= IMPORT_LIMIT ? IMPORT_LIMIT : fileSize; // fileSize used for showing progress bar[Client Side]

            jobj.put("isHeader", false);
            int recIndex = 0;
            Session session = txnManager.getSessionFactory().getCurrentSession();
            int batchCounter = 0;
            while (itr.hasNext()) {
                Object[] fileData = (Object[]) itr.next();
                tempFileData = null;
                tempFileData = fileData;
                recIndex = (Integer) fileData[0];
                HashMap<String, Object> dataMap = new HashMap<String, Object>();
                HashMap<String, Object> columnHeaderMap = new HashMap<String, Object>();
                HashMap<String, Object> columnCSVindexMap = new HashMap<String, Object>();
                JSONArray customfield = new JSONArray();
                for (int k = 0; k < mapping.length(); k++) {
                    JSONObject mappingJson = mapping.getJSONObject(k);
                    String datakey = mappingJson.getString("columnname");
                    Object dataValue = cleanHTML((String) fileData[mappingJson.getInt("csvindex") + 1]); //+1 for id column at index-0
                    dataMap.put(datakey, dataValue);
                    columnHeaderMap.put(datakey, mappingJson.getString("csvheader"));
                    columnCSVindexMap.put(datakey, mappingJson.getInt("csvindex") + 1);
                }

                for (int j = 0; j < extraParams.length(); j++) {
                    String datakey = (String) extraParams.names().get(j);
                    Object dataValue = extraParams.get(datakey);
                    dataMap.put(datakey, dataValue);
                }

                try {
                    if (total >= IMPORT_LIMIT) {
                        exceededLimit = "yes";
                        break;
                    }
                    //Update processing status at client side
                    if (total > 0 && total % 10 == 0) {
                        try {
                            ServerEventManager.publish(channelName,
                                    "{parsedCount:" + total + ",invalidCount:" + failed + ", fileSize:" + fileSize
                                            + ", finishedValidation:false}",
                                    (ServletContext) requestParams.get("servletContext"));
                        } catch (Exception ex) {
                            throw ex;
                        }
                    }

                    //                    CheckUniqueRecord(requestParams, dataMap, classPath, uniqueKeyMethodName, uniqueKeyHbmName);
                    validateDataMAP2(requestParams, dataMap, columnConfig, customfield, columnHeaderMap,
                            columnCSVindexMap, dateFormat, importDao, new HashMap<String, String>());
                } catch (Exception ex) {
                    failed++;
                    String errorMsg = ex.getMessage(), invalidColumns = "";
                    try {
                        JSONObject errorLog = new JSONObject(errorMsg);
                        errorMsg = errorLog.getString("errorMsg");
                        invalidColumns = errorLog.getString("invalidColumns");
                    } catch (JSONException jex) {
                    }

                    importDao.markRecordValidation(tableName, recIndex, 0, errorMsg, invalidColumns);
                    if (batchCounter % 30 == 0) {
                        session.flush();
                        session.clear();
                    }
                    batchCounter++;
                    JSONObject jtemp = new JSONObject();
                    if (tempFileData != null) {
                        for (int i = 0; i < tempFileData.length - 2; i++) {
                            jtemp.put("col" + i, tempFileData[i] == null ? "" : tempFileData[i].toString());
                        }
                    } else {
                        for (int i = 0; i < fileData.length - 2; i++) {
                            jtemp.put("col" + i, fileData[i] == null ? "" : fileData[i].toString());
                        }
                    }
                    jtemp.put("invalidcolumns", invalidColumns);
                    jtemp.put("validateLog", errorMsg);
                    DataJArr.put(jtemp);

                    //                    try {
                    //                        jtemp.put("count", failed);
                    //                        jtemp.put("totalrecords", total+1);
                    //                        jtemp.put("fileSize", fileSize);
                    //                        jtemp.put("finishedValidation", false);
                    //                        ServerEventManager.publish(channelName, jtemp.toString(), (ServletContext) requestParams.get("servletContext"));
                    //                    } catch(Exception dex) {
                    //                        throw dex;
                    //                    }
                }
                total++;
            }

            int success = total - failed;
            if (total == 0) {
                msg = "Empty file.";
            } else if (success == 0) {
                msg = "All the records are invalid.";
            } else if (success == total) {
                msg = "All the records are valid.";
            } else {
                msg = "" + success + " valid record" + (success > 1 ? "s" : "") + "";
                msg += (failed == 0 ? "." : " and " + failed + " invalid record" + (failed > 1 ? "s" : "") + ".");
            }

            jobj.put("record", recordJArr);
            jobj.put("columns", columnsJArr);
            jobj.put("data", DataJArr);
            jobj.put("count", failed);
            jobj.put("valid", success);
            jobj.put("totalrecords", total);

            try {
                ServerEventManager.publish(channelName,
                        "{parsedCount:" + total + ",invalidCount:" + failed + ", fileSize:" + fileSize
                                + ", finishedValidation:true}",
                        (ServletContext) requestParams.get("servletContext"));
            } catch (Exception ex) {
                throw ex;
            }

            try {
                txnManager.commit(status);
            } catch (Exception ex) {
                commitedEx = true;
                throw ex;
            }
        } catch (Exception e) {
            if (!commitedEx) { //if exception occurs during commit then dont call rollback
                txnManager.rollback(status);
            }
            issuccess = false;
            msg = "" + e.getMessage();
            Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                jobj.put("success", issuccess);
                jobj.put("msg", msg);
                jobj.put("exceededLimit", exceededLimit);
            } catch (JSONException ex) {
                Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return jobj;
    }

    /**
     * @param strText
     * @return
     * @throws IOException
     */
    private static String cleanHTML(String strText) throws IOException {
        return strText != null ? StringUtil.serverHTMLStripper(strText) : null;
    }

    /**
     * @param requestParams
     * @param dataMap
     * @param columnConfigArray
     * @param customfield
     * @param columnHeaderMap
     * @param columnCSVindexMap
     * @param dateFormat
     * @param importDao
     * @throws DataInvalidateException
     */
    private static void validateDataMAP2(HashMap<String, Object> requestParams, HashMap<String, Object> dataMap,
            JSONArray columnConfigArray, JSONArray customfield, HashMap<String, Object> columnHeaderMap,
            HashMap<String, Object> columnCSVindexMap, String dateFormat, ImportDAO importDao,
            HashMap<String, String> autoNoMap) throws DataInvalidateException {
        String errorMsg = "", invalidColumns = "";
        Integer csvIndex = null;
        for (int k = 0; k < columnConfigArray.length(); k++) {
            JSONObject columnConfig = null;
            String column = "";
            try {
                columnConfig = columnConfigArray.getJSONObject(k);
                column = columnConfig.getString("pojoName");
                csvIndex = (Integer) columnCSVindexMap.get(column);

                if (dataMap.containsKey(column)) {
                    validateColumnData(requestParams, dataMap, columnConfig, column, customfield, columnHeaderMap,
                            dateFormat, importDao, csvIndex, autoNoMap);
                } else {
                    if (columnConfig.has("xtype") && columnConfig.getString("xtype")
                            .equals(String.valueOf(Constants.CUSTOM_FIELD_AUTONUMBER))) {
                        if (autoNoMap.containsKey(column)) {
                            int nextAutoNo = Integer.parseInt(autoNoMap.get(column));
                            Object vDataValue = fieldDataManager.getNextAutoNo(
                                    autoNoMap.get(column + "_" + Constants.CUSTOM_FIELD_PREFIX), nextAutoNo,
                                    autoNoMap.get(column + "_" + Constants.CUSTOM_FIELD_SUFFIX));
                            autoNoMap.put(column, String.valueOf(nextAutoNo + 1));
                            JSONObject jobj = new JSONObject();
                            createCustomColumnJSON(jobj, columnConfig, vDataValue);
                            customfield.put(jobj);
                        }
                    } else if (columnConfig.has("defaultValue")) {
                        dataMap.put(column, getDefaultValue(columnConfig));
                    }
                }
            } catch (Exception ex) {
                errorMsg += ex.getMessage();
                invalidColumns += ("col" + columnCSVindexMap.get(column) + ",");
            }
        }
        if (errorMsg.length() > 0) {
            try {
                JSONObject errorLog = new JSONObject();
                errorLog.put("errorMsg", errorMsg);
                errorLog.put("invalidColumns", invalidColumns);
                errorMsg = errorLog.toString();
            } catch (JSONException ex) {
            }
            throw new DataInvalidateException(errorMsg);
        }
    }

    /**
     * @param requestParams
     * @param dataMap
     * @param columnConfig
     * @param column
     * @param customfield
     * @param columnHeaderMap
     * @param dateFormat
     * @param importDao
     * @throws JSONException
     * @throws DataInvalidateException
     * @throws ParseException
     */
    private static void validateColumnData(HashMap<String, Object> requestParams, HashMap<String, Object> dataMap,
            JSONObject columnConfig, String column, JSONArray customfield, HashMap<String, Object> columnHeaderMap,
            String dateFormat, ImportDAO importDao, Integer colCsvIndex, HashMap<String, String> autoNoMap)
            throws JSONException, DataInvalidateException, ParseException {
        int maxLength = columnConfig.getInt("maxLength");
        String csvHeader = (String) columnHeaderMap.get(column);
        csvHeader = (csvHeader == null ? csvHeader : csvHeader.replaceAll("\\.", " "));//remove '.' from csv Header
        String columnHeader = columnConfig.getString("columnName");
        String data = dataMap.get(column) == null ? null : String.valueOf(dataMap.get(column));
        Object vDataValue = data;

        if (columnConfig.has("validatetype")) {
            String validatetype = columnConfig.getString("validatetype");
            boolean customflag = false;
            if (columnConfig.has("customflag")) {
                customflag = columnConfig.getBoolean("customflag");
            }
            if (validatetype.equalsIgnoreCase("integer")) {
                try {
                    if (!StringUtil.isNullOrEmpty(data)) { // Remove ","(comma) from number
                        data = data.replaceAll(",", "");
                    }
                    if (maxLength > 0 && data != null && data.length() > maxLength) { // Added null value check for data[Sandeep k]
                        throw new DataInvalidateException(
                                "Data length greater than " + maxLength + " for column " + csvHeader + ".");
                    }
                    vDataValue = StringUtil.isNullOrEmpty(data) ? "" : Integer.parseInt(data);
                } catch (DataInvalidateException dex) {
                    throw dex;
                } catch (Exception ex) {
                    throw new DataInvalidateException(
                            "Incorrect numeric value for " + csvHeader + ", Please ensure that value type of "
                                    + csvHeader + " matches with the " + columnHeader + ".");
                }
            } else if (validatetype.equalsIgnoreCase("double")) {
                try {
                    if (!StringUtil.isNullOrEmpty(data)) { // Remove ","(comma) from number
                        data = data.replaceAll(",", "");
                    }
                    if (maxLength > 0 && data != null && data.length() > maxLength) {
                        throw new DataInvalidateException(
                                "Data length greater than " + maxLength + " for column " + csvHeader + ".");
                    }
                    vDataValue = StringUtil.isNullOrEmpty(data) ? "" : Double.parseDouble(data);
                } catch (DataInvalidateException dex) {
                    throw dex;
                } catch (Exception ex) {
                    throw new DataInvalidateException(
                            "Incorrect numeric value for " + csvHeader + ", Please ensure that value type of "
                                    + csvHeader + " matches with the " + columnHeader + ".");
                }
            } else if (validatetype.equalsIgnoreCase("date")) {
                if (!StringUtil.isNullOrEmpty(data)) {
                    String ldf = dateFormat != null ? dateFormat : (data.length() > 10 ? df_full : df);
                    try {
                        if (maxLength > 0 && data == null) {
                            throw new DataInvalidateException(
                                    "Data length greater than " + maxLength + " for column " + csvHeader + ".");
                        }
                        DateFormat sdf = new SimpleDateFormat(ldf);
                        sdf.setLenient(false);
                        sdf.setTimeZone(TimeZone.getTimeZone("GMT" + requestParams.get("tzdiff")));
                        vDataValue = StringUtil.isNullOrEmpty(data) ? null : sdf.parse(data);
                        if (customflag && vDataValue != null) {
                            vDataValue = ((Date) vDataValue).getTime();
                            tempFileData[colCsvIndex] = vDataValue;
                            if (tempFileData != null) {//In case of invalid records date field is showing number(Long value) so replacing by proper format in tempFileData
                                tempFileData[colCsvIndex.intValue()] = new Date((Long) vDataValue);
                            }
                        }
                    } catch (DataInvalidateException dex) {
                        throw dex;
                    } catch (Exception ex) {
                        try {
                            vDataValue = Long.parseLong(data);
                            if (!customflag && vDataValue != null) {
                                vDataValue = new Date((Long) vDataValue);
                            }

                            if (tempFileData != null) {//In case of invalid records date field is showing number(Long value) so replacing by proper format in tempFileData
                                tempFileData[colCsvIndex.intValue()] = customflag ? new Date((Long) vDataValue)
                                        : vDataValue;
                            }
                        } catch (Exception e) {
                            throw new DataInvalidateException("Incorrect date format for " + csvHeader
                                    + ", Please specify values in " + ldf + " format.");
                        }
                    }
                } else {
                    vDataValue = null;
                }
            } else if (validatetype.equalsIgnoreCase("time")) {
                if (!StringUtil.isNullOrEmpty(data)) {
                    //@@@ need to uncomment
                    //                    Pattern pattern = Pattern.compile(EmailRegEx);
                    //                    if(!pattern.matcher(data).matches()){
                    //                        throw new DataInvalidateException("Incorrect time format for "+columnConfig.getString("columnName")+" use HH:MM AM or PM");
                    //                    }
                    vDataValue = data;
                } else {
                    vDataValue = null;
                }
            } else if (validatetype.equalsIgnoreCase("ref")) {
                if (!StringUtil.isNullOrEmpty(data)) {
                    try {
                        String pref = (String) requestParams.get("masterPreference"); //0:Skip Record, 1:Skip Column, 2:Add new
                        if (columnConfig.has("refModule") && columnConfig.has("refDataColumn")
                                && columnConfig.has("refFetchColumn") && columnConfig.has("configid")) {
                            requestParams.put("defaultheader", columnConfig.getString("columnName"));
                            List list = getRefData(requestParams, columnConfig.getString("refModule"),
                                    columnConfig.getString("refDataColumn"),
                                    columnConfig.getString("refFetchColumn"), columnConfig.getString("configid"),
                                    data, importDao);
                            if (list.size() == 0) {
                                //                                String configid = columnConfig.getString("configid");
                                //                                // Configid =>> 1:Owner, 2:Product, 3:Account, 4:Contact
                                //                                // then we can't create new entry for such module
                                //                                if(pref.equalsIgnoreCase("2") && (configid.equals("1") || configid.equals("2") || configid.equals("3") || configid.equals("4"))) {
                                //                                    throw new DataInvalidateException(csvHeader+" entry not found in master list for "+ columnHeader +" dropdown."); // Throw ex to skip record.
                                //                                } else
                                if (pref.equalsIgnoreCase("0")) { //Skip Record
                                    if (!ImportHandler.isMasterTable(columnConfig.getString("refModule"))) { // Cant't create entry for ref. module
                                        throw new DataInvalidateException(csvHeader + " entry not present in "
                                                + columnHeader + " list, Please create new " + columnHeader
                                                + " entry for '" + (data.replaceAll("\\.", ""))
                                                + "' as it requires some other details.");
                                    } else
                                        throw new DataInvalidateException(
                                                csvHeader + " entry not found in master list for " + columnHeader
                                                        + " dropdown."); // Throw ex to skip record.
                                } else if (pref.equalsIgnoreCase("1")) {
                                    vDataValue = null; // Put 'null' value to skip column data.
                                } else if (pref.equalsIgnoreCase("2")) {
                                    if (!ImportHandler.isMasterTable(columnConfig.getString("refModule"))) { // Cant't create entry for ref. module
                                        throw new DataInvalidateException(csvHeader + " entry not present in "
                                                + columnHeader + " list, Please create new " + columnHeader
                                                + " entry for '" + (data.replaceAll("\\.", ""))
                                                + "' as it requires some other details.");
                                    }
                                }
                            } else {
                                vDataValue = list.get(0).toString();
                            }
                        } else {
                            throw new DataInvalidateException(
                                    "Incorrect reference mapping(" + columnHeader + ") for " + csvHeader + ".");
                        }
                    } catch (ServiceException ex) {
                        throw new DataInvalidateException(
                                "Incorrect reference mapping(" + columnHeader + ") for " + csvHeader + ".");
                    } catch (DataInvalidateException ex) {
                        throw ex;
                    } catch (Exception ex) {
                        throw new DataInvalidateException(
                                csvHeader + " entry not found in master list for " + columnHeader + " dropdown.");
                    }
                } else {
                    vDataValue = null;
                }
            } else if (!customflag && validatetype.equalsIgnoreCase("multiselect")) {
                if (!StringUtil.isNullOrEmpty(data)) {
                    try {
                        String pref = (String) requestParams.get("masterPreference"); //0:Skip Record, 1:Skip Column, 2:Add new

                        if (columnConfig.has("refModule") && columnConfig.has("refDataColumn")
                                && columnConfig.has("refFetchColumn") && columnConfig.has("configid")) {
                            String[] multiData = data.toString().split(Constants.Custom_Column_Sep);
                            String mdata = "";
                            Boolean isRefData = columnConfig.has("refModule") && columnConfig.has("refDataColumn")
                                    && columnConfig.has("refFetchColumn") && columnConfig.has("configid");
                            List list = null;
                            for (int i = 0; isRefData && i < multiData.length; i++) { // Reference module
                                list = getRefData(requestParams, columnConfig.getString("refModule"),
                                        columnConfig.getString("refDataColumn"),
                                        columnConfig.getString("refFetchColumn"),
                                        columnConfig.getString("configid"), multiData[i], importDao);
                                if (list.size() == 0) {
                                    if (pref.equalsIgnoreCase("0")) { //Skip Record
                                        if (!ImportHandler.isMasterTable(columnConfig.getString("refModule"))) { // Cant't create entry for ref. module
                                            throw new DataInvalidateException(csvHeader + " entry not present in "
                                                    + columnHeader + " list, Please create new " + columnHeader
                                                    + " entry for '" + (multiData[i].replaceAll("\\.", ""))
                                                    + "' as it requires some other details.");
                                        } else {
                                            throw new DataInvalidateException(
                                                    csvHeader + " entry not found in master list for "
                                                            + columnHeader + " dropdown."); // Throw ex to skip record.
                                        }
                                    } else if (pref.equalsIgnoreCase("2")) {
                                        if (!ImportHandler.isMasterTable(columnConfig.getString("refModule"))) { // Cant't create entry for ref. module
                                            throw new DataInvalidateException(csvHeader + " entry not present in "
                                                    + columnHeader + " list, Please create new " + columnHeader
                                                    + " entry for '" + (multiData[i].replaceAll("\\.", ""))
                                                    + "' as it requires some other details.");
                                        }
                                    }
                                } else {
                                    mdata += list.get(0).toString() + Constants.Custom_Column_Sep;
                                }
                            }
                            if (mdata.length() > 0) {
                                vDataValue = mdata.substring(0, mdata.length() - 1);
                            } else {
                                if (pref.equalsIgnoreCase("1")) {
                                    vDataValue = null;
                                } else
                                    vDataValue = "";
                            }
                        } else {
                            throw new DataInvalidateException(
                                    "Incorrect reference mapping(" + columnHeader + ") for " + csvHeader + ".");
                        }

                    } catch (ServiceException ex) {
                        throw new DataInvalidateException(
                                "Incorrect reference mapping(" + columnHeader + ") for " + csvHeader + ".");
                    } catch (DataInvalidateException ex) {
                        throw ex;
                    } catch (Exception ex) {
                        throw new DataInvalidateException(
                                csvHeader + " entry not found in master list for " + columnHeader + " dropdown.");
                    }
                } else {
                    vDataValue = null;
                }
            } else if (validatetype.equalsIgnoreCase("email")) {
                if (maxLength > 0 && data != null && data.length() > maxLength) {
                    throw new DataInvalidateException(
                            "Data length greater than " + maxLength + " for column " + csvHeader + ".");
                }
                if (!StringUtil.isNullOrEmpty(data)) {
                    Pattern pattern = Pattern.compile(EmailRegEx);
                    if (!pattern.matcher(data).matches()) {
                        throw new DataInvalidateException("Invalid email address for " + csvHeader + ".");
                    }
                    vDataValue = data;
                } else {
                    vDataValue = null;
                }
            } else if (validatetype.equalsIgnoreCase("boolean")) {
                if (data.equalsIgnoreCase("true") || data.equalsIgnoreCase("1") || data.equalsIgnoreCase("T")) {
                    vDataValue = true;
                } else if (data.equalsIgnoreCase("false") || data.equalsIgnoreCase("0")
                        || data.equalsIgnoreCase("F")) {
                    vDataValue = false;
                } else {
                    throw new DataInvalidateException("Incorrect boolean value for " + csvHeader + ".");
                }
            }

            if (vDataValue == null && columnConfig.has("isNotNull") && columnConfig.getBoolean("isNotNull")) {
                throw new DataInvalidateException(
                        "Empty data found in " + csvHeader + ", Can not set empty data for " + columnHeader + ".");
            } else {
                if (customflag) {
                    JSONObject jobj = new JSONObject();
                    if (columnConfig.getString("xtype").equals("4")
                            || columnConfig.getString("xtype").equals("7")) {//Drop down & Multi Select Drop down
                        try {
                            if (vDataValue != null) {
                                if (!StringUtil.isNullOrEmpty(vDataValue.toString())) {
                                    HashMap<String, Object> comborequestParams = new HashMap<String, Object>();
                                    comborequestParams = requestParams;
                                    String pref = (String) requestParams.get("masterPreference"); //0:Skip Record, 1:Skip Column, 2:Add new
                                    KwlReturnObject result = null;
                                    if (columnConfig.getString("xtype").equals("4")) {
                                        comborequestParams.put("filtergetColumn_names",
                                                Arrays.asList("fieldid", "value"));
                                        comborequestParams.put("filter_values", Arrays.asList(
                                                columnConfig.getString("pojoHeader"), vDataValue.toString()));

                                        List lst = getCustomComboID(requestParams, vDataValue.toString(),
                                                columnConfig.getString("pojoHeader"), "id", importDao);
                                        //                                        List lst = result.getEntityList();
                                        if (lst.size() == 0) {
                                            if (pref.equalsIgnoreCase("0")) { //Skip Record
                                                throw new DataInvalidateException(
                                                        csvHeader + " entry not found in the list for "
                                                                + columnHeader + " dropdown."); // Throw ex to skip record.
                                            } else if (pref.equalsIgnoreCase("1")) {
                                                vDataValue = null; // Put 'null' value to skip column data.
                                            }
                                        } else {
                                            //                                                FieldComboData tmpcontyp = (FieldComboData) ite.next();
                                            vDataValue = lst.get(0).toString();
                                        }
                                    } else if (columnConfig.getString("xtype").equals("7")) {
                                        String[] multiData = vDataValue.toString()
                                                .split(Constants.Custom_Column_Sep);
                                        String mdata = "";
                                        for (int i = 0; i < multiData.length; i++) {

                                            // if for master  multiselect data item and else for custom multiselect
                                            if (columnConfig.has("refModule") && columnConfig.has("refDataColumn")
                                                    && columnConfig.has("refFetchColumn")
                                                    && columnConfig.has("configid")) {
                                                List list = getRefData(requestParams,
                                                        columnConfig.getString("refModule"),
                                                        columnConfig.getString("refDataColumn"),
                                                        columnConfig.getString("refFetchColumn"),
                                                        columnConfig.getString("configid"), multiData[i],
                                                        importDao);
                                                mdata += list.get(0).toString() + Constants.Custom_Column_Sep;
                                            } else {
                                                comborequestParams.put("filter_names",
                                                        Arrays.asList("fieldid", "value"));
                                                comborequestParams.put("filter_values", Arrays.asList(
                                                        columnConfig.getString("pojoHeader"), multiData[i]));

                                                List lst = getCustomComboID(requestParams, multiData[i],
                                                        columnConfig.getString("pojoHeader"), "id", importDao);
                                                //                                                List lst = result.getEntityList();
                                                if (lst.size() == 0) {
                                                    if (pref.equalsIgnoreCase("0")) { //Skip Record
                                                        throw new DataInvalidateException(
                                                                csvHeader + " entry not found in the list for "
                                                                        + columnHeader + " dropdown."); // Throw ex to skip record.
                                                    } else if (pref.equalsIgnoreCase("1")) {
                                                        vDataValue = null; // Put 'null' value to skip column data.
                                                    }
                                                } else {
                                                    //                                                FieldComboData tmpcontyp = (FieldComboData) ite.next();
                                                    mdata += lst.get(0).toString() + Constants.Custom_Column_Sep;
                                                }
                                            }
                                        }
                                        if (mdata.length() > 0) {
                                            vDataValue = mdata.substring(0, mdata.length() - 1);
                                        }
                                    }
                                }
                            } /* else {
                              throw new DataInvalidateException("Incorrect reference mapping("+columnHeader+") for "+csvHeader+".");
                              }*/
                        } catch (DataInvalidateException ex) {
                            throw ex;
                        } catch (Exception ex) {
                            throw new DataInvalidateException(csvHeader + " entry not found in master list for "
                                    + columnHeader + " dropdown.");
                        }
                    } else if (columnConfig.getString("xtype").equals("8")) {//Reference Drop down
                        try {
                            if (vDataValue != null) {
                                if (!StringUtil.isNullOrEmpty(vDataValue.toString())) {
                                    String pref = (String) requestParams.get("masterPreference"); //0:Skip Record, 1:Skip Column, 2:Add new
                                    if (columnConfig.has("refModule") && columnConfig.has("refDataColumn")
                                            && columnConfig.has("refFetchColumn") && columnConfig.has("configid")) {
                                        List list = getRefData(requestParams, columnConfig.getString("refModule"),
                                                columnConfig.getString("refDataColumn"),
                                                columnConfig.getString("refFetchColumn"),
                                                columnConfig.getString("configid"), vDataValue, importDao);
                                        if (list.size() == 0) {
                                            if (pref.equalsIgnoreCase("0")) { //Skip Record
                                                throw new DataInvalidateException(
                                                        csvHeader + " entry not found in the list for "
                                                                + columnHeader + " dropdown."); // Throw ex to skip record.
                                            } else if (pref.equalsIgnoreCase("1")) {
                                                vDataValue = null; // Put 'null' value to skip column data.
                                            } else if (pref.equalsIgnoreCase("2")) { //2:Add new
                                                if (columnConfig.getString("refModule")
                                                        .equalsIgnoreCase(Constants.Crm_users_pojo)) {//For users custom column.
                                                    throw new DataInvalidateException(
                                                            csvHeader + " entry not found in the list for "
                                                                    + columnHeader + " dropdown."); // Throw ex to skip record.
                                                }
                                            }
                                        } else {
                                            vDataValue = list.get(0).toString();
                                        }
                                    } else {
                                        throw new DataInvalidateException("Incorrect reference mapping("
                                                + columnHeader + ") for " + csvHeader + ".");
                                    }
                                }
                            } /* else {
                              throw new DataInvalidateException("Incorrect reference mapping("+columnHeader+") for "+csvHeader+".");
                              }*/
                        } catch (ServiceException ex) {
                            throw new DataInvalidateException(
                                    "Incorrect reference mapping(" + columnHeader + ") for " + csvHeader + ".");
                        } catch (DataInvalidateException ex) {
                            throw ex;
                        } catch (Exception ex) {
                            throw new DataInvalidateException(csvHeader + " entry not found in master list for "
                                    + columnHeader + " dropdown.");
                        }
                    } else {
                        if (!validatetype.equalsIgnoreCase("date") && maxLength > 0 && data != null
                                && data.length() > maxLength) {
                            throw new DataInvalidateException(
                                    "Data length greater than " + maxLength + " for column " + csvHeader + ".");
                        }
                    }
                    createCustomColumnJSON(jobj, columnConfig, vDataValue);

                    customfield.put(jobj);
                    if (dataMap.containsKey(column)) {
                        dataMap.remove(column);
                    }
                } else {
                    if (validatetype.equalsIgnoreCase("string") && maxLength > 0 && data != null
                            && data.length() > maxLength) {
                        throw new DataInvalidateException(
                                "Data length greater than " + maxLength + " for column " + csvHeader + ".");
                    }
                    dataMap.put(column, vDataValue);
                }
            }
        } else { // If no validation type then check allow null property[SK]
            if (vDataValue == null && columnConfig.has("isNotNull") && columnConfig.getBoolean("isNotNull")) {
                throw new DataInvalidateException(
                        "Empty data found in " + csvHeader + ". Can not set empty data for " + columnHeader + ".");
            }
            if (data != null && maxLength > 0 && data.length() > maxLength) {
                throw new DataInvalidateException(
                        "Data length greater than " + maxLength + " for column " + csvHeader + ".");
            }
        }
    }

    private static void createCustomColumnJSON(JSONObject jobj, JSONObject columnConfig, Object vDataValue) {
        try {
            jobj.put(columnConfig.getString("pojoName"), vDataValue == null ? "" : vDataValue);
            jobj.put("refcolumn_name", Constants.Custom_Column_Prefix + columnConfig.getString("refcolumn_number"));
            jobj.put("fieldname", columnConfig.getString("columnName"));
            jobj.put(columnConfig.getString("dbcolumnname"), vDataValue == null ? "" : vDataValue);
            jobj.put(columnConfig.getString("columnName"), columnConfig.getString("dbcolumnname"));

            jobj.put("filedid", columnConfig.getString("pojoHeader"));
            jobj.put("xtype", columnConfig.getString("xtype"));
        } catch (JSONException ex) {
            Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * @param columnConfig
     * @return
     * @throws ParseException
     * @throws JSONException
     * @throws DataInvalidateException
     */
    private static Object getDefaultValue(JSONObject columnConfig)
            throws ParseException, JSONException, DataInvalidateException {
        Object defaultValue = columnConfig.get("defaultValue");
        if (columnConfig.has("validatetype")) {
            String validatetype = columnConfig.getString("validatetype");
            if (validatetype.equalsIgnoreCase("integer")) {
                defaultValue = StringUtil.isNullOrEmpty(defaultValue.toString()) ? 0
                        : Integer.parseInt(defaultValue.toString());
            } else if (validatetype.equalsIgnoreCase("double")) {
                defaultValue = StringUtil.isNullOrEmpty(defaultValue.toString()) ? 0.0
                        : Double.parseDouble(defaultValue.toString());
            } else if (validatetype.equalsIgnoreCase("date")) {
                String ddateStr = defaultValue.toString();
                //DateFormat sdf = new SimpleDateFormat(ddateStr.length()>10 ? df_full : df);
                boolean customflag = columnConfig.optBoolean("customflag");
                if (ddateStr.equals("now")) {
                    defaultValue = Long.toString(System.currentTimeMillis());
                } else {
                    defaultValue = StringUtil.isNullOrEmpty(ddateStr) ? null : ddateStr;
                }
                if (!customflag && defaultValue != null) {
                    defaultValue = new Date(Long.parseLong((String) defaultValue));
                }
            } else if (validatetype.equalsIgnoreCase("boolean")) {
                String data = defaultValue.toString();
                if (data.equalsIgnoreCase("true") || data.equalsIgnoreCase("1") || data.equalsIgnoreCase("T")) {
                    defaultValue = true;
                } else if (data.equalsIgnoreCase("false") || data.equalsIgnoreCase("0")
                        || data.equalsIgnoreCase("F")) {
                    defaultValue = false;
                } else {
                    throw new DataInvalidateException(
                            "Incorrect default boolean value for " + columnConfig.getString("columnName") + ".");
                }
            }
        }
        if (defaultValue == null && columnConfig.has("isNotNull") && columnConfig.getBoolean("isNotNull")) {
            throw new DataInvalidateException(
                    "Can not set default empty data for " + columnConfig.getString("columnName") + ".");
        }
        return defaultValue;
    }

    /**
     * @param requestParams
     * @param table
     * @param dataColumn
     * @param fetchColumn
     * @param comboConfigid
     * @param token
     * @param importDao
     * @return
     * @throws ServiceException
     * @throws DataInvalidateException
     */
    private static List getRefData(HashMap<String, Object> requestParams, String table, String dataColumn,
            String fetchColumn, String comboConfigid, Object token, ImportDAO importDao)
            throws ServiceException, DataInvalidateException {
        ArrayList<String> filterNames = new ArrayList<String>();
        ArrayList<Object> filterValues = new ArrayList<Object>();
        filterNames.add(dataColumn);
        filterValues.add(token);
        filterNames.add(Constants.deleteflag);
        filterValues.add(0);
        return importDao.getRefModuleData(requestParams, table, fetchColumn, comboConfigid, filterNames,
                filterValues);
    }

    /**
     * @param requestParams
     * @param combovalue
     * @param fieldid
     * @param fetchColumn
     * @param importDao
     * @return
     * @throws ServiceException
     * @throws DataInvalidateException
     */
    private static List getCustomComboID(HashMap<String, Object> requestParams, String combovalue, String fieldid,
            String fetchColumn, ImportDAO importDao) throws ServiceException, DataInvalidateException {
        ArrayList filterNames = new ArrayList<String>();
        ArrayList filterValues = new ArrayList<Object>();
        filterNames.add(ImportConstants.Crm_value);
        filterValues.add(combovalue);
        filterNames.add(ImportConstants.Crm_fieldid);
        filterValues.add(fieldid);
        filterNames.add(ImportConstants.Crm_deleteflag);
        filterValues.add(0);
        return importDao.getCustomComboID(requestParams, fetchColumn, filterNames, filterValues);
    }

    /**
     * @param request
     * @return
     * @throws SessionExpiredException
     */
    public static HashMap<String, Object> getImportRequestParams(HttpServletRequest request)
            throws SessionExpiredException {
        HashMap<String, Object> requestParams = new HashMap<String, Object>();
        requestParams.put("modName", request.getParameter("modName"));
        requestParams.put("moduleName", request.getParameter("moduleName"));
        requestParams.put("delimiterType", request.getParameter("delimiterType"));
        requestParams.put("filename", request.getParameter("filename"));
        requestParams.put("resjson", request.getParameter("resjson"));
        requestParams.put("sheetindex", request.getParameter("sheetindex"));
        requestParams.put("onlyfilename", request.getParameter("onlyfilename"));
        requestParams.put("dateFormat", request.getParameter("dateFormat"));
        requestParams.put("masterPreference", request.getParameter("masterPreference"));

        requestParams.put("companyid", sessionHandlerImpl.getCompanyid(request));
        requestParams.put("tzdiff", sessionHandlerImpl.getTimeZoneDifference(request));
        requestParams.put("userid", sessionHandlerImpl.getUserid(request));
        requestParams.put("doAction", request.getParameter("do"));
        return requestParams;
    }

    /**
     * @param requestParams
     * @param importDao
     * @return
     */
    public static String addPendingImportLog(HashMap<String, Object> requestParams, ImportDAO importDao) {
        String logId = null;
        try {
            //Insert Integration log
            String fileName = (String) requestParams.get("filename");
            String Module = (String) requestParams.get("modName");
            try {
                List list = importDao.getModuleObject(Module);
                Modules module = (Modules) list.get(0); //Will throw null pointer if no module entry found
                Module = module.getId();
            } catch (Exception ex) {
                throw new DataInvalidateException("Column config not available for module " + Module);
            }
            HashMap<String, Object> logDataMap = new HashMap<String, Object>();
            logDataMap.put("FileName", ImportLog.getActualFileName(fileName));
            logDataMap.put("StorageName", fileName);
            logDataMap.put("Log", "Pending");
            logDataMap.put("Type", fileName.substring(fileName.lastIndexOf(".") + 1));
            logDataMap.put("Module", Module);
            logDataMap.put("ImportDate", new Date());
            logDataMap.put("User", (String) requestParams.get("userid"));
            logDataMap.put("Company", (String) requestParams.get("companyid"));
            ImportLog importlog = (ImportLog) importDao.saveImportLog(logDataMap);
            logId = importlog.getId();
        } catch (Exception ex) {
            logId = null;
        }
        return logId;
    }

    /**
     * @param requestParams
     * @param txnManager
     * @param KwlCommonTablesDAOObj
     * @param importDao
     * @param fieldManagerDAOobj
     * @return
     */
    public static JSONObject importFileData(HashMap<String, Object> requestParams,
            HibernateTransactionManager txnManager, kwlCommonTablesDAO KwlCommonTablesDAOObj, ImportDAO importDao,
            fieldManagerDAO fieldManagerDAOobj) {

        JSONObject jobj = new JSONObject();
        String msg = "";
        boolean issuccess = true;

        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setName("import_Tx");
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus status = txnManager.getTransaction(def);
        boolean commitedEx = false;

        int total = 0, failed = 0;
        String fileName = "", tableName = "", extn = "";
        Modules module = null;

        try {
            String moduleID = "";
            String mode = (String) requestParams.get("modName");
            fileName = (String) requestParams.get("filename");
            String companyid = (String) requestParams.get("companyid");
            extn = fileName.substring(fileName.lastIndexOf(".") + 1);
            StringBuilder failedRecords = new StringBuilder();

            String dateFormat = null, dateFormatId = (String) requestParams.get("dateFormat");
            if (extn.equalsIgnoreCase("csv") && !StringUtil.isNullOrEmpty(dateFormatId)) {
                KWLDateFormat kdf = (KWLDateFormat) KwlCommonTablesDAOObj
                        .getClassObject(KWLDateFormat.class.getName(), dateFormatId);
                dateFormat = kdf != null ? kdf.getJavaForm() : null;
            }

            Object extraObj = requestParams.get("extraObj");
            JSONObject extraParams = (JSONObject) requestParams.get("extraParams");

            String jsondata = (String) requestParams.get("resjson");
            JSONObject rootcsvjobj = new JSONObject(jsondata);
            JSONArray mapping = rootcsvjobj.getJSONArray("root");

            String classPath = "", primaryKey = "", uniqueKeyMethodName = "", uniqueKeyHbmName = "";
            try {
                List list = importDao.getModuleObject(mode);
                module = (Modules) list.get(0); //Will throw null pointer if no module entry found
            } catch (Exception ex) {
                throw new DataInvalidateException("Column config not available for module " + mode);
            }

            try {
                classPath = module.getPojoClassPathFull().toString();
                primaryKey = module.getPrimaryKey_MethodName().toString();
                moduleID = module.getId();
            } catch (Exception ex) {
                throw new DataInvalidateException("Please set proper properties for module " + mode);
            }
            uniqueKeyMethodName = module.getUniqueKey_MethodName();
            uniqueKeyHbmName = module.getUniqueKey_HbmName();

            JSONArray columnConfig = getModuleColumnConfig1(module.getId(), companyid, fieldManagerDAOobj,
                    module.getModuleName(), true);
            tableName = importDao.getTableName(fileName);
            HashMap<String, Object> filterParams = new HashMap<String, Object>();
            //            filterParams.put("isvalid", 1); //To fetch valid records
            KwlReturnObject kresult = importDao.getFileData(tableName, filterParams); //Fetch all valid records
            List fileDataList = kresult.getEntityList();
            Iterator itr = fileDataList.iterator();
            if (itr.hasNext()) {
                Object[] fileData = (Object[]) itr.next(); //Skip header row
                failedRecords.append(createCSVrecord(fileData) + "\"Error Message\"");//failedRecords.append("\"Row No.\","+createCSVrecord(fileData)+"\"Error Message\"");
            }
            int recIndex = 0;
            importDao.markRecordValidation(tableName, -1, 1, "", ""); //reset all invalidation
            int batchCounter = 0;
            Session session = txnManager.getSessionFactory().getCurrentSession();

            /*-Auto no custom column changes*/
            String customdataclasspath = "";
            int intModuleId = 0;
            if (moduleID.equals(Constants.MODULEID_LEAD)) {
                intModuleId = Constants.Crm_lead_moduleid;
                customdataclasspath = Constants.Crm_lead_custom_data_classpath;
            } else if (moduleID.equals(Constants.MODULEID_ACCOUNT)) {
                intModuleId = Constants.Crm_account_moduleid;
                customdataclasspath = Constants.Crm_account_custom_data_classpath;
            } else if (moduleID.equals(Constants.MODULEID_CONTACT)) {
                intModuleId = Constants.Crm_contact_moduleid;
                customdataclasspath = Constants.Crm_contact_custom_data_classpath;
            } else if (moduleID.equals(Constants.MODULEID_OPPORTUNITY)) {
                intModuleId = Constants.Crm_opportunity_moduleid;
                customdataclasspath = Constants.Crm_opportunity_custom_data_classpath;
            } else if (moduleID.equals(Constants.MODULEID_CASE)) {
                intModuleId = Constants.Crm_case_moduleid;
                customdataclasspath = Constants.Crm_case_custom_data_classpath;
            } else if (moduleID.equals(Constants.MODULEID_PRODUCT)) {
                intModuleId = Constants.Crm_product_moduleid;
                customdataclasspath = Constants.Crm_product_custom_data_classpath;
            }
            List autoNoFieldName = new ArrayList();
            HashMap<String, String> autoNoMap = new HashMap<String, String>();
            HashMap<String, Object> fieldrequestParams = new HashMap<String, Object>();
            fieldrequestParams.put("isexport", true);
            fieldrequestParams.put("filter_names", Arrays.asList("companyid", "moduleid", "fieldtype"));
            fieldrequestParams.put("filter_values",
                    Arrays.asList(companyid, intModuleId, Constants.CUSTOM_FIELD_AUTONUMBER));
            KwlReturnObject AutoNoFieldMap = fieldManagerDAOobj.getFieldParams(fieldrequestParams);
            if (AutoNoFieldMap.getEntityList().size() != 0) {
                List<FieldParams> autNoList = AutoNoFieldMap.getEntityList();
                for (FieldParams obj : autNoList) {
                    String maxNo = fieldManagerDAOobj.getMaxAutoNumber(
                            Constants.Custom_column_Prefix + obj.getColnum(), customdataclasspath, companyid,
                            obj.getPrefix(), obj.getSuffix());
                    Integer maxNumber = Integer.parseInt(maxNo) + 1;
                    autoNoMap.put(obj.getFieldname(), maxNumber.toString());
                    autoNoFieldName.add(obj.getFieldname());
                    autoNoMap.put(obj.getFieldname() + "_" + Constants.CUSTOM_FIELD_PREFIX, obj.getPrefix());
                    autoNoMap.put(obj.getFieldname() + "_" + Constants.CUSTOM_FIELD_SUFFIX, obj.getSuffix());
                }
            }
            // End
            while (itr.hasNext()) {
                total++;
                Object[] fileData = (Object[]) itr.next();
                recIndex = (Integer) fileData[0];
                HashMap<String, Object> dataMap = new HashMap<String, Object>();
                HashMap<String, Object> columnHeaderMap = new HashMap<String, Object>();
                HashMap<String, Object> columnCSVindexMap = new HashMap<String, Object>();
                JSONArray customfield = new JSONArray();
                for (int k = 0; k < mapping.length(); k++) {
                    JSONObject mappingJson = mapping.getJSONObject(k);
                    String datakey = mappingJson.getString("columnname");
                    Object dataValue = cleanHTML(fileData[mappingJson.getInt("csvindex") + 1] == null ? null
                            : String.valueOf(fileData[mappingJson.getInt("csvindex") + 1])); //+1 for id column at index-0
                    dataMap.put(datakey, dataValue);
                    columnHeaderMap.put(datakey, mappingJson.getString("csvheader"));
                    columnCSVindexMap.put(datakey, mappingJson.getInt("csvindex") + 1);
                }

                for (int j = 0; j < extraParams.length(); j++) {
                    String datakey = (String) extraParams.names().get(j);
                    Object dataValue = extraParams.get(datakey);
                    dataMap.put(datakey, dataValue);
                }

                Object object = null;
                try {
                    //                    CheckUniqueRecord(requestParams, dataMap, classPath, uniqueKeyMethodName, uniqueKeyHbmName);
                    validateDataMAP2(requestParams, dataMap, columnConfig, customfield, columnHeaderMap,
                            columnCSVindexMap, dateFormat, importDao, autoNoMap);
                    object = importDao.saveRecord(requestParams, dataMap, null, mode, classPath, primaryKey,
                            extraObj, customfield);
                    if (batchCounter % 100 == 0) {
                        session.flush();
                        session.clear();
                    }
                    batchCounter++;
                } catch (Exception ex) {
                    failed++;
                    String errorMsg = ex.getMessage(), invalidColumns = "";
                    try {
                        JSONObject errorLog = new JSONObject(errorMsg);
                        errorMsg = errorLog.getString("errorMsg");
                        invalidColumns = errorLog.getString("invalidColumns");
                    } catch (JSONException jex) {
                    }
                    failedRecords.append("\n" + createCSVrecord(fileData) + "\"" + errorMsg + "\"");//failedRecords.append("\n"+(total)+","+createCSVrecord(fileData)+"\""+ex.getMessage()+"\"");
                    importDao.markRecordValidation(tableName, recIndex, 0, errorMsg, invalidColumns);
                }
            }

            if (failed > 0) {
                createFailureFiles(fileName, failedRecords, ".csv");
            }

            int success = total - failed;
            if (total == 0) {
                msg = "Empty file.";
            } else if (success == 0) {
                msg = "Failed to import all the records.";
            } else if (success == total) {
                msg = "All records are imported successfully.";
            } else {
                msg = "Imported " + success + " record" + (success > 1 ? "s" : "") + " successfully";
                msg += (failed == 0 ? "."
                        : " and failed to import " + failed + " record" + (failed > 1 ? "s" : "") + ".");
            }

            try {
                txnManager.commit(status);
                importDao.linkCustomData(mode);
            } catch (Exception ex) {
                commitedEx = true;
                throw ex;
            }
        } catch (Exception e) {
            if (!commitedEx) { //if exception occurs during commit then dont call rollback
                txnManager.rollback(status);
            }
            issuccess = false;
            msg = "" + e.getMessage();
            Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            DefaultTransactionDefinition ldef = new DefaultTransactionDefinition();
            ldef.setName("import_Tx");
            ldef.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
            TransactionStatus lstatus = txnManager.getTransaction(ldef);
            boolean exCommit = false;
            try {
                //Insert Integration log

                requestParams.put("modulename", module.getModuleName());
                requestParams.put("validflag", 2);
                if (!module.getModuleName().equals("Target") && !module.getModuleName().equals("Calibration"))
                    fieldManagerDAOobj.validateimportrecords(requestParams);

                HashMap<String, Object> logDataMap = new HashMap<String, Object>();
                String logId = (String) requestParams.get("logId");
                if (!StringUtil.isNullOrEmpty(logId)) {
                    logDataMap.put("Id", logId);
                }
                failed = issuccess ? failed : total;
                logDataMap.put("FileName", ImportLog.getActualFileName(fileName));
                logDataMap.put("StorageName", fileName);
                logDataMap.put("Log", msg);
                logDataMap.put("Type", fileName.substring(fileName.lastIndexOf(".") + 1));
                logDataMap.put("TotalRecs", total);
                logDataMap.put("Rejected", failed);
                logDataMap.put("Module", module.getId());
                logDataMap.put("ImportDate", new Date());
                logDataMap.put("User", (String) requestParams.get("userid"));
                logDataMap.put("Company", (String) requestParams.get("companyid"));
                importDao.saveImportLog(logDataMap);
                importDao.removeFileTable(tableName);//Remove table after importing all records
                try {
                    txnManager.commit(lstatus);
                } catch (Exception ex) {
                    exCommit = true;
                    throw ex;
                }
            } catch (Exception ex) {
                if (!exCommit) { //if exception occurs during commit then dont call rollback
                    txnManager.rollback(lstatus);
                }
                Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                jobj.put("success", issuccess);
                jobj.put("msg", msg);
                jobj.put("totalrecords", total);
                jobj.put("successrecords", total - failed);
                jobj.put("failedrecords", failed);
                jobj.put("filename", ImportLog.getActualFileName(fileName));
            } catch (JSONException ex) {
                Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return jobj;
    }

    /**
     * @param listArray
     * @return
     */
    private static String createCSVrecord(Object[] listArray) {
        String rec = "";
        for (int i = 1; i < listArray.length - 3; i++) { //Discard columns id at index 0 and isvalid,invalidColumns, validationlog at last 3 indexes.
            rec += "\"" + (listArray[i] == null ? "" : listArray[i].toString()) + "\",";
        }
        return rec;
    }

    /**
     * @param filename
     * @param failedRecords
     * @param ext
     */
    private static void createFailureFiles(String filename, StringBuilder failedRecords, String ext) {
        String destinationDirectory;
        try {
            destinationDirectory = storageHandlerImpl.GetDocStorePath() + "importplans";
            if (StringUtil.isNullOrEmpty(ext)) {
                ext = filename.substring(filename.lastIndexOf("."));
            }
            filename = filename.substring(0, filename.lastIndexOf("."));

            java.io.FileOutputStream failurefileOut = new java.io.FileOutputStream(
                    destinationDirectory + "/" + filename + ImportLog.failureTag + ext);
            failurefileOut.write(failedRecords.toString().getBytes());
            failurefileOut.flush();
            failurefileOut.close();
        } catch (Exception ex) {
            System.out.println("\nError file write [success/failed] " + ex);
        }
    }

    /**
     * @param request
     * @return
     * @throws IOException
     */
    public static JSONObject getMappingCSVHeader(HttpServletRequest request) throws IOException {
        String contentType = request.getContentType();
        CsvReader csvReader = null;
        JSONObject jtemp1 = new JSONObject();
        JSONObject jobj = new JSONObject();
        JSONObject jsnobj = new JSONObject();
        String delimiterType = request.getParameter("delimiterType");
        String str = "";
        {
            FileInputStream fstream = null;
            try {
                if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {

                    String fileid = UUID.randomUUID().toString();
                    fileid = fileid.replaceAll("-", ""); // To append UUID without "-" [SK]
                    //                    String Module = request.getParameter("type")==null?"":"_"+request.getParameter("type");
                    String f1 = uploadDocument(request, fileid);

                    if (f1.length() != 0) {
                        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "importplans";
                        File csv = new File(destinationDirectory + "/" + f1);
                        fstream = new FileInputStream(csv);
                        csvReader = new CsvReader(new InputStreamReader(fstream), delimiterType);

                        csvReader.readHeaders();

                        int cols = csvReader.getHeaderCount();
                        for (int k = 0; k < csvReader.getHeaderCount(); k++) {
                            jtemp1 = new JSONObject();
                            if (!StringUtil.isNullOrEmpty(csvReader.getHeader(k).trim())) {
                                jtemp1.put("header", csvReader.getHeader(k));
                                jtemp1.put("index", k);
                                jobj.append("Header", jtemp1);
                            }
                        }

                        if (jobj.isNull("Header")) {
                            jsnobj.put("success", "true");

                            str = jsnobj.toString();
                        } else {
                            jobj.append("success", "true");
                            jobj.append("FileName", f1);
                            jobj.put("name", f1);
                            jobj.put("delimiterType", delimiterType);
                            jobj.put("cols", cols);
                            str = jobj.toString();
                        }
                    }
                }
            } catch (FileNotFoundException ex) {
                Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
            } catch (Exception ex) {
                Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                csvReader.close();
                fstream.close();
            }
        }
        return jobj;
    }

    /**
     * @param request
     * @param fileid
     * @return
     * @throws ServiceException
     */
    private static String uploadDocument(HttpServletRequest request, String fileid) throws ServiceException {
        String result = "";
        try {
            String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "importplans";
            org.apache.commons.fileupload.DiskFileUpload fu = new org.apache.commons.fileupload.DiskFileUpload();
            org.apache.commons.fileupload.FileItem fi = null;
            org.apache.commons.fileupload.FileItem docTmpFI = null;

            List fileItems = null;
            try {
                fileItems = fu.parseRequest(request);
            } catch (FileUploadException e) {
                KrawlerLog.op.warn("Problem While Uploading file :" + e.toString());
            }

            long size = 0;
            String Ext = "";
            String fileName = null;
            boolean fileupload = false;
            java.io.File destDir = new java.io.File(destinationDirectory);
            fu.setSizeMax(-1);
            fu.setSizeThreshold(4096);
            fu.setRepositoryPath(destinationDirectory);
            java.util.HashMap arrParam = new java.util.HashMap();
            for (java.util.Iterator k = fileItems.iterator(); k.hasNext();) {
                fi = (org.apache.commons.fileupload.FileItem) k.next();
                arrParam.put(fi.getFieldName(), fi.getString("UTF-8"));
                if (!fi.isFormField()) {
                    size = fi.getSize();
                    fileName = new String(fi.getName().getBytes(), "UTF8");

                    docTmpFI = fi;
                    fileupload = true;
                }
            }

            if (fileupload) {

                if (!destDir.exists()) {
                    destDir.mkdirs();
                }
                if (fileName.contains(".")) {
                    Ext = fileName.substring(fileName.lastIndexOf("."));
                }
                if (size != 0) {
                    int startIndex = fileName.contains("\\") ? (fileName.lastIndexOf("\\") + 1) : 0;
                    fileName = fileName.substring(startIndex, fileName.lastIndexOf("."));
                    fileName = fileName.replaceAll(" ", "");
                    fileName = fileName.replaceAll("/", "");
                    result = fileName + "_" + fileid + Ext;

                    File uploadFile = new File(destinationDirectory + "/" + result);
                    docTmpFI.write(uploadFile);
                    //                    fildoc(fileid, fileName, fileid + Ext, AuthHandler.getUserid(request), size);

                }
            }

        }
        //        catch (ConfigurationException ex) {
        //            Logger.getLogger(ExportImportContacts.class.getName()).log(Level.SEVERE, null, ex);
        //            throw ServiceException.FAILURE("ExportImportContacts.uploadDocument", ex);
        //        }
        catch (Exception ex) {
            Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
            throw ServiceException.FAILURE("ExportImportContacts.uploadDocument", ex);
        }
        return result;
    }

    /**
     * @param filename
     * @param delimiterType
     * @param startindex
     * @param importDao
     * @return
     * @throws ServiceException
     */
    public static void dumpCSVFileData(String filename, String delimiterType, int startindex, ImportDAO importDao,
            HibernateTransactionManager txnManager) throws ServiceException {
        boolean commitedEx = false;
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setName("import_Tx");
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus status = txnManager.getTransaction(def);
        try {
            CsvReader csvReader = null;
            FileInputStream fstream = null;
            String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "importplans";
            File csv = new File(destinationDirectory + "/" + filename);
            fstream = new FileInputStream(csv);
            csvReader = new CsvReader(new InputStreamReader(fstream), delimiterType);
            //            csvReader.readHeaders();
            String tableName = importDao.getTableName(filename);
            Session session = txnManager.getSessionFactory().getCurrentSession();
            int flushCounter = 0;
            while (csvReader.readRecord()) {
                ArrayList<String> dataArray = new ArrayList<String>();
                for (int i = 0; i < csvReader.getColumnCount(); i++) {
                    dataArray.add(cleanHTML(csvReader.get(i)));
                }
                importDao.dumpFileRow(tableName, dataArray.toArray());
                if (flushCounter % 30 == 0) {
                    session.flush();
                    session.clear();
                }
                flushCounter++;
            }
            try {
                txnManager.commit(status);
            } catch (Exception ex) {
                commitedEx = true;
                throw ex;
            }
        } catch (IOException ex) {
            throw ServiceException.FAILURE("dumpCSVFileData: " + ex.getMessage(), ex);
        } catch (Exception ex) {
            if (!commitedEx) { //if exception occurs during commit then dont call rollback
                txnManager.rollback(status);
            }
            throw ServiceException.FAILURE("dumpCSVFileData: " + ex.getMessage(), ex);
        }
    }

    /**
     * Generate the preview of the xls grid
     * @param filename
     * @param sheetNo
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     * @throws JSONException
     */
    public static JSONObject parseXLS(String filename, int sheetNo)
            throws FileNotFoundException, IOException, JSONException {
        JSONObject jobj = new JSONObject();
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
        HSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df);

        int startRow = 0;
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        int maxSheetColCnt = 0;
        int noOfRowsDisplayforSample = 20;
        if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
            noOfRowsDisplayforSample = sheet.getLastRowNum();
        }
        int firstValidRec = 0;
        JSONArray jArr = new JSONArray();
        try {
            for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
                HSSFRow row = sheet.getRow(i);
                JSONObject obj = new JSONObject();
                JSONObject jtemp1 = new JSONObject();
                if (row == null) {
                    continue;
                }
                if (i != 0 && firstValidRec == 0 && !jobj.has("Header")) // get first valid row which having some columns with data as a header
                    firstValidRec = i;

                //                    if(i==0) {
                maxCol = row.getLastCellNum();
                if (maxSheetColCnt < maxCol) // get max column count
                    maxSheetColCnt = maxCol;
                //                    }
                for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                    HSSFCell cell = row.getCell(cellcount);
                    CellReference cref = new CellReference(i, cellcount);
                    String colHeader = cref.getCellRefParts()[2];
                    String val = null;

                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                val = cell.toString();//Long.toString(cell.getDateCellValue().getTime());
                            } else {
                                val = dfmt.format(cell.getNumericCellValue());
                            }
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                            break;
                        }
                    }

                    if (i == firstValidRec) { // List of Headers (Consider first row as Headers)
                        if (val != null) {
                            jtemp1 = new JSONObject();
                            jtemp1.put("header", val == null ? "" : val);
                            jtemp1.put("index", cellcount);
                            jobj.append("Header", jtemp1);
                        }
                    }
                    obj.put(colHeader, val);
                }
                //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
                jArr.put(obj);
                //                    }
            }
        } catch (Exception ex) {
            Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        jobj.put("startrow", startRow);
        jobj.put("maxrow", maxRow);
        jobj.put("maxcol", maxSheetColCnt);
        jobj.put("index", sheetNo);
        jobj.put("data", jArr);
        jobj.put("filename", filename);

        jobj.put("msg", "XLS has been successfully uploaded");
        jobj.put("lsuccess", true);
        jobj.put("valid", true);
        return jobj;
    }

    /**
     * @param filename
     * @param sheetNo
     * @param startindex
     * @param importDao
     * @return
     * @throws ServiceException
     */
    public static void dumpXLSFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
            HibernateTransactionManager txnManager) throws ServiceException {
        boolean commitedEx = false;
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setName("import_Tx");
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus status = txnManager.getTransaction(def);
        Session session = txnManager.getSessionFactory().getCurrentSession();
        try {
            String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(sheetNo);
            //DateFormat sdf = new SimpleDateFormat(df_full);
            int maxRow = sheet.getLastRowNum();
            int maxCol = 0;
            String tableName = importDao.getTableName(filename);
            int flushCounter = 0;
            for (int i = startindex; i <= maxRow; i++) {
                HSSFRow row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (i == startindex) {
                    maxCol = row.getLastCellNum(); //Column Count
                }
                ArrayList<String> dataArray = new ArrayList<String>();
                JSONObject dataObj = new JSONObject();
                for (int j = 0; j < maxCol; j++) {
                    HSSFCell cell = row.getCell(j);
                    String val = null;
                    if (cell == null) {
                        dataArray.add(val);
                        continue;
                    }
                    String colHeader = new CellReference(i, j).getCellRefParts()[2];
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            val = Long.toString(cell.getDateCellValue().getTime());
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                    dataObj.put(colHeader, val);
                    dataArray.add(val); //Collect row data
                }
                //Insert Query
                if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                    importDao.dumpFileRow(tableName, dataArray.toArray());
                    if (flushCounter % 30 == 0) {
                        session.flush();
                        session.clear();
                    }
                    flushCounter++;
                }

            }
            try {
                txnManager.commit(status);
            } catch (Exception ex) {
                commitedEx = true;
                throw ex;
            }
        } catch (IOException ex) {
            throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
        } catch (Exception ex) {
            if (!commitedEx) { //if exception occurs during commit then dont call rollback
                txnManager.rollback(status);
            }
            throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
        }
    }

    /**
     * Generate the preview of the xls grid
     * @param filename
     * @param sheetNo
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     * @throws JSONException
     */
    public static JSONObject parseXLSX(String filename, int sheetNo)
            throws FileNotFoundException, IOException, JSONException {
        JSONObject jobj = new JSONObject();
        FileInputStream fs = new FileInputStream(filename);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df);

        int startRow = 0;
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        int noOfRowsDisplayforSample = 20;
        if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
            noOfRowsDisplayforSample = sheet.getLastRowNum();
        }

        JSONArray jArr = new JSONArray();
        try {
            for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
                XSSFRow row = sheet.getRow(i);
                JSONObject obj = new JSONObject();
                JSONObject jtemp1 = new JSONObject();
                if (row == null) {
                    continue;
                }
                if (i == 0) {
                    maxCol = row.getLastCellNum();
                }
                for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                    XSSFCell cell = row.getCell(cellcount);
                    CellReference cref = new CellReference(i, cellcount);
                    String colHeader = cref.getCellRefParts()[2];
                    String val = null;

                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                val = Long.toString(cell.getDateCellValue().getTime());
                            } else {
                                val = dfmt.format(cell.getNumericCellValue());
                            }
                            break;
                        case XSSFCell.CELL_TYPE_STRING:
                            val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                            break;
                        }
                    }

                    if (i == 0) { // List of Headers (Consider first row as Headers)
                        if (val != null) {
                            jtemp1 = new JSONObject();
                            jtemp1.put("header", val == null ? "" : val);
                            jtemp1.put("index", cellcount);
                            jobj.append("Header", jtemp1);
                        }
                    }
                    obj.put(colHeader, val);
                }
                //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
                jArr.put(obj);
                //                    }
            }
        } catch (Exception ex) {
            Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        jobj.put("startrow", startRow);
        jobj.put("maxrow", maxRow);
        jobj.put("maxcol", maxCol);
        jobj.put("index", sheetNo);
        jobj.put("data", jArr);
        jobj.put("filename", filename);

        jobj.put("msg", "XLSX has been successfully uploaded");
        jobj.put("lsuccess", true);
        jobj.put("valid", true);
        return jobj;
    }

    /**
     * @param filename
     * @param sheetNo
     * @param startindex
     * @param importDao
     * @return
     * @throws ServiceException
     */
    public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
            HibernateTransactionManager txnManager) throws ServiceException {
        boolean commitedEx = false;
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setName("import_Tx");
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus status = txnManager.getTransaction(def);
        Session session = txnManager.getSessionFactory().getCurrentSession();
        try {
            String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
            FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename);
            XSSFWorkbook wb = new XSSFWorkbook(fs);
            XSSFSheet sheet = wb.getSheetAt(sheetNo);
            //DateFormat sdf = new SimpleDateFormat(df_full);
            int maxRow = sheet.getLastRowNum();
            int maxCol = 0;
            String tableName = importDao.getTableName(filename);
            int flushCounter = 0;
            for (int i = startindex; i <= maxRow; i++) {
                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (i == startindex) {
                    maxCol = row.getLastCellNum(); //Column Count
                }
                ArrayList<String> dataArray = new ArrayList<String>();
                JSONObject dataObj = new JSONObject();
                for (int j = 0; j < maxCol; j++) {
                    XSSFCell cell = row.getCell(j);
                    String val = null;
                    if (cell == null) {
                        dataArray.add(val);
                        continue;
                    }
                    String colHeader = new CellReference(i, j).getCellRefParts()[2];
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            val = Long.toString(cell.getDateCellValue().getTime());
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                    dataObj.put(colHeader, val);
                    dataArray.add(val); //Collect row data
                }
                //Insert Query
                if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                    importDao.dumpFileRow(tableName, dataArray.toArray());
                    if (flushCounter % 30 == 0) {
                        session.flush();
                        session.clear();
                    }
                    flushCounter++;
                }

            }
            try {
                txnManager.commit(status);
            } catch (Exception ex) {
                commitedEx = true;
                throw ex;
            }
        } catch (IOException ex) {
            throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
        } catch (Exception ex) {
            if (!commitedEx) { //if exception occurs during commit then dont call rollback
                txnManager.rollback(status);
            }
            throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
        }
    }
}