public void updateData(SiteInformation siteInformation) throws LIMSRuntimeException {

    SiteInformation oldData = readSiteInformation(siteInformation.getId());
    SiteInformation newData = siteInformation;

    try {
      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();

      auditDAO.saveHistory(
          newData,
          oldData,
          siteInformation.getSysUserId(),
          IActionConstants.AUDIT_TRAIL_UPDATE,
          "SITE_INFORMATION");
    } catch (Exception e) {
      LogEvent.logError("SiteInformationDAOImpl", "AuditTrail updateData()", e.toString());
      throw new LIMSRuntimeException("Error in SiteInformation AuditTrail updateData()", e);
    }

    try {
      HibernateUtil.getSession().merge(siteInformation);
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
      HibernateUtil.getSession().evict(siteInformation);
      HibernateUtil.getSession().refresh(siteInformation);
    } catch (Exception e) {
      LogEvent.logError("SiteInformationsDAOImpl", "updateData()", e.toString());
      throw new LIMSRuntimeException("Error in SiteInformation updateData()", e);
    }
  }
  public void updateData(ReferenceTables referenceTables) throws LIMSRuntimeException {
    // bugzilla 1482 throw Exception if record already exists

    // String isHl7Encoded;
    // String keepHistory;
    boolean isNew = false;

    /*isHl7Encoded = referencetables.getIsHl7Encoded();
    System.out.println ("Yi isH17Encodded is " + isHl7Encoded);
    if (StringUtil.isNullorNill(isHl7Encoded) || "0".equals(isHl7Encoded)) {
    	referencetables.setIsHl7Encoded ("N");
    }

    keepHistory = referencetables.getKeepHistory();
    System.out.println ("Yi isH17Encodded is " + keepHistory);
    if (StringUtil.isNullorNill(keepHistory) || "0".equals(keepHistory)) {
    	referencetables.setKeepHistory ("N");
    }*/

    try {
      if (duplicateReferenceTablesExists(referenceTables, isNew)) {
        throw new LIMSDuplicateRecordException(
            "Duplicate record exists for " + referenceTables.getTableName());
      }
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "updateData()", e.toString());
      throw new LIMSRuntimeException("Error in Referencetables updateData()", e);
    }

    // System.out.println("This is name from updateData " + referencetables.getTableName());
    ReferenceTables oldData = (ReferenceTables) readReferenceTables(referenceTables.getId());
    ReferenceTables newData = referenceTables;

    // System.out.println("updateDate " + newData.getTableName() + " " + oldData.getTableName());
    // add to audit trail
    try {
      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();
      String sysUserId = referenceTables.getSysUserId();
      String event = IActionConstants.AUDIT_TRAIL_UPDATE;
      String tableName = "REFERENCE_TABLES";
      auditDAO.saveHistory(newData, oldData, sysUserId, event, tableName);
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "AuditTrail updateData()", e.toString());
      throw new LIMSRuntimeException("Error in Referencetables AuditTrail updateData()", e);
    }

    try {
      HibernateUtil.getSession().merge(referenceTables);
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
      HibernateUtil.getSession().evict(referenceTables);
      HibernateUtil.getSession().refresh(referenceTables);
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "updateData()", e.toString());
      throw new LIMSRuntimeException("Error in Referencetables updateData()", e);
    }
  }
  public void updateData(SampleHuman sampleHuman) throws LIMSRuntimeException {

    SampleHuman oldData = readSampleHuman(sampleHuman.getId());
    SampleHuman newData = sampleHuman;

    // add to audit trail
    try {
      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();
      String sysUserId = sampleHuman.getSysUserId();
      String event = IActionConstants.AUDIT_TRAIL_UPDATE;
      String tableName = "SAMPLE_HUMAN";
      auditDAO.saveHistory(newData, oldData, sysUserId, event, tableName);
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("SampleHumanDAOImpl", "updateData()", e.toString());
      throw new LIMSRuntimeException("Error in SampleHuman AuditTrail updateData()", e);
    }

    try {
      HibernateUtil.getSession().merge(sampleHuman);
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
      HibernateUtil.getSession().evict(sampleHuman);
      HibernateUtil.getSession().refresh(sampleHuman);
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("SampleHumanDAOImpl", "updateData()", e.toString());
      throw new LIMSRuntimeException("Error in SampleHuman updateData()", e);
    }
  }
  // bugzilla 1427
  public List getPreviousRecord(String id, String table, Class clazz) throws LIMSRuntimeException {
    int currentId = (Integer.valueOf(id)).intValue();
    String tablePrefix = getTablePrefix(table);

    List list = new Vector();
    // bugzilla 1908
    int rrn = 0;
    try {
      // bugzilla 1908 cannot use named query for postgres because of oracle ROWNUM
      // instead get the list in this sortorder and determine the index of record with id =
      // currentId
      String sql = "select r.id from ReferenceTables r order by r.tableName";
      org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql);
      list = query.list();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
      rrn = list.indexOf(String.valueOf(currentId));

      list =
          HibernateUtil.getSession()
              .getNamedQuery(tablePrefix + "getPrevious")
              .setFirstResult(rrn - 1)
              .setMaxResults(2)
              .list();

    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "getPreviousRecord()", e.toString());
      throw new LIMSRuntimeException("Error in getPreviousRecord() for " + table, e);
    }

    return list;
  }
  public void deleteData(String siteInformationId, String currentUserId)
      throws LIMSRuntimeException {

    try {
      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();

      SiteInformation oldData = readSiteInformation(siteInformationId);
      SiteInformation newData = new SiteInformation();

      auditDAO.saveHistory(
          newData, oldData, currentUserId, IActionConstants.AUDIT_TRAIL_DELETE, "SITE_INFORMATION");

    } catch (Exception e) {
      LogEvent.logError("SiteInformationDAOImpl", "AuditTrail deleteData()", e.toString());
      throw new LIMSRuntimeException("Error in SiteInformation AuditTrail deleteData()", e);
    }

    try {
      SiteInformation siteInformation = readSiteInformation(siteInformationId);
      HibernateUtil.getSession().delete(siteInformation);
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();

    } catch (Exception e) {
      LogEvent.logError("SiteInformationsDAOImpl", "deleteData()", e.toString());
      throw new LIMSRuntimeException("Error in SiteInformation deleteData()", e);
    }
  }
  public SiteInformation readSiteInformation(String idString) {
    SiteInformation recoveredSiteInformation = null;
    try {
      recoveredSiteInformation =
          (SiteInformation) HibernateUtil.getSession().get(SiteInformation.class, idString);
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (Exception e) {
      LogEvent.logError("SiteInformationDAOImpl", "readSiteInformation()", e.toString());
      throw new LIMSRuntimeException("Error in SiteInformation readSiteInformation()", e);
    }

    return recoveredSiteInformation;
  }
  public SampleHuman readSampleHuman(String idString) {
    SampleHuman sh = null;
    try {
      sh = (SampleHuman) HibernateUtil.getSession().get(SampleHuman.class, idString);
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("SampleHumanDAOImpl", "readSampleHuman()", e.toString());
      throw new LIMSRuntimeException("Error in SampleHuman readSampleHuman()", e);
    }

    return sh;
  }
  public ReferenceTables readReferenceTables(String idString) {
    ReferenceTables referenceTables = null;
    try {
      referenceTables =
          (ReferenceTables) HibernateUtil.getSession().get(ReferenceTables.class, idString);
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "readReferenceTables()", e.toString());
      throw new LIMSRuntimeException("Error in Referencetables readReferenceTables(idString)", e);
    }

    return referenceTables;
  }
  @SuppressWarnings("unchecked")
  public List<PatientIdentityType> getAllPatientIdenityTypes() throws LIMSRuntimeException {
    List<PatientIdentityType> list = null;
    try {
      String sql = "from PatientIdentityType";
      Query query = HibernateUtil.getSession().createQuery(sql);

      list = query.list();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (HibernateException e) {
      handleException(e, "getAllPatientIdenityTypes");
    }

    return list;
  }
  // bugzilla 1482
  private boolean duplicateReferenceTablesExists(ReferenceTables referenceTables, boolean isNew)
      throws LIMSRuntimeException {
    try {

      List list = new ArrayList();
      String sql;

      // not case sensitive hemolysis and Hemolysis are considered
      // duplicates
      if (isNew) {
        sql = "from ReferenceTables t where trim(lower(t.tableName)) = :param";
      } else {
        sql = "from ReferenceTables t where trim(lower(t.tableName)) = :param and id != :param2";
      }

      // System.out.println("Yi in duplicateReferencetables sql is " + sql);
      org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql);
      // System.out.println("duplicateReferencetables sql is " + sql);

      query.setParameter("param", referenceTables.getTableName().toLowerCase().trim());

      // initialize with 0 (for new records where no id has been generated
      // yet
      String referenceTablesId = "0";
      if (!StringUtil.isNullorNill(referenceTables.getId())) {
        referenceTablesId = referenceTables.getId();
      }

      if (!isNew) {
        query.setParameter("param2", referenceTablesId);
      }
      list = query.list();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();

      if (list.size() > 0) {
        return true;
      } else {
        return false;
      }

    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "duplicateReferenceTablesExists()", e.toString());
      throw new LIMSRuntimeException("Error in duplicateReferenceTablesExists()", e);
    }
  }
 public void getData(SiteInformation siteInformation) throws LIMSRuntimeException {
   try {
     SiteInformation tmpSiteInformation =
         (SiteInformation)
             HibernateUtil.getSession().get(SiteInformation.class, siteInformation.getId());
     HibernateUtil.getSession().flush();
     HibernateUtil.getSession().clear();
     if (tmpSiteInformation != null) {
       PropertyUtils.copyProperties(siteInformation, tmpSiteInformation);
     } else {
       siteInformation.setId(null);
     }
   } catch (Exception e) {
     LogEvent.logError("SiteInformationsDAOImpl", "getData()", e.toString());
     throw new LIMSRuntimeException("Error in SiteInformation getData()", e);
   }
 }
 public void getData(SampleHuman sampleHuman) throws LIMSRuntimeException {
   try {
     SampleHuman sampHuman =
         (SampleHuman) HibernateUtil.getSession().get(SampleHuman.class, sampleHuman.getId());
     HibernateUtil.getSession().flush();
     HibernateUtil.getSession().clear();
     if (sampHuman != null) {
       PropertyUtils.copyProperties(sampleHuman, sampHuman);
     } else {
       sampleHuman.setId(null);
     }
   } catch (Exception e) {
     // bugzilla 2154
     LogEvent.logError("SampleHumanDAOImpl", "getData()", e.toString());
     throw new LIMSRuntimeException("Error in SampleHuman getData()", e);
   }
 }
  public Patient getPatientForSample(Sample sample) throws LIMSRuntimeException {
    Patient patient = null;
    try {
      String sql =
          "select patient from Patient as patient, SampleHuman as sampleHuman where sampleHuman.patientId = patient.id and sampleHuman.sampleId = :sId";
      Query query = HibernateUtil.getSession().createQuery(sql);
      query.setInteger("sId", Integer.parseInt(sample.getId()));
      patient = (Patient) query.uniqueResult();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();

    } catch (HibernateException he) {
      LogEvent.logError("SampleHumanDAOImpl", "getPatientForSample()", he.toString());
      throw new LIMSRuntimeException("Error in SampleHuman getPatientForSample()", he);
    }

    return patient;
  }
  public List getAllReferenceTables() throws LIMSRuntimeException {
    List list = new Vector();
    try {
      String sql = "from ReferenceTables";
      org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql);
      // query.setMaxResults(10);
      // query.setFirstResult(3);
      list = query.list();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "getAllReferenceTables()", e.toString());
      throw new LIMSRuntimeException("Error in Referencetables getAllReferenceTables()", e);
    }

    return list;
  }
 public void getData(ReferenceTables referenceTables) throws LIMSRuntimeException {
   try {
     ReferenceTables reftbl =
         (ReferenceTables)
             HibernateUtil.getSession().get(ReferenceTables.class, referenceTables.getId());
     HibernateUtil.getSession().flush();
     HibernateUtil.getSession().clear();
     if (reftbl != null) {
       PropertyUtils.copyProperties(referenceTables, reftbl);
     } else {
       referenceTables.setId(null);
     }
   } catch (Exception e) {
     // bugzilla 2154
     LogEvent.logError("ReferenceTablesDAOImpl", "getData()", e.toString());
     throw new LIMSRuntimeException("Error in Referencetables getData()", e);
   }
 }
  public boolean insertData(ReferenceTables referenceTables) throws LIMSRuntimeException {

    // String isHl7Encoded;
    // String keepHistory;
    boolean isNew = true;

    /*isHl7Encoded = referencetables.getIsHl7Encoded();
    if (StringUtil.isNullorNill(isHl7Encoded) || "0".equals(isHl7Encoded)) {
    	referencetables.setIsHl7Encoded ("N");
    }

    keepHistory = referencetables.getKeepHistory();
    if (StringUtil.isNullorNill(keepHistory) || "0".equals(keepHistory)) {
    	referencetables.setKeepHistory ("N");
    }*/

    try {
      // bugzilla 1482 throw Exception if record already exists
      if (duplicateReferenceTablesExists(referenceTables, isNew)) {
        throw new LIMSDuplicateRecordException(
            "Duplicate record exists for " + referenceTables.getTableName());
      }

      // System.out.println("This is ID from insert referencetables " + referencetables.getId());
      String id = (String) HibernateUtil.getSession().save(referenceTables);
      referenceTables.setId(id);

      // bugzilla 1824 inserts will be logged in history table
      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();
      String sysUserId = referenceTables.getSysUserId();
      String tableName = "REFERENCE_TABLES";
      auditDAO.saveNewHistory(referenceTables, sysUserId, tableName);

      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "insertData()", e.toString());
      throw new LIMSRuntimeException("Error in Referencetables insertData()", e);
    }

    return true;
  }
  public void getDataBySample(SampleHuman sampleHuman) throws LIMSRuntimeException {

    try {
      String sql = "from SampleHuman sh where samp_id = :param";
      Query query = HibernateUtil.getSession().createQuery(sql);
      query.setInteger("param", Integer.parseInt(sampleHuman.getSampleId()));
      List list = query.list();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
      SampleHuman sh = null;
      if (list.size() > 0) {
        sh = (SampleHuman) list.get(0);
        PropertyUtils.copyProperties(sampleHuman, sh);
      }
    } catch (Exception e) {
      LogEvent.logError("SampleHumanDAOImpl", "getDataBySample()", e.toString());
      throw new LIMSRuntimeException("Error in SampleHuman getDataBySample()", e);
    }
  }
  public boolean insertData(SiteInformation siteInformation) throws LIMSRuntimeException {

    try {
      String id = (String) HibernateUtil.getSession().save(siteInformation);
      siteInformation.setId(id);

      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();
      auditDAO.saveNewHistory(siteInformation, siteInformation.getSysUserId(), "SITE_INFORMATION");

      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();

    } catch (Exception e) {
      LogEvent.logError("SiteInformationDAOImpl", "insertData()", e.toString());
      throw new LIMSRuntimeException("Error in SiteInformation insertData()", e);
    }

    return true;
  }
  @SuppressWarnings("unchecked")
  public List<Sample> getCollectedSamplesForPatient(String patientID) throws LIMSRuntimeException {

    List<Sample> samples;

    try {
      String sql =
          "select sample from Sample as sample, SampleHuman as sampleHuman where sampleHuman.sampleId = sample.id and sampleHuman.patientId = :patientId and sample.accessionNumber is not null order by sample.id";
      Query query = HibernateUtil.getSession().createQuery(sql);
      query.setInteger("patientId", Integer.parseInt(patientID));
      samples = query.list();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (HibernateException he) {
      LogEvent.logError("SampleHumanDAOImpl", "getSamplesForPatient()", he.toString());
      throw new LIMSRuntimeException("Error in SampleHuman getSamplesForPatient()", he);
    }

    return samples;
  }
  @SuppressWarnings("unchecked")
  public List<SiteInformation> getAllSiteInformation() throws LIMSRuntimeException {
    List<SiteInformation> list = null;
    try {
      String sql = "from SiteInformation";
      org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql);
      list = query.list();
    } catch (Exception e) {
      throw new LIMSRuntimeException("Error in SiteInformation getAllSiteInformation()", e);
    }

    return list;
  }
  public void deleteData(List referenceTableses) throws LIMSRuntimeException {
    // add to audit trail
    try {
      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();
      for (int i = 0; i < referenceTableses.size(); i++) {

        ReferenceTables data = (ReferenceTables) referenceTableses.get(i);

        ReferenceTables oldData = (ReferenceTables) readReferenceTables(data.getId());
        ReferenceTables newData = new ReferenceTables();

        String sysUserId = data.getSysUserId();
        String event = IActionConstants.AUDIT_TRAIL_DELETE;
        String tableName = "REFERENCE_TABLES";
        auditDAO.saveHistory(newData, oldData, sysUserId, event, tableName);
      }
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "AuditTrail deleteData()", e.toString());
      throw new LIMSRuntimeException("Error in ReferenceTables AuditTrail deleteData()", e);
    }

    try {
      for (int i = 0; i < referenceTableses.size(); i++) {

        ReferenceTables data = (ReferenceTables) referenceTableses.get(i);
        // bugzilla 2206
        data = (ReferenceTables) readReferenceTables(data.getId());
        HibernateUtil.getSession().delete(data);
        HibernateUtil.getSession().flush();
        HibernateUtil.getSession().clear();
      }
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "deleteData()", e.toString());
      throw new LIMSRuntimeException("Error in ReferenceTables deleteData()", e);
    }
  }
  public void insertData(PatientIdentityType patientIdentityType) throws LIMSRuntimeException {
    try {

      if (duplicatePatientIdentityTypeExists(patientIdentityType)) {
        throw new LIMSDuplicateRecordException(
            "Duplicate record exists for " + patientIdentityType.getIdentityType());
      }

      String id = (String) HibernateUtil.getSession().save(patientIdentityType);
      patientIdentityType.setId(id);

      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();
      auditDAO.saveNewHistory(
          patientIdentityType, patientIdentityType.getSysUserId(), "PATIENT_IDENTITY_TYPE");

      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (HibernateException e) {
      handleException(e, "insertData");
    } catch (LIMSDuplicateRecordException e) {
      handleException(e, "insertData");
    }
  }
  public boolean insertData(SampleHuman sampleHuman) throws LIMSRuntimeException {

    try {
      String id = (String) HibernateUtil.getSession().save(sampleHuman);
      sampleHuman.setId(id);

      // bugzilla 1824 inserts will be logged in history table
      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();
      String sysUserId = sampleHuman.getSysUserId();
      String tableName = "SAMPLE_HUMAN";
      auditDAO.saveNewHistory(sampleHuman, sysUserId, tableName);

      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();

    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("SampleHumanDAOImpl", "insertData()", e.toString());
      throw new LIMSRuntimeException("Error in SampleHuman insertData()", e);
    }

    return true;
  }
  @Override
  public SiteInformation getSiteInformationById(String id) throws LIMSRuntimeException {

    try {
      SiteInformation info =
          (SiteInformation) HibernateUtil.getSession().get(SiteInformation.class, id);
      closeSession();
      return info;
    } catch (HibernateException e) {
      handleException(e, "getSiteInformationById");
    }

    return null;
  }
  public void deleteData(List sampleHumans) throws LIMSRuntimeException {
    // add to audit trail
    try {
      AuditTrailDAO auditDAO = new AuditTrailDAOImpl();
      for (int i = 0; i < sampleHumans.size(); i++) {
        SampleHuman data = (SampleHuman) sampleHumans.get(i);

        SampleHuman oldData = (SampleHuman) readSampleHuman(data.getId());
        SampleHuman newData = new SampleHuman();

        String sysUserId = data.getSysUserId();
        String event = IActionConstants.AUDIT_TRAIL_DELETE;
        String tableName = "SAMPLE_HUMAN";
        auditDAO.saveHistory(newData, oldData, sysUserId, event, tableName);
      }
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("SampleHumanDAOImpl", "AuditTrail deleteData()", e.toString());
      throw new LIMSRuntimeException("Error in SampleHuman AuditTrail deleteData()", e);
    }

    try {
      for (int i = 0; i < sampleHumans.size(); i++) {
        SampleHuman data = (SampleHuman) sampleHumans.get(i);
        // bugzilla 2206
        data = (SampleHuman) readSampleHuman(data.getId());
        HibernateUtil.getSession().delete(data);
        HibernateUtil.getSession().flush();
        HibernateUtil.getSession().clear();
      }
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("SampleHumanDAOImpl", "deleteData()", e.toString());
      throw new LIMSRuntimeException("Error in SampleHuman deleteData()", e);
    }
  }
  public List getPageOfReferenceTables(int startingRecNo) throws LIMSRuntimeException {
    List list = new Vector();
    try {
      // calculate maxRow to be one more than the page size
      int endingRecNo =
          startingRecNo + (SystemConfiguration.getInstance().getDefaultPageSize() + 1);

      String sql = "from ReferenceTables r order by r.tableName";
      org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql);
      query.setFirstResult(startingRecNo - 1);
      query.setMaxResults(endingRecNo - 1);
      // query.setCacheMode(org.hibernate.CacheMode.REFRESH);

      list = query.list();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (Exception e) {
      // bugzilla 2154
      LogEvent.logError("ReferenceTablesDAOImpl", "getPageOfReferenceTables()", e.toString());
      throw new LIMSRuntimeException("Error in Referencetables getPageOfReferenceTables()", e);
    }

    return list;
  }
  public PatientIdentityType getNamedIdentityType(String name) throws LIMSRuntimeException {
    String sql = "from PatientIdentityType t where t.identityType = :identityType";

    try {
      Query query = HibernateUtil.getSession().createQuery(sql);
      query.setString("identityType", name);
      PatientIdentityType pit = (PatientIdentityType) query.uniqueResult();
      closeSession();
      return pit;
    } catch (HibernateException e) {
      handleException(e, "getNamedIdentityType");
    }

    return null;
  }
  @Override
  public int getCountForDomainName(String domainName) throws LIMSRuntimeException {
    String sql = "select count(*) from SiteInformation si where si.domain.name = :domainName";

    try {
      Query query = HibernateUtil.getSession().createQuery(sql);
      query.setString("domainName", domainName);
      Integer count = ((Long) query.uniqueResult()).intValue();
      closeSession();
      return count;
    } catch (HibernateException e) {
      handleException(e, "getSiteInformationForDomainName");
    }
    return 0;
  }
  @SuppressWarnings("unchecked")
  public List<SiteInformation> getPageOfSiteInformationByDomainName(
      int startingRecNo, String domainName) throws LIMSRuntimeException {
    List<SiteInformation> list = null;
    try {

      int endingRecNo =
          startingRecNo + (SystemConfiguration.getInstance().getDefaultPageSize() + 1);

      String sql = "from SiteInformation si where si.domain.name = :domainName order by si.name";
      Query query = HibernateUtil.getSession().createQuery(sql);
      query.setString("domainName", domainName);
      query.setFirstResult(startingRecNo - 1);
      query.setMaxResults(endingRecNo - 1);

      list = query.list();
      HibernateUtil.getSession().flush();
      HibernateUtil.getSession().clear();
    } catch (Exception e) {
      handleException(e, "getPageOfSiteInformationByDomainName");
    }

    return list;
  }
  @Override
  public SiteInformation getSiteInformationByName(String siteName) throws LIMSRuntimeException {
    String sql = "From SiteInformation si where name = :name";

    try {
      Query query = HibernateUtil.getSession().createQuery(sql);
      query.setString("name", siteName);
      SiteInformation information = (SiteInformation) query.uniqueResult();
      //			closeSession();
      return information;
    } catch (HibernateException e) {
      handleException(e, "getSiteInformationByName");
    }

    return null;
  }