/*
* 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.pm.dao;
import java.io.IOException;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.Random;
import javax.sql.DataSource;
import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;
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.RowMapper;
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.xml.XMLUtil;
import edu.harvard.i2b2.pm.ejb.DBInfoType;
//import edu.harvard.i2b2.pm.services.EnvironmentData;
//import edu.harvard.i2b2.pm.services.HiveParamData;
//import edu.harvard.i2b2.pm.services.ProjectUserParamData;
//import edu.harvard.i2b2.pm.services.RegisteredCellParam;
//import edu.harvard.i2b2.pm.services.RoleData;
//import edu.harvard.i2b2.pm.services.VariableData;
import edu.harvard.i2b2.pm.services.HiveParamData;
import edu.harvard.i2b2.pm.services.ProjectUserParamData;
import edu.harvard.i2b2.pm.services.SessionData;
import edu.harvard.i2b2.pm.services.UserParamData;
//import edu.harvard.i2b2.pm.services.RegisteredCellParam;
//import edu.harvard.i2b2.pm.services.RoleData;
//import edu.harvard.i2b2.pm.services.VariableData;
import edu.harvard.i2b2.pm.util.PMUtil;
import edu.harvard.i2b2.pm.datavo.i2b2message.MessageHeaderType;
import edu.harvard.i2b2.pm.datavo.pm.ApprovalType;
import edu.harvard.i2b2.pm.datavo.pm.BlobType;
import edu.harvard.i2b2.pm.datavo.pm.ConfigureType;
import edu.harvard.i2b2.pm.datavo.pm.ParamType;
import edu.harvard.i2b2.pm.datavo.pm.ParamsType;
import edu.harvard.i2b2.pm.datavo.pm.PasswordType;
import edu.harvard.i2b2.pm.datavo.pm.CellDataType;
import edu.harvard.i2b2.pm.datavo.pm.GlobalDataType;
import edu.harvard.i2b2.pm.datavo.pm.ProjectRequestType;
import edu.harvard.i2b2.pm.datavo.pm.ProjectType;
import edu.harvard.i2b2.pm.datavo.pm.RoleType;
import edu.harvard.i2b2.pm.datavo.pm.RolesType;
import edu.harvard.i2b2.pm.datavo.pm.UserType;
public class PMDbDao extends JdbcDaoSupport {
private static Log log = LogFactory.getLog(PMDbDao.class);
private SimpleJdbcTemplate jt;
private String database = "";
public PMDbDao() throws I2B2Exception{
DataSource ds = null;
Connection conn = null;
try {
ds = PMUtil.getInstance().getDataSource("java:/PMBootStrapDS");
// database = ds.getConnection().getMetaData().getDatabaseProductName();
log.debug(ds.toString());
} catch (I2B2Exception e2) {
log.error("bootstrap ds failure: " + e2.getMessage());
throw e2;
// } catch (SQLException e2) {
// log.error("bootstrap ds failure: " + e2.getMessage());
//throw e2;
}
try {
conn = ds.getConnection();
database = conn.getMetaData().getDatabaseProductName();
conn.close();
conn = null;
} catch (Exception e)
{
conn = null;
log.error("Error geting database name:" + e.getMessage());
} finally
{
conn = null;
}
this.jt = new SimpleJdbcTemplate(ds);
// ds = null;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getUser(String userId, String caller) throws I2B2Exception, I2B2DAOException {
return getUser(userId, caller, null, true);
/*String sql = "select * from pm_user_data where user_id = ? and status_cd<>'D'";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getUser(), userId);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error in getting User Data");
}
return queryResult;
*/
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getEnvironmentData(String domainId) throws I2B2Exception, I2B2DAOException {
String sql = "select * from pm_hive_params where domain_id = ? and status_cd<>'D'";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getEnvironmentParams(), domainId);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error");
}
return queryResult;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getEnvironment(String domainId) throws I2B2Exception, I2B2DAOException {
String sql = "select * from pm_hive_data where status_cd <> 'D'";
if (domainId != null)
sql += " and domain_id = ?";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
log.debug("Start query");
if (domainId == null)
queryResult = jt.query(sql, getEnvironment());
else
queryResult = jt.query(sql, getEnvironment(), domainId);
log.debug("Query Size: " + queryResult.size());
log.debug("End query");
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getRole(String userId, String project ) throws I2B2Exception, I2B2DAOException {
String sql = "select distinct" +
" case upper(rr.COLUMN_CD)" +
" when '@' then pur.PROJECT_ID" +
" when 'PROJECT_ID' then pur.PROJECT_ID" +
" else null" +
" end as PROJECT_ID," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pur.USER_ID" +
" when 'USER_ID' then pur.USER_ID" +
" else null" +
" end as USER_ID," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pur.USER_ROLE_CD" +
" when 'USER_ROLE_CD' then pur.USER_ROLE_CD" +
" else null" +
" end as USER_ROLE_CD" +
" from " +
" pm_project_user_roles pur, pm_role_requirement rr" +
" where " +
" pur.status_cd<>'D' and" +
" rr.status_cd<>'D' and" +
" pur.user_id = ? and" +
(project!=null?" pur.project_id = ? and":"") +
" (rr.read_hivemgmt_CD = '@') OR (upper(rr.read_hivemgmt_CD) = upper(pur.USER_ROLE_CD)) and" +
" upper(rr.table_cd) = 'PM_PROJECT_USER_ROLES'";
// String sql = "select * from pm_project_user_roles where user_id=? and project_id=? and status_cd<>'D'";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
if (project == null)
queryResult = jt.query(sql, getRole(), userId);
else
queryResult = jt.query(sql, getRole(), userId, project);
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getRole(String userId) throws I2B2Exception, I2B2DAOException {
return getRole(userId, null);
/*
String sql = "select * from pm_project_user_roles where user_id=? and status_cd<>'D' order by project_id ";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getRole(), userId);
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
*/
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getProject(Object utype, boolean ignoreDeleted) throws I2B2Exception, I2B2DAOException {
String sql = "select distinct" +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_ID" +
" when 'PROJECT_ID' then pd.PROJECT_ID" +
" else null" +
" end as PROJECT_ID," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_NAME" +
" when 'PROJECT_NAME' then pd.PROJECT_NAME" +
" else null" +
" end as PROJECT_NAME," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_WIKI" +
" when 'PROJECT_WIKI' then pd.PROJECT_WIKI" +
" else null" +
" end as PROJECT_WIKI," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_PATH" +
" when 'PROJECT_PATH' then pd.PROJECT_PATH" +
" else null" +
" end as PROJECT_PATH," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_KEY" +
" when 'PROJECT_KEY' then pd.PROJECT_KEY" +
" else null" +
" end as PROJECT_KEY," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_DESCRIPTION" +
" when 'PROJECT_DESCRIPTION' then pd.PROJECT_DESCRIPTION" +
" else null" +
" end as PROJECT_DESCRIPTION" +
" from " +
" pm_project_data pd, pm_project_user_roles pur, pm_role_requirement rr" +
" where " +
" pur.status_cd<>'D' and" +
(ignoreDeleted?" pd.STATUS_CD<>'D' and ":"") +
" rr.status_cd<>'D' and" +
" pd.project_ID = ? and" +
//" pd.project_path = ? and" +
" (rr.read_hivemgmt_CD = '@') OR (upper(rr.read_hivemgmt_CD) = upper(pur.USER_ROLE_CD)) and" +
" pd.PROJECT_ID = pur.project_id and" +
" upper(rr.table_cd) = 'PM_PROJECT_USER_ROLES'";
// String sql = "select * from pm_project_data where project_id=? and status_cd<>'D'";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getProject(), ((ProjectType) utype).getId()); //, ((ProjectType) utype).getPath());
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
public List<DBInfoType> getUserProject(String user) throws I2B2Exception, I2B2DAOException {
String sql = "select distinct" +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_ID" +
" when 'PROJECT_ID' then pd.PROJECT_ID" +
" else null" +
" end as PROJECT_ID," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_NAME" +
" when 'PROJECT_NAME' then pd.PROJECT_NAME" +
" else null" +
" end as PROJECT_NAME," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_WIKI" +
" when 'PROJECT_WIKI' then pd.PROJECT_WIKI" +
" else null" +
" end as PROJECT_WIKI," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_PATH" +
" when 'PROJECT_PATH' then pd.PROJECT_PATH" +
" else null" +
" end as PROJECT_PATH," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_KEY" +
" when 'PROJECT_KEY' then pd.PROJECT_KEY" +
" else null" +
" end as PROJECT_KEY," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pd.PROJECT_DESCRIPTION" +
" when 'PROJECT_DESCRIPTION' then pd.PROJECT_DESCRIPTION" +
" else null" +
" end as PROJECT_DESCRIPTION" +
" from " +
" pm_project_data pd, pm_project_user_roles pur, pm_role_requirement rr" +
" where " +
" pd.status_cd<>'D' and" +
" pur.status_cd<>'D' and" +
" rr.status_cd<>'D' and" +
" pur.user_ID = ? and" +
" pd.PROJECT_ID = pur.PROJECT_ID and" +
" (rr.read_hivemgmt_CD = '@') OR (upper(rr.read_hivemgmt_CD) = upper(pur.USER_ROLE_CD)) and" +
" pd.PROJECT_ID = pur.project_id and" +
" upper(rr.table_cd) = 'PM_PROJECT_DATA'";
// String sql = "select distinct pd.* from pm_project_data pd, pm_project_user_roles pur where pd.project_id=pur.project_id and pur.user_id = ? and pur.status_cd<>'D' and pd.status_cd<>'D'";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getProject(), user);
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getProjectUserParams(String projectId, String userId) throws I2B2Exception, I2B2DAOException {
String sql = "select * from pm_project_user_params where project_id=? and user_id=? and status_cd<>'D'";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getProjectUserParams(), projectId, userId);
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getProjectParams(String projectId) throws I2B2Exception, I2B2DAOException {
String sql = "select * from pm_project_params where project_id=? and status_cd<>'D'";
//log.debug(sql + projectId );
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getProjectParams(), projectId);
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getCell(String cell, String project, boolean ignoreDeleted) throws I2B2Exception, I2B2DAOException {
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
log.debug("Searching for cell: " + cell + " within project " + project);
try {
if (cell.equals("@"))
{
String sql = "select * from pm_cell_data where project_path = ?";
if (ignoreDeleted)
sql += " and status_cd<>'D'";
queryResult = jt.query(sql, getCell(), project);
} else if ((cell.equals("@") && !project.equals("/")))
{
String sql = "select * from pm_cell_data where project_path = ?";
if (ignoreDeleted)
sql += " and status_cd<>'D'";
queryResult = jt.query(sql, getCell(), project);
} else
{
String sql = "select * from pm_cell_data where cell_id = ? and project_path = ?";
if (ignoreDeleted)
sql += " and status_cd<>'D'";
queryResult = jt.query(sql, getCell(), cell, project);
}
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getApproval(ApprovalType approval, boolean ignoreDeleted) throws I2B2Exception, I2B2DAOException {
// log.info(sql + domainId + projectId + ownerId);
String sql = "select a.* from pm_approvals a ";
String sqlWhere = " where a.object_cd = 'APPROVAL' ";
String sqlFrom = "";
List<DBInfoType> queryResult = null;
try {
ArrayList al = new ArrayList();
if (approval.getId() != null)
{
sqlWhere += "and a.approval_id = ? ";
if (ignoreDeleted)
sqlWhere += "and a.status_cd<>'D' ";
al.add(approval.getId());
}
else {
// Search if user and project are set
String foundUser = "", foundProject = "";
for (int i=0; i < approval.getSearch().size(); i++)
{
if (approval.getSearch().get(i).getBy().equalsIgnoreCase("USER"))
foundUser = approval.getSearch().get(i).getValue().toUpperCase();
if (approval.getSearch().get(i).getBy().equalsIgnoreCase("PROJECT"))
foundUser = approval.getSearch().get(i).getValue().toUpperCase();
}
if ((foundUser != "") && (foundProject != ""))
{
sqlFrom += ", pm_project_user_params p ";
sqlWhere += "and a.STATUS_CD = 'A' and p.STATUS_CD = 'A' and " +
"p.PARAM_NAME_CD = 'APPROVAL_ID' and p.VALUE = a.APPROVAL_ID and p.USER_ID = ? and p.PROJECT_ID = ?";
al.add(foundUser);
al.add(foundProject);
}
for (int i=0; i < approval.getSearch().size(); i++)
{
if ((approval.getSearch() != null) && (approval.getSearch().get(i).getBy().equalsIgnoreCase("NAME")))
{
sqlWhere += "and UPPER(a.approval_name) = ? ";
if (ignoreDeleted)
sqlWhere += "and a.status_cd<>'D' ";
al.add(approval.getSearch().get(i).getValue().toUpperCase());
// queryResult = jt.query(sql, getApproval(), approval.getActivationDate());
}
if ((approval.getSearch() != null) && (approval.getSearch().get(i).getBy().equalsIgnoreCase("ACTIVATION_DATE")))
{
sqlWhere += "and a.activation_date = ? ";
if (ignoreDeleted)
sqlWhere += "and a.status_cd<>'D' ";
al.add(approval.getSearch().get(i).getValue().toUpperCase());
// queryResult = jt.query(sql, getApproval(), approval.getActivationDate());
} else if ((approval.getSearch() != null) && ((foundUser == "") || (foundProject == "")) && (approval.getSearch().get(i).getBy().equalsIgnoreCase("USER")))
{
sqlFrom += ", pm_user_params p ";
sqlWhere += "and a.STATUS_CD = 'A' and p.STATUS_CD = 'A' and " +
"p.PARAM_NAME_CD = 'APPROVAL_ID' and p.VALUE = a.APPROVAL_ID and p.USER_ID = ? ";
al.add(approval.getSearch().get(i).getValue());
//sql = "select a.* from pm_approvals a, pm_user_params p where a.STATUS_CD = 'A' and p.STATUS_CD = 'A' and " +
//"p.PARAM_NAME_CD = 'APPROVAL' and p.VALUE = a.OBJECT_CD and p.USER_ID = ? ";
// queryResult = jt.query(sql, getApproval(), approval.getSearch().get(0).getValue());
} else if ((approval.getSearch() != null) && ((foundUser == "") || (foundProject == "")) && (approval.getSearch().get(i).getBy().equalsIgnoreCase("PROJECT")))
{
sqlFrom += ", pm_project_params p ";
sqlWhere += "and a.STATUS_CD = 'A' and p.STATUS_CD = 'A' and " +
"p.PARAM_NAME_CD = 'APPROVAL_ID' and p.VALUE = a.APPROVAL_ID and p.PROJECT_ID = ? ";
al.add(approval.getSearch().get(i).getValue());
//sql = "select a.* from pm_approvals a, pm_project_params p where a.STATUS_CD = 'A' and p.STATUS_CD = 'A' and " +
//"p.PARAM_NAME_CD = 'APPROVAL' and p.VALUE = a.OBJECT_CD and p.PROJECT_ID = ? ";
// queryResult = jt.query(sql, getApproval(), approval.getSearch().get(0).getValue());
}
}
}
sql += sqlFrom + sqlWhere;
log.debug("My sql statement: " + sql);
queryResult = jt.query(sql, getApproval(), al.toArray());
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
@SuppressWarnings("unchecked")
public List<DBInfoType> getCellParam(String cellId, String project) throws I2B2Exception, I2B2DAOException {
String sql = "select * from pm_cell_params where cell_id = ? and project_path = ? and status_cd<>'D'";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getParam(), cellId, project);
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
private boolean validateRole(String caller, String isrole, String project) throws I2B2DAOException
{
List response = null;
if (isrole.equalsIgnoreCase("admin"))
{
try {
String sql = "select * from pm_project_user_roles where project_id=? and user_id=? and status_cd<>'D' order by project_id";
response = jt.query(sql, getRole(), "@", caller);
//response = getRole(caller, project);
} catch (Exception e1) {
throw new I2B2DAOException ("Database error in getting role data for validateRole");
}
Iterator it = response.iterator();
while (it.hasNext()){
RoleType user = (RoleType) it.next();
if(user.getRole().equalsIgnoreCase("ADMIN")) {
return(true);
}
}
return false;
}
try {
response = getRole(caller, project);
} catch (I2B2DAOException e1) {
throw new I2B2DAOException ( "Database error in getting user data for setuser");
} catch (I2B2Exception e1) {
throw new I2B2DAOException ("Database error in getting user data for setuser");
}
Iterator it = response.iterator();
while (it.hasNext()){
RoleType role = (RoleType) it.next();
if(role.getRole().toLowerCase().equals(isrole)) {
return(true);
}
}
return false;
}
// All user Process
public List<DBInfoType> getUser(String user, String caller,String password, boolean ignoreDeleted) throws I2B2Exception, I2B2DAOException {
String sql = null;
List<DBInfoType> queryResult = null;
if (caller == null)
{
sql = "select * from pm_user_data where user_id = ? "+ (password!=null?" and password = ? ":"");
if (ignoreDeleted)
sql += " and status_cd<>'D'";
try {
if (password == null)
queryResult = jt.query(sql, getUser(true), user);
else
queryResult = jt.query(sql, getUser(false), user, password);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error in getting userdata with password");
}
} else {
sql = "select distinct" +
" case upper(rr.COLUMN_CD)" +
" when '@' then pud.USER_ID" +
" when 'USER_ID' then pud.USER_ID" +
" else null" +
" end as USER_ID," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pud.FULL_NAME" +
" when 'FULL_NAME' then pud.FULL_NAME" +
" else null" +
" end as FULL_NAME," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pud.PASSWORD" +
" when 'PASSWORD' then pud.PASSWORD" +
" else null" +
" end as PASSWORD," +
" case upper(rr.COLUMN_CD)" +
" when '@' then pud.EMAIL" +
" when 'EMAIL' then pud.EMAIL" +
" else null" +
" end as EMAIL " +
" from " +
" pm_user_data pud, pm_role_requirement rr" +
" where " +
//" pur.status_cd<>'D' and" +
" rr.status_cd<>'D' and" +
(ignoreDeleted?" pud.STATUS_CD<>'D' and ":"") +
//" pur.USER_ID = ? and " +
" pud.user_id = ? and" +
(password!=null?" password = ? and":"") +
" (rr.read_hivemgmt_CD = '@') and" + //OR (upper(rr.read_hivemgmt_CD) = upper(pur.USER_ROLE_CD)) and" +
" upper(rr.table_cd) = 'PM_USER_DATA'";
try {
if (password == null)
queryResult = jt.query(sql, getUser(true), user);
else
queryResult = jt.query(sql, getUser(false), user, password);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error in getting userdata with password");
}
}
// String sql = "select * from pm_user_data where user_id = ? and password = ? and status_cd <> 'D'";
// log.info(sql + domainId + projectId + ownerId);
return queryResult;
}
public List<DBInfoType> getAllProjectRequest(String project, String caller) throws I2B2Exception, I2B2DAOException {
String sql = null;
List<DBInfoType> queryResult = null;
if ((validateRole(caller, "admin", null)) || (validateRole(caller, "admin", null)))
{
sql = "select * from pm_project_request where status_cd<>'D'";
queryResult = jt.query(sql, getProjectRequest());
}
return queryResult;
}
public List<DBInfoType> getAllUser(String project, String caller) throws I2B2Exception, I2B2DAOException {
String sql = null;
List<DBInfoType> queryResult = null;
if ((validateRole(caller, "admin", null)) || (validateRole(caller, "admin", null)))
{
sql = "select * from pm_user_data where status_cd<>'D'";
queryResult = jt.query(sql, getUser(false));
}
return queryResult;
}
public int setUser(final UserType userdata, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if (validateRole(caller, "admin", null))
{
try {
if ((getUser(userdata.getUserName(), caller, null, false) == null) || (getUser(userdata.getUserName(), caller,null, false).size() == 0))
{
String addSql = "insert into pm_user_data " +
"(user_id, full_name, email, password, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
userdata.getUserName(),
userdata.getFullName(),
userdata.getEmail(),
PMUtil.getInstance().getHashedPassword(userdata.getPassword().getValue()),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else if (userdata.getPassword() != null)
{
//user already exists, lets try to update
String addSql = "update pm_user_data " +
"set full_name = ?, email = ?, password = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where user_id = ?";
numRowsAdded = jt.update(addSql,
userdata.getFullName(),
userdata.getEmail(),
PMUtil.getInstance().getHashedPassword(userdata.getPassword().getValue()),
Calendar.getInstance().getTime(),
caller,
userdata.getUserName());
} else
{
//user already exists, lets try to update
String addSql = "update pm_user_data " +
"set full_name = ?, email = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where user_id = ?";
numRowsAdded = jt.update(addSql,
userdata.getFullName(),
userdata.getEmail(),
Calendar.getInstance().getTime(),
caller,
userdata.getUserName());
}
// Deal with is_admin
String addSql = "update pm_project_user_roles " +
" set status_cd = 'D', change_date = ?, changeby_char = ? where user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
userdata.getUserName(),
"ADMIN");
if (userdata.isIsAdmin() == true) {
try {
addSql = "insert into pm_project_user_roles " +
"( project_id, user_id, user_role_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?)";
numRowsAdded += jt.update(addSql,
"@",
userdata.getUserName(),
"ADMIN",
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} catch (Exception e) {
addSql = "update pm_project_user_roles " +
" set status_cd = 'A', change_date = ?, changeby_char = ? where project_id = ? and user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
"@",
userdata.getUserName(),
"ADMIN");
}
}
} catch (DataAccessException e) {
log.error("Dao update setuser failed for: " + userdata.getUserName());
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
}
else
{
throw new I2B2DAOException("Access Denied for " + caller);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows added: " + numRowsAdded);
return numRowsAdded;
}
public int deleteUser(final String user, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if (validateRole(caller, "admin", null))
{
try {
String addSql = "update pm_user_data " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where user_id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
user);
if (numRowsAdded ==0)
throw new I2B2DAOException("User not updated, does it exist?");
} catch (DataAccessException e) {
log.error("Dao deleteuser failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
}
else
{
throw new I2B2DAOException("Access Denied for " + caller);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows deleted: " + numRowsAdded);
return numRowsAdded;
}
public List<DBInfoType> setProjectRequest(final ProjectRequestType groupdata,String project, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
List<DBInfoType> queryResult = null;
try {
String clob = null;
if (groupdata.getRequestXml() != null)
{
clob = groupdata.getRequestXml();
/*
BlobType blobType = (BlobType)groupdata.getRequestXml();
for (int i=0; i < blobType.getContent().size(); i++)
{
clob = (String) blobType.getContent().get(i);
//Clob myclob = (Clob) blobType.getContent().get(i);
// int len = (int) myclob.length();
// clob = myclob.getSubString(Long.parseLong("1"),len);
// blobType.getContent().add(
// JDBCUtil.getClobString(clob));
// rData.setRequestXml(blobType);
}
*/
}
String addSql = "insert into pm_project_request " +
"(title, request_xml, project_id, change_date, entry_date, submit_char, changeby_char, status_cd) values (?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
groupdata.getTitle(),
clob,
(project == null? "@" : project),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
caller,
"A");
if (numRowsAdded != 0)
{
addSql = "select * from pm_project_request where id = ( select max(id) from pm_project_request)";
queryResult = jt.query(addSql, getProjectRequest());
}
} catch (Exception e) {
log.error("Dao deleteuser failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
return queryResult;
}
public int setPassword(final String password, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
try {
String addSql = "update pm_user_data " +
"set password = ?, change_date = ?, changeby_char = ? where user_id = ?";
numRowsAdded = jt.update(addSql,
password,
Calendar.getInstance().getTime(),
caller,
caller);
if (numRowsAdded ==0)
throw new I2B2DAOException("User not updated, does it exist?");
} catch (DataAccessException e) {
log.error("Dao deleteuser failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows deleted: " + numRowsAdded);
return numRowsAdded;
}
// All Cell Process
/*
public List<DBInfoType> getCell(String cell, String project, String owner) throws I2B2Exception, I2B2DAOException {
String sql = "select * from cell_data where cell_id = ? and project_path = ? and owner_id = ?";
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
queryResult = jt.query(sql, getCell(), cell, project, owner);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error");
}
return queryResult;
}
*/
public List<DBInfoType> getAllApproval(String project, String caller) throws I2B2Exception, I2B2DAOException {
String sql = null;
List<DBInfoType> queryResult = null;
sql = "select * from pm_approvals where status_cd<>'D'";
queryResult = jt.query(sql, getApproval());
return queryResult;
}
public List<DBInfoType> getAllCell(String project, String caller) throws I2B2Exception, I2B2DAOException {
String sql = null;
List<DBInfoType> queryResult = null;
sql = "select * from pm_cell_data where status_cd<>'D'";
queryResult = jt.query(sql, getCell());
return queryResult;
}
public List<DBInfoType> getSession(String userId, String sessionID) throws I2B2Exception, I2B2DAOException {
String sql = "select * from pm_user_session where user_id = ? and session_id = ?";
List<DBInfoType> queryResult = null;
log.debug("Searching for " + userId + " with session id of " + sessionID);
queryResult = jt.query(sql, getSession(), userId, sessionID);
return queryResult;
}
public boolean verifyNotLockedOut(String userId)
{
String sql = null;
//get results count max
sql = "select * from pm_global_params where status_cd = 'A' and param_name_cd ='PM_LOCKED_MAX_COUNT'";
int resultmax = 10;
try {
List<DBInfoType> queryResult = jt.query(sql, getParam());
Iterator it = queryResult.iterator();
while (it.hasNext())
{
ParamType user = (ParamType)it.next();
resultmax = Integer.parseInt(user.getValue());
}
} catch (Exception e)
{
e.printStackTrace();
}
sql = "select * from pm_global_params where status_cd = 'A' and param_name_cd ='PM_LOCKED_WAIT_TIME'";
int waittime = 2;
try {
List<DBInfoType> queryResult = jt.query(sql, getParam());
Iterator it = queryResult.iterator();
while (it.hasNext())
{
ParamType user = (ParamType)it.next();
waittime = Integer.parseInt(user.getValue());
}
} catch (Exception e)
{
e.printStackTrace();
}
if (database.equalsIgnoreCase("oracle"))
sql = "select count(*) as badlogin from pm_user_login where user_id = ? and " +
" attempt_cd = 'BADPASSWORD' and " +
"(entry_date + interval '" + waittime + "' minute) >= CURRENT_TIMESTAMP ";
else if (database.equalsIgnoreCase("Microsoft sql server"))
sql = "select count(*) as badlogin from pm_user_login where user_id = ? and " +
" attempt_cd = 'BADPASSWORD' and " +
"dateadd(minute, " + waittime + ", entry_date) >= getdate() ";
else if (database.equalsIgnoreCase("postgresql"))
sql = "select count(*) as badlogin from pm_user_login where user_id = ? and " +
" attempt_cd = 'BADPASSWORD' and " +
"(entry_date + cast('" + waittime + " minutes' as interval)) >= now() ";
int results = jt.queryForInt(sql, userId);
//int results = 0;
if (results >= resultmax)
return true;
else
return false;
}
public int setLoginAttempt(String userId, String attemptCd) {
String addSql = "insert into pm_user_login " +
"(user_id, attempt_cd, changeby_char, entry_date, status_cd) values (?,?,?,?,'A')";
int numRowsAdded =
jt.update(addSql,
userId,
attemptCd,
userId,
Calendar.getInstance().getTime());
return numRowsAdded;
}
public int setSession(String userId, String sessionId, int timeout)
{
String addSql = "insert into pm_user_session " +
"(user_id, session_id, changeby_char, entry_date, expired_date) values (?,?,?,?,?)";
Calendar now = Calendar.getInstance();
now.add(Calendar.MILLISECOND, timeout);
int numRowsAdded = jt.update(addSql,
userId,
sessionId,
userId,
Calendar.getInstance().getTime(),
now.getTime());
return numRowsAdded;
}
public int removeSession(String userId, String sessionId)
{
String addSql = "delete from pm_user_session " +
" where session_id = ? and user_id =?";
int numRowsAdded = jt.update(addSql,
userId,
sessionId);
addSql = "delete from pm_user_session " +
" where expired_date > ?";
int numRowsAdded2 = jt.update(addSql,
Calendar.getInstance().getTime());
return numRowsAdded;
}
public int updateSession(String userId, String sessionId, int timeout)
{
int numRowsAdded = -1;
String addSql = "update pm_user_session set expired_date = ? " +
" where session_id = ? and user_id =?";
Calendar now = Calendar.getInstance();
now.add(Calendar.MILLISECOND, timeout);
try {
numRowsAdded = jt.update(addSql,
now.getTime(),
sessionId,
userId);
} catch (Exception e)
{ try {
if (e.getMessage().contains("deadlock")
|| e.getMessage().contains("try restarting transaction")
|| e.getMessage().contains(
"failed to resume the transaction")) {
int tosleep = new Random().nextInt(2000);
log.warn("Transaction rolled back. Restarting transaction.");
Thread.sleep(tosleep);
numRowsAdded = jt.update(addSql,
now.getTime(),
sessionId,
userId);
} else {
throw e;
} } catch (Exception ee) {}
}
return numRowsAdded;
}
public int setCell(final CellDataType groupdata, String project, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if ((validateRole(caller, "admin", null)) || (validateRole(caller, "manager", project)))
{
log.debug("Setting cell with ID of " + groupdata.getId() + " and path of " + groupdata.getProjectPath());
if ((getCell(groupdata.getId(), groupdata.getProjectPath(), false) == null) || (getCell(groupdata.getId(), groupdata.getProjectPath(), false).size() == 0))
{
String addSql = "insert into pm_cell_data " +
"(cell_id, project_path, name, url, method_cd, can_override, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
groupdata.getId(),
(groupdata.getProjectPath() == null? "@" : groupdata.getProjectPath()),
groupdata.getName(),
groupdata.getUrl(),
groupdata.getMethod(),
(groupdata.isCanOverride() == null? 1 : groupdata.isCanOverride() ? 1 : 0),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_cell_data " +
"set name = ?, url = ?, method_cd = ?, can_override = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where cell_id = ? and project_path = ?";
numRowsAdded = jt.update(addSql,
groupdata.getName(),
groupdata.getUrl(),
groupdata.getMethod(),
(groupdata.isCanOverride() == null? 1 : groupdata.isCanOverride() ? 1 : 0),
Calendar.getInstance().getTime(),
caller,
groupdata.getId(),
groupdata.getProjectPath());
}
}
else
{
throw new I2B2DAOException("Access Denied for " + caller);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows added: " + numRowsAdded);
return numRowsAdded;
}
public int deleteCell(String cell, String project, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if ((validateRole(caller, "admin", null)) || (validateRole(caller, "manager", project)))
{
try {
String addSql = "update pm_cell_data " +
"set status_cd = 'D' where project_path = ? and cell_id = ?";
numRowsAdded = jt.update(addSql,
((project == null || project.equals(""))? "@": project),
cell);
if (numRowsAdded ==0)
throw new I2B2DAOException("Cell not updated, does it exist?");
} catch (DataAccessException e) {
log.error("Dao deleteuser failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows deleted: " + numRowsAdded);
return numRowsAdded;
}
public int setApproval(final ApprovalType groupdata, String project, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if ((validateRole(caller, "admin", null)) || (validateRole(caller, "manager", project)))
{
if ((getApproval(groupdata, false) == null) || (getApproval(groupdata, false).size() == 0))
{
String addSql = "insert into pm_approvals " +
"(approval_id, approval_name, approval_description, approval_activation_date, approval_expiration_date, object_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
groupdata.getId(),
groupdata.getName(),
groupdata.getDescription(),
groupdata.getActivationDate(),
groupdata.getExpirationDate(),
groupdata.getObjectCd(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_approvals " +
"set approval_name = ?, approval_description = ?, approval_activation_date = ?, approval_expiration_date = ?, object_cd = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where approval_id = ?";
numRowsAdded = jt.update(addSql,
groupdata.getName(),
groupdata.getDescription(),
groupdata.getActivationDate(),
groupdata.getExpirationDate(),
groupdata.getObjectCd(),
Calendar.getInstance().getTime(),
caller,
groupdata.getId());
}
}
else
{
throw new I2B2DAOException("Access Denied for " + caller);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows added: " + numRowsAdded);
return numRowsAdded;
}
public int deleteApproval(String id, String project, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if ((validateRole(caller, "admin", null)) || (validateRole(caller, "manager", project)))
{
try {
String addSql = "update pm_approvals " +
"set status_cd = 'D' where approval_id = ?";
numRowsAdded = jt.update(addSql,
id);
if (numRowsAdded ==0)
throw new I2B2DAOException("approval not updated, does it exist?");
} catch (DataAccessException e) {
log.error("Dao deleteuser failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows deleted: " + numRowsAdded);
return numRowsAdded;
}
public List<DBInfoType> getAllProject(String project, String caller) throws I2B2Exception, I2B2DAOException {
String sql = null;
List<DBInfoType> queryResult = null;
if (validateRole(caller, "admin", null))
{
sql = "select * from pm_project_data where status_cd<>'D'";
queryResult = jt.query(sql, getProject());
}
return queryResult;
}
public int setProject(final ProjectType groupdata, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if (validateRole(caller, "admin", null))
{
if ((getProject(groupdata, false) == null) || (getProject(groupdata, false).size() == 0))
{
String addSql = "insert into pm_project_data " +
"(project_id, project_name, project_key, project_path, project_description, project_wiki, changeby_char, change_date, entry_date, status_cd) values (?,?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
groupdata.getId(),
groupdata.getName(),
groupdata.getKey(),
groupdata.getPath(),
groupdata.getDescription(),
groupdata.getWiki(),
caller,
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
"A");
} else
{
//project already exists, lets try to update
String addSql = "update pm_project_data " +
"set project_name = ?, project_key = ?, project_wiki = ?, project_description = ?, project_path = ?, changeby_char = ?, change_date = ?, status_cd = 'A' where project_id = ?";
numRowsAdded = jt.update(addSql,
groupdata.getName(),
groupdata.getKey(),
groupdata.getWiki(),
groupdata.getDescription(),
groupdata.getPath(),
caller,
Calendar.getInstance().getTime(),
groupdata.getId());
}
}
else
{
throw new I2B2DAOException("Access Denied for " + caller);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows added: " + numRowsAdded);
return numRowsAdded;
}
public List<DBInfoType> getAllParam(Object utype, String project, String caller) throws I2B2Exception, I2B2DAOException {
String sql = null;
List<DBInfoType> queryResult = null;
// if (validateRole(caller, "admin", null))
// {
if (utype instanceof ProjectType)
{
if (((ProjectType) utype).getUserName() == null)
{
sql = "select * from pm_project_params where status_cd<>'D' and project_id = ? order by project_id";
queryResult = jt.query(sql, getParam(), ((ProjectType) utype).getId());
} else
{
ArrayList al = new ArrayList();
sql = "select * from pm_project_user_params where status_cd<>'D' ";
if (((ProjectType) utype).getUserName() != null) // || !((UserType) utype).getUserName().equals(""))
{
sql += " and user_id=?";
al.add(((ProjectType) utype).getUserName());
//sql = "select * from pm_user_params where status_cd<>'D' order by user_id";
//queryResult = jt.query(sql, getUserParams());
}
if (((ProjectType) utype).getPath() != null)
{
sql += " and project_id=?";
al.add(((ProjectType) utype).getPath());
}
if (((ProjectType) utype).getParam() != null)
{
for (int i=0; i < ((ProjectType) utype).getParam().size(); i++)
{
if (((ProjectType) utype).getParam().get(i).getName() != null)
{
sql += " and param_name_cd=?";
al.add((((ProjectType) utype).getParam().get(i).getName()));
}
if (((ProjectType) utype).getParam().get(i).getValue() != null)
{
sql += " and value=?";
al.add((((ProjectType) utype).getParam().get(i).getValue()));
}
}
}
log.debug("My SQL: " + sql);
queryResult = jt.query(sql, getParam(), al.toArray());
/*
if (((ProjectType) utype).getUserName() != null && !((ProjectType) utype).getUserName().equals("") )
{
sql = "select * from pm_project_user_params where project_id=? and user_id = ? and status_cd<>'D'";
queryResult = jt.query(sql, getParam(), ((ProjectType) utype).getId(), ((ProjectType) utype).getUserName());
} else {
sql = "select * from pm_project_params where project_id=? and status_cd<>'D'";
queryResult = jt.query(sql, getParam(), ((ProjectType) utype).getId());
}
*/
}
}
else if (utype instanceof UserType)
{
ArrayList al = new ArrayList();
sql = "select * from pm_user_params where status_cd<>'D' ";
if (((UserType) utype).getUserName() != null) // || !((UserType) utype).getUserName().equals(""))
{
sql += " and user_id=?";
al.add(((UserType) utype).getUserName());
//sql = "select * from pm_user_params where status_cd<>'D' order by user_id";
//queryResult = jt.query(sql, getUserParams());
}
if (((UserType) utype).getParam() != null)
{
for (int i=0; i < ((UserType) utype).getParam().size(); i++)
{
if (((UserType) utype).getParam().get(i).getName() != null)
{
sql += " and param_name_cd=?";
al.add((((UserType) utype).getParam().get(i).getName()));
}
if (((UserType) utype).getParam().get(i).getValue() != null)
{
if (database.equalsIgnoreCase("oracle"))
sql += " and to_char(value)=?";
else
sql += " and value=?";
al.add((((UserType) utype).getParam().get(i).getValue()));
}
}
}
queryResult = jt.query(sql, getUserParams(), al.toArray());
}
else if (utype instanceof ApprovalType)
{
if (((ApprovalType) utype).getId() == null)
{
sql = "select * from pm_approvals where status_cd<>'D' order by id";
queryResult = jt.query(sql, getApproval());
} else {
sql = "select * from pm_approvals_params where id=? and status_cd<>'D'";
queryResult = jt.query(sql, getParam(), ((ApprovalType) utype).getId());
}
}
else if (utype instanceof ConfigureType)
{
if (((ConfigureType) utype).getDomainId() == null)
{
sql = "select * from pm_hive_data where status_cd<>'D' order by domain_id";
queryResult = jt.query(sql, getEnvironment());
} else {
sql = "select * from pm_hive_params where domain_id=? and status_cd<>'D'";
queryResult = jt.query(sql, getParam(), ((ConfigureType) utype).getDomainId());
}
}
else if (utype instanceof GlobalDataType)
{
if (((GlobalDataType) utype).getProjectPath() == null)
{
sql = "select * from pm_global_params where status_cd<>'D'";
queryResult = jt.query(sql, getParam());
}
else
{
sql = "select * from pm_global_params where project_path = ? and status_cd<>'D'";
queryResult = jt.query(sql, getParam(), ((GlobalDataType) utype).getProjectPath());
}
}
else if (utype instanceof CellDataType)
{
if (((CellDataType) utype).getProjectPath() == null)
{
sql = "select * from pm_cell_params where status_cd<>'D' order by project_path";
queryResult = jt.query(sql, getParam());
} else {
sql = "select * from pm_cell_params where project_path=? and cell_id=? and status_cd<>'D'";
queryResult = jt.query(sql, getParam(), ((CellDataType) utype).getProjectPath(), ((CellDataType) utype).getId());
}
}
else if (utype instanceof RoleType)
{
String addsql = " and user_id = '" + caller + "' ";
if ((validateRole(caller, "admin", null)) || (validateRole(caller, "manager", project)))
{
addsql = "";
}
if (((RoleType) utype).getProjectId() == null)
{
sql = "select * from pm_project_user_roles where status_cd<>'D' " + addsql + " order by project_id";
queryResult = jt.query(sql, getRole());
} else if (((RoleType) utype).getUserName() != null)
{
sql = "select * from pm_project_user_roles where project_id=? and user_id=? and status_cd<>'D' " + addsql + " order by project_id";
queryResult = jt.query(sql, getRole(), ((RoleType) utype).getProjectId(), ((RoleType) utype).getUserName());
}
else {
sql = "select * from pm_project_user_roles where project_id=? and status_cd<>'D' " + addsql;
queryResult = jt.query(sql, getRole(), ((RoleType) utype).getProjectId());
}
// }
}
return queryResult;
}
public List<DBInfoType> getParam(Object utype, boolean showStatus) throws I2B2Exception, I2B2DAOException {
// log.info(sql + domainId + projectId + ownerId);
List<DBInfoType> queryResult = null;
try {
if (utype instanceof ProjectType)
{
if (((ProjectType) utype).getUserName() != null && !((ProjectType) utype).getUserName().equals("") )
{
String sql = "select * from pm_project_user_params where id=? " + (showStatus == false? "" :" and status_cd<>'D'");
if (((ProjectType) utype).getParam().get(0).getId() != null)
queryResult = jt.query(sql, getParam(),
((ProjectType) utype).getParam().get(0).getId());
} else {
String sql = "select * from pm_project_params where id=? " + (showStatus == false? "" :" and status_cd<>'D'");
if (((ProjectType) utype).getParam().get(0).getId() != null)
queryResult = jt.query(sql, getParam(),
((ProjectType) utype).getParam().get(0).getId());
}
}
else if (utype instanceof GlobalDataType)
{
String sql = "select * from pm_global_params where id=? " + (showStatus == false? "" :" and status_cd<>'D'");
if (((GlobalDataType) utype).getParam().get(0).getId() != null)
queryResult = jt.query(sql, getGlobal(),
((GlobalDataType) utype).getParam().get(0).getId());
}
else if (utype instanceof ApprovalType)
{
String sql = "select * from pm_approval_params where id=? " + (showStatus == false? "" :" and status_cd<>'D'");
if (((ApprovalType) utype).getParam().get(0).getId() != null)
queryResult = jt.query(sql, getParam(),
((UserType) utype).getParam().get(0).getId());
}
else if (utype instanceof UserType)
{
String sql = "select * from pm_user_params where id=? " + (showStatus == false? "" :" and status_cd<>'D'");
if (((UserType) utype).getParam().get(0).getId() != null)
queryResult = jt.query(sql, getParam(),
((UserType) utype).getParam().get(0).getId());
}
else if (utype instanceof CellDataType)
{
String sql = "select * from pm_cell_params where id=? " + (showStatus == false? "" :" and status_cd<>'D'");
if (((CellDataType) utype).getParam().get(0).getId() != null)
queryResult = jt.query(sql, getParam(),
((CellDataType) utype).getParam().get(0).getId());
}
else if (utype instanceof RoleType)
{
String sql = "select * from pm_project_user_roles where project_id=? and user_id=? " + (showStatus == false? "" :" and status_cd<>'D'");
queryResult = jt.query(sql, getRole(),
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName());
}
else if (utype instanceof ConfigureType)
{
if (((ConfigureType) utype).getParam().isEmpty() == false) // || (((ConfigureType) utype).getDomainId()).size() == 0))
{
String sql = "select * from pm_hive_params where id=? " + (showStatus == false? "" :" and status_cd<>'D'");
if (((ConfigureType) utype).getParam().get(0).getId() != null)
queryResult = jt.query(sql, getParam(),
((ConfigureType) utype).getParam().get(0).getId());
} else {
String sql = "select * from pm_hive_data where domain_id=? " + (showStatus == false? "" :" and status_cd<>'D'");
if (((ConfigureType) utype).getParam().get(0).getId() != null)
queryResult = jt.query(sql, getEnvironment(),
((ConfigureType) utype).getDomainId());
}
}
} catch (DataAccessException e) {
log.error(e.getMessage());
e.printStackTrace();
throw new I2B2DAOException("Database error");
}
return queryResult;
}
public int setParam(Object utype, String project, String name, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
log.debug("Caller: "+ caller);
log.debug("Project: " + project);
if ((utype instanceof UserType) && (caller.equals(((UserType) utype).getUserName())))
{
log.debug("Searching for existing User Param");
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_user_params " +
"(user_id, datatype_cd, param_name_cd, value, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?)";
log.debug("Ading new User Param");
numRowsAdded = jt.update(addSql,
((UserType) utype).getUserName(),
((UserType) utype).getParam().get(0).getDatatype(),
((UserType) utype).getParam().get(0).getName(),
((UserType) utype).getParam().get(0).getValue(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_user_params " +
"set value = ?, datatype_cd = ?, change_date = ?, status_cd = 'A' where changeby_char = ? and id = ? ";
log.debug("Updating User Param");
numRowsAdded = jt.update(addSql,
((UserType) utype).getParam().get(0).getValue(),
((UserType) utype).getParam().get(0).getDatatype(),
Calendar.getInstance().getTime(),
caller,
((UserType) utype).getParam().get(0).getId());
if (numRowsAdded == 0)
throw new I2B2DAOException("Record does not exist or access denied.");
}
}
else if ((utype instanceof ProjectType) && (((ProjectType) utype).getUserName() != null) && (((ProjectType) utype).getUserName().equals(caller) ))
{
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_project_user_params " +
"(project_id, user_id, datatype_cd, param_name_cd, value, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
((ProjectType) utype).getId(),
((ProjectType) utype).getUserName(),
((ProjectType) utype).getParam().get(0).getDatatype(),
((ProjectType) utype).getParam().get(0).getName(),
((ProjectType) utype).getParam().get(0).getValue(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_project_user_params " +
"set value = ?, datatype_cd = ?, change_date = ?, status_cd = 'A' where changeby_char = ? and id = ?";
numRowsAdded = jt.update(addSql,
((ProjectType) utype).getParam().get(0).getValue(),
((ProjectType) utype).getParam().get(0).getDatatype(),
Calendar.getInstance().getTime(),
caller,
((ProjectType) utype).getParam().get(0).getId());
if (numRowsAdded == 0)
throw new I2B2DAOException("Record does not exist or access denied.");
}
} else if (validateRole(caller, "admin", null) || validateRole(caller, "manager", project))
{
if (utype instanceof ParamsType)
{
} else if (utype instanceof UserType)
{
log.debug("Searching for existing User Param");
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_user_params " +
"(user_id, datatype_cd, param_name_cd, value, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?)";
log.debug("Ading new User Param");
numRowsAdded = jt.update(addSql,
((UserType) utype).getUserName(),
((UserType) utype).getParam().get(0).getDatatype(),
((UserType) utype).getParam().get(0).getName(),
((UserType) utype).getParam().get(0).getValue(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_user_params " +
"set value = ?, datatype_cd = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where id = ? ";
log.debug("Updating User Param");
numRowsAdded = jt.update(addSql,
((UserType) utype).getParam().get(0).getValue(),
((UserType) utype).getParam().get(0).getDatatype(),
Calendar.getInstance().getTime(),
caller,
((UserType) utype).getParam().get(0).getId());
}
}
else if (utype instanceof ProjectType)
{
log.debug("Testing to see if username is set: " + ((ProjectType) utype).getUserName() );
if ((((ProjectType) utype).getUserName() != null) && (!((ProjectType) utype).getUserName().equals("") ))
{
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_project_user_params " +
"(project_id, user_id, datatype_cd, param_name_cd, value, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
((ProjectType) utype).getId(),
((ProjectType) utype).getUserName(),
((ProjectType) utype).getParam().get(0).getDatatype(),
((ProjectType) utype).getParam().get(0).getName(),
((ProjectType) utype).getParam().get(0).getValue(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_project_user_params " +
"set value = ?, datatype_cd = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where id = ?";
numRowsAdded = jt.update(addSql,
((ProjectType) utype).getParam().get(0).getValue(),
((ProjectType) utype).getParam().get(0).getDatatype(),
Calendar.getInstance().getTime(),
caller,
((ProjectType) utype).getParam().get(0).getId());
}
} else {
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_project_params " +
"(project_id, datatype_cd, param_name_cd, value, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
((ProjectType) utype).getId(),
((ProjectType) utype).getParam().get(0).getDatatype(),
((ProjectType) utype).getParam().get(0).getName(),
((ProjectType) utype).getParam().get(0).getValue(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_project_params " +
"set value = ?, datatype_cd = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where id = ?";
numRowsAdded = jt.update(addSql,
((ProjectType) utype).getParam().get(0).getValue(),
((ProjectType) utype).getParam().get(0).getDatatype(),
Calendar.getInstance().getTime(),
caller,
((ProjectType) utype).getParam().get(0).getId());
}
}
} else if (utype instanceof CellDataType)
{
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_cell_params " +
"(cell_id, datatype_cd, project_path, param_name_cd, value, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
((CellDataType) utype).getId(),
((CellDataType) utype).getParam().get(0).getDatatype(),
((CellDataType) utype).getProjectPath(),
((CellDataType) utype).getParam().get(0).getName(),
((CellDataType) utype).getParam().get(0).getValue(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_cell_params " +
"set value = ?, datatype_cd = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where id = ?";
numRowsAdded = jt.update(addSql,
((CellDataType) utype).getParam().get(0).getValue(),
((CellDataType) utype).getParam().get(0).getDatatype(),
Calendar.getInstance().getTime(),
caller,
((CellDataType) utype).getParam().get(0).getId());
}
} else if (utype instanceof ApprovalType)
{
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_approval_params " +
"(approval_id, datatype_cd, object_cd, param_name_cd, value, activation_date, expiration_date, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
((ApprovalType) utype).getId(),
((ApprovalType) utype).getParam().get(0).getDatatype(),
((ApprovalType) utype).getObjectCd(),
((ApprovalType) utype).getParam().get(0).getName(),
((ApprovalType) utype).getParam().get(0).getValue(),
((ApprovalType) utype).getActivationDate(),
((ApprovalType) utype).getExpirationDate(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_approval_params " +
"set value = ?, datatype_cd = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where id = ?";
numRowsAdded = jt.update(addSql,
((ApprovalType) utype).getParam().get(0).getValue(),
((ApprovalType) utype).getParam().get(0).getDatatype(),
Calendar.getInstance().getTime(),
caller,
((ApprovalType) utype).getParam().get(0).getId());
}
} else if (utype instanceof GlobalDataType)
{
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_global_params " +
"( param_name_cd, datatype_cd, project_path, value, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
((GlobalDataType) utype).getParam().get(0).getName(),
((GlobalDataType) utype).getParam().get(0).getDatatype(),
((GlobalDataType) utype).getProjectPath(),
((GlobalDataType) utype).getParam().get(0).getValue(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_global_params " +
"set value = ?, datatype_cd = ?, project_path = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where id = ?";
numRowsAdded = jt.update(addSql,
((GlobalDataType) utype).getParam().get(0).getValue(),
((GlobalDataType) utype).getParam().get(0).getDatatype(),
((GlobalDataType) utype).getProjectPath(),
Calendar.getInstance().getTime(),
caller,
((GlobalDataType) utype).getParam().get(0).getId());
}
} else if (utype instanceof ConfigureType)
{
if (((ConfigureType) utype).getParam().isEmpty() == false) // || (((ConfigureType) utype).getDomainId()).size() == 0))
{
if ((getParam(utype, false) == null) || (getParam(utype, false).size() == 0))
{
String addSql = "insert into pm_hive_params " +
"(domain_id, datatype_cd, param_name_cd, value, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?)";
log.debug("Ading new Hive Param");
numRowsAdded = jt.update(addSql,
((ConfigureType) utype).getDomainId(),
((ConfigureType) utype).getParam().get(0).getDatatype(),
((ConfigureType) utype).getParam().get(0).getName(),
((ConfigureType) utype).getParam().get(0).getValue(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
//user already exists, lets try to update
String addSql = "update pm_hive_params " +
"set value = ?, datatype_cd = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where id = ? ";
log.debug("Updating Hive Param");
numRowsAdded = jt.update(addSql,
((ConfigureType) utype).getParam().get(0).getValue(),
((ConfigureType) utype).getParam().get(0).getDatatype(),
Calendar.getInstance().getTime(),
caller,
((ConfigureType) utype).getParam().get(0).getId());
}
}
else if (((ConfigureType) utype).getDomainId() == null) // || (((ConfigureType) utype).getDomainId()).size() == 0))
{
//user already exists, lets try to update
String addSql = "";
if ((((ConfigureType) utype).isActive() != null) && (((ConfigureType) utype).isActive() == true) ) {
addSql = "update pm_hive_data " +
"set status_cd = 'D', active = 0, change_date = ?, changeby_char = ? where status_cd = 'A'";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller);
}
addSql = "insert into pm_hive_data " +
"( domain_id, environment_cd, domain_name, helpurl, active, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
PMUtil.getInstance().generateMessageId(),
((ConfigureType) utype).getEnvironment(),
((ConfigureType) utype).getDomainName(),
((ConfigureType) utype).getHelpURL(),
((ConfigureType) utype).isActive(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} else
{
String addSql = "update pm_hive_data " +
"set environment_cd = ?, domain_name = ?, helpurl = ?, active = ?, change_date = ?, changeby_char = ?, status_cd = 'A' where domain_id = ?";
numRowsAdded = jt.update(addSql,
((ConfigureType) utype).getEnvironment(),
((ConfigureType) utype).getDomainName(),
((ConfigureType) utype).getHelpURL(),
((ConfigureType) utype).isActive(),
Calendar.getInstance().getTime(),
caller,
((ConfigureType) utype).getDomainId());
}
} else if (utype instanceof RoleType)
{
try
{
// First try to insert if fails than update
String addSql2 = "insert into pm_project_user_roles " +
"( project_id, user_id, user_role_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?)";
numRowsAdded += jt.update(addSql2,
((RoleType) utype).getProjectId().trim(),
((RoleType) utype).getUserName().trim(),
((RoleType) utype).getRole().trim(),
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
} catch (Exception e) {
String addSql2 = "update pm_project_user_roles " +
" set status_cd = 'A', change_date = ?, changeby_char = ? where project_id = ? and user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql2,
Calendar.getInstance().getTime(),
caller,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
((RoleType) utype).getRole());
}
try {
if ((((RoleType) utype).getRole().equals("DATA_AGG")) || (((RoleType) utype).getRole().equals("DATA_LDS")) ||
(((RoleType) utype).getRole().equals("DATA_DEID")) || (((RoleType) utype).getRole().equals("DATA_PROT")))
{
String addSql = "insert into pm_project_user_roles " +
"( project_id, user_id, user_role_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?)";
numRowsAdded += jt.update(addSql,
((RoleType) utype).getProjectId().trim(),
((RoleType) utype).getUserName().trim(),
"DATA_OBFSC",
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
}
} catch (Exception e) {
if ((((RoleType) utype).getRole().equals("DATA_AGG")) || (((RoleType) utype).getRole().equals("DATA_LDS")) ||
(((RoleType) utype).getRole().equals("DATA_DEID")) || (((RoleType) utype).getRole().equals("DATA_PROT")))
{
String addSql = "update pm_project_user_roles " +
" set status_cd = 'A', change_date = ?, changeby_char = ? where project_id = ? and user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"DATA_OBFSC");
}
}
try {
if ((((RoleType) utype).getRole().equals("DATA_LDS")) ||
(((RoleType) utype).getRole().equals("DATA_DEID")) || (((RoleType) utype).getRole().equals("DATA_PROT")))
{
String addSql = "insert into pm_project_user_roles " +
"( project_id, user_id, user_role_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?)";
numRowsAdded += jt.update(addSql,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"DATA_AGG",
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
}
} catch (Exception e) {
if ((((RoleType) utype).getRole().equals("DATA_LDS")) ||
(((RoleType) utype).getRole().equals("DATA_DEID")) || (((RoleType) utype).getRole().equals("DATA_PROT")))
{
String addSql = "update pm_project_user_roles " +
" set status_cd = 'A', change_date = ?, changeby_char = ? where project_id = ? and user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"DATA_AGG");
}
}
try {
if ((((RoleType) utype).getRole().equals("DATA_DEID")) || (((RoleType) utype).getRole().equals("DATA_PROT")))
{
String addSql = "insert into pm_project_user_roles " +
"( project_id, user_id, user_role_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?)";
numRowsAdded += jt.update(addSql,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"DATA_LDS",
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
}
} catch (Exception e) {
if ((((RoleType) utype).getRole().equals("DATA_DEID")) || (((RoleType) utype).getRole().equals("DATA_PROT")))
{
String addSql = "update pm_project_user_roles " +
" set status_cd = 'A', change_date = ?, changeby_char = ? where project_id = ? and user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"DATA_LDS");
}
}
try {
if ( (((RoleType) utype).getRole().equals("DATA_PROT")))
{
String addSql = "insert into pm_project_user_roles " +
"( project_id, user_id, user_role_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?)";
numRowsAdded += jt.update(addSql,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"DATA_DEID",
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
}
} catch (Exception e) {
if ( (((RoleType) utype).getRole().equals("DATA_PROT")))
{
String addSql = "update pm_project_user_roles " +
" set status_cd = 'A', change_date = ?, changeby_char = ? where project_id = ? and user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"DATA_DEID");
}
}
//admin track
try {
if ((((RoleType) utype).getRole().equals("MANAGER")) ||(((RoleType) utype).getRole().equals("ADMIN")) )
{
String addSql = "insert into pm_project_user_roles " +
"( project_id, user_id, user_role_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?)";
numRowsAdded += jt.update(addSql,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"USER",
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
}
} catch (Exception e) {
//admin track
if ((((RoleType) utype).getRole().equals("MANAGER")) ||(((RoleType) utype).getRole().equals("ADMIN")) )
{
String addSql = "update pm_project_user_roles " +
" set status_cd = 'A', change_date = ?, changeby_char = ? where project_id = ? and user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"USER");
}
}
try {
if ((((RoleType) utype).getRole().equals("ADMIN")) )
{
String addSql = "insert into pm_project_user_roles " +
"( project_id, user_id, user_role_cd, change_date, entry_date, changeby_char, status_cd) values (?,?,?,?,?,?,?)";
numRowsAdded += jt.update(addSql,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"MANAGER",
Calendar.getInstance().getTime(),
Calendar.getInstance().getTime(),
caller,
"A");
}
} catch (Exception e) {
if ((((RoleType) utype).getRole().equals("ADMIN")) )
{
String addSql = "update pm_project_user_roles " +
" set status_cd = 'A', change_date = ?, changeby_char = ? where project_id = ? and user_id = ? and user_role_cd = ?";
numRowsAdded += jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName(),
"MANAGER");
}
}
}
}
else
{
throw new I2B2DAOException("Access Denied for " + caller);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows added: " + numRowsAdded);
return numRowsAdded;
}
public int deleteParam(Object utype, final String project, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if (validateRole(caller, "admin", null) || validateRole(caller, "manager", project))
{
try {
if (utype instanceof UserType)
{
String addSql = "update pm_user_params " +
"set status_cd = 'D', change_date = ?, changeBy_char = ? where id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((UserType) utype).getParam().get(0).getId());
} else
if (utype instanceof ProjectType)
{
if ((((ProjectType) utype).getUserName() != null) && (!((ProjectType) utype).getUserName().equals("") ))
{
String addSql = "update pm_project_user_params " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((ProjectType) utype).getParam().get(0).getId());
} else {
String addSql = "update pm_project_params " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((ProjectType) utype).getParam().get(0).getId());
}
} else if (utype instanceof ConfigureType)
{
if (((ConfigureType) utype).getParam().isEmpty() == false) // || (((ConfigureType) utype).getDomainId()).size() == 0))
{
String addSql = "update pm_hive_params " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((ConfigureType) utype).getParam().get(0).getId());
} else {
String addSql = "update pm_hive_data " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where domain_id = ? and active = 0";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((ConfigureType) utype).getDomainId());
}
} else if (utype instanceof CellDataType)
{
String addSql = "update pm_cell_params " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((CellDataType) utype).getParam().get(0).getId());
} else if (utype instanceof ApprovalType)
{
String addSql = "update pm_approval_params " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((ApprovalType) utype).getParam().get(0).getId());
} else if (utype instanceof GlobalDataType)
{
String addSql = "update pm_global_params " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
((GlobalDataType) utype).getParam().get(0).getId());
} else if (utype instanceof RoleType)
{
if (((RoleType) utype).getRole().equals("DATA_PROT"))
{
numRowsAdded += executeRemoveRole("DATA_PROT", caller, utype);
}
else if (((RoleType) utype).getRole().equals("DATA_DEID"))
{
numRowsAdded += executeRemoveRole("DATA_PROT", caller, utype);
numRowsAdded += executeRemoveRole("DATA_DEID", caller, utype);
}
else if (((RoleType) utype).getRole().equals("DATA_LDS"))
{
numRowsAdded += executeRemoveRole("DATA_PROT", caller, utype);
numRowsAdded += executeRemoveRole("DATA_DEID", caller, utype);
numRowsAdded += executeRemoveRole("DATA_LDS", caller, utype);
}
else if (((RoleType) utype).getRole().equals("DATA_AGG"))
{
numRowsAdded += executeRemoveRole("DATA_PROT", caller, utype);
numRowsAdded += executeRemoveRole("DATA_DEID", caller, utype);
numRowsAdded += executeRemoveRole("DATA_LDS", caller, utype);
numRowsAdded += executeRemoveRole("DATA_AGG", caller, utype);
}
else if (((RoleType) utype).getRole().equals("DATA_OBFSC"))
{
numRowsAdded += executeRemoveRole("DATA_PROT", caller, utype);
numRowsAdded += executeRemoveRole("DATA_DEID", caller, utype);
numRowsAdded += executeRemoveRole("DATA_LDS", caller, utype);
numRowsAdded += executeRemoveRole("DATA_AGG", caller, utype);
numRowsAdded += executeRemoveRole("DATA_OBFSC", caller, utype);
}
//admin track
if ((((RoleType) utype).getRole().equals("MANAGER")) )
{
numRowsAdded += executeRemoveRole("MANAGER", caller, utype);
}
else if ((((RoleType) utype).getRole().equals("USER")) )
{
numRowsAdded += executeRemoveRole("MANAGER", caller, utype);
numRowsAdded += executeRemoveRole("USER", caller, utype);
} else
{
numRowsAdded += executeRemoveRole(((RoleType) utype).getRole(), caller, utype);
}
}
if (numRowsAdded == 0)
throw new I2B2DAOException("not updated, does it exist?");
} catch (DataAccessException e) {
log.error("Dao deleteuser failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
} else if ((utype instanceof UserType) && (caller.equals(((UserType) utype).getUserName())))
{
String addSql = "update pm_user_params " +
"set status_cd = 'D', change_date = ? where user_id = ? and changeby_char = ? and id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
caller,
((UserType) utype).getParam().get(0).getId());
if (numRowsAdded == 0)
throw new I2B2DAOException("Record does not exist or access denied.");
} else if ((utype instanceof ProjectType) && (((ProjectType) utype).getUserName() != null) && (((ProjectType) utype).getUserName().equals(caller) ))
{
String addSql = "update pm_project_user_params " +
"set status_cd = 'D', change_date = ? where user_id = ? and changeby_char = ? and id = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
caller,
((ProjectType) utype).getParam().get(0).getId());
if (numRowsAdded == 0)
throw new I2B2DAOException("Record does not exist or access denied.");
}
else
{
throw new I2B2DAOException("Access Denied for " + caller);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows deleted: " + numRowsAdded);
return numRowsAdded;
}
public int executeRemoveRole(String role, String caller, Object utype)
{
String addSql = "update pm_project_user_roles " +
"set status_cd = 'D', change_date = ?, changeby_char = ? where user_role_cd = ? and project_id = ? and user_id = ?";
return jt.update(addSql,
Calendar.getInstance().getTime(),
caller,
role,
((RoleType) utype).getProjectId(),
((RoleType) utype).getUserName());
}
public int deleteProject(final Object project, String caller) throws I2B2DAOException, I2B2Exception{
int numRowsAdded = 0;
if (validateRole(caller, "admin", null))
{
try {
String addSql = "update pm_project_data " +
"set status_cd = 'D', change_date = ? where project_id = ? and project_path = ? and changeby_char = ?";
numRowsAdded = jt.update(addSql,
Calendar.getInstance().getTime(),
((ProjectType) project).getId(),
((ProjectType) project).getPath(),
caller
);
if (numRowsAdded == 0)
throw new I2B2DAOException("Project not updated, does it exist?");
} catch (DataAccessException e) {
log.error("Dao deleteuser failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
}
else
{
throw new I2B2DAOException("Access Denied for " + caller);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows deleted: " + numRowsAdded);
return numRowsAdded;
}
private ParameterizedRowMapper getEnvironmentParams() {
ParameterizedRowMapper<HiveParamData> map = new ParameterizedRowMapper<HiveParamData>() {
public HiveParamData mapRow(ResultSet rs, int rowNum) throws SQLException {
HiveParamData eData = new HiveParamData();
eData.setDomain(rs.getString("domain_id"));
eData.setName(rs.getString("param_name_cd"));
eData.setValue(rs.getString("value"));
return eData;
}
};
return map;
}
private ParameterizedRowMapper getProject() {
ParameterizedRowMapper<ProjectType> map = new ParameterizedRowMapper<ProjectType>() {
public ProjectType mapRow(ResultSet rs, int rowNum) throws SQLException {
ProjectType rData = new ProjectType();
DTOFactory factory = new DTOFactory();
rData.setKey(rs.getString("project_key"));
rData.setName(rs.getString("project_name"));
rData.setPath(rs.getString("project_path"));
rData.setDescription(rs.getString("project_description"));
rData.setId(rs.getString("project_id"));
rData.setWiki(rs.getString("project_wiki"));
return rData;
}
};
return map;
}
private ParameterizedRowMapper getCell() {
ParameterizedRowMapper<CellDataType> map = new ParameterizedRowMapper<CellDataType>() {
public CellDataType mapRow(ResultSet rs, int rowNum) throws SQLException {
CellDataType rData = new CellDataType();
DTOFactory factory = new DTOFactory();
rData.setId(rs.getString("cell_id"));
rData.setName(rs.getString("name"));
rData.setProjectPath(rs.getString("project_path"));
rData.setCanOverride(rs.getBoolean("can_override"));
rData.setMethod(rs.getString("method_cd"));
rData.setUrl(rs.getString("url"));
return rData;
}
};
return map;
}
private ParameterizedRowMapper getProjectRequest() {
ParameterizedRowMapper<ProjectRequestType> map = new ParameterizedRowMapper<ProjectRequestType>() {
public ProjectRequestType mapRow(ResultSet rs, int rowNum) throws SQLException {
ProjectRequestType rData = new ProjectRequestType();
DTOFactory factory = new DTOFactory();
rData.setId(Integer.toString(rs.getInt("id")));
rData.setProjectId(rs.getString("project_id"));
rData.setTitle(rs.getString("title"));
rData.setSubmitChar(rs.getString("submit_char"));
Date date = rs.getDate("entry_date");
if (date == null)
rData.setEntryDate(null);
else
rData.setEntryDate(long2Gregorian(date.getTime()));
rData.setRequestXml(rs.getString("request_xml"));
/*
Clob clob = rs.getClob("request_xml");
if (clob != null) {
try {
BlobType blobType = new BlobType();
blobType.getContent().add(
JDBCUtil.getClobString(clob));
rData.setRequestXml(blobType);
} catch (IOException ioe)
{
log.debug(ioe.getMessage());
}
}
*/
//rData.setRequestXml(rs.getClob("request_xml"));
return rData;
}
};
return map;
}
private ParameterizedRowMapper getApproval() {
ParameterizedRowMapper<ApprovalType> map = new ParameterizedRowMapper<ApprovalType>() {
public ApprovalType mapRow(ResultSet rs, int rowNum) throws SQLException {
ApprovalType rData = new ApprovalType();
DTOFactory factory = new DTOFactory();
rData.setId(rs.getString("approval_id"));
rData.setName(rs.getString("approval_name"));
rData.setDescription(rs.getString("approval_description"));
rData.setObjectCd(rs.getString("object_cd"));
Date date = rs.getDate("approval_activation_date");
if (date == null)
rData.setActivationDate(null);
else
rData.setActivationDate(long2Gregorian(date.getTime()));
date = rs.getDate("approval_expiration_date");
if (date == null)
rData.setExpirationDate(null);
else
rData.setExpirationDate(long2Gregorian(date.getTime()));
return rData;
}
};
return map;
}
private ParameterizedRowMapper getParam() {
ParameterizedRowMapper<ParamType> map = new ParameterizedRowMapper<ParamType>() {
public ParamType mapRow(ResultSet rs, int rowNum) throws SQLException {
ParamType eData = new ParamType();
log.debug("setting name");
eData.setName(rs.getString("param_name_cd"));
eData.setValue(rs.getString("value"));
eData.setId(rs.getInt("id"));
eData.setDatatype(rs.getString("datatype_cd"));
return eData;
}
};
return map;
}
public static XMLGregorianCalendar long2Gregorian(long date) {
DatatypeFactory dataTypeFactory;
try {
dataTypeFactory = DatatypeFactory.newInstance();
} catch (DatatypeConfigurationException e) {
throw new RuntimeException(e);
}
GregorianCalendar gc = new GregorianCalendar();
gc.setTimeInMillis(date);
return dataTypeFactory.newXMLGregorianCalendar(gc);
}
private ParameterizedRowMapper getGlobal() {
ParameterizedRowMapper<GlobalDataType> map = new ParameterizedRowMapper<GlobalDataType>() {
public GlobalDataType mapRow(ResultSet rs, int rowNum) throws SQLException {
DTOFactory factory = new DTOFactory();
GlobalDataType eData = new GlobalDataType();
log.debug("setting name");
ParamType param = new ParamType();
param.setId(rs.getInt("id"));
param.setName(rs.getString("param_name_cd"));
param.setValue(rs.getString("value"));
param.setDatatype(rs.getString("datatype_cd"));
eData.getParam().add(param);
eData.setProjectPath(rs.getString("project_path"));
eData.setCanOverride(rs.getBoolean("can_override"));
return eData;
}
};
return map;
}
private ParameterizedRowMapper getUserParams() {
ParameterizedRowMapper<UserParamData> map = new ParameterizedRowMapper<UserParamData>() {
public UserParamData mapRow(ResultSet rs, int rowNum) throws SQLException {
UserParamData eData = new UserParamData();
eData.setId(rs.getInt("id"));
eData.setDatatype(rs.getString("datatype_cd"));
eData.setUser(rs.getString("user_id"));
eData.setName(rs.getString("param_name_cd"));
eData.setValue(rs.getString("value"));
log.debug("Found a user/param: " + rs.getString("user_id") + ":" + rs.getString("param_name_cd"));
return eData;
}
};
return map;
}
private ParameterizedRowMapper getProjectUserParams() {
ParameterizedRowMapper<ProjectUserParamData> map = new ParameterizedRowMapper<ProjectUserParamData>() {
public ProjectUserParamData mapRow(ResultSet rs, int rowNum) throws SQLException {
ProjectUserParamData eData = new ProjectUserParamData();
eData.setProject(rs.getString("project_path"));
eData.setUser(rs.getString("user_id"));
eData.setName(rs.getString("param_name"));
eData.setValue(rs.getString("value"));
return eData;
}
};
return map;
}
private ParameterizedRowMapper getProjectParams() {
ParameterizedRowMapper<ParamType> map = new ParameterizedRowMapper<ParamType>() {
public ParamType mapRow(ResultSet rs, int rowNum) throws SQLException {
ParamType eData = new ParamType();
//eData.setProject(rs.getString("project_path"));
eData.setName(rs.getString("param_name_cd"));
eData.setValue(rs.getString("value"));
return eData;
}
};
return map;
}
private ParameterizedRowMapper getSession() {
ParameterizedRowMapper<SessionData> map = new ParameterizedRowMapper<SessionData>() {
public SessionData mapRow(ResultSet rs, int rowNum) throws SQLException {
SessionData rData = new SessionData();
// DTOFactory factory = new DTOFactory();
rData.setSessionID(rs.getString("session_id"));
Date date = rs.getTimestamp("expired_date");
if (date == null)
rData.setExpiredDate(null);
else
rData.setExpiredDate(date);
date = rs.getTimestamp("entry_date");
if (date == null)
rData.setIssuedDate(null);
else
rData.setIssuedDate(date);
return rData;
}
};
return map;
}
private ParameterizedRowMapper getUserLogin() {
ParameterizedRowMapper<SessionData> map = new ParameterizedRowMapper<SessionData>() {
public SessionData mapRow(ResultSet rs, int rowNum) throws SQLException {
SessionData rData = new SessionData();
// DTOFactory factory = new DTOFactory();
rData.setSessionID(rs.getString("session_id"));
Date date = rs.getTimestamp("expired_date");
if (date == null)
rData.setExpiredDate(null);
else
rData.setExpiredDate(date);
date = rs.getTimestamp("entry_date");
if (date == null)
rData.setIssuedDate(null);
else
rData.setIssuedDate(date);
return rData;
}
};
return map;
}
private ParameterizedRowMapper getRole() {
ParameterizedRowMapper<RoleType> map = new ParameterizedRowMapper<RoleType>() {
public RoleType mapRow(ResultSet rs, int rowNum) throws SQLException {
RoleType rData = new RoleType();
rData.setProjectId(rs.getString("project_id"));
rData.setUserName(rs.getString("user_id"));
rData.setRole(rs.getString("user_role_cd"));
return rData;
}
};
return map;
}
private ParameterizedRowMapper getEnvironment() {
ParameterizedRowMapper<ConfigureType> map = new ParameterizedRowMapper<ConfigureType>() {
public ConfigureType mapRow(ResultSet rs, int rowNum) throws SQLException {
DTOFactory factory = new DTOFactory();
ConfigureType eData = new ConfigureType();
eData.setActive(rs.getBoolean("active"));
eData.setDomainId(rs.getString("domain_id"));
eData.setDomainName(rs.getString("domain_name"));
eData.setHelpURL(rs.getString("helpurl"));
eData.setEnvironment(rs.getString("environment_cd"));
return eData;
}
};
return map;
}
private ParameterizedRowMapper getUser(final boolean includePassword) {
ParameterizedRowMapper<UserType> map = new ParameterizedRowMapper<UserType>() {
public UserType mapRow(ResultSet rs, int rowNum) throws SQLException {
DTOFactory factory = new DTOFactory();
UserType userData = new UserType();
userData.setFullName(rs.getString("full_name"));
userData.setUserName(rs.getString("user_id"));
try {
userData.setIsAdmin(validateRole(userData.getUserName(), "ADMIN",null));
} catch (I2B2DAOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (includePassword) {
PasswordType pass = new PasswordType();
pass.setValue(rs.getString("password"));
userData.setPassword(pass);
}
userData.setEmail(rs.getString("email"));
return userData;
}
};
return map;
}
}