ConceptDao.java

/*
 * 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.ArrayList;
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.common.util.jaxb.DTOFactory;
import edu.harvard.i2b2.common.util.jaxb.JAXBUtilException;
import edu.harvard.i2b2.ontology.datavo.pm.ProjectType;
import edu.harvard.i2b2.ontology.datavo.vdo.ConceptType;
import edu.harvard.i2b2.ontology.datavo.vdo.GetCategoriesType;
import edu.harvard.i2b2.ontology.datavo.vdo.GetChildrenType;
import edu.harvard.i2b2.ontology.datavo.vdo.GetModifierChildrenType;
import edu.harvard.i2b2.ontology.datavo.vdo.GetModifierInfoType;
import edu.harvard.i2b2.ontology.datavo.vdo.GetReturnType;
import edu.harvard.i2b2.ontology.datavo.vdo.GetTermInfoType;
import edu.harvard.i2b2.ontology.datavo.vdo.VocabRequestType;
import edu.harvard.i2b2.ontology.datavo.vdo.ModifierType;
import edu.harvard.i2b2.ontology.datavo.vdo.GetModifiersType;
import edu.harvard.i2b2.ontology.datavo.vdo.XmlValueType;
import edu.harvard.i2b2.ontology.ejb.DBInfoType;
import edu.harvard.i2b2.ontology.ejb.NodeType;
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.ws.GetChildrenDataMessage;

public class ConceptDao extends JdbcDaoSupport {

	private static Log log = LogFactory.getLog(ConceptDao.class);
	final static String CAT_CORE = " c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_totalnum, c_basecode, c_facttablecolumn, c_dimtablename, c_columnname, c_columndatatype, c_operator, c_dimcode, c_tooltip, valuetype_cd ";
	final static String CAT_DEFAULT = " c_fullname, c_name ";
	final static String CAT_LIMITED =  " c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_totalnum, c_basecode, c_tooltip, valuetype_cd ";


	final static String MOD_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, m_applied_path ";
	final static String MOD_CORE = MOD_DEFAULT;
	final static String MOD_LIMITED = " c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_totalnum, c_basecode, c_tooltip, m_applied_path ";


	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, valuetype_cd ";
	final static String CORE = DEFAULT;
	final static String LIMITED = " c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_totalnum, c_basecode, c_tooltip, valuetype_cd ";

	final static String ALL = ", update_date, download_date, import_date, sourcesystem_cd ";
	final static String BLOB = ", c_metadataxml, c_comment ";

	final static String NAME_DEFAULT = " c_name ";

	private SimpleJdbcTemplate jt;

	private void setDataSource(String dataSource) {
		DataSource ds = null;
		try {
			ds = OntologyUtil.getInstance().getDataSource(dataSource);
		} catch (I2B2Exception e2) {
			log.error(e2.getMessage());;
		} 
		this.jt = new SimpleJdbcTemplate(ds);

	}

	private String getMetadataSchema() throws I2B2Exception{

		return OntologyUtil.getInstance().getMetaDataSchemaName();
	}

	public List findRootCategories(final GetCategoriesType returnType, final ProjectType projectInfo, final DBInfoType dbInfo) throws I2B2Exception, I2B2DAOException{

		// find return parameters
		String parameters = CAT_DEFAULT;		
		if (returnType.getType().equals("limited")){
			parameters = CAT_LIMITED;
		}
		else if(returnType.getType().equals("core")){
			parameters = CAT_CORE;
		}

		/*		else if (returnType.getType().equals("all")){
			parameters = ALL;
		}
		 */
		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());


		//		 First step is to call PM to see what roles user belongs to.

		if (projectInfo.getRole().size() == 0)
		{
			log.error("no role found for this user in project: " + projectInfo.getName());
			I2B2Exception e = new I2B2Exception("No role found for user");
			throw e;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}

		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
				child.setKey("\\\\" + rs.getString("c_table_cd")+ rs.getString("c_fullname")); 
				child.setName(rs.getString("c_name"));
				if(returnType.getType().equals("limited")) {
					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"));

					child.setTooltip(rs.getString("c_tooltip"));
					child.setValuetypeCd(rs.getString("valuetype_cd"));
				}
				else 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");
					boolean nullFlag = rs.wasNull();


					if (nullFlag) { 
						log.debug("null in totalnum flag ");
					} else { 
						log.debug("not null in totalnum flag ");
					}

					if (rs.getString("c_totalnum") == null) { 
						log.debug("null in totalnum flag using getString method");
					} else { 
						log.debug("not null in totalnum flag using getString method  [" + rs.getString("c_totalnum") + "]");
					}

					if (obfuscatedUserFlag == false && nullFlag == false) {
						child.setTotalnum(totalNum);
					} 


					child.setFacttablecolumn(rs.getString("c_facttablecolumn" ));
					child.setTablename(rs.getString("c_dimtablename")); 
					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"));
					child.setValuetypeCd(rs.getString("valuetype_cd"));
				}
				return child;
			}
		};


		List queryResult = null;



		if (!protectedAccess){
			String categoriesSql = "select c_table_cd, " + parameters + " from " +  metadataSchema +  "table_access where c_protected_access = ? ";

			String hidden = "";
			if(returnType.isHiddens() == false)
				hidden = " and c_visualattributes not like '_H%'";

			String synonym = "";
			if(returnType.isSynonyms() == false)
				synonym = " and c_synonym_cd = 'N'";

			categoriesSql = categoriesSql + hidden + synonym + "order by upper(c_name)";

			log.debug(categoriesSql);
			try {
				queryResult = jt.query(categoriesSql, mapper, "N");
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw new I2B2DAOException("Database error");
			}
		}
		else{
			String categoriesSql = "select c_table_cd, " + parameters + " from " +  metadataSchema +  "table_access ";

			String hidden = "";
			if(returnType.isHiddens() == false)
				hidden = " where c_visualattributes not like '_H%'";

			String synonym = "";
			if(returnType.isSynonyms() == false)
				synonym = " c_synonym_cd = 'N'";

			String whereClause = hidden;
			if((whereClause.length() > 2) && (synonym.length() > 2))
				whereClause = whereClause + " and " + synonym;

			else if (synonym.length() > 2)
				whereClause = " where " + synonym;

			categoriesSql = categoriesSql + whereClause + " order by upper(c_name)";			
			log.debug(categoriesSql);

			try {
				queryResult = jt.query(categoriesSql, mapper);
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw new I2B2DAOException("Database Error");
			}
		}
		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_table_cd = ?";
				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





						String c_xml = null;
						try {

							if (dbInfo.getDb_serverType().equals("POSTGRESQL"))
								c_xml = rs.getString("c_metadataxml");
							else  if (rs.getClob("c_metadataxml") != null)
								c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
						} catch (IOException e) {
							log.error(e.getMessage());
							concept.setMetadataxml(null);
						}

						if(c_xml == null){
							concept.setMetadataxml(null);
						}else {



							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);
							}
						}	

						try {
							if (dbInfo.getDb_serverType().equals("POSTGRESQL"))
							{
								concept.setComment(rs.getString("c_comment"));
							} else  if (rs.getClob("c_comment") != null)
							{
								concept.setComment(JDBCUtil.getClobString(rs.getClob("c_comment")));
							}
						}
						catch (Exception e)
						{
							concept.setComment(null);
						}

						return concept;
					}
				};
				List clobResult = null;
				try {
					clobResult = jt.query(clobSql, map, StringUtil.getTableCd(child.getKey()));
				} catch (DataAccessException e) {
					log.error(e.getMessage());
					throw new I2B2DAOException("Database Error");
				}
				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;
	}

	public List findChildrenByParent(final GetChildrenDataMessage childrenMsg, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception, JAXBUtilException{

		final GetChildrenType childrenType = childrenMsg.getChildrenType();

		// find return parameters
		String parameters = DEFAULT;		
		if (childrenType.getType().equals("limited")){
			parameters = LIMITED;
		}

		else if (childrenType.getType().equals("core")){
			parameters = CORE;
		}
		else if (childrenType.getType().equals("all")){
			parameters = CORE + ALL;
		}
		if(childrenType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		if (projectInfo.getRole().size() == 0)
		{
			log.error("no role found for this user in project: " + projectInfo.getName());
			I2B2Exception e = new I2B2Exception("No role found for user");
			throw e;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}

		ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name = (rs.getString("c_table_name"));
				return name;
			}
		};

		//extract table code
		String tableCd = StringUtil.getTableCd(childrenType.getParent());
		String tableName=null;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? ";
			//		log.info("getChildren " + tableSql);
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw new I2B2DAOException("Database Error");
			}
		}else {
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd);	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw new I2B2DAOException("Database Error");
			}
		}

		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 = ?  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 new I2B2DAOException("Database Error");
		}

		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 ? " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + " and c_hlevel = ? "; 
		sql = sql + hidden + synonym + " order by upper(c_name) ";

		//log.info(sql + " " + path + " " + level);
		boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);
		ParameterizedRowMapper<ConceptType> mapper = getMapper(new NodeType(childrenType),obfuscatedUserFlag, dbInfo.getDb_serverType());

		if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
			searchPath = StringUtil.escapeSQLSERVER(path);
			searchPath += "%";
			//			log.info("escaped searchPath is " + searchPath);
		}

		else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
			searchPath = StringUtil.escapeORACLE(path); 
			searchPath += "%";
		}
		else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
			searchPath = StringUtil.escapePOSTGRESQL(path); 
			searchPath += "%";
		}		

		List<ConceptType> queryResult = null;
		try {
			queryResult = jt.query(sql, mapper, searchPath, (level + 1) );
		} catch (Exception e) {
			log.error(e.getMessage());
			throw new I2B2DAOException("Database Error");
		}


		if(Float.parseFloat(				
				childrenMsg.getMessageHeaderType().getSendingApplication().getApplicationVersion()) > 1.5)
		{	
			if(queryResult.size() > 0){
				Iterator<ConceptType>  it2 = queryResult.iterator();
				while (it2.hasNext()){
					ConceptType concept = it2.next();
					// if a leaf has modifiers report it with visAttrib == F
					if(concept.getVisualattributes().startsWith("L")){
						String modPath = StringUtil.getPath(concept.getKey());
						// I have to do this the hard way because there are a dynamic number of applied paths to check
						//   prevent SQL injection
						if(modPath.contains("'")){
							modPath = modPath.replaceAll("'", "''");
						}
						String sqlCount = "select count(*) from " + metadataSchema+ tableName  + " where m_exclusion_cd is null and c_fullname in";
						int queryCount = 0;
						// build m_applied_path sub-query
						String m_applied_pathSql = "(m_applied_path = '" + modPath +"'";
						while (modPath.length() > 3) {
							if(modPath.endsWith("%")){
								modPath = modPath.substring(0, modPath.length()-2);
								modPath = modPath.substring(0, modPath.lastIndexOf("\\") + 1) + "%";			
							}
							else
								modPath = modPath + "%";
							m_applied_pathSql = m_applied_pathSql + " or m_applied_path = '" + modPath + "'" ;
						}
						sqlCount = sqlCount + "(select c_fullname from " + metadataSchema+ tableName  + " where c_hlevel = 1 and m_exclusion_cd is null and " + m_applied_pathSql + " )";

						if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE"))
							sqlCount = sqlCount + " MINUS ";
						else
							sqlCount = sqlCount + " EXCEPT ";

						sqlCount = sqlCount+ " (select c_fullname from " + metadataSchema+ tableName  + " where m_exclusion_cd is not null and " + m_applied_pathSql + " )))";


						try {
							queryCount = jt.queryForInt(sqlCount);
						} catch (DataAccessException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						//				log.debug("COUNT " + queryCount + " for " +sqlCount);


						if(queryCount > 0){
							concept.setVisualattributes(concept.getVisualattributes().replace('L', 'F'));
							log.debug("changed " + concept.getName() + " from leaf to folder: modCount > 0");
						}
					}
				}
			}

		}
		//		log.debug("Find Children By Parent " + sql);
		log.debug("get_children result size = " + queryResult.size());
		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> 
	}

	public List findByFullname(final GetTermInfoType termInfoType, ProjectType projectInfo, DBInfoType dbInfo) throws DataAccessException, I2B2Exception{

		// find return parameters
		String parameters = DEFAULT;		
		if (termInfoType.getType().equals("limited")){
			parameters = LIMITED;
		}

		else if (termInfoType.getType().equals("core")){
			parameters = CORE;
		}
		else if (termInfoType.getType().equals("all")){
			parameters = CORE + ALL;
		}
		if(termInfoType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		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;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}
		boolean ofuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);

		//tableCd to table name conversion
		ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name = (rs.getString("c_table_name"));
				return name;
			}
		};

		//extract table code
		String tableCd = StringUtil.getTableCd(termInfoType.getSelf());
		String tableName=null;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? ";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}else {
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd);	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}

		String path = StringUtil.getPath(termInfoType.getSelf());
		if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
			//path = path.replaceAll("\\[", "[[]");
			path = StringUtil.escapeSQLSERVER(path);
		}
		else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
			path = StringUtil.escapeORACLE(path);
		}
		else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
			path = StringUtil.escapePOSTGRESQL(path); 
		}		

		String searchPath = path;


		String hidden = "";
		if(termInfoType.isHiddens() == false)
			hidden = " and c_visualattributes not like '_H%'";

		String synonym = "";
		if(termInfoType.isSynonyms() == false)
			synonym = " and c_synonym_cd = 'N'";

		String sql = "select " + parameters +" from " + metadataSchema+tableName  + " where c_fullname like ? " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + ""; 
		sql = sql + hidden + synonym + " order by upper(c_name) ";

		//log.info(sql + " " + path + " " + level);

		ParameterizedRowMapper<ConceptType> mapper = getMapper(new NodeType(termInfoType), ofuscatedUserFlag, dbInfo.getDb_serverType());

		List queryResult = null;
		try {
			queryResult = jt.query(sql, mapper, searchPath );
		} catch (DataAccessException e) {
			log.error(e.getMessage());
			throw e;
		}

		log.debug("Term Info result size = " + queryResult.size());


		return queryResult;

	}



	public List findNameInfo(final VocabRequestType vocabType, ProjectType projectInfo, DBInfoType dbInfo) throws DataAccessException, I2B2Exception{

		// find return parameters
		String parameters = NAME_DEFAULT;		

		if (vocabType.getType().equals("limited")){
			parameters = LIMITED;
		}

		else if (vocabType.getType().equals("core")){
			parameters = CORE;
		}

		else if (vocabType.getType().equals("all")){
			parameters = CORE + ALL;
		}
		if(vocabType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		//	log.info(metadataSchema);

		if (projectInfo.getRole().size() == 0)
		{
			log.error("no role found for this user in project: " + projectInfo.getName());
			I2B2Exception e = new I2B2Exception("No role found for user");
			throw e;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}

		//tableCd to table name + fullname conversion
		ParameterizedRowMapper<ConceptType> map = new ParameterizedRowMapper<ConceptType>() {
			public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
				ConceptType category = new ConceptType();	 

				category.setTablename(rs.getString("c_table_name"));
				category.setKey(rs.getString("c_fullname"));
				return category;
			}
		};

		//extract table code
		String tableCd = vocabType.getCategory();
		List<ConceptType> categoryResult;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name), c_fullname from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? ";
			try {
				categoryResult = jt.query(tableSql, map, tableCd, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}else {
			String tableSql = "select distinct(c_table_name), c_fullname from " + metadataSchema + "table_access where c_table_cd = ?";
			try {
				categoryResult = jt.query(tableSql, map, tableCd);	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}

		String nameInfoSql = null;
		String compareName = null;

		String value = vocabType.getMatchStr().getValue();
		//		using JDBCtemplate so dont need to do apostrophe replace   
		//		if(value.contains("'")){
		//			value = value.replaceAll("'", "''");
		//		}

		String category = categoryResult.get(0).getKey();
		if(category.contains("'")){
			category = category.replaceAll("'", "''");
		}

		if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
			category = StringUtil.escapeSQLSERVER(category);
		}
		else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
			category = StringUtil.escapeORACLE(category);
		}
		else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
			category = StringUtil.escapePOSTGRESQL(category); 
		}		


		// dont do the sql injection replace; it breaks the service.
		if(vocabType.getMatchStr().getStrategy().equals("exact")) {
			nameInfoSql = "select " + parameters  + " from " + metadataSchema+categoryResult.get(0).getTablename() + " where upper(c_name) = ? and c_fullname like '" + category +	"%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    
			compareName = value.toUpperCase();  	
		}

		else if(vocabType.getMatchStr().getStrategy().equals("left")){
			nameInfoSql = "select " + parameters  + " from " + metadataSchema+categoryResult.get(0).getTablename() +" where upper(c_name) like ? " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + " and c_fullname like '" + category +"%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    
			if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				compareName = StringUtil.escapeSQLSERVER(vocabType.getMatchStr().getValue().toUpperCase());
				//compareName = compareName.replaceAll("\\[", "[[]");
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
				compareName = StringUtil.escapeORACLE(vocabType.getMatchStr().getValue().toUpperCase());
				//compareName = compareName.replaceAll("\\[", "[[]");
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
				compareName = StringUtil.escapePOSTGRESQL(vocabType.getMatchStr().getValue().toUpperCase());
				//compareName = compareName.replaceAll("\\[", "[[]");
			}
			compareName = compareName + "%";

		}

		else if(vocabType.getMatchStr().getStrategy().equals("right")) {
			nameInfoSql = "select " + parameters  + " from " + metadataSchema+categoryResult.get(0).getTablename() +" where upper(c_name) like ? " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + " and c_fullname like '" + category +"%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";     {ESCAPE '?'}";
			if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				compareName = StringUtil.escapeSQLSERVER(vocabType.getMatchStr().getValue().toUpperCase());
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
				compareName = StringUtil.escapeORACLE(vocabType.getMatchStr().getValue().toUpperCase());
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
				compareName = StringUtil.escapePOSTGRESQL(vocabType.getMatchStr().getValue().toUpperCase());
			}

			compareName =  "%" + compareName;
			//   	if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
			//		compareName = compareName.replaceAll("\\[", "[[]");
			//	}
		}

		else if(vocabType.getMatchStr().getStrategy().equals("contains")) {
			if(!(value.contains(" "))){
				nameInfoSql = "select " + parameters  + " from " + metadataSchema+categoryResult.get(0).getTablename() +" where upper(c_name) like ? " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + "and c_fullname like '" + category +"%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + "";
				if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
					compareName = StringUtil.escapeSQLSERVER(vocabType.getMatchStr().getValue().toUpperCase());
				}
				else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
					compareName = StringUtil.escapeORACLE(vocabType.getMatchStr().getValue().toUpperCase());
				}
				else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
					compareName = StringUtil.escapePOSTGRESQL(vocabType.getMatchStr().getValue().toUpperCase());
				}
				compareName =  "%" + compareName + "%";
				//if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				//		compareName = compareName.replaceAll("\\[", "[[]");
				//	}
			}else{
				nameInfoSql = "select " + parameters  + " from " + metadataSchema+categoryResult.get(0).getTablename();
				if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
					compareName = StringUtil.escapeSQLSERVER(vocabType.getMatchStr().getValue().toUpperCase());
				}
				else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
					compareName = StringUtil.escapeORACLE(vocabType.getMatchStr().getValue().toUpperCase());
				}
				else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
					compareName = StringUtil.escapePOSTGRESQL(vocabType.getMatchStr().getValue().toUpperCase());
				}

				//		if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				//			value = value.replaceAll("\\[", "[[]");
				//		}
				//	WAS
				//		nameInfoSql = nameInfoSql + parseMatchString(value)+ " and c_fullname like '" + category +"%'" + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + "";;
				nameInfoSql = nameInfoSql + parseMatchString(compareName, dbInfo)+ " and c_fullname like '" + category +"%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + " ";;

				compareName = null;
			}
		}


		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 upper(c_name) ";

		log.info(nameInfoSql + " " +compareName);
		boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);
		ParameterizedRowMapper<ConceptType> mapper = getMapper(new NodeType(vocabType),obfuscatedUserFlag, dbInfo.getDb_serverType());

		List queryResult = null;
		try {
			if(compareName != null)
				queryResult = jt.query(nameInfoSql, mapper, compareName);
			else
				queryResult = jt.query(nameInfoSql, mapper);
		} catch (DataAccessException e) {
			log.error(e.getMessage());
			throw e;
		}
		log.debug("search by NameInfo result size = " + queryResult.size());
		return queryResult;

	}

	public List findCodeInfo(final VocabRequestType vocabType, ProjectType projectInfo, DBInfoType dbInfo) throws DataAccessException, I2B2Exception{

		// find return parameters
		String parameters = NAME_DEFAULT;	

		if (vocabType.getType().equals("limited")){
			parameters = LIMITED;
		}

		else if (vocabType.getType().equals("core")){
			parameters = CORE;
		}

		else if (vocabType.getType().equals("all")){
			parameters = CORE + ALL;
		}
		if(vocabType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		String dbType = dbInfo.getDb_serverType();

		//		log.info(metadataSchema);

		if (projectInfo.getRole().size() == 0)
		{
			log.error("no role found for this user in project: " + projectInfo.getName());
			I2B2Exception e = new I2B2Exception("No role found for user");
			throw e;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}

		ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name =  rs.getString("c_table_name");
				return name;
			}
		};

		//no table code provided so check all tables user has access to
		List tableNames=null;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_protected_access = ? ";
			//			log.info(tableSql);
			try {
				tableNames = jt.query(tableSql, map, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}else {
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access ";
			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'";

		// I have to do this the hard way because there are a dynamic number of codes to pass in
		//   prevent SQL injection
		String value = vocabType.getMatchStr().getValue();
		if(value.contains("'")){
			value = vocabType.getMatchStr().getValue().replaceAll("'", "''");
		}
		String whereClause = null;

		String compareCode = value.toUpperCase();
		if(dbType.toUpperCase().equals("SQLSERVER")){
			compareCode = StringUtil.escapeSQLSERVER(compareCode);
		}
		else if(dbType.toUpperCase().equals("ORACLE")){
			compareCode = StringUtil.escapeORACLE(compareCode);
		}
		else if(dbType.toUpperCase().equals("POSTGRESQL")){
			compareCode = StringUtil.escapePOSTGRESQL(compareCode);
		}

		if(vocabType.getMatchStr().getStrategy().equals("exact")) {
			whereClause = " where upper(c_basecode) = '" + compareCode+ "'";
		}

		else if(vocabType.getMatchStr().getStrategy().equals("left")){
			whereClause = " where upper(c_basecode) like '" + compareCode + "%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    
		}

		else if(vocabType.getMatchStr().getStrategy().equals("right")) {
			compareCode = compareCode.replaceFirst(":", ":%");
			whereClause = " where upper(c_basecode) like '" +  compareCode + "' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    
		}

		else if(vocabType.getMatchStr().getStrategy().equals("contains")) {
			compareCode = compareCode.replaceFirst(":", ":%");
			whereClause = " where upper(c_basecode) like '" + compareCode + "%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    
		}

		//	log.debug(vocabType.getMatchStr().getStrategy() + whereClause);

		String codeInfoSql = null;
		if(tableNames != null){
			Iterator itTn = tableNames.iterator();
			String table = (String)itTn.next();
			// the following (distinct) doesnt work for a flattened hierarchy but is left for
			//  dbs other than sqlserver or oracle.   [c_table_cd is needed for key]
			String tableCdSql = ", (select distinct(c_table_cd) from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+  table+ "') as tableCd"; 
			if(dbType.toUpperCase().equals("SQLSERVER"))
				tableCdSql = ", (select top 1(c_table_cd) from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+  table+ "') as tableCd"; 
			else if (dbType.toUpperCase().equals("ORACLE"))
				tableCdSql = ", (select c_table_cd from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+  table+ "' and rownum <= 1) as tableCd"; 
			else if(dbType.toUpperCase().equals("POSTGRESQL"))
				tableCdSql = ", (select c_table_cd from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+  table+ "' limit 1) as tableCd"; 
			codeInfoSql = "select " + parameters + tableCdSql + " from " + metadataSchema + table + whereClause	+ hidden + synonym;;
			while(itTn.hasNext()){		
				table = (String)itTn.next();
				// the following (distinct) doesnt work for a flattened hierarchy but is left for
				//  dbs other than sqlserver or oracle.    [c_table_cd is needed for key]
				tableCdSql = ", (select distinct(c_table_cd) from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+  table+ "') as tableCd"; 
				if(dbType.toUpperCase().equals("SQLSERVER"))
					tableCdSql = ", (select top 1(c_table_cd) from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+  table+ "') as tableCd"; 
				else if (dbType.toUpperCase().equals("ORACLE"))
					tableCdSql = ", (select c_table_cd from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+  table+ "' and rownum <= 1) as tableCd"; 
				else if(dbType.toUpperCase().equals("POSTGRESQL"))
					tableCdSql = ", (select  c_table_cd from "+ metadataSchema + "TABLE_ACCESS where c_table_name = '"+  table+ "' limit 1) as tableCd"; 

				codeInfoSql = codeInfoSql +  " union all (select "+ parameters + tableCdSql + " from " + metadataSchema + table + whereClause
						+ hidden + synonym + ")";
			}
			codeInfoSql = codeInfoSql + " order by (c_name) ";
		}
		else
			return null;

		boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);
		ParameterizedRowMapper<ConceptType> mapper = getMapper(new NodeType(vocabType),obfuscatedUserFlag, dbInfo.getDb_serverType());

		List queryResult = null;
		try {
			queryResult = jt.query(codeInfoSql, mapper);
		} catch (DataAccessException e) {
			log.error(e.getMessage());
			throw e;
		}

		log.debug("searchByCodeInfo result size = " + queryResult.size());


		return queryResult;

	}


	private ParameterizedRowMapper<ConceptType> getMapper(final NodeType node, final boolean ofuscatedUserFlag, final String dbType){

		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"));
				if(!(node.getType().equals("default"))){
					child.setBasecode(rs.getString("c_basecode"));
					child.setLevel(rs.getInt("c_hlevel"));
					// cover get Code Info case where we dont know the vocabType.category apriori
					if(node.getNode() != null)	
						child.setKey("\\\\" + node.getNode() + rs.getString("c_fullname"));  
					else
						child.setKey("\\\\" + rs.getString("tableCd") + rs.getString("c_fullname")); 
					child.setSynonymCd(rs.getString("c_synonym_cd"));
					child.setVisualattributes(rs.getString("c_visualattributes"));
					Integer totalNumValue = rs.getInt("c_totalnum");
					boolean nullFlag = rs.wasNull();

					/*
					if (nullFlag) { 
						("null in totalnum flag ");
					} else { 
						("not null in totalnum flag ");
					}

					if (rs.getString("c_totalnum") == null) { 
						("null in totalnum flag using getString method");
					} else { 
						("not null in totalnum flag using getString method  [" + rs.getString("c_totalnum") + "]");
					}
					 */
					if ( ofuscatedUserFlag == false && nullFlag == false) { 
						child.setTotalnum(totalNumValue);
					}
					child.setTooltip(rs.getString("c_tooltip"));
					child.setValuetypeCd(rs.getString("valuetype_cd"));
					if(!(node.getType().equals("limited"))) {
						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")); 
					}
				}
				if(node.isBlob() == true){
					try {
						if (dbType.equals("POSTGRESQL"))
						{
							if(rs.getString("c_comment") == null)
								child.setComment(null);
							else
								child.setComment(rs.getString("c_comment"));
						}
						else {

							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);
					} 

					String c_xml = null;
					try {

						if (dbType.equals("POSTGRESQL"))
							c_xml = rs.getString("c_metadataxml");
						else if (rs.getClob("c_metadataxml") != null)
							c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
					} catch (IOException e) {
						log.error(e.getMessage());
						child.setMetadataxml(null);
					}

					if(c_xml == null){
						child.setMetadataxml(null);
					}else {
						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((node.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"));

				}
				return child;
			}
		};
		return mapper;
	}

	private String parseMatchString(String match, DBInfoType dbInfo){
		String whereClause = null;

		String[] terms = match.split(" ");
		ArrayList<String> goodWords = new ArrayList<String>();

		String word = getStopWords();
		for(int i=0; i< terms.length; i++){			
			if(word.contains(terms[i]))
				;
			else{
				goodWords.add(terms[i]);
			}
		}			

		if(goodWords.isEmpty())
			return null;

		Iterator it = goodWords.iterator();
		while(it.hasNext()){
			if(whereClause == null)	
				whereClause = " where upper(c_name) like " + "'%"  + ((String)it.next()).toUpperCase() + "%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    
			else
				whereClause = whereClause + " AND upper(c_name) like " + "'% " + ((String)it.next()).toUpperCase() + "%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    
		}	
		return whereClause;
	}

	private String getStopWords(){

		String stopWord = 	"a,able,about,across,after,all,almost,also,am,among,an,and,any,are,as,at,be,because,been,but,by,can,cannot,could,dear,did,do,does,either,else,ever,every,for,from,get,got,had,has,have,he,her,hers,him,his,how,however,i,if,in,into,is,it,its,just,least,let,like,likely,may,me,might,most,must,my,neither,no,nor,not,of,off,often,on,only,or,other,our,own,rather,said,say,says,she,should,since,so,some,than,that,the,their,them,then,there,these,they,this,tis,to,too,twas,us,wants,was,we,were,what,when,where,which,while,who,whom,why,will,with,would,yet,you,your";	
		//		String[] stopWords = stopWord.split("'");	
		return stopWord;
	}

	public List findModifiers(final GetModifiersType modifierType, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{

		// find return parameters
		String parameters = MOD_DEFAULT;	
		if (modifierType.getType().equals("limited")){
			parameters = MOD_LIMITED;
		}
		else if (modifierType.getType().equals("core")){
			parameters = MOD_CORE;
		}
		else if (modifierType.getType().equals("all")){
			parameters = MOD_CORE + ALL;
		}
		if(modifierType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		if (projectInfo.getRole().size() == 0)
		{
			log.error("no role found for this user in project: " + projectInfo.getName());
			I2B2Exception e = new I2B2Exception("No role found for user");
			throw e;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}


		ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name = (rs.getString("c_table_name"));
				return name;
			}
		};

		//extract table code
		String tableCd = StringUtil.getTableCd(modifierType.getSelf());
		String tableName=null;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? ";
			//		log.info("getChildren " + tableSql);
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw new I2B2DAOException("Database Error");
			}
		}else {
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd);	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw new I2B2DAOException("Database Error");
			}
		}




		//     Original sql before exclusions		
		//		String path = StringUtil.getLiteralPath(modifierType.getSelf());
		/*		String sql = "select " + parameters +" from " + metadataSchema+ tableName  + " where m_applied_path = '" + path + "' and c_hlevel = 1";
		while (path.length() > 2) {
			if(path.endsWith("%")){
				path = path.substring(0, path.length()-2);
				path = path.substring(0, path.lastIndexOf("\\") + 1) + "%";			
			}
			else
				path = path + "%";
			sql = sql + " union all (select " + parameters +" from " + metadataSchema+ tableName  + " where m_applied_path = '" + path + "' and c_hlevel = 1)";
		}
		 */
		String path = StringUtil.getLiteralPath(modifierType.getSelf());
		// I have to do this the hard way because there are a dynamic number of applied paths to check
		//   prevent SQL injection
		if(path.contains("'")){
			path = path.replaceAll("'", "''");
		}

		String hidden = "";
		if(modifierType.isHiddens() == false)
			hidden = " and c_visualattributes not like '_H%'";

		String synonym = "";
		if(modifierType.isSynonyms() == false)
			synonym = " and c_synonym_cd = 'N'";		


		//		String sql = "select " + parameters + " from "+ metadataSchema+ tableName + " where m_exclusion_cd is null and c_fullname in (";
		String inclusionSql = "select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = '" + path + "' and c_hlevel = 1 and m_exclusion_cd is null " + hidden + synonym;
		String modifier_select =  " and m_applied_path in ('" + path + "'";
		while (path.length() > 2) {
			if(path.endsWith("%")){
				path = path.substring(0, path.length()-2);
				path = path.substring(0, path.lastIndexOf("\\") + 1) + "%";			
			}
			else
				path = path + "%";
			inclusionSql = inclusionSql + " union all (select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = '" + path +  "' and c_hlevel = 1 and m_exclusion_cd is null " + hidden + synonym +")";
			modifier_select = modifier_select + ", '" + path + "'";
		}

		String sql = "select " + parameters + " from "+ metadataSchema+ tableName + " where m_exclusion_cd is null " + synonym + modifier_select +") and c_fullname in (";

		if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE"))
			sql = sql + inclusionSql + " MINUS (";
		else
			sql = sql + inclusionSql + " EXCEPT (";

		path = StringUtil.getLiteralPath(modifierType.getSelf());
		// I have to do this the hard way because there are a dynamic number of applied paths to check
		//   prevent SQL injection
		if(path.contains("'")){
			path = path.replaceAll("'", "''");
		}
		String exclusionSql = "select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = '" + path + "' and m_exclusion_cd is not null";
		while (path.length() > 2) {
			if(path.endsWith("%")){
				path = path.substring(0, path.length()-2);
				path = path.substring(0, path.lastIndexOf("\\") + 1) + "%";			
			}
			else
				path = path + "%";
			exclusionSql = exclusionSql + " union all (select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = '" + path + "' and m_exclusion_cd is not null)";
		}
		/*		// applied paths on exclusions dont end in %
		while (path.length() > 2) {
			path = path.substring(0, path.length()-2);		
			path = path.substring(0, path.lastIndexOf("\\") +1) ;		
			exclusionSql = exclusionSql + " union all (select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = '" + path + "' and m_exclusion_cd = 'X')";
		}
		 */
		sql = sql + exclusionSql + "))";

		sql = sql + " order by (c_name) ";

	//	("findMods: " + sql );
		final boolean ofuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);

		ParameterizedRowMapper<ModifierType> modMapper = getModMapper(new NodeType (modifierType), ofuscatedUserFlag, dbInfo.getDb_serverType());

		List queryResult = null;

		try {
			//			queryResult = jt.query(sql, modMapper, path );
			queryResult = jt.query(sql, modMapper);
		} catch (DataAccessException e) {
			log.error(e.getMessage());
			throw new I2B2DAOException("Database Error");
		}
		log.debug("findModifiers result size " + queryResult.size());
		return queryResult;
	}

	private ParameterizedRowMapper<ModifierType> getModMapper(final NodeType node, final boolean ofuscatedUserFlag, final String dbType){

		ParameterizedRowMapper<ModifierType> mapper = new ParameterizedRowMapper<ModifierType>() {
			public ModifierType mapRow(ResultSet rs, int rowNum) throws SQLException {
				ModifierType child = new ModifierType();	          
				if(node.getType().equals("limited")){
					child.setName(rs.getString("c_name"));
					child.setAppliedPath(rs.getString("m_applied_path"));
					child.setBasecode(rs.getString("c_basecode"));
					child.setKey("\\\\" + node.getNode() + rs.getString("c_fullname"));  
					child.setLevel(rs.getInt("c_hlevel"));
					child.setFullname(rs.getString("c_fullname"));  
					child.setVisualattributes(rs.getString("c_visualattributes"));
					child.setSynonymCd(rs.getString("c_synonym_cd"));
					child.setTooltip(rs.getString("c_tooltip"));
				}else{
					child.setName(rs.getString("c_name"));
					child.setAppliedPath(rs.getString("m_applied_path"));
					child.setBasecode(rs.getString("c_basecode"));
					child.setKey("\\\\" + node.getNode() + rs.getString("c_fullname"));  
					child.setLevel(rs.getInt("c_hlevel"));
					child.setFullname(rs.getString("c_fullname"));  
					child.setVisualattributes(rs.getString("c_visualattributes"));
					child.setSynonymCd(rs.getString("c_synonym_cd"));
					child.setFacttablecolumn(rs.getString("c_facttablecolumn" ));
					child.setTooltip(rs.getString("c_tooltip"));
					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")); 
				}

				if(node.isBlob() == true){
					try {
						if (dbType.equals("POSTGRESQL"))
						{
							if(rs.getString("c_comment") == null)
								child.setComment(null);
							else
								child.setComment(rs.getString("c_comment"));

						} else {
							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);
					} 


					String c_xml = null;
					try {

						if (dbType.equals("POSTGRESQL"))
							c_xml = rs.getString("c_metadataxml");
						else  if (rs.getClob("c_metadataxml") != null)
							c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
					} catch (IOException e) {
						log.error(e.getMessage());
						child.setMetadataxml(null);
					}

					if(c_xml == null){
						child.setMetadataxml(null);
					}else {

						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((node.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"));

				}
				return child;
			}
		};
		return mapper;
	}

	public List findChildrenByParent(final GetModifierChildrenType modifierChildrenType, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{

		//	("MOD: " + modifierChildrenType.getParent());
		//	log.debug("MOD: " + modifierChildrenType.getAppliedPath());

		// find return parameters
		String parameters = MOD_DEFAULT;		
		if (modifierChildrenType.getType().equals("limited")){
			parameters = MOD_LIMITED;
		}
		else if (modifierChildrenType.getType().equals("core")){
			parameters = MOD_CORE;
		}
		else if (modifierChildrenType.getType().equals("all")){
			parameters = MOD_CORE + ALL;
		}
		if(modifierChildrenType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		if (projectInfo.getRole().size() == 0)
		{
			log.error("no role found for this user in project: " + projectInfo.getName());
			I2B2Exception e = new I2B2Exception("No role found for user");
			throw e;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}

		ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name = (rs.getString("c_table_name"));
				return name;
			}
		};

		//extract table code
		String tableCd = StringUtil.getTableCd(modifierChildrenType.getParent());
		String tableName=null;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? ";
			//		log.info("getChildren " + tableSql);
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw new I2B2DAOException("Database Error");
			}
		}else {
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd);	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw new I2B2DAOException("Database Error");
			}
		}

		String path = StringUtil.getPath(modifierChildrenType.getParent());

		String searchPath = path;
		if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
			searchPath = StringUtil.escapeSQLSERVER(searchPath);
		}
		else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
			searchPath = StringUtil.escapeORACLE(searchPath);
		}
		else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
			searchPath = StringUtil.escapePOSTGRESQL(searchPath);
		}

		searchPath = searchPath + "%";

		String levelSql = "select c_hlevel from " + metadataSchema+tableName  + " where c_fullname = ?  and c_synonym_cd = 'N' and m_applied_path = ? and m_exclusion_cd is null";

		int level = 0;
		try {
			level = jt.queryForInt(levelSql, path, modifierChildrenType.getAppliedPath());
		} catch (DataAccessException e1) {
			// should only get 1 result back  (path == c_fullname which should be unique)
			log.error(e1.getMessage());
			throw new I2B2DAOException("Database Error");
		}

		String hidden = "";
		if(modifierChildrenType.isHiddens() == false)
			hidden = " and c_visualattributes not like '_H%'";

		String synonym = "";
		if(modifierChildrenType.isSynonyms() == false)
			synonym = " and c_synonym_cd = 'N'";		

		String appliedConcept = StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept());
		// I have to do this the hard way because there are a dynamic number of applied paths to check
		//   prevent SQL injection
		if(appliedConcept.contains("'")){
			appliedConcept = appliedConcept.replaceAll("'", "''");
		}

		String inclusionSql = "select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = ? and c_hlevel = " + (level+1) + " and m_exclusion_cd is null";
		String modifier_select =  " and m_applied_path in ('" + appliedConcept + "'";
		while (appliedConcept.length() > 2) {
			if(appliedConcept.endsWith("%")){
				appliedConcept = appliedConcept.substring(0, appliedConcept.length()-2);
				appliedConcept = appliedConcept.substring(0, appliedConcept.lastIndexOf("\\") + 1) + "%";			
			}
			else
				appliedConcept = appliedConcept + "%";
			inclusionSql = inclusionSql + " union all (select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = '" + appliedConcept + "' and c_hlevel = " + (level+1) + " and m_exclusion_cd is null)";
			modifier_select = modifier_select + ", '" + appliedConcept + "'";
		}

		String sql = "select " + parameters + " from "+ metadataSchema+ tableName + " where m_exclusion_cd is null and c_hlevel = ? and c_fullname like ? "  + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" ) + hidden + synonym
				+ modifier_select +") and c_fullname in (";


		if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE"))
			sql = sql + inclusionSql + " MINUS (";
		else
			sql = sql + inclusionSql + " EXCEPT (";

		String exclusionSql = "select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = ? and m_exclusion_cd is not null";
		String appliedExclConcept = StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept());
		// I have to do this the hard way because there are a dynamic number of applied paths to check
		//   prevent SQL injection
		if(appliedExclConcept.contains("'")){
			appliedExclConcept = appliedExclConcept.replaceAll("'", "''");
		}
		while (appliedExclConcept.length() > 2) {
			if(appliedExclConcept.endsWith("%")){
				appliedExclConcept = appliedExclConcept.substring(0, appliedExclConcept.length()-2);
				appliedExclConcept = appliedExclConcept.substring(0, appliedExclConcept.lastIndexOf("\\") + 1) + "%";			
			}
			else
				appliedExclConcept = appliedExclConcept + "%";
			exclusionSql = exclusionSql + " union all (select c_fullname from " + metadataSchema+ tableName  + " where m_applied_path = '" + appliedExclConcept + "' and m_exclusion_cd is not null)";
		}

		sql = sql + exclusionSql + "))";

		sql = sql + " order by (c_name) ";

	//	log.debug("findModChildren: " + sql + (level+1) + searchPath +  StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept()));


		final boolean ofuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);

		ParameterizedRowMapper<ModifierType> modMapper = getModMapper(new NodeType (modifierChildrenType), ofuscatedUserFlag, dbInfo.getDb_serverType());

		List queryResult = null;
		
		try {
			queryResult = jt.query(sql, modMapper, (level+1), searchPath,  StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept()),
					StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept()));
		} catch (DataAccessException e) {
			log.error(e.getMessage());
			throw new I2B2DAOException("Database Error");
		}


		log.debug("Get Mod children result size = " + queryResult.size());


		return queryResult;

	}



	public List findByFullname(final GetModifierInfoType modifierInfoType, ProjectType projectInfo, DBInfoType dbInfo) throws DataAccessException, I2B2Exception{

		// find return parameters
		String parameters = MOD_DEFAULT;	
		if (modifierInfoType.getType().equals("limited")){
			parameters = MOD_LIMITED;
		}
		else if (modifierInfoType.getType().equals("core")){
			parameters = MOD_CORE;
		}
		else if (modifierInfoType.getType().equals("all")){
			parameters = MOD_CORE + ALL;
		}
		if(modifierInfoType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		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;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}

		//tableCd to table name conversion
		ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name = (rs.getString("c_table_name"));
				return name;
			}
		};

		//extract table code
		String tableCd = StringUtil.getTableCd(modifierInfoType.getSelf());
		String tableName=null;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? ";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}else {
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd);	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}

		String path = StringUtil.getPath(modifierInfoType.getSelf());
		String searchPath = path;

		String hidden = "";
		if(modifierInfoType.isHiddens() == false)
			hidden = " and c_visualattributes not like '_H%'";

		String synonym = "";
		if(modifierInfoType.isSynonyms() == false)
			synonym = " and c_synonym_cd = 'N'";

		//	Removed dependency on m_applied_path 8/4/14 lcp
//		String sql = "select " + parameters +" from " + metadataSchema+tableName  + " where c_fullname = ? and m_applied_path = ?"; 
		String sql = "select " + parameters +" from " + metadataSchema+tableName  + " where c_fullname = ? "; 

		sql = sql + hidden + synonym + " order by upper(c_name) ";

		//log.info(sql + " " + path + " " + level);

		final boolean ofuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);

		ParameterizedRowMapper<ModifierType> modMapper = getModMapper(new NodeType (modifierInfoType), ofuscatedUserFlag, dbInfo.getDb_serverType());

		List queryResult = null;
		try {
//			queryResult = jt.query(sql, modMapper, searchPath, modifierInfoType.getAppliedPath() );
			queryResult = jt.query(sql, modMapper, searchPath);

		} catch (DataAccessException e) {
			log.error(e.getMessage());
			throw e;
		}

		log.debug("Get ModInfo result size = " + queryResult.size());


		return queryResult;

	}

	public List findModifierNameInfo(final VocabRequestType vocabType, ProjectType projectInfo, DBInfoType dbInfo) throws DataAccessException, I2B2Exception{

		// find return parameters
		String parameters = NAME_DEFAULT;		

		if (vocabType.getType().equals("limited")){
			parameters = MOD_LIMITED;
		}

		else if (vocabType.getType().equals("core")){
			parameters = MOD_CORE;
		}

		else if (vocabType.getType().equals("all")){
			parameters = MOD_CORE + ALL;
		}
		if(vocabType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		//	log.info(metadataSchema);

		if (projectInfo.getRole().size() == 0)
		{
			log.error("no role found for this user in project: " + projectInfo.getName());
			I2B2Exception e = new I2B2Exception("No role found for user");
			throw e;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}

		//tableCd to table name conversion
		ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name = (rs.getString("c_table_name"));
				return name;
			}
		};

		//extract table code
		String tableCd = StringUtil.getTableCd(vocabType.getSelf());
		String tableName=null;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? ";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}else {
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd);	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}

		//   prevent SQL injection and also catch case where the value contains an (')
		String value = vocabType.getMatchStr().getValue();
		if(value.contains("'")){
			value = vocabType.getMatchStr().getValue().replaceAll("'", "''");
		}
		String nameInfoSql = null;
		String compareName = null;
		String modifierPath = StringUtil.getLiteralPath(vocabType.getSelf());
		if(modifierPath.contains("'")){
			modifierPath = modifierPath.replaceAll("'", "''");
		}

		if(vocabType.getMatchStr().getStrategy().equals("exact")) {
			compareName = value.toUpperCase();
			nameInfoSql = "select c_fullname from " + metadataSchema + tableName + " where upper(c_name) = '" + compareName + "'" ;//and m_applied_path = '" + path + "'";	  
		}

		else if(vocabType.getMatchStr().getStrategy().equals("left")){ 
			compareName = value.toUpperCase();
			if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				//compareName = compareName.replaceAll("\\[", "[[]");
				compareName = StringUtil.escapeSQLSERVER(compareName);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
				compareName = StringUtil.escapeORACLE(compareName);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
				compareName = StringUtil.escapePOSTGRESQL(compareName);
			}
			compareName +=  "%";
			nameInfoSql = "select c_fullname from " + metadataSchema + tableName +" where upper(c_name) like '" + compareName + "' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";      and m_applied_path = '" + path + "'";
		}

		else if(vocabType.getMatchStr().getStrategy().equals("right")) {
			compareName = value.toUpperCase();
			if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				//compareName = compareName.replaceAll("\\[", "[[]");
				compareName = StringUtil.escapeSQLSERVER(compareName);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
				compareName = StringUtil.escapeORACLE(compareName);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
				compareName = StringUtil.escapePOSTGRESQL(compareName);
			}
			compareName =  "%" + compareName;
			nameInfoSql = "select c_fullname from " + metadataSchema + tableName +" where upper(c_name) like '" + compareName + "' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";     {ESCAPE '?'}";//and m_applied_path = '" + path + "'";	
		}

		else if(vocabType.getMatchStr().getStrategy().equals("contains")) {
			if(!(value.contains(" "))){
				//	compareName =  "%" + value.toUpperCase() + "%";
				compareName = value.toUpperCase();
				if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
					//compareName = compareName.replaceAll("\\[", "[[]");
					compareName = StringUtil.escapeSQLSERVER(compareName);
				}
				else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
					compareName = StringUtil.escapeORACLE(compareName);
				}
				else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
					compareName = StringUtil.escapePOSTGRESQL(compareName);
				}
				compareName =  "%" + compareName + "%";
				nameInfoSql = "select c_fullname from " + metadataSchema + tableName +" where upper(c_name) like '" + compareName + "' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    {ESCAPE '?'}";  //and m_applied_path = '" + path + "'";

			}else{
				nameInfoSql = "select c_fullname from " + metadataSchema + tableName ;
				//	if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				//		value = value.replaceAll("\\[", "[[]");
				//	}
				if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
					value = StringUtil.escapeSQLSERVER(value);
				}
				else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
					value = StringUtil.escapeORACLE(value);
				}
				else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
					value = StringUtil.escapePOSTGRESQL(value);
				}
				nameInfoSql = nameInfoSql + parseMatchString(value, dbInfo);// + "and m_applied_path = '" + path + "'";
				compareName = null;
			}
		}

		String appliedPath   = " and m_applied_path = '" + modifierPath + "' ";
		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 inclusionSql = nameInfoSql + appliedPath + hidden + synonym + " and m_exclusion_cd is null ";
		String modifierSelect = "'" +	modifierPath + "'";
		while (modifierPath.length() > 3) {
			if(modifierPath.endsWith("%")){
				modifierPath = modifierPath.substring(0, modifierPath.length()-2);
				modifierPath = modifierPath.substring(0, modifierPath.lastIndexOf("\\") + 1) + "%";			
			}
			else
				modifierPath = modifierPath + "%";
			modifierSelect = modifierSelect + ", '" + modifierPath + "'";
			appliedPath = " and m_applied_path = '" + modifierPath + "' ";
			inclusionSql = inclusionSql + " union all " + nameInfoSql + appliedPath + hidden + synonym + " and m_exclusion_cd is null ";
		}

		String exclusionSql = nameInfoSql + appliedPath + hidden + synonym + " and m_exclusion_cd is not null";
		modifierPath = StringUtil.getLiteralPath(vocabType.getSelf());
		if(modifierPath.contains("'")){
			modifierPath = modifierPath.replaceAll("'", "''");
		}
		while (modifierPath.length() > 3) {
			if(modifierPath.endsWith("%")){
				modifierPath = modifierPath.substring(0, modifierPath.length()-2);
				modifierPath = modifierPath.substring(0, modifierPath.lastIndexOf("\\") + 1) + "%";			
			}
			else
				modifierPath = modifierPath + "%";
			appliedPath = " and m_applied_path = '" + modifierPath + "' ";
			exclusionSql = exclusionSql + " union all " + nameInfoSql + appliedPath + hidden + synonym + " and m_exclusion_cd is not null ";
		}

		String exceptSql =  " EXCEPT (";
		if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE"))
			exceptSql =	" MINUS (";


		String 	modNameInfoSql = " select " + parameters + " from "  + metadataSchema + tableName +  " where m_exclusion_cd is null and m_applied_path in ("
				+ modifierSelect +") and c_fullname in ("	+ inclusionSql	+ exceptSql + exclusionSql 	+ ")) order by (c_name) ";

//		log.debug("MODnameInfo: " + modNameInfoSql + " " +compareName);
		boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);

		ParameterizedRowMapper<ModifierType> modMapper = getModMapper(new NodeType(vocabType), obfuscatedUserFlag, dbInfo.getDb_serverType());

		List queryResult = null;
		try {
			if(compareName != null)
				queryResult = jt.query(modNameInfoSql, modMapper);
			else
				queryResult = jt.query(modNameInfoSql, modMapper);
		} catch (DataAccessException e) {
			log.error(e.getMessage());
			throw e;
		}

		log.debug("Mod search by name result size = " + queryResult.size());


		return queryResult;

	}

	public List findModifierCodeInfo(final VocabRequestType vocabType, ProjectType projectInfo, DBInfoType dbInfo) throws DataAccessException, I2B2Exception{

		// find return parameters
		String parameters = NAME_DEFAULT;	

		if (vocabType.getType().equals("limited")){
			parameters = MOD_LIMITED;
		}

		else if (vocabType.getType().equals("core")){
			parameters = MOD_CORE;
		}

		else if (vocabType.getType().equals("all")){
			parameters = MOD_CORE + ALL;
		}
		if(vocabType.isBlob() == true)
			parameters = parameters + BLOB;

		String metadataSchema = dbInfo.getDb_fullSchema();
		setDataSource(dbInfo.getDb_dataSource());

		String dbType = dbInfo.getDb_serverType();

		//		log.info(metadataSchema);

		if (projectInfo.getRole().size() == 0)
		{
			log.error("no role found for this user in project: " + projectInfo.getName());
			I2B2Exception e = new I2B2Exception("No role found for user");
			throw e;
		}

		Boolean protectedAccess = false;
		Iterator it = projectInfo.getRole().iterator();
		while (it.hasNext()){
			String role = (String) it.next();
			if(role.toUpperCase().equals("DATA_PROT")) {
				protectedAccess = true;
				break;
			}
		}

		//tableCd to table name conversion
		ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String name = (rs.getString("c_table_name"));
				return name;
			}
		};

		//extract table code
		String tableCd = StringUtil.getTableCd(vocabType.getSelf());
		String tableName=null;
		if (!protectedAccess){
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? ";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd, "N");	    
			} catch (DataAccessException e) {
				log.error(e.getMessage());
				throw e;
			}
		}else {
			String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?";
			try {
				tableName = jt.queryForObject(tableSql, map, tableCd);	    
			} 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'";

		// I have to do this the hard way because there are a dynamic number of codes to pass in
		//   prevent SQL injection
		String value = vocabType.getMatchStr().getValue();
		if(value.contains("'")){
			value = vocabType.getMatchStr().getValue().replaceAll("'", "''");
		}
		String whereClause = null;

		if(vocabType.getMatchStr().getStrategy().equals("exact")) {
			whereClause = " where upper(c_basecode) = '" + value.toUpperCase()+ "'";
		}

		else if(vocabType.getMatchStr().getStrategy().equals("left")){
			if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				value = StringUtil.escapeSQLSERVER(value);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
				value = StringUtil.escapeORACLE(value);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
				value = StringUtil.escapePOSTGRESQL(value);
			}
			whereClause = " where upper(c_basecode) like '" + value.toUpperCase() + "%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    {ESCAPE '?'}";
		}

		else if(vocabType.getMatchStr().getStrategy().equals("right")) {
			if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				value = StringUtil.escapeSQLSERVER(value);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
				value = StringUtil.escapeORACLE(value);
			}
			value = value.replaceFirst(":", ":%");
			whereClause = " where upper(c_basecode) like '%" +  value.toUpperCase() + "' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    {ESCAPE '?'}";

		}

		else if(vocabType.getMatchStr().getStrategy().equals("contains")) {
			if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
				value = StringUtil.escapeSQLSERVER(value);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")){
				value = StringUtil.escapeORACLE(value);
			}
			else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")){
				value = StringUtil.escapePOSTGRESQL(value);
			}
			value = value.replaceFirst(":", ":%");
			whereClause = " where upper(c_basecode) like '%" + value.toUpperCase() + "%' " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "" )	;  //{ESCAPE '?'}";    {ESCAPE '?'}";
		}

		String codeInfoSql = "select c_fullname from " + metadataSchema + tableName + whereClause;


		String modifierPath = StringUtil.getLiteralPath(vocabType.getSelf());
		if(modifierPath.contains("'")){
			modifierPath = modifierPath.replaceAll("'", "''");
		}
		String appliedPath   = " and m_applied_path = '" + modifierPath + "' ";
		String inclusionSql = codeInfoSql + appliedPath + hidden + synonym + " and m_exclusion_cd is null ";
		String modifierSelect = "'" +	modifierPath + "'";
		while (modifierPath.length() > 3) {
			if(modifierPath.endsWith("%")){
				modifierPath = modifierPath.substring(0, modifierPath.length()-2);
				modifierPath = modifierPath.substring(0, modifierPath.lastIndexOf("\\") + 1) + "%";			
			}
			else
				modifierPath = modifierPath + "%";
			modifierSelect = modifierSelect + ", '" + modifierPath + "'";
			appliedPath = " and m_applied_path = '" + modifierPath + "' ";
			inclusionSql = inclusionSql + " union all " + codeInfoSql + appliedPath + hidden + synonym + " and m_exclusion_cd is null ";
		}

		String exclusionSql = codeInfoSql + appliedPath + hidden + synonym + " and m_exclusion_cd is not null";
		modifierPath = StringUtil.getLiteralPath(vocabType.getSelf());
		if(modifierPath.contains("'")){
			modifierPath = modifierPath.replaceAll("'", "''");
		}
		while (modifierPath.length() > 3) {
			if(modifierPath.endsWith("%")){
				modifierPath = modifierPath.substring(0, modifierPath.length()-2);
				modifierPath = modifierPath.substring(0, modifierPath.lastIndexOf("\\") + 1) + "%";			
			}
			else
				modifierPath = modifierPath + "%";
			appliedPath = " and m_applied_path = '" + modifierPath + "' ";
			exclusionSql = exclusionSql + " union all " + codeInfoSql + appliedPath + hidden + synonym + " and m_exclusion_cd is not null ";
		}

		String exceptSql =  " EXCEPT (";
		if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE"))
			exceptSql =	" MINUS (";


		String 	modCodeInfoSql = " select " + parameters + " from "  + metadataSchema + tableName +  " where m_exclusion_cd is null and m_applied_path in ("
				+ modifierSelect +") and c_fullname in (" 	+ inclusionSql	+ exceptSql + exclusionSql 	+ ")) order by (c_name) ";


//		log.debug("MODCodeInfo " + modCodeInfoSql);
		boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);
		ParameterizedRowMapper<ModifierType> modMapper = getModMapper(new NodeType(vocabType),obfuscatedUserFlag, dbInfo.getDb_serverType());

		List queryResult = null;
		try {
			queryResult = jt.query(modCodeInfoSql, modMapper);
		} catch (DataAccessException e) {
			log.error(e.getMessage());
			throw e;
		}


		log.debug("Mod search by code result size = " + queryResult.size());

		return queryResult;

	} 
}