/*
* 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.GetChildrenType;
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;
public class GetChildrenDao extends JdbcDaoSupport {
private static Log log = LogFactory.getLog(GetChildrenDao.class);
final static String DEFAULT = " 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 = DEFAULT + ", update_date, download_date, import_date, sourcesystem_cd, valuetype_cd";
final static String CORE = DEFAULT;
final static String BLOB = ", c_metadataxml, c_comment ";
public List findChildrenByParent(final GetChildrenType childrenType, final List categories, final ProjectType projectInfo) 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 (childrenType.getType().equals("core")){
parameters = CORE;
}
else if (childrenType.getType().equals("all")){
parameters = ALL;
}
if(childrenType.isBlob() == true)
parameters = parameters + BLOB;
//extract table code
String tableCd = StringUtil.getTableCd(childrenType.getParent());
// 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());
}
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 tableName = jt.queryForObject(tableSql, map, tableCd);
String path = StringUtil.getPath(childrenType.getParent());
String searchPath = path + "%";
// Lookup to get chlevel + 1 --- dont allow synonyms so we only get one result back
String levelSql = "select c_hlevel from " + metadataSchema+tableName + " where c_fullname like ? and c_synonym_cd = 'N'";
int level = 0;
try {
level = jt.queryForInt(levelSql, path);
} catch (DataAccessException e1) {
// should only get 1 result back (path == c_fullname which should be unique)
log.error(e1.getMessage());
throw e1;
}
String hidden = "";
if(childrenType.isHiddens() == false)
hidden = " and c_visualattributes not like '_H%'";
String synonym = "";
if(childrenType.isSynonyms() == false)
synonym = " and c_synonym_cd = 'N'";
String sql = "select " + parameters +" from " + metadataSchema+tableName + " where c_fullname like ? and c_hlevel = ? ";
sql = sql + hidden + synonym + " order by c_name ";
// log.info(sql + path + level);
final boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated( projectInfo );
ParameterizedRowMapper<ConceptType> mapper = new ParameterizedRowMapper<ConceptType>() {
public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
ConceptType child = new ConceptType();
child.setName(rs.getString("c_name"));
child.setBasecode(rs.getString("c_basecode"));
child.setLevel(rs.getInt("c_hlevel"));
child.setKey(rs.getString("c_fullname"));
child.setSynonymCd(rs.getString("c_synonym_cd"));
child.setVisualattributes(rs.getString("c_visualattributes"));
Integer totalNum = rs.getInt("c_totalnum");
if (obfuscatedUserFlag == false ) {
child.setTotalnum(totalNum);
}
child.setFacttablecolumn(rs.getString("c_facttablecolumn" ));
child.setTablename(rs.getString("c_tablename"));
child.setColumnname(rs.getString("c_columnname"));
child.setColumndatatype(rs.getString("c_columndatatype"));
child.setOperator(rs.getString("c_operator"));
child.setDimcode(rs.getString("c_dimcode"));
child.setTooltip(rs.getString("c_tooltip"));
if(childrenType.isBlob() == true){
try {
if(rs.getClob("c_comment") == null)
child.setComment(null);
else
child.setComment(JDBCUtil.getClobString(rs.getClob("c_comment")));
} catch (IOException e) {
log.error(e.getMessage());
child.setComment(null);
}
if(rs.getClob("c_metadataxml") == null){
child.setMetadataxml(null);
}else {
String c_xml = null;
try {
c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
} catch (IOException e) {
log.error(e.getMessage());
child.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());
child.setMetadataxml(null);
} catch (IOException e1) {
log.error(e1.getMessage());
child.setMetadataxml(null);
}
if (rootElement != null) {
XmlValueType xml = new XmlValueType();
xml.getAny().add(rootElement);
child.setMetadataxml(xml);
}
}else {
child.setMetadataxml(null);
}
}
}
if((childrenType.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)
child.setUpdateDate(null);
else
child.setUpdateDate(factory.getXMLGregorianCalendar(date.getTime()));
date = rs.getDate("download_date");
if (date == null)
child.setDownloadDate(null);
else
child.setDownloadDate(factory.getXMLGregorianCalendar(date.getTime()));
date = rs.getDate("import_date");
if (date == null)
child.setImportDate(null);
else
child.setImportDate(factory.getXMLGregorianCalendar(date.getTime()));
child.setSourcesystemCd(rs.getString("sourcesystem_cd"));
child.setValuetypeCd(rs.getString("valuetype_cd"));
}
return child;
}
};
//log.info(sql + " " + path + " " + level);
List queryResult = null;
try {
queryResult = jt.query(sql, mapper, searchPath, (level + 1) );
} 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 it = queryResult.iterator();
while (it.hasNext()){
ConceptType child = (ConceptType) it.next();
child.setKey("\\\\" + tableCd + child.getKey());
}
}
return queryResult;
// tested statement with aqua data studio verified output from above against this.
// select c_fullname, c_name, c_synonym_cd, c_visualattributes from metadata.testrpdr
// where c_fullname like '\RPDR\Diagnoses\Circulatory system (390-459)\Arterial vascular disease (440-447)\(446) Polyarteritis nodosa and al%'
// and c_hlevel = 5 and c_visualattributes not like '_H%' and c_synonym_cd = 'N'
// verified both with and without hiddens and synonyms.
// clob test level = 4
// <parent>\\testrpdr\RPDR\HealthHistory\PHY\Health Maintenance\Mammogram\Mammogram - Deferred</parent>
}
}