/*
* 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;
public class GetNameInfoDao 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 findNameInfo(final VocabRequestType vocabType, List categories, ProjectType projectInfo, final String dbType)throws DataAccessException{
DataSource ds = null;
try {
ds = OntologyUtil.getInstance().getDataSource("java:OntologyLocalDS");
} catch (I2B2Exception e2) {
log.error(e2.getMessage());
}
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();
// 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());
}
// 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;
}
};
String table = jt.queryForObject(tableSql, map, tableCd);
String nameInfoSql = null;
String compareName = null;
if(vocabType.getMatchStr().getStrategy().equals("exact")) {
nameInfoSql = "select " + parameters + " from " + metadataSchema+table + " where upper(c_name) = ? ";
compareName = vocabType.getMatchStr().getValue().toUpperCase();
}
else if(vocabType.getMatchStr().getStrategy().equals("left")){
nameInfoSql = "select " + parameters + " from " + metadataSchema+table +" where upper(c_name) like ? ";
compareName = vocabType.getMatchStr().getValue().toUpperCase() + "%";
}
else if(vocabType.getMatchStr().getStrategy().equals("right")) {
nameInfoSql = "select " + parameters + " from " + metadataSchema+table +" where upper(c_name) like ? ";
compareName = "%" + vocabType.getMatchStr().getValue().toUpperCase();
}
else if(vocabType.getMatchStr().getStrategy().equals("contains")) {
nameInfoSql = "select " + parameters + " from " + metadataSchema+table +" where upper(c_name) like ? ";
compareName = "%" + vocabType.getMatchStr().getValue().toUpperCase() + "%";
}
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'";
nameInfoSql = nameInfoSql + hidden + synonym + " order by c_name ";
final boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated( projectInfo );
ParameterizedRowMapper<ConceptType> mapper = new ParameterizedRowMapper<ConceptType>() {
public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
ConceptType entry = new ConceptType();
entry.setName(rs.getString("c_name"));
if(!(vocabType.getType().equals("default"))) {
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.isBlob() == true) {
if(rs.getClob("c_comment") == null)
entry.setComment(null);
else {
try {
if (dbType.equals("POSTGRESQL"))
entry.setComment(rs.getString("c_comment"));
else
entry.setComment(JDBCUtil.getClobString(rs.getClob("c_comment")));
} catch (IOException e1) {
log.error(e1.getMessage());
entry.setComment(null);
}
}
if(rs.getClob("c_metadataxml") == null){
entry.setMetadataxml(null);
}else {
String c_xml = null;
try {
if (dbType.equals("POSTGRESQL"))
c_xml = rs.getString("c_comment");
else
c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
} catch (IOException e1) {
log.error(e1.getMessage());
entry.setMetadataxml(null);
}
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());
entry.setMetadataxml(null);
} catch (IOException e1) {
log.error(e1.getMessage());
entry.setMetadataxml(null);
}
if (rootElement != null) {
XmlValueType xml = new XmlValueType();
xml.getAny().add(rootElement);
entry.setMetadataxml(xml);
}
}else {
entry.setMetadataxml(null);
}
}
}
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 {
queryResult = jt.query(nameInfoSql, mapper, compareName);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw e;
}
log.debug("result size = " + queryResult.size());
// Fix the key so it equals "\\tableCd\fullname"
if(queryResult != null) {
Iterator itr = queryResult.iterator();
while (itr.hasNext()){
ConceptType entry = (ConceptType) itr.next();
entry.setKey("\\\\" + tableCd + entry.getKey());
}
}
return queryResult;
}
}