/*
* Copyright (c) 2006-2013 Massachusetts General Hospital
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the i2b2 Software License v1.0
* which accompanies this distribution.
*
* Contributors:
* Christopher Herrick
*/
package edu.harvard.i2b2.crc.dao.setfinder.querybuilder.temporal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import edu.harvard.i2b2.common.exception.I2B2DAOException;
import edu.harvard.i2b2.common.exception.I2B2Exception;
import edu.harvard.i2b2.crc.dao.DAOFactoryHelper;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.ConceptNotFoundException;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.DateConstrainUtil;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.QueryTimingHandler;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.TotalItemOccurrenceHandler;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.temporal.TemporalQueryOptions.InvertedConstraintStrategy;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.temporal.TemporalQueryOptions.QueryConstraintStrategy;
import edu.harvard.i2b2.crc.datavo.db.DataSourceLookup;
import edu.harvard.i2b2.crc.datavo.i2b2message.SecurityType;
import edu.harvard.i2b2.crc.datavo.setfinder.query.ItemType;
import edu.harvard.i2b2.crc.datavo.setfinder.query.PanelType;
import edu.harvard.i2b2.crc.datavo.setfinder.query.QueryDefinitionType;
import edu.harvard.i2b2.crc.datavo.setfinder.query.PanelType.TotalItemOccurrences;
import edu.harvard.i2b2.crc.datavo.setfinder.query.TotOccuranceOperatorType;
import edu.harvard.i2b2.crc.util.ItemKeyUtil;
/**
* Temporal Panel Object
*
* <P>
* Panel query object that wraps the panel tag found in the query definition
* xml. It roughly corresponds to a panel in the query UI. Panel is responsible
* for organizing the sql that comes back from individual panel items - sql from
* items should be logically or'd together. It is also the container that holds
* the panel constraint types - occurrence, dates, and exclude.
*
* @author Christopher Herrick
*
*/
public class TemporalPanel implements Comparable<Object> {
protected final Log log = LogFactory.getLog(getClass());
/*
* Max Timing Score - max score a panel can get when ordering based on items
* and timing
*/
private final int MAXTIMINGSCORE = 5;
private TemporalSubQuery parent;
private PanelType basePanel;
private int estimatedPanelSize = 0;
private List<TemporalPanelItem> panelItemList = null;
private int missingItemTotals = 0;
/**
* Constructor
*
* @param parent
* reference to the temporal panel group to which this panel
* belongs
* @param panel
* i2b2 instantiation of the panel xml object found in the query
* definition
* @throws I2B2Exception
* thrown when an i2b2 specific error is found
*/
public TemporalPanel(TemporalSubQuery parent, PanelType panel)
throws I2B2Exception {
this.parent = parent;
this.basePanel = panel;
parsePanel();
}
/**
* Parse Panel
*
* Parses through the paneltype object passed in through the constructor.
* Evaluates each panel item and casts them to the appropriate type.
* Calculates the estimated return size of the times in the panel which will
* then be used when sorting panel items
*
* @throws I2B2Exception
* thrown when an i2b2 specific error is found
*/
private void parsePanel() throws I2B2Exception {
panelItemList = new ArrayList<TemporalPanelItem>();
List<ItemType> itemList = basePanel.getItem();
for (ItemType itemType : itemList) {
TemporalPanelItem panelItem = null;
try{
if (itemType.getItemKey().toLowerCase()
.startsWith(ItemKeyUtil.ITEM_KEY_PATIENT_SET)) {
panelItem = new TemporalPanelPatientSetItem(this, itemType);
} else if (itemType.getItemKey().toLowerCase()
.startsWith(ItemKeyUtil.ITEM_KEY_PATIENT_ENCOUNTER_SET)) {
panelItem = new TemporalPanelPatientEncounterSetItem(this,
itemType);
} else if (itemType.getItemKey().toLowerCase()
.startsWith(ItemKeyUtil.ITEM_KEY_MASTERID)) {
panelItem = new TemporalPanelEmbeddedQueryItem(this, itemType);
} else if (itemType.getItemKey().toLowerCase()
.startsWith(ItemKeyUtil.ITEM_KEY_PATIENT)) {
panelItem = new TemporalPanelPatientItem(this, itemType);
} else if (itemType.getItemKey().toLowerCase()
.startsWith(ItemKeyUtil.ITEM_KEY_ENCOUNTER)) {
panelItem = new TemporalPanelEncounterItem(this, itemType);
} else {
panelItem = new TemporalPanelConceptItem(this, itemType);
if (panelItem != null
&& panelItem.getConceptType() != null
&& panelItem.getConceptType().getDimcode() != null
&& panelItem.getConceptType().getDimcode()
.toLowerCase().trim()
.startsWith(ItemKeyUtil.ITEM_KEY_CELLID)) {
panelItem = new TemporalPanelCellQueryItem(this, itemType,
panelItem.getConceptType());
}
}
Integer conceptTotal = panelItem.getConceptTotal();
if (conceptTotal != null) {
estimatedPanelSize += conceptTotal;
} else
missingItemTotals++;
panelItemList.add(panelItem);
}
catch (ConceptNotFoundException ce){
log.debug("Concept not found error: " + ce.getMessage());
parent.addIgnoredMessage(ce.getMessage() + " panel#" + parent.getPanelIndex(this));
}
}
}
/**
* Build Sql
*
* Main method for generating the sql string that will be run on the
* database for this panel item. Iterates through each item contained in the
* panel and appends sql together to form sql string for all items in the
* panel
*
* @param currentIndex
* int value that represents the panel position in the query
* @return String sql representation of the items in the query
* @throws I2B2DAOException
* thrown when a data related i2b2 issue is encountered
*/
public String buildSql(int currentIndex) throws I2B2DAOException {
boolean firstPanel = this.isFirstPanelInQuery();
StringBuilder panelSqlBuffer = new StringBuilder();
boolean addDelimiter = false;
List<String> itemSqlList = getItemSql();
if (itemSqlList!=null&&itemSqlList.size()>0){
if (this.hasPanelOccurrenceConstraint()
&& this.applyOccurrenceToPanelLevel()
&& (this.getItemList().size() > 1 || !this.isPatientOnlyQuery())) {
if (firstPanel) {
panelSqlBuffer
.append(firstPanelItemSqlWithOccurrence(itemSqlList));
} else {
panelSqlBuffer.append(nonFirstPanelItemSqlWithOccurrence(
currentIndex, itemSqlList));
}
} else if (firstPanel && this.isPanelInverted()) {
panelSqlBuffer.append(buildFirstPanelInvertSql(itemSqlList));
} else {
if (firstPanel) {
panelSqlBuffer.append(firstPanelItemSql(itemSqlList));
} else {
panelSqlBuffer.append(nonFirstPanelItemSql(itemSqlList,
currentIndex));
}
}
}
else if (this.isPanelInverted()){
//no items and inverted panel means this is a get everyone query
String schema = getDatabaseSchema();
if (schema == null)
schema = "";
else if (!schema.endsWith("."))
schema += ".";
panelSqlBuffer.append("insert into " + parent.getTempTableName() + " (patient_num, panel_count ) " +
"select distinct patient_num, 0 from " + schema + "patient_dimension pat ");
}
return panelSqlBuffer.toString();
}
/**
* Get Item Sql
*
* Gets the sql for each of the items in the panel and returns the results
* in an List
*
* @return List<String> list of sql statements for all the times contained in the panel
* @throws I2B2DAOException
*/
private List<String> getItemSql() throws I2B2DAOException {
List<String> itemList = null;
if (panelItemList.size() > 0) {
itemList = new ArrayList<String>(panelItemList.size());
for (TemporalPanelItem item : panelItemList) {
try {
itemList.add(item.buildSql());
}
catch (ConceptNotFoundException ce){
parent.addIgnoredMessage(ce.getMessage() + " panel#" + parent.getPanelIndex(this));
}
}
}
return itemList;
}
/**
* Union Item Sql
*
* Takes the list of individual sql statements and constructs one sql statement by unioning
* individual statements together. Submitted statements are assumed to have the same select list
*
* @param itemSqlList List of individual sql statements
* @return String one sql statement that unions individual statement together
*/
private String unionItemSql(List<String> itemSqlList) {
StringBuilder unionSql = new StringBuilder();
boolean first = true;
for (String itemSql : itemSqlList) {
if (!first)
unionSql.append("\n union all \n");
else
first = false;
unionSql.append(itemSql);
}
return unionSql.toString();
}
/**
* Consolidate Item Sql
*
* Takes in list of individual sql statements and combines them by consolidating statements with the same from clause
* into one statement with multiple constraints. Statements with different from clauses are unioned together
*
* @param itemSql List of individual sql statements
* @return String one sql statement that combines all statements from the individual list
*/
private String consolidateItemSql(List<String> itemSql) {
StringBuilder itemSqlBuffer = new StringBuilder();
HashMap<String, List<TemporalQuerySimpleSqlParser>> tableMatch = new HashMap<String, List<TemporalQuerySimpleSqlParser>>();
int index = 0;
for (String sql : itemSql) {
TemporalQuerySimpleSqlParser simpleSql = new TemporalQuerySimpleSqlParser(
sql);
String selectClause = simpleSql.getSelectClause();
String fromClause = simpleSql.getFromClause();
String groupByClause = simpleSql.getGroupByClause();
String havingClause = simpleSql.getHavingClause();
String consolidatedKey = selectClause
+ "|"
+ fromClause
+ "|"
+ (groupByClause != null
&& groupByClause.trim().length() > 0 ? "|"
+ groupByClause : "");
if (havingClause != null && havingClause.trim().length() > 0) {
consolidatedKey += "|" + String.valueOf(index);
}
List<TemporalQuerySimpleSqlParser> sqlList = null;
if (tableMatch.containsKey(consolidatedKey)) {
sqlList = tableMatch.get(consolidatedKey);
} else {
sqlList = new ArrayList<TemporalQuerySimpleSqlParser>();
}
sqlList.add(simpleSql);
tableMatch.put(consolidatedKey, sqlList);
index++;
}
boolean firstKey = true;
for (String consolidatedKey : tableMatch.keySet()) {
List<TemporalQuerySimpleSqlParser> sqlList = tableMatch
.get(consolidatedKey);
boolean firstItem = true;
String selectClause = "";
String fromClause = "";
String whereClause = "";
String groupByClause = "";
String havingClause = "";
for (TemporalQuerySimpleSqlParser simpleSql : sqlList) {
if (firstItem) {
selectClause = "select " + simpleSql.getSelectClause()
+ " ";
fromClause = "from " + simpleSql.getFromClause() + " ";
if (simpleSql.getGroupByClause() != null
&& simpleSql.getGroupByClause().trim().length() > 0)
groupByClause = "group by "
+ simpleSql.getGroupByClause() + " ";
if (simpleSql.getHavingClause() != null
&& simpleSql.getHavingClause().trim().length() > 0)
havingClause = "having " + simpleSql.getHavingClause()
+ " ";
}
if (simpleSql.getWhereClause() != null
&& simpleSql.getWhereClause().trim().length() > 0) {
if (whereClause.length() == 0) {
whereClause = "where (" + simpleSql.getWhereClause()
+ ") ";
} else {
whereClause += "or (" + simpleSql.getWhereClause()
+ ") ";
}
}
firstItem = false;
}
if (!firstKey) {
itemSqlBuffer.append("\nunion all \n");
}
itemSqlBuffer.append(selectClause);
itemSqlBuffer.append(fromClause);
if (whereClause != null && whereClause.trim().length() > 0)
itemSqlBuffer.append(whereClause);
if (groupByClause != null && groupByClause.trim().length() > 0)
itemSqlBuffer.append(groupByClause);
if (havingClause != null && havingClause.trim().length() > 0)
itemSqlBuffer.append(havingClause);
firstKey = false;
}
return itemSqlBuffer.toString();
}
/**
* Build Item Union Sql
*
* Used to return the proper sql syntax for unioning two sql statements
* together
*
* @return String with sql building block used to union to sql statements
* together
* @throws I2B2DAOException
* thrown when an i2b2 related data error is encountered
*/
private String buildItemUnionSql() throws I2B2DAOException {
StringBuilder itemSqlBuffer = new StringBuilder();
int currentIndex = 0;
for (TemporalPanelItem item : panelItemList) {
String itemSql = item.buildSql();
if (currentIndex > 0) {
itemSqlBuffer.append("\n union all \n");
}
itemSqlBuffer.append(itemSql);
currentIndex++;
}
return itemSqlBuffer.toString();
}
/**
* First Panel Item Sql
*
* Processes an item from the first panel of a panel group. First panel
* items are processed as an insert statement into a temporary table rather
* than an update
*
* @param itemSqlList
* List of individual sql statements found in this panel
* @return String sql representation that joins the item sql to the panel
* sql
*/
private String firstPanelItemSql(List<String> itemSqlList) {
StringBuilder panelSql = new StringBuilder();
boolean addDelimiter = false;
for (String itemSql : itemSqlList) {
String insertValuesClause = buildInsertValuesClause();
StringBuilder withItemSql = new StringBuilder();
String firstPanelItemSql = "";
String innerSelectClause = buildInnerSelectClause();
String innerGroupByClause = buildInnerGroupByClause();
String itemStatement = "";
StringBuilder tableStatement = new StringBuilder(itemSql);
boolean useTempTables = false;
if (parent.getServerType().equalsIgnoreCase(
DAOFactoryHelper.SQLSERVER)
&& parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.TEMP_TABLES) {
useTempTables = true;
}
// String suffix = getPanelId();
String suffix = "";
String tSelect = "select " + innerSelectClause + " " + "from "
+ (useTempTables ? "#t" + suffix + " " : "") + "t ";
String schema = getDatabaseSchema();
if (schema == null)
schema = "";
else if (!schema.endsWith("."))
schema += ".";
if (getPanelTiming().equals(QueryTimingHandler.ANY)) {
// this means there are negation panels behind this one
if (returnInstanceToParent()) {
itemStatement = itemSql;
innerSelectClause = buildInnerSelectClause("f");
innerGroupByClause = buildInnerGroupByClause("f");
tableStatement = new StringBuilder();
tableStatement.append("select " + innerSelectClause + " "
+ "from " + schema + "observation_fact f, ");
if (useTempTables) {
tableStatement.append("#i" + suffix + " ");
} else if (parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.DERIVED_TABLES) {
tableStatement.append("(" + itemSql + ") ");
}
tableStatement
.append(" i where i.patient_num = f.patient_num ");
if (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement()) {
tableStatement.append("group by " + innerGroupByClause);
}
} else if (returnEncounterToParent()) {
itemStatement = itemSql;
innerSelectClause = buildInnerSelectClause("v");
innerGroupByClause = buildInnerGroupByClause("v");
tableStatement = new StringBuilder();
tableStatement.append("select " + innerSelectClause + " "
+ "from " + schema + "visit_dimension v, ");
if (useTempTables) {
tableStatement.append("#i" + suffix + " ");
} else if (parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.DERIVED_TABLES) {
tableStatement.append("(" + itemSql + ") ");
}
tableStatement
.append(" i where i.patient_num = v.patient_num ");
if (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement()) {
tableStatement.append("group by " + innerGroupByClause);
}
}
} else if (getPanelTiming().equals(QueryTimingHandler.SAME)
|| getPanelTiming().equals(QueryTimingHandler.SAMEVISIT)) {
if (returnInstanceToParent()) {
itemStatement = itemSql;
innerSelectClause = buildInnerSelectClause("f");
innerGroupByClause = buildInnerGroupByClause("f");
tableStatement = new StringBuilder();
tableStatement.append("select " + innerSelectClause + " "
+ "from " + schema + "observation_fact f, ");
if (useTempTables) {
tableStatement.append("#i" + suffix + " ");
} else if (parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.DERIVED_TABLES) {
tableStatement.append("(" + itemSql + ") ");
}
tableStatement.append(" i "
+ "where i.patient_num = f.patient_num "
+ "and i.encounter_num = f.encounter_num ");
if (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement()) {
tableStatement.append("group by " + innerGroupByClause);
}
}
}
if (useTempTables) {
if (itemStatement != null
&& itemStatement.trim().length() > 0) {
withItemSql
.append(parent.buildTempTableCheckDrop("i" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(buildSelectIntoStatement(
itemStatement, "i" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql
.append(parent.buildTempTableCheckDrop("t" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(buildSelectIntoStatement(
tableStatement.toString(), "t" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append("drop table #i" + suffix);
withItemSql.append(parent.getSqlDelimiter());
} else {
withItemSql
.append(parent.buildTempTableCheckDrop("t" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(buildSelectIntoStatement(
tableStatement.toString(), "t" + suffix));
withItemSql.append(parent.getSqlDelimiter());
}
} else if (parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.DERIVED_TABLES) {
withItemSql = new StringBuilder();
innerSelectClause = buildInnerSelectClause();
tSelect = "select " + innerSelectClause + " " + "from ("
+ tableStatement.toString() + ") t ";
} else {
if (itemStatement != null
&& itemStatement.trim().length() > 0) {
withItemSql.append("with i as ( " + "\n"
+ itemStatement + "\n" + " ) " + "\n");
withItemSql.append(", t as ( " + "\n"
+ tableStatement.toString() + "\n" + " ) "
+ "\n");
} else {
withItemSql.append("with t as ( " + "\n"
+ tableStatement.toString() + "\n" + " ) "
+ "\n");
}
}
if (parent.getServerType().equalsIgnoreCase(
DAOFactoryHelper.ORACLE) || parent.getServerType()
.equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
firstPanelItemSql = "insert into "
+ parent.getTempTableName() + " ("
+ insertValuesClause + ")" + "\n"
+ withItemSql.toString() + tSelect;
} else {
firstPanelItemSql = withItemSql.toString() + "insert into "
+ parent.getTempTableName() + " ("
+ insertValuesClause + ")" + "\n" + tSelect;
}
if (useTempTables) {
firstPanelItemSql += parent.getSqlDelimiter();
firstPanelItemSql += "drop table #t" + suffix + "";
}
if (addDelimiter) {
panelSql.append(parent.getSqlDelimiter());
}
panelSql.append(firstPanelItemSql);
addDelimiter = true;
}
return panelSql.toString();
}
/**
* Build Select Into Statement
*
* Takes a sql statement and creates sql statement that selects the results into a temporary table. This method should only be used on
* Sql Server or other database that supports the select into sybntax
*
* @param sqlStatement String that contains the sql statements to be saved into a temporary table
* @param tempTableName String that contains the name of the temporary table
* @return String properly formated sql statement that stores the result of the select statement into a temporary table
*/
private String buildSelectIntoStatement(String sqlStatement,
String tempTableName) {
TemporalQuerySimpleSqlParser sqlParser = new TemporalQuerySimpleSqlParser(
sqlStatement);
String select = sqlParser.getSelectClause();
String into = " into #" + tempTableName;
String from = sqlParser.getFromClause();
String where = sqlParser.getWhereClause();
String groupBy = sqlParser.getGroupByClause();
String having = sqlParser.getHavingClause();
String orderBy = sqlParser.getOrderByClause();
String selectIntoSql = "select "
+ select
+ " \n"
+ into
+ " \n"
+ "from "
+ from
+ " \n"
+ (where != null && where.trim().length() > 0 ? "where "
+ where + " \n" : "")
+ (groupBy != null && groupBy.trim().length() > 0 ? "group by "
+ groupBy + " \n" : "")
+ (having != null && having.trim().length() > 0 ? "having "
+ having + " \n" : "")
+ (orderBy != null && orderBy.trim().length() > 0 ? "order by "
+ orderBy + " \n" : "");
return selectIntoSql;
}
/**
* Build First Panel Invert Sql
*
* Take in the list of individual sql statements from this panel and creates a sql statement that is inverted. Current invert options are
* limited to either using a minus/except syntax or a not exists/not in syntax.
*
* @param itemSqlList List of individual sql statements for all items in this panel
* @return String sql statement that applies invert clause to all items in first panel
*/
private String buildFirstPanelInvertSql(List<String> itemSqlList) {
String insertValuesClause = buildInsertValuesClause();
StringBuilder withItemSql = new StringBuilder();
String invertSql = "";
String replaceString = "<!***PLACEHOLDER****!>";
String innerSelectClause = buildInnerSelectClause();
String innerGroupByClause = buildInnerGroupByClause();
String itemSql = unionItemSql(itemSqlList);
String itemStatement = "";
StringBuilder tableStatement = new StringBuilder(itemSql);
boolean useTempTables = false;
if (parent.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)
&& parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.TEMP_TABLES) {
useTempTables = true;
}
String schema = getDatabaseSchema();
if (schema == null)
schema = "";
else if (!schema.endsWith("."))
schema += ".";
// String suffix = getPanelId();
String suffix = "";
String tSelect = "select " + innerSelectClause + " " + "from "
+ (useTempTables ? "#t" + suffix + " " : "") + " t";
if (getPanelTiming().equals(QueryTimingHandler.ANY)) {
// this means there are negation panels behind this one
if (returnInstanceToParent()) {
itemStatement = itemSql;
innerSelectClause = buildInnerSelectClause("f");
innerGroupByClause = buildInnerGroupByClause("f");
tableStatement = new StringBuilder();
tableStatement.append("select " + innerSelectClause + " "
+ "from " + schema + "observation_fact f, ");
if (useTempTables) {
tableStatement.append("#i" + suffix + " ");
} else if (parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.DERIVED_TABLES) {
tableStatement.append("(" + replaceString + ") ");
}
tableStatement.append(" i "
+ "where i.patient_num = f.patient_num ");
if (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement()) {
tableStatement.append("group by " + innerGroupByClause);
}
} else if (returnEncounterToParent()) {
itemStatement = itemSql;
innerSelectClause = buildInnerSelectClause("v");
innerGroupByClause = buildInnerGroupByClause("v");
tableStatement = new StringBuilder();
tableStatement.append("select " + innerSelectClause + " "
+ "from " + schema + "visit_dimension v, ");
if (useTempTables) {
tableStatement.append("#i" + suffix + " ");
} else if (parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.DERIVED_TABLES) {
tableStatement.append("(" + replaceString + ") ");
}
tableStatement.append(" i "
+ "where i.patient_num = v.patient_num ");
if (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement()) {
tableStatement.append("group by " + innerGroupByClause);
}
}
} else if (getPanelTiming().equals(QueryTimingHandler.SAME)
|| getPanelTiming().equals(QueryTimingHandler.SAMEVISIT)) {
if (returnInstanceToParent()) {
itemStatement = itemSql;
innerSelectClause = buildInnerSelectClause("f");
innerGroupByClause = buildInnerGroupByClause("f");
tableStatement = new StringBuilder();
tableStatement.append("select " + innerSelectClause + " "
+ "from " + schema + "observation_fact f, ");
if (useTempTables) {
tableStatement.append("#i" + suffix + " ");
} else if (parent.getQueryOptions().getQueryConstraintLogic() == QueryConstraintStrategy.DERIVED_TABLES) {
tableStatement.append("(" + replaceString + ") ");
}
tableStatement.append(" i "
+ "where i.patient_num = f.patient_num "
+ "and i.encounter_num = f.encounter_num ");
if (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement()) {
tableStatement.append("group by " + innerGroupByClause);
}
}
}
if (useTempTables) {
if (itemStatement != null && itemStatement.trim().length() > 0) {
withItemSql.append(parent.buildTempTableCheckDrop("y" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(buildSelectIntoStatement(itemStatement, "y"
+ suffix));
withItemSql.append(parent.getSqlDelimiter());
String invertClause = "";
if (parent.getQueryOptions().getInvertedConstraintLogic()==InvertedConstraintStrategy.MINUS_CLAUSE){
invertClause = buildInvertExceptSql("#y" + suffix);
}
else {
invertClause = buildInvertNotExistsSql("#y" + suffix);
}
withItemSql.append(parent.buildTempTableCheckDrop("i" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(buildSelectIntoStatement(invertClause, "i"
+ suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append("drop table #y" + suffix + "");
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(parent.buildTempTableCheckDrop("t" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(buildSelectIntoStatement(
tableStatement.toString(), "t" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append("drop table #i" + suffix);
withItemSql.append(parent.getSqlDelimiter());
} else {
withItemSql.append(parent.buildTempTableCheckDrop("y" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(buildSelectIntoStatement(itemSql, "y"
+ suffix));
withItemSql.append(parent.getSqlDelimiter());
String invertClause = "";
if (parent.getQueryOptions().getInvertedConstraintLogic()==InvertedConstraintStrategy.MINUS_CLAUSE){
invertClause = buildInvertExceptSql("#y" + suffix);
}
else {
invertClause = buildInvertNotExistsSql("#y" + suffix);
}
withItemSql.append(parent.buildTempTableCheckDrop("i" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append(buildSelectIntoStatement(invertClause, "i"
+ suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append("drop table #y" + suffix);
withItemSql.append(parent.getSqlDelimiter());
String invertInsertSql = buildInvertInsertSelectSql("i");
withItemSql.append(parent.buildTempTableCheckDrop("t" + suffix));
withItemSql.append(parent.getSqlDelimiter());
withItemSql
.append("select " + invertInsertSql + " " + "into #t"
+ suffix + " " + "from #i" + suffix + " i ");
withItemSql.append(parent.getSqlDelimiter());
withItemSql.append("drop table #i" + suffix);
withItemSql.append(parent.getSqlDelimiter());
}
invertSql = withItemSql.toString() + "insert into "
+ parent.getTempTableName() + " (" + insertValuesClause
+ ")" + "\n" + tSelect + parent.getSqlDelimiter()
+ "drop table #t" + suffix;
} else if (parent.getQueryOptions().getQueryConstraintLogic()==QueryConstraintStrategy.DERIVED_TABLES){
if (itemStatement != null && itemStatement.trim().length() > 0) {
String invertClause = "";
if (parent.getQueryOptions().getInvertedConstraintLogic()==InvertedConstraintStrategy.MINUS_CLAUSE){
invertClause = buildInvertExceptSql("y");
}
else {
invertClause = buildInvertNotExistsSql(itemStatement, "y");
}
String derivedSql = tableStatement.toString().replace(replaceString, invertClause);
withItemSql.append(derivedSql);
} else {
String invertClause = "";
if (parent.getQueryOptions().getInvertedConstraintLogic()==InvertedConstraintStrategy.MINUS_CLAUSE){
invertClause = buildInvertExceptSql("y");
}
else {
invertClause = buildInvertNotExistsSql(itemSql, "y");
}
String invertInsertSql = buildInvertInsertSelectSql("i");
withItemSql.append(" select " +
invertInsertSql + " from (" +
invertClause +
") i \n");
}
innerSelectClause = buildInnerSelectClause();
tSelect = "select " + innerSelectClause + " " + "from ("
+ withItemSql.toString() + ") t ";
if (parent.getServerType()
.equalsIgnoreCase(DAOFactoryHelper.ORACLE) || parent.getServerType()
.equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
invertSql = "insert into " + parent.getTempTableName() + " ("
+ insertValuesClause + ")" + "\n"
+ tSelect;
} else {
invertSql = "insert into "
+ parent.getTempTableName() + " (" + insertValuesClause
+ ")" + "\n" + tSelect;
}
} else {
if (itemStatement != null && itemStatement.trim().length() > 0) {
withItemSql.append("with y as ( " + "\n" + itemStatement + "\n"
+ " ) " + "\n");
String invertClause = "";
if (parent.getQueryOptions().getInvertedConstraintLogic()==InvertedConstraintStrategy.MINUS_CLAUSE){
invertClause = buildInvertExceptSql("y");
}
else {
invertClause = buildInvertNotExistsSql("y");
}
withItemSql.append(", i as ( " + "\n" + invertClause + "\n"
+ " ) " + "\n");
withItemSql.append(", t as ( " + "\n"
+ tableStatement.toString() + "\n" + " ) " + "\n");
} else {
withItemSql.append("with y as ( " + "\n" + itemSql + "\n"
+ " ) " + "\n");
String invertClause = "";
if (parent.getQueryOptions().getInvertedConstraintLogic()==InvertedConstraintStrategy.MINUS_CLAUSE){
invertClause = buildInvertExceptSql("y");
}
else {
invertClause = buildInvertNotExistsSql("y");
}
withItemSql.append(", i as ( " + "\n" + invertClause + "\n"
+ " ) " + "\n");
String invertInsertSql = buildInvertInsertSelectSql("i");
withItemSql.append(", t as ( " + "\n" + " select "
+ invertInsertSql + " from i " + "\n" + " ) " + "\n");
}
if (parent.getServerType()
.equalsIgnoreCase(DAOFactoryHelper.ORACLE) || parent.getServerType()
.equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
invertSql = "insert into " + parent.getTempTableName() + " ("
+ insertValuesClause + ")" + "\n"
+ withItemSql.toString() + tSelect;
} else {
invertSql = withItemSql.toString() + "insert into "
+ parent.getTempTableName() + " (" + insertValuesClause
+ ")" + "\n" + tSelect;
}
}
return invertSql;
}
/**
* First Panel Item Sql With Occurrence
*
* This method returns the sql representation of all items in a panel when
* the panel is the first panel in a query. This method is only accessed
* when usePanelLevelOccurrence flag is set to true - in which case the
* occurrence is processed on the panel level instead of in an item by item
* basis
*
* @param itemSqlList List of individual sql statements for all items in this panel
* @return String sql representation for the first panel in the query
* @throws I2B2DAOException
* thrown when an i2b2 data related error is encountered
*/
private String firstPanelItemSqlWithOccurrence(List<String> itemSqlList)
throws I2B2DAOException {
String itemSql = consolidateItemSql(itemSqlList);
String insertValuesClause = buildInsertValuesClause();
String innerSelectClause = buildInnerSelectClause("");
String innerGroupByClause = buildInnerGroupByClause();
TotalItemOccurrenceHandler totalItemOccurrencHandler = new TotalItemOccurrenceHandler();
String totalItemOccurrenceClause = totalItemOccurrencHandler
.buildTotalItemOccurrenceClause(this.getTotalOccurrences());
StringBuilder withItemSql = new StringBuilder();
String firstPanelItemSql = "";
String schema = getDatabaseSchema();
if (schema == null)
schema = "";
else if (!schema.endsWith("."))
schema += ".";
String tSelect = "select " + innerSelectClause + " " + "from t";
if (getPanelTiming().equals(QueryTimingHandler.ANY)) {
// this means there are negation panels behind this one
if (returnInstanceToParent()) {
withItemSql
.append("with sub_t as ( "
+ "\n"
+ itemSql
+ "\n"
+ " ), "
+ "\n"
+ "y as ("
+ "select "
+ innerSelectClause
+ " "
+ "from sub_t "
+ "where patient_num in (select patient_num from sub_t group by patient_num having sum(fact_count) "
+ totalItemOccurrenceClause + ") " + ") \n");
innerSelectClause = buildInnerSelectClause("f");
innerGroupByClause = buildInnerGroupByClause("f");
withItemSql
.append(", t as ("
+ "\n"
+ "select "
+ innerSelectClause
+ " "
+ "from "
+ schema
+ "observation_fact f "
+ "where exists ("
+ "select 1 "
+ "from y "
+ "where y.patient_num = f.patient_num) "
+ (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement() ? "group by "
+ innerGroupByClause
: "") + ") " + "\n");
} else if (returnEncounterToParent()) {
withItemSql
.append("with sub_t as ( "
+ "\n"
+ itemSql
+ "\n"
+ " ), "
+ "\n"
+ "y as ("
+ "select "
+ innerSelectClause
+ " "
+ "from sub_t "
+ "where patient_num in (select patient_num from sub_t group by patient_num having sum(fact_count) "
+ totalItemOccurrenceClause + ") " + ") \n");
innerSelectClause = buildInnerSelectClause("v");
innerGroupByClause = buildInnerGroupByClause("v");
withItemSql
.append(", t as ("
+ "\n"
+ "select "
+ innerSelectClause
+ " "
+ "from "
+ schema
+ "visit_dimension v "
+ "where exists ("
+ "select 1 "
+ "from y "
+ "where y.patient_num = v.patient_num) "
+ (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement() ? "group by "
+ innerGroupByClause
: "") + ") " + "\n");
}
} else if (getPanelTiming().equals(QueryTimingHandler.SAME)
|| getPanelTiming().equals(QueryTimingHandler.SAMEVISIT)) {
if (returnInstanceToParent()) {
withItemSql
.append("with sub_t as ( "
+ "\n"
+ itemSql
+ "\n"
+ " ), "
+ "\n"
+ "y as ("
+ "select "
+ innerSelectClause
+ " "
+ "from sub_t "
+ "where patient_num in (select patient_num from sub_t group by patient_num having sum(fact_count) "
+ totalItemOccurrenceClause + ") " + ") \n");
innerSelectClause = buildInnerSelectClause("f");
innerGroupByClause = buildInnerGroupByClause("f");
withItemSql
.append(", t as ("
+ "\n"
+ "select "
+ innerSelectClause
+ " "
+ "from "
+ schema
+ "observation_fact f "
+ "where exists ("
+ "select 1 "
+ "from y "
+ "where y.patient_num = f.patient_num "
+ "and y.encounter_num = f.encounter_num) "
+ (innerGroupByClause != null
&& innerGroupByClause.trim().length() > 0
&& parent.getQueryOptions().useItemGroupByStatement() ? "group by "
+ innerGroupByClause
: "") + ") " + "\n");
}
} else {
withItemSql
.append("with sub_t as ( "
+ "\n"
+ itemSql
+ "\n"
+ " ), "
+ "\n"
+ "t as ("
+ "select "
+ innerSelectClause
+ " "
+ "from sub_t "
+ "where patient_num in (select patient_num from sub_t group by patient_num having sum(fact_count) "
+ totalItemOccurrenceClause + ") " + ") \n");
}
if (parent.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE) || parent.getServerType()
.equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
firstPanelItemSql = "insert into " + parent.getTempTableName()
+ " (" + insertValuesClause + ")" + "\n" + withItemSql
+ tSelect;
} else {
firstPanelItemSql = withItemSql + "insert into "
+ parent.getTempTableName() + " (" + insertValuesClause
+ ")" + "\n" + tSelect;
}
return firstPanelItemSql;
}
/**
* Build Insert Values Clause
*
* Returns a list of columns from the temporary table that will be populated
* in an insert
*
* @return String sql list of columns from temporary table that will be
* inserted into
*/
public String buildInsertValuesClause() {
String insertValuesClause = "";
if (returnInstanceToParent()
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM)) {
insertValuesClause = "provider_id, start_date, concept_cd, instance_num, encounter_num, patient_num";
} else if (returnEncounterToParent()
|| getPanelTiming().equalsIgnoreCase(QueryTimingHandler.SAME)
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)) {
insertValuesClause = "encounter_num, patient_num";
} else {
insertValuesClause = "patient_num";
}
insertValuesClause += ", panel_count";
return insertValuesClause;
}
/**
* Build Inner Select Clause
*
* Returns a list of columns that will be inserted into the temporary table
* for each item
*
* @return String sql statement that specifies the columns that should be
* returned from the item sql
*/
public String buildInnerSelectClause() {
return buildInnerSelectClause("t");
}
/**
* Build Inner Select Clause
*
* Returns a list of columns that will be inserted into the temporary table
* for each item
*
* @param tableAlias
* String that specifies the table alias to use when building sql
* string
* @return String sql statement that specifies the columns that should be
* returned from the item sql
*/
public String buildInnerSelectClause(String tableAlias) {
if (tableAlias != null && tableAlias.trim().length() > 0
&& !tableAlias.trim().endsWith("."))
tableAlias += ".";
String innerSelectClause = " ";
if (!isFirstPanelInQuery()) {
innerSelectClause = " 1 as panel_count ";
} else {
if (returnInstanceToParent()
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM)) {
innerSelectClause = "" + tableAlias + "provider_id, "
+ tableAlias + "start_date, " + tableAlias
+ "concept_cd, " + tableAlias + "instance_num, "
+ tableAlias + "encounter_num, ";
} else if (returnEncounterToParent()
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAME)
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)) {
innerSelectClause = "" + tableAlias + "encounter_num, ";
}
innerSelectClause += "" + tableAlias
+ "patient_num, 0 as panel_count ";
}
return innerSelectClause;
}
/**
* Build Inner Group By Clause
*
* Returns a list of columns that will be used to group the results of the panel sql statement
*
* @return String sql statement that specifies the columns that should be used to group the results
*/
public String buildInnerGroupByClause() {
return buildInnerGroupByClause("t");
}
/**
* Build Inner Group By Clause
*
* Returns a list of columns that will be used to group the results of the panel sql statement
*
* @param tableAlias
* String that specifies the table alias to use when building sql
* string
* @return String sql statement that specifies the columns that should be used to group the results
*/
public String buildInnerGroupByClause(String tableAlias) {
if (tableAlias != null && tableAlias.trim().length() > 0
&& !tableAlias.trim().endsWith("."))
tableAlias += ".";
String innerSelectClause = " ";
if (!isFirstPanelInQuery()) {
innerSelectClause = " 1 as panel_count ";
} else {
if (returnInstanceToParent()
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM)) {
innerSelectClause = "" + tableAlias + "provider_id, "
+ tableAlias + "start_date, " + tableAlias
+ "concept_cd, " + tableAlias + "instance_num, "
+ tableAlias + "encounter_num, ";
} else if (returnEncounterToParent()
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAME)
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)) {
innerSelectClause = "" + tableAlias + "encounter_num, ";
}
innerSelectClause += "" + tableAlias + "patient_num ";
}
return innerSelectClause;
}
/**
* Non First Panel Item Sql With Occurrence
*
* This method returns the sql representation of all items in a panel when
* the panel is not the first panel in a query. This method is only accessed
* when usePanelLevelOccurrence flag is set to true - in which case the
* occurrence is processed on the panel level instead of in an item by item
* basis
*
* @param panelIndex
* int update index for the given panel
* @param itemSqlList
* List of individual sql statements for all items in this panel
* @return String sql representation for updating temporary table with panel
* information
* @throws I2B2DAOException
* thrown when an i2b2 data related error is encountered
*/
private String nonFirstPanelItemSqlWithOccurrence(int panelIndex,
List<String> itemSqlList) throws I2B2DAOException {
String encounterNumClause = " ", instanceNumClause = " ";
String tempTableName = parent.getTempTableName();
int oldPanelIndex = panelIndex - 1;
if (getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM)) {
instanceNumClause = " and " // + parent.getDatabaseSchema()
+ tempTableName
+ ".encounter_num = t.encounter_num and "
+ tempTableName
+ ".instance_num = t.instance_num and "
+ tempTableName
+ ".start_date = t.start_date and "
+ tempTableName
+ ".concept_cd = t.concept_cd and "
+ tempTableName + ".provider_id = t.provider_id ";
} else if (getPanelTiming().equalsIgnoreCase(QueryTimingHandler.SAME)
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)) {
encounterNumClause = " and " + tempTableName
+ ".encounter_num = t.encounter_num ";
}
String itemSql = consolidateItemSql(itemSqlList);
String nonFirstPanelItemSql = "";
if (this.isPanelInverted()) {
oldPanelIndex = 0;
panelIndex = -1;
}
TotalItemOccurrenceHandler totalItemOccurrencHandler = new TotalItemOccurrenceHandler();
String totalItemOccurrenceClause = totalItemOccurrencHandler
.buildTotalItemOccurrenceClause(this.getTotalOccurrences());
String withItemSql = "with sub_t as ( " + "\n" + itemSql + "\n"
+ " ) \n";
if (parent.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE) || parent.getServerType()
.equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
nonFirstPanelItemSql += "update "
+ tempTableName
+ " set panel_count ="
+ panelIndex
+ " where "
+ tempTableName
+ ".panel_count = "
+ oldPanelIndex
+ " and exists ( "
+ withItemSql
+ "select 1 "
+ "from ("
+ "select * "
+ "from sub_t "
+ "where patient_num in (select patient_num from sub_t group by patient_num having sum(fact_count) "
+ totalItemOccurrenceClause + ") " + ") t " + "where "
+ tempTableName + ".patient_num = t.patient_num "
+ encounterNumClause + instanceNumClause + " ) ";
} else {
nonFirstPanelItemSql += withItemSql
+ "update "
+ tempTableName
+ " set panel_count ="
+ panelIndex
+ " where "
+ tempTableName
+ ".panel_count = "
+ oldPanelIndex
+ " and exists ( "
+ "select 1 "
+ "from ("
+ "select * "
+ "from sub_t "
+ "where patient_num in (select patient_num from sub_t group by patient_num having sum(fact_count) "
+ totalItemOccurrenceClause + ") " + ") t " + "where "
+ tempTableName + ".patient_num = t.patient_num "
+ encounterNumClause + instanceNumClause + " ) ";
}
return nonFirstPanelItemSql;
}
/**
* Non First Panel Item Sql
*
* Processes item sql from all items in panel that is not the first panel in a subquery.
* Non first panel items are processed as an update statement to the
* temporary table rather than an insert
*
* @param itemSqlList List of individual sql statements for all items in this panel
* @param panelIndex
* int update index for the given panel
* @return String sql representation that joins the item sql to the panel
* sql
*/
private String nonFirstPanelItemSql(List<String> itemSqlList, int panelIndex) {
StringBuilder panelSql = new StringBuilder();
boolean addDelimiter = false;
for (String itemSql : itemSqlList) {
String encounterNumClause = " ", instanceNumClause = " ";
String tempTableName = parent.getTempTableName();
int oldPanelIndex = panelIndex - 1;
if (getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM)) {
instanceNumClause = " and " // + parent.getDatabaseSchema()
+ tempTableName
+ ".encounter_num = t.encounter_num and "
+ tempTableName
+ ".instance_num = t.instance_num and "
+ tempTableName
+ ".start_date = t.start_date and "
+ tempTableName
+ ".concept_cd = t.concept_cd and "
+ tempTableName + ".provider_id = t.provider_id ";
} else if (getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAME)
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)) {
encounterNumClause = " and " + tempTableName
+ ".encounter_num = t.encounter_num ";
}
String nonFirstPanelItemSql = "";
/*
* "with t as ( " + "\n" + itemSql + "\n" + " ) " + "\n" + "\n";
*/
if (this.isPanelInverted()) {
if (oldPanelIndex<0)
oldPanelIndex = 0;
nonFirstPanelItemSql += " update " + tempTableName
+ " set panel_count = -1 " + " where " + tempTableName
+ ".panel_count = " + oldPanelIndex + " and exists ( "
+ "select 1 " + "from (" + itemSql + ") t " + "where "
+ tempTableName + ".patient_num = t.patient_num "
+ encounterNumClause + instanceNumClause + " ) ";
} else {
nonFirstPanelItemSql += "update " + tempTableName
+ " set panel_count =" + panelIndex + " where "
+ tempTableName + ".panel_count = " + oldPanelIndex
+ " and exists ( " + "select 1 " + "from (" + itemSql
+ ") t " + "where " + tempTableName
+ ".patient_num = t.patient_num " + encounterNumClause
+ instanceNumClause + " ) ";
}
if (addDelimiter) {
panelSql.append(parent.getSqlDelimiter());
}
panelSql.append(nonFirstPanelItemSql);
addDelimiter = true;
}
return panelSql.toString();
}
/**
* Build Invert Main Table Sql
*
* Constructs sql statement that selects the correct columns from the superset of items from which
* an invert can be applied - through either a minus/except or not in/not exists clause
*
* @return String sql statement that contains sql for accessing main table sql
*/
public String buildInvertMainTableSql() {
String selectClause = "";
String whereClause = "";
String groupByClause = "";
String patientTable = "patient_dimension p ";
String instanceTable = "observation_fact f ";
String visitTable = "visit_dimension v ";
String invertTableName = patientTable;
if (getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM)) {
selectClause = "f.provider_id, f.start_date, f.concept_cd, f.instance_num, f.encounter_num, f.patient_num";
invertTableName = instanceTable;
} else if (getPanelTiming().equalsIgnoreCase(QueryTimingHandler.SAME)
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)) {
invertTableName = visitTable;
selectClause = "v.encounter_num, v.patient_num";
} else {
selectClause = "p.patient_num";
}
String invertSql = "select " + selectClause + ", 0 panel_count"
+ " from " + parent.getDatabaseSchema() + invertTableName
+ whereClause + groupByClause;
return invertSql;
}
/**
* Build Invert Insert Select Sql
*
* Constructs select clause for use in a larger statement. Clause contains the correct columns
* based on panel and query timing models
*
* @param tableAlias String alias for table referenced in from clause
* @return String select clause for inclusion in larger statement
*/
protected String buildInvertInsertSelectSql(String tableAlias) {
if (tableAlias != null && tableAlias.trim().length() > 0)
tableAlias = tableAlias + ".";
else
tableAlias = "";
String insertSelectClause = "";
if (getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM)) {
insertSelectClause = tableAlias + "provider_id, " + tableAlias
+ "start_date, " + tableAlias + "concept_cd, " + tableAlias
+ "instance_num, " + tableAlias + "encounter_num, "
+ tableAlias + "patient_num";
} else if (getPanelTiming().equalsIgnoreCase(QueryTimingHandler.SAME)
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)) {
insertSelectClause = " " + tableAlias + "encounter_num, "
+ tableAlias + "patient_num";
} else {
insertSelectClause = " " + tableAlias + "patient_num";
}
insertSelectClause += ", 0 as panel_count";
return insertSelectClause;
}
/**
* Build Invert Not Except Sql
*
* Constructs with clause for use in larger invert statement. Clause contains proper minus/except
* constraints based on the panel timing
*
* @param withAlias String alias of table or with clause for use in referencing column names
* @return String not exists clause for use in larger sql invert statement
*/
protected String buildInvertExceptSql(String withAlias) {
if (withAlias == null)
withAlias = "y";
String minusOperator = "minus";
if (!parent.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
minusOperator = "except";
}
StringBuilder exceptClause = new StringBuilder();
exceptClause.append(buildInvertMainTableSql() + "\n");
exceptClause.append(" " + minusOperator + "\n");
String invertInsertSql = buildInvertInsertSelectSql(withAlias);
exceptClause.append("select " + invertInsertSql + " from " + withAlias
+ " ");
return exceptClause.toString();
}
/**
* Build Invert Not Exists Sql
*
* Constructs with clause for use in larger invert statement. Clause contains proper
* constraints based on the panel timing
*
* @param withAlias String alias of table or with clause for use in referencing column names
* @return String not exists clause for use in larger sql invert statement
*/
protected String buildInvertNotExistsSql(String withAlias){
return buildInvertNotExistsSql("", withAlias);
}
/**
* Build Invert Not Exists Sql
*
* Constructs with clause for use in larger invert statement. Clause contains proper not in/not exists
* constraints based on the panel timing
*
* @param withTable String name of table or with clause name
* @param withAlias String alias of table or with clause for use in referencing column names
* @return String not exists clause for use in larger sql invert statement
*/
protected String buildInvertNotExistsSql(String withTable, String withAlias) {
if (withAlias == null)
withAlias = "y";
StringBuilder notExistsClause = new StringBuilder();
notExistsClause.append(buildInvertMainTableSql() + "\n");
notExistsClause.append("where not exists (\n");
notExistsClause.append("select 1 from " +
(withTable!=null&&withTable.trim().length()>0 ? withTable + " " : "") +
withAlias + " \n");
if (getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM)) {
notExistsClause.append("where " + withAlias
+ ".patient_num = f.patient_num ");
notExistsClause.append("and " + withAlias
+ ".encounter_num = f.encounter_num ");
notExistsClause.append("and " + withAlias
+ ".start_date = f.start_date ");
notExistsClause.append("and " + withAlias
+ ".concept_cd = f.concept_cd ");
notExistsClause.append("and " + withAlias
+ ".instance_num = f.instance_num ");
notExistsClause.append("and " + withAlias
+ ".provider_id = f.provider_id) ");
} else if (getPanelTiming().equalsIgnoreCase(QueryTimingHandler.SAME)
|| getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)) {
notExistsClause.append("where " + withAlias
+ ".patient_num = v.patient_num ");
notExistsClause.append("and " + withAlias
+ ".encounter_num = v.encounter_num) ");
} else {
notExistsClause.append("where " + withAlias
+ ".patient_num = p.patient_num) ");
}
return notExistsClause.toString();
}
@Override
public int compareTo(Object element) {
if (element.getClass().equals((TemporalPanel.class))) {
TemporalPanel tp2 = (TemporalPanel) element;
Integer tp1Score = this.getTimingScore();
Integer tp2Score = tp2.getTimingScore();
int compare = tp1Score.compareTo(tp2Score);
if (compare == 0) {
tp1Score = this.getEstimatedTotal();
tp2Score = tp2.getEstimatedTotal();
compare = tp1Score.compareTo(tp2Score);
if (compare == 0) {
tp1Score = this.basePanel.getPanelNumber();
tp2Score = tp2.basePanel.getPanelNumber();
compare = tp1Score.compareTo(tp2Score);
if (compare == 0) {
return this.toString().compareTo(tp2.toString());
} else
return compare;
} else
return compare;
} else
return compare;
} else {
return this.toString().compareTo(element.toString());
}
}
/**
* Get Timing Score
*
* Calculates the timing score of the panel. Timing scores are used to
* properly sort panels so query processing can be optimized
*
* @return int represents the timing score of the panel
*/
private int getTimingScore() {
String timing = getPanelTiming();
int score = 0;
if (timing == null || timing.trim().length() == 0)
return MAXTIMINGSCORE;
else if (timing.equalsIgnoreCase(QueryTimingHandler.ANY))
score = MAXTIMINGSCORE - 1;
else if (timing.equalsIgnoreCase(QueryTimingHandler.SAME))
score = MAXTIMINGSCORE - 2;
else if (timing.equalsIgnoreCase(QueryTimingHandler.SAMEVISIT))
score = MAXTIMINGSCORE - 2;
else if (timing.equalsIgnoreCase(QueryTimingHandler.SAMEINSTANCENUM))
score = MAXTIMINGSCORE - 3;
else
score = MAXTIMINGSCORE - 4;
if (isPanelInverted())
return (MAXTIMINGSCORE * 2) - (MAXTIMINGSCORE - score);
else
return score;
}
/**
* Get Estimated Total
*
* Calculate the estimated number of patients that will be returned by this
* panel. This method accounts for missing values and inverted panels when
* estimated the number of patients returned. Estimated counts are used to
* help sort panels in a way that optimizes query performance
*
* @return int estimated count of patients returned by this panel
*/
private int getEstimatedTotal() {
int totalCount = getSumOfPanelItemCounts();
double missingCount = getMissingItemTotals();
if (missingCount > 0) {
double size = getItemList().size();
double knownSize = size - missingCount;
if (knownSize == 0)
return Integer.MAX_VALUE;
// estimate total size by averaging known items and applying it to
// all items
double dblTotal = totalCount;
totalCount = (int) ((dblTotal / knownSize) * size);
}
if (this.isPanelInverted())
totalCount = Integer.MAX_VALUE - totalCount;
return totalCount;
}
/**
* Is Panel Inverted
*
* @return boolean true if the panel has the inverted flag set, otherwise
* false
*/
public boolean isPanelInverted() {
return basePanel.getInvert() == 1;
}
/**
* Get Sum Of Patient Item Counts
*
* Sum the estimated patient counts for all items in the panel
*
* @return int total of estimated counts from items within the panel
*/
public int getSumOfPanelItemCounts() {
return estimatedPanelSize;
}
/**
* Get Panel Timing
*
* Return timing to use for this panel. If no timing is specified on the
* panel level, than the timing for the query is used.
*
* @return String representation of the timing used for this panel
*/
public String getPanelTiming() {
if (basePanel.getPanelTiming() == null
|| basePanel.getPanelTiming().trim().length() == 0)
return parent.getQueryTiming();
else
return basePanel.getPanelTiming();
}
/**
* Return Encounter To Parent
*
* @return boolean true if panel is needs to return encounter num
* results to parent query
*/
private boolean returnEncounterToParent() {
return parent.returnEncounterNum();
}
/**
* Return Instance to Parent
*
* @return boolean true if panel is needs to return instance based columns
* results to parent query
*/
private boolean returnInstanceToParent() {
return parent.returnInstanceNum();
}
/**
* Get Accuracy Scale
*
* @return int accuracy as included in the original query xml
*/
public int getAccuracyScale() {
return basePanel.getPanelAccuracyScale();
}
/**
* Get Item List
*
* @return List<TemporalPanelItem> list of items in this panel
*/
public List<TemporalPanelItem> getItemList() {
return panelItemList;
}
/**
* Get Security Type
*
* @return SecurityType returns security to use for this query
*/
protected SecurityType getSecurityType() {
return parent.getSecurityType();
}
/**
* Get Requestor Security Type
*
* @return SecurityType returns security used by requestor when submitting
* this query
*/
protected SecurityType getRequestorSecurityType() {
return parent.getRequestorSecurityType();
}
/**
* Get Project Id
*
* @return String project id used for this query
*/
protected String getProjectId() {
return parent.getProjectId();
}
/**
* Get Data Source Lookup
*
* @return DataSourceLookup data source information used for submitting
* query to database
*/
protected DataSourceLookup getDataSourceLookup() {
return parent.getDataSourceLookup();
}
/**
* Get Database Schema
*
* @return String name of schema to use when referencing tables
*/
protected String getDatabaseSchema() {
return parent.getDatabaseSchema();
}
/**
* Build Date Constraint Sql
*
* Builds string sql statement that contains the date constraint from the panel leve
*
* @return String sql statement that contains the date constraint from the panel level
*/
public String buildDateConstraintSql() {
return buildDateConstraintSql("");
}
/**
* Build Date Constraint Sql
*
* Builds string sql statement that contains the date constraint from the panel leve
*
* @param tableAlias String alias of the table to use when reference columns
* @return String sql statement that contains the date constraint from the panel level
*/
public String buildDateConstraintSql(String tableAlias) {
DateConstrainUtil dateConstrainUtil = new DateConstrainUtil(
parent.getDataSourceLookup());
return dateConstrainUtil.buildPanelDateSql(basePanel, tableAlias);
}
/**
* Get Server Type
*
* Returns name of the underlying database type. Currently, only Oracle and SqlServer are supported
*
* @return String name of the database server type
*/
public String getServerType() {
return parent.getServerType();
}
/**
* Get Project Parameter Map
*
* Returns the Map of project parameter values passed in when the query was created
*
* @return Map the project parameter map that was passed into the query
*/
protected Map getProjectParameterMap() {
return parent.getProjectParameterMap();
}
public int getMissingItemTotals() {
return missingItemTotals;
}
/**
* Has Panel Date Constraint
*
* Returns whether or not this panel has a panel level date constraint: date from, date to, or both
*
* @return Boolean true if this panel has a panel level date constraint
*/
public boolean hasPanelDateConstraint() {
if (basePanel.getPanelDateFrom() != null
|| basePanel.getPanelDateTo() != null)
return true;
else
return false;
}
/**
* Has Panel Occurrence Constraint
*
* Returns whether or not this panel has an occurrence constraint other than the default
*
* @return Boolean true if the panel has an occurrence constraint > 1, else false
*/
public boolean hasPanelOccurrenceConstraint() {
if (this.basePanel.getTotalItemOccurrences() != null
&& this.basePanel.getTotalItemOccurrences().getOperator() != null) {
if ((this.basePanel.getTotalItemOccurrences().getOperator() == TotOccuranceOperatorType.GE)
&& (this.basePanel.getTotalItemOccurrences().getValue() == 1))
return false;
else
return true;
} else
return false;
}
/**
* Is First Panel In Query
*
* Determines whether this panel is the first panel in the query
*
* @return Boolean true if panel is first panel in query, otherwise false
*/
public boolean isFirstPanelInQuery() {
return parent.getPanelIndex(this) == 0;
}
/**
* Get Total Occurrences
*
* Gets the total occurrences constraint for this panel
*
* @return TotalItemOccurrences occurrence constraint from main query
*/
public TotalItemOccurrences getTotalOccurrences() {
return this.basePanel.getTotalItemOccurrences();
}
/**
* Get Total Occurrences Operator
*
* Get the operator for the total occurrences constraint for this panel
*
* @return String operator for total occurrences constraint
*/
public String getTotalOccurrenceOperator() {
if (this.basePanel.getTotalItemOccurrences() != null)
return this.basePanel.getTotalItemOccurrences().getOperator()
.toString();
else
return "";
}
/**
* Allow Large Text Value Constrain Flag
*
* Return whether or not constraints on large text values are allowed
*
* @return Boolean true if large text constraints are allowed, otherwise false
*/
protected boolean allowLargeTextValueConstrainFlag() {
return parent.allowLargeTextValueConstrainFlag();
}
/**
* Appply Occurrence to Panel Level
*
* Return whether or not occurrences should be applied on the panel level instead of on each item **NOTE: This is experimental funcationality and not current supported**
*
* @return Boolean true if occurrence should be applied over all items in a query instead of on an item by item basis, otherwise false
*/
protected boolean applyOccurrenceToPanelLevel() {
return parent.getQueryOptions().usePanelLevelOccurrence();
}
/**
* Get Processing Level
*
* Returns the processing level of the current query. Default value is 1, when processing an embedded query, processing level is incremented
*
* @return int Processing level of current query
*/
protected int getProcessingLevel() {
return parent.getProcessingLevel();
}
/**
* Add Pre Processing Sql
*
* Add sql statement to run before main sql statement is run
*
* @param sql String sql statement to be processed before main sql statement has been run
*/
protected void addPreProcessingSql(String sql) {
parent.addPreProcessingSql(sql);
}
/**
* Add Post Processing Sql
*
* Add sql statement to run after main sql statement has run
*
* @param sql String sql statement to processed after main sql statement has been run
*/
protected void addPostProcessingSql(String sql) {
parent.addPostProcessingSql(sql);
}
/**
* Search For Query In Request Definition
*
* Looks for query defintion with the specified query id in the parent query xml definition
*
* @param subQueryId String specified the query id for the query to look for
* @return QueryDefinitionType if query is found, otherwise returns null
*/
protected QueryDefinitionType searchForQueryInRequestDefinition(String subQueryId){
return parent.searchForQueryInRequestDefinition(subQueryId);
}
/**
* Is Patient Only Query
*
* @return true if only patient num is required to be returned by this panel
*/
protected boolean isPatientOnlyQuery() {
if (this.returnEncounterToParent()
|| this.returnInstanceToParent()
|| this.getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAME)
|| this.getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEVISIT)
|| this.getPanelTiming().equalsIgnoreCase(
QueryTimingHandler.SAMEINSTANCENUM))
return false;
else
return true;
}
/**
* Add Ignored Message
*
* @param errorMessage String error m
*/
public void addIgnoredMessage(String errorMessage) {
parent.addIgnoredMessage(errorMessage);
}
/**
* Get Query Options
*
* @return TemporalQueryOptions that are valid for this query
*/
protected TemporalQueryOptions getQueryOptions() {
return parent.getQueryOptions();
}
}