public Vector buscar(String pidConsulta) throws java.sql.SQLException, Exception {
Medicina medicina = null;
Vector medicinas;
java.sql.ResultSet rs;
String sql;
sql =
"SELECT id, nombre,dosis, numDias, FK_idConsulta, fechaInicio, fechaFinal, precauciones "
+ "FROM TMedicina "
+ "WHERE FK_idConsulta='"
+ pidConsulta
+ "';";
rs = Conector.getConector().ejecutarSQL(sql, true);
medicinas = new Vector<Medicina>();
if (rs.next()) {
do {
medicina =
new Medicina(
rs.getString("nombre"),
rs.getString("dosis"),
rs.getString("numDias"),
rs.getString("FK_idConsulta"),
rs.getString("fechaInicio"),
rs.getString("fechaFinal"),
rs.getString("precauciones"));
medicinas.add(medicina);
} while (rs.next());
} else {
throw new Exception("No hay examenes de ese paciente.");
}
rs.close();
return medicinas;
}
protected void setTrayActive(String strDir) {
String strTray;
String strStudy;
String strActive = "Active";
String cmd =
"SELECT vrack_,studystatus from study WHERE (autodir=\'"
+ strDir
+ "\') AND (hostname=\'"
+ getHost()
+ "\')";
m_dbResult = null;
try {
m_dbResult = getdbmanager().executeQuery(cmd);
} catch (Exception e) {
return;
}
if (m_dbResult == null) return;
int nTray = 0;
try {
while (m_dbResult.next()) {
strTray = m_dbResult.getString(1);
strStudy = m_dbResult.getString(2);
if (strStudy != null && strStudy.equalsIgnoreCase(strActive)) {
try {
nTray = Integer.parseInt(strTray);
} catch (Exception e) {
}
if (nTray > 0) {
m_pnlVast[nTray - 1].setbackground(Color.blue);
m_pnlVast[nTray - 1].setToolTipText(Util.getTooltipString(TRAYACTIVE));
break;
}
}
}
} catch (Exception e) {
// e.printStackTrace();
Messages.writeStackTrace(e);
}
}
public void assertObjectExistenceInSQLIteMasterTable(
final String name,
final String type,
boolean exists,
final ConnectionManager connectionManager) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.prepareStatement("SELECT name FROM sqlite_master WHERE type=?");
statement.setString(1, type);
java.sql.ResultSet indices = statement.executeQuery();
boolean found = false;
StringBuilder objectsFound = new StringBuilder();
String next;
while (indices.next()) {
next = indices.getString(1);
objectsFound.append("'").append(next).append("' ");
if (name.equals(next)) {
found = true;
}
}
if (exists)
Assert.assertTrue(
"Object '"
+ name
+ "' must exists in 'sqlite_master' but it doesn't. found: "
+ found
+ ". Objects found: "
+ objectsFound,
found);
else
Assert.assertFalse(
"Object '"
+ name
+ "' must NOT exists in 'sqlite_master' but it does. found: "
+ found
+ " Objects found: "
+ objectsFound,
found);
} catch (Exception e) {
throw new IllegalStateException(
"Unable to verify existence of the object '" + name + "' in the 'sqlite_master' table",
e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
protected String getSampleName(String strDir, String strTray) {
String strSampleName = "";
String strSample;
int nTray = 0;
try {
nTray = Integer.parseInt(strTray);
} catch (Exception e) {
}
if (nTray <= 0) return strSampleName;
String cmd =
"SELECT loc_,studystatus from study WHERE (autodir=\'"
+ strDir
+ "\') AND (hostname=\'"
+ getHost()
+ "\')";
// NB: bArrSample[0] is not used; array implicitly initialized false
boolean[] bArrSample = new boolean[nTray + 1];
m_dbResult = null;
try {
m_dbResult = getdbmanager().executeQuery(cmd);
} catch (Exception e) {
return strSampleName;
}
if (m_dbResult == null) return strSampleName;
try {
while (m_dbResult.next()) {
strSample = m_dbResult.getString(1);
int nSample = 0;
nSample = Integer.parseInt(strSample);
try {
bArrSample[nSample] = true;
} catch (IndexOutOfBoundsException ioobe) {
Messages.postDebug("getSampleName: index out of bounds: " + nSample);
}
}
for (int i = 1; i < bArrSample.length; i++) {
if (!bArrSample[i]) {
strSampleName = String.valueOf(i);
break;
}
}
} catch (Exception e) {
// e.printStackTrace();
Messages.writeStackTrace(e);
}
return strSampleName;
}
// ---------------------------------------------------------------------------
protected void initialize(java.sql.ResultSet rs) {
try {
if (s_logger.isDebug()) {
java.sql.ResultSetMetaData meta = rs.getMetaData();
for (int I = 1; I <= meta.getColumnCount(); I++) {
s_logger.debug("Reading - " + meta.getColumnName(I) + " : " + rs.getString(I));
}
}
m_id.setValue(rs, 1);
m_metricId.setValue(rs, 2);
m_timestamp.setValue(rs, 3);
m_longValue.setValue(rs, 4);
m_doubleValue.setValue(rs, 5);
} catch (java.sql.SQLException sqle) {
throw new GenOrmException(sqle);
}
}
/**
* A method to build a collection of collaborator objects representing a network based on an
* organisation
*
* @param id the id of the central organisation
* @param radius the number of edges required from the central contributor
* @return the collection of collaborator objects
*/
@SuppressWarnings("rawtypes")
public TreeMap<Integer, Collaborator> getRawCollaboratorData_org(String id, int radius) {
// check the parameters
if (InputUtils.isValidInt(id) == false) {
throw new IllegalArgumentException("Error: the id parameter is required");
}
if (InputUtils.isValidInt(radius, ExportServlet.MIN_DEGREES, ExportServlet.MAX_DEGREES)
== false) {
throw new IllegalArgumentException(
"Error: the radius parameter must be between "
+ ExportServlet.MIN_DEGREES
+ " and "
+ ExportServlet.MAX_DEGREES);
}
// define helper variables
// collection of collaborators
java.util.TreeMap<Integer, Collaborator> cId_cObj_map =
new java.util.TreeMap<Integer, Collaborator>();
// set of collaborators that we've already processed
java.util.TreeSet<Integer> foundCollaboratorsSet = new java.util.TreeSet<Integer>();
// define other helper variables
String contributor_id = null;
QuerySolution row = null;
Collaborator collaborator = null;
String sql =
"SELECT DISTINCT b.eventid "
+ "FROM events a, orgevlink b "
+ "WHERE b.organisationid = ? " // +id
+ "AND a.eventid = b.eventid";
int[] param = {Integer.parseInt(id)};
java.sql.ResultSet resultSet = db.exePreparedStatement(sql, param);
ArrayList<String> eventResults = new ArrayList<String>();
try {
// check to see that data was returned
if (!resultSet.last()) {
db.tidyup();
return null;
} else resultSet.beforeFirst();
// loop through the resultset
while (resultSet.next() == true) {
eventResults.add(resultSet.getString(1));
}
} catch (java.sql.SQLException ex) {
System.out.println("Exception: " + ex.getMessage());
resultSet = null;
}
db.tidyup();
// helper
int first = 0;
// define the query
String sparqlQuery1 =
"PREFIX foaf: <"
+ FOAF.NS
+ "> "
+ "PREFIX ausestage: <"
+ AuseStage.NS
+ "> "
+ "PREFIX event: <http://purl.org/NET/c4dm/event.owl#> "
+ "PREFIX dcterms: <http://purl.org/dc/terms/> "
+ "SELECT DISTINCT ?agent ?givenName ?familyName "
+ "WHERE { ";
for (String event : eventResults) {
if (first > 0) {
sparqlQuery1 += "UNION ";
}
first++;
sparqlQuery1 +=
"{<ausstage:e:"
+ event
+ "> a event:Event; "
+ " event:agent ?agent. "
+ " ?agent a foaf:Person; "
+ " foaf:givenName ?givenName; "
+ " foaf:familyName ?familyName. "
+ " } ";
}
sparqlQuery1 += " } ";
// execute query
ResultSet results = rdf.executeSparqlQuery(sparqlQuery1);
// now we transfer the results to a TreeMap <Integer,
while (results.hasNext()) {
row = results.nextSolution();
contributor_id = AusStageURI.getId(row.get("agent").toString());
collaborator = new Collaborator(contributor_id);
cId_cObj_map.put(Integer.parseInt(contributor_id), collaborator);
foundCollaboratorsSet.add(Integer.parseInt(contributor_id));
}
rdf.tidyUp();
return cId_cObj_map;
} // end getRawCollaboratorData_org method
/**
* A method to build a list of Collaboration objects representing a list of collaborations
* associated with a collaborator
*
* @param id the unique identifier of a collaborator
* @return a CollaborationList object containing a list of Collaboration objects
*/
public CollaborationList getCollaborationList_org(String org_id, String id) {
// check on the input parameters
if (InputUtils.isValidInt(id) == false) {
throw new IllegalArgumentException("The id parameter cannot be null");
}
// declare helper variables
CollaborationList list = new CollaborationList(id);
// define other helper variables
Collaboration collaboration = null;
// define other helper variables
String partner = null;
Integer count = null;
String firstDate = null;
String lastDate = null;
// define the base sql query
String sqlQuery =
"SELECT distinct count(*), con.contributorid, min(e.first_date), max(e.first_date) "
+ "FROM contributor con, conevlink c , conevlink c2, orgevlink o, events e "
+ "WHERE o.organisationid = ? " // + org_id + " "
+ "AND c.eventid = O.EVENTID "
+ "AND e.eventid = O.EVENTID "
+ "AND e.eventid = c.EVENTID "
+ "AND c.contributorid != ?" // + id + " "
+ "AND con.contributorid = c.contributorid "
+ "AND c2.contributorid = ? " // + id + " "
+ "AND c2.eventid = c.eventid "
+ "GROUP BY con.contributorid, con.first_name ";
int[] param = {Integer.parseInt(org_id), Integer.parseInt(id), Integer.parseInt(id)};
// execute the query
java.sql.ResultSet resultSet = db.exePreparedStatement(sqlQuery, param);
try {
// check to see that data was returned
if (!resultSet.last()) {
db.tidyup();
return null;
} else resultSet.beforeFirst();
// loop though the resulset
while (resultSet.next()) {
// get the data
partner = resultSet.getString(2);
count = resultSet.getInt(1);
firstDate = resultSet.getDate(3).toString();
lastDate = resultSet.getDate(4).toString();
// create the collaboration object
collaboration = new Collaboration(id, partner, count, firstDate, lastDate);
// add the collaboration to the list
list.addCollaboration(collaboration);
}
} catch (java.sql.SQLException ex) {
System.out.println("Exception: " + ex.getMessage());
resultSet = null;
}
db.tidyup();
// return the list of collaborations
return list;
} // end the CollaborationList method
private List<Object[]> getCustomStgUsers(boolean usedb2) {
List<Object[]> listData = new ArrayList<Object[]>();
String ServerName = "";
int PortNumber;
String DatabaseName = "";
java.util.Properties properties;
String url = "";
java.sql.Connection con = null;
if (usedb2) {
ServerName = synDB.getConfigProperties("SERVER_NAME");
PortNumber = Integer.parseInt(synDB.getConfigProperties("PORT_NUMBER"));
DatabaseName = synDB.getConfigProperties("DATABASE");
properties = new java.util.Properties();
properties.put("user", synDB.getConfigProperties("USER"));
properties.put("password", synDB.getConfigProperties("PASSWORD"));
properties.put("sslConnection", "true");
System.setProperty("javax.net.ssl.trustStore", synDB.getConfigProperties("CACERTS_PATH"));
System.setProperty("javax.net.ssl.trustStorePassword", "changeit");
System.setProperty("db2.jcc.charsetDecoderEncoder", "3");
url = "jdbc:db2://" + ServerName + ":" + PortNumber + "/" + DatabaseName;
try {
new DB2Driver();
} catch (Exception e) {
System.out.println("Error: failed to load Db2 jcc driver.");
}
} else {
ServerName = synDB.getConfigProperties("SERVER_NAME");
PortNumber = Integer.parseInt(synDB.getConfigProperties("PORT_NUMBER"));
DatabaseName = synDB.getConfigProperties("DATABASE");
properties = new java.util.Properties();
properties.put("user", synDB.getConfigProperties("USER"));
properties.put("password", synDB.getConfigProperties("PASSWORD"));
url = "jdbc:postgresql://" + ServerName + ":" + PortNumber + "/" + DatabaseName;
}
try {
System.out.println("url: " + url);
con = java.sql.DriverManager.getConnection(url, properties);
try {
String sql =
"SELECT a.ID_NRIC, FIRST_NAME, LAST_NAME, MOBILE, EMAIL, ACCOUNT_STATUS, AGENT_CODE, AGENCY, NEED2FA, NEEDTNC, USER_TYPE, USER__SUB_TYPE, a.CREATED_DATE, b.ROLE_NAME "
+ "FROM CUSTOM_STG_USER a INNER JOIN CUSTOM_STG_USER_ROLE b ON (b.ID_NRIC=a.ID_NRIC) ";
StringBuilder sbSQLSTGUser = new StringBuilder();
sbSQLSTGUser.append(sql);
System.out.println("Select from PSE.CUSTOM_STG_USER DB2: " + sbSQLSTGUser.toString());
java.sql.Statement ps = con.createStatement();
java.sql.ResultSet rs = ps.executeQuery(sbSQLSTGUser.toString());
System.out.println("get data....");
Object[] objArrays = new Object[14];
while (rs.next()) {
objArrays = new Object[14];
objArrays[0] = rs.getString(1);
objArrays[1] = rs.getString(2);
objArrays[2] = rs.getString(3);
objArrays[3] = rs.getString(4);
objArrays[4] = rs.getString(5);
objArrays[5] = rs.getString(6);
objArrays[6] = rs.getString(7);
objArrays[7] = rs.getString(8);
objArrays[8] = rs.getString(9);
objArrays[9] = rs.getString(10);
objArrays[10] = rs.getString(11);
objArrays[11] = rs.getString(12);
objArrays[12] = rs.getString(13);
objArrays[13] = rs.getString(14);
listData.add(objArrays);
System.out.println(
"GET FROM DB2 | ID_NRIC:"
+ objArrays[0]
+ ", FIRST_NAME:"
+ objArrays[1]
+ ", LAST_NAME:"
+ objArrays[2]
+ ", MOBILE:"
+ objArrays[3]
+ ", EMAIL:"
+ objArrays[4]
+ ", ACCOUNT_STATUS:"
+ objArrays[5]
+ ", AGENT_CODE:"
+ objArrays[6]
+ ", AGENCY:"
+ objArrays[7]
+ ", NEED2FA:"
+ objArrays[8]
+ ", NEEDTNC:"
+ objArrays[9]
+ ", USER_TYPE:"
+ objArrays[10]
+ ", USER_SUB_TYPE:"
+ objArrays[11]
+ ", CREATED_DATE :"
+ objArrays[12]
+ ", ROLE_NAME :"
+ objArrays[13]);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("select is failing1");
}
if (con != null) {
try {
con.close();
con = null;
} catch (Exception e) {
}
}
} catch (Exception e) {
e.printStackTrace();
}
return listData;
}