/*
* 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.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.I2B2DAOException;
import edu.harvard.i2b2.common.exception.I2B2Exception;
import edu.harvard.i2b2.common.util.db.JDBCUtil;
import edu.harvard.i2b2.ontology.datavo.pm.ProjectType;
import edu.harvard.i2b2.ontology.datavo.vdo.ConceptType;
import edu.harvard.i2b2.ontology.datavo.vdo.GetReturnType;
import edu.harvard.i2b2.ontology.datavo.vdo.XmlValueType;
import edu.harvard.i2b2.ontology.util.OntologyUtil;
import edu.harvard.i2b2.ontology.util.Roles;
public class GetCategoriesDao extends JdbcDaoSupport {
private static Log log = LogFactory.getLog(GetCategoriesDao.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 DEFAULT = " c_fullname, c_name ";
public List findRootCategories(final GetReturnType returnType, final ProjectType projectInfo) throws DataAccessException, I2B2DAOException{
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 (returnType.getType().equals("core")){
parameters = CORE;
}
/* else if (returnType.getType().equals("all")){
parameters = ALL;
}
*/
// First step is get metadata schema name from properties file.
String metadataSchema = "";
try {
metadataSchema = OntologyUtil.getInstance().getMetaDataSchemaName();
} catch (I2B2Exception e1) {
log.error(e1.getMessage());
}
// First step is to call PM to see what roles/project user belongs to.
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2DAOException e = new I2B2DAOException("No role found for user");
throw e;
}
String roles = "( '";
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
roles = roles + role;
if (it.hasNext()){
roles = roles + "', '";
}
else {
roles = roles + "' )";
}
}
log.debug(roles);
log.debug(projectInfo.getId().toLowerCase());
String tablesSql = "select distinct(c_table_cd), " + parameters + " from " + metadataSchema + "table_access where c_project = ? and c_role in " + roles;
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();
//TODO fix this for all/+blob
child.setKey("\\\\" + rs.getString("c_table_cd")+ rs.getString("c_fullname"));
child.setName(rs.getString("c_name"));
if(returnType.getType().equals("core")) {
child.setBasecode(rs.getString("c_basecode"));
child.setLevel(rs.getInt("c_hlevel"));
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"));
}
return child;
}
};
List queryResult = null;
try {
queryResult = jt.query(tablesSql, mapper, projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw e;
}
log.debug("result size = " + queryResult.size());
if (returnType.isBlob() == true && queryResult != null){
Iterator itr = queryResult.iterator();
while (itr.hasNext()){
ConceptType child = (ConceptType) itr.next();
String clobSql = "select c_metadataxml, c_comment from "+ metadataSchema + "table_access where c_name = ? and c_tooltip = ?";
ParameterizedRowMapper<ConceptType> map = new ParameterizedRowMapper<ConceptType>() {
public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
ConceptType concept = new ConceptType();
// ResultSetMetaData rsmd = rs.getMetaData();
// rsmd.get
if(rs.getClob("c_metadataxml") == null){
concept.setMetadataxml(null);
}else {
String c_xml = null;
try {
c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
} catch (IOException e1) {
// TODO Auto-generated catch block
log.error(e1.getMessage());
}
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);
} catch (IOException 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);
}
}
if(rs.getClob("c_comment") == null){
concept.setComment(null);
}else {
try {
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) {
child.setMetadataxml(((ConceptType)(clobResult.get(0))).getMetadataxml());
child.setComment(((ConceptType)(clobResult.get(0))).getComment());
}
else {
child.setMetadataxml(null);
child.setComment(null);
}
}
}
return queryResult;
}
}