public EventSet getVisitByFact(
List<String> panelSqlList,
List<Integer> sqlParamCountList,
IInputOptionListHandler inputOptionListHandler,
boolean detailFlag,
boolean blobFlag,
boolean statusFlag)
throws I2B2DAOException {
EventSet eventSet = new EventSet();
I2B2PdoFactory.EventBuilder eventBuilder =
new I2B2PdoFactory().new EventBuilder(detailFlag, blobFlag, statusFlag);
VisitFactRelated eventFactRelated =
new VisitFactRelated(buildOutputOptionType(detailFlag, blobFlag, statusFlag));
eventFactRelated.setMetaDataParamList(this.metaDataParamList);
String selectClause = eventFactRelated.getSelectClause();
String serverType = dataSourceLookup.getServerType();
String factTempTable = "";
Connection conn = null;
PreparedStatement query = null;
try {
conn = dataSource.getConnection();
if (serverType.equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
factTempTable = getDbSchemaName() + FactRelatedQueryHandler.TEMP_FACT_PARAM_TABLE;
} else if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
log.debug("creating temp table");
java.sql.Statement tempStmt = conn.createStatement();
factTempTable = getDbSchemaName() + SQLServerFactRelatedQueryHandler.TEMP_FACT_PARAM_TABLE;
try {
tempStmt.executeUpdate("drop table " + factTempTable);
} catch (SQLException sqlex) {;
}
String createTempInputListTable =
"create table " + factTempTable + " ( set_index int, char_param1 varchar(500) )";
tempStmt.executeUpdate(createTempInputListTable);
log.debug("created temp table" + factTempTable);
} else if (serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
log.debug("creating temp table");
java.sql.Statement tempStmt = conn.createStatement();
factTempTable = getDbSchemaName() + FactRelatedQueryHandler.TEMP_FACT_PARAM_TABLE;
try {
tempStmt.executeUpdate("drop table if exists " + factTempTable);
} catch (SQLException sqlex) {;
}
String createTempInputListTable =
"create temporary table "
+ factTempTable
+ " ( set_index int, char_param1 varchar(500) )";
tempStmt.executeUpdate(createTempInputListTable);
log.debug("created temp table" + factTempTable);
}
// if the inputlist is enumeration, then upload the enumerated input
// to temp table.
// the uploaded enumerated input will be used in the fact join.
if (inputOptionListHandler.isEnumerationSet()) {
inputOptionListHandler.uploadEnumerationValueToTempTable(conn);
}
String insertSql = "";
int i = 0;
int sqlParamCount = 0;
ResultSet resultSet = null;
for (String panelSql : panelSqlList) {
insertSql =
" insert into "
+ factTempTable
+ "(char_param1) select distinct obs_encounter_num from ( "
+ panelSql
+ ") b";
log.debug("Executing SQL [ " + insertSql + "]");
sqlParamCount = sqlParamCountList.get(i++);
// conn.createStatement().executeUpdate(insertSql);
executeUpdateSql(insertSql, conn, sqlParamCount, inputOptionListHandler);
}
String finalSql =
"SELECT "
+ selectClause
+ " FROM "
+ getDbSchemaName()
+ "visit_dimension visit where encounter_num in (select distinct char_param1 from "
+ factTempTable
+ ") order by encounter_num";
log.debug("Executing SQL [" + finalSql + "]");
System.out.println("Final Sql " + finalSql);
query = conn.prepareStatement(finalSql);
resultSet = query.executeQuery();
while (resultSet.next()) {
EventType event = eventBuilder.buildEventSet(resultSet, this.metaDataParamList);
eventSet.getEvent().add(event);
}
} catch (SQLException sqlEx) {
log.error("", sqlEx);
throw new I2B2DAOException("sql exception", sqlEx);
} catch (IOException ioEx) {
log.error("", ioEx);
throw new I2B2DAOException("IO exception", ioEx);
} finally {
PdoTempTableUtil tempUtil = new PdoTempTableUtil();
tempUtil.clearTempTable(dataSourceLookup.getServerType(), conn, factTempTable);
if (inputOptionListHandler != null && inputOptionListHandler.isEnumerationSet()) {
try {
inputOptionListHandler.deleteTempTable(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
JDBCUtil.closeJdbcResource(null, query, conn);
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
}
return eventSet;
}
/**
* Function to return list of eventset for given encounter number list
*
* @param detailFlag
* @param blobFlag
* @param statusFlag
* @exception I2B2DAOException
*/
public EventSet getVisitsByEncounterNum(
List<String> encounterNumList, boolean detailFlag, boolean blobFlag, boolean statusFlag)
throws I2B2DAOException {
EventSet visitDimensionSet = new EventSet();
log.debug("visit list size " + encounterNumList.size());
Connection conn = null;
PreparedStatement query = null;
String tempTableName = "";
try {
conn = getDataSource().getConnection();
VisitFactRelated visitRelated =
new VisitFactRelated(buildOutputOptionType(detailFlag, blobFlag, statusFlag));
visitRelated.setMetaDataParamList(this.metaDataParamList);
String selectClause = visitRelated.getSelectClause();
String serverType = dataSourceLookup.getServerType();
if (serverType.equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
oracle.jdbc.driver.OracleConnection conn1 =
(oracle.jdbc.driver.OracleConnection)
((WrappedConnection) conn).getUnderlyingConnection();
String finalSql =
"SELECT "
+ selectClause
+ " FROM "
+ getDbSchemaName()
+ "visit_dimension visit WHERE visit.encounter_num IN (SELECT * FROM TABLE (cast (? as QT_PDO_QRY_STRING_ARRAY)))";
log.debug("Executing sql[" + finalSql + "]");
query = conn1.prepareStatement(finalSql);
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("QT_PDO_QRY_STRING_ARRAY", conn1);
oracle.sql.ARRAY paramArray =
new oracle.sql.ARRAY(desc, conn1, encounterNumList.toArray(new String[] {}));
query.setArray(1, paramArray);
} else if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
log.debug("creating temp table");
tempTableName =
this.getDbSchemaName() + SQLServerFactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE;
java.sql.Statement tempStmt = conn.createStatement();
try {
tempStmt.executeUpdate("drop table " + tempTableName);
} catch (SQLException sqlex) {;
}
uploadTempTable(tempStmt, tempTableName, encounterNumList);
String finalSql =
"SELECT "
+ selectClause
+ " FROM "
+ getDbSchemaName()
+ "visit_dimension visit WHERE visit.encounter_num IN (select distinct char_param1 FROM "
+ tempTableName
+ ") order by encounter_num";
log.debug("Executing [" + finalSql + "]");
query = conn.prepareStatement(finalSql);
} else if (serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
log.debug("creating temp table");
tempTableName = this.getDbSchemaName() + FactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE;
java.sql.Statement tempStmt = conn.createStatement();
try {
tempStmt.executeUpdate("drop table if exists " + tempTableName);
} catch (SQLException sqlex) {;
}
uploadTempTable(tempStmt, tempTableName, encounterNumList);
String finalSql =
"SELECT "
+ selectClause
+ " FROM "
+ getDbSchemaName()
+ "visit_dimension visit WHERE visit.encounter_num IN (select distinct char_param1 FROM "
+ tempTableName
+ ") order by encounter_num";
log.debug("Executing [" + finalSql + "]");
query = conn.prepareStatement(finalSql);
}
ResultSet resultSet = query.executeQuery();
I2B2PdoFactory.EventBuilder eventBuilder =
new I2B2PdoFactory().new EventBuilder(detailFlag, blobFlag, statusFlag);
while (resultSet.next()) {
EventType visitDimensionType =
eventBuilder.buildEventSet(resultSet, this.metaDataParamList);
visitDimensionSet.getEvent().add(visitDimensionType);
}
} catch (SQLException sqlEx) {
log.error("", sqlEx);
throw new I2B2DAOException("sql exception", sqlEx);
} catch (IOException ioex) {
log.error("", ioex);
throw new I2B2DAOException("io exception", ioex);
} finally {
if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
PdoTempTableUtil tempUtil = new PdoTempTableUtil();
tempUtil.deleteTempTableSqlServer(conn, tempTableName);
} else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
PdoTempTableUtil tempUtil = new PdoTempTableUtil();
tempUtil.deleteTempTablePostgres(conn, tempTableName);
}
try {
JDBCUtil.closeJdbcResource(null, query, conn);
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
}
return visitDimensionSet;
}
/**
* Get visit dimension from patientlist (InputOptionList.getPatientList())
*
* @param patientListType
* @param detailFlag
* @param blobFlag
* @param statusFlag
* @return PatientDataType.VisitDimensionSet
* @throws I2B2DAOException
*/
public EventSet getVisitDimensionSetFromPatientList(
PatientListType patientListType, boolean detailFlag, boolean blobFlag, boolean statusFlag)
throws I2B2DAOException {
VisitFactRelated visitRelated =
new VisitFactRelated(buildOutputOptionType(detailFlag, blobFlag, statusFlag));
visitRelated.setMetaDataParamList(this.metaDataParamList);
String selectClause = visitRelated.getSelectClause();
String mainSqlString =
" SELECT "
+ selectClause
+ " FROM "
+ getDbSchemaName()
+ "visit_dimension visit WHERE visit.patient_num IN ( ";
PatientListTypeHandler patientListTypeHandler =
new PatientListTypeHandler(dataSourceLookup, patientListType);
String inSqlClause = patientListTypeHandler.generateWhereClauseSql();
mainSqlString += inSqlClause;
mainSqlString += " ) order by visit.encounter_num, visit.patient_num \n";
log.debug("Executing sql[" + mainSqlString + "]");
EventSet visitDimensionSet = new EventSet();
Connection conn = null;
PreparedStatement preparedStmt = null;
try {
// execute fullsql
conn = getDataSource().getConnection();
if (patientListTypeHandler.isCollectionId()) {
preparedStmt = conn.prepareStatement(mainSqlString);
preparedStmt.setInt(1, Integer.parseInt(patientListTypeHandler.getCollectionId()));
} else if (patientListTypeHandler.isEnumerationSet()) {
String serverType = dataSourceLookup.getServerType();
patientListTypeHandler.uploadEnumerationValueToTempTable(conn);
preparedStmt = conn.prepareStatement(mainSqlString);
} else if (patientListTypeHandler.isEntireSet()) {
// log.debug("No need to pass parameter to sql");
preparedStmt = conn.prepareStatement(mainSqlString);
}
ResultSet resultSet = preparedStmt.executeQuery();
I2B2PdoFactory.EventBuilder eventBuilder =
new I2B2PdoFactory().new EventBuilder(detailFlag, blobFlag, statusFlag);
while (resultSet.next()) {
// VisitDimensionType visitDimensionType =
// getVisitDimensionType(resultSet);
EventType visitDimensionType =
eventBuilder.buildEventSet(resultSet, this.metaDataParamList);
visitDimensionSet.getEvent().add(visitDimensionType);
}
} catch (SQLException sqlEx) {
log.error("", sqlEx);
throw new I2B2DAOException("sql exception", sqlEx);
} catch (IOException ioEx) {
log.error("", ioEx);
throw new I2B2DAOException("io exception", ioEx);
} finally {
if (patientListTypeHandler.isEnumerationSet()) {
try {
patientListTypeHandler.deleteTempTable(conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
try {
JDBCUtil.closeJdbcResource(null, preparedStmt, conn);
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
}
return visitDimensionSet;
}