/* * 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.reports.action.implementation.reportBeans; import org.hibernate.Session; import us.mn.state.health.lims.analyte.dao.AnalyteDAO; import us.mn.state.health.lims.analyte.daoimpl.AnalyteDAOImpl; import us.mn.state.health.lims.analyte.valueholder.Analyte; import us.mn.state.health.lims.common.services.StatusService; import us.mn.state.health.lims.common.services.StatusService.OrderStatus; import us.mn.state.health.lims.common.services.TestService; import us.mn.state.health.lims.common.services.TypeOfTestResultService; import us.mn.state.health.lims.common.util.DateUtil; import us.mn.state.health.lims.common.util.StringUtil; import us.mn.state.health.lims.hibernate.HibernateUtil; import us.mn.state.health.lims.observationhistorytype.dao.ObservationHistoryTypeDAO; import us.mn.state.health.lims.observationhistorytype.daoImpl.ObservationHistoryTypeDAOImpl; import us.mn.state.health.lims.observationhistorytype.valueholder.ObservationHistoryType; import us.mn.state.health.lims.project.dao.ProjectDAO; import us.mn.state.health.lims.project.daoimpl.ProjectDAOImpl; import us.mn.state.health.lims.project.valueholder.Project; import us.mn.state.health.lims.result.dao.ResultDAO; import us.mn.state.health.lims.result.daoimpl.ResultDAOImpl; import us.mn.state.health.lims.result.valueholder.Result; import us.mn.state.health.lims.test.daoimpl.TestDAOImpl; import us.mn.state.health.lims.test.valueholder.Test; import us.mn.state.health.lims.testresult.daoimpl.TestResultDAOImpl; import us.mn.state.health.lims.testresult.valueholder.TestResult; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.*; import static org.apache.commons.validator.GenericValidator.isBlankOrNull; import static us.mn.state.health.lims.reports.action.implementation.reportBeans.CSVColumnBuilder.Strategy.DICT; import static us.mn.state.health.lims.reports.action.implementation.reportBeans.CSVColumnBuilder.Strategy.TEST_RESULT; /** * @author pahill (pahill@uw.edu) * @since Mar 18, 2011 */ abstract public class CSVColumnBuilder { /** * */ public CSVColumnBuilder(StatusService.AnalysisStatus validStatusFilter) { // we'll round up everything via hibernate first. ResourceTranslator.DictionaryTranslator.getInstance(); ResourceTranslator.GenderTranslator.getInstance(); if (validStatusFilter != null) { this.validStatusId = StatusService.getInstance().getStatusID(validStatusFilter); } } /** * The list of all columns that can be exported for lookup when report is * generated */ private final Map<String, CSVColumn> columnsByDbName = new HashMap<String, CSVColumn>(); /** * The list of columns in order of definition, so that we can generate * JasperReports XML in order for display */ private final List<CSVColumn> columnsInOrder = new ArrayList<CSVColumn>(); /** * In order as postgres would order varchar columns (alphabetical ignoring * case?). */ protected List<ObservationHistoryType> allObHistoryTypes; /** * All possible tests, so we can have 1 result per test. */ protected List<Test> allTests; /** * This table provide a mapping from test name (CSV column heading also) to * the TestResult record so we can look at the result type (Dictionary vs. * constant etc.) */ protected Map<String, TestResult> testResultsByTestName; protected String validStatusId; protected static final SimpleDateFormat postgresDateFormat = new SimpleDateFormat("yyyy-MM-dd"); private static final SimpleDateFormat postgresDateTime = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); protected ResultSet resultSet; protected String eol = System.getProperty("line.separator"); protected ResultDAO resultDAO = new ResultDAOImpl(); //This is the largest value possible for a postgres column name. The code will convert the //test description to a column name so we need to truncate //It's actually 63 but UTF_8 makes 59 safer. private static final int MAX_POSTGRES_COL_NAME = 59; /** * the test have to be sorted by the name, because they have to match the * pivot table order in the results */ @SuppressWarnings("unchecked") protected void defineAllTestsAndResults() { if (allTests == null) { allTests = new TestDAOImpl().getAllOrderBy("name"); } if (testResultsByTestName == null) { testResultsByTestName = new HashMap<String, TestResult>(); List<TestResult> allTestResults = new TestResultDAOImpl().getAllTestResults(); for (TestResult testResult : allTestResults) { String key = TestService.getLocalizedTestNameWithType( testResult.getTest() ); testResultsByTestName.put(key, testResult); } } } /** * map to provide appropriate tag to identify the project. */ static Map<String /* project Id */, String /* project tag */> projectTagById = new HashMap<String, String>(); static { defineAllProjectTags(); } public static void defineAllProjectTags() { ProjectDAO projectDAO = new ProjectDAOImpl(); List<Project> allProjects = projectDAO.getAllProjects(); for (Project project : allProjects) { String projectTag; // Watch the order on these 1st 2! if (project.getNameKey().contains("ARVF")) { projectTag = "ARVS"; } else if (project.getNameKey().contains("ARV")) { projectTag = "ARVB"; } else if (project.getNameKey().contains("VLS")) { projectTag = "VLS"; } else { // otherwise we use the letters from the Sample ID prefix, which // at some locations for some projects is undefined. String code = project.getProgramCode(); projectTag = (code == null)?"":code.substring(1); // drop the L } projectTagById.put(project.getId(), projectTag); } } protected StringBuilder query; private String gendCD4CountAnalyteId; /** * The various ways that columns are converted for CSV export * * @author Paul A. Hill (pahill@uw.edu) * @since Feb 1, 2011 */ public static enum Strategy { DICT, // dictionary localized value DICT_PLUS, // dictionary localized value or a string constant DICT_RAW, // dictionary localized value, no attempts at trimming to show just code number. DATE, // date (i.e. 01/01/2013) DATE_TIME, // date with time (i.e. 01/01/2013 12:12:00) NONE, GENDER, DROP_ZERO, TEST_RESULT, GEND_CD4, SAMPLE_STATUS, PROJECT, LOG, // results is a real number, but display the log of it. AGE_YEARS, AGE_MONTHS, AGE_WEEKS, DEBUG, CUSTOM, //special handling which is encapsulated in an instance of ICSVColumnCustomStrategy BLANK //Will always be an empty string. Used when column is wanted but data is not } public void buildDataSource() throws Exception { buildResultSet(); } protected void buildResultSet() throws SQLException { makeSQL(); String sql = query.toString(); //System.out.println("===1===\n" + sql.substring(0, 7000)); // the SQL is chunked out only because Eclipse thinks printing really big strings to the console must be wrong, so it truncates them //System.out.println("===2===\n" + sql.substring(7000)); Session session = HibernateUtil.getSession().getSessionFactory().openSession(); PreparedStatement stmt = session.connection().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet = stmt.executeQuery(); } /** * @param value * @return */ protected String datetimeToLocalDate(String value) { try { Date parsed = postgresDateTime.parse(value); java.sql.Date date = new java.sql.Date(parsed.getTime()); return DateUtil.convertSqlDateToStringDate(date); } catch (Exception e) { return value; } } protected String datetimeToLocalDateTime(String value) { try { Date parsed = postgresDateTime.parse(value); return DateUtil.formatDateTimeAsText(parsed); } catch (Exception e) { return value; } } public String getValue(CSVColumn column, String accessionNumber) throws Exception { String value; // look in the data source for a value try { value = resultSet.getString( trimToPostgresMaxColumnName(column.dbName)); } catch (Exception e) { // if you end up where it is because the result set doesn't return a // column of the right name // Check MAX_POSTGRES_COL_NAME if this fails on a long name System.out.println("Internal Error: Unable to find db column \"" + column.dbName + "\" in data."); return "?" + column.csvName + "?"; } String result = column.translate(value, accessionNumber); // translate should never return null, "" is better while it is doing // translation. if (result == null) { System.out.println("A null found " + column.dbName); } return result; } private String trimToPostgresMaxColumnName(String name) { if( name.length() <= MAX_POSTGRES_COL_NAME ){ return name; }else{ return name.substring(0, MAX_POSTGRES_COL_NAME); } } public void add(String dbName, String csvTitle) { add(dbName, csvTitle, DICT); } public void add(String dbName, String csvTitle, Strategy strat) { CSVColumn col = new CSVColumn(dbName, csvTitle, strat); columnsByDbName.put(dbName, col); columnsInOrder.add(col); } public void add(String dbName, String csvTitle, ICSVColumnCustomStrategy customStrategy ){ CSVColumn col = new CSVColumn(dbName, csvTitle, Strategy.CUSTOM, customStrategy); columnsByDbName.put(dbName, col); columnsInOrder.add(col); } /** * A utility routine for finding the project short tag (used in exporting * etc.) from a projectId. */ public static String translateProjectId(String projectId) { return (projectId == null) ? null : projectTagById.get(projectId); } public class CSVColumn { public String csvName; public String dbName; public Strategy strategy; public ICSVColumnCustomStrategy customStrategy; public CSVColumn(String dbName, String csvName, Strategy strategy) { this.csvName = csvName; this.dbName = dbName; this.strategy = strategy; } public CSVColumn(String dbName, String csvName, Strategy strategy, ICSVColumnCustomStrategy customStrategy) { this.csvName = csvName; this.dbName = dbName; this.strategy = strategy; this.customStrategy = customStrategy; } public String translate(String value, String accessionNumber) throws Exception { switch (strategy) { case CUSTOM: return customStrategy.translate(value, accessionNumber, csvName, dbName); case DICT_RAW: return isBlankOrNull(value) ? "" : ResourceTranslator.DictionaryTranslator.getInstance().translateRaw(value); case DICT_PLUS: return isBlankOrNull(value) ? "" : ResourceTranslator.DictionaryTranslator.getInstance().translateOrNot(value); case DICT: return isBlankOrNull(value) ? "" : ResourceTranslator.DictionaryTranslator.getInstance().translate(value); case DATE: return isBlankOrNull(value) ? "" : datetimeToLocalDate(value); case DATE_TIME: return isBlankOrNull(value) ? "" : datetimeToLocalDateTime(value); case AGE_YEARS: case AGE_MONTHS: case AGE_WEEKS: return isBlankOrNull(value) ? "" : translateAge(strategy, value); case GENDER: return isBlankOrNull(value) ? "" : ResourceTranslator.GenderTranslator.getInstance().translate(value); case DROP_ZERO: return ("0".equals(value) || value == null) ? "" : value; case TEST_RESULT: return isBlankOrNull(value) ? "" : translateTestResult(this.csvName, value); case GEND_CD4: return isBlankOrNull(value) ? "" : translateGendResult(getGendCD4CountAnalyteId(), value); case LOG: return isBlankOrNull(value) ? "" : translateLog(value); case SAMPLE_STATUS: OrderStatus orderStatus = StatusService.getInstance().getOrderStatusForID(value); if (orderStatus == null) return "?"; switch (orderStatus) { case Entered: return "E"; // entered, entr�e case Started: return "C"; // commenced, commenc� case Finished: return "F"; // Finished, Finale case NonConforming_depricated: return "N"; // Non-conforming, Non-conformes } case PROJECT: return translateProjectId(value); case DEBUG: System.out.println("Processing Column Value: " + this.csvName + " \"" + value + "\""); case BLANK: return ""; default: return isBlankOrNull(value) ? "" : value; } } private String translateGendResult(String gendResultAnalyteId, String sampleItemId) { // 'generated CD4 Count' Result gendCD4Result = resultDAO.getResultForAnalyteAndSampleItem(gendResultAnalyteId, sampleItemId); if (gendCD4Result == null) { return ""; } String value = gendCD4Result.getValue(); return (value == null) ? "" : value; } /** * The log value of the given value as a floating-point value. * * @param value * @return The log value */ private String translateLog(String value) { try { double d = Double.parseDouble(value); return String.valueOf(Math.log10(d)); } catch (NumberFormatException nfe) { return ""; } } public String translateAge(Strategy strategy, String end) throws Exception { Date birthday = resultSet.getDate("birth_date"); Date endDate = postgresDateTime.parse(end); switch (strategy) { case AGE_YEARS: return String.valueOf(DateUtil.getAgeInYears(birthday, endDate)); case AGE_MONTHS: return String.valueOf(DateUtil.getAgeInMonths(birthday, endDate)); case AGE_WEEKS: return String.valueOf(DateUtil.getAgeInWeeks(birthday, endDate)); } return ""; } /** * @param value * @return * @throws Exception */ public String translateTestResult(String testName, String value) throws Exception { TestResult testResult = testResultsByTestName.get(testName); // if it is not in the table then its just a value in the result // that was NOT selected from a list, thus no translation if (testResult == null) { return value; } String type = testResult.getTestResultType(); // if it is in the table D have to be translated through the // dictionary, otherwise don't if (TypeOfTestResultService.ResultType.DICTIONARY.getCharacterValue().equals(type)) { return ResourceTranslator.DictionaryTranslator.getInstance().translateRaw(value); } else if ( TypeOfTestResultService.ResultType.MULTISELECT.getCharacterValue().equals(type)) { return findMultiSelectItemsForTest(testResult.getTest().getId()); } return value; } /** * @param testId * @return * @throws SQLException */ private String findMultiSelectItemsForTest(String testId) throws SQLException { String sampleId = resultSet.getString("sample_id"); List<Result> results = resultDAO.getResultsForTestAndSample(sampleId, testId); StringBuilder multi = new StringBuilder(); for (Result result : results) { multi.append(ResourceTranslator.DictionaryTranslator.getInstance().translateRaw(result.getValue())); multi.append( "," ); } if( multi.length() > 0){ multi.setLength(multi.length() - 1); } return multi.toString(); } } abstract public void makeSQL(); protected void defineAllObservationHistoryTypes() { ObservationHistoryTypeDAO ohtDao = new ObservationHistoryTypeDAOImpl(); allObHistoryTypes = ohtDao.getAllOrderBy("type_name"); } /** * For every sample, one row per sample item, one column per test result for * that sample item. * * @param lowDatePostgres * @param highDatePostgres */ protected void appendResultCrosstab(String lowDatePostgres, String highDatePostgres) { // A list of analytes which should not show up in the regular results, // because they are not the primary results, but, for example, is a // conclusion. // String excludeAnalytes = getExcludedAnalytesSet(); String listName = "result"; query.append(", \n\n ( SELECT si.samp_id, si.id AS sampleItem_id, si.sort_order AS sampleItemNo, " + listName + ".* " + " FROM sample_item AS si LEFT JOIN \n "); // Begin cross tab / pivot table query.append( " crosstab( " + "\n 'SELECT si.id, t.description, r.value " + "\n FROM clinlims.result AS r, clinlims.analysis AS a, clinlims.sample_item AS si, clinlims.sample AS s, clinlims.test AS t, clinlims.test_result AS tr " + "\n WHERE " + "\n s.id = si.samp_id" + " AND s.collection_date >= date(''" + lowDatePostgres + "'') AND s.collection_date <= date(''" + highDatePostgres + " '') " + "\n AND s.id = si.samp_id " + "\n AND si.id = a.sampitem_id " + (( validStatusId == null )?"": " AND a.status_id = " + validStatusId) + "\n AND a.id = r.analysis_id " + "\n AND r.test_result_id = tr.id" + "\n AND tr.test_id = t.id " // + (( excludeAnalytes == null)?"": // " AND r.analyte_id NOT IN ( " + excludeAnalytes) + ")" // + " AND a.test_id = t.id " + "\n ORDER BY 1, 2 " + "\n ', 'SELECT description FROM test where description != ''CD4'' AND is_active = ''Y'' ORDER BY 1' ) "); // end of cross tab // Name the test pivot table columns . We'll name them all after the // resource name, because some tests have fancy characters in them and // somewhere // between iReport, Java and postgres a complex name (e.g. one including // a beta, "�HCG Quant") get messed up and isn't found. query.append("\n as " + listName + " ( " // inner use of the list name + "\"si_id\" numeric(10) "); for (Test col : allTests) { String testName = TestService.getLocalizedTestNameWithType( col ); if (!"CD4".equals(testName)) { // CD4 is listed as a test name but // it isn't clear it should be line // 446 may also have to be changed query.append("\n, \"" + trimToPostgresMaxColumnName(testName) + "\" varchar(200) "); } } query.append(" ) \n"); // left join all sample Items from the right sample range to the results table. query.append( "\n ON si.id = " + listName + ".si_id " // the inner use a few lines above + "\n ORDER BY si.samp_id, si.id " + "\n) AS " + listName + "\n " ); // outer re-use the list name to name this sparse matrix of results. } /** * @return */ private String getExcludedAnalytesSet() { String[] excludedAnalytes = new String[] { getGendCD4CountAnalyteId() }; StringBuilder sb = new StringBuilder(); for (String a : excludedAnalytes) { sb.append(a).append(","); } sb.setLength(sb.length() - 1); return sb.toString(); } protected void appendObservationHistoryCrosstab(String lowDatePostgres, String highDatePostgres) { String listName = "demo"; appendCrosstabPreamble(listName); query.append( // any Observation History items "\n crosstab( " + "\n 'SELECT DISTINCT oh.sample_id as samp_id, oht.type_name, value " + "\n FROM observation_history AS oh, sample AS s, observation_history_type AS oht " + "\n WHERE s.collection_date >= date(''" + lowDatePostgres + "'') " + "\n AND s.collection_date <= date(''" + highDatePostgres + "'')" + "\n AND s.id = oh.sample_id AND oh.observation_history_type_id = oht.id order by 1;' " + "\n , " + "\n 'SELECT DISTINCT oht.type_name FROM observation_history_type AS oht ORDER BY 1;' " + // must be the same list as the column definition for the demo table below. "\n ) \n "); // in the following list of observation history items, all valid values // are listed in alphabetical order (since that is how crosstab lists // them), so that that the queries constructed don't have to filter the // exceptional ones. // It is the column name list defined in the constructor of this class // which picks the right column to display (and in what order). That // list is also used to generated the JasperReport XML // That list skips the OH types which actually repeat (priorDiseases, // priorARVTreatmentINNs) which are returned as as crosstab set of // columns either specially named columns (disease acronyms for each // diseases) or with with n after their name (priorARVTreatmentINNs1, 2, // ... ) query.append(" as demo ( " + " \"s_id\" numeric(10) "); for (ObservationHistoryType oht : allObHistoryTypes) { query.append("\n," + oht.getTypeName() + " varchar(100) "); } query.append(" ) \n"); appendCrosstabPostfix(lowDatePostgres, highDatePostgres, listName); } protected void appendCrosstabPreamble(String listName) { query.append( ", \n\n ( SELECT s.id AS samp_id, " + listName + ".* " + " FROM sample AS s LEFT JOIN \n " ); } protected void appendCrosstabPostfix(String lowDatePostgres, String highDatePostgres, String listName) { query.append( "\n ON s.id = " + listName + ".s_id " + " AND s.collection_date >= '" + lowDatePostgres + "'" + " AND s.collection_date <= '" + highDatePostgres + "'" + " ORDER BY 1 " + "\n) AS " + listName + "\n " ); } protected Object pad20(Object translate) { // uncomment the following lines to help make everything line up when // debugging output. // String strIn = (String)translate; // int len = strIn.length(); // len = ( len > 30 )? 30: len; // translate = translate + " ".substring(len); return translate; } /** * Generate a column to the list of all columns. One for each possible test. */ protected void addAllResultsColumns() { for (Test test : allTests) { String testTag = TestService.getLocalizedTestNameWithType( test ); if (!"CD4".equals(testTag)) { add(testTag, TestService.getLocalizedTestNameWithType( test ), TEST_RESULT ); } } } /** * Useful for the 1st line of a CSV files. This produces a completely * escaped for MSExcel comma separated list of columns. * * @return one string with all names. */ public String getColumnNamesLine() { StringBuilder line = new StringBuilder(); for (CSVColumn column : columnsInOrder) { line.append(StringUtil.escapeCSVValue(column.csvName)); line.append(","); } line.setLength(line.length() - 1); line.append(eol); return line.toString(); } /** * @return * @throws Exception */ public String nextLine() throws Exception { StringBuilder line = new StringBuilder(); String accessionNumber = null; for (CSVColumn column : columnsInOrder) { //OK this is a little hocky. Some of the custom translation strategies need the accession number and MOST of the time //they are called after the accession number column. Not sure what to do if they are ever called before. String translatedValue = StringUtil.escapeCSVValue(getValue(column, accessionNumber)); if( accessionNumber == null && "accession_number".equals(column.dbName)){ accessionNumber = translatedValue; } line.append(translatedValue); line.append(","); } line.setLength(line.length() - 1); line.append(eol); return line.toString(); } public boolean next() throws Exception { return resultSet.next(); } /** * @throws SQLException * */ public void closeResultSet() throws SQLException { resultSet.close(); resultSet = null; } protected String getGendCD4CountAnalyteId() { if (gendCD4CountAnalyteId == null) { AnalyteDAO analyteDAO = new AnalyteDAOImpl(); Analyte a = new Analyte(); a.setAnalyteName("generated CD4 Count"); this.gendCD4CountAnalyteId = analyteDAO.getAnalyteByName(a, false).getId(); } return gendCD4CountAnalyteId; } }