Java tutorial
/** * The contents of this file are subject to the Mozilla Public License * Version 1.1 (the "License"); you may not use this file except in * compliance with the License. You may obtain a copy of the License at * http://www.mozilla.org/MPL/ * * Software distributed under the License is distributed on an "AS IS" * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the * License for the specific language governing rights and limitations under * the License. * * The Original Code is OpenELIS code. * * Copyright (C) The Minnesota Department of Health. All Rights Reserved. * * Contributor(s): CIRG, University of Washington, Seattle WA. */ package us.mn.state.health.lims.sample.daoimpl; import java.sql.Date; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Calendar; import java.util.List; import java.util.Locale; import java.util.Vector; import org.apache.commons.beanutils.PropertyUtils; import org.apache.commons.validator.GenericValidator; import org.hibernate.HibernateException; import org.hibernate.Query; import us.mn.state.health.lims.audittrail.dao.AuditTrailDAO; import us.mn.state.health.lims.audittrail.daoimpl.AuditTrailDAOImpl; import us.mn.state.health.lims.common.action.IActionConstants; import us.mn.state.health.lims.common.daoimpl.BaseDAOImpl; import us.mn.state.health.lims.common.exception.LIMSRuntimeException; import us.mn.state.health.lims.common.log.LogEvent; import us.mn.state.health.lims.common.util.DateUtil; import us.mn.state.health.lims.common.util.SystemConfiguration; import us.mn.state.health.lims.hibernate.HibernateUtil; import us.mn.state.health.lims.sample.dao.SampleDAO; import us.mn.state.health.lims.sample.valueholder.Sample; /** * @author diane benz */ public class SampleDAOImpl extends BaseDAOImpl implements SampleDAO { private static final String ACC_NUMBER_SEQ_BEGIN = "000001"; public void deleteData(List samples) throws LIMSRuntimeException { // add to audit trail try { AuditTrailDAO auditDAO = new AuditTrailDAOImpl(); for (int i = 0; i < samples.size(); i++) { Sample data = (Sample) samples.get(i); Sample oldData = (Sample) readSample(data.getId()); Sample newData = new Sample(); String sysUserId = data.getSysUserId(); String event = IActionConstants.AUDIT_TRAIL_DELETE; String tableName = "SAMPLE"; auditDAO.saveHistory(newData, oldData, sysUserId, event, tableName); } } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "AuditTrail deleteData()", e.toString()); throw new LIMSRuntimeException("Error in Sample AuditTrail deleteData()", e); } try { for (int i = 0; i < samples.size(); i++) { Sample data = (Sample) samples.get(i); //bugzilla 2206 data = (Sample) readSample(data.getId()); HibernateUtil.getSession().delete(data); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); } } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "deleteData()", e.toString()); throw new LIMSRuntimeException("Error in Sample deleteData()", e); } } //Note: the accession number is a business rule and should be externalized public boolean insertData(Sample sample) throws LIMSRuntimeException { try { sample.setAccessionNumber(getNextAccessionNumber()); String id = (String) HibernateUtil.getSession().save(sample); sample.setId(id); AuditTrailDAO auditDAO = new AuditTrailDAOImpl(); String sysUserId = sample.getSysUserId(); String tableName = "SAMPLE"; auditDAO.saveNewHistory(sample, sysUserId, tableName); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); } catch (Exception e) { LogEvent.logError("SampleDAOImpl", "insertData()", e.toString()); throw new LIMSRuntimeException("Error in Sample insertData()", e); } return true; } /** * Insert the specified Sample, which has already been assigned an * accession number by the user from the input view. * * @param Sample The Sample to be inserted into the database. * * @return boolean True if the insert was successful. */ public boolean insertDataWithAccessionNumber(Sample sample) throws LIMSRuntimeException { try { String id = (String) HibernateUtil.getSession().save(sample); sample.setId(id); AuditTrailDAO auditDAO = new AuditTrailDAOImpl(); auditDAO.saveNewHistory(sample, sample.getSysUserId(), "SAMPLE"); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "insertData()", e.toString()); throw new LIMSRuntimeException("Error in Sample insertData()", e); } return true; } public void updateData(Sample sample) throws LIMSRuntimeException { Sample oldData = (Sample) readSample(sample.getId()); Sample newData = sample; // add to audit trail try { AuditTrailDAO auditDAO = new AuditTrailDAOImpl(); String sysUserId = sample.getSysUserId(); String event = IActionConstants.AUDIT_TRAIL_UPDATE; String tableName = "SAMPLE"; auditDAO.saveHistory(newData, oldData, sysUserId, event, tableName); } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "updateData()", e.toString()); throw new LIMSRuntimeException("Error in Sample AuditTrail updateData()", e); } try { HibernateUtil.getSession().merge(sample); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); HibernateUtil.getSession().evict(sample); HibernateUtil.getSession().refresh(sample); } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "updateData()", e.toString()); throw new LIMSRuntimeException("Error in Sample updateData()", e); } } public void getData(Sample sample) throws LIMSRuntimeException { try { Sample samp = (Sample) HibernateUtil.getSession().get(Sample.class, sample.getId()); if (samp != null) { // set sample projects String sql = "from SampleProject sp where samp_id = :sampleId"; Query query = HibernateUtil.getSession().createQuery(sql); query.setParameter("sampleId", Integer.parseInt(samp.getId())); List list = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); samp.setSampleProjects(list); PropertyUtils.copyProperties(sample, samp); } else { sample.setId(null); } } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "getData()", e.toString()); throw new LIMSRuntimeException("Error in Sample getData()", e); } } public List getPageOfSamples(int startingRecNo) throws LIMSRuntimeException { List samples = new Vector(); try { // calculate maxRow to be one more than the page size int endingRecNo = startingRecNo + (SystemConfiguration.getInstance().getDefaultPageSize() + 1); String sql = "from Sample s order by s.id"; org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql); query.setFirstResult(startingRecNo - 1); query.setMaxResults(endingRecNo - 1); samples = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); Sample samp = new Sample(); String locale = SystemConfiguration.getInstance().getDefaultLocale().toString(); // set the display dates for STARTED_DATE, COMPLETED_DATE for (int i = 0; i < samples.size(); i++) { samp = (Sample) samples.get(i); samp.setEnteredDateForDisplay(DateUtil.convertSqlDateToStringDate(samp.getEnteredDate(), locale)); samp.setReceivedDateForDisplay(DateUtil.convertSqlDateToStringDate(samp.getReceivedDate(), locale)); samp.setCollectionDateForDisplay( DateUtil.convertTimestampToStringDate(samp.getCollectionDate(), locale)); samp.setTransmissionDateForDisplay( DateUtil.convertSqlDateToStringDate(samp.getTransmissionDate(), locale)); samp.setReleasedDateForDisplay(DateUtil.convertSqlDateToStringDate(samp.getReleasedDate(), locale)); } } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "getPageOfSamples()", e.toString()); throw new LIMSRuntimeException("Error in Sample getPageOfSamples()", e); } return samples; } public void getSampleByAccessionNumber(Sample sample) throws LIMSRuntimeException { try { String sql = "from Sample s where s.accessionNumber = :param"; org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql); query.setParameter("param", sample.getAccessionNumber()); List list = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); Sample samp = null; if (list.size() > 0) samp = (Sample) list.get(0); if (samp != null) { // set sample projects sql = "from SampleProject sp where samp_id = :param"; query = HibernateUtil.getSession().createQuery(sql); query.setInteger("param", Integer.parseInt(samp.getId())); List sp = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); samp.setSampleProjects(sp); PropertyUtils.copyProperties(sample, samp); } else { sample.setId(null); } } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "getDataByAccessionNumber()", e.toString()); throw new LIMSRuntimeException("Error in Sample getDataByAccessionNumber()", e); } } public Sample readSample(String idString) { Sample samp = null; try { samp = (Sample) HibernateUtil.getSession().get(Sample.class, idString); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "readSample()", e.toString()); throw new LIMSRuntimeException("Error in Sample readSample()", e); } return samp; } public String getNextAccessionNumber() throws LIMSRuntimeException { String accessionNumber = null; String lastAccessionNumber = null; // get the current year Calendar cal = Calendar.getInstance(); int currentYear = cal.get(Calendar.YEAR); try { String sql = "select max(s.accessionNumber) from Sample s"; org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql); List reports = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); if (reports != null && reports.get(0) != null) { if (reports.get(0) != null) { lastAccessionNumber = (String) reports.get(0); if (lastAccessionNumber != null) { String lastAccessionNumberYear = lastAccessionNumber.substring(0, 4); if (lastAccessionNumberYear.equals(String.valueOf(currentYear))) { String seqNumber = lastAccessionNumber.substring(4); int sequence = Integer.parseInt(seqNumber); sequence++; String stringSequenceShort = String.valueOf(sequence); String stringSequenceLong = ""; if (stringSequenceShort.length() < 6) { int zeroPaddingLength = 6 - stringSequenceShort.length(); StringBuffer zeros = new StringBuffer(); for (int i = 0; i < zeroPaddingLength; i++) { zeros.append("0"); } stringSequenceLong = zeros + stringSequenceShort; } if (stringSequenceShort.length() == 6) { stringSequenceLong = stringSequenceShort; } if (stringSequenceShort.length() > 6) {// we // are // over // the // limit //System.out.println("Error in Sample getNextAccessionNumber() max sequence number reached"); throw new LIMSRuntimeException( "Error in Sample getNextAccessionNumber() max sequence number reached"); } accessionNumber = currentYear + stringSequenceLong; } else { // start with new sequence - new year accessionNumber = currentYear + ACC_NUMBER_SEQ_BEGIN; } } else { // nothing retrieved (start from scratch) accessionNumber = currentYear + ACC_NUMBER_SEQ_BEGIN; } } else { // nothing retrieved (start from scratch) accessionNumber = currentYear + ACC_NUMBER_SEQ_BEGIN; } } else { //fixed bug - don't throw an exception - if no samples in database start from scratch //nothing retrieved (start from scratch) accessionNumber = currentYear + ACC_NUMBER_SEQ_BEGIN; } } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "getNextAccessionNumber()", e.toString()); throw new LIMSRuntimeException("Error in Sample getNextAccessionNumber()", e); } return accessionNumber; } public List getNextSampleRecord(String id) throws LIMSRuntimeException { return getNextRecord(id, "Sample", Sample.class); } public List getPreviousSampleRecord(String id) throws LIMSRuntimeException { return getPreviousRecord(id, "Sample", Sample.class); } /** * Get the Sample for the specified accession number. * * @param String The accession number of the Sample being sought. * * @return Sample The Sample for the specified accession number, or * null if the accession number does not exist. */ @SuppressWarnings("unchecked") public Sample getSampleByAccessionNumber(String accessionNumber) throws LIMSRuntimeException { Sample sample = null; try { String sql = "from Sample s where accession_number = :param"; Query query = HibernateUtil.getSession().createQuery(sql); query.setParameter("param", accessionNumber); List<Sample> list = query.list(); if ((list != null) && !list.isEmpty()) { sample = list.get(0); } closeSession(); } catch (Exception e) { throw new LIMSRuntimeException("Exception occurred in getSampleForAccessionNumber", e); } return sample; } //============================================================== public List getSamplesByStatusAndDomain(List statuses, String domain) throws LIMSRuntimeException { List list = new Vector(); try { String sql = "from Sample s where status in (:param1) and domain = :param2"; org.hibernate.Query query = HibernateUtil.getSession().createQuery(sql); query.setParameterList("param1", statuses); query.setParameter("param2", domain); list = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); } catch (Exception e) { //bugzilla 2154 LogEvent.logError("SampleDAOImpl", "getAllSampleByStatusAndDomain()", e.toString()); throw new LIMSRuntimeException("Error in Sample getAllSampleByStatusAndDomain()", e); } return list; } @SuppressWarnings("unchecked") public List<Sample> getSamplesWithPendingQaEvents(Sample sample, boolean filterByQaEventCategory, String qaEventCategoryId, boolean filterByDomain) throws LIMSRuntimeException { List<Sample> list; try { String sql = ""; if (filterByDomain) { if (filterByQaEventCategory) { sql = "from Sample s where s.id IN " + "(select sqe.sample.id from SampleQaEvent sqe where sqe.completedDate is null and sqe.qaEvent.category = :param2) " + " or s.id IN " + "(select aqe.analysis.sampleItem.sample.id from AnalysisQaEvent aqe where aqe.completedDate is null and aqe.qaEvent.category = :param2 and " + //bugzilla 2300 exclude canceled tests "aqe.analysis.status NOT IN (:param3) and" + //make sure we only pick the max revision analyses that have qa events pending "(aqe.analysis.sampleItem.id, aqe.analysis.test.id, aqe.analysis.revision) IN (select b.sampleItem.id, b.test.id, max(b.revision) from Analysis b " + " group by b.sampleItem.id, b.test.id)) " + "and s.domain = :param " + "order by s.accessionNumber"; } else { sql = "from Sample s where s.id IN " + "(select sqe.sample.id from SampleQaEvent sqe where sqe.completedDate is null) " + " or s.id IN " + "(select aqe.analysis.sampleItem.sample.id from AnalysisQaEvent aqe where aqe.completedDate is null and " + //bugzilla 2300 exclude canceled tests "aqe.analysis.status NOT IN (:param3) and " + //make sure we only pick the max revision analyses that have qa events pending "(aqe.analysis.sampleItem.id, aqe.analysis.test.id, aqe.analysis.revision) IN (select b.sampleItem.id, b.test.id, max(b.revision) from Analysis b " + " group by b.sampleItem.id, b.test.id)) " + "and s.domain = :param " + "order by s.accessionNumber"; } } else { if (filterByQaEventCategory) { sql = "from Sample s where s.id IN " + "(select sqe.sample.id from SampleQaEvent sqe where sqe.completedDate is null and sqe.qaEvent.category = :param2) " + " or s.id IN " + "(select aqe.analysis.sampleItem.sample.id from AnalysisQaEvent aqe where aqe.completedDate is null and aqe.qaEvent.category = :param2 and " + //bugzilla 2300 exclude canceled tests "aqe.analysis.status NOT IN (:param3) and" + //make sure we only pick the max revision analyses that have qa events pending "(aqe.analysis.sampleItem.id, aqe.analysis.test.id, aqe.analysis.revision) IN (select b.sampleItem.id, b.test.id, max(b.revision) from Analysis b " + " group by b.sampleItem.id, b.test.id)) " + "order by s.accessionNumber"; } else { sql = "from Sample s where s.id IN " + "(select sqe.sample.id from SampleQaEvent sqe where sqe.completedDate is null) " + " or s.id IN " + "(select aqe.analysis.sampleItem.sample.id from AnalysisQaEvent aqe where aqe.completedDate is null and " + //bugzilla 2300 exclude canceled tests "aqe.analysis.status NOT IN (:param3) and " + //make sure we only pick the max revision analyses that have qa events pending "(aqe.analysis.sampleItem.id, aqe.analysis.test.id, aqe.analysis.revision) IN (select b.sampleItem.id, b.test.id, max(b.revision) from Analysis b " + " group by b.sampleItem.id, b.test.id)) " + "order by s.accessionNumber"; } } Query query = HibernateUtil.getSession().createQuery(sql); if (filterByDomain) { query.setParameter("param", sample.getDomain()); } if (filterByQaEventCategory) { query.setParameter("param2", qaEventCategoryId); } List<String> statusesToExclude = new ArrayList<String>(); statusesToExclude.add(SystemConfiguration.getInstance().getAnalysisStatusCanceled()); query.setParameterList("param3", statusesToExclude); list = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); } catch (Exception e) { LogEvent.logError("SampleDAOImpl", "getSamplesWithPendingQaEvents()", e.toString()); throw new LIMSRuntimeException("Error in Sample getSamplesWithPendingQaEvents()", e); } return list; } public List<Sample> getSamplesReceivedOn(String receivedDate) throws LIMSRuntimeException { //covers full day so handles time stamps return getSamplesReceivedInDateRange(receivedDate, receivedDate); } /** * @see us.mn.state.health.lims.sample.dao.SampleDAO#getSamplesReceivedWithin(java.lang.String, java.lang.String) */ @SuppressWarnings("unchecked") @Override public List<Sample> getSamplesReceivedInDateRange(String receivedDateStart, String receivedDateEnd) throws LIMSRuntimeException { List<Sample> list = null; Calendar start = getCalendarForDateString(receivedDateStart); if (GenericValidator.isBlankOrNull(receivedDateEnd)) { receivedDateEnd = receivedDateStart; } Calendar end = getCalendarForDateString(receivedDateEnd); // worried about time stamps including time information, so might be missed comparing to midnight (00:00:00.00) on the last day of range. end.add(Calendar.DAY_OF_YEAR, 1); end.set(Calendar.HOUR_OF_DAY, 0); end.set(Calendar.MINUTE, 0); end.set(Calendar.SECOND, 0); try { String sql = "from Sample as s where s.receivedTimestamp >= :start AND s.receivedTimestamp < :end"; Query query = HibernateUtil.getSession().createQuery(sql); query.setCalendarDate("start", start); query.setCalendarDate("end", end); list = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); } catch (HibernateException he) { LogEvent.logError("SampleDAOImpl", "getSamplesReceivedInDateRange()", he.toString()); throw new LIMSRuntimeException("Error in Sample getSamplesReceivedInDateRange()", he); } return list; } @SuppressWarnings("unchecked") public List<Sample> getSamplesCollectedOn(String collectionDate) throws LIMSRuntimeException { List<Sample> list = null; Calendar calendar = getCalendarForDateString(collectionDate); try { String sql = "from Sample as sample where sample.collectionDate = :date"; Query query = HibernateUtil.getSession().createQuery(sql); query.setCalendarDate("date", calendar); list = query.list(); HibernateUtil.getSession().flush(); HibernateUtil.getSession().clear(); } catch (HibernateException he) { LogEvent.logError("SampleDAOImpl", "getSamplesRecievedOn()", he.toString()); throw new LIMSRuntimeException("Error in Sample getSamplesRecievedOn()", he); } return list; } private Calendar getCalendarForDateString(String recievedDate) { String localeName = SystemConfiguration.getInstance().getDefaultLocale().toString(); Locale locale = new Locale(localeName); Calendar calendar = Calendar.getInstance(locale); Date date = DateUtil.convertStringDateToSqlDate(recievedDate, localeName); calendar.setTime(date); return calendar; } @SuppressWarnings("unchecked") public List<Sample> getSamplesByProjectAndStatusIDAndAccessionRange(String projectId, List<Integer> inclusiveStatusIdList, String minAccession, String maxAccession) throws LIMSRuntimeException { String sql = "select from Sample s where s.statusId in (:statusList) and " + "s.accessionNumber >= :minAccess and s.accessionNumber <= :maxAccess and " + "s.id in (select sp.sample.id from SampleProject sp where sp.project.id = :projectId)"; try { Query query = HibernateUtil.getSession().createQuery(sql); query.setParameterList("statusList", inclusiveStatusIdList); query.setInteger("projectId", Integer.parseInt(projectId)); query.setString("minAccess", minAccession); query.setString("maxAccess", maxAccession); List<Sample> sampleList = query.list(); closeSession(); return sampleList; } catch (HibernateException e) { handleException(e, "getSamplesByProjectAndStatusIDAndAccessionRange"); } return null; } @SuppressWarnings("unchecked") public List<Sample> getSamplesByAccessionRange(String minAccession, String maxAccession) throws LIMSRuntimeException { String sql = "select from Sample s where s.accessionNumber >= :minAccess and s.accessionNumber <= :maxAccess"; try { Query query = HibernateUtil.getSession().createQuery(sql); query.setString("minAccess", minAccession); query.setString("maxAccess", maxAccession); List<Sample> sampleList = query.list(); closeSession(); return sampleList; } catch (HibernateException e) { handleException(e, "getSamplesByAccessionRange"); } return null; } public String getLargestAccessionNumber() throws LIMSRuntimeException { String greatestAccessionNumber = null; try { String sql = "select max(s.accessionNumber) from Sample s"; Query query = HibernateUtil.getSession().createQuery(sql); greatestAccessionNumber = (String) query.uniqueResult(); } catch (Exception e) { LogEvent.logError("SampleDAOImpl", "getLargestAccessionNumber()", e.toString()); throw new LIMSRuntimeException("Exception occurred in SampleDAOImpl.getLargestAccessionNumber", e); } return greatestAccessionNumber; } public String getLargestAccessionNumberWithPrefix(String prefix) throws LIMSRuntimeException { String greatestAccessionNumber = null; try { String sql = "select max(s.accessionNumber) from Sample s where s.accessionNumber like :prefix"; Query query = HibernateUtil.getSession().createQuery(sql); query.setParameter("prefix", prefix + "%"); greatestAccessionNumber = (String) query.uniqueResult(); } catch (Exception e) { LogEvent.logError("SampleDAOImpl", "getLargestAccessionNumberWithPrefix()", e.toString()); throw new LIMSRuntimeException( "Exception occurred in SampleNumberDAOImpl.getLargestAccessionNumberWithPrefix", e); } return greatestAccessionNumber; } @Override public String getLargestAccessionNumberMatchingPattern(String startingWith, int accessionSize) throws LIMSRuntimeException { String greatestAccessionNumber = null; try { String sql = "select max(s.accessionNumber) from Sample s where s.accessionNumber LIKE :starts and length(s.accessionNumber) = :numberSize"; Query query = HibernateUtil.getSession().createQuery(sql); query.setParameter("starts", startingWith + "%"); query.setInteger("numberSize", accessionSize); greatestAccessionNumber = (String) query.uniqueResult(); } catch (Exception e) { handleException(e, "getLargestAccessionNumberMatchingPattern"); } return greatestAccessionNumber; } @SuppressWarnings("unchecked") @Override public List<Sample> getSamplesWithPendingQaEventsByService(String serviceId) throws LIMSRuntimeException { String sql = "Select sqa.sample From SampleQaEvent sqa where sqa.sample.id IN (select sa.sample.id from SampleOrganization sa where sa.organization.id = :serviceId) "; try { Query query = HibernateUtil.getSession().createQuery(sql); query.setInteger("serviceId", Integer.parseInt(serviceId)); List<Sample> samples = query.list(); closeSession(); return samples; } catch (HibernateException e) { handleException(e, "getSamplesWithPendingQaEventsByService"); } return null; } }