public class DbManager {

    protected Session session = null;    
    protected Logger log = null;    
    protected DbManager()

            throws DbException { 

        this.log = Logger.getLogger(this.getClass());

        try {
            // This step will read hibernate.cfg.xml             
            //    and prepare hibernate for use
            SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();            
            session = sessionFactory.openSession();
        }
        catch (HibernateException he) {                    
            he.printStackTrace();
            throw new DbException("Failed to initialize DbManager", he);
        }              
    }


    public static DbManager createDbManager(TypedProperties props) 
            throws DbException { 

        DbManager dbm = null;

        if (props.isNoDb() || props.isMailDryRun() ||  props.isMailDryRun2()) {
            dbm = new ReadOnlyDbManager();            
        }    
        else {
            dbm = new DbManager();
        }
        return dbm;
    }


    protected Object loadLongObject (Class<?> clazz,  long objId) 
            throws DbException { 

        Object obj = session.get(clazz, objId);

        if (obj == null) {
            String msg = ("Invalid object reference: " + objId + ".");
            log.error(msg);
            throw new DbException(msg);
        }
        return obj;
    }

    protected Object loadIntegerObject (Class<?> clazz,  int objId) 
            throws DbException { 

        Object obj = session.get(clazz, objId);

        if (obj == null) {
            String msg = ("Invalid object reference: " + objId + ".");
            log.error(msg);
            throw new DbException(msg);
        }
        return obj;
    }


    public Object genericLoadObject(String className, String[] searchFields, Object[] values)
            throws DbException { 

        if (searchFields.length != values.length) {
            throw new DbException("Number of search fields and number of values are not maching");
        }
        if (searchFields.length < 1) {
            throw new DbException("Search fields are empty");
        }

        Object obj = null;

        String hql = "from " + className + " where ";                

        try {

            for (int i=0; i<searchFields.length; i++) {            
                hql = hql + ( i == 0 ? searchFields[i] + "= ?" :  " AND " +  searchFields[i]  + "= ?");
            }
            Query query = session.createQuery(hql);

            for (int i=0; i<searchFields.length; i++) {

                String typeName = values[i].getClass().getSimpleName();

                Method method = null;
                if (typeName.equals("Integer")) {
                    query.setInteger(i, ((Integer)values[i]).intValue());
                }
                else if (typeName.equals("Long")) {
                    query.setLong(i, ((Long)values[i]).longValue());
                }
                else {
                    method = query.getClass().getMethod("set" + typeName, Integer.TYPE, values[i].getClass());
                    Object[] args = {i, values[i]};
                    method.invoke(query, args);
                }                
            }                       
            obj = query.uniqueResult();

        } catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in genericLoadObject", e);
        }

        return obj;
    }



    public Object genericLoadObject(String className, String searchField, Object value)
            throws DbException { 

        Object obj = null;

        Query query = session.createQuery("from " + className + " where " + searchField + "= ?");

        String typeName = value.getClass().getSimpleName();

        try {

            Method method = null;
            if (typeName.equals("Integer")) {
                query.setInteger(0, ((Integer)value).intValue());
            }
            else if (typeName.equals("Long")) {
                query.setLong(0, ((Long)value).longValue());
            } 
            else {
                method = query.getClass().getMethod("set" + typeName, Integer.TYPE, value.getClass());
                Object[] args = {0, value};
                method.invoke(query, args);
            }            

            obj = query.uniqueResult();

        } catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in genericLoadObject", e);
        }

        return obj;

    }

    public void addPriority(Priority prio) 
            throws DbException { 

        try {                
            session.saveOrUpdate(prio);
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in addPriority", e);
        }
    }


    public void addEntryType(EntryType entryType) 
            throws DbException { 
        try {                    
            session.saveOrUpdate(entryType);
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in addEntryType", e);
        }
    }


    public void addLogEntry(LogEntry logEntry) 
            throws DbException { 
        try {                
            session.save(logEntry);
            session.flush();
            session.evict(logEntry);
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in addLogEntry", e);
        }
    }


    public void updateLogEntry(LogEntry logEntry) 
            throws DbException { 
        try {                
            session.update(logEntry);
            session.flush();
            session.evict(logEntry);
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in updateLogEntry", e);
        }
    }


    @SuppressWarnings("unchecked")
    public List<LogEntry> listLogEntries(long logJobId, int startIndex,
            int noOfRecords) 
                    throws DbException { 
        try {
            Query query = session.createQuery("from LogEntry where Job.Id = ? order by Id asc");
            query.setLong(0, logJobId);
            query.setMaxResults(noOfRecords);
            query.setFirstResult(startIndex);
            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in listLogEntries", e);
        }
    }


    @SuppressWarnings("unchecked")
    public List<LogEntry> listLogEntries(long logJobId, boolean usePrimaryOrg, int startIndex,
            int noOfRecords) 
                    throws DbException { 

        try {
            Query query = null;

            if (usePrimaryOrg) {
                query = session.createQuery("from LogEntry where Job.Id = ? order by Organization, Id asc");
            }
            else {
                query = session.createQuery("from LogEntry where Job.Id = ? order by Organization2, Id asc");
            }
            query.setLong(0, logJobId);
            query.setMaxResults(noOfRecords);
            query.setFirstResult(startIndex);

            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in listLogEntries", e);
        }
    }


    /**
     * Returns high priority entries for specified job. Both primary and 
     * secondary organization is checked (org_id and org_id2).
     * 
     * Note, this method contains MySQL specific query parameters.
     * Must be checked if another database is used.
     * 
     * @param prio log entries linked to an organization with a priority equals
     *     or above this value will be included in the result. 
     * 
     * @return High priority entries, sorted by prio.
     */

    @SuppressWarnings({ "deprecation" })
    public List<LogEntry> listLogEntries(long logJobId, int prio, int startIndex, int noOfRecords) throws DbException {

        try {            
            ArrayList<LogEntry> entries = new ArrayList<LogEntry>();

            String sql = "select distinct l.id from log_entry l, organization o, prio p where l.job_id = ? and (l.org_id = o.id or l.org_id2 = o.id) and o.prio_id = p.id and p.prio >= ? order by prio desc limit ?, ?;";    
            PreparedStatement ps = session.connection().prepareStatement(sql);
            ps.setLong(1, logJobId);
            ps.setInt(2, prio);
            ps.setInt(3, startIndex);
            ps.setInt(4, startIndex + noOfRecords);

            boolean result = ps.execute();
            if (result) {
                while (ps.getResultSet().next()) {                        
                    long id = ps.getResultSet().getLong("id");
                    LogEntry entry = (LogEntry)this.loadLongObject(LogEntry.class, id);
                    entries.add(entry);                        
                }                             
            }
            ps.close();     

            return entries;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in listLogEntries", e);
        }                               
    }


    /**
     * Returns the log entry with the latest "created" timestamp, where 
     * ipAddress is equal to specified IP. Null is returned if not found.
     * Only ipAddress is checked, not ipAddress2 or ipRange.
     * 
     * @param ip ip-address for the log entry.
     * @param jobId log entries for this job will be ignored. 
     */
    @SuppressWarnings("deprecation")
    public LogEntry getLastSeenLogEntry(long ip, long jobId) throws DbException {

        LogEntry entry = null;

        try {
            String sql = "select id from log_entry where ip_address = ? and job_id != ? order by created desc";    
            PreparedStatement ps = session.connection().prepareStatement(sql);
            ps.setLong(1, ip);            
            ps.setLong(2, jobId);
            boolean result = ps.execute();
            if (result) {
                if (ps.getResultSet().first()) {
                    long id = ps.getResultSet().getLong("id");
                    entry = (LogEntry)this.loadLongObject(LogEntry.class, id);
                }                
            }
            ps.close();     
        }        
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getLastSeenLogEntry", e);
        }
        return entry;
    }


    /**
     * Returns the mail job with the latest "started" timestamp, which have
     * mailed specified IP. Null is returned if not found.
     * Only ipAddress is checked, not ipAddress2 or ipRange.
     */
    @SuppressWarnings("deprecation")
    public MailJob getLastSeenMailJob(long ip) throws DbException {

        MailJob mailJob = null;

        try {
            String sql = "select mj.id from mail_job mj, mail_job_log_entry_mapping mjlem, log_entry le " + 
                    "where mjlem.log_entry_id = le.id and le.ip_address = ? and mjlem.mail_job_id = mj.id order by mj.started desc";

            PreparedStatement ps = session.connection().prepareStatement(sql);
            ps.setLong(1, ip);                   
            boolean result = ps.execute();
            if (result) {
                if (ps.getResultSet().first()) {
                    long id = ps.getResultSet().getLong("id");
                    mailJob = (MailJob)this.loadLongObject(MailJob.class, id);
                }                
            }
            ps.close();     
        }       
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getLastSeenMailJob", e);
        }
        return mailJob;
    }


    public void addOrganization(Organization org, String modifiedBy) 
            throws DbException { 
        try {
            java.util.Date date = new java.util.Date();
            long time = SqlUtil.convertTimestamp(date);        
            org.setLastModified(time);
            org.setModifiedBy(modifiedBy);
            if (org.getCreated() == null) {
                org.setCreated(time);            
            }      
            session.save(org);
            session.flush();
        } 

        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " not possible to add organiation", e);
        }
    }



    public void updateOrganization(Organization org, String modifiedBy) 
            throws DbException { 
        try {
            java.util.Date date = new java.util.Date();
            long time = SqlUtil.convertTimestamp(date);        
            org.setLastModified(time);
            org.setModifiedBy(modifiedBy);
            session.update(org);
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in updateOrganization", e);
        }
    }


    @SuppressWarnings("unchecked")
    public List<Organization> searchOrganizations(String name, int startIndex,
            int noOfRecords) 
                    throws DbException { 
        try {
            Query query = session.createQuery("from Organization where name like ?");
            query.setString(0, "%" + name +"%");

            query.setMaxResults(noOfRecords);
            query.setFirstResult(startIndex);

            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchOrganizations", e);
        }
    }


    public Organization searchOrganization(String name) 
            throws DbException { 
        try {
            Query query = session.createQuery("from Organization where name = ?");
            query.setString(0, name);

            Organization org = (Organization)query.uniqueResult();

            return org;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchOrganization", e);
        }

    }


    @SuppressWarnings("unchecked")
    public List<Organization> listOrganizations(int startIndex, int noOfRecords) 
            throws DbException { 

        try {
            Query query = session.createQuery("from Organization order by Name asc");        
            query.setMaxResults(noOfRecords);
            query.setFirstResult(startIndex);                

            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in listOrganizations", e);
        }

    }


    public Organization getOrganization(int orgId) 
            throws DbException { 

        try {
            Organization org = (Organization)loadIntegerObject(Organization.class, orgId);

            return org;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getOrganization", e);
        }

    }


    public void addMailJob(MailJob mailJob) 
            throws DbException { 

        try {
            mailJob.setStarted(SqlUtil.convertTimestamp(new Date()));
            session.saveOrUpdate(mailJob);
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in addMailJob", e);
        }
    }


    /*        
        The updateMailJob is deprecated due to a problem when persisting the MailJob object after
        a change in the LogEntry collection. The reason could be due to some missconfiguration in the
        Hibernate object mapping for MailJob or a bug in Hibernate. 
        Use finishMailJob to persist the MailJob once it has been completed.      
     */
    @Deprecated
    public void updateMailJob(MailJob mailJob) 
            throws DbException { 

        try {
            session.update(mailJob);        
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in updateMailJob", e);
        }
    }


    public void finishMailJob(MailJob mailJob, String errorMsg) 
            throws DbException { 

        try {
            mailJob.setErrorMsg(errorMsg);
            mailJob.setFinished(SqlUtil.convertTimestamp(new Date()));
            session.update(mailJob);            
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in finishMailJob", e);
        }
    }


    public long deleteMailJob(MailJob mailJob) 
            throws DbException { 

        try {
            Query query = session.createSQLQuery(
                    "SELECT COUNT(*) FROM mail_job_log_entry_mapping WHERE mail_job_id = ?");
            query.setLong(0, mailJob.getId());
            long noLogEntriesInJob = ((BigInteger)query.list().get(0)).longValue();

            session.delete(mailJob);
            session.flush();

            long entriesLeft = ((BigInteger)query.list().get(0)).longValue();

            return noLogEntriesInJob - entriesLeft;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in deleteMailJob", e);
        }
    }


    // Search MailJob by time frame
    @SuppressWarnings("unchecked")
    public List<MailJob> searchMailJobs(Date startTime,
            Date endTime, int startIndex, int noOfRecords) 
                    throws DbException { 
        try {
            Query query = session.createQuery("from MailJob where started >= ? and started <= ? order by Id asc");
            query.setLong(0, SqlUtil.convertTimestamp(startTime));
            query.setLong(1, SqlUtil.convertTimestamp(endTime));
            query.setMaxResults(noOfRecords);
            query.setFirstResult(startIndex);

            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchMailJobs", e);
        }
    }


    // Search MailJob by Job entity
    @SuppressWarnings("unchecked")
    public List<MailJob> searchMailJobs(Job job) 
            throws DbException { 

        try {
            Query query = session.createQuery("from MailJob where Job = ?");
            query.setEntity(0, job);

            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchMailJobs", e);
        }
    }

    
    // Search MailJob by log entry
    public MailJob searchMailJob(LogEntry entry)
        throws DbException {
        
        try {
            Query query = session.createSQLQuery("select mail_job_id from mail_job_log_entry_mapping where log_entry_id = ?");
            query.setLong(0, entry.getId());
          
            Object result = query.uniqueResult();
            MailJob mailJob = null;
            if (result != null) {
                Long mailJobId = Long.valueOf(result.toString());
                mailJob = (MailJob)this.loadLongObject(MailJob.class, mailJobId.longValue());
            }    
            return mailJob;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchMailJob", e);
        }
    }


    public void addJobType(JobType jobType) 
            throws DbException { 

        try {                
            session.saveOrUpdate(jobType);
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in addJobType", e);
        }
    }

    public void addLogJob(Job job) 
            throws DbException {

        try {
            session.save(job);
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in addLogJob", e);
        }
    }

    public boolean existsFileHash(String hash)
            throws DbException {

        try {
            Query query = session.createQuery("from Job where FileHash = ?");
            query.setString(0, hash);

            return query.list().size() > 0;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in existsFileHash", e);
        }
    }

    @SuppressWarnings("unchecked")
    public List<Job> searchLogJobs(Date startTime, Date endTime, int startIndex,
            int noOfRecords) 
                    throws DbException { 

        try {

            Query query = session.createQuery("from Job where started >= ? and started <= ? order by Id asc");
            query.setLong(0, SqlUtil.convertTimestamp(startTime));
            query.setLong(1, SqlUtil.convertTimestamp(endTime));
            query.setMaxResults(noOfRecords);
            query.setFirstResult(startIndex);

            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchLogJobs", e);
        }

    }                           


    public Job searchLogJob(String name)
            throws DbException { 

        try {
            Query query = session.createQuery("from Job where name = ?");
            query.setString(0, name);

            return (Job)query.uniqueResult();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchLogJob", e);
        }

    } 


    public void finishLogJob(Job job, String errorMsg) 
            throws DbException { 

        try {
            job.setFinished(SqlUtil.convertTimestamp(new Date()));
            job.setErrorMsg(errorMsg);
            session.update(job);
            session.flush();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in finishLogJob", e);
        }

    }


    @SuppressWarnings("deprecation")
    public long deleteLogJob(Job job) 
            throws DbException {

        long jobId = job.getId();        


        // Use JDBC connection to delete additional_item, free_text & original_log_entry
        try {

            // additional_item
            String sqlDelete = "delete from additional_item where log_entry_id in (select id from log_entry where job_id = ?)";       
            PreparedStatement ps = session.connection().prepareStatement(sqlDelete);
            ps.setLong(1, jobId);
            ps.execute();
            ps.close();        

            // free_text
            sqlDelete = "delete from free_text where log_entry_id in (select id from log_entry where job_id = ?)";
            ps = session.connection().prepareStatement(sqlDelete);
            ps.setLong(1, jobId);
            ps.execute();
            ps.close();

            // original_log_entry
            sqlDelete = "delete from original_log_entry where id in (select id from log_entry where job_id = ?)";
            ps = session.connection().prepareStatement(sqlDelete);
            ps.setLong(1, jobId);
            ps.execute();
            ps.close();

        }
        catch(SQLException sqle) {
            throw handleException("SQL exception in deleteLogJob", sqle);
        }


        try {
            // Delete LogEntry using hql
            String hqlDelete = "delete LogEntry where Job.Id = ?";
            long  numberOfDeletedLogEntries = session.createQuery( hqlDelete ).setLong(0, job.getId()).executeUpdate();

            session.evict(job);
            Job realJob = (Job)loadLongObject(job.getClass(), jobId);

            // Delete Job
            session.delete(realJob);        
            session.flush();


            return numberOfDeletedLogEntries;
        }

        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in deleteLogJob", e);
        }

    }


    public JobType searchJobType(String name) 
            throws DbException { 

        try {
            Query query = session.createQuery("from JobType where name = ?");
            query.setString(0, name);

            return (JobType)query.uniqueResult();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchJobType", e);
        }

    }


    public EntryType searchEntryType(String name) 
            throws DbException { 

        try {

            Query query = session.createQuery("from EntryType where name = ?");
            query.setString(0, name);

            return (EntryType)query.uniqueResult();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchEntryType", e);
        }

    }


    public Priority getPriority(int prio)
            throws DbException { 
        try {
            Query query = session.createQuery("from Priority where prio = ?");
            query.setInteger(0, prio);

            return (Priority)query.uniqueResult();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getPriority", e);
        }

    }


    @SuppressWarnings("unchecked")
    public List<Priority> getAllPriorities()
            throws DbException { 

        try {
            Query query = session.createQuery("from Priority order by Prio desc");


            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getAllPriorities", e);
        }

    }


    @SuppressWarnings("unchecked")
    public List<IpRange> getAllIpRanges(boolean includeDisabledOrgs)
            throws DbException { 

        try {

            Query query = null;
            if (includeDisabledOrgs) {
                query = session.createQuery("from IpRange");
            }
            else {
                query = session.createQuery("from IpRange where OrganizationId in (from Organization where Enabled = ?)");
                query.setBoolean(0, true);
            }

            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getAllIpRanges", e);
        }

    }


    @SuppressWarnings("unchecked")
    public List<ASNumber> getAllASNumbers(boolean includeDisabledOrgs)
            throws DbException { 

        try {

            Query query = null;
            if (includeDisabledOrgs) {
                query = session.createQuery("from ASNumber");
            }
            else {
                query = session.createQuery("from ASNumber where OrganizationId in (from Organization where Enabled = ?)");
                query.setBoolean(0, true);
            }

            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getAllASNumbers", e);
        }

    }


    @SuppressWarnings("unchecked")
    public List<DomainName> getAllDomainNames(boolean includeDisabledOrgs)
            throws DbException { 

        try {

            Query query = null;
            if (includeDisabledOrgs) {
                query = session.createQuery("from DomainName");
            }
            else {
                query = session.createQuery("from DomainName where OrganizationId in (from Organization where Enabled = ?)");
                query.setBoolean(0, true);
            }
            return query.list();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getAllDomainNames", e);
        }

    }

    // Gets the organization with the given domain name
    public Organization getOrgByDomainName(String domainName)
            throws DbException { 

        try {

            Organization org = null;
            // Ugly to step search...
            Query query = session.createQuery("from DomainName where Name = ?");
            query.setString(0, domainName);

            DomainName dName = (DomainName) query.uniqueResult();

            if (dName != null) {
                org = (Organization)loadIntegerObject(Organization.class, dName.getOrganizationId());               
            }
            return org;

        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchOrgByDomainName", e);
        }

    }

    // Searches for organizations with domain names like the input name
    public List<Organization> searchOrgForDomainName(String domainName)
            throws DbException { 

        try {                       
            //Query query = session.createQuery("OrganizationId from DomainName where Name like '%?%'");
            //query.setString(0, domainName);

            String sql = "SELECT DISTINCT org_id FROM domain_name WHERE domain_name LIKE ? ORDER BY org_id ASC";            
            Query query = session.createSQLQuery(sql);
            query.setString(0, "%" + domainName + "%");
            @SuppressWarnings("unchecked")
            List<Object> orgIDs = query.list();
            List<Organization> orgs = new ArrayList<Organization>();    

            Iterator<Object> itr = orgIDs.iterator();           
            while (itr.hasNext()) { 
                Organization org = getOrganization(((Integer)itr.next()).intValue());
                if (org != null) {
                    orgs.add(org);
                }               
            }           
            return orgs;            
            
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchOrgForDomainName", e);
        }        
    }
    
    
    public List<Organization> searchOrganizationByPrioNumber(Integer prioNumber) 
            throws DbException { 
        
        try { 
            String sql = "SELECT DISTINCT o.id FROM organization o, prio p WHERE o.prio_id = p.id and p.prio = ? ORDER BY id ASC";
            Query query = session.createSQLQuery(sql);
            query.setInteger(0, prioNumber);
            
            @SuppressWarnings("unchecked")
            List<Object> orgIDs = query.list();
            List<Organization> orgs = new ArrayList<Organization>();    

            Iterator<Object> itr = orgIDs.iterator();           
            while (itr.hasNext()) { 
                Organization org = getOrganization(((Integer)itr.next()).intValue());
                if (org != null) {
                    orgs.add(org);
                }               
            }           
            return orgs;            
            
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchOrgByPrioNumber", e);
        }  
    }

    public Organization searchOrganizationByASNumber(int asn)
            throws DbException { 

        try {

            Organization org = null;
            // Ugly two step search...
            Query query = session.createQuery("from ASNumber where Number = ?");
            query.setInteger(0, asn);

            ASNumber asnum = (ASNumber) query.uniqueResult();

            if (asnum != null) {
                org = (Organization)loadIntegerObject(Organization.class, asnum.getOrganizationId());               
            }
            return org;

        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchOrgByASNumber", e);
        }

    }


    @SuppressWarnings("unchecked")
    public List<Organization> searchOrganizationsByIPrange(long start, long end) 
            throws DbException { 
        try {
            String sql = "SELECT DISTINCT org_id FROM ip_range WHERE (end_address >= ? AND end_address <= ?) OR (start_address <=  ? AND end_address >= ?) OR  (start_address >= ? AND start_address <= ?) OR (start_address >= ? AND end_address <= ?) ORDER BY org_id ASC";

            Query query = session.createSQLQuery(sql);
            query.setLong(0, start);   
            query.setLong(1, end);
            query.setLong(2, start);
            query.setLong(3, end);
            query.setLong(4, start);
            query.setLong(5, end);
            query.setLong(6, start);
            query.setLong(7, end);


            List<Object> orgIDs = query.list();
            List<Organization> orgs = new ArrayList<Organization>();    

            Iterator<Object> itr = orgIDs.iterator();           
            while (itr.hasNext()) { 
                Organization org = getOrganization(((Integer)itr.next()).intValue());
                if (org != null) {
                    orgs.add(org);
                }               
            }           
            return orgs;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchOrgsByIPrange", e);
        }           
    }   


    @SuppressWarnings("unchecked")
    public List<LogEntry> searchLogEntriesByIPrange(long start, long end) 
            throws DbException { 
        try {
            //String sql = "SELECT id FROM log_entry where (ip_address >= ? AND ip_address <= ?) OR (ip_address2 >= ? AND ip_address2 <= ?)";
            Query query = session.createQuery("from LogEntry where (IpAddress >= ? AND IpAddress <= ?) OR (IpAddress2 >= ? AND IpAddress2 <= ?)");

            query.setLong(0, start);   
            query.setLong(1, end);
            query.setLong(2, start);
            query.setLong(3, end);


            /*List<Object> entryIDs = query.list();
                List<LogEntry> entries = new ArrayList<LogEntry>(); 

                Iterator<Object> itr = entryIDs.iterator();         
                while (itr.hasNext()) {                                 
                    LogEntry entry = (LogEntry)loadLongObject(LogEntry.class, ((Long)itr.next()).longValue());                                  

                    if (entry != null) {
                        entries.add(entry);
                    }               
                }           */
            return query.list();
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchLogEntriesByIPrange", e);
        }           
    }   

    @SuppressWarnings("unchecked")
    public List<LogEntry> searchLogEntriesByOrgId(int orgId)            
            throws DbException { 
        try {
            Query query = session.createQuery("from LogEntry where Organization = ?");
            query.setInteger(0, orgId);

            return query.list();
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchLogEntriesByOrgId", e);
        }       
    }

    @SuppressWarnings("unchecked")
    public List<LogEntry> searchLogEntriesByASNumber(long asn) 
            throws DbException { 
        try {

            Query query = session.createQuery("from LogEntry where Asn = ? or Asn2 = ?");           
            query.setLong(0, asn);   
            query.setLong(1, asn);

            return query.list();
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchLogEntriesByASNumber", e);
        }           
    }   


    @SuppressWarnings("unchecked")
    public List<Organization> searchOrganizationByEmailAddress(String address) 
            throws DbException {

        List<Organization> orgs = new ArrayList<Organization>();

        try {
            Query query = session.createQuery("from Contact where EmailAddress like ?");
            query.setString(0, "%" + address +"%");
            List<Contact> contacts = query.list();
            for (Contact contact: contacts) {   
                Organization org = (Organization)loadIntegerObject(Organization.class, contact.getOrganizationId());                
                if (orgs.contains(org) == false) {              
                    orgs.add(org);
                }
            }       
            return orgs;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in searchOrganizationByEmailAddress", e);
        }

    }


    public long getNumberOfLogEntries()
            throws DbException { 
        try {

            Query query = session.createSQLQuery(
                    "SELECT COUNT(*) FROM log_entry");

            return ((BigInteger)query.list().get(0)).longValue();
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfLogEntries", e);
        }

    }


    public boolean existsMailForIp(long ip, Organization organization, long periodInSecs, boolean usePrimaryOrg)
            throws DbException { 

        try {        
            String orgIdColName = "org_id";
            String ipAddrColName = "ip_address";
            if (usePrimaryOrg != true) {
                orgIdColName = "org_id2";
                ipAddrColName = "ip_address2";
            }

            String select = "SELECT COUNT(*) FROM log_entry le, mail_job_log_entry_mapping map, mail_job mj where mj.started >= ? AND mj.finished <= ? AND mj.id = map.mail_job_id AND map.log_entry_id = le.id AND le." + orgIdColName + " = ? AND le." + ipAddrColName + " = ?";                  
            Query query = session.createSQLQuery(select); 

            long endTime = SqlUtil.convertTimestamp(new Date());        
            long startTime = endTime - periodInSecs;

            query.setLong(0, startTime);
            query.setLong(1, endTime);    
            query.setInteger(2, organization.getId());
            query.setLong(3, ip);

            return ((BigInteger)query.list().get(0)).intValue() > 0 ? true:false;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in existsMailForIp", e);
        }

    }


    public DbStatisticsData fetchStatistics(Date startDate, Date endDate)
            throws DbException { 
        try {

            DbStatisticsData dbStats = new DbStatisticsData(startDate, endDate);

            dbStats.setNoOfSuccessfulJobs(getNumberOfSuccessfulLogJobs(startDate, endDate));
            dbStats.setNoOfFailedJobs(getNumberOfFailedLogJobs(startDate, endDate));
            dbStats.setNoOfProcessedLines(getNumberOfProcessedLines(startDate, endDate));
            dbStats.setNoOfLogEntries(getNumberOfLogEntries(startDate, endDate));
            dbStats.setNoOfMatchedLogEntries(getNumberOfMatchedLogEntries(startDate, endDate));
            dbStats.setNoOfSentEmails(getNumberOfSentEmails(startDate, endDate));        
            dbStats.setNoOfNewOrganizations(getNumberOfNewOrganizations(startDate, endDate));
            dbStats.setNoOfModifiedOrganizations(getNumberOfModifiedOrganizations(startDate, endDate));

            return dbStats;

        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in fetchStatistics", e);
        }

    }

    /**
     * Fetches number of processed lines for successful jobs per job type.
     * 
     * @return Map with result, where key is job type and value is no. 
     *     of processed lines.
     */
    @SuppressWarnings("unchecked")
    public Map<String, Long> fetchNoOfProcessedLinesPerEntryType(Date startDate, Date endDate) 
            throws DbException { 
        try {

            Map<String, Long> result = new HashMap<String, Long>();

            String select = "SELECT et.name, sum(j.processed_lines) FROM job j, job_type jt, entry_type et WHERE job_type_id = jt.id AND jt.entry_type_id = et.id AND finished >= ? AND finished <= ? group by et.name;";

            Query query = session.createSQLQuery(select);        
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            List<Object[]> resultSet = query.list();

            if (resultSet != null) {
                for (Object[] obj: resultSet) {                
                    result.put(new String(obj[0].toString()), new Long(((java.math.BigDecimal)obj[1]).longValue()));                
                }
            }    

            return result;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in fetchNoOfProcessedLinesPerJobType", e);
        }
    }


    /**
     * Fetches number of log entries for successful jobs per job type.
     * 
     * @param onlyMatchedEntries If true, count only log entries where organization and 
     *     organization2 is not null. If false, count all log entries. 
     * 
     * @return Map with result, where key is job type and value is no. 
     *     of processed lines.
     */
    @SuppressWarnings("unchecked")
    public Map<String, Long> fetchNoOfLogEntriesPerEntryType(Date startDate, Date endDate, boolean onlyMatchedEntries) 
            throws DbException { 

        try {

            Map<String, Long> result = new HashMap<String, Long>();

            String select = "SELECT et.name, count(le.id) FROM log_entry le, job j, job_type jt, entry_type et WHERE le.job_id = j.id AND j.job_type_id = jt.id AND jt.entry_type_id = et.id AND j.finished >= ? AND j.finished <= ?";

            if (onlyMatchedEntries) {
                select = select + " AND (le.org_id IS NOT null OR le.org_id2 IS NOT null)";
            }
            select = select + " GROUP BY jt.entry_type_id";

            Query query = session.createSQLQuery(select);        
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            List<Object[]> resultSet = query.list();

            if (resultSet != null) {
                for (Object[] obj: resultSet) {                
                    result.put(new String(obj[0].toString()), new Long(((BigInteger)obj[1]).longValue()));                
                }
            }    

            return result;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in fetchNoOfLogEntriesPerJobType", e);
        }

    }


    /**
     * Fetches log entries for use in GeolocationXmlReportGenerator. Hibernate 
     * is not used due to memory constraints and complexity of the query.  
     * 
     * @param jobTypeKillList list if job types to exclude (the db-field "job_type.name").
     */
    @SuppressWarnings("deprecation")
    public List<LogEntry> fetchLogEntriesForGeolocation(Date startDate, Date endDate, List<String> jobTypeKillList) throws DbException {
        List<LogEntry> result = new ArrayList<LogEntry>(2048);
        PreparedStatement ps = null;
        try {
            // -- build query
            String queryStr = 
                    "select log_entry.id, log_timestamp, log_entry.created, ip_address, ip_range_start, port, hostname, asn, prio.name, additional_item.name, additional_item.value " +
                            "from (log_entry, job, job_type) " +
                            "left join additional_item on log_entry.id = additional_item.log_entry_id " +
                            "left join organization on log_entry.org_id = organization.id " +
                            "left join prio on organization.prio_id = prio.id " + 
                            "where " +
                            "log_entry.log_timestamp >= ? and log_entry.log_timestamp <= ? and " +
                            "log_entry.job_id = job.id and " +
                            "job.job_type_id = job_type.id and " +
                            "job_type.name not in (@inList@) " + 
                            "order by log_entry.log_timestamp, log_entry.id asc;";
            if ((jobTypeKillList == null) || jobTypeKillList.isEmpty()) {
                jobTypeKillList = new ArrayList<String>();
                jobTypeKillList.add("");
            }
            String inList = StringUtil.toSingleQuotedString(jobTypeKillList);
            queryStr = StringUtil.replace(queryStr, "@inList@", inList);

            // -- execute query
            log.debug("Executing query: " + queryStr);
            long t1 = System.currentTimeMillis();
            ps = session.connection().prepareStatement(queryStr);
            ps.setLong(1, SqlUtil.convertTimestamp(startDate));
            ps.setLong(2, SqlUtil.convertTimestamp(endDate));
            boolean successful = ps.execute();
            String durationStr = DateUtil.formatDuration(System.currentTimeMillis() - t1);
            log.debug("Query executed. Time: " + durationStr);

            // -- read result set; only one LogEntry per ip-address.
            final String dbIdKey = "dbId";
            final String timesSeenKey = "timesSeen";
            final String lastSeenKey = "lastSeen";
            final String prioNameKey = "prioName";
            final String infectionKey = "infection";
            if (successful) {
                long id = 0L;
                Map<Long, LogEntry> ipAddressMap = new HashMap<Long, LogEntry>();
                ResultSet resultSet = ps.getResultSet(); 
                while (resultSet.next()) {
                    Long ipAddress = resultSet.getLong(4);
                    if (ipAddress == null) {
                        ipAddress = resultSet.getLong(5);
                        if (ipAddress == null) {
                            continue;
                        }
                    }
                    LogEntry logEntry = ipAddressMap.get(ipAddress);
                    if (logEntry != null) {
                        int timesSeen = Integer.parseInt(logEntry.getAdditionalItems().get(timesSeenKey)); 
                        logEntry.getAdditionalItems().put(timesSeenKey, Integer.toString(++timesSeen));
                        long lastSeen = Long.parseLong(logEntry.getAdditionalItems().get(lastSeenKey));
                        long maxLastSeen = Math.max(lastSeen, resultSet.getLong(2));
                        logEntry.getAdditionalItems().put(lastSeenKey, Long.toString(maxLastSeen));
                    } 
                    else {
                        logEntry = new LogEntry(id++);
                        ipAddressMap.put(ipAddress, logEntry);
                        result.add(logEntry);
                        if (logEntry.getAdditionalItems() == null) {
                            logEntry.setAdditionalItems(new HashMap<String, String>());
                        }
                        logEntry.setIpAddress(ipAddress);
                        // logTimestamp == firstSeen
                        logEntry.setLogTimestamp(resultSet.getLong(2));
                        logEntry.setCreated(resultSet.getLong(3));
                        logEntry.setPort(resultSet.getInt(6));
                        logEntry.setHostname(resultSet.getString(7));
                        logEntry.setAsn(resultSet.getLong(8));
                        logEntry.getAdditionalItems().put(dbIdKey, Long.toString(resultSet.getLong(1))); 
                        logEntry.getAdditionalItems().put(timesSeenKey, "1");
                        logEntry.getAdditionalItems().put(lastSeenKey, logEntry.getLogTimestamp().toString());
                        logEntry.getAdditionalItems().put(prioNameKey, StringUtil.getNotNull(resultSet.getString(9)));
                    }
                    // add infection if missing
                    String infection = logEntry.getAdditionalItems().get(infectionKey);
                    if (StringUtil.isNullOrEmpty(infection)) {
                        infection = null;
                        String name = resultSet.getString(10);
                        if ((name != null) && name.equals(infectionKey)) {
                            infection = resultSet.getString(11);
                            if ((infection != null) && (infection.equals("irc") || infection.equalsIgnoreCase("sinkhole") || infection.equalsIgnoreCase("other"))) {
                                infection = null;
                            }
                        }
                        logEntry.getAdditionalItems().put(infectionKey, StringUtil.getNotNull(infection));
                    }
                }
            }
        } catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in fetchLogEntriesForGeolocation", e);
        } finally {
            try { if (ps != null) ps.close(); } catch (SQLException ignored) { }
        }

        return result;
    }


    /**
     * Fetches log entries for specified organization for use in OrganizationReportGenerator.
     * 
     * @param jobTypes list of job types (db-field "job_type.name") for log entries to be included in the result.
     */
    @SuppressWarnings("deprecation")
    public List<LogEntry> fetchLogEntriesForOrganizationReport(int orgId, Date startDate, Date endDate, List<String> jobTypes) 
            throws DbException {
        List<LogEntry> entries = new ArrayList<LogEntry>(2048);
        PreparedStatement ps = null;
        if ((jobTypes == null) || jobTypes.isEmpty()) {
            return entries;
        }

        try {
            String inList = StringUtil.toSingleQuotedString(jobTypes);
            String sql = 
                    "SELECT le.id from log_entry le, job j, job_type jt " +
                            "WHERE j.finished >= ? AND j.finished <= ? AND j.job_type_id = jt.id AND jt.name in (" + inList + ") AND le.job_id = j.id AND le.org_id = ?;";

            log.debug("Executing query: " + sql);
            ps = session.connection().prepareStatement(sql);            
            ps.setLong(1, SqlUtil.convertTimestamp(startDate));
            ps.setLong(2, SqlUtil.convertTimestamp(endDate));
            ps.setInt(3, orgId);
            boolean successful = ps.execute();

            if (successful) {
                ResultSet resultSet = ps.getResultSet(); 
                while (resultSet.next()) {
                    long entryID = resultSet.getLong(1);
                    LogEntry entry = (LogEntry)this.loadLongObject(LogEntry.class, entryID);
                    entries.add(entry);
                }
            }
        } catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in fetchLogEntriesForOrganizationReport", e);
        } finally {
            try { if (ps != null) ps.close(); } catch (SQLException ignored) { }
        }

        return entries;
    }


    public long getNumberOfSuccessfulLogJobs(Date startDate, Date endDate) 
            throws DbException {
        try {
            /** No. of successful log jobs (excluding mail jobs). */
            String sql = "SELECT COUNT(*) FROM job WHERE finished >= ? AND finished <= ? AND error_msg IS NULL";
            Query query = session.createSQLQuery(sql);

            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            return (query.list() != null) ? ((BigInteger)query.list().get(0)).longValue() : 0;
        } 
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfSuccessfulLogJobs", e);
        }

    }


    public long getNumberOfFailedLogJobs(Date startDate, Date endDate) 
            throws DbException {
        try {    
            /** No. of failed log jobs (excluding mail jobs). */
            String sql = "SELECT COUNT(*) FROM job WHERE started >= ? AND started <= ? AND finished is null";
            Query query = session.createSQLQuery(sql);
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            return (query.list() != null) ? ((BigInteger)query.list().get(0)).longValue() : 0;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfFailedLogJobs", e);
        }   
    }


    public long getNumberOfProcessedLines(Date startDate, Date endDate) 
            throws DbException { 
        try {
            /** No. of processed lines (after possible split or merge). */
            String sql = "SELECT SUM(processed_lines) FROM job WHERE finished >= ? AND finished <= ?";
            Query query = session.createSQLQuery(sql);
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            return ((query.list() != null) && (query.list().get(0) != null)) ? ((java.math.BigDecimal)query.list().get(0)).longValue() : 0;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfProcessedLines", e);
        }
    }


    public long getNumberOfLogEntries(Date startDate, Date endDate) 
            throws DbException { 
        try {

            /** No. of processed lines (after possible split or merge). */
            String sql = "SELECT COUNT(*) FROM job j, log_entry l WHERE j.finished >= ? AND j.finished <= ? AND j.id = l.job_id" ;
            Query query = session.createSQLQuery(sql);
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            return (query.list() != null) ? ((BigInteger)query.list().get(0)).longValue() : 0;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfLogEntries", e);
        }
    }


    public long getNumberOfMatchedLogEntries(Date startDate, Date endDate) 
            throws DbException { 
        try {

            /** No. of log entries that have been matched to an organisation. */
            String sql = "SELECT COUNT(*) FROM job j, log_entry l WHERE j.finished >= ? AND j.finished <= ? AND j.id = l.job_id AND (org_id IS NOT NULL or org_id2 IS NOT NULL)" ;
            Query query = session.createSQLQuery(sql);
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            return (query.list() != null) ? ((BigInteger)query.list().get(0)).longValue() : 0;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfMatchedLogEntries", e);
        }
    }


    public long getNumberOfSentEmails(Date startDate, Date endDate) 
            throws DbException { 
        try {

            /** No. of sent emails in all mail jobs. */
            String sql = "SELECT DISTINCT le.org_id FROM log_entry le, mail_job mj, mail_job_log_entry_mapping mjle WHERE le.id = mjle.log_entry_id AND mj.id = mjle.mail_job_id AND mj.started >= ? AND mj.finished <= ?;";

            Query query = session.createSQLQuery(sql);
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            List<?> rs = query.list();

            return rs != null ? (long)rs.size() : 0;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfSentEmails", e);
        }
    }


    public long getNumberOfNewOrganizations(Date startDate, Date endDate) 
            throws DbException { 
        try {
            /** No. of created organizations. */        
            String sql = "SELECT COUNT(*) FROM organization WHERE created >= ? AND created <= ?";
            Query query = session.createSQLQuery(sql);
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            return (query.list() != null) ? ((BigInteger)query.list().get(0)).longValue() : 0;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfNewOrganizations", e);
        }
    }


    public long getNumberOfModifiedOrganizations(Date startDate, Date endDate) 
            throws DbException { 
        try {    
            /** No. of modified organizations. */        
            String sql = "SELECT COUNT(*) FROM organization WHERE last_modified >= ? AND last_modified <= ? AND created <  last_modified";
            Query query = session.createSQLQuery(sql);
            query.setLong(0, SqlUtil.convertTimestamp(startDate));
            query.setLong(1, SqlUtil.convertTimestamp(endDate));

            return (query.list() != null) ? ((BigInteger)query.list().get(0)).longValue() : 0;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfModifiedOrganizations", e);
        }   
    }


    public boolean isRangeOverlapping(long start, long end) 
            throws DbException { 
        try {
            String sql = "SELECT COUNT(*) FROM ip_range WHERE (end_address >= ? AND end_address <= ?) OR (start_address <=  ? AND end_address >= ?) OR  (start_address >= ? AND start_address <= ?) OR (start_address >= ? AND end_address <= ?)";

            Query query = session.createSQLQuery(sql);
            query.setLong(0, start);   
            query.setLong(1, end);
            query.setLong(2, start);
            query.setLong(3, end);
            query.setLong(4, start);
            query.setLong(5, end);
            query.setLong(6, start);
            query.setLong(7, end);

            long result = ((BigInteger)query.list().get(0)).longValue();

            return query.list() != null && result > 0;
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in isRangeOverlapping", e);
        }           
    }


    @SuppressWarnings("unchecked")
    public List<BigInteger> isRangeOverlappingOrgId(long start, long end)
            throws DbException {

        try {
            String sql = "SELECT ORG_ID FROM ip_range WHERE (end_address >= ? AND end_address <= ?) OR (start_address <=  ? AND end_address >= ?) OR  (start_address >= ? AND start_address <= ?) OR (start_address >= ? AND end_address <= ?)";

            Query query = session.createSQLQuery(sql);
            query.setLong(0, start);
            query.setLong(1, end);
            query.setLong(2, start);
            query.setLong(3, end);
            query.setLong(4, start);
            query.setLong(5, end);
            query.setLong(6, start);
            query.setLong(7, end);

            return query.list();

        } catch (Exception e) {
            throw handleException(e.getClass().getSimpleName()
                    + " exception in isRangeOverlappingOrgId", e);
        }
    }


    @SuppressWarnings("unchecked")
    public List<Contact> searchContactsByOrgIDsAndRoles(String[] orgIDs, String roles) 
            throws DbException {

        /*
         * Searches for contacts by orgId (if present) and roles (if present).
         * If no criterias are given, all contacts are returned.
         */
        ArrayList<Contact> contacts = new ArrayList<Contact>();
        Query query = null;


        if (orgIDs != null && orgIDs.length > 0) {
            for (String orgID : orgIDs) {           
                if (roles != null && roles.isEmpty() == false) {
                    // Search by orgId and roles
                    query = session.createQuery("from Contact where OrganizationId = ? and Role in (:roles)");              
                    query.setParameterList("roles", roles.split(","));

                    //System.out.println("1" + query.getQueryString() + " roles: " + roles);
                }   
                else {
                    // Search by orgId only
                    query = session.createQuery("from Contact where OrganizationId = ?");       
                    //System.out.println("2" + query.getQueryString());

                }
                query.setInteger(0, Integer.parseInt(orgID));
                //System.out.println("3" + query.getQueryString());
                //System.out.println("Found " + query.list().size() + " number of contacts.");
                if (query.list().isEmpty() == false) {              
                    contacts.addAll((List<Contact>)query.list());
                }
            }
        }
        else {

            if (roles != null && roles.isEmpty() == false) {    
                // Search by roles only
                query = session.createQuery("from Contact where Role in (:roles)");             
                query.setParameterList("roles", roles.split(","));
            }
            else {
                // Search all contacts
                query = session.createQuery("from Contact");
            }

            contacts.addAll((List<Contact>)query.list());
        }
        return contacts;

    }


    @SuppressWarnings("unchecked")
    public List<Contact>  searcContactsByPrioAndRoles(String prioHigh, String prioLow, String searchRoles)
            throws DbException {

        ArrayList<Contact> contacts = new ArrayList<Contact>();

        Query query = null;
        query = session.createQuery("from Organization where Priority.Prio <= ? and Priority.Prio >= ?");
        query.setString(0, prioHigh);
        query.setString(1, prioLow);

        if (query.list() != null) {         
            List<Organization> orgs = query.list();         
            Iterator<Organization> itr = orgs.iterator();           
            while (itr.hasNext()) {                     
                Set<Contact> orgContacts = ((Organization)itr.next()).getContacts();

                if (orgContacts != null && orgContacts.isEmpty() == false) {
                    if (searchRoles != null && searchRoles.isEmpty() == false) {
                        for (Contact contact : orgContacts) {
                            if (contact != null && contact.getRole() != null && searchRoles.contains(contact.getRole())) {

                                contacts.add(contact);                              
                            }
                        }
                    }
                    else {
                        contacts.addAll(orgContacts);
                    }
                }                               
            }                       
        }       

        return contacts;


    }

    @SuppressWarnings("unchecked")
    public List<String> getAllContactRoleNames() 
            throws DbException {
        try {
            String sql = "SELECT DISTINCT role FROM contact where role is not null ORDER BY role";
            Query query = session.createSQLQuery(sql);

            return (List<String>)query.list();

        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in getAllContactRoleNames", e);
        }

    }


    public void genericDelete(Object obj) 
            throws DbException {
        try {
            session.delete(obj);
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in genericDelete", e);
        }
    }


    public void flushSession()
            throws DbException {
        try {    
            session.flush();
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in flushSession", e);
        }
    }    

    public void close() 
            throws DbException {

        try {
            session.close();
        }
        catch (Exception e) {
            throw handleException(e.getClass().getSimpleName() + " exception in close", e);
        }
    }

    private DbException handleException (String msg, Exception e) {

        return new DbException(msg, e);

    }

} public class Job extends BaseJob {
	private static final long serialVersionUID = 1L;

/*[CONSTRUCTOR MARKER BEGIN]*/
	public Job () {
		super();
	}

	/**
	 * Constructor for primary key
	 */
	public Job (java.lang.Long id) {
		super(id);
	}

	/**
	 * Constructor for required fields
	 */
	public Job (
		java.lang.Long id,
		java.lang.String name,
		java.lang.String filename,
		java.lang.String fileHash,
		java.lang.Long fileSize,
		java.lang.Long started) {

		super (
			id,
			name,
			filename,
			fileHash,
			fileSize,
			started);
	}

/*[CONSTRUCTOR MARKER END]*/


} public class LogEntry extends BaseLogEntry {
	private static final long serialVersionUID = 1L;
	
	
/*[CONSTRUCTOR MARKER BEGIN]*/
	public LogEntry () {
		super();
	}

	/**
	 * Constructor for primary key
	 */
	public LogEntry (java.lang.Long id) {
		super(id);
	}

	/**
	 * Constructor for required fields
	 */
	public LogEntry (
		java.lang.Long id,
		java.lang.Long created,
		java.lang.Long logTimestamp) {

		super (
			id,
			created,
			logTimestamp);
	}

/*[CONSTRUCTOR MARKER END]*/
	
	@Override
    public int compareTo(Object obj) {

        int result = 0;
        
        if (this.getId() == 0 || ((LogEntry)obj).getId() == 0) {
            // Assert that only objects that have been persited are used. 
            throw new java.lang.AssertionError("LogEntry ID is undefined (0)");
        }
        		
		if (this.getId() == ((LogEntry)obj).getId()) {		
			result = 0;
		}
		else if (this.getId() < ((LogEntry)obj).getId()) {
			result = -1;
		}
		else {
			return 1;
		}
		return result;
	}
	
} public class MailJob extends BaseMailJob {
	private static final long serialVersionUID = 1L;

/*[CONSTRUCTOR MARKER BEGIN]*/
	public MailJob () {
		super();
	}

	/**
	 * Constructor for primary key
	 */
	public MailJob (java.lang.Long id) {
		super(id);
	}

	/**
	 * Constructor for required fields
	 */
	public MailJob (
		java.lang.Long id,
		boolean usePrimaryOrg,
		java.lang.Long started) {

		super (
			id,
			usePrimaryOrg,
			started);
	}

/*[CONSTRUCTOR MARKER END]*/

} public class Organization extends BaseOrganization {
    private static final long serialVersionUID = 1L;

    /*[CONSTRUCTOR MARKER BEGIN]*/
    public Organization () {
        super();
    }

    /**
     * Constructor for primary key
     */
    public Organization (java.lang.Integer id) {
        super(id);
    }

    /**
     * Constructor for required fields
     */
    public Organization (
            java.lang.Integer id,
            java.lang.String name,
            boolean enabled,
            java.lang.Long created,
            java.lang.Long lastModified,
            java.lang.String modifiedBy,
            boolean autoUpdateMatchFields) {

        super (
                id,
                name,
                enabled,
                created,
                lastModified,
                modifiedBy,            
                autoUpdateMatchFields);
    }

    /*[CONSTRUCTOR MARKER END]*/


    // Overloaded method from the base class that adds organization id to the Contact.
    @Override
    public void addToContacts (se.sitic.megatron.entity.Contact contact) {
        if (null == getContacts()) setContacts(new java.util.TreeSet<se.sitic.megatron.entity.Contact>());

        contact.setOrganizationId(this.getId());        
        super.addToContacts(contact);        
    }    
    // Overloaded method from the base class that adds organization id to the IP-range.
    @Override
    public void addToIpRanges (se.sitic.megatron.entity.IpRange ipRange) {
        if (null == getIpRanges()) setIpRanges(new java.util.TreeSet<se.sitic.megatron.entity.IpRange>());

        ipRange.setOrganizationId(this.getId());        
        super.addToIpRanges(ipRange);        
    }
    // Overloaded method from the base class that adds organization id to the DomainName.
    @Override
    public void addToDomainNames (se.sitic.megatron.entity.DomainName domainName) {
        if (null == getDomainNames()) setDomainNames(new java.util.TreeSet<se.sitic.megatron.entity.DomainName>());

        domainName.setOrganizationId(this.getId());
        super.addToDomainNames(domainName);        
    }
    // Overloaded method from the base class that adds organization id to the ASNumber.
    @Override
    public void addToASNumbers (se.sitic.megatron.entity.ASNumber asNumber) {
        if (null == getASNumbers()) setASNumbers(new java.util.TreeSet<se.sitic.megatron.entity.ASNumber>());

        asNumber.setOrganizationId(this.getId());
        super.addToASNumbers(asNumber);
    }
    
    // Returning all enabled email addresses
    public String getEmailAddresses(){        
        return this.getEmailAddresses(null, true);
    }

    // This method returns all email addresses for the organization that matches the given email type and 
    // the enabledOnly flag. If the email type is null or empty, all addresses matching the enabledOnly
    // flag are returned.

    public String getEmailAddresses(String emailType, boolean enabledOnly){

        java.util.Set<se.sitic.megatron.entity.Contact> allContacts = getContacts();
        java.util.Set<String> addresses = new java.util.TreeSet<String>(); 

        for (se.sitic.megatron.entity.Contact contact : allContacts) {
            if (contact.isEnabled() == true || enabledOnly == false) {
                if (emailType == null) {
                    addresses.add(contact.getEmailAddress());
                }
                else if (contact.getEmailType().toLowerCase().equals(emailType.toLowerCase())) {                    
                    addresses.add(contact.getEmailAddress());    
                }                
            }
        }
        if (addresses.isEmpty()) {
            return "";
        }
        else {
            String emails = java.util.Arrays.toString(addresses.toArray());        
            return emails.substring(1, emails.length()-1);
        }
    }
} public class Priority extends BasePriority {
	private static final long serialVersionUID = 1L;

/*[CONSTRUCTOR MARKER BEGIN]*/
	public Priority () {
		super();
	}

	/**
	 * Constructor for primary key
	 */
	public Priority (java.lang.Integer id) {
		super(id);
	}

	/**
	 * Constructor for required fields
	 */
	public Priority (
		java.lang.Integer id,
		java.lang.String name,
		java.lang.Integer prio) {

		super (
			id,
			name,
			prio);
	}

/*[CONSTRUCTOR MARKER END]*/


} public abstract class AppUtil {
    private static final Logger log = Logger.getLogger(AppUtil.class);

    private static Random rand = new Random();
    

    /**
     * Compiles regular expression from specified property. Expands the
     * regular expression before compiling it.
     *
     * @return compiled regular expression, or null if not found.
     * @throws MegatronException if invalid regular expression.
     */
    public static Matcher compileRegExp(TypedProperties props, String regExpPropertyKey) throws MegatronException {
        Matcher result = null;
        String regExp = null;
        try {
            regExp = props.getString(regExpPropertyKey, null);
            regExp = AppUtil.expandRegExp(props, regExp);
            if (!StringUtil.isNullOrEmpty(regExp)) {
                log.debug("Compiling " + regExpPropertyKey + ": " + regExp);
                result = Pattern.compile(regExp).matcher("");
            }
        } catch (PatternSyntaxException e) {
            String msg = "Cannot compile regular expression: " + regExp;
            throw new MegatronException(msg, e);
        }

        return result;
    }


    /**
     * Expands specified regular expresson with templates defined in properties.
     * <p>
     * Example: "titleMustNotMatch=@secuniaLinuxDistro@" will be expanded to
     * "titleMustNotMatch=(?i)(debian|gentoo|suse|ubuntu)\supdate".
     *
     * @param regExp regular expression to expand, e.g. "@secuniaLinuxDistro@".
     */
    public static String expandRegExp(TypedProperties props, String regExp) {
        if ((props == null) || (regExp == null)) {
            return regExp;
        }

        // Return if no "@" found
        if (regExp.indexOf('@') == -1) {
            return regExp;
        }

        // -- Find template tokens
        List<String> tokens = new ArrayList<String>();
        // "@" may be escaped by "\"
        String tokenStr = StringUtil.replace(regExp, "\\@", "#");
        Pattern pattern = Pattern.compile("(@.+?@)");
        Matcher matcher = pattern.matcher(tokenStr);
        while (matcher.find()) {
            if (matcher.group(1) != null) {
                tokens.add(matcher.group(1));
            }
        }

        // -- Replace tokens
        String result = regExp;
        final String regExpTemplatePrefix = "@regExp.";
        for (Iterator<String> iterator = tokens.iterator(); iterator.hasNext(); ) {
            String templateName = iterator.next();
            String key = templateName;
            // key may not be prefixed with "@regExp."
            if (!key.startsWith(regExpTemplatePrefix)) {
                key = regExpTemplatePrefix + key.substring(1);
            }
            // remove enclosing "@".
            key = key.substring(1, key.length()-1);
            String templateValue = props.getString(key, null);

            if (templateValue != null) {
                result = StringUtil.replace(result, templateName, templateValue);
            }
        }

        return result;
    }


    /**
     * Returns specified string hashed, e.g. using the MD5 algorithm.<p>
     *
     * @return hex-encoded hash value.
     */
    public static String hashString(String str) {
        str = (str != null) ? str : "";
        String result = null;
        try {
            MessageDigest digest = MessageDigest.getInstance(Constants.DIGEST_ALGORITHM);
            byte[] hashValue = digest.digest(str.getBytes(Constants.UTF8));
            result = StringUtil.encode(hashValue);
        } catch (Exception e) {
            // NoSuchAlgorithmException, UnsupportedEncodingException
            log.error("Cannot hash string (this should never happen).", e);
            result = str;
        }
        return result;
    }
    
    
    /**
     * Expands variables in specified template. Side effect: items may be added to attrMap. 
     * 
     * @param template template with variables to expand.
     * @param attrMap map replace values (key: variable name, value: variable value).
     * 
     * @throws ConversionException if not all variables have been expanded.
     */
    public static String replaceVariables(String str, Map<String, String> attrMap, boolean isXml, String templateName) throws ConversionException {
        
        // TODO: Convert this method to a Class, e.g. TemplateReplacer. Can avoid creating so many objects. May also use TypedProperties.   
        
        // -- Add padding if present
        // Format: variableName#padRight<num>|padLeft<num>
        // Example: $ipAddress#padRight10
        if ((str.indexOf("#padLeft") != -1) || (str.indexOf("#padRight") != -1)) {
            String regExp = "\\" + LogEntryMapper.VARIABLE_PREFIX + "(\\w+?)#(padLeft|padRight)(\\d+)";
            Matcher matcher = Pattern.compile(regExp).matcher(str);
            while (matcher.find() && (matcher.groupCount() == 3)) {
                String variable = matcher.group(1);
                String padType = matcher.group(2);
                int len = Integer.parseInt(matcher.group(3));
                String value = attrMap.get(variable);
                if (value != null) {
                    if (padType.equals("padLeft")) {
                        value = StringUtil.leftPad(value, len);
                    } else if (padType.equals("padRight")) {
                        value = StringUtil.rightPad(value, len);
                    }
                    String key = variable + "#" + padType + len;
                    attrMap.put(key, value);
                }
            }
        }
        
        // -- Add $freeTextList and $additionalItemList if present 
        if (str.indexOf(LogEntryMapper.VARIABLE_PREFIX + "freeTextList") != -1) {
            StringBuilder freeTextListStr = new StringBuilder(128);
            int freeTextIndex = 0;
            boolean freeTextFound = true; 
            while (freeTextFound) {
                String key = LogEntryMapper.FREE_TEXT_PREFIX + (freeTextIndex++);
                if (attrMap.get(key) != null) {
                    if (freeTextListStr.length() > 0) {
                        freeTextListStr.append(", ");
                    }
                    freeTextListStr.append('"').append(attrMap.get(key)).append('"');
                } else {
                    freeTextFound = false;
                }
            }
            attrMap.put("freeTextList", freeTextListStr.toString());
        }
        if (str.indexOf(LogEntryMapper.VARIABLE_PREFIX + "additionalItemList") != -1) {
            StringBuilder additonalItemListStr = new StringBuilder(128);
            List<String> keys = new ArrayList<String>(attrMap.keySet());
            Collections.sort(keys, ObjectStringSorter.createDefaultSorter());
            for (Iterator<String> iterator = keys.iterator(); iterator.hasNext(); ) {
                String key = iterator.next();
                if (key.startsWith(LogEntryMapper.ADDITIONAL_ITEM_PREFIX)) {
                    if (additonalItemListStr.length() > 0) {
                        additonalItemListStr.append(", ");
                    }
                    String[] headTail = StringUtil.splitHeadTail(key, "_", false);
                    additonalItemListStr.append(headTail[1]).append("=").append('"').append(attrMap.get(key)).append('"');
                }
            }
            attrMap.put("additionalItemList", additonalItemListStr.toString());
        }
        
        // -- Sort variables by length, e.g. $ipAddress2 must come before $ipAddress (otherwise "2" will not be replaced)
        List<String> keys = new ArrayList<String>(attrMap.keySet());
        Collections.sort(keys, new Comparator<String>() {
                @Override
                public int compare(String o1, String o2) {
                    // reverse sort order
                    return (o1.length() > o2.length()) ? -1 : (o1.length() == o2.length() ? 0 : 1);
                }
            });
        
        // -- Replace variables
        String result = str;
        int indexOfVariable = result.indexOf(LogEntryMapper.VARIABLE_PREFIX);
        for (Iterator<String> iterator = keys.iterator(); (indexOfVariable != -1) && iterator.hasNext(); ) {
            String key = iterator.next();
            String value = isXml ? StringUtil.encodeCharacterEntities(attrMap.get(key)) : attrMap.get(key);
            value = (value != null) ? value : "";
            result = StringUtil.replace(result, LogEntryMapper.VARIABLE_PREFIX + key, value);
            indexOfVariable = result.indexOf(LogEntryMapper.VARIABLE_PREFIX);
        }
        
        // Note: '$' not allowed in template.
        if (indexOfVariable != -1) {
            boolean dollarSignInvalues = false;
            for (Iterator<String> iterator = keys.iterator(); !dollarSignInvalues && iterator.hasNext(); ) {
                if (attrMap.get(iterator.next()).indexOf(LogEntryMapper.VARIABLE_PREFIX) != -1) {
                    dollarSignInvalues = true;
                }
            }
            if (!dollarSignInvalues) {
                String msg = "The template '" + templateName + "' may contain unused variables because '$' is present. Replaced template: " + result;               
               // Issue Warning message instead of exception until a better solution is implemented
               //TODO Fix issue with two stage variable replacement
               log.warn(msg);
            }
        }
        
        return result;
    }

    
    /**
     * Returns IP-addresses from ipAddress and ipRange to make lookups for.
     * 
     * @return list of IP-addresses, or null if not found.
     */
    public static List<Long> getIpAddressesToDecorate(LogEntry logEntry) {
        List<Long> result = null;
        
        // order: ipAddress, rangeStart+1, rangeStart, rangeEnd
        if (logEntry.getIpAddress() != null) {
            result = new ArrayList<Long>();
            result.add(logEntry.getIpAddress());
        }
        
        if ((logEntry.getIpRangeStart() != null) && (logEntry.getIpRangeStart().longValue() != 0L) && 
                (logEntry.getIpRangeEnd() != null) && (logEntry.getIpRangeEnd().longValue() != 0L)) {
            if (result == null) {
                result = new ArrayList<Long>();
            }
            Long start = logEntry.getIpRangeStart();
            Long end = logEntry.getIpRangeEnd();
            if ((start.longValue() + 1) < end.longValue()) {
                result.add(new Long(start.longValue() + 1));
            }
            result.add(start);
            if (start.longValue() != end.longValue()) {
                result.add(end);
            }
        }
        
        return result;
    }
    
    
    /**
     * Returns a random value in specified range.
     */
    public static Long randomLong(int intervalStart, int intervalEnd) {
        long result = Math.abs(rand.nextLong());
        result = intervalStart + (result % (intervalEnd - intervalStart));
        return new Long(result);
    }

    
    /**
     * Extracts hostname from specified URL, or null if not found or
     * domain name contains illegal characters.
     * <p>
     * Example: "http://www.sitic.se/om-sitic" returns "www.sitic.se", 
     * "" returns null.
     */
    public static String extractHostnameFromUrl(String url) {
        if ((url == null) || (url.trim().length() == 0)) {
            return null;
        }
        
        // Syntax: resource_type://username:password@domain:port/path?query_string#anchor
        String result = url.trim(); 
        
        // remove "protocol://"
        int index = result.indexOf("://");
        if (index != -1) {
            if ((index + 3) < result.length())  {
                result = result.substring(index + 3, result.length());
            } else {
                return null;
            }
        }
        
        // remove username and password (supposed to be encoded)
        int indexAt = result.indexOf("@");
        if (indexAt != -1) {
            int indexSlash = result.indexOf("/");
            if ((indexSlash == -1) || ((indexSlash != -1) && (indexAt < indexSlash))) {
                if ((indexAt + 1) < result.length())  {
                    result = result.substring(indexAt + 1, result.length());
                } else {
                    return null;
                }
            }
        }
        
        // copy to ':' or '/'
        int indexColon = result.indexOf(":");
        int indexSlash = result.indexOf("/");
        if ((indexColon != -1) && (indexSlash != -1)) {
            index = Math.min(indexColon, indexSlash);
        } else if (indexColon != -1) {
            index = indexColon; 
        } else if (indexSlash != -1) {
            index = indexSlash;
        } else {
            index = result.length();
        }
        result = result.substring(0, index);
        
        // exists illegal characters?
        for (int i = 0; i < result.length(); i++) {
            char ch = result.charAt(i);
            if (!((('a' <= ch) && (ch <= 'z')) || (('A' <= ch) && (ch <= 'Z')) || (('0' <= ch) && (ch <= '9')) || (((ch == '-') || (ch == '.')) && (i > 0)))) {
                return null;
            }
        }
        if (result.trim().length() == 0) {
            return null;
        }
        
        return result;
    }


    /**
     * Returns true if specified email addresses are valid, otherwise false.
     * 
     * @param emailAddresses comma separated list of email addresses.
     */
    public static boolean isEmailAddressesValid(String emailAddresses) {
        if (StringUtil.isNullOrEmpty(emailAddresses)) {
            return false;
        }
        
        String[] addresses = emailAddresses.trim().split(",");
        if ((addresses == null) || (addresses.length == 0)) {
            return false;
        }

        String regExp = "(?i)^[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,6}$";
        Matcher matcher = Pattern.compile(regExp).matcher("");
        for (int i = 0; i < addresses.length; i++) {
            String address = addresses[i].trim();
            matcher.reset(address);
            if (!matcher.find()) {
                return false;
            }
        }

        return true;
    }

} public abstract class Constants {

    /** Line break in files etc. */
    public static final String LINE_BREAK = "\n";

    /** UTF-8 character-set in Java core API. */
    public static final String UTF8 = "UTF-8";

    /** ISO-8859 character-set in Java core API. */
    public static final String ISO8859 = "ISO-8859-1";

    /** MIME-type for plain text. */
    public static final String MIME_TEXT_PLAIN = "text/plain";
    
    /** Comments in config files starts with this string. */
    public static final String CONFIG_COMMENT_PREFIX = "#";

    /** Hash algoritm to use. */
    public static final String DIGEST_ALGORITHM = "md5";

    /** Job type name to use when name is missing in the job_type table. */
    public static final String DEFAULT_JOB_TYPE = "default";
    
    // Values for the property filter.countryCodeFilter.organizationToFilter
    // and filter.asnFilter.organizationToFilter
    public static final String ORGANIZATION_PRIMARY = "primary";
    public static final String ORGANIZATION_SECONDARY = "secondary";
    public static final String ORGANIZATION_BOTH = "both";

    // Additional format strings for parser.timestampFormat.
    public static final String TIME_STAMP_FORMAT_EPOCH_IN_SEC = "epochInSec";
    public static final String TIME_STAMP_FORMAT_EPOCH_IN_MS = "epochInMs";
    public static final String TIME_STAMP_FORMAT_WINDOWS_EPOCH = "windowsEpoch";
    
} public abstract class IpAddressUtil {
    private static final Logger log = Logger.getLogger(IpAddressUtil.class);
    private static final int HOST_NAME_CACHE_MAX_ENTRIES = 2048;
    
    private static Map<Long, String> hostNameCache;
    private static boolean useDnsJava;
    private static boolean useSimpleResolver; 
    private static SimpleResolver simpleResolver;
    
    private static final Matcher IP_ADDRESS_MATCHER = Pattern.compile("(\\d{1,3})\\.(\\d{1,3})\\.(\\d{1,3})\\.(\\d{1,3})").matcher("");
     
    
    static {
        TypedProperties globalProps = AppProperties.getInstance().getGlobalProperties();

        if (globalProps.existsProperty(AppProperties.USE_DNS_JAVA_KEY) && !globalProps.existsProperty(AppProperties.DNS_JAVA_USE_DNS_JAVA_KEY)) {
            // use deprecated property
            useDnsJava = globalProps.getBoolean(AppProperties.USE_DNS_JAVA_KEY, true);
        } else {
            useDnsJava = globalProps.getBoolean(AppProperties.DNS_JAVA_USE_DNS_JAVA_KEY, true);
        }
        if (useDnsJava) {
            String dnsServers = globalProps.getString(AppProperties.DNS_JAVA_DNS_SERVERS_KEY, null);
            if (dnsServers != null) {
                System.getProperties().put("dns.server", dnsServers);
            }
            int timeOut = globalProps.getInt(AppProperties.DNS_JAVA_TIME_OUT_KEY, 4);
            useSimpleResolver = globalProps.getBoolean(AppProperties.DNS_JAVA_USE_SIMPLE_RESOLVER_KEY, true);
            if (useSimpleResolver) {
                try {
                    simpleResolver = new SimpleResolver();
                    simpleResolver.setTimeout(timeOut);
                } catch (UnknownHostException e) {
                    log.error("Cannot initialize DNS service (SimpleResolver). Bad DNS server?", e);
                }
            }
            Lookup.getDefaultResolver().setTimeout(timeOut);
        } 
        
        hostNameCache = Collections.synchronizedMap(new LinkedHashMap<Long, String>(HOST_NAME_CACHE_MAX_ENTRIES, .75F, true) {
            private static final long serialVersionUID = 1L;

            @Override
            protected boolean removeEldestEntry(Map.Entry<Long, String> eldest) {
                return size() > HOST_NAME_CACHE_MAX_ENTRIES;
            }
        });
    }

    
    /**
     * Converts specified IP-range as a string to numeric values.
     * <p>
     * Supported formats:<ul>
     * <li>192.121.218.4
     * <li>192.121.218.0/20
     * <li>192.121.x.x
     * <li>192.121.218.0-255
     * <li>192.121.218.0-192.121.220.255
     * </li>
     * 
     * @return IP-range in an array with two elements (start address, end address).
     */
    public static long[] convertIpRange(String ipRange) throws MegatronException {
        // param check
        if (StringUtil.isNullOrEmpty(ipRange) || (ipRange.trim().length() < "0.0.0.0".length())) {
            throw new MegatronException("IP-range is null or too small: " + ipRange);
        }
        
        if (ipRange.toLowerCase().contains("x")) {
            ipRange = expandWildCardOctets(ipRange.toLowerCase().trim(), "x");
        }
        
        long[] result = null;
        try {
            if (ipRange.contains("/")) {
                // format: 192.121.218.0/20
                result = convertBgpPrefix(ipRange, 32);
            } else if (ipRange.contains("-")) {
                result = new long[2];
                String[] headTail = StringUtil.splitHeadTail(ipRange.trim(), "-", false);
                if ((headTail[0].length() == 0) || (headTail[1].length() == 0)) {
                    throw new MegatronException("Invalid format for IP-range: " + ipRange);
                }
                if (headTail[1].contains(".")) {
                    // format: 192.121.218.0-192.121.220.255
                    result[0] = convertIpAddress(headTail[0]);
                    result[1] = convertIpAddress(headTail[1]);
                } else {
                    // format: 192.121.218.0-255
                    String[] ipAddressHeadTail = StringUtil.splitHeadTail(headTail[0].trim(), ".", true);
                    if ((ipAddressHeadTail[0].length() == 0) || (ipAddressHeadTail[1].length() == 0)) {
                        throw new MegatronException("Invalid start IP-address in IP-range: " + ipRange);
                    }
                    String endIpAddress = ipAddressHeadTail[0] + "." + headTail[1];
                    result[0] = convertIpAddress(headTail[0]);
                    result[1] = convertIpAddress(endIpAddress);
                }
            } else {
                // format: 192.121.218.4
                result = new long[2];
                result[0] = convertIpAddress(ipRange);
                result[1] = convertIpAddress(ipRange);
            }
        } catch (UnknownHostException e) {
            throw new MegatronException("Cannot convert IP-address in IP-range: " + ipRange, e);
        }
        
        // check range
        if (result[0] > result[1]) {
            throw new MegatronException("Start address is greater than end address in IP-range: " + ipRange);
        }
        
        return result;
    }

    
    /**
     * As convertIpRange(String) but with the option to expand trailing 
     * zero octets.
     *  
     * @param expandZeroOctets expand trailing zero octets? If true, e.g. 
     *      "202.131.0.0" will be expanded to "202.131.0.0/16".
     */
    public static long[] convertIpRange(String ipRange, boolean expandZeroOctets) throws MegatronException {
        if (!expandZeroOctets) {
            return convertIpRange(ipRange);
        }
        
        // param check
        if (StringUtil.isNullOrEmpty(ipRange) || (ipRange.trim().length() < "0.0.0.0".length())) {
            throw new MegatronException("IP-range is null or too small: " + ipRange);
        }

        if (ipRange.contains(".0")) {
            ipRange = expandWildCardOctets(ipRange, "0");
        } else if (ipRange.contains(".000")) {
            ipRange = expandWildCardOctets(ipRange, "000");
        }
        
        return convertIpRange(ipRange);
    }

    
    /**
     * Converts a BGP-prefix to an IP-range.
     * 
     * @param bgpPrefix may include slash or not. Example: "202.88.48.0/20" or "202.131.0.0".
     *  
     * @return IP-range in an array with two elements (start address, end address).
     */
    public static long[] convertBgpPrefix(String bgpPrefix) throws MegatronException {
        return convertBgpPrefix(bgpPrefix, 24);
    }
    

    /**
     * Converts specified integer IP-address to an InetAddress-object.
     *    
     * @throws UnknownHostException if specified IP-address is invalid.
     */
    public static InetAddress convertIpAddress(long ipAddress) throws UnknownHostException {
        // How to convert ip-number to integer:
        // http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp
        
        // ff.ff.ff.ff = 256*16777216 + 256*65536 + 256*256 + 256 = 4311810304
        if ((ipAddress <= 0L) || (ipAddress >= 4311810304L)) {
            throw new UnknownHostException("IP-address out of range: " + ipAddress);
        }
    
        byte[] ip4Address = convertLongAddressToBuf(ipAddress);
        return InetAddress.getByAddress(ip4Address);
    }

    
    /**
     * Converts specified integer IP-address in the format XX.XX.XX.XX to an InetAddress-object.
     *    
     * @throws UnknownHostException if specified IP-address is invalid.
     */
    public static long convertIpAddress(String ipAddress) throws UnknownHostException {
        if (ipAddress == null) {
            throw new UnknownHostException("Invalid IP-address; is null.");
        }
                
        String[] tokens = ipAddress.trim().split("\\.");
        if ((tokens == null) || (tokens.length != 4)) {
            throw new UnknownHostException("Invalid IP-address: " + ipAddress);
        }
        
        long result = 0L;
        long factor = 1;
        for (int i = 3; i >= 0; i--) {
            try {
                int intToken = Integer.parseInt(tokens[i]);
                if ((intToken < 0) || (intToken > 255)) {
                    throw new UnknownHostException("Invalid IP-address: " + ipAddress);
                }
                result += intToken*factor;
                factor = factor << 8;
            } catch (NumberFormatException e) {
                throw new UnknownHostException("Invalid IP-address: " + ipAddress);
            }
        }
        
        return result;
    }


    /**
     * Converts specified integer IP-address to its string representation.
     * 
     * @param includeHostName will do a reverse name lookup and append host 
     *      name to result if lookup successful.
     *    
     * @return ip-adress, or empty string if specified address is invalid or
     *      in case of an exception. Example: "130.239.8.25 (wsunet.umdc.umu.se)". 
     */
    public static String convertIpAddress(long ipAddress, boolean includeHostName) {
        if (ipAddress == 0L) {
            return "";
        }

        String hostAddress = null;
        try {
            hostAddress = convertIpAddressToString(ipAddress);
        } catch (UnknownHostException e) {
            String msg = "Cannot convert IP-address (address probably invalid): " + ipAddress;
            log.warn(msg, e);
            hostAddress = "";
        }
        
        String hostName = null;
        if (includeHostName) {
            hostName = reverseDnsLookupInternal(ipAddress, true);
        }
        
        StringBuilder result = new StringBuilder(256); 
        result.append(hostAddress);
        if (!StringUtil.isNullOrEmpty(hostName)) {
            result.append(" (").append(hostName).append(")");
        }

        return result.toString();
    }

    
    /**
     * Converts specified integer IP-address and mask the two last octets.
     * <p>Note: This method is not thread-safe.
     * 
     * @return ip-adress, or empty string if specified address is invalid or
     *      in case of an exception. Example: "130.239.x.x". 
     */
    public static String convertAndMaskIpAddress(long ipAddress) {
        String result = convertIpAddress(ipAddress, false);
        if (!StringUtil.isNullOrEmpty(result)) {
            // keep first two numbers in ip-address
            String replaceStr = "$1.$2.x.x";
            IP_ADDRESS_MATCHER.reset(result);
            String resultMasked = IP_ADDRESS_MATCHER.replaceFirst(replaceStr);
            if (result.equals(resultMasked)) {
                // may be some error if not masked at all; mask everything
                resultMasked = "x.x.x.x";
            }
            // log.debug("Masked IP-address: " + result + " --> " + resultMasked);
            result = resultMasked;
        }
        
        return result;
    }
    
    
    /**
     * Returns IP-address as an integer for specified hostname, or 0L
     * if lookup fails.
     */
    public static long dnsLookup(String hostname) {
        long result = 0L;
        String hostAddress = null;

        try {
            hostname = hostname.trim();
            InetAddress inetAddress = null;
            if (useDnsJava) {
                inetAddress = Address.getByName(hostname);
            } else {
                inetAddress = InetAddress.getByName(hostname);
            }
            hostAddress = inetAddress.getHostAddress();
        } catch (UnknownHostException e) {
            String msg = "DNS lookup failed for hostname: " + hostname;
            log.debug(msg, e);
        }
        
        try {
            if (hostAddress != null) {
                result = convertIpAddress(hostAddress);
            }
        } catch (UnknownHostException e) {
            String msg = "Connot convert IP-address to an integer: " + hostAddress;
            log.warn(msg, e);
        }

        return result;
    }

    
    /**
     * Returns hostname from specified ip-address, or empty string
     * if lookup fails. 
     */
    public static String reverseDnsLookup(long ipAddress) {
        return reverseDnsLookupInternal(ipAddress, true);
    }

    
    /**
     * As reverseDnsLookup(long), but without cache.
     */
    public static String reverseDnsLookupWithoutCache(long ipAddress) {
        return reverseDnsLookupInternal(ipAddress, false);
    }

    
    private static String reverseDnsLookupInternal(long ipAddress, boolean useCache) {
        String result = null;
        
        // -- Cache lookup
        Long cacheKey = null;
        if (useCache) {
            cacheKey = new Long(ipAddress);
            result = hostNameCache.get(cacheKey);
            if (result != null) {
                // log.debug("Cache hit: " + ipAddress + ":" + result);
                return result;
            }
        }
        
        // -- Reverse DNS lookup 
        try {
            if (useDnsJava) {
                if (useSimpleResolver) {
                    result = reverseDnsLookupUsingDnsJavaSimpleResolver(ipAddress);
                } else {
                    result = reverseDnsLookupUsingDnsJavaExtendedResolver(ipAddress);
                }
            } else {
                result = reverseDnsLookupUsingJdk(ipAddress);
            }
            // -- Validate hostname
            validateHostname(result);
        } catch(IOException e) {
            // UnknownHostException, IOException
            result = "";
            if (log.isDebugEnabled()) {
                String ipAddressStr = null;
                try {
                    ipAddressStr = convertIpAddressToString(ipAddress);
                } catch (UnknownHostException e2) {
                    ipAddressStr = Long.toString(ipAddress);
                }
                String msg = "Reverse DNS lookup failed for IP-address: " + ipAddressStr;
                log.debug(msg, e);
            }
        }
        
        // -- Add to cache (even empty entries)
        if (useCache) {
            if (log.isDebugEnabled()) {
                String ipAddressStr = null;
                try {
                    ipAddressStr = convertIpAddressToString(ipAddress);
                } catch (UnknownHostException e2) {
                    ipAddressStr = Long.toString(ipAddress);
                }
                log.debug("Adds hostname to cache: " + ipAddress + " [" + ipAddressStr + "]:" + result);
            }
            hostNameCache.put(cacheKey, result);
        }
        
        return result;
    }

    
    private static String reverseDnsLookupUsingJdk(long ipAddress) throws UnknownHostException {
        // -- Convert ip-address (long) to string
        InetAddress inetAddress = convertIpAddress(ipAddress);
        String hostAddress = inetAddress.getHostAddress();
        if (StringUtil.isNullOrEmpty(hostAddress)) {
            throw new UnknownHostException("Cannot convert IP-address: " + ipAddress);
        }

        // -- Reverse DNS lookup 
        log.debug("Making a reverse DNS lookup for " + ipAddress);
        String result = inetAddress.getHostName();
        log.debug("Lookup result: " + result);
        if (StringUtil.isNullOrEmpty(result) || result.equals(hostAddress)) {
            throw new UnknownHostException("Reverse DNS lookup failed: " + hostAddress);
        }
        return result;
    }

    
    private static String reverseDnsLookupUsingDnsJavaExtendedResolver(long ipAddress) throws UnknownHostException {
        byte[] address = convertLongAddressToBuf(ipAddress);
        Name name = ReverseMap.fromAddress(InetAddress.getByAddress(address));
        Record[] records = new Lookup(name, Type.PTR).run();
        if (records == null) {
            throw new UnknownHostException();
        }
        String result = ((PTRRecord)records[0]).getTarget().toString();
        // remove trailing "."
        result = result.endsWith(".") ? result.substring(0, result.length() - 1) : result;
        return result;
    }

    
    private static String reverseDnsLookupUsingDnsJavaSimpleResolver(long ipAddress) throws IOException {
        String result = null;
        byte[] address = convertLongAddressToBuf(ipAddress);
        Name name = ReverseMap.fromAddress(InetAddress.getByAddress(address));
        Record record = Record.newRecord(name, Type.PTR, DClass.IN);
        Message query = Message.newQuery(record);
        Message response = simpleResolver.send(query);
        Record[] answers = response.getSectionArray(Section.ANSWER);
        if (answers.length != 0) {
            // If PTR-record exists this will be at index 1 or above (more than one PTR-record may exist)
            Record answer = (answers.length > 1) ? answers[1] : answers[0];  
            result = answer.rdataToString();
            // remove trailing "."
            result = result.endsWith(".") ? result.substring(0, result.length() - 1) : result;
        } else {
            throw new IOException("Empty DNS response.");
        }
        return result;
    }
    
    
    private static String convertIpAddressToString(long ipAddress) throws UnknownHostException {
        InetAddress inetAddress = convertIpAddress(ipAddress);
        String result = inetAddress.getHostAddress();
        if (StringUtil.isNullOrEmpty(result)) {
            throw new UnknownHostException("Cannot convert IP-address: " + ipAddress);
        }
        return result;
    }
    
    
    private static byte[] convertLongAddressToBuf(long ipAddress) {
        byte[] result = new byte[4];
        result[0] = (byte)((ipAddress >> 24) & 0xFF);
        result[1] = (byte)((ipAddress >> 16) & 0xFF);
        result[2] = (byte)((ipAddress >> 8) & 0xFF);
        result[3] = (byte)((ipAddress >> 0) & 0xFF);
        return result;
    }

    
    /**
     * Validates specified hostname.
     * 
     * @throws UnknownHostException if hostname contain malicious or invalid content.
     */
    private static void validateHostname(String hostName) throws UnknownHostException {
        // More info: http://en.wikipedia.org/wiki/Hostname
        
        // Valid letters: a..z, A..Z, 0..9, -
        for (int i = 0; i < hostName.length(); i++) {
            char ch = hostName.charAt(i);
            if (!((('a' <= ch) && (ch <= 'z')) || (('A' <= ch) && (ch <= 'Z')) || (('0' <= ch) && (ch <= '9')) || (((ch == '-') || (ch == '.')) && (i > 0)))) {
                throw new UnknownHostException("Host name contains illegal character: " + ch + ". Host name: " + hostName);
            }
        }
        // Validate total length
        if (hostName.length() > 255) {
            throw new UnknownHostException("Host name too long: " + hostName);
        }
        // Exists empty labels?
        if (hostName.indexOf("..") != -1) {
            throw new UnknownHostException("Host name contains an empty label: " + hostName);
        }
        // Validate label length
        String[] labels = hostName.split("\\.");
        for (int i = 0; i < labels.length; i++) {
            if (StringUtil.isNullOrEmpty(labels[i])) {
                throw new UnknownHostException("Host name contains an empty label: " + hostName);
            }
            if (labels[i].length() > 63) {
                throw new UnknownHostException("Host name contains an label that is too long: " + hostName);
            }
        }
    }

    
    /**
     * Expands trailing octets that contains specified wildcard. 
     * A BGP prefix is returned.
     * <p>
     * Example: ("202.131.x.x", "x") returns "202.131.0.0/16", 
     * ("202.131.101.0", "0") returns "202.131.101.0/24".
     */
    private static String expandWildCardOctets(String ipRange, String wildCard) throws MegatronException {
        String result = ipRange;
        int mask = 32;
        boolean wildcardFound = true;
        while (wildcardFound) {
            int lengthBefore = result.length();
            result = StringUtil.removeSuffix(result, "." + wildCard);
            wildcardFound = result.length() < lengthBefore;
            if (wildcardFound) {
                mask -= 8;
            }
        }
        if (mask == 32) {
            result = ipRange;
        } else if ((mask < 8) || result.equals(wildCard)) {
            throw new MegatronException("Cannot expand wildcard IP-range: " + ipRange);
        } else {
            String suffix = StringUtil.nCopyString(".0", (32 - mask) / 8);
            result = result + suffix + "/" + mask;
        }
        
        return result;
    }

    
    private static long[] convertBgpPrefix(String bgpPrefix, int maxMask) throws MegatronException {
        // param check
        if (StringUtil.isNullOrEmpty(bgpPrefix) || (bgpPrefix.trim().length() < "0.0.0.0".length())) {
            throw new MegatronException("BGP-prefix is null or too small: " + bgpPrefix);
        }
        
        long[] result = new long[2];
        bgpPrefix = bgpPrefix.trim(); 
        bgpPrefix = bgpPrefix.endsWith("/") ? bgpPrefix.substring(0, bgpPrefix.length()-1) : bgpPrefix;
        int slashIndex = bgpPrefix.indexOf("/");
        if (slashIndex != -1) {
            // Format: 202.88.48.0/20 (endAddress: 202.88.63.255)
            // 202      . 88       . 48       . 0
            // =
            // 11001010 . 01011000 . 00110000 . 00000000
            // and
            // 11111111 . 11111111 . 11110000 . 00000000 mask with 20 1's; bitMaskHead
            // =
            // 11001010 . 01011000 . 0011
            // or
            //                           1111 . 11111111 bitMaskTail
            //
            // 11001010 . 01011000 . 00111111 . 11111111 (= 202.88.63.255)
            
            String startAddressStr = bgpPrefix.substring(0, slashIndex);
            try {
                result[0] = convertIpAddress(startAddressStr);
            } catch (UnknownHostException e) {
                throw new MegatronException("Cannot convert IP-address in BGP-prefix: " + bgpPrefix, e);
            }
            String maskStr = bgpPrefix.substring(slashIndex+1);
            int mask = 0;
            try {
                mask = Integer.parseInt(maskStr);
            } catch (NumberFormatException e) {
                throw new MegatronException("Cannot parse mask in BGP-prefix: " + maskStr + " (" + bgpPrefix + ").", e);
            }
            if ((mask <= 1) || (mask > maxMask)) {
                throw new MegatronException("Invalid BGP-prefix mask: " + mask + " (" + bgpPrefix + ").");
            }
            long bitMaskHead = ((1L << mask) - 1) << (32 - mask);
            long bitMaskTail = (1L << (32 - mask)) - 1;
            result[1] = (result[0] & bitMaskHead) | bitMaskTail;   
        } else {
            // Format: 202.131.0.0 (endAddress: 202.131.255.255)
            try {
                result[0] = convertIpAddress(bgpPrefix);
            } catch (UnknownHostException e) {
                throw new MegatronException("Cannot convert IP-address in BGP-prefix: " + bgpPrefix, e);
            }
            // add "#" as suffix temporary, e.g. "202.131.0.0#"
            String endAddressStr = bgpPrefix + "#";
            endAddressStr = StringUtil.replace(endAddressStr, ".0.0.0#", ".255.255.255#");
            endAddressStr = StringUtil.replace(endAddressStr, ".0.0#", ".255.255#");
            endAddressStr = StringUtil.replace(endAddressStr, ".0#", ".255#");
            endAddressStr = endAddressStr.substring(0, endAddressStr.length()-1);
            try {
                result[1] = convertIpAddress(endAddressStr);
            } catch (UnknownHostException e) {
                throw new MegatronException("Cannot convert end IP-address in BGP-prefix: " + bgpPrefix, e);
            }
        }
        return result;
    }

} public abstract class ObjectStringSorter implements Comparator<Object> {
    private static final Logger log = Logger.getLogger(ObjectStringSorter.class);

    /** String-comparer that is locale-sensitive */
    private Collator collator = null;
    /** Compare trailing numbers? */
    private boolean numberSensitive = true;
    /** Is a null value greater than a non-null value? */
    private boolean nullGreaterThanNotNull = true;


    /**
     * Returns default implementation of this class that uses toString() as
     * string representation.
     */
    public static synchronized ObjectStringSorter createDefaultSorter() {
        ObjectStringSorter result = new ObjectStringSorter() {
            @Override
            protected String getObjectString(Object obj) {
                return (obj != null) ? obj.toString() : null;
            }
        };

        return result;
    }

    /**
     * Same as createDefaultSorter(), but ignoring trailing numbers (better
     * performance).
     */
    public static synchronized ObjectStringSorter createBasicSorter() {
        ObjectStringSorter result = new ObjectStringSorter(false, true) {
            @Override
            protected String getObjectString(Object obj) {
                return (obj != null) ? obj.toString() : null;
            }
        };

        return result;
    }

    /**
     * Constructs a sorter that is number sensitive, i.e. trailing numbers
     * matters.
     */
    public ObjectStringSorter() {
        this.collator = Collator.getInstance();
    }

    /**
     * Constructor.
     *
     * @param numberSensitive
     *            Compare trailing numbers? If true, and the two strings to
     *            compare have numbers as suffixes and the same prefix, the
     *            numbers will be compared. Only integers are supported. Example
     *            when false: { "str1", "str10", "str2", "str20", "str3" }
     *            Example when true: { "str1", "str2", "str3", "str10", "str20" }
     * @param nullGreaterThanNotNull
     *            Is a null object string greater than a non-null value?
     */
    public ObjectStringSorter(boolean numberSensitive, boolean nullGreaterThanNotNull) {
        this();
        this.numberSensitive = numberSensitive;
        this.nullGreaterThanNotNull = nullGreaterThanNotNull;
    }

    /**
     * Compares string representation for specified objects, which may be null.
     * Implements Comparator.compare(Object, Object).
     */
    @Override
    public int compare(Object obj1, Object obj2) {
        String str1 = getObjectString(obj1);
        String str2 = getObjectString(obj2);

        if (str1 == str2)
            return 0;

        if (str1 == null)
            return this.nullGreaterThanNotNull ? 1 : -1;

        if (str2 == null)
            return this.nullGreaterThanNotNull ? -1 : 1;

        int result = collator.compare(str1, str2);

        if (this.numberSensitive && (result != 0) && isNumericSuffixAvailable(str1) && isNumericSuffixAvailable(str2)) {
            String[] headTailArray1 = splitNumericSuffixString(str1);
            String[] headTailArray2 = splitNumericSuffixString(str2);
            String prefix1 = headTailArray1[0];
            String prefix2 = headTailArray2[0];
            int prefixResult = collator.compare(prefix1, prefix2);
            // same prefix?
            if (prefixResult == 0) {
                try {
                    // compare as numbers
                    long long1 = Long.parseLong(headTailArray1[1]);
                    long long2 = Long.parseLong(headTailArray2[1]);
                    result = (long1 < long2) ? -1 : (long1 == long2) ? 0 : 1;
                } catch (NumberFormatException e) {
                    log.error("Internal Error: String should be a long.", e);
                }
            }
        }

        return result;
    }

    /** Gets the comparision string for the specified object */
    protected abstract String getObjectString(Object obj);

    /** Returns true if specifed string ends with a number. */
    private boolean isNumericSuffixAvailable(String str) {
        // assert: null-check already done
        if (str.length() == 0)
            return false;

        char lastChar = str.charAt(str.length() - 1);
        return (('0' <= lastChar) && (lastChar <= '9'));
    }

    /**
     * Splits specified string in two parts: head (string before numeric
     * suffix), and tail (numeric suffix or an empty string).
     * <p>
     * Example: "foo42" returns {"foo", "42"}, "foo" returns {"foo", ""}
     */
    private String[] splitNumericSuffixString(String str) {
        String[] result = new String[2];

        for (int i = (str.length() - 1); i >= 0; i--) {
            char ch = str.charAt(i);
            // is not a number?
            if (!(('0' <= ch) && (ch <= '9'))) {
                result[0] = str.substring(0, i + 1);
                result[1] = ((i + 1) < str.length()) ? str.substring(i + 1, str.length()) : "";
                return result;
            }
        }

        result[0] = "";
        result[1] = str;
        return result;
    }

} public abstract class StringUtil {


    /**
     * Replaces a substring in a string with another string. Replaces all
     * occurrences.
     * <p>
     * Example: replace("name=@name@", "@name@", "hubba") --> "name=hubba".
     *
     * @throws NullPointerException if the from- or to-argument is null.
     */
    public static String replace(String str, String from, String to) {
        return replaceInternal(str, from, to, false);
    }

    
    /**
     * As replace, but replace one the first occurrence.  
     */
    public static String replaceFirst(String str, String from, String to) {
        return replaceInternal(str, from, to, true);
    }


    /**
     * Removes prefix from specified string.<p>
     * Example: removePrefix("foobar.config", "foobar.") --> "config"
     */
    public static String removePrefix(String str, String prefix) {
        if ((str == null) || (prefix == null)) {
            return str;
        }

        String result = null;
        int hitIndex = str.indexOf(prefix);
        if (hitIndex == 0) {
            if (prefix.length() < str.length()) {
                result = str.substring(prefix.length());
            } else {
                result = "";
            }
        } else {
            result = str;
        }

        return result;
    }


    /**
     * Removes suffix from specified string.<p>
     * Example: removeSuffix("foobar.config", ".config") --> "foobar"
     */
    public static String removeSuffix(String str, String suffix) {
        if ((str == null) || (suffix == null)) {
            return str;
        }

        String result = str;
        int hitIndex = str.lastIndexOf(suffix);
        if ((hitIndex != -1) && ((hitIndex + suffix.length()) == str.length())) {
            result = result.substring(0, hitIndex);
        }
        return result;
    }


    /**
     * Splits specified string in two parts at delimiter position.
     *
     * @param str string to split.
     * @param delim delimiter that marks split between head and tail.
     * @param reverse is search for delimiter reverse (search starts at end of string)?
     * @return null if one argument is null, otherwise a String-array of length
     *         two in following format: { head, tail }. If delimiter is not
     *         found, then tail is empty if isReverse is false or
     *         head is empty if isReverse is true. Delimiter is not
     *         included.
     */
    public static String[] splitHeadTail(String str, String delim, boolean reverse) {
        // check param
        if ((str == null) || (delim == null)) {
            return null;
        }

        String[] result = new String[2];
        int hitPos = reverse ? str.lastIndexOf(delim) : str.indexOf(delim);
        if (hitPos != -1) {
            result[0] = str.substring(0, hitPos);
            int tailPos = hitPos + delim.length();
            if (tailPos < str.length()) {
                result[1] = str.substring(tailPos);
            } else {
                result[1] = "";
            }
        } else {
            if (reverse) {
                result[0] = "";
                result[1] = str;
            } else {
                result[0] = str;
                result[1] = "";
            }
        }

        return result;
    }


    /**
     * Returns true if specified string is null or empty.
     */
    public static boolean isNullOrEmpty(String str) {
        return str == null || str.length() == 0;
    }


    /**
     * Returns specified string if not null, otherwise an empty string.
     */
    public static String getNotNull(String str) {
        return (str != null) ? str : "";
    }


    /**
     * Returns specified string if not null, otherwise specified default value.
     */
    public static String getNotNull(String str, String defaultStr) {
        return (str != null) ? str : defaultStr;
    }


    /**
     * Converts specifed byte-array to hex-string.
     */
    public static String encode(byte[] buf) {
        StringBuffer result = new StringBuffer(2 * buf.length);

        for (int i = 0; i < buf.length; i++) {
            int byteAsInt = buf[i] & 0xFF;
            if (byteAsInt < 16) {
                result.append("0");
            }
            result.append(Integer.toString(byteAsInt, 16).toLowerCase());
        }

        return result.toString();
    }


    /**
     * Converts specified hex-string to byte-array.
     */
    public static byte[] decodeHexString(String hexStr) throws NumberFormatException {
        if (hexStr == null) {
            return null;
        }
        // is every digit of size two?
        if ((hexStr.length() % 2) != 0) {
            throw new NumberFormatException("Bad hex-string. Length of string must be even.");
        }

        byte[] result = new byte[hexStr.length() / 2];
        for (int i = 0; i < hexStr.length(); i += 2) {
            String hexDigit = hexStr.substring(i, i + 2);
            int byteValue = Integer.parseInt(hexDigit, 16);
            result[i / 2] = (byte)(byteValue & 0xFF);
        }

        return result;
    }


    /**
     * Encodes specifed text using named entities, e.g "<" converts
     * to "&amp;lt;".
     * <p>
     * The result is safe to include in XML or HTML.
     */
    public static String encodeCharacterEntities(String str) {
        if (str == null) {
            return null;
        }

        StringBuilder result = new StringBuilder(str.length() + 50);
        for (int i = 0; i < str.length(); i++) {
            char chr = str.charAt(i);
            switch (chr) {
                case '<':
                    result.append("&lt;");
                    break;
                case '>':
                    result.append("&gt;");
                    break;
                case '&':
                    result.append("&amp;");
                    break;
                case '"':
                    result.append("&quot;");
                    break;
                default:
                    result.append(chr);
            }
        }
        return result.toString();
    }

    
    /**
     * Removes line beaks from specified string.
     */
    public static String removeLineBreaks(String str, String replaceWith) {
        String result = str;
        result = replace(result, "\n", replaceWith);
        result = replace(result, "\r", replaceWith);
        return result;
    }


    /**
     * Removes trailing white spaces (including line breaks) from 
     * specified string.
     */
    public static String removeTrailingSpaces(String str) {
        if ((str == null) || (str.length() == 0)) {
            return str;
        }

        int endIndex = 0;
        for (int i = (str.length()-1); i >= 0; i--) {
            if (!Character.isWhitespace(str.charAt(i))) {
                endIndex = i + 1;
                break;
            }
        }
        
        return str.substring(0, endIndex);
    }
    
    
    /**
     * Removes enclosing characters from string.
     * Example: str="'foobar'", enclosingChars="'" will return "foobar". 
     */
    public static String removeEnclosingChars(String str, String enclosingChars) {
        if ((str == null) || !(str.startsWith(enclosingChars) && str.endsWith(enclosingChars))) {
            return str;
        }

        return removeSuffix(removePrefix(str, enclosingChars), enclosingChars); 
    }

    
    /**
     * Truncates specified string if longer than max length. "..." is added
     * as suffix to mark that the string have been truncated.
     */
    public static String truncateString(String str, int maxLength) {
        if ((str == null) || (str.length() <= maxLength)) {
            return str;
        }
        final String suffix = "...";
        if (maxLength < suffix.length()) {
            return suffix;
        }
        
        StringBuilder result = new StringBuilder(maxLength);
        int endIndex = maxLength - suffix.length();
        result.append(str.substring(0, endIndex)).append(suffix);

        return result.toString();
    }

    
    /**
     * Returns a string containing strToCopy duplicated
     * noOfCopies times.
     */
    public static String nCopyString(String strToCopy, int noOfCopies) {
        // check param
        if (strToCopy == null) {
            return null;
        }

        StringBuilder result = new StringBuilder(noOfCopies * strToCopy.length());
        for (int i = 0; i < noOfCopies; i++) {
            result.append(strToCopy);
        }

        return result.toString();
    }

    
    /**
     * Pads specified string in head with spaces.
     */
    public static String leftPad(String str, int len) {
        return pad(str, len, " ", true);
    }


    /**
     * Pads specified string in tail with spaces.
     */
    public static String rightPad(String str, int len) {
        return pad(str, len, " ", false);
    }


    /**
     * Pads specified string in head with pad-string.
     */
    public static String leftPad(String str, int len, String padString) {
        return pad(str, len, padString, true);
    }


    /**
     * Pads specified string in tail with pad-string.
     */
    public static String rightPad(String str, int len, String padString) {
        return pad(str, len, padString, false);
    }

    
    /**
     * Returns specified files as a string list. Returns never null.
     */
    public static String toString(List<File> files, boolean shortName) {
        if (files == null) {
            return "[null]";
        }

        StringBuilder result = new StringBuilder(256);
        for (Iterator<File> iterator = files.iterator(); iterator.hasNext(); ) {
            File file = iterator.next();
            if (result.length() > 0) {
                result.append(", ");
            }

            String filename = shortName ? file.getName() : file.getAbsolutePath();
            result.append(filename);
        }

        return result.toString();
    }

    
    /**
     * Returns specified list as a string where every element is quoted and
     * comma separated.
     */
    public static String toQuotedString(List<?> list) {
        return toQuotedString(list, "\"");
    }

    
    /**
     * As toQuotedString, but use a single quote instead of a double quote.
     */
    public static String toSingleQuotedString(List<?> list) {
        return toQuotedString(list, "\'");
    }

    
    /**
     * Returns specified list as a string where every element is quoted with
     * specified character and comma separated.
     */
    private static String toQuotedString(List<?> list, String quoteChar) {
        if ((list == null) || (list.size() == 0)) {
            return "";
        }
        
        StringBuffer result = new StringBuffer(256);
        for (Iterator<?> iterator = list.iterator(); iterator.hasNext(); ) {
            Object obj = iterator.next();
            String str = (obj != null) ? obj.toString() : "";
            if (result.length() > 0) {
                result.append(",");
            }
            result.append(quoteChar).append(str).append(quoteChar);
        }

        return result.toString();
    }

    
    /**
     * Pads specified string with pad-string.
     */
    private static String pad(String str, int len, String padString, boolean leftPad) {
        if ((str == null) || (str.length() >= len) || (padString == null)) {
            return str;
        }

        int n = (len - str.length()) / padString.length();
        String paddedString = nCopyString(padString, n);
        StringBuilder result = new StringBuilder(len);
        if (leftPad) {
            result.append(paddedString).append(str);
        } else {
            result.append(str).append(paddedString);
        }

        return result.toString();
    }
    
    
    @SuppressWarnings("null")
    private static String replaceInternal(String str, String from, String to, boolean replaceFirst) {
        if (str == null) {
            return null;
        }
        if ((from == null) || (to == null)) {
            throw new NullPointerException("from- or to-argument is null.");
        }

        StringBuilder result = null;
        int fromlength = 0;
        int index = 0;
        boolean quit = false;
        while (!quit) {
            int hitIndex = str.indexOf(from, index);
            if (hitIndex < 0) {
                break;
            }
            if (result == null) {
                result = new StringBuilder(str.length() + 40);
                fromlength = from.length();
            }
            result.append(str.substring(index, hitIndex));
            result.append(to);
            index = hitIndex + fromlength;
            quit = replaceFirst;
        }
        if (index == 0) {
            return str;
        }
        result.append(str.substring(index));
        return result.toString();
    }


}
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.log4j.Logger;
import org.joda.time.DateTime;
import org.joda.time.Days;
import se.sitic.megatron.db.DbManager;
import se.sitic.megatron.entity.Job;
import se.sitic.megatron.entity.LogEntry;
import se.sitic.megatron.entity.MailJob;
import se.sitic.megatron.entity.Organization;
import se.sitic.megatron.entity.Priority;
import se.sitic.megatron.util.AppUtil;
import se.sitic.megatron.util.Constants;
import se.sitic.megatron.util.DateUtil;
import se.sitic.megatron.util.IpAddressUtil;
import se.sitic.megatron.util.ObjectStringSorter;
import se.sitic.megatron.util.StringUtil;
package se.sitic.megatron.core;





/**
 * Fetches data about a log job and writes the info to stdout or to a string.
 * Handles output for the "--job-info" switch.
 */
public class JobInfoWriter {
    private final Logger log = Logger.getLogger(JobInfoWriter.class);
    private static final int MAX_NO_OF_LOG_ENTRIES = 2000;
    private static final String INIT_PRIO_FAILED_MSG = "Interval list in priority not supported for job info. Use a simple value, e.g. --prio 45.";
    private static final String MORE_LOG_ENTRIES_EXISTS_MSG = "Note: Not all log entries listed. Number of log entries exceeded threshold.";
    
    private static final String SECTION_HEADER_TEMPLATE  = "==== $organizationName ($prio) ====";
    private static final String SECTION_ROW_TEMPLATE = "$ipAddress#padRight15 $hostname#padRight35 Last Seen: $lastSeen#padRight25 Last Emailed: $lastEmailed#padRight25 $org2";
    private static final String SECTION_FOOTER_TEMPLATE  = "To: $toAddresses";
    
    private TypedProperties props; 
    private boolean toStdout;
    private DbManager dbManager;
    private Job job;
    private boolean useHighPriorityThreshold;
    private int prio;
    private int quarantinePeriodInSecs; 
    private boolean initPrioFailed = false;
    private Map<Organization, List<LogEntry>> organizationMap;
    private Map<String, String> attrMap;
    private Set<LogEntry> quarantinedLogEntries;
    private StringBuilder rows;
    private boolean moreLogEntriesExists;
    private int noOfIps;
    private int noOfQuarantinedIps;

    
    /**
     * Creates a writer that writes to stdout as soon as data are available. 
     */
    public JobInfoWriter(TypedProperties globalProps) {
        this.props = globalProps;
        this.toStdout = true;
        useHighPriorityThreshold = false;
    }

    
    /**
     * Creates a writer that stores data in a map, which is used in
     * createContent to substitute variables in a template. Use this
     * method e.g. to create a notification mail. 
     */
    public JobInfoWriter(TypedProperties props, DbManager dbManager, Job job) {
        this.props = props;
        this.dbManager = dbManager;
        this.job = job;
        this.toStdout = false;
        useHighPriorityThreshold = true;
    }

    
    public void execute() throws MegatronException {
        if (!initPrio()) {
            return;
        }
        
        boolean dbManagerCreated = false;
        try {
            long t1 = System.currentTimeMillis();
            if (dbManager == null) {
                dbManager = DbManager.createDbManager(props);
                dbManagerCreated = true;
            }
            init();
            
            // write header
            write("Job Name: " + job.getName());
            write("Filename: " + job.getFilename() + " (" + job.getFileSize() + " bytes, " + job.getProcessedLines() + " lines)" + Constants.LINE_BREAK);
            write("Organizations with priority " + prio + " or higher:" + Constants.LINE_BREAK);
            
            // fetching log entries and build map
            List<LogEntry> logEntries = dbManager.listLogEntries(job.getId(), prio, 0, MAX_NO_OF_LOG_ENTRIES);
            if (logEntries.size() >= MAX_NO_OF_LOG_ENTRIES) {
                moreLogEntriesExists = true;
            }
            organizationMap = createOrganizationMap(logEntries);
            
            // write rows
            List<Organization> organizations = new ArrayList<Organization>(organizationMap.keySet());
            sortOrganizationsByPriority(organizations);
            for (Iterator<Organization> iterator = organizations.iterator(); iterator.hasNext(); ) {
                Organization organization = iterator.next();
                List<LogEntry> logEntriesForOrg = organizationMap.get(organization);
                sortLogEntriesById(logEntriesForOrg);
                noOfIps += writeSection(organization, logEntriesForOrg);
            }

            write("");
            write("No. of Quarantined IPs: " + noOfQuarantinedIps + " of " + noOfIps);    
            
            if (moreLogEntriesExists) {
                write("");
                write(MORE_LOG_ENTRIES_EXISTS_MSG);
            }
            
            addJobInfo(logEntries);
            
            String durationStr = DateUtil.formatDuration(System.currentTimeMillis() - t1);
            log.debug("Duration for job info: " + durationStr);
        } finally {
            if (dbManagerCreated) {
                dbManager.close();
            }
        }
    }

    
    public String createContent(String template) throws ConversionException {
        if (initPrioFailed) {
            return INIT_PRIO_FAILED_MSG;
        }
        return AppUtil.replaceVariables(template, attrMap, false, "template-parameter in createContent");