com.pari.nm.utils.db.InventoryDBHelper.java Source code

Java tutorial

Introduction

Here is the source code for com.pari.nm.utils.db.InventoryDBHelper.java

Source

/**
 * Copyright (c) 2005 - 2006 Pari Networks, Inc.  All Rights Reserved.
 *
 * This software is the proprietary information of Pari Networks, Inc.
 *
 */

package com.pari.nm.utils.db;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import oracle.jdbc.OraclePreparedStatement;

import org.apache.activemq.util.ByteArrayInputStream;
import org.apache.commons.io.IOUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

import com.pari.api.customers.CustomerFault;
import com.pari.api.extdevice.ExternalDeviceRegDetails;
import com.pari.api.utils.HeuristicDescriptor;
import com.pari.api.utils.RegExpUtils;
import com.pari.api.utils.StringOperatorType;
import com.pari.api.utils.TrapActionDescriptor;
import com.pari.base.so.CatOSDevice;
import com.pari.base.so.CatOSSwitchModule;
import com.pari.base.so.CatOSVersion;
import com.pari.base.so.CiscoNetworkNodeInterface;
import com.pari.base.so.DAXDevice;
import com.pari.base.so.DAXVersion;
import com.pari.base.so.DiscoveredDevice;
import com.pari.base.so.IOSDevice;
import com.pari.base.so.IOSVersion;
import com.pari.base.so.IOSXRDevice;
import com.pari.base.so.IOSXRVersion;
import com.pari.base.so.MeruController;
import com.pari.base.so.MeruControllerVersion;
import com.pari.base.so.ModuleInfo;
import com.pari.base.so.ModuleType;
import com.pari.base.so.NetworkInterface;
import com.pari.base.so.NetworkNode;
import com.pari.base.so.NetworkNodeExt;
import com.pari.base.so.NetworkNodeTemp;
import com.pari.base.so.NxOSDevice;
import com.pari.base.so.NxOSVersion;
import com.pari.base.so.OSType;
import com.pari.base.so.PixDevice;
import com.pari.base.so.PixVersion;
import com.pari.base.so.Version;
import com.pari.base.so.WlcDevice;
import com.pari.base.so.WlcVersion;
import com.pari.base.so.defs.IPAddressBase;
import com.pari.common.historyserver.HistoryManager;
import com.pari.common.historyserver.HistoryRecord.Operation;
import com.pari.jelly.engine.communication.ISnmpColumn;
import com.pari.jelly.engine.communication.ISnmpScalar;
import com.pari.logger.PariLogger;
import com.pari.logger.PariLoggerFactory;
import com.pari.mw.api.ServerIfProvider;
import com.pari.nm.faults.FaultProfile;
import com.pari.nm.faults.FaultSeverity;
import com.pari.nm.faults.FaultSpec;
import com.pari.nm.faults.FaultSpecFactory;
import com.pari.nm.groups.rules.GroupRuleOperator;
import com.pari.nm.gui.guiservices.FtpServerDetails;
import com.pari.nm.gui.guiservices.PariException;
import com.pari.nm.gui.guiservices.VirtualDevice;
import com.pari.nm.modules.customers.Customer;
import com.pari.nm.modules.customers.CustomerInstance;
import com.pari.nm.modules.customers.CustomerManager;
import com.pari.nm.modules.customers.CustomerWingInstanceManager;
import com.pari.nm.modules.dsp.pojo.DSPCLICommandOutput;
import com.pari.nm.modules.dsp.pojo.DSPNetconfCommandOutput;
import com.pari.nm.modules.dsp.pojo.IDSPDeviceInfoProvider;
import com.pari.nm.modules.dsp.pojo.SnmpColumn;
import com.pari.nm.modules.dsp.pojo.SnmpMibData;
import com.pari.nm.modules.dsp.pojo.SnmpScalar;
import com.pari.nm.modules.inventory.CollectionProfile;
import com.pari.nm.modules.inventory.CollectionProfileNotificationPrefs;
import com.pari.nm.modules.inventory.DeviceModule;
import com.pari.nm.modules.jobs.JobMgr;
import com.pari.nm.modules.jobs.JobParameters;
import com.pari.nm.modules.jobs.PariJobDetails;
import com.pari.nm.modules.parser.config.AclElements;
import com.pari.nm.modules.parser.config.CliToObjectsBuilder;
import com.pari.nm.modules.parser.config.InterfaceElements;
import com.pari.nm.modules.parser.config.IpRouteData;
import com.pari.nm.modules.parser.config.MeruAccessPoint;
import com.pari.nm.modules.parser.config.ParseShowCommand;
import com.pari.nm.modules.parser.config.PixAccessListElement;
import com.pari.nm.modules.parser.config.PixSSHConfElement;
import com.pari.nm.modules.parser.config.PixSSHElements;
import com.pari.nm.modules.parser.config.RouterVlanElements;
import com.pari.nm.modules.parser.config.SwitchVlanElements;
import com.pari.nm.modules.parser.xmldefs.ShowCommandGrammer;
import com.pari.nm.modules.parser.xmldefs.ShowCommandGrammerBuilder;
import com.pari.nm.modules.session.CredentialSetFactory;
import com.pari.nm.modules.session.NetworkNodeCache;
import com.pari.nm.modules.session.NmIfImpl;
import com.pari.nm.modules.session.UserDetails;
import com.pari.nm.modules.session.UsersFactory;
import com.pari.nm.modules.snmp.MIBNodeType;
import com.pari.nm.modules.syslogd.DecodedSyslogMessage;
import com.pari.nm.modules.versioncontrol.ConfFileType;
import com.pari.nm.modules.versioncontrol.NewVersionControlManager;
import com.pari.nm.utils.ConfigIgnoreLines;
import com.pari.nm.utils.Constants;
import com.pari.nm.utils.MgmtReportsPaginationUtil;
import com.pari.nm.utils.ServerAuditConstants;
import com.pari.nm.utils.ServerAuditLog;
import com.pari.reports.custom.core.NetconfOutput;
import com.pari.server.LicenseValidator;
import com.pari.server.ServerProperties;
import com.pari.server.historyserver.CommandResult;
import com.pari.swim.archive.BackupSoftwareImageRequestProcessor;
import com.pari.utils.CompressionUtils;
import com.pari.utils.xml.XMLUtil;

public class InventoryDBHelper {
    private static PariLogger logger = PariLoggerFactory.getLogger("DB");
    private static final Object lock = new Object();

    private static final String SELECT_SYSLOG_ACTIONS = "select * from syslog_actions order by type";
    private static final String SELECT_TRAP_ACTIONS = "select * from snmp_trap_actions order by type";

    private static final String RESULTSET_CLOSE_ERROR = "Error while closing the resultset";
    private static final String CONNECTION_ERROR = "Error while connecting";
    private static final String DATASETNAME = "dataSetName";
    private static final String STATUS = "status";

    public static void deleteIPRouteEntries(ArrayList alist) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            ArrayList delList = new ArrayList();
            Iterator it = alist.iterator();

            while (it.hasNext()) {
                IpRouteData ipRouteData = (IpRouteData) it.next();
                int device_id = ipRouteData.getDeviceId();

                if (delList.contains(device_id)) {
                    continue;
                } else {
                    delList.add(device_id);
                }
            }

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.DELETE_IP_ROUTE);

            Iterator idel = delList.iterator();

            while (idel.hasNext()) {
                String ipAddr = (String) idel.next();
                ps.setString(1, ipAddr);
                ps.executeUpdate();
            }
        } catch (Exception e) {
            logger.warn("Error while deleting Ip Route Entries.", e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ex) {
            }
        }
    }

    /*
     * public static void deleteNeighborLink(int[] linkId) throws Exception { ResultSet rs = null;
     *
     * for ( int i = 0; i < linkId.length; i++ ) { try { rs =
     * DBHelper.executeQuery("select * from device_cdp_table where link_id=" + linkId[i]);
     *
     * if ( (rs != null) && rs.next() ) { String fromIpAddress = rs.getString("fromipaddress"); NetworkNode node =
     * NetworkNodeCache.getInstance().getLiveNode(fromIpAddress);
     *
     * if ( node == null ) { continue; }
     *
     * node.deleteNeighbor(linkId[i] + 1000);
     *
     * DBHelper.executeUpdate("delete from device_cdp_table where link_id=" + linkId[i]); } } catch ( Exception ee ) {
     * logger.warn("Error while deleting Neighbor Links.", ee); } finally { try { rs.close(); } catch ( Exception ee )
     * {} } } }
     */
    public static void deleteDevice(int devId) throws Exception {
        DBHelper.executeUpdate("delete from nodes where id=" + devId);
    }

    public static void deleteUnmanagedDevices() throws Exception {
        /***
         * Added for CSCuq85761:Removing an image from Image repository hangs the wizard Just temporary solution it
         * should be handled in better way by tuning schema
         * **/
        try {
            DBHelper.executeUpdate(
                    "delete from software_image_devices where device_id in (select device_id from nodes where state='r')");
        } catch (Exception e) {
            logger.error(
                    "Failed to delete software image and node/device mapping references for unmanaged devices.", e);
        }

        DBHelper.executeUpdate("delete from nodes where state='r'");
    }

    public static void removeOrUnManageDevice(int devId, boolean isDelete) throws Exception {
        if (isDelete) {
            DBHelper.executeUpdate("update nodes set state='r' where id=" + devId);
        } else {
            DBHelper.executeUpdate("update nodes set state='u' where id=" + devId);
        }
    }

    public static String getCapabilities(int device_id) {
        ResultSet rs = null;
        String caps = null;

        try {
            rs = DBHelper.executeQuery("select * from node_not_supp_caps where device_id=" + device_id);

            if ((rs != null) && rs.next()) {
                caps = rs.getString("NON_SUPP_CAP_LIST");
            }
        } catch (Exception ee) {
            logger.warn("Error while getting Capabilities for :", device_id, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }

        return caps;
    }

    public static void insertMlsQosTable(Map<Integer, Integer> map, String tableName, String col1, String col2,
            int deviceId) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            try {
                DBHelper.executeUpdate("delete from " + tableName + " where device_id=" + deviceId);
            } catch (Exception ee) {
                logger.info("Error while deleting " + tableName + " where device_id=" + deviceId, ee);
            }
            if (map == null || map.isEmpty()) {
                return;
            }
            c = DBHelper.getConnection();
            ps = c.prepareStatement(
                    "INSERT INTO " + tableName + " (device_id, " + col1 + ", " + col2 + ") VALUES (?,?,?)");
            for (Integer key : map.keySet()) {
                if (map.get(key) == null) {
                    continue;
                }
                ps.setInt(1, deviceId);
                ps.setInt(2, key);
                ps.setInt(3, map.get(key));
                ps.executeUpdate();
            }
        } catch (Exception e) {
            logger.warn("Error while inserting ip data into db:", deviceId, e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void insertIpDataIntoDB(ArrayList alist, int device_id) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            try {
                DBHelper.executeUpdate("delete from ip_route_table where device_id=" + device_id);
            } catch (Exception ee) {
                logger.info("Error while deleting ip_route_table where device_id=" + device_id, ee);
            }

            c = DBHelper.getConnection();

            Iterator it = alist.iterator();
            ps = c.prepareStatement(DBHelperConstants.INSERT_IP_ROUTE);

            while (it.hasNext()) {
                IpRouteData ipRouteData = (IpRouteData) it.next();
                // Bug 0004693 - Trying to insert NULL into database;
                if (ipRouteData == null || ipRouteData.getRouteType() == null || ipRouteData.getRoute() == null) {
                    logger.warn("One of the required element (id or route or routetype) is null");
                    continue;
                }
                // Bug 0004693 - Trying to insert NULL into database;
                ps.setInt(1, ipRouteData.getDeviceId());
                ps.setString(2, ipRouteData.getRouteType());
                ps.setString(3, ipRouteData.getRoute());
                ps.setString(4, ipRouteData.getPrefix());
                ps.setString(5, ipRouteData.getNextHop());
                ps.setString(6, ipRouteData.getIfName());
                ps.executeUpdate();
            }
        } catch (Exception e) {
            logger.warn("Error while inserting ip data into db:", device_id, e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void insertSwitchVlanIntoDB(ArrayList pElems, int device_id) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            try {
                DBHelper.executeUpdate("delete from device_show_l2_vlans where device_id=" + device_id);
            } catch (Exception ee) {
                logger.warn("Error while deleting from device_show_l2_vlans", device_id, ee);
            }
            c = DBHelper.getConnection();
            c.setAutoCommit(false);
            ps = c.prepareStatement(DBHelperConstants.INSERT_SWITCH_VLAN_DATA);
            Iterator it = pElems.iterator();
            while (it.hasNext()) {
                SwitchVlanElements elem = (SwitchVlanElements) it.next();

                ps.setString(1, elem.getVlanId());
                ps.setString(2, elem.getVlanName());
                ps.setString(3, elem.getVlanStatus());
                ps.setString(4, elem.getVlanPorts());
                ps.setInt(5, device_id);
                try {
                    ps.executeUpdate();
                } catch (Exception e) {
                    logger.warn("Exception while inserting switch vlan data \t" + elem.getVlanId() + "\t"
                            + elem.getVlanName() + "\t" + elem.getVlanStatus() + "\n");
                }
            }
        } catch (Exception e) {
            logger.warn("Exception while inserting switch vlan info into DB.", e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                c.commit();
            } catch (Exception e1) {
            }

            try {
                c.setAutoCommit(true);
            } catch (Exception ee) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static void insertPixSSHIntoDB(PixSSHElements pElems, int device_id) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            try {
                // device_pix_ssh_table is for access list
                DBHelper.executeUpdate("delete from device_pix_ssh_table where device_id=" + device_id);
            } catch (Exception ee) {
                logger.warn("Error while deleting from device_pix_ssh_table", device_id, ee);
            }

            try {
                // device_pix_ssh_table is for access list
                DBHelper.executeUpdate("delete from device_pix_ssh_state_table where device_id=" + device_id);
            } catch (Exception ee) {
                logger.warn("Error while deleting from device_pix_ssh_state_table", device_id, ee);
            }

            c = DBHelper.getConnection();
            c.setAutoCommit(false);

            // First insert state information into device_pix_ssh_state_table.
            ps = c.prepareStatement(DBHelperConstants.INSERT_PIX_SSH_STATE_DATA);
            ps.setInt(1, device_id);
            ps.setString(2, pElems.getpixSshState());
            ps.setString(3, pElems.getTimeout());
            ps.setString(4, pElems.getVersion());
            try {
                ps.executeUpdate();
            } catch (Exception e) {
                logger.warn("Exception while inserting pix ssh state data\t" + pElems.getpixSshState() + "\t"
                        + pElems.getTimeout() + "\t" + pElems.getVersion() + "\n");
            }

            try {
                ps.close();
            } catch (Exception e) {
            }
            // Now insert access list
            ps = c.prepareStatement(DBHelperConstants.INSERT_PIX_SSH_DATA);
            Iterator it = pElems.getpixSshConfigList().iterator();
            while (it.hasNext()) {
                PixSSHConfElement pelem = (PixSSHConfElement) it.next();
                ps.setInt(1, device_id);
                ps.setString(2, pelem.getConfIpAddr());
                ps.setString(3, pelem.getConfNetMaskAddr());
                ps.setString(4, pelem.getpixConfInOutData());
                try {
                    ps.executeUpdate();
                } catch (Exception e) {
                    logger.warn("Exception while inserting pix ssh data\t" + pelem.getConfIpAddr() + "\t"
                            + pelem.getConfNetMaskAddr() + "\t" + pelem.getpixConfInOutData() + "\n");
                }
            }

        } catch (Exception e) {
            logger.warn("Exception while inserting Pix SSH info into DB.", e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                c.commit();
            } catch (Exception e1) {
            }

            try {
                c.setAutoCommit(true);
            } catch (Exception ee) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static void insertAclIntoDB(ArrayList alist, int device_id) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            try {
                DBHelper.executeUpdate("delete from device_acl_table where device_id=" + device_id);
            } catch (Exception ee) {
                logger.warn("Error while deleting from device_acl_table", device_id, ee);
            }

            c = DBHelper.getConnection();
            c.setAutoCommit(false);

            Iterator it = alist.iterator();
            ps = c.prepareStatement(DBHelperConstants.INSERT_ACL_DATA);

            while (it.hasNext()) {
                AclElements aclElem = (AclElements) it.next();

                if (aclElem.getAclId().equals("-")) {
                    continue;
                }

                if ((aclElem != null) && (aclElem.getACLData().size() <= 0)) {
                    ps.setInt(1, device_id);
                    ps.setString(2, aclElem.getAclId());
                    if (aclElem.getAclId().trim().equalsIgnoreCase("s")) {
                        continue;
                    }
                    // System.err.println("Aclid\t" + aclElem.getAclId());
                    ps.setString(3, "0");
                    ps.setString(4, "0");
                    ps.setString(5, aclElem.getMatches());

                    try {
                        ps.executeUpdate();
                    } catch (Exception e) {
                        logger.warn("Exception while inserting aclid\t" + aclElem.getAclId());
                    }
                } else { // multiple values for data

                    Iterator datait = aclElem.getACLData().iterator();

                    while (datait.hasNext()) {
                        String aclData = (String) datait.next();
                        ps.setInt(1, device_id);
                        ps.setString(2, aclElem.getAclId());
                        // logger.warn("aclData\t"+ aclData);

                        String num = "0";
                        // Fix for matches field
                        try {
                            if (aclData != null && aclData.length() > 0) {
                                // Number from aclData
                                int nNum = -1;
                                if ((aclData.indexOf("permit") != -1) && (aclData.indexOf("Dynamic") != -1)) {
                                    nNum = aclData.indexOf("Dynamic");
                                    num = aclData.substring(0, nNum).trim();
                                    aclData = aclData.substring(nNum);
                                } else if ((nNum = aclData.indexOf("permit")) != -1) {
                                    num = aclData.substring(0, nNum).trim();
                                    aclData = aclData.substring(nNum);
                                }

                                if ((aclData.indexOf("deny") != -1) && (aclData.indexOf("Dynamic") != -1)) {
                                    nNum = aclData.indexOf("Dynamic");
                                    num = aclData.substring(0, nNum).trim();
                                    aclData = aclData.substring(nNum);
                                } else if ((nNum = aclData.indexOf("deny")) != -1) {
                                    num = aclData.substring(0, nNum).trim();
                                    aclData = aclData.substring(nNum);
                                }

                                if ((nNum = aclData.indexOf("evaluate")) != -1) {
                                    num = aclData.substring(0, nNum).trim();
                                    aclData = aclData.substring(nNum);
                                }
                                ps.setString(3, num);
                                // Matches
                                if (aclData.indexOf("matches") != -1) {
                                    int nIdx = aclData.indexOf("(");
                                    int nIdy = aclData.indexOf("matches");
                                    if (nIdx != -1 && nIdy != -1) {
                                        ps.setString(4, aclData.substring(0, nIdx));
                                        ps.setString(5, aclData.substring(nIdx + 1, nIdy));
                                    }
                                } else {
                                    ps.setString(4, aclData.trim());
                                    ps.setString(5, aclElem.getMatches());
                                }
                            }
                        } catch (Exception e) {
                            logger.warn("Exception while picking matches from acldata - falling back\t" + aclData);
                            ps.setString(3, num);
                            ps.setString(4, aclData.trim());
                            ps.setString(5, aclElem.getMatches());
                        }
                        // till this point

                        try {
                            ps.executeUpdate();
                        } catch (Exception e) {
                            logger.warn("Exception while inserting aclid\t" + aclElem.getAclId() + "\n"
                                    + "\tacldata\t" + aclData, e);
                        }
                    }
                }
            }
        } catch (Exception e) {
            logger.warn("Exception while inserting ACLS into DB.", e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                c.commit();
            } catch (Exception e1) {
            }

            try {
                c.setAutoCommit(true);
            } catch (Exception ee) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static void insertPixAclIntoDB(LinkedHashMap lmap, int device_id) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            try {
                DBHelper.executeUpdate("delete from device_acl_table where device_id=" + device_id);
            } catch (Exception ee) {
                logger.warn("Error while deleting from device_acl_table", device_id, ee);
            }

            c = DBHelper.getConnection();
            c.setAutoCommit(false);

            Iterator it = lmap.keySet().iterator();
            // ps = c.prepareStatement(DBHelperConstants.INSERT_PIX_ACL_DATA);
            ps = c.prepareStatement(DBHelperConstants.INSERT_ACL_DATA);

            // System.out.println("Inside insertPixAclIntoDB\t" +
            // lmap.toString());
            while (it.hasNext()) {
                String aclId = (String) it.next();
                ArrayList alist = (ArrayList) lmap.get(aclId);
                Iterator itr = alist.iterator();
                while (itr.hasNext()) {
                    PixAccessListElement pelem = (PixAccessListElement) itr.next();
                    // device_id, aclId, aceData, aclData, matches
                    ps.setInt(1, device_id);
                    ps.setString(2, aclId);
                    ps.setString(3, pelem.getAceData());
                    ps.setString(4, pelem.getAclData());
                    ps.setString(5, pelem.getMatches());
                    try {
                        ps.executeUpdate();
                    } catch (Exception e) {
                        logger.warn(
                                "Exception while inserting pix aclid\t" + aclId + "\t for \t" + pelem.getAclData());
                    }
                }
            }
        } catch (Exception e) {
            logger.warn("Exception while inserting Pix ACLS into DB.", e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                c.commit();
            } catch (Exception e1) {
            }

            try {
                c.setAutoCommit(true);
            } catch (Exception ee) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static void insertIntfStatsIntoDB(ArrayList alist, int device_id) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            try {
                DBHelper.executeUpdate("delete from device_intf_stats where device_id=" + device_id);
            } catch (Exception ee) {
                logger.info("error while deleting from device_intf_stats where device_id=" + device_id, ee);
            }

            c = DBHelper.getConnection();

            Iterator it = alist.iterator();
            ps = c.prepareStatement(DBHelperConstants.INSERT_INTF_DATA);

            while (it.hasNext()) {
                InterfaceElements intfElem = (InterfaceElements) it.next();
                String sTmp = intfElem.getIntfName();
                if ((sTmp != null) && sTmp.equalsIgnoreCase("-")) {
                    continue;
                }

                ps.setInt(1, device_id);
                ps.setString(2, intfElem.getIntfName());
                ps.setString(3, intfElem.getInPackets());
                ps.setString(4, intfElem.getInBytes());
                ps.setString(5, intfElem.getOutPackets());
                ps.setString(6, intfElem.getOutBytes());
                ps.setString(7, intfElem.getInPacketsPerSec());
                ps.setString(8, intfElem.getInBytesPerSec());
                ps.setString(9, intfElem.getOutPacketsPerSec());
                ps.setString(10, intfElem.getOutBytesPerSec());
                ps.setString(11, intfElem.getInputErrors());
                ps.setString(12, intfElem.getCRC());
                ps.setString(13, intfElem.getOutputErrors());
                ps.setString(14, intfElem.getOverRun());
                ps.setString(15, intfElem.getUnderRuns());
                ps.setString(16, intfElem.getRunts());
                ps.setString(17, intfElem.getGiants());
                ps.setString(18, intfElem.getLostCarrier());
                ps.setString(19, intfElem.getNoCarrier());
                ps.executeUpdate();
            }
        } catch (Exception e) {
            logger.warn("Error while inserting into device_intf_stats where device_id=" + device_id, e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void insertRouterVlanDataIntoDB(ArrayList alist, int device_id) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            try {
                DBHelper.executeUpdate("delete from device_show_l3_vlans where device_id=" + device_id);
            } catch (Exception ee) {
                logger.info("Error while deleting from device_show_l3_vlans where device_id=" + device_id, ee);
            }

            c = DBHelper.getConnection();

            Iterator it = alist.iterator();
            ps = c.prepareStatement(DBHelperConstants.INSERT_ROUTER_VLAN_DATA);

            while (it.hasNext()) {
                RouterVlanElements l3Elem = (RouterVlanElements) it.next();

                if (l3Elem == null) {
                    continue;
                }

                String sId = l3Elem.getvLanId();

                if ((sId != null) && (sId.indexOf("-") != -1)) {
                    continue;
                }

                if (sId != null && sId.equalsIgnoreCase("0")) {
                    continue;
                }

                ps.setString(1, sId);
                ps.setString(2, l3Elem.getvLanTrunkInterface());
                ps.setString(3, l3Elem.getvLanNative());
                ps.setString(4, l3Elem.getProtocol());
                ps.setString(5, l3Elem.getAddress());
                ps.setString(6, l3Elem.getDataRecieved());
                ps.setString(7, l3Elem.getDataTransmitted());
                ps.setInt(8, device_id);
                ps.executeUpdate();
            }
        } catch (Exception e) {
            logger.warn("Error while inserting into device_show_l3_vlans where device_id='" + device_id, e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void insertMeruAccessPointDataIntoDB(ArrayList alist, int device_id) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            try {
                DBHelper.executeUpdate("delete from lw_aps where device_id=" + device_id);
            } catch (Exception ee) {
                logger.info("Error while deleting from lw_aps where device_id=" + device_id, ee);
            }

            c = DBHelper.getConnection();

            Iterator it = alist.iterator();
            ps = c.prepareStatement(DBHelperConstants.INSERT_MERU_AP_DATA);

            // public static final String INSERT_INTO_MERU_AP_DATA =
            // "INSERT INTO  lw_aps(device_id, ap_name, ap_id, vendor, model, mac_address, up_time, state, availability, runtime, connectivity) VALUES(?,?,?,?,?,?,?,?,?,?,?)";

            while (it.hasNext()) {

                MeruAccessPoint map = (MeruAccessPoint) it.next();

                if (map == null) {
                    continue;
                }

                ps.setInt(1, device_id);
                ps.setString(2, map.getApName());
                ps.setString(3, map.getApId());
                ps.setString(4, "Meru Networks Inc"); // TBD
                ps.setString(5, map.getModel());
                ps.setString(6, map.getMacAddress());
                ps.setString(7, map.getUpTime());
                ps.setString(8, map.getState());
                ps.setString(9, map.getAvailability());
                ps.setString(10, map.getRuntime());
                ps.setString(11, map.getConnectvity());
                ps.executeUpdate();
            }
        } catch (Exception e) {
            logger.warn("Error while inserting into lw_aps where device_id='" + device_id, e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static boolean vendorMatches(String input, String vendorName) {
        vendorName = vendorName.toLowerCase();
        input = input.toLowerCase();
        if (input.equals(vendorName)) {
            return true;
        }
        if (!input.contains(vendorName)) {
            return false;
        }
        if (input.matches(vendorName + " .*systems.*")) {
            return true;
        }
        if (input.matches(vendorName + " .*networks.*")) {
            return true;
        }
        return false;
    }

    public static NetworkNode insertVirtualDevice(String nodeName, String vendor, String deviceType,
            String versionStr, String osName, String runConf, NetworkInterface[] nfs) throws Exception {
        NetworkNode node = null;
        Version ver = null;

        OSType osType = OSType.getFromString(osName);
        if (osType == null) {
            throw new Exception("Unknown OS Type: " + osName);
        }
        System.out.println("Adding virtual device: " + nodeName + " OSTYpe: " + osType);
        switch (osType) {
        case CISCO_CATOS:
            node = new CatOSDevice();
            ver = new CatOSVersion();
            break;
        case CISCO_FWSM:
            node = new PixDevice();
            ver = new PixVersion();
            ver.setDeviceType("FWSM");
            break;
        case CISCO_IOSXR:
            node = new IOSXRDevice();
            ver = new IOSXRVersion();
            break;
        case CISCO_IOS:
            node = new IOSDevice();
            ver = new IOSVersion();
            break;
        case CISCO_PIX:
            node = new PixDevice();
            ver = new PixVersion();
            if (deviceType.toLowerCase().contains("asa")) {
                ver.setDeviceType("ASA");
            }
            break;
        case CISCO_NXOS:
            node = new NxOSDevice();
            ver = new NxOSVersion();
            break;
        case CISCO_WLC:
            node = new WlcDevice();
            ver = new WlcVersion();
            break;
        case MERU_CONTROLLER:
            node = new MeruController();
            ver = new MeruControllerVersion();
            break;
        case DAX_DEVICE:
            node = new DAXDevice();
            ver = new DAXVersion();
            break;
        default:
            throw new Exception("Devices of OS Type: " + osType.shortDisplayStr
                    + " are not supported to be added through API right now.");
        }
        node.setVersion(ver);
        node.setDeviceMode(Constants.VIRTUAL_DEVICE);
        node.setVendorName(vendor);
        if (deviceType != null) {
            String lc = deviceType.toLowerCase();
            if (lc.startsWith("router")) {
                deviceType = "Routers";
            } else if (lc.startsWith("switch")) {
                deviceType = "Switches";
            } else if (lc.startsWith("firewall")) {
                deviceType = "Security";
            } else if (lc.startsWith("accesspoint")) {
                deviceType = "Wireless";
            }
        }
        node.setProductFamily(deviceType);

        String uniqIp = InventoryDBHelper.getUniqIp();
        node.setIpAddr(IPAddressBase.parseIPAddressString(uniqIp));
        node.setNodeName(nodeName);

        String version = null;

        if ((versionStr != null) && (versionStr.length() > 0)) {
            version = versionStr;
        } else {
            version = InventoryDBHelper.retrieveVersionStr(runConf).trim();
        }

        com.pari.services.def.version.IOSVersion iosVersion = com.pari.services.def.version.IOSVersion
                .parseString(version);
        if (iosVersion == null) {
            throw new Exception("Unable to parse version: " + version);
        }
        switch (osType) {
        case CISCO_CATOS:
            ((CatOSVersion) ver).setIosVersion(iosVersion);
            break;
        case CISCO_FWSM:
        case CISCO_PIX:
            ver.setIosVersion(iosVersion);
            break;
        case CISCO_IOSXR:
            ver.setIosVersion(iosVersion);
            break;
        case CISCO_IOS:
            ((IOSVersion) ver).setIosVersion(iosVersion);
            break;
        case CISCO_NXOS:
            ver.setIosVersion(iosVersion);
            break;
        case CISCO_WLC:
            ver.setIosVersion(iosVersion);
            break;
        default:
            throw new Exception("Devices of OS Type: " + osType.shortDisplayStr
                    + " are not supported to be added through API right now.");
        }

        ver.setVersionStr(version);
        ver.setDeviceType(deviceType);
        ver.setNodeName(nodeName);
        node.setVersion(ver);

        InventoryDBHelper.retrieveNetworkInterfaces(runConf, node);
        NetworkNodeTemp tempNode = new NetworkNodeTemp();
        tempNode.setRunConfig(runConf);
        InventoryDBHelper.updateDevice(node, tempNode);
        NetworkNodeCache.getInstance().addDevice(node);
        return node;
    }

    public static void updateDevice(NetworkNode device, NetworkNodeTemp tempNode) throws Exception {
        int updateId = -1;
        String deviceIp = device.getIpAddr().toString();

        NetworkNode node = NetworkNodeCache.getInstance().getNode(deviceIp);

        if ((node != null) && (node.getIpAddr().toString() != deviceIp)) {
            deviceIp = node.getIpAddr().toString();
            updateId = node.getNodeId();
        }

        updateDeviceDetails(device, true, deviceIp, updateId == -1 ? false : true, updateId, false);
        updateIosVersionDetails(device);

        if (tempNode != null) {
            updateDeviceIfDetails(device, tempNode.getNetworkInterfaces());
            updateDeviceConfig(device, tempNode.getRunConfig(), tempNode.getStartConfig());
        }
    }

    public static void updateDeviceConfig(NetworkNode device, String runConf, String startupConf) {
        if (runConf != null) {
            updateConfigInternal(device, Constants.RUN_CONF, runConf);
        }
        if (startupConf != null) {
            updateConfigInternal(device, Constants.STARTUP_CONF, startupConf);
        }
    }

    private static void updateConfigInternal(NetworkNode device, int confType, String config) {
        String confTypeStr = (confType == Constants.RUN_CONF) ? " running " : " startup ";
        // logger.debug("Updating " + confTypeStr + "  config for node: " +
        // device.getNodeId() + " IP:" +
        // device.getIpAddr());
        if (config == null || config.isEmpty()) {
            logger.debug("No " + confTypeStr + " conf found for node: " + device.getNodeId() + " IP:"
                    + device.getIpAddr() + ". Skipping");
            return;
        }
        try {
            deleteConfig(device, confType);
        } catch (Exception ex) {
            logger.error("Exception while deleting " + confTypeStr + " config for node: " + device.getNodeId()
                    + " IP:" + device.getIpAddr() + " can not update.", ex);
            // Try and insert it anyway.
        }
        // logger.debug("Inserting " + confTypeStr + "  config for node: " +
        // device.getNodeId() + " IP:" +
        // device.getIpAddr());
        insertConfig(device.getNodeId(), confType, config);
    }

    public static void insertConfig(Integer nodeId, int confType, String config) {
        PreparedStatement ps = null;
        Connection c = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("insert into node_current_config (id, conf_type, config) VALUES (?,?,?)");
            ps.setInt(1, nodeId);
            ps.setInt(2, confType);
            byte b[] = CompressionUtils
                    .compressInputStream(new ByteArrayInputStream(config.getBytes(Charset.forName("UTF-8"))));
            ps.setObject(3, b);
            ps.executeUpdate();
        } catch (Exception ex) {
            logger.error("Exception while inserting " + confType + " configuration for node: " + nodeId
                    + " can not update.", ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception ex) {
            } finally {
                if (c != null) {
                    DBHelper.releaseConnection(c);
                }
            }
        }

    }

    private static void deleteConfig(NetworkNode device, int confType) throws SQLException {
        String confTypeStr = (confType == Constants.RUN_CONF) ? " running " : " startup ";
        logger.debug("Deleting " + confTypeStr + " config for node: " + device.getNodeId() + " IP:"
                + device.getIpAddr());
        String query = "delete from node_current_config where id=" + device.getNodeId() + " and conf_type="
                + confType;
        DBHelper.executeUpdate(query);
    }

    private static void deleteConfigOld(int nodeId, int confType) throws SQLException {
        String confTypeStr = (confType == Constants.RUN_CONF) ? " running " : " startup ";
        logger.debug("Deleting " + confTypeStr + " config for node: " + nodeId);
        String query = "delete from ios_run_conf where id=" + nodeId + " and conf_type=" + confType;
        DBHelper.executeUpdate(query);
    }

    private static void dumpFullIosVersion(NetworkNode device) {
        PreparedStatement ps = null;
        Connection c = null;

        if (device == null || device.getVersion() == null || device.getVersion().getShowVersion() == null
                || device.getVersion().getShowVersion().trim().length() == 0) {
            return;
        }

        try {
            String query = "delete from version_dump where id=" + device.getNodeId();

            DBHelper.executeUpdate(query);

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.IOS_VERSION_DUMP_INSERT);

            String versionStr = device.getVersion().getShowVersion();

            int size = versionStr.length();

            // TODO fix the hardcoding
            int noOfDBRows = size / 4000;

            if ((noOfDBRows * 4000) < size) {
                noOfDBRows += 1;
            }
            for (int i = 0; i < noOfDBRows; i++) {
                int startIndex = i * 4000;
                int endIndex = (i + 1) * 4000;

                if (endIndex > size) {
                    endIndex = size;
                }

                String conf = versionStr.substring(startIndex, endIndex);
                ps.setInt(1, device.getNodeId());
                ps.setInt(2, i);
                ps.setString(3, conf);

                ps.executeUpdate();
            }
        } catch (Exception ee) {
            logger.warn("Error while inserting into version_dump where id=" + device.getNodeId(), ee);
        } finally {

            try {
                ps.close();
            } catch (Exception e) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static Map<String, String> getComputeDeviceDetails(int deviceid) {
        Map<String, String> details = new HashMap<String, String>();
        ResultSet rs = null;
        if (deviceid != 0) {
            try {
                String query = "select * from nodes_compute where id=" + deviceid;

                rs = DBHelper.executeQuery(query);
                while (rs.next()) {
                    details.put("AvailableMemory", rs.getString("availableMemory"));
                    details.put("MemorySpeed", rs.getString("memorySpeed"));
                    details.put("TotalMemory", rs.getString("totalMemory"));
                    details.put("NumOfAdaptors", rs.getString("numOfAdaptors"));
                    details.put("NumOfCores", rs.getString("numOfCores"));
                    details.put("NumOfCpus", rs.getString("numOfCpus"));
                    details.put("NumOfThreads", rs.getString("numOfThreads"));
                }
            } catch (Exception d) {
                logger.error("Error while fetching Compute Device details ", d);

            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {

                        e.printStackTrace();
                    }
                }
            }
        }
        return details;
    }

    public static void updateComputeDetails(IDSPDeviceInfoProvider device, int deviceid) {
        PreparedStatement ps = null;
        Connection c = null;
        ResultSet rs = null;
        Map<String, Object> computeMap = device.getComputeAttributes();

        logger.debug("Compute map details = " + computeMap.entrySet().toString());
        try {
            String query = "select id,os from nodes_compute where id=" + deviceid;

            rs = DBHelper.executeQuery(query);

            if ((rs != null) && rs.next()) {
                logger.debug("Executing update for compute device id = " + deviceid);
                c = DBHelper.getConnection();
                logger.debug("Statement = " + DBHelperConstants.NODE_COMPUTE_UPDATE);
                ps = c.prepareStatement(DBHelperConstants.NODE_COMPUTE_UPDATE);

                ps.setString(1, device.getIpAddress());
                ps.setString(2, device.getOsName());
                ps.setString(3, device.getDeviceFamily());
                ps.setString(4, device.getAvailableMemory());
                ps.setString(5, device.getLowVoltageMemory());
                ps.setString(6, device.getMemorySpeed());
                ps.setString(7, device.getName());
                ps.setString(8, device.getNumOfAdaptors());
                ps.setString(9, device.getNumOfCores());
                ps.setString(10, device.getNumOfCoresEnabled());
                ps.setString(11, device.getNumOfCpus());
                ps.setString(12, device.getNumOfEthHostIfs());
                ps.setString(13, device.getNumOfEthHostIfs());
                ps.setString(14, device.getNumOfThreads());
                ps.setString(15, device.getOperability());
                ps.setString(16, device.getOriginalUuid());
                ps.setString(17, device.getPartNumber());
                ps.setString(18, device.getRevision());
                ps.setString(19, device.getServerId());
                ps.setString(20, device.getTotalMemory());
                ps.setString(21, device.getFsmProgr());
                ps.setTime(22, null);
                ps.setTime(23, null);
                ps.setTime(24, null);
                ps.setString(25, device.getUuid());
                ps.setString(26, device.getVid());
                ps.setInt(27, deviceid);
                ps.executeUpdate();
            } else {

                c = DBHelper.getConnection();
                ps = c.prepareStatement(DBHelperConstants.NODE_COMPUTE_INSERT);
                ps.setInt(1, deviceid);
                ps.setString(2, device.getIpAddress());
                ps.setString(3, device.getOsName());
                ps.setString(4, device.getDeviceFamily());
                ps.setString(5, device.getAvailableMemory());
                ps.setString(6, device.getLowVoltageMemory());
                ps.setString(7, device.getMemorySpeed());
                ps.setString(8, device.getName());
                ps.setString(9, device.getNumOfAdaptors());
                ps.setString(10, device.getNumOfCores());
                ps.setString(11, device.getNumOfCoresEnabled());
                ps.setString(12, device.getNumOfCpus());
                ps.setString(13, device.getNumOfEthHostIfs());
                ps.setString(14, device.getNumOfEthHostIfs());
                ps.setString(15, device.getNumOfThreads());
                ps.setString(16, device.getOperability());
                ps.setString(17, device.getOriginalUuid());
                ps.setString(18, device.getPartNumber());
                ps.setString(19, device.getRevision());
                ps.setString(20, device.getServerId());
                ps.setString(21, device.getTotalMemory());
                ps.setString(22, device.getFsmProgr());
                ps.setTime(23, null);
                ps.setTime(24, null);
                ps.setTime(25, null);
                ps.setString(26, device.getUuid());
                ps.setString(27, device.getVid());
                ps.executeUpdate();
            }

        } catch (Exception e) {
            logger.error("Error while updating Compute Details ", e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();

                    DBHelper.releaseConnection(c);

                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ee) {
                logger.error("Error while closing connections ", ee);
            }

        }
    }

    public static boolean updateDeviceDetails(NetworkNode device, boolean manage, String deviceKnownIp,
            boolean updatevirtualDevice, int updateDeviceId, boolean discoveryJob) {
        PreparedStatement ps = null;
        Connection c = null;
        int deviceId = updateDeviceId;
        try {
            c = DBHelper.getConnection();
            c.setAutoCommit(false);
            boolean insert = true;

            if (updatevirtualDevice) {
                insert = false;
                ps = c.prepareStatement(DBHelperConstants.NODE_UPDATE);
            } else {
                ps = c.prepareStatement(DBHelperConstants.NODE_INSERT);
            }
            Timestamp ts = null;
            // CSCtr59009 -Bug fix - Fix for timestamp issue of discovered
            // devices.
            if (device.getDiscoveredTime() > 0) {
                ts = new Timestamp(device.getDiscoveredTime());
            } else {
                synchronized (lock) {
                    // Even though synchronization here looks unnecessary, all
                    // we are trying to do
                    // is to gaurantee a distinct timestamp value across
                    // multiple threads in which
                    // updateDeviceDetails() method is invoked
                    ts = new Timestamp(System.currentTimeMillis());
                    Thread.sleep(1);
                }
            }
            if (insert) {
                // logger.debug("Before inserting before device: " +
                // deviceKnownIp);
                ps.setString(1, deviceKnownIp);
                // ps.setInt(2, device.getVersion().getVersionType().typeInt);
                ps.setInt(2, device.getVersion().getOsTypeDetails().getOsId());
                // ps.setString(3, device.getVersion().getDeviceType());

                // Setting Device Family as family
                ps.setString(3, device.getVersion().getDeviceFamily());
                ps.setString(4, device.getVendorName());
                ps.setString(5, manage ? "m" : "u");

                ps.setTimestamp(6, ts);
                ps.setInt(7, device.getDeviceMode());
                ps.setString(8, device.getSysObjectID());
                // Setting ShowFamilyAs as Product_Family
                ps.setString(9, device.getVersion().getShowFamilyAs());
                ps.setString(10, device.getVersion().getOsTypeDetails().getOsAlias());
                ps.setString(11, device.getProductId());
            } else {
                // ps.setString(1, device.getVersion().getDeviceType());

                // Setting Device Family as family
                ps.setString(1, device.getVersion().getDeviceFamily());
                ps.setString(2, device.getVendorName());
                ps.setString(3, manage ? "m" : "u");
                // ps.setInt(4, device.getVersion().getVersionType().typeInt);
                ps.setInt(4, device.getVersion().getOsTypeDetails().getOsId());
                ps.setInt(5, device.getDeviceMode());
                ps.setTimestamp(6, ts); // CSCtr59009 - updating timestamp also
                if (device.getSysObjectID() != null && !device.getSysObjectID().isEmpty()) {
                    ps.setString(7, device.getSysObjectID());
                } else {
                    ps.setString(7, null);
                }
                // Setting ShowFamilyAs as Product_Family
                ps.setString(8, device.getVersion().getShowFamilyAs());
                ps.setString(9, device.getVersion().getOsTypeDetails().getOsAlias());
                ps.setString(10, device.getProductId());
                ps.setInt(11, deviceId);

            }
            int id = -1;
            if (insert) {
                synchronized (lock) {
                    ps.executeUpdate();
                    id = getCurrentMaxNodeId(c, device);
                }
            } else {
                ps.executeUpdate();
            }

            if (insert) {
                // logger.debug("Just inserted device: " + deviceKnownIp);
                if (id >= 0) {
                    logger.debug("Setting nodeId: " + id + " for device: " + deviceKnownIp);
                    device.setNodeId(id);
                    /*
                     * DeviceWeightage weightage = DeviceWeightageManager.getInstance ().getWeightage(device);
                     * device.setWeightageObj(weightage);
                     */
                    if (!discoveryJob) {
                        BackupSoftwareImageRequestProcessor.getInstance().processRequest(id);
                        // logger.debug("Device :"+id+" is added to Automatic Backup Job.... ");
                    }
                } else {
                    throw new Exception("Unable to get new device ID for device: " + deviceKnownIp);
                }
                /*
                 * try {
                 *
                 * PreparedStatement ps1 = null; // rs = DBHelper.executeQueryNoCommit(c,
                 * "select max(id) as id from nodes"); ps1 = c.prepareStatement(DBHelperConstants.NODE_DISOVERED);
                 * ps1.setTimestamp(1, ts); rs = ps1.executeQuery();
                 *
                 * if ((rs != null) && rs.next()) { int id = rs.getInt("ID");
                 *
                 * device.setNodeId(id); } } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps1.close();
                 * } catch (Exception ex) {} }
                 */
            } else {
                device.setNodeId(deviceId);
            }
            c.commit();
        } catch (Exception ee) {
            ee.printStackTrace();
            logger.warn("Error while updating deviceDetails for the deviceId " + updateDeviceId, ee);
            try {
                if (c != null) {
                    c.rollback();
                }
            } catch (Exception e) {
            }
            return false;
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception ee) {
            }

            try {
                if (c != null) {
                    c.setAutoCommit(true);
                }
            } catch (Exception e) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
        return true;
    }

    private static int getCurrentMaxNodeId(Connection c, NetworkNode device) throws Exception {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(c, "select max(id) as id from nodes");
            if ((rs != null) && rs.next()) {
                return rs.getInt("id");
            }
        } catch (Exception ex) {
            logger.error(
                    "Exception while getting the node id of the node just inserted (" + device.getIpAddr() + ")",
                    ex);
            throw ex;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex1) {
                }
            }
        }
        return -1;
    }

    public static void updateIpToIdDetails(NetworkNode device) {
        PreparedStatement ps = null;
        Connection c = null;
        int id = device.getNodeId();
        HashMap<String, Integer> map = new HashMap<String, Integer>();
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery("select * from ip_to_id where id=" + id);

            while (rs.next()) {
                map.put(rs.getString("ipaddress"), rs.getInt("id"));
            }
        } catch (Exception ee) {
            logger.warn("Error while updating Ip To Id details for the device= " + id, ee);
        }

        try {
            rs.close();
        } catch (Exception ee) {
        }

        try {
            ArrayList alist = device.getAllInterfaces();
            Iterator it = alist.iterator();
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_IP_TO_ID);

            while (it.hasNext()) {
                NetworkInterface nIf = (NetworkInterface) it.next();

                if (nIf.getIpAddress() == null) {
                    continue;
                }

                String ip = nIf.getIpAddress();

                try {
                    if (rs != null) {
                        rs.close();
                    }
                } catch (Exception ee) {
                }

                rs = DBHelper
                        .executeQuery("select id from ip_to_id where id=" + id + " AND ipaddress='" + ip + "'");

                if ((rs != null) && rs.next()) {
                    int devId = rs.getInt("ID");

                    if (devId != id) {
                        try {
                            DBHelper.executeUpdate(
                                    "delete from ip_to_id where id=" + id + " AND ipaddress='" + ip + "'");
                        } catch (Exception ee) {
                            logger.warn(
                                    "Error while deleting from ip_to_id where id=" + id + " AND ipaddress='" + ip,
                                    ee);
                        }
                    } else {
                        map.remove(ip);

                        continue;
                    }
                }

                try {
                    ps.setString(1, ip);
                    ps.setInt(2, id);
                    ps.executeUpdate();
                } catch (Exception e) {
                    logger.warn("Error while inserting into ip_to_id where id=" + id + " AND ipaddress='" + ip, e);
                }
            }

            // insert the device ip (in hsrp case none of the device interfaces
            // will have the hsrp ip address
            try {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (Exception ee) {
                    }
                }

                rs = DBHelper.executeQuery(
                        "select * from ip_to_id where ipaddress='" + device.getIpAddr().toString() + "'");

                if ((rs == null) || !rs.next()) {
                    try {
                        ps.setString(1, device.getIpAddr().toString());
                        ps.setInt(2, id);
                        ps.executeUpdate();
                        map.remove(device.getIpAddr().toString());
                    } catch (Exception ee) {
                        logger.warn("Error while querying for ip to id", ee);
                    }
                } else {
                    map.remove(device.getIpAddr().toString());
                }
            } catch (Exception ee) {
                logger.warn("Error in updateIpToIdDetails", ee);
            }

            for (String ip : map.keySet()) {
                try {
                    DBHelper.executeUpdate("delete from ip_to_id where ipaddress='" + ip + "'");
                } catch (Exception ee) {
                }
            }
        } catch (Exception ee) {
            logger.warn("Error in updateIpToIdDetails", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                rs.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void updateDeviceIfDetails(NetworkNode device, NetworkInterface[] nfs) {
        PreparedStatement ps = null;
        PreparedStatement ps1 = null;
        Connection c = null;

        try {

            String query = "delete from ios_if where id=" + device.getNodeId();
            DBHelper.executeUpdate(query);

            if (device instanceof PixDevice) {
                query = "delete from pix_if where id=" + device.getNodeId();
                DBHelper.executeUpdate(query);

            }
            if (nfs == null || nfs.length == 0) {
                return;
            }
            c = DBHelper.getConnection();
            if (device instanceof PixDevice) {
                ps1 = c.prepareStatement(DBHelperConstants.PIXNODE_IF_INSERT);
            }
            ps = c.prepareStatement(DBHelperConstants.IOSNODE_IF_INSERT);

            // ArrayList alist = device.getAllInterfaces();
            for (NetworkInterface nIf : nfs) {

                //
                // Iterator it = alist.iterator();
                //
                // while ( it.hasNext() )
                // {
                try {
                    // NetworkInterface nIf = (NetworkInterface) it.next();
                    ps.setInt(1, device.getNodeId());
                    ps.setString(2, nIf.getIfName());
                    ps.setString(3, nIf.getInterfaceTypeName());

                    if (nIf.getMacAddress() != null) {
                        ps.setString(4, nIf.getMacAddress().toDottedString());
                    } else {
                        ps.setString(4, null);
                    }

                    ps.setInt(5, nIf.getSlot());
                    ps.setLong(6, nIf.getPort());
                    ps.setLong(7, nIf.getSubPort());
                    ps.setInt(8, nIf.getIfSpeed());
                    ps.setInt(9, nIf.getMtu());

                    if (nIf.getIpAddress() == null) {
                        ps.setString(10, null);
                    } else {
                        ps.setString(10, nIf.getIpAddress().toString());
                    }

                    if (nIf.getNetMask() == null) {
                        ps.setString(11, null);
                    } else {
                        ps.setString(11, nIf.getNetMask().toString());
                    }

                    String aStatus = null; // (nIf.getIfAdminStatus() == 1) ?
                    // "up" : "down";

                    if (nIf.getIfAdminStatus() == 0) {
                        aStatus = "down";
                    } else if (nIf.getIfAdminStatus() == 1) {
                        aStatus = "up";
                    } else if (nIf.getIfAdminStatus() == 2) {
                        aStatus = "administratively down";
                    }

                    String oStatus = null; // (nIf.getIfOperStatus() == 1) ?
                    // "up" : "down";

                    if (nIf.getIfOperStatus() == 0) {
                        oStatus = "down";
                    } else if (nIf.getIfOperStatus() == 1) {
                        oStatus = "up";
                    } else if (nIf.getIfOperStatus() == 2) {
                        oStatus = "administratively down";
                    }

                    ps.setString(12, aStatus);
                    ps.setString(13, oStatus);
                    ps.setString(14, nIf.getMediaType());
                    ps.executeUpdate();

                    if (ps1 != null) {
                        ps1.setInt(1, device.getNodeId());
                        ps1.setString(2, nIf.getIfName());
                        ps1.setString(3, nIf.getIfNameAlias());
                        ps1.setInt(4, nIf.getSecurityLevel());
                        ps1.executeUpdate();

                    }
                } catch (Exception e) {
                }
            }
        } catch (Exception ee) {
            logger.warn("Error in update device if details", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                if (ps1 != null) {
                    ps1.close();
                }
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void updateIosVersionDetails(NetworkNode device) throws Exception {
        PreparedStatement ps = null;
        Connection c = null;

        try {

            c = DBHelper.getConnection();
            c.setAutoCommit(false);
            String query = "delete from ios_version where id=" + device.getNodeId() + "";
            DBHelper.executeUpdateNoCommit(c, query);

            ps = c.prepareStatement(DBHelperConstants.IOS_VERSION_INSERT);

            Version ver = device.getVersion();
            ps.setInt(1, device.getNodeId());
            ps.setString(2, ver.getVersionStr());

            ps.setString(3, ver.getBuild());
            ps.setString(4, ver.getProductId());
            ps.setString(5, ver.getDeviceType());
            ps.setString(6, ver.getNodeName());
            ps.setString(7, ver.getFlash());
            ps.setString(8, ver.getMemory());
            ps.setString(9, ver.getSerialNumber());
            ps.setString(10, ver.getImageFileName());

            ps.executeUpdate();
            c.commit();
        } catch (Exception ee) {
            System.err.println("NODE ID:" + device.getNodeId());
            ee.printStackTrace();
            c.rollback();
            logger.warn("Error in updateIosVersionDetails", ee);
            throw ee;
        } finally {
            try {
                c.setAutoCommit(true);
            } catch (Exception ex) {
            }
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }

        dumpFullIosVersion(device);
    }

    public static void updateCapabilities(int device_id, String cap) {
        PreparedStatement ps = null;
        Connection c = null;

        try {
            String query = "delete from node_not_supp_caps where device_id=" + device_id;
            DBHelper.executeUpdate(query);

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.NODE_CAPABILITIES_INSERT);

            ps.setInt(1, device_id);
            ps.setString(2, cap);
            if (cap.length() > 4000) {
                logger.error("Size of cap for deviceID: " + device_id + " = " + cap.length());
                logger.error(cap);
            }
            ps.executeUpdate();
        } catch (Exception ee) {
            logger.warn("Error while updating the capabilities for the device=" + device_id, ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static int getNodeId(String ipAddress) {
        int nodeId = -1;
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery("select ID from nodes where ipaddress='" + ipAddress + "'");

            if (rs.next()) {
                nodeId = rs.getInt("ID");
            }
        } catch (Exception ee) {
            logger.info("Error while selecting from nodes for the ip address: " + ipAddress, ee);

            try {
                rs.close();
            } catch (Exception e) {
            }
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }

        return nodeId;
    }

    public static boolean checkExistenceInDB(int nodeId) {
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery("select * from nodes where id=" + nodeId);

            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ee) {
            logger.info("Error while checking node existence: " + nodeId, ee);

            try {
                rs.close();
            } catch (Exception e) {
            }
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        return false;
    }

    public static void deleteDeviceHistory(int customerId, String ipAddress) {
        try {
            DBHelper.executeUpdate("delete from unmanaged_devices where customer_id=" + customerId
                    + " and device_ip='" + ipAddress + "'");
        } catch (SQLException e) {
            logger.error("Failed to delete device history for unmanaged devices.", e);
        }
    }

    public static boolean checkUnmanagedDeviceInDB(int nodeId) {
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery("select state from nodes where id=" + nodeId);

            if (rs.next()) {
                String state = rs.getString("state");
                if (state.equals("u")) {
                    return true;
                }
            }

        } catch (Exception ee) {
            logger.info("Error while checking node existence: " + nodeId, ee);

            try {
                rs.close();
            } catch (Exception e) {
            }
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        return false;
    }

    public static NetworkNode[] loadDevices() {
        return ((new InventoryLoader()).loadDevices());
        /*
         * NetworkNode[] nodes = null; NetworkNode node = null; ArrayList<NetworkNode> lis = new
         * ArrayList<NetworkNode>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from nodes");
         *
         * while ( rs.next() ) { if ( !rs.getString("state").equals("m") ) { continue; }
         *
         * String vendor = rs.getString("vendor"); int id = rs.getInt("ID");
         *
         * int deviceMode = rs.getInt("device_type");
         *
         * int osType = rs.getInt("os"); OSType os = OSType.getFromInt(osType); node = VersionFactory.createDevice(os);
         * node.setVendorName(vendor); node.setNodeId(id); node.setDeviceMode(deviceMode);
         *
         * if (node.getVersion() == null && node.getDeviceMode() == Constants.VIRTUAL_DEVICE) {
         * logger.debug("Version is null..skipping loading of " +node.getNodeId()); continue; } DeviceWeightage
         * weightage = DeviceWeightageManager.getInstance().getWeightage(node); node.setWeightageObj(weightage);
         * lis.add(node); } } catch ( Exception ee ) { logger.warn("Error while loading devices", ee); } finally { try {
         * rs.close(); } catch ( Exception ee ) { } }
         *
         * nodes = new NetworkNode[lis.size()]; lis.toArray(nodes);
         *
         * return nodes;
         */
    }

    public static NetworkNode readDevice(int nodeId, OSType deviceType) {
        // NetworkNode node = VersionFactory.createDevice(deviceType);
        NetworkNode node = new NetworkNode();
        node.setNodeId(nodeId);
        readDevice(node);

        return node;
    }

    public static NetworkNode readDevice(NetworkNode node) {
        try {
            LinkedHashMap<String, String> queryTypes = node.getQueryTypes();
            if (queryTypes != null) {
                for (String qtype : queryTypes.keySet()) {
                    InventoryDBHelper.loadQuery(node, queryTypes.get(qtype), qtype);
                }
            }
        } catch (Exception ee) {
            logger.warn("Error in readDevice for the deviceId=" + node.getNodeId(), ee);
        }

        return node;
    }

    public static NetworkNode[] loadUnmanagedDevices() {
        NetworkNode[] nodes = null;
        NetworkNode node = null;
        ArrayList<NetworkNode> lis = new ArrayList<NetworkNode>();
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery("select * from nodes where state='u'");

            while (rs.next()) {
                String vendor = rs.getString("vendor");
                int id = rs.getInt("ID");

                // if (vendor.toLowerCase().indexOf("cisco") != -1)
                // {
                node = new NetworkNode();
                node.setVendorName(vendor);
                node.setNodeId(id);
                /*
                 * DeviceWeightage weightage = DeviceWeightageManager.getInstance().getWeightage(node);
                 * node.setWeightageObj(weightage);
                 */
                // }

                readDevice(node);
                lis.add(node);
            }
        } catch (Exception ee) {
            logger.warn("Error while loading unmanage devices.", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }

        nodes = new NetworkNode[lis.size()];
        lis.toArray(nodes);

        return nodes;
    }

    /*
     * public static int saveDeviceNeighborDetails(int fromDeviceId, int toDeviceId, String fromPort, String toPort)
     * throws Exception { Connection c = null; PreparedStatement ps = null; ResultSet rs = null; int linkId = -1;
     * boolean entryExists = false; NetworkNode node = NetworkNodeCache.getInstance().getNodeByID(toDeviceId); if (node
     * == null) { return -1; }
     *
     * String toIpAddress = node.getIpAddr().toString(); try { rs =
     * DBHelper.executeQuery("select * from device_cdp_table where device_id=" + fromDeviceId + " AND toIPAddress='" +
     * toIpAddress + "' AND fromport='" + fromPort + "' AND toport='" + toPort + "'");
     *
     * if ( (rs != null) && rs.next() ) { entryExists = true; } } catch ( Exception ee ) {
     * logger.warn("Error while querying device neighbor details for device_id=" + fromDeviceId, ee ); }
     *
     * try { rs.close(); } catch ( Exception ee ) {}
     *
     * if ( entryExists ) { throw (new Exception("The Neighbor Entry already exists.")); }
     *
     * c = DBHelper.getConnection();
     *
     * try { ps = c.prepareStatement(DBHelperConstants.INSERT_DEVICE_NEIGHBOR); ps.setInt(1, fromDeviceId);
     * ps.setString(2, toIpAddress); ps.setString(3, fromPort); ps.setString(4, toPort);
     *
     * ps.executeUpdate();
     *
     * rs = DBHelper.executeQuery("select link_id from device_cdp_table where device_id=" + fromDeviceId +
     * " AND toIPAddress='" + toIpAddress + "' AND fromport='" + fromPort + "' AND toport='" + toPort + "'");
     *
     * if ( (rs != null) && rs.next() ) { linkId = rs.getInt(1);
     *
     * NetworkNode device = NetworkNodeCache.getInstance().getNodeByID(fromDeviceId);
     *
     * if ( device != null ) { device.updateCDPNeighbors(linkId, fromDeviceId, toIpAddress, fromPort, toPort); } } }
     * catch ( Exception ee ) { logger.warn("Error while savingdevice neighbor details for device_id=" + fromDeviceId,
     * ee ); } finally { try { ps.close(); } catch ( Exception ee ) {}
     *
     * try { rs.close(); } catch ( Exception ee ) {}
     *
     * try { DBHelper.releaseConnection(c); } catch ( Exception ee ) {} }
     *
     * return linkId; }
     */
    public static String retrieveVersionStr(String runConf) {
        try {
            StringTokenizer st = new StringTokenizer(runConf, "\n");
            while (st.hasMoreTokens()) {
                String token = st.nextToken().trim().toLowerCase();
                if (token.indexOf("fwsm version") != -1) {
                    String version = token.substring("FWSM Version".length());

                    return version.trim();
                } else if (token.startsWith("version")) {
                    String version = token.substring("version".length());

                    return version.trim();
                } else if (token.startsWith("pix version")) {
                    String version = token.substring("PIX Version".length());

                    return version.trim();
                } else if (token.startsWith("asa version")) {
                    String version = token.substring("ASA Version".length());

                    return version.trim();
                } else if (token.startsWith("#version")) {
                    String version = token.substring("#version".length());

                    return version.trim();
                }
            }
        } catch (Exception ee) {
            logger.warn("Error in retrieveVersionStr", ee);
        }

        return null;
    }

    public static String getVirtualDeviceTypeFromRunConf(String runConf) {
        try {
            StringTokenizer st = new StringTokenizer(runConf, "\r\n");

            while (st.hasMoreTokens()) {
                String token = st.nextToken().trim().toLowerCase();

                if (token.startsWith("pix version") || (token.startsWith("asa version"))) {
                    return "PIX";
                }

                if (token.startsWith("#version")) {
                    return "CatOS";
                }
            }
        } catch (Exception ee) {
            logger.warn("Error in getVirtualDeviceTypeFromRunConf", ee);
        }

        return null;
    }

    public static String getFirewallOsTypeFromRunConf(String runConf) {
        try {
            StringTokenizer st = new StringTokenizer(runConf, "\r\n");

            while (st.hasMoreTokens()) {
                String token = st.nextToken().trim().toLowerCase();

                if (token.startsWith("pix version")) {
                    return "PIX";
                } else if (token.startsWith("asa version")) {
                    return "ASA";
                }
            }
        } catch (Exception ee) {
            logger.warn("Error in getFirewallOsTypeFromRunConf", ee);
        }

        return null;
    }

    public static NetworkInterface[] retrieveNetworkInterfaces(String runConf, NetworkNode node) {
        if (runConf == null) {
            return new NetworkInterface[0];
        }

        ShowCommandGrammer gram = ShowCommandGrammerBuilder.getInstance().getShowCommandGrammerFor("*",
                "parseRunConf");
        ParseShowCommand parseComm = new ParseShowCommand(gram, runConf, node);

        try {
            ArrayList list = parseComm.parse();
            return CliToObjectsBuilder.parseInterfaceInfo(node, list);
        } catch (Exception ee) {
            logger.warn("Error while retrieving network interfaces", ee);
        }
        return new NetworkInterface[0];
    }

    public static NetworkInterface[] retrievePixNetworkInterfaces(String runConf, NetworkNode node) {
        ShowCommandGrammer gram = ShowCommandGrammerBuilder.getInstance().getShowCommandGrammerFor("*",
                "parsePixRunConf");
        try {
            ParseShowCommand parseComm = new ParseShowCommand(gram, runConf, node);

            ArrayList list = parseComm.parse();
            return CliToObjectsBuilder.parseInterfaceInfo(node, list);
        } catch (Exception ee) {
            logger.warn("Error while retrieving pix network interfaces", ee);
        }
        return new NetworkInterface[0];
    }

    public static void updateDeviceOperationTime(int deviceId, String moduleName, String subModuleName,
            long timestamp) {
        PreparedStatement ps = null;
        Connection c = null;

        try {
            String query = "delete from device_operation_time where dev_id=" + deviceId + " and module='"
                    + moduleName + "' and sub_module='" + subModuleName + "'";
            DBHelper.executeUpdate(query);

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.DEVICE_OPERATION_TIME_INSERT);

            ps.setInt(1, deviceId);
            ps.setString(2, moduleName);
            ps.setString(3, subModuleName);
            ps.setTimestamp(4, new Timestamp(timestamp));

            ps.executeUpdate();
        } catch (Exception ee) {
            logger.warn("Error while updating device operation time for the device=" + deviceId, ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void updateDeviceMode(int deviceId, int device_type) {
        PreparedStatement ps = null;
        Connection c = null;

        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.NODE_CSPCMODE_UPDATE);

            ps.setInt(1, device_type);
            ps.setInt(2, deviceId);

            ps.executeUpdate();
        } catch (Exception ee) {
            logger.error("Error while updating device mode for the device=" + deviceId, ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
                logger.error("Error while closing prepared statement", ee);
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
                logger.error("Error while releasing connection ", ee);
            }
        }
    }

    public static void storeSyslogMsg(int nodeId, DecodedSyslogMessage msg) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            NetworkNode node = null;
            if (nodeId != -1) {
                node = NetworkNodeCache.getInstance().getNodeByID(nodeId);
            } else {
                node = NetworkNodeCache.getInstance().getLiveNode(msg.getSource().toString());
            }

            if (node == null) {
                return;
            }

            String compare_str = msg.get_module();

            if (node instanceof PixDevice) {
                compare_str = msg.get_mnemonic();
            }

            int id = node.getNodeId();
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_SYSLOG_MESSAGE);
            ps.setInt(1, id);
            ps.setString(2, msg.getModule());
            ps.setString(3, msg.get_mnemonic());
            ps.setInt(4, msg.get_severity());
            ps.setString(5, msg.message());
            ps.setString(6, msg.tag());
            ps.setInt(7, msg.priority());
            ps.setLong(8, msg.getSeqNumber());
            ps.setTimestamp(9, (new Timestamp(msg.getReceivedTime())));
            ps.setString(10, compare_str);
            ps.executeUpdate();
        } catch (Exception ee) {
            logger.warn("Error while storing syslog message for the deviceId=" + nodeId, ee);
        } finally {
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void addSyslogMsg(int deviceId, DecodedSyslogMessage msg) {
        Connection c = null;
        PreparedStatement ps = null;

        try {

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_SYSLOG_MESSAGE);
            ps.setInt(1, deviceId);
            ps.setString(2, msg.getModule());
            ps.setString(3, msg.get_mnemonic().substring(1));
            ps.setInt(4, msg.get_severity());
            ps.setString(5, msg.message());
            ps.setString(6, msg.tag());
            ps.setInt(7, msg.priority());
            ps.setLong(8, msg.getSeqNumber());
            ps.setTimestamp(9, (new Timestamp(msg.getReceivedTime())));
            ps.setString(10, msg.get_mnemonic());
            ps.executeUpdate();
        } catch (Exception ee) {
            logger.warn("Error while storing syslog message for the deviceId=" + deviceId, ee);
        } finally {
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void pergeSyslogMsgPerDevice(long maxSyslogMsgsPerDev) {
        String selectQuery = null;
        String deleteQuery = null;
        if (isPostgres()) {
            selectQuery = "select distinct(device_id) from syslog_messages";
            deleteQuery = "delete from syslog_messages where device_id = ? and received_at not in "
                    + "(Select received_at from (select received_at from syslog_messages where device_id = ? "
                    + " order by received_at desc)slm LIMIT ?) ";
        } else {
            selectQuery = "select distinct(device_id) from syslog_messages";
            deleteQuery = "delete from syslog_messages where device_id = ? and received_at not in "
                    + "(Select received_at from (select received_at from syslog_messages where device_id = ? "
                    + " order by received_at desc) where rownum <= ?) ";
        }
        Connection c = null;
        PreparedStatement ps = null;

        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery(selectQuery);
            while ((rs != null) && rs.next()) {
                int deviceId = rs.getInt("device_id");
                try {
                    c = DBHelper.getConnection();
                    ps = c.prepareStatement(deleteQuery);
                    ps.setInt(1, deviceId);
                    ps.setInt(2, deviceId);
                    ps.setLong(3, maxSyslogMsgsPerDev);
                    ps.executeUpdate();
                } catch (Exception ee) {
                    logger.warn("Error while perging syslog message for the deviceId=" + deviceId, ee);
                } finally {
                    try {
                        ps.close();
                    } catch (Exception e) {
                    }
                    try {
                        DBHelper.releaseConnection(c);
                    } catch (Exception ee) {
                    }
                }
            }
        } catch (Exception ee) {
            logger.warn("Error while executing getUniqIp", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
    }

    public static void pergeSyslogMsgAllDevice(long maxSyslogMsgs) {
        String deleteQuery = null;
        if (isPostgres()) {
            deleteQuery = "delete from syslog_messages where received_at not in "
                    + "(Select received_at from (select received_at from syslog_messages "
                    + " order by received_at desc)slm  LIMIT ?) ";
        } else {
            deleteQuery = "delete from syslog_messages where received_at not in "
                    + "(Select received_at from (select received_at from syslog_messages "
                    + " order by received_at desc) where rownum < ?) ";
        }

        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(deleteQuery);
            ps.setLong(1, maxSyslogMsgs);

            ps.executeUpdate();
        } catch (Exception ee) {
            logger.warn("Error while perging syslog message for all devices", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void pergeSyslogMsgForNonExistingDevice() {
        String deleteQuery = "delete from syslog_messages where DEVICE_ID not in " + "(Select ID from nodes) ";

        try {
            DBHelper.executeUpdate(deleteQuery);
        } catch (Exception ee) {
            logger.warn("Error while perging syslog message for non existing devices", ee);
        }
    }

    public static Map<Integer, Long> getLastSyslogInfoPerDevice() {
        Map<Integer, Long> deviceSeqNoMap = new HashMap<Integer, Long>();
        ResultSet rs = null;
        try {

            // received_at
            rs = DBHelper.executeQuery("select device_id, max(seq_number) from syslog_messages group by device_id");

            if (rs != null) {
                while (rs.next()) {
                    if (rs.getInt(1) > 0) {
                        deviceSeqNoMap.put(rs.getInt(1), rs.getLong(2));
                    }
                }
            }
        } catch (Exception ee) {
            logger.warn("Error while retriving last syslog info per device", ee);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ee) {
            }
        }

        return deviceSeqNoMap;
    }

    public static Map<Integer, Long> getSyslogLastReceivedTimePerDevice() {
        Map<Integer, Long> deviceSeqNoMap = new HashMap<Integer, Long>();
        ResultSet rs = null;
        try {

            rs = DBHelper
                    .executeQuery("select device_id, max(received_at) from syslog_messages group by device_id");

            if (rs != null) {
                while (rs.next()) {
                    if (rs.getInt(1) > 0) {
                        deviceSeqNoMap.put(rs.getInt(1), rs.getTimestamp(2).getTime());
                    }
                }
            }
        } catch (Exception ee) {
            logger.warn("Error while retriving syslog last received time per device", ee);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ee) {
            }
        }

        return deviceSeqNoMap;
    }

    public static int isVirtualDeviceExists(String deviceName) {
        String query = "select id from ios_version where hostname=?";
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int id = -1;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(query);
            ps.setString(1, deviceName);
            rs = ps.executeQuery();
            if (rs != null && rs.next()) {
                id = rs.getInt("ID");
                NetworkNode node = NetworkNodeCache.getInstance().getNodeByID(id);
                if (node == null || node.getDeviceMode() == Constants.PHYSICAL_DEVICE) {
                    id = -1;
                }
            }

        } catch (Exception ee) {
            logger.warn("Error while checking whether virtual device exists with the device name: " + deviceName,
                    ee);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception e) {
            }
        }
        return id;
    }

    public static int isVirtualDeviceExists(String deviceName, int custId) {
        String query = "select DEVICE_ID from CUSTOMER_HOSTNAME where CUSTOMER_ID=? and HOSTNAME=?";
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int id = -1;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(query);
            ps.setInt(1, custId);
            ps.setString(2, deviceName);
            rs = ps.executeQuery();
            if (rs != null && rs.next()) {
                id = rs.getInt("DEVICE_ID");
                NetworkNode node = NetworkNodeCache.getInstance().getNodeByID(id);
                if (node == null) {
                    id = -1;
                }
            }

        } catch (Exception ee) {
            logger.warn("Error while checking whether virtual device exists with the device name: " + deviceName,
                    ee);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception e) {
            }
        }
        return id;
    }

    public static String getUniqIp() {
        StringBuffer sbuf = new StringBuffer();
        Random ran = new Random();
        sbuf.append("0.");
        sbuf.append(ran.nextInt(254)).append(".");
        sbuf.append(ran.nextInt(254)).append(".");
        sbuf.append(ran.nextInt(254));

        String ip = sbuf.toString();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from nodes where ipaddress='" + ip + "'");
            if ((rs != null) && rs.next()) {
                return getUniqIp();
            }
        } catch (Exception ee) {
            logger.warn("Error while executing getUniqIp", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return ip;
    }

    public static void loadQuery(NetworkNode node, String query, String queryType) {
        // if ( logger.isDebugEnabled() )
        // {
        // logger.debug("loadQuery", query);
        // logger.debug("Query Type: " + queryType);
        // logger.debug("Node Id: " + node.getNodeId());
        // }

        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery(query);

            if (queryType.equals("ios.runconf")) {
                // StringBuffer sb = new StringBuffer();
                // while ( rs.next() )
                // {
                // sb.append(rs.getString("run_conf"));
                // }
                // node.setRunningConfig(sb.toString());
            } else {
                while (rs.next()) {
                    node.updateObject(queryType, rs);
                }
            }
        } catch (Exception e) {
            logger.warn("Error while running loadQuery", query, e);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
            }
        }
    }

    public static void updateInventoryTime(int nodeId, long invUpdateTime) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.UPDATE_INVENTORY_TIME);
            ps.setTimestamp(1, (new Timestamp(invUpdateTime)));
            ps.setInt(2, nodeId);
            ps.executeUpdate();
        } catch (Exception ee) {
            logger.warn("Error while updating inventory time for the device= " + nodeId, ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ex) {
            }
        }
    }

    public static void updateConfigChangeTime(int nodeId, long confUpdateTime) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.UPDATE_CONFIG_UPDATE_TIME);
            ps.setTimestamp(1, (new Timestamp(confUpdateTime)));
            ps.setInt(2, nodeId);
            ps.executeUpdate();
        } catch (Exception ee) {
            logger.warn("Error while updating config change time for the device= " + nodeId, ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ex) {
            }
        }
    }

    public static CustomerInstance[] loadWingInstances() {
        ArrayList<CustomerInstance> lis = new ArrayList<CustomerInstance>();
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery("select * from wing_instance");

            while (rs.next()) {
                CustomerInstance instance = new CustomerInstance();

                int id = rs.getInt("instance_id");

                instance.setInstnceId(id);

                String name = rs.getString("instance_name");
                instance.setInstanceName(name);

                String contact_name = rs.getString("contact_name");
                if (contact_name != null) {
                    instance.setContactName(contact_name);
                }

                String contact_email = rs.getString("contact_email");
                if (contact_email != null) {
                    instance.setContactEmail(contact_email);
                }

                String contact_phone = rs.getString("contact_phone");
                if (contact_phone != null) {
                    instance.setContactPhoneNumber(contact_phone);
                }

                String location = rs.getString("location");
                if (location != null) {
                    instance.setLocation(location);
                }

                String description = rs.getString("description");
                if (description != null) {
                    instance.setDescription(description);
                }

                lis.add(instance);
            }
        } catch (Exception ee) {
            logger.error("Error while loading customers from db.", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.debug("Error while closing cursor.", ee);
            }
        }
        return lis.toArray(new CustomerInstance[lis.size()]);
    }

    public static Customer[] loadCustomers() {
        ArrayList<Customer> lis = new ArrayList<Customer>();
        ResultSet rs = null;
        ArrayList<Customer> updateCustomers = new ArrayList<Customer>();

        try {
            rs = DBHelper.executeQuery("select  * from customers");

            while (rs.next()) {
                Customer customer = new Customer();

                int id = rs.getInt("customer_id");

                customer.setId(id);

                String name = rs.getString("customer_name");
                customer.setCustomerName(name);

                String contact_name = rs.getString("contact_name");
                if (contact_name != null) {
                    customer.setContactName(contact_name);
                }

                String contact_email = rs.getString("contact_email");
                if (contact_email != null) {
                    customer.setEmailAddresses(contact_email);
                }

                String contact_phone = rs.getString("contact_phone");
                if (contact_phone != null) {
                    customer.setContactPhoneNumber(contact_phone);
                }

                String wing_login = rs.getString("wing_login");
                if (wing_login != null) {
                    customer.setLogin(wing_login);
                }

                String securityKey = rs.getString("security_key");
                if (securityKey != null) {
                    customer.setSecurityKey(securityKey);
                }

                int noOfLicenses = rs.getInt("no_of_licenses");
                int totLicUnits = LicenseValidator.getInstance().getNoOfDevices();
                if (totLicUnits == -1) {
                    noOfLicenses = -1;
                    updateCustomers.add(customer);
                } else if (noOfLicenses > totLicUnits) {
                    noOfLicenses = totLicUnits;
                    updateCustomers.add(customer);
                }
                customer.setNoOfLicenses(noOfLicenses);

                int creatorId = rs.getInt("creator_id");
                customer.setCreatorId(creatorId);

                String creatorName = rs.getString("creator_name");
                UserDetails ud = UsersFactory.getUser(creatorId);
                if (ud != null) {
                    customer.setCreaterName(
                            ud.getName() != null ? (ud.getName() + " (" + ud.getLogin() + ")") : ud.getLogin());
                } else {
                    customer.setCreaterName(creatorName);
                }

                customer.setFaultList(getCustomerFaults(id));

                lis.add(customer);
            }
        } catch (Exception ee) {
            logger.error("Error while loading customers from db.", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.debug("Error while closing cursor.", ee);
            }
        }
        if (updateCustomers.size() > 0) {
            for (Customer cus : updateCustomers) {
                insertOrUpdateCustomer(cus);
            }
        }
        return lis.toArray(new Customer[lis.size()]);
    }

    public synchronized static CustomerInstance insertOrUpdateWingInstance(CustomerInstance instance) {
        PreparedStatement ps = null;
        Connection c = null;

        try {
            c = DBHelper.getConnection();
            boolean insert = false;
            if (instance.getInstanceId() == -1) {
                int instId = getNextWingInstanceId(c);
                instance.setInstnceId(instId);
                ps = c.prepareStatement(DBHelperConstants.WING_INSTANCE_INSERT);
                insert = true;
            } else {
                ps = c.prepareStatement(DBHelperConstants.WING_INSTANCE_UPDATE);
            }

            if (insert) {
                ps.setInt(1, instance.getInstanceId());
                ps.setString(2, instance.getInstanceName());
                ps.setString(3, instance.getContactName());
                ps.setString(4, instance.getContactEmail());
                ps.setString(5, instance.getContactPhoneNumber());
                ps.setString(6, instance.getLocation());
                ps.setString(7, instance.getDescription());
            } else {
                ps.setString(1, instance.getInstanceName());
                ps.setString(2, instance.getContactName());
                ps.setString(3, instance.getContactEmail());
                ps.setString(4, instance.getContactPhoneNumber());
                ps.setString(5, instance.getLocation());
                ps.setString(6, instance.getDescription());
                ps.setInt(7, instance.getInstanceId());
            }
            ps.executeUpdate();
            return instance;
        } catch (Exception ee) {
            logger.warn("Error while inserting or updating the customer instance", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }

        }
        return null;
    }

    public static ConcurrentHashMap<Integer, HashSet<Integer>> loadCustomerWingInstances() {
        ConcurrentHashMap<Integer, HashSet<Integer>> instTable = new ConcurrentHashMap<Integer, HashSet<Integer>>();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from customer_wing_instance");
            while (rs.next()) {
                int custId = rs.getInt("customer_id");
                int instId = rs.getInt("instance_id");
                HashSet<Integer> instances = instTable.get(custId);
                if (instances == null) {
                    instances = new HashSet<Integer>();
                    instTable.put(custId, instances);
                }
                instances.add(instId);

            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }

        return instTable;
    }

    public static void deletecustomerInstanceId(int custId, int instId) throws Exception {
        DBHelper.executeUpdate(
                "delete from customer_wing_instance where customer_id=" + custId + " and instance_id=" + instId);
        CustomerInstance custInst = CustomerWingInstanceManager.getInstance().getCustomerInstance(instId);
        if (custInst != null) {
            DBHelper.executeUpdate("delete from wing_settings_update_details where customer_id=" + custId
                    + " and instance_name='" + custInst.getInstanceName() + "'");
        }
    }

    public static void insertCustomerWingInstance(int custId, int instId) {
        try {
            DBHelper.executeUpdate("insert into customer_wing_instance values (" + custId + "," + instId + ")");
        } catch (Exception ee) {
            ee.printStackTrace();
        }
    }

    public static Customer insertOrUpdateCustomer(Customer customer) {
        PreparedStatement ps = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        Connection c = null;
        ResultSet rs = null;

        try {
            c = DBHelper.getConnection();
            boolean insert = true;
            if (customer.getId() != -1) {
                String query = "select * from customers where customer_id=" + customer.getId();
                rs = DBHelper.executeQuery(query);

                if (rs.next()) {
                    insert = false;
                }
                try {
                    rs.close();
                } catch (Exception ee) {
                }
            }

            if (!insert) {
                ps = c.prepareStatement(DBHelperConstants.CUSTOMER_UPDATE);
            } else {
                customer.setId(getNextCustomerId(c));
                ps = c.prepareStatement(DBHelperConstants.CUSTOMER_INSERT);
                ps1 = c.prepareStatement(DBHelperConstants.SHADOW_CUSTOMER_INSERT);
                ps2 = c.prepareStatement(DBHelperConstants.INSERT_PROFILES_ALL);
            }
            if (insert) {
                ps.setInt(1, customer.getId());
                ps.setString(2, customer.getCustomerName());
                ps.setString(3, customer.getContactName());
                ps.setString(4, customer.getEmailAddresses());
                ps.setString(5, customer.getContactPhoneNumber());
                ps.setString(6, customer.getLogin());
                ps.setString(7, customer.getSecurityKey());
                ps.setInt(8, customer.getCreatorId());
                ps.setString(9, customer.getCreaterName());
                ps.setDouble(10, customer.getNoOfLicenses());

                ps1.setInt(1, customer.getId());
                ps1.setInt(2, customer.getId());

                ps2.setInt(1, customer.getId());
                ps2.setString(2, customer.getCustomerName());

                // Creating profiles
                FaultProfile fp = new FaultProfile();
                fp.setProfId(customer.getId());
                fp.setProfName(customer.getCustomerName());
                FaultSpecFactory.getInstance().addFaultProfileOnly(fp);
            } else {
                ps.setString(1, customer.getCustomerName());
                ps.setString(2, customer.getContactName());
                ps.setString(3, customer.getEmailAddresses());
                ps.setString(4, customer.getContactPhoneNumber());
                ps.setString(5, customer.getLogin());
                ps.setString(6, customer.getSecurityKey());
                ps.setInt(7, customer.getNoOfLicenses());
                ps.setDouble(8, customer.getId());
            }
            ps.executeUpdate();
            if (ps1 != null) {
                ps1.executeUpdate();
            }
            if (ps2 != null) {
                ps2.executeUpdate();
            }
            return customer;
        } catch (Exception ee) {
            logger.warn("Error while inserting or updating the customer", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                ps1.close();
            } catch (Exception ee) {
            }

            try {
                ps2.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }

            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return null;
    }

    /**
     * Generarates Public/Private keypairs and stores the keystore file in database. If the keystore already exists then
     * it will re generate only if regenerate paramater is true
     *
     * @param customerId
     *            Customer ID
     * @param instace_id
     *            Instance ID
     * @param alias
     *            Keystore alias for the key pairs
     * @param keystore
     *            Keystore data in base64 encoded format
     * @param regenerate
     *            If keystore already exists then true value will re-generte the keystore
     */
    public static void insertCustomerSecurityKeystore(int customerId, int instace_id, String alias, String keystore,
            boolean regenerate) {
        if (!regenerate && getCustomerSecurityKeystore(customerId, instace_id) != null) {
            logger.info("Keystore already exists for customer/instance ", customerId + "/" + instace_id);
            return;
        }
        Connection c = null;
        PreparedStatement ps = null;
        try {
            DBHelper.executeUpdate("delete from customer_security_keystore where customer_id=" + customerId
                    + " and instance_id = " + instace_id);

            c = DBHelper.getConnection();

            ps = c.prepareStatement(
                    "insert into customer_security_keystore (customer_id, instance_id, alias, keystore) VALUES (?,?,?,?)");
            ps.setInt(1, customerId);
            ps.setInt(2, instace_id);
            ps.setString(3, alias);
            if (ps instanceof OraclePreparedStatement) {
                ((OraclePreparedStatement) ps).setClob(4,
                        new InputStreamReader(IOUtils.toInputStream(keystore, "UTF-8")));
            } else if (isPostgres()) {

                ps.setString(4, keystore);

            } else {
                ps.setString(4, keystore);
            }
            ps.executeUpdate();
        } catch (Exception e) {
            logger.warn("Error while inserting key pairs for : \'" + alias + "\'", e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static String getCustomerSecurityKeystore(int customer_id, int installation_id) {
        String keystore = null;
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select keystore from customer_security_keystore where customer_id = "
                    + customer_id + " and instance_id = " + installation_id);
            if (rs.next()) {
                keystore = rs.getString("keystore");
            }
        } catch (Exception e) {
            logger.warn("Error while loading keystore for : \'" + customer_id + "/" + installation_id + "\'", e);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return keystore;
    }

    public static String getCustomerSecurityKeystoreAlias(int customer_id, int installation_id) {
        String alias = null;
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select alias from customer_security_keystore where customer_id = "
                    + customer_id + " and instance_id = " + installation_id);
            if (rs.next()) {
                alias = rs.getString("alias");
            }
        } catch (Exception e) {
            logger.warn("Error while loading alias for : \'" + customer_id + "/" + installation_id + "\'", e);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return alias;
    }

    private static int getNextCustomerId(Connection c) throws Exception {
        return DBHelper.getNextSequence(c, "customers_customer_id");
    }

    private static int getNextWingInstanceId(Connection c) throws Exception {
        return DBHelper.getNextSequence(c, "wing_instance_instance_id");
    }

    public static void deleteCustomer(int customer_id) throws Exception {
        try {
            DBHelper.executeUpdate("delete from customers where customer_id=" + customer_id);
            DBHelper.executeUpdate("delete from flt_profiles where profile_id=" + customer_id);
            FaultSpecFactory.getInstance().deleteFaultProfileOnly(customer_id);
        } catch (Exception ee) {
            logger.error("Error in Delete Customer ID :" + customer_id, ee);
        }
    }

    public static void loadCustomerDeviceDetails(
            ConcurrentHashMap<Integer, HashMap<Integer, String>> customerDevice,
            ConcurrentHashMap<Integer, ConcurrentHashMap<String, Integer>> customerDeviceIPList) {
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery(
                    "select * from customer_device where device_id  not in (select mod_id from nodes_modules)");
            while (rs.next()) {
                int custId = rs.getInt("customer_id");
                int deviceId = rs.getInt("device_id");
                NetworkNode node = NetworkNodeCache.getInstance().getNodeByID(deviceId);
                if (node == null) {
                    continue;
                }
                node.setCustomerId(custId);
                // DeviceWeightageCache.getInstance().addCustNodeWeightageMap(custId,
                // node);
                HashMap<Integer, String> devices = customerDevice.get(custId);
                if (devices == null) {
                    devices = new HashMap<Integer, String>();
                    customerDevice.put(custId, devices);
                }

                devices.put(deviceId, "");

                ConcurrentHashMap<String, Integer> devicesIpList = customerDeviceIPList.get(custId);
                if (devicesIpList == null) {
                    devicesIpList = new ConcurrentHashMap<String, Integer>();
                    customerDeviceIPList.put(custId, devicesIpList);
                }
                devicesIpList.put(node.getIpAddr().toString(), deviceId);
                /*
                 * Removing this code because we no longer worry about duplicate devices in NCCM. CSPC takes care of
                 * this. We only put the device management IP address in this list now.
                 */
                // ArrayList list = node.getAllInterfaces();
                // Iterator it = list.iterator();
                // while (it.hasNext())
                // {
                // NetworkInterface nif = (NetworkInterface) it.next();
                // if (nif.getIpAddress() != null)
                // {
                // devicesIpList.put(nif.getIpAddress(), deviceId);
                // }
                // }

            }

        } catch (Exception ee) {
            logger.warn("Error in loadCustomerDeviceDetails", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
    }

    public static void addCustomerDeviceDetails(int custId, int[] deviceDetails) {
        try {
            for (int i = 0; i < deviceDetails.length; i++) {
                DBHelper.executeUpdate(
                        "insert into customer_device VALUES (" + custId + "," + deviceDetails[i] + ")");
            }
        } catch (Exception ee) {
            logger.warn("Error while setting up customer to device ids", ee);
        }
    }

    public static void updateCustomerDeviceDetails(int custId, int[] deviceDetails) {
        try {
            for (int i = 0; i < deviceDetails.length; i++) {
                DBHelper.executeUpdate(
                        "insert into customer_device VALUES (" + custId + "," + deviceDetails[i] + ")");
            }
        } catch (Exception ee) {
            logger.warn("Error while setting up customer to device ids", ee);
        }
    }

    public static void addCustomerDeviceDetails(int customerId, int devId) {
        try {
            DBHelper.executeUpdate("insert into customer_device VALUES (" + customerId + "," + devId + ")");
        } catch (Exception ee) {
            logger.warn("Error adding device to the customer_device table", ee);
        }
    }

    public static void delCustomerDeviceDetails(int customerId, int devId) {
        try {
            DBHelper.executeUpdate(
                    "delete from customer_device where CUSTOMER_ID=" + customerId + " AND DEVICE_ID=" + devId);
        } catch (Exception ee) {
            logger.warn("Error while deleting device ids from the customer_device.", ee);
        }
    }

    public static void updateCatOsDeviceDetails(NetworkNode device) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            CatOSVersion ver = (CatOSVersion) device.getVersion();
            DBHelper.executeUpdate("delete from catos_version where id=" + device.getNodeId());
            DBHelper.executeUpdate("delete from device_module_info where device_id=" + device.getNodeId());

            c = DBHelper.getConnection();

            ps = c.prepareStatement(DBHelperConstants.UPDATE_CATOS_VERSION);
            ps.setInt(1, device.getNodeId());
            ps.setString(2, ver.getMcpSwVersion());
            ps.setString(3, ver.getHardwareVersion());

            try {
                ps.executeUpdate();
            } catch (Exception ee) {
            }

            try {
                ps.close();
            } catch (Exception ee) {
            }

            ArrayList<CatOSSwitchModule> modules = ver.getSwitchModules();

            for (CatOSSwitchModule module : modules) {
                try {
                    ps = c.prepareStatement(DBHelperConstants.INSERT_CATOS_SWITCH_MODULES);
                    ps.setInt(1, device.getNodeId());
                    ps.setInt(2, module.getModule());
                    ps.setString(3, module.getModel());
                    ps.setString(4, module.getPort());
                    ps.setString(5, module.getSerialNumber());
                    ps.setString(6, module.getHw());
                    ps.setString(7, module.getFw());
                    ps.setString(8, module.getFw1());
                    ps.setString(9, module.getSw());
                    ps.setString(10, module.getSw1());

                    ps.executeUpdate();
                } catch (Exception ee) {
                }
                try {
                    ps.close();
                } catch (Exception ee) {
                }

                try {
                    InventoryDBHelper.insertCatOsModules(c, device.getNodeId(), module.getModel(), "Module",
                            module.getModel(), module.getSerialNumber());
                } catch (Exception e) {
                    logger.warn("Error while inserting catOs modules", e);
                }
            }
        } catch (Exception ee) {
            logger.warn("Error hwhile updating catos details", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ex) {
            }
        }
    }

    public static List<String> getConsolidatedProductFamily() {
        ResultSet rs = null;
        List<String> productFamilyList = new ArrayList<>();
        try {
            rs = DBHelper.executeQuery("select distinct product_family as ProductFamily from nodes");
            while (rs.next()) {
                productFamilyList.add(rs.getString("ProductFamily"));
            }
            return productFamilyList;
        } catch (Exception ex) {
            logger.warn("Error while getting product family " + ex);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {

            }
        }
        return productFamilyList;
    }

    public static String getCustomerWingSettings(int custId, String instanceName) throws PariException {
        StringBuffer sbuf = new StringBuffer();
        ResultSet rs = null;
        try {
            if (instanceName == null) {
                rs = DBHelper.executeQuery(
                        "select * from CUSTOMER_WING_SETTINGS where CUSTOMER_ID=" + custId + "  order by row_id");
            } else {
                rs = DBHelper.executeQuery("select * from CUSTOMER_WING_SETTINGS where CUSTOMER_ID=" + custId
                        + " AND instance_name='" + instanceName + "' order by row_id");
            }
            while (rs.next()) {
                sbuf.append(rs.getString("settings"));
            }
        } catch (Exception ee) {
            logger.warn("Error while getting customer wing settings for the customer: " + custId, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        return sbuf.toString();
    }

    public static String getCustomerWingSettings(String customerName, String instanceName) throws PariException {
        Customer customer = CustomerManager.getInstance().getCustomer(customerName);
        if (customer == null) {
            throw (new PariException(-1, "No customer with the customerName" + customerName));
        }

        int customerId = customer.getId();

        return getCustomerWingSettings(customerId, instanceName);
    }

    // public static void insertCustomerWingSettings(String customerName, String
    // wingSettings) throws PariException {
    // Customer customer =
    // CustomerManager.getInstance().getCustomer(customerName);
    // if (customer == null)
    // throw (new PariException(-1,
    // "No customer with the customerName"+customerName));
    //
    // int customerId = customer.getId();
    //
    // insertCustomerWingSettings(customerId, null, wingSettings);
    //
    // }

    public static void insertCustomerWingSettings(int customerId, String instanceName, String wingSettings)
            throws PariException {
        int MAX_WING_SETTINGS_SIZE = 4000;
        Customer customer = null;
        if (ServerProperties.getInstance().getProductProfile().getProductShortName()
                .equals(Constants.PARITRA_PRODUCT)) {
            customer = CustomerManager.getInstance().getCustomerById(customerId);
            if (customer == null) {
                throw (new PariException(-1,
                        "Unable to save Customer Wing Settings. \nSelected Customer may have been deleted."));
            }
        }

        PreparedStatement ps = null;
        Connection c = null;

        try {
            c = DBHelper.getConnection();
            c.setAutoCommit(false);
            String query = null;

            if (instanceName == null) {
                query = "delete from customer_wing_settings where customer_id=" + customerId;
            } else {
                query = "delete from customer_wing_settings where customer_id=" + customerId
                        + " and instance_name='" + instanceName + "'";
            }

            DBHelper.executeUpdateNoCommit(c, query);

            int size = wingSettings.length();

            int noOfDBRows = size / MAX_WING_SETTINGS_SIZE;
            if ((noOfDBRows * MAX_WING_SETTINGS_SIZE) < size) {
                noOfDBRows += 1;
            }

            ps = c.prepareStatement(DBHelperConstants.INSERT_CUSTOMER_WING_SETTINGS);
            for (int i = 0; i < noOfDBRows; i++) {
                int startIndex = i * MAX_WING_SETTINGS_SIZE;
                int endIndex = (i + 1) * MAX_WING_SETTINGS_SIZE;

                if (endIndex > size) {
                    endIndex = size;
                }

                String conf = wingSettings.substring(startIndex, endIndex);
                ps.setInt(1, customerId);
                ps.setString(2, instanceName);
                ps.setInt(3, i);
                ps.setString(4, conf);

                ps.executeUpdate();
            }
            c.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                c.rollback();
            } catch (Exception sqlEx) {
            }
            logger.warn("Error while trying to save wing settings for customer", ex);
            throw (new PariException(-1, "Error while saving Customer Wing Settings."
                    + (customer == null ? "" : " for the customer " + customer.getCustomerName())));
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception psex) {
                }
            }

            try {
                if (c != null) {
                    c.setAutoCommit(true);
                }
            } catch (Exception ee) {
            }
            DBHelper.releaseConnection(c);
        }
        updateWingSettingsDetailsModifiedTime(customerId, instanceName);
    }

    private static void updateWingSettingsDetailsModifiedTime(int customerId, String wingInstance) {
        Connection c = null;
        ResultSet rs = null;
        PreparedStatement ps = null;

        try {
            c = DBHelper.getConnection();
            rs = DBHelper.executeQuery("select * from wing_settings_update_details where customer_id = "
                    + customerId + " and instance_name='" + wingInstance + "'");
            if (rs.next()) {
                ps = c.prepareStatement(DBHelperConstants.UPDATE_WING_SETTINGS_UPDATE_DETAILS_MODIFIED);
                ps.setTimestamp(1, (new Timestamp(System.currentTimeMillis())));
                ps.setInt(2, customerId);
                ps.setString(3, wingInstance);
                ps.executeUpdate();
            } else {
                ps = c.prepareStatement(DBHelperConstants.INSERT_WING_SETTINGS_UPDATE_DETAILS_MODIFIED);
                ps.setInt(1, customerId);
                ps.setString(2, wingInstance);
                ps.setTimestamp(3, (new Timestamp(System.currentTimeMillis())));
                ps.executeUpdate();
            }
        } catch (Exception ee) {
            logger.warn("Error while trying to save wing_settings_update_details for customer", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }

            try {
                ps.close();
            } catch (Exception e) {
            }

            try {
                if (c != null) {
                    DBHelper.releaseConnection(c);
                }
            } catch (Exception ee) {
            }
        }
    }

    public static void updateWingSettingsDetails(int customerId, String instanceName, long updateTime) {
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            c = DBHelper.getConnection();
            rs = DBHelper.executeQuery("select * from wing_settings_update_details where customer_id = "
                    + customerId + " and instance_name='" + instanceName + "'");
            if (rs.next()) {
                ps = c.prepareStatement(DBHelperConstants.UPDATE_WING_SETTINGS_UPDATE_DETAILS_UPLOAD);
                ps.setTimestamp(1, (new Timestamp(System.currentTimeMillis())));
                ps.setInt(2, customerId);
                ps.setString(3, instanceName);
                ps.executeUpdate();
            } else {
                ps = c.prepareStatement(DBHelperConstants.INSERT_WING_SETTINGS_UPDATE_DETAILS_UPLOAD);
                ps.setInt(1, customerId);
                ps.setString(2, instanceName);
                ps.setTimestamp(3, (new Timestamp(System.currentTimeMillis())));
                ps.executeUpdate();
            }
        } catch (Exception ee) {
            logger.warn("Error while updating wing setting details for the customer: " + customerId, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }

            try {
                ps.close();
            } catch (Exception e) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static ArrayList<String> getIgnorableConfigStrings() {
        ArrayList<String> ignoreStrings = new ArrayList<String>();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from config_ignore_strings");
            while (rs.next()) {
                ignoreStrings.add(rs.getString("config_string"));
            }
        } catch (Exception ee) {
            logger.warn("Error while obtaining ignorable config strings", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
            }
        }
        return ignoreStrings;
    }

    public static void setIgnorableConfigStrings(ArrayList<String> ignoreStrings) {
        NewVersionControlManager.setIgnoreConfigChanges(ignoreStrings);
        PreparedStatement ps = null;
        Connection c = null;
        try {
            DBHelper.executeUpdate("delete from config_ignore_strings");
            if (ignoreStrings == null || ignoreStrings.size() == 0) {
                return;
            }
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_CONFIG_IGNORE_STRINGS);
            for (String conf : ignoreStrings) {
                ps.setString(1, conf);
                ps.executeUpdate();
            }
        } catch (Exception ee) {
            logger.warn("Error while inserting ignorable config strings", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }

        }
    }

    public static ArrayList<String> getIgnorableApplyConfigStrings() {
        ArrayList<String> ignoreStrings = new ArrayList<String>();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from apply_config_ignore_strings");
            while (rs.next()) {
                ignoreStrings.add(rs.getString("config_string"));
            }
        } catch (Exception ee) {
            logger.warn("Error while obtaining ignorable apply config strings", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
            }
        }
        return ignoreStrings;
    }

    public static void setIgnorableApplyConfigStrings(ArrayList<String> ignoreStrings) {
        ConfigIgnoreLines.getInstance().setIgnorableApplyConfigStrings(ignoreStrings);
        PreparedStatement ps = null;
        Connection c = null;
        try {
            DBHelper.executeUpdate("delete from apply_config_ignore_strings");
            if (ignoreStrings == null || ignoreStrings.size() == 0) {
                return;
            }
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_APPLY_CONFIG_IGNORE_STRINGS);
            for (String conf : ignoreStrings) {
                ps.setString(1, conf);
                ps.executeUpdate();
            }
        } catch (Exception ee) {
            logger.warn("Error while inserting ignorable apply config strings", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }

        }
    }

    public static String loadStartupConfig(int nodeId) {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select config from node_current_config where id=" + nodeId
                    + " AND conf_type=" + Constants.STARTUP_CONF);
            if (rs.next()) {
                byte b[] = null;
                if (ServerProperties.getInstance().isPostgres()) {
                    b = rs.getBytes(1);
                } else {
                    Blob blob = rs.getBlob(1);
                    if (blob != null) {
                        b = blob.getBytes(1, (int) blob.length());
                    }
                }

                if (b != null) {
                    return CompressionUtils.getUncompressedString(b);

                }
            }
        } catch (Exception ex) {
            logger.error("Exception while loading startup config for device: " + nodeId, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                }
            }
        }
        return "";
    }

    public static void migrateConfigs() {
        try {
            logger.debug("Migrating configurations from old database (ios_run_conf) to new database table.");
            Set<Integer> nodeIds = getUniqNodesFromOldConfigTable();
            if (nodeIds == null || nodeIds.isEmpty()) {
                logger.debug("No rows found in the old configuration table ios_run_conf.");
                return;
            }
            for (Integer nodeId : nodeIds) {
                try {
                    String config = loadRunningConfigOld(nodeId);
                    if (config != null) {
                        try {
                            insertConfig(nodeId, Constants.RUN_CONF, config);
                            deleteConfigOld(nodeId, Constants.RUN_CONF);
                        } catch (Exception ex) {
                            logger.error("Exception while migrating old running config for device: " + nodeId, ex);
                        }
                    }
                    config = loadStartupConfigOld(nodeId);
                    if (config != null) {
                        try {
                            insertConfig(nodeId, Constants.STARTUP_CONF, config);
                            deleteConfigOld(nodeId, Constants.STARTUP_CONF);
                        } catch (Exception ex) {
                            logger.error("Exception while migrating old startup config for device: " + nodeId, ex);
                        }
                    }
                } catch (Exception ex) {
                    logger.error("Exception while migrating old configs for device: " + nodeId, ex);
                }
            }
            logger.debug("Done. Migrating configurations from old database to new database table for "
                    + nodeIds.size() + " rows");
        } catch (Exception ex) {
            logger.error("Exception while migrating old configurations from old db tables", ex);
        }

    }

    private static Set<Integer> getUniqNodesFromOldConfigTable() throws SQLException {
        Set<Integer> nodeIds = new HashSet<Integer>();
        String sql = "select distinct id from ios_run_conf";
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery(sql);
            while (rs != null && rs.next()) {
                nodeIds.add(rs.getInt(1));
            }
            return nodeIds;
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    public static boolean isSNMPMIBMatches(int nodeId, String oid, String mib, GroupRuleOperator operator) {
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(
                    "select mib from SNMP_MIBS where device_id=" + nodeId + " and OID like '%" + oid + "%'");

            rs = ps.executeQuery();
            return isSNMPValueMatches(rs, operator, mib);
        } catch (Exception ex) {
            logger.error("Exception while getting SNMP MIB for device: " + nodeId, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {

                }
            }
            DBHelper.releaseConnection(c);
        }
        return false;

    }

    public static boolean isSNMPMIBNameMatches(int nodeId, String title, String mib, GroupRuleOperator operator) {
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(
                    "select * from SNMP_MIBS where device_id=" + nodeId + " and title LIKE '%" + title + "%'");
            rs = ps.executeQuery();
            return isSNMPValueMatches(rs, operator, mib);
        } catch (Exception ex) {
            logger.error("Exception while getting SNMP MIB for device: " + nodeId, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {

                }
            }
            DBHelper.releaseConnection(c);
        }

        return false;
    }

    public static boolean isSNMPValueMatches(ResultSet rs, GroupRuleOperator operator, String mib)
            throws Exception {
        while (rs.next()) {
            String mibXml = rs.getString("mib");
            if (mibXml != null) {
                List<Element> rows = XMLUtil.getElements(mibXml, "RowList.Row");
                for (Element rowElement : rows) {
                    String value = XMLUtil.getElementText(rowElement, "Row.Column");
                    if (value == null || value.trim().isEmpty()) {
                        continue;
                    }
                    if (operator.equals(GroupRuleOperator.CONTAINS)) {
                        if (value.contains(mib)) {
                            return true;
                        }
                    } else if (operator.equals(GroupRuleOperator.MATCHES_EXPRESSION)) {
                        Pattern pattern = Pattern.compile(mib);
                        Matcher matcher = pattern.matcher(value);
                        if (matcher.find()) {
                            return true;
                        }
                    }
                }
            }
        }
        return false;
    }

    public static boolean isCommandMatches(int devId, String command, String value, GroupRuleOperator operator) {

        String decodedOutput = getShowCli(devId, command);

        if (decodedOutput == null || decodedOutput.trim().isEmpty()) {
            return false;
        }
        if (operator.equals(GroupRuleOperator.CONTAINS)) {
            return decodedOutput.contains(value);
        }
        if (operator.equals(GroupRuleOperator.MATCHES_EXPRESSION)) {
            return RegExpUtils.isMatching(decodedOutput, value);
        }
        return false;
    }

    public static String loadRunningConfig(int nodeId) {
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(
                    "select config from node_current_config where id= ? AND conf_type=" + Constants.RUN_CONF);
            // rs =
            // DBHelper.executeQuery("select config from node_current_config where id="
            // + nodeId +
            // " AND conf_type="
            // + Constants.STARTUP_CONF);
            ps.setInt(1, nodeId);
            rs = ps.executeQuery();
            if (rs.next()) {
                byte b[] = null;
                if (ServerProperties.getInstance().isPostgres()) {
                    b = rs.getBytes(1);
                } else {
                    Blob blob = rs.getBlob(1);
                    if (blob != null) {
                        b = blob.getBytes(1, (int) blob.length());
                    }
                }
                if (b != null) {
                    return CompressionUtils.getUncompressedString(b);
                }
            }
        } catch (Exception ex) {
            logger.error("Exception while loading running config for device: " + nodeId, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {

                }
            }
            DBHelper.releaseConnection(c);
        }
        return "";
    }

    public static String loadRunningConfigOld(int nodeId) {
        ResultSet rs = null;
        StringBuffer sbuf = new StringBuffer();

        try {
            rs = DBHelper.executeQuery("select * from ios_run_conf where id=" + nodeId + " AND conf_type="
                    + Constants.RUN_CONF + "  order by CONF_PART");
            while (rs.next()) {
                sbuf.append(rs.getString("run_conf"));
            }

        } catch (Exception ee) {
            logger.warn("Error while loading running config", ee);
        } finally {

            // logger.info("1Time taken to load Running conf: " +
            // node.getNodeName() + ", " + (endTime-startTime));

            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return sbuf.toString();
    }

    public static String loadStartupConfigOld(int nodeId) {
        ResultSet rs = null;
        StringBuffer sbuf = new StringBuffer();
        try {
            rs = DBHelper.executeQuery("select * from ios_run_conf where id=" + nodeId + " AND conf_type="
                    + Constants.STARTUP_CONF + "  order by CONF_PART");
            while (rs.next()) {
                sbuf.append(rs.getString("run_conf"));
            }

        } catch (Exception ee) {
            logger.warn("Error while loading startup config", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return sbuf.toString();
    }

    public static String getShowVersion(int nodeId) {
        ResultSet rs = null;
        StringBuffer sbuf = new StringBuffer();
        try {
            rs = DBHelper
                    .executeQuery("select * from version_dump where id=" + nodeId + "   order by VERSION_PART");
            while (rs.next()) {
                sbuf.append(rs.getString("version"));
            }
        } catch (Exception ee) {
            logger.warn("Error while getting show version.", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return sbuf.toString();

    }

    public static void deleteFromOldShowCommandsTable() {
        logger.info("Deleting show commands from old table");
        try {
            DBHelper.executeUpdate("delete from show_commands");
            logger.info("Done. Deleting show commands from old table");
        } catch (Exception ex) {
            logger.error("Exception while deleting from table show_commands", ex);
        }

    }

    private static void deleteFromOldShowCommandsTable(Set<String> toBeRemoved) throws SQLException {
        Connection c = null;
        PreparedStatement ps = null;
        logger.debug("Deleting " + toBeRemoved.size() + " rows from old show_commands file");
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("delete from show_commands_new where device_id=? and cli=?");
            for (String key : toBeRemoved) {
                int nodeId = -1;
                String cli = "";
                try {
                    String[] keys = key.split(":", 2);
                    nodeId = Integer.parseInt(keys[0]);
                    cli = keys[1];
                    ps.setInt(1, nodeId);
                    ps.setString(2, cli);
                    ps.executeUpdate();
                } catch (Exception ex) {
                    logger.error("Exception while deleteing " + nodeId + " CLI: " + cli, ex);
                }
            }
        } finally {
            try {
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (Exception ex) {
                    }
                }
            } finally {
                if (c != null) {
                    DBHelper.releaseConnection(c);
                }
            }
        }
    }

    public static void migrageShowCommands() throws Exception {
        try {
            Set<String> toBeRemoved = new HashSet<String>();
            boolean deleteAll = migrateShowCommandsInternal(toBeRemoved);
            if (deleteAll) {
                deleteFromOldShowCommandsTable();
            } else {
                deleteFromOldShowCommandsTable(toBeRemoved);
            }
        } catch (Exception ex) {
            logger.error("Exception while migrating CLIs from old show_commands table to new tbale.", ex);
        }
    }

    public static boolean migrateShowCommandsInternal(Set<String> toBeRemoved) throws Exception {
        Connection c = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        int curNodeId = -1;
        String curCli = null;
        StringBuilder curOp = new StringBuilder();
        // logger.info("Looking to see if we need to migrate from old show command tables to new one.");
        boolean deleteAll = true;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)");
            rs = DBHelper.executeQuery("select * from show_commands order by device_id,cli,output_part");
            int count = 0;
            while (rs != null && rs.next()) {
                int newDev = rs.getInt("device_id");
                String newCli = rs.getString("cli");
                if (newDev != curNodeId || !newCli.equals(curCli)) {
                    try {
                        ps.setInt(1, curNodeId);
                        ps.setString(2, curCli);
                        ps.setObject(3, curOp.toString().getBytes());
                        curOp = new StringBuilder();
                        ps.executeUpdate();
                        toBeRemoved.add(curNodeId + ":" + curCli);
                    } catch (Exception ex) {
                        deleteAll = false;
                        logger.trace("Exception while inserting into new table", ex);
                    }
                }
                curOp.append(rs.getString("out_put"));
                curNodeId = newDev;
                curCli = newCli;
                count++;
            }
            logger.info("Done migrating from old show_commands table to new table. Migrated " + count + " rows");

        } catch (Exception ee) {
            deleteAll = false;
            throw ee;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
            } finally {
                try {
                    if (ps != null) {
                        ps.close();
                    }
                } catch (Exception ex) {
                } finally {
                    try {
                        if (c != null) {
                            DBHelper.releaseConnection(c);
                        }
                    } catch (Exception ex) {
                    }
                }
            }
        }
        return deleteAll;
    }

    public static String[] getAvailableShowCommands(int devId) {
        ArrayList<String> showCommands = new ArrayList<String>();
        ResultSet rs = null;
        try {
            String whereClause = "";
            if (devId != -1) {
                whereClause = " where device_id=" + devId;
            }

            rs = DBHelper.executeQuery("select distinct cli from show_commands_new" + whereClause);
            while (rs.next()) {
                showCommands.add(rs.getString("cli"));
            }
        } catch (Exception ee) {
            logger.error("Exception while getting available CLIs for device: " + devId, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }

        String[] cmds = new String[showCommands.size()];
        showCommands.toArray(cmds);

        return cmds;
    }

    public static String getShowCli(int nodeId, String cli) {
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        String sql = "select out_put from show_commands_new where device_id=? and cli=?";
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(sql);
            ps.setInt(1, nodeId);
            ps.setString(2, cli);
            rs = ps.executeQuery();
            byte b[] = null;
            if (rs != null && rs.next()) {
                if (ServerProperties.getInstance().isPostgres()) {
                    b = rs.getBytes(1);
                } else {
                    Blob blob = rs.getBlob(1);
                    if (blob != null) {
                        b = blob.getBytes(1, (int) blob.length());
                    }
                }

                if (b != null) {
                    return CompressionUtils.getUncompressedString(b);
                }
            }

        } catch (Exception ee) {
            logger.warn("Error while getting show cli for the node = " + nodeId + " and cli = " + cli, ee);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception ee) {
                }
            }
            if (c != null) {
                try {
                    DBHelper.releaseConnection(c);
                } catch (Exception ex) {
                }
            }
        }
        return "";
    }

    public static String getShowSyslog(int nodeId, Map variables) {
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        long startTime = 0, endTime = 0;
        int timeGap = 0;
        if (variables.containsKey("syslogTimeInterval")) {
            timeGap = (int) variables.get("syslogTimeInterval");

            if (timeGap != -1) {
                endTime = System.currentTimeMillis();
                startTime = System.currentTimeMillis() - TimeUnit.MINUTES.toMillis(timeGap);
            }

        } else {
            // if syslogTimeInterval is not specified, we are expecting the input names should be endTime and startTime.
            // More enhancements can be done to support different names

            endTime = (int) variables.get("endTime");
            startTime = (int) variables.get("startTime");
        }
        String sql = "select COMPARE_STR|| ': ' || MESSAGE as message_desc from syslog_messages where device_id=?";
        if (timeGap != -1) {
            sql += " and RECEIVED_AT " + "BETWEEN TO_TIMESTAMP(?) AND to_TIMESTAMP(?) ORDER BY RECEIVED_AT";

        }
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(sql);
            ps.setInt(1, nodeId);
            if (timeGap != -1) {
                ps.setTimestamp(2, new Timestamp(startTime));
                ps.setTimestamp(3, new Timestamp(endTime));
            }
            rs = ps.executeQuery();
            byte b[] = null;
            StringBuffer ab = new StringBuffer();

            while (rs != null && rs.next()) {

                ab.append(rs.getString("message_desc"));

                ab.append("\n");

            }
            return ab.toString();

        } catch (Exception ee) {
            logger.warn("Error while getting show cli for the node = " + nodeId, ee);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception ee) {
                }
            }
            if (c != null) {
                try {
                    DBHelper.releaseConnection(c);
                } catch (Exception ex) {
                }
            }
        }
        return "";
    }

    public static void saveClisOptimized(int nodeId, IDSPDeviceInfoProvider device, JobParameters jobParameters) {
        String[] clis = device.getAllClis();
        if (clis == null || clis.length == 0) {
            return;
        }
        deleteClisOptimized(nodeId, clis);
        insertClisOptimized(nodeId, clis, device, jobParameters);

    }

    public static void saveNetconfResponse(int nodeId, IDSPDeviceInfoProvider device) {
        Set<String> colIds = device.getNetconfDatasetIds();
        if (colIds == null || colIds.size() == 0) {
            return;
        }
        deleteNetconfCollection(nodeId, colIds);
        insertNetconfCollection(nodeId, colIds, device);

    }

    public static NetconfOutput getNetconfResponse(int nodeId, String datasetId) {
        NetconfOutput output = null;
        Connection con = null;
        try {
            con = DBHelper.getConnection();
            String sql = "SELECT operation_name, request, response from netconf_collection where device_id = ? AND collection_id = ?";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, nodeId);
                ps.setString(2, datasetId);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    byte[] compressedOut = rs.getBytes("response");
                    if (compressedOut != null) {
                        output = new NetconfOutput(CompressionUtils.getUncompressedString(compressedOut));
                    }
                    output.setOperation(rs.getString("operation_name"));
                    output.setRequest(rs.getString("request"));
                }

            }
        } catch (Exception e) {
            logger.error("Error while retrieving netconf output from db", e);
        } finally {
            DBHelper.releaseConnection(con);
        }
        return output;

    }

    private static void insertClisOptimized(int nodeId, String[] clis, IDSPDeviceInfoProvider device,
            JobParameters jobParameters) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)");

            for (String cli : clis) {
                DSPCLICommandOutput output = device.getCLI(cli);
                if (output != null) {
                    String outputText = output.getOutput();
                    if (outputText == null) {
                        outputText = output.getErrors();
                    }
                    if (outputText == null) {
                        continue;
                    }
                    if (outputText.startsWith(cli)) {
                        outputText = outputText.substring(cli.length());
                    }
                    ps.clearParameters();
                    ps.setInt(1, nodeId);
                    ps.setString(2, cli);
                    ps.setObject(3, CompressionUtils.getCompressedString(outputText));
                    ps.executeUpdate();

                    CommandResult result = new CommandResult();
                    result.setOperation(Operation.UPDATE);
                    result.setOperatedBy(jobParameters.getUserName());
                    result.setJobId(jobParameters.getParentJobId());
                    result.setInventoryJobId(jobParameters.getJobId());
                    result.setCli(cli);
                    result.setDeviceId((long) nodeId);
                    result.setOutput(CompressionUtils.getCompressedString(outputText));
                    HistoryManager.getInstance().createRecord(result);
                }
            }
        } catch (Exception ex) {
            logger.error("Exception while saving CLIs for node: " + nodeId, ex);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {

                }
            }
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        }
    }

    private static void insertNetconfCollection(int nodeId, Set<String> colIds, IDSPDeviceInfoProvider device) {
        Connection c = null;
        try {
            c = DBHelper.getConnection();
            String stmt = "INSERT INTO netconf_collection (device_id, collection_id, operation_name, request, response) VALUES (?, ?, ?, ?, ?)";
            try (PreparedStatement ps = c.prepareStatement(stmt)) {
                for (String colId : colIds) {
                    DSPNetconfCommandOutput output = device.getNetconfOutput(colId);
                    if (output != null) {
                        String operation = output.getOperation();
                        String request = output.getRequest();
                        String outputText = output.getOutput();
                        if (outputText == null) {
                            outputText = output.getErrors();
                        }
                        if (outputText == null) {
                            continue;
                        }
                        if (outputText.startsWith(colId)) {
                            outputText = outputText.substring(colId.length());
                        }
                        ps.clearParameters();
                        ps.setInt(1, nodeId);
                        ps.setString(2, colId);
                        ps.setString(3, operation);
                        ps.setString(4, request);
                        ps.setObject(5, CompressionUtils.getCompressedString(outputText));
                        ps.addBatch();
                    }
                }
                ps.executeBatch();
            }
        } catch (Exception ex) {
            logger.error("Exception while saving netconf collection for node: " + nodeId, ex);
        } finally {
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        }
    }

    private static void deleteClisOptimized(int nodeId, String[] clis) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBHelper.getConnection();
            StringBuilder str = new StringBuilder("delete from show_commands_new where device_id=? and cli in (");
            boolean first = true;
            for (int i = 0; i < clis.length; i++) {

                if (!first) {
                    str.append(",");
                }
                str.append("?");
                first = false;
            }
            str.append(")");
            ps = c.prepareStatement(str.toString());
            ps.setInt(1, nodeId);
            for (int i = 0; i < clis.length; i++) {
                ps.setString(i + 2, clis[i]);
            }
            ps.executeUpdate();
        } catch (Exception ex) {
            logger.error("Exception while saving show commands for node: " + nodeId, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Throwable th) {

            }
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        }
    }

    private static void deleteNetconfCollection(int nodeId, Set<String> colIds) {
        Connection c = null;
        try {
            c = DBHelper.getConnection();
            StringBuilder str = new StringBuilder(
                    "DELETE FROM netconf_collection where device_id=? and collection_id in (");
            boolean first = true;
            for (String colId : colIds) {
                if (!first) {
                    str.append(",");
                }
                str.append("?");
                first = false;
            }
            str.append(")");
            try (PreparedStatement ps = c.prepareStatement(str.toString())) {
                ps.setInt(1, nodeId);
                int i = 1;
                for (String colId : colIds) {
                    ps.setString(i + 1, colId);
                    i++;
                }
                ps.executeUpdate();
            }
        } catch (Exception ex) {
            logger.error("Exception while deleting netconf collection for node: " + nodeId, ex);
        } finally {
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        }
    }

    public static void saveShowCommand(int nodeId, String cli, String output) {
        try {
            String query = "delete from show_commands_new where device_id=" + nodeId + " and cli='" + cli + "'";
            DBHelper.executeUpdate(query);
        } catch (Exception ex) {
            logger.error("Exception while deleting show command : " + nodeId + " and cli='" + cli + "'", ex);
        }
        PreparedStatement ps = null;
        Connection c = null;
        if (output == null || output.trim().isEmpty()) {
            return;
        }

        output = output.trim();

        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)");
            if (output.startsWith(cli)) {
                output = output.substring(cli.length());
            }
            ps.setInt(1, nodeId);
            ps.setString(2, cli);
            ps.setObject(3, CompressionUtils.getCompressedString(output));

            ps.executeUpdate();
        } catch (Exception ee) {
            logger.warn("Error while saving show cli for the node = " + nodeId + " and cli = " + cli, ee);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static String getStartupVersion(int deviceId, String runConfVersion) {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from cvs_runconf_startconf where device_id=" + deviceId
                    + " AND runconf_version=" + runConfVersion);
            if (rs.next()) {
                return rs.getString("startconf_version");
            }
        } catch (Exception ee) {
            logger.warn("Error while getting show startup version the node = " + deviceId, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        return null;
    }

    public static void insertConfRelations(NetworkNode node) {
        ResultSet rs = null;
        try {
            // StringBuffer runConfFile = new
            // StringBuffer("devices").append(File.separator).
            // append(node.getDeviceFamily().toLowerCase()).append(File.separator).append(node.getNodeId()).
            // append(File.separator).append(Constants.CVS_FILE_TYPE_RUNCONF);
            //
            // StringBuffer startConfFile = new
            // StringBuffer("devices").append(File.separator).
            // append(node.getDeviceFamily().toLowerCase()).append(File.separator).append(node.getNodeId()).
            // append(File.separator).append(Constants.CVS_FILE_TYPE_STARTCONF);

            int latestRunConfVersion = NewVersionControlManager.getInstance().getLatestVersion(node.getNodeId(),
                    ConfFileType.RUNNING_CONFIG.confType);
            int latestStartConfVersion = NewVersionControlManager.getInstance().getLatestVersion(node.getNodeId(),
                    ConfFileType.STARTUP_CONFIG.confType);

            // String runLatest =
            // VersionControlManager.getManager().getLatestVersion(runConfFile.toString());
            // String startLatest =
            // VersionControlManager.getManager().getLatestVersion(startConfFile.toString());

            rs = DBHelper.executeQuery("select * from cvs_runconf_startconf where device_id=" + node.getNodeId()
                    + " AND runconf_version=" + latestRunConfVersion);
            if (rs.next()) {
                DBHelper.executeUpdate("update cvs_runconf_startconf set startconf_version="
                        + latestStartConfVersion + " where device_id=" + node.getNodeId() + " AND runconf_version="
                        + latestRunConfVersion);
            } else {
                DBHelper.executeUpdate("insert into cvs_runconf_startconf values(" + node.getNodeId() + ","
                        + latestRunConfVersion + "," + latestStartConfVersion + ")");
            }

        } catch (Exception ee) {
            logger.warn("Error in insertConfRelations", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }

    }

    public static int getDeviceCount() {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select count(*) from nodes where state='m'");
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception ee) {
            logger.warn("Error while getting device count", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }

        return -1;
    }

    public static int getCummilativeDeviceCount() {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select max(id) from nodes");
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception ee) {
            logger.warn("Error while getting cummilative device count", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }

        return -1;
    }

    public static void insertModuleInfo(int moduleId, int parentNodeId, String slotNumber, int moduleType) {
        if (slotNumber == null) {
            return;
        }
        try {
            String delQuery = "delete from nodes_modules where mod_id=" + moduleId + " AND id=" + parentNodeId
                    + " AND slot_number='" + slotNumber + "'" + " AND module_type=" + moduleType;
            System.err.println(delQuery);
            DBHelper.executeUpdate(delQuery);
            DBHelper.executeUpdate("insert into nodes_modules VALUES(" + moduleId + "," + parentNodeId + ",'"
                    + slotNumber + "'," + moduleType + ")");
        } catch (Exception ee) {
            logger.warn("Error while inserting module info for the node=" + parentNodeId + " moduleId= " + moduleId
                    + " slot=" + slotNumber + " moduleType=" + moduleType, ee);
        }
    }

    public static void deleteModuleInfo(int moduleId, int parentNodeId, String slotNumber, int moduleType) {
        if (slotNumber == null) {
            return;
        }
        try {
            String delQuery = "delete from nodes_modules where mod_id=" + moduleId + " AND id=" + parentNodeId
                    + " AND slot_number='" + slotNumber + "'" + " AND module_type=" + moduleType;
            System.err.println(delQuery);
            DBHelper.executeUpdate(delQuery);
        } catch (Exception ee) {
            logger.warn("Error while inserting module info for the node=" + parentNodeId + " moduleId= " + moduleId
                    + " slot=" + slotNumber + " moduleType=" + moduleType, ee);
        }
    }

    public static void loadModules(NetworkNode node) {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from nodes_modules where mod_id=" + node.getNodeId());
            if (rs != null && rs.next()) {
                int parentId = rs.getInt("id");
                String slotNumber = rs.getString("slot_number");
                int moduleType = rs.getInt("module_type");
                ModuleInfo mInfo = new ModuleInfo();
                mInfo.setParentId(parentId);
                mInfo.setSlotNumber(slotNumber);
                mInfo.setType(ModuleType.getFromInt(moduleType));
                node.setSupportedModuleInfo(mInfo);
            }
        } catch (Exception ee) {
            logger.warn("Error while loading mdoule info", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
    }

    public static void deleteCustomerDiscoveredDevices(int custId, int instanceId) {
        try {
            DBHelper.executeUpdate("delete from discovered_devices where customer_id=" + custId
                    + " and instance_id=" + instanceId);
        } catch (Exception ex) {
            logger.warn("Failed to delete discovered device list of  wing instance  ->" + custId + ":" + instanceId,
                    ex);
        }
    }

    public static void insertDiscoveredDevice(DiscoveredDevice discDevice) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            DBHelper.executeUpdate("delete from discovered_devices where ipaddress='" + discDevice.getIpAddress()
                    + "' and customer_id=" + discDevice.getCustomer_id() + " and instance_id="
                    + discDevice.getInstanceId());

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_DISCOVERED_DEVICE);
            ps.setInt(1, discDevice.getCustomer_id());
            ps.setInt(2, discDevice.getInstanceId());
            ps.setString(3, discDevice.getIpAddress());
            ps.setString(4, discDevice.getMacAddress());
            ps.setString(5, discDevice.getNodeName());
            ps.setString(6, discDevice.getDescription());
            ps.setString(7, discDevice.getDevice_family());
            ps.setString(8, discDevice.getProduct_family());
            ps.setString(9, discDevice.getProduct_model());
            ps.setString(10, discDevice.getOSName());
            ps.setString(11, discDevice.getOSVersion());
            ps.setString(12, discDevice.getVendor_name());
            if (discDevice.isManaged()) {
                ps.setString(13, "Yes");
            } else {
                // CSCtr61612 - Setting of 'isManaged' is based on if the
                // network
                // node is present in the same customer and instance
                VirtualDevice vd = new VirtualDevice();
                vd.setIpAddress(discDevice.getIpAddress());
                int[] nodes = NetworkNodeCache.getInstance().getMatchingNodeIds(vd, discDevice.getCustomer_id());
                if (nodes != null && nodes.length > 0) {
                    ps.setString(13, "Yes");
                } else {
                    ps.setString(13, "No");
                }
            }
            ps.setString(14, discDevice.getDiscovered_from());
            ps.setString(15, discDevice.getDiscovery_method());
            ps.setString(16, discDevice.getDiscovery_credential());
            ps.setString(17, null);
            ps.setTimestamp(18, discDevice.getDiscovery_time() == null ? (new Timestamp(System.currentTimeMillis()))
                    : (new Timestamp(Long.parseLong(discDevice.getDiscovery_time()))));

            ps.executeUpdate();

            // customer_id, ipaddress, macaddress, hostname, device_family,
            // product_family, product_model, vendor_name,
            // discovered_from, discovery_method, discovery_credential,
            // current_state, discovery_time

        } catch (Exception ee) {
            ee.printStackTrace();
            logger.warn("Error while inserting into discovered devices", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    private static boolean isModulesPopulated() {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select count(*) from module_eol_eox");
            if (rs != null && rs.next()) {
                int count = rs.getInt(1);
                if (count > 0) {
                    return true;
                } else {
                    return false;
                }
            }

        } catch (Exception ee) {
            logger.warn("Error while checking to see iof modules are populated", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return false;
    }

    public static void insertModuleEolInfo(String modFilePath) {
        if (InventoryDBHelper.isModulesPopulated()) {
            return;
        }
        Connection c = null;
        PreparedStatement ps = null;
        InputStream in = null;
        try {
            if (modFilePath != null && (modFilePath.startsWith("/") || modFilePath.startsWith("\\"))) {
                modFilePath = modFilePath.substring(1);
            }
            in = Thread.currentThread().getContextClassLoader().getResourceAsStream(modFilePath);

            c = DBHelper.getConnection();
            c.setAutoCommit(false);
            ps = c.prepareStatement(DBHelperConstants.INSERT_MODULE_EOL_DB);

            StringBuffer sbuf = new StringBuffer();
            byte[] buf = new byte[8096];
            int read = -1;
            while ((read = in.read(buf)) != -1) {
                sbuf.append(new String(buf, 0, read));
            }

            StringTokenizer st = new StringTokenizer(sbuf.toString(), "\n");
            if (st.hasMoreElements()) {
                st.nextElement(); // takeout the header
            }
            while (st.hasMoreElements()) {
                String line = st.nextToken().trim();
                if (line.length() == 0) {
                    continue;
                }
                StringTokenizer stt = new StringTokenizer(line, "\t");
                String fru = null;
                String deviceType = null;
                String url = null;
                Timestamp aDate = null;
                Timestamp eosDate = null;
                Timestamp eolDate = null;
                Timestamp eocrDate = null;
                Timestamp eoeDate = null;
                if (stt.hasMoreTokens()) {
                    fru = stt.nextToken();
                }
                if (stt.hasMoreTokens()) {
                    deviceType = stt.nextToken();
                }
                if (stt.hasMoreTokens()) {
                    url = stt.nextToken();
                }
                if (stt.hasMoreTokens()) {
                    aDate = getTimestamp(stt.nextToken());
                }
                if (stt.hasMoreTokens()) {
                    eosDate = getTimestamp(stt.nextToken());
                }
                if (stt.hasMoreTokens()) {
                    eolDate = getTimestamp(stt.nextToken());
                }
                if (stt.hasMoreTokens()) {
                    eoeDate = getTimestamp(stt.nextToken());
                }
                if (stt.hasMoreTokens()) {
                    eocrDate = getTimestamp(stt.nextToken());
                }

                ps.setString(1, fru);
                ps.setString(2, url);
                ps.setString(3, deviceType);
                ps.setTimestamp(4, aDate);
                ps.setTimestamp(5, eosDate);
                ps.setTimestamp(6, eolDate);
                ps.setTimestamp(7, eocrDate);
                ps.setTimestamp(8, eoeDate);
                ps.executeUpdate();
            }
        } catch (Exception ee) {
            logger.warn("Error in insertModuleEolInfo", ee);
        } finally {
            try {
                c.commit();

            } catch (Exception ee) {
            }
            try {
                c.setAutoCommit(true);

            } catch (Exception ee) {
            }

            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
            try {
                if (in != null) {
                    in.close();
                }
            } catch (IOException ex) {
                logger.error("Exception in closing InputStream in InventoryDBHelper", ex);
            }
        }
    }

    private static Timestamp getTimestamp(String str) {
        if (str == null || str.length() == 0) {
            return null;
        }
        long t = getMyDate(str);
        if (t == -1) {
            return null;
        } else {
            return (new Timestamp(t));
        }
    }

    public static long getMyDate(String str) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy");
            Date d = sdf.parse(str);
            return d.getTime();
        } catch (Exception ee) {
            logger.info("Error while parsing date for the string=" + str, ee);
            return -1;
        }
    }

    public static void insertShowDiagCommand(int devId, String moduleName, String moduleDescr, String pid,
            String vid, String serialNum) {
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        try {
            if (serialNum != null) {
                rs = DBHelper.executeQuery("select * from DEVICE_MODULE_INFO where device_id=" + devId
                        + " and SERIALNUM='" + serialNum + "'");
            }
            boolean flag = false;
            if (rs != null && rs.next()) {
                flag = true;
            }

            try {
                rs.close();
            } catch (Exception ex) {
            }

            if (flag) {
                String PID = rs.getString("PID");
                if (PID != null && PID.trim().length() > 0) {
                    return;
                } else if (pid != null && pid.length() > 0) {
                    String mName = rs.getString("module_name");
                    String mDescr = rs.getString("module_descr");

                    if (mName != null && mName.length() > 0) {
                        moduleName = mName;
                    }
                    if (mDescr != null && mDescr.length() > 0) {
                        moduleDescr = mDescr;
                    }
                    DBHelper.executeUpdate("delete from DEVICE_MODULE_INFO where device_id=" + devId
                            + " and SERIALNUM='" + serialNum + "'");
                } else {
                    return;
                }
            }

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_SHOW_DIAG_MODULE);
            ps.setInt(1, devId);
            ps.setString(2, moduleName);
            ps.setString(3, moduleDescr);
            ps.setString(4, pid);
            ps.setString(5, vid);
            ps.setString(6, serialNum);

            ps.executeUpdate();

        } catch (Exception ee) {
            logger.warn("Error while inserting show diag command for the device=" + devId, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
            }
            try {
                if (c != null) {
                    DBHelper.releaseConnection(c);
                }
            } catch (Exception e) {
            }
        }
    }

    public static void insertModuleData(int devId, DeviceModule module) {
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_SHOW_MODULE);
            ps.setInt(1, devId);
            ps.setString(2, module.getName());
            ps.setString(3, module.getDescr());
            ps.setString(4, module.getPID());
            ps.setString(5, module.getSerialNumber());
            ps.setString(6, module.getModuleType().name());
            ps.setInt(7, module.getParentIndex());
            ps.setInt(8, module.getMyIndex());
            ps.setInt(9, module.getRelPos());
            ps.setString(10, module.getSlot());
            ps.setString(11, module.getVendorType());
            ps.setString(12, module.getHwVersion());
            ps.setInt(13, module.isFruable() ? 1 : 0);

            ps.executeUpdate();

        } catch (Exception ee) {
            logger.warn("Error while inserting show diag command for the device=" + devId, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
            }
            try {
                if (c != null) {
                    DBHelper.releaseConnection(c);
                }
            } catch (Exception e) {
            }
        }
    }

    // public static void insertCatOsChassisAsModule(NetworkNode newNode) throws
    // Exception {
    public static void insertCatOsModules(Connection c, int nodeId, String model, String moduleDescr, String pid,
            String serialNumber) throws Exception {

        PreparedStatement ps = null;
        try {

            try {
                ps = c.prepareStatement(DBHelperConstants.INSERT_SHOW_DIAG_MODULE);
                ps.setInt(1, nodeId);
                ps.setString(2, model);
                ps.setString(3, moduleDescr);
                ps.setString(4, pid);
                ps.setString(5, "");
                ps.setString(6, serialNumber);

                ps.executeUpdate();

            } catch (Exception ee) {
                logger.warn("Error while insertring catOS modules", ee);
            } finally {
                try {
                    if (ps != null) {
                        ps.close();
                    }
                } catch (Exception e) {
                }
            }
        } catch (Exception ee) {

        }

    }

    public static void insertModuleInfoSnmp(int nodeId, String descr, String name, String mclass, String serialnum,
            String modelname) {
        // TODO Auto-generated method stub
    }

    public static void deleteVoipPhones(int customerId, int instanceId) {
        try {
            DBHelper.executeUpdate(
                    "delete from VOIP_PHONES where customer_id=" + customerId + " and instance_id=" + instanceId);
        } catch (Exception ex) {
            logger.warn("Failed to delete the VoIP phones associated with the customer " + customerId + ":"
                    + instanceId);
        }
    }

    /**
     * @param profile
     *            , creator Id
     */
    public static int insertCollectionProfile(CollectionProfile profile, int creatorId) throws Exception {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_COLLECTION_PROFILE);
            ps.setString(1, profile.getProfileName());
            ps.setString(2, profile.getProfileDescr());
            ps.setString(3, profile.getDatasetsString());
            ps.setString(4, profile.isAllDevicesSelected() ? "y" : "n");
            ps.setString(5, profile.getGroupsStr());
            ps.setInt(6, -1);
            ps.setTimestamp(7, (new Timestamp(profile.getCreatedAt())));
            ps.setTimestamp(8, null);
            ps.setInt(9, creatorId);
            ps.setInt(10, -1);
            ps.setString(11, profile.isSystemProfile() ? "y" : "n");
            ps.setString(12, profile.getCommandCLI());
            ps.setInt(13, profile.isRunDiscovery() ? 1 : 0);
            ps.setInt(14, profile.isRunDAV() ? 1 : 0);
            ps.executeUpdate();

            int profile_id = -1;

            ResultSet rs = null;
            try {
                rs = DBHelper.executeQuery(
                        "select * from collection_profile where profile_name='" + profile.getProfileName() + "'");
                if (rs.next()) {
                    profile_id = rs.getInt("profile_id");
                    profile.setProfId(profile_id);
                }
                if (profile.getFtpDetails() != null) {
                    ServerDBHelper.saveFtpServerDetails(profile.getFtpDetails(), profile.getProfileName());
                }
            } catch (Exception ee) {
                logger.warn("Exception while inserting collection profile", ee);
                return -1;
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                } catch (Exception ex) {
                    logger.warn("Exception while closing the resultset in inserting collection profile", ex);
                }
                try {
                    if (ps != null) {
                        ps.close();
                    }
                } catch (Exception ex) {
                    logger.warn("Exception while closing the statement in inserting collection profile", ex);
                }

            }

            try {
                DBHelper.executeUpdate(
                        "delete from collection_prof_devices where profile_id=" + profile.getProfId());

                int size = profile.getNodesStr().length();

                if (size > 0) {
                    int count = size / 4000 + 1;
                    String idsStr = profile.getNodesStr();
                    for (int i = 0; i < count; i++) {
                        String ids = (idsStr.length() > 4000) ? idsStr.substring(0, 4000) : idsStr;
                        if (ids.length() > 0) {
                            DBHelper.executeUpdate("insert into collection_prof_devices values ("
                                    + profile.getProfId() + "," + i + ",'" + ids + "')");

                            idsStr = (idsStr.length() > 4000) ? idsStr.substring(4001) : "";
                        }
                    }
                }
            } catch (Exception ee) {
                logger.warn("Exception while removing old device in inserting collection profile", ee);
            }

            if (profile.getScheduleDetails() != null) {
                System.err.println("SCHED:" + profile.getScheduleDetails().toString());
                int jobId = JobMgr.getInstance().scheduleCollectionProfileJob(profile, creatorId);
                profile.setJobId(jobId);
                setCollectionProfileJobId(profile.getProfId(), jobId);
            }
            profile.setProfId(profile_id);
            return profile_id;

        } catch (Exception ee) {
            logger.warn("Error while inserting collection profile", ee);
            throw ee;
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
                logger.warn("Exception while closing the statement in inserting collection profile", e);
            }

            try {
                if (c != null) {
                    DBHelper.releaseConnection(c);
                }
            } catch (Exception e) {
                logger.warn("Exception while closing the connection in inserting collection profile", e);
            }
        }
    }

    public static void modifyCollectionProfile(CollectionProfile profile, int creatorId, boolean updateSchedule) {
        Connection c = null;
        PreparedStatement ps = null;
        PreparedStatement ps1 = null;

        try {
            c = DBHelper.getConnection();

            ps = c.prepareStatement(DBHelperConstants.MODIFY_COLLECTION_PROFILE);
            ps.setString(1, profile.getProfileDescr());
            ps.setString(2, profile.getDatasetsString());
            ps.setString(3, profile.isAllDevicesSelected() ? "y" : "n");
            ps.setString(4, profile.getGroupsStr());
            ps.setTimestamp(5, (new Timestamp(profile.getModifiedAt())));
            ps.setInt(6, creatorId);
            ps.setString(7, profile.getCommandCLI());
            ps.setInt(8, profile.isRunDiscovery() ? 1 : 0);
            ps.setInt(9, profile.isRunDAV() ? 1 : 0);

            ps.setInt(10, profile.getProfId());
            try {
                ps.executeUpdate();

                if (profile.getFtpDetails() != null) {
                    ServerDBHelper.saveFtpServerDetails(profile.getFtpDetails(), profile.getProfileName());
                }
            } catch (Exception ex) {
                logger.warn("Unable to modify collection profile", ex);
            }

            try {
                DBHelper.executeUpdate(
                        "delete from collection_prof_devices where profile_id=" + profile.getProfId());

                int size = profile.getNodesStr().length();
                if (size > 4000) {
                    int count = size / 4000 + 1;
                    String idsStr = profile.getNodesStr();

                    for (int i = 0; i < count; i++) {
                        String ids = (idsStr.length() > 4000) ? idsStr.substring(0, 4000) : idsStr;
                        if (ids.length() > 0) {
                            DBHelper.executeUpdate("insert into collection_prof_devices VALUES ("
                                    + profile.getProfId() + "," + i + ",'" + ids + "')");

                            idsStr = (idsStr.length() > 4000) ? idsStr.substring(4001) : "";
                        }
                    }
                } else if (size > 0) {
                    DBHelper.executeUpdate("insert into collection_prof_devices VALUES (" + profile.getProfId()
                            + "," + 0 + ",'" + profile.getNodesStr() + "')");

                }
            } catch (Exception ee) {
                logger.warn("Unable to update device details in collection profile", ee);
            }
            if (updateSchedule) {
                if (profile.getJobId() == -1) {
                    int jobId = JobMgr.getInstance().scheduleCollectionProfileJob(profile, creatorId);
                    profile.setJobId(jobId);
                    setCollectionProfileJobId(profile.getProfId(), jobId);
                } else {
                    String[] jobAndGrp = ServerDBHelper.getJobAndGrpNames(profile.getJobId());
                    if (jobAndGrp != null && jobAndGrp.length == 2) {
                        JobMgr.getInstance().editJob(jobAndGrp[0], jobAndGrp[1], profile.getProfileDescr(),
                                profile.getScheduleDetails(), profile.getNotificationPrefs());
                    }
                }
            }
        } catch (Exception ee) {
            logger.warn("Error while inserting collection profile", ee);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
                logger.warn("Exception while closing the statement in inserting collection profile", e);
            }

            try {
                if (ps1 != null) {
                    ps1.close();
                }
            } catch (Exception e) {
                logger.warn("Exception while closing the statement in inserting collection profile", e);
            }
            try {
                if (c != null) {
                    DBHelper.releaseConnection(c);
                }
            } catch (Exception e) {
                logger.warn("Exception while closing the connection in inserting collection profile", e);
            }
        }
    }

    public static void setCollectionProfileJobId(int profileId, int jobId) {
        try {
            DBHelper.executeUpdate(
                    "update collection_profile set jobid = " + jobId + " where profile_id=" + profileId);
        } catch (Exception ee) {
            logger.warn("Unable to set the Collection Profile Job Id", ee);
        }
    }

    public static void loadCollectionProfiles(ConcurrentHashMap<Integer, CollectionProfile> systemDefinedProfiles,
            ConcurrentHashMap<Integer, CollectionProfile> userDefinedProfiles) {
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery("select * from collection_profile");
            while (rs.next()) {
                CollectionProfile prof = new CollectionProfile();
                prof.setProfId(rs.getInt("profile_id"));
                prof.setProfileName(rs.getString("profile_name"));
                prof.setProfileDescr(rs.getString("profile_descr"));
                String datasetStr = rs.getString("datasets");
                if (datasetStr != null) {
                    prof.setDatasets(datasetStr);
                }
                prof.setJobId(rs.getInt("jobid"));
                if (prof.getJobId() != -1) {
                    if (JobMgr.getInstance().isJobScheduled(prof.getJobId())) {
                        PariJobDetails jDetails = JobMgr.getInstance().getJobDetail(prof.getJobId());
                        if (jDetails != null) {
                            prof.setScheduleDetails(jDetails.getSchedDetails());
                            prof.setNotificationPrefs(
                                    (CollectionProfileNotificationPrefs) jDetails.getJobSpecificData());
                        }
                    }
                }

                String allDevices = rs.getString("alldevices");
                if (allDevices.equals("y")) {
                    prof.setAllDevicesSelected(true);
                } else {
                    prof.setAllDevicesSelected(false);
                }
                StringBuffer sbuf = new StringBuffer();
                try {
                    ResultSet rss = DBHelper.executeQuery("select * from collection_prof_devices where profile_id="
                            + prof.getProfId() + " order by sequence_id");

                    while (rss.next()) {
                        sbuf.append(rss.getString("device_ids"));
                    }

                    try {
                        rss.close();
                    } catch (Exception ee) {
                        logger.warn("Exception while closing the resultset in inserting collection profile", ee);
                    }
                } catch (Exception ee) {
                    logger.warn("Error while loading device to collection profile", ee);
                }

                String nodeStr = sbuf.toString();
                if (nodeStr != null && nodeStr.length() > 0) {
                    String[] nodeIds = nodeStr.split(",");
                    Integer[] nodes = new Integer[nodeIds.length];
                    for (int i = 0; i < nodeIds.length; i++) {
                        try {
                            nodes[i] = Integer.parseInt(nodeIds[i]);
                        } catch (Exception ee) {
                        }
                    }

                    prof.setNodes(nodes);
                }

                String groupStr = rs.getString("group_ids");

                if (groupStr != null && groupStr.trim().length() > 0) {
                    String[] groupIds = groupStr.split(",");
                    Integer[] groups = new Integer[groupIds.length];
                    for (int i = 0; i < groupIds.length; i++) {
                        try {
                            groups[i] = Integer.parseInt(groupIds[i]);
                        } catch (Exception ee) {
                        }
                    }

                    prof.setGroups(groups);
                }

                Timestamp ts = rs.getTimestamp("created_at");
                if (ts != null) {
                    prof.setCreatedAt(ts.getTime());
                }

                ts = rs.getTimestamp("modified_at");
                if (ts != null) {
                    prof.setModifiedAt(ts.getTime());
                }

                prof.setCreatorId(rs.getInt("creator_id"));
                prof.setModifierId(rs.getInt("modifier_id"));

                String sysProfile = rs.getString("system_defined").trim();
                if (sysProfile.equals("y")) {
                    prof.setSystemProfile(true);
                } else {
                    prof.setSystemProfile(false);
                }

                int runDiscovery = rs.getInt("run_discovery");
                if (runDiscovery == 1) {
                    prof.setRunDiscovery(true);
                } else {
                    prof.setRunDiscovery(false);
                }

                int runDAV = rs.getInt("run_dav");
                if (runDAV == 1) {
                    prof.setRunDAV(true);
                } else {
                    prof.setRunDAV(false);
                }

                String cliCommand = rs.getString("cli_command");
                prof.setCommandCLI(cliCommand);

                if (prof.isSystemProfile()) {
                    systemDefinedProfiles.put(prof.getProfId(), prof);
                } else {
                    userDefinedProfiles.put(prof.getProfId(), prof);
                }

                FtpServerDetails ftpDetails = ServerDBHelper.getFtpServerPreferences(prof.getProfileName());
                if (ftpDetails != null) {
                    prof.setFtpDetails(ftpDetails);
                }

            }
        } catch (Exception ee) {
            logger.warn("Error while loading the collection profiles", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                logger.warn("Exception while closing the resultset in inserting collection profile", ex);
            }
        }
    }

    public static void deleteCollectionProfile(int profId) {
        try {
            DBHelper.executeUpdate("delete from collection_profile where profile_id=" + profId);
        } catch (Exception ee) {
            logger.error("Error while deleting collection profile", ee);
        }
    }

    public static boolean isCollectionProfileExists(String collectionProfile) {
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery(
                    "select * from collection_profile  where profile_name='" + collectionProfile + "'");
            if (rs.next()) {
                return true;
            }
        } catch (Exception ee) {
            logger.warn("Error while checking collection profile exists", ee);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ex) {
                logger.warn("Exception while closing the resultset in checking collection profile exists", ex);
            }
        }
        return false;
    }

    public static void deletePreferedCredentials() {
        try {
            DBHelper.executeUpdate("delete from device_prefer_credentials");
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        CredentialSetFactory.getInstance().clearPreferedCredentials();
    }

    public static void updateDevicePreferedCredentials(String ipaddress, int credSetId) {
        try {
            DBHelper.executeUpdate("delete from device_prefer_credentials where device_ip='" + ipaddress + "'");
        } catch (Exception ee) {
            ee.printStackTrace();
        }

        try {
            DBHelper.executeUpdate(
                    "insert into device_prefer_credentials VALUES ('" + ipaddress + "'," + credSetId + ")");
        } catch (Exception ee) {
            ee.printStackTrace();
        }

        CredentialSetFactory.getInstance().loadPreferedCredentials();
    }

    public static void insertDeviceSnmpEntMib(int devId, String sysOid, DeviceModule module) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.INSERT_SNMP_ENT_MIB);
            ps.setInt(1, devId);
            ps.setString(2, sysOid);
            ps.setString(3, module.getDescr());
            ps.setInt(4, module.getModuleType().getclassNum());
            ps.setString(5, module.getName());
            ps.setString(6, module.getSerialNumber());
            ps.setString(7, module.getPID());
            ps.setString(8, module.getVendorType());
            ps.setString(9, module.getHwVersion());

            ps.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (Exception e) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception e) {
            }
        }
    }

    public static void insertStandByIps(int id, String[] ips) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = DBHelper.getConnection();
            c.setAutoCommit(false);
            DBHelper.executeUpdateNoCommit(c, "delete from standby_to_id where id=" + id);
            ps = c.prepareStatement(DBHelperConstants.INSERT_INTO_STANDBY_IP);

            for (int i = 0; i < ips.length; i++) {
                ps.setString(1, ips[i]);
                ps.setInt(2, id);
                ps.executeUpdate();
            }
            c.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                c.rollback();
            } catch (Exception e) {
            }
        } finally {
            try {
                c.setAutoCommit(true);
            } catch (Exception ex) {
            }

            try {
                ps.close();
            } catch (Exception ex) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception e) {
            }
        }
    }

    public static void insertDeviceSerialNumber(int id, String cli, String serialNumber) {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from device_serial_numbers where id=" + id + " and serial_number='"
                    + serialNumber + "'");
            if (rs.next()) {
                return;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
            }
        }
        if (cli == null || serialNumber == null) {
            logger.error("CLI and Serial numer should not be null, device id " + id + " CLI " + cli + " serial no :"
                    + serialNumber);
            return;
        }
        Connection c = DBHelper.getConnection();
        PreparedStatement ps = null;
        try {
            ps = c.prepareStatement("insert into device_serial_numbers values (?,?,?,?,?)");
            ps.setInt(1, id);
            ps.setString(2, cli);
            ps.setInt(3, 1);
            ps.setString(4, "Chassis");
            ps.setString(5, serialNumber);

            ps.executeUpdate();
            // DBHelper.executeUpdate("insert into device_serial_numbers names (id, hardware_type, serial_number) values("+id+",1,'"+serialNumber+"')");
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static String getCommandForRunningConfig(String osType) {
        ResultSet rs = null;
        String command = null;

        try {

            rs = DBHelper.executeQuery("Select COMMAND from CONFIG_HEURISTICS where OS='" + osType + "'");
            if (rs.next()) {
                command = rs.getString("COMMAND");
            }
        } catch (Exception ee) {
            ee.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
            }
        }
        return command;
    }

    public static String getCommandForStartupConfig(String osType) {
        ResultSet rs = null;
        String command = null;

        try {

            rs = DBHelper.executeQuery("Select STARTUP_COMMAND from CONFIG_HEURISTICS where OS='" + osType + "'");
            if (rs.next()) {
                command = rs.getString("STARTUP_COMMAND");
            }
        } catch (Exception ee) {
            logger.error("Error while getting Startup command from Config heuristics...", ee);
        } finally {
            DBHelper.close(rs);
        }
        return command;
    }

    public static void insertRunningConfigCommand(int id, String cli) {
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from running_config_command where dev_id='" + id + "'");
            boolean updateNeeded = false;
            if (rs.next()) {
                updateNeeded = true;
            }
            try {
                rs.close();
                rs = null;
            } catch (Exception ex) {
            } finally {
                if (updateNeeded) {
                    if (cli != null) {
                        // before executing next sql statement close the
                        // previous rs, This will make sure that previous
                        // connection is released
                        // before trying for new connection.

                        DBHelper.executeUpdate(
                                "update running_config_command set cli='" + cli + "' where dev_id='" + id + "'");
                    }
                    return;
                }

            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ex) {
            }
        }
        Connection c = DBHelper.getConnection();
        PreparedStatement ps = null;
        try {
            ps = c.prepareStatement("insert into running_config_command values (?,?)");
            ps.setInt(1, id);
            // ps.setString(1, ip);
            ps.setString(2, cli);

            ps.executeUpdate();
            // DBHelper.executeUpdate("insert into device_serial_numbers names (id, hardware_type, serial_number) values("+id+",1,'"+serialNumber+"')");
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static ArrayList<String> getSerialNumbers(int id) {
        ArrayList<String> serNumbers = new ArrayList<String>();
        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery(
                    "select serial_number from device_serial_numbers where id=" + id + " and hardware_type=1");
            while (rs.next()) {
                serNumbers.add(rs.getString("serial_number"));
            }
        } catch (Exception ex) {

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }

        return serNumbers;
    }

    public static List<HeuristicDescriptor> getHeuristics(String attribute) {
        ResultSet rs = null;
        List<HeuristicDescriptor> list = new ArrayList<HeuristicDescriptor>();
        try {
            rs = DBHelper.executeQuery(
                    "select * from device_heuristics where output_attr='" + attribute + "' order by id");

            while ((rs != null) && rs.next()) {
                String in_attr = rs.getString("input_attr");
                String operatorStr = rs.getString("operator");
                String operand = rs.getString("operand");
                String value = rs.getString("value");
                int weight = rs.getInt("weight");

                StringOperatorType operator = null;
                try {
                    operator = StringOperatorType.valueOf(operatorStr);
                } catch (Exception ex) {
                    //
                }
                if (operator == null) {
                    logger.error("Unknown operator: " + operatorStr + " while loading heuristics for " + attribute
                            + " ... ignoring");
                }
                HeuristicDescriptor heu = new HeuristicDescriptor(in_attr, operator, operand, value, weight);
                list.add(heu);
            }
        } catch (Exception ee) {
            logger.warn("Error while getting Heuristics for :", attribute, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }

        return list;
    }

    public static List<HeuristicDescriptor> getConfigHeuristics(String attribute) {
        ResultSet rs = null;
        List<HeuristicDescriptor> list = new ArrayList<HeuristicDescriptor>();
        try {
            rs = DBHelper.executeQuery("select * from config_heuristics order by id");

            while ((rs != null) && rs.next()) {
                String os = rs.getString("OS");
                String command = rs.getString("COMMAND");
                String startupCommand = rs.getString("STARTUP_COMMAND");
                HeuristicDescriptor heu = new HeuristicDescriptor(os, command);
                heu.setStartupCommand(startupCommand);
                list.add(heu);
            }
        } catch (Exception ee) {
            logger.warn("Error while getting Heuristics for :", attribute, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }

        return list;
    }

    public static void updateHeuristics(String attribute, List<HeuristicDescriptor> list) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            DBHelper.executeUpdate("delete from device_heuristics where output_attr='" + attribute + "'");
        } catch (Exception ee) {
            logger.error("Exception while trying to delete heuristics for attribute: " + attribute, ee);
        }
        if (list == null || list.isEmpty()) {
            return;
        }
        try {
            c = DBHelper.getConnection();
            int i = 0;
            ps = c.prepareStatement(
                    "INSERT INTO device_heuristics (id, output_attr, input_attr, operator, operand, value, weight) VALUES (?,?,?,?,?,?,?)");
            for (HeuristicDescriptor heu : list) {
                try {
                    ps.setInt(1, i);
                    ps.setString(2, attribute);
                    ps.setString(3, heu.getDeviceAttrName());
                    ps.setString(4, heu.getOperator().name());
                    ps.setString(5, heu.getOperand());
                    ps.setString(6, heu.getResultVal());
                    if (heu.getWeight() == null) {
                        ps.setInt(7, HeuristicDescriptor.MAX_WEIGHT);
                    } else {
                        ps.setInt(7, heu.getWeight());
                    }
                    ps.executeUpdate();
                } catch (Exception ex) {
                    logger.error("Exception while adding heuristic for attribute: " + attribute + " operand: "
                            + heu.getOperand(), ex);
                }
                i++;
            }
        } catch (Exception e) {
            logger.warn("Error while inserting heuristics for attribute: " + attribute, e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    public static void updateConfigHeuristics(String attribute, List<HeuristicDescriptor> list) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            DBHelper.executeUpdate("delete from config_heuristics");
        } catch (Exception ee) {
            logger.error("Exception while trying to delete heuristics for attribute: " + attribute, ee);
        }
        if (list == null || list.isEmpty()) {
            return;
        }
        try {
            c = DBHelper.getConnection();
            int i = 0;
            ps = c.prepareStatement(
                    "INSERT INTO config_heuristics (id, os, command, startup_command) VALUES (?,?,?,?)");
            for (HeuristicDescriptor heu : list) {
                try {
                    ps.setInt(1, i);
                    ps.setString(2, heu.getOS());
                    ps.setString(3, heu.getCommand());
                    ps.setString(4, heu.getStartupCommand());
                    ps.executeUpdate();
                } catch (Exception ex) {
                    logger.error("Exception while adding heuristic for attribute: " + attribute + " operand: "
                            + heu.getOperand(), ex);
                }
                i++;
            }
        } catch (Exception e) {
            logger.warn("Error while inserting heuristics for attribute: " + attribute, e);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }
        }
    }

    /**
     * Save OS name and corresponding running config and/or startup config command to DB.
     *
     * @param OSDescription
     * @param runConfigCmd
     */
    public static void saveConfigHeuristics(String oSName, String runConfigCmd, String startConfigCmd)
            throws SQLException {
        Connection conn = null;
        String selectQuery = "SELECT * from config_heuristics where os = '" + oSName + "'";
        String insQuery = "INSERT INTO config_heuristics (select coalesce(max(id), 0) + 1, ?, ?, ? from config_heuristics)";
        if ((runConfigCmd == null) && (startConfigCmd == null)) {
            return;
        }
        // String updateQuery =
        // "UPDATE config_heuristics SET command = ? and startup_command = ? where os = ?";
        try {
            conn = DBHelper.getConnection();
            try (ResultSet rs = DBHelper.executeQuery(selectQuery)) {
                if (rs.next()) {
                    // If record already exists, update it
                    if (startConfigCmd == null) {
                        // If startConfigCmd is sent as null, do not set it (we
                        // don't want to set it to null if value
                        // already exists in DB)
                        String updateQuery = "UPDATE config_heuristics SET command = ? where os = ?";
                        try (PreparedStatement ps = conn.prepareStatement(updateQuery)) {
                            ps.setString(1, runConfigCmd);
                            ps.setString(2, oSName);
                            ps.executeUpdate();
                        }
                    }
                    // If runConfigCmd is sent as null, do not set it (we don't
                    // want to set it to null if value already
                    // exists
                    // in DB)
                    else if (runConfigCmd == null) {
                        String updateQuery = "UPDATE config_heuristics SET startup_command = ? where os = ?";
                        try (PreparedStatement ps = conn.prepareStatement(updateQuery)) {
                            ps.setString(1, startConfigCmd);
                            ps.setString(2, oSName);
                            ps.executeUpdate();
                        }
                    } else {
                        String updateQuery = "UPDATE config_heuristics SET command = ? and startup_command = ? where os = ?";
                        try (PreparedStatement ps = conn.prepareStatement(updateQuery)) {
                            ps.setString(1, runConfigCmd);
                            ps.setString(2, startConfigCmd);
                            ps.setString(3, oSName);
                            ps.executeUpdate();
                        }
                    }
                } else {
                    // Otherwise insert it
                    try (PreparedStatement ps = conn.prepareStatement(insQuery)) {
                        ps.setString(1, oSName);
                        ps.setString(2, runConfigCmd);
                        ps.setString(3, startConfigCmd);
                        ps.executeUpdate();
                    }
                }
            }

        } catch (SQLException sqlex) {
            throw sqlex;
        } catch (Exception ex) {
            logger.error("Error while saving os and run config cmd to database - " + oSName + " " + runConfigCmd,
                    ex);
        } finally {
            DBHelper.closeAll(conn, null, null);
        }

    }

    public static void saveCredTransportDetails(int nodeId, String transportType, String credName) {
        try {
            DBHelper.executeUpdate("delete from device_discovered_creds where id=" + nodeId);
            if (transportType != null && !"".equals(transportType.trim())) {
                DBHelper.executeUpdate("insert into device_discovered_creds values (" + nodeId + ",'"
                        + transportType + "','" + credName + "')");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static void cleanModuleSerialNumbers(int nodeId) {
        try {
            DBHelper.executeUpdate("update device_module_info set SERIALNUM='' where device_id=" + nodeId
                    + " and SERIALNUM='NAME:'");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static int getJobId(String jobName, String jobGrp) {
        int jobId = -1;
        PreparedStatement ps = null;
        Connection c = null;
        ResultSet rs = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("select job_id from pari_job_addl_details where job_name=? and job_group=?");
            ps.setString(1, jobName);
            ps.setString(2, jobGrp);
            rs = ps.executeQuery();
            if (rs.next()) {
                jobId = rs.getInt("job_id");
            }
        } catch (Exception ee) {
            logger.warn("Error while trying to get the job id", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }

        }
        return jobId;
    }

    public static ArrayList<Integer> getAllJobIds(String jobGrp) {
        int jobId = -1;
        PreparedStatement ps = null;
        Connection c = null;
        ResultSet rs = null;
        ArrayList<Integer> jobIdList = new ArrayList<Integer>();
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(
                    "select job_id from pari_job_addl_details where job_group=? ORDER BY job_id DESC");
            ps.setString(1, jobGrp);
            rs = ps.executeQuery();
            while (rs.next()) {
                jobId = rs.getInt("job_id");
                jobIdList.add(jobId);
            }
        } catch (Exception ee) {
            logger.warn("Error while trying to get all job ids", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }

        }
        return jobIdList;
    }

    public static String getLastCreatedJobNameByType(String jobType, ServerIfProvider serverIfProvider) {
        PreparedStatement ps = null;
        Connection c = null;
        ResultSet rs = null;
        String jobName = null;
        try {
            c = DBHelper.getConnection();
            String query = null;
            String user = serverIfProvider.getUserDetails().getLogin();
            if (isPostgres()) {
                query = "select job_name from pari_job_addl_details where job_group = '" + jobType
                        + "' and creator_login='" + user + "' order by created_time desc limit 1;";
            } else {
                query = "select job_name from pari_job_addl_details where job_group = '" + jobType
                        + "' and creator_login='" + user + "' and ROWNUM <= 1 order by created_time desc;";
            }

            ps = c.prepareStatement(query);
            rs = ps.executeQuery();
            while (rs.next()) {
                jobName = rs.getString("job_name");
            }

        } catch (Exception ee) {
            logger.warn("Error while getting job name", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            try {
                ps.close();
            } catch (Exception ee) {
            }
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
            }

        }
        return jobName;
    }

    public static void saveSerialNumber(int nodeId, String saveSer) {
        try {
            DBHelper.executeUpdate("update ios_version set serial_number='" + saveSer + "' where id=" + nodeId);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static void saveProductModel(int nodeId, String pid) {
        try {
            DBHelper.executeUpdate("update ios_version set productid='" + pid + "' where id=" + nodeId);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static void insertJobCreatorDetails(String jobName, String jobGroup, int creatorId, long createdTime,
            String devices) throws Exception {
        Connection c = null;
        PreparedStatement ps = null;

        try {

            String loginName = "";
            UserDetails ud = UsersFactory.getUser(creatorId);
            if (ud != null) {
                loginName = ud.getLogin();
            }

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_CREATOR_DETAILS);

            ps.setString(1, jobName);
            ps.setString(2, jobGroup);
            ps.setInt(3, creatorId);
            ps.setString(4, loginName);
            ps.setTimestamp(5, (new Timestamp(createdTime)));
            ps.setString(6, devices);
            ps.executeUpdate();
            insertJobCustomerDetails(jobName, jobGroup, -1);

        } catch (Exception ex) {
            logger.warn("Error while creating a job entry in db.", ex);
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
                logger.debug("Error while closing prepared statement.", ex);
            }
            DBHelper.releaseConnection(c);

        }

    }

    public static void insertJobCreatorDetails(String jobName, String jobGroup, int creatorId, long createdTime,
            String devices, int cusID) throws Exception {
        Connection c = null;
        PreparedStatement ps = null;

        try {

            String loginName = "";
            UserDetails ud = UsersFactory.getUser(creatorId);
            if (ud != null) {
                loginName = ud.getLogin();
            }

            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_CREATOR_DETAILS);

            ps.setString(1, jobName);
            ps.setString(2, jobGroup);
            ps.setInt(3, creatorId);
            ps.setString(4, loginName);
            ps.setTimestamp(5, (new Timestamp(createdTime)));
            ps.setString(6, devices);
            ps.executeUpdate();
            insertJobCustomerDetails(jobName, jobGroup, cusID);

        } catch (Exception ex) {
            logger.warn("Error while creating a job entry in db.", ex);
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
                logger.debug("Error while closing prepared statement.", ex);
            }
            DBHelper.releaseConnection(c);

        }

    }

    private static void insertJobCustomerDetails(String jobName, String jobGroup, int customerID) {
        Connection cc = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int jobId = -1;
        int cusid = customerID;
        try {

            cc = DBHelper.getConnection();
            ps = cc.prepareStatement("select job_id from pari_job_addl_details where job_name=? and job_group=?");
            ps.setString(1, jobName);
            ps.setString(2, jobGroup);
            rs = ps.executeQuery();
            if (rs.next()) {
                jobId = rs.getInt("job_id");
            }
            rs.close();
            List<Integer> deviceIdsList = NmIfImpl.getInstance().getDeviceList(jobId);

            StringBuilder customerQuery = new StringBuilder();
            String device = null;
            if (!deviceIdsList.isEmpty()) {
                if (deviceIdsList.size() < 500) {
                    device = deviceIdsList.toString().replace("[", "").replace("]", "").trim();
                    customerQuery.append("select  distinct  customer_id from  customer_device where device_id in ("
                            + (device) + ")");
                    ResultSet rsVal = DBHelper.executeQuery(customerQuery.toString());
                    while (rsVal.next()) {
                        cusid = rsVal.getInt("customer_id");

                        ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS);

                        ps.setInt(1, jobId);
                        ps.setInt(2, cusid);

                        ps.executeUpdate();

                    }
                    rsVal.close();

                }

                else {

                    Set<Integer> customerIdset = getCustomerIdList(deviceIdsList);
                    for (Integer customer : customerIdset) {
                        ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS);

                        ps.setInt(1, jobId);
                        ps.setInt(2, customer);

                        ps.executeUpdate();
                    }
                }

            } else if (cusid != -1) {
                ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS);

                ps.setInt(1, jobId);
                ps.setInt(2, cusid);

                ps.executeUpdate();
            } else {
                List<String> customerNames = NmIfImpl.getUserDomain().getApplicableCustomerIds();
                Boolean hasAccess = NmIfImpl.getUserDomain().isHasUnlimitedAccess();

                if (hasAccess) {

                    ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS);

                    ps.setInt(1, jobId);
                    ps.setInt(2, -1);

                    ps.executeUpdate();
                } else {
                    for (String customer : customerNames) {
                        ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS);

                        ps.setInt(1, jobId);
                        ps.setInt(2, Integer.parseInt(customer));

                        ps.executeUpdate();
                    }

                }
            }

        } catch (Exception ex) {
            logger.warn("Error while inserting job entry in job customer details db.", ex);
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
                logger.debug("Error while closing job customer details  prepared statement.", ex);
            }
            DBHelper.releaseConnection(cc);
        }
    }

    public static Set<Integer> getCustomerIdList(List deviceIdsList) {
        Connection cn = null;
        Set<Integer> deviceset = new HashSet<Integer>();
        String device = null;
        int cusid = -1;

        try {

            cn = DBHelper.getConnection();

            int val = (deviceIdsList.size() / 500) + 1;

            int start = 0;
            int end = 500;
            for (int s = 1; s <= val; s++) {

                List<Integer> deviceIds = new ArrayList<>();
                if (s == val) {
                    deviceIds = deviceIdsList.subList(start, deviceIdsList.size());
                    if (!deviceIds.isEmpty()) {
                        device = deviceIds.toString().replace("[", "").replace("]", "").trim();
                        ResultSet rsVal = DBHelper.executeQuery(
                                "select  distinct  customer_id from  customer_device where device_id in ("
                                        + (device) + ")");

                        while (rsVal.next()) {
                            cusid = rsVal.getInt("customer_id");
                            deviceset.add(cusid);
                        }
                        rsVal.close();
                    }
                } else {

                    deviceIds = deviceIdsList.subList(start, end);
                    if (!deviceIds.isEmpty()) {
                        device = deviceIds.toString().replace("[", "").replace("]", "").trim();
                        ResultSet rsVal = DBHelper.executeQuery(
                                "select  distinct  customer_id from  customer_device where device_id in ("
                                        + (device) + ")");

                        while (rsVal.next()) {
                            cusid = rsVal.getInt("customer_id");
                            deviceset.add(cusid);
                        }
                        rsVal.close();

                    }
                    start = s * 500;
                    end = start + 500;
                }

            }

        } catch (Exception ex) {
            logger.warn("Error while creating a job entry in db.", ex);
        } finally {
            try {
                DBHelper.releaseConnection(cn);
            } catch (Exception ex) {
                logger.debug("Error while release connection.", ex);
            }

        }

        return deviceset;
    }

    public static void updateJobModifierDetails(int jobId, int modifierId, long modifiedTime) throws Exception {
        Connection c = null;
        PreparedStatement ps = null;

        String loginName = "";
        UserDetails ud = UsersFactory.getUser(modifierId);
        if (ud != null) {
            loginName = ud.getLogin();
        }

        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_MODIFIER_DETAILS);

            ps.setInt(1, modifierId);
            ps.setString(2, loginName);
            ps.setTimestamp(3, (new Timestamp(modifiedTime)));
            ps.setInt(4, jobId);

            ps.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static void updateJobDevicesDetails(int jobId, int noOfDevices, int noOfChanges) throws Exception {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_DEVICES_DETAILS);

            ps.setInt(1, noOfDevices);
            ps.setInt(2, noOfChanges);
            ps.setInt(3, jobId);

            ps.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static void deleteJob(int jobId) throws Exception {
        DBHelper.executeUpdate("delete from pari_job_addl_details where job_id=" + jobId);
        DBHelper.executeUpdate("delete from job_customer_details where job_id=" + jobId);
    }

    public static ArrayList<Integer> getCustomerIDs(int jobId) {
        ArrayList<Integer> customerIds = new ArrayList<Integer>();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from job_customer_details where  job_id=" + jobId);
            while ((rs != null) && rs.next()) {
                int custId = rs.getInt("customer_id");
                customerIds.add(custId);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return customerIds;
    }

    public static int getJobCreatorId(int jobId) {
        int creatorId = -1;
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select creator_id from pari_job_addl_details where  job_id=" + jobId);
            if (rs.next()) {
                creatorId = rs.getInt("creator_id");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }

        return creatorId;
    }

    public static int getJobModifierId(int jobId) {
        int modifierId = -1;
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select modifier_id from pari_job_addl_details where  job_id=" + jobId);
            if (rs.next()) {
                modifierId = rs.getInt("modifier_id");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }

        return modifierId;
    }

    // Added as part of CSCtr59009 bug fix to get discovered time of devices
    public static Map<String, Long> getDiscoveryTimeForAllDevices(int customerId, int instanceId) {

        ResultSet rs = null;
        Map<String, Long> map = new HashMap<String, Long>();
        try {
            rs = DBHelper.executeQuery("select ipaddress, discovery_time from discovered_devices where customer_id="
                    + customerId + " and instance_id='" + instanceId + "'");

            while ((rs != null) && rs.next()) {
                String ipaddress = rs.getString("ipaddress");
                Timestamp ts = rs.getTimestamp("discovery_time");

                map.put(ipaddress, ts.getTime());
            }
        } catch (Exception ee) {
            logger.warn("Error while getting getDiscoveryTimeForAllDevices :", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }

        return map;
    }

    // Added as part of bug fix - CSCtr61612
    public static void updateDiscoveredDevice(String deviceIp, int customerId, int instanceId, String isManaged) {
        try {

            DBHelper.executeUpdate(
                    "update discovered_devices set is_managed = '" + isManaged + "' where ipaddress='" + deviceIp
                            + "' and customer_id=" + customerId + " and instance_id='" + instanceId + "'");
        } catch (Exception e) {
            logger.error("Error while executing updateDiscoveredDevice..", e);
        }

    }

    public static void insertExternalDeviceDetails(int nccmId, int customerId, String ipAddress, Long externalId,
            String serviceName, int wingId, boolean replace) throws Exception {
        Connection c = DBHelper.getConnection();
        PreparedStatement ps = null;

        try {
            ConcurrentHashMap<Integer, String> nodeMap = getExternalNodeInfo(serviceName, externalId);
            if (nodeMap != null && nodeMap.size() > 0) {
                if (replace) {
                    try {
                        String query = "delete from external_device where EXTERNAL_ID=" + externalId
                                + " and SERVICE_NAME='" + serviceName + "'";
                        DBHelper.executeUpdate(query);
                    } catch (Exception ex) {
                        logger.error("Exception while deleting external_device: EXTERNAL_ID=" + externalId
                                + " and SERVICE_NAME='" + serviceName + "'", ex);
                    }
                } else {
                    throw new Exception("An Entry already Exist with the same EXTERNALID=" + externalId
                            + " and SERVICENAME='" + serviceName + "'");
                }
            }
        } catch (SQLException e) {
            logger.error("Exception while retrieving external_device: EXTERNAL_ID=" + externalId
                    + " and SERVICE_NAME='" + serviceName + "'", e);
        }

        try {
            ps = c.prepareStatement(
                    "insert into external_device(NCCM_ID, CUSTOMER_ID, IPADDRESS, EXTERNAL_ID, SERVICE_NAME, WING_INSTANCE_ID) values (?,?,?,?,?,?)");
            ps.setInt(1, nccmId);
            ps.setInt(2, customerId);
            ps.setString(3, ipAddress);
            ps.setLong(4, externalId);
            ps.setString(5, serviceName);
            ps.setInt(6, wingId);

            ps.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
            }
            DBHelper.releaseConnection(c);
        }
    }

    public static void deleteExternalDevice(String serviceName) throws SQLException {
        DBHelper.executeUpdate("delete from external_device where SERVICE_NAME='" + serviceName + "'");
    }

    public static void deleteExternalDevice(String serviceName, Long externalId) throws SQLException {
        DBHelper.executeUpdate("delete from external_device where EXTERNAL_ID=" + externalId + "AND SERVICE_NAME='"
                + serviceName + "'");
    }

    public static void deleteExternalDevice(Integer customerId, String ipAddress) throws SQLException {
        String delStmt = "delete from external_device where CUSTOMER_ID=" + customerId + " AND IPADDRESS='"
                + ipAddress + "'";
        // logger.info("Delete Statement---\n"+delStmt);
        DBHelper.executeUpdate(delStmt);
    }

    public static HashMap<String, Long> getExtDeviceInfo(int customerId, String ipAddress) throws SQLException {
        String query = "select * from external_device where CUSTOMER_ID=" + customerId + " AND IPADDRESS= '"
                + ipAddress + "'";
        Connection c = DBHelper.getConnection();
        HashMap<String, Long> map = new HashMap<String, Long>();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(c, query);

            while (rs.next()) {
                String serviceName = rs.getString("SERVICE_NAME");
                Long externalId = rs.getLong("EXTERNAL_ID");
                if (serviceName != null && !serviceName.isEmpty() && externalId > 0) {
                    map.put(serviceName, externalId);
                }
            }
        } catch (Exception e) {
            logger.error("Error in getExtDeviceInfo :  ", e);
        } finally {
            // CSCtx91377 - Releasing the DB connection.
            if (rs != null) {
                rs.close();
            }
            DBHelper.releaseConnection(c);
        }

        return map;
    }

    public static ConcurrentHashMap<Integer, String> getExternalNodeInfo(String serviceName, Long externalId)
            throws PariException {
        ConcurrentHashMap<Integer, String> nodeMap = new ConcurrentHashMap<Integer, String>(1);
        if (externalId == null || externalId.longValue() <= 0) {
            throw new PariException("_NCCM_SRV_DEVMGMT_EXTERNALID_MISSING", new Object[] {});
        }
        String query = "select * from external_device where EXTERNAL_ID=" + externalId + " AND SERVICE_NAME= '"
                + serviceName + "'";
        Connection c = DBHelper.getConnection();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(c, query);
            if (rs.next()) {
                String ipAddress = rs.getString("IPADDRESS");
                Integer nccmId = rs.getInt("NCCM_ID");
                nodeMap.put(nccmId, ipAddress);
                return nodeMap;
            }
        } catch (Exception e) {
            throw new PariException("_NCCM_SRV_DEVMGMT_GET_EXTERNAL_NODE_INFO_FAILED", new Object[] {});
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (c != null) {
                try {
                    DBHelper.releaseConnection(c);
                } catch (Exception ee) {
                    logger.error("Failed to release connection", ee);
                }
            }
        }
        return null;
    }

    public static void updateExtDevice(String ipAddress, int customerId, int id) {
        ResultSet rs = null;
        String selectQuery = "select * from external_device where IPADDRESS ='" + ipAddress + "' AND CUSTOMER_ID="
                + customerId;
        try {
            rs = DBHelper.executeQuery(selectQuery);
            boolean needUpdate = false;
            try {
                if (rs.next()) {
                    needUpdate = true;
                }
            } catch (SQLException e) {
                logger.warn(
                        "Exception occured while closing ResultSet in updateNccmIdFromDeviceIPandCustomerId" + e);
            } finally {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (needUpdate) {
                    DBHelper.executeUpdate("update external_device set NCCM_ID=" + id + "where IPADDRESS ='"
                            + ipAddress + "' AND CUSTOMER_ID=" + customerId);
                }
            }
        } catch (SQLException e) {
            logger.warn("Exception occured in updateNccmIdFromDeviceIPandCustomerId" + e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                logger.warn(
                        "Exception occured while closing ResultSet in updateNccmIdFromDeviceIPandCustomerId" + e);
            }
        }
    }

    public static Set<Long> getExternalIds(String serviceName) {
        String query = null;
        Set<Long> externalIds = new HashSet<Long>();
        Connection connection = null;
        ResultSet rs = null;
        query = "select * from external_device where SERVICE_NAME='" + serviceName + "'";
        connection = DBHelper.getConnection();
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query);
            if (rs != null && rs.next()) {
                externalIds.add(rs.getLong("EXTERNAL_ID"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBHelper.releaseConnection(connection);
        }
        return externalIds;
    }

    public static Set<String> getNccmIds(String serviceName) {
        Set<String> externalIds = new HashSet<String>();
        String query = "select * from external_device where SERVICE_NAME='" + serviceName + "'";
        getNccmIds(query, externalIds);
        return externalIds;
    }

    public static Set<String> getNccmIds(String serviceName, int customerId) {
        Set<String> externalIds = new HashSet<String>();
        String query = "select * from external_device where SERVICE_NAME='" + serviceName + "' AND CUSTOMER_ID="
                + customerId;
        getNccmIds(query, externalIds);
        return externalIds;
    }

    /**
     * @param query
     * @param externalIds
     */
    private static void getNccmIds(String query, Set<String> externalIds) {
        Connection connection = DBHelper.getConnection();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query);
            while (rs != null && rs.next()) {
                externalIds.add(String.valueOf(rs.getInt("NCCM_ID")));
            }
        } catch (SQLException e) {
            logger.warn("Exception while executing query:" + e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                logger.warn("Unable to close the resultset:" + e);
            }
            DBHelper.releaseConnection(connection);
        }
    }

    public static List<ModuleInfo> getChildModulesOrContexts(int parentNodeId, ModuleType moduleType) {
        ResultSet rs = null;
        List<ModuleInfo> devices = null;
        try {
            rs = DBHelper.executeQuery("select * from nodes_modules where id=" + parentNodeId + " AND module_type="
                    + moduleType.moduleType);
            devices = new ArrayList<ModuleInfo>();
            while (rs != null && rs.next()) {
                int parentId = rs.getInt("id");
                int modId = rs.getInt("mod_id");
                String slotNumber = rs.getString("slot_number");
                int modType = rs.getInt("module_type");
                ModuleInfo mInfo = new ModuleInfo();
                mInfo.setParentId(parentId);
                mInfo.setModId(modId);
                mInfo.setSlotNumber(slotNumber);
                mInfo.setType(ModuleType.getFromInt(modType));
                devices.add(mInfo);
            }
        } catch (Exception ee) {
            logger.warn("Error while getting module/conetxt info", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return devices;
    }

    public static List<ModuleInfo> getChildModulesOrContextForParent(int parentNodeId) {
        ResultSet rs = null;
        List<ModuleInfo> devices = null;
        try {
            rs = DBHelper.executeQuery("select * from nodes_modules where id=" + parentNodeId);
            devices = new ArrayList<ModuleInfo>();
            while (rs != null && rs.next()) {
                int parentId = rs.getInt("id");
                int modId = rs.getInt("mod_id");
                String slotNumber = rs.getString("slot_number");
                int modType = rs.getInt("module_type");
                ModuleInfo mInfo = new ModuleInfo();
                mInfo.setParentId(parentId);
                mInfo.setModId(modId);
                mInfo.setSlotNumber(slotNumber);
                mInfo.setType(ModuleType.getFromInt(modType));
                devices.add(mInfo);
            }
        } catch (Exception ee) {
            logger.warn("Error while getting module/conetxt info", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.warn("Error while closing result set", ee);
            }
        }
        return devices;
    }

    public static DeviceModule getDeviceModuleData(int devId, String slot) {
        String query = null;
        DeviceModule devModule = null;
        Connection connection = null;
        ResultSet rs = null;
        query = "select * from DEVICE_MODULE_INFO_NEW where DEVICE_ID='" + devId + "'"
                + "AND moduletype='module' AND slot='" + slot + "'";
        connection = DBHelper.getConnection();
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query);
            if (rs != null && rs.next()) {
                devModule = new DeviceModule();
                devModule.setName(rs.getString("MODULE_NAME"));
                devModule.setDescr(rs.getString("MODULE_DESCR"));
                devModule.setPID(rs.getString("PID"));
                devModule.setSerialNumber(rs.getString("SERIALNUM"));
                devModule.setModuleType(rs.getString("moduleType"));
                devModule.setParentIndex(rs.getInt("parentIndex"));
                devModule.setMyIndex(rs.getInt("myIndex"));
                devModule.setRelPos(rs.getInt("rel_pos"));
                devModule.setSlot(rs.getString("slot"));
                devModule.setVendorType(rs.getString("vendor_type"));
                devModule.setHwVersion(rs.getString("hwversion"));
                devModule.setFruable(rs.getInt("fru") == 1 ? true : false);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBHelper.releaseConnection(connection);
        }
        return devModule;
    }

    public static boolean isEntityDataCollectedForDevice(int devId) {
        Connection connection = null;
        ResultSet rs = null;
        int count = -1;
        String query = "select count(*) as count from DEVICE_MODULE_INFO_NEW where DEVICE_ID='" + devId + "'";
        connection = DBHelper.getConnection();
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query);
            if (rs != null && rs.next()) {
                count = rs.getInt("count");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBHelper.releaseConnection(connection);
        }

        return (count >= 1);
    }

    public static List<ExternalDeviceRegDetails> getExternalDeviceInfoDetails(int customerId, String ipAddress,
            int nccmId, Long externalId, String serviceName, int wingId, ServerIfProvider serverIfProvider)
            throws Exception {
        ExternalDeviceRegDetails extDevRegDet = new ExternalDeviceRegDetails();
        List<ExternalDeviceRegDetails> extDevicesList = new ArrayList<ExternalDeviceRegDetails>();

        boolean whereExists = false;
        String and = " ";
        StringBuffer query = new StringBuffer();
        query.append("SELECT * FROM external_device ");

        if (nccmId > 0) {
            if (!whereExists) {
                query.append(" WHERE ");
                whereExists = true;
            }
            query.append(and + " NCCM_ID=" + nccmId);
            if (whereExists) {
                and = " AND ";
            }
        }

        if (serviceName != null) {
            if (!whereExists) {
                query.append(" WHERE ");
                whereExists = true;

            }
            query.append(and + " SERVICE_NAME = '" + serviceName + "'");
            if (whereExists) {
                and = " AND ";
            }
        }
        /*
         * if (nccmId > 0) { query.append("AND  NCCM_ID = " + nccmId); }
         */
        if (ipAddress != null) {
            if (!whereExists) {
                query.append(" WHERE ");
                whereExists = true;
            }
            query.append(and + " IPADDRESS = '" + ipAddress + "'");
            if (whereExists) {
                and = " AND ";
            }
        }
        if (externalId != null && externalId > 0) {
            if (!whereExists) {
                query.append(" WHERE ");
                whereExists = true;
            }
            query.append(and + " EXTERNAL_ID = " + externalId);
            if (whereExists) {
                and = " AND ";
            }
        }
        if (customerId > 0) {
            if (!whereExists) {
                query.append(" WHERE ");
                whereExists = true;
            }
            query.append(and + " CUSTOMER_ID = " + customerId);
            if (whereExists) {
                and = " AND ";
            }
        }

        if (wingId > 0) {
            if (!whereExists) {
                query.append(" WHERE ");

            }
            query.append(and + "  WING_INSTANCE_ID = " + wingId);

        }
        // logger.info("GetExternalDeviceInfo Query:" + query.toString());

        Connection connection = DBHelper.getConnection();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query.toString());
            while (rs != null && rs.next()) {
                extDevRegDet = new ExternalDeviceRegDetails();
                extDevRegDet.setServiceName(rs.getString("SERVICE_NAME"));
                extDevRegDet.setExternalId(rs.getLong("EXTERNAL_ID"));
                extDevRegDet.setNccmId(rs.getInt("NCCM_ID"));
                extDevRegDet.setIPAddress(rs.getString("IPADDRESS"));
                String customerName = serverIfProvider.getCustomerIf()
                        .getCustomerNameById(rs.getInt("CUSTOMER_ID"));
                if (customerName != null) {
                    extDevRegDet.setCustomerName(customerName);
                }
                String wingInstanceName = serverIfProvider.getCustomerIf()
                        .getWingInstanceName(rs.getInt("WING_INSTANCE_ID"));
                extDevRegDet.setWingInstanceName(wingInstanceName);
                extDevicesList.add(extDevRegDet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBHelper.releaseConnection(connection);
        }

        return extDevicesList;
    }

    public static List<ExternalDeviceRegDetails> getAllExternalDeviceInfoDetails(ServerIfProvider serverIfProvider)
            throws Exception {
        ExternalDeviceRegDetails extDevRegDet = new ExternalDeviceRegDetails();
        List<ExternalDeviceRegDetails> extDevicesList = new ArrayList<ExternalDeviceRegDetails>();

        StringBuffer query = new StringBuffer();
        query.append("SELECT * FROM external_device");

        // logger.info("GetAllExternalDeviceInfo Query:" + query.toString());

        Connection connection = DBHelper.getConnection();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query.toString());
            while (rs != null && rs.next()) {
                extDevRegDet = new ExternalDeviceRegDetails();
                extDevRegDet.setServiceName(rs.getString("SERVICE_NAME"));
                extDevRegDet.setExternalId(rs.getLong("EXTERNAL_ID"));
                extDevRegDet.setNccmId(rs.getInt("NCCM_ID"));
                extDevRegDet.setIPAddress(rs.getString("IPADDRESS"));
                String customerName = serverIfProvider.getCustomerIf()
                        .getCustomerNameById(rs.getInt("CUSTOMER_ID"));
                if (customerName != null) {
                    extDevRegDet.setCustomerName(customerName);
                }
                String wingInstanceName = serverIfProvider.getCustomerIf()
                        .getWingInstanceName(rs.getInt("WING_INSTANCE_ID"));
                extDevRegDet.setWingInstanceName(wingInstanceName);
                extDevicesList.add(extDevRegDet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBHelper.releaseConnection(connection);
        }
        return extDevicesList;
    }

    public static void deleteDeviceSerialNumber(int nodeId) {
        try {
            DBHelper.executeUpdate("delete from device_serial_numbers where id=" + nodeId);
        } catch (Exception e) {
            logger.error(
                    "Exception while deleting Serial numbers for device :  " + nodeId + " : " + e.getMessage());
        }
    }

    public static void loadInterfacesFromDB(NetworkNode node, NetworkNodeExt networkNodeExt) {
        int nodeId = node.getNodeId();
        String query = "select * from ios_if where id=" + nodeId + " order by if_name";
        loadInterfacesFromDBInternal(query, nodeId, networkNodeExt, node);
        query = "select * from pix_if where id=" + nodeId + " order by if_name";
        loadInterfacesFromDBInternal(query, nodeId, networkNodeExt, node);
    }

    public static void loadInterfacesFromDBInternal(String query, int nodeId, NetworkNodeExt networkNodeExt,
            NetworkNode node) {

        Connection connection = DBHelper.getConnection();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query.toString());
            while (rs != null && rs.next()) {
                CiscoNetworkNodeInterface nIf = new CiscoNetworkNodeInterface();
                nIf.updateObject(rs);
                networkNodeExt.addInterface(nIf, node);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBHelper.releaseConnection(connection);
            }
        }
    }

    public static void loadCdpNeighbors(NetworkNode node, NetworkNodeExt networkNodeExt) {
        String query = "select * from  device_cdp_table where device_id=" + node.getNodeId();
        Connection connection = DBHelper.getConnection();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query.toString());
            while (rs != null && rs.next()) {
                int linkId = rs.getInt("link_id");
                int fromDeviceId = rs.getInt("device_id");
                String toIpAddress = rs.getString("toIPAddress");
                String fromPort = rs.getString("fromPort");
                String toPort = rs.getString("toPort");
                networkNodeExt.addCDPNeighbor(linkId, fromDeviceId, node, toIpAddress, fromPort, toPort);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBHelper.releaseConnection(connection);
            }
        }
    }

    public static void loadCapabilitiesFromDB(NetworkNode node, NetworkNodeExt networkNodeExt) {
        String query = "select * from node_not_supp_caps where device_id=" + node.getNodeId();
        Connection connection = DBHelper.getConnection();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query.toString());
            while (rs != null && rs.next()) {
                String capsList = rs.getString("non_supp_cap_list");
                networkNodeExt.popuateCapabilitiesFromDBString(capsList);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBHelper.releaseConnection(connection);
            }
        }

    }

    public static Hashtable<String, String> loadStandByIpsFromDB(NetworkNode node) {
        Hashtable<String, String> standByIps = new Hashtable<String, String>(1);
        String query = "select * from standby_to_id where id=" + node.getNodeId();
        Connection connection = DBHelper.getConnection();
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQueryNoCommit(connection, query.toString());
            while (rs != null && rs.next()) {
                String stdIP = rs.getString("standby_ip");
                standByIps.put(stdIP, "");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBHelper.releaseConnection(connection);
            }
        }
        return standByIps;
    }

    public static String[] getAvailableShowCommands() {
        return getAvailableShowCommands(-1);
    }

    public static List<SyslogPreferences> loadSyslogPatternFromDB() {

        List<SyslogPreferences> syslogPrefLists = new ArrayList<SyslogPreferences>();
        SyslogPreferences syslogPrefObj = null;
        ResultSet resultSet = null;

        try {
            try {
                resultSet = DBHelper.executeQuery(SELECT_SYSLOG_ACTIONS);

                while (resultSet.next()) {
                    syslogPrefObj = new SyslogPreferences();
                    syslogPrefObj.setID(resultSet.getInt("ID"));
                    syslogPrefObj.setTitle(resultSet.getString("TITLE"));
                    syslogPrefObj.setOperator(resultSet.getString("OPERATOR"));
                    syslogPrefObj.setOperand(resultSet.getString("OPERAND"));

                    int dataSetId = resultSet.getInt("DATASETID");
                    if (dataSetId != -1) {
                        Map<String, String> dataSetInfoMap = getDataSetNameAndStatus(dataSetId);
                        syslogPrefObj.setDataSet(dataSetInfoMap.get(DATASETNAME));
                        syslogPrefObj.setStatus(Boolean.parseBoolean(dataSetInfoMap.get(STATUS)));
                        getSyslogHeuristicsCommands(dataSetId, syslogPrefObj.getOsCommandMap());
                    }

                    syslogPrefLists.add(syslogPrefObj);

                }

            } catch (Exception e) {
                logger.debug("Error while executing the query to retrieve syslog patterns", e);
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                        resultSet = null;
                    } catch (Exception ex) {
                        logger.debug(RESULTSET_CLOSE_ERROR, ex);
                    }
                }

            }
        } catch (Exception e) {
            logger.debug(CONNECTION_ERROR, e);
        }
        return syslogPrefLists;
    }

    public static List<HeuristicDescriptor> getSyslogHeuristics(String attribute) {
        List<HeuristicDescriptor> syslogList = new ArrayList<HeuristicDescriptor>();
        ResultSet resultSet = null;

        try {
            try {
                resultSet = DBHelper.executeQuery(SELECT_SYSLOG_ACTIONS);

                while (resultSet.next()) {
                    int id = resultSet.getInt("ID");
                    String title = resultSet.getString("TITLE");
                    String desc = resultSet.getString("DESCRIPTION");
                    String operator = resultSet.getString("OPERATOR");
                    String operand = resultSet.getString("OPERAND");
                    String type = resultSet.getString("TYPE");

                    HeuristicDescriptor heu = new HeuristicDescriptor(id, title, desc, operator, operand, type);

                    int dataSetId = resultSet.getInt("DATASETID");
                    if (dataSetId != -1) {
                        Map<String, String> dataSetInfoMap = getDataSetNameAndStatus(dataSetId);
                        heu.setDataSet(dataSetInfoMap.get(DATASETNAME));
                        heu.setStatus(Boolean.parseBoolean(dataSetInfoMap.get(STATUS)));
                        getSyslogHeuristicsCommands(dataSetId, heu.getOsCommandMap());
                    }
                    syslogList.add(heu);
                }

            } catch (Exception e) {
                logger.warn("Error while getting Heuristics for :", attribute, e);
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                        resultSet = null;
                    } catch (Exception ex) {
                        logger.debug(RESULTSET_CLOSE_ERROR, ex);
                    }
                }

            }
        } catch (Exception e) {
            logger.debug(CONNECTION_ERROR, e);
        }
        return syslogList;
    }

    public static Map<String, String> getDataSetNameAndStatus(int dataSetId) {
        ResultSet resultSet = null;
        Map<String, String> dataSetInfoMap = new HashMap<String, String>();
        try {
            try {
                resultSet = DBHelper
                        .executeQuery("select datasetname,status from syslog_datasets where id=" + dataSetId);
                while (resultSet.next()) {
                    dataSetInfoMap.put(DATASETNAME, resultSet.getString(DATASETNAME));
                    dataSetInfoMap.put(STATUS, String.valueOf(resultSet.getBoolean(STATUS)));
                }

            } catch (Exception e) {
                logger.warn("Error while getting dataset details for Syslog Heuristics", e);
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                        resultSet = null;
                    } catch (Exception ex) {
                        logger.debug(RESULTSET_CLOSE_ERROR, ex);
                    }
                }

            }
        } catch (Exception e) {
            logger.debug(CONNECTION_ERROR, e);
        }
        return dataSetInfoMap;
    }

    public static void getSyslogHeuristicsCommands(int dataSetId, Map<String, String> osCommandMap) {
        ResultSet resultSet = null;

        try {
            try {
                resultSet = DBHelper.executeQuery("select * from syslog_commands where id=" + dataSetId);
                while (resultSet.next()) {
                    String os = resultSet.getString("OS");
                    String command = resultSet.getString("COMMAND");
                    osCommandMap.put(os, command);
                }

            } catch (Exception e) {
                logger.warn("Error while getting commands for Syslog Heuristics", e);
                e.printStackTrace();
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                        resultSet = null;
                    } catch (Exception ex) {
                        logger.debug(RESULTSET_CLOSE_ERROR, ex);
                    }
                }

            }
        } catch (Exception e) {
            logger.debug(CONNECTION_ERROR, e);
        }
    }

    public static List<TrapActionDescriptor> getTrapActionHeuristics() {
        List<TrapActionDescriptor> trapActionList = new ArrayList<TrapActionDescriptor>();
        ResultSet resultSet = null;
        try {
            try {
                resultSet = DBHelper.executeQuery(SELECT_TRAP_ACTIONS);
                while (resultSet.next()) {
                    int id = resultSet.getInt("ID");
                    String title = resultSet.getString("TITLE");
                    String objectId = resultSet.getString("OBJECT_ID");
                    String type = resultSet.getString("TYPE");
                    TrapActionDescriptor heu = new TrapActionDescriptor(id, title, objectId, type);
                    int dataSetId = resultSet.getInt("DATASETID");
                    if (dataSetId != -1) {
                        Map<String, String> dataSetInfoMap = getDataSetNameAndStatus(dataSetId);
                        heu.setDataSet(dataSetInfoMap.get(DATASETNAME));
                        heu.setStatus(Boolean.parseBoolean(dataSetInfoMap.get(STATUS)));
                        getSyslogHeuristicsCommands(dataSetId, heu.getOsCommandMap());
                    }
                    trapActionList.add(heu);
                }
            } catch (Exception e) {
                logger.warn("Error while getting Heuristics for :", "TrapAction", e);
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                        resultSet = null;
                    } catch (Exception ex) {
                        logger.debug(RESULTSET_CLOSE_ERROR, ex);
                    }
                }
            }
        } catch (Exception e) {
            logger.debug(CONNECTION_ERROR, e);
        }
        return trapActionList;
    }

    public static void updateSyslogHeuristics(String attribute, List<HeuristicDescriptor> list) {
        logger.info("Updating Heuristics for " + attribute);
        for (HeuristicDescriptor heu : list) {
            if ("Add".equals(heu.getAction())) {
                insertSyslogHeuristics(heu);
            }
            if ("Modify".equals(heu.getAction())) {
                modifySyslogHeuristics(heu);
            }
            if ("Delete".equals(heu.getAction())) {
                deleteSyslogHeuristics(heu.getId());
            }
        }
    }

    public static void updateTrapActionHeuristics(String attribute, List<TrapActionDescriptor> list) {
        logger.info("Updating SNMP Trap ActionsHeuristics for " + attribute);
        for (TrapActionDescriptor heu : list) {
            if ("Add".endsWith(heu.getAction())) {
                insertTrapActionHeuristics(heu);
            }
            if ("Modify".equals(heu.getAction())) {
                modifyTrapActionheuristics(heu);
            }
            if ("Delete".equals(heu.getAction())) {
                deleteTrapActionHeuristics(heu.getId());
            }
        }
    }

    private static void deleteTrapActionHeuristics(int id) {
        try {
            removeDataSet(id, "snmp_trap_actions");
            DBHelper.executeUpdate("delete from snmp_trap_actions where id = " + id);
            ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT,
                    ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "SNMP Trap Action Deleted", -1, -1);
        } catch (Exception ee) {
            logger.error("Exception while trying to delete from snmp_trap_actions : ", ee);
        }
    }

    private static void modifyTrapActionheuristics(TrapActionDescriptor heu) {
        try {
            int dataSetId = -1;
            String dataSetName = heu.getDataSet();
            if (dataSetName == null || dataSetName.equals("")) {
                removeDataSet(heu.getId(), "snmp_trap_actions");
            } else {
                dataSetId = getDataSetId(dataSetName);
                if (dataSetId == -1) {
                    dataSetId = createDataSet(dataSetName, true);
                }
                updateSyslogActionsCommand(dataSetId, heu.getOsCommandMap(), heu.getAction());
            }
            StringBuffer sbf = new StringBuffer();
            sbf.append("update snmp_trap_actions set ");
            sbf.append("title = '").append(heu.getTrapActionTitle()).append("'");
            sbf.append(" , object_id = '").append(heu.getOID()).append("'");
            sbf.append(" , datasetid = ").append(dataSetId);
            sbf.append(" where id = ").append(heu.getId());

            logger.info("SNMP Trap Action Update query" + sbf.toString());
            DBHelper.executeUpdate(sbf.toString());

            ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT,
                    ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "SNMP Trap Action Modified", -1, -1);
        } catch (Exception e) {
            logger.debug("Exception while Modifying SNMP TrapAction", e);
        }
    }

    private static void insertSyslogHeuristics(HeuristicDescriptor heu) {
        try {
            String dataSetName = heu.getDataSet();
            int dataSetId = -1;

            if (dataSetName != null && !dataSetName.equals("")) {
                dataSetId = createDataSet(dataSetName, false);
                updateSyslogActionsCommand(dataSetId, heu.getOsCommandMap(), heu.getAction());
            }
            StringBuffer sbf = new StringBuffer();
            sbf.append("insert into syslog_actions (title, description, operator, operand, dataSetId) values (");

            sbf.append("'").append(heu.getSyslogTitle()).append("'");
            if (heu.getDescription() != null) {
                sbf.append(",'").append(heu.getDescription()).append("'");
            } else {
                sbf.append(",'  '");
            }

            sbf.append(",'").append(heu.getSyslogOperator()).append("'");
            sbf.append(",'").append(heu.getSyslogOperand()).append("'");
            sbf.append(",'").append(dataSetId).append("')");

            logger.info("Syslog Insert query" + sbf.toString());
            DBHelper.executeUpdate(sbf.toString());

            ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT,
                    ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "Syslog Pattern Added", -1, -1);
        }

        catch (Exception e) {
            logger.debug("Error while inserting into syslog heuristics", e);
        }
    }

    private static int getDataSetId(String dataSetName) {
        int dataSetId = -1;
        PreparedStatement ps = null;
        Connection c = null;
        ResultSet rs = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("select id from syslog_datasets where datasetname='" + dataSetName + "'");
            rs = ps.executeQuery();
            if (rs.next()) {
                dataSetId = rs.getInt("id");
            }
        } catch (Exception ee) {
            logger.warn("Error while trying to get dataset id", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                logger.debug(RESULTSET_CLOSE_ERROR, ex);
            }
            try {
                ps.close();
            } catch (Exception ee) {
                logger.error("Error while closing ps", ee);
            }
            DBHelper.releaseConnection(c);

        }
        return dataSetId;

    }

    private static int createDataSet(String dataSetName, boolean status) throws SQLException {
        int dataSetId = -1;
        DBHelper.executeUpdate(
                "insert into syslog_datasets (datasetname,status) values('" + dataSetName + "'," + status + ")");
        dataSetId = getDataSetId(dataSetName);
        return dataSetId;
    }

    private static int removeDataSet(int syslogActionId, String parentTable) {
        int dataSetId = -1;
        PreparedStatement ps = null;
        Connection c = null;
        ResultSet rs = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("select datasetid from " + parentTable + " where id=" + syslogActionId);
            rs = ps.executeQuery();
            if (rs.next()) {
                dataSetId = rs.getInt("datasetid");
            }
            if (dataSetId != -1) {
                DBHelper.executeUpdate("delete from syslog_datasets where id = " + dataSetId);
            }
        } catch (Exception ee) {
            logger.warn("Error while trying to get dataset id", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ex) {
                logger.debug(RESULTSET_CLOSE_ERROR, ex);
            }
            try {
                ps.close();
            } catch (Exception ee) {
                logger.error("Error while closing ps", ee);
            }
            DBHelper.releaseConnection(c);

        }
        return dataSetId;

    }

    private static void updateSyslogActionsCommand(int dataSetId, Map<String, String> osCommandMap, String action) {
        if ("Modify".equals(action)) {
            try {
                DBHelper.executeUpdate("delete from syslog_commands where id = " + dataSetId);
            } catch (SQLException e) {
                logger.error("Exception while trying to delete syslog commands", e);
            }
        }
        Iterator<String> itr = osCommandMap.keySet().iterator();
        while (itr.hasNext()) {
            String os = itr.next();
            String command = osCommandMap.get(os);
            try {
                DBHelper.executeUpdate("insert into syslog_commands (id,os,command) values (" + dataSetId + ",'"
                        + os + "','" + command + "')");
            } catch (SQLException e) {
                logger.error("Exception while trying to insert syslog commands", e);
            }
        }

    }

    private static void insertTrapActionHeuristics(TrapActionDescriptor heu) {
        try {
            String dataSetName = heu.getDataSet();
            int dataSetId = -1;

            if (dataSetName != null && !dataSetName.equals("")) {
                dataSetId = createDataSet(dataSetName, true);
                updateSyslogActionsCommand(dataSetId, heu.getOsCommandMap(), heu.getAction());
            }
            StringBuffer sbf = new StringBuffer();
            sbf.append("insert into snmp_trap_actions (title,object_id,type,datasetid) values (");

            sbf.append("'").append(heu.getTrapActionTitle()).append("'");
            sbf.append(",'").append(heu.getOID()).append("'");
            sbf.append(",'").append("User_Defined").append("'");
            sbf.append(",'").append(dataSetId).append("')");

            logger.info("Trap Action Insert query" + sbf.toString());
            DBHelper.executeUpdate(sbf.toString());
            ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT,
                    ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "SNMP Trap Action Added", -1, -1);
        } catch (Exception e) {
            logger.debug("Exception while inserting into SNMP Trap Actions heuristics", e);
        }
    }

    public static void deleteSyslogHeuristics(int id) {

        try {
            removeDataSet(id, "syslog_actions");
            DBHelper.executeUpdate("delete from syslog_actions where id = " + id);
            ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT,
                    ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "Syslog Pattern Deleted", -1, -1);
        } catch (Exception ee) {
            logger.error("Exception while trying to delete from syslog_actions : ", ee);
        }

    }

    public static void modifySyslogHeuristics(HeuristicDescriptor heu) {
        try {
            int dataSetId = -1;
            String dataSetName = heu.getDataSet();
            if (dataSetName == null || dataSetName.equals("")) {
                removeDataSet(heu.getId(), "syslog_actions");
            } else {
                dataSetId = getDataSetId(dataSetName);
                if (dataSetId == -1) {
                    dataSetId = createDataSet(dataSetName, false);
                }
                updateSyslogActionsCommand(dataSetId, heu.getOsCommandMap(), heu.getAction());
            }
            StringBuffer sbf = new StringBuffer();
            sbf.append("update syslog_actions set ");
            sbf.append("title = '").append(heu.getSyslogTitle()).append("'");

            if (heu.getDescription() != null) {
                sbf.append(" , description = '").append(heu.getDescription()).append("'");
            } else {
                sbf.append(" , description = ' '");
            }
            sbf.append(" , operator = '").append(heu.getSyslogOperator()).append("'");
            sbf.append(" , operand = '").append(heu.getSyslogOperand()).append("'");
            sbf.append(" , datasetid = ").append(dataSetId);
            sbf.append(" where id = ").append(heu.getId());

            logger.info("Syslog Update query" + sbf.toString());
            DBHelper.executeUpdate(sbf.toString());

            ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT,
                    ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "Syslog Pattern Modified", -1, -1);
        } catch (Exception e) {
            logger.debug("Exception while Modifying Syslog", e);
        }

    }

    public static void deleteSnmpMib(int deviceId) {
        String delQuery = DBHelperConstants.DELETE_SNMP_MIB_QUERY;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBHelper.getConnection();
            ps = con.prepareStatement(delQuery);
            ps.setInt(1, deviceId);
            ps.executeUpdate();
        } catch (Exception ex) {
            logger.error("Error while deleting snmp data for device = " + deviceId, ex);
        } finally {
            try {
                ps.close();
            } catch (SQLException ex) {
                logger.error("Error while closing ps", ex);
            }
            DBHelper.releaseConnection(con);
        }
    }

    public static void insertSnmpMib(int deviceId, String oid, String tableOid, String mib, String type) {
        String insQuery = DBHelperConstants.INSERT_SNMP_MIB_QUERY;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBHelper.getConnection();
            ps = con.prepareStatement(insQuery);
            ps.setInt(1, deviceId);
            ps.setString(2, oid);
            ps.setString(3, tableOid);
            // TODO: Do we need to compress data before storing?
            if (ps instanceof OraclePreparedStatement) {
                ((OraclePreparedStatement) ps).setStringForClob(4, mib);
            } else {
                logger.debug("PS is not OraclePreparedStatement, inserting as regular string");
                ps.setString(4, mib);
            }
            ps.setString(5, type);
            ps.executeUpdate();
        } catch (Exception ex) {
            logger.error("Error while inserting snmp data", ex);
        } finally {
            try {
                ps.close();
            } catch (SQLException ex) {
                logger.error("Error while closing ps", ex);
            }
            DBHelper.releaseConnection(con);
        }
    }

    public static void insertSnmpColsInBatch(int deviceId, Map<String, ISnmpColumn> snmpCols, String type) {
        String insQuery = DBHelperConstants.INSERT_SNMP_MIB_QUERY;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBHelper.getConnection();
            ps = con.prepareStatement(insQuery);
            con.setAutoCommit(false);
            for (String colOid : snmpCols.keySet()) {
                // e.g. if colOid is .1.3.6.1.2.1.47.1.1.1.1.5.2, then entryOid
                // is .1.3.6.1.2.1.47.1.1.1.1.5
                String entryOid = colOid.substring(0, colOid.lastIndexOf("."));
                // and tableOid is .1.3.6.1.2.1.47.1.1.1.1
                String tableOid = entryOid.substring(0, entryOid.lastIndexOf("."));
                ISnmpColumn snmpCol = snmpCols.get(colOid);
                String colXml = snmpCol.toXml();
                ps.setInt(1, deviceId);
                ps.setString(2, colOid);
                ps.setString(3, tableOid);
                ps.setString(6, snmpCol.getTitle());
                // TODO: Do we need to compress data before storing?
                if (ps instanceof OraclePreparedStatement) {
                    ((OraclePreparedStatement) ps).setStringForClob(4, colXml);
                } else {
                    logger.debug("PS is not OraclePreparedStatement, inserting as regular string");
                    ps.setString(4, colXml);
                }
                ps.setString(5, type);
                ps.addBatch();
            }
            ps.executeBatch();
            con.commit();
        } catch (SQLException sqlex) {
            logger.error("Error while inserting rows to database", sqlex);
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException ex) {
                logger.error("Error while calling rollback on db conn", ex);
            }
        } catch (Exception ex) {
            logger.error("Error while inserting snmp data in batch", ex);
        } finally {
            try {
                if (con != null) {
                    con.setAutoCommit(true);
                }
            } catch (SQLException sqlex) {
                logger.error("Error while calling setAutoCommit", sqlex);
            }
            try {
                ps.close();
            } catch (SQLException sqlex) {
                logger.error("Error while closing ps", sqlex);
            }
            DBHelper.releaseConnection(con);
        }
    }

    public static void insertSnmpScalarsInBatch(int deviceId, Map<String, ISnmpScalar> snmpScalars, String type) {
        String insQuery = DBHelperConstants.INSERT_SNMP_MIB_QUERY;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBHelper.getConnection();
            ps = con.prepareStatement(insQuery);
            con.setAutoCommit(false);
            for (String oid : snmpScalars.keySet()) {
                ISnmpScalar value = snmpScalars.get(oid);
                String xmlValue = value.toXml();
                ps.setInt(1, deviceId);
                ps.setString(2, oid);
                // There's no table oid for scalar values
                ps.setString(3, null);
                ps.setString(6, null);
                // TODO: Do we need to compress data before storing?
                if (ps instanceof OraclePreparedStatement) {
                    ((OraclePreparedStatement) ps).setStringForClob(4, xmlValue);
                } else {
                    logger.debug("PS is not OraclePreparedStatement, inserting as regular string");
                    ps.setString(4, xmlValue);
                }
                ps.setString(5, type);
                ps.addBatch();
            }
            ps.executeBatch();
            con.commit();
        } catch (SQLException sqlex) {
            logger.error("Error while inserting rows to database", sqlex);
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException ex) {
                logger.error("Error while calling rollback on db conn", ex);
            }
        } catch (Exception ex) {
            logger.error("Error while inserting snmp data in batch", ex);
        } finally {
            try {
                if (con != null) {
                    con.setAutoCommit(true);
                }
            } catch (SQLException sqlex) {
                logger.error("Error while calling setAutoCommit", sqlex);
            }
            try {
                ps.close();
            } catch (SQLException sqlex) {
                logger.error("Error while closing ps", sqlex);
            }
            DBHelper.releaseConnection(con);
        }
    }

    public static List<SnmpMibData> getSnmpByDevice(String deviceId) {
        String selQuery = DBHelperConstants.GET_SNMP_MIB_BY_DEVICE_QUERY;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String colXml = null;
        List<SnmpMibData> snmpMibList = new ArrayList<>();
        try {
            con = DBHelper.getConnection();
            ps = con.prepareStatement(selQuery);
            ps.setInt(1, Integer.parseInt(deviceId));
            rs = ps.executeQuery();
            while (rs.next()) {
                SnmpMibData snmpMib = new SnmpMibData();
                snmpMib.setDeviceId(deviceId);
                snmpMib.setMib(rs.getString("mib"));
                snmpMib.setOid(rs.getString("oid"));
                snmpMib.setTitle(rs.getString("title"));
                snmpMibList.add(snmpMib);
            }
        } catch (Exception ex) {
            logger.error("Exception while quering snmp mibs", ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    logger.error("Error while closing resultset", ex);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    logger.error("Error while closing ps", ex);
                }
            }
            DBHelper.releaseConnection(con);
        }
        return snmpMibList;
    }

    public static ISnmpColumn getSnmpColumn(String deviceId, String oid) {
        String selQuery = DBHelperConstants.GET_SNMP_MIB_QUERY;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String colXml = null;
        ISnmpColumn snmpCol = null;
        try {
            con = DBHelper.getConnection();
            ps = con.prepareStatement(selQuery);
            ps.setString(1, deviceId);
            ps.setString(2, oid);
            rs = ps.executeQuery();
            if (rs.next()) {
                String mibType = rs.getString("type");
                if (mibType != null && mibType.equals(MIBNodeType.TABLE.toString())) {
                    colXml = rs.getString("mib");
                    if (colXml != null) {
                        Document doc = XMLUtil.parseXMLDocument(colXml);
                        snmpCol = new SnmpColumn(oid);
                        snmpCol.load(doc.getDocumentElement());
                    }
                }
            }
        } catch (Exception ex) {
            logger.error("Exception while quering snmp mibs", ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    logger.error("Error while closing resultset", ex);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    logger.error("Error while closing ps", ex);
                }
            }
            DBHelper.releaseConnection(con);
        }
        return snmpCol;
    }

    public static ISnmpScalar getSnmpScalar(String deviceId, String oid) {
        String selQuery = DBHelperConstants.GET_SNMP_MIB_QUERY;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String scalarXml = null;
        SnmpScalar scalar = null;
        try {
            con = DBHelper.getConnection();
            ps = con.prepareStatement(selQuery);
            if (isPostgres()) {
                ps.setInt(1, Integer.valueOf(deviceId));

            } else {
                ps.setString(1, deviceId);
            }
            ps.setString(2, oid);
            rs = ps.executeQuery();
            if (rs.next()) {
                String mibType = rs.getString("type");
                if (mibType != null && mibType.equals(MIBNodeType.SCALAR.toString())) {
                    scalarXml = rs.getString("mib");
                    if (scalarXml != null) {
                        Document doc = XMLUtil.parseXMLDocument(scalarXml);
                        scalar = new SnmpScalar(0, "");
                        scalar.load(doc.getDocumentElement());
                    }
                }
            }

        } catch (Exception ex) {
            logger.error("Exception while quering snmp mibs", ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    logger.error("Error while closing resultset", ex);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    logger.error("Error while closing ps", ex);
                }
            }
            DBHelper.releaseConnection(con);
        }
        return scalar;
    }

    public static List<Integer> getRunConfId(String os, String model, String version) {
        ResultSet rs = null;
        List<Integer> NodeIds = new ArrayList<Integer>();

        try {

            if (ServerProperties.getInstance().isPostgres()) {
                rs = DBHelper.executeQuery("select device_id from inventory_changes where os_name ~* '" + os + "'"
                        + " and product_model ~* '" + model + "'" + " and os_version ~* '" + version + "'");
            } else {
                rs = DBHelper.executeQuery("select device_id from inventory_changes where ((REGEXP_LIKE (os_name,'"
                        + os + "', 'i'))" + " and (REGEXP_LIKE (product_model,'" + model + "', 'i'))"
                        + " and (REGEXP_LIKE (os_version,'" + version + "', 'i')))");
            }

            if (rs != null) {
                while (rs.next()) {
                    NodeIds.add(rs.getInt("device_id"));

                }
                return NodeIds;
            }
        } catch (Exception e) {
            logger.warn("Error while getting the  Node Ids ", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ee) {
            }

        }
        return null;
    }

    public static List<Integer> getStartUpIds(String os, String model, String version) {
        ResultSet rs = null;
        List<Integer> NodeIds = new ArrayList<Integer>();

        try {
            rs = DBHelper.executeQuery("select device_id from inventory_changes where ((REGEXP_LIKE (os_name,'" + os
                    + "', 'i'))" + " and (REGEXP_LIKE (product_model,'" + model + "', 'i'))"
                    + "and (REGEXP_LIKE (os_version,'" + version + "', 'i')))");

            if (rs != null) {
                while (rs.next()) {
                    NodeIds.add(rs.getInt("device_id"));

                }
                return NodeIds;
            }
        } catch (Exception e) {
            logger.warn("Error while getting the  Node Ids ", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ee) {
            }
        }
        return null;
    }

    public static void updatePCEInputs(int nodeId, IDSPDeviceInfoProvider device) {
        String wlcMaxAPSupported = device.getAttribute("WlcMaxAPSupported");
        String smuNames = device.getAttribute("SMUNames");
        String processorRev = device.getAttribute("ProcessorRev");

        // Check if entry for node id already exists
        boolean update = false;
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from pce_input_data where device_id =" + nodeId);

            if (rs != null && rs.next()) {
                update = true;
            }
        } catch (Exception e) {
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ee) {
            }
        }

        String query = null;
        if (wlcMaxAPSupported != null) {
            try {
                if (update) {
                    query = "update pce_input_data set WLC_MAX_AP_SUPPORTED = " + wlcMaxAPSupported
                            + "where device_id = " + nodeId;
                } else {
                    query = "insert into pce_input_data (device_id, WLC_MAX_AP_SUPPORTED) values (" + nodeId + ","
                            + wlcMaxAPSupported + ")";
                }

                DBHelper.executeUpdate(query);
            } catch (Exception e) {
                logger.error("Error while updating wlcMaxAPSupported..", e);
            }
        }
        if (smuNames != null) {
            try {
                if (update) {
                    query = "update pce_input_data set iosxr_smu_names = '" + smuNames + "' where device_id = "
                            + nodeId;
                } else {
                    query = "insert into pce_input_data (device_id, iosxr_smu_names) values (" + nodeId + ",'"
                            + smuNames + "')";
                }
                DBHelper.executeUpdate(query);
            } catch (Exception e) {
                logger.error("Error while updating iosxr_smu_names..", e);
            }
        }
        if (processorRev != null) {
            try {
                if (update) {
                    query = "update pce_input_data set processor_rev = " + processorRev + "where device_id = "
                            + nodeId;
                } else {
                    query = "insert into pce_input_data (device_id, processor_rev) values (" + nodeId + ",'"
                            + processorRev + "')";
                }

                DBHelper.executeUpdate(query);
            } catch (Exception e) {
                logger.error("Error while updating processorRev..", e);
            }
        }
    }

    public static int getCustomersCount(HashMap<String, Object> filterMap) {
        return PolicyDBHelper.getRecordCount("customers", filterMap);
    }

    public static List<Customer> loadCustomers(int start, int pageLength, HashMap<String, Object> filterMap) {
        ArrayList<Customer> lis = new ArrayList<Customer>();
        ResultSet rs = null;
        ArrayList<Customer> updateCustomers = new ArrayList<Customer>();

        try {
            StringBuffer sb = MgmtReportsPaginationUtil.getQuery(start, pageLength, "customers", filterMap);

            rs = DBHelper.executeQuery(sb.toString());

            while (rs.next()) {
                Customer customer = new Customer();

                int id = rs.getInt("customer_id");

                customer.setId(id);

                String name = rs.getString("customer_name");
                customer.setCustomerName(name);

                String contact_name = rs.getString("contact_name");
                if (contact_name != null) {
                    customer.setContactName(contact_name);
                }

                String contact_email = rs.getString("contact_email");
                if (contact_email != null) {
                    customer.setEmailAddresses(contact_email);
                }

                String contact_phone = rs.getString("contact_phone");
                if (contact_phone != null) {
                    customer.setContactPhoneNumber(contact_phone);
                }

                String wing_login = rs.getString("wing_login");
                if (wing_login != null) {
                    customer.setLogin(wing_login);
                }

                String securityKey = rs.getString("security_key");
                if (securityKey != null) {
                    customer.setSecurityKey(securityKey);
                }

                int noOfLicenses = rs.getInt("no_of_licenses");
                int totLicUnits = LicenseValidator.getInstance().getNoOfDevices();
                if (totLicUnits == -1) {
                    noOfLicenses = -1;
                    updateCustomers.add(customer);
                } else if (noOfLicenses > totLicUnits) {
                    noOfLicenses = totLicUnits;
                    updateCustomers.add(customer);
                }
                customer.setNoOfLicenses(noOfLicenses);

                int creatorId = rs.getInt("creator_id");
                customer.setCreatorId(creatorId);

                String creatorName = rs.getString("creator_name");
                UserDetails ud = UsersFactory.getUser(creatorId);
                if (ud != null) {
                    customer.setCreaterName(
                            ud.getName() != null ? (ud.getName() + " (" + ud.getLogin() + ")") : ud.getLogin());
                } else {
                    customer.setCreaterName(creatorName);
                }
                customer.setNoOfDevices(getCustomerDeviceCount(id));
                lis.add(customer);
            }
        } catch (Exception ee) {
            logger.error("Error while loading customers from db.", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.debug("Error while closing cursor.", ee);
            }
        }
        if (updateCustomers.size() > 0) {
            for (Customer cus : updateCustomers) {
                insertOrUpdateCustomer(cus);
            }
        }
        return lis;
    }

    private static int getCustomerDeviceCount(int id) {
        int count = 0;
        ResultSet rs = null;

        try {
            String query = "Select count(device_id) as device_count from customer_device where customer_id=" + id
                    + " and device_id  not in (select mod_id from nodes_modules)";
            rs = DBHelper.executeQuery(query);

            while (rs.next()) {
                count = rs.getInt("device_count");
            }
        } catch (Exception ee) {
            logger.error("Error while getting device count associated with customer from db.", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.debug("Error while closing cursor.", ee);
            }
        }

        return count;
    }

    /**
     * (1)To find any pas job's status as Running, if new pas job schedules as run now then job id passes as zero or
     * negative(as it checks before creation of job and no job id). (2)Any scheduled pas job comes to start, it should
     * be confirmed no other pas job started or running other than this scheduled pas job. So this case job id(greater
     * than zero) of scheduled job need to be passed to separate this job's run state from other pas job.
     *
     * @param jobId
     *            - job id
     * @return
     */
    public static boolean findAnyPasJobRunning(int jobId) {
        boolean running = false;
        ResultSet rs = null;
        String query = null;

        try {

            if (jobId <= 0) {
                query = "SELECT JOB_ID, JOB_NAME, JOB_GROUP FROM pari_job_addl_details WHERE (JOB_GROUP = 'PasProfilingJobGroup' OR JOB_GROUP = 'PasImportJobGroup') AND JOB_ID IN (SELECT jobid from pari_job_runs where job_run_state = 'Running')";
            } else {
                query = "SELECT JOB_ID, JOB_NAME, JOB_GROUP FROM pari_job_addl_details WHERE (JOB_GROUP = 'PasProfilingJobGroup' OR JOB_GROUP = 'PasImportJobGroup') AND JOB_ID IN (SELECT jobid from pari_job_runs where jobid !="
                        + jobId + " and job_run_state = 'Running')";
            }
            logger.debug("isPasJobRunning query check : " + query);
            rs = DBHelper.executeQuery(query);
            while (rs.next()) {
                running = true;
                logger.debug("Currently running PAS job details given below :");
                logger.debug("JOB_ID    :   " + rs.getInt("JOB_ID"));
                logger.debug("JOB_NAME  :   " + rs.getString("JOB_NAME"));
                logger.debug("JOB_GROUP :   " + rs.getString("JOB_GROUP"));
            }
        } catch (Exception ee) {
            logger.error("exception caught while executing findAnyPasJobRunning!", ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.error("Error while closing cursor.[findAnyPasJobRunning]", ee);
            }
        }
        return running;
    }

    public static List<Integer> getConfigNodeId(String os, String model, String version) {

        ResultSet rs = null;
        List<Integer> NodeIds = new ArrayList<Integer>();

        try {
            String query = getQueryForSearchId(os, model, version);
            rs = DBHelper.executeQuery(query);

            if (rs != null) {
                while (rs.next()) {
                    NodeIds.add(rs.getInt("device_id"));

                }
                return NodeIds;
            }
        } catch (Exception e) {
            logger.warn("Error while getting the  Node Ids ", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ee) {
            }

        }
        return null;
    }

    private static String getQueryForSearchId(String os, String model, String version) {
        StringBuffer query = new StringBuffer();
        StringBuffer osQuery = null;
        query = query.append(" select device_id from inventory_changes where (");
        if (os != null) {
            osQuery = new StringBuffer();
            osQuery.append(" (os_name IN (" + os + ") )");
        }
        if (model != null) {
            if (osQuery != null) {
                query.append(osQuery).append(" and ").append("(product_model IN(" + model + ")) and ");
            } else {
                query.append("(product_model IN(" + model + ")) and ");
            }
        } else if (os != null) {
            query.append(osQuery).append(" and ");

        }

        if (isPostgres()) {
            version = version.replace("*", "%");
            query.append("(LIKE (os_version,'" + version + "'))");
        } else {
            query.append("(REGEXP_LIKE (os_version,'" + version + "', 'i'))");
        }
        query.append(")");
        return query.toString();

    }

    public static String[] retrieveCustomerSpecificEmails(int customerId) {
        String[] emails = new String[0];
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select * from customers where customer_id=" + customerId);
            if ((rs != null) && rs.next()) {
                String emailStr = rs.getString("contact_email");

                if (!(emailStr == null || "".equals(emailStr))) {
                    emails = emailStr.split(",");
                }
            }
        } catch (Exception e) {
            logger.error("Exception while getting customer related emails", e);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
                logger.error("Exception while closing the result set", e);
            }
        }
        return emails;
    }

    public static boolean retrieveMailNotificationOptionForFaults(String fltCondition) {
        boolean isSendMail = false;
        ResultSet rs = null;
        try {
            rs = DBHelper
                    .executeQuery("select NOTIFICATION_ENABLED from fault_notification_prefs where FLT_CONDITION='"
                            + fltCondition + "'");
            if ((rs != null) && rs.next()) {
                isSendMail = rs.getBoolean("NOTIFICATION_ENABLED");
            }
        } catch (Exception ex) {
            logger.error("Exception while getting email notification option", ex);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.error("Exception while closing the result set", ee);
            }
        }

        return isSendMail;
    }

    public static String[] getEmailListFromSMTPSettings() {
        String[] emails = new String[0];
        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery("select emails from smtp_settings");
            if ((rs != null) && rs.next()) {
                String mailList = rs.getString("emails");
                if ((mailList != null) && (mailList.trim().length() > 0)) {
                    emails = mailList.split(",");
                }
            }
        } catch (Exception ex) {
            logger.error("Exception while getting email addresses", ex);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.error("Exception while closing the result set", ee);
            }
        }

        return emails;
    }

    public static int getCustomerIdUsingFaultSubModule(String profileName) {
        int customerId = -1;
        ResultSet rs = null;
        try {
            rs = DBHelper
                    .executeQuery("select customer_id from policy_profiles where prof_name='" + profileName + "'");
            if ((rs != null) && rs.next()) {
                customerId = rs.getInt("customer_id");
            }

        } catch (Exception ex) {
            logger.error("Exception while getting customer id", ex);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
                logger.error("Exception while closing the result set", ee);
            }
        }

        return customerId;

    }

    public static void insertOrUpdateCustomerFaults(Customer customer) {
        Connection c = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            boolean insert = true;

            String query = "select * from customer_fault where customer_id=" + customer.getId();
            rs = DBHelper.executeQuery(query);

            if (rs.next()) {
                insert = false;
            }

            List<CustomerFault> faultList = customer.getFaultList();
            if (faultList != null && faultList.size() != 0) {
                if (!insert) {
                    ps = c.prepareStatement(DBHelperConstants.CUSTOMER_FAULT_DELETE);
                    ps.setInt(1, customer.getId());
                    ps.executeUpdate();

                    // FaultSpecFactory.getInstance().deleteFaultProfileOnly(customer.getId());
                }
                for (CustomerFault fault : faultList) {
                    ps = c.prepareStatement(DBHelperConstants.CUSTOMER_FAULT_INSERT);
                    ps.setInt(1, customer.getId());
                    ps.setString(2, fault.getName());
                    ps.setString(3, fault.getDescription());
                    ps.setString(4, fault.getSeverity());
                    ps.setInt(5, fault.getSendMail());
                    ps.executeUpdate();
                }
            } else if ((faultList == null || faultList.size() == 0) && !insert) {
                ps = c.prepareStatement(DBHelperConstants.CUSTOMER_FAULT_DELETE);
                ps.setInt(1, customer.getId());
                ps.executeUpdate();

                // FaultSpecFactory.getInstance().deleteFaultProfileOnly(customer.getId());
            }

        } catch (Exception ee) {
            logger.warn("Error while inserting or updating the customer faults", ee);
        } finally {
            try {
                ps.close();
            } catch (Exception ee) {
                logger.error("Error while closing statement for updating customer faults", ee);
            }

            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
                logger.error("Error while closing connection for updating customer faults", ee);
            }

            try {
                rs.close();
            } catch (Exception ee) {
                logger.error("Error while closing resultset for updating customer faults", ee);
            }
        }
    }

    public static void loadCustomerFaultNotificationSettings() {
        logger.debug("Loading Customer Specific Faults");

        ResultSet rs = null;

        try {
            rs = DBHelper.executeQuery(DBHelperConstants.READ_CUSTOMER_FAULT);

            FaultSpec cusSpec;

            while (rs.next()) {
                int profId = rs.getInt("CUSTOMER_ID");
                String cusfltCondition = rs.getString("NAME");
                String cusfltSeverity = rs.getString("SEVERITY");
                int enabled = rs.getInt("SENDMAIL");
                FaultProfile fp = FaultSpecFactory.getInstance().getFaultProfile(profId);
                if (fp == null) {
                    continue;
                }

                fp.enableMailNotification(cusfltCondition, (enabled == 1));
                fp.addFaultCondition(cusfltCondition, cusfltSeverity);
                cusSpec = fp.getFaultSpec(cusfltCondition);

                if (cusSpec == null) {
                    FaultSpec spe = FaultSpecFactory.getInstance().getFaultSpec(cusfltCondition);
                    cusSpec = new FaultSpec(spe);
                    fp.addFautlSpec(cusfltCondition, cusSpec);
                    FaultSpecFactory.getInstance().updateSpecToProfile(cusfltCondition, fp.getProfId());
                }
                cusSpec.setSeverity(FaultSeverity.getByName(cusfltSeverity));
                FaultProfile[] prf = FaultSpecFactory.getInstance().getAllProfiles();
            }
        } catch (Exception ee) {
            ee.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
    }

    public static List<CustomerFault> getCustomerFaults(int customerId) {
        Connection c = DBHelper.getConnection();
        ResultSet rs = null;
        List<CustomerFault> faultList = null;
        try {
            faultList = new LinkedList<CustomerFault>();

            String query = "select * from customer_fault where customer_id=" + customerId;
            rs = DBHelper.executeQuery(query);

            if (rs != null) {
                while (rs.next()) {
                    String name = rs.getString("name");
                    String description = rs.getString("description");
                    String severity = rs.getString("severity");
                    int sendMail = rs.getInt("sendmail");
                    CustomerFault fault = new CustomerFault(name, description, severity, sendMail);
                    faultList.add(fault);
                }
            }

        } catch (Exception ee) {
            logger.warn("Error while getting the customer faults", ee);
        } finally {
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ee) {
                logger.error("Error while releasing connection for customer faults", ee);
            }

            try {
                rs.close();
            } catch (Exception ee) {
                logger.error("Error while releasing resultset for customer faults", ee);
            }
        }
        return faultList;
    }

    public static Integer getNextTerminalGatewaySessionId() throws Exception {
        Connection c = DBHelper.getConnection();
        try {
            return DBHelper.getNextSequence(c, "terminal_gateway_session_id");
        } catch (Exception e) {
            logger.warn("Unable to create terminal gateway session id - " + e.getMessage());
            throw e;
        } finally {
            DBHelper.releaseConnection(c);
        }
    }

    public static void createTerminalGatewaySessionEntry(Integer connectionId, int user_id, int customerId,
            String deviceIp, String deviceName, long startTime, String protocol) {
        logger.debug("Creating tg session entry for customerId:" + customerId + ", deviceIp:" + deviceIp);
        PreparedStatement ps = null;
        Connection c = null;
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(
                    "insert into terminal_gateway_sessions (SESSION_ID, CREATOR_ID, CUSTOMER_ID, HOST_IP, HOST_NAME, START_TIME, PROTOCOL) VALUES (?,?,?,?,?,?,?)");
            ps.setInt(1, connectionId);
            ps.setInt(2, user_id);
            ps.setInt(3, customerId);
            ps.setString(4, deviceIp);
            ps.setString(5, deviceName);
            ps.setTimestamp(6, new Timestamp(startTime));
            ps.setString(7, protocol);
            ps.executeUpdate();
        } catch (Exception ex) {
            logger.error("Exception while inserting terminal gateway session for node: " + deviceIp, ex);
        } finally {
            DBHelper.closeAll(c, ps, null);
        }
    }

    public static void storeTerminalGatewaySessionData(Integer connectionId, String data) {
        if (data == null || data.isEmpty()) {
            return;
        }

        ResultSet rs = null;
        String oldData = null;
        try {
            rs = DBHelper.executeQuery(
                    "select SESION_DATA from terminal_gateway_sessions where SESSION_ID=" + connectionId);
            if (rs != null) {
                if (rs.next()) {
                    oldData = rs.getString(1);
                }
            }
        } catch (Exception e) {
            logger.warn("Error loading terminal session data - " + e.getMessage(), e);
        } finally {
            DBHelper.close(rs);
        }

        if (oldData != null) {
            oldData = new StringBuffer(oldData).append(data).toString();
        } else {
            oldData = data;
        }

        PreparedStatement ps = null;
        Connection c = null;
        c = DBHelper.getConnection();

        try {
            ps = c.prepareStatement(
                    "update terminal_gateway_sessions  SET SESION_DATA=? where  SESSION_ID=" + connectionId);

            if (ps instanceof OraclePreparedStatement) {
                ((OraclePreparedStatement) ps).setClob(1,
                        new InputStreamReader(IOUtils.toInputStream(oldData, "UTF-8")));
            } else {
                ps.setString(1, oldData);
            }
            ps.executeUpdate();
        } catch (Exception e) {
            logger.warn("Error with updating terminals session data - " + e.getMessage(), e);
        } finally {
            DBHelper.closeAll(c, ps, null);
        }
    }

    public static String getTerminalGatewaySessionConfig(int sessionId) {
        String selectQuery = "SELECT SESION_DATA FROM TERMINAL_GATEWAY_SESSIONS WHERE SESSION_ID =" + sessionId
                + "";
        ResultSet rs = null;
        String configChanges = null;
        try {
            rs = DBHelper.executeQuery(selectQuery);
            if (rs != null) {
                if (rs.next()) {
                    configChanges = rs.getString("SESION_DATA");
                }
            }

        } catch (SQLException e) {
            logger.warn("Error with retriving terminals session data - " + e.getMessage(), e);

        } finally {
            DBHelper.close(rs);
        }
        return configChanges;
    }

    public static void endTerminalGatewaySession(int id) {
        PreparedStatement ps = null;
        Connection c = null;
        c = DBHelper.getConnection();

        try {
            ps = c.prepareStatement("update terminal_gateway_sessions  SET END_TIME=? where SESSION_ID =" + id);

            ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
            ps.executeUpdate();
        } catch (Exception e) {
            logger.warn("Error with updating terminals session end time - " + e.getMessage(), e);
        } finally {
            DBHelper.closeAll(c, ps, null);
        }
    }

    public static String[] getCustomerShowCommands(String customer) {

        logger.info("Inside getCustomerShowCommands method");
        ArrayList<String> showCommands = new ArrayList<String>();
        PreparedStatement ps = null;

        Connection c = null;
        c = DBHelper.getConnection();

        ResultSet rs = null;
        try {

            ps = c.prepareStatement(
                    "select distinct cli from show_commands_new where device_id in (select distinct a.device_id from customer_device a,customers b where a.customer_id = b.customer_id and  b.customer_name = ? )");
            ps.setString(1, customer);
            rs = ps.executeQuery();

            while (rs.next()) {
                showCommands.add(rs.getString("cli"));
            }
        } catch (Exception ee) {
            logger.error("Exception while getting available CLIs for device: " + customer, ee);
        } finally {
            try {
                DBHelper.closeAll(c, ps, null);
            } catch (Exception e) {
            }
        }

        String[] cmds = new String[showCommands.size()];
        showCommands.toArray(cmds);
        return cmds;
    }

    public static boolean isPostgres() {
        return ServerProperties.getInstance().isPostgres();
    }

    public static void updateJobDevicesDetails(int jobId, String devStr) {
        Connection c = null;
        PreparedStatement ps = null;

        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement("UPDATE  pari_job_addl_details set devices=? where job_id=?");

            ps.setString(1, devStr);
            ps.setInt(2, jobId);
            ps.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (Exception ex) {
            }
            DBHelper.releaseConnection(c);
        }

    }

    public static boolean isJobRunning(int jobId) {
        boolean isJobInRunning = false;
        ResultSet rs = null;
        try {
            rs = DBHelper
                    .executeQuery("select * from PARI_JOB_RUNS where JOB_RUN_STATE='Running' and JOBID=" + jobId);
            if ((rs != null) && rs.next()) {
                isJobInRunning = true;
            }
        } catch (Exception ee) {
            logger.debug("Error while checking if a job is running: ", jobId, ee);
        } finally {
            try {
                rs.close();
            } catch (Exception ee) {
            }
        }
        return isJobInRunning;
    }

    public static Set<String> getDeviceShowRunCommands(List<Integer> selectedDevices) {
        ResultSet rs = null;
        ResultSet rs1 = null;
        Set<String> running_config_command = new HashSet<String>();
        Set<Integer> dbDevices = new HashSet<Integer>();
        try {
            rs = DBHelper.executeQuery("SELECT DISTINCT dev_id FROM running_config_command");
            while (rs.next()) {
                int dev_id = rs.getInt("dev_id");
                if (selectedDevices.contains(dev_id)) {
                    dbDevices.add(dev_id);
                    int index = selectedDevices.indexOf(dev_id);
                    selectedDevices.remove(index);
                }

            }
            if (!dbDevices.isEmpty()) {

                StringBuilder queryBuilder = new StringBuilder(
                        "SELECT DISTINCT CLI FROM running_config_command where dev_id IN (");
                boolean isRemoveLastComma = false;
                for (Integer deviceId : dbDevices) {
                    queryBuilder.append(deviceId + ",");
                    isRemoveLastComma = true;
                }
                if (isRemoveLastComma) {
                    queryBuilder.deleteCharAt(queryBuilder.length() - 1);
                }
                queryBuilder.append(")");

                rs1 = DBHelper.executeQuery(queryBuilder.toString());
                while (rs1.next()) {
                    running_config_command.add(rs1.getString("CLI"));
                }

            }
            // get show run command by mapping of OS type for devices which is not found in running_config_command table
            // incase CP is not collected in NCCM side.
            for (Integer deviceId : selectedDevices) {
                NetworkNode nodeByID = NetworkNodeCache.getInstance().getNodeByID(deviceId);
                if (nodeByID != null) {
                    String command = getCommandForRunningConfig(
                            nodeByID.getVersion().getOsTypeDetails().getShortDesc());
                    if (command != null && !command.isEmpty()) {
                        running_config_command.add(command);
                    } else {
                        running_config_command.add("show running-config");
                    }
                }
            }

        } catch (Exception ee) {
            logger.debug("Error while getting running config Command Of Devices", ee);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(rs1);
        }
        return running_config_command;
    }
}