/*
* Copyright (c) 2006-2007 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:
* Lori Phillips
*/
package edu.harvard.i2b2.ontology.dao;
import java.io.IOException;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.jdom.output.DOMOutputter;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import edu.harvard.i2b2.common.exception.I2B2Exception;
import edu.harvard.i2b2.common.util.db.JDBCUtil;
import edu.harvard.i2b2.common.util.jaxb.DTOFactory;
import edu.harvard.i2b2.ontology.datavo.pm.ProjectType;
import edu.harvard.i2b2.ontology.datavo.vdo.ConceptType;
import edu.harvard.i2b2.ontology.datavo.vdo.VocabRequestType;
import edu.harvard.i2b2.ontology.datavo.vdo.XmlValueType;
import edu.harvard.i2b2.ontology.util.OntologyUtil;
import edu.harvard.i2b2.ontology.util.Roles;
import edu.harvard.i2b2.ontology.util.StringUtil;
import edu.harvard.i2b2.ontology.ejb.ExpandedConceptType;;
public class GetCodeInfoDao extends JdbcDaoSupport {
private static Log log = LogFactory.getLog(GetCodeInfoDao.class);
final static String CORE = "c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_totalnum, c_basecode, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype, c_operator, c_dimcode, c_tooltip";
final static String ALL = CORE + ", update_date, download_date, import_date, sourcesystem_cd, valuetype_cd";
final static String DEFAULT = " c_name ";
final static String BLOB = ", c_metadataxml, c_comment ";
public List findCodeInfo(final VocabRequestType vocabType, final List categories, ProjectType projectInfo) throws DataAccessException, I2B2Exception{
DataSource ds = null;
try {
ds = OntologyUtil.getInstance().getDataSource("java:OntologyLocalDS");
} catch (I2B2Exception e2) {
log.error(e2.getMessage());
throw e2;
}
SimpleJdbcTemplate jt = new SimpleJdbcTemplate(ds);
// find return parameters
String parameters = DEFAULT;
if (vocabType.getType().equals("core")){
parameters = CORE;
}
else if (vocabType.getType().equals("all")){
parameters = ALL;
}
if(vocabType.isBlob() == true)
parameters = parameters + BLOB;
//extract table code
String tableCd = vocabType.getCategory();
// log.info(tableCd);
// table code to table name conversion
// Get metadata schema name from properties file.
String metadataSchema = "";
try {
metadataSchema = OntologyUtil.getInstance().getMetaDataSchemaName();
} catch (I2B2Exception e1) {
log.error(e1.getMessage());
throw e1;
}
List tableNames = null;
if(tableCd != null) {
// table code to table name conversion
String tableSql = "select distinct(c_table_name) from "+ metadataSchema + "table_access where c_table_cd = ? ";
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
tableNames = jt.query(tableSql, map, tableCd);
}
else { // tableCd is null, so query all tables user has access to
String whereClause = "where ";
Iterator it = categories.iterator();
while (it.hasNext()){
ConceptType entry = null;
if (whereClause.equals("where ")) {
entry = (ConceptType) it.next();
whereClause = whereClause + " c_table_cd = '" + StringUtil.getTableCd(entry.getKey()) + "' ";
}
else {
entry = (ConceptType) it.next();
whereClause = whereClause + " or " + " c_table_cd = '" + StringUtil.getTableCd(entry.getKey())+ "' ";
}
}
String tableSql = "select distinct(c_table_name) from "+ metadataSchema + "table_access " + whereClause;
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
try {
tableNames = jt.query(tableSql, map);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw e;
}
}
String hidden = "";
if(vocabType.isHiddens() == false)
hidden = " and c_visualattributes not like '_H%'";
String synonym = "";
if(vocabType.isSynonyms() == false)
synonym = " and c_synonym_cd = 'N'";
String codeInfoSql = null;
if(tableNames != null){
Iterator it = tableNames.iterator();
String table = (String)it.next();
String tableCdSql = ", (select distinct(c_table_cd) from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+ table+ "') as tableCd";
String basecode = " '" + vocabType.getMatchStr().getValue() + "' ";
codeInfoSql = "select " + parameters + tableCdSql + " from " + metadataSchema + table + " where upper(c_basecode) = " + basecode.toUpperCase() + hidden + synonym;;
while(it.hasNext()){
table = (String)it.next();
tableCdSql = ", (select distinct(c_table_cd) from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+ table + "') as tableCd";
codeInfoSql = codeInfoSql + " union select "+ parameters + tableCdSql + " from " + metadataSchema + table + " where upper(c_basecode) = " + basecode.toUpperCase()
+ hidden + synonym;
}
codeInfoSql = codeInfoSql + " order by c_name ";
}
else
return null;
log.debug(codeInfoSql);
final boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated( projectInfo );
ParameterizedRowMapper<ExpandedConceptType> mapper = new ParameterizedRowMapper<ExpandedConceptType>() {
public ExpandedConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
ExpandedConceptType entry = new ExpandedConceptType();
//TODO fix this for all/+blob
entry.setName(rs.getString("c_name"));
entry.setTableCd(rs.getString("tablecd"));
if(vocabType.getType().equals("core")) {
entry.setKey(rs.getString("c_fullname"));
entry.setBasecode(rs.getString("c_basecode"));
entry.setLevel(rs.getInt("c_hlevel"));
entry.setSynonymCd(rs.getString("c_synonym_cd"));
entry.setVisualattributes(rs.getString("c_visualattributes"));
Integer totalNum = rs.getInt("c_totalnum");
if (obfuscatedUserFlag == false) {
entry.setTotalnum(totalNum);
}
entry.setFacttablecolumn(rs.getString("c_facttablecolumn" ));
entry.setTablename(rs.getString("c_tablename"));
entry.setColumnname(rs.getString("c_columnname"));
entry.setColumndatatype(rs.getString("c_columndatatype"));
entry.setOperator(rs.getString("c_operator"));
entry.setDimcode(rs.getString("c_dimcode"));
entry.setTooltip(rs.getString("c_tooltip"));
}
if((vocabType.getType().equals("all"))){
DTOFactory factory = new DTOFactory();
// make sure date isnt null before converting to XMLGregorianCalendar
Date date = rs.getDate("update_date");
if (date == null)
entry.setUpdateDate(null);
else
entry.setUpdateDate(factory.getXMLGregorianCalendar(date.getTime()));
date = rs.getDate("download_date");
if (date == null)
entry.setDownloadDate(null);
else
entry.setDownloadDate(factory.getXMLGregorianCalendar(date.getTime()));
date = rs.getDate("import_date");
if (date == null)
entry.setImportDate(null);
else
entry.setImportDate(factory.getXMLGregorianCalendar(date.getTime()));
entry.setSourcesystemCd(rs.getString("sourcesystem_cd"));
entry.setValuetypeCd(rs.getString("valuetype_cd"));
}
return entry;
}
};
List queryResult = null;
try {
if(tableNames != null)
queryResult = jt.query(codeInfoSql, mapper);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw e;
}
log.debug("result size = " + queryResult.size());
if (queryResult != null) {
if(parameters != DEFAULT){
// fix the key so it equals "\\tableCd\fullname"
// in all cases but default
Iterator it = queryResult.iterator();
while (it.hasNext()){
ExpandedConceptType entry = (ExpandedConceptType) it.next();
entry.setKey("\\\\" + entry.getTableCd() + entry.getKey());
}
}
// Cant gather clobs when you perform unions....
// So you have to loop through all the results and gather clobs
if (vocabType.isBlob() == true){
Iterator itr = queryResult.iterator();
while (itr.hasNext()){
ConceptType child = (ConceptType) itr.next();
Iterator it = tableNames.iterator();
while(it.hasNext()){
String clobSql = "select c_metadataxml, c_comment from "+ metadataSchema+(String)it.next() + " where c_name = ? and " + synonym;
ParameterizedRowMapper<ConceptType> map = new ParameterizedRowMapper<ConceptType>() {
public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
ConceptType concept = new ConceptType();
try {
if(rs.getClob("c_metadataxml") == null){
concept.setMetadataxml(null);
}else {
String c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
if ((c_xml!=null)&&(c_xml.trim().length()>0)&&(!c_xml.equals("(null)")))
{
SAXBuilder parser = new SAXBuilder();
java.io.StringReader xmlStringReader = new java.io.StringReader(c_xml);
Element rootElement = null;
try {
org.jdom.Document metadataDoc = parser.build(xmlStringReader);
org.jdom.output.DOMOutputter out = new DOMOutputter();
Document doc = out.output(metadataDoc);
rootElement = doc.getDocumentElement();
} catch (JDOMException e) {
log.error(e.getMessage());
concept.setMetadataxml(null);
}
if (rootElement != null) {
XmlValueType xml = new XmlValueType();
xml.getAny().add(rootElement);
concept.setMetadataxml(xml);
}
}else {
concept.setMetadataxml(null);
}
}
} catch (IOException e) {
log.error(e.getMessage());
concept.setMetadataxml(null);
}
try {
if(rs.getClob("c_comment") == null){
concept.setComment(null);
}else {
concept.setComment(JDBCUtil.getClobString(rs.getClob("c_comment")));
}
} catch (IOException e) {
log.error(e.getMessage());
concept.setComment(null);
}
return concept;
}
};
List clobResult = null;
try {
clobResult = jt.query(clobSql, map, child.getName(), child.getTooltip());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw e;
}
if(clobResult != null) {
if( (((ConceptType)(clobResult.get(0))).getMetadataxml() != null) ||
(((ConceptType)(clobResult.get(0))).getComment() != null) ) {
child.setMetadataxml(((ConceptType)(clobResult.get(0))).getMetadataxml());
child.setComment(((ConceptType)(clobResult.get(0))).getComment());
break;
}
}
else {
child.setMetadataxml(null);
child.setComment(null);
}
}
}
}
}
return queryResult;
}
}