/*
* 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.im.dao;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
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.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.Element;
import edu.harvard.i2b2.common.exception.I2B2DAOException;
import edu.harvard.i2b2.common.exception.I2B2Exception;
import edu.harvard.i2b2.common.util.jaxb.DTOFactory;
import edu.harvard.i2b2.common.util.jaxb.JAXBUnWrapHelper;
import edu.harvard.i2b2.common.util.jaxb.JAXBUtilException;
import edu.harvard.i2b2.common.util.xml.XMLUtil;
import edu.harvard.i2b2.im.datavo.i2b2message.BodyType;
import edu.harvard.i2b2.im.datavo.pdo.PatientDataType;
import edu.harvard.i2b2.im.datavo.pdo.PidSet;
import edu.harvard.i2b2.im.datavo.pdo.PidType;
import edu.harvard.i2b2.im.datavo.pdo.PidType.PatientId;
import edu.harvard.i2b2.im.datavo.pdo.PidType.PatientMapId;
import edu.harvard.i2b2.im.datavo.pdo.query.FilterListType;
import edu.harvard.i2b2.im.datavo.pdo.query.GetPDOFromInputListRequestType;
import edu.harvard.i2b2.im.datavo.pdo.query.OutputOptionListType;
import edu.harvard.i2b2.im.datavo.pdo.query.OutputOptionNameType;
import edu.harvard.i2b2.im.datavo.pdo.query.OutputOptionSelectType;
import edu.harvard.i2b2.im.datavo.pdo.query.OutputOptionType;
import edu.harvard.i2b2.im.datavo.pdo.query.PageByPatientType;
import edu.harvard.i2b2.im.datavo.pdo.query.PageRangeType;
import edu.harvard.i2b2.im.datavo.pdo.query.PageType;
import edu.harvard.i2b2.im.datavo.pdo.query.PatientDataResponseType;
import edu.harvard.i2b2.im.datavo.pdo.query.PdoQryHeaderType;
import edu.harvard.i2b2.im.datavo.pdo.query.PdoRequestTypeType;
import edu.harvard.i2b2.im.datavo.pdo.query.PidListType;
import edu.harvard.i2b2.im.datavo.pdo.query.PidListType.Pid;
import edu.harvard.i2b2.im.datavo.pdo.query.RequestType;
import edu.harvard.i2b2.im.datavo.pm.ProjectType;
import edu.harvard.i2b2.im.datavo.wdo.AuditType;
import edu.harvard.i2b2.im.datavo.wdo.AuditsType;
import edu.harvard.i2b2.im.datavo.wdo.GetAuditType;
import edu.harvard.i2b2.im.delegate.crc.CallCRCUtil;
import edu.harvard.i2b2.im.util.HighEncryption;
import edu.harvard.i2b2.im.ws.PDORequestMessage;
import edu.harvard.i2b2.im.util.IMUtil;
import edu.harvard.i2b2.im.ejb.DBInfoType;
import edu.harvard.i2b2.im.util.StringUtil;
public class PdoDao extends JdbcDaoSupport {
private static Log log = LogFactory.getLog(PdoDao.class);
private GetPDOFromInputListRequestType getPDOFromInputListRequestType = null;
private DTOFactory dtoFactory = new DTOFactory();
private SimpleJdbcTemplate jt;
private void setDataSource(String dataSource) {
DataSource ds = null;
try {
ds = IMUtil.getInstance().getDataSource(dataSource);
} catch (I2B2Exception e2) {
log.error(e2.getMessage());;
}
this.jt = new SimpleJdbcTemplate(ds);
}
private String getMetadataSchema() throws I2B2Exception{
return IMUtil.getInstance().getIMDataSchemaName();
}
public String getPDO(PdoQryHeaderType requestType,
ProjectType projectInfo, PDORequestMessage getFoldersMsg) throws Exception {
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
if (!protectedAccess)
throw new I2B2DAOException("Access Denied");
//Update so sending is from IM cell
getFoldersMsg.getMessageHeaderType().getSendingApplication().setApplicationName("Identity Management Cell");
getFoldersMsg.getMessageHeaderType().getSendingApplication().setApplicationVersion("1.7");
getPDOFromInputListRequestType = getFoldersMsg.getgetPDOFromInputListRequestType();
//Clear the filter list if set
FilterListType filterList = new FilterListType();
getPDOFromInputListRequestType.setFilterList(filterList );
//Set output options to only be pidset
OutputOptionListType outputOptions = new OutputOptionListType();
outputOptions.setNames(OutputOptionNameType.ASATTRIBUTES);
OutputOptionType optionType = new OutputOptionType();
optionType.setOnlykeys(false);
optionType.setSelect(OutputOptionSelectType.USING_INPUT_LIST);
outputOptions.setPidSet(optionType);
getPDOFromInputListRequestType.setOutputOption(outputOptions);
//Go through the patie
// String patientList = null;
if (getPDOFromInputListRequestType.getInputList() != null & getPDOFromInputListRequestType.getInputList().getPidList() != null)
{
List<Pid> pids = getPDOFromInputListRequestType.getInputList().getPidList().getPid();
// else if (getPDOFromInputListRequestType.getInputList() != null & getPDOFromInputListRequestType.getInputList().getPatientList() != null)
// patientList = getPDOFromInputListRequestType.getInputList().getPatientList().getPatientSetCollId();
HighEncryption highEnc = new HighEncryption(IMKey.getKey(projectInfo));
if (highEnc == null)
throw new I2B2Exception ("High Encrpytion not found.");
for (Pid pid : pids)
{
if ((!pid.getSource().endsWith("_E")) && (!pid.getSource().equals("HIVE")))
{
pid.setValue( highEnc.mrn_encrypt(pid.getValue(), true, pid.getSource()));
pid.setSource(pid.getSource() + "_E");
}
// pid.setValue( pid.getValue());
// pid.setSource(pid.getSource());
}
}
// Call the CRC with the new PDO
return CallCRCUtil.callCRCPDORequest(getFoldersMsg);
}
public int addAudit( String projectInfo, PidSet pids, String userId, String comments, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
int numRowsAdded = -1;
try {
String xml = null;
if (comments != null) {
String addSql = "insert into " + metadataSchema +
"IM_AUDIT (lcl_site, lcl_id, user_id, project_id, comments) values (?,?,?,?,?)";
for (PidType pidType : pids.getPid()) {
numRowsAdded += jt.update(addSql,
pidType.getPatientId().getSource(), pidType.getPatientId().getValue(),
userId, projectInfo, comments);
for (PatientMapId pidPatient : pidType.getPatientMapId())
{
numRowsAdded += jt.update(addSql,
pidPatient.getSource(), pidPatient.getValue(),
userId, projectInfo, comments);
}
}
}
else {
String addSql = "insert into " + metadataSchema +
"IM_AUDIT (lcl_site, lcl_id, user_id, project_id) values (?,?,?,?)";
for (PidType pidType : pids.getPid()) {
// numRowsAdded += jt.update(addSql,
// pidType.getPatientId().getSource(), pidType.getPatientId().getValue(),
// userId, projectInfo);
for (PatientMapId pidPatient : pidType.getPatientMapId())
{
numRowsAdded += jt.update(addSql,
pidPatient.getSource(), pidPatient.getValue(),
userId, projectInfo);
}
}
}
} catch (DataAccessException e) {
log.error("Dao addChild failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows added: " + numRowsAdded);
return numRowsAdded;
}
public PidSet findPidsByProject(final PidSet returnType, final String userId, final ProjectType projectInfo, final DBInfoType dbInfo) throws Exception{
PidSet pidSet = new PidSet();
String tempTable = "im_temp_site";
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.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
if (!protectedAccess)
throw new I2B2DAOException("Access Denied");
ParameterizedRowMapper<PatientMapId> map = new ParameterizedRowMapper<PatientMapId>() {
public PatientMapId mapRow(ResultSet rs, int rowNum) throws SQLException {
PatientMapId pid = new PatientMapId();
pid.setValue(rs.getString("lcl_id"));
pid.setSource(rs.getString("lcl_site"));
return pid;
}
};
HighEncryption highEnc = new HighEncryption(IMKey.getKey(projectInfo));
if (highEnc == null)
throw new I2B2Exception ("High Encrpytion not found.");
//If sqlserver add # in front of it
if (dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER"))
{
tempTable = "#"+tempTable;
tempTable = metadataSchema + tempTable;
String sql = "CREATE TABLE " + tempTable +" ( "+
"LCL_SITE VARCHAR(50) NULL, "+
"LCL_ID VARCHAR(200) NULL, "+
"PROJECT_ID VARCHAR(50) NULL "+
")";
jt.update(sql);
} else if (dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL"))
{
// Drop if already exists
String sql = "DROP TABLE IF EXISTS "+ tempTable;
jt.update(sql);
sql = "CREATE TEMP TABLE " + tempTable +" ( "+
"LCL_SITE VARCHAR(50) NULL, "+
"LCL_ID VARCHAR(200) NULL, "+
"PROJECT_ID VARCHAR(50) NULL "+
")";
jt.update(sql);
} else
{
tempTable = metadataSchema + tempTable;
}
//Save entries in temp table
String addSql = "insert into " + tempTable +
" (lcl_site, lcl_id, project_id) values (?,?,?)";
for (PidType pidType: returnType.getPid()) {
int numRowsAdded = 0;
PidType pidt = new PidType();
pidt.setPatientId(pidType.getPatientId());
for (PatientMapId pid : pidType.getPatientMapId()) {
//If enecrpyted than decyppy
if (pid.getSource().endsWith("_E"))
{
pid.setValue(highEnc.mrn_decrypt(pid.getValue(), true));
pid.setSource(pid.getSource().substring(0,pid.getSource().length()-2));
}
// pid.setValue( highEnc.mrn_encrypt(pid.getValue(), true, pid.getSource()));
// pid.setSource(pid.getSource() + "_E");
numRowsAdded += jt.update(addSql, pid.getSource(), pid.getValue(), projectInfo.getId());
}
List<PidType.PatientMapId> queryResult = null;
String tablesSql =
"SELECT distinct m1.lcl_id, m1.lcl_site " +
"FROM (SELECT global_id, " +
" lcl_site, " +
" lcl_id, " +
" lcl_status, " +
" Row_number() " +
" over ( " +
" PARTITION BY lcl_site, lcl_id " +
" ORDER BY update_date) AS new_id " +
" FROM " + metadataSchema + "im_mpi_mapping) m1, " +
metadataSchema + "im_mpi_demographics d, " +
metadataSchema + "im_project_patients pp, " +
metadataSchema + "im_project_sites ps, " +
tempTable + " ts " +
"WHERE m1.new_id = 1 " +
" AND d.global_id = m1.global_id " +
" AND d.global_status = 'A' " +
" AND m1.lcl_status = 'A' " +
" AND pp.global_id = d.global_id " +
" AND ps.project_id = pp.project_id " +
" AND m1.lcl_site = ts.lcl_site " +
" AND m1.lcl_id = ts.lcl_id " ;
try {
queryResult = jt.query(tablesSql, map);
// queryResult = jt.query(tablesSql, mapper, "N", projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error: " + e.getMessage());
}
log.debug("result size = " + queryResult.size());
//PatientMap
pidt.getPatientMapId().addAll(queryResult);
pidSet.getPid().add(pidt);
if (dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER") ) {
String sql = "DROP TABLE " + tempTable;
jt.update(sql);
} else if (dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL"))
{
String sql = "DISCARD TEMP ";
jt.update(sql);
} else {
String sql = "DELETE FROM " + tempTable;
jt.update(sql);
}
}
return pidSet;
}
public AuditsType getAudit(final GetAuditType auditType, final String userId, final ProjectType projectInfo, final DBInfoType dbInfo) throws Exception{
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.toLowerCase().equalsIgnoreCase("ADMIN") || (role.toLowerCase().equalsIgnoreCase("MANAGER"))) {
protectedAccess = true;
break;
}
}
if (!protectedAccess)
throw new I2B2DAOException("Access Denied");
ParameterizedRowMapper<AuditType> map = new ParameterizedRowMapper<AuditType>() {
public AuditType mapRow(ResultSet rs, int rowNum) throws SQLException {
AuditType pid = new AuditType();
pid.setPid(rs.getString("lcl_id"));
pid.setComment(rs.getString("comments"));
pid.setProjectId(rs.getString("project_id"));
pid.setSource(rs.getString("lcl_site"));
pid.setImportDate(dtoFactory
.getXMLGregorianCalendar(rs.getTimestamp(
"query_date").getTime()));
pid.setUserId(rs.getString("user_id"));
return pid;
}
};
List<AuditType> queryResult = null;
String tablesSql = "";
int min = 1;
int max = 1000;
if (dbInfo.getDb_serverType().equals("ORACLE")) {
tablesSql = "SELECT * FROM ( " +
" SELECT a.*, rownum r__ " +
" FROM " +
" ( " +
" SELECT * FROM " + metadataSchema + "im_audit WHERE ";
if (auditType != null)
{
if (auditType.getMin() != null)
min = auditType.getMin();
if (auditType.getMax() != null)
max = auditType.getMax();
if ((auditType.getPid() != null) && (auditType.getPid().length() > 0))
tablesSql += "lcl_site = '"+auditType.getSource()+"' and lcl_id = '"+auditType.getPid() +"' and ";
if ((auditType.getUserId() != null) && (auditType.getUserId().length() > 0))
tablesSql += "user_id = '"+ auditType.getUserId() +"' and ";
if ((auditType.getProjectId() != null) && (auditType.getProjectId().length() > 0))
tablesSql += " project_id = '"+auditType.getProjectId()+"' and ";
if (auditType.getComment() != null)
tablesSql += " comment = '"+auditType.getComment()+"' and ";
}
tablesSql += " 1 = 1 " +
" ORDER BY query_date DESC " +
" ) a " +
" WHERE rownum < (("+ min +" * "+max + ") + 1 ) " +
" ) " +
" WHERE r__ >= ((("+ min +"-1) * "+max + ") + 1) ";
} else if (dbInfo.getDb_serverType().equals("SQLSERVER") || (dbInfo.getDb_serverType().equals("POSTGRESQL"))) {
tablesSql = "SELECT * FROM ( " +
" SELECT ROW_NUMBER() OVER ( ORDER BY query_date ) AS RowNum, * " +
" FROM " + metadataSchema + "im_audit WHERE ";
if (auditType != null)
{
if (auditType.getMin() != null)
min = auditType.getMin();
if (auditType.getMax() != null)
max = auditType.getMax();
if (auditType.getPid() != null)
tablesSql += "lcl_site = '"+auditType.getSource()+"' and lcl_id = '"+auditType.getPid() +"' and ";
if ((auditType.getUserId() != null) && (auditType.getUserId().length() > 0))
tablesSql += "user_id = '"+ auditType.getUserId() +"' and ";
if ((auditType.getProjectId() != null) && (auditType.getProjectId().length() > 0))
tablesSql += " project_id = '"+auditType.getProjectId()+"' and ";
if (auditType.getComment() != null)
tablesSql += " comment = '"+auditType.getComment()+"' and ";
}
tablesSql += " 1=1 " +
" ) as RowConstrainedResult " +
" WHERE RowNum >= "+ min +" and RowNum < "+max +
" ORDER BY RowNum ";
}
try {
queryResult = jt.query(tablesSql, map);
// queryResult = jt.query(tablesSql, mapper, "N", projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error: " + e.getMessage());
}
log.debug("result size = " + queryResult.size());
AuditsType auditTypes = new AuditsType();
auditTypes.getAudit().addAll( queryResult);
return auditTypes; //patientdataResonse;
}
}